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

Thank you!
Written by arucard
6 months ago
12 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.
6 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.