This shows you the differences between two versions of the page.
— | mysqlbackend [2007/03/02 13:45] (current) – created - external edit 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== MySQL Backend ====== | ||
+ | |||
+ | ===== Note ===== | ||
+ | **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. | ||
+ | |||
+ | 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. | ||
+ | |||
+ | ===== Important Note About MySQL and POPFile ===== | ||
+ | |||
+ | 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: | ||
+ | |||
+ | - Orphan detail records when magnets are deleted | ||
+ | - 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 ===== | ||
+ | |||
+ | 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' | ||
+ | |||
+ | [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' | ||
+ | |||
+ | 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, | ||
+ | |||
+ | ===== Existing POPFile Users Switching to MySQL as the backend ===== | ||
+ | ==== IMPORTANT NOTE ==== | ||
+ | |||
+ | Users that have an existing installation of POPFile should follow the steps below to switch to MySQL. | ||
+ | |||
+ | - Using the MySQL client, create a popfile database and a user< | ||
+ | mysql | ||
+ | create database popfile; | ||
+ | grant all on popfile.* to pfuser identified by ' | ||
+ | flush privileges; | ||
+ | quit | ||
+ | </ | ||
+ | - Confirm that you have the following Perl Modules installed, if not, obtain them from CPAN (or ActiveState using PPM) and install them | ||
+ | - DBD::mysql | ||
+ | - Shutdown any running instance of POPFile | ||
+ | - Edit the popfile.cfg file, change the following lines< | ||
+ | bayes_dbconnect dbi: | ||
+ | bayes_dbuser pfuser | ||
+ | bayes_dbauth pfpassword | ||
+ | </ | ||
+ | - Use the SQLite commandline utility to dump your existing POPFile data to a text file < | ||
+ | sqlite popfile | ||
+ | .out dump.sql | ||
+ | .dump | ||
+ | .q</ | ||
+ | - Use the MySQL commandline utility to load the dumped database.< | ||
+ | mysql | ||
+ | use popfile; | ||
+ | source dump.sql | ||
+ | update popfile set version = 0; | ||
+ | quit</ | ||
+ | - 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. | ||
+ | - You should be up and running with MySQL as the backend. | ||
+ | |||
+ | ===== Users New to POPFile ===== | ||
+ | |||
+ | Users who are installing POPFile for the first time and wish to use MySQL as the backend should follow these steps; | ||
+ | |||
+ | - Using the MySQL client, create a popfile database and a user< | ||
+ | mysql | ||
+ | create database popfile; | ||
+ | grant all on popfile.* to pfuser identified by ' | ||
+ | flush privileges; | ||
+ | quit | ||
+ | </ | ||
+ | - Confirm that you have the following Perl Module installed, if not, obtain it from CPAN (or ActiveState using PPM) and install it | ||
+ | - DBD::mysql | ||
+ | - Create a popfile.cfg file in your $POPFILE_USER location, it should contain the following lines < | ||
+ | bayes_dbconnect dbi: | ||
+ | bayes_dbuser pfuser | ||
+ | bayes_dbauth pfpassword | ||
+ | </ | ||
+ | - Start POPFile | ||
+ | - 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. | ||
Should you find anything in the documentation that is incomplete, unclear, outdated or just plain wrong, please let us know and leave a note in the Documentation Forum.