MySQL ANSI Mode

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

MySQL ANSI Mode

Post 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?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Any idea how you would make it permanent then?
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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:
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Whoops sorry I didn't refresh before posting. Thanks Begby.
Post Reply