4TM Blog

Mysql 5.6 on Ubuntu 12.04 LTS

mysql , 5.6 , database
Jose Luis Canciani

i've recently had to test some queries on Mysql 5.6. Here's a very quick help to install the latest version using the Oracle supplied .deb package.

Go to mysql.com and download the latest package in .deb format.

Install dependencies, the most important is libaio-dev:

sudo apt-get install libaio-dev

Now install the package:

sudo dpkg -i mysql-5.6.10-debian6.0-x86_64.deb

The package will install on /opt/mysql/server-5.6. Now we need to integrate this with our system, since it doesn't include setup scripts for doing it automatically.

First, lets create a user and group, and the data directory (you can place it anywhere, but if you change it leave a symbolink link or change the my.cnf file to reflect the new path):

sudo groupadd mysql
sudo useradd -r -g mysql mysql
cd /opt/mysql/server-5.6/
sudo mkdir data
sudo chown mysql:mysql data

Now create the basic database, setup the config file and service, configurate log rotation and start the service:

sudo scripts/mysql_install_db --user=mysql
sudo cp support-files/my-default.cnf /etc/my.cnf
sudo cp support-files/mysql.server /etc/init.d/mysql.server
sudo cp support-files/mysql-log-rotate /etc/logrotate.d/mysql.server
sudo service mysql.server start

We are done! Now just set up set the bin directory in the PATH so you can use the binaries anywhere you are:

echo 'export PATH=$PATH:/opt/mysql/server-5.6/bin' | sudo tee /etc/profile.d/mysql.server.sh

Just open a new console/terminal and you should be able to run mysql client binary.

Time Machine with OSX Lion and a Linux server

lion , ubuntu , netatalk , avahi , macosx , debian , macbookair
Jose Luis Canciani


I got my new Mac Book Air, 13", 2011, Core i7, 256GB SSD. Incredible speed and portability. And the new Mac OS Lion.

Everything worked great except... Time Machine. I have a fitPC1 (http://www.linuxfordevices.com/c/a/News/Tiniest-Linux-system-yet/) from several years ago that I used as a File Server, SVN repo and LAMP server (among other things like torrent client). It worked great with Snow Leopard, but it was time for an upgrade.

Apparently the old version of NetATalk I had installed does not worked with Lion anymore. No new packages (of anything) were to be found for my Ubuntu 8.10. So I started with the long -but no so painful- upgrade process: 8.10 => 9.04 => 9.10 => 10.04.3 LTS (I could have made a clean install, but didn't want to reconfigure everything).

After that, I installed netatalk 2.2 from source (download at http://netatalk.sourceforge.net/ )


./configure --enable-debian 
make install


Everything default but with the debian keyword to have scripts added in my ubuntu init. Avahi daemon was already setup from my previous install, if you don't have it, see next link).

Differently from the binary package I used the first time (as mentioned here: HowTo: Make Ubuntu A Perfect Mac File Server And Time Machine Volume http://www.kremalicious.com/2008/06/ubuntu-as-mac-file-server-and-time-machine-volume/ ), configure put my config files in /usr/local/etc/netatalk by default, so I created a symbolic link from /etc/netatalk => /usr/local/etc/netatalk and replaced/updated my old files as I see fit.

I tried to add the uams_dhx2.so to the afpd.conf file, which was suggested in every forum I visited, with not so much luck. Lion saw the volumes, but kept giving me permissions errors. I had to change from cdb to dbd in the AppleVolumes.default file, and deleted all .Apple* from all directories in the shared folders just to be sure there was no conflic with the old db).

After that, everything was working but Time Machine. I could not see the drive available in the Time Machine setup screen.

I googled and googled, tried several things until I got it working. Let me make a list of what I did:

This is a coLinux tutorial (running netatalk on Windows with coLinux). It had useful scripts to configure some files. These one I used:

sed -i s/Xserve/TimeCapsule6,106/ /etc/avahi/services/afpd.service
uuid=`uuidgen | tr '[a-z]' '[A-Z]'`;mac=$(ip link show eth0 | awk '/ether/ {print $2}')
echo -e '\n\n\n\t\n\t\t_adisk._tcp\n\t\t9\n\t\tsys=waMA='$mac',adVF=0x100\n\t\tdk0=adVF=0x81,adVN=TimeMachine,adVU='$uuid'\n\t\n' >/etc/avahi/services/adisk.service
mkdir -p /media/tm # for log file
echo - -tcp -noddp -uamlist uams_dhx.so,uams_dhx2.so -nosavepassword -setuplog \"default log_warn /media/tm/.afpd.log\" >>/etc/netatalk/afpd.conf
echo \"TimeMachine\" $uuid >>/etc/netatalk/afp_voluuid.conf

It still didn't work, but helped me setup several files that didn't exists. There is an "option:tm" in this tutorial that I missed (since I already had my AppleVolumes.default configured and working, so I didn't pay attention to modification of that file). It would have help me later to save some time instead of keep googling.

I kept googling and found this one: Time Machine meet Netatalk http://fmepnet.org/time_machine.html
Down there there's a simple touch that did the magic:

touch /pool/backup/time_machine/.com.apple.timemachine.supported

(change /pool/backup to your time machine shared directory, in my case /mnt/seagate640).

Magically the drive appeared in Time Machine, altough not sure if after or before doing this on my macbook air:

sudo defaults write com.apple.systempreferences TMShowUnsupportedNetworkVolumes 1

I could select the disk, configure Time Machine and start the backup. But, after some seconds, I received this error: "The network backup disk does not support the required AFP features."

I kept googling until I found this newsletter: http://permalink.gmane.org/gmane.network.netatalk.devel/10018 that mentioned: "Please check if you're really setting options:tm for the Time Machine volume"

That was it! I modified by AppleVolumes.default:

:DEFAULT: options:upriv,usedots
/mnt/seagate640 seagate640 allow:joe,melina cnidscheme:dbd options:usedots,upriv,tm
/var/cache/torrentflux torrentflux allow:joe,melina cnidscheme:dbd options:usedots,upriv

I added "tm" to my options and it worked! I now have my Lion backing up to my Fit-PC Ubuntu 10.04 server, with a Seagate 640GB Password drive attached.

I hope this helps someone to avoid hours googling and trying over and over again :)


Mysql, SQLite3 and ANSI SQL thoughts

sql , sqlite3 , mysql , ansi sql
Jose Luis Canciani

I recently did a refactor for one of the tools I maintain to switch from MySQL to SQLite3. It's and error reporter tool, so we need it to be available even if MySQL fails Tongue out

Even though it's a small app, I hit several things that are worth mentioning. Please note that this is not a recommendation to start doing things differently, it's just a series of issues I found in the process.


The standard way is to enclose stringsin SQL in simple quotes. No need to escape anything but the simple quote itself. Escaping is done with another simple quote.

insert into contactInfo (contactId, lastName) values (100, 'O''Neal');

Escaping % and _ in the LIKE function is done by adding an explicit the escape character:

select * from contactInfo where firstName like 'hola\_mundo' escape '\';

In Mysql we were using double quotes, and we escape with backslash. This is not cross platform (see ANSI_QUOTES bellow).

Object names - identifiers- (tables, columns, functions, etc)

Almost all database engines are case insensitive when referring to object names. Double quotes is used when you want to use a reserved word, special characters, unicode characters or case sensitivity. If you don't use double quotes, then the names are usually translated to uppercase in the database dictionary, but it remains case insensitive when referring to it.

In Mysql, table names are case sensitive (because they are files in a case sensitive filesystem, at least on unix/linux). This could be changed using the lower_case_table_names parameter. Mysql would convert all table names to lowercase for storage and lookups.

But also in MySQL, columns names are not case sensitive. So
  select FirstName from contactInfo;
  select firstname from contactInfo;
is the same, no matter how you write the SQL to create the table.

In Mysql you can use, like we were doing, backquotes; but that won't help in setting a case to column names (or index, store routines and column aliases)

Mysql                         Standard SQL
create table `table` (        create table "table" (
  `select` varchar(10)          "select" varchar(10)
);                            );

Regarding the use of quotes, we could change the behavior to use the standard doble quotes by setting the ANSI_QUOTES sql mode. Note that once you do change it, all double-quoted strings become identifiers, so all strings are required to be enclosed by single quotes.

SQL statement diferences

INSERT INTO TABLE SET col1 = val1, col2 = val2 is not a valid SQL in other database apart from Mysql. The correct use is INSERT INTO table (<columnlist>) values (<valuelist>), although I have to say that I like this syntax.

SQLite3 does not support TRUNCATE TABLE, but when doing a DELETE without a where clause, it is treated as a truncate statement.

and SQLite3's
are not standard SQL. The correct SQL to do these tasks is the MERGE statement, but non of them supports it (today). It would be a good practice to wrap all this queries using classes, that way time and work for switching dbs -if we need to- will be greatly reduce.

SQLite3 Types

SQLite3 supports less types than mysql:

            SQLITE3_INTEGER => 'int',
            SQLITE3_FLOAT => 'float',
            SQLITE3_NULL => 'null',
            SQLITE3_TEXT => 'text',
            SQLITE3_BLOB => 'blob'

You don't need to specify a character set for a text column, you simply insert what you have in a variable. If its UTF8, you insert UTF8, if its ASCII, you insert ASCII.

Bind Variables (or Bound Parameters)

Both databases now support bind variables. We do not use it today, but when running the same query several times Binding variables have good performance advantages since the query is only parsed once. It also removes the SQL Injection problem, since there is no need to escape strings anymore.

Here's a quick example, inserting 10000 rows in a table:

    if ($testType == 'Bind') {
        $stmt = $conn->prepare("insert into test (a,b,c,d,e,f,g,h,i,j) values (?,?,?,?,?,?,?,?,?,?)");
        for($i=1;$i<=10000;$i++) {
            $stmt->bind_param("iiiiiiiiii", $i,$i,$i,$i,$i,$i,$i,$i,$i,$i);
    } else {
        for($i=1;$i<=10000;$i++) {
            $stmt = $conn->prepare("insert into test (a,b,c,d,e,f,g,h,i,j) values ($i,$i,$i,$i,$i,$i,$i,$i,$i,$i)");

Bind 10000 inserts done in 0.57499408721924 seconds
No bind 10000 inserts done in 1.006609916687 seconds

The difference could be even greater on SELECT statements with a lot of tables, since the time to parse and generate an execution plan will increase.

When having hundreds of queries per second, like we do, time and CPU reduction could be considerable. Since MySQL 5.1.17, prepared SQL Statements are cached too. 



Sending HTML AWR reports with unix sendmail

oracle , tuning , awr , scripts
Jose Luis Canciani

A small command to send your AWR html report (from sqlplus you run @?/rdbms/admin/awrrpt.sql).


Un pequeño comando unix para enviar el reporte HTML de AWR (que se obtiene con sqlplus si corres @?/rdbms/admin/awrrpt.sql).


[oracle@DBSERVER]$ cat << EOF | sendmail -t
> From: oracle@yourserver.yourcompany.com
> To: your.email.address@yourcompany.com
> Mime-Version: 1.0
> Content-type: text/html; charset="iso-8859-1"
> Subject: AWR report inline
> `cat awrrpt_1_10697_10698.html`

Nuevo sitio web / New Website


Finalmente se encuentra en linea el nuevo diseño de 4TM.biz. También estrenamos nuevas secciones como Ask A DBA, donde puedes realizar consultas a un DBA Oracle.

Esperamos que te guste el nuevo diseño.



Finally the new website design is online at www.4tm.biz.

We not only changed the design, we are also preseting a new application called Ask A DBA where you can ask question to an Oracle DBA. It's mainly for Spanish use but we will answer in English too if you ask. Remember asktom.oracle.com already does this too!

New Oracle Vulnerabilities

oracle , security
Jose Luis Canciani

These last days have surprised us with several vulnerabilities on Oracle products. Here's a link for an Oracle report from Feb 4th:


Also it has been circulating a vulnerability that would allow any database user to access any filesystem file with oracle ownership. This is serious since the attacker could potentially delete database files, for example.

Workaround until next patch is easy, just remove execute privilege from PUBLIC to package DBMS_JVM_EXP_PERMS. Another one includes a DBA escalation (sysdba) via the DBMS_JAVA.SET_OUTPUT_TO_JAVA procedure. You can remove execution on the package, which is not the case for all of you that use the package for something else.

More details on http://secunia.com/advisories/38353/

Displaying config for Embedded PLSQL Gateway

4tm.biz , oracle , sql scripts , plsql
Jose Luis Canciani

Recently I posted an article about displaying a binary file from pl/sql directly to a browser. This is done with 10.2's feature EPG (Embedded PL/SQL Gateway). Yes, you can serve HTTP content (html, js, json, binary files like images and of course xml) directly from your Oracle database. I'll be posting a detailed article about how to configure it (if you need help now, check out this site). For now I'll post a very handy script I've just written.

Sometimes you don't know how EPG is configured and you have to go through the DBMS_EPG package to find that out. It's not a straight thing: you need to do some plsql programming... it would be nice a v$ view but it's not available as far as I know. So this script show come handy for those in need (I know I'll be one of them in the future ;) ).


set serveroutput on

	*  Get all the database DADs and their configuration
	*  Written by jose.canciani
	*  Source: http://www.4tm.com.ar/4tmsite/wordpress/?p=31
	dads dbms_epg.varchar2_table;
	mappings dbms_epg.varchar2_table;
	attr_names dbms_epg.varchar2_table;
	attr_values dbms_epg.varchar2_table;
	dbms_output.put_line('-Global Attributes:');
	if attr_names.count > 0 then
		for i in 1..attr_names.count
			dbms_output.put_line('-  '||attr_names(i)||': "'||attr_values(i)||'"');
		end loop;
		dbms_output.put_line('-  Not global attributes found.');
	end if;
	for i in 1..dads.count
		dbms_output.put_line('-DAD: "'||dads(i)||'"');
		dbms_output.put('-  Mappings:');
		for j in 1..mappings.count
			dbms_output.put(' "'||mappings(j)||'"');
		end loop;
		dbms_output.put('-  Authorized Users:');
		for j in (select username from dba_epg_dad_authorization where dad_name = dads(i))
			dbms_output.put(' "'||j.username||'"');
		end loop;
		dbms_output.put_line('-  Attributes:');
		for j in 1..attr_names.count
			dbms_output.put_line('-    '||attr_names(j)||': "'||attr_values(j)||'"');
		end loop;
	end loop;


Here's a quick output of the script (I only have one DAD configured and no global attributes):

-Global Attributes:
-  Not global attributes found.
-  Mappings: "/oramon/*"
-  Authorized Users: "ORAMON"
-  Attributes:
-    database-username: "ORAMON"
-    default-page: "showpage?p_name=index.html"
-    error-style: "DebugStyle"
-    owa-debug-enable: "On"

That's all!

Facebook hack to see any photo album

php , internet , sql , facebook , api
Jose Luis Canciani

UPDATE 16-Jul-2009: apparently it's not working anymore! It took several weeks for facebook to fix it!

In order for Facebook to patch this, I'm writing this method that has become so popular these last days. You can see any album of any user in Facebook by following the next steps:


1) Get the person's ID: search him/her on facebook and look in the "View Frinds" link where you'll see something like this: http://www.facebook.com/friends/?id=1660869234. This number is the user's ID.

2) Go to http://developers.facebook.com/tools.php?api -> API Test Console

3) Choose "Facebook PHP Client" in Response Format.

4) Choose "fql.query" in Method.

5) Now you'll see a text area called "query": wrote there the following replacing xxxxxxxxxx with the contact's ID you find in the first step:


SELECT name, link FROM album WHERE owner=xxxxxxxxxx


6) Run the query to find all the contact's albums. Just take the URL of the album you want and paste it in your browser.


That's it! Let's hope Facebook fix it soon, but until then, enjoy looking at pictures your enemies shouldn't have uploaded ;)

Display a BLOB file using PLSQL Web Toolkit

dbms_epg , oracle , plsql
Jose Luis Canciani

I've been working in a simple reporting tool with PL/SQL Web Toolkit in Oracle 10g. Basically you just configure a DAD and the listener opens an HTTP port (8080 by default) and you just access your procedures and print html from PL/SQL. Pretty cool and no extra software or server configuration (no Apache or iAS or APEX needed).

For static content I needed to display image files, so I started looking a bit on Google and I end up with this configuration.

First, I created two tables: one to store my character lob content (CLOB) like html and js files, and another for my binary (BLOB content like images and other binary documents.


create table static_cfiles (
	file_name varchar2(50),
	file_type varchar2(20),
	contents clob,
	primary key (file_name)

create table static_bfiles (
	file_name varchar2(50),
	file_type varchar2(20),
	contents blob,
	primary key (file_name)


Then, I develop a procedure to read the tables based on the page name parameter. The CLOB is easy to read, you just assign it to a CLOB variable on PL/SQL and print it with HTP.P. But the BLOBS are a bit different. Here's a sample code for getting the blobs:


create or replace procedure showblob (p_name IN VARCHAR2)
	page_b_contents blob;
	intImgSize INTEGER;
	content_type static_bfiles.file_type%TYPE;
	select file_type, contents
	into content_type, page_b_contents
	from static_bfiles
	where file_name = p_name;
	-- get image size
	intImgSize := dbms_lob.getlength(page_b_contents);
	-- print the header    	
	owa_util.mime_header(content_type, FALSE, NULL);
    	htp.p('Content-length: '|| intImgSize);
    	-- htp.p('Pragma: no-cache');
    	-- htp.p('Cache-Control: no-cache');
    	-- htp.p('Expires: Thu, 01 Jan 1970 12:00:00 GMT');

 	-- download BLOB


The wpg_docload.download_file procedure does all the magic: it signals the PL/SQL Gateway that the blob is to be downloaded to the client's browser. No copy is needed!

I've seen examples that do a dbms_lob.copy to a PL/SQL variable previously to displaying the file: that's dangerous! Imagine you have a 4GB document... you'll be eating all the PGA!

The Content-Length is useful when you are sending a big file since your browser will be able to know how much is left and can draw you a progress bar or just give you the percent done. I've also put there other common headers, feel free to use them as you need.

For static images you better not use the No-Cache option... unless you are changing images from time to time :)


Oracle: Histograms and Bind

oracle , sql , sql tuning
Jose Luis Canciani

When the Oracle Optimizer needs to generate an execution plan for a query it goes to through the already taken statistics for the tables and indexes involved.

Histograms are one of those statistics taken from tables. Basically they show how the values of a column are distributed. Oracle will use that to determine how many number of rows will be return by a certain filter on the column. If there are few rows, then it will choose to use an index (if any on that column). If there are a lot of rows then Oracle could choose to do a full table scan.

Doing these calculations requires resources (CPU) so we use Bind Variables to avoid re-parsing of the SQL. So once the plan has been calculated, Oracle will use it again and again until it is removed from the shared pool. The execution plan is calculated only once. But when using Bind Variables, what values are used to calculate the plan? Yes, the values of the first query it processes.

Do you start to see the problem here?

Lets say your table has a column called callerid. This column stores the caller phone number, and "0" if no callerid was registered. Now let's say you store millions of calls per month. Your table is now 60 million rows.

Each phone number does an average of 5 calls per month. Top call registered from one number is 2000. Those are very good numbers for creating a btree-index on "callerid".

But what happens with the "0" value? You'll have million of rows and the index is not a good choice (since it will be cheaper to do a full scan than to use the index with so many rows).

That's exactly the problem: if the first query Oracle recieves has the "0" value in the callerid column, then the stored plan will not use an index, and all future queries will be slow even with different values on callerid!!!

Let's get the histogram information:


select num_rows from dba_tables where owner = 'OWNER' and table_name = 'CALLS';


select HISTOGRAM from dba_tab_col_statistics where owner = 'OWNER' and table_name = 'CALLS' and column_name = 'CALLERID';


select callerid, count(1) 
from calls
group by callerid 
order by 2 desc

CALLERID          COUNT(1)      
0                 3127825 
310596574            1808 
293584543            1405          
915089527            1229          
915144244             694 


And now let's take a look at the histograms and see how even is this data distributed on the table:


  2  from dba_histograms
  3  where table_name = 'CALLS' and column_name = 'CALLERID' and owner = 'OWNER'
  4  order by 1;
--------------- --------------
              8              0
              9      269304330
             10      283241184
             11      289850129
             12      295923724
             13      296092273
             14      296248610
             15      296416759
             16      296585656
             17      296757192


As you can see the table is HEIGHT BALANCED according to the statistics taken previously, that is their values are evenly distributed, except for the first 8 buckets of the histogram. Each bucket in the histogram represents (63627796/254) ~= 250503 rows. So in our case the "0" value is repeated (8*250503)~= 2 million rows.

Remember we said that the Optimizer builds the execution path based on the values of the first query it receives? What happens if the first query it receives involves a CALLERID = 0?


select * 
from calls 
where callerid = :b1 


:b1 bind variable is 0, so oracle receives the query and determine that it does not need to use the index on callerid. This is the best thing to do with a "0" value, but next time the application will do the same select with callerid = 269304330 and what will happen? Since the execution plan is already on the Shared Pool, it will be reused, WITHOUT THE INDEX!!!!

This is called "Variable Peeking", and is a know problem. It can happen at any time. Imagine your gather_stats_job run on the CALLS table at Sunday night. The execution plan becomes invalidated. On Monday a user opens the application and do the query with callerid = 0 and BAM! Your new execution plan is created without getting the index.

For this situation you can do several options. The best thing would be to HINT your SQL to use the right index (or not to use it when the value is 0, if you can know it before running it).

But sometimes you can't touch the application code. In those cases you can create a Store Outline for the query with the DBMS_OUTLINE package. See Metalink note 463288.1 for more details.


Last comments

2010 Copyright © 4TM - All Right Reserved