What will not work
There are many details which other authors will forget to discuss, but those small issues can overwhelm a novice. Here are some of them which I have either encountered or recalled:
1. Some hosting providers have the database located on localhost (same server which serves the website itself), but some (such as 1and1) have the database on a separate server. Hosting providers which use cPanel usually have the database on localhost. If your database is not on localhost (same server as Apache server), then you also need to specify the host for such commands as mysql import and mysql dump, for example:
mysqldump -h hostname.domainofsomeserver.com otheroptions...
mysql -h hostname.domainofsomeserver.com otheroptions...
NOTE: the domain here is not your website address, but is a physical address of the server inside of your hosting company facility. It can consist of strange letters and numbers, such as:
db711232300.db2.1and1.com
2. In most instances you will also need to provide a password, otherwise you might get an error such as:
mysql: Got error: 1045: Access denied for user '*' (using password: *) when trying to connect
If using password: YES, then you are using an incorrect password (more on this later).
If using password: NO, then you probably need to specify the password.
There are two ways to specify the password. You can specify it on the same line as the rest of your command, as:
otheroptions... -p=yourpassword otheroptions...
Or if you just put in
otheroptions... -p otheroptions...
Then after you input the command, the terminal will then prompt you for:
Enter password:
3. Your mysql dump may fail to be imported. There are many causes for this. One of them is when the dump file specifies the DROP or CREATE DATABASE commands, such as:
DROP DATABASE 'dbname';
CREATE DATABASE 'dbname';
If you are importing data into a pre-existing database (which you have already created), you will need to manually erase these lines from the dump file. For your next mysqldump command, specify this to avoid that line created:
--no-create-db=true
4. Your password might not work, when you are sure you are specifying the correct one. This happens when the table prefix of the dump file has names of the old database, but the new database has a different name. This happens because most hosting providers assign sequential names for the database. For example,
Old database db2244 contained tables with prefix db2244_
New database is named db 2245.
In this case, open the dump file in a UTF-8 compatible editor (NOT NOTEPAD), such as Notepad++ for example, and do a search&replace on the prefix, replacing it with the name of the new database. NOTE: if you do this, software such as a CMS may rely on a setting of the table prefix, and may stop working until that setting is corrected with the new name. TAKE CARE to not modify encoding of the file when saving it after such an edit.
5. Many online instructions tell you to do a search-and-replace for string latin1 and replace it with utf8. This only works for a database which is already SQL version 5 format, and a dump of which contains the statement such as
DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
A SQL version 4 database and / or its dump will not have this specification. Following instructions which rely on this operation will be useless and a waste of your time.
Also, these instructions often fail to mention to replace the COLLATE statement as well.
6. The following commands and / or instructions have been confirmed to NOT WORK AND BE USELESS:
file dump.sql
This *nix command shows the encoding of the file, but it cannot tell you whether the data contained within is garbled or not. You must view the file yourself with a UTF-8 capable viewer.
iconv -f ISO8859-1 -t UTF-8 dump.sql > dump_utf8.sql
Our procedure eliminates the need for such conversion because a properly set-up mysqldump command will export UTF-8 formatted data. Also, this command converts the encoding of the file, but does not replace the CHARSET and COLLATION statements with the proper ones! You must also set those to the proper values.
ALTER DATABASE <database_name> CHARACTER SET utf8;
This command only changes how future operations with the database are formatted, and does not modify the existing data, encoding, or collation of the database and data you have.
ALTER DATABASE 'db_name' COLLATE utf8_general_ci;
This command only changes how future operations with the database are formatted, and does not modify the existing data, encoding, or collation of the database and data you have.
7. "ERROR 1044 (42000) at line 17: Access denied for user 'dbusername'@'%' to database 'dbname'"
This error is shown if the database contains statements for which you do not have permissions for (DROP, CREATE), or it contains name of previous database name from which it was exported, or it contains incorrect table prefix names. Replace the old database table prefix name occurrences with the new database name, and change your CMS configuration file accordingly.
8. Avoid the urge to edit ANYTHING directly in PHP MyAdmin. If your browser is not set to the correct encoding, then editing any fields will cause incorrectly-encoded data to be written to your database, which will then appear incorrectly on your website when parsed by the CMS. If you wish to view or edit any data with MyAdmin,DO NOT EDIT YOUR DATABASE UNLESS THE BROWSER IS SET TO EXACTLY THE SAME ENCODING AS THE DATABASE CONTENT ACTUALLY IS, OR YOU WILL OVERWRITE YOUR DATABASE WITH INCORRECT ENCODING DATA.
9. For the correct encoding to appear to your website visitor, DATABASE ENCODING MUST MATCH CMS CONFIGURATION FILE ENCODING, AND MUST MATCH META STATEMENT (<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />, AND MUST MATCH BROWSER ENCODING (THIS IS SUGGESTED BY META SPECIFICATION) WHEN VIEWING OR EDITING CONTENT TO PREVENT ENCODING PROBLEMS
10. WHEN YOU CREATE IT, YOUR DATABASE MUST START OUT AS A VERSION 5 MYSQL DATABASE, AND WITH UTF-8 ENCODING, UTF8_GENERAL_CI TO BEGIN WITH, NOT A LOCAL CHARSET LIKE LATIN1. Also, OPERATIONS tab of PHP MyAdmin must show "utf8_general_ci" collation for all future operations with the database. See this article on further instructions about verifying that your database is fully set for UTF-8 operation.
11. CHECK THAT DATABASE ENCODING MATCHES PAGE META ENCODING SETTING (THIS IS WHAT CMS TELLS YOUR BROWSER THE ENCODING OF THE DATABASE IS). IF THERE IS A MISMATCH, YOU HAVE A TEMPLATE PROBLEM.
˅˅˅ Additional valuable information is available at one of the links below: ˅˅˅
Did you like the article? Let Google Search know by clicking this button:
Page last modified 06-Jan-13 20:34:07 EST
Previous page: How to check what your database encoding is set to
Next page: Procedure Outline
. 