MySQL Upgrade Issues – Resolving Sticky Problems After MySQL Upgrade

MySQLmysql-5.5mysql-5.6upgrade

few weeks ago we upgraded mysql server from 5.5 to 5.6
on 1st server few days ago (5May) I reboot mysql server , after this customer reported that some database data table not load to their site . (mostly phpfox – also few in other scripts) – also new data not submitted to database
I was reinstall mysql – disable cloudlinux – cagefs … but problem not resolve. also rebuild php
after above , I move that database to remote-mysql-test-server with same Mysql version (5.6) and problem resolved. So I found this problem mysql related
yesterday in an other server like above (server B – that mysql upgraded from 5.5 – 5.6 few weeks ago) when mysql server restart , same problem occurred . for example when I answer ticket (WHMCS) , this date not submitted to ticket … some clients can't update their site .

few website error like this :
SQL] [1364] Field 'stat_time' doesn't have a default value
STRING: INSERT INTO pn_statistics (stat_name,stat_value) VALUES ('Day-1394-02-24','0')

also in this server when I move database to remote-mysql-test-server this problem resolved.

I speak with few system admin and they can't find my issue .

Any suggestion ?

Best Answer

PROBLEM

I think your problem stems from a change in sql_mode.

sql_mode is blank in MySQL 5.5
It is not blank in MySQL 5.6

SOLUTION

Run this command

SET GLOBAL sql_mode = '';

Then, add this to my.cnf to keep sql_mode blank should you restart mysqld

[mysqld]
sql_mode = ''

You do not need to restart mysqld since you set it globally

GIVE IT A TRY !!!

Related Solutions

Mysql – Changing the thesql.proc table directly after upgrade

Here is a suggestion: do a bait-and-switch.

ALTER TABLE mysql.proc RENAME mysql.proc_orig;
CREATE TABLE mysql.proc LIKE mysql.proc_orig;
ALTER TABLE mysql.proc MODIFY comment TEXT;
INSERT INTO mysql.proc SELECT * FROM mysql.proc_orig;
FLUSH TABLES;

CREATE TABLE mysql.proc_orig LIKE mysql.proc;
ALTER TABLE mysql.proc_orig MODIFY comment TEXT;
INSERT INTO mysql.proc_orig SELECT * FROM mysql.proc;
ALTER TABLE mysql.proc RENAME mysql.proc_bckp;
ALTER TABLE mysql.proc_orig RENAME mysql.proc;
ALTER TABLE mysql.proc_bckp RENAME mysql.proc_orig;
FLUSH TABLES;

If the Stored Procedures work, you can drop mysql.proc_orig or keep it as a backup.

If not, undo everything as follows:

DROP TABLE mysql.proc;
ALTER TABLE mysql.proc_orig RENAME mysql.proc;

Give it a Try !!!

CAVEAT : The table INFORMATION_SCHEMA.ROUTINES for MySQL 5.5 should have ROUTINE_COMMENT as LONGTEXT. Make sure the contents of mysql.proc and INFORMATION_SCHEMA.ROUTINES match.

Since you cannot manipulate INFORMATION_SCHEMA.ROUTINES, If you want to feel safe about this, just restart MySQL.

Mysql – How to upgrade MySQL (5.5) production DB to 5.6 with minimal downtime

We used replication (no GTID) to replicate from our production 5.5 servers to our new 5.6 servers so to minimize downtime. Basically, set up your new 5.6 server to replicate from the 5.5 (cannot use GTID at this time). Dump 5.5 and restore to 5.6, start the replication.

When you move production to the 5.6 server, stop the replication from the 5.5 server . Reconfigure 5.6 server to use GTID (need to issue RESET MASTER and RESET SLAVE on the 5.6). Then configure your downstream 5.6 slaves.