Bug #114340 wbcopytables only copies 12 rows when using --table-where option
Submitted: 13 Mar 2024 20:52 Modified: 3 Apr 2024 11:58
Reporter: Lefteris Goumas Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Workbench Severity:S1 (Critical)
Version:8.0.36 OS:Linux
Assigned to: CPU Architecture:Any
Tags: table-where, wbcopytables

[13 Mar 2024 20:52] Lefteris Goumas 
Description:
I use wbcopytables on Linux server with Workbench 8.0.36, using the option --table-where like below:
test=scheme
PAYMENT, PAYMENT1 = 2 identical tables
PAYMENT = PRIMARY KEY

/usr/bin/wbcopytables --mysql-source="root@localhost:3306" --source-password="password" --target="root@localhost:3306" --target-password="password" --table-where '`test`' '`PAYMENT`' '`test`' '`PAYMENT1`' '`PAYMENT`' '`PAYMENT`' '*'  '1=1' --log-level=debug3

The output is:
22:41:52 [INF][      copytable]: Logger set to level 'debug3'. '1111111'
22:41:52 [INF][      copytable]: Connecting to MySQL server at localhost:3306 with user root
22:41:52 [INF][      copytable]: Connection to MySQL opened
22:41:52 [DB1][      copytable]: Detected server version=8.0.36
22:41:52 [DB1][      copytable]: Detected max_allowed_packet=67108864
22:41:52 [DB1][      copytable]: Retrieving trigger list
22:41:52 [INF][      copytable]: Connecting to MySQL server at localhost:3306 with user root
22:41:52 [INF][      copytable]: Connection to MySQL opened
22:41:52 [INF][      copytable]: Connecting to MySQL server at localhost:3306 with user root
22:41:52 [INF][      copytable]: Connection to MySQL opened
22:41:52 [DB1][      copytable]: Detected server version=8.0.36
22:41:52 [DB1][      copytable]: Detected max_allowed_packet=67108864
22:41:52 [INF][      copytable]: Resuming copy of table `test`.`PAYMENT1`. Starting on record with keys:
22:41:52 [DB1][      copytable]: Executing query: SELECT * FROM `PAYMENT` WHERE (1=1) ORDER BY `PAYMENT` LIMIT 12
22:41:52 [DB2][      copytable]: Columns from source table `test`.`PAYMENT` (6):
22:41:52 [DB2][      copytable]: 1 - COMPANY: MYSQL_TYPE_SHORT
22:41:52 [DB2][      copytable]: 2 - SODTYPE: MYSQL_TYPE_SHORT
22:41:52 [DB2][      copytable]: 3 - PAYMENT: MYSQL_TYPE_SHORT
22:41:52 [DB2][      copytable]: 4 - CODE: MYSQL_TYPE_VAR_STRING
22:41:52 [DB2][      copytable]: 5 - NAME: MYSQL_TYPE_VAR_STRING
22:41:52 [DB2][      copytable]: 6 - INTERESTCRE: MYSQL_TYPE_DOUBLE
BEGIN:`test`.`PAYMENT1`:Copying 6 columns of 22 rows from table `test`.`PAYMENT`
22:41:52 [DB2][      copytable]: Columns from target table `test`.`PAYMENT1` (6) [skipped: 0]:
22:41:52 [DB2][      copytable]: 1 - COMPANY: MYSQL_TYPE_SHORT
22:41:52 [DB2][      copytable]: 2 - SODTYPE: MYSQL_TYPE_SHORT
22:41:52 [DB2][      copytable]: 3 - PAYMENT: MYSQL_TYPE_SHORT
22:41:52 [DB2][      copytable]: 4 - CODE: MYSQL_TYPE_STRING
22:41:52 [DB2][      copytable]: 5 - NAME: MYSQL_TYPE_STRING
22:41:52 [DB2][      copytable]: 6 - INTERESTCRE: MYSQL_TYPE_DOUBLE
ERROR:`test`.`PAYMENT1`:Failed copying 10 rows
22:41:52 [INF][      copytable]: Re-enabling triggers for schema 'test'
22:41:52 [DB1][      copytable]: Retrieving trigger definitions
22:41:52 [INF][      copytable]: No triggers found for 'test'
FINISHED

See line: Executing query: SELECT * FROM `PAYMENT` WHERE (1=1) ORDER BY `PAYMENT` LIMIT 12
and line: ERROR:`test`.`PAYMENT1`:Failed copying 10 rows
wbcopytables fails to copy rows after the first 12...

Now if I run the following but with --table option instead of --table-where:
/usr/bin/wbcopytables --mysql-source="root@localhost:3306" --source-password="password" --target="root@localhost:3306" --target-password="password" --table '`test`' '`PAYMENT`' '`test`' '`PAYMENT1`' '`PAYMENT`' '`PAYMENT`' '*' --log-level=debug3

The output is:
22:46:09 [INF][      copytable]: Logger set to level 'debug3'. '1111111'
22:46:09 [INF][      copytable]: Connecting to MySQL server at localhost:3306 with user root
22:46:09 [INF][      copytable]: Connection to MySQL opened
22:46:09 [DB1][      copytable]: Detected server version=8.0.36
22:46:09 [DB1][      copytable]: Detected max_allowed_packet=67108864
22:46:09 [DB1][      copytable]: Retrieving trigger list
22:46:09 [INF][      copytable]: Connecting to MySQL server at localhost:3306 with user root
22:46:09 [INF][      copytable]: Connection to MySQL opened
22:46:09 [INF][      copytable]: Connecting to MySQL server at localhost:3306 with user root
22:46:09 [INF][      copytable]: Connection to MySQL opened
22:46:09 [DB1][      copytable]: Detected server version=8.0.36
22:46:09 [DB1][      copytable]: Detected max_allowed_packet=67108864
22:46:09 [DB1][      copytable]: Executing query: SELECT * FROM `PAYMENT` ORDER BY `PAYMENT`
22:46:09 [DB2][      copytable]: Columns from source table `test`.`PAYMENT` (6):
22:46:09 [DB2][      copytable]: 1 - COMPANY: MYSQL_TYPE_SHORT
22:46:09 [DB2][      copytable]: 2 - SODTYPE: MYSQL_TYPE_SHORT
22:46:09 [DB2][      copytable]: 3 - PAYMENT: MYSQL_TYPE_SHORT
22:46:09 [DB2][      copytable]: 4 - CODE: MYSQL_TYPE_VAR_STRING
22:46:09 [DB2][      copytable]: 5 - NAME: MYSQL_TYPE_VAR_STRING
22:46:09 [DB2][      copytable]: 6 - INTERESTCRE: MYSQL_TYPE_DOUBLE
BEGIN:`test`.`PAYMENT1`:Copying 6 columns of 22 rows from table `test`.`PAYMENT`
22:46:09 [DB2][      copytable]: Columns from target table `test`.`PAYMENT1` (6) [skipped: 0]:
22:46:09 [DB2][      copytable]: 1 - COMPANY: MYSQL_TYPE_SHORT
22:46:09 [DB2][      copytable]: 2 - SODTYPE: MYSQL_TYPE_SHORT
22:46:09 [DB2][      copytable]: 3 - PAYMENT: MYSQL_TYPE_SHORT
22:46:09 [DB2][      copytable]: 4 - CODE: MYSQL_TYPE_STRING
22:46:09 [DB2][      copytable]: 5 - NAME: MYSQL_TYPE_STRING
22:46:09 [DB2][      copytable]: 6 - INTERESTCRE: MYSQL_TYPE_DOUBLE
END:`test`.`PAYMENT1`:Finished copying 22 rows in 0m00s
22:46:09 [INF][      copytable]: Re-enabling triggers for schema 'test'
22:46:09 [DB1][      copytable]: Retrieving trigger definitions
22:46:09 [INF][      copytable]: No triggers found for 'test'

As you can see the 2nd time with --table option all rows are copied.
For some reason when using --table-where wbcopytables applies LIMIT 12!!

How to repeat:
Create 2 clone tables and insert in the 1st one more than 12 records.
Try to copy data from 1st table to 2nd using wbcopytables with option --table-where.
It will succeed for the first 12 rows and will fail with the rest.
[1 Apr 2024 12:46] MySQL Verification Team 
Hello Lefteris Goumas,

Thank you for the bug report.
Could you please provide create table with insert statements?

Regards,
Ashwini Patil
[1 Apr 2024 14:40] Lefteris Goumas 
You can create 2 tables PAYMENT and PAYMENT1 with below sql:

CREATE TABLE `PAYMENT` (
  `PAYMENT` smallint NOT NULL,
  `CODE` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `NAME` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  PRIMARY KEY (`PAYMENT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `PAYMENT1` (
  `PAYMENT` smallint NOT NULL,
  `CODE` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `NAME` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  PRIMARY KEY (`PAYMENT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

And insert below records only for the 1st one in order to copy them to the 2nd:

INSERT INTO `PAYMENT` (`PAYMENT`,`CODE`,`NAME`) VALUES (1,'1','1'),(2,'2','2'),(3,'3','3'),(4,'4','4'),(5,'5','5'), 
(6,'6','6'),(7,'7','7'),(8,'8','8'),(9,'9','9'),(10,'10','10'),(11,'11','11'),(12,'12','12'),(13,'13','13'),(14,'14','14'),(15,'15','15');
[1 Apr 2024 14:41] Lefteris Goumas 
use `test` schema in order to fit with my example
[2 Apr 2024 13:04] MySQL Verification Team 
Hello Lefteris Goumas,

Thank you for the details.
I tried to reproduce your issue with workbench 8.0.36 and server 8.0.36 using tables given but I am not seeing any issues at my end. 
If you are using the same server to copy from one table to other, try using following simple sql statement.

insert into test.payment1
select * from test.payment where 1=1 order by payment;

Regards,
Ashwini Patil
[2 Apr 2024 13:05] MySQL Verification Team 
Logger set to level 'debug3'. '1111111'
18:18:46 [INF][      copytable]: Connecting to MySQL server at localhost:3306 with user root
18:18:46 [INF][      copytable]: Connection to MySQL opened
18:18:46 [DB1][      copytable]: Detected server version=8.0.36
18:18:46 [DB1][      copytable]: Detected max_allowed_packet=67108864
18:18:46 [DB1][      copytable]: Retrieving trigger list
18:18:46 [INF][      copytable]: Connecting to MySQL server at localhost:3306 with user root
18:18:46 [INF][      copytable]: Connection to MySQL opened
18:18:46 [INF][      copytable]: Connecting to MySQL server at localhost:3306 with user root
18:18:46 [INF][      copytable]: Connection to MySQL opened
18:18:46 [DB1][      copytable]: Detected server version=8.0.36
18:18:46 [DB1][      copytable]: Detected max_allowed_packet=67108864
18:18:46 [INF][      copytable]: Resuming copy of table `test`.`PAYMENT1`. Starting on record with keys:
18:18:46 [DB1][      copytable]: Executing query: SELECT * FROM `PAYMENT` WHERE ('1=1') ORDER BY `PAYMENT` LIMIT 2529429587392
18:18:46 [DB2][      copytable]: Columns from source table `test`.`PAYMENT` (3):
18:18:46 [DB2][      copytable]: 1 - PAYMENT: MYSQL_TYPE_SHORT
18:18:46 [DB2][      copytable]: 2 - CODE: MYSQL_TYPE_VAR_STRING
18:18:46 [DB2][      copytable]: 3 - NAME: MYSQL_TYPE_VAR_STRING
BEGIN:`test`.`PAYMENT1`:Copying 3 columns of 15 rows from table `test`.`PAYMENT`
18:18:46 [DB2][      copytable]: Columns from target table `test`.`PAYMENT1` (3) [skipped: 0]:
18:18:46 [DB2][      copytable]: 1 - PAYMENT: MYSQL_TYPE_SHORT
18:18:46 [DB2][      copytable]: 2 - CODE: MYSQL_TYPE_STRING
18:18:46 [DB2][      copytable]: 3 - NAME: MYSQL_TYPE_STRING
END:`test`.`PAYMENT1`:Finished copying 15 rows in 0m00s
18:18:46 [INF][      copytable]: Re-enabling triggers for schema 'test'
18:18:46 [DB1][      copytable]: Retrieving trigger definitions
18:18:46 [INF][      copytable]: No triggers found for 'test'
[2 Apr 2024 13:28] Lefteris Goumas 
Hello Ashwini Patil,

Can you confirm to me that you used a linux server?
I am reproducing this problem for about 2 years now in Centos, Ubuntu, Almalinux,
always with only copying the first 12 records!
I know I can copy from one to other table with a simple sql query but this is not want I want. I just use 2 tables to make it simpler for you to reproduce it.
My purpose is to copy data from sql server to mysql server.
Please let me know what LINUX OS you used in order to try it myself too.

Regards
Lefteris
[3 Apr 2024 11:44] MySQL Verification Team 
Hello Lefteris Goumas,

Can you confirm to me that you used a linux server?
Yes, I tried to reproduce this issue on Windows 11 and Ubuntu 22.04 through VM.

Regards,
Ashwini Patil
[3 Apr 2024 11:58] Lefteris Goumas 
Hello Ashwini Patil,

My log from wdcopytable shows:
22:41:52 [DB1][      copytable]: Executing query: SELECT * FROM `PAYMENT` WHERE (1=1) ORDER BY `PAYMENT` LIMIT 12

Yours shows:
18:18:46 [DB1][      copytable]: Executing query: SELECT * FROM `PAYMENT` WHERE ('1=1') ORDER BY `PAYMENT` LIMIT 2529429587392
 
Is there a paremeter anywhere for linux systems to define the limit number, because this is the cause of my problem,
and happens to me in any flavour of Linux I have tried , all the latest versions.

Regards
Lefteris
[8 Sep 2024 14:43] Alexandre º 
I am facing the same issue but on Windows.

Replicating Lefteris Goumas’ case here gives me a worse result.

[DB1][      copytable]: Executing query: SELECT `PAYMENT`, `CODE`, `NAME` FROM `PAYMENT` WHERE (`PAYMENT` >= 1) ORDER BY `PAYMENT` LIMIT 0

Both wbcopytables documentation and the --help option give any clue on how to define or omit the LIMIT clause.

What is the solution for this annoying bug?

In my case, the result is bizarre, and wbcopytables are turned into useless tools.