#2820 (ok 3.1.4)ANSI mode not supported - DB rename and table move

[darking]

Performing DB rename operations or moving tables from one DB to the other these problems show up.
Tried it both in my server (phpmyadmin-2.11.8.1 mysql(d)-5.0.51a) and in the demo server (3.2.0-dev-svn12170 mysql-5.1.19-beta mysqld-5.1.30-2-log).
With both versions problems occur. This includes apache peaking at 100% CPU with connections open to mysql but without actually executing all queries. Failing due to exceeding memory limits. Failing due to hitting Maximum execution time of (default) 30 seconds.

Steps to reproduce:
First create some content.

CREATE DATABASE `ansi_test1` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
CREATE TABLE `ansi_test1`.`bah` (
`me` int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
`you` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY ( `me` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1;
INSERT INTO `ansi_test1`.`bah` VALUES(1, NOW());

Set GLOBAL.sql_mode to ANSI:

SET @@GLOBAL.sql_mode='ANSI';

Now try to Rename the DB using the Operations interface.
In the demo server I get:
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 81 bytes) in /srv/http/pma.cihar.com/trunk-config/libraries/Error.class.php on line 124

In my server I haven't reached memory limits but I do get maximum execution timeouts, idle/sleeping mysql connections and apache peaking with 100%CPU.

If afterwards we remove the ANSI mode:
SET @@GLOBAL.sql_mode='';
The DB renaming operation takes a fraction of second to run.

However if in sql_mode='' we copy the SQL code generated with the DB renaming operation and run it in ANSI mode it executes without problems:

SET @@GLOBAL.sql_mode='ANSI';
CREATE DATABASE `ansi_test2` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
CREATE TABLE `ansi_test2`.`bah` (
`me` int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
`you` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY ( `me` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1;
INSERT INTO `ansi_test2`.`bah`
SELECT *
FROM `ansi_test1`.`bah` ;
DROP TABLE `ansi_test1`.`bah` ;
DROP DATABASE `ansi_test1` ;

So it seems this is triggered by some other internal reason and not by the generated SQL.

[Marc Delisle]

Fixed in subversion, thanks for reporting.