fr88
commented
7 years ago No response in four days. Let me simplify my question.
I copied a bunch of isam files (.frm, .MYD and *.MYI) from a backup into my data directory. Based on past experience I expected no problems and there weren't any. Until I copied one other table into that directory. That one table, and only that one, is not accessible:
mysql> select count(*) from positions; ERROR 1146 (42S02): Table 'fr.positions' doesn't exist
It does exist! -rwxrwxrwx 1 fr fr 9011 Dec 4 2016 /var/lib/mysql/fr/positions.frm -rwxrwxrwx 1 fr fr 30744 Sep 12 19:17 /var/lib/mysql/fr/positions.MYD -rwxrwxrwx 1 fr fr 13312 Sep 12 19:17 /var/lib/mysql/fr/positions.MYI
Like, for example, this one. Same permissions, same owner, same group: -rwxrwxrwx 1 fr fr 9333 May 31 2014 /var/lib/mysql/fr/ids.frm -rwxrwxrwx 1 fr fr 383736 Sep 12 10:10 /var/lib/mysql/fr/ids.MYD -rwxrwxrwx 1 fr fr 33792 Sep 12 18:43 /var/lib/mysql/fr/ids.MYI
mysql> select count() from ids; +----------+ | count() | +----------+ | 1950 | +----------+ 1 row in set (0.00 sec)
myisamchk acknowledges the table 'positions': fr@hatchbox-2:~$ sudo myisamchk -o /var/lib/mysql/fr/positions
- recovering (with keycache) MyISAM-table '/var/lib/mysql/fr/positions' Data records: 854
mysql still doesn't: mysql> select count(*) from positions; ERROR 1146 (42S02): Table 'fr.positions' doesn't exist
There must be something beneath the surface. I am quite unfamiliar with the way installations are organized, initialization scripts and configuration files, which ones and where to look for.
Frederic
andrewnicols
Hi, I copied a bunch of isam files (frm, MYI, MYD) from a backup and they work just fine. One table was missing, though, on that backup. So I created a new identical table. It made an innodb table (frm, idb). I then found the original isam table on another backup, changed the name of the new inno files, adding an underscore to each name and copied the table (isam files) from that spare backup, as I had copied in all the other files. That isam table is not accessible, apparently because it has the same name as the innodb had before I put it out of the way with the underscore. These is next deleted ($ rm ...) and did a shutdown and restart.
ls -l /var/lib/mysql/fr/positions* -rwxrwxrwx 1 fr fr 9011 Dec 4 2016 /var/lib/mysql/fr/positions.frm -rwxrwxrwx 1 fr fr 16384 Aug 7 2012 /var/lib/mysql/fr/positions.MYI -rwxrwxrwx 1 fr fr 30744 Aug 7 2012 /var/lib/mysql/fr/positions.MYD (positions.frm and positions.idb have been deleted)
SELECT * FROM positions; (ERROR 1812 (HY000): Tablespace is missing for table
fr.positions.)SELECT * FROM positions; (ERROR 1146 (42S02): Table 'fr.positions' doesn't exist)
ALTER TABLE positions DROP TABLESPACE; (ERROR 1812 (HY000): Tablespace is missing for table
fr.positions)DROP TABLE positions; (ERROR 1051 (42S02): Unknown table 'fr.positions')
$ find / -name 'positions*.dat' (finds nothing)
Question: how can I release the table name postions to allow the isam backup to work? (Just that one gets tripped up by this tablespace thing. All the others work fine.) This is a personal one-user system that has no compliance constraints.
Frederic