PlanetMysql.ru – информация о СУБД MySQL

The DDL code paths for Drizzle are increasingly different from MySQL. For example, the embedded_innodb StorageEngine CREATE TABLE code path is completely different than what it would have to be for MySQL. This is because of a number of reasons, the primary one being that Drizzle uses a protobuf message to describe the table format instead of several data structures and a FRM file.

We are pretty close to having the table protobuf message format being final (there’s a few bits left to clean up, but expect them done Real Soon Now (TM)). You can see the definition (which is pretty simple to follow) in drizzled/message/table.proto. Also check out my series of blog posts on the table message (more posts coming, I promise!).

Drizzle allows either your StorageEngine or the Drizzle kernel to take care of storage of table metadata. You tell the Drizzle kernel that your engine will take care of metadata itself by specifying HTON_HAS_DATA_DICTIONARY to the StorageEngine constructor. If you don’t specify HTON_HAS_DATA_DICTIONARY, the Drizzle kernel stores the serialized Table protobuf message in a “table_name.dfe” file in a directory named after the database. If you have specified that you have a data dictionary, you’ll also have to implement some other methods in your StorageEngine. We’ll cover these in a later post.

If you ever dealt with creating a table in MySQL, you may recognize this method:

virtual int create(const char *name, TABLE *form, HA_CREATE_INFO *info)=0;

This is not how we do things in Drizzle. We now have this function in StorageEngine that you have to implement:

int doCreateTable(Session* session, const char *path,
                  Table& table_obj,
                  drizzled::message::Table& table_message)

The existence of the Table parameter is largely historic and at some point will go away. In the Embedded InnoDB engine, we don’t use the Table parameter at all. Shortly we’ll also get rid of the path parameter, instead having the table schema in the Table message and helper functions to construct path names.

Methods name “doFoo” (such as doCreateTable) mean that there is a method named foo() (such as createTable()) in the base class. It does some base work (such as making sure the table_message is filled out and handling any errors) while the “real” work is done by your StorageEngine in the doCreateTable() method.

The Embedded InnoDB engine goes through the table message and constructs a data structure for the Embedded InnoDB library to create a table. The ARCHIVE storage engine is much simpler, and it pretty much just creates the header of the ARZ file, mostly ignoring the format of the table. The best bet is to look at the code from one of these engines, depending on what type of engine you’re working on. This code, along with the table message definition should be more than enough

The DDL code paths for Drizzle are increasingly different from MySQL. For example, the embedded_innodb StorageEngine CREATE TABLE code path is completely different than what it would have to be for MySQL. This is because of a number of reasons, the primary one being that Drizzle uses a protobuf message to describe the table format instead of several data structures and a FRM file.

We are pretty close to having the table protobuf message format being final (there’s a few bits left to clean up, but expect them done Real Soon Now (TM)). You can see the definition (which is pretty simple to follow) in drizzled/message/table.proto. Also check out my series of blog posts on the table message (more posts coming, I promise!).

Drizzle allows either your StorageEngine or the Drizzle kernel to take care of storage of table metadata. You tell the Drizzle kernel that your engine will take care of metadata itself by specifying HTON_HAS_DATA_DICTIONARY to the StorageEngine constructor. If you don’t specify HTON_HAS_DATA_DICTIONARY, the Drizzle kernel stores the serialized Table protobuf message in a “table_name.dfe” file in a directory named after the database. If you have specified that you have a data dictionary, you’ll also have to implement some other methods in your StorageEngine. We’ll cover these in a later post.

If you ever dealt with creating a table in MySQL, you may recognize this method:

virtual int create(const char *name, TABLE *form, HA_CREATE_INFO *info)=0;

This is not how we do things in Drizzle. We now have this function in StorageEngine that you have to implement:

int doCreateTable(Session* session, const char *path,
                  Table& table_obj,
                  drizzled::message::Table& table_message)

The existence of the Table parameter is largely historic and at some point will go away. In the Embedded InnoDB engine, we don’t use the Table parameter at all. Shortly we’ll also get rid of the path parameter, instead having the table schema in the Table message and helper functions to construct path names.

Methods name “doFoo” (such as doCreateTable) mean that there is a method named foo() (such as createTable()) in the base class. It does some base work (such as making sure the table_message is filled out and handling any errors) while the “real” work is done by your StorageEngine in the doCreateTable() method.

The Embedded InnoDB engine goes through the table message and constructs a data structure for the Embedded InnoDB library to create a table. The ARCHIVE storage engine is much simpler, and it pretty much just creates the header of the ARZ file, mostly ignoring the format of the table. The best bet is to look at the code from one of these engines, depending on what type of engine you’re working on. This code, along with the table message definition should be more than enough

Taking TOTD #121 forward, this blog explains how to create a JPA Persistence Unit for a MySQL sample database and package it as a library. This JAR file can then be easily included in other web applications.

Lets get started!

1. Configure GlassFish for using the MySQL sample database (sakila) as described in TOTD #121.
2. Add the GlassFish instance in NetBeans IDE using "Services" panel.
3. Create JPA entities using NetBeans IDE.
   1. Create a Java class library:
      
      
      
      Our ultimate goal is to create a reusable JAR file and that's why this project type is chosen.
      
   2. Specify the name of project as "SakilaPU":
      
      
      
   3. Right-click on the project and select "New", "Entity Classes from Database ..." to initiate the process of entity generation:
      
      
   4. Choose the database connection as:
      
      
      
      If not configured, then can be easily done by clicking on "New Database Connection ..." in the list box.
      1. Click on "Add All >>" to generate the mapped JPA entities for all tables and views.
      2. The views do not have primary keys and will need to be appropriately annotated (described later).
      3. Click on "Next >".
         
   5. Give the package name as:
      
      
      
      and specify the package name as "sakila". Click on "Create Persistence Unit ...".
   6. Change the default PU name from "SakilaPUPU" to "SakilaPU":
      
      
      
      and click on "Finish". Notice that "EclipseLink", the Reference Implementation of JPA 2.0, is used as the persistence library.
   7. Add "@javax.persistence.Id" annotation to the following class/field combination:

      Class	Field
      sakila.SalesByFilmCategory	category
      sakila.ActorInfo	actorId
      sakila.FilmList	fid
      sakila.CustomerList	id
      sakila.NicerButSlowerFilmList	fid
      sakila.StaffList	id
      sakila.SalesByStore	store

      
      This is required because none of the "views" are defined with a primary key.

   8. Right-click on the project and select "Clean & Build". This generates "dist/SakilaPU.jar" and the structure looks like:

      

This JAR file can now be included in any web application. The pre-built JAR file can also be downloaded here. The key items to note about this pre-built JAR:

• Persistence Unit Name: "SakilaPU"
• All classes are in "sakila.*" package.
• Each class has a pre-defined "<CLASS-NAME>.findAll" named query that returns all elements from the underlying view/table.
• Can be easily added in "WEB-INF/lib" directory of your web application.

Even though this blog uses a MySQL sample database, these steps can be easily followed for any other database such as Oracle or JavaDB.

Technorati: totd javaee glassfish v3 jpa eclipselink persistenceunit mysql sakila netbeans

This blog clearly explains how to configure the MySQL sample database (sakila) with GlassFish. Even though the instructions use a specific database but should work for other databases (such as Oracle, JavaDB, PostgreSQL, and others) as well. The second half of the blog provide specific syntax for the Oracle sample database.

1. Download sakila sample database and unzip the archive.
2. Install the database as described here - basically load and run "sakila-schema.sql" and "sakila-data.sql" extracted from the archive.
3. Create a new MySQL user account using MySQL CLI Admin and assign the privileges
   1. Using "root" user (sudo mysql --user root)
      

      CREATE USER glassfish IDENTIFIED BY 'glassfish';
      GRANT ALL PRIVILEGES ON *.* TO 'glassfish'@'localhost' IDENTIFIED BY 'glassfish';
      FLUSH PRIVILEGES;
      

   2. Using "glassfish" user (sudo mysql --user glassfish)

      source sakila-schema.sql;
      source sakila-data.sql;
      

4. Download Connector/J, unzip and copy "mysql-connector-java-5.x.x-bin.jar" to "glassfish/domains/domain1/lib/ext" directory.
5. Start GlassFish server as:

   asadmin start-domain
   
   

6. Create a JDBC resource
   1. Create JDBC connection pool as:

      asadmin create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource --restype javax.sql.DataSource --property "User=glassfish:Password=glassfish:URL=jdbc\:mysql\://localhost/sakila" jdbc/sakilaPool
      

   2. Test the JDBC connection pool as:

      asadmin ping-connection-pool jdbc/sakilaPool
      

   3. Create the JDBC resource as:

      asadmin create-jdbc-resource --connectionpoolid jdbc/sakilaPool jdbc/sakila
      

That's it!

Creating a JDBC resource for any other database requires the following updates to the steps mentioned above. Lets consider modifying these steps for the Oracle sample database.

1. Use the client interface SQL*PLus and connect as:
   

   sqlplus "/ as sysdba"
   

   
   create user and grant the privileges as:
   

   CREATE USER glassfish IDENTIFIED BY glassfish DEFAULT tablespace users TEMPORARY tablespace temp;
   GRANT CONNECT TO glassfish IDENTIFIED BY glassfish;
   GRANT UNLIMITED TABLESPACE TO glassfish;
   GRANT CREATE TABLE TO glassfish;
   GRANT CREATE SEQUENCE TO glassfish;
   

2. Copy the appropriate JDBC driver (ojdbc6.jar).
3. Create the JDBC resource as:
   

   asadmin create-jdbc-connection-pool --datasourceclassname oracle.jdbc.pool.OracleDataSource --restype javax.sql.DataSource --property "User=hr:Password=hr:URL=jdbc\:oracle\:thin\:@localhost\:1521\:orcl" jdbc/hr
   asadmin ping-connection-pool jdbc/hr
   asadmin create-jdbc-resource --connectionpoolid jdbc/hr jdbc/hr
   

   
   as explained in TOTD #108.

Here are a few other related entries:

• RESTful representation of sakila using NetBeans and GlassFish

• JPA + Servlet 3.0 Application using Sakila in Eclipse

• JSF + JPA + EJB Application using Oracle, NetBeans, and GlassFish

Technorati: totd javaee glassfish v3 jpa mysql sakila oracle

Bernhard blogged over at http://ocklin.blogspot.com/2010/02/mysql-cluster-711-is-there.html that MySQL Cluster 7.1.1 Beta has been released. The big feature (from my point of view) is the SQL interface on top of NDB$INFO. This means there is now full infrastructure from the NDB data nodes right out to SQL in the MySQL Server for adding monitoring to any bit of the internals of the data nodes.

Bernhard blogged over at http://ocklin.blogspot.com/2010/02/mysql-cluster-711-is-there.html that MySQL Cluster 7.1.1 Beta has been released. The big feature (from my point of view) is the SQL interface on top of NDB$INFO. This means there is now full infrastructure from the NDB data nodes right out to SQL in the MySQL Server for adding monitoring to any bit of the internals of the data nodes.

Another short post just to remember the procedure for the next time I’ll be setting up a new mac. For those of my readers who do not know what Midnight Commander (aka mc) is, GNU Midnight Commander is a visual file manager, created under a heavy influence of Norton Commander file manager from dark DOS ages For more information, you can visit their web site. Now, get to the installation topic itself.

To install mc on a Mac OS X machine, you need macports installed and then first thing you’ll need to do is to install some prerequisite libraries:

Next thing, download the sources from their web site and unpack them. When the sources are ready, you can configure the build:

Then, normal GNU-style build and install procedure:

And the last thing would be to add /opt/mc/bin to your PATH environment variable.

Those of you that know the documentation well will be aware of the old page we used to have for the MySQL documentation. It was huge, and over the years we’d done a number of things to try and improve the layout and make it easier to find what you wanted. We had in-age links to jump to the different documentation types, and the old topic table that allowed you to jump to specific parts of the documentation.

The problem was that the more documentation that we produced (and there are over a thousand docs in various formats now), the bigger the page got. When we added the individual topic guides, for example, we trebled the size of the page by adding the links for each individual topic guide.

Ultimately that makes it increasingly difficult for you guys to find what you are looking for, despite the quick links and other elements.

We’ve now changed all this and split the single, big, monolithic page of *every* piece of documentation that we create, and instead spread the documentation out over a number of pages. The actual documentation itself remains the same, and we still have the same range of documentation (in fact, it’s increased slightly as I’ve been able to squeeze in a few more formats and topic guide docs), but everything is still there.

The key is the new sub-navigation bar that the Web team have provided us with:

The pages have been split out as follows:

• MySQL Manual — the full, complete reference manuals
• Workbench — the Workbench manuals
• Expert Guides — the standalone guides for some of our more detailed products and system such as the Falcon storage engine and the MySQL Test Framework
• Topic Guides — the topic reference, with the topic table at the top providing direct links into the 5.1 manual or standalone guides, and the full list of downloadable standalone guides.
• MySQL Cluster — the full cluster manuals, including the guide to the MySQL Cluster API (NDBAPI)
• Other docs — other documentation, not already mentioned, including the sample databases (Sakila, World, Employee), the help tables you can import into MySQL, and printed material and links elsewhere.
• MySQL Uni — a page about the MySQL University, which is run by the documentation team, and which provides links to the MySQL Uni pages on Forge
• About — information about the documentation team, who we are, and some statistics on the documentation we produce
• Archives — archives of older manuals

We are aware of a few issues with some of the links to some documentation, and I’m working right now to address those problems, but all the documentation should be there and available. If it isn’t, please report a Bug.

I’ve talked about PERFORMANCE_SCHEMA in the past - almost a year ago now.

Back then the feature was just coming together in to something useable, and was on the cusp of moving towards code review. It entered code review, and went around, and around for 9 months, whilst various refinements were made.

Never the less, Marc Alff persevered (much respect!), and yesterday pushed his final merge in to the mysql-next-mr bzr tree. PERFORMANCE_SCHEMA is now awaiting the next milestone release, and will be a part of the next GA release of MySQL!

We have the initial documentation ready:

http://dev.mysql.com/doc/performance-schema/en/index.html

This first round adds the infrastructure to take monitoring of the MySQL Server to the next level, initially adding in instrumentation for sync points (mutexes, rw locks, etc.) and file IO, in the SQL layer, and most of the default storage engines (all those controlled by MySQL/Sun).

Here’s a shout out to the other storage engine developers - we’d love you to start looking at instrumenting your own engines as well. Ask away on the internals@ list - I’m sure Marc will be more than willing to help.

And now that we have the above in - what’s next? It’s a good question! Here’s a list of the major outstanding worklogs:

WL#4674 PERFORMANCE_SCHEMA Setup For Actors
WL#4895 PERFORMANCE_SCHEMA Instrumenting Table IO
WL#4896 PERFORMANCE_SCHEMA Instrumenting Net IO
WL#4878 PERFORMANCE_SCHEMA Trace

Personally, I’m voting for WL#4895 next (well, I’d like to see InnoDB instrumentation too!), but you can vote for what ever you are most interested in via the votes in Worklog as well.

Thanks for all the hard work Marc!

I’ve talked about PERFORMANCE_SCHEMA in the past - almost a year ago now.

Back then the feature was just coming together in to something useable, and was on the cusp of moving towards code review. It entered code review, and went around, and around for 9 months, whilst various refinements were made.

Never the less, Marc Alff persevered (much respect!), and yesterday pushed his final merge in to the mysql-next-mr bzr tree. PERFORMANCE_SCHEMA is now awaiting the next milestone release, and will be a part of the next GA release of MySQL!

We have the initial documentation ready:

http://dev.mysql.com/doc/performance-schema/en/index.html

This first round adds the infrastructure to take monitoring of the MySQL Server to the next level, initially adding in instrumentation for sync points (mutexes, rw locks, etc.) and file IO, in the SQL layer, and most of the default storage engines (all those controlled by MySQL/Sun).

Here’s a shout out to the other storage engine developers - we’d love you to start looking at instrumenting your own engines as well. Ask away on the internals@ list - I’m sure Marc will be more than willing to help.

And now that we have the above in - what’s next? It’s a good question! Here’s a list of the major outstanding worklogs:

WL#4674 PERFORMANCE_SCHEMA Setup For Actors
WL#4895 PERFORMANCE_SCHEMA Instrumenting Table IO
WL#4896 PERFORMANCE_SCHEMA Instrumenting Net IO
WL#4878 PERFORMANCE_SCHEMA Trace

Personally, I’m voting for WL#4895 next (well, I’d like to see InnoDB instrumentation too!), but you can vote for what ever you are most interested in via the votes in Worklog as well.

Thanks for all the hard work Marc!