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

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

User, date Message
Written by Chris M.
2 months ago
Category: General
5 posts since Mon, 03 Feb 14
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.
Written by adrien
2 months ago
12 posts since Mon, 27 Jan 14
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 ?
Written by Chris M.
2 months ago
5 posts since Mon, 03 Feb 14
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

Written by kalvaro
2 months ago
563 posts since Thu, 29 Nov 07
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.
Written by adrien
2 months ago
12 posts since Mon, 27 Jan 14
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 ?
Written by kalvaro
2 months ago
563 posts since Thu, 29 Nov 07
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.
Written by Chris M.
2 months ago
5 posts since Mon, 03 Feb 14

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.
Written by adrien
2 months ago
12 posts since Mon, 27 Jan 14
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
Written by adrien
2 months ago
12 posts since Mon, 27 Jan 14
*I know* (not "knaw")
Written by kalvaro
2 months ago
563 posts since Thu, 29 Nov 07
Alright, I was mislead by your reference to Strict Mode.
Written by Chris M.
2 months ago
5 posts since Mon, 03 Feb 14
I confirm that the problem is solved in build 4710.

Thank you!
Written by arucard
2 months ago
6 posts since Wed, 22 Aug 12
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.
Written by Chris M.
2 months ago
5 posts since Mon, 03 Feb 14
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.