wiki:support-team/mysql-mysqli-conversion

Version 15 (modified by Ross, 10 years ago) (diff)

--

Step for converting red to use mysqli

This page documents how to convert red to use mysqli instead of mysql.

Function changes

Since mysqli is not a drop in replacement for mysql, we need to determine the necessary function changes required by mysqli.

Currently red uses 10 mysql functions, below is a description of each one and the comparable function in mysqli.

mysql_connect

mysql_connect takes only the parameters (host, user, password) and returns the connection resource.

mysqli_connect

For this function requires a few more parameters. Our currently functional implementation looks like this:

$flags = 0;
$flags = MYSQL_CLIENT_SSL;
$sql = mysqli_init();
if (!($sql->options(MYSQLI_READ_DEFAULT_FILE, '/root/horror/my.cnf')))
  printf("failed to set the default file (MYSQLI_READ_DEFAULT_FILE: %d)\n", MYSQLI_READ_DEFAULT_FILE);
if (!($sql->options(MYSQLI_READ_DEFAULT_GROUP, 'red')))
  printf("failed to set the group for the default file (MYSQLI_READ_DEFAULT_GROUP: %d)\n", MYSQLI_READ_DEFAULT_GROUP);
if (!($sql->real_connect($db_host,$db_user,$db_pass, $db_name))) {
  printf("failed to really connect\n");
}
Notable changes
  • $flags = MYSQL_CLIENT_SSL;
    • This flag ensures the client is connection via ssl.
  • $sql->options(MYSQLI_READ_DEFAULT_FILE, '/root/horror/my.cnf')
    • Here we add a new set of options in a custom my.cnf file, probably stored in /user/local/etc/red/my.cnf. The configured options are:
      [red]
      ssl=true
      ssl-ca=/etc/mysql/red-cert.pem
      ssl-verify-server-cert=true
      
      • where red-cert.pem is the mysql ca certificate, which will need to be stored on all MOSHes and on hay.
  • $sql->options(MYSQLI_READ_DEFAULT_GROUP, 'red')
    • This simply says, "Read from the my.cnf group red.
  • $sql->real_connect($db_host,$db_user,$db_pass, $db_name)
    • This makes the mysql connection.

mysql_data_seek

This function just moves the internal result pointer.

mysql_data_seek ( resource $result , int $row_number )

mysqli_data_seek

The mysqli procedural version should be a drop in replacement for our implementation of the function.

mysqli_data_seek ( mysqli_result $result , int $offset )

mysql_error

mysql_error ([ resource $link_identifier = NULL ] )

mysqli_error

The mysqi procedural version should be a drop in replacement.

mysqli_error ( mysqli $link )

mysql_fetch_array

mysql_fetch_array ( resource $result [, int $result_type = MYSQL_BOTH ] )

mysqli_fetch_array

This appears to be a drop in replacement.

mysqli_fetch_array ( mysqli_result $result [, int $resulttype = MYSQLI_BOTH ] )

mysql_fetch_assoc

mysql_fetch_assoc ( resource $result )

mysqli_fetch_assoc

This appears to be a drop in replacement.

mysqli_fetch_assoc ( mysqli_result $result )

mysql_fetch_row

mysql_fetch_row ( resource $result )

mysqli_fetch_row

This appears to be a drop in replacement.

mysqli_fetch_row ( mysqli_result $result )

mysql_insert_id

mysql_insert_id ([ resource $link_identifier = NULL ] )

mysqli_insert_id

This function requires that the mysql $link be supplied.

mysqli_insert_id ( mysqli $link )

It will need to be modified in at least one location /ui/sbin/transfer-red-items-to-new-host:171

mysql_num_rows

mysql_num_rows ( resource $result )

mysqli_num_rows

Should be drop in replacement.

mysqli_num_rows ( mysqli_result $result )

mysql_query

This function can and in some places in red does use the construction $query = mysql_query($sql).

mysql_query ( string $query [, resource $link_identifier = NULL ] )

mysqli_query

It appears that using the procedural style of this function we will need to ensure that the database connection is supplied to the function. In addition in every case we will need to flip the resource identifier and the query string.

mysqli_query ( mysqli $link , string $query [, int $resultmode = MYSQLI_STORE_RESULT ] )

mysql_select_db

mysql_select_db ( string $database_name [, resource $link_identifier = NULL ] )

mysqli_select_db

We will need to change the construct of this function in every case as it flips the resource identifier and database placement in the parameters.

mysqli_select_db ( mysqli $link , string $dbname )

There are a number of places where we reference the resource identifier in mysql_select_db, and other places where we simply use the database name. In the former case, we need to swap the placement of database string and resource, and in the latter case, we need to add the resource identifier ($link).

These functions occur in 22 different files

Struck through filenames have been modified with mysqli fixes.

  • common/share/src/class.red_ado.inc.php
  • common/share/src/class.red_db.inc.php
  • common/share/src/red.utils.inc.php
  • docs/sql/2009-07-08.php
  • node/sbin/find-inactive
  • node/sbin/red-dump-db-list
  • node/sbin/red-node-update
  • node/sbin/red-set-mysql-connection-limits
  • node/share/src/modules/class.red_item_mysql_db_node_mysql.inc.php
  • node/share/src/modules/class.red_item_mysql_user_node_mysql.inc.php
  • ui/sbin/create-reverse-ip
  • ui/sbin/import-addresses
  • ui/sbin/import-dns
  • ui/sbin/import-users-and-email-addresses
  • ui/sbin/mv-server-name-and-alias
  • ui/sbin/populate-member-end-date
  • ui/sbin/print-invoice
  • ui/sbin/transfer-red-items-to-new-host
  • ui/share/src/class.red_api.inc.php
  • ui/share/src/functions.inc.php
  • ui/www/extras/change_password/index.php
  • ui/www/extras/reset_password/index.php

Additional Steps

  • We will need to add a new variable in red_node.conf for the additional cert parameters $mysql_cnf = '/usr/local/etc/red/my.cnf'