0

I have just inherited a PHP/MySQL application which I am providing support for. I have tried to set up my local environment as close to the production environment, however somehow, the live code is managing to insert a row of data where the same code on my local setup is producing a warning and not performing the insert.

I have tried turning off MySQL Strict Mode and running the query from PHP directly against the database, and I found that the warnings were generated but the insert still worked.

What is preventing the insert from happening from code within PHP in my local environment?

UPDATE (Include Example)

$sql="insert into table("name") values("12312341234")";     
$result=mysql_query($sql);
if ($result) {
    // do stuff
}
else {
    $error = mysql_error();
    data['error'] = $error;
}

The error message which is generated is

"Field 'filed_name' doesn't have a default value"

So basically, what is happening on the production server post query is a new row is appearing, however locally, there is nothing.

I will also mention, the else clause is not even present on the production code. I have put it there to simply find out what is going on.

samazi
  • 1,161
  • 12
  • 24
  • 1
    Well for starters, showing us the error/code would help – Darren Apr 15 '14 at 00:23
  • Haha, yeah I know, however I am tied by an NDA which prevents me from displaying code. I will update my question with an 'example'. Sorry. – samazi Apr 15 '14 at 00:26
  • I understand. Btw, your query breaks as it is; Try this: `$sql="insert into table(`name`) values("12312341234")";`. I highly doubt thats the issue though. – Darren Apr 15 '14 at 00:37
  • 1
    Have a look if this helps : [mysql error 1364 Field doesn't have a default values](http://stackoverflow.com/questions/15438840/mysql-error-1364-field-doesnt-have-a-default-values) – Uours Apr 15 '14 at 00:38
  • @Darren yes you are right, however the actual SQL which is executed becomes: insert into meetings(name) values('150414102616') – samazi Apr 15 '14 at 00:40
  • @Uours As I said, I have tried disabling String Mode. Adding a default value is not an option as it is a mediumtext type field. Also this whole application is going to be riddled with this kind of stuff (unfortunately) so I am hoping to be able to at least replicate the behaviour to start with before fixing things. – samazi Apr 15 '14 at 00:42
  • It looks like it is an issue with MySQL Strict Mode. However, simply changing this from within the database was not enough. I have provided an explanation of how I was able to replicate the behaviour below. – samazi Apr 15 '14 at 03:27

3 Answers3

1

The production enviroment may be configured to not show warnings with somethin like error_reporting(0); or similar configuration on php.ini. Maybe if you use this setup on your local enviroment the results will be similar. BUT. be aware: dont see the errors and/or warnings maybe a VERY dangerous thing. You should try to solve the problem that raise the warning.

bovino Marcelo Bezerra
  • 2,042
  • 16
  • 28
  • Yeah I thought that too so I have already changed this, but it still is not inserting. I agree that it is better to solve the error than just not show it, I don't even know how this is possible. I have never seen code like this before! – samazi Apr 15 '14 at 00:34
  • hmmmm.. bizarre... well, you can also try using the @ at the line that raises the warning (which can be, again, a dangerous thing). You can also surround the problematic portion of the code with try-catch.... well, without seeing the code and without seeing the error message, it´s hard to help more – bovino Marcelo Bezerra Apr 15 '14 at 00:38
1

"Field 'filed_name' doesn't have a default value"

Meaning of the message is that

You have a column in the table defined not null without a default value clause.
And unless which I can't insert without passing a value for it through an insert statement.

Example:

create table test_table( i int not null auto_increment primary key,
                         j int not null,
                         name varchar(10) );

insert into test_table(name) values( 'Ravinder' );
ERROR 1364 (HY000): Field 'j' doesn't have a default value

Check for such field in your table and include it in your insert statement.

Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
  • Thanks. I am aware of this. The field is of type mediumtext so does not actually allow for a default value. Apart from that, I am not actually trying to fix this error (just now). I am more concerned why the same code works on the production server but not in my local environment. – samazi Apr 15 '14 at 00:44
  • Check if there exist any before insert trigger on the table in pro server ? – Ravinder Reddy Apr 15 '14 at 00:46
  • There are no triggers. – samazi Apr 15 '14 at 00:46
  • And the column in pro server table has a `not null` on it or *not*? – Ravinder Reddy Apr 15 '14 at 00:48
  • Yes it does have not null set. I have no idea who would set a field not null and then write a query which does not set that value, however they have managed to make it work and I have no idea how! – samazi Apr 15 '14 at 00:52
  • Ok. Then it seems your current server environment's table does not match with that of pro server. Unless which, the query should have worked. – Ravinder Reddy Apr 15 '14 at 00:56
0

So I have been able to replicate the behaviour. I had previously tried setting sql_mode from within the database.

SET @@global.sql_mode = '';
SET @@sql_mode = '';

This did not work. I am using MySQL 64bit on Windows 7. I tried editing the my.ini file and changing 

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

to 

sql-mode=

This prevented the MySQL service from starting.

In the end, I re ran the MySQLInstanceConfig.exe and unchecked the Enable Strict Mode option during the setup.

I also now think it might be possible to simply remove sql-mode= from the my.ini file all together to achieve the same result as this seems to be the only thing which is different after running the config wizard.

Lastly, I restarted MySQL and Apache2.2

samazi
  • 1,161
  • 12
  • 24