List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:November 13 2001 8:54pm
Subject:Re: Introducing hidden row having duplicated primary key
View as plain text  
Looks correct to me.
What do you think is the problem?

2 rows were affected by the replace.
One row was deleted, and one was inserted,
Both rows had a value of 'test' for column 'Tab'.

Martin MOKREJŠ wrote:

> Hello,
>   I think I've found a bug. Just by playing with REPLACE and INSERT, the
> following happened. I'm including full log, demonstrating that I don't
> understand sql at all. ;-) Can someone explain me what "2 rows affected"
> means? How can I select the second row from the table? How can I delete it?
> Please CC me in reply. Thanks.
> 
> 
> mysql> select * from Upd;
> +------------------------+---------------------+
> | Tab                    | Time                |
> +------------------------+---------------------+
> | prot_data              | 2001-07-20 00:45:44 |
> | contig_data            | 2001-07-20 00:45:47 |
> | orf_data               | 2001-07-20 00:45:48 |
> | blast_data             | 2001-11-12 15:19:13 |
> | seg_data               | 2001-11-12 15:19:29 |
> | nonglob_data           | 2001-11-12 15:19:35 |
> | pfam_data              | 2001-11-12 15:33:25 |
> | scop_data              | 2001-11-12 15:33:41 |
> | mem_data               | 2001-11-12 15:33:47 |
> | coils_data             | 2001-11-12 15:33:54 |
> | funcat_data            | 2001-11-12 15:34:15 |
> | cogs_data              | 2001-11-12 15:34:59 |
> | known3d_data           | 2001-11-12 15:35:11 |
> | blimps_data            | 2001-11-12 15:37:55 |
> | pros_data              | 2001-11-12 15:38:08 |
> | prd_data               | 2001-11-12 15:39:16 |
> | scop1_data             | 2001-09-02 16:34:01 |
> | scop2_data             | 2001-09-02 17:05:52 |
> | blast_crossupdate_data | 2001-08-23 18:05:12 |
> | blast_self_data        | 2001-10-03 05:15:48 |
> | intergenome_data       | 2001-10-23 20:56:48 |
> +------------------------+---------------------+
> 21 rows in set (0.02 sec)
> 
> mysql> replace into Upd Tab="test" Time=NOW();
> ERROR 1064: You have an error in your SQL syntax near 'Tab="test" Time=NOW()' at line
> 1
> mysql> replace into Upd Tab="test" Time="2";
> ERROR 1064: You have an error in your SQL syntax near 'Tab="test" Time="2"' at line
> 1
> mysql> replace into Upd Tab="test" Time=NOW();
> ERROR 1064: You have an error in your SQL syntax near 'Tab="test" Time=NOW()' at line
> 1
> mysql> replace into Upd set Tab="test" Time=NOW();
> ERROR 1064: You have an error in your SQL syntax near 'Time=NOW()' at line 1
> mysql> replace into Upd (Tab,Time) values ('test', NOW());
> Query OK, 1 row affected (0.02 sec)
> 
> mysql> update into Upd (Tab,Time) values ('test', NOW());
> ERROR 1064: You have an error in your SQL syntax near 'into Upd (Tab,Time) values
> ('test', NOW())' at line 1
> mysql> update Upd (Tab,Time) values ('test', NOW());
> ERROR 1064: You have an error in your SQL syntax near '(Tab,Time) values ('test',
> NOW())' at line 1
> mysql> update Upd set Time=NOW() where Tab="test";
> Query OK, 1 row affected (0.02 sec)
> Rows matched: 1  Changed: 1  Warnings: 0
> 
> mysql> replace into Upd (Tab,Time) values ('test', NOW());
> Query OK, 2 rows affected (0.02 sec)
> 
> mysql> select * from Upd;
> +------------------------+---------------------+
> | Tab                    | Time                |
> +------------------------+---------------------+
> | prot_data              | 2001-07-20 00:45:44 |
> | contig_data            | 2001-07-20 00:45:47 |
> | orf_data               | 2001-07-20 00:45:48 |
> | blast_data             | 2001-11-12 15:19:13 |
> | seg_data               | 2001-11-12 15:19:29 |
> | nonglob_data           | 2001-11-12 15:19:35 |
> | pfam_data              | 2001-11-12 15:33:25 |
> | scop_data              | 2001-11-12 15:33:41 |
> | mem_data               | 2001-11-12 15:33:47 |
> | coils_data             | 2001-11-12 15:33:54 |
> | funcat_data            | 2001-11-12 15:34:15 |
> | cogs_data              | 2001-11-12 15:34:59 |
> | known3d_data           | 2001-11-12 15:35:11 |
> | blimps_data            | 2001-11-12 15:37:55 |
> | pros_data              | 2001-11-12 15:38:08 |
> | prd_data               | 2001-11-12 15:39:16 |
> | scop1_data             | 2001-09-02 16:34:01 |
> | scop2_data             | 2001-09-02 17:05:52 |
> | blast_crossupdate_data | 2001-08-23 18:05:12 |
> | blast_self_data        | 2001-10-03 05:15:48 |
> | intergenome_data       | 2001-10-23 20:56:48 |
> | test                   | 2001-11-13 22:39:40 |
> +------------------------+---------------------+
> 22 rows in set (0.02 sec)
> 
> mysql> insert into Upd (Tab,Time) values ('test', NOW());
> ERROR 1062: Duplicate entry 'test' for key 1
> mysql> select * from Upd;
> +------------------------+---------------------+
> | Tab                    | Time                |
> +------------------------+---------------------+
> | prot_data              | 2001-07-20 00:45:44 |
> | contig_data            | 2001-07-20 00:45:47 |
> | orf_data               | 2001-07-20 00:45:48 |
> | blast_data             | 2001-11-12 15:19:13 |
> | seg_data               | 2001-11-12 15:19:29 |
> | nonglob_data           | 2001-11-12 15:19:35 |
> | pfam_data              | 2001-11-12 15:33:25 |
> | scop_data              | 2001-11-12 15:33:41 |
> | mem_data               | 2001-11-12 15:33:47 |
> | coils_data             | 2001-11-12 15:33:54 |
> | funcat_data            | 2001-11-12 15:34:15 |
> | cogs_data              | 2001-11-12 15:34:59 |
> | known3d_data           | 2001-11-12 15:35:11 |
> | blimps_data            | 2001-11-12 15:37:55 |
> | pros_data              | 2001-11-12 15:38:08 |
> | prd_data               | 2001-11-12 15:39:16 |
> | scop1_data             | 2001-09-02 16:34:01 |
> | scop2_data             | 2001-09-02 17:05:52 |
> | blast_crossupdate_data | 2001-08-23 18:05:12 |
> | blast_self_data        | 2001-10-03 05:15:48 |
> | intergenome_data       | 2001-10-23 20:56:48 |
> | test                   | 2001-11-13 22:39:40 |
> +------------------------+---------------------+
> 22 rows in set (0.02 sec)
> 
> mysql> replace into Upd (Tab,Time) values ('test', NOW());
> Query OK, 2 rows affected (0.00 sec)
> 
> mysql> insert into Upd (Tab,Time) values ('test', NOW());
> ERROR 1062: Duplicate entry 'test' for key 1
> mysql> replace into Upd (Tab,Time) values ('test', NOW());
> Query OK, 2 rows affected (0.00 sec)
> 
> mysql> select * from Upd;
> +------------------------+---------------------+
> | Tab                    | Time                |
> +------------------------+---------------------+
> | prot_data              | 2001-07-20 00:45:44 |
> | contig_data            | 2001-07-20 00:45:47 |
> | orf_data               | 2001-07-20 00:45:48 |
> | blast_data             | 2001-11-12 15:19:13 |
> | seg_data               | 2001-11-12 15:19:29 |
> | nonglob_data           | 2001-11-12 15:19:35 |
> | pfam_data              | 2001-11-12 15:33:25 |
> | scop_data              | 2001-11-12 15:33:41 |
> | mem_data               | 2001-11-12 15:33:47 |
> | coils_data             | 2001-11-12 15:33:54 |
> | funcat_data            | 2001-11-12 15:34:15 |
> | cogs_data              | 2001-11-12 15:34:59 |
> | known3d_data           | 2001-11-12 15:35:11 |
> | blimps_data            | 2001-11-12 15:37:55 |
> | pros_data              | 2001-11-12 15:38:08 |
> | prd_data               | 2001-11-12 15:39:16 |
> | scop1_data             | 2001-09-02 16:34:01 |
> | scop2_data             | 2001-09-02 17:05:52 |
> | blast_crossupdate_data | 2001-08-23 18:05:12 |
> | blast_self_data        | 2001-10-03 05:15:48 |
> | intergenome_data       | 2001-10-23 20:56:48 |
> | test                   | 2001-11-13 22:41:21 |
> +------------------------+---------------------+
> 22 rows in set (0.02 sec)
> 
> mysql> select * from Upd where Tab="test";
> +------+---------------------+
> | Tab  | Time                |
> +------+---------------------+
> | test | 2001-11-13 22:41:21 |
> +------+---------------------+
> 1 row in set (0.02 sec)
> 
> mysql>
> 
> mysql> show columns from Upd;
> +-------+--------------+------+-----+---------+-------+
> | Field | Type         | Null | Key | Default | Extra |
> +-------+--------------+------+-----+---------+-------+
> | Tab   | varchar(100) |      | PRI |         |       |
> | Time  | varchar(100) |      |     |         |       |
> +-------+--------------+------+-----+---------+-------+
> 2 rows in set (0.02 sec)
> 
> mysql>
> mysql> explain select * from Upd where Tab="test";
> +-------+-------+---------------+---------+---------+-------+------+-------+
> | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
> +-------+-------+---------------+---------+---------+-------+------+-------+
> | Upd   | const | PRIMARY       | PRIMARY |     100 | const |    1 |       |
> +-------+-------+---------------+---------+---------+-------+------+-------+
> 1 row in set (0.02 sec)
> 
> mysql>
> 

Thread
Introducing hidden row having duplicated primary keyMartin MOKREJŠ13 Nov
  • Re: Introducing hidden row having duplicated primary keyJeremy Zawodny13 Nov
    • Re: Introducing hidden row having duplicated primary keyJeremy Zawodny13 Nov
  • Re: Introducing hidden row having duplicated primary keyGerald Clark13 Nov
    • Re: Introducing hidden row having duplicated primary keyMartin MOKREJŠ13 Nov
      • Re: Introducing hidden row having duplicated primary keyJeremy Zawodny14 Nov
  • Show query page by pageAuri Net SAC13 Nov
    • Re: Show query page by pageJeremy Zawodny13 Nov
    • Re: Show query page by pageRobert Alexander13 Nov
  • Re: Show query page by pageBill Adams13 Nov
RE: Introducing hidden row having duplicated primary keyQuentin Bennett14 Nov
RE: Show query page by pageRick Emery14 Nov