The move to the new server is underway, and although DNS propagation is a bit sketchy, everything seems to be working pretty well, with one exception.
I used the very cool Typemover plugin to speed things along, and although it did its job, somehow trackbacks have become decoupled from their associated entries. I can see the entry list fine, and the trackback list is still there, but it looks like the key field between the tables has gone kablooie, since no entries have the associated trackbacks against them.
Does anyone have any ideas how to fix it? If it involves messing with the database, I’m prepared to do so, but my SQL is rusty at best.
[Update : OK, so what I think I need is an Update statement that will fix the ‘trackback_entry_id’ field in the ‘mt_trackback’ table (which begins with 413 and ranges upward) to match up with the ‘entry_id’ fields in the ‘mt_entry’ table (which begins at 1 and ranges upward), if that is indeed the correct key relationship. Unfortunately, I don’t know what if any other dependencies may exist, so I’m hesitant to go in and try it myself. It’s probably just that, but if there’s anyone out there with a more detailed knowledge of the data structure, I love some guidance. Also, like I said, my SQL syntax is rustier than hell. Anyone?]
Any suggestions would be appreciated, as would any reports of general site-move weirdness.
Thanks!
[Update the second: the move seems to have gone off without a hitch, other than the trackbacks issue. I’m in codemode at the moment, messing around under the hood and designing the sites for some new projects, so please let me know if something’s broken here. Thanks again.]
[Updated update: I’ve switched over to the very cool Feedburner for my XMLery. It should just work seamlessly; I’ve updated the autodiscovery code and am redirecting requests for the old Atom, RSS 1.0 and 2.0 feeds. This post will be the last one that updates those files, so you may need to switch if the ol’ bottle starts to seem even quieter than usual. This is the feed URI now if you want to hop on to that manually.
Bloglines is the only service that seems to have hiccupped so far, near as I can tell, but that may just be temporary. I’ll be feedburnerizing the Coasters sidebar linkblog too, soon. [Update to the updated update: done! I also redesigned the index page, finally]. As always, bug reports are welcomed.]
Category:
Metablogging Join the conversation! 6 Comments
Comments are closed.
Testing comments. Whee! Comments are fun, especially when free from manufactured meat!
sounds like the ID columns are using automagically-generated IDs and, rather than simply copying the previously-generated numbers across, the new db server has generated new numbers.
a friend saw this recently when upgrading a SQLServer db, and dug up a pre-existing tool to fix it IIRC. alternatively, you might be able to do an Update on the new table’s ID column, depending on how the new IDs map to the old IDs.
but short summary: your old IDs are jiggered and need to be re-copied across into the new ID col.
doh. brain-to-fingers wibble. please substitute final para:
but short summary: your old IDs are fine but the new IDs are jiggered and need to be re-copied across from the old ID col into the new ID col.
(and it may be easier to re-copy the entire table than to re-map the IDs)
re EDIT:-
DISCLAIMER: I know less about MT’s datamodel than you do. plus i still don’t know what rdbms you’re using and none of them follow strict sql for the nontrivial stuff, so i’ll be guessing a bit
my initial thought was:
here’s something to try which is low probability but which is easy AND REVERSIBLE :
1. subtract 412 from all the trackback_entry_id’s
“update mt_trackback set trackback_entry_id = trackback_entry_id – 412”
2. see if it’s all working now
just tap round your site
3. if not PERFECT, remove your changes:
“update mt_trackback set trackback_entry_id = trackback_entry_id + 412”
but then came secondthought time:
1. i wondered whether trackback_entry_id was the foreign key for mt_entry or the id for mt_trackback.
2. i remembered it’s a live constantly-read site: if trackback_entry_id is the id for mt_trackback and someone adds a trackback mid-investigation, things could get fiddly. (you COULD just delete any added rows and ask that person to re-send the trackback ping)
so we need to prevent Inserts while you’re trying this.
begin trans…commit/rollback is ideal but won’t work since you need to be able to see the results on your website mid-transaction. unless the blog site building code reads the db data in Unsafe mode or with an Isolation Level that allows seeing uncommitted transactions, you won’t see your changes in your website.
2 options:
1/ temporarily disable trackback in your settings
2/ issue an explicit Lock Table command, with whatever syntax your rdbms wants.
REGARDLESS, good idea to take a quick&dirty table backup before you start. any time you don’t like what the new data looks like, you can just replace it with the old contents with no fuss or effort.
essentially, create a copy of the table and data. to recover that data, delete all from the original table then insert the copy table into the original table.
Here’s the SQL for one table (note, neither oracle nor sybase/sqlserver respect this sql):
1. create table BACKUP_mt_entry as select * from mt_entry
N. delete from mt_entry ; insert into mt_entry select * from BACKUP_mt_entry
Sybase syntax for this is freshish in my head (errr… 3 years) and is as follows, assuming you have your select */bulkcopy option set to ON:
1. select * into mt_entry from mt_entry
2. delete mt_entry ; select * into mt_entry from BACKUP_mt_entry
i’d strongly suggest you try the whole backup process with a dummy table or two first though 🙂
re EDIT:-
DISCLAIMER: I know less about MT’s datamodel than you do. plus i still don’t know what rdbms you’re using and none of them follow strict sql for the nontrivial stuff, so i’ll be guessing a bit
my initial thought was:
here’s something to try which is low probability but which is easy AND REVERSIBLE :
1. subtract 412 from all the trackback_entry_id’s
“update mt_trackback set trackback_entry_id = trackback_entry_id – 412”
2. see if it’s all working now
just tap round your site
3. if not PERFECT, remove your changes:
“update mt_trackback set trackback_entry_id = trackback_entry_id + 412”
but then came secondthought time:
1. i wondered whether trackback_entry_id was the foreign key for mt_entry or the id for mt_trackback.
2. i remembered it’s a live constantly-read site: if trackback_entry_id is the id for mt_trackback and someone adds a trackback mid-investigation, things could get fiddly. (you COULD just delete any added rows and ask that person to re-send the trackback ping)
so we need to prevent Inserts while you’re trying this.
begin trans…commit/rollback is ideal but won’t work since you need to be able to see the results on your website mid-transaction. unless the blog site building code reads the db data in Unsafe mode or with an Isolation Level that allows seeing uncommitted transactions, you won’t see your changes in your website.
2 options:
1/ temporarily disable trackback in your settings
2/ issue an explicit Lock Table command, with whatever syntax your rdbms wants.
REGARDLESS, good idea to take a quick&dirty table backup before you start. any time you don’t like what the new data looks like, you can just replace it with the old contents with no fuss or effort.
essentially, create a copy of the table and data. to recover that data, delete all from the original table then insert the copy table into the original table.
Here’s the SQL for one table (note, neither oracle nor sybase/sqlserver respect this sql):
1. create table BACKUP_mt_entry as select * from mt_entry
N. delete from mt_entry ; insert into mt_entry select * from BACKUP_mt_entry
Sybase syntax for this is freshish in my head (errr… 3 years) and is as follows, assuming you have your select */bulkcopy option set to ON:
1. select * into BACKUP_mt_entry from mt_entry
2. delete mt_entry ; select * into mt_entry from BACKUP_mt_entry
i’d strongly suggest you try the whole backup process with a dummy table or two first though 🙂
Thanks, saltation. I’m gonna have to gird my loins and top up the coffee and just wade in at some point, I guess!