| Author |
 Topic  |
|
Alfred
Senior Member
USA
1527 Posts |
 Posted - 17 March 2003 : 11:39:51
|
I am currently using MS Access as my database, but my host is also offering a MySQL Server Database for my account.
If I understand it right this is not an application like Access, which I have on my HD and just upload the file.
Since mySQL resides on the server I would need to find out how to edit my DB on line.
How much would I have to modify my code to take advantage of that, and what else is involved?
|
Alfred
The Battle Group
CREDO
|
|
|
spyordie007
Junior Member
USA
408 Posts |
Posted - 17 March 2003 : 11:47:24
|
all you should have to do is change a few lines in the config.asp and than find a way to migrate your forum to a MySQL database (piece of cake right?).
The hardest part is migrating your database because the defaults and Nulls dont translate excactly between Access and MySQL. A MySQL database resides on the MySQL server, since it is unlikely that they will allow you to have direct access to it your best course of action would probably be to install a mysql server locally (like on your desktop) so you can play with setting up the database and than give your host a dump file of your final product (mysqldump --opt databasename > dumpfile.sql) they could than import that data into a database on their mysql server (mysql databasename < dumpfile.sql).
Lastly do a search here, there are quite a few good threads with info about the database migration (which is really the only hard part).
-Spy |
Power - The only narcotic controlled by the SEC, not the FDA.
Prosperity without pollution! The American Hydrogen Association - http://www.ahanw.org
Questions about Hydrogen? Post them on our forum - http://www.ahanw.org/forum |
 |
|
|
Steve D.
Average Member
USA
640 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
spyordie007
Junior Member
USA
408 Posts |
Posted - 17 March 2003 : 12:23:23
|
Here is what I did:
1) Allow a new forum install to create the tables in a new blank database (database1).
a) Run the dbs scripts for my mods so they modify the tables in the database appropreatly.
b) Run reports on each of the tables so I can see the field settings and indexes.
2) Use MySQL Front to import my forums into a differant new blank database (database2) on my MySQL server (you could do this locally).
3) Dump the tables in the 2nd database and import them into the first database:
mysqldump --opt database2 > backup-file.sql
mysql database1 < backup-file.sql
4) Used MySQLAdmin (a php web-based database admin) to fix the null and default values based upon reports I ran on the tables from step 1.
This thread is a good read and I highly recommend reading it if you plan on moving to MySQL:
http://forum.snitz.com/forum/topic.asp?TOPIC_ID=34900
-Spy
|
Power - The only narcotic controlled by the SEC, not the FDA.
Prosperity without pollution! The American Hydrogen Association - http://www.ahanw.org
Questions about Hydrogen? Post them on our forum - http://www.ahanw.org/forum |
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 17 March 2003 : 13:50:31
|
Most hosts give you access to manage your mysql database online. That includes creating/deleting/editing your mysql database. No need to install the mysql server on your computer.
If you need to convert your access database to a mysql database, I can do it for you. It's not too hard.
MySQL-Front is a great tool to manage your mysql database. You can download it here |
Support Snitz Forums
|
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 17 March 2003 : 14:07:06
|
Davio, I would be most grateful to take you up on your offer.
If I did it myself chances are I will make a dumb little error and get stuck, as I do so often.
A couple of questions:
- The advantage of using mySQL is basically to eliminate the 10 user limit of Access - does it have additional advantages?
- While we do the preparation and conversion, my current Access DB will continue to function properly, and when we are ready to switch to the new one I can just shut down the forums for a few minutes while switching over?
I will download the MySQL-Front to start with. |
Alfred
The Battle Group
CREDO
|
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 17 March 2003 : 14:25:58
|
quote:
The advantage of using mySQL is basically to eliminate the 10 user limit of Access - does it have additional advantages?
It's faster than an access database and able to hold a lot more data without losing performance.quote:
While we do the preparation and conversion, my current Access DB will continue to function properly, and when we are ready to switch to the new one I can just shut down the forums for a few minutes while switching over?
Uhhm, sure. But then you will need to create a new forum in a new folder to do the conversion. |
Support Snitz Forums
|
|
|
|
spyordie007
Junior Member
USA
408 Posts |
Posted - 17 March 2003 : 14:27:21
|
Using Access shouldnt have a 10 user limit, technically as far as the file structure is concerned there is always only one user (IUSER). The big advantages of moving to MySQL are in the speed and scalability arenas.
quote:
While we do the preparation and conversion, my current Access DB will continue to function properly, and when we are ready to switch to the new one I can just shut down the forums for a few minutes while switching over?
Yes, the only problem with doing it this way is that any posts (replies new topics changes etc) that are made on your forum since the last time you pulled the access database for the conversion will not be there once you change the code to use the MySQL DB.
I did exactly that, I waited until a time that I knew it was unlikely someone would make a new post (early in the morning) and grabed a copy of the access database, than created my MySQL DB for it and fixed the fields and changed my forum to use it before the next post on the forum was made; In effect I had *no* downtime, however if someone were to have posted as soon as I changed over to the other DB they would not be able to see those changes.
-Spy |
Power - The only narcotic controlled by the SEC, not the FDA.
Prosperity without pollution! The American Hydrogen Association - http://www.ahanw.org
Questions about Hydrogen? Post them on our forum - http://www.ahanw.org/forum |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 17 March 2003 : 15:01:37
|
quote:
It's faster than an access database and able to hold a lot more data without losing performance.
That alone is good enough reason!quote:
Uhhm, sure. But then you will need to create a new forum in a new folder to do the conversion.
Can't I just copy the files into another folder on my host?
What is the alternative - I mean how would you suggest to do it without closing down during the work?
spyordie007:quote:
Yes, the only problem with doing it this way is that any posts (replies new topics changes etc) that are made on your forum since the last time you pulled the access database for the conversion will not be there once you change the code to use the MySQL DB.
This is why I thought to get it all ready while the current files are untouched and in service, and then shut down the forums for the short switch.
|
Alfred
The Battle Group
CREDO
|
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 17 March 2003 : 15:27:10
|
Be advised that while Snitz works well with MySQL Server databases, there are some annoying differences in the way the MyODBC drivers work vs. the way MS drivers work that can cause you problems in other non-snitz code. Things like coercing your varchar column to a char column in an ADO recordset if the existing data is real short, differences in the way recordcount works, etc. Review the Snitz code for MySQL specific coding samples. :)
|
======
Doug G
======
Computer history and help at www.dougscode.com |
Edited by - Doug G on 17 March 2003 15:27:54 |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 17 March 2003 : 15:38:39
|
| Doug, are our forums here based on MS SQL db? |
Alfred
The Battle Group
CREDO
|
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 17 March 2003 : 16:48:23
|
quote:
Can't I just copy the files into another folder on my host?
Sure.
Things I would need from you is a link to download your access database. A link to the folder where you copied your forum files.
Make sure you are using v3.4.03 of the snitz forums otherwise, you will need to upgrade first.
Make sure you have the DBS (database setup) files for the mods you have installed (if you have any mods installed). |
Support Snitz Forums
|
|
|
|
Alfred
Senior Member
USA
1527 Posts |
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 17 March 2003 : 21:32:53
|
quote:
Originally posted by Alfred
Doug, are our forums here based on MS SQL db?
Yep. HuwR hosts them, I will guess it's a SQL Server 2000 DB but it's some version of MS SQL Server.
|
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
Alfred
Senior Member
USA
1527 Posts |
Posted - 18 March 2003 : 11:05:17
|
One more question on the subject -
I am currently using a strategy game site for all this, as a kind of practice ground for future more practical application.
For now there is no real concern over volume of users, or bandwidth, but I realize that for a serious commercial site MS SQL will be needed.
Are there significant differences in the coding between mySQL and MS SQL? |
Alfred
The Battle Group
CREDO
|
|
|
|
Topic |
|
Topic Locked
