MPNews KnowledgeBase
Storing newsgroup messages in MySQL
Thread Starter: Chris Reich Started: 12/17/2007 6:30 PM UTC
Replies: 1
12/17/2007 6:30 PM UTC
Storing newsgroup messages in MySQL
Reply Quote

Chris Reich

Using the database settings in MPNews, you can store all the messages in your newsgroups in a MySQL database. This can help improve performance, simplify backing up your server and allows you to integrate your newsgroups with other systems and reporting tools.

Download the driver
MPNews requires the "Connector/Net" driver for MySQL, which you can download free from http://www.mysql.com/products/connector/net/. Install this before continuing.

Setting up the database
You must first create and configure the database to use in MySQL. You can either use an existing database or create a new one; you will normally use a new database for MPNews.

Create a new database in the mysql command line tool by entering the command:

mysql> CREATE DATABASE MPNews;
       Query OK, 1 row affected (0.00 sec)
       Securing the database
Normally, the only thing that needs direct access to the database is MPNews itself. You can create a new user account for MPNews to use by entering the command:

mysql> GRANT ALL ON MPNews.* TO MPNews@localhost IDENTIFIED BY 'password';
       Query OK, 0 rows affected (0.11 sec)
       Configuring MPNews
Log in to the main MPNews administration site, select the "Storage" option and select the "Database" option.

Ensure the "Supported database types" option is selected, and select the "MySQL" option from the drop down list.

Enter the connection string Server=localhost;Database=MPNews;User ID=username;Password=password, replacing the values as appropriate to match the server, database, login and password you are using in SQL Server.

Set the "Named parameter prefix" option to ? and click on "Save"

Back on the main Storage screen, change the "Currently selected storage engine" to "Database".

Finished
MPNews is now set up to use a MySQL database. It will have created the tables it requires in the database automatically, and you can use standard MySQL tools to read the data it creates for your own purposes. For example, you can use some simple queries to produce reports of how many messages are posted by each user, over a certain size, at what time of day and much more.
4/1/2008 3:47 PM UTC
Re: Storing newsgroup messages in MySQL (update for MySQL 5)
Reply Quote

Chris Reich

We have uncovered an issue with MySQL 4 (likely) and 5 and working for update in next release.  There is a fix - technically not too difficult if you know MySQL.



What is happening?

MySQL v5 now uses the InnoDB engine by default which will automatically add foreign keys to the tables when they are created by MPNews.  These key constraints will cause the errors you see posting messages.



What is the fix?

We need to do two things.  First, we need to remove the foreign keys.  You can do this in the MySQL admin interface by editing the tables to remove the foreign keys from tables groupmessages, groupthreads, headers, and servergroups.  Or you can run the following SQL queries to do the same thing (change "mpnews" below to your database name):



     ALTER TABLE `mpnews`.`groupmessages` DROP FOREIGN KEY `groupmessages_ibfk_1`, DROP FOREIGN KEY `groupmessages_ibfk_2`;

     ALTER TABLE `mpnews`.`groupthreads` DROP FOREIGN KEY `groupthreads_ibfk_1`;

     ALTER TABLE `mpnews`.`headers` DROP FOREIGN KEY `headers_ibfk_1`;

     ALTER TABLE `mpnews`.`servergroups` DROP FOREIGN KEY `servergroups_ibfk_1`, DROP FOREIGN KEY `servergroups_ibfk_2`;



The second thing is to specify the correct database.  When you select MySQL as a database type, it sets the type as a MySQL v1 database.  Instead, you need a custom database type with the following (change version number if you have different connector installed):



     CUSTOM DATABASE TYPE (exactly as shown, change version number if you have a different version):

           MySql.Data.MySqlClient.MySqlConnection, MySql.Data, Version=5.1.5.0, Culture=Neutral, PublicKeyToken=c5687fc88969c44d

     CONNECTION STRING:

           Server=localhost;Database=MPNews;UserID=MPNews;Password=MPNews



Once you restart MPNews, you should be working normally.



We will document further and update in future release to resolve.







"Chris Reich" <info@messagepixels.com> wrote in message news:279dd030$7fb55e53$705@vlad...
Using the database settings in MPNews, you can store all the messages in your newsgroups in a MySQL database. This can help improve performance, simplify backing up your server and allows you to integrate your newsgroups with other systems and reporting tools.

Download the driver
MPNews requires the "Connector/Net" driver for MySQL, which you can download free from http://www.mysql.com/products/connector/net/. Install this before continuing.

Setting up the database
You must first create and configure the database to use in MySQL. You can either use an existing database or create a new one; you will normally use a new database for MPNews.

Create a new database in the mysql command line tool by entering the command:

mysql> CREATE DATABASE MPNews;
      Query OK, 1 row affected (0.00 sec)
      Securing the database
Normally, the only thing that needs direct access to the database is MPNews itself. You can create a new user account for MPNews to use by entering the command:

mysql> GRANT ALL ON MPNews.* TO MPNews@localhost IDENTIFIED BY 'password';
      Query OK, 0 rows affected (0.11 sec)
      Configuring MPNews
Log in to the main MPNews administration site, select the "Storage" option and select the "Database" option.

Ensure the "Supported database types" option is selected, and select the "MySQL" option from the drop down list.

Enter the connection string Server=localhost;Database=MPNews;User ID=username;Password=password, replacing the values as appropriate to match the server, database, login and password you are using in SQL Server.

Set the "Named parameter prefix" option to ? and click on "Save"

Back on the main Storage screen, change the "Currently selected storage engine" to "Database".

Finished
MPNews is now set up to use a MySQL database. It will have created the tables it requires in the database automatically, and you can use standard MySQL tools to read the data it creates for your own purposes. For example, you can use some simple queries to produce reports of how many messages are posted by each user, over a certain size, at what time of day and much more.