>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 = 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
---------------------------------------------------------------------
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
