Base Connection to Server MySQL

[Anon_E_Mouse]

After I don't know how many hours ... can't get Base on a client to connect to a MySQL database on the server (i.e. remotely). Have followed very carefully instructions on page 4 of instruction booklet http://wiki.services.openoffice.org/wik ... SQLandBase. Just doesn't work. Once I figured out parsing issue in connection string generated by Base connection code, still get error "can't get host name for your address" when testing connection. Note, can get Base on client to connect to Base data file on server, can get Base to connect to MySQL database on localhost (even tested simultaneous edits using both Base and MySQL client command line at same time - no problems). Top of page 5 of above referenced instruction document says if error there may be a problem in setting of "default-character-set" in my.cnf file - but is no such file anywhere on computers (either one). Even ran the repair option in the MySQL installer package in case of possible loss somehow. Still no luck. The computers "see" each other, Base on client can navigate to to server, etc.
So ... surely Base can be used as a front end in a multi user enviroment. Any suggestions?????
Thank you.
JEH

[Villeroy]

Firewall anybody? Closed port?

[Anon_E_Mouse]

Villeroy,
Turned firewall on server off. Same error. Rebooted server. Still same error. Base on the client just can't make connection to MySQL on server. Don't know about "closed port" - client and server are otherwise communicating. Have done stuff from copying files between them to used other programs that communicated. I just don't know. I've exhausted my understanding. It should work. Other types of communication between the client and server work. I followed the instructions very carefully - I don't think I missed anything. The only issue may be the missing my.cnf file. But the "repair option" in the installer package didn't put one back on.
I'm at a loss. I really don't want to go back to dBase as a front end since it is tied to Microsoft and I couldn't use it if/when I switch operating systems to something else - Linux, Mac O/S 10 or ? Base is a nice program and will supposedly run on the other systems. Writer is a very comprehensive program with features better than Word. Like & use Calc as well.
Any suggestions???
JEH

[r4zoli]

The mysql connector has bug, it can connect sites in localhosts only.
I presume you use mysql connector.
Use JDBC driver for mysql connection over the internet.

Can you connect to mysql external server with mysql tools, such as mysql workbench?

[Anon_E_Mouse]

4zoli (& Villeroy),
Since above posts have been doing some additional testing to try and isolate problem.
1) Tried using MySQL command line client on client computer to access server - returned exact same error as Base. So, I think we can rule out Base as the culprit. The parsing within Base is not creating a faulty connection string.
2) To see if the problem was within MySQL itself, I installed Firebird on the client (normally used for internet access). The Firebird command line client (similar to the MySQL one) when trying to access a database on the server generated a similar error from what I got with Base and MySQL. The Firebird error was "unable to establish connection - host failed to respond".
3) To further check from the other direction created a testem.fdb database on client and tried to access from server. Same error.
4) As an additional check I cranked up dBase on the server, navigated to the client computer and created a file. No issue - file there and editable from the client itself.
So ..... my conclusion at this point is that it is an issue with the operating system (XP) when using it with client/server databases in general. Perhaps, as Villeroy suggested, we have a "closed (clogged?!) port". At this point what is certain is that I can only count on using Base by itself in a sequential mode, one user at a time accessing the datafile on the server. Hardly practical in a multi user environment. What I am referring to as the server has been in use for some years with no problem of accessing from dBase generated exe applications running on both XP and W-98 computers.
There is probably a "secret" switch in XP that would clear up the problem. But I doubt Microsoft is going to tell me. Perhaps some kind soul that actually uses client/server would let me in on the solution.
JEH
PS I realize this is straying "off topic" since I do not now believe the problem to be with Base itself. My long term goal is to replace windows and I have trying to find good software (like OOo) that will run on different platforms. dBase is a good system but it is tied completely to windows/dos. Of course, I still have to find a replacement for AutoCAD (though they recently came out with a version that will run on OS/10, my accounting program (Peachtree) and Project management software. Anyway, I hope someone can offer a fix. Thanks.

[rudolfo]

It's all about network traffic: mysql uses TCP/IP much the same way as your browser uses the http protocol. So I suggest to start with the facts: Both machines, client and server, run on Windows XP? XP was the first MS Windows OS that came with a builtin firewall.
Mysql listens on tcp port 3306 on the server, but by default the listening port is bound to the local loop interface and not reachable from a remote computer. So do it step by step, if one step doesn't show the expected results, this is where you have to fix it:

• Open a command window on the server an run netstat -na. You should see a line with :3306 as the port somewhere in the middle of one of the listed lines. Make sure the ip address before that is 0.0.0.0 or another IP address of the server but not the local loop address 127.0.0.1. If it is the local loop address you have to change the my.cnf on the server.
• Check the basic network connectivity and name resolution from the client: ping servername. If only the name resolution is not working, but you know the IP address of the server in the local network you can use the IP instead of the servername (with ping and with mysql/jdbc)
• Test if the tcp port of the mysql server can be reached from the client: telnet servername 3306. Again if name resolution is not working in your local network use the server's IP address instead of the servername. It should say something like "Connected to ..., Escape character is .." if the port can be reached. If you get something else the port is probably filtered by the firewall or the my.cnf from step 1 is still not configured in the right way. Turn of the firewall (maybe on both machines) and repeat the test. If that helps talk with your network admin in order to enable some rules in the firewall that open traffic on tcp port 3306.
• Still from the command line on the client mysql -p -u your_user -h servername

If all 4 tests above show the expected result we can continue with Base specific issues. Nevertheless it is always a good idea to confirm what driver you are using for the connection: the native sdbc driver extension or the jdbc driver ... or ODBC?

[Anon_E_Mouse]

rudolfo,
Thanks for your explicit and detailed response - still not working. (Must be repayment for my frivolous and misspent youth).
Anyway,
1) netstat -na shows 0.0.0.0 3306 with a status of "listening" (also shows others such as 3050 which is used by Firebird)
2) can ping <server_name> with appropriate packets sent/received. (no need to use IP address)
3) telnet <server_name> 3306 generates the error. Turned off the firewall, checked exceptions to include everything including telnet itself, disabled (or completely uninstalled) any third party virus checking. All to no avail. So can't reach the MySQL server port from the client. Couldn't from Firebird either (subsequent to my last post and before reading yours, I checked both ways between server (an XP box) and another client (one of my W-98 boxes) Firebird command line client can reach the W-98 box from the XP, but not the other way). So it has to be some setting, some toggle, something with XP that is causing the problem. Perhaps it is the missing my.cnf file. Have run searches including hidden files, system directories etc. It just doesn't show up. Any idea how to locate it, or, recreate one? Is this a windows file? It must be since this connection problem (accessing the port) shows up with Firebird as well as MySQL.

What is so frustrating is I have never had this kind of problem with flat file databases. dBase generated exe's can read and update files on other computers with no problem - nor with SQL databases when run strictly on localhost - including XPs. As far as talking with my "network administrator" don't I wish. If I had any money to pay one I would have dumped this (and many, many other things) on them long ago. The closest thing I have to a network administrator is the kind help I get on user forums from real experts - like the people here.

By the way, switched from native driver to jdbc driver when first ran into problem. Given what appears to be the issue, I don't think ODBC would make any difference. Question is how to "unclog" my ports, how to keep XP from interfering with communications between the client and the SQL engines "listening" at the various ports. And that I just don't know. Have tried all suggestions (at least I think I have). Maybe further suggestions will turn the light bulb on for me. Your post really zeroed in on the issue - at least I know what it is now. Question is how to fix it.

Anyway, I don't know how to express my appreciation for the efforts to help.
JEH

[rudolfo]

I am still not sure about your setup: Reading your last post I think you have one XP box and one or two Win98 computers?
Connecting a Win9x system to a Win2k/WinXP had some flaws, at least in my constellations.
What kind of WinXP release do you use? Home or Professional Edition?
I think the main problem is to locate the mysql.conf file. Apparently if you say that the port 3306 is open the mysql service is running. Though I'd rather assume that it won't run, if it doesn't find a my.cnf configuration file. Check the Services in the Control Panel and locate the Mysql service. Right Click on the Properties of the Mysql service and check what it says. In my german installation it shows:
"C:\Programme\MySQL\MySQLServer-5.1\bin\mysqld" --defaults-file="C:\Programme\MySQL\MySQLServer-5.1\my.ini" MySQL

The next test would be if you can connect to the external interface on the XP computer itself:
mysql -p -u root -h 192.168.1.1 mysql
The -h option specifies the host computer to which you want to connect. If you don't specify it the mysql tool will use the local loop 127.0.0.1 address as default which doesn't help us for our test. Of course the 192.168.1.1 is only a typical IP address in a local network, but you have to change this to the IP of your Windows XP box.

[Anon_E_Mouse]

rudolfo;
Thanks for your post - am running 3 XP machines, 3 W-98 boxes, Professional edition, vers 2002, Service Pack 3. Just realized that when everyone talks about my.cnf file, they may be talking about my.ini file - which IS there with pointers to datadir=, basedir= etc. The link you showed to your file my.ini made me realize that. Don't know if some setting there is the problem or not.
However, progress being made ... FINALLY got an error that came (at least I think it did) from mysqld.exe, not XP. Used your connection string suggested above "mysql -p -u root -h <my IP address> mysql" generated same errors "can't connect", "can't get host name for your address". However, when I used the computer name, ie. "mysql -p -u root <JOHNXP=my computer name> mysql" it came back "host 'JOHNXP' is not allowed to connect to this MySQL server" - clearly (at least I think it is clear?) this, at least, is coming from the server engine, not from XP - but maybe not. At least I am getting different errors, that's an improvement, I guess.
Anyway, I have been trying to get XP to allow access to the port and explicitly specified that XP should allow access to port 3306 by anybody.
I have the feeling I am close to resolving this. I will be doing some more variations. What's the old cliche, given an infinite number of monkeys, and an infinite number of typewriters, eventually one of them will write Hamlet <or perhaps Faust, or, Sorrows of Young Werther since you're from Germany> (or something like that).
Your responses have been most helpful in stimulating my continued efforts. Thank you.
JEH

[rudolfo]

Yes, right. I usually have mysql running on Linux and there the config file is named my.cnf. my.ini is used on windows. Good for those that never saw a Linux box before, bad for the others. There is something that puzzles me: The -h option specifies the hostname or IP address of the server to which you are going to connect. The "host 'JOHNXP' is not allowed to connect to this MySQL server" uses the hostname of the client computer. There is no way to specify this client hostname on the command line. It is evaluated dynamically when the server receives the connection request. Not sure about the internals here, but I think it does a reverse DNS lookup on the IP address. Or it simply gets it from C:\windows\system32\drivers\etc\hosts.
mysql is the "system" database with rather rigid restrictions. Just run the same command without the final mysql parameter. If you are still rejected with "not allowed" you will have to grant access from other hosts to your user (better use a different user than root for this):
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'someuser'@'clienthost';

[Anon_E_Mouse]

rudolfo,
I thought the idea from one of your suggestions was to connect to the server, from the server, acting as a client by calling up the host name as server, rather than a connection as localhost. Anyway, the error message was as quoted.
After spending more time over the past few days, messing with settings in Windows, etc., finally got connection from 'client' units (XP and w-98) to connect with a Firebird engine, and hence a database, on the server. After doing some SELECTs from different clients and then running netstat on the server (as you suggested) the port Firebird uses (3050) showed up in the foreign address column with the foreign IP. The status column showed "established". Too bad I can't get MySQL to allow connections from foreign computers.
Frankly, something seems messed up with MySQL. Maybe when I was first playing with it something got screwed up. I may try to get a COMPLETE removal of anything connected with MySQL (including password files, the apparently unmovable database directory, etc) on the server and try a "clean" reinstallation. While MySQL seems friendly, the documentation is comprehensive (am glad I didn't spend the money to print that 3600 page reference manual though). Frankly in a sense I learned more from your responses, your attacking it in a methodical way.
Anyway, I like Base - it's a nice program. The command window SQL (I think is in the tools) is nice to change tables, etc. Now, if can get Base to play nice with Firebird. It can connect via ODBC with a firebird database, but, when I attempt to open a table the code it is sending to the firebird engine is generating errors "[ODBC Firebird Driver] Fetch type out of range". Don't know if the firebird people need or are working on a different driver that can work with with what Base is doing, or, maybe the ODBC driver I down loaded from Oracle wasn't installed right. Anyway. am disgusted for now with this stuff.
My old dBase compiled code works (still in character mode), application on 'client' opens a file pointing to the 'server' (where the data files are anyway), asks for a password, opens a password file on the 'server' and restricts user to assigned records. Example, a salesman can be assigned only their own records and nothing else even shows up. I wrote all that out nearly 20 years ago. The benefit of an SQL is of course the user is working with copies or data not the original stuff, much of the 'work' can be done by the server database through procedures, triggers and such (have done a little of that in Firebird). But, even more important is that the SQL SELECT can really be fine tuned to zero in on exactly the data subsets that you want.
Anyway, thank you so much.
John

[rudolfo]

The crucial difference between database servers and remote file access to dbase files is that the database server manages the remote access permissions and the concurrency (table and row locking) while the remote dbase files leave the access permission to the underlying OS of the fileserver and the locking to the client program.
The server needs configuration while the file access approach seems to work out of the box (not only "seems" but it actually does so, but only for a few scenarios).

The MySQL privilege system is not too complicated. Its a who, a from where and a what.
(user, location/host and granted privileges). And there are 3 tables user, db and host.
who and where are (logically) ANDed over the 3 tables and the allowed privileges are ORed (they accumulate).
The table mysql.user is the main entrance. It restricts the access to all databases. If you have a line there user: john, host: localhost, select_priv: yes, create_priv: yes then user john can select from all tables of all databases and can create tables on all available databases but only if he logs in from locallly. Additionally the table mysql.db can have an entry: user: john, host: %, db: test_db, insert_priv: yes, update_priv: yes. On its own this line means john can update and insert rows on all tables in the database "test_db" no matter from which host he logs in.
But now the combination logic comes into the game. user & host are ANDed: We need the intersection of localhost and any host, so that's again only localhost. If John tries to connect from remote he will be rejected. But once he is logged in from localhost he has select and create privileges on all databases, but additionally for the spefiic database test_db the privileges "insert and update" are accumulated to his standard privileges.

So don't give up to early. Start the mysql command line client on the server.
use mysql
select user, host, select_priv, insert_priv, create_priv from user;
select user, host, db, select_priv, insert_priv, create_priv from db;

Most probably you have to extend the value of the host field in the users table if you have problems to connect. I would rather guess that your table mysql.db has no rows as in most other installations.

[Anon_E_Mouse]

rudolfo,
On the basis of your last post, plugging away at it produces exactly the same error message as before. What done?
1) root still cannot log onto server computer ip (or by name). Only as localhost.
2) created <newuser> and added rows to db, user, host and even servers tables
3) in db table shows <newuser>,<servername>(under host),and under db <testdb>
4) in user table inserted <newuser>,<servername>(under host)
5) in host table inserted <server name>,<testdb>(under db)
6) even did CREATE SERVER in the server table inserting (checking) data to be sure port correct & etc.
7) did a select on all above tables to be sure info was there and correct.
8) <newuser> can log onto <servername> now (root can not) which is something. Even get a welcome message from Oracle and a connection id.
9) but.............. when <newuser> attempts to log on to <servername> from a second computer same crap - "Error #1042 (HY000): Can't get host name for your address". So??

I hesitate in asking this question, but I'm beginning to get a sinking feeling that the Community edition of MySQL as downloaded and as it installs itself is either disabled or deliberately designed only to work on a single computer and not in a multi user environment. Tell me, do you actually know anyone, first hand, that has/is actually using Base on one computer to connect to a MySQL engine on another computer? Anyone, I mean, who is not a full time computer person?

Re your comment about the difference between the client/server paradigm and remote file access (as in dBase) I am perhaps misunderstanding it (a not uncommon failing on my part) but I want to move to the client/server paradigm not just because of security issues (after all, the current dBase has field level access setting based on user log in, encrypted files etc) but also, because in client/server the user is not working with "live" data - data is changed on server, not on the client computer. Also, the ability to have the server engine do the "work" through triggers, procedures, etc. that would otherwise have to be done on each local computer. As far as concurrency issues, never had a problem with record locks etc with dBase, but it only had a handful of concurrent users at any given time. Frankly, I haven't had the opportunity to actually test out a server engine for that. I presume that server engines are more reliable.

Anyway, that's it for now. If can think of any suggestions will certainly try them. I've learned a lot from you - but the original problem is still there. Thanks.
John

[rudolfo]

Here are my settings of the tables user and db. The table host is empty and that's how it should be in 99% of the cases. I think you have missunderstood something at least your statement about "CREATE SERVER" and "server name" indicates this. From the view of the server anything related with the server itself is in the configuration file my.ini or my.cnf. The host columns in the mysql.user and mysql.db tables are always refering to remote hosts where the client is started (localhost is just a special kind of remote host with the client that is the same machine as where the server is running).

Look at the data in my administrative tables of the Mysql community edition:

Code: Select all

C:\>mysql -p -u root
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.22-rc-community MySQL Community Server (GPL)

mysql> use mysql
Database changed

mysql> select host,user,select_priv,create_priv,super_priv from user;
+-----------+---------+-------------+-------------+------------+
| host      | user    | select_priv | create_priv | super_priv |
+-----------+---------+-------------+-------------+------------+
| localhost | root    | Y           | Y           | Y          | root can only login locally
| %         | webuser | N           | N           | N          | webuser can login from remote
| 192.168.% | admin   | Y           | Y           | N          | admin can login from the local network
+-----------+---------+-------------+-------------+------------+
2 rows in set (0.03 sec)

mysql> select db,host,user,select_priv,create_priv from db;
+-----------+-------------+---------+-------------+-------------+
| db        | host        | user    | select_priv | create_priv |
+-----------+-------------+---------+-------------+-------------+
| invoices  | 192.168.0.% | webuser | Y           | Y           |
| drupalcms | %           | webuser | Y           | Y           |
| test_db   | 192.168.1.% | webuser | Y           | Y           |
+-----------+-------------+---------+-------------+-------------+
| mysql     | 192.168.1.% | root    | Y           | Y           | HAS NO EFFECT
+-----------+-------------+---------+-------------+-------------+
3 rows in set (0.05 sec)

As mentioned in my previous post: The entries in the table db can not extend the settings in the user table. They can only restrict them further. In other words the 4th record of the above db table is usually not there. I have only added it for demonstration purpose.
webuser can access the mysql server from anywhere (the host field uses the '%' wildcard), but he won't get any privileges upon connection (all N in the user table). Only when he selects a database the access rights for this database will be evaluated ("the ..._priv columns accumulate").

The following is what happens when connecting as user webuser from a remote machine (192.168.1.15) to the server on 192.168.1.92:

Code: Select all

C:\>mysql -p -u webuser -h 192.168.1.92
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.22-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql
ERROR 1044 (42000): Access denied for user 'webuser'@'%' to database 'mysql'
mysql> use test_db
Database changed
mysql> use invoices
ERROR 1044 (42000): Access denied for user 'webuser'@'%' to database 'invoices'

There is no entry for database mysql and user webuser so "use mysql" fails. Database test_db is okay, because the client IP 192.168.1.15 falls into the range 192.168.1.%. Database invoices fails because remote access to this database is only allowed from the 192.168.0.0/24 subnet.

And nestat on the server shows the tcp connection as established:

Code: Select all

  Proto  Local Address          Remote address         Status
  TCP    192.168.1.92:3306      192.168.1.15:1401      ESTABLISHED

Please see the table on Access Control, Stage 1: Connection Verification for detailed information on this.

This should give you enough understanding and example data to get your privilege tables configured. And please remember, if you do a insert, update or delete on the mysql.* tables you have to run flush privileges; to activate the changed settings.

And once the connection works with the mysql tool from the remote computer it will also work with OOo Base and the JDBC driver or the SDBC driver.

[Anon_E_Mouse]

rudolfo,
Have to rush this since I think site cuts me off after so many minutes, so will reenter message I just did.
First, thanks for you last response. When printed it out and studied it had brief glimmer of hope could get connected.

I followed your example (at least I think I did) very carefully. Now, selects on servers and host tables return null set. user and db table shows client address under host (not host address that normal meaning would imply). did ping server to client computer and client to server computer to be sure was working with correct IP. Did "FLUSH PRIVILEGES" (again, opposite from expected meaning) as well as COMMIT on changes. Even exited everything and reboot to check changes taking in mysql tables. Same connection error as was getting a week ago. Used wildcard '%' as per your examples. Same error.

"ERROR 1042 Can't get hostname for your address" Unsure if "hostname" means server computer or client, or, whether "address" means server or client computer from whence the connection request was initiated. Unless I can figure out exactly what this means, or, exactly how it can be fixed that's it. The MySQL ref manual shows error but can't find exactly what it means or how it is fixed. If, can figure it out then I should be able to connect the MySQL command line client on one computer to the MySQL database engine on another computer and, as you pointed out, I should be able to get Base->MySQL working together (as they do nicely when running on localhost).

Any thoughts on source, meaning and fix for the above error message? At this point my only hope is complete uninstall of MySQL, removal of database directories and anything else that is left after running the uninstall program. Then could reinstall MySQL completely clean. Don't have much hope that will help but it's the only thing I can think of.

Base will connect to Firebird using ODBC, but when attempt to open tables Firebird sends back an error message that seems to indicate the select statement originating from Base is in error. I think it is I need a different driver rather than the old ODBC driver have used in past.

Anyway, thank you so much, rudolfo. Have learned a lot about the workings of MySQL from you (although unless I can get Base to connect with it in a multi-user environment it won't do me much good as I certainly cannot use MySQL).

Thanks

John

[rudolfo]

Couldn't you just paste the content of your user and db tables in a code section?! Then I could state more clearly if there is something wrong with your tables or not. My previous post helps you to decide which columns are relevant. Also it would be helpful if you tell us, what IP addresses you are using. For the client and for the server. Otherwise it is not possible to say if your values in the host columns of the two above tables can work or not.
I am sure you are using non-routable IP-adress 192.168.xxx.xxx or 10.0.xxx.xxx. in your local network so you won't reveal anything sensible information with this.

I haven't seen "ERROR 1042 Can't get hostname for your address" before, but I understand this as the server is trying to retrieve some information about the client, it has the IP address (that's directly coming with the tcp CONNECT request) and queries the hostname for this IP. The DNS phrase for this is reverse lookup.

On the server machine, open a console and check:
nslookup 192.168.1.7 (the IP address of your client where you start mysql monitor)

If you see 192.168.1.7 Non-existent domain, you have the root of your problem. But you can work around this, if you enter an explicit line for this IP in the hosts file of the Mysql server computer. On windows it is C:\windows\system32\drivers\etc\hosts

Code: Select all

192.168.1.7      host-name.of-your.choice

[Anon_E_Mouse]

rudolfo,
After contemplating issues believe I see problem (though not how to fix). MySQL at a minimum uses a non standards compliant connection algorithm. It is not intelligent enough to acquire information it may need regarding from whence the connection is originating.

As far as providing exact info regarding settings, no problem - can't cut/paste directly as server NEVER connected directly to internet. For purpose of testing connection two computers being used. One named JOHNXP ("server") with 192.168.0.2. Second named IBMNOTEBOOK ("client") with 192.168.0.5. Both IBMs running XP with service pack 3. Both can ping each other by computer name or IP address. Database named "mystuff" created in Base when experimenting with it, copied to spreadsheet and exported as tab delimited for importing into MySQL per chapter 4 tutorial of MySQL ref manual.

Exact current readings in mysql.user table on server using select host,user,select_priv,super,priv:
localhost root Y Y
JOHNXP john Y Y
localhost N N
192.168.0.5 diane Y N
% webuser N N (left over from trying some experiments based on your last post)

Exact current readings in mysql.db table on server using select host,user,db,select_priv
JOHNXP john mystuff Y
192.168.0.5 diane mystuff Y

Please note, these are current contents and have varied over time with different settings attempted per your previous posts.

running selects on hosts and server tables yields null set.

Don't know how the above will help you - problem is in connection from client. there are other computers connected via router to JOHNXP but they are not being used to test the Base->MySQL connection. All computers can successfully ping each other by name or IP address. Normally, a connection string "should" consist of a CONNECT <server> <database> <user attempting to connect> <password>. The database engine listening on a given port (3306 for MySQL, 3050 for Firebird) should then parse the connection string, check its tables for connection ok and then go to work. At least that's how I understand it.

JOHN

[rudolfo]

In MySQL you do not connect to a database (mysql, john or diane in your case) you connect to the database server engine. That's a two step process with the mysql monitor application (mysql.exe) as you can see in my second last post:

• the tcp connection is made with C:\>mysql -p -u webuser -h 192.168.1.92 at the OS command line (Note: only username and the host ip of the server are given, no database name!)
• the database is opened with mysql> use john at the prompt of the mysql monitor

Your problem is related with the tcp connection. OpenOffice Base is not appropriate to investigate such problems because Base can not split this process into two separate steps. And hence it's error messages might be missleading. As long as you don't open a database with use ... the mysql.db table is not evaluated by the server.
In other words the root for your problem is -- if anywhere in Mysl -- then it is in the table mysql.user. But that looks okay according to your last post. So we have to look on the network level!

You haven't told me about the results that you get with nslookup. Microsoft OSes are a bit special in how they use name resolution. Up to XP or maybe Vista they used WINS or SMB (LAN Manager) protocol to resolve host names before they attempted to use standard DNS service. The ping.exe on Microsoft machines knows about WINS or LAN Manager, so it can use it to resolve names to IP addresses. But mysql as a cross platform software doesn't use this technology. It relies on file based resolution (/etc/hosts on Unix systems and C:\windows\system32\drivers\etc\hosts on Windows XP) or on DNS lookups on udp port 53. Typically in a small MS Windows network you don't have dns resolution for your LAN computers, only for internet hosts like www.google.com or www.microsoft.com. Running a local DNS server is not easy to setup and maintain. But you don't need it, because you can use the file based resolution with /etc/hosts instead.

So please add lines to your file C:\windows\system32\drivers\etc\hosts on the machine where the Mysql server is running:

Code: Select all

192.168.0.2    johnxp
192.168.0.5    ibmnotebook

And I repeat again: there is no such thing as a table server in mysql. Whoever told you to create this was completely wrong.

[Anon_E_Mouse]

Rudolfo,
After printing out, studying and implementing suggestions in your last post, results:
1) Changing host file and rebooting doesn't change anything, except computer JOHNXP could no longer ping IBMNOTEBOOK (although could ping the other way around).
2) CONNECT string used was based on MySQL ref manual syntax. Using your suggested tcp connection syntax generates same error results, to wit: can't get hostname for your address.
3) nslookup 1st generates DNS request timed out - can't find server name for address 192.118.0.1. After setting DNS address to 198.168.0.2 error "can't find server name for address default server: not available. HOWEVER, if run hostname from command line it does return the name (JOHNXP).
4) I have MySQL version 5.5.10 Community Server. When I use mysql and do a show tables, a table named servers shows up in the mysql db between proxies_priv and slow_log. I did not create table (only inserted values, then removed them after your prior posts suggesting there shouldn't be anything there - the table itself has been there.
5) the error (and inability to connect) are returning same error when effort made to connect from either client command line, mysql command line on client, or, from within Base. ie. same error from where ever attempt is made.
6) Firebird is cross platform software and I am now getting connections between client and Firebird engine running on server and thus can connect to databases on server.

At this point I can only conclude (as I had previously) that either the Community Server version isn't designed for normal experienced user, or, it somehow didn't install itself correctly on JOHNXP, or, it simply isn't smart enough to figure out how to allow connections from remote client computers without the efforts of a full time computer professional. Something is wrong here. I am going to try and completely remove all traces of the MySQL installation on JohnXP and reinstall in case this problem was caused by how MySQL installed itself in the first instance. Frankly, it's too bad. MySQL worked nicely with Base as long as it was running under local host. The documentation is comprehensive. I would still like to use Base as a front end to an SQL database engine - Base is nicely integrated with Writer, Calc etc. I have been using Writer in my business, am beginning to create templates, etc. which is speeding up the work. It's too bad there's something wrong with MySQL. In reflecting on this some more, I will post a "conclusions" later.

Again, I have learned at lot from your efforts to help me resolve this and I thank you.

John

P.S. I think you probably know something that you don't know you know because you're so close to it - something so obvious to you that I am walking right by it. What it is, obviously, I have no idea. You are obviously a smart, methodical, computer professional intimately acquainted with Base and MySQL who has been willing to "go the extra mile" to help someone solve a problem.

[rudolfo]

Okay, the servers table was my bad. It's needed for the Federated storage engine which is new for me, but I am sure you won't use this feature.

The whole point with the reverse lookup comes back to the fact that the Mysql server allows client connections filtered by IP addresses or by host names. A tcp connect request, which is always the initial thing that a server (no matter if web server (http) or database server) sees from the client. This request or more precisely the TCP packet that is used for this request contains only the IP addresses of both sides, but not the hostnames. Hopefully both side use the same data source to lookup hostnames or if they use separate data sources the data should at least be synchronised.

Code: Select all

  CLIENT (192.168.0.5)                                      SERVER (192.168.0.2)
 +-----------------+                                      +----------------------------+
 | hostname JOHNXP |                                      |                IBMNOTEBOOK |
 |           |     |                                      |                 hopefully  |
 |   resolves to   |                                      | 192.168.0.5 is  ^      <---- PROBLEM
 |           |     |                                      |                 |          | IS HERE
 |           V     |                                      |        reverse lookup      |
 |     192.168.0.2 |                                      |                 |          |
 |-----------------' sends Packet                         ' inspects src 192.168.0.5   |
 |         +-----------------+            +-----------------+               
 |         |src: 192.168.0.5 |  ------->  |src: 192.168.0.5 |  accepts only packets
 |         |dst: 192.168.0.2 |            |dst: 192.168.0.2 |  for IP 192.168.0.2

I hope you have mysql.exe on the IBMNOTEBOOK client computer. As I said, Base is not for network testing.

The server would either use the plain IP address (of the client = 192.168.0.5) to check against the mysql.user table. Or it uses the resolved name to do the check. I don't know about the internals of Mysql but it will probably do both checks and if either one is successful the connection request is accepted.

What do you mean with could no longer ping IBMNOTEBOOK from the server?

If I run C:\>ping TESTSERVER
I get Ping testserver [192.168.1.125] with 32 Bytes Data:
Just because I have a line with 192.168.1.125 testserver in my hosts file. The testserver computer is not running so all pings time out. The crucial part is that it actually tries to send something and doesn't simply return Unknown host!

If you get Unknown host IBMNOTEBOOK you have a typo in your lines that you added to the hosts file. Or the file is missing the final newline. Make sure that the cursor is on an empty final line if you open the hosts file with Notepad and do Ctrl-End to go to the end of the file.

Not sure if I understood ERROR 1042 Can't get hostname for your address correctly, so far. Maybe it doesn't mean that the reverse resolving process for 192.168.0.5 did return an empty answer, but that the resolving process itself had an error. With an empty answer mysql should simply proceed with the plain IP address. But if this lookup process had an error it is no wonder that the mysql server does an emergency exit at that point and immediately closes the connection.

[Anon_E_Mouse]

rudolfo,
Subsequent to my last post and prior to your last post decided to completely remove and reinstall MySQL on server (JOHNXP) in case the problem has been caused by incorrect/faulty installation. Started with instructions in MySQL ref manual, ran uninstall, deleted all MySQL data directories, deleted c:\program files\MySQL directories, searched for any stray .opt files, even checked that uninstall removed registry entry (it had), even removed PATH environmental variable. SHOULD have had a "clean" computer (at least as far as MySQL is concerned). However .....
When reinstalled (per directions beginning page 49 on Ref Manual) using mysql-5.5.10-win32.msi, the end result was that the MySQL server config wizard couldn't apply security settings. Kept getting "no authentication string" error. The config wizard just wasn't able to set those, and, kept asking me for existing passward. If used, or left blank, same error. So, had to pass (uncheck) security settings. And then, of course, you can't access the mysql database for work with user, db etc. So, as this is written no longer have a MySQL server running on JOHNXP. Am sure I can figure it out if I spend another week, month or year of my life. Could be MySQL left something (hidden files or?) so the new installation knew it had been there before. Or, also possible, in all these weeks of messing with stuff I changed something in XP that is blocking the MySQL server instance config wizard.

Re: your post,
1) Yes, MySQL is installed on IBMNOTEBOOK
2) Your schematic clearly shows what is SUPPOSED to happen, but hasn't been - it illustrated, graphically the issue.
3) Inability to have JOHNXP ping IBMNOTEBOOK happened after entries in hosts file in windows. have removed those lines anyway since they weren't working and now each can ping the other.
4) Your explanation of the issue, what is SUPPOSED to happen is very good. Unfortunately that's not what has been happening. Where the flaw is I don't know (obviously).
5) Got a chuckle out of your comment about ERROR 1042 Can't get hostname for your address. I've been seeing that for quite awhile now - bet I've seen it 50 times if I've seen it once. If you go back to the original post in this thread you will note that was the trigger for my original post.

Frankly, I would have chucked MySQL a long time ago (even though it was used on a web-site I set up for my business earlier this year - using Joomla) except for three things: 1) your continued efforts, 2) the 3600 page MySQL ref manual is one of the most comprehensive software manuals I have seen, and, 3) an inherent character flaw in me in that I never give up once I get my teeth into something - silly on my part, isn't it.

John

[rudolfo]

I hope you still have your data as csv or per mysqldump.
Actually I am out of ideas now. Though I could still bet that the problem is related with some specific configuration on the OS level.
Only two things left:

• Test your DNS cache on the WinXP box with ipconfig /displaydns You might get quite a lot of data, so better redirect the output into a file (> filename.txt appended to the command). And also run the same command after you have emptied the dns cache with ipconfig /flushdns. As far as I know valid lines from the hosts file will be immediately inserted back into the cache.
• Use the no-installer MySQL versions: Mysql 5.1 or Mysql 5.0. Unzip them, copy the my-small.ini to my.ini and start the server from the command line: bin\mysqld --defaults-file=C:\my.ini (see my other post for details). This approach should work on a Win98 box, as well. Allthough I haven't tested this. The good thing is, that once you got it runing on one machine you can copy the full mysql directory over to the other computer and start it there in the same way from the command line. Except for one (obvious) caveat: Don't copy the files when the mysql daemon is running!

[Anon_E_Mouse]

rudolfo,
1) Thank you for letting me off the hook. As long as you were willing to continue offering suggestions to fix problem, I had to keep trying your suggestions. I believe a solution is possible, but would require immersing myself in both MySQL and XP for weeks or months to exclusion of everything else. Have learned a lot from your posts though.
2) Data not is CVS file, but in base. What was transferred to MySQL database was practice data. Also, your comment about it being an OS issue may very well be correct, or not - myself I would bet either way. I just don't know.
3) Now have Base connecting to Firebird database BOTH from JOHNXP and from IBMNOTEBOOK so can continue working with Base on localhost knowing will be able to get connected later. My vision, since OOo is so nicely integrated, is that I and employees will be able to use current data (18,000+ names now running under dBase IV) with much better targeting via SQL select statements, viewable on form in Base with such features as, one simple example, one button push to "Write Letter" which will then call up Writer (or even better, template options).
4) Some conclusions about my Alice in Wonderland experience with MySQL (and delving into XP at your behest):

• A) Much software built today is as "stupid" as 20 - 30 years ago. Simple example is error messages. When was running the MySQL server config "wizard" (ha) after attempt at a re-installation I was getting error message "no authorization string" - obviously, the MySQL software was asking for something from someplace and not getting it. Was it asking itself (the MySQL server engine) or the operating system? A better error message would have been something along the lines "The server instance wizard requested an authorization string from ???? to make security settings, but, ???? did not provide one. You need to enter an authorization string in ???? by ????." Another example was the original error message "can't get hostname from your address". Why not? Who couldn't get it (MySQL? OS not providing it?). Since Firebird was obviously able to get it without any intervention on my part, what is the "fix"?
  B) Windows is a kludge - in essence it is still DOS with layers of stuff added willy nilly. Never realized that so explicitly until trying to solve this connection issue. Whether the Unix/Linux line is any better (ie, more intelligent and integrated) I don't know.
  C) Finally, "computing" has, in reality, changed very little from Babbage's original conceptual model of the 1840s. Far faster, electrically based switches yes (instead of steam powered, mechanical switching), but, other than a binary base (instead of Babbage's 10 base system) works the same way. From the programming end, Ada Lovelace's model is what we are using today at it's root.

Thank you so much for your efforts.
John

[mickjsn]

"Thanks for your post - am running 3 XP machines, 3 W-98 boxes, Professional edition, vers 2002, Service Pack 3. Just realized that when everyone talks about my.cnf file, they may be talking about my.ini file - which IS there with pointers to datadir=, basedir= etc. The link you showed to your file my.ini made me realize that. Don't know if some setting there is the problem or not. However, progress being made ... FINALLY got an error that came (at least I think it did) from mysqld.exe, not XP. Used your connection string suggested above "mysql -p -u root -h <my IP address> mysql" generated same errors "can't connect", "can't get host name for your address". However, when I used the computer name, ie. "mysql -p -u root <JOHNXP=my computer name> mysql" it came back "host 'JOHNXP' is not allowed to connect to this MySQL server" - clearly (at least I think it is clear?) this, at least, is coming from the server engine, not from XP - but maybe not. At least I am getting different errors, that's an improvement, I guess.
Anyway, I have been trying to get XP to allow access to the port and explicitly specified that XP should allow access to port 3306 by anybody.
I have the feeling I am close to resolving this."

i think you must then make a mysql repair (www.mysqlrepair.org) when there is some problem in your MySQL database.

[viswamprasad]

Guys!!!
try starting mysql with option "--skip-name-resolve"
# service mysql start --skip-name-resolve

-Viswa