Bug 190731

Summary: "Field doesn't have a default value" does not take into account triggers
Product: db Reporter: hubbitus <hubbitus>
Component: MySQLAssignee: Jiri Rechtacek <jrechtacek>
Status: RESOLVED WONTFIX QA Contact: issues <issues.netbeans.org>
Priority: P3    
Version: 6.x   
Target Milestone: 7.0   
Hardware: PC   
OS: Linux   
Whiteboard:
Issue Type: DEFECT Exception Report:

Description hubbitus 2010-10-03 19:55:56 UTC
I try debug my SQL for MySQL, but got error similar:
"Error code 1364, SQL state HY000: Field '_lft' doesn't have a default value"
Yes, '_lft' doesn't have a default value, but it is not problem - it is computed in triggers (automatic Nested Sets implementation).

Console native mysql client does not complain about it and execute query perfectly.
Comment 1 Jiri Rechtacek 2010-11-26 08:45:17 UTC
Could you describe your use-case? DDL of the table (incl. trigger declaration) and SQL command for inserting values (or screen shot of Insert values dialog). It'll help to evaluate this problem. Thanks
Comment 2 hubbitus 2010-11-26 10:19:59 UTC
Use case is very simple as INSERT INTO tablename set field1 = 'fieldvalue';
Statement was rejected from Netbeans IDE with error what listed not all fields which have not default values. But it is not always problem if such fields will populated in trigger (in any case). Console MySQL client already inserted such lines.
Comment 3 Jiri Rechtacek 2010-11-26 10:31:52 UTC
Bug description is not enough for me. Describe in detail by me previous comment (DDL table, SQL statemnt, trigger declaration).
Comment 4 hubbitus 2010-12-13 09:35:56 UTC
Ok, full reproduce:

DDL:
DROP TABLE IF EXISTS `nb`;
CREATE TABLE `nb` (
  `id` int(11) NOT NULL,
  `field` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

delimiter ;;

DROP TRIGGER IF EXISTS `test_nb_before_insert`;;

CREATE TRIGGER `test_nb_before_insert` BEFORE INSERT ON `nb`
    FOR EACH ROW
BEGIN
	set NEW.field := NEW.id + 7;
END;;

Then in NetBeans try execute statement:
INSERT INTO nb (id) VALUES(1)
you got error:

Error code 1364, SQL state HY000: Field 'field' doesn't have a default value
Line 1, column 1

Execution finished after 0 s, 1 error(s) occurred.

Try check - no rows inserted in table. But it is incorrect, it statement fully legal, trying do the same in console:

[pasha@bela Fascad]$ mysql -u test test < error.sql 
[pasha@bela Fascad]$ mysql -u test -e 'SELECT * from nb;'
[pasha@bela Fascad]$ mysql -u test -e 'INSERT INTO nb (id) VALUES(1);'
[pasha@bela Fascad]$ mysql -u test test -e 'SELECT * from nb;'
+----+-------+
| id | field |
+----+-------+
|  1 |     8 |
+----+-------+

As you see, in console insert statement work correctly, and as expected 1 row in table!
Comment 5 Jiri Rechtacek 2010-12-13 13:01:08 UTC
Thanks. Now I can reproduce this problem, it worked as you wrote. I'll investigate if it's intentional behavior or a bug.
Comment 6 Jiri Rechtacek 2010-12-13 16:07:31 UTC
I made a simple Java application which executes that insert statement on same JDBC driver as NetBeans IDE does, it ends with the same SQLException:
java.sql.SQLException: Field 'field' doesn't have a default value
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2512)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:781)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:624)
Maybe there is a problem with the driver or some settings are missing.
Comment 7 Jiri Rechtacek 2010-12-13 16:49:49 UTC
I can setup mysql tool to prohibit insert new values as IDE, see:

mysql> set @@sql_mode = 
'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO nb (id) VALUES(3);
ERROR 1364 (HY000): Field 'field' doesn't have a default value

mysql> set @@sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO nb (id) VALUES(3);
Query OK, 1 row affected, 1 warning (0.00 sec)
Comment 8 Jiri Rechtacek 2010-12-13 16:55:06 UTC
Already reported as http://bugs.mysql.com/bug.php?id=6295
Nothing to fix on IDE side.
Comment 9 hubbitus 2010-12-13 17:41:41 UTC
Thank you for investigation.

MySQL cli client have empty @@sql_mode by default and its work. In what (and for what) it setup netbeans? Can I change this mode for my IDE exemplar?
Comment 10 Jiri Rechtacek 2010-12-13 17:46:38 UTC
As a workaround, it works:
set sql_mode = '';
INSERT INTO nb (id) VALUES(1);
Comment 11 hubbitus 2010-12-13 18:40:26 UTC
How I make this mode by default in netbeans as temporary workaround?
By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2012, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo