Import large database in remote server
If you owned slow internet connection, it would be headache if you have large database to be imported in a remote server. Either you import the database through MySQL GUI or phpMyAdmin, the execution time is mostly will exceed the maximum execution time allowed, resulting in an script timeout problem. It would be same if you manually paste the queries and execute it in remote server. The server simply doesn't have enough time to capture your bulk records of "INSERT".
So is there another alternative?
Actually, it is. You can upload your sql file to your remote server, and then use the PHP script to parse the sql file and then run the query line by line.
Fortunately, there is an open source code which ease our job to write our own script. You may visit "BigDump", and download its source code, bigdump.php, which is zipped as archive sized at only 10KB zip archive.
Before using BigDump, you need to upload your sql file into your remote server. Be sure not to upload your file to somewhere people may access it, such as your /public_html folder. Normally, you would upload the file at the parent folder of /public_html, such as, /home/<username>/
Bigdump also allow import the records from CSV file. You may refer the following configuration source code, foundat the beginning of bigdump.php.
// Database configuration $db_server = 'localhost'; $db_name = ''; $db_username = ''; $db_password = ''; // Other settings (optional) $filename = ''; // Specify the dump filename to suppress the file selection dialog $csv_insert_table = ''; // Destination table for CSV files $csv_preempty_table = false; // true: delete all entries from table specified in $csv_insert_table before processing $ajax = true; // AJAX mode: import will be done without refreshing the website $linespersession = 3000; // Lines to be executed per one import session $delaypersession = 0; // You can specify a sleep time in milliseconds after each session // Works only if JavaScript is activated. Use to reduce server overrun // Allowed comment delimiters: lines starting with these strings will be dropped by BigDump $comment[]='#'; // Standard comment lines are dropped by default $comment[]='-- '; // $comment[]='---'; // Uncomment this line if using proprietary dump created by outdated mysqldump // $comment[]='CREATE DATABASE'; // Uncomment this line if your dump contains create database queries in order to ignore them $comment[]='/*!'; // Or add your own string to leave out other proprietary things // Connection character set should be the same as the dump file character set (utf8, latin1, cp1251, koi8r etc.) // See http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html for the full list $db_connection_charset = '';
LikeINSERT/UPDATE using existing table/row Value
There is a table 'user' with score. Simple enough.
| id | username | score |
|---|---|---|
| 10 | neobie | 530000 |
INSERT STATEMENT
In my application I need to add another user with username 'neobie_2' and score of 1000 less than user 'neobie'.
I would use
INSERT INTO user (username, score) VALUES ('neobie_2', (SELECT score-1000 FROM user WHERE username = 'neobie'));
MySQL will return an error message "You can't specify target table 'user' for update in FROM clause.". It seems like I cannot use table user twice. Let's give an alias and change the statement to:
INSERT INTO user (username, score) VALUES ('neobie_2', (SELECT score-1000 FROM user WHERE username = 'neobie') AS x);
And this would succeed my INSERT query.
UPDATE STATEMENT
Another circumstance, I need to add more points to the score of the user as a reward for his contribution.
I know I have to use ALIAS in order to update base on the existing value.
UPDATE user SET score = (SELECT score+10000 from10 AS x) WHERE id = 10;
This does not work either? It simply return syntax error.
Let's try to nested the query once more.
UPDATE user SET score =
(SELECT score + 10000 FROM
(SELECT score FROM user WHERE id = 10)
as x)
WHERE id = 10;
The query above is equivalent to
UPDATE user SET score = score + 10000 WHERE id = 10;
However, if you going to update the record base on the different record value in the same table, the nested query would be come in handy. For example, updating the record with id 10, using the value from id 9.
Take note that always use open and close bracket for any nested query.
If you have better method or suggestion, feel free to share with us.
MySQL Default SQL-Mode
If developers' testing server platform configuration is different with the real server testing configuration, it could be troublesome for both developers and users, only to realize that the real system is buggy and certain process could not be completed. In worst case, undesired wrong data is inserted, resulting in unwanted aftermath.
Here we talk about MySQL problems.
By using XAMPP because of the ease of installation, the default configuration of the its MySQL without STRICT MODE is quite dangerous. For example, a wrong date format of 2009-Feb-01 instead of 2009-2-01 inserted into DATE column resulting in the value of 0000-00-00, converted by "loose" mode of mysql. The real date to be captured is definitely unrecoverable if strict mode is not enabled. By having strict mode, mysql rejects the query with an error message "invalid value for column date" or something similar. Without strict mode, a lot of problems could arise if the programming code is not perfect enough.
To check MySQL SQL Mode, use the query
SELECT @@global.sql_mode;
Running query on xampp MySQL simply return nothing. For the official version of MySQL, you should be able to read this line:
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
How do I configure MySQL to be in STRICT mode?
For xampp user, there is no my.ini as possessed by official MySQL. Look for my.cnf instead, as located in <xampp-directory>/mysql/bin/my.cnf
Open my.cnf using any text editor. Put the following line anywhere under [mysqld] section or else it won't work.
# Set the SQL mode to strict
sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Save my.cnf, exit and remember to restart MySQL service.