[04:52:22] 10DBA, 10Cloud-Services, 10User-Urbanecm: Prepare and check storage layer for idwikimedia - https://phabricator.wikimedia.org/T193187#4163163 (10Marostegui) p:05Triage>03Normal Let us know when this is created so we can sanitize it [07:46:34] 10DBA, 10Operations: Investigate dropping "edit_page_tracking" database table from Wikimedia wikis after archiving it - https://phabricator.wikimedia.org/T57385#4163250 (10Marostegui) a:03Marostegui I have backuped the tables and left them at: `/srv/backups/tmp/T57385/T57385.tar.gz` Most of the tables are e... [07:55:09] I am a bit blocked with ongoing compression, if you allow me, I would like to take one of the new hosts and test 8.0 there [07:55:32] (not to stay, just to test erase later) [08:09:04] go for it! [08:24:09] 10DBA, 10Quibble: Error: 1071 Specified key was too long; max key length is 767 bytes - https://phabricator.wikimedia.org/T193222#4163311 (10hashar) [08:25:00] hashar: that is a very easy one [08:25:25] jynus: but I only filled it a minute ago! :] [08:25:34] it is not the version [08:25:37] it is the configuration [08:25:44] I noticed tool labs has innodb_large_prefix = 1 and backend barracuda or something [08:25:49] yes [08:25:56] I collected the output of --help --verbose and comparing the config [08:25:58] I was looking for the official documentation [08:26:07] I guess the package betwee Jessie and Stretch have some differences [08:26:29] I would guess actually that it was manually fixed on jessie [08:26:40] but didn't work on reinstall [08:26:50] character-set-server collation-server are different [08:27:14] ah, that could be it, too [08:27:52] I think this is a good summary: https://stackoverflow.com/a/35851332/342196 [08:28:11] enable innodb_large_prefix [08:28:44] no need to reduce the size [08:29:40] do you want me to comment on the ticket? [08:30:28] 10DBA, 10Quibble: Error: 1071 Specified key was too long; max key length is 767 bytes - https://phabricator.wikimedia.org/T193222#4163321 (10hashar) [08:31:17] 10DBA, 10Quibble: Error: 1071 Specified key was too long; max key length is 767 bytes - https://phabricator.wikimedia.org/T193222#4163311 (10hashar) Diff of Jessie F17337439 versus Stretch F17337441 based on `mysqld --help --verbose`: ``` --- nodepool.txt 2018-04-27 10:28:05.640537847 +0200 +++ docker.txt 2018... [08:31:20] it is more than changing the variable, it needs a row format change [08:31:27] jynus: I pasted the whole configs and the diff [08:32:05] well, in your case you migrated from latin1 [08:32:07] in Docker a db is initialized with something like mysql_db_init then I spawn it in the background as the current user [08:32:11] to utf8mb4 [08:32:22] that multiplied the max size by 4 [08:32:27] yup make sense [08:32:28] of everything [08:32:34] which is not bad [08:32:46] but definitely you want to use a modern row format [08:32:47] like a VARCHAR(255) could be made of 255 unicode characters each of 4 bytes or 1020 bytes [08:33:02] we no longer support <5.5 [08:33:16] so innodb_large_prefix is ok to advice [08:33:21] we don't do that on production [08:33:28] because using binary == using latin1 [08:33:35] in terms of sizes [08:34:51] what I noticed is (toollabs?) uses: [08:34:52] innodb_file_format = Barracuda [08:34:52] innodb_large_prefix = 1 [08:35:01] but barracuda, dynamic (or compressed) and large prefix index should almost be enforced [08:35:14] yep [08:35:16] that comes from https://phabricator.wikimedia.org/T179614 [08:35:22] but it needs a table rebuild [08:35:33] those are defaults [08:35:36] and since the mysql database is created from scratch at the start of the job, I have no migration or past data to migrate [08:35:39] if old defults are used [08:35:45] they need to be rebuilt [08:35:56] then just changing those options should work for you [08:36:05] so I guess mysql_db_init will take in account the options [08:36:07] awesome [08:36:18] gonna craft a config file, rebuild the container and see what happens :) [08:36:28] btw [08:36:39] I am not sure how tested is [08:36:48] real utf8 on mediawiki [08:37:13] I know some time ago the recommended ones were binary or fake (3-byte) utf8 [08:37:51] I think real utf8 (utf8mb4) should be a target, but I wouldn't be surprised with more bugs [08:38:06] it is the default everywhere [08:38:53] also, it would be nice to enable this on CI/beta https://phabricator.wikimedia.org/T108255 [08:39:11] asked 3 years ago warning users will complain [08:39:22] and now it is also the default everywhere [08:40:11] there is also https://phabricator.wikimedia.org/T150949 [08:40:12] 10DBA, 10Quibble: Error: 1071 Specified key was too long; max key length is 767 bytes - https://phabricator.wikimedia.org/T193222#4163325 (10hashar) That is similar to T179614. Jaime explained it as: ---- It is a migration from latin1 to utf8mb4 hence the maximum reacheable size is multiplied by 4. He pointed... [08:40:15] jynus: well I am probably going to use utf8 for the CI job [08:40:21] and we will catch whatever is breaking this way [08:40:56] real or fake? [08:41:10] fake utf8 is called utf8 [08:41:13] oh [08:41:17] real utf8 is called utf8mb4 [08:41:32] I strongly suggest real one [08:41:36] sold! [08:41:46] it will fail more [08:42:03] but everything that works with it will work with the 3-byte one [08:42:07] I am going to trigger the new job with that config against ever single mediawiki extension [08:42:10] (and has emoji support) [08:42:19] and will be able to collect interesting results for sure [08:42:24] ^this is higly critical [08:42:30] emoji? [08:42:33] HECK FOR SURE [08:43:40] we can create a task for "better support utf8mb4" [08:43:53] and even recommend it on a future release [08:44:18] about strict mode, maybe we should talk about it during the hackathon and maybe implement it as well. Seems it is all about adding a few settings here and there [08:44:25] for beta that is surely a trivial fix [08:44:30] hashar: it is a single setting [08:44:36] the problem is chicken and egg [08:44:42] for CI it might suddenly blocks a few things here and there but most probably they are easy patches [08:44:42] if extensions don't support it [08:44:48] exactly [08:44:54] they don't fix code [08:45:03] well we can enable it, announce the breaking change, and catch up whatever test failures we encounter [08:45:04] and if they don't fix code CI doesn't add it [08:45:27] but litteraly not enabling it has made real bugs go through the cracks [08:45:28] that is the same for latin1 utf8mb4 [08:45:37] heck [08:45:47] while I am at adding utf8mb4 settings, I could add the strict mode as well [08:45:58] is that done in my.cnf ? [08:46:01] yes [08:46:10] while I agree on not breaking workflow [08:46:20] I would like to be stricter, specially on testing [08:46:31] or push towards it [08:46:38] we have an excellent opportunity right now [08:46:44] the thing is [08:46:50] we enable it on testing [08:46:51] I am creating new CI jobs that are not going to be triggered when devs send a patch [08:46:56] or we will recive lots of bus [08:47:01] *bugs from end users [08:47:11] as all these options are now defaults [08:47:13] once I am happy with those jobs I will trigger them on every single extension/branches, collect result and make the new job pass [08:47:32] so it is not like obscure options! [08:48:24] would you mind updating the task about strict mode to list the settings to be added? https://phabricator.wikimedia.org/T108255 [08:48:34] at least as a reference for the future [08:48:58] and I will add the setting to the new jobs, together with innodb_large_prefix [08:49:03] * hashar coffee break [08:49:10] you mean the actual my.cnf settings? [08:52:30] euh yeah [08:52:43] mysql configuration settings is a foreign language to me [08:52:48] well SQL as well [08:53:42] I will add them to T119371 too [08:53:42] T119371: Enable MariaDB/MySQL strict mode on CI slaves - https://phabricator.wikimedia.org/T119371 [09:00:08] 10DBA, 10Continuous-Integration-Infrastructure, 10MediaWiki-Database: Enable MariaDB/MySQL strict mode on CI slaves - https://phabricator.wikimedia.org/T119371#4163341 (10jcrespo) [09:01:00] 10DBA, 10Continuous-Integration-Infrastructure, 10MediaWiki-Database: Enable MariaDB/MySQL strict mode on CI slaves - https://phabricator.wikimedia.org/T119371#1824701 (10jcrespo) @hashar Updated with actual configuration to add to my.cnf, and a bit of comments. [09:02:16] jynus: given CI will uses MariaDB 10.0.32 and 10.1.26 should we use ONLY_FULL_GROUP_BY ? [09:02:37] I dont know how mariadb versions map to mysql ones, you mentionned it should be mysql >= 5.7 [09:02:40] I don't know, I would try [09:02:54] depending on feedback, we can disable it [09:02:54] well we can start with TRADITIONAL [09:03:05] that is probably going to catch a few things already that would have to be addressed [09:03:12] and later look at adding ONLY_FULL_GROUP_BY [09:03:14] but traditional is the default on all modern versions of both [09:03:26] so we should be compatible with it [09:03:44] note that mediawiki may* override this [09:03:49] on each query [09:04:01] so it may also be a mediawiki config [09:04:14] I think it does sql_mode='' on every transaction [09:04:56] 10DBA, 10Beta-Cluster-Infrastructure, 10Continuous-Integration-Infrastructure, 10MediaWiki-Database, and 2 others: Enable MariaDB/MySQL's Strict Mode - https://phabricator.wikimedia.org/T108255#4163348 (10jcrespo) [09:05:43] hashar: you literally said that https://phabricator.wikimedia.org/T108255#2708529 [09:05:50] $wgSQLMode [09:06:04] yeah [09:06:20] added by Domas Mituzas in June 2010 so it is every where [09:06:20] so setting the server may not really be that important [09:06:44] which was ok back then [09:06:48] but not in 2018 [09:07:13] so we would need both a setting in my.cnf and $wgSQLMode = TRADITIONAL; [09:07:15] in fact, an option to not do a roundtrip for WMF would be nice [09:07:30] that may be 1ms lost on every query [09:07:46] and we do 500000 of those every secod [09:08:15] not that this is something you handle, but I will propose to core to be able to optionally remove it [09:10:56] hashar: https://www.slideshare.net/jynus/query-optimization-with-mysql-57-and-mariadb-10-even-newer-tricks/43 [09:11:11] this is how I convince people to stop using sql_mode='' [09:11:20] :] [09:11:40] I am giving $wgSQLMode = 'TRADITIONAL'; a try https://gerrit.wikimedia.org/r/#/c/429386/1/includes/DevelopmentSettings.php [09:11:45] thanks! [09:11:48] that is the PHP file included on CI [09:12:06] it is ok to revert [09:12:12] because Y is broken [09:12:18] but at leasy we will know it [09:12:59] so 'tv mode' is not in the enum. one would expect an exception of some sort but I guess mysql is being newbie friendly [09:13:13] those were bad old defaults [09:13:22] but 3 warnings and no error? [09:13:25] nice [09:14:00] https://www.slideshare.net/jynus/query-optimization-with-mysql-57-and-mariadb-10-even-newer-tricks/45 == TRADITIONAL fixes it [09:14:14] AHHHHHHHH [09:14:34] and "performance" (which I doubt would be be an issue for us) should not be a concern for testing envs [09:14:44] so without TRADITIONAL, mysql behave like PHP: it is doing bad tricks and cause corner case bugs [09:14:48] yes [09:14:56] which for php may be actually ok [09:15:05] but not for reliable storage [09:15:35] if you like mysql, I am proud of those sildes if you want to check them (if you had the time) [09:16:52] drupas is traditional bv default, and also is ruby on rails [09:17:01] mediawiki shouldn't get behind [09:17:04] :-) [09:17:45] my stance is MySQL used to be a quick and dirty database but opened the world of databases/sql to the mass [09:17:54] oh, and that is ok [09:17:58] making it trivial to install and use a db. Similar to PHP [09:17:58] I don't complain [09:18:01] yes [09:18:09] but we as in, mediawiki/wmf [09:18:17] can aspire to be higher quality [09:18:25] but both mysql and php have a lot of issues. For beginners it is good enough, but intermediate users will end up with bugs and issues [09:18:37] I am not blaming mysql nor php [09:18:40] while expert will know about them and code/act based on the "limitation" of mysql/php [09:18:53] I am saying, mysql now fixed its default behaviour [09:18:58] we should follow! [09:19:10] then you have wikimedia which has a set of guru/expert that know all the tricks and get mysql to work just fine [09:19:43] so probably ideally one should start with postgre and a typed language of some sort [09:19:59] at least it is good to know MySQL caught up and is being more strict. That teach good habits to everyone [09:20:03] yes [09:20:16] but there are old code bases than need to adapt [09:20:27] and you know, wheneverI talked about the wikimedia cluster, people are in choke about us using mysql [09:20:55] that is until I tell them that our DBA know all the settings and read the C code (ie: you master the beast) [09:21:25] you are putting read the code like if it was an achivement [09:21:32] well [09:21:35] it is the whole poing of open source [09:21:42] read and patch easier [09:21:51] by that I mean you know how it works internally and don't just blindly flip settings here and there hoping for the best [09:22:22] my english crap bah [09:22:34] people that say "you shoud use postgres" havent ever admin a large cluster [09:22:40] it has high operational pains [09:23:49] (and I don't have anything against postgres at all) but I would never dare to tell the "expert" on an organization to "use something else" [09:23:52] without context [09:24:19] imagine me telling you you should use X CI software which I heard on hacker news is better [09:25:15] yeah that is the point [09:25:32] postgre is probably an equal choice for smallish sites [09:25:42] but at our level, the difference is not in the sfotware but in the expertise [09:25:48] for small sites you could use almost anything [09:26:00] at which point it is people making the difference, not the db backend (except mongodb) [09:30:49] 10DBA, 10MediaWiki-Database, 10Operations: Evaluate and decide the future of relational datastore at WMF after the upgrade of MariaDB 10.1 is finished - https://phabricator.wikimedia.org/T193224#4163403 (10jcrespo) [09:31:51] 10DBA, 10MediaWiki-Database, 10Operations: Evaluate and decide the future of relational datastore at WMF after the upgrade of MariaDB 10.1 is finished - https://phabricator.wikimedia.org/T193224#4163394 (10jcrespo) p:05Triage>03Low (Low for now, likely to get a boost for the 15-year plan) [09:33:34] 10DBA: Test MySQL 8.0 with production data and evaluate its fit for WMF databases - https://phabricator.wikimedia.org/T193226#4163421 (10jcrespo) p:05Triage>03Low [09:33:47] 10DBA: Test MySQL 8.0 with production data and evaluate its fit for WMF databases - https://phabricator.wikimedia.org/T193226#4163435 (10jcrespo) p:05Low>03Normal [09:37:18] Function: WikiPage::doDeleteArticleReal/multi-row [09:37:18] Error: 19 NOT NULL constraint failed: unittest_archive.ar_text [09:37:25] that is mediawiki with sqlite though [09:39:41] 10DBA, 10Cloud-Services, 10cloud-services-team (Kanban): Prepare storage layer for euwikisource - https://phabricator.wikimedia.org/T189466#4163451 (10Urbanecm) 05Resolved>03Open It isn't possible to access the replicated database from Toolforge. ``` urbanecm@tools-bastion-03 ~ $ sql --cluster analyt... [09:39:53] hashar: might be related to? https://gerrit.wikimedia.org/r/#/c/393929/ ? [09:39:55] hashar: I remember anomie fixing something like that [09:40:05] eyah I guess some sqlite patch is wrong [09:40:24] https://gerrit.wikimedia.org/r/#/c/429386/ might be enabling TRADITIONAL, would have to confirm whether that is actually the case [09:40:25] hashar: actually like anomie about to run a job manually fixing that [09:40:37] maybe by crafting a tesdt that intentionally should trigger an error [09:40:40] due to some old mediawiki bug [09:40:49] check with him [09:40:52] 10DBA, 10Cloud-Services, 10cloud-services-team (Kanban): Prepare storage layer for euwikisource - https://phabricator.wikimedia.org/T189466#4163454 (10MarcoAurelio) Yep, confirmed on my side as well. [09:41:43] 10DBA, 10Cloud-Services, 10cloud-services-team (Kanban): Prepare storage layer for euwikisource - https://phabricator.wikimedia.org/T189466#4163455 (10Marostegui) 05Open>03Resolved Fixed ``` marostegui@tools-bastion-03:~$ sql --cluster analytics euwikisource_p Reading table information for completion o... [09:42:36] hashar: maybe not setting it and setting it on the server would work? [09:42:49] if it only runs when it is set [09:43:01] as long as it is not set to '' we should be ok [09:43:34] but I would consider that actually a code bug [09:45:17] 10DBA, 10Cloud-Services, 10Hindi-Sites, 10Patch-For-Review, and 2 others: Prepare and check storage layer for hiwikimedia - https://phabricator.wikimedia.org/T188490#4163459 (10Urbanecm) Thanks! @Bstorm, are you sure this patch is directy related to this task? I think another task "Add GRANT statement to $... [09:45:55] marostegui: I commented on anomie task at https://phabricator.wikimedia.org/T33223#4163460 :] it is probably a trivial issue related to upgrading a database schema [09:46:35] 10DBA, 10Cloud-Services, 10Hindi-Sites, 10Patch-For-Review, and 2 others: Prepare and check storage layer for hiwikimedia - https://phabricator.wikimedia.org/T188490#4163462 (10Marostegui) >>! In T188490#4163459, @Urbanecm wrote: > Thanks! @Bstorm, are you sure this patch is directy related to this task? I... [09:46:46] jynus: we will see :) I am heading back to innodb_file_format / innodb_large_prefix. [09:47:06] yes, that should be more immediate fix [09:47:16] *a [09:50:06] compressing ongoing: https://grafana.wikimedia.org/dashboard/file/server-board.json?refresh=1m&panelId=17&fullscreen&orgId=1&var-server=db1090&var-network=eno1&from=now-24h&to=now [09:50:11] going for a coffee [10:03:49] café [10:15:05] 10DBA, 10Cloud-Services, 10Hindi-Sites, 10Patch-For-Review, and 2 others: Prepare and check storage layer for hiwikimedia - https://phabricator.wikimedia.org/T188490#4163506 (10Urbanecm) Well, it isnt related to JUST this task but all task I reopened and you fixed. [10:17:33] 10DBA, 10Cloud-Services, 10Hindi-Sites, 10Patch-For-Review, and 2 others: Prepare and check storage layer for hiwikimedia - https://phabricator.wikimedia.org/T188490#4163509 (10Marostegui) >>! In T188490#4163506, @Urbanecm wrote: > Well, it isnt related to JUST this task but all task I reopened and you fix... [10:38:11] jynus: The purge lag is zero now. Shall we continue? :D [10:38:24] let me rebase my patch [10:41:33] jynus: marostegui https://gerrit.wikimedia.org/r/#/c/428297/ please deploy when you have some time [10:51:10] Amir1: thank you for waiting [10:51:17] we can deploy now [10:52:19] Thank you! [10:53:01] it will likely happen again, and then we will do a defragmentation everywhere [10:54:02] yeah [10:55:41] it should be now on terbium [10:56:48] thanks, I think we should wait for half an hour until it starts running [10:57:25] ok [10:57:38] have a look at the log when that happens in case something goes wrong [11:09:54] 10DBA, 10Patch-For-Review: Productionize 8 eqiad hosts - https://phabricator.wikimedia.org/T192979#4163605 (10jcrespo) [11:14:15] sure thing [11:14:45] didn't I upload a mysql package? [11:17:19] oh, I did it to jessie [12:05:47] 10DBA, 10Continuous-Integration-Infrastructure, 10MediaWiki-Database, 10Quibble, and 2 others: Enable MariaDB/MySQL strict mode on CI slaves - https://phabricator.wikimedia.org/T119371#4163655 (10hashar) a:03hashar Can be done in the new #Quibble containers [12:05:51] 10DBA, 10Quibble, 10Patch-For-Review, 10Release-Engineering-Team (Kanban): Error: 1071 Specified key was too long; max key length is 767 bytes - https://phabricator.wikimedia.org/T193222#4163659 (10hashar) a:03hashar [12:14:07] jynus: I made a stupid mistake [12:14:12] can I make a patch? [12:19:59] jynus: marostegui https://gerrit.wikimedia.org/r/429407 [12:23:56] Amir1: merged [12:25:28] jynus: It would be great if you run puppet agent on terbium, in five minutes the next run starts [12:25:37] ah, yes [12:27:06] done [12:27:41] Thank you! [12:27:51] I keep an eye on the logs [12:53:55] Everything is fine, the purge lag is going up though :D https://grafana.wikimedia.org/dashboard/db/mysql?panelId=11&fullscreen&orgId=1&var-dc=codfw%20prometheus%2Fops&var-server=dbstore2001&var-port=13318&from=now-3h&to=now [12:55:47] as expected :-) [12:58:38] jynus: so eventually I went with a setting file and i still get key too long :D https://gerrit.wikimedia.org/r/#/c/429406/1/dockerfiles/quibble-jessie/mariadb.cnf [12:58:47] I am missing something somewhere and investigating still [12:59:30] unrelated, but the best collation for universal support [13:00:05] utf8mb4_unicode_520_ci if available [13:00:14] or utf8mb4_unicode_ci otherwise [13:00:32] but let me check [13:01:37] Is innodb_file_per_table = 1, too? [13:01:46] not sure when the default changed for mariadb [13:02:04] can I have physical access to a standard database [13:02:08] created like that? [13:03:04] maybe the tables are being created with ROW_FORMAT=compact/redundant instead of dynamic [13:03:26] "MariaDB 10.0 and 10.1 have InnoDB 5.6, which by default creates tables with ROW_FORMAT=Compact (even if innodb_file_format is set to Barracuda). " [13:03:31] probably is that [13:03:54] so there is a mediawiki switch for innodb options [13:03:58] cannot remember which [13:04:27] at least it has Barracuda file format [13:04:44] yes, but barracuda is compatible with old row formats [13:04:53] innodb_default_row_format=DYNAMIC [13:04:59] ^that on my.cnf [13:05:02] should do the trick [13:05:09] sorry for all those issues [13:05:31] from 5.5 to 8.0 and 10.2 there has been a lot of transition to saner defaults [13:05:45] latest versions should have everthing ok [13:05:56] but it is highly confusing for versions in between [13:06:53] hashar: did you saw the innodb_default_row_format=DYNAMIC ? [13:07:16] yup but I cant find the setting in /usr/sbin/mysqld --help --verbose :D [13:07:24] I swear this is easier than it looks [13:07:48] SHOW GLOBAL VARIABLES like 'innodb_default%'; [13:08:01] mysql -e "SHOW GLOBAL VARIABLES like 'innodb_default%'" [13:08:10] innodb_default_encryption_key_id | 1 [13:08:11] :) [13:08:16] mmm [13:08:22] are you sure you are on 10.1? [13:08:48] 10.1.26 and innodb version 5.6.36 [13:08:58] I guess I screwed up something heavily :] [13:09:07] no, that is ok [13:09:14] how can I check the the raw format of a created table? [13:09:14] but on 10.1 there is a variable [13:09:21] Server version: 10.1.32-MariaDB MariaDB Server [13:09:26] innodb_default_row_format | compact [13:09:29] ^ [13:09:32] it exists [13:09:50] not dreaming it [13:10:14] it should say dynamic and not compact [13:10:38] AHH [13:10:49] introduced in 10.2.2 and 10.1.32 ( https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_default_row_format ) [13:10:57] ah, wow [13:11:09] there is a fallback [13:11:16] and I use whatever mariadb is avaibable in stretch [13:11:19] which is the mediawiki variable I told you [13:11:24] or [13:11:27] you can use our package [13:11:48] table_options or something like that [13:11:54] so even if the db is created innodb_large_prefix , there is still some additional trick needed in mediawiki right? [13:11:58] $wgTableOptions probably [13:12:04] correct [13:12:09] then add there [13:12:36] row_format=DYNAMIC [13:12:52] on utf8 installation it contains ENGINE=InnoDB, DEFAULT CHARSET=utf8 [13:13:08] check if you want to keep something of that or not, etc. [13:13:38] https://www.mediawiki.org/wiki/Manual:$wgDBTableOptions [13:14:36] sorry about the mess [13:14:49] cant it be set when the database is created? [13:15:01] no, it is or a global option [13:15:04] or a table option [13:15:15] it is not like charset, where a database default can be set [13:15:20] or collation [13:16:21] alternatively, you can use a binary collation, which will not have any of those issues [13:16:33] becuse all the units will be bytes, not characters [13:16:44] which might be good enough for my use case :D [13:17:06] it depends [13:17:18] for individual wikis, utf8 may be better [13:17:36] but aiming full internationalization support like production, binary is the only way [13:18:33] which index is failing? because maybe it could be reduced too, for extra compatibility [13:19:26] and I dont think MediaWiki installer supports passing table options at installation [13:19:54] well I had the issue on the first two extensions I tried testing. So probably there are too many failling [13:20:00] the alternative is to use latin1 :] [13:20:41] KEY (afa_consequence(100)) should solve the issue [13:21:18] clearly those extensions do not support well different db configurations [13:21:44] ah because the key is currently created on the whole field [13:21:59] afa_consequence varchar(255) + KEY(afa_consequence) [13:22:04] which might be overkill [13:22:06] so I can tell you why if fails [13:22:11] *it [13:22:16] and how to solve it [13:22:31] (either change the field or the database configuration) [13:22:39] the rest is up to you :-) [13:23:14] tweaking the index size might well be the easiest path afterall [13:23:49] I don't know how mediawiki is regarding large_index support [13:24:23] but yes, probably being more conservative would be useful for now [13:26:55] so I will look at patching the mediawiki extension [13:27:09] but still keep innodb_file_format = Barracuda and innodb_large_prefix = 1 [13:27:25] check first if there are other extensions with issues [13:27:57] yeah i will trigger a few thousands CI jobs over the week-end [13:28:01] to check them all [13:28:24] I mean, it failing it means it doesn't work on a default configuration [13:28:34] which for me it would be a bug on the code [13:28:36] not on CI [13:28:41] yup [13:28:44] honestly [13:29:02] at least on Stretch MariaDB comes with utf8mb4 by default it seems [13:29:11] the thing is probably many of those extensions [13:29:15] but CI currently runs with the Jessie version which still defaults to latin1 [13:29:19] are mostly used only for WMF [13:29:34] the ones that fail [13:29:42] and those run just on binary [13:29:56] so in the end I am hitting a bug in the code [13:29:58] \o/ [13:30:10] I think it is fair to ask all code to be compatible with utf8 [13:30:25] and all that is compatible with utf8, will be with binary [13:31:11] then we still hit a wall with things like: `feature` varchar(255) primary key not null, [13:31:15] a key on a varchar(255) [13:32:53] well, developers should agree on what they support [13:33:09] if they require large prefix [13:33:22] or don't support utf8, that should be explicit [13:33:30] let them decide [13:33:42] and make CI work based on that [13:34:50] Ok ok all of that now makes a lot more sense to me. Thank you [13:35:03] if mediawiki does not install, I think you should file a bug [13:35:22] yup I am most probably going to do that. I want to reproduce with a stock Stretch MariaDB [13:39:38] 10DBA, 10Wikimedia-Site-requests, 10Tracking: Database table cleanup (tracking) - https://phabricator.wikimedia.org/T18660#4163911 (10Marostegui) [13:39:41] 10DBA, 10Epic, 10Tracking: Database tables to be dropped on Wikimedia wikis and other WMF databases (tracking) - https://phabricator.wikimedia.org/T54921#4163915 (10Marostegui) [13:40:17] 10DBA, 10Wikimedia-Site-requests, 10Tracking: Database table cleanup (tracking) - https://phabricator.wikimedia.org/T18660#215865 (10Marostegui) I have closed this, as we are tracking all the tables on T54921 [13:41:49] marostegui: I don't think those are duplicate [13:41:54] did you see the subtasks there? [13:41:57] innodb_large_prefix is deprecated in MySQL 5.7.7 and will be removed in a future release. innodb_large_prefix was introduced in MySQL 5.5 [13:42:03] so I should forget about largeprefix [13:42:17] deprecated because it become the default [13:42:23] and mediawiki has to learn to use DYNAMIC all solved on my side [13:42:27] thank you very much jynus [13:42:35] but on earlier versions it wasn't by default [13:42:43] 10DBA, 10Wikimedia-Site-requests, 10Tracking: Database table cleanup (tracking) - https://phabricator.wikimedia.org/T18660#4163918 (10Marostegui) 05duplicate>03Open [13:43:00] marostegui: https://phabricator.wikimedia.org/T174513 [13:43:17] I would put it as a parent [13:43:30] https://phabricator.wikimedia.org/T75181 [13:44:03] it is true it could be defined a bit better [13:44:14] and send to the other task for dropping-only tasks [13:44:19] yeah, I guess. Anyways, I just reopened it [13:44:33] I don't even think we should be tagged on that ticket [13:44:42] oh, I agree [13:44:50] I just don't want to take care of those tasks :-) [13:45:23] content issues should be solved with maintenance scripts that devels run [13:45:43] Indeed ,that's why I think the DBA tag isn't too valid [13:45:56] which removing DBA is ok to me [13:47:36] just did [13:47:54] 10DBA, 10Epic, 10Tracking: Database tables to be dropped on Wikimedia wikis and other WMF databases (tracking) - https://phabricator.wikimedia.org/T54921#4163920 (10jcrespo) [13:47:58] I added it as as parent [13:48:07] as I think one subtask of cleaup [13:48:11] is drop tables [13:48:18] yeah, that makes sense [13:48:20] but there is other unrelated stuff on the parent [13:48:26] due to software bugs [13:49:25] Apparently there is also: https://phabricator.wikimedia.org/maniphest/?project=PHID-PROJ-b4pmscw4btz2gxdfh2pm&statuses=open()&group=none&order=newest#R [13:51:06] I edited https://phabricator.wikimedia.org/T18660 [13:52:12] ah nice, it is now clearer [13:52:35] probably those should all be migrated to that [13:52:43] but I am not going to do that myself [13:53:55] unrelated, I think I finally have a working mysql server like-core [13:54:08] \o/ [13:54:13] can't wait to see its replication [13:54:15] there are so many things that are similar [13:54:17] with a mariadb master [13:54:19] but different [13:54:39] like instead of unix_socket we have socket_auth [13:54:51] instead of skip-ssl, we have ssl-mode=disabled [13:54:56] and dozens more [13:55:28] I have puppetized for now the my.cnf on a separate file [13:55:50] I mention we may want a single one with conditional based on $vendor [13:56:47] yeah I agree, it is probably easier [13:56:55] or at least easier to see the differences [13:57:11] the wrapper could be nice [13:57:34] because it could dynamically use different command line options depending on the version [14:01:11] mysqlsh is not part of the server? [14:01:15] strange [14:01:49] lol [14:01:56] it has its own download [14:02:01] is it a different package? [14:02:07] is this another failed experiment of oracle? [14:02:34] https://dev.mysql.com/downloads/shell/ [14:21:47] mariadb libs fail to connect with "Error in server handshake" :-/ [14:21:55] or mysql older libs [14:22:13] I will try with other autnetication plugins/configuration [14:22:21] bufff [14:23:31] I am going to guess there will be an "enable older libs" setup [14:23:38] or [14:23:49] we could force the aps to use a newer connection lib [14:27:08] 10DBA, 10Quibble, 10Patch-For-Review, 10Release-Engineering-Team (Kanban): Error: 1071 Specified key was too long; max key length is 767 bytes - https://phabricator.wikimedia.org/T193222#4164031 (10hashar) @jcrespo took extra time and care to fully explain the situation to me over IRC. The summary of the d... [14:27:23] 10DBA, 10Quibble, 10Release-Engineering-Team (Kanban): Error: 1071 Specified key was too long; max key length is 767 bytes - https://phabricator.wikimedia.org/T193222#4164034 (10hashar) [14:34:54] It was the socket authentication [14:35:16] with the native password it works with backwards compatibility [14:35:26] jynus: so thank you very much. The summary is I will fix up the index/keys that are too long so that extensions can work properly with a default mariadb config. An example https://gerrit.wikimedia.org/r/#/c/429422/1/sql/create_counts.sql [14:35:44] jynus: really thank a ton. I learned more than a few things today! [14:35:59] I unsubscribed you and #dba from the task. Support fullfilled! [14:36:17] well, thanks to you for pushing also for stricted defaults [14:36:23] *stricter [14:36:32] (unrelated to your bug) [14:40:07] will look at adding it next week [14:40:11] it is too late to deploy it [14:40:26] another interesting thing [14:40:44] primary key (bigint, varchar_255_field(191) ) [14:40:48] that does not exceed the limit [14:41:34] I was expecting an error since a bigint is 8 + 191 * 4 = 772 which is more than 767 bytes [14:41:56] there is some weirdness [14:42:10] cannot remember the details [14:42:48] maybe for composite indexes (foo, bar) that acts diffrently behind the hood [14:43:03] who knows. The patch works and that seems ok [14:52:20] A wild 8.0 appears: https://grafana.wikimedia.org/dashboard/db/mysql?orgId=1&var-dc=eqiad%20prometheus%2Fops&var-server=db1118&var-port=9104&from=now-30m&to=now [15:21:07] tendril required table fixes due to the movement of certain tables to p_s https://tendril.wikimedia.org/host/view/db1118.eqiad.wmnet/3306 [15:24:20] 10DBA, 10Beta-Cluster-Infrastructure, 10Continuous-Integration-Infrastructure, 10MediaWiki-Database, and 2 others: Enable MariaDB/MySQL's Strict Mode - https://phabricator.wikimedia.org/T108255#4164188 (10Nikerabbit) [15:46:38] jynus: my best assumption is mysql has a limit of 3072 bytes for index length and one can combine two varchar(255) just fine (255 * 2 * 4 bytes = 2040 < 3072 ) [15:46:55] so it would have a different limit for primary key and for indices [15:47:38] or it makes no sense :D [15:47:40] whatever [15:49:55] primary keys are not really indexes for innodb [15:50:04] they are just "data" rows [15:50:28] the limit is really 16K for default page [15:50:50] (and varchars/blobs/etc. do not count) [15:54:11] ohhahh [15:56:29] beer break [15:56:47] PRIMARY KEY (afa_filter,afa_consequence(191)) doesn't make much sense to me, though [15:58:45] hashar: look first bug discovered with traditional https://phabricator.wikimedia.org/T193246 [16:16:05] jynus: I don't know either. with two varchar(255) , it accepts PRIMARY KEY (first(191), second(191) [17:02:41] 10DBA, 10MW-1.31-release-notes (WMF-deploy-2018-03-27 (1.31.0-wmf.27)), 10Patch-For-Review, 10User-notice, and 2 others: 1.31.0-wmf.27 rolled back due to increase in fatals: "Replication wait failed: lost connection to MySQL server during query" - https://phabricator.wikimedia.org/T190960#4164422 (10mmodell) [20:18:33] 10DBA, 10Beta-Cluster-Infrastructure, 10Continuous-Integration-Infrastructure, 10MediaWiki-Database, and 2 others: Enable MariaDB/MySQL's Strict Mode - https://phabricator.wikimedia.org/T108255#4164843 (10hashar) https://gerrit.wikimedia.org/r/#/c/429386/ would enable sql_mode = TRADITIONAL via includes/De... [20:37:14] 10DBA, 10Beta-Cluster-Infrastructure, 10Continuous-Integration-Infrastructure, 10MediaWiki-Database, and 2 others: Enable MariaDB/MySQL's Strict Mode - https://phabricator.wikimedia.org/T108255#4164873 (10hashar) [20:52:20] 10DBA, 10Operations, 10hardware-requests, 10ops-codfw, 10Patch-For-Review: Decommission db2011 - https://phabricator.wikimedia.org/T187886#4164880 (10RobH) [20:54:06] 10DBA: Decommission db1051-db1060 (DBA tracking) - https://phabricator.wikimedia.org/T186320#4164883 (10jcrespo) p:05Low>03Normal [20:57:16] 10DBA, 10Operations, 10hardware-requests, 10ops-codfw: Decommission db2011 - https://phabricator.wikimedia.org/T187886#4164890 (10RobH) [20:57:36] 10DBA, 10Operations, 10hardware-requests, 10ops-codfw: Decommission db2011 - https://phabricator.wikimedia.org/T187886#3989185 (10RobH) a:05RobH>03Papaul ready for onsite completion of steps