This thread on the Boston MySQL User Group Board is getting interesting:
http://mysql.meetup.com/137/boards/view/viewthread?thread=2280640
(From the original poster:)
I think that nonequivalence comes from the past when the data sharing was a
rare situation. The data changes were always initiated by application and it
always knew about those changes. Now the situation is different. When the data
are shared between multiple remote applications you have to notify other
interested parties about those changes.
Currently databases are mostly used as “pull” components. If they had standard
“push” functionality they could compete with messaging systems with the advantages
of automatic data persistence and powerful query language.
(my response:)
Well, and that’s the problem — the paradigm *has* changed. MySQL is fast and reliable because it does NOT put things like messaging into their database, which Oracle and SQL Server do. A database is not a messaging system, it’s a database.
What effect would notification that there have been changes have on MVCC? I do wish there was a “pull” way to check if the data has changed.
The paradigm change of the application managing the schema causes this. I do not believe messaging is the correct way to handle this problem.
Consider the parallel to source code version control. Much like MVCC, you check out code, change it, and commit the code. Unlike many source code version control systems, though, MVCC (“data version control”) does not have the equivalent of an “update” command, except for doing another pull from the database. It would be great if there was an easy way to do a “diff” of what’s in the database versus what the application is changing, but that seems like it would be a programmatic thing (function or method), not a database thing.
And consider the database overhead and bandwidth….instead of just running queries, MySQL would have to somehow keep track of which thread has what data, and then notify every single thread that has that data, that it’s changed. The applications will have to be written to keep threads open longer, which will consume lots of resources. That’s lots more overhead for the database, and much more bandwidth, because there may be instances of the application that are using data that they do not care if it changed….so the messaging system would be wasting bandwidth, sending messages to instances that do not care. Although that could be mitigated by the application keeping a thread open when it cares about whether or not the data has changed.
Then again, I’m not fond of managed schema in the application…or at least, when the developers write that code. Seems to me it should be the DBA writing that code. It’s *very* useful for data consistency and integrity, which is a function of the DBA, not a developer.
What effects do you see the managed schema having on databases? Who should be responsible for writing a managed schema? Should a managed schema be used for database consistency within an application? Where is the line drawn between the application putting the required information into the database, and the database’s job of maintaining consistency and integrity?
It’s somewhat ironic, since for a long time MySQL advocated using the application to ensure the consistency and integrity (ie, before MySQL had a storage engine with foreign keys and transactions).
I often say that the biggest reason MySQL is a widely used database is because it is fast. A fast database can be complemented by an application that adds the features the database is missing; but a slow database that is full-featured cannot be made faster by an application. So it worries me when folks request very specialized systems such as a messaging server (or ANY “push” system) into the database, because that could be easily done with a “pull” mechanism, only using the bandwidth needed by the instances of the applications that care. Otherwise, it will end up adding Microsoft-level bloat to a really nice and fast program.
This thread on the Boston MySQL User Group Board is getting interesting:
http://mysql.meetup.com/137/boards/view/viewthread?thread=2280640
(From the original poster:)
I think that nonequivalence comes from the past when the data sharing was a
rare situation. The data changes were always initiated by application and it
always knew about those changes. Now the situation is different. When the data
are shared between multiple remote applications you have to notify other
interested parties about those changes.
Currently databases are mostly used as “pull” components. If they had standard
“push” functionality they could compete with messaging systems with the advantages
of automatic data persistence and powerful query language.
(my response:)
Well, and that’s the problem — the paradigm *has* changed. MySQL is fast and reliable because it does NOT put things like messaging into their database, which Oracle and SQL Server do. A database is not a messaging system, it’s a database.
What effect would notification that there have been changes have on MVCC? I do wish there was a “pull” way to check if the data has changed.
The paradigm change of the application managing the schema causes this. I do not believe messaging is the correct way to handle this problem.
Consider the parallel to source code version control. Much like MVCC, you check out code, change it, and commit the code. Unlike many source code version control systems, though, MVCC (“data version control”) does not have the equivalent of an “update” command, except for doing another pull from the database. It would be great if there was an easy way to do a “diff” of what’s in the database versus what the application is changing, but that seems like it would be a programmatic thing (function or method), not a database thing.
And consider the database overhead and bandwidth….instead of just running queries, MySQL would have to somehow keep track of which thread has what data, and then notify every single thread that has that data, that it’s changed. The applications will have to be written to keep threads open longer, which will consume lots of resources. That’s lots more overhead for the database, and much more bandwidth, because there may be instances of the application that are using data that they do not care if it changed….so the messaging system would be wasting bandwidth, sending messages to instances that do not care. Although that could be mitigated by the application keeping a thread open when it cares about whether or not the data has changed.
Then again, I’m not fond of managed schema in the application…or at least, when the developers write that code. Seems to me it should be the DBA writing that code. It’s *very* useful for data consistency and integrity, which is a function of the DBA, not a developer.
What effects do you see the managed schema having on databases? Who should be responsible for writing a managed schema? Should a managed schema be used for database consistency within an application? Where is the line drawn between the application putting the required information into the database, and the database’s job of maintaining consistency and integrity?
It’s somewhat ironic, since for a long time MySQL advocated using the application to ensure the consistency and integrity (ie, before MySQL had a storage engine with foreign keys and transactions).
I often say that the biggest reason MySQL is a widely used database is because it is fast. A fast database can be complemented by an application that adds the features the database is missing; but a slow database that is full-featured cannot be made faster by an application. So it worries me when folks request very specialized systems such as a messaging server (or ANY “push” system) into the database, because that could be easily done with a “pull” mechanism, only using the bandwidth needed by the instances of the applications that care. Otherwise, it will end up adding Microsoft-level bloat to a really nice and fast program.
Boston October MySQL User Group: see full event listings at:
http://mysql.meetup.com/137/calendar/5118339/
Tuesday, Oct. 10th at MIT, free pizza and soda (thanks to MySQL, AB and the MIT community). Please RSVP!!
To RSVP anonymously, please login to the Meetup site with the e-mail address “admin at sheeri dot com” and the password “guest”.
Plenty of free parking (you can park in MIT lots after 3 pm); 1 block south of the Kendall Square T stop.
————–
Most of the September Boston User Group was spent discussing an interesting problem with a large amount of data (5 million records). Basically, this data had about 40 boolean (or small set) fields that needed to be able to be searched against. Folks suggested:
1) Just leaving the table as is and using 1-character values
Pro: simple
Con: Indexes are bad for columns with low selectivity, searching will take a long time due to full table scans
2) Creating a “joining” table for each boolean value
Pro: Indexing for each boolean value can be used
Con: Complex — lots of tables, lots of joins for search
3) Using BIT(1) values or BIT(2) values and matching up booleans
Pro: Simple
Con: Difficult to write the search query, keeping in mind the search terms given below.
The biggest issue is the accuracy of indexes vs. size/amount of tables and joins. The person with the original problem (Chris) and I are doing a joint presentation, with real data on those three cases to figure out which is the best for his situation.
What have other folks done for boolean values? Please be specific about the amount of data, and the performance. Remember that this situation involves a lot of data and a lot of boolean fields, and searching across any or all boolean/small set fields is a core function. As well, fields may be null, and searching may include:
For boolean:
search for 0
search for 1
search for 0 or 1 (any value set)
search for NULL (any value not set)
search for 0 or NULL
search for 1 or NULL
For small sets:
search for ‘a’ (single value match)
search for ‘a’,’b’, and ‘c’ (multiple values will match)
search for ‘any value not null’ (anything not null)
search for ‘any value including null’ (anything null)
Any ideas? I will do some quick research if there’s another option that the September User Group did not come up with.
Boston October MySQL User Group: see full event listings at:
http://mysql.meetup.com/137/calendar/5118339/
Tuesday, Oct. 10th at MIT, free pizza and soda (thanks to MySQL, AB and the MIT community). Please RSVP!!
To RSVP anonymously, please login to the Meetup site with the e-mail address “admin at sheeri dot com” and the password “guest”.
Plenty of free parking (you can park in MIT lots after 3 pm); 1 block south of the Kendall Square T stop.
————–
Most of the September Boston User Group was spent discussing an interesting problem with a large amount of data (5 million records). Basically, this data had about 40 boolean (or small set) fields that needed to be able to be searched against. Folks suggested:
1) Just leaving the table as is and using 1-character values
Pro: simple
Con: Indexes are bad for columns with low selectivity, searching will take a long time due to full table scans
2) Creating a “joining” table for each boolean value
Pro: Indexing for each boolean value can be used
Con: Complex — lots of tables, lots of joins for search
3) Using BIT(1) values or BIT(2) values and matching up booleans
Pro: Simple
Con: Difficult to write the search query, keeping in mind the search terms given below.
The biggest issue is the accuracy of indexes vs. size/amount of tables and joins. The person with the original problem (Chris) and I are doing a joint presentation, with real data on those three cases to figure out which is the best for his situation.
What have other folks done for boolean values? Please be specific about the amount of data, and the performance. Remember that this situation involves a lot of data and a lot of boolean fields, and searching across any or all boolean/small set fields is a core function. As well, fields may be null, and searching may include:
For boolean:
search for 0
search for 1
search for 0 or 1 (any value set)
search for NULL (any value not set)
search for 0 or NULL
search for 1 or NULL
For small sets:
search for ‘a’ (single value match)
search for ‘a’,’b’, and ‘c’ (multiple values will match)
search for ‘any value not null’ (anything not null)
search for ‘any value including null’ (anything null)
Any ideas? I will do some quick research if there’s another option that the September User Group did not come up with.
Question #2 from the September MySQL User Group was whether or not a TRIGGER can affect a different table. Apparently the documentation (perhaps for an earlier version??) specified this was not possible. Tom Hanlon, MySQL employee, put up this example (modified from the original, special thanks to Ralph Navarro for copying it down):
Basically, this trigger will insert the current user and timestamp into another table.
mysql> delimiter $$
mysql> CREATE TRIGGER BIcity BEFORE INSERT ON city
-> FOR EACH ROW BEGIN
-> INSERT INTO citytest (name,happened) values (current_user(),now());
-> END;
-> $$
Query OK, 0 rows affected (0.03 sec)
mysql> delimiter ;
mysql> create table citytest (
name varchar(60) not null default '',
happened datetime not null);
Query OK, 0 rows affected (0.19 sec)
mysql> describe city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> insert into city (Name) values ('tomtown');
Query OK, 1 row affected (0.02 sec)
mysql> select * from citytest\G
*************************** 1. row ***************************
name: root@localhost
happened: 2006-09-07 21:45:14
1 row in set (0.01 sec)
Question #2 from the September MySQL User Group was whether or not a TRIGGER can affect a different table. Apparently the documentation (perhaps for an earlier version??) specified this was not possible. Tom Hanlon, MySQL employee, put up this example (modified from the original, special thanks to Ralph Navarro for copying it down):
Basically, this trigger will insert the current user and timestamp into another table.
mysql> delimiter $$
mysql> CREATE TRIGGER BIcity BEFORE INSERT ON city
-> FOR EACH ROW BEGIN
-> INSERT INTO citytest (name,happened) values (current_user(),now());
-> END;
-> $$
Query OK, 0 rows affected (0.03 sec)
mysql> delimiter ;
mysql> create table citytest (
name varchar(60) not null default '',
happened datetime not null);
Query OK, 0 rows affected (0.19 sec)
mysql> describe city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> insert into city (Name) values ('tomtown');
Query OK, 1 row affected (0.02 sec)
mysql> select * from citytest\G
*************************** 1. row ***************************
name: root@localhost
happened: 2006-09-07 21:45:14
1 row in set (0.01 sec)
I promised to write this up for for the folks who attended the Boston MySQL September User Group meeting, so here’s performance question #1 that was asked:
How can a bulk insert be speeded up?
We discussed disabling keys with
ALTER TABLE tblname DISABLE KEYS;
loading the data
ALTER TABLE tblname ENABLE KEYS;
However, as a post by Frank Mash not too long ago and comments explain, this has no effect on InnoDB tables.
For InnoDB tables, you can load the data in primary key order, which makes the loading much faster. Basically, InnoDB stores the data in primary key order on disk. If there is no primary key specified, the internal engine makes one anyway, so you might as well specify one and take advantage of it.
As well, you can SET UNIQUE CHECKS=0 before the load and SET UNIQUE CHECK=1 after the load if there are unique constraints. The final suggestion is to SET AUTOCOMMIT=0 before the load and SET AUTOCOMMIT=1 after the load, again to speed things up.
I promised to write this up for for the folks who attended the Boston MySQL September User Group meeting, so here’s performance question #1 that was asked:
How can a bulk insert be speeded up?
We discussed disabling keys with
ALTER TABLE tblname DISABLE KEYS;
loading the data
ALTER TABLE tblname ENABLE KEYS;
However, as a post by Frank Mash not too long ago and comments explain, this has no effect on InnoDB tables.
For InnoDB tables, you can load the data in primary key order, which makes the loading much faster. Basically, InnoDB stores the data in primary key order on disk. If there is no primary key specified, the internal engine makes one anyway, so you might as well specify one and take advantage of it.
As well, you can SET UNIQUE CHECKS=0 before the load and SET UNIQUE CHECK=1 after the load if there are unique constraints. The final suggestion is to SET AUTOCOMMIT=0 before the load and SET AUTOCOMMIT=1 after the load, again to speed things up.
| What: |
Free MySQL help with Tom Hanlon, MySQL employee.
at the Boston MySQL User Group |
| When: |
Thursday, September 7, 2006, 7:00 PM |
| Where: |
MIT Building E51, Room 372
Wadsworth and Amherst Streets
Cambridge , MA 02117 |
|
| Cost: |
Free |
| Transportation: |
1 block from Kendall Square T station;
free parking (MIT does not enforce their lot restrictions in the evenings,
so any signs except handicapped parking can safely be ignored) |
| RSVP: |
Free pizza and soda will be served, so please RSVP accurately.
To RSVP anonymously, go to http://www.meetup.com/login/ ,
login with the e-mail address “admin at sheeri dot com”
and the password “guest”, then click on “Add Guests”
and add 1 to the number of guests. |
The September Boston MySQL User Group Meeting will feature Tom Hanlon, MySQL employee, answering questions about performance issues (or anything else). Please feel free to bring theoretical questions as well as actual issues you’ve been having.
If you have a specific question, please bring descriptions of all relevant queries, tables, data samples, etc if you have a complex question. (see ********** below for more details)
You may submit a question to awfief@gmail.com or just bring it to the user group meeting.
We will be meeting on MIT campus, close to the Kendall stop on the Red Line (subway). There is also plenty of free parking — you can park in ANY MIT lot after 3 pm, even if it says “parking by permit only”. We are in building E51, room 372.
Here is the URL for the MIT Map with the location of this building:
http://whereis.mit.edu/map-jpg?selection=E51&Buildings=go
This map shows the MBTA Kendall Stop:
http://whereis.mit.edu/map-jpg?selection=L5&Landmarks=go
(the stop is in red on that map, and you can see E51 in the bottom right)
Here are the URL’s for the parking lots:
http://whereis.mit.edu/map-jpg?selection=P4&Parking=go
http://whereis.mit.edu/map-jpg?selection=P5&Parking=go
Free pizza and soda will be served, so please RSVP accurately.
To RSVP anonymously, please login to the Meetup site with the e-mail address “admin at sheeri dot com” and the password “guest”.
More information:
http://mysql.meetup.com/137/events/4976426/
**********
What to bring:
If you are submitting materials, you must submit your materials by noon the day of the user group meeting.
1) Either submit materials to Sheeri at awfief@gmail.com ahead of time or bring them on your laptop and be prepared to connect to the projector (we have the cables, just bring your laptop). Alternatively, you can make overhead projector slides and bring those.
2) Descriptions of relevant tables. Run the following for each table and bring the output:
SHOW CREATE TABLE tbl1\G
SHOW CREATE TABLE tbl2\G
3) Sample data for relevant tables. Run the following for each table and bring the output:
SELECT * FROM tbl1 ORDER BY RAND() LIMIT 5;
SELECT * FROM tbl2 ORDER BY RAND() LIMIT 5;
4) Query descriptions. Run the following for each query and bring the output:
The actual query, ie, “SELECT name FROM addresses WHERE city=’Boston’;”
The EXPLAIN output for the query, ie, EXPLAIN SELECT name FROM addresses WHERE city='Boston';
What you expect to get (data if the issue is inaccurate results, or a time estimate if the issue is slowness)
What you actually get (data if the issue is inaccurate results, or a time estimate if the issue is slowness)
| What: |
Free MySQL help with Tom Hanlon, MySQL employee.
at the Boston MySQL User Group |
| When: |
Thursday, September 7, 2006, 7:00 PM |
| Where: |
MIT Building E51, Room 372
Wadsworth and Amherst Streets
Cambridge , MA 02117 |
|
| Cost: |
Free |
| Transportation: |
1 block from Kendall Square T station;
free parking (MIT does not enforce their lot restrictions in the evenings,
so any signs except handicapped parking can safely be ignored) |
| RSVP: |
Free pizza and soda will be served, so please RSVP accurately.
To RSVP anonymously, go to http://www.meetup.com/login/ ,
login with the e-mail address “admin at sheeri dot com”
and the password “guest”, then click on “Add Guests”
and add 1 to the number of guests. |
The September Boston MySQL User Group Meeting will feature Tom Hanlon, MySQL employee, answering questions about performance issues (or anything else). Please feel free to bring theoretical questions as well as actual issues you’ve been having.
If you have a specific question, please bring descriptions of all relevant queries, tables, data samples, etc if you have a complex question. (see ********** below for more details)
You may submit a question to awfief@gmail.com or just bring it to the user group meeting.
We will be meeting on MIT campus, close to the Kendall stop on the Red Line (subway). There is also plenty of free parking — you can park in ANY MIT lot after 3 pm, even if it says “parking by permit only”. We are in building E51, room 372.
Here is the URL for the MIT Map with the location of this building:
http://whereis.mit.edu/map-jpg?selection=E51&Buildings=go
This map shows the MBTA Kendall Stop:
http://whereis.mit.edu/map-jpg?selection=L5&Landmarks=go
(the stop is in red on that map, and you can see E51 in the bottom right)
Here are the URL’s for the parking lots:
http://whereis.mit.edu/map-jpg?selection=P4&Parking=go
http://whereis.mit.edu/map-jpg?selection=P5&Parking=go
Free pizza and soda will be served, so please RSVP accurately.
To RSVP anonymously, please login to the Meetup site with the e-mail address “admin at sheeri dot com” and the password “guest”.
More information:
http://mysql.meetup.com/137/events/4976426/
**********
What to bring:
If you are submitting materials, you must submit your materials by noon the day of the user group meeting.
1) Either submit materials to Sheeri at awfief@gmail.com ahead of time or bring them on your laptop and be prepared to connect to the projector (we have the cables, just bring your laptop). Alternatively, you can make overhead projector slides and bring those.
2) Descriptions of relevant tables. Run the following for each table and bring the output:
SHOW CREATE TABLE tbl1\G
SHOW CREATE TABLE tbl2\G
3) Sample data for relevant tables. Run the following for each table and bring the output:
SELECT * FROM tbl1 ORDER BY RAND() LIMIT 5;
SELECT * FROM tbl2 ORDER BY RAND() LIMIT 5;
4) Query descriptions. Run the following for each query and bring the output:
The actual query, ie, “SELECT name FROM addresses WHERE city=’Boston’;”
The EXPLAIN output for the query, ie, EXPLAIN SELECT name FROM addresses WHERE city='Boston';
What you expect to get (data if the issue is inaccurate results, or a time estimate if the issue is slowness)
What you actually get (data if the issue is inaccurate results, or a time estimate if the issue is slowness)
Life has been super busy, but I have plenty of posting to catch up on. Fear not, there will be more content soon (after Monday, hopefully); I am working on editing a very relevant book, and I hope to be able to share my excitement after I finish.
Also on Monday is the next Boston MySQL User Group, which will go through how to do capacity planning in MySQL with very little pain and effort. In the beginning we will have 10 minutes for user questions, so we can all benefit from each other’s knowledge. I already have a user with a great question!
We have rebuilding our product all summer, with a deadline of releasing the product in the next 2 months. Our lead developer had put a surrogate key in our new schema about a month ago. He said he needed the surrogate key because “the ORM requires it.” I just accepted it.
My mistake was that I made an assumption. The table already had a primary key, but it was a composite key. I assumed that the ORM required a unique key to be one field, and thus I assumed he combined the 2 values in the primary key to get the unique key.
I was wrong. In adding the final subsystems into the schema this week, I noticed that the surrogate key was an auto-increment field. I also noticed he put it in every single table he dealt with. We had hours of meetings about schema, and this was NEVER put in as a requirement. Thus, today we had over three hours of agonizing discussions, including a back-and-forth of “If it’s going into the database I need to understand why,” and the lead developer saying “No you don’t.”
I severely wanted to say “If I don’t understand it, it does not go in the database,” but resisted. I asked him to send me the ORM so I could understand why it required the field. At first he said he would, and then kept talking to me about why I don’t need to understand the field; it didn’t add more overhead, it didn’t change the way the data relate to each other, etc.
I need to understand this because there may be other, similar requirements in the future. Or perhaps I’ll find a better way to do it (maybe a VIEW would work). Perhaps I’ll find other places where other fields need to be added. He finally explained that the API JDBC was using was awkward — it needs to retrieve basically the row number of any row it’s looking at, and if it deletes or changes the row number it uses the row number as the key to find the row.
Aha! That makes sense. However, why do the row numbers need to be in the database? Can’t it just retrieve the rows and put a row number in its own copy? Apparently, not. I cannot imagine that a mature technology would require something like that. It’s not that difficult to do. I said this, and the lead developer was insanely frustrated by it.
So I said, “Are you using Connector/J?” He was confused, but asked, “Is that JDBC?”
“Yes,” I replied. “Oh, then yes, we’re using it.”
“I don’t think so. If the interface is awkward, you’re not using Connector/J.”
He left my office. So I type in “Connector/J” into my MySQL Community Toolbar (I love it!) and find the following on http://www.mysql.com/products/connector/j/
New features from the JDBC-3.0 API in the latest production version of MySQL Connector/J include getGeneratedKeys which allows users to retrieve auto-increment fields in a non-database-specific way. Auto-increment fields now work with object-relational mapping tools, as well as Enterprise Java Beans (EJB) servers with Container Managed Persistence (CMP) that support JDBC-3.0.
Hrm….retrieve auto-increment fields in a non-database-specific way? I think that solves our problem!!!
[EDIT: I am, apparently wrong….but I cannot imagine that anyone using JDBC specifies an auto-increment field for EVERY SINGLE TABLE their application will touch. Do people actually do this?!?!?]
Life has been super busy, but I have plenty of posting to catch up on. Fear not, there will be more content soon (after Monday, hopefully); I am working on editing a very relevant book, and I hope to be able to share my excitement after I finish.
Also on Monday is the next Boston MySQL User Group, which will go through how to do capacity planning in MySQL with very little pain and effort. In the beginning we will have 10 minutes for user questions, so we can all benefit from each other’s knowledge. I already have a user with a great question!
We have rebuilding our product all summer, with a deadline of releasing the product in the next 2 months. Our lead developer had put a surrogate key in our new schema about a month ago. He said he needed the surrogate key because “the ORM requires it.” I just accepted it.
My mistake was that I made an assumption. The table already had a primary key, but it was a composite key. I assumed that the ORM required a unique key to be one field, and thus I assumed he combined the 2 values in the primary key to get the unique key.
I was wrong. In adding the final subsystems into the schema this week, I noticed that the surrogate key was an auto-increment field. I also noticed he put it in every single table he dealt with. We had hours of meetings about schema, and this was NEVER put in as a requirement. Thus, today we had over three hours of agonizing discussions, including a back-and-forth of “If it’s going into the database I need to understand why,” and the lead developer saying “No you don’t.”
I severely wanted to say “If I don’t understand it, it does not go in the database,” but resisted. I asked him to send me the ORM so I could understand why it required the field. At first he said he would, and then kept talking to me about why I don’t need to understand the field; it didn’t add more overhead, it didn’t change the way the data relate to each other, etc.
I need to understand this because there may be other, similar requirements in the future. Or perhaps I’ll find a better way to do it (maybe a VIEW would work). Perhaps I’ll find other places where other fields need to be added. He finally explained that the API JDBC was using was awkward — it needs to retrieve basically the row number of any row it’s looking at, and if it deletes or changes the row number it uses the row number as the key to find the row.
Aha! That makes sense. However, why do the row numbers need to be in the database? Can’t it just retrieve the rows and put a row number in its own copy? Apparently, not. I cannot imagine that a mature technology would require something like that. It’s not that difficult to do. I said this, and the lead developer was insanely frustrated by it.
So I said, “Are you using Connector/J?” He was confused, but asked, “Is that JDBC?”
“Yes,” I replied. “Oh, then yes, we’re using it.”
“I don’t think so. If the interface is awkward, you’re not using Connector/J.”
He left my office. So I type in “Connector/J” into my MySQL Community Toolbar (I love it!) and find the following on http://www.mysql.com/products/connector/j/
New features from the JDBC-3.0 API in the latest production version of MySQL Connector/J include getGeneratedKeys which allows users to retrieve auto-increment fields in a non-database-specific way. Auto-increment fields now work with object-relational mapping tools, as well as Enterprise Java Beans (EJB) servers with Container Managed Persistence (CMP) that support JDBC-3.0.
Hrm….retrieve auto-increment fields in a non-database-specific way? I think that solves our problem!!!
[EDIT: I am, apparently wrong….but I cannot imagine that anyone using JDBC specifies an auto-increment field for EVERY SINGLE TABLE their application will touch. Do people actually do this?!?!?]
Please feel free to forward to interested parties.
Who: Jim Starkey at the Boston MySQL User Group
What: Falcon, the new MySQL storage engine
When:
Monday, July 10, 2006 at 7:00 PM
Where:
MIT Building E51, Room 372
Wadsworth and Amherst Streets
Cambridge, MA 02117
Steps from the Red line, plenty of free parking.
The July Boston MySQL User Group’s topic is Falcon, the new storage engine for MySQL. Creator Jim Starkey will speak. Jim Starkey has been writing database software for 20 years. He created BLOBs, multi-versioning concurrency for relational databases, cascading update triggers, event alerters, and more. Read more about him at http://tinyurl.com/lno4p and http://tinyurl.com/mym7d.
We will be meeting on MIT campus, close to the Kendall stop on the Red Line (subway). There is also plenty of free parking — you can park in ANY MIT lot after 3 pm, even if it says “parking by permit only”. We are in building E51, room 372.
Here is the URL for the MIT Map with the location of this building:
http://whereis.mit.edu/map-jpg?selection=E51&Buildings=go
This map shows the MBTA Kendall Stop:
http://whereis.mit.edu/map-jpg?selection=L5&Landmarks=go
(the stop is in red on that map, and you can see E51 in the bottom right)
Here are the URL’s for the parking lots:
http://whereis.mit.edu/map-jpg?selection=P4&Parking=go
http://whereis.mit.edu/map-jpg?selection=P5&Parking=go
Free pizza and soda will be served, so please RSVP accurately and arrive a few minutes early.
To RSVP anonymously, please login to the Meetup site (http://mysql.meetup.com/137) with the e-mail address “admin at sheeri dot com” and the password of “guest”.
Sponsors welcome, call Sheeri at 857-205-9786 for details.
Please feel free to forward to interested parties.
Who: Jim Starkey at the Boston MySQL User Group
What: Falcon, the new MySQL storage engine
When:
Monday, July 10, 2006 at 7:00 PM
Where:
MIT Building E51, Room 372
Wadsworth and Amherst Streets
Cambridge, MA 02117
Steps from the Red line, plenty of free parking.
The July Boston MySQL User Group’s topic is Falcon, the new storage engine for MySQL. Creator Jim Starkey will speak. Jim Starkey has been writing database software for 20 years. He created BLOBs, multi-versioning concurrency for relational databases, cascading update triggers, event alerters, and more. Read more about him at http://tinyurl.com/lno4p and http://tinyurl.com/mym7d.
We will be meeting on MIT campus, close to the Kendall stop on the Red Line (subway). There is also plenty of free parking — you can park in ANY MIT lot after 3 pm, even if it says “parking by permit only”. We are in building E51, room 372.
Here is the URL for the MIT Map with the location of this building:
http://whereis.mit.edu/map-jpg?selection=E51&Buildings=go
This map shows the MBTA Kendall Stop:
http://whereis.mit.edu/map-jpg?selection=L5&Landmarks=go
(the stop is in red on that map, and you can see E51 in the bottom right)
Here are the URL’s for the parking lots:
http://whereis.mit.edu/map-jpg?selection=P4&Parking=go
http://whereis.mit.edu/map-jpg?selection=P5&Parking=go
Free pizza and soda will be served, so please RSVP accurately and arrive a few minutes early.
To RSVP anonymously, please login to the Meetup site (http://mysql.meetup.com/137) with the e-mail address “admin at sheeri dot com” and the password of “guest”.
Sponsors welcome, call Sheeri at 857-205-9786 for details.
Note the title is “How I have a successful MySQL User Group.” There’s more than one way to do it, I’m sure. There are 3 basic principles:
1) Try to do as little work as possible.
2) Make your colleagues do as little work as possible.
3) Always have a topic/presentation
These three principles will get you far, and should be weighted equally. Do not use principle # 1 as an excuse to not follow principle #3. As well, “doing work” includes “paying money”. With that being said:
- Make your user group easy to get to. This has different meanings for different areas. It may mean near a major highway interchange, it may mean near a mass transit station. Whatever it means for you, make it easy.
When the Boston MySQL User Group first started, we had free space in an office building right in the city of Boston.
| Pros |
Cons |
| Free |
Not enough parking |
| Close to the subway and train station |
No free parking |
| The street was clearly labeled |
We had to have a person stand by the door to let people in |
| The building was clearly labeled |
| There was a great pub next door |
Then we moved to a space at MIT:
| Pros |
Cons |
| Free |
Passers-by eat the pizza and drink the soda |
| Close to the subway and train station |
We had to have good maps to find the location |
| Plenty of free parking |
Now, you may not be so lucky to find a place that will give you space for free. Consider local universities. Also, local libraries (university or otherwise) usually have some meeting space where they might be able to host you. Our first space, an office space, was gotten through a contact at MySQL. Contact some companies who use MySQL and ask if they’ll lend you space — most tech companies have folks there at night anyway, and it’s free advertising for the company! There is a book company near you, or a Pearson VUE center. Remember: it does not hurt to ask. The worst people can say is “no”, and if it’s contributing toward education, a book company (Pearson Education, Apress, O’Reilly, etc) might sponsor it.
Getting a free space is key, particularly if it has A/V equipment for you to use (if you want to have lectures).
Granted, if you want to just have a freeform discussion, any pub or coffee shop or location will do.
You may think about having it in your home. Consider issues of personal safety, domestic distractions (kids, pets, etc), parking and transit, and the fact that most people feel comfortable going to their first meeting on “neutral” ground. However, if the culture surrounding where you live encourages it, go for it!
If you must pay for a location, get a company to sponsor it. Get their monetary and time commitment in writing if you can (ie, they will sponsor for a year).
- Make the meetings easy to remember. Have them on the same day of the month (ie, 2nd Monday). Do not make them conflict with something similar (ie, PHP group, linux group). Try to have it at the same place each time.
- Figure out what your group wants and give it to them.
Do they want lectures on specific topics? Maybe go to a place with wireless access and a “troubleshooting” meeting every so often. Maybe they want a blog with news, or war stories. Maybe a lending library would work. Anyone that comes to your meeting is looking for something. Ask each new member what they’re looking for. If they say “to learn more” then go with lectures — make sure to accomodate all skill sets, and have advanced lectures as well as beginning lectures. If they want to get to know each other better, go to a pub and talk about MySQL while you down a pint. Or run a charity fundraiser and donate the proceeds to a not-for-profit like the Electronic Freedom Foundation. There’s plenty the group can do, including having contests, or even starting a business together if you’re plucky.
- Have incentives.
But remember rule #2, so keep costs as low as possible. Ask companies to sponsor dinner or light refreshments. Don’t be shy; ask book companies for books, T-shirts and buttons to give away as prizes. Again, the worst they can say is “no”, and I’ve found book companies and MySQL AB to be VERY accomodating, as well as local businesses. Ask company techies to present. Ask MySQL to send someone out to present.
- Be available, and follow up when you say you will. Often times folks will have a question. Being able to respond in a timely manner is important. But if you speak out and make regular annoucements, and solicit feedback, folks will know you are accomodating.
- Do not do work others can do. When someone asks if you can forward a job listing, have them post it to your group members. Or, better yet, have them come to your meeting and make an announcement in the first 5 minutes. This includes advertising! Find your local MySQL Sales Rep and ask them to help promote your group. Promote it on any site you can think of — in a blog, on Craigslist, heck, make flyers and post it around town if you have to. Get the word out there!
- Have the right attitude. It’s not your meeting, it’s your group’s meeting. If they want different topics, ask someone to research and present. It’s OK if the research isn’t perfect or complete; many times your group will fill in with their experiences.
- It’s OK to be wrong. No, really. If you don’t know the answer to a question, look it up later on, and follow up. Or ask the group if anyone knows. Also, presentations take time to make, and if you feel compelled to get every single detail right, they’ll take a lot longer. Keep an offline copy of the manual so you can look up anything you need to.
- Ask others for help. Even if you think you can do it better or faster. Your group should be able to survive without you, so if you can’t make a meeting all is not lost. This includes asking others to do a presentation of any length — it can be a 10 minute “this is how we use MySQL and this is what our setup looks like” to an hour or more. It does not have to be intimidating. Ask folks to present workshops they’re presenting at conferences, or present notes from conferences they go to. People need to feel like part of the group if the group is to be successful.
- If you can’t get a presentation, download one. MySQL is excellent at providing past webinars. Download it to your computer, and have a free lecture from an expert, that you can pause and rewind if you need to. MySQL Webinars. The Boston MySQL User Group videorecords the presentations and puts them online — see the links under “Presentations” at http://www.sheeri.net
Note: The Boston MySQL User Group is successful, but we don’t quite do everything above. I’d love to have more socialization, and I think once summer comes I’ll think of something geeky and fun to do outdoors, with little cost. Or maybe just a bowling trip.
Note the title is “How I have a successful MySQL User Group.” There’s more than one way to do it, I’m sure. There are 3 basic principles:
1) Try to do as little work as possible.
2) Make your colleagues do as little work as possible.
3) Always have a topic/presentation
These three principles will get you far, and should be weighted equally. Do not use principle # 1 as an excuse to not follow principle #3. As well, “doing work” includes “paying money”. With that being said:
- Make your user group easy to get to. This has different meanings for different areas. It may mean near a major highway interchange, it may mean near a mass transit station. Whatever it means for you, make it easy.
When the Boston MySQL User Group first started, we had free space in an office building right in the city of Boston.
| Pros |
Cons |
| Free |
Not enough parking |
| Close to the subway and train station |
No free parking |
| The street was clearly labeled |
We had to have a person stand by the door to let people in |
| The building was clearly labeled |
| There was a great pub next door |
Then we moved to a space at MIT:
| Pros |
Cons |
| Free |
Passers-by eat the pizza and drink the soda |
| Close to the subway and train station |
We had to have good maps to find the location |
| Plenty of free parking |
Now, you may not be so lucky to find a place that will give you space for free. Consider local universities. Also, local libraries (university or otherwise) usually have some meeting space where they might be able to host you. Our first space, an office space, was gotten through a contact at MySQL. Contact some companies who use MySQL and ask if they’ll lend you space — most tech companies have folks there at night anyway, and it’s free advertising for the company! There is a book company near you, or a Pearson VUE center. Remember: it does not hurt to ask. The worst people can say is “no”, and if it’s contributing toward education, a book company (Pearson Education, Apress, O’Reilly, etc) might sponsor it.
Getting a free space is key, particularly if it has A/V equipment for you to use (if you want to have lectures).
Granted, if you want to just have a freeform discussion, any pub or coffee shop or location will do.
You may think about having it in your home. Consider issues of personal safety, domestic distractions (kids, pets, etc), parking and transit, and the fact that most people feel comfortable going to their first meeting on “neutral” ground. However, if the culture surrounding where you live encourages it, go for it!
If you must pay for a location, get a company to sponsor it. Get their monetary and time commitment in writing if you can (ie, they will sponsor for a year).
- Make the meetings easy to remember. Have them on the same day of the month (ie, 2nd Monday). Do not make them conflict with something similar (ie, PHP group, linux group). Try to have it at the same place each time.
- Figure out what your group wants and give it to them.
Do they want lectures on specific topics? Maybe go to a place with wireless access and a “troubleshooting” meeting every so often. Maybe they want a blog with news, or war stories. Maybe a lending library would work. Anyone that comes to your meeting is looking for something. Ask each new member what they’re looking for. If they say “to learn more” then go with lectures — make sure to accomodate all skill sets, and have advanced lectures as well as beginning lectures. If they want to get to know each other better, go to a pub and talk about MySQL while you down a pint. Or run a charity fundraiser and donate the proceeds to a not-for-profit like the Electronic Freedom Foundation. There’s plenty the group can do, including having contests, or even starting a business together if you’re plucky.
- Have incentives.
But remember rule #2, so keep costs as low as possible. Ask companies to sponsor dinner or light refreshments. Don’t be shy; ask book companies for books, T-shirts and buttons to give away as prizes. Again, the worst they can say is “no”, and I’ve found book companies and MySQL AB to be VERY accomodating, as well as local businesses. Ask company techies to present. Ask MySQL to send someone out to present.
- Be available, and follow up when you say you will. Often times folks will have a question. Being able to respond in a timely manner is important. But if you speak out and make regular annoucements, and solicit feedback, folks will know you are accomodating.
- Do not do work others can do. When someone asks if you can forward a job listing, have them post it to your group members. Or, better yet, have them come to your meeting and make an announcement in the first 5 minutes. This includes advertising! Find your local MySQL Sales Rep and ask them to help promote your group. Promote it on any site you can think of — in a blog, on Craigslist, heck, make flyers and post it around town if you have to. Get the word out there!
- Have the right attitude. It’s not your meeting, it’s your group’s meeting. If they want different topics, ask someone to research and present. It’s OK if the research isn’t perfect or complete; many times your group will fill in with their experiences.
- It’s OK to be wrong. No, really. If you don’t know the answer to a question, look it up later on, and follow up. Or ask the group if anyone knows. Also, presentations take time to make, and if you feel compelled to get every single detail right, they’ll take a lot longer. Keep an offline copy of the manual so you can look up anything you need to.
- Ask others for help. Even if you think you can do it better or faster. Your group should be able to survive without you, so if you can’t make a meeting all is not lost. This includes asking others to do a presentation of any length — it can be a 10 minute “this is how we use MySQL and this is what our setup looks like” to an hour or more. It does not have to be intimidating. Ask folks to present workshops they’re presenting at conferences, or present notes from conferences they go to. People need to feel like part of the group if the group is to be successful.
- If you can’t get a presentation, download one. MySQL is excellent at providing past webinars. Download it to your computer, and have a free lecture from an expert, that you can pause and rewind if you need to. MySQL Webinars. The Boston MySQL User Group videorecords the presentations and puts them online — see the links under “Presentations” at http://www.sheeri.net
Note: The Boston MySQL User Group is successful, but we don’t quite do everything above. I’d love to have more socialization, and I think once summer comes I’ll think of something geeky and fun to do outdoors, with little cost. Or maybe just a bowling trip.
Yet again, the Boston MySQL User Group was a success. Larry Stefonic, Senior Vice President, Worldwide OEM & Embedded Sales and President, MySQL K.K. (Japan) was on hand to witness the event. There were 15 people total at the user group, which is our lowest yet, but I’ll get to why I was not disappointed at all later on.
However, the topic was advanced: “Measuring MySQL Server Performance for the Sensor Data Stream Processing” presented by faithful MySQL User Group attendee Jacob Nikom, from MIT Lincoln Labs. (he’s been to every user group meeting!)
And I was very impressed with the questions folks asked of Jacob — the group was half the size of what we usually are, but we were all fully engaged. I’m very proud of the MySQL group! We have a diverse range of skills and I’m glad we can accomodate all of them.
Next month’s topic will be “Storing images in a database,” which was pushed out of the way in April to make room for Jay Pipes’ wonderful presentation. As well, we hope to have a special User Group before then with an extra special MySQL guest. More details on that when they happen.
Yet again, the Boston MySQL User Group was a success. Larry Stefonic, Senior Vice President, Worldwide OEM & Embedded Sales and President, MySQL K.K. (Japan) was on hand to witness the event. There were 15 people total at the user group, which is our lowest yet, but I’ll get to why I was not disappointed at all later on.
However, the topic was advanced: “Measuring MySQL Server Performance for the Sensor Data Stream Processing” presented by faithful MySQL User Group attendee Jacob Nikom, from MIT Lincoln Labs. (he’s been to every user group meeting!)
And I was very impressed with the questions folks asked of Jacob — the group was half the size of what we usually are, but we were all fully engaged. I’m very proud of the MySQL group! We have a diverse range of skills and I’m glad we can accomodate all of them.
Next month’s topic will be “Storing images in a database,” which was pushed out of the way in April to make room for Jay Pipes’ wonderful presentation. As well, we hope to have a special User Group before then with an extra special MySQL guest. More details on that when they happen.
Do you have a users group near you? Is it thriving? In conjunction with Mike Kruckenberg, I’ve been running the Boston MySQL Users Group for 6 months. Every meeting draws 30-60 members, and members overall are quite satisfied.
How do we do it? If you’re at the MySQL Users Conference, come find out at the MySQL Users Group BOF to get tips and tricks, or to share your own success stories and pitfalls. 8:30 -9:30 pm, Tuesday April 25th.
Do you have a users group near you? Is it thriving? In conjunction with Mike Kruckenberg, I’ve been running the Boston MySQL Users Group for 6 months. Every meeting draws 30-60 members, and members overall are quite satisfied.
How do we do it? If you’re at the MySQL Users Conference, come find out at the MySQL Users Group BOF to get tips and tricks, or to share your own success stories and pitfalls. 8:30 -9:30 pm, Tuesday April 25th.