Mysql – Predict MySQL errors when turning on STRICT mode(s)

MySQL

I've got a database which hasn't been very well designed. For example, there are DATETIME fields (not null) which have values like 0000-00-00 00:00:00.

I want to turn on STRICT_TRANS_TABLES mode. The database functions but the apps often fail with broken queries (often INSERT/UDPATE)

Are there any tools to recommend what should be fixed by looking at the data in tables & structure?

Specifically looking for:

Built in warnings (if any?)
Asking the mysql server if it thinks there is anything wrong with the structure & table data

Best Answer

There are few, if any, tools are available.
You really must study the code enough to understand it, before thinking about automated fixes.
You can probably study each problem in depth and fix it before moving on. For example, changing 0 dates to NULLs is probably an ALTER plus an UPDATE. But then the code needs changing to test for NULL instead of 0.

Related Solutions

Mysql – Errors after installing thesql

The problem I see is the error message that says

Table 'mysql.plugin' doesn't exist **120105 6:02:49 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it

MySQL 5.1 introduced plugins. MySQL 5.0 and prior do not have plugins. My guess is that you had MySQL 5.0 running, you uninstalled MySQL 5.0, and tried to install either MySQL 5.1 or 5.5 and it complained about mysql.plugin being missing.

Try cleaning up the 5.0 install as follows

uninstall mysql 5.0
Move /usr/local/var/mysql to /usr/local/var/mysql_old
Install mysql 5.5

Give it a Try !!!

MySQL: Can’t find .ibd files after turning on innodb_file_per_table

The .ibd files will be under the innodb_data_home_dir under a subdirectory, the subdirectory name matching the database name.

Given innodb_data_home_dir=/etc/local/mysql/data and innodb_file_per_table is set:

 SHOW VARIABLES LIKE 'innodb_data_home_dir'
 SHOW VARIABLES LIKE 'innodb_file_per_table'

then when table test.mytable is created, the .ibd file will be located in /etc/local/mysql/data/test/mytable.ibd

(It's possible that the ibdata1 file will grow to accommodate rollback for transactions. When a transaction is run, pre-change copies of blocks get recorded, in case you issue a ROLLBACK, and also for the benefit of concurrent transactions (with an appropriate isolation levels), that need to reference only committed changes, and not any uncommitted changes.)

Best Answer

Related Solutions

Mysql – Errors after installing thesql

MySQL: Can’t find .ibd files after turning on innodb_file_per_table

Related Question