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

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

Chris M. posted 11 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 11 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 11 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 11 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 10 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 10 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 10 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 10 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 10 months ago
*I know* (not "knaw")
kalvaro posted 10 months ago
Alright, I was mislead by your reference to Strict Mode.

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

Thank you!
arucard posted 10 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 10 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.