Re: How many pager command within mysql command line client?
I know, all the shell command can do this. Thanks. 2010/8/25 Moon's Father yueliangdao0...@gmail.com Hi. For example, entering mysql command line client, mysql pager more ( or pager md5sum and so on.) I want to know how many command the 'pager' follows? Any reply will be big appreciated.
How many pager command within mysql command line client?
Hi. For example, entering mysql command line client, mysql pager more ( or pager md5sum and so on.) I want to know how many command the 'pager' follows? Any reply will be big appreciated.
Re: Indexing dynamics in MySQL Community Edition 5.1.34
Who can please tell me what is mean of The db storage is on fiber channel.? On Fri, Jun 26, 2009 at 1:05 AM, mos mo...@fastmail.fm wrote: Mike, I re-posted your Show Status to the group to see if anyone can offer a way to speed up the indexing for you. BTW, you are adding ALL of the indexes to the table using ONE sql statement right? And not a separate SQL statement to build each index? Mike At 02:01 AM 6/25/2009, you wrote: Like I said in the subject line, I am using 5.1.34. I started with my-huge.cnf, which says key_buffer rather than key_buffer_size; SHOW GLOBAL VARIABLES confirms, however, that my key_buffer_size is 8GB. That indexing operation finally finished after about 1.5 hours; that was about 0.5 hours ago. Now I am on to other things. Here is the status you suggested: +---+---+ | Variable_name | Value | +---+---+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received| 135 | | Bytes_sent| 1405 | | Com_admin_commands| 0 | | Com_assign_to_keycache| 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 | | Com_alter_function| 0 | | Com_alter_procedure | 0 | | Com_alter_server | 0 | | Com_alter_table | 0 | | Com_alter_tablespace | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_binlog| 0 | | Com_call_procedure| 0 | | Com_change_db | 0 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit| 0 | | Com_create_db | 0 | | Com_create_event | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_procedure | 0 | | Com_create_server | 0 | | Com_create_table | 0 | | Com_create_trigger| 0 | | Com_create_udf| 0 | | Com_create_user | 0 | | Com_create_view | 0 | | Com_dealloc_sql | 0 | | Com_delete| 0 | | Com_delete_multi | 0 | | Com_do| 0 | | Com_drop_db | 0 | | Com_drop_event| 0 | | Com_drop_function | 0 | | Com_drop_index| 0 | | Com_drop_procedure| 0 | | Com_drop_server | 0 | | Com_drop_table| 0 | | Com_drop_trigger | 0 | | Com_drop_user | 0 | | Com_drop_view | 0 | | Com_empty_query | 0 | | Com_execute_sql | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert| 0 | | Com_insert_select | 0 | | Com_install_plugin| 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 0 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_release_savepoint | 0 | | Com_rename_table | 0 | | Com_rename_user | 0 | | Com_repair| 0 | | Com_replace | 0 | | Com_replace_select| 0 | | Com_reset | 0
Re: How can we stock the query result into a text file
Login mysql client. Do the following commands. tee ie.txt; ... notee; On Thu, Jun 25, 2009 at 10:29 PM, John Daisley john.dais...@mypostoffice.co.uk wrote: SELECT INTO OUTFILE does almost the same thing as spool in SQL*Plus SELECT columnname from tablename into outfile '/path/and/filename.txt' Regards John Hi In mysql , how can we stock the query result into a text file the table is too large , i don't want to dump it , but select a part of it, i wrote a script perl to do this, but each time i have to change the parameters in the script to get another table, when i google it, i find the function in Oracle spool 'filenameandpath.txt' select ... from .. / spool off does Mysql has such function? -- - Lin Chun __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
Re: Multiple Rows DELETE Fails on Replication
Could you tell me your detail statements? On Wed, Jun 24, 2009 at 9:46 AM, sangprabv sangpr...@gmail.com wrote: Hi, I found every time I try to do multiple rows delete on replicated MySQL is always failed. Is there any explanation regarding this issue and how to solve it? TIA. Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
Re: How to Optimize distinct with index
Hi. I think you should create an index like this. alter table user add index idx_tmp (key1,key2,key3,user_id) 2009/6/19 Darryle Steplight dstepli...@gmail.com Select user_id from user where key1=value and key2=value2 and key3=value2 GROUP BY user_id is faster than Select distinct user_id from user where key1=value and key2=value2 and key3=value2; 2009/6/18 周彦伟 yanwei.z...@opi-corp.com: Hi, I have a sql : Select distinct user_id from user where key1=value and key2=value2 and key3=value2; I add index on (key1,key2,key3,user_id), this sql use temporary table howevery I have thousands of queries per second. How to optimize it? Anthoer question: Select * from user where user_id in(id1,id2,id3,id4,.) order by use_id; I add index on user_id,but after in,order use temporary table, How to optimize it? Thanks! zhouyanwei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
Re: INNODB INDEX SIZE
Hi. I think innodb will split these into many small pieces and then merge them to execute. On Thu, Jun 18, 2009 at 1:52 PM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi guys, On MIS (management information system) server we have 16GB of physical memory. 10GB has been allocated to innodb_buffer_pool_size. Database size is around 500GB and some tables contains 600millions records. my question is if innodb index_length(25GB) is greater than data_length(20GB). How innodb manage and execute (select query) the join between the bigger table which contains 500millions and 600millions records. Although innodb_buffer_pool has been allocated only 10GB. Any response is highly appreciated. Thanks in advance. Thanks, Krishna -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
Re: replication problem
Hi. Can you give me a flow of your master/slave? On Wed, Jun 24, 2009 at 7:45 PM, 赵琦 tyzha...@gmail.com wrote: hi all: I have three mysql database,tow run as master and the other one runs as slave. Some tables in the database have an autoincreament field named as 'rowid'. These tables have 100 records on master, but some of these tables on the slave only have thousands of record. The tables on the slave are not the same as those on master. When i use 'show slave status', i find that Last_Errno equals to 0. How to solve this problem. -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
Re: IDE
Hi. This is a good choice! On Tue, Jun 23, 2009 at 5:53 PM, Janek Bogucki janek.bogu...@studylink.comwrote: Have you tried SQL Developer? It works great with Oracle and also supports MySQL, http://www.oracle.com/technology/products/database/sql_developer/files/what_is_sqldev.html Cheers, -Janek On Tue, 2009-06-09 at 19:40 +0300, Mosaed zamil wrote: Hello all, I used Database Workbench on trial bases. It is nice. I plan to purchase an IDE. Is is it the best around. Your feed back is appreciated. yours mosaed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
Re: Setting up host password on a shared server
Hi. If you know chinese, I advise that you should visit the website called http://yueliangdao0608.cublog.cn On Sat, Jun 20, 2009 at 10:44 AM, michel compu...@videotron.ca wrote: I have MySQL set up and running, but I am under the impression that I am unable to password protect it properly because I can't protect it as root user. From what I've been reading I should be setting it up as mysqladmin -u root password _thepassword_ But with phpMyAdmin I can still log in as other users. Is there a way around this? I am under the impression that I could not. Much thanks for any help! Michael Katz -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
Re: How to check whether the lock is a share lock or exclusive lock?
Ok. I have already gotten the answer. On Wed, Jun 3, 2009 at 4:15 PM, Moon's Father yueliangdao0...@gmail.comwrote: Hi. I want to know which lock method is used within MySQL? For example, if I query the following statements within MySQL client. mysql begin; Query OK, 0 rows affected (0.00 sec) mysql select * from GroupId_Test where id = 212 lock in share mode; +-+-+ | id | GroupId | +-+-+ | 212 | 3014485 | +-+-+ 1 row in set (0.00 sec) But when I execute statement called show engine innodb status, it only display the total lock numbers. Anybody can tell me how to see whether the lock is a share lock or exclusive lock? -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
Does MySQL have the same function as the ORACLE TDE technique?
Hi. Here is the introduction. http://www.oracle.com/technology/oramag/oracle/05-sep/o55security.html I want to know whether MySQL has the same function as Oracle's? Any reply is appreciated. -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
Re: Should I be able to DELETE FROM ... WHERE .. IN ( ... ) with multiple a column key?
As I know, IN sometimes invoke unmormal index. On Thu, May 28, 2009 at 1:15 AM, Baron Schwartz ba...@xaprb.com wrote: Simon, On Wed, May 27, 2009 at 11:23 AM, Simon J Mudd sjm...@pobox.com wrote: per...@elem.com (Perrin Harkins) writes: On Wed, May 27, 2009 at 6:43 AM, Simon J Mudd sjm...@pobox.com wrote: So is the format of the DELETE FROM .. WHERE ... IN ( ... ) clause I propose valid and SHOULD the optimiser recognise this and be expected to just find the 2 rows by searching on the primary key? Not according to the docs: http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in I'm not sure that the reference makes anything clear. The statements are wrote ARE valid SQL and even though containing mulitiple column values ARE constants. Problem is I'm finding it hard to find a definitive reference to something like this. I'll have to check my Joe Celko books to see if he mentions ths. Nothing's wrong with the SQL -- it's just that MySQL doesn't optimize this type of query well. See http://code.openark.org/blog/mysql/mysql-not-being-able-to-utilize-a-compound-index Regards Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn Comanpy: http://www.actionsky.com
Re: MySQL could not support bit storage?
Thanks for your sincerely reply. On Mon, May 11, 2009 at 11:04 PM, Baron Schwartz ba...@xaprb.com wrote: On Sun, May 10, 2009 at 10:12 PM, Moon's Father yueliangdao0...@gmail.com wrote: Hi. MySQL only has one datatype called bit, but its smallest storage is one byte. How to save a bit on disk, but not a byte? In some cases, CHAR(0) NULL can actually use one bit on disk. You either store the empty string '', or you leave the column NULL. If it is NULL, there is one bit in a bitmask that gets set. If it stores the empty string, it uses no space, and the NULL bit is unset. This is a stupid hack that is probably not a good idea in the general case. Of course, the bitmap of NULL-ness is larger than one bit, so it makes no sense to do this if there is only one such column in the table. And in that case, you might be better off using an integer and packing many bits together into it. -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn Comanpy: http://www.actionsky.com
Lock wait timeout error
Here is the table structure. CREATE TABLE `UP_UserEx` ( `UserId` INT(11) NOT NULL, `UserNationality` CHAR(2) NOT NULL DEFAULT '', `UserProvince` CHAR(2) NOT NULL DEFAULT '', `UserCity` CHAR(4) NOT NULL DEFAULT '', `HomePhone` VARCHAR(32) NOT NULL DEFAULT '', `WorkPhone` VARCHAR(32) NOT NULL DEFAULT '', `OtherPhone` VARCHAR(32) NOT NULL DEFAULT '', `PersonalEmail` VARCHAR(64) NOT NULL DEFAULT '', `WorkEmail` VARCHAR(64) NOT NULL DEFAULT '', `OtherEmail` VARCHAR(64) NOT NULL DEFAULT '', `PrimaryEmail` SMALLINT(6) NOT NULL DEFAULT '0', `Company` VARCHAR(128) NOT NULL DEFAULT '', `CompanyWebsite` VARCHAR(128) NOT NULL DEFAULT '', `Occupation` VARCHAR(64) NOT NULL DEFAULT '', `JobTitle` VARCHAR(64) NOT NULL DEFAULT '', `Interest` VARCHAR(128) NOT NULL DEFAULT '', `Profile` VARCHAR(256) NOT NULL DEFAULT '', `Phrases` LONGTEXT NOT NULL, `LunarAnimal` SMALLINT(6) NOT NULL DEFAULT '0', `Horoscope` SMALLINT(6) NOT NULL DEFAULT '0', `BloodType` SMALLINT(6) NOT NULL DEFAULT '0', `ReservedFlag` INT(11) NOT NULL DEFAULT '0', `ReservedXml` LONGTEXT, PRIMARY KEY (`UserId`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 The lock table timeout error display when I use the following statement. UPDATE UP_UserEx SET Phrases ='' WHERE UserID = 1; I don't know why? Thanks for your patient read. -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn Comanpy: http://www.actionsky.com
Re: Sun bought by Oracle
Waiting for more interesting points. On Tue, Apr 21, 2009 at 1:42 PM, Manish Gupta manish.in@gmail.comwrote: http://www.sun.com/third-party/global/oracle/ anyone saw this ?? On Tue, Apr 21, 2009 at 2:54 AM, John Meyer john.l.me...@gmail.com wrote: Yep. In particular the anti-trust division of the DOJ. Kaushal Shriyan wrote: On Mon, Apr 20, 2009 at 11:14 PM, John Meyer john.l.me...@gmail.com mailto:john.l.me...@gmail.com wrote: I'm wondering what the DOJ is going to think of that deal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=kaushalshri...@gmail.com DOJ ? does it mean Department of Justice ? Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=manish.in@gmail.com -- Manish Gupta Follow me on twitter: twitter.com/nimbus3000 -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Small InnoDB table with many concurrent queries
Once your tables' engine are all of innodb, your configuration file has to be changed to fit innodb's feature, not myisam. On Tue, Apr 21, 2009 at 2:09 PM, living liquid | Christian Meisinger c.meisin...@livingliquid.com wrote: ah sorry... there are a few UPDATEs too but most is SELECTs... 60:40 i would say. Excuse me. But why concurrent queries request table locks? 2009/4/20 living liquid | Christian Meisinger c.meisin...@livingliquid.com Hi there. I've a small table with my daily banner hits. 1. version was with myisam but with a lot of concurrent queries (all SELECTs) i get too many table locks. so i changed it to an innodb table. works great most of the time. sometimes it seems to be too much, starting at about 500 concurrent queries i see a huge amount of processes taking about 3 minutes to finish 'sending data'. the SELECT queries use the correct index and data returned is small (2 integers). the table has only 4MB and about 35000 rows. it can't be the size of the table... mysql server is connected with a 1G switch. so i don't think it's network related. mysql server is a dual xeon 2,3GHz with 8G ram and SCSI disk RAID5. did i hit a innodb limit with this server configuration? or is my my.cnf bad? my.cnf --- key_buffer = 750M max_allowed_packet = 32M table_cache = 1 sort_buffer_size= 4M join_buffer_size= 4M read_buffer_size= 2M read_rnd_buffer_size= 4M myisam_sort_buffer_size = 128M query_cache_size= 750M query_cache_limit = 16M thread_cache= 32 thread_concurrency = 16 tmp_table_size = 700M max_heap_table_size = 700M net_buffer_length = 16K skip-external-locking innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 2G innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 8M innodb_lock_wait_timeout= 120 innodb_log_file_size= 256M innodb_log_files_in_group = 3 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Is Temporary table right approach
Thanks for your comments. I just gave him a sample value. On Fri, Apr 17, 2009 at 11:10 PM, Andrew Braithwaite andrew.braithwa...@lovefilm.com wrote: If you can not eliminate your temporary tables, you have to adjust the following parameters in my.cnf [mysqld] max_heap_table_size=1G tmp_table_size=1G You're making a lot of assumptions about this guy's setup. You shouldn't just tell him to apply these kinds of settings as you don't what effect they will have on his system. If he only has 512MB available for MySQL and he starts writing lots of 1GB temporary tables what's going to happen to the performance of his server? With advice like that you could grind his server/s to a halt and cause his site To die a miserable swappy death. Andrew -Original Message- From: Moon's Father [mailto:yueliangdao0...@gmail.com] Sent: 17 April 2009 06:36 To: Manoj Singh Cc: php...@lists.php.net; mysql@lists.mysql.com Subject: Re: Is Temporary table right approach If you can not eliminate your temporary tables, you have to adjust the following parameters in my.cnf [mysqld] max_heap_table_size=1G tmp_table_size=1G On Fri, Apr 17, 2009 at 12:57 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, Thanks for your valuable input. I have decided to use temporary table approach. Since I am using it for the first time and this has to be done in the production server. Do I need to consider some facts before using this such as setting some parameters in my.cnf etc or the MYSQL will handle all. Actually I want to know if any one has faces issues practically when implementing temporary tables. Waiting for your suggestion. Thanks, Manoj On Fri, Apr 17, 2009 at 8:21 AM, Moon's Father yueliangdao0...@gmail.comwrote: Use temporary table can be a good idea. But I think you performance would be boost so much if you can do something else to replace the temporary table. On Wed, Apr 15, 2009 at 2:03 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, I have a query which returns the large number of ids which i am using in other queries. I am doing this in PHP. Now the first query can return unlimited number of ids which might create problem in PHP. I want to store this ids in MYSQL through temporary table so that i can access that ids in other queries directly. Do you think the approach is right or there is any other good approach? Please suggest. Regards, Manoj -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn Comanpy: http://www.actionsky.com
Re: MySQL runs on 16-cores server
Thanks for you reply. I read the official document and found the variable called thread_concurrency could only affect on solaris. On Sat, Apr 11, 2009 at 5:04 AM, mos mo...@fastmail.fm wrote: At 06:00 AM 4/10/2009, you wrote: Hi. If the server has 16 cores, how to set parameters to make MySQL runs well. Any reply is appreciated. Using more cores with MySQL doesn't mean it will run faster. In fact, it could slow it down. Make sure you have done benchmarking with your current computer so you can compare the difference. InnoDb and MyISAM don't scale well with multi-cores I'm afraid. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: MySQL command line remote monitoring tool for 5.0
Innotop can satisfy you demand. On Thu, Apr 16, 2009 at 4:36 PM, Hitesh Shah hrs...@gmail.com wrote: Hello, I'd like to know if there is a command line tool I can run to collect vital health information for a remote mysql server (just like mysqltop) for 5.0 - I often see mysql swapping to disk and would like to know what causes that. Thanks Hitesh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Error msg: MySQL server has gone away
Can you tell me which version of mysql you use in you test? On Thu, Apr 16, 2009 at 12:43 AM, Pete Wilson p...@pwilson.net wrote: Hi folks -- New to MySQL. I'm developing C-language connector software and, while debugging, I often get the error message MySQL server has gone away if, say, I've stopped at a breakpoint and then issued a call that resembles: mysql_query(pmysql, insert into usrs(usr,email) values(\pete\, \p...@pwilson.net\); ); I can imagine this happening when the connector is running in real life. So: What is the correct and reasonable way for a running connector to deal with this error intelligently? Thanks! -- Pete Wilson http://www.pwilson.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Retrieving results of a stored function using MySql C API
Yeah, please show us the source code of yours. On Wed, Apr 15, 2009 at 4:10 PM, Venu Gopal neo.v...@gmail.com wrote: Hi guys, I am using stored procedures and stored functions for the first time. And currently stuck at a this point where. I am unable to retrieve results returned by stored function using MySql C API. Kindly let me know how to do so. In case you need details I'll share the source code. Cheers, Venu -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Is Temporary table right approach
Use temporary table can be a good idea. But I think you performance would be boost so much if you can do something else to replace the temporary table. On Wed, Apr 15, 2009 at 2:03 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, I have a query which returns the large number of ids which i am using in other queries. I am doing this in PHP. Now the first query can return unlimited number of ids which might create problem in PHP. I want to store this ids in MYSQL through temporary table so that i can access that ids in other queries directly. Do you think the approach is right or there is any other good approach? Please suggest. Regards, Manoj -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Is Temporary table right approach
If you can not eliminate your temporary tables, you have to adjust the following parameters in my.cnf [mysqld] max_heap_table_size=1G tmp_table_size=1G On Fri, Apr 17, 2009 at 12:57 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, Thanks for your valuable input. I have decided to use temporary table approach. Since I am using it for the first time and this has to be done in the production server. Do I need to consider some facts before using this such as setting some parameters in my.cnf etc or the MYSQL will handle all. Actually I want to know if any one has faces issues practically when implementing temporary tables. Waiting for your suggestion. Thanks, Manoj On Fri, Apr 17, 2009 at 8:21 AM, Moon's Father yueliangdao0...@gmail.comwrote: Use temporary table can be a good idea. But I think you performance would be boost so much if you can do something else to replace the temporary table. On Wed, Apr 15, 2009 at 2:03 PM, Manoj Singh manojsingh2...@gmail.comwrote: Hi All, I have a query which returns the large number of ids which i am using in other queries. I am doing this in PHP. Now the first query can return unlimited number of ids which might create problem in PHP. I want to store this ids in MYSQL through temporary table so that i can access that ids in other queries directly. Do you think the approach is right or there is any other good approach? Please suggest. Regards, Manoj -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: MySQL runs on 16-cores server
Thank you very much. Could you tell me what is IIRC? On Fri, Apr 10, 2009 at 8:24 PM, Uwe Kiewel m...@kiewel-online.ch wrote: Moon's Father wrote: Hi. If the server has 16 cores, how to set parameters to make MySQL runs well. IIRC is mysqld multi threaded - so if you have parallel queries, mysqld will spam multiple threads across multiple cores. HTH, Uwe -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: When will MySQL support array datatype?
Thanks for your fast reply. Then only temporary table can simulate array datatype. On Mon, Feb 16, 2009 at 3:12 AM, Claudio Nanni claudio.na...@gmail.comwrote: Complex datatypes are not compatible with the concept of relational databases, probably you want to refer to an Object-Oriented DBMS or Object-Relational DBMS. Cheers Claudio Nanni Moon's Father wrote: Hi. Who could tell me when the MySQL support array datatype? Any reply will be appreciated. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: How to execute prepare statement when the placeholder is a specific table name?
Thanks for your fast reply.
Do you know MySQL will support this feature in the future?
On Sun, Feb 15, 2009 at 12:59 AM, Baron Schwartz ba...@xaprb.com wrote:
Hello,
On Sat, Feb 14, 2009 at 11:15 AM, Moon's Father
yueliangdao0...@gmail.com wrote:
Here is my routine.
DELIMITER $$
CREATE
PROCEDURE `t_girl`.`sp_join2`()
BEGIN
set @a = 'a';
set @b = 'g';
set @stmt = concat('select * from ?,? where a.id = g.id');
prepare s1 from @stmt;
execute s1;
drop prepare s1;
END$$
DELIMITER ;
But it didn't work for me.
So what I want to know is how to table name when there're a placeholder
within sproc.
You can't use placeholders for identifiers, only for literal values.
So you will need to use CONCAT() to build the string with the
identifiers already in it, before you PREPARE.
--
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html
--
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
Re: WHY do I see this error when restoring my backup db : InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files.
This is because you didn't copy innodb ibdata and ib_log files togeter. Or you forgot to stop mysqld when you remove its ib_log files. On Sat, Feb 7, 2009 at 7:21 AM, my sql mysql.g...@gmail.com wrote: WHY do I see this error when restoring my backup db : InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. GOAL: Trying to restore mysql backup on different host using InnoDB backup that copes the backed up files to a files sever where I pulle them down to the new host I place all the MySQL datafiles and InnoDB log files in the same directory and all paths in the my.cnf files are pointing here upon the startup of the mysqld and the following crach recover that the Innodb do I get tons of this errors [see below] why? I do have the two InnoDB log files - so why does it complain that my DB 'may' be corrupt - I don't like this message. Event tried with seting innodb_force_recovery = 4 but I still get the error upon startup - why? It doesn't look like a clean startup to me 090206 14:56:34 InnoDB: Error: page 27060 log sequence number 4 1755884236 InnoDB: is in the future! Current system log sequence number 4 1682795020. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Is deleting the .FRM, .MYD and .MYI files the same as dropping table?
Yeah, you're right. On Fri, Jan 16, 2009 at 10:57 AM, Daevid Vincent dae...@daevid.com wrote: you misunderstand me. I have three servers (dev, test, prod) that all have maybe 3 databases EACH that have all these eventum* tables in them. don't ask. a simple trickle won't do. I'm writing a script to loop through them all. On Fri, 2009-01-16 at 01:57 +, John Daisley wrote: Well surely its a simple case of drop the tables on the master and let replication do the rest! John Daisley On Thu, 2009-01-15 at 17:44 -0800, Daevid Vincent wrote: I've been tasked with cleaning up a bunch of 'eventum' tables that got accidentally dumped into several databases and then replicated. I'm wondering if I can just go through with a simple command to blow these all away: find /var/lib/mysql/ -name eventum* Or is there some other magic that a DROP TABLE eventum* does? -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: On fighting with master-slave replication lag
Yeah. You should use mk-heartbeat, it's the best tool for this situation that I have seen before. On Wed, Dec 24, 2008 at 10:06 PM, Baron Schwartz ba...@xaprb.com wrote: On Wed, Dec 24, 2008 at 2:31 AM, Jake Maul jakem...@gmail.com wrote: Slightly more complicated (and also probably more accurate- the time reported by show slave status is known to be unreliable in some cases) would be a script that inserts a row into a table, then check the slave over and over till it arrives. Or even better, insert 2 values... a timestamp that *you* provide (in a shell script, something like $(date) would work) and a timestamp generated by MySQL assuming the times are syncronized on the master, slave, and the box you're inserting from, when the insert hits the slave it'll generate it's own timestamp, which you can then subtract *your* timestamp from. There's also a tool in maatkit which does replication tracking, although I've not yet used it. Judging by the other tools in that package though, it's probably pretty decent :). It is mk-heartbeat, and it does pretty much what you described, although it's been tweaked to be slightly more complex to suit various real-world scenarios. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: About combine ssl with mysql.
I have solved this problem. ^_^ On Fri, Dec 12, 2008 at 10:54 AM, Moon's Father yueliangdao0...@gmail.comwrote: Hi. I want to know how to connect mysqld with ssl encryption from windows or linux system. Any reply will be appreciated. Here is my status. mysql status -- /usr/local/mysql-ytt/bin/mysql Ver 14.14 Distrib 5.1.30, for pc-linux-gnu (i686) using EditLine wrapper Connection id: 4 Current database: Current user: r...@localhost SSL:Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter:; Server version: 5.1.30-log Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:latin1 Db characterset:latin1 Client characterset:latin1 Conn. characterset:latin1 UNIX socket:/tmp/mysql3309.sock Uptime: 1 hour 2 min 4 sec Threads: 2 Questions: 15 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.4 -- mysql show variables like '%ssl%'; +---+---+ | Variable_name | Value | +---+---+ | have_openssl | YES | | have_ssl | YES | | ssl_ca| /home/david_yeung/ssl/openssl/cacert.pem | | ssl_capath| | | ssl_cert | /home/david_yeung/ssl/openssl/server-cert.pem | | ssl_cipher| | | ssl_key | /home/david_yeung/ssl/openssl/server-key.pem | +---+---+ 7 rows in set (0.00 sec) Here is my.cnf. [mysqld] # SSL options. ssl-ca=/home/david_yeung/ssl/openssl/cacert.pem ssl-cert=/home/david_yeung/ssl/openssl/server-cert.pem ssl-key=/home/david_yeung/ssl/openssl/server-key.pem [client] # SSL options. ssl-ca=/home/david_yeung/ssl/openssl/cacert.pem ssl-cert=/home/david_yeung/ssl/openssl/client-cert.pem ssl-key=/home/david_yeung/ssl/openssl/client-key.pem Here is my user. mysql show grants for root@'%'; +--+ | Grants for r...@% | +--+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE SSL | +--+ 1 row in set (0.00 sec) -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Could somebody tell me how to use the memcache engine inside mysql?
Thanks for your reply. I have installed memcached_function before this post. But when I use any function of this api, mysql client is dead. On Thu, Dec 4, 2008 at 12:15 AM, Rolando Edwards [EMAIL PROTECTED]wrote: Read The Attached PDF and Check Out These URLs http://www.danga.com/memcached/apis.bml http://tangent.org/index.pl?node_id=506 -Original Message- From: Moon's Father [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 03, 2008 1:22 AM To: MySql Subject: Could somebody tell me how to use the memcache engine inside mysql? Although I set up memcache engine successfully on my computer, I didn't know how to use it? I can not find where the document is. Here is my result of engines. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.1.30 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show engines; ++-++--+-++ | Engine | Support | Comment| Transactions | XA | Savepoints | ++-++--+-++ | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES| | MEMCACHE | YES | Simple Interface for working with memcache as a storage engine | NO | NO | NO | | CSV| YES | CSV storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | ARCHIVE| YES | Archive storage engine | NO | NO | NO | ++-++--+-++ 8 rows in set (0.00 sec) mysql Aborted Any reply is appreciated. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Could somebody tell me how to use the memcache engine inside mysql?
Although I set up memcache engine successfully on my computer, I didn't know how to use it? I can not find where the document is. Here is my result of engines. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.1.30 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show engines; ++-++--+-++ | Engine | Support | Comment| Transactions | XA | Savepoints | ++-++--+-++ | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES| | MEMCACHE | YES | Simple Interface for working with memcache as a storage engine | NO | NO | NO | | CSV| YES | CSV storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | ARCHIVE| YES | Archive storage engine | NO | NO | NO | ++-++--+-++ 8 rows in set (0.00 sec) mysql Aborted Any reply is appreciated. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: MySQL Cluster
Hi. Here are some of my tests on Centos 5.0. http://blog.chinaunix.net/u/29134/article_71956.html On Fri, Nov 21, 2008 at 3:49 AM, Ronan Lucio [EMAIL PROTECTED] wrote: Hi, Does anybody has a tip to install a MySQL Cluster in a Linux CentOS-5? Is it better from source or can it be from yum? I do prefer yum because it's easier for upgrades, but I don't know if the available package was compiled for that. Thank you, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Error on MySQL-5.0
You may execute mysql_fix_privileges_table script to upgrade all of your mysqld. On Thu, Nov 20, 2008 at 7:54 PM, Ronan Lucio [EMAIL PROTECTED] wrote: Hi, I installed MySQL-5.0.67_1. When I execute CHECK TABLE information_schema.COLUMNS FOR UPGRADE I get the message: ++---+--++ | Table | Op| Msg_type | Msg_text | ++---+--++ | information_schema.COLUMNS | check | error| Table upgrade required. Please do REPAIR TABLE `/var/tmp/#sql_43b6_0` to fix it! | ++---+--++ The same occurs for tables ROUTINES, TRIGGERS and VIEWS. If I execute REPAIR TABLE COLUMNS I got: ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema' So I GRANT ALL ON information_schema.* TO 'root'@'localhost'; and got the same error: ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema' So I backed to command shell and mysqlcheck -u root -p --repair information_schema. It gives me no error, but the problem persists: When I execute CHECK TABLE information_schema.COLUMNS FOR UPGRADE I get the message: ++---+--++ | Table | Op| Msg_type | Msg_text | ++---+--++ | information_schema.COLUMNS | check | error| Table upgrade required. Please do REPAIR TABLE `/var/tmp/#sql_43b6_0` to fix it! | ++---+--++ Any help would be appreciate. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Virtualizing MySQL
I had done many instances on one machine before, the most important thing is about the my.cnf. And there are many individual my.cnf, which belonged to their own instance. Since your total memory is 32GB, you can assign them properly. On Fri, Nov 21, 2008 at 3:40 AM, Claudio Nanni [EMAIL PROTECTED]wrote: quote we are going to be setting up a 3 to 4 node MySQL replication cluster (1 master-rw and 2 slaves-ro)...each having 16 to 32 GB of RAM. quote If it is still true what you wrote you need different installations. Of course master and slave on the same host has the only use of an online backup solution, better if using different storage for data partitions, anyway adding not much to high availability. But if your only concern is to test a Master/Slave configuration I would go for multiple instances on same host. If you need a complete description on how to do it contact me. Sorry if I repeat myself, but for reliable test you should have the same architecture for both prod and preprod, Claudio Nanni Shain Miley wrote: Ok...based on the responses that I received so far...it seems like maybe I should be leaning toward a non virtualized solution. What I am wondering now is... 1)would it be better to have one MySQL instance running and have the developers each have their own DB inside that one instance? or 2) would it be better to have each developer have their own MySQL instance on the same machine? or 3) some combination of the above...maybe have the developers split between 2 or 3 MySQL instances on the same machine... Any thoughts? Thanks again, Shain Simon J Mudd wrote: [EMAIL PROTECTED] (Shain Miley) writes: I am looking into the idea of setting up 10 - 15 virtualized instances of MySQL. The reason for this is as follows...we are going to be setting up a 3 to 4 node MySQL replication cluster (1 master-rw and 2 slaves-ro)...each having 16 to 32 GB of RAM. In order for our development team to do their work...they must have access to some Mysql resources that are close to the production environment. I am not currently in a position to provide each developer two MySQL servers (one master and one slave with 16 to 32 GB of RAM) for testing...or obvious reasons...mainly cost ;-) So I have been thinking about how best to provide such resources, at this point I am thinking that I can use OpenVZ to help me out a bit. I was wondering if anyone had any thoughts on this issue...should I just run 10 instances of MySQL on the same server...are there other options? I am concerned with trying to ensure that the metrics, resources, workloads, etc from these development servers has some sort of relevance to our production environment...otherwise we are testing apples and oranges...which the dev team will clearly point out...and in a way I know we are...but I would like to minimize the effects My only concern would be that if you have busy mysql instances that they will interfere with each other. We used to have a couple of busy mysqld processes running on the same Linux server only to find that the performance characteristics were worse than 1/2 of the performance of having each instance on a separate server. Both mysqld instances were busy and so fought each other for I/O and for CPU often at the same time. If this might be an issue for your virtual servers may not be an ideal solution as most of the free virtualisation options don't control sufficiently the hardware resources distributed to each virtual machine. YMMV. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: How to determine if temporary table exists
Try drop table if exists Tablex; On Fri, Nov 21, 2008 at 9:53 AM, mos [EMAIL PROTECTED] wrote: How can I determine if a temporary table exists? Normally I use something like: create temporary table Tablex like Table1; show tables like Tablex; but the Show Tables never displays any rows for a temporary table even though the temporary Tablex exists. (All in same thread). So is there a better way to determine if a temporary table exists? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqldump: Got error: 1030
What is your storage engine used? On Fri, Nov 21, 2008 at 8:59 AM, Marten Lehmann [EMAIL PROTECTED] wrote: Hello, what do these errors mean: mysqldump: Got error: 1030: Got error 1 from storage engine when using LOCK TABLES mysqldump: Couldn't execute 'show create table `Antrag`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Autor`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Bild`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Galerie`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Link`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Seite`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Termin`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Veroeffentlichung`': Got error 1 from storage engine (1030) Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqldump: Got error: 1030
What is your storage engine used? On Fri, Nov 21, 2008 at 8:59 AM, Marten Lehmann [EMAIL PROTECTED] wrote: Hello, what do these errors mean: mysqldump: Got error: 1030: Got error 1 from storage engine when using LOCK TABLES mysqldump: Couldn't execute 'show create table `Antrag`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Autor`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Bild`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Galerie`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Link`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Seite`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Termin`': Got error 1 from storage engine (1030) mysqldump: Couldn't execute 'show create table `Veroeffentlichung`': Got error 1 from storage engine (1030) Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: MySQL Cluster
Thanks for advice. There're no environment for me to test the cluster again right now. Hope the chance chooses me, then the english version will be done. :) On Fri, Nov 21, 2008 at 10:48 AM, steve grosz [EMAIL PROTECTED]wrote: Hello Moon's Father, That would be great..if it was in english ;) Hi. Here are some of my tests on Centos 5.0. http://blog.chinaunix.net/u/29134/article_71956.html On Fri, Nov 21, 2008 at 3:49 AM, Ronan Lucio [EMAIL PROTECTED] wrote: Hi, Does anybody has a tip to install a MySQL Cluster in a Linux CentOS-5? Is it better from source or can it be from yum? I do prefer yum because it's easier for upgrades, but I don't know if the available package was compiled for that. Thank you, Ronan -- 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] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: A tool
Yeah, it's really a nice tool for me. Thanks. On Tue, Nov 18, 2008 at 5:44 AM, Saravanan [EMAIL PROTECTED] wrote: really nice tool. Thanks, Saravanan --- On Mon, 11/17/08, lakshmi pathi [EMAIL PROTECTED] wrote: From: lakshmi pathi [EMAIL PROTECTED] Subject: A tool To: mysql@lists.mysql.com Date: Monday, November 17, 2008, 1:25 PM Hi all, If you are using mysql in linux with ext3 file system,then this might help. I have written a undelete tool. [Short functionality is ,once installed files can be recovered using the tool but files deleted before installation can't be recovered. It acts as a fail-safe.] I tried it with mysql,for screenshots check here, http://www.giis.co.in/giis_mysql_screenshots.html You can download it from http://sourceforge.net/projects/giis or from here www.giis.co.in I have recovered only tables and not yet started with complete databases, based on your comments i hope to decided on futher enhancements. (If you have already such functionality available ,please let me know - as there is no point in reinventing the wheel :) ) (ps:I posted the similar message few days back on the forum ..but didn't received any comments/info..so i'm here :) ) Cheers, Lakshmipathi.G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Overhead Issue
Maybe your tables were not properly designed. On Tue, Nov 18, 2008 at 10:35 AM, sangprabv [EMAIL PROTECTED] wrote: Thanks for the reply, does this overhead reduce performance? And is there any tips to avoid this overhead? TIA. WM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: About innodb's max_rows attribute.
I'm sorry to mistake the wrong storage engine. On Wed, Nov 12, 2008 at 3:52 PM, Moon's Father [EMAIL PROTECTED]wrote: Hi. I got an error when I use alter statement to modify an innodb's table structure. The error no is 1114 and the detail information is ERROR 1114 (HY000): The table '#sql-4c0_1' is full. Here is my table's structure. mysql show create table t1; +---+--- ---+ | Table | Create Table | +---+--- ---+ | t1| CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `message` char(20) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=MyISAM AUTO_INCREMENT=9437185 DEFAULT CHARSET=latin1 MAX_ROWS=100 | +---+--- ---+ 1 row in set (0.02 sec) I just want to know how to affect a innodb table's behavior when I added a max_rows option to it. I read the document but she said this option didn't matter. mysql alter table t1 modify message varchar(20) not null; ERROR 1114 (HY000): The table '#sql-4c0_1' is full mysql alter table t1 max_rows = 10; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql alter table t1 modify message varchar(20) not null; Query OK, 4437184 rows affected (1 min 32.93 sec) Records: 4437184 Duplicates: 0 Warnings: 0 -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Incorrect value for default-time-zone
Hi. I got an error message when I adjust the system variable named default-time-zone.I don't how to adjust this variable. Hope to get help here.Thanks. Here is my error log. 081113 15:24:43 InnoDB: Started; log sequence number 0 423199200 081113 15:24:43 [Note] Recovering after a crash using mysql-bin 081113 15:24:43 [Note] Starting crash recovery... 081113 15:24:43 [Note] Crash recovery finished. 081113 15:24:43 [ERROR] Fatal error: Illegal or unknown default time zone 'GMT +08:00' 081113 15:26:57 InnoDB: Started; log sequence number 0 423199200 081113 15:26:57 [Note] Recovering after a crash using mysql-bin 081113 15:26:57 [Note] Starting crash recovery... 081113 15:26:57 [Note] Crash recovery finished. 081113 15:26:58 [ERROR] Fatal error: Illegal or unknown default time zone '08:00' 081113 15:27:16 InnoDB: Started; log sequence number 0 423199200 081113 15:27:16 [Note] Recovering after a crash using mysql-bin 081113 15:27:16 [Note] Starting crash recovery... 081113 15:27:16 [Note] Crash recovery finished. 081113 15:27:16 [ERROR] Fatal error: Illegal or unknown default time zone 'GMT' -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
About innodb's max_rows attribute.
Hi. I got an error when I use alter statement to modify an innodb's table structure. The error no is 1114 and the detail information is ERROR 1114 (HY000): The table '#sql-4c0_1' is full. Here is my table's structure. mysql show create table t1; +---+--- ---+ | Table | Create Table | +---+--- ---+ | t1| CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `message` char(20) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=MyISAM AUTO_INCREMENT=9437185 DEFAULT CHARSET=latin1 MAX_ROWS=100 | +---+--- ---+ 1 row in set (0.02 sec) I just want to know how to affect a innodb table's behavior when I added a max_rows option to it. I read the document but she said this option didn't matter. mysql alter table t1 modify message varchar(20) not null; ERROR 1114 (HY000): The table '#sql-4c0_1' is full mysql alter table t1 max_rows = 10; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql alter table t1 modify message varchar(20) not null; Query OK, 4437184 rows affected (1 min 32.93 sec) Records: 4437184 Duplicates: 0 Warnings: 0 -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Where should I download mysql 4.0.12?
Thanks for alll of you reply. I know this version is too old and it has many bugs and security issues, but this is our current using edition .I have no choice for it. Thanks for you again! On Fri, Nov 7, 2008 at 4:31 AM, Eric Bergen [EMAIL PROTECTED] wrote: If you still want it you can download it from the Proven Scaling mirror. http://mirror.provenscaling.com/mysql/community/source/4.0/ -Eric On Thu, Nov 6, 2008 at 1:58 AM, Joerg Bruehe [EMAIL PROTECTED] wrote: Hi! Moon's Father wrote: Hi. Where can I download a mysql 4.0.12? I found nowhere to download it. You will not find it anywhere at MySQL, it is out of support since more than 2 years (September 2006). For an explanation, read the text here: http://downloads.mysql.com/archives.php?p=mysql-4.0 As regards 4.0.12 in specific: That version is totally obsolete, the last published version of the 4.0 series was 4.0.27. Anybody installing 4.0.12 now would miss many security fixes and so run great risks. (This is valid for all unsupported versions, of course - the older a version is, the more security fixes will be missing.) Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- high performance mysql consulting. http://provenscaling.com -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: How to understand this phrase on the document.
Thanks for your patient reply. Now I believe that the memory leak problem was generated by the program. On Sat, Nov 1, 2008 at 5:31 PM, Ian Christian [EMAIL PROTECTED] wrote: 2008/10/31 Moon's Father [EMAIL PROTECTED] *A prepared statement is also global to the connection. If you create a prepared statement within a stored routine, it is not deallocated when the stored routine ends. * Then I don't know how to deallocate the memory used by prepare statement within procedure? The memory will be de-allocated when you disconnect that session, there is no need to worry about it's memory usage. This statement simply means that if you call a method twice, which uses the same prepared statement, it will on need to be re-prepared, and hence you will see an improvement in speed. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: I don' t know difference between myisam_sort_buffer_size and myisam_max_sort_file_size
Thanks for your patient replay. This means the myisam_max_sort_file_size is the limitation of myisam_sort_buffer_size. And they are both implemented to improve the DDL statements. On Fri, Oct 31, 2008 at 11:45 PM, Jake Maul [EMAIL PROTECTED] wrote: From dev.mysql.com: myisam_max_sort_file_size : The maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes. The default value is 2GB. If MyISAM index files exceed this size and disk space is available, increasing the value may help performance. myisam_sort_buffer_size: The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE. The maximum allowable setting for myisam_sort_buffer_size is 4GB. (Default is 8MB) Simply: one's a buffer, one's a limit on the temp file size used for sorting. Which you might need to increase depends entirely on which limitation you're running into... if the current index files for the relevant tables on disk exceed 2GB, you might want to increase myisam_max_sort_file_size. Otherwise, myisam_sort_buffer_size might be better. Jake On Fri, Oct 31, 2008 at 12:20 AM, Moon's Father [EMAIL PROTECTED] wrote: Because alter table and repair table are both affected by myisam_sort_buffer_size or myisam_max_sort_file_size, I'm in confusion then. Anybody can tell me which to be adjusted when I want to improve the performance of my index operation. Thanks. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: auto_increment problem
alter table tablename modify id int not null auto_increment primary key; On Sat, Oct 25, 2008 at 2:48 AM, Paul [EMAIL PROTECTED] wrote: Anybody know if there's a way to change a primary key field that is not auto-incremented, turning on auto-increment but preserving the values that are currently in it? TIA, Paul W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysql dump problems, no data dumped
Hi,andy. Can you show me the details about the options of mysqldump to be used ? On Fri, Oct 31, 2008 at 1:25 AM, Andy Smith [EMAIL PROTECTED] wrote: Hi, Im having an issue using mysqldump to dump a DB from comercial app which includes mysql 4.0.18-pro. It doesnt however include mysql dump for online backups so Im using the one installed by default in my linux dist which as you can see below is version 10.11. My problem is that the dump is exiting with exist status 0 but Im not getting any of the data dumped. The WHOLE dump is shown in text below: -- MySQL dump 10.11 -- -- Host: localhostDatabase: OpManagerDB -- -- -- Server version 4.0.18-pro /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*!40103 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40111 SET [EMAIL PROTECTED] */; -- Dump completed on 2008-10-27 13:50:53 Can anyone help me? I need to work out why no data is written, thanks Andy. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: moving from 3.23.58 to 5.0.45
I think Claudio Nanni 's suggestion is the best! On Thu, Oct 30, 2008 at 8:29 PM, Claudio Nanni [EMAIL PROTECTED]wrote: Hi Mark, from my experience I can tell you that you can easily migrate. A quick and dirty way is to use the two(or more) different MySQL installations on same server (or even different ones) and different ports of course. You dump the whole DB from the 3.23.58 and import it on the 5.0.45. If you have problems try with 4.1 or 4.0 as a midway step. At this very moment I can't tell you if you will have problems with the 'big' jump, but you should test with a smaller set of data(if your DB is huge) and after that decide if do the direct jump or use a step in the middle. Here's the idea: [3.23.58]---[5.0.45] OR [3.23.58]---[4.x]---[5.0.45] OR [3.23.58]---[4.0.x]---[4.1.x]---[5.0.45] Commands to use: [3.23.58]# mysqldump --all-databases dump.sql will do the job and [5.0.45] mysql source dump.sql will complete the opera! remember that after that also the grant tables will be replaced from the original DB(3.23.58) hope it helps Claudio 2008/10/30 Obantec Support [EMAIL PROTECTED] Hi are there any doc's on how to migrate DB in 3.23.58 format to 5.0.45 moving from a Fedora Core3 to Centos5.2 server. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: lost connection to mysql server during query errors
In my experiences, there're three reasons below. 1. Your network is not stable. 2. Your mysqld's parameter called max_allowed_packet is adjusted too small, trying to increase it. 3. Your mysqld's parameter called connect_timeout is adjusted too small, trying to increase it. On Thu, Oct 30, 2008 at 1:05 PM, mos [EMAIL PROTECTED] wrote: At 10:21 PM 10/29/2008, you wrote: I've never had a lot of luck tracking down this sort of problem. One thing I've found to be a good first step is to add each server involved to the other server's /etc/hosts file (and restart MySQL so it notices). Don't have much more to offer other than the usual suspects: recent versions, persistent vs. non-persistent connections, etc. A long shot would be to make sure your always talking to the same database server- if you're doing, say, DNS round-robin or load balancing or something, maybe you're getting shunted to a different db server and it's killing the connection... don't know what your setup is. Another long shot in a multi-db-server config would be to make sure they all have different server ID's. Good luck... hopefully someone else has better advice :) Jake Just a guess, but maybe it's your network card? I'm using MySQL 5.01 with MyISAM tables and my application will occasionally hang for hours in the midst of executing a simple 1 table Select statement. I usually end up killing the program. There are no processes running on the MySQL server. I think the problem was the number of connections the program created. Although there were only at most 10 simultaneous connections, my program when the query finished executing, it threw the connection away and recreated a new one for each query, and MySQL reported there were some 10k connections made to the server. I ended up using connection pooling and now the number of connections reaches a high of around 10 and I haven't had the problem since. Mike On Wed, Oct 29, 2008 at 2:47 AM, Waynn Lue [EMAIL PROTECTED] wrote: We've started seeing mysql errors in the logs, and when i look at the output of mysql_error() (in php), i get lost connection to mysql server during query. Here's an example stack trace: 'Can't connect to name database [Lost connection to MySQL server during query]' Similarly, we're seeing stack traces here as well: 'Can't connect to name database []' I usually only see this mesasge when I don't use a connection for awhile and it timeouts, but in this case, the connection is only opened for the duration of a script, which can't be running for more than a second. The mysql error logs don't show anything, and wait_timeout is set to 28800. At first, I thought it was because I was calling mysql_select_db too much, so I ended up using two mysql connections per page load, but that didn't seem to change anything. How can we prevent this error from happening, what else can I do to diagnose this further? Google brings up some more discussions about it, but nothing seems related to this, like packetsize. This is happening when we select two ids from a database. And SHOW PROCESSLIST shows that the number of connections aren't even coming close to max connections. Thanks for any advice, Waynn -- 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] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Deployment of Database in a DEB package.
Thanks. On Tue, Oct 28, 2008 at 3:17 PM, Micah Stevens [EMAIL PROTECTED]wrote: It's a package for Debian based systems, which include Ubuntu and a few others.. It's like an RPM for Fedora/Redhat linux. -Micah On 10/27/2008 06:39 PM, Moon's Father wrote: I'm sorry that if I can ask a question.What is DEB? On Fri, Oct 3, 2008 at 9:49 PM, US Data Export[EMAIL PROTECTED] [EMAIL PROTECTED]wrote: -Original Message- From: Ellison, David [mailto:[EMAIL PROTECTED] [EMAIL PROTECTED]] Sent: Friday, October 03, 2008 2:35 AM To: mysql@lists.mysql.com Subject: RE: Deployment of Database in a DEB package. That's true, you would need to consider that risk. Can a DEB package ask for information before deployment? If so you could ask for the username/password and run the mysql command using the provided info. Therefore not risking the problem of having a username/password within the files. Although I am making a big assumption on whether you can grab input and use that in scripts of course :) [JS] Bash, ksh, and other shells have a read command that can be used to take input from the console (or other sources). Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.comwww.giiexpress.comwww.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Any easier way to compare mysql schema of 50 databases?!
Just use command line tools called fc on windows or diff on linux if you are patient and perfer trouble. On Tue, Oct 7, 2008 at 10:07 PM, Duzenbury, Rich [EMAIL PROTECTED]wrote: I just used mysqldiff for a project, and was successful. -Original Message- From: Uma Bhat [mailto:[EMAIL PROTECTED] Sent: Monday, October 06, 2008 7:51 PM To: Andy Shellam; mysql@lists.mysql.com Subject: Re: Any easier way to compare mysql schema of 50 databases?! Thank you all, guys!! i shall try these and respond on which worked best for me. Regards, Uma On 10/6/08, Andy Shellam [EMAIL PROTECTED] wrote: Hi, I was also going to go down this route some time back, but then when I looked at it, it's pretty simple how it works and you can do the same thing yourself for free. Take a dump of both servers (mysqldump or via MySQL Administrator) of the databases in question, then use WinMerge (for free) to compare the dump files and (optionally) create a 3rd file which is a result of merging the 2 by choosing the changes you want to keep. I've used this method plenty of times to synchronise changes between servers, some that concern 000s of rows of data. Granted you cannot do this with PostgreSQL as those dumps tend to be in binary format, but it works well for MySQL backups. WinMerge: http://www.winmerge.org/ Andy D. Dante Lorenso wrote: Uma Bhat wrote: We are in progress of *optimizing* and designing the existing mysql database enviromnent on *linux*. And need help in comaparing schema of 50 databases from the same mysql instance. If you can afford to spend a few dollars to get the right tool, you want to get DB Comparer for MySQL from the folks at EMS: http://www.sqlmanager.net/en/products/mysql/dbcomparer This tool will compare the schemas of 2 MySQL Databases and allow you to selectively choose which changes to make in order to synch to the master or the target DB. I've been using the PostgreSQL version of this tool for many years and just recently started using their MySQL one. -- Dante -- D. Dante Lorenso [EMAIL PROTECTED] -- 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] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Getting more info from show full processlist
I also want to know the answer to this question.If it's for me.I'll scan all the source code manually.But it's too complicated. On Tue, Oct 7, 2008 at 1:02 AM, Olaf Stein [EMAIL PROTECTED] wrote: It specifies the query in the Info field. In your case the connection is sleeping, nothing is being executed at the moment, therefore info is NULL Olaf On 10/6/08 11:38 AM, MaBa.listas [EMAIL PROTECTED] wrote: Hello, reading the MySQL documentation I've run into the show full processlist\G command. In my case I've got an output similar to this: -- --- mysql show full processlist\G *** 1. row *** Id: 30127 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show full processlist *** 2. row *** Id: 30399 User: root Host: localhost db: this would be my DB Command: Sleep Time: 0 State: Info: NULL -- --- What I'd like to know is how to get more info about the query identified with 30399 (in this example). I mean, the exact query, like SELECT pr1, pr2 FROM someDB. Thanks Matias - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Changing the location of my.ini and the order option files are read
I advise that you should put your my.cnf in mysql installation location. On Mon, Oct 6, 2008 at 11:06 PM, Varuna Seneviratna [EMAIL PROTECTED] wrote: Armin I did what you said, now I understand what you say that there is no sence in what I wanted to do.But I gave these commands as follows But it did not work.What I want is to specify a configuration file which is placed in the WINDOWS directory.The commands I ran C:\NET STOP MySQL C:\mysqld-nt --remove C:\mysqld-nt --install MYSQL --defaults-file=\c:\WINDOWS\my.ini\ Service successfully installed. C:\net start mysql System error 2 has occurred. The system cannot find the file specified. Why is the file not able to be found, I have taken my.ini out of the MySQL installation directory and placed it in the WINDOWS directory. Are there other commands to start a service apart from NET commands,like using mysqladmin? Varuna On Mon, Oct 6, 2008 at 7:07 PM, Armin Schöffmann [EMAIL PROTECTED] wrote: Varuna, obviously it doesn't make much sense, to specify the my.ini location in my.ini itself. The --defaults-file option is used as a direct start-up parameter for mysql-server both, run as service or console-process. It simply tells the server-process where to look for the oneandonly my.ini. Any fullpath given here, overrides the default search-path during server-startup. I assume your server-process has been already started with a --defaults-file -option pointing to a my.ini in the mysql-application-directory, therefor it failed after deleting the file at this location. You may change the direct service-startup options by invoking mysqld-nt with options --remove and --install: With the install option you can specify the complete command-line including additional options which will be used when the server starts as a service. e.g. net stop mysql mysqld-nt --remove mysqld-nt --install MYSQL --defaults-file=\c:\program files(x86)\mysql\my.ini\ net start mysql Regards, Armin. 2008/10/6 Varuna Seneviratna [EMAIL PROTECTED]: I added a line to the [mysqld) group As --defaults-file=C:\WINDOWS\my.ini and then I restarted the service.Everything was fine.Then I took out the my.ini file in the MySQL instalation directory the result was an error message AS Could Not start the MySQL Service on Local Computer Error 1067 The preocess terminated unexpectedly According to the manual MySQL server reads option files in the order Default options are read from the following files in the given order: C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 5.0\my.ini C:\Program Files\MySQL\MySQL Server 5.0\my.cnf To see the order I ran the command mysqld-nt --verbose --help If the optin files read order is as above why can,t get the service started Varuna -- Aegaeon technologies GmbH phone: +49.941.8107344 fax: +49.941.8107356 Legal disclaimer: http://aegaeon.de/disclaimer/email_all_int.txt -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Deployment of Database in a DEB package.
I'm sorry that if I can ask a question.What is DEB? On Fri, Oct 3, 2008 at 9:49 PM, US Data Export [EMAIL PROTECTED]wrote: -Original Message- From: Ellison, David [mailto:[EMAIL PROTECTED] Sent: Friday, October 03, 2008 2:35 AM To: mysql@lists.mysql.com Subject: RE: Deployment of Database in a DEB package. That's true, you would need to consider that risk. Can a DEB package ask for information before deployment? If so you could ask for the username/password and run the mysql command using the provided info. Therefore not risking the problem of having a username/password within the files. Although I am making a big assumption on whether you can grab input and use that in scripts of course :) [JS] Bash, ksh, and other shells have a read command that can be used to take input from the console (or other sources). Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: How could i check the following values in MySQL Server 5.0
Setting is difficult but checking is simple. On Thu, Oct 9, 2008 at 9:26 PM, Amit Sharma [EMAIL PROTECTED] wrote: Hi Sudhir, #show table status command will give you most of what all is required. Have a look at the results post any further query if you have. Regards, Amit Sharma On Thu, Oct 9, 2008 at 6:44 PM, Sudhir Menon [EMAIL PROTECTED] wrote: Hi all, I would like to check or set the following values in MySQL Server . How can that be done ? 1. MySQL Database Free Space for any database. 2. Maximum Database size allowed. 3. Calculate Index Size. 4. Maximum table size 5. Free table space 6. Free Index space 7. Calculate Table size Thanks Regards Sudhir -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: MYSQL DB BACKUP
There are some of backup scripts written by me.You can find it at: http://blog.chinaunix.net/u/29134/article_71953.html On Wed, Oct 22, 2008 at 4:52 PM, Mad Unix [EMAIL PROTECTED] wrote: Any one tried the script from HowToForge http://www.howtoforge.com/shell-script-to-back-up-all-mysql-databases-each-table-in-an-individual-file-and-upload-to-remote-ftp #!/bin/sh # System + MySQL backup script # Copyright (c) 2008 Marchost # This script is licensed under GNU GPL version 2.0 or above # - # ##TO BE MODIFIED# ### System Setup ### BACKUP=YOUR_LOCAL_BACKUP_DIR ### MySQL Setup ### MUSER=MYSQL_USER MPASS=MYSQL_USER_PASSWORD MHOST=localhost ### FTP server Setup ### FTPD=YOUR_FTP_BACKUP_DIR FTPU=YOUR_FTP_USER FTPP=YOUR_FTP_USER_PASSWORD FTPS=YOUR_FTP_SERVER_ADDRESS ##DO NOT MAKE MODIFICATION BELOW# # ### Binaries ### TAR=$(which tar) GZIP=$(which gzip) FTP=$(which ftp) MYSQL=$(which mysql) MYSQLDUMP=$(which mysqldump) ### Today + hour in 24h format ### NOW=$(date +%d%H) ### Create hourly dir ### mkdir $BACKUP/$NOW ### Get all databases name ### DBS=$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases') for db in $DBS do ### Create dir for each databases, backup tables in individual files ### mkdir $BACKUP/$NOW/$db for i in `echo show tables | $MYSQL -u $MUSER -h $MHOST -p$MPASS $db|grep -v Tables_in_`; do FILE=$BACKUP/$NOW/$db/$i.sql.gz echo $i; $MYSQLDUMP --add-drop-table --allow-keywords -q -c -u $MUSER -h $MHOST -p$MPASS $db $i | $GZIP -9 $FILE done done ### Compress all tables in one nice file to upload ### ARCHIVE=$BACKUP/$NOW.tar.gz ARCHIVED=$BACKUP/$NOW $TAR -cvf $ARCHIVE $ARCHIVED ### Dump backup using FTP ### cd $BACKUP DUMPFILE=$NOW.tar.gz $FTP -n $FTPS END_SCRIPT quote USER $FTPU quote PASS $FTPP cd $FTPD mput $DUMPFILE quit END_SCRIPT ### Delete the backup dir and keep archive ### rm -rf $ARCHIVED On Wed, Sep 17, 2008 at 10:18 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Thanks a lot. I am writing script which will take backup and copy it to another box. On Wed, Sep 17, 2008 at 11:14 AM, Ananda Kumar [EMAIL PROTECTED] wrote: Can u mount that file system on the slave db and take the backup so that u can avoid Network latency. regards anandkl On 9/16/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Yes On Tue, Sep 16, 2008 at 6:39 PM, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Krishna, When u say remote server, do u mean the file system storing the backup is on a different machine. regards anandkl On 9/16/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi, Currently, i am taking production server backup on hourly basis on the slave server. Is it feasible to take 15G backup on remote server on hourly basis. It takes 10 minutes on slave server. How much time it will take on remote server. Thanks, -- Krishna Chandra Prajapati -- Krishna Chandra Prajapati -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Tables are too wide
Just use another term will solve your problem.Or you can use pager more in mysql command line client. On Sun, Sep 28, 2008 at 12:44 AM, Dan Nelson [EMAIL PROTECTED]wrote: In the last episode (Sep 27), Alex Katebi said: Hi, When selecting (example: select * from mysql.db ) some of the mysql database tables the result set does not fit in a standard xterm window. Is there any terminal display program in Linux that has a horizontal scroll bar that I can use? Or what can I do to split the table output to fit on my screen? You can pipe results though less (which can display long lines unwrapped with -S) by running pager before sending your query. nopager turnes it off. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Finding gaps
Create an extra trigger on that table with delete event.Then the deleted item will be recorded in the database. On Fri, Oct 10, 2008 at 11:34 PM, Jerry Schwartz [EMAIL PROTECTED] wrote: Thanks. Although I've been around SQL for quite a while, I've never really gotten the hang of self-joins. From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 8:22 PM To: US Data Export; mysql@lists.mysql.com Subject: Re: Finding gaps Jerry, Here is a workaround for 4.1.22: SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING `Missing From` MIN(b.id); +--+--+ | Missing From | To | +--+--+ |3 |3 | |5 | 17 | +--+--+ PB US Data Export wrote: Well, 5.x accepted the query. It's been running for awhile, now, so I'll find out later if it did what I need. -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 5:25 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: Re: Finding gaps I must be missing something obvious; or does this not work in 4.1.22? Looks like a 4.1.22 bug. PB Jerry Schwartz wrote: I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id MIN(b.id) - 1; Looks like exactly what I want. However, when I try it (prod is my tbl, prod_num is my id) I get mysql select a.prod_num + 1 AS `Missing From`, - MIN(b.prod_num - 1) AS `To` - from prod as a, prod as b - where a.prod_num b.prod_num - group by a.prod_num - having a.prod_num min(b.prod_num) -1 ; ERROR 1054 (42S22): Unknown column 'a.prod_num' in 'having clause' I must be missing something obvious; or does this not work in 4.1.22? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 5:26 PM To: Stut; mysql@lists.mysql.com Subject: Re: Finding gaps Is there any elegant way of finding the gaps? You'll find some ideas under (and near) Find missing numbers in a sequence at http://www.artfulsoftware.com/infotree/queries.php. PB - Stut wrote: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database: 270.6.21/1677 - Release Date: 9/17/2008 5:07 PM -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM _ No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Finding gaps
Or you can create a temporary table including a auto_increment attribute and fill it with continuous numbers. Then simply use it to left join the original table. On Sat, Oct 25, 2008 at 2:57 PM, Moon's Father [EMAIL PROTECTED]wrote: Create an extra trigger on that table with delete event.Then the deleted item will be recorded in the database. On Fri, Oct 10, 2008 at 11:34 PM, Jerry Schwartz [EMAIL PROTECTED] wrote: Thanks. Although I've been around SQL for quite a while, I've never really gotten the hang of self-joins. From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 8:22 PM To: US Data Export; mysql@lists.mysql.com Subject: Re: Finding gaps Jerry, Here is a workaround for 4.1.22: SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING `Missing From` MIN(b.id); +--+--+ | Missing From | To | +--+--+ |3 |3 | |5 | 17 | +--+--+ PB US Data Export wrote: Well, 5.x accepted the query. It's been running for awhile, now, so I'll find out later if it did what I need. -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2008 5:25 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: Re: Finding gaps I must be missing something obvious; or does this not work in 4.1.22? Looks like a 4.1.22 bug. PB Jerry Schwartz wrote: I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id b.id GROUP BY a.id HAVING a.id MIN(b.id) - 1; Looks like exactly what I want. However, when I try it (prod is my tbl, prod_num is my id) I get mysql select a.prod_num + 1 AS `Missing From`, - MIN(b.prod_num - 1) AS `To` - from prod as a, prod as b - where a.prod_num b.prod_num - group by a.prod_num - having a.prod_num min(b.prod_num) -1 ; ERROR 1054 (42S22): Unknown column 'a.prod_num' in 'having clause' I must be missing something obvious; or does this not work in 4.1.22? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 17, 2008 5:26 PM To: Stut; mysql@lists.mysql.com Subject: Re: Finding gaps Is there any elegant way of finding the gaps? You'll find some ideas under (and near) Find missing numbers in a sequence at http://www.artfulsoftware.com/infotree/queries.php. PB - Stut wrote: On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: I have records that should be sequentially (not auto-increment) numbered, but there are gaps. Is there any elegant way of finding the gaps? Why do they need to be sequential? When this requirement comes up it's usually for illogical reasons. -Stut -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database: 270.6.21/1677 - Release Date: 9/17/2008 5:07 PM -- -- No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM _ No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 6:40 PM -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Aborted connects
This is because either somebody is attacking your mysqld or your variable named connect_timeout is very lower. On Wed, Oct 22, 2008 at 9:55 PM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi list, Aborted clients and connects are increasing very fast. Aborted_clients 4934 Aborted_connects 5034 connect_timeout 10 wait_timeout 28800 Please tell me how to fix this problem. -- Krishna Chandra Prajapati MySQL DBA, Mob: 9912924044 Email-id: [EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: super-smack on mysql 5.0 solaris 10 x86_64
Maybe you should set your old_passwords = on. On Fri, Oct 17, 2008 at 4:36 PM, Sudhir Menon [EMAIL PROTECTED] wrote: Hi Ujang I could reproduce the same error mentioned by you in the actual post even after having all of the packages for MySQL. Figuring out the what could be the problem . Thanks Regards Sudhir Menon -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: MyQuery 2.3 Beta available for download
It's very nice! But it's too simple. On Mon, Oct 6, 2008 at 12:26 AM, Anders Karlsson [EMAIL PROTECTED] wrote: Sorry for crossposting, but I think this is relevant both th general MySQL and specifically to Win32 users. MyQuery 2.3 has a lot of new features, a few bugfixes and some other niceties: * Output and sparse_output commands - The commands will output selected data to a file. The latter is a way to output ONLY what you select, no column headers, no summaries, ni fillers etc. only the data. This is useful when using the next new feature. * Source commands - This is a means of running another script from inside a script. * A better, more structured settings dialog using tabs. * Better handling of locked results, and a means of auto-locking results, so that where there are more than on result, these will show up in multiple tabs. * Lock / unlock of result tabs using right-click on the tabs. * Fixed a bug in the login dialog that caused the database list to work if there was an initial, unsuccessful attempt to connect. Dowload from https://sourceforge.net/projects/myquery/ Using the SPARSE_OUTPUT_FILE and SOURCE commands to write SQL that in turn generates SQL and then ruun this is a powerful feature. This particular feature is though rather complex to implement, more so than one might think, so input in this area is highly valued. /Karlsson -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Joining subqueries
mrc_titles is a temp table? On Wed, Oct 15, 2008 at 11:59 PM, Jerry Schwartz [EMAIL PROTECTED] wrote: I tried to make a query that joins to subqueries: SELECT discontinued.b FROM (SELECT mrc_titles.title AS a FROM mrc_titles JOIN prod ON mrc_titles.title = prod.prod_title JOIN pub ON prod.pub_id = pub.pub_id WHERE pub.pub_code = MRC AND prod.prod_discont = 1) AS `discontinued` LEFT JOIN (SELECT mrc_titles.title AS b FROM mrc_titles JOIN prod ON mrc_titles.title = prod.prod_title JOIN pub ON prod.pub_id = pub.pub_id WHERE pub.pub_code = MRC AND prod.prod_discont = 0) AS `available` ON discontinued.a = available.b WHERE available.b IS NULL ; Basically I'm trying to find the `mrc_titles.title` records that only match where `prod`.`prod_discont` = 1, excluding those that match `prod`.`prod_discont` = 0. I think the query makes sense to a human, but I get ERROR 1137 (HY000): Can't reopen table: 'mrc_titles' from MySQL 4.1.22-standard. I didn't see anything about this limitation in the 4.x documentation (although somehow it seems to ring a bell). What am I missing? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: simple design choice
If you want to choose the first one, then the flag' data type must be enum.For int fill the disk with 4 byte and enum just 1 byte. On Sat, Oct 4, 2008 at 2:15 AM, Alex K [EMAIL PROTECTED] wrote: That seems like a nice trick. I suppose the flag would just be an int and not an enum in this case. 2008/10/3 Mr. Shawn H. Corey [EMAIL PROTECTED]: On Fri, 2008-10-03 at 09:58 -0700, Rob Wultsch wrote: On Fri, Oct 3, 2008 at 9:49 AM, Alex K [EMAIL PROTECTED] wrote: Hello, I have a table of a 1 million users. I want to add a flag called delete if a user wants to delete his account. Note that this situation does not happen a lot. 1) Should I alter my users table and add a delete flag to the users table. it's easy to update however it uses a lot of unnecessary space. 2) Should I create a new table user_id, flag already prefilled with all user_ids. 3) Should I create a new table called deleted_users that has a user_id if this user wants to be deleted. it's hassle to update but takes into consideration the spareness of the data. Thank you, Alex #1 Define uses a lot of unnecessary space. I would imagine it would add not much more than 1 MB to the size of db, depending on column choice. A decent choice I think. #2 Yuck. #3 A compact and clean solution. If you're going to do #1, make the new column status, with two states: active and deleted. In the future you can add more states without re-doing your tables again. -- Just my 0.0002 million dollars worth, Shawn Linux is obsolete. -- Andrew Tanenbaum -- 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] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Master-master setup
There're an article about master to master replication at my blog. On Thu, Oct 9, 2008 at 3:45 PM, Simon J Mudd [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (Carl) writes: I am running 5.0.24a on Slackware Linux. I would like to set up a master-master replication process so that I can use both servers as master as add/delete/update records on both servers from different application servers (Tomcat.) I suspect the inserts will be OK but don't understand how the edits and deletes would work (primary key is autoincrement): (Serial) (Serial) TransactionServer A Server B Add to server A1 Replicated 1 Add to server A2 Add to server B (before record 2 2 is replicated) Replicate to server B ? Replicate to server A? Does replication control the order in which transactions are applied so that somehow the replication from server A to server B is applied before the insert to server B? You need to set 2 variables to ensure you don't have problems. # when you have 2 master servers auto_increment_increment = 2 # each server has a different offset (values in this case 1,2) auto_increment_offset= 1 This way each master will generate unique ids Note: doing this means that you will get gaps in your ids as each server uses its own value to generate new ids and these increment by auto_increment_increment every time. Be aware that if the updates to the tables are very frequent it's quite possible that replication delay may mean that the data on both servers is not the same. The only way to ensure that this is avoided is to use explicit WHERE id IN (1,3,43,5,...,nn) clauses so that you are absolutely certain that the changes applied on one master will be produced on the other one. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqld, mysqld-nt, mysqld-debug
Hi. I think you had a mistake for the server types.Mysql-nt.exe is the only exexutable program on windows.So you just pay close attention about it. On Mon, Oct 6, 2008 at 8:35 PM, Steven [EMAIL PROTECTED] wrote: Steve (n) Martin! if you want apache and mysql (and possibly PHP for scripting) AND you want all 3 to run at once then d/l and implement with XAMP there are alot of XAMP specific bells and whistles that are installed that get in the way of tuning your MySQL and tuning your Apache installations so my advice is if you just want to use Mysql standalone dont use XAMP but start mysql with mysqld as suggested Well, that's your opinion. But I think for beginners is XAMPP the best basis. Everything else you can later still wish to change. Once is a fact, I wanted no discussion on the pros and reignite XAMPP. I just wanted to help. ;-) Viel Gluck! Martin Good Luck? What for? Greetings, Steven -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqldump: Error 5: Out of memory
Show the details of your hardware us. On Thu, Oct 2, 2008 at 3:02 PM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi, Just try the below command on console. It will give that the error is exactly related to what. $perror 5 What is total ram in your box. On Thu, Oct 2, 2008 at 12:26 PM, Uma Bhat [EMAIL PROTECTED] wrote: Hey Guys! I have been googling a lot on this error and read various suggestions. But havnt found an appropriate solution yet. I get this error while taking mysqldump of an InnoDB table (say mytable) mysqldump: Error 5: *Out of memory (Needed 632894352 bytes) when dumping table `mytable` at row: 484911* *current my.cnf settings:* innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 32M max_allowed_packet = 1024M -q with mysqldump option did not help. it resulted in same error. Appreciate your quick response ! Much Thanks, Uma -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Speed up slow SQL statement.
You may see the section named group by optimization on the document. On Tue, Sep 30, 2008 at 4:44 AM, Rob Wultsch [EMAIL PROTECTED] wrote: Glancing over things I suggest: ALTER TABLE browse_nodes_to_products ADD INDEX(browse_node_id,product_id); (if product_id has greater cardinality put that before browse_node_id) The syntax: inner join (browse_nodes, browse_nodes_to_products) on (browse_nodes.amazon_id = browse_nodes_to_products.browse_node_id and products.id = browse_nodes_to_products.product_id) is pretty ugly in my opinion. On Mon, Sep 29, 2008 at 7:10 AM, Eric Stewart [EMAIL PROTECTED] wrote: Good morning everyone, products.id is defined as a PRIMARY KEY so it's index. browse_nodes_to_products.product_id is defined as a INDEX so it's indexed. browse_nodes_to_products.browse_node_id is defined as an INDEX so it's indexed. browse_nodes.amazon_id is defined as an INDEX so it's indexed. See http://pastebin.com/m46cced58 It has complete table structures, row counts and EXPLAIN output of the SQL statement I'm trying to optimize. I don't think I understand your question regarding carrying the product_id through the relationship. This is a many to many relationship. A browse_node can contain many products and a product can be in many browse_nodes. This is achieved through a many to many join table browse_nodes_to_products. Further research into the SQL statement is revealing that a temp table is being created and may be one of the reason it's slowing down. Any ideas how I can optimize this? Eric On Sep 26, 2008, at 11:47 AM, Martin Gainty wrote: Hi Eric- the immediate challenge is to fic the join statement so make sure products.id is indexed make sure browse_nodes_to_products.product_id is indexed make sure browse_nodes_to_products.browse_node_id is indexed make sure browse_nodes.amazon_id is indexed there seems to be mapping/relationship challenge for your product to browse_node_id which finally maps to amazon_id would be simpler if is there any way you can carry the product_id thru from products table to browser_nodes_to_products table to browse_nodes table anyone? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Speed up slow SQL statement. Date: Fri, 26 Sep 2008 10:42:07 -0400 Good morning everyone, I've got a sql statement that is running quite slow. I've indexed everything I can that could possibly be applicable but I can't seem to speed it up. I've put up the table structures, row counts, the sql statement and the explain dump of the sql statement all in paste online here http://pastebin.com/m46cced58 I'm including the sql statement itself here as well: select distinct products.id as id, products.created_at as created_at, products.asin as asin, products.sales_rank as sales_rank, products.points as points from products inner join (browse_nodes, browse_nodes_to_products) on (browse_nodes.amazon_id = browse_nodes_to_products.browse_node_id and products.id = browse_nodes_to_products.product_id) where browse_nodes.lft = 5 and browse_nodes.rgt = 10 order by products.sales_rank desc limit 10 offset 0; What I'm trying to accomplish with this is to get an ordered list of unique products found under a category. Any ideas on how I could speed this up? Thanks in advance, Eric Stewart [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ See how Windows connects the people, information, and fun that are part of your life. http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: MySQL 5.1 Function Creation
Make sure your log_bin_trust_function_creator is on. On Sun, Sep 28, 2008 at 3:04 AM, Jesse [EMAIL PROTECTED] wrote: I'm trying to use existing functions from a restored database from 5.0xx to 5.1, and get an error about the mysql.proc table is missing or corrupt. The mysql.proc table appears to be there, and does not appear to be corrupt. I did a grant select on mysql.proc to user, and that did not make any difference, as it has in the past. So, I decided that I'd delete the function from the database, and try to add it back in, and when I do, I get an error, Failed to CREATE FUNCTION. The code that I'm trying to execute is as follows: CREATE DEFINER = 'root'@'localhost' FUNCTION `Age`(dob DATE) RETURNS int(11) DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE today DATE; SELECT CampStartDate INTO today FROM config; RETURN DATE_FORMAT(FROM_DAYS(TO_DAYS(today) - TO_DAYS(dob)), '%Y') + 0; END; Any ideas what's going on? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: How could i check the following values in MySQL Server 5.0
Any additional tools will satisfy your demand. On Mon, Oct 13, 2008 at 7:34 PM, Sudhir Menon [EMAIL PROTECTED] wrote: My reply was with regards to the answer in this post. http://lists.mysql.com/mysql/214827 Anyways thanks for the concern from your end.. Mike :) My query was answered. Thanks Regards Sudhir Menon -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: order of items in a WHERE...IN clause
See the usage of the function named field. On Mon, Jul 28, 2008 at 8:15 PM, Mr. Shawn H. Corey [EMAIL PROTECTED]wrote: On Mon, 2008-07-28 at 07:32 -0400, Gary Josack wrote: Andrew Martin wrote: Hello, Is it permissible to order a clause such that the search term is the first item (in the clause)? standard: field1 IN (123, 654, 789) in question: 123 IN (field1, field2, field3) I am interested to know if the optimizer treats this any differently if anybody can shed any light on it (except for the obvious difference in the above queries!) Thanks, Andy Both are valid syntax where 1 is returned if the expression is equal to any of the values in the list. I can't see the optimizer treating these any differently. Thanks, Gary M. Josack Any difference will come up in an EXPLAIN. To run one, put the word EXPLAIN in front of the SQL statement: EXPLAIN sql_statement; -- Just my 0.0002 million dollars worth, Shawn Where there's duct tape, there's hope. Perl is the duct tape of the Internet. Hassan Schroeder, Sun's first webmaster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: MySQL Sort by Array
Just staightly use ... in .. is ok. On Fri, Oct 24, 2008 at 12:57 AM, Bill Newton [EMAIL PROTECTED]wrote: Pretty standard mysql function. Its been in mysql for a while. http://dev.mysql.com/doc/refman/4.1/en/string-functions.html#function_field Jim Lyons wrote: I'm not familiar with order by field (unless field is a UDF). I know of order by binary. Is this standard mysql syntax? On Wed, Oct 22, 2008 at 10:42 AM, Peter Brawley [EMAIL PROTECTED] wrote: ORDER BY id(5, 34, 9, 25) Can anyone tell me the proper syntax to accomplish this task? ORDER BY FIELD( id, 5, 34, 9, 25 ) PB - Keith Spiller wrote: Hi Guys, I'm trying to sort by a particular order: SELECT * FROM tablename WHERE id='5' OR id='9' OR id='25' OR id='34' ORDER BY id(5, 34, 9, 25) Can anyone tell me the proper syntax to accomplish this task? Thanks for your help. Keith No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus Database: 270.8.2/1739 - Release Date: 10/22/2008 7:23 AM -- Bill Newton Network Merchants Inc. http://www.nmi.com (847) 352-4850 ext 141/ Tel (888) 829-3631/ Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Rotate regular log file only
You're wrong.The new log file will be generated when you flush logs manually. On Fri, Oct 24, 2008 at 2:23 AM, Olaf Stein [EMAIL PROTECTED] wrote: And I assume you backup script also archives or removes the old log file, because flush-logs does not start a new log file if there is still one present On 10/23/08 2:20 PM, Andy Shellam [EMAIL PROTECTED] wrote: Hi Olaf, We use our mysqldump script to rotate the binlogs; it's much safer as it allows MySQL to do the log rotate natively (if you use logrotate, MySQL will complain that either the log doesn't exist when it expects it to, or your slaves will bail out because they didn't know the log was changed. It happened to us recently when we moved the log directory and didn't update the log index.) At 2am our backup system runs the mysqldump script with the extra parameter --flush-logs. This causes MySQL to rotate the log it's using, and as you found out, all slaves respond to the change without an issue. Andy Olaf Stein wrote: Thanks all... Rotating actually does not affect the slaves, they adjust to the new binlog just fine, I guess I should have tried that first. I will nevertheless take a closer look at logrotate... Olaf On 10/23/08 12:13 PM, Uwe Kiewel [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Olaf Stein schrieb: Hi all Is it possible to rotate just the regular (--log) log file? I am not sure if it will be safe, but maybe with logrotate and for /var/log/mysqld.log the copytruncate option for logrotate. If I do flush-logs I have to tell my slaves that (at least I have done so in the past, maybe I don't and the slves realizes by itself?) I think so, b/c I've never told my slaves... HTH, Uwe -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIcBAEBAgAGBQJJAKK3AAoJEEJXG7BUuynntkkP/R5IiZWpafUfQqR+hVUax9at NV8YKfUIz8J1QLrT7cWOEqpuliABP0P6AOS06Tmm4t2ve15BJ1fwxRqHiHEem9BE 7nb1AuQDlGW+qTOVpzJqj2H8b5SARdLoKswTisT0Yz++NDj3WQxVM/UIKotwRnLH edDHSrfjPl+38TmlmGP7/3ZYA2gEAKosgYGrax6bHtSnrw2pfDq6BaXvEwXABAHc aCE6P3DKGr4Ycs2Xlc49IkPHgE6/+SNM9MqVAs83OgxNZK5+c474YdJl7i5hfth1 8RKMPweQgBtYRT3vfrvJdfzg2Wg75pJv1RwkKiGofaAjBmO9y93iNkE57pNXq3sd eWFZR5YcPA+3+GCnAvOMcjzytISlpxNNic235qaYSuoNDMV1rukxSYNpH62kzQPH V3gTKuZcjWYWasa0Y6ylSBWywSOnfc49n0mVdXeoHb7CpIQn3jwCtRG2+UCZUM1W O4U5+bKgXERqqwjNS5sk9SNmq5gQAKYU4IsDZwZcyFY7t/XEHwB3+bCVnm1y4V/s Fzin0FoAIbqm9VzALzTs5YUkWzoSzniGepIBrZR0PO98sDxOlDFUESpYnFj8oNap wjM/5P0tgbw99lIsLAMy7+FdPIlSssWxq+LFC4dR6o+pzVrYjFjoRg3MdYn9ein8 svOEP/N79cK5pPZJpDyY =cN1H -END PGP SIGNATURE- - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Permissions
I know it.Thanks. I'm sorry that I had a mistake to what you said. On Thu, Oct 23, 2008 at 2:32 AM, Ian Christian [EMAIL PROTECTED] wrote: 2008/10/21 Moon's Father [EMAIL PROTECTED]: Could you please give me an idea of how to manage the privileges inside mysql? http://www.google.co.uk/search?q=mysql+grant first hit :) -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: user expires?
It didn't occured unless you manually changed your user's privilege. On Wed, Oct 22, 2008 at 11:05 PM, kalin m [EMAIL PROTECTED] wrote: hi all... i had a weired thing happened is it possible for a user privileges to expire?! suddenly today an application stopped working and i was getting the message that the user can't login. now, i did mess with it last night trying to give it file privileges but since i would have to give it file privileges to everything (doesn't make sense) i didn't do it. i did try thought with: mysql grant file to the [EMAIL PROTECTED] etc. it didn't work and i left it alone. didn't flush any privileges. it appears all the privileges were taken off the user anyway?! i just did grant all again and it seems to be working again... whats up with that?!??! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Stopping DNS Lookups
Add skip-name-reslove in my.cnf and restart mysql immediately. On Thu, Oct 23, 2008 at 12:37 AM, Richard S. Huntrods [EMAIL PROTECTED]wrote: Awesome! Thanks very much - exactly what I was looking for. I'm in the field and was under the gun, otherwise would have checked the manuals first. Again, thanks. -Richard Hassan Schroeder wrote: On Wed, Oct 22, 2008 at 7:40 AM, Richard S. Huntrods [EMAIL PROTECTED] wrote: Recently I had to start monitoring the firewall traffic on this intranet, and discovered the MySQL server is routinely sending queries to the main DNS server (outside the firewall). I suspect the server is performing reverse DNS lookups for some reason. Is there a quick way of disabling these calls to the DNS server? See http://dev.mysql.com/doc/refman/5.0/en/dns.html HTH, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: select ... into outfile=stdout ?
Maybe you can use mysql -e instead. On Mon, Oct 20, 2008 at 12:51 AM, walter harms [EMAIL PROTECTED] wrote: hi ronaldo i tried and failed. it seems that mysql has no option to specify a select statement. did i mis something ? re, wh walter harms schrieb: hi ronaldo, iadmit i was mysql (the command) fixated :) thx a lot, wh Rolando Edwards schrieb: Try mysqldump !!! On this web page, http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html It says the following: --fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=... These options are used with the -T option and have the same meaning as the corresponding clauses for LOAD DATA INFILE. See Section 12.2.6, LOAD DATA INFILE Syntax. By default, its output to stdout. Give it a try !!! -Original Message- From: walter harms [mailto:[EMAIL PROTECTED] Sent: Thursday, October 16, 2008 5:06 AM To: 'mysql' Subject: select ... into outfile=stdout ? hi list, i need some options from outfile (exspecialy:FIELDS TERMINATED BY) and would like to send the output to stdout to further processing. unfortunately i found no proper way to force the output to stdout. for now i use the redirection of the mysql -NB output but the interface lacks the options of into outfile. (It is easy to fix using tr but not what was intended). i tried /dev/stdout but this does not work either. any ideas ? re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: MySQL Date problem
Timestamp is the best bridge between java and mysql,I think.
On Sun, Oct 19, 2008 at 4:48 PM, Rama [EMAIL PROTECTED] wrote:
DATE TIME ZONE SENSITIVE
DATETIME NOT SENSITIVE TO TIME ZONE
TIMESTAMP TIMEZONE SENSITIVE
iam getting the above problem as date is timezone sensitive.i resolved it
by changing the field type from date to string.
YOu can use any method to fill the DATE field of mysql .which method we
use to fill mysql date is not important in this context.
On Sun, Oct 19, 2008 at 1:33 PM, Roland Kaber [EMAIL PROTECTED] wrote:
SK wrote:
2008/10/19 Rama [EMAIL PROTECTED]
hi,
iam using java,hibernate,mysql
i am storing *19-10-2008 (*of type java.sql.Date) (TIMEZONE GMT) to
mysql
DATE field.
when i retrieve the same date from mysql to java it is being
displayed
as
*18-10-2008 18:30:00 GMT *
i could not able to figure out what is going wrong . can any one
please
guide me on What can go wrong in above scenario?
there are a lot of mothods to express data in java,but in mysql only
have
one type.
may be types not match.
i think you should storing 19-10-2008 to mysql char(10) field.
--rama
Hi
What about using the STR_TO_DATE function to transform the java Date as a
MySQL Date: STR_TO_DATE('19/10/2008', '%m/%d/%Y')? To transform this back
into the original format, you may use the DATE_FORMAT function:
DATE_FORMAT('2008-10-19', '%d/%m/%Y).
Best regards
Roland
--
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
Re: Problem with GROUP BY
Learnt! On Wed, Oct 15, 2008 at 5:28 PM, philip [EMAIL PROTECTED] wrote: Date: Tue, 14 Oct 2008 16:55:11 +0300 From: Olexandr Melnyk [EMAIL PROTECTED] To: [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Problem with GROUP BY http://jan.kneschke.de/projects/mysql/groupwise-max 2008/10/14 Peter Brawley [EMAIL PROTECTED] Philip mysql SELECT number, MAX(event), name FROM info GROUP BY number; For discussion examples see Within-group aggregates at http://www.artfulsoftware.com/queries.php. Thank you both very much for your replies. Of course the solution is 'obvious' now I know the answer but as a relative newcomer to MySQL I had spent the best part of a day trying to find it. TTFN, Philip Riebold, [EMAIL PROTECTED] /\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (switchboard), 09259 (internal) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: SQL select basics
select * from ( SELECT ID_number, count( CU_number ) AS CC FROM MyTable GROUP BY ID_number ) T WHERE CC = ( select max(cc) from ( SELECT ID_number, count( CU_number ) AS CC FROM MyTable GROUP BY ID_number ) T2 ) On Wed, Oct 15, 2008 at 5:08 PM, dave aptiva [EMAIL PROTECTED]wrote: Hello all, I'm new to sql and have a question if someone would be kind enough to help me with, if I have a table that stores telemarketers by ID_number and the customer that they spoke to by CU_number and I use a select statement such as; SELECT ID_number, count( CU_number ) FROM MyTable GROUP BY ID_number; To find the number of calls that each telemarketer made, this works fine but how do I then use the results that are returned to find those telemarketers that made the most telephone calls ? I tried SELECT ID_number, max( count( CU_number ) ) but this causes an error # - Invalid use of group function I also tried SELECT ID_number, count( CU_number ) AS CC FROM MyTable GROUP BY ID_number WHERE CC = max( CC ); But this also causes an error, anyone able to offer some pointers ? Thanks Dave. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: How to build sub-sequence ... AUTO_INCREMENT on a sub-key starting with a specific offset?
You could create an extra table in order to record the max number of widget,the the ID should alway be 1. On Thu, Oct 16, 2008 at 2:04 AM, Rob Wultsch [EMAIL PROTECTED] wrote: I would do a muli key PK with a after insert trigger to that would change widget_number 1 to 1000. Just my HO... I would use this combo as the primary key, but I hate doing joins with multiple primary keys, so I'll also keep the widget_id for the purpose of making joins easier. Why? Both of these fields are ints, so the key length would rather small. I don't think the 'MAX' is optimized, though and maybe there is a better, more robust way to do this which is already built into MySQL that I don't know about. MAX should be fast, assuming the field is indexed. -- Rob Wultsch [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Permissions
Could you please give me an idea of how to manage the privileges inside mysql? Thank you very much. On Wed, Oct 15, 2008 at 5:01 AM, Rob Wultsch [EMAIL PROTECTED] wrote: On Tue, Oct 14, 2008 at 1:49 PM, Grant Peel [EMAIL PROTECTED] wrote: Hi all, I run a shared Apache, Perl, PHP, Mysql, on FreeBSD environment. Here is a question: IF I have a user, that has no permissions, but with a decent password, (in the mysql 'Users' table), AND that user only has access to his/her database through the local host (i.e. perl or PHP scripts), IS it safe to grant 'All' privs to that user in the database grants table? -Grant GRANT ALL PRIVILEGES is a bad habit. http://ronaldbradford.com/blog/why-you-do-not-use-grant-all-on-2008-09-23/ -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysql binlogs and their expiry times
Maybe it's a bug.^___^ On Thu, Oct 9, 2008 at 1:37 PM, Andrew Garner [EMAIL PROTECTED]wrote: On Wed, Oct 8, 2008 at 3:10 AM, Zbigniew Szalbot [EMAIL PROTECTED] wrote: Hi there, I hope someone can help. Due to they way my HD has been sliced I had to move mysql database to /usr/local/mysql. All works fine. Last week I added this entry: #expire bin logs expire_logs_days = 7 to /usr/local/mysql/my.cnf I restarted the MySQL server and now I have been waiting for the binlogs to automatically expire but this is not happening: $ ls -l /usr/local/mysql -r--r--r-- 1 mysql mysql4954 Oct 1 07:30 my.cnf drwx-- 2 mysql mysql1536 Sep 27 07:10 mysql -rw-rw 1 mysql mysql 1073745213 Sep 2 04:07 mysql-bin.47 -rw-rw 1 mysql mysql 1073746878 Sep 7 03:48 mysql-bin.48 -rw-rw 1 mysql mysql 1073745707 Sep 11 20:07 mysql-bin.49 -rw-rw 1 mysql mysql 175527890 Sep 12 08:32 mysql-bin.50 -rw-rw 1 mysql mysql 128272 Sep 12 08:40 mysql-bin.51 -rw-rw 1 mysql mysql 1073745119 Sep 17 04:35 mysql-bin.52 -rw-rw 1 mysql mysql 1073747657 Sep 22 04:26 mysql-bin.53 -rw-rw 1 mysql mysql 1073744456 Sep 27 03:28 mysql-bin.54 -rw-rw 1 mysql mysql 986782722 Oct 1 07:32 mysql-bin.55 -rw-rw 1 mysql mysql 1073742442 Oct 6 04:18 mysql-bin.56 -rw-rw 1 mysql mysql 536487381 Oct 8 07:45 mysql-bin.57 -rw-r- 1 mysql mysql 209 Oct 6 04:18 mysql-bin.index Do you have any idea why? Or if /usr/local/mysql/ is a correct location for my.cnf file? Perhaphs it should go to /usr/local/etc/ ? If it matters, I use mysql-server-5.0.67 on FreeBSD 7.0-Release machine. Many thanks! I've seen MySQL get confused in some situations - seemingly after running out of disk space or when someone manually maintains bin logs outside of mysql-bin.index (e.g. find+rm). expire-logs-days stops working - explicit flush logs doesn't trigger expiration, log rotations doesn't trigger expiration. In the cases I've run into an explicit PURGE MASTER LOGS or a restart usually fixes things and further flush-logs/log rotations appear to work - sometimes after correcting the mysql-bin.index. There's a bug report similar to this here: http://bugs.mysql.com/bug.php?id=28238. Might be useful to also be aware of: http://bugs.mysql.com/bug.php?id=37027 ~Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Upgrade from 4.0.26 to 5.0.67
Use mysqldump on the old version and import the data into new version in my opinion. On Fri, Aug 22, 2008 at 1:29 AM, Andy Shellam [EMAIL PROTECTED]wrote: FYI the manual for 5.0 recommends upgrading to 4.1 first. As a general rule, we recommend that when upgrading from one release series to another, you should go to the next series rather than skipping a series. If you wish to upgrade from a release series previous to MySQL 4.1, you should upgrade to each successive release series in turn until you have reached MySQL 4.1, and then proceed with the upgrade to MySQL 5.0. For example, if you currently are running MySQL 3.23 and wish to upgrade to a newer series, upgrade to MySQL 4.0 first before upgrading to 4.1. It also says to run the mysql_upgrade program to convert your table formats and grant tables. There have been plenty of changes as you'd expect, including numerous incompatible changes to the SQL parser, so make sure you read the following manual page first to see if your applications are affected: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html. If in any doubt, a dump from the old server and reload into the new server would probably be a better upgrade method. Andy Quoting Nanu Kalmanovitz [EMAIL PROTECTED]: Hi! I wish to upgrade the MySQL on a web server (Novell 6.5 sp6 - Apache 2, MySQL ver. 4.0.26, PHP 5.2.3) to 4.1.2 or 5.0.67. Is there any possibility to upgrade directly from MySQL 4.0.26 to 5.0.67, without upgrading first to the intermediate versions? TIA Nanu -- 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] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Lost connection
Only increase max_allowed_packet is ok. On Tue, Aug 19, 2008 at 5:49 PM, Warren Young [EMAIL PROTECTED] wrote: Mad Unix wrote: During the update of the MySQL DB (delete/insert), I keep getting the following message Lost connection to MySQL server during query... By default, the MySQL server drops a connection after 8 hours of receiving no queries on that connection. This can happen in an application that keeps its connection open constantly, and people don't use it overnight or over a weekend. You can either increase the timeout in my.cnf, or you can ping the connection occasionally with mysql_ping(). Or, you can add code to your applications to detect this, and reestablish the connection and retry the command. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqlimport/load data infile is using a temp file - why?
You should increase parameter named max_bulk_insert_buffer_size and max_allowed_packet. On 8/21/08, Ananda Kumar [EMAIL PROTECTED] wrote: Mysql use tmpdir, when ever there is any index creation. regards anandkl On 8/21/08, jthorpe [EMAIL PROTECTED] wrote: Hi, I've been trying to import a 10G dump file using mysqlimport and it is eventually failing because it runs out of tmpdir space -- I get Errcode: 28. I was surprised that it was using a temp file at all. I've looked in the documentation and other sources but have not been able to find anything about this file. This is on v5.0.51. The table is innodb and has 75 columns with 65% of them tinyint, 20% float, and the rest char,datetime,int. The primary key is composite on two columns (int,char). The data file that is being imported is on the database server. I'll try this again and monitor the status parameters to see what it is doing, but I was wondering if anyone already knows? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: My Tables are in use
This is weird issue that I have never met.I think you should show us your table's definition phrase and use flush tables to shut down all the open tables. On 8/21/08, Abdul Gomaa [EMAIL PROTECTED] wrote: Hey everyone! I really hope this is the right place to post this problem. I have have been struggling with this for over 3 months now. Have a look at the following screenshot: http://img395.imageshack.us/img395/8792/63815426ma1.jpg It just happened one day without me doing anything. And if I try and do anything I get an error message along the lines of: #1017 - Can't find file: 'ads' (errno: 2) However, when I tried to upgrade wordpress, this also happened: http://img503.imageshack.us/img503/587/20694910cf8.jpg I also get an error message saying: #1033 - Table './asgsoft_domainleft/wp_posts' was created with a different version of MySQL and cannot be read . This isn't true as I was only trying to upgrade WP and it was all done on the same server Can you help me fix this? Can my databse content be retrieved? Please help!! Thanks -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: removing duplicate entries
If you're good at Chinese ,just visit here.
http://blog.chinaunix.net/u/29134/showart_375303.html
On Thu, Aug 7, 2008 at 10:34 PM, Magnus Smith
[EMAIL PROTECTED] wrote:
I think got it in the end by doing a union and a join.
delete AA, PA from ACCOUNTACTION AA, ACCOUNTPAYMENTACTION PA
where AA.ID = PA.ID and AA.ID in
(select D.ID from
(select A1.ID from ACCOUNTACTION A1
left join
(
select * from ACCOUNTACTION A2
where A2.ACTIONDATE like '2008-08-01 02:00%'
group by A2.ACCOUNT_ID
having count(A2.ACCOUNT_ID) 1
union
select * from ACCOUNTACTION A3
where A3.ACTIONDATE like '2008-08-01 02:00%'
group by A3.ACCOUNT_ID having count(A3.ACCOUNT_ID) = 1
) as U1
on A1.ID = U1.ID
where A1.ACTIONDATE like '2008-08-01 02:00%'
and U1.ID is NULL
) as D
);
Thanks for the pointers ;-)
-Original Message-
From: Magnus Smith [mailto:[EMAIL PROTECTED]
Sent: 07 August 2008 10:35
To: Ananda Kumar
Cc: mysql@lists.mysql.com
Subject: RE: removing duplicate entries
Yes I can see you are correct. I tried setting up a little test case
myself.
CREATE TABLE ACCOUNTACTION (
ID INT NOT NULL PRIMARY KEY,
ACTIONDATE DATETIME,
ACCOUNT_ID INT NOT NULL
);
CREATE TABLE ACCOUNTPAYMENTACTION (
ID INT NOT NULL PRIMARY KEY,
AMOUNT INT
);
INSERT INTO ACCOUNTACTION (ID, ACTIONDATE, ACCOUNT_ID)
VALUES('001', '2008-08-01 02:00:00', '101'),
('002', '2008-08-01 02:00:00', '101'),
('003', '2008-08-01 02:00:00', '101'),
('004', '2008-08-01 02:00:00', '102'),
('005', '2008-08-01 02:00:00', '103'),
('006', '2008-08-01 02:00:00', '104'),
('007', '2008-08-01 02:00:00', '104'),
('008', '2008-08-01 02:00:00', '105'),
('009', '2008-08-01 03:00:00', '104'),
('010', '2008-08-01 03:00:00', '105'),
('011', '2008-08-01 02:00:00', '106');
INSERT INTO ACCOUNTPAYMENTACTION (ID, AMOUNT)
VALUES('001', '1000'),
('002', '1000'),
('003', '1000'),
('004', '1000'),
('005', '1000'),
('006', '1000'),
('007', '1000'),
('008', '1000'),
('009', '1000'),
('010', '1000'),
('011', '1000');
I got the following query that seems to work on my test case.
I create a union of everything that is not a duplicate and then take the
ones that are not in this to be the duplicates
select ACCOUNTACTION.ID, ACCOUNTACTION.ACCOUNT_ID from ACCOUNTACTION
where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%'
and (ACCOUNTACTION.ID, ACCOUNTACTION.ACCOUNT_ID)
not in
(select ACCOUNTACTION.ID, ACCOUNTACTION.ACCOUNT_ID from ACCOUNTACTION
where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%'
group by ACCOUNTACTION.ACCOUNT_ID
having count(ACCOUNTACTION.ACCOUNT_ID) 1
union
select ACCOUNTACTION.ID, ACCOUNTACTION.ACCOUNT_ID from ACCOUNTACTION
where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%'
group by ACCOUNTACTION.ACCOUNT_ID having count(ACCOUNTACTION.ACCOUNT_ID)
= 1);
The problem is that when I try to apply this to my real database tables
that are quite large then the query does not return.
I am thinking that there must be a more efficient way to write the
query?
I would be pleased to hear any suggestions - thanks
From: Ananda Kumar [mailto:[EMAIL PROTECTED]
Sent: 06 August 2008 13:51
To: Magnus Smith
Cc: mysql@lists.mysql.com
Subject: Re: removing duplicate entries
I just did a test case here
select * from amc_25;
+--+
| id |
+--+
|2 |
| 14 |
|1 |
|2 |
+--+
4 rows in set (0.01 sec)
select id from amc_25 group by id having count(id) 1 and id!=min(id);
Empty set (0.00 sec)
It does not give me any rows.
R u sure the rows returned, are the one you want to keep are indeed
duplicates
On 8/6/08, Magnus Smith [EMAIL PROTECTED] wrote:
When I try the first suggestion (i) then I get all the 1682
duplicate rows. The thing is that I need to keep the originals which
are the ones with the lowest ACCOUNTACTION.ID http://accountaction.id/
value.
The second suggestion (ii) gives me 563 rows that are the
duplicates with the lowest ACCOUNTACTION.ID http://accountaction.id/
which are the ones I wish to keep
So the ones I want to delete are the ones in (i) and not (ii)
When I use
select ACCOUNTACTION.ID http://accountaction.id/ from
ACCOUNTACTION
where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%'
group by ACCOUNTACTION.ACCOUNT_ID
having (count(ACCOUNTACTION.ACCOUNT_ID) 1
and ACCOUNTACTION.ID http://accountaction.id/ !=
min(ACCOUNTACTION.ID http://accountaction.id/ ));
then I get 1119 rows which is all the duplicates in (i) less the
originals in (ii)
The problem I'm having is using this in a delete statement.
From: Ananda Kumar [mailto:[EMAIL PROTECTED]
Sent: 06 August 2008 10:11
Re: Could not initialize master info structure;
Delete all your relay log ,restart mysqld and use a new change master to statement to do a slave configuration. On Sat, Aug 9, 2008 at 2:32 PM, Brown, Charles [EMAIL PROTECTED] wrote: Could not initialize master info structure; Can someone help me. I am getting the above message This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: can some please help me -- REPLICATION
Add additional option to my.cnf [mysqld] relay-log-purge=1 On Mon, Aug 11, 2008 at 2:21 AM, Brown, Charles [EMAIL PROTECTED] wrote: Here is what I'm getting mysql change master to master_host = 'naxbmisq01.bmi.com', master_user = 'repl', master_password = 'repl'; ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log mysql reset slave; Query OK, 0 rows affected (0.00 sec) mysql change master to master_host = 'naxbmisq01.bmi.com', master_user = 'repl', master_password = 'repl'; ERROR 29 (HY000): File './naxbmisq02-relay-bin.000157' not found (Errcode: 2) mysql -Original Message- From: David Giragosian [mailto:[EMAIL PROTECTED] Sent: Sunday, August 10, 2008 12:54 PM To: mysql@lists.mysql.com Subject: Re: can some please help me -- REPLICATION On 8/10/08, Jim Lyons [EMAIL PROTECTED] wrote: you should probably just resync your slave. If it hasn't run for over a month then there's not a lot of point in trying to start it up. Even if you did start the slave (which seems doubtful) you'd have over a month's worth of commands to make up. You can tell mysql to not keep relay logs that have already been used. What's the command or setting for this action? --David. This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: relay-bin.000157' not found
Remove line contains naxbmisq02-relay-bin.000157 in your naxbmisq02-relay-bin.index file. On Fri, Aug 8, 2008 at 1:58 AM, Brown, Charles [EMAIL PROTECTED] wrote: Please advise me on how to restart MYSQL slave instance. All efforts have failed. When I issued the START SLAVE command it came back with an error -- see below. ERROR 29 (HY000): File './naxbmisq02-relay-bin.000157' not found (Errcode: 2 I inadvertently deleted this file. Thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Failed to initialize the master info structure
If you have the whole backup ,then you can simply use reset slave statement to purge all the relay log on your slave machine and use change master to statement to do a new slave configuration. On Tue, Aug 12, 2008 at 2:59 AM, Michael Dykman [EMAIL PROTECTED] wrote: The first thing that occurs to me is to double-check the r/w permissions in your data directory. I assume you are logged into mysql as root when you attempt this command? - michael dykman On Mon, Aug 11, 2008 at 1:47 PM, Brown, Charles [EMAIL PROTECTED] wrote: Can someone tell me why I am getting this message whenever I issued a CHANGE MASTER command? @4000489f38462d0bedd4 080810 13:49:32 [ERROR] Failed to initialize the master info structure Thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Connections
Check if your connections are too large to afford any other connections. On Mon, Aug 11, 2008 at 8:54 PM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi All, Connecting to mysql server is taking lot of time near around 4 to 5 seconds. Its a pentium box with 512Mb ram max_connections=200 what can be the reason ? -- Krishna Chandra Prajapati -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
How can I disable the app's reuse of prepared statements
Now mysql's version is 5.0.45 Any reply is appreciated. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Issue about routine's memory leak.
Hi. There are 1 store procedures in my database,and prepared statement was used within procedure's definition. But when our concurrency is higher,mysqld's memory grows very fast. PS:I have explicitly used drop prepare .. statement in procedure's definition. Anybody can tell me why this happened to me? -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Why people don't use engine named BDB?
Thanks.I know the reason now.:) On Mon, Jul 21, 2008 at 10:54 PM, Bill Newton [EMAIL PROTECTED] wrote: No, its mainly because BDB wasn't very good. Its transactional, but not MVCC. Take a look at a contemporary article when the acquisition was made : http://www.computerworld.com/databasetopics/data/software/story/0,10801,108705,00.html Curtis Maurand wrote: Its mainly because it was purchased by Oracle. BDB provided transaction support. Innodb has been the defacto choice for a ACID transactions, but Innodb was also purchased by Oracle in its attempt to kill MySQL after its failed attempt to purchase MySQL. That's why MySQL has been working on their own storage engine as well as the pluggable storage system. Curtis David Giragosian wrote: On 7/21/08, Moon's Father [EMAIL PROTECTED] wrote: Any reply is appreciated . -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn Maybe something to do with this: *BDB support will be removed. * Note that, as of MySQL 5.1, BDB isn't supported any longer. http://dev.mysql.com/doc/refman/5.0/en/bdb-storage-engine.html But you're right that as a storgage engine, there have been very few questions related to it, on this mailing list anyway. -- Bill Newton Network Merchants Inc. http://www.nmi.com (847) 352-4850/ Tel (888) 829-3631/ Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Who could please tell me whether my procedure's memory usefulness is normally or not?
mysql show global status like '%stmt%'; +-+-+ | Variable_name | Value | +-+-+ | Com_stmt_close | 4875504 | | Com_stmt_execute| 4875504 | | Com_stmt_fetch | 0 | | Com_stmt_prepare| 4875507 | | Com_stmt_reset | 0 | | Com_stmt_send_long_data | 0 | | Prepared_stmt_count | 0 | +-+-+ 7 rows in set (0.00 sec) -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
