Re: MySQL Master Master Replication and data loss
Dear Suresh, Thank you. In MySQL Replication, as the slave itself takes the writes from master but in how much time period does slave goes to master. is there any parameter where I can set this. that after every 60 seconds slave should write data from master to its own local database. -- Regards, Manasi Save Quoting Suresh Kuna sureshkumar...@gmail.com: Hi Manasi, As both are implemented by replication, there is a possibility for loosing data. Thanks Suresh Kuna MySQL DBA On Wed, Jan 13, 2010 at 8:20 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I want to implement MySQL Master Master Replication. But I have read in the various documentation that in mysql replication it is possible that I loose data. Is it also applicable to MySQL Master Master Replication. Thanks in advance. -- Regards, Manasi Save -- Thanks Suresh Kuna MySQL DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
When using FOR UPDATE whole the table seems to lock instead of selected row
Hi, I have created a query to read and update a stock item by using the FOR UPDATE statement. According to the MySql documention only the rows that are selected using the FOR UPDATE should be locked for other sessions, but somehow whole the table is locked. This post gives some general information and then shows 3 scenarios. The first 2 scenarios work as expected, but the last one fails. Two questions: a.. Does anyone has an idea why scenario 3 does not work as expected and what is the solution to make it work? b.. Is this the preferred way to update a stock table? If not, could you provide me an example how it should be done? Here some clarification about the problem: Version MySQL 5.0.45-community-nt Stock table DROP TABLE IF EXISTS `mydatabase`.`stock`; CREATE TABLE `mydatabase`.`stock` ( `ID` bigint(20) unsigned NOT NULL auto_increment, `ProductID` bigint(20) unsigned NOT NULL, `SizeID` bigint(20) unsigned NOT NULL, `Quantity` int(11) NOT NULL, PRIMARY KEY USING BTREE (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; MySQL example This query updates the stock of one product of a particular size by decrementing it's quantity by 1. START TRANSACTION; SET AUTOCOMMIT=0; SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 2 FOR UPDATE; UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1 AND Stock.SizeID = 2; COMMIT; Now let me show you three scenarios wherein you can see that using the FOR UPDATE statement is not working as it should. Scenario 1 (works as expected) I opened two sessions of MySql Query Browser and each executing the same MySql query. So, both looking at the same row (same selection criteria). START TRANSACTION; SET AUTOCOMMIT=0; SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 2 FOR UPDATE; UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1 AND Stock.SizeID = 2; COMMIT; 1) Session 1: START TRANSACTION; 2) Session 1: SET AUTOCOMMIT=0; 3) Session 1: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 2 FOR UPDATE; (returns Stock.Quantity as expected) 4) Session 2: START TRANSACTION; 5) Session 2: SET AUTOCOMMIT=0; 6) Session 2: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 2 FOR UPDATE; (blocks as exepected) 7) Session 1: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1 AND Stock.SizeID = 2; 8) Session 1: COMMIT; (the blocking step 6 is now executed and returns the updated Stock.Quantity as exepected) 9) Session 2: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1 AND Stock.SizeID = 2; 10) Session 2: COMMIT; Scenario 2 (works as expected) I opened two sessions of MySql Query Browser and each executing the same MySql query. Only the selection criteria are different; both are using another value for the primary key ID. When I use different values for the primary key ID I have the same results as in scenario 1. Session 1 query START TRANSACTION; SET AUTOCOMMIT=0; SELECT Stock.Quantity FROM Stock WHERE Stock.ID = 1 FOR UPDATE; UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ID = 1; COMMIT; Session 2 query START TRANSACTION; SET AUTOCOMMIT=0; SELECT Stock.Quantity FROM Stock WHERE Stock.ID = 2 FOR UPDATE; UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ID = 2; COMMIT; 1) Session 1: START TRANSACTION; 2) Session 1: SET AUTOCOMMIT=0; 3) Session 1: SELECT Stock.Quantity FROM Stock WHERE Stock.ID = 1 FOR UPDATE; (returns Stock.Quantity as expected) 4) Session 2: START TRANSACTION; 5) Session 2: SET AUTOCOMMIT=0; 6) Session 2: SELECT Stock.Quantity FROM Stock WHERE Stock.ID = 2 FOR UPDATE; (no blocking as exepected, because it's another row; returns Stock.Quantity as expected) 7) Session 1: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ID = 1; 8) Session 1: COMMIT; 9) Session 2: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ID = 2; 10) Session 2: COMMIT; Scenario 3 (does not work as expected) I opened two sessions of MySql Query Browser and each executing the same MySql query. Only the selection criteria are different; both are using another value for the non-primary key SizeID. Making SizeID a primary key does not influence the results of this scenario. So, the sessions work in different rows, but session 2 is blocked! It looks like the whole table is locked instead of only one row? Session 1 query START TRANSACTION; SET AUTOCOMMIT=0; SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 1 FOR UPDATE; UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1 AND Stock.SizeID = 1; COMMIT; Session 2 query START TRANSACTION; SET AUTOCOMMIT=0; SELECT Stock.Quantity FROM Stock WHERE
Re: MySQL Master Master Replication and data loss
Hi Manasi, Inside MySQL, there are no such parameters and the Slave delay depends on different things like network between Master and Slave, load of the MySQLD server etc... To make the slave behind for a particular period of time, use mk-slave-delay tool from the the maakit. It help your slave to be lack for a particular period of time. On Thu, Jan 14, 2010 at 2:11 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Dear Suresh, Thank you. In MySQL Replication, as the slave itself takes the writes from master but in how much time period does slave goes to master. is there any parameter where I can set this. that after every 60 seconds slave should write data from master to its own local database. -- Regards, Manasi Save Quoting Suresh Kuna sureshkumar...@gmail.com: Hi Manasi, As both are implemented by replication, there is a possibility for loosing data. Thanks Suresh Kuna MySQL DBA On Wed, Jan 13, 2010 at 8:20 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I want to implement MySQL Master Master Replication. But I have read in the various documentation that in mysql replication it is possible that I loose data. Is it also applicable to MySQL Master Master Replication. Thanks in advance. -- Regards, Manasi Save -- Thanks Suresh Kuna MySQL DBA -- Thanks Suresh Kuna MySQL DBA
Re: myslqd_safe is not starting. pls help
Hi Faizal, all! F.A.I.Z.A.L wrote: hi anyone can help. its urgent. iam trying to start mysql server but it is not starting. Just read the output: bash-2.03# ./mysqld_safe [2] 3422 bash-2.03# Starting mysqld daemon with databases from /usr/local/mysql/var STOPPING server from pid file /usr/local/mysql/var/andd141.pid 100114 11:31:22 mysqld ended log error bash-2.03# more andd141.err 100114 11:31:19 mysqld started 100114 11:31:20 InnoDB: Started; log sequence number 0 44957717 /usr/local/mysql/libexec/mysqld: File './mysql-bin.25' not found (Errcode: 13) 100114 11:31:20 [ERROR] Failed to open log (file './mysql-bin.25', errno 13) ^^ ^^ Assuming this is Linux: joerg:~ find /usr/include -name 'errno*' | xargs fgrep 13 ... ((some irrelevant lines)) /usr/include/asm-generic/errno-base.h:#define EACCES 13 /* Permission denied */ Call man 2 open and search for EACCES, and you will find: | EACCES The requested access to the file is not allowed, or search permission is denied for one of | the directories in the path prefix of pathname, or the file did not exist yet and write | access to the parent directory is not allowed. (See also path_resolution(7).) Then take appropriate action. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel 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/mysql?unsub=arch...@jab.org
Re: myslqd_safe is not starting. pls help
hi thanks for your response.. this is solaris 8 machine. thanks a lot. i found the problem and resolved.. problem is: access problem in file system './mysql-bin.25' is created in root.root but it should be in mysql.mysql (user.group). i changed this to mysql, then service started... thanks again.. Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Jan 14, 2010 at 4:13 PM, Joerg Bruehe joerg.bru...@sun.com wrote: Hi Faizal, all! F.A.I.Z.A.L wrote: hi anyone can help. its urgent. iam trying to start mysql server but it is not starting. Just read the output: bash-2.03# ./mysqld_safe [2] 3422 bash-2.03# Starting mysqld daemon with databases from /usr/local/mysql/var STOPPING server from pid file /usr/local/mysql/var/andd141.pid 100114 11:31:22 mysqld ended log error bash-2.03# more andd141.err 100114 11:31:19 mysqld started 100114 11:31:20 InnoDB: Started; log sequence number 0 44957717 /usr/local/mysql/libexec/mysqld: File './mysql-bin.25' not found (Errcode: 13) 100114 11:31:20 [ERROR] Failed to open log (file './mysql-bin.25', errno 13) ^^ ^^ Assuming this is Linux: joerg:~ find /usr/include -name 'errno*' | xargs fgrep 13 ... ((some irrelevant lines)) /usr/include/asm-generic/errno-base.h:#define EACCES 13 /* Permission denied */ Call man 2 open and search for EACCES, and you will find: | EACCES The requested access to the file is not allowed, or search permission is denied for one of | the directories in the path prefix of pathname, or the file did not exist yet and write | access to the parent directory is not allowed. (See also path_resolution(7).) Then take appropriate action. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
version
I have an install script that does some stuff with mysql (i.e. install, start, etc). It installs mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline 5.0 This was good when we just used CentOS 4.5. Now we are doing some later CentOS versions and the mysql version may be higher. I want to do something like mysql --version and process the result and if the version is = 5.0.19 skip the mysql installation and just do the other stuff. I can't compare as it is right now because the . and stuff may screw up the comparison (e.g. ver 5.2 will show as greater than 5.19 eg). I want to know, if I break the individual pieces like 14 12 5 0 19 I can do some sort of calculation to determine a number that I can actually compare. Or can I just remove all the decimal points, like 14.12.5.0.19 becomes 14125019? I might have to make it like 14120050019 or something. What is an algorithm I can use? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: version
You *should* be using a package manager (perfectly fine RPMs available for all your needs), but if you must do this, it's a reasonably safe bet to right-align and zero-pad all your number to 4 digits, at which point you're free to concatenate them and treat them as a single number. 14.12.5.0.19 then becomes 0014 0012 0005 0019 which becomes 14001200050019. You'd probably be safe with 3 or maaaybe even two positions, depending on how many releases get done :-) The better-but-more-work way is to compare every number separately, starting with the major release. On Thu, Jan 14, 2010 at 4:21 PM, tony.chamberl...@lemko.com wrote: I have an install script that does some stuff with mysql (i.e. install, start, etc). It installs mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline 5.0 This was good when we just used CentOS 4.5. Now we are doing some later CentOS versions and the mysql version may be higher. I want to do something like mysql --version and process the result and if the version is = 5.0.19 skip the mysql installation and just do the other stuff. I can't compare as it is right now because the . and stuff may screw up the comparison (e.g. ver 5.2 will show as greater than 5.19 eg). I want to know, if I break the individual pieces like 14 12 5 0 19 I can do some sort of calculation to determine a number that I can actually compare. Or can I just remove all the decimal points, like 14.12.5.0.19 becomes 14125019? I might have to make it like 14120050019 or something. What is an algorithm I can use? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: version
I am using RPM. And for Centos 5 it was installing a lower version of mysql than what was installed with the system. That is why I want to check before doing the RPM. I guess the alternative is to use the latest version all the time, but not sure whether that will work on the 4.5 version. The other thing is, mysql appears to keep changing between /etc/init.d/mysqld and /etc/init/mysql so I also have to do an ls /etc/init.s/mysql* to figure out what to use to start which is also kind of a pain. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Thursday, January 14, 2010 09:44 AM To: tony.chamberl...@lemko.com Cc: mysql@lists.mysql.com Subject: Re: version You *should* be using a package manager (perfectly fine RPMs available for all your needs), but if you must do this, it's a reasonably safe bet to right-align and zero-pad all your number to 4 digits, at which point you're free to concatenate them and treat them as a single number. 14.12.5.0.19 then becomes 0014 0012 0005 0019 which becomes 14001200050019. You'd probably be safe with 3 or maaaybe even two positions, depending on how many releases get done :-) The better-but-more-work way is to compare every number separately, starting with the major release. On Thu, Jan 14, 2010 at 4:21 PM, tony.chamberl...@lemko.com wrote: I have an install script that does some stuff with mysql (i.e. install, start, etc). It installs mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline 5.0 This was good when we just used CentOS 4.5. Now we are doing some later CentOS versions and the mysql version may be higher. I want to do something like mysql --version and process the result and if the version is = 5.0.19 skip the mysql installation and just do the other stuff. I can't compare as it is right now because the . and stuff may screw up the comparison (e.g. ver 5.2 will show as greater than 5.19 eg). I want to know, if I break the individual pieces like 14 12 5 0 19 I can do some sort of calculation to determine a number that I can actually compare. Or can I just remove all the decimal points, like 14.12.5.0.19 becomes 14125019? I might have to make it like 14120050019 or something. What is an algorithm I can use? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: version
On Thu, Jan 14, 2010 at 5:02 PM, tony.chamberl...@lemko.com wrote: The other thing is, mysql appears to keep changing between /etc/init.d/mysqld and /etc/init/mysql so I also have to do an ls /etc/init.s/mysql* to figure out what to use to start which is also kind of a pain. Hmm... I'm no RedHat/CentOS man, but if there's also init.1 through init.6, it should be pretty safe to ignore those - the scripts in there should be symlinks to the init.d ones. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Thursday, January 14, 2010 09:44 AM To: tony.chamberl...@lemko.com Cc: mysql@lists.mysql.com Subject: Re: version You *should* be using a package manager (perfectly fine RPMs available for all your needs), but if you must do this, it's a reasonably safe bet to right-align and zero-pad all your number to 4 digits, at which point you're free to concatenate them and treat them as a single number. 14.12.5.0.19 then becomes 0014 0012 0005 0019 which becomes 14001200050019. You'd probably be safe with 3 or maaaybe even two positions, depending on how many releases get done :-) The better-but-more-work way is to compare every number separately, starting with the major release. On Thu, Jan 14, 2010 at 4:21 PM, tony.chamberl...@lemko.com wrote: I have an install script that does some stuff with mysql (i.e. install, start, etc). It installs mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline 5.0 This was good when we just used CentOS 4.5. Now we are doing some later CentOS versions and the mysql version may be higher. I want to do something like mysql --version and process the result and if the version is = 5.0.19 skip the mysql installation and just do the other stuff. I can't compare as it is right now because the . and stuff may screw up the comparison (e.g. ver 5.2 will show as greater than 5.19 eg). I want to know, if I break the individual pieces like 14 12 5 0 19 I can do some sort of calculation to determine a number that I can actually compare. Or can I just remove all the decimal points, like 14.12.5.0.19 becomes 14125019? I might have to make it like 14120050019 or something. What is an algorithm I can use? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: optimize mysql
its a linux server fedora with 2GB ram, insert mostly used through the update, no load or on cpu during that time mysql show status; +++ | Variable_name | Value | +++ | Aborted_clients| 16 | | Aborted_connects | 7 | | Binlog_cache_disk_use | 1 | | Binlog_cache_use | 1 | | Bytes_received | 327786682 | | Bytes_sent | 2078650970 | | Com_admin_commands | 9725 | | Com_alter_db | 0 | | Com_alter_table| 0 | | Com_analyze| 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 65467 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 1 | | Com_create_db | 0 | | Com_create_function| 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_dealloc_sql| 0 | | Com_delete | 41271 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db| 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 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 | 42096 | | Com_insert_select | 36 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables| 6 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql| 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace| 0 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 2597515| | Com_set_option | 625| | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets | 1 | | Com_show_collations| 0 | | Com_show_column_types | 0 | | Com_show_create_db | 9 | | Com_show_create_table | 818| | Com_show_databases | 3 | | Com_show_errors| 0 | | Com_show_fields| 482| | Com_show_grants| 0 | | Com_show_innodb_status | 8386 | | Com_show_keys | 0 | | Com_show_logs | 0 | | Com_show_master_status | 3 | | Com_show_new_master| 0 | | Com_show_open_tables | 0 | | Com_show_privileges| 0 | | Com_show_processlist | 2 | | Com_show_slave_hosts | 3 | | Com_show_slave_status | 0 | | Com_show_status| 8385 | | Com_show_storage_engines | 0 | | Com_show_tables| 493| | Com_show_variables | 19 | | Com_show_warnings | 0 | | Com_slave_start| 0 | | Com_slave_stop | 0 | | Com_stmt_prepare | 0 | | Com_stmt_execute | 0 | | Com_stmt_send_long_data| 0 | | Com_stmt_reset | 0 | | Com_stmt_close | 0 | | Com_truncate | 0 | | Com_unlock_tables | 6 | | Com_update | 30723 | | Com_update_multi | 0 | | Connections| 65423 | | Created_tmp_disk_tables| 2027 | | Created_tmp_files | 431| | Created_tmp_tables | 647863 | | Delayed_errors | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | |
Re: Ordering field names in a DESC / DESCRIBE table or SHOW COLUMNS from table command
Hi Peter (and MySQL list), On Wed, Jan 13, 2010 I (Ricardo Dias Marques) asked the following : It would be convenient for me to get a list of those fields ordered by field / column name. ... and on the same day, Peter Brawley peter.braw...@earthlink.net kindly replied: SELECT * FROM information_schema.columns WHERE table_schema='db' AND table_name='tbl'; Thank you Peter! With your good example, it became very easy for me to reach a working solution, that was running the following query (replacing database_name and table_name by their real names, obviously): SELECT column_name from INFORMATION_SCHEMA.columns WHERE table_schema='database_name' AND table_name='table_name' ORDER BY column_name; This query works perfectly in the scenario that I described in my original post (Linux server running MySQL 5.0). It does NOT work for MySQL 4.x, however (I have another machine that only has MySQL 4.1.12). For MySQL 4, the best I could do was this: 1 - Run the following mysqlshow command : # mysqlshow database_name table_name -p field_list.txt 2 - Open the field_list.txt in the Vim text editor. I then did a column selection in that file (by pressing CTRL + V to enter Visual Block mode and then selecting and deleting), I removed the columns that I didn't need (basically, removed every column EXCEPT the Field column). I also removed the extra lines that the mysqlshow command adds (decorative lines, column labels, etc...). Then, I saved this changed text file. 3 - Finally, I ran the sort command on that file: # sort field_list.txt Et voilà! Thanks again for helping me Peter! :) Cheers, Ricardo Dias Marques lists AT ricmarques DOT net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SUM() acting funny when joining
Hi, The function is probably behaving as intended, but its confusing the hell out of me. ;) Anyway, say I have two tables; orders and lineitems Orders has two columns: orderid(primary key) and ordertotal Lineitems has two columns: orderid and itemid For every orderid in the orders table, there can be one or more matching rows in the lineitems table. I'm trying to get the sum of all the orders, as well as count the total number of line items with a query like this: SELECT Sum(a.ordertotal) as total, Count(b.itemid) as line_items FROM Orders a LEFT JOIN Lineitems b ON a.orderid = b.orderid What seems to be happening is that MySQL is adding ordertotal multiple times for orders which have multiple line items. Eg, Say there are two orders, both with an order total of $10. I'm expecting MySQL to return $20 for total, and it does when each order only has one line item a piece. However, if the first order has one line item and the second order has two line items, MySQL returns $30 as the total. Is there a way to make MySQL add the ordertotal column only once per unique order in the orders table? TIA -- John C. Nichel IV System Administrator KegWorks http://www.kegworks.com 716.362.9212 x16 j...@kegworks.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
DRAFT 2: MySQL 5.5.1-m2 has been released
Dear MySQL users, MySQL Server 5.5.1-m2, a new version of the popular Open Source Database Management System, has been released. The -m2 suffix tells this belongs to the second milestone according to our milestone release model, also called Betony. You can read more about the release model and the planned milestones at http://forge.mysql.com/wiki/Development_Cycle The new features in this release are of beta quality. As with any other pre-production release, caution should be taken when installing on production level systems or systems with critical data. For production level systems using 5.1, we would like to direct your attention to the product description of MySQL Enterprise at: http://mysql.com/products/enterprise/ MySQL 5.5 is based on MySQL 5.4, which won't get any further updates. So MySQL 5.5 includes several high-impact changes to address scalability and performance issues in MySQL Server. These changes exploit advances in hardware and CPU design and enable better utilization of existing hardware. For an overview of what's new in MySQL 5.5, please see the section What Is New in MySQL 5.5 below, or view it online at http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html For information on installing MySQL 5.5.1-m2 on new servers, please see the MySQL installation documentation at http://dev.mysql.com/doc/refman/5.5/en/installing.html For upgrading from previous MySQL releases, please see the important upgrade considerations at http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html MySQL Server is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ Not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://forge.mysql.com/wiki/Contributing Following the What Is New section, this mail lists the changes in the MySQL source code of MySQL 5.5.1-m2. The list of all Bugs Fixed may also be viewed online at http://dev.mysql.com/doc/refman/5.5/en/news-5-5-1.html When the publishing process for 5.5.1-m2 was already running, the MySQL team was informed about a security problem in the SSL connect area (a possibility to crash the server). The problem is caused by a buffer overflow in the YaSSL library, MySQL Servers using OpenSSL are not affected. It can only occur when SSL (using YaSSL) is enabled. This problem is still under detailed investigation with the various versions, configurations, and platforms. When that has finished, the problem will be fixed ASAP, and new binaries for the affected versions will be released. Obviously, building and testing these binaries in the various configurations on the various platforms will take some time. The bug is tracked with a CVE ID already: http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2009-4484 The related bug report is currently marked private, it will be made public once the new binaries are out: http://bugs.mysql.com/50227 In the meantime, we repeat the general security hint: If it is not *absolutely* necessary that external machines can connect to your database instance, we recommend that the server's connection port be blocked by a firewall to prevent any such illegitimate accesses. Enjoy! On behalf of the MySQL Build Team at Sun Microsystems: Jörg Brühe, Senior Production Engineer - What Is New in MySQL 5.5 The following features have been added to MySQL 5.5: * Support for an interface for semisynchronous replication: A commit performed on the master side blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction. Semisynchronous replication is implemented through an optional plugin component. See Section 16.2.8, Semisynchronous Replication * Support for the SQL standard SIGNAL and RESIGNAL statements. See Section 12.8.8, SIGNAL and RESIGNAL. * Enhancements to XML functionality, including a new LOAD XML statement. * Two new types of user-defined partitioning: RANGE COLUMNS partitioning is an extension to RANGE partitioning; LIST COLUMNS partitioning is an extension to LIST partitioning. Each of these extensions provides two enhancements to MySQL partitioning capabilities: 1. It is possible to define partitioning ranges or lists based on DATE, DATETIME, or string values (such as CHAR or VARCHAR). You can also define ranges or lists based on multiple column values when partitioning tables by RANGE COLUMNS or LIST COLUMNS, respectively. Such a range or list may refer to up to 16 columns. 2. For tables defined using these partitioning types, partition pruning can now optimize queries with WHERE conditions
Re: MySQL 5.5.1-m2 has been released
Dear MySQL users, I made a very silly mistake: My previous mail entitled DRAFT is really final and valid, I forgot to change the subject after the internal review cycle. I ask you all to apologize that mistake and and hope you will excuse any confusion I may have created. On behalf of the MySQL Build Team at Sun Microsystems: Jörg Brühe, Senior Production Engineer -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel 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/mysql?unsub=arch...@jab.org
converting non-materialized view to a table?
Hello, I need to convert a non-materialized MySQL view to a MySQL table. Are there any tools to do that? Thanks, Jacek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: converting non-materialized view to a table?
At 04:55 PM 1/14/2010, Jacek Becla wrote: Hello, I need to convert a non-materialized MySQL view to a MySQL table. Are there any tools to do that? Thanks, Jacek Jacek, Can't you just do a: create table mytable select * from myview; ??? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SUM() acting funny when joining
John, What's happening is that the tables do not have a one-to-one relationship, so the JOIN duplicates rows from Orders to match the rows in Lineitems. You need to ensure the aggregation is consistent across the two datasets. Try this: SELECT Sum(a.ordertotal) as total, line_items FROM Orders a LEFT JOIN ( SELECT orderid, COUNT(*) AS line_items FROM Lineitems GROUP BY orderid ) AS b ON a.orderid = b.orderid This may not be very efficient because the subquery in the FROM clause will result in a temporary table without indexes. - Baron On Thu, Jan 14, 2010 at 5:09 PM, John Nichel jnic...@kegworks.com wrote: Hi, The function is probably behaving as intended, but its confusing the hell out of me. ;) Anyway, say I have two tables; orders and lineitems Orders has two columns: orderid(primary key) and ordertotal Lineitems has two columns: orderid and itemid For every orderid in the orders table, there can be one or more matching rows in the lineitems table. I'm trying to get the sum of all the orders, as well as count the total number of line items with a query like this: SELECT Sum(a.ordertotal) as total, Count(b.itemid) as line_items FROM Orders a LEFT JOIN Lineitems b ON a.orderid = b.orderid What seems to be happening is that MySQL is adding ordertotal multiple times for orders which have multiple line items. Eg, Say there are two orders, both with an order total of $10. I'm expecting MySQL to return $20 for total, and it does when each order only has one line item a piece. However, if the first order has one line item and the second order has two line items, MySQL returns $30 as the total. Is there a way to make MySQL add the ordertotal column only once per unique order in the orders table? TIA -- John C. Nichel IV System Administrator KegWorks http://www.kegworks.com 716.362.9212 x16 j...@kegworks.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ba...@xaprb.com -- Baron Schwartz Percona Inc: Services and Support for MySQL http://www.percona.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: When using FOR UPDATE whole the table seems to lock instead of selected row
Johan, I don't see a valid need for using FOR UPDATE here. In fact, FOR UPDATE is the cause of many grievances, and I would advise you to avoid it by any means possible. Among other things, it will cause serious performance problems when your server gets busy. And as you can see, it's hard to figure out why it behaves as it does. Simply issue the UPDATE statement without a SELECT first. - Baron On Thu, Jan 14, 2010 at 4:08 AM, Johan Machielse johan.machie...@kpnplanet.nl wrote: Hi, I have created a query to read and update a stock item by using the FOR UPDATE statement. According to the MySql documention only the rows that are selected using the FOR UPDATE should be locked for other sessions, but somehow whole the table is locked. This post gives some general information and then shows 3 scenarios. The first 2 scenarios work as expected, but the last one fails. Two questions: a.. Does anyone has an idea why scenario 3 does not work as expected and what is the solution to make it work? b.. Is this the preferred way to update a stock table? If not, could you provide me an example how it should be done? Here some clarification about the problem: Version MySQL 5.0.45-community-nt Stock table DROP TABLE IF EXISTS `mydatabase`.`stock`; CREATE TABLE `mydatabase`.`stock` ( `ID` bigint(20) unsigned NOT NULL auto_increment, `ProductID` bigint(20) unsigned NOT NULL, `SizeID` bigint(20) unsigned NOT NULL, `Quantity` int(11) NOT NULL, PRIMARY KEY USING BTREE (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; MySQL example This query updates the stock of one product of a particular size by decrementing it's quantity by 1. START TRANSACTION; SET AUTOCOMMIT=0; SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 2 FOR UPDATE; UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1 AND Stock.SizeID = 2; COMMIT; Now let me show you three scenarios wherein you can see that using the FOR UPDATE statement is not working as it should. Scenario 1 (works as expected) I opened two sessions of MySql Query Browser and each executing the same MySql query. So, both looking at the same row (same selection criteria). START TRANSACTION; SET AUTOCOMMIT=0; SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 2 FOR UPDATE; UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1 AND Stock.SizeID = 2; COMMIT; 1) Session 1: START TRANSACTION; 2) Session 1: SET AUTOCOMMIT=0; 3) Session 1: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 2 FOR UPDATE; (returns Stock.Quantity as expected) 4) Session 2: START TRANSACTION; 5) Session 2: SET AUTOCOMMIT=0; 6) Session 2: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 2 FOR UPDATE; (blocks as exepected) 7) Session 1: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1 AND Stock.SizeID = 2; 8) Session 1: COMMIT; (the blocking step 6 is now executed and returns the updated Stock.Quantity as exepected) 9) Session 2: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1 AND Stock.SizeID = 2; 10) Session 2: COMMIT; Scenario 2 (works as expected) I opened two sessions of MySql Query Browser and each executing the same MySql query. Only the selection criteria are different; both are using another value for the primary key ID. When I use different values for the primary key ID I have the same results as in scenario 1. Session 1 query START TRANSACTION; SET AUTOCOMMIT=0; SELECT Stock.Quantity FROM Stock WHERE Stock.ID = 1 FOR UPDATE; UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ID = 1; COMMIT; Session 2 query START TRANSACTION; SET AUTOCOMMIT=0; SELECT Stock.Quantity FROM Stock WHERE Stock.ID = 2 FOR UPDATE; UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ID = 2; COMMIT; 1) Session 1: START TRANSACTION; 2) Session 1: SET AUTOCOMMIT=0; 3) Session 1: SELECT Stock.Quantity FROM Stock WHERE Stock.ID = 1 FOR UPDATE; (returns Stock.Quantity as expected) 4) Session 2: START TRANSACTION; 5) Session 2: SET AUTOCOMMIT=0; 6) Session 2: SELECT Stock.Quantity FROM Stock WHERE Stock.ID = 2 FOR UPDATE; (no blocking as exepected, because it's another row; returns Stock.Quantity as expected) 7) Session 1: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ID = 1; 8) Session 1: COMMIT; 9) Session 2: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ID = 2; 10) Session 2: COMMIT; Scenario 3 (does not work as expected) I opened two sessions of MySql Query Browser and each executing the same MySql query. Only the selection criteria are different; both are using another value for the non-primary key
Better that `NOT IN`
Hi guys i have a problem, 3 big tables: item_instance about 15KK rows, character_inventory 15KK rows, guild_bank_item 2KK rows. And i need i clean on item_instance how this query: DELETE FROM `item_instance` WHERE guid NOT IN(SELECT item FROM `character_inventory`) AND guid NOT IN(SELECT item_guid FROM `guild_bank_item`) AND guid NOT IN(SELECT item_guid FROM `mail_items`) and guid NOT IN(SELECT itemguid FROM `auctionhouse`); Well atm is running about 13 hours, State = Sending Data. I will be a better option ? Thanks all ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Better that `NOT IN`
For alternatives, have a look at The unbearable slowness of IN() at http://www.artfulsoftware.com/queries.php. PB - Junior Ortis wrote: Hi guys i have a problem, 3 big tables: item_instance about 15KK rows, character_inventory 15KK rows, guild_bank_item 2KK rows. And i need i clean on item_instance how this query: DELETE FROM `item_instance` WHERE guid NOT IN(SELECT item FROM `character_inventory`) AND guid NOT IN(SELECT item_guid FROM `guild_bank_item`) AND guid NOT IN(SELECT item_guid FROM `mail_items`) and guid NOT IN(SELECT itemguid FROM `auctionhouse`); Well atm is running about 13 hours, State = Sending Data. I will be a better option ? Thanks all ! No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.432 / Virus Database: 270.14.139/2620 - Release Date: 01/14/10 07:35:00
Re: When using FOR UPDATE whole the table seems to lock instead of selected row
Hi Baron, Thank you for your answer. The problem is that multiple users can read and update the same field simultaneously (worse case) which could lead to unpredictable problems. According to the MySql online documentation (http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html) this is the way to solve this problem. What I really want is the following: When person A is reading and updating a field value, person B should not be able to do this simultaneously. Person B has to wait till the Person A has finished his work. Are there other more robust solutions to solve such a problem? Thank you in advance. Regards, Johan Machielse - Original Message - From: Baron Schwartz ba...@xaprb.com To: Johan Machielse johan.machie...@kpnplanet.nl Cc: mysql@lists.mysql.com Sent: Friday, January 15, 2010 3:14 AM Subject: Re: When using FOR UPDATE whole the table seems to lock instead of selected row Johan, I don't see a valid need for using FOR UPDATE here. In fact, FOR UPDATE is the cause of many grievances, and I would advise you to avoid it by any means possible. Among other things, it will cause serious performance problems when your server gets busy. And as you can see, it's hard to figure out why it behaves as it does. Simply issue the UPDATE statement without a SELECT first. - Baron On Thu, Jan 14, 2010 at 4:08 AM, Johan Machielse johan.machie...@kpnplanet.nl wrote: Hi, I have created a query to read and update a stock item by using the FOR UPDATE statement. According to the MySql documention only the rows that are selected using the FOR UPDATE should be locked for other sessions, but somehow whole the table is locked. This post gives some general information and then shows 3 scenarios. The first 2 scenarios work as expected, but the last one fails. Two questions: a.. Does anyone has an idea why scenario 3 does not work as expected and what is the solution to make it work? b.. Is this the preferred way to update a stock table? If not, could you provide me an example how it should be done? Here some clarification about the problem: Version MySQL 5.0.45-community-nt Stock table DROP TABLE IF EXISTS `mydatabase`.`stock`; CREATE TABLE `mydatabase`.`stock` ( `ID` bigint(20) unsigned NOT NULL auto_increment, `ProductID` bigint(20) unsigned NOT NULL, `SizeID` bigint(20) unsigned NOT NULL, `Quantity` int(11) NOT NULL, PRIMARY KEY USING BTREE (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; MySQL example This query updates the stock of one product of a particular size by decrementing it's quantity by 1. START TRANSACTION; SET AUTOCOMMIT=0; SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 2 FOR UPDATE; UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1 AND Stock.SizeID = 2; COMMIT; Now let me show you three scenarios wherein you can see that using the FOR UPDATE statement is not working as it should. Scenario 1 (works as expected) I opened two sessions of MySql Query Browser and each executing the same MySql query. So, both looking at the same row (same selection criteria). START TRANSACTION; SET AUTOCOMMIT=0; SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 2 FOR UPDATE; UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1 AND Stock.SizeID = 2; COMMIT; 1) Session 1: START TRANSACTION; 2) Session 1: SET AUTOCOMMIT=0; 3) Session 1: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 2 FOR UPDATE; (returns Stock.Quantity as expected) 4) Session 2: START TRANSACTION; 5) Session 2: SET AUTOCOMMIT=0; 6) Session 2: SELECT Stock.Quantity FROM Stock WHERE Stock.ProductID = 1 AND Stock.SizeID = 2 FOR UPDATE; (blocks as exepected) 7) Session 1: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1 AND Stock.SizeID = 2; 8) Session 1: COMMIT; (the blocking step 6 is now executed and returns the updated Stock.Quantity as exepected) 9) Session 2: UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ProductID = 1 AND Stock.SizeID = 2; 10) Session 2: COMMIT; Scenario 2 (works as expected) I opened two sessions of MySql Query Browser and each executing the same MySql query. Only the selection criteria are different; both are using another value for the primary key ID. When I use different values for the primary key ID I have the same results as in scenario 1. Session 1 query START TRANSACTION; SET AUTOCOMMIT=0; SELECT Stock.Quantity FROM Stock WHERE Stock.ID = 1 FOR UPDATE; UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ID = 1; COMMIT; Session 2 query START TRANSACTION; SET AUTOCOMMIT=0; SELECT Stock.Quantity FROM Stock WHERE Stock.ID = 2 FOR UPDATE; UPDATE Stock SET Stock.Quantity = Stock.Quantity - 1 WHERE Stock.ID = 2; COMMIT; 1) Session 1: START TRANSACTION; 2) Session 1: SET AUTOCOMMIT=0; 3) Session 1: SELECT Stock.Quantity FROM Stock
