search

TablePlus / DBngin

DB Engine
https://dbngin.com
958 stars 19 forks source link

Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode' #120

Closed nexxai closed 9 months ago

nexxai commented 9 months ago

Please fill out the detail below, it helps me investigate the bug:

  1. Driver (Ex: PostgreSQL 10.0): MySQL 8.0.33

  2. DBngin build number: 6.9 (69)

  3. macOS version: Sonoma 14.0 (23A344)

  4. The steps to reproduce this issue:

The connection will fail but in the /Users/[USERNAME]/Library/Application Support/com.tinyapp.DBngin/Engines/mysql/[INSTANCE ID]/mysqld.local.err file, there will be the following logs:

2023-10-21T19:00:12.275984Z 0 [ERROR] [MY-000077] [Server] /Users/Shared/DBngin/mysql/8.0.33/bin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2023-10-21T19:00:12.276148Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-10-21T19:00:12.276173Z 0 [Note] [MY-010120] [Server] Binlog end
2023-10-21T19:00:22.494029Z 0 [ERROR] [MY-000077] [Server] /Users/Shared/DBngin/mysql/8.0.33/bin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2023-10-21T19:00:22.494116Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-10-21T19:00:22.494142Z 0 [Note] [MY-010120] [Server] Binlog end
2023-10-21T19:00:32.704689Z 0 [ERROR] [MY-000077] [Server] /Users/Shared/DBngin/mysql/8.0.33/bin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2023-10-21T19:00:32.704778Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-10-21T19:00:32.704803Z 0 [Note] [MY-010120] [Server] Binlog end
2023-10-21T19:00:42.918036Z 0 [ERROR] [MY-000077] [Server] /Users/Shared/DBngin/mysql/8.0.33/bin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2023-10-21T19:00:42.918125Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-10-21T19:00:42.918149Z 0 [Note] [MY-010120] [Server] Binlog end
2023-10-21T19:00:53.153350Z 0 [ERROR] [MY-000077] [Server] /Users/Shared/DBngin/mysql/8.0.33/bin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2023-10-21T19:00:53.153444Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-10-21T19:00:53.153471Z 0 [Note] [MY-010120] [Server] Binlog end
2023-10-21T19:01:03.370474Z 0 [ERROR] [MY-000077] [Server] /Users/Shared/DBngin/mysql/8.0.33/bin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2023-10-21T19:01:03.370685Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-10-21T19:01:03.370707Z 0 [Note] [MY-010120] [Server] Binlog end
2023-10-21T19:01:13.577597Z 0 [ERROR] [MY-000077] [Server] /Users/Shared/DBngin/mysql/8.0.33/bin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2023-10-21T19:01:13.577694Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-10-21T19:01:13.577719Z 0 [Note] [MY-010120] [Server] Binlog end
2023-10-21T19:01:23.783491Z 0 [ERROR] [MY-000077] [Server] /Users/Shared/DBngin/mysql/8.0.33/bin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2023-10-21T19:01:23.783566Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-10-21T19:01:23.783590Z 0 [Note] [MY-010120] [Server] Binlog end
2023-10-21T19:01:33.983750Z 0 [ERROR] [MY-000077] [Server] /Users/Shared/DBngin/mysql/8.0.33/bin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2023-10-21T19:01:33.983854Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-10-21T19:01:33.983876Z 0 [Note] [MY-010120] [Server] Binlog end
2023-10-21T19:01:44.169522Z 0 [ERROR] [MY-000077] [Server] /Users/Shared/DBngin/mysql/8.0.33/bin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2023-10-21T19:01:44.169607Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-10-21T19:01:44.169633Z 0 [Note] [MY-010120] [Server] Binlog end
2023-10-21T19:01:54.386004Z 0 [ERROR] [MY-000077] [Server] /Users/Shared/DBngin/mysql/8.0.33/bin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2023-10-21T19:01:54.386112Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-10-21T19:01:54.386137Z 0 [Note] [MY-010120] [Server] Binlog end
  1. Steps I've tried
    • Reinstalled DBngin
    • Recreating the MySQL database engine
    • Deleting the MySQL binaries to force DBngin to redownload them
    • Removing the engine, rebooting my laptop, and recreating it
    • Creating a blank mysql.conf file with only sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION" in it and setting it as the 'Config` inside the engine's config within DBngin

I am able to successfully create a working Redis engine and a working PostgreSQL engine. I've also tried to figure out where the default mysqld.conf file is supposed to be located, but cannot find one to manually remove the NO_AUTO_CREATE_USER flag, as it seems that according to this SO post, that flag is no longer available in MySQL 8

nexxai commented 9 months ago

Ended up solving my own problem.

I went into the downloaded binaries folder the DBngin downloads and creates, and saw that there was a my_print_defaults executable there. I ran it, and even though it errored out, it did say this:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

On a whim, I checked if any of those files existed and the first one (/etc/my.cnf) actually did, I assume from a previous install of MySQL that I had created and subsequently removed. After opening it, I saw this:

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

I deleted the file, recreated the engine in DBngin and it started up perfectly fine.