Issue Mysql tables innodb lost after alter table

[Nicola]

Hi,
I have a problem for a couple of days with mysql (5.7) and innodb tables, I try to explain:

I have a main database (dbDeveloping) and a series of databases in production (db1Production, db2Production, ...), when I modify the structure of some tables in "db1Developing", through a php script I update the structure of the tables in "db1Production", "db2Production", ... .

In a table I added two columns of type "date" and during the updating phase in some databases it happens that the table disappears and I can not recreate it anymore.

I checked in /var/lib/mysql/db1Production/ and there are table.frm and table.ibd files but I can no longer access the tables via mysql.

At the moment when this problem occurs mysql no longer responds and I am forced to restart it.

I attach some mysql logs



This morning I have lots of lines with this warning

2019-02-20T07:38:08.940353Z 648090 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2019-02-20T07:38:08.940366Z 648090 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

Thanks in advance for the help

 

[Mikhail Lapkin]

I'd do backup and then run mysql_upgrade just as warnings suggest. Similar issue: MySQL :: InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name
If that wouldn't solve the problem then MySQL :: MySQL 5.7 Reference Manual :: 14.21.3 Troubleshooting InnoDB Data Dictionary Operations should be of some help.

Also as of mySQL 5.7 ERROR_FOR_DIVISION_BY_ZERO is deprecated and one should use STRICT_TRANS_TABLES or TRADITIONAL sql mode along with it: MySQL :: MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes