I have been at Mozilla nearly three months, and I used to blog a lot more than I currently do. A lot of the content I used to blog about I end up blogging and talking about in OurSQL: The MySQL Database Community Podcast. And I have also been getting used to the Mozilla firehose, as well as my own firehose of database projects that need to be done.

There are two very large projects that are time-sensitive that I am working on: migrating databases from an older data center to a newer one, and the impending public launch of the Mozilla Apps Store.

That being said, this week in Mozilla databases we have:

- migrated/improved/built our dev/stage databases for Socorro, our crash stats database.

- put monitoring on a newer backup server, after a random check showed replication had been stopped on one backup …

I recently wrote about SQL_MODE, and Ronald Bradford was quick to reply with his view on things. Although my post was a bit of a rant, and I think it was a necessary rant, it seems that Ronald and I largely agree on the overall matter of things.

Now today WebYog implies that I and Ronald want to get rid of it and replace it with more strict, rigid checks on data entering MySQL. Tell you want, however much I would like that, I am not stupid, I realize that is not practical. Rather, I say sure, keep SQL_MODE, but make it possible to enforce it and check it, and do not allow just about anyone with …

There are a lot of events scheduled for the next few months, we are excited about the international scope and the mix of big and small, paid and free events. This week we finish explaining the output of EXPLAIN. Ear candy is sshfs, at the movies is a presentation on joins and subqueries and how to optimize them.

Free OTN MySQL Developer Day, Singapore, Tuesday Feb 21st

Free OTN MySQL Developer Day, Paris, France, Tuesday Feb 21st

read more

This is my considerations/thoughts after reading:

http://karlssonondatabases.blogspot.com/2012/02/why-i-think-sqlmode-is-useless.html
http://ronaldbradford.com/blog/why-sql_mode-is-essential-even-when-not-perfect-2012-02-16/

It is not possible to remove SQL_mode and in particularly not to remove ‘non-strict’ modes without breaking a lot of applications. The problems occurs with a table like this

CREATE TABLE `test`.`strict`(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`col1` VARCHAR(10) NOT NULL,
`col2` VARCHAR(10),
PRIMARY KEY (`id`));

.. and an INSERT statement like this:

INSERT INTO `test`.`strict`(`col2`) VALUES …

MySQL is the world’s most popular open-source database and platform for millions of web applications – it’s critical but cumbersome to monitor.

Monitis’ MySQL monitoring provides three key benefits:

Insight
» 246 potential monitoring variables
» 21 aggregated, percentage-based metrics
» Adjustable thresholds to separate real issues from false alarms

Control

» Monitor entire IT universe from one dashboard
» Quick diagnosis & root cause detection

Simplicity
» Cloud-based means no need to install, update or maintain it
» Leaves you time to focus on more important things

For a FREE trial, go to:

…

With the weather taking extremes turns, technology is providing the much-needed warmth through the blogs. This Log Buffer Edition is sizzling with few of the hand-picked blog posts in Log Buffer #259. Oracle: Love Day has passed but love is still in the air and has been captured by Vanessa in a scintillating post. Tuning [...]

Here's how to update your fields value by adding a prefix on it. You will have to use some functions in your WHERE statement to filter only those records that you want to be updated.

UPDATE `database`.`table`
SET field_value = CONCAT('PREFIX', field_value)
WHERE field_value = var;

Have you ever encountered that after a successful logins to your mysql then the other day you are not able to login anymore using the root account? To fix it, follow below steps for linux users.

1. Stop Mysql
shell> /etc/init.d/mysql stop
2. Start Mysql Safe
shell> mysqld_safe -skip-grant-tables &

3. Login as root without password
shell> mysql -u root -p

4. Update root password using "UPDATE" statement
shell> UPDATE mysql.user SET password = PASSWORD('yourpassword') WHERE user='root';

5. Stop or kill the mysqld_safe instance

shell> pidof mysqld
shell> kill -TERM TheProcessID 
3. Start Mysql normally
shell> /etc/init.d/mysql start OR
shell> service mysql …

To get the current database size just by querying into your query browser or CLI from the INFORMATION_SCHEMA database in table TABLES.

SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;
Get the database free space

SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema;
Get the database last update ordered by update time then by create time.

SELECT MAX(UPDATE_TIME), MAX(CREATE_TIME), TABLE_SCHEMA
FROM `TABLES`
GROUP BY TABLE_SCHEMA
ORDER BY 1, 2;

I’m in the process of getting the tests passing for the 0.03 release of NIST::NVD::Store::SQLite3 wherein our hero imports the CWE data and cross-indexes it with CVEs and CPEs.

Follow along and suggest some patches. I’m developing on Debian Wheezy, but I would very much like input from devs on other platforms.

http://git.colliertech.org/?p=NIST-NVD-Store-SQLite3.git;a=summary

cjac@foxtrot:/tmp$ time git clone http://git.colliertech.org/git/NIST-NVD-Store-SQLite3.git
Cloning into 'NIST-NVD-Store-SQLite3'...

real    0m32.757s
user    0m0.200s
sys     0m0.088s
cjac@foxtrot:/tmp$ ls NIST-NVD-Store-SQLite3/t/data/
cwec_v2.1.xml  nvdcve-2.0-test.xml

Publish your patches and I’ll fetch them, or you can submit them in udiff format and I’ll review/apply. Thanks for playing along!

[edit 20120216T1456 -0800] …