Problem when posting in the grid a row that contains a YEAR column

[expired user #7710]'s profile image [expired user #7710] posted 10 years ago in General Permalink
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.
[expired user #7662]'s profile image [expired user #7662] posted 10 years ago Permalink
Hi,

when I try your example, I don't have error.

here the SQL executed by heidiSQL when I insert row by hand :

INSERT INTO `admen`.`test_case` (`id`, `a_year`) VALUES (1, '2014');

What sql engine do you use ?
[expired user #7710]'s profile image [expired user #7710] posted 10 years ago Permalink
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

kalvaro's profile image kalvaro posted 10 years ago Permalink
I can reproduce it as well (same client version, MySQL 5.5):

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.
[expired user #7662]'s profile image [expired user #7662] posted 10 years ago Permalink
I don't reproduce that but maybe I know why.

Do you know if the "Strict Mode" of your mysql server is enabled ?

Do you have "Prefill empty date/time fields with current date/time" checked in Data editing tab on Preferences window ?
kalvaro's profile image kalvaro posted 10 years ago Permalink
Adrien, it's strictly a client-side issue. HeidiSQL populates the grid cell with a full date (rather than just a year). I do have that check enabled.
[expired user #7710]'s profile image [expired user #7710] posted 10 years ago Permalink


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
[expired user #7662]'s profile image [expired user #7662] posted 10 years ago Permalink
Hi kalvaro, Chris M.

kalvaro, I knaw it's stictly a client-side issue.
I wanted to reproduce the problem to test the correction that why I asked these questions.

Fix in r4710
[expired user #7662]'s profile image [expired user #7662] posted 10 years ago Permalink
*I know* (not "knaw")
kalvaro's profile image kalvaro posted 10 years ago Permalink
Alright, I was mislead by your reference to Strict Mode.
[expired user #7710]'s profile image [expired user #7710] posted 10 years ago Permalink
I confirm that the problem is solved in build 4710.

Thank you!
arucard's profile image arucard posted 10 years ago Permalink
Hi, I'm aware this is fixed, but I'm just curious.

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.
[expired user #7710]'s profile image [expired user #7710] posted 10 years ago Permalink
Actually, there is no "insert query" because there is no application... yet!

I use HeidiSQL for manual data entry in a few tables that store some personal notes, links, etc.

In the near future I plan to develop a Java Swing GUI to manipulate the data.

Please login to leave a reply, or register at first.