Cannot dump Clustrix tables #87

utdrmac · 2017-12-05T17:24:31Z

I'm attempting to dump a Clustrix database. This speaks MySQL protocol as it is based off of NDB. mydumper is skipping all 100s of tables. I believe the issue is because SHOW CREATE TABLE does not list ENGINE=InnoDB even though 'SHOW TABLE STATUS' lists all tables as InnoDB.

mydumper -S /var/lib/mysql/mysql.sock -t 8 -c -G -R -v 3 -W -T "iofd.wantads,iofd.widgets" --lock-all-tables

Even --trx-consistency-only still skips these tables.

How can I force mydumper to dump all tables regardless of engine type?

The text was updated successfully, but these errors were encountered:

utdrmac · 2017-12-05T17:26:54Z

mydumper seems to think these tables are views. Which they are not.

[root@clx1-8 mysqldump]# mydumper -S /var/lib/mysql/mysql.sock -t 8 -c -G -R -v 3 -T "iofd.wantads,iofd.widgets" -k
** Message: Connected to a MySQL server

** (mydumper:119482): WARNING **: Executing in no-locks mode, snapshot will notbe consistent
** Message: Started dump at: 2017-12-05 09:25:46

** Message: Written master status
** Message: Written slave status
** Message: Thread 1 connected using MySQL connection ID 138100746
** Message: Thread 2 connected using MySQL connection ID 138101770
** Message: Thread 3 connected using MySQL connection ID 138102794
** Message: Thread 4 connected using MySQL connection ID 138103818
** Message: Thread 5 connected using MySQL connection ID 138104842
** Message: Thread 6 connected using MySQL connection ID 138105866
** Message: Thread 7 connected using MySQL connection ID 138106890
** Message: Thread 8 connected using MySQL connection ID 138107914
** Message: Thread 1 dumping view for `iofd`.`wantads`
** Message: Thread 2 dumping view for `iofd`.`widgets`
** Message: Thread 4 shutting down
** Message: Thread 5 shutting down
** Message: Thread 3 shutting down
** Message: Thread 7 shutting down
** Message: Thread 8 shutting down
** Message: Thread 6 shutting down

** (mydumper:119482): CRITICAL **: Error dumping schemas (ioffer_development.widgets): [8194] View not found: `ioffer_development`.`widgets`
** Message: Thread 2 shutting down

** (mydumper:119482): CRITICAL **: Error dumping schemas (ioffer_development.wantads): [8194] View not found: `ioffer_development`.`wantads`
** Message: Thread 1 shutting down
** Message: Finished dump at: 2017-12-05 09:25:46

utdrmac · 2017-12-06T20:13:04Z

I found the issue is here: https://github.com/maxbube/mydumper/blob/37aa06686dc9ca97abbb335f8bd4b354a9694d7c/mydumper.c#L1829

mydumper is looking at the "Comment" field to determine if the table is a view or not. In normal MySQL, this field is blank/empty string. But in Clustrix, this field is NULL and thus mydumper believes this normal InnoDB table is actually a view.

By commenting out the line above and the one below it, I was able to get mydumper working on Clustrix.

utdrmac · 2017-12-06T20:15:43Z

Clustrix:

mysql> CREATE VIEW foo AS SELECT * FROM user_to_joins;
Query OK, 0 rows affected (0.13 sec)

mysql> show table status like 'foo'\G
*************************** 1. row ***************************
           Name: foo
         Engine: InnoDB
        Version: NULL
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 0
   Index_length: 0
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: binary
       Checksum: NULL
 Create_options: NULL
        Comment: NULL
1 row in set (0.18 sec)

mysql> show create table foo\G
*************************** 1. row ***************************
       View: foo
Create View: CREATE VIEW `foo` (`id`, `club_id`, `inviter`, `email`, `user_id`, `club_invitation_id`, `invited`, `viewed`, `viewed_at`, `joined`) AS SELECT * FROM user_to_joins
1 row in set (0.00 sec)

utdrmac · 2017-12-06T20:17:50Z

MySQL 5.7

mysql> CREATE VIEW foo AS SELECT * FROM user_to_joins;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW TABLE STATUS LIKE 'foo'\G
*************************** 1. row ***************************
           Name: foo
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE foo\G
*************************** 1. row ***************************
                View: foo
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `foo` AS select `user_to_joins`.`id` AS `id`,`user_to_joins`.`club_id` AS `club_id`,`user_to_joins`.`inviter` AS `inviter`,`user_to_joins`.`email` AS `email`,`user_to_joins`.`user_id` AS `user_id`,`user_to_joins`.`club_invitation_id` AS `club_invitation_id`,`user_to_joins`.`invited` AS `invited`,`user_to_joins`.`viewed` AS `viewed`,`user_to_joins`.`viewed_at` AS `viewed_at`,`user_to_joins`.`joined` AS `joined` from `user_to_joins`
character_set_client: utf8
collation_connection: utf8_general_ci

utdrmac · 2017-12-06T20:18:42Z

mydumper should check BOTH 'Engine' and 'Comment' columns to determine if a table is a view or not.

maxbube · 2017-12-06T21:35:50Z

Hi @utdrmac thanks for the detailed report!!!, I will take a look on this.

maxbube · 2017-12-08T17:39:12Z

Hi @utdrmac,

So based on your comments in clustrix a view STS output is:

Engine: InnoDB
Comment: NULL

What about an Innodb table? Is it:

Engine: InnoDB
Comment: <empty>

utdrmac · 2017-12-08T18:42:10Z

On Clustrix:

Clustrix-mysql> CREATE TABLE table_foo (id int);
Query OK, 0 rows affected (0.10 sec)

Clustrix-mysql> CREATE VIEW view_foo AS SELECT * FROM table_foo;
Query OK, 0 rows affected (0.11 sec)

Clustrix-mysql> show table status\G
*************************** 1. row ***************************
           Name: table_foo
         Engine: InnoDB
        Version: NULL
     Row_format: Compact
           Rows: 1
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 0
   Index_length: 0
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: NULL
        Comment: NULL
*************************** 2. row ***************************
           Name: view_foo
         Engine: InnoDB
        Version: NULL
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 0
   Index_length: 0
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: binary
       Checksum: NULL
 Create_options: NULL
        Comment: NULL
2 rows in set (0.30 sec)

utdrmac · 2017-12-08T18:43:32Z

Percona Server 5.7

mysql> CREATE TABLE table_foo (id int);
Query OK, 0 rows affected (0.31 sec)

mysql> CREATE VIEW view_foo AS SELECT * FROM table_foo;
Query OK, 0 rows affected (0.12 sec)

mysql> show table status\G
*************************** 1. row ***************************
           Name: table_foo
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-12-08 10:42:35
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 2. row ***************************
           Name: view_foo
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
2 rows in set (0.08 sec)

davidducos · 2021-06-14T19:49:11Z

I asked @utdrmac and he was ok to close it, as he was able to workaround the issue.

rookie7799 · 2021-07-20T20:25:27Z

Percona Server 5.7

mysql> CREATE TABLE table_foo (id int);
Query OK, 0 rows affected (0.31 sec)

mysql> CREATE VIEW view_foo AS SELECT * FROM table_foo;
Query OK, 0 rows affected (0.12 sec)

mysql> show table status\G
*************************** 1. row ***************************
           Name: table_foo
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-12-08 10:42:35
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
*************************** 2. row ***************************
           Name: view_foo
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
2 rows in set (0.08 sec)

hey, we are having the same issue, how did you resolve it?

utdrmac · 2021-07-20T20:38:28Z

@rookie7799 You are using Clustrix and are trying to use mydumper? Fix is in comment #3 above.

rookie7799 · 2021-07-20T20:43:48Z

@rookie7799 You are using Clustrix and are trying to use mydumper? Fix is in comment #3 above.

Yep, and I saw that "fix" but I get "option parsing failed: Unknown option --defaults-file"

utdrmac · 2021-07-20T20:45:23Z

--defaults-file is a parameter for mysql client. Do you have that set in /etc/my.cnf or .my.cnf somewhere?

rookie7799 · 2021-07-20T23:18:02Z

--defaults-file is a parameter for mysql client. Do you have that set in /etc/my.cnf or .my.cnf somewhere?

--defaults-file is mysql client's parameter that you use to actually specify the my.cnf file - that's why I'm a bit confused here :)

utdrmac changed the title ~~Cannot dump tables with missing ENGINE~~ Cannot dump Clustrix tables Dec 6, 2017

maxbube added the enhancement label Feb 20, 2018

davidducos added this to the Release 0.10.7 milestone Apr 20, 2021

davidducos added wontfix and removed enhancement labels Jun 14, 2021

davidducos closed this as completed Jun 14, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Cannot dump Clustrix tables #87

Cannot dump Clustrix tables #87

utdrmac commented Dec 5, 2017 •

edited

utdrmac commented Dec 5, 2017 •

edited

utdrmac commented Dec 6, 2017

utdrmac commented Dec 6, 2017

utdrmac commented Dec 6, 2017

utdrmac commented Dec 6, 2017

maxbube commented Dec 6, 2017

maxbube commented Dec 8, 2017

utdrmac commented Dec 8, 2017

utdrmac commented Dec 8, 2017

davidducos commented Jun 14, 2021

rookie7799 commented Jul 20, 2021

utdrmac commented Jul 20, 2021

rookie7799 commented Jul 20, 2021

utdrmac commented Jul 20, 2021

rookie7799 commented Jul 20, 2021

Cannot dump Clustrix tables #87

Cannot dump Clustrix tables #87

Comments

utdrmac commented Dec 5, 2017 • edited

utdrmac commented Dec 5, 2017 • edited

utdrmac commented Dec 6, 2017

utdrmac commented Dec 6, 2017

utdrmac commented Dec 6, 2017

utdrmac commented Dec 6, 2017

maxbube commented Dec 6, 2017

maxbube commented Dec 8, 2017

utdrmac commented Dec 8, 2017

utdrmac commented Dec 8, 2017

davidducos commented Jun 14, 2021

rookie7799 commented Jul 20, 2021

utdrmac commented Jul 20, 2021

rookie7799 commented Jul 20, 2021

utdrmac commented Jul 20, 2021

rookie7799 commented Jul 20, 2021

utdrmac commented Dec 5, 2017 •

edited

utdrmac commented Dec 5, 2017 •

edited