Version v 0.22.x of POPFile is not compatible with MySQL. It is hoped that v 0.23 will restore MySQL compatibility but the developers are having difficulties. If you have experience with Perl and MySQL/SQLite compatiability the developers could use your help.

Starting with version 0.21.0 of POPFile, the backend database is an SQL database. POPFile ships configured to use SQLite, but can be reconfigured to use any SQL database that is supported by Perl's DBI. These instructions are specific to MySQL, but could easily be adapted to any supported SQL Server.

How you go about performing that reconfiguration depends on the version of POPFile you are using, so take care to chose the correct set of instructions from below. Also note that these instructions assume that you already have a MySQL server up and running and that you are familiar with MySQL and the MySQL client.

The current version of MySQL does not support triggers. POPFile uses database triggers in several areas to automatically maintain referential integrity in the database. Users who use MySQL for the backend will have the following issues due to lack of triggers:

1. Orphan detail records when magnets are deleted
2. Orphan bucket contents when buckets are deleted

You can avoid the second issue by always emptying a bucket (click remove all words) before deleting a bucket.

Windows users that use the installer version of POPFile are using a minimal subset of Perl that the installer installs. That subset is missing components required to use MySQL.

To successfully change the backend, those users must install a full installation of ActiveState's Perl, disable the minimal Perl by deleting or renaming the exe files noted below in the POPFile installation folder, and then follow the appropriate instructions for the cross-platform version below.

[Note: The Windows installer for 0.21.0 is still under development. The following information is therefore subject to change.

Some of the exe files in the POPFile installation folder are not part of the minimal Perl, so deleting them may not be a good idea (e.g. one of uninstall.exe's tasks is to restore the email settings changed during the installation).

To disable the minimal Perl, rename (or delete) the perl.exe, wperl.exe and perl58.dll files in the POPFile installation folder. It is probably also a good idea to rename (or delete) the lib folder in the POPFile installation folder as it contains the rest of the minimal Perl installation. NB This lib folder contains some components which are not part of a standard ActiveState Perl installation.

The Windows version also ships with some exe files which use the minimal Perl (popfile.exe, popfileb.exe, popfilef.exe, popfileib.exe and popfileif.exe) so they should also be renamed (or deleted) to avoid problems.]

Users that have an existing installation of POPFile should follow the steps below to switch to MySQL.

1. Using the MySQL client, create a popfile database and a user

   mysql
   create database popfile;
   grant all on popfile.* to pfuser identified by 'pfpassword';
   flush privileges;
   quit

   Note: pfuser and pfpassword should be set to whatever you want your user and password to be.

2. Confirm that you have the following Perl Modules installed, if not, obtain them from CPAN (or ActiveState using PPM) and install them
   1. DBD::mysql
3. Shutdown any running instance of POPFile
4. Edit the popfile.cfg file, change the following lines

   bayes_dbconnect dbi:mysql:database=popfile;host=localhost
   bayes_dbuser pfuser
   bayes_dbauth pfpassword

   Note: pfuser and pfpassword should be set to whatever you want your user and password to be.

5. Use the SQLite commandline utility to dump your existing POPFile data to a text file

   sqlite popfile
   .out dump.sql
   .dump
   .q

6. Use the MySQL commandline utility to load the dumped database.

   mysql
   use popfile;
   source dump.sql
   update popfile set version = 0;
   quit

7. Start POPFile, it will detect that the version of the database is zero and will dump it again and recreate it using the MySQL flavor of the schema. This process may take some time, so be patient.
8. You should be up and running with MySQL as the backend.

Users who are installing POPFile for the first time and wish to use MySQL as the backend should follow these steps;

1. Using the MySQL client, create a popfile database and a user

   mysql
   create database popfile;
   grant all on popfile.* to pfuser identified by 'pfpassword';
   flush privileges;
   quit

   Note: pfuser and pfpassword should be set to whatever you want your user and password to be.

2. Confirm that you have the following Perl Module installed, if not, obtain it from CPAN (or ActiveState using PPM) and install it
   1. DBD::mysql
3. Create a popfile.cfg file in your $POPFILE_USER location, it should contain the following lines

   bayes_dbconnect dbi:mysql:database=popfile;host=localhost
   bayes_dbuser pfuser
   bayes_dbauth pfpassword

   Note: pfuser and pfpassword should be set to whatever you want your user and password to be.

4. Start POPFile
5. You should be up and running with MySQL as the backend. You MUST go to the UI and create at least two buckets before POPFile will work properly.