Unable to automatically insert auto_increment values

Bug #911194 reported by Sebastian Schmidt on 2012-01-03
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
chive
Fix Released
Medium
David Roth

Bug Description

If you create a table with an primary column that uses auto_increment values, you aren't able to use that feature while inserting.

Coming from phpMyAdmin I'm used to just leave the auto_increment column empty while entering the data to insert. This results in a error, which tells adding the line failed using the following query:

INSERT INTO `table` (`table_id`, `table_value`) VALUES
 ('', 'example');

Instead of trying to insert an empty string for the auto_increment column `table_id` there should be NULL in the query, which works fine.

Inserting NULL in the form before inserting, chive uses the following query:

INSERT INTO `table` (`table_id`, `table_value`) VALUES
 ('NULL', 'example');

This obviously failes again, for using NULL as a string.

Related branches

David Roth (davrot) wrote :

Hi there,

I don`t really understand the problem.

I tested the following table:

DROP TABLE IF EXISTS `mazzo`;
CREATE TABLE IF NOT EXISTS `mazzo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idgiocatore` int(11) NOT NULL,
  `idcarta` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

I can no execute the following queries:

INSERT INTO `mazzo` (`id`, `idgiocatore`, `idcarta`) VALUES
 ('', '', '');

INSERT INTO `mazzo` (`id`, `idgiocatore`, `idcarta`) VALUES
 (null, '', '');

INSERT INTO `mazzo` (`idgiocatore`, `idcarta`) VALUES
 ('', '');

In all of these cases I get a new row with a new auto_increment value, so everything is ok?

Hi David,

it themes to me, that you've mysqls strict mode not enabled. If I run your example I get mysql error #1366, because '' is not a valid integer value.

Disabling mysqls strict mode is absolutely no option in production. None of your examples should work in a productiv environment. A valid query for your table definition should look like this:

INSERT INTO `mazzo` (`id`, `idgiocatore`, `idcarta`) VALUES
 (null, 0, 0);

David Roth (davrot) wrote :

Hi Sebastian,

ahh, now it makes sense, thanks for pointing that out.
Yes, I don`t have mysql`s strict mode enabled on my dev machine so this is why I could not reproduce it.
And of course you are right, we should really create valid sql on the insert tab.

So I will change the title of this report to something like this: Unable to automatically insert auto_increment values on "Insert-Tab" when in mysql_strict_mode. Do you agree?

Hi again,

yes of course I agree.

This bug is the only thing that stops me from using chive in our company for production. It would be great if that's getting fixed in future. Thx in advance.

David Roth (davrot) on 2012-01-03
Changed in chive:
assignee: nobody → David Roth (davrot)
importance: Undecided → Medium
milestone: none → 1.0.2
status: New → Triaged
David Roth (davrot) on 2012-01-04
Changed in chive:
milestone: 1.0.2 → 1.0.3
David Roth (davrot) on 2012-03-18
Changed in chive:
status: Triaged → In Progress
David Roth (davrot) wrote :

Fixed in trunk. Thanks for the report.

Changed in chive:
status: In Progress → Fix Committed
David Roth (davrot) on 2012-03-18
Changed in chive:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers