CREATE TABLE new_table LIKE existing_table WITH DATA

[sanikolaev]

Manticore already supports CREATE TABLE new_table LIKE existing_table. Let's improve it, so it can copy a table with data by:

• Calling FREEZE against the existing table
• Copying the files
• Calling IMPORT TABLE
• Calling UNFREEZE

Test it well in terms of copying external files (wordforms, stopwords etc.) properly.

Checklist:

^{To be completed by the assignee. Check off tasks that have been completed or are not applicable.}

• Task estimated
• Specification created, reviewed and approved
• Implementation completed
• Tests developed
• Documentation updated
• Documentation proofread
• Changelog updated
• OpenAPI YAML updated and issue created to rebuild clients

[djklim87]

@sanikolaev JFY Import command performs copy itself. So we can skip 2nd step

[djklim87]

Wait for review manticoresoftware/manticoresearch-buddy#257

[djklim87]

Done manticoresoftware/manticoresearch-buddy@0796823

[sanikolaev]

Reopening to updated the docs.

[djklim87]

Done in PR #2123

[sanikolaev]

Pls update the docs. More in the PR

[djklim87]

Done

[sanikolaev]

Reopening since:

• it seems to be not working on dev2:

  mysql> show version;
  --------------
  show version
  --------------
  
  +-----------+--------------------------------+
  | Component | Version                        |
  +-----------+--------------------------------+
  | Daemon    | 6.2.13 694cbe53d@24050319 dev  |
  | Columnar  | columnar 2.2.5 0c18998@240424  |
  | Secondary | secondary 2.2.5 0c18998@240424 |
  | KNN       | knn 2.2.5 0c18998@240424       |
  | Buddy     | buddy v2.3.7                   |
  +-----------+--------------------------------+
  5 rows in set (0.00 sec)
  
  mysql> create table name_copy like name with data;
  --------------
  create table name_copy like name with data
  --------------
  
  ERROR 1064 (42000): Failed to parse options
  

• we need to cover the new functionality with tests

[djklim87]

• @PavelShilin89 we need to cover the new functionality with tests

[djklim87]

Reopening since:

* it seems to be not working on dev2:
  ```
  mysql> show version;
  --------------
  show version
  --------------
  
  +-----------+--------------------------------+
  | Component | Version                        |
  +-----------+--------------------------------+
  | Daemon    | 6.2.13 694cbe53d@24050319 dev  |
  | Columnar  | columnar 2.2.5 0c18998@240424  |
  | Secondary | secondary 2.2.5 0c18998@240424 |
  | KNN       | knn 2.2.5 0c18998@240424       |
  | Buddy     | buddy v2.3.7                   |
  +-----------+--------------------------------+
  5 rows in set (0.00 sec)
  
  mysql> create table name_copy like name with data;
  --------------
  create table name_copy like name with data
  --------------
  
  ERROR 1064 (42000): Failed to parse options
  ```

* we need to cover the new functionality with tests

Fixed in 60c90e774faeb9d38b23ea8ab24919aad3bdd7c3

[sanikolaev]

Fixed in 60c90e774faeb9d38b23ea8ab24919aad3bdd7c3

LGTM now.

[sanikolaev]

at the moment on the initial table without data everything works

Not working for me on dev2:

snikolaev@dev2:~$ mysql -P9306 -h0 -e "drop table if exists t; create table t; drop table if exists t2; create table t2 like t with data; show table t2 status"
ERROR 1064 (42000) at line 1: SHOW TABLE STATUS requires an existing table

Version info:

snikolaev@dev2:~$ mysql -P9306 -h0 -e "show version"
+-----------+--------------------------------+
| Component | Version                        |
+-----------+--------------------------------+
| Daemon    | 6.2.13 a9b48600c@24050806 dev  |
| Columnar  | columnar 2.2.5 0c18998@240424  |
| Secondary | secondary 2.2.5 0c18998@240424 |
| KNN       | knn 2.2.5 0c18998@240424       |
| Buddy     | buddy v2.3.7                   |
+-----------+--------------------------------+

[djklim87]

Blocked by #2154

[sanikolaev]

Blocked by #2154

Unblocked.

[sanikolaev]

Copying an empty table is still not working for me on the latest dev version:

snikolaev@dev2:~$ mysql -P9306 -h0 -v
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1892
Server version: 6.2.13 db4442c8b@24051014 dev (columnar 2.2.5 0c18998@240424) (secondary 2.2.5 0c18998@240424) (knn 2.2.5 0c18998@240424) git branch master...origin/master

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Reading history-file /home/snikolaev/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists t; create table t(f text); drop table if exists t2; create table t2 like t with data; show table t2 settings;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
create table t(f text)
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
drop table if exists t2
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create table t2 like t with data
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
show table t2 settings
--------------

ERROR 1064 (42000): SHOW TABLE SETTINGS requires an existing table

[djklim87]

Yes, it's correct. I didn't release my fixes yet

[djklim87]

Waiting for review manticoresoftware/manticoresearch-buddy#285

[donhardman]

I left my comments, all is good, just need to fix approach how we get searchd data dir: manticoresoftware/manticoresearch-buddy#285 (review)

[djklim87]

I answered to your suggestions. Review again, please

[djklim87]

Done in manticoresoftware/manticoresearch-buddy@3508b32

Docs https://manual.manticoresearch.com/dev/Creating_a_table/Local_tables/Real-time_table#CREATE-TABLE-LIKE:

[sanikolaev]

Reopening to:

• confirm it works fine
• add tests

[djklim87]

It still blocked by #2154

[sanikolaev]

It still blocked by #2154

Unblocked now.