connection to a database with invalid name

[olivierf77]

First, and most important : many thanks and gratitude for a great program.

I am hosting a website at Proxad. (called online.fr in France). Great hoster, with Apache, Php and MySql, all reasonnably up to date, and at a very reasonnable price. They provide one database per website (still acceptable, within limits).

BUT the name of the database is the name of the domain: 'domainname.com'. This name has a '.', which is very clearly defined as an invalid character in MySql reference. As a result, almost all frontend programs choke on this invalid name, either immediately, or later, when transferring data from local to host.

All the frontends issue, quite reasonnably, orders such as:

Code:

     “sqlorder database.table.field …;”

which turn in my case to become:

Code:

     “sqlorder domainname.com.table.field …;”

===> WRONG

I asked online.fr to modify my database name to domainname_com, but their answer can honestly be summarized as:

-we modified mysql to suit our own needs.

-we have 50000 users, and no one complains.

-you say all frontends fail=> you are wrong.

-do not call us, we will call you.

-bye

— end of answer–

To say the least, their answer was not fully customer-oriented. Also, they do not have any phone available, and communicating one's frustration by email to such people seems somewhat hopeless.

I was wondering whether you may consider modifying your program to take into account such forbidden names. If you don't, I will fully understand, and not complain.

I did notice that at least some of your orders seem to work once I manually connect to it, by

Code:

  “use `domainname.com`;”

there may be a possibility, through turnaround processes, to still get the job done, but I cannot use the full power of your program, that I enjoy on my local databases, nor your synchronization process, which looks wonderful.

Unless mistaken, I believe that I cannot define an alias for a database name, only for field names.

Also, when one connects to such a system, shared between many different users, the following order:

Code:

“show databases;”

which is a reasonnable one when issued against a local, earned database,

is forbidden, since each user only has one database, named after its own domain.

Therefore, such an order, issued automatically at connection time, should be avoided, since a error is generated, which stops the frontend to query for the tables of the database.

As an option, if the database name is provided in the connection panel, the order following the connection should directly be

“use `domainname.com`;”

I hope my explanations are clear enough, and once again, convey my gratitude to you for this very neat program.

Olivier, Paris, France.

[nero]

Hi

I tried 'USE databasename as aliasname'

Very nice crash with SQLyog (SQLyog3.11, mysql4.0.12).

So the alias isn't posible from SQLyog.

I haven't tried it on the commandline level.

[Ritesh]

Quote:

Code:

“sqlorder database.table.field …;”

SQLyog uses a ` around names in all the SQL commands that it sends to the MySQL server 😀

Is it not happening in your case? Which version of MySQL are you using?

Quote:

cannot use the full power of your program, that I enjoy on my local databases, nor your synchronization process,

Can you give us the complete detail of errors you are getting?

Quote:

directly be “use `domainname.com`;”

Thanks for the suggestion 😀

[olivierf77]

Dear Ritesh

Thank you for your quick answer.

The Mysql my host is providing is:

OS=Debian GNU/Linux version unknown

Apache 1.x

MySql 3.23.52 (AFAIK latest 3.x release)

Php 4.3.1

All in all, a rather modern and up to date setup

my domain name is of the form 'myname.cc'. myname is another string, but it is irrelevant to the problem. 'cc' is another tld, such as fr, edu or com.

The single database provided by my host is:

host name='sql.myname.cc', => ok

user name='myname.cc', => ok

databasename='myname.cc' => NOT OK at ALL

password='xxx'. => OK

(also, accessing internet through: easynet.fr, a very good ADSL ISP, with great newsgroups).

I am running locally with

Windows XP SP 1 latest patches

Apache 2.0.45 (latest release)

MySql 4.0.12-nt – localhost via TCP/IP

When logging to host site, errmsg is:

Quote:

Err no 1045

Access denied for user: '[email protected]' (using password: YES)

then another errmsg:

Quote:

Could not refresh ObjectBrowser

then on the left panel, I see:

Code:

[email protected]

as the host, but no database available.

if I type in the top right panel:

Code:

use myname.cc;

answer in lower right panel is:

Quote:

Error Code : 1064

You have an error in your SQL syntax near '.cc' at line 1

(60 ms taken)

but an order with back-ticks:

Code:

use `myname.cc`;

=> SUCCESS !!:

Quote:

(0 row(s) affected)

(60 ms taken)

the database has indeed been successfully connected, since the order

Code:

show tables;

=> OK: displays the right tables in lower panel.

However, the database is still not shown as connected, since the program has no way

to realize that connection is now fully made. I understand that this is not a bug.

Also, when connect, I can issue the order

Code:

show tables;
select * from mytable;

=> OK. results displayed.

but:

Code:

select * from `myname.cc`.mytable;

will fail with errmsg:

Quote:

Error Code : 1103

Incorrect table name 'mytable'

(60 ms taken)

Also, probably, when synchronizing tables, I guess that the order 'use database_name;' cannot be issued by itself, but is probably issued for each order, as a prefix to each table_name and field name.

Finally, here are a few local tests: I created locally a folder with a name including a period: 'foo.cc', and copied a full small database in it.

SQLyog refused to link to it, since the database name is the name of the folder, and

this name is illegal. errmsg is:

Quote:

error 1102

Incorrect database name 'foo.cc”.

in this specific case, even issuing theorder with the name between back-ticks does not

work and generates the same errcode and msg (though it is displayed in the lower panel, and no more as a pop-up errmsg):

Code:

use `foo.cc`;

=>

Quote:

error 1102

Incorrect database name 'foo.cc”.

So the SQLyog behavior is slightly different on my local database (mysql 4.0.12 on Windows XP) than on the hosted database (Mysql 3.23.52 on Linux).

I hope this information is precise enough for you to understand my problem.

As I already said, definitely NOT the fault of SQLyog, but the fault of my host, who is quite un-cooperative on this specific field. Apart from this, good service, at a very reasonable price.

Once again, if you cannot take into account this non-standard behavior, I will definitely NOT say or even think anything wrong about your program. Many thanks again for providing it. Locally it is great.

Best Regards

Olivier

[Ritesh]

Hello

SQLyog logs all the queries which it sends to the server in the History tab. Can you give me the complete list of what is there in your History window after you have connected to the MySQL server.

This will help me in understanding where the problem lies

HTH

[olivierf77]

Dear Ritesh

History tab content, with my comments prefixed by —

— user comments: initialization

[15:20:16][ 60 ms] show databases

[15:20:19][ 60 ms] use `myname.cc`

[15:20:19][ 70 ms] use “

[15:20:19][ 60 ms] use “

— => displaying msg err 1045

— issuing order manually

[15:21:05][ 70 ms] use `myname.cc`

[15:21:05][ 60 ms] select database()

— => success

— issuing order manually

[15:23:12][ 60 ms] show tables

— => success

[15:24:03][ 491 ms] select * from tb_first

— => success

[15:26:21][ 60 ms] select * from `myname.cc`.tb_first

— => failure. errmsg:

— Error code 1103

— Incorrect table name 'tb_first'

— (60 ms taken)

And, once again, the problem is not in your program, but in their very poor choice of a databasename.

BTW, PhpAdmin installed on their site (rel 2.2.42) accepts data and/or structure dump orders, on their database, but a more recent release (2.5.0) installed on my computer, fails to get any value or list dump.

Best Regards

Olivier

[nero]

olivierf77 wrote on May 28 2003, 07:34 PM:

Dear Ritesh

History tab content, with my comments prefixed by —

— user comments: initialization

[15:20:16][ 60 ms] show databases

[15:20:19][ 60 ms] use `myname.cc`

[15:20:19][ 70 ms] use “

[15:20:19][ 60 ms] use “

— => displaying msg err 1045

— issuing order manually

[15:21:05][ 70 ms] use `myname.cc`

[15:21:05][ 60 ms] select database()

— => success

— issuing order manually

[15:23:12][ 60 ms] show tables

— => success

[15:24:03][ 491 ms] select * from tb_first

— => success

[15:26:21][ 60 ms] select * from `myname.cc`.tb_first

— => failure. errmsg:

— Error code 1103

— Incorrect table name 'tb_first'

— (60 ms taken)

And, once again, the problem is not in your program, but in their very poor choice of a databasename.

BTW, PhpAdmin installed on their site (rel 2.2.42) accepts data and/or structure dump orders, on their database, but a more recent release (2.5.0) installed on my computer, fails to get any value or list dump.

Best Regards

Olivier

Hi,

I follow this discussion.

I have a tip for you.

The problem can be this part:

[15:26:21][ 60 ms] select * from `myname.cc`.tb_first

— => failure. errmsg:

— Error code 1103

— Incorrect table name 'tb_first'

— (60 ms taken)

try select * from `myname.cc`.`tb_first`

I think if you start using backticks you have to do it for all the parts of a database.table.field construction. MySql did accept the databasename now but have problems with the tablename.

I did see your way of using backticks in more examples and as far I know it should work with backticks but it is imho stupid to use names like this by a provider that should have competence.

Succes Nero.

[nero]

Hi again,

Sorry for the long quote above. had not intention to do that.

I'm i right 'olivierf77' if i understand the provider can use dots in the database name?

If so and the backticks does not work then maybe they are running the server in ANSI mode.

As I have just read in the manual (for 4.0.5) the dot is not allowed to use.

You can find about that in the manual at 6.1.2.

The difference for ANSI mode describes that the double qoute is used instead of the backtick when I am reading it the right way. See section 1.7.2 (or on the website 1.8.2).

English is not my native language so I can interprete it false but Ritesh wil understand it, I think.

About your idee to use a alias for the database name, in the same section 6.1.2 is

also information about the characters allowed for the objects, all say NO to the dot but for the alias it's ok.

from the man:

Identifier, Max length, Allowed characters

---

Database 64 Any character that is allowed in a directory name except `/', `' or `.'

Table 64 Any character that is allowed in a file name, except `/' or `.'

Column 64 All characters.

Alias 255 All characters.

Also a question is the real name of the database.

Is it shure it does not contain a reserved word. The list is very big but some should be nice to have as domain name. In manual 6.1.7 is the list

Ritesh:

Can it be a bug(small) if SQLyog crashes when I try to use

'USE databasename as aliasname' ?

Have a little luck Nero.

[nero]

Still another question.

I have readed your explanation again.

They say customized MySQL? –we modified mysql to suit our own needs

Well that gives me the hint for the posibility that they did not use a dot at all.

Like I do some protection to not follow the simple standard rules but use a comma in the name. For many viewers it is not always clear to see in a filename in a quick look. Just like using not a space chr(20) but a space chr(255) or chr(176).

Posible is the dot a ¸ ascii character 184 ¸ look almost the same for example on my lcd monitor size 1024/768. 😉

Why did they customize? Securety perhaps :P. Strange that there phpAdmin works and yours not. I have tried the same as your action create a dot db and copy the files in it. No difference with phpAdmin 2.3.3 or 2.5 and the same result as you have. Not working :(. SQLyog show the database in the objectbrowser but can't create/see any table. Did they modify phpAdmin also to show you a dot and are they using in real another character.? It can be done.

It looks to me that you are able to access your database with there phpAdmin localy only for security reasons.

Secure Nero

[olivierf77]

dear Nero

Thank you for your help on this matter, and for your suggestions. I tried`to connect as you suggested with

Code:

select `myname.cc`.`tb_first`;

but either SQLyog or MySql refused with error 1103: incorrect table name.

It was worth trying though.

As regards the caracter used as a separator, your comments are quite pertinent, and I am aware that this trick was used for DOS filenames and directories, but this is not the case here.

The separator used is indeed the period ('.').

I am quite aware that this is completely illegal, under reference manual 6.1.2

—- Start extract

6.1.2 Database, Table, Index, Column, and Alias Names

Database, table, index, column, and alias names all follow the same

rules in MySQL.

Note that the rules changed starting with MySQL Version 3.23.6 when

we introduced quoting of identifiers (database, table, and column

names) with “'. `”' will also work to quote identifiers if you run

in ANSI mode. See section 1.7.2 Running MySQL in ANSI Mode.

Identifier – Max length – Allowed characters

– Database 64 Any character that is allowed in a directory name except `/', `' or `.'.

– Table 64 Any character that is allowed in a file name, except `/' or `.'.

– Column 64 All characters.

– Alias 255 All characters.

Note that in addition to the above, you can't have ASCII(0) or ASCII(255) or the quoting character in an identifier.

…..

You cannot use the `.' character in names because it is used to

extend the format by which you can refer to columns (see immediately

below).

---

End extract

I emailed this part to Online.net support, and their answer was (translation):

> Problem: Acoording to MySql norm 6.1.2 , joined hereafter here, the

>character '.' is forbidden for use in a database name

Answer: we modified the norm for our own needs. We host 50 000 customers this way, and it never created any problems.

> Result: no GUI frontend is available to easily manage the data on your site

Answer:

Wrong

I am not making up their answer.

At first I could not believe my eyes when I read their answer.

If I cannot solve this problem from the GUI end, I will try again to contact them (by email, since they offer absolutely no phone contact), but by the tone of their answer, I have a sinking feeling in my stomach that they could not care less about customer service. How do you say “go to hell?”.

Also, running in ANSI mode will not solve the problem.

Olivier

[nero]

Hi Olivier,

Wel that sounds not good. 🙁

If they say there is a GUI that works on there MySQL version why don't they tell you wich one.

Problem may be solved, and not a upset customer. 😡

Still I believe they must have changed more things to not standard behaviour. If the customers all using phpAdmin then they must have changed that one to. :huh:

If they have a commercial license for MySQL then they are also allowed to change it as they like, I think everybody has that right, so you can't do anything against it.

Same is for phpAdmin. For both are the sourcecode free to use I believe. 😮

With there response is it also not custom friendly.

I don't understand that in the beginning of this topic you was so happy with them, but anyway thay are not the only one in France.

Quote:

How do you say “go to hell?”.

That is in Dutch “Loop naar de hel.” 🙄

I hope you will find a solution but this will be fighting against air.

Nero :ph34r:

[olivierf77]

Dear Nero,

I agree with you.

I just wrote again to my host, and kindly asked him to reconsider his stupid naming scheme, and/or to point to any working MySql Client program.

I do not expect very much though.

If I do not have a reasonable answer, I will consider switching.

I appreciate them because online.fr is very reasonably priced (30 EUR/year)

offers 5 email addresses, unlimited alias email, PHP 4.3.1, Mysql 3.x (but with a stupid database name), webmail access, and since a few weeks, secured payment.

I started a new thread for a suggestion related to this topic.

Best Regards

Olivier

[allan_hope]

Although this doesn't help with the problem you are having using SQLyog it might address the question as to which GUI tool will connect. I have had similar experiences with GUI tools being unable to connect to certain hosts and have found that MySQL-Front will work. Unfortunately some of the features you may like in SQLyog may not be available.

Hope this helps, at least temporarily.

[Author]

Posts