Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18301

sql_safe_updates prevents initial database creation

    XMLWordPrintable

    Details

      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
      

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            darkain Vincent Milum Jr
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated: