URL to your eFiction: http://www.silmarillionwritersguild.org/archive/home/index.php
Version of eFiction: 3.5.2
Have you bridged eFiction, if so with what?: no
Version of PHP: 5.2.13
Version of MySQL: 5.1.47-community-log
Have you searched for your problem: yes
If so, what terms did you try: subseries, "add series", "add to series", parent series
State the nature of your problem: unable to add more than one subseries to a parent series
Do you have a test account for us? Orodreth, password Finduilas
Recently, an author reported to us that she was encountering an error whenever she tried to add a second subseries to a parent series. While adding one subseries poses no problem, upon trying to enter the second, we are getting the following MySQL error:
A fatal MySQL error was encountered.
Query: INSERT INTO fanfiction_inseries (seriesid, subseriesid, inorder) VALUES('53', '8', '2')
Error: (1062) Duplicate entry '0-53' for key 'PRIMARY'
This issue was addressed on another post:
https://efiction.org/forums/index.php?topic=6030.0
And a solution offered. Today, I tried the solution suggested on that thread on our test site. (The test site can be found at http://www.silmarillionwritersguild.org/mirror2/index.php; the test account there is the same as for the main site.) When I tried to add a second subseries to a parent series, I got a different mySQL error:
A fatal MySQL error was encountered.
Query: INSERT INTO fanfiction_inseries (seriesid, subseriesid, inorder) VALUES('3', '2', '2')
Error: (1062) Duplicate entry '0' for key 'sid'
Also, I manage two 3.5.2 sites. This is an issue for both sites.
Thank you in advance for any help or suggestions anyone can offer. 🙂
Hmm, sounds like you modified your fanfiction_inseries? Can you post the current table schema?
My guess is the reason it didn't work originally is because sid + seriesid is was set as a primary key, and primary keys have to be unique. For whatever reason a second subseries is trying to enter a row where one already exists with that sid & seriesid (I think that would make sense, rather than having two entries in subseriesid for the parent series row). Is there a subseries without stories?
Sorry if this is rambly and not that helpful; it's late for me. I don't have any suggestions for fixes because I don't know the code well enough and don't want to start messing with keys.
Thanks for the reply, Lyndsie. From my understanding (and that is rather shallow, as my grasp of mySQL is really, really basic!), the solution proposed in the thread I linked removed the primary key and set sid, seriesid, and subseriesid as unique. (And, yes, it was the fanfiction_inseries table; sorry for not being clearer on that.) It made sense to me that that would work, but it hasn't so far.
Both subseries have one story each in them for testing purposes on the mirror2 test site ( http://www.silmarillionwritersguild.org/mirror2/browse.php?type=series). The member on the main site who has been trying to add a handful of subseries to a parent series has several stories in each of her subseries.
If, given all of that, it would be helpful to post the table schema for either site, I'll do that ... as soon as I figure out how! 😛
What indexes do you have for that table? It looks like you have a duplicate key for an index named sid which I do not have on my end.
Being a fellow admin for that archive, here are the schema's of both databases.
This one is from the test site where Dawn had followed the suggestions made in the other threads, the schema of the inseries table:
This is how it looks like in the live database, we did a clean install of the latest efiction version in June and let the script set up the tables, so how sid snuck in there Tammy, I don't know. I've been - together with Dawn - very puzzled about this.
This is the schema of the inseries table in our live database:
Take the primary key OFF sid. You should have 2 indexes.
Primary:
sid
seriesid
subseriesid
INORDER
seriesid
inorder
To recap, take off primary key of sid, but keep a primary key on series and subseriesid? Or should only have inorder and seriesid a primary key + index?
You want 2 indexes.
The primary should include sid, seriesid, and subseriesid.
The second one should just be an index and it should include seriesid, and inorder.
I ran the following queries on the table inseries:
ALTER TABLE `silmaril_swgvault`.`fanfiction_inseries` DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `sid` , `seriesid` , `subseriesid` )
ALTER TABLE `silmaril_swgvault`.`fanfiction_inseries` ADD UNIQUE ` INORDER` ( `seriesid` )
ALTER TABLE `silmaril_swgvault`.`fanfiction_inseries` DROP INDEX ` INORDER` ,
ADD UNIQUE ` INORDER` ( `seriesid` , `inorder` )
Then I created two series and tried to add one as a subseries to the other:
A fatal MySQL error was encountered.
Query: INSERT INTO fanfiction_inseries (seriesid, subseriesid, inorder) VALUES('5', '4', '1')
Error: (1062) Duplicate entry '0' for key 'sid'
Hmmm
I had a look at the indexes and made a screenshot, I think it's a mess there but I want to make sure what I index I can delete and what not... and for the record Tammy, thank you so much for your patience with all of this!
These are the indexes of the database where we ran previously suggested solutions on:
These are the indexes and are left untouched since the 3.5.2 install in June, just for the record:
You need to drop all the indexes except the 2 you just added. PRIMARY and INORDER.
