manticoresearch CREATE TABLE new_table LIKE existing

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.}

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

Feb 02 '24 06:02 sanikolaev

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

Apr 24 '24 22:04 djklim87

Wait for review https://github.com/manticoresoftware/manticoresearch-buddy/pull/257

Apr 24 '24 22:04 djklim87

Done https://github.com/manticoresoftware/manticoresearch-buddy/commit/07968235e4190756e4b623a606a0c7f85b909ec3

Apr 26 '24 15:04 djklim87

Reopening to updated the docs.

Apr 29 '24 08:04 sanikolaev

Done in PR https://github.com/manticoresoftware/manticoresearch/pull/2123

May 02 '24 10:05 djklim87

Pls update the docs. More in the PR

May 02 '24 14:05 sanikolaev

Done

May 05 '24 20:05 djklim87

Reopening since:

it seems to be not working on dev2:

ysql> show version;
-------------
how 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                   |
-----------+--------------------------------+
 rows in set (0.00 sec)

ysql> create table name_copy like name with data;
-------------
reate table name_copy like name with data
-------------

RROR 1064 (42000): Failed to parse options

we need to cover the new functionality with tests

May 06 '24 03:05 sanikolaev

[ ] @PavelShilin89 we need to cover the new functionality with tests

May 06 '24 13:05 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

May 06 '24 18:05 djklim87

Fixed in 60c90e774faeb9d38b23ea8ab24919aad3bdd7c3

LGTM now.

May 07 '24 02:05 sanikolaev

@djklim87 a case was seen in which a table with data could not be copied. Please note that the folder itself is created.

root@68d3b57be072:/# echo "a > b" > /tmp/wf; mysql -P9306 -h0 -e "drop table if exists t; create table t(f text) wordforms='/tmp/wf'; insert into t(id) values(1); drop table if exists t2; create table t2 like t with data; show tables;"
+---------+------+
| Index   | Type |
+---------+------+
| new_tbl | rt   |
| t       | rt   |
| tbl     | rt   |
+---------+------+
root@68d3b57be072:/# ls -la /var/lib/manticore
total 48
drwxr-xr-x 1 manticore manticore 4096 May  7 16:42 .
drwxr-xr-x 1 root      root      4096 May  6 20:25 ..
drwx------ 2 root      root      4096 May  7 16:42 binlog
-rw-r--r-- 1 root      root         6 May  7 13:00 exc.txt
-rw------- 1 root      root       142 May  7 16:42 manticore.json
drwx------ 2 root      root      4096 May  7 13:04 new_tbl
-rw------- 1 root      root         0 May  7 08:17 state.sql
-rw-r--r-- 1 root      root         8 May  7 13:00 stop.txt
drwx------ 2 root      root      4096 May  7 16:42 t
drwx------ 2 root      root      4096 May  7 16:42 t2
drwx------ 2 root      root      4096 May  7 13:01 tbl
-rw-r--r-- 1 root      root         6 May  7 13:00 wordforms1.txt
root@68d3b57be072:/# ls -la /var/lib/manticore/t2
total 8
drwx------ 2 root      root      4096 May  7 16:42 .
drwxr-xr-x 1 manticore manticore 4096 May  7 16:42 ..

May 07 '24 20:05 PavelShilin89

case was seen in which a table with data could not be copied

You probably meant the case of a table without data. Reassigned this issue to Klim.

May 08 '24 08:05 sanikolaev

a case was seen in which a table with data could not be copied. Please note that the folder itself is created.

at the moment on the initial table without data everything works. This case has a different problem, @djklim87 and I have already discussed it.

May 08 '24 08:05 PavelShilin89

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                   |
+-----------+--------------------------------+

May 08 '24 11:05 sanikolaev

Blocked by https://github.com/manticoresoftware/manticoresearch/issues/2154

May 08 '24 22:05 djklim87

Blocked by https://github.com/manticoresoftware/manticoresearch/issues/2154

Unblocked.

May 10 '24 16:05 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

May 10 '24 16:05 sanikolaev

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

May 10 '24 16:05 djklim87

Waiting for review https://github.com/manticoresoftware/manticoresearch-buddy/pull/285

May 13 '24 12:05 djklim87

I left my comments, all is good, just need to fix approach how we get searchd data dir: https://github.com/manticoresoftware/manticoresearch-buddy/pull/285#pullrequestreview-2052611752

May 13 '24 12:05 donhardman

I answered to your suggestions. Review again, please

May 13 '24 15:05 djklim87

Done in https://github.com/manticoresoftware/manticoresearch-buddy/commit/3508b32b3ec7f3bd2785a26f902e68defafea127

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

May 13 '24 16:05 djklim87

Reopening to:

confirm it works fine
add tests

May 14 '24 11:05 sanikolaev

It still blocked by https://github.com/manticoresoftware/manticoresearch/issues/2154

May 14 '24 15:05 djklim87

It still blocked by https://github.com/manticoresoftware/manticoresearch/issues/2154

Unblocked now.

May 20 '24 03:05 sanikolaev

I confirm that now everything works fine, so @PavelShilin89 can continue writing tests

# echo "a > b" > /tmp/wf;
# mysql -h0 -P9306 -e "create table wftest(f text) wordforms='/tmp/wf'"
# mysql -h0 -P9306 -e "insert into wftest(id) values(1)"
# mysql -h0 -P9306 -e "CREATE TABLE wftest2 LIKE wftest with data"
# mysql -h0 -P9306 -e "select * from wftest2"
+------+------+
| id   | f    |
+------+------+
|    1 |      |
+------+------+
# mysql -h0 -P9306 -e "CREATE TABLE wftest3 LIKE wftest2 with data"
# mysql -h0 -P9306 -e "select * from wftest3"
+------+------+
| id   | f    |
+------+------+
|    1 |      |
+------+------+
root@b9ea799596c0:/workdir# mysql -h0 -P9306 -e "show create table wftest3"
+---------+---------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                      |
+---------+---------------------------------------------------------------------------------------------------+
| wftest3 | CREATE TABLE wftest3 (
id bigint,
f text
) wordforms='/var/lib/manticore/wftest3/wordforms_0.txt' |
+---------+---------------------------------------------------------------------------------------------------+

Jul 15 '24 22:07 djklim87

Done in https://github.com/manticoresoftware/manticoresearch/pull/2428

Jul 21 '24 21:07 PavelShilin89

manticoresearch
manticoresearch copied to clipboard

CREATE TABLE new_table LIKE existing_table WITH DATA

Checklist:

manticoresearch manticoresearch copied to clipboard

CREATE TABLE new_table LIKE existing_table WITH DATA

Checklist:

manticoresearch
manticoresearch copied to clipboard