Postgres Pro
Downloads
Home   >   mailing lists

Thread: Migration wizard cannot connect via ODBC

Migration wizard cannot connect via ODBC

From
"Dan Langille"
Date:
I'm trying to copy the data in a MySQL database over to a PostgreSQL 
database.  I'm running:

- mysql 3.23.57-nt under Windows XP
- PostgreSQL 7.3.3 under FreeBSD 4.8
- pgAdmin II 1.4.12

Here are my steps:

1 - Connect to my pg db
2 - Select menu item Plugins -> Database Migration Wizard
3 - click on ODBC
4 - From the Datasource drop down list box, select the ODBC database
5 - type in the user name (root)
5 - click on Next

It it then that I get the lovely Windows message "pgAdmin II has 
encountered a problem and needs to close.  We are sorry for 
inconvenience."

If I suppy the wrong user name (e.g. rooted), I get an access denied 
message.

Through the control panel, I have confirmed that the ODBC connection 
works by clicking on the "Test Data Source" button. I've taken scree 
shots of the various ODBC settings:

http://beta.freebsddiary.org/tmp/odbc-main.jpg
http://beta.freebsddiary.org/tmp/odbc-options.jpg
http://beta.freebsddiary.org/tmp/odbc-test.jpg

Any ideas?  Thanks.
-- 
Dan Langille : http://www.langille.org/

Re: Migration wizard cannot connect via ODBC

From
"Dave Page"
Date:
It's rumoured that Dan Langille once said:
>
> It it then that I get the lovely Windows message "pgAdmin II has
> encountered a problem and needs to close.  We are sorry for
> inconvenience."

>
> Any ideas?  Thanks.

Yes, there is a bug in the MySQL driver. One of the older ones works fine,
but I don't have access to the details right now. Perhaps search for
Mysql/myodbc in the list archives, if not, let me know and I'll look
tomorrow.
Regards, Dave.

Re: Migration wizard cannot connect via ODBC

From
"Dan Langille"
Date:
On 22 Jun 2003 at 16:51, Dave Page wrote:

> It's rumoured that Dan Langille once said:
> >
> > It it then that I get the lovely Windows message "pgAdmin II has
> > encountered a problem and needs to close.  We are sorry for
> > inconvenience."
> 
> >
> > Any ideas?  Thanks.
> 
> Yes, there is a bug in the MySQL driver. One of the older ones works fine,
> but I don't have access to the details right now. Perhaps search for
> Mysql/myodbc in the list archives, if not, let me know and I'll look
> tomorrow.

Thanks.   http://pgadmin.postgresql.org/ seems to be down just now...
-- 
Dan Langille : http://www.langille.org/

Re: Migration wizard cannot connect via ODBC

From
"Dave Page"
Date:

> -----Original Message-----
> From: Dan Langille [mailto:dan@langille.org]
> Sent: 22 June 2003 17:51
> To: Dave Page
> Cc: pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Migration wizard cannot
> connect via ODBC
>
>
> Thanks.   http://pgadmin.postgresql.org/ seems to be down just now...

Looks OK from here.
From
http://archives.postgresql.org/pgadmin-support/2003-03/msg00068.php:

FYI - If you have anyone else reporting this issue, MyODBC 2.50.19 looks
to be the go!

Regards, Dave.

Re: Migration wizard cannot connect via ODBC

From
"Dan Langille"
Date:
On 22 Jun 2003 at 21:33, Dave Page wrote:

> > Thanks.   http://pgadmin.postgresql.org/ seems to be down just now...
> 
> Looks OK from here.

And now from here.

>  From
> http://archives.postgresql.org/pgadmin-support/2003-03/msg00068.php:
> 
> FYI - If you have anyone else reporting this issue, MyODBC 2.50.19 looks
> to be the go!

Thanks for that.  I've just tried version 2.50.39 (latest on website) 
and it seems fine too.

For what it's worth, the migration gets this error:

Creating table: newforum1_bodies Copying data...

An error occured at: 22/06/2003 5:11:40 PM: 
-2147467259: ERROR:  parser: unterminated quoted string at or near 
"'<HTML>tr -d "" at character 72

Rolling back... Done.

Sounds like the data contains extra "s which are not being escaped.  
Here's what I found:

mysql> select * from newforum1_bodies where body like '<HTML>tr -d 
%';
+-----+------------------------------------------+--------+
| id  | body                                     | thread |
+-----+------------------------------------------+--------+
| 155 | <HTML>tr -d "                            |    155 |
+-----+------------------------------------------+--------+
1 row in set (0.08 sec)

mysql>

If you want me to try a patch etc, I can do that.  Otherwise, I'll 
just modify the data.

And if you're on IRC or MSN, email me offlist and I'll send you my 
contact details.
-- 
Dan Langille : http://www.langille.org/

Re: Migration wizard cannot connect via ODBC

From
"Dave Page"
Date:

> -----Original Message-----
> From: Dan Langille [mailto:dan@langille.org]
> Sent: 22 June 2003 22:19
> To: Dave Page
> Cc: pgadmin-support@postgresql.org
> Subject: RE: [pgadmin-support] Migration wizard cannot
> connect via ODBC
>
>
> On 22 Jun 2003 at 21:33, Dave Page wrote:
>
> > > Thanks.   http://pgadmin.postgresql.org/ seems to be down
> just now...
> >
> > Looks OK from here.
>
> And now from here.
>
> >  From
> > http://archives.postgresql.org/pgadmin-support/2003-03/msg00068.php:
> >
> > FYI - If you have anyone else reporting this issue, MyODBC 2.50.19
> > looks to be the go!
>
> Thanks for that.  I've just tried version 2.50.39 (latest on website)
> and it seems fine too.

Good.

> For what it's worth, the migration gets this error:
>
> Creating table: newforum1_bodies
>   Copying data...
>
> An error occured at: 22/06/2003 5:11:40 PM:
> -2147467259: ERROR:  parser: unterminated quoted string at or near
> "'<HTML>tr -d "" at character 72
>
>
> If you want me to try a patch etc, I can do that.  Otherwise, I'll
> just modify the data.

Odd, it should escape the data - in fact the code looks like:

Values = Values & "'" & Replace(Replace((szValue), "\", "\\"), "'",
"''") & "', "

Thing is with that though, it's not escaping double quotes, but that's
because it doesn't need to. In PostgreSQL, the following will work just
fine:

INSERT INTO foo (bar) VALUES ('"');

Can you provide a log with the SQL query that's failing in it please?

Regards, Dave.

Re: Migration wizard cannot connect via ODBC

From
"Dan Langille"
Date:
On 23 Jun 2003 at 8:56, Dave Page wrote:

> > For what it's worth, the migration gets this error:
> > 
> > Creating table: newforum1_bodies
> >   Copying data...
> > 
> > An error occured at: 22/06/2003 5:11:40 PM: 
> > -2147467259: ERROR:  parser: unterminated quoted string at or near 
> > "'<HTML>tr -d "" at character 72
> >
> > 
> > If you want me to try a patch etc, I can do that.  Otherwise, I'll 
> > just modify the data.
> 
> Odd, it should escape the data - in fact the code looks like:
> 
> Values = Values & "'" & Replace(Replace((szValue), "\", "\\"), "'",
> "''") & "', "
> 
> Thing is with that though, it's not escaping double quotes, but that's
> because it doesn't need to. In PostgreSQL, the following will work just
> fine:
> 
> INSERT INTO foo (bar) VALUES ('"');
> 
> Can you provide a log with the SQL query that's failing in it please?

23/06/2003 9:04:58 AM - SQL (phorum_conversion): INSERT INTO "newforum1_bodies" ("id", "body", "thread") VALUES ('151',
'<HTML>Uh,Internal Server Errors may be triggered by a particular browser, but the problem _lies on the server itself_,
soyou''d best off scouring your error logs for 
 
clues.</HTML>', '133')
23/06/2003 9:04:58 AM - SQL (phorum_conversion): INSERT INTO "newforum1_bodies" ("id", "body", "thread") VALUES ('155',
'<HTML>tr-d "
 
23/06/2003 9:04:58 AM - SQL (phorum_conversion): ROLLBACK

If there's anything else, let me know.
-- 
Dan Langille : http://www.langille.org/

Re: Migration wizard cannot connect via ODBC

From
"Dave Page"
Date:

> -----Original Message-----
> From: Dan Langille [mailto:dan@langille.org]
> Sent: 23 June 2003 14:11
> To: Dave Page
> Cc: pgadmin-support@postgresql.org
> Subject: RE: [pgadmin-support] Migration wizard cannot
> connect via ODBC
>
>
> 23/06/2003 9:04:58 AM - SQL (phorum_conversion): INSERT INTO
> "newforum1_bodies" ("id", "body", "thread") VALUES ('151',
> '<HTML>Uh, Internal Server Errors may be triggered by a
> particular browser, but the problem _lies on the server
> itself_, so you''d best off scouring your error logs for
> clues.</HTML>', '133')
> 23/06/2003 9:04:58 AM - SQL (phorum_conversion): INSERT INTO
> "newforum1_bodies" ("id", "body", "thread") VALUES ('155',
> '<HTML>tr -d "
> 23/06/2003 9:04:58 AM - SQL (phorum_conversion): ROLLBACK

Hi Dan,

I notice that the query is not even fully built - could there be a \0 or
some other odd character in your data? A non-escaped quote or wahtever
wouldn't prevent the SQL being built in this way - it would just be
invalid SQL.

Regards, Dave.

Re: Migration wizard cannot connect via ODBC

From
"Dan Langille"
Date:
On 23 Jun 2003 at 15:00, Dave Page wrote:

> > On 23 Jun 2003 at 14:35, Dave Page wrote:
> > 
> > > > 23/06/2003 9:04:58 AM - SQL (phorum_conversion): INSERT INTO
> > > > "newforum1_bodies" ("id", "body", "thread") VALUES ('151', 
> > > > '<HTML>Uh, Internal Server Errors may be triggered by a 
> > > > particular browser, but the problem _lies on the server 
> > > > itself_, so you''d best off scouring your error logs for 
> > > > clues.</HTML>', '133')
> > > > 23/06/2003 9:04:58 AM - SQL (phorum_conversion): INSERT INTO 
> > > > "newforum1_bodies" ("id", "body", "thread") VALUES ('155', 
> > > > '<HTML>tr -d "
> > > > 23/06/2003 9:04:58 AM - SQL (phorum_conversion): ROLLBACK
> > > 
> > 
> > > I notice that the query is not even fully built - could 
> > there be a \0 or
> > > some other odd character in your data? A non-escaped quote 
> > or wahtever
> > > wouldn't prevent the SQL being built in this way - it would just be
> > > invalid SQL.
> > 
> > Looking at the output of mysqldump, I found this:
> > 
> > (155,'<HTML>tr -d \"\015\" < dosfile.txt\r\n</HTML>',155)
> 
> That's almost certainly the problem then. pgAdmin (or more precisely VB)
> is barfing on the control character (which mysqldump nicely outputs as a
> charcode).
> 
> Not much I can do about that I'm afraid.

Are you saying that databases which contain control characters cannot 
be migrated to PostgreSQL using this procedure?  Control characters 
are valid in strings.  They are just characters, slightly special, 
but characters nonetheless.

Hmmm, other suggestions?  
-- 
Dan Langille : http://www.langille.org/

Re: Migration wizard cannot connect via ODBC

From
"Dave Page"
Date:

> -----Original Message-----
> From: Dan Langille [mailto:dan@langille.org]
> Sent: 23 June 2003 15:18
> To: Dave Page
> Cc: pgadmin-support@postgresql.org
> Subject: RE: [pgadmin-support] Migration wizard cannot
> connect via ODBC
>
>
>
> Are you saying that databases which contain control characters cannot
> be migrated to PostgreSQL using this procedure?  Control characters
> are valid in strings.  They are just characters, slightly special,
> but characters nonetheless.

Everything is just a character at the end of the day (even null, after
all they are all just byte values between 0 & 255), unfortunately VB
cannot always handle the unprintable ones. I would suggest trying to
remove such characters from your data, or if you feel inclined modifying
the pgAdmin code to meet your requirements. I'm afraid this isnot
something I think can be easily fixed.

Regards, Dave.

Re: Migration wizard cannot connect via ODBC

From
"Dave Page"
Date:

> -----Original Message-----
> From: Dan Langille [mailto:dan@langille.org]
> Sent: 23 June 2003 14:45
> To: Dave Page
> Cc: pgadmin-support@postgresql.org
> Subject: RE: [pgadmin-support] Migration wizard cannot
> connect via ODBC
>
>
> On 23 Jun 2003 at 14:35, Dave Page wrote:
>
> > > 23/06/2003 9:04:58 AM - SQL (phorum_conversion): INSERT INTO
> > > "newforum1_bodies" ("id", "body", "thread") VALUES ('151',
> > > '<HTML>Uh, Internal Server Errors may be triggered by a
> > > particular browser, but the problem _lies on the server
> > > itself_, so you''d best off scouring your error logs for
> > > clues.</HTML>', '133')
> > > 23/06/2003 9:04:58 AM - SQL (phorum_conversion): INSERT INTO
> > > "newforum1_bodies" ("id", "body", "thread") VALUES ('155',
> > > '<HTML>tr -d "
> > > 23/06/2003 9:04:58 AM - SQL (phorum_conversion): ROLLBACK
> >
>
> > I notice that the query is not even fully built - could
> there be a \0 or
> > some other odd character in your data? A non-escaped quote
> or wahtever
> > wouldn't prevent the SQL being built in this way - it would just be
> > invalid SQL.
>
> Looking at the output of mysqldump, I found this:
>
> (155,'<HTML>tr -d \"\015\" < dosfile.txt\r\n</HTML>',155)

That's almost certainly the problem then. pgAdmin (or more precisely VB)
is barfing on the control character (which mysqldump nicely outputs as a
charcode).

Not much I can do about that I'm afraid.

Regards, Dave.

Re: Migration wizard cannot connect via ODBC

From
"Dan Langille"
Date:
On 23 Jun 2003 at 14:35, Dave Page wrote:

> > 23/06/2003 9:04:58 AM - SQL (phorum_conversion): INSERT INTO 
> > "newforum1_bodies" ("id", "body", "thread") VALUES ('151', 
> > '<HTML>Uh, Internal Server Errors may be triggered by a 
> > particular browser, but the problem _lies on the server 
> > itself_, so you''d best off scouring your error logs for 
> > clues.</HTML>', '133')
> > 23/06/2003 9:04:58 AM - SQL (phorum_conversion): INSERT INTO 
> > "newforum1_bodies" ("id", "body", "thread") VALUES ('155', 
> > '<HTML>tr -d "
> > 23/06/2003 9:04:58 AM - SQL (phorum_conversion): ROLLBACK
> 

> I notice that the query is not even fully built - could there be a \0 or
> some other odd character in your data? A non-escaped quote or wahtever
> wouldn't prevent the SQL being built in this way - it would just be
> invalid SQL.

Looking at the output of mysqldump, I found this:

(155,'<HTML>tr -d \"\015\" < dosfile.txt\r\n</HTML>',155)
-- 
Dan Langille : http://www.langille.org/