Solutions

Using PHP function intval().

Code example: echo intval(”05″);
Returned value: 5

Using PHP function ltrim().

Code example: echo ltrim(”005″,”0″);
Returned value: 5

ShareThis

Last week I had the great opportunity to sit in on a DBA class session taught by George Trujillo. I know that most of the folks administrating and programming MySQL databases are self taught. Learning by the 'RFTM' has benefits but many lessons come directly from the education institution know as The School of Hard Knocks. And some knocks are much harder and larger than other.
Self-teaching is a valuable way to learn but a lot depends on the quality of the materials you can discover. The MySQL DBA class is well structured so that concepts are presented in easy to understand portions and build on each other to cover the information. It really beats trying to read the online documentation at three in the morning.

I was shocked and somewhat …

Large databases, long mysqldump times, long waits for globally locked tables. These problems basically never go away when you rely on mysqldump with –all-databases or a list of databases, as it dumps schemas serially. I’m not going to explain serial vs parallel processing here since that’s a larger topic. Suffice to say that in these days of multi-core / multi-cpu servers we only make use of one processor’s core when we serially export databases using mysqldump. So, I have a new script that attempts to alleviate those issues and now I need testers to provide feedback/improvements.

In order to keep some sanity when dealing with hundreds of database servers, the script takes care of the following:

1. low global locking time requirements: solved by parallel tasks / forked processes
2. backup file checking: with mysqldump files; it checks for “–Dump completed” at the end of the sql file …

Background Knowledge

Since MySQL v4.1 timestamp and datetime data types are formatted “YYYY-MM-DD HH:MM:SS”. Prior to MySQL v4.1 the timestamp was formatted as YYYYMMDDHHMMSS” and datetime formatted as “YYYY-MM-DD HH:MM:SS”. Refer to MySQL Reference Manual for further details.

The Unix timestamp differs from MySQL. Unix’s timestamp is a integer value of seconds since January 1, 1970. For further explanation of Unix timestamps refer to Wikiepedia or UnixTimestamp.com.

Solutions

In MySQL you can use Unix_Timestamp() function.

Query Example: SELECT …

Tomorrow, at 1pm EDT/10am PDT, I'll be giving a webinar on performance tuning MySQL for commercial application developers. The webinar is open to all participants. I'll be covering my normal Join-fu material but will try to tailor the talk to developers working on applications for the commercial market. I'm actually looking forward to the different questions this group might propose. I can't say I'll have answers to all the questions, but I'll certainly try my best! See you online tomorrow.

At the end of the Orlando meeting in January this year when the Sun acquisition was announced, I remember sitting next to MySQL’s co-founder David Axmark in the bus going to some evening event. “What do you want to do now, with so many opportunities opening up?” was my question to him, partly as his friend, partly as his colleague and partly as his line manager. David seemed very confident in the future of MySQL within Sun, but less sure about his own future role.

With that as a background, and knowing David since well over 20 years, I was not all that surprised to read his resignation letter, and in particular his reasoning for resigning:

I have thought about my role at Sun and decided that I am better off in smaller organisations. I HATE all the rules that I need to follow, and I also HATE breaking them. It would be far better for me to “retire” from employment and work with MySQL and Sun on a less formal …

I started my first day at Lycos yesterday. I've met a lot of really nice and talented people already and am enjoying myself. The environment here is very well-organized and very conducive for communication and exchanging ideas. It'll be a big change coming from a startup with a few people where I was *the* database guy to being at a larger organization (around 80 or so people) where they have different groups to handle all the different parts of the organization. Having people who set up servers so I can focus on development will be a big change. I don't mind doing that kind of work, but when you have do develop applications as well as manage the entire database-- anything that blows up on the database or network, you have to fix which can make it hard to focus. They have entire OPs group here to take care of that! I keep feeling like I have to worry about the database... post-DBA trauma. I'm sure I'll still get to keep my head on DBA type tasks …

As mentioned in a blog 2 years ago I worked on a new
partitioning feature:

I've been busy with many other things but now I've taken this
work a step forward and will most likely set-up a preview tree
of this feature in a short time.

The new feature adds the possibility to perform partitioning
on any type of column also for range and list partitioning
(has been possible for KEY partitioning all the time). It also
adds a new function to the MySQL Server and this function is
also a monotonic function which means it gets a nice treatment
of the partition pruning. This new function is TO_SECONDS which
works very similarly to TO_DAYS.

So here are couple of new cases of what one will be able to do:

create table t1 (d date)
partition …

Last week I mentioned that we had added a topic-based interface to the MySQL documentation to make it easier to go to specific topics, identified either by your needs, user type or technology.

It occurred to me at the end of the week that the information is just as useful when reading the documentation, so you can go direct to a topic within the online reference manual, rather than trying to work out what chapter it is in.

This works in all reference manuals, whether you are viewing the online HTML version:

Topics embedded in the reference manual, HTML

It also works in all the offline versions, including HTML and PDF, as here:

…

The interesting part is that this session is not a theoretical …