Join vs. Where (help)
This: SELECT * FROM machines INNER JOIN people ON machines.machinesID=people.peopleID; Is not producing the same results as this: SELECT lastname, model FROM people, machines WHERE machines.peopleID = people.peopleID; Can someone please tell why, what's wrong? (What happens is that the wrong person are listed with the wrong machine -using the INNER JOIN shown above, the second statement I listed works as expected, right person with right machine.) Thanks in Advance, Ted Rogers
Initializing primary key values for existing table
I needed to add a new, autoincrementing, primary key column to a table and have been struggling to figure out how to assign an initial, unique value to each row. I finally accomplished my task, but feel sure there's an easier way. Here is my solution: 1. Add the column: alter table mytable add mycolumn int auto_increment; 2. Set up a user variable: @mycounter = 0; 3. Assign the initial values by incrementing the counter: Update mytable set mycolumn = max((@mycounter := @mycounter + 1), @mycounter); 4. Finally, set the column to be the primary key: alter table mytable set primary key mycolumn; This seems like a roundabout way of doing things. Can any of you improve on it? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help) -(I got it!)
Ok, should be this: SELECT * FROM machines INNER JOIN people ON machines.peopleID=people.peopleID; Ted This: SELECT * FROM machines INNER JOIN people ON machines.machinesID=people.peopleID; Is not producing the same results as this: SELECT lastname, model FROM people, machines WHERE machines.peopleID = people.peopleID; Can someone please tell why, what's wrong? (What happens is that the wrong person are listed with the wrong machine -using the INNER JOIN shown above, the second statement I listed works as expected, right person with right machine.) Thanks in Advance, Ted Rogers
Problem setting/activating password
Hi, I am created a user by using: GRANT ALL ON *.* TO username@% IDENTIFIED BY password I did this from the mysql command line tool, logged in as root. The command succeeds and the new user is created in the mysql.user table, but the password does not work. I can only use this new user if I use a empty string password (no password). I also tried to do the SET PASSWORD afterwords, no luck. I then tried the Windows GUI / Administration to grant access and then set the password, but again, only an empty string password is accepted. I am using mysql 4.0.13. How do I actually get the password validation to be activated and instruct the server that the new user must provide his password? __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help)
Erm, the first sql as i can see doesn't connect on the same field as the second one that produces the correct result. George Christoforakis - Original Message - From: [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 9:01 AM Subject: Join vs. Where (help) This: SELECT * FROM machines INNER JOIN people ON machines.machinesID=people.peopleID; Is not producing the same results as this: SELECT lastname, model FROM people, machines WHERE machines.peopleID = people.peopleID; Can someone please tell why, what's wrong? (What happens is that the wrong person are listed with the wrong machine -using the INNER JOIN shown above, the second statement I listed works as expected, right person with right machine.) Thanks in Advance, Ted Rogers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help)
2003 6 25 15:[EMAIL PROTECTED] : This: SELECT * FROM machines INNER JOIN people ON machines.machinesID=people.peopleID; Inludes NULL records Is not producing the same results as this: SELECT lastname, model FROM people, machines WHERE machines.peopleID = people.peopleID; Dosent include NULL records Can someone please tell why, what's wrong? Nothing is wrong thats the way it is supposed to be. (What happens is that the wrong person are listed with the wrong machine -using the INNER JOIN shown above, the second statement I listed works as expected, right person with right machine.) Thanks in Advance, Ted Rogers This is a good example which is also described on page 202-203 Michael Kofler Mysql (Edition1 ) Best regards -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help)
Hi tlr7425, Besides what I said in my last e-mail I completely overlooked that SELECT * FROM machines INNER JOIN people ON machines.machinesID=people.peopleID; should be SELECT * FROM machines INNER JOIN people ON machines.peopleID=people.peopleID; Even though my first statement should still remain correct. The statement above does not return the NULL record while the other one does INNER JOIN) Best regards Nils Valentin Tokyo/Japan 2003 6 25 15:37Nils Valentin : 2003 6 25 15:[EMAIL PROTECTED] : This: SELECT * FROM machines INNER JOIN people ON machines.machinesID=people.peopleID; Inludes NULL records Is not producing the same results as this: SELECT lastname, model FROM people, machines WHERE machines.peopleID = people.peopleID; Dosent include NULL records Can someone please tell why, what's wrong? Nothing is wrong thats the way it is supposed to be. (What happens is that the wrong person are listed with the wrong machine -using the INNER JOIN shown above, the second statement I listed works as expected, right person with right machine.) Thanks in Advance, Ted Rogers This is a good example which is also described on page 202-203 Michael Kofler Mysql (Edition1 ) Best regards -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting default auto_increment start int
Hi All, Just a quick question: How do I set the auto increment for a field to start from an integer other than 1. For example, I would like to be able to have the auto increment counter start from 1000. Thanks, Javan. Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB file...
When should I use the autoextend option for the InnoDB file...Even after emptying the tables the files keeps its size and for new insertions it gets even bigger instead of using the already allocated space(which I suppose should be available after emptyibg the tables). thank you friends aik_b p.s. MySQL doesn't seem to do a good usage of the disk space and the InnoDB file size and its density..anyway
Subselect functionality
Hi MySQL Fans ;-), I have a question regarding the MySQL feaures. From Version 4.1 Full subselect support was/is announced. However if I understood correctly then already from Version3.23-41 (or earlier) there are some subselect functions already included. For xample if I try this: CREATE TABLE tblname_new SELECT * FROM tblname_ori; then it works. Or do I have a misunderstanding of subselects (nested queries) ?? Best regards -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem setting/activating password
I don't know if this apply when using GRANT but have you tried afterwards: flush privileges; HTH, Lian -Original Message- From: Riaan Oberholzer [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 9:19 AM To: [EMAIL PROTECTED] Subject: Problem setting/activating password Hi, I am created a user by using: GRANT ALL ON *.* TO username@% IDENTIFIED BY password I did this from the mysql command line tool, logged in as root. The command succeeds and the new user is created in the mysql.user table, but the password does not work. I can only use this new user if I use a empty string password (no password). I also tried to do the SET PASSWORD afterwords, no luck. I then tried the Windows GUI / Administration to grant access and then set the password, but again, only an empty string password is accepted. I am using mysql 4.0.13. How do I actually get the password validation to be activated and instruct the server that the new user must provide his password? __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone had a chance to try an Opteron yet?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 24 Jun 2003, David Griffiths wrote: I'm surprised there is not more interest in this; is it that not many work with large-ish (10+ gig) databases that need high-end performance? Many of our customers do. A 64-bit CPU won't have the 4-gig memory limit that a 32-bit processor will; even worse, Linux is apparently limited to about a 2-gig process. It depends - there is a BIGMEM patch from Andrea Arcangeli that raised that limit to 3.5 GB on 32bit systems. I think the patch is in the mainline kernel as well by now. SuSe Enterprise Linux supports 512-gigabyte processes with 16 processors. Imagine 10 gigabyte database all in memory. Even better, larger file sizes - no more 2-gig files. Max file size is 9-Exabytes ( 9 followed by 18 0's ). All the posts I see about people trying to get around the 2 gig file limit should be really excitied. Actually, you can create larger files on 32bit Linux systems as well. It's just that the file system and the C library must have support for LFS (Large File Support): http://www.suse.de/~aj/linux_lfs.html I guess I'm just surprised by lack of interest. I've been bugging our CTO once a week about this, and hopefully should have a server on my desk by mid summer to late fall. Have fun with it! If you're interested, SuSe has a good PDF on AMD64 and SuSe Enterprise Linux 1.0: http://www.suse.com/en/business/products/server/sles/misc/sles8_amd64.pdf Anyone have some practical experience with the software and hardware? Yes, we do have two AMD64 systems (one dual Opteron with 1GB of RAM), running SLES here. Quite impressive. We also have some Itanium (1/2) systems and SGI kindly gave us access to an Altix system (16 x 900MHz Intel Itanium-2, 32GB of RAM). So MySQL runs quite happily on 64bit systems. The main benefit of a 64 bit platform is that MySQL can handle more concurrent threads and that you can give more than 4GB of memory to MySQL buffers. The disadvantage is that MySQL will be about 3-5 % slower because of the extra memory usage 64 bit pointers require (Structures are aligned to be 64 bit aligned, which makes the structures bigger and reduces the CPU cache usage, more stack space is used for registers). We tested this on Sun Solaris, using the Sun Forte compilers, but I assume this applies to other architectures as well. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE++VFtSVDhKrJykfIRAsLHAJ4nx0SfyJxBhtMQW+nodnjXNArSdQCeKxL8 G6ScGyAcT8tN4mI++T1K7j4= =7ti/ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dumping data
Hi all, Is there any way to do a mysql dump in which each table is dumped into a separate file. I know I can use the --tables option to specify a table, but this means I have to type out each table name (and there are a lot of them). Is there any way to get mysql to automatically iterate through all the table names and dump each table to a separate file. The reason for this is that the db is BIG and we don't want to have to lug 200+mb files around. Plus mysql seems to have a real issue with dumping large dbs into one file. We've tried about 5 - 10 times and we keep getting corrupted data in the file. Thanks --- Rob ** Rob Cherry mailto:[EMAIL PROTECTED] +27 21 447 7440 Jam Warehouse RSA Smart Business Innovation http://www.jamwarehouse.com ** All information contained in this email is confidential and may be used by the intended recipient only. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB file...
I.-A., - Original Message - From: I-A.Kotopoulos [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, June 25, 2003 10:05 AM Subject: InnoDB file... --=_NextPart_000_00B5_01C33B01.642AD2C0 Content-Type: text/plain; charset=Windows-1252 Content-Transfer-Encoding: quoted-printable When should I use the autoextend option for the InnoDB file...Even after = emptying the tables the files keeps its size and for new insertions it = gets even bigger instead of using the already allocated space(which I = suppose should be available after emptyibg the tables). yes, it should free the space if you empty the table. The size of ibdata1 will stay the same, but other tables can use the freed space. Are you sure you do not have long-running transactions dangling? Use SHOW INNODB STATUS\G to print a list of transactions. The purge operation cannot remove delete-marked rows if there are old transactions which could still see them. thank you friends aik_b p.s. MySQL doesn't seem to do a good usage of the disk space and the = InnoDB file size and its density..anyway Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM
Hi all, I have a question about MyISAM, during an UPDATE for a row, is it a row locking or a table locking? -- Cedric Gavage [EMAIL PROTECTED] http://unixtech.be - http://gavage.com - OpenPGP: 0xED325C64 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help)
What do you mean when you say, in this case, "does not return the NULL (Brecord"? (B (BI have no records that are completely "NULL" though some records do (Bhave a NULL field or two? (B (BThanks, (BTed (B (B (BOn Wednesday, June 25, 2003, at 02:42 AM, Nils Valentin wrote: (B (B Hi tlr7425, (B (B Besides what I said in my last e-mail I completely overlooked that (B (B SELECT * FROM machines INNER JOIN people ON (B machines.machinesID=people.peopleID; (B (B should be (B (B SELECT * FROM machines INNER JOIN people ON (B machines.peopleID=people.peopleID; (B (B Even though my first statement should still remain correct. The (B statement (B above does not return the NULL record while the other one does INNER (B JOIN) (B (B Best regards (B (B Nils Valentin (B Tokyo/Japan (B (B (B 2003$BG/(B 6$B7n(B 25$BF|(B $B?eMKF|(B 15:37$B!"(BNils Valentin $B$5$s$O=q$-$^$7$?(B: (B 2003$BG/(B 6$B7n(B 25$BF|(B $B?eMKF|(B 15:01$B!"([EMAIL PROTECTED] $B$5$s$O=q$-$^$7$?(B: (B This: (B (B SELECT * FROM machines INNER JOIN people ON (B machines.machinesID=people.peopleID; (B (B Inludes NULL records (B (B Is not producing the same results as this: (B (B SELECT lastname, model FROM people, machines WHERE machines.peopleID (B = (B people.peopleID; (B (B Dosent include NULL records (B (B Can someone please tell why, what's wrong? (B (B Nothing is wrong thats the way it is supposed to be. (B (B (What happens is that the wrong person are listed with the wrong (B machine -using the INNER JOIN shown above, the second statement I (B listed works as expected, right person with right machine.) (B (B Thanks in Advance, (B Ted Rogers (B (B This is a good example which is also described on page 202-203 Michael (B Kofler Mysql (Edition1 ) (B (B Best regards
Large file : InnoDB or MyISAM
Hi to all, I will have to do with a very big file (approx 600 millions of records). Which is the best table handler for this king of table : InnoDB or MyISAM (many INSERT and MANY SELECT, no UPDATE - statistics file). Best regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: about desc command
On 25-Jun-2003 MaFai wrote: Hello, mysql, After create a table,it can use desc tablename to describe the table scheme. But it can not demonstrate that the column is unique or not.It just simplely show the column is MUL type. How can I get more information from the table by using mysql commend?(Except use mysqldump to read the sql file) or How can I type command to show the table structure? mysqltype some command mysqlcreate tabe //Show the specifing created table sql information DESC tbl; SHOW INDEX FROM tbl; SHOW CREATE TABLE tbl; Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting Countries
On 25-Jun-2003 Ralph wrote: I am getting a list of all countries from database, and then I am sorting by country name. However since most orders will be from US I want the US to appear first over the rest of the countries. How can I go about doing this? Currently, this is my query: SELECT countries_id, countries_name FROM TABLE_COUNTRIES ORDER BY countries_name; SELECT IF(countries_id='US', 0, 1) as ctsort, countries_id, countries_name FROM TABLE_COUNTRIES ORDER BY ctsort, countries_name Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Conection to MySQL
Installed MySQL v4.0.13 on RH8, with phpMyAdmin-2.5.1 and PHP v4. The phpMyadmin is installed on another server (gateway) while MySQL and PHP is installed on a separate server. When the programmer tries to access MySQL he gets the following message: Error 2003 Cannot connect to MySQL server 203.153.229.84 (10061). Anyone know why this is happening. My my.cnf file has the following: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock port=3308 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid I have the phpMyAdmin port set to 3308. Thanks Jon L. Miller, MCNE, CNS Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au I don't know the key to success, but the key to failure is trying to please everybody. -Bill Cosby -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in last CVS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 24 Jun 2003, Dyego Souza do Carmo wrote: try to bk pull the mysql 4.0 from CVS tree... after I run: aclocal; autoheader; autoconf; automake inside dir... the error is: autoconf: Undefined macros: configure.in:8:AC_CONFIG_HEADERS(config.h) configure.in: 8: `automake requires `AM_CONFIG_HEADER', not `AC_CONFIG_HEADER' What versions of autoconf/automake do you use? Seems like this new construct requires newer version of the toolchain. As a simple workaround, simply replace AC_CONFIG_HEADERS with AM_CONFIG_HEADER again. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE++WNrSVDhKrJykfIRArF+AJ0RyevuTDhPfzzB23Y1WgFBSTBzlACeN5Yo mZkRI0nUBFEXDsbhO1I2h74= =u3Hu -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime column dummy question
# [EMAIL PROTECTED] / 2003-06-25 12:05:49 +0800: //Wrong sql statement alter table p_asset add mydate datetime default now(); alter table p_asset add mydate datetime default time(); alter table p_asset add mydate datetime default now; alter table p_asset add mydate datetime default time; alter table p_asset add mydate datetime default date(); alter table p_asset add mydate datetime default datetime(); How can I add the default now value into the specified column? I try to find in the mysql manual,but in the default value charter,no relative information can be found. There *is* relevant information in the manual: http://www.mysql.com/doc/en/CREATE_TABLE.html: A DEFAULT value has to be a constant, it cannot be a function or an expression. http://www.mysql.com/doc/en/DATETIME.html: The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: * The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. * The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) * You explicitly set the TIMESTAMP column to NULL. -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime column dummy question
alter table p_asset add mydate datetime default 'now()'; - you need the '' around now(); apparently http://www.mysql.com/doc/en/DATETIME.html and scroll down to the comment made by Lazy Soul on Tuesday May 27 2003, @8:15am Jan Janice Wright Ingenta plc [EMAIL PROTECTED] http://www.ingentaselect.com/ Sometime recently MaFai said: Hello, mysql, A table contains a column named mydate. //Wrong sql statement alter table p_asset add mydate datetime default now(); alter table p_asset add mydate datetime default time(); alter table p_asset add mydate datetime default now; alter table p_asset add mydate datetime default time; alter table p_asset add mydate datetime default date(); alter table p_asset add mydate datetime default datetime(); How can I add the default now value into the specified column? I try to find in the mysql manual,but in the default value charter,no relative information can be found. I also know this question is stupid,but hope you help. Best regards. MaFai [EMAIL PROTECTED] 2003-06-25 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Janice Wright Ingenta plc [EMAIL PROTECTED] Tel: +44 (0) 01865 799114 http://www.ingentaselect.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SCO UnixWare porting of MYSQL
Hi ! Just wanted to know if there is any installation procedure available on = UnixWare 7.1.x. There is patch available for SCO UnixWare libz.so, which = I have downloaded but Iam not able to initialize ./Configure script. = Any help/support will be appreciated. Thanks In Advance Regards Sudhipan - Sudhipan Sharma SCO Group - formerly Caldera International. 56, Janpath New Delhi - 110 001 INDIA. Ph: 91-11-23736466, Fax : +91-11-23359997 Web :http://www.sco.com
Re: default value does not set using LOAD DATA INFILE ...
# [EMAIL PROTECTED] / 2003-06-25 15:18:09 +1000: From: Roman Neuhauser [EMAIL PROTECTED] please don't piggyback on other people's threads. you can start your own for free. Although it was the first time for me to send the post on this list, I was sure that I did not follow other people's threads. I checked my original post again after read your response, and I did start with a new thread. However, I apologize if somehow I did make any mistake. What I was referring to was the fact that you posted to the list by hitting the reply button (or whatever technic you use) to an unrelated post, replaced the subject, and sent it to the list. doing it this way leaves certain headers in you post that make it look like it's a reply to the message you abused this way, which screws threading in mail clients that can do such thing. # [EMAIL PROTECTED] / 2003-06-24 09:46:24 +1000: I am using LOAD DATA INFILE to import some data to MySQL. I would like to leave some fields blank and use the default value set in the tables. I have spent many hours trying to find the problem, but no luck so far. According to the manual, it should work. I am hoping some one on this list would help me. The following is sql query string I used: LOAD DATA LOCAL INFILE \' file \' REPLACE INTO TABLE table FIELDS TERMINATED BY \'terminator\' ENCLOSED BY \'\\' LOAD DATA LOCAL INFILE 'file' REPLACE INTO TABLE table FIELDS TERMINATED BY 'terminator' ENCLOSED BY '' (col1, col3, col5) I tried every thing I could think following the manual for LOAD DATA before I joined this list. I probably did not explain my problem clearly in my initial post. What I try to do is to import a csv data file into MySQL using LOAD DATA INFILE I would like to leave some fields blank in the csv file, and let MySQL to set default values to those fields in the database when loading the data. But every thing goes fine except the default values do not set. You made yourself quite clear in your original post. Does what I suggested not do what you want? Send in: * output of the DESCRIBE command for your table * the actual LOAD DATA command you used * SELECT * after LOADing a few lines of sample data into the table (just the imported lines) -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help)
SELECT * FROM machines INNER JOIN people ON machines.peopleID=people.peopleID; Inludes NULL records I meant the first statement returns also empty record fields or should I say incomplete data records ? Is not producing the same results as this: SELECT lastname, model FROM people, machines WHERE machines.peopleID = people.peopleID; Dosent include NULL records This one doesnt return any entries with incomplete data records (no data in them). So if you have an entry for machines.peopleID but not for people.peopeID than it wont show up while it does in the first statement. Does it make it a bit clearer ? Sorry dont know how to better explain it really. Best regards Nils Valentin Tokyo/Japan 2003 6 25 17:[EMAIL PROTECTED] : What do you mean when you say, in this case, does not return the NULL record? I have no records that are completely NULL though some records do have a NULL field or two? Thanks, Ted On Wednesday, June 25, 2003, at 02:42 AM, Nils Valentin wrote: Hi tlr7425, Besides what I said in my last e-mail I completely overlooked that SELECT * FROM machines INNER JOIN people ON machines.machinesID=people.peopleID; should be SELECT * FROM machines INNER JOIN people ON machines.peopleID=people.peopleID; Even though my first statement should still remain correct. The statement above does not return the NULL record while the other one does INNER JOIN) Best regards Nils Valentin Tokyo/Japan 2003 6 25 15:37Nils Valentin : 2003 6 25 15:[EMAIL PROTECTED] : This: SELECT * FROM machines INNER JOIN people ON machines.machinesID=people.peopleID; Inludes NULL records Is not producing the same results as this: SELECT lastname, model FROM people, machines WHERE machines.peopleID = people.peopleID; Dosent include NULL records Can someone please tell why, what's wrong? Nothing is wrong thats the way it is supposed to be. (What happens is that the wrong person are listed with the wrong machine -using the INNER JOIN shown above, the second statement I listed works as expected, right person with right machine.) Thanks in Advance, Ted Rogers This is a good example which is also described on page 202-203 Michael Kofler Mysql (Edition1 ) Best regards -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sorting Countries
insert one space before US ;) in recieving do trim if not HTML output -Original Message- From: Ralph [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 7:32 AM To: mySQL Mailing List Subject: Sorting Countries I am getting a list of all countries from database, and then I am sorting by country name. However since most orders will be from US I want the US to appear first over the rest of the countries. How can I go about doing this? Currently, this is my query: SELECT countries_id, countries_name FROM TABLE_COUNTRIES ORDER BY countries_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: AES 256-Bit Encryption and /etc/my.cnf
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 24 Jun 2003, Daevid Vincent wrote: It would be really nice if ANY of these kinds of configuration options were settable via the /etc/my.cnf file. I use RPM's almost exclusively for their ease of maintenance, compatibility and stability and I certainly wouldn't want to go and have to re-compile anything just to change a value from 128 to 256. As a feature request/suggestion on behalf of every other RPM (or package such as .deb or .mdk or whatever) user, I implore you guys at mysql.com to consider this going forward. Thanks for your suggestion, which I will forward to the developer in charge of that part of the code. However, I am not sure if we can fulfil this request. Enabling 256 bit encryption by default may pose legal problems (export regulations), but IANAL. We shall see. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD4DBQE++XVLSVDhKrJykfIRAltdAJjFMt9d98O0nvALrujhDQiP6D7IAJ9h/1VS jr/u+yS9CYe1ZOIyvVd6dg== =H0PG -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime column dummy question
# [EMAIL PROTECTED] / 2003-06-25 06:18:04 +0100: Sometime recently MaFai said: A table contains a column named mydate. //Wrong sql statement alter table p_asset add mydate datetime default now(); alter table p_asset add mydate datetime default time(); alter table p_asset add mydate datetime default now; alter table p_asset add mydate datetime default time; alter table p_asset add mydate datetime default date(); alter table p_asset add mydate datetime default datetime(); How can I add the default now value into the specified column? I try to find in the mysql manual,but in the default value charter,no relative information can be found. I also know this question is stupid,but hope you help. alter table p_asset add mydate datetime default 'now()'; - you need the '' around now(); apparently http://www.mysql.com/doc/en/DATETIME.html and scroll down to the comment made by Lazy Soul on Tuesday May 27 2003, @8:15am I'd like to know in which version of MySQL this does what it's intended to do. Definitely not in 4.0.13: mysql create table autotime (foo int, bar datetime default 'now()'); Query OK, 0 rows affected (0.00 sec) mysql insert autotime set foo = 1; Query OK, 1 row affected (0.06 sec) mysql insert autotime set foo = 2; Query OK, 1 row affected (0.00 sec) mysql select * from autotime; +--+-+ | foo | bar | +--+-+ |1 | -00-00 00:00:00 | |2 | -00-00 00:00:00 | +--+-+ 2 rows in set (0.02 sec) mysql select version(); ++ | version() | ++ | 4.0.13-log | ++ 1 row in set (0.00 sec) -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime column dummy question
You're right, just tested it myself on 3.23.41; and I get exactly the same result as you. We should post an amendment to the docs on the website. However, (still with 3.23.41): mysql create table autotime2 (foo int, bar timestamp default now()); ERROR 1064: You have an error in your SQL syntax near 'now())' at line 1 mysql create table autotime2 (foo int, bar timestamp default 'now()'); Query OK, 0 rows affected (0.00 sec) mysql insert autotime2 set foo = 2; Query OK, 1 row affected (0.00 sec) mysql insert autotime2 set foo = 1; Query OK, 1 row affected (0.00 sec) mysql select * from autotime2; +--++ | foo | bar| +--++ |2 | 20030625102215 | |1 | 2003062510 | +--++ 2 rows in set (0.00 sec) Cheers, Jan Sometime recently Roman Neuhauser said: # [EMAIL PROTECTED] / 2003-06-25 06:18:04 +0100: Sometime recently MaFai said: A table contains a column named mydate. //Wrong sql statement alter table p_asset add mydate datetime default now(); alter table p_asset add mydate datetime default time(); alter table p_asset add mydate datetime default now; alter table p_asset add mydate datetime default time; alter table p_asset add mydate datetime default date(); alter table p_asset add mydate datetime default datetime(); How can I add the default now value into the specified column? I try to find in the mysql manual,but in the default value charter,no relative information can be found. I also know this question is stupid,but hope you help. alter table p_asset add mydate datetime default 'now()'; - you need the '' around now(); apparently http://www.mysql.com/doc/en/DATETIME.html and scroll down to the comment made by Lazy Soul on Tuesday May 27 2003, @8:15am I'd like to know in which version of MySQL this does what it's intended to do. Definitely not in 4.0.13: mysql create table autotime (foo int, bar datetime default 'now()'); Query OK, 0 rows affected (0.00 sec) mysql insert autotime set foo = 1; Query OK, 1 row affected (0.06 sec) mysql insert autotime set foo = 2; Query OK, 1 row affected (0.00 sec) mysql select * from autotime; +--+-+ | foo | bar | +--+-+ |1 | -00-00 00:00:00 | |2 | -00-00 00:00:00 | +--+-+ 2 rows in set (0.02 sec) mysql select version(); ++ | version() | ++ | 4.0.13-log | ++ 1 row in set (0.00 sec) -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- Janice Wright Ingenta plc [EMAIL PROTECTED] Tel: +44 (0) 01865 799114 http://www.ingentaselect.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary tables - MySQL 4.1 alpha
Nils Valentin [EMAIL PROTECTED] wrote: I have some questions about temporary tables. I would appreciate any replies: I created a temporary table f.e. like this: mysql create temporary table temp SELECT * FROM relations; Query OK, 4 rows affected (2.35 sec) Records: 4 Duplicates: 0 Warnings: 0 When I do mysql show create table temp; Then it will give me this: | temp | CREATE TEMPORARY TABLE `temp` ( `member_id` int(16) NOT NULL default '0', `company_id` int(16) NOT NULL default '0', `membership_id` int(16) NOT NULL default '0' ) TYPE=MyISAM CHARSET=latin1 | Now what confuses me is that a) it says here TYPE=MyISAM Because MyISAM is default table type b) I understood that only HEAP tables are stored in the memory. Yes. I was assuming (until now) that HEAP tables are (the only one type of) temporary tables. Temporary tables may have type HEAP. I tried CREATE TEMPORARY TABLE temp TYPE= InnoDB SELECT * FROM relations; or CREATE TEMPORARY TABLE temp TYPE= HEAP SELECT * FROM relations; and they are all created as in-memory tables no files are created. Can I use any table format (ISAM,MYISAM,BDB,InnoDB,HEAP) for temporary tables You can use MyISAM, ISAM, MERGE, HEAP, InnoDB. Temporary table files are created in the temporary directory: http://www.mysql.com/doc/en/Temporary_files.html If, so what would be the difference between a temporary table in general and a HEAP table ? Temporary tables are the per-connection tables. HEAP tables are stored in memory tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_pconnect
Hi My server www has Suse 8.2, apache-1.3.27, mod_php4-4.3.1, and my server mysql has Suse 8.2, and mysql.4.0.12-Max-log Sometimes, not always, it appears an error in www. Warning: mysql_pconnect() [function.mysql-pconnect]: Link to server lost, unable to reconnect in db_mysql.inc on line 73 I have changes mysql_pconnect to mysql_connect and it seems that no error appears but cpu increases. Any solution ??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime column dummy question
# [EMAIL PROTECTED] / 2003-06-25 07:10:46 +0100: Sometime recently Roman Neuhauser said: # [EMAIL PROTECTED] / 2003-06-25 06:18:04 +0100: alter table p_asset add mydate datetime default 'now()'; - you need the '' around now(); apparently http://www.mysql.com/doc/en/DATETIME.html and scroll down to the comment made by Lazy Soul on Tuesday May 27 2003, @8:15am I'd like to know in which version of MySQL this does what it's intended to do. Definitely not in 4.0.13: mysql create table autotime (foo int, bar datetime default 'now()'); Query OK, 0 rows affected (0.00 sec) mysql insert autotime set foo = 1; Query OK, 1 row affected (0.06 sec) mysql insert autotime set foo = 2; Query OK, 1 row affected (0.00 sec) mysql select * from autotime; +--+-+ | foo | bar | +--+-+ |1 | -00-00 00:00:00 | |2 | -00-00 00:00:00 | +--+-+ 2 rows in set (0.02 sec) You're right, just tested it myself on 3.23.41; and I get exactly the same result as you. We should post an amendment to the docs on the website. However, (still with 3.23.41): mysql create table autotime2 (foo int, bar timestamp default now()); ERROR 1064: You have an error in your SQL syntax near 'now())' at line 1 mysql create table autotime2 (foo int, bar timestamp default 'now()'); Query OK, 0 rows affected (0.00 sec) mysql insert autotime2 set foo = 2; Query OK, 1 row affected (0.00 sec) mysql insert autotime2 set foo = 1; Query OK, 1 row affected (0.00 sec) mysql select * from autotime2; +--++ | foo | bar| +--++ |2 | 20030625102215 | |1 | 2003062510 | +--++ 2 rows in set (0.00 sec) mysql create table dt (foo int, bar datetime default 'now()'); Query OK, 0 rows affected (0.00 sec) mysql create table ts (foo int, bar timestamp default 'now()'); Query OK, 0 rows affected (0.02 sec) mysql desc dt; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | foo | int(11) | YES | | NULL| | | bar | datetime | YES | | -00-00 00:00:00 | | +---+--+--+-+-+---+ 2 rows in set (0.02 sec) mysql desc ts; +---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | foo | int(11) | YES | | NULL| | | bar | timestamp(14) | YES | | NULL| | +---+---+--+-+-+---+ 2 rows in set (0.00 sec) -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can you tell me how
any one can tell me how can i use the MySQL ++ function in CBuilder 6?? i include it in my project but i got linker error
Re: Setting default auto_increment start int
Javan B. [EMAIL PROTECTED] wrote: Just a quick question: How do I set the auto increment for a field to start from an integer other than 1. For example, I would like to be able to have the auto increment counter start from 1000. Use AUTO_INCREMENT option of CREATE TABLE statement: http://www.mysql.com/doc/en/CREATE_TABLE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
comp-err.exe
Compiler errormessage ver 1.3 C:\mysql\bincomp-err.exe -I comp-err.exe (Compile errormessage) Ver 1.3 This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Usage: comp-err.exe [-?] [-I] [-V] fromfile[s] tofile Options: -Info -Version Hello Programmers, for which purpose is this file included in the file C:\mysql\bin directory ? I have tried to RTFM and I did not find any entry about This specific file, perhaps it has another name under linux, I am using windows and would like to know what useful tasks Can be accomplished with comp-err.exe under windows 2000. Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Initializing primary key values for existing table
# [EMAIL PROTECTED] / 2003-06-25 02:11:05 -0400:
I needed to add a new, autoincrementing, primary key column
to a table and have been struggling to figure out how to
assign an initial, unique value to each row.
I finally accomplished my task, but feel sure there's an
easier way.
Here is my solution:
1. Add the column:
alter table mytable add mycolumn int auto_increment;
2. Set up a user variable:
@mycounter = 0;
3. Assign the initial values by incrementing the counter:
Update mytable set mycolumn = max((@mycounter := @mycounter
+ 1), @mycounter);
4. Finally, set the column to be the primary key:
alter table mytable set primary key mycolumn;
This seems like a roundabout way of doing things. Can any
of you improve on it?
doesn't simply adding the column + primary key in one ALTER TABLE
do what you want?
mysql create table test (a char);
Query OK, 0 rows affected (0.00 sec)
mysql insert test values ('a'), ('b'), ('c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql alter table test add b int auto_increment, add primary key (b);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select * from test;
+--+---+
| a| b |
+--+---+
| a| 1 |
| b| 2 |
| c| 3 |
+--+---+
3 rows in set (0.00 sec)
mysql desc test;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| a | char(1) | YES | | NULL||
| b | int(11) | | PRI | NULL| auto_increment |
+---+-+--+-+-++
2 rows in set (0.00 sec)
--
If you cc me or remove the list(s) completely I'll most likely ignore
your message.see http://www.eyrie.org./~eagle/faqs/questions.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM
Cedric Gavage [EMAIL PROTECTED] wrote: I have a question about MyISAM, during an UPDATE for a row, is it a row locking or a table locking? MySQL locks the whole MyISAM table. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comp-err.exe
Morten Gulbrandsen [EMAIL PROTECTED] wrote: Compiler errormessage ver 1.3 C:\mysql\bincomp-err.exe -I comp-err.exe (Compile errormessage) Ver 1.3 This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Usage: comp-err.exe [-?] [-I] [-V] fromfile[s] tofile Options: -Info -Version Hello Programmers, for which purpose is this file included in the file C:\mysql\bin directory ? It's used for generating error message file: http://www.mysql.com/doc/en/Languages.html I have tried to RTFM and I did not find any entry about This specific file, perhaps it has another name under linux, I am using windows and would like to know what useful tasks Can be accomplished with comp-err.exe under windows 2000. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subselect functionality
Nils Valentin [EMAIL PROTECTED] wrote: I have a question regarding the MySQL feaures. From Version 4.1 Full subselect support was/is announced. However if I understood correctly then already from Version3.23-41 (or earlier) there are some subselect functions already included. For xample if I try this: CREATE TABLE tblname_new SELECT * FROM tblname_ori; then it works. Or do I have a misunderstanding of subselects (nested queries) ?? Nested queries is not only subselects. Yes, you are right, some nested queries like INSERT .. SELECT, CREATE .. SELECT was implemented in earlier than 4.1 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: datetime column dummy question
Also doesnt work in 4.1 alpha. Best regards Nils Valentin Tokyo/Japan (As requested I took Roman of , as he doesnt like direct e-mails ;-) 2003 6 25 19:12Roman Neuhauser : # [EMAIL PROTECTED] / 2003-06-25 06:18:04 +0100: Sometime recently MaFai said: A table contains a column named mydate. //Wrong sql statement alter table p_asset add mydate datetime default now(); alter table p_asset add mydate datetime default time(); alter table p_asset add mydate datetime default now; alter table p_asset add mydate datetime default time; alter table p_asset add mydate datetime default date(); alter table p_asset add mydate datetime default datetime(); How can I add the default now value into the specified column? I try to find in the mysql manual,but in the default value charter,no relative information can be found. I also know this question is stupid,but hope you help. alter table p_asset add mydate datetime default 'now()'; - you need the '' around now(); apparently http://www.mysql.com/doc/en/DATETIME.html and scroll down to the comment made by Lazy Soul on Tuesday May 27 2003, @8:15am I'd like to know in which version of MySQL this does what it's intended to do. Definitely not in 4.0.13: mysql create table autotime (foo int, bar datetime default 'now()'); Query OK, 0 rows affected (0.00 sec) mysql insert autotime set foo = 1; Query OK, 1 row affected (0.06 sec) mysql insert autotime set foo = 2; Query OK, 1 row affected (0.00 sec) mysql select * from autotime; +--+-+ | foo | bar | +--+-+ |1 | -00-00 00:00:00 | |2 | -00-00 00:00:00 | +--+-+ 2 rows in set (0.02 sec) mysql select version(); ++ | version() | ++ | 4.0.13-log | ++ 1 row in set (0.00 sec) -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comp-err.exe
At 12:54 25/6/2003 +0200, Morten Gulbrandsen wrote: Hi, for which purpose is this file included in the file C:\mysql\bin directory ? For to build the errmsg.sys files: Microsoft Windows 2000 [Versão 5.00.2195] (C) Copyright 1985-2000 Microsoft Corp. c:\mysql\bincomp-err c:\mysql\share\english\errmsg.txt c:\temp\errmsg.sys Found 237 messages in language file c:\temp\errmsg.sys -- Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problems with farsi sorting!!
I use unicode to store farsi data in mysql database but I have problems sorting it using ORDERBY command...(ORDER BY does'nt work properly) I don't know what to do . 1---)pleases help me if there is any solution for me to define a charset in the order that I want . 2---) how can I replace the default charset of mysql (latin1) with this new charset. 3---) please help me if there is any other solution like writing a functioin (how should I write a functioin). thanx for your helps... - Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month!
Re: mysql.sock doesn't exist
Yes, I installed by RPM. I have now found the error file as you mentioned. The output for the last few days is: 030621 06:49:27 mysqld started 030621 6:49:28 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43892 InnoDB: Doing recovery: scanned up to log sequence number 0 43892 030621 6:49:28 InnoDB: Flushing modified pages from the buffer pool... 030621 6:49:28 InnoDB: Started /usr/libexec/mysqld: ready for connections. Version: '4.0.13-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 A mysqld process already exists at Mon Jun 23 10:55:32 CDT 2003 A mysqld process already exists at Mon Jun 23 10:59:02 CDT 2003 A mysqld process already exists at Mon Jun 23 11:19:42 CDT 2003 A mysqld process already exists at Mon Jun 23 11:21:32 CDT 2003 A mysqld process already exists at Mon Jun 23 12:17:43 CDT 2003 A mysqld process already exists at Mon Jun 23 13:26:53 CDT 2003 A mysqld process already exists at Mon Jun 23 16:04:24 CDT 2003 A mysqld process already exists at Mon Jun 23 16:04:31 CDT 2003 A mysqld process already exists at Mon Jun 23 16:21:38 CDT 2003 A mysqld process already exists at Tue Jun 24 08:02:47 CDT 2003 A mysqld process already exists at Tue Jun 24 13:27:15 CDT 2003 A mysqld process already exists at Tue Jun 24 13:31:30 CDT 2003 A mysqld process already exists at Tue Jun 24 16:09:39 CDT 2003 A mysqld process already exists at Tue Jun 24 16:37:12 CDT 2003 Was one of my installation attempts perhaps not removed cleanly? Kevin John Nichel wrote: Did you install by RPM? If so, look in /var/lib/mysql The file will be you machine name dot err, eg... localhost.err -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subselect functionality
Hi Egor, Thank you for the reply. Do you mind to go abit mor into details ? It seems that I got domething wrong here. If nested queries are not only subselects, which other nested queries exist there ? Do you mind just writing a few samples, please ? Sorry if this seems a simple question - from the manual I misunderstood that nested queries and subselects are the same, which is obviously not the case as I heard now. I understood that Subselects originally exist of 2 or more SELECT... statements. Best regards Nils Valentin Tokyo/Japan 2003 6 25 19:53Egor Egorov : Nils Valentin [EMAIL PROTECTED] wrote: I have a question regarding the MySQL feaures. From Version 4.1 Full subselect support was/is announced. However if I understood correctly then already from Version3.23-41 (or earlier) there are some subselect functions already included. For xample if I try this: CREATE TABLE tblname_new SELECT * FROM tblname_ori; then it works. Or do I have a misunderstanding of subselects (nested queries) ?? Nested queries is not only subselects. Yes, you are right, some nested queries like INSERT .. SELECT, CREATE .. SELECT was implemented in earlier than 4.1 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dumping data
Hi, Try mysqldump -T: -T| --tab=... Creates tab separated textfile for each table to given path. (creates .sql and .txt files). NOTE: This only works if mysql dump is run on the same machine as the mysqld daemon. This was taken from the man page. On Wed, 2003-06-25 at 08:56, Rob wrote: Hi all, Is there any way to do a mysql dump in which each table is dumped into a separate file. I know I can use the --tables option to specify a table, but this means I have to type out each table name (and there are a lot of them). Is there any way to get mysql to automatically iterate through all the table names and dump each table to a separate file. The reason for this is that the db is BIG and we don't want to have to lug 200+mb files around. Plus mysql seems to have a real issue with dumping large dbs into one file. We've tried about 5 - 10 times and we keep getting corrupted data in the file. Thanks --- Rob ** Rob Cherry mailto:[EMAIL PROTECTED] +27 21 447 7440 Jam Warehouse RSA Smart Business Innovation http://www.jamwarehouse.com ** -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Performance
Todd Burke wrote: Hello 1/ I have a search engine which crawls auction sites and returns information which is inserted into a mysql database. This activity represents on average about 3 inserts per second with a combined payload of 450 bytes per second added to the database. This updating occurs continuously throughout the day. Since this activity puts a burden on the machine on which it occurs (not only the mysqld inserts but also the scripts and http client activity), I am planning to dedicate one box to the crawling/inserting and replicate the data to one (or possibly more) other servers. These slave servers will handle all client requests (almost exclusively reads). The mysql documentation states: You should set up one server as the master, and direct all writes to it, and configure as many slaves as you have the money and rackspace for, distributing the reads among the master and the slaves. My questions are: Since the updating occurs throughout the day and the same amount of data has to be eventually inserted into the slaves I assume the updating will require the same amount of resources (disk, cpu usage) on the slaves as on the master - the same number of writes will occur on the slaves. Correct. So the the benefit of this configuration (in terms of performance) is that it is the extra processing required to do the crawling will be offloaded to the one master server, not the resources required for the mysql writes. Your benefit will be to spread the load from the client requests across multiple servers. If all you want to do is to limit the affect of the crawler, you could batch those up and add them during off hours. I assume inserts done thru replication are not more efficient than regular inserts. Correct, replication inserts affect the db just as the original writes do. However, one nice thing that you can do to the slaves to limit the affect of inserts/updates is to turn on low priority updates. This prevents updates from locking out selects and makes your clients happier. (See the replication FAQ for more details on this: http://www.mysql.com/doc/en/Replication_FAQ.html) Also, as an alternative to batching the updates to the master as mentioned above, you could turn off replication during heavy times and turn it on until things catch up. This could easily be automated in a script. What is the performance hit of replication and is there a way to limit the effect of the writes on the slave servers, thru configuration parameters, for example, or would it even make sense to take the slave offline at regular intervals while replication is taking place? Performance hit for replication will just be extra load caused by the inserts from the master. The slave process has little or no extra overhead. As mentioned in the FAQ: You can also start the slaves with --skip-bdb, --low-priority-updates and --delay-key-write=ALL to get speed improvements for the slave. In this case the slave will use non-transactional MyISAM tables instead of BDB tables to get more speed. 2/ Is there any documentation on handling and configuring large tables? Hope this is not too confusing... Many thanks Todd Burke phbnyc.com Marc Prewitt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
user@% vs user@localhost question
This follows on a previous mail from me: When using GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY password I could not get the password authentication to kick in. Only supplying no password (empty string) succeeded. Even after doing SET PASSWORD and FLUSH PRIVILEGES. Then I tried GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY password And now it works the new password must be supplied for the user to logon. Does the % domain not include the localhost domain? If not, what is the use of the % domain? When should I use % and when localhost ? __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary tables - MySQL 4.1 alpha
WOW Victoria, thats a good explanation. That makes many things clearer now. Thank you very much. However, when not having the set the tmpdir variable were are the temporary tables stored ? (I searched the whole harddisc) I assume in this case that they must be stored in the memory, is that right ? Best regards Nils Valentin Tokyo/Japan 2003 6 25 19:17Victoria Reznichenko : Nils Valentin [EMAIL PROTECTED] wrote: I have some questions about temporary tables. I would appreciate any replies: I created a temporary table f.e. like this: mysql create temporary table temp SELECT * FROM relations; Query OK, 4 rows affected (2.35 sec) Records: 4 Duplicates: 0 Warnings: 0 When I do mysql show create table temp; Then it will give me this: | temp | CREATE TEMPORARY TABLE `temp` ( `member_id` int(16) NOT NULL default '0', `company_id` int(16) NOT NULL default '0', `membership_id` int(16) NOT NULL default '0' ) TYPE=MyISAM CHARSET=latin1 | Now what confuses me is that a) it says here TYPE=MyISAM Because MyISAM is default table type b) I understood that only HEAP tables are stored in the memory. Yes. I was assuming (until now) that HEAP tables are (the only one type of) temporary tables. Temporary tables may have type HEAP. I tried CREATE TEMPORARY TABLE temp TYPE= InnoDB SELECT * FROM relations; or CREATE TEMPORARY TABLE temp TYPE= HEAP SELECT * FROM relations; and they are all created as in-memory tables no files are created. Can I use any table format (ISAM,MYISAM,BDB,InnoDB,HEAP) for temporary tables You can use MyISAM, ISAM, MERGE, HEAP, InnoDB. Temporary table files are created in the temporary directory: http://www.mysql.com/doc/en/Temporary_files.html If, so what would be the difference between a temporary table in general and a HEAP table ? Temporary tables are the per-connection tables. HEAP tables are stored in memory tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: problems with farsi sorting!!
Note: forwarded message attached. Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month!---BeginMessage--- I use unicode to store farsi data in mysql database but I have problems sorting it using ORDERBY command...(ORDER BY does'nt work properly) I don't know what to do . 1---)pleases help me if there is any solution for me to define a charset in the order that I want . 2---) how can I replace the default charset of mysql (latin1) with this new charset. 3---) please help me if there is any other solution like writing a functioin (how should I write a functioin). thanx for your helps... - Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month!---End Message--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
type casting help!!!
hi!! i wrote a simple function that returns a string in C
the function fetches values from a table and stores all the value in a string variable
and then returns it ...This is small part inside the function i wrote!
while((row = mysql_fetch_row(res)))
{
unsigned long *lengths;
lengths = mysql_fetch_lengths(res);
for(i=0;i{
str = strcat(str,row[i]);
}
str = strcat(str,\n);
}
but i know that the content in red would rerturn an error!!
how to convert the output of mysql_fetch_row() function to an equivalent string ?
-
Want to chat instantly with your online friends? Get the FREE Yahoo!Messenger
Gant permission problems with domains and full hostnames for same user
Hello, I currently am struggling to get the permissions and table restrictions to work. Description of the case: -- For a database "examp", where table "foobar" which contains colums "A", "B" and C ,only a restricted access is allowed. Lets say that user "peter" idendified by "kieke" may only READattributes "A" and "B"from hosts in the domain "siemens.com", but can also do an UPDATE attribute "A" when he is connecting from host "stardust.siemens.com". The attribute "C" cannot be read or updated. What I tried, is to apply these rules for given case. I used the (textbook) grant statement like: grant SELECT (A,B) on examp.foobar for 'peter'@'stardust.siemens.com' identified by "kieke"; grantUPDATE (A) on examp.foobar for 'peter'@'stardust.siemens.com' identified by "kieke"; This does the job well. only from that host I have the correct access and restrictions. BUT, when I apply the 'domain' rule, it does not work anymore. In detail, I can still SELECT the A and B, but cannotUPDATE Aanymore from the specific host. grant SELECT (A,B) on examp.foobar for 'peter'@'%.siemens.com' identified by "kieke"; I tried by adding an entry to the host and db table of mysql-db, but there either then R/W is allowed on all attributes or on none. (all 'Y' or all 'N') Am I forgetting something ? I also tried to delete all created entries in the user table, and replaced them with one entry, namely by grantUSAGE on*.* for 'peter'@'%' identified by "kieke"; So that default permissions are set to 'N'. Basic idea: restrict access to only READ for specific domains (using the % ) and allow WRITE to some specic hosts of the same domain Hope someone can hint me a solution. With regards, Peter De Leuze SIEMENS Atea [EMAIL PROTECTED] phone: +32 14 253493 Fax: +32 14 22 29 94 Mobile Solutionsand Enabling Services http://www.ic.siemens.be Customer driven solution providers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Execution Time in mysql
# [EMAIL PROTECTED] / 2003-06-24 20:32:48 -0700: insert into table1 select * from table2; Table2 has some 10,000 records while table1 has around 11,00,000 records. The time reqd. to execute the above query is round about 80- 90 secs. The table1 has 7 columns out of which 5 are composite primary keys. No other indexing is provided. I have also tried to use the optimize table table1 command but to no effect. does this help? http://www.mysql.com/doc/en/Insert_speed.html -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: type casting help!!!
Prem Soman wrote:
hi!! i wrote a simple function that returns a string in C
the function fetches values from a table and stores all the value in a string variable
and then returns it ...This is small part inside the function i wrote!
while((row = mysql_fetch_row(res)))
{
unsigned long *lengths;
lengths = mysql_fetch_lengths(res);
for(i=0;i{
str = strcat(str,row[i]);
}
str = strcat(str,\n);
}
but i know that the content in red would rerturn an error!!
how to convert the output of mysql_fetch_row() function to an equivalent string ?
I think, perhaps, the C fragment above has got a bit mangled in
transmission. Assuming that
you want to concatenate the individual column and the code is based on
manual section
22.4.18 (My 3.23.28 manual) then you need some space in which to
construct the
concatenated columns.
unsigned long *lengths;
unsigned long tlength,i;
MYSQL_ROW row;
char *str;
row = mysql_fetch_row(res);
lengths = mysql_fetch_lengths(res); /* gives array of column
widths */
tlength = 0;
for(i=0;incols;i++) tlength += lengths[i];
tlength += 1; /* need space
for string terminator */
str = (char *)calloc(tlength,sizeof char); /* get
some space */
for(i=0;incols;i++) strcat(str,row[i]);/* build the string */
/* don't forget to free the space !! */
-
Want to chat instantly with your online friends? Get the FREE Yahoo!Messenger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting Countries
Alternatively you can create a priority column defaulting to one number (99) this will allow you to easily change the country sequence as needed. SELECT countries_id, countries_name FROM TABLE_COUNTRIES ORDER BY priority,countries_name; I hope this helps... Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP - Original Message - From: Don Read [EMAIL PROTECTED] To: Ralph [EMAIL PROTECTED] Cc: mySQL Mailing List [EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 4:38 AM Subject: Re: Sorting Countries On 25-Jun-2003 Ralph wrote: I am getting a list of all countries from database, and then I am sorting by country name. However since most orders will be from US I want the US to appear first over the rest of the countries. How can I go about doing this? Currently, this is my query: SELECT countries_id, countries_name FROM TABLE_COUNTRIES ORDER BY countries_name; SELECT IF(countries_id='US', 0, 1) as ctsort, countries_id, countries_name FROM TABLE_COUNTRIES ORDER BY ctsort, countries_name Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table 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]
CREATE TEMPORARY TABLE
Hi, I am running two identical systems, the only difference between the two are the database name and username. The problem I am getting only occurs on one of the systems. I am running -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
KEYS error 1216
Hi All, Im altering a number of table from MyISAM to innoDb and adding foreign keys. The alteration of the table type works. Adding the row as an index works. Adding the foreign key fails, generating the error: alter table project add FOREIGN KEY (company_id) references company (id) [mySQL] ERROR 1216: Cannot add or update a child row: a foreign key constraint fails CREATE TABLE company ( id int NOT NULL auto_increment, PRIMARY KEY (id) ) CREATE TABLE project ( id int NOT NULL auto_increment, company_id int default NULL, ) Would anyone have any idea what causing this error? Thanks Terry Terry Spencer Haigh Consultancy Services +44 (0)2073007329 www.haigh-cs.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help)
Nils Valentin wrote: SELECT * FROM machines INNER JOIN people ON machines.peopleID=people.peopleID; Inludes NULL records I meant the first statement returns also empty record fields or should I say incomplete data records ? Is not producing the same results as this: SELECT lastname, model FROM people, machines WHERE machines.peopleID = people.peopleID; Dosent include NULL records This one doesnt return any entries with incomplete data records (no data in them). So if you have an entry for machines.peopleID but not for people.peopeID than it wont show up while it does in the first statement. Nils, I disagree. The INNER JOIN should be completely equivalent to the (corrected) WHERE version. This is supported by Paul DuBois's MySQL, second edition, which states that the INNER JOIN is equivalent to the comma operator except that it allows and requires the ON clause. It is also supported by standard SQL usage in other RDBMSs. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re: Query Execution Time in mysql
I think we need more info: Table definition, MySQL version etc.. Med venlig hilsen Lars Geisler -Original Message- From: Roman Neuhauser [EMAIL PROTECTED] To: Amit Lonkar [EMAIL PROTECTED] CC: [EMAIL PROTECTED] [EMAIL PROTECTED] Subject: Re: Query Execution Time in mysql Sent: on, 25 jun 2003 12:36:34 GMT Received: on, 25 jun 2003 12:40:14 GMT Read: on, 25 jun 2003 13:19:51 GMT # [EMAIL PROTECTED] / 2003-06-24 20:32:48 -0700: insert into table1 select * from table2; Table2 has some 10,000 records while table1 has around 11,00,000 records. The time reqd. to execute the above query is round about 80- 90 secs. The table1 has 7 columns out of which 5 are composite primary keys. No other indexing is provided. I have also tried to use the optimize table table1 command but to no effect. does this help? http://www.mysql.com/doc/en/Insert_speed.html -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- 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: KEYS error 1216
# [EMAIL PROTECTED] / 2003-06-25 14:03:17 +0100: Im altering a number of table from MyISAM to innoDb and adding foreign keys. The alteration of the table type works. Adding the row as an index works. Adding the foreign key fails, generating the error: alter table project add FOREIGN KEY (company_id) references company (id) [mySQL] ERROR 1216: Cannot add or update a child row: a foreign key constraint fails CREATE TABLE company ( id int NOT NULL auto_increment, PRIMARY KEY (id) ) CREATE TABLE project ( id int NOT NULL auto_increment, company_id int default NULL, ) http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user@% vs user@localhost question
your MUA doesn't properly represent quotation marks, breaking them in other MUAs. # [EMAIL PROTECTED] / 2003-06-25 04:51:49 -0700: This follows on a previous mail from me: When using GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY ?password? I could not get the password authentication to kick in. Only supplying no password (empty string) succeeded. Even after doing ?SET PASSWORD?? and ?FLUSH PRIVILEGES?. Then I tried GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY ?password? And now it works ? the new password must be supplied for the user to logon. Does the ?%? domain not include the localhost domain? If not, what is the use of the ?%? domain? When should I use ?%? and when ?localhost? ? I believe % doesn't include localhost, but I could be wrong. -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help) -(I got it!)
In the second query, the server may switch the order of the join, producing the same output in different order. Try adding an ORDER BY to both and see what happens. [EMAIL PROTECTED] wrote: Ok, should be this: SELECT * FROM machines INNER JOIN people ON machines.peopleID=people.peopleID; Ted This: SELECT * FROM machines INNER JOIN people ON machines.machinesID=people.peopleID; Is not producing the same results as this: SELECT lastname, model FROM people, machines WHERE machines.peopleID = people.peopleID; Can someone please tell why, what's wrong? (What happens is that the wrong person are listed with the wrong machine -using the INNER JOIN shown above, the second statement I listed works as expected, right person with right machine.) Thanks in Advance, Ted Rogers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem setting/activating password
To also add priviliges on the local machine GRANT ALL ON *.* TO username@localhost IDENTIFIED BY password Riaan Oberholzer wrote: Hi, I am created a user by using: GRANT ALL ON *.* TO username@% IDENTIFIED BY password I did this from the mysql command line tool, logged in as root. The command succeeds and the new user is created in the mysql.user table, but the password does not work. I can only use this new user if I use a empty string password (no password). I also tried to do the SET PASSWORD afterwords, no luck. I then tried the Windows GUI / Administration to grant access and then set the password, but again, only an empty string password is accepted. I am using mysql 4.0.13. How do I actually get the password validation to be activated and instruct the server that the new user must provide his password? __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary tables - MySQL 4.1 alpha
Nils Valentin [EMAIL PROTECTED] wrote: WOW Victoria, thats a good explanation. That makes many things clearer now. Thank you very much. However, when not having the set the tmpdir variable were are the temporary tables stored ? (I searched the whole harddisc) I assume in this case that they must be stored in the memory, is that right ? Nope. Temporary tables are stored in memory only if table type is HEAP. All other temporary tables are stored on disk. By default temporary dir is /tmp or /usr/tmp. You can check it with SHOW VARIABLES LIKE 'tmpdir'; Name of temporary table files looks like #sql2884_b_0.frm. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problems with farsi sorting!!
mohammad kh.tajer [EMAIL PROTECTED] wrote: I use unicode to store farsi data in mysql database but I have problems sorting it using ORDERBY command...(ORDER BY does'nt work properly) I don't know what to do . 1---)pleases help me if there is any solution for me to define a charset in the order that I want . 2---) how can I replace the default charset of mysql (latin1) with this new charset. 3---) please help me if there is any other solution like writing a functioin (how should I write a functioin). You can change used character set with --default-character-set option of mysqld, but unicode is supported since version 4.1 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [MYSQL] Question about 'SHOW PROCESSLIST' output columns
PAUL MENARD [EMAIL PROTECTED] wrote: I'm having an intermittent problem with a MySQL server that I have running on a Windows 2000 Advance server systems. This issue occurs every few days depending on the load of the system. Here are the details. MySQL version 3.23.42-nt (I know I need to upgrade. Soon). Machine: Windows 2000 Advanced Server 2G memory 36G disk space. I have a Web page that allows my operators to monitor various process tasks on this server. Sometimes they receive an error that PHP cannot connect to MySQL. If this happens during the day they call me. I log into the server and bring up the WinMySQLadmin 3.1 client and click on the 'Process' tab. There are a few (maybe 250 attached processes). I know there are many machines connecting to this database to feed updates from enterprise applications. So I bumped up the 'max_connections' limit to 1000. This just seems to delay the limit and the phone call. My question is in looking at the output form the 'Process' tab I see the columns 'Command' and 'Time'. In all of the rows for 'Command' the value is 90% 'Sleep'. This would tell me I have some processes out there that are not dis-connecting (right?). In the 'Time' column is see numbers ranging from 150 to 1000+. I'm guessing this is the time the connection is idle (correct?). What I would like to do is define my parameters so that these dead processes will be disconnected by MySQL after a shorter time but not sure which variable to change. Advice? You can decrease value of wait_timout variable: http://www.mysql.com/doc/en/SHOW_VARIABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: user@% vs user@localhost question
I believe % doesn't include localhost, but I could be wrong. % Does indeed include localhost. At least it does on 4.0.13. Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user@% vs user@localhost question
Maybe it does, or maybe it doesn't; but if you're connecting to your server on the localhost, you're probably connecting through a pipe/UNIX type socket instead of over the network. This might be the distinction that matters in this case; does @localhost in this context mean through a non-network communications channel on the localhost rather than over the 127.0.0.1 network interface? Andy On Wed, 25 Jun 2003, Roman Neuhauser wrote: your MUA doesn't properly represent quotation marks, breaking them in other MUAs. # [EMAIL PROTECTED] / 2003-06-25 04:51:49 -0700: This follows on a previous mail from me: When using GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY ?password? I could not get the password authentication to kick in. Only supplying no password (empty string) succeeded. Even after doing ?SET PASSWORD?? and ?FLUSH PRIVILEGES?. Then I tried GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY ?password? And now it works ? the new password must be supplied for the user to logon. Does the ?%? domain not include the localhost domain? If not, what is the use of the ?%? domain? When should I use ?%? and when ?localhost? ? I believe % doesn't include localhost, but I could be wrong. -- Andy Stubbs, B.A., Ph.D. Network Manager, Active Hotels Ltd. +44 1223 578106 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SCO UnixWare porting of MYSQL
On Wed, 25 Jun 2003, Sudhipan Sharma wrote: Hi ! Just wanted to know if there is any installation procedure available on = UnixWare 7.1.x. There is patch available for SCO UnixWare libz.so, which = I have downloaded but Iam not able to initialize ./Configure script. = Any help/support will be appreciated. Take a look at www.zenez.com and look on the UnixWare/OpenUNIX FAQ, look for Third Party Programs... You should find what you need. I just started with MySQL and The next Releases have been fixed with this. BTW, what version are you using? tt -- Boyd Gerber, Software Engineeer Support Specialist MySQL AB, www.mysql.com Office: +1 801 604 5332 Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SCO UnixWare porting of MYSQL
I simply followed the directions listed here: http://www.mysql.com/doc/en/SCO_UnixWare.html And I had no problems. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Sudhipan Sharma [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 3:08 AM To: [EMAIL PROTECTED] Subject: SCO UnixWare porting of MYSQL Hi ! Just wanted to know if there is any installation procedure available on = UnixWare 7.1.x. There is patch available for SCO UnixWare libz.so, which = I have downloaded but Iam not able to initialize ./Configure script. = Any help/support will be appreciated. Thanks In Advance Regards Sudhipan -- --- Sudhipan Sharma SCO Group - formerly Caldera International. 56, Janpath New Delhi - 110 001 INDIA. Ph: 91-11-23736466, Fax : +91-11-23359997 Web :http://www.sco.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TEMPORARY TABLE
And what would that be? Phil Dowson wrote: Hi, I am running two identical systems, the only difference between the two are the database name and username. The problem I am getting only occurs on one of the systems. I am running -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Large file : InnoDB or MyISAM
In a situation with many concurrent reads and writes an InnoDB table would be preferable. See http://www.mysql.com/doc/en/Table_types.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: JOUANNET, Rodolphe [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 2:30 AM To: [EMAIL PROTECTED] Subject: Large file : InnoDB or MyISAM Hi to all, I will have to do with a very big file (approx 600 millions of records). Which is the best table handler for this king of table : InnoDB or MyISAM (many INSERT and MANY SELECT, no UPDATE - statistics file). Best regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user@% vs user@localhost question
Riaan Oberholzer [EMAIL PROTECTED] wrote: This follows on a previous mail from me: When using GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY ?password? I could not get the password authentication to kick in. Only supplying no password (empty string) succeeded. Even after doing ?SET PASSWORD?? and ?FLUSH PRIVILEGES?. Then I tried GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY ?password? And now it works ? the new password must be supplied for the user to logon. Does the ?%? domain not include the localhost domain? Include, '%' means any host. But when MySQL reads privilege table, it find first match entry. So if you have ''@'localhost', it will be taken. You can read more about connection verification at: http://www.mysql.com/doc/en/Connection_access.html If not, what is the use of the ?%? domain? When should I use ?%? and when ?localhost? ? Remove from table 'user' entry ''@'localhost'. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TEMPORARY TABLE
Phil Dowson [EMAIL PROTECTED] wrote: I am running two identical systems, the only difference between the two are the database name and username. The problem I am getting only occurs on one of the systems. I am running What exactly problem do you have? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM
MyISAM tables do not support row-level locking, only table locking. See http://www.mysql.com/doc/en/Table_locking.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Cedric Gavage [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 2:14 AM To: [EMAIL PROTECTED] Subject: MyISAM Hi all, I have a question about MyISAM, during an UPDATE for a row, is it a row locking or a table locking? -- Cedric Gavage [EMAIL PROTECTED] http://unixtech.be - http://gavage.com - OpenPGP: 0xED325C64 -- 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 4.0.13 GRANT syntax
I am attempting to modify the GRANT table using the syntax specified in the
MySQL 4.0.13 documentation, and am getting error messages claiming the
syntax is incorrect. (I'm running MySQL on Windows 98, by the way.) I used
mysqlc with root access.
mysql USE mysql;
Database changed
mysql GRANT SELECT ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'update';
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
'[EMAIL PROTECTED] IDENTIFIED BY 'update'' at line 1
mysql
From the manual, the syntax:
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER cipher [AND]]
[ISSUER issuer [AND]]
[SUBJECT subject]]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
MAX_UPDATES_PER_HOUR # |
MAX_CONNECTIONS_PER_HOUR #]]
... and the following examples:
mysql GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED]
- IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql GRANT ALL PRIVILEGES ON *.* TO monty@%
- IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql GRANT RELOAD,PROCESS ON *.* TO [EMAIL PROTECTED];
mysql GRANT USAGE ON *.* TO [EMAIL PROTECTED];
It appears that my syntax is consistant with the examples provided in the
4.0.13 documentation. The root account, of course, has full privileges with
GRANT.
Any ideas?
--
Adam Lawrence
Sustaining Engineering
Tectrol Inc.
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: user@% vs user@localhost question
yesterday i had same problem on 4.0.13 for win added [EMAIL PROTECTED] - and was rejected added [EMAIL PROTECTED] - then accepted -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 4:54 PM To: Roman Neuhauser; Riaan Oberholzer Cc: [EMAIL PROTECTED] Subject: RE: user@% vs user@localhost question I believe % doesn't include localhost, but I could be wrong. % Does indeed include localhost. At least it does on 4.0.13. Regards, Mike Hillyer www.vbmysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Dumping data
I think you need the -T option, which will break out table data to separate files. Here's a description from the manual: QUOTE -T, --tab=path-to-some-directory Creates a table_name.sql file, that contains the SQL CREATE commands, and a table_name.txt file, that contains the data, for each give table. The format of the `.txt' file is made according to the --fields-xxx and --lines--xxx options. Note: This option only works if mysqldump is run on the same machine as the mysqld daemon, and the user/group that mysqld is running as (normally user mysql, group mysql) needs to have permission to create/write a file at the location you specify. /QUOTE You will also want to look at http://www.mysql.com/doc/en/mysqldump.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Rob [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 1:57 AM To: MySql Subject: Dumping data Hi all, Is there any way to do a mysql dump in which each table is dumped into a separate file. I know I can use the --tables option to specify a table, but this means I have to type out each table name (and there are a lot of them). Is there any way to get mysql to automatically iterate through all the table names and dump each table to a separate file. The reason for this is that the db is BIG and we don't want to have to lug 200+mb files around. Plus mysql seems to have a real issue with dumping large dbs into one file. We've tried about 5 - 10 times and we keep getting corrupted data in the file. Thanks --- Rob ** Rob Cherry mailto:[EMAIL PROTECTED] +27 21 447 7440 Jam Warehouse RSA Smart Business Innovation http://www.jamwarehouse.com ** All information contained in this email is confidential and may be used by the intended recipient only. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Subselect functionality
I am not sure that could be viewed as a subselect, as it in not a SELECT within an SELECT, but is instead a SELECT within a CREATE. I would imagine that the SELECT within the CREATE is easier to implement that the actual SELECT within a SELECT. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Nils Valentin [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 1:12 AM To: [EMAIL PROTECTED] Subject: Subselect functionality Hi MySQL Fans ;-), I have a question regarding the MySQL feaures. From Version 4.1 Full subselect support was/is announced. However if I understood correctly then already from Version3.23-41 (or earlier) there are some subselect functions already included. For xample if I try this: CREATE TABLE tblname_new SELECT * FROM tblname_ori; then it works. Or do I have a misunderstanding of subselects (nested queries) ?? Best regards -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql ended error
Thanks you Nils . (B (B I truly appreciate how helpful you have been !! I have now got the (Berror sorted out now. (B (BI re-installed MySQL on my Mac OS Jaguar 3 times. Finally the third (Btime I got it to run. Somehow I had corrupted my User.MYI file (BI did have to delete the /usr/local/mysql/data Dir so that I can get (Brid of the Privilege Tables. (B (BHave a great day! (B (Bregards (B--Pushpinder (B (B (B (B (B (B (BOn Monday, June 23, 2003, at 10:55 PM, Nils Valentin wrote: (B (B Hi Singh, (B (B I followed the threat carefully, and the more I read the more I (B believe we are (B looking in the wrong place. (B (B I believe that this is an OS Issue. Uninstall mysql once more. Take (B whichever (B install method you are most comfortable with (but stick to it :-). (B check the (B datadir and used linux account VERY carefully before you install (B anything. (B (B f.e (rpm) /var/lib/mysql (B f.e. (binary) /usr/local/mysql/data (B (B must be owned by the Linux account which mysqld will be using (in most (B cases (B the name of the account is also mysql) (B (B do this: (B (B chown -R mysql /var/lib/mysql (for rpm) (B chown -R mysql /usr/local/mysql/data (for binary) (B shell ls -al (B (B drwx--2 mysqlmysqlgrp 4096 Jun 9 08:41 mysql (B lrwxrwxrwx1 mysqlmysqlgrp 15 Mar 19 16:51 mysql.sock - (B /tmp/mysql.sock (B (B To be 200% sure you can delete the folder (B (B /var/lib/mysql/mysql (B /usr/local/mysql/data/mysql (B (B This will completely remove the folder containing the privilege (B database (B files. (B (B Next lets see if we have a user account setup for the mysql demon. (B (B do this: (B (B shell cat /etc/group |grep mysql (B (B mysql:x:500: (B (B shell cat /etc/passwd |grep mysql (B (B mysql:x:60:2:MySQL database admin:/var/lib/mysql:/bin/false (B (B If this account doesnt exist create it !! (B (B Then try to install it again. (B (B Summary: We checked and setup the useraccount which will be used for (B mysql. (B We also removed the privilege database once more (just in case) to (B make a (B clean install. (B (B The privilege database will not be removed even when removing mysql - (B so (B doesnt matter how often you reinstall it, unless you delete it it wont (B help (B !! (B (B That should keep your mysql demon running. There is nothing left (B anymore - (B except on OS side (user quotes, linux account limitations etc.) (B (B I would be surprised if you dont get this beast into the air ;-) (B (B Let me know the outcome please. (B (B Best regards (B (B Nils Valentin (B (B (B (B (B (B 2003$BG/(B 6$B7n(B 24$BF|(B $B2PMKF|(B 02:43$B!"(BPushpinder Singh Garcha $B$5$s$O=q$-$^$7$?(B: (B Is there an mysql directory in /usr/local/mysql/data? (B [psg:/usr/local/mysql] psgarcha% sudo ls data (B 127.0.0.1.errib_logfile1 (Bpsg.local..err (B ShwetaParekh-Computer.local..err ibdata1 (Bpsg.local..err.save (B ShwetaParekh-Computer.local..pid masterstream (Btest (B ib_arch_log_00 mydb (B ib_logfile0 mysql (B (B Does mysql own /usr/local/mysql/data? (B Yes (B [psg:/usr/local/mysql] psgarcha% ls -la (B total 11288 (B drwxr-xr-x 22 root wheel 748 Jun 23 11:50 . (B drwxr-xr-x 6 root wheel 204 Jun 23 11:50 .. (B -rw-r--r-- 1 root wheel19106 May 14 16:50 COPYING (B -rw-r--r-- 1 root wheel28003 May 14 16:50 COPYING.LIB (B -rw-r--r-- 1 root wheel 181571 May 14 16:24 ChangeLog (B -rw-r--r-- 1 root wheel 6802 May 14 16:50 INSTALL-BINARY (B -rw-r--r-- 1 root wheel 1937 May 14 16:24 README (B drwxr-xr-x 46 root wheel 1564 Jun 23 09:22 bin (B -rwxr-xr-x 1 root wheel 773 May 14 17:01 configure (B drwxr-x--- 15 mysql wheel 510 Jun 23 13:29 data (B drwxr-xr-x 51 root wheel 1734 Jun 23 09:22 include (B drwxr-xr-x 8 root wheel 272 Jun 23 09:22 lib (B drwxr-xr-x 3 root wheel 102 Jun 23 09:22 man (B -rw-r--r-- 1 root wheel 2893112 May 14 16:48 manual.html (B -rw-r--r-- 1 root wheel 2514300 May 14 16:48 manual.txt (B -rw-r--r-- 1 root wheel 115727 May 14 16:48 manual_toc.html (B drwxr-xr-x 9 root wheel 306 Jun 23 09:22 mysql-test (B drwxr-xr-x 3 root wheel 102 Jun 23 09:22 scripts (B drwxr-xr-x 3 root wheel 102 Jun 23 09:22 share (B drwxr-xr-x 31 root wheel 1054 Jun 23 09:22 sql-bench (B drwxr-xr-x 12 root wheel 408 Jun 23 09:22 support-files (B drwxr-xr-x 21 root wheel 714 Jun 23 09:22 tests (B (B (B Does mysql have r/w privileges on /usr/local/mysql/data? (B Yes (B (B [psg:local/mysql/bin] psgarcha% cd /usr/local/mysql/ (B [psg:/usr/local/mysql] psgarcha% (B [psg:/usr/local/mysql] psgarcha% ./scripts/mysql_install_db --force (B mkdir: ./data/mysql: Permission denied (B
RE: Initializing primary key values for existing table
You need simply add the column predefined as AUTO_INCREMENT and PRIMARY KEY: CREATE TABLE incrtest ( name varchar(100) NOT NULL ) TYPE=MyISAM; INSERT INTO incrtest VALUES(ben); INSERT INTO incrtest VALUES(bob); INSERT INTO incrtest VALUES(bom); INSERT INTO incrtest VALUES(gddo); INSERT INTO incrtest VALUES(billy); ALTER TABLE incrtest ADD prikey INT auto_increment PRIMARY KEY; mysql SELECT * FROM incrtest; +---++ | name | prikey | +---++ | ben | 1 | | bob | 2 | | bom | 3 | | gddo | 4 | | billy | 5 | +---++ 5 rows in set (0.00 sec) As you can see, the values are added automatically. Regards, Miek Hillyer www.vbmysql.com -Original Message- From: John Hicks [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 12:11 AM To: [EMAIL PROTECTED] Subject: Initializing primary key values for existing table I needed to add a new, autoincrementing, primary key column to a table and have been struggling to figure out how to assign an initial, unique value to each row. I finally accomplished my task, but feel sure there's an easier way. Here is my solution: 1. Add the column: alter table mytable add mycolumn int auto_increment; 2. Set up a user variable: @mycounter = 0; 3. Assign the initial values by incrementing the counter: Update mytable set mycolumn = max((@mycounter := @mycounter + 1), @mycounter); 4. Finally, set the column to be the primary key: alter table mytable set primary key mycolumn; This seems like a roundabout way of doing things. Can any of you improve on it? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem setting/activating password
What about using the username only instead of username@% ? According to manual (section 7.34) it should be identic in behaviour: [...] The simple form user is a synonym for user@%. [...] Does it do the same? Lian P.S. Andy, sorry for posting by mistake to you. ;-| -Original Message- From: Riaan Oberholzer [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 9:19 AM To: [EMAIL PROTECTED] Subject: Problem setting/activating password Hi, I am created a user by using: GRANT ALL ON *.* TO username@% IDENTIFIED BY password I did this from the mysql command line tool, logged in as root. The command succeeds and the new user is created in the mysql.user table, but the password does not work. I can only use this new user if I use a empty string password (no password). I also tried to do the SET PASSWORD afterwords, no luck. I then tried the Windows GUI / Administration to grant access and then set the password, but again, only an empty string password is accepted. I am using mysql 4.0.13. How do I actually get the password validation to be activated and instruct the server that the new user must provide his password? __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: datetime column dummy question
The TIMESTAMP column type does this for you: See: http://www.mysql.com/doc/en/DATETIME.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: MaFai [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 11:01 AM To: [EMAIL PROTECTED] Subject: datetime column dummy question Hello, mysql, A table contains a column named mydate. //Wrong sql statement alter table p_asset add mydate datetime default now(); alter table p_asset add mydate datetime default time(); alter table p_asset add mydate datetime default now; alter table p_asset add mydate datetime default time; alter table p_asset add mydate datetime default date(); alter table p_asset add mydate datetime default datetime(); How can I add the default now value into the specified column? I try to find in the mysql manual,but in the default value charter,no relative information can be found. I also know this question is stupid,but hope you help. Best regards. MaFai [EMAIL PROTECTED] 2003-06-25 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problems with farsi sorting!!
You can change your charset by adding a line in your mysql configuration file like this. default-character-set = cp1256 Here cp1256 means that mysql will use Windows Arabic character set. I don't know the difference between farsi and arabic charsets. And also I don't know if mysql has a farsi charset support. But if you are able to use arabic charset you can set your charset to cp1256 like above. You can also take a look at http://www.mysql.com/doc/en/Charset-charsets.html page. Here you can get info about mysql charsets. [EMAIL PROTECTED] wrote: I use unicode to store farsi data in mysql database but I have problems sorting it using ORDERBY command...(ORDER BY does'nt work properly) I don't know what to do . 1---)pleases help me if there is any solution for me to define a charset in the order that I want . 2---) how can I replace the default charset of mysql (latin1) with this new charset. 3---) please help me if there is any other solution like writing a functioin (how should I write a functioin). thanx for your helps... - Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! -- Veysel Harun Sahin [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Execution Time in mysql
You may want to try disabling the index during the insert: ALTER TABLE table1 DISABLE KEYS; insert into table1 select * from table2; ALTER TABLE table1 ENABLE KEYS; And see what that does. See: http://www.mysql.com/doc/en/ALTER_TABLE.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Amit Lonkar [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 9:33 PM To: [EMAIL PROTECTED] Subject: Query Execution Time in mysql Hi All, I have 2 tables say table1 and table2 in the database. I am using the following query to copy all the data from table2 to table1. insert into table1 select * from table2; Table2 has some 10,000 records while table1 has around 11,00,000 records. The time reqd. to execute the above query is round about 80- 90 secs. The table1 has 7 columns out of which 5 are composite primary keys. No other indexing is provided. I have also tried to use the optimize table table1 command but to no effect. Please let know if any solution is available. Thanks Amit Lonkar __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Anyone had a chance to try an Opteron yet?
-Original Message- From: Lenz Grimmer [mailto:[EMAIL PROTECTED] Actually, you can create larger files on 32bit Linux systems as well. It's just that the file system and the C library must have support for LFS (Large File Support): http://www.suse.de/~aj/linux_lfs.html I can confirm this. I've been able to create files bigger than 2 gigs on ext2fs for quite a while now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem setting/activating password
The problem is that when I only define the % host, then no password is required to log in... (not a wanted feature!). I CAN log in, i just have to specify no password. The password checking is only done for localhost --- [EMAIL PROTECTED] wrote: What about using the username only instead of username@% ? According to manual (section 7.34) it should be identic in behaviour: [...] The simple form user is a synonym for user@%. [...] Does it do the same? Lian P.S. Andy, sorry for posting by mistake to you. ;-| -Original Message- From: Riaan Oberholzer [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 9:19 AM To: [EMAIL PROTECTED] Subject: Problem setting/activating password Hi, I am created a user by using: GRANT ALL ON *.* TO username@% IDENTIFIED BY password I did this from the mysql command line tool, logged in as root. The command succeeds and the new user is created in the mysql.user table, but the password does not work. I can only use this new user if I use a empty string password (no password). I also tried to do the SET PASSWORD afterwords, no luck. I then tried the Windows GUI / Administration to grant access and then set the password, but again, only an empty string password is accepted. I am using mysql 4.0.13. How do I actually get the password validation to be activated and instruct the server that the new user must provide his password? __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.13 GRANT syntax
On Wed, Jun 25, 2003 at 10:09:58AM -0400, Adam Lawrence wrote: I am attempting to modify the GRANT table using the syntax specified in the MySQL 4.0.13 documentation, and am getting error messages claiming the syntax is incorrect. (I'm running MySQL on Windows 98, by the way.) I used mysqlc with root access. mysql USE mysql; Database changed mysql GRANT SELECT ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'update'; 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 '[EMAIL PROTECTED] IDENTIFIED BY 'update'' at line 1 mysql Try [EMAIL PROTECTED] instead. UPDATE is a reserved word in SQL. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.0.13 GRANT syntax
Does enclosing the username and host in single quotes help?
I.E. GRANT SELECT ON *.* TO 'update'@'localhost' IDENTIFIED BY 'update';
Regards,
Mike Hillyer
www.vbmysql.com
-Original Message-
From: Adam Lawrence [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 25, 2003 8:10 AM
To: [EMAIL PROTECTED]
Subject: MySQL 4.0.13 GRANT syntax
I am attempting to modify the GRANT table using the syntax
specified in the
MySQL 4.0.13 documentation, and am getting error messages claiming the
syntax is incorrect. (I'm running MySQL on Windows 98, by the
way.) I used
mysqlc with root access.
mysql USE mysql;
Database changed
mysql GRANT SELECT ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'update';
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
'[EMAIL PROTECTED] IDENTIFIED BY 'update'' at line 1
mysql
From the manual, the syntax:
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER cipher [AND]]
[ISSUER issuer [AND]]
[SUBJECT subject]]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
MAX_UPDATES_PER_HOUR # |
MAX_CONNECTIONS_PER_HOUR #]]
... and the following examples:
mysql GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED]
- IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql GRANT ALL PRIVILEGES ON *.* TO monty@%
- IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql GRANT RELOAD,PROCESS ON *.* TO [EMAIL PROTECTED];
mysql GRANT USAGE ON *.* TO [EMAIL PROTECTED];
It appears that my syntax is consistant with the examples
provided in the
4.0.13 documentation. The root account, of course, has full
privileges with
GRANT.
Any ideas?
--
Adam Lawrence
Sustaining Engineering
Tectrol Inc.
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TEMPORARY TABLE
Sorry the problem fixed itself, and I mistakenly sent this email - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Phil Dowson [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 9:01 am Subject: Re: CREATE TEMPORARY TABLE And what would that be? Phil Dowson wrote: Hi, I am running two identical systems, the only difference between the two are the database name and username. The problem I am getting only occurs on one of the systems. I am running -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Mull in show fields and table keeps crashing
I'm not sure what errors are being reported to the server. I'm not sure how to get those. I am not local to the box. Is there a way to look at the log files without being at the box? Karl - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Karl J. Stubsjoen' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 2:20 PM Subject: RE: Strange Mull in show fields and table keeps crashing The `MUL` indicates that the column have multiple values. When the server `crashes`, what errors are being reported log file? -Original Message- From: Karl J. Stubsjoen [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 4:13 PM To: [EMAIL PROTECTED] Subject: Strange Mull in show fields and table keeps crashing Hello, I have this table: CLUB Field Type Null Key Default Extra clubid int(11) PRI NULL auto_increment clubusgf int(11) UNI 0 program char(3) url varchar(75) email varchar(75) phone varchar(14) fax varchar(14) address1 varchar(75) MUL address2 varchar(75) city varchar(75) notes varchar(255) zip varchar(10) clubname varchar(75) contact_primary varchar(40) state char(2) contact_secondary varchar(40) See address1 above the the MUL next to it. What is that? Also, this table keeps crashing, it crashes when I make an edit to anything in this field. So I copy the column, move the data over and then I can make edits in this field. However, another field in my table will get this strange MUL indication. From this point forward then, any changes to the data in that column will cause the table to crash. I've succesfully repaired the table a 1/2 dozen times or so... and copied/renamed about 4 of the columns as they took on this MUL characteristic. Any ideas how to fix this problem? Any ideas what is going on? Here is my version of MySQL: Server version: 4.0.0-alpha Karl -- 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]
RedHat 9.0 - Mysql 3.23.56
I installed the RPM version of MySQL 3.23.56 on Red Hat 9.0 When it installed, it started up mysql, no problems, I could do all mysql functions I recently rebooted the box, and mysql did not start automatically. I can start it if I run /etc/rc.d/init.d/mysql start Is there any know problems why this would not be working correctly with this particular combination? Thanks Luc Foisy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.0.13 GRANT syntax
update is a reserved word in MySQL. Choose another username.
Lian Sebe
Freelance Analyst-Programmer
www.programEz.net
-Original Message-
From: Adam Lawrence [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 25, 2003 5:10 PM
To: [EMAIL PROTECTED]
Subject: MySQL 4.0.13 GRANT syntax
I am attempting to modify the GRANT table using the syntax
specified in the
MySQL 4.0.13 documentation, and am getting error messages claiming the
syntax is incorrect. (I'm running MySQL on Windows 98, by the way.) I used
mysqlc with root access.
mysql USE mysql;
Database changed
mysql GRANT SELECT ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'update';
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
'[EMAIL PROTECTED] IDENTIFIED BY 'update'' at line 1
mysql
From the manual, the syntax:
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER cipher [AND]]
[ISSUER issuer [AND]]
[SUBJECT subject]]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
MAX_UPDATES_PER_HOUR # |
MAX_CONNECTIONS_PER_HOUR #]]
... and the following examples:
mysql GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED]
- IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql GRANT ALL PRIVILEGES ON *.* TO monty@%
- IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql GRANT RELOAD,PROCESS ON *.* TO [EMAIL PROTECTED];
mysql GRANT USAGE ON *.* TO [EMAIL PROTECTED];
It appears that my syntax is consistant with the examples provided in the
4.0.13 documentation. The root account, of course, has full
privileges with
GRANT.
Any ideas?
--
Adam Lawrence
Sustaining Engineering
Tectrol Inc.
--
--
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]
RedHat 9 - MySQL 3.23.56
Something interesting that may be my problem This is a known working install # mysql --version mysql Ver 11.18 Distrib 3.23.52, for pc-linux-gnu (i686) # find /etc/rc.d -name *mysql /etc/rc.d/init.d/mysql /etc/rc.d/rc0.d/K90mysql /etc/rc.d/rc1.d/K90mysql /etc/rc.d/rc2.d/S90mysql /etc/rc.d/rc3.d/S90mysql /etc/rc.d/rc4.d/S90mysql /etc/rc.d/rc5.d/S90mysql /etc/rc.d/rc6.d/K90mysql This is the broken one # mysql --version mysql Ver 11.18 Distrib 3.23.56, for pc-linux (i686) ]# find /etc/rc.d -name *mysql /etc/rc.d/init.d/mysql /etc/rc.d/rc0.d/K90mysql /etc/rc.d/rc1.d/K90mysql /etc/rc.d/rc2.d/S90mysql /etc/rc.d/rc3.d/K90mysql /etc/rc.d/rc4.d/S90mysql /etc/rc.d/rc5.d/K90mysql /etc/rc.d/rc6.d/K90mysql Anyone else see the possible problem? Where there a reason this was changed? Luc Foisy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 and PHP/Perl Clients
Hi All, Im a member of a similar PHP email list and posed the question to community about issues anyone has experienced with being able to connect to MySQL 4.1. Below are my email and a response. Is this reply statement correct? Why would MySQL break an interface to clients? Read his comment #2 below. Can anyone here explain this. I do NOT wish to start using ODBC. Im going to panic now. On Wed, 2003-06-25 at 03:50, Paul Menard wrote: I've been considering upgrading my data , MySQL 3.23.42 to 4.013 or even going to 4.1. I've read on the MySQL upgrade documents that some client might be affected when upgrading to 4.1. It does specifically mention Perl which I use to supplement PHP. By I'm more concerned about PHP. Should I be concerned? Yes, it will not work at all. MySQL 4.1 uses a protocol that's not compatible with MySQL 3.23 and 4.0. You need to use the ext/mysqli extension for it to work, but there are two problems with this: 1) it is only available with PHP5 which is not released yet. 2) it cannot be legally used since the MySQL 4.1 client libraries have been re-licensed under the GPL (old versions of the client libraries used to be under the more friendly LGPL). The only way out of this mess would be to use the ODBC driver for MySQL, but that will likely not be at all as fast as the native driver. -- Best regards, Per Lundberg / Capio ApS Phone: +46-18-4186040 Fax: +46-18-4186049 Web: http://www.nobolt.com
Re: RedHat 9.0 - Mysql 3.23.56
At 10:55 -0400 6/25/03, Luc Foisy wrote: I installed the RPM version of MySQL 3.23.56 on Red Hat 9.0 When it installed, it started up mysql, no problems, I could do all mysql functions I recently rebooted the box, and mysql did not start automatically. I can start it if I run /etc/rc.d/init.d/mysql start Is there any know problems why this would not be working correctly with this particular combination? Try: chkconfig --list mysql to see what runlevels the mysql script thinks it's supposed to start for. My guess is that it's not enabled properly. If not, then do this: chkconfig --levels 2345 mysql on Thanks Luc Foisy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW DATABASES user rights
Hello, I have a MySQL server running with some users besides root, that I want to grant all privileges for their own databases, but no rights for anything else. So to say, I have a user web01 that shall be able to do whatever he wants with the database db01. There are more databases, like db02 etc. If user web01 runs a 'SHOW DATABASES' command, he'll get to see ALL databases on the server, but I don't want him to see them... When the user does a 'USE dbname;', it says 'access denied'. There must be some special trick to let him only see the database(s) he's got rights on. (At least my webhoster managed that somehow...) Anyone an idea what rights I have to assign to get this running? yves at unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dumping data
On 25-Jun-2003 Rob wrote: Hi all, Is there any way to do a mysql dump in which each table is dumped into a separate file. I know I can use the --tables option to specify a table, but this means I have to type out each table name (and there are a lot of them). Is there any way to get mysql to automatically iterate through all the table names and dump each table to a separate file. The reason for this is that the db is BIG and we don't want to have to lug 200+mb files around. Plus mysql seems to have a real issue with dumping large dbs into one file. We've tried about 5 - 10 times and we keep getting corrupted data in the file. --- #!/bin/sh # DBS=gl ar ap inv for K in $DBS do TBLS=`mysql -N -e show tables $K` for I in $TBLS do mysqldump -e -q --add-drop-table $K $I $K.$I.sql rm -rf $K.$I.sql.gz gzip $K.$I.sql done done --- Regards -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange Mull in show fields and table keeps crashing
Do you have any sort of `shell` access to the box? SSH? Telnet? -Original Message- From: Karl J. Stubsjoen [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 9:51 AM To: [EMAIL PROTECTED] Subject: Re: Strange Mull in show fields and table keeps crashing I'm not sure what errors are being reported to the server. I'm not sure how to get those. I am not local to the box. Is there a way to look at the log files without being at the box? Karl - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Karl J. Stubsjoen' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 2:20 PM Subject: RE: Strange Mull in show fields and table keeps crashing The `MUL` indicates that the column have multiple values. When the server `crashes`, what errors are being reported log file? -Original Message- From: Karl J. Stubsjoen [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 4:13 PM To: [EMAIL PROTECTED] Subject: Strange Mull in show fields and table keeps crashing Hello, I have this table: CLUB Field Type Null Key Default Extra clubid int(11) PRI NULL auto_increment clubusgf int(11) UNI 0 program char(3) url varchar(75) email varchar(75) phone varchar(14) fax varchar(14) address1 varchar(75) MUL address2 varchar(75) city varchar(75) notes varchar(255) zip varchar(10) clubname varchar(75) contact_primary varchar(40) state char(2) contact_secondary varchar(40) See address1 above the the MUL next to it. What is that? Also, this table keeps crashing, it crashes when I make an edit to anything in this field. So I copy the column, move the data over and then I can make edits in this field. However, another field in my table will get this strange MUL indication. From this point forward then, any changes to the data in that column will cause the table to crash. I've succesfully repaired the table a 1/2 dozen times or so... and copied/renamed about 4 of the columns as they took on this MUL characteristic. Any ideas how to fix this problem? Any ideas what is going on? Here is my version of MySQL: Server version: 4.0.0-alpha Karl -- 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: RedHat 9.0 - Mysql 3.23.56
Yes, I turned those runlevels on already and now it works. The problem is that it was a practically default install, that is the way the runlevels were set out of the box 3.23.56 was this way after install mysql 0:off 1:off 2:on3:off4:on5:off6:off 3.23.52 was this way mysql 0:off 1:off 2:on3:on4:on5:on6:off Was wondering why though. A mistake or for some reason that I am curious about.. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 12:13 PM To: Luc Foisy; MYSQL-List (E-mail) Subject: Re: RedHat 9.0 - Mysql 3.23.56 At 10:55 -0400 6/25/03, Luc Foisy wrote: I installed the RPM version of MySQL 3.23.56 on Red Hat 9.0 When it installed, it started up mysql, no problems, I could do all mysql functions I recently rebooted the box, and mysql did not start automatically. I can start it if I run /etc/rc.d/init.d/mysql start Is there any know problems why this would not be working correctly with this particular combination? Try: chkconfig --list mysql to see what runlevels the mysql script thinks it's supposed to start for. My guess is that it's not enabled properly. If not, then do this: chkconfig --levels 2345 mysql on -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
