Creating a Table (wot no GUI?)
Excuse my naivety but how do I create a Table in MySQL? I cant find any Text/Windows based programme that allows be to create and edit the table, fields, data types, size etc? Im not looking for anything complex. Many thanks, Ed Edward Hasted Contact Information: E-mail : [EMAIL PROTECTED] Tel : +44 (0) 20 8780 0801 Fax : +44 (0) 20 8780 0802 Mobile : +44 (0) 7711 421180
Re: How to handle Date related types in Java?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Venkata Srinivasa Rao, Yerra wrote: Dear All, Any body know how to handle DATE related types in Java. It seems JDBC driver fails to construct java.sql.TimeStamp object or java.sql.Date object. Thanks in Advance. Best Regards, Srinivas. The driver handles these types just fine. Is there a particular problem you're running into? Please give an example of your code, and the errors that occur, otherwise, we won't be able to be of much help to you :( -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+1LQgtvXNTca6JD8RAkI2AJ9EQ8wMpZfsGK+0TIXgnVF041BrTQCePxzS Ckm1AF5QbBIed/+WzNLRD2s= =j2SX -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [My Solution] Re: Mailing Labels from MySQL database on web
Hi One more suggestion that does work reasonably well. Generate a basic HTML page with a table laid out to suit the labels, in php, with addresses in place Write the page to your webspace as labels.htm (you will need to set permissions to allow this) You then need a program that will open and print HTML pages (From experience MS Word will, and there will be plenty of others too ) then in your php ?php header (Content-type: application/msword); header (Content-Disposition: attachment; filename=report.doc); include('labels.htm'); ? The above is for Word, substitute .doc for a file type associated with your program, or invent one and associate it with your program. Again tested under Windoze, and I should think it will work under other o/s The doc will then open in the program, correctly laid out ready for printing. No special software, or techiness needed I only did single pages, might need some playing with to make it work with multiple pages Also works for any sort of mailmerge, or reports. I only had it working with text and table borders. Be interested in anybody trying it and making it work with images and multiple pages. HTH Peter --- Excellence in internet and open source software --- Sunmaia Birmingham UK www.sunmaia.net tel. 0121-242-1473 International +44-121-242-1473 --- -Original Message- From: Landy Atkinson [mailto:[EMAIL PROTECTED] Sent: 28 May 2003 13:40 To: [EMAIL PROTECTED] Subject: [My Solution] Re: Mailing Labels from MySQL database on web Thanks for all the ideas. Here is a list of the basic paths suggested along with the comments given for each method. Nobody claimed any of these was really good and hoped someone would suggest a better approach. 1. Use MS Word's mail merge and MS Query with MyODBC as the data source. - Only good for Windows MacOSX and requires fairly high end user knowledge to download and install MyODBC and set up MS Query. 2. Use PHP to generate VBScript to control MS Word. - Difficult to set up. 3. Use PHP to build a PDF on the server in the proper Avery label format. - Nobody suggested a PDF file builder for PHP, but suggested there were cgi-scripts available for this purpose. 4. Use one of the reporting packages which can be found at sites like freshmeat.net or sourceforge.net, but nobody responding had tried this. - Nobody suggested a particular package or stated how well this might work. 5. Use PHP to create the CSV file and present it as an HTTP right-click download link on the web page. - This might be a good solution which could work with most any word processor. 6. Use PHP to dynamically produce an HTML page formatted to work with your labels and print it right from there. - Dependent upon which browser and printer is being used, i.e. not very device independent. Number 1 5 seemed like the best of what was suggested for my purposes. I decided to take path #1 and it seems to work pretty well. I've written up instructions for the end user to follow and will give it a try as soon as I get MySQL permissions straightened out. It turns out my ISP sets up only an admin user for the MySQL database on our site and this user does not have GRANT privileges. Mailing label printing works fine when MyODBC is set up using the admin account. I have a Support Ticket in with my ISP to create a couple of users with only the necessary privileges. Once this happens, I will pass it on the the Club Secretary and see if this is really a workable solution. -Landy - Original Message - From: Landy Atkinson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 2:25 PM Subject: Mailing Labels from MySQL database on web I have a club roster database set up on our web site in which the underlying data is in a MySQL database. I am using PHP to access the data and display it on a web page as a phone list, e-mail list, directory etc. Now what I need is to give the club secretary a way to print mailing labels. What is the best way to approach this? As a temporary measure, I used an auxiliary program (MySQL-Front running on a Windows PC) to create a CSV file which I e-mailed to the secretary along with instructions on how to use the file in mail merge document in MS Word to create labels. I thought about writing PHP code to create a CSV formatted output on a web page which the secretary could copy and paste into a document for the mail merge, but it seems there should be a better way. Any suggestions? -Landy -- 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:
Nested query issue in MySQL 4.1
Hello all I have a problem to report. There is a SELECT query that selects data from three tables according to certain criteria. It goes like this: SELECT bglavna.T001, bpolja.ID AS ID2, bpotpolja.TEKST FROM (bglavna INNER JOIN bpolja ON bglavna.ID=bpolja.FK) INNER JOIN bpotpolja ON bpolja.ID=bpotpolja.FK WHERE bpolja.TAG = '200' AND bpotpolja.SUBF = 'a' AND bglavna.ID IN (...); ... is replaced by a subquery that works perfectly and goes like this: SELECT bglavna.ID FROM (bglavna INNER JOIN bpolja ON bglavna.ID = bpolja.FK) INNER JOIN bpotpolja ON bpolja.ID = bpotpolja.FK WHERE bpolja.TAG = '700' AND bpotpolja.SUBF = 'a' AND bpotpolja.TEKST = 'kishon' As it can be seen, two very similar queries executed on same tables. Subquery works perfectly and gives the results immediately. But when i put it in the first query, disk activity does not stop at all and I need to kill the process. Then I execute subquery, gather results and separate them with comma(, ) and put them in the first query and everything works perfectly. So, first query works good when results are explicitly given, and second query works fine all the time, but when I join them together, everything stops functioning. I indexed almost every column but it did not help. These kind of queries allways worked, I don't know what could have happened. Every sugestion is highly appreciated. Thank you Bruno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump suddenly returns error 1064
David Precious [EMAIL PROTECTED] wrote: I hope this isn't an obvious question, but I'm having a hard time figuring this one out. I have a cron job set up to run mysqldump regularly to dump my databases out to a flat file, which is then compressed and passed to our backup server by another script. The cron job runs the command: mysqldump -Aac --add-drop-table --all-databases --opt /tmp/mysqldump This had been working fine for ages. However, now I'm getting an error every time it runs, indicating that there's a problem with the SQL syntax - however, this is the SQL being written out by mysqldump, and nothing on the server has changed which I would expect to cause this - MySQL hasn't been reconfigured or upgraded or anything. The error I'm seeing is: mysqldump: Got error: 1064: You have an error in your SQL syntax near '404 READ /*!32311 LOCAL */' at line 1 when using LOCK TABLES Just for info: MySQL v3.23.51 on Slackware Linux 9.0 (on a high-performance dual-Xeon server). Anyone got any ideas what could be causing this to happen? Table name :) It consists only of digits. Use -Q option of mysqldump. -- 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: Problem with priv_db
Elian Kool [EMAIL PROTECTED] wrote: I just upgraded to 4.0.12. I ran the mysql_fix_privilege_tables script to add the new privileges. Unfortunately, SHOW DATABASES still shows every users all databases. mysql show grants for [EMAIL PROTECTED]; GRANT REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'elian'@'localhost' IDENTIFIED BY PASSWORD '1aa97e373172bdc1' I even flushed the privileges and restarted the MySQL server. Any idea? Please, show me the structure of table 'user'. -- 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]
Update in select
Im attempting to update a table. We perform a select on the table to determine what row to update. update test a set visit_date = now() where a.id in (select b.id from test b where code ='Z') Running this generates an error. You cant specify target table 'test' for update in FROM clause. I can locate documentation on the constraints on referring to the target table in the FROM clause. Can anyone point me to any? In my example the rows the select is the select are not being updated. Is there anyways to perform this type of statement? Thanks. Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Update in select
Subqueries are only available in MySQL 4.1. However, you should be able to write this as follows: UPDATE test1 a, test2 b SET a.visit_date = NOW() WHERE a.id = b.id AND b.code = 'Z'; Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Terry Spencer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 7:26 AM To: [EMAIL PROTECTED] Subject: Update in select Im attempting to update a table. We perform a select on the table to determine what row to update. update test a set visit_date = now() where a.id in (select b.id from test b where code ='Z') Running this generates an error. You cant specify target table 'test' for update in FROM clause. I can locate documentation on the constraints on referring to the target table in the FROM clause. Can anyone point me to any? In my example the rows the select is the select are not being updated. Is there anyways to perform this type of statement? Thanks. Terry -- 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: Creating a Table (wot no GUI?)
I would suggest myCC -- http://www.mysql.com/downloads/mysqlcc.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 6:45 AM To: [EMAIL PROTECTED] Subject: Creating a Table (wot no GUI?) Excuse my naivety but how do I create a Table in MySQL? I can't find any Text/Windows based programme that allows be to create and edit the table, fields, data types, size etc? I'm not looking for anything complex. Many thanks, Ed Edward Hasted Contact Information: E-mail : [EMAIL PROTECTED] Tel : +44 (0) 20 8780 0801 Fax : +44 (0) 20 8780 0802 Mobile : +44 (0) 7711 421180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Could somebody tell me why I am getting this error
It worked yesterday but not today: mysql LOAD DATA LOCAL INFILE users INTO TABLE users; ERROR 1148: The used command is not allowed with this MySQL version Jesse Hardy [EMAIL PROTECTED] 704-348-8085 - This e-mail message is intended only for the personal use of the recipient(s) named above. If you are not an intended recipient, you may not review, copy or distribute this message. If you have received this communication in error, please notify the Hearst Service Center ([EMAIL PROTECTED]) immediately by e-mail and delete the original message. - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL CC question
Where do you change the default LIMIT value for queries. I am using 0.9.1-beta. Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump suddenly returns error 1064 [sorted]
snip mysqldump: Got error: 1064: You have an error in your SQL syntax near '404 READ /*!32311 LOCAL */' at line 1 when using LOCK TABLES Anyone got any ideas what could be causing this to happen? snip Table name :) It consists only of digits. Use -Q option of mysqldump. Aaahh! I can't believe I didn't get that one. I've been trying to figure it out for ages! Of course, it all makes sense now. And, looking back through the cron job output, it started doing it right around the time that table got created. Many thanks for your help Victoria, you've saved me from tearing out a considerable amount of hair! Cheers David P -- David Precious [EMAIL PROTECTED] http://www.preshweb.co.uk A mind is a terrible thing to ... er ... h Now playing: Oasis - Whatever (Live, Knebworth) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL CC question
Where do you change the default LIMIT value for queries. I am using 0.9.1-beta. right click on the server name and click on edit. Click on the MySQL Options tab. Scott Pippin [EMAIL PROTECTED]
Red Hat ES 2.1 and MySQL
I am about to install Red Hat Enterprise Server 2.1. I would like to put MySQL on it. I was wondering if there were any Red Hat settings I should be aware of as I install the OS. Thanks, Scott Pippin [EMAIL PROTECTED]
Storing procedures
How can I store procedures in mysql? Themis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL CC question
Thanks, that was what I was looking for. It has changed since earlier versions then. Thanks again, Ed On Wed, 28 May 2003, Scott Pippin wrote: Where do you change the default LIMIT value for queries. I am using 0.9.1-beta. right click on the server name and click on edit. Click on the MySQL Options tab. Scott Pippin [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing procedures
By waiting or reading : http://www.mysql.com/doc/en/ANSI_diff_Triggers.html Jerry - Original Message - From: Themis Kafetzopoulos [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 3:37 PM Subject: Storing procedures How can I store procedures in mysql? Themis -- 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]
table copy
I made the mistake of copying the MYI, MYD, and frm files of a table while the mysql server was running. Unfortunately the table is now reporting the following error: ./mysqlshow: Cannot list columns in db: rt2, table: Keywords: Incorrect information in file: 'Table.frm' What is the easiest way to recover the table? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Redirect to IP in table
Quick, simple question for the masses I'm sure. I've been having trouble assigning a property to a SELECT statement which pulls an IP from a table, then redirecting the users browser to that value stored in rstemp. % dim rstemp % % rstemp = MyConn.execute(SELECT group.hostip FROM test.group, test.user WHERE user.groupid = group.groupid AND user.username=' currentUser ' ) % response.Redirect(rstemp) 'returns Type Mismatch response.Write(rstemp) 'returns default property not found Any thoughts on what I'm doing wrong? Any help would be greatly appreciated. Sincerely, Reuben
Re: Mailing Labels from MySQL database on web
Le mer 28/05/2003 à 14:34, [EMAIL PROTECTED] a écrit : From: Landy Atkinson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Mailing Labels from MySQL database on web Date: 27 May 2003 15:25:20 -0400 What is the best way to approach this? As a temporary measure, I used an auxiliary program (MySQL-Front running on a PC) to create a CSV file on my PC which I e-mailed to the secretary along with instructions on how to use the file in mail merge document in MS Word to create labels. I thought about writing PHP code to create a CSV formatted output on a web page which the secretary could copy and paste into a document for the mail merge, but it seems there should be a better way. How about running OpenOffice.org on your sec's computer, and using the database facilities to do mail merge from there via the remote MySQL database. You can connect to the database via ODBC or JDBC, and OpenOffice.org has a labels wizard or template that can help you do this. It is even possible to run OpenOffice.org on the server as a headless display using an XVFB device, and then you could program it via the Basic or Java API to perform the work for you. Check out the mailing lists on the API, and UNO sections on the OpenOffice.org website for more info. Someone already has a PHP script to pass files to OOo to convert them to other formats, including to PDF. Alex Thurgood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: table copy
REPAIR TABLE tbl_name QUICK; You could try that or check out myismchk (sp?) --Joe -- Joe Stump [EMAIL PROTECTED] http://www.joestump.net Label makers are proof God wants Sys Admins to be happy. -Original Message- From: Cassily, Ryan [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 7:56 AM To: MySQL (E-mail) Subject: table copy I made the mistake of copying the MYI, MYD, and frm files of a table while the mysql server was running. Unfortunately the table is now reporting the following error: ./mysqlshow: Cannot list columns in db: rt2, table: Keywords: Incorrect information in file: 'Table.frm' What is the easiest way to recover the table? Thanks. -- 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: Creating a Table (wot no GUI?)
Hello I would suggest SQLyog at http://www.webyog.com/sqlyog. Its FREE and find it more powerful and easy to use then MyCC Karam --- Mike Hillyer [EMAIL PROTECTED] wrote: I would suggest myCC -- http://www.mysql.com/downloads/mysqlcc.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 6:45 AM To: [EMAIL PROTECTED] Subject: Creating a Table (wot no GUI?) Excuse my naivety but how do I create a Table in MySQL? I can't find any Text/Windows based programme that allows be to create and edit the table, fields, data types, size etc? I'm not looking for anything complex. Many thanks, Ed Edward Hasted Contact Information: E-mail : [EMAIL PROTECTED] Tel : +44 (0) 20 8780 0801 Fax : +44 (0) 20 8780 0802 Mobile : +44 (0) 7711 421180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Update in select
Thanks for the help, that close to what Im after. I however simplified the problem too much in my previous question and Im still not there. :) Im imitating a 'tree' structure. The table has rows - ID and parent_ID. The depth of the tree is only three layers (parent - child - grandchild). There may be any number of parents. Various solution exists to the tree problem that involve adding a additional row (or two) to the table. This is not an option available to me at this point. Under certain circumstances if the parent is updated, I also need to update the children and grandchildren. So far I have (using a select while testing :) select t1.*, t2.*, t3.* from tree t1, tree t2, tree t3 where t1.id = 10 and t2.parent_id = t1.id and t3.parent_id = t2.id However this only returns the parent row. If I instead use select t2.* the children are returned If I instead use select t3.* the grandchildren are returned So my problem is 1. why is only the result selected for the front table; t1 returns only parent, t2 return only the children 2. this fails for parent that have no children, and children that have no grandchildren. Any thought, suggestion, points are greatly appreciated. Im using 4.1. Thanks Terry -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 2:53 PM To: Terry Spencer; [EMAIL PROTECTED] Subject: RE: Update in select Subqueries are only available in MySQL 4.1. However, you should be able to write this as follows: UPDATE test1 a, test2 b SET a.visit_date = NOW() WHERE a.id = b.id AND b.code = 'Z'; Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Terry Spencer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 7:26 AM To: [EMAIL PROTECTED] Subject: Update in select Im attempting to update a table. We perform a select on the table to determine what row to update. update test a set visit_date = now() where a.id in (select b.id from test b where code ='Z') Running this generates an error. You cant specify target table 'test' for update in FROM clause. I can locate documentation on the constraints on referring to the target table in the FROM clause. Can anyone point me to any? In my example the rows the select is the select are not being updated. Is there anyways to perform this type of statement? Thanks. Terry -- 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]
Getting data in excel into a mysql table?
I have an MS excel file with 8 columns and 500 rows I have a table called 'names' setup in mysql which i have created with 8 columns. I now need to get my data from excel into that table. How would u do this?. I had a look in mysqlcc to see if there was an import option but couldn't see one Advice v much appreciated thanks Dan
Re: How to secure a MySQL database from people with physical acce ss
On Tue, May 27, 2003 at 03:43:14PM -0500, mos wrote: Correct, which is why I have a means of compressing and *encrypting* the .exe file. I can also lock it to the person's machine (or server) so it won't fall into the wrong hands. All such methods have been broken. If they weren't, the major software companies would all be using them to prevent cracking ... remember that the EXE has to decrypt itself. Usually it contains an obfuscated series of jumps that decrypt its image in memory while at the same time changing word offsets within the image so the decompile looks wrong from within a debugger. A couple anti-tracing measures and it makes it pretty hard to reverse- engineer, but people still do it. Other databases that use encryption will decrypt the information when a row is accessed, so there is no unencrypted data lying on the hard drive. It is extremely fast and I don't notice a speed difference between encrypted and If I were going to trust anything to be secure, it would involve data that was encrypted to the public keys of the users who deserve access *before* being sent to the database for storage. This of course prevents the use of indexing. Anything else has the problems others have mentionned. -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ pgp0.pgp Description: PGP signature
Re: Mailing Labels from MySQL database on web
On Tue, May 27, 2003 at 05:02:47PM -0500, Cal Evans wrote: 2: BETTER Use PHP to build a PDF on the server in the proper Avery label format and then serve it up through the web server. If you get this working, make sure you share how you did it. Generating postscript from PHP is easy enough; ps2pdf on *nix platforms will do the rest. 3: Better Use a reporting package. There are several reporting packages for MySQL. Check freshmeat.net or sourceforge.net to find the one best for you. May I recommend checking the ability to fetch the data directly from Word yourself with ADO and ODBC? -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ pgp0.pgp Description: PGP signature
RE: Getting data in excel into a mysql table?
Export the worksheet to CSV and use the LOAD DATA command in MySQL to import it. That is how I import all but my most complex excel spreadsheets. Mike Hillyer www.vbmysql.com -Original Message- From: D Crompton [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 9:51 AM To: [EMAIL PROTECTED] Subject: Getting data in excel into a mysql table? I have an MS excel file with 8 columns and 500 rows I have a table called 'names' setup in mysql which i have created with 8 columns. I now need to get my data from excel into that table. How would u do this?. I had a look in mysqlcc to see if there was an import option but couldn't see one Advice v much appreciated thanks Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Signal 11 on replicated MySQL Slave (4.0.13-standard)
Description: MySQL crashes out with signal 11 when replication has been set up as a slave. How-To-Repeat: Follow instructions for replicated slave, then simply log out. If you started mysqld (even if in nohup or background) it still sig11's - options used were: ./bin/safe_mysqld --user=mysql --skip-slave-start Fix: It doesn't seem to affect the replication, which can be restarted with slave start, so other than being aware of it, it doesn't seem to be too bad. However, I'll know in a day or so when I can check how the replication is going. Submitter-Id: submitter ID Originator:Joel Rowbottom Organization: Fotopic Ltd. www.fotopic.net MySQL support: none Synopsis: mysqld crashes with signal 11 on replicated slave Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.13-standard (Official MySQL-standard binary) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: Linux utonium 2.4.20 #2 Thu Apr 3 14:54:42 BST 2003 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Dec 31 2001 /lib/libc.so.6 - libc-2.2.3.so -rwxr-xr-x1 root root 4783716 May 26 2001 /lib/libc-2.2.3.so -rw-r--r--1 root root 24721042 May 26 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 May 26 2001 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB backup - the best way.
Alternatively, use InnoDB Hot Backup: http://www.innodb.com/hotbackup.html This will let you take a real-time backup of your InnoDB tables without taking the server down. Chris Rafal Jank wrote: Dnia Wed, 28 May 2003 12:57:59 +0200 Jarek Jarzebowski [EMAIL PROTECTED] zezna/a co nastpuje: Hi, I have MySQL 4.0.12 working on Debian Linux box. DB server uses MYISAM and InnoDB tables. MySQL docs shows what is the best way to backup InnoDB tables but I don't want to shutdown db server every backup time. I suppose it is not a good idea to just copy database files and innodb log files to the safe place because of unwanted rollbacks danger. What if I use mysqldump to backup databases data, and then I copy innodb log files to the safe place? I suppose I should flush tables first. You can mysqldump InnoDB tables too. Copying InnoDB files (data or redologs) without shuting down mysql is a bad idea. You can also use mysql replication if you have spare server and do a cold backup from it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Update in select
Well, getting back only table one could be because you can only select * from one table at a time, I am not sure. If so you need to either spell out your columns specifically, or use a UNION in the query. As for the fail when there are no children, you need a LEFT JOIN. http://www.mysql.com/doc/en/UNION.html http://www.mysql.com/doc/en/JOIN.html Mike Hillyer www.vbmysql.com -Original Message- From: Terry Spencer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 9:44 AM To: Mike Hillyer; MySQL (E-mail) Subject: RE: Update in select Thanks for the help, that close to what Im after. I however simplified the problem too much in my previous question and Im still not there. :) Im imitating a 'tree' structure. The table has rows - ID and parent_ID. The depth of the tree is only three layers (parent - child - grandchild). There may be any number of parents. Various solution exists to the tree problem that involve adding a additional row (or two) to the table. This is not an option available to me at this point. Under certain circumstances if the parent is updated, I also need to update the children and grandchildren. So far I have (using a select while testing :) select t1.*, t2.*, t3.* from tree t1, tree t2, tree t3 where t1.id = 10 and t2.parent_id = t1.id and t3.parent_id = t2.id However this only returns the parent row. If I instead use select t2.* the children are returned If I instead use select t3.* the grandchildren are returned So my problem is 1. why is only the result selected for the front table; t1 returns only parent, t2 return only the children 2. this fails for parent that have no children, and children that have no grandchildren. Any thought, suggestion, points are greatly appreciated. Im using 4.1. Thanks Terry -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 2:53 PM To: Terry Spencer; [EMAIL PROTECTED] Subject: RE: Update in select Subqueries are only available in MySQL 4.1. However, you should be able to write this as follows: UPDATE test1 a, test2 b SET a.visit_date = NOW() WHERE a.id = b.id AND b.code = 'Z'; Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Terry Spencer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 7:26 AM To: [EMAIL PROTECTED] Subject: Update in select Im attempting to update a table. We perform a select on the table to determine what row to update. update test a set visit_date = now() where a.id in (select b.id from test b where code ='Z') Running this generates an error. You cant specify target table 'test' for update in FROM clause. I can locate documentation on the constraints on referring to the target table in the FROM clause. Can anyone point me to any? In my example the rows the select is the select are not being updated. Is there anyways to perform this type of statement? Thanks. Terry -- 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: table copy
I have tried using myisamchk, but I do not believe it is fixing the problem because I am still getting the same errors. Here's the output I get from it: bash-2.05# ../../bin/myisamchk -r Table.frm - recovering (with sort) MyISAM-table 'Table.frm' Data records: 111 - Fixing index 1 - Fixing index 2 - Fixing index 3 - Fixing index 4 ./mysqlshow: Cannot list columns in db: rt2, table: Keywords: Incorrect information in file: 'Table.frm' -Original Message- From: Joe Stump [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 2:03 PM To: Cassily, Ryan; MySQL (E-mail) Subject: RE: table copy REPAIR TABLE tbl_name QUICK; You could try that or check out myismchk (sp?) --Joe -- Joe Stump [EMAIL PROTECTED] http://www.joestump.net Label makers are proof God wants Sys Admins to be happy. -Original Message- From: Cassily, Ryan [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 7:56 AM To: MySQL (E-mail) Subject: table copy I made the mistake of copying the MYI, MYD, and frm files of a table while the mysql server was running. Unfortunately the table is now reporting the following error: ./mysqlshow: Cannot list columns in db: rt2, table: Keywords: Incorrect information in file: 'Table.frm' What is the easiest way to recover the table? Thanks. -- 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: Getting data in excel into a mysql table?
Hello Export the data in CSV from Excel and then you can try SQLyog at http://www.webyog.com/sqlyog It has a very good interface to import data from CSV file. Karam --- D Crompton [EMAIL PROTECTED] wrote: I have an MS excel file with 8 columns and 500 rows I have a table called 'names' setup in mysql which i have created with 8 columns. I now need to get my data from excel into that table. How would u do this?. I had a look in mysqlcc to see if there was an import option but couldn't see one Advice v much appreciated thanks Dan __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: table copy
You copied corrupted data. Shutdown the server and make a copy again, or restore from a backup. Myisamchk will probably not work in this situation even if you use Myisamchk -o -f ---Original Message- --From: Cassily, Ryan [mailto:[EMAIL PROTECTED] --Sent: Wednesday, May 28, 2003 10:28 AM --To: Joe Stump; MySQL (E-mail) --Subject: RE: table copy -- --I have tried using myisamchk, but I do not believe it is fixing the --problem because I am still getting the same errors. Here's the output I --get from it: -- -- --bash-2.05# ../../bin/myisamchk -r Table.frm --- recovering (with sort) MyISAM-table 'Table.frm' --Data records: 111 --- Fixing index 1 --- Fixing index 2 --- Fixing index 3 --- Fixing index 4 -- -- --./mysqlshow: Cannot list columns in db: rt2, table: Keywords: Incorrect --information in file: 'Table.frm' -- -- ---Original Message- --From: Joe Stump [mailto:[EMAIL PROTECTED] --Sent: Wednesday, May 28, 2003 2:03 PM --To: Cassily, Ryan; MySQL (E-mail) --Subject: RE: table copy -- -- --REPAIR TABLE tbl_name QUICK; -- --You could try that or check out myismchk (sp?) -- Joe -- --Joe Stump [EMAIL PROTECTED] --http://www.joestump.net --Label makers are proof God wants Sys Admins to be happy. -- ---Original Message- --From: Cassily, Ryan [mailto:[EMAIL PROTECTED] --Sent: Wednesday, May 28, 2003 7:56 AM --To: MySQL (E-mail) --Subject: table copy -- -- --I made the mistake of copying the MYI, MYD, and frm files of a table --while --the mysql server was running. Unfortunately the table is now reporting --the --following error: -- --./mysqlshow: Cannot list columns in db: rt2, table: Keywords: Incorrect --information in file: 'Table.frm' -- --What is the easiest way to recover the table? Thanks. -- --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: How to use stored procedure in MySQL
mySQL does not support stored procedures your going to have to write code to supplement the behavior or wait until mySQL 5.0. ---Original Message- --From: rajesh_sharma [mailto:[EMAIL PROTECTED] --Sent: Wednesday, May 28, 2003 10:58 AM --To: [EMAIL PROTECTED] --Subject: How to use stored procedure in MySQL -- --Hi --I have used stored procedure in my application -- with MS-SQL Server and I -- want to use it with My SQL. -- Please let me know if any way is there to use stored -- procedure in MySQL. or -- if any other way is there. -- -- Thanx and regards : -- Rajesh Sharma -- Asst. System Engineer -- Tata Consultancy Services -- Pune,India -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating a Table (wot no GUI?)
Assuming that MySQL is on your computer (not on a remote host), run MySQL from the command line. If you're accessing MySQL remotely, you can issue CREATE statements from most scripting languages (PHP, for one), the same languages you'll likely need to interact with the DB later. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, 28 May, 2003 08:45 Subject: Creating a Table (wot no GUI?) Excuse my naivety but how do I create a Table in MySQL? I can't find any Text/Windows based programme that allows be to create and edit the table, fields, data types, size etc? I'm not looking for anything complex. Many thanks, Ed Edward Hasted Contact Information: E-mail : [EMAIL PROTECTED] Tel : +44 (0) 20 8780 0801 Fax : +44 (0) 20 8780 0802 Mobile : +44 (0) 7711 421180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
local vs %
Taken from manual: mysql GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql GRANT ALL PRIVILEGES ON *.* TO monty@% - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; A full superuser who can connect to the server from anywhere, but who must use a password 'some_pass' to do so. Note that we must issue GRANT statements for both [EMAIL PROTECTED] and monty@%. If we don't add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence when we connect from the local host, because it has a more specific Host field value and thus comes earlier in the user table sort order. - The above implies you should have 2 permissions for every user, i.e. one for local and one for % Is this really necessary? We have root user and one other user called mark. A lookup therefore shows 4, 2 for each: [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] Should we really have 2 permissions for every user?. Doesn't the root just need access to localhost only?. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld stops taking connections
I have a system that when I start mysqld, it works fine for a while (anywhere from 10 minutes to 2 hours) and then stops taking connections. I get the Can't connect to local MySQL server through socket ... however, the socket is still there. What's even weirder, is that any processes that are still connected, continue to work fine. The MySQL server installed is 3.23.56 binary for AIX 5.1 Standard downloaded off of a mysql.com mirror. Anyone have any ideas on how to solve this problem or why it might be happening? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY and ORDER BY not working together
I'm having some trouble with a GROUP BY clause. My database has a main table ('grants') with grant listings. Each grant has one listing, including a grantid column. A second table ('dates') holds date info about each grant. 'grants' to 'dates' is one-to-many as a grant can have several submission dates. The tables are related by the grantid. This table also has a yearday column for sorting. (Some of the grants don't have years attached as they are ongoing.) Here is a sample SELECT statement that's not working the way I want it to. SELECT grants.grantid, grants.refnum, dates.subdaynum FROM grants, dates WHERE (grants.agency = NIH-O AND grants.grantid = dates.grantid) GROUP BY grants.grantid ORDER BY dates.yearday; This gives me one listing per grant but they are not sorted correctly. From what I can figure out, mysql is only looking at the first occurence of a grant in the 'dates' table and uses the yearday from that listing. If the listings are already in ascending order, it works but if they are stored in descending order, my sort can be off. It works the way it's supposed to when I remove the GROUP BY clause but I get multiple listings for each grant. I hope this is clear enough. I'm guessing there's a better way to do this that works the way I want it to. Thanks. -- Peter Fleck Webmaster | University of Minnesota Cancer Center Dinnaken Office Bldg. 925 Delaware St. SE Minneapolis, MN 55414 612-625-8668 | [EMAIL PROTECTED] | www.cancer.umn.edu Campus Mail: MMC 806 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with subquery
I am trying to do what seems like a simple Select: SELECT * FROM TAB1 WHERE TAB1_ID IN (SELECT TAB2_ID FROM TAB2 WHERE TAB2_ID 2) I know I this select is very simple, but I need to make this one work before I can tackle my 'real' query where the subquery is essential. I have MySQL version 4.1.0 alpha Thank you all in advance!
Re: GROUP BY and ORDER BY not working together
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 28 May 2003 14:17:55 -0500, Peter Fleck wrote: The results you wanted and the results you get were not very clear. Some examples would have gone a long way. SELECT grants.grantid, grants.refnum, dates.subdaynum FROM grants, dates WHERE (grants.agency = NIH-O AND grants.grantid = dates.grantid) GROUP BY grants.grantid ORDER BY dates.yearday; The results are sorted within the groups by yearday. However, the groups themselves are not in any particular order. Try this: order by grants.grantid,dates.yearday; - -- 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+1RI7sxxMki0foKoRAlQbAJ92tXMjfXUVLwct/cHG7dtvwkUpUwCg5Um/ VBH0ZcarjD+r805mEd/wg7g= =0SHm -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with subquery
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 28 May 2003 15:18:16 -0400, Hassan Farha wrote: SELECT * FROM TAB1 WHERE TAB1_ID IN (SELECT TAB2_ID FROM TAB2 WHERE TAB2_ID 2) I know I this select is very simple, ... So what's the problem? - -- 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+1RJ4sxxMki0foKoRAkk/AJ4tVDPmziZsSmTze2KpAQge6AQb5wCePN7O c8oEF5CeBo915LfDeHj0lLo= =Xb4Z -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY and ORDER BY not working together
On 28 May 2003 at 14:17, Peter Fleck wrote: SELECT grants.grantid, grants.refnum, dates.subdaynum FROM grants, dates WHERE (grants.agency = NIH-O AND grants.grantid = dates.grantid) GROUP BY grants.grantid ORDER BY dates.yearday; This gives me one listing per grant but they are not sorted correctly. From what I can figure out, mysql is only looking at the first occurence of a grant in the 'dates' table and uses the yearday from that listing. Yes, that seems reasonable. Since you're not telling it which yearday you want, you're saying it doesn't matter which one it uses. In most cases you wouldn't do that unless you expected all the values of yearday to be the same for a particular value of grantid. MySQL does its best, but it can't read your mind. You have to tell it what you want. If you want the latest yearday, for example, then select MAX(dates.yearday) AS max_yearday and order by that. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to perform the following Query in MySQL
Hello, I am studying the possible migration of a system using another database engine to MySQL for various reasons. However, I have several instances of the following query spread throughout my application that do not work in mysql. The query is: Select count(*) from customer where customer.id in (select customer.id where customer.status=A and customer.id = customer.opt_id MINUS select vendor.id from vendor) The error I receive is Error Code 1064, DB Error State = 42000 indicating an error in my SQL syntax. I checked the documentation and found that for mysql the SET MEMBERSHIP CLAUSE (IN) can only accept lists made of constants. However, my SQL reference shows this query as a valid SQL2 type query. I have two questions: 1. Can anyone tell me why is this query returning an error in mysql? 2. Can anyone suggest a way to write this query in a mysql friendly way? TIA Jaime - Jaime J. Maiz 630 979 2088 mailto:[EMAIL PROTECTED] IH 4F-310
Re: GROUP BY and ORDER BY not working together
At 3:50 PM -0400 5/28/03, Keith C. Ivey wrote: On 28 May 2003 at 14:17, Peter Fleck wrote: SELECT grants.grantid, grants.refnum, dates.subdaynum FROM grants, dates WHERE (grants.agency = NIH-O AND grants.grantid = dates.grantid) GROUP BY grants.grantid ORDER BY dates.yearday; This gives me one listing per grant but they are not sorted correctly. From what I can figure out, mysql is only looking at the first occurence of a grant in the 'dates' table and uses the yearday from that listing. Yes, that seems reasonable. Since you're not telling it which yearday you want, you're saying it doesn't matter which one it uses. In most cases you wouldn't do that unless you expected all the values of yearday to be the same for a particular value of grantid. MySQL does its best, but it can't read your mind. You have to tell it what you want. If you want the latest yearday, for example, then select MAX(dates.yearday) AS max_yearday and order by that. Thanks. That does solve the problem. I use MIN to sort ascending but I get the concept. GROUP BY is hard for me to understand and the online manual isn't very clear. -- Peter Fleck Webmaster | University of Minnesota Cancer Center Dinnaken Office Bldg. 925 Delaware St. SE Minneapolis, MN 55414 612-625-8668 | [EMAIL PROTECTED] | www.cancer.umn.edu Campus Mail: MMC 806 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to perform the following Query in MySQL
MySQL only supports sub-queries as of v4.1. What version are you running now? While I am not terribly familiar with joins, I believe that one, properly executed, could solve your problem. In fact, a similar question was raised earlier today. Search the archives for a post with the subject Update in select Good luck! Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Jaime J. Maiz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, 28 May, 2003 15:58 Subject: How to perform the following Query in MySQL Hello, I am studying the possible migration of a system using another database engine to MySQL for various reasons. However, I have several instances of the following query spread throughout my application that do not work in mysql. The query is: Select count(*) from customer where customer.id in (select customer.id where customer.status='A' and customer.id = customer.opt_id MINUS select vendor.id from vendor) The error I receive is Error Code 1064, DB Error State = 42000 indicating an error in my SQL syntax. I checked the documentation and found that for mysql the SET MEMBERSHIP CLAUSE (IN) can only accept lists made of constants. However, my SQL reference shows this query as a valid SQL2 type query. I have two questions: 1. Can anyone tell me why is this query returning an error in mysql? 2. Can anyone suggest a way to write this query in a mysql friendly way? TIA Jaime - Jaime J. Maiz630 979 2088 mailto:[EMAIL PROTECTED] IH 4F-310 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with subquery
WHERE TAB2_ID 2 is the problem. Do you want = or or what? Also your version of MySQL must support sub selects. James Moe wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 28 May 2003 15:18:16 -0400, Hassan Farha wrote: SELECT * FROM TAB1 WHERE TAB1_ID IN (SELECT TAB2_ID FROM TAB2 WHERE TAB2_ID 2) I know I this select is very simple, ... So what's the problem? - -- 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+1RJ4sxxMki0foKoRAkk/AJ4tVDPmziZsSmTze2KpAQge6AQb5wCePN7O c8oEF5CeBo915LfDeHj0lLo= =Xb4Z -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrading to v 4.1
Is it recommended that I upgrade MySQL 3.23.53 to 4.1.x? Are then any special notes I might remember when doing so? (Mac OS X Server 10.2.6) Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading to v 4.1
on 05/28/2003 1:51 PM, Ted Rogers at [EMAIL PROTECTED] wrote: Is it recommended that I upgrade MySQL 3.23.53 to 4.1.x? Are then any special notes I might remember when doing so? (Mac OS X Server 10.2.6) Ted Yeah, I posted this and no reply, but you need to do this Subject : mysql_fix_privilege_tables behaves odd, explain this Well this is very interesting At the start of the script they have root_password=$1 host=localhost user=root I added in just below the above echo -- echo $root_password echo -- Then if you try sh mysql_fix_privilege_tables -p somepassword Guess what you get... -- -p somepassword -- So it seems the script takes the whole argument... sh mysql_fix_privilege_tables my_root_pass Works fine Is this standard behavior? The Script says echo If you get 'Access denied' errors, you should run this script again echo and give the MySQL root user password as an argument! Does not really tell you there is some special way to do it. What is the heck is $1 anyway? And why is it a literally quoted value? - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Connector/J 3.0.8 STABLE Has Been Released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL Connector/J 3.0.8, a new version of the Type-IV all-Java JDBC driver for MySQL has been released. It is now available in source and binary form from the Connector/J download pages at http://www.mysql.com/downloads/api-jdbc-stable.html and mirror sites. Note that not all mirror sites may be up to date at this point of time - if you can't find this version on some mirror, please try again later or choose another download site. - From the changelog: - Allow bogus URLs in Driver.getPropertyInfo(). - Return list of generated keys when using multi-value INSERTS with Statement.getGeneratedKeys(). - Use JVM charset with filenames and 'LOAD DATA [LOCAL] INFILE' - Fix infinite loop with Connection.cleanup(). - Changed Ant target 'compile-core' to 'compile-driver', and made testsuite compilation a separate target. - Fixed result set not getting set for Statement.executeUpdate(), which affected getGeneratedKeys() and getUpdateCount() in some cases. - Unicode character 0x in a string would cause the driver to throw an ArrayOutOfBoundsException (Bug #378) - Return correct amount of generated keys when using 'REPLACE' statements. - Fix problem detecting server character set in some cases. - Fix row data decoding error when using _very_ large packets. - Optimized row data decoding. - Issue exception when operating on an already-closed prepared statement. - Fixed SJIS encoding bug, thanks to Naoto Sato. - Optimized usage of EscapeProcessor. - Fixed numbering of KEY_SEQ column in DBMD.getImported/Exported keys (thanks to Dave Schoorl). - Allow multiple calls to Statement.close() - Use hex escaping for PreparedStatement.setBytes()/setBinaryStream() when the character set is Shift_JIS, workaround for parsing bug in server. - Added whitespace insensitivity to EscapeProcessor. Have fun with it! -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+1SYqtvXNTca6JD8RAnCdAKCx+3ldUwo/kYDC5oBk5h4iepmIxgCdFi9d FMLHyUOxQBmx8gN1nVUlnp8= =toBB -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlhotcopy script problem
Greetings: Attempting to run mysqlhotcopy on my RH 7.3 server/ MySQL 4.012 generates the following: install_driver(mysql) failed: Can't load '/usr/lib/perl5/site_perl/5.6.1/i386-linux/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.10: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.6.1/i386-linux/DynaLoader.pm line 206. at (eval 1) line 3 Compilation failed in require at (eval 1) line 3. Perhaps a required shared library or dll isn't installed where expected However, mysql.so is present in the proper path so I suspect the error message might be a red-herring. As well, the requisite RPMs are present, ie: perl-DBI perl-DBD-MySQL Has anyone experienced this error and gotten past it? Much thanks, -stu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with subquery
Well noticed, Gerald. I glossed over that bcs the entire query was in caps. I find it best to capitalize only SQL reserved words, which might be a good suggestion to the original poster. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: gerald_clark [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, 28 May, 2003 16:49 Subject: Re: problem with subquery WHERE TAB2_ID 2 is the problem. Do you want = or or what? Also your version of MySQL must support sub selects. James Moe wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 28 May 2003 15:18:16 -0400, Hassan Farha wrote: SELECT * FROM TAB1 WHERE TAB1_ID IN (SELECT TAB2_ID FROM TAB2 WHERE TAB2_ID 2) I know I this select is very simple, ... So what's the problem? - -- 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+1RJ4sxxMki0foKoRAkk/AJ4tVDPmziZsSmTze2KpAQge6AQb5wCePN7O c8oEF5CeBo915LfDeHj0lLo= =Xb4Z -END PGP SIGNATURE- -- 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: upgrading to v 4.1
AHHH! I bet that is why I was having such a horrible time last night trying to upgrade! I finally said fsck it and reverted back to 3.23.55... I don't use a root password on localhost... Now I'll try your suggestions and see if it works. *sigh* *crosses fingers* -Original Message- From: Scott Haneda [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 1:59 PM To: MySql Subject: Re: upgrading to v 4.1 on 05/28/2003 1:51 PM, Ted Rogers at [EMAIL PROTECTED] wrote: Is it recommended that I upgrade MySQL 3.23.53 to 4.1.x? Are then any special notes I might remember when doing so? (Mac OS X Server 10.2.6) Ted Yeah, I posted this and no reply, but you need to do this Subject : mysql_fix_privilege_tables behaves odd, explain this Well this is very interesting At the start of the script they have root_password=$1 host=localhost user=root I added in just below the above echo -- echo $root_password echo -- Then if you try sh mysql_fix_privilege_tables -p somepassword Guess what you get... -- -p somepassword -- So it seems the script takes the whole argument... sh mysql_fix_privilege_tables my_root_pass Works fine Is this standard behavior? The Script says echo If you get 'Access denied' errors, you should run this script again echo and give the MySQL root user password as an argument! Does not really tell you there is some special way to do it. What is the heck is $1 anyway? And why is it a literally quoted value? - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- 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: MySQL Connector/J 3.0.8 STABLE Has Been Released
Where does Connector/J fit into the MySQL3.23.x versus MySQL4.x client libraries? Someone mentioned to me today that mod_php is still compiled with the old 3.23 client even though it may be talking to a 4.x server, so I'm wondering if Connector/J does anything different depending on whether it's talking to a 3.23 server or a 4.x server. --- Mark Matthews [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL Connector/J 3.0.8, a new version of the Type-IV all-Java JDBC driver for MySQL has been released. It is now available in source and binary form from the Connector/J download pages at http://www.mysql.com/downloads/api-jdbc-stable.html and mirror sites. Note that not all mirror sites may be up to date at this point of time - if you can't find this version on some mirror, please try again later or choose another download site. - From the changelog: - Allow bogus URLs in Driver.getPropertyInfo(). - Return list of generated keys when using multi-value INSERTS with Statement.getGeneratedKeys(). - Use JVM charset with filenames and 'LOAD DATA [LOCAL] INFILE' - Fix infinite loop with Connection.cleanup(). - Changed Ant target 'compile-core' to 'compile-driver', and made testsuite compilation a separate target. - Fixed result set not getting set for Statement.executeUpdate(), which affected getGeneratedKeys() and getUpdateCount() in some cases. - Unicode character 0x in a string would cause the driver to throw an ArrayOutOfBoundsException (Bug #378) - Return correct amount of generated keys when using 'REPLACE' statements. - Fix problem detecting server character set in some cases. - Fix row data decoding error when using _very_ large packets. - Optimized row data decoding. - Issue exception when operating on an already-closed prepared statement. - Fixed SJIS encoding bug, thanks to Naoto Sato. - Optimized usage of EscapeProcessor. - Fixed numbering of KEY_SEQ column in DBMD.getImported/Exported keys (thanks to Dave Schoorl). - Allow multiple calls to Statement.close() - Use hex escaping for PreparedStatement.setBytes()/setBinaryStream() when the character set is Shift_JIS, workaround for parsing bug in server. - Added whitespace insensitivity to EscapeProcessor. Have fun with it! -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+1SYqtvXNTca6JD8RAnCdAKCx+3ldUwo/kYDC5oBk5h4iepmIxgCdFi9d FMLHyUOxQBmx8gN1nVUlnp8= =toBB -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: upgrading to v 4.1
Ted, 4.1 is not in production release yet. So, for home development to test or develop for new features specific to 4.1, then OK. If your database server is for production use serving critical data, then use 4.0.13 which is the current production release. Follow the instructions carefully and read http://www.mysql.com/doc/en/Upgrading-from-3.23.html and http://www.mysql.com/doc/en/Mac_OS_X_installation.html and http://www.entropy.ch/software/macosx/mysql/ -Original Message- From: Ted Rogers [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 4:52 PM To: [EMAIL PROTECTED] Subject: upgrading to v 4.1 Is it recommended that I upgrade MySQL 3.23.53 to 4.1.x? Are then any special notes I might remember when doing so? (Mac OS X Server 10.2.6) Ted -- 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: upgrading to v 4.1
I don't know about Mac OS X, but I can tell you that with 4.1 on FreeBSD w/LinuxThreads, my application's query threads get hung up almost instantly, requiring a kill -9. When I back down to 4.0.13 (and 3.23), I don't see that particular problem anymore. So keep an eye out for how it behaves for you on Max OS X under load. --- Ted Rogers [EMAIL PROTECTED] wrote: Is it recommended that I upgrade MySQL 3.23.53 to 4.1.x? Are then any special notes I might remember when doing so? (Mac OS X Server 10.2.6) Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading to v 4.1
Hi, What do you call hung up ? All query appearing in opening table state when doing a show processlist ? If this is the case, I'm experiencing the same problem here with Linux. Regards, Jocelyn - Original Message - From: LS [EMAIL PROTECTED] To: Ted Rogers [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 29, 2003 12:42 AM Subject: Re: upgrading to v 4.1 I don't know about Mac OS X, but I can tell you that with 4.1 on FreeBSD w/LinuxThreads, my application's query threads get hung up almost instantly, requiring a kill -9. When I back down to 4.0.13 (and 3.23), I don't see that particular problem anymore. So keep an eye out for how it behaves for you on Max OS X under load. --- Ted Rogers [EMAIL PROTECTED] wrote: Is it recommended that I upgrade MySQL 3.23.53 to 4.1.x? Are then any special notes I might remember when doing so? (Mac OS X Server 10.2.6) Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.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]
timestamps and time zones
Bearing in mind the international nature of the internet, how do I make a timestamp local to the user rather than local to my webserver in Canada? If a user is adding a record to my database from England, I want the timestamp to be GMT rather than PST, and the same for my Australian clients, and so on. I will probably still use a master time stamp too which will be master server time much like ebay time. How do I go about this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query log/binlog inconsistency
Hi, I'm running into an issue on MySQL 4.0.12 (not tested on other releases) using an InnoDB table type, where an update query is getting written to the query log but never being propogated as far as the binlog. The query is also not updating the DB, though according to the connection layer (PEAR DB) it is affecting rows as one would expect. Running the query through a command line (logged in as the same user, from the same box, etc.) works as expected, writing to the query log, updating the DB, and then writing to the binlog. At present it seems the failure to write to the binlog is almost certainly because something is failing between the arrival of the query at the DB server (as signified by the entry in the query log) and the committing of the data (as would be signified by the data being appropriately modified and the binlog being written to). My question is essentially: what could fail between these steps that would: 1) not be reported back to the calling agent 2) not be logged to the db error log 3) not happen when running directly through the MySQL command-line client but happen when running through an (admittedly rather questionable) PHP library when the queries received by the DB are verifiably the same in every apparent aspect (through inspection of the query log). If anybody has an idea as to what may be happening, or better yet has seen this problem and maybe even has a solution, I'd be delighted to hear it. Further, if anyone can provide more details on what happens between the query log being written and the bin log being written (a rough process flow of what happens in the DB internals) that would be of great help (even if just to improve my knowledge of this stuff). Thanks in advance, Chris Tucker -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rebuild source RPM on RedHat 9.0
Hello, I just joined the list. I was trying to rebuild the MySQL 4.0.13 source RPM on my RedHat 9.0 machine. I get the following error messages at the end: make[2]: *** [isamchk] Error 1 make[2]: Leaving directory `/usr/src/redhat/BUILD/mysql-4.0.13/isam' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/redhat/BUILD/mysql-4.0.13' make: *** [all] Error 2 error: Bad exit status from /var/tmp/rpm-tmp.45120 (%build) RPM build errors: user mysqldev does not exist - using root user mysqldev does not exist - using root user mysqldev does not exist - using root Bad exit status from /var/tmp/rpm-tmp.45120 (%build) [EMAIL PROTECTED] src]# I don't know if that is the information anyone would need to help me. Does anyone have any suggestions on how I can deal with this problem? I have gcc-3.2.2-5. Thanks for any suggestions Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Test
test -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migrating from 3.23.51 - 4.0.12--question and clarification
Okay, don't cut my head off. MySQL is new for me, but I'm not new to databases. I have a MySQL DB for development running on WinNT (3.23.51-max-nt). I'm migrating the database for development to Mac OS X MySQL 4.0.12. I've read both the Upgrading From v3.23 to v4.0 (Section 2.5.2) and Upgrading to Another Architecture (Section 2.5.6) in the user manual. I have also run on the NT box the following commands: c:\ mkdir \dump c:\ mysqldump --opt --tab=\dump mydbname c:\ mydir \dump\mysql c:\ mysqldump --opt --tab=\dump\mysql mysql Okay .sql and .txt dumps in hand... Sec 2.5.2 talks about running 'mysql_fix_privilege_tables' I also see I have to update the scripts for some deprecated variables Do I run 'mysql_fix_privilege_tables' *before* or *after* I import the databases per section 2.5.6? I also understand from section 2.5.6 I don't have to worry about the ISAM files because this is a arch migration and the files are arch-dependent. Is this a correct understanding? Thanks in advance for the help! Tim -- /** * Timothy Stone . java fueled macintosh zealot * Harrisburg PA . javafueled at petmystone dot com * USA . www.petmystone.com * * This Satan's drink [coffee] is so delicious, * we shall cheat Satan and baptize it. * --Pope Clement VIII */ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld stops taking connections
1) Are your users connected temporarely (disconnecing after a certain time) or do you stay connected until the server crashes ;-) 2) Check your processlist and see if your max_user_connections or max_connections (depending on your client) is reached. Best regards Nils Valentin Tokyo/Japan 2003 5 29 04:[EMAIL PROTECTED] : I have a system that when I start mysqld, it works fine for a while (anywhere from 10 minutes to 2 hours) and then stops taking connections. I get the Can't connect to local MySQL server through socket ... however, the socket is still there. What's even weirder, is that any processes that are still connected, continue to work fine. The MySQL server installed is 3.23.56 binary for AIX 5.1 Standard downloaded off of a mysql.com mirror. Anyone have any ideas on how to solve this problem or why it might be happening? -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Connector/J 3.0.8 STABLE Has Been Released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 LS wrote: Where does Connector/J fit into the MySQL3.23.x versus MySQL4.x client libraries? Someone mentioned to me today that mod_php is still compiled with the old 3.23 client even though it may be talking to a 4.x server, so I'm wondering if Connector/J does anything different depending on whether it's talking to a 3.23 server or a 4.x server. Connector/J does things differently when talking to 3.23, 4.0 and 4.1, but it's all transparent as far as an end-user is concerned. There are no native libraries used in Connector/J ... It's 100% pure Java, and speaks the MySQL network protocol directly, so it doesn't have the issues with library incompatibility that native applications might have on some operating systems. -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+1WNftvXNTca6JD8RAmp7AJ0SykjObHYp5xsXjAZIsvaqx00EIQCghUTu xltrrCOZtKbvHbfKlh8pW4U= =op7p -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database permissions
On Wednesday, May 28, 2003, at 06:31 AM, Jon Haugsand wrote: * [EMAIL PROTECTED] What the permissions/ownership be on my database directory? I think is the cause of my problem of only being able to startup mysqld as 'root'. On my system the mysqld deamon runs as mysql and files are owned by mysql. -- Jon Haugsand, [EMAIL PROTECTED] http://www.norges-bank.no How can I check that my 'mysql daemon' is running as user 'mysql' and not 'root'? I have already done 'chown -R mysql /pathto/databases Thanks. Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limiting words...
Hi all, I have articles that I wish to display on a site. They are contained in long text fields in a MySQL table. I would like to display the first 25 words in the article, then have a continued link to the rest of the article on another page. I am using PHP on my pages currently. Anyone know how to accomplish this? Thanks for your help. -Steve Marquez Marquez Design www.marquez-design.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: local vs %
I might be wrong, but the first thing I always do immmediately after installation is delete any anonymous user. There is really no need for any extra account from security point of view. This also means that one entry f.e [EMAIL PROTECTED] would be enough as the % contains any host (including the localhost). Anybody, please correct me if I am talking rubish ;-) Best regards Nils Valentin Tokyo/Japan 2003 5 29 03:45D Crompton : Taken from manual: mysql GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; mysql GRANT ALL PRIVILEGES ON *.* TO monty@% - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; A full superuser who can connect to the server from anywhere, but who must use a password 'some_pass' to do so. Note that we must issue GRANT statements for both [EMAIL PROTECTED] and monty@%. If we don't add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence when we connect from the local host, because it has a more specific Host field value and thus comes earlier in the user table sort order. - The above implies you should have 2 permissions for every user, i.e. one for local and one for % Is this really necessary? We have root user and one other user called mark. A lookup therefore shows 4, 2 for each: [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] Should we really have 2 permissions for every user?. Doesn't the root just need access to localhost only?. -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database permissions
Thanks, I get: (B (B[localhost:~] ted% ps ax |grep mysql (B 180 ?? S 0:00.04 sh /usr/bin/safe_mysqld (B 197 ?? S 0:00.21 /usr/libexec/mysqld (B 2054 std R+ 0:00.00 grep mysql (B (Bdoes this look good? safe? (B (BThanks again, (BTed (B (B (B (BOn Wednesday, May 28, 2003, at 09:45 PM, Nils Valentin wrote: (B (B How about ? (B (B ps ax |grep mysql (B (B (B 818 ?S 0:00 /usr/sbin/mysqld-max --basedir=/ (B --datadir=/var/lib/mysql --user=mysql (B --pid-file=/var/lib/mysql/baby-bumble-bee.pid --skip-locking (B (B (B Best regards (B (B Nils Valentin (B (B 2003$BG/(B 5$B7n(B 29$BF|(B $BLZMKF|(B 10:40$B!"([EMAIL PROTECTED] $B$5$s$O=q$-$^$7$?(B: (B On Wednesday, May 28, 2003, at 06:31 AM, Jon Haugsand wrote: (B * [EMAIL PROTECTED] (B (B What the permissions/ownership be on my database directory? (B (B I think is the cause of my problem of only being able to startup (B mysqld as 'root'. (B (B On my system the mysqld deamon runs as mysql and files are owned by (B mysql. (B (B -- (B Jon Haugsand, [EMAIL PROTECTED] (B http://www.norges-bank.no (B (B How can I check that my 'mysql daemon' is running as user 'mysql' and (B not 'root'? (B I have already done 'chown -R mysql /pathto/databases (B (B Thanks. (B (B Ted (B (B -- (B (B Valentin Nils (B Internet Technology (B (B E-Mail: [EMAIL PROTECTED] (B URL: http://www.knowd.co.jp/staff/nils (B (B $BM-8B2q
Re: database permissions
On Wednesday, May 28, 2003, at 07:40 PM, [EMAIL PROTECTED] wrote: On Wednesday, May 28, 2003, at 06:31 AM, Jon Haugsand wrote: * [EMAIL PROTECTED] What the permissions/ownership be on my database directory? I think is the cause of my problem of only being able to startup mysqld as 'root'. On my system the mysqld deamon runs as mysql and files are owned by mysql. -- Jon Haugsand, [EMAIL PROTECTED] http://www.norges-bank.no How can I check that my 'mysql daemon' is running as user 'mysql' and not 'root'? I have already done 'chown -R mysql /pathto/databases It looks like you're using Apple Mail, so I'll assume you're on OS X. Open '/Applications/Utilities/Process Viewer' and type mysql (or some combination) into the find box. It'll give you the user (mysqld is the one you're looking at). There are other ways, but that's the easiest way if you're not interested in reading 'man ps'. If you are interested in sticking with the command line, here are a couple of ways you could check: ps -au ps -U httpd and of course: ps -au|grep mysqld (terminal screen will need to be wide for this, most likely) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database permissions
On Wednesday, May 28, 2003, at 08:16 PM, otherguy wrote: ps -U httpd I screwed this up. It's been a long day. Of course it should be: ps -U mysqld sorry about that -Cameron Wilhelm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlhotcopy usage
Hello mysql fans ;-), Does anybody know detailed how mysqlhotcopy works ? Does it put a lock on the table or only on a row in case of transaction safe tables ? Does it work with any table type (Innodb,BDB, Merge, MyISAM) ? Any comments much appreciated. --- To general list Moderator: Please relist in case I put into the wrong list. Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
intersting bug with full text match against
i am getting people to try and break a search engine i built , when we put in the word DB into the search query i get no results even if its there in the record , why is this ? WHERE MATCH (sl.shotlist , sl.slug , sl.summary) AGAINST ('DB' IN BOOLEAN MODE) LIMIT 0 , 10 ; also how come this doesnt work for the above syntax ? word1 word2 word3 word4 , i cant seem to get exact matches more than once :| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem compiling MyODBC 3.51.06
Hi, I try to compile MyODBC-3.51.06 on my GNU/Linux system with gcc-3.2 there was no problem with the configure, but for the make I got this : # gmake Making all in driver gmake[1]: Entering directory `/tmp/MyODBC-3.51.06/driver' gmake all-am gmake[2]: Entering directory `/tmp/MyODBC-3.51.06/driver' source='catalog.c' object='catalog.lo' libtool=yes \ depfile='.deps/catalog.Plo' tmpdepfile='.deps/catalog.TPlo' \ depmode=gcc3 /bin/sh ../depcomp \ /bin/sh ../libtool --mode=compile gcc -s -DHAVE_CONFIG_H -I. -I. -I. -I/usr/local/mysql/include/mysql -I/usr/local/BerkeleyDB.4.1/include -I/usr/local/include -O3 -DDBUG_OFF -O3 -march=i686 -c -o catalog.lo `test -f catalog.c || echo './'`catalog.c gcc -s -DHAVE_CONFIG_H -I. -I. -I. -I/usr/local/mysql/include/mysql -I/usr/local/BerkeleyDB.4.1/include -I/usr/local/include -O3 -DDBUG_OFF -O3 -march=i686 -c catalog.c -MT catalog.lo -MD -MP -MF .deps/catalog.TPlo -fPIC -DPIC -o catalog.lo cc1: warning: changing search order for system directory /usr/local/include cc1: warning: as it has already been specified as a non-system directory In file included from myodbc3.h:106, from catalog.c:46: /usr/local/include/iodbcinst.h:77:20: config.h: No such file or directory gmake[2]: *** [catalog.lo] Error 1 gmake[2]: Leaving directory `/tmp/MyODBC-3.51.06/driver' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/tmp/MyODBC-3.51.06/driver' gmake: *** [all-recursive] Error 1 It would be great if you could help me with this ! Best regards, Pierre. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem compiling mysql-connector-java 3.1.0-alpha
Hi, I try to compile mysql-connector-java-3.1.0-alpha on my GNU/Linux system with gcc-3.2, but for the make I got this : # ant Buildfile: build.xml Overriding previous definition of reference to non.test.sources clean: [delete] Deleting directory /tmp/build-mysql-jdbc init: [copy] Copying 78 files to /tmp/build-mysql-jdbc/mysql-connector-java-3.1.0-alpha [copy] Copied 2 empty directories to /tmp/build-mysql-jdbc/mysql-connector-java-3.1.0-alpha [copy] Copying 3 files to /tmp/build-mysql-jdbc/mysql-connector-java-3.1.0-alpha [mkdir] Created dir: /tmp/build-mysql-jdbc/clover [mkdir] Created dir: /tmp/build-mysql-jdbc/clover/db [mkdir] Created dir: /tmp/build-mysql-jdbc/clover/report compile-core: [javac] Compiling 72 source files to /tmp/build-mysql-jdbc/mysql-connector-java-3.1.0-alpha [javac] /tmp/build-mysql-jdbc/mysql-connector-java-3.1.0-alpha/testsuite/BaseTestCase.java:21: package junit.framework does not exist [javac] import junit.framework.TestCase; [javac]^ [javac] /tmp/build-mysql-jdbc/mysql-connector-java-3.1.0-alpha/testsuite/BaseTestCase.java:38: cannot resolve symbol [javac] symbol : class TestCase [javac] location: class testsuite.BaseTestCase [javac] public abstract class BaseTestCase extends TestCase { [javac]^ [javac] /tmp/build-mysql-jdbc/mysql-connector-java-3.1.0-alpha/testsuite/BaseTestCase.java:125: cannot resolve symbol [javac] symbol : method fail () [javac] location: class testsuite.BaseTestCase [javac] fail(); [javac] ^ [...] It would be great if you could help me with this ! Best regards, Pierre. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Errors configuring mysql 4.1.0
Hi, I tried to configure mysql-4.1.0 and I got these errors : [...] checking for OpenSSL... yes ./configure: test: =: unary operator expected no ./configure: no: command not found ./configure: no: command not found mkdir: cannot create directory `include/readline': File exists checking character sets... default: latin1; compiled in: latin1 [...] I don't know if it's important but I wanted to report it. Regards, Pierre. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: intersting bug with full text match against
i have also noticed that the boolean default is OR how can i explicitly set it to AND like i'd like both exact matches in the search currently its exact match 1 or exact match 2 :| Daniel Rossi [EMAIL PROTECTED] 05/29/03 01:23pm i am getting people to try and break a search engine i built , when we put in the word DB into the search query i get no results even if its there in the record , why is this ? WHERE MATCH (sl.shotlist , sl.slug , sl.summary) AGAINST ('DB' IN BOOLEAN MODE) LIMIT 0 , 10 ; also how come this doesnt work for the above syntax ? word1 word2 word3 word4 , i cant seem to get exact matches more than once :| -- 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]
Multiple lookups for one item in one description table
Hello, Our company is currently redesigning its MySQL database to be more efficient. Right now, our major concern is how to effectively do multiple value lookups in one value table without sacrificing our searching capability. Basically, we are trying to link an Item from one table to multiple Options in another table without replicating too much data. Item 1 (lets say car1) has options 1, 2, 3 4 (doors, wheels, engine, sunroof) for example. Our current system (see below for an example) has a table in the middle that acts a reference table of sorts, which works alright, but we are currently doing two or more queries at run time; One to select and compile the multiple options into a single text string, and one to select the rest of the items info. Then we combine both queries and fire them out to a web page. What we would like to see is a single MySQL select query that joins the data for us into another string using the 'AS' name construct (not necessarily with the current database structure, we are open to all options at this point). If that is not possible (or recommended) any suggestions on how to improve re-organize for efficiency is greatly appreciated. TIA, James. Example of existing system: Table item item_id - 1 item_desc - car1 Table item_TO_options - item_id option_id Data for Table item_TO_options item_id | option_id 11 1... 2 1... 3 14 Table options - option_id option_desc
Re: upgrading to v 4.1
No, in my case, both queries report being in Connect state and just stay there. Something like this: | 14376 | unauthenticated user | 10.0.48.147 | | Connect | | login | | | 14416 | unauthenticated user | 10.0.48.147 | | Connect | | login Googling around on this I see various people have run into something similar from time to time, so perhaps it's an old bug that has been reintroduced in the 4.1alpha code --- Jocelyn Fournier [EMAIL PROTECTED] wrote: Hi, What do you call hung up ? All query appearing in opening table state when doing a show processlist ? If this is the case, I'm experiencing the same problem here with Linux. Regards, Jocelyn - Original Message - From: LS [EMAIL PROTECTED] To: Ted Rogers [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 29, 2003 12:42 AM Subject: Re: upgrading to v 4.1 I don't know about Mac OS X, but I can tell you that with 4.1 on FreeBSD w/LinuxThreads, my application's query threads get hung up almost instantly, requiring a kill -9. When I back down to 4.0.13 (and 3.23), I don't see that particular problem anymore. So keep an eye out for how it behaves for you on Max OS X under load. --- Ted Rogers [EMAIL PROTECTED] wrote: Is it recommended that I upgrade MySQL 3.23.53 to 4.1.x? Are then any special notes I might remember when doing so? (Mac OS X Server 10.2.6) Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my backup script
I've just spent the past few days writing this backup script... http://resume.daevid.com follow the link for it. It will do weekly full backups on Sunday and daily diff backups. It also does daily dumps of all databases in the mysql db directory then combines them into a single .tgz file for storage (I find this more sexy than the mysqldump -A option which combines them all into a huge dump file! Yipes). It is best if you have /backups as a separate device because I mount it rw and then ro when finished storing the backups. Simply list what you want at the top and use the exclude file to omit what you don't. Ideally I'd like to make it so it only backs up the mysql databases that have changed (diff), but I'm not sure how to determine that, as the directory doesn't seem to change it's date as I would expect, if the contents within are updated. Hmmm. Ideas? Also, I use ctime in my find for the differentials, is mtime a better choice or does it really matter? Will ctime find all the mtime files too? Daevid Vincent http://daevid.com Ps. And yes, I sent that as a link in my resume because I'm unemployed and would love any offers for coding work with PHP, Linux, SQL, etc... ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upgrading to v 4.1
On Wed, May 28, 2003 at 03:42:24PM -0700, LS wrote: I don't know about Mac OS X, but I can tell you that with 4.1 on FreeBSD w/LinuxThreads, my application's query threads get hung up almost instantly, requiring a kill -9. When I back down to 4.0.13 (and 3.23), I don't see that particular problem anymore. So keep an eye out for how it behaves for you on Max OS X under load. Which version of FreeBSD and LinuxThreads are you using? I've never experienced that bug before. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,574,536,636 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query log/binlog inconsistency
On Wed, May 28, 2003 at 05:05:38PM -0700, Chris Tucker wrote: Hi, I'm running into an issue on MySQL 4.0.12 (not tested on other releases) using an InnoDB table type, where an update query is getting written to the query log but never being propogated as far as the binlog. The query is also not updating the DB, though according to the connection layer (PEAR DB) it is affecting rows as one would expect. Running the query through a command line (logged in as the same user, from the same box, etc.) works as expected, writing to the query log, updating the DB, and then writing to the binlog. Hmm. The fact that the it doesn't show up in the binlog *and* it never affects you data is good. That means the binlog is working properly. :-) At present it seems the failure to write to the binlog is almost certainly because something is failing between the arrival of the query at the DB server (as signified by the entry in the query log) and the committing of the data (as would be signified by the data being appropriately modified and the binlog being written to). Agreed. My question is essentially: what could fail between these steps that would: 1) not be reported back to the calling agent 2) not be logged to the db error log 3) not happen when running directly through the MySQL command-line client but happen when running through an (admittedly rather questionable) PHP library when the queries received by the DB are verifiably the same in every apparent aspect (through inspection of the query log). The first thing that comes to mind is that the abstraction layer you're using forgets to COMMIT the data, so InnoDB rolls it back and never write the query to the binlog. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,574,615,610 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB backup - the best way.
On Wed, May 28, 2003 at 09:53:02AM -0700, Chris Tucker wrote: Alternatively, use InnoDB Hot Backup: http://www.innodb.com/hotbackup.html This will let you take a real-time backup of your InnoDB tables without taking the server down. Agreed. Aside from shutting down MySQL and backing it up with your favorite backup too, the hot backup tool is the best option. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,574,938,408 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication with non permanent connection
On Wed, May 28, 2003 at 11:45:44AM +0200, Kristian Halm wrote: Hi all I'm going to setup a mySQL database that has to be accessed from Asia and Europe. Due to the huge distance I plan to duplicate the database and store one copy both in Europe and Asia. To achive data consistency both databases has to synchronize each other. Because of the long distance a disconnect for several hours is possible (worst case). Is database replication an adequate solution for synchronizing the databases or is there maybe a better way? How does mySQL replication handle disconnects in respect to the synchronization? If you're either (1) not using auto-increment columns, or (2) you've partitioned the data so that Asia only updates Asia's data, you should be fine with using a dual-master setup. MySQL doesn't care if the connection dies. It'll happily reconnect and continue replicating. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,575,036,244 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
On Tue, May 27, 2003 at 07:50:21AM -0400, Becoming Digital wrote: MyISAM is the default MySQL table type. This is the table type of choice for tables whose primary activity comes from SELECT statements. There is no need for transaction-safe tables unless INSERT, UPDATE, and/or DELETE actions will be performed frequently. Remember that with transaction-safe tables comes an increase in the amount of system resources needed to use those table types. BDB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini types. Gemini? I haven't heard that name for a while. I thought it was dead. Is NuSphere still selling their Gemini-enhanced MySQL? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,575,209,651 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: intersting bug with full text match against
On Thu, May 29, 2003 at 01:23:08PM +1000, Daniel Rossi wrote: i am getting people to try and break a search engine i built , when we put in the word DB into the search query i get no results even if its there in the record , why is this ? WHERE MATCH (sl.shotlist , sl.slug , sl.summary) AGAINST ('DB' IN BOOLEAN MODE) LIMIT 0 , 10 ; Because you haven't told MySQL to index 2 character words. http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,575,496,646 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Practical samples for table types
Hi Jeremy, You are right it's not sold anymore, but that doesnt mean that it wasnt good, right ;-). Apart from the fact that Gemini might be outdated or not, I am really more interested in the technical features etc. Anyway thanks for the reply. Best regards Nils Valentin 2003 5 29 16:27Jeremy Zawodny : On Tue, May 27, 2003 at 07:50:21AM -0400, Becoming Digital wrote: MyISAM is the default MySQL table type. This is the table type of choice for tables whose primary activity comes from SELECT statements. There is no need for transaction-safe tables unless INSERT, UPDATE, and/or DELETE actions will be performed frequently. Remember that with transaction-safe tables comes an increase in the amount of system resources needed to use those table types. BDB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini types. Gemini? I haven't heard that name for a while. I thought it was dead. Is NuSphere still selling their Gemini-enhanced MySQL? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,575,209,651 queries (360/sec. avg) -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stunnel
Has anyone ever been able to communicate between a client app and a mysql server over stunnel ? (stunnel listening on client; connecting to stunnel listening on mysql server; which is redirecting traffic to the mysql port on localhost on the server client software connecting to localhost on client; stunnel picks up the connection and sends it to the other side. Will this work ? How will this affect the user security ? will all connections come from the IP address of the server, going to localhost ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my backup script
On Thu, May 29, 2003 at 12:10:09AM -0700, Daevid Vincent wrote: Ps. And yes, I sent that as a link in my resume because I'm unemployed and would love any offers for coding work with PHP, Linux, SQL, etc... ;-) Well, if you're gonna pimp your resume here, I'll respond similarly. :-) We use lots of PHP and MySQL at Yahoo! Visit join.yahoo.com to search for current engineering openings. If you apply, make sure to note that I referred you. Contact me for more details. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,575,609,920 queries (360/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query log/binlog inconsistency
Chris, - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, May 29, 2003 10:17 AM Subject: Re: Query log/binlog inconsistency On Wed, May 28, 2003 at 05:05:38PM -0700, Chris Tucker wrote: Hi, I'm running into an issue on MySQL 4.0.12 (not tested on other releases) using an InnoDB table type, where an update query is getting written to the query log but never being propogated as far as the binlog. The query is also not updating the DB, though according to the connection layer (PEAR DB) it is affecting rows as one would expect. Running the query through a command line (logged in as the same user, from the same box, etc.) works as expected, writing to the query log, updating the DB, and then writing to the binlog. Hmm. The fact that the it doesn't show up in the binlog *and* it never affects you data is good. That means the binlog is working properly. :-) At present it seems the failure to write to the binlog is almost certainly because something is failing between the arrival of the query at the DB server (as signified by the entry in the query log) and the committing of the data (as would be signified by the data being appropriately modified and the binlog being written to). Agreed. My question is essentially: what could fail between these steps that would: 1) not be reported back to the calling agent 2) not be logged to the db error log 3) not happen when running directly through the MySQL command-line client but happen when running through an (admittedly rather questionable) PHP library when the queries received by the DB are verifiably the same in every apparent aspect (through inspection of the query log). The first thing that comes to mind is that the abstraction layer you're using forgets to COMMIT the data, so InnoDB rolls it back and never write the query to the binlog. Jeremy's explanation is plausible. If the PHP library runs in the AUTOCOMMIT=0 mode, then the query is executed and reports modified rows, but when the connection ends mysqld rolls back the transaction because it was not explicitly committed. Also note that a deadlock or a lock wait timeout error rolls back the WHOLE current transaction. But I assume you did not get any of these errors or other errors? It would help if you could post the relevant query log excerpt. Jeremy Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 114 days, processed 3,574,615,610 queries (360/sec. avg) -- 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 4.1
Hello, can anyone tell me estimated production release for mysql 4.1? We are now testing it in development enviroment, and it looks like we will be utilizing its variable charsets per table, column etc. :). -- Martin Hudec -- :@: [EMAIL PROTECTED] :w: http://www.corwin.sk :m: +421.907.303.393 In google non est, ergo non est. - unknown IRC operator -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
importing data
Hello List, i know little bit about mysql. i use mysql to use the webportal system of phpnuke. can anyone help me to import data in mysql. for exams i have a file nuke.sql which contains like tables design and there records. how to call this file when i am having sql prompt. i use redhat linux for mysql. any help appreciated. Naren. Missed your favourite TV serial last night? Try the new, Yahoo! TV. visit http://in.tv.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RDBMS listserv?
I'm trying to study some books, like Database Design for Mere Mortals. I'm finding that I could really use a listserv to get some occasional questions out. Anyone know of any? (Preferably a listserv, but forums will do as well.) Thanks, Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: local vs %
- Original Message - From: Nils Valentin [EMAIL PROTECTED] To: D Crompton [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 29, 2003 2:54 AM Subject: Re: local vs % I might be wrong, but the first thing I always do immmediately after installation is delete any anonymous user. There is really no need for any extra account from security point of view. This also means that one entry f.e [EMAIL PROTECTED] would be enough as the % contains any host (including the localhost). Anybody, please correct me if I am talking rubish ;-) Best regards -- This is what i orignally thought but he manual implies differently: If we don't add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence Therefore you have to also add a localhost ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
importing data to tables
Hello List, i am new to mysql. i use php-nuke web portal. i have a database.sql file which contains my database. it contains tables there records in .sql file which can be opened with notepad. if i do a fresh installation of mysql how do i recover my database using my backup file. i mean how to import database or tables. any help appreciated. Thanks,Naren. Catch all the cricket action. Download Yahoo! Score tracker
New to mailing lists
Hi Newto the lists and just starting to learn about SQL. Looking forward to learning more here. Cheers Petra Cowled Zawadi Rhodesian Ridgebacks http://www.geocities.com/alekzandr1 ICQ: 20953335 To see whats happening with Rhodesian Ridgebacks in Australia go to: http://www.australianrrs.com
Problem connecting with sockets to MySQL
Hi all! I have asked this before, but no replys. I'll rewrite the question because I don't know if I got understood. ;-) Anyway, I have not found a MySQL list best suited for this question, so here I go... ¿Anyone knows why the server (mysqld) behaves differently to clients connecting from the same remote machine? (Server 3.23.41 on a RedHat 7.2. The client is Windows XP Professional.) In other words, say we are connecting from Host B to Server A. Well, I will list the possible responses I'm getting (remember, always speaking of low level socket connections). - telnet servera 3306-- Response OK. (First handshake packet received OK). - MySQLFront -- Response OK. (MySQLFront connects OK). - mysql -u user -h servera -- Response OK. (MySQL client connects OK). - nc servera 3306-- Response OK. (First handshake packet received OK). OK, at this point, you do think all is ok, don't you? not quite... - Connecting via a simple TCP socket coded in Borland C++ Builder 5.0: The first packet response is: 1130: Host 'hostb' isn't allowed to connect to this mysql server. I am not even allowed to reply with the packet that identifies my to the server. Pardon? What?? I'm not ALLOWED to connect to the server? As you can see, this is nonsense because I CAN connect to the server as seen above. I have not come to a conclusion on this topic, so all you can help is greatly appreciated. I go so far as to capture the packets with a packet sniffer (ethereal), and I got crazy: Everything seems fine, all connections generate the same packets, in the same order... But when connecting from my program, I get that 1130 error. Cheers, Jose Miguel. PS: Yes, I have done a mysqladmin flush-hosts everytime. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem connecting with sockets to MySQL
Hi Jose, I believe you miss an important point. Error 1130 means that the host (ip address) is not allowed to connect, it doesnt mean that its not physically possible. You have to check the privileges you gave the user or host that doesnt work. login to mysql from a working account. do this: USE mysql SHOW GRANTS FOR xxxuserxxx; loook exactly what it says. If you are used to mysqlcc than connect from there look at the data in all tables of the mysql database. They contain the privilege of each user or host. there should be 5 tables f.e. mysql.user mysql.host mysql.columns_priv mysql.tables_priv mysql.db You will see that the rights for the host will not be set correctly, most likely in the mysql.host or mysql.user table. Take a GOOD read in the manual, have a rest and then troubleshoot it. Good Luck !! Best regards Nils Valentin Tokyo/Japan 2003 5 29 18:47Jose Miguel Prez : Hi all! I have asked this before, but no replys. I'll rewrite the question because I don't know if I got understood. ;-) Anyway, I have not found a MySQL list best suited for this question, so here I go... Anyone knows why the server (mysqld) behaves differently to clients connecting from the same remote machine? (Server 3.23.41 on a RedHat 7.2. The client is Windows XP Professional.) In other words, say we are connecting from Host B to Server A. Well, I will list the possible responses I'm getting (remember, always speaking of low level socket connections). - telnet servera 3306-- Response OK. (First handshake packet received OK). - MySQLFront -- Response OK. (MySQLFront connects OK). - mysql -u user -h servera -- Response OK. (MySQL client connects OK). - nc servera 3306-- Response OK. (First handshake packet received OK). OK, at this point, you do think all is ok, don't you? not quite... - Connecting via a simple TCP socket coded in Borland C++ Builder 5.0: The first packet response is: 1130: Host 'hostb' isn't allowed to connect to this mysql server. I am not even allowed to reply with the packet that identifies my to the server. Pardon? What?? I'm not ALLOWED to connect to the server? As you can see, this is nonsense because I CAN connect to the server as seen above. I have not come to a conclusion on this topic, so all you can help is greatly appreciated. I go so far as to capture the packets with a packet sniffer (ethereal), and I got crazy: Everything seems fine, all connections generate the same packets, in the same order... But when connecting from my program, I get that 1130 error. Cheers, Jose Miguel. PS: Yes, I have done a mysqladmin flush-hosts everytime. -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp/staff/nils 182-0024 4-6-1 7F Phone: 0424-40-7912 Fax: 0424-40-7913 URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem connecting with sockets to MySQL
Hi Nils! First of all, thanks for your reply. I believe you miss an important point. Error 1130 means that the host (ip address) is not allowed to connect, it doesnt mean that its not physically possible. You have to check the privileges you gave the user or host that doesnt work. Yes, the privileges is all set. Read my post again and you can see I'm explaining that connecting with the mysql client all goes OK and trying to connect with a socket I receive the error. It all goes like this: A) Connecting with mysql -h myhost -u user. Using the command line client all goes ok, I can connect. This is indication for all the privileges are correctly set. B) Connecting with a socket in my C++ program I receive the 1130 error. (This obviously using the SAME machine, not speaking the same IP). What troubles me is what the heck does the libmysql.dll to connect correctly that I can't replicate using a socket. I suppose (and only suppose) I can migrate the server from 3.23.41 to 4.0.13 and all this goes away. If that is the case, I imagine I have catched a bug to the 3.23.41 version. However, I have read all the changelogs up to 4.0 and it doesn't say anything the developers have changed something to the low level connection layer or something. Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: RE: About LOAD DATA LOCAL INFILE - Still not working
Vahri? MUHTARYAN [EMAIL PROTECTED] wrote: I commented out because they are not working when I use set-variable = local-infile=1 in Server and Client .. Mysql is not starting ?f I use local-infile=1 -- Mysql is starting but still same problem ... I used --local-infile=1 at command line but still same problem -- This version is not ... It worked perfect for me. Do you use command-line client program mysql or any other? -- 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: importing data
Rajendra Kumar [EMAIL PROTECTED] wrote: i know little bit about mysql. i use mysql to use the webportal system of phpnuke. can anyone help me to import data in mysql. for exams i have a file nuke.sql which contains like tables design and there records. how to call this file when i am having sql prompt. i use redhat linux for mysql. Execute from the shell: mysql -uuser_name -p database_name nuke.sql and enter the user password. -- 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]
Re: Possible issue with mysqlgui
Gordon [EMAIL PROTECTED] wrote: My environment is as follows Dell Inspiron notebook with 512mb ram and 30gb disk (900mhz) Windows 2000 sp3 MySQL 3.23.52 MySQLGUI 1.7.5-2 I created a database and connected to it ok. Tried to create a table and nothing happens. Tried using F9 and nothing happens. Program does not seem to want to create tables. I am new to MySQL so it is probably me, but I tried everything I could think to do. Thank you in advance for any light you can shed on this issue. F9 for creating table doesn't work in MySQLGUI and it will not work. You can use another client like MySQL Control Center. -- 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]