Inserting Or Modifying Data

[idroj]

Rare situations when inserting or modifying data:

— Column value violations: SQLyog alter non correct data in front of no modification with command line.

— Invalid Date Conversions: SQLyog alter non correct data in front of no modification with command line.

SCENARIO:

Server version: 5.0.24a-community-nt

TABLE: person

Field Type Null Key Default Extra

---

---

---

---

---

---

person_id smallint(5) unsigned NO PRI (NULL) auto_increment

fname varchar(20) YES (NULL)

lname varchar(20) YES (NULL)

gender enum('M','F') YES (NULL)

birth_date date YES (NULL)

address varchar(30) YES (NULL)

city varchar(20) YES (NULL)

state varchar(20) YES (NULL)

country varchar(20) YES (NULL)

postal_code varchar(20) YES (NULL)

TABLE person contents:

*************************** 1. row ***************************

person_id: 1

fname: William

lname: Turner

gender: M

birth_date: 1972-05-27

address: 1225 Tremont St.

city: Boston

state: MA

country: USA

postal_code: 02138

1 row in set (0.00 sec)

1)– Column value violations

a) QUERY with SQLyog:

=================

UPDATE person

SET gender = 'Z'

WHERE person_id = 1;

Messages:

(1 row(s)affected)

(0 ms taken)

TABLE person contents After QUERY with SQLyog: with NULL value (0 in numeric fields)

*************************** 1. row ***************************

person_id: 1

fname: William

lname: Turner

gender:

birth_date: 1972-05-27

address: 1225 Tremont St.

city: Boston

state: MA

country: USA

postal_code: 02138

1 row in set (0.00 sec)

😎 The same QUERY with Command Line and other Programs:

===================================================

mysql> UPDATE person

-> SET gender = 'Z'

-> WHERE person_id = 1;

ERROR 1265 (01000): Data truncated for column 'gender' at row 1

mysql>

TABLE person contents After QUERY with command line and other Programs no change and error:

*************************** 1. row ***************************

person_id: 1

fname: William

lname: Turner

gender: M

birth_date: 1972-05-27

address: 1225 Tremont St.

city: Boston

state: MA

country: USA

postal_code: 02138

1 row in set (0.00 sec)

2)– Invalid Date Conversions

a) QUERY with SQLyog:

=================

UPDATE person

SET birth_date = 'DEC-21-1980'

WHERE person_id = 1;

Messages:

(1 row(s)affected)

(0 ms taken)

TABLE person contents After QUERY with SQLyog date 0000-00-00:

*************************** 1. row ***************************

person_id: 1

fname: William

lname: Turner

gender: M

birth_date: 0000-00-00

address: 1225 Tremont St.

city: Boston

state: MA

country: USA

postal_code: 02138

1 row in set (0.00 sec)

😎 The same QUERY with Command Line and other Programs:

===================================================

mysql> UPDATE person

-> SET birth_date = 'DEC-21-1980'

-> WHERE person_id = 1;

ERROR 1292 (22007): Incorrect date value: 'DEC-21-1980' for column 'birth_date' at row 1

mysql>

TABLE person contents After QUERY with command line and other Programs no change and error:

*************************** 1. row ***************************

person_id: 1

fname: William

lname: Turner

gender: M

birth_date: 1972-05-27

address: 1225 Tremont St.

city: Boston

state: MA

country: USA

postal_code: 02138

1 row in set (0.00 sec)

[peterlaursen]

this is a sql-mode issue.

Please refer to: http://webyog.com/faq/28_72_en.html

Please understand that it is the server that behaves like that in 'non-strict' mode – not SQLyog.

SQLyog does not alter data itself. It only sends SQL to the server.

We do intend to improve the handling of sql_modes, but have not yet fully annalyzed what is possible form a grid-based program. The problem is that if a column is defined as NOT NULL and does not have a DEFAULT, there are some issues when updating from the RESULT tab. That also will affect Usr management as the user table has some BLOB fields defined as NOT NULL (and BLOBs cannot have defaults).

One solution couild be only to SET sql_mode = '' for those operations where it is required and switch back again after that.

[idroj]

Thanks a lot, for the answer, it works OK.

The new Question is:

Is posible to change sql-mode when I start a conection in SQLyog with statement set session sql_mode ='TRADITIONAL' or …?

[peterlaursen]

Yes .. just write the “SET sql_mode = ” yourself.

But when updating from RESULT tab if not all columns are shown and if the columns not shown are defined as NOT NULL and have no DEFAULT, then the UPDATE (and INSERT) statement may fail. That is true for the 'strict_all_tables' mode and the 'strict_trans_tables' with transactional tables (InnoDB tables). And maybe other modes too.

[Author]

Posts