Re: Performance issue old server witn mysql 4 vs new server with mysql 5 and old server WINS!
On Sun, Feb 13, 2011 at 11:40 PM, Andrés Tello mr.crip...@gmail.com wrote: I have a test process, which runs in the old server in 35 seconds, the new server runs the same process in 110. There is a change of version from mysql 4.1.22 to 5.1.22. We were stuck at 5.1.22 because higher version give us another issules like encoding, case sensitivity... Hi. For 5.0 or 5.1 you should really use the most recent versions. 5.1.22 is beta software, the first stable (GA) release was 5.1.30. For best performance, you might be better off going directly to 5.5 series. henrik -- henrik.i...@avoinelama.fi +358-40-5697354 skype: henrik.ingo irc: hingo www.openlife.cc My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performance issue old server witn mysql 4 vs new server with mysql 5 and old server WINS!
Yup, I'm doing clean tests,lshutdown, and reload mysql each test. The raid setup is similar, Faster is raid1 with 10k harddisk, slower is raid 10 with 15k. Metrics show Old raid Secuecial writting 1G: 533 mb/s (using dd if=/dev/zero of=1G bs=1024 count=102400) Secuencial reading 1G: 500 mb/s New raid Writting: 500 mb/s Reading 800 mb/s Average... On Wed, Feb 16, 2011 at 4:33 PM, Singer X.J. Wang w...@singerwang.comwrote: Dumb questions 1) Are you doing clean tests? Shutdown and restart MySQL after each test? 2) Is the RAID setup similar on the servers? Are you doing something like RAID10 on the old and RAID6 on the new? Singer On Sun, Feb 13, 2011 at 16:40, Andrés Tello mr.crip...@gmail.com wrote: I have a test process, which runs in the old server in 35 seconds, the new server runs the same process in 110. There is a change of version from mysql 4.1.22 to 5.1.22. We were stuck at 5.1.22 because higher version give us another issules like encoding, case sensitivity... I really belive that the issue is regarding the mysql server there is extensive information about my setup... I have more processors, more memory, more disk speed, but lower results... T_T, because the wtf is long forgotten. hdparm -tT /dev/sda /dev/sda: Timing cached reads: 13392 MB in 2.00 seconds = 6699.90 MB/sec Timing buffered disk reads: 174 MB in 3.02 seconds = 57.64 MB/sec free total used free sharedbuffers cached Mem: 16631296 16065356 565940 0 83148 13415520 -/+ buffers/cache:2566688 14064608 Swap: 16779852128 16779724 uname -r 2.6.16.21-0.8-bigsmp Your MySQL connection id is 21 to server version: 4.1.22-standard-log more /proc/cpuinfo | grep -e processor\|name processor : 0 model name : Intel(R) Xeon(R) CPU5160 @ 3.00GHz processor : 1 model name : Intel(R) Xeon(R) CPU5160 @ 3.00GHz processor : 2 model name : Intel(R) Xeon(R) CPU5160 @ 3.00GHz processor : 3 model name : Intel(R) Xeon(R) CPU5160 @ 3.00GHz grep -v ^# /etc/my.cnf | sed '/^$/d' [client] port= 3306 socket = /tmp/mysql.sock [mysqld] innodb_file_per_table port= 3306 socket = /tmp/mysql.sock back_log = 50 max_connections = 100 max_connect_errors = 10 table_cache = 2048 max_allowed_packet = 256M binlog_cache_size = 16M max_heap_table_size = 64M sort_buffer_size = 16M join_buffer_size = 32M thread_cache = 8 thread_concurrency = 8 query_cache_size = 256M query_cache_limit = 32M ft_min_word_len = 4 memlock default_table_type = INNODB thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 256M log_slow_queries long_query_time = 2 log_long_format tmpdir = /tmp key_buffer_size = 128M read_buffer_size = 64M read_rnd_buffer_size = 128M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover skip-bdb innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 2G innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 32 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 10240 New Server, which happnes to be 2x SLOWER! hdparm -tT /dev/sda ; free ; uname -r ; cat /proc/cpuinfo | grep -e processor\|name /dev/sda: Timing cached reads: 5858 MB in 2.00 seconds = 2932.17 MB/sec Timing buffered disk reads: 1304 MB in 3.00 seconds = 434.06 MB/sec total used free sharedbuffers cached Mem: 330086242097924 30910700 0 21308 76024 -/+ buffers/cache:2000592 31008032 Swap: 8388604 08388604 uname -r 2.6.34.7-0.7-desktop processor : 0 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 1 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 2 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 3 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 4 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 5 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 6 model name : Intel(R) Xeon(R) CPU
Performance issue old server witn mysql 4 vs new server with mysql 5 and old server WINS!
I have a test process, which runs in the old server in 35 seconds, the new server runs the same process in 110. There is a change of version from mysql 4.1.22 to 5.1.22. We were stuck at 5.1.22 because higher version give us another issules like encoding, case sensitivity... I really belive that the issue is regarding the mysql server there is extensive information about my setup... I have more processors, more memory, more disk speed, but lower results... T_T, because the wtf is long forgotten. hdparm -tT /dev/sda /dev/sda: Timing cached reads: 13392 MB in 2.00 seconds = 6699.90 MB/sec Timing buffered disk reads: 174 MB in 3.02 seconds = 57.64 MB/sec free total used free sharedbuffers cached Mem: 16631296 16065356 565940 0 83148 13415520 -/+ buffers/cache:2566688 14064608 Swap: 16779852128 16779724 uname -r 2.6.16.21-0.8-bigsmp Your MySQL connection id is 21 to server version: 4.1.22-standard-log more /proc/cpuinfo | grep -e processor\|name processor : 0 model name : Intel(R) Xeon(R) CPU5160 @ 3.00GHz processor : 1 model name : Intel(R) Xeon(R) CPU5160 @ 3.00GHz processor : 2 model name : Intel(R) Xeon(R) CPU5160 @ 3.00GHz processor : 3 model name : Intel(R) Xeon(R) CPU5160 @ 3.00GHz grep -v ^# /etc/my.cnf | sed '/^$/d' [client] port= 3306 socket = /tmp/mysql.sock [mysqld] innodb_file_per_table port= 3306 socket = /tmp/mysql.sock back_log = 50 max_connections = 100 max_connect_errors = 10 table_cache = 2048 max_allowed_packet = 256M binlog_cache_size = 16M max_heap_table_size = 64M sort_buffer_size = 16M join_buffer_size = 32M thread_cache = 8 thread_concurrency = 8 query_cache_size = 256M query_cache_limit = 32M ft_min_word_len = 4 memlock default_table_type = INNODB thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 256M log_slow_queries long_query_time = 2 log_long_format tmpdir = /tmp key_buffer_size = 128M read_buffer_size = 64M read_rnd_buffer_size = 128M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover skip-bdb innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 2G innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 32 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 10240 New Server, which happnes to be 2x SLOWER! hdparm -tT /dev/sda ; free ; uname -r ; cat /proc/cpuinfo | grep -e processor\|name /dev/sda: Timing cached reads: 5858 MB in 2.00 seconds = 2932.17 MB/sec Timing buffered disk reads: 1304 MB in 3.00 seconds = 434.06 MB/sec total used free sharedbuffers cached Mem: 330086242097924 30910700 0 21308 76024 -/+ buffers/cache:2000592 31008032 Swap: 8388604 08388604 uname -r 2.6.34.7-0.7-desktop processor : 0 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 1 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 2 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 3 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 4 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 5 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 6 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 7 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 8 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 9 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 10 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz processor : 11 model name : Intel(R) Xeon(R) CPU E7450 @ 2.40GHz Server version: 5.1.52-log Source distribution grep -v ^# /etc/my.cnf | sed '/^$/d' [client] port = 3306 socket= /tmp/mysqld.sock [safe_mysqld] err_log = /mysql/logs/mysql.err [mysqld] skip-external-locking server_id = 9000 user = mysql port
Mysql 4 and or Partitions.
Ok... I solved my mistery of the slow 22G table rebuild the kernel to support memory and now things are working. But still quite slow. I do sum() operations over the 22G table. I'm using the latest version of mysql 4... (I WILL migrate soon, I promise), one thing I have notice, is that operations over that 22G table, seasuring the I/O with iostat never surpass the 23mb/s for reading... even if I test the IO of the array, it can easily give me 300mb/s for reads... and like 150 for random reads... first of all, is there any way to squeeze more speed out from mysql 4? No... I imagined that... So, my next step is migate to a newer version of mysql ( YEY ^_^ )... I have read a few about partitions. The specific query I'm making is a query which do a sum filtered over a date field. What would be the best approach to partition this table? Can I mix innodb with partioned tables and still have acid compliance? The split in several partitions creates more lecture threads or how parelelization over partitions works? And last, does mysql 4 support partitions? XD tia.
RE: Mysql 4 and or Partitions.
Use indexes before thinking about partitions. -Original Message- From: Andrés Tello [mailto:mr.crip...@gmail.com] Sent: Thursday, July 15, 2010 8:44 AM To: mysql Subject: Mysql 4 and or Partitions. Ok... I solved my mistery of the slow 22G table rebuild the kernel to support memory and now things are working. But still quite slow. I do sum() operations over the 22G table. I'm using the latest version of mysql 4... (I WILL migrate soon, I promise), one thing I have notice, is that operations over that 22G table, seasuring the I/O with iostat never surpass the 23mb/s for reading... even if I test the IO of the array, it can easily give me 300mb/s for reads... and like 150 for random reads... first of all, is there any way to squeeze more speed out from mysql 4? No... I imagined that... So, my next step is migate to a newer version of mysql ( YEY ^_^ )... I have read a few about partitions. The specific query I'm making is a query which do a sum filtered over a date field. What would be the best approach to partition this table? Can I mix innodb with partioned tables and still have acid compliance? The split in several partitions creates more lecture threads or how parelelization over partitions works? And last, does mysql 4 support partitions? XD tia. This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mysql 4 to 5
Hi all, I recently migrated a script called perldesk from mysql 4 to mysql 5. When I envoke the script from the web, I am now getting an error. I suspect its a version syntax thing. Here is what I see: Couldn't execute statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call = '1307' ORDER BY id' at line 1; stoppedContent-type: text/html PerlDesk: Script Error Perldesk was unable to launch due to the following errors: 1 at include/staff_subs.cgi line 1401. line 1401 is the third line below: $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; Any help on how to fix this would be appreciated, -G
Re: Mysql 4 to 5
One problem you might be having is the column named 'call'.. It's a keyword, so perhaps you might get away with backticking it?ie. `call` Also, you shouldn't need that final semi-colon inside your statement string.. I have had preparedstatement interfaces give me grief about that. As I recall, DBI doesn't attempt to validate the statement until execute time. $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; - md On Sun, Jul 11, 2010 at 7:50 PM, Grant Peel gp...@thenetnow.com wrote: Hi all, I recently migrated a script called perldesk from mysql 4 to mysql 5. When I envoke the script from the web, I am now getting an error. I suspect its a version syntax thing. Here is what I see: Couldn't execute statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call = '1307' ORDER BY id' at line 1; stoppedContent-type: text/html PerlDesk: Script Error Perldesk was unable to launch due to the following errors: 1 at include/staff_subs.cgi line 1401. line 1401 is the third line below: $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; Any help on how to fix this would be appreciated, -G -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql 4 to 5
- Original Message - From: Michael Dykman mdyk...@gmail.com To: Grant Peel gp...@thenetnow.com Cc: mysql@lists.mysql.com Sent: Sunday, July 11, 2010 9:20 PM Subject: Re: Mysql 4 to 5 One problem you might be having is the column named 'call'.. It's a keyword, so perhaps you might get away with backticking it?ie. `call` Also, you shouldn't need that final semi-colon inside your statement string.. I have had preparedstatement interfaces give me grief about that. As I recall, DBI doesn't attempt to validate the statement until execute time. $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; - md On Sun, Jul 11, 2010 at 7:50 PM, Grant Peel gp...@thenetnow.com wrote: Hi all, I recently migrated a script called perldesk from mysql 4 to mysql 5. When I envoke the script from the web, I am now getting an error. I suspect its a version syntax thing. Here is what I see: Couldn't execute statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call = '1307' ORDER BY id' at line 1; stoppedContent-type: text/html PerlDesk: Script Error Perldesk was unable to launch due to the following errors: 1 at include/staff_subs.cgi line 1401. line 1401 is the third line below: $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; Any help on how to fix this would be appreciated, -G -- - michael dykman - mdyk...@gmail.com May the Source be with you. Thanks Mike, I will try that :-) -G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql 4 to 5
- Original Message - From: Grant Peel gp...@thenetnow.com To: Michael Dykman mdyk...@gmail.com Cc: mysql@lists.mysql.com Sent: Sunday, July 11, 2010 9:25 PM Subject: Re: Mysql 4 to 5 - Original Message - From: Michael Dykman mdyk...@gmail.com To: Grant Peel gp...@thenetnow.com Cc: mysql@lists.mysql.com Sent: Sunday, July 11, 2010 9:20 PM Subject: Re: Mysql 4 to 5 One problem you might be having is the column named 'call'.. It's a keyword, so perhaps you might get away with backticking it?ie. `call` Also, you shouldn't need that final semi-colon inside your statement string.. I have had preparedstatement interfaces give me grief about that. As I recall, DBI doesn't attempt to validate the statement until execute time. $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; - md On Sun, Jul 11, 2010 at 7:50 PM, Grant Peel gp...@thenetnow.com wrote: Hi all, I recently migrated a script called perldesk from mysql 4 to mysql 5. When I envoke the script from the web, I am now getting an error. I suspect its a version syntax thing. Here is what I see: Couldn't execute statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call = '1307' ORDER BY id' at line 1; stoppedContent-type: text/html PerlDesk: Script Error Perldesk was unable to launch due to the following errors: 1 at include/staff_subs.cgi line 1401. line 1401 is the third line below: $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; Any help on how to fix this would be appreciated, -G -- - michael dykman - mdyk...@gmail.com May the Source be with you. Thanks Mike, I will try that :-) -G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gp...@thenetnow.com Mike, UPDATE: THe backtick worked, thanks for the help! -Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql 4 to 5
Grant Peel wrote: Hi all, I recently migrated a script called perldesk from mysql 4 to mysql 5. When I envoke the script from the web, I am now getting an error. I suspect its a version syntax thing. Here is what I see: Couldn't execute statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call = '1307' ORDER BY id' at line 1; stoppedContent-type: text/html PerlDesk: Script Error Perldesk was unable to launch due to the following errors: 1 at include/staff_subs.cgi line 1401. line 1401 is the third line below: $statemente = 'SELECT * FROM notes WHERE call = ? ORDER BY id;'; $sth = $dbh-prepare($statemente) or die print Couldn't prepare statement:$DBI::errstr; stopped; $sth-execute($trackedcall) or die print Couldn't execute statement: $DBI::errstr; stopped; WTF? If this sample is indicative of the quality of the rest of PerlDesk, I'd seriously reconsider allowing it anywhere near my servers until a full review of the code was performed. Any help on how to fix this would be appreciated, I'd recommend that you either contact the vendor for support or forgo upgrading mysql. I assume that the software was tested with mysql 4 so you probably should continue to use that until you get a version of the software that supports mysql 5. You might fix this one problem, but who knows what other incompatibilities are lurking in the code (and will bite you at the worst possible time) -r -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql 4 to 5
Michael Dykman wrote: One problem you might be having is the column named 'call'.. It's a keyword, so perhaps you might get away with backticking it?ie. `call` Also, you shouldn't need that final semi-colon inside your statement string.. I have had preparedstatement interfaces give me grief about that. As I recall, DBI doesn't attempt to validate the statement until execute time. DBI does not get involved with the validation of the statement; it leaves that to the database driver (DBD). Not getting any feedback until execute might have been true for earlier version of DBD::mysql because they did not support server-side prepared statements (BTA, neither did mysql itself...). These days you should get feedback when preparing the statement (assuming your server supports it and it is enabled in the driver). -r -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL 4 Grant command
OK, what is wrong with the following statement? MySQL 4.1 doesn't like my syntax ... mysql grant all privileges on *.* to [EMAIL PROTECTED] identified by 'myownpassword';
Re: MySQL 4 Grant command
At 4:12 PM -0800 3/5/08, Garris, Nicole wrote: OK, what is wrong with the following statement? MySQL 4.1 doesn't like my syntax ... mysql grant all privileges on *.* to [EMAIL PROTECTED] identified by 'myownpassword'; % needs quotes around it. I recommend always quoting the username and hostname: 'login3'@'%' -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stable mysql 4.xx
I might have misread the original message, but it sounded to me like the person was looking for earlier software than available via the official download links. I personally don't have any need for old versions, but I use Giuseppe Maxia's mysql_sandbox tool to run many different versions so I can test the tools I write (mysqltoolkit, innotop) against them. Mogens Melander wrote: I'm haing a hard time figuring out why you want an old 4.0 server, when newer better versions are available. MySQL 4.0.27 can be downloaded from Softpedia: http://linux.softpedia.com/progDownload/MySQL-Download-3074.html On Mon, June 4, 2007 00:44, Baron Schwartz wrote: Only back to 4.1.x -- before that, they say it's no longer supported. I couldn't find a way to get it, anyway. Mogens Melander wrote: If i'm not mistaken, the mysql site, also has older versions. On Sun, June 3, 2007 18:31, Baron Schwartz wrote: Hi, Subasta.pl - Darmowe Aukcje Internetowe wrote: Hello, Where I can find the best stable version 4.xx? Some of the mirrors still have older versions, such as http://mysql.mirrors.pair.com/ Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stable mysql 4.xx
Hello, Where I can find the best stable version 4.xx? Greetings, Łukasz Ciszak http://www.subasta.pl
Re: Stable mysql 4.xx
Hi, Subasta.pl - Darmowe Aukcje Internetowe wrote: Hello, Where I can find the best stable version 4.xx? Some of the mirrors still have older versions, such as http://mysql.mirrors.pair.com/ Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stable mysql 4.xx
In the last episode (Jun 03), Subasta.pl - Darmowe Aukcje Internetowe said: Where I can find the best stable version 4.xx? http://dev.mysql.com/downloads/mysql/4.1.html You should really consider using MySQL 5.0, though. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stable mysql 4.xx
I'm haing a hard time figuring out why you want an old 4.0 server, when newer better versions are available. MySQL 4.0.27 can be downloaded from Softpedia: http://linux.softpedia.com/progDownload/MySQL-Download-3074.html On Mon, June 4, 2007 00:44, Baron Schwartz wrote: Only back to 4.1.x -- before that, they say it's no longer supported. I couldn't find a way to get it, anyway. Mogens Melander wrote: If i'm not mistaken, the mysql site, also has older versions. On Sun, June 3, 2007 18:31, Baron Schwartz wrote: Hi, Subasta.pl - Darmowe Aukcje Internetowe wrote: Hello, Where I can find the best stable version 4.xx? Some of the mirrors still have older versions, such as http://mysql.mirrors.pair.com/ Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
On Fri, 2007-04-27 at 09:19 -0500, [EMAIL PROTECTED] wrote: So if one is doing a full mysqldump every night, all bin-logs can be deleted after this? On the slave - Yes. In fact I would highly recommend it before starting the slave processes again. This will reset the bin log's 'position' back to 0, giving you a clear starting point that will correspond to the backup. For the master server (if your backing up from the slave) you'll only want to purge the bin logs back to your slowest slave. If bin-logging is disabled, will master/slave syncing still occur? No, if you disable the bin-logs, replication wont be happening. Regards, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
So if one is doing a full mysqldump every night, all bin-logs can be deleted after this? If bin-logging is disabled, will master/slave syncing still occur? David Issuing a 'reset master' will purge all of the logs as well. I wouldn't just rm them, as they are being tracked in the index file. If you aren't running a slave, then these files are only good for data recovery purposes. Say a DBA goes crazy and deletes all of the databases mid-day (too much stress). You could restore the previous nights backup and run these bin logs up to the point of the delete command - a little bit of editing would be needed to do this, but you get the idea. For this to work smoothly, you need to reset the logs after every backup. If your using mysqldump, just add the --delete-master-logs option. If you want to turn the logs off, remove log-bin and log-bin-index from the conf file. Regards, Scott Tanner On Thu, 2007-04-26 at 09:47 -0700, Scott Haneda wrote: In the short term, see the manual page for PURGE MASTER LOGS. In the long term, write a cron job. innotop (http://sourceforge.net/projects/innotop) also has a new feature, unreleased because I just wrote it a few hours ago, which will help you figure out which binlogs can be purged s keystroke :-)--delete-master-logs I don't quite get this, if SHOW SLAVE STATUS shows empty result set, and I am just running one server, not a master + slave setup at all, its really rather simple. So, how would I ever know what logs I can safely delete or purge? Do I really need to use mysql to purge them or can I just `rm` them? I guess I could push this to cron? PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); My question is, what are these logs really good for, I assume restoration, and from what I read, but how do I know how far back I should keep? thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A.
Bin logs and mysql 4
Running mysql 4, just poked into data and see I have gigs and gigs of hostname-bin.xxx log files. How does one maintain these, can someone point me to relevant data on what to do about drive space being lost to these? thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
Hi, Scott Haneda wrote: Running mysql 4, just poked into data and see I have gigs and gigs of hostname-bin.xxx log files. How does one maintain these, can someone point me to relevant data on what to do about drive space being lost to these? thanks See attached message I just sent to another user a bit ago :-) Baron ---BeginMessage--- Hello, Brown, Charles wrote: Hello All. My bin.log directory is getting full with bin.log files. We are running out of space. What can I do in the short term? Is there a command that I can issue that will get rid of old bin log files not needed? In the short term, see the manual page for PURGE MASTER LOGS. In the long term, write a cron job. innotop (http://sourceforge.net/projects/innotop) also has a new feature, unreleased because I just wrote it a few hours ago, which will help you figure out which binlogs can be purged safely with a single keystroke :-) Baron ---End Message--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
In the short term, see the manual page for PURGE MASTER LOGS. In the long term, write a cron job. innotop (http://sourceforge.net/projects/innotop) also has a new feature, unreleased because I just wrote it a few hours ago, which will help you figure out which binlogs can be purged safely with a single keystroke :-) I don't quite get this, if SHOW SLAVE STATUS shows empty result set, and I am just running one server, not a master + slave setup at all, its really rather simple. So, how would I ever know what logs I can safely delete or purge? Do I really need to use mysql to purge them or can I just `rm` them? I guess I could push this to cron? PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); My question is, what are these logs really good for, I assume restoration, and from what I read, but how do I know how far back I should keep? thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
Hi Scott, Scott Haneda wrote: In the short term, see the manual page for PURGE MASTER LOGS. In the long term, write a cron job. innotop (http://sourceforge.net/projects/innotop) also has a new feature, unreleased because I just wrote it a few hours ago, which will help you figure out which binlogs can be purged safely with a single keystroke :-) I don't quite get this, if SHOW SLAVE STATUS shows empty result set, and I am just running one server, not a master + slave setup at all, its really rather simple. So, how would I ever know what logs I can safely delete or purge? Do I really need to use mysql to purge them or can I just `rm` them? I guess I could push this to cron? PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); My question is, what are these logs really good for, I assume restoration, and from what I read, but how do I know how far back I should keep? thanks Yes -- sorry for being so general. You can use the binlogs for a) replication b) replaying changes since your last backup so you get point-in-time recovery. If you have no replication slaves, just delete everything older than your latest backup. You can just use 'rm'. If you use PURGE MASTER LOGS BEFORE, it's a bit easier than cron because you can do it across all platforms easily. On UNIX of course, you'd use something like find /var/lib/mysql/data -name *.bin -mtime +7 -exec rm {} \ (My find syntax is guaranteed to be wrong there... don't run that as I typed it). But if you do it via SQL, you don't have to mess with this. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
Yes -- sorry for being so general. You can use the binlogs for a) replication b) replaying changes since your last backup so you get point-in-time recovery. If you have no replication slaves, just delete everything older than your latest backup. You can just use 'rm'. If you use PURGE MASTER LOGS BEFORE, it's a bit easier than cron because you can do it across all platforms easily. On UNIX of course, you'd use something like find /var/lib/mysql/data -name *.bin -mtime +7 -exec rm {} \ (My find syntax is guaranteed to be wrong there... don't run that as I typed it). But if you do it via SQL, you don't have to mess with this. Thanks! So, I take it since I do not have a slave at all, I could safely just disable this feature altogether? If I do not need point in time recovery, and the once every 12 hour dump I do across all databases is ok with me, I suppose I can just disable said feature? Heck, some of these boogers are a GB each :-) -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
Issuing a 'reset master' will purge all of the logs as well. I wouldn't just rm them, as they are being tracked in the index file. If you aren't running a slave, then these files are only good for data recovery purposes. Say a DBA goes crazy and deletes all of the databases mid-day (too much stress). You could restore the previous nights backup and run these bin logs up to the point of the delete command - a little bit of editing would be needed to do this, but you get the idea. For this to work smoothly, you need to reset the logs after every backup. If your using mysqldump, just add the --delete-master-logs option. If you want to turn the logs off, remove log-bin and log-bin-index from the conf file. Regards, Scott Tanner On Thu, 2007-04-26 at 09:47 -0700, Scott Haneda wrote: In the short term, see the manual page for PURGE MASTER LOGS. In the long term, write a cron job. innotop (http://sourceforge.net/projects/innotop) also has a new feature, unreleased because I just wrote it a few hours ago, which will help you figure out which binlogs can be purged s keystroke :-)--delete-master-logs I don't quite get this, if SHOW SLAVE STATUS shows empty result set, and I am just running one server, not a master + slave setup at all, its really rather simple. So, how would I ever know what logs I can safely delete or purge? Do I really need to use mysql to purge them or can I just `rm` them? I guess I could push this to cron? PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); My question is, what are these logs really good for, I assume restoration, and from what I read, but how do I know how far back I should keep? thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
So, I take it since I do not have a slave at all, I could safely just disable this feature altogether? If I do not need point in time recovery, and the once every 12 hour dump I do across all databases is ok with me, I suppose I can just disable said feature? Heck, some of these boogers are a GB each :-) Sounds reasonable to me! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replacing mysql 4 with versiono 5
hi, we are in the process of moving from our old hosting company to new one. and I was thinking that it's the best time to move from mysql 4 to version 5. currently, we use php 4.3.4 / mysql 4.0.18 / apache 2.0 where I can expect problems because of difference in versions? all queries I use are really simple, standard if I can say so. Thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replacing mysql 4 with versiono 5
Afan, the password hashing is different, starting with 4.1. Also the JOINs were changed / made to comply with standard in 5.0. http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html and http://dev.mysql.com/doc/refman/5.0/en/join.html Dan On 11/10/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: hi, we are in the process of moving from our old hosting company to new one. and I was thinking that it's the best time to move from mysql 4 to version 5. currently, we use php 4.3.4 / mysql 4.0.18 / apache 2.0 where I can expect problems because of difference in versions? all queries I use are really simple, standard if I can say so. Thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4 vs 5 - threading model change?? Config Problem?
When I ran version 4, mysql would start as many mysqld process as it needed. This would vary depending upon how busy it was. As a lay perosn I will refer to this as the threading model. I have upgraded to version 5 and notice that no matter what it only runs 1 mysqld. Everything seems to work fine, but I want to make sure that mysqld is taking advantage of my multiple processors. Has the htreading model changed and now MYSQL will only run one mysqld OR have I missed something and need to enable it? Am I getting the best bang for my buck by haveing just one mysqld running? Travis
Re: datetime issue on MySQL 4.x
Willy wrote: Hello, I have a MyISAM table: CREATE TABLE `dlr` ( `msisdn` varchar(20) NOT NULL default '', `source` varchar(20) NOT NULL default '', `operator_id` varchar(20) NOT NULL default '', `sms_message` longtext NOT NULL, `smsc_id` varchar(20) NOT NULL default '', `sms_id` varchar(250) NOT NULL default '', `dlr_smsc` text NOT NULL, `dlr` tinyint(4) NOT NULL default '0', `date_time` datetime NOT NULL default '-00-00 00:00:00' ) TYPE=MyISAM I have a case here, on July 7 I imported some data into the table with this query: INSERT INTO dlr (source, msisdn, operator_id, sms_message, smsc_id, sms_id, dlr_smsc, dlr, date_time) VALUES ('366508', '1234', 4, '', 'IM33665', MD5(RAND()), 'RE:', '2', NOW()), ('366508', '1234', 4, 'id:0943381383 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:, 'IM33665', MD5(RAND()), 'RE:id:0943381383 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:', '2', '2006-06-28 03:17:00'), ('366508', '1234', 4, 'id:1226442088 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:', 'IM33665', MD5(RAND()), 'RE:id:1226442088 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:Silahkan tawar: XDA ', '2', '2006-06-28 03:17:00'), ('366508', '1234', 4, 'id:1861980498 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:', 'IM33665', MD5(RAND()), 'RE:id:1861980498 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:', '2', '2006-06-28 03:17:00'), ('366508', '1234', 4, '', 'IM33665', MD5(RAND()), 'RE:', '2', NOW())... And when I try to fetch the data using PHP with this query: SELECT operator_id, DATE_FORMAT(date_time,'%d-%m-%Y') AS tanggal, COUNT(*) AS TOTAL FROM dlr WHERE 1=1 AND dlr = 8 AND MONTH(date_time) = MONTH(NOW()) GROUP BY tanggal, operator_id ORDER BY date_time The weird thing happened. Why does all the rows are shown or treated as July 7th data? If we see the imported data, there should be June 28, June 29.until July 7. Please help and many thanks for any reply. Regards Willy Because you specified only for the month of July. AND MONTH(date_time) = MONTH(NOW()) -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
datetime issue on MySQL 4.x
Hello, I have a MyISAM table: CREATE TABLE `dlr` ( `msisdn` varchar(20) NOT NULL default '', `source` varchar(20) NOT NULL default '', `operator_id` varchar(20) NOT NULL default '', `sms_message` longtext NOT NULL, `smsc_id` varchar(20) NOT NULL default '', `sms_id` varchar(250) NOT NULL default '', `dlr_smsc` text NOT NULL, `dlr` tinyint(4) NOT NULL default '0', `date_time` datetime NOT NULL default '-00-00 00:00:00' ) TYPE=MyISAM I have a case here, on July 7 I imported some data into the table with this query: INSERT INTO dlr (source, msisdn, operator_id, sms_message, smsc_id, sms_id, dlr_smsc, dlr, date_time) VALUES ('366508', '1234', 4, '', 'IM33665', MD5(RAND()), 'RE:', '2', NOW()), ('366508', '1234', 4, 'id:0943381383 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:, 'IM33665', MD5(RAND()), 'RE:id:0943381383 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:', '2', '2006-06-28 03:17:00'), ('366508', '1234', 4, 'id:1226442088 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:', 'IM33665', MD5(RAND()), 'RE:id:1226442088 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:Silahkan tawar: XDA ', '2', '2006-06-28 03:17:00'), ('366508', '1234', 4, 'id:1861980498 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:', 'IM33665', MD5(RAND()), 'RE:id:1861980498 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:', '2', '2006-06-28 03:17:00'), ('366508', '1234', 4, '', 'IM33665', MD5(RAND()), 'RE:', '2', NOW())... And when I try to fetch the data using PHP with this query: SELECT operator_id, DATE_FORMAT(date_time,'%d-%m-%Y') AS tanggal, COUNT(*) AS TOTAL FROM dlr WHERE 1=1 AND dlr = 8 AND MONTH(date_time) = MONTH(NOW()) GROUP BY tanggal, operator_id ORDER BY date_time The weird thing happened. Why does all the rows are shown or treated as July 7th data? If we see the imported data, there should be June 28, June 29.until July 7. Please help and many thanks for any reply. Regards Willy
Re: datetime issue on MySQL 4.x (SOLVED)
Hello, I have solved this problem, thanks. Regards Willy - Original Message - From: Willy To: mysql@lists.mysql.com Sent: Wednesday, July 12, 2006 8:55 AM Subject: datetime issue on MySQL 4.x Hello, I have a MyISAM table: CREATE TABLE `dlr` ( `msisdn` varchar(20) NOT NULL default '', `source` varchar(20) NOT NULL default '', `operator_id` varchar(20) NOT NULL default '', `sms_message` longtext NOT NULL, `smsc_id` varchar(20) NOT NULL default '', `sms_id` varchar(250) NOT NULL default '', `dlr_smsc` text NOT NULL, `dlr` tinyint(4) NOT NULL default '0', `date_time` datetime NOT NULL default '-00-00 00:00:00' ) TYPE=MyISAM I have a case here, on July 7 I imported some data into the table with this query: INSERT INTO dlr (source, msisdn, operator_id, sms_message, smsc_id, sms_id, dlr_smsc, dlr, date_time) VALUES ('366508', '1234', 4, '', 'IM33665', MD5(RAND()), 'RE:', '2', NOW()), ('366508', '1234', 4, 'id:0943381383 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:, 'IM33665', MD5(RAND()), 'RE:id:0943381383 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:', '2', '2006-06-28 03:17:00'), ('366508', '1234', 4, 'id:1226442088 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:', 'IM33665', MD5(RAND()), 'RE:id:1226442088 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:Silahkan tawar: XDA ', '2', '2006-06-28 03:17:00'), ('366508', '1234', 4, 'id:1861980498 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:', 'IM33665', MD5(RAND()), 'RE:id:1861980498 sub:001 dlvrd:000 submit date:0606280317 done date:0606290317 stat:EXPIRED err:999 text:', '2', '2006-06-28 03:17:00'), ('366508', '1234', 4, '', 'IM33665', MD5(RAND()), 'RE:', '2', NOW())... And when I try to fetch the data using PHP with this query: SELECT operator_id, DATE_FORMAT(date_time,'%d-%m-%Y') AS tanggal, COUNT(*) AS TOTAL FROM dlr WHERE 1=1 AND dlr = 8 AND MONTH(date_time) = MONTH(NOW()) GROUP BY tanggal, operator_id ORDER BY date_time The weird thing happened. Why does all the rows are shown or treated as July 7th data? If we see the imported data, there should be June 28, June 29.until July 7. Please help and many thanks for any reply. Regards Willy
BBEdit tip (was: Differences between MySQL 4 and 5 for scripts)
At 23:00 +0100 14/6/06, Graham Reeds wrote: 1) You may have a bogus hidden character in your SQL file. If you look at it with a text editor (BBEdit, TextWrangler, etc), with the show invivisbles feature on, do you see unusual stuff? Sounds strange but I've seen stranger. Took a brief look over it but didn't see anything that I thought looked untoward. Nor did side by side comparison show up anything. I know your problem is now solved, but a quick tip for users of BBEdit when searching for 'rogue' characters: Sometimes you can't see them at all in the normal display, but if you go to the page preferences menu - the third little square from the left inside the message window - and select Show Invisibles, this can reveal various oddities. You may find, eg, control characters appearing as inverted red ?s, or non-breaking spaces (I /think/ that's what they are) appearing as grey bullets where you expect normal spaces. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Empty warhead found in White House -- Sign carried on New York peace rally, April 2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Differences between MySQL 4 and 5 for scripts
I have posted a similar question on the blojsom group but I feel I will have better chance of an answer here. Blojsom 3 was developed using MySQL5 for it's back end. However the host I am with uses 4.0.25 and are unwilling to upgrade - which is fair enough. So I decided to see what I can do to make the database creation scripts MySQL4 compliant. The CHARSET was the easy one - just change it to CHARACTER SET but the next one was more tricky: The problem I am having is that version 4 fails giving a syntax error for the single quotation marks. It appears the script (which was written on a Mac using MySQL dump 10.9 if that is any help) uses ` and not ', but doing a general search and replace doesn't fix it. I've searched the online docs regarding quotes but came up short (too many hits with too many unrelated items) so if anyone knows the exact url that would be helpful too. Here's a snippet of the code: DROP TABLE IF EXISTS `Blog`; CREATE TABLE `Blog` ( `blog_id` varchar(50) NOT NULL, PRIMARY KEY (`blog_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Thanks, G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Differences between MySQL 4 and 5 for scripts
Graham, I seem to recall those single quote marks working without a problem on various platforms and versions of MySQL. Of course they are generally just a nicety and only required if you are using reserved words as table/column/key names, so you could just remove them entirely. Could you post the exact error message you're getting please? Two thoughts - 1) You may have a bogus hidden character in your SQL file. If you look at it with a text editor (BBEdit, TextWrangler, etc), with the show invivisbles feature on, do you see unusual stuff? Sounds strange but I've seen stranger. 2) You could work around this problem a different way ... - create a 5.0 database yourself using the standard supplied scripts, perhaps on your own workstation - dump your new database structure using mysqldump with the flag --compatible=mysql40 which should create a file suitable for piping into a MySQL 4.0 server Dan Graham Reeds wrote: I have posted a similar question on the blojsom group but I feel I will have better chance of an answer here. Blojsom 3 was developed using MySQL5 for it's back end. However the host I am with uses 4.0.25 and are unwilling to upgrade - which is fair enough. So I decided to see what I can do to make the database creation scripts MySQL4 compliant. The CHARSET was the easy one - just change it to CHARACTER SET but the next one was more tricky: The problem I am having is that version 4 fails giving a syntax error for the single quotation marks. It appears the script (which was written on a Mac using MySQL dump 10.9 if that is any help) uses ` and not ', but doing a general search and replace doesn't fix it. I've searched the online docs regarding quotes but came up short (too many hits with too many unrelated items) so if anyone knows the exact url that would be helpful too. Here's a snippet of the code: DROP TABLE IF EXISTS `Blog`; CREATE TABLE `Blog` ( `blog_id` varchar(50) NOT NULL, PRIMARY KEY (`blog_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Thanks, G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Differences between MySQL 4 and 5 for scripts
Graham Reeds wrote: I have posted a similar question on the blojsom group but I feel I will have better chance of an answer here. Blojsom 3 was developed using MySQL5 for it's back end. However the host I am with uses 4.0.25 and are unwilling to upgrade - which is fair enough. So I decided to see what I can do to make the database creation scripts MySQL4 compliant. The CHARSET was the easy one - just change it to CHARACTER SET but the next one was more tricky: The problem I am having is that version 4 fails giving a syntax error for the single quotation marks. It appears the script (which was written on a Mac using MySQL dump 10.9 if that is any help) uses ` and not ', but doing a general search and replace doesn't fix it. I've searched the online docs regarding quotes but came up short (too many hits with too many unrelated items) so if anyone knows the exact url that would be helpful too. Here's a snippet of the code: DROP TABLE IF EXISTS `Blog`; CREATE TABLE `Blog` ( `blog_id` varchar(50) NOT NULL, PRIMARY KEY (`blog_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Thanks, G. Single quotes (') are for strings. Backticks (`) are for identifiers. To the best of my knowledge, this hasn't changed from 4.0 to 5. Hence, using your table definition, INSERT INTO `Blog` (`blog_id`) VALUES ('This is a blog id'); would be correctly quoted. Strictly speaking, you only need to quote identifiers which wouldn't otherwise be allowed (reserved words), so none of the backticks are needed in your example (or in mine). See the manual for details: http://dev.mysql.com/doc/refman/4.1/en/legal-names.html http://dev.mysql.com/doc/refman/4.1/en/reserved-words.html What leads you to believe your error is related to quoting? I think it is more likely to be something else. If you post the query that gives the error and include the exact error message, I'm sure someone will be able to identify the problem. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Differences between MySQL 4 and 5 for scripts
Dan Buettner wrote: Graham, I seem to recall those single quote marks working without a problem on various platforms and versions of MySQL. Of course they are generally just a nicety and only required if you are using reserved words as table/column/key names, so you could just remove them entirely. Could you post the exact error message you're getting please? You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''Blog'' at line 1 when line one is DROP TABLE IF EXISTS 'Blog'; Two thoughts - 1) You may have a bogus hidden character in your SQL file. If you look at it with a text editor (BBEdit, TextWrangler, etc), with the show invivisbles feature on, do you see unusual stuff? Sounds strange but I've seen stranger. Took a brief look over it but didn't see anything that I thought looked untoward. Nor did side by side comparison show up anything. 2) You could work around this problem a different way ... - create a 5.0 database yourself using the standard supplied scripts, perhaps on your own workstation - dump your new database structure using mysqldump with the flag --compatible=mysql40 which should create a file suitable for piping into a MySQL 4.0 server That worked. The gotchas: * CHARSET wasn't exported as CHARACTER SET - it wasn't exported at all. * TYPE is now ENGINE (ie TYPE=InnoDB is now ENGINE=InnoDB) * The tables have gone from having an initial capital letter to all lower caps (I thought v5 was all lower caps anyway?) * Also an error occurred regarding varchar size (it appears v5 can hold 4096 while v4 max is 255). Can you load a v5 server with a v4 script? Thanks for your help! Graham. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Differences between MySQL 4 and 5 for scripts
On Wed, Jun 14, 2006 at 11:00:36PM +0100, Graham Reeds wrote: Dan Buettner wrote: Graham, I seem to recall those single quote marks working without a problem on various platforms and versions of MySQL. Of course they are generally just a nicety and only required if you are using reserved words as table/column/key names, so you could just remove them entirely. Could you post the exact error message you're getting please? You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''Blog'' at line 1 when line one is DROP TABLE IF EXISTS 'Blog'; You're not quoting the table name correctly. Identifiers must be quoted using the backtick (`), if they are quoted at all. You should be fine with: DROP TABLE IF EXISTS Blog; or DROP TABLE IF EXISTS `Blog`; 2) You could work around this problem a different way ... - create a 5.0 database yourself using the standard supplied scripts, perhaps on your own workstation - dump your new database structure using mysqldump with the flag --compatible=mysql40 which should create a file suitable for piping into a MySQL 4.0 server That worked. The gotchas: * CHARSET wasn't exported as CHARACTER SET - it wasn't exported at all. Because MySQL 4.0 does not understand character sets at the table level. (It was a new feature in MySQL 4.1.) * TYPE is now ENGINE (ie TYPE=InnoDB is now ENGINE=InnoDB) ENGINE is supported as of MySQL 4.0.18. * The tables have gone from having an initial capital letter to all lower caps (I thought v5 was all lower caps anyway?) On systems with case-insensitive file systems, MySQL will lowercase table and database identifiers. * Also an error occurred regarding varchar size (it appears v5 can hold 4096 while v4 max is 255). The maximum size of a VARCHAR is 255 bytes in MySQL 4.0, and about 64K characters in MySQL 4.1 and later. Can you load a v5 server with a v4 script? Yes, you can generally load scripts written for MySQL 4.x into MySQL 5.x. You may also want to use the special syntax for specifying version-specific parts of your query: http://dev.mysql.com/doc/refman/5.0/en/comments.html Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4 Kernel Panic-ing
I've got an xserve running 10.3.9 and MySQL 4.0.27-max that is restarting itself every 2-3 days. It appears that it is due to a kernel panic, though I don't have direct access to the machine (colocated) to verify when it occurs. There are no crash.logs from MySQL, and watchdog and system.logs show nothing. I have culled the following panic.log - can anyone tell if MySQL is the cause of such? Has anyone seen MySQL 4 crash on an Xserve (G4 specifically)? I have tried two totally different servers, and the problems continue. - John * Mon Jun 12 06:53:02 2006 Unresolved kernel trap(cpu 0): 0x300 - Data access DAR=0xFF864A5C PC=0x00056218 Latest crash info for cpu 0: Exception state (sv=0x3E6ABC80) PC=0x00056218; MSR=0x9030; DAR=0xFF864A5C; DSISR=0x4000; LR=0x00053AB4; R1=0x1C17BC70; XCP=0x000C (0x300 - Data access) Backtrace: 0x 0x00055DC8 0x0002F750 0x00033710 0x00032028 0x00031FE4 Proceeding back via exception chain: Exception state (sv=0x3E6ABC80) previously dumped as Latest state. skipping... Exception state (sv=0x0091F280) PC=0x; MSR=0xD030; DAR=0x; DSISR=0x; LR=0x; R1=0x; XCP=0x (Unknown) Kernel version: Darwin Kernel Version 7.9.0: Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC panic(cpu 0): copyin/out has no recovery point Latest stack backtrace for cpu 0: Backtrace: 0x00083498 0x0008397C 0x0001EDA4 0x00090C38 0x0009402C Proceeding back via exception chain: Exception state (sv=0x3E6ABC80) PC=0x00056218; MSR=0x9030; DAR=0xFF864A5C; DSISR=0x4000; LR=0x00053AB4; R1=0x1C17BC70; XCP=0x000C (0x300 - Data access) Backtrace: 0x 0x00055DC8 0x0002F750 0x00033710 0x00032028 0x00031FE4 Exception state (sv=0x0091F280) PC=0x; MSR=0xD030; DAR=0x; DSISR=0x; LR=0x; R1=0x; XCP=0x (Unknown) Kernel version: Darwin Kernel Version 7.9.0: Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] Professional Lasso / PHP / MySQL / FileMaker Pro Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4 Kernel Panic-ing
John May wrote: I've got an xserve running 10.3.9 and MySQL 4.0.27-max that is restarting itself every 2-3 days. It appears that it is due to a kernel panic, though I don't have direct access to the machine (colocated) to verify when it occurs. There are no crash.logs from MySQL, and watchdog and system.logs show nothing. I have culled the following panic.log - can anyone tell if MySQL is the cause of such? Has anyone seen MySQL 4 crash on an Xserve (G4 specifically)? I have tried two totally different servers, and the problems continue. - John * Mon Jun 12 06:53:02 2006 Unresolved kernel trap(cpu 0): 0x300 - Data access DAR=0xFF864A5C PC=0x00056218 Latest crash info for cpu 0: Exception state (sv=0x3E6ABC80) PC=0x00056218; MSR=0x9030; DAR=0xFF864A5C; DSISR=0x4000; LR=0x00053AB4; R1=0x1C17BC70; XCP=0x000C (0x300 - Data access) Backtrace: 0x 0x00055DC8 0x0002F750 0x00033710 0x00032028 0x00031FE4 Proceeding back via exception chain: Exception state (sv=0x3E6ABC80) previously dumped as Latest state. skipping... Exception state (sv=0x0091F280) PC=0x; MSR=0xD030; DAR=0x; DSISR=0x; LR=0x; R1=0x; XCP=0x (Unknown) Kernel version: Darwin Kernel Version 7.9.0: Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC panic(cpu 0): copyin/out has no recovery point Latest stack backtrace for cpu 0: Backtrace: 0x00083498 0x0008397C 0x0001EDA4 0x00090C38 0x0009402C Proceeding back via exception chain: Exception state (sv=0x3E6ABC80) PC=0x00056218; MSR=0x9030; DAR=0xFF864A5C; DSISR=0x4000; LR=0x00053AB4; R1=0x1C17BC70; XCP=0x000C (0x300 - Data access) Backtrace: 0x 0x00055DC8 0x0002F750 0x00033710 0x00032028 0x00031FE4 Exception state (sv=0x0091F280) PC=0x; MSR=0xD030; DAR=0x; DSISR=0x; LR=0x; R1=0x; XCP=0x (Unknown) Kernel version: Darwin Kernel Version 7.9.0: Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC How could a user space program like mysqld possibly cause a kernel panic? Sounds like bad kernel or bad hardware. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4 Kernel Panic-ing
Hi John - Yes, at my former workplace, we had a couple of different instances where we experienced severe crashing problems on G4-based machines. Prior to using a G4 Xserve, we were using a dual-G4 tower as our MySQL and Intranet server, and as we ramped up MySQL usage, the hard crashing got to where it was nearly every morning. Finally traced it to our backup process, Retrospect, accessing files while MySQL was trying to access them. Specifically excluding MySQL's data directory from your backup process is a must. Excluding temp and log dirs is also probably a good idea. We were already dumping out data out to another directory daily, but didn't realize that the simultaneous access would be such a problem. This was with MySQL 3.23 and later 4.0, and OS 10.1 through 10.2 I think. When we moved to a G4 Xserve (serving MySQL and our Intranet), we again had horrible crashing problems. Weird, like none I'd ever seen, where the OS basically just entered a slow spiral of death that led to it either rebooting on its own or us noticing the problem first and hard-resetting it. Apps would stop opening, cli processes would no longer run, running apps would slowly stop responding. Traced it to a pair of Kingston RAM modules. Once those were out, the machine righted itself and I believe continues to serve data (though no longer MySQL) to this day. Weird I know but I swear that was it. This was with MySQL 4.0 and (I think) OS 10.2 through 10.3. HTH, Dan John May wrote: I've got an xserve running 10.3.9 and MySQL 4.0.27-max that is restarting itself every 2-3 days. It appears that it is due to a kernel panic, though I don't have direct access to the machine (colocated) to verify when it occurs. There are no crash.logs from MySQL, and watchdog and system.logs show nothing. I have culled the following panic.log - can anyone tell if MySQL is the cause of such? Has anyone seen MySQL 4 crash on an Xserve (G4 specifically)? I have tried two totally different servers, and the problems continue. - John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4 Kernel Panic-ing
Nope, no backups happening at the time. Anyone other ideas? - John At 11:42 AM -0500 6/12/06, Dan Buettner wrote: Hi John - Yes, at my former workplace, we had a couple of different instances where we experienced severe crashing problems on G4-based machines. Prior to using a G4 Xserve, we were using a dual-G4 tower as our MySQL and Intranet server, and as we ramped up MySQL usage, the hard crashing got to where it was nearly every morning. Finally traced it to our backup process, Retrospect, accessing files while MySQL was trying to access them. Specifically excluding MySQL's data directory from your backup process is a must. Excluding temp and log dirs is also probably a good idea. We were already dumping out data out to another directory daily, but didn't realize that the simultaneous access would be such a problem. This was with MySQL 3.23 and later 4.0, and OS 10.1 through 10.2 I think. When we moved to a G4 Xserve (serving MySQL and our Intranet), we again had horrible crashing problems. Weird, like none I'd ever seen, where the OS basically just entered a slow spiral of death that led to it either rebooting on its own or us noticing the problem first and hard-resetting it. Apps would stop opening, cli processes would no longer run, running apps would slowly stop responding. Traced it to a pair of Kingston RAM modules. Once those were out, the machine righted itself and I believe continues to serve data (though no longer MySQL) to this day. Weird I know but I swear that was it. This was with MySQL 4.0 and (I think) OS 10.2 through 10.3. HTH, Dan John May wrote: I've got an xserve running 10.3.9 and MySQL 4.0.27-max that is restarting itself every 2-3 days. It appears that it is due to a kernel panic, though I don't have direct access to the machine (colocated) to verify when it occurs. There are no crash.logs from MySQL, and watchdog and system.logs show nothing. I have culled the following panic.log - can anyone tell if MySQL is the cause of such? Has anyone seen MySQL 4 crash on an Xserve (G4 specifically)? I have tried two totally different servers, and the problems continue. - John -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] Professional Lasso / PHP / MySQL / FileMaker Pro Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 5 vs. mysql 4: str_to_date
The following query runs fine on my test server at home, running MySQL 5.0.15, but on my hosting provider's server, running MySQL 4.0.24, it gets a syntax error. Can anyone help me figure out why? ... insert into product (name, url, cost, purchase_dt) values('Whatchamacallit', 'http://www.whatchamacallit.com', 5, str_to_date('2006-04-20', '%Y-%m-%d')) Here's the error message : #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '('2006-04-20', '%Y-%m-%d'))' at line 2 Thank you, Vince Aggrippino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 5 vs. mysql 4: str_to_date
Vincente Aggrippino wrote: The following query runs fine on my test server at home, running MySQL 5.0.15, but on my hosting provider's server, running MySQL 4.0.24, it gets a syntax error. Can anyone help me figure out why? ... insert into product (name, url, cost, purchase_dt) values('Whatchamacallit', 'http://www.whatchamacallit.com', 5, str_to_date('2006-04-20', '%Y-%m-%d')) Here's the error message : #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '('2006-04-20', '%Y-%m-%d'))' at line 2 Thank you, Vince Aggrippino He doesn't know str_to_date. That's it. -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 5 vs. mysql 4: str_to_date
In the last episode (Apr 21), Vincente Aggrippino said: The following query runs fine on my test server at home, running MySQL 5.0.15, but on my hosting provider's server, running MySQL 4.0.24, it gets a syntax error. Can anyone help me figure out why? ... insert into product (name, url, cost, purchase_dt) values('Whatchamacallit', 'http://www.whatchamacallit.com', 5, str_to_date('2006-04-20', '%Y-%m-%d')) Here's the error message : #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '('2006-04-20', '%Y-%m-%d'))' at line 2 From the manual: * STR_TO_DATE(str,format) STR_TO_DATE() is available as of MySQL 4.1.1. http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
symbolic-links mysql 4 error on ubuntu 5.10
hi all, I read http://dev.mysql.com/doc/refman/5.0/en/symbolic-links-to-databases.html but on MySQL 4.0.24_Debian-10ubuntu2 server , in my my.conf file: datadir = /var/lib/mysql ls show as: lrwxrwxrwx 1 mysql mysql 67 2006-03-12 22:42 mnogosearch3235 - /home/username/mnt/cobd/temp_mysql_datadir/datadir/mnogosearch3235/ after reboot mysql server,i cannot find the DB:mnogosearch3235. and if i change mysql my.conf: datadir = /home/username/mnt/cobd/temp_mysql_datadir/datadir/ then it runs work! but if chang as this : -rw-r- 1 mysql mysql 8998 2006-03-21 17:00 url.frm lrwxrwxrwx 1 mysql mysql 76 2006-03-21 16:41 url.MYD - /home/username/mnt/cobd/temp_mysql_tabledir/tabledir/mnogosearch3235/url.MYD -rw-r- 1 mysql mysql 47276032 2006-03-21 17:08 url.MYI show me following error: Error: 'DB err: MySQL driver: #1105: File '/home/username/mnt/cobd/temp_mysql_tabledir/tabledir/mnogosearch3235/url.MYD' not found (Errcode: 13) [EMAIL PROTECTED]:~/var/temp_mysql_datadir$ sudo ls -l /home/username/mnt/cobd/temp_mysql_tabledir/tabledir/mnogosearch3235/url.MYD -rw-rw 1 mysql mysql 47498160 2006-03-21 15:18 /home/username/mnt/cobd/temp_mysql_tabledir/tabledir/mnogosearch3235/url.MYD what`s wrong?anybody can help me? -- View this message in context: http://www.nabble.com/symbolic-links-mysql-4-error-on-ubuntu-5.10-t1322625.html#a3528642 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Totally different join-behaviour in mysql 4 and 5?
As I already wrote I try do get a replication running from a mysql-4.1.13 (32bit) master to a 5.0.18 (64bit) slave. It only runs for a few minutes and then a query hangs. I think I now found out why: I modified a multi-table-update that hung to a select. The same query on the absolutely identical tables gives totally different explains on both systems: While my query has a cardinality of 23,124*1=23,124 on mysql4, it has 6,412*34,341=220,194,492 on mysql5 - and takes forever and makes me think everything hangs?! I verified this with a dump of to tables that I imported on various few different systems. I created a new test-database, piped the dump into that and ran the following queries. It looks like the join-behaviour of mysql has totally changed in mysql5! This seems to affect quite a lot of queries here. Am I doing really stupid mistakes or did I miss a major chapter in the upgrading to mysql5-documentation that I read over and over during the last days? Jan I put the mysql versions in front of the mysql prompts: 4.1.13-standard mysql show table status; +--++-++++-+-+--+---++-+-+-+---+--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--++-++++-+-+--+---++-+-+-+---+--++-+ | dlstm_data_d | MyISAM | 9 | Dynamic| 48621 | 49 | 2428108 | 4294967295 | 1921024 | 0 | NULL | 2006-02-01 11:54:57 | 2006-02-01 11:55:08 | 2006-02-01 11:55:09 | latin1_swedish_ci | NULL || | | stm_data_d | MyISAM | 9 | Dynamic| 480772 |105 | 50816164 | 4294967295 | 57697280 | 0 | NULL | 2006-02-01 11:55:09 | 2006-02-01 11:57:00 | 2006-02-01 11:58:58 | latin1_swedish_ci | NULL || | +--++-++++-+-+--+---++-+-+-+---+--++-+ 2 rows in set (0.00 sec) 5.0.18-max-log mysql show table status; +--++-++++-+-+--+---++-+-+-+---+--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--++-++++-+-+--+---++-+-+-+---+--++-+ | dlstm_data_d | MyISAM | 10 | Dynamic| 48621 | 49 | 2384860 | 281474976710655 | 1836032 | 0 | NULL | 2006-02-01 11:54:05 | 2006-02-01 11:54:07 | 2006-02-01 11:54:07 | latin1_swedish_ci | NULL || | | stm_data_d | MyISAM | 10 | Dynamic| 480772 |104 | 50192768 | 281474976710655 | 52738048 | 0 | NULL | 2006-02-01 11:54:07 | 2006-02-01 11:55:12 | 2006-02-01 11:55:40 | latin1_swedish_ci | NULL || | +--++-++++-+-+--+---++-+-+-+---+--++-+ 2 rows in set (0.00 sec) 4.1.13-standard mysql explain select *from dlstm_data_d s, stm_data_d t where s.sym_cd_ is null and s.sys_cdd=t.sys_cdd and s.local=t.local and s.local is not null and s.local!= and s.sys_cdd is not null and s.sys_cdd!= and t.sym_cd is not null; ++-+---+--+---+-+-+---+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
Re: Totally different join-behaviour in mysql 4 and 5?
Jan Kirchhoff [EMAIL PROTECTED] wrote on 02/01/2006 06:31:20 AM: As I already wrote I try do get a replication running from a mysql-4.1.13 (32bit) master to a 5.0.18 (64bit) slave. It only runs for a few minutes and then a query hangs. I think I now found out why: I modified a multi-table-update that hung to a select. The same query on the absolutely identical tables gives totally different explains on both systems: While my query has a cardinality of 23,124*1=23,124 on mysql4, it has 6,412*34,341=220,194,492 on mysql5 - and takes forever and makes me think everything hangs?! I verified this with a dump of to tables that I imported on various few different systems. I created a new test-database, piped the dump into that and ran the following queries. It looks like the join-behaviour of mysql has totally changed in mysql5! This seems to affect quite a lot of queries here. Am I doing really stupid mistakes or did I miss a major chapter in the upgrading to mysql5-documentation that I read over and over during the last days? Jan I put the mysql versions in front of the mysql prompts: 4.1.13-standard mysql show table status; +--++-++ ++-+-+-- +---++- +-+-+--- +--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--++-++ ++-+-+-- +---++- +-+-+--- +--++-+ | dlstm_data_d | MyISAM | 9 | Dynamic| 48621 | 49 | 2428108 | 4294967295 | 1921024 | 0 | NULL | 2006-02-01 11:54:57 | 2006-02-01 11:55:08 | 2006-02-01 11:55: 09 | latin1_swedish_ci | NULL || | | stm_data_d | MyISAM | 9 | Dynamic| 480772 | 105 |50816164 | 4294967295 | 57697280 | 0 | NULL | 2006-02-01 11:55:09 | 2006-02-01 11:57:00 | 2006-02-01 11:58: 58 | latin1_swedish_ci | NULL || | +--++-++ ++-+-+-- +---++- +-+-+--- +--++-+ 2 rows in set (0.00 sec) 5.0.18-max-log mysql show table status; +--++-++ ++-+-+-- +---++- +-+-+--- +--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--++-++ ++-+-+-- +---++- +-+-+--- +--++-+ | dlstm_data_d | MyISAM | 10 | Dynamic| 48621 | 49 | 2384860 | 281474976710655 | 1836032 | 0 | NULL | 2006-02-01 11:54:05 | 2006-02-01 11:54:07 | 2006-02-01 11:54: 07 | latin1_swedish_ci | NULL || | | stm_data_d | MyISAM | 10 | Dynamic| 480772 | 104 |50192768 | 281474976710655 | 52738048 | 0 | NULL | 2006-02-01 11:54:07 | 2006-02-01 11:55:12 | 2006-02-01 11:55: 40 | latin1_swedish_ci | NULL || | +--++-++ ++-+-+-- +---++- +-+-+--- +--++-+ 2 rows in set (0.00 sec) 4.1.13-standard mysql explain select *from dlstm_data_d s, stm_data_d t where s.sym_cd_ is null and s.sys_cdd=t.sys_cdd and s. local=t.local and s.local is not null and s.local!= and s.sys_cdd is not null and s.sys_cdd!= and t.sym_cd is not null; ++-+---+--+--- +-+-+---+---+-+ | id | select_type | table |
Re: Totally different join-behaviour in mysql 4 and 5?
Jan, While my query has a cardinality of 23,124*1=23,124 on mysql4, it has 6,412*34,341=220,194,492 on mysql5 - and takes forever and makes me think everything hangs?! Yep, MySQL improved the SQL compatibility of its join syntax beginning with 5.0.12, see http://dev.mysql.com/doc/refman/5.0/en/join.html and, esp. comments there on the change in precedence of the comma operator. PB - Jan Kirchhoff wrote: As I already wrote I try do get a replication running from a mysql-4.1.13 (32bit) master to a 5.0.18 (64bit) slave. It only runs for a few minutes and then a query hangs. I think I now found out why: I modified a multi-table-update that hung to a select. The same query on the absolutely identical tables gives totally different "explains" on both systems: While my query has a cardinality of 23,124*1=23,124 on mysql4, it has 6,412*34,341=220,194,492 on mysql5 - and takes forever and makes me think everything hangs?! I verified this with a dump of to tables that I imported on various few different systems. I created a new test-database, piped the dump into that and ran the following queries. It looks like the join-behaviour of mysql has totally changed in mysql5! This seems to affect quite a lot of queries here. Am I doing really stupid mistakes or did I miss a major chapter in the "upgrading to mysql5"-documentation that I read over and over during the last days? Jan I put the mysql versions in front of the "mysql" prompts: 4.1.13-standard mysql show table status; +--++-++++-+-+--+---++-+-+-+---+--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--++-++++-+-+--+---++-+-+-+---+--++-+ | dlstm_data_d | MyISAM | 9 | Dynamic | 48621 | 49 | 2428108 | 4294967295 | 1921024 | 0 | NULL | 2006-02-01 11:54:57 | 2006-02-01 11:55:08 | 2006-02-01 11:55:09 | latin1_swedish_ci | NULL | | | | stm_data_d | MyISAM | 9 | Dynamic | 480772 | 105 | 50816164 | 4294967295 | 57697280 | 0 | NULL | 2006-02-01 11:55:09 | 2006-02-01 11:57:00 | 2006-02-01 11:58:58 | latin1_swedish_ci | NULL | | | +--++-++++-+-+--+---++-+-+-+---+--++-+ 2 rows in set (0.00 sec) 5.0.18-max-log mysql show table status; +--++-++++-+-+--+---++-+-+-+---+--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--++-++++-+-+--+---++-+-+-+---+--++-+ | dlstm_data_d | MyISAM | 10 | Dynamic | 48621 | 49 | 2384860 | 281474976710655 | 1836032 | 0 | NULL | 2006-02-01 11:54:05 | 2006-02-01 11:54:07 | 2006-02-01 11:54:07 | latin1_swedish_ci | NULL | | | | stm_data_d | MyISAM | 10 | Dynamic | 480772 | 104 | 50192768 | 281474976710655 | 52738048 | 0 | NULL | 2006-02-01 11:54:07 | 2006-02-01 11:55:12 | 2006-02-01 11:55:40 | latin1_swedish_ci | NULL | | | +--++-++++-+-+--+---++-+-+-+---+--++-+ 2 rows in set (0.00 sec)
Re: Totally different join-behaviour in mysql 4 and 5?
Comma separated JOINS strikes again!!! [...] Here is where you will find this change documented in the manual: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html I read that page over and over again... probably too late at night. thanks for that info. Thanks to Peter, too. I made that select out of an multi-table update that hung in the processlist. That query came out of a very old script floating around... I found a few more and modified the updates, i think I shouldn't have that problem any more. I think nobody over here ever used comma-joins except for those few times. At least I hope so. ;) but that was only part of my problems, my replication still hangs every now and then... I'd be glad if you could have a look at my other postings 5.0.18-max-log as a slave of a 4.1.13-standard-log master problem - slave hangs. Maybe you have any ideas what I could try to get this working... thanks Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
About union sql Mysql 4.x
Hi, I am trying to run an sql query such as below to list items=x randomly and then items != x randomly.. mysql (select * from tablea where item=1 order by rand()) union all (select * from tablea where item != 1 order by rand()); but the result is not as expected. rand() seems not to be working... What can be the problem? or what can be the difference between my will and the exact result of the query? Thanks.. Halil Demirezen System Support Engineer/ Sistem Destek Muhendisi Mobile Tel/Cep Tel: +90(543) 502 04 42 E-Mail/E-Posta: [EMAIL PROTECTED] ---
Re: About union sql Mysql 4.x
Hello. From http://dev.mysql.com/doc/refman/5.0/en/union.html: ORDER BY for individual SELECT statements within parentheses has an effect only when combined with LIMIT. Otherwise, the ORDER BY is optimized away. Therefore you're getting the same results, because ORDER BY doen't work for your query. HALIL DEMIREZEN wrote: Hi, I am trying to run an sql query such as below to list items=x randomly and then items != x randomly.. mysql (select * from tablea where item=1 order by rand()) union all (select * from tablea where item != 1 order by rand()); but the result is not as expected. rand() seems not to be working... What can be the problem? or what can be the difference between my will and the exact result of the query? Thanks.. Halil Demirezen System Support Engineer/ Sistem Destek Muhendisi Mobile Tel/Cep Tel: +90(543) 502 04 42 E-Mail/E-Posta: [EMAIL PROTECTED] --- -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About union sql Mysql 4.x
What if i want to list all the records not limiting them to a constant? Halil Demirezen System Support Engineer/ Sistem Destek Muhendisi Mobile Tel/Cep Tel: +90(543) 502 04 42 E-Mail/E-Posta: [EMAIL PROTECTED] --- Gleb Paharenko [EMAIL PROTECTED] 05.12.2005 12:19 To mysql@lists.mysql.com cc Subject Re: About union sql Mysql 4.x Hello. From http://dev.mysql.com/doc/refman/5.0/en/union.html: ORDER BY for individual SELECT statements within parentheses has an effect only when combined with LIMIT. Otherwise, the ORDER BY is optimized away. Therefore you're getting the same results, because ORDER BY doen't work for your query. HALIL DEMIREZEN wrote: Hi, I am trying to run an sql query such as below to list items=x randomly and then items != x randomly.. mysql (select * from tablea where item=1 order by rand()) union all (select * from tablea where item != 1 order by rand()); but the result is not as expected. rand() seems not to be working... What can be the problem? or what can be the difference between my will and the exact result of the query? Thanks.. Halil Demirezen System Support Engineer/ Sistem Destek Muhendisi Mobile Tel/Cep Tel: +90(543) 502 04 42 E-Mail/E-Posta: [EMAIL PROTECTED] --- -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: About union sql Mysql 4.x
HALIL DEMIREZEN wrote: Hi, I am trying to run an sql query such as below to list items=x randomly and then items != x randomly.. mysql (select * from tablea where item=1 order by rand()) union all (select * from tablea where item != 1 order by rand()); but the result is not as expected. rand() seems not to be working... What can be the problem? or what can be the difference between my will and the exact result of the query? Gleb Paharenko wrote: Hello. From http://dev.mysql.com/doc/refman/5.0/en/union.html: ORDER BY for individual SELECT statements within parentheses has an effect only when combined with LIMIT. Otherwise, the ORDER BY is optimized away. Therefore you're getting the same results, because ORDER BY doen't work for your query. HALIL DEMIREZEN wrote: What if i want to list all the records not limiting them to a constant? How about SELECT * FROM tablea ORDER BY (item != 1), RAND(); Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About union sql Mysql 4.x
Michael, Thank you and all for effort to help.. I solved the problem by giving high limit numbers such as; (select * from tablea where item=1 order by rand() limit 0, 1) union all (select * from tablea where item != 1 order by rand() limit 0, 1); Halil Demirezen System Support Engineer/ Sistem Destek Muhendisi Mobile Tel/Cep Tel: +90(543) 502 04 42 E-Mail/E-Posta: [EMAIL PROTECTED] --- Michael Stassen [EMAIL PROTECTED] 05.12.2005 15:46 To HALIL DEMIREZEN [EMAIL PROTECTED] cc Gleb Paharenko [EMAIL PROTECTED], mysql@lists.mysql.com Subject Re: About union sql Mysql 4.x HALIL DEMIREZEN wrote: Hi, I am trying to run an sql query such as below to list items=x randomly and then items != x randomly.. mysql (select * from tablea where item=1 order by rand()) union all (select * from tablea where item != 1 order by rand()); but the result is not as expected. rand() seems not to be working... What can be the problem? or what can be the difference between my will and the exact result of the query? Gleb Paharenko wrote: Hello. From http://dev.mysql.com/doc/refman/5.0/en/union.html: ORDER BY for individual SELECT statements within parentheses has an effect only when combined with LIMIT. Otherwise, the ORDER BY is optimized away. Therefore you're getting the same results, because ORDER BY doen't work for your query. HALIL DEMIREZEN wrote: What if i want to list all the records not limiting them to a constant? How about SELECT * FROM tablea ORDER BY (item != 1), RAND(); Michael
Re: About union sql Mysql 4.x
Hello. If you want all records with item=1 to be at the beginning and sorted in a random order you can use this query: select * from tablea order by if(item=1,0,1), rand(); HALIL DEMIREZEN wrote: What if i want to list all the records not limiting them to a constant? Halil Demirezen System Support Engineer/ Sistem Destek Muhendisi Mobile Tel/Cep Tel: +90(543) 502 04 42 E-Mail/E-Posta: [EMAIL PROTECTED] --- Gleb Paharenko [EMAIL PROTECTED] 05.12.2005 12:19 To mysql@lists.mysql.com cc Subject Re: About union sql Mysql 4.x Hello. From http://dev.mysql.com/doc/refman/5.0/en/union.html: ORDER BY for individual SELECT statements within parentheses has an effect only when combined with LIMIT. Otherwise, the ORDER BY is optimized away. Therefore you're getting the same results, because ORDER BY doen't work for your query. HALIL DEMIREZEN wrote: Hi, I am trying to run an sql query such as below to list items=x randomly and then items != x randomly.. mysql (select * from tablea where item=1 order by rand()) union all (select * from tablea where item != 1 order by rand()); but the result is not as expected. rand() seems not to be working... What can be the problem? or what can be the difference between my will and the exact result of the query? Thanks.. Halil Demirezen System Support Engineer/ Sistem Destek Muhendisi Mobile Tel/Cep Tel: +90(543) 502 04 42 E-Mail/E-Posta: [EMAIL PROTECTED] --- -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About union sql Mysql 4.x
HALIL DEMIREZEN wrote: Michael, Thank you and all for effort to help.. I solved the problem by giving high limit numbers such as; (select * from tablea where item=1 order by rand() limit 0, 1) union all (select * from tablea where item != 1 order by rand() limit 0, 1); I think this is not so much a solution as a temporary work-around. It will stop working correctly, without giving any errors, as soon as your table gets to a certain size. Perhaps that will never happen in this particular case, but I think this sort of thing is a bad idea, in general. Your desire is to sort the rows of tablea. You want all the rows with item = 1 first, then all the rest. Within each group (item = 1, item != 1), you want the rows in random order. You should see that this is simply a two step ordering. Instead of trying to fake that with unioned queries, you should solve it directly in your ORDER BY clause. Both the query Gleb sent, SELECT * FROM tablea ORDER BY IF(item=1,0,1), RAND(); and the one I sent, SELECT * FROM tablea ORDER BY (item != 1), RAND(); do just that. Both will provide the results you asked for, and neither will break when the table reaches some particular size. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Linux Yum DELETED mysql 4! Can I install mysql 5 and be on my way?
Hello. I do have my data Check if yum has cleared the data directory, if not, you should be able to recover the data. Can I install mysql 5, and it will migrate/work with my existing data? In my opinion it is a good idea, but be aware of compatibility issues for your applications which are used working with MySQL 3.xx (it is shipped by default with Fedora 3). Have a look here before an upgrade: http://dev.mysql.com/doc/refman/5.0/en/upgrade.html Hi; On FC3, for some silly reason, Yum deleted my mysql 4.1x production server! I do have my data. Now that I am in this hole, maybe I should go to mysql 5. Can I install mysql 5, and it will migrate/work with my existing data? Thanks,Nathan Gross wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Linux Yum DELETED mysql 4! Can I install mysql 5 and be on my way?
Hi; On FC3, for some silly reason, Yum deleted my mysql 4.1x production server! I do have my data. Now that I am in this hole, maybe I should go to mysql 5. Can I install mysql 5, and it will migrate/work with my existing data? Thanks, -nat
Re: Linux Yum DELETED mysql 4! Can I install mysql 5 and be on my way?
I think you should go through the change log just incase. Especially if you are using one of the api's in your apps (php, etc...). Don't be rash about upgrading if it is production. - Sent from my NYPL BlackBerry Handheld. - Original Message - From: Nathan Gross [EMAIL PROTECTED] Sent: 11/07/2005 03:19 PM To: mysql@lists.mysql.com Subject: Linux Yum DELETED mysql 4! Can I install mysql 5 and be on my way? Hi; On FC3, for some silly reason, Yum deleted my mysql 4.1x production server! I do have my data. Now that I am in this hole, maybe I should go to mysql 5. Can I install mysql 5, and it will migrate/work with my existing data? Thanks, -nat
RE: Linux Yum DELETED mysql 4! Can I install mysql 5 and be on my way?
MySql v3.23 is part of the Fedore Core 3 distribution. MySQL v4.1 only became standard in Fedora Core 4. I suspect it's one of the non-Fedora repositories you're using. Check your logs and also check /etc/yum.repos.d grep enabled /etc/yum/repos.d/* Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- Hi; On FC3, for some silly reason, Yum deleted my mysql 4.1x production server! I do have my data. Now that I am in this hole, maybe I should go to mysql 5. Can I install mysql 5, and it will migrate/work with my existing data? Thanks, -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNIQUE issue at mysql 4.* and mysql 5.*
Hi all I've created a table in mysql 4.1.12 and 5.0.9-beta-standard as follow | manager_new | CREATE TABLE `manager_new` ( `id` bigint(20) NOT NULL auto_increment, `id_from_hits` bigint(20) default NULL, `from_ip` varchar(15) default NULL, `pool` char(1) NOT NULL default 'n', `to_ip` varchar(15) default '', `lstupdate` date default NULL, `comment` varchar(50) default NULL, `added_by` enum('yariv','eddie','lisman','sweiss','interface') default 'interface', `black_white` char(1) NOT NULL default 'B', PRIMARY KEY (`id`), UNIQUE KEY `from_ip` (`from_ip`,`to_ip`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 The two tables are EMPTY ,where I try this insert in each version I get : mysql 5 mysql insert into manager_new (id_from_hits,from_ip,to_ip,lstupdate,comment) values (39,'192.118.68.5','192.118.68.5',now(),'added automatically'); ERROR 1062 (23000): Duplicate entry '192.118.68.5-192.118.68.5' for key 2 mysql 4 mysql insert into manager_new (id_from_hits,from_ip,to_ip,lstupdate,comment) values (39,'192.118.68.5','192.118.68.5',now(),'added automatically'); Query OK, 1 row affected (0.00 sec) Is there any change in the way that mysql 5 treats UNIQUE index? or is it a bug? Best regards Yariv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNIQUE issue at mysql 4.* and mysql 5.*
Hi all I've created a table in mysql 4.1.12 and 5.0.9-beta-standard as follow | manager_new | CREATE TABLE `manager_new` ( `id` bigint(20) NOT NULL auto_increment, `id_from_hits` bigint(20) default NULL, `from_ip` varchar(15) default NULL, `pool` char(1) NOT NULL default 'n', `to_ip` varchar(15) default '', `lstupdate` date default NULL, `comment` varchar(50) default NULL, `added_by` enum('yariv','eddie','lisman','sweiss','interface') default 'interface', `black_white` char(1) NOT NULL default 'B', PRIMARY KEY (`id`), UNIQUE KEY `from_ip` (`from_ip`,`to_ip`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 The two tables are EMPTY ,where I try this insert in each version I get : mysql 5 mysql insert into manager_new (id_from_hits,from_ip,to_ip,lstupdate,comment) values (39,'192.115.52.5','92.115.52.5',now(),'added automatically'); ERROR 1062 (23000): Duplicate entry '192.118.68.5-192.118.68.5' for key 2 mysql 4 mysql insert into manager_new (id_from_hits,from_ip,to_ip,lstupdate,comment) values (39,'192.115.52.5','192.115.52.5',now(),'added automatically'); Query OK, 1 row affected (0.00 sec) Is there any change in the way that mysql 5 treats UNIQUE index? or is it a bug? Best regards Yariv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNIQUE issue at mysql 4.* and mysql 5.*
Hello. It works for me on MySQL 5.0.10: mysql CREATE TABLE `manager_new` ( - `id` bigint(20) NOT NULL auto_increment, - `id_from_hits` bigint(20) default NULL, - `from_ip` varchar(15) default NULL, - `pool` char(1) NOT NULL default 'n', - `to_ip` varchar(15) default '', - `lstupdate` date default NULL, - `comment` varchar(50) default NULL, - `added_by` enum('yariv','eddie','lisman','sweiss','interface') default 'interface', - `black_white` char(1) NOT NULL default 'B', - PRIMARY KEY (`id`), - UNIQUE KEY `from_ip` (`from_ip`,`to_ip`) - ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; Query OK, 0 rows affected (0.22 sec) mysql insert into manager_new (id_from_hits,from_ip,to_ip,lstupdate,comment) values (39,'192.118.68.5','192.118.68.5',now(),'added automatically'); Query OK, 1 row affected (0.04 sec) mysql select version(); +---+ | version() | +---+ | 5.0.10-beta-debug-log | +---+ Yariv Graf [EMAIL PROTECTED] wrote: Hi all I've created a table in mysql 4.1.12 and 5.0.9-beta-standard as follow | manager_new | CREATE TABLE `manager_new` ( `id` bigint(20) NOT NULL auto_increment, `id_from_hits` bigint(20) default NULL, `from_ip` varchar(15) default NULL, `pool` char(1) NOT NULL default 'n', `to_ip` varchar(15) default '', `lstupdate` date default NULL, `comment` varchar(50) default NULL, `added_by` enum('yariv','eddie','lisman','sweiss','interface') default 'interface', `black_white` char(1) NOT NULL default 'B', PRIMARY KEY (`id`), UNIQUE KEY `from_ip` (`from_ip`,`to_ip`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 The two tables are EMPTY ,where I try this insert in each version I get : mysql 5 mysql insert into manager_new (id_from_hits,from_ip,to_ip,lstupdate,comment) values (39,'192.118.68.5','192.118.68.5',now(),'added automatically'); ERROR 1062 (23000): Duplicate entry '192.118.68.5-192.118.68.5' for key 2 mysql 4 mysql insert into manager_new (id_from_hits,from_ip,to_ip,lstupdate,comment) values (39,'192.118.68.5','192.118.68.5',now(),'added automatically'); Query OK, 1 row affected (0.00 sec) Is there any change in the way that mysql 5 treats UNIQUE index? or is it a bug? Best regards Yariv -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compat RPM for MySQL 4.x on RHEL3 [PRESS RELEASE]
PD Inc. http://www.pdinc.us 156 Briarvista Way North East Atlanta, Georgia 30329-3615 +1(443)269-1555 EIN:52-2351863 August 1, 2005 Document#20050801P00 References#--- PRESS RELEASE: For immediate release. Contact:Ning Li [EMAIL PROTECTED] (443) 921-0381 PD Inc. has open-sourced their compatibility RPM that enables system administrators to install MySQL 4.x on Red Hat Enterprise Linux (RHEL3) Operating System with minimal impact. Baltimore -- August 1, 2005 -- PD Inc. today announced the release of their MySQL-shared compat RPMs for RHEL3. With these compatibility RPMs a system administrator can now upgrade to MySQL 4.x whilst keeping legacy libmysqlclient.so.10 and libmysqlclient.so.12 client libraries as provided by the RPMs and maintaining maximum Red Hat support compliance. Red Hat Enterprise Linux (RHEL) Operating System is one of the most commonly used open-source operating systems. More than 1/3 of the server market uses RHEL, including all Linux servers from IBM, and Dell. There are current limitations on RHEL support, limiting system administrators from reasonable deviations in application selection on the system. MySQL greater than 3.23 is not supported by Red Hat, Inc. on RHEL3. Normally to upgrade to MySQL 4.x many other applications on the system need to be upgraded and/or recompiled including Apache, PHP, and Perl. In doing so, Red Hat, Inc. no longer will support these system modifications. To solve this problem, PD Inc. has now released two compatibility RPM solutions. One for MySQL 4.0 and the other for MySQL 4.1. This open-source software allows system administrator to upgrade MySQL 4.x without upgrading other applications. With the a compat RPM, an IT department can minimize the non compliant applications installed, reduce the workload on staff, and ensure a smoother transition. These RPMs can be downloaded from: http://public.pdinc.us/rpms/mysql About Red Hat, Inc. Red Hat, the world's leading open source and Linux provider, is headquartered in Raleigh, NC with satellite offices spanning the globe. Red Hat is leading Linux and open source solutions into the mainstream by making high quality, low cost technology accessible. Red Hat provides operating system software along with middleware, applications and management solutions. Red Hat also offers support, training and consulting services to its customers worldwide and through top-tier partnerships. Red Hat's open source strategy offers customers a long term plan for building infrastructures that are based on and leverage open source technologies with focus on security and ease of management. Learn more: http://www.redhat.com About MySQL AB MySQL AB develops and supports a family of high performance, affordable database products -- including MySQL Network, a comprehensive set of certified software and premium support services. The company's flagship product is MySQL, the world's most popular open source database, with more than six million active installations. Many of the world's largest organizations, including Yahoo!, Sabre Holdings, The Associated Press, Suzuki and NASA are realizing significant cost savings by using MySQL to power high-volume Web sites, business-critical enterprise applications and packaged software. http://www.mysql.com About PD Inc. PD Inc is a business solutions provider. The company provides expert solutions for tailored consulting projects on an enterprise-wide scale. PD Inc. uses an applied think tank structure to integrate business solutions, create a clear well-defined architecture, and target horizontal markets for it's customers. Because each of the company's consultants have strong backgrounds in their specific field, PD Inc. is able to focus on vertical markets as well. PD Inc provides solutions in general engineering, electrical computer engineering, e-commerce, software development, business method analysis, and procurement. It's customer base draws from federal and municipal governments, IT firms, biotech corporations, and Fortune 500 companies. For more information see http://www.pdinc.us ### -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Ning Li PD Inc. http://www.pdinc.us - - Marketing Trade Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com
Mysql 4.x Error
Hi, Im using : mysqlclient10-3.23.58-6 ,mysql-server-4.1.11-2,mysql-4.1.11-2 On fedora 3. when i run the following command its gives Error: [EMAIL PROTECTED] etc]$ /etc/init.d/mysqld start touch: cannot touch `/var/log/mysqld.log': Permission denied chmod: changing permissions of `/var/log/mysqld.log': Operation not permitted Initializing MySQL database: mkdir: cannot create directory `/var/lib/mysql/mysql': Permission denied chmod: cannot access `/var/lib/mysql/mysql': No such file or directory mkdir: cannot create directory `/var/lib/mysql/test': Permission denied chmod: cannot access `/var/lib/mysql/test': No such file or directory Installing all prepared tables /usr/libexec/mysqld: error while loading shared libraries: libssl.so.5: cannot open shared object file: No such file or directory Installation of system tables failed! Examine the logs in /var/lib/mysql for more information. You can also try to start the mysqld daemon with: /usr/libexec/mysqld --skip-grant You can use the command line tool /usr/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /var/lib/mysql that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/bin/mysqlbug script! [FAILED] why ? the above error is comming..? When i try to remove libssl.so.4 from my system and install libssl.so.5 in the system ,the system does not start.As libssl.so.4 is required. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.x Error
Hello. You have several problems and they're should be resolved step by step. First fix the problems with permissions. Usually they're solved by changing the owner of mysql database to mysql user. Check what's wrong with /var/log/mysqld.log. It might be necessery to create it manually. Why are you removing libssl.so.4. I think these libraries could exists both in the system. plaza c [EMAIL PROTECTED] wrote: Hi, Im using : mysqlclient10-3.23.58-6 ,mysql-server-4.1.11-2,mysql-4.1.11-2 On fedora 3. when i run the following command its gives Error: [EMAIL PROTECTED] etc]$ /etc/init.d/mysqld start touch: cannot touch `/var/log/mysqld.log': Permission denied chmod: changing permissions of `/var/log/mysqld.log': Operation not permitt= ed Initializing MySQL database: mkdir: cannot create directory `/var/lib/mysql/mysql': Permission denied chmod: cannot access `/var/lib/mysql/mysql': No such file or directory mkdir: cannot create directory `/var/lib/mysql/test': Permission denied chmod: cannot access `/var/lib/mysql/test': No such file or directory Installing all prepared tables /usr/libexec/mysqld: error while loading shared libraries: libssl.so.5: cannot open shared object file: No such file or directory Installation of system tables failed! Examine the logs in /var/lib/mysql for more information. You can also try to start the mysqld daemon with: /usr/libexec/mysqld --skip-grant You can use the command line tool /usr/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /var/lib/mysql that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db= ', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/bin/mysqlbug script! [FAILED] ---= - why ? the above error is comming..? When i try to remove libssl.so.4 from my system and install=20 libssl.so.5 in the system ,the system does not start.As libssl.so.4 is required. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Install Mysql 4 on Fedora Core 2
I want to install MySQL 4 on Fedora Core 2. Questions: #1. Do I need to uninstall MySQL 3 from the server first? #2. Where I can get source for MySQL 4? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Install Mysql 4 on Fedora Core 2
Jerry Swanson wrote: #1. Do I need to uninstall MySQL 3 from the server first? Not really. #2. Where I can get source for MySQL 4? At your local 7/11. They usually store it hidden behind the counter along with the most popular nuclear launch codes. If they are out of stock, you might consider browsing mysql's site at http://www.mysql.com. Specifically http://dev.mysql.com/downloads/mysql/4.0.html might be interesting. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Install Mysql 4 on Fedora Core 2
I want to install MySQL 4 on Fedora Core 2. Questions: #1. Do I need to uninstall MySQL 3 from the server first? http://dev.mysql.com/doc/mysql/en/upgrading-from-3-23.html #2. Where I can get source for MySQL 4? http://dev.mysql.com/downloads/ Go to the page of the version you like to install. It's advised to install the appropriate binary package, but if you really want the sources you can find them near the end of the page. http://dev.mysql.com/downloads/mysql/4.0.html http://dev.mysql.com/downloads/mysql/4.1.html Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Install Mysql 4 on Fedora Core 2
Hello. Use the rpm or binary distribution from: http://dev.mysql.com/downloads/mysql/4.1.html If you install MySQL using rpm with -U command line option, it will remove the old package. You may meet some problems using PHP or Perl when you try to connect through they was compiled for 3.xx version. See notes about them at: http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html Jerry Swanson [EMAIL PROTECTED] wrote: I want to install MySQL 4 on Fedora Core 2. Questions: #1. Do I need to uninstall MySQL 3 from the server first? #2. Where I can get source for MySQL 4? Thanks -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4 Old Password
Is there a way to tell mysql 4 to defaul to the old style passwords instead of having to run this every time we add a user? SET PASSWORD FOR mailto:'user'@'localhost' 'user'@'localhost' = OLD_PASSWORD('passowordo'); Applications we develop in php, and phpMyadmin don't seem to work with the new style password. Thanks!
Re: MySQL 4 Old Password
On Friday 03 June 2005 03:36 pm, Joey wrote: SET PASSWORD FOR mailto:'user'@'localhost' 'user'@'localhost' = OLD_PASSWORD('passowordo'); Why not recompile php to work with 4.1? --old-password Jeff pgpEctk2PnMK1.pgp Description: PGP signature
Re: MySQL 4 Old Password
Joey wrote: Is there a way to tell mysql 4 to defaul to the old style passwords instead of having to run this every time we add a user? SET PASSWORD FOR mailto:'user'@'localhost' 'user'@'localhost' = OLD_PASSWORD('passowordo'); Applications we develop in php, and phpMyadmin don't seem to work with the new style password. Thanks! Did you check the manual? http://dev.mysql.com/doc/mysql/en/password-hashing.html http://dev.mysql.com/doc/mysql/en/old-client.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fedora Core 2: upgrade mysql 3 to mysql 4
Hello. I want to upgrade mysql 3 to mysql 4. At first, you should read: http://dev.mysql.com/doc/mysql/en/upgrade.html Search the MySQL lists archives about possible problems which you can expect. It is strongly recommended to make a backup. How to remove mysql3 and install mysql 4? It depends on what kind of distribution you have. Probably rpm -U would help for the rpm distribution. Jerry Swanson [EMAIL PROTECTED] wrote: I want to upgrade mysql 3 to mysql 4. Operating System is Fedore Core 2. Is there any package dependency? How to remove mysql3 and install mysql 4? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fedora Core 2: upgrade mysql 3 to mysql 4
I want to upgrade mysql 3 to mysql 4. Operating System is Fedore Core 2. Is there any package dependency? How to remove mysql3 and install mysql 4? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Authorizing Problem in MySQL 4 with Mandrake 10
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hash: SHA1 Hi All, I installed a fresh copy of Mandrake Linux 10.0 with MySQL. I tried to login to MySQL server ,But it says that authorization is failed. My hostname is localhost and user is root.I entered the root password. Here is the log :- [EMAIL PROTECTED] html]# mysql -h localhost -u root -p Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Please help me to solve this problem. Best Regards, Prabath. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFCHRXVIlUQPx/AjvYRAo2fAKCJ+DWXNgSbfW3RySeJGADUSytjWACgxHYt SHHwE2P22LankU/ovx/s28M= =k/T0 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Authorizing Problem in MySQL 4 with Mandrake 10
Prabath Ranasinghe (by way of Prabath Ranasinghe [EMAIL PROTECTED]) ha scritto: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hash: SHA1 Hi All, I installed a fresh copy of Mandrake Linux 10.0 with MySQL. I tried to login to MySQL server ,But it says that authorization is failed. My hostname is localhost and user is root.I entered the root password. Here is the log :- [EMAIL PROTECTED] html]# mysql -h localhost -u root -p Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Please help me to solve this problem. Best Regards, Prabath. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFCHRXVIlUQPx/AjvYRAo2fAKCJ+DWXNgSbfW3RySeJGADUSytjWACgxHYt SHHwE2P22LankU/ovx/s28M= =k/T0 -END PGP SIGNATURE- If you still have NO password trying to use it will give you an error ;) try # mysql -h localhost -u root regards Francesco Riosa -- No problem is so formidable that you can't walk away from it. ~ Charles M. Schulz But sometimes run fast is better ~ Francesco R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Authorizing Problem in MySQL 4 with Mandrake 10
Be careful maybe you are mistaking here: the mysql root user as nothing to see with the mandrake root user. For exemple I lauch the mysql server as root from command line whereas I'm just a user on my desktop. So the mandrake root password and the mysql root password are also completly different. maybe this can be helpful: http://dev.mysql.com/doc/mysql/en/unix-post-installation.html From: Prabath Ranasinghe [EMAIL PROTECTED] (by way of Prabath Ranasinghe [EMAIL PROTECTED]) Reply-To: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Authorizing Problem in MySQL 4 with Mandrake 10 Date: Wed, 23 Feb 2005 18:46:29 -0500 -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hash: SHA1 Hi All, I installed a fresh copy of Mandrake Linux 10.0 with MySQL. I tried to login to MySQL server ,But it says that authorization is failed. My hostname is localhost and user is root.I entered the root password. Here is the log :- [EMAIL PROTECTED] html]# mysql -h localhost -u root -p Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Please help me to solve this problem. Best Regards, Prabath. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFCHRXVIlUQPx/AjvYRAo2fAKCJ+DWXNgSbfW3RySeJGADUSytjWACgxHYt SHHwE2P22LankU/ovx/s28M= =k/T0 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: download mysql 4 in rpm for Redhat 9.0
Hello. See: http://dev.mysql.com/downloads/mysql/4.1.html sam wun [EMAIL PROTECTED] wrote: Hi, I realised that install mysql 4 thru rpm is the best way for a smooth installation. Can anyone please point me to a site where I can download mysql 4 rpm for Redhat9? Thanks Sam -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
download mysql 4 in rpm for Redhat 9.0
Hi, I realised that install mysql 4 thru rpm is the best way for a smooth installation. Can anyone please point me to a site where I can download mysql 4 rpm for Redhat9? Thanks Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help getting mysqld to read my my.cnf on start from init.d on mandrake 10.1 and mysql 4
Hello, I am building a new DB box. The box is mandrake 10.1 and mysql 4.0.20 All my other boxes are RH9 and mysql 3 The one problem I am having is they appear to do the my.cnf differently There was no my.cnf in the /etc dir, so I created one from my-huge.cnf and placed it in the /etc But the startup does not use it. So I tried to append the same command to the mysql script that is called from init.d --defaults-file=/etc/my.cnf and I get the error 041215 10:51:59 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'defaults-file=/etc/my.cnf' 041215 10:51:59 mysqld ended That part that confuses me is in the script /etc/rc.d/init.d/mysqld (I put in some debug echos to see where it is exiting) When I run the startup it print 2 But I am not failure with /usr/bin/my_print_defaults Thanks for any help! Randy ==SNIP=== # Get arguments from the my.cnf file, # groups [mysqld] [mysql_server] and [mysql.server] if test -x ./bin/my_print_defaults then echo 1 print_defaults=./bin/my_print_defaults elif test -x $bindir/my_print_defaults then echo 2 print_defaults=$bindir/my_print_defaults elif test -x $bindir/mysql_print_defaults then echo 3 print_defaults=$bindir/mysql_print_defaults else echo I am here # Try to find basedir in /etc/my.cnf conf=/etc/my.cnf print_defaults= if test -r $conf then subpat='^[^=]*basedir[^=]*=\(.*\)$' dirs=`sed -e /$subpat/!d -e 's//\1/' $conf` for d in $dirs do d=`echo $d | sed -e 's/[ ]//g'` if test -x $d/bin/my_print_defaults then print_defaults=$d/bin/my_print_defaults break fi if test -x $d/bin/mysql_print_defaults then print_defaults=$d/bin/mysql_print_defaults break fi done fi # Hope it's in the PATH ... but I doubt it test -z $print_defaults print_defaults=my_print_defaults Fi ==SNIP=== -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help getting mysqld to read my my.cnf on start from init.d on mandrake 10.1 and mysql 4
At 11:02 -0600 12/15/04, Randy Paries wrote: Hello, I am building a new DB box. The box is mandrake 10.1 and mysql 4.0.20 All my other boxes are RH9 and mysql 3 The one problem I am having is they appear to do the my.cnf differently There was no my.cnf in the /etc dir, so I created one from my-huge.cnf and placed it in the /etc But the startup does not use it. Run mysqld --help and look at the help message to see what option files the server thinks it's supposed to read. One possibility is that it reads /etc/mysql/my.cnf instead of /etc/my.cnf -- I believe some vendors hack MySQL to change the location of the global option file. So I tried to append the same command to the mysql script that is called from init.d --defaults-file=/etc/my.cnf and I get the error 041215 10:51:59 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'defaults-file=/etc/my.cnf' 041215 10:51:59 mysqld ended The probable cause of this is that --defaults-file is not the first option. That part that confuses me is in the script /etc/rc.d/init.d/mysqld (I put in some debug echos to see where it is exiting) When I run the startup it print 2 But I am not failure with /usr/bin/my_print_defaults Thanks for any help! Randy ==SNIP=== # Get arguments from the my.cnf file, # groups [mysqld] [mysql_server] and [mysql.server] if test -x ./bin/my_print_defaults then echo 1 print_defaults=./bin/my_print_defaults elif test -x $bindir/my_print_defaults then echo 2 print_defaults=$bindir/my_print_defaults elif test -x $bindir/mysql_print_defaults then echo 3 print_defaults=$bindir/mysql_print_defaults else echo I am here # Try to find basedir in /etc/my.cnf conf=/etc/my.cnf print_defaults= if test -r $conf then subpat='^[^=]*basedir[^=]*=\(.*\)$' dirs=`sed -e /$subpat/!d -e 's//\1/' $conf` for d in $dirs do d=`echo $d | sed -e 's/[ ]//g'` if test -x $d/bin/my_print_defaults then print_defaults=$d/bin/my_print_defaults break fi if test -x $d/bin/mysql_print_defaults then print_defaults=$d/bin/mysql_print_defaults break fi done fi # Hope it's in the PATH ... but I doubt it test -z $print_defaults print_defaults=my_print_defaults Fi ==SNIP=== -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help getting mysqld to read my my.cnf on start from init.d on mandrake 10.1 and mysql 4
At 11:23 -0600 12/15/04, Randy Paries wrote: So I tried $bindir/mysqld_safe --defaults-file=/etc/my.cnf --datadir=$datadir --defaults-file=/etc/my.cnf --pid-file=$pid_file /dev/null I get the error:: 041215 11:15:11 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'defaults-file=/etc/my.cnf' 041215 11:15:11 mysqld ended mysqld does accept the --defaults-file, but I am trying to get this to run properly from the init.d startup It appears that mysqld_safe does not accept --defaults-file I guess I could hack the mysqld_safe, but I would really like to do it the correct way... thanks What did mysqld --help report for the set of option files that mysqld will read? -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 15, 2004 11:08 AM To: Randy Paries; [EMAIL PROTECTED] Subject: Re: Help getting mysqld to read my my.cnf on start from init.d on mandrake 10.1 and mysql 4 At 11:02 -0600 12/15/04, Randy Paries wrote: Hello, I am building a new DB box. The box is mandrake 10.1 and mysql 4.0.20 All my other boxes are RH9 and mysql 3 The one problem I am having is they appear to do the my.cnf differently There was no my.cnf in the /etc dir, so I created one from my-huge.cnf and placed it in the /etc But the startup does not use it. Run mysqld --help and look at the help message to see what option files the server thinks it's supposed to read. One possibility is that it reads /etc/mysql/my.cnf instead of /etc/my.cnf -- I believe some vendors hack MySQL to change the location of the global option file. So I tried to append the same command to the mysql script that is called from init.d --defaults-file=/etc/my.cnf and I get the error 041215 10:51:59 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'defaults-file=/etc/my.cnf' 041215 10:51:59 mysqld ended The probable cause of this is that --defaults-file is not the first option. That part that confuses me is in the script /etc/rc.d/init.d/mysqld (I put in some debug echos to see where it is exiting) When I run the startup it print 2 But I am not failure with /usr/bin/my_print_defaults Thanks for any help! Randy ==SNIP=== # Get arguments from the my.cnf file, # groups [mysqld] [mysql_server] and [mysql.server] if test -x ./bin/my_print_defaults then echo 1 print_defaults=./bin/my_print_defaults elif test -x $bindir/my_print_defaults then echo 2 print_defaults=$bindir/my_print_defaults elif test -x $bindir/mysql_print_defaults then echo 3 print_defaults=$bindir/mysql_print_defaults else echo I am here # Try to find basedir in /etc/my.cnf conf=/etc/my.cnf print_defaults= if test -r $conf then subpat='^[^=]*basedir[^=]*=\(.*\)$' dirs=`sed -e /$subpat/!d -e 's//\1/' $conf` for d in $dirs do d=`echo $d | sed -e 's/[ ]//g'` if test -x $d/bin/my_print_defaults then print_defaults=$d/bin/my_print_defaults break fi if test -x $d/bin/mysql_print_defaults then print_defaults=$d/bin/mysql_print_defaults break fi done fi # Hope it's in the PATH ... but I doubt it test -z $print_defaults print_defaults=my_print_defaults Fi ==SNIP=== -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
File locking mysql 4
Does mysql 4 require file locking? I'm not talking about in the database -- I know how to lock and unlock tables for data consistency. I'm referring to the filesystem that the data lives on. For reasons too boring to go in to, I have a mysql server and I'm keeping the database files (i.e., the var directory) on a filesystem that doesn't support file locking. Is this going to pose a problem? If this is being sent to the wrong mailing list, please point me to the right one. Any help will be appreciated! -- Shelly Zhang mysql-zrxve AT mysql-zrxve DOT kicks-ass DOT org (Yes, that address works.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: File locking mysql 4
MySQL itself doesn't require file locking. Infact there is an option to turn it off. --skip-external-locking that disables using the flock() system call. Just watch out that you don't accidently start two mysqlds on the same datadir. -Eric On 24 Jun 2004 13:42:33 -0700, Shelly Zhang [EMAIL PROTECTED] wrote: Does mysql 4 require file locking? I'm not talking about in the database -- I know how to lock and unlock tables for data consistency. I'm referring to the filesystem that the data lives on. For reasons too boring to go in to, I have a mysql server and I'm keeping the database files (i.e., the var directory) on a filesystem that doesn't support file locking. Is this going to pose a problem? If this is being sent to the wrong mailing list, please point me to the right one. Any help will be appreciated! -- Shelly Zhang mysql-zrxve AT mysql-zrxve DOT kicks-ass DOT org (Yes, that address works.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is there any good soul out there who can help me with mysql 4 .0.17 replication.
-Original Message- From: Ravi T To: [EMAIL PROTECTED] Sent: 6/2/04 11:49 AM Subject: Is there any good soul out there who can help me with mysql 4.0.17 replication. Hi, I am new to mysql and having some trouble with replication. Simple stuff like, 1. How to restart the stopped resplication, On the slave: SHOW SLAVE STATUSL Fix the error found in the `Last_error` column 2. How to check if the replication is working, SHOW SLAVE STATUS Both `Slave_IO_Running` and `Slave_SQL_Running` should be `Yes` 3. How to bring the slave into master status when master crashes, The slave needs to be set up to log-bin The change master on the other slaves to point to this new master 4. How to restore master and re-enable replication. Rebuild the tables on the master and sync up the slaves Thanks and appreciate very much if some one could donate the time and help. Ravi T. - Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2.4.25/MySql (4/5) gcc 3.2.3
Hi, I'm using a kernel 2.4.25 system (Slackware distro) installed with gcc 3.2.3. I've tried both 4.0 and 5.0 source tarballs and when configured with : ./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql \ --with-openssl and compiled, I always end up with: gcc -DDEFAULT_BASEDIR=\/usr/local/mysql\ -DDATADIR=\/usr/local/mysql/var\ -DDEFAULT_CHARSET_HOME=\/usr/local/mysql\ -DSHAREDIR=\/usr/local/mysql/share/mysql\ -DHAVE_CONFIG_H -I. -I. -I.. -I../include -I.-O3 -DDBUG_OFF -c `test -f my_semaphore.c || echo './'`my_semaphore.c In file included from ../include/my_semaphore.h:38, from my_semaphore.c:23: /usr/include/semaphore.h:34: field `__sem_lock' has incomplete type make[2]: *** [my_semaphore.o] Error 1 make[2]: Leaving directory `/root/curinsts/mysql-5.0.0-alpha/mysys' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/root/curinsts/mysql-5.0.0-alpha' make: *** [all] Error 2 Now, I've searched with google and came up with a link stating that gcc 3.2.3 doesn't work with the current kernel 2.4.25. And it gave me an example sem.c file: 8 8 #include semaphore.h 8 8 and I compiled with : gcc -c sem.c It works and creates a sem.o file. Is there a more complete test file to check whether it is a kernel issue or a compiler issue? Thanks Edmund -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: building mysql 4 on RH8
Hi! Marius Stan wrote: Hello all, While trying to build MySQL-4.0.18-0.src.rpm downloaded from mysql.com on a RedHat 8 I get these: /usr/lib/gcc-lib/i386-redhat-linux/3.2/../../../libncurses.a(lib_termcap.o): In function `skip_zero': 8 Are you sure you _do_ have ncurses devel packages installed? Google hints me that the __ctype_b error should arise only on RH9+ because it's glibc doesn't export some stuff anymore, while RH8 should be just fine, because: -8 I always build my packages from source, I used to have mysql 4.1, 4.0.18 and 5-alpha compiled and running just fine under RH9 (I said used cause i have just upgraded from RH9 to SuSE 9) I am pretty sure i still have the RH9 packages somewhere. Just let me know if you want my copy. =) Again, I never had problems compilling mysql under RH9. Most of those errors are due to incomplete or not properly configure developement-build environments. Best Regards -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ||Cel: +58-412-8859934 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
building mysql 4 on RH8
Hello all, While trying to build MySQL-4.0.18-0.src.rpm downloaded from mysql.com on a RedHat 8 I get these: /usr/lib/gcc-lib/i386-redhat-linux/3.2/../../../libncurses.a(lib_termcap.o): In function `skip_zero': lib_termcap.o(.text+0xcc): undefined reference to `__ctype_b' /usr/lib/gcc-lib/i386-redhat-linux/3.2/../../../libncurses.a(lib_tparm.o): In function `parse_format': lib_tparm.o(.text+0x1112): undefined reference to `__ctype_b' /usr/lib/gcc-lib/i386-redhat-linux/3.2/../../../libncurses.a(lib_tputs.o): In function `tputs': lib_tputs.o(.text+0x213): undefined reference to `__ctype_b' collect2: ld returned 1 exit status make[2]: *** [mysql] Error 1 make[2]: Leaving directory `/usr/src/redhat/BUILD/mysql-4.0.18/client' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/redhat/BUILD/mysql-4.0.18' make: *** [all] Error 2 error: Bad exit status from /var/tmp/rpm-tmp.95095 (%build) Google hints me that the __ctype_b error should arise only on RH9+ because it's glibc doesn't export some stuff anymore, while RH8 should be just fine, because: # rpm -q glibc glibc-2.3.2-4.80 Any hints ? Can I find an usable mysql4 src.rpm to compile cleanly on my RH8 ? 10x, Marius -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Drop and Add Functions in Mysql 4
You could try MySQL 5(it is in alpha state), which comes with stored procedures, mixing stored procedures and prepered statements could be a possibility. About Sybex's book, haven't read it, but Paul's book (Oreilly's) is very nice. IMHO Best Regards On Thu, 2004-03-04 at 23:23, [EMAIL PROTECTED] wrote: Hi there, i happened to be peering into the Sybex book on Mysql4 , it was pretty expensive about 100 AUD is it a good one ? Anyway i had reference to be able to compile c code into Mysql to add functions into it. Is there anyway posible to avodi doing this and add it using queries ? Let me know thanks. -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...|
Drop and Add Functions in Mysql 4
Hi there, i happened to be peering into the Sybex book on Mysql4 , it was pretty expensive about 100 AUD is it a good one ? Anyway i had reference to be able to compile c code into Mysql to add functions into it. Is there anyway posible to avodi doing this and add it using queries ? Let me know thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bigint support in MySql 4
sorry, I don't know that one. Never have used any version of mysql lower than 4.04. a suggestion might be to call your 4.0 column an unsigned bigint, or if possible an unsigned int if the numbers can fit in 32 bits. unsigned means its always positive. jeff ps. you may want top reply all so the rest of the group can comment. you sent it to me directly. [EMAIL PROTECTED] wrote: Thanks for your quick reply. I had another question. I have some tables with columns of tyoe bigint running on MySql 3.23. When i copied these tables on to MySql 4.0 the values of these columns are displayed negative. When I copy them back the values are correctly displayed. Could you tell me as to how I can handle this situation ? yes it does. we use a bigint(20) for example. [EMAIL PROTECTED] wrote: Hi, I would like to know whether MySql 4.0 supports bigint. If not what is the corresponding data type that I ccould use if I am imoprting tables from MySql 3.2 to MySql 4.0 Thanks, Sharath -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bigint support in MySql 4
Hi, I would like to know whether MySql 4.0 supports bigint. If not what is the corresponding data type that I ccould use if I am imoprting tables from MySql 3.2 to MySql 4.0 Thanks, Sharath -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bigint support in MySql 4
yes it does. we use a bigint(20) for example. [EMAIL PROTECTED] wrote: Hi, I would like to know whether MySql 4.0 supports bigint. If not what is the corresponding data type that I ccould use if I am imoprting tables from MySql 3.2 to MySql 4.0 Thanks, Sharath -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4 goes to sleep with table locks?
El Sáb 07 Feb 2004 02:28, escribió: Mr. Alfredo Pls, will you give the complete structure as 'create query' of your database? that will help us to solve your problem. Pradap This is the structure of the table that holds the sequential numbers for various documents that need them: CREATE TABLE `invcorr` ( `empresa` tinyint(2) unsigned zerofill NOT NULL default '00', `tienda` tinyint(2) unsigned zerofill NOT NULL default '00', `ultfac` bigint(9) unsigned zerofill NOT NULL default '0', `ultfaccred` bigint(9) unsigned zerofill NOT NULL default '0', `ultconsig` bigint(9) unsigned zerofill NOT NULL default '0', `ultdev` bigint(9) unsigned zerofill NOT NULL default '0', `ultped` bigint(9) unsigned zerofill NOT NULL default '0', `ulting` bigint(9) unsigned zerofill NOT NULL default '0', `ultapar` bigint(9) unsigned zerofill NOT NULL default '0', `ultcoti` bigint(9) unsigned zerofill NOT NULL default '0', `ulttras` bigint(9) unsigned zerofill NOT NULL default '0', `ultreq` bigint(9) unsigned zerofill NOT NULL default '0', `ultajus` bigint(9) unsigned zerofill NOT NULL default '0', `timestamp` timestamp(14) NOT NULL, `usuario` varchar(20) default NULL, PRIMARY KEY (`empresa`,`tienda`) ) TYPE=MyISAM; The condensed extract of the C program tha does the locking is as follows: SQLinst = lock tables invcorr write, invfacturas write; state = mysql_query(connection, SQLinst); if(state != 0) { wxMessageBox(mysql_error(mysql), Error:, wxOK | wxICON_EXCLAMATION, this); wxMessageBox(_(Could not lock table!), _(Error:), wxOK | wxICON_EXCLAMATION, this); return; } SQLinst = select ultfac from invcorr where empresa=' + sCodigoEmp + ' and tienda=' + sFacTienda + ' order by tienda; state = mysql_query(connection, SQLinst); result = mysql_store_result(connection); if(mysql_num_rows(result) != 0) // Tiene registros { while((row = mysql_fetch_row(result)) != NULL) { // Asigno valores sFacDocu = row[0] ? row[0] : ; } } mysql_free_result(result); sFacDocu.ToDouble(dFacDocu); dFacDocu++; sFacDocu.Printf(%09.0f, dFacDocu); SQLinst = update invcorr set ultfac=' + sFacDocu + ' where empresa=' + sCodigoEmp + ' and tienda=' + sFacTienda + '; state = mysql_query(connection, SQLinst); SQLinst = unlock tables; state = mysql_query(connection, SQLinst); This final update is what seems to sleep and not react inmediately. The application resides in one central server with dual Xeon 2.8 Ghz CPU's and 6 Gb RAM. All users run the application at the server connecting either via ssh, VNC or Linux Terminal Server Project. During the day, I will get 3 or 4 duplicate numbers per store. Given the fact that reporting sales tax received, depends on a correct sequence of invoices, my company could get into big trouble if authorities think we are trying to avoid sales tax reporting. Thank you for your help. -- Alfredo J. Cole [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4 goes to sleep with table locks?
I am using Mandrake 9.1 and MySQL 4.0.11 from the LM CD's. In my application, I have a table that stores the sequential numbers of invoices prepared by several stores. Every time a salesman prepares an invoice for a customer, the system goes to this table, locks it with lock tables table write, reads the number of the last invoice made for that store, adds one to that number, updates the field, and unlocks the table. In theory, I should never get a duplicate invoice, but in practice, I do. So, it seems that MySQL maintains, under some special circumstances, the same number and does not update it. Maybe there is a parameter in my.cnf I could change to make sure all updates are processed inmediately? Auto increment field would not apply in this case, since there is only one record per store that gets updated for every invoice. I would appreciate any advise. Thank you. -- Alfredo J. Cole [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.x performance tuning
Hi all I have a MySQL installation running on a Pentium3 based system with 2GB RAM... The database primarily uses MyISAM tables with 60% SELECT statements. The database is used as a backend for a PHP web application. Do the following values make any sense? set-variable= key_buffer=200M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=12 set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=10M set-variable= join_buffer_size=8M set-variable= record_buffer=5M set-variable= long_query_time=4 set-variable= query_cache_limit=2M set-variable= query_cache_size=50M set-variable= query_cache_type=2 set-variable= max_connections=200 Is there a way to see if the key buffer size was ever reached? The SHOW STATUS says the following about the Keys: Key blocks used 125400 Key read requests 3518220013 Key reads 94807 Key write requests 17079943 Key writes 8323850 It's interesting to see, that SHOW STATUS shows a lot of generated temporary tables: Created tmp disk tables 28814 Created tmp tables 716581 Created tmp files 506757 Are these values too high? Thanks a lot for your help reto
View Users in MySQL 4
Hello! Got a database I'd like to know what user/s have access to this database. I am able to login to MySQL and can do SHOW DATABASES, Now, I know there's a user supposedly called kong using an specific database. How can I tell if that is so? I am new to MySQL, being this the 1st time I ever use SQL commands. Thanks! __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]