MySql 4.0.20 : Parenthesis number problem ?
Hi, Using Mysql 4.0.20-standard-log since a few days I encontred an error that I had never seen before. Here is what I have done : 1_ create an innodb table : create table Test (JobStatus varchar(32) ) type = innodb; 2_ Run the following request : select JobStatus from Test where ( JobStatus = 'xyz' ) ; This imply a mysqld crash (and restart) each time I run the request ! I encontred this problem with 4.0.20-standard-log running on Solaris 8 and on Solaris 9. If I made a try on the previous version I used (4.0.13, on Solaris 8) everything work fine. If I decrease the number of parenthesis to 2 * 88, It run If I increase the number of parenthesis to higher than 2 * 88 mysqld crash Is there a bug on 4.0.20 ? or do I miss-configure a variable ? In a hostname.err we find : mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=67108864 read_buffer_size=1044480 max_used_connections=0 max_connections=128 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1506815 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. -e Number of processes running now: 0 040614 13:33:33 mysqld restarted Thans for any help Regards -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) email: mailto:[EMAIL PROTECTED] Tel : 04 72 69 42 18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query string make mysqld crash (mysqld 4.0.20)
Hi, Using Mysql 4.0.20-standard-log since a few days I encontred an error that I had never seen before. Here is what I have done : 1_ create an innodb table : create table Test (JobStatus varchar(32) ) type = innodb; 2_ Run the following request : select JobStatus from Test where ( JobStatus = 'xyz' ) ; This imply a mysqld crash (and restart) each time I run the request ! I encontred this problem with 4.0.20-standard-log running on Solaris 8 and on Solaris 9. If I made a try on the previous version I used (4.0.13, on Solaris 8) everything work fine. If I decrease the number of parenthesis to 2 * 88, It run If I increase the number of parenthesis to higher than 2 * 88 mysqld crash Is there a bug on 4.0.20 ? or do I miss-configure a variable ? In a hostname.err we find : mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=67108864 read_buffer_size=1044480 max_used_connections=0 max_connections=128 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1506815 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. -e Number of processes running now: 0 040614 13:33:33 mysqld restarted Thans for any help Regards -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) email: mailto:[EMAIL PROTECTED] Tel : 04 72 69 42 18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
key_buffer or key_buffer_size
Hi, Can somebody tell me if 'key_buffer' and 'key_buffer_size' are the same variables ? Perhaps it is just a question of syntax, I mean, in the my.cnf you write key_buffer=100M or you write set-variable = key_buffer_size=100M Same question for sort_buffer and sort_buffer_size, thread_cache and thread_cache_size, innodb_buffer_pool and innodb_buffer_pool_size etc. -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) email: mailto:[EMAIL PROTECTED] Tel : 04 72 69 42 18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions on index and 'order by'
Thank you Sasha for your answer.0 I still miss-understand index and 'order by'. After creating multiples index on a 'Test' table, the current problem is that mysql optimizer doesn't choose the right index. Here is a small example mysql desc Test ; +-+-+--+-+-+ | Field | Type| Null | Key | Default | +-+-+--+-+-+ | JobStatus | varchar(32) | YES | MUL | NULL| | SubmitDate | datetime| YES | | NULL| | SpawnDate | datetime| YES | | NULL| | TerminationDate | datetime| YES | | NULL| +-+-+--+-+-+ with 3 multiple indexes : KEY K00(JobStatus, SubmitDate) KEY K01(JobStatus, SpawnDate) KEY K02(JobStatus, TerminationDate) When I run a request like : select ... order by SubmitDate Mysql doesn't choose the right index (see Using filesort needed) mysql explain select * from Test \ where (JobStatus = 'RUNNING') order by SubmitDate ; +---+--+---+--+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+---+-+ | Test | ref | K00,K01,K02 | K02 | 33 | const | 20600 | Using where; Using filesort | +---+--+---+--+-+---+---+-+ if I force the index choice with : use index (K00), I got : mysql explain select * from Test use index (K00) \ where (JobStatus = 'RUNNING') order by SubmitDate ; +---+--+---+--+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+---+-+ | Test | ref | K00 | K00 | 33 | const | 30084 | Using where | +---+--+---+--+-+---+---+-+ I understand that the fisrt choice is done according to the number of rows to examine (20600 vs 30084), but I checked, with a small external program, that the second solution (use index (K00)) is faster (~ x2); I try to enforce the optimizer by setting max-seeks-for-key but no result ! What is wrong with my indexes ? Regards -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) email: mailto:[EMAIL PROTECTED] Tel : 04 72 69 42 18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Questions on index and 'order by'
I try to understand index usage for 'order by' but that's seemn hard to me. Here is what I have tried : 1_ About documentation I have read carefully related documentation (7.2.9 How MySQL Optimizes ORDER BY) but I don't understand it. - What is the difference betwween 'key1' and 'key_part1' ? - Why is it written ? : The following query will use the index to resolve the ORDER BY SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2 The following query cannot use indexes to resolve the ORDER BY SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2; 2_ I also tried a small example (table Test), with 2 fields and 2 (multiple) indexes create table Test(Name varchar(64), Adress varchar(128), \ index K0(Name(16), Adress(16)), \ index K1(Adress(16), Name(16)) ) type = INNOBASE ; What are the indexes needed for 'orber by' in the request select * from Test where Name='n1' order by Adress ; If I ask explain, I got 'Using filesort' in Extra column information mysql explain select * from Test where Name='n1' order by Adress ; +---+--+---+--+-+---+-- +-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+-- +-+ | Test | ref | K0| K0 | 17 | const |1 | Using where; Using filesort | +---+--+---+--+-+---+-- +-+ Thanks for any help - Bernard Chambon IN2P3 / CNRS [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
log-bin file rotation questions
Hello, I set up a replication architecture with MySql everything works fine. Now I concentrate on log-bin file and I have a question I have understood (I hope... ) -that I can set a max_binlog_size, -that a new file (extension n+1) will be used while reaching max_binlog_size, -that the slave will know the new log-bin filename and position so doing a 'purge master logs' will remove previous files ok, but what is the high limit for the extension value ? This question because a set up a mysql server, running for months and months without stopping, and with a lot of insert/update In over words how to set a initial extension value WITHOUT stopping master nor slave ? According to me, the only solution is doing a 'reset master' with a lot of care like : (lock master; sync slave; reset master; reset slave; unlock master) But doing a lock master seem to me dangerous for many reasons Do I misunderstood something ? Regards -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) email: mailto:[EMAIL PROTECTED] Tel : 04 72 69 42 18 - 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: Does replication works with innodb ?
Hello, Sorry for disturbing you. In fact replication works fine whith Innodb and Mysql 3.23.x or Mysql 4.x My errors were syntax error in my.cnf like for example : replicate-do-db=bqsql ; instead of replicate-do-db=bqsql My only complain is that mysql give me no error at startup I also notice that 'slave_net_timeout' wasn't recognized (unrecognized option `--slave_net_timeout=300') Now I concentrate on 'bin-log rotation problem' Regards. Jeremy Zawodny wrote: On Tue, Jan 07, 2003 at 12:29:31PM +0100, Chambon wrote: The problem is that NOTHING happens on the slave I mean, when I do insert or update something on the master, nothing happens on the slave. The table remains still empty on the slave. The questions are - Does replication work with Mysql 3.23 and Innodb Yes. - Is-it a Mysql version problem (3.23.54) ? No. - Is-it a table type problem (Innodb) ? No. - Do I miss understand something (probably) ? Perhaps. What does SHOW SLAVE STATUS on the slave say? Any errors loged in the error log? Jeremy -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) email: mailto:[EMAIL PROTECTED] Tel : 04 72 69 42 18 - 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
Does replication works with innodb ?
Hello, I am using Mysql and Innodb tables since one year with great success. Everything works fine. Now I try to set up a master/slave replication architecture. master slave pc Linux rh 6.2 pc Linux rh 6.2 version 3.23.54 version 3.23.54 I have read the documentation carrefully, setup config file (my.cnf). I check everything with show master/show slave, slave stop/start, show processlist, etc, etc. I also check that bin-log file register actions on master The problem is that NOTHING happens on the slave I mean, when I do insert or update something on the master, nothing happens on the slave. The table remains still empty on the slave. The questions are - Does replication work with Mysql 3.23 and Innodb - Is-it a Mysql version problem (3.23.54) ? - Is-it a table type problem (Innodb) ? - Do I miss understand something (probably) ? Best regards. -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) email: mailto:[EMAIL PROTECTED] Tel : 04 72 69 42 18 - 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
Question about replication
Hello I am using Mysql and Innodb tables since one year with great success. Everything works fine. Now I try to set up a master/slave replication architecture. master slave pc Linux rh 6.2 pc Linux rh 6.2 version 3.23.47 version 3.23.54 I have read the documentation carrefully. I got no erro on the master nor the slave but nothing run, I mean I got no data on the slave. The only indication is that doing : 'load table xxx from master' doesn't work I got the following : ERROR 1189: Net error reading from master and the .err file says : 030106 16:21:10 create_table_from_dump::failed in handler::net_read_dump() 030106 16:21:10 fetch_nx_table: failed on create table I can check that with this instruction, the table is created, but it remains empty (no data) I have checked that the bin-log file is updated on the master, that the slave thread is running. Show slave status is ok. Any idea ? Best regards. -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) email: mailto:[EMAIL PROTECTED] Tel : 04 72 69 42 18 - 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
auto_increment and range or highest value
Hi, I'am using autoincrement field with innodb table. (Mysql 3.23.47-log on Linux ppc) Two questions 1_ how to set a range ? Is the only solution chosing betwwen tinyint, smallint, ..., bigint I over words : I want a range from 1 to 999 for example , how can I do ? 2_ What's happens when the highest value is reached I have made a try with : create table Generator (Sequence smallint(7) zerofill unique primary key not null auto_increment ) type=innodb ; insert into Generator values(null); update Generator set Sequence=LAST_INSERT_ID(Sequence+1) ; Everything works fine, ok now what happens for the highest value ? update Generator set Sequence=65535 ; select * from Generator ; +--+ | Sequence | +--+ | 0065535 | +--+ update Generator set Sequence=LAST_INSERT_ID(Sequence+1) ; select * from Generator ; +--+ | Sequence | +--+ | 0065535 | +--+ Here something doesn't work, or I probably not undestood ! Thank you for any help -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) Tél : 04 72 69 42 18 http://www.in2p3.fr/CC - 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
mysql_use_result, out of sync, Mysql
Hello What do to if mysql_use_result return NULL ? I can't understand ? I have only one query by connection, of course. I talk about SELECT statement in my query (not INSERT or anything else) Mysql run on linux. From time to time mysql_use_result() return NULL. I throw and catch an exception, then I try to make another query an I got 'out of sync' As mentionned in documentation, I use mysql_free_result() (even on a NULL pointer) while cathing the exception but nothing change. I have also try another mysql_use_result(), mysql_fetch_row() and mysql_free_result() in a dummy function. (by dummy, i mean nusefull) but I still got 'out of sync' Perhaps, the solution is to close and open a new connection in such a case ?. But before that, I want to understand. Thank you for your help Regards. -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) Tél : 04 72 69 42 18 http://www.in2p3.fr/CC - 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: Lost connection, C API
Heikki Tuuri wrote: Hi! Are you running mysqld under the safe_mysqld script, which automatically restarts mysqld after a crash? Yes, mysqld started with safe_mysqld script If yes, look at the 'hostname'.err file and send its contents to me. Nothing happens in the hostname.err (I started mysqld this morning at 11:45) 020107 11:45:08 mysqld started 020107 11:45:08 Warning: setrlimit couldn't increase number of open files to more than 1024 020107 11:45:08 Warning: Changed limits: max_connections: 512 table_cache: 251 020107 11:45:10 InnoDB: Started /opt/mysql/libexec/mysqld: ready for connections With ps ..., I confirm that mysqld processes have NOT restarted (running since 11:45) Or, if you run mysqld from the command prompt, capture what mysqld prints to the command prompt window. If mysqld does not crash, then the problem is probably some client/server issue. Regards, Heikki Has we got a network error yesterday, I have run another test this night with the following modifications : max_connect_errors=65535 skip-host-cache connection to 'localhost' (the client and the serveur run on the same machine) I have got errors like : MySQL server has gone away (12 occurences) Commands out of sync; You can't run this command now (85 occurences) Lost connection to MySQL server during query (only one occurence) The transaction overload is about : 200848 Insert 486205 Update 334945 Select for 8 hours Before restarting the 2nd test I restart mysqld (yesterday at 22:28) so here is the hotname.err --- 020107 11:45:08 mysqld started 020107 11:45:08 Warning: setrlimit couldn't increase number of open files to more than 1024 020107 11:45:08 Warning: Changed limits: max_connections: 512 table_cache: 251 020107 11:45:10 InnoDB: Started /opt/mysql/libexec/mysqld: ready for connections 020107 22:27:45 /opt/mysql/libexec/mysqld: Normal shutdown 020107 22:27:45 InnoDB: Starting shutdown... 020107 22:27:58 InnoDB: Shutdown completed 020107 22:27:58 /opt/mysql/libexec/mysqld: Shutdown Complete 020107 22:27:58 mysqld ended 020107 22:28:25 mysqld started 020107 22:28:26 Warning: setrlimit couldn't increase number of open files to more than 1024 020107 22:28:26 Warning: Changed limits: max_connections: 512 table_cache: 251 020107 22:28:27 InnoDB: Started /opt/mysql/libexec/mysqld: ready for connections Status information: Current dir: /opt/mysql/var/ Current locks: lock: 83f3ed0: lock: 8396308: lock: 8395a28: lock: 838cfa0: lock: 8390c00: lock: 838cf00: lock: 838ce60: lock: 838c588: lock: 837e8b8: lock: 837ee28: lock: 837ed90: lock: 83746e0: key_cache status: blocks used: 0 not flushed: 0 w_requests: 0 writes: 0 r_requests: 0 reads: 0 handler status: read_key: 5704 read_next:1706 read_rnd47 read_first:394 write:1447 delete 0 update:465 Table status: Opened tables: 26 Open tables: 20 Open files: 2 Open streams: 0 -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) Tél : 04 72 69 42 18 http://www.in2p3.fr/CC - 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: Lost connection, C API
Hello, No there's no thread in my application, the program use fork but of course a new connection is create after each fork() Today before running another test I do the following - drop then create the tables - increase the limit for opened file from 1024 to 4096 I run a test for 2 hours and I got : - Lost connection to MySQL (708) - Can't connect to MySQL server (49) //error while trying new connection 49 errors in 6 seconds in from 14:07:16 to 14:07:22 (due to a burst of fork() ) - 'ip_adress' is not allowed to connect to this MySQL Server (4) //error while trying new connection - Commands out of sync (6) But apart of these errors I have made many successful insert (4457) , update (11594), select (25017) that why I am confused and I don't know what to change ! Best regards Heikki Tuuri wrote: Hi! Then it looks like a problem in the communication or in your client test program. The following error Commands out of sync; You can't run this command now (85 occurences) means that you have issued C API commands to a single MySQL connection in a wrong order. If your application has several client threads, are you sure you create one connection to each thread, and issue the C API commands in the right order? Sometimes people try to use a single connection for several client threads, and get the error above. Best regards, Heikki Tuuri Innobase Oy -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) email: mailto:[EMAIL PROTECTED] Tel : 04 72 69 42 18 - 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
Mysql, innodb, linux problems
Hello, Hope it is a good place for the following question I use Mysql (3.23.39) and InnoDB tables on Linux (2.2.17-14smp) and I got deadlock (In suppose it is a deadlock) when I do intensive work. intensive work = one insert + one update + 2 selects (simultaneously, with 4 differents processes, on the same table) I got deadlock = My processes went down (after the timeout I had set) and if I try an interactive request on the table (like select count(*) from ... I never got the answer. If I do a 'mysqladmin processlist', I see about 10 threads of insert/update/select worst, if I do a 'mysqladmin kill ...' to kill some thread, nothing happens The only way to recover, is to kill the mysqld processes and restart mysql Thank you for your help -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) Tél : 04 72 69 42 18 http://www.in2p3.fr/CC - 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: Mysql, innodb, linux problems
Hello, As recommended I upgrade from 3.23.39 to 3.23.47 and now everything works very well Intensive insert with simultaneous update and several simultaneous select on the same table works perfectly on InnoDB tables Regards -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) email: mailto:[EMAIL PROTECTED] Tel : 04 72 69 42 18 - 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
INNOBASE, autocommit = 0, and C API ( mysql_real_query )
Hello, I am using Innobase with MySql version 3.23.39 under Linux and want to run WITHOUT autocommit mode (= autocommit=0 for using commit/roolback instructions) I have made a test whith 2 INTERACTIVE MySql sessions and this works fine. BUT, when I use the C API (mysql_real_query), it DOESN't work. I mean the autocommit=0 is NOT taken in account //-- My C code looks like : // set autocommit = 0 mysql_real_query(connection, set autocommit = 0, the_length ); // begin mysql_real_query(connection, begin, the_length ); // insert mysql_real_query(connection, insert into , the_length ); // NO commit is DONE, for test purpose //- END of My C code When I check the table, in an interaction session, my insertion is available! I mean a request like 'select... ' show the NEW insertion ! Why ? NO commit has been DONE! I have made several other try, like reading a config file (and a 'set-variable = autocommit=0' in it) But the probleme doesn't change Furthermore,the server has a global transaction-isolation option set to serializable. We tried to force the autocommit=0 through an init-file. The problem is still there ! The autocommit option seems to be an session command. Is there any way to set it for the whole server ? Thank you for your help Sincerely. -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) Tél : 04 72 69 42 18 http://www.in2p3.fr/CC - 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
