RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect?
An update - it also occurs in 4.1.19 Bug filed here: http://bugs.mysql.com/bug.php?id=19978 Cheers, Richard. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INESRT ... ON DUPLICATE KEY - rows affected incorrect?
Mysql 4.1.16 running on Fedora Core 4 (Linu) X86_64 with InnoDB tables I have a table called which has a (combined) primary key of assetid and date plus several data fields (open, high, low, close - all doubles, and voume - a bigint). See the bottom of the email for the table definition. INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE KEY UPDATE open='334.25',high='334.25',low='334.25',close='334.25',volume='1'; The first time around, the insert works fine: Query OK, 1 row affected (0.03 sec); The second time around, when there should be neither an insert nor an update that affects any rows I get: Query OK, 2 rows affected (0.05 sec) To check that the update wasn't in fact updating any rows, I tried: UPDATE price SET open='334.25',high='334.25',low='334.25',close='334.25',volume='1' WHERE assetid=202690 AND date='2006-05-18'; Query OK, 0 rows affected (0.02 sec) Rows matched: 1 Changed: 0 Warnings: 0 So, it appears that the rows affected returned by an INSERT ... ON DUPLICATE KEY is incorrect. Can anyone else confirm that this is the correct behaviour? Thanks, Richard. - Table definition: CREATE TABLE `price` ( `assetid` int(11) NOT NULL default '0', `date` date NOT NULL default '-00-00', `open` double default NULL, `high` double default NULL, `low` double default NULL, `close` double default NULL, `volume` bigint(20) default NULL, PRIMARY KEY (`assetid`,`date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect?
Most likely, MySQL has done a 'DELETE' followed by an 'INSERT' - i.e. two rows. According to: http://dev.mysql.com/doc/refman/4.1/en/insert-on-duplicate.html The rows-affected value is 1 if the row is inserted as a new record and 2 if an existing record is updated. But what happens if the row is neither inserted nor updated? Right now it appears to returns '2'. I think it should return '0'. Cheers, Richard. -Original Message- From: Richard Dale [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 11:23 a.m. To: mysql@lists.mysql.com Subject: INESRT ... ON DUPLICATE KEY - rows affected incorrect? Mysql 4.1.16 running on Fedora Core 4 (Linu) X86_64 with InnoDB tables I have a table called which has a (combined) primary key of assetid and date plus several data fields (open, high, low, close - all doubles, and voume - a bigint). See the bottom of the email for the table definition. INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE KEY UPDATE open='334.25',high='334.25',low='334.25',close='334.25',volume='1'; The first time around, the insert works fine: Query OK, 1 row affected (0.03 sec); The second time around, when there should be neither an insert nor an update that affects any rows I get: Query OK, 2 rows affected (0.05 sec) To check that the update wasn't in fact updating any rows, I tried: UPDATE price SET open='334.25',high='334.25',low='334.25',close='334.25',volume='1' WHERE assetid=202690 AND date='2006-05-18'; Query OK, 0 rows affected (0.02 sec) Rows matched: 1 Changed: 0 Warnings: 0 So, it appears that the rows affected returned by an INSERT ... ON DUPLICATE KEY is incorrect. Can anyone else confirm that this is the correct behaviour? Thanks, Richard. - Table definition: CREATE TABLE `price` ( `assetid` int(11) NOT NULL default '0', `date` date NOT NULL default '-00-00', `open` double default NULL, `high` double default NULL, `low` double default NULL, `close` double default NULL, `volume` bigint(20) default NULL, PRIMARY KEY (`assetid`,`date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect?
Thanks Quentin... Further to this, I tried changing the types of the fields from DOUBLE to: DECIMAL(10,2) and also VARCHAR(20) to avoid any issues with precision The query still returns back Query OK, 2 rows affected (0.05 sec) when the fields are DECIMAL or VARCHAR too. Might be time to file a bug report. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -Original Message- From: Quentin Bennett [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 12:41 PM To: Richard Dale; mysql@lists.mysql.com Subject: RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect? Its not a problem with precision is it - 332.25 looks OK to you and me, but who knows what it looks like to the CPU? Maybe it sees one of the doubles as slightly different and updates? -Original Message- From: Richard Dale [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 11:48 a.m. To: mysql@lists.mysql.com Subject: RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect? Most likely, MySQL has done a 'DELETE' followed by an 'INSERT' - i.e. two rows. According to: http://dev.mysql.com/doc/refman/4.1/en/insert-on-duplicate.html The rows-affected value is 1 if the row is inserted as a new record and 2 if an existing record is updated. But what happens if the row is neither inserted nor updated? Right now it appears to returns '2'. I think it should return '0'. Cheers, Richard. -Original Message- From: Richard Dale [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 11:23 a.m. To: mysql@lists.mysql.com Subject: INESRT ... ON DUPLICATE KEY - rows affected incorrect? Mysql 4.1.16 running on Fedora Core 4 (Linu) X86_64 with InnoDB tables I have a table called which has a (combined) primary key of assetid and date plus several data fields (open, high, low, close - all doubles, and voume - a bigint). See the bottom of the email for the table definition. INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE KEY UPDATE open='334.25',high='334.25',low='334.25',close='334.25',volume='1'; The first time around, the insert works fine: Query OK, 1 row affected (0.03 sec); The second time around, when there should be neither an insert nor an update that affects any rows I get: Query OK, 2 rows affected (0.05 sec) To check that the update wasn't in fact updating any rows, I tried: UPDATE price SET open='334.25',high='334.25',low='334.25',close='334.25',volume='1' WHERE assetid=202690 AND date='2006-05-18'; Query OK, 0 rows affected (0.02 sec) Rows matched: 1 Changed: 0 Warnings: 0 So, it appears that the rows affected returned by an INSERT ... ON DUPLICATE KEY is incorrect. Can anyone else confirm that this is the correct behaviour? Thanks, Richard. - Table definition: CREATE TABLE `price` ( `assetid` int(11) NOT NULL default '0', `date` date NOT NULL default '-00-00', `open` double default NULL, `high` double default NULL, `low` double default NULL, `close` double default NULL, `volume` bigint(20) default NULL, PRIMARY KEY (`assetid`,`date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Linux Yum DELETED mysql 4! Can I install mysql 5 and be on my way?
MySql v3.23 is part of the Fedore Core 3 distribution. MySQL v4.1 only became standard in Fedora Core 4. I suspect it's one of the non-Fedora repositories you're using. Check your logs and also check /etc/yum.repos.d grep enabled /etc/yum/repos.d/* Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- Hi; On FC3, for some silly reason, Yum deleted my mysql 4.1x production server! I do have my data. Now that I am in this hole, maybe I should go to mysql 5. Can I install mysql 5, and it will migrate/work with my existing data? Thanks, -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: default table type = innodb is stable??
this is my error log file. InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes InnoDB: than specified in the .cnf file 0 268435456 bytes! Your problem here is that you must have started MySQL when you had a 5MB transaction log file specified, but you then put a new config file online with a different log file size (256MB), InnoDB complains and dies. You need to delete your old transaction log files (ib_logfile0) then start MySQL and all should be fine. Note: You should only delete your log files when you are absolutely sure MySQL has shut down OK. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1 on 64bit Fedora Core 3
The OS installed is the 64bit version of FC3. I have upgraded MySQL from 3.23 to 4.1 and I have installed the shared library. (MySQL-shared-4.1.14-0.glibc23.x86_64.rpm) The problem is that PHP complains that it cannot find 'libmysqlclient.so.10'. I think you need the shared-compat libraries - it appears that they are no provided on mysql.com As an alternative, consider using Fedora Core 4 which has Mysql 4.1 in there natively. The following might also be helpful (not all info in there is Opteron-specific): http://hashmysql.org/index.php?title=Opteron_HOWTO Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1 on 64bit Fedora Core 3
The following might also be helpful (not all info in there is Opteron-specific): http://hashmysql.org/index.php?title=Opteron_HOWTO Argh - should have read my own entry on that wiki page. If you use PHP v4.3 (as installed with FC3) then it requires the MySQL v3.23 interface libraries (aka libmysqlclient.so.10). By removing the pre-installed MySQL it will remove these libraries. As of 31 May 2005 For some reason these were not compiled into the MySQL 4.1.12 shared library RPMs as downloaded from the MySQL site. However, they are available from any Fedora mirror in the FC4 testing area. You will need to install a package called mysqlclient10-3.23.58-6.x86_64.rpm - I found such a package at my local mirror here: http://mirror.pacific.net.au/linux/redhat/fedora/test/3.92/x86_64/os/Fedora/ RPMS/mysqlclient10-3.23.58-6.x86_64.rpm Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Continued x86_64 install problems
Hi Anne, I managed to install MySQL-client-4.0.25-0.ia64.rpm MySQL-server-4.0.25-0.ia64.rpm MySQL-devel-4.0.25-0.ia64.rpm ... x86_64 != ia64 You have installed the wrong architecture executables. Try downloading the x86_64 versions instead. Also have a read of our Opteron (x86_64) HOWTO: http://hashmysql.org/index.php?title=Opteron_HOWTO Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysqld chewing up cpu in the background.
I am fairly sure that there aren't any queries being run, but while in the background, my mysqld process chews up exactly 50% of my cpu. It runs queries nicely and has excellent response times for most any query I throw at it, but its causing issues for other apps. Try using mytop to see if there are queries going on. It's like the unix top command. http://jeremy.zawodny.com/mysql/mytop/ Also try: SHOW PROCESSLIST; If you use InnoDB: show innodb status; Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: AMD64 Linux MySQL Error 1041 Out of Memory
So we have recently started stress testing Mysql on an Opteron dual CPU machine running Ubuntu Hoary. We are using the 64-bit GCC 4.0.24-standard binary from mysql. The stress test that I'm currently running on it involves inserting a large database (from a mysqldump) ... Which linux kernel are you running? Why not consider MySQL 4.1? It's now a stable production release. Also, consider visiting: http://hashmysql.org/index.php?title=Opteron_HOWTO We would welcome a section on there about Ubuntu and x86-64. Also, perhaps this bug is related: http://bugs.mysql.com/bug.php?id=10829 Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysqldump
I took a dump of (pretty chunk) database, the output is close to 45G. I am trying to reload this dump file onto a development server but it's taking long time to load the database. Is their a faster way to load the data in? I am using plain and simple mysql dump.sql syntax on a Mysql 0.24 server. Make sure you use the -e (extended insert) option on your mysqldump - this speeds things up tremendously. Also, make sure you use -q on your mysqldump so it doesn't buffer the results in RAM during the dump. I did a ~10GB dump and it took around 2 hours to load on an Opteron 250, 4GB RAM, 8x15K RPM RAID 10 drive system. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: email notification
If you use MyISAM tables you could always do: SHOW TABLE STATUS LIKE 'tablename' Then look at the Update_time column. If that has changed since last time then something has changed. Then implement a php/perl/whatever script around it to email you. Note: On my InnoDB tables this time seems to be quite often NULL, so I guess it's not implemented in InnoDB. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -Original Message- From: Jayson [mailto:[EMAIL PROTECTED] Sent: Tuesday, 31 May 2005 23:59 To: mysql@lists.mysql.com Subject: email notification I'm looking for a feature in mysql where it will email me if ther are any changes in a particular database or table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL not using optimum disk throughput.
I've added a fair bit of information on the Opteron HOWTO Wiki at: http://hashmysql.org/index.php?title=Opteron_HOWTO for using Fedora Core 3 with X86-64. In my performance testing, I was finding that with so much RAM, everything was coming from RAM anyway. RAID10 seemed to be most stable, and writeback caching increased the speed by about 15% although I don't think I was really able to max out the IO anyway. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Opteron HOWTO - #mysql Freenode
Over the last week I added in lots of comments pasted in from various places. I'd appreciate those running with Opteron and MySQL to have a close look at the WIKI and make any amendments/suggestions. http://hashmysql.org/index.php?title=Opteron_HOWTO My Opteron server will be here shortly and I'll do some performance testing on RAID5 (8 disks) versus RAID 10 (8 disks, 2 channels). I'll also do some performance tests with 128MB cache and 512MB cache on the SCSI card to let you know if that makes a difference. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dual Opteron, linux kernels, 64 bit, mysql 4.1, InnoDB
A new server is about to arrive here and will have have 8x15K RPM spindles, dual Opteron processors and 4GB of RAM, and will have around 100GB of database (primarily stock market prices) - the SCSI controller will also have battery-backed RAM too. InnoDB will be used exclusively. I've searched the list and seen varying reports of which Linux kernels work best etc. I'd be intersted to know the following: a) Which 64 bit Linux distributions are good for the task? b) Which 64 bit Linux distributions are bad for the task? c) Any comments on kernels, particularly with regard to 64 bit support and schedulers? Any problems with the latest kernels (2.6.11 2.6.12-rcX)? d) Any recommendations for RAID volume setup e) Any MySQL optimisations for multiple spindles, onboard caching, stripe sizes, RAID5 vs RAID10. f) Any MySQL reliability settings to take into account the battery-backed RAM on the RAID controller? I'm happy to collate the responses into a summary too. I'm aware of the following discussions which describes a reasonably grunty Dual AMD system with a similar configuration to mine: http://meta.wikimedia.org/wiki/Hardware_ordered_April_2005 http://meta.wikimedia.org/wiki/Hardware_order_May_2004 Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RAID, MySQL and SATA - benchmarks
Tweakers.net has completed a comparison of 9 serial ATA RAID 0/1/5/10 controllers at: http://www.tweakers.net/reviews/557 There is a specific section on MySQL performance in the section: http://www.tweakers.net/reviews/557/25 Just thought these articles would be of interest to some (it's interesting to see the difference between single drive operations and multiple drive operations - up to 12 drives, with the different RAID levels). Here's my rough speed comparison based upon eyeballing the graphs. Some controllers were better than others so this represents a rough average of the entire set of controllers: Single drive - 1.0 RAID 1 - 2 disks - 1.4 RAID5 - 3 disks - 1.7 RAID5 - 4 disks - 2.0 RAID10 - 4 disks - 2.0 RAID5 - 6 disks - 2.3 RAID5 - 8 disks - 2.4 RAID5 - 10 disks - 2.9 RAID5 - 12 disks - 3.1 The article also highlighted the difference between the reliable write-through mode and the write-back mode. In write-through mode, performance is degraded by approximately 50%. Clearly if you want reliability, a controller with a battery backup is highly recommended. On the issue of SCSI version SATA performance, it would appear that SCSI still performas somewhat better (about 20% more transactions but the test was comparing 15K RPM SCSI drives to 10K RPM SATA drives) but the reduced cost of SATA drives allows you to add more drives to achieve the same performance levels at lesser cost. With Serial ATA II drives around the corner (with Native Command Queueing) then I think we'll find SATA will take a much bigger lead in database performance. Really nice work from tweakers.net - would have been interesting to see the Linux performance too though. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4.1.1, InnoDB - Slow TRUNCATE operations with Multiple Tablespaces
Hi everybody, I'm experiencing that the command TRUNCATE TABLE with InnoDB takes an extraordinary amount of time on large ( 400MB) tables - ie about 3-4 minutes. In comparison, performing a DROP TABLE then recreating it takes about 2 seconds. When the truncate operation is performed from a PERL script with DBI, this usually results in a lost connection to databse error which is rather frustrating. I am using the new multiple tablespaces feature since during my development I'll be truncating the tables a lot (perhaps this was a bad move?) Has anyone else experienced such slow TRUNCATE operations? Here's a summary of my environment: Platform: Mysql 4.1.1-aplha, InnoDB Noteable parameters: innodb_file_per_table, 160MB innodb buffer pool Hardware: Win XP, 3Ghz P4 (HT), 1GB RAM, multiple 7200RPM drives Database location: On its own database on its own hard drive. Swapping: WinXP reporting commit charge 800MB Note: I'll be moving to a Linux-based development server soon and will be able to tell whether the above is specific to Windows or Linux. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Europe, UK USA - http://www.premiumdata.net/ www.premiumdata.net
