MySQL | Z-Car

Database Admin Controller for Code Igniter and Ext JS 4

By Jimmy February 3, 2012 MySQL, Programming, Software 5 Comments

I have been using Code Igniter and Ext JS 4 for several months. In that time, I have created a couple basic templates to display MySQL tables and perform CRUD on those tables. In addition, I have wanted to create some basic admin screens for the Core Igniter configured databases. In my configuration, I have multiple databases defined, using Code Igniter’s built-in support for multiple database access. So, given all of that, I have created a controller that will display all Code Igniter configured databases, list the contained tables, and allow you to display and edit those tables.

Right now the basic template supports simple pagination, as well as Adding, Deleting, and Updating records. While fairly full-featured, it does not yet handle remote sorting. In addition, I am sure you will find some minor display issues with data fields that are not your standard integer or varchar. I will look to address these later. As an added bonus, the template references Ext JS remotely, so you do not need to install Ext JS 4 for these admin screens to work. If you are interested in giving Ext JS 4 a try, this is a great way to explore with it.

One feature of this controller is that the panel for the table admin is dynamically generated. This will allow you to save the created JS file, and modify it to suit your needs. You can easily create very custom screens from this generated template.

If you would like to see updates, or have any issues, leave me a message here and I will try to address them.

You can download the zip file here.

To install, just add the enclosed files to your controllers and views directory and make sure you have a valid database configured.

Count number of tables in the database – MySQL

By Prodromus January 27, 2011 MySQL, Scripting 2 Comments

Determining the number of tables that are contained in a MySQL database is very straight-forward, although it is an often asked question. The simplest way to accomplish this is using the following SQL query. In this query, you will provide the database, and the SQL will access MySQL’s internal data scheme (information_schema).

SELECT count(*) as ‘Tables’, table_schema as ‘Database’
FROM information_schema.TABLES
WHERE table_schema= ‘The Database Name’
GROUP BY table_schema

If you need to retrieve this information using PHP, use can use the following code. It creates a connection, runs the query, and returns the number of rows retrieved. This query you notice is slightly different than the one above, often there are multiple ways to get the same result!

$conn = mysql_connect(‘localhost’, ‘USERNAME’, ‘PASSWORD’, 1, 65536);
$res = mysql_query( “select table_name from information_schema.tables where table_schema=’test'”, $conn );
echo mysql_num_rows( $res );

And lastly, there is one more way that you can retrieve the number of tables in a database using PHP and the Show Tables command. In this example, you will need to be connected to a server, and have the database set to the one you are querying against.

echo “<pre>”;
$tbl_List = mysql_query(“SHOW TABLES”);
$i=0;
while($tables = mysql_tablename($tbl_List,$i))
{

echo $tables;

$i++;
}
echo “<br />Table count = $i”;
echo “</pre>”;

What database does Facebook use?

By Prodromus January 27, 2011 MySQL 5 Comments

What database does Facebook use is one of the most common questions asked when folks start taking about what database is the most scalable for large scale web applications. In fact, it is usually a person who is an open source proponent, and knows very well that Facebook uses MySQL as their core database engine. Because of this fact, this is often the single biggest reason that developers use to push to get MySQL used in their company. I would imagine that is why it is a very popular Google query.

While Facebook uses MySQL, they do not use it as-is out of the box. In fact, their team has submitted numerous high-performance enhancements to the MySQL core and Innodb plug-in. Their main focus has been on adding performance counters to Innodb. Other changes focused on the IO sub-system, including the following new features :

innodb_io_capacity – sets the IO capacity of the server to determine rate limits for background IO
innodb_read_io_threads, innodb_write_io_threads – set the number of background IO threads
innodb_max_merged_io – sets the maximum number of adjacent IO requests that may be merged into a large IO request

Facebook uses MySQL as a key-value store in which data is randomly distributed across a large set of logical instances. These logical instances are spread out across physical nodes and load balancing is done at the physical node level. Facebook has developed a partitioning scheme in which a global ID is assigned to all user data. They also have a custom archiving scheme that is based on how frequent and recent data is on a per-user basis. Most data is distributed randomly. Amazingly, it has been rumored that Facebook has 1800 MySQL servers, but only 3 full-time DBAs.

Facebook primarily uses MySQL for structured data storage such as wall posts, user information, etc. This data is replicated between their various data centers. For blob storage (photos, video, etc.), Facebook makes use of a custom solution that involves a CDN externally and NFS internally.

It is also important to note that Facebook makes heavy use of Memcache, a memory caching system that is used to speed up dynamic database-driven websites by caching data and objects in RAM to reduce reading time. Memcache is Facebook’s primary form of caching and greatly reduces the database load. Having a caching system allows Facebook to be as fast as it is at recalling your data. If it doesn’t have to go to the database it will just fetch your data from the cache based on your user ID.

So, while “What database does Facebook use?” seems like a simple question, you can see that they have added a variety of other systems to make it truly web scalable. But, still feel free to use the argument, “MySQL is as good or better than Oracle or MS SQL Server, heck, even Facebook uses it, and they have 500 Million users!”.

Facebook Showcasing Its Open Source Database (allfacebook.com)
Google Chrome’s new False Start “Feature” (z-car.com)
Video: How Facebook Makes MySQL Scale (readwriteweb.com)

MySQL – ORDER BY does not sort data properly…

By Prodromus December 28, 2010 Errors, MySQL, Optimization 4 Comments

Imagine a developer working late at night trying to complete some simple queries for a report due the next day. No matter what she does, the resulting query will just not sort properly. Is this some undiscovered MySQL bug? No wonder this software is free, MySQL is broke and can’t even perform a basic sort. It looks simple, sort the data in a table:

mysql> SELECT id, technology from Enigma;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+

To sort the records based on the technology, do an ORDER BY on column Technology:

mysql> SELECT id, technology from Enigma ORDER BY Technology;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+

It seems that the server does not understand what I have asked, or I have not mastered the entire alphabet …
Let’s add the clause CSA (just in case!)

mysql> SELECT id, technology from Enigma ORDER BY ASC Technology;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+

That does not seem to be any better! MySQL does not sort the data as I wish, am I going crazy? Should I change my RDBMS?
Don’t worry, a careful look at the table structure allows us to see things clearly.

mysql> SHOW CREATE TABLE enigma;

Table: enigma
Create Table: CREATE TABLE `Enigma` (
`Id` int (11) DEFAULT NULL,
`Technology` enum (‘PHP’, ‘Linux’, ‘MySQL’) DEFAULT NULL
) ENGINE = MyISAM DEFAULT CHARSET = latin1

The column technology is of a type ENUM. The Enum field is stored as an integer, and that is the index that MySQL uses for sorting. In other words, 1 is PHP, 3 is MySQL, and 2 is Linux, so sorting does works properly, just not as expected when the Enum data field is storing character data. So how do you get the desired result? Simply force MySQL to use the value string and not index when sorting. This can be done by using either the Concat() or Cast() functions, as follows:

function concat ()
mysql> SELECT id, technology from Enigma ORDER BY concat (technology);
+——+————-+
| Id | technology |
+——+————-+
| 2 | LINUX |
| 3 | MySQL |
| 1 | PHP |
+——+————-+
function cast ()
mysql> SELECT id, technology from Enigma ORDER BY cast (technology as char);
+——+————-+
| id | Technology |
+—– -+————-+
| 2 | LINUX |
| 3 | MySQL |
| 1 | PHP |
+——+———– – +

MySQL – A simple script to truncate all MySQL tables in a Database

By Prodromus December 22, 2010 MySQL, Scripting No Comments

Want a quick and dirty way to Truncate all the tables in a MySQL DB? You can use the following one line script.

mysqldump -ppassword YourDBName –no-data dumpfile | mysql dumpfile

What this does is uses mysqldump to output all the tables in the database “YourDBName” into a dump file. This dump file contains drop statements, and create table statements for each table in the database. What this effectively does is drops every table, and recreates it WITHOUT any data inside. Be very careful, since this is a very powerful script, it has the ability to completely wipe out your database with no hope of recovery unless you have a current backup.

For additional ways to execute specific commands against all tables in a DB, look into using mk-find in Maatkit, it is an excellent tool for advanced MySQL scripting.

MySQL – Select rows from a table when a field starts with a number

By Prodromus December 22, 2010 MySQL, Query Optimization 1 Comment

A common request is how to select records from a table when a specific field starts with a number. This request often is used when searching free-form text fields where the fields were used to provide an answer field to a question.

The simplest way to attack this is by using a regular expression. For example:

SELECT * FROM BadlyDesignedTable WHERE AnswerColumn regexp ‘^[0-9]+’;

or
SELECT * FROM BadlyDesignedTable WHERE AnswerColumn RLIKE ‘^[0-9]+’;

Regex and RLIKE are compatible keywords that both represent regular expression matching.

The regex is described as follows :

^ – Start anchor, used to ensure the pattern matches start of the string.
[ – Start of character class.
0-9 – Any digit
] – End of character class

« Previous

Related articles