Before importing a UTF-8 dataset, be sure to change the default MySQL database collation to utf8_unicode_ci.
Note: If the tables and structures are in the utf8 collation but the database is on a Latin collation, the import will not be successful.
The database, tables and structures need to be all UTF8 enabled.
When escaping a MySQL query, be sure to escape the variables in the correct order.
Example:
UPDATE
table_name
SET
var1='%s',
var3='%s',
var2='%s'
WHERE
foo=bar
mysql_real_escape_string($var1, $db),
mysql_real_escape_string($var3, $db),
mysql_real_escape_string($var2, $db)
The mysql_real_escape_string function will escape variables in the order specified in SET
You've probably grabbed a bunch of records from the database, pumped them into an array and then tried to delete the duplicate records. The result is a dataset that is messed up, the reason why is unknown.
Lets look at the array:
// Your query here
$SqlSelectRow = mysql_fetch_array($SqlSelectResult)
This behavior occurs because you perform the action on the contents in the array. Updates to the array are not reflected on the database, and therein lies the problem.
Be sure to directly update records in the database or have the array update contents in the db.
An easy solution though is to set the column as 'unique' and then dump the dataset. Be sure to remove the unique attribute once this process completes if you want duplicate records during future inserts.
Before dumping a list of databases between different MySQL servers, be sure to exclude the MySQL database.
Assuming you are importing the dump from Server A to Server B, the database imports the password from Server A onto B. There could be a few other settings that could be messed up too in the MySQL db if the versions are different.
To resolve this issue, set the MySQL password /etc/my.cnf on Server B to the one that was set on Server A. Restart the MySQL daemon.
To prevent this issue from occurring, specify the --databases parameter and explicitly mention the database names to be included before the process is executed.
Slow MySQL Performance over a USB Bus
Parsing a 3gig MySQL dB with ~5 million datasets can be agonizingly slow. The bottleneck here being the USB bus.
I had to get MySQL running off an external USB HDD due to the heat generated on my notebook (Intel Core 2 Duo, SATA HDD).
The only advantage of this setup is the heat issue. As for the cons, there are plenty:
- USB connector can break the connection for some reason
- Slow USB bus
- USB external is powered through AC, brick needs to convert from AC -> DC
- The external IDE connector
Time consumed:
100 tables per week
~2.5 weeks
22/7, One 10-15 minute standby break before script execution
Filesystem: NTFS
dB dumped and imported to an ext3 filesystem
mysqldump: Couldn't execute SHOW TRIGGERS LIKE
Importing a database with 251+ tables on Windows XP results in the following error:
mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE table_name. Can't create
ate/write to file '\tmp\#sql_8d4_0.MYD' (Errcode: 17) (1)
This issue is due to the windows file handles.
[quote]
Please examine the server status variable "open_files_limit" and note that
mysqld requires two open files for each myisam table.
On my machine it looks like this:
mysql> SHOW VARIABLES LIKE 'Open_files_limit';
Variable_name Value
open_files_limit 1024
From the manual:
The number of files that the operating system allows mysqld to open. This is
the real value allowed by the system and might be different from the value you
gave using the --open-files-limit option to mysqld or mysqld_safe. The value is
0 on systems where MySQL can't change the number of open files.
When mysqldump tries to dump the files, it will first take a read lock on all
the tables in the database. That requires it to open all of them at the same
time. So if the number of available open files is low, this kind of error can
occur.
To make mysqldump avoid taking the read lock use --skip-lock-tables option. I
successfully used that to dump more tables than my system had file descriptors.
It should also be possible to put a smaller number of tables in each database or
only dump a selected number of tables at a time.
But that are workarounds, best thing is to increase the number of open files on
the system.
[/quote]
Source:
http://bugs.mysql.com/bug.php?id=17089
mysqldump: Couldn't execute SHOW TRIGGERS LIKE
CSV Invalid Field When Importing Into MySQL
Check that there are no funny characters such as single quotes or double quotes.
The import fields need to be specified correctly.
The CSV input file needs to be saved in the CSV format.
CSV Invalid Field When Importing Into MySQL
Restore a MySQL Database
Through Shell:
$ mysql -uUserName -pPassword -hlocalhost DatabaseName < mysql_db.sql
Restore a MySQL Database
Backup a MySQL Database
Through Shell:
Backup an entire database
$mysqldump -udatabase_user -p --host="host.com" --opt -f database_name > database_backup.sql
Backup through Windows:
c:\> mysqldump -udatabase_user -p --host="host.com" --skip-lock-tables database_name > database_backup.sql
Backup a table
$mysqldump -udatabase_user -p --host="host.com" --opt -f database_name table_name > database_table_backup.sql
-p prompts for a password after the above command is executed
--force, -f
Continue even if an SQL error occurs during a table dump.
One use for this option is to cause mysqldump to continue executing even when it encounters a view that has become invalid because the defintion refers to a table that has been dropped. Without --force, mysqldump exits with an error message. With --force, mysqldump prints the error message, but it also writes a SQL comment containing the view definition to the dump output and continues executing.
--opt
This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.
The --opt option is enabled by default. Use --skip-opt to disable it. See the discussion at the beginning of this section for information about selectively enabling or disabling certain of the options affected by --opt.
Backup a MySQL Database
I've been using the following method as the naming convention to name DB's/tables.
DB name-> host_dbtitle
Table name-> dbtitle_tableTopic
Foreign keys are created across all tables and _not_ on the last relation only.
