MySQL

General
Development
Edit

News

[11603 total ]
FORUM PHP 2009 PARIS

Posted about 11 hours ago by Serge Frezefond

rance FORUM PHP 2009 was held last week in Paris (12th and 13th nov) at Cite des sciences et de l'industrie
Thanks to LeMuG (the french MySQL User Group) MySQL has had a nice set of presentations :

Michael Widenius aka Monty ( Monty ... [More] Program Ab ) presented "MariaDB, the future of MySQL".
Johannes Schluter (MySQL connectors dev team + release manager of PHP 5.3) has presented "PHP and MySQL - A good match"
This was the closing presentation for the 2 days but audience stayed to hear about the nice functionalities of the mysqlnd driver. A lot of interest for the new features (some experimental) : persistent connections, performance statistics, asynchronous request, client side cache, streams + filter, statistics collection, PDO + mysqlnd good match. [Less]

Watching the Retweeted Get Retweeted-er: Power User Secret Retweetist Love

Posted about 16 hours ago by Tim O'Reilly

When Twitter decided to slowly roll out a new, official retweeting feature, people waited in anticipation. When they let their users know what it might look like, people debated whether that was the right way to deploy it. When it actually became ... [More] available, people almost universally disliked it.

But my post is about why I love the new Twitter retweet feature, without ever having to think about it. The reason is that official retweeting represents the new-new arms race for authority among power users. The new-new arms race, you say? Yes, because the new arms race was to get on as many lists as possible, with the most-followed lists having a special significance.

The new-new arms race is the race to get officially retweeted the most. The idea is that in a sea of boring or useless or narrow-topic tweets, people who have "authority" will get retweeted the most. And finally, Twitter has built its own system for keeping track of that - officially. Think of that silly "RT" thing that users generated as a wristwatch at a track meet; Twitter operates the official Rolex timeclock.

Getting officially retweeted has two huge benefits for users that disproportionately benefit the already popular. One, the already popular gain even more authority that will enable their profiles or tweets to be featured, for example, higher in Google and Bing search results. Two, their profile link, photo, and original tweet appear in other people's tweet streams, even if those people don't follow the already very popular person.

Both of these have the potential to drive a tremendous amount of traffic to a person's Twitter account, and the people with the most official retweets will become recommended-users-list version 2.0, I believe (see the ninth paragraph of this story). With all the hub-bub about advertising within one's Twitter stream, driving traffiic is becoming more important to more users than ever before. Who isn't tempted to sign up to push one ad a day and make $30,000 per month in bonus cash?

But not everyone will make $30,000 or $3,000 or even $300 a month. The official retweet system tends to disproportionately favor the already-massively popular. Their authority, already very high, will only become higher relative to that of the average user. To modify the common saying, the common person will watch the retweeted will get retweeted-er.

Not sure if you are part of the retweeted-er class? It's easy to find out. Go to your account on Twitter.com, click the "Retweets" tab, then click on the "Your tweets, retweeted" tab. Is almost every single one of your original tweets in there? Didn't even realize that was happening? Welcome to the club.

Of course, it's not really the fault of the massively popular Twitter users (I don't think Twitter consulted many of them before creating this feature), so don't blame them for trading in on their fame. The petit-bourgeois wealth of authority no doubt creates opportunities for the working-class Twitter users, under the theory of trickle-down tweetonomics. The real question is, will Twitter's proletariat class stand by and watch this happen, or form an uprising?

Addendum: Shortly after I wrote this I came across a Valleywag post with a similar theme. [Less]

A simpler startup script for MySQL on MacOS X

Posted about 23 hours ago by Geert Vanderkelen

What you do when you're fed up with a script? Right, you write your own.You'll have to excuse me for the long shell script you'll find here below, but I'm not going to bother putting it on some download website.It's a shell script which starts and ... [More] stops the MySQL server. Indeed, a replacement for the init.d script found in the MySQL distributions. I'm using it personally on my Macs and it's not supported in any way.But why? Well, I'm playing with MySQL Workbench, Server Administration. The MySQL init.d script didn't work right away (oh, various reasons for that), so I used mine. So I figured it might be useful for others and it's not complicated or shocking-new-stuff. If you want to use it, you'll have to edit the 2 variables at the top. It's only going to work on MacOS X.#!/bin/bash# Author: Geert Vanderkelen <geert@kemuri.org>BASEDIR="/opt/mysql/mysql"CNF="/opt/mysql/my51.cnf"MODE="$1"_RETURN="" # for returning from functionspidof() { local CMD=$1 local PID=`ps cax -o "pid,command" | awk -v cmd="$CMD" '{ if (\$2 == cmd) printf("%s",\$1) }'` if [ "x$PID" == "x" ]; then _RETURN="" return 1 else _RETURN=$PID return 0 fi}checkprocess() { local CMD=$1 _RETURN="" pidof $CMD if [ $? -eq 0 ]; then local PID=${_RETURN} kill -n 0 $PID 2>/dev/null 1>&2 if [ $? -ne 0 ]; then return 2 fi _RETURN=$PID return 0 fi return 1}waituntildown() { local CMD=$1 pidof $CMD while [ $? -eq 0 ]; do echo -n '.' sleep 1 pidof $CMD done}exec_mysqld_safe() { ( cd $BASEDIR ./bin/mysqld_safe --defaults-file=$CNF 2>/dev/null 1>&2 </dev/null & )}status() { checkprocess mysqld RET=$? if [ $RET -ne 0 ]; then if [ $RET -eq 1 ]; then echo "MySQL is not running." elif [ $RET -eq 2 ]; then echo "You have no permission to stop MySQL." fi return 1 fi return 0}start() { checkprocess mysqld if [ $? -eq 0 ]; then echo "MySQL is running." exit 1 fi echo -n "Starting MySQL.. " exec_mysqld_safe sleep 2 checkprocess mysqld if [ $? -ne 0 ]; then echo " Failed!" exit 1 fi echo " OK"}stop() { status RET=$? if [ $RET -ne 0 ]; then exit 1 fi PID=${_RETURN} echo -n "Stopping MySQL.. " kill $PID 2>/dev/null 1>&2 if [ $? -eq 0 ]; then waituntildown mysqld echo " OK" else echo " Failed!" fi}case "$MODE" in "start") start ;; "stop") stop ;; "restart") stop start ;; "status") status if [ $? -eq 0 ]; then echo "MySQL is running." exit 0 else exit 1 fi ;; "getpid") pidof mysqld if [ $? -eq 0 ]; then echo ${_RETURN} fi ;; *) echo "Usage: $0 {start|stop|restart|status|getpid}" exit 1 ;;esac [Less]

MySQL Performance Blog was down today

Posted 1 day ago by MySQL Performance Blog

MySQL Performance Blog (and percona.com too) were down today because the switch in our rack died completely. It took a while to fix it using secondary switch we had. Provider was not willing to do it as remote hands so I had to drive to the data ... [More] center to fix it.
We got number of calls and messages from the customers and friends about web site going down so we probably have to invest into getting infrastructure more redundant – currently we were quite cheap and a lot of servers have single network card (so you can’t use trunking to eliminate switch as single point of failure).
The customer case management systems were not affected by this outage.

Entry posted by peter |
No comment
Add to: | | | | [Less]

Waiting for Godot, Migrating to JavaEE 6, and Other Highlights from Nov 21th, 2009

Posted 1 day ago by The Aquarium

Today is Nov 21th, 2009.

News shorts of interest to our communities, including:

New date for EU review of Oracle/Sun acquisition,
getting closer to v3 fcs,
new OpenESB and OpenDS releases,
Devoxx whiteboards,
new ... [More] customers and japanese event,
and more.

Waiting for Godot

I read
Waiting for Godot
for HS, but I didn't expect to live it...

E.U. Takes More Time to Review Oracle-Sun Deal

On the Road to GlassFish v3

We are getting very close. The buzz around JavaEE 6 and GFv3 at
#devoxx
was very positive; some more links:

Migrating your Apps to JavaEE 6
(Wouter van Reeven)

Adam Bien on

Adoption of EJB and JavaEE,
Ready for Production
and
... (it is) a massive rewrite.

Also see Adam's
JavaEE Patterns and Best Practices @ Kenai,
all based on GF.

New Releases

Final and Release Candidates releases:

OpenDS 2.2.0 Release Candidate 3 is now available

DSEE 7 Download, Documentation and Upgrade Guide Available Today

JavaCAPS 6.2 is now available
(ontherecord,
webpage)
- Based on GlassFish 2.1p2 and OpenESB 2.1

Fuji (OpenESB v3) Milestone 9 is now available -
(Mark's note,
Wiki page).

More Devoxx

Devoxx is over.  By all accounts, a successful show.

Devoxx 2009 picassa album... with

Whiteboard pics - JVM,
CI Tool,
Persistence Layer,
Build Tool,
UI Framework

Hudson #1 in CI (113); next are TeamCity (31) and CruiseControl (30).

Notes
on Mark's jigsaw prezo (from Reinier Zwitserloot)

GlassFish Customers and Events

New customers; new events

Mapion Co. Ltd, in Japan
(chhandomay,
customers)
- Using GlassFish Enterprise Server, MySQL Cluster and more.

Health Sciences South Carolina
(ontherecord)
- Using JavaCAPS 6 including GlassFish ESB, MDM, HL7, LifeRay and more.

New event: Benefits of GlassFish and MySQL
in Japanese (Dec 9th, 2009).
 All events at
GF Event Calendar.

Other News

ICEfaces 2.0 is now
available for download.

State of
OpenSource AppServers
by Masoud Kalali

The LHC is back online!
(website, #LHC) [Less]

18/Nov/2009 - The Open Source Data Warehouse Revolution

Posted 1 day ago by Luca Olivari

On the 18th of November we've hold in Milan an event focused on Open Source Data Warehousing.

For many organizations, data warehouses are simply too costly to buy, too costly to implement and too costly to maintain. Data warehousing is still ... [More] a luxury of deep-pocketed organizations, although the resulting benefits can be virtually reaped by companies of all sizes.
Open Source Software is changing the rules again, lowering the economic barriers to undertake Business Intelligence and Data Warehousing projects.

During the event we proved that MySQL can help organizations to achieve higher ROI on their projects. With the support of our partners, Infobright and Talend, we showed how to design, deploy and manage a multi-terabyte Data Warehouse with Open Source Software.

Participants have shown a lot of interest in MySQL, Infobright and Talend. More informations can be found here: http://www.mysql.com/why-mysql/application-scenarios/data-warehouse.html

We hope to host similar events in the future. Thanks to all participants!
Luca [Less]

Josh Berkus helps clarify clustering

Posted 1 day ago by Baron Schwartz (xaprb)

If you haven’t seen it, Josh Berkus has a very concise way to look at the confusing mess that is database “clustering” from the point of view of three distinct types of users: transactional, analytic, and online. I think that using ... [More] this kind of distinction could help keep discussions clear — I’ve seen a lot of conversations around clustering run off the rails due to disagreements about what clustering means. MySQL Cluster, for example, is a huge red herring for a lot of people, but it seems to be a difficult process to learn it well enough to decide. If we called it a clustering solution for transactional users, but not for analytic or online users, it might help a lot.

Related posts:Sessions of interest at the Percona Performance Conference Having wri50 things to know before migrating Oracle to MySQL A while baWhat are your favorite PostgreSQL performance resources? If you wer
Related posts brought to you by Yet Another Related Posts Plugin. [Less]

I am The Chief Technical Officer

Posted 1 day ago by Vidyut Luther

What exactly does that mean? It means I can talk to the CEO, the Developers, the Users, and make sense out of things, and help you move your business/idea/website further along than where you are.  Quite often, a good idea, or a website gets bogged ... [More] down due to lack of knowledge, or an abundance of misunderstanding and mis-expectations (I also invent words in my free time).  Usually, you know what you want the system to do, I help you figure out why it’s not doing it, and what you need to do, to get it there.
The following are the reasons why my phone rings:
 

By the CEO/Founder of the Company: Their awesome developer, does not have the skills to fine tune the OS, Apache, or MySQL, so even though the application works perfectly in a staging/development environment, it crashes under the load of actual users.
By the Lead Developer/Technical Founder: The CEO doesn’t understand why he can’t have all 20 features by next week, I mean he can think of them, so they can’t be that hard to implement, all he wants is a button on the left hand side of that text, that’ll do x,y,z.   Sometimes, developers need help expressing themselves in more than just binary. I’m fluent in binary, Trinary, and Bottom line. As a developer, I can usually relate, and help them prioritize the task list, and then show the CEO what we can do, and over what period of time. CEO’s don’t like to hear “It can’t be done”, “It’s too complicated”, “It’ll get done when it’s done” from their own teams, if it is too complicated, I can explain why it is, and what a good compromise would be.
By the Systems Administrator: Not All Systems Administrators are Database Admins, they may know what to change in the my.cnf file, but they don’t know when to change it, or they don’t know what to tell the developer. Here’s where I come in. Since, I can tell what the system is doing, and what the programmer intended it to do, I can figure out if you need more RAM, CPU, or what changes the developer needs to make in the application.

I understand budgets, I understand the mythical man month, I understand ramp up times. I get the need for security, documentation, and most importantly, I get people. I’ll help you figure out what the best solution is, and how to start implementing it without losing your shirt, or mind.
  [Less]

ON DUPLICATE KEY With NULL Validation

Posted 2 days ago by Venu Anuganti

I am not sure if this is a bug or how MySQL works on validating constraints in association with ON DUPLICATE KEY (late or early checking). For example, consider the following use case (this is irrepective of storage engine and MySQL ... [More] version):

mysql> create table t1(id int not null primary key, val int not null) Engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)
 
mysql> insert into t1 values(10,20);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into t1 values(20,10);
Query OK, 1 row affected (0.00 sec)
 
mysql> create table t2(id1 int not null primary key, val1 int) Engine=MyISAM;
Query OK, 0 rows affected (0.14 sec)
 
mysql> insert into t2 values(10,NULL);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t1(id, val) select id1, val1 from t2 ON DUPLICATE KEY UPDATE val=IF (VALUES(val) IS NULL, val, VALUES(val));
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 1 Duplicates: 1 Warnings: 1
 
mysql> show warnings;
+---------+------+-----------------------------+
| Level | Code | Message |
+---------+------+-----------------------------+
| Warning | 1048 | Column 'val' cannot be null |
+---------+------+-----------------------------+
1 row in set (0.00 sec)
 
mysql> select * from t1;
+----+------+
| id | val |
+----+------+
| 10 | 0 |
| 20 | 10 |
+----+------+
2 rows in set (0.00 sec)

In the above case, you have table t1 with ‘val’ column being NOT NULL; and t2 is trying to insert to t1 with NULL for val column as part of the INSERT, and ON DUPLICATE KEY; it actually keeps the old value and will not populate the NULL to t1 at all (common use case when you have a business logic to replace old with new or keep the old as is when new value is not appropriate or not modified, which in general is represented by NULL).
So; but as you can see; the validation of NULL happens before the ON DUPLICATE constraint checking; and in the above case, it should not as already primary key satisfies the duplicate constraint (before trigger of fill_records) and server should proceed with changes from what was specified from the ON DUPLICATE section (after trigger).
That is apart; and still you can see that it got a new value 0 from no where by replacing the old one 20 for column val, which is odd. The correct behavior is to just check only primary/unique constraint values when ON DUPLICATE KEY is specified in early binding and validate the rest in late binding by taking the final output from ON DUPLICATE clause… then the above should have been…

mysql> insert into t1(id, val) select id1, val1 from t2 ON DUPLICATE KEY UPDATE conv=IF (VALUES(val) IS NULL, val, VALUES(val));
Query OK, 2 rows affected (0.00 sec)
Records: 1 Duplicates: 1 Warnings: 0
 
mysql> select * from t1;
+----+------+
| id | val |
+----+------+
| 10 | 20 |
| 20 | 10 |
+----+------+
2 rows in set (0.00 sec)

As no other server supports ON DUPLICATE KEY, its hard to derive what is the right logic that needs to be applied here. But I still think early validation is wrong here as the value can change in the late binding due to other constraints that can happen from ON DUPLICATE KEY derivation.
The above is only one use case with NULL; this can be simulated to other constraints as well… [Less]

DynamoDB: Built in Time Dimension support!

Posted 2 days ago by Nicholas Goodman

DynamoDB (aka LucidDB) is not just another column store database. Our goal is being the best database for actually doing Business Intelligence; while that means being fast and handling large amounts of data there’s a lot of other things BI ... [More] consultant/developers need. I’ll continue to post about some of the great BI features that DynamoDB has for the modern datasmiths.
First feature to cover that’s dead easy, is the built in ability to generate a time dimension, including a Fiscal Calendar attributes. If you’re using Mondrian (or come to that, your own custom SQL on a star schema) you need to have a time dimension. Time is the most important dimension! Every OLAP model I’ve ever built uses one! It something that you, as a datasmith will need to do with every project; that’s why we’ve built it right into our database.
Here’s a dead simple way to create a fully baked, ready to use Time Dimension to use with Mondrian.
-- Create a view that is our time dimension for 10 years, with our
-- Fiscal calendar starting in March (3)
create view dim_time as select * from
table(applib.fiscal_time_dimension (2000, 1, 1, 2009, 12, 31, 3));

OK, that’s it. You’ve created a Time Dimension! * see NOTE at end of post.
So, we’ve created our time dimension, complete with a Fiscal calendar for 10 years in a single statement! Awesome - but what does it contain?

-- Structure of new time dimension
select "TABLE_NAME", "COLUMN_NAME", "DATATYPE" from sys_root.dba_columns
where table_name = 'DIM_TIME';
+-------------+---------------------------------+-----------+
| TABLE_NAME | COLUMN_NAME | DATATYPE |
+-------------+---------------------------------+-----------+
| DIM_TIME | FISCAL_YEAR_END_DATE | DATE |
| DIM_TIME | FISCAL_YEAR_START_DATE | DATE |
| DIM_TIME | FISCAL_QUARTER_NUMBER_IN_YEAR | INTEGER |
| DIM_TIME | FISCAL_QUARTER_END_DATE | DATE |
| DIM_TIME | FISCAL_QUARTER_START_DATE | DATE |
| DIM_TIME | FISCAL_MONTH_NUMBER_IN_YEAR | INTEGER |
| DIM_TIME | FISCAL_MONTH_NUMBER_IN_QUARTER | INTEGER |
| DIM_TIME | FISCAL_MONTH_END_DATE | DATE |
| DIM_TIME | FISCAL_MONTH_START_DATE | DATE |
| DIM_TIME | FISCAL_WEEK_NUMBER_IN_YEAR | INTEGER |
| DIM_TIME | FISCAL_WEEK_NUMBER_IN_QUARTER | INTEGER |
| DIM_TIME | FISCAL_WEEK_NUMBER_IN_MONTH | INTEGER |
| DIM_TIME | FISCAL_WEEK_END_DATE | DATE |
| DIM_TIME | FISCAL_WEEK_START_DATE | DATE |
| DIM_TIME | FISCAL_DAY_NUMBER_IN_YEAR | INTEGER |
| DIM_TIME | FISCAL_DAY_NUMBER_IN_QUARTER | INTEGER |
| DIM_TIME | FISCAL_YEAR | INTEGER |
| DIM_TIME | YEAR_END_DATE | DATE |
| DIM_TIME | YEAR_START_DATE | DATE |
| DIM_TIME | QUARTER_END_DATE | DATE |
| DIM_TIME | QUARTER_START_DATE | DATE |
| DIM_TIME | MONTH_END_DATE | DATE |
| DIM_TIME | MONTH_START_DATE | DATE |
| DIM_TIME | WEEK_END_DATE | DATE |
| DIM_TIME | WEEK_START_DATE | DATE |
| DIM_TIME | CALENDAR_QUARTER | VARCHAR |
| DIM_TIME | YR | INTEGER |
| DIM_TIME | QUARTER | INTEGER |
| DIM_TIME | MONTH_NUMBER_OVERALL | INTEGER |
| DIM_TIME | MONTH_NUMBER_IN_YEAR | INTEGER |
| DIM_TIME | MONTH_NUMBER_IN_QUARTER | INTEGER |
| DIM_TIME | MONTH_NAME | VARCHAR |
| DIM_TIME | WEEK_NUMBER_OVERALL | INTEGER |
| DIM_TIME | WEEK_NUMBER_IN_YEAR | INTEGER |
| DIM_TIME | WEEK_NUMBER_IN_QUARTER | INTEGER |
| DIM_TIME | WEEK_NUMBER_IN_MONTH | INTEGER |
| DIM_TIME | DAY_FROM_JULIAN | INTEGER |
| DIM_TIME | DAY_NUMBER_OVERALL | INTEGER |
| DIM_TIME | DAY_NUMBER_IN_YEAR | INTEGER |
| DIM_TIME | DAY_NUMBER_IN_QUARTER | INTEGER |
| DIM_TIME | DAY_NUMBER_IN_MONTH | INTEGER |
| DIM_TIME | DAY_NUMBER_IN_WEEK | INTEGER |
| DIM_TIME | WEEKEND | VARCHAR |
| DIM_TIME | DAY_OF_WEEK | VARCHAR |
| DIM_TIME | TIME_KEY | DATE |
| DIM_TIME | TIME_KEY_SEQ | INTEGER |
+-------------+---------------------------------+-----------+

-- Let's look at a few rows
select time_key_seq, time_key, yr, month_number_in_year, fiscal_year
, fiscal_month_number_in_year from dim_time;
+---------------+-------------+-------+-----------------------+--------------+------------------------------+
| TIME_KEY_SEQ | TIME_KEY | YR | MONTH_NUMBER_IN_YEAR | FISCAL_YEAR | FISCAL_MONTH_NUMBER_IN_YEAR |
+---------------+-------------+-------+-----------------------+--------------+------------------------------+
| 1 | 2000-01-01 | 2000 | 1 | 2000 | 11 |
| 2 | 2000-01-02 | 2000 | 1 | 2000 | 11 |
| 3 | 2000-01-03 | 2000 | 1 | 2000 | 11 |
| 4 | 2000-01-04 | 2000 | 1 | 2000 | 11 |
| 5 | 2000-01-05 | 2000 | 1 | 2000 | 11 |
| 6 | 2000-01-06 | 2000 | 1 | 2000 | 11 |
| 7 | 2000-01-07 | 2000 | 1 | 2000 | 11 |
| 8 | 2000-01-08 | 2000 | 1 | 2000 | 11 |
| 9 | 2000-01-09 | 2000 | 1 | 2000 | 11 |
| 10 | 2000-01-10 | 2000 | 1 | 2000 | 11 |
+---------------+-------------+-------+-----------------------+--------------+------------------------------+

Generating the Time Dimension is accomplished using DynamoDBs ability to include Java based UDF Table Functions. Table functions are really powerful - they allow a BI developer to write custom functions that output a “table” that can be queried like ANY OTHER TABLE (mostly). Check out the wiki page FarragoUdx if your interested.
And of course: download LucidDB and give it a whirl!
NOTE: To be candid, doing it as a view isn’t the best approach. For anything beyond tiny (5 million +) we should actually create the table, and do an INSERT INTO SELECT * FROM TABLE(fiscal_time_dimension). [Less]