 |
Check the storage engine used in your existing tables. It should be InnoDB. There should be no tables using MyISAM engine. If this is the case, your database was somehow broken before. The transition from MyISAM to InnoDB was performed in 0.17.4. |
 |
I think you may be right... Here is what I found after a command I found via google. mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'racktables'; +---------------------+--------+ | TABLE_NAME | ENGINE | +---------------------+--------+ | Atom | InnoDB | | Attribute | MyISAM | | AttributeMap | InnoDB | | AttributeValue | MyISAM | | CachedPAV | InnoDB | | CachedPNV | InnoDB | | CachedPVM | InnoDB | | Chapter | MyISAM | | Config | InnoDB | | Dictionary | MyISAM | | File | MyISAM | | FileLink | MyISAM | | IPv4Address | InnoDB | | IPv4Allocation | InnoDB | | IPv4LB | MyISAM | | IPv4NAT | MyISAM | | IPv4Network | InnoDB | | IPv4RS | MyISAM | | IPv4RSPool | MyISAM | | IPv4VS | MyISAM | | LDAPCache | MyISAM | | Link | MyISAM | | Molecule | InnoDB | | MountOperation | InnoDB | | Port | MyISAM | | PortAllowedVLAN | InnoDB | | PortCompat | InnoDB | | PortInnerInterface | MyISAM | | PortInterfaceCompat | MyISAM | | PortNativeVLAN | InnoDB | | PortVLANMode | InnoDB | | Rack | InnoDB | | RackHistory | InnoDB | | RackObject | MyISAM | | RackObjectHistory | InnoDB | | RackRow | InnoDB | | RackSpace | InnoDB | | Script | InnoDB | | TagStorage | MyISAM | | TagTree | MyISAM | | UserAccount | MyISAM | | UserConfig | InnoDB | | VLANDescription | InnoDB | | VLANDomain | InnoDB | | VLANIPv4 | InnoDB | | VLANSTRule | InnoDB | | VLANSwitch | InnoDB | | VLANSwitchTemplate | InnoDB | | VLANValidID | InnoDB | +---------------------+--------+ 49 rows in set (0.00 sec) |
|
|
The above results were after the failed upgrade, in checking the results before, all of our tables were MyISAM. I converted all of them, and then ran the upgrade.. Here are the results, (new errors) For 0.18.1: ALTER TABLE AttributeValue DROP FOREIGN KEY `AttributeValue-FK-object_id` -- Error on rename of './racktables/AttributeValue' to './racktables/#sql2-974-2cd' (errno: 152) ALTER TABLE Link DROP FOREIGN KEY `Link-FK-a` -- Error on rename of './racktables/Link' to './racktables/#sql2-974-2cd' (errno: 152) ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE -- Cannot add or update a child row: a foreign key constraint fails (`racktables`., CONSTRAINT `Link?FK?a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE) ALTER TABLE Link DROP FOREIGN KEY `Link-FK-b` -- Error on rename of './racktables/Link' to './racktables/#sql2-974-2cd' (errno: 152) ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE -- Cannot add or update a child row: a foreign key constraint fails (`racktables`., CONSTRAINT `Link?FK?b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE) ALTER TABLE Port DROP FOREIGN KEY `Port-FK-object_id` -- Error on rename of './racktables/Port' to './racktables/#sql2-974-2cd' (errno: 152) For 0.18.7: DELETE FROM Dictionary WHERE dict_key BETWEEN 1 AND -- You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 Is there a way to re-run the old upgrade scripts? I have the 0.17.5 code, which appears to go from 0.16.4 to 0.17.5.. I'm guessing I SHOULD be able to manually run those commands in the upgrade.php script, and those commands that error are okay, and those that do not are changing things we need.. Sadly it looks like the previous owner of this application did a partially failed upgrade, and as the product continued to work, nothing was done or said.. |
|
|
There is no change that previous upgrade batches will work without any errors now. But you can force the execution of these batches by rolling back your installation version: UPDATE Config SET varvalue='0.17.3' WHERE varname='DB_VERSION'; Back up before doing this. |
|
|
FYI, this entire system I am working with is a new system with a database dump from our production system.. I was able to get a successfull upgrade, but still with errors.. It appears that there is something went wrong in our database prior to 0.16.4, and the oldest version i have only upgrades as far back as that.. What I did to yeild the "working" version of 0.20.4 (which did error at least once) was first replay all the changes from 0.16.4, which seemed to only update a few versions, then get stuck. Then update all of the changes from 0.17.1, which successfully took us to 0.17.5 with 2 errors that seemed to not be because it was replying something that already did. (not a duplicate name etc) Now I check the tables, and about half are still MyISAM, so I manually convert all of them... (I'm guessing some of them were converted at an earlier version, but it wasnt until the version you stated that they were completely converted) At this point I upgraded to the interim version 0.18.7, but the browser never showed a completed upgrade, so i checked the version to see how far it had gotten after waiting over an hour.. It reflected 0.18.7, so I went to the page, and it loaded up and worked.. Sadly I don't know if anything errored out because no output.. Then I tried the 0.20.4 upgrade, and it had a single error.. and in testing the features, I can find nothing that doesnt work.. and it seems to be much faster than the original version was.. When I get a few minutes, I'll repeat the process, and see if i can get some specific errors.. I don't know if its possible to download earlier updates if I'm missing schema changes from previous upgrades, or if the database keeps a record of its past versions that it ran? Ideally I'd like to get a clean upgrade, but functional will do if needed.. |
|
|
+ infrastation@ Denis, could you please review this case? I don't know how the upgrades were performed before 0.16.4. |
 |
Which version the production copy of RackTables is? Is it working? |
|
|
This issue is likely to be closed unless you provide the feedback. |
|
|
Sorry, I was forced to put this aside for a few days.. Our Production copy of racktables that I am trying to upgrade is running with what is reported as 0.17.5, however there is ample evidence to suggest some of the upgrade proceedure did not go 100%. All of the tables are currently set to MyISAM.. I was able to get a "almost" clean upgrade in a cloned copy by replaying the upgrades present in the upgrade script packaged in 0.17.5, and then manually converting any tables which are still MyISAM. Then I ran the interim upgrade, and then the finale upgrade.. More details shortly.. I am replaying this proceedure. The final upgrade in my test upgrade had a single error. |
|
|
In replying the changes from 0.17.1 -> 0.17.5, the output is all expected errors except these two errors for 0.17.4: The following queries failed: ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (portb) REFERENCES Port (id) -- Cannot add or update a child row: a foreign key constraint fails (`racktables`., CONSTRAINT `Link?FK?b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`)) ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (porta) REFERENCES Port (id) -- Cannot add or update a child row: a foreign key constraint fails (`racktables`., CONSTRAINT `Link?FK?a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`)) |
|
|
Afer replying the steps from 0.17.1 -> 0.17.5 I then begin the upgrade from 0.17.5 > 0.18.7 In this upgrade I get green across the board except in 0.18.1 and 0.18.7, which yeilds the following output: 0.18.1: The following queries failed: ALTER TABLE Link DROP FOREIGN KEY `Link-FK-a` -- Error on rename of './racktables/Link' to './racktables/#sql2-a8e-3d' (errno: 152) ALTER TABLE Link ADD CONSTRAINT `Link-FK-a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE -- Cannot add or update a child row: a foreign key constraint fails (`racktables`., CONSTRAINT `Link?FK?a` FOREIGN KEY (`porta`) REFERENCES `Port` (`id`) ON DELETE CASCADE) ALTER TABLE Link DROP FOREIGN KEY `Link-FK-b` -- Error on rename of './racktables/Link' to './racktables/#sql2-a8e-3d' (errno: 152) ALTER TABLE Link ADD CONSTRAINT `Link-FK-b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE -- Cannot add or update a child row: a foreign key constraint fails (`racktables`., CONSTRAINT `Link?FK?b` FOREIGN KEY (`portb`) REFERENCES `Port` (`id`) ON DELETE CASCADE) 0.18.7: DELETE FROM Dictionary WHERE dict_key BETWEEN 1 AND -- You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 |
|
|
I could be wrong, but it looks like something prior to 0.16.4 makes a change to Link-FK-a and Link-FK-b, which are needed for multiple upgrades |
|
|
If I ignore the error and upgrade to 0.20.4, the errors are still talking about the same Link-FK-a/b ALTER TABLE AttributeValue ADD CONSTRAINT `AttributeValue-FK-map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`) -- Cannot add or update a child row: a foreign key constraint fails (`racktables`., CONSTRAINT `AttributeValue?FK?map` FOREIGN KEY (`object_tid`, `attr_id`) REFERENCES `AttributeMap` (`objtype_id`, `attr_id`)) |
|
|
Thank you for the information. Could you attach the schema (mysqldump --no-data) of the production database? |
|
|
I just uploaded rack-schema.sql.gz The output is resulting from an export of a clone of our production system, which was obtained by this command: mysqldump -u root -p --no-data racktables > /tmp/rack-schema.sql And then I ran gzip to compress it.. |
|
|
Any luck? |
|
|
I'm a bit backlogged right now, but this issue is on my list for this week. Thanks for the patience. |
|
|
No problem, I didn't see a response, so I was just looking for some dial-tone to make sure you weren't waiting on me still. Please let me know if there is anything else I can do to help. |
|
|
Looks like I didn't make it into last week.. How are we looking for this week? |
|
|
Positively! I'm getting closer and closer... |
|
|
You know you want to work on this ticket today :) |
|
|
I really do need to get this fixed, I have a few things waiting on this upgrade. I realize there isnt any paid support here, so I'm very appreciative of any help you can provide, but we do use racktables as our corporate asset tracking tool. I'm actually shocked that our current install continues to work with soo many things wrong with the schema.. |
|
|
I suggest you to examine the DB schema and import the important data from your corrupted DB into the clear one. |
|
|
I may have found the main problem.. Basically I had orphaned links which i found with the following two commands: SELECT * FROM `racktables`.`Link` AS L WHERE NOT EXISTS ( select * from `racktables`.`Port` AS P WHERE L.porta = P.id ) SELECT * FROM `racktables`.`Link` AS L WHERE NOT EXISTS ( select * from `racktables`.`Port` AS P WHERE L.portb = P.id ) Then I deleted those links with the following type of command DELETE FROM `racktables`.`Link` WHERE `porta`='1234' and `portb`='5678'; where racktables is the database, and you use the results from the above commands |
|
|
Is it expected that I would get the error DELETE FROM Dictionary WHERE dict_key BETWEEN 1 AND -- You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 in 0.18.7? There doesnt appear to be any line even remotely like that in the upgrade script. And definitely not in the section of code marked 0.18.7.. |
|
|
Yes, there is a bug in 0.18.7. This query is performed in dictionary.php/reloadDictionary. It fails because config.php/max_dict_key does not contain ID for 0.18.7. |
|
|
It is looking promising, there was a second contraint issue in the final upgrade path, with our AttributeValue table which is a little more complicated.. I was able to resolve it by adding a few entries, but still trying to figure out if that is the best configuration as I'm not really a SQL guy.. (I'm getting help from a DBA on site, but they dont know anything about RT) Before I ran the upgrades, we had our schema matching the 0.17.5 schema minus the size of the barcode attribute, and the autogrow parameters for tables were slightly larger (I'm guessing intentionally) I think we are good to go.. thank you for the support |
|
|
I'm glad it is figured out, because I failed to understand which specific version this bug was filed against. FYI, the query below returns AttributeValue rows that violate AttributeMap constraints: SELECT AV.object_id, AV.attr_id, RO.objtype_id FROM AttributeValue AS AV INNER JOIN RackObject AS RO ON AV.object_id = RO.id LEFT JOIN AttributeMap AS AM ON AV.attr_id = AM.attr_id AND RO.objtype_id = AM.objtype_id WHERE AM.attr_id IS NULL; |
- Anonymous
