Re: [otrs] Database Conversion from MySQL to PostgreSQL
Another good option. I haven't been all that impressed with it yet, but it's improving rapidly as they clean-room reimplement some of the stuff Oracle did on the performance front. It'd just be good for Oracle to get what's coming to them. From: otrs-boun...@otrs.org [mailto:otrs-boun...@otrs.org] On Behalf Of Gerald Young Sent: Thursday, March 28, 2013 4:11 PM To: User questions and discussions about OTRS. Subject: Re: [otrs] Database Conversion from MySQL to PostgreSQL May I point out MariaDB<https://mariadb.org/> while we're talking about MySQL? Just in case you want a different MySQL that isn't encumbered by Oracle, but is a drop-in replacement for MySQL. - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Re: [otrs] Database Conversion from MySQL to PostgreSQL
According to ZDNet <https://www.google.com/search?q=zdnet+mariadb>, OpenSUSE, and Fedora are going to replace MySQL with MariaDB as part of their LAMP stack. Wikipedia, too<http://www.zdnet.com/wikipedia-moving-from-mysql-to-mariadb-708912/> , Performance, support, bug fixes at a better rate than Oracle, At least, that's what I understand. I haven't made the change because I don't have personal concerns about the licensing, but if it came to that, at least I have options. On Thu, Mar 28, 2013 at 4:18 PM, Bogdan Iosif wrote: > MariaDB very recently entered my radar, mostly because the project is > driven by MySQL's inventor. Don't know much about it beyond that. Have you > seen it used in a production environment? Is it at the same performance / > reliability level as MySQL? > > > On Thu, Mar 28, 2013 at 10:11 PM, Gerald Young wrote: > >> May I point out MariaDB <https://mariadb.org/> while we're talking about >> MySQL? Just in case you want a different MySQL that isn't encumbered by >> Oracle, but is a drop-in replacement for MySQL. >> >> >> On Thu, Mar 28, 2013 at 4:02 PM, David Boyes wrote: >> >>> Neat. Hadn’t considered using something like that for reporting. >>> >>> ** ** >>> >>> ** ** >>> >>> *From:* otrs-boun...@otrs.org [mailto:otrs-boun...@otrs.org] *On Behalf >>> Of *Bogdan Iosif >>> *Sent:* Thursday, March 28, 2013 3:51 PM >>> *To:* User questions and discussions about OTRS. >>> *Subject:* Re: [otrs] Database Conversion from MySQL to PostgreSQL >>> >>> ** ** >>> >>> >>> http://en.wikipedia.org/wiki/Window_functions >>> >>> Very useful for some queries used mostly with reports (e.g. when you >>> need that Nth occurence in ticket_history) >>> >>> ** ** >>> >>> On Thu, Mar 28, 2013 at 9:46 PM, David Boyes >>> wrote: >>> >>> >>> >>> >>> >>> I'll see your T&Cs and raise you window functions >>> >>> >>> >>> ?? window functions? Not sure I follow. >>> >>> >>> - >>> OTRS mailing list: otrs - Webpage: http://otrs.org/ >>> Archive: http://lists.otrs.org/pipermail/otrs >>> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs >>> >>> ** ** >>> >>> - >>> OTRS mailing list: otrs - Webpage: http://otrs.org/ >>> Archive: http://lists.otrs.org/pipermail/otrs >>> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs >>> >> >> >> - >> OTRS mailing list: otrs - Webpage: http://otrs.org/ >> Archive: http://lists.otrs.org/pipermail/otrs >> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs >> > > > - > OTRS mailing list: otrs - Webpage: http://otrs.org/ > Archive: http://lists.otrs.org/pipermail/otrs > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs > - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Re: [otrs] Database Conversion from MySQL to PostgreSQL
MariaDB very recently entered my radar, mostly because the project is driven by MySQL's inventor. Don't know much about it beyond that. Have you seen it used in a production environment? Is it at the same performance / reliability level as MySQL? On Thu, Mar 28, 2013 at 10:11 PM, Gerald Young wrote: > May I point out MariaDB <https://mariadb.org/> while we're talking about > MySQL? Just in case you want a different MySQL that isn't encumbered by > Oracle, but is a drop-in replacement for MySQL. > > > On Thu, Mar 28, 2013 at 4:02 PM, David Boyes wrote: > >> Neat. Hadn’t considered using something like that for reporting. >> >> ** ** >> >> ** ** >> >> *From:* otrs-boun...@otrs.org [mailto:otrs-boun...@otrs.org] *On Behalf >> Of *Bogdan Iosif >> *Sent:* Thursday, March 28, 2013 3:51 PM >> *To:* User questions and discussions about OTRS. >> *Subject:* Re: [otrs] Database Conversion from MySQL to PostgreSQL >> >> ** ** >> >> >> http://en.wikipedia.org/wiki/Window_functions >> >> Very useful for some queries used mostly with reports (e.g. when you need >> that Nth occurence in ticket_history) >> >> ** ** >> >> On Thu, Mar 28, 2013 at 9:46 PM, David Boyes >> wrote: >> >> >> >> >> >> I'll see your T&Cs and raise you window functions >> >> >> >> ?? window functions? Not sure I follow. >> >> >> - >> OTRS mailing list: otrs - Webpage: http://otrs.org/ >> Archive: http://lists.otrs.org/pipermail/otrs >> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs >> >> ** ** >> >> - >> OTRS mailing list: otrs - Webpage: http://otrs.org/ >> Archive: http://lists.otrs.org/pipermail/otrs >> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs >> > > > - > OTRS mailing list: otrs - Webpage: http://otrs.org/ > Archive: http://lists.otrs.org/pipermail/otrs > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs > - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Re: [otrs] Database Conversion from MySQL to PostgreSQL
May I point out MariaDB <https://mariadb.org/> while we're talking about MySQL? Just in case you want a different MySQL that isn't encumbered by Oracle, but is a drop-in replacement for MySQL. On Thu, Mar 28, 2013 at 4:02 PM, David Boyes wrote: > Neat. Hadn’t considered using something like that for reporting. > > ** ** > > ** ** > > *From:* otrs-boun...@otrs.org [mailto:otrs-boun...@otrs.org] *On Behalf > Of *Bogdan Iosif > *Sent:* Thursday, March 28, 2013 3:51 PM > *To:* User questions and discussions about OTRS. > *Subject:* Re: [otrs] Database Conversion from MySQL to PostgreSQL > > ** ** > > > http://en.wikipedia.org/wiki/Window_functions > > Very useful for some queries used mostly with reports (e.g. when you need > that Nth occurence in ticket_history) > > ** ** > > On Thu, Mar 28, 2013 at 9:46 PM, David Boyes > wrote: > > > > > > I'll see your T&Cs and raise you window functions > > > > ?? window functions? Not sure I follow. > > > - > OTRS mailing list: otrs - Webpage: http://otrs.org/ > Archive: http://lists.otrs.org/pipermail/otrs > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs > > ** ** > > - > OTRS mailing list: otrs - Webpage: http://otrs.org/ > Archive: http://lists.otrs.org/pipermail/otrs > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs > - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Re: [otrs] Database Conversion from MySQL to PostgreSQL
Neat. Hadn’t considered using something like that for reporting. From: otrs-boun...@otrs.org [mailto:otrs-boun...@otrs.org] On Behalf Of Bogdan Iosif Sent: Thursday, March 28, 2013 3:51 PM To: User questions and discussions about OTRS. Subject: Re: [otrs] Database Conversion from MySQL to PostgreSQL http://en.wikipedia.org/wiki/Window_functions Very useful for some queries used mostly with reports (e.g. when you need that Nth occurence in ticket_history) On Thu, Mar 28, 2013 at 9:46 PM, David Boyes mailto:dbo...@sinenomine.net>> wrote: I'll see your T&Cs and raise you window functions ?? window functions? Not sure I follow. - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Re: [otrs] Database Conversion from MySQL to PostgreSQL
Wrong link @ wikipedia but you can jump to the correct one from the top. Or use this here direct link http://en.wikipedia.org/wiki/Window_function_%28SQL%29#Window_functionSorry :) On Thu, Mar 28, 2013 at 9:51 PM, Bogdan Iosif wrote: > > http://en.wikipedia.org/wiki/Window_functions > > Very useful for some queries used mostly with reports (e.g. when you need > that Nth occurence in ticket_history) > > > On Thu, Mar 28, 2013 at 9:46 PM, David Boyes wrote: > >> ** ** >> >> ** ** >> >> I'll see your T&Cs and raise you window functions >> >> ** ** >> >> ?? window functions? Not sure I follow. >> >> - >> OTRS mailing list: otrs - Webpage: http://otrs.org/ >> Archive: http://lists.otrs.org/pipermail/otrs >> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs >> > > - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Re: [otrs] Database Conversion from MySQL to PostgreSQL
http://en.wikipedia.org/wiki/Window_functions Very useful for some queries used mostly with reports (e.g. when you need that Nth occurence in ticket_history) On Thu, Mar 28, 2013 at 9:46 PM, David Boyes wrote: > ** ** > > ** ** > > I'll see your T&Cs and raise you window functions > > ** ** > > ?? window functions? Not sure I follow. > > - > OTRS mailing list: otrs - Webpage: http://otrs.org/ > Archive: http://lists.otrs.org/pipermail/otrs > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs > - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Re: [otrs] Database Conversion from MySQL to PostgreSQL
I'll see your T&Cs and raise you window functions ?? window functions? Not sure I follow. - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Re: [otrs] Database Conversion from MySQL to PostgreSQL
I'll see your T&Cs and raise you window functions On Thu, Mar 28, 2013 at 9:22 PM, David Boyes wrote: > ** ** > > ** ** > > Did you run tests that showed PostgreSQL would be better performance-wise > or, if not, why did you move? > > ** ** > > Oracle’s new licensing T&Cs for use of MySQL for business tasks in one big > reason. The lawyers here think the new language implies that using MySQL > with other applications that have a business purpose now requires a MySQL > commercial license, which is . > > - > OTRS mailing list: otrs - Webpage: http://otrs.org/ > Archive: http://lists.otrs.org/pipermail/otrs > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs > - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Re: [otrs] Database Conversion from MySQL to PostgreSQL
Did you run tests that showed PostgreSQL would be better performance-wise or, if not, why did you move? Oracle’s new licensing T&Cs for use of MySQL for business tasks in one big reason. The lawyers here think the new language implies that using MySQL with other applications that have a business purpose now requires a MySQL commercial license, which is . - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Re: [otrs] Database Conversion from MySQL to PostgreSQL
It seems faster, but we've made no performance tests at all. We moved fo the sake of simplicity (keeping just one RDBMS to maintain). On Thu, Mar 28, 2013 at 2:12 PM, Bogdan Iosif wrote: > An interesting and ingenious method. I was thinking you're using some ETL > tool. > > I'm strongly considering doing the same switch as you did but I'm wary of > performance issues. Initially, I tried running OTRS on top of SQL Server but > Perl's database interface implementation for this RDBMS is very weak (5x > slower) compared to MySQL. > > Did you run tests that showed PostgreSQL would be better performance-wise > or, if not, why did you move? > > > On Thu, Mar 28, 2013 at 6:55 PM, aledr wrote: >> >> Used mysqldump for data only; >> Added 'set backslash_quote = on;' and 'set standard_conforming_strings >> = off;' in the header of the dump file. >> After that you need to create the PostgreSQL schema from OTRS scripts, >> import your data, run OTRS post database script and adjust sequences >> (you can generate the queries for that with py-mysql2pgsql and some >> scripting). >> >> Solved the article_attachment with otrs.ArticleStorageSwitch.pl. >> Dropped files to filesystem, moved files to the new server and >> imported to database again. >> >> Thanks. >> >> On Thu, Mar 28, 2013 at 12:18 PM, Bogdan Iosif >> wrote: >> > Does OTRS have a tool that you used to move the data? >> > >> > >> > On Thu, Mar 28, 2013 at 5:12 PM, aledr >> > wrote: >> >> >> >> Good day! >> >> >> >> I'm moving my otrs installation from MySQL to PostgreSQL. >> >> Almost everything went fine, except article_attachment table. >> >> >> >> Is there any available instructions on how to move that data? >> >> >> >> Thanks. >> >> -- >> >> [ ]'s >> >> Aledr - Alexandre >> >> - >> >> OTRS mailing list: otrs - Webpage: http://otrs.org/ >> >> Archive: http://lists.otrs.org/pipermail/otrs >> >> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs >> > >> > >> > >> > - >> > OTRS mailing list: otrs - Webpage: http://otrs.org/ >> > Archive: http://lists.otrs.org/pipermail/otrs >> > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs >> -- >> [ ]'s >> Aledr - Alexandre >> - >> OTRS mailing list: otrs - Webpage: http://otrs.org/ >> Archive: http://lists.otrs.org/pipermail/otrs >> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs > > > > - > OTRS mailing list: otrs - Webpage: http://otrs.org/ > Archive: http://lists.otrs.org/pipermail/otrs > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs -- [ ]'s Aledr - Alexandre - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Re: [otrs] Database Conversion from MySQL to PostgreSQL
An interesting and ingenious method. I was thinking you're using some ETL tool. I'm strongly considering doing the same switch as you did but I'm wary of performance issues. Initially, I tried running OTRS on top of SQL Server but Perl's database interface implementation for this RDBMS is very weak (5x slower) compared to MySQL. Did you run tests that showed PostgreSQL would be better performance-wise or, if not, why did you move? On Thu, Mar 28, 2013 at 6:55 PM, aledr wrote: > Used mysqldump for data only; > Added 'set backslash_quote = on;' and 'set standard_conforming_strings > = off;' in the header of the dump file. > After that you need to create the PostgreSQL schema from OTRS scripts, > import your data, run OTRS post database script and adjust sequences > (you can generate the queries for that with py-mysql2pgsql and some > scripting). > > Solved the article_attachment with otrs.ArticleStorageSwitch.pl. > Dropped files to filesystem, moved files to the new server and > imported to database again. > > Thanks. > > On Thu, Mar 28, 2013 at 12:18 PM, Bogdan Iosif > wrote: > > Does OTRS have a tool that you used to move the data? > > > > > > On Thu, Mar 28, 2013 at 5:12 PM, aledr > wrote: > >> > >> Good day! > >> > >> I'm moving my otrs installation from MySQL to PostgreSQL. > >> Almost everything went fine, except article_attachment table. > >> > >> Is there any available instructions on how to move that data? > >> > >> Thanks. > >> -- > >> [ ]'s > >> Aledr - Alexandre > >> - > >> OTRS mailing list: otrs - Webpage: http://otrs.org/ > >> Archive: http://lists.otrs.org/pipermail/otrs > >> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs > > > > > > > > - > > OTRS mailing list: otrs - Webpage: http://otrs.org/ > > Archive: http://lists.otrs.org/pipermail/otrs > > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs > -- > [ ]'s > Aledr - Alexandre > - > OTRS mailing list: otrs - Webpage: http://otrs.org/ > Archive: http://lists.otrs.org/pipermail/otrs > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs > - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Re: [otrs] Database Conversion from MySQL to PostgreSQL
Used mysqldump for data only; Added 'set backslash_quote = on;' and 'set standard_conforming_strings = off;' in the header of the dump file. After that you need to create the PostgreSQL schema from OTRS scripts, import your data, run OTRS post database script and adjust sequences (you can generate the queries for that with py-mysql2pgsql and some scripting). Solved the article_attachment with otrs.ArticleStorageSwitch.pl. Dropped files to filesystem, moved files to the new server and imported to database again. Thanks. On Thu, Mar 28, 2013 at 12:18 PM, Bogdan Iosif wrote: > Does OTRS have a tool that you used to move the data? > > > On Thu, Mar 28, 2013 at 5:12 PM, aledr wrote: >> >> Good day! >> >> I'm moving my otrs installation from MySQL to PostgreSQL. >> Almost everything went fine, except article_attachment table. >> >> Is there any available instructions on how to move that data? >> >> Thanks. >> -- >> [ ]'s >> Aledr - Alexandre >> - >> OTRS mailing list: otrs - Webpage: http://otrs.org/ >> Archive: http://lists.otrs.org/pipermail/otrs >> To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs > > > > - > OTRS mailing list: otrs - Webpage: http://otrs.org/ > Archive: http://lists.otrs.org/pipermail/otrs > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs -- [ ]'s Aledr - Alexandre - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
Re: [otrs] Database Conversion from MySQL to PostgreSQL
Does OTRS have a tool that you used to move the data? On Thu, Mar 28, 2013 at 5:12 PM, aledr wrote: > Good day! > > I'm moving my otrs installation from MySQL to PostgreSQL. > Almost everything went fine, except article_attachment table. > > Is there any available instructions on how to move that data? > > Thanks. > -- > [ ]'s > Aledr - Alexandre > - > OTRS mailing list: otrs - Webpage: http://otrs.org/ > Archive: http://lists.otrs.org/pipermail/otrs > To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs > - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
[otrs] Database Conversion from MySQL to PostgreSQL
Good day! I'm moving my otrs installation from MySQL to PostgreSQL. Almost everything went fine, except article_attachment table. Is there any available instructions on how to move that data? Thanks. -- [ ]'s Aledr - Alexandre - OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
