Posts Tagged ‘MySQL’

Multiple databases support on ZF

Wednesday, June 8th, 2011

Zend Framework is nice but learning it is very confusing and time demanding. Official doc isn’t that clear, lacks of good examples and sometimes is outdated. Whenever I want something to be done I read the official documentation and then google it so I can get a good picture of what everyone did.

One thing that I recently needed was to enable multiple schema support for a specific project. One schema stores client info, the other stores products. One very interesting thing is that the products schema depends on the account so clients for region 1 should use a schema called reg1, while clients from region 2 a schema called reg2.

So the products schema kind of switches dynamically according to users setup.

The first thing to do is setup the config file. This is the easy part, I added some lines that looked like this:

resources.multidb.maindb.adapter = oracle
resources.multidb.maindb.persistent = true
resources.multidb.maindb.host =
resources.multidb.maindb.username = usr
resources.multidb.maindb.password = usr123
resources.multidb.maindb.dbname = "(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=oracle.host.com)(PORT=1521)) (ADDRESS= (PROTOCOL=TCP)(HOST=oracle2.host.com)(PORT=1521)) (LOAD_BALANCE=yes) (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=srvcnm)))"

resources.multidb.seconddb.adapter = oracle
resources.multidb.seconddb.persistent = true
resources.multidb.seconddb.host =
resources.multidb.seconddb.username = usr2
resources.multidb.seconddb.password = usr2123
resources.multidb.seconddb.dbname = "(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=soracle.host.com)(PORT=1521)) (ADDRESS= (PROTOCOL=TCP)(HOST=soracle2.host.com)(PORT=1521)) (LOAD_BALANCE=yes) (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=srvcnm2)))"

resources.multidb.thirddb.adapter = oracle
resources.multidb.thirddb.persistent = true
resources.multidb.thirddb.host =
resources.multidb.thirddb.username = usr3
resources.multidb.thirddb.password = usr3123
resources.multidb.thirddb.dbname = "(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=toracle.host.com)(PORT=1521)) (ADDRESS= (PROTOCOL=TCP)(HOST=toracle2.host.com)(PORT=1521)) (LOAD_BALANCE=yes) (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=srvcnm3)))"

Notice I’m storing everything in a multidb array with three different elements: maindb, seconddb and thirddb. Let’s consider the maindb to be the users schema, for products I’ll be switching between seconddb and thirddb.

First of all the main connection needs to be set so we can login, update, delete and o more stuff with a user account. Alo this connection will be set as the default adapter. I did this in the bootstrap. Here is the code:

/**
* Init the main database connection.
*/
protected function _initDatabase(){
    $applicationConfig = new Zend_Config_Ini(APPLICATION_PATH . '/configs/application.ini', APPLICATION_ENV);
    $config = $applicationConfig->toArray();
    $dbIni = $config['resources']['multidb']['maindb'];
    $db = Zend_Db::factory($dbIni['adapter'], $dbIni);
    Model_DbTable_Db::setDefaultAdapter($db);
}

So now we have the main connection up and working as a default adapter. This works the same as a basic database configuration. Now to the interesting part… set up the other database connection according to the user region. In my case the user’s account has a field/property that stores the correct products schema name so one of the things I’ll be doing is reading that property to know which schema I need to connect to for the products details. I won’t explain the account part because it’s quite simple.

We use a baseController from which we inherit all other controllers so we can easily concentrate most of the common methods in one single place. Within that baseController I added the following method:

/**
* Depending on the account we set up the adapter for the products tables
* @param Account A valid account object
*/
private function setProductsSchema(Account $account){
    if(!Zend_Registry::isRegistered('erpdb')){
        $applicationConfig = new Zend_Config_Ini(APPLICATION_PATH . '/configs/application.ini', APPLICATION_ENV);
        $config = $applicationConfig->toArray();
        $dbIni = $config['resources']['multidb'][strtolower($account->getSchemaName())]; // In this case $account->getSchemaName() may return 'seconddb' or 'thirddb' 😀
        $erpdb = Zend_Db::factory($dbIni['adapter'], $dbIni);
        Zend_Registry::set('erpdb',$erpdb);
    }
}

Now the application is able to dynamically choose the proper schema for products according to the account. It’s important to add it to the registry with a name, in this case is erpdb. This is necessary in order to let the DbTable objects know which schema they’ll use. Be sure to call this method before making a query to the secondary schema.

Now let’s set up the DbTable objects so they know which connection they’ll be using. Right now all DbTable objects are using the default adapter set in the bootstrap, for those objects that refer to products we need to make them aware that they’ll be using the secondary connection. doing this is pretty straightforward.

Go to your DbTable object and add the following method:

/**
* Set the default adapter for this object
*/
public function init(){
    $this->_setAdapter('erpdb');
}

That’s it! Notice that we set the adapter name the same as the db registry added before.

And that’s basically all we need to do to make the connection dynamic 😀

Hope it helps.

Zend Server – Mac OS issue with MySQL

Tuesday, May 3rd, 2011

So I finally decided to install a dev env in my Mac.
A couple of days I installed Zend Server on my Mac but I wasn’t too convinced about installling a dev env in my Mac so I uninstalled Zend Server as I thought was the proper way to do it: Drag the Zend Server from apps to the trash.
So yesterday I finally made up my mind and decided to go along with a dev env. Since the first install went smoothly I thought it would only require me to do exactly the same thing…. WRONG!!!!
I downloaded the package and ran the installer, no error messages were displayed, I even opened the Zend Server console and it worked, I even tested a few scripts and it was doing fine.
Next thing I wanted to do was to create a database in MySQL and this is when my headache started.
First time I installed Zend Server I realized it wasn’t enable to work as a network server, by default you can connect to it by a socket which is fine if the server will only run on your machine… Honestly I didn’t have any problems with it… the problem was that apparently MySQL wasn’t working.
Ok, calm down, don’t panic. I thought it had something to do with socket permissions… WRONG!
mmmm ok, what exactly can be the problem?
I opened a console and typed:

sudo [path_to_zend_bin]/zendctl.sh restart

Apache did ok, Zend Server GUI (Lighthttpd) did ok but MySQL didn’t…. it showed the following message:

ERROR! MySQL manager or server PID file could not be found!
Starting MySQL
. ERROR! Manager of pid-file quit without updating file.

What in God’s name does that mean????
I searched it in Google, apparently I’m not the only one experiencing the issue. It looks that there were a lot of different solutions but none of them worked for me :(…. Now I really wanted my Zend Server to work, and I’m stubborn so I kept searching, went to the forums (official MySQL forums) and nothing. By that time I was both mad and sad. I tried deleting files, reinstalling, changing owners and NADA!
Finally, since it was already to late and I was falling asleep I decided to leave it for “tomorrow”.
I even dreamed about this not being solved hahahahaha.
Anyway the next day I relaxed, saw a couple of videos on youtube and finally came up with the most easy solution to this in a blog.
In a few words this meant that me dragging the Zend Server app to the trash can is not the proper way to uninstall it. I have to use the following command:

sudo /usr/local/zend/bin/uninstall.sh

It was the only thing I didn’t try so I typed it and hit enter…. this got the job done!!! :O
OMG! it was sooooo simple, I felt stupid hahahahahaha
So I’m posting it here to remember it.

Lock wait timeout exceeded; try restarting transaction

Sunday, February 14th, 2010

So last friday I came up with this nasty message… Believe me, it was a very troublesome issue to solve. We have MySQL running Innodb for a couple of tables. We wanted to modify 3 tables and we decided to create a transaction if inserts and updates went ok then commit if not rollback.

I did it and tested it on my dev env (my lap hehehehe) and it all worked fine, speed was ok and everything worked as wanted. So I commited it to my svn server and uploaded the code to the production server. 5 min. before I leave the office my boss decided to test the new version…. good and bad idea, good because he got the nasty error, bad because of the time.

Anyway, we saw the error. Initially I thought it was because of a web service (no, I didn’t realized it was a database issue), then after discarding lots of probable errors I realize it was a transaction error. After hours looking and reading google search results I learned the following: When doing a transaction tables involved receive a lock, the lock is released once the transaction ends. If a table is being updated within the transaction and you try to select data from it then you’ll be trying to access a locked table, since it is locked you can’t read from it and since you can’t read from it you can’t finish the transaction so the waiting for the lock to release takes for ever and the connection throws the error:

“Lock wait timeout exceeded; try restarting the transaction”

How did I solved it?

After trying docens of solutions without success I finally realized that I had to take the reading statements off the transaction.

It worked! And because I forget things very fast I decided to put it here.

Hope it helps someone else out there.

When a server asks for help!

Saturday, September 26th, 2009

So it’s been a couple of days since the last post but I’ve been occupied with Nsteins WCM, customer support and server maintenance.

This post has to do with server maintenance and the proper diagnosis of problems.

About a month ago one of our most important customers called arguing that their web page was “down”. I’m no longer working full time for that customer but because I’m basically the server admin the ball was thrown to me.

Not all the site was down, it was a subdomain wich was aquired to a third party because they did such a great job with a project. So the company I work for was given the task to improve and mantain the site. Since the guy that created the site was proficient with Javascript and not that good with PHP he decided to code the whole site in Javascript and deal with the database through simple PHP scripts (right! AJAX).

He even coded a javascript obfuscator! He’s a man in his old 40’s so I was really impressed.

Anyway there’s this tiny minimum html code and then a HUGE obfuscated Javascript code.

Since the former programmer wasn’t really a programmer he coded the stuff to work forgeting about efficiency, security and errors management. Being the lack of errors management the issue that made me go bananas for like a week.

The site was displaying just HTML code, anything related to databases was blank. No error messages, not a clue of what was happening. So my coleagues asked me to take a look at the server and so I did.

First thing I checked were the files then the permissions. After that I went to php.ini and enabled error messages. Then I got this error saying that the script weren’t able to connect to the database, cool! this looked like an easy task!

Typed top, view the server status and realized MySQL was down but trying to start. Why wasn’t it starting?

I manually tried to start MySQL and got an error that after a couple of minutes I realized means the server HD is full. It was a 70 Gb HD so what was growing so fast?

Went to mysql data folder and realized a table was using 17Gb, ok still I’m missing lots of Gb so i started to look for very big files playing with find:

find / -type f -size +1000000k -exec ls -lh {} \;

I got a huge list and saw a couple of access log files related to apache. All of them adding a total of approx 12Gb. So I went and deleted them and left one so Apache could use it.

Everything went fine for a couple of weeks and then it happened again and I did the same thing I did before: delete the files and restart MySQL.

A week ago happened the same and also yesterday so I thought the database was growing really fast and indeed it was taking all the disk’s free space so I went ando told that to my boss and made a petition to get another disk up ASAP.

Just today the server went down again! I deleted the files and freed 2 Gb, a couple of hours later it went down again! WTF! something weird is happening! the database couldn’t grow so fast so I took a dive in the server searching again for long files and ther it was! a HUGE log file (25Gb) called mysqld.log…..

So stupid me read the last 50 lines and became aware that a table was damaged and needed to be repaired… the whole 25 Gb were the same message!!!! To make it better the damaged table size was 17Gb… 😮

repair table [table];

After a couple of hours I got a message indicating the table was repaired, reduced the log file size to cero and gave me a hug, everything was ok. Later that day just checked the logs again and surprise! they were empty. The thing is that the damaged table didn’t stored any onfo for about a month…. Next time the first thing I’ll check will be logs.

If a server asks for help this is how it’ll talk to me. Rookie no more hahahaha

osCommerce installation problems solved!

Thursday, September 3rd, 2009

I decided to install oscommerce to sell stuff.

Created a vhost, created the database, downloaded oscommerce, copied the files to the proper directory, assigned the right privileges and typed the proper url, up to here everything is really easy, looks smooth.

So here comes an installation screen. It looks like an easy process right?

First you type in the database connection info after you click ok you get a nice message saying that the database was created successfully!, the next steps are trivial, it’s a couple of normal settings. On the last step you can see 2 buttons that take you to the catalog or to the administration tool.

So I clicked catalog…. boom! nothing! just a nasty error page with something like this:

Warning: require(includes/languages/.php) [function.require]: yadda yadda on line 288

Ok, so I tried the administration tool… crap! same screen! something wrong is happening!

Being a PHP programmer I was like OK fine let’s correct the code, so I opened my eclipse and started debugging… after a couple of hours of adding flags, debuging, linking code logic and more I started thinking that it might be a DB problem so I went to the database console and check the language table WOW! it’s empty! ok, lt’s see configuration… empty!

Ok, I don’t think oscommerce delivers an empty database right? so after looking at the code I ended up on line 57 of the file install/rpc.php and there’s the sql source file.

Next thing I did was directly import the file to mysql…. ok 😀

Did a couple of queries to see if the languages and configuration tables were empty and they were not so the last step was to refresh the catalog and administration tools and guess what? They both work!!!!

So I’m writing this post to help anyone that gets the same errors.

I’ll correct the code and let oscommerce know something is wrong with their code.

Memcached, working!!!!

Thursday, September 3rd, 2009

Hard work, a couple of hours of investigation and a few modified lines of code later I got a project working with memcached and let me tell you it does work!!!

I was very happy when I saw my database being released from a lot of processes.

What I love the most is the options memcached gives you to choose how much time a cached object will remain active in the server!!! for very uncommon queries I set the cache to remain on the server for 15 min while for the other more persistent queries I decided to give them no limit.

I’m really pleased! just sent an email to my boss so he can check this out and it’s 2 o’clock in the morning so job done and now it’s time to go to sleep.

Memcached

Wednesday, September 2nd, 2009

Wow!

I’m in charge of a site that uses a lot of resources, thousands of images, thousands of db rows and literally thousands of visitors each day. We are currently getting approximately 60 to 70 thousand visitors but the thing is that by the time we get to 60 thousand the server starts to slow down. The require for images, queries and number of petitions per second made us think of a redesign of the site.

First step was to load the balance between 2 servers for http request and for database queries. Each server will have its own database and web code. Over those servers we put a load balancer hardware.

Second step was clean the database, eliminate duplicated rows and reindex fields. Throw away those fields that were not used and improve queries.

Third step was cleaning up the code, delete lines of unused code and repurpose a couple of functions. We also set a template engine (Smarty).

Now it seems the site is working fine server speed improved but still the database queries were too slow… what was left to do? Memcache!

First install memcache on the server. In my case I’m running CentOS so I just had to do:

yum install memcached

Then install php’s memcache support:

yum install php-pecl-memcache

Restart httpd and then type the following command on your server’s console

memcached -d -u apache -m 512 -p 11211

What this does is that it starts the memcached daemon with apache user privileges usin 512 Mb of RAM memory and using port 11211.

Since you’ll be needing port 11211 don’t forget to check your iptables config so you may be able to use it.

That’s it! everything is ready, the only thing left is modify your php’s code and you’re on your way.

I haven’t tested it but to be honest I really think it will save us lot’s of resources from the server. I’ll post the result in a few days.

MyODBC

Monday, August 24th, 2009

Bueno, que continuando con el proyecto me encuentro con que el connector ODBC de MySQL en su version 3.51 hace algunas cosas extrañas.
No detallaré todas y cada una de ellas, simplemente mencionaré una de ellas.
Cualquier tabla o campo cuyo nombre supere los 21 caracteres se recorta a 21 caracteres!
Por qué razón no lo sé, pero la cuestión es que me estaba volviendo loco y ya estaba pensando en renombrar las tablas de mi BD!!!! Con cerca de 60 tablas y algunas con más de 20 campos sería una labor de flojera. Pero la solución llegó después de continuar investigando un poco:

http://bugs.mysql.com/bug.php?id=32864

Simplemente bajé el conector 5.1 y el asunto quedó solucionado.

Update: En este caso hay que actualizar el valor del registro del que hablo en el post anterior, lo único que hay que hacer es cambiar el 3 por un 5 y listo!

Crystal Reports 10 & MySQL

Monday, August 24th, 2009

Las grandes dificultades que uno se encuentra al hacer un proyecto.
Como comentaba en una entrada anterior estamos en un proyecto que necesita generar a través de CR10 unos PDF’s y XLS’s. Todo marcha a la perfección, los archivos se generan ok pero el único detalle es que ahora en vez de funcionar con MS-SQL Server funcionarán con MySQL. La muda de base es transparente, no hay problemas de ningún tipo y los script de ABC funcionan correctamente. El problema viene al conectar los reportes de CR!
Qué sucede? bueno primero CR no soporta MySQL de manera directa así que se conecta a través del ODBC de MySQL que se puede descargar de aquí:

http://dev.mysql.com/downloads/connector/

Aparentemente todo funciona ok con este odbc pero oh sorpresa, en caso de que el SQL generado por CR10 sea más complicado y utilice Outer Joins entra en escena una pesadilla!
Sucede que CR10 cuando utiliza Outer Joins genera un script con una sintáxis que el ODBC de MySQL no entiende!, son unas pocas letras en cuestión:
{oj …… }
Aquí truena el query y CR10 es incapaz de traer un registro.
Cómo solucionarlo?! me estaba generando una cantidad enorme de canas verdes, lo que debería de ser una adecuación transparente se estaba convirtiendo en mi peor pesadilla.
No encontré manera alguna de desactivar eso!
Gracias a dios en Google encontré la respuesta adecuada y los caminos por los que me llevó eran totalmente sorprendentes.
El string de búsqueda fue:
“{oj” mysql crystal reports
Tal cual con dobles comillas y todo. en fin el primer y segundo resultado de la búsqueda me llevaron a la solución. La lectura no fue del todo sencilla, decenas de colegas tenían el mismo problema y a pesar de que lo resolvieron de repente dejaban semanas sin reportar nada jajaja pero aquí mi determinación por encontrar una respuesta me llevó por el camino correcto.
Caí en esta página:
http://bugs.mysql.com/bug.php?id=18563
Como bien lo hace notar MySQL NO es un error/bug de ellos, más bien es un asunto de CR10.
El chiste es que después de leer encontré esta liga:

http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do?cmd=displayKC&docType=kc&externalId=c2016114&sliceId=&dialogID=16742671&stateId=1%200%2016744519

En pocas palabras es el tema en cuestión y la respuesta que da es que hay que agregar un registro a CR para que omita esa sintáxis al usar el MyODBC.
Hay que seleccionar entre varias opciones:

Para Crystal Reports 10:
------------------------

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Crystal Decisions\10.0\
Crystal Reports\Database\QueryBuilder\OuterJoinEscSeq]
"NoOuterJoinEscSeq"="MyODBC3"

Para Crystal Reports XI Release 1:
-----------------------------------

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.0\
Crystal Reports\Database\Querybuilder\OuterJoinEscSeq]
"NoOuterJoinEscSeq"="MYODBC3, libmyodbc3"

Para Crystal Reports XI Release 2:
-----------------------------------

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Business Objects\Suite 11.5\
Crystal Reports\Database\Querybuilder\OuterJoinEscSeq]
"NoOuterJoinEscSeq"="MYODBC3, libmyodbc3"

Cualquiera ingresarla en un block de notas y guardarla con la extensión .reg, darle doble click y esperar que se agregue el registro.
Ahora bien, hice lo anterior y seguía apareciendo la sintáxis!!!! bueno ya un poco más tranquilo decidí revisar si acaso el registro estaba bien agregado… sorpresa!!! no estaba bien así que lo agregué a manita.

Cerré y ejecuté de nuevo CR10 y voilá! todo ok!
Ahora sí, a continuar con mi trabajo y terminarlo rápidamente. Espero que quien lea esto solucione su problema.
Saludos & happy coding!