mysql crash! TIMESTAMP with implicit DEFAULT value is deprecated.

[stefano_ts]

What can I do?
I can not restart the mysql service.
Thank you

150905 22:57:40 mysqld_safe Number of processes running now: 0
150905 22:57:40 mysqld_safe mysqld restarted
2015-09-05 22:57:42 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-09-05 22:57:43 18793 [Note] Plugin 'FEDERATED' is disabled.
2015-09-05 22:57:43 18793 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-09-05 22:57:43 18793 [Note] InnoDB: The InnoDB memory heap is disabled
2015-09-05 22:57:43 18793 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-09-05 22:57:43 18793 [Note] InnoDB: Memory barrier is not used
2015-09-05 22:57:43 18793 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-09-05 22:57:43 18793 [Note] InnoDB: Using Linux native AIO
2015-09-05 22:57:43 18793 [Note] InnoDB: Using CPU crc32 instructions
2015-09-05 22:57:43 18793 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-09-05 22:57:44 18793 [Note] InnoDB: Completed initialization of buffer pool
2015-09-05 22:57:44 18793 [Note] InnoDB: Highest supported file format is Barracuda.
2015-09-05 22:57:44 18793 [Note] InnoDB: Log scan progressed past the checkpoint lsn 2094130155
2015-09-05 22:57:44 18793 [Note] InnoDB: Database was not shutdown normally!
2015-09-05 22:57:44 18793 [Note] InnoDB: Starting crash recovery.
2015-09-05 22:57:44 18793 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-09-05 22:57:44 18793 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace roundcube_bk/cache_messages uses space ID: 291 at filepath: ./roundcube_bk/cache_messages.ibd. Cannot open tablespace tosolab_www/wp_rg_form which uses space ID: 291 at filepath: ./tosolab_www/wp_rg_form.ibd
2015-09-05 22:57:44 7f91257a1720 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: Error: could not open single-table tablespace file ./tosolab_www/wp_rg_form.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
150905 22:57:44 mysqld_safe mysqld from pid file /var/lib/mysql/vps110954.ovh.net.pid ended

 

[stefano_ts]

I partially solved in this way:
- in my.cfg i added the following line: innodb_force_recovery = 1
- i finally managed to start mysql service
- i removed the line innodb_force_recovery = 1 from my.cfg
- i stopped/started mysql service.

But in /var/lib/mysql/... .err i's yet reported: [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

what can I do?
Thanks

 

[cPanelMichael]

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.

Hello

Do you have a corresponding entry in your /etc/my.cnf file for "timestamp"?

Thank you.

 

[stefano_ts]

The file my.cnf contains this information:

[mysqld]
innodb_file_per_table=1
bind-address=127.0.0.1
max_allowed_packet=268435456
open_files_limit=10000
default-storage-engine=MyISAM
#innodb_force_recovery = 1

Thank you.

 

[cPanelMichael]

But in /var/lib/mysql/... .err i's yet reported: [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

You can add the following entry to your /etc/my.cnf file (assuming you are using MySQL 5.6):

explicit_defaults_for_timestamp=1

Then, restart MySQL. This variable is documented at:

https://dev.mysql.com/doc/refman/5....s.html#sysvar_explicit_defaults_for_timestamp

Thank you.

 

[Venomous21]

I am also receiving the [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details) but mysql appears to be running fine. I just upgraded from 5.5 to 5.6 tonight. Here's my.cnf:

[mysqld]
performance-schema=0
slow-query-log=1
long-query-time=1
max_connections=500
# safe-show-database
open_files_limit=10000
innodb_buffer_pool_size=134217728
max_allowed_packet=268435456
default-storage-engine=MyISAM
innodb_file_per_table=1

Should I definitely add explicit_defaults_for_timestamp=1 ? If I don't, will it cause problems? I read the documentation and find parts of it hard to understand. Appreciate your help. Thanks.

 

[cPanelMichael]

Should I definitely add explicit_defaults_for_timestamp=1 ? If I don't, will it cause problems? I read the documentation and find parts of it hard to understand. Appreciate your help. Thanks.

It's not required at this time, but it's a good idea to add the value in the future because of it's deprecation status.

Thank you.