reproducible error 17

reproducible error 17

am 24.10.2003 20:27:05 von Dathan Vance Pattishall

------=_NextPart_000_0057_01C39A21.C3BA4A70
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Error 'Can't create symlink './ffadult_recent/#sql-44b_e87.MYI' pointing
at '/var/lib/mysql/ff_recent/#sql-44b_e87.MYI' (Error 17)' on query
'ALTER TABLE recentmembers_8 add have_children tinyint unsigned not NULL
default 0'. Default database: 'ff_recent' | 0 | 346296822
| 369493795


Perror says
Error code 17: File exists

I have 2 alters submitted on the master in sequential order. On the
slave it executes in the same order but produces error 17. Somehow the
tempfile already exists, but when I go to the datadir this temp file is
not displayed in the directory.

Performing a slave stop slave start does not work.
Restarting the mysql server on the slave is the only method I found to
recover from the error.


Version of mySQL: 4.0.15
Kernel: Linux ef242.friendfinderinc.com 2.4.18-4smp #1 SMP Sun Apr 14
13:50:35 PDT 2002 i686 unknown
*PATCHED*


- Dathan Vance Pattishall
- Sr. Programmer and mySQL DBA for FriendFinder Inc.
- http://friendfinder.com/go/p40688


------=_NextPart_000_0057_01C39A21.C3BA4A70--

RE: reproducible error 17

am 24.10.2003 21:21:04 von Dathan Vance Pattishall

*************************** 1. row ***************************
Master_Host: 10.2.12.224
Master_User: rep
Master_Port: 3306
Connect_retry: 60
Master_Log_File: ef224-bin.020
Read_Master_Log_Pos: 409223612
Relay_Log_File: ef242-relay-bin.016
Relay_Log_Pos: 330063332
Relay_Master_Log_File: ef224-bin.020
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_do_db:=20
Replicate_ignore_db:=20
Last_errno: 25
Last_error: Error 'Can't create symlink
'./ff_recent/#sql-17f7_c.MYI' pointing at
'/var/lib/mysql/ff_recent/#sql-17f7_c.MYI' (Error 17)' on query 'ALTER
TABLE recentmembers_20 add have_children tinyint unsigned not NULL
default 0'. Default database: 'ff_recent'
Skip_counter: 0
Exec_master_log_pos: 348632736
Relay_log_space: 390689457

- Dathan Vance Pattishall
=A0 - Sr. Programmer and mySQL DBA for FriendFinder Inc.
=A0 - http://friendfinder.com/go/p40688


-->-----Original Message-----
-->From: Dathan Vance Pattishall [mailto:dathan@friendfinderinc.com]
-->Sent: Friday, October 24, 2003 11:27 AM
-->To: mysql@lists.mysql.com
-->Cc: bugs@lists.mysql.com
-->Subject: reproducible error 17
-->
-->Error 'Can't create symlink './ffadult_recent/#sql-44b_e87.MYI'
pointing
-->at '/var/lib/mysql/ff_recent/#sql-44b_e87.MYI' (Error 17)' on query
-->'ALTER TABLE recentmembers_8 add have_children tinyint unsigned not
NULL
-->default 0'. Default database: 'ff_recent' | 0 | 346296822
-->| 369493795
-->
-->
-->Perror says
-->Error code 17: File exists
-->
-->I have 2 alters submitted on the master in sequential order. On the
-->slave it executes in the same order but produces error 17. Somehow
the
-->tempfile already exists, but when I go to the datadir this temp file
is
-->not displayed in the directory.
-->
-->Performing a slave stop slave start does not work.
-->Restarting the mysql server on the slave is the only method I found
to
-->recover from the error.
-->
-->
-->Version of mySQL: 4.0.15
-->Kernel: Linux ef242.friendfinderinc.com 2.4.18-4smp #1 SMP Sun Apr 14
-->13:50:35 PDT 2002 i686 unknown
-->*PATCHED*
-->
-->
-->- Dathan Vance Pattishall
--> - Sr. Programmer and mySQL DBA for FriendFinder Inc.
--> - http://friendfinder.com/go/p40688
-->



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org

RE: reproducible error 17

am 24.10.2003 21:21:04 von Dathan Vance Pattishall

*************************** 1. row ***************************
Master_Host: 10.2.12.224
Master_User: rep
Master_Port: 3306
Connect_retry: 60
Master_Log_File: ef224-bin.020
Read_Master_Log_Pos: 409223612
Relay_Log_File: ef242-relay-bin.016
Relay_Log_Pos: 330063332
Relay_Master_Log_File: ef224-bin.020
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_do_db:=20
Replicate_ignore_db:=20
Last_errno: 25
Last_error: Error 'Can't create symlink
'./ff_recent/#sql-17f7_c.MYI' pointing at
'/var/lib/mysql/ff_recent/#sql-17f7_c.MYI' (Error 17)' on query 'ALTER
TABLE recentmembers_20 add have_children tinyint unsigned not NULL
default 0'. Default database: 'ff_recent'
Skip_counter: 0
Exec_master_log_pos: 348632736
Relay_log_space: 390689457

- Dathan Vance Pattishall
=A0 - Sr. Programmer and mySQL DBA for FriendFinder Inc.
=A0 - http://friendfinder.com/go/p40688


-->-----Original Message-----
-->From: Dathan Vance Pattishall [mailto:dathan@friendfinderinc.com]
-->Sent: Friday, October 24, 2003 11:27 AM
-->To: mysql@lists.mysql.com
-->Cc: bugs@lists.mysql.com
-->Subject: reproducible error 17
-->
-->Error 'Can't create symlink './ffadult_recent/#sql-44b_e87.MYI'
pointing
-->at '/var/lib/mysql/ff_recent/#sql-44b_e87.MYI' (Error 17)' on query
-->'ALTER TABLE recentmembers_8 add have_children tinyint unsigned not
NULL
-->default 0'. Default database: 'ff_recent' | 0 | 346296822
-->| 369493795
-->
-->
-->Perror says
-->Error code 17: File exists
-->
-->I have 2 alters submitted on the master in sequential order. On the
-->slave it executes in the same order but produces error 17. Somehow
the
-->tempfile already exists, but when I go to the datadir this temp file
is
-->not displayed in the directory.
-->
-->Performing a slave stop slave start does not work.
-->Restarting the mysql server on the slave is the only method I found
to
-->recover from the error.
-->
-->
-->Version of mySQL: 4.0.15
-->Kernel: Linux ef242.friendfinderinc.com 2.4.18-4smp #1 SMP Sun Apr 14
-->13:50:35 PDT 2002 i686 unknown
-->*PATCHED*
-->
-->
-->- Dathan Vance Pattishall
--> - Sr. Programmer and mySQL DBA for FriendFinder Inc.
--> - http://friendfinder.com/go/p40688
-->



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org

RE: reproducible error 17

am 27.10.2003 19:55:18 von Guilhem Bichot

Hi,

> *************************** 1. row ***************************
> Master_Host: 10.2.12.224
> Master_User: rep
> Master_Port: 3306
> Connect_retry: 60
> Master_Log_File: ef224-bin.020
> Read_Master_Log_Pos: 409223612
> Relay_Log_File: ef242-relay-bin.016
> Relay_Log_Pos: 330063332
> Relay_Master_Log_File: ef224-bin.020
> Slave_IO_Running: Yes
> Slave_SQL_Running: No
> Replicate_do_db:
> Replicate_ignore_db:
> Last_errno: 25
> Last_error: Error 'Can't create symlink
> './ff_recent/#sql-17f7_c.MYI' pointing at
> '/var/lib/mysql/ff_recent/#sql-17f7_c.MYI' (Error 17)' on query 'ALTER
> TABLE recentmembers_20 add have_children tinyint unsigned not NULL
> default 0'. Default database: 'ff_recent'
> Skip_counter: 0
> Exec_master_log_pos: 348632736
> Relay_log_space: 390689457

Are the master and slave located on two different machines, or on one
single machine?
Are you using symlinks to place tables or databases at other locations
than the datadir?
If yes:
* How did you do it (with manual Unix 'ln -s', with
CREATE TABLE ... DATA DIRECTORY= INDEX DIRECTORY=, another way?).
* Did you do it the same way on master and slave?

Do master and slave have the same tree structure (i.e. do the
directories ./ff_recent/ and /var/lib/mysql/ff_recent all exist on the 2
machines?).

If you could provide a simple complete testcase like:
"create a symlinked table on the master by following these steps, then
do this ALTER, then do that ALTER", this could surely help us.

Thank you.

--
For technical support contracts, visit https://order.mysql.com/?ref=mgbi
Are you MySQL certified? visit http://www.mysql.com/certification/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Guilhem Bichot
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Software Developer
/_/ /_/\_, /___/\___\_\___/ Bordeaux, France
<___/ www.mysql.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

RE: reproducible error 17

am 27.10.2003 22:01:24 von Dathan Vance Pattishall

-->-----Original Message-----
-->From: Guilhem Bichot [mailto:guilhem@mysql.com]
-->Sent: Monday, October 27, 2003 10:55 AM
-->To: mysql@lists.mysql.com
-->Cc: bugs@lists.mysql.com; dlenev@mysql.com; sinisa@mysql.com
-->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(_*)

-->If yes:
-->* How did you do it (with manual Unix 'ln -s', with
-->CREATE TABLE ... DATA DIRECTORY= INDEX DIRECTORY=, another way?).
-->* Did you do it the same way on master and slave?


Used ln -s

-->
-->Do master and slave have the same tree structure (i.e. do the
-->directories ./ff_recent/ and /var/lib/mysql/ff_recent all exist on
the 2
-->machines?).

Yes

-->
-->If you could provide a simple complete testcase like:
-->"create a symlinked table on the master by following these steps,
then
-->do this ALTER, then do that ALTER", this could surely help us.
-->

I wish I could but all my servers (> 56) are in production. I will try
late at night, so our users are not affected. Anything for the mysql
team, thanks for your hard work.




-->
-->--




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: reproducible error 17

am 27.10.2003 22:01:24 von Dathan Vance Pattishall

-->-----Original Message-----
-->From: Guilhem Bichot [mailto:guilhem@mysql.com]
-->Sent: Monday, October 27, 2003 10:55 AM
-->To: mysql@lists.mysql.com
-->Cc: bugs@lists.mysql.com; dlenev@mysql.com; sinisa@mysql.com
-->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(_*)

-->If yes:
-->* How did you do it (with manual Unix 'ln -s', with
-->CREATE TABLE ... DATA DIRECTORY= INDEX DIRECTORY=, another way?).
-->* Did you do it the same way on master and slave?


Used ln -s

-->
-->Do master and slave have the same tree structure (i.e. do the
-->directories ./ff_recent/ and /var/lib/mysql/ff_recent all exist on
the 2
-->machines?).

Yes

-->
-->If you could provide a simple complete testcase like:
-->"create a symlinked table on the master by following these steps,
then
-->do this ALTER, then do that ALTER", this could surely help us.
-->

I wish I could but all my servers (> 56) are in production. I will try
late at night, so our users are not affected. Anything for the mysql
team, thanks for your hard work.




-->
-->--




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: reproducible error 17

am 28.10.2003 00:15:04 von Guilhem Bichot

On Mon, 2003-10-27 at 22:01, Dathan Vance Pattishall wrote:
> -->-----Original Message-----
> -->From: Guilhem Bichot [mailto:guilhem@mysql.com]
> -->Sent: Monday, October 27, 2003 10:55 AM
> -->To: mysql@lists.mysql.com
> -->Cc: bugs@lists.mysql.com; dlenev@mysql.com; sinisa@mysql.com
> -->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/mysql?unsub=gcdmg-mysql@m.gmane.org

RE: reproducible error 17

am 28.10.2003 00:15:04 von Guilhem Bichot

On Mon, 2003-10-27 at 22:01, Dathan Vance Pattishall wrote:
> -->-----Original Message-----
> -->From: Guilhem Bichot [mailto:guilhem@mysql.com]
> -->Sent: Monday, October 27, 2003 10:55 AM
> -->To: mysql@lists.mysql.com
> -->Cc: bugs@lists.mysql.com; dlenev@mysql.com; sinisa@mysql.com
> -->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/mysql?unsub=gcdmg-mysql@m.gmane.org

RE: reproducible error 17

am 28.10.2003 12:07:49 von Guilhem Bichot

On Tue, 2003-10-28 at 00:15, Guilhem Bichot wrote:
> On Mon, 2003-10-27 at 22:01, Dathan Vance Pattishall wrote:

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

Sorry, I should have been more accurate in the last sentence.
It's ok to do DML commands *always* on the real table OR *always* on the
synonym table.
If thread1 uses the real table's name, and thread 2 uses the synonym,
the query cache can be fooled:
- set global query_cache_size=1000000;
- connection1: select * from tbl_;
- connection2: insert into tbl values(1);
- connection1: select * from tbl_; you don't see the inserted row!
- connection1: flush tables (empties caches); select * from tbl_; you
see the inserted row!

Even if you disable the query cache, I am not sure if it's safe to use
both names; there could be some other fooled caches in MySQL.

Simply put, things go wild when the real name and the synonym are both
used. Which impacts the interest of using synonyms (hum). And FLUSH
TABLES is a remedy.

I'll add this to the manual.

Regards,
Guilhem


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: reproducible error 17

am 28.10.2003 12:07:49 von Guilhem Bichot

On Tue, 2003-10-28 at 00:15, Guilhem Bichot wrote:
> On Mon, 2003-10-27 at 22:01, Dathan Vance Pattishall wrote:

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

Sorry, I should have been more accurate in the last sentence.
It's ok to do DML commands *always* on the real table OR *always* on the
synonym table.
If thread1 uses the real table's name, and thread 2 uses the synonym,
the query cache can be fooled:
- set global query_cache_size=1000000;
- connection1: select * from tbl_;
- connection2: insert into tbl values(1);
- connection1: select * from tbl_; you don't see the inserted row!
- connection1: flush tables (empties caches); select * from tbl_; you
see the inserted row!

Even if you disable the query cache, I am not sure if it's safe to use
both names; there could be some other fooled caches in MySQL.

Simply put, things go wild when the real name and the synonym are both
used. Which impacts the interest of using synonyms (hum). And FLUSH
TABLES is a remedy.

I'll add this to the manual.

Regards,
Guilhem


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: reproducible error 17

am 28.10.2003 12:31:38 von Guilhem Bichot

On Tue, 2003-10-28 at 12:07, Guilhem Bichot wrote:
> On Tue, 2003-10-28 at 00:15, Guilhem Bichot wrote:
> > On Mon, 2003-10-27 at 22:01, Dathan Vance Pattishall wrote:
>
> > 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.
>
> Sorry, I should have been more accurate in the last sentence.
> It's ok to do DML commands *always* on the real table OR *always* on the
> synonym table.
> If thread1 uses the real table's name, and thread 2 uses the synonym,
> the query cache can be fooled:
> - set global query_cache_size=1000000;
> - connection1: select * from tbl_;
> - connection2: insert into tbl values(1);
> - connection1: select * from tbl_; you don't see the inserted row!
> - connection1: flush tables (empties caches); select * from tbl_; you
> see the inserted row!
>
> Even if you disable the query cache, I am not sure if it's safe to use
> both names; there could be some other fooled caches in MySQL.
>
> Simply put, things go wild when the real name and the synonym are both
> used. Which impacts the interest of using synonyms (hum). And FLUSH
> TABLES is a remedy.
>
> I'll add this to the manual.

Added. You should be able to see it in our online manual www.mysql.com/doc
(end of section "Using symbolic links") in the next hours.


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: reproducible error 17

am 28.10.2003 12:31:38 von Guilhem Bichot

On Tue, 2003-10-28 at 12:07, Guilhem Bichot wrote:
> On Tue, 2003-10-28 at 00:15, Guilhem Bichot wrote:
> > On Mon, 2003-10-27 at 22:01, Dathan Vance Pattishall wrote:
>
> > 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.
>
> Sorry, I should have been more accurate in the last sentence.
> It's ok to do DML commands *always* on the real table OR *always* on the
> synonym table.
> If thread1 uses the real table's name, and thread 2 uses the synonym,
> the query cache can be fooled:
> - set global query_cache_size=1000000;
> - connection1: select * from tbl_;
> - connection2: insert into tbl values(1);
> - connection1: select * from tbl_; you don't see the inserted row!
> - connection1: flush tables (empties caches); select * from tbl_; you
> see the inserted row!
>
> Even if you disable the query cache, I am not sure if it's safe to use
> both names; there could be some other fooled caches in MySQL.
>
> Simply put, things go wild when the real name and the synonym are both
> used. Which impacts the interest of using synonyms (hum). And FLUSH
> TABLES is a remedy.
>
> I'll add this to the manual.

Added. You should be able to see it in our online manual www.mysql.com/doc
(end of section "Using symbolic links") in the next hours.


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: reproducible error 17

am 29.10.2003 00:50:37 von Dathan Vance Pattishall

Thanks for the information. I to found a work around-a much uglier
approach.

For all ALTERs of a src table to work when synonyms of tables are
present: perform the ALTER on the slave itself and add to
skip-slave-errors=3D1060

This is a quick work around. Very very ugly.


- Dathan Vance Pattishall
=A0 - Sr. Programmer and mySQL DBA for FriendFinder Inc.
=A0 - http://friendfinder.com/go/p40688


-->-----Original Message-----
-->From: Guilhem Bichot [mailto:guilhem@mysql.com]
-->Sent: Tuesday, October 28, 2003 3:32 AM
-->To: Dathan Vance Pattishall
-->Cc: mysql@lists.mysql.com; bugs@lists.mysql.com; dlenev@mysql.com;
-->sinisa@mysql.com
-->Subject: RE: reproducible error 17
-->
-->On Tue, 2003-10-28 at 12:07, Guilhem Bichot wrote:
-->> On Tue, 2003-10-28 at 00:15, Guilhem Bichot wrote:
-->> > On Mon, 2003-10-27 at 22:01, Dathan Vance Pattishall wrote:
-->>
-->> > 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.
-->>
-->> Sorry, I should have been more accurate in the last sentence.
-->> It's ok to do DML commands *always* on the real table OR *always*
on
-->the
-->> synonym table.
-->> If thread1 uses the real table's name, and thread 2 uses the
synonym,
-->> the query cache can be fooled:
-->> - set global query_cache_size=3D1000000;
-->> - connection1: select * from tbl_;
-->> - connection2: insert into tbl values(1);
-->> - connection1: select * from tbl_; you don't see the inserted row!
-->> - connection1: flush tables (empties caches); select * from tbl_;
you
-->> see the inserted row!
-->>
-->> Even if you disable the query cache, I am not sure if it's safe to
use
-->> both names; there could be some other fooled caches in MySQL.
-->>
-->> Simply put, things go wild when the real name and the synonym are
both
-->> used. Which impacts the interest of using synonyms (hum). And FLUSH
-->> TABLES is a remedy.
-->>
-->> I'll add this to the manual.
-->
-->Added. You should be able to see it in our online manual
-->www.mysql.com/doc
-->(end of section "Using symbolic links") in the next hours.
-->
-->
-->--
-->MySQL General Mailing List
-->For list archives: http://lists.mysql.com/mysql
-->To unsubscribe:
-->http://lists.mysql.com/mysql?unsub=3Ddathan@friendfinderi nc.com




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org

RE: reproducible error 17

am 29.10.2003 00:50:37 von Dathan Vance Pattishall

Thanks for the information. I to found a work around-a much uglier
approach.

For all ALTERs of a src table to work when synonyms of tables are
present: perform the ALTER on the slave itself and add to
skip-slave-errors=3D1060

This is a quick work around. Very very ugly.


- Dathan Vance Pattishall
=A0 - Sr. Programmer and mySQL DBA for FriendFinder Inc.
=A0 - http://friendfinder.com/go/p40688


-->-----Original Message-----
-->From: Guilhem Bichot [mailto:guilhem@mysql.com]
-->Sent: Tuesday, October 28, 2003 3:32 AM
-->To: Dathan Vance Pattishall
-->Cc: mysql@lists.mysql.com; bugs@lists.mysql.com; dlenev@mysql.com;
-->sinisa@mysql.com
-->Subject: RE: reproducible error 17
-->
-->On Tue, 2003-10-28 at 12:07, Guilhem Bichot wrote:
-->> On Tue, 2003-10-28 at 00:15, Guilhem Bichot wrote:
-->> > On Mon, 2003-10-27 at 22:01, Dathan Vance Pattishall wrote:
-->>
-->> > 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.
-->>
-->> Sorry, I should have been more accurate in the last sentence.
-->> It's ok to do DML commands *always* on the real table OR *always*
on
-->the
-->> synonym table.
-->> If thread1 uses the real table's name, and thread 2 uses the
synonym,
-->> the query cache can be fooled:
-->> - set global query_cache_size=3D1000000;
-->> - connection1: select * from tbl_;
-->> - connection2: insert into tbl values(1);
-->> - connection1: select * from tbl_; you don't see the inserted row!
-->> - connection1: flush tables (empties caches); select * from tbl_;
you
-->> see the inserted row!
-->>
-->> Even if you disable the query cache, I am not sure if it's safe to
use
-->> both names; there could be some other fooled caches in MySQL.
-->>
-->> Simply put, things go wild when the real name and the synonym are
both
-->> used. Which impacts the interest of using synonyms (hum). And FLUSH
-->> TABLES is a remedy.
-->>
-->> I'll add this to the manual.
-->
-->Added. You should be able to see it in our online manual
-->www.mysql.com/doc
-->(end of section "Using symbolic links") in the next hours.
-->
-->
-->--
-->MySQL General Mailing List
-->For list archives: http://lists.mysql.com/mysql
-->To unsubscribe:
-->http://lists.mysql.com/mysql?unsub=3Ddathan@friendfinderi nc.com




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org