Smart code

To contact me via e-mail:

awfief@gmail.com
To contact me via e-mail:

awfief@gmail.com
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
To contact me via e-mail:

awfief@gmail.com
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
The first Boston MySQL User Group meeting went swimmingly. About 1/2 the people who RSVP’s yes or maybe showed up, ) got pizza as a thank-you gift.

My boss offered me a ride home, definitely — I’ll just go into work later, and not be tempted by a ride home.

The demographics of the group was really amazing:

about 15% female
those with no experience with any database
those with experience with databases but not MySQL
those who’ve been using MySQL for weeks
those who’ve been using MySQL for months
those who’ve been using MySQL for years
those who are trying to convince their company to switch
about 10% Indian
about 20% other-Asian
(I didn’t notice anyone that was recognizably Hispanic or black)
job titles ranging from developer, dba, all the way up to the vice president and president level
The publishing sector was represented by O’Reilly, Addison-Wesley (which is owned by Pearson, which handles the MySQL Press imprint), and Apress. O’Reilly and Apress solicited authors.

Corrections always welcome, and special thanks to Mike Kruckenberg, and Mark Rubin of MySQL AB.

I cannot wait for next month. . .
To contact me via e-mail:

awfief@gmail.com
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
The first Boston MySQL User Group meeting went swimmingly. About 1/2 the people who RSVP’s yes or maybe showed up, ) got pizza as a thank-you gift.

My boss offered me a ride home, definitely — I’ll just go into work later, and not be tempted by a ride home.

The demographics of the group was really amazing:

about 15% female
those with no experience with any database
those with experience with databases but not MySQL
those who’ve been using MySQL for weeks
those who’ve been using MySQL for months
those who’ve been using MySQL for years
those who are trying to convince their company to switch
about 10% Indian
about 20% other-Asian
(I didn’t notice anyone that was recognizably Hispanic or black)
job titles ranging from developer, dba, all the way up to the vice president and president level
The publishing sector was represented by O’Reilly, Addison-Wesley (which is owned by Pearson, which handles the MySQL Press imprint), and Apress. O’Reilly and Apress solicited authors.

Corrections always welcome, and special thanks to Mike Kruckenberg, and Mark Rubin of MySQL AB.

I cannot wait for next month. . .
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
The first Boston MySQL User Group meeting went swimmingly. About 1/2 the people who RSVP’s yes or maybe showed up, ) got pizza as a thank-you gift.

My boss offered me a ride home, definitely — I’ll just go into work later, and not be tempted by a ride home.

The demographics of the group was really amazing:

about 15% female
those with no experience with any database
those with experience with databases but not MySQL
those who’ve been using MySQL for weeks
those who’ve been using MySQL for months
those who’ve been using MySQL for years
those who are trying to convince their company to switch
about 10% Indian
about 20% other-Asian
(I didn’t notice anyone that was recognizably Hispanic or black)
job titles ranging from developer, dba, all the way up to the vice president and president level
The publishing sector was represented by O’Reilly, Addison-Wesley (which is owned by Pearson, which handles the MySQL Press imprint), and Apress. O’Reilly and Apress solicited authors.

Corrections always welcome, and special thanks to Mike Kruckenberg, and Mark Rubin of MySQL AB.

I cannot wait for next month. . .
So I’ve been looking into the Archive Storage Engine. What I would really like to do with it is get data in realtime, they want all the data up until “now”.

It is inadvisable to replicate from one storage engine type to another. I have not yet played with it, but since an Archive table does not allow updates and deletes, replicating from a MyISAM or InnoDB table to an Archive one is a bad idea.

Most folks probably run a batch job; but I wonder if it can be done in real-time. Or rather, ‘what is the best way to run it real-time?’ One way, off the top of my head, is to do this are to replicate to a blackhole table with a trigger, to insert into an archive table whenever an INSERT statement is called. The blackhole table should not give an error upon UPDATE or DELETE statements.

This also allows for easy aggregation, because the trigger can say “update the count and the country of a new profile” instead of having an entire replicated set of data, with reports running “SELECT count(*)”. Instead of copying all the data and running the same reports on a different server/table, we can now collect the data we actually want, which is “1 new paid membership at time t located in Sao Paulo, Brazil.” For reporting, we do not care what the name of the member is.

I have searched around but have not yet found how users are getting data into their archived databases. I need a sandbox server at work so I can play with the options.
To contact me via e-mail:

awfief@gmail.com
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
The first Boston MySQL User Group meeting went swimmingly. About 1/2 the people who RSVP’s yes or maybe showed up, ) got pizza as a thank-you gift.

My boss offered me a ride home, definitely — I’ll just go into work later, and not be tempted by a ride home.

The demographics of the group was really amazing:

about 15% female
those with no experience with any database
those with experience with databases but not MySQL
those who’ve been using MySQL for weeks
those who’ve been using MySQL for months
those who’ve been using MySQL for years
those who are trying to convince their company to switch
about 10% Indian
about 20% other-Asian
(I didn’t notice anyone that was recognizably Hispanic or black)
job titles ranging from developer, dba, all the way up to the vice president and president level
The publishing sector was represented by O’Reilly, Addison-Wesley (which is owned by Pearson, which handles the MySQL Press imprint), and Apress. O’Reilly and Apress solicited authors.

Corrections always welcome, and special thanks to Mike Kruckenberg, and Mark Rubin of MySQL AB.

I cannot wait for next month. . .
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
The first Boston MySQL User Group meeting went swimmingly. About 1/2 the people who RSVP’s yes or maybe showed up, ) got pizza as a thank-you gift.

My boss offered me a ride home, definitely — I’ll just go into work later, and not be tempted by a ride home.

The demographics of the group was really amazing:

about 15% female
those with no experience with any database
those with experience with databases but not MySQL
those who’ve been using MySQL for weeks
those who’ve been using MySQL for months
those who’ve been using MySQL for years
those who are trying to convince their company to switch
about 10% Indian
about 20% other-Asian
(I didn’t notice anyone that was recognizably Hispanic or black)
job titles ranging from developer, dba, all the way up to the vice president and president level
The publishing sector was represented by O’Reilly, Addison-Wesley (which is owned by Pearson, which handles the MySQL Press imprint), and Apress. O’Reilly and Apress solicited authors.

Corrections always welcome, and special thanks to Mike Kruckenberg, and Mark Rubin of MySQL AB.

I cannot wait for next month. . .
So I’ve been looking into the Archive Storage Engine. What I would really like to do with it is get data in realtime, they want all the data up until “now”.

It is inadvisable to replicate from one storage engine type to another. I have not yet played with it, but since an Archive table does not allow updates and deletes, replicating from a MyISAM or InnoDB table to an Archive one is a bad idea.

Most folks probably run a batch job; but I wonder if it can be done in real-time. Or rather, ‘what is the best way to run it real-time?’ One way, off the top of my head, is to do this are to replicate to a blackhole table with a trigger, to insert into an archive table whenever an INSERT statement is called. The blackhole table should not give an error upon UPDATE or DELETE statements.

This also allows for easy aggregation, because the trigger can say “update the count and the country of a new profile” instead of having an entire replicated set of data, with reports running “SELECT count(*)”. Instead of copying all the data and running the same reports on a different server/table, we can now collect the data we actually want, which is “1 new paid membership at time t located in Sao Paulo, Brazil.” For reporting, we do not care what the name of the member is.

I have searched around but have not yet found how users are getting data into their archived databases. I need a sandbox server at work so I can play with the options.
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
The first Boston MySQL User Group meeting went swimmingly. About 1/2 the people who RSVP’s yes or maybe showed up, ) got pizza as a thank-you gift.

My boss offered me a ride home, definitely — I’ll just go into work later, and not be tempted by a ride home.

The demographics of the group was really amazing:

about 15% female
those with no experience with any database
those with experience with databases but not MySQL
those who’ve been using MySQL for weeks
those who’ve been using MySQL for months
those who’ve been using MySQL for years
those who are trying to convince their company to switch
about 10% Indian
about 20% other-Asian
(I didn’t notice anyone that was recognizably Hispanic or black)
job titles ranging from developer, dba, all the way up to the vice president and president level
The publishing sector was represented by O’Reilly, Addison-Wesley (which is owned by Pearson, which handles the MySQL Press imprint), and Apress. O’Reilly and Apress solicited authors.

Corrections always welcome, and special thanks to Mike Kruckenberg, and Mark Rubin of MySQL AB.

I cannot wait for next month. . .
So I’ve been looking into the Archive Storage Engine. What I would really like to do with it is get data in realtime, they want all the data up until “now”.

It is inadvisable to replicate from one storage engine type to another. I have not yet played with it, but since an Archive table does not allow updates and deletes, replicating from a MyISAM or InnoDB table to an Archive one is a bad idea.

Most folks probably run a batch job; but I wonder if it can be done in real-time. Or rather, ‘what is the best way to run it real-time?’ One way, off the top of my head, is to do this are to replicate to a blackhole table with a trigger, to insert into an archive table whenever an INSERT statement is called. The blackhole table should not give an error upon UPDATE or DELETE statements.

This also allows for easy aggregation, because the trigger can say “update the count and the country of a new profile” instead of having an entire replicated set of data, with reports running “SELECT count(*)”. Instead of copying all the data and running the same reports on a different server/table, we can now collect the data we actually want, which is “1 new paid membership at time t located in Sao Paulo, Brazil.” For reporting, we do not care what the name of the member is.

I have searched around but have not yet found how users are getting data into their archived databases. I need a sandbox server at work so I can play with the options.
Why is it that running a monitoring system is such a marketable skill? In every company, having servers up is important. So does everyone have a robust monitoring system? Sure, folks implement scripts, but those are always one-off solutions. And what IS it with folks writing scripts that e-mail them when they’re successful? Just touch a file and have a simple script check to see if the file was touched, that way you know when it failed and do not have to rely on “did I get that e-mail?”

I was lucky to have worked on Nagios while at Tufts, and have set it up everywhere else I’ve worked. I should definitely remember to check up on the plugins site from time to time, so I do not duplicate efforts. Of course, submitting plugins would also eliminate the problem of “I wrote that check at my last company, and now I want it again!” I have not yet come across that, although I did come across a plugin I could have used at my last job, checking data via snmp, that would have been useful, and which I will implement here.

For now, nagios (2.0) is happy. I’ve written checks to find the lag time on replication and the remaining innodb space. Behind the cut is a 50k picture of the nagios server, currently happy.

I also intend to use the reporting feature of nagios to get stats on how available our services are.



To contact me via e-mail:

awfief@gmail.com
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
The first Boston MySQL User Group meeting went swimmingly. About 1/2 the people who RSVP’s yes or maybe showed up, ) got pizza as a thank-you gift.

My boss offered me a ride home, definitely — I’ll just go into work later, and not be tempted by a ride home.

The demographics of the group was really amazing:

about 15% female
those with no experience with any database
those with experience with databases but not MySQL
those who’ve been using MySQL for weeks
those who’ve been using MySQL for months
those who’ve been using MySQL for years
those who are trying to convince their company to switch
about 10% Indian
about 20% other-Asian
(I didn’t notice anyone that was recognizably Hispanic or black)
job titles ranging from developer, dba, all the way up to the vice president and president level
The publishing sector was represented by O’Reilly, Addison-Wesley (which is owned by Pearson, which handles the MySQL Press imprint), and Apress. O’Reilly and Apress solicited authors.

Corrections always welcome, and special thanks to Mike Kruckenberg, and Mark Rubin of MySQL AB.

I cannot wait for next month. . .
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
The first Boston MySQL User Group meeting went swimmingly. About 1/2 the people who RSVP’s yes or maybe showed up, ) got pizza as a thank-you gift.

My boss offered me a ride home, definitely — I’ll just go into work later, and not be tempted by a ride home.

The demographics of the group was really amazing:

about 15% female
those with no experience with any database
those with experience with databases but not MySQL
those who’ve been using MySQL for weeks
those who’ve been using MySQL for months
those who’ve been using MySQL for years
those who are trying to convince their company to switch
about 10% Indian
about 20% other-Asian
(I didn’t notice anyone that was recognizably Hispanic or black)
job titles ranging from developer, dba, all the way up to the vice president and president level
The publishing sector was represented by O’Reilly, Addison-Wesley (which is owned by Pearson, which handles the MySQL Press imprint), and Apress. O’Reilly and Apress solicited authors.

Corrections always welcome, and special thanks to Mike Kruckenberg, and Mark Rubin of MySQL AB.

I cannot wait for next month. . .
So I’ve been looking into the Archive Storage Engine. What I would really like to do with it is get data in realtime, they want all the data up until “now”.

It is inadvisable to replicate from one storage engine type to another. I have not yet played with it, but since an Archive table does not allow updates and deletes, replicating from a MyISAM or InnoDB table to an Archive one is a bad idea.

Most folks probably run a batch job; but I wonder if it can be done in real-time. Or rather, ‘what is the best way to run it real-time?’ One way, off the top of my head, is to do this are to replicate to a blackhole table with a trigger, to insert into an archive table whenever an INSERT statement is called. The blackhole table should not give an error upon UPDATE or DELETE statements.

This also allows for easy aggregation, because the trigger can say “update the count and the country of a new profile” instead of having an entire replicated set of data, with reports running “SELECT count(*)”. Instead of copying all the data and running the same reports on a different server/table, we can now collect the data we actually want, which is “1 new paid membership at time t located in Sao Paulo, Brazil.” For reporting, we do not care what the name of the member is.

I have searched around but have not yet found how users are getting data into their archived databases. I need a sandbox server at work so I can play with the options.
To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
The first Boston MySQL User Group meeting went swimmingly. About 1/2 the people who RSVP’s yes or maybe showed up, ) got pizza as a thank-you gift.

My boss offered me a ride home, definitely — I’ll just go into work later, and not be tempted by a ride home.

The demographics of the group was really amazing:

about 15% female
those with no experience with any database
those with experience with databases but not MySQL
those who’ve been using MySQL for weeks
those who’ve been using MySQL for months
those who’ve been using MySQL for years
those who are trying to convince their company to switch
about 10% Indian
about 20% other-Asian
(I didn’t notice anyone that was recognizably Hispanic or black)
job titles ranging from developer, dba, all the way up to the vice president and president level
The publishing sector was represented by O’Reilly, Addison-Wesley (which is owned by Pearson, which handles the MySQL Press imprint), and Apress. O’Reilly and Apress solicited authors.

Corrections always welcome, and special thanks to Mike Kruckenberg, and Mark Rubin of MySQL AB.

I cannot wait for next month. . .
So I’ve been looking into the Archive Storage Engine. What I would really like to do with it is get data in realtime, they want all the data up until “now”.

It is inadvisable to replicate from one storage engine type to another. I have not yet played with it, but since an Archive table does not allow updates and deletes, replicating from a MyISAM or InnoDB table to an Archive one is a bad idea.

Most folks probably run a batch job; but I wonder if it can be done in real-time. Or rather, ‘what is the best way to run it real-time?’ One way, off the top of my head, is to do this are to replicate to a blackhole table with a trigger, to insert into an archive table whenever an INSERT statement is called. The blackhole table should not give an error upon UPDATE or DELETE statements.

This also allows for easy aggregation, because the trigger can say “update the count and the country of a new profile” instead of having an entire replicated set of data, with reports running “SELECT count(*)”. Instead of copying all the data and running the same reports on a different server/table, we can now collect the data we actually want, which is “1 new paid membership at time t located in Sao Paulo, Brazil.” For reporting, we do not care what the name of the member is.

I have searched around but have not yet found how users are getting data into their archived databases. I need a sandbox server at work so I can play with the options.
Why is it that running a monitoring system is such a marketable skill? In every company, having servers up is important. So does everyone have a robust monitoring system? Sure, folks implement scripts, but those are always one-off solutions. And what IS it with folks writing scripts that e-mail them when they’re successful? Just touch a file and have a simple script check to see if the file was touched, that way you know when it failed and do not have to rely on “did I get that e-mail?”

I was lucky to have worked on Nagios while at Tufts, and have set it up everywhere else I’ve worked. I should definitely remember to check up on the plugins site from time to time, so I do not duplicate efforts. Of course, submitting plugins would also eliminate the problem of “I wrote that check at my last company, and now I want it again!” I have not yet come across that, although I did come across a plugin I could have used at my last job, checking data via snmp, that would have been useful, and which I will implement here.

For now, nagios (2.0) is happy. I’ve written checks to find the lag time on replication and the remaining innodb space. Behind the cut is a 50k picture of the nagios server, currently happy.

I also intend to use the reporting feature of nagios to get stats on how available our services are.



To contact me via e-mail:

awfief@gmail.com
Having used Oracle, DB2, Postgres, Sybase, Informix, and MSSQL, I always enjoyed that MySQL just named everything “MySQL”. Sure, it can get confusing — there’s MySQL the server, MySQL the client, MySQL the database instance. . . .MySQL the flamethrower (the kids love this one). . . .But seriously, the ‘big guys’ have all this complicated jargon for really simple ideas.

MySQL has joined them. Granted, I’d been out of the MySQL world for about a year, and some wonderful things have happened in that year. Even a year ago, the company I worked for wasn’t using the most recent software nor taking advantage of all the features their versions of MySQL did have to offer. But I digress.

I’ve been working on MySQL knowledge, particularly with the free webinars. Today I attended the “MySQL Network and MySQL 5.0” webinar, where I learned that MySQL is packaging (better) software, support, tools, access to developers, and a knowledgebase into what they call “MySQL Network.” I was completely unclear on the concept of MySQL Network from the description, and from the name I figured it would have something to do with technical networking, not business to business networking.

Meanwhile, yesterday I realized that the “Pluggable Storage Engines” in MySQL just mean “you can use different table types, and turn off the ones you don’t want to use.” I was familiar with the concept, but not the buzz-phrase used to describe it.
The first Boston MySQL User Group meeting went swimmingly. About 1/2 the people who RSVP’s yes or maybe showed up, ) got pizza as a thank-you gift.

My boss offered me a ride home, definitely — I’ll just go into work later, and not be tempted by a ride home.

The demographics of the group was really amazing:

about 15% female
those with no experience with any database
those with experience with databases but not MySQL
those who’ve been using MySQL for weeks
those who’ve been using MySQL for months
those who’ve been using MySQL for years
those who are trying to convince their company to switch
about 10% Indian
about 20% other-Asian
(I didn’t notice anyone that was recognizably Hispanic or black)
job titles ranging from developer, dba, all the way up to the vice president and president level
The publishing sector was represented by O’Reilly, Addison-Wesley (which is owned by Pearson, which handles the MySQL Press imprint), and Apress. O’Reilly and Apress solicited authors.

Corrections always welcome, and special thanks to Mike Kruckenberg, and Mark Rubin of MySQL AB.

I cannot wait for next month. . .
So I’ve been looking into the Archive Storage Engine. What I would really like to do with it is get data in realtime, they want all the data up until “now”.

It is inadvisable to replicate from one storage engine type to another. I have not yet played with it, but since an Archive table does not allow updates and deletes, replicating from a MyISAM or InnoDB table to an Archive one is a bad idea.

Most folks probably run a batch job; but I wonder if it can be done in real-time. Or rather, ‘what is the best way to run it real-time?’ One way, off the top of my head, is to do this are to replicate to a blackhole table with a trigger, to insert into an archive table whenever an INSERT statement is called. The blackhole table should not give an error upon UPDATE or DELETE statements.

This also allows for easy aggregation, because the trigger can say “update the count and the country of a new profile” instead of having an entire replicated set of data, with reports running “SELECT count(*)”. Instead of copying all the data and running the same reports on a different server/table, we can now collect the data we actually want, which is “1 new paid membership at time t located in Sao Paulo, Brazil.” For reporting, we do not care what the name of the member is.

I have searched around but have not yet found how users are getting data into their archived databases. I need a sandbox server at work so I can play with the options.
Why is it that running a monitoring system is such a marketable skill? In every company, having servers up is important. So does everyone have a robust monitoring system? Sure, folks implement scripts, but those are always one-off solutions. And what IS it with folks writing scripts that e-mail them when they’re successful? Just touch a file and have a simple script check to see if the file was touched, that way you know when it failed and do not have to rely on “did I get that e-mail?”

I was lucky to have worked on Nagios while at Tufts, and have set it up everywhere else I’ve worked. I should definitely remember to check up on the plugins site from time to time, so I do not duplicate efforts. Of course, submitting plugins would also eliminate the problem of “I wrote that check at my last company, and now I want it again!” I have not yet come across that, although I did come across a plugin I could have used at my last job, checking data via snmp, that would have been useful, and which I will implement here.

For now, nagios (2.0) is happy. I’ve written checks to find the lag time on replication and the remaining innodb space. Behind the cut is a 50k picture of the nagios server, currently happy.

I also intend to use the reporting feature of nagios to get stats on how available our services are.



So, the other day I was asked by a developer to come up with a table to tally votes. Basically, there are 6 choices, and customers can vote once per day. I asked if there were any other constraints, and there were none. I specifically asked if they wanted once per calendar date, or ‘it has to be 24 hours since the last vote’; they wanted ‘once per calendar date’. And updating the current day’s vote is not allowed. Once the vote is in, it cannot be changed.

So I came up with a simple table:

CREATE TABLE `ManOfMonth` (
`uid` int(10) unsigned NOT NULL default ‘0’,
`voteDate` date NOT NULL default ‘0000-00-00’,
`uidVoteFor` int(10) unsigned NOT NULL default ‘0’,
PRIMARY KEY (`uid`,`voteDate`),
KEY `countVotes` (`uidVoteFor`)
)

There’s no need for a timestamp, and you can select now() into the voteDate part, and MySQL truncates the data for you (we’re using 4.1.12).

The smart part of this is that the primary key is the constraint on the database — that is, a userid cannot vote more than once per day. So I told the developer, “When you’re writing the code, don’t bother running a select to see if they’ve already voted; just do the insert query, and check if there ends up being an error for a duplicate key.”

Using the MySQL error messages in this way prevents unnecessary selects. This kind of coding should be common practice; I have a feeling it is not, particularly since I had to explain to the developer why the select was unnecessary. Then again, the developer also had wanted a table with uidVoteFor and the tally as 2 columns. This would be a smaller table, sure, and getting the result would be easy, but then there’s still the accounting for whether or not the person had voted that day. The index on uidVoteFor helps make the tally query faster.

11 responses to “Smart code

  1. Hi guys.

    Why don’t you mention the very powerful “REPLACE INTO” MySQL specific instruction that does all the job…

    REPLACE INTO ManOfMonth SET uid=1,voteDate=now(),uidVoteFor=2;

    This will not trigger any error and mysql_affected_rows() will return 1 if the row was just inserted, or 2 if the existing row has been deleted and inserted again.

  2. Hi blackflags,

    REPLACE INTO would mean that a person could change their vote. And the end of the first paragraph specifically states “Once the vote is in, it cannot be changed.” Otherwise, yes, we would have used REPLACE INTO.