Help for query
Hi, all The version of Mysql is 4.0.15 on Solaris 9. I am in such a situation. There are 2 tables something like these: Table A: -- location|timestamp | other fields --- Table B location|timestamp | other fields - (location. timestamp) make the 'primary key' for each table. The task is that: the locations are the same, given a timestamp from table A, I need to find the record in table B which has the closest timestamp as the given one in table A. I checked the book and research the internet, but didn't find a hint or solution yet. Could any one give me a hint please? Thanks in advance. Xiaobo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help for query
Yes, you're right, Jerry. Thing is that I can NOT use 'min_t' in 2) query, but have to plug in the actual value from 1) query. This isn't what I want. If I tried this: 1) create temporary table tbl_min as (select min(abs(Ta - timestamp)) as min_t from B; so tbl_min is like: min_t | 0.00012 | - 2) then, I say: select min_t, * from tbl_min, B where (timestamp + min_t) = Ta or (timestamp - min_t) = Ta; then I will get the record as: -- min_t | other fields --- you see, the field 'min_t' isn't necessary there. Wait, if I say: select * from tbl_min, B where (timestamp + min_t) = Ta or (timestamp - min_t) = Ta; Yes, I got the result without 'min_t' in it. But this solution still used more than 1 query and used a temporary table to hold the imtermidiate value. Any better solution? Thanks. Your first query returns a scalar (single value), right? You can put its value into a variable, and use that in the second query. It's not exactly what you wanted, but it will work without external programming. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Xiaobo Chen [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 26, 2006 10:09 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: Help for query I found if I divided into 2 steps, I will find the record in table B: Ta - the given timestamp from table A; 1) select min(abs(Ta - timestamp)) as min_t from B; 2) select * from B where (timestamp + min_t = Ta) or (timestamp - min_t = Ta); But, how can I make these 2 steps into 1 query? Thanks. Xiaobo Hi, all The version of Mysql is 4.0.15 on Solaris 9. I am in such a situation. There are 2 tables something like these: Table A: -- location|timestamp | other fields --- Table B location|timestamp | other fields - (location. timestamp) make the 'primary key' for each table. The task is that: the locations are the same, given a timestamp from table A, I need to find the record in table B which has the closest timestamp as the given one in table A. I checked the book and research the internet, but didn't find a hint or solution yet. Could any one give me a hint please? Thanks in advance. Xiaobo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help for query
I found if I divided into 2 steps, I will find the record in table B: Ta - the given timestamp from table A; 1) select min(abs(Ta - timestamp)) as min_t from B; 2) select * from B where (timestamp + min_t = Ta) or (timestamp - min_t = Ta); But, how can I make these 2 steps into 1 query? Thanks. Xiaobo Hi, all The version of Mysql is 4.0.15 on Solaris 9. I am in such a situation. There are 2 tables something like these: Table A: -- location|timestamp | other fields --- Table B location|timestamp | other fields - (location. timestamp) make the 'primary key' for each table. The task is that: the locations are the same, given a timestamp from table A, I need to find the record in table B which has the closest timestamp as the given one in table A. I checked the book and research the internet, but didn't find a hint or solution yet. Could any one give me a hint please? Thanks in advance. Xiaobo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why didn't I insert small number into table?
Hi, all I have a table like this: +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | id_freq | int(11) | | PRI | 0 | | | id_theta | int(11) | | PRI | 0 | | | es | double | | | 0 | | +--+-+--+-+-+---+ the values for 'es' is very small, like 10^-8. When I checked the result after running my code, the column for 'es' are still '0's. Could anybody give a hint why this occured and how to avoid this? Thanks in advance. Xiaobo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Just need script for creating tables
Hi, all If I use 'mysqldump', I will get the script to create the tables and those 'insert' statements to insert the data. I am wondering if I just want the first part, i.e, the script to create the table, is there a command for this end? (I could copy paste the part from 'mysqldump' but it's not what I want to do.) Cheers. Xiaobo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
simple query
Hi, all I have a table which has a column with time. It's format is like -00-00 00:00:00 (default value). I want to get the minimum and maximum values for this cloumn. Obviously, -00-00 00:00:00 isn't the minimum value I want. What I did for the minimum time, suppose the column name is ctime, the table name is tbl_a: select ctime form tbl_a where ctime-00-00 00:00:00 order by ctime limit 1; Using this query, I am able to get the minimum time value. But what is the query for max time value? I guess it's something about reverseing the order by, but I couldn't find it. Could anyone please give me a hand? Thanks a lot. Xiaobo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: jdbc:mysql
Not sure yet. Could it be that you're behind the router? And you might need to redirect the traffic to the model to the IP of your PC and you're PC's IP should be set static so that next time when you reboot, it won't change. Xiaobo Hi, I have a problem connecting to MySQL. I could connect to the database with this sentence: jdbc:mysql://localhost:3306/db but not with this sentence, with the same IP that the localhost: jdbc:mysql://192.xxx.xxx.xxx:3306/db What's wrong? Thanks for all, Amaia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with transfer databases from different Mysql versions
Hi, all I have installed a portal server and which has Mysql 4.0.15-nt with it. Before I installed this server, I have Mysql 4.1 in my PC. But thing is that they will conflict and I have to delete the old Mysql service. So I used mysqldump to export the databases from the 4.1 version to the sql files. But when I use source to retrieve those database in the 4.0.15-nt version, it reports errors. Can anyone tell me how I should tackle this problem? I am also concerned what the -nt here means? Thanks a lot. Xiaobo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Type conversion from Mysql to Oracle
Hi, folks I am trying to convert some Mysql database into Oracle. But their types are not exactly match. Could any one give a hint? Thanks in advance! Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Type conversion from Mysql to Oracle
Hi, folks
First thank all you who replied to my question. I should be more specific
with my question.
In mysql database table, there is some thing like in the 'create table
syntax':
fieldname enum('yes','no') default null
and
fieldname datetime default null
I have no idea about the first one. For the second, I am not familiar with
the 'DATE' type in Oracle and its formatting. In the Mysql table, the
format is like '-MM-DD HH:MN:SS'.
What I am doing is trying to duplicate all those tables from Mysql
database into the Oracle database and I am trying to do that by writing a
small java program. The issue I am facing now is that I need to get
correctly the 'create table syntax' for Oracle and trying to keep the
formats of each column as close as possible to that in the Mysql table.
I couldn't find a FREE tool to do this from Mysql to Oracle (not the other
direction). So I decided to write it up.
Thanks again.
Xiaobo
Hi,
I am trying to convert some Mysql database into Oracle. But their types
are not exactly match. Could any one give a hint?
What types do you need to convert?
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Faculty of Computer Science
Dalhousie University
Halifax, Nova Scotia
Canada
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql and Oracle
Hi, all I have a questions: I installed MySQL and Oracle 9i in the same XP box, is there any interference with each other? Thanks in advance. Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to copt table not whole database
Thank you Shawn. What I originally thought is that if there is any command like 'sqldump' to copy a few tables of a database. Xiaobo Xiaobo Chen [EMAIL PROTECTED] wrote on 01/16/2006 03:33:36 PM: Hi, all How should I copy a table of a database? Thanks in advance. Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada Do you want the data or the structure too? Just the data: CREATE TABLE newtable SELECT * from oldtable; Data and the original structure (no foreign keys): CREATE TABLE newtable LIKE oldtable; INSERT newtable SELECT * FROM oldtable; Both forms of CREATE TABLE documented here: http://dev.mysql.com/doc/refman/4.1/en/create-table.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to copt table not whole database
Dan, thank you very much. That's what I want. Xiaobo Xiaobo, Using mysqldump, you can specify which tables to copy. The man for mysqldump is as such: mysqldump [options] [dbname [tablename]] Say db 'db' has tables x, y, and z in it. You only want to copy table x. mysqldump db x x.sql(or whatever you want to name it). Dan Thank you Shawn. What I originally thought is that if there is any command like 'sqldump' to copy a few tables of a database. Xiaobo Xiaobo Chen [EMAIL PROTECTED] wrote on 01/16/2006 03:33:36 PM: Hi, all How should I copy a table of a database? Thanks in advance. Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada Do you want the data or the structure too? Just the data: CREATE TABLE newtable SELECT * from oldtable; Data and the original structure (no foreign keys): CREATE TABLE newtable LIKE oldtable; INSERT newtable SELECT * FROM oldtable; Both forms of CREATE TABLE documented here: http://dev.mysql.com/doc/refman/4.1/en/create-table.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- Master of Applied Computer Science Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
retriving password
Hi, all I am trying to retrieve the password for one user in my database. When I say this: select password from user; The returned value is not human readable. Any one know how to converted it to readable format? Thanks in advance. Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to copt table not whole database
Hi, all How should I copy a table of a database? Thanks in advance. Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
temporary table issue
Hi, all I am trying to use this with error: drop temporary tabel temp_a if exists 'temp_a'; it said syntax error. Could anybody tell me the right syntax? I didn't find the answer after googling a while. Thanks in advance. Xiaobo -- Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary table issue
Hi, Jigal
Thanks a lot. It works.
Xiaobo Chen wrote:
Hi, all
I am trying to use this with error:
drop temporary tabel temp_a if exists 'temp_a';
it said syntax error.
Try:
DROP TEMPORARY TABLE IF EXISTS `temp_a`;
('table' instead of 'tabel'; table name only once; backticks around
table name instead of quotes)
http://dev.mysql.com/doc/refman/5.0/en/drop-table.html
Regards, Jigal.
--
Faculty of Computer Science
Dalhousie University
Halifax, Nova Scotia
Canada
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequential id
Try this: CREATE TABLE tablename ( `id` mediumint(9) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '' PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Greetings, I have a bunch of names and addresses that I am adding to MySql database. I would like to automatically assign a unique sequence number to each person. Is there a way to do this easily? idname 1 bob jones 2 larry smith 3 henry rogers Thanks for you help, Joe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Master of Applied Computer Science Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequential id
Try this: CREATE TABLE tablename ( `id` mediumint(9) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '' PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Greetings, I have a bunch of names and addresses that I am adding to MySql database. I would like to automatically assign a unique sequence number to each person. Is there a way to do this easily? idname 1 bob jones 2 larry smith 3 henry rogers Thanks for you help, Joe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sequential id
Oops, forgot ',' at the end of the 3rd line. Try this: CREATE TABLE tablename ( `id` mediumint(9) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '' PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Greetings, I have a bunch of names and addresses that I am adding to MySql database. I would like to automatically assign a unique sequence number to each person. Is there a way to do this easily? idname 1 bob jones 2 larry smith 3 henry rogers Thanks for you help, Joe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Master of Applied Computer Science Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Master of Applied Computer Science Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: neet setup tips for remote mysql access
Could it be the firewall? You might want to open the port 3306 for external access(or both). In my XP, I did this: Firewall -- Advance-- Network Connection Settings --Select Local Area Connection -- Click Setting, then you might want to 'add...', from there filling your IP, 3306 for both ports. Hope this will work for you. Good luck. X.Chen Dear List, I have mysql server and php apps on the same machine, they worked very well. The machine's OS is win2k pro Mysql 5.0.15 Then I access the db from local network area 192.l68 And it worked fine although I have to reset my account privileges.. Then I tried to access it remotely by domain name, it stop working even I can ping the server with the domain name: F:\home\elimmysql -h myDomain.com -u elim -p Enter password: ** ERROR 2003 (HY000): Can't connect to MySQL server on 'myDomain.com' (10061) Please help me for the general steps of setting this up. Thanks a lot! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot get database connection
Have you tried to put the port 3306 after the host? I assume you are in
the localhost.
Not sure if this is the place to ask but hopefully someone can help me
here. I am setting an application system and have successfully setup
MySQL 5.0.15 and Tomcat 5.5.12. However, I cannot seem to get a
connection object from my servlet and for the life of me, I can't figure
out why. Here is what I have done:
Downloaded mysql-connector-java-3.1.11-bin.jar into
$CATALINA_HOME/common/lib
Added mysql-connector-java-3.1.11-bin.jar to my classpath
and I created the following servlet, TestSQL:
import java.sql.*;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.naming.*;
public class TestSQL extends HttpServlet {
private Connection conn = null;
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException {
response.setContentType(text/html);
PrintWriter out = response.getWriter();
try {
if (conn == null) {
Class.forName(com.mysql.jdbc.Driver).newInstance();
connW =
DriverManager.getConnection(jdbc:mysql://localhost/hostname, id,
password);
}
} catch (Exception e) {
e.printStackTrace();
}
if (conn != null) {
out.println(Connection read successful);
} else {
out.println(Cannot get connection);
}
}
}
I keep getting Cannot get connection message in my servlet. I tried
connecting to the database (locally) using mysql client with id just
to make sure the account works and it does. I setup a similar system
before and was able to connect to the database successfully so I can't
figure out what's wrong.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Master of Applied Computer Science
Faculty of Computer Science
Dalhousie University
Halifax, Nova Scotia
Canada
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot get database connection
I am not sure if you're trying to connect from outside of your office newwork. If you try 'telnet' using 3306 (forgot the syntax, check the Mysql site)outside your office network and if you couldn't get through,(otherwise you will get some garbage characters) then you can't connect to the database from outside. I had once had such connection issue. If that will be your case, I guess you have to ask admin for help, it's not mysql at all. If your database is on a XP box, you need to create a service and open the 3306 port there (assuming you have firewall on it, otherwise you shouldn't have this issue.) Good luck. Xiaobo Chen wrote: Have you tried to put the port 3306 after the host? I assume you are in the localhost. Yes, I am trying to connect in localhost. I did 'nmap localhost' and port 3306 is open and I can connect to mysqld with mysql client. I will try putting 3306 after the host when I get in the office tommorrow but I doubt that is the problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Master of Applied Computer Science Faculty of Computer Science Dalhousie University Halifax, Nova Scotia Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to adjust the row id after modifying the tabel
Hi, all
I follow the example:
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
The thing is that after delete one of them like this:
delete from animals where id=4;
how can I have the 'id' to be continuous. Now it will be like:
1
2
3
5
6
Or shall I create the table differently at first? If so, how?
Thanks for your help in advance.
Xiaobo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access control for mysql database
Hi, all I am trying to find out how to check which user is connecting to the server from which host. I have a database, data_A; I wrote a simple java code to connect this database as user 'root', then it just waits there for input from the keyboard. Before I type anything, it should keep connecting to the database. But when I type 'select current_user()' in mysql prompt, it always says: [EMAIL PROTECTED] Can any one help me figure out this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to add a host so 'root' can connect to the server from it
I have a question related to connection control. If I want to connect to one database A in the server from another computer. I am using 'root' and it password. But how shall I add the host in which table of which database (mysql?) so that the user 'root' can connect to the database? A little explaination is that, I used 'root' in a java program which use JDBC and tried to connect a database A in the server. And the program is run in a XP machine. In that XP machine, there is no user called 'root'. I read through this link: http://dev.mysql.com/doc/refman/5.0/en/connection-access.html I still didn't know how to do it. Anyone can help me? Xiaobo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to add a host so 'root' can connect to the server from it
Thank so much for the detailed explaination. I do appreciate it. It's more clear now. But I still have a question: I do see 'root' after: select user,host from mysql.user; Then I did this as you said: GRANT SELECT ON mydatabase.* TO [EMAIL PROTECTED] IDENTIFIED BY 'mypassword'; then I issue: select * from mysql.user where user='root'; I found the select previlege is still 'N'. Besided this, how do I know 'mydatabase' from those tables in 'mysql' database that 'mydatabase' is allowed to be connected by 'root' from the IP. I am confusing here because the 'user' table only give the association betweem 'host' and user 'root' in the Mysql server. But where is the database association? wait a minute, yes, I see. When I issue: select * from mysql.db where user='root'; I did see the association and the SELECT_priv is 'Y' there. But, in the java program running in the local XP machine still can NOT connect to the database existing in the other Solaris machine!! I tried in the local XP machine: telnet theserver 3306 I failed. I guess this is why I can not connect to the server. Could be it possible that the Solaris machine deny any request from the PC to the port 3306? I can ssh to the server, or using winscp. I am really confused here. Is it a mysql issue or the system configuration issue on the server? Btw, I can run the same java program from other machine in Solaris system to connect the database as the user 'root'. Does this imply that it's administration issue? Thanks for your kind help and patience. Xiaobo Xiaobo Chen [EMAIL PROTECTED] wrote on 11/03/2005 02:54:16 PM: I have a question related to connection control. If I want to connect to one database A in the server from another computer. I am using 'root' and it password. But how shall I add the host in which table of which database (mysql?) so that the user 'root' can connect to the database? A little explaination is that, I used 'root' in a java program which use JDBC and tried to connect a database A in the server. And the program is run in a XP machine. In that XP machine, there is no user called 'root'. I read through this link: http://dev.mysql.com/doc/refman/5.0/en/connection-access.html I still didn't know how to do it. Anyone can help me? Xiaobo You are correct in saying that the XP machine doesn't have a user called 'root'. The fact that your database server's operating system (I assume you are hosting your database on a LINUX or UNIX server) has a user called 'root' is only confusing. The only 'root' you need to worry about is the one defined as a user WITHIN MySQL. run this query SELECT * from mysql.user; Can you see a user called 'root' in the results? There may be other users listed there, too. MySQL security is based mostly on the values of the columns `Host` and `User`. Look at part of a sample `user` table: localhostselect Host,User from mysql.user; +---++ | Host | User | +---++ | % | odbctest | | 192.168.% | sgreen | | % | slave | | localhost | ChartReader| | localhost | cpwapp2| | localhost | dataimporter | | localhost | datareader | | localhost | odbctest | | localhost | root | +---++ 8 rows in set (0.03 sec) For this server, the user 'root' can only log in from the local machine (the computer running the MySQL daemon). Why? That is the only value allowed by its `Host` entry. The user 'sgreen' cannot login from the local machine. However, that user can try to login from any machine on the 192.168.xx.xx subnet. The user 'odbctest' can login to the MySQL server from either the local machine or from any outside address. Here are some valid login combinations: -- Username - location of that user -- sgreen - 192.168.1.17 odbctest - 192.168.1.17 root - localhost (from the machine hosting the MySQL server) Here are some invalid (disallowed) login combinations: -- Username - location of that user -- sgreen - 10.10.1.45 root - 192.168.1.1 cpwapp2 - 10.10.1.45 You manage the contents of the `user` table (and a few others, too) with the GRANT command and the REVOKE command. If a user attempts to login from an address they do not have permission to use (you limit the usable addresses with the GRANT statement you used you define the user account within MySQL) they will not be able to connect to the MySQL server. For example, *I wanted to create a new user account for the login 'idiotuser' *AND only allow this login to select data from any table in the `safety` database (and no others) *AND their login password is to be 'dunce' *AND I only want 'idiotuser' to be able to connect from one machine (IP address
Re: How to add a host so 'root' can connect to the server from it
Thanks for your speedy reply. I already turn off the firewall in the XP machine. I have no idea about the system sonfiguration on the Solaris machine. I did this experiment as well: I installed Mysql 4.1.5 on XP machine, then use the same java program to connect to the 'test' data base from the installation. It works because I didn't network to anywhere I guess. Maybe, I should bring this to the system administrtor. It looking like a networking issue. Again, thank you very much for your patience and help. Xiaobo Answers intermixed. See below Xiaobo Chen [EMAIL PROTECTED] wrote on 11/03/2005 04:28:08 PM: Thank so much for the detailed explaination. I do appreciate it. It's more clear now. But I still have a question: I do see 'root' after: select user,host from mysql.user; Then I did this as you said: GRANT SELECT ON mydatabase.* TO [EMAIL PROTECTED] IDENTIFIED BY 'mypassword'; then I issue: select * from mysql.user where user='root'; I found the select previlege is still 'N'. Besided this, how do I know 'mydatabase' from those tables in 'mysql' database that 'mydatabase' is allowed to be connected by 'root' from the IP. I am confusing here because the 'user' table only give the association betweem 'host' and user 'root' in the Mysql server. But where is the database association? wait a minute, yes, I see. When I issue: select * from mysql.db where user='root'; I did see the association and the SELECT_priv is 'Y' there. That's correct, the `user` table controls GLOBAL permissions. The `db` table controls database-specific permissions (there can be multiple databases on any server). But, in the java program running in the local XP machine still can NOT connect to the database existing in the other Solaris machine!! I tried in the local XP machine: telnet theserver 3306 I failed. I guess this is why I can not connect to the server. Could be it possible that the Solaris machine deny any request from the PC to the port 3306? I can ssh to the server, or using winscp. There are several possibilities here: XP is denying outbound connections to your Solaris machine (quite likely) Solaris is denying connections from your XP machine (not likely) Firewalls, routers, or proxy servers between the XP and Solaris machines are blocking the connection attempt. If you were able to connect to the MySQL server, your MySQL error would say that you could not authenticate not could not connect I am really confused here. Is it a mysql issue or the system configuration issue on the server? I think it's a system configuration issue on the XP machine. I believe the XP firewall is getting in your way. Btw, I can run the same java program from other machine in Solaris system to connect the database as the user 'root'. Does this imply that it's administration issue? Network administration, not MySQL administration (yet). Thanks for your kind help and patience. Xiaobo My pleasure! Shawn Green Database Administrator Unimin Corporation - Spruce Pine previous responses snipped -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to add a host so 'root' can connect to the server from it
Sorry, Sean, I think I forgot in the emails to you to mention that the MySQL server is running on a Solaris machine, I am trying to connect the database from a XP machine which is the one I am using. I tried the same program in other Solaris machine, it works fine. I also noticed that the other Solaris machines are in the same subnet as the one in which Mysql server is running, all of them have 129.173.23.*, but the XP machine is different: 129.173.105.*, could it be possible the firewall on the Solaris system (I already turned off the firewall on the XP when I tried to connect to the mysql server.) block the connection from the XP to the port 3306 though I can use ssh or putty, winscp from the XP machine to remotely log in those Solaris machines. Again, thank you very much for your time and patience. I do appreciate it. Xiaobo Its been a while since i've dealt much with MySQL permissions, but do you need to explicitly state 'localhost' as the machine in some circumstances. The other thing i thought of is a guess, as i don't program Java, but have had an analogous problem using Perl. It it possible that Java is dealing with the passwords using the old password scheme and the server has the passwords in the new scheme (or the other way around)? Just a couple guesses that are probably incorrect. On Thursday 03 November 2005 16:02, [EMAIL PROTECTED] wrote: Answers intermixed. See below Xiaobo Chen [EMAIL PROTECTED] wrote on 11/03/2005 04:28:08 PM: Thank so much for the detailed explaination. I do appreciate it. It's more clear now. But I still have a question: I do see 'root' after: select user,host from mysql.user; Then I did this as you said: GRANT SELECT ON mydatabase.* TO [EMAIL PROTECTED] IDENTIFIED BY 'mypassword'; then I issue: select * from mysql.user where user='root'; I found the select previlege is still 'N'. Besided this, how do I know 'mydatabase' from those tables in 'mysql' database that 'mydatabase' is allowed to be connected by 'root' from the IP. I am confusing here because the 'user' table only give the association betweem 'host' and user 'root' in the Mysql server. But where is the database association? wait a minute, yes, I see. When I issue: select * from mysql.db where user='root'; I did see the association and the SELECT_priv is 'Y' there. That's correct, the `user` table controls GLOBAL permissions. The `db` table controls database-specific permissions (there can be multiple databases on any server). But, in the java program running in the local XP machine still can NOT connect to the database existing in the other Solaris machine!! I tried in the local XP machine: telnet theserver 3306 I failed. I guess this is why I can not connect to the server. Could be it possible that the Solaris machine deny any request from the PC to the port 3306? I can ssh to the server, or using winscp. There are several possibilities here: XP is denying outbound connections to your Solaris machine (quite likely) Solaris is denying connections from your XP machine (not likely) Firewalls, routers, or proxy servers between the XP and Solaris machines are blocking the connection attempt. If you were able to connect to the MySQL server, your MySQL error would say that you could not authenticate not could not connect I am really confused here. Is it a mysql issue or the system configuration issue on the server? I think it's a system configuration issue on the XP machine. I believe the XP firewall is getting in your way. Btw, I can run the same java program from other machine in Solaris system to connect the database as the user 'root'. Does this imply that it's administration issue? Network administration, not MySQL administration (yet). Thanks for your kind help and patience. Xiaobo My pleasure! Shawn Green Database Administrator Unimin Corporation - Spruce Pine previous responses snipped -- Sean Peters Senior Programmer, WIREData Inc. [EMAIL PROTECTED] The software required Windows 2000 or better, so i Downloaded Linux -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql and JDBC connection time out.
Hi, all.
First, I should give some information on the setting:
I have mysql database, say, mydatabase, existing in one machine A which is
Solaris; I am trying to connect this database from another XP machine.
The code snippet in the XP machine looks like this:
String driver = com.mysql.jdbc.Driver;
String URL = jdbc:mysql://url:3306/mydatabase;
String username = abc;
String password = abc;
Connection con;
Class.forName(driver).newInstance();
con = DriverManager.getConnection(URL, username, password);
I got error like this:
root cause
java.sql.SQLException: Unable to connect to any hosts due to exception:
java.net.ConnectException: Connection timed out: connect
I did some search before I come to ask it. There's a very similar question
in this internet:
http://forum.java.sun.com/thread.jspa?threadID=564265messageID=2780194
From there I got the test code to test if the 3306 port is accessible:
import java.net.*;
public class MySQLConnectTest{
public static void main(String args[])throws Exception{
String hostname = 129.173.23.53; // replace with IP of your router
Socket s = new Socket(hostname,3306);
System.out.println(Got connection!);
s.close();
}
}
If I run this code in other few Solaris machines, I got connection. But if
I run this code on the XP machine, I got time out.
I also tried on the XP machine this:
ping the machine with mysql database: ping aaa.bbb.ca, this works
then I tried 'telnet':
telnet aaa.bbb.ca or telnet aaa.bbb.ca:3306
both case, it said: Could not connected to the host , on port 23: connect
failed.
From those tests, I am thinking there are some issues about letting the XP
machine to connect the database. Is there some setting I need to do on the
database so that it will allow certain machine to access the database
Any one can give a hand?
Thanks in advance.
X.Chen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and JDBC connection time out.
I already turned off the firewall on the XP machine, but it doesn't work either. I am doubting that in the database on the other Solaris machine, it doesn't allow the connection from the XP machine. I am doubting this way because other application on other Solaris machine can conenction to the mysql database. If so, is there any way to enable the XP machine to access the database? Thanks a lot for your reply. Xiaobo Xiaobo Chen wrote: then I tried 'telnet': telnet aaa.bbb.ca or telnet aaa.bbb.ca:3306 both case, it said: Could not connected to the host , on port 23: connect failed. Your second example is invalid -- it should be `aaa.bbb.ca 3306` -- so it's not surprising that didn't work :-) But the first one is OK, and shows that this has nothing whatsoever to do with MySQL; you'll have to find out why your XP machine is not apparently able to create outbound connections. Good luck, -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and JDBC connection time out.
I can not use 'telnet aaa.bbb.ca 3306' on XP machine, it will give error like this: Could not open connection to the host, on port 3306. Connection failed. But if I try on other Solaris machines ,it will say something like this: Trying (IP address) Connected to aaa.bbb.ca. Escape character is '^]'. ' 4.0.15 (some characters), Connection closed by foreign host. So that's the reason my application can not connect to the database from the XP machine? If so, to fix this issue, the system administrator has to be involved? Or say, it has nothing to do with Mysql database at this point? Thanks for your time and patience. Xiaobo Xiaobo Chen wrote: I already turned off the firewall on the XP machine, but it doesn't work either. I am doubting that in the database on the other Solaris machine, it doesn't allow the connection from the XP machine. I am doubting this way because other application on other Solaris machine can conenction to the mysql database. Then did you try `telnet aaa.bbb.ca 3306` to confirm you can reach the database machine? Either you can reach the port MySQL is listening on, or you can't. If you can't, you need to fix that. I don't use XP so I can't help there. If you *can* reach that port, but not access the database, you need to add the appropriate permissions to the grant tables. HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question for JDBC and Mysql
Hi, all I have a question like this: There's a field in table_A, date_time, if I say this in Mysql: select min(date_time), max(date_time) from table_A; it returned something like this: +-+-+ | min(date_time) | max(date_time) | +-+-+ | 2003-05-06 11:59:00 | 2003-05-23 11:59:00 | +-+-+ My question is that if I used JDBC like this: String sqlcmd = select min(date_time), max(date_time) from table_A; Statement Stmt = conn.createStatement(); ResultSet RS = Stmt.executeQuery(sqlcmd); How should I get the values, like this? start_time=RS.getString(1); end_time=RS.getString(2); or start_time=RS.getString(min(date_time)); end_time=RS.getString(max(date_time)); Thanks for help. X.Chen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help needed for SQL statement
Hi, I have such a situation: There is a table with gene_ids and clone_ids. Each gene only resides on a single clone and each clone may contain multiple genes. How do would I find how many genes are on each and every clone? Thanks in advance. X.Chen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
