Ganskop: /* On Macs: */

2025-01-23T02:00:20Z

On Macs:

← Older revision		Revision as of 22:00, 22 January 2025
Line 23:		Line 23:

	== On Macs: ==		== On Macs: ==
	Edit: <code>/private/etc/my.cnf</code>		Edit: <code>/private/etc/my.cnf</code> (if the file doesn't already exist, you can just create it)

	That's specified at System Preferences → MySQL → Configuration tab → Configuration File		That's specified at System Preferences → MySQL → Configuration tab → Configuration File


	Include:		Include:

Ganskop at 01:58, 23 January 2025

2025-01-23T01:58:29Z

← Older revision		Revision as of 21:58, 22 January 2025
Line 29:		Line 29:
	Include:		Include:
	[mysqld]		[mysqld]
			sql_mode = "ANSI,TRADITIONAL"
	sql_mode = "ANSI,TRADITIONAL"
	Restart the MySQL server with System Preferences → MySQL → Configuration tab → Stop MySQL Server		Restart the MySQL server with System Preferences → MySQL → Configuration tab → Stop MySQL Server

	The MySQL server has to be restarted after editing my.cnf (or my.ini on Windows; use services.msc to restart it)		The MySQL server has to be restarted after editing my.cnf (or my.ini on Windows; use services.msc to restart it)


	== Temporary Changes ==		== Temporary Changes ==

Ganskop: Created the "MySQL Configuration" page

2025-01-14T14:45:52Z

Created the "MySQL Configuration" page

New page

See the MySQL Server SQL Mode specs at https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

== On Windows: ==
Edit <code>C:\ProgramData\MySQL\MySQL Server 8.0\my.ini</code>

Change:
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
To:
sql-mode="ANSI,TRADITIONAL"
Restart the MySQL server (search for services.msc → search for MySQL 8 in the list of services → click restart link)

Note that ProgramData is a hidden folder and that my.ini is read-only for non-admins, so its perms have to be adjusted before editing it. To edit the permissions:

* Right click on my.ini
* Go to Properties
* Go to Security tab
* Click Edit button
* Click Users
* Check "Full Control" in Allow column
* Click OK, OK

== On Macs: ==
Edit: <code>/private/etc/my.cnf</code>

That's specified at System Preferences → MySQL → Configuration tab → Configuration File

Include:
[mysqld]

sql_mode = "ANSI,TRADITIONAL"
Restart the MySQL server with System Preferences → MySQL → Configuration tab → Stop MySQL Server

The MySQL server has to be restarted after editing my.cnf (or my.ini on Windows; use services.msc to restart it)

== Temporary Changes ==
To just change the settings for the current session, this query can be used:
SET SESSION sql_mode = "ANSI,TRADITIONAL";
And can be checked with:
SELECT @@SESSION.SQL_MODE;
The global settings can be checked with:
SELECT @@GLOBAL.SQL_MODE;

== Why? ==
Given this example in the GROUP BY spec:
CREATE TABLE mytable (
id INT UNSIGNED NOT NULL PRIMARY KEY,
a VARCHAR(10),
b INT
);

INSERT INTO mytable
VALUES (1, 'abc', 1000),
(2, 'abc', 2000),
(3, 'def', 4000);

This should return an error:
SELECT a, SUM(b) FROM mytable;

Also,
SELECT "foo";
Will return
Unknown column 'foo' in 'field list'
since strings should be enclosed in single quotes, not double quotes.

And the SQL concatenation operator will work. I.e.,
SELECT 'foo' || 'bar';
Will return
foobar
instead of 0

== Notes ==
Both <code>ANSI</code> and <code>TRADITIONAL</code> are combination modes. Therefore,
sql_mode="ANSI,TRADITIONAL"
is actually the equivalent of:
sql_mode="REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

MySQL Workbench has an error relating to <code>ANSI_QUOTES</code>. If <code>sql_mode</code> contains <code>ANSI_QUOTES</code>, the Reverse Engineering functionality will throw this error for all tables in the selected database: <code>is not valid at this position for this server version, expecting an identifier</code>.

You can get around this error by omitting <code>ANSI_QUOTES</code>:
SET GLOBAL sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,IGNORE_SPACE,ONLY_FULL_GROUP_BY,TRADITIONAL';

MySQL Configuration - Revision history

Ganskop: /* On Macs: */

Ganskop at 01:58, 23 January 2025

Ganskop: Created the "MySQL Configuration" page