Just to start, I am posting instructions not asking a question. I attempted the above installation setup and took notes while going through the process of setting up my own
site. Visit if you like, but it's not finished as of this posting so I'm not actively blogging yet. Since there wasn't too much in detail on installing and setting up use with the MySQL provider, I decided to
help out everyone having issues and write instructions to prevent and correct common problems.
Just some notes on me and my setup, I'm running a Windows client/server programmer by trade with a lot of experience in web development. I run Windows XP SP2, Visual Studio 2005 and MySQL 5.0.67. My host is GoDaddy with Shared Windows Hosting with
multiple domains sharing my hosting account. My database is of course MySQL because I get more of them than SQL Server databases. This is my second MySQL back-end website so MySQL is new to me but databases are not.
While I use GoDaddy, I'm sure these instructions will apply to other hosts as well. If people are running into the problems such as the be_Settings table does not exist, referencing MySql.Data.dll issues or others, these instruction will help them correct
them so you can point them here. If you come up with any other MySQL issues, please ask here and I will try to answer them as best I can.
Local Windows Development Environment
- Download MySQL Community Server (pick the version your web host supports) and follow its instructions for installation.
- (GoDaddy Note) – As of writing, GoDaddy supports 4.1 and 5.0.
Go with the 5.0 or greater if available.
- Also while here, download the
MySQL Connector/Net 5.2.
- The choice is yours as to which package you are comfortable with (binaries or source).
Either way, you will need to reference this dll later on.
- After the installation, start the MySQL Administrator then locate and select Startup Variables.
- On the Advanced tab, scroll down and find the “Various” group and check “Make table names”.
This enables the combo box next to it. Select “0 – Store as Created, Case Sensitive”.
- This will force the table names to be created with the same case as in the scripts.
BE uses mixed case queries and by default, MySQL on windows forces all table names to lower case thus making be_settings and be_Settings two different tables in case sensitive environments such as Linux.
- (GoDaddy Note) – GoDaddy uses a Linux host and thus the table names are case sensitive
- Locate and select Service Control and press the “Stop Service” button.
Once finished, press the button again to start the service.
- Locate and select Catalogs then right-click in the list that appears at the bottom and select “Create New Schema”. This is the name of your database so make sure it matches what is on your host to simplify things in the future.
- Select your newly created schema and add custom objects (tables, views, stored procedures) if needed.
- (GoDaddy Note) – GoDaddy has limited the use of Stored Procedures in MySQL.
SP’s cannot return result sets or perform complex logic.
Custom Functions are also not allowed.
- Download BlogEngine.Net and follow its instructions for installation if not already installed.
- If installed and looking to switch over to MySQL, take a look at
Al Nyveldt’s post on
Provider Migration. I followed it and had no problems at all.
- After the install, locate the setup folder in the package and browse to the MySQL folder.
Open the MySQLSetup18.104.22.168.sql file and make sure the table names follow the naming standard below.
Find and Replace may be the quickest way to do this. (Hopefully in a next release this will be corrected to prevent case-sensitivity issues this document aims to prevent)
- Table Names
- If you already have table names that are all lowercase, you must do steps 3 – 5, then rename the tables to something mixed case but different than the original name then back to the original name.
i.e. be_settings > be_Settings1 > be_Settings.
MySQL will not let you rename it to mixed case in GUI because it attempts to create the new table and only one table name can exist regardless of its case, similar to SQL Server.
- Save the file then go back into the MySQL Administrator. Go to Tools>MySQL Query Browser. Locate your schema in the Schemata list on the right then right-click on it and choose “Make default schema”.
This will ensure all statements are run against this database.
- Next go to File>Open Script and select the MySQLSetup22.214.171.124.sql file you just edited and press the Execute button.
Assuming there are no errors, your database is now set up.
- Open the BE project in Visual Studio.
- Note – I am using Visual Studio 2005. Steps may vary in other versions from this point on.
- Follow the instructions in the ReadMe.txt in the Setup folder for setting up BE to use MySQL.
- Open the web.config file and delete the following line in the assemblies section:
- <add assembly="MySql.Data, Version=126.96.36.199, Culture=neutral, PublicKeyToken=C5687FC88969C44D"/>
- In the DbProviderFactories section, locate the “MySQL Data Provider” entry and remove the following from the end:
- ,Version=188.8.131.52, Culture=neutral, PublicKeyToken=c5687fc88969c44d "
- Be sure to remove the comma.
- If you downloaded the source, add the MySql.Data project to the solution then add a reference to the project.
- If you downloaded the binaries, add a reference to MySql.Data.dll.
- You are now ready to go. If you started using the XML provider or want to start using the XML provider first then migrate to MySQL, go back to step 8a for details on how to migrate.
Is it possible to use your methods with the phpadmin from MySQL.
I try to use a MySQL5.0 database fot the blogengine and I get the same problem as ajhiggins1 wrote to you.
The difference with your situation is that I'm using the phpadmin tool on internet to create a database.
When I import the sql script with your changes the tables have not the names with the capitals.
Do you know a method to import them casesensitive?
Thanks withe regards,
I used the phpadmin to create my database as well. Try running the following:
SHOW VARIABLES LIKE 'lower%';
Then look for: lower_case_table_names
There is a detailed explanation
here but it sounds like your setting may be 1. Mine is set to 0. You may need to check with your host to see if it is possible to change it. My host (Godaddy) does not allow it to be changed but thankfully they have it set to 0.
I have re-written the MySQL 1.4.5. setup script to use correct cases. If anyone wants this setup script, let me know.