Bug #109072 wbcopytables query limit bug when using --table-where
Submitted: 13 Nov 2022 18:45 Modified: 5 Jul 2023 12:48
Reporter: Lefteris Goumas Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Workbench: Migration Severity:S3 (Non-critical)
Version:8.0.22-8.0.31 OS:Ubuntu (22.04)
Assigned to: CPU Architecture:Any

[13 Nov 2022 18:45] Lefteris Goumas
Description:
Further analysis of Bug #108308:
I try to use wbcopytables on Ubuntu 22.04 server with Workbench 8.0.31, using the option --table-where like below:

wbcopytables --mysql-source=<mysql connstring> --source-rdbms-type=Mysql --target=<mysql connstring> --source-password=<password> --target-password=<password> --thread-count=1 
--table-where <source schema> <source table> <target schema> <target table> <source pk columns> <target pk columns> "*" "1=1" --log-level="debug3"

Among other log information, I also get the below:
[DB1][      copytable]: Executing query: SELECT * FROM <table> WHERE (1=1) ORDER BY <source pk columns>
When I get the above log all goes well.

Sometimes I get, instead, the below and the copy fails:
[DB1][      copytable]: Executing query: SELECT * FROM <table> WHERE (1=1) ORDER BY <source pk columns> LIMIT 0

I then tried to do the same thing to a Fedora 36 server, again with Workbench 8.0.31. The log from Fedora is always the below:
[DB1][      copytable]: Executing query: SELECT * FROM <table> WHERE (1=1) ORDER BY <source pk columns> LIMIT 139639627937645

My conclusion is that for a strange reason, specifically on the Ubuntu server, LIMIT is not applied always and when it is applied the value is 0.
So I found the below 3 possible cases:
            OS      LIMIT APPLIED        LIMIT VALUE      FREQUENCY      RESULT
1.UBUNTU 22.04                 NO                  -           ~80%          OK
2.UBUNTU 22.04                YES                  0           ~20%        FAIL
3.FEDORA 36                ALWAYS    139639627937645           100%          OK 

How to repeat:
Try wbcopytables using options --table-where and --log-level="debug3"
on an Ubuntu 22.04 server and on a Fedora 36 server with Workbench 8.0.31 installed in both of them.
Execute the command at least 10-15 times on Ubuntu to find out that occasionally LIMIT is applied with value=0, but usually is not applied.
Also execute the same command on a Fedora server to find out that LIMIT is always applied with value=139639627937645
[23 Dec 2022 12:48] MySQL Verification Team
Hello,

Thank you for the bug report.
As per the comment in Bug #108308, I could not connect to the server credentials provided. If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.  

Regards,
Ashwini Patil
[5 Jul 2023 12:31] MySQL Verification Team
Hello Lefteris Goumas,

Thank you for the details.
I could not repeat the issue on Ubuntu 22.04 with workbench 8.0.33. If you can provide more information, feel free to add it to this bug. Thanks.

Regards,
Ashwini Patil
[5 Jul 2023 12:46] Lefteris Goumas
Screenshot of problem

Attachment: Screenshot 2023-07-05 154504.jpg (image/jpeg, text), 422.66 KiB.

[5 Jul 2023 12:48] Lefteris Goumas
Hello Ashwini Patil

You can see in the screenshot what I mean.
Also you can ssh my test server To see for yourself.
You can use the info of previous private message to do it.
I have the same behaviour in every ubuntu 20.04 or 22.04 I have tried also with workbench 8.0.33.

Thank you
Lefteris