Error 3552 hy000 access to system schema mysql is rejected

Let us examine and look at the possible scenario that triggers the error access to system schema ‘MySQL’ is rejected. We will look at the troubleshooting steps necessary to remove the error with the support of MySQL support services at Bobcares.

ERROR 3552 (HY000): Access to System Schema ‘mysql’ Rejection.

As the MySQL base is a system in MySQL8, the —add-drop-database and —all-databases attributes clash, resulting in an error in the restoration “ERROR 3552 (HY000) at line 19044: Access to system schema error 352’mysql’  reject.”

Troubleshooting methods:

• The error SQL:

  /*!40000 DROP DATABASE IF EXISTS `mysql`*/;

  The simplest way around this is to open the dump SQL file, remove this SQL, and use sed if the file is too large.

• When dumping a broken instance with all databases and using add-drop-statement to try to preserve the data, we are likely to get the ‘access to system schema ‘mysql’ is refusal’ error.

  In this process, we will be stymied. As a result, we are no longer able to delete the MySQL system database.

  Consider that the database that we have is at 150GB, and opening it manually is not an option. The statement to remove in this case was as shown below:

  /*!40000 DROP DATABASE IF EXISTS `mysql`*/;

  In this scenario, the sed command is:

  sed -i 's//*!40000 DROP DATABASE IF EXISTS `mysql`*/;/ /g' backup.sql

One approach to dealing with the access to system schema ‘MySQL’ rejection scenario is to first paste the statement into an empty text file, then execute the program to ensure that it works.

In this manner, we don’t spend time running a very big backup file, because the version of sed, or the operating system, may resolve the regular expression differently.

Finally, we can check whether the resolution of the error is successful or not by updating the MySQL Services.

[Need assistance with similar queries? We are here to help]

Conclusion

To conclude we have now gone through the error that access to system schema ‘MySQL’ is rejected. We have also seen the reasons and the troubleshooting method necessary to remove the error.

With the support of our MySQL support services, we have gone through all of the possible troubleshooting and configurations to deal with this error.

Похожие вопросы

MySQL — система управления базами данных (СУБД) с открытым исходным кодом от компании Oracle. Она была разработана и оптимизирована специально для работы веб-приложений. MySQL является неотъемлемой частью таких веб-сервисов, как Facebook, Twitter, Wikipedia, YouTube и многих других.

Эта статья расскажет, как определять, с чем связаны частые ошибки на сервере MySQL, и устранять их.

Не удаётся подключиться к локальному серверу

Одной из распространённых ошибок подключения клиента к серверу является «ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)».

Эта ошибка означает, что на хосте не запущен сервер MySQL (mysqld) или вы указали неправильное имя файла сокета Unix или порт TCP/IP при попытке подключения.

Убедитесь, что сервер работает. Проверьте процесс с именем mysqld на хосте сервера, используя команды ps или grep, как показано ниже.

$ ps xa | grep mysqld | grep -v mysqld

Если эти команды не показывают выходных данных, то сервер БД не работает. Поэтому клиент не может подключиться к нему. Чтобы запустить сервер, выполните команду systemctl.

$ sudo systemctl start mysql        #Debian/Ubuntu
$ sudo systemctl start mysqld       #RHEL/CentOS/Fedora

Чтобы проверить состояние службы MySQL, используйте следующую команду:

$ sudo systemctl status mysql       #Debian/Ubuntu
$ sudo systemctl status mysqld      #RHEL/CentOS/Fedora

Если в результате выполнения команды произошла ошибка службы MySQL, вы можете попробовать перезапустить службу и ещё раз проверить её состояние.

$ sudo systemctl restart mysql
$ sudo systemctl status mysql

Если сервер работает (как показано) и вы по-прежнему видите эту ошибку, вам следует проверить, не заблокирован ли порт TCP/IP брандмауэром или любой другой службой блокировки портов.

Для поиска порта, который прослушивается сервером, используйте команду netstat.

$ sudo netstat -tlpn | grep "mysql"

Ещё одна похожая и часто встречающаяся ошибка подключения — «(2003) Can’t connect to MySQL server on ‘server’ (10061)». Это означает, что в сетевом соединении было отказано.

Следует проверить, работает ли в системе сервер MySQL (смотрите выше) и на тот ли порт вы подключаетесь (как найти порт, можно посмотреть выше).

Похожие частые ошибки, с которыми вы можете столкнуться при попытке подключиться к серверу MySQL:

ERROR 2003: Cannot connect to MySQL server on 'host_name' (111)
ERROR 2002: Cannot connect to local MySQL server through socket '/tmp/mysql.sock' (111)

Ошибки запрета доступа в MySQL

В MySQL учётная запись (УЗ) определяется именем пользователя и клиентским хостом, с которого пользователь может подключиться. УЗ может также иметь данные для аутентификации (например, пароль).

Причин для запрета доступа может быть много. Одна из них связана с учётными записями MySQL, которые сервер разрешает использовать клиентским программам при подключении. Это означает, что имя пользователя, указанное в соединении, может не иметь прав доступа к базе данных.

В MySQL есть возможность создавать учётные записи, позволяющие пользователям клиентских программ подключаться к серверу и получать доступ к данным. Поэтому при ошибке доступа проверьте разрешение УЗ на подключение к серверу через клиентскую программу.

Увидеть разрешённые привилегии учётной записи можно, выполнив в консоли команду SHOW GRANTS
Входим в консоль (пример для Unix, для Windows консоль можно найти в стартовом меню):

В консоли вводим команду:

> SHOW GRANTS FOR 'tecmint'@'localhost';

Дать привилегии конкретному пользователю в БД по IP-адресу можно, используя следующие команды:

> grant all privileges on *.test_db to 'tecmint'@'192.168.0.100';
> flush privileges;

Ошибки запрещённого доступа могут также возникнуть из-за проблем с подключением к MySQL (см. выше).

Потеря соединения с сервером MySQL

С этой ошибкой можно столкнуться по одной из следующих причин:

• плохое сетевое соединение;
• истекло время ожидания соединения;
• размер BLOB  больше, чем max_allowed_packet.

В первом случае убедитесь, что у вас стабильное сетевое подключение (особенно, если подключаетесь удалённо).

Если проблема с тайм-аутом соединения (особенно при первоначальном соединении MySQL с сервером), увеличьте значение параметра connect_timeout.

В случае с размером BLOB нужно установить более высокое значение для max_allowed_packet в файле конфигурации /etc/my.cnf в разделах [mysqld] или [client] как показано ниже.

[mysqld]
connect_timeout=100
max_allowed_packet=500M

Если файл конфигурации недоступен, это значение можно установить с помощью следующей команды.

> SET GLOBAL connect_timeout=100;
> SET GLOBAL max_allowed_packet=524288000;

Слишком много подключений

Эта ошибка означает, что все доступные соединения используются клиентскими программами. Количество соединений (по умолчанию 151) контролируется системной переменной max_connections. Устранить проблему можно, увеличив значение переменной в файле конфигурации /etc/my.cnf.

[mysqld]
max_connections=1000

Недостаточно памяти

Если такая ошибка возникла, это может означать, что в MySQL недостаточно памяти для хранения всего результата запроса.

Сначала нужно убедиться, что запрос правильный. Если это так, то нужно выполнить одно из следующих действий:

• если клиент MySQL используется напрямую, запустите его с ключом --quick switch, чтобы отключить кешированные результаты;
• если вы используете драйвер MyODBC, пользовательский интерфейс (UI) имеет расширенную вкладку с опциями. Отметьте галочкой «Do not cache result» (не кешировать результат).

Также может помочь MySQL Tuner. Это полезный скрипт, который подключается к работающему серверу MySQL и даёт рекомендации по настройке для более высокой производительности.

$ sudo apt-get install mysqltuner     #Debian/Ubuntu
$ sudo yum install mysqltuner         #RHEL/CentOS/Fedora
$ mysqltuner

MySQL продолжает «падать»

Если такая проблема возникает, необходимо выяснить, заключается она в сервере или в клиенте. Обратите внимание, что многие сбои сервера вызваны повреждёнными файлами данных или индексными файлами.

Вы можете проверить состояние сервера, чтобы определить, как долго он работал.

$ sudo systemctl status mysql       #Debian/Ubuntu
$ sudo systemctl status mysqld      #RHEL/CentOS/Fedora

Чтобы узнать время безотказной работы сервера, запустите команду mysqladmin.

$ sudo mysqladmin version -p 

Кроме того, можно остановить сервер, сделать отладку MySQL и снова запустить службу. Для отображения статистики процессов MySQL во время выполнения других процессов откройте окно командной строки и введите следующее:

$ sudo mysqladmin -i 5 status

Или

$ sudo mysqladmin -i 5 -r status

Заключение

Самое важное при диагностике — понять, что именно вызвало ошибку. Следующие шаги помогут вам в этом:

• Первый и самый важный шаг — просмотреть журналы MySQL, которые хранятся в каталоге /var/log/mysql/. Вы можете использовать утилиты командной строки вроде tail для чтения файлов журнала.
• Если служба MySQL не запускается, проверьте её состояние с помощью systemctl. Или используйте команду journalctl (с флагом -xe) в systemd.
• Вы также можете проверить файл системного журнала (например, /var/log/messages) на предмет обнаружения ошибок.
• Попробуйте использовать такие инструменты, как Mytop, glances, top, ps или htop, чтобы проверить, какая программа использует весь ресурс процессора или блокирует машину. Они также помогут определить нехватку памяти, дискового пространства, файловых дескрипторов или какого-либо другого важного ресурса.
• Если проблема в каком-либо процессе, можно попытаться его принудительно остановить, а затем запустить (при необходимости).
• Если вы уверены, что проблемы именно на стороне сервера, можете выполнить команды: mysqladmin -u root ping или mysqladmin -u root processlist, чтобы получить от него ответ.
• Если при подключении проблема не связана с сервером, проверьте, нормально ли работает клиент. Попробуйте получить какие-либо его выходные данные для устранения неполадок.

Перевод статьи «Useful Tips to Troubleshoot Common Errors in MySQL»

Hello, I am doing an import from a 5.7.31 database to a 8.0 database in preparation of adding this as an additional READ-ONLY replica.

Command used for the dump: mysqldump -u root -p --skip-lock-tables --single-transaction --flush-logs --master-data=2 -A --max_allowed_packet=1G > ./mysqldump-test-01212022.sql

Command used for the import: mysql -uroot -p < ./mysqldump-crmtest-01212022.sql

I am getting the error on import : ERROR 3554 (HY000) at line 30970: Access to system table 'mysql.innodb_index_stats' is rejected.

Research shows I can use the -f option to continue despite errors or retake a dump excluding the innodb tables. Since this I am trying to make add an additional replica, I’m not 100% on what to do here.

1. What is the recommended handing for this?

Similar but question related to replication:

Even though this is a replica, is this concept correct — I can exclude any table from the source dump I want. But the replication will pick up at the position it was left off at. This means if a table doesn’t exists at the replica an INSERT/READ/UPDATE would fail. I could manually create the table on the replica, and the INSERTS would work, but READS/UPDATES may not be consistent because the only records that would be in the replica would be from the point starting after the dump file, according to the BIN LOG POSITION.

This is the URL I am using add a replica to an existing 3 node Source-Replica cluster: https://linuxscriptshub.com/mysql-replication-setup-without-downtime/

Thanks!

DD

With MySQL 8.0, one key feature is the new Data Dictionary.

The system tables that were previously in MyISAM are now replaced by new protected ones in the DD.

My friend Giuseppe already explained how you could see those tables using sandbox  and he also warned you that you should not mess up with them in this post too.

I’ll explain you how you can see those tables and their actual content. But will also explain why we decided to protect them and why it should stay like that.

DD protected internal tables list

The easiest way to get the list of the DD protected tables is to use the debug binary. Since Giuseppe’s blog, DD has evolved to use a dedicated tablespace, therefor the comment about using the filesystem files won’t’ work with newer MySQL 8.0.

The easiest way to use the debug binary of mysqld is to change the systemd service file for mysqld:

# systemctl edit --full mysqld

Just replace mysqld by mysqld-debug in the [Service] section:

ExecStart=/usr/sbin/mysqld-debug --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS

Then restart mysqld:

# systemctl restart mysqld

Now in a MySQL client session, we can see the DD hidden protected tables when we add a debug attribute:

mysql> SET SESSION debug='+d,skip_dd_table_access_check';

If you don’t do it, you will have the following error:

ERROR 3554 (HY000): Access to data dictionary table 'mysql.tables' is rejected.

Now you can list the tables:

SELECT tables1.name, mysql.tablespaces.name 
FROM
(  SELECT * FROM mysql.tables WHERE schema_id 
   IN (SELECT id FROM mysql.schemata WHERE name='mysql')
) AS tables1 
LEFT JOIN
  mysql.tablespaces ON tables1.tablespace_id = tablespaces.id
WHERE tables1.name NOT IN ('ndb_binlog_index') 
ORDER BY tables1.name;
+------------------------------+-------+
| name                         | name  |
+------------------------------+-------+
| catalogs                     | mysql |
| character_sets               | mysql |
| collations                   | mysql |
| column_statistics            | mysql |
...
| tablespaces                  | mysql |
| triggers                     | mysql |
| view_routine_usage           | mysql |
| view_table_usage             | mysql |
+------------------------------+-------+

Accessing the DD protected tables

In the same session where the debug variable has been set, we can now access those tables:

mysql> select * from mysql.tablespaces;
+----+------------------+---------+-----------------------------------------------------------------+---------+--------+
| id | name             | options | se_private_data                                                 | comment | engine |
+----+------------------+---------+-----------------------------------------------------------------+---------+--------+
|  1 | mysql            | NULL    | flags=18432;id=4294967294;server_version=80004;space_version=1; |         | InnoDB |
|  2 | innodb_system    | NULL    | flags=18432;id=0;server_version=80004;space_version=1;          |         | InnoDB |
|  3 | innodb_temporary | NULL    | flags=4096;id=4294967293;server_version=80004;space_version=1;  |         | InnoDB |
|  4 | innodb_undo_001  | NULL    | flags=0;id=4294967279;server_version=80004;space_version=1;     |         | InnoDB |
|  5 | innodb_undo_002  | NULL    | flags=0;id=4294967278;server_version=80004;space_version=1;     |         | InnoDB |
|  6 | fred/fred        | NULL    | flags=16417;id=1;server_version=80004;space_version=1;          |         | InnoDB |
|  8 | fred/t1          | NULL    | flags=16417;id=3;server_version=80004;space_version=1;          |         | InnoDB |
|  9 | fred/test_json   | NULL    | flags=16417;id=4;server_version=80004;space_version=1;          |         | InnoDB |
+----+------------------+---------+-----------------------------------------------------------------+---------+--------+
8 rows in set (0.00 sec)

Why DD protected tables should stay hidden

At MySQL we decided that we won’t expose those tables because nobody should write in those tables as they are very sensitive and could break the full system.

These tables must also be protected against DDL as someone with sufficient privileges could change their definition, which would be disastrous, since the server code accesses the tables and expects a certain definition.

We also don’t want that people start writing tools around those tables that would reduce our freedom to modify those tables (structure, names) when we need it. If we expose those tables and allow queries directly against them, the DD tables will  become a de facto interface that the users will expect to be stable and limit our changes. We provide all needed commands and interfaces for the DD, there is no need for hacks and we are always happy to receive feature requests.

Data Dictionary is an amazing addition that opens the door for many future improvements on how MySQL deals with DDLs. We don’t want to slow down that innovation because we have constraints in changing our internal structure of the DD.

Недавно мы обновили свои сервера с MySQL 5.7 на 8.0.

Оставим за рамками этой статьи зачем и какие новые плюшки появились в MySQL 8.0, а вместо этого расскажем о том, с какими сложностями мы столкнулись в процессе обновления.

Во-первых, перед обновлением стоит посмотреть на список изменений и поправить свой конфиг-файл.

Как минимум, удалены следующие параметры:

innodb_file_format, innodb_file_format_check, innodb_file_format_max,innodb_large_prefix
query_cache_limit, query_cache_min_res_unit, query_cache_size, query_cache_type, query_cache_wlock_invalidate.

В параметре sql_mode удалён в частности NO_AUTO_CREATE_USER — что особенно важно, т.к. в MySQL 5.7 он был включен по-умолчанию.

Инструкция по in-place upgrade есть у Percona. И в общем случае можно следовать ей, однако нам удалось так обновить только один кластер, у остальных попытка такого обновления заканчивалась неудачно с подобной ошибкой:

2019-06-22T05:04:18.510888Z 1 [ERROR] [MY-011014] [Server] Found partially upgraded DD. Aborting upgrade and deleting all DD tables. Start the upgrade process again.
2019-06-22T05:04:23.115018Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-06-22T05:04:23.115655Z 0 [ERROR] [MY-010119] [Server] Aborting

Поэтому остальные кластеры мы обновляли путем поднятия нового пустого инстанса и восстановления дампа БД с предыдущей версии.

Чтобы сделать такое, во-первых, потребуется дамп БД. И тут поджидает опасность #1 — сделанный стандартным образом дамп:

mysqldump -u root -p --hex-blob --default-character-set=utf8mb4 --all-databases --triggers --routines --events > dump.sql

не восстанавливается, выдавая ошибку:

ERROR 3554 (HY000) at line 15915: Access to system table 'mysql.innodb_index_stats' is rejected.

Описание есть в багтрекере MySQL (со статусом Not a bug :), там же есть и совет как делать дамп, чтобы его таки можно было восстановить:

mysqldump -u root -p --hex-blob --default-character-set=utf8mb4 --all-databases --triggers --routines --events --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats > dump.sql

Но при попытке использовать такой дамп, если в нём присутствовали триггеры (а у нас они были), может поджидать опасность #2, в виде вот такой ошибки:

ERROR 1231 (42000) at line 54: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

Причина этого в том, как MySQL использует sql_mode для триггеров, а именно: MySQL сохраняет значение sql_mode для триггера в момент его создания и выполняет его потом всегда с этим значением. И соответственно сохраняет это значение в дамп.

Описание этого в справке:

dev.mysql.com/doc/refman/8.0/en/create-trigger.html
MySQL stores the sql_mode system variable setting in effect when a trigger is created, and always executes the trigger body with this setting in force, regardless of the current server SQL mode when the trigger begins executing.

Что же нам делать? Мы просто с помощью sed вырезали NO_AUTO_CREATE_USER из готового дампа. Подобной командой:

sed "s/50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER/50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO/g" dump.sql > dump2.sql

После этого дамп успешно восстанавливается, но нас поджидает опасность #3 (вполне ожидаемая правда) — системные таблицы восстановлены в состоянии от версии 5.7 и в логах у нас следующие ошибки:

[ERROR] [MY-013143] [Server] Column count of mysql.user is wrong. Expected 51, found 45. The table is probably corrupted

По опыту работы с прошлыми версиями это лечится запуском mysql_upgrade — но начиная с версии 8.0.16 — это не работает, т.к. mysql_upgrade объявлен deprecated и ничего не делает.

Теперь, чтобы вызвать обновление системных таблиц, необходимо запустить MySQL с опцией upgrade=FORCE.

На свежей ubuntu это можно сделать следующим образом:

systemctl set-environment MYSQLD_OPTS="--upgrade=FORCE"

После чего перезапустить MySQL. Ну и после успешного обновления удалить её:

systemctl unset-environment MYSQLD_OPTS