MySQL Bugs: #7963: Zero records not possible

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:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.1.8	OS:	Any (*)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
I created an database using MySQL 4.1 (Alpha).

All of the tables have zero record, which I created using:
SET GLOBAL sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
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.

Hi,

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?

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.

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".

tested on 4.1.10 .

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.)