pt-archiver doesn't work when ANSI_QUOTES is set in sql_mode
General
Escalation
General
Escalation
Description
Environment
None
AFFECTED CS IDs
CS0036290
relates to
Activity
Show:
Yunus Shaikh May 16, 2023 at 4:32 AM
was able to reproduce and provide a workaround
Reproduced and fix/workaround below :
#######################################################
# ERROR
#######################################################
[root@c7-242-ps57 ~]# pt-archiver --source h=127.0.0.1,P=3306,u=root,p='Secret1!',D=db1,t=t1,i=PRIMARY --dest h=192.168.122.242,P=3306,u=root,p='Secret1!',D=db2,t=t1 --where "i<3" --progress 10 --limit=10 --txn-size 10 --statistics --purge --retries=3
DBD::mysql::db selectrow_array failed: Unknown column '' in 'field list' [for Statement "SELECT CONCAT(/*!40100 @@session.character_set_connection, */ "")"] at /usr/bin/pt-archiver line 6361.
[root@c7-242-ps57 ~]# pt-archiver --version
pt-archiver 3.5.2#######################################################
WORKAROUND/SUGGESTED FIX :
Replace double quote(") with single quote(') and escape character backslash() on SELECT query at line 6359 of /usr/bin/pt-archiver.
#######################################################
my $sql = 'SELECT CONCAT(/*!40100 @@session.character_set_connection, */ "")';
To
my $sql = 'SELECT CONCAT(/*!40100 @@session.character_set_connection, */ \'\')';#######################################################
# Manually test the failing SELECT query and the workaround :
#######################################################
mysql [192.168.122.242:3306] {root} ((none)) > SELECT CONCAT(/*!40100 @@session.character_set_connection, */ "");
ERROR 1054 (42S22): Unknown column '' in 'field list'
mysql [192.168.122.242:3306] {root} ((none)) > SELECT CONCAT(/*!40100 @@session.character_set_connection, */ '');
+--------------------------------------------------+
| CONCAT( @@session.character_set_connection, '') |
+--------------------------------------------------+
| latin1 |
+--------------------------------------------------+
1 row in set (0.00 sec)
#######################################################
# SUCCESS : no more error in pt-archiver
#######################################################
[root@c7-242-ps57 ~]# pt-archiver --source h=127.0.0.1,P=3306,u=root,p='Secret1!',D=db1,t=t1,i=PRIMARY --dest h=192.168.122.242,P=3306,u=root,p='Secret1!',D=db2,t=t1 --where "i<3" --progress 10 --limit=10 --txn-size 10 --statistics --purge --retries=3
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
[...]
# A software update is available:
TIME ELAPSED COUNT
2023-05-16T11:05:44 0 0
2023-05-16T11:05:44 0 2
Started at 2023-05-16T11:05:44, ended at 2023-05-16T11:05:44
Source: D=db1,P=3306,h=127.0.0.1,i=PRIMARY,p=...,t=t1,u=root
Dest: D=db2,P=3306,h=192.168.122.242,i=PRIMARY,p=...,t=t1,u=root
SELECT 2
INSERT 2
DELETE 2
Action Count Time Pct
commit 2 0.0108 70.98
select 2 0.0015 10.13
inserting 2 0.0012 7.69
deleting 2 0.0009 6.17
other 0 0.0008 5.03
aristotle.po May 16, 2023 at 3:39 AM
Reproduced and fix/workaround :
WORKAROUND/SUGGESTED FIX : Replace double quote(") with escape character backslash() and single quote(') on SELECT query at line 6359 of /usr/bin/pt-archiver.
pt-archiver --version
pt-archiver 3.5.2
Having error adding here in JIRA the exact lines, perhaps due to special character issue.
Steps to reproduce.
add ANSI_QUOTES to sql_mode, run pt-archiver
$ ./pt-archiver --source h=127.0.0.1,P=5735,D=test,t=a --file=a --no-delete --where 1=1 --user=msandbox --password=msandbox DBD::mysql::db selectrow_array failed: Unknown column '' in 'field list' [for Statement "SELECT CONCAT(/*!40100 @@session.character_set_connection, */ "")"] at ./pt-archiver line 6361.