hi,
If you try to insert rows by hand in this sample table:
CREATE TABLE `test_case` (
`id` INT(11) NULL DEFAULT NULL,
`a_year` YEAR NULL DEFAULT NULL
)
ENGINE=InnoDB;
you will get the sql error 1265, because the value '2014-02-03 10:26:43.0000' is put in the cell instead of '2014'.
A possible workaround is to right-click on the grid cell and use the "insert value" submenu items.
Please fix it.
Thanks.
Problem when posting in the grid a row that contains a YEAR column
Mine tries to post this:
INSERT INTO `test`.`test_case` (`id`, `a_year`) VALUES (1, '2014-02-03 10:43:01.0000');
This was working fine a couple of HeidiSQL updates before.
Software versions
---------------------
HeiSQL client: 8.3.0.4696 (64bit)
MySQL server: 5.6.10
INSERT INTO `test`.`test_case` (`id`, `a_year`) VALUES (1, '2014-02-03 10:43:01.0000');
This was working fine a couple of HeidiSQL updates before.
Software versions
---------------------
HeiSQL client: 8.3.0.4696 (64bit)
MySQL server: 5.6.10
I can reproduce it as well (same client version, MySQL 5.5):
I find the issue when I leave the default value (I only see the year with the control is on edit mode, but full date shows up on blur). It gets fixed when I type or select something else.
INSERT INTO `test`.`test_case` (`id`, `a_year`) VALUES (1, '2014-02-03 11:47:46.0000');
/* SQL Error (1265): Data truncated for column 'a_year' at row 1 */
I find the issue when I leave the default value (I only see the year with the control is on edit mode, but full date shows up on blur). It gets fixed when I type or select something else.
Do you know if the "Strict Mode" of your mysql server is enabled ?
My server sql_mode is
'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
Do you have "Prefill empty date/time fields with current date/time" checked in Data editing tab on Preferences window ?
Yes, this option is checked.
Thanks.
Code modification/commit
from adri.granger@gmail.com,
10 years ago,
revision 8.3.0.4710
Fix good format when try to insert value in year column. See http://www.heidisql.com/forum.php?t=14728
Hi, I'm aware this is fixed, but I'm just curious.
Why you didn't use something like this in you INSERT query?
IMHO it's right way...
Then full INSERT query like this:
Thanks for reply.
Why you didn't use something like this in you INSERT query?
SELECT YEAR('2014-02-03 10:43:01.0000');
IMHO it's right way...
Then full INSERT query like this:
INSERT INTO `test`.`test_case` (`id`, `a_year`) VALUES (1, YEAR('2014-02-03 10:43:01.0000'));
Thanks for reply.
Please login to leave a reply, or register at first.