16
Sep
Got this irritating message when I was working with a simple PHP /mysql app i was trying out
Client does not support authentication protocol requested by server; consider upgrading MySQL client
I just changed the database username for the application and the error went missing
http://dev.mysql.com/doc/refman/5.0/en/old-client.html has some thoughts on the same
9
Sep
MySQL.com has got a facelift. But IM not too impressed with the design. I would say the previous design was better and more readable.
MySQL 6.0Â Alpha with the new Falcon storage engine is available for download
27
May
I was migrating a simple MySQL database from my PC to my Web server.
I was stuck when I could not add records to my table . MySQl threw a duplicate value for primary key error.
I was able to add records to my table in the local database in my PC though.
When I compared the two tables side by side , I found the auto_increment property missing in the remote table.
remote table mysql 4.1.21
local table mysql 5.0.27-community-nt
Further investigation proved that the real culprit is PhpMyAdmin.
When you export a table along with the SQL compatiblity mode set to Mysql 4, it chops the auto_increment property.
Need to find a workaround for this, Or is it time to revert back to Mysql 4.1 on local or look of a Mysql 5.0 host
8
May
Object Relational Mapping ORM is a programming approach to handle data between the Database and the Object Oriented Language ex: .Net , PHP5 , Ruby on Rails, Python etc. I m writing in the context of Web Applications alone.
You might have come accross the term RAD - Rapid Application Development. ORM is a tool which helps rapid development.
CRUD - Create Read Update Delete is a cool technique used to speed up development and data entry.
There are many CRUD generators available for allmost all platforms as form development takes up a lot of development time. Some frameworks like CakePHP call it Scaffolding.Its not recommended for production environment though.
Some examples
Active Record Design Pattern
According to this design pattern, each database table is an object.So each Object is mapped to a database table (Relational Data) and hence O R M.
In short a database table is wrapped into a Class.
Also these concepts aid seperation of business logic and database code in the application.
For instance,
Product. name=”Code Book”
Product.price=”20″
Product.quantity=”10″
Product.save();
The framework should fire a SQL statement internally like
INSERT into Products (id,name,price,quantity) VALUES(null,’Code Book’,20,10)
For reading the Model / Class Data , you should be able to do something like
Product.findAll() which does a query - select * from products
Product.find(condition=>[name LIKE ‘%Code%’])
select * from products where name LIKE ‘%Code%’
I would like to tell about the Dependent Models/ Classes or Parent Child relationships.
A Product can have a ProductDetail class which is stored in the product_details table
Now we would normally have a foreign key in the product_details table which points to the Product table.
In terms of Classes/ Models the ProductDetail class is a child of the Product class. or Product class is a Parent of the ProductDetail class.
So when i load the Product using
Product.find(condition=>[name LIKE ‘%Code%’]) Internally a JOIN happens on the product_details table the object would have all related data Stored or Persisted
This is a very simple case, ideally many tables might be joined on the parent table based on a Foreign Key.
Performance Issues
What’s my concern about ORM is the amount of System resources this thing is going to take in an high traffic production environment.Say i have 1 Parent table and 30 dependent tables.
Each and every time I do any manipulation on the main table (even a simple read) 31 tables are going to used.
I remember Ram telling me that some ORMs do support Lazy Loading.
CakePHP does offer this called as the Recursive Loading where u can say recursive =0,1,2
from the manual
When the $recursive option is set to an integer value greater than 1, the findAll() operation will make an effort to return the models associated to the ones found by the findAll(). If your property has many owners who in turn have many contracts, a recursive findAll() on your Property model will return those associated models.
What I feel is that ORMs are not perfect and are highly suitable for internal applications.
Links
http://manual.cakephp.org/chapter/models
http://ramsnotes.blogspot.com/2007/02/brief-introduction-or-mapping.html
http://www.martinfowler.com/eaaCatalog/metadataMapping.html
11
Dec
My last post about MySQL was about the 10 things i like about it.
After using SQL Server there are somethings I hate about MySQL.
Support for Views only in version 5+
Support for Stored Procs only in version 5+
Triggers support only in version 5+
Lack of a Complete GUI
Lack of built in XML support
Increased development time
Slow when handling about a million records
Difficult to track changes
Too many storage engines to pick from
No inbuilt backup strategy
7
Dec
I have worked quite a few databases but most frequently land up using MySQL.
I have been working a lot on SQL Server 2005 recently at work.
Some things I love about MySQL are as follows.
High Speed of Operation
If you are talking about high speed database access and operation, there’s no better choice. MySQL became popular mainly due to this. Oracle and SQL server may be enterprise ready and more robust when compared to the database from the land of Dolphins.But I just love the speed of MySQL.
Command line interface
Simple yet powerful command line gives access entire feature set. In times of Server crashes or high traffic, using the command line via SSH is such a great possibility
Cool tools
phpMyAdmin and this database go along so well. MySQL query browser and Administrator are decent tools. Sqlyog is also good enough. There’s a commercial product called Navicat which is quite fabulous.
Keeps SQL really simple
In the land of MySQL, you need not be a SQL guru or know the ANSI SQL 92 standard . You just need to have something people call ” Common Sense ” . Its fairly easy to translate real world requirements into mysql queries. If you have worked in SQL server , I am sure you would know how much of crap SQL you need to know for simple operations. MySQL might not be the Enterprise Database but it certainly follows the KISS (Keep it Simple and Stupid) approach.
Really fast to setup
I remember using this the backend for my final year project. Its just so easy to get this one running.Be it the official installer,zip or 3rd party tools like Xampp
Nice variety of storage engines
MyISAM, InnoDB ,Heap ,Merge are some of the storage engines you can use for your database.
Cross Platform
Windows, Linux, Mac, Solaris or even on VMware.
Light Hardware Requirements
MySQL is known to eat some amount of RAM, but its works fairly well even on the most simple configurations like a AMD 1.6GHz 256MB RAM Machine. Short learning curve Pretty easy to learn
Huge User Base
This is one of the best points about MySQL. Since its free , so many people use it and so you can easily find free help.
28
Oct
I had a problem with getting the list of the tables in a mysql db.
MySQL 5.0 offers Views so the usual
SHOW TABLES
is not accurate
Used this to get the tables alone
SHOW FULL TABLES WHERE Table_type = ‘BASE TABLE’
17
Sep
Today I was playing with my pet site FoneArena.com
Was figuring out ways of improving the site. And I had not added some highly accessed columns in a table as Indexes.
So i checked out what diff a query was going to make with and without an Index.
consider this simple query :
[code lang=”SQL”]
SELECT Distinct col from table ORDER BY col ASC LIMIT 10
[/code]
The above query took 0.0034 seconds to execute
Now how indexing helped me reduce the query time to 0.0009 seconds.
Can you believe that ?
nearly 400% of time saved just by adding an Index.
1
Sep
Its been a long while since i have actually used the mysql command line client..
Thanks to phpmyadmin which i have been using for the past 2 years.
phpMyadmin does many things without troubling me.
I had a problem renaming a database and I found out that mysql does not have rename database statement until MySQL 5.1.7.
so what to do ?
mysqldump came to the rescue as usual. I had to generate a dump of the current database.
Then create a new database with the desired name, and then import back the dump into the
new database , and then drop the old database.
5
Mar
Stored Procedures at last in version 5 of MySQL.
Often people from the SQL Server world would downplay MySQL saying that it lacks views and stored procedures which have been present in the MS world and the PostgreSQL world for a long time.
With the latest version of 5.0.18 MySQL has made a promising effort to bring these cool features into its feature set. I had blogged about Views in Mysql 5 here earlier.
Now got some time to write about the Stored Procedures feature in MySQL 5
Why Stored Procedures ?
Links
Database Journal Article
I will post a part 2 of this article where i will take you through some examples
| M | T | W | T | F | S | S |
|---|---|---|---|---|---|---|
| « Oct | ||||||
| 1 | 2 | |||||
| 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| 10 | 11 | 12 | 13 | 14 | 15 | 16 |
| 17 | 18 | 19 | 20 | 21 | 22 | 23 |
| 24 | 25 | 26 | 27 | 28 | 29 | 30 |
