How can we improve the support for databases like PostgreSQL, Oracle, DB2 and MS SQL?
Getting Jenkins involved in testing isn't the (only) answer, though it would certainly help.
If developers who were interested in those databases could watch includes/db, that would help, as well.
If nothing else, I will make an effort to get the developers for those databases involved in RC testing and make the RC available a month before release.
I would be happy to install some mediawiki on oracle db, but I have no oracle db on any of my personal servers :/
The main problem of oracle is that it's not very much free - thus it's not packaged by most of linux vendors and it might be hard to install for many sysadmins. (In order to install oracle, you don't just need a system that meets all the technical parameters, but also good understanding of response files used by their universal installer).
From my experience, installing oracle db on debian or ubuntu is pretty
complicated (installation on oracle linux and such is not that hard though - it comes with exact version of packages that oracle requires).
However - it's not a freeware and that automatically makes it very unpopular for open source OS vendors and very unlikely a choice of standard webadmin and that makes it hard to test. Even if I was able to obtain some license to install oracle on any of my servers, I wouldn't do it as it eats too much resources. On other hand if oracle granted some license to wikimedia, we could set up a project on wikimedia labs for this purpose.
On Mon, Feb 25, 2013 at 4:27 PM, Mark A. Hershberger mah@everybody.org wrote:
How can we improve the support for databases like PostgreSQL, Oracle, DB2 and MS SQL?
Getting Jenkins involved in testing isn't the (only) answer, though it would certainly help.
If developers who were interested in those databases could watch includes/db, that would help, as well.
If nothing else, I will make an effort to get the developers for those databases involved in RC testing and make the RC available a month before release.
There is no path to peace. Peace is the path. -- Mahatma Gandhi, "Non-Violence in Peace and War"
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
However - it's not a freeware and that automatically makes it very unpopular for open source OS vendors and very unlikely a choice of standard webadmin and that makes it hard to test. Even if I was able to obtain some license to install oracle on any of my servers, I wouldn't do it as it eats too much resources. On other hand if oracle granted some license to wikimedia, we could set up a project on wikimedia labs for this purpose.
Doesnt oracle offer a free "express" eddition?
-bawolff
On Mon, Feb 25, 2013 at 9:27 AM, Mark A. Hershberger mah@everybody.org wrote:
How can we improve the support for databases like PostgreSQL, Oracle, DB2 and MS SQL?
The main issues arise from not keeping these other DBs in mind when writing the queries (and also not using the db layer).
Things like quoting timestamps, more strict adherence to the SQL spec(s), stricter data type checking, etc.
On Mon, Feb 25, 2013 at 7:27 AM, Mark A. Hershberger mah@everybody.org wrote:
Getting Jenkins involved in testing isn't the (only) answer, though it would certainly help.
If developers who were interested in those databases could watch includes/db, that would help, as well.
The latter is the real problem here. We don't have any people who are dedicated to supporting these. People show up, say they want to work on supporting these, then disappear.
Covering all the non-mysql/sqlite we "support": - DB2 has been unmaintained for ages, and personally I'm in favor of dropping that one altogether. - MSSQL would be nice to improve. - Oracle support's not bad (maybe not perfect), freakolowsy would know more. - Postgres support needs major work. There's a lot of inconsistencies between PG and the other backends (especially for install/upgrade). There *are* people here who care about PG.
-Chad
On 02/25/2013 10:40 AM, Chad wrote:
Covering all the non-mysql/sqlite we "support":
- DB2 has been unmaintained for ages, and personally I'm in favor
of dropping that one altogether.
- MSSQL would be nice to improve.
- Oracle support's not bad (maybe not perfect), freakolowsy would
know more.
- Postgres support needs major work. There's a lot of inconsistencies
between PG and the other backends (especially for install/upgrade). There *are* people here who care about PG.
This pretty much mirrors my thinking as well.
Microsoft has been very helpful in the past year by funding some of the Azure support and MSSQL seems like a natural fit there. I think we could get an instance on Azure to help test if we could fit that into Jenkins somehow.
Other than that, I can only add my +1 to dropping DB2 support. I don't know of it being used anywhere.
On Mon, Feb 25, 2013 at 7:48 AM, Mark A. Hershberger mah@everybody.org wrote:
Other than that, I can only add my +1 to dropping DB2 support. I don't know of it being used anywhere.
https://gerrit.wikimedia.org/r/#/c/50764/
-Chad
I'm still alive and willing to continue to work on MSSQL.
On Mon, Feb 25, 2013 at 9:40 AM, Chad innocentkiller@gmail.com wrote:
On Mon, Feb 25, 2013 at 7:27 AM, Mark A. Hershberger mah@everybody.org wrote:
Getting Jenkins involved in testing isn't the (only) answer, though it would certainly help.
If developers who were interested in those databases could watch includes/db, that would help, as well.
The latter is the real problem here. We don't have any people who are dedicated to supporting these. People show up, say they want to work on supporting these, then disappear.
Covering all the non-mysql/sqlite we "support":
- DB2 has been unmaintained for ages, and personally I'm in favor
of dropping that one altogether.
- MSSQL would be nice to improve.
- Oracle support's not bad (maybe not perfect), freakolowsy would
know more.
- Postgres support needs major work. There's a lot of inconsistencies
between PG and the other backends (especially for install/upgrade). There *are* people here who care about PG.
-Chad
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
On 02/25/2013 10:52 AM, Danny Joe Bauch wrote:
I'm still alive and willing to continue to work on MSSQL.
Have you tried to run MediaWiki's latest from git against MSSQL?
No. The last version I got running was 1.19, and have not worked on any since. I could do as you suggest, though.
On Mon, Feb 25, 2013 at 9:54 AM, Mark A. Hershberger mah@everybody.orgwrote:
On 02/25/2013 10:52 AM, Danny Joe Bauch wrote:
I'm still alive and willing to continue to work on MSSQL.
Have you tried to run MediaWiki's latest from git against MSSQL?
There is no path to peace. Peace is the path. -- Mahatma Gandhi, "Non-Violence in Peace and War"
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
It would be best to have automated environment for this, either on labs or somewhere else. Problem is that it's not possible to install MSSQL and such on wikimedia labs given the restrictions
On Mon, Feb 25, 2013 at 5:36 PM, Danny Joe Bauch dj.bauch@gmail.com wrote:
No. The last version I got running was 1.19, and have not worked on any since. I could do as you suggest, though.
On Mon, Feb 25, 2013 at 9:54 AM, Mark A. Hershberger mah@everybody.orgwrote:
On 02/25/2013 10:52 AM, Danny Joe Bauch wrote:
I'm still alive and willing to continue to work on MSSQL.
Have you tried to run MediaWiki's latest from git against MSSQL?
There is no path to peace. Peace is the path. -- Mahatma Gandhi, "Non-Violence in Peace and War"
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
On 02/25/2013 11:43 AM, Petr Bena wrote:
It would be best to have automated environment for this, either on labs or somewhere else. Problem is that it's not possible to install MSSQL and such on wikimedia labs given the restrictions
Which is why I suggested that we work with someone at MS to get an instance on Azure for testing.
Microsoft was very kind to provide me with an Azure account which I used to do the testing of 1.18 and 1.19. I've not been in touch with them lately, nor have I checked whether that account is still open. I would gladly jump back on that. I'm afraid I got a bit distracted when my job disappeared out from under me, but I'm back at work at a new job and eager to jump back in on MSSQL and Azure support.
On Mon, Feb 25, 2013 at 11:04 AM, Mark A. Hershberger mah@everybody.orgwrote:
On 02/25/2013 11:43 AM, Petr Bena wrote:
It would be best to have automated environment for this, either on labs or somewhere else. Problem is that it's not possible to install MSSQL and such on wikimedia labs given the restrictions
Which is why I suggested that we work with someone at MS to get an instance on Azure for testing.
There is no path to peace. Peace is the path. -- Mahatma Gandhi, "Non-Violence in Peace and War"
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
There are some bugs that also prevents accurate unit testing on multiple backends (these are the ones I hit personally):
* *Bug 37702* https://bugzilla.wikimedia.org/show_bug.cgi?id=37702 - Cloned tables for unittests do not have references and constraints * *Bug 44790* https://bugzilla.wikimedia.org/show_bug.cgi?id=44790 - Sqlite category table contains duplicates in unit tests
On Mon, Feb 25, 2013 at 12:04 PM, Mark A. Hershberger mah@everybody.orgwrote:
On 02/25/2013 11:43 AM, Petr Bena wrote:
It would be best to have automated environment for this, either on labs or somewhere else. Problem is that it's not possible to install MSSQL and such on wikimedia labs given the restrictions
Which is why I suggested that we work with someone at MS to get an instance on Azure for testing.
There is no path to peace. Peace is the path. -- Mahatma Gandhi, "Non-Violence in Peace and War"
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
On 02/25/2013 07:40 AM, Chad wrote:
On Mon, Feb 25, 2013 at 7:27 AM, Mark A. Hershberger mah@everybody.org wrote:
Getting Jenkins involved in testing isn't the (only) answer, though it would certainly help.
If developers who were interested in those databases could watch includes/db, that would help, as well.
The latter is the real problem here. We don't have any people who are dedicated to supporting these. People show up, say they want to work on supporting these, then disappear.
Covering all the non-mysql/sqlite we "support":
- DB2 has been unmaintained for ages, and personally I'm in favor
of dropping that one altogether.
- MSSQL would be nice to improve.
- Oracle support's not bad (maybe not perfect), freakolowsy would
know more.
- Postgres support needs major work. There's a lot of inconsistencies
between PG and the other backends (especially for install/upgrade). There *are* people here who care about PG.
-Chad
Yeah. Just wanted to point people to prior roundups on this topic -- see http://thread.gmane.org/gmane.science.linguistics.wikipedia.technical/56384 & https://www.mediawiki.org/wiki/Bug_management/Triage/Databases_20111102 for some people Mark could reach out to to ask for testing & development help, and for this summary of what needed doing as of November 2011:
How you can help MediaWiki administrators who don't use MySQL:
- Write tests or specs. Ben and DJ Bauch want to work on better unit
testing per https://bugzilla.wikimedia.org/show_bug.cgi?id=32118 (special page SQL queries), and could use specifications to test against. Improve https://www.mediawiki.org/wiki/Database_testing .
The question: what do we need to test more often to keep RDBMSes happy? https://www.mediawiki.org/wiki/Database_testing & https://www.mediawiki.org/wiki/New_installer/Test_plan Permissions, searching, schema setup, schema changes, quoting & identifiers, weird page names, transactions, import, dump, & interface coverage seem like the main culprits.
And I think we can mostly agree that MySQL/MariaDB (InnoDB & secondarily MyISAM), SQLite, and PostgreSQL are higher priority than SQL Server/SQL Server Express, Oracle, and DB2, although others might volunteer as maintainers and get some of that switched around. :-)
- Try to reproduce this installation failure on SQLite or PostgreSQL:
Now fixed.
- Fix "Database layer should automagically add GROUP BY columns on
backends that need them (postgres)" https://bugzilla.wikimedia.org/show_bug.cgi?id=26273 . This is a large project.
Still needs fixing.
- Make a meta-schema so that we no longer use tables.sql as a canonical
source. Chad and Max started in http://svn.wikimedia.org/viewvc/mediawiki/branches/abstract-schema/ . See https://www.mediawiki.org/wiki/Bug_management/Triage/Databases_20111102#Idea...: for more discussion. This is a large project.
Where's this branch now, if it's still useful?
Thanks for working on this, Mark!
On Mon, Feb 25, 2013 at 9:53 AM, Sumana Harihareswara sumanah@wikimedia.org wrote:
- Make a meta-schema so that we no longer use tables.sql as a canonical
source. Chad and Max started in http://svn.wikimedia.org/viewvc/mediawiki/branches/abstract-schema/ . See https://www.mediawiki.org/wiki/Bug_management/Triage/Databases_20111102#Idea...: for more discussion. This is a large project.
Where's this branch now, if it's still useful?
Still in SVN, we never migrated it. It worked as far as installing, but we never got to the upgrade point. Worth dusting off, but no way it'll merge cleanly right now.
-Chad
On 02/25/2013 12:53 PM, Sumana Harihareswara wrote:
The question: what do we need to test more often to keep RDBMSes happy? https://www.mediawiki.org/wiki/Database_testing & https://www.mediawiki.org/wiki/New_installer/Test_plan Permissions, searching, schema setup, schema changes, quoting & identifiers, weird page names, transactions, import, dump, & interface coverage seem like the main culprits.
Sure, for starters. :) Bear in mind, if we want to keep support for all these dbs, every change to the database schema has to (at some point) result in a change to separate SQL files for each DB (MySQL and SQLite use the same ones). For instance, there is a separate active oracle/tables.sql.
Then, we need to make sure it actually works as intended for all the dbs. We do have database tests. As people noted, just having Jenkins run all the existing tests for every db would be a major step forward. We *only* run them for SQLite (not even MySQL!).
See https://bugzilla.wikimedia.org/show_bug.cgi?id=35912
Matt Flaschen
On 26/02/13 04:18, Matthew Flaschen wrote:
Sure, for starters. :) Bear in mind, if we want to keep support for all these dbs, every change to the database schema has to (at some point) result in a change to separate SQL files for each DB (MySQL and SQLite use the same ones). For instance, there is a separate active oracle/tables.sql.
I am wondering if it would make sense to give up on SQL, make universal table creation functions in PHP, the same way there are for other queries, and use that. Has anyone tried this before, is there other software that works like this?
26 Февраль 2013 г. 14:27:06 пользователь Nikola Smolenski (smolensk@eunet.rs) написал:
On 26/02/13 04:18, Matthew Flaschen wrote:
Sure, for starters. :) Bear in mind, if we want to keep support for all these dbs, every change to the database schema has to (at some point) result in a change to separate SQL files for each DB (MySQL and SQLite use the same ones). For instance, there is a separate active oracle/tables.sql.
I am wondering if it would make sense to give up on SQL, make universal table creation functions in PHP, the same way there are for other queries, and use that. Has anyone tried this before, is there other software that works like this?
http://stackoverflow.com/questions/108699/good-php-orm-library
By the way, MySQL 5.6 is out and it supports fulltext search indexation for InnoDB tables. They also promise better peformance on large hardware. Still cannot find 12.04 ppa for 5.6.10, though and do not want to go troubles installing from source (although I installed MySQL from source some years ago).. Why going another database engines besides MySQL / MariaDB? Dmitriy
Do we have an official position on cross database compatibility?
Some of the MediaWiki SQL is in separate files and can be easily directed at a specific database engine. A lot of it though is scattered as fragments though other code and is going to be run on any engine we connect to.
Specifically, do we use MySQL specific syntax that is more efficient (but breaks elsewhere) or do we attempt to write lowest common denominator SQL that will run more places, but not run as efficiently on our primary target?
The only SQL coding standard doc I have seen treats Database and MySQL as synonyms: http://www.mediawiki.org/wiki/Manual:Coding_conventions/Database
Luke Welling
On Tue, Feb 26, 2013 at 5:37 AM, Dmitriy Sintsov questpc@rambler.ru wrote:
26 Февраль 2013 г. 14:27:06 пользователь Nikola Smolenski (
smolensk@eunet.rs) написал:
On 26/02/13 04:18, Matthew Flaschen wrote:
Sure, for starters. :) Bear in mind, if we want to keep support for all these dbs, every change to the database schema has to (at some point) result in a change to separate SQL files for each DB (MySQL and SQLite use the same ones). For instance, there is a separate active oracle/tables.sql.
I am wondering if it would make sense to give up on SQL, make universal table creation functions in PHP, the same way there are for other queries, and use that. Has anyone tried this before, is there other software that works like this?
http://stackoverflow.com/**questions/108699/good-php-orm-**libraryhttp://stackoverflow.com/questions/108699/good-php-orm-library
By the way, MySQL 5.6 is out and it supports fulltext search indexation for InnoDB tables. They also promise better peformance on large hardware. Still cannot find 12.04 ppa for 5.6.10, though and do not want to go troubles installing from source (although I installed MySQL from source some years ago).. Why going another database engines besides MySQL / MariaDB? Dmitriy
______________________________**_________________ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/**mailman/listinfo/wikitech-lhttps://lists.wikimedia.org/mailman/listinfo/wikitech-l
On 02/26/2013 02:14 PM, Luke Welling WMF wrote:
Do we have an official position on cross database compatibility?
It would be nice if we did. In my own production environments, I always use postgres. To date, mw has been "good enough" with its support that I've never had significant integration problems, but looking forward is always a little worrying.
-- Marc
On 02/26/2013 02:14 PM, Luke Welling WMF wrote:
Specifically, do we use MySQL specific syntax that is more efficient (but breaks elsewhere) or do we attempt to write lowest common denominator SQL that will run more places, but not run as efficiently on our primary target?
There's a DB layer for runtime operations (select, update, insert, etc.), and my understanding is that's supposed to be used for pretty much everything.
However, part of the optimization is choosing indices, which as you noted is db-specific (part of tables.sql)
Matt Flaschen
I'm still active on Oracle front, but as you might have noticed i have been [WARNING ... understatement ahead] a bit busy in the last 1,5 year so i just can't manage to pay as much attention to what's going on as i would like to ...
I used to get paid by my company to do this, but due to some budget cuts and some other projects that keep me busy at the office and over the weekends i'm now maintaining this in the little i have left of my own spare time. I'm still trying to stay up to date, but i just can't manage to stay involved in the actual development and the comunity as i used to, but if anyone needs my help with testing and fixing bugs in my department, my mailbox is always open. Because of this lack of time on my part i can't keep track of all the changes being made and i would appretiate it if somone could drop me a line if there are oracle specific issues ... as i'm doing a lot of DBA work i've got access to Oracle installations of all shapes and sizes, so i can test on almost any Oracle version.
As to the current state ... in the last 6 months most of the changes to the code just was keeping up to date with schema changes ... no major issues so i'm quite satisfied with the current state, i still wouldn't suggest it to anyone who would expect it to just plug and play, but if the admin has any DBA-fu moves up his sleeve it performs nicely. I'm planning get back into the game and do some development in a month or so (when i finish one of my other projects) to bring some of the extensions i've developed for my company and my clients into git and update my farms to a more recent version (i'm still running those farms on 1.17) ...
... so ... yeah ... i'm still alive and kicking here ... glad to see ppl still remember i exist :D
LP, Jure
On 25. 02. 2013 16:40, Chad wrote:
On Mon, Feb 25, 2013 at 7:27 AM, Mark A. Hershberger mah@everybody.org wrote:
Getting Jenkins involved in testing isn't the (only) answer, though it would certainly help.
If developers who were interested in those databases could watch includes/db, that would help, as well.
The latter is the real problem here. We don't have any people who are dedicated to supporting these. People show up, say they want to work on supporting these, then disappear.
Covering all the non-mysql/sqlite we "support":
- DB2 has been unmaintained for ages, and personally I'm in favor
of dropping that one altogether.
- MSSQL would be nice to improve.
- Oracle support's not bad (maybe not perfect), freakolowsy would
know more.
- Postgres support needs major work. There's a lot of inconsistencies
between PG and the other backends (especially for install/upgrade). There *are* people here who care about PG.
-Chad
Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
wikitech-l@lists.wikimedia.org