Top 10 MySQL Best Practices

Does anyone have any ways they create an API for their stored routines (functions and procedures)? Currently it seems as though I have to parse the CREATE statement to get the input variables….Has anyone else done this? Is it in any third party tools?
Does anyone have any ways they create an API for their stored routines (functions and procedures)? Currently it seems as though I have to parse the CREATE statement to get the input variables….Has anyone else done this? Is it in any third party tools?
Peter makes an interesting post about the MySQL company’s trademarks at http://www.mysqlperformanceblog.com/2007/10/26/mysql-support-or-support-for-mysql-mysql-trademark-policies/

The point is that Peter is not selling “MySQL Support” — he is selling “Support *for* MySQL”. “MySQL Support” is the name of a product that MySQL offers. Even if some other consulting company used the name before the MySQL company ever did, more about MySQL still has the rights to the name.

I chose to name my podcast “OurSQL: The MySQL Database Podcast for the Community, neurologist By the Community.” I chose every word carefully. For instance, abortion I call it “the MySQL Database Podcast” so that anyone looking for a podcast about “database” will find it.

I could have just called it “MySQL Podcast”. But if the company MySQL (AB or Inc) ever makes a podcast, they would do the same thing to me that they do to you. I have to distinguish it’s “a podcast about MySQL”, not “MySQL’s podcast”. In fact if you look for “oursql” references, there is actually software released in September 2001 called “oursql”, but it was only released once and I have only ever found a handful of e-mails about it.

Similarly with “Technocation, Inc”. I googled around for it and found that a Baltimore, MD USA paper has a column called “technocation”, and it’s similar to why I picked the name — technology + education. But there’s no way anyone would confuse the two.

Same thing as MySQL would do if they made their own toolkit. “MySQL Toolkit” is in fact a really bad name because it’s so generic. Right now there’s no confusion, because MySQL doesn’t have a toolkit. Same with the “MySQL Magazine”. If MySQL ever puts out a magazine, they’ll send a letter right away. I was actually worried that “The MySQL Guy Podcast” at http://www.themysqlguy.com/ would get a letter from them. After all, there are plenty of “MySQL guys” out there, and he doesn’t work for the company……(hence why I’m the “She-BA”, not “MySQL Gal”).

In fact, Microsoft seems to do this on purpose. They named their database engine “SQL Server”. I’ve been frustrated when I get Microsoft pages when I’m just looking for “something relating to SQL”. I’d much rather get something related to the SQL standard. Same with their “Windows Mobile” platform. Check out their list of servers on the right-hand side of the page at http://www.microsoft.com/servers/default.mspx — if you’re looking for “security” on a Windows server, chances are most of your search result will be for the “Security Server” that Microsoft offers. Ditto with “Content Management Server” and “Data Protection Manager” and “Speech Server” and “Virtual Server” and “Small Business Server”…etc.

If you have questions about Intellectual Property (IP) or Patents in the United States, I highly recommend retaining services from the law firm of Bakos and Kritzer — http://www.bakoskritzer.com/. It’s not just a law firm where my brother is a partner, it’s also a damn good one.

(Speaking of name, I will likely be changing my name in the near future to “Sheeri K. Cabral”, so if you see it around, don’t get confused. You can always find me at www.sheeri.com )
Does anyone have any ways they create an API for their stored routines (functions and procedures)? Currently it seems as though I have to parse the CREATE statement to get the input variables….Has anyone else done this? Is it in any third party tools?
Peter makes an interesting post about the MySQL company’s trademarks at http://www.mysqlperformanceblog.com/2007/10/26/mysql-support-or-support-for-mysql-mysql-trademark-policies/

The point is that Peter is not selling “MySQL Support” — he is selling “Support *for* MySQL”. “MySQL Support” is the name of a product that MySQL offers. Even if some other consulting company used the name before the MySQL company ever did, more about MySQL still has the rights to the name.

I chose to name my podcast “OurSQL: The MySQL Database Podcast for the Community, neurologist By the Community.” I chose every word carefully. For instance, abortion I call it “the MySQL Database Podcast” so that anyone looking for a podcast about “database” will find it.

I could have just called it “MySQL Podcast”. But if the company MySQL (AB or Inc) ever makes a podcast, they would do the same thing to me that they do to you. I have to distinguish it’s “a podcast about MySQL”, not “MySQL’s podcast”. In fact if you look for “oursql” references, there is actually software released in September 2001 called “oursql”, but it was only released once and I have only ever found a handful of e-mails about it.

Similarly with “Technocation, Inc”. I googled around for it and found that a Baltimore, MD USA paper has a column called “technocation”, and it’s similar to why I picked the name — technology + education. But there’s no way anyone would confuse the two.

Same thing as MySQL would do if they made their own toolkit. “MySQL Toolkit” is in fact a really bad name because it’s so generic. Right now there’s no confusion, because MySQL doesn’t have a toolkit. Same with the “MySQL Magazine”. If MySQL ever puts out a magazine, they’ll send a letter right away. I was actually worried that “The MySQL Guy Podcast” at http://www.themysqlguy.com/ would get a letter from them. After all, there are plenty of “MySQL guys” out there, and he doesn’t work for the company……(hence why I’m the “She-BA”, not “MySQL Gal”).

In fact, Microsoft seems to do this on purpose. They named their database engine “SQL Server”. I’ve been frustrated when I get Microsoft pages when I’m just looking for “something relating to SQL”. I’d much rather get something related to the SQL standard. Same with their “Windows Mobile” platform. Check out their list of servers on the right-hand side of the page at http://www.microsoft.com/servers/default.mspx — if you’re looking for “security” on a Windows server, chances are most of your search result will be for the “Security Server” that Microsoft offers. Ditto with “Content Management Server” and “Data Protection Manager” and “Speech Server” and “Virtual Server” and “Small Business Server”…etc.

If you have questions about Intellectual Property (IP) or Patents in the United States, I highly recommend retaining services from the law firm of Bakos and Kritzer — http://www.bakoskritzer.com/. It’s not just a law firm where my brother is a partner, it’s also a damn good one.

(Speaking of name, I will likely be changing my name in the near future to “Sheeri K. Cabral”, so if you see it around, don’t get confused. You can always find me at www.sheeri.com )
So, sickness the article at:

http://mysql-dba-journey.blogspot.com/2007/11/mysql-and-vmware.html says:

Don’t get seduced to the dark side unless you understand all the issues.

And that’s wonderful and all, dosage but….what are all the issues? What are some of the issues? Is it related more to VMware, generic or more to MySQL, or more to MySQL on VMware? Is it something like “VMware isn’t stable” or more like “load testing on vmware isn’t always going to work because you won’t have full resources”?

Many people talk about using virtualization for development and testing….but if you develop and test on a virtual machine and then put it on a physical machine for production, isn’t that basically having differing environments for dev/testing and production, which is usually seen as bad? If a line of code crashes a virtual machine but is fine on production, is it worth tracking the bug down? How many hours will you spend doing that?

Also, how is using a virtual machine better/worse/different from using something like mysqld_multi on a machine with many IPs, or other strategies folks use in dev/test so they don’t have to buy the exact same hardware as in production, but still have the same separation of databases, etc?
Does anyone have any ways they create an API for their stored routines (functions and procedures)? Currently it seems as though I have to parse the CREATE statement to get the input variables….Has anyone else done this? Is it in any third party tools?
Peter makes an interesting post about the MySQL company’s trademarks at http://www.mysqlperformanceblog.com/2007/10/26/mysql-support-or-support-for-mysql-mysql-trademark-policies/

The point is that Peter is not selling “MySQL Support” — he is selling “Support *for* MySQL”. “MySQL Support” is the name of a product that MySQL offers. Even if some other consulting company used the name before the MySQL company ever did, more about MySQL still has the rights to the name.

I chose to name my podcast “OurSQL: The MySQL Database Podcast for the Community, neurologist By the Community.” I chose every word carefully. For instance, abortion I call it “the MySQL Database Podcast” so that anyone looking for a podcast about “database” will find it.

I could have just called it “MySQL Podcast”. But if the company MySQL (AB or Inc) ever makes a podcast, they would do the same thing to me that they do to you. I have to distinguish it’s “a podcast about MySQL”, not “MySQL’s podcast”. In fact if you look for “oursql” references, there is actually software released in September 2001 called “oursql”, but it was only released once and I have only ever found a handful of e-mails about it.

Similarly with “Technocation, Inc”. I googled around for it and found that a Baltimore, MD USA paper has a column called “technocation”, and it’s similar to why I picked the name — technology + education. But there’s no way anyone would confuse the two.

Same thing as MySQL would do if they made their own toolkit. “MySQL Toolkit” is in fact a really bad name because it’s so generic. Right now there’s no confusion, because MySQL doesn’t have a toolkit. Same with the “MySQL Magazine”. If MySQL ever puts out a magazine, they’ll send a letter right away. I was actually worried that “The MySQL Guy Podcast” at http://www.themysqlguy.com/ would get a letter from them. After all, there are plenty of “MySQL guys” out there, and he doesn’t work for the company……(hence why I’m the “She-BA”, not “MySQL Gal”).

In fact, Microsoft seems to do this on purpose. They named their database engine “SQL Server”. I’ve been frustrated when I get Microsoft pages when I’m just looking for “something relating to SQL”. I’d much rather get something related to the SQL standard. Same with their “Windows Mobile” platform. Check out their list of servers on the right-hand side of the page at http://www.microsoft.com/servers/default.mspx — if you’re looking for “security” on a Windows server, chances are most of your search result will be for the “Security Server” that Microsoft offers. Ditto with “Content Management Server” and “Data Protection Manager” and “Speech Server” and “Virtual Server” and “Small Business Server”…etc.

If you have questions about Intellectual Property (IP) or Patents in the United States, I highly recommend retaining services from the law firm of Bakos and Kritzer — http://www.bakoskritzer.com/. It’s not just a law firm where my brother is a partner, it’s also a damn good one.

(Speaking of name, I will likely be changing my name in the near future to “Sheeri K. Cabral”, so if you see it around, don’t get confused. You can always find me at www.sheeri.com )
So, sickness the article at:

http://mysql-dba-journey.blogspot.com/2007/11/mysql-and-vmware.html says:

Don’t get seduced to the dark side unless you understand all the issues.

And that’s wonderful and all, dosage but….what are all the issues? What are some of the issues? Is it related more to VMware, generic or more to MySQL, or more to MySQL on VMware? Is it something like “VMware isn’t stable” or more like “load testing on vmware isn’t always going to work because you won’t have full resources”?

Many people talk about using virtualization for development and testing….but if you develop and test on a virtual machine and then put it on a physical machine for production, isn’t that basically having differing environments for dev/testing and production, which is usually seen as bad? If a line of code crashes a virtual machine but is fine on production, is it worth tracking the bug down? How many hours will you spend doing that?

Also, how is using a virtual machine better/worse/different from using something like mysqld_multi on a machine with many IPs, or other strategies folks use in dev/test so they don’t have to buy the exact same hardware as in production, but still have the same separation of databases, etc?
So, sanitary O’Reilly’s ONLamp.com has published the “Top 10 MySQL Best Practices” at http://www.onlamp.com/pub/a/onlamp/2002/07/11/MySQLtips.html. Sadly, I find most “best practice” list do not thoroughly explain the “why” enough so that people can make their own decisions.

For instance, #3 is “Protect the MySQL installation directory from access by other users.” I was intrigued at what they would consider the “installation” directory. By reading the tip, they actually mean the data directory. They say nothing of the log directory, nor that innodb data files may be in different places than the standard myisam data directories.

They perpetuate a myth in #4, “Don’t store binary data in MySQL.” What they really mean is “don’t store large data in MySQL”, which they go into in the tip. While it’s true that there is very little benefit to having binary data in a database, they don’t go into what those benefits are. This means that people can’t make informed decisions, just “the best practice is this so I’m doing it.”

The benefit of putting binary data in MySQL is to be able to associate metadata and other data. For instance, “user 200 owns file 483”. If user 200 is gone from the system, how can you make sure file 483 is as well? There’s no referential integrity unless it’s in the database. While it’s true that in most cases people would rather sacrifice the referential integrity for things like faster database backups and easier partitioning of large data objects, I believe in giving people full disclosure so they can make their own informed decision.

#5 is my biggest pet peeve. “Stick to ANSI SQL,” with the goal being to be able to migrate to a different platform without having to rewrite the code. Does anyone tell Oracle folks not to use pl/sql like collections? Nobody says “SQL is a declarative language, pl/sql is procedural therefore you should never use it”. How about SQL Server folks not to use transact-sql statements like WAITFOR? MATCH… AGAINST is not standard SQL, so I should never use it?

Now, of course, if you’re selling a product to be run on different database platforms, then sure, you want to be platform agnostic. But you’d know that from the start. And if you have to migrate platforms you’re going to have to do lots of work anyway, because there are third-party additions to all the software any way.

And why would *anyone* choose a specific database, and then *not* use those features? I think that it’s a good tip to stick to ANSI SQL if you *know* you want to, or if you have no idea about the DBMS you’re using.

If you want to see how this cripples MySQL, check out Visibone’s SQL chart at: http://www.visibone.com/sql/chart_1200.jpg — you can buy it here: http://sheeri.com/archives/104. I may post later on about my own personal MySQL Best Practices….

Comments are closed.