Page 1 of 1

MySQL ANSI Mode

Posted: Fri Feb 23, 2007 5:13 am
by Ollie Saunders
According to this page in the MySQL you can execute these two commands

Code: Select all

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET GLOBAL sql_mode = 'ANSI';
to run your server in ANSI mode. This I did. Then I restarted the server. And yet I didn't the result I expected.

I thought with ANSI mode you use single quotes instead of backticks for indentifier names. So...

Code: Select all

DROP DATABASE IF EXISTS 'Example';
...would work and...

Code: Select all

DROP DATABASE IF EXISTS `Example`;
...wouldn't and yet I get this...

Code: Select all

mysql> DROP DATABASE IF EXISTS 'Example';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Example'' at line 1
mysql>
Have I done something wrong / got the wrong end of the stick?

Posted: Fri Feb 23, 2007 5:21 am
by onion2k
Wouldn't all the things you've used SET for be reset back to the default config on a restart? I know that "SET NAMES" and "SET CHARACTERSET" are both per connection. I imagine most things would be.

Posted: Fri Feb 23, 2007 7:29 am
by Begby
onion2k wrote:Wouldn't all the things you've used SET for be reset back to the default config on a restart? I know that "SET NAMES" and "SET CHARACTERSET" are both per connection. I imagine most things would be.
This is correct. You need to edit the mysql config file before restarting.

Posted: Fri Feb 23, 2007 9:46 am
by Ollie Saunders
Any idea how you would make it permanent then?

Posted: Fri Feb 23, 2007 11:46 am
by Begby
ole wrote:Any idea how you would make it permanent then?
Begby wrote:This is correct. You need to edit the mysql config file before restarting.
If you save the file after editing it, then don't change it, that will make it permanent :wink:

Posted: Fri Feb 23, 2007 6:21 pm
by Ollie Saunders
Whoops sorry I didn't refresh before posting. Thanks Begby.