Re: MySQL Rookie having trouble with query containing several outer joins
* [EMAIL PROTECTED] > I'm new to MySQL and have tried many attempts myself and looked > all over for > this answer to no avail. How do I write MySQL joins to accomodate the > Oracle equivalent listed belowany help would be appreciated. > > From > iteration, > story, > person tracker, > person customer, > person developer, > task, > time_entry > Where > iteration.id=story.iteration_id and > story.tracker_id=tracker.id(+) and > story.id=task.story_id(+) and > story.customer_id=customer.id(+) and > task.acceptor_id=developer.id(+) and > task.id=time_entry.task_id(+) > > I've got this so far, but it seems to be returning a cartesian product > between iteration and story > [...] Ouch. I find this syntax easier: From iteration left join story on iteration.id=story.iteration_id left join person tracker on story.tracker_id=tracker.id left join person customer on story.customer_id=customer.id left join task on story.id=task.story_id left join person developer on task.acceptor_id=developer.id left time_entry on task.id=time_entry.task_id The WHERE clause is eliminated in this case. Had to move task before developer, the tables are read in the order you provide when using left joins. http://www.mysql.com/doc/en/JOIN.html > -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble compiling 5.0
Mark Matthews wrote: [snip] On the same page, there's a note about requiring Bison-1.75 or newer to compile MySQL-4.1 (the same holds true for 5.0 as well). You can check what version you have by issuing 'bison --version' in your shell. Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Sorry. I missed that bit. I have MySQL-5 now installed. Thanks for the help. Does anyone have a link to some docs on the syntax for 'create procedure'? I checked on the net for PL/SQL syntax, and found some Oracle and Postgres examples, but none of them worked for me. Just a little example? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select query question
Luis Lebron wrote: I have a test results table that looks like this student_id test_id score 1 1 90 1 1 100 1 1 80 2 1 95 2 1 85 2 1 75 I need to create a query that would give me the average of the top 2 scores per student per test. Following this example, student #1 would have an average of 95 (100 + 90)/2 for test #1 and student #2 would have an average of 90 (95 + 85)/2 Tricky, but doable. SELECT a.student_id, a.test_id, avg(b.score), a.score AS second_highest, max(b.score) AS highest FROM test_results a INNER JOIN test_results b ON a.student_id = b.student_id WHERE a.score <= b.score GROUP BY a.student_id, a.test_id, a.score HAVING count(b.score) = 2; I think this ought to work. To see how, try executing it by hand against the sample data. Basically, the WHERE restricts the join to look at combinations where the student has scores at least the value found in a.score, which is needed to rank the scores. The "group by" allows us to count how many scores are at least as high as the one from 'a'. And, the "HAVING" clause allows us to isolate scores in 'a' which are second-highest using that information; we then compute the average score that's at least as high as the second-highest value. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select query question
I have a test results table that looks like this student_id test_id score 1 1 90 1 1 100 1 1 80 2 1 95 2 1 85 2 1 75 I need to create a query that would give me the average of the top 2 scores per student per test. Following this example, student #1 would have an average of 95 (100 + 90)/2 for test #1 and student #2 would have an average of 90 (95 + 85)/2 BTW, I using mysql 3.23 Any ideas on how I can do this? thanks, Luis
MySQL Rookie having trouble with query containing several outer j oins
I'm new to MySQL and have tried many attempts myself and looked all over for this answer to no avail. How do I write MySQL joins to accomodate the Oracle equivalent listed belowany help would be appreciated. From iteration, story, person tracker, person customer, person developer, task, time_entry Where iteration.id=story.iteration_id and story.tracker_id=tracker.id(+) and story.id=task.story_id(+) and story.customer_id=customer.id(+) and task.acceptor_id=developer.id(+) and task.id=time_entry.task_id(+) I've got this so far, but it seems to be returning a cartesian product between iteration and story SELECT task.name, developer.name, time_entry.start_time, story.name, customer.name, tracker.name, iteration.name FROM ( ( (story left outer join (task left outer join time_entry on task.id=time_entry.task_id left outer join person as developer on task.acceptor_id=developer.id ) on story.id=task.story_id ) left outer join person as customer on story.customer_id=customer.id ) left outer join person as tracker on story.tracker_id=tracker.id ) join iteration on story.iteration_id=iteration.id
Re: It is secure to access MySQL thru internet?
Flavio Tobias wrote: >>> I need to access a database thru internet. It is secure to do this >>> using MySql? >> Sure. If you use a secured connection. > What do you mean with secure connection, ssh? > How to configure this on MySql? I'ld think about SSL http://www.mysql.com/doc/en/Secure_basics.html Use show variables like '%ssl%' to see if it is enabled on your server. For Windows2Windows you might consider IPsec. HansH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: listing all people who have the same firstname and lastname
Fortuno, Adam wrote: I would suggest a SQL statement like this to get the results you're looking for. SELECT COUNT(a.id), a.lname, a.fname FROM people AS a GROUP BY a.lname, a.fname HAVING COUNT(a.id) > 1; That's a great way of doing it if Grant doesn't need the IDs, which he *did* have listed in his original question. If IDs are needed, try this: SELECT a.id, a.lname, a.fname, count(b.id) FROM people a LEFT JOIN people b ON a.lname = b.lname AND a.fname = b.fname GROUP BY a.id, a.lname, a.fname HAVING count(b.id) > 1; I haven't tested this, but I think it ought to work OK. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing Java Objects
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: > I'd be willing to bet if you implement serializable, serialize it and dump > it to a binary column (blob) .. you should be able to restore... > If you use PreparedStatements, Connector/J will do it 'automagically' for you as long as you use PreparedStatement.setObject() and ResultSet.getObject() and store the object into a BLOB field. There is a caveat that for this to work, Connector/J and the serializable class have to come from the same classloader in many cases (some application servers make this functionality break). Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/T8B+tvXNTca6JD8RAjRgAJ4m6wvWjSCteyLtOdAidNXYKwf9nwCdEOMB QaUaOAQsOwMlwYE+y98D8bk= =sEYV -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: It is secure to access MySQL thru internet?
That seems like a lot of work to do when mysql has it built in. http://www.mysql.com/doc/en/Secure_basics.html 4.3.9.1 Basics Beginning with version 4.0.0, MySQL has support for SSL encrypted connections. To understand how MySQL uses SSL, it's necessary to explain some basic SSL and X509 concepts. People who are already familiar with them can skip this part. -->-Original Message- -->From: Cal Evans [mailto:[EMAIL PROTECTED] -->Sent: Friday, August 29, 2003 1:39 PM -->To: Flavio Tobias -->Cc: [EMAIL PROTECTED] -->Subject: Re: It is secure to access MySQL thru internet? --> -->As stated before, it is possible via ssh. --> -->This assumes 2 things: -->1: Your client machine is windows -->2: Your server is running a ssh server. (Which USUALLY means it's a -->flavor -->of Unix but there are ssh servers for windows...why, I don't know but -->there are.) --> -->First, grab putty. Putty is a ssh client on windows. To make putty work -->you will need a shell account on the server running MySQL. If you don't -->have that, stop reading now because none of this will work if putty won't -->connect. -->http://www.putty.nl/download.html --> -->Next grab SQLYog. yes, there are other windows clients for MySQL. Some of -->them are even free. I've used just about every one and SQLYog is the -->best. Anyhow, for the purposes of this message, grab the 30 day trial -->version. You can decide for yourself if you want to buy it. -->http://www.webyog.com/ --> -->Now, here's a tutorial for setting up a tunnel in putty. MySQL runs on -->3306 usually. You will need the name or IP address of the machine running -->the MySQL server. -->http://www.cyberknights.com.au/doc/PuTTY-tunnelling-HOWTO.html --> -->Once you can connect putty to the server and you have the tunnel built, -->fire up SQLYog and setup a connection. NOTE: MySQL host address is -->localhost because you are talking to your tunnel, not the server. --> -->That's all there is. Now you have a safe and encrypted tunnel to MySQL. --> -->HTH, -->=C= -->* -->* Cal Evans -->* http://www.eicc.com -->* We build IT solutions -->* --> -->Flavio Tobias said: -->> I need to access a database thru internet. It is secure to do this -->using -->> MySql? -->> -->> -->> Thanks -->> Flavio Tobias -->> --> --> -->-- -->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 Replicaiton
Hi, It looks like your slave can't connect to the master you've defined. Here are the steps you need to follow : 1- On shell type mysql -h mail.dbi.tju.edu -urepl -pPASSWORD You need to replace word PASSWORD with your actual password. This will show you if you can connect to master from your slave computer. If you can not connect check your password and make sure that on master server user 'repl' has permission to access from your slave computer. You can see permissions by checking user table on mysql db. 2- If you can access to master server from slave next step is : make sure that you have entered your password correctly to /etc/my.cnf file. You can chance your replication user's password by typing change master to master_user = 'repl', master_password = 'PASSWORD' After all these steps type 'start slave' on slave server to see if it is working. Kayra Otaner On Fri, 2003-08-29 at 15:10, Leo Genyuk wrote: > I am having problems to start MySQL replication. I followed all the > steps outlined on the website, but replicaiton is not working. slave > status shows the following: > > mysql> show slave status\G > *** 1. row *** >Master_Host: mail.dbi.tju.edu >Master_User: repl >Master_Port: 3306 > Connect_retry: 60 >Master_Log_File: mail-bin.001 >Read_Master_Log_Pos: 3651 > Relay_Log_File: blade4-relay-bin.001 > Relay_Log_Pos: 3133 > Relay_Master_Log_File: mail-bin.001 > Slave_IO_Running: No > Slave_SQL_Running: Yes >Replicate_do_db: >Replicate_ignore_db: > Last_errno: 0 > Last_error: > Skip_counter: 0 >Exec_master_log_pos: 3651 >Relay_log_space: 3133 > 1 row in set (0.00 sec) > > > As you can see Slave_IO_Running is set to NO. > > I tried to start it manually with the follwoing command: > slave start IO_THREAD; > without any luck. I have also tried to start and stop the slave server > also wihtout any luck. > > Thank you in advance for any help. > > Leo. > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: It is secure to access MySQL thru internet?
We use a point to point VPN between server sites for this... so the security/encryption is totally transparent to mysql, it's just connecting to an IP address on tcp/3306 and the vpn appliances down the line deal with all the data security... There are cheaper solutions such as using freeswan, ssh port forwarding, etc.. But we have had great luck with vpn appliances which are getting very cheap nowdays... On Fri, 29 Aug 2003, Flavio Tobias wrote: > I need to access a database thru internet. It is secure to do this using MySql? > > > Thanks > Flavio Tobias > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing Java Objects
I'd be willing to bet if you implement serializable, serialize it and dump it to a binary column (blob) .. you should be able to restore... On Fri, 29 Aug 2003, Dennis Knol wrote: > Hello, > > Is it possible to store Java objects in the mysql database? > > Kind regards, > Dennis > > > > Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail! > http://login.mail.lycos.com/r/referral?aid=27005 > > -- > 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: It is secure to access MySQL thru internet?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 29 Aug 2003 15:59:18 -0400, Fortuno, Adam wrote: > I need to access a database thru internet. It is secure to do this using > MySql? > If the database traffic is encrypted, yes. I do not know if that is an option in Mysql. If you did not do anything different when accessing the database locally vs. the Internet, then no, it is not secure. The traffic is basically in clear text. Other posts mention ssh (Secure SHell). That is a secure logon to the host where mysql resides and using the command window there. So that is a local connection and no need to encrypt (I would hope). Your interaction with the shell is encrypted. - -- jimoe at sohnen-moe dot com pgp/gpg public key: http://www.keyserver.net/en/ -BEGIN PGP SIGNATURE- Version: PGPfreeware 5.0 OS/2 for non-commercial use Comment: PGP 5.0 for OS/2 Charset: cp850 wj8DBQE/T7vhsxxMki0foKoRAiERAJ9gqttfaV1UMwF700/R6Ayx9VVZeACg5SVq 61Zuy4FtgBIUKjJvxfrDsws= =IHIg -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can we remove the results of "Analyze Table"?
Can we 'unanalyze' a table? I'd like to remove the statistical information from my analyzed table, for testing purposes. Thanks Kevin
Re: It is secure to access MySQL thru internet?
As stated before, it is possible via ssh. This assumes 2 things: 1: Your client machine is windows 2: Your server is running a ssh server. (Which USUALLY means it's a flavor of Unix but there are ssh servers for windows...why, I don't know but there are.) First, grab putty. Putty is a ssh client on windows. To make putty work you will need a shell account on the server running MySQL. If you don't have that, stop reading now because none of this will work if putty won't connect. http://www.putty.nl/download.html Next grab SQLYog. yes, there are other windows clients for MySQL. Some of them are even free. I've used just about every one and SQLYog is the best. Anyhow, for the purposes of this message, grab the 30 day trial version. You can decide for yourself if you want to buy it. http://www.webyog.com/ Now, here's a tutorial for setting up a tunnel in putty. MySQL runs on 3306 usually. You will need the name or IP address of the machine running the MySQL server. http://www.cyberknights.com.au/doc/PuTTY-tunnelling-HOWTO.html Once you can connect putty to the server and you have the tunnel built, fire up SQLYog and setup a connection. NOTE: MySQL host address is localhost because you are talking to your tunnel, not the server. That's all there is. Now you have a safe and encrypted tunnel to MySQL. HTH, =C= * * Cal Evans * http://www.eicc.com * We build IT solutions * Flavio Tobias said: > I need to access a database thru internet. It is secure to do this using > MySql? > > > Thanks > Flavio Tobias > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: It is secure to access MySQL thru internet?
Actually, Secure Shell is built on Secure Socket Layer. You generally speak of SSL with HTTPS connections. You can open an SSH session with a remote Unix box (or anything with an sshd running) then MySQL through that and it's encrypted. You can also create tunnels with SSH, but I've never done it. Gregory L. Hering 4807 Bradford Drive Huntsville, AL 35805 (256) 722-6420 > -Original Message- > From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] > Sent: Friday, August 29, 2003 3:23 PM > To: 'Fortuno, Adam'; 'Flavio Tobias'; [EMAIL PROTECTED] > Subject: RE: It is secure to access MySQL thru internet? > > > I think your thinking of SSL. > > There is some work for mySQL that allows a sol connection and > even some work to sol the replication over the net. > > Look for SSL on mysql.com > > -->-Original Message- > -->From: Fortuno, Adam [mailto:[EMAIL PROTECTED] > -->Sent: Friday, August 29, 2003 12:59 PM > -->To: 'Flavio Tobias'; [EMAIL PROTECTED] > -->Subject: RE: It is secure to access MySQL thru internet? > --> > -->Flavio, > --> > -->Sure. If you wanted to MySQL into a DB over the internet. > I'd think > -->ssh'ing would do the trick. This actually has nothing to do with > -->MySQL, but > allot > -->to > -->do with your OS. If you're running OS X, Linux, or some forms of > Unix. > -->SSH > -->is probably a default package. Follow-up with the documentation for > your > -->OS. > -->If its not available through the OS, you'll need some sort of 3rd > party > -->utility. > --> > -->Regards, > -->Adam > --> > -->-Original Message- > -->From: Flavio Tobias [mailto:[EMAIL PROTECTED] > -->Sent: Friday, August 29, 2003 3:53 PM > -->To: Fortuno, Adam; [EMAIL PROTECTED] > -->Subject: Re: It is secure to access MySQL thru internet? > --> > --> > -->What do you mean with secure connection, ssh? > -->How to configure this on MySql? > --> > -->- Original Message - > -->From: "Fortuno, Adam" <[EMAIL PROTECTED]> > -->To: "'Flavio Tobias'" <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]> > -->Sent: Friday, August 29, 2003 2:17 PM > -->Subject: RE: It is secure to access MySQL thru internet? > --> > --> > -->> Sure. If you use a secured connection. > -->> > -->> A$ > -->> > -->> -Original Message- > -->> From: Flavio Tobias [mailto:[EMAIL PROTECTED] > -->> Sent: Friday, August 29, 2003 1:11 PM > -->> To: [EMAIL PROTECTED] > -->> Subject: It is secure to access MySQL thru internet? > -->> > -->> > -->> I need to access a database thru internet. It is secure > to do this > -->using > -->> MySql? > -->> > -->> > -->> Thanks > -->> Flavio Tobias > -->> > -->> -- > -->> 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/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: It is secure to access MySQL thru internet?
I think your thinking of SSL. There is some work for mySQL that allows a sol connection and even some work to sol the replication over the net. Look for SSL on mysql.com -->-Original Message- -->From: Fortuno, Adam [mailto:[EMAIL PROTECTED] -->Sent: Friday, August 29, 2003 12:59 PM -->To: 'Flavio Tobias'; [EMAIL PROTECTED] -->Subject: RE: It is secure to access MySQL thru internet? --> -->Flavio, --> -->Sure. If you wanted to MySQL into a DB over the internet. I'd think -->ssh'ing -->would do the trick. This actually has nothing to do with MySQL, but allot -->to -->do with your OS. If you're running OS X, Linux, or some forms of Unix. -->SSH -->is probably a default package. Follow-up with the documentation for your -->OS. -->If its not available through the OS, you'll need some sort of 3rd party -->utility. --> -->Regards, -->Adam --> -->-Original Message- -->From: Flavio Tobias [mailto:[EMAIL PROTECTED] -->Sent: Friday, August 29, 2003 3:53 PM -->To: Fortuno, Adam; [EMAIL PROTECTED] -->Subject: Re: It is secure to access MySQL thru internet? --> --> -->What do you mean with secure connection, ssh? -->How to configure this on MySql? --> -->- Original Message - -->From: "Fortuno, Adam" <[EMAIL PROTECTED]> -->To: "'Flavio Tobias'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> -->Sent: Friday, August 29, 2003 2:17 PM -->Subject: RE: It is secure to access MySQL thru internet? --> --> -->> Sure. If you use a secured connection. -->> -->> A$ -->> -->> -Original Message- -->> From: Flavio Tobias [mailto:[EMAIL PROTECTED] -->> Sent: Friday, August 29, 2003 1:11 PM -->> To: [EMAIL PROTECTED] -->> Subject: It is secure to access MySQL thru internet? -->> -->> -->> I need to access a database thru internet. It is secure to do this -->using -->> MySql? -->> -->> -->> Thanks -->> Flavio Tobias -->> -->> -- -->> 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: Simple query help
> select value from tableName where date in (select max(date) from > tableName where id = 4); > > But, it doesn't work with mysql 4.0. > > Any ideas? Does anybody had this problem before? What about: SELECT value, date FROM tablename WHERE id = 4 ORDER BY date ASC Just pick the first row. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: It is secure to access MySQL thru internet?
Flavio, Sure. If you wanted to MySQL into a DB over the internet. I'd think ssh'ing would do the trick. This actually has nothing to do with MySQL, but allot to do with your OS. If you're running OS X, Linux, or some forms of Unix. SSH is probably a default package. Follow-up with the documentation for your OS. If its not available through the OS, you'll need some sort of 3rd party utility. Regards, Adam -Original Message- From: Flavio Tobias [mailto:[EMAIL PROTECTED] Sent: Friday, August 29, 2003 3:53 PM To: Fortuno, Adam; [EMAIL PROTECTED] Subject: Re: It is secure to access MySQL thru internet? What do you mean with secure connection, ssh? How to configure this on MySql? - Original Message - From: "Fortuno, Adam" <[EMAIL PROTECTED]> To: "'Flavio Tobias'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 29, 2003 2:17 PM Subject: RE: It is secure to access MySQL thru internet? > Sure. If you use a secured connection. > > A$ > > -Original Message- > From: Flavio Tobias [mailto:[EMAIL PROTECTED] > Sent: Friday, August 29, 2003 1:11 PM > To: [EMAIL PROTECTED] > Subject: It is secure to access MySQL thru internet? > > > I need to access a database thru internet. It is secure to do this using > MySql? > > > Thanks > Flavio Tobias > > -- > 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]
Simple query help
Hi I have a "little" problem with my sql skills. I have a table with the following fields: id (int) | value (varchar) | date (date) I need to show for a given id the value of the oldest date. Normally, I'd do something like this: select value from tableName where date in (select max(date) from tableName where id = 4); But, it doesn't work with mysql 4.0. Any ideas? Does anybody had this problem before? Thanks in advance and best regards boricles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: It is secure to access MySQL thru internet?
What do you mean with secure connection, ssh? How to configure this on MySql? - Original Message - From: "Fortuno, Adam" <[EMAIL PROTECTED]> To: "'Flavio Tobias'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 29, 2003 2:17 PM Subject: RE: It is secure to access MySQL thru internet? > Sure. If you use a secured connection. > > A$ > > -Original Message- > From: Flavio Tobias [mailto:[EMAIL PROTECTED] > Sent: Friday, August 29, 2003 1:11 PM > To: [EMAIL PROTECTED] > Subject: It is secure to access MySQL thru internet? > > > I need to access a database thru internet. It is secure to do this using > MySql? > > > Thanks > Flavio Tobias > > -- > 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: listing all people who have the same firstname and lastname
Grant, Sure, anything's possible. Assuming you're table looks something like this: CREATE TABLE people ( id INT NOT NULL, fname VARCHAR(15) NULL, lname VARCHAR(20) NULL ) Type=InnoDB; With data something like this: INSERT INTO people (id, fname, lname) VALUES (1, 'John', 'Smith'); INSERT INTO people (id, fname, lname) VALUES (2, 'John', 'Smith'); INSERT INTO people (id, fname, lname) VALUES (3, 'Erika', 'Snow'); INSERT INTO people (id, fname, lname) VALUES (4, 'Michael', 'Boxer'); INSERT INTO people (id, fname, lname) VALUES (5, 'Julian', 'Baser'); INSERT INTO people (id, fname, lname) VALUES (6, 'Mary', 'McKnight'); INSERT INTO people (id, fname, lname) VALUES (7, 'Julian', 'Baser'); I would suggest a SQL statement like this to get the results you're looking for. SELECT COUNT(a.id), a.lname, a.fname FROM people AS a GROUP BY a.lname, a.fname HAVING COUNT(a.id) > 1; You're selecting the information that's important to you (count, last name, and first name). Grouping by first and last name. Then from that list, only reviewing those with a count greater than 1. Presumably anything with a count of 1 is unique in the table. Regards, Adam -Original Message- From: Grant Cooper [mailto:[EMAIL PROTECTED] Sent: Friday, August 29, 2003 3:42 PM To: [EMAIL PROTECTED] Subject: listing all people who have the same firstname and lastname I'm trying to get a query to work by listing all the people in a row with the same last name and first name. key, fname, lname 1 ,John, Smith 4, John, Smith 5, Cody,Edwards 2, Cody, Edwards Don't list anyone that has a unique first name last name. Is this possible? Trying to use the Count command. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Replicaiton
I am having problems to start MySQL replication. I followed all the steps outlined on the website, but replicaiton is not working. slave status shows the following: mysql> show slave status\G *** 1. row *** Master_Host: mail.dbi.tju.edu Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: mail-bin.001 Read_Master_Log_Pos: 3651 Relay_Log_File: blade4-relay-bin.001 Relay_Log_Pos: 3133 Relay_Master_Log_File: mail-bin.001 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 3651 Relay_log_space: 3133 1 row in set (0.00 sec) As you can see Slave_IO_Running is set to NO. I tried to start it manually with the follwoing command: slave start IO_THREAD; without any luck. I have also tried to start and stop the slave server also wihtout any luck. Thank you in advance for any help. Leo. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MysqlDump
sorry got it Sanya Shaik <[EMAIL PROTECTED]> wrote:I have the mysql dump for only 1 table from a database and i want to populate only that table back into the database. how do i do that ? - Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. - Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo.
listing all people who have the same firstname and lastname
I'm trying to get a query to work by listing all the people in a row with the same last name and first name. key, fname, lname 1 ,John, Smith 4, John, Smith 5, Cody,Edwards 2, Cody, Edwards Don't list anyone that has a unique first name last name. Is this possible? Trying to use the Count command. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MysqlDump
I have the mysql dump for only 1 table from a database and i want to populate only that table back into the database. how do i do that ? - Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo.
RE: Question about sub-query.
Greg,
Since you're executing this from a perl script. Try saving the result of
"SELECT MAX(cycle) FROM test.results WHERE snum = '$snum';" to a variable.
Then use that variable in your embedded SQL statement. This alleviates that
issue all together. If you're updating multiple rows, you're query should
run faster since it won't have to re-calculate the max() function for each
iteration - maybe SQL optimizer treats it as a constant after the first
iteration, but whatever.
About your specific question, why the syntax doesn't work. I would double
check the version of MySQL you're using ("SELECT VERSION();"). If you're
like me, you're using version 4.0.x,(Production) which doesn't have sub
queries. I think sub queries are supported as of version 4.1.x (alpha) - see
the below URL for more details. Otherwise, you're syntax looks fine.
Regards,
Adam
http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
I am getting
ERROR 1093 at line 1: You can't specify target table 'results' for update in
FROM clause.
I found an article on the internet that says an update with a sub-query that
references the same table is illegal. Their example solution didn't help
any though because they were using a join and the rewrote the update with
EXISTS.
I'm trying to update some elements of a row in which another of the values
in that row is a MAX.
Sample update statement:
my $c1Str =
qq[ UPDATE test.results
SET result = '$result',
WHERE snum = '$snum'
AND cycle = (SELECT MAX(cycle)
FROM test.results
WHERE snum = '$snum') ];
I could break it into two steps, but I wonder if that wouldn't be much less
efficient.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about sub-query.
Dear MySQL gurus, I am trying to update on a Perl program that connectes to Oracle through DBI. I want to develop to a local MySQL database because I don't have access Pro*C to build DBI::Oracle on my Sun box. The problem is that my update statement is giving errors. The query as written works in DBI with DBD:Oracle but not with DBD:MySQL. I am getting ERROR 1093 at line 1: You can't specify target table 'results' for update in FROM clause. I found an article on the internet that says an update with a sub-query that references the same table is illegal. Their example solution didn't help any though because they were using a join and the rewrote the update with EXISTS. I'm trying to update some elements of a row in which another of the values in that row is a MAX. Sample update statement: my $c1Str = qq[ UPDATE test.results SET result = '$result', WHERE snum = '$snum' AND cycle = (SELECT MAX(cycle) FROM test.results WHERE snum = '$snum') ]; I could break it into two steps, but I wonder if that wouldn't be much less efficient. I found this on the MySQL General list UPDATE main m, sub s SET m.Value = s.subValue WHERE m.id= s.mainid AND m.KeyDate = MAX(m.KeyDate); In this case, would the MAX be the maximum of all values in the KeyDate field or would it consider only the subset of rows generated by the join? Would that apply in my case or could I use a 'self' join to generate that situation? Thanks for your feedback. Sincerely, Greg Gregory L. Hering 4807 Bradford Drive Huntsville, AL 35805 (256) 722-6420
Re: Trouble compiling 5.0
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Spam Bait wrote: > Hi all. > > It's about time I tried out MySQL-5.0. I want to get comfortable with > the stored procedures so I'm ready to go when it's released. > > I'm running Gentoo ( current, of course ). > > I ran: > > export WANT_AUTOMAKE=1.5 > export WANT_AUTOCONF_2_5=1 > > These are ( I think ) Gentoo-specific commands that make select the > appropriate versions of the above tools ( as in the instructions in the > 'installing from development source' ). > > Then I ran: > > aclocal > autoheader > autoconf > automake > cd innobase ; aclocal; autoheader; autoconf; automake > ./configure --prefix=/usr/local/mysql --enable-assembler > --enable-thread-safe-client --enable-local-infile > make > > After a fair bit of compiling, I got: > > gcc -O3 -DDBUG_OFF -O3 -rdynamic -o test-sslclient test-sslclient.o > ../dbug/libdbug.a libvio.a ../mysys/libmysys.a ../strings/libmystrings.a > -lpthread -lz -lcrypt -lnsl -lm -lpthread > make[2]: Leaving directory `/mnt/roomy/src/mysql/mysql-5.0_29-08-03/vio' > Making all in sql > make[2]: Entering directory `/mnt/roomy/src/mysql/mysql-5.0_29-08-03/sql' > bison -y -d sql_yacc.yy && mv y.tab.c sql_yacc.cc > conflicts: 380 shift/reduce > sql_yacc.yy:5962: fatal error: maximum table size (32767) exceeded [snip] On the same page, there's a note about requiring Bison-1.75 or newer to compile MySQL-4.1 (the same holds true for 5.0 as well). You can check what version you have by issuing 'bison --version' in your shell. Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/T5KqtvXNTca6JD8RArftAKC+/ONkkRj/l6V803HvUX6za0gqGgCdFSiq fAOy9aEqY7yt1E0D7FerD0k= =w3yz -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Atomic operations
Hi all, Now that technologies such as Reiser4 are emerging that offer atomic filesystem operations, is it expected that MySQL will take advantage of them? I guess I'm asking a massive number of questions with the above simplistic statement. Admittedly, this is currently a Linux-centric thing (to my knowledge - corrections invited and welcome!) and the benefit of moving some of this type of responsibilty to the fs is another open question. Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: It is secure to access MySQL thru internet?
Sure. If you use a secured connection. A$ -Original Message- From: Flavio Tobias [mailto:[EMAIL PROTECTED] Sent: Friday, August 29, 2003 1:11 PM To: [EMAIL PROTECTED] Subject: It is secure to access MySQL thru internet? I need to access a database thru internet. It is secure to do this using MySql? Thanks Flavio Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
It is secure to access MySQL thru internet?
I need to access a database thru internet. It is secure to do this using MySql? Thanks Flavio Tobias
Re: Storing Java Objects
I wonder if you could serialize your object, and then store it in a blob column. I'd be interested to know ... Dennis Knol wrote: > > Hello, > > Is it possible to store Java objects in the mysql database? > > Kind regards, > Dennis > > > Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail! > http://login.mail.lycos.com/r/referral?aid=27005 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixing Replication
Keith Bussey wrote: Hi all, I was wodnering if someone has a better way of re-sync'ing a slave when replication fails. Here's my setup: 1 - MASTER 5 - SLAVES Now sometimes, one of the slaves will fail. Usually when it's the sql that stops running, I can fix it and get it back. However sometimes, I also get the "master binlog corruption" error and haven't found a way to fix that. What I do is: - stop mysql on the master and all the slaves - remove the server-bin files on the master - remove the mysql data dir on each slave - copy the master's datadir to each slave - restart mysql everywhere and everything is fine Now while this method works, it is extremely time-consuming and I was wondering if anyone knows a better solution to only fix the affected slave?? I have tried stopping just the affected slave and one other good slave, taking its datadir and renaming the bin files to the affected server's name. That method doesn't seem to work though. Thanks in advance! Hi, My setup is: 1 master, 6 slaves. I have a different approach: On master: - "FLUSH TABLES WITH READ LOCK" - mysqldump relevant tables (we don't replicate all tables) - "SHOW MASTER STATUS" and write down binary log file and offset - "UNLOCK TABLES" On broken slave: - "SLAVE STOP" (just in case) - rebuild tables using mysqldump data - "CHANGE MASTER TO MASTER_LOG_FILE=xxx, MASTER_LOG_POS=yyy" - "SLAVE START" It has on big advantage: it doesn't stop the master and other slaves. There is only a slowdown while tables are locked on the master but I do it early in the morning when database activity is low (I can wait to repair the broken slave : it is removed from the server pool used by the applications when it fails). I am sure that it is possible to further optimize this procedure but, since I had to use it only 3 times since I have setup replication (18 months ago), I haven't invested more time to it. However, I like your idea to use another slave instead of the master; it may be possible to adapt my approach to do it. PS: I use mysql 3.23, mysql 4.x has a "LOAD DATA FROM MASTER" that makes it much easier ;) Hope this helps, Joseph Bueno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Table question
Hi, How do you copy a table in mySQL. I know I can do it in phpMyAdmin but I want the actual syntax. Thanks!! Tim Winters Creative Development Manager Sampling Technologies Incorporated 1600 Bedford Highway, Suite 212 Bedford, Nova Scotia B4A 1E8 www.samplingtechnologies.com [EMAIL PROTECTED] [EMAIL PROTECTED] Office: 902 450 5500 Cell: 902 430 8498 Fax:: 902 484 7115
Master/Master Replication
Hello, I have set up a master/slave replication environment using 4.0.13 but would like to know if there is a way to set up master/master replication between two databases, both able to be inserted/updated/deleted, tables created, etc., with the changes on both databases propagated to each other. I see one comment in the documentation on two way replication: Q: What issues should I be aware of when setting up two-way replication? A: MySQL replication currently does not support any locking protocol between master and slave to guarantee the atomicity of a distributed (cross-server) update. In other words, it is possible for client A to make an update to co-master 1, and in the meantime, before it propagates to co-master 2, client B could make an update to co-master 2 that will make the update of client A work differently than it did on co-master 1. Thus when the update of client A will make it to co-master 2, it will produce tables that will be different from what you have on co-master 1, even after all the updates from co-master 2 have also propagated. So you should not co-chain two servers in a two-way replication relationship, unless you are sure that you updates can safely happen in any order, or unless you take care of mis-ordered updates somehow in the client code. You must also realise that two-way replication actually does not improve performance very much, if at all, as far as updates are concerned. Both servers need to do the same amount of updates each, as you would have one server do. The only difference is that there will be a little less lock contention, because the updates originating on another server will be serialised in one slave thread. This benefit, though, might be offset by network delays. -- But there is no further info on how to set it up. Is bidrectional set up by using the log-slave-updates parameter on the slave and point it to the 'master'? It's just kind of vague :-( Thanks in advance, Emi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up fulltext
At 05:17 PM 8/28/2003, you wrote: Hi, no - adding a limit doesn't really help. thanks, - Mark Mark et al, Has anyone tried MNOGoSearch (http://search.mnogo.ru/) as a full text replacement for MySQL? Although it 's primary focus is to index web pages, it can also be used directly on MySQL tables. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem When ugrade to MySQL4.1-alpha
Hi, I am not sure how to upgrade to MySQL4.1-alpha (window) from MySQL4.0, so i just copy the file in the folder which i downloaded from web and then paste in the mysql folder in C:\mysql . But later i found out that the winmysqladmin in the bin folder can not be opened( i double click and nothing come out) and whenever i want to connect to the MySQL, many warnings come out. Can anyone tell me why? What's wrong? How to upgrade? regards, florence Yahoo! Games - Who Wants to Be A Millionaire? Play now!
Fixing Replication
Hi all, I was wodnering if someone has a better way of re-sync'ing a slave when replication fails. Here's my setup: 1 - MASTER 5 - SLAVES Now sometimes, one of the slaves will fail. Usually when it's the sql that stops running, I can fix it and get it back. However sometimes, I also get the "master binlog corruption" error and haven't found a way to fix that. What I do is: - stop mysql on the master and all the slaves - remove the server-bin files on the master - remove the mysql data dir on each slave - copy the master's datadir to each slave - restart mysql everywhere and everything is fine Now while this method works, it is extremely time-consuming and I was wondering if anyone knows a better solution to only fix the affected slave?? I have tried stopping just the affected slave and one other good slave, taking its datadir and renaming the bin files to the affected server's name. That method doesn't seem to work though. Thanks in advance! -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Querying a Linux machine
Michael Piko <[EMAIL PROTECTED]> wrote: > Its a standard RedHat 8.0 install. MySQL version is 3.23.52-3 installed from > the standard RPMs Run mysqld with --thread-stack=192K option: http://www.mysql.com/doc/en/Linux-x86.html > > - Original Message - > From: "Victoria Reznichenko" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, August 29, 2003 7:56 PM > Subject: Re: Querying a Linux machine > > >> Michael Piko <[EMAIL PROTECTED]> wrote: >> > I have mysql on a Linux machine and can connect to through 'localhost' > and >> > its real IP/hostname. I cannot connect to it from other machines. Is > there >> > anything I need to do to make this happen? >> >> Please, provide some more info >> 1. Version of MySQL >> 2. Do you use binary or sorce distribution? >> 3. What exactly Linux do you use (SuSe, Red Hat etc.)? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to start MYSQL
"Suresh Babu A. [IT Engineer]" <[EMAIL PROTECTED]> wrote: > > I couldn't start the mysql in the red hat 8, due the bad > configuration in my.cnf and non availability of data file for > the database. I want to reinstall the mysql, kindly let me know how to. You can just edit my.cnf file. > > I have also tried "rmp -e .rpm' and removed the files from > /var/lib/mysql. Reinstalled everything, still i couldn't make it. > > Pacakage availabe now : - > > [EMAIL PROTECTED] mysql]# rpm -qa | grep -i mysql > MySQL-bench-4.0.13-0 > MySQL-server-4.0.13-0 > MySQL-shared-4.0.13-0 > MySQL-shared-compat-4.0.13-0 > MySQL-devel-4.0.13-0 > MySQL-client-4.0.13-0 > MySQL-embedded-4.0.13-0 > Mysql-DBI-perl-bin-1.1825-1 > > > Error at startup : - > > 030829 17:09:57 mysqld started > 030829 17:09:57 InnoDB: Started > 030829 17:09:57 Fatal error: Can't open privilege tables: Table 'mysql.host' do > esn't exist > 030829 17:09:57 Aborting > > 030829 17:09:57 InnoDB: Starting shutdown... > 030829 17:09:59 InnoDB: Shutdown completed > 030829 17:09:59 /usr/sbin/mysqld: Shutdown Complete > > 030829 17:09:59 mysqld ended Check if privilege tables exist. If so check permissions on the files. If you don't have privilege tables, run mysql_install_db script. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing Java Objects
Hello, Is it possible to store Java objects in the mysql database? Kind regards, Dennis Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail! http://login.mail.lycos.com/r/referral?aid=27005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance Problem with full text search
Hello,
we're trying to do a BOOLEAN full text search over a table with about
200.000 entrys and 650MB of data (full text index is about half the size
of the data, the average row length is about 3,2kB) on the System stated
below. A sample statement we're trying to run is:
SELECT
id, title
FROM
texts
WHERE
MATCH(title, text) AGAINST('+strasse* +erneuerung*' IN BOOLEAN MODE);
EXPLAIN says: table: texts
type : fulltext
possible_keys: text
key : text
key_len : 0
The key text is a combined full text index over the colums TEXT and TITLE.
This kind of statement takes about 10 to 30 seconds and generates 30-40%
CPU usage for the first time the match string is used. If the search is
already in cache it takes from 0 to 3 seconds. But unfortunaltely we
could not preexecute all possible searches, so the time for the first
use of the match string is of capital importance.
Is such a full text search really that slow? Or is there any possibility
to speed this up to about 2 to 5 seconds? Help would be very appreciated.
best regards and thanks
Axel Scheel
System Description:
---
Linux 2.4.21
CPU: AMD Duron(tm) Processor stepping 00
Detected 647.810 MHz processor.
Calibrating delay loop... 1291.05 BogoMIPS
Memory: 774500k/786368k available
MySQL Version: 4.0.13
mytop says : Key Efficiency: 99.7%
my.cnf : key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
The data file (*.MYD) and the index file (*.MYI) resist on different
hard disks. The one with the index is of type ext2 the one with the data
is of type ext3.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why are Duplicate keys allowed???
This is perfectly legal and right. A null should not be used for a value, and null should be used where a value will be put but has not been yet, so if I plan on putting 3 entries in where they are all the same but that in future will be different this is correct. Anyways two NULLS should not be equal to eachother (I believe they may be in oracle, but someone else is welcome to correct me on that fact) when tested. mos wrote: I have a table with a UNIQUE compound index, Cust_Primary that is composed of 4 columns: Cust_Type, Area, Join_Date, Join_Time. This index should allow for only unique entries, but it doesn't. If Join_Time is NULL then it allows for duplicates. Why? CREATE TABLE `CustHistory2` ( `Cust_Id` int(10) unsigned NOT NULL auto_Increment, `Area` char(2) default NULL, `Cust_Type` char(17) default NULL, `Join_Date` date default NULL, `Join_Time` time default null, PRIMARY KEY (`Cust_Id`), UNIQUE KEY `Cust_Id` (`Cust_Id`), UNIQUE KEY `Cust_Primary` (`Cust_Type`,`Area`,`Join_Date`,`Join_Time`) ) TYPE=MyISAM Example. Join_Time is usually NULL (but not always). I've discovered that if Join_Time is NULL, MySQL allows for duplicate rows! ! Data: Cust_Type, Area, Join_Date, Join_Time 'BIG', 'N', '2003-01-01',NULL 'BIG', 'N', '2003-01-01',NULL 'BIG', 'N', '2003-01-01',NULL These rows are allowed even though they are all duplicates. I could have the same row duplicated a thousand times. Why? Apparently MySQL only prevents duplicate rows if Join_Time is not NULL. It seems that if Join_Time is NULL then all bets are off and you can have as many duplicates as you like. I don't understand the reasoning behind this loophole. The only way around this is to specify a NOT NULL for Join_Time and define a default value like 0 so it defaults to 12:00:00AM. I don't see why I have to do this. Can someone shed some light on this? TIA Mike MySQL version 4.10 Alpha-Max-Nt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable to start MYSQL
Hi Team, I couldn't start the mysql in the red hat 8, due the bad configuration in my.cnf and non availability of data file for the database. I want to reinstall the mysql, kindly let me know how to. I have also tried "rmp -e .rpm' and removed the files from /var/lib/mysql. Reinstalled everything, still i couldn't make it. Pacakage availabe now : - [EMAIL PROTECTED] mysql]# rpm -qa | grep -i mysql MySQL-bench-4.0.13-0 MySQL-server-4.0.13-0 MySQL-shared-4.0.13-0 MySQL-shared-compat-4.0.13-0 MySQL-devel-4.0.13-0 MySQL-client-4.0.13-0 MySQL-embedded-4.0.13-0 Mysql-DBI-perl-bin-1.1825-1 Error at startup : - 030829 17:09:57 mysqld started 030829 17:09:57 InnoDB: Started 030829 17:09:57 Fatal error: Can't open privilege tables: Table 'mysql.host' do esn't exist 030829 17:09:57 Aborting 030829 17:09:57 InnoDB: Starting shutdown... 030829 17:09:59 InnoDB: Shutdown completed 030829 17:09:59 /usr/sbin/mysqld: Shutdown Complete 030829 17:09:59 mysqld ended 030829 17:10:09 mysqld started 030829 17:10:10 InnoDB: Started 030829 17:10:10 Fatal error: Can't open privilege tables: Table 'mysql.host' do esn't exist 030829 17:10:10 Aborting 030829 17:10:10 InnoDB: Starting shutdown... 030829 17:10:12 InnoDB: Shutdown completed 030829 17:10:12 /usr/sbin/mysqld: Shutdown Complete 030829 17:10:12 mysqld ended -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Scripts with arguments
* Enrique Andreu > Hello, I think I have explained bad. I want to pass > the arguments to the sql script. > The argument is for the sql script. > > For example: > myscript.sql should be: > GRANT ALL PRIVILEGES ON *.* TO &1@'%' > where &1 would be parameter 1 > > I want to do something like that: > dos>mysql mysql -u root -p < myscript.sql argument You should probably also give the users passwords... Try this: C:>copy con adduser.cmd @mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* TO '%1'@'%%'" ^Z C:>adduser username Note the double %% for host. "^Z" means press Ctrl+Z, it's the end-of-file marker. The leading '@' is to make it silent. Remove this or add the -v option to mysql to see the command beeing executed. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Querying a Linux machine
Is it giving you access denied, or is it failing to find the host machine? if it's access denied you will need to add the username and hostname (of whichever machine you are connecting from) and grant that user access. use the 'mysql_setpermission' script to do this. If you can't actually see the machine, then it's a linux issue. Marty - Original Message - From: "Michael Piko" <[EMAIL PROTECTED]> To: "Victoria Reznichenko" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 29, 2003 11:39 AM Subject: Re: Querying a Linux machine > Its a standard RedHat 8.0 install. MySQL version is 3.23.52-3 installed from > the standard RPMs > > - Original Message - > From: "Victoria Reznichenko" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, August 29, 2003 7:56 PM > Subject: Re: Querying a Linux machine > > > > Michael Piko <[EMAIL PROTECTED]> wrote: > > > I have mysql on a Linux machine and can connect to through 'localhost' > and > > > its real IP/hostname. I cannot connect to it from other machines. Is > there > > > anything I need to do to make this happen? > > > > Please, provide some more info > > 1. Version of MySQL > > 2. Do you use binary or sorce distribution? > > 3. What exactly Linux do you use (SuSe, Red Hat etc.)? > > > > > > -- > > For technical support contracts, goto https://order.mysql.com/?ref=ensita > > This email is sponsored by Ensita.net http://www.ensita.net/ > >__ ___ ___ __ > > / |/ /_ __/ __/ __ \/ /Victoria Reznichenko > > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net > ><___/ www.mysql.com > > > > > > > > > > > > -- > > 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: Scripts with arguments
Hello, I think I have explained bad. I want to pass the arguments to the sql script. The argument is for the sql script. For example: myscript.sql should be: GRANT ALL PRIVILEGES ON *.* TO &1@'%' where &1 would be parameter 1 I want to do something like that: dos>mysql mysql -u root -p < myscript.sql argument But I don´t know how to do it. Thanks: Enrique Andreu ___ Yahoo! Messenger - Nueva versión GRATIS Super Webcam, voz, caritas animadas, y más... http://messenger.yahoo.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ERROR in LOAD DATA
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > I use a CPanel Shared hosting environment where MySQL user is not permitted > with FILE privilege. Please help me by telling what is the alternate > process to LOAD DATA from a text data file to MySQL table. > You can use LOAD DATA LOCAL. In this case user doesn't need FILE privilege, but this command should be enabled. For more info look at: http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html Note: file should be located on the client box. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble compiling 5.0
Hi all. It's about time I tried out MySQL-5.0. I want to get comfortable with the stored procedures so I'm ready to go when it's released. I'm running Gentoo ( current, of course ). I ran: export WANT_AUTOMAKE=1.5 export WANT_AUTOCONF_2_5=1 These are ( I think ) Gentoo-specific commands that make select the appropriate versions of the above tools ( as in the instructions in the 'installing from development source' ). Then I ran: aclocal autoheader autoconf automake cd innobase ; aclocal; autoheader; autoconf; automake ./configure --prefix=/usr/local/mysql --enable-assembler --enable-thread-safe-client --enable-local-infile make After a fair bit of compiling, I got: gcc -O3 -DDBUG_OFF -O3 -rdynamic -o test-sslclient test-sslclient.o ../dbug/libdbug.a libvio.a ../mysys/libmysys.a ../strings/libmystrings.a -lpthread -lz -lcrypt -lnsl -lm -lpthread make[2]: Leaving directory `/mnt/roomy/src/mysql/mysql-5.0_29-08-03/vio' Making all in sql make[2]: Entering directory `/mnt/roomy/src/mysql/mysql-5.0_29-08-03/sql' bison -y -d sql_yacc.yy && mv y.tab.c sql_yacc.cc conflicts: 380 shift/reduce sql_yacc.yy:5962: fatal error: maximum table size (32767) exceeded make[2]: *** [sql_yacc.cc] Error 1 make[2]: Leaving directory `/mnt/roomy/src/mysql/mysql-5.0_29-08-03/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/mnt/roomy/src/mysql/mysql-5.0_29-08-03' make: *** [all] Error 2 [EMAIL PROTECTED] mysql-5.0_29-08-03 $ Something I'm doing wrong? Thanks! Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Scripts with arguments
If it's going to be a simple batch file then use %1, %2, ... inside the script to get the parameters passed to it. Or better install & use a real scripting language like perl. ;-) Lian Sebe, M.Sc. Freelance Analyst-Programmer www.programEz.net > -Original Message- > From: Enrique Andreu [mailto:[EMAIL PROTECTED] > Sent: Friday, August 29, 2003 12:36 PM > To: [EMAIL PROTECTED] > Subject: Scripts with arguments > > > Hello. > I have to do an script to create MySQL users, the name > of the new user have to be a parameter of the script. > I have no idea, someone can helps me. > I'm an NT user. > > > Thanks: >Enrique Andreu > > ___ > Yahoo! Messenger - Nueva versión GRATIS > Super Webcam, voz, caritas animadas, y más... > http://messenger.yahoo.es > > -- > 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]
UDF support on windows
Hello, I have found a very interesting UDF function called MyPHP. There is no problems with UDF installation on Linux based machine but my latop and all developing tools are working on windows and I don't know how to install UDF on windows. Can anyone help me with this ?? ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem Query - Help Please
> When I execute the following query I get duplicate > product_id's as shown > below: > > SELECT * FROM product, product_category_xref, category WHERE > product_parent_id='' > AND product.product_id=product_category_xref.product_id > AND category.category_id=product_category_xref.category_id > AND product.product_publish='Y' > AND product.product_special='Y' ORDER BY product_name DESC\G > > > Results ( As you can see product_id 4139 occurs twice and I desire the > product_id's to be unique in this query) > > I have also included the descriptions of the tables. > > I would appreciate someone assisting me with a query that > works correctly. Product 2139 has two different categories BOOKS and EDUCATION and therefore appears twice. category_id: 7920cfab5c630ca88ceabcfda6b3848d product_id: 4139 product_list: NULL category_id: 7920cfab5c630ca88ceabcfda6b3848d vendor_id: 1 category_name: BOOKS category_id: 4ee8c8513ee84c95c8eb7f24e63d7222 product_id: 4139 product_list: NULL category_id: 4ee8c8513ee84c95c8eb7f24e63d7222 vendor_id: 1 category_name: EDUCATION If you need to show all the categories you will probably need to retrieve thos separately and build a list programatically to give something like categories: EDUCATION, BOOKS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Querying a Linux machine
Its a standard RedHat 8.0 install. MySQL version is 3.23.52-3 installed from the standard RPMs - Original Message - From: "Victoria Reznichenko" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, August 29, 2003 7:56 PM Subject: Re: Querying a Linux machine > Michael Piko <[EMAIL PROTECTED]> wrote: > > I have mysql on a Linux machine and can connect to through 'localhost' and > > its real IP/hostname. I cannot connect to it from other machines. Is there > > anything I need to do to make this happen? > > Please, provide some more info > 1. Version of MySQL > 2. Do you use binary or sorce distribution? > 3. What exactly Linux do you use (SuSe, Red Hat etc.)? > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.net http://www.ensita.net/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Victoria Reznichenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ><___/ www.mysql.com > > > > > > -- > 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: Querying a Linux machine
Michael Piko <[EMAIL PROTECTED]> wrote: > I have mysql on a Linux machine and can connect to through 'localhost' and > its real IP/hostname. I cannot connect to it from other machines. Is there > anything I need to do to make this happen? Please, provide some more info 1. Version of MySQL 2. Do you use binary or sorce distribution? 3. What exactly Linux do you use (SuSe, Red Hat etc.)? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to upgrade to MySQL4.1-alpha
florence florence <[EMAIL PROTECTED]> wrote: > > I have downloaded the folder which contains MySQL 4.1 alpha. For the notes in > the documentation, i still can not understand how to upgrade to version4.1. I am > just start to use MySQL and straigh forward want to upgrade to 4.1 but there is not > setup file there. How to do? Thanks. > Which OS do you use? Windows? Did you download source or binary distribution? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Scripts with arguments
Hello. I have to do an script to create MySQL users, the name of the new user have to be a parameter of the script. I have no idea, someone can helps me. I'm an NT user. Thanks: Enrique Andreu ___ Yahoo! Messenger - Nueva versión GRATIS Super Webcam, voz, caritas animadas, y más... http://messenger.yahoo.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help Instalation
Good morning. In a normal case, to install Mysql in windows we need to run setup.exe and it's launch a wizard when we define some things like a destination directory. I want to know (if somebody can help me) if it is possible to install mysql and the wizard not be launched. How to do it?? Thanks Hugo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up fulltext
Hello
If You need some elp You have to give us more details:
What version You use?
Does exist one fulltext index on title + keywords?
The schema and indexes.
The result of:
explain select count(*) from resources where match title,keywords
against('common word');
I think index file size is small. I expect a size greater or equal
than the data file
( 134 471 560 data << 61 629 440 of index).
Santino
At 12:32 -0700 28-08-2003, Mark wrote:
Hi,
I have a fulltext index on a table with 80,000 rows. when I do a
search for a common word it is very slow, for example:
select count(*) from resources where match title,keywords
against('common word');
might take over a minute if there are a 5,000 or so rows that match.
I'm looking for a way to speed this up and I'm thinking about adding
as stop words any word that occurs in more than 1000 records. is
there a way to find these? or is there something else someone can
suggest?
here are some of my variables:
ft_boolean_syntax | + -><()~*:""&
ft_min_word_len | 4
ft_max_word_len | 254
ft_max_word_len_for_sort| 20
ft_stopword_file| (built-in)
key_buffer_size | 268435456
myisam_sort_buffer_size | 67108864
here is my table size on disk:
-rw-rw1 mysqlmysql8976 Aug 27 10:20 resources.frm
-rw-rw1 mysqlmysql134471560 Aug 28 09:33
resources.MYD
-rw-rw1 mysqlmysql61629440 Aug 28 10:23 resources.MYI
any tips are appreciated.
thanks,
- Mark
--
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 API C & leaks
gerald_clark wrote:
Andreï V. FOMITCHEV wrote:
Hello everyone,
I use Valgrind to check my programs and this last found leaks in
libmysqlclient.so.
My code is simple:
char * requete = "SELECT * FROM "NOM_TABLE_1;
Looks like a misplaced '"' .
lol
#define NOM_TABLE_1 "files"
The problem is a function get_charset() used in libmysqlclient.so
This function defined in
mysql-4.0.14/include/my_sys.h:213:extern CHARSET_INFO *get_charset(uint
cs_number, myf flags);
mysql-4.0.14/mysys/charset.c:375:CHARSET_INFO *get_charset(uint
cs_number, myf flags)
If you use Vagrind (valgrind -v --leak-check=yes --show-reachable=yes)
for checking your program, does it find errors or leaks?
MYSQL * mysql = mysql_init((MYSQL *)NULL);
MYSQL_RES * mysql_resultat;
MYSQL_ROW mysql_ligne;
if(mysql == NULL)
{
return(-1);
}
if (!mysql_real_connect(mysql, ADRESSE_BDD, LOGIN_BDD, PASSWORD_BDD,
NOM_BDD, 0, NULL, 0))
{
fprintf(stderr, "Impossible de se connecter au serveur %s\n
Error=%s\n", ADRESSE_BDD, mysql_error(mysql));
mysql_close(mysql);
return(-1);
}
if(mysql_query(mysql, requete) != 0)
{
fprintf(stderr, "Impossible d'executer la requête %s\nError=%s\n",
requete, mysql_error(mysql));
}
else
{
mysql_resultat = mysql_store_result(mysql);
if(mysql_resultat != NULL)
{
for(i = 0; i < mysql_resultat->row_count; i++)
{
mysql_ligne = mysql_fetch_row(mysql_resultat);
// traitement
}
}
mysql_free_result(mysql_resultat);
}
mysql_close(mysql);
Result of Valgrind: ==3691== 64 bytes in 1 blocks are still reachable
in loss record 1 of 2
==3691== at 0x4015E310: malloc (vg_clientfuncs.c:103)
==3691== by 0x40254A2D: my_malloc (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025A218: init_dynamic_array (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025AE96: init_available_charsets (in /usr/lib/mysql/
libmysqlclient.so.10.0.0)
==3691== ==3691== 4088 bytes in 1 blocks are still reachable in loss
record 2 of 2
==3691== at 0x4015E310: malloc (vg_clientfuncs.c:103)
==3691== by 0x4025A64C: my_once_alloc (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025ACB0: read_charset_index (in
/usr/lib/mysql/libmysqlclient.so.10.0.0)
==3691== by 0x4025AEAB: init_available_charsets (in /usr/lib/mysql/
libmysqlclient.so.10.0.0)
Is it a BUG or did I something?
Best regards,
--
Andreï V. FOMITCHEV [Quand faut-il arrêter l'informatique]
Software R&D Engineer [Lorsque, dans un kilo, on trouve 1024 grammes]
Odixion SAS, FRANCE
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Querying a Linux machine
Here it is: /var/log/mysqld.log Number of processes running now: 1 mysqld process hanging, pid 17153 - killed 030829 09:28:31 mysqld restarted Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add to the [mysqld] section, for example, innodb_data_file_path = ibdata1:10M:autoextend But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 2 at http://www.innodb.com/ibman.html /usr/libexec/mysqld: ready for connections Doesnt tell me much! This is repeated each time I try to connect. TIA Michael. - Original Message - From: "Antony Dovgal" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, August 29, 2003 4:17 PM Subject: Re: Querying a Linux machine > On Fri, 29 Aug 2003 09:15:48 +1000 > Michael Piko <[EMAIL PROTECTED]> wrote: > > > I should have mentioned the error: > > [kryten2] ERROR 2013: Lost connection to MySQL server during query > > check your MySQLd log to see what happends. > usually you can find it in /var/log/ > > --- > WBR, > Antony Dovgal aka tony2001 > [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: Newbie Question: how to set field a lookup field from another table?
On Fri, 29 Aug 2003 01:02:57 -0400 (EDT) "Jordan Morgan" <[EMAIL PROTECTED]> wrote: > Hi, > > I'm very new to MySQL. I normally use MS Access but my project needs > MySQL. I'm able to set a field(categoryID) in table A(product) a lookup > field to another table(category) in Access but I can't find anywhere that > teaches me how to do that in MySQL. > > I'm managing MySQL through a web interface provided by my hosting company. > I can use SQL statements as well as clicking a few buttons. > > Any advice is highly appreciated. Thanks a bunch! You mean you need to create a FOREIGN KEY? Read this: "In MySQL Server 3.23.44 and up, InnoDB tables support checking of foreign key constraints, including CASCADE, ON DELETE, and ON UPDATE. See section 7.5.5.2 Foreign Key Constraints. For other table types, MySQL Server only parses the FOREIGN KEY syntax in CREATE TABLE commands, but does not use/store this info. " http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Querying a Linux machine
On Fri, 29 Aug 2003 09:15:48 +1000 Michael Piko <[EMAIL PROTECTED]> wrote: > I should have mentioned the error: > [kryten2] ERROR 2013: Lost connection to MySQL server during query check your MySQLd log to see what happends. usually you can find it in /var/log/ --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to upgrade to MySQL4.1-alpha
Hi, I have downloaded the folder which contains MySQL 4.1 alpha. For the notes in the documentation, i still can not understand how to upgrade to version4.1. I am just start to use MySQL and straigh forward want to upgrade to 4.1 but there is not setup file there. How to do? Thanks. regards, florence Yahoo! Games - Who Wants to Be A Millionaire? Play now!
Newbie Question: how to set field a lookup field from another table?
Hi, I'm very new to MySQL. I normally use MS Access but my project needs MySQL. I'm able to set a field(categoryID) in table A(product) a lookup field to another table(category) in Access but I can't find anywhere that teaches me how to do that in MySQL. I'm managing MySQL through a web interface provided by my hosting company. I can use SQL statements as well as clicking a few buttons. Any advice is highly appreciated. Thanks a bunch! Jordan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ERROR in LOAD DATA
I use a CPanel Shared hosting environment where MySQL user is not permitted with FILE privilege. Please help me by telling what is the alternate process to LOAD DATA from a text data file to MySQL table. Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JAVA JCONNECT MYSQL SIMPLE EXAMPLE
All,
After installing MYSQL, APACHE, PHP.. My first goal was to get PHP working with MYSQL.
That is pretty easy (Just requires editing the php.ini file) and now you have the Web
Based
PHP communicating with your MYSQL database. So at least you know something is working
and in fact, I got the proper arguments for my JAVA program to connect to MYSQL in a
PHP Script.
Perhaps my oversite but as a Re-training JAVA programmer, with my Simple Goal to
Connect
and extract one field out of the MYSQL database, (knowing that would definately
validate the Jconnect Driver as well) I went searching for a "Simple" test JAVA
program.
Thanks to the JAVA database programming Bible by John Donahue, There was a simple
example
(Although it's not on the wiley.com [source code] web page.. It's easily typed in.)
and some borrowed lines form other programs and the paramaters from the PHP scripts.
Bottom Line.. This is a working, simple example to test the Jconnector to MYSQL.
NOTE: You have to do what they say and move the JAR File to the EXT library
My database in this example is named MYMATCH and the table name is uonline.
So here it is.. It works.. I hope they post this or clean it up and put it into the
distribution of the JCONNECTOR.. SIMPLE. In fact, if someone wants to clean
it up and put the standard "TEST" [Standard meaning its a database already distributed
with MYSQL" into this example.. We all would connect and test or JAVA/MYSQL right away!
Best Regards
David
//package java_databases.ch04;
import java.sql.*;
import javax.sql.*;
import java.awt.*;
import javax.swing.*;
public class jdbcdemo{
public static void main(String args[]){
String dbtime;
String dbUrl = "jdbc:mysql:///mymatch";
String dbClass = "com.mysql.jdbc.Driver";
String query = "Select onlinetime FROM uonline";
try {
String newDbUrl = System.getProperty("com.mysql.jdbc.mymatch.url");
if ((newDbUrl != null) && (newDbUrl.trim().length() != 0)) {
dbUrl = newDbUrl;
}
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection (dbUrl);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
dbtime = rs.getString("onlinetime");
System.out.println(dbtime);
}
con.close();
}
catch(ClassNotFoundException e) {
e.printStackTrace();
}
catch(SQLException e) {
e.printStackTrace();
}
}
}
//package java_databases.ch04;
import java.sql.*;
import javax.sql.*;
import java.awt.*;
import javax.swing.*;
public class jdbcdemo{
public static void main(String args[]){
String dbtime;
String dbUrl = "jdbc:mysql:///mymatch";
String dbClass = "com.mysql.jdbc.Driver";
String query = "Select onlinetime FROM uonline";
// Display the four images in row order in a 2 x 2 grid.
setLayout(new GridLayout(2, 2));
// Add the components, starting with the first entry in the
// first row, the second, etc.
add(new ScrollingImagePanel(david.jpg, 10, 10));
// add(new ScrollingImagePanel(im2, width, height));
// add(new ScrollingImagePanel(im3, width, height));
// add(new ScrollingImagePanel(im4, width, height));
pack();
show();
try {
String newDbUrl = System.getProperty("com.mysql.jdbc.mymatch.url");
if ((newDbUrl != null) && (newDbUrl.trim().length() != 0)) {
dbUrl = newDbUrl;
}
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection (dbUrl);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
dbtime = rs.getString("onlinetime");
System.out.println(dbtime);
}
con.close();
}
catch(ClassNotFoundException e) {
e.printStackTrace();
}
catch(SQLException e) {
e.printStackTrace();
}
}
}
Problem Query - Help Please
When I execute the following query I get duplicate product_id's as shown below: SELECT * FROM product, product_category_xref, category WHERE product_parent_id='' AND product.product_id=product_category_xref.product_id AND category.category_id=product_category_xref.category_id AND product.product_publish='Y' AND product.product_special='Y' ORDER BY product_name DESC\G Results ( As you can see product_id 4139 occurs twice and I desire the product_id's to be unique in this query) I have also included the descriptions of the tables. I would appreciate someone assisting me with a query that works correctly. *** 1. row *** product_id: 4199 vendor_id: 1 product_parent_id: 0 product_sku: ToBeAs product_s_desc: Coming Soon! Preorder Today! A series of essays on the influential thinkers and ideas in modern times. product_desc: Coming Soon! Preorder Today! By R.J. Rushdoony. This monumental work is a series of essays on the influential thinkers and ideas in modern times. The author begins with De Sade, who self-consciously broke with any Christian basis for morality and law. Enlightenment thinking began with nature as the only reality, and Christianity was reduced to one option among many. It was then, in turn, attacked as anti-democratic and anti-freedom for its dogmatic assertion of the supernatural. Literary figures such as Shelly, Byron, Whitman, and more are also examined, for the Enlightenment presented both the intellectual and the artist as replacement for the theologian and his church. Ideas, such as the spirit of the age, truth, reason, Romanticism, persona, and Gnosticism are related to the desire to negate God and Christian ethics. Reading this book will help you understand the need to avoid the syncretistic blending of humanistic philosophy with the Christian faith. Paperback, 230 pages, and indices. product_thumb_image: 62c16392f436313324d9922ecf2f5a30.jpg product_full_image: d99c1de85355c6bc853102a4d85065b3.jpg product_publish: Y product_weight: 0. product_weight_uom: pounds product_length: 0. product_width: 0. product_height: 0. product_lwh_uom: inches product_url: product_in_stock: 0 product_available_date: 0 product_special: y product_discount_id: 0 ship_code_id: NULL cdate: 1057785021 mdate: 1059273555 product_name: To Be As God: A Study of Modern Thought Since the Marquis De Sade product_discount_use: category_id: 7920cfab5c630ca88ceabcfda6b3848d product_id: 4199 product_list: NULL category_id: 7920cfab5c630ca88ceabcfda6b3848d vendor_id: 1 category_name: BOOKS category_description: category_thumb_image: NULL category_full_image: NULL category_publish: Y menu_image_1: menu_image_2: menu_image_3: page_image: cdate: 1028759226 mdate: 1028759226 category_flypage: category_browsepage: *** 2. row *** product_id: 4139 vendor_id: 1 product_parent_id: 0 product_sku: Victims product_s_desc: The decline of Americas public education - how and why. product_desc: By Samuel L. Blumenfeld. Americas most effective critic of public education shows us how Americas public schools were remade by educators who used curriculum to create citizens suitable for their own vision of a utopian socialist society. This collection of essays will show you how and why Americas public education declined. You will see the educator-engineered decline of reading skills. The author describes the causes for the decline and the way back to competent education methodologies that will result in a self-educated, competent, and freedom-loving populace. Paperback, 266 pages, and index. product_thumb_image: 63ad73b92ddd18d83eb6942914bcf277.jpg product_full_image: 1440c376576aba8783f183ff145c248b.jpg product_publish: Y product_weight: 0. product_weight_uom: pounds product_length: 0. product_width: 0. product_height: 0. product_lwh_uom: inches product_url: product_in_stock: 0 product_available_date: 0 product_special: y product_discount_id: 0 ship_code_id: NULL cdate: 1056405288 mdate: 1061947639 product_name: The Victims of Dick and Jane product_discount_use: category_id: 7920cfab5c630ca88ceabcfda6b3848d product_id: 4139 product_list: NULL category_id: 7920cfab5c630ca88ceabcfda6b3848d vendor_id: 1 category_name: BOOKS category_description: category_thumb_image: NULL category_full_image:
