MySQL


Importing wordpress database error “Invalid default value for .. “

While moving my wordpress blog to the new Google Cloud Hosting setup, the database would not import. This is due to MySQL being setup in strict mode by default in MySQL version 5.7 . One solution on the internet is to set MySQL’s SQL_MODE to “NO_ENGINE_SUBSTITUTION”, but Google knows better and won’t let you do that for security purposes. They do provide an option https://cloud.google.com/sql/docs/mysql/flags to alter the settings and allow for invalid dates. In the drop down as pictured, select “Allow Invalid Dates”. Then your wordpress database import will work.


HeidiSQL SSH Tunnel Connection

The SSH tunnel forwards requests from a port on your machine (3307 in this case since 3306 is already taken by the local mysql server) to a remote server via SSH.

The below are instructions for populating the fields on the settings and SSH tunnel tab.

In the ‘Settings’ tab

1. In the dropdown list of ‘Network type’, please select SSH tunnel

2. Hostname/IP: localhost (even you are connecting remotely)

3. Username & Password: your mysql user and password

Next, in the tab SSH Tunnel:

1. specify plink.exe or you need to download it and specify where it’s located

2. Host + port: the remote IP of your SSH server(should be MySQL server as well), port 22 if you don’t change anything

3. Username & password: SSH username (not MySQL users)

sshtunnel


Using Maxmind’s geo city lite database on your website 2

I’ve been using the maxmind geo city lite data for a couple years now and wanted to share what I’ve learned.

Setting up the data

Firstly, there is a great article here about constructing your table and importing the data Geo IP location system in MySQL . My import for windows and my table was slightly different –

LOAD DATA LOCAL INFILE 'C:\\GeoLiteCity-Blocks.csv'
INTO TABLE geo_city_ip_block_new CHARACTER SET utf8 FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' IGNORE 2 LINES (start_ip, end_ip, location_id);
LOAD DATA LOCAL INFILE 'C:\\GeoLiteCity-Location.csv'
INTO TABLE geo_city_ip_location_new CHARACTER SET utf8 FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' IGNORE 2 LINES (location_id, country, region, city, zip, latitude, longitude, metro_code);

Be sure to load this data via the command line b/c it’s WAY faster… like 4 seconds as compared to ten minutes or more through phpmyadmin or some other client software. As of this writing, the blocks data is 2.256 million records.

Querying the data

My query initially looked just like the query Mr. Dobrzanski says not to do (using a between clause) and it took 0.2 seconds to complete. My query actually ran faster without any indexes on the columns, with the exception of having one on the location_id. After changing to his query using the less than, sorting, and limit, and also adding a primary key on the start and end ip address in the block table, it completed in 0.0004 seconds!

SELECT * 
FROM web_data.geo_city_ip_block b 
INNER JOIN web_data.geo_city_ip_location l USING(location_id)
WHERE b.start_ip <= INET_ATON('your ip')
ORDER BY b.start_ip DESC
LIMIT 1

More about Maxmind’s data

Maxmind updates their data frequently; they publish a new data set every month. For each month out of date your data is, it loses 1.5% accuracy. I think the last time I updated my data was like two years ago :). The location data, which is currently 403,000 records, contains mostly historical locations and is no longer of any use if you’re searching for locations by latitude and longitude as with the below query

SELECT *, ( 3959 * acos( cos( radians(35.1495343) ) * cos( radians( latitude ) ) 
* cos( radians( longitude ) - radians(-90.0489801) ) + sin( radians(35.1495343) ) 
* sin( radians( latitude ) ) ) ) AS distance
FROM web_data.geo_city_ip_location 
HAVING distance < 50 ORDER BY distance LIMIT 20

Take zip code 30301 for example. The locations data says it’s in both Atlanta GA and Jackson TN. This is not possible. The most up to date and authoritative data source for current zip codes is USPS’s zip code lookup tool. To clean out the old data that is of no use, run the below sql. Having less than half the data to look through will also make the above query complete over twice as fast.

DELETE FROM geo_city_ip_location
WHERE NOT EXISTS (
	SELECT *
	FROM geo_city_ip_block
	WHERE location_id = geo_city_ip_location.location_id
)

After purging the historical data you’ll see the location for zip code 30301 Jackson TN is gone and the location table now contains only 118,000 records.


Enable slow query log for cpanel whm mysql 5.5 2

*** Note the comments below, this is for MySQL 5.5. MySQL 5.6 uses slow_query_log ***

To enable the slow query log for your whm / cpanel server, do the following:

open /etc/my.cnf with your favorite editor and add the following lines under the [mysqld] section:

log-slow-queries=/var/lib/mysql/slow_queries.log
long_query_time = 3
log_queries_not_using_indexes

The log_queries_not_using_indexes option will also write any queries to the file that are not using indexes. They may take less than your long_query_time threshold, but is good for finding easy targets for indexes and performance gains.

Then create the log file and set the proper permissions:

touch /var/lib/mysql/slow_queries.log
chmod 660 /var/lib/mysql/slow_queries.log
chown mysql:mysql /var/lib/mysql/slow_queries.log

Then restart mysql:

service mysql restart

If you want to view the contents of the file in real time, go to the mysql directory – /var/lib/mysql/ and then run:
tail -f slow_queries.log

Monitor the queries being written to the file and make adjustments as necessary.


MySQL – indexes on unique data can slow down your query

Today I was running a query that joined two tables with one table having 1.9 million records and the other 340K. I had put an index on two columns that contained unique data and the query took 1.37 seconds to complete. By removing these indexes, the completion time was reduced to 0.25 seconds. The indexes did nothing for performance since the all the data was already unique. What surprised me was how much slower it made the query though.

SELECT * FROM geo_city_ip_block b INNER JOIN geo_city_ip_location l USING(location_id) 
WHERE '1142362046' BETWEEN b.start_ip AND b.end_ip
/* without indexes: 0.25 */
/* with indexes: 1.37 */

It’s worth noting that I was able to further improve the performance of this query by not using MySQL’s “between” clause.

SELECT * FROM geo_city_ip_block b INNER JOIN geo_city_ip_location l USING(location_id) 
WHERE b.start_ip < '1142362046' AND b.end_ip > '1142362046';
/* without indexes: 0.16 */