{question}
Why do I get an ERROR 1146 (42S02): Leaf Error (10.10.212.121:3307): Table 'db_8._MV_PROCESSLIST' doesn't exist in SingleStore v7.x when trying to create a new table from some information_schema views?
Example queries and error responses:
singlestore> create table test_mvprocesslist as select now(),* from information_schema.mv_processlist;
ERROR 1146 (42S02): Leaf Error (10.10.212.121:3307): Table 'db_8._MV_PROCESSLIST' doesn't exist
singlestore> create table mvprocesslist like information_schema.mv_processlist;
ERROR 1347 (HY000): 'information_schema.mv_processlist' is not a BASE TABLE
{question}
{answer}
The issue you're experiencing is specific to SingleStore version 7.x and earlier. It arises when using create table...as select, create table...like and insert...select from some information_schema tables. The error indicates that the underlying table being accessed doesn't exist, which prevents the creation of a new table.
This issue has been fixed in SingleStore version 8.x.
Workaround:
While it's recommended to upgrade to SingleStore v8.x to resolve this issue completely, a workaround is available in version 7.x. You can use the LIMIT clause or create a reference table.
Here is an example using the LIMIT clause:
singlestore> create table test_mvprocesslist as select now(),* from information_schema.mv_processlist limit 5000000;
Query OK, 28 rows affected (0.13 sec)
This query will limit the number of rows selected for the new table.
Alternatively, you can create a reference table:
singlestore> create reference table test_mvprocesslist_ref as select now(),* from information_schema.mv_processlist;
Query OK, 28 rows affected (0.23 sec)
These workarounds allow you to create a new table from some information_schema tables in SingleStore v7.x.
Reference:
{answer}