You don't see the failure because you are doing an insert ignore.
You are also inserting numbers with 4 digits right of the decimal, where you have specified 2.
Mudit Wahal wrote:
Description:I have a delete statement and an insert statment. I'm replacing a line but I want
to make sure, that its deleted. The insertion is successful as per sql batch output.
But when I dump the data from the table, there is no record for the date ! Seems like
delete operation happened after the insert.
How-To-Repeat:SQL Input Script
================================
create table if not exists MSFT (date DATE not null, close DECIMAL(10,2), high DECIMAL(10,2), low DECIMAL(10,2),
volume MEDIUMINT, yest DECIMAL(10,2), PRIMARY KEY date (date));
delete from MSFT where date='2003-02-04';
insert ignore into MSFT Values('2003-02-04','47.3200','47.9400','46.8800','406440','47.8000');
===============================
SQL Output from the script
===============================
--------------
create table if not exists MSFT (date DATE not null, close DECIMAL(10,2), high DECIMAL(10,2), low DECIMAL(10,2),
volume MEDIUMINT, yest DECIMAL(10,2), PRIMARY KEY date (date))
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
delete from MSFT where date='2003-02-04'
--------------
Query OK, 0 rows affected (0.01 sec)
--------------
insert ignore into MSFT Values('2003-02-04','47.3200','47.9400','46.8800','406440','47.8000')
--------------
Query OK, 1 row affected (0.02 sec)
------------------------------------------
SQL Output from query for the date 2003-02-04
===============================
mysql> select * from eod_stocks.MSFT where date = '2003-02-04';
Empty set (0.01 sec)
Fix:
Submitter-Id: <submitter ID>
Originator: Mudit Wahal
Organization:
MySQL support: noneCopyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
Synopsis: Insert after delete fails.
Severity: critical
Priority: high
Category: mysql
Class:
Release: mysql-4.0.1-alpha-max (Official MySQL-max binary)
Server: /usr/local/mysql/bin/mysqladmin Ver 8.23 Distrib 4.0.1-alpha, for pc-linux-gnu on i686
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.1-alpha-max
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 2 days 4 hours 15 min 23 sec
Threads: 1 Questions: 1301712 Slow queries: 0 Opens: 182971 Flush tables: 11 Open tables: 64 Queries per second avg: 6.919
Environment:
System: Linux bp6 2.4.20 #8 Wed Jan 29 09:50:06 PST 2003 i686 unknown
Architecture: i686
Some paths: /home/wmudit/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/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.1 2.96-98)
Compilation info: CC='gcc' CFLAGS='-O3 -mpentium ' CXX='gcc' CXXFLAGS='-O3 -mpentium -felide-constructors' LDFLAGS='-static'
LIBC: lrwxrwxrwx 1 root root 13 Jan 24 18:13 /lib/libc.so.6 -> libc-2.2.4.so
-rwxr-xr-x 1 root root 1282588 Sep 4 2001 /lib/libc-2.2.4.so
-rw-r--r-- 1 root root 27304836 Sep 4 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Sep 4 2001 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL-max binary' --with-extra-charsets=complex --with-server-suffix=-max --enable-thread-safe-client --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --disable-shared --with-berkeley-db --with-innodb
---------------------------------------------------------------------
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
--------------------------------------------------------------------- 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
