Table column default set to NULL, but NULL insert query makes a "0" instead

.
Greetings!

I have got a table that has a columm that has the following settings:

Field (or name): Opponent
Type: smallint(5)
Collation: blank
Attributes: UNSIGNED
Null: Yes
Default: NULL

If I do an insert query using the below $team_id_opp variable the table new row will have a value of zero (0) instead of the value of NULL.


$team_id_opp	=	NULL;

INSERT INTO Games ( Opponent ) VALUES ('$team_id_opp')  // Value for the Opponent cell turns out to be "0" instead of NULL

If I put single quotes or double quotes around NULL like below the table row still has a value of zero instead of NULL for the Opponent table cell value.

$team_id_opp	=	'NULL'; // Still has 0 instead of NULL
$team_id_opp	=	"NULL"; // Still has 0 instead of NULL

If I remove the $team_id_opp variable from the script altogether and do an insert query with no variable value at all it still inserts a 0 instead of the NULL value.

Question. How can I make the Opponent column cell in a new row have the default NULL value instead of a value of 0?

Thanks.

easy peasy

INSERT INTO Games ( Opponent ) VALUES ( NULL )

.
That won’t work. Some of the Opponent column cells need to have a numerical value. Other records/rows have no value for the opponent column. That’s why I need to use NULL.

The Opponent column is for Division 1 baseball opponents. If the opposing team is not a Division 1 team then I have another column in the same row for those ones. For a non D1 opposing team the Opponent column cell needs to have no value or a value of NULL.

Just as a test I used the value as you suggested just to see the results.

INSERT INTO Games ( Opponent ) VALUES ( NULL )

Strangely, oddly the value, using the above query, still resulted in a cell value of 0 instead of NULL.

It’s unusual that it would do that. There has got to be a simple answer.

Try a little checking:


SELECT *
  FROM Games
 WHERE Opponent is NULL

and


SELECT *
  FROM Games
 WHERE Opponent = 0

Have you declared any triggers on the Games table?

Do you have an index on that column?

Are you using a test db and a production db?

Have you replaced default display values in your SQL manager?

fascinating

would you kindly do a SHOW CREATE TABLE for that table

.


//----
CREATE TABLE `Games` (
  `team_id` smallint(5) unsigned NOT NULL,
  `Year` year(4) NOT NULL,
  `game_id` mediumint(8) unsigned NOT NULL,
  `GameDate` int(10) unsigned NOT NULL,
  `GameOrder` tinyint(2) NOT NULL default '1',
  `Stadium` set('vs','at','neu','t','c','r','sr','cws') NOT NULL default 'vs',
  `Opponent` smallint(5) unsigned default NULL,
  `NonD1` varchar(75) NOT NULL COMMENT 'Not D1 opponent',
  `ConferenceGame` set('','*') NOT NULL,
  `DivisionalPlay` set('','*') NOT NULL,
  `Comment` varchar(75) NOT NULL COMMENT 'For footnotes',
  `Status` set('ForTesting','Tournament','Scheduled','Done','Resumed','Suspended','Postponed','Cancelled') NOT NULL,
  `Innings` tinyint(2) unsigned default NULL,
  `Outcome` set('','W','L','T') NOT NULL,
  `RunsScored` tinyint(3) unsigned default NULL,
  `OpponentScore` tinyint(3) unsigned default NULL,
  `tourn_id` smallint(5) unsigned default NULL COMMENT 'id for in season tournament',
  `TournHomeTeam` set('','Yes','No') NOT NULL,
  `TournRoadTeam` set('','Yes','No') NOT NULL,
  `TournGameNum` smallint(3) unsigned default NULL,
  `Bracket` tinyint(1) default NULL COMMENT 'CWS has two brackets',
  `Finals` set('','Yes','No') NOT NULL COMMENT 'CWS has finals',
  UNIQUE KEY `game_id` (`game_id`),
  KEY `team_id` (`team_id`),
  KEY `Year` (`Year`),
  KEY `GameDate` (`GameDate`),
  KEY `GameOrder` (`GameOrder`),
  KEY `ConferenceGame` (`ConferenceGame`),
  KEY `DivisionalPlay` (`DivisionalPlay`),
  KEY `tourn_id` (`tourn_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Schedules and game results.';
//---

It’s unrelated to the actual question, but the use of a SET field for a yes/no isn’t suitable, since a SET can be both yes AND no in this case (and ‘’ looking at your code). You would want an enum, and rather than allow ‘’ I would allow it to be NULL instead. Even better, use a TINYINT and set 1 for yes, 0 for no, and NULL for, well, NULL.

SET should only be used where you (may) need to choose multiples as not only does it allow this, but internally it also takes up more space. An ENUM, for example, would store the values internally as incrementing numbers (0, 1, 2, 3, 4, 5, 6, 7 etc), whereas a SET stores them as follows: 1, 2, 4, 8, 16, 32, 64.

Actually related to your question, I just created the table according to your code and inserted a NULL record to the Opponent row with no problems at all. Are you able to insert NULL anywhere? Can you do it with a GUI such a PHPMyAdmin, or HeidiSQL? I’m not aware of a setting for it, nor a reason why you’d want to, but I wonder if there is a server config value that disallows NULL and forces a 0 instead?

Also, while I think, how are you checking the value? Are you checking it via PHP? If you use something like the aforementioned HeidiSQL it will tell you EXACTLY what the value of the field is, and not what another language interprets it as

antnee beat me to it – while i was fixing errors!

i created your table, then tried this –

INSERT INTO Games ( Opponent ) VALUES ( NULL ) 

unfortunately, this failed because i was missing a value for some column

so i supplied a value, and tried again, and got a similar message for another column

so i tried it again… and again… and again…

eventually, this was the query that actually ran –

INSERT 
  INTO Games 
     ( team_id
     , `year`
     , game_id 
     , gamedate
     , nond1
     , conferencegame
     , divisionalplay
     , `comment`
     , `status`
     , outcome
     , tournhometeam
     , tournroadteam
     , finals
     , Opponent ) -- this is the column i was trying to fill
VALUES 
     ( 9
     , 2012
     , 37
     , UNIX_TIMESTAMP()
     , 0
     , 0
     , 0
     , 'meh'
     , 'cancelled'
     , 't'
     , 'yes'
     , 'no'
     , 'yes'
     , NULL ) -- this is the value i was trying to fill it with

you’ll be happy to know that mysql ~does~ accept NULL for Opponent, and does not change it to 0

:slight_smile:

I wonder if we have conflicting server configs, r397? I had no problem creating the table and immediately inserting NULL


CREATE TABLE `Games` (
`team_id` smallint(5) unsigned NOT NULL, 
`Year` year(4) NOT NULL, 
`game_id` mediumint(8) unsigned NOT NULL, 
`GameDate` int(10) unsigned NOT NULL, 
`GameOrder` tinyint(2) NOT NULL default '1', 
`Stadium` set('vs','at','neu','t','c','r','sr','cws') NOT NULL default 'vs', 
`Opponent` smallint(5) unsigned default NULL, 
`NonD1` varchar(75) NOT NULL COMMENT 'Not D1 opponent', 
`ConferenceGame` set('','*') NOT NULL, 
`DivisionalPlay` set('','*') NOT NULL, 
`Comment` varchar(75) NOT NULL COMMENT 'For footnotes', 
`Status` set('ForTesting','Tournament','Scheduled','Done','Resumed','Suspended','Postponed','Cancelled') NOT NULL, 
`Innings` tinyint(2) unsigned default NULL, `Outcome` set('','W','L','T') NOT NULL, 
`RunsScored` tinyint(3) unsigned default NULL, 
`OpponentScore` tinyint(3) unsigned default NULL, 
`tourn_id` smallint(5) unsigned default NULL COMMENT 'id for in season tournament', 
`TournHomeTeam` set('','Yes','No') NOT NULL, 
`TournRoadTeam` set('','Yes','No') NOT NULL, 
`TournGameNum` smallint(3) unsigned default NULL, 
`Bracket` tinyint(1) default NULL COMMENT 'CWS has two brackets', 
`Finals` set('','Yes','No') NOT NULL COMMENT 'CWS has finals', 
UNIQUE KEY `game_id` (`game_id`),
KEY `team_id` (`team_id`), 
KEY `Year` (`Year`), 
KEY `GameDate` (`GameDate`), 
KEY `GameOrder` (`GameOrder`), 
KEY `ConferenceGame` (`ConferenceGame`), 
KEY `DivisionalPlay` (`DivisionalPlay`), 
KEY `tourn_id` (`tourn_id`) ) 
ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Schedules and game results.';

INSERT INTO Games ( Opponent ) VALUES ( NULL );
/* 1 rows affected, 0 rows found. Duration for 2 queries: 0.047 sec. */ 

I ran that all at once via HeidiSQL

.
Antnee;

Thanks for helping out with my problem.

Yes.

I’m using the phpMyAdmin. I can insert a new row with the phpMyAdmin “Insert” button and it creates a NULL record in the Opponent row.

It’s just with the PHP script that inserting a row with the $team_id_opp set to NULL that the Opponent row sets to 0 (this might be a question for the PHP forum).

I was thinking yesterday that I might ought to post the PHP script. I just didn’t want to put too much information in one post.

If you scroll about 3/5’s of the way down the page you will see the snippet:
// --------------------------------------------------
// ### For non d1 opponent empty team id. ###
// --------------------------------------------------

$team_id_opp = NULL;

Then if you look down at the first query you will see the following:
(The query is not yet complete. I am still working on it.)
[COLOR=“#FF0000”]
team_id,
Year,
game_id,
GameDate,
GameOrder,
Stadium,
Opponent,
NonD1 )

VALUES[/COLOR]


(‘$team_id’,
‘$Year’,
‘$game_id_this_team’,
‘$GameDateTS’,
‘$Sec’,
‘$StadiumThisTeam’,
‘$team_id_opp’,
// This value should be NULL when the $team_id_opp var above is set to NULL but it sets to 0 instead.
‘$ShiftedOffNonD1’)"

<?php


// ############################################################
// ### 																										  ###
// ### 																											###
// ### 																											###
// ### 																											###
// ############################################################

// --------------------------------------------------
// ### Assign post values to vars. ###
// --------------------------------------------------
$Month					=	$_POST["Month"];
$Day						=	$_POST["Day"];
$GameOrder			=	$_POST["GameOrder"];
$Stadium				=	$_POST["Stadium"];
$Opponent				=	$_POST["Opponent"];
$NonD1					=	$_POST["NonD1"];
//$Comment				=	$_POST["Comment"];
//$Status					=	$_POST["Status"];
//$Innings				=	$_POST["Innings"];
//$Outcome				=	$_POST["Outcome"];
//$RunsScored			=	$_POST["RunsScored"];
//$OpponentScore	=	$_POST["OpponentScore"];
//$tourn_id				=	$_POST["tourn_id"];
//$TournHomeTeam	=	$_POST["TournHomeTeam"];
//$Bracket				=	$_POST["Bracket"];
//$Finals					=	$_POST["Finals"];
//$TournGameNum		=	$_POST["TournGameNum"];
//$			=	$_POST[""];
//$			=	$_POST[""];

// --------------------------------------------------
// ### Call the configuration file for the MySQL database. ###
// --------------------------------------------------
require('/home/content/59/4519259/html/baseball/conf/config.inc.php');

// --------------------------------------------------
// ### Set the default time zone. ###
// --------------------------------------------------
date_default_timezone_set('America/New_York');

// --------------------------------------------------
// ### Tells session start the name of the session cookie. ###
// --------------------------------------------------
session_name("AdminSession");

// --------------------------------------------------
// ### Start the session. ###
// --------------------------------------------------
session_start();

// --------------------------------------------------
// ### Assign session vars num add, year, and team id to vars. ###
// --------------------------------------------------
$NumAdd		=	$_SESSION["NumAdd"];
$Year			=	$_SESSION["Year"];

// --------------------------------------------------
// ### Set both vars to type integer. ###
// --------------------------------------------------
settype($NumAdd,"integer");
settype($Year,"integer");

// --------------------------------------------------
// ### Set both vars to type integer. ###
// --------------------------------------------------
$ValidRecord	=	FALSE;

// --------------------------------------------------
// ### Require the function to find the game ids for this team and the opponent. ###
// --------------------------------------------------
require('./functions/GameRef.inc.php');

// --------------------------------------------------
// ### Remove empty values from non d1 array. ###
// --------------------------------------------------
$NonD1	=	array_filter($NonD1);

// --------------------------------------------------
// ### Loop through each of the games to be added. ###
// --------------------------------------------------
for ($n = 0; $n < $NumAdd; $n++){

	// --------------------------------------------------
	// ### Assign session var to team id var. ###
	// --------------------------------------------------
	$team_id	=	$_SESSION["team_id"];

	// --------------------------------------------------
	// ### Assign game order, month, day array values to vars. ###
	// --------------------------------------------------
	$Sec	=	$GameOrder[$n];
	$M		=	$Month[$n];
	$D		=	$Day[$n];
	// --------------------------------------------------
	// ### Set type to integer. ###
	// --------------------------------------------------
	settype($Sec, "integer");
	settype($M, "integer");
	settype($D, "integer");	
	// --------------------------------------------------
	// ### mktime (hour, minute, second, month, day, year) Make time stamp. Game order is seconds past midnight Eastern Time. ###
	// --------------------------------------------------
	$GameDateTS	=	mktime(0,0,$Sec,$M,$D,$Year);

	// --------------------------------------------------
	// ### If conditionals for stadium. ###
	// --------------------------------------------------
	if ($Stadium[$n] == 'vs'){
		$StadiumThisTeam	=	'vs';
		$StadiumOpponent	=	'at';
		}
	if ($Stadium[$n] == 'at'){
		$StadiumThisTeam	=	'at';
		$StadiumOpponent	=	'vs';
		}
	if ($Stadium[$n] == 'neu'){
		$StadiumThisTeam	=	'neu';
		$StadiumOpponent	=	'neu';
		}
	if ($Stadium[$n] == 't'){
		$StadiumThisTeam	=	't';
		$StadiumOpponent	=	't';
		}
	if ($Stadium[$n] == 'c'){
		$StadiumThisTeam	=	'c';
		$StadiumOpponent	=	'c';
		}
	if ($Stadium[$n] == 'r'){
		$StadiumThisTeam	=	'r';
		$StadiumOpponent	=	'r';
		}
	if ($Stadium[$n] == 'sr'){
		$StadiumThisTeam	=	'sr';
		$StadiumOpponent	=	'sr';
		}
	if ($Stadium[$n] == 'cws'){
		$StadiumThisTeam	=	'cws';
		$StadiumOpponent	=	'cws';
		}

	// --------------------------------------------------
	// ### Team id for the opponent. ###
	// --------------------------------------------------
	$team_id_opp	=	$Opponent[$n];
	// --------------------------------------------------
	// ### Call the game id reference function. Calculates the game ids for this team and for the opponent. ###
	// --------------------------------------------------
	GameRef($MySQLi,$team_id_opp);

	// --------------------------------------------------
	// ### If month, day, and D1 opponent has been selected. ###
	// --------------------------------------------------
	if ( (!empty($Opponent[$n])) AND 
			 (!empty($Month[$n]))		 AND 
			 (!empty($Day[$n])) 		 AND 
			 (empty($NonD1[$n])) ){
		// --------------------------------------------------
		// ### Since this opponent is a d1 school set non d1 to empty. ###
		// --------------------------------------------------
		$ShiftedOffNonD1	=	'';
		// --------------------------------------------------
		// ### Set valid record to true. ###
		// --------------------------------------------------
		$ValidRecord	=	TRUE;
		}

	// --------------------------------------------------
	// ### If month, day, and non D1 opponent has been input. ###
	// --------------------------------------------------
	if ( (count($NonD1) > 0)	 	 AND 
			 (!empty($Month[$n]))		 AND 
			 (!empty($Day[$n])) 		 AND 		
			 (empty($Opponent[$n])) ) {

		// --------------------------------------------------
		// ### For non d1 opponent empty team id. ###
		// --------------------------------------------------
		$team_id_opp	=	NULL;
		
		// --------------------------------------------------
		// ### Shift off and assign the first value off the non d1 opponent array to non d1 var. ###
		// --------------------------------------------------
		$ShiftedOffNonD1	=	array_shift($NonD1);

		// --------------------------------------------------
		// ### Set valid record to true. ###
		// --------------------------------------------------
		$ValidRecord	=	TRUE;
		}

	// --------------------------------------------------
	// ### If user has input enough this loop to make this a valid record. ###
	// --------------------------------------------------
	if ($ValidRecord	==	TRUE){


		//$team_id 						= $MySQLi -> real_escape_string($team_id);
		//$Year 							= $MySQLi -> real_escape_string($Year);
		//$game_id_this_team 	= $MySQLi -> real_escape_string($game_id_this_team);


		// --------------------------------------------------
		// ### Insert game record into database for this team. ###
		// --------------------------------------------------
		$MySQLi -> query("INSERT INTO Games 
															( team_id,
																Year,
																game_id,
																GameDate,
																GameOrder,
																Stadium,
																Opponent,
																NonD1 )			
																
																VALUES
																
															 ('$team_id',
															 	'$Year',
															 	'$game_id_this_team',
															 	'$GameDateTS',
															 	'$Sec',
															 	'$StadiumThisTeam',
															 	'$team_id_opp',
															 	'$ShiftedOffNonD1')");

	}

	// --------------------------------------------------
	// ### If input is valid and opposing team is a d1 opponent. ###
	// --------------------------------------------------
	if ( ($ValidRecord	==	TRUE) AND (!empty($team_id_opp)) ){
		// --------------------------------------------------
		// ### Insert game record into database for opposing team. ###
		// --------------------------------------------------
		$MySQLi -> query("INSERT INTO Games 
															( team_id,
																Year,
																game_id, 
																GameDate, 
																GameOrder,
																Stadium,
																Opponent,
																NonD1 )			
																
																VALUES
																
															 ('$team_id_opp',
															 	'$Year',
															 	'$game_id_opponent',
															 	'$GameDateTS',
															 	'$Sec',
															 	'$StadiumOpponent',
															 	'$team_id',
															 	'$ShiftedOffNonD1')");

	}

// --------------------------------------------------
// ### Set valid record back to false for next loop. ###
// --------------------------------------------------
$ValidRecord	=	FALSE;

// --------------------------------------------------
// ### Set this var to empty in case next opponent is non d1 team. ###
// --------------------------------------------------
$team_id_opp	=	'';

// --------------------------------------------------
// ### Close for loop. ###
// --------------------------------------------------
}

/*
// --------------------------------------------------
// ### Run the mysql query insert into db. ###
// --------------------------------------------------
@ mysql_query("INSERT INTO Members 
													( remote_address,
														web_ring,
														email, 
														username, 
														password,
														join_date,
														domain_name,
														your_name,
														mailing_address,
														business_name,
														address_locale,
														state_business )			
														
														VALUES
														
													 ('$InternetProtocol',
													 	'$WebRing',
													 	'$Email',
													 	'$UserName',
													 	'$PassWord1',
													 	'$CurrTS',
													 	'$DomainName',
													 	'$YourName',
													 	'$StreetAddress',
													 	'$BusinessName',
													 	'$Locale',
													 	'$DescribeBusiness')");

*/
// --------------------------------------------------
// ### Free the result. ###
// --------------------------------------------------
//$Result->free();


?>

PHP uses what they call “loose” comparisons as default, and isn’t very good at understanding types. For example, if you do this:

<?php
$foo = FALSE;
$bar = NULL;
$baz = TRUE;

if ($foo == FALSE)  echo "Foo is FALSE\
";
if ($foo == 0)      echo "Foo is also 0\
";
if ($foo == NULL)   echo "hang on, Foo is also NULL?\
";
if ($foo == TRUE)   echo "Foo is true\
";
if ($foo === FALSE) echo "Foo is EXACTLY FALSE\
";
if ($foo === 0)     echo "Foo is EXACTLY ZERO\
";
if ($foo === NULL)  echo "Foo is EXACTLY NULL\
";
if ($foo === TRUE)  echo "Foo is EXACTLY TRUE\
";

if ($bar == FALSE)  echo "Bar is FALSE\
";
if ($bar == 0)      echo "Bar is also 0\
";
if ($bar == NULL)   echo "Bar is also NULL?\
";
if ($bar == TRUE)   echo "Bar is true\
";
if ($bar === FALSE) echo "Bar is EXACTLY FALSE\
";
if ($bar === 0)     echo "Bar is EXACTLY ZERO\
";
if ($bar === NULL)  echo "Bar is EXACTLY NULL\
";
if ($bar === TRUE)  echo "Bar is EXACTLY TRUE\
";

if ($baz == FALSE)  echo "Baz is FALSE\
";
if ($baz == 0)      echo "Baz is also 0\
";
if ($baz == NULL)   echo "hang on, Baz is also NULL?\
";
if ($baz == TRUE)   echo "Baz is true\
";
if ($baz === FALSE) echo "Baz is EXACTLY FALSE\
";
if ($baz === 0)     echo "Baz is EXACTLY ZERO\
";
if ($baz === NULL)  echo "Baz is EXACTLY NULL\
";
if ($baz === TRUE)  echo "Baz is EXACTLY TRUE\
";

echo "Foo = $foo\
";
echo "Bar = $bar\
";
echo "Baz = $baz\
";

You will get this output:

Foo is FALSE 
Foo is also 0 
hang on, Foo is also NULL? 
Foo is EXACTLY FALSE 
Bar is FALSE 
Bar is also 0 
Bar is also NULL? 
Bar is EXACTLY NULL 
Baz is true 
Baz is EXACTLY TRUE
Foo = 
Bar = 
Baz = 1

Not always what you expect :wink:

You need to check EXACTLY what you expect from PHP. And don’t set a variable to be equal to the NULL constant before you insert, as that WILL go in as a zero. What you’d need to do is more like this:

$var = 'NULL';
$sql = "INSERT INTO Games ( Opponent ) VALUES ( $var );";

That will translate to the exact:

INSERT INTO Games ( Opponent ) VALUES ( NULL );

that you’re after

If you did this:

$var = 'NULL';
$sql = "INSERT INTO Games ( Opponent ) VALUES ( '$var' );";

This would translate in to:

INSERT INTO Games ( Opponent ) VALUES ( 'NULL' );

and that would try to insert the string “NULL” instead, but since it’s a SMALLINT field it will instead fall back to the type default, which is a zero (and not the NULL that you would expect). This happens when you try to insert an invalid value when you’re not running MySQL in strict mode. If you immediately ran the MySQL command:

SHOW WARNINGS;

You would see somthing like this:

Incorrect integer value: 'NULL' for column 'Opponent' at row 1

well, there has to be some kind of explanation, doesn’t there?

for instance, what was the value that got assigned to these two columns, for which you failed to provide a value, and which don’t have a default –

`game_id` mediumint(8) unsigned NOT NULL, -- this is also UNIQUE
`TournHomeTeam` set('','Yes','No') NOT NULL, 

btw i’m using heidisql as well

game_id = 0, TournHomeTeam = ‘’

Full dataset in CSV:

team_id,Year,game_id,GameDate,GameOrder,Stadium,Opponent,NonD1,ConferenceGame,DivisionalPlay,Comment,Status,Innings,Outcome,RunsScored,OpponentScore,tourn_id,TournHomeTeam,TournRoadTeam,TournGameNum,Bracket,Finals
0,0000,0,0,1,vs,NULL,,,,,,NULL,,NULL,NULL,NULL,,,NULL,NULL,

Are you in strict mode? I’m using the default config that comes with WAMP Server and MySQL 5.5.20

Thanks r937, Antnee, itmitică for responding to my problem.

Yesterday I thought it was a MySQL issue. Today I’m starting to think that it’s more of a PHP issue.

I think what I need to do is go back and make a table with just one column and then just insert one variable at a time until I figure out what’s causing the problem.

Thanks again.

I agree, I think it’s a PHP issue. Don’t worry, we’ve all done it :wink: Just sort out your SETs and make the ENUMs or TINYINTs :wink: (Except where you do actually need multiple options)

I did actually write some code for a previous employer where you could pass in a load of PHP data, whether an object or an array, and as long as you had the same key in your array (or property name in your object) as the DB row name, it would deal with PHP types correctly, so NULL would actually insert NULL instead of 0. Technically I guess it was a form of Object Relational Mapping (ORM) and it did a really good job of properly sanitising data and stopping mistakes like this happening. It’s a shame that I had to leave it behind, as I think it would’ve helped you no end. Although some would argue that it also allows lazy coding :wink:

i honestly don’t know

i installed mysql without touching a thing, so whatever its presets are, that’s what i’m using

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
SHOW VARIABLES LIKE "%mode%";

The first two are blank for me. The last one shows innodb_strict_mode OFF and sql_mode blank

I don’t suppose it’s important, unless you really want to know why it works for one of us and not for the other. Though I couldn’t insert using that query above if there was any data already in the table

not really, no

i’m happy just being able to run normal queries :slight_smile:

[COLOR="#0000FF"]SELECT @@GLOBAL.sql_mode;[/COLOR]
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[COLOR="#0000FF"]SELECT @@SESSION.sql_mode;[/COLOR]
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[COLOR="#0000FF"]SHOW VARIABLES LIKE "%mode%";[/COLOR]
innodb_autoinc_lock_mode  1
innodb_strict_mode        OFF
slave_exec_mode           STRICT
sql_mode                  STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION