Multiple items in an ALTER TABLE statement
Was in the midst of doing something today and I attempted to drop a number of columns in a table with the following: ALTER TABLE tmp DROP COLUMN col_1, col_2, col_3, col_4; Unfortunately MySQL gave me an error reading: ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'col_2, col_3, col_4' at line 1 Can you not have multiple columns names in an alter statement? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't start MySQL on Mac OS X
Nicos, Actually, MySQL should be installed by an administrator, but it should be executed by root, which would permit the writing to the tmp dir. Meaning everytime you kick off MySQL server, you'll need to do so as root. This is usually how I dow it: cd /usr/local/mysql sudo echo sudo ./bin/mysqld_safe --user=mysql [Note: You'll need to do this on an account with administrator access. When you type sudo echo, you'll need to type your OS X user password.] My boy Marc has a slam'in site for MySQL on OS X http://www.entropy.ch. Since MySQL nor Apple have a startup script for MySQL (to kick off the DB at boot), I use the one he's generated - see his site for more details. Regards, Adam On Thursday, August 7, 2003, at 10:54 PM, Nicos Kekchidis wrote: Todd and Guys, Your advice helped me too. I think either Apple screwed up when set up /tmp directory to be writeable by root only or since MySQL package has bug or shall be installed ONLY as root user. - Nicos -- 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: SubQuery bug in 4.1
Mark, I agree there is no reason for the sub-select. I'd make one change in response to the original request - asking for the most recent item. Take Mark's statement and suffix AND m.KeyDate = MAX(m.KeyDate); to the last WHERE statement (see example). UPDATE main m, sub s SET m.Value = s.subValue WHERE m.id= s.mainid AND m.KeyDate = MAX(m.KeyDate); I'm not sure if this is a bug with MySQL. What are the results you're getting? The sub-select you wrote will retrieve multiple rows (assuming you have multiple rows for a sub.MainId, which I assume you do by the requirment for the most recent item). Maybe I'm really off :( Regards, A$ - Original Message - From: Mark Hedges [EMAIL PROTECTED] Date: Monday, August 4, 2003 11:16 am Subject: Re: SubQuery bug in 4.1 Surely this will just work? update main,sub set main.Value = sub.subValue where main.id=sub.mainid; Or have I misunderstood what you are wanting? -- Mark - Original Message - From: Daniel Kiss [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, August 04, 2003 4:09 PM Subject: SubQuery bug in 4.1 Hi all, I have two tables CREATE TABLE main ( ID int not null, Value int ); CREATE TABLE sub ( mainID int not null, KeyDate date not null, SubValue int not null ); I want the Value field in the main table to be set to the latest SubValue in the sub table. I suppose this syntax should work. But it does not, and sets the Value fields to incorrect values. update main set Value = (select SubValue from sub where main.ID = sub.mainID order by KeyDate desc limit 1) Any ideas? Thanks, Dan -- MySQL Bugs Mailing List For list archives: http://lists.mysql.com/bugs 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: Date to Days Query
Mike, Sure, try this: UPDATE table SET days = TO_DAYS(exp_dte) - TO_DAYS(NOW()); As a side note, I recommend you make this calculation in whatever application this DB supports. Otherwise you will continue to need running a script to update the dates. Regards, A$ On Saturday, August 2, 2003, at 06:07 PM, Mike Blezien wrote: Hello, I need to update one of our mysql tables, which has about 60,000 entires and correct the amount of days remain on each data record. An example of one of the data entires is: memiddaysregdate expdate -- 625290 | 5 | 2003-07-15 | 2003-08-16 now the days should be 14 and not 5 days. I have been trying to update the table with a single SQL query but haven't come up with a way to do this.. I'm sure it's something simple but I can't seem to come up with it. What is the best way to accomplish this in a single query to update the entire table so all the days are accurate according to the expdate, and change the days so they are correct ?? thx's -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Web Hosting http://www.justlightening.net Tel: 1(985)902-8484 MSN: [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: Nested SELECT statements problem
What version of MySQL are you using? Regards, A$ On Saturday, August 2, 2003, at 11:45 PM, Pascal Délisle wrote: Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur = ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks 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: mysql_info not very informative?
Woah! 10.3! Some is using the developer preview of Panther! Actually, MySQL doesn't normally give overrun cut-off information (best I know). Use MySQL 4.0.x on 10.2.6, and MySQL has always performed that way. Regards, A$ On Friday, August 1, 2003, at 05:37 PM, Ray Kiddy wrote: I am trying to insert data using the C API. Particulars: OS: Mac OS X 10.3 (7A179) MySQL: MySQL 4.1.0-alpha table type: tried both MyISAM and InnoDB I have a table: mysql describe testtable; ++-+---+--+-+-+---+ | Field | Type| Collation | Null | Key | Default | Extra | ++-+---+--+-+-+---+ | _PK| int(11) | binary| | PRI | 0 | | | first | char(3) | latin1_swedish_ci | YES | | NULL| | | second | char(3) | latin1_swedish_ci | YES | | NULL| | | third | char(3) | latin1_swedish_ci | YES | | NULL| | ++-+---+--+-+-+---+ 4 rows in set (0.00 sec) Note that the first, second, and third columns have 3 characters of space. When I do: printf(insert: %s\n, insert); int result = mysql_query(one, insert); printf(info: %s\n, mysql_info(one)); I get: insert: INSERT INTO testtable (_PK,first,second,third) VALUES (1,'AAAXXX','BBB','CCC') info: (null) insert: INSERT INTO testtable (_PK,first,second,third) VALUES (2,'DDD','EEE','FFF') info: (null) insert: INSERT INTO testtable (_PK,first,second,third) VALUES (3,'GGG','HHHXXX','III') info: (null) Note that the value in the first insert, 'AAAXXX', is too long to fit. As is 'HHHXXX' in the third insert. And indeed, I see: mysql select * from testtable; +-+---++---+ | _PK | first | second | third | +-+---++---+ | 1 | AAA | BBB| CCC | | 2 | DDD | EEE| FFF | | 3 | GGG | HHH| III | +-+---++---+ 3 rows in set (0.00 sec) So, why is mysql_info not giving me any information about the data loss that is going on here? Is that not information that might be of interest? Is there some other call I have to make that will prepare for the mysql_info call? The doc does not seem to indicate this, but one never knows. thanx - ray -- 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: Allowing remote login
Mary,
I guess, depending on your perspective, that is the case. MySQL
identifies users by their user name and their originating host. For
example: [EMAIL PROTECTED] is different than [EMAIL PROTECTED]. Its
likely you have 2-entries in your mysql.user table for root: localhost
and [workstation].[domain] (where workstation is the workstation's name
and domain is the name of the domain that its in - it might be suffixed
with .com or .net).
As a side note, you should never share your root account. At best, give
them all privileges for the database(s) they need.
Try something like this:
GRANT ALL TO DB_NAME.* ON [EMAIL PROTECTED];
UPDATE mysql.user SET password=PASSWORD('temp') WHERE user =
'temp_user');
FLUSH PRIVILEGES;
Then see if they can access that.
Regards,
A$
On Saturday, August 2, 2003, at 11:55 AM, Mary Grace wrote:
I have been using MySQL for awhile, and for the first time someone
from outside our portable /16 needs access to some tables. We have
given them the server IP address, the dB name, the port 3306, the root
username, and the password, but they still can't get access. Of
course, no firewalls or other things in the way.
Is it true that MySQL defaults to local access only, and to enable
remote access you must do something unusual with grants? If so, how
would I do this? (warning, we use the win version, but this question
is not a windows-only thing so it is topical for this list:-)
Thanks in advance for reading a post like this with such a dumb
question!
Mary Grace
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie question for MySQL
Its doesn't look like you are starting the process as root. Turn into root then start the process. Then exit turn back into you're user account and use mysql. Regards, A$ - Original Message - From: Jbo [EMAIL PROTECTED] Date: Thursday, July 31, 2003 10:26 pm Subject: Newbie question for MySQL I installed MySQL 4.0.4-beta from a cdrom that came with the book Teach yourself PHP, MySQL and Apache (a SAMs publication) on a linux 7.2 OS. I installed the binary distribution and attempted to follow the directions from the included manual. When I execute the command: .bin/safe_mysqld --user=mysql I see displayed: starting mysqld daemon with databases from /usr/local/mysql/data mysqld ended then I hit a carriage return and see [1]+ donebin/safe_mysqld --user=mysql And I grep for the process mysql using ps -ef and it is not there. I found a file called $hostname.err and it said Can't find messagefile 'mysql-4.0.4-beta-pc-linux-gnu- i686/share/mysql/englishy/errmsg.sys'. Does this mean I don't have some environment variables set up correctly? So how do I start debugging this problem? thanks Joel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: standardized naming system ?
Jim, Great question! I use the ol'Reddick VBA naming conventions. tbl - table idx - index fld - field You can search them in google, but I'd like to know if MySQL has its own established conventions too. Regards, A$ - Original Message - From: Jim Smith [EMAIL PROTECTED] Date: Friday, August 1, 2003 10:03 am Subject: RE: standardized naming system ? Hello list, is there a common naming system for db objects ? Thousands. Like: 1) Tables: mytable, tblmytable, tbl_mytable 2) Indices: idx_anindex 3) Columns: int_somenumber, date_lastupdate 4) id for the numerical primary key e.g. table customers.id and then for referencing foreign keys table addresses : addresses.customer_id or addresses.customer_fk OK, I know I could name them the way I want but perhaps there is some kind of common sense in this regard ? Common sense will do, but here is my take on it. There are three main objectives - portability, maintainability and consistency. General. Use long names. Don't abbreviate unnecessarily, but don't go to far that you have to rename all your tables if you move to a different DBMS. A max of 30 chars should fit most DBMSs. Use lower case names, with words separated by underscores '_'. Some DBMSs are case sensitive, others aren't and some convert all names to upper case ( this is an ANSI standard feature, I believe). If you use camel case ( studentClassScores),this could become STUDENTCLASSSCORES which isn't very readable, whereas STUDENT_CLASS_SCORESis much better. Don't use reserved words. Most DBMSs allow you to use reserved words with various degrees of effort, but why bother. Also try to avoid simple names which might be a reserved word in another DBMS. Tables. Give tables a clear simple name which represents the content. If it holds student records, call it students; course details, courses etc. Also assign each table a unique 2-4 letter prefix for use in naming objects which belong to that table. Columns Again say what it is. I use the prefix referred to above in all columnnames, but some people think that is a waste of name space. e.g std_id, std_surname, std_forename, student_birthdate, crs_name, crs_tutor_id, etc Constraints Use the prefix Primary Key std_pk Foreign Keys std_fk_col ( i.e. source_prefix_FK_target_prefix Unique Keys std_uk_nnwhere nn is a sequence number. Some people like std_uk_column_name, but if you have a composite key, that doesn't work. Indexes Where an index is used to enforce (or instead of) a unique or primary key constraint, same name as the constraint. Primary Key index std_pk Unique Index std_uk_nn Non-unique indexstd_nu_nn If indexes share the same namespace as constraints, stick an i_ on the front of the index name. -- 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: RE: RE: standardized naming system ?
Jim, Habit. I look at so many objects I don't know what I would do if they weren't prefixed with a type. I still didn't answer your question on why should you use them, but this is why I do. Regards, A$ - Original Message - From: Jim Smith [EMAIL PROTECTED] Date: Friday, August 1, 2003 10:39 am Subject: RE: RE: standardized naming system ? Wasn't my question. Why do you need to label a table as a table? There is some merit in using type prefixes in VB because of its loose typing, but it makes no sense in a database context where the types of objects are quite distinct. -Original Message- From: Adam Fortuno KOVICK [EMAIL PROTECTED] Sent: 01 August 2003 15:24 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: RE: standardized naming system ? Jim, Great question! I use the ol'Reddick VBA naming conventions. tbl - table idx - index fld - field You can search them in google, but I'd like to know if MySQL has its own established conventions too. Regards, A$ - Original Message - From: Jim Smith [EMAIL PROTECTED] Date: Friday, August 1, 2003 10:03 am Subject: RE: standardized naming system ? Hello list, is there a common naming system for db objects ? Thousands. Like: 1) Tables: mytable, tblmytable, tbl_mytable 2) Indices: idx_anindex 3) Columns: int_somenumber, date_lastupdate 4) id for the numerical primary key e.g. table customers.id and then for referencing foreign keys table addresses : addresses.customer_id or addresses.customer_fk OK, I know I could name them the way I want but perhaps there is some kind of common sense in this regard ? Common sense will do, but here is my take on it. There are three main objectives - portability, maintainability and consistency. General. Use long names. Don't abbreviate unnecessarily, but don't go to far that you have to rename all your tables if you move to a different DBMS. A max of 30 chars should fit most DBMSs. Use lower case names, with words separated by underscores '_'. Some DBMSs are case sensitive, others aren't and some convert all names to upper case ( this is an ANSI standard feature, I believe). If you use camel case ( studentClassScores),this could become STUDENTCLASSSCORES which isn't very readable, whereas STUDENT_CLASS_SCORESis much better. Don't use reserved words. Most DBMSs allow you to use reserved words with various degrees of effort, but why bother. Also try to avoid simple names which might be a reserved word in another DBMS. Tables. Give tables a clear simple name which represents the content. If it holds student records, call it students; course details, courses etc. Also assign each table a unique 2-4 letter prefix for use in naming objects which belong to that table. Columns Again say what it is. I use the prefix referred to above in all columnnames, but some people think that is a waste of name space. e.g std_id, std_surname, std_forename, student_birthdate, crs_name, crs_tutor_id, etc Constraints Use the prefix Primary Key std_pk Foreign Keys std_fk_col ( i.e. source_prefix_FK_target_prefix Unique Keys std_uk_nnwhere nn is a sequence number. Some people like std_uk_column_name, but if you have a composite key, that doesn't work. Indexes Where an index is used to enforce (or instead of) a unique or primary key constraint, same name as the constraint. Primary Key index std_pk Unique Index std_uk_nn Non-unique indexstd_nu_nn If indexes share the same namespace as constraints, stick an i_ on the front of the index name. -- 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: Newbie question...memo field
Normally I'd say do a select...into...from, but I don't think MySQL supports that. In lew of that, try this: (1) Create the new table. CREATE TABLE tbl_table_b ( record SMALLINT NULL, id SMALLINT NULL, color VARCHAR(10) ); (2) Insert the values from the source table to the destination. INSERT INTO tbl_b SELECT record, id, color FROM tbl_a; (3) Update the values you want changed. UPDATE tbl_b SET record = 2 id = 001 WHERE color = 'Pink'; Continue for each additional row to be updated Not sure if this is what you were after. Regards, A$ - Original Message - From: Brent Elison [EMAIL PROTECTED] Date: Friday, August 1, 2003 11:19 am Subject: Newbie question...memo field Hi all, I'm fairly new to MySQL and have the following scenario: The Table-A below was converted from a Filemaker Pro database. The color field was a value list in the FMP database that converted over as a text field. The color field contains all the selected values in the value list (from the FMP database) and looks like the data below (Table-A) when viewed in the BLOB-editor (MySQL-Front). So, how do I extract the color valuesfrom the color field in Table-A and put them into a separate table looking like Table-B? Sorry for the totally newbie question. Thanks for any suggestion. Brent Table-A RecordID Color 1 001 Black Pink White 2 002 Black 3 003 Green 4 AAA Black White Table-B RecordID Color 1 001 Black 2 001 Pink 3 001 White 4 002 Black 5 003 Green 6 AAA Black 7 AAA White - -- 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: RE: Newbie question...memo field
Brent, Maybe but give us more information. I know you moving data from one table into a new one, but not much else. Whats the logic to the changes you're making? A$ - Original Message - From: Brent Elison [EMAIL PROTECTED] Date: Friday, August 1, 2003 3:25 pm Subject: RE: Newbie question...memo field I appreciate the suggestion. However, this solution will require me to run step (3) for each record I want updated into Table-B. That would be 1000+ times. Any other way to get this done without having to run all those updates? Thanks, Brent -Original Message- From: Adam Fortuno KOVICK [EMAIL PROTECTED] Sent: Friday, August 01, 2003 11:31 AM To: [EMAIL PROTECTED] Cc: MySQL List Subject: Re: Newbie question...memo field Normally I'd say do a select...into...from, but I don't think MySQL supports that. In lew of that, try this: (1) Create the new table. CREATE TABLE tbl_table_b ( record SMALLINT NULL, id SMALLINT NULL, color VARCHAR(10) ); (2) Insert the values from the source table to the destination. INSERT INTO tbl_b SELECT record, id, color FROM tbl_a; (3) Update the values you want changed. UPDATE tbl_b SET record = 2 id = 001 WHERE color = 'Pink'; Continue for each additional row to be updated Not sure if this is what you were after. Regards, A$ - Original Message - From: Brent Elison [EMAIL PROTECTED] Date: Friday, August 1, 2003 11:19 am Subject: Newbie question...memo field Hi all, I'm fairly new to MySQL and have the following scenario: The Table-A below was converted from a Filemaker Pro database. The color field was a value list in the FMP database that converted over as a text field. The color field contains all the selected values in the value list (from the FMP database) and looks like the data below (Table-A) when viewed in the BLOB-editor (MySQL-Front). So, how do I extract the color valuesfrom the color field in Table-A and put them into a separate table looking like Table-B? Sorry for the totally newbie question. Thanks for any suggestion. Brent Table-A Record ID Color 1 001 Black Pink White 2 002 Black 3 003 Green 4 AAA Black White Table-B Record ID Color 1 001 Black 2 001 Pink 3 001 White 4 002 Black 5 003 Green 6 AAA Black 7 AAA White - -- 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]
ARGGH! - User password problems
All,
I've been attempting to assign a password to a user with the following
statement:
UPDATE mysql.user SET password=PASSWORD('foo') WHERE user = 'acc_name';
I get the typical:
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
However, when the user attempts the login using the password they are
unable (access is denied to acc_name with password yes). If they
attempt to login without a password, they are successful - WTF!.
There is only 1-entry in the user table for this user so I didn't
specify a host.
Next, for the same user account, if I try to login from a remote
machine (via ODBC) I am denied access (event with the blank password!).
The user has the following host '%' (no other hosts present), yet I
receive an error that reads:
Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)
HELP before I lose my mind and start pretending I am a glass of orange
juice.
Regards,
Adam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
