Tool for shifting tables from Mysql to Postgresql
Dear all, I want to convert some tables from Mysql database to Postgresql Database in Linux Systems ( Ubuntu-10.4, CentOS ). Can someone Please tell me tool for it that makes it easier. I am able to done it through FW tools in Windows System but i want to achieve it in Linux ( CentOS ) System. I researched a lot tried below steps : 1. mysqldump --compatible=postgresql wiki20100130 /hdd4-1/wiki20100130_mysql108feb22.sql 2. sed s/\\\'/\'\'/g wiki20100130_mysql108feb22.sql 3. bin/psql -Upostgres wiki20100130 /hdd4-1/wiki20100130_mysql108feb22.sql invalid byte sequence for encoding UTF8: 0xe3ba27 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. ERROR: invalid byte sequence for encoding UTF8: 0xee6c65 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. ERROR: invalid byte sequence I think a tool would ease that work. Thanks best Regards, Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Tool for shifting tables from Mysql to Postgresql
2011/3/1 Adarsh Sharma adarsh.sha...@orkash.com: Dear all, I want to convert some tables from Mysql database to Postgresql Database in Linux Systems ( Ubuntu-10.4, CentOS ). [...] invalid byte sequence for encoding UTF8: 0xe3ba27 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. ERROR: invalid byte sequence for encoding UTF8: 0xee6c65 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. ERROR: invalid byte sequence This is not a MySQL-specific error. You are using a character set encoding as if it were another. Please, check documentation for the options to export and import database dumps such as: http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_default-character-set -- Jaime Crespo MySQL Java Instructor Software Developer Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Tool for shifting tables from Mysql to Postgresql
By default Postgresql database encoding in UTF8. It seems to me by seeing error that database encoding in mysql is different from it. -- Cheers, Dhaval Jaiswal On 01/03/2011 3:57 PM, Adarsh Sharma wrote: Dear all, I want to convert some tables from Mysql database to Postgresql Database in Linux Systems ( Ubuntu-10.4, CentOS ). Can someone Please tell me tool for it that makes it easier. I am able to done it through FW tools in Windows System but i want to achieve it in Linux ( CentOS ) System. I researched a lot tried below steps : 1. mysqldump --compatible=postgresql wiki20100130 /hdd4-1/wiki20100130_mysql108feb22.sql 2. sed s/\\\'/\'\'/g wiki20100130_mysql108feb22.sql 3. bin/psql -Upostgres wiki20100130 /hdd4-1/wiki20100130_mysql108feb22.sql invalid byte sequence for encoding UTF8: 0xe3ba27 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. ERROR: invalid byte sequence for encoding UTF8: 0xee6c65 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. ERROR: invalid byte sequence I think a tool would ease that work. Thanks best Regards, Adarsh Sharma font Face='Arial' style='font-size:9pt'This e-mail, and any attachments are strictly confidential and may also contain legally privileged information. It is intended for the addressee(s) only. If you are not the intended recipient, please do not print, copy, store or act in reliance on the e-mail or any of its attachments. Instead, please notify the sender immediately and then delete the e-mail and any attachments. Unless expressly stated to the contrary, the views expressed in this e-mail are not necessarily the views of Enzen Global Solutions (P) Limited or any of its subsidiaries or affiliates (Group Companies), and the Group Companies, their directors, officers and employees makes no representation and accept no liability for the accuracy or completeness of this e-mail. You are responsible for maintaining your own virus protection and the Group Companies do not accept any liability for viruses. Enzen reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Enzen e-mail system./font -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Newbie question: Association table and Foreign Key
Hey guys, Am a newbie here and need a little help. Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Can I get some help. Thanks a million! Regards, Suren
Re: Newbie question: Association table and Foreign Key
Just curious as it is not mentioned. Can Category ID also have multiple event id ? -- Cheers Dhaval Jaiswal On 01/03/2011 5:53 PM, Wagyu Beef wrote: Hey guys, Am a newbie here and need a little help. Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Can I get some help. Thanks a million! Regards, Suren font Face='Arial' style='font-size:9pt'This e-mail, and any attachments are strictly confidential and may also contain legally privileged information. It is intended for the addressee(s) only. If you are not the intended recipient, please do not print, copy, store or act in reliance on the e-mail or any of its attachments. Instead, please notify the sender immediately and then delete the e-mail and any attachments. Unless expressly stated to the contrary, the views expressed in this e-mail are not necessarily the views of Enzen Global Solutions (P) Limited or any of its subsidiaries or affiliates (Group Companies), and the Group Companies, their directors, officers and employees makes no representation and accept no liability for the accuracy or completeness of this e-mail. You are responsible for maintaining your own virus protection and the Group Companies do not accept any liability for viruses. Enzen reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Enzen e-mail system./font -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie question: Association table and Foreign Key
Oh yeah, forgot to mention that. Yes, one event will have multiple categories. And one category can be applicable to multiple events. On Tue, Mar 1, 2011 at 8:33 PM, Dhaval Jaiswal jaiswal.dha...@enzenglobal.com wrote: Just curious as it is not mentioned. Can Category ID also have multiple event id ? -- Cheers Dhaval Jaiswal On 01/03/2011 5:53 PM, Wagyu Beef wrote: Hey guys, Am a newbie here and need a little help. Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Can I get some help. Thanks a million! Regards, Suren font Face='Arial' style='font-size:9pt'This e-mail, and any attachments are strictly confidential and may also contain legally privileged information. It is intended for the addressee(s) only. If you are not the intended recipient, please do not print, copy, store or act in reliance on the e-mail or any of its attachments. Instead, please notify the sender immediately and then delete the e-mail and any attachments. Unless expressly stated to the contrary, the views expressed in this e-mail are not necessarily the views of Enzen Global Solutions (P) Limited or any of its subsidiaries or affiliates (Group Companies), and the Group Companies, their directors, officers and employees makes no representation and accept no liability for the accuracy or completeness of this e-mail. You are responsible for maintaining your own virus protection and the Group Companies do not accept any liability for viruses. Enzen reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Enzen e-mail system./font
Convert unix time to 12:00 AM of that same day
I'd asked before how to convert a unix timestamp to the hour that it is in (and got the perfect answer) : 1298999201 = 3/1/2011 11:06:41 AM (1298999201 - (1298999201 % 3600)) = 3/1/2011 11:00:00 AM Now getting the timestamp converted to midnight of that same day isn't as simple as: 1298999201 - (1298999201 % 85400) That just gives me a unix time from yesterday... How can I convert 1298999201 (3/1/2011 11:06:41 AM) to 1298959200 (3/1/2011 12:00:00 AM)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Convert unix time to 12:00 AM of that same day
You can start by using 60*60*24=86400 ;) On Mar 1, 2011 6:17 PM, Bryan Cantwell bcantw...@firescope.com wrote: I'd asked before how to convert a unix timestamp to the hour that it is in (and got the perfect answer) : 1298999201 = 3/1/2011 11:06:41 AM (1298999201 - (1298999201 % 3600)) = 3/1/2011 11:00:00 AM Now getting the timestamp converted to midnight of that same day isn't as simple as: 1298999201 - (1298999201 % 85400) That just gives me a unix time from yesterday... How can I convert 1298999201 (3/1/2011 11:06:41 AM) to 1298959200 (3/1/2011 12:00:00 AM)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: Convert unix time to 12:00 AM of that same day
It was of course a typo, and even with the correct number isn't the answer On 03/01/2011 11:47 AM, Claudio Nanni wrote: You can start by using 60*60*24=86400 ;) On Mar 1, 2011 6:17 PM, Bryan Cantwell bcantw...@firescope.com mailto:bcantw...@firescope.com wrote: I'd asked before how to convert a unix timestamp to the hour that it is in (and got the perfect answer) : 1298999201 = 3/1/2011 11:06:41 AM (1298999201 - (1298999201 % 3600)) = 3/1/2011 11:00:00 AM Now getting the timestamp converted to midnight of that same day isn't as simple as: 1298999201 - (1298999201 % 85400) That just gives me a unix time from yesterday... How can I convert 1298999201 (3/1/2011 11:06:41 AM) to 1298959200 (3/1/2011 12:00:00 AM)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: Convert unix time to 12:00 AM of that same day
You could use: CONCAT(DATE_FORMAT(FROM_UNIXTIME(1298999201),'%Y-%m-%d'),' 12:00:00') JW On Tue, Mar 1, 2011 at 11:58 AM, Bryan Cantwell bcantw...@firescope.comwrote: It was of course a typo, and even with the correct number isn't the answer On 03/01/2011 11:47 AM, Claudio Nanni wrote: You can start by using 60*60*24=86400 ;) On Mar 1, 2011 6:17 PM, Bryan Cantwell bcantw...@firescope.commailto: bcantw...@firescope.com wrote: I'd asked before how to convert a unix timestamp to the hour that it is in (and got the perfect answer) : 1298999201 = 3/1/2011 11:06:41 AM (1298999201 - (1298999201 % 3600)) = 3/1/2011 11:00:00 AM Now getting the timestamp converted to midnight of that same day isn't as simple as: 1298999201 - (1298999201 % 85400) That just gives me a unix time from yesterday... How can I convert 1298999201 (3/1/2011 11:06:41 AM) to 1298959200 (3/1/2011 12:00:00 AM)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: versions 5.1, 5.5
From the command line there was no way to run mysql.exe I may be dwelling on the obvious, but have you checked the place MySQL system libraries in the system path (or something similar) checkbox on install? v5.5.9 runs fine with me on Win7. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question about performance
Or you can interrupt the query instead, although I've seen it not to work on occasions: KILL QUERY id; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Convert unix time to 12:00 AM of that same day
SELECT unix_timestamp() + 86400 + (unix_timestamp() % 86400); currently gives me 6:39 AM tomorrow SELECT UNIX_TIMESTAMP(DATE(FROM_UNIXTIME(1299003702))); actually gives me what I want, but seems really stupid way of getting something that is probably very simple On 03/01/2011 12:03 PM, Singer X.J. Wang wrote: http://en.wikipedia.org/wiki/Midnight On Tue, Mar 1, 2011 at 13:00, Singer X.J. Wang w...@singerwang.com mailto:w...@singerwang.com wrote: That's cause there's two midnights.. use = 1298999201 + 86400 + (1298999201 % 86400) On Tue, Mar 1, 2011 at 12:58, Bryan Cantwell bcantw...@firescope.com mailto:bcantw...@firescope.com wrote: It was of course a typo, and even with the correct number isn't the answer On 03/01/2011 11:47 AM, Claudio Nanni wrote: You can start by using 60*60*24=86400 ;) On Mar 1, 2011 6:17 PM, Bryan Cantwell bcantw...@firescope.com mailto:bcantw...@firescope.com mailto:bcantw...@firescope.com mailto:bcantw...@firescope.com wrote: I'd asked before how to convert a unix timestamp to the hour that it is in (and got the perfect answer) : 1298999201 = 3/1/2011 11:06:41 AM (1298999201 - (1298999201 % 3600)) = 3/1/2011 11:00:00 AM Now getting the timestamp converted to midnight of that same day isn't as simple as: 1298999201 - (1298999201 % 85400) That just gives me a unix time from yesterday... How can I convert 1298999201 (3/1/2011 11:06:41 AM) to 1298959200 (3/1/2011 12:00:00 AM)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- The best compliment you could give Pythian for our service is a referral.
Re: Convert unix time to 12:00 AM of that same day
That's closer: SELECT UNIX_TIMESTAMP() + 86400 - (UNIX_TIMESTAMP() % 86400); Gives me 6:00 PM today... On 03/01/2011 12:32 PM, Singer X.J. Wang wrote: SELECT unix_timestamp() + 86400 - (unix_timestamp() % 86400); -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Convert unix time to 12:00 AM of that same day
Hi Bryan, all! Bryan Cantwell wrote: That's closer: SELECT UNIX_TIMESTAMP() + 86400 - (UNIX_TIMESTAMP() % 86400); Gives me 6:00 PM today... The Unix timestamp is UTC-based (old name: GMT). You don't write which timezone you are using, but your notation 6:00 PM makes me assume you are US-based. Is it 6:00 PM in your timezone at UTC midnight? Joerg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie question: Association table and Foreign Key
2011/03/01 20:23 +0800, Wagyu Beef Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Well, if your problem is really like that in the example that you quote, then look up 'REFERENCES' under 'CREATE TABLE'. That shows you what to put in the common table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
