Come together with the global Drupal community in Rotterdam, 28 Sept – 1 Oct 2026. Sessions, contribution, connection, and Early Bird savings until 8 June.I understand not rolling out an update path for the 5.x-3.0 version as it would have to be supported, and it's tough to test and make robust a script which changes databases. But I'm wondering if we could get a manual step-by-step listing of SQL statements and other actions necessary to perform in order to be able to install the latest version? Most of your users are probably using 5.x-1.8, and there is a lot of great work represented in 3.0 (thank you!) that we would like to be able to use and test.
Thanks for your work.
Alternatively, if there is no time to document this, would it be enough to simply do a comparison between the 3.0 database schema and the 1.8 db schema and come up with the SQL alter table statements necessary to convert from one to the other, or are there other steps/tricks?
| Comment | File | Size | Author |
|---|---|---|---|
| #8 | privatemsg_upgrade_5.x-1.8-5.x-3.0_v3.txt | 1.8 KB | apotek |
| #6 | privatemsg_upgrade_5.x-1.8-5.x-3.0_v2.txt | 1.81 KB | apotek |
| #4 | privatemsg_upgrade_5.x-1.8-5.x-3.0_draft.txt | 1.67 KB | apotek |
Comments
Comment #1
litwol commentedThe issue i am facing now is that i took over maintaining private message mdule when i was past the 1.8 release. i have no knowledge of the difference in schema. the problem is much bigger than just database changes however. not only that we need to change the database schema, but we also need to correctly copy over all private messages from the old schema into the new schema with all the message relations intact.
i will release an upgrade path soon, but for now i need to research to find out the differences myself.
Comment #2
apotek commentedHello. I'll try to put together the SQL Alter Table statements necessary (though we made modifications locally as well due to so many missing indexes on the distributed module). That should get us halfway there. Then it's an issue of how to move the data around. Thanks for all your work.
Comment #3
litwol commentedi honestly dont know.. i've never looked at the 1.8 code. please do a backup of your database before you attempt any changes. it will be horrible if you loose your data during this manual change.
Comment #4
apotek commentedI'm attaching the SQL statements I used to make my database look more like what the 3.0 version is. I do make some suggestions in the SQL comments. I'm not sure why we're adding the enormous longtext as it entails a huge performance drag, and I think it would be better to constrain the sizes of the fields more (also for performance as well as space reasons). The site I work with has 60,000 users, and well over 300,000 private messages, so these are very relevant details.
What I'm unsure of is what happens to the data in the old privatemsg.replied column? Does it somehow get calculated for insertion into the thread column, or does it just get dropped.
That's the missing piece of the puzzle. A PHP script or SQL statement that would do the right thing with the replied column data is all that's missing, as far as I can tell.
Don't worry, I have several versions of the site running on my local desktop Mac, so I'm not touching any live data yet.
Comment #5
litwol commentedWonderful work. thank you very much.
I am unsure of the behavior privatemsg.replied provides so i'm not sure how its handled. I'll await for results of your further investigation :)
Comment #6
apotek commentedPlaying around with this more, and seeing how slow my database is now with the longtext fields, I decided to make the following change.
* Definitely change the longtext to something else. (See this thread for details: http://forum.mysqlperformanceblog.com/s/t/302/). MySQL 5+ can take VARCHAR fields up to 65 thousand chars, but of course that limits cross-database-engine compatibility. So I'm just going to make this VARCHAR(255) until I see the reason for such a huge field.
Comment #7
litwol commented255 characters per commend is ridicolously small field size.
i recommend trying small text or something instead. or regular text versus long text.
Comment #8
apotek commentedI guess I don't understand what the variables field is for? I imagined that if it's there to store a few serialized variables, 255 chars would be sufficient, but if there's a lot more data to be stored there, then it seems like it will be less of an ad hoc extension to the privatemsg data, and more of something that should be broken out into columns/tables and normalized.
SMALLTEXT is also 255 chars, so if you need to store lots of variables in there, I guess TEXT is our only option unfortunately.
Also, the 'type' field.. assuming this is for storing mimetype, 128 chars would be sufficient. I don't see any defined mimetypes that are longer than that.
Thanks for getting back to me!
I wanted to add that after running the conversion SQL (without the column drop), I am able to switch back and forth between version 1.8 and 3.0 fairly reliably, though I'm still not sure about the replied/thread data.
Comment #9
catchSubscribing to this. I'll try to get these manual SQL commands drupalised and into a patch over the next couple of weeks. I've also got 30k pms to test it with.
klktrk - I don't have a 'replied' column in my 5.8 install...
Comment #10
catchI found some update routines in privatemsg.install that handle the 1.8 to 3.0 update. Ran these on a test site, then my live site with 30,000 pms and it's working fine so far. Given that, this ought to be marked fixed. I think it'd be good to open a new issue about possibly changing the variables field to VARCHAR(255).
Comment #11
Carlos Miranda Levy commentedNot sure what the status of this is after reading the thread above...
Should I apply http://drupal.org/files/issues/privatemsg_upgrade_5.x-1.8-5.x-3.0_v3.txt or are the "update routines in privatemsg.install" already able to "handle the 1.8 to 3.0 update"?
Comment #12
catchThe updates in privatemsg.install work fine afaik. As always - best to run updates on a backup of your site first.
Comment #13
benovic commentedwell this is really confusing!
Here:
http://drupal.org/project/privatemsg
it is recommendet to upgrade
here:
http://drupal.org/node/283511
its written in capital letters THAT YOU SHOULD NOT UPGRADE (sorry for the capitals)
and the install.txt seems to be untouched since 2005
well, at least its a good reason to reactivate my sandbox :)
Comment #14
catch@Ben - the upgrade path was undocumented, and it seems that litwol overlooked it when taking over the module. I was about to work on writing the upgrade path myself, then I found it in privatemsg.install all ready to go.
I've personally tested it, and applied it to a medium-sized site (30k private messages, 2.5k active users) - and I've experienced no issues - however afaik that's the only positive test the upgrade has had - hence some caution advised, although IMO it's completely fine.
I'm going to change this to a documentation task and mark back to active since it'd be good to remove those warnings.
Comment #15
benovic commentedthank you catch!
I successfully made the upgrade in a testing environment and on the live site. no problems at all! (my site is about 300 users, 3000PM)
Comment #16
Carlos Miranda Levy commentedI confirm that I have proceeded and completed several successful updates from 5.x-1.8 to 5.x-3.0 in 4 of my portals without any incident or problem report (so far...).
All I did was to download and deploy the new version and then run the general update.php, upgrade routines were successfully executed without any complication or warning.
Comment #17
berdirI'm closing old issues since Privatemsg for Drupal 5 is not maintained anymore. I suggest you switch to Drupal 6 if possible, many of the reported issues are probably already resolved there and if not, you're welcome to open a new issue.