Category Archives: MySQL

Export MS Access Database To MySQL Database

Here are few tips (with undocumented features) to help you export huge MS Access database (production database with real data) to MySQL.

Use mdbtools utilities as described below:

mdb-tables displays the list of tables in MS Access

mdb-scheme export the MS Access schema to MySQL database. The documentation doesn’t mention that MySQL is supported. You should use the following command:

mdb-schema [-S] database mysql

You may need to replace column names if they collide with MySQL reserved keywords. few column names like GROUP, PRIMARY & CROSS.

The schema created contains drop tables. You can use grep -v to exclude them. Additionally you should add drop database followed by create database commands at the very top of the file when you are importing to a new database. This is essential because you may have to go through multiple passes. Having drop table when a table is not there creates problem as also not having them creates problem when the table has already been loaded. So I remove the drop tables and instead drop and re-create the database for each run by the process described above.

mdb-export exports MS Access tables to CSV format. What is not emphasized is that you can directly create INSERT statements instead of a CSV file. Converting it directly to SQL INSERT statements is very helpful, especially for converting data types. Use it like shown in the sample:

mdb-export -I -R’;\n’ MSAccessDatabase.mdb Table > Table.sql

Note: Replace MSAccessDatabase.mdb with the full path of your MS Access Database, Table is Table name. You can save the output in any file, I just chose Tablename.sql for convenience.

The -R’;\n’ ensures that each SQL INSERT is followed by a semicolon and then a new line.

You may need to modify this to substitute column names, if you have previously changed them while importing the schema to prevent conflict with reserved MySQL keywords.

Advertisements

Oracle will boost MySQL, release Cloud Office suite

Oracle Corp. promised to aggressively push its newly acquired MySQL open-source database, rather than kill it.

Oracle also plans to continued to invest in and maintain the independence of OpenOffice.org, the longtime Microsoft Office challenger from Sun Microsystems Inc., but it will also launch a separate cloud productivity suite that’s similar to Google Docs.

Many users feared that Oracle would bury MySQL, a lightweight database that’s gaining acceptance in corporate enterprises after starting out as a favorite of Web start-ups, to avoid cannibalizing its flagship Oracle Database.

But Oracle plans to keep MySQL’s sales team independent while improving MySQL’s code, support and compatibility with other Oracle apps. Oracle will oversee MySQL, OpenOffice.org and other open-source apps in Oracle’s Open-Source Software division.

During a webcast briefing, Oracle executives said the company’s acquisition of Sun’s many top-notch technologies will allow the combined company to offer “complete, open, integrated systems.” That vow appears to be a challenge to IBM, which prior to the Oracle-Sun combination, was considered the largest proponent of enterprise use of open-source technologies.

Here’s a look at some of the highlights of today’s presentation:

Java: Oracle plans to “extend and enhance the reach of Java,” according to Thomas Kurian, an executive vice president. This will be achieved by integrating and simplifying the Java platform runtime — specifically, delivering Version 7 of the Java Standard Edition client for desktop PCs with a variety of improvements, while making the mobile version, Java ME, compatible with the desktop version to lessen work for programmers.

Oracle also vowed to make it easier for Web developers using JavaScript to work with Java. These moves, he said, will all help revitalize the Java developer community, numbers 10 million.

Finally, The JavaOne show will remain independent, but it will now take place during the Oracle OpenWorld conference, which is scheduled to be held in San Francisco in September.

OpenOffice.org: OpenOffice.org will be managed as an independent business unit, with Sun’s development and support teams retained. Oracle will continue to support the free community edition of OpenOffice.org. However, Oracle also plans to deliver a cloud offering called Oracle Cloud Office, which had been under development for a while. Oracle did not comment on the fate of StarOffice, the paid, supported version of OpenOffice.org that competes with IBM’s own OpenOffice.org-based Lotus Symphony.

Solaris: Sun open-sourced its longtime server operating system in 2005. A former Sun executive who now serves as executive vice president of hardware engineering at Oracle, said the company plans to increase investment in Solaris so that it, among other things, will be able to scale to run thousands of CPU threads simultaneously and handle multiple terabytes of memory.

Linux: Oracle has thousands of customers for Unbreakable Linux, its supported version of Red Hat Linux, Screven said. Oracle will now invest in both Linux and Solaris.

What Do Oracle’s MySQL Promises Amount To?

Oracle has NOT promised (certainly not in a legally binding manner):

– To keep (all of) MySQL under an open source license.
– Not to add closed source parts, modules or required tools.
– To keep the code for MySQL enterprise edition and MySQL community edition the same.
– To not raise MySQL license or MySQL support prices.
– To release new MySQL versions in a regular and timely manner.
– To continue with dual licensing and always provide affordable commercial licenses to MySQL to those who needs them (to storage vendors and application vendors) or provide MySQL under a more permissive license
– To develop MySQL as an Open Source project
– To actively work with the community
– Apply submitted patches in a timely manner
– To not discriminate patches that make MySQL compete more with Oracles other products
– To ensure that MySQL is improved also in manners that make it compete even more with Oracles’ main offering.

In short, Oracle hasn’t promised to do much of anything that truly matters.

Oracle made a statement about MySQL’s future — but which addressed only one of the concerns on the above list, the one marked with an asterisk. Nothing — or at least, nothing of substance — has been said about MySQL’s future as an open source project.

It’s not surprising at all, given that all of Oracle’s experience with open source would come through their acquisition of Sun and not through anything they’ve done directly. Plus, almost nothing Oracle has said so far indicates they’re willing to let Sun’s people do the heavy lifting where MySQL is concerned.

At the very least, all that hammering on Oracle’s facade is producing a few cracks. It’s a sign that they can be persuaded to do the smart thing, and not tick off a great many people who could be potential customers.

MySQL Magic: Find and Replace Data

SQL provides a simple way to find and replace data with its wonderful UPDATE function.

General Example

Using the SQL UPDATE command is straightforward. Here is the general syntax:

UPDATE table_name SET field_name = replace( field_name, ‘string_to_find’, ‘string_to_replace’ ) ;

Simply replace the table_name and both instances of field_name with your specific information, and then edit string_to_find, and string_to_replace with the desired values. This is pretty standard stuff, but it is always a good idea to backup your database before executing commands. To run a “Find and Replace” via phpMyAdmin, simply login, select your database, and enter the command via the SQL tab. Copy, paste, and go!

SQL JOINS

The SQL JOIN clause is used whenever we have to select data from 2 or more tables.

To be able to use SQL JOIN clause to extract data from 2 (or more) tables, we need a relationship between certain columns in these tables.

We are going to illustrate our SQL JOIN example with the following 2 tables:

Customers:

CustomerID FirstName LastName Email DOB Phone
1 John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222
2 Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545
3 Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
4 James Smith jim@supergig.co.uk 20/10/1980 416 323-8888

Sales:

CustomerID Date SaleAmount
2 5/6/2004 $100.22
1 5/7/2004 $99.95
3 5/7/2004 $122.95
3 5/13/2004 $100.00
4 5/22/2004 $555.55

As you can see those 2 tables have common field called CustomerID and thanks to that we can extract information from both tables by matching their CustomerID columns.

Consider the following SQL statement:
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers, Sales
WHERE Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName

The SQL expression above will select all distinct customers (their first and last names) and the total respective amount of dollars they have spent.
The SQL JOIN condition has been specified after the SQL WHERE clause and says that the 2 tables have to be matched by their respective CustomerID columns.

Here is the result of this SQL statement:

FirstName LastName SalesPerCustomers
John Smith $99.95
Steven Goldfish $100.22
Paula Brown $222.95
James Smith $555.55

The SQL statement above can be re-written using the SQL JOIN clause like this:
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName

There are 2 types of SQL JOINS – INNER JOINS and OUTER JOINS. If you don’t put INNER or OUTER keywords in front of the SQL JOIN keyword, then INNER JOIN is used. In short “INNER JOIN” = “JOIN” (note that different databases have different syntax for their JOIN clauses).

The INNER JOIN will select all rows from both tables as long as there is a match between the columns we are matching on. In case we have a customer in the Customers table, which still hasn’t made any orders (there are no entries for this customer in the Sales table), this customer will not be listed in the result of our SQL query above.

If the Sales table has the following rows:

CustomerID Date SaleAmount
2 5/6/2004 $100.22
1 5/6/2004 $99.95

And we use the same SQL JOIN statement from above:
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName

We’ll get the following result:

FirstName LastName SalesPerCustomers
John Smith $99.95
Steven Goldfish $100.22

Even though Paula and James are listed as customers in the Customers table they won’t be displayed because they haven’t purchased anything yet.

But what if you want to display all the customers and their sales, no matter if they have ordered something or not? We’ll do that with the help of SQL OUTER JOIN clause.

The second type of SQL JOIN is called SQL OUTER JOIN and it has 2 sub-types called LEFT OUTER JOIN and RIGHT OUTER JOIN.

The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.

If we slightly modify our last SQL statement to:
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers LEFT JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName

and the Sales table still has the following rows:

CustomerID Date SaleAmount
2 5/6/2004 $100.22
1 5/6/2004 $99.95

The result will be the following:

FirstName LastName SalesPerCustomers
John Smith $99.95
Steven Goldfish $100.22
Paula Brown NULL
James Smith NULL

As you can see we have selected everything from the Customers (first table). For all rows from Customers, which don’t have a match in the Sales (second table), the SalesPerCustomer column has amount NULL (NULL means a column contains nothing).

The RIGHT OUTER JOIN or just RIGHT JOIN behaves exactly as SQL LEFT JOIN, except that it returns all rows from the second table (the right table in our SQL JOIN statement).

interesting?..http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/:

What’s New in MySQL 5.1?

Partitioning, Event scheduler are two big features that are introduced in this version.
and a major of sql optimization has been done.
MySQL 5.1 performs much better in all tests: it can handle more transactions per second and it does not deadlock with 256 threads, unlike 5.0.

The following features have been added to MySQL 5.1.

Partitioning. This capability enables distributing portions of individual tables across a file system, according to rules which can be set when the table is created. In effect, different portions of a table are stored as separate tables in different locations, but from the user point of view, the partitioned table is still a single table. Syntactically, this implements a number of new extensions to the CREATE TABLE, ALTER TABLE, and EXPLAIN … SELECT statements. As of MySQL 5.1.6, queries against partitioned tables can take advantage of partition pruning. In some cases, this can result in query execution that is an order of magnitude faster than the same query against a non-partitioned version of the same table. See Chapter 18, Partitioning, for further information on this functionality. (Author: Mikael Ronström)

Row-based replication. Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based replication. As of MySQL 5.1.5, another basis for replication is available. This is called row-based replication. Instead of sending SQL statements to the slave, the master writes events to its binary log that indicate how individual table rows are effected. As of MySQL 5.1.8, a third option is available: mixed. This will use statement-based replication by default, and only switch to row-based replication in particular cases. See Section 16.1.2, “Replication Formats”. (Authors: Lars Thalmann, Guilhem Bichot, Mats Kindahl)

Plugin API. MySQL 5.1 adds support for a very flexible plugin API that enables loading and unloading of various components at runtime, without restarting the server. Although the work on this is not finished yet, plugin full-text parsers are a first step in this direction. This allows users to implement their own input filter on the indexed text, enabling full-text search capability on arbitrary data such as PDF files or other document formats. A pre-parser full-text plugin performs the actual parsing and extraction of the text and hands it over to the built-in MySQL full-text search. See Section 22.2, “The MySQL Plugin Interface”. (Author: Sergey Vojtovich)

Event scheduler. MySQL Events are tasks that run according to a schedule. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a “cron job”) or the Windows Task Scheduler. See Section 19.4, “Using the Event Scheduler”. (Author: Andrey Hristov)

Server log tables. Before MySQL 5.1, the server writes general query log and slow query log entries to log files. As of MySQL 5.1, the server’s logging capabilities for these logs are more flexible. Log entries can be written to log files (as before) or to the general_log and slow_log tables in the mysql database. If logging is enabled, either or both destinations can be selected. The –log-output option controls the destination or destinations of log output. See Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”. (Author: Petr Chardin)

Upgrade program. The mysql_upgrade program (available as of MySQL 5.1.7) checks all existing tables for incompatibilities with the current version of MySQL Server and repairs them if necessary. This program should be run for each MySQL upgrade. See Section 4.4.8, “mysql_upgrade — Check Tables for MySQL Upgrade”. (Authors: Alexey Botchkov, Mikael Widenius)

MySQL Cluster. MySQL Cluster is now released as a separate product, based on MySQL 5.1 but with the addition of the NDBCLUSTER storage engine. Clustering support is no longer available in mainline MySQL 5.1 releases. MySQL Cluster releases are identified by a 3-part NDB version number; currently, the MySQL Cluster NDB 6.2 and MySQL Cluster NDB 6.3 release series are available for production use.

Some of the changes in MySQL Cluster since MySQL 5.0 are listed here:

MySQL Cluster replication. Replication between MySQL Clusters is now supported. It is now also possible to replicate between a MySQL Cluster and a non-cluster database. See Section 17.10, “MySQL Cluster Replication”.

MySQL Cluster disk data storage. Formerly, the NDBCLUSTER storage engine was strictly in-memory; now, it is possible to store Cluster data (but not indexes) on disk. This allows MySQL Cluster to scale upward with fewer hardware (RAM) requirements than previously. In addition, the Disk Data implementation includes a new “no-steal” restoration algorithm for fast node restarts when storing very large amounts of data (terabyte range). See Section 17.11, “MySQL Cluster Disk Data Tables”, for more information.

Improved backups for MySQL Cluster. A fault arising in a single data node during a Cluster backup no longer causes the entire backup to be aborted, as occurred in previous versions of MySQL Cluster.

Many other new features and improvements have been made to the NDBCLUSTER storage engine in MySQL Cluster NDB 6.2 and MySQL Cluster NDB 6.3; for more information about these, see Section 17.14, “MySQL Cluster Development Roadmap”.

Backup of tablespaces. The mysqldump utility now supports an option for dumping tablespaces. Use -Y or –all-tablespaces to enable this functionality.

Improvements to INFORMATION_SCHEMA. MySQL 5.1 provides much more information in its metadata database than was available in MySQL 5.0. New tables in the INFORMATION_SCHEMA database include FILES, EVENTS, PARTITIONS, PROCESSLIST, ENGINES, and PLUGINS.

XML functions with XPath support. ExtractValue() returns the content of a fragment of XML matching a given XPath expression. UpdateXML() replaces the element selected from a fragment of XML by an XPath expression supplied by the user with a second XML fragment (also user-supplied), and returns the modified XML. See Section 11.10, “XML Functions”. (Author: Alexander Barkov)

Load emulator. The mysqlslap program is designed to emulate client load for a MySQL server and report the timing of each stage. It works as if multiple clients were accessing the server. See Section 4.5.7, “mysqlslap — Load Emulation Client”. (Authors: Patrick Galbraith, Brian Aker)

How Big MySQL Tables Can Be

MySQL 3.22 had a 4GB (4 gigabyte) limit on table size. With the MyISAM storage engine in MySQL 3.23, the maximum table size was increased to 8 million terabytes (2 ^ 63 bytes). With this larger allowed table size, the maximum effective table size for MySQL databases now usually is determined by operating system constraints on file sizes, not by MySQL internal limits.

The InnoDB storage engine maintains InnoDB tables within a tablespace that can be created from several files. This allows a table to exceed the maximum individual file size. The tablespace can include raw disk partitions, which allows extremely large tables. The maximum tablespace size is 64TB.

The following table lists some examples of operating system file-size limits. This is only a rough guide and is not intended to be definitive. For the most up-to-date information, be sure to check the documentation specific to your operating system.

Linux 2.2-Intel 32-bit – 2GB (LFS: 4GB)
Linux 2.4 (using ext3 filesystem) – 4TB
Solaris 9/10 – 16TB
NetWare w/NSS filesystem – 8TB
win32 w/ FAT/FAT32 – 2GB/4GB
win32 w/ NTFS – 2TB (possibly larger)
MacOS X w/ HFS+ – 2TB

On Linux 2.2, you can get MyISAM tables larger than 2GB in size by using the Large File Support (LFS) patch for the ext2 filesystem. On Linux 2.4, patches also exist for ReiserFS to get support for big files (up to 2TB). Most current Linux distributions are based on kernel 2.4 and already include all the required LFS patches. With JFS and XFS, petabyte and larger files are possible on Linux. However, the maximum available file size still depends on several factors, one of them being the filesystem used to store MySQL tables.

For a detailed overview about LFS in Linux, have a look at Andreas Jaeger’s Large File Support in Linux page at http://www.suse.de/~aj/linux_lfs.html.

Windows users please note: FAT and VFAT (FAT32) are not considered suitable for production use with MySQL. Use NTFS instead.

By default, MySQL creates MyISAM tables with an internal structure that allows a maximum size of about 4GB. You can check the maximum table size for a table with the SHOW TABLE STATUS statement or with myisamchk -dv tbl_name.

If you need a MyISAM table that will be larger than 4GB in size (and your operating system supports large files), the CREATE TABLE statement allows AVG_ROW_LENGTH and MAX_ROWS options. You can also change these options with ALTER TABLE after the table has been created, to increase the table’s maximum allowable size.

Other ways to work around file-size limits for MyISAM tables are as follows:

* If your large table is read-only, you can use myisampack to compress it. myisampack usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. myisampack also can merge multiple tables into a single table.
* Another way to get around the operating system file limit for MyISAM data files is by using the RAID options. See section
* MySQL includes a MERGE library that allows you to handle a collection of MyISAM tables that have identical structure as a single MERGE table.