Re: [Maria-developers] How to change max key length for MyISAM?

[jocelyn fournier]

Just FYI, I confirm Prestashop (at least since 1.6.1.x) is designed to work 
much better on InnoDB than MyISAM

BR,
  Jocelyn Fournier

> Le 15 janv. 2021 à 10:09, Reindl Harald  a écrit :
> 
> 
> 
> Am 15.01.21 um 09:01 schrieb Luuk:
>> On 14-1-2021 21:45, Reindl Harald wrote:
 I'm trying to install prestashop-1.7.7.1 on MyISAM
>>> 
>>> and why does that piece of software don't work with sensible defaults and 
>>> force you too pacth source code and recompile your database server in a way 
>>> nobody but you is running?
>>> 
>>> this is asking for troubles and until someone can prove a *real* benefit i 
>>> call it bullshit and won't install "prestashop-1.7.7.1 on MyISAM"
>>> 
>>> the other possibilty is that it's designed to work with innoDB and *not* 
>>> MyISAM which means: just don't enforce MyISAM, key sizes are your smallest 
>>> problems in reality
>> A: It is a MySQL issue. 
>> http://stackoverflow.com/questions/8746207/1071-specified-key-was-too-long-max-key-length-is-1000-bytes
>>  
> 
> as expected DO NOT INSTALL IT ON MYISAM jesus christ
> ENGINE=InnoDB
> 
> and no it's not a mysql issue when a software is written in a way than even 
> the installer dies
> 
> CREATE TABLE IF NOT EXISTS `pds_core_menu_items` (
>  `menu_id` varchar(32) NOT NULL,
>  `parent_menu_id` int(32) unsigned DEFAULT NULL,
>  `menu_name` varchar(255) DEFAULT NULL,
>  `menu_link` varchar(255) DEFAULT NULL,
>  `plugin` varchar(255) DEFAULT NULL,
>  `menu_type` int(1) DEFAULT NULL,
>  `extend` varchar(255) DEFAULT NULL,
>  `new_window` int(1) DEFAULT NULL,
>  `rank` int(100) DEFAULT NULL,
>  `hide` int(1) DEFAULT NULL,
>  `template_id` int(32) unsigned DEFAULT NULL,
>  `alias` varchar(255) DEFAULT NULL,
>  `layout` varchar(255) DEFAULT NULL,
>  PRIMARY KEY (`menu_id`),
>  KEY `index` (`parent_menu_id`,`menu_link`,`plugin`,`alias`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> 
> ___
> Mailing list: https://launchpad.net/~maria-developers
> Post to : maria-developers@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~maria-developers
> More help   : https://help.launchpad.net/ListHelp


___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] How to change max key length for MyISAM?

[Reindl Harald]

Am 15.01.21 um 09:01 schrieb Luuk:

On 14-1-2021 21:45, Reindl Harald wrote:

I'm trying to install prestashop-1.7.7.1 on MyISAM


and why does that piece of software don't work with sensible defaults 
and force you too pacth source code and recompile your database server 
in a way nobody but you is running?


this is asking for troubles and until someone can prove a *real* 
benefit i call it bullshit and won't install "prestashop-1.7.7.1 on 
MyISAM"


the other possibilty is that it's designed to work with innoDB and 
*not* MyISAM which means: just don't enforce MyISAM, key sizes are 
your smallest problems in reality



A: It is a MySQL issue. 
http://stackoverflow.com/questions/8746207/1071-specified-key-was-too-long-max-key-length-is-1000-bytes 


as expected DO NOT INSTALL IT ON MYISAM jesus christ
ENGINE=InnoDB

and no it's not a mysql issue when a software is written in a way than 
even the installer dies


CREATE TABLE IF NOT EXISTS `pds_core_menu_items` (
  `menu_id` varchar(32) NOT NULL,
  `parent_menu_id` int(32) unsigned DEFAULT NULL,
  `menu_name` varchar(255) DEFAULT NULL,
  `menu_link` varchar(255) DEFAULT NULL,
  `plugin` varchar(255) DEFAULT NULL,
  `menu_type` int(1) DEFAULT NULL,
  `extend` varchar(255) DEFAULT NULL,
  `new_window` int(1) DEFAULT NULL,
  `rank` int(100) DEFAULT NULL,
  `hide` int(1) DEFAULT NULL,
  `template_id` int(32) unsigned DEFAULT NULL,
  `alias` varchar(255) DEFAULT NULL,
  `layout` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`menu_id`),
  KEY `index` (`parent_menu_id`,`menu_link`,`plugin`,`alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] How to change max key length for MyISAM?

[Luuk]

On 14-1-2021 21:45, Reindl Harald wrote:



Am 14.01.21 um 21:39 schrieb Witold Filipczyk:

Dnia Thu, Jan 14, 2021 at 08:52:14PM +0100, Sergei Golubchik napisał(a):

Hi, Witold!

On Jan 14, Witold Filipczyk wrote:


Why do you want to increase the max key length?


I like mysqlhotcopy as backup tool, but new soft requires big key.


Yes, that's what I was asking. What does it require, can you show an
example of a table definition?

I mean, a non-unique key doesn't have to be that long. If it's smaller,
it'll still help to narrow the search to all rows with the same
1000-byte prefix, and then just compare them all with the exact 3K
value. So 1K-key will still help even if values are 3K (except when all
your values have exactly the same 1K prefix, but it's not a very
realistic scenario). But with 1K keys you'll fit 3 times more keys per
index page, you'll have less disk IO, and less tree levels to search.

You would really need 3K, if you have 3K UNIQUE key, for UNIQUE 3k
contraints. Using 1K keys would change the semantics, values won't
necessarily be unique anymore. But since 10.4 MariaDB supports unique
constraints of arbitrary length. So, again, you should not need to
increase max key length for that (except that you're on 10.3 now).


I'm trying to install prestashop-1.7.7.1 on MyISAM


and why does that piece of software don't work with sensible defaults and 
force you too pacth source code and recompile your database server in a way 
nobody but you is running?


this is asking for troubles and until someone can prove a *real* benefit i 
call it bullshit and won#t install "prestashop-1.7.7.1 on MyISAM"


the other possibilty is that it's designed to work with innoDB and *not* 
MyISAM which means: just don't enforce MyISAM, key sizes are your smallest 
problems in reality



Error 1071 at Installation Prestashop 1.7.11 - Installing PrestaShop for the 
first time - PrestaShop Forums 



[quoot]

April 18, 2017

Q: i have a problem. i cant install the prestashop 1.7.11 because i get this 
Error Message: "SQLSTATE[42000]: Syntax error or access violation: 1071 
Specified key was too long; max key length is 1000 bytes".How can i fix this 
problem. i have tried everything:(


A: It is a MySQL issue. 
http://stackoverflow.com/questions/8746207/1071-specified-key-was-too-long-max-key-length-is-1000-bytes 



[/quoot]

😵😖🥱🥱😉




___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp
___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] How to change max key length for MyISAM?

[Reindl Harald]

Am 14.01.21 um 21:39 schrieb Witold Filipczyk:

Dnia Thu, Jan 14, 2021 at 08:52:14PM +0100, Sergei Golubchik napisał(a):

Hi, Witold!

On Jan 14, Witold Filipczyk wrote:


Why do you want to increase the max key length?


I like mysqlhotcopy as backup tool, but new soft requires big key.


Yes, that's what I was asking. What does it require, can you show an
example of a table definition?

I mean, a non-unique key doesn't have to be that long. If it's smaller,
it'll still help to narrow the search to all rows with the same
1000-byte prefix, and then just compare them all with the exact 3K
value. So 1K-key will still help even if values are 3K (except when all
your values have exactly the same 1K prefix, but it's not a very
realistic scenario). But with 1K keys you'll fit 3 times more keys per
index page, you'll have less disk IO, and less tree levels to search.

You would really need 3K, if you have 3K UNIQUE key, for UNIQUE 3k
contraints. Using 1K keys would change the semantics, values won't
necessarily be unique anymore. But since 10.4 MariaDB supports unique
constraints of arbitrary length. So, again, you should not need to
increase max key length for that (except that you're on 10.3 now).


I'm trying to install prestashop-1.7.7.1 on MyISAM


and why does that piece of software don't work with sensible defaults 
and force you too pacth source code and recompile your database server 
in a way nobody but you is running?


this is asking for troubles and until someone can prove a *real* benefit 
i call it bullshit and won#t install "prestashop-1.7.7.1 on MyISAM"


the other possibilty is that it's designed to work with innoDB and *not* 
MyISAM which means: just don't enforce MyISAM, key sizes are your 
smallest problems in reality


___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] How to change max key length for MyISAM?

[Witold Filipczyk]

Dnia Thu, Jan 14, 2021 at 08:52:14PM +0100, Sergei Golubchik napisał(a):
> Hi, Witold!
> 
> On Jan 14, Witold Filipczyk wrote:
> 
> > > Why do you want to increase the max key length?
> > 
> > I like mysqlhotcopy as backup tool, but new soft requires big key.
> 
> Yes, that's what I was asking. What does it require, can you show an
> example of a table definition?
> 
> I mean, a non-unique key doesn't have to be that long. If it's smaller,
> it'll still help to narrow the search to all rows with the same
> 1000-byte prefix, and then just compare them all with the exact 3K
> value. So 1K-key will still help even if values are 3K (except when all
> your values have exactly the same 1K prefix, but it's not a very
> realistic scenario). But with 1K keys you'll fit 3 times more keys per
> index page, you'll have less disk IO, and less tree levels to search.
> 
> You would really need 3K, if you have 3K UNIQUE key, for UNIQUE 3k
> contraints. Using 1K keys would change the semantics, values won't
> necessarily be unique anymore. But since 10.4 MariaDB supports unique
> constraints of arbitrary length. So, again, you should not need to
> increase max key length for that (except that you're on 10.3 now).

I'm trying to install prestashop-1.7.7.1 on MyISAM.

> 
> Regards,
> Sergei
> VP of MariaDB Server Engineering
> and secur...@mariadb.org

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] How to change max key length for MyISAM?

[Sergei Golubchik]

Hi, Witold!

On Jan 14, Witold Filipczyk wrote:

> > Why do you want to increase the max key length?
> 
> I like mysqlhotcopy as backup tool, but new soft requires big key.

Yes, that's what I was asking. What does it require, can you show an
example of a table definition?

I mean, a non-unique key doesn't have to be that long. If it's smaller,
it'll still help to narrow the search to all rows with the same
1000-byte prefix, and then just compare them all with the exact 3K
value. So 1K-key will still help even if values are 3K (except when all
your values have exactly the same 1K prefix, but it's not a very
realistic scenario). But with 1K keys you'll fit 3 times more keys per
index page, you'll have less disk IO, and less tree levels to search.

You would really need 3K, if you have 3K UNIQUE key, for UNIQUE 3k
contraints. Using 1K keys would change the semantics, values won't
necessarily be unique anymore. But since 10.4 MariaDB supports unique
constraints of arbitrary length. So, again, you should not need to
increase max key length for that (except that you're on 10.3 now).

Regards,
Sergei
VP of MariaDB Server Engineering
and secur...@mariadb.org

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] How to change max key length for MyISAM?

[Witold Filipczyk]

Dnia Thu, Jan 14, 2021 at 08:25:42PM +0100, Sergei Golubchik napisał(a):
> Hi, Witold!
> 
> On Jan 14, Witold Filipczyk wrote:
> > Sorry, for private question, but I did not know where to ask.
> > mariadb-10.3.27.
> > 
> > I changed in include/myisam.h:
> > 
> > #define MI_MAX_KEY_LENGTH   3072/* Max length in bytes 
> > */
> > 
> > but, it did not help, still this ERROR 1071 (42000): Specified key was too 
> > long; max key length is 1000 bytes.
> > 
> > What more to change to get key length 3072 for MyISAM tables?
> 
> Change HA_MAX_KEY_LENGTH. MI_MAX_KEY_LENGTH is only used for temporary
> tables.

I'll try. Thanks!

> 
> > And what can be negative impact of such a change?
> 
> More memory on the stack, so if you have a table with lots and lots of
> rows, searching for long keys is more likely to cause overflow the
> stack. Might be slower, perhaps, as it'll need to copy more data.
> It's all "might", may be there will be no negative impact at all.
> 
> Why do you want to increase the max key length?

I like mysqlhotcopy as backup tool, but new soft requires big key.

> 
> Regards,
> Sergei
> VP of MariaDB Server Engineering
> and secur...@mariadb.org

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-developers] How to change max key length for MyISAM?

[Sergei Golubchik]

Hi, Witold!

On Jan 14, Witold Filipczyk wrote:
> Sorry, for private question, but I did not know where to ask.
> mariadb-10.3.27.
> 
> I changed in include/myisam.h:
> 
> #define MI_MAX_KEY_LENGTH   3072/* Max length in bytes */
> 
> but, it did not help, still this ERROR 1071 (42000): Specified key was too 
> long; max key length is 1000 bytes.
> 
> What more to change to get key length 3072 for MyISAM tables?

Change HA_MAX_KEY_LENGTH. MI_MAX_KEY_LENGTH is only used for temporary
tables.

> And what can be negative impact of such a change?

More memory on the stack, so if you have a table with lots and lots of
rows, searching for long keys is more likely to cause overflow the
stack. Might be slower, perhaps, as it'll need to copy more data.
It's all "might", may be there will be no negative impact at all.

Why do you want to increase the max key length?

Regards,
Sergei
VP of MariaDB Server Engineering
and secur...@mariadb.org

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp

[Maria-developers] How to change max key length for MyISAM?

[Witold Filipczyk]

Sorry, for private question, but I did not know where to ask.
mariadb-10.3.27.

I changed in include/myisam.h:

#define MI_MAX_KEY_LENGTH   3072/* Max length in bytes */

but, it did not help, still this ERROR 1071 (42000): Specified key was too 
long; max key length is 1000 bytes.

What more to change to get key length 3072 for MyISAM tables?
And what can be negative impact of such a change?

Witold Filipczyk

___
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp