MySQL Bugs: #102096: clone plugin initialization fails due to sql

Bug #102096	clone plugin initialization fails due to sql_mode
Submitted:	31 Dec 2020 7:27	Modified:	12 Jan 2021 9:13
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Clone Plugin	Severity:	S2 (Serious)
Version:	8.0.21, 8.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This is similar to Bug #96281

Installing the clone plugin fails with an sql mode set to:
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION

This is likely to be related to the `NO_ZERO_DATE` sql mode and the `performance_schema.clone_progress.END_TIME` having a zero date as default.

How to repeat:

with a `tail -f /var/log/mysqld.log` in the background:

root@myserver1 [(none)]> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
2020-12-31T07:22:06.999125Z 35665 [ERROR] [MY-013272] [Clone] Plugin Clone reported: 'Client: PFS table creation failed.'
2020-12-31T07:22:06.999184Z 35665 [ERROR] [MY-010202] [Server] Plugin 'clone' init function returned error.
2020-12-31T07:22:06.999209Z 35665 [Note] [MY-010733] [Server] Shutting down plugin 'clone'
ERROR 1123 (HY000): Can't initialize function 'clone'; Plugin initialization function failed.
root@myserver1 [(none)]> SELECT @@session.sql_mode;
+----------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@myserver1 [(none)]> SET SESSION sql_mode='';
Query OK, 0 rows affected (0.00 sec)

root@myserver1 [(none)]> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.01 sec)

I don't think the session settings from who runs the plugin installation should matter. The code should probably override sql_mode, primary key requirements, explicit timestamp settings etc. when doing the installation to make sure this is always what the plugin expects and what is tested.

As said in the related bug: It would be good to report the actual error to the client, probably in addition to the generic error message.

Hello Daniël,

Thank you for the report and feedback.
I quickly tried to reproduce on 8.0.22 build with the mentioned sql_mode but still not seeing any issues. Is there anything I'm missing here? Please let me know. Thank you!

-- 8.0.22

rm -rf 102096/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/102096 --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/102096 --core-file --socket=/tmp/mysql_ushastry.sock  --port=3333 --log-error=$PWD/102096/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv=/tmp/ --sql-mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION' 2>&1 &

-
 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT @@session.sql_mode;
+----------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.04 sec)

Sincerely,
Umesh

mysql> SELECT @@session.sql_mode, @@session.sql_require_primary_key, @@session.explicit_defaults_for_timestamp, VERSION()\G
*************************** 1. row ***************************
                       @@session.sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION
        @@session.sql_require_primary_key: 1
@@session.explicit_defaults_for_timestamp: 0
                                VERSION(): 8.0.21
1 row in set (0.00 sec)

mysql> SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_NAME LIKE '%clone%';
Empty set (0.12 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
ERROR 1123 (HY000): Can't initialize function 'clone'; Plugin initialization function failed.
mysql> SET SESSION sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
ERROR 1123 (HY000): Can't initialize function 'clone'; Plugin initialization function failed.
mysql> SET SESSION sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
ERROR 1123 (HY000): Can't initialize function 'clone'; Plugin initialization function failed.
mysql> SET SESSION sql_require_primary_key=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
ERROR 1123 (HY000): Can't initialize function 'clone'; Plugin initialization function failed.
mysql> SET SESSION sql_mode='ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
ERROR 1123 (HY000): Can't initialize function 'clone'; Plugin initialization function failed.
mysql> SET SESSION sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (1.82 sec)

So here you can see that the sql_mode of the session is the issue. I still think this is due to NO_ZERO_DATE. In this case this is a bit deceiving as the TRADITIONAL sql_mode is used, which includes NO_ZERO_DATE.

Tested on a 8.0.22 instance with dbdeployer:

mysql> SELECT @@session.sql_mode, @@session.sql_require_primary_key, @@session.explicit_defaults_for_timestamp, VERSION()\G
*************************** 1. row ***************************
                       @@session.sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
        @@session.sql_require_primary_key: 0
@@session.explicit_defaults_for_timestamp: 1
                                VERSION(): 8.0.22
1 row in set (0.01 sec)

mysql> SET SESSION sql_require_primary_key=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION explicit_defaults_for_timestamp=OFF;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SET SESSION sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_NAME LIKE '%clone%';
Empty set (0.02 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
ERROR 1123 (HY000): Can't initialize function 'clone'; Plugin initialization function failed.
mysql> SET SESSION explicit_defaults_for_timestamp=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
ERROR 1123 (HY000): Can't initialize function 'clone'; Plugin initialization function failed.
mysql> SET SESSION sql_require_primary_key=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
ERROR 1123 (HY000): Can't initialize function 'clone'; Plugin initialization function failed.
mysql> SET SESSION sql_mode='NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
ERROR 1123 (HY000): Can't initialize function 'clone'; Plugin initialization function failed.
mysql> SET SESSION sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_NAME LIKE '%clone%';
+----------------+
| TABLE_NAME     |
+----------------+
| clone_progress |
| clone_status   |
+----------------+
2 rows in set (0.01 sec)

Thank you for the feedback.
Verified as described.

-

mysql> SET SESSION explicit_defaults_for_timestamp=OFF;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SET SESSION sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_NAME LIKE '%clone%';
Empty set (0.06 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
ERROR 1123 (HY000): Can't initialize function 'clone'; Plugin initialization function failed.
mysql> show errors;
+-------+------+---------------------------------------------------------------------------+
| Level | Code | Message                                                                   |
+-------+------+---------------------------------------------------------------------------+
| Error | 1123 | Can't initialize function 'clone'; Plugin initialization function failed. |
+-------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET SESSION explicit_defaults_for_timestamp=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql>  INSTALL PLUGIN clone SONAME 'mysql_clone.so';
ERROR 1123 (HY000): Can't initialize function 'clone'; Plugin initialization function failed.
mysql> SET SESSION sql_require_primary_key=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
ERROR 1123 (HY000): Can't initialize function 'clone'; Plugin initialization function failed.
mysql> SET SESSION sql_mode='NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
ERROR 1123 (HY000): Can't initialize function 'clone'; Plugin initialization function failed.
mysql> SET SESSION sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';
Query OK, 0 rows affected (4.10 sec)

mysql> SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_NAME LIKE '%clone%';
+----------------+
| TABLE_NAME     |
+----------------+
| clone_status   |
| clone_progress |
+----------------+
2 rows in set (0.01 sec)

mysql> show variables like '%version%';
+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| admin_tls_version        | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| immediate_server_version | 999999                        |
| innodb_version           | 8.0.22                        |
| original_server_version  | 999999                        |
| protocol_version         | 10                            |
| slave_type_conversions   |                               |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version                  | 8.0.22                        |
| version_comment          | MySQL Community Server - GPL  |
| version_compile_machine  | x86_64                        |
| version_compile_os       | Linux                         |
| version_compile_zlib     | 1.2.11                        |
+--------------------------+-------------------------------+
12 rows in set (0.01 sec)

Fixed in the upcoming 8.0.24 under the heading of

Bug#32199611 CLONE PLUGIN INSTALLATION FAILS IN PFS TABLE CREATION