Drizzle

Bug #588487
Comment #3

Comment 3 for bug 588487

Revision history for this message

Patrick Crews (patrick-crews) wrote on 2010-06-10: Re: [Bug 588487] Re: Using 'ALTER TABLE <> ENABLE KEYS' on a BlitzDB table locks up the server

Very cool that you found it so fast : )
I think your solution is a good one. Mainly, I ran into this by accident as
the randgen does this automatically during test table creation/population (I
believe it is a trick to speed up large INSERT operations). We were seeing
an error thrown on the DISABLE KEYS portion of things and the randgen could
proceed.

As long as things don't lock up, we can do more with comparison testing
(once I've figured out a few more randgen/drizzle issues that is...grr)

I'll be busy with some transaction_log / drizzledump testing for a while,
but I'm hoping that late July will see me setting up more tests and I will
be writing more on the randgen on the wiki.

Here are my initial (very initial) pages on the randgen. If you should have
any questions, just let me know and I'll write up an answer.
http://drizzle.org/wiki/Category:Randgen

Thanks,
Patrick

On Wed, Jun 9, 2010 at 4:07 AM, Toru Maesaka <email address hidden> wrote:

> Here are my findings on this bug so far.
>
> It turns out that to support ENABLE INDEX syntax, the storage engine
> needs to implement Cursor::enable_indexes() and preferably
> Cursor::disable_indexes(). BlitzDB hasn't implemented these functions
> so I suspect this is what's causing the weird behavior.
>
> I have no prior knowledge on this syntax so until I do more research,
> I will make BlitzDB return HA_ERR_UNSUPPORTED. This way, at least the
> user won't have to panic.
>
> I'll push the patch soon :)
>
> Cheers,
> Toru
>
> On Wed, Jun 9, 2010 at 1:30 AM, Toru Maesaka <email address hidden> wrote:
> > Thank you Patrick.
> >
> > Admittedly, ENABLE KEYS is something new to me so I think I'll have
> > fun tackling this issue. I just solved a build problem in FreeBSD so
> > I'm going to start investigating this bug.
> >
> > Cheers,
> > Toru
> >
> > On Wed, Jun 2, 2010 at 6:20 AM, Patrick Crews <email address hidden> wrote:
> >> ** Changed in: drizzle
> >> Assignee: (unassigned) => Toru Maesaka (tmaesaka)
> >>
> >> --
> >> Using 'ALTER TABLE <> ENABLE KEYS' on a BlitzDB table locks up the
> server
> >> https://bugs.launchpad.net/bugs/588487
> >> You received this bug notification because you are a bug assignee.
> >>
> >> Status in A Lightweight SQL Database for Cloud and Web: New
> >>
> >> Bug description:
> >> Issuing an ALTER TABLE <name> ENABLE KEYS against a BlitzDB table causes
> Drizzle to lock up.
> >>
> >> Background:
> >> This was discovered while attempting to test BlitzDB with the Random
> Query Generator and part of the gendata (table creation + population script)
> process involves disabling/enabling keys during the initial table load.
> >>
> >> Test case:
> >> You will see appropriate warnings for key size being too long on the
> 1024 column as well as warnings for the truncation of data for the
> varchar_10 columns.
> >>
> >> You will also see an appropriate warning when we attempt to DISABLE KEYS
> (not supported).
> >>
> >> However, when we hit the ENABLE KEYS query, things lock up every time.
> >> drizzle> ALTER TABLE `C` ENABLE KEYS;
> >> Query OK, 0 rows affected, 1 warning (0 sec)
> >> ^ It appears that the query is processed correctly, however any queries
> after this point will not be productive, nor will additional attempts to
> connect to the server.
> >>
> >>
> >> CREATE TABLE `C` (
> >> `col_varchar_10_key` varchar(10),
> >> `col_varchar_10` varchar(10),
> >> `col_int` int,
> >> `col_varchar_1024_key` varchar(1024),
> >> `col_varchar_1024` varchar(1024),
> >> `col_int_key` int,
> >> pk integer auto_increment,
> >> /*Indices*/
> >> key (`col_varchar_10_key` ),
> >> key (`col_varchar_1024_key` ),
> >> key (`col_int_key` ),
> >> primary key (pk)) ENGINE=blitzdb ;
> >> ALTER TABLE `C` DISABLE KEYS ;
> >> INSERT /*! IGNORE */ INTO C VALUES
> ('xermmjkovqnoqhpubzfjxwqacdiwyekwemegyaluzytnccflbvqnqnmfvvlhitckzgppyilihmleccmmxqxcilxqvfikqseftdkgtlqzbwpucapktdeqxndcqytizhxaaiyashfuwbyynvenjcsmasklnxxtbzrqxmoupppjcctptgoksleizxkrbednzzwgvlnnghjvvtlpajyalhsygtjrhjxreywihgnxlevtsvfzsztftgvgzkqhwpnbztbcpoqdmhgjffokjvoae',
> 'from', -2114322432, 'mean', 't', -1076690944, NULL) , ('r',
> 'qxermmjkovqnoqhpubzfjxwqacdiwyekwemegyaluzytnccflbvqnqnmfvvlhitckzgppyilihmleccmmxqxcilxqvfikqseftdkgtlqzbwpucapktdeqxndcqytizhxaaiyashfuwbyynvenjcsmasklnxxtbzrqxmoupppjcctptgoksleizxkrbednzzwgvlnnghjvvtlpajyalhsygtjrhjxreywihgn',
> 0, 'his',
> 'eqxermmjkovqnoqhpubzfjxwqacdiwyekwemegyaluzytnccflbvqnqnmfvvlhitckzgppyilihmleccmmxqxcilxqvfikqseftdkgtlqzbwpucapktdeqxndcqytizhxaaiyashfuwbyynvenjcsmasklnxxtbzrqxmoupppjcctptgokslei',
> NULL, NULL) ;
> >> COMMIT;
> >> ALTER TABLE `C` ENABLE KEYS;
> >>
> >>
> >>
> >
> >
> >
> > --
> > Toru Maesaka <email address hidden>
> >
>
>
> --
> Toru Maesaka <email address hidden>
>
> --
> Using 'ALTER TABLE <> ENABLE KEYS' on a BlitzDB table locks up the server
> https://bugs.launchpad.net/bugs/588487
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in A Lightweight SQL Database for Cloud and Web: New
>
> Bug description:
> Issuing an ALTER TABLE <name> ENABLE KEYS against a BlitzDB table causes
> Drizzle to lock up.
>
> Background:
> This was discovered while attempting to test BlitzDB with the Random Query
> Generator and part of the gendata (table creation + population script)
> process involves disabling/enabling keys during the initial table load.
>
> Test case:
> You will see appropriate warnings for key size being too long on the 1024
> column as well as warnings for the truncation of data for the varchar_10
> columns.
>
> You will also see an appropriate warning when we attempt to DISABLE KEYS
> (not supported).
>
> However, when we hit the ENABLE KEYS query, things lock up every time.
> drizzle> ALTER TABLE `C` ENABLE KEYS;
> Query OK, 0 rows affected, 1 warning (0 sec)
> ^ It appears that the query is processed correctly, however any queries
> after this point will not be productive, nor will additional attempts to
> connect to the server.
>
>
> CREATE TABLE `C` (
> `col_varchar_10_key` varchar(10),
> `col_varchar_10` varchar(10),
> `col_int` int,
> `col_varchar_1024_key` varchar(1024),
> `col_varchar_1024` varchar(1024),
> `col_int_key` int,
> pk integer auto_increment,
> /*Indices*/
> key (`col_varchar_10_key` ),
> key (`col_varchar_1024_key` ),
> key (`col_int_key` ),
> primary key (pk)) ENGINE=blitzdb ;
> ALTER TABLE `C` DISABLE KEYS ;
> INSERT /*! IGNORE */ INTO C VALUES
> ('xermmjkovqnoqhpubzfjxwqacdiwyekwemegyaluzytnccflbvqnqnmfvvlhitckzgppyilihmleccmmxqxcilxqvfikqseftdkgtlqzbwpucapktdeqxndcqytizhxaaiyashfuwbyynvenjcsmasklnxxtbzrqxmoupppjcctptgoksleizxkrbednzzwgvlnnghjvvtlpajyalhsygtjrhjxreywihgnxlevtsvfzsztftgvgzkqhwpnbztbcpoqdmhgjffokjvoae',
> 'from', -2114322432, 'mean', 't', -1076690944, NULL) , ('r',
> 'qxermmjkovqnoqhpubzfjxwqacdiwyekwemegyaluzytnccflbvqnqnmfvvlhitckzgppyilihmleccmmxqxcilxqvfikqseftdkgtlqzbwpucapktdeqxndcqytizhxaaiyashfuwbyynvenjcsmasklnxxtbzrqxmoupppjcctptgoksleizxkrbednzzwgvlnnghjvvtlpajyalhsygtjrhjxreywihgn',
> 0, 'his',
> 'eqxermmjkovqnoqhpubzfjxwqacdiwyekwemegyaluzytnccflbvqnqnmfvvlhitckzgppyilihmleccmmxqxcilxqvfikqseftdkgtlqzbwpucapktdeqxndcqytizhxaaiyashfuwbyynvenjcsmasklnxxtbzrqxmoupppjcctptgokslei',
> NULL, NULL) ;
> COMMIT;
> ALTER TABLE `C` ENABLE KEYS;
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/drizzle/+bug/588487/+subscribe
>

Very cool that you found it so fast : )
I think your solution is a good one.  Mainly, I ran into this by accident as
the randgen does this automatically during test table creation/population (I
believe it is a trick to speed up large INSERT operations).  We were seeing
an error thrown on the DISABLE KEYS portion of things and the randgen could
proceed.

As long as things don't lock up, we can do more with comparison testing
(once I've figured out a few more randgen/drizzle issues that is...grr)

I'll be busy with some transaction_log / drizzledump testing for a while,
but I'm hoping that late July will see me setting up more tests and I will
be writing more on the randgen on the wiki.

Here are my initial (very initial) pages on the randgen.  If you should have
any questions, just let me know and I'll write up an answer.
http://drizzle.org/wiki/Category:Randgen

Thanks,
Patrick

On Wed, Jun 9, 2010 at 4:07 AM, Toru Maesaka <dev@torum.net> wrote:

> Here are my findings on this bug so far.
>
> It turns out that to support ENABLE INDEX syntax, the storage engine
> needs to implement Cursor::enable_indexes() and preferably
> Cursor::disable_indexes(). BlitzDB hasn't implemented these functions
> so I suspect this is what's causing the weird behavior.
>
> I have no prior knowledge on this syntax so until I do more research,
> I will make BlitzDB return HA_ERR_UNSUPPORTED. This way, at least the
> user won't have to panic.
>
> I'll push the patch soon :)
>
> Cheers,
> Toru
>
> On Wed, Jun 9, 2010 at 1:30 AM, Toru Maesaka <dev@torum.net> wrote:
> > Thank you Patrick.
> >
> > Admittedly, ENABLE KEYS is something new to me so I think I'll have
> > fun tackling this issue. I just solved a build problem in FreeBSD so
> > I'm going to start investigating this bug.
> >
> > Cheers,
> > Toru
> >
> > On Wed, Jun 2, 2010 at 6:20 AM, Patrick Crews <gleebix@gmail.com> wrote:
> >> ** Changed in: drizzle
> >>     Assignee: (unassigned) => Toru Maesaka (tmaesaka)
> >>
> >> --
> >> Using 'ALTER TABLE <> ENABLE KEYS' on a BlitzDB table locks up the
> server
> >> https://bugs.launchpad.net/bugs/588487
> >> You received this bug notification because you are a bug assignee.
> >>
> >> Status in A Lightweight SQL Database for Cloud and Web: New
> >>
> >> Bug description:
> >> Issuing an ALTER TABLE <name> ENABLE KEYS against a BlitzDB table causes
> Drizzle to lock up.
> >>
> >> Background:
> >> This was discovered while attempting to test BlitzDB with the Random
> Query Generator and part of the gendata (table creation + population script)
> process involves disabling/enabling keys during the initial table load.
> >>
> >> Test case:
> >> You will see appropriate warnings for key size being too long on the
> 1024 column as well as warnings for the truncation of data for the
> varchar_10 columns.
> >>
> >> You will also see an appropriate warning when we attempt to DISABLE KEYS
> (not supported).
> >>
> >> However, when we hit the ENABLE KEYS query, things lock up every time.
> >> drizzle> ALTER TABLE `C` ENABLE KEYS;
> >> Query OK, 0 rows affected, 1 warning (0 sec)
> >> ^ It appears that the query is processed correctly, however any queries
> after this point will not be productive, nor will additional attempts to
> connect to the server.
> >>
> >>
> >> CREATE TABLE `C` (
> >>  `col_varchar_10_key` varchar(10),
> >> `col_varchar_10` varchar(10),
> >> `col_int` int,
> >> `col_varchar_1024_key` varchar(1024),
> >> `col_varchar_1024` varchar(1024),
> >> `col_int_key` int,
> >>  pk integer auto_increment,
> >>  /*Indices*/
> >>  key (`col_varchar_10_key` ),
> >>  key (`col_varchar_1024_key` ),
> >>  key (`col_int_key` ),
> >>  primary key (pk)) ENGINE=blitzdb ;
> >>  ALTER TABLE `C` DISABLE KEYS ;
> >> INSERT /*! IGNORE */ INTO C VALUES
>  ('xermmjkovqnoqhpubzfjxwqacdiwyekwemegyaluzytnccflbvqnqnmfvvlhitckzgppyilihmleccmmxqxcilxqvfikqseftdkgtlqzbwpucapktdeqxndcqytizhxaaiyashfuwbyynvenjcsmasklnxxtbzrqxmoupppjcctptgoksleizxkrbednzzwgvlnnghjvvtlpajyalhsygtjrhjxreywihgnxlevtsvfzsztftgvgzkqhwpnbztbcpoqdmhgjffokjvoae',
> 'from', -2114322432, 'mean', 't', -1076690944, NULL) ,  ('r',
> 'qxermmjkovqnoqhpubzfjxwqacdiwyekwemegyaluzytnccflbvqnqnmfvvlhitckzgppyilihmleccmmxqxcilxqvfikqseftdkgtlqzbwpucapktdeqxndcqytizhxaaiyashfuwbyynvenjcsmasklnxxtbzrqxmoupppjcctptgoksleizxkrbednzzwgvlnnghjvvtlpajyalhsygtjrhjxreywihgn',
> 0, 'his',
> 'eqxermmjkovqnoqhpubzfjxwqacdiwyekwemegyaluzytnccflbvqnqnmfvvlhitckzgppyilihmleccmmxqxcilxqvfikqseftdkgtlqzbwpucapktdeqxndcqytizhxaaiyashfuwbyynvenjcsmasklnxxtbzrqxmoupppjcctptgokslei',
> NULL, NULL) ;
> >>  COMMIT;
> >> ALTER TABLE `C` ENABLE KEYS;
> >>
> >>
> >>
> >
> >
> >
> > --
> > Toru Maesaka <dev@torum.net>
> >
>
>
> --
> Toru Maesaka <dev@torum.net>
>
> --
> Using 'ALTER TABLE <> ENABLE KEYS' on a BlitzDB table locks up the server
> https://bugs.launchpad.net/bugs/588487
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in A Lightweight SQL Database for Cloud and Web: New
>
> Bug description:
> Issuing an ALTER TABLE <name> ENABLE KEYS against a BlitzDB table causes
> Drizzle to lock up.
>
> Background:
> This was discovered while attempting to test BlitzDB with the Random Query
> Generator and part of the gendata (table creation + population script)
> process involves disabling/enabling keys during the initial table load.
>
> Test case:
> You will see appropriate warnings for key size being too long on the 1024
> column as well as warnings for the truncation of data for the varchar_10
> columns.
>
> You will also see an appropriate warning when we attempt to DISABLE KEYS
> (not supported).
>
> However, when we hit the ENABLE KEYS query, things lock up every time.
> drizzle> ALTER TABLE `C` ENABLE KEYS;
> Query OK, 0 rows affected, 1 warning (0 sec)
> ^ It appears that the query is processed correctly, however any queries
> after this point will not be productive, nor will additional attempts to
> connect to the server.
>
>
> CREATE TABLE `C` (
>  `col_varchar_10_key` varchar(10),
> `col_varchar_10` varchar(10),
> `col_int` int,
> `col_varchar_1024_key` varchar(1024),
> `col_varchar_1024` varchar(1024),
> `col_int_key` int,
>  pk integer auto_increment,
>  /*Indices*/
>  key (`col_varchar_10_key` ),
>  key (`col_varchar_1024_key` ),
>  key (`col_int_key` ),
>  primary key (pk)) ENGINE=blitzdb ;
>  ALTER TABLE `C` DISABLE KEYS ;
> INSERT /*! IGNORE */ INTO C VALUES
>  ('xermmjkovqnoqhpubzfjxwqacdiwyekwemegyaluzytnccflbvqnqnmfvvlhitckzgppyilihmleccmmxqxcilxqvfikqseftdkgtlqzbwpucapktdeqxndcqytizhxaaiyashfuwbyynvenjcsmasklnxxtbzrqxmoupppjcctptgoksleizxkrbednzzwgvlnnghjvvtlpajyalhsygtjrhjxreywihgnxlevtsvfzsztftgvgzkqhwpnbztbcpoqdmhgjffokjvoae',
> 'from', -2114322432, 'mean', 't', -1076690944, NULL) ,  ('r',
> 'qxermmjkovqnoqhpubzfjxwqacdiwyekwemegyaluzytnccflbvqnqnmfvvlhitckzgppyilihmleccmmxqxcilxqvfikqseftdkgtlqzbwpucapktdeqxndcqytizhxaaiyashfuwbyynvenjcsmasklnxxtbzrqxmoupppjcctptgoksleizxkrbednzzwgvlnnghjvvtlpajyalhsygtjrhjxreywihgn',
> 0, 'his',
> 'eqxermmjkovqnoqhpubzfjxwqacdiwyekwemegyaluzytnccflbvqnqnmfvvlhitckzgppyilihmleccmmxqxcilxqvfikqseftdkgtlqzbwpucapktdeqxndcqytizhxaaiyashfuwbyynvenjcsmasklnxxtbzrqxmoupppjcctptgokslei',
> NULL, NULL) ;
>  COMMIT;
> ALTER TABLE `C` ENABLE KEYS;
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/drizzle/+bug/588487/+subscribe
>