Bug #102096 clone plugin initialization fails due to sql_mode
Submitted: 31 Dec 2020 7:27 Modified: 12 Jan 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

[31 Dec 2020 7:27] Daniël van Eeden
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)
[31 Dec 2020 7:44] Daniël van Eeden
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.
[31 Dec 2020 12:06] MySQL Verification Team
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
[7 Jan 6:35] Daniël van Eeden
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.
[7 Jan 6:39] Daniël van Eeden
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)
[7 Jan 7:01] MySQL Verification Team
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)
[12 Jan 9:13] Erlend Dahl
Fixed in the upcoming 8.0.24 under the heading of

Bug#32199611 CLONE PLUGIN INSTALLATION FAILS IN PFS TABLE CREATION