#1062 - Duplicate entry indexed_field' for key 2
Mysql will return a duplicate entry error when trying to insert a duplicate value in an indexed field in the database. We could modify our sql insert state to check for primary key duplicate or index field duplicate and automatically update the first field with the updated value like this.
INSERT INTO table1 (a,b,c)
SELECT a,b,c
FROM table2
WHERE a IS NOT NULL
ON DUPLICATE KEY
UPDATE table1.a = table2.a
mysqldump - i often use these to copy tables from one server to another since i find the phpmyadmin dump to be very slow and sometimes i get a page timeout. aside from copying a database to another you can also use it to backup your database or all of your databases. a mysql dump file contains sql statements to create databases, populate tables, or populate rows. Most of the time i saved the dump file as a .sql but you could also specify to save it as a .csv or perhaps .xml etc.
When restoring it i use the command \. on the mysql command prompt, i find it much easier that way than typing it on the shell.
i keep on forgetting the syntax to these commands so i better jot them down. These are syntax from the manual and is the most common syntax used in creating a mysql database dump.
shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] --databases db_name1 [db_name2 db_name3...]
shell> mysqldump [options] --all-databases
example in backing up the mysql database:
shell> mysqldump db_name > [backup-file.sql]
dump all databases:
shell> mysqldump --all-databases > all_databases.sql
and restoring it back:
shell> mysql db_name < backup-file.sql
this is what I use:
mysqldump -u [user] -p[pass] [dbname] > [filename.sql]
add it in cron:
#!/bin/sh
date=`date -I`
mysqldump --opt --all-databases | bzip2 -c > /var/backup/backup-$date.sql.bz2
I often get this error when i'm installing mysql server and running phpmyadmin. Just in case you are also experiencing the same,
Find your php.ini file. Open your httpd.conf file and look for the PHPIniDir variable. If it is set to C:\PHP then use that ini file.
Open your php.ini file on your php folder usually at C:\PHP. Look for this line
Look for the loadable extension modules directory called extension_dir, set it to
You might want to enable mbstring and curl modules as well by uncommenting the following lines on your php.ini file.
I never knew there was a built-in random function in MySQL. What i did was get all the rows and using PHP's array_rand to get a random set of rows. When using RAND() in MySQL together with the LIMIT keywords it returns only the number of rows required for a random. It really saved so many lines of my code.
We can get a random number from the MySQL server using:
mysql> SELECT RAND();
We can also add a seed value by adding an argument to our RAND function:
mysql> SELECT RAND(34);
To retrive rows in random we use:
mysql> SELECT * FROM tbl_name ORDER BY RAND();
Now with the LIMIT keyword added:
mysql> SELECT * FROM tbl_name ORDER BY RAND() LIMIT 1000;
