How to improve efficiency and reduce TCO with a professional LAMP StackOracle Linux + MySQL + Zend PHP
February 15, 2013, Milan, Italy

Nell’ultimo decennio lo stack LAMP (Linux, Apache, MySQL, PHP) è diventato la piattaforma web
più diffusa, adottata per l’espansione delle più importanti installazioni web di tutto il mondo. Dalle startup alle aziende di grandi dimensioni, sono ormai numerose le società che utilizzano lo stack LAMP per accrescere l’efficienza operativa e ridurre i costi, sia implementando applicazioni on-premise che scegliendo il modello cloud.

Oracle offre lo stack LAMP più completo oggi disponibile nel settore, mentre Zend è un’azienda leader nel software e nei servizi per lo sviluppo, la distribuzione e la gestione di applicazioni business-critical in PHP.

…

This weekend I am supposed to be giving a talk at FOSDEM on Libdrizzle.  Unfortunately my kids and I all fell ill on Thursday (my wife appears to be immune) so I had to cancel my plans (infecting 5000 people didn't seem wise :)

Instead I am writing this blog post about Libdrizzle and my part in it which covers some of what I was going to talk about.

History of LibdrizzleLibdrizzle started out as a from-scratch C connector for Drizzle and MySQL originally created by Eric Day.  It was designed to be high performance and use common standards to make it easy to work on.  In the summer of 2010 it was merged into the main Drizzle tree where development has been focused.  There were several attempts to split it out again but until now none were truly successful.

For a few years Brian …

Some show commands support a where clause. The column name that can be used in the expression for the where clause depends on the result of the show command. For example in show tables the column is Tables_in_foo where foo is the database name.

MariaDB [test]> show tables where Tables_in_test = ‘t';
+—————-+
| Tables_in_test |
+—————-+
| t |
+—————-+
1 row in set (0.00 sec)

This is a problem with the show databases command because databases aren’t really *in* anything. Database is a reserved word so this happens.

MariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| test |
+——————–+
3 rows in set (0.00 sec)

MariaDB [(none)]> show databases where database=’test';
ERROR 1064 …

While most of the talk recently has mostly been around the new changes in MySQL 5.6 (and that is understandable), I have had lately some very interesting cases to deal with, with respect to the Metadata Locking related changes that were introduced in MySQL 5.5.3. It appears that the implications of Metadata Locking have not been covered well, and since there are still a large number of MySQL 5.0 and 5.1 installations that would upgrade or are in the process of upgrading to MySQL 5.5, I thought it necessary to discuss what these implications exactly are.

To read what Metadata Locking exactly is please read this section here in the MySQL manual.

Let’s start off with having a look at the Meta Data Locking behavior prior to MySQL 5.5.3

Metadata Locking behavior prior to MySQL 5.5.3

Prior to MySQL 5.5.3 a statement that opened a …

When will it stop. These amateur programmers that simply cut/paste code really affect those good programmers in the ecosystem trying to make a decent living. I was reviewing a developed (but incomplete) PHP/MySQL system using a common framework (which in itself is irrelevant for this post).

In one source file there were 12 repetitions of the following code:

   //permissions
    $this->security_model->setUserPermissions($id);
    if (!array_key_exists($id,$this->session->userdata['permissions']) OR
        !array_key_exists('id', $this->session->userdata['permissions'][$id]) OR
        !array_key_exists('scope', $this->session->userdata['permissions'][$id]['name'])){
      $this->session->set_flashdata('alert', 'You are not authorized to go there.');
      redirect($this->agent->referrer());
    }

It’s bad enough when code is repeated and not put in a simple re factored function. When it’s …

The key to success for the database technologists is staying a step ahead of technology challenges and keep growing in order to efficiently scale to meet the needs of dynamic businesses and customers. Blogs are one way to remain abreast of technology trends and Log Buffer Edition this week once again helps database professionals in that regard in Log Buffer #305.

Oracle:

As LinkedIn grew from a startup to a post-IPO market leader, they relied on Oracle applications to support transformations, Lana Prout informs.

BPM is the promise of business transformation by maximizing the potential of turnkey business processes in any organization. But majority of organizations never realize this dream and land up restricting BPM to …

The news is already making the rounds (ex: rumors here, and confirmed here and here), but Fedora will apparently replace MySQL with MariaDB in an upcoming release.

read more

If you have to store big texts in MySQL you have to choose in between using a big VARCHAR column and the TEXT type. So what are the differences between using for example a VARCHAR(2000) and TEXT?

The difference between TEXT and the VARCHAR type is how MySQL is storing the data. TEXT (and BLOB) data is stored off the table, leaving only a pointer to the actual storage.

So using VARCHAR is faster when the size of the data is reasonable. How big the performance difference is depends on the data and the used hardware.

Due the row length limit of 65,535 bytes the data you can store in the table itself is limited (Using utf8 2000 chars could use up to 6000 bytes).

Last October I posted an example and description of a common_lookup table. It was intended to show how common_lookup tables support drop down selections in web forms. However, it wasn’t adequate to show how they work with existing data, and the function only supported fresh queries.

This post goes to the next level, and shows how to use foreign keys to preselect values for display in web forms. It also rewrites the prior function so that it supports querying existing data and inserting new data.

Let’s start with data stored in join between two tables – the member and contact tables. The internal lookup uses the customers name from the contact table to find the membership account information in the member table.

1
2 …

To get a briest status overview of your MySQL server, you can use the command \\n, it will give you a fast idea of the version you are running, the most important settings and an overview of the amount of running queries.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.29, for debian-linux-gnu (i686) using readline 6.2

Connection id:          993822
Current database:       
Current user:           root@85.50.57.117
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.66-0+squeeze1 (Debian)
Protocol version:       10
Connection:             h via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 23 days 21 hours 50 min 56 sec

Threads: 1  Questions: 12676338  Slow queries: 12  Opens: 21832  Flush tables: 1  Open …