export file
i need help!! i an doing my assignment using mysql to establish a database!! i already create the table and insert the data, but i don't know how to create a export file, anyone can help me!! _ Ãâ·ÑÏÂÔØ MSN Explorer£ºhttp://explorer.msn.com/lccn/intl.asp¡£ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Quick SQL design help
Hello MySQL users I was wondering if one of you could advise me on the best solution to a problem I'm having - I'm sure this has been done before but haven't found anything in the archives. Basically the problem I am having is how best to handle multiple values for a specific column, in this case the values in question are coming from an HTML SELECT MULTI box processed by PHP. The way I have been doing this so far is to have a delimited value stored in a varchar column, e.g. If my select box returns the values 2,4 and 7 I insert into my table the string '|2|4|7|'. Surely there must be a better way than this - but it escapes me. In this setup the only way to match a specifc value when searching is to use the query: SELECT dataid,title FROM table WHERE category LIKE '%|4|%' Which obviously has a huge performance penalty - and of course you can't JOIN against any of these values. The only other way I thought of was to use a separate table for the category entries: SELECT dataid,title,category FROM table LEFT JOIN table_categories ON table.dataid=table_categories.dataid But in the example above this would return 3 entries, which I don't want, and I can't select a particular dataid which satisfies more than category, e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%'). Any ideas? Please help! Cheers, james - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Request for help in testing new replication code in 4.0.2
Dear MySQL users, I have just pushed my latest changes in the replication code in our 4.0 development tree, which change the slave to use two threads - I/O thread that gets the data from the master and logs it, and SQL thread which processes the logged data. I have tested it extensively, and it has passed all of my tests. However, as we all know, field testing tends to find nasty bugs that have not been caught in regression tests and visual code examination. So I need your help with field testing of my code. For those of you who are wondering why you should - this will help us stabilize 4.0 a lot of faster, and not only replication, but also the general SQL features. So if you depend on MySQL for your mission-critical applications, helping us test our alpha is directly in your best interest, and is worth the hassle. Testing the new code is relatively easy, and does not require risking the stability of your application by running alpha code. Here is what you need to do: * Find a machine that will act as a test server. With the availablity of Linux and FreeBSD, this can be done cost-effectively by sacrificing some useless dust-gathering PC in your office. In some case, you may even try it with your own desktop. * If you want to do it this very moment, read the instructions at http://www.mysql.com/doc/I/n/Installing_source_tree.html and install a pre-release 4.0.2 MySQL on your test server. You may also wait until 4.0.2 is released, but in that case, replication in 4.0.2 may have a bug you could have caught on your system and it will not be fixed until 4.0.3. * Follow instructions at http://www.mysql.com/doc/R/e/Replication_HOWTO.html and set up the test server as a slave of one of your production machines. Add log-slave-updates and log-bin to your slave configuration for easier troublshooting. The master can be either 3.23.33+ or 4.0.2 * Monitor your slave to make sure it does not crash ( watch error log for stack trace messages), slave keeps running ( check with SHOW SLAVE STATUS), and data is consistent. * If there are problems, I will need the following: - error logs on the master and on the slave - binary logs on the master, binary logs on the slave, and relay logs on the slave ( by default, placed in datadir and called `hostname`-relay-bin.* ) - output of SHOW SLAVE STATUS - version of the master Please send all follow-up mails to [EMAIL PROTECTED] and do not forget to CC to one of the lists, depending on the nature of the message. A bug report with precise instructions on how we can repeat it should be CC'ed to [EMAIL PROTECTED], patches and comments on the source code should be CC'ed to [EMAIL PROTECTED], and everything else should be CC'ed to [EMAIL PROTECTED] Please do not send personal mails without CC'ing them to a list - three reasons: a) I may not be the best person to answer, b) somebody else can get around to answering it sooner, and c) others may want to participate in the discussion. Overall, it is generally a good idea to avoid personal-only mails to MySQL employees unless you have a strictly personal question, of course. If you are talking about MySQL, you may send it to the person you think would be the best to answer your mail, but always make sure to CC to some general alias so that others can follow the discussion. -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA <___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB foreign keys crash MySQL
Harald, I was now able to repeat the error and fixed it. If one defines a non-latin1 character set as the default (german1 in your case), then the sorting order of the InnoDB internal datatype DATA_VARCHAR was undefined, and foreign key system tables did not work, because they contain DATA_VARCHAR columns. The fix probably makes it to upcoming MySQL-4.0.2, if Monty has not yet frozen the 4.0.2 codebase. It will also be in 3.23.49. Best regards, Heikki Innobase Oy -Original Message- From: Harald Fuchs <[EMAIL PROTECTED]> To: Heikki Tuuri <[EMAIL PROTECTED]> Date: Saturday, February 09, 2002 3:59 PM Subject: Re: InnoDB foreign keys crash MySQL >> Harald, > >Hi, Heikki! > ... > >> I tested now with the official Linux binary of 4.0.1 (not -max) and it >> worked ok on our dual Xeon Linux-2.4.16-SMP-64GB. I did not define any >> InnoDB startup options in my.cnf. > >I just downloaded mysql-4.0.1-alpha-pc-linux-gnu-i686.tar.gz (now also >not -max) from the German mirror and tried it. I still get the crash. > >My system is a 550MHz AMD K6-2 running kernel 2.2.18, if this should >matter. I don't think this is a hardware bug: I tried the same script >on another Linux box (dual 450MHz Pentium II, 2.2.18-SMP) running >4.0.1-max and also get the crash. > >> When you recreated the InnoDB data files, did you remember to remove the >> .frm files of the InnoDB tables t1, t2, t3 in the database directory 'test'? > >Yes. Actually, I did "rm -rf test/* ib*" before starting MySQL. > >> What is your my.cnf like? > >After removing all InnoDB directives, there's not much left: > > [client] > port = 3306 > > [mysqld] > port = 3306 > user = mysql > skip-locking > set-variable = join_buffer_size=512k > set-variable = key_buffer_size=64M > set-variable = record_buffer=256k > set-variable = sort_buffer=2M > set-variable = table_cache=16 > set-variable = tmp_table_size=32M > log-slow-queries > > default-character-set=german1 > >The SQL command sequence causing the crash is as follows: > > DROP TABLE IF EXISTS t1; > DROP TABLE IF EXISTS t2; > DROP TABLE IF EXISTS t3; > > CREATE TABLE t1 ( >id INT UNSIGNED NOT NULL AUTO_INCREMENT, >PRIMARY KEY (id) > ) TYPE=InnoDB; > > CREATE TABLE t2 ( >id INT UNSIGNED NOT NULL AUTO_INCREMENT, >t1id INT UNSIGNED NOT NULL, >PRIMARY KEY (id), >KEY (t1id), >FOREIGN KEY (t1id) REFERENCES t1(id) > ) TYPE=InnoDB; > > CREATE TABLE t3 ( >id INT UNSIGNED NOT NULL AUTO_INCREMENT, >t2id INT UNSIGNED NOT NULL, >PRIMARY KEY (id), >KEY (t2id), >FOREIGN KEY (t2id) REFERENCES t2(id) > ) TYPE=InnoDB; > > DROP TABLE IF EXISTS t3; > DROP TABLE IF EXISTS t2; > DROP TABLE IF EXISTS t1; > >Any other information I could supply? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQLGUI
How do you install it I untarred it and now what do I do. I am running RH 7.0 downloaded mysqlgui-1.7.5. Thanks Andy Magana San Diego,Ca - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: is possible 1 query with 2 selects?
I changed it...
they were | AND relAB.B='a1' in the other examples
| SELECT QB.id, QB.ref, QB.question from QB, relAB
| WHERE QB.ref=relAB.B
| AND relAB.A='a1'
|
| SELECT DISTINCT QB.id, QB.ref, QB.question from QB, relAB
| WHERE QB.ref=relAB.B
| AND relAB.A='a1'
|
| SELECT * from QB as tbl1
| LEFT JOIN relAB as tbl2
| ON tbl2.B=tbl1.ref
| where tbl2.A='a1'
-Original Message-
From: Nuno Teixeira [mailto:[EMAIL PROTECTED]]
Sent: Saturday, February 09, 2002 12:45 PM
To: Butch Bean
Cc: [EMAIL PROTECTED]
Subject: Re: is possible 1 query with 2 selects?
Hi,
Ok. Now I don't get syntax error but I don't get any results.
Anyway, I think that it is something like your examples.
Again, I need to get this results with only one query:
1st: get results from relAB to see QB relations:
SELECT B from relAB WHERE A='a1';
results:
+--+
| B|
+--+
| b1 |
| b2 |
| b3 |
+--+
(This results said that only QB b1, b2 and b3 refs are related to 'a1')
2nd: get only b1, b2, b3 from QB:
SELECT * FROM QB WHERE ref in ('b1','b2','b3');
results:
++--+--+
| id | ref | question |
++--+--+
| 1 | b1 | qb1 |
| 2 | b2 | qb2 |
| 3 | b3 | qb3 |
++--+--+
I'm trying to adapt your examples to see if I can get it to work.
Thanks again,
Nuno Teixeira
On Sat, Feb 09, 2002 at 12:16:40PM -0500, Butch Bean wrote:
| Nuno,
|
| I think these examples will work OK for what you want...
|
| Butch
|
| SELECT QB.id, QB.ref, QB.question from QB, relAB
| WHERE QB.ref=relAB.B
| AND relAB.B='a1'
|
| SELECT DISTINCT QB.id, QB.ref, QB.question from QB, relAB
| WHERE QB.ref=relAB.B
| AND relAB.B='a1'
|
| SELECT * from QB as tbl1
| LEFT JOIN relAB as tbl2
| ON tbl2.B=tbl1.ref
| where tbl2.B='a1'
|
|
| -Original Message-
| From: Nuno Teixeira [mailto:[EMAIL PROTECTED]]
| Sent: Saturday, February 09, 2002 11:18 AM
| To: Butch Bean
| Cc: [EMAIL PROTECTED]
| Subject: Re: is possible 1 query with 2 selects?
|
|
|
| Hi,
|
| I try it and I get a syntax error:
|
| "You have an error in your SQL syntax near 'on tbl2.A=tbl1.A where
| tbl2.A='a1'' at line 1"
|
| My tables structure are:
|
| show fields from QB;
| +--+-+--+-+-++
| | Field| Type| Null | Key | Default | Extra |
| +--+-+--+-+-++
| | id | int(11) | | PRI | NULL| auto_increment |
| | ref | char(3) | YES | | NULL||
| | question | varchar(30) | YES | | NULL||
| +--+-+--+-+-++
|
| show fields from relAB;
| +---+-+--+-+-++
| | Field | Type| Null | Key | Default | Extra |
| +---+-+--+-+-++
| | id| int(11) | | PRI | NULL| auto_increment |
| | A | char(3) | YES | | NULL||
| | B | char(3) | YES | | NULL||
| +---+-+--+-+-++
|
|
| Thanks for your help,
|
| Nuno Teixeira
|
| On Sat, Feb 09, 2002 at 08:49:44AM -0500, Butch Bean wrote:
| | I don't know you table structure but something like this would do it...
| |
| | SELECT * from QB as tbl1
| | JOIN relAB as tbl2
| | ON tbl2.A=tbl1.A
| | where tbl2.a='a1'
| |
| | Butch Bean
| |
| | -Original Message-
| | From: Egor Egorov [mailto:[EMAIL PROTECTED]]
| | Sent: Saturday, February 09, 2002 7:24 AM
| | To: [EMAIL PROTECTED]
| | Subject: is possible 1 query with 2 selects?
| |
| |
| | Nuno,
| |
| | Friday, February 08, 2002, 11:04:51 PM, you wrote:
| |
| |
| | NT> Hello to all,
| |
| | NT> I'm trying to make a query with only one command but I can't see
how
| | to do
| | NT> it.
| |
| | [skip]
| |
| | NT> What I want to do is get a list from table QB related to an item
| from
| | NT> table QA:
| |
| | NT> For example, get a list from QB related to item 'a1':
| |
| | NT> 1. select B from relAB where A='a1';
| |
| | NT> (result: b2, b3, b4)
| |
| | NT> 2. select * from QB where ref in ('b2','b3','b4');
| |
| | NT> (result: only 'a1' related items)
| |
| | NT> There is a way of make this query with only one command?
| |
| | NT> Something like:
| |
| | NT> 1. select * from QB where ref in (select B from relAB where
| | A='a1');
| |
| | MySQL doesn't curently support sub-selects, look at:
| | http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html
| |
| | It is in our plans to support sub-selects in future:
| | http://www.mysql.com/doc/T/O/TODO_MySQL_4.1.html
| |
| | NT> Please help me in this matter.
| | NT> Thanks very m
Re: My SQL TIME_FORMAT
Hello,
Removing the quotations worked. Thanks for the help.
Mike
- Original Message -
From: "DL Neil" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Saturday, February 09, 2002 5:54 PM
Subject: Re: My SQL TIME_FORMAT
> Hello mike
>
> > I've been reading about TIME _FORMAT in the mysql manual. Does this
only
> > work
> > with a manually entered time?
> > SELECT TIME_FORMAT('13:45:00','%h:%i') as time FROM table_name;
> >
> > I tried this,
> > SELECT TIME_FORMAT('column_name','%h:%i') as time FROM table_name;
> > to format my time column, But it didn't work.
>
>
> Try entering the column_name without the quotation marks.
> The column has been declared as an appropriate type and filled with data,
hasn't it?
>
> =dn
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.319 / Virus Database: 178 - Release Date: 1/28/02
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: My SQL TIME_FORMAT
Hello mike
> I've been reading about TIME _FORMAT in the mysql manual. Does this only
> work
> with a manually entered time?
> SELECT TIME_FORMAT('13:45:00','%h:%i') as time FROM table_name;
>
> I tried this,
> SELECT TIME_FORMAT('column_name','%h:%i') as time FROM table_name;
> to format my time column, But it didn't work.
Try entering the column_name without the quotation marks.
The column has been declared as an appropriate type and filled with data, hasn't it?
=dn
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: permissions problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 sql * and then Paul DuBois declared > Do you have an entry for user apache, host localhost in the user No just entries for 'nick' and 'root' > table, and have you deleted the entries for user='' and run FLUSH > PRIVILEGES? Certainly have. - -- Nick Wilson Tel:+45 3325 0688 Fax:+45 3325 0677 Web:www.explodingnet.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) iD8DBQE8ZbGfHpvrrTa6L5oRAmGHAJ4i/zpcnuo+l9NZedwdRi1s923tVACfV2v/ YUD5rUO5rge/dadTNFfbN0c= =n7Zo -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: COUNT --- GROUP BY --- LEFT JOIN
At 2:08 -0800 2/9/02, Joe Chesak wrote: >I am attempting to combine COUNT, GROUP BY, and LEFT >JOIN.. Adopting the theme on >http://www.mysql.com/doc/C/o/Counting_rows.html my >goal is to display only species/sex combinations that >occur more than once. Additionally I wish to include >the count for each row, and some keyed field from >another table.. When you do a GROUP BY, you can't just arbitrarily include some other stuff. See below. > >So I added a separate table "owner" containing foreign >key "petID" and the field "zipcode".. > >A couple rows of output might look like (species=dog >sex=male animalcount=3 zipcode=5) (species=dog >sex=female animalcount=2 zipcode=5). There would >be no rows with a count of 1, and zipcode can repeat.. > > >The following statement unfortunately includes zipcode >when determining uniqueness. It's as close as I've >come! Any suggestions?: > >SELECT species, sex, zip, COUNT(*) as animalcount FROM >pet LEFT JOIN owner ON pet.petID = owner.petID GROUP >By species, sex HAVING animalcount > 1; With GROUP BY, you can select only the values of the grouped columns and the values of the summary function (COUNT() in this case. If you include other columnes, you change the meaning of the query. You say that when zipcode is added, it's used in determining uniqueness. There's probably something else going on, but suppose that's what's really happening. And suppose that zip is not unique for a given species and sex. Which one should MySQL choose? Which row having a given species and sexid combination should MySQL use for picking a pet.petID value to association with the owner.petID so that it can pick a zipcode value? What's the solution? If I understand your first paragraph correctly, I *think* what you may be able to do is generate the species/sex counts into another table. Then join that with the original table and join the result to the owner table based on the petID values. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
My SQL TIME_FORMAT
Hello,
I've been reading about TIME _FORMAT in the mysql manual. Does this only
work
with a manually entered time?
SELECT TIME_FORMAT('13:45:00','%h:%i') as time FROM table_name;
I tried this,
SELECT TIME_FORMAT('column_name','%h:%i') as time FROM table_name;
to format my time column, But it didn't work.
Thanks in advance for the help,
Mike
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.319 / Virus Database: 178 - Release Date: 1/28/02
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: permissions problem
At 0:19 +0100 2/10/02, Nick Wilson wrote: >* and then Paul DuBois declared >> >BTW >> >er... I have your book here :) >> >> Ah. Well, in *that* case, check p429 and pp464-465. You may be getting >> bit by the phenomenon described there. >sql > >Aha! Well that certainly appears to be it but now I'm getting Access >denied to user apache@localhost > >What next Paul? Do you have an entry for user apache, host localhost in the user table, and have you deleted the entries for user='' and run FLUSH PRIVILEGES? > > >Much thanks BTW >-- > >Nick Wilson > >Tel: +45 3325 0688 >Fax: +45 3325 0677 >Web: www.explodingnet.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: permissions problem
* and then Paul DuBois declared > >BTW > >er... I have your book here :) > > Ah. Well, in *that* case, check p429 and pp464-465. You may be getting > bit by the phenomenon described there. sql Aha! Well that certainly appears to be it but now I'm getting Access denied to user apache@localhost What next Paul? Much thanks BTW -- Nick Wilson Tel:+45 3325 0688 Fax:+45 3325 0677 Web:www.explodingnet.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL on OpenBSD/sparc64?
[I didn't get a response from openbsd-sparc, so I'm trying here. Any help would be appreciated!] I'm trying to get a MySQL server running under a recent OpenBSD 3.0 snapshot on an Ultra 5, but I'm not having much luck. I tried installing the binary mysql-server package (from 3.0/packages/sparc64/), but it looks like the mysqld in that package is linked against libwrap1.2, whereas 2.0 is included in that snapshot (and 3.0 itself, too?). I've also tried compiling the mysql packages from the ports collection, but gen_lex_hash complains that it can't find an ideal hash and prompts me to run it with --search. gen_lex_hash --search yields: # ./gen_lex_hash --search >> lex_hash.h Fatal error when generating hash for symbols Didn't find suitable values for perfect hashing: You have to edit gen_lex_hash.cc to generate a new hashing function. You can try running gen_lex_hash with --search to find a suitable value Symbol array size = 8000 Symbols: 311 Functions: 123; Total: 434 Shifts per char: 3, Array size: 5303 Error: Got duplicate value for symbol 'FUNCTION' I've tried running it a few times (at a couple hours' runtime a shot), but get the same thing every time, just with a different symbol. Has anyone else gotten mysql-server built and installed on OpenBSD 3.0/sparc64? thanks, john -- John Morrissey _o/\ __o [EMAIL PROTECTED]_-< \_ / \ < \, www.horde.net/__(_)/_(_)/\___(_) /_(_)__ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: permissions problem
At 22:25 +0100 2/9/02, Nick Wilson wrote: >-BEGIN PGP SIGNED MESSAGE- >Hash: SHA1 > >Hi all, >I'm having a spot of bother with permissions (i think) >I just set up MySQL for private developement at home with PHP and >created a db 'mydb' and granted all privs to nick@localhost > >Now, I can 'USE mydb' from the command line but if I try to do the same >from a php script I get 'Access denied for user: '@localhost' .. Note that the message doesn't include a user name in front of the '@'. Which it would if you were specifying one. > >Can somebody please tell me what to do? > >Many thanks... >- -- > >Nick Wilson > >Tel: +45 3325 0688 >Fax: +45 3325 0677 >Web: www.explodingnet.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Indexes on UPDATE/DELETE
sql,query At 15:35 -0700 2/9/02, Andrei Cojocaru wrote: >Also, how do I reset an auto-increment value back to 1 (with all >rows being already deleted of course). ALTER TABLE tbl_name AUTO_INCREMENT = 1; > >Thanks for your help in advance. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: permissions problem
At 23:31 +0100 2/9/02, Nick Wilson wrote: >-BEGIN PGP SIGNED MESSAGE- >Hash: SHA1 > > >* and then Paul DuBois declared >> Note that the message doesn't include a user name in front of the '@'. >> >> Which it would if you were specifying one. > >Hello Paul, > I did notice that but I /am/ specifying a user mysql_connect($host, >$user, $pass); > >Is that what you mean? Okay, then it's not that, at least if $user has a non-empty value. :-) > >BTW >er... I have your book here :) Ah. Well, in *that* case, check p429 and pp464-465. You may be getting bit by the phenomenon described there. >- -- > >Nick Wilson > >Tel: +45 3325 0688 >Fax: +45 3325 0677 >Web: www.explodingnet.com > > > >-BEGIN PGP SIGNATURE- >Version: GnuPG v1.0.6 (GNU/Linux) > >iD8DBQE8ZaNXHpvrrTa6L5oRAiPrAJ435aYGm+c3twUrFMzLHfVB+qMkeQCfXGHM >2GcGatSYMx2pQo7kXUjWx58= >=1N7q >-END PGP SIGNATURE- > >- >Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Indexes on UPDATE/DELETE
Hello fellow mySQL users, I'd like to know if UPDATE/DELETE statements use indexes on the where statement the same way as SELECTs. If I build a SELECT using the WHERE statement that I use in an UPDATE/DELETE statement, can I use that accurately to determine what indexes mySQL will use? Also, how do I reset an auto-increment value back to 1 (with all rows being already deleted of course). Thanks for your help in advance. _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: permissions problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 * and then Paul DuBois declared > Note that the message doesn't include a user name in front of the '@'. > > Which it would if you were specifying one. Hello Paul, I did notice that but I /am/ specifying a user mysql_connect($host, $user, $pass); Is that what you mean? BTW er... I have your book here :) - -- Nick Wilson Tel:+45 3325 0688 Fax:+45 3325 0677 Web:www.explodingnet.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) iD8DBQE8ZaNXHpvrrTa6L5oRAiPrAJ435aYGm+c3twUrFMzLHfVB+qMkeQCfXGHM 2GcGatSYMx2pQo7kXUjWx58= =1N7q -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
permissions problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm having a spot of bother with permissions (i think) I just set up MySQL for private developement at home with PHP and created a db 'mydb' and granted all privs to nick@localhost Now, I can 'USE mydb' from the command line but if I try to do the same from a php script I get 'Access denied for user: '@localhost' .. Can somebody please tell me what to do? Many thanks... - -- Nick Wilson Tel:+45 3325 0688 Fax:+45 3325 0677 Web:www.explodingnet.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) iD8DBQE8ZZO4HpvrrTa6L5oRAv4RAJ9duXHJqZfzZ9Oqe35I0rDvclHdIACePmJ+ hCYzY9nxo9GCX0lCNF4S+kk= =Tzyb -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Is it a bug / not implemented /me ?
* Almar van Pel > mysql> select uren.uur, > -> count(messages.msg_date_time) > -> from uren left join messages > -> on hour(messages.msg_date_time) = uren.uur where messages.user_id = > 'almar' group by uren.uur > -> ; I'm not sure it will solve your problem, but try this: select uren.uur, count(messages.msg_date_time) from uren left join messages on hour(messages.msg_date_time) = uren.uur and messages.user_id = 'almar' group by uren.uur -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ignoring in ORDER
Hi, Can Mysql ignore alphabet , like 'the' when ORDERING a name. A sample : I have a table (first_name) : - The test. - Falcon - The Armageddon - Battle When ordering it will be : Battle Falcon The Armageddon The test. Can it be like this : The Armageddon Battle Falcon The test. Is that possible? __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: I must ask.
Bob, > > There are some RDBMS-es that implement SQL to the point of apparently setting up a >construct that links tables - > > foreign key based data integrity being the first example that springs to (my) >mind. However relational logic > > suggests that the way to set up a linkage between tables is to show this in >dynamic logic - your SQL statement, > > eg > > > > SELECT * > > FROM tbl1, tbl2 > > WHERE tbl1.PrimaryKey = tbl2.ForeignKey; > > > > will produce a result-row for every intersection 'match' between the two tables. > > > > One of the MAJOR advances of relational databases over their predecessors was this >move 'away' from structural > > 'connections'. However these have been maintained as 'features' by some, usually >to promote speed/efficiency. First off, I'll happily discuss this with you, if you want. I won't argue because we'll end up agreeing! At issue in the original conversation was not database theory, but re-training a person's thinking. The discussion was for the benefit of a Portugese guy who was protesting his abilities in English - a tall task for the explanation of technical philosophies or complex theory! You seem to have a background involving an overview/theoretical study of databases - like myself (first at Uni, and later falling victim to various IBM and Oracle training courses). You and I can chat about referential integrity, constraints, and the differences between them and various types of joins; but it's fairly rarified air up there! It is my observaton that many people coming to MySQL/this list, are 'graduating' from Access, FileMaker, Paradox, dBase-derivatives and suchlike (largely/originally) 'personal' systems. Consequently when they ask questions like: 'I can define two tables in SQL, but where is the 'line' that shows me how they relate to each other', the answer needs to be first related back to such a tool/the person's background knowledge; and then brought (sometimes kicking and screaming) 'forward' into the brave new world of unadulterated SQL/RDBMS. > Speed/efficiency is a minor issue. Not true the guy is into gaming and perceived response time is a major issue to gamers. On the more mainstream RDBMS front, see elsewhere (here or perhaps PHP-DB list) for a discussion about how Google searches appear to be so many order of magnitude times faster than MySQL fulltext retrievals (gripe, gripe). Within RDBMS practice, about the ONLY reasonable justification for de-normalisation is speed. Secondly, the reason why a number of applications don't use RDBMS is still speed - back when we were working with indexed files, ISAM and VSAM, IDMS, etc, it was a long-held belief (and an IBM mantra, for example) that one used 'efficient' file systems for transaction processing systems, and 'copied over' the data captured there (perhaps nightly) to a DB2/SQL database for 'inefficient'/expensive "user access". To me this is one of the grand attractions of MySQL - that by being able to strip away many of the non-aerodynamic features of RDBMSes (as are unnecessary to the particular application), you can achieve raw speed - and that when you must have referential-integrity (for example), you can ask the RDBMS to 'activate' such a facility. (now if I could just get Views...) Integrity constraints are used to > prevent people from writing queries that violate relational integrity > rules; e.g. create orphan records. I have to admit, I really did gloss over this - intending "efficiency" of the database application to include the dislocation of orphans, and ending up displaying an incomplete response to a query because of such, etc, etc. And integrity constraints are > unrelated to joins, except in the most incidental way. I concur - in SQL. However in a bid to 'update' their products, others use the term to define a fixed/permanent relationship between the tables, that goes beyond the logical because one can presume the join and talk about the 'second table' without formally mentioning its name - as you would in a SQL query. When such users graduate to SQL they ask questions like "how do I 'set' the join", and/or, the other way around "I joined these tables 'last time', but now they're not joined any more". I dispute the "incidental" part. The relationship of a FOREIGN KEY back to the 'main' table IS a stated relationship/join between the tables. It is not 'exclusive' to be sure, but if you think it worthwhile doing, then MOST of the time that will be where/how the two tables are joined when they are used together... It is possible > to use them to create joins, but that's not why they're built into > databases. Absolutely, and that's the way it should be - that's the way CODASYL wrote it - but as we all know, the problem with SQL is that it is not a restricted/regulated standard, and when moving from one SQL to another it is not the 'standard' bits that are the problem, but the extras and 'add-ons'. That was the primary issue (as I r
Is it a bug / not implemented /me ?
Hi, This morning is posted a message : --- I have a question about a query. We have a guestbook and I want to do some statistics for the messages in it. So I've created a query that looks like this. select hour(msg_date_time), count(*) from messages where user_id = 'almar' group by hour(msg_date_time) It returns the hour and the number of messages posted in that hour. However, there are hours where no messages have been posted, so the result would look like this: hour msg_count 0 10 1 15 3 6 So in the example between 2 a clock and 3 a clock no messages have been posted. But I do want it to return 0 for hour 2. I know that my query will never return that result. We are using perl DBI. Does anyone have an idea? -- Ok i've got some great reactions on it, however when testing it, it does not seam to work correct. Below i've made a dump. As you can see, it doesn't touch the index on te user_id, wich works fine in other query's. That's why it takes 20 seconds to do the query. (0,01 second in all the other query's, without the join) And second of all it does not use the all fields in the table uren (wich stands for hours) as you can see in the output below the hour 3 is missing, mysql> explain -> select uren.uur, -> count(messages.msg_date_time) -> from uren left join messages -> on hour(messages.msg_date_time) = uren.uur where messages.user_id = 'almar' group by uren.uur -> ; +--+---+---+---+-+--++-- + | table| type | possible_keys | key | key_len | ref | rows | Extra| +--+---+---+---+-+--++-- + | uren | index | NULL | PRIMARY | 4 | NULL | 24 | Using index; Using temporary | | messages | index | NULL | date_time | 16 | NULL | 237765 | where used; Using index | +--+---+---+---+-+--++-- + 2 rows in set (0.00 sec) mysql> select uren.uur, -> count(messages.msg_date_time) -> from uren left join messages -> on hour(messages.msg_date_time) = uren.uur where messages.user_id = 'almar' group by uren.uur -> ; +-+---+ | uur | count(messages.msg_date_time) | +-+---+ | 0 | 4 | | 1 | 5 | | 2 | 3 | | 4 | 1 | | 5 | 1 | | 6 | 1 | | 7 | 2 | | 8 | 4 | | 9 | 8 | | 10 |12 | | 11 |15 | | 12 |17 | | 13 |15 | | 14 |17 | | 15 |15 | | 16 |21 | | 17 |19 | | 18 |17 | | 19 |26 | | 20 |19 | | 21 |22 | | 22 |14 | | 23 |18 | +-+---+ 23 rows in set (20.84 sec) Who can help me out on this one? Or is it not implemented, the way i use it? Thanks in advance Regards, Almar van Pel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Auto Increment field duplicate key ...
Just for further puzzlement -- I've run 'check table extended' on the table, dropped the table and reloaded it, shut down the server and restarted it ... all to the same effect. First insert to the table is the 214... integer limit then duplicate key errors. On Sat, Feb 09, 2002 at 11:18:08AM -0600, Hank Marquardt wrote: > I moved a database from one server to another (mysqldump -> mysql dbname > < filefromump) ... and on the new server one table that has an auto > increment index fails with duplicate keys after the first insert ... the > first insert as an integer bound value (2147483647) rather than using > the established sequence (which was at 512 on the old server) ... how > can I fix this (and what caused it) ... if I 'describe' the table it > shows properly as an auto_increment integer. > > I did a couple quick searches but didn't find anyone with the same > problem ... weird. Any and all help appreciated. > > -- > Hank Marquardt <[EMAIL PROTECTED]> > http://web.yerpso.net > GPG Id: 2BB5E60C > Fingerprint: D807 61BC FD18 370A AC1D 3EDF 2BF9 8A2D 2BB5 E60C > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > -- Hank Marquardt <[EMAIL PROTECTED]> http://web.yerpso.net GPG Id: 2BB5E60C Fingerprint: D807 61BC FD18 370A AC1D 3EDF 2BF9 8A2D 2BB5 E60C *** Web Development: PHP, MySQL/PgSQL - Network Admin: Debian/FreeBSD *** PHP Instructor - Intnl. Webmasters Assn./HTML Writers Guild *** Beginning PHP -- Starts January 7, 2002 *** See http://www.hwg.org/services/classes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Avanced query question
* DL Neil > Consider: > a) add a 'construction' table with the row-values 0 through 11 > (or 23) and use it to do a left join against the > messages tbl; > b) if the database is not busy, use perl to fire off 12 (or 24) > separate queries. c) use perl to 'fill in the holes', putting zero values in the 'empty slots' of an array 0-23 -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: is possible 1 query with 2 selects?
Hi,
Ok. Now I don't get syntax error but I don't get any results.
Anyway, I think that it is something like your examples.
Again, I need to get this results with only one query:
1st: get results from relAB to see QB relations:
SELECT B from relAB WHERE A='a1';
results:
+--+
| B|
+--+
| b1 |
| b2 |
| b3 |
+--+
(This results said that only QB b1, b2 and b3 refs are related to 'a1')
2nd: get only b1, b2, b3 from QB:
SELECT * FROM QB WHERE ref in ('b1','b2','b3');
results:
++--+--+
| id | ref | question |
++--+--+
| 1 | b1 | qb1 |
| 2 | b2 | qb2 |
| 3 | b3 | qb3 |
++--+--+
I'm trying to adapt your examples to see if I can get it to work.
Thanks again,
Nuno Teixeira
On Sat, Feb 09, 2002 at 12:16:40PM -0500, Butch Bean wrote:
| Nuno,
|
| I think these examples will work OK for what you want...
|
| Butch
|
| SELECT QB.id, QB.ref, QB.question from QB, relAB
| WHERE QB.ref=relAB.B
| AND relAB.B='a1'
|
| SELECT DISTINCT QB.id, QB.ref, QB.question from QB, relAB
| WHERE QB.ref=relAB.B
| AND relAB.B='a1'
|
| SELECT * from QB as tbl1
| LEFT JOIN relAB as tbl2
| ON tbl2.B=tbl1.ref
| where tbl2.B='a1'
|
|
| -Original Message-
| From: Nuno Teixeira [mailto:[EMAIL PROTECTED]]
| Sent: Saturday, February 09, 2002 11:18 AM
| To: Butch Bean
| Cc: [EMAIL PROTECTED]
| Subject: Re: is possible 1 query with 2 selects?
|
|
|
| Hi,
|
| I try it and I get a syntax error:
|
| "You have an error in your SQL syntax near 'on tbl2.A=tbl1.A where
| tbl2.A='a1'' at line 1"
|
| My tables structure are:
|
| show fields from QB;
| +--+-+--+-+-++
| | Field| Type| Null | Key | Default | Extra |
| +--+-+--+-+-++
| | id | int(11) | | PRI | NULL| auto_increment |
| | ref | char(3) | YES | | NULL||
| | question | varchar(30) | YES | | NULL||
| +--+-+--+-+-++
|
| show fields from relAB;
| +---+-+--+-+-++
| | Field | Type| Null | Key | Default | Extra |
| +---+-+--+-+-++
| | id| int(11) | | PRI | NULL| auto_increment |
| | A | char(3) | YES | | NULL||
| | B | char(3) | YES | | NULL||
| +---+-+--+-+-++
|
|
| Thanks for your help,
|
| Nuno Teixeira
|
| On Sat, Feb 09, 2002 at 08:49:44AM -0500, Butch Bean wrote:
| | I don't know you table structure but something like this would do it...
| |
| | SELECT * from QB as tbl1
| | JOIN relAB as tbl2
| | ON tbl2.A=tbl1.A
| | where tbl2.a='a1'
| |
| | Butch Bean
| |
| | -Original Message-
| | From: Egor Egorov [mailto:[EMAIL PROTECTED]]
| | Sent: Saturday, February 09, 2002 7:24 AM
| | To: [EMAIL PROTECTED]
| | Subject: is possible 1 query with 2 selects?
| |
| |
| | Nuno,
| |
| | Friday, February 08, 2002, 11:04:51 PM, you wrote:
| |
| |
| | NT> Hello to all,
| |
| | NT> I'm trying to make a query with only one command but I can't see how
| | to do
| | NT> it.
| |
| | [skip]
| |
| | NT> What I want to do is get a list from table QB related to an item
| from
| | NT> table QA:
| |
| | NT> For example, get a list from QB related to item 'a1':
| |
| | NT> 1. select B from relAB where A='a1';
| |
| | NT> (result: b2, b3, b4)
| |
| | NT> 2. select * from QB where ref in ('b2','b3','b4');
| |
| | NT> (result: only 'a1' related items)
| |
| | NT> There is a way of make this query with only one command?
| |
| | NT> Something like:
| |
| | NT> 1. select * from QB where ref in (select B from relAB where
| | A='a1');
| |
| | MySQL doesn't curently support sub-selects, look at:
| | http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html
| |
| | It is in our plans to support sub-selects in future:
| | http://www.mysql.com/doc/T/O/TODO_MySQL_4.1.html
| |
| | NT> Please help me in this matter.
| | NT> Thanks very much,
| |
| |
| |
| |
| |
| | --
| | For technical support contracts, goto https://order.mysql.com/
| | This email is sponsored by Ensita.net http://www.ensita.net/
| |__ ___ ___ __
| | / |/ /_ __/ __/ __ \/ /Egor Egorov
| | / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
| | /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
| |<___/ www.mysql.com
| |
| |
| |
| | -
| | Before posting, please check:
| |http://www.mysql.com/manual.php (the manual)
| |
Re: Hello! Date question....
On Friday 08 February 2002 19:28, Eric Mayers wrote: > Jim, > > What you're asking about is the "timestamp" data type. > > Look at : http://www.mysql.com/doc/D/A/DATETIME.html > > In a nutshell, if you assign 'null' to a timestamp column it will put in > value of 'NOW()'. > > Eric Mayers > Software Engineer Hi Eric, This was what I was looking for, thanks! JIM -- Jim Hatridge Linux User #88484 -- BayerWulf Linux System # 129656 The Recycled Beowulf Project Looking for throw-away or obsolete computers and parts to recycle into a Linux super computer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Auto Increment field duplicate key ...
I moved a database from one server to another (mysqldump -> mysql dbname < filefromump) ... and on the new server one table that has an auto increment index fails with duplicate keys after the first insert ... the first insert as an integer bound value (2147483647) rather than using the established sequence (which was at 512 on the old server) ... how can I fix this (and what caused it) ... if I 'describe' the table it shows properly as an auto_increment integer. I did a couple quick searches but didn't find anyone with the same problem ... weird. Any and all help appreciated. -- Hank Marquardt <[EMAIL PROTECTED]> http://web.yerpso.net GPG Id: 2BB5E60C Fingerprint: D807 61BC FD18 370A AC1D 3EDF 2BF9 8A2D 2BB5 E60C - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: is possible 1 query with 2 selects?
Nuno,
I think these examples will work OK for what you want...
Butch
SELECT QB.id, QB.ref, QB.question from QB, relAB
WHERE QB.ref=relAB.B
AND relAB.B='a1'
SELECT DISTINCT QB.id, QB.ref, QB.question from QB, relAB
WHERE QB.ref=relAB.B
AND relAB.B='a1'
SELECT * from QB as tbl1
LEFT JOIN relAB as tbl2
ON tbl2.B=tbl1.ref
where tbl2.B='a1'
-Original Message-
From: Nuno Teixeira [mailto:[EMAIL PROTECTED]]
Sent: Saturday, February 09, 2002 11:18 AM
To: Butch Bean
Cc: [EMAIL PROTECTED]
Subject: Re: is possible 1 query with 2 selects?
Hi,
I try it and I get a syntax error:
"You have an error in your SQL syntax near 'on tbl2.A=tbl1.A where
tbl2.A='a1'' at line 1"
My tables structure are:
show fields from QB;
+--+-+--+-+-++
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-++
| id | int(11) | | PRI | NULL| auto_increment |
| ref | char(3) | YES | | NULL||
| question | varchar(30) | YES | | NULL||
+--+-+--+-+-++
show fields from relAB;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| id| int(11) | | PRI | NULL| auto_increment |
| A | char(3) | YES | | NULL||
| B | char(3) | YES | | NULL||
+---+-+--+-+-++
Thanks for your help,
Nuno Teixeira
On Sat, Feb 09, 2002 at 08:49:44AM -0500, Butch Bean wrote:
| I don't know you table structure but something like this would do it...
|
| SELECT * from QB as tbl1
| JOIN relAB as tbl2
| ON tbl2.A=tbl1.A
| where tbl2.a='a1'
|
| Butch Bean
|
| -Original Message-
| From: Egor Egorov [mailto:[EMAIL PROTECTED]]
| Sent: Saturday, February 09, 2002 7:24 AM
| To: [EMAIL PROTECTED]
| Subject: is possible 1 query with 2 selects?
|
|
| Nuno,
|
| Friday, February 08, 2002, 11:04:51 PM, you wrote:
|
|
| NT> Hello to all,
|
| NT> I'm trying to make a query with only one command but I can't see how
| to do
| NT> it.
|
| [skip]
|
| NT> What I want to do is get a list from table QB related to an item
from
| NT> table QA:
|
| NT> For example, get a list from QB related to item 'a1':
|
| NT> 1. select B from relAB where A='a1';
|
| NT> (result: b2, b3, b4)
|
| NT> 2. select * from QB where ref in ('b2','b3','b4');
|
| NT> (result: only 'a1' related items)
|
| NT> There is a way of make this query with only one command?
|
| NT> Something like:
|
| NT> 1. select * from QB where ref in (select B from relAB where
| A='a1');
|
| MySQL doesn't curently support sub-selects, look at:
| http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html
|
| It is in our plans to support sub-selects in future:
| http://www.mysql.com/doc/T/O/TODO_MySQL_4.1.html
|
| NT> Please help me in this matter.
| NT> Thanks very much,
|
|
|
|
|
| --
| For technical support contracts, goto https://order.mysql.com/
| This email is sponsored by Ensita.net http://www.ensita.net/
|__ ___ ___ __
| / |/ /_ __/ __/ __ \/ /Egor Egorov
| / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
| /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
|<___/ www.mysql.com
|
|
|
| -
| Before posting, please check:
|http://www.mysql.com/manual.php (the manual)
|http://lists.mysql.com/ (the list archive)
|
| To request this thread, e-mail <[EMAIL PROTECTED]>
| To unsubscribe, e-mail
| <[EMAIL PROTECTED]>
| Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
|
|
|
| -
| Before posting, please check:
|http://www.mysql.com/manual.php (the manual)
|http://lists.mysql.com/ (the list archive)
|
| To request this thread, e-mail <[EMAIL PROTECTED]>
| To unsubscribe, e-mail
<[EMAIL PROTECTED]>
| Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
|
--
Nuno Teixeira
pt-quorum.com
/*
PGP Public Key:
http://www.pt-quorum.com/pgp/nunoteixeira.asc
Key fingerprint:
8C2C B364 D4DC 0C92 56F5 CE6F 8F07 720A 63A0 4FC7
*/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---
Re: Avanced query question
Hello Almar > I have a question about a query. We have a guestbook and I want to do some > statistics for the messages in it. So I've created a query that looks like > this. > > select hour(msg_date_time), count(*) > from messages > where user_id = 'almar' > group by hour(msg_date_time) > > It returns the hour and the number of messages posted in that hour. However, > there are hours where no messages have been posted, so the result would look > like this: > > hour msg_count > 0 10 > 1 15 > 3 6 > > So in the example between 2 a clock and 3 a clock no messages have been > posted. But I do want it to return 0 for hour 2. I know that my query will > never return that result. We are using perl DBI. Does anyone have an idea? Data cannot be retrieved from the database, if it has not first been stored there! Your mind holds a firm relationship between a particular time and the number of hours in a day - the computer does not (appear) to have the latter information. Consider: a) add a 'construction' table with the row-values 0 through 11 (or 23) and use it to do a left join against the messages tbl; b) if the database is not busy, use perl to fire off 12 (or 24) separate queries. Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: is possible 1 query with 2 selects?
Hi,
I try it and I get a syntax error:
"You have an error in your SQL syntax near 'on tbl2.A=tbl1.A where
tbl2.A='a1'' at line 1"
My tables structure are:
show fields from QB;
+--+-+--+-+-++
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-++
| id | int(11) | | PRI | NULL| auto_increment |
| ref | char(3) | YES | | NULL||
| question | varchar(30) | YES | | NULL||
+--+-+--+-+-++
show fields from relAB;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| id| int(11) | | PRI | NULL| auto_increment |
| A | char(3) | YES | | NULL||
| B | char(3) | YES | | NULL||
+---+-+--+-+-++
Thanks for your help,
Nuno Teixeira
On Sat, Feb 09, 2002 at 08:49:44AM -0500, Butch Bean wrote:
| I don't know you table structure but something like this would do it...
|
| SELECT * from QB as tbl1
| JOIN relAB as tbl2
| ON tbl2.A=tbl1.A
| where tbl2.a='a1'
|
| Butch Bean
|
| -Original Message-
| From: Egor Egorov [mailto:[EMAIL PROTECTED]]
| Sent: Saturday, February 09, 2002 7:24 AM
| To: [EMAIL PROTECTED]
| Subject: is possible 1 query with 2 selects?
|
|
| Nuno,
|
| Friday, February 08, 2002, 11:04:51 PM, you wrote:
|
|
| NT> Hello to all,
|
| NT> I'm trying to make a query with only one command but I can't see how
| to do
| NT> it.
|
| [skip]
|
| NT> What I want to do is get a list from table QB related to an item from
| NT> table QA:
|
| NT> For example, get a list from QB related to item 'a1':
|
| NT> 1. select B from relAB where A='a1';
|
| NT> (result: b2, b3, b4)
|
| NT> 2. select * from QB where ref in ('b2','b3','b4');
|
| NT> (result: only 'a1' related items)
|
| NT> There is a way of make this query with only one command?
|
| NT> Something like:
|
| NT> 1. select * from QB where ref in (select B from relAB where
| A='a1');
|
| MySQL doesn't curently support sub-selects, look at:
| http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html
|
| It is in our plans to support sub-selects in future:
| http://www.mysql.com/doc/T/O/TODO_MySQL_4.1.html
|
| NT> Please help me in this matter.
| NT> Thanks very much,
|
|
|
|
|
| --
| For technical support contracts, goto https://order.mysql.com/
| This email is sponsored by Ensita.net http://www.ensita.net/
|__ ___ ___ __
| / |/ /_ __/ __/ __ \/ /Egor Egorov
| / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
| /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
|<___/ www.mysql.com
|
|
|
| -
| Before posting, please check:
|http://www.mysql.com/manual.php (the manual)
|http://lists.mysql.com/ (the list archive)
|
| To request this thread, e-mail <[EMAIL PROTECTED]>
| To unsubscribe, e-mail
| <[EMAIL PROTECTED]>
| Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
|
|
|
| -
| Before posting, please check:
|http://www.mysql.com/manual.php (the manual)
|http://lists.mysql.com/ (the list archive)
|
| To request this thread, e-mail <[EMAIL PROTECTED]>
| To unsubscribe, e-mail
|<[EMAIL PROTECTED]>
| Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
|
--
Nuno Teixeira
pt-quorum.com
/*
PGP Public Key:
http://www.pt-quorum.com/pgp/nunoteixeira.asc
Key fingerprint:
8C2C B364 D4DC 0C92 56F5 CE6F 8F07 720A 63A0 4FC7
*/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
OSCON 2002: Call for Participation
Hello All, Later this year, O'Reilly will be hosting the fourth annual Open Source Conference. In preparation for the conference, they have asked us to issue a request for proposals for conference talks and tutorials. We know that there are a lot of people out there doing a lot of interesting and exceptional things with MySQL. If you have a great idea for a presentation or tutorial on some facet of MySQL (or another Open Source topic, as well) please propose the idea to the OSCON conference board. Visit http://conferences.oreillynet.com/os2002/ to find out more about the conference and to make your proposal. Also, if you have a suggestion for presentations and tutorials that you would like to see presented, please write to [EMAIL PROTECTED] As the work on the MySQL track at the conference develops, we will put up a web-page that provides more information on what to expect at the conference. Stay tuned for details! Thank You! Yours Truly, Zak Greant -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Zak Greant <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Advocate /_/ /_/\_, /___/\___\_\___/ Calgary, Canada <___/ www.mysql.com 403.244.7213 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
HELP: Binary Installation
Hi, I had successfull installed MySQL on RedHat 6.1 (2.2.12-20) for a long time but recently there was a power shutdown and in the next bootup I am unable to bring the deamon. I unstalled MySQL and trying to reinstall the new one but its going nowhere. [root@ktwo mysql]# rpm -i MySQL-3_23_47-1_i386.rpm MySQL-client-3_23_47-1_i386.rpm Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables Also I tried with binary distribution but getting the same error. I am trying for last 2 days. Any help. Thanks. JG - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: is possible 1 query with 2 selects?
I don't know you table structure but something like this would do it...
SELECT * from QB as tbl1
JOIN relAB as tbl2
ON tbl2.A=tbl1.A
where tbl2.a='a1'
Butch Bean
-Original Message-
From: Egor Egorov [mailto:[EMAIL PROTECTED]]
Sent: Saturday, February 09, 2002 7:24 AM
To: [EMAIL PROTECTED]
Subject: is possible 1 query with 2 selects?
Nuno,
Friday, February 08, 2002, 11:04:51 PM, you wrote:
NT> Hello to all,
NT> I'm trying to make a query with only one command but I can't see how
to do
NT> it.
[skip]
NT> What I want to do is get a list from table QB related to an item from
NT> table QA:
NT> For example, get a list from QB related to item 'a1':
NT> 1. select B from relAB where A='a1';
NT> (result: b2, b3, b4)
NT> 2. select * from QB where ref in ('b2','b3','b4');
NT> (result: only 'a1' related items)
NT> There is a way of make this query with only one command?
NT> Something like:
NT> 1. select * from QB where ref in (select B from relAB where
A='a1');
MySQL doesn't curently support sub-selects, look at:
http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html
It is in our plans to support sub-selects in future:
http://www.mysql.com/doc/T/O/TODO_MySQL_4.1.html
NT> Please help me in this matter.
NT> Thanks very much,
--
For technical support contracts, goto https://order.mysql.com/
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: a LIKE problem ecc.
Use SELECT * FROM Table1 WHERE BINARY name LIKE "K%" It will probably slow the search... You can also define the table a BINARY but 'Most' will be different than 'most' Butch Bean -Original Message- From: savaidis [mailto:[EMAIL PROTECTED]] Sent: Saturday, February 09, 2002 3:07 AM To: MySQL list (E-mail) Subject: a LIKE problem ecc. Hi! I use a query : SELECT * FROM Table1 WHERE name LIKE "K%" (K = Greek char = ASCII 137) but it selects also "k" (small K Greek) and "L" (Greek , ASCII 138) Why so and what I have to do? Something is in Apache setup? Also is there a way to print a counter at first row intead of ID (autoincrement) that shows the increment of each record displayed? (1,2,3, ecc) Also something strange with Apache (on WIN98) I have a "root" directory for root of "localhost" and "Php" directory on it for testing various php files. I changed "Php" to "php" becouse the browsers IE and Netscape did NOT recognize the capital "P" (!!!) and now even I reboot it continues to accept only "Php" as valid directory. I change it to "Php1" and still see only the old "Php". What happens? Thanks Makis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
is possible 1 query with 2 selects?
Nuno,
Friday, February 08, 2002, 11:04:51 PM, you wrote:
NT> Hello to all,
NT> I'm trying to make a query with only one command but I can't see how to do
NT> it.
[skip]
NT> What I want to do is get a list from table QB related to an item from
NT> table QA:
NT> For example, get a list from QB related to item 'a1':
NT> 1. select B from relAB where A='a1';
NT> (result: b2, b3, b4)
NT> 2. select * from QB where ref in ('b2','b3','b4');
NT> (result: only 'a1' related items)
NT> There is a way of make this query with only one command?
NT> Something like:
NT> 1. select * from QB where ref in (select B from relAB where A='a1');
MySQL doesn't curently support sub-selects, look at:
http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html
It is in our plans to support sub-selects in future:
http://www.mysql.com/doc/T/O/TODO_MySQL_4.1.html
NT> Please help me in this matter.
NT> Thanks very much,
--
For technical support contracts, goto https://order.mysql.com/
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table appears in multiple joins returns null column
Try to delete the index (drop index) of the table! I had the problem and solved this this way. regards, Andreas On Samstag, Februar 9, 2002, at 11:41 Uhr, DL Neil wrote: > Keith, > >> I have the below query that returns the correct records but the >> POINTFUND.description field is all NULL while it has values in the >> table. >> Any idea why? Thanks. >> >> SELECT >> >> RACES.RACE_ID, >> RACES.sequence, >> TYPES.typelong, >> SCHEDULE.event, >> SCHEDULE.date, >> PARTY.lname, >> POINTFUND.description >> >> FROM >> >> TYPES LEFT JOIN RACES ON RACES.TYPE_ID = TYPES.TYPE_ID >> LEFT JOIN POINTFUND ON POINTFUND.TYPE_ID = TYPES.TYPE_ID, >> SCHEDULE, >> PARTY >> >> WHERE >> >> RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND >> PARTY.PARTY_ID = SCHEDULE.TRACK_ID >> >> ORDER BY >> >> SCHEDULE.date, PARTY.lname > > > Obviously the syntax of the SQL command is ok, else the RDBMS would > have told you. The answer may lie in the > definitions of the races, types, and pointfund tables; or in the data > contained in the join-ing fields (neither > of which are shown here). > > Two suggestions: > 1 simplify the query to deal with pointfund and one other first, then > add another tbl/join, etc, until you have > rebuilt the entire query - your formatting is 'clean' and allows the > insertion of # comment symbols at the > beginning of lines to remove/return then easily (and without loads of > typing/retyping) > 2 have you tried running EXPLAIN to see what MySQL thinks of it all? > > Let us know how you get on! > =dn > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail [EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Andreas Habereder Kirchenstr. 17e 81675 München private: [EMAIL PROTECTED] fax: +49 1212 5 107 37 317 mobile: +49 172 838 7771 - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error Msg on Connect
Paul, Saturday, February 09, 2002, 1:14:56 AM, you wrote: PC> Can't connect to local MySQl server through port 111 Please, check the following link: http://www.mysql.com/doc/C/a/Can_not_connect_to_server.html -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL won't start...
FISHER, Friday, February 08, 2002, 9:01:10 PM, you wrote: Feoen> I have 2 SuSE Linux 7.2 machines at home... These boxes Feoen> have completely different hardware in each... But the Feoen> O/S on each, contains virtually all of Feoen> the same applications... Feoen> On the first machine, MySQL started right up, and has Feoen> been working correctly ever since... Feoen> On the second machine, MySQL will NOT start... Feoen> It tries to start, but keeps aborting... Feoen> Here are the only messages that I can seem to find: Feoen> 020126 18:01:29 mysqld started Feoen> 020126 18:01:29 /usr/sbin/mysqld: Feoen> Table 'mysql.host' doesn't exist Feoen> 020126 18:01:29 mysqld ended Feoen> 020126 18:04:56 mysqld started Feoen> 020126 18:04:57 /usr/sbin/mysqld: Can't find Feoen> file: Feoen> './mysql/host.frm' (errno: 13) Feoen> Each time I try to restart, I get the above messages... Feoen> If anyone can point me to some specific log files, that Feoen> would help to debug this problem further, I would much Feoen> appreciate it... Feoen> my.cnf is locate in /etc, as it's supposed to be... Feoen> host.frm exists, but it exists in the following Feoen> location: (I'm thinking that Feoen> something in /usr/sbin/mysqld isn't pointing to the Feoen> file...) Feoen> /var/lib/mysql/mysql/host.frm Feoen> Is there a configuration file someplace, that I can Feoen> modify to point to this host.frm file? Feoen> If so, what is the correct command, including syntax? $ perror 13 Error code 13: Permission denied Does your "user" have permission to read, write and execute those dirs and files? Feoen> TIA Feoen> Joe F. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Checking the Table Size
Satish, Saturday, February 09, 2002, 12:21:06 AM, you wrote: SS> Hi guys, SS> I am not able to insert into the MySQL database SS> anymore. I think it has reached the table size. How do SS> i check the size to see if it has reached the table SS> size? Also let me know how to increase the table size SS> in case it is reached the limit. You can check table size using SHOW TABLE STATUS. Look at: http://www.mysql.com/doc/S/H/SHOW_TABLE_STATUS.html The maximum table size depends on your operation system. See at: http://www.mysql.com/doc/T/a/Table_size.html SS> Thanks, SS> Satish -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB File Size
Hi! I just tried with 3.23.48 on Linux-2.4.16-SMP-64GB (ext2 I think), and a 5000 MB data file was created. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com -Original Message- From: Gurupartap Davis <[EMAIL PROTECTED]> To: Heikki Tuuri <[EMAIL PROTECTED]>; [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Saturday, February 09, 2002 2:32 AM Subject: Re: InnoDB File Size >Yes, I saw that before...My filesize was limited to 2GB before, but now with >a 2.4 kernel and reiserfs I _should_ be allowed to create a 20GB data file >now, right? > >So something's wrong... as I said before, I have successfully created a 9GB >file on this machine before, so the filesystem isn't to blame for a 4GB >limitation... > >> >From http://www.innodb.com/ibman.html : >> >> >> MySQL/InnoDB-3.23.44, November 2, 2001 >> >> You can define foreign key constraints on InnoDB tables. An example: >FOREIGN >> KEY (col1) REFERENCES table2(col2). >> You can create > 4 GB data files in those file systems that allow it. >> >> >> Thus > 4 G files should be available also on 4.0.1. >> >> Best regards, >> >> Heikki Tuuri >> Innobase Oy > >> "Gurupartap Davis" wrote in message ... >> >I'm using MySQL 4.0, trying to set up some innodb tables with several >20GB >> >data files, >> >but mysql is telling me that the files must be <= 4096MB >> > >> >This is on a RedHat 7.2 box with redhat's 2.4.9-13 enterprise kernel on a >> >reiserfs filesystem... >> > >> >I don't think there should be such a low limit...I accidentally made a >9GB >> >log file the other day. >> > >> >Is this a limit of innodb? Is there somewhere I can adjust this limit? >> > >> >Thanks >> >-Partap Davis >> >Syncrasy, LLC > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table appears in multiple joins returns null column
Keith, > I have the below query that returns the correct records but the > POINTFUND.description field is all NULL while it has values in the table. > Any idea why? Thanks. > > SELECT > > RACES.RACE_ID, > RACES.sequence, > TYPES.typelong, > SCHEDULE.event, > SCHEDULE.date, > PARTY.lname, > POINTFUND.description > > FROM > > TYPES LEFT JOIN RACES ON RACES.TYPE_ID = TYPES.TYPE_ID > LEFT JOIN POINTFUND ON POINTFUND.TYPE_ID = TYPES.TYPE_ID, > SCHEDULE, > PARTY > > WHERE > > RACES.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID AND > PARTY.PARTY_ID = SCHEDULE.TRACK_ID > > ORDER BY > > SCHEDULE.date, PARTY.lname Obviously the syntax of the SQL command is ok, else the RDBMS would have told you. The answer may lie in the definitions of the races, types, and pointfund tables; or in the data contained in the join-ing fields (neither of which are shown here). Two suggestions: 1 simplify the query to deal with pointfund and one other first, then add another tbl/join, etc, until you have rebuilt the entire query - your formatting is 'clean' and allows the insertion of # comment symbols at the beginning of lines to remove/return then easily (and without loads of typing/retyping) 2 have you tried running EXPLAIN to see what MySQL thinks of it all? Let us know how you get on! =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Avanced query question
Hello, I have a question about a query. We have a guestbook and I want to do some statistics for the messages in it. So I've created a query that looks like this. select hour(msg_date_time), count(*) from messages where user_id = 'almar' group by hour(msg_date_time) It returns the hour and the number of messages posted in that hour. However, there are hours where no messages have been posted, so the result would look like this: hour msg_count 0 10 1 15 3 6 So in the example between 2 a clock and 3 a clock no messages have been posted. But I do want it to return 0 for hour 2. I know that my query will never return that result. We are using perl DBI. Does anyone have an idea? Kind regards Almar van Pel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
COUNT --- GROUP BY --- LEFT JOIN
I am attempting to combine COUNT, GROUP BY, and LEFT JOIN.. Adopting the theme on http://www.mysql.com/doc/C/o/Counting_rows.html my goal is to display only species/sex combinations that occur more than once. Additionally I wish to include the count for each row, and some keyed field from another table.. So I added a separate table "owner" containing foreign key "petID" and the field "zipcode".. A couple rows of output might look like (species=dog sex=male animalcount=3 zipcode=5) (species=dog sex=female animalcount=2 zipcode=5). There would be no rows with a count of 1, and zipcode can repeat.. The following statement unfortunately includes zipcode when determining uniqueness. It's as close as I've come! Any suggestions?: SELECT species, sex, zip, COUNT(*) as animalcount FROM pet LEFT JOIN owner ON pet.petID = owner.petID GROUP By species, sex HAVING animalcount > 1; __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
a LIKE problem ecc.
Hi! I use a query : SELECT * FROM Table1 WHERE name LIKE "K%" (K = Greek char = ASCII 137) but it selects also "k" (small K Greek) and "L" (Greek , ASCII 138) Why so and what I have to do? Something is in Apache setup? Also is there a way to print a counter at first row intead of ID (autoincrement) that shows the increment of each record displayed? (1,2,3, ecc) Also something strange with Apache (on WIN98) I have a "root" directory for root of "localhost" and "Php" directory on it for testing various php files. I changed "Php" to "php" becouse the browsers IE and Netscape did NOT recognize the capital "P" (!!!) and now even I reboot it continues to accept only "Php" as valid directory. I change it to "Php1" and still see only the old "Php". What happens? Thanks Makis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
