HandlerSocket is cool. But, it turns out there are a few issues.

Justin Swanhart points out HandlerSocket currently lacks atomic operations . Since HandlerSocket uses different connections for reading and writing, you can’t increment/decrement a value without creating a race condition.

Still, the idea of skipping SQL interpretation and just reading the data you know you want is a great one.  Writing data might even be better. But being able to use both SQL and NoSQL could be really wonderful.  What if we could use complex queries to update complex tables and pluck values out as needed.  For example, queries to analyze current weather conditions and produce forecasts that we could then retrieve via a location key? What about updating current condition data via NoSQL before running the analysis?  Will this work?

NoSQL writes (insert/update/delete) and SQL reads

Using the PHP test program from my last post, I’ve tried inserting and updating data via NoSQL and reading it back via SQL.  What I found was not good.  NoSQL is consistent.  If I write data via NoSQL I always get back my data via NoSQL.  However, if I try to write data via NoSQL and read it with a  simple ‘Select * from user;’  I get inconsistent results.

There is what the record looked like.

MariaDB [test]> select * from user where user_id = 1;
+---------+-----------+-----------------------+---------------------+
| user_id | user_name | user_email            | created             |
+---------+-----------+-----------------------+---------------------+
|       1 | mark      |                       | 0000-00-00 00:00:00 |
+---------+-----------+-----------------------+---------------------+

Here is the code segment I used to update  the table. Review my previous posts for more details.

$hs2 = new HandlerSocket($host, $wport);
if (!($hs2->openIndex(1, $dbname, $table, '', 'user_id,user_name,user_email,created')))
{
 echo $hs2->getError(), PHP_EOL;
 die();
}

if (!($hs2->executeUpdate(1, '=', array('1'), array('1',"Mark",'mark@www.mysqlfanboy.com'), 1, 0)))
{
 echo $hs2->getError(), PHP_EOL;
 die();
}

A quick query via NoSQL shows the data has been updated. The ‘mark’ is now ‘Mark’ and the email address is there.

# php mytest.php
data > Mark
array(1) {
 [0]=>
 array(4) {
 [0]=>
 string(1) "1"
 [1]=>
 string(4) "Mark"
 [2]=>
 string(20) "mark@www.mysqlfanboy.com"
 [3]=>
 string(19) "0000-00-00 00:00:00"
 }
}

I found MySQL SQL queries did not return my changes.  I tried many times to write new and updated records without SQL show the results. I thought this was because of query caching so I tried flushing the query cache without effect.  I was worried the date was buffered somewhere but not written to disk.  I restarted MySQL and the data was corrected.

# mysql test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 26457
Server version: 5.1.51-MariaDB-mariadb87-log (MariaDB - http://mariadb.com/)

This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> select * from user;
+---------+-----------+-----------------------+---------------------+
| user_id | user_name | user_email            | created             |
+---------+-----------+-----------------------+---------------------+
|       1 | mark      |                       | 0000-00-00 00:00:00 |
|       2 | linda     | linda@www.mysqlfanboy.com | 0000-00-00 00:00:00 |
|       3 | mark      | test@www.mysqlfanboy.com  | NULL                |
|       4 | test      | test@www.mysqlfanboy.com  | 0000-00-00 00:00:00 |
|       5 | foo       | foo@www.mysqlfanboy.com   | 0000-00-00 00:00:00 |
+---------+-----------+-----------------------+---------------------+
5 rows in set (0.00 sec)

MariaDB [test]> FLUSH QUERY CACHE;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> select * from user;
+---------+-----------+-----------------------+---------------------+
| user_id | user_name | user_email            | created             |
+---------+-----------+-----------------------+---------------------+
|       1 | mark      |                       | 0000-00-00 00:00:00 |
|       2 | linda     | linda@www.mysqlfanboy.com | 0000-00-00 00:00:00 |
|       3 | mark      | test@www.mysqlfanboy.com  | NULL                |
|       4 | test      | test@www.mysqlfanboy.com  | 0000-00-00 00:00:00 |
|       5 | foo       | foo@www.mysqlfanboy.com   | 0000-00-00 00:00:00 |
+---------+-----------+-----------------------+---------------------+
5 rows in set (0.00 sec)

MariaDB [test]> quit
Bye
# service mysql stop
Shutting down MySQL.                                       [  OK  ]
# service mysql start
Starting MySQL.                                            [  OK  ]
# mysql test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 5.1.51-MariaDB-mariadb87-log (MariaDB - http://mariadb.com/)

This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> select * from user;
+---------+-----------+-----------------------+---------------------+
| user_id | user_name | user_email            | created             |
+---------+-----------+-----------------------+---------------------+
|       1 | Mark      | mark@www.mysqlfanboy.com  | 0000-00-00 00:00:00 |
|       2 | linda     | linda@www.mysqlfanboy.com | 0000-00-00 00:00:00 |
|       3 | mark      | test@www.mysqlfanboy.com  | NULL                |
|       4 | test      | test@www.mysqlfanboy.com  | 0000-00-00 00:00:00 |
|       5 | foo       | foo@www.mysqlfanboy.com   | 0000-00-00 00:00:00 |
+---------+-----------+-----------------------+---------------------+
5 rows in set (0.00 sec)

MySQL writes and NoSQL reads

Writing data via MySQL and reading via NoSQL works.  I found no issues with this process.  This leads me to believe the issue is related to write buffering and not query caching.

Security

What security.  HandleSocket gives you direct access to EVERY table.  With HandleSocket you can update anything including MySQL security tables.  You would be crazy to use HandleSocket in a public facing system.  Even if you using it in back office process you should use some sort of filewall technology like iptables to limit commectivity.

I’ll say this again: HANDLESOCKET HAS NO SECURITY!

Tweet