The authMySQL plugin is deprecated.
Please use its replacement: the authPDO plugin. The authMySQL plugin was bundled up to Greebo (2018-04-22).

This backend uses a MySQL Database to store user data and passwords.

Any database that contains basic user and group information could be used with DokuWiki. How you adapt the SQL statements to match your favorite database will be discussed in this page. All configuration and SQL statements are tested with MySQL 4.0 and are based on the example database structure below. The backend is able to work with MySQL 3.23, too, but needs slightly modified SQL statements.

Before this plugin can be used, you need to setup some settings:

1. Activate the authMySQL plugin in the Extension Manager.
2. Define the database in the Configuration Manager
3. Define also the statements that performs the authentication and administration actions.
4. Switch on this Auth plugin via the configuration option authtype by selecting authmysql.

The example configuration explained on this page assumes to uses a database which is setup below, this can be modified to your own database setup.

Administration of users and its groups can be done in the User manager, when the needed functions are supplied by this plugin.

Before you go forward to Configuration section, let's see quickly how many and what kind of real world configurations of MySQL Authentication exist. The variation reflects the flexibility SQL is offering.

• Unclassified News Board (UNB) - a message board / forum
• Another Unclassified News Board (UNB) - a message board / forum
• Phorum - a message board / forum
• FUDforum - a message board / forum
• InvisionBoard - a message board / forum
• phpBB Board - a message board / forum
• Flyspray - a bug tracker
• Drupal - a fine content management system (CMS)
• e107 - another fine content management system (CMS)
• Nucleus - a powerful blogging system
• Gallery2 - a gallery: Gallery
• Nuked Klan - a CMS for gamers :Nuked Klan
• PostNuke - a CMS http://www.postnuke.org
• Simple Machines Forum - Simple Machines Forum http://www.simplemachines.org
• ProjectPier - collaboration system: http://projectpier.org
• Typo3 - an enterprise related content management system (CMS)
• Contao - a content management system (CMS)
• VBulletin - a message board / forum
• Woltlab Burning Board - a message board / forum
• Moodle - an elearning system
• Wordpress >= 4.0 - a blogging system
• Zentao - a project management system

Config based on the old Auth backend:
(needs renaming $conf['auth']['mysql'] to $conf['plugin']['authmysql'])

• WordPress < 4.0 - a blogging system

Prior to enable authmysql plugin, you need to configure many options such as your database and at least the SQL statements that perform the authentication and basic administration actions. In the Configuration Manager, you will find “Authmysql Plugin Settings” under “Plugin Settings” section.

This auth plugin will be enabled with the authtype configuration option by selecting authmysql, which is found in the “Authentication Settings” under “DokuWiki Settings” section.

There are more general authentication related settings available too.

There are many approaches to configure this auth plugin. You may mix more than one approach shown below, depending on your own requirements and preferences.

1. use Configuration manager interface – your settings are kept in conf/local.php
2. define configuration options in conf/local.protected.php
3. define options in any php file (mysql.conf.php, for example) and include it in conf/local.protected.php

If you setup authmysql plugin configuration using Configuration Manager, your auth settings are stored in conf/local.php. But please be careful of unintentional change of your configuration. In order to avoid eventual loss of your configuration, especially of user authentication, it is preferable that auth settings should be defined in conf/local.protected.php (please create it if it doesn't exist).

The common settings for AuthMySQL plugin will be found in conf/mysql.conf.php.example which is included in the DokuWiki distribution. You can copy it to another file (mysql.conf.php for example), and edit the file to fit your needs. (This way you don't have to try and copy/paste all the code below). Then edit conf/local.protected.php for your settings to be loaded by your DokuWiki installation like:

conf/local.protected.php

<?php
// include config for MySQL backend
require_once('mysql.conf.php');

Some checks and actions needed before enabling this backend.

When you start with an empty database, you need to add a user into your database that is the superuser. The easiest way to add the superuser into your MySQL authentication environment is using the Add User capability in the User Manager interface, after switching to the authMySQL authentication. Just remember not to log out before adding the superuser, and remember to include him/her in both the admin and user groups. Of course you need to have a authMySQL plugin configuration that support adding users, otherwise you should use your database software or an external interface with database editing capabilities.

If for some reason you mess up and do log out before adding the superuser, and you have no external access to your database, use shell access to edit the conf/local.php file to change $conf['authtype'] back to authplain. That should let you log back in with the “original” superuser (i.e., the one you set up when first setting up Dokuwiki).

Note: The file conf/users.auth.php will not used for user authentication any more after this authMySQL has enabled.

When you start with non-empty database, from for example another application, which already defines administrator roles usable for your wiki you can set them in DokuWiki. Therefore you need to set the superuser and eventually the manager in the Configuration Manager.

This auth plugin will be enabled with the authtype configuration option by selecting authmysql, which is found in the “Authentication Settings” under “DokuWiki Settings” section.

Instead, if you want lock authtype to avoid eventual / unintentional change, define following setting in conf/local.protected.php.

$conf['authtype'] = "authmysql";

Prior to enable authmysql plugin, you need to configure many options such as your database and each SQL statements that performs the authentication and administration actions.

All options explained assuming to use with a Sample Database which stores the first and last name of the users in one field. The common example for AuthMySQL plugin – conf/mysql.conf.php.example – included in the DokuWiki distribution also assumes the sample database database. At the bottom of this page is also an example how to Store first and last name separated.

This option defines the MySQL server to connect to. If you are running MySQL on the same server as your DokuWiki installation, 'localhost' would be sufficient. Otherwise put in your remote MySQL server here. Keep in mind that the remote database server must allow access from your DokuWiki server. See MySQL documentation for details on this issue.

$conf['plugin']['authmysql']['server'] = 'localhost';

This option defines which user DokuWiki should use to access the database.

$conf['plugin']['authmysql']['user'] = 'dbuser';

Set the database password for 'user' here. Because it is entered in clear text some additional security prophylaxes should be performed.

$conf['plugin']['authmysql']['password'] = 'dbpassword';

Last but not least you need to specify the database that stores all the user information.

$conf['plugin']['authmysql']['database'] = 'users';

Setting this option to 1 will print out all SQL errors that occur on executing the auth plugin. This is useful during configuring the backend but should be disabled when everything works.

$conf['plugin']['authmysql']['debug'] = 1;

You can set this option to 2 to make it print every SQL query that is sent to the database.

Normally password encryption is done by DokuWiki (recommended) but for some reasons it might be useful to let the database do the encryption. Set 'forwardClearPass' to '0' and DokuWiki do the password encryption. Different encryption algorithms are possible. See Chapter Configuration for full list

If you set 'forwardClearPass' to 1 the backend expects the database to do the crypting. It will forward the clear text password to the database. Be aware of the security risk.

$conf['plugin']['authmysql']['forwardClearPass'] = 0;

Multiple table operations will be protected by locks. This array tells the module which tables to lock. If you use any aliases for table names the array must also contain these aliases. Any not named alias will cause a warning during operation.

MySQL 3.23 doesn't support transactions so that this mechanism is simulated with LOCK TABLES to be downwards compatible. Future versions of this backend may support transactions natively.

$conf['plugin']['authmysql']['TablesToLock'] = array("users", "users AS u",
              "groups", "groups AS g", "usergroup", "usergroup AS ug");

The SQL statements in this section are necessary to use DokuWiki with the MySQL authentication backend. They are the minimum set you have to define.

This statement is used to grant or deny access to the wiki. The result should be a table with exact one line containing at least the password of the user. If the result table is empty or contains more than one row, access will be denied. The module access the password as 'pass' so an alias might be necessary.

• %{user} will be replaced by a user name
• %{pass} will be replaced by an encrypted or clear text password (depends on 'forwardClearPass')
• %{dgroup} will be replaced by the default group name

$conf['plugin']['authmysql']['checkPass']   = "SELECT pass
                                               FROM usergroup AS ug
                                               JOIN users AS u ON u.uid=ug.uid
                                               JOIN groups AS g ON g.gid=ug.gid
                                               WHERE login='%{user}'
                                               AND name='%{dgroup}'";

If you share the user database with other applications it might be useful to be able to define which user may access the Wiki. Okay, the main spirit of a Wiki is it not to limit the access and give all users the possibility to participate. But hey, you are reading an article about access control, what did you expect? So let us get the donkey from the ice.

As already mentioned we have a user database but not every user in this database should be allowed to log into the wiki. The easiest way to check this is the DokuWiki defaultgroup. Every new DokuWiki user will automatically be a member of this group so all we have to do is to check the group besides the password at login time.

The above SQL statement already does this with name='%{dgroup}'.

Potential Security vulnerability: If you set 'forwardClearPass = 1' the password must be verified from the database. An additional WHERE clause will do the job: AND pass = MD5('%pass'). Otherwise, anyone can login by providing any registered username only!

A much better way is and I really recommend to do it this way is to set 'forwardClearPass = 0' and let DokuWiki do the crypting.

This statement should return a table with exact one row containing information about one user. The needed field names are:

• 'pass' containing the encrypted or clear text password
• 'name' the user's full name
• 'mail' the user's email address

Keep in mind that DokuWiki will access these information through the names listed above so aliases might be necessary.

• %{user} will be replaced by a user name

$conf['plugin']['authmysql']['getUserInfo'] = "SELECT pass, fullname AS name, email AS mail
                                               FROM users
                                               WHERE login='%{user}'";

This statement is used to get all groups a user is member of. The result should be a table containing all groups the given user is member of. The module access the group name as 'group' so an alias might be necessary.

• %{user} will be replaced by a user name

$conf['plugin']['authmysql']['getGroups']   = "SELECT name as `group`
                                               FROM groups g, users u, usergroup ug
                                               WHERE u.uid = ug.uid
                                               AND g.gid = ug.gid
                                               AND u.login='%{user}'";

The SQL statements in this section are necessary to use the user manager plugin. They set up only basic support and you will only be able to get and display the user list.

This statement should return a table containing all user login names that meet certain filter criteria. The filter expressions will be added case dependent by the module. At the end a sort expression will be added.

Important is that this list contains no double entries to a user. Each user name is only allowed once in the table.

The login name will be accessed as 'user' to an alias might be necessary. No patterns will be replaced in this statement but following patters will be replaced in the filter expressions:

• %{user} in FilterLogin will be replaced by a user name
• %{name} in FilterName will be replaced by user's full name
• %{email} in FilterEmail will be replaced by user's email address
• %{group} in FilterGroup will be replaced by a group name

$conf['plugin']['authmysql']['getUsers']    = "SELECT DISTINCT login AS user
                                               FROM users AS u 
                                               LEFT JOIN usergroup AS ug ON u.uid=ug.uid
                                               LEFT JOIN groups AS g ON ug.gid=g.gid";
$conf['plugin']['authmysql']['FilterLogin'] = "login LIKE '%{user}'";
$conf['plugin']['authmysql']['FilterName']  = "fullname LIKE '%{name}'";
$conf['plugin']['authmysql']['FilterEmail'] = "email LIKE '%{email}'";
$conf['plugin']['authmysql']['FilterGroup'] = "name LIKE '%{group}'";
$conf['plugin']['authmysql']['SortOrder']   = "ORDER BY login";

You additionally need the SQL statements in this section if you want to add new users in the database with the user manager.

This statement should add a user to the database. Minimum information to store are: login name, password, email address and full name.

• %{user} will be replaced by the user name
• %{pass} will be replaced by the password (encrypted or clear text, depends on 'forwardClearPass')
• %{email} will be replaced by user's email address
• %{name} will be replaced by user's full name

$conf['plugin']['authmysql']['addUser']     = "INSERT INTO users
                                               (login, pass, email, fullname)
                                               VALUES ('%{user}', '%{pass}', '%{email}', '%{name}')";

Please keep in mind that if you set 'forwardClearPass = 1' the clear text password is filled in here. You should at least replace %{pass} with MD5('%{pass}') or better set 'forwardClearPass = 0' and let DokuWiki do the crypting. I really recommend the second method.

This statement should add a group to the database.

• %{group} will be replaced by a group name

$conf['plugin']['authmysql']['addGroup']    = "INSERT INTO groups (name)
                                               VALUES ('%{group}')";

This statement should connect a user to a group (a user become member of that group).

• %{user} will be replaced by a user name
• %{uid} will be replaced by the id of a user data-set
• %{group} will be replaced by a group name
• %{gid} will be replaced by the id of a group data-set

$conf['plugin']['authmysql']['addUserGroup']= "INSERT INTO usergroup (uid, gid)
                                               VALUES ('%{uid}', '%{gid}')";

This statement should remove a group from the database.

• %{group} will be replaced by the group name
• %{gid} will be replaced by the id of a group data-set

$conf['plugin']['authmysql']['delGroup']    = "DELETE FROM groups
                                               WHERE gid='%{gid}'";

This statement should return the database index of a given user name. The module will access the index with the name 'id' so an alias might be necessary.

• %{user} will be replaced by the user name

$conf['plugin']['authmysql']['getUserID']   = "SELECT uid AS id FROM users WHERE login='%{user}'";

This statement should return the database index of a given group name. The module will access the index with the name 'id' so an alias might be necessary.

• %{group} will be replaced by the group name

$conf['plugin']['authmysql']['getGroupID']  = "SELECT gid AS id FROM groups WHERE name='%{group}'";

You additionally need the SQL statements in this section if you want to remove users from the database with the user manager.

This statement should remove a user from the database.

• %{user} will be replaced by the user name
• %{uid} will be replaced by the id of a user data-set

$conf['plugin']['authmysql']['delUser']     = "DELETE FROM users
                                               WHERE uid='%{uid}'";

This statement should remove all connections from a user to any group (a user quits membership of all groups).

• %{uid} will be replaced by the id of a user data-set

$conf['plugin']['authmysql']['delUserRefs'] = "DELETE FROM usergroup
                                               WHERE uid='%{uid}'";

This statements should modify a user entry in the database. The statements UpdateLogin, UpdatePass, UpdateEmail and UpdateName will be added to updateUser on demand. Only changed parameters will be used.

• %{user} will be replaces by the user name
• %{pass} will be replaced by the encrypted or clear text password (depends on 'forwardClearPass')
• %{email} will be replaced by the email address
• %{name} will be replaced by the user's full name
• %{uid} will be replaced by the user id that should be updated

$conf['plugin']['authmysql']['updateUser']  = "UPDATE users SET";
$conf['plugin']['authmysql']['UpdateLogin'] = "login='%{user}'";
$conf['plugin']['authmysql']['UpdatePass']  = "pass='%{pass}'";
$conf['plugin']['authmysql']['UpdateEmail'] = "email='%{email}'";
$conf['plugin']['authmysql']['UpdateName']  = "fullname='%{name}'";
$conf['plugin']['authmysql']['UpdateTarget']= "WHERE uid=%{uid}";

This statement should remove a single connection from a user to a group (a user quits membership of that group).

• %{user} will be replaced by a user name
• %{uid} will be replaced by the id of a user data-set
• %{group} will be replaced by a group name
• %{gid} will be replaced by the id of a group data-set

$conf['plugin']['authmysql']['delUserGroup']= "DELETE FROM usergroup
                                               WHERE uid='%{uid}'
                                               AND gid='%{gid}'";

This is an example of the authmysql plugin configuration. The example is based on the above given SQL statements. Use the Config Manager or add it to the conf/local.protected.php to store the config protected.

conf/local.protected.php

<?php
/**
 * Configuration for MySQL Auth Plugin
 * See https://www.dokuwiki.org/plugin:authmysql for details and explanation
 */
 
//database access
$conf['plugin']['authmysql']['server']   = 'localhost';
$conf['plugin']['authmysql']['user']     = '';
$conf['plugin']['authmysql']['password'] = '';
$conf['plugin']['authmysql']['database'] = '';
 
//Debug level: 0 = disable, 1 = only mysql errors, 2 = print every sent SQL query 
$conf['plugin']['authmysql']['debug']= 2;
 
//multiple table operation needs locks
$conf['plugin']['authmysql']['TablesToLock']= array("users", "users AS u", "groups", "groups AS g", "usergroup", "usergroup AS ug");
 
//Where password encryption is done, 0 by DokuWiki (recommended) or 1 by database
$conf['plugin']['authmysql']['forwardClearPass'] = 0;
 
/**
 * Basic SQL statements for user authentication (required) 
 */
 
//This statement is used to grant or deny access to the wiki. 
$conf['plugin']['authmysql']['checkPass']   = "SELECT pass
                                               FROM usergroup AS ug
                                               JOIN users AS u ON u.uid=ug.uid
                                               JOIN groups AS g ON g.gid=ug.gid
                                               WHERE login='%{user}'
                                               AND name='%{dgroup}'";
//Return a table with exact one row containing information about one user
$conf['plugin']['authmysql']['getUserInfo'] = "SELECT pass, fullname AS name, email AS mail
                                               FROM users
                                               WHERE login='%{user}'";
//get all groups a user is member of                                            
$conf['plugin']['authmysql']['getGroups']   = "SELECT name as `group`
                                               FROM groups g, users u, usergroup ug
                                               WHERE u.uid = ug.uid
                                                 AND g.gid = ug.gid
                                                 AND u.login='%{user}'";
/**
 * Additional minimum SQL statements to use the user manager
 */
 
//return a table containing all user login names that meet certain filter criteria
$conf['plugin']['authmysql']['getUsers']    = "SELECT DISTINCT login AS user
                                               FROM users AS u 
                                               LEFT JOIN usergroup AS ug ON u.uid=ug.uid
                                               LEFT JOIN groups AS g ON ug.gid=g.gid";
$conf['plugin']['authmysql']['FilterLogin'] = "login LIKE '%{user}'";
$conf['plugin']['authmysql']['FilterName']  = "fullname LIKE '%{name}'";
$conf['plugin']['authmysql']['FilterEmail'] = "email LIKE '%{email}'";
$conf['plugin']['authmysql']['FilterGroup'] = "name LIKE '%{group}'";
$conf['plugin']['authmysql']['SortOrder']   = "ORDER BY login";                                         
 
/**
 * Additional SQL statements to add new users with the user manager 
 */
 
//should add a user to the database
$conf['plugin']['authmysql']['addUser']     = "INSERT INTO users
                                               (login, pass, email, fullname)
                                               VALUES ('%{user}', '%{pass}', '%{email}', '%{name}')";
//should add a group to the database
$conf['plugin']['authmysql']['addGroup']    = "INSERT INTO groups (name)
                                               VALUES ('%{group}')";
//should connect a user to a group (a user become member of that group).
$conf['plugin']['authmysql']['addUserGroup']= "INSERT INTO usergroup (uid, gid)
                                               VALUES ('%{uid}', '%{gid}')";
//This statement should remove a group fom the database
$conf['plugin']['authmysql']['delGroup']    = "DELETE FROM groups
                                               WHERE gid='%{gid}'";
//This statement should return the database index of a given user name.
$conf['plugin']['authmysql']['getUserID']   = "SELECT uid AS id FROM users WHERE login='%{user}'";
 
/**
 * Additional SQL statements to delete users with the user manager
 */
 
//return the database index of a given group name
$conf['plugin']['authmysql']['getGroupID']  = "SELECT gid AS id FROM groups WHERE name='%{group}'";
//should remove a user fom the database.
$conf['plugin']['authmysql']['delUser']     = "DELETE FROM users
                                               WHERE uid='%{uid}'";
//This statement should remove all connections from a user to any group
$conf['plugin']['authmysql']['delUserRefs'] = "DELETE FROM usergroup
                                               WHERE uid='%{uid}'";
/**
 * Additional SQL statements to modify users with the user manager 
 */
 
//should modify a user entry in the database
$conf['plugin']['authmysql']['updateUser']  = "UPDATE users SET";
$conf['plugin']['authmysql']['UpdateLogin'] = "login='%{user}'";
$conf['plugin']['authmysql']['UpdatePass']  = "pass='%{pass}'";
$conf['plugin']['authmysql']['UpdateEmail'] = "email='%{email}'";
$conf['plugin']['authmysql']['UpdateName']  = "fullname='%{name}'";
$conf['plugin']['authmysql']['UpdateTarget']= "WHERE uid=%{uid}";
 
//should remove a single connection from a user to a group
$conf['plugin']['authmysql']['delUserGroup']= "DELETE FROM usergroup
                                               WHERE uid='%{uid}'
                                                 AND gid='%{gid}'";                                         

This is a very simple example database.

CREATE TABLE `users` (
  `uid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `login` VARCHAR(20) NOT NULL DEFAULT '',
  `pass` VARCHAR(60) NOT NULL DEFAULT '',
  `fullname` VARCHAR(255) NOT NULL DEFAULT '',
  `email` VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `login` (`login`)
) ENGINE = MYISAM;
 
CREATE TABLE `groups` (
  `gid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  PRIMARY KEY  (`gid`),
  UNIQUE KEY `name` (`name`)
) ENGINE = MYISAM;
 
CREATE TABLE `usergroup` (
  `uid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  `gid` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY  (`uid`,`gid`)
) ENGINE = MYISAM;

Storing the fullname in one single database field may be sufficient for some basic tasks but it has a lot of advantages to split the name up in firstname and lastname. Only one table of the above example need some tuning to get this to work:

CREATE TABLE `users` (
  `uid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `login` VARCHAR(20) NOT NULL DEFAULT '',
  `pass` VARCHAR(60) NOT NULL DEFAULT '',
  `firstname` VARCHAR(255) NOT NULL DEFAULT '',
  `lastname` VARCHAR(255) NOT NULL DEFAULT '',
  `email` VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `login` (`login`)
) TYPE = MYISAM;

To use this slightly changed structure without the need to recode major parts of DokuWiki, it becomes necessary to modify some of our SQL statements:

$conf['plugin']['authmysql']['getUserInfo'] = "SELECT pass, CONCAT(firstname,' ',lastname) AS name, email AS mail
                                               FROM users
                                               WHERE login='%{user}'";
$conf['plugin']['authmysql']['addUser']     = "INSERT INTO users
                                               (login, pass, email, firstname, lastname)
                                               VALUES ('%{user}', '%{pass}', '%{email}',
                                               SUBSTRING('%{name}', 1, LOCATE(' ', '%{name}')),
                                               IF(Length('%{name}') = Length(REPLACE('%{name}', ' ', '')),'',SUBSTRING_INDEX('%{name}', ' ',-((Length('%{name}')-Length(REPLACE('%{name}', ' ', '')))))) )";
$conf['plugin']['authmysql']['FilterName']  = "CONCAT(firstname,' ',lastname) LIKE '%{name}'";
$conf['plugin']['authmysql']['UpdateName']  = "firstname=SUBSTRING_INDEX('%{name}',' ', 1),
                                               lastname=IF(Length('%{name}') = Length(REPLACE('%{name}', ' ', '')),'', SUBSTRING_INDEX('%{name}', ' ',-((Length('%{name}')-Length(REPLACE('%{name}', ' ', ''))))))";

This modifications in the first statement and also in the FilterName join the firstname and the lastname to fullname used by DokuWiki and the second statement splits the given fullname up into two parts: firstname and lastname. This should also work if the last name consists of multiply words like “de la Cruz”. And anyone who registers with a one-word name will have all of it put into the “lastname” field of the database.

Please keep in mind that if you set 'forwardClearPass = 1' the cleartext password is filled in here. You should at least replace %{pass} with MD5('%{pass}') or better set 'forwardClearPass = 0' and let DokuWiki do the crypting. I really recommend the second method.

For reporting issues about this AuthMySQL plugin, please refer to https://github.com/dokuwiki/dokuwiki/issues.

Unfortunately this plugin does not work with PHP7, which has deprecated all the MYSQL_ functions. The current mysqlauth plugin does not match the fixes suggested by Jacket. It's largely the same, but there was one line that was still killing the operation with PHP7.

Here's the stock authmysql file, updated to mysqli: authmysql.zip. Almost all of the fixes were as described in Jacket's patch file, but for one function (below). The commented out line was the original, but it seemed to be checking something that didn't need to be checked, and it caused an error 500, so I simply removed it. The empty lines are where its error result code went.

It's probably a dangerous chunk of code, so you shouldn't use it, but I use it and it seems fine. Be careful.

    protected function _openDB() {
        if(!$this->dbcon) {
            $con = mysqli_connect($this->getConf('server'), $this->getConf('user'), conf_decodeString($this->getConf('password')), $t
            if($con) {
                // if((if (mysqli_connect_errno()) mysqli_select_db($this->getConf('database'), $con))) {
                    if((preg_match('/^(\d+)\.(\d+)\.(\d+).*/', mysqli_get_server_info($con), $result)) == 1) {
                        $this->dbver = $result[1];
                        $this->dbrev = $result[2];
                        $this->dbsub = $result[3];
                    }
                    $this->dbcon = $con;
                    if($this->getConf('charset')) {
                         mysqli_query('SET CHARACTER SET "'.$this->getConf('charset').'"', $con);
                    }
                    return true; // connection and database successfully opened
 
 
            } else {
                $this->_debug(
                    "MySQL err: Connection to {$this->getConf('user')}@{$this->getConf('server')} not possible.",
                    -1, __LINE__, __FILE__
                );
            }
 
            return false; // connection failed
        }
        return true; // connection already open
    }

— NFG 2018-03-16 01:40

Wishlist

• It would be useful to have a tutorial and example script to import the users from file users.auth.php to MySQL tables.

In the SQL snippets above I can't find the handling of the usergroup table's fields in case of adding/deleting records to/from the respective master table: The addUserGroup and delUserGroup statements are separated and since there are neither transactions nor FK constraints involved the whole construct is not safe. While this could be left to the DBMS (provided that you're using InnoDB tables with the correct FK/DELETE/UPDATE constraints, which would obsolete as well application level table locking and for example delUserGroup), here (i.e. when using MyISAM) you have to do this on application level.

Table usergroup is handled by addUserGroup and delUserGroup. Multi-statement commands will be protected by table locks. This backend must also work with MySQL 3.23 and because neither transaction are well supported nor innoDB work without a lot of tweaking in 3.23, transactions aren't used here. This may change in future.
— Matthias Grimm 2006-01-31 19:44

Hence you should either make sure that there's no way to add/remove records to/from the users / groups tables without updating the usergroup table appropriately or consequently use the DBMS features provided by the InnoDB table type as mentioned above. In any case, however, whatever you choose, always specify the table type you want to use!
– 2005-12-04 Matthias Watermann

If you have trace_mode on in you MySQL config, you may find that even if all your queries are OK, usermanager still reports “Found 0 users”. To fix this find a way to turn off mysql_trace_mode like This one.

Apparently the default setting for this has changed since MySQL version 5.2.6 from off to on.

If you don't want, or can't change mysql_trace_mode, there is another fix. Open the file WIKIPATH/inc/auth/mysql.class.php, find “function getUserCount($filter=array()) {” (must be line 309). Replace the whole getUserCount() function with this:

    function getUserCount($filter=array()) {
      $rc = 0;
      if($this->_openDB()) {
        $sql = $this->_createSQLFilter($this->cnf['getUsers'], $filter);
 
        if ($this->dbver >= 4) {
          $sql = substr($sql, strpos(strtolower($sql),"from"));  /* remove everytnig between 'FROM' */
          $sql = "SELECT COUNT(*) ".$sql." LIMIT 1";
          $result = $this->_queryDB($sql);
          $rc = $result[0]['COUNT(*)'];
        } else if (($result = $this->_queryDB($sql)))
          $rc = count($result);
 
        $this->_closeDB();
      }
      return $rc;
    }

Hope this will save someone the hours I spend investigating.

— Jacket 2010/02/10 12:41