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

table doesn't exist in engine after ALTER other tables with CONSTRAINTs

    Details

      Description

      After ALTER of several tables (few of them had CONSTRAINs to a problematic table) a table 'vanished' from server.

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

      it is however visible in table inventory (show tables).

      During ALTER statements foreign_key_checks was set to OFF.

      If I disable foreign_key_checks table appears again and it is visible after enabling foreign_key_checks. It seems this affect 'global' variable. After the server restart the table is 'invisible' again (with the same error as described above).

      MariaDB [DBname]> select count(*) from transaction;
      ERROR 1932 (42S02): Table 'DBname.transaction' doesn't exist in engine
      MariaDB [DBname]> set foreign_key_checks=0;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [DBname]> select count(*) from transaction;
      +-----------+
      | count(*)  |
      +-----------+
      | 244715434 |
      +-----------+
      1 row in set (2 min 0.19 sec)
       
      MariaDB [DBname]> set foreign_key_checks=1;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [DBname]> select count(*) from transaction;
      +-----------+
      | count(*)  |
      +-----------+
      | 244715434 |
      +-----------+
      1 row in set (2 min 1.28 sec)
       
      MariaDB [DBname]> Bye
      [root@host ~]# mysql DBname
       
       
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 151
      Server version: 10.2.11-MariaDB-log MariaDB Server
       
      Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [DBname]> select count(*) from transaction;
      +-----------+
      | count(*)  |
      +-----------+
      | 244715435 |
      +-----------+
      1 row in set (1 min 59.65 sec)
       
      MariaDB [DBname]> 
      
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                marko Marko Mäkelä
                Reporter:
                bzwitt Benjamin Zwittnig
              • Votes:
                1 Vote for this issue
                Watchers:
                8 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: