Hello,

I keep looking for where the problem comes from and I've some questions

The problem seems to come from id thas are not unique in some table.

What I don't understand is why now ?

I've got a question :

• Should all tables have the value 'AUTO_INCREMENT" ?

I have some table with this value, some other without.

And If I look for a new install of Projeqtor, there are table with and table without.

And in one of my test environnement, all the table have this value.

So, I don't really understand 🙁

Is it a stupidity if I "force" this value AUTO_INCREMENT to all tables ?

Should all tables have the value 'AUTO_INCREMENT" ?

Yes.
id for all columns should be 'AUTO_INCREMENT"

And If I look for a new install of Projeqtor, there are table with and table without.

This is not normal. All should have AUTO_INCREMENT on id.
I just tried new install, and can confirm that all tables are correct, with AUTO_INCREMENT on id.

Is it a stupidity if I "force" this value AUTO_INCREMENT to all tables ?

This seem to be a solution.
But you must also identify why you get tables without AUTO_INCREMENT.
Maybe version of MySql ?
Or specific configuration ?

Hello,

Effectively, all tables coming from a newx install have AUTO_INCREMENT on the "id" field.

I have an environnement that I considered like new install but I have certainly make some things that altered the tables....but why ? I don't know for the moment.

I'm doing tests in a test environnment to understand what happend, how and in which circumstances.

Anyway, I resolved my proble on my production environment :

• Dump from my production database (only data) and structure in an other dump
• Edit the dump to find and supress all the id=0 (there was many in tables accessright, affectation and assignment
• Supress the entire production database
• Install a new environment on an other server, with the same version and plugins
• Dump database structure from this new environment
• Edit this dump to verify AUTO_INCREMENT and PRIMARY KEY , and set AUTO_INCREMENT=1 for all
• Import this "modified" structure dump in my production environment
• Import the data dump modified in this new "structure"
• Verification

Everithing is operationnel again (I lost some assignments (probably those with an id=0))

Now I will do tests to try to understand what's happend ( problem during an update from a version to another ? other tihings ?) : I tell you more in a few days.

Thanks for help

Happy to know your retrieved your data.

I am interested in cause if you find it, possibly to post a hint to avoid this happen for other users.

Hello,

After some tests, PhPMyAdmin (version 4.3.13) seems to be the responsible.

If I export the structure (or structure and data) of my Database with this version, I don't have the notion of Primary key and AUTO_INCREMENT :blink:

CREATE TABLE IF NOT EXISTS `prjaccessprofile` (
  `id` int(12) unsigned NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `description` mediumtext,
  `idAccessScopeRead` int(12) DEFAULT NULL,
  `idAccessScopeCreate` int(12) DEFAULT NULL,
  `idAccessScopeUpdate` int(12) DEFAULT NULL,
  `idAccessScopeDelete` int(12) DEFAULT NULL,
  `sortOrder` int(3) DEFAULT NULL,
  `idle` int(1) unsigned DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

The same table, with an export with an other version of PhpMyAdmin (here an older version 4.1.6) give this result, correct

CREATE TABLE IF NOT EXISTS `prjaccessprofile` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `description` mediumtext,
  `idAccessScopeRead` int(12) DEFAULT NULL,
  `idAccessScopeCreate` int(12) DEFAULT NULL,
  `idAccessScopeUpdate` int(12) DEFAULT NULL,
  `idAccessScopeDelete` int(12) DEFAULT NULL,
  `sortOrder` int(3) DEFAULT NULL,
  `idle` int(1) unsigned DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;

My production environment is in version 4.3.13 and my test environment is in 4.1.6.

Before any change of version, I make a backup (normal 🙂 ), with a dump executed from PhpMyAdmin (3 dumps : structure, data, structure and data).
Few weeks ago, the update was not good (problem with formatting - see ticket in the forum). so I restored my precedent database version......and the problem come from this restoration

The dump did'nt have primary key and AUTO_INCREMENT and my base was corrupted :woohoo:

I can reproduce this problemon all my test environment. I'm interested to know if other people can also reproduce it ?

Thanks

Hi,

I never produced with phpMyAdmin 4.4.4, 4.6.4 or mySqlDumper 1.24.4

Thanks for sharing, it may save other users.