0

I have a MySQL installation on my machine where I need to add a very large table that will max out existing capacity.

I've previously housed MySQL data on external drives but I'd prefer to keep as much as possible on my laptop hard drive rather than an external HD due to read/write concerns, necessitating a symbolic link vs just moving all data to the HD.

I referenced MySQL documentation to do this, so it seems like it should work but nothing I've done allows me to (first) create a table on this new database (would ultimately like to import a bulk export .sql file/table to this new DB).

Getting:

mysql> create table test(`test` varchar(8) NOT NULL);
    ERROR 1030 (HY000): Got error 168 - 'Unknown (generic) error from engine' from storage engine

Error log isn't showing much, just this warning:

chris@chris-X1C6:/var/log/mysql$ cat error.log
2024-02-26T21:36:16.697730Z 19 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

I've checked that symbolic links are turned on in MySQL 8, I've checked mysql is owner of all applicable directories, and that there's plenty of space in each location, but I still get this error 168. What am I missing? Running MySQL 8.0 on Ubuntu 22.04 if it matters.

Some related details:

chris@chris-X1C6:/var/log/mysql$ df -h
Filesystem      Size  Used Avail Use% Mounted on
tmpfs           1.6G  4.1M  1.6G   1% /run
/dev/nvme0n1p6  173G  153G   12G  93% /
tmpfs           7.7G   31M  7.7G   1% /dev/shm
tmpfs           5.0M  4.0K  5.0M   1% /run/lock
efivarfs        154K   52K   98K  35% /sys/firmware/efi/efivars
tmpfs           7.7G     0  7.7G   0% /run/qemu
/dev/nvme0n1p4   60G  769M   60G   2% /media/chris/Share
/dev/nvme0n1p1  256M   35M  222M  14% /boot/efi
/dev/sdc2       4.6T   36K  4.3T   1% /media/chris/F
/dev/sda1       932G  287G  646G  31% /media/chris/T7
/dev/sdb2       4.6T  4.3T  260G  95% /media/chris/E
tmpfs           1.6G   96K  1.6G   1% /run/user/1000


chris@chris-X1C6:/var/lib/mysql$ ll --ignore=binlog*
total 1790432
-rw-rw-rw-  1 mysql mysql      1709 Dec  5  2022  ca-key.pem
-rw-rw-rw-  1 mysql mysql      1112 Dec  5  2022  ca.pem
-rw-rw-rw-  1 mysql mysql      1705 Dec  5  2022  server-key.pem
-rw-rw-rw-  1 mysql mysql      1112 Dec  5  2022  server-cert.pem
-rw-rw-rw-  1 mysql mysql      1705 Dec  5  2022  client-key.pem
-rw-rw-rw-  1 mysql mysql      1112 Dec  5  2022  client-cert.pem
-rw-rw-rw-  1 mysql mysql      1705 Dec  5  2022  private_key.pem
-rw-rw-rw-  1 mysql mysql       452 Dec  5  2022  public_key.pem
drwxrwxrwx  2 mysql mysql      4096 Dec  5  2022  sys/
drwxrwxrwx  2 mysql mysql      4096 Dec 14  2022  fu/
drwxrwxrwx  2 mysql mysql      4096 Dec 21  2022  eq/
drwxrwxrwx  2 mysql mysql      4096 May  8  2023  performance_schema/
-rw-rw-rw-  1 mysql mysql         0 Jan 26 17:44  mysql.sock
-rw-rw-rw-  1 mysql mysql         0 Jan 26 17:44  mysql.pid
-rw-rw-rw-  1 mysql mysql         0 Jan 31 06:40  debian-5.7.flag
drwxrwxrwx  2 mysql mysql      4096 Jan 31 06:40  mysql/
-rw-rw-rw-  1 mysql mysql         6 Jan 31 06:40  mysql_upgrade_info
lrwxrwxrwx  1 root  root         14 Feb 13 22:31  F -> /media/chris/F/
drwxr-xr-x 83 root  root       4096 Feb 17 09:46  ../
-rw-rw-rw-  1 mysql mysql   8585216 Feb 20 17:00 '#ib_16384_1.dblwr'
lrwxrwxrwx  1 mysql mysql        23 Feb 20 19:27  op -> /media/chris/F/mysql/op/
-rw-r-----  1 mysql mysql        56 Feb 20 19:28  auto.cnf
-rw-r-----  1 mysql mysql      3272 Feb 21 11:22  ib_buffer_pool
drwxrwxrwx  2 mysql mysql      4096 Feb 22 14:31 '#innodb_temp'/
drwxrwxrwx  2 mysql mysql      4096 Feb 22 14:32 '#innodb_redo'/
-rw-r-----  1 mysql mysql         5 Feb 22 14:32  chris-X1C6.pid
-rw-r-----  1 mysql mysql  12582912 Feb 22 14:32  ibtmp1
-rw-rw-rw-  1 mysql mysql 855638016 Feb 22 15:31  undo_002
drwxrwxrwx  9 mysql mysql      4096 Feb 26 00:00  ./
-rw-rw-rw-  1 mysql mysql 838860800 Feb 26 13:37  undo_001
-rw-rw-rw-  1 mysql mysql  79691776 Feb 26 13:37  ibdata1
-rw-rw-rw-  1 mysql mysql    196608 Feb 26 13:37 '#ib_16384_0.dblwr'
-rw-rw-rw-  1 mysql mysql  37748736 Feb 26 13:37  mysql.ibd

chris@chris-X1C6:/var/lib/mysql$ ls -lad /media/chris/F/mysql/op /var/lib/mysql/op
drwxrwxrwx 2 mysql mysql 4096 Feb 20 18:34 /media/chris/F/mysql/op
lrwxrwxrwx 1 mysql mysql   23 Feb 20 19:27 /var/lib/mysql/op -> /media/chris/F/mysql/op
Improve this question
1

1 Answer 1

Reset to default
1

I've done this with InnoDB tables in MySQL v5.6. I created an empty table, shut down mysqld, moved the file to a filesystem with sufficient space, set a symbolic link to the moved file, and started mysqld again.

I had to make sure the directory where I moved the file was readable and writable by the user that mysqld ran under, and the directories in the path up to that place were readable and executable by the mysqld user. (might only need to be executable, I don't remember)

Another point I remember is that mysqld will perform ALTER TABLE actions (often creating a new file, copying rows to the new file, then swapping the filenames of the old and new files, then deleting the old file) using the filesystem holding the (large) table, but other operations on the table's data might not. Sorting or grouping that create temporary files (and perhaps also temp tables) will save to temporary files in mysqld's standard temp area, which is probably not the large filesystem where you moved this table's file.

My experiences above happened probably 8-10 years ago, so they could be out of date. I think modern versions of MySQL will still behave this way, but it's best to go by the current documentation and current experiences (if anyone else shares theirs).

Improve this answer
1
  • thanks for that. I didn't create the table before creating the symlink so something else to try. I'd also seen the DATA DIRECTORY param for use when creating the table which I wasn't aware of. Commented Feb 28, 2024 at 18:05

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.