How do I enable "explicit_defaults_for_timestamp"?

Asked

Viewed 3,713 times

5

I installed Mysql Server 5.6. When I try to connect the Mysql server by running mysqld.exe, I get the following message:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

In fact, the solution lies in the documentation, but I have not found a way to put it into practice. Some say to put explicit_defaults_for_timestamp = true in the archive my.ini, others say to put in the file my.cnf, but there are none of those files here. I was also told that it is possible to solve this by putting Mysql as a Windows service, but I don’t see it in the list of services.

How can I fix this problem and start the server normally?

I created the file my.ini manually, the error disappeared, but another appeared, twice:

2014-08-28 19:27:45 1292 [Warning] Can't create test file C:\Program Files\MySQL\MySQL Server 5.6\data\Patrick.lower-test
2014-08-28 19:27:45 1292 [Warning] Can't create test file C:\Program Files\MySQL\MySQL Server 5.6\data\Patrick.lower-test

That’s when I try to run the mysqld.exe, when I try to execute the mysql.exe appears the following:

ERROR 2003 <HY000>: Can't connect to MySQL server on 'localhost' <10061>

Today I tried to start Mysql with Netbeans IDE 8.0.1 and this came up:

2014-09-22 23:32:55 2580 [Warning] Can't create test file C:\Program Files\MySQL\MySQL Server 5.6\data\Patrick.lower-test
2014-09-22 23:32:58 2580 [Warning] Can't create test file C:\Program Files\MySQL\MySQL Server 5.6\data\Patrick.lower-test
2014-09-22 23:32:58 2580 [Note] Plugin 'FEDERATED' is disabled.
2014-09-22 23:33:02 2580 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-09-22 23:33:02 2580 [Note] InnoDB: The InnoDB memory heap is disabled
2014-09-22 23:33:02 2580 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2014-09-22 23:33:02 2580 [Note] InnoDB: Memory barrier is not used
2014-09-22 23:33:02 2580 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-09-22 23:33:02 2580 [Note] InnoDB: Not using CPU crc32 instructions
2014-09-22 23:33:03 2580 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-09-22 23:33:04 2580 [Note] InnoDB: Completed initialization of buffer pool
2014-09-22 23:33:04 2580 [ERROR] InnoDB: .\ibdata1 can't be opened in read-write mode
2014-09-22 23:33:04 2580 [ERROR] InnoDB: The system tablespace must be writable!
2014-09-22 23:33:04 2580 [ERROR] Plugin 'InnoDB' init function returned error.
2014-09-22 23:33:04 2580 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2014-09-22 23:33:04 2580 [ERROR] Unknown/unsupported storage engine: InnoDB
2014-09-22 23:33:04 2580 [ERROR] Aborting

2014-09-22 23:33:05 2580 [Note] Binlog end
2014-09-22 23:33:05 2580 [Note] Shutting down plugin 'partition'
2014-09-22 23:33:05 2580 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2014-09-22 23:33:05 2580 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2014-09-22 23:33:05 2580 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2014-09-22 23:33:05 2580 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2014-09-22 23:33:05 2580 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_METRICS'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_CMPMEM'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_CMP'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_LOCKS'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'INNODB_TRX'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'BLACKHOLE'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'ARCHIVE'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'MRG_MYISAM'
2014-09-22 23:33:06 2580 [Note] Shutting down plugin 'MyISAM'
2014-09-22 23:33:07 2580 [Note] Shutting down plugin 'MEMORY'
2014-09-22 23:33:07 2580 [Note] Shutting down plugin 'CSV'
2014-09-22 23:33:07 2580 [Note] Shutting down plugin 'sha256_password'
2014-09-22 23:33:31 2580 [Note] Shutting down plugin 'mysql_old_password'
2014-09-22 23:33:31 2580 [Note] Shutting down plugin 'mysql_native_password'
2014-09-22 23:33:31 2580 [Note] Shutting down plugin 'binlog'
2014-09-22 23:33:31 2580 [Note] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: Shutdown complete
mysql
  • 2

    This may be the case of manually creating the my.ini.

  • @bfavaretto, the error disappeared, but another appeared.

  • The error still occurs?

  • @Fernando, yes.

  • Then go back to my.ini original because apparently, by the can’t connect message, Mysql is not even running. Then confirm how to only add the required directive to my.ini (explicit_defaults_for_timestamp)

  • There was no my.ini original, I had to use the manual method that the bfavaretto indicated, when the messages, they appeared since before the creation of the my.ini.

  • At first I only put a message (the first) because I thought the problem was there only, but later I realized that this was not the case.

  • Take it easy. By the way, my.ini is in windows. My.cnf on linux.

  • Take a look here: http://stackoverflow.com/a/16836525/1817673

  • Did you find a solution? Poste as an answer to help other people.

  • I haven’t found a solution yet.

Show 6 more comments

1 answer

2

I’ve been through this problem and solved it this way:

I edited the my.cnf file (which in my case is on a Debian linux server, is stored on /etc/mysql/my.cnf) and on the tag [mysqld] inserted the following definition:

explicit_defaults_for_timestamp = false

Thus the tag [mysqld] my.cnf file was as follows:

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp = false

In your case, because it is Windows, I think the difference would be only the mysql configuration file location, which is probably my.ini

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.