RE: strange OS X (Panther) MySql 4.0.16 behaviour!!! HELP!!!
Jason, The possiblity exists that the issue is Apache. I only use 1.3 so I cannot give specific feedback. Is the PHP.ini file the same in production and as in development? Regards, Adam -Original Message- From: g4 [mailto:[EMAIL PROTECTED] Sent: Thursday, November 06, 2003 11:20 AM To: [EMAIL PROTECTED] Subject: strange OS X (Panther) MySql 4.0.16 behaviour!!! HELP!!! Hi list, I'm still trying to ascertain if this is in fact an MySql problem, here goes. I'm developing locally on My G4 box running Apache 2 + MySql 4.0.16, I have a live site on a FreeBSD box running Apache 1.3 + MySql 3.x. The site is an e-commerce site (PHP 4.3). When I try and access a page that paginates because of the number of items has exceeded 6 items I get in Safari: Could not open the page http://192.168.0.11/Englishhall/shopping/index.php? p=1%7C5%7C10%7C14%7C19id=27 because Safari could not load any data for this location. and in Mozilla, well just nothing! When I try an access a page with no pagination it works. So for example http://192.168.0.11/Englishhall/shopping/index.php? p=1%7C5%7C10%7C14%7C19id=30 will work. My local Database (on my G4 box) has been created from dumps from the live site. At first I thought this was a problem with My PHP, so I downloaded the unchanged PHP source from the live site, so essentially an exact copy of a working site. and it still produces the same results. So it must be a Database issue right? I have upgraded to Panther recently and this caused some of the tables to lose their permissions, which I've fixed. I reinstalled the DB and the issue still remains. Is there a limiter of some sort I have to increase? Anyway any help much appreciated. TIA. Jason Lane Developer Root10 developments -- 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: select query syntax help [ANSWER]
This is a common question. The syntax looks like this: SELECT a.* FROM tbl_a AS a LEFT JOIN tbl_b AS b ON a.id = b.id WHERE b.id.id IS NULL; The idea is you're retrieving a recordset of the two tables where the rows are joined on the id. For tbl_b, the id field has no value (its null) so you can identify those rows by asking for nulls in the `tbl_b` `id` column. In your case, I would try: SELECT tbl.* FROM ResourceTable AS tbl LEFT JOIN ResourceLinkTable AS lnk ON tbl.ResourceID= lnk.ResourceID WHERE lnk.ResourceID.id IS NULL ORDER BY ResourceName ASC; Regards, Adam -Original Message- From: Dan Lamb [mailto:[EMAIL PROTECTED] Sent: Thursday, October 30, 2003 9:39 AM To: [EMAIL PROTECTED] Subject: select query syntax help Hello All, I have two table the look like this (greatly simplified): ResourceTable - int ResourceID var ResourceName ResourceLinkTable - int ResourceLinkID int ResourceID var Text I need to find all rows in ResourceTable for which there is NO entry in ResourceLinkTable. I know I could do this with sub-selects like this: Select * from ResourceTable where ResourceID not in (select distinct ResourceID from ResourceLinkTable) How can I do this in MySQL 4.0 without using sub-selects? Thanks, Dan Lamb -- 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: Insert ... Select question
Nikos, Yes. Use the following syntax: INSERT INTO db_name.tbl_name (col, col, col) SELECT col, col, col FROM db_name.tbl_name; Regards, Adam -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 9:58 AM To: [EMAIL PROTECTED] Subject: Insert ... Select question Hello list I want to insert ... select data from table1 of db1 to table2 of db3. Is that possible? Thank in advance -- 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: Online Tutorials for beginners
New to SQL or new just to MySQL? -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 10:54 AM To: [EMAIL PROTECTED] Subject: Online Tutorials for beginners Hi, Does anyone know of any good mysql tutorials online that would suit someone who has a computer science degree but knows nothing about MySQL. Pointers will be most welcome. Cheers, Andrew Sql, query -- 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: OS X - can't set root password
Robert, If I follow you're email correctly, it looks like you attempting to assign a password to the root user twice. The first statement you provide: bin/mysqladmin -u root password ** Sets the password to whatever value you've set in-place of **. Then in the statement below you attempt to set the password again. /usr/local/mysql/bin/mysqladmin -u root -h `hostname` password ** Take a good look at that second line. You're assigning a new password to the root account, but when you attempt to reset the password for root as root you don't use root's new (as set in line 1) password. Make sense? Normally, once you've set the root password you don't need to set it again. If you garble the password and cannot access the root account restart MySQL and bypass the GRANT tables (see below for direction). If you need more help, let me know. I'm running 4.0.15 on a 12 PB with OS X 10.2.8 (get that system patch brother!). # See what MySQL processes are running. ps -aux | grep -i mysql # Enable the sudo construct (you will need to enter your OS X password) sudo -v # Start MySQL and without using the grant tables. sudo mysqld_safe --user=mysql --skip-grant-tables # Assign a new root password (ONLY do this ONCE) mysqladmin -uroot password 'password' # Access MySQL as root. You will be prompted to enter root's password. mysql -uroot -p Regards, Adam -Original Message- From: Robert Lund [mailto:[EMAIL PROTECTED] Sent: Saturday, October 18, 2003 8:32 PM To: [EMAIL PROTECTED] Subject: OS X - can't set root password I just installed 4.0.15 on an iBook running Mac OS X 10.2.6. I started the daemon using bin/mysqld_safe, and then tried to initialize the root password as instructed. The first, bin/mysqladmin -u root password ** ran apparently successfully. When I tried to execute the second: /usr/local/mysql/bin/mysqladmin -u root -h `hostname` password ** the response was: /usr/local/mysql/bin/mysqladmin: connect to server at 'lazarus.local.' failed error: 'Host '192.168.1.100' is not allowed to connect to this MySQL server' That is the IP address assigned my Mac by my DSL router; and its name is 'lazarus'. Where can I find out the source of this permissions gap and how the channel might be opened up? 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: OS X - can't set root password
Robert, I also think the read me is a little vague. I see what they're getting at but I don't understand why it was phrased that way. When you think about it, the error message makes sense. When you do any command through mysqladmin your authenticating to MySQL then executing the command. You were attempting to connect to MySQL without a password when you had just set one. MySQL responded by telling you your login attempt was unsuccessful. After all, you can set your root user's password as many times as you like. Its been a while but I believe MySQL identifies the full hostname, lazarus.local. versus the short name lazarus (see command line example below). So yes, I wouldn't be surprised to see a host name like that returned. [machine] prompt% hostname lazarus.local. [machine] prompt% hostname -s lazarus If you have additional questions, let me know. I don't use MySQL too much these days, but I'll be glad to tell you what I know. Regards, Adam -Original Message- From: Robert Lund [mailto:[EMAIL PROTECTED] Sent: Monday, October 20, 2003 12:00 PM To: [EMAIL PROTECTED] Subject: Re: OS X - can't set root password Fortuno, Adam wrote: If I follow you're email correctly, it looks like you attempting to assign a password to the root user twice. The first statement you provide: bin/mysqladmin -u root password ** Sets the password to whatever value you've set in-place of **. Then in the statement below you attempt to set the password again. /usr/local/mysql/bin/mysqladmin -u root -h `hostname` password ** Take a good look at that second line. You're assigning a new password to the root account, but when you attempt to reset the password for root as root you don't use root's new (as set in line 1) password. Make sense? What you say makes sense - although I would expect the mysql response to be something indicating that I'm trying to set the root password twice, instead of telling me: error: 'Host '192.168.1.100' is not allowed to connect to this MySQL server' I only entered those two instructions because in the README supplied with the Installation PKG, the following was included: If you installed MySQL for the first time, *please remember to set a password for the MySQL `root' user!* This is done with the following two commands: /usr/local/mysql/bin/mysqladmin -u root password password /usr/local/mysql/bin/mysqladmin -u root -h `hostname` password password Am I missing something here? Do they actually mean either of the following two commands? Robert PS: Does 'lazarus.local.' look like a normal response from 'hostname' or should it be just the plain name 'lazarus'? -- 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: Anyone using MySQL 4.x on Apple's G5?
If memory serves, adjustments to the OS need to be made to take advantage of the 64-bit memory addressing. I know some changes have been made to OS X to run on a 64-bit chip, but I'm not certain they've made enough modifications to take advantage of the memory addressing. I'd be interested to see how this works on Panther. -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] To: Gabriel Ricard I wrote a small C program to test malloc() and see just how much I could allocate, and I was able to get up to 3.5GB before being cut off by the OS, which leads me to believe that I should be able to use that much RAM for MySQL. Yes. I wonder why you got cut off at 3.5GB. I'd have expected OS X on 64bit hardware not to have the weird limitations that, say, FreeBSD or Linux with kernel reserved memory. Something is wonky here... Thanks for the info. I'd love to hear if you're successful getting MySQL to use more than 2GB. I'm gonna hunt around a bit more to see what others may know. Thanks, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Anyone using MySQL 4.x on Apple's G5?
Wow, new dual processor G5... behaviordrool/behavior How are the bulk operations performing? -Original Message- From: Gabriel Ricard [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 11:05 AM To: [EMAIL PROTECTED] Subject: Re: Anyone using MySQL 4.x on Apple's G5? I am currently testing this on a new dual 2GHz G5 with 4GB of RAM. Seems pretty nice so far. I've loaded up a copy of our production database (4GB of data for real estate web sites) and moderate property search queries of ours run much faster than on our dual G4 MDD PowerMac. I guess we're really not taking advantage of the 64 bit memory space as we've only got 4GB of RAM in it currently, heh. I've been running the sql-bench/run-all-tests benchmark and trying to figure out why insert_key is taking 2440 wall clock seconds. Everything else is damn zippy. Anything specific you'd like to see numbers for? This box isn't going to be in production use for a few weeks anyways. - Gabriel On Wednesday, October 15, 2003, at 02:13 AM, Jeremy Zawodny wrote: If so, how is it working out? Are you taking advantage of the 64bit memory space? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 31 days, processed 1,151,820,403 queries (426/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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB Design
Give this more thought. I think you have more options that the two you proposed. With really large tables, you can collect data in them for a fixed time period (monthly) then run a batch that removes the data for the time period after moving it to an archive table. Try making a staging table that records the data daily and is flushed nightly (or monthly) into a main repository. Your architecture will be successful as long as you understand what you're going to do with the data - reports, exports, maintenance (update/delete), etc. Regards, Adam -Original Message- From: Mahesh Tailor [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 5:05 PM To: [EMAIL PROTECTED] Subject: DB Design New to the list . . . Running MySQL Server 3.23.58-1.72 on RedHat Enterprise AS. System has four 3GHz processors and 6GB RAM. I need some advise on what would be best way to approach this problem. This system is using snmpcollect to collect network statistics from about 1500 devices. The collections are configured to get data every 5-30 minutes depending on the collection type. Given this I am collecting approximately 170K records per hour. I have to keep this collected data for at least 365 days. This works out to approximately 1.50B records/year. After setting up the database, each record is 42 bytes [which would yield, if my math is correct, a database of approximately 62GB]. So my question is: is it better to create one database one table or one database many tables? If I use the many tables option, I will have about 1500 tables. Or, it is better to create 1500 databases with one table each. BTW, I tried the 1-DB-1-table approach the the server came to a crawl. Thanks for any opinions. Mahesh -- 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: Setting root password on OS X
Shane, Maybe I can lend a hand. When you first install MySQL, there is (basically) only one user root. The root user exists without a password, which is why pretty much everyone recommends that you assign root a password immediately. You're first command: mysqladmin -uroot password password Can you explain why you're using that second line. Tell me a little more about your installation. You're installing MySQL __ machines which are __, running OS X version __. You're installing ___ version of MySQL. Stuff like that. As for getting your hostname on OS X, go to the command prompt and simply type hostname. The value returned is the hostname for your computer. You can also go into the System Preferences in the Sharing preference. The hostname is also the name of your computer. Regards, Adam -Original Message- From: Shane Korosec [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 9:30 PM To: [EMAIL PROTECTED] Subject: Setting root password on OS X I am a new mysql user. I am installing MySQL for the first time on my mac. I need to set a password for the 'root' user by using the following 2 commands: /usr/local/mysql/bin/mysqladmin -u root password password /usr/local/mysql/bin/mysqladmin -u root -h `hostname` password password The first worked, but i get the following error when i attempt to run the second: /usr/local/mysql/bin/mysqladmin: connect to server at 'skc.local.' failed error: 'Host ip address is not allowed to connect to this MySQL server' How do i know what my hostname is? I have entered a number of options of what i think it might be (like username 'localhost'...), but they have gotten me nowhere. I really don't know what im doing here, so i would appreciate any advice. Cheers Shane _ Get less junk mail with ninemsn Premium. Click here http://ninemsn.com.au/premium/landing.asp -- 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: deleting mysql users
That's how I do it. Take the user out of both the user and db tables. -Original Message- From: Alejandro Javier Pomeraniec [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 6:33 AM To: [EMAIL PROTECTED] Subject: deleting mysql users Hi !! Whenever i create a database user using GRANT command, mysql add a register inside the user table in mysql database. I've noticed that if i use REVOKE to revoke all privileges from that user, mysql does not delete the register from user table, but denies every permission for that user. What if i want to delete a user from user table? Should i delete it manually? is that safe? 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: ENCRYPT Syntax problem
First, don't use the word table in an insert into statement (e.g. `INSERT
INTO tbl_nm (col_1, col_2, col_3) VALUES ('a', 'b', 3)` where tbl_nm is the
name of a table in the database and col_1, col_2, etc. are names of columns
in the specified table). Second, ensure the number of columns your inserting
match the schema of the table - if not explicitly specify the columns.
Regards,
Adam
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, October 10, 2003 3:03 PM
To: [EMAIL PROTECTED]
Subject: ENCRYPT Syntax problem
Importance: High
Dear list:
I`m trying to insert some values into my table but I received a error
message when I type::
INSERT INTO TABLE admin VALUES ('admin',ENCRYPT('system'),15,0);
The error said:
Error 1064: You have an error in SQL syntax.
Could someone help me to clarify the correct syntax for ENCRYPT function,
please?
Thank you.
EDWIN LIMACHI N.
DATACOM - Instalaciones
TSE - INFONET BOLIVIA
Phone. 591-2-2123978
Movil: 591-715-29967
Fax: 591-2-2123975
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Installation problem
James, Hey James! I'm also running MySQL on OS X. Maybe I can lend a hand. First question, are you running this on an X-Serve? So you're doing the following # (1) Going to the mysql directory. cd /usr/local/mysql # (2) Kicking off mysql using the mysqld_safe script. sudo -v enter password sudo mysqld_safe --user=mysql However, from looking at the output you pasted. It doesn't look like MySQL isn't starting up (nothing about the daemon shutting down). How are you seeing its not up? Use the ampersand at the end of the line or you'll lose the terminal session. Once you've kicked off mysql type mysqladmin version to confirm the server is online. If you downloaded the absolute most recent version, you should see the result 4.0.15-standard (or something like it). Drop me a note. Let me know the 411. Regards, Adam -Original Message- From: Anderson, James H [IT] [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 10:32 AM To: [EMAIL PROTECTED] Subject: Installation problem I'm running OS X 10.2.8 and installed the latest OS X mysql package. The problem is the server won't start and there don't seem to be any logs available to help diagnose the problem. I've tried both sudo safe_mysqld Starting mysqld daemon with databases from /usr/local/mysql/data sudo /Library/StartupItems/MySQL start Starting MySQL database server Any help greatly appreciated! jim -- 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: Installation problem
James, Yea, I have to agree its not starting. Alright, you're probably already in the /usr/local/mysql or you would've seen a nasty message reminding you to execute the mysqld_safe script from mysql's directory. Ugh, (whatelse could it be).. if your running OS X Server.. the scripts name is mysql_safed not mysqld_safe. If it were that you'd probably get a script doesn't exist or something. If you were running as something other than root, you'd get a permission error. Hmm.. Well back to your earlier question. Yes, MySQL's got a bunch of logs. Take a look in the /usr/local/mysql/data/ directory. Look for a file named hostname.err. That log gets input from the mysqld_safe script. This is an interesting problem! I'm running 4.0.13-standard on my laptop; however, I'll throw 4.0.15 on a spare G4. Let me see if I can recreate the issue, and if not, I can tell you what I've done not to get it - make sense? Take a look at the error log and let us know what is says. Regards, Adam -Original Message- From: Anderson, James H [IT] [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 11:15 AM To: Fortuno, Adam Cc: [EMAIL PROTECTED] Subject: RE: Installation problem Adam, Yes, that's how I'm starting it. There are 2 reasons that lead me to believe that it's not running: 1. when I issue mysql -h localhost test I get this msg: ERROR 2002: Can' connect to local MySQL server through socket '/tmp/mysql.sock' (61) 2. when I issue ps -auxww|grep mysql I get no results other than the grep job Just to be sure, I did this: su (to root) mysqld_safe --user=mysql and got there msgs: Starting mysqld daemon with databases from /usr/local/mysql/data mysqld ended -Original Message- From: Fortuno, Adam [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 10:54 AM To: Anderson, James H [IT] Cc: [EMAIL PROTECTED] (E-mail) Subject: RE: Installation problem James, Hey James! I'm also running MySQL on OS X. Maybe I can lend a hand. First question, are you running this on an X-Serve? So you're doing the following # (1) Going to the mysql directory. cd /usr/local/mysql # (2) Kicking off mysql using the mysqld_safe script. sudo -v enter password sudo mysqld_safe --user=mysql However, from looking at the output you pasted. It doesn't look like MySQL isn't starting up (nothing about the daemon shutting down). How are you seeing its not up? Use the ampersand at the end of the line or you'll lose the terminal session. Once you've kicked off mysql type mysqladmin version to confirm the server is online. If you downloaded the absolute most recent version, you should see the result 4.0.15-standard (or something like it). Drop me a note. Let me know the 411. Regards, Adam -Original Message- From: Anderson, James H [IT] [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 10:32 AM To: [EMAIL PROTECTED] Subject: Installation problem I'm running OS X 10.2.8 and installed the latest OS X mysql package. The problem is the server won't start and there don't seem to be any logs available to help diagnose the problem. I've tried both sudo safe_mysqld Starting mysqld daemon with databases from /usr/local/mysql/data sudo /Library/StartupItems/MySQL start Starting MySQL database server Any help greatly appreciated! jim -- 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: Installation problem
Sounds like a plan. Actually, MySQL ships with OS X Server - sounds like your using the desktop version, which doesn't include it. Over lunch I threw 4.0.15 on a G4 running 10.2.6 - love that new installer. I had no issues. - Downloaded it. - Ran the installation package - kicked-off MySQL via mysqld_safe script I was able to rock and roll. It went even quicker than that ol'tar ball installation, which I thought was pretty easy. Let me know if the permission changes work. I'm curious to see what the issue is. Regards, Adam -Original Message- From: Anderson, James H [IT] [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 2:02 PM To: Fortuno, Adam Cc: [EMAIL PROTECTED] Subject: RE: Installation problem Adam, Knowing that Mac OS comes with a version of MySQL pre-installed, I didn't do anything special when I downloaded and installed the latest version. I assumed that anything in need of doing would be taken care of during package install. I guess that was just wishful thinking. I'll make the ownership/group changes you suggest below and let you know the results. Thanks! jim -Original Message- From: Fortuno, Adam [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 12:49 PM To: Anderson, James H [IT] Cc: [EMAIL PROTECTED] (E-mail) Subject: RE: Installation problem James: Yea, you can enable root or just use sudo. Sometimes sudo'ing can be a pain with the timeout and all. Anyway, at first blush I agree with you. When you installed mysql did you give mysql (or whatever account that runs MySQL) rights to the /usr/local/mysql/data directory? Make root the owner of the MySQL directory and place the directory under mysql (or whatever account that runs MySQL) group permissions? Assuming you're running MySQL as user 'mysql' in group 'mysql'. Do the following: chown -R root /usr/local/mysql chgrp -R mysql /usr/local/mysql chwon -R mysql /usr/local/mysql/data Then try mysqld_safe --user=mysql from the /usr/local/mysql directory. Got to run to lunch. Be back in about 45 min. Regards, Adam -Original Message- From: Anderson, James H [IT] [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 12:21 PM To: Fortuno, Adam Cc: [EMAIL PROTECTED] Subject: RE: Installation problem Adam, Well, you were right about there being logs :) (I hadn't looked into the data dir since it required root and I was reluctant to setup a root user--live and learn.) 031009 11:12:10 mysqld started 031009 11:12:11 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 031009 11:12:11 mysqld ended Presumably this is due to the permissions on the data dir which are set to drwrxr-x--- What's the best way to solve this? Change the directory permissions or change the characteristics of the mysql user? jim -Original Message- From: Fortuno, Adam [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 11:53 AM To: Anderson, James H [IT] Cc: [EMAIL PROTECTED] Subject: RE: Installation problem James, Yea, I have to agree its not starting. Alright, you're probably already in the /usr/local/mysql or you would've seen a nasty message reminding you to execute the mysqld_safe script from mysql's directory. Ugh, (whatelse could it be).. if your running OS X Server.. the scripts name is mysql_safed not mysqld_safe. If it were that you'd probably get a script doesn't exist or something. If you were running as something other than root, you'd get a permission error. Hmm.. Well back to your earlier question. Yes, MySQL's got a bunch of logs. Take a look in the /usr/local/mysql/data/ directory. Look for a file named hostname.err. That log gets input from the mysqld_safe script. This is an interesting problem! I'm running 4.0.13-standard on my laptop; however, I'll throw 4.0.15 on a spare G4. Let me see if I can recreate the issue, and if not, I can tell you what I've done not to get it - make sense? Take a look at the error log and let us know what is says. Regards, Adam -Original Message- From: Anderson, James H [IT] [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 11:15 AM To: Fortuno, Adam Cc: [EMAIL PROTECTED] Subject: RE: Installation problem Adam, Yes, that's how I'm starting it. There are 2 reasons that lead me to believe that it's not running: 1. when I issue mysql -h localhost test I get this msg: ERROR 2002: Can' connect to local MySQL server through socket '/tmp/mysql.sock' (61) 2. when I issue ps -auxww|grep mysql I get no results other than the grep job Just to be sure, I did this: su (to root) mysqld_safe --user=mysql and got there msgs: Starting mysqld daemon with databases from /usr/local/mysql/data mysqld ended -Original
RE: Error: unable to execute query - Problems using Win32 MySQL ODBC driver
Hmm, that is weird. Actually, I had the same issue (sort of). I was grabbing some data from a MySQL instance on an OS X workstation. I had shared the tables in Access via ODBC. I'd start to update the MySQL DB and get an ODBC error. Usually I'd get about 93 rows done (out of 1000 or so). You're getting the same issue going from Win32 to Linux via ODBC. I wonder if its a bug with the ODBC driver? What version are you using? Anyone else have this problem? Regards, Adam -Original Message- From: Pak Gza [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 2:22 PM To: Fortuno, Adam Subject: Re: Error: unable to execute query instantly. if i execute inserts in loop, about 30 runs well, then comes the error. if i reconnect i can exec the next 30, and so on... - Original Message - From: Fortuno, Adam [EMAIL PROTECTED] To: 'Pak Gza' [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 8:16 PM Subject: RE: Error: unable to execute query Peko, it breaks in the sec the sentence is... what do you mean by sec? Regards, Adam -Original Message- From: Pak Gza [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 2:06 PM To: Fortuno, Adam Subject: Re: Error: unable to execute query no, it breaks in the sec the sentence is executed. and i can reconnect at once thanks! thats fast...:) - Original Message - From: Fortuno, Adam [EMAIL PROTECTED] To: 'Pak Gza' [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 8:02 PM Subject: RE: Error: unable to execute query Did the connection time out? -Original Message- From: Pak Gza [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 1:44 PM To: [EMAIL PROTECTED] Subject: Error: unable to execute query hello, Im writing a db software using delphi7 mysql(3.23.41) (with standard dbexpress and data access components) The problem is: sometimes i get the error message unable to execute query, and the connection breaks. i only saw this occur on UPDATEs or INSERTs, but every time a different line in the code, which otherwise executes ok. If i use an xception handler, reconnect manually and execute the statement again it works fine, but it would be important to know whats causing this. I can invoke this error by executing updates or inserts repeatedly (needs about 30-50 repeated command) but it also happens when i update only 1 field in 1 record. Mysql logs show no error. The connection between the server clients is throught 10mbps lan, server runs on redhat 7.3, clients on windows machines. unfortunately, this is extremely urgent 4 me, any help would be appreciated... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error: unable to execute query - Problems using Win32 MySQL ODBC driver
Pako, That blows my theory. Regards, Adam -Original Message- From: Pak Gza [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 2:32 PM To: Fortuno, Adam Subject: Re: Error: unable to execute query - Problems using Win32 MySQL ODBC driver i dont use odbc. i only need libmysql.dll dbexpmysql.dll - Original Message - From: Fortuno, Adam [EMAIL PROTECTED] To: 'Pak Gza' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 8:30 PM Subject: RE: Error: unable to execute query - Problems using Win32 MySQL ODBC driver Hmm, that is weird. Actually, I had the same issue (sort of). I was grabbing some data from a MySQL instance on an OS X workstation. I had shared the tables in Access via ODBC. I'd start to update the MySQL DB and get an ODBC error. Usually I'd get about 93 rows done (out of 1000 or so). You're getting the same issue going from Win32 to Linux via ODBC. I wonder if its a bug with the ODBC driver? What version are you using? Anyone else have this problem? Regards, Adam -Original Message- From: Pak Gza [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 2:22 PM To: Fortuno, Adam Subject: Re: Error: unable to execute query instantly. if i execute inserts in loop, about 30 runs well, then comes the error. if i reconnect i can exec the next 30, and so on... - Original Message - From: Fortuno, Adam [EMAIL PROTECTED] To: 'Pak Gza' [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 8:16 PM Subject: RE: Error: unable to execute query Peko, it breaks in the sec the sentence is... what do you mean by sec? Regards, Adam -Original Message- From: Pak Gza [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 2:06 PM To: Fortuno, Adam Subject: Re: Error: unable to execute query no, it breaks in the sec the sentence is executed. and i can reconnect at once thanks! thats fast...:) - Original Message - From: Fortuno, Adam [EMAIL PROTECTED] To: 'Pak Gza' [EMAIL PROTECTED] Sent: Thursday, October 09, 2003 8:02 PM Subject: RE: Error: unable to execute query Did the connection time out? -Original Message- From: Pak Gza [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 1:44 PM To: [EMAIL PROTECTED] Subject: Error: unable to execute query hello, Im writing a db software using delphi7 mysql(3.23.41) (with standard dbexpress and data access components) The problem is: sometimes i get the error message unable to execute query, and the connection breaks. i only saw this occur on UPDATEs or INSERTs, but every time a different line in the code, which otherwise executes ok. If i use an xception handler, reconnect manually and execute the statement again it works fine, but it would be important to know whats causing this. I can invoke this error by executing updates or inserts repeatedly (needs about 30-50 repeated command) but it also happens when i update only 1 field in 1 record. Mysql logs show no error. The connection between the server clients is throught 10mbps lan, server runs on redhat 7.3, clients on windows machines. unfortunately, this is extremely urgent 4 me, any help would be appreciated... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Which is the best MySQL book?
Cheap books - http://www.bookpool.com -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 4:16 PM To: Anderson, James H [IT] Cc: [EMAIL PROTECTED] Subject: Re: Which is the best MySQL book? At 02:03 PM 10/9/2003, you wrote: Is there one that's especially outstanding? Thanks, jim -- Jim, MySQL Cookbook also by Paul Dubois is also a good read. I got both books (including MySQL 2nd Edition) at a 30% discount off the list price plus another $10 off with a coupon plus free shipping. So don't pay retail (unless you have plenty of $$). I got one book from Amazon and the other from Chapters. You need to search around for a coupon site and you'll usually find a $10 off coupon that can be applied to each bookstore. Since there was free shipping it didn't cost me anything to order it from 2 different locations. If they were any cheaper, Paul would have to pay me to take them.vbg Mike (el cheapo) -- 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: printing reports
If you're going the ODBC route, StarOffice sounds like a cheap alternative (and a good suggestion); however, there are a ton of other tools at your disposal too - MS Access if you've got access to a Win32 workstation. Regards, Adam -Original Message- From: Christensen, Dave [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 11:11 AM To: Wang Feng; [EMAIL PROTECTED] Subject: RE: printing reports You can, via ODBC, connect to MySQL using OpenOffice or Star Office which would give you a no-cost, or low-cost, option for producing reports from MySQL. There are a number of execellent web articles about interfacing either of these with MySQL. Try searching via Google. -Original Message- From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 9:49 AM To: Wang Feng; [EMAIL PROTECTED] Subject: Re: printing reports To get data from/into MySQL, you will always need a client software. So, the reporting thing should be implemented in your client software. Hope to be right!!! Thanks Emery - Original Message - From: Wang Feng [EMAIL PROTECTED] To: Wang Feng [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 12:22 Subject: Re: printing reports In MySQL, how can I create and print reports without the help of neither a scripting language(e.g. php) nor a thrid party software tool? bad grammar :-( but you guys understand what i'm saying, right? :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query help
Darryl, At first blush, I would try something like: DELETE faqcat FROM faqcat LEFT JOIN article ON faqcat.cat = article.cat WHERE article.cat Is Null; I vaguley remember MySQL implementing syntax permitting users to remove data from one or more tables. I think this might be your ticket. Regards, Adam -Original Message- From: Darryl Hoar [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 11:11 AM To: [EMAIL PROTECTED] Subject: Query help I have a two tables. One has catagories, the other has articles. I need a query that deletes catagories only if not articles have that catagory. Table faqcat(cat int not null auto_increment, name varchar(20)) Table article(aid in not null auto_increment, cat int, .) Want to : delete from faqcat where cat=1 (if no records in table article have field with cat set to 1. how do I accomplish this with a single query ? thanks, -D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL not null vs MSAccess required
Arunas, Actually, I think you mean MySQL is the backend for MS Access. Don't assume columns that are NOT NULL must have a default value. You are welcome to define columns as NOT NULL without a default value. Doing this will force the client (the Access form's) to require a value before inserting the record. CREATE TABLE customers ( ID INT(9) NOT NULL, CustName VARCHAR(30) NOT NULL, ... ) Type = MyISAM; Since you're using an MS Access front-end to allow users to enter data. Put logic in your form to require the users to populate a value for the customer name. Something like this: ** Assuming you have a form with customer name field named tbCustName. Private Sub Form_BeforeUpdate() Dim strMsg as String On Error Resume Next If (Not ValidateSelf()) Then '** Notify the user that the form failed validation. Let strMsg = Please ensure all required fields are populated. Call MsgBox(strMsg, vbOkOnly) '** Cancel's the save event. Call DoCmd.CancelEvent EndIf End Sub Private Function ValidateSelf() Dim blnIsValid As Boolean '** Determine if the field has a value. If (Not Nz(Me.tbCustName.Value, ) = ) Then Let blnIsValid = True '** Return the results of the validation process. Let ValidateSelf = blnIsValid End Function Its been a while since I've used Access for soemthing like this. My VBA might be a little rusty. The point is you want to enforce user population of the customer name field in the client. If you do it from MySQL, the odbc driver will generate a runtime error, which Access will then need to capture - you should capture errors anyway. Regards, Adam -Original Message- From: Arunas Mila?auskas [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 12:23 PM To: [EMAIL PROTECTED] Subject: MySQL not null vs MSAccess required Hello, I'm Using MSAccess as backend for my MySQL database. Tables are linked with MyODBC 3.51, so all data user enters through MSAccess forms. I need that some fields (CustName) in MySQL table (CUSTOMERS) would be required, it means, that user must fill a value into that field (CustName) displayed on MSAccess form (Customers). MySQL table create statement is: create table CUSTOMERS (ID `ID` int(9) NOT NULL, `CustName` Varchar(30) NOT NULL DEFAULT ''); Default value in table declaration is requred as you know for NOT NULL columns. The problem is: When user enters a new record in MSAccess form and does not even touch field CustName, new record is created with empty string as default value If user enters for example 1 and then deletes the value in MSAccess form, Access reports, that value must not be NULL. But what can I do if user does not even go into that field and leave empty field? User Must Enter A Value How to do this without writing many code in every form with required (NOT NULL) fields? Thanks for any help, It is needed very very much because for now any user can not fill required fields! It would be very very appreciated Regards, Arunas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Check (expr) Syntax and/or other fields checks
Miguel: First: Be more specific. Did you have an expression in mind? Second: Yes, use InnoDb tables to enforce referential integrity. See the URL (below) for information on InnoDb table usage. Regards, Adam InnoDb in MySQL - http://www.mysql.com/doc/en/InnoDB.html http://www.mysql.com/doc/en/InnoDB.html -Original Message- From: Miguel Ernesto [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 12:57 PM To: [EMAIL PROTECTED] Subject: Check (expr) Syntax and/or other fields checks Hello everyones, Did somebody know: First: What kind of expr it is allowed on the check condition on: create table syntax? Second: Is it posible to force conditional FOREIGN KEY checks? for example: We make One table who have one UNIQUE Key and one special condicion: TRUE or FALSE. Then we create a second table who refers one field to the FOREIGN key table ... but we got that there are 1 or 2 posible values for each row on the FOREIGN table: 'the foreign key + true' and 'the foreing key + false' for each row, but we want to allow just 2 posible values when the foreign key condition its set to TRUE, and only 1 when its set to false. And we want be completly sure that just the correspondings values are entered on the second table. Example: First table: Primary key field: SKU+Special_Condition SKUSpecial_ConditionAllow A-1FALSEJUST one value with this SKU on the foreign table B-1TRUE 2 values with this SKU on the foreign table: 'B-1: FALSE' and 'B-1: TRUE' Second table: Foreign key field: SKU+Special_Condition SKUSpecial_ConditionIntegrety_check A-1TRUE Refuesed!: not valid value A-1FALSEOk! B-1TRUE Ok! B-1TRUE Ok! Why do I need this? 1. I have a series of rows who can take one or more values depending on a special condition who much be specified first. 2. I need to choose a specific field depending on the special condition given, 3. I do not want to allow to enter special conditions on the foreign table who are not allowed the primary table, and 4. Later I'm going to retrieve that specific value, based on the special condition given Example: SKUSpecial_ConditionValue1 Value2 A-1FALSE10 null B-1TRUE 20 30 C-1 TRUE 10 20 As you can see: - I would like to retrieve from A-1 just Value1 because the special condition just allow the first value - I would like to retrieve from B-1 or C-1 Value1 or Value2 because the special condition allow both values Any Idea? A BETTER LIFE, starts with the best and more exclusive products for HEALTH, BEAUTY and HOME, the Sweet 16 by http://www.quixtar.com/ Quixtar. Miguel Ernesto Pérez Cabrera Independent Businnes Owner IBO# 1066703 ___ For more professional information, please go to: http://www.quixtar.com/ Quixtar and http://www.pronetvirtual.com/ Pronet and contact me: at mailto:[EMAIL PROTECTED] [EMAIL PROTECTED], or call me now at (809) 530-3433. If you have not visited yet, go to http://www.pronetvirtual.com/ Pronet choose the region or country closest to you, register like guest, and post a comments about what was that who get more interesting to you.
RE: FW: MySQL not null vs MSAccess required
Are you serious? Its certainly a feature I can do without. Just one more idiosyncrasy to remember for another RDBMS. Regards, Adam -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 5:46 PM To: [EMAIL PROTECTED] Subject: Re: FW: MySQL not null vs MSAccess required Cal Evans wrote: I humbly submit an apology. You are correct. This is a bug (No it is NOT a feature) While you may not like it, this definitely is a feature (or an intentional design decision, at least), not a bug. See the docs at http://www.mysql.com/doc/en/constraint_NOT_NULL.html. The first line is, To be able to support easy handling of non-transactional tables, all fields in MySQL have default values. So, if you don't set a default for a column, mysql chooses one for you. With a few exceptions, NULLable columns default to NULL, NOT NULL columns default to 0 (zero) or '' (empty string). You can change this behavior by building your own mysql from source with the -DDONT_USE_DEFAULT_FIELDS compile option. you should be able to define a field as NOT NULL without a default or at the very least, define the default as NULL. This does not make sense to me. Allowing NULL as the default for a column declared NOT NULL would defeat the purpose of declaring it NOT NULL in the first place. Michael -- 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]
ODBC Error
All, Yo gang! Having a problem with the MySQL ODBC 3.51 driver. I've got a MySQL volume which has a user table (tbl_usr). I'm linking this table to an Access database on my workstation (via ODBC link). The account being used for the connection has select, update, and insert rights to all tables in the database. I'm populating the linked (MySQL) table with data I've got on my workstation in an MS Access database. To do this, I'm simply executing an insert into ... select ... from statement. INSERT INTO tbl_usr (fst_nm, lst_nm, tle_nm, email_addr) SELECT tbl_stage.First, tbl_stage.Last, tbl_stage.Title, tbl_stage.Email FROM tbl_stage; However, each time I execute the statement I receive an error (see attached file - mysql_odbc_err.pdf). The error message provided isn't very helpful. Can anyone elaborate? Client Environment: - Windows 2000 (SP3) - MS Access 2000 Server Environment: - MacOS X 10.2.6 (Jaguar) - MySQL 4.0.13-standard Regards, Adam mysql_odbc_err.pdf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
** Composit Key - Is it possible, and if so, what is the syntax?
All, I've got an InnoDb table that requires a composit key constraint. However, I'm not certain how to word the DDL syntax. Pretending for a sec this was SQL Server 2000 or Sybase ASE 12.5 I'd use the following. CREATE TABLE foo( pri_1 INTEGER NOT NULL, pri_2 INTEGER NOT NULL, pri_3 INTEGER NOT NULL, test_data VARCHAR(120) NULL CONSTRAINT pk_foo PRIMARY KEY (pri_1, pri_2, pri_3) ) Type = InnoDB; In MySQL, this would return an error. I'd really appreciate knowing if its possible and (if so) what the syntax is. Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Running a definition file from MS-SQL
Tormod, I feel your pain brother! Actually, I do this allot more than I'd prefer to admit. I assume when you say MS SQL you mean SQL Server 2000. When you say MySQL, you mean MySQL 4.0.x. Scripting the database/tables from query analyzer doesn't produce files, which are immediately executable in MySQL - as you know. The reason being the syntax is a little different and more importantly SQL Server supports a number of features MySQL doesn't. Keep an eye on the dates. The format for dates in SQL (MM-DD-) is a different from MySQL (-MM-DD). My SQL wraps dates with single quotes while SQL Server uses hashes. If you're using referential integrity, try InnoDB or BDB tables versus MyISAM tables. Note, default values are constraints in SQL Server not in MySQL. No triggers or stored procs. Function names will change from sp_xxx to the mysql equivalent. Lots of security differences especially where user setup is concerned. Make sure you're security scripts account for user ID and host. To answer your question, there is a data base migration tool, which I've used to move DBs from any_db to Sybase's ASE. Not sure if it works for MySQL. To be honest, I just usually run through the script myself and make the necessary changes. If you run into any specific problems, shoot me a note. I might be able to help. Regards, Adam -Original Message- From: Tormod Halvorsen [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 9:41 AM To: [EMAIL PROTECTED] Subject: Running a definition file from MS-SQL Hi all! (My apologies if this has been beaten to death allready, but I *did* search the archives without much luck.) I'm moving from MS SQL to MySQL. Before jumping from the old server, I scripted out the definitions for tables and their fields, thinking I might just run them into MySQL. Guess I need to edit the script some, because it keeps giving me a Syntax error message. Anyone know of a good source for info on how to make MS SQL scripts work on MySQL? I also have a copy of the database exported in MS Access format to hold the data - don't suppose it's any easier that way, uh? Thanks! peace, Tormod in Stockholm -- 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: SELECT only unique records
SELECT DISTINCT(state_ID) FROM financial_master WHERE category_ID = '1'; OR SELECT state_ID FROM financial_master WHERE category_ID = '1' GROUP BY state_ID; -Original Message- From: Comcast [mailto:[EMAIL PROTECTED] Sent: Friday, September 12, 2003 2:03 PM To: [EMAIL PROTECTED] Subject: SELECT only unique records I am sure this is a slam-dunk, but I am new to this and stumped ... thanks. I have the following statement, but I need it to pull only unique listings - I get repeated items. SELECT state_ID FROM financial_master WHERE category_ID = '1' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Configuration File Creation - Automatic or manual?
All, A little confused about what creates configuration (my.cnf) files. I'm running MacOS X 10.2 (Jaguar) running MySQL 4.0.x. I was looking for the global and server level configuration files in the following directories: Global - /etc/my.cnf Server - /mysql/data/my.cnf I was unable to locate the files. Is this something I've got to create, or is it generated during installation? If the files aren't generated during installation, what does MySQL use when the .cnf files don't exist? http://www.mysql.com/doc/en/Option_files.html Regards, Adam Fortuno (x4904) Adam Fortuno (E-mail).vcf About the auto-start-up/shut-down on OS X: OS X doesn't provide (as best I can find) a way through aqua (OS X's GUI) to automatically start and stop MySQL. However, OS X allows items to be kicked-off automatically at boot-up through the /Library/StartupItems/ directory. The book Managing Using MySQL from O'Reilly (ISBN: 0-596-00211-4) has a page dedicated to the setup (page 69). I haven't been able to get this to work. I was able to download a binary that started MySQL server. The installer setup everything in the StartupItems folder. I got the stuff from www.entropy.ch (Marc Liyanage's site). With this, MySQL server starts at boot, but I cannot get it to automatically shut down! So each time I shutdown I have to first remember to shutdown mysqld (that sucks). For now, I'm just starting and stopping it manually (makes it easier to remember I have to shut it down). I'll keep at it though. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to do a random ORDER BY in a SELECT statement
Comcast, Simply order by the random function, RAND(). For example: SELECT aff.* FROM financial_affiliates AS aff WHERE aff.state = 'wa' AND aff.category = '3' ORDER BY RAND(); Regards, Adam -Original Message- From: Comcast [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 10, 2003 8:05 PM To: [EMAIL PROTECTED] Subject: how to do a random ORDER BY in a SELECT statement I am querying a table and using the following statement: SELECT * FROM financial_affiliates WHERE state = 'wa' AND category = '3' ORDER BY name I would like to ORDER BY RANDOM - is there an easy way to do that. Thanks. -Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL SSL
Hong, I suggest reviewing the MySQL manual (sections 4.3.9.1 through 4.3.9.4 inclusive). As best I know, those are the manual sections dealing with SSL. 4.3.9.1 - http://www.mysql.com/doc/en/Secure_basics.html 4.3.9.2 - http://www.mysql.com/doc/en/Secure_requirements.html 4.3.9.3 - http://www.mysql.com/doc/en/Secure_Create_Certs.html 4.3.9.4 - http://www.mysql.com/doc/en/Secure_GRANT.html Regards, Adam -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 09, 2003 10:34 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: MySQL SSL Dear Sir/Madam: I have download MySQL 4.0 Windows version server binaries. Would you please tell me is this version support SSL? How to config it? Thanks a lot, Hong -- 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: Functions
Gustavo, Prefix them with 'SELECT FUNCTION_CALL;' (e.g. 'SELECT VERSION();'). Regards, Adam -Original Message- From: Gustavo Castro [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 10:03 AM To: [EMAIL PROTECTED] Subject: Functions Hello, in order to call a native function do i have to add something else to its name, i.e. mysql_get_update? because everytime i try to call my native function from the mysql prompt it gives me a syntax error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SubQueries and IN
Andy: Sub queries are supported as of version 4.1 (see link #1). As for your query, double-check the syntax in the select piece. Specifically take out the 'sales.sale_id' and anything else from the 'sales' table. Then try again. Regards, Adam Link #1 - http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html -Original Message- From: Andy Hall [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 10:02 AM To: [EMAIL PROTECTED] Subject: SubQueries and IN Hi, I have just started using MySQL from MSSQL 7. I need to port the following into MySQL from an existing (working) query on MSSQL Server: SELECT product_id, name, description FROM products WHERE product_id NOT IN (SELECT product_id FROM sales WHERE customer_id = 10) i.e. get all the products that a particular customer has not already bought This errors, and I have since read that the MySQL IN does not allow sub-queries, but also seen examples of it done. Is it only supported in a later version? We are running v. 3.23.3. I have also tried: SELECT product_id, name, description, sales.sale_id FROM products LEFT JOIN sales ON products.product_id = sales.product_id WHERE sales.customer_id = 10 AND sales.sale_id IS NULL This does not return any records as it seems to ignoring the LEFT JOIN part when I stick on the WHERE sales.customer_id = 10. (pretty sure this query would work in MS-SQL) There must be a way to do this, but I dont seem to be able to put my finger on it and I would appreciate any help! Thanks Andy Hall. -- 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]
2-Questions: Starting MySQL and Shutting Down on OS X
All: (1) I've got /user/local/mysql/data in my path, but when I attempt to run mysqld_safe I get a message reading: Please do a cd to the mysql installation directory and restart this script from there as follows: ./bin/mysqld_safe Do I really need to be in the installation directory to kick-off the MySQL server? If so, why? (2) Anyone running OS X have a good way to automatically shut MySQL server before shutdown. Currently, I've got to do it manually. If someone else sits at my workstation, they may or may not restart/shutdown without shutting down the server. Before I invest time in trying to resolve, let me know if there is something quick and easy out there. Regards, Adam Adam Fortuno (E-mail).vcf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 1045: Access denied for user: 'root@localhost' (Using p assword: YES)
Jerry, Don't forget that MySQL authenticates an entity, which is based on the user name and host. For example, on my MySQL boxes root can only login from the localhost. If I attempt to login with root from another host, I am denied access. Regards, Adam -Original Message- From: Jerry M. Howell II [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 1:24 PM To: [EMAIL PROTECTED] Subject: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Hey there all, Here is the problem I am haveing and was wondering if anyone can help me here. I was getting the following error when loging into mysql ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) After searching for an answer I found the answer was to re-start using --skip-grant-tables and change the password then restart mysql useing the normal startup script. This worked well. The next step is where I run into problems. After I log into mysql in the normal mode and reset the password I go back to ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:YES). Also, other users are reporting that they can't access thier databases. Based on what I am seeing here I am able to login to mysql as root as long as there is no password. Once I set a password it wont let me connect. Also found that users can't login to the databases. Does anyone have any idea whats going on and how I might be able to resove this problem? Thanks, -- Jerry M. Howell II -- 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 can I select the column names?
I don't follow your question. Please elaborate. Cheers, Adam -Original Message- From: Jake Johnson [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2003 1:46 AM To: Mysql Subject: How can I select the column names? I am trying to select the column names from a table to be displayed in a web page. Is there anyway to select column names without desc? Thanks, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Tires, and Wheel Packages. -- 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: ISAM....the name sounds so familiar....
Chris, Wow, tell us how you really feel. Less age is getting the better of me ISAM (indexed sequential access method) tables are used by a few different DMS such as Informix. ISAM was original developed by IBM. It allows data access sequentially or randomly via indices. Anyway, are you asking if MySQL can simply use the .ISM files from this other OS? To the best of my knowledge, ISAM files are stored in a OS' native language - the ISAM files are not platform independent. MySQL Specific: http://www.mysql.com/doc/en/ISAM.html Regards, Adam -Original Message- From: Chris Nolan [mailto:[EMAIL PROTECTED] Sent: Thursday, September 04, 2003 9:48 PM To: [EMAIL PROTECTED] Subject: ISAMthe name sounds so familiar Hi all, One of my clients has two applications running on SCO OpenServer (I said a naughty word...oh dear...) boxes. Personally, I have major personal and professional problems with this current arrangement and am trying as quickly as possible to move them away from these ancient things. Interestingly, both of the applications in question (written in COBOL for reasons I fail to understand) both mention ISAM storage engines when starting. Is there any relationship here to the storage engine MySQL uses as it's default? I'm just looking for an easy way of pulling this data out should it be needed for whatever reason and would prefer to do it with a proper database, written in a proper language, running on a proper operating system (preferably running on a proper server platform, like an x86-64 box). Regards, Chris -- 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: Select statement
Darryl, The following query would return all rows in employee that existed in emp2 (**Assuming 'employee' and 'emp2' have a common field 'key'). SELECT e1.* FROM employee AS e1, emp2 AS e2 WHERE e1.key = e2.key; There is a great book that introduces SQL (SQL-1 and SQL-2 concepts) titled The Practical SQL Handbook (ISBN: 0-201-44787-8), which I've kept in my library since college. If you're in the mood, thumb through it. Regards, Adam -Original Message- From: Darryl Hoar [mailto:[EMAIL PROTECTED] Sent: Thursday, September 04, 2003 2:58 PM To: [EMAIL PROTECTED] Subject: Select statement Greetings, I am just trying to wrap my brain around joins. I have a table employee. For each record in employee, I want to see if a record exists in table emp2 based on a field value in both tables. IE, for each employee for each emp2 if employee.field1 = emp2.field3 then do something interesting. end emp2 loop end employee loop. can someone point me to the right join syntax to get this done ? thanks, Darryl -- 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: mysqlimport command question
Darryl, Sorry for not responding sooner. Looking at this. First I'd recommend delimiting by something other than tabs. I typically use pipes '|' as my delimiter. Tabs and commas can be problematic for a variety of reasons. Second, double check your dates. All I can think of is SQL Server syntax right now, but I remember having some problems with dates being imported in the past. If memory serves, I had to put them in -MM-DD format. That should clean you're import file up. Regards, Adam -Original Message- From: Darryl Hoar [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 5:10 PM To: 'Fortuno, Adam'; [EMAIL PROTECTED] Subject: RE: mysqlimport command question To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: RE: mysqlimport command question Darryl, Provide a copy of the table's details either with a describe table output or the table's definition and a sample of the input file's top 5-rows. Regards, Adam OK, here is the table structure: mysql describe emp2; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | empnum | varchar(4) | YES | | NULL| | | name | varchar(30) | YES | | NULL| | | fname | varchar(20) | YES | | NULL| | | lname | varchar(30) | YES | | NULL| | | email | varchar(60) | YES | | NULL| | | ext| varchar(4) | YES | | NULL| | | listit | char(1) | YES | | NULL| | | bdm| int(11) | YES | | NULL| | | bdd| int(11) | YES | | NULL| | | hdate | datetime| YES | | NULL| | ++-+--+-+-+---+ 10 rows in set (0.00 sec) mysql the first 5 records of emp2.txt are: 100 ALBERT JEFFREYALBERT [EMAIL PROTECTED] N 3 6 04/11/88 101 ALBERT STEVEN ALBERT [EMAIL PROTECTED] N 811 08/19/97 105 ARKEMA DORMANDARKEMA [EMAIL PROTECTED] N 729 10/01/87 110 ARKEMA DUANE ARKEMA [EMAIL PROTECTED] N 428 10/28/91 125 BECKER ALOYSIUS BECKER [EMAIL PROTECTED] N1118 03/01/93 Sorry about the wrap, but in the file each is on its own line (no wrap). Cut and paste causes a wrap in outlook. thanks, Darryl -Original Message- From: Darryl Hoar [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 4:31 PM To: [EMAIL PROTECTED] Subject: mysqlimport command question greetings, I have created a text file in windows (from a database) that has the format empnum name fname lname ext email listit bm bd hd bm, bd are integers and hd is date. I have tried to import using the follow command #mysqlimport -u root -pmypass -d --local iweb emp2.txt it imports the 97 records with 970 errors. When I look at the records created, the empnum field is properly populated, but all other fields are NULL. what am I screwing up here ? thanks, Darryl -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: HAVING column not in select_statement
Stefano, OOh! You're taking me back a few years. As best I remember, the 'HAVING' operator applies to the results returned by the query not in shaping the results to be generated. For example, if you have a table that stores trouble tickets per user. You select the user's last name, first name, and number of tickets they've called in. In your query, you use a 'Having' statement to only see people who've had more than 1-ticket (see example below) -- Example Query: -- Query returns a list of users that have had more than one -- trouble ticket with a severity greater than 4 (severity is -- 1 through 5). SELECT COUNT(tkt.*), tkt.last_name, tkt.first_name FROM tbl_trouble_tickets AS tkt WHERE tkt.severity 4 HAVING COUNT(tkt.*) 1; -- Example Result: 3, Jackson, Janet 2, Jackson, Michael The RDMS is going to first retrieve a record set with count, last name, first name for all users with tickets of severity greater than 4. From THAT recordset the DBMS will display items with a count greater than 1. The point being, 'HAVING' only operates on the recordset retrieved from the prefixing query. Therefore, if a field doesn't exist in the query, you cannot use it in the 'HAVING' statement. Regards, Adam -Original Message- From: Stefano Fraccaro [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2003 9:44 AM To: [EMAIL PROTECTED] Subject: HAVING column not in select_statement I have a query with a column in HAVING clause that not refer any column listed in SELECT statement because I don't need to group by this column and ... this query don't work. It's possible or this feature is planned for the future? Any solution? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Show database problem
Albert, This may sound minuscule. You're certain that 'uma' is a user account and not a database or table? Normally, you use SHOW GRANTS FOR user_account. See example: mysql SHOW GRANTS FOR test_usr; +--+ | Grants for [EMAIL PROTECTED] +--+ | GRANT USAGE ON *.* TO 'test_usr'@'% | GRANT ALL PRIVILEGES ON 'tempdb'.* TO 'test_usr'@'%' | GRANT ALL PRIVILEGES ON 'test'.* TO 'test_usr'@'%' +--+ However, if you attempt to show grants on an object with SHOW GRANTS FOR object_name you get the following error. See example: mysql SHOW GRANTS FOR tempdb; ERROR 1141: There is no such grant defined for user 'tempdb' on host '%' Albert, one suggestion. Next time provide the exact error message. It makes it easier to assist. Regards, Adam -Original Message- From: Albert [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:46 AM To: [EMAIL PROTECTED] Subject: Re: Show database problem When I enter : mysql\binSHOW GRANTS FOR uma; I get a response that show is not a command etc.. This is on Win32 version 4.x The same happens when I try it from mysql and also after I run mysqld Any ideas what is wrong here? Thanks Albert - Original Message - From: Joris Beckers [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 01, 2003 11:19 AM Subject: Show database problem I've got a user, admFrederic He got the grants shown below. Those are correct and I configured them that way. mysql SHOW GRANTS FOR admFrederic; +--- + | Grants for [EMAIL PROTECTED] | +--- + | GRANT USAGE ON *.* TO 'admFrederic'@'%' IDENTIFIED BY PASSWORD '6ddf5e1a1cc75e8a' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `cehdb`.* TO 'admFrederic'@'%' | +--- + But, when I log in using that username. That user can access the main database... mysql show databases; +--+ | Database | +--+ | cehdb| | mysql| +--+ Does anyone know how to fix this? Only cehdb should be accessable. Also, the user can run SQL commands on the mysql database. GRANT commands etc. are not possible. This is the version info. mysql status; -- mysql Ver 11.18 Distrib 3.23.53, for Win95/Win98 (i32) Connection id: 351 Current database: Current user: [EMAIL PROTECTED] Server version: 4.1.0-alpha-max-nt Protocol version: 10 Connection: nemesis via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: Uptime: 1 hour 6 min 14 sec Thanks, Joris -- 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: mysqlimport command question
Darryl, Provide a copy of the table's details either with a describe table output or the table's definition and a sample of the input file's top 5-rows. Regards, Adam -Original Message- From: Darryl Hoar [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 4:31 PM To: [EMAIL PROTECTED] Subject: mysqlimport command question greetings, I have created a text file in windows (from a database) that has the format empnum name fname lname ext email listit bm bd hd bm, bd are integers and hd is date. I have tried to import using the follow command #mysqlimport -u root -pmypass -d --local iweb emp2.txt it imports the 97 records with 970 errors. When I look at the records created, the empnum field is properly populated, but all other fields are NULL. what am I screwing up here ? thanks, Darryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: It is secure to access MySQL thru internet?
Sure. If you use a secured connection. A$ -Original Message- From: Flavio Tobias [mailto:[EMAIL PROTECTED] Sent: Friday, August 29, 2003 1:11 PM To: [EMAIL PROTECTED] Subject: It is secure to access MySQL thru internet? I need to access a database thru internet. It is secure to do this using MySql? Thanks Flavio Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question about sub-query.
Greg, Since you're executing this from a perl script. Try saving the result of SELECT MAX(cycle) FROM test.results WHERE snum = '$snum'; to a variable. Then use that variable in your embedded SQL statement. This alleviates that issue all together. If you're updating multiple rows, you're query should run faster since it won't have to re-calculate the max() function for each iteration - maybe SQL optimizer treats it as a constant after the first iteration, but whatever. About your specific question, why the syntax doesn't work. I would double check the version of MySQL you're using (SELECT VERSION();). If you're like me, you're using version 4.0.x,(Production) which doesn't have sub queries. I think sub queries are supported as of version 4.1.x (alpha) - see the below URL for more details. Otherwise, you're syntax looks fine. Regards, Adam http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] I am getting ERROR 1093 at line 1: You can't specify target table 'results' for update in FROM clause. I found an article on the internet that says an update with a sub-query that references the same table is illegal. Their example solution didn't help any though because they were using a join and the rewrote the update with EXISTS. I'm trying to update some elements of a row in which another of the values in that row is a MAX. Sample update statement: my $c1Str = qq[ UPDATE test.results SET result = '$result', WHERE snum = '$snum' AND cycle = (SELECT MAX(cycle) FROM test.results WHERE snum = '$snum') ]; I could break it into two steps, but I wonder if that wouldn't be much less efficient. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: listing all people who have the same firstname and lastname
Grant, Sure, anything's possible. Assuming you're table looks something like this: CREATE TABLE people ( id INT NOT NULL, fname VARCHAR(15) NULL, lname VARCHAR(20) NULL ) Type=InnoDB; With data something like this: INSERT INTO people (id, fname, lname) VALUES (1, 'John', 'Smith'); INSERT INTO people (id, fname, lname) VALUES (2, 'John', 'Smith'); INSERT INTO people (id, fname, lname) VALUES (3, 'Erika', 'Snow'); INSERT INTO people (id, fname, lname) VALUES (4, 'Michael', 'Boxer'); INSERT INTO people (id, fname, lname) VALUES (5, 'Julian', 'Baser'); INSERT INTO people (id, fname, lname) VALUES (6, 'Mary', 'McKnight'); INSERT INTO people (id, fname, lname) VALUES (7, 'Julian', 'Baser'); I would suggest a SQL statement like this to get the results you're looking for. SELECT COUNT(a.id), a.lname, a.fname FROM people AS a GROUP BY a.lname, a.fname HAVING COUNT(a.id) 1; You're selecting the information that's important to you (count, last name, and first name). Grouping by first and last name. Then from that list, only reviewing those with a count greater than 1. Presumably anything with a count of 1 is unique in the table. Regards, Adam -Original Message- From: Grant Cooper [mailto:[EMAIL PROTECTED] Sent: Friday, August 29, 2003 3:42 PM To: [EMAIL PROTECTED] Subject: listing all people who have the same firstname and lastname I'm trying to get a query to work by listing all the people in a row with the same last name and first name. key, fname, lname 1 ,John, Smith 4, John, Smith 5, Cody,Edwards 2, Cody, Edwards Don't list anyone that has a unique first name last name. Is this possible? Trying to use the Count command. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: It is secure to access MySQL thru internet?
Flavio, Sure. If you wanted to MySQL into a DB over the internet. I'd think ssh'ing would do the trick. This actually has nothing to do with MySQL, but allot to do with your OS. If you're running OS X, Linux, or some forms of Unix. SSH is probably a default package. Follow-up with the documentation for your OS. If its not available through the OS, you'll need some sort of 3rd party utility. Regards, Adam -Original Message- From: Flavio Tobias [mailto:[EMAIL PROTECTED] Sent: Friday, August 29, 2003 3:53 PM To: Fortuno, Adam; [EMAIL PROTECTED] Subject: Re: It is secure to access MySQL thru internet? What do you mean with secure connection, ssh? How to configure this on MySql? - Original Message - From: Fortuno, Adam [EMAIL PROTECTED] To: 'Flavio Tobias' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, August 29, 2003 2:17 PM Subject: RE: It is secure to access MySQL thru internet? Sure. If you use a secured connection. A$ -Original Message- From: Flavio Tobias [mailto:[EMAIL PROTECTED] Sent: Friday, August 29, 2003 1:11 PM To: [EMAIL PROTECTED] Subject: It is secure to access MySQL thru internet? I need to access a database thru internet. It is secure to do this using MySql? Thanks Flavio Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
