Latest discussions near Data, No SQL, Clouds

Latest discussions near Data, No SQL, Clouds, etc:

• Hadoop Use Cases


• Hadoop/MapReduce vs SQL MPP databases/appliances


• A Comparison of Approaches to Large-Scale Data Analysis
• Databasesupport for Continuous Prediction Queries over Streaming Data
• XtremeData dbX: An FPGA-Based Data Warehouse Appliance
• Comparing the Performance of Clusters, Hadoop, and Active Disks on Microarray Correlation Computations


• Publicly Available BigData sets:


• Public Data Sets on AWS
• OpenStreetMap
• Wikipedia:Database download
• European Parliament Proceedings


• MySQL and Memcached: End of an Era?
• Yes, Sql!
• BigMemory: Heap Envy
• Considering Data Stores
• 2011 Cloud, PaaS, NoSQL Predictions
  

Optimize Queries in MySQL

List of resources about MySQL query optimization:

• 10 Tips for Optimizing MySQL Queries
• MySQL Performance Blog

Stored Procedures in MySQL 5.x: log / trace messages

There is no standard way to log / trace message in MySQL.
But there is a trick - execute SQL queries like next:

SELECT 'log message' AS 'title';

So to have Log Message feature we need next stored procedures:

• procedure to execute dynamic SQL statements
• procedure to create SELECT SQL query and simulate log output

See the code below:

DELIMITER $$
  
  
-- PROCEDURE pLog
--   outputs log message
-- Params:
--   sTitle - title
--   sMsg - log message
DROP PROCEDURE IF EXISTS `pLog` $$
CREATE PROCEDURE `pLog`(IN sTitle VARCHAR(255), IN sMsg VARCHAR(255))
BEGIN
  DECLARE strSQL VARCHAR(512);
  
  SET strSQL = CONCAT('SELECT ''', sMsg, ''' AS ''', sTitle, '''');
  CALL pExecuteImmediate(strSQL);
END $$
  
  
-- PROCEDURE pExecuteImmediate
--   executes dynamic SQL statement
-- Params:
--   tSQLStmt - SQL statement to be executed
DROP PROCEDURE IF EXISTS `pExecuteImmediate` $$
CREATE PROCEDURE `pExecuteImmediate`(IN tSQLStmt TEXT)
BEGIN
  SET @executeImmediateSQL = tSQLStmt;
  PREPARE executeImmediateSTML FROM @executeImmediateSQL;
  EXECUTE executeImmediateSTML;
  DEALLOCATE PREPARE executeImmediateSTML;
END $$
  
  
DELIMITER ;

Function pCheckCurrentUserRoot contains examples of using pLog

DELIMITER $$
  
  
-- FUNCTION pCheckCurrentUserRoot
--   checks if we are connected as root user
DROP PROCEDURE IF EXISTS `pCheckCurrentUserRoot` $$
CREATE PROCEDURE `pCheckCurrentUserRoot`()
BEGIN
  DECLARE strUser VARCHAR(50);
  
  CALL pLog('INFO', 'pCheckCurrentUserRoot...');
  
  SELECT USER() INTO strUser;
  CALL pLog('INFO', CONCAT('user: ', strUser));
  
  IF (strUser LIKE 'root%') THEN
    CALL pLog('INFO', 'current user is root');
  ELSE
    CALL pLog('INFO', 'current user is not root');
  END IF;
END $$
  
  
DELIMITER ;

Additional information about Stored Procedures in MySQL 5.x could be found here:

• Stored Procedures in MySQL 5.x
• Raise Exception / Error in MySQL 5.0

MySQL 5.x Performance Improvements

Some information about MySQL 5.x Performance Improvements:

• MySQL 5.4 Feature Summary :: 3 Scalability Improvements
  
• SMP Performance Improvements

See also:

• MySQL InnoDB Tuning

MySQL InnoDB Tuning

Here I've collected information about MySQL InnoDB Tuning:

• MySQL 5.0 Reference Manual :: 13 Storage Engines :: 13.2 The InnoDB Storage Engine :: 13.2.13 InnoDB Performance Tuning and Troubleshooting :: 13.2.13.1 InnoDB Performance Tuning Tips
• MySQL 5.0 Reference Manual :: 13 Storage Engines :: 13.2 The InnoDB Storage Engine :: 13.2.13 InnoDB Performance Tuning and Troubleshooting
• Innodb Performance Optimization Basics
• MySQL/Innodb performance optimization

Some my advices:

• After any configuration change do server restart
• After any server restart do SHOW INNODB STATUS sql command to check that InnoDB engine is up
  

Bigtable: Distributed Storage System

Here I've tried to collect information about Bigtable:

• Google Research Publication: BigTable
• Bigtable: A Distributed Storage System for Structured Data
• BigTable - Wikipedia, the free encyclopedia
• Google's BigTable
• Back to the Future for Data Storage
• The Python Datastore API
• Videos
  

Raise Exception / Error in MySQL 5.0

There is no standard way to raise exception or error in MySQL.
But there is a trick:

• call to a not existing Stored Procedure raises MySQL exception
  
• so call to not existing Stored Procedure using "error message" as "procedure name" forces MySQL to raise exception that looks like "error message"

So to have Raise Exception feature we need next stored procedures / functions:

• procedure to execute dynamic SQL statements
• function to format text
• and main procedure to raise error / exception

See the code below:

DELIMITER $$
  
  
-- PROCEDURE pExecuteImmediate
--   executes dynamic SQL statement
-- Params:
--   tSQLStmt - SQL statement to be executed
DROP PROCEDURE IF EXISTS `pExecuteImmediate` $$
CREATE PROCEDURE `pExecuteImmediate`(IN tSQLStmt TEXT)
BEGIN
  SET @executeImmediateSQL = tSQLStmt;
  PREPARE executeImmediateSTML FROM @executeImmediateSQL;
  EXECUTE executeImmediateSTML;
  DEALLOCATE PREPARE executeImmediateSTML;
END $$
  
  
-- FUNCTION fFormat
--   replaces '%s' in a string with some value
-- Params:
--   sFormat - string to be formatted
--   sPar1 - value used in replacement
-- Returns:
--   formatted string
DROP FUNCTION IF EXISTS `fFormat` $$
CREATE FUNCTION  fFormat(sFormat TEXT, sPar1 TEXT)
  RETURNS TEXT
BEGIN
  RETURN REPLACE(sFormat, '%s', sPar1);
END $$
  
  
-- PROCEDURE pRaiseError
--   raises error
-- Params:
--   sError - error message
DROP PROCEDURE IF EXISTS `pRaiseError` $$
CREATE PROCEDURE `pRaiseError`(sError VARCHAR(255))
BEGIN
  -- trick
  --   calling of not existing procedure with name that equals error message
  --   will force MySQL exception that looks like error message
  CALL pExecuteImmediate(fFormat('CALL `error: %s, solution`', sError));
END  $$
  
  
DELIMITER ;

After execution of SQL:

CALL pRaiseError("this is a sample error message")

MySQL will raise exception:

PROCEDURE error: this is a sample error message, solution does not exist

Additional information could be found here: Stored Procedures in MySQL 5.x

Disable Triggers in MySQL 5.0

Information about triggers in MySQL 5.0 you can find here:

• MySQL 5.0 New Features: Triggers
• MySQL 5.0 Reference Manual :: 18 Stored Programs and Views :: 18.3 Using Triggers
  

It is not possible to temporary disable triggers in MySQL 5.0.
But there are several tricks:

1. drop/create triggers
   • drop triggers
     
   • do anything you need (import, update, etc)
     
   • create triggers


2. Use global variable
   
   • each trigger should check this variable in its code beginning
   • set global variable to 1 (or other NOT NULL value) to disable triggers
     
   • do anything you need (import, update, etc)
   • set global variable to NULL to enable triggers
   • global variable IS NULL by default
     
   
   
   • see sample below:

schema:

script:

CREATE TABLE  `users` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
CREATE TABLE  `properties` (
  `userid` int(10) unsigned NOT NULL,
  `updated` varchar(45) NOT NULL,
  PRIMARY KEY  (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


trigger:

DROP TRIGGER IF EXISTS tUsersInsert;
  
DELIMITER $$
CREATE TRIGGER tUsersInsert AFTER INSERT ON users
FOR EACH ROW BEGIN
  IF (@DISABLE_TRIGGERS IS NULL) then
    # main trigger part
    INSERT INTO properties VALUES(NEW.id, NEW.name);
  END IF;
END; $$
DELIMITER ;


trigger is enabled during insert:

INSERT INTO users(name) VALUES('aaa');


disable trigger during insert:

BEGIN;
SET @DISABLE_TRIGGERS=1;
INSERT INTO users(name) VALUES('bbb');
SET @DISABLE_TRIGGERS=NULL;
COMMIT;


result:

Migrating to MySQL

If you plan to migrate to MySQL then next advices could be helpful for you:

1. Be famous how AUTO_INCREMENT works in MySQL
   MySQL 5.0 Reference Manual :: 3 Tutorial :: 3.6 Examples of Common Queries :: 3.6.9 Using AUTO_INCREMENT
   
   Check how SQL Mode NO_AUTO_VALUE_ON_ZERO affects AUTO_INCREMENT
   MySQL 5.0 Reference Manual :: 5 MySQL Server Administration :: 5.1 The MySQL Server :: 5.1.7 Server SQL Modes
   
   Inserting 0 to auto-numbered collumn gives next result, i.e. 0 will be replaced by next autonumbered value.
   
   


2. JPA Generator
   
   MySQL doesn't support:

   @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "xxx")

   
   It will throw exception:

   Caused by: org.hibernate.MappingException: Dialect does not support sequences

   
   It should be changed to

   @GeneratedValue(strategy = GenerationType.AUTO, generator = "xxx")



3. The default transaction isolation level of InnoDB is REPEATABLE READ
   MySQL 5.0 Reference Manual :: 13 Storage Engines :: 13.2 The InnoDB Storage Engine :: 13.2.8 The InnoDB Transaction Model and Locking
   


4. MySQL date/time types have bad precision
   MySQL 5.0 Reference Manual :: 10 Data Types :: 10.5 Data Type Storage Requirements
   
   So think about changing data type to Long
   


5. Be famous with Restrictions on Subqueries
   MySQL 5.0 Reference Manual :: F Restrictions and Limits :: F.3 Restrictions on Subqueries
   In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:
   

   
   DELETE FROM t WHERE ... (SELECT ... FROM t ...);
   UPDATE t ... WHERE col = (SELECT ... FROM t ...);
   {INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

   
   Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:
   

   
   UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);

   
   Here the prohibition does not apply because the result from a subquery in the FROM clause is stored as a temporary table, so the relevant rows in t have already been selected by the time the update to t takes place.
   
   Some useful functions for JPA/Hibernate that supports this wrapping are listed below:
   

   private static final Pattern SUBQUERY_ON_MUTATING_TABLE_PATTERN = Pattern.compile("^(.*?)(\\(\\s*SELECT\\s+)(.*?)$", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE | Pattern.DOTALL);
       
   public static int wrappedExecuteUpdate(EntityManager entityManager, Query query, Map<String, Object> parameters) {
       SessionFactoryImplementor sessionFactory = getHibernateSessionFactoryImplementor(entityManager);
   
       // if (!sessionFactory.getDialect().supportsSubqueryOnMutatingTable()) {
       if (sessionFactory.getDialect() instanceof MySQLDialect) {
           return doesntSupportSubqueryOnMutatingTable(sessionFactory, entityManager, query, parameters);
       } else {
           return supportsSubqueryOnMutatingTable(entityManager, query, parameters);
       }
   }
       
   private static int supportsSubqueryOnMutatingTable(EntityManager entityManager, Query query, Map<String, Object> parameters) {
       for (String p : parameters.keySet()) {
           query.setParameter(p, parameters.get(p));
       }
       return query.executeUpdate();
   }
       
   private static int doesntSupportSubqueryOnMutatingTable(SessionFactoryImplementor sessionFactory, EntityManager entityManager, Query query, Map<String, Object> parameters) {
       org.hibernate.impl.QueryImpl queryImpl = getHibernateQueryImpl(query);
       
       QueryTranslatorImpl qt = (QueryTranslatorImpl) new ASTQueryTranslatorFactory().createQueryTranslator(
           "tempTableWrapping", queryImpl.getQueryString(), null, sessionFactory);
       
       qt.compile(null, false);
       String sql;
       if (qt.getSqlAST().needsExecutor()) {
           sql = qt.collectSqlStrings().get(0).toString();
       } else {
           sql = qt.getSQLString();
       }
       
       // org.hibernate.ejb.QueryImpl could handle Collection parameters right ONLY for named parameters
       // (setParameterList supported only for named parameters)
       // so we need to switch from positional parameters to named parameters
       // (we have positional parameters after QueryTranslator.compile)
       ParameterTranslations pt = qt.getParameterTranslations();
       sql = switchToNamedParameters(sql, pt);
       
       Query newQuery = entityManager.createNativeQuery(getTempTableWrapping(sql, "_t"));
       
       for (String p : parameters.keySet()) {
           newQuery.setParameter(p, parameters.get(p));
       }
       return newQuery.executeUpdate();
   }
       
   @SuppressWarnings("unchecked")
   private static String switchToNamedParameters(String sql, ParameterTranslations pt) {
       String[] parts = sql.split("\\?");
       for (String p : (Set<String>) pt.getNamedParameterNames()) {
           int locations[] = pt.getNamedParameterSqlLocations(p);
           for (int l : locations) {
               parts[l] = parts[l] + ":" + p;
           }
       }
       StringBuffer sb = new StringBuffer();
       for (int i = 0; i < parts.length; i++) {
           sb.append(parts[i]);
       }
       return sb.toString();
   }

   
   Also is not supported:
   

   
   WHERE (X, Y) IN (SELECT X, Y FROM ...)

Stored Procedures in MySQL 5.x

As you know MySQL 5.0 introduced Stored Procedures which allow us to automate or program our way out of many tasks on directly on the server rather than having to write external scripts to do complex manipulation of data.

• Stored Procedures in MySQL 5.0
• MySQL 5.0 Reference Manual :: 18 Stored Programs and Views :: 18.2 Using Stored Routines (Procedures and Functions)
• MySQL Stored Procedure Programming by Guy Harrison, Steven Feuerstein

You can find some useful procedures and functions below.
The most important are:

• execute dynamic SQL statement
• raise error/exception

DELIMITER $$
  
  
-- PROCEDURE pExecuteImmediate
--   executes dynamic SQL statement
-- Params:
--   tSQLStmt - SQL statement to be executed
DROP PROCEDURE IF EXISTS `pExecuteImmediate` $$
CREATE PROCEDURE `pExecuteImmediate`(IN tSQLStmt TEXT)
BEGIN
  SET @executeImmediateSQL = tSQLStmt;
  PREPARE executeImmediateSTML FROM @executeImmediateSQL;
  EXECUTE executeImmediateSTML;
  DEALLOCATE PREPARE executeImmediateSTML;
END $$
  
  
-- FUNCTION fFormat
--   replaces '%s' in a string with some value
-- Params:
--   sFormat - string to be formatted
--   sPar1 - value used in replacement
-- Returns:
--   formatted string
DROP FUNCTION IF EXISTS `fFormat` $$
CREATE FUNCTION  fFormat(sFormat TEXT, sPar1 TEXT)
  RETURNS TEXT
BEGIN
  RETURN REPLACE(sFormat, '%s', sPar1);
END $$
  
  
-- PROCEDURE pRaiseError
--   raises error
-- Params:
--   sError - error message
DROP PROCEDURE IF EXISTS `pRaiseError` $$
CREATE PROCEDURE `pRaiseError`(sError VARCHAR(255))
BEGIN
  -- trick
  --   calling of not existing procedure with name that equals error message
  --   will force MySQL exception that looks like error message
  CALL pExecuteImmediate(fFormat('CALL `error: %s, solution`', sError));
END $$
  
  
-- FUNCTION fIsCurrentUserRoot
--   checks if we are connected as root user
-- Returns:
--   0 - table doesn't exist
--   1 - table exists
DROP FUNCTION IF EXISTS `fIsCurrentUserRoot` $$
CREATE FUNCTION `fIsCurrentUserRoot`()
  RETURNS INT
BEGIN
  DECLARE strUser VARCHAR(50);
  DECLARE iRes INT;
  SELECT USER() INTO strUser;
  IF (strUser LIKE 'root%') THEN
    SET iRes = 1;
  ELSE
    SET iRes = 0;
  END IF;
  RETURN iRes;
END $$
  
  
-- FUNCTION fIsTableExists
--   checks if table exists
-- Params:
--   vcTableName - table name
-- Returns:
--   0 - table doesn't exist
--   1 - table exists
DROP FUNCTION IF EXISTS `fIsTableExists` $$
CREATE FUNCTION `fIsTableExists`(vcTableName VARCHAR(50))
  RETURNS INT
BEGIN
  DECLARE iCount INT;
  SELECT count(*) INTO iCount FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE vcTableName AND table_type = 'BASE TABLE';
  RETURN iCount;
END $$
  
  
-- FUNCTION fIsViewExists
--   checks if view exists
-- Params:
--   vcViewName - view name
-- Returns:
--   0 - view doesn't exist
--   1 - view exists
DROP FUNCTION IF EXISTS `fIsViewExists` $$
CREATE FUNCTION `fIsViewExists`(vcViewName VARCHAR(50))
  RETURNS INT
BEGIN
  DECLARE iCount INT;
  SELECT count(*) INTO iCount FROM INFORMATION_SCHEMA.VIEWS WHERE table_name LIKE vcViewName;
  RETURN iCount;
END $$
  
  
-- FUNCTION fIsIndexExists
--   checks if index exists
-- Params:
--   vcTableName - table name
--   vcIndexName - index name
-- Returns:
--   0 - index doesn't exist
--   1 - index exists
DROP FUNCTION IF EXISTS `fIsIndexExists` $$
CREATE FUNCTION `fIsIndexExists`(vcTableName VARCHAR(50), vcIndexName VARCHAR(50))
  RETURNS INT
BEGIN
  DECLARE iCount INT;
  SELECT count(*) INTO iCount FROM INFORMATION_SCHEMA.STATISTICS
    WHERE table_name LIKE vcTableName AND index_name LIKE vcIndexName;
  RETURN iCount;
END $$
  
  
-- FUNCTION fIsColumnExists
--   checks if column exists
-- Params:
--   vcTableName - table name
--   vcColumnName - column name
-- Returns:
--   0 - column doesn't exist
--   1 - column exists
DROP FUNCTION IF EXISTS `fIsColumnExists` $$
CREATE FUNCTION `fIsColumnExists`(vcTableName VARCHAR(50), vcColumnName VARCHAR(50))
  RETURNS INT
BEGIN
  DECLARE iCount INT;
  SELECT count(*) INTO iCount FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name LIKE vcTableName AND column_name LIKE vcColumnName;
  RETURN iCount;
END $$
  
  
DELIMITER ;

Some examples how these procedures and functions can be used...

..from other procedures/functions:

DELIMITER $$
  
  
-- PROCEDURE pDropTable
--   drops table only if it exists
-- Params:
--   vcTableName - table name
DROP PROCEDURE IF EXISTS `pDropTable` $$
CREATE PROCEDURE `pDropTable`(IN vcTableName VARCHAR(50))
BEGIN
  IF (fIsTableExists(vcTableName)=1) THEN
    CALL pExecuteImmediate(CONCAT('DROP TABLE ', vcTableName));
  END IF;
END $$
  
  
-- PROCEDURE pRenameTable
--   renames table only if it exists
-- Params:
--   vcTableName - old table name
--   vcNewTableName - new table name
DROP PROCEDURE IF EXISTS `pRenameTable` $$
CREATE PROCEDURE `pRenameTable`(IN vcTableName VARCHAR(50), IN vcNewTableName VARCHAR(50))
BEGIN
  IF (fIsTableExists(vcTableName)=1) THEN
    CALL pExecuteImmediate(CONCAT('ALTER TABLE ', vcTableName, ' RENAME TO ', vcNewTableName));
  END IF;
END $$
  
  
-- PROCEDURE pCheckCurrentUserRoot
--   raise exception if current user is root
DROP PROCEDURE IF EXISTS `pCheckCurrentUserRoot` $$
CREATE PROCEDURE `pCheckCurrentUserRoot`()
BEGIN
  IF (fIsCurrentUserRoot()=1) THEN
    CALL pRaiseError('Root user is not allowed to run this script');
  END IF;
END $$
  
  
DELIMITER ;

...or from SQL:

SELECT fIsTableExists('test');

SELECT fIsCurrentUserRoot();

CALL pExecuteImmediate('SELECT 1');

CALL pCheckCurrentUserRoot();