Error: 1265 SQLSTATE: 01000 (WARN_DATA_TRUNCATED)

Question

Well, I want to practice with php and mysql, so I want to make a bbdd and a GUI to control it. Here are the code where I have the problem:

MySql (v5.6.12):

CREATE TABLE IF NOT EXISTS `personaje` (

`id_personaje` int(3) NOT NULL AUTO_INCREMENT,
`nombre` varchar(30) NOT NULL,
`servidor` varchar(25) NOT NULL,
`nivel` int(3) NOT NULL,
`faccion` varchar(10) NOT NULL,
`clase` varchar(25) NOT NULL,
`raza` char(30) NOT NULL,
`profesion1` varchar(20) NOT NULL,
`nivel1` int(4) NOT NULL,
`profesion2` varchar(20) NOT NULL,
`nivel2` int(4) NOT NULL,
`nivel_coc` int(4) NOT NULL,
`nivel_pes` int(4) NOT NULL,
`nivel_arqu` int(4) NOT NULL,
`nivel_prim` int(4) NOT NULL,

PRIMARY KEY (`id_personaje`),
UNIQUE KEY `raza` (`raza`),
UNIQUE KEY `clase` (`clase`),
UNIQUE KEY `prof1` (`profesion1`),
UNIQUE KEY `prof2` (`profesion2`),
UNIQUE KEY `faccion` (`faccion`)
) 
ENGINE=InnoDB

PHP(v5.4.12):

    <?php
   
    
    //Recoger los datos que llegan
    
    $nombre = $_POST['nombreChar'];
    $raza = $_POST['razaChar'];
    $clase = $_POST['claseChar'];
    $servidor = $_POST['servidorChar'];
    $faccion = $_POST['faccionChar'];
    $nivel = $_POST['nivelChar'];
    settype($nivel,"int");
    $prof1 = $_POST['prof1Char'];
    $lvlpr1 = $_POST['lvlpr1Char'];
    $prof2 = $_POST['prof2Char'];
    $lvlpr2 = $_POST['lvlpr2Char'];
    $nivel_coc = $_POST['nivel_cocChar'];
    $nivel_arqu = $_POST['nivel_arquChar'];
    $nivel_pes = $_POST['nivel_pesChar'];
    $nivel_prim = $_POST['nivel_primChar'];
    $muestra = gettype($nivel);
    
     //Conectandonos con la base de datos
   $conexion = mysql_connect(/* ... */);
    mysql_select_db (/* ... */, $conexion) OR die ("No se puede conectar");
           
    //Comprobar que no haya otro personaje repetido
   /* $Consulta_per = "SELECT nombre, servidor FROM personaje WHERE nombre =
        '".$nombre."' && servidor = '".$servidor."'";
    $busqueda = mysql_query($Consulta_per,$conexion) or die ("Error en busqueda " . mysql_error());       
    if (!$busqueda)
        */
    $res = mysql_query("INSERT INTO personaje (nombre, servidor, nivel, faccion, 
        clase, raza, profesion1, nivel1, profesion2, nivel2, nivel_coc, 
        nivel_pes, nivel_arqu, nivel_prim) VALUE ('$nombre','$servidor',
            '$nivel,','$faccion','$clase','$raza','$prof1','$lvlpr1',
                '$prof2','$lvlpr2','$nivel_coc','$nivel_pes','$nivel_arqu',
                    '$nivel_prim')",$conexion)
    or die ("No se pudo insertar " . mysql_error() ." ". $nivel . $muestra );// this line show the error
    echo "Insertado con exito";
    mysql_close($conexion);
    
    ?>

The form that comes is:

    <FORM method="POST" action="crear-personaje.php">
        Nombre <INPUT type="text" name="nombreChar" id="nombreChar" value="Nombre"></br>
        Raza <INPUT type="text" name="razaChar" id="razaChar" value="Raza"></br>
        Facción <INPUT type="text" name="faccionChar" id="faccionChar" value="Facción"></br>
        Clase <INPUT type="text" name="claseChar" id="claseChar" value="Clase"></br>
        Servidor <INPUT type="text" name="servidorChar" id="servidorChar" value="Servidor"></br>
        Nivel <INPUT type="number" name="nivelChar" id="nivelChar" value="1"></br>
        Profesión 1 <INPUT type="text" name="prof1Char" id="prof1Char" value="Profesion1"></br>
        Nivel de profesión 1 <INPUT type="text" name="lvlpr1Char" id="lvlpr1Char" value="1"></br>
        Profesion 2 <INPUT type="text" name="prof2Char" id="prof2Char" value="Profesion2"></br>
        Nivel de profesion 2 <INPUT type="text" name="lvlpr2Char" id="lvlpr2Char" value="1"></br>
        Nivel cocina<INPUT type="text" name="nivel_cocChar" id="nivel_cocChar" value="1"></br>
        Nivel pesca<INPUT type="text" name="nivel_pesChar" id="nivel_pesChar" value="1"></br>
        Nivel primeros auxilios<INPUT type="text" name="nivel_primChar" id="nivel_primChar" value="1"></br>
        Nivel arqueología<INPUT type="text" name="nivel_arquChar" id="nivel_arquChar" value="1"></br>
        
        <INPUT type="submit" NAME="enviar" VALUE="Dar de alta!" id="enviar">
    
    </FORM>

Apache version 2.4.4

Everything must be ok, but... no. When I tried to insert a integer value (like 87 or 1) in the "nivel" field, mysql give me the next error:

"No se pudo insertar Data truncated for column 'nivel' at row 1 1integer", like the comment I wrote in the code.

("No se pudo insertar" means "Cannot insert").

As you can see, I forced the variable $nivel as integer, and PHP recognizes it correctly (that's the reason I put $nivel and $muestra in the die sentence). I tried to change the type "nivel" variable from int (3) to Varchar (3) in MySql, and let me introduce the character, but... I prefer use the type int (the level of a character ever is a integer, obviously).

Why does MySql give me this error? What can I do to solve this?

Your SQL command maybe transforming it as the current SQL format dictates. I would use `sprintf` for the `sql` command in this instance. — Kevin Lynch, Oct 28 '13 at 21:42

score 3 · Accepted Answer · answered Oct 28 '13 at 21:44

3

In your INSERT SQL you've got '$nivel,' - note the comma inside the quotes:

 $res = mysql_query("INSERT INTO personaje (nombre, servidor, nivel, faccion, 
        clase, raza, profesion1, nivel1, profesion2, nivel2, nivel_coc, 
        nivel_pes, nivel_arqu, nivel_prim) VALUE ('$nombre','$servidor',
            '$nivel,', ...

Is that just a typo in the question? If not it could be what's causing your error as MySQL is struggling to turn '87,', for example, into an integer.

answered Oct 28 '13 at 21:44

Clart Tent

1,319
2
9
11

1

Thanks a lot. I can't belive it's so simple... For the next time I read at least 200 times my code. – UnnamedFreak Oct 28 '13 at 22:40
1

Not a problem, those sorts of bugs can be pretty good at hiding! Just to be sure you're aware - we shouldn't really be using the `mysql_*` functions anymore, they've been replaced by `mysqli_*` equivalents. Also make sure you're watching out for SQL Injection vulnerabilities in your code (see http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). – Clart Tent Oct 29 '13 at 06:59

score 1 · Answer 2 · answered Nov 10 '22 at 06:10

1

I faced the same error message while trying to insert a value that was out of the range of an enum-typed column.

answered Nov 10 '22 at 06:10

menoktaokan

346
3
13

Error: 1265 SQLSTATE: 01000 (WARN_DATA_TRUNCATED)

2 Answers2