?
Solved

Zero default value in MySQL Database

Posted on 2012-09-17
3
Medium Priority
?
442 Views
Last Modified: 2012-09-24
I recently learned that when attempting to input a large number into a database field that's set up to accommodate only a certain number of digits, the number that is entered is 2147483647.

Is there another anomaly that occurs that results in a zero being entered into the database? I'm not talking about a zero being in the data that's being inserted. Rather, I'm talking about something that chokes the system resulting a default "0" being entered into the field.

Thoughts?
0
Comment
Question by:brucegust
  • 2
3 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 38407258
Uhh, please post the CREATE TABLE script and tell us which column you're concerned about, thanks.
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1000 total points
ID: 38407274
Maybe you're speaking of something like the issue that is illustrated here (affects 32 bit systems)?

<?php // RAY_integer_overflow.php
error_reporting(E_ALL);
echo "<pre>\n";

// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$dbcx = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $dbcx))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}

// CREATE A TABLE AND ALTER IT TO A HIGH INDEX NUMBER
$sql = "CREATE TEMPORARY TABLE noise ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ) ENGINE=MEMORY";
if (!$res = mysql_query($sql)) die(mysql_error());

$sql = "ALTER TABLE noise AUTO_INCREMENT = 2147483646";
if (!$res = mysql_query($sql)) die(mysql_error());


// INSERT DATA TO ADD TO THE AUTO_INCREMENT INDEX
$kount = 0;
while ($kount < 3)
{
    $sql = "INSERT INTO noise () VALUES ()";
    if (!$res = mysql_query($sql)) die(mysql_error());
    $nid = mysql_insert_id($dbcx);
    var_dump($nid);
    $kount++;
}

Open in new window

0
 
LVL 84

Accepted Solution

by:
Dave Baldwin earned 1000 total points
ID: 38407349
This page http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html lists the sizes and maximum values of the numeric data types available in MySQL.  In addition, if you have specified 0 as the default value in a numeric column that is specified as NOT NULL and you do not enter a value when you do an INSERT, then 0 will be inserted for that column.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month16 days, 1 hour left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question