Re: suspected bug...
I could be lame, but I couldn't reproduce the error. The query worked ok for me -- using 4.0.4-beta-max-nt i posted this to the newsgroups and got told it was probably a sql parser bug so here goes... if u try to execute a query such as: insert into foo (id,textfield) values (54,'\\fred\\') the query fails if i add a space after the last \\ it works this is a real pain personally cos we use a lot of that stuff :( any workarounds or fixes or whatnots? many thanks lauren David - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: suspected bug...
[EMAIL PROTECTED] wrote: I could be lame, but I couldn't reproduce the error. The query worked ok for me -- using 4.0.4-beta-max-nt Isn't Lauren using a language that uses \ as an escaping character? And then tries to do something with \f? If it works form the mysql comman line, then this can be the case. - Cs. i posted this to the newsgroups and got told it was probably a sql parser bug so here goes... if u try to execute a query such as: insert into foo (id,textfield) values (54,'\\fred\\') the query fails if i add a space after the last \\ it works this is a real pain personally cos we use a lot of that stuff :( any workarounds or fixes or whatnots? many thanks lauren - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: suspected bug...
On 13 Mar 2003, at 14:59, lauren wrote: if u try to execute a query such as: insert into foo (id,textfield) values (54,'\\fred\\') the query fails if i add a space after the last \\ it works this is a real pain personally cos we use a lot of that stuff :( You'll have to show us exactly how you're getting the query to MySQL. Are you using PHP? Perl? C? Regardless, I suspect that by the time the SQL gets to MySQL you don't have as many backslashes as you think you do. Constructing strings that have contain backslashes, especially multiple backslashes, and passing them around can be tricky. If you're doing this in PHP: $sql = INSERT INTO foo (id, text_field) VALUES (54, '\\fred\\'); then the string doesn't actually contain double backslashes, only single ones, so it'll be invalid if you pass it to MySQL. You'll need quadruple backslashes in your PHP to end up with double backslashes in your SQL. (And things get even worse if you're using LIKE, which involves yet another level of escaping.) If your backslash-containing strings are file paths, you might consider avoiding the complication and using forward slashes instead. They work fine on Windows in most contexts. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: suspected bug...
the actual horror story query is: update link set path=insert(path,1,13,'\\test\\test21\\') where person_id = 8 and path like '\\test\\test2\\%' am going slightly dilly :/ --- even if my world is weird its my world -Original Message- From: Keith C. Ivey [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 15:27 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: suspected bug... On 13 Mar 2003, at 14:59, lauren wrote: if u try to execute a query such as: insert into foo (id,textfield) values (54,'\\fred\\') the query fails if i add a space after the last \\ it works this is a real pain personally cos we use a lot of that stuff :( You'll have to show us exactly how you're getting the query to MySQL. Are you using PHP? Perl? C? Regardless, I suspect that by the time the SQL gets to MySQL you don't have as many backslashes as you think you do. Constructing strings that have contain backslashes, especially multiple backslashes, and passing them around can be tricky. If you're doing this in PHP: $sql = INSERT INTO foo (id, text_field) VALUES (54, '\\fred\\'); then the string doesn't actually contain double backslashes, only single ones, so it'll be invalid if you pass it to MySQL. You'll need quadruple backslashes in your PHP to end up with double backslashes in your SQL. (And things get even worse if you're using LIKE, which involves yet another level of escaping.) If your backslash-containing strings are file paths, you might consider avoiding the complication and using forward slashes instead. They work fine on Windows in most contexts. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: suspected bug...
On 13 Mar 2003, at 15:30, lauren wrote: the actual horror story query is: update link set path=insert(path,1,13,'\\test\\test21\\') where person_id = 8 and path like '\\test\\test2\\%' You still haven't told us how you're sending this query to MySQL, and my advice remains the same. If at all possible, avoid the problem by using forward slashes rather than backslashes in your paths. If that's not possible, carefully work through how many levels of escaping you need to get MySQL to see the final SQL statement as you want it. Note that for the LIKE part of your query you're going to need four backslashes to match a single backslash, which means you likely will be typing eight backslashes at that point in your PHP (or whatever) code. See the documentation: | Note: Because MySQL uses the C escape syntax in strings (for | example, `\n'), you must double any `\' that you use in your | LIKE strings. For example, to search for `\n', specify it as | `\\n'. To search for `\', specify it as `' (the backslashes | are stripped once by the parser and another time when the | pattern match is done, leaving a single backslash to be | matched). http://www.mysql.com/doc/en/String_comparison_functions.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: suspected bug...
its cool i got the 4 backslashes thing worked out thanks very much for the pointer :) --- even if my world is weird its my world -Original Message- From: Keith C. Ivey [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 16:11 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: suspected bug... On 13 Mar 2003, at 15:30, lauren wrote: the actual horror story query is: update link set path=insert(path,1,13,'\\test\\test21\\') where person_id = 8 and path like '\\test\\test2\\%' You still haven't told us how you're sending this query to MySQL, and my advice remains the same. If at all possible, avoid the problem by using forward slashes rather than backslashes in your paths. If that's not possible, carefully work through how many levels of escaping you need to get MySQL to see the final SQL statement as you want it. Note that for the LIKE part of your query you're going to need four backslashes to match a single backslash, which means you likely will be typing eight backslashes at that point in your PHP (or whatever) code. See the documentation: | Note: Because MySQL uses the C escape syntax in strings (for | example, `\n'), you must double any `\' that you use in your | LIKE strings. For example, to search for `\n', specify it as | `\\n'. To search for `\', specify it as `' (the backslashes | are stripped once by the parser and another time when the | pattern match is done, leaving a single backslash to be | matched). http://www.mysql.com/doc/en/String_comparison_functions.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Suspected Bug
* Miguel Angel Solorzano snip * Roger Baklund mysql rename table U1 to U2; ERROR 7: Error on rename of '.\test\u1.MYI' to '.\test\u2.MYI' (Errcode: 13) mysql alter table U1 rename as U2; Query OK, 0 rows affected (0.01 sec) snip How you can see we don't have a constant behavior of the Windows OS in the handle of lower/upper case names. The user should know if his application will works on NT/Win2K should expects error messages with write operation using different syntax with the lower/upper case. Thanks for the detailed reply. :) I did my tests on a pretty old version, 3.23.30-gamma, on a win2k machine. I was not aware of the lower_case_table_names variable. (This explains why I got lower case filename in my error message, while Fred Lovine got upper case filename... I was a bit puzzled about that.) Can we from this conclude that it is safer to use all lowercase table names when one want to make an application that should run on _any_ platform? That way there should never be an error because of letter casing in table names, regardless of the lower_case_table_names setting, windows version and even mysql version (at least 3.23.6 and later)? There is a phrase about this in the documentation: If you have a problem remembering the used cases for a table names, adopt a consistent convention, such as always creating databases and tables using lowercase names. URL: http://www.mysql.com/doc/N/a/Name_case_sensitivity.html Maybe this should be upgraded to an advise to use lowercase names to assure cross platform and version compatibility? -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Suspected Bug
At 17:59 16/02/2002 +0100, Roger Baklund wrote: Hi, Thanks for the detailed reply. :) I did my tests on a pretty old version, 3.23.30-gamma, on a win2k machine. I was not aware of the lower_case_table_names variable. (This explains why I got lower case filename in my error message, while Fred Lovine got upper case filename... I was a bit puzzled about that.) Can we from this conclude that it is safer to use all lowercase table names when one want to make an application that should run on _any_ platform? In my personal opinion yes, not only this but to use short description names and avoiding blank spaces. However I know that the developer sometimes doesn't have option. That way there should never be an error because of letter casing in table names, regardless of the lower_case_table_names setting, windows version and even mysql version (at least 3.23.6 and later)? There is a phrase about this in the documentation: If you have a problem remembering the used cases for a table names, adopt a consistent convention, such as always creating databases and tables using lowercase names. URL: http://www.mysql.com/doc/N/a/Name_case_sensitivity.html Maybe this should be upgraded to an advise to use lowercase names to assure cross platform and version compatibility? Yes. Regards, Miguel -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel A. Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Mogi das Cruzes - São Paulo, Brazil ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Suspected Bug
Yeah, for me also find error. -- mysql create table X1(name varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into X1 values(5); Query OK, 1 row affected (0.01 sec) mysql RENAME TABLE X1 TO X2; ERROR 1064: You have an error in your SQL syntax near 'RENAME TABLE X1 TO X2' at line 1 mysql Regards, ~ SubbaReddy .M Sr. Programmer, Frontlinesoft, Hyderabad http://www.frontlinesoft.com ICQ: 56093095 - Original Message - From: Fred Lovine [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 15, 2002 12:23 PM Subject: Suspected Bug MySQL is awesome, but I think I found a bug. The following script: CREATE TABLE X1 (x smallint); insert into X1 values(5); RENAME TABLE X1 TO X2; is producing the following error: 7 - Error on rename '.\db\X1.MY1' to '.\db\X2.MYI' (Errcode: 13) The problem seems to be caused by using uppercase table names. The problem does not happen when the table names are all lowercase. MySQL version: 3.23.47-nt Running on Windows 2000 Pro Feel free to contact me if there are any questions. Server Variables Variable CLW Laptop back_log 50 basedir C:\CLW\MySQL\ binlog_cache_size 32768 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert ON connect_timeout 5 datadir C:\CLW\MySQL\data\ delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 1800 have_bdb NO have_gemini NO have_innodb NO have_isam YES have_raid NO have_openssl NO init_file interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 8384512 language C:\CLW\MySQL\share\english\ large_files_support ON log OFF log_update OFF log_bin OFF log_slave_updates OFF log_long_queries OFF long_query_time 10 low_priority_updates OFF lower_case_table_names 1 max_allowed_packet 130048 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connections 100 max_connect_errors 10 max_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_sort_length 1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_max_extra_sort_file_size 256 myisam_max_sort_file_size 2047 myisam_recover_options 0 myisam_sort_buffer_size 8388608 net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 open_files_limit 0 pid_file C:\CLW\MySQL\data\laptop.pid port 3306 protocol_version 10 record_buffer 131072 record_rnd_buffer 131072 query_buffer_size 0 safe_show_database OFF server_id 0 slave_net_timeout 3600 skip_locking ON skip_networking OFF skip_show_database OFF slow_launch_time 2 socket MySQL sort_buffer 2097144 sql_mode 0 table_cache 64 table_type MYISAM thread_cache_size 0 thread_stack 65536 transaction_isolation READ-COMMITTED timezone Eastern Standard Time tmp_table_size 33554432 tmpdir C:\WINNT\TEMP\ version 3.23.47-nt wait_timeout 28800 Fred Lovine [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Suspected Bug
I am afraid it's not a bug, rather a syntax error. please follow the syntax to rename the table: mysql alter table X1 rename as X2; Regards, ~ SubbaReddy .M - Original Message - From: SubbaReddy M [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, February 15, 2002 3:46 PM Subject: Re: Suspected Bug Yeah, for me also find error. -- -- -- mysql create table X1(name varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into X1 values(5); Query OK, 1 row affected (0.01 sec) mysql RENAME TABLE X1 TO X2; ERROR 1064: You have an error in your SQL syntax near 'RENAME TABLE X1 TO X2' at line 1 mysql -- -- Regards, ~ SubbaReddy .M Sr. Programmer, Frontlinesoft, Hyderabad http://www.frontlinesoft.com ICQ: 56093095 - Original Message - From: Fred Lovine [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 15, 2002 12:23 PM Subject: Suspected Bug MySQL is awesome, but I think I found a bug. The following script: CREATE TABLE X1 (x smallint); insert into X1 values(5); RENAME TABLE X1 TO X2; is producing the following error: 7 - Error on rename '.\db\X1.MY1' to '.\db\X2.MYI' (Errcode: 13) The problem seems to be caused by using uppercase table names. The problem does not happen when the table names are all lowercase. MySQL version: 3.23.47-nt Running on Windows 2000 Pro Feel free to contact me if there are any questions. Server Variables Variable CLW Laptop back_log 50 basedir C:\CLW\MySQL\ binlog_cache_size 32768 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert ON connect_timeout 5 datadir C:\CLW\MySQL\data\ delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 1800 have_bdb NO have_gemini NO have_innodb NO have_isam YES have_raid NO have_openssl NO init_file interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 8384512 language C:\CLW\MySQL\share\english\ large_files_support ON log OFF log_update OFF log_bin OFF log_slave_updates OFF log_long_queries OFF long_query_time 10 low_priority_updates OFF lower_case_table_names 1 max_allowed_packet 130048 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connections 100 max_connect_errors 10 max_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_sort_length 1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_max_extra_sort_file_size 256 myisam_max_sort_file_size 2047 myisam_recover_options 0 myisam_sort_buffer_size 8388608 net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 open_files_limit 0 pid_file C:\CLW\MySQL\data\laptop.pid port 3306 protocol_version 10 record_buffer 131072 record_rnd_buffer 131072 query_buffer_size 0 safe_show_database OFF server_id 0 slave_net_timeout 3600 skip_locking ON skip_networking OFF skip_show_database OFF slow_launch_time 2 socket MySQL sort_buffer 2097144 sql_mode 0 table_cache 64 table_type MYISAM thread_cache_size 0 thread_stack 65536 transaction_isolation READ-COMMITTED timezone Eastern Standard Time tmp_table_size 33554432 tmpdir C:\WINNT\TEMP\ version 3.23.47-nt wait_timeout 28800 Fred Lovine [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Suspected Bug
* Egor Egorov FL = Fred Lovine FL CREATE TABLE X1 (x smallint); FL insert into X1 values(5); FL RENAME TABLE X1 TO X2; FL is producing the following error: FL 7 - Error on rename '.\db\X1.MY1' to '.\db\X2.MYI' (Errcode: 13) FL The problem seems to be caused by using uppercase table FL names. The problem does not happen when the table names FL are all lowercase. $ perror 13 Error code 13: Permission denied When you rename table, you also rename files that are appropriate to this table. You don't have file permission to rename files in mysql data dir. Do you mean this is the desired behaviour? Also, this does not explain why it only happens when the name is uppercase. -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Suspected Bug
Once tables are CREATEd, the name is listed in SHOW tables using lower case characters, regardless of the case used in the CREATE command. Using upper case table names in RENAME results in the error. Workaround: Using lower case equivalent table names works, regardless of the case used in the CREATE command. Windows does not differentiate between X1 and x1 as table/filenames. *nix does. Perhaps CREATE has been Window-ised but RENAME has not (yet). Regards, =dn WinNT 4.0 SP6a, MySQL vn 3.23.40-nt - Original Message - From: SubbaReddy M [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 15 February 2002 10:16 Subject: Re: Suspected Bug Yeah, for me also find error. -- mysql create table X1(name varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into X1 values(5); Query OK, 1 row affected (0.01 sec) mysql RENAME TABLE X1 TO X2; ERROR 1064: You have an error in your SQL syntax near 'RENAME TABLE X1 TO X2' at line 1 mysql Regards, ~ SubbaReddy .M Sr. Programmer, Frontlinesoft, Hyderabad http://www.frontlinesoft.com ICQ: 56093095 - Original Message - From: Fred Lovine [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 15, 2002 12:23 PM Subject: Suspected Bug MySQL is awesome, but I think I found a bug. The following script: CREATE TABLE X1 (x smallint); insert into X1 values(5); RENAME TABLE X1 TO X2; is producing the following error: 7 - Error on rename '.\db\X1.MY1' to '.\db\X2.MYI' (Errcode: 13) The problem seems to be caused by using uppercase table names. The problem does not happen when the table names are all lowercase. MySQL version: 3.23.47-nt Running on Windows 2000 Pro Feel free to contact me if there are any questions. Server Variables Variable CLW Laptop back_log 50 basedir C:\CLW\MySQL\ binlog_cache_size 32768 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert ON connect_timeout 5 datadir C:\CLW\MySQL\data\ delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 1800 have_bdb NO have_gemini NO have_innodb NO have_isam YES have_raid NO have_openssl NO init_file interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 8384512 language C:\CLW\MySQL\share\english\ large_files_support ON log OFF log_update OFF log_bin OFF log_slave_updates OFF log_long_queries OFF long_query_time 10 low_priority_updates OFF lower_case_table_names 1 max_allowed_packet 130048 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connections 100 max_connect_errors 10 max_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_sort_length 1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_max_extra_sort_file_size 256 myisam_max_sort_file_size 2047 myisam_recover_options 0 myisam_sort_buffer_size 8388608 net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 open_files_limit 0 pid_file C:\CLW\MySQL\data\laptop.pid port 3306 protocol_version 10 record_buffer 131072 record_rnd_buffer 131072 query_buffer_size 0 safe_show_database OFF server_id 0 slave_net_timeout 3600 skip_locking ON skip_networking OFF skip_show_database OFF slow_launch_time 2 socket MySQL sort_buffer 2097144 sql_mode 0 table_cache 64 table_type MYISAM thread_cache_size 0 thread_stack 65536 transaction_isolation READ-COMMITTED timezone Eastern Standard Time tmp_table_size 33554432 tmpdir C:\WINNT\TEMP\ version 3.23.47-nt wait_timeout 28800 Fred Lovine [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Suspected Bug
SubbaReddy M I am afraid it's not a bug, rather a syntax error. please follow the syntax to rename the table: mysql alter table X1 rename as X2; Why do you say this? Manual entry: 6.5.5 RENAME TABLE Syntax RENAME TABLE tbl_name TO new_table_name[, tbl_name2 TO new_table_name2,...] ... RENAME TABLE was added in MySQL 3.23.23. Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Suspected Bug
Egor, Yours/this arrived in my InBox whilst I was addressing the original msg - please see my other reply/ies. Error still occurs when running as Administrator on WinNT and root under MySQL - definitely no access/permission issues. Lower case works (please see earlier reply). If I/my NT PC can be of assistance, please don't hesitate to request further clarification/experimentation. Regards, =dn WinNT 4.0 SP6a, PHP vn 4.0.6, Apache vn 1.3.20WinNT, MySQL vn 3.23.40-nt - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 15 February 2002 11:00 Subject: Suspected Bug Fred, Friday, February 15, 2002, 8:53:17 AM, you wrote: FL MySQL is awesome, but I think I found a bug. FL The following script: FL CREATE TABLE X1 (x smallint); FL insert into X1 values(5); FL RENAME TABLE X1 TO X2; FL is producing the following error: FL 7 - Error on rename '.\db\X1.MY1' to '.\db\X2.MYI' (Errcode: 13) FL The problem seems to be caused by using uppercase table names. The problem FL does not happen when the table names are all lowercase. $ perror 13 Error code 13: Permission denied When you rename table, you also rename files that are appropriate to this table. You don't have file permission to rename files in mysql data dir. FL MySQL version: 3.23.47-nt FL Running on Windows 2000 Pro FL Feel free to contact me if there are any questions. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Suspected Bug
Hi, this has to be a bug. It seems on windows RENAME TABLE old TO new only works for uppercase table names _after_ an ALTER TABLE old RENAME AS new _or_ if you first rename the table using lower case letters: mysql create table U1 (id int); Query OK, 0 rows affected (0.04 sec) mysql insert into U1 values (1); Query OK, 1 row affected (0.01 sec) mysql rename table U1 to U2; ERROR 7: Error on rename of '.\test\u1.MYI' to '.\test\u2.MYI' (Errcode: 13) mysql alter table U1 rename as U2; Query OK, 0 rows affected (0.01 sec) mysql rename table U2 to U1; Query OK, 0 rows affected (0.00 sec) mysql rename table U1 to U2; Query OK, 0 rows affected (0.01 sec) I think there is some confusion within the mysql server because windows has case insensitive file names. The table is created with lowercase letters on the OS level: mysql create table W1 (id int); Query OK, 0 rows affected (0.05 sec) mysql insert into W1 values (1); Query OK, 1 row affected (0.00 sec) mysql show tables like W%; Empty set (0.01 sec) mysql show tables like w%; +-+ | Tables_in_test (w%) | +-+ | w1 | +-+ 1 rows in set (0.00 sec) C:\mysql\data\testdir w1* Volume in drive C has no label. Volume Serial Number is 28E6-EF3E Directory of C:\mysql\data\test 15.02.2002 13:028 550 w1.frm 15.02.2002 13:035 w1.MYD 15.02.2002 13:021 024 w1.MYI 3 File(s) 9 579 bytes 0 Dir(s) 573 779 968 bytes free mysql rename table W1 to W2; ERROR 7: Error on rename of '.\test\w1.MYI' to '.\test\w2.MYI' (Errcode: 13) mysql rename table w1 to W2; Query OK, 0 rows affected (0.01 sec) mysql rename table W2 to W1; Query OK, 0 rows affected (0.00 sec) mysql rename table W1 to W2; Query OK, 0 rows affected (0.01 sec) Note that the error message has the table name in lower case, so it seems mysql 'knows' about the OS case insensitivity, but for some reason it fails on the rename if the user used upper case letters on the RENAME _and_ on the CREATE, _and_ this is done within the same session. Creating the table, exiting the mysql console, re-entering the console and then do the rename works just fine. -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Suspected Bug
Roger, Friday, February 15, 2002, 1:32:13 PM, you wrote: RB * Egor Egorov FL = Fred Lovine FL CREATE TABLE X1 (x smallint); FL insert into X1 values(5); FL RENAME TABLE X1 TO X2; FL is producing the following error: FL 7 - Error on rename '.\db\X1.MY1' to '.\db\X2.MYI' (Errcode: 13) FL The problem seems to be caused by using uppercase table FL names. The problem does not happen when the table names FL are all lowercase. $ perror 13 Error code 13: Permission denied When you rename table, you also rename files that are appropriate to this table. You don't have file permission to rename files in mysql data dir. RB Do you mean this is the desired behaviour? RB Also, this does not explain why it only happens when the name is uppercase. I have tested RENAME TABLE on RedHat with uppercase table name and it works fine. On Windows 2000 it works when i rename table from lowercase table name to uppercase table name but not otherwise. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Suspected Bug
The RENAME syntax is correct, please see the following documentation: http://www.mysql.com/doc/R/E/RENAME_TABLE.html -Original Message- From: SubbaReddy M [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 5:23 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Suspected Bug I am afraid it's not a bug, rather a syntax error. please follow the syntax to rename the table: mysql alter table X1 rename as X2; Regards, ~ SubbaReddy .M - Original Message - From: SubbaReddy M [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, February 15, 2002 3:46 PM Subject: Re: Suspected Bug Yeah, for me also find error. -- -- -- mysql create table X1(name varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into X1 values(5); Query OK, 1 row affected (0.01 sec) mysql RENAME TABLE X1 TO X2; ERROR 1064: You have an error in your SQL syntax near 'RENAME TABLE X1 TO X2' at line 1 mysql -- -- Regards, ~ SubbaReddy .M Sr. Programmer, Frontlinesoft, Hyderabad http://www.frontlinesoft.com ICQ: 56093095 - Original Message - From: Fred Lovine [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 15, 2002 12:23 PM Subject: Suspected Bug MySQL is awesome, but I think I found a bug. The following script: CREATE TABLE X1 (x smallint); insert into X1 values(5); RENAME TABLE X1 TO X2; is producing the following error: 7 - Error on rename '.\db\X1.MY1' to '.\db\X2.MYI' (Errcode: 13) The problem seems to be caused by using uppercase table names. The problem does not happen when the table names are all lowercase. MySQL version: 3.23.47-nt Running on Windows 2000 Pro Feel free to contact me if there are any questions. Server Variables Variable CLW Laptop back_log 50 basedir C:\CLW\MySQL\ binlog_cache_size 32768 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert ON connect_timeout 5 datadir C:\CLW\MySQL\data\ delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 1800 have_bdb NO have_gemini NO have_innodb NO have_isam YES have_raid NO have_openssl NO init_file interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 8384512 language C:\CLW\MySQL\share\english\ large_files_support ON log OFF log_update OFF log_bin OFF log_slave_updates OFF log_long_queries OFF long_query_time 10 low_priority_updates OFF lower_case_table_names 1 max_allowed_packet 130048 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connections 100 max_connect_errors 10 max_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_sort_length 1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_max_extra_sort_file_size 256 myisam_max_sort_file_size 2047 myisam_recover_options 0 myisam_sort_buffer_size 8388608 net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 open_files_limit 0 pid_file C:\CLW\MySQL\data\laptop.pid port 3306 protocol_version 10 record_buffer 131072 record_rnd_buffer 131072 query_buffer_size 0 safe_show_database OFF server_id 0 slave_net_timeout 3600 skip_locking ON skip_networking OFF skip_show_database OFF slow_launch_time 2 socket MySQL sort_buffer 2097144 sql_mode 0 table_cache 64 table_type MYISAM thread_cache_size 0 thread_stack 65536 transaction_isolation READ-COMMITTED timezone Eastern Standard Time tmp_table_size 33554432 tmpdir C:\WINNT\TEMP\ version 3.23.47-nt wait_timeout 28800 Fred Lovine [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Suspected Bug
I think there are a couple of bugs here: First, per the documentation, 6.1.3 Case Sensitivity in Names (http://www.mysql.com/doc/N/a/Name_case_sensitivity.html), the Windows mysqld is starting with -O lower_case_table_names=1 by default, so all filenames should be converted to lowercase automatically. The create and insert statements are performing as they should. But the RENAME is not, RENAME will create a table in upper case. I believe the first bug is that the RENAME is not respecting the lower_case_table_names option. Second, the error, 7 - Error on rename '.\db\X1.MY1' to '.\db\X2.MYI' (Errcode: 13) is with the index file. After playing around a bit, the following SQL does work: CREATE TABLE X1 (x smallint); insert into X1 values(5); FLUSH TABLES; RENAME TABLE X1 TO X2; My guess is that RENAME is not closing the index file, but FLUSH takes care of that. And the workaround as stated before is to just use lowercase table names. BTW, the following works fine: CREATE TABLE X1 (x smallint); insert into X1 values(5); RENAME TABLE x1 TO x2; -- note names are lowercase --Fred -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 7:20 AM To: SubbaReddy M; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Suspected Bug Once tables are CREATEd, the name is listed in SHOW tables using lower case characters, regardless of the case used in the CREATE command. Using upper case table names in RENAME results in the error. Workaround: Using lower case equivalent table names works, regardless of the case used in the CREATE command. Windows does not differentiate between X1 and x1 as table/filenames. *nix does. Perhaps CREATE has been Window-ised but RENAME has not (yet). Regards, =dn WinNT 4.0 SP6a, MySQL vn 3.23.40-nt - Original Message - From: SubbaReddy M [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 15 February 2002 10:16 Subject: Re: Suspected Bug Yeah, for me also find error. -- -- -- mysql create table X1(name varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into X1 values(5); Query OK, 1 row affected (0.01 sec) mysql RENAME TABLE X1 TO X2; ERROR 1064: You have an error in your SQL syntax near 'RENAME TABLE X1 TO X2' at line 1 mysql -- -- Regards, ~ SubbaReddy .M Sr. Programmer, Frontlinesoft, Hyderabad http://www.frontlinesoft.com ICQ: 56093095 - Original Message - From: Fred Lovine [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, February 15, 2002 12:23 PM Subject: Suspected Bug MySQL is awesome, but I think I found a bug. The following script: CREATE TABLE X1 (x smallint); insert into X1 values(5); RENAME TABLE X1 TO X2; is producing the following error: 7 - Error on rename '.\db\X1.MY1' to '.\db\X2.MYI' (Errcode: 13) The problem seems to be caused by using uppercase table names. The problem does not happen when the table names are all lowercase. MySQL version: 3.23.47-nt Running on Windows 2000 Pro Feel free to contact me if there are any questions. Server Variables Variable CLW Laptop back_log 50 basedir C:\CLW\MySQL\ binlog_cache_size 32768 character_set latin1 character_sets latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert ON connect_timeout 5 datadir C:\CLW\MySQL\data\ delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 flush OFF flush_time 1800 have_bdb NO have_gemini NO have_innodb NO have_isam YES have_raid NO have_openssl NO init_file interactive_timeout 28800 join_buffer_size 131072 key_buffer_size 8384512 language C:\CLW\MySQL\share\english\ large_files_support ON log OFF log_update OFF log_bin OFF log_slave_updates OFF log_long_queries OFF long_query_time 10 low_priority_updates OFF lower_case_table_names 1 max_allowed_packet 130048 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connections 100 max_connect_errors 10 max_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_sort_length 1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_max_extra_sort_file_size 256 myisam_max_sort_file_size 2047 myisam_recover_options 0 myisam_sort_buffer_size 8388608 net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 open_files_limit 0 pid_file C:\CLW\MySQL\data\laptop.pid port 3306 protocol_version 10 record_buffer 131072 record_rnd_buffer 131072
