My Adventures in sql dumping and database migration

My latest problem project presented itself as a phpbb conversion. The client was having some issues with the old versions of phpbb so we elected to move them to the new version. While this is a great idea on the surface I did not realize how big the database was. I started out doing an export using phpMyAdmin not knowing the file sizes I was dealing with. When that failed I decided to do a mysqldump from the shell. Imagine the look of shock and dumbfoundedness on my face when I finished the dump to discover a 300 meg + sql file.

Why is this a problem? Well most ISPs have upload limits usually under 20meg for import uploads using phpMyAdmin. My default install of Xampp limit is 16 meg. I decided to try to gzip the file with a 100meg + result. At least now I could download the file to my local machine to try to figure out what to do.

First Trial PhpMyAdmin Local File upload

I edited my php.ini files and change the file_upload, and max_execution time to try and enable a way to upload the file.

max_execution_time = 560
max_input_time = 160
memory_limit = 60M
upload_max_filesize = 160M
 

After an hour of battling the settings to try and get one that would work, I had to read some phpMyadmin documentation and see if I could find a solution.

PhpMyAdmin Config Change
My first config setting change was a nice config setting called $cfg['UploadDir']. With this option set to a local server folder, you can select a direct data upload from a local drive. When you open phpMyAdmin it will give you a list of files to select from in the folder. You can then select the file and it will perform a direct import of the larger than limit sql file. However, this still didn't solve the timeout problems with a 100meg sql file. I had to look further.

SQL file splitter

After kicking around some work a rounds, I realized the only way to beat this, was to split the file into parts. I can either do this by hand, or find a solution.

Google is a wonderful tool for finding things and it didn't let me down this time either.

I found a blog the outlined exactly what I was looking for: Sql file splitter From reviewing his blog It appeared he had the exact same issues I had. The solution was a program called SQLDumpSplitter 2, by Philip Lehmann-Böhm (http://www.philiplb.de). The main links on the blog no longer worked, but a dutch website had left a blog comment with a direct link to the file. You can download from here: Sqldumpsplitter2 Hopefully that file link will stick around for a while.

I split the 300 meg file into 23 16-meg chunks. This worked very well, I was able to import the large 300 meg file, but it still took baby sitting and remembering what the last file I used was. In fact I spent several hours importing and then still experienced some time out issues.

I then decided to see if I could find another solution, so it was back to google.

php mysql large file importer

was the keyword search I did. What I found was this blog: How to import large mysql data files. This WAS the solution to all my problems!!! In this blog, there was a link to a site and a program called Bigdump.

Bigdump is a staggered mysql dump importer. What this means, is you start the file, and it will automatically import the large file till it is complete. That means, no file splitting. It is truly an amazing piece of 10K of code.

To import a large file you perform the following steps:

1 - Copy bigdump.php to the website where you want to import the file.
2 - If the file size is larger that the upload limit of the server, copy the
    file to the same foldere.
3 - Edit the bigdump.php file, set the database name, the username and password.
4 - If the file size is larger than the limit, enter the filename into the
    "filename" field.
5 - Execute the bigdump file by going to http://yourdomainlocation/bigdump.com
6 - Wait for the import to complete and be prepared for the shock and awe.
 

For my test I used the uncompressed 300 meg sql file. The documentation states, you can use the gzipped version also. It took approx 15 minutes or less, to import that file with the bigdump program. I still am amazed. Had I found this earlier today, this blog may not actually exist.

Hurrah for Bigdump!!!! I gladly sent a paypal donation for this code! It saved my life.

I hope that the users that are looking for a sql splitter will find this blog and save themselves the initial headaches I had.

Dealing with date based data can be a mind numbing experience. Calculation date differences, doing date comparisons can really stretch you to the limits at times. There is a variety of ways to deal with date based data but the first step usually is to convert it into a standard format that you can easily deal with. That format is the unix timestamp.

The unix time stamp is a way to track time as a running total of seconds. This count starts at the Unix Epoch on January 1st, 1970. Therefore, the unix time stamp is merely the number of seconds between a particular date and the Unix Epoch.

Below is a code snippet for creating a timestamp from formatted date data. For this example the date_in variable contains a date in the format of YYYY-MM-DD and is passed from a previously executed form post.

$datearray=explode("-",$_POST['date_in']);
$year=$datearray[0];
$month=$datearray[1];
$day=$datearray[2];
$mytimestamp=mktime(0,0,0,$month,$day$year);

Recently I discovered an easier way to get a date converted to a timestamp. This just goes to show you that a programmer is always learning. There will always be functions that are overlooked and underused.

strtotime() function this function will take an english based date format and convert it to timestamp. The code below illustrates how much easier this is.

$mytimestamp=strtotime($_POST['date_in']);

One line of code! No exploding parts, just a quick and easy solution.

Calculate Number of days

Now that timestamps have been reviewed to show you some easy way to take two days and return a total number of days. The example below will take an arbitrary date of April 4, 2008 and May 12, 2008 (the current date I wrote the article)

$time_A = strtotime("April 4, 2008");
$time_B=strtotime(now);
$numdays=intval(($time_B-$time_A)/86400)+1;

The number of seconds in a day is 86400 and the calculations usually shorts you a day so thats why we add a 1. This is do the fact the now() returns the current time which is not a complete day.

Another example of using date comparisons is expiring accounts in a database. If you can let the database do the work for you, DO IT, as anytime you can save code calculations the easier it is on you. This code will take the current date and compare it to the user_expdate and delete those records that are less than or equal to 0.

 $current_date=date("Y-m-d G:i:s"); 
 
  $query="delete from {$db_prefix}users where user_expdate!='0000-00-00
 00:00:00' and datediff(user_expdate,'".$current_date."')<= 0 ";
  if (!$result=$db->execute($query)){
    $db->ErrorMsg();
  }

Date comparisons

Date comparisons are now simple a matter of comparing the timestamps. Given 2 timestamps you can compare them and know very simply which is greater. Note: in the example below we forced the today variable to midnight so only days before the two timestamps are selected.

$today=strtotime(date("Y-m-d")." 00:00:00");
$datecheck = strtotime($year."-".$month."-".$day);
if ($datecheck < $today){
     //find all dates before today and block them out
}

Date comparisons in mysql can be done similarly. However mysql give a variety of date tools to work with as you are calculating dates.

The following code snipplet shows a way to delete code based on a timestamp comparison. Note: The code uses ADODB Lite calls. The $db->qstr function add's the appropriate quote code.

$today=strtotime(date("Y-m-d")." 00:00:00");
 
 $query="delete from {$db_prefix}users where user_expdate!='0000-00-00
 00:00:00' and timestamp(user_expdate) < ".$db->qstr($today) ";
  if (!$result=$db->execute($query)){
    $db->ErrorMsg();
  }

While these are some basic ways to do this, there are many other ways. I always welcome comments, feel free to add your own two cents.

Just make sure you register as No Spammers are allowed.

There is one programing concept that always hurts my head, and that is the variable variables or dynamic variables. What dynamic let you do is access data from the value of the a variable you are viewing. (See I told you it would hurt your head)

 
$b =20;  // set the value of b
$c="b";  // set c to the name of the variable you want to view
$d=${$c}; // the variable variable  $d is now equal to 20
 

Many of you are probably asking yourself, " OK I understand that , but why do I need to do that?"
The number one place I use this, is in a web grid form. Any place you need to update/or delete data in that grid. Below is an example of what a grid of data may look like:

The issues you face in the above grid is how do I delete multiple items and change multiple elements of data in grid once it is submitted. The solution is you assign each field a row number and add that to the field name.

Example:

 
<input name="del_1" type="checkbox" />
<input name="id_1" type="hidden" value="117" />
<input name="del_2" type="checkbox" />
<input name="id_2" type="hidden" value="95" />

Note each field had its name attached with a _row number so you will see an id_1 as a field name. After this form is submitted you can use the below php code to get the values dynamically.


// Check for item delete
if ((isset($_POST['delete'])) or (isset($_POST['delete_x']))){
for ($i=0; $i<=$totrecs; $i++)
{
$tdel=$_POST['del_'.$i];
$tid=$_POST['id_'.$i];
// Another way to do this is this way
// $tempvar="id_".$i;
// $tid=$_POST[${$tempvar}];
if ($tdel=="on")
{
// Delete Item, the item ID is $tid
}
} // Loop
} // End Post Check


Template Lite And Dynamic Variables
Form Data grid is also the reason I used dynamic variables in templates. Once I have edited a data grid I need to set values in the grid, select drop downs and, check radio or check boxes. There is some extra steps you need to do to use them and it also depends on how you pass your data to template lite. The following is an example showing a more complex use of variable variables.

{foreach key=key2 value=item2 from=$jetarray }
{ assign var="dummyvar1" value="`$item2.jetski_id`_bookid"}
{ assign var="dummyvar2" value="`$item2.jetski_id`_owner"}
{ assign var="cellcolor" value="`$item2.jetski_id`_color"}
{ assign var="dummyvar3" value="`$item2.jetski_id`"}
 
 {if $item[$dummyvar1]!=""}
   {if $session.myid == $item[$dummyvar2]}
<input name="jetid_{$item2.jetski_id}_{$key}" type="hidden"
 value="{$item2.jetski_id}" />
   {/if}
{/if}
 
 {/foreach}

Note we assigned a variable $dummyvar1 with the value $item2.jetski_id and appended _bookid to that variable name. Jetski number 1 turns into 1_bookid. After that we pull the value out of the array $item[$dummyvar1] so what we are actually doing is viewing $item['1_bookid'].

While there are some other uses for dynamic variables these are how I most recently have used them. If it helps someone other than myself, cool. Otherwise this is a place I have my own personal reference library.

One of the greatest tools I have in my library set is Template Lite . Template lite is a light-weight version of another template engine known as Smarty. Once you start using template lite you will be pleasantly surprised by how powerful yet modular it is.

My whole point of this blog is to talk about modifiers. A modifier enables you to pass some php modifying code into a template lite variable.

Example:


{$mytlitevariable|ucfirst }
//This would capitalize the first letter of the variable.
What doesn't work

It is important to note that you cannot necessarily use this on all modify type php functions. Some php functions do not pass the main variable into the first option.

Using str_replace would not work as the passed variable is the third option in the command. {$mytlitevariable|str_replace:black:brown }
//is the equivalent to
str_replace($mytlitevariable,"black","brown")
//which will not work. You will have to use it via php:
{php} echo str_replace("black","brown",$mytlitevariable) {/php}

Modifiers Stacking

Modifier stacking allows you to make multiple changes to a variable by passing multiple modifiers to the template lite variable.

Example: {$myvar|ucfirst|truncate:40:"..."}

Creating your own Modifiers

Don't be afraid to create your own modifiers. When ever I run into something that I can't do I add it into template lite.
//Example: This is the source code for the indent modifier.
//Once you create the modifier and save it in the plugins folder with the name
//modifier.indent.php.
{$myvar|indent}
//Note there are 2 extra arguments you can use with it as well.

function tpl_modifier_indent($string,$chars=4,$char=" ")
{
return preg_replace('!^!m',str_repeat($char,$chars),$string);
}
This is the start of my experimentation with blogs. I am constantly forgetting how to do things with template lite and other things. I plan on adding these tips and tricks to my blog so I have one place to search for the info. Feel free to comment as I realize I don't know it all but am willing to share my experiences along the way.