wiki:support-team/mysql-mysqli-conversion

Version 5 (modified by Ross, 6 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_assoc

mysql_fetch_row

mysql_insert_id

mysql_num_rows

mysql_query

mysql_select_db

These functions occur in 22 different files

  • 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