Technorati Profile

Why to use Procedures in MySQL

I heard this question many times from all developers which I tried to convince them to use procedures and upgrade their versions from mysql-4 to mysql-5.

They used to put their sql statements in their php pages, and then transfer the whole query to MySQL server and waiting for the response to display the result, what a way?! So I decided to make a session for all these developers to describe to them why we have to use procedures, and this is some brief points that I came with:

First, procedures are pre-complied objects so there’s no need to compile the queries each time the page called, which will increase the performance.

Let’s assume that we have a query repeated in 100 pages, so to make just a simple change such as adding a column in the selected fields or add a condition in the where clause, then you have to go through all these pages and apply these changes, that will take a long time for searching, applying, and testing, but when we using procedures the query will be in database side and it takes you to change it once, so it easier to change also.

Procedure definitely far secure than embedded queries which can interrupt by sql injection, because the procedure has already complied in the building stage, so the variables defined already and can’t have sql injection inside these variables.

I found that MySQL procedures are more reliable than oracle procedures and that because oracle build the procedure even if there was syntax error but of course gives you a warning and set the status of the procedure is invalid. Mean while MySQL strict in the right syntax and can’t accept the errors in their procedures.

Customize your SQL Writing

MySQL has many SQL Modes, and these modes rarely had been considered by SQL Developers, and my be they came from ORACLE programming or SQL Server which they didn’t have these modes.

Modes in MySQL are so important and must be considerable especially when they embed queries in their quires, to prevent sql injection for an example.

The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients.

Ways to set different sql modes:

• Start MySQL server with --sql-mode=value[,value,value,…].
• Sql_mode= value[,value,value,…] Inside my.cnf (under unix) or my.ini (under windows)
• By mysql client tool

Select @@global sql_mode= value[,value,value,…]; (effect on the whole connections)
Select @@session sql_mode= value[,value,value,…]; (effect just in the same session)

I’ll describe some sql modes here and you can find all the sql modes in mysql manual online:

• STRICT_TRANS_TABLES

If a value could not be inserted as given into a transactional table, abort the statement. For a non-transactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement

• TRADITIONAL

Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column. Which we always wondering why I didn’t get error for that just a warning ;)

• ALLOW_INVALID_DATES

Don't do full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. This is very convenient for Web applications where you obtain year, month, and day in three different fields and you want to store exactly what the user inserted (without date validation).

• ANSI_QUOTES

Treat “"” as an identifier quote character (like the “`” quote character) and not as a string quote character. You can still use “`” to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotes to quote literal strings, because it is interpreted as an identifier.

• NO_AUTO_CREATE_USER

Prevent the GRANT statement from automatically creating new users if it would otherwise do so, unless a non-empty password also is specified

• ONLY_FULL_GROUP_BY

Do not allow queries for which the SELECT list refers to non-aggregated columns that are not named in the GROUP BY clause. The following query is invalid with this mode enabled because address is not named in the GROUP BY claus.

This one when set sql queries will look like ORACLE standard queries.

Huh?!! MySQL has what other engines have and more, so by setting one or more sql modes you can customize you sql writing language. It is so great, isn’t?

Hiring PHP Developer

A leading online portal in the region is seeking PHP developers (Senior, Intermediate, Junior) levels

Number of Vacancies: 4

PHP Web Developer

Working as part of a skilled team you will develop, maintain and support existing applications across the business utilizing PHP5 , 4, mysql 4, 5 HTML and CSS

This role reports directly to the Technical Team Leader

Technical Skills

* Strong PHP programming experience (PERL experience is a plus)
* Must know JavaScript, HTML, CSS and XML
* RDBMS experience, especially MySQL
* Knowledge of DOM-scripting and the concepts behind AJAX is highly desirable
* Familiarity with OOP, is a plus
* Familiarity with VBulletin, PHPNuke, Drupal is a plus

Nontechnical skills

Good team player.
Ability to self learning.
Ability to work with different and new technologies

to apply to this position please send your CV to

basd34@hotmail.com

with Subject

Senior Web Developer
OR Intermediate Web Developer
OR Junior Web Developer

Best Regards

I'm Hiring SQL Developers In Jordan

Good opportunity to find Database job in a large company.

i want to expand my database team, so i want to hire 2 SQL developers, One for MS SQL server 2000/2005 Developer and another one is MySQL 5.0 Developer.



MS SQL Server Responsibilities and Skills:

* SQL Server development experience using DTS and T-SQL (transact SQL).
* Understand and have proven experience using DTS in the Business Logic layer - meaning: designing DTS packages within Business Intelligence solutions is a plus.
* Experience in writing stored procedures
* Experience with triggers, functions, and views.
* Experience working with end users and transform requests into designs and systems
* Excellent communication skills both written and verbal .
* Ability to work in a team environment .
* Understanding of data warehouse structure is a huge plus ( this is the Business Intelligence division and they are the data warehousing group) .
* Experience working with large (high) volumes of data .


MySQL 5.0 Developer Responsibilities and Skills:

* Experience in writing stored procedures
* Experience in writing prepare statements
* Experience with triggers, functions, and views.
* Experience with bottleneck and suggest solutions .
* Working under Unix platform.
* Ability to optimize SQL queries.
* Experience with Mysql build-in functions.
* Ability to design database system from scratch.
* Mysql 5.0 Certificate is a plus.
* Experience working with end users and transform requests into designs and systems
* Excellent communication skills both written and verbal .
* Ability to work in a team environment .
* Experience working with large (high) volumes of data .

there will be an exam for all developers to test their abilities.

for more information or sending me your resume (C.V)
email: bashar@maktoob.com
Bashar Shannak
Senior Database Administrator.

P.S please send the email with SQL Developer employee.

Thanks and Good Luck for All :)

How to backup a Mix MySQL database

in my last post i was talking about MySQL Backup strategies and Techniques, one of the most critical and difficult to backup is when we have a mix database.

First what do we mean byMySQL Mix database? we previously mentioned that MySQL has many Engines, such as MyISAM, Innodb, memory, ndbcluster, etc, so we can have in one database multiple engines inside it.

so let's suppose that we have a database mixed between two engines, "MyISAM and Innodb", what's the big deal when you want to take a backup for this kind of database, let's exam some situations for taking backups and restore the backups back:

• what if we take backup by using mysqldump, if the database size was small, then no problem, but if it was a large database then we need hours between taking backups (write it to text file), and restore it back. and the restoration time will be greater than the backup time, because it's load data to indexes too, so it's not a solution.
  
• if we take backup by mysqlhotcopy, just the MyISAM tables will backed up, and not the Innodb, and that because the mysqlhotcopy's procedure is to enter inside the database folder and copy every thing inside it by command "cp", so also it's not a solution.
• what if we copy the whole mysql directory, that will take a long time based on how many databases there are, and what the size of ibdata, which is always so large, and that because innodb tables takes a lot of storage., so we can't depends on this.
• ok then, first technique that we can depends on is taking the MyISAM tables by mysqlhotcopy, and using in the same time mysqldump to backup the Innodb only, so by this we minimize the backup time but still have a long restoration time.
• a replication is a solution, let's have another mirror database in another server, so the master server will have all the reads and writes and the slave server will be offline, so we take the backups from slave server without making any load of locking in the online database.
• clustering also could be a good solution, but it's expensive, because it's need hardwares and resources.

MySQL Backup Techniques

MySQL Backup plan and implementation is one of the Most Critical and important technique that any successful DBA should have. In fact planning a MySQL backup strategy it's not that easy and can't be standard for all databases, so it's depend on what kind of system that database run, tables engine that database have, size of the database, traffic on the database, etc.

Systems are varying, some of it financial, educational, web 2.0 (such as forums, blogs, communities, etc), news, mobile services, etc. So defiantly there should be more than one strategy to take BACKUPs for these MySQL databases.

MySQL Engines varying too, you can have a database with MyISAM engine, other have InnoDB engine, some is mixed between these two engines, or also some database has NDB cluster, so you can't use one technique to all of these situation.

MySQL Databases also varying in its size, some database its size reaches in Terra Bytes, some of it didn't reach 2MB, so the techniques for taking these database backups also varying.

So we have to set a plan for backups after studying and analyzing what database we have, and here are some general tips:

· If you have a small database with all tables created by engine MyISAM, use a mysqlhotcopy utility rather than mysqldump, and that because it's faster when taking backups and restore it.

· If you have a large database with all tables created by engine MyISAM , you may use one of these techniques, replicate your database in another server and take the backup from the slave server, or turn on your binary log and copied daily, but if you use mysqlhotcopy or mysqldump, your database will be unavailable for a long time depends on how much large your database is.

· If you have a small database with all tables created by engine Innodb , you may use mysqldump to take a backup or using the commercial tool inoodb hotcopy.

· If you have a large database with all tables created by engine Innodb , you may use you may use one of these techniques, replicate your database in another server and take the backup from the slave server, or turn on your binary log and copied daily, and that will faster in taking backups and restore it.

· Another technique is clustering your database, and then all backups you take will not effect on the database and keep the availability to the maximum 99,999%.

These are some tips to how plan you MySQL backup strategy, and maybe in later I'll get deep in these techniques and show the advantages and disadvantages for each techniques.

first time i blog...

Hello All
For everyone who knows me "Bashar Shannak", they know that i hate to blog or write eventually, and that maybe i'm working more than 17 hrs per day and on call 24/7, huuuh why is all that about? what's your job to work all these hours? and what's the interesting to work 24/7?

All these questions have one answer, just three letters enough to answer all these questions; it's a "DBA", when you are a DBA then you are dealing with the most important and critical part in any company, which is "DATA".

To be a professional DBA in my point of view only, you have treat the "DATA" as your own baby, watching it grow in front your eyes, and try to keep it away from any risk, and study how to keep it alive and available, and have many copies "Backups" from it, and spread these copies everywhere to ensure that your data will still remain somehow.

When you get a call at 3:00 am on weekend's night or holidays, you wake up so quickly to see what is going on, is it an attack on your baby "DATA", or a human error dropped everything, or hardware failure you can't have an access to your "DATA", sure it's one of them or more but for sure it is NOT a good news, it's 3:00 am sure it's not good news, then you have to prepare you plans to restore everything the way it was, and you do that, not for extra money, or for hear a thank you words, no no, it's your job, it's your baby, can you offer money for a DAD who taking care of his baby, right?

So what is good to be a DBA? you don't have extra money for your extra effort -in some companies-, you are always online, your job is so risky, any mistake worth much!!!! so why you choose to be a DBA????

When you are a DBA, it's not a skills you need to learn it's also natural of you, you love to be in control, you love to monitor what's going on and how things going on. you love to draw a plans to be ready for any risks you'll have, you love to keep tracking on your data efficiency.
But the most thing interesting to be an DBA is the feeling when you can solve any problem happened to you baby, watching it up again, this feeling draw a smile, not only on my face, also on all people who concern of this data.

So after all it's a great to be a DBA...... from my point only ;)