Field 'text' doesn't have a default value

Question

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

I realise this is an old question, but I had a similar problem and found this question via Google without an answer, so in case anyone else does too, here was my solution:

In my case, I was trying to run a shopping cart on my local machine, and I kept getting database errors like the one mentioned in the question. I was using MySQL 5.5.12 and it turns out that the php in my chosen shopping cart was written for an older MySQL version, and this kept flagging up errors and incompatabilities. I'm not going to suggest if you have a similar problem to downgrade, luckily it's not necessary!

What is needed here is for you to disable MySQL strict mode which is causing the compatability issues. To do so all you have to do is run the following query via phpMyAdmin/SQLyog (or chosen interface):

SET @@global.sql_mode='';

Just to clarify that is two single quotes (').

Refer to this site for more info or other ways to disable MySQL strict mode: http://nickbartlett.com/wordpress/how-to-turn-off-mysql-strict-mode/

Answer 2

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 3

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', '');

Answer 4

Ran into this same problem when debugging a legacy app and was able to solve it by going into my MySQL configuration file (my.cnf on my mac, but probably my.ini on windows if that's how you roll) and commenting out the sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES line at the bottom of the file. Restart mysql once this is done for the change to take affect.