ERROR 1932 (42S02): Table 'xyz' doesn't exist in engine

[Tom Madureira]

We're running a 2 pm node distributed setup (host 1: um/pm, host 2: pm) and a user noticed yesterday that a few tables are no longer available to be read with the subject error message. In reviewing some of the CS debug logs I only noticed a high memory utilization restart, but other than that nothing of relevance as far as I can tell. Most of the tables are just fine. The tables in question are relatively small and are rarely written to, especially at the time of restart. I also noticed they all happened to be non-CS (BASE TABLE/InnoDB).

I only receive the 'table doesn't exist in engine message' on pm1. I am still able to query and retrieve some of the data via pm2. Another observation, and we rarely use pm2 directly, is that from pm2 (mcsmysql) we don't see nearly the quantity of tables available as pm1, why is that? Any help here would be greatly appreciated as the goal would be to be able to restore the missing set where unfortunately we haven't made a backup yet. 

Also to note, based on mysql.err logs it appears 1 out of the 5 doesn't exist in engine tables appears to be corrupted. I've tried to query them all and this is the only one that I can get some relevant output on:

     2020-03-19 15:02:17 17 [ERROR] InnoDB: Table `xyz` is corrupted. Please drop the table and recreate.

CS Version: 1.2.5 r1

[Tom Madureira]

Interesting, so one table I just wanted to recreate and populate quickly for a user...

DROP TABLE xyz;

CREATE TABLE xyz (...) Engine=Columnstore 

   ERROR 1815 (HY000): Internal error: CAL0009: Internal create table error for xyz : table already exists (your schema is probably out-of-sync)

CREATE TABLE xyz (...) Engine=Columnstore COMMENT='SCHEMA SYNC ONLY'

Table was able to create and full data is present...

[drrtuy]

Greetings Tom,

I missed the point do you have combined installation with mariadb available at both nodes?

If so do you have async replication setup b/w MDB nodes?

Regards,

Roman

четверг, 19 марта 2020 г., 22:56:57 UTC+3 пользователь Tom Madureira написал:

[drrtuy]

I need the answers to my previous message to be able to comment on this.

четверг, 19 марта 2020 г., 23:30:45 UTC+3 пользователь Tom Madureira написал:

[Tom Madureira]

Roman, 

We're currently running CS 1.2.5 across two hosts, so yes,

mariadb@xxx:~$ mcsadmin getSystemStatus

WARNING: running on non Parent OAM Module, can't make configuration changes in this session.

         Access Console from 'pm1' if you need to make changes.

getsystemstatus   Fri Mar 20 07:24:29 2020

System columnstore-1

System and Module statuses

Component     Status                       Last Status Change

------------  --------------------------   ------------------------

System        ACTIVE                       Thu Mar 19 22:37:39 2020

Module pm1    ACTIVE                       Thu Mar 19 14:20:30 2020

Module pm2    ACTIVE                       Thu Mar 19 14:20:27 2020

Active Parent OAM Performance Module is 'pm1'

Primary Front-End MariaDB ColumnStore Module is 'pm1'

MariaDB ColumnStore Replication Feature is enabled

MariaDB ColumnStore set for Distributed Install

mariadb@xxx:~$ mcsadmin getProcessStatus

WARNING: running on non Parent OAM Module, can't make configuration changes in this session.

         Access Console from 'pm1' if you need to make changes.

getprocessstatus   Fri Mar 20 07:24:50 2020

MariaDB ColumnStore Process statuses

Process             Module    Status            Last Status Change        Process ID

------------------  ------    ---------------   ------------------------  ----------

ProcessMonitor      pm1       ACTIVE            Thu Mar 19 14:19:34 2020       10259

ProcessManager      pm1       ACTIVE            Thu Mar 19 14:19:40 2020       10426

DBRMControllerNode  pm1       ACTIVE            Thu Mar 19 14:20:09 2020       11475

ServerMonitor       pm1       ACTIVE            Thu Mar 19 14:20:11 2020       11576

DBRMWorkerNode      pm1       ACTIVE            Thu Mar 19 14:20:11 2020       11626

PrimProc            pm1       ACTIVE            Thu Mar 19 14:20:15 2020       11770

ExeMgr              pm1       ACTIVE            Thu Mar 19 14:20:19 2020       11977

WriteEngineServer   pm1       ACTIVE            Thu Mar 19 14:20:23 2020       12148

DDLProc             pm1       ACTIVE            Thu Mar 19 22:33:01 2020       14827

DMLProc             pm1       ACTIVE            Thu Mar 19 14:20:32 2020       12430

mysqld              pm1       ACTIVE            Thu Mar 19 14:20:04 2020       11297

ProcessMonitor      pm2       ACTIVE            Thu Mar 19 14:19:55 2020       22771

ProcessManager      pm2       HOT_STANDBY       Thu Mar 19 14:20:00 2020       22875

DBRMControllerNode  pm2       COLD_STANDBY      Thu Mar 19 14:20:08 2020

ServerMonitor       pm2       ACTIVE            Thu Mar 19 14:20:11 2020       23319

DBRMWorkerNode      pm2       ACTIVE            Thu Mar 19 14:20:13 2020       23377

PrimProc            pm2       ACTIVE            Thu Mar 19 14:20:17 2020       23394

ExeMgr              pm2       ACTIVE            Thu Mar 19 14:20:21 2020       23518

WriteEngineServer   pm2       ACTIVE            Thu Mar 19 22:37:30 2020       22143

DDLProc             pm2       COLD_STANDBY      Thu Mar 19 14:20:27 2020

DMLProc             pm2       COLD_STANDBY      Thu Mar 19 14:20:27 2020

mysqld              pm2       ACTIVE            Thu Mar 19 14:20:11 2020       23206

We're using the default installation configuration and curious to learn more about the replication side and what is being done (if you could share some more information here).

Thanks

[drrtuy]

The point is that MCS table consists out of two parts: MariaDB .frm description used by MDB to operate with the tabe, its meta-data and real data in ColumnStore itself. If you have two combined nodes there will be two MDB running so you need to sync .frm b/w them. To do so you need to configure normal async replication b/w MDB instances.

Regards,

Roman

пятница, 20 марта 2020 г., 14:25:51 UTC+3 пользователь Tom Madureira написал:

[Tom Madureira]

Roman - clear on the why pm2.mdb is not displaying all of the tables. Am I wrong in thinking that async used to be setup as part of the installation? one we have partial data there and two I vaguely recall seeing slave replication at some point in a previous install. 

Secondly, back to the original issue, 'table doesn't exist in engine'. Any ideas how that came about suddenly? And the drop/sync/add where data was present, how was that possible?

[Tom Madureira]

I answered my previous question and this is what I found - running enableMySQLReplication again enabled the replication which is now visible and not sure when that failed. table xyz doesn't exist in engine issue still present 

mcsadmin> enableMySQLReplication

enablemysqlreplication   Mon Mar 23 16:09:24 2020

MariaDB ColumnStore Replication Feature is already enabled

           Do you want to proceed: (y or n) [n]: y

Enter the 'User' Password or 'ssh' if configured with ssh-keys

           Please enter:

Enter the 'User' Password or 'ssh' if configured with ssh-keys

           Please enter: ssh

   Successful Enabling of MariaDB ColumnStore Replication

   MariaDB ColumnStore Replication Master Node is pm1

On Thursday, March 19, 2020 at 3:56:57 PM UTC-4, Tom Madureira wrote:

[drrtuy]

I think that at some point async replication stopped and the non-CS tables weren't replicated. Async replication is a bit fragile and should be monitored.

вторник, 24 марта 2020 г., 0:00:23 UTC+3 пользователь Tom Madureira написал: