MySQL - MySQLi and the story of a mysterious error.

Question

Hello,

I have a system that runs on hundreds of websites and I am getting an error for the first time. I was hoping that someone could tell me what may cause this error so I can try to remove it.

The issue comes when trying to add a page.

INSERT INTO pages (parent, name, type, sort) VALUES ('0', 'test', 'text', '37.5');

This spits out the following error.

[nativecode=1364 ** Field 'text' doesn't have a default value]

I thought this may be code based so I uploaded phpMyAdmin and the error still persisted.

There is a TEXT field called text. This doesn't have a default value, however it has never needed one. It has worked fine without one up to now.

When I try to set the default field on this server I get the following error.

#1101 - BLOB/TEXT column 'text' can't have a default value 

Basically, the question is - what is going on?

Is MySQLi different from MySQL? Could this be the cause.

My server runs mysql 5.0.5, this server runs mysql 5.0.51a. Can I safely assume this isn't the cause?

Does anyone have any ideas or even guesses as to where the cause of this may lie?

Answer 1

If your field named 'text' is not nullable, you would have to insert a value into it if there is also no default. I do not know why there is a difference in behavior between the two versions. MySQLi is just the version of the MySQL driver that works on later versions of MySQL (I think >= 4.1, but I don't recall for sure).

Answer 2

If the field text is in the table that you're inserting into, and it can't be NULL, then you need to set its value. If you don't, a default value will be set for it, and there is no default value, as you see. Try using backticks to escape the reserved word, and inserting a value for it, as follows:

INSERT INTO pages (parent, name, type, sort, `text`) VALUES ('0', 'test', 'text', '37.5', '');