MySQL Chicklets can help promote MySQL

Today I came across very neat buttons (chicklets) for W3C. The best thing about the chicklets is that they take very little space and convey the message elegantly.



For many of my projects, I would like to include a "includes MySQL" logo to give MySQL credit for providing a wonderful software. However, as you can see, the sheer size of "Includes MySQL" logo is quite heavy for the screen real-estate.

Why should MySQL authorize the use of these chicklets?
Because then developers can place the MySQL chicklet without hesitation on their side bars as it will consume little space.

So using Silkscreen, a small free font for Web graphics, I created a proof of concept for MySQL chicklets.

Do not use/distribute these chicklets without permission from MySQL AB.

Please note that these are only proof of concepts chicklets and you should not use them until MySQL AB approves them for use (and puts them on their web site). I am publishing them here only to get MySQL's attention (and possible approval). MySQL is the trademark of MySQL AB and it's trademark policy is available here.





Do you like these chicklets? Do you hate them? I would love to know. Maybe your comments can help me convince MySQL that they should allow the use of the above chicklets.

Does anyone know where should I write to get permission for these chicklets? If MySQL AB approves, then I will update this blog entry. Otherwise, I will simply remove the chicklets.

Thanks
Frank

Is MySQL AB experiencing a bozo explosion?

"Bozo explosion" seems to be buzzing around the blogosphere. Started by Guy Kawasaki, who gave some great tips on avoiding the bozo explosion, many others including Scobleizer and Jeremy have jumped in on it.

I don't work for a really big company that could be suffering from a bozo explosion but was really interested in asking MySQL employees about whether they think MySQL is experiencing a bozo explosion. I think they will come back and say no, but that will give us folks a look inside the company's culture.

My favorite pieces:

1. The two most popular words in your company are “partner” and “strategic.” In addition, “partner” has become a verb, and “strategic” is used to describe decisions and activities that don't make sense.


Correct me if I am wrong but I haven't seen as much of "partner" and "strategic" talk from MySQL. I guess the two most popular words at MySQL are "features" and "Oracle" .

Guy Kawasaki: #9: "An employee is paid to do nothing but write a blog."

To which Scoble said:
"#9 got a little close to home. ;-) *"

My wife also takes that statement a little personally ;-)

Scoble also adds:

"#15: If you are a software developer and if you spend more time in meetings than writing code you might be in a bozo explosion"

And last but not least:

#12. Your hire a big name consulting firm who brings in MBAs with one year of experience to re-think your corporate strategies.

So folks lucky to work at MySQL AB, what do you think?

Frank

Paypal: This invoice has already been paid.

Earlier, I was talking about transferring an OSCommerce store that uses MySQL. Today I was told that the test orders being placed on the store were receiving an error that "This invoice has already been paid."

At first I thought that it may be a bug as the following post suggests:
Paypal: This invoice has already been paid.

But then after a little bit of thinking I realized that this problem was ocurring due to a discrepancy in order numbers. The client had asked me to transfer the store and let them test it for a while before they make the switch. During this testing phase, the "live implementation" of the store was already receiving orders. Therefore when the client tried to place an order using the "test implementation" they received the errors that "this invoice has already been paid".

I told the client that once they are ready for a switchover, I will resynch the databases and the problem should be gone.

Frank

What provides the optimal usage?

Today, while browsing around, I came across a post by Jeff, a CNET employee who was wondering about what provides the optimal usage (assuming a server has 16GB of RAM)? To paraphrase his question, would it be better to use a Large RAM disk or have the MySQL live on the hard disks?

So far I haven't used RAM disk for MySQL simply because most of the servers didn't have 16GB of RAM and some of the databases I worked with were quite larger than 16GB.

I assume that using RAM disk would provide a volatile infrastructure but would certainly improve the performance. Gurus are your thoughts? What can be the possible disadvantages of setting up a RAM disk for large databases?

Your thoughts and comments are needed.

--Frank

What a weekend

It seems like Markus was doing something similar to me this weekend. We both were dealing with unwelcomed (and scary) nightmares. His ordeal was related to PostgreSQL and MySQL, mine was a bit scarier. In other words, while he knew exactly what went wrong from the start, I did not have that luxury.

It happened right about midnight. I was working on finalizing Lucene integration with my new community project when suddenly the server went down for automatic reboot due to heavy load (someone was trying a brute force attack). It wouldn't come back up.

After hours of communicating with my unmanaged hosting provider, the server was still not back up.

The issue as they reported was that my hard drive was mounted read-only and there were a lot of /dev/null related errors. They (my hosting company) told me that my hard drive may have gone bad.

Unfortunately my entire project was on the hard drive. *sweat*

All night I couldn't sleep.

Today, I was told it could take many weeks to have a systems support specialist investigate the issue.

I researched furiously without KVM or direct access and was able to find some pointers which I communicated to my hosting company. Basically I asked them to recreate the /dev/null properly after removing it. To my horror, they told me that after recreating /dev/null, the system won't come back on.

As I sat in a state of shock I posted on the hosting company's support forum where a different SSS came to my rescue and within minutes he had the server back online (he recreated /dev/null properly this time).

So the lesson here is that if you are working on a project of any kind, keep backups on different drives. Normally I am pretty paranoid about such things but since this project was in development I hadn't backed it up. All my data and MySQL database schema were at risk.

In the end I didn't have enough words to thank the SSS. Without him, I would be crying.

In related notes:

Arjen had a homework for everyone about checking Horde's schema but before I could comment he posted the answer. He also has a post differentiating relation from relationship, something that many people often get wrong.

Rumors are that Google may be planning to buy Sun Microsystems. I have commented earlier that Yahoo should consider jumping in.

sun MySQL community backup database Google

MySQL with JDBC

As part of integrating Lucene with my Ruby on Rails project, I needed to get MySQL working with Java on my server. Following are the summarized steps I took to make my first query:

1. Download MySQL Connector J
2. After downloading, extract the archive

tar -zxvf mysql-connector-java-3.1.12.tar.gz

3. Copy the jar file to your class path. If using bash, modify .bashrc and place the class path there. Remember to export the classpath so it's available to your current session.

4. Create a connect function (The following is a crude example. Modify before usage). Also ensure you have a property named con of type Connection defined.

public static void Connect()
throws Exception {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
}
catch (Exception E) {
System.err.println("Unable to load MySQL driver");
E.printStackTrace();
}
try {
String url = "jdbc:mysql://127.0.0.1/db";
con = DriverManager.getConnection(url, "hosting", "flores0050");
}
catch (SQLException E) {
System.out.println("SQLException: " + E.getMessage());
System.out.println("SQLState: " + E.getSQLState());
System.out.println("VendorError: " + E.getErrorCode());
}
}

Then in your app do something like:

try { Connect(); }
catch (Exception e){ System.out.println("ERROR in connection" + e.toString() ); }

Statement statement = con.createStatement();
ResultSet RS = statement.executeQuery ("SELECT * FROM " + " table1 , table2 WHERE table1.id = table2.foreign_id " );
while (RS.next()) {
// ...
//RS.getString("id")
//RS.getString(1)
}
con.close();
RS.close();
statement.close();

Some errors:
If you get the error:

SQLException: No suitable driver
SQLState: 08001
VendorError: 0

Then make sure you have the right database settings (URL) and that the driver (connector J) is in your class path)

If you get the error:

SQLException: Before start of result set

Then check out my post on my Ruby on Rails blog. Basically you need to make sure you are calling the first() or next() function before using your retrieved result set.

Other notes:
- Instead of relying on MySQL to provide Full Text Searches for your application, upgrade to a more robust (and fast) Full Text search solution like Lucene. I have always wondered why something like Lucene can't be integrated with MySQL (may be with a special engine).
- My wife has a new blog where she comments about everything/anything she wants to talk about.
- If using Lucene in Action Book by Erik Hatcher, make sure you are not using deprecated code (the book has great info but seems to be full of deprecated code)
- Markus has a post on event and table logging test
- Sheeri has a cool post about MySQL inheritance that I would like to check out in near future.

MySQL UC 2006: Watching everyone go!

As the time for MySQL UC 2006 approaches closer, all my blogosphere friends are excited about going to the event of the year, that is except me of course.

The sessions are so mouth watering that I can hardly control myself.

I really wanted to be at the conference myself but unfortunately won't be able to make it this year.

Lately I have been working crazily trying to integrate Lucene with my Ruby on Rails based application. I first tried Ferret by Dave Balmain and it was working just fine however I needed some serious scalability for this application and after a week of research and testing, I finally decided to go with Lucene.

Today I bought Lucene in Action by Erik Hatcher and while it has some great information, I was kinda disappointed that he didn't go into implementation details using XML RPC. Nonetheless the book is a real gem.

The challenge was to integrate Lucene using XML RPC. After an entire day, the good news is that I have the server working. Now I just need to polish it up.

And after five years, I am finally developing in Java. Feels so weird to come back to a language.

Frank

Best way to ensure 100% data mirroring?

From what I understand, there are at least three ways one can go about replicating/mirroring MySQL databases to different servers overs the Internet

1. Use MySQL Replication.
All incoming data will be sent to a master which will then read the binary logs to the slaves. The replication can be “circular” or “one-master-to-many-slaves”

2. Use MySQL Clusters
Pros: Delivers “99.999%” availability
Cons: Need a lot more RAM and hardware for heavy load system

3. Through Application (Redundant)
Application first sends the data / operation to a master for replication. Following the success of the above, the application then sends data to a third independent server (that is not replicated)

Since 2004, I have been using replication and it works fine except when for some reason the slaves get behind the master.

I would like to ask the MySQL gurus here a few questions.

What is the best way in your experience to ensure 100% data mirroring, since Replication can break and slaves can, at rare but possible instances, fall behind the master (for e.g. when file based operations are performed on master)?

Can MySQL use UDP? I know using the option –master-port, one can easily specify the TCP/IP port number where the master is listening. Is there any way to use UDP for replication or for clusters to avoid TCP/IP overhead? I believe MySQL uses TCP/IP for all its operations over the Internet. Can someone kindly confirm or deny that? Also, What are the options if someone insists on using UDP to send data to MySQL for their application?

Can MySQL triggers be used to update a database on a separate host? My research so far tells me “no” as there is no way (that I see) to connect to a remote machine using triggers? Am I right in assuming that?

Am I right to assume that “circular” replication is more suitable for a mission critical, large scale application where 100% data mirroring needs to be ensured?

Many thanks for your valuable comments/suggestions/insights.

Frank

MySQL DBA Interview

Yesterday I gave an hour long interview to a firm that ranks top 10 within it's industry. Out of the many people interviewing me, most of them were pro-MS SQL server even though the company had everything on MySQL.

The interviewers repeatedly asked me about how will I migrate to MS SQL server from MySQL? I on the other hand kept pushing for MySQL telling them that there is no need to go with MS SQL server as they can do everything they want with MySQL. I did ended up telling them about how I would go about migrating in the end.

I felt a concern that for some reason they weren't sure about MySQL's future and the acquisition of Innobase by Oracle (Heck they didn't know about BDB's acquisition).

Anyway, I am currently waiting to hear from them.

Frank

Oracle eating up the Open Source World?

After buying PeopleSoft for $11.1 billion and Siebel Systems for $5.85 billion dollars, Oracle has finally bought Sleepycat, the makers of Berkeley DB (or BDB) for an undisclosed amount.

According to the media reports, Oracle has spent nearly $20 billion in acquisitions in the last 2 years. That's 10 billion dollars a year and close to a billion dollars per month.

The troubling thing is the fact that "stopping" is not in Oracle's dictionary. The company is also after Zend and JBoss.

From what I see Oracle felt very threatened by MySQL and Open Source, and as a result is trying to drive MySQL out of the market.

Is this Game over?
As my fellow blogger, Markus has been pointing out for some time now, MySQL needs to take matters in its own hands. MySQL DBAs worldwide feel threatened by Oracle's acquisitions. I completely agree with Markus that MySQL needs to see "an own and independent full featured storage engines as one of the very highest priorities for MySQL's future"

I hope Zak is right in betting that the 18.5 million dollars recently secured by MySQL will go "towards building a transactional database engine that they hold complete copyright on".

My other blogosphere friend Ronald Bradford is also calling for MySQL to have it's own transactional engine. He, like many of us is "personally concerned" and says:

In my opinion [there] should be a bold announcement from MySQL now. In stating the development and release of a new Transactional Storage Engine this year, and then not even mentioning InnoDB and BDB, they are downplaying the Oracle buy in, and emphasising a true Open Source Company option. In no means say they are no longer supporting InnoDB and BDB, but if the media exposure from MySQL continues to mention them, then it’s going to bleed into some reference back to Oracle.

Jeremy says that Oracle is following Yahoo!'s style in a different industry.

This leads to the obvious question: What is Oracle up to? Are they trying to do to Open Source Databases what Yahoo appears to be doing to Web 2.0 companies?

While I get very happy when Yahoo! buys a company, Oracle's acquisitions mostly make me sad especially because I see them targeting MySQL.

And don't you guys think 18.5 million dollars is a really low amount for financing? MySQL needs to pitch for more investment so it can compete with giants like Oracle and Microsoft. May be by introducing a new licensing model based on the amount of revenues that a user's company generates. So company's like Google who are generating billions from the MySQL platform, would at least pay a couple hundred million every year. Thoughts about why and why not?

As a MySQL DBA, I have never been so unsure of its future, unless MySQL AB really gets aggressive in its business.

As soon as my upcoming projects pick up or I get a job, I plan to devote a lot of time in MySQL development. Like Ronald says, it's a call to arms!

OsCommerce w/ MySQL Store Transfer

Today I was asked to transfer an OSCommerce store with MySQL for a client.

First of all, we need to login to the old server and archive all the data. So navigate to home directory and issue the following command

tar -cvf old.tar ./

Now FTP this directory to the new server and untar it.

tar -xvf old.tar

Now we need to dump MySQL databases. Login to old server and issue:

mysqldump -uusername -ppassword -a --databases database1 database2 database3 | ssh user@newhost.com "mysql -uusername_new -h hostname_new.com -ppassword"

The hostname is needed in the above command if you cannot connect using localhost.

Q: Does anyone know how I can dump a database that has a "." in its name? I tried escaping it with a backslash but I still get errors while selecting it.

At this point, we need to be able to preview the new domain name under a URL. Since the project for which I am doing this for surprisingly doesn't has a preview URL, I must wait for the client's hosting company to setup a preview URL so I can continue with the transfer.

Part 2

Ok, so I just got the notification that a dedicated IP has been assigned to the site. I can therefore continue with the migration.

I first transferred the files to the public folder where they can be accessible.
I also changed my hosts file on my laptop to take advantage of temporary routing (so I can get the store to work without having to change DNS).

After the files were transferred, everything looked fine at first glance but when I tried the advanced search, I got the following error.

1054 - Unknown column 'p.products_id' in 'on clause'

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%disney%' or p.products_model like '%disney%' or m.manufacturers_name like '%disney%') )

Oops, what's going on here? Checking the version revealed that the client had MySQL 5 on new server and MySQL 4 on the old one. I had luckily asked about this issue previously and had received some good comments on how to fix the issue. On top of that I found the change log of OSCommerce that documents the changes that need to be made to make OScommerce compatible for MySQL 5.

So I opened up the advanced_search_result.php and modified as follows:

/*Start for MySQL5 */
$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id";



if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {

if (!tep_session_is_registered('customer_country_id')) {

$customer_country_id = STORE_COUNTRY;

$customer_zone_id = STORE_ZONE;

}

$from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left join " . TABLE_ZONES_TO_GEO_ZONES . " gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')";


}



$from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";



$where_str = " where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id ";
/* END change for MySQL 5*/

IN index.php I changed

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";

to

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";

and changed

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

to

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

and changed

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

to

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

Changed

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

to

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

To be continued...

If you have any questions, suggestions and/or comments please feel free to leave a comment.

Frank

Credits:
OSCommerce change log

MySQL needs triggers and stored procedures

I am very happy to see Markus go in great detail about why the "new" features such as stored procedures and triggers are needed in MySQL. Many thanks to all those who took the time to comment.

I had asked the same question on Ruby on Rails list and following are the responses I received.

Joe said:

I think it's more along the lines of keeping everything in one layer. See this:

Choose a single layer of cleverness
http://www.loudthinking.com/arc/000516.html

I'm sort of coming around to that way of thinking. I switched from MySQL to PostgreSQL about two years ago and loved it. But now it DOES sound appealing to just handle stuff in Ruby/Rails instead of switching to pgsql for sprocs, constraints, triggers, etc. and then handling db errors that arise from them.

Pat replied to Joe saying:

This is one case where I have no problem being unDRY. The fact is that for the apps I write, my Rails app isn't going to be the only point of access to the DB. Most of the time the DB is there long before the Rails app anyway. So there are two ways of handling things that need checks, in my mind:

1. Write code to handle errors that pop up from the DB
2. Code the checks into the application itself

Sometimes it's easier and makes more sense to do #1. In Rails, you can achieve nearly all of the checks with one line of code, and everything else with just a few. You never even get to a point where the database would create errors.

Anyway this is getting slightly OT. Basically I just mean that I've got no problem having two layers of cleverness. It's necessary for me to have a clever database, and easy for me to have a clever Rails app. The Rails app never sees the DB cleverness, which only exists because other languages and people that access the DB aren't as bright.

Joe replied back:

Yeah, I'm redundant in this too. I've started using runner scripts more to interact with models, but I still do a lot of interfacing via psql and phppgadmin and not having data integrity protections in place at that level would be playing with fire. They haven't interfered with Rails or gotten in the way - things like validates_uniqueness_of happen before a unique key in postgresql can generate an error.

Joe

As far as my thoughts are concerned, I completely agree with Markus above and those who have posted in the favor of MySQL having advanced functionality like Stored Procedures.

MySQL is not "a little kid" anymore and MySQL DBA's would like for it to be realized so folks like Lee Asher won't wrongfully bash MySQL for not having these features.


Frank

"Entirely uninterested in fancy features like stored procedures, triggers, and the like" : David Heinemeier Hansson

So David Heinemeier Hansson, the inventor of Rails was recently interviewed by Lenz Grimmer of MySQL AB.

I was very happy to see the interview on PlanetMySQL as just the other day I was talking about RubyOnRails on this blog and was surprised to see that no one posted a comment about Rails. Actually, I got only one comment about PHP which honestly made me very sad.

I am also very glad to hear about DHH's future plans of continuing to support Rails but not like MySQL AB supports MySQL (Not that I don't like MySQL's way).

On the other hand, being a DBA, I was kinda disappointed at David's following remarks:

"I love MySQL because its fast and easy for the simple stuff. I’m entirely uninterested in fancy features like stored procedures, triggers, and the like. So I hope nothing is being sacrificed while the 5.0 series chases these.

My advice would be to focus on enabling that simple feature set to go higher. Make clustering and other scaling techniques even easier. Perhaps baking in support for managing partitioned data sets."

I think stored procedures, triggers and events are very needed in MySQL. Hope David is not worried about competition from MySQL :)

MySQL gurus, I would love to hear your opinions on the above statement.

Frank

Welcoming the new Kruckenberg

I just wanted to take a moment to congratulate Mike Kruckenberg on his son (he's sooo precious).

It must feel pretty good to have your son born the same day as your birthday.

Congrats Mike!

Frank
P.S. Thanks Jay

Is this a MySQL bug?

Today, I received a message from a friend who is also a MySQL DBA and recently upgraded to MySQL 4 from MySQL 3.23:

I thought this might be useful for you:

I had two fields, say "a" and "b", both of which were unsigned int. For one calculation, I need to subtract b from a. Before upgrading, if b>a, the result would be negative as expected. After upgrading, if b>a, the result is something like 18446744073709551615. Of course, this sort of thing happens in c, but MySql apparently used to automatically cast so that the result of such calculations would come out as expected. Of course, changing the fields to signed int fixed the problem. Interesting, eh? Seems like this kind of thing could cause huge problems for some people when upgrading.

My question to MySQL gurus here: Is this a bug in MySQL 4? Why is MySQL 4 not "casting" it properly?

Thanks
Frank

Dear Jeremy

Hey Jeremy,
Today was the third time I tried to comment on your blog (about Amr's post) but was redirected to a "DSBL: Listing Data" page.

Since I don't have a dedicated IP at home, it seems you have my ISP (Alltel.net) blocked.

Just wanted to say please open up your comments to legitimate commenters like me and please don't make us pay for someone who has used this IP in the past.

IP: 151.213.158.103
State: Listed
Listed in unconfirmed (unconfirmed.dsbl.org): yes
Listed in singlehop (list.dsbl.org): yes
Listed in multihop (multihop.dsbl.org): no
Reverse DNS identifies server as: h103.158.213.151.ip.alltel.net

Can you manually whitelist me or give me a way to comment on your blog? Thanks

Frank
softwareengineer99 at yahoo dot com

Innodb or MyISAM? What's your preference?

I have been reading a lot about Innodb and MyISAM recently. MyISAM offers speed whereas Innodb offers reliability. MyISAM is fast but with no transactions support, Innodb has certain, but very important limitations.

I hate to ask it like this, but what do you prefer for a high volume application with heavy inserts/updates/selects? In your regular job (and projects), do you go for the speed or the reliability? or do you try to find a balance between both by having tables of both kinds?

I am just interested in hearing your thoughts (and igniting a discussion) as to which one is better in the long run?

I have personally used MyISAM and outsourced everything else to my application so far.

Thanks
Frank

APF, Iptables, hackers and some random thoughts

So I logged in yesterday to a server recently handed over to me. Upon login, I found the following report of hackers trying to break-in using brute force attacks.

Username Failures Maximum Latest
root 16640 0 Thu Feb 2 -0600 on 209.160.64.
bin 24 0 Thu Feb 2 -0600 on 83-103-110-
daemon 68 0 Thu Feb 2 -0600 on 206.40.148.
adm 94 0 Thu Feb 2 -0600 2006 on ssl.ctssg.c
lp 58 0 Thu Feb 2 -0600 2006 on 83-103-110-
sync 20 0 Thu Feb 2 -0600 2006 on 83-103-110-
shutdown 20 0 Thu Feb 2 -0600 2006 on 83-103-110-
halt 20 0 Thu Feb 2 -0600 2006 on 83-103-110-
mail 68 0 Thu Feb 2 -0600 2006 on 59.3.116.92
news 87 0 Thu Feb 2 -0600 2006 on 125.251.123
uucp 18 0 Thu Feb 2 -0600 2006 on 83-103-110-
operator 23 0 Thu Feb 2 -0600 2006 on 83-103-110-
games 73 0 Thu Feb 2 -0600 2006 on 125.251.123
gopher 12 0 Thu Feb 2 -0600 2006 on 192.38.8.20
ftp 334 0 Thu Feb 2 -0600 2006 on 211.154.205
squid 55 0 Thu Feb 2 -0600 2006 on 219.84.168.
named 99 0 Thu Feb 2 -0600 2006 on 125.251.123
mysql 470 0 Thu Feb 2 -0600 2006 on 211.154.205
rpcuser 15 0 Thu Feb 2 -0600 2006 on 220.80.132.
rpc 12 0 Thu Feb 2 -0600 2006 on 192.38.8.20
apache 260 0 Thu Feb 2 -0600 2006 on 83.228.113.

As if these idiotic hackers (and spammers) aren't enough to "boil your blood" as Ronald says it, the firewall on my sever won't let me connect to another server of mine.

Since yesterday, I have been trying to troubleshoot a faulty APF/iptables configuration on one of my servers. The configuration was setup by a colleague of mine and nonetheless, it brought more headaches than anything I could've imagined (Sorry man, but you gotta pay more attention to rules when adding them).

The problem was that though I could ping my server A from server B, I couldn't connect to it using SSH nor using MySQL. I had explicitily allowed connections from server B and had added server A to hosts.allow file, to no avail.

After more than 16 hours of pulling my hair with the old firewall configurations, (with more than 2000 rules), I finally decided that it was time to try upgrading APF to the most recent version.

Here are some of the helpful commands for reference:

List iptable rules

iptables --list

List IP addresses and port numbers in numeric format:

iptables --list -n

IP tables help refernce

iptables -h

View current iptables rules

cat /etc/sysconfig/iptables | less

View current APF rules ( I hate that the output is shown in pico instead of vi/vim)

apf --list

To fix, I upgraded apf and edited hosts.allow

sshd, imapd, mysqld:XXX.XX.XXX.XXX.86

Once again added the server to /etc/apf/allow_hosts.rules by allowing it explicitly

apf -a XXX.XX.XXX.XXX.86

and using IPTables (if you have no firewall)

iptables -A INPUT -p all -s XXX.XXX.XXX.XXX.86 -j ACCEPT

then reloaded APF and iptables.

To block an IP explicitly using APF, you can use (hacker's address):

/etc/apf/apf -d XX.141.48.33

The lesson I learned is that its much better to let go of your old firewall and use a new one if the current configuration (set up by someone else) is not working.

If you use Firefox and still don't have Jennifer Madden's visual extension installed, do so now as I highly recommend it.

Jay is talking about holding a regional meeting of MySQL users in Columbus, OH. I wish they would come near Atlanta, GA or Greenville, SC soon so I could get to meet all the cool folks.

I really liked Stewart's comments on having an integrated place for comments and documentation with a nice interface. May be if I get time, I will jump on the idea.

Mike has made available Sakila Schema 0.3 which can be downloaded here. Mike, your hard work on the documentation and guiding the community is greatly appreciated.

What a day indeed!

Frank
P.S. If anyone wants to brainstorm on setting up a next generation spam and hacker intrusion detection, blocking and reporting system with MySQL, I am all ears.

Resources: hosts.allow, SSH for TCP wrappers, RHCE Security Guide, IPTables tutorial