MySQL default values not working as expected

Question

Hi friends from SO!

This code used to be working, but it seems like it stop working when I cloned the database. There's a table called maps, which has some default values, like maximum. As far as I understand, if my INSERT code has no value, then, the dafault value will be used.

This is what I'm doing:

These values might be there, as they might not be there.

$scan->maximo_exploracion   = addslashes($_GET['scan_maximo_exploracion']);
$scan->proceso_analisis = addslashes($_GET['scan_proceso_analisis']);
$scan->email_user           = addslashes($_GET['scan_email_user']);
$scan->email_owner          = addslashes($_GET['scan_email_owner']);

Then:

db_ask($db_resonance_slave, "
INSERT INTO mapas
SET
usuario_id              = '$user->id',
maximo_exploracion      = '$scan->maximo_exploracion',
proceso_analisis        = '$scan->proceso_analisis',
email_user              = '$scan->email_user',
email_owner             = '$scan->email_owner',
max_proc                = '5',
proceso_exploracion     = 'esperando',
direccion               = '$scan->address',
dominio                 = '$scan->dominio',
dominio_id              = '$dominio->id',
server_address          = '$scan->server_address',
server_zone             = '$scan->zone_name',
server_zone_code        = '$scan->zone_code',
server_response_speed   = '$scan->server_response_speed'
");

Now, a few days ago, with the same code, if $scan->maximo_exploracion didn't have any information, the record would be inserted with the default value which is 100.

At this moment, it's appearing blank. Which, of course, is causing problems.

Any ideas?

Thanks! Chris;

The Table:

CREATE TABLE `mapas` (
`id` INT(12) NOT NULL AUTO_INCREMENT,
`usuario_id` VARCHAR(256) NOT NULL,
`maximo_exploracion` VARCHAR(256) NOT NULL DEFAULT '100',
`max_proc` VARCHAR(256) NOT NULL,
`open_proc` VARCHAR(256) NOT NULL,
`hora_lanzado` VARCHAR(256) NOT NULL,
`direccion` VARCHAR(256) NOT NULL,
`dominio` VARCHAR(256) NOT NULL,
`dominio_id` VARCHAR(256) NOT NULL,
`idioma` VARCHAR(256) NOT NULL,
`server_address` VARCHAR(256) NOT NULL,
`server_zone` VARCHAR(256) NOT NULL,
`server_zone_code` VARCHAR(256) NOT NULL,
`server_response_speed` VARCHAR(256) NOT NULL,
`proceso_exploracion` VARCHAR(256) NOT NULL DEFAULT 'esperando',
`proceso_analisis` VARCHAR(256) NOT NULL DEFAULT 'esperando',
`email_owner` VARCHAR(256) NOT NULL DEFAULT 'False',
`email_user` VARCHAR(256) NOT NULL DEFAULT 'True',
`eliminado` INT(12) NOT NULL DEFAULT '0',
`creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)

COMMENT='Almacena mapas, los mapas son ordenes de exploración y análisis para un dominio específico.' COLLATE='latin1_swedish_ci' ENGINE=InnoDB;

If all you changed was on DB side, why not post all information regarding the DB side? — fancyPants
– fancyPants, Commented Jun 30, 2014 at 11:15
Mr / Miss fancyPants, that's absolutely logical, and it's what I've done just now. — Chris Russo
– Chris Russo, Commented Jun 30, 2014 at 11:19
And this is from the cloned database or the original one? Just to be sure... — fancyPants
– fancyPants, Commented Jun 30, 2014 at 11:21
As I wasn't expecting this, I removed the original one. This is from the cloned database. This is a fragment of a web crawler, as it was getting too big, I divided the database, creating a master / slave scheme. However, now is doing this behavior... — Chris Russo
– Chris Russo, Commented Jun 30, 2014 at 11:27
My suspicion is that your script is setting maximo_exploracion to blank rather than NULL. As blank is not null it isn't inserting the default value. — Kickstart
– Kickstart, Commented Jun 30, 2014 at 11:37

xQbert · Accepted Answer · 2014-06-30 13:51:41Z

1

Perhaps: http://www.noelherrick.com/blog/mysql-quirk-not-null-defaults

It indicates using an extended insert (where you specify multiple rows in an insert statement), that you can bypass this default value; a warning is provided instead of errros. Yet single insert statements throw an error.

This can be avoided presently by

Set sql_mode = "strict_all_tables";

Then use the the default keyword, which will insert that column’s default value.

answered Jun 30, 2014 at 13:51

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xQbert Over a year ago

According to post on 2ndary question... this didn't resolve the issue: stackoverflow.com/questions/24491008/…

Oli · Accepted Answer · 2014-06-30 11:42:06Z

0

As suggested in the comments, you're passing a blank instead of NULL. Try this:

if (isset($_GET['scan_maximo_exploracion']) && $_GET['scan_maximo_exploracion']) {
    $scan->maximo_exploracion   = addslashes($_GET['scan_maximo_exploracion']);
}

answered Jun 30, 2014 at 11:42

Oli

2,5043 gold badges29 silver badges48 bronze badges

7 Comments

Chris Russo Over a year ago

Thanks for your help Oli, I'm not sure, but since the $_GET['scan_maximo_exploracion'] is not assigned at all, the representation $scan->maximo_exploracion should be null, not empty.

Chris Russo Over a year ago

I would like to preserve the PHP as it is, since I know it's functional... or well, it used to be. However, an approach like yours could be -- if ($scan->maximo_exploracion == '') { unset($scan->maximo_exploracion); }

Oli Over a year ago

addslashes() will return a string, regardless of the contents of $_GET right now.

Chris Russo Over a year ago

Addslashes() removed - (kids: on development environment just for testing proposes) and it's still behaving the same, the variable is 100% clear, and the MySQL doesn't behaves as expected.

Oli Over a year ago

What happens when you remove "maximo_exploracion = '$scan->maximo_exploracion'," from the db_ask() ?

|

Collectives™ on Stack Overflow

MySQL default values not working as expected

2 Answers 2

1 Comment

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related