Topic: mysql localhost vs local networking question

[Agent86]

Hi

Acknowledging that mysql by default is only accessible from the server / localhost.

My question is:
Is there any way to connect to a database on SME WITHOUT following HOWTO instruction for LocalNetworking or Remote Access.

In other words Can I connect to the a database via OpenOffice with JDBC connector from a local network machine using the msql.socket ?
Or is it required to set LocalNetowrking access per the HowTo in order to connect to the database ?

Secondly the HowTo indicates:

config setprop mysqld LocalNetworkingOnly no
expand-template /etc/my.cnf
sv t /service/mysqld

Shouldn't this be LocalNetworkingOnly yes ????

Please confirm this HowTo is correct for LocalNetwork Access, and if I need this for OpenOffice to connect to a database on SME

Thanks very much in advance

[janet]

Agent86

Please confirm this HowTo is correct for LocalNetwork Access

The Howto is correct, think about the logic.

...and if I need this for OpenOffice to connect to a database on SME

Yes.
If you do not enable access (to mysql), then how do you expect "a connector" to be able to access it !

[Agent86]

Agent86

The Howto is correct, think about the logic.

Yes.
If you do not enable access (to mysql), then how do you expect "a connector" to be able to access it !

I agree that is logical
I was just not sure how the connector actually works.
I thought perhaps it might work similar to some php,java applet or some other language that access the database similar to horde,joomla,gallery2 which access the mysql databases.
I saw some mention in the forums of OpenOffice non-gui,which started me thinking that maybe I was suppose to install that and the connector on the SME server:and point the local OpenOffice installation to the SME installed connector,which would then access the mysql socket locally.I don't know if that's even possible without know exactly how the connector works.
I understand now that this is not the function of the connector and that it's no different then trying to log my browser into the the mysql database LOL. I honestly had no idea how the connector worked, but thanks for clearing this up.

I just hate changing things without knowing exactly especially when the SME team indicates that for security reasons they have disabled Network, and Remote Access to mysql by default.
I figured since this was the default that there must be some workaround, that people must be useing to access the mysql databases.

I appreciate the clarification this will help me complete my OpenOffice database and connection to mysql

Is there some reason that people appear to use JDBC connection over ODBC connection ?
I want to keep it simple, what do you recommend ?

Thanks again for the response

[Agent86]

Hi

One more question about enabling local network access

I see in the forums serveral different versions of doing this:

I see in the HOWTO:
onfig setprop mysqld LocalNetworkingOnly no
expand-template /etc/my.cnf
sv t /service/mysqld


And in other forum sections:
/sbin/e-smith/config setprop mysqld LocalNetworkingOnly no
/sbin/e-smith/expand-template /etc/my.cnf
/etc/rc.d/init.d/mysqld restart

And there are some other deviants in the forums as well, but much older posts.

Anyhow I've attempted the HowTo method and cannot connect to mysql with OpenOffice connecting to MYSQL using JDBC connector.

I cannot telnet to the server via local network using telnet (serverIPaddress 3306)

I have created a database with myphpadmin and user for the database etc. and cannot connect to it from local network.

I'm sure I'm just missing something simple, but I can't seem to figure out exactly what

Any advise on this would be very appreciated
Thanks

[janet]

Agent86

The wiki howto is the most current, the main difference being the use of sv to control services.
Search these forums more if you want more details as it has been answered before eg sv versus svc versus /etc/init.d versus even other options.

The /sbin/e-smith/ part at the start of commands just specifies the full path. From about sme6.0 onwards, there was a link/shortcut in place that meant you did not need to enter the /sbin/e-smith/ part any more.
Done for ease of use and expediency.


It works for me using ODBC connector.
I'm guessing your issue is that you have probably not added the % user in your mysql db when setting up user access permissions. You need this so that users (other than local host users) can access the db. Read the mysql online tutorial about setting up user permissions etc.
eg in the grant command you add two entries one for
'user'@'localhost'
and one for
'user'@'%'
Something like, depending on exactly which command you prefer/need to issue

mysql> grant all on tablename.* to 'user'@'localhost' identified by 'password';
mysql> grant all on tablename.* to 'user'@'%' identified by 'password';

[Agent86]

Hi

I've read thru the mysql site, and openoffice site,

Still no connection, I just don't know enough to get this working.
It's very complicated to me; and since I may or may not be taking the correct procedures, I can't confirm it because I don't even know enough about it to do a simple test of the mysql connection.I can only assume I'm doing something wrong. But as I gaze into the screen for serveral days trying on read and figure out why I cannot connect I slowly fall asleep in confusion.

Then another day goes by and I stay up late reading even more links to other sites from sites from sites from sites and reading other similar problems people are having that may or may not relate to the problem I'm trying to solve. Only to find that perhaps these other posts all over the world have nothing to do with the problem I'm having but appear to have similar symptoms, but are actually different problem altogether since they are using a different OS with mysql.

This is so much fun and SME gives me plenty to do. You will never run out of things to read with SME thats for sure.

Happy hacking, I think I'll have to give up on my openoffice project since I completed my forms and database but after doing so realized that openoffice will only work on one machine and one user at a time.

So considering I have a SME server that in theory should be able to allow me to create a database and some simple tables so that I can connect to it with openoffice and create a simple form to use for inventory.

I'll have to tackle this project some other time.

Thanks to all for the help

I look forward to seeing future posts to see if anyone can connect to mysql from local network and how they did it, or what commands they used to test the connection from the local network.

Happy Hacking

 

[janet]

Agent86

Are you going to tell us any error messages you get ?

I look forward to seeing future posts to see if anyone can connect to mysql from local network and how they did it, or what commands they used to test the connection from the local network.

Well I essentially already answered that, perhaps you chose to ignore what I said ???

I never used JDBC so cannot comment on that.
I can read mysql tables using the mysql ODBC connector & Excel spreadsheet, both locally on workstations on the network, and remotely (if appropriate mysql access is enabled).

As advised you do need to enable appropriate access permissions for users who are not on the localhost (ie not on the sme server). You do this using commands like:
mysql> grant all on tablename.* to 'user'@'localhost' identified by 'password';
mysql> grant all on tablename.* to 'user'@'%' identified by 'password';

(plus of course the other lead in and lead out mysql commands normally required to set permissions).

The 'user'@'%' indicates a specific username at anyhost ie anyworkstation or location.

Did you add this second permission to your table to allow external users to access the db ?

[cactus]

mysql> grant all on tablename.* to 'user'@'localhost' identified by 'password';
mysql> grant all on tablename.* to 'user'@'%' identified by 'password';

That might be OK for testing, but you are better of setting only permissions the user really need, see for the possible options the MySQL Manual: http://dev.mysql.com/doc/refman/4.1/en/grant.html

Besides that it is most off the times not the table name but the database name you are assigning privileges to using above syntax, the wildcard (*) symbol is not supported in operations on tables (to specify columns a different syntax is required):

Code: [Select]

GRANT SELECT INSERT UPDATE DELETE on database.* ...against:

Code: [Select]

GRANT SELECT (col1), INSERT (col1,col2) ON database.table TO 'someuser'@'somehost';

[Agent86]

Thanks to all

mary- this command really helps me figure this out.

cactus-thanks for mentioning of database command

I don't understand why I could not get this setup using myphpadmin which was suppose to be easier.

Thanks again.

[janet]

Agent86

So does that mean you have got it working now ?

I don't understand why I could not get this setup using myphpadmin which was suppose to be easier.

Probaby because you were not using phpmyadmin correctly and did not really understand what you were doing.

[Agent86]

mary- very likely

Thanks to everyone for the responses

One last question if you could be so kind, my question is all the way at the bottom of this text:

Regarding these commands

config setprop mysqld LocalNetworkingOnly no
expand-template /etc/my.cnf
sv t /service/mysqld

and

config set mysqld service access public status enabled TCPPort 3306
signal-event remoteaccess-update
signal-event reboot

I assume this is SME specific commands ?

Acknowledging that SME mysql and other mysql servers must first be told to allow local network access and/or remote access.
And Acknowledging that SME msql and other mysql servers users must be added and privilege given to that user from anywhere for that database and/or table.

The mysql site indicates to edit the etc/my.cnf file but all versions of the file seem different. The methods for allowing networking access appears different for different versions.
For example:
One post on mysql site says to change the my/cnf to uncomment the (skip-networking) in order to enable network access.

But some versions of the my.cnf file say this:

Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address      = 127.0.0.1

This one does not have the skip-networking comment
Yet Another post on mysql site indicates:

I had problems connecting to MySQL over tcp/3306
on debian stable, localhost. It was not enabling networking
in my.cnf, but it was a missing entry in /etc/hosts.allow

I added the following to /etc/hosts.allow:

mysqld 127.0.0.1

I don't understand the point of the mysql.socket and the 127.0.0.1 subject.

Please advise.
Thanks very very much for any enlightenment on this.

[cactus]

The mysql site indicates to edit the etc/my.cnf file but all versions of the file seem different. The methods for allowing networking access appears different for different versions.
For example:
One post on mysql site says to change the my/cnf to uncomment the (skip-networking) in order to enable network access.

But some versions of the my.cnf file say this:This one does not have the skip-networking comment
Yet Another post on mysql site indicates:
I don't understand the point of the mysql.socket and the 127.0.0.1 subject.

Please advise.
Thanks very very much for any enlightenment on this.

By this time you should now (as you have been around for quite some time), that SME Server is just linux and handles like every other linux system/architecture, but to facilitate the server administration configuration files are templates and a internal configuration database is errected so a lot (almost all) administration can be done using the server-manager instead of through command line instructions or directly modifying configuration files.

Since SME Server therefore is not a plain/vanilla installation configuration files differ, you should certainly not expect configuration files to be basic and simple as SME Server does a lot more tasks than a so called normal setup used in documenting issues, most of the times they are a simple and unlikely state to find on production machines.

So to go short, the MySQL manual is a good source of information and all information in there is true, but due to SME Server implementation it is not always the plain sailing the pretend it to be.

Your best bet is to come here and ask like you did...

Now for the latter part of your question:

Yet Another post on mysql site indicates:
I don't understand the point of the mysql.socket and the 127.0.0.1 subject.

MySQL can run using a socket and using a port two different ways of running a service, they all have their specifics. The IP address you specified is the equivalent for the localhost hostname.

From the questions you raise I think you are way in over your head and mixing all sorts of stuff, now you are certainly talking about a debian system and accessing mysql there locally AFAICT.

The /etc/hosts.allow file allows hosts to contact your server for certain services, in this case mysqld on the 127.0.0.1 (localhost) IP address, yes strange but to connect to MySQL or other services you need to allow this explicitly. This still means no external or internal network access, only access from the host itself. On SME Server all required configuration settings are handled internally by setting the correct database values and running the proper (set of) events, other arcitectures require you to do this all manually.

Unfortunately this forum only supports SME Server and you should ask for other linux architectures on their support channels.

I suggest you (re)read the manuals and if you have any questions please raise them here, but be sure to try and implement the advice given here: http://wiki.contribs.org/SME_Server:Community:Forum especially the section on how to formulate your question(s) in order to get the answer you are after, as I find it very hard to provide you with the proper answer(s) to your questions.

[Agent86]

Hello,

Thanks for the response
But Why ? Why even mention this ?

From the questions you raise I think you are way in over your head and mixing all sorts of stuff, now you are certainly talking about a debian system and accessing mysql there locally AFAICT.

It only rightfully prompts a response such as:

It's obvious from your statement that you do not understand the question.

I'll try to clarify for you;and I appreciate your concern, however this is not over my head; and is not mixing all sorts of stuff, and I am NOT talking about debian.

As mentioned above these valid points have been mentioned on the mysql site and in the msql reference manual regarding starting mysql and uncomment skip-networking.
The solution by one poster on the msql site, who appears to have a debian distro mentioned that his file entries were missing entries.
This raised a question about the mysql socket and the my.cnf file.

When considering the SME HowTo I noticed there is a part of the SME command to the my.cnf file as well.

I assume I can still change the SME my.cnf file manually if I wanted to enable network or remote access as directed in the mysql manual ?

Acknowledging that I would still have to setup the proper msql user and db privileges as well.

I additionally acknowledge that this post is in the General Section for Discussions about contribs.org, Linux in general, and other topics that don't belong in the other forums.

Funny, from this link:
http://wiki.contribs.org/SME_Server:Community:Forum
I noticed no instruction regarding giving answers LOL, only how to ask questions but everyone should acknowledge the main text listed in this sections:

The forums are the places where users can help each other and exchange experiences and knowledge.

I do appreciate the responses regarding the socket etc. And don't want to sound ungrateful, but these forums seem to just be a place for people to just slap you around.

I've realized a while back that I'm on my own and really no need to ask here, I've been doing fine learning without any help from the forums, but just wanted to give it a try since getting answers shortens the learning curve time frame.

I've not really asked any questions here in a while.
I figured I would be better off answering questions for people instead of asking since I noticed a lot of word slapping here and it appears to be getting worse.

I would surely not accept treatment like this in person, so why would I accept it from anyone in any forums.
 
But I have hope for the SME forums still.
Perhaps A How To Answer Questions would be a good idea in order to avoid any confusion.
Why not a poll to see if anyone would like a How To Answer Questions Section ?

Or a poll to put a feedback system in place
Such as an answer rating system,like on ebay.
Did you find the answer helpful 5 stars,
Would you recommend this answer to someone no/yes

This could tell you how you are doing and certainly would not allow you to lie to yourself about it too.Likely many don't really care how they are doing either LOL.
But a poll would be nice to see if the SME community would even want something like this.

Anyhow way off topic now, but Happy Hacking.
Thanks again.

[cactus]

When considering the SME HowTo I noticed there is a part of the SME command to the my.cnf file as well.

I assume I can still change the SME my.cnf file manually if I wanted to enable network or remote access as directed in the mysql manual ?

Yes you can, but since you have a history in this forum long enough to not make you a newbie anymore, you should already have been aware of the fact that SME Server handles configuration files a little different than normal linux systems (as well as being told earlier in this thread).

Why do you really want to modify your configuraiton files directly while that will only result in problems in the long run as those changes will get lost when you update, change other parameters as the configuration files are regenerated?

It's obvious from your statement that you do not understand the question.

Perhaps your question is not clear to me...

The forums are the places where users can help each other and exchange experiences and knowledge.

Like I usually do and am doing in this thread as well IMHO.

I've realized a while back that I'm on my own and really no need to ask here, I've been doing fine learning without any help from the forums, but just wanted to give it a try since getting answers shortens the learning curve time frame.

Maybe because you have bad experiences with this, ever considered it not being the community but you also being a factor in it?

I figured I would be better off answering questions for people instead of asking since I noticed a lot of word slapping here and it appears to be getting worse.

I am not trying to word slap you... but perhaps I should indeed stop answering your questions since you do not seem to appreciate my answers and I have mistakenly tried to answer you again after a long time. Luckily there are a lot of people who appreciate my contributions to the community.

[Agent86]

Why do you really want to modify your configuration files directly while that will only result in problems in the long run as those changes will get lost when you update, change other parameters as the configuration files are regenerated?

I didn't really say that I wanted to modify the configuration files directly, but only that I wanted to know how to. This is because I'm considering installing a Ubuntu Server.

Maybe because you have bad experiences with this, ever considered it not being the community but you also being a factor in it?

Sure I can consider this ?
Hmmm Perhaps it's me?
Perhaps it's the way I asked the question ? or perhaps the tone of the question that prompted the rude responses? I'll have to re-inspect my question now to see if anything there was rude or prompted a rude response from anyone ?

Sort of retorical here, but anyhow.
Of course it's me, thats the whole point since it is obviously not the question itself that is prompting rude response;and the wording or tone of the questions do not appear to have reason for rude responses so, YES it must be me.
So the question is why ? I have not deserved any treatment like this nor does anyone else.
And it's not the whole community just a few that do this and it also happens in a couple other forums, but not the whole community.

I am not trying to word slap you... but perhaps I should indeed stop answering your questions since you do not seem to appreciate my answers and I have mistakenly tried to answer you again after a long time. Luckily there are a lot of people who appreciate my contributions to the community.

Not true I do appreciate the contribution, and I do appreciate the answers very much.But not at the expense of being belittled or talked down rudely.
Everyone is aware of the HowTo's and Everyone is Aware of the Manual, and Google for crying out loud.If a question is asked here by me it's usually after 3 days or a week of reading and researching.Sometimes I just want confirmation that what I've learned or read is accurate ? I don't have the means of multiple servers to try things out and sometimes scared to perform tasks on my server because if I break something I don't know for sure I can fix it.Which is why I'm setting up another server to play with.But I am continuing to learn and feel confident that I will be able to fix any problem on my server no matter how problematic.
Anyhow I do appreciate the responses to be clear.
I'll end with that, and hope to contribute in the forums myself in the near future.