• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1435
  • Last Modified:

Null integer not allowed in MySQL 5 ???

Hi All,

I have very weird problem. I'm trying to setup replication between two database servers - one MySQL 4.1, the other 5.0. It's only for testing purposes, as we're going to switch to the newer one once we're happy with tests.

However, after i import SQL dump, databases looks identical. However, MySQL 5 after running query:

INSERT INTO tblpolicylist (
ClientID, ProductGroup, Product, ProductType, RefID, PolicyStatus, Referer, OfferID , OwnerID
) VALUES (
'112402','Household','1','22','079580298' , 'New Business' , '' , '' , '91'
);

is coming back with error "Incorrect integer value: '' for column 'Referer' at row 1' " . It's interesting, cause on MySQL 4.1 the same query is working just fine. Table is exact repliaca of the one from 4.1, Referer field is integer, with null allowed... Of course if I replace '' with '0', query is working fine, but I need to have this value null... It's also causing problems with replications, as similar queries are returning error on 5.0 slave.

Any ideas?
0
dannydudek
Asked:
dannydudek
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
'' is a string value, and NOT a integer. MySQL 5 is a bit more picky about implicit data type conversions..

try this (I removed the single quotes for ALL the values that I assume that have numerical data type, and put NULL instead of '' ...

INSERT INTO tblpolicylist (
ClientID, ProductGroup, Product, ProductType, RefID, PolicyStatus, Referer, OfferID , OwnerID
) VALUES (
112402,'Household', 1 , 22,'079580298' , 'New Business' , NULL , NULL , 91
);

Open in new window

0
 
dannydudekAuthor Commented:
Thanks for that, however that means I would have modified application that is using database as well as data in the primary database (4.1).

Is there no way I could enable "less picky" legacy mode in handling integers or so?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
not that i know.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now