Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23106

Unable to recognize/import partitioned tables from physical MySQL databases.

Details

    Description

      In MySQL 5.7:

      create table example (
      		 
      name varchar(20) NOT NULL,
      		 
      logblob blob NOT NULL,
      		 
      created datetime NOT NULL,
      		 
      primary key (name, created)
      		 
      ) engine=InnoDB default charset=utf8
      		 
      partition by hash( to_days(created))
      		 
      partitions 10;

      Stop server & physically copy datadir, or remove MySQL binaries & replace with MariaDB binaries.
      Start server.
      Try to access table:

      MariaDB [t38689]> select count(*) from example;
      		 
      ERROR 1932 (42S02): Table 't38689.example' doesn't exist in engine

      Try running mysql_upgrade:

      Repairing tables
      		 
      t38689.example
      		 
      Error    : Table 't38689.example' doesn't exist in engine
      		 
      status   : Operation failed

      Problem confirmed with tables partitioned by HASH, COLUMN, KEY, RANGE, and LIST.

      Attachments

        Issue Links

          is duplicated by

          Task - A task that needs to be done. MDEV-29253 Detect incompatible MySQL partition scheme and either convert them or report to user and in error log.

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31417 ASAN errors in ha_partition::create_handlers upon upgrading from MySQL 5.7 with partitioned tables

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik added a comment -

            is it native innodb partitioning? what's the content of the datadir?

            serg Sergei Golubchik added a comment - is it native innodb partitioning? what's the content of the datadir?
            elenst Elena Stepanova added a comment - - edited 

            -rw-r----- 1 elenst elenst     65 Feb 17 18:26 db.opt
            		 
            -rw-r----- 1 elenst elenst   8632 Jul  8 00:56 example.frm
            		 
            -rw-r----- 1 elenst elenst  98304 Jul  8 00:57 example#P#p0.ibd
            		 
            -rw-r----- 1 elenst elenst  98304 Jul  8 00:57 example#P#p1.ibd
            		 
            -rw-r----- 1 elenst elenst  98304 Jul  8 00:57 example#P#p2.ibd
            		 
            -rw-r----- 1 elenst elenst  98304 Jul  8 00:57 example#P#p3.ibd
            		 
            -rw-r----- 1 elenst elenst  98304 Jul  8 00:57 example#P#p4.ibd
            		 
            -rw-r----- 1 elenst elenst  98304 Jul  8 00:57 example#P#p5.ibd
            		 
            -rw-r----- 1 elenst elenst  98304 Jul  8 00:57 example#P#p6.ibd
            		 
            -rw-r----- 1 elenst elenst  98304 Jul  8 00:57 example#P#p7.ibd
            		 
            -rw-r----- 1 elenst elenst  98304 Jul  8 00:57 example#P#p8.ibd
            		 
            -rw-r----- 1 elenst elenst  98304 Jul  8 00:57 example#P#p9.ibd

            Yes, I assume it's native InnoDB partitioning. No .par file in the datadir.

            elenst Elena Stepanova added a comment - - edited -rw-r----- 1 elenst elenst 65 Feb 17 18:26 db.opt -rw-r----- 1 elenst elenst 8632 Jul 8 00:56 example.frm -rw-r----- 1 elenst elenst 98304 Jul 8 00:57 example#P#p0.ibd -rw-r----- 1 elenst elenst 98304 Jul 8 00:57 example#P#p1.ibd -rw-r----- 1 elenst elenst 98304 Jul 8 00:57 example#P#p2.ibd -rw-r----- 1 elenst elenst 98304 Jul 8 00:57 example#P#p3.ibd -rw-r----- 1 elenst elenst 98304 Jul 8 00:57 example#P#p4.ibd -rw-r----- 1 elenst elenst 98304 Jul 8 00:57 example#P#p5.ibd -rw-r----- 1 elenst elenst 98304 Jul 8 00:57 example#P#p6.ibd -rw-r----- 1 elenst elenst 98304 Jul 8 00:57 example#P#p7.ibd -rw-r----- 1 elenst elenst 98304 Jul 8 00:57 example#P#p8.ibd -rw-r----- 1 elenst elenst 98304 Jul 8 00:57 example#P#p9.ibd Yes, I assume it's native InnoDB partitioning. No .par file in the datadir.
            serg Sergei Golubchik added a comment -

            MariaDB does not support InnoDB native partitioning

            serg Sergei Golubchik added a comment - MariaDB does not support InnoDB native partitioning
            monty Michael Widenius added a comment -

            Reopened the bug as we now have a solution for this problem

            monty Michael Widenius added a comment - Reopened the bug as we now have a solution for this problem
            monty Michael Widenius added a comment -

            The problem with MySQL partitions compared to MariaDB partitions is two fold:

            • The .frm file is slightly different.
            • MySQL does not have a .par file , which in MariaDB contains the storage engines and the partition names.

            The MySQL InnoDB data on disk seams to be identical to MariaDB's, which means that in theory it is possible to 'instantly' convert a partitioned MySQL table to MariaDB.

            I have now been able to provide a fix for the above issues:

            • Add support for the MySQL .frm file format
            • Automatically create a .par file if it does not exists.
            • Verified that mariadb-upgrade creates the missing a .par files.
            monty Michael Widenius added a comment - The problem with MySQL partitions compared to MariaDB partitions is two fold: The .frm file is slightly different. MySQL does not have a .par file , which in MariaDB contains the storage engines and the partition names. The MySQL InnoDB data on disk seams to be identical to MariaDB's, which means that in theory it is possible to 'instantly' convert a partitioned MySQL table to MariaDB. I have now been able to provide a fix for the above issues: Add support for the MySQL .frm file format Automatically create a .par file if it does not exists. Verified that mariadb-upgrade creates the missing a .par files.
            monty Michael Widenius added a comment -

            Pushed to 10.6 tree

            monty Michael Widenius added a comment - Pushed to 10.6 tree

            People

              monty Michael Widenius
              juan.vera Juan
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.