[MDEV-18301] sql_safe_updates prevents initial database creation Created: 2019-01-18  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Configuration, Scripts & Clients
Affects Version/s: 10.3.11
Fix Version/s: 10.4

Type: Bug Priority: Minor
Reporter: Vincent Milum Jr Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: None
Environment:

FreeBSD 11.2 jail
mariadb103-server-10.3.11_2


 Description   

In my particular use case, I use Galera and was attempting to add a new node to the cluster. MariaDB first bootstraps itself by generated the mysql tables before attempting to copy the contents from an existing node in the Galera cluster. The creation of the base mysql tables fails if sql_safe_updates is enabled. My current work-around is to remove sql_safe_updates to initially create the tables, then stop the service, re-enable sql_safe_updates, and start the service again to join the cluster with it enabled.

1) Create a fresh FreeBSD 11.2 jail (any jail version in theory should work for this)

2) pkg install mariadb103-server

3) Create /usr/local/etc/my.cnf with the following contents:

[mysqld]
 
sql-safe-updates=1

4) Attempt to start the service:

root@maria-bork:~ # service mysql-server onestart
 
Installing MariaDB/MySQL system tables in '/var/db/mysql' ...
 
ERROR: 1175  You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
 
2019-01-18  9:12:32 0 [ERROR] Aborting
 
 
 
 
 
Installation of system tables failed!  Examine the logs in
 
/var/db/mysql for more information.
 
 
 
The problem could be conflicting information in an external
 
my.cnf files. You can ignore these by doing:
 
 
 
    shell> /usr/local/bin/mysql_install_db --defaults-file=~/.my.cnf
 
 
 
You can also try to start the mysqld daemon with:
 
 
 
    shell> /usr/local/libexec/mysqld --skip-grant-tables --general-log &
 
 
 
and use the command line tool /usr/local/bin/mysql
 
to connect to the mysql database and look at the grant tables:
 
 
 
    shell> /usr/local/bin/mysql -u root mysql
 
    mysql> show tables;
 
 
 
Try 'mysqld --help' if you have problems with paths.  Using
 
--general-log gives you a log in /var/db/mysql that may be helpful.
 
 
 
The latest information about mysql_install_db is available at
 
https://mariadb.com/kb/en/installing-system-tables-mysql_install_db
 
You can find the latest source at https://downloads.mariadb.org and
 
the maria-discuss email list at https://launchpad.net/~maria-discuss
 
 
 
Please check all of the above before submitting a bug report
 
at http://mariadb.org/jira
 
 
 
/usr/local/etc/rc.d/mysql-server: WARNING: failed precmd routine for mysql

5) Attempting to start the service again (this time logs are generated)

root@maria-bork:/var/db/mysql # service mysql-server onestart
 
Starting mysql.
 
 
 
 
 
root@maria-bork:/var/db/mysql # cat maria-bork.err
 
2019-01-18  9:12:50 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 
2019-01-18  9:12:50 0 [Note] InnoDB: Uses event mutexes
 
2019-01-18  9:12:50 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
 
2019-01-18  9:12:50 0 [Note] InnoDB: Number of pools: 1
 
2019-01-18  9:12:50 0 [Note] InnoDB: Using SSE2 crc32 instructions
 
2019-01-18  9:12:50 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
 
2019-01-18  9:12:50 0 [Note] InnoDB: Completed initialization of buffer pool
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/gtid_slave_pos.ibd' OS error: 81
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``mysql`.`gtid_slave_pos``. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
 
2019-01-18  9:12:51 0 [Warning] InnoDB: Ignoring tablespace for `mysql`.`gtid_slave_pos` because it could not be opened.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/innodb_index_stats.ibd' OS error: 81
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``mysql`.`innodb_index_stats``. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
 
2019-01-18  9:12:51 0 [Warning] InnoDB: Ignoring tablespace for `mysql`.`innodb_index_stats` because it could not be opened.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/innodb_table_stats.ibd' OS error: 81
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``mysql`.`innodb_table_stats``. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
 
2019-01-18  9:12:51 0 [Warning] InnoDB: Ignoring tablespace for `mysql`.`innodb_table_stats` because it could not be opened.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/transaction_registry.ibd' OS error: 81
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
 
2019-01-18  9:12:51 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``mysql`.`transaction_registry``. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
 
2019-01-18  9:12:51 0 [Warning] InnoDB: Ignoring tablespace for `mysql`.`transaction_registry` because it could not be opened.
 
2019-01-18  9:12:51 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
 
2019-01-18  9:12:51 0 [Note] InnoDB: Creating shared tablespace for temporary tables
 
2019-01-18  9:12:51 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 
2019-01-18  9:12:51 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
 
2019-01-18  9:12:51 0 [Note] InnoDB: Waiting for purge to start
 
2019-01-18  9:12:51 1 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`innodb_index_stats` in the cache. Attempting to load the tablespace with space id 2
 
2019-01-18  9:12:51 1 [ERROR] InnoDB: Operating system error number 13 in a file operation.
 
2019-01-18  9:12:51 1 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
 
2019-01-18  9:12:51 1 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/innodb_index_stats.ibd' OS error: 81
 
2019-01-18  9:12:51 1 [ERROR] InnoDB: Operating system error number 13 in a file operation.
 
2019-01-18  9:12:51 1 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
 
2019-01-18  9:12:51 1 [ERROR] InnoDB: Could not find a valid tablespace file for ``mysql`.`innodb_index_stats``. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
 
2019-01-18  9:12:51 3 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`innodb_table_stats` in the cache. Attempting to load the tablespace with space id 1
 
2019-01-18  9:12:51 3 [ERROR] InnoDB: Operating system error number 13 in a file operation.
 
2019-01-18  9:12:51 3 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
 
2019-01-18  9:12:51 3 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/innodb_table_stats.ibd' OS error: 81
 
2019-01-18  9:12:51 3 [ERROR] InnoDB: Operating system error number 13 in a file operation.
 
2019-01-18  9:12:51 3 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
 
2019-01-18  9:12:51 3 [ERROR] InnoDB: Could not find a valid tablespace file for ``mysql`.`innodb_table_stats``. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
 
2019-01-18  9:12:51 0 [Note] InnoDB: 10.3.11 started; log sequence number 1631012; transaction id 21
 
2019-01-18  9:12:51 0 [Note] InnoDB: Loading buffer pool(s) from /var/db/mysql/ib_buffer_pool
 
2019-01-18  9:12:51 0 [Note] InnoDB: Buffer pool(s) load completed at 190118  9:12:51
 
2019-01-18  9:12:51 0 [Note] Plugin 'FEEDBACK' is disabled.
 
2019-01-18  9:12:51 0 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
 
2019-01-18  9:12:51 0 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
 
2019-01-18  9:12:51 0 [Note] Server socket created on IP: '::'.
 
2019-01-18  9:12:51 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist



 Comments   
Comment by Elena Stepanova [ 2019-01-19 ]

Indeed, quite expectedly installation / upgrade don't work with sql_safe_updates configured in the cnf. serg, do you think we should maybe unset it in the scripts creating/updating the datadir?

Generated at Thu Feb 08 08:43:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.