Resolving Error 1918, System Error Code 126 … – Chris on MySQL

by Lanh on April 18, 2014

If you are installing MySQL ODBC Driver and encounter the following error:
Error 1918. Error installing ODBC driver MySQL ODBC 5.1 Driver,
ODBC error 13: The setup routines for the MySQL ODBC 5.1 Driver
could not be loaded due to system error code 126:
The specified module could not be found.
…myodbc5S.dll).. Verify…
Then you will need to install the Microsoft Visual C++ 2010 Redistributable Package (select the appropriate one for your OS architecture below):
64-bit version:

http://www.microsoft.com/en-us/download/confirmation.aspx?id=14632

32-bit version:

http://www.microsoft.com/en-gb/download/details.aspx?id=5555

After installing that, then re-attempt installing the MySQL ODBC connector, and things should work smoothly.
Hope this helps.

Tags: Error 1918, Error 1918. Error installing ODBC driver, Microsoft Visual C++ 2010 Redistributable Package, myodbc5S.dll, MySQL ODBC, MySQL ODBC 5.1 Driver could not be loaded due to system error code 126, System Error Code 126, The specified module could not be found

This entry was posted
on Thursday, April 17th, 2014 at 3:32 pm and is filed under MySQL, SkySQL.
You can follow any responses to this entry through the RSS 2.0 feed.

You can leave a response, or trackback from your own site.

See the rest here: Resolving Error 1918, System Error Code 126 … – Chris on MySQL

weight loss pinterest

How to Recover InnoDB MySQL Table Data from ibdata and .frm Files

by Lanh on April 18, 2014

This tutorial explains how to restore MySQL tables when all or some of the tables are lost, or when MySQL fails to load table data.
One of the reason for this to happen is when the table data is corrupted.
In this particular scenario, when you connect to the MySQL database server, you cannot see one more tables, as they are missing.Under this scenario, the MySQL log file contained the following messages:
InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
[ERROR] Plugin ‘InnoDB’ init function returned error.
[ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting
The method explained below will work only for InnoDB database.
Note: Before you do anything, take a backup of all the MySQL files and database in the current condition, and keep it somewhere safe.
To restore the table data you have make sure that data directory and its contents are intact. In my case it was fine.
drwx—— 2 mysql mysql 4096 Oct 11 2012 performance_schema
drwx—— 2 mysql mysql 4096 Dec 10 2012 ndbinfo
drwx–x–x 2 mysql mysql 4096 Dec 10 2012 mysql
-rw-rw—- 1 mysql mysql 56 Dec 19 2012 auto.cnf
drwx—— 2 mysql mysql 4096 Jul 30 2013 bugs
-rw-r—– 1 mysql mysql 50331648 Mar 18 10:35 ib_logfile0
-rw-r—– 1 mysql mysql 50331648 Apr 22 2013 ib_logfile1
-rw-r—– 1 mysql mysql 35651584 Mar 18 10:35 ibdata1
..
Ibdata1 – This file is the InnoDB system table space, which contains multiple InnoDB tables and associated indexes.
*.frm – Holds metadata information for all MySQL tables. These files are located inside the folder of the corresponding MySQL database. (for example, inside “bugs” directory)
ib_logfile* – All data changes are written into these log files. This is similar to the archive logs concepts that we find in other RDBMS databases.
Copy the Files
To restore the data from the above files, first stop the MySQL server.

# service mysqld stop
Copy the ibdata files, and the database schema folder to some other directory. We will use this to restore our Mysql database. In this case, we’ll copy it to the /tmp directory. The name of the database scheme in this example is bugs.
cp –r ibdata* ib_logfile* /tmp

cp –r schema_name/ /tmp/schema_name/
Start the MySQL server:
# service mysqld start
On a related note, for a typical MySQL database backup and restore, you should use the mysqldump command.
Restore the Data
Next, restore the table data as explained below.
In the my.cnf configuration file, set the value of the following parameter to the current size of the ib_logfile0 file. In the following example, I’ve set it to 48M, as that is the size I see for the ib_logfile0 file when I did “ls -lh ib_logfile0″
innodb_log_file_size=48M
Please note that both the ib_logfile0 and ib_logfile1 file size will be the same.
Copy the previous ibdata files to respective position, inside mysql data directory.
cp –r /tmp/ibdata* /var/lib/mysql/
Create an empty folder inside data directory with the same name as the database schema name that you are trying to restore, and copy the previous .frm files inside this folder as shown below:
cp –r /tmp/ib_logfile* /var/lib/mysql/
cp –r /tmp/schema_name/*.frm /var/lib/mysql/schema_name/
Finally, restart the MySQL server.
service mysqld restart
Now you have MySQL server running with the restored tables. Don’t forget to grant appropriate privileges for the clients to connect to the MySQL database.

If you enjoyed this article, you might also like..

See the article here: How to Recover InnoDB MySQL Table Data from ibdata and .frm Files

MySQL 5.7 and GIS, an Example | MySQL Server Blog

by Lanh on April 18, 2014

SummaryThis post will offer a very simple demonstration of how you can use MySQL 5.7 for Spatial features within your applications. In order to demonstrate this, I’ll walk through how we can determine the 10 closest Thai restaurants to a particular location. For this example, we’ll be using the apartment that I lived in when I first started working at MySQL, back in 2003.
For more details on all of the new GIS related work that we’ve done in MySQL 5.7, please read through these blog posts from the developers:
Installing MySQLLet’s first install the MySQL 5.7 labs release that contains all of the refactored GIS functions, as well as the R-tree based InnoDB Spatial Indexes. For the first time in MySQL’s history, you can now use an ACID/MVCC engine for geospatial data! It’s a bit hard for me to contain my excitement on that particular point.
Importing Open Street Map DataWe can now use a customized version of the Open Street Map (OSM) MySQL data import scripts, which will additionally generate a geometry column from the “longitude,latitude” coordinate pairs, and create a new InnoDB spatial index on it.
You can download a copy of my customized scripts here.
We can then use these scripts to load a NYC area OSM data extract, using these steps:

mysql -e “create database nyosm”
bunzip2 new-york.osm.bz2
./bulkDB.pl new-york.osm nyosm

mysql -e “create database nyosm”./bulkDB.pl new-york.osm nyosm

The resulting nodes table looks like this after the initial data load:

mysql> show create table nodesG
*************************** 1. row ***************************
Table: nodes
Create Table: CREATE TABLE `nodes` (
`id` bigint(20) DEFAULT NULL,
`geom` geometry NOT NULL,
`user` varchar(50) DEFAULT NULL,
`version` int(11) DEFAULT NULL,
`timestamp` varchar(20) DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
`changeset` int(11) DEFAULT NULL,
UNIQUE KEY `i_nodeids` (`id`),
SPATIAL KEY `i_geomidx` (`geom`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table nodesG*************************** 1. row ***************************       Table: nodesCreate Table: CREATE TABLE `nodes` (  `id` bigint(20) DEFAULT NULL,  `geom` geometry NOT NULL,  `user` varchar(50) DEFAULT NULL,  `version` int(11) DEFAULT NULL,  `timestamp` varchar(20) DEFAULT NULL,  `uid` int(11) DEFAULT NULL,  `changeset` int(11) DEFAULT NULL,  UNIQUE KEY `i_nodeids` (`id`),  SPATIAL KEY `i_geomidx` (`geom`)) ENGINE=InnoDB DEFAULT CHARSET=latin1

You can read more about playing with the resulting OSM data here.
Now we can also de-normalize the data a bit in order to avoid unnecessary and heavy subquery usage, and instead make use of the new (in MySQL 5.6) fulltext indexing feature in InnoDB. To do that, we can load all of the nodetags into a single column for each node this way:

mysql> alter table nodes add column tags text, add fulltext index(tags);
mysql> update nodes set tags=(SELECT group_concat( concat(k, “=”, v) SEPARATOR ‘,’) from nodetags where nodetags.id=nodes.id group by nodes.id);

mysql> alter table nodes add column tags text, add fulltext index(tags);mysql> update nodes set tags=(SELECT group_concat( concat(k, “=”, v) SEPARATOR ‘,’) from nodetags where nodetags.id=nodes.id group by nodes.id);

You can download a dump of the final schema that I ended up with here.
Creating Distance Calculation FunctionsNext we can create a new function to calculate the geographical distance between two points (“longitude,latitude” coordinate pairs, or nodes) using the spherical law of cosines (SLC) formula for determining the “Great-circle distance”:

mysql> CREATE FUNCTION slc (lat1 double, lon1 double, lat2 double, lon2 double)
RETURNS double
RETURN 6371 * acos(cos(radians(lat1)) * cos(radians(lat2)) * cos(radians(lon2) – radians(lon1)) + sin(radians(lat1)) * sin(radians(lat2)));

mysql> CREATE FUNCTION slc (lat1 double, lon1 double, lat2 double, lon2 double)RETURN 6371 * acos(cos(radians(lat1)) * cos(radians(lat2)) * cos(radians(lon2) – radians(lon1)) + sin(radians(lat1)) * sin(radians(lat2)));

This is necessary because with Geometry types, the ST_distance and related functions only return degrees—which isn’t really helpful for us here—as they’re dealing with points in a 2 dimensional cartesian plane (euclidian geometry) and we have to manually calculate these distances on an earthlike spheriod. When MySQL has Geography type support, then the need for haversine or SLC functions will go away and we can simply use the ST_distance and similar functions to calculate the actual distance on the surface of the earth, between two geographic points.
Creating the Bounding Rectangle for Our Spatial IndexAgain, as a starting point in our search for Thai restaurants, I’m going to be using the location of my apartment (40.716743,-73.951368) at the time that I first started working at MySQL.
For creating the envelope or bounding box around that location—so that we can take advantage of the spatial index on it—we can use the average distance between longitude and latitude degrees of 111km. It’s pretty close for latitude, but much more variant for longitude. If you want the bounding box or envelope to be more accurate, you can use abs(cos(radians(${latitude}))*111) for the longitude calculations. In our case though, we’re only using it for an envelope to push down to the spatial index (R-tree) for the Minimum Bounding Rectangle (MBR), so we could instead just increase the size of the envelope—since we’re still calculating the actual distance later (using the SLC function), which is what we care about in the end here—and keep things simple. Thus the calculation would be something like:

${origlon} = -73.951368
${origlat} = 40.716743
${lon1} = ${origlon} + (${distance_in_km}/111)
${lat1} = ${origlat} + (${distance_in_km}/111)
${lon2} = ${origlon} – (${distance_in_km}/111)
${lat2} = ${origlat} – (${distance_in_km}/111)

${lon1} = ${origlon} + ({lat1} = ${origlat} + ({lon2} = ${origlon} – ({lat2} = ${origlat} – (${distance_in_km}/111)

Or, if you want to have a more precise envelope/MBR:

${origlon} = -73.951368
${origlat} = 40.716743
${lon1} = ${origlon} + (origlat}))*111))
${lat1} = ${origlat} + (${distance_in_km}/111)
${lon2} = ${origlon} – (origlat}))*111))
${lat2} = ${origlat} – (${distance_in_km}/111)

${lon1} = ${origlon} + (origlat}))*111))${lat1} = ${origlat} + ({lon2} = ${origlon} – (origlat}))*111))${lat2} = ${origlat} – (${distance_in_km}/111)

Our Final Spatial QuerySo now let’s use that (simpler) MBR formula along with our SLC function to try and find out what the 10 closest Thai restaurants are to my old place, using the spatial index to weed out anything that’s not within an envelope that covers approximately 20 square km around it:

mysql> select id, slc( 40.716743, -73.951368, y(geom), x(geom))*1000 as distance_in_meters, tags, astext(geom) from nodes where MBRContains(envelope(linestring(point((-73.951368+(20/111)), (40.716743+(20/111))), point((-74.2921873-(20/111)), (40.8007363-(20/111))))), geom) AND match(tags) against (“+thai +restaurant” IN BOOLEAN MODE) order by distance_in_meters limit 10;
+————+——————–+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————+——————————-+
| id | distance_in_meters | tags | astext(geom) |
+————+——————–+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————+——————————-+
| 888976948 | 614.4973960877276 | addr:street=Bedford Avenue,amenity=restaurant,name=Tai Thai,addr:housenumber=206,phone=7185995556 | POINT(-73.958637 40.717174) |
| 2178443635 | 2780.87697408101 | cuisine=thai,phone=(212) 228-8858,microbrewery=no,website=http://www.onemorethai.net/,name=One More Thai,amenity=restaurant,opening_hours=12:00-22:30 | POINT(-73.983871 40.7210541) |
| 2287619730 | 3126.8703373046105 | addr:street=East 12th Street,website=www.thaiterminalnyc.com,amenity=restaurant,addr:housenumber=349,cuisine=thai,addr:postcode=10003,name=Thai Terminal | POINT(-73.9838861 40.7302864) |
| 1883739637 | 4077.4322333568093 | amenity=restaurant,wheelchair=yes,cuisine=thai,addr:postcode=10003,name=Laut,addr:street=15 E 17th Street,website=http://www.lautnyc.com | POINT(-73.991158 40.7376087) |
| 2300356463 | 4418.032727119699 | addr:housenumber=101,amenity=restaurant,website=http://lanternthai.com/,addr:postcode=11201,cuisine=thai,addr:city=Brooklyn,addr:state=NY,name=Lantern Thai Kitchen,addr:country=US,addr:street=Montague Street,phone=+1 718 237-2594 | POINT(-73.9954678 40.6952708) |
| 2506678248 | 4496.608193543068 | amenity=restaurant,cuisine=thai,name=Udom Thai | POINT(-73.9635028 40.6773645) |
| 2387026673 | 4942.961598763798 | phone=(718) 875-1369,addr:street=Court Street,amenity=restaurant,website=ghangthai.com,addr:city=Brooklyn,cuisine=thai,addr:housenumber=229,name=Ghang Thai Kitchen,addr:postcode=11201 | POINT(-73.9939167 40.6861559) |
| 2334601455 | 5046.741052543991 | addr:city=New York,addr:state=NY,drive_in=no,name=M-Thai,takeaway=yes,addr:country=US,addr:street=8th Avenue,internet_access=no,outdoor_seating=no,website=www.M-Thainyc.com,addr:housenumber=232,amenity=restaurant,internet_access:fee=no,phone=+1 212 229 9799,wheelchair=yes,addr:postcode=10011,cuisine=thai,microbrewery=no,smoking=no,wifi=no | POINT(-73.9988791 40.744378) |
| 2377128179 | 5051.746380714221 | addr:postcode=10011,cuisine=thai,addr:city=New York,addr:state=NY,name=Spice,addr:country=US,addr:street=8th Avenue,website=http://www.spicethainyc.com/spice/home/,addr:housenumber=236,amenity=restaurant | POINT(-73.9986916 40.7446356) |
| 2377128187 | 5053.678371601868 | addr:postcode=10011,cuisine=thai,addr:city=New York,addr:state=NY,name=Thai Royal Siam,addr:country=US,addr:street=8th Avenue,addr:housenumber=240,amenity=restaurant | POINT(-73.9986209 40.7447326) |
+————+——————–+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————+——————————-+
10 rows in set (0.00 sec)

mysql> select id, slc( 40.716743, -73.951368, y(geom), x(geom))*1000 as distance_in_meters, tags, astext(geom) from nodes where MBRContains(envelope(linestring(point((-73.951368+(20/111)), (40.716743+(20/111))), point((-74.2921873-(20/111)), (40.8007363-(20/111))))), geom) AND match(tags) against (“+thai +restaurant” IN BOOLEAN MODE) order by distance_in_meters limit 10;+————+——————–+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————+——————————-+| id | distance_in_meters | tags | astext(geom) |+————+——————–+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————+——————————-+|  888976948 |  614.4973960877276 | addr:street=Bedford Avenue,amenity=restaurant,name=Tai Thai,addr:housenumber=206,phone=7185995556 | POINT(-73.958637 40.717174) || 2178443635 |   2780.87697408101 | cuisine=thai,phone=(212) 228-8858,microbrewery=no,website=http://www.onemorethai.net/,name=One More Thai,amenity=restaurant,opening_hours=12:00-22:30 | POINT(-73.983871 40.7210541)  || 2287619730 | 3126.8703373046105 | addr:street=East 12th Street,website=www.thaiterminalnyc.com,amenity=restaurant,addr:housenumber=349,cuisine=thai,addr:postcode=10003,name=Thai Terminal | POINT(-73.9838861 40.7302864) || 1883739637 | 4077.4322333568093 | amenity=restaurant,wheelchair=yes,cuisine=thai,addr:postcode=10003,name=Laut,addr:street=15 E 17th Street,website=http://www.lautnyc.com | POINT(-73.991158 40.7376087)  | | 2300356463 |  4418.032727119699 | addr:housenumber=101,amenity=restaurant,website=http://lanternthai.com/,addr:postcode=11201,cuisine=thai,addr:city=Brooklyn,addr:state=NY,name=Lantern Thai Kitchen,addr:country=US,addr:street=Montague Street,phone=+1 718 237-2594 | POINT(-73.9954678 40.6952708) || 2506678248 |  4496.608193543068 | amenity=restaurant,cuisine=thai,name=Udom Thai | POINT(-73.9635028 40.6773645) || 2387026673 |  4942.961598763798 | phone=(718) 875-1369,addr:street=Court Street,amenity=restaurant,website=ghangthai.com,addr:city=Brooklyn,cuisine=thai,addr:housenumber=229,name=Ghang Thai Kitchen,addr:postcode=11201 | POINT(-73.9939167 40.6861559) || 2334601455 |  5046.741052543991 | addr:city=New York,addr:state=NY,drive_in=no,name=M-Thai,takeaway=yes,addr:country=US,addr:street=8th Avenue,internet_access=no,outdoor_seating=no,website=www.M-Thainyc.com,addr:housenumber=232,amenity=restaurant,internet_access:fee=no,phone=+1 212 229 9799,wheelchair=yes,addr:postcode=10011,cuisine=thai,microbrewery=no,smoking=no,wifi=no | POINT(-73.9988791 40.744378)  || 2377128179 |  5051.746380714221 | addr:postcode=10011,cuisine=thai,addr:city=New York,addr:state=NY,name=Spice,addr:country=US,addr:street=8th Avenue,website=http://www.spicethainyc.com/spice/home/,addr:housenumber=236,amenity=restaurant | POINT(-73.9986916 40.7446356) || 2377128187 |  5053.678371601868 | addr:postcode=10011,cuisine=thai,addr:city=New York,addr:state=NY,name=Thai Royal Siam,addr:country=US,addr:street=8th Avenue,addr:housenumber=240,amenity=restaurant | POINT(-73.9986209 40.7447326) | +————+——————–+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————+——————————-+10 rows in set (0.00 sec)

OK, we got some values, and thanks to a combination of the spatial index and the full text index, we got them very quickly!
Examining the ResultsOK, now on to the data. Let’s assume that we simply want the closest one, and examine that record:

| 888976948 | 614.4973960877276 | addr:street=Bedford Avenue,amenity=restaurant,name=Tai Thai,addr:housenumber=206,phone=7185995556 | POINT(-73.958637 40.717174)

| 888976948 |  614.4973960877276 | addr:street=Bedford Avenue,amenity=restaurant,name=Tai Thai,addr:housenumber=206,phone=7185995556 | POINT(-73.958637 40.717174)

So it’s about 600 meters from my old place, its name is Tai Thai, and its phone number is 718-599-5556. Let’s do a quick sanity check using google maps: directions, and location info. As we can see, our data matches up pretty well with Google maps (which I consider to be the gold standard today).
Now I know that there are many other Thai restaurants in the area, but they simply don’t have any records in OSM currently (at least in the extracted snapshot for the NYC area that I’m working with). As always, your data is key.
Presenting the DataNow we know what our data is telling us are the closest 10 Thai restaurants. We also have relevent information to present to the application user about each place: name, address, phone number, website, etc. So let’s look at a very simple example of how we can push these new locations out to google maps so that we can see each one on a map of the city, to go along with all of the metadata that we have for them. The simplest way is to feed the “latitude, longitude” coordinate pairs into google maps. Let’s try generating a URL to map my old place to the next closest Thai place. Again, the “latitude, longitude” coordinates for my old apartment are “40.716743, -73.951368″ and the coordinates for the next closest Thai place, One More Thai, are “40.7210541, -73.983871″. We can plug these into a simple GET call like this: https://www.google.com/maps/dir/40.716743,+-73.951368/40.7210541,+-73.983871. Or we can use the embedded API like this:

As you can see it’s a pretty easy GET call either way, passing in the coordinate pairs in the given format. You can read more about the google maps APIs here.
ConclusionHopefully this has opened your eyes a bit to what’s possible with MySQL 5.7 in the GIS world, and whet your appetite for jumping in and playing with it!
Lastly, a big thank you to all of the developers who worked so hard on these new features! Please stay tuned in here for additional features that truly make MySQL a leading player in the geospatial market.

Visit link: MySQL 5.7 and GIS, an Example | MySQL Server Blog

car insurance app

Remove definers from MySQL DUMP in Linux | learnopensource

by Lanh on April 17, 2014

Dump file created using mysqldump in Linux, includes a clause named “DEFINER”. As of now there is no way to exclude this clause in mysqldump. So whenever you'll restore this database to another database, it'll through a …

Read the rest here: Remove definers from MySQL DUMP in Linux | learnopensource

Duplicate a MySQL Database – Rubyrobot

by Lanh on April 17, 2014

This shell command will duplicate an existing database:

# mysqladmin create DB_name -u DB_user –password=DB_pass &&
mysqldump -u DB_user –password=DB_pass DB_name | mysql -u DB_user –password=DB_pass -h DB_host DB_name

Replace DB_* with your MySQL settings. Where could this be useful besides making a mirror backup of a database?

I am thinking of a Rails app running on a production server. You would like to test major code upgrades or database migrations on the production hardware before you mess with your business data, but don’t have money to clone your production hardware.

This approach would simply duplicate the production database and deploy the app on the same server, running on a high port number. Needs a Capistrano recipe to be done!

See the original post here: Duplicate a MySQL Database – Rubyrobot

las mer

Zemian Blog: A Programmer's Journal: How to enable MySQL query …

by Lanh on April 17, 2014

Do you need to monitor any SQL statements passing through your MySQL server? You would need to turn on the server logging. Edit your my.cnf file and add these:log-output=FILEgeneral-log=1general_log_file=mysql-general.logYou also need to restart the database server after these changes.

See original here: Zemian Blog: A Programmer's Journal: How to enable MySQL query …

compare car insurance quotes mobile

Geographically distributed multi-master MySQL clusters – Continuent …

by Lanh on April 17, 2014

In this webinar, we discuss the multi-master capabilities of Continuent Tungsten to help you build and manage systems that spread data across multiple sites. We cover important topics such as setting up large scale topologies, handling failures, and how to handle data privacy issues like removing personally identifiable information or handling privacy law restrictions on data movement. We will conclude with a live demonstration of a distributed MySQL solution with Continuent Tungsten clusters working across multiple Amazon Web Services (AWS) availability zones and regions. 
 Join us on Thursday, April 24th at 10 am PDT/1 pm EDT!

Read the original post: Geographically distributed multi-master MySQL clusters – Continuent …

Installing Apache2 With PHP5 And MySQL Support On Fedora 20 …

by Lanh on April 17, 2014

Installing Apache2 With PHP5 And MySQL Support On Fedora 20 (LAMP). Version 1.0. Author: Falko Timme, updated by Srijan Kishore Last edited 14/Apr/2014. LAMP is short for Linux, Apache, MySQL, PHP. This tutorial …

Originally posted here: Installing Apache2 With PHP5 And MySQL Support On Fedora 20 …

malacandra redeemed: MySQL Disable Foreign Key Checks

by Lanh on April 16, 2014

What and Why
Here are some test results regarding foreign key checks in mysql, and what actually happens to the data, when you change the relation type, eg CASCADE, SET NULL, RESTRICT, and NO ACTION.I basically want to check whether foreign keys are processed on the table after the key checks are reenabled, or they are ignored completely, or if there is some other vooodoo that happens.
Method
I setup 2 Tables, T1 and T2, T1 has a primary key (id) and T2 has a foreign key (ref) which points to T1.id.For the test I just did updates to the id in T1, and deleted a row in T1, and watched what happened to the data in T2, for each of the FK relations types.The code used to disable the foreign key checks is:SET foreign_key_checks = 0;– UPDATE `test1` set id=13 where id = 3;DELETE FROM `test1` WHERE id = 3;SET foreign_key_checks = 1;I commented out the delete or update line as appropriate.and then reset the tables after each test.
Results
Normal Operation================CASCADE on update = both records updatedCASCADE on delete = both records deletedSET NULL on delete = row deleted and ref set to nullSET NULL on update = row updated and ref set to nullRESTRICT on delete = delete preventedRESTRICT on update = update preventedNO ACTION on update = update preventedNO ACTION on delete = delete preventedDisable FK Checks=================CASCADE on update = row updated and leaves dangling refCASCADE on delete = row deleted and leaves dangling refSET NULL on update = row updated and leaves dangling refSET NULL on delete = row deleted and leaves dangling refRESTRICT on update = row updated and leaves dangling refRESTRICT on delete = row deleted and leaves dangling refNO ACTION on update = row updated and leaves dangling refNO ACTION on delete = row deleted and leaves dangling ref

Conclusion

The most obvious conclusion, is that disabling foreign key checks as a  part of the update or delete process can leave invalid foreign keys in the database.  While there might be good reasons for doing this, the chance that the data will be left in an inconsistent state is very high. 

Read more here: malacandra redeemed: MySQL Disable Foreign Key Checks

MySQL Enterprise Backup 3.10: Teasing compression. | MySQL-Med

by Lanh on April 15, 2014

Ok, so I wanted to look into the new compression options of MEB 3.10.
And I would like to share my tests with you. Remember, they’re just this, tests, so please feel free to copy n paste and obtain your own results and conclusions, and should I say it, baselines, in order to compare future behaviour, on your own system.
An Oracle Linux 6.3 virtual machine with 3Gb RAM, 2 virtual threads, on a 1x quad core, windows laptop. Not pretty, but hey.
So, these tests are solely about backup. I’ll do restore when I get some *more* time.

First up, lets compare like with like, i.e. MEB version 3.9 & 3.10:
Let’s make this interesting, hence, want to use as much resources available as possible, read, write, process threads and number of buffers.
mysqlbackup –user=root –password=oracle –socket=/tmp/mysql5614.sock
–backup-dir=/home/mysql/MEB/test –with-timestamp –read-threads=4 –write-threads=4
–process-threads=4 –limit-memory=300 backup
empty DB         1.2Gb        2.2Gb     2.6Gb
v3.9          0:14 min            2:37          4:26        6:04
v3.10        0:07                  2:34          4:03        5:52
So, with a bit more adjustments to use as much as possible, without compression, here’s testing 3.10:
mysqlbackup –user=root –password=oracle –socket=/tmp/mysql5614.sock
–backup-dir=/home/mysql/MEB/test –with-timestamp –read-threads=2 –write-threads=2
–process-threads=8 –limit-memory=1280 –number-of-buffers=80 backup
This takes 5:20 mins. 2685708 Kb backup size.
Now onto compression.
And I want to double check, let’s check how compression is in v3.9:
mysqlbackup –user=root –password=oracle –socket=/tmp/mysql5614.sock
–backup-dir=/home/mysql/MEB/test –backup-image=full_$BKUP_DATE.img –with-timestamp
–read-threads=2 –write-threads=2 –process-threads=8 –limit-memory=1280 –number-of-buffers=80
–compress backup-to-image
6:31 min    867264 Kb (32% of original size, 68% compression)

Time for v3.10. Same options, just using the mysqlbackup binary in another location, /usr/local/meb310/bin/mysqlbackup:
4:28    Lz4 default compression. 1271720 Kb (47% of original size, 53% compression)
Ok, now let’s use 3.10 properly:
mysqlbackup –user=root –password=oracle –socket=/tmp/mysql5614.sock
–backup-dir=/home/mysql/MEB/test –backup-image=full_$BKUP_DATE.img –with-timestamp
–read-threads=2 –write-threads=2 –process-threads=8 –limit-memory=1280 –number-of-buffers=80
–compress –compress-level=1 –compress-method=lzma backup-to-image
3:43    469916 Kb (82.6% compression).
changing it slightly, with:
–compress-level=9 –compress-method=lzma
4:35    2512022 Kb (compression 6.33%)
Ok, so now I feel I have something useful, compress-level=1 and lzma for my environment is the best option, with it taking ~83% less disk, and at 1:37 min faster. This obviously will depend on the memory (number-of-buffers) available, and of course the processing power and core’s available. Not to mention that I expect lots of you to have much better IO rates than me, so, now it’s up to you to tease MEB compression…
Try it.. just for me.
Like this:Like Loading…

Related

Read the original here: MySQL Enterprise Backup 3.10: Teasing compression. | MySQL-Med