01.Soporte de
DBA Oracle

  • Servicios de DBA Oracle.
  • Servicios de Adm Linux y Solaris.
  • Servicios de Networking.
ver más

02.Desarrollos
Web

  • Desarrollo de paginas web, portales.
  • Desarrollo de soluciones/aplicaciones a medida
      basadas en Internet.
ver más

03.Soluciones
Back Office

  • Hosting de sitios y aplicaciones.
  • Mail, Calendario, Docs, Social web.
  • Soluciones a medida y Cloud Computing.
ver más

últimos posts / pensamos

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 
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:

http://forum.wegotserved.com/index.php/topic/19923-timemachine-on-lion-with-netatalk/
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 :)

Regards,
Jose

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.

Strings

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;
and
  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.

MySQL's
    "INSERT IGNORE", "INSERT REPLACE", "INSERT ... ON DUPLICATE KEY UPDATE" and "REPLACE",
and SQLite3's
    "INSERT OR REPLACE INTO"
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);
            $stmt->execute();
        }
    } 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)");
            $stmt->execute();
        }
    }

Results:
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. 

Regards,

Jose.

Ver entradas anteriores »

casos de exito

ÚLTIMOS Tweets

Cargando tweets...

2010 Copyright © 4TM - todos los derechos reservados

www.4tm.biz