CodeIgniter Forums
Database Utility - Backups Not Working with MySQLi - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Database Utility - Backups Not Working with MySQLi (/showthread.php?tid=43705)

Database Utility - Backups Not Working with MySQLi - El Forum - 07-20-2011

[eluser]TomRPS[/eluser]
I am getting the error:
Code:
Unsupported feature of the database platform you are using.

When trying to export a database backup using the db utility class.

Export works OK with MySQL driver. Is there any way I can export the database when using the MySQLi driver?

Or how can I switch the driver just for the export?

Database Utility - Backups Not Working with MySQLi - El Forum - 02-14-2012

[eluser]resolv_25[/eluser]
Same error on CI 2.1,
mysql works properly, but mysqli driver in database.php produces such error.

Database Utility - Backups Not Working with MySQLi - El Forum - 01-11-2013

[eluser]Tim Post[/eluser]
This is actually not implemented in the Mysqli driver. If you look at:

Code:
system/database/drivers/mysqli/mysqli_utility.php

You'll see the stub:

Code:
/**
  * MySQLi Export
  *
  * @access private
  * @param array Preferences
  * @return mixed
  */
function _backup($params = array())
{
  // Currently unsupported
  return $this->db->display_error('db_unsuported_feature');
}

As PHP has begun the deprecation of the mysql_* family of functions, it's probably a good idea to implement this in the not too distant future Smile I think the issue is the mysql_* family has mysql_field_type(), which mysqli does *not* have, so you'd have to work around that in order to generate a dump (presumably, talking to information_schema directly).

Database Utility - Backups Not Working with MySQLi - El Forum - 10-25-2014

[eluser]Unknown[/eluser]
This works for me and it's probable that it would work for other people also. Not sure if it covers all cases (i.e. not so common field types).

Based on what Tim Post said, I decided to rewrite _backup() for mysqli driver, using old _backup() from mysql driver as template.

I know, this is not the desirable way of solving this, but it is the quickest thing I found right now (I am writing this post in the middle of a long night). I will try to overwrite the method in an own library. Let you know if I found a way.

Code:
/**
  * MySQLi Export
  *
  * @access private
  * @param array Preferences
  * @return mixed
  */
function _backup($params = array())
{
/*
  // Currently unsupported
  return $this->db->display_error('db_unsuported_feature');
*/
  if (count($params) == 0)
  {
   return FALSE;
  }

  // Extract the prefs for simplicity
  extract($params);

  // Build the output
  $output = '';
  foreach ((array)$tables as $table)
  {
   // Is the table in the "ignore" list?
   if (in_array($table, (array)$ignore, TRUE))
   {
    continue;
   }

   // Get the table schema
   $query = $this->db->query("SHOW CREATE TABLE `".$this->db->database.'`.`'.$table.'`');

   // No result means the table name was invalid
   if ($query === FALSE)
   {
    continue;
   }

   // Write out the table schema
   $output .= '#'.$newline.'# TABLE STRUCTURE FOR: '.$table.$newline.'#'.$newline.$newline;

   if ($add_drop == TRUE)
   {
    $output .= 'DROP TABLE IF EXISTS '.$table.';'.$newline.$newline;
   }

   $i = 0;
   $result = $query->result_array();
   foreach ($result[0] as $val)
   {
    if ($i++ % 2)
    {
     $output .= $val.';'.$newline.$newline;
    }
   }

   // If inserts are not needed we're done...
   if ($add_insert == FALSE)
   {
    continue;
   }

   // Grab all the data from the current table
   $query = $this->db->query("SELECT * FROM $table");

   if ($query->num_rows() == 0)
   {
    continue;
   }

   // Fetch the field names.
   // We are going to surround all values with single quotes
   // and hope that mysql would be able to make type conversion...
   $field_str = '';
   foreach ($query->row() as $field_name => $field_value) $field_str .= '`'.$field_name.'`, ';

   // Trim off the end comma
   $field_str = preg_replace( "/, $/" , "" , $field_str);

   // Build the insert string
   foreach ($query->result_array() as $row)  
   {
    $val_str = '';

    $i = 0;
    foreach ($row as $v)
    {
     // Is the value NULL?
     if ($v === NULL)
     {
      $val_str .= 'NULL';
     }
     else
     {
      $val_str .= $this->db->escape($v);
     }

     // Append a comma
     $val_str .= ', ';
     $i++;
    }

    // Remove the comma at the end of the string
    $val_str = preg_replace( "/, $/" , "" , $val_str);

    // Build the INSERT string
    $output .= 'INSERT INTO '.$table.' ('.$field_str.') VALUES ('.$val_str.');'.$newline;
   }

   $output .= $newline.$newline;
  }

  return $output;
}


Database Utility - Backups Not Working with MySQLi - El Forum - 10-25-2014

[eluser]Unknown[/eluser]
Probably you would face the same problem than me if you are trying to export BIG tables. Here you are a quick workaround.

Replace this...

Code:
// Grab all the data from the current table
$query = $this->db->query("SELECT * FROM $table");

if ($query->num_rows() == 0)
{
continue;
}

with this...

Code:
// Grab all the data from the current table - in slices of 1000 records
// Workaround for BIG tables
$slice = 0;
$query = $this->db->query("SELECT * FROM $table LIMIT $slice, 1000;");

while ($query->num_rows() !== 0)
{

... and then, replace this...

Code:
$output .= $newline.$newline;
}

with this...

Code:
$output .= $newline.$newline;

$slice = $slice + 1000;
$query = $this->db->query("SELECT * FROM $table LIMIT $slice, 1000;");
}


Database Utility - Backups Not Working with MySQLi - El Forum - 10-27-2014

[eluser]www.sblog.in[/eluser]
Example http://www.webtuts.in/backup-site-and-database-in-codeigniter/