tag:blogger.com,1999:blog-58562298169670353122012-01-25T13:25:59.223-08:00David Holoboffhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.comBlogger341100tag:blogger.com,1999:blog-5856229816967035312.post-81816695129440779962012-01-24T12:45:00.000-08:002012-01-24T12:48:42.300-08:00

Problem: Find out if an entire string is numeric.  (Just like the is_numeric function in php.) Problem, Part II: Most online resources show how you can find out if part of your string is numeric. Solution: SELECT str FROM tbl   WHERE str REGEXP('(^[0-9]+$)');Give it a whirl! For those who would like to view some test results, here goes: mysql> SELECT str FROM tbl; +-----+ | str | +-----+ |

David Holoboffhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com2tag:blogger.com,1999:blog-5856229816967035312.post-28905491159337004942011-06-03T07:37:00.000-07:002011-06-03T07:37:16.961-07:00

Problem: Pin down redundant queries by finding out which tables are being selected from the most. I like general query log parsing tools, such as mk-query-digest or mysqlsla. However, there are times when I am on a server as a dba with limited access and can only read files in /tmp.  And other times I just need to get something done quickly. So what did I do to find out the most selected

David Holoboffhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com2tag:blogger.com,1999:blog-5856229816967035312.post-57754721994166684262011-04-27T10:03:00.000-07:002011-04-27T10:03:15.069-07:00

My Ignite Talk (5 minute talk, 20 slides, 15 seconds per slide) at the MySQL Conference about Data Visualization was picked among three for "the Best of Ignite".  I would say I barely scraped into third spot, and it was inspiring giving a talk after Baron Schwartz gave his at the keynote on the last day of the conference. The other two talks were "Scale Fail" by Josh Berkus (hilarious, must see

David Holoboffhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-33006068541115215092011-02-02T08:57:00.000-08:002011-02-02T08:57:59.787-08:00

This is the first time this has happened to me, so I got excited about it until I noticed that this command has been with us from nearly the very beginning (ie MySQL 4). The Situation Master Server contains db1 Slave Server contains db1 and db2 Slave Server needs db1 from the Master to replicate to db2 The Solution On my.cnf on the slave server: --replicate-rewrite-db=from_name->to_name Thus,

David Holoboffhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com3tag:blogger.com,1999:blog-5856229816967035312.post-29234622892171370392011-01-11T13:17:00.000-08:002011-01-11T13:46:11.223-08:00

There is nothing that MySQL 5.1 explicitly performs to log user connections, but there is a combination of MySQL commands that can log user connections without having to turn on the general query log. I repeat: you do not have to turn the general query log on to make this work! Would you like to find out what that is? With the help of Baron Schwartz's post on http://www.xaprb.com/blog/2006/07/

David Holoboffhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com5tag:blogger.com,1999:blog-5856229816967035312.post-55842051611720771812010-02-19T14:40:00.001-08:002010-02-19T15:00:05.910-08:00

Are you tired of waiting an hour or so performing a LOAD DATA INFILE statement involving a file with millions of rows?If so, and you have enough money to pay for 3 monthly installments of $5.99... (Belgian chocolate will substitute nicely) -- you TOO can be a hero in your office and go home early for the weekend. :)How? Disable keys. Load data infile. Enable keys.ALTER TABLE

David Holoboffhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-43141968929469747162010-01-08T13:33:00.000-08:002010-01-08T14:02:53.819-08:00

Yes, it has been a while since I last posted, so we'll see how this year goes...Just the other evening I came across something that I should have done much sooner. Run SHOW PROCESSLIST every second without hitting "ENTER" repetitively.The Setup1) Create a temporary user with a non-sensitive password on localhost with SUPER privs. Yes, this crazy, but I did say *temporary*, right? mysql> GRANT

David Holoboffhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com4tag:blogger.com,1999:blog-5856229816967035312.post-38110894159503957812008-11-19T08:04:00.000-08:002008-11-19T08:34:11.692-08:00

An excellent question was posted in a MySQL group in LinkedIn the other day that I had to post it here.What is your "Top 10 List for Programmers Working with MySQL"?I started mine... Let me know your thoughts on my list, and feel free to add other items to the list. I interface with programmers on a daily basis, and our experience lends us to help them in certain ways, but I think a "Top 10"

David Holoboffhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com2tag:blogger.com,1999:blog-5856229816967035312.post-25075496143231404332008-10-15T14:59:00.000-07:002008-10-15T15:46:44.245-07:00

After receiving an email about talking with Robert David, Director of Sales at KickFire, I checked out recent news on KickFire, as there had been little written about this company aside from the big splash they made at the MySQL Users Conference back in April 2008.And, lo and behold, there was a piece of news, posted on October 14 - Kickfire Enters Into MySQL Enterprise Agreement With Sun

David Holoboffhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com2tag:blogger.com,1999:blog-5856229816967035312.post-7467324608236616832008-10-10T08:48:00.000-07:002008-10-10T14:46:45.509-07:00

SetupWe have one InnoDB file per table on our database, which was set with the following option: innodb_file_per_table.This allows me to portably transport Innodb files on a system level with minimal hassle, and is much faster than mysqldump, as these tables are several GB each.ProblemWhen transporting an .idb file from one server to another, I ran into the following error:ERROR 1030 (HY000): Got

David Holoboffhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com3tag:blogger.com,1999:blog-5856229816967035312.post-705500855070541682008-09-04T10:12:00.000-07:002008-10-11T14:08:05.688-07:00

Yes, it has been too long since I last posted on this blog (3 months) - that's my apology; let's move forward.There's too many interesting MySQL bits and pieces that I still come across my desk that I cannot help but post these fantastic learning opportunities to my blog. Obviously, you can read about all things interesting from PlanetMySql.org, but I hope that my blog will help sift people

David H.noreply@blogger.com1tag:blogger.com,1999:blog-5856229816967035312.post-80546842658547483442008-06-27T14:21:00.000-07:002008-10-11T14:48:57.752-07:00

My last post on June 10 was about when MySQL received queries from php scripts that looked like this:mysql_select_db = ('database_I_DO_want_to_replicate', $link);$sql = "INSERT INTO db_I_DO_NOT_want_to_replicate.repl_table (a,b,c) VALUES (1,2,3);";-- and thus could not pass this over to the slave unless I set Replicate_Wild_Ignore_Table value IN ADDITION to Replicate_Ignore_DB as such in my

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-66319549472742413292008-06-10T07:21:00.000-07:002008-10-11T14:11:23.060-07:00

We recently moved some databases to different servers, and I altered our mySQL slave configuration files to take into account some of the databases we wanted to replicate (Replicate-do-db) and others that we wanted to ignore (Replicate-ignore-db) -- each of these server cases were mutually exclusive.All went well, until I found a peculiar error:Error 'Table 'db101.table101' doesn't exist' on

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-23745430485748910672008-05-07T14:48:00.000-07:002008-05-07T18:29:28.372-07:00

This is a great little sql statement that packs a nice punch for the number crunchers out there...A disclaimer first: make sure you don't do this on a live OLTP table with millions of rows unless you want your customers to wait. Thus, run it on your slave :).Onto business...The Request"I need to know the number of people who made one purchase, two purchases, three purchases, and so on. Right

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-8318816963504613832008-04-22T13:42:00.000-07:002008-10-11T14:12:09.830-07:00

Wow... I had a great time at the annual MySQL Users Conference in Santa Clara.Highlights:Received my CMDBA designation. Woohoo!Sun announced the possibility of an open source mobile phone;All day mysql proxy tutorial with Jan Kneschke and Giuseppe Maxia;Amazon's Power of Infrastructure as a Service - great premise; glitches remain;Great food and a nifty side trip to Santa Cruz;MySQL Workbench -

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-43255925000395419622008-03-14T07:43:00.000-07:002008-03-14T09:40:53.474-07:00

Sometimes technology gets in the way of getting things done, and us tekkies must do what we can to make our data warehouse work for the purpose it is intended. Ralph Kimball makes 10 good points about the common pitfalls to avoid when implementing a data warehouse, as listed in his book "The Data Warehouse Toolkit", published by Wiley.Become immersed with technology rather than the requirements

David H.noreply@blogger.com1tag:blogger.com,1999:blog-5856229816967035312.post-8333405359146773522008-03-07T10:59:00.000-08:002008-03-11T13:03:58.185-07:00

Yes, it has been a while since I added an entry in my blog. I have been working on creating a dynamic data warehouse system reliant on the traditional LAMP stack (and a very nifty graphical plug-in - please comment below if you wish to know what it is!).Firstly, I must make these qualifications before you read further:I consider Bill Inmon and Ralph Kimball the pioneers of data warehousing; Data

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-21767158635554822102008-02-13T07:55:00.000-08:002008-02-13T08:22:29.174-08:00

***All point news bulletin***Do not ever use NOW() - `datetime field`! Broadcast this from the mountaintops of open source to the valleys of companies who dabble in it.Instead, use the following:TIME_TO_SEC(TIMEDIFF(Now(),`datetime field`))There are other ways to do the above, just as long as you do not use NOW() - `datetime field`.Wanna see proof?mysql> SELECT RIGHT(NOW(),8) `Now`, RIGHT(

David H.noreply@blogger.com1tag:blogger.com,1999:blog-5856229816967035312.post-18140953644527000582008-02-06T11:29:00.000-08:002008-02-06T12:03:23.169-08:00

When your company decides that "it is time to build a data warehouse", what thoughts come to mind?1) A magical fairy ice cream land where data is presented in chocolate shells for everyone to digest perfectly;2) A big literal warehouse in the industrial section of town with rusty old containers;3) Another place to put data, which means another place for you to track and monitor additional

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-27449295037368599232008-01-30T08:38:00.000-08:002008-10-11T14:12:45.922-07:00

I have seen too many date fields that have been named after reserved key words that I need to make an entry for this... It's like naming your kid "Kid". Wait a sec... that's happened many times before as well.I will make a "Proper Naming Conventions" page for mySQL after checking a few more sources first, but this date naming issue cannot wait another day.Thus..First Rule: Do not use a reserved

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-90427987875089922942008-01-23T07:22:00.000-08:002008-01-23T20:17:26.973-08:00

I really like the 21st century open source technology community. 20 years ago, if a company bought out another company, everyone would have their own island of opinions and response, and you would not know if you were on-track or even if the publicized reports were on-track either.Today, it is amazing to see the responses around the boards and blogs, which seems to keep the mainstream reports

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-40165429491530066872008-01-15T08:09:00.000-08:002008-01-15T09:19:43.130-08:00

There was only so much I could do for my 1976 Mustang. I did not have money to put an Edelbrock head in, so I spent what little I had in a new stereo, a tinted back window, a new muffler that increased the neighborhood noise level a notch, and a couple of other random parts. In the end, it really did not go any faster, and I was $650 poorer.I liken my experience with SELF JOIN to my 1976

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-16215306936189572202008-01-07T07:48:00.000-08:002008-01-09T10:34:39.303-08:00

This is a continuance from my previous entry on reversing an email address to search for a domain. Minutes after I ran the following query,SELECT COUNT(1) FROM rv_emails WHERE rv_email LIKE REVERSE('%msn.com');I was asked to do a count of the top 50 domains in our table of email addresses.So, I simply changed my previous route by performing the following steps:1) Add a 'domain' column to my

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-30410406062881332982007-12-18T13:14:00.000-08:002007-12-18T14:33:48.561-08:00

I like working at companies where there are "problems related to success". My colleague and I from a former company that I worked for in LA talked about two types of problems faced by business:Problems related to failure - the problems of having your doors open for business but few people know about it;Problems related to success -the problems of having so many people come through your doors

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-13704714166070661332007-12-13T14:10:00.000-08:002007-12-18T14:35:52.232-08:00

I like driving. It relaxes me. I especially like driving in the Carolinas versus driving the freeways in Los Angeles. In LA, you have to intentionally calm yourself down before going to the on ramp of the 101, 5 or the 10. Here, in South Carolina, it is possible to drive highway speeds on the highways (in LA, it is possible, but only at night time or early in the morning).When I heard that

David H.noreply@blogger.com1tag:blogger.com,1999:blog-5856229816967035312.post-38289306859577081462007-12-06T12:27:00.000-08:002007-12-06T14:18:21.349-08:00

I was reminded the other day how SQL Server was annoying in regards to table structure changes. All I wanted to to was copy a table (with structure intact - keys, dependancies) with its data.How hard could that be?MS SQL in ActionFirst, I needed to do a complete DDL on TableOne and replace every instance of the word "TableOne" with "TableTwo". It works, even though it is bulky and takes time,

David H.noreply@blogger.com1tag:blogger.com,1999:blog-5856229816967035312.post-24503127789387852892007-11-30T14:20:00.001-08:002007-12-04T13:26:49.980-08:00

The Price is MySql with Jay PipesQuick! Pick a curtain! Do it now!Curtain #1:SELECT zip_code FROM table_name WHERE LEFT(zip_code,3) IN (123,456,789);What's under curtain #2 you ask?Well, I'm glad you asked! It is...ALTER TABLE table_name ADD COLUMN zip3 INTEGER UNSIGNED ZEROFILL NOT NULL, ADD INDEX idx_zip3 (zip3);What?? Another column and another index? My table already has 7,351 columns?

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-49579046033304641332007-11-20T13:49:00.000-08:002007-11-20T14:18:54.673-08:00

Table joins are fun and useful, but did you know that there are circumstances that a php loop will beat out table joins?For those of you who already know this, feel free to return to www.PlanetMySql.com. But for the rest of us, read on...The Setup (fictional, but mimics my production environment)I have two tables, employees and termination_info. Employees has 150,000 records and

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-8675322353319386762007-11-16T07:14:00.000-08:002008-10-11T14:59:57.912-07:00

Quick... You have to let everyone (boss, biz dev, customer service, and random bean counters) know why everything is moving slowly! Of course, rarely do people define what "everything" is, and what type of slowness is occuring. But, in the face of customer service agents that cannot work because their pages will not render, generally all eyes are on the famed-dba-of-the-minute.So, with 7 people

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-55244888283473488252007-11-02T12:17:00.000-07:002007-11-02T13:00:13.991-07:00

Many times DBAs work with data sets that are not the most optimal (in their opinion), and I recently had the pleasure of working with a table that did not have a unique identifier. And I needed to get unique results from this table, as I would need to do this query in different forms in the future, without selecting records from the past.Caveat: I could not alter the table structure yet; I

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-70472960098375546732007-10-30T07:18:00.000-07:002007-10-30T07:45:08.689-07:00

This utility written by Kevin Burton is worth checking into - even though it is a first version and needs to be modded for your purposes (in my case I had to alter the local mysql connection string). However, in any case, this is going to be needed by us DBAs in the future, as we manage and build more and more slave servers.This is what the script does, in Kevin's words:This is a script to

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-8777770006738584242007-10-25T12:00:00.000-07:002007-10-25T13:19:25.161-07:00

Google seems to be everybody's favorite girlfriend these days. We might even replace the antiquated statement "Midas touch" with "googlitis", a condition you get when google looks in your direction. Your valuation goes up even if google does not become a successful bidder (MSN + facebook = $15 billion dollar valuation), and everybody wants to be your best friend, even if you are politely

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-20603631042366988872007-10-19T08:01:00.000-07:002007-10-25T08:50:19.858-07:00

OK... This is my second entry on this blog. And it is about something I want every mysql/php newbie to know at the starting gate:DON'T USE CHAR(15) FOR IP Address fields in mysql!Well... what about the periods in ip addresses, you say?Well... mysql has a FANTASTIC way of solving this issue! Convert the standard ip address into an unsigned integer!ok... at first it sounds a little cryptic, but

David H.noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-55755390627736749792007-09-05T08:23:00.000-07:002007-09-05T12:16:14.640-07:00

This is the query that just got me to thinking that I should blog about mySQL hints, tips, and ramblings. So... thinking turned into action and now I have a blog! The next step is to be able to keep it going so that it becomes useful to mySQL database administrators that are concerned about performance of mySQL and also need make sure that queries are written well.Now... onward and forward...

David H.noreply@blogger.com0