In this chapter, we will learn how to import data that we may have exported for backup or transfer purposes. Exported data may also come from authors of other applications, and could contain the whole foundation structure of these applications, along with some sample data.
The current phpMyAdmin version (3.4) can import the following:
- Files containing MySQL statements (usually having a
.sqlsuffix, but not necessarily so) - CSV files (comma-separated values, although the separator is not necessarily a comma); these files can be imported by phpMyAdmin itself or via the MySQL
LOAD DATA INFILEstatement which enables the MySQL server to handle the data directly rather than having phpMyAdmin parse it first - Open Document Spreadsheet files
- XML files (generated by phpMyAdmin)
The binary column upload covered in Chapter 5 can be said to belong to the import family.
In general, an exported file can be imported to the same database it came from or to any other database; the XML format is an exception to this and a workaround is given in the XML section later in the chapter. Also, a file generated from an older phpMyAdmin version should have no problem being imported by the current version, but the difference between the MySQL version at time of export and the one at time of import might play a bigger role regarding compatibility. It's difficult to evaluate how future MySQL releases will change the language's syntax, bringing import challenges.
The import feature can be accessed from several panels:
- The Import menu available from the home page, the
Databaseview, or theTableview - The Import files menu offered inside the Query window (as explained in Chapter 11)
The default values for the Import interface are defined in $cfg['Import'].
Before examining the actual import dialog, let us discuss some limits issues.
When we import, the source file is usually on our client machine and, therefore, must travel to the server via HTTP. This transfer takes time and uses resources that may be limited in the web server's PHP configuration.
Instead of using HTTP, we can upload our file to the server using a protocol such as FTP, as described in the Reading files from a web server upload directory section. This method circumvents the web server's PHP upload limits.
First, let us consider the time limit. In config.inc.php, the $cfg['ExecTimeLimit'] configuration directive assigns, by default, a maximum execution time of 300 seconds (five minutes) for any phpMyAdmin script, including the scripts that process data after the file has been uploaded. A value of 0 removes the limit, and in theory, gives us infinite time to complete the import operation. If the PHP server is running in safe mode, modifying $cfg['ExecTimeLimit'] will have no effect. This is because the limits set in php.ini or in user-related web server configuration files (such as .htaccess or virtual host configuration files), take precedence over this parameter.
Of course, the time it effectively takes depends on two key factors:
- Web server load
- MySQL server load
Note
The time taken by the file, as it travels between the client and the server, does not count as execution time as the PHP script only starts to execute after the file has been received on the server. Therefore, the $cfg['ExecTimeLimit'] parameter has an impact only on the time used to process data (such as decompression or sending it to the MySQL server).
The system administrator can use the php.ini file or the web server's virtual host configuration file to control uploads on the server.
The upload_max_filesize parameter specifies the upper limit or maximum file size that can be uploaded via HTTP. This one is obvious, but another less obvious parameter is post_max_size. As HTTP uploading is done via the POST method, this parameter may limit our transfers. For more details about the POST method, please refer to http://en.wikipedia.org/wiki/Http#Request_methods.
The memory_limit parameter is provided to prevent web server child processes from grabbing too much of the server's memory—phpMyAdmin runs inside a child process. Thus, the handling of normal file uploads, especially compressed dumps, can be compromised by giving this parameter a small value. Here, no preferred value can be recommended; the value depends on the size of uploaded data we want to handle and on the size of the physical memory. The memory limit can also be tuned via the $cfg['MemoryLimit'] parameter in config.inc.php, as seen in Chapter 6.
Finally, file uploads must be allowed by setting file_uploads to On; otherwise, phpMyAdmin won't even show a dialog to choose a file. It would be useless to display this dialog as the connection would be refused later by the PHP component of the web server.
If the file is too big, there are ways in which we can resolve the situation. If the original data is still accessible via phpMyAdmin, we could use phpMyAdmin to generate smaller export files, choosing the Dump some row(s) dialog. If this were not possible, we could use a spreadsheet program or a text editor to split the file into smaller sections. Another possibility is to use the upload directory mechanism, which accesses the directory defined in $cfg['UploadDir']. This feature is explained later in this chapter.
In recent phpMyAdmin versions, the Partial import feature can also solve this file size problem. By selecting the Allow the interruption… checkbox, the import process will interrupt itself if it detects that it is close to the time limit. We can also specify a number of queries to skip from the start, in case we successfully import a number of rows and wish to continue from that point.
On a server, a PHP security feature called open_basedir (which limits the files that can be opened by PHP to the specified directory tree) can impede the upload mechanism. In this case, or for any other reason, when uploads are problematic, the $cfg['TempDir'] parameter can be set with the value of a temporary directory. This is probably a sub-directory of phpMyAdmin's main directory, into which the web server is allowed to put the uploaded file.