On Mon, 2003-10-27 at 22:01, Dathan Vance Pattishall wrote:
> -->-----Original Message-----
> -->From: Guilhem Bichot [mailto:[EMAIL PROTECTED]
> -->Sent: Monday, October 27, 2003 10:55 AM
> -->To: [EMAIL PROTECTED]
> -->Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
> -->Subject: RE: reproducible error 17
> -->
> -->Hi,
> -->
> -->Are the master and slave located on two different machines, or on one
> -->single machine?
>
>
> The master and slave are located on 2 different machines.
>
> -->Are you using symlinks to place tables or databases at other
> locations
> -->than the datadir?
> Yes, in the db directory under the datadir. I have tables which are one
> name symlinked to the tables which where altered. These fake tables or
> tables which are synonyms to the src tables did not have any actions
> performed on them at this moment in time.
>
> For example
>
> recent_members.frm -> /var/lib/mysql/ffadult_recent/recentmembers.frm
> recent_members.MYI -> /var/lib/mysql/ffadult_recent/recentmembers.MYI
> recent_members.MYD -> /var/lib/mysql/ffadult_recent/recentmembers.MYD
>
> The tables altered where recentmembers(_*)
I have run some tests and what triggers the problems is this symlinking.
If I understood you correctly, your setup is analogous to mine which is:
- the datadir of my MySQL server is /m/data/4/1/
- in this I have a database called test: /m/data/4/1/test/
- I have this in /m/data/4/1/test/ :
lrwxrwxrwx 1 guilhem qq 24 Oct 27 23:25 tbl_.frm ->
/m/data/4/1/test/tbl.frm
-rw-rw---- 1 guilhem qq 8620 Oct 27 23:30 tbl.frm
lrwxrwxrwx 1 guilhem qq 24 Oct 27 23:26 tbl_.MYD ->
/m/data/4/1/test/tbl.MYD
-rw-rw---- 1 guilhem qq 84 Oct 27 23:30 tbl.MYD
lrwxrwxrwx 1 guilhem qq 24 Oct 27 23:26 tbl_.MYI ->
/m/data/4/1/test/tbl.MYI
-rw-rw---- 1 guilhem qq 1024 Oct 27 23:30 tbl.MYI
("tbl_" is a "synonym" for the real "tbl" table).
On my master (no replication) I got:
MASTER> flush tables;
Query OK, 0 rows affected (0.00 sec)
MASTER> desc tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
| b | int(11) | YES | | NULL | |
| c | int(11) | YES | | NULL | |
| d | int(11) | YES | | NULL | |
| e | int(11) | YES | | NULL | |
| f | int(11) | YES | | NULL | |
| g | int(11) | YES | | NULL | |
| h | int(11) | YES | | NULL | |
| i | int(11) | YES | | NULL | |
| k | int(11) | YES | | NULL | |
| l | int(11) | YES | | NULL | |
| m | int(11) | YES | | NULL | |
| n | int(11) | YES | | NULL | |
| o | int(11) | YES | | NULL | |
| p | int(11) | YES | | NULL | |
| q | int(11) | YES | | NULL | |
| r | int(11) | YES | | NULL | |
| s | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
18 rows in set (0.00 sec)
MASTER> flush tables;
Query OK, 0 rows affected (0.00 sec)
MASTER> alter table tbl drop s;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
This show that altering the real table is ok.
Now we use the synonym (the symbolic link) instead:
MASTER> desc tbl_;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
| b | int(11) | YES | | NULL | |
| c | int(11) | YES | | NULL | |
| d | int(11) | YES | | NULL | |
| e | int(11) | YES | | NULL | |
| f | int(11) | YES | | NULL | |
| g | int(11) | YES | | NULL | |
| h | int(11) | YES | | NULL | |
| i | int(11) | YES | | NULL | |
| k | int(11) | YES | | NULL | |
| l | int(11) | YES | | NULL | |
| m | int(11) | YES | | NULL | |
| n | int(11) | YES | | NULL | |
| o | int(11) | YES | | NULL | |
| p | int(11) | YES | | NULL | |
| q | int(11) | YES | | NULL | |
| r | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
17 rows in set (0.00 sec)
MASTER> alter table tbl_ drop r;
ERROR 25: Can't create symlink './test/#sql-1aa7_4.MYI' pointing at
'/m/data/4/1/test/#sql-1aa7_4.MYI' (Error 17)
MASTER> alter table tbl_ drop r;
ERROR 25: Can't create symlink './test/#sql-1aa7_4.MYI' pointing at
'/m/data/4/1/test/#sql-1aa7_4.MYI' (Error 17)
This is the same error as you: it's impossible to alter the synonym.
What happens is that "ALTER TABLE tbl_ ...":
- Creates temporary files (named #sql*) in the same place as the tbl_
files, which is ./ (which is also /m/data/4/1/test btw). Those temporary
files are the result of the ALTER TABLE (the new frm/MYD/MYI files).
- Notices that the tbl_ files are in fact symlinks to files in
/m/data/4/1/test; so it wants to move the temporary files from ./ to
/m/data/4/1/test (ALTER wants, in some way, to preserve the symbolic
links). As the 2 directories are the same, the move fails.
Note that this error occured often on my system but not always,
depending on which table was accessed first (see below), which could
explain why you got it on slave only (on slave all queries are performed
by one unique thread).
Here is another problem:
MASTER> flush tables;
Query OK, 0 rows affected (0.00 sec)
MASTER> alter table tbl_ add t int;
ERROR 25: Can't create symlink './test/#sql-1aa7_4.MYI' pointing at
'/m/data/4/1/test/#sql-1aa7_4.MYI' (Error 17)
MASTER> alter table tbl add t int;
ERROR 25: Can't create symlink './test/#sql-1aa7_4.MYI' pointing at
'/m/data/4/1/test/#sql-1aa7_4.MYI' (Error 17)
you see, now even the alter on the real table fails.
This is because in the MySQL table cache, the real and synonym are
considered one same table; as MySQL first opened the synonym, it also
uses the synonym when the real table is invoked ("it's the same table"),
leading to the error for the real table too. If now I empty the table
cache:
MASTER> flush tables;
Query OK, 0 rows affected (0.00 sec)
MASTER> alter table tbl add t int;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
it works.
So the conclusion is: unfortunately, the symlink support in MySQL was
not designed for "synonyming", as far as DDL (Data Definition Language -
CREATE TABLE / DROP TABLE / ALTER TABLE) commands are concerned. It was
designed with the thought that symlinks are to be used to point to a
*different* directory (another partition where there is more room, or
another device to balance disk load). For DDL commands MySQL always
expects a table to exist only once, i.e. to have only one name. Putting,
in the database directory, a symlink and the real table means giving 2
names to one table...
I will add a note about this into our manual soon. I understand this is
is an inconvenience for you; you will be safe if you always do the DDL
commands (ALTER TABLE, in your case) on the real table. It's ok to do
DML commands (INSERT/DELETE/UPDATE/LOADDATA, which fortunately occur
much more often than ALTER TABLE normally) on both tables indifferently.
Guilhem
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
