Clone not working

[marcvander]

Hey,

The clone button on my form is not working. Here is what happens:

After clicking on Clone, changing info, and clicking on Save, my form gets all empty. Here is the error message in the debug console:

Code: Select all

[0] : 
===USER==========

globeadmin

===PDO MESSAGE=== 

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '72' for key 'PRIMARY'

===SQL=========== 

INSERT INTO entreprise         (`entreprise_groupe_id`, `entreprise_telfixe`, `entreprise_ville`, `entreprise_siteweb`, `entreprise_cp`, `entreprise_pays`, `entreprise_contrat_maintenance`, `entreprise_conditions_commerciales_particulieres`, `entreprise_radie`, `entreprise_rachete`, `entreprise_date_ajout`, `entreprise_etat`, `entreprise_statut`, `entreprise_email`, `entreprise_commentaires`, `entreprise_adresse`, `entreprise_nom`, `entreprise_id`)  VALUES ('', '', '', '', '', '', '0', '', '0', '', '2018-05-29 19:09:38', '', '', '', '', '', 'TEST2', '72');

===BACK TRACE====

/var/www/erpversus/nudata.php - line 320 (nuRunQuery)

/var/www/erpversus/nuapi.php - line 50 (nuUpdateDatabase)

Basically my primary key is an INT(11) with AUTO_INCREMENT parameter. So when I click on Clone, it still takes the primary key of the previous entry (in my example above '72'). How can I delete the primary key from the previous entry after clicking on Clone, to prevent the error ?

[toms]

AUTO-INCREMENT is not recommended.

Recommended Table Structure:
https://wiki.nubuilder.net/nubuilderv3/ ... _Structure

[marcvander]

Ok thanks. Though the problem now is that my database has been designed, so if I can avoid changing everything, I would.
I came across this topic : https://forums.nubuilder.cloud/viewtopic. ... ilit=clone

But disabling clone for my display object which displays the current Record Id is of course not doing much. When I click on Save, I still have the PK violation. So I tried this as well, to set my record id after nuIsClone to NULL (because then the auto increment will take place):

Code: Select all

if (nuFormType() == 'edit') {
        if (nuIsClone()) {
           nuSetProperty('record_id', '');
          var p = nuCurrentProperties();
          console.log(p.record_id);
        }
}

or

Code: Select all

if (nuFormType() == 'edit') {
        if (nuIsClone()) {
           $("#RECORD_ID#").val('').change();
        }
}

None worked

[toms]

When I implemented my first nuBuilder project, I also used an AUTO_INCREMENT primary key. One day users reported that entries had not been saved.
I then found out that when entries were created at the same time by different users, already saved entries were overwritten because the same primary key was used.
For this reason I strongly recommend not to use an AUTO_INCREMENT key.

[marcvander]

Ok seems legit

And if I take away auto increment, will the clone work normally then ?

I checked your link regarding the recommended database structure. So I just have to replace my primary keys to varchar(25) without auto increment ?

[toms]

And if I take away auto increment, will the clone work normally then ?

Yes it will. I've never had an issue with cloning.

I checked your link regarding the recommended database structure. So I just have to replace my primary keys to varchar(25) without auto increment ?

Yes, that's it.

Because my table already contained real data at the time of the primary key change, it was a little more complicated to do the change since there were also linked tables.

So basically I had to do the following steps:

The first step is to add a new field, that is going to be your new primary key (assuming that entreprise_id is your current primary key):

Code: Select all

ALTER TABLE `entreprise` ADD `entreprise_id2` VARCHAR(25) NOT NULL AFTER `entreprise_id`;

For existing data, you can generate a random id with UUID():

Code: Select all

UPDATE `entreprise` SET `entreprise_id2`= LEFT(MD5(UUID()),15)

I also had to do this (I can't exactly remember why the following change was necessary - probably to avoid errors)

Code: Select all

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE `entreprise` modify column entreprise_id int(11) not null;
SET FOREIGN_KEY_CHECKS=1;

The last step is to drop the current primary key and add the new primary key

Code: Select all

ALTER TABLE  `entreprise` DROP PRIMARY KEY , ADD PRIMARY KEY (  `entreprise_id2` )

Last but not least, rename entreprise_id2 back to entreprise_id

[marcvander]

Toms, thanks a lot for this tutorial

I am scared of one thing, my current primary keys are all foreign keys in other tables. So if I drop the primary keys, I drop the foreign keys and the links between tables no ?

[marcvander]

Or if they are On Update Cascade, it should update them all ?

[toms]

I would make copies of your production tables and then experiment on those.

[marcvander]

Ok I'm gonna make some tests and let you know