Bug #7963 Zero records not possible
Submitted: 17 Jan 2005 17:12 Modified: 7 Mar 2005 22:20
Reporter: Bojan Hrnkas Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.1.8 OS:Any (*)
Assigned to: Jim Winstead CPU Architecture:Any

[17 Jan 2005 17:12] Bojan Hrnkas
I created an database using MySQL 4.1 (Alpha).

All of the tables have zero record, which I created using:
INSERT INTO table (id) VALUES (0);

I recently installed MySQL 4.1.8 and copied my old 'data' folder to the new server location. I have all tables, and the 0 records are visible and updatable.

Problem occures if I try to create a new Table and insert a new zero record. Also if I try to update zero record using:
 INSERT INTO table (id, name) VALUES (0, test) ON DUPLICATE KEY UPDATE name = 'test',

name field in zero record is not updated, but a new record is created and name is set to 'test'.

I noticed that the problem occures also if I try to create new databases and new tables with zero records.

How to repeat:
Try to create a new table with zero records using MySQL 4.1.8

Suggested fix:
Probably some of the new features or fixes created this problem.
[19 Jan 2005 14:44] MySQL Verification Team

I'm not sure if I understood you problem correctly. Could you describe your problem more detailed? 
Regarding ON DUPLICATE KEY UPDATE, do you have PRIMARY or UNIQUE key?
[20 Jan 2005 0:52] Bojan Hrnkas
First, I'd like to thank You for Your submission.

All tables have PRIMARY KEY on ´id´ column that is also my AUTO_INCREMENT column.
I want to have a 0 (zero) value in a record in each table.

With older MySQL version (4.1.5 Gamma) was everything allright.
I used NO_AUTOVALUE_ON_ZERO option in sqlmode variable, and I could insert a new record with value 0 for ´id´ column.

Since I installed the new MySQL server (4.1.8), I cannot insert a new record with id = 0. Instead a new AUTO_VALUE is created.

I made a workaround:
1. I insert a record;
2. update it, changing id to 0;
3. execute: ALTER TABLE <table> AUTO_VALUE = 1;

That does the job, only with 3 statements instead of one.

I hope it is clearer now.
[21 Feb 2005 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[21 Feb 2005 20:50] Aleksey Kishkin
tested on 4.1.10 .
[7 Mar 2005 22:20] Jim Winstead
I was not able to reproduce this with:

create table t1 (a int auto_increment primary key);
set sql_mode = 'no_auto_value_on_zero';
insert into t1 values (0);
select * from t1;
drop table t1;

Note that using "set global sql_mode = 'no_auto_value_on_zero';" will only change the global SQL_MODE setting, not the setting for the current session. (See http://dev.mysql.com/doc/mysql/en/system-variables.html.)