distal-attribute
distal-attribute
distal-attribute
distal-attribute

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

Chris M. posted 9 months ago in General
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.
adrien posted 9 months ago
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 ?
Chris M. posted 9 months ago
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 posted 9 months ago
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.
adrien posted 9 months ago
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 posted 9 months ago
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.
Chris M. posted 9 months ago

adrien wrote:
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'

adrien wrote:
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.
adrien posted 9 months ago
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
adrien posted 9 months ago
*I know* (not "knaw")
kalvaro posted 9 months ago
Alright, I was mislead by your reference to Strict Mode.

Chris M. posted 9 months ago
I confirm that the problem is solved in build 4710.

Thank you!
arucard posted 9 months ago
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.
Chris M. posted 9 months ago
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.