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.
Given the following scenario:
| Addresses |
| name |
group |
city |
| Johnny |
Friends |
Cambridge |
| Kevin |
Friends |
Allston |
| Justin |
Coworkers |
Easton |
| Aya |
Coworkers |
North Andover |
| Josh |
Family |
Groton |
| Connie |
Family |
Easton |
In a database, that could be represented as such:
CREATE TABLE GroupNum (
groupId TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
groupName VARCHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE AddressNum (
firstName VARCHAR(20),
groupId TINYINT UNSIGNED NOT NULL,
city VARCHAR(20),
KEY (groupId),
FOREIGN KEY (groupId) REFERENCES GroupNum (groupId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO GroupNum (groupName) VALUES ('Friends'),('Coworkers'),('Family');
INSERT INTO AddressNum (firstName,groupId,city) VALUES
('Johnny',1,'Cambridge'),
('Kevin',1,'Allston'),
('Justin',2,'Easton'),
('Aya',2,'North Andover'),
('Josh',3,'Groton'),
('Connie',3,'Easton');
SELECT firstName,groupName,city
FROM AddressNum INNER JOIN GroupNum USING (groupId);
| firstName |
groupName |
city |
| Johnny |
Friends |
Cambridge |
| Kevin |
Friends |
Allston |
| Justin |
Coworkers |
Easton |
| Aya |
Coworkers |
North Andover |
| Josh |
Family |
Groton |
| Connie |
Family |
Easton |
or, using descriptive foreign keys:
CREATE TABLE GroupText (
groupName VARCHAR(20) NOT NULL PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE AddressText (
firstName VARCHAR(20),
groupName VARCHAR(20) NOT NULL,
city VARCHAR(20),
KEY (groupName),
FOREIGN KEY (groupName) REFERENCES GroupText (groupName)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO GroupText (groupName) VALUES ('Friends'),('Coworkers'),('Family');
INSERT INTO AddressText (firstName,groupName,city) VALUES
('Johnny','Friends','Cambridge'),
('Kevin','Friends','Allston'),
('Justin','Coworkers','Easton'),
('Aya','Coworkers','North Andover'),
('Josh','Family','Groton'),
('Connie','Family','Easton');
SELECT firstName,groupName,city
FROM AddressText;
| firstName |
groupName |
city |
| Johnny |
Friends |
Cambridge |
| Kevin |
Friends |
Allston |
| Justin |
Coworkers |
Easton |
| Aya |
Coworkers |
North Andover |
| Josh |
Family |
Groton |
| Connie |
Family |
Easton |
So what are the pros and cons of using descriptive foreign keys, as opposed to surrogate keys?
Con:
1) An auto-increment surrogate key (a number) is referenced faster than strings, so INSERTS will be slightly slower.
2) Data redundancy. Foreign keys need to be updated (and/or deleted) whenever the data is updated.
Pro:
1) Using a surrogate key means that to get user-readable data (ie, the text) a JOIN must be made. Using a descriptive foreign key, no JOIN needs to be made to get user-readable data. This includes reading with SELECTs and when INSERTing or modifying data. For example, to insert into AddressNum the groupId is needed.
2) Updates/deletes on a foreign key can be set to cascade.
3) Human-readable data in the database. This will make it easier for the DBA to debug data issues, as they can use a SHOW CREATE TABLE to find the foreign key references, and then get a sample of data from one table and understand the scenario, without having to JOIN.
My thinking right now is that I would use a descriptive foreign key where the forign key is “static” data, or the foreign key references are used in a small number of tables.
For instance, in a user-based application, I would not use “username” as a descriptive foreign key, I would stick with a surrogate key of “uid”, because the foreign key is ubiquitous, and if a user changed their username, every single table would need to cascade — or else the update would fail.
However, I would use descriptive foreign keys for “static” data like locale (language) data, country and state names, because they likely will not change, even though they might affect many changes. Athough note that in these places, I’m also using standards — “locale” for language, ISO country and state/province codes, etc.
I would also use it in the scenario above, where the data is not static, but also does not touch many tables — the ‘group name’ only affects the ‘listings’ table if it gets changed.
So, what are the pros and cons of this method? When would you and would you not use it?
Given the following scenario:
| Addresses |
| name |
group |
city |
| Johnny |
Friends |
Cambridge |
| Kevin |
Friends |
Allston |
| Justin |
Coworkers |
Easton |
| Aya |
Coworkers |
North Andover |
| Josh |
Family |
Groton |
| Connie |
Family |
Easton |
In a database, that could be represented as such:
CREATE TABLE GroupNum (
groupId TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
groupName VARCHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE AddressNum (
firstName VARCHAR(20),
groupId TINYINT UNSIGNED NOT NULL,
city VARCHAR(20),
KEY (groupId),
FOREIGN KEY (groupId) REFERENCES GroupNum (groupId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO GroupNum (groupName) VALUES ('Friends'),('Coworkers'),('Family');
INSERT INTO AddressNum (firstName,groupId,city) VALUES
('Johnny',1,'Cambridge'),
('Kevin',1,'Allston'),
('Justin',2,'Easton'),
('Aya',2,'North Andover'),
('Josh',3,'Groton'),
('Connie',3,'Easton');
SELECT firstName,groupName,city
FROM AddressNum INNER JOIN GroupNum USING (groupId);
| firstName |
groupName |
city |
| Johnny |
Friends |
Cambridge |
| Kevin |
Friends |
Allston |
| Justin |
Coworkers |
Easton |
| Aya |
Coworkers |
North Andover |
| Josh |
Family |
Groton |
| Connie |
Family |
Easton |
or, using descriptive foreign keys:
CREATE TABLE GroupText (
groupName VARCHAR(20) NOT NULL PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE AddressText (
firstName VARCHAR(20),
groupName VARCHAR(20) NOT NULL,
city VARCHAR(20),
KEY (groupName),
FOREIGN KEY (groupName) REFERENCES GroupText (groupName)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO GroupText (groupName) VALUES ('Friends'),('Coworkers'),('Family');
INSERT INTO AddressText (firstName,groupName,city) VALUES
('Johnny','Friends','Cambridge'),
('Kevin','Friends','Allston'),
('Justin','Coworkers','Easton'),
('Aya','Coworkers','North Andover'),
('Josh','Family','Groton'),
('Connie','Family','Easton');
SELECT firstName,groupName,city
FROM AddressText;
| firstName |
groupName |
city |
| Johnny |
Friends |
Cambridge |
| Kevin |
Friends |
Allston |
| Justin |
Coworkers |
Easton |
| Aya |
Coworkers |
North Andover |
| Josh |
Family |
Groton |
| Connie |
Family |
Easton |
So what are the pros and cons of using descriptive foreign keys, as opposed to surrogate keys?
Con:
1) An auto-increment surrogate key (a number) is referenced faster than strings, so INSERTS will be slightly slower.
2) Data redundancy. Foreign keys need to be updated (and/or deleted) whenever the data is updated.
Pro:
1) Using a surrogate key means that to get user-readable data (ie, the text) a JOIN must be made. Using a descriptive foreign key, no JOIN needs to be made to get user-readable data. This includes reading with SELECTs and when INSERTing or modifying data. For example, to insert into AddressNum the groupId is needed.
2) Updates/deletes on a foreign key can be set to cascade.
3) Human-readable data in the database. This will make it easier for the DBA to debug data issues, as they can use a SHOW CREATE TABLE to find the foreign key references, and then get a sample of data from one table and understand the scenario, without having to JOIN.
My thinking right now is that I would use a descriptive foreign key where the forign key is “static” data, or the foreign key references are used in a small number of tables.
For instance, in a user-based application, I would not use “username” as a descriptive foreign key, I would stick with a surrogate key of “uid”, because the foreign key is ubiquitous, and if a user changed their username, every single table would need to cascade — or else the update would fail.
However, I would use descriptive foreign keys for “static” data like locale (language) data, country and state names, because they likely will not change, even though they might affect many changes. Athough note that in these places, I’m also using standards — “locale” for language, ISO country and state/province codes, etc.
I would also use it in the scenario above, where the data is not static, but also does not touch many tables — the ‘group name’ only affects the ‘listings’ table if it gets changed.
So, what are the pros and cons of this method? When would you and would you not use it?
| 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)
Developers* seem to think of a transaction as a block of code. A try/catch block seems analogous at first glance — try to do a series of actions, and if one fails, roll them all back, else commit. So many people trip up and try to write code within a transaction to avoid deadlock or race conditions.
This is the source of the problems with the commands that perform an implicit COMMIT (that links to the 4.1 and under documentation, be sure to check manual for the specific version you are using). Implicit COMMITs can throw a lot of unexpected bugs in, so try to use those commands wisely. Transactions are session-specific. So a COMMIT, implicit or otherwise, affects any existing transaction within that session — as START TRANSACTION performs an implicit commit, you can only work on one transaction at a time in a session. The SQL Standard provides the START TRANSACTION, ROLLBACK and COMMIT syntax.
However, a transaction really it should be treated as a command. The point of a transaction is to be atomic. It is designed put a few tasks in a natural group. The beauty of a declarative language like SQL is that I tell it what I want, not how to do it. The optimizer figures out how to do it. In procedural and object-oriented programming, I specify how to do something.
Trying to fix a race condition or deadlock by using locking within a transaction defeats the point of a transaction. Locking a table, changing the schema, or starting another transaction, is not a natural grouping. I keep using “natural” but that is my made-up term for it.
Think of a joint bank account — if one person uses their bank card to pay at a restaurant, the waiter will go to the terminal and authorize an amount — larger than the bill, probably 30% larger, to ensure that when the gratuity is added, there is enough money in the account to cover it. After the tip is added and the bill signed, the receipt gets put into a pile, and once a day a batch is run, which is when the money actually gets taken out of your account.
Now imagine the other person fills their tank with gasoline. When the bank card is swiped at the gas pump, an amount is authorized**, and the driver is allowed to start filling the tank. At the end of the fill, the exact amount is determined and sent off to the bank for processing (or done in a batch, as above).
What banks and credit cards do is authorize the amount and “hold” it — this is why the “available” bank balance may be different than the “actual” bank balance. That is the “natural” way to avoid a race condition. What developers try to do by adding “unnatural” statements such as locking tables, or transactions within transactions, is analogous to a bank skipping authorization and not allowing any transactions to occur until a payment clears.
This is obviously primitive to folks who want to purchase more than one item per day with their card. And yet, developers foam at the mouth, cursing the database engine (all have implicit commits, MySQL is not alone) for not doing what they tell it to. Of course the database engine does not do what you tell it to — it is not designed to. It is designed to give you the answer you want, not follow the path you specify.
So the moral of the story is that care should be taken that transactions actually encompass the very smallest atomic operation, not “things to group in a block”. Stored procedures actually help in the matter of getting around the issue folks have with implicit commits; in fact, while researching this article I found that the MySQL Reference Manual for Version 3.23.10 alpha states:
For the moment, we are much more for implementing the SQL server language (something like stored procedures). With this you would very seldom really need COMMIT-ROLLBACK. This would also give much better performance.
* When I say developers, I mean “people who think like developers” meaning they do not grok the declarative nature of SQL.
** I found this out the hard way driving from Boston to Chicago and back in a 24′ moving truck. I was filling the tank and the pump stopped filling at $50, and I had to re-swipe my debit card.
Developers* seem to think of a transaction as a block of code. A try/catch block seems analogous at first glance — try to do a series of actions, and if one fails, roll them all back, else commit. So many people trip up and try to write code within a transaction to avoid deadlock or race conditions.
This is the source of the problems with the commands that perform an implicit COMMIT (that links to the 4.1 and under documentation, be sure to check manual for the specific version you are using). Implicit COMMITs can throw a lot of unexpected bugs in, so try to use those commands wisely. Transactions are session-specific. So a COMMIT, implicit or otherwise, affects any existing transaction within that session — as START TRANSACTION performs an implicit commit, you can only work on one transaction at a time in a session. The SQL Standard provides the START TRANSACTION, ROLLBACK and COMMIT syntax.
However, a transaction really it should be treated as a command. The point of a transaction is to be atomic. It is designed put a few tasks in a natural group. The beauty of a declarative language like SQL is that I tell it what I want, not how to do it. The optimizer figures out how to do it. In procedural and object-oriented programming, I specify how to do something.
Trying to fix a race condition or deadlock by using locking within a transaction defeats the point of a transaction. Locking a table, changing the schema, or starting another transaction, is not a natural grouping. I keep using “natural” but that is my made-up term for it.
Think of a joint bank account — if one person uses their bank card to pay at a restaurant, the waiter will go to the terminal and authorize an amount — larger than the bill, probably 30% larger, to ensure that when the gratuity is added, there is enough money in the account to cover it. After the tip is added and the bill signed, the receipt gets put into a pile, and once a day a batch is run, which is when the money actually gets taken out of your account.
Now imagine the other person fills their tank with gasoline. When the bank card is swiped at the gas pump, an amount is authorized**, and the driver is allowed to start filling the tank. At the end of the fill, the exact amount is determined and sent off to the bank for processing (or done in a batch, as above).
What banks and credit cards do is authorize the amount and “hold” it — this is why the “available” bank balance may be different than the “actual” bank balance. That is the “natural” way to avoid a race condition. What developers try to do by adding “unnatural” statements such as locking tables, or transactions within transactions, is analogous to a bank skipping authorization and not allowing any transactions to occur until a payment clears.
This is obviously primitive to folks who want to purchase more than one item per day with their card. And yet, developers foam at the mouth, cursing the database engine (all have implicit commits, MySQL is not alone) for not doing what they tell it to. Of course the database engine does not do what you tell it to — it is not designed to. It is designed to give you the answer you want, not follow the path you specify.
So the moral of the story is that care should be taken that transactions actually encompass the very smallest atomic operation, not “things to group in a block”. Stored procedures actually help in the matter of getting around the issue folks have with implicit commits; in fact, while researching this article I found that the MySQL Reference Manual for Version 3.23.10 alpha states:
For the moment, we are much more for implementing the SQL server language (something like stored procedures). With this you would very seldom really need COMMIT-ROLLBACK. This would also give much better performance.
* When I say developers, I mean “people who think like developers” meaning they do not grok the declarative nature of SQL.
** I found this out the hard way driving from Boston to Chicago and back in a 24′ moving truck. I was filling the tank and the pump stopped filling at $50, and I had to re-swipe my debit card.
At the July MySQL User Group Meeting, Jim Starkey wondered aloud, “What happens when I COMMIT on a memory table?” I wrote the question down, to research it later.
The obvious answer is “COMMIT on a non-transactional table does nothing.”
Tonight I was thinking about this, and I realized I do not actually COMMIT “on a table.”
The manual page at: http://dev.mysql.com/doc/refman/4.1/en/commit.html (and the 5.0 and 5.1 equivalents) state:
By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk.
If you are using a transaction-safe storage engine (such as InnoDB, BDB, or NDB Cluster), you can disable autocommit mode with the following statement:
SET AUTOCOMMIT=0;
But what does If you are using a transaction-safe storage engine really mean? I ask because I do not specify a table type with that command. So I tried it on a fresh MySQL install (5.0.19 GA, on a RedHat Linux Enterprise machine):
[skritzer]$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select distinct table_schema,engine from information_schema.tables\G
*************************** 1. row ***************************
table_schema: information_schema
engine: MEMORY
*************************** 2. row ***************************
table_schema: information_schema
engine: MyISAM
*************************** 3. row ***************************
table_schema: mysql
engine: MyISAM
3 rows in set (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET autocommit=1;
Query OK, 0 rows affected (0.00 sec)
As you can see, this machine has no user-created tables (seriously, it was just installed today, that user connection id of 1 is because it was the first time I logged into it). From my playing around, I can extrapolate that If you are using a transaction-safe storage engine really means If you have a transaction-safe storage engine enabled.
Let’s try it out:
mysql> show engines\G
*************************** 1. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
*************************** 4. row ***************************
Engine: BerkeleyDB
Support: NO
Comment: Supports transactions and page-level locking
*************************** 5. row ***************************
Engine: BLACKHOLE
Support: NO
Comment: /dev/null storage engine (anything you write to it disappears)
*************************** 6. row ***************************
Engine: EXAMPLE
Support: NO
Comment: Example storage engine
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
*************************** 8. row ***************************
Engine: CSV
Support: NO
Comment: CSV storage engine
*************************** 9. row ***************************
Engine: ndbcluster
Support: NO
Comment: Clustered, fault-tolerant, memory-based tables
*************************** 10. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
*************************** 11. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
*************************** 12. row ***************************
Engine: ISAM
Support: NO
Comment: Obsolete storage engine
12 rows in set (0.00 sec)
mysql> exit
Bye
[skritzer]$ ls -l /etc/my.cnf
ls: /etc/my.cnf: No such file or directory
[skritzer]$ sudo vi /etc/my.cnf
We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these two things:
#1) Respect the privacy of others.
#2) Think before you type.
Password:
[mysqld]
skip-innodb
:wq
"/etc/my.cnf" 2L, 21C written
[skritzer]$ sudo /etc/init.d/mysql stop
Shutting down MySQL.. [ OK ]
[skritzer]$ ps -ef | grep mysql
skritzer 7253 7179 0 21:07 pts/0 00:00:00 grep mysql
[skritzer]$ sudo /etc/init.d/mysql start
Starting MySQL[ OK ]
[skritzer]$ ps -ef | grep mysql
root 7261 1 0 21:07 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/mysql.pid
mysql 7282 7261 0 21:07 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/mysql.pid --skip-locking
skritzer 7290 7179 0 21:07 pts/0 00:00:00 grep mysql
[skritzer]$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show engines\G
*************************** 1. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
*************************** 4. row ***************************
Engine: BerkeleyDB
Support: NO
Comment: Supports transactions and page-level locking
*************************** 5. row ***************************
Engine: BLACKHOLE
Support: NO
Comment: /dev/null storage engine (anything you write to it disappears)
*************************** 6. row ***************************
Engine: EXAMPLE
Support: NO
Comment: Example storage engine
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
*************************** 8. row ***************************
Engine: CSV
Support: NO
Comment: CSV storage engine
*************************** 9. row ***************************
Engine: ndbcluster
Support: NO
Comment: Clustered, fault-tolerant, memory-based tables
*************************** 10. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
*************************** 11. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
*************************** 12. row ***************************
Engine: ISAM
Support: NO
Comment: Obsolete storage engine
12 rows in set (0.00 sec)
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET autocommit=1;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
No errors or warnings are generated. So technically speaking, I can SET autocommit=0 no matter what storage engines are in use, for any of the varying definitions of in use.
At the July MySQL User Group Meeting, Jim Starkey wondered aloud, “What happens when I COMMIT on a memory table?” I wrote the question down, to research it later.
The obvious answer is “COMMIT on a non-transactional table does nothing.”
Tonight I was thinking about this, and I realized I do not actually COMMIT “on a table.”
The manual page at: http://dev.mysql.com/doc/refman/4.1/en/commit.html (and the 5.0 and 5.1 equivalents) state:
By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk.
If you are using a transaction-safe storage engine (such as InnoDB, BDB, or NDB Cluster), you can disable autocommit mode with the following statement:
SET AUTOCOMMIT=0;
But what does If you are using a transaction-safe storage engine really mean? I ask because I do not specify a table type with that command. So I tried it on a fresh MySQL install (5.0.19 GA, on a RedHat Linux Enterprise machine):
Continue reading “COMMIT and Non-Transactional Tables”
My desktop at work is a Windows machine. Why? Because it gives me what I need — shell access to servers so I can do real work on the machines, a text editor, an e-mail client and a web browser. That’s really all I need to do my job. Sure, I could put in for a Macintosh or install a Unix variant. But if it gives me what I want, why would I spend all that work changing things around, just to ultimately get the same requirements — shell, web browser, text editor, e-mail client….????
I love MySQL, it’s a great database. But in order to meet its tenets, it has sacrificed features. When database religious wars start, it comes down to “MySQL stinks because it does not have the features,” or, nowadays, “It didn’t have [x feature] for a long time.” When that happens, my question is always, “If MySQL is so bad, why do so many people use it?”
Because it gives them the most important feature — SPEED. Speed is the #1 top priority in embedded databases, web applications and most desktop applications. (Am I missing a use of a database?) Companies will pay through the nose for training and licenses if it means their customers are happy because their product is speedy. (Meanwhile, MySQL is offering it very affordably, so folks do not have to pay through the nose.)
So to the folks who argue that MySQL stinks — I’ll agree, if you are talking about being feature-rich. However, MySQL has been growing in that department, so the argument is only relevant if you want to do a pivot table, or index a calculated field in a VIEW, or something complex like that. Perhaps MySQL isn’t appropriate for, say, a data warehouse. SQL Server is a better choice for that, as it has reporting modules and analysis wizards and all sorts of stuff.
MySQL is not perfect for everything, and it is not lousy for everything. But if you look at what most people need, it is speed. MySQL delivers that.
http://www.mysql.com/news-and-events/press-release/release_2006_35.html
MySQL won a contest, and it is proclaimed the fastest database.
I am happy, but I am not surprised. The basic tenets of MySQL are “fast, easy-to-use and bug-free”.
It is nice to know MySQL is actually meeting their goals. ‘Bug-free’ is not totally true, of course, but MySQL’s features are well-implemented. And being the fastest database is an achievement, even if it was one that was planned for.
We all know MySQL is the fastest. That’s why so many organizations have used it, even during the time when MyISAM was the only widely used storage engine. Before transactions, before fulltext indexing, before views and stored procedures and triggers, MySQL was widely used. A developer should not have to write transaction code in a programming language, but many organizations were happy to use bad coding techniques and zoom past their competitors with a speedy site.
Now the rest of the world knows that MySQL is the fastest. And, of course, MySQL is fast, easy-to-use, affordable AND full-featured. MySQL is doing a good job of marketing their new features, but another thing they should do is find out all the outdated information on websites and educate folks, that the arguments against MySQL are fewer and fewer as time goes on.
My desktop at work is a Windows machine. Why? Because it gives me what I need — shell access to servers so I can do real work on the machines, a text editor, an e-mail client and a web browser. That’s really all I need to do my job. Sure, I could put in for a Macintosh or install a Unix variant. But if it gives me what I want, why would I spend all that work changing things around, just to ultimately get the same requirements — shell, web browser, text editor, e-mail client….????
I love MySQL, it’s a great database. But in order to meet its tenets, it has sacrificed features. When database religious wars start, it comes down to “MySQL stinks because it does not have the features,” or, nowadays, “It didn’t have [x feature] for a long time.” When that happens, my question is always, “If MySQL is so bad, why do so many people use it?”
Because it gives them the most important feature — SPEED. Speed is the #1 top priority in embedded databases, web applications and most desktop applications. (Am I missing a use of a database?) Companies will pay through the nose for training and licenses if it means their customers are happy because their product is speedy. (Meanwhile, MySQL is offering it very affordably, so folks do not have to pay through the nose.)
So to the folks who argue that MySQL stinks — I’ll agree, if you are talking about being feature-rich. However, MySQL has been growing in that department, so the argument is only relevant if you want to do a pivot table, or index a calculated field in a VIEW, or something complex like that. Perhaps MySQL isn’t appropriate for, say, a data warehouse. SQL Server is a better choice for that, as it has reporting modules and analysis wizards and all sorts of stuff.
MySQL is not perfect for everything, and it is not lousy for everything. But if you look at what most people need, it is speed. MySQL delivers that.
http://www.mysql.com/news-and-events/press-release/release_2006_35.html
MySQL won a contest, and it is proclaimed the fastest database.
I am happy, but I am not surprised. The basic tenets of MySQL are “fast, easy-to-use and bug-free”.
It is nice to know MySQL is actually meeting their goals. ‘Bug-free’ is not totally true, of course, but MySQL’s features are well-implemented. And being the fastest database is an achievement, even if it was one that was planned for.
We all know MySQL is the fastest. That’s why so many organizations have used it, even during the time when MyISAM was the only widely used storage engine. Before transactions, before fulltext indexing, before views and stored procedures and triggers, MySQL was widely used. A developer should not have to write transaction code in a programming language, but many organizations were happy to use bad coding techniques and zoom past their competitors with a speedy site.
Now the rest of the world knows that MySQL is the fastest. And, of course, MySQL is fast, easy-to-use, affordable AND full-featured. MySQL is doing a good job of marketing their new features, but another thing they should do is find out all the outdated information on websites and educate folks, that the arguments against MySQL are fewer and fewer as time goes on.
With recent posts by Frank Mash and Stewart Smith about password protecting, I am reminded of all the privacy vs. security arguments we have going on in the United States. Basically, I see a somewhat similar situation — how much privacy do folks give up for the sake of security is analogous to how much calculation, how many hoops to jump through, to ensure that data is secured properly.
On the one hand, the analogy falls apart, because encryption calculation times are much less of an “inconvenience” than an invasion of privacy, and thus the argument gets usurped. It’s just a function, or a few calculations, no big deal. We all use SSH instead of telnet, and hopefully SFTP instead of FTP, because plaintext passwords are bad.
As a retort, most folks do not use SSL-enabled MySQL, and some do not even use SSL-enabled http. Why? Because it’s slow! Well, we do not want anything slow! But the security is worth the slowness! What? You mean people will go to another web site if yours is too slow? But the competitor is not as secure!!!!! So the analogy works there.
The analogy also works, when you consider how valuable the data is that you are attempting to lock up. Financial and health institutions need as high a level of encryption as possible for passwords, and any organization that stores a federal ID number should encrypt that. Nobody wants their privacy violated by having their health information exposed, their identity stolen, their bank accounts drained and their credit cards maxxed out.
Many people worry about things that are basically public information. For instance, in the US, the bank account number and the routing number are both printed on every check. Electronic Fund Transfers (EFTs) require three numbers — the checking account number, the bank routing number, and the amount. Or at least, that is all I type in. Perhaps my account stores address information and it is checked against that, but I am not asked for my billing address, as I am with a credit card transaction.
Some people guard their bank account number with extreme privacy, but it is in plain sight on the bottom of every paper check written!
Similarly, does it matter if someone cracks my password in some applications? How much damage could someone do if they got my password to a newspaper site. What are they going to do, write a letter to the editor or a comment? You’re not liable if someone cracks your server and then uses it to port-scan government agencies. It’s just a pain when you have to wipe your OS and start over. But no valuable information is lost, just time and patience.
At work, sure, they could get their hands on trade secrets if they cracked my desktop, laptop, VPN, or email password.
What about a dating site? What about a community forum? Should my password on forums.mysql.com be stored as tightly as my password on mysqlcamp.org? What does it matter if either password is cracked? Sure, if they try that same password on paypal, and I am dumb enough to use the same password for important data that I do for non-important data, my password will be stolen.
This is, to me, one of the greatest things about wikis. Sure, people have vandalized wikis, but it’s much more satisfying for folks to vandalize a site that’s not “open”. Someone is going to steal a new $3,000 bicycle that’s not locked up, but nobody is going to touch the old beater with a rusty frame, missing front wheel, flat back tire.
And of course, your application probably falls somewhere in between “everyone wants it” and “nobody wants it”. One of the things I say over and over in the presentations I give is “think about it.” Think about the security you need. Do a risk analysis. If you want your data secure, write it on a piece of paper and have recipients swallow it after they’ve read it. For any other security method, think about the gaps, and think about what really matters.
As a user, think about the ramifications of your passwords, as well. Many sites without “important” information will e-mail your password if you forget it. There it is, in plaintext for the world to intercept. I keep a few passwords at any given time (and change them every so often) — “really secure” ones, for financial institutions and such, “somewhat secure” ones, for things like blogging sites, and then “throwaway” ones, for sites where the info is not important, and I would suffer very little if my password is “cracked”.
—————————-
As well, by highlighting the encryption functions (MD5() and SHA1()) in MySQL, both articles imply that applications should call the encryption functions within MySQL. If an application is using encryption at all, it should be done as close to the user as possible. A client-side encryption such as a Javascript function is much better, security-wise, than using MySQL. You want to encrypt it *before* it goes over the network. If someone’s sniffing the network, then running SELECT nickname FROM myUser WHERE username='sheeri' and password=SHA1('kritzer'); is pointless — even if you salt the data. Someone can sniff the packet and find the plaintext ‘kritzer’ — either between the client’s browser and the web server, or between the web server and the database server.
Stewart’s post did not mention that a JOIN is actually the best way to go — JOIN the words table with the passwords on the salted hash, and then you can possibly retrieve a password. As well, if I were a cracker, I wouldn’t care about using a VIEW, I’d just add a “saltedmd5” column to my table, index it, and then JOIN the tables (creating an index on the table field if need be). Because he was comparing md5 sums, not actually trying to compare passwords through an application, it means he had a backdoor to make database calls, so we could indeed assume a JOIN is possible.
With recent posts by Frank Mash and Stewart Smith about password protecting, I am reminded of all the privacy vs. security arguments we have going on in the United States. Basically, I see a somewhat similar situation — how much privacy do folks give up for the sake of security is analogous to how much calculation, how many hoops to jump through, to ensure that data is secured properly.
On the one hand, the analogy falls apart, because encryption calculation times are much less of an “inconvenience” than an invasion of privacy, and thus the argument gets usurped. It’s just a function, or a few calculations, no big deal. We all use SSH instead of telnet, and hopefully SFTP instead of FTP, because plaintext passwords are bad.
As a retort, most folks do not use SSL-enabled MySQL, and some do not even use SSL-enabled http. Why? Because it’s slow! Well, we do not want anything slow! But the security is worth the slowness! What? You mean people will go to another web site if yours is too slow? But the competitor is not as secure!!!!! So the analogy works there.
The analogy also works, when you consider how valuable the data is that you are attempting to lock up. Financial and health institutions need as high a level of encryption as possible for passwords, and any organization that stores a federal ID number should encrypt that. Nobody wants their privacy violated by having their health information exposed, their identity stolen, their bank accounts drained and their credit cards maxxed out.
Many people worry about things that are basically public information. For instance, in the US, the bank account number and the routing number are both printed on every check. Electronic Fund Transfers (EFTs) require three numbers — the checking account number, the bank routing number, and the amount. Or at least, that is all I type in. Perhaps my account stores address information and it is checked against that, but I am not asked for my billing address, as I am with a credit card transaction.
Some people guard their bank account number with extreme privacy, but it is in plain sight on the bottom of every paper check written!
Similarly, does it matter if someone cracks my password in some applications? How much damage could someone do if they got my password to a newspaper site. What are they going to do, write a letter to the editor or a comment? You’re not liable if someone cracks your server and then uses it to port-scan government agencies. It’s just a pain when you have to wipe your OS and start over. But no valuable information is lost, just time and patience.
At work, sure, they could get their hands on trade secrets if they cracked my desktop, laptop, VPN, or email password.
What about a dating site? What about a community forum? Should my password on forums.mysql.com be stored as tightly as my password on mysqlcamp.org? What does it matter if either password is cracked? Sure, if they try that same password on paypal, and I am dumb enough to use the same password for important data that I do for non-important data, my password will be stolen.
This is, to me, one of the greatest things about wikis. Sure, people have vandalized wikis, but it’s much more satisfying for folks to vandalize a site that’s not “open”. Someone is going to steal a new $3,000 bicycle that’s not locked up, but nobody is going to touch the old beater with a rusty frame, missing front wheel, flat back tire.
And of course, your application probably falls somewhere in between “everyone wants it” and “nobody wants it”. One of the things I say over and over in the presentations I give is “think about it.” Think about the security you need. Do a risk analysis. If you want your data secure, write it on a piece of paper and have recipients swallow it after they’ve read it. For any other security method, think about the gaps, and think about what really matters.
As a user, think about the ramifications of your passwords, as well. Many sites without “important” information will e-mail your password if you forget it. There it is, in plaintext for the world to intercept. I keep a few passwords at any given time (and change them every so often) — “really secure” ones, for financial institutions and such, “somewhat secure” ones, for things like blogging sites, and then “throwaway” ones, for sites where the info is not important, and I would suffer very little if my password is “cracked”.
—————————-
As well, by highlighting the encryption functions (MD5() and SHA1()) in MySQL, both articles imply that applications should call the encryption functions within MySQL. If an application is using encryption at all, it should be done as close to the user as possible. A client-side encryption such as a Javascript function is much better, security-wise, than using MySQL. You want to encrypt it *before* it goes over the network. If someone’s sniffing the network, then running SELECT nickname FROM myUser WHERE username='sheeri' and password=SHA1('kritzer'); is pointless — even if you salt the data. Someone can sniff the packet and find the plaintext ‘kritzer’ — either between the client’s browser and the web server, or between the web server and the database server.
Stewart’s post did not mention that a JOIN is actually the best way to go — JOIN the words table with the passwords on the salted hash, and then you can possibly retrieve a password. As well, if I were a cracker, I wouldn’t care about using a VIEW, I’d just add a “saltedmd5” column to my table, index it, and then JOIN the tables (creating an index on the table field if need be). Because he was comparing md5 sums, not actually trying to compare passwords through an application, it means he had a backdoor to make database calls, so we could indeed assume a JOIN is possible.
On Wednesday night, I did some consulting, and it ended up taking twice as long as I thought it would. So I am rewarding myself by going to MySQLCamp!
Speaking of which, I updated the home page, adding explicitly that registration is free, and a section on travel information. I have no idea how housing is being organized, or if it is, and I am happy to take the lead on doing so.
The basics are that there are some good, cheap* 3-star hotels not too far away. Cheap = under $100, I even found some in the $60 price range! I would love to get a sense of what folks are doing for lodging, and if folks want, I can work on getting a group discount (some hotels will arrange one for a minimum of 10 rooms), arranging a suite for the “evening track”, etc.
Currently stating that you’re interested does not require a commitment to get a hotel room. If we have critical mass, I can see what the options are, and folks can reserve a room for themselves or, if it’s easier/cheaper, I can make the reservations for folks.
Alternatively, if someone speaks up and says, “Silly Sheeri! It’s all taken care of!” please point me in the right direction.
Public transit information would be great on the travel page, particularly from the airport to the venue. Also, knowing what time camp starts on Friday and ends on Monday would be great…..sure, they’re approximate….
* the hotels are good and cheap, therefore they cannot be fast.
On Wednesday night, I did some consulting, and it ended up taking twice as long as I thought it would. So I am rewarding myself by going to MySQLCamp!
Speaking of which, I updated the home page, adding explicitly that registration is free, and a section on travel information. I have no idea how housing is being organized, or if it is, and I am happy to take the lead on doing so.
The basics are that there are some good, cheap* 3-star hotels not too far away. Cheap = under $100, I even found some in the $60 price range! I would love to get a sense of what folks are doing for lodging, and if folks want, I can work on getting a group discount (some hotels will arrange one for a minimum of 10 rooms), arranging a suite for the “evening track”, etc.
Currently stating that you’re interested does not require a commitment to get a hotel room. If we have critical mass, I can see what the options are, and folks can reserve a room for themselves or, if it’s easier/cheaper, I can make the reservations for folks.
Alternatively, if someone speaks up and says, “Silly Sheeri! It’s all taken care of!” please point me in the right direction.
Public transit information would be great on the travel page, particularly from the airport to the venue. Also, knowing what time camp starts on Friday and ends on Monday would be great…..sure, they’re approximate….
* the hotels are good and cheap, therefore they cannot be fast.