Pitfalls when working with php-handlersocket

HandlerSocket is a noSQL plugin for mySQL that allows you to access databases bypassing the SQL level.

This article is intended for those who successfully installed HandlerSocket and php-handlersocket, and also encountered a poor manual and strange behavior of this plugin for php.

If you hear about this decision for the first time, then I recommend to get acquainted with the following materials:
NoSQL in MySQL: MySQL overclocks to 750,000 queries per second
First experience with Handler Socket & php_handlersocket
Some subtleties of Update & Insert in Handler Socket

On close examination and knowledge of the deepest depths of mySQL, many of the questions are rather naive, but these are the questions that have puzzled me during development. At the same time, it is not always clear that from the “features” I discovered are a consequence of the internal structure of mySQL, which is the result of the work of tentacles by the creator of the HandlerSocket Yoshinori Matsunobu, and that, in turn, by the code php-handlersocket.
')
To illustrate the examples, the following source table is used:

CREATE TABLE IF NOT EXISTS `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`country_id` int(11) NOT NULL,
`city_id` int(11) NOT NULL,
`language` varchar(2) NOT NULL,
`name` varchar(255) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_country_language` (`country_id`,`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;

INSERT INTO `test` (`id`, `country_id`, `city_id`, `language`, `name`, `created_at`) VALUES
(1, 1, 1, 'es', 'Terminator', '2011-07-02 00:00:00'),
(2, 1, 1, 'en', 'Flash Gordon'', '2011-07-02 01:00:00'),
(3, 3, 4, 'en', 'Batman', '2011-07-02 02:00:00'),
(4, 1, 2, 'jp', 'Godzilla', '2011-07-02 03:00:00'),
(5, 2, 8, 'es', 'Superman', '2011-07-02 04:00:00');

And, of course, the HandlerSockets themselves

$hsr = new HandlerSocket('localhost', 9998); //
$hsw = new HandlerSocket('localhost', 9999); //

And now let's imagine for a moment that we are confronted only with a junior and his more experienced comrade who has sat down for using php-handlersocket.

I executed executeInsert (), and nothing appeared in the database!

If you use innoDB, then do FLUSH TABLE. innoDB is transactional tables, so you need to tell mySQL that "everything has arrived." Of course, there are no such problems with myISAM.

And why does my AUTO_INCREMENT field always get the value 0?

Considering auto_increment is not the responsibility of php-handlersocket. But it does not forbid to consider it yourself, the main thing is not to forget to install it in the table using ALTER TABLE SET auto_increment after finishing work with php-handlersocket.

I can’t add a record by setting values ​​of only some fields!

When using executeInsert (), you cannot skip fields in openIndex (), but you can stop there, i.e. Do not specify all fields in the table until the end.

Does not work:

$hsw->openIndex(0, 'test_db', 'test', '', 'id,name');
$hsw->executeInsert(0, array(999, 'Green Lantern'));

Works:

$hsw->openIndex(0, 'test_db', 'test', '', 'id,country_id,city_id,language,name');
$hsw->executeInsert(0, array(999, 2, 3, 'en', 'Green Lantern'));

I need to do a “SELECT * FROM Customers”!

By default, if the $ limit parameter is not specified, php-handlersocket returns only one entry that matches the specified criteria. In order to get all the records from the table, regardless of the criteria, you can try the following:

$hsr->openIndex(0, 'test_db', 'test', '', 'id,country_id,city_id,language,name,created_at');
$res = $hsr->executeSingle(0, '>', array(0), 9999999999);

However, it is worth remembering that an array will return to $ res, which can be very large, which can lead to irreparable consequences.

Reverse sorting by index is vital for me!

Then you should be a Hindu for a while! Attention, never try to reproduce it at home:

$hsr->openIndex(0, 'test_db'', 'test', '', 'id,country_id,city_id,language,name,created_at');
$res = $hsr->executeSingle(0, '<', array(9999999999));

If you decided to achieve nirvana at all, and you want to sort the results by an index, which is a text field (in our case, idx_name), you can try the following:

$hsr->openIndex(0, 'test_db'', 'test', 'idx_name', 'id,country_id,city_id,language,name,created_at');
$res = $hsr->executeSingle(0, '<', array('ZZZZZZZZZ'));

I recommend to treat these "ficham" very skeptical.

Some kind of nonsense with $ limit and $ skip. $ limit = 3, $ skip = 0 returns the first 3 records, and $ limit = 3, $ skip = 3 does not return anything at all!

The fact is that php-handlersocket believes that $ limit is the number of all entries that should be selected, starting with zero, and $ skip - with which in order of recording it is necessary to return the result. Those. using the values ​​of the parameters $ limit = 3, $ skip = 3 means “hey, take me only 3 entries and return to me those that are after the third”, which results in the return of an empty array. To emulate "LIMIT 3, 3" you need to use the following:

$hsw->openIndex(0, 'db_test', 'test', '', 'id,country_id,city_id,language,name,created_at');
$r = $hsw->executeSingle(0, '>', array(0), 6, 3);

My sample does not work by index, where there are two fields and one of them is text (idx_country_language)!

Unfortunately, as practice has shown, php-handlersocket is not able to search by indexes containing text fields or dates. Alternatively, it may be advisable to use the ENUM field type where possible. At the same time, php-handlersocket copes remarkably with a sample of indices for one field (text or date).

findings

HandlerSocket + php-handlersocket are currently very interesting, but still a crutch for mySQL. Rewriting the entire site for their use will not work in any case, but for a banal key-value-storage with pleasant additions, this solution is recommended.