My quiz about SQL mode was a bit more difficult than I expected.
The question was which ones of the following statements are
affected by a change from the default SQL mode to ANSI
mode.
I used the INFORMATION_SCHEMA tables for examples throughout.
This is to make it easy to try for yourself, without needing to
create tables etc. If you use the Query Browser to try this out
you should beware that setting the sql_mode might not work, since
the Query Browser by default creates a new database connection
for every statement. The easiest way to check this si to use the
'set global sql_mode' command, but you should not do this on a
production database, since strange things might happen....
If you want to read the documentation, you find it here.
Now for the solution...
a) SELECT concat(table_name,' …
The NetBeans IDE has simplified the steps to deploy JRuby on Rails application on GlassFish. This blog explains the steps and is an update to screencast #web6.
- Download the install the latest NetBeans 6 Nightly. I downloaded the Ruby pack by
clicking on the "
Download" button in the Ruby column. - Create a new Rails Application
- Right-click in the Project window and select "
New Project...". Take all the defaults as shown below:
- Click on …
- Right-click in the Project window and select "
Returning to my post about memory consumption with Stored Function at this moment I want to annul that post. Trying to repeat described behavoir I cannot repeat that again and probably there was something wrong with the whole box, not MySQL. I apologize if it was confusing. Although I am almost sure this topic will popup somewhen.
I had some fun yesterday with some odd performance problems. So I did a run with oprofile and got this:
561612 25.0417 /lib64/tls/libc-2.3.4.so memset 429457 19.1491 /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux clear_page 214268 9.5540 /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux do_page_fault 144293 6.4339 /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux do_no_page 94410 4.2097 /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux buffered_rmqueue 64998 2.8982 /lib64/tls/libc-2.3.4.so memcpy 59565 2.6559 /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux __down_read_trylock 59369 2.6472 /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux handle_mm_fault 47312 2.1096 /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux free_hot_cold_page 39161 1.7462 /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux release_pages 39140 1.7452 …[Read more]
I was asked a question today, “DATETIME vs TIMESTAMP. When to use which & why?”
It’s a good MySQL introduction question, here are some general considerations for choosing one.
Do you need Date values other then an EPOCH value? (i.e. before
1970) If the answer is yes, then DATETIME is required.
If you do not however, then TIMESTAMP is the best choice for a
few reasons.
1. The TIMESTAMP columns uses 4 Bytes to record it’s value, while
DATETIME uses 8 bytes. Using the smallest storage is always a
best practice for all columns.
2. The TIMESTAMP column supports the CURRENT_DATE syntax in the
CREATE TABLE command. This enables the column to have a default
value for INSERT or for UPDATE, but not both. Indeed this is the
only data type that allows for any default value that is not a
constant.
3. All date functions (at least the ones I use) work equally as
well with TIMESTAMP and DATETIME.
I …
[Read more]Today, I am in Boston for training, I am shadowing Ronald Bradford, another MySQL consultant. It is fun and I learn a lot although it is not always easy to follow someone else mind. But we needed to evaluate the performance and the stability of a SAN. Do do so, we have used the bonnie++ tool. The server has 32 GB of memory and the filesystem was mounted under /data3. The command line used was the following:
mkdir /data3/testbonnie; nohup bonnie++ -d /data3/testbonnie -s 65000 -n 10 -r 32000 -x 5 -u root -g root &
After more six hours… the results were the following:
cat nohup.out | grep proddb | bon_csv2txt
Version @version@ ——Sequential Output—— –Sequential Input-
–Random-
-Per Chr- –Block– -Rewrite- -Per Chr- –Block– –Seeks–
Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP
/sec %CP
proddb06 65000M 71639 84 67884 13 54084 7 60194 60 126326 7 572.7
0 …
SiteCrafting is in the process of phasing out some of our older servers, and as an added bonus, the clients hosted on those servers are getting a MySQL jumpstart, leapfrogging over 4.1 to go straight from 4.0.24 to 5.0.32. Tragically, it's not quite as simple as dump | import. This is what I get for bothering my bosses for a few weeks not long after coming aboard about how nice stored procedures, updatable views, and triggers could be."The wonderful thing about standards," a wiser person than me once said, "is that there are so many of them." That's not the whole of it, though. One good thing about standards is that there are certain features one can generally rely upon to work, translate, port, etc. Assuming one works within them, rather than taking advantage of loopholes allowed by their not-entirely-compliant-but-we're-getting-there-and-anyway-isn't-this-way- easier-and-faster software. When people don't (and I'm not entirely innocent here), you …
[Read more]We purchased MySQL support, and I installed the advisor software that comes with it, and checked out the tuning advisors. I feel like I’m back in the days before the Oracle wait interface, tuning everything with hit ratios, etc, and basically guessing which changes might help what how much. (Tune, cross your fingers, start over, try again. Repeat.) Like before I had ever read “Optimizing Oracle Performance” by Cary Millsap, or “Oracle Insights:Tales of the Oak Table”.
My #1 wish for MySQL at this point would be that version 6 would be instrumented, so that we could tune using wait events, to identify actual wait times and what contributed to them by how much.
For now, I get recommendations like “Query Cache Has Sub-Optimal Hit Rate“, which is giving me flashbacks… (In our case, I don’t have to worry much about this in particular, and the advisors are good enough to let me know that I might “Evaluate whether …
[Read more]Whenever I decide to take a holiday I can usually guarantee that something cool will occur in the IT industry in my absence.
Now this holiday has been slightly different in many ways. I had to cancel my flight due to a sudden onset of Vertigo 1 day before I was supposed to fly. If like me you have never experienced Vertigo before, thank yourself lucky!. Having no sense of balance and intense room spin whenever you open your eyes is no fun at all, it took at least 2 days for me to stop being sick.
Having been able to actually use my laptop in the last couple of days I notice that the “cool thing” I usually miss on holiday turns out to be big trouble all over OpenSourceVille, the majority of which seems to revolve around licensing and intellectual property.
Who could miss the BSD vs GPL debate that I’m sure will rage for a long time after …
[Read more]Administrative note:
I had a bit of a mishap involving my hand and a glass door pane. The OurSQL podcast will be on hiatus for a few weeks as I recover. I injured my left hand, and I’m a lefty, so I’m typing one-handed these days. I apologize for the break in the show schedule, and hope you’ll be able to hear new podcasts about MySQL very soon!