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.