Primary key (ID) stays 0 (zero) when adding content...What the!?

[SmashD]

I've tried soo much, but after inserting my db backup I am unable to add ne menu items/sections/categories/content/whatever because the ID stays at 0 (imo the id for new content, right?).

I've also tried to re-number all menu items/sections/categories/content/whatever ID's to this: 1,2,3,4,....,xx
and changed the relations inbetween by hand. The EXISTING articles,... work fine, but I am unable to add anything.
Please help me out of this situation, I don't want do change the IDs by hand every time. Aaaaarf

[leolam]

what do you mean by inserting your databasee back up? did you had a mambo site before and upgraded opr what is the db-insert for?

Leo

[Tonie]

Does the content table still have the 'Auto increment' setting? This setting will automatically create a new id which is 1 bigger than the last one used.

[SmashD]

Does the content table still have the 'Auto increment' setting? This setting will automatically create a new id which is 1 bigger than the last one used.

(image removed by SmashD)

I am new to all this so what should be written here? "On"? "1"?
And the next question:
Which of the joomla 1.0.8 stable tables do need the autoincrement setting?
Maybe I've saved the table backup (as mysql instructions) wrong...

[Tonie]

If there needs to be a value, it should be 1. This will be the number of values added when a new entry is added in the table. Most of the tables need the auto_increment setting. When looking at a normal download, take a look at the /installation/sql/joomla.sql file. The second line (or thereabout) of each table will have the auto_increment setting.

To be sure if a table has auto_increment enabled, you can also do the following from one of the SQL windows in Phpmyadmin:

Code: Select all

describe jos_content

for example.

[SmashD]

Thanks alot! I've compared all table settings with mine and changed all table to auto_increment with the max id + 1 as current auto_increment value. Now everything seems to work properly.

But one question still remains:

How should I backup my DB next time to NOT loose such important settings?!
For this time I've used phpMyAdmin's Export function including drop table.Wrong?

[Tonie]

No, this is right. Either an export or SQLdump should be perfect. One thing that might be tricky is when you are using different Mysql versions for restoring a database, this can give some trouble due to functionality in newer versions or a different character set.

[SmashD]

Thanks alot again for your fast help! I'll keep your informations in mind.

[Pathos]

How did you change your database to get auto-implement? I have this problem on one site and didn't get it to work yet. Now i'm just installing everything again/fresh...but the database-data i try to import still gives the problem...

[SmashD]

I did a complete re-check of the whole database sql files. First, my exported data, second, the fresh database "skeleton" that comes with the joomla package. Everywhere where it is needed I've added the auto-implement value plus a number exactly one higher than the highest idea of the table. (eg if you have content that has IDs from 0 to 49 I did an autoimplement with value 50)

VERY annoying and time consuming, of course, but you'll get your data back. Good luck!

[Tonie]

Do you have access to Phpmyadmin? When selecting a table, you can browse for 'structure'. On the Id column, there should be a dropdown on the right where you can choose auto increment (all done out of the top of my head, so I hopefully have all steps right).

[Pathos]

Thanks Antonie,

I'll look into that. Although i have set up the complete site again by copy paste content and datablocks per block... That worked for some large data (ext calendar)........ Now getting it from my server to another server (i hate sites on other servers with other versions of software and settings :-)

[Pathos]

That works fine :-)

[boswell255]

Hi I'm having the same trouble and unfortunately I'm not that experienced with MySQL

I'm using myphpadmin

I found the table jos_content and the changed id to auto increment, but it gives me the error message "#1067 - Invalid default value for 'id'"

There is a setting called Default next to the auto increment drop down, and this is set to 0.

I have tried changin this to 1 but i still get the same message. I can see no information as to what id number i am up to.

Also do i just need to do this for jos_content? i have 90 tables :S i hope its not for all of them

[twerp]

RIGHT! me too! I'll give this a Go... to try to fix.... my prob with 1.1.10 is it appears IDENTICAL.. LOTS of weirdness...

FOR ME: ALL the auto-increment bits are missing in ALL tables ....it appears on first comparison to virgin database for 1.1.10 and the one I exported from my messed up LIVE and Localhost versions of site...

More info or Tips on this appreciated... I rountinely move the database between localhost and live site... I DO add the drop tables option... though I'm now thinking I need to find a trick as I set the mySQL export compatibility ro version 4..... hmmm ...what if that's it as the live site is 323 or something and local host I believe I am 4


STILL, never had this before...


ANY GURU tips appreciated....

I think this may be about to happen to a LOT of us ?


Could be good..... That's how we become more expert!!!!!!!!!!??

That said , so much other stuff to be doing, so getting quickly-a bit-more-expert should be great

SO: any HELP on this one as to HOW we ADD ALL the missing auto-increment fields would be REALLLY appreciatted....obviously!

[boswell255]

OK I managed to find the content id I think. When I browse the table jos_content, in the id column the highest number there is 13.

But I tried to change the default auto-increment to 14 and i still get the same error. I tried 12, 13 and 15 and even 100 and still get the same message. I'm not ven sure if I'm changing the right thing 

And MySQL help is absolutely terrible for explaining the error messages.

[Tonie]

When browsing through Phpmyadmin, the auto_increment value should be available on the "Extra" field in the structure of a given table, is this the case?

[boswell255]

Unfortunately the extra field is blank. (see first attachment)

when i click the pencil (edit mode i presume, can you tell i know nothing about MySQL )
there is a drop down box with auto-increment. However setting this casues the error to appear (see second attachment)

I'm completely at a loss 

Thanks for the help

[Tonie]

What happens if you leave the default field empty, and then try? The auto_increment value should know where it starts and finishes.

[boswell255]

AHA!!!


Thank you!


It now appars in the extra column.

although I'm still not sure what other people were talking about when setting the id values?

For instance when SmashD said

eg if you have content that has IDs from 0 to 49 I did an autoimplement with value 50

I can see no text box or similar for this?


Thanks a bunch anyway!

Edit: I have 90 tables, will i have to do this with all of them? some have diferent names such as "bid" for banner ID, should they still be auto-inc. ?

[Tonie]

The easiest to check with tables should have auto_increment values is to download a full joomla version, and extract it. Open the file (in wordpad or other text editor, not notepad) /installation/sql/joomla.sql and search for auto_increment. This will give you the tables as well.

[boswell255]

Ok I think i managed to fix everything. but I'm not sure if this has created another problem which I am having (see this topic http://forum.joomla.org/index.php/topic,75214.0.html )

Thanks again 

[twerp]

Having LOST every instance of the word auto_increment from both my localhost AND live databases of site I am VERY keen to learn more about this! NOT a big deal relatively speaking to ADD to word  'auto_increment'  to the END of the first [usually] line of many/most of the TABLES in a copoy of the database then reupping it with phpmyadimn or similar...

is that RIGHT?


the problem is:

WHY did it happen : I think due to the way and options/versions I saved and loaded - [import/export] the database with... things like exporting with, add drop tables and specifying mySQL version 4 [while exporting] are things I'll look into... and learn about ...Also, just the different phpmyadmin version and HOW I imported/exported in a DIFFERENT way [spot in the program even] in one as compared to the other phpmyadmin ..

What about OTHER components etc. that ADD tables to DATABASE: it's one thing to compare and therefore find OUT what a virgin Joomla database is like and where auto_increment is in it.. then there's the OTHER components etc. that ADD auto_increment to the database it THEIR tables...

Thinking that backing up the database a LOT is important... and also maybe getting familiar WITH the components etc. more ... by unzipping their files to see what ANY .sql files are like...


All in all this is gonna be great to understand more.... as it's critical to know the database...


SO: anyone have a workflow or comments on why this happens or MOST of all... the cleverest ways to fix... WHEN one has MORE than a stock JOOMLA database.... ie. there will be other probs. even after adding the auto_increment word back to the forst line [usually] of the tables in database... as there are OTHER auto_increment lines leasewhere...



bit of a ramble, crazy busy at the momoent many have to come back to clean this up, sorry.

ALL ideas'thought obviously of interest...

[boswell255]

What about OTHER components etc. that ADD tables to DATABASE: it's one thing to compare and therefore find OUT what a virgin Joomla database is like and where auto_increment is in it.. then there's the OTHER components etc. that ADD auto_increment to the database it THEIR tables...

If you're worried about components databases, then create a fresh installation of joomla on another part of your site, and add all the components which were on your original site. Then you can just go through in phpmyadmin, write everything down which needs auto increment, and make the necessary changes. It actually doesn't take as long as you might expect.

[twerp]

Boswell! Yeah, it's ben pretty cool here for 2 day. and, many thanks!
SO: YES, it IS fun and easy... I am working localhost [using uniformserver] and then I UP the database which is where I think MAYBE my missing auto_increment [EXTRA] began...

When I WAS exporting the database in EITHER direction with phpmyadimn I would ADD drop tables and select compatibility with mysql 4.

anyways.. I'm playing with 3 prorams now  sqlYOG  -EMS's mysql manager - mySQL front

They allow syncronization.

WHEN I use sqlYOG [perhaps my fav] I now get ONE error ONLY after syncronization:

`jos_core_acl_groups_aro_map`        3        3  WARNING: No primary key found in table `jos_core_acl_groups_aro_map`. This might result in error.   

Everything still works though  I WOULD obviously like to deduce what this error IS...

ANY ideas anyone??

I will soon be creating a DEMO localhost FULL install of 1.1.10 and ALL components that add tables to the database..

this will be VERY good for learning and essentisal IF I have any future probs...

SO: I would like to ask everyone:

1) KNOW what's causing this error?

`jos_core_acl_groups_aro_map`        3        3  WARNING: No primary key found in table `jos_core_acl_groups_aro_map`. This might result in error.

2) Any thought on adding options to a cpanel export of the database.
As mentioned I WAS adding 2 options in the cpanel export.  DROP TABLES  and [compatibility drop down] mySQl 4

WHAT SEEMED TO HAPPEN:
auto_increment problem [they ALL disappeared!] has been a 2 day thing, SO far!.... though has added MUCH to my knowledge...

ERROR should be fixed though..YET,, it looking at virgin install database AND non installed .sql file found in unZIPPED/tarred 1.1.10 joomla files I cannot YET see what is different of what is needed to stop error in > `jos_core_acl_groups_aro_map`

There does NOT seem to be any need to add  auto_increment field at all...


Help?

hehehe

THANKS again ..

[twerp]

Hi.. Boswell.... Hi ALL! Whew ...good fun... Anyone know HOW un-normal this is... still searching here..

WENT thru with CPanel and found all this... it runs,  tho there are Explanaion mark icons [in a triangle shaped icon] near the indexes part of Cpanel..and THES errors
So BELOW is a cut and paste of every table that has weirdness and then > the error message.


jos_categories  > More than one INDEX key was created for column `section`

jos_core_acl_aro_groups > More than one INDEX key was created for column `parent_id`

jos_core_acl_aro_sections > More than one UNIQUE key was created for column `value`  More than one INDEX key was created for column `hidden`

jos_core_log_items > No index defined!

jos_core_log_searches > No index defined!


jos_stats_agents > No index defined!




Good fun this.... Is this VERY un-normal... going to look into it more now...

YIKES! I feel like a database cowboy hack I'm afraid...

[twerp]

Yeah, this seem...er... normal...

Loaded in the SQL data from 1.1.10 which didn't even rename the table to jos_ though it seems all data OK..
Now I just wanna suss out the:

WARNING: No primary key found in table `jos_core_acl_groups_aro_map`. This might result in error. 

When I do the SYNC between localhost and hosted space

ANY ideas on all this...

I think this is ALL very cool to be stressing with as I have had to reinstall JOS in the past and it looks like now I can get dirty with the database and avoid that...

woohoo!

-gulp-

[twerp]

NEW INFO:

it appears that when i EXPORT from hosted space cpanel and LEAVE the "add auto-increment' option CHECKED as it is by default on mine.
The RXPORT results in the auto_increment being REMOVED from the usual places up top and ADDED to the last line of each table As below...


Still sussing... VERYVERY useful to me this ....um.... perhaps!



TYPE=MyISAM ROW_FORMAT=DYNAMIC AUTO_INCREMENT=11 ;

[twerp]

FURTHER TESTS have revealed to Me anyways... THAT:

WHEN exporting via phpmyadmin  IF  as I've been doing i set the EXPORT SQL COMPATIBILITY drop down to  MYSQL40

THIS will REMOVE the  auto_increment line from the exported database...


MOST interesting....

SO: by leaving the default Add AUTO_INCREMENT value  under phpmy adimn's STRUCTURE menu it add's this bit to the last of every table

>  ) TYPE=MyISAM AUTO_INCREMENT=1 ;

I believe a NEWLY [not yet exported database will just say:
) TYPE=MyISAM

at the end of most every table ...have yet to totally check...


NOW... it's under the SQL OPTIONS part of phpmyadmin EXPORT page that I would always set.

SQL export compatibility:  to MYSQL40

THIS is what REMOVES the auto_increment line in the exported and then imported database for ME.

I BEGAN doing this as I was getting that RED error message about ...well...can't recall.. though setting the COMPATIBILITY to MYSQL40 seem to slove it...


NOW I'm thining it's just IMPORTANT to DROP all tables BEFORE IMPORTING your database..

which is obvious... and well... not sure IF I'll get errors now that I WILL NEVER set COMPATIBILITY again in phpmyadmin..




Time for more testing ....though I now know WHERe to add the  auto_increment line at the top of MOSt every table [not all though!]

AND I NOW know WHAt caused the problem


Thanks all! Cool Boswell!

wooHoo.... who!

??:)