About 3 years ago, I introduced Review Board into our team with a great help from RB group and replaced CodeStrike. We deployed RB on a WinXP VM and the backend database is sqlite. I can’t remember the reason. Maybe I just wanted to save some time. Actually, it has spent a lot of time of our team.
Why? Because sqlite is a lightweight DB and it have bad performace in concurrency scenario. For our team, we have about 20 developers and RB is the most popular daily tools. Now we have to say, we highly depend it to control the quality of team’s code.
Recently, the dababase issue was getting worse. RB pages can’t be created well and there are many warnings/errors on it. And yesterday, the database was dead finally-“The database is locked!”. That’s a message from Django. I googled and the best solution is to use better database to avoid such issues in future.
I took about 3 hours to migrate database to MySQL 5.1. Maybe my experience can help you to save some time.
Step 1. sqlite bump
Go to sqlite.com and download a command line. For window version, you can find it here.
Unzip it and use command to dump sqlite database.
|
|
c:\sqlite3.exe reviewboard.db .dump > reviewboard_sqlite_dump |
reviewboard.db is the database file of sqlite.
Step 2. Convert to MySQL dump
Now we have the sqlite dump file. But you can’t import it into a MySQL database directly. Because some syntax is not supported by MySQL.
I googled and got a free converter to do that. You can find it here and download sqlite3_mysql.zip.
In the deep of the zip , you can find a executable file – sqlite_mysql.exe. Run it and convert the sqlite dump file to a MySQL one.
Step 3. Import MySQL dump
Before that, please create a database in your MySQL database.
|
|
mysql -uroot -p reviewboard < mysql_dump |
In my case, the dump is about 180M and I waited very long time. (And there may be some warning messages. I don’t know why but for now the migrated server works well. So maybe we can’t ignore them.)
Step 3.1 Alter database structure
Actually, this issue was found when I finished Step 4 and restarted RB. At the beginning, RB worked well. But a minute later, a guy said he could’t submit any comments.
The root cause was the “id” field of some tables lost the property of “AUTO_INCREMENT”. I didn’t know why and SQL in dump file was right. Then I write a very simple .py script to fix this issue.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
import os import string sql="alter table %s change id id int not null auto_increment" tables = """ accounts_profile accounts_profile_starred_groups accounts_profile_starred_review_requests accounts_reviewrequestvisit auth_group auth_group_permissions auth_message auth_permission auth_user auth_user_groups auth_user_user_permissions changedescs_changedescription diffviewer_diffset diffviewer_diffsethistory diffviewer_filediff django_admin_log django_content_type django_evolution django_project_version django_session django_site reviews_comment reviews_defaultreviewer reviews_defaultreviewer_groups reviews_defaultreviewer_people reviews_defaultreviewer_repository reviews_group reviews_group_users reviews_review reviews_review_comments reviews_review_screenshot_comments reviews_reviewrequest reviews_reviewrequest_changedescs reviews_reviewrequest_inactive_screenshots reviews_reviewrequest_screenshots reviews_reviewrequest_target_groups reviews_reviewrequest_target_people reviews_reviewrequestdraft reviews_reviewrequestdraft_inactive_screenshots reviews_reviewrequestdraft_screenshots reviews_reviewrequestdraft_target_groups reviews_reviewrequestdraft_target_people reviews_screenshot reviews_screenshotcomment scmtools_repository scmtools_tool siteconfig_siteconfiguration""" if __name__=='__main__': tableNames = tables.splitlines() for name in tableNames: if name: print 'Change table %s...' % name command = 'mysql -uroot -proot reviewboard -e "%s"' % (sql % name) print command os.system(command) |
I just went through all tables and alter the property of “id” field if the table has one.
Step 4. Change RB configuration file
Change rb_site_root/conf/settings_local.py to:
|
|
DATABASE_ENGINE = 'mysql' DATABASE_NAME = 'reviewboard' DATABASE_USER = 'root' DATABASE_PASSWORD = 'xxxxxxxxx' # comment the former setting #DATABASE_ENGINE = 'sqlite3' #DATABASE_NAME = 'C:\\review-board\\rbsite2\\db\\reviewboard.db' |
Make sure MySQLdb is installed or there will be error when restart RB.
The best moment came finally! RB restarted successfully and all review request/comment/diff were there. And the performance was improved. You can feel the page is loaded fast. Cheers! 😀
If you have the same problem with me, I hope this post can help and save some time.
