This morning I'm getting a bunch of errors that look like the following:
ERROR: CREATE TABLE IF NOT EXISTS admin_list ( admin varchar(255) binary NOT NULL default '', PRIMARY KEY (admin) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC: Specified key was too long; max key length is 767 bytes
I don't know whether this might be related to yesterday's database outage. I also don't understand the error message, since the primary key clearly is 255 bytes, which is less than 767.
What database are you using?
On 12/08 08:59, Russell Blau wrote:
This morning I'm getting a bunch of errors that look like the following:
ERROR: CREATE TABLE IF NOT EXISTS admin_list ( admin varchar(255) binary NOT NULL default '', PRIMARY KEY (admin) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC: Specified key was too long; max key length is 767 bytes
I don't know whether this might be related to yesterday's database outage. I also don't understand the error message, since the primary key clearly is 255 bytes, which is less than 767.
-- Russell Blau russblau@imapmail.org
Cloud mailing list -- cloud@lists.wikimedia.org List information: https://lists.wikimedia.org/postorius/lists/cloud.lists.wikimedia.org/
Toolsdb is a service that has many databases:
``` $ ssh login.toolforge.org
dcaro@tools-sgebastion-10:~$ mysql --defaults-file=$HOME/replica.my.cnf -h tools.db.svc.wikimedia.cloud
dcaro@tools-sgebastion-10:~$ mysql --defaults-file=$HOME/replica.my.cnf -h tools.db.svc.wikimedia.cloud <<< "show databases;" | wc 150 150 2992 ```
You had to create your own at some point (see https://wikitech.wikimedia.org/wiki/Help:Toolforge/Database#User_databases).
You can check the database you are connected to with the query: ``` select DATABASE(); ```
On 12/08 11:11, Russell Blau wrote:
As it says in the subject line, it is toolsdb. __________________________________________________________________
From: David Caro dcaro@wikimedia.org Sent: Thursday, December 8, 2022 11:07 AM To: Wikimedia Cloud Services general discussion and support cloud@lists.wikimedia.org Subject: [Cloud] Re: Key errors on toolsdb database
What database are you using? On 12/08 08:59, Russell Blau wrote:
This morning I'm getting a bunch of errors that look like the
following:
ERROR: CREATE TABLE IF NOT EXISTS admin_list ( admin varchar(255)
binary NOT NULL default '', PRIMARY KEY (admin) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC: Specified key was too long; max key length is 767 bytes
I don't know whether this might be related to yesterday's database
outage. I also don't understand the error message, since the primary key clearly is 255 bytes, which is less than 767.
-- Russell Blau russblau@imapmail.org _______________________________________________ Cloud mailing list -- cloud@lists.wikimedia.org List information:
https://lists.wikimedia.org/postorius/lists/cloud.lists.wikimedia.org/
David Caro SRE - Cloud Services Wikimedia Foundation https://wikimediafoundation.org/ PGP Signature: 7180 83A2 AC8B 314F B4CE 1171 4071 C7E1 D262 69C3 "Imagine a world in which every single human being can freely share in the sum of all knowledge. That's our commitment."
Cloud mailing list -- cloud@lists.wikimedia.org List information: https://lists.wikimedia.org/postorius/lists/cloud.lists.wikimedia.org/
Sorry; it is the database 's51290__dpl_p' on tools.db.svc.wikimedia.cloud.
And I realize since my initial message that a VARCHAR() can be more than one byte per character, so perhaps the key may not be less than 767 bytes after all; but the job never threw this error before today.
On Thu, Dec 8, 2022, at 11:32 AM, David Caro wrote:
Toolsdb is a service that has many databases:
$ ssh login.toolforge.org dcaro@tools-sgebastion-10:~$ mysql --defaults-file=$HOME/replica.my.cnf -h tools.db.svc.wikimedia.cloud dcaro@tools-sgebastion-10:~$ mysql --defaults-file=$HOME/replica.my.cnf -h tools.db.svc.wikimedia.cloud <<< "show databases;" | wc 150 150 2992You had to create your own at some point (see https://wikitech.wikimedia.org/wiki/Help:Toolforge/Database#User_databases).
You can check the database you are connected to with the query:
select DATABASE();On 12/08 11:11, Russell Blau wrote:
As it says in the subject line, it is toolsdb. __________________________________________________________________
From: David Caro dcaro@wikimedia.org Sent: Thursday, December 8, 2022 11:07 AM To: Wikimedia Cloud Services general discussion and support cloud@lists.wikimedia.org Subject: [Cloud] Re: Key errors on toolsdb database
What database are you using? On 12/08 08:59, Russell Blau wrote:
This morning I'm getting a bunch of errors that look like the
following:
ERROR: CREATE TABLE IF NOT EXISTS admin_list ( admin varchar(255)
binary NOT NULL default '', PRIMARY KEY (admin) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC: Specified key was too long; max key length is 767 bytes
I don't know whether this might be related to yesterday's database
outage. I also don't understand the error message, since the primary key clearly is 255 bytes, which is less than 767.
-- Russell Blau russblau@imapmail.org _______________________________________________ Cloud mailing list -- cloud@lists.wikimedia.org List information:
https://lists.wikimedia.org/postorius/lists/cloud.lists.wikimedia.org/
David Caro SRE - Cloud Services Wikimedia Foundation https://wikimediafoundation.org/ PGP Signature: 7180 83A2 AC8B 314F B4CE 1171 4071 C7E1 D262 69C3 "Imagine a world in which every single human being can freely share in the sum of all knowledge. That's our commitment."
Cloud mailing list -- cloud@lists.wikimedia.org List information: https://lists.wikimedia.org/postorius/lists/cloud.lists.wikimedia.org/
-- David Caro SRE - Cloud Services Wikimedia Foundation https://wikimediafoundation.org/ PGP Signature: 7180 83A2 AC8B 314F B4CE 1171 4071 C7E1 D262 69C3
"Imagine a world in which every single human being can freely share in the sum of all knowledge. That's our commitment."
Cloud mailing list -- cloud@lists.wikimedia.org List information: https://lists.wikimedia.org/postorius/lists/cloud.lists.wikimedia.org/
Attachments:
- signature.asc
Note also that innodb supports indexes larger than that (up to 3072 bytes), but they require a minimum version and specific configuration. It is very picky (depends on vendor, version, config, table version and format, character set, etc.). But here is some hopefully useful links to help with the riddle (it is a very common confusing issue :-(): https://stackoverflow.com/questions/45822688/key-was-too-long-in-mariadb-but... https://answers.launchpad.net/maria/+question/241612
On Thu, Dec 8, 2022 at 7:17 PM Russell Blau russblau@imapmail.org wrote:
Sorry; it is the database 's51290__dpl_p' on tools.db.svc.wikimedia.cloud.
And I realize since my initial message that a VARCHAR() can be more than one byte per character, so perhaps the key may not be less than 767 bytes after all; but the job never threw this error before today.
On Thu, Dec 8, 2022, at 11:32 AM, David Caro wrote:
Toolsdb is a service that has many databases:
$ ssh login.toolforge.org dcaro@tools-sgebastion-10:~$ mysql --defaults-file=$HOME/replica.my.cnf -h tools.db.svc.wikimedia.cloud dcaro@tools-sgebastion-10:~$ mysql --defaults-file=$HOME/replica.my.cnf -h tools.db.svc.wikimedia.cloud <<< "show databases;" | wc 150 150 2992You had to create your own at some point (see
https://wikitech.wikimedia.org/wiki/Help:Toolforge/Database#User_databases ).
You can check the database you are connected to with the query:
select DATABASE();On 12/08 11:11, Russell Blau wrote:
As it says in the subject line, it is toolsdb. __________________________________________________________________
From: David Caro dcaro@wikimedia.org Sent: Thursday, December 8, 2022 11:07 AM To: Wikimedia Cloud Services general discussion and support cloud@lists.wikimedia.org Subject: [Cloud] Re: Key errors on toolsdb database
What database are you using? On 12/08 08:59, Russell Blau wrote:
This morning I'm getting a bunch of errors that look like the
following:
ERROR: CREATE TABLE IF NOT EXISTS admin_list ( admin varchar(255)
binary NOT NULL default '', PRIMARY KEY (admin) ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC: Specified key was too long; max key length is 767 bytes
I don't know whether this might be related to yesterday's database
outage. I also don't understand the error message, since the primary key clearly is 255 bytes, which is less than 767.
-- Russell Blau russblau@imapmail.org _______________________________________________ Cloud mailing list -- cloud@lists.wikimedia.org List information:
https://lists.wikimedia.org/postorius/lists/cloud.lists.wikimedia.org/
-- David Caro SRE - Cloud Services Wikimedia Foundation https://wikimediafoundation.org/ PGP Signature: 7180 83A2 AC8B 314F B4CE 1171 4071 C7E1 D262 69C3 "Imagine a world in which every single human being can freely share
in
the sum of all knowledge. That's our commitment."
Cloud mailing list -- cloud@lists.wikimedia.org List information:
https://lists.wikimedia.org/postorius/lists/cloud.lists.wikimedia.org/
-- David Caro SRE - Cloud Services Wikimedia Foundation https://wikimediafoundation.org/ PGP Signature: 7180 83A2 AC8B 314F B4CE 1171 4071 C7E1 D262 69C3
"Imagine a world in which every single human being can freely share in
the
sum of all knowledge. That's our commitment."
Cloud mailing list -- cloud@lists.wikimedia.org List information: https://lists.wikimedia.org/postorius/lists/cloud.lists.wikimedia.org/
Attachments:
- signature.asc
-- Russell Blau russblau@imapmail.org _______________________________________________ Cloud mailing list -- cloud@lists.wikimedia.org List information: https://lists.wikimedia.org/postorius/lists/cloud.lists.wikimedia.org/
On Fri, Dec 9, 2022 at 2:26 AM Russell Blau russblau@imapmail.org wrote:
So, putting two and two together, it sounds likely that when Francesco re-enabled write mode on toolsdb yesterday, they did not restart it with the same configuration options that it was using before, particularly whatever control the max key length.
I think that after the reboot the value for innodb_large_prefix changed to "false", and innodb_file_format changed to "Antelope", because those variables (introduced in [1]) were removed recently from the tools.my.cnf.erb file in [2] and [3].
I re-instated the values innodb_file_format=Barracuda and innodb_large_prefix=1 by manually running "SET GLOBAL" on clouddb1001.
Those variables will no longer be needed after we upgrade ToolsDB to MariaDB 10.4 [4]
[1] https://gerrit.wikimedia.org/r/c/operations/puppet/+/389477/ [2] https://gerrit.wikimedia.org/r/c/operations/puppet/+/837490/ [3] https://gerrit.wikimedia.org/r/c/operations/puppet/+/769025/ [4] https://phabricator.wikimedia.org/T301949