Re: Replication update bug/error/problem.
Eric Bergen wrote: Jay, Are you using the replicate-do-db option on the slave? This option relies on 'use' being set correctly when the query is issued. A quote from the manual explains it better than I can: Tells the slave to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database. Note that this will not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database URL:http://dev.mysql.com/doc/mysql/en/Replication_Options.html Other possibilities are to use show slave status; and show master status; to make sure queries are actually being sent from the master to the slave. I am not using cross database updates. It is all on one database but the update uses two tables. The query update content_review_site as a,site_rating_factors as b set a.overall_rating = 77 where a.content_id=243 is a stripped down version of a bigger but i stripped down to the point of failing. The failing factor is when i use content_review_site as a,site_rating_factors as b (not a cross database but a cross table query). And i am using replicate-do-table on both the tables in the query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication update bug/error/problem.
On Dec 8, 2004, at 9:12 AM, Jay Ess wrote: I am not using cross database updates. It is all on one database but the update uses two tables. The query update content_review_site as a,site_rating_factors as b set a.overall_rating = 77 where a.content_id=243 is a stripped down version of a bigger but i stripped down to the point of failing. The failing factor is when i use content_review_site as a,site_rating_factors as b (not a cross database but a cross table query). I wonder if you're running into this bug http://bugs.mysql.com/bug.php?id=7011 We saw it on 4.0.22 and Mac OS X. MySQL has been able to reproduce it, but only on OS X, not Linux. The query works on the master, makes it into the binlog but doesn't update and records on the slave (and doesn't cause replication failure). When the query is run manually on the slave from the command line it works fine, the records are updated as on the master. However cross table updates won't run on the slave under replication. We had to downgrade to 4.0.21. Good luck, Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication update bug/error/problem.
I have a problem with an update query not replicating through to the slave. The query is update content_review_site as a,site_rating_factors as b set a.overall_rating = 77 where a.content_id=243 Version : 4.0.22 OS : Linux X86 How to replicate the error. CREATE TABLE content_review_site ( content_id int(11) unsigned NOT NULL default '0', site_id int(10) unsigned NOT NULL default '0', overall_rating float(4,2) unsigned NOT NULL default '0.00', rating_software_and_graphics tinyint(4) unsigned NOT NULL default '0', rating_game_variety tinyint(4) unsigned NOT NULL default '0', rating_maximum_bonus tinyint(4) unsigned NOT NULL default '0', rating_bonus_match tinyint(4) unsigned NOT NULL default '0', rating_wagering_requirements tinyint(4) unsigned NOT NULL default '0', rating_payout_ratio tinyint(4) unsigned NOT NULL default '0', rating_multiplayer_games tinyint(4) unsigned NOT NULL default '0', rating_loyalty_bonus tinyint(4) unsigned NOT NULL default '0', rating_deposit_withdrawal_options tinyint(4) unsigned NOT NULL default '0', rating_support tinyint(4) unsigned NOT NULL default '0', rating_progressive_jackpots tinyint(4) unsigned NOT NULL default '0', rating_languages tinyint(4) unsigned NOT NULL default '0', rating_reputation_fairness tinyint(4) unsigned NOT NULL default '0', rating_free_bonus tinyint(4) unsigned NOT NULL default '0', create_ts datetime NOT NULL default '-00-00 00:00:00', modify_ts datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (content_id,site_id) ) TYPE=MyISAM; CREATE TABLE site_rating_factors ( site_id int(11) unsigned NOT NULL default '0', factor_software_and_graphics tinyint(4) unsigned NOT NULL default '0', factor_game_variety tinyint(4) unsigned NOT NULL default '0', factor_bonus_amount tinyint(4) unsigned NOT NULL default '0', factor_bonus_clauses tinyint(4) unsigned NOT NULL default '0', factor_payout_ratio tinyint(4) unsigned NOT NULL default '0', factor_multiplayer_games tinyint(4) unsigned NOT NULL default '0', factor_loyalty_bonus tinyint(4) unsigned NOT NULL default '0', factor_deposit_withdrawal_options tinyint(4) unsigned NOT NULL default '0', factor_support tinyint(4) unsigned NOT NULL default '0', factor_graphics tinyint(4) unsigned NOT NULL default '0', factor_progressive_jackpots tinyint(4) unsigned NOT NULL default '0', factor_languages tinyint(4) unsigned NOT NULL default '0', factor_reputation_fairness tinyint(4) unsigned NOT NULL default '0', factor_free_bonus tinyint(4) unsigned NOT NULL default '0', create_ts datetime NOT NULL default '-00-00 00:00:00', modify_ts datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (site_id) ) TYPE=MyISAM; insert into site_rating_factors values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now(),now()); insert into content_review_site values (243,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now(),now()); On the slave get the data with load data from master. Both slave and master shows : +++ | content_id | overall_rating | +++ |243 | 1.00 | +++ update content_review_site set overall_rating = 666; updates the tables just fine on both slave and master. But the troubling query below does NOT it only updates on the master. update content_review_site as a,site_rating_factors as b set a.overall_rating = 77 where a.content_id=243; Any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication update bug/error/problem.
Jay, Are you using the replicate-do-db option on the slave? This option relies on 'use' being set correctly when the query is issued. A quote from the manual explains it better than I can: Tells the slave to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database. Note that this will not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database URL:http://dev.mysql.com/doc/mysql/en/Replication_Options.html Other possibilities are to use show slave status; and show master status; to make sure queries are actually being sent from the master to the slave. -Eric On Wed, 08 Dec 2004 00:57:42 +0100, Jay Ess [EMAIL PROTECTED] wrote: I have a problem with an update query not replicating through to the slave. The query is update content_review_site as a,site_rating_factors as b set a.overall_rating = 77 where a.content_id=243 Version : 4.0.22 OS : Linux X86 How to replicate the error. CREATE TABLE content_review_site ( content_id int(11) unsigned NOT NULL default '0', site_id int(10) unsigned NOT NULL default '0', overall_rating float(4,2) unsigned NOT NULL default '0.00', rating_software_and_graphics tinyint(4) unsigned NOT NULL default '0', rating_game_variety tinyint(4) unsigned NOT NULL default '0', rating_maximum_bonus tinyint(4) unsigned NOT NULL default '0', rating_bonus_match tinyint(4) unsigned NOT NULL default '0', rating_wagering_requirements tinyint(4) unsigned NOT NULL default '0', rating_payout_ratio tinyint(4) unsigned NOT NULL default '0', rating_multiplayer_games tinyint(4) unsigned NOT NULL default '0', rating_loyalty_bonus tinyint(4) unsigned NOT NULL default '0', rating_deposit_withdrawal_options tinyint(4) unsigned NOT NULL default '0', rating_support tinyint(4) unsigned NOT NULL default '0', rating_progressive_jackpots tinyint(4) unsigned NOT NULL default '0', rating_languages tinyint(4) unsigned NOT NULL default '0', rating_reputation_fairness tinyint(4) unsigned NOT NULL default '0', rating_free_bonus tinyint(4) unsigned NOT NULL default '0', create_ts datetime NOT NULL default '-00-00 00:00:00', modify_ts datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (content_id,site_id) ) TYPE=MyISAM; CREATE TABLE site_rating_factors ( site_id int(11) unsigned NOT NULL default '0', factor_software_and_graphics tinyint(4) unsigned NOT NULL default '0', factor_game_variety tinyint(4) unsigned NOT NULL default '0', factor_bonus_amount tinyint(4) unsigned NOT NULL default '0', factor_bonus_clauses tinyint(4) unsigned NOT NULL default '0', factor_payout_ratio tinyint(4) unsigned NOT NULL default '0', factor_multiplayer_games tinyint(4) unsigned NOT NULL default '0', factor_loyalty_bonus tinyint(4) unsigned NOT NULL default '0', factor_deposit_withdrawal_options tinyint(4) unsigned NOT NULL default '0', factor_support tinyint(4) unsigned NOT NULL default '0', factor_graphics tinyint(4) unsigned NOT NULL default '0', factor_progressive_jackpots tinyint(4) unsigned NOT NULL default '0', factor_languages tinyint(4) unsigned NOT NULL default '0', factor_reputation_fairness tinyint(4) unsigned NOT NULL default '0', factor_free_bonus tinyint(4) unsigned NOT NULL default '0', create_ts datetime NOT NULL default '-00-00 00:00:00', modify_ts datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (site_id) ) TYPE=MyISAM; insert into site_rating_factors values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now(),now()); insert into content_review_site values (243,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now(),now()); On the slave get the data with load data from master. Both slave and master shows : +++ | content_id | overall_rating | +++ |243 | 1.00 | +++ update content_review_site set overall_rating = 666; updates the tables just fine on both slave and master. But the troubling query below does NOT it only updates on the master. update content_review_site as a,site_rating_factors as b set a.overall_rating = 77 where a.content_id=243; Any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update bug with limit syntax - MySQL Ver 4.011
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: Description: It is unbelievable that the MySQL ver 4.0 have so many bug, I have been reported 2 bugs just a few days ago. Now, I have found a bug again. The bug is : When I execute select * from old_topic where FID=4 and (page=0 or page=167) order by replytime asc limit 40; returns 40 rows[40 rows in set (0.01 sec)] 23 rows value of the column page=167, and and 17 are page=0. Then I execute update old_topic set page=167 where FID=4 and (page=0 or page=167) order by replytime asc limit 40; ## mysql update old_topic set page=167 where FID=4 and (page=0 or page=167) order by replytime asc limit 40; Query OK, 40 rows affected (7.75 sec) Rows matched: 61 Changed: 40 Warnings: 0 # It tells that 40 rows are updated, but it is incorrect, as there should only 17 rows are affected because the limit 40 mysql select count(*) from old_topic where FID=4 and page=167; +--+ | count(*) | +--+ | 61 | +--+ 1 row in set (0.01 sec) [snip] Hi! I think this is a misunderstanding of how/why LIMIT applies to updates. It's not exactly the same as when used for SELECTs. The limit applies to the number of rows _changed_, not examined. Here's the relevant section from the manual: http://www.mysql.com/doc/en/UPDATE.html Paul DuBois will probably correct me if I'm wrong, but if you want to have the behavior you expect, you will either have to do it from your program based on the SELECT you have issued, or use MySQL-4.1 which has subqueries. -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+ahAstvXNTca6JD8RAlJ8AJ4xOpcLH2PO6ImTcZ/CuMYu/+vnPgCfas+z 3zwNaD/bQFe/7yIU6at1Nfw= =2tMz -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: update bug with limit syntax - MySQL Ver 4.011
At 9:45 -0600 3/8/03, Mark Matthews wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: Description: It is unbelievable that the MySQL ver 4.0 have so many bug, I have been reported 2 bugs just a few days ago. Now, I have found a bug again. The bug is : When I execute select * from old_topic where FID=4 and (page=0 or page=167) order by replytime asc limit 40; returns 40 rows[40 rows in set (0.01 sec)] 23 rows value of the column page=167, and and 17 are page=0. Then I execute update old_topic set page=167 where FID=4 and (page=0 or page=167) order by replytime asc limit 40; ## mysql update old_topic set page=167 where FID=4 and (page=0 or page=167) order by replytime asc limit 40; Query OK, 40 rows affected (7.75 sec) Rows matched: 61 Changed: 40 Warnings: 0 # It tells that 40 rows are updated, but it is incorrect, as there should only 17 rows are affected because the limit 40 mysql select count(*) from old_topic where FID=4 and page=167; +--+ | count(*) | +--+ | 61 | +--+ 1 row in set (0.01 sec) [snip] Hi! I think this is a misunderstanding of how/why LIMIT applies to updates. It's not exactly the same as when used for SELECTs. The limit applies to the number of rows _changed_, not examined. Here's the relevant section from the manual: http://www.mysql.com/doc/en/UPDATE.html Paul DuBois will probably correct me if I'm wrong, but if you want to have the behavior you expect, you will either have to do it from your program based on the SELECT you have issued, or use MySQL-4.1 which has subqueries. I'm with Mark. A LIMIT clause in an UPDATE statement limits the number of records updated, just as a LIMIT clause in a DELETE statement limits the number of records deleted. This is consistent with a LIMIT in a SELECT statement, which does not limit the number of records selected by the WHERE clause, but the number of those records that actually are returned to the client. There is one subtlety here: If you set a value to the value it currently has, that is not considered an update, and thus does not count against the limit. Note that all aspects of the behavior of LIMIT with UPDATE may easily be discovered with a little experimentation. -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
update bug with limit syntax - MySQL Ver 4.011
Description:
It is unbelievable that the MySQL ver 4.0 have so many bug, I have been
reported 2 bugs just a few days ago.
Now, I have found a bug again.
The bug is :
When I execute select * from old_topic where FID=4 and (page=0 or page=167) order by
replytime asc limit 40; returns 40 rows[40 rows in set (0.01 sec)]
23 rows value of the column page=167, and and 17 are page=0.
Then I execute update old_topic set page=167 where FID=4 and (page=0 or page=167)
order by replytime asc limit 40;
##
mysql update old_topic set page=167 where FID=4 and (page=0 or page=167) order by
replytime asc limit 40;
Query OK, 40 rows affected (7.75 sec)
Rows matched: 61 Changed: 40 Warnings: 0
#
It tells that 40 rows are updated, but it is incorrect, as there should only 17 rows
are affected because the limit 40
mysql select count(*) from old_topic where FID=4 and page=167;
+--+
| count(*) |
+--+
| 61 |
+--+
1 row in set (0.01 sec)
It is a bug of MySQL server, or I am miss-understanding of the limit in the update
command?
CREATE TABLE `old_topic` (
`FID` smallint(5) unsigned NOT NULL default '0',
`page` smallint(5) unsigned NOT NULL default '0',
`MGID` mediumint(8) unsigned NOT NULL auto_increment,
`status`
enum('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20')
NOT NULL default '1',
`clicks` smallint(5) unsigned NOT NULL default '0',
`topic` char(80) NOT NULL default '',
`nickname` char(20) binary NOT NULL default '',
`uid` mediumint(8) unsigned NOT NULL default '0',
`no_of_reply` smallint(5) unsigned NOT NULL default '0',
`last_replyer` char(20) binary NOT NULL default '',
`replytime` datetime NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`MGID`),
KEY `uid` (`uid`),
KEY `FID` (`FID`,`page`,`replytime`),
FULLTEXT KEY `topic` (`topic`)
) TYPE=MyISAM
How-To-Repeat:
Fix:
Submitter-Id: [EMAIL PROTECTED]
Originator:root
Organization:
MySQL support: [none | licence | email support | extended email support ]
Synopsis: update bug with limit syntax
Severity:
Priority:
Category: mysql
Class:
Release: mysql-4.0.11-gamma (Official MySQL RPM)
C compiler:2.95.3
C++ compiler: 2.95.3
Environment:
System: Linux ip-83-99-134-202.rev.dyxnet.com 2.4.18-14smp #1 SMP Wed Sep 4 12:34:47
EDT 2002 i686 i686 i386 GNU/Linux
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking
--host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit
Thread model: posix
gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='g++'
CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx1 root root 14 Jan 23 11:25 /lib/libc.so.6 -
libc-2.2.93.so
-rwxr-xr-x1 root root 1235468 Sep 6 2002 /lib/libc-2.2.93.so
-rw-r--r--1 root root 2233342 Sep 6 2002 /usr/lib/libc.a
-rw-r--r--1 root root 178 Sep 6 2002 /usr/lib/libc.so
Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static'
'--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb'
'--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile'
'--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock'
'--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr'
'--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share'
'--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info'
'--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server'
'--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CFLAGS=-O6
-fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer
-felide-constructors -fno-exceptions -fno-rtti -mpentium'
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
update bug again on Ver4.011.
Description:
The update command seems does not using the index to find the rows to update.
Here are the command I have execute on MySQL client. You will be found the different
in execute time used. I don't know it is normal or just a bug of the MySQL server.
###
Rows matched: 6541 Changed: 1400 Warnings: 6541
ld_topic use index(FID_2) set page=1 where FID=1 order by replytime asc limit 40;
Query OK, 40 rows affected (6.81 sec)
Rows matched: 80 Changed: 40 Warnings: 0
mysql update old_topic use index(FID_2) set page=1 where FID=1 and page='' order by
replytime asc limit 40;
Query OK, 40 rows affected (6.32 sec)
Rows matched: 40 Changed: 40 Warnings: 0
mysql update old_topic set page= where FID=1;
Query OK, 1400 rows affected (0.13 sec)
Rows matched: 6541 Changed: 1400 Warnings: 6541
mysql explain select * from old_topic use index(FID_2) where FID=1 and page='' order
by replytime asc limit 40;
+---+--+---+---+-+---+--+-+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+--+---+---+-+---+--+-+
| old_topic | ref | FID_2 | FID_2 | 2 | const | 6291 | Using where |
+---+--+---+---+-+---+--+-+
1 row in set (0.00 sec)
mysql show create table old_topic;
CREATE TABLE `old_topic` (
`FID` smallint(5) unsigned NOT NULL default '0',
`page` smallint(5) unsigned NOT NULL default '0',
`MGID` mediumint(8) unsigned NOT NULL auto_increment,
`status`
enum('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20')
NOT NULL default '1',
`clicks` smallint(5) unsigned NOT NULL default '0',
`topic` char(80) NOT NULL default '',
`nickname` char(20) binary NOT NULL default '',
`uid` mediumint(8) unsigned NOT NULL default '0',
`no_of_reply` smallint(5) unsigned NOT NULL default '0',
`last_replyer` char(20) binary NOT NULL default '',
`replytime` datetime NOT NULL default '-00-00 00:00:00',
PRIMARY KEY (`MGID`),
KEY `uid` (`uid`),
KEY `FID` (`FID`,`page`),
KEY `FID_2` (`FID`,`replytime`),
FULLTEXT KEY `topic` (`topic`)
) TYPE=MyISAM
###
How-To-Repeat:
Fix:
Submitter-Id: [EMAIL PROTECTED]
Originator:root
Organization:
MySQL support: [none | licence | email support | extended email support ]
Synopsis: update bug
Severity:
Priority:
Category: mysql
Class:
Release: mysql-4.0.11-gamma (Official MySQL RPM)
C compiler:2.95.3
C++ compiler: 2.95.3
Environment:
System: Linux ip-83-99-134-202.rev.dyxnet.com 2.4.18-14smp #1 SMP Wed Sep 4 12:34:47
EDT 2002 i686 i686 i386 GNU/Linux
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking
--host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit
Thread model: posix
gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='g++'
CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx1 root root 14 Jan 23 11:25 /lib/libc.so.6 -
libc-2.2.93.so
-rwxr-xr-x1 root root 1235468 Sep 6 07:12 /lib/libc-2.2.93.so
-rw-r--r--1 root root 2233342 Sep 6 06:59 /usr/lib/libc.a
-rw-r--r--1 root root 178 Sep 6 06:50 /usr/lib/libc.so
Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static'
'--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb'
'--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile'
'--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock'
'--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr'
'--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share'
'--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info'
'--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server'
'--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CFLAGS=-O6
-fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer
-felide-constructors -fno-exceptions -fno-rtti -mpentium'
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive
update bug
I am using mysql 3.23.41 and I discovered this bug. Imagine you have a varchar column named nick. If you send this query it correctly fails: select * from users where nick = 0; because data types are different but if you issue this one: update users set psw = mypsw where nick = 0; it modifies all data !!! as if where nick = 0 were always true. Bye, have a nice day Daniele - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
UPDATE bug
Description: Ok, I know I submitted an earlier bug report about this, but I've actually had it happen from the mysql monitor. Essentially, UPDATE queries are executing, but not actually updating, unless I SELECT data from the table first. How-To-Repeat: Simple as that. I have yet to notice any particular pattern. It happens for some queries, on some tables, some of the time. Fix: Select data from the table first. Submitter-Id: submitter ID Originator:nick edward purvis Organization: CarSpot.com MySQL support: none Synopsis: UPDATE queries executing, but failing to actually update Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.34a (Source distribution) Environment: System: FreeBSD xtabi.carspot.com 4.2-RELEASE FreeBSD 4.2-RELEASE #4: Sat Jun 2 22:42:12 CDT 2001 [EMAIL PROTECTED]:/usr/src/sys/compile/XTABI i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.2 19991024 (release) Compilation info: CC='cc' CFLAGS='-O -pipe' CXX='c++' CXXFLAGS='-O -pipe -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1169076 Nov 20 2000 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 9 Mar 13 12:02 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 559196 Nov 20 2000 /usr/lib/libc.so.4 Configure command: ./configure --localstatedir=/var/db/mysql --without-perl --without-debug --without-readline --without-bench --with-mit-threads=no --with-libwrap --with-low-memory --enable-assembler --with-berkeley-db --prefix=/usr/local i386--freebsd4.2 Perl: This is perl, version 5.005_03 built for i386-freebsd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: UPDATE bug
On Wed, Jul 04, 2001 at 07:16:50PM -0500, [EMAIL PROTECTED] wrote: Ok, I know I submitted an earlier bug report about this, but I've actually had it happen from the mysql monitor. Essentially, UPDATE queries are executing, but not actually updating, unless I SELECT data from the table first. How-To-Repeat: Simple as that. I have yet to notice any particular pattern. It happens for some queries, on some tables, some of the time. Can you try MySQL 3.23.41? What table type are you using? MyISAM? System: FreeBSD xtabi.carspot.com 4.2-RELEASE FreeBSD 4.2-RELEASE #4: Sat Jun 2 22:42:12 CDT 2001 [EMAIL PROTECTED]:/usr/src/sys/compile/XTABI i386 Ugh. You really need to run FreeBSD 4.3 if you want to avoid nasty threading problems. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 1 days, processed 41,358,529 queries (261/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
UPDATE Bug fixed or not?
Hi, regarding http://ep33.tp4.ruhr-uni-bochum.de/mlists/MySQL/May.2000/index.html#2025 , I had the same problem with MySQL 3.23.30. The following statement Update Tree set left=left+2 where (left$right and right=$right); sometimes (not reproducable) locked up MySQL completely. The left value in at least one row was very high (e.g. 455434), but should be e.g 56. After updating MySQL to version 3.23.37 I couldn cause this error any more. Can anybody tell me if this Bug is fixed now finally? Best regards, Manuel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Possible UPDATE bug
=
tlkp_dealer_lead_method.method_id WHERE dealer.dealer_id = $dealer_id AND
dealer.address_id = tlkp_address.address_id AND tlkp_address.address_state =
tlkp_state.state_id;
And the updates that are run:
UPDATE dealer_lead_method SET method_parameters = '[EMAIL PROTECTED]' WHERE
dealer_id = 2 AND method_id = 1
UPDATE dealer_lead_method SET method_parameters = '(414) 271-1814' WHERE
dealer_id = 2 AND method_id = 2
How-To-Repeat:
I have been unable to repeat this in the mysql monitor.
Fix:
The updates execute normally if I SELECT * FROM dealer_lead_method WHERE
dealer_id = 2 before updating any records for that dealer_id
Submitter-Id:submitter ID
Originator: nick edward purvis
Organization:
CarSpot.com
MySQL support: none
Synopsis:UPDATE fails after select w/JOIN
Severity:non-critical
Priority:low
Category:mysql
Class: sw-bug
Release: mysql-3.23.34a (Source distribution)
Environment:
System: FreeBSD xtabi.carspot.com 4.2-RELEASE FreeBSD 4.2-RELEASE #3: Mon Apr 2
11:10:40 CDT 2001 [EMAIL PROTECTED]:/usr/src/sys/compile/XTABI i386
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.2 19991024 (release)
Compilation info: CC='cc' CFLAGS='-O -pipe' CXX='c++' CXXFLAGS='-O -pipe
-felide-constructors -fno-rtti -fno-exceptions' LDFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1169076 Nov 20 2000 /usr/lib/libc.a
lrwxrwxrwx 1 root wheel 9 Mar 13 12:02 /usr/lib/libc.so - libc.so.4
-r--r--r-- 1 root wheel 559196 Nov 20 2000 /usr/lib/libc.so.4
Configure command: ./configure --localstatedir=/var/db/mysql --without-perl
--without-debug --without-readline --without-bench --with-mit-threads=no
--with-libwrap --with-low-memory --enable-assembler --with-berkeley-db
--prefix=/usr/local i386--freebsd4.2
Perl: This is perl, version 5.005_03 built for i386-freebsd
Hi!
Sorry but UPDATE's and SELECT's are totally unrelated in SQL. You do
not have to do SELECT prior to UPDATE, the only exception being (in
MySQL with InnoDB tables) SELECT ... FOR UPDATE which will only locks
rows involved, which is all.
If the UPDATE is not working then it could be a bug, but we need more
data to establish that. There was an update bug in the version you are
using, so I suggest you upgrade to our 3.23.38 binary for your OS.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Possible UPDATE bug
Description:
When updating a table after it has been selected with a LEFT JOIN, some
UPDATE queries execute normally without actually updating the data, unless the
data to be updated is selected normally first.
This script is written in mod_perl, using DBI::mysql to connect to the
database. The following are the tables referenced by the query:
mysql desc dealer;
+--+---+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+---+--+-+-++
| dealer_id| int(8)| | PRI | NULL| auto_increment |
| dealer_code | varchar(5)| | | ||
| group_id | int(8)| | | 0 ||
| emp_id | int(8)| | | 0 ||
| address_id | int(8)| | | 0 ||
| dealer_name | varchar(200) | | | ||
| dealer_state | int(8)| | | 0 ||
| dealer_zip | varchar(5)| | | ||
| dealer_contact_fname | varchar(100) | YES | | NULL||
| dealer_contact_lname | varchar(100) | YES | | NULL||
| dealer_URL | varchar(200) | YES | | NULL||
| dealer_email | varchar(100) | YES | | NULL||
| dealer_password | varchar(100) | YES | | NULL||
| dealer_ph1 | varchar(25) | YES | | NULL||
| dealer_ph2 | varchar(25) | YES | | NULL||
| dealer_fax | varchar(25) | YES | | NULL||
| active | enum('Y','N') | | | N ||
+--+---+--+-+-++
17 rows in set (0.00 sec)
mysql desc dealer_lead_method;
+---++--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---++--+-+-+---+
| dealer_id | int(8) | | PRI | 0 | |
| method_id | tinyint(4) | | PRI | 0 | |
| method_parameters | text | YES | | NULL| |
+---++--+-+-+---+
3 rows in set (0.00 sec)
mysql desc tlkp_dealer_lead_method;
+-+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+-+-+--+-+-++
| method_id | tinyint(4) | | PRI | NULL| auto_increment |
| method_name | varchar(50) | YES | | NULL||
+-+-+--+-+-++
2 rows in set (0.00 sec)
mysql desc tlkp_address;
++--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-++
| address_id | int(8) | | PRI | NULL| auto_increment |
| export | int(8) | | | 0 ||
| address_addr_1 | varchar(200) | YES | | NULL||
| address_addr_2 | varchar(200) | YES | | NULL||
| address_city | varchar(200) | YES | | NULL||
| address_state | int(8) | | | 0 ||
| address_zip| varchar(5) | YES | | NULL||
| address_plus4 | varchar(4) | YES | | NULL||
| cdate | int(8) | | | 0 ||
++--+--+-+-++
9 rows in set (0.01 sec)
mysql desc tlkp_state;
++--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-++
| state_id | int(8) | | PRI | NULL| auto_increment |
| state_abbr | char(2) | YES | | NULL||
| state_name | varchar(100) | YES | | NULL||
++--+--+-+-++
3 rows in set (0.00 sec)
The select with join:
my $q = SELECT * FROM dealer, tlkp_address, tlkp_state LEFT JOIN
dealer_lead_method ON dealer.dealer_id = dealer_lead_method.dealer_id LEFT JOIN
tlkp_dealer_lead_method ON dealer_lead_method.method_id =
tlkp_dealer_lead_method.method_id WHERE dealer.dealer_id = $dealer_id AND
dealer.address_id =
Update bug?
Hi, There might be some kind of bug in UPDATE. Let's say there is a 4 row table, which looks like this: | fd_01 | int(11) unsigned | | PRI | 0 | | | fd_02 | text | | | | | | fd_03 | varchar(4) | | | | | ... | fd_10 | varchar(20) | | MUL | | | | fd_11 | varchar(20) | | | | | and indexes are: | test| 0 | PRIMARY |1 | fd_01 | A | 40934 | NULL | NULL | | | test| 1 | fd_10|1 | fd_10 | A | 40934 | NULL | NULL | | Now when I do this update: UPDATE test SET fd_11='value' WHERE fd_10='some_value' it usually executes in 0.00 sec But when the query looks like this: UPDATE test SET fd_11='value' WHERE fd_10=some_value It takes about 2 seconds to execute. Tested on MySQL 3.23.36 / FreeBSD 4.2 (i386) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Update bug?
On 2001 Apr 13, Maciek Dobrzanski [EMAIL PROTECTED] wrote: | fd_10 | varchar(20) | | MUL | | | | fd_11 | varchar(20) | | | | | Now when I do this update: UPDATE test SET fd_11='value' WHERE fd_10='some_value' it usually executes in 0.00 sec But when the query looks like this: UPDATE test SET fd_11='value' WHERE fd_10=some_value It takes about 2 seconds to execute. This is because with the first query it can use the index. With the second query, it has to check the whole table. Why? Because obviously you're using numbers. And let's make some_value == 10. In the second query it has to check for rows where fd_10 is: '10' '010' '0010' ... If you're storing numbers, use a numerical column. If you're testing a varchar field, use a string. Tim -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Tim Smith [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Development Team /_/ /_/\_, /___/\___\_\___/ Boone, NC USA ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Update bug?
This is because with the first query it can use the index. With the second query, it has to check the whole table. Why? Because obviously you're using numbers. And let's make some_value == 10. I thought that maybe MySQL should check the field type and do the conversion to string. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Update bug?
On 2001 Apr 13, Maciek Dobrzanski [EMAIL PROTECTED] wrote: This is because with the first query it can use the index. With the second query, it has to check the whole table. Why? Because obviously you're using numbers. And let's make some_value == 10. I thought that maybe MySQL should check the field type and do the conversion to string. To which string? '10' or ' 10' or '010', ...? One thing that might work in this case is to only scan the ranges ['0' - '1'], [' ' - '!'] and an exact match. Something like that. But it's messy, and best to fix the application. It would be nice if MySQL could optimize even the worst queries, though. Tim -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Tim Smith [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Development Team /_/ /_/\_, /___/\___\_\___/ Boone, NC USA ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
log-update bug
Hi, Can you guys confirm that "create database" statements are not logged in the log-update log? The version I am using is 3.22.32. Regards, Jerry. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
