Error: duplicate entry '0' for key 'primary' mysql

Question

Error: duplicate entry '0' for key 'primary' mysql

Navigation

#1 by (1 votes)

2

Good evening

Update phpMyAdmin and when I export the database and import it into another server it sends me the following message:

duplicate entry '0' for key 'primary' mysql

I know what it means and what causes it.

But my question is this: In my previous version of phpMyAdmin I exported the databases and imported them without any problem. In my previous version the tables were exported in this format:

CREATE TABLE IF NOT EXISTS 'accion' (
  'id_accion' int(10) unsigned NOT NULL AUTO_INCREMENT,
  'nombre_accion' varchar(45) CHARACTER SET utf8 NOT NULL DEFAULT '',
  'id_modulo' int(10) unsigned NOT NULL DEFAULT '0',
  'url' text CHARACTER SET utf8 NOT NULL,
  'mostrar' int(10) unsigned NOT NULL DEFAULT '0',
  'accion_padre' int(10) unsigned NOT NULL DEFAULT '0',
  'posicion' int(10) unsigned NOT NULL,
  PRIMARY KEY ('id_accion'),
  KEY 'id_modulo' ('id_modulo')
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1210 ;

In the new version of phpMyAdmin, you export it to me this way:

CREATE TABLE 'accion' (
  'id_accion' int(10) UNSIGNED NOT NULL,
  'nombre_accion' varchar(45) CHARACTER SET utf8 NOT NULL DEFAULT '',
  'id_modulo' int(10) UNSIGNED NOT NULL DEFAULT '0',
  'url' text CHARACTER SET utf8 NOT NULL,
  'mostrar' int(10) UNSIGNED NOT NULL DEFAULT '0',
  'accion_padre' int(10) UNSIGNED NOT NULL DEFAULT '0',
  'posicion' int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

--
-- Indices de la tabla 'accion'
--
ALTER TABLE 'accion'
  ADD PRIMARY KEY ('id_accion'),
  ADD KEY 'id_modulo' ('id_modulo');

--
-- AUTO_INCREMENT de la tabla 'accion'
--
ALTER TABLE 'accion'
  MODIFY 'id_accion' int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1210;

How can I configure phpMyAdmin to generate the export in the previous format?

If I export it in MySQL Administrator it works correctly, but I want to solve it with phpMyAdmin

If I manually correct the second export so that it looks like the first one, it works perfectly for me.

After @wchiquito's response , I add:

This is one of the tables:

    CREATE TABLE 'historico_usuarios' (
      'idregistro' int(10) UNSIGNED NOT NULL,
      'estacion' varchar(45) NOT NULL DEFAULT '',
      'fechayhora' datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
      'usuario' varchar(20) NOT NULL DEFAULT ''
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE 'historico_usuarios'
  ADD PRIMARY KEY ('idregistro');
ALTER TABLE 'historico_usuarios'
  MODIFY 'idregistro' int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1260;

And this is a the first insert that I make when I enter the application to save a record of the user's income:

mysql_query("insert into historico_usuarios 
                                (estacion,fechayhora,usuario) 
                            values ('$pc','$fh','$login')");

The fields validated and are not null.

mysql phpmyadmin

asked by damianlema 21.11.2016 в 02:13

source

1 answer

Create Lists with BindingList C # Doubt to create a ModelFactory Laravel 5.3

score 1 · Answer 1

The format change when exporting the table, in principle, should not be the cause of the error: duplicate entry '0' for key 'primary' mysql .

Basically the three (3) sentences of the new version of phpMyAdmin generate a table definition just like the version installed before updating.

Is it possible to show the INSERT statements causing the error?

UPDATE

As you can see in the example, I can not reproduce the problem:

mysql> DROP TABLE IF EXISTS 'historico_usuarios';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE 'historico_usuarios' (
    ->   'idregistro' int(10) UNSIGNED NOT NULL,
    ->   'estacion' varchar(45) NOT NULL DEFAULT '',
    ->   'fechayhora' datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   'usuario' varchar(20) NOT NULL DEFAULT ''
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE 'historico_usuarios'\G
*************************** 1. row ***************************
       Table: historico_usuarios
Create Table: CREATE TABLE 'historico_usuarios' (
  'idregistro' int(10) unsigned NOT NULL,
  'estacion' varchar(45) NOT NULL DEFAULT '',
  'fechayhora' datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  'usuario' varchar(20) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> ALTER TABLE 'historico_usuarios'
    ->   ADD PRIMARY KEY ('idregistro');
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE 'historico_usuarios'
    ->   MODIFY 'idregistro' int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1260;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE 'historico_usuarios'\G
*************************** 1. row ***************************
       Table: historico_usuarios
Create Table: CREATE TABLE 'historico_usuarios' (
  'idregistro' int(10) unsigned NOT NULL AUTO_INCREMENT,
  'estacion' varchar(45) NOT NULL DEFAULT '',
  'fechayhora' datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  'usuario' varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY ('idregistro')
) ENGINE=InnoDB AUTO_INCREMENT=1260 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> INSERT INTO 'historico_usuarios'
    ->   ('estacion', 'fechayhora', 'usuario')
    -> VALUES
    ->   ('127.0.0.1', '2000-01-01 00:00:00', 'user1');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT
    ->   'idregistro',
    ->   'estacion',
    ->   'fechayhora',
    ->   'usuario'
    -> FROM
    ->   'historico_usuarios';
+------------+-----------+---------------------+---------+
| idregistro | estacion  | fechayhora          | usuario |
+------------+-----------+---------------------+---------+
|       1260 | 127.0.0.1 | 2000-01-01 00:00:00 | user1   |
+------------+-----------+---------------------+---------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE 'historico_usuarios'\G
*************************** 1. row ***************************
       Table: historico_usuarios
Create Table: CREATE TABLE 'historico_usuarios' (
  'idregistro' int(10) unsigned NOT NULL AUTO_INCREMENT,
  'estacion' varchar(45) NOT NULL DEFAULT '',
  'fechayhora' datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  'usuario' varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY ('idregistro')
) ENGINE=InnoDB AUTO_INCREMENT=1261 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)