Re: MySQL 4.0.26 has been released
Just a note: the main site still reports version 4.0.25-0. I dowloaded version 4.0.26 from a mirror, but it was necessary to tweak the URL. Congratulations for this fantastic project, Diana Soares On 9/8/05, Joerg Bruehe <[EMAIL PROTECTED]> wrote: > Hi, > > MySQL 4.0.26, a new version of the popular Open Source/Free Software > Database Management System, has been released. It is now available in > source and binary form for a number of platforms from our download pages > at http://dev.mysql.com/downloads/ and mirror sites. > > Note that not all mirror sites may be up to date at this point in time - > if you can't find this version on some mirror, please try again later or > choose another download site. > > This is a bugfix release for the recent production version. > > Please refer to our bug database at http://bugs.mysql.com/ for more > details about the individual bugs fixed in this version. > > News from the ChangeLog: > > Functionality added or changed: > * Added the mysql_get_client_version C API function to the embedded >server library. (It was present in the regular client library but >inadvertently omitted from the embedded library.) >(Bug #10266 (http://bugs.mysql.com/10266)) > > Bugs fixed: > * An optimizer estimate of zero rows for a non-empty InnoDB table used >in a left or right join could cause incomplete rollback for the table. >(Bug #12779 (http://bugs.mysql.com/12779)) > * Query cache is switched off if a thread (connection) has tables >locked. This prevents invalid results where the locking thread inserts >values between a second thread connecting and selecting from the >table. (Bug #12385 (http://bugs.mysql.com/12385)) > * For PKG installs on Mac OS X, the preinstallation and postinstallation >scripts were being run only for new installations and not for upgrade >installations, resulting in an incomplete installation process. >(Bug #11380 (http://bugs.mysql.com/11380)) > * On Windows, applications that used the embedded server made it not >possible to remove certain files in the data directory, even after the >embedded server had been shut down. This occurred because a file >descriptor was being held open. >(Bug #12177 (http://bugs.mysql.com/12177)) > * Creation of the mysql group account failed during the RPM >installation. (Bug #12348 (http://bugs.mysql.com/12348)) > * Attempting to repair a table having a fulltext index on a column >containing words whose length exceeded 21 characters and where >myisam_repair_threads was greater than 1 would crash the server. (Bug >#11684 (http://bugs.mysql.com/11684)) > * When two threads compete for the same table, a deadlock could occur if >one thread has also a lock on another table through LOCK TABLES and >the thread is attempting to remove the table in some manner and the >other thread want locks on both tables. >(Bug #10600 (http://bugs.mysql.com/10600)) > > > Bye, > Joerg > > > > -- > 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: Suppression of result in SELECT @temp := column?
I didn't understand the problem very well, but maybe disabling the pager before that query where you set @temp, do the query, setting the pager to its default value and then do the "next statement to produce the actual result." \P cat /dev/null SELECT @temp := columnID FROM table WHERE column = whatever LIMIT 0,1 \n SELECT .... -- Diana Soares On Tue, 2004-08-31 at 23:00, Eamon Daly wrote: > Did anyone ever follow up on this question? I'm looking for > the answer, too. > > Eamon Daly > - Original Message - > From: <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, August 25, 2004 9:16 AM > Subject: Suppression of result in SELECT @temp := column? > > Is there any way to not sending the result of a user variable assignment > > to the client? > > I.e. Suppress the result of; > > > > SELECT @temp := columnID FROM table WHERE column = whatever LIMIT 0,1 > > > > ...since I only use @temp in my next statement to produce the actual > result. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: count() on multiple similar tables?
You may use MERGE TABLES: http://dev.mysql.com/doc/mysql/en/MERGE.html -- Diana Soares On Tue, 2004-05-04 at 10:08, Ken Gieselman wrote: > Heya folks -- > > Trying to come up with a way to count across multiple tables, and failing > miserably. I need a simple way, preferably better than looped queries, of > summing the number of rows in multiple tables. > > Example: > > I have multiple tables with the same column layout, due to the amount of data > expected to land in each one, broken up by year/month. This is simplified > some, but should serve to illustrate the issue. > > create table info_2004_03 ( > itemID integer auto_increment NOT NULL, > eventID integer NOT NULL, > eventNamechar(40), > primary key (itemID) > ); > > create table info_2004_04 ( > itemID integer auto_increment NOT NULL, > eventID integer NOT NULL, > eventNamechar(40), > primary key (itemID) > ); > > I need to get a total number of itemIDs over all the info_ tables. I could just > query each of the tables individually and add the results, but looking for a > more graceful way of doing it, hopefully. > > Thanks in advance! > > ken > === > "Diplomacy is the weapon of the Civilized Warrior" > - Hun, A.T. > > Ken Gieselman [EMAIL PROTECTED] > System Administratorhttp://www.endlessknot.com/~ken > Endlessknot Communications http://www.endlessknot.com > === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior with IF?
On Mon, 2004-02-16 at 09:07, Batara Kesuma wrote: > Hi, > > Can someone tell me why this query doesn't work? > > SELECT IF(ISNULL(network.level), 4, network.level) AS level, > member.photo_level > FROM member > LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) > ORDER BY member.last_login DESC > LIMIT 0,3 > > +---+-+ > | level | photo_level | > +---+-+ > | 4 | 4 | > | 4 | 4 | > | 4 | 4 | > +---+-+ > 3 rows in set (0.01 sec) > > Then, when I add WHERE. > > SELECT IF(ISNULL(network.level), 4, network.level) AS level, > member.photo_level > FROM member > LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id) > WHERE level <= member.photo_level > ORDER BY member.last_login DESC > LIMIT 0,3 > > Empty set (0.00 sec) > > I think it is supposed to return all rows, since all level is the same > as photo_level, but why does it return empty set? Conditions with fields from the table in the LEFT JOIN side (in this case, "network") should be in the ON clause, not in the WHERE clause. Check the manual about using LEFT JOIN and try: SELECT IF(ISNULL(network.level), 4, network.level) AS level, member.photo_level FROM member LEFT JOIN network ON (network.from_id=101 AND network.to_id=member.id AND network.level <= member.photo_level) ORDER BY member.last_login DESC LIMIT 0,3 -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Re: Outer join question]
Sorry, i meant "gender", not "genre".
-Forwarded Message-
First, you database design. You don't need to separate actresses from
actors... Why do that? They are the same entity, a person, with only one
different attribute: the genre. So, you should join them in one single
table:
Actors
==
act_id
name
genre ENUM('m','f')
Then, the table DVD. If we mantain things as they are, we would need one
entry in table DVD for each actor/actress in the movie, we would be
repeating the title N times... Whenever you change it, you would need to
change N records... And is redundant information.
So, let's take DVD as an entity by itself. DVD would be:
DVD
===
dvd_id
title
description
year
other_fields_related...
and then, you relate the 2 tables with this one:
DVD_Actors
==
dvd_id
act_id
leader ENUM('yes','no')
(the table name may not be the happiest :-p)
The field leading tells you if that actor is the leading one or not...
> > I want to select the title of each movie, along with the corresponding
> > leading actor and/or actress name, but a straightforward join will only
> > return those movie titles that have NOT NULL values in BOTH the acto_id
> > and actr_id fields in the DVD table.
With this design, you could use:
SELECT M.title, A.name, A.genre, DA.leader
FROM DVD AS M
LEFT JOIN DVD_Actors AS DA ON (M.dvd_id=DA.dvd_id)
LEFT JOIN Actors AS A ON (DA.act_id=A.act_id)
Please, read the manual about LEFT JOIN.
> > My grey-haired memory tells me that an outer join for both the actor table
> > and the actress table is the answer, in that the query will return all
> > titles *even if* one or both fields are NULL. (At least that was the case
> > when I was using Oracle!)
And you were right.
> > So, can somebody please correct the following query (and explain the
> > syntax) so that it will work please? (I haven't tried putting an outer
> > join in it because I don't understand the syntax.)
In this example, you are using inner join... please, read the manual
about JOINs.
> > Select
> > actr.name,
> > acto.name,
> > dvd.title
> > from
> > actresses actr,
> > actors acto,
> > dvd
> > where
> > actr.actr_id = dvd.actr_id
> > and
> > acto.acto_id = dvd.acto_id
> > order by dvd.title;
> >
> > (I used to put (+) at the end of the outer join line, but don't think this
> > will work in MYSQL - at least I don't see it in the manual.)
> >
> > Thanks in advance for your kind help and sorry for the wordy question!
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
First, you database design. You don't need to separate actresses from
actors... Why do that? They are the same entity, a person, with only one
different attribute: the genre. So, you should join them in one single
table:
Actors
==
act_id
name
genre ENUM('m','f')
Then, the table DVD. If we mantain things as they are, we would need one
entry in table DVD for each actor/actress in the movie, we would be
repeating the title N times... Whenever you change it, you would need to
change N records... And is redundant information.
So, let's take DVD as an entity by itself. DVD would be:
DVD
===
dvd_id
title
description
year
other_fields_related...
and then, you relate the 2 tables with this one:
DVD_Actors
==
dvd_id
act_id
leader ENUM('yes','no')
(the table name may not be the happiest :-p)
The field leading tells you if that actor is the leading one or not...
> > I want to select the title of each movie, along with the corresponding
> > leading actor and/or actress name, but a straightforward join will only
> > return those movie titles that have NOT NULL values in BOTH the acto_id
> > and actr_id fields in the DVD table.
With this design, you could use:
SELECT M.title, A.name, A.genre, DA.leader
FROM DVD AS M
LEFT JOIN DVD_Actors AS DA ON (M.dvd_id=DA.dvd_id)
LEFT JOIN Actors AS A ON (DA.act_id=A.act_id)
Please, read the manual about LEFT JOIN.
> > My grey-haired memory tells me that an outer join for both the actor table
> > and the actress table is the answer, in that the query will return all
> > titles *even if* one or both fields are NULL. (At least that was the case
> > when I was using Oracle!)
And you were right.
> > So, can somebody please correct the following query (and explain the
> > syntax) so that it will work please? (I haven't tried putting an outer
> > join in it because I don't understand the syntax.)
In this example, you are using inner join... please, read the manual
about JOINs.
> > Select
> > actr.name,
> > acto.name,
> > dvd.title
> > from
> > actresses actr,
> > actors acto,
> > dvd
> > where
> > actr.actr_id = dvd.actr_id
> > and
> > acto.acto_id = dvd.acto_id
> > order by dvd.title;
> >
> > (I used to put (+) at the end of the outer join line, but don't think this
> > will work in MYSQL - at least I don't see it in the manual.)
> >
> > Thanks in advance for your kind help and sorry for the wordy question!
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: checking that any element from one group appears in another group?
Couldn't it be something like:
WHERE ('a' IN ('a','b','c','d')
OR 'f' IN ('a','b','c','d')
OR 'g' IN ('a','b','c','d')
)
On Tue, 2004-01-13 at 15:34, Eli Hen wrote:
> Hello All,
>
> In MySQL it is possible to check if an element is existing in a group, like:
>
> ... WHERE 'a' IN ('a','b','c','d') ...
>
> but that checks one element only.
> I want to check if any element from a group exists in another group, like:
>
> ... WHERE ('a','f','g') IN ('a','b','c','d') ...
>
> 'a' in the first group appears in the second group, so it will return true,
> no matter if 'f' or 'g' exist in too.
>
> Well, is there anything like above that I can use?
>
> -thanks, Eli
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: metric conversions
You could do something like (don't copy and paste, this is just for you to have an ideia - i didn't understand your calculations with 0.063 and others...): SELECT IF (metric="cm", expression_in_centimeters, expression_in_inches) , ...other_fields FROM ... where "metric" is the name of the table field which can have values "cm" or "inches" (could be a ENUM, for example). On Mon, 2004-01-12 at 13:20, Richard Davies wrote: > I recieve measurements for a product sometimes in inches, sometimes in > centimeters. > > These are stored in table1 > int not null primary key, measurement1 int, measurement2 int, type > varchar(255) > > I need to extract data from this table and store it in another table with all > measurements converted to inches. > > insert into table2 select from table1 no, measurement1+0.625, > measurement2+0.063, 'Style'; > > Would it be possible to have a field in table1 that indicates centimeters or > inches and modify the insert statement to check this field and divide > everything by 2.54 before adding and inserting? If this is possible how would > I do it I can't quite make it work. > > -- > Regards > > Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems loading a TEXT field
>From the manual, check:
* the file must be on the server
* you must specify the full pathname to the file (ok, you did this)
* you must have the FILE privilege
* the file must be readable by all and be smaller than
max_allowed_packet
If the file doesn't exist or can't be read due to one of the above
reasons, the function returns NULL.
On Fri, 2004-01-09 at 18:34, mark wrote:
> Table's defined with ... "content TEXT" field, but when I issue the command
> insert into content values ( 'test', '', 'index.html', 1.000,
> 'index.html','','', load_file('/home/projects/URCMS/test/index.html'),
> 'import', now(), 'test', 'import', 0,'');
> either as script input, or from the sql command line, I get rc=0, but when
> I select from the table, what I see is the value of the field named content
> is NULL (and I *did* define it to be NOT NULL).
>
> What am I missing about load_file? I do have the full path to the file to be
> loaded into the TEXT column, and I've tried it with both single and double
> quotes.
>
> mark
> --
> FASCISM. A system of government that exercises a dictatorship of the
> extreme right, typically through the merging of state and business
> leadership, together with belligerent nationalism. -- The American
> Heritage Dictionary Houghton Mifflin Company, 1983
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New to joins - this simple one doesn't work.
Try using '$id' :
SELECT properties.area, images.image_filename FROM properties,
images WHERE properties.reference_number=images.reference_number AND
properties.area='$id'
On Mon, 2003-12-08 at 23:18, fatblokeonbike wrote:
> .
> I'm new to MySQL joins with PHP and I can't get this to work - and I don't
> understand *why* it won't work.
>
> Table 1 - Countries - has fields: country
> Table 2 - Properties - has fields: area, reference_number
> Table 3 - Images - has fields: image_filename, reference_number
>
> The first page goes -
>
> $Query="SELECT country FROM countries";
> $Result=mysql_db_query ($DBName, $Query, $Link);
> while ($Row=mysql_fetch_array($Result))
> {
> print("$Row[country]");
> }
>
> The visitor, wanting to see an area, clicks on a particular country's link
> and arrives in the country.php page, which contains -
>
> $id = $HTTP_GET_VARS["country"];.
>
> $Query="SELECT properties.area, images.image_filename FROM properties,
> images WHERE properties.reference_number=images.reference_number ";
>
> This works, but it delivers the images of every country. To call the images
> from just one country, I try -
>
> $Query="SELECT properties.area, images.image_filename FROM properties,
> images WHERE properties.reference_number=images.reference_number AND
> properties.area=$id";
>
> but it doesn't work - I get the usual "...not a valid MySQL result resource"
>
> I've played around with it, but I confess myself beat. I expect the
> answer's terribly simple - but then, as everyone keeps telling me, so am I.
>
> If you can help, thanks in advance.
>
> Iain.
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication/binary log
Use "PURGE {MASTER|BINARY} LOGS TO 'log_name'" instead of "RESET
MASTER".
>From the manual:
"
Deletes all the binary logs listed in the log index that are strictly
prior to the specified log or date. The logs also are removed from this
list recorded in the log index file, so that the given log now becomes
the first.
(...)
You must first check all the slaves with SHOW SLAVE STATUS to see which
log they are reading, then do a listing of the logs on the master with
SHOW MASTER LOGS, find the earliest log among all the slaves (if all the
slaves are up to date, this will be the last log on the list), backup
all the logs you are about to delete (optional) and purge up to the
target log.
"
http://www.mysql.com/doc/en/PURGE_MASTER_LOGS.html
--
DS
On Mon, 2003-12-08 at 23:09, Mayuran Yogarajah wrote:
> We are running MySQL 3.23 in production, and have replication
> setup in the following manner: There are two machines (m1 and m2).
> Replication is setup in a circular way. Both machines are master and
> slave, more specifically, m1 is master to m2 and m2 is master to m1.
> I checked today and saw that one of the machines had a bunch of
> binary log files (see below). I read in the MySQL documentation that
> you can delete the logs by issuing a RESET MASTER command. I am
> wonder how this will affect replication. Is this going to break replication
> in any way? Is it safe to simply delete the binary log files manually ?
> (Id prefer to do this). Any input would be helpful.
>
> Thank you.
>
>
> MySQL data directory:
>
> total 4388640
> drwx--2 mysqlmysql4096 Aug 17 19:24 mysql
> drwx--2 mysqlmysql4096 Aug 17 19:46 test
> -rw-rw1 mysqlmysql 445 Aug 17 20:42 mw01-bin.001
> drwxr-xr-x8 root root 4096 Aug 17 23:51 ..
> -rw-rw1 mysqlmysql 111 Aug 24 04:02 mw01-bin.003
> -rw-rw1 mysqlmysql 308 Aug 24 04:02 mw01-bin.002
> -rw-rw1 mysqlmysql 111 Aug 31 04:02 mw01-bin.005
> -rw-rw1 mysqlmysql 244491 Aug 31 04:02 mw01-bin.004
> -rw-rw1 mysqlmysql 111 Sep 7 04:02 mw01-bin.007
> -rw-rw1 mysqlmysql 28177 Sep 7 04:02 mw01-bin.006
> -rw-rw1 mysqlmysql7947 Sep 13 23:59 mw01-bin.008
> -rw-rw1 mysqlmysql 111 Sep 14 04:02 mw01-bin.010
> -rw-rw1 mysqlmysql3513 Sep 14 04:02 mw01-bin.009
> -rw-rw1 mysqlmysql 111 Sep 21 04:02 mw01-bin.012
> -rw-rw1 mysqlmysql30791885 Sep 21 04:02 mw01-bin.011
> -rw-rw1 mysqlmysql 111 Sep 28 04:02 mw01-bin.014
> -rw-rw1 mysqlmysql111270867 Sep 28 04:02 mw01-bin.013
> -rw-rw1 mysqlmysql12105202 Sep 28 19:18 mw01-bin.015
> -rw-rw1 mysqlmysql 111 Oct 5 04:02 mw01-bin.017
> -rw-rw1 mysqlmysql38094517 Oct 5 04:02 mw01-bin.016
> -rw-rw1 mysqlmysql 111 Oct 12 04:02 mw01-bin.019
> -rw-rw1 mysqlmysql276605852 Oct 12 04:02 mw01-bin.018
> -rw-rw1 mysqlmysql61917421 Oct 12 23:48 mw01-bin.020
> -rw-rw1 mysqlmysql 111 Oct 19 04:02 mw01-bin.022
> -rw-rw1 mysqlmysql101760652 Oct 19 04:02 mw01-bin.021
> -rw-rw1 mysqlmysql 111 Oct 26 04:02 mw01-bin.024
> -rw-rw1 mysqlmysql579578833 Oct 26 04:02 mw01-bin.023
> -rw-rw1 mysqlmysql 479 Nov 2 04:02 mw01-bin.026
> -rw-rw1 mysqlmysql844900359 Nov 2 04:02 mw01-bin.025
> -rw-rw1 mysqlmysql 111 Nov 9 04:02 mw01-bin.028
> -rw-rw1 mysqlmysql869670836 Nov 9 04:02 mw01-bin.027
> drwx--2 mysqlmysql4096 Nov 10 21:15 Viper
> -rw-rw1 mysqlmysql 111 Nov 16 04:02 mw01-bin.030
> -rw-rw1 mysqlmysql700865150 Nov 16 04:02 mw01-bin.029
> -rw-rw1 mysqlmysql 111 Nov 23 04:02 mw01-bin.032
> -rw-rw1 mysqlmysql 111 Nov 23 04:02 mw01-bin.031
> -rw-rw1 mysqlmysql 111 Nov 30 04:02 mw01-bin.034
> -rw-rw1 mysqlmysql 111 Nov 30 04:02 mw01-bin.033
> -rw-rw1 mysqlmysql 86 Dec 7 01:23 mw01-bin.035
> srwxrwxrwx1 mysqlmysql 0 Dec 7 01:25 mysql.sock
> -rw-rw1 mysqlmysql 570 Dec 7 01:30 mw01-bin.index
> -rw-rw1 mysqlmysql 111 Dec 7 01:30 mw01-bin.037
> -rw-rw1 mysqlmysql 111 Dec 7 01:30 mw01-bin.036
> drwxr-xr-x5 mysqlmysql4096 Dec 7 01:30 .
> -rw-rw1 mysqlmysql 63 Dec 7 01:49 master.info
> -rw-rw1 mysqlmysql861518654 Dec 8 17:53 mw01-bin.038
>
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with query. Please!
Try: SELECT C.company_id, C.company_name FROM companies C LEFT JOIN company_group_intersect CG ON (C.company_id=CG.company_id AND CG.group_id='1') WHERE C.status='1' AND CG.company_id IS NULL -- Diana Soares On Fri, 2003-12-05 at 15:08, Robert Hughes wrote: > I have the following 3 tables: > > table 1: companies > fields 1: company_id, company name, status > Sample data: > 1 , company 1 , 0 > 2 , company 2 , 1 > 3 , company 3 , 1 > 4 , company 4 , 0 > 5 , company 5 , 1 > > table 2: groups > fields 2: group_id, group_name > Sample data: > 1 , Group 1 > 2 , Group 2 > 3 , Group 3 > 4 , Group 4 > 5 , Group 5 > > table 3: company_group_intersect > table 3: group_id, company_id > Sample data: > 1 , 2 > 1 , 3 > 2 , 2 > 2 , 3 > 2 , 5 > > As you can see, Group 1 consists of Companies 2 and 3. And Group 2 > consists of Companies 2, 3 and 5. > > The query I'm having trouble with is trying to get a result set of > (status=1) companies that AREN'T in a particular group. > > "group_id" is my only available variable passed in from the script. > > I need a results set that has: * companies.company_name, > companies.company_id where companies.status='1' and companies.company_id > is not in intersect table next to the variable group_id. > > If I pass in group_id 1 the result set should be: > 5 , company 5 > > since it's the only status='1' company that's not in group 1 > > Thanks in advance for your help. > > Robert > > --- > At Executive Performance Group we take security very seriously. All > emails and attachments are scanned for viruses prior to sending. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.544 / Virus Database: 338 - Release Date: 11/25/2003 > -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: mysql request problem.
On Wed, 2003-12-03 at 09:22, lamjoun wrote: > I have a problem with this request. > > insert into table month_var (year,month,var_cd,val) select > '2003','10',var_cd,0 from var and > var_cd not in (select var_cd from pers_var where year='2003' and > month='10'); > > after execution I have this error > > [Mysql][ODBC 3.51 Driver][mysqld-4.1.0-alpha-max-nt] you can't specify > target table 'month_var' for update in from clause. Is your query correctly pasted ? insert into table month_var (year,month,var_cd,val) select '2003','10',var_cd,0 from var and var_cd not in (select var_cd from pers_var where year='2003' and month='10'); Look at: from var and var_cd not in supposed to be: from var where var_cd not in -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: strange difference between a != b and (a < b OR a > b)
Hi,
>From the MySQL Manual:
MySQL normally uses the index that finds the least number of rows. An
index is used for columns that you compare with the following operators:
=, >, >=, <, <=, BETWEEN, and a LIKE with a non-wildcard prefix like
'something%'.
http://www.mysql.com/doc/en/MySQL_indexes.html
The != does not belong to that list of comparision operators...
Hope this helps!
--
Diana Soares
On Thu, 2003-11-20 at 16:12, [EMAIL PROTECTED] wrote:
> >Description:
> a simple select on a large table does not use an indexed column when the WHERE
> clause uses a != b
> The same query using as WHERE a < b OR a > b (which of course does the same)
> *does* use an index.
> >How-To-Repeat:
>
> mysql <
> create database unequalproblem;
> use unequalproblem;
>
> -- MySQL dump 8.22
> --
> -- Host: localhostDatabase: unequalproblem
> -
> -- Server version 3.23.56
>
> --
> -- Table structure for table 'test'
> --
>
> CREATE TABLE test (
> id int(11) NOT NULL auto_increment,
> number int(11) NOT NULL default '0',
> PRIMARY KEY (id),
> KEY k_number (number)
> ) TYPE=MyISAM;
>
> --
> -- Dumping data for table 'test'
> --
>
>
> INSERT INTO test VALUES (1,2);
> INSERT INTO test VALUES (2,4);
> INSERT INTO test VALUES (3,9);
>
> select('EXPLAIN SELECT id from test WHERE number != 1; *** uses NO index');
> EXPLAIN SELECT id from test WHERE number != 1;
> select('EXPLAIN SELECT id from test WHERE number < 1 OR number > 1; *** actually
> same query, index on number');
> EXPLAIN SELECT id from test WHERE number < 1 OR number > 1;
>
> drop database unequalproblem
>
> END_OF_FILE
>
> >Fix:
> the work around is in the prblem description
>
> >Submitter-Id:
> >Originator: [EMAIL PROTECTED]
> >Organization:
> European Design Centre b.v.
> >MySQL support: none
> >Synopsis:"WHERE a != b" evaluates different from "WHERE a < b OR a > b"
> >Severity:non-critical
> >Priority:low
> >Category:mysql
> >Class: sw-bug
> >Release: mysql-3.23.58 (Source distribution)
>
> >Environment:
> System: Linux archie 2.4.20-20.9 #1 Mon Aug 18 11:27:43 EDT 2003 i686 athlon i386
> GNU/Linux
> Architecture: i686
>
> Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
> GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/specs
> Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
> --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking
> --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux
> Thread model: posix
> gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5)
> Compilation info: CC='i386-redhat-linux-gcc' CFLAGS='-O2 -g -pipe -march=i386
> -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE'
> CXX='i386-redhat-linux-g++' CXXFLAGS='-O2 -g -pipe -march=i386 -mcpu=i686
> -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions'
> LDFLAGS=''
> LIBC:
> lrwxrwxrwx1 root root 13 May 15 2003 /lib/libc.so.6 ->
> libc-2.3.2.so
> -rwxr-xr-x1 root root 1557296 Apr 8 2003 /lib/libc-2.3.2.so
> -rw-r--r--1 root root 2331360 Apr 8 2003 /usr/lib/libc.a
> -rw-r--r--1 root root 204 Apr 8 2003 /usr/lib/libc.so
> Configure command: ./configure '--host=i386-redhat-linux'
> '--build=i386-redhat-linux' '--target=i386-redhat-linux-gnu' '--program-prefix='
> '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin'
> '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include'
> '--libdir=/usr/lib' '--libexecdir=/usr/libexec' '--localstatedir=/var'
> '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info'
> '--without-readline' '--without-debug' '--enable-shared'
> '--with-extra-charsets=complex' '--with-bench' '--localstatedir=/var/lib/mysql'
> '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-mysqld-user=mysql'
> '--with-extra-charsets=all' '--with-innodb' '--enable-local-infile'
> '--enable-large-files=yes' '--enable-largefile=yes'
> '--with-berkeley-db-includes=/usr/include' '--with-berkeley-db-libs=/usr/lib'
> '--enable-thread-safe-client' 'CFLAGS=-O2 -g -pipe -march=i386 -mcpu=i686
> -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE'!
'!
> CXXFLAGS=-O2 -g -pipe -march=i386 -mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64
> -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions' 'build_alias=i386-redhat-linux'
> 'host_alias=i386-redhat-linux' 'target_alias=i386-redhat-linux-gnu'
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: String functions for GROUP BY clause
If you're using MySQL 4.1, you have the GROUP_CONCAT(expr) function. If not, then... i think someone has already done an UDF about this... Search the archives..or see this: http://www.cwts.nl/buter/misc/my-mysql.html -- Diana Soares On Wed, 2003-11-12 at 08:56, Ciprian Trofin wrote: > Is there a way to use a string concatenating function in connection with a > GROUP BY clause? I mean, for a one-to-many relation (like firm-stockholders > relation), to "catch" a structure like: > > firm | stocholders > > f_1 | s_1; s_2; s_3 > f_2 | s_4; s_5 > f_3 | s_6 > > instead of > firm | stocholders > > f_1 | s_1 > f_1 | s_2 > f_1 | s_3 > f_2 | s_4 > f_2 | s_5 > f_3 | s_6 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Challenging query....
On Thu, 2003-10-16 at 17:42, Jeff McKeon wrote: > Is there a way to generate a "line number" for a query return within the > returned rows?? > > For instance, if I return 5 rows from a query, is there a command or > function I can put in a query to add a column that contains the row > number returned? > > Select some_command(),blah, blew from table where blah > 1; > > +---+---+---+ > | row | blah | blew | > +---+---+---+ > | 1 | blah | blew | > | 2 | blah | blew | > | 3 | blah | blew | > | 4 | blah | blew | > +---+---+---+ You can do: SELECT @a:=0; SELECT @a:[EMAIL PROTECTED], blah, blew from table where blah > 1; -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tough sql joining and aggregate question
Couldn't you also group by grouper2 and date2 ? Something like: ... GROUP BY grouper1, grouper2, date2 Just a hint I didn't test it. On Thu, 2003-10-16 at 01:01, Travis Reeder wrote: > I am trying to do an aggregate query, but having some problems and here > they are simplified: > > Table1 > date1 > grouper1 > x > > Table2 > date2 > grouper2 > y > > Query is something like this: > > SELECT SUM(x) as sum_x, SUM(y) as sum_y > FROM Table1 > LEFT OUTER JOIN Table2 on grouper2 = grouper1 > (date2 >= '2003-07-01 00:00:00' AND date2 <= '2003-07-01 23:59:59' ) > and year(date2) = year(date1) > and month(date2) = month(date1) > and DAYOFMONTH(date2) = DAYOFMONTH(date1) > ) > WHERE date1 >= '2003-07-01 00:00:00' AND date2 <= '2003-07-01 23:59:59' > GROUP BY grouper1 > > > Ok, so this works fine unless one of the following happens: > 1. there are 2 entries in Table2 that have the same dayofmonth and > grouper2 value. In this case, they are joined to the same row in Table1 > so I have a duplicate of a record in Table1 and sum_x will be double for > that particular day. > > 2. The exact opposite, so 2 entreies in table1 on the same day, i'm not > too worried about this at this at the moment. > > So how can I ensure to only get one of each? Distinct does not work. > > Travis -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN help (or "come and slap the newbie")
You're confusing the left/right "sides" of LEFT JOIN... Using LEFT JOIN, it is the right table that is dependent on the left table. All results from left table are selected. So you may try: SELECT state.name AS state , group.name AS group, group.zip AS zip, city.name as city FROM state LEFT JOIN city ON city.state_id = state.id LEFT JOIN zip ON zip.city_id = city.id LEFT JOIN group ON group.zip = zip.zip Hope this helps, -- Diana Soares On Tue, 2003-10-14 at 22:27, D. R. Hansen wrote: > Uberdumb question - but I'm still enough of a newbie that this is giving > me fits... > > I have four tables, with relevant columns as follows: > > ++ ++ > group zip > -- -- > name varchar city_id int > zip mediumint zip mediumint > > ++ ++ > state city > -- -- > id int id int > name varchar name varchar > state_id int > > [group]<-n..1->[zip]<-n..1->[city]<-n..1->[state] > > I want my query to return a list that includes all states -- regardless of > whether it matches any records in the other tables. The queries below (and > I have tried many other permutations without > success) returns only rows for states where there is a corresponding group > record: > >SELECT state.name AS state , group.name AS group, >group.zip AS zip, city.name as city >FROM city, group, zip >LEFT JOIN state ON city.state_id = state.id >WHERE group.zip = zip.zip >AND zip.city_id = city.id > >SELECT state.name AS state , group.name AS group, >group.zip AS zip, city.name as city >FROM group >LEFT JOIN zip ON zip.zip = group.zip >LEFT JOIN city ON city.id = zip.city_id >LEFT JOIN state ON state.id = city.state_id > > My test data returns the following data (yes, all other tables are fully > populated). > > +--+-+---+--+ > | state| group | zip | city | > +--+-+---+--+ > | Illinois | Test Group | 60070 | Prospect Heights | > +--+-+---+--+ > 1 row in set (0.41 sec) > > I'd like to see > +--+-+---+--+ > | state| group | zip | city | > +--+-+---+--+ > . > . > . > | Idaho| NULL| NULL | NULL | > | Illinois | Test Group | 60070 | Prospect Heights | > | Indiana | NULL| NULL | NULL | > . > . > . > etc... > > Can anyone tell me where I'm blowing it? > > Dan Hansen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie select statement question
Look at:
if ($tenureid=3)
You're not comparing $tenureid with 3, you're assigning 3 to
$ternureid... If you want to compare both values, you must use the
operator "==" (and not only "=").
On Fri, 2003-10-10 at 05:43, Jordan Morgan wrote:
> Hi,
>
> I have the following statement:
>
> echo "$tenureid";
>
> if ($tenureid=3)
> {
> // get faculty employment record - award date
> $sql = "select TD.Award_Date from TenureDescription TD LEFT JOIN
> InstitutionEmployment IE on TD.TenureDescriptionID=IE.Tenure WHERE
> IE.FacultyMember='$fid'";
> $result = mysql_db_query($database, $sql, $connection) or die ("Error in query:
> $sql. " . mysql_error());
>
> // obtain data from resultset
> list($tenuredate) = mysql_fetch_row($result);
>
> echo "Date Tenure Granted: ";
> echo fixDate($tenuredate);
> echo "";
> }
> else {
>
> echo "Date Tenure Granted: Null";
> }
>
> and I can't figure out why I always get the following result:
>
>
> 2
>
> Date Tenure Granted: 31 Dec 1969
> ---
> or
>
> 1
>
> Date Tenure Granted: 31 Dec 1969
> ---
>
> when 1) tenureid <> 3, and 2) no 31 Dec 1969 date in the database anywhere.
>
> The only thing I can think of is that I modified the dropdown box for the tenure
> date on the data entry page like this:
>
>
> Date Tenure Granted(in mm-dd- format)
>
>
>
>
> "
> . sprintf("%02d", $x) . ""; } ?>
> -
>
>
> "
> . sprintf("%02d", $x) . ""; } ?>
> -
>
>
>
> =1970; $x--) { echo " value=$x>$x"; } ?>
>
>
>
>
> by adding to those 3 fields as I
> want null to be a default selection. but I can't imagine why that'll mess up the if
> statement evaluation.
>
> It seems that the 1st if statement just runs whatever the tenureid is.
>
> Can anyone help me on this? I'm using PHP 4.2.2 and MySQL 3.23.54 btw.
>
> Thanks millions!
>
> Jordan
>
>
>
>
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Query-Display current month and last 11 months...
Maybe like: SELECT something FROM tablename WHERE date_column > DATE_SUB(CONCAT(YEAR(NOW()),'-',MONTH(NOW()),'-','01'), INTERVAL @n MONTH) @n is the number of months you want. If you want data from the current month, @n would be 0. -- Diana Soares On Mon, 2003-10-06 at 07:23, [EMAIL PROTECTED] wrote: > Hi all, >Having a slight problem with mysql select query right here. > I've learnt that if I were to select a particular data within the last 30 days, > this is what my select query should be like: > > SELECT something FROM tablename > WHERE TO_DAYS(NOW(()_TO_DAYS(date_column)<=30; > > (This query selects all records with a 'date_column' value within the last 30 days.) > > Now my question is: What if I would like to display data for the CURRENT MONTH > and the last 11 months???(May also said to be the LAST MONTHS) > HOw should my select query be like?? > Hope to receive some help soon. > Any help given is greatly appreciated. > > Regards, > Irin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE and CHECK Clause
Hi, Check this first: http://www.mysql.com/doc/en/CREATE_TABLE.html On Mon, 2003-09-08 at 18:34, Kraan Deufe wrote: > hi, it's the third time i'm trying to post this message to the list.. so > please reply , even if it is for saying nothing.. only to be sure that this > mail have been distributed. > > Thanx > --- > > > > ok here we are, > > first of all, hi to every one since i'm new to this list. > > then it's my first real advanced experience with mysql, even if i've already > used SQL Server, PostgreSQL and (kof kof) Access. > > well, then i'm trying to create a heavy database with many relations and > check clauses. > > And i'm blocking on the check clause constraint. > > So i'm asking here for precisions: > is the check clause working ? (i've seen some post saying that it is not > working) > Wich kind of tables (BDB, InnoDB, ) is supporting CHECK clauses, CASCADE > DELETE, CASCADE UPGRADE, REFERENCES. > and what is the problem with the following Query ? : > > CREATE TABLE IF NOT EXISTS definitions_avantages ( > id_avantage BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, > nom_avantage VARCHAR(128) NOT NULL UNIQUE CONSTRAINT CHECK (<> ''), > description_avantage TEXT) > > thanx for your help. > > Kraan Deufe > even more than Kojak ;) > > -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about mysql backup
You may tar de mysql data directory but you shouldn't do it with mysql server running (because it is using the files). If you do not want to stop the mysql server (or lock it from writes), you can, for example, use mysqlhotcopy first to create an identical data directory and then do a tar on that copy (and then remove the copy). Cheers, On Mon, 2003-09-08 at 09:58, [EMAIL PROTECTED] wrote: > Hi all, > > Up until now we always created database backups using the mysqldump tool. > > But I'm wondering, if it is possible to just create a tar archive of the > complete mysql data directory. > > We are running on Linux (2.4.18) and we use mysql 4.014. > > Thanks, > > Harm de Laat > Informatiefabriek > The Netherlands -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to correct error code 1006
# perror 13 Error code 13: Permission denied Check the ownership (and permissions) of your . Maybe your is owned by user "mysql" and not "mysqladmin" and then "mysqladmin" can't write to . You may find what your datadir is with: # mysqladmin var | grep datadir Hope this helps, On Fri, 2003-09-05 at 17:42, Jonathan Villa wrote: > I installed MySQL 4.0.14-standard from binary onto a Red Hat 8.0 system. > I always follow the steps described in INSTALL-BINARY as well as review > what shows up when I type ./configure. > > The only thing I did this time around was that I changed the user's name > from mysql to mysqladmin. I get the follow error when trying to create > a new database: > > #1006 - Can't create database 'demo'. (errno: 13) > > Does anyone know the fix to this? -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL syntax question
Hi, Have a look at: http://www.mysql.com/doc/en/String_functions.html You can find there functions to use in SELECT and WHERE clauses, like UPPER(), LOWER(), SUBSTRING(), etc. and http://www.mysql.com/doc/en/String_comparison_functions.html for string comparison functions (LIKE, REGEXP, MATCH AGAINST, ...). On Thu, 2003-09-04 at 18:32, Darryl Hoar wrote: > greetings, > When I am doing a select or update statement, I was wondering if there were > functions to compare strings. IE > > Select * from employee,emp2 where uppercase(employee.fname) > matches(emp2.fname*) > > that is to compare two fields from two tables and see if they match > regardless of whether > one is upper,lower,mixed case. Also see if table1.field1 is a partial match > to another. > > So, > JOHNATHAN would match Jon or Jonny. > > thanks, > Darryl -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with #1111 - Invalid use of group function
Hi! Try this: SELECT TD.project_id, P.project_manager, SUM( TD.time_hours_worked ) as hours FROM time_daily TD INNER JOIN projects P ON P.project_id = TD.time_project_id WHERE TD.time_user_id = 'xpt' HAVING hours <> '0.00' Hope this helps! On Wed, 2003-09-03 at 17:24, Cory Hicks wrote: > Hey folks, > > I am trying to run the following sql query in mysql: > > SELECT TD.project_id, P.project_manager > FROM time_daily TD > INNER JOIN projects P ON P.project_id = TD.time_project_id > WHERE TD.time_user_id = 'xpt' AND ( > SUM( TD.time_hours_worked ) <> '0.00' > ) > > And I keep getting the # errno.- Invalid use of group function - > > I don't want to pull out any rows where the SUM of time_hours_worked is > '0.00'... > > I would be most grateful if anyone has any suggestions > > Many thanks! > > Cory -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding up DATETIME field
Hi, You're adding 2 different type elements. Try just doing SELECT NOW() + SEC_TO_TIME(6000); and check the result: mysql> select NOW() + sec_to_time(6000); +---+ | NOW() + sec_to_time(6000) | +---+ |20030821159528 | +---+ 1 row in set (0.00 sec) For example, minutes=95 !!! Try using DATE_ADD(NOW(), INTERVAL 6000 SECOND) or NOW() + INTERVAL 6000 SECOND On Thu, 2003-08-21 at 13:14, M. Bader wrote: > Hi, > > i'm struggling with updating session expire times in my login table. > > Can you tell me, how to correctly add an amount of seconds (after that > the session expires) to a datetime field? > > when i do the insert for a new login, or an update, the expire field > will alway end up containing zeros > > below are my statements and structure, (MySQL version: 4.0.13) > > > Thanks for any help > > Maik > > INSERT INTO `user_login` ( > `id_kontakt`, > `id_user`, > `session`, > `expire`, > `ip`, > `browser`, > `id_sprache`, > `username`) > VALUES ( > 25, > 39, > '1061461259142493', > (NOW()+SEC_TO_TIME(6000)), > '192.168.0.3', > 'Opera/7.11 (Windows NT 5.0; U) [de]', > 1, > 'maik' > ); > > > UPDATE `user_login` SET `expire` = (NOW()+SEC_TO_TIME(6000)) > WHERE `session` LIKE '1061461259142493'; > > > CREATE TABLE `lok_user_login` ( > `lfdnr` bigint(20) unsigned NOT NULL auto_increment, > `id_kontakt` bigint(20) unsigned NOT NULL default '0', > `id_user` bigint(20) unsigned NOT NULL default '0', > `username` varchar(255) NOT NULL default '', > `session` varchar(255) NOT NULL default '', > `expire` datetime NOT NULL default '-00-00 00:00:00', > `ip` varchar(15) NOT NULL default '', > `browser` varchar(255) NOT NULL default '', > `id_sprache` int(10) unsigned NOT NULL default '0', > PRIMARY KEY (`lfdnr`), > UNIQUE KEY `session` (`session`), > FULLTEXT KEY `session2` (`session`) > ) TYPE=MyISAM; > > > -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rewrite
On Thu, 2003-07-24 at 11:58, mixo wrote: > How can I write this query so it works on mysql 3.54: > > select groupmembers.memberid,users.name from groupmembers,users where > (not users.name='root') > and groupmembers.groupid=(select groups.id from > groups where groups.type='Privileged') > and groupmembers.memberid=users.id > order by name > Try: SELECT groupmembers.memberid, users.name FROM groupmembers, users, groups WHERE users.name<>'root' AND groupmembers.groupid=groups.id AND groups.type='Privileged' AND groupmembers.memberid=users.id ORDER BY name > And, it it possible to remove the case sensetivity of table names. Read the first item that appears in MySQL manual if you search for "case sensitivity": http://www.mysql.com/doc/en/Name_case_sensitivity.html -- Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL statement dilemna
Hi, Since i don't know what is "played for Sussex in two separate spells" -- what's a spell ? (sorry) -- i assume it's a different period of time, but don't know how to calculate it. Ignoring the "spell", i did this: SELECT A.Playerid, A.Teamid, sum(if (A.Substitute=1,0,1)) not_a_substitute, sum(if (A.Substitute=1,1,0)) substitute, min(F.fixdate) FROM Fixtures F, Appearances A WHERE F.Fixid=A.Fixid AND (A.Teamid=F.Hometeam OR A.Teamid=F.Awayteam) GROUP BY A.Teamid ORDER BY F.Fixdate For shure this isn't the best query, but maybe it can turn on some lights. My data was: (i didn't use table Team or Player, used directly names): mysql> select * from Fixtures; +---++++ | Fixid | Fixdate| Hometeam | Awayteam | +---++++ | 1 | 2003-05-10 | Lancashire | Sussex | | 2 | 2003-05-12 | Sussex | Northants | | 3 | 2003-05-15 | Essex | Durham | | 4 | 2003-05-16 | Durham | Leicestershire | | 5 | 2003-05-20 | Sussex | Derbyshire | | 6 | 2003-05-21 | Leicestershire | Derbyshire | +---++++ 6 rows in set (0.01 sec) mysql> select * from Appearances; +--+---+++ | Playerid | Fixid | Teamid | Substitute | +--+---+++ | Anderson | 1 | Sussex | 0 | | Anderson | 2 | Sussex | 1 | | Anderson | 3 | Durham | 0 | | Anderson | 4 | Durham | 0 | | Anderson | 5 | Sussex | 1 | +--+---+++ 5 rows in set (0.01 sec) mysql> SELECT A.Playerid, A.Teamid, sum(if (A.Substitute=1,0,1)) not_a_substitute, sum(if (A.Substitute=1,1,0)) substitute, min(F.fixdate) FROM Fixtures F, Appearances A WHERE F.Fixid=A.Fixid AND (A.Teamid=F.Hometeam OR A.Teamid=F.Awayteam) GROUP BY A.Teamid ORDER BY F.Fixdate; +--++--+++ | Playerid | Teamid | not_a_substitute | substitute | fixdate| +--++--+++ | Anderson | Sussex |1 | 2 | 2003-05-10 | | Anderson | Durham |2 | 0 | 2003-05-15 | +--++--+++ 2 rows in set (0.01 sec) On Wed, 2003-06-25 at 17:40, Gary Broughton wrote: > I'm attempting to write one SQL statement to retrieve data in a > particular way, and don't seem to be able to do it despite dozens of > attempts (indeed maybe it cannot be done), but wondered if anyone could > suggest anything, such as a function I may have missed that can do it, > or that it simply isn't possible! > > I have to list player's histories in a football team throughout his > career, and only have the fixture table and appearance table to go off: > > Fixtures > > Fixid, Fixdate, Hometeam, Awayteam > > 1, 10 May 2003, Lancashire, Sussex > 2, 12 May 2003, Sussex, Northants > 3, 15 May 2003, Essex, Durham > 4, 16 May 2003, Durham, Leicestershire > 5, 20 May 2003, Sussex, Derbyshire > > Appearances > > Playerid, Fixid, Teamid, Substitute? > > Anderson, 1, Sussex, 0 > Anderson, 2, Sussex, 1 > Anderson, 3, Durham, 0 > Anderson, 4, Durham, 0 > Anderson, 5, Sussex, 1 > > > What I am trying to do is retrieve a count of how many games a player > has started, or been substitute for, per team, per chronological spell > at the team (i.e. in this instance he's played for Sussex in two > separate spells, so I need that information grouped in two different > returned records), such as: > > Playerid, Teamid, count(not a substitute), count(substitute), > first_game_for_team > > Anderson, Sussex, 1, 1, 10 May 2003 > > Anderson, Durham, 2, 0, 15 May 2003 > > Anderson, Sussex, 0, 1, 20 May 2003 > > > > My latest SQL statement is: > > SELECT COUNT(*), a.playerid, a.substitute, a.teamid, f.fixdate, t.name > FROM fixture f, apps a, team t > > WHERE a.player_id = 'Anderson' AND a.fixid = f.fixid AND a.teamid = > t.teamid > > GROUP BY a.teamid, a.substitute ORDER BY f.fixdate DESC > > . but this simply creates two records per team, one for substitute > appearances, one for starting appearances. > > > Any pointers would be greatly appreciated, and if I'm asking an > inappropriate question for the group please accept my apologies in > advance. > > Many thanks > > Gary Broughton > -- Diana Soares -- 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: left outer join
Hi, You have one thing wrong in your query... You're using LEFT JOIN but then you use a condition over DFL in the where clause. That's why it doesn't give you the results expected from a LEFT JOIN. You should have a look in the manual to see how to use left joins. The query should by: SELECT DF.id, DFL.id FROM document_fields AS DF LEFT JOIN document_fields_link AS DFL ON DF.id = DFL.document_field_id WHERE DF.is_generic = 1 I removed the DFL.document_id = 37 because you don't mention it in your goals. But if you need that, you should do like: SELECT DF.id, DFL.id FROM document_fields AS DF LEFT JOIN document_fields_link AS DFL ON DF.id = DFL.document_field_id AND DFL.document_id = 37 WHERE DF.is_generic = 1 This will give you all from DF, the DFL which have document_id = 37 and the others DFL will be NULL. On Fri, 2003-02-28 at 12:33, Rob wrote: > Could someone please tell me what I'm doing wrong here? > > I have the following two tables > > a)document_fields > > ++---+---+++ > | id | name | data_type | is_generic | has_lookup | > ++---+---+++ > | 1 | Category | String| 1 | NULL | > | 2 | Keywords | String| 1 | NULL | > | 3 | Comments | String| 0 | NULL | > | 4 | Author(s) | String| 0 | NULL | > ++---+---+++ > > b)document_fields_link > > ++-+---+---+ > | id | document_id | document_field_id | value | > ++-+---+---+ > | 57 | 37 | 3 | | > | 58 | 37 | 4 | jklhkljmh | > ++-+---+---+ > > I'm running the following query > > SELECT DF.id, DFL.id > FROM document_fields AS DF LEFT JOIN document_fields_link AS DFL on DF.id = > DFL.document_field_id > WHERE DFL.document_id = 37 > AND DF.is_generic = 1 > > Basically what I want to achieve is this. I want to select all the document > fields that are > generic and IF the document has values for those fields I want to see those > to, otherwise > I want to see null values? > > I thought a left outer join worked as follows: Select all items on the left > table (document_fields) > and join them to all items on the right table (document_fields_link) for > corresponding values > exists, otherwise insert null. > > So I should always get all the values in the document_fields table and > sometimes values in the > document_fields_link table. > > But I don't get any results back. > > Any ideas? > > > > --- > Rob > > ** > Rob Cherry > mailto:[EMAIL PROTECTED] > +27 21 447 7440 > Jam Warehouse RSA > Smart Business Innovation > http://www.jamwarehouse.com > ** > -- Diana Soares - 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
UDF and mysql binary distr
Hi, For the busy ones, here's my question (then i explain): is it possible to add an UDF to a binary distr (.rpm for RH-8, downloaded from mysql.com) ? I'm having problems in creating an UDF (i create but when a use it mysqld restarts automatically). I've already created UDF functions in the past, i think i was lucky I've been reading the manual and now i'm really confused.. There says: "...For the UDF mechanism to work, functions must be written in C or C++ and your operating system must support dynamic loading. ..." "...For mysqld to be able to use UDF functions, you should configure MySQL with --with-mysqld-ldflags=-rdynamic ..." http://www.mysql.com/doc/en/Adding_UDF.html I saw the spec that came with MySQL-3.23.53 and there is --with-mysqld-ldflags='-all-static' So, i assumed that i could not create the UDF with this binary distr. Since i would have to compile mysql, i thought in adding a native function instead of an UDF. But then i read this: "...You can add UDFs to a binary MySQL distribution. Native functions require you to modify a source distribution..." May i add UDFs to a binary MySQL distr, and keep upgrading mysql only using the rpms or do i must get the source and compile it with .=rdynamic ? Thank you very much, -- Diana Soares - 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: How to Join tables in the right way
Hi, Try the following: SELECT p.name, SUM( t.minutes ) FROM t_project p LEFT JOIN t_subproject sp ON (p.id = sp.project_id) LEFT JOIN t_time t ON (sp.id = t.subproject_id) GROUP BY p.id; (i prefer grouping by id than by name). I didn't understand why your query didn't result, don't know the data you have inserted. I tested the above query and i think it gives the results you want. The big difference is that i used LEFT JOIN instead of INNER JOIN because i assumed that you may have projects which have or may have not subprojects, (and you may have defined subprojects which doesn't already have the minutes defined). Hope this helps. On Thu, 2003-02-27 at 10:32, Sorin Marti wrote: > Hi all, > > I've got following question: > I've got three tables: > > TABLE t_project (id, name,dossier_id, PRIMARY KEY(id)) > TABLE t_subproject (id, name,project_id, PRIMARY KEY(id)) > TABLE t_time (id, minutes, subproject_id,PRIMARY KEY(id)) > > Now I want to dp a SELECT which has following effect: > > Projectname =A6 minutes > testname=A6 300 > > In this example testname should be a value of the column name from the > table t_project. '300' should be the SUM of all 'minutes' (from t_time) > which have the the 'subproject_id' of subprojects which have the > 'project_id' of the project'name': 'testname' > > I tried following query: > > SELECT p.name, SUM( z.minutes ) FROM ( ( t_zeit z INNER JOIN > t_subproject sp ON sp.id =3D z.subproject_id ) INNER JOIN t_project p ON= > sp.project_id =3D p.id ) GROUP BY p.name > > But it has not the efeect I want... > What is wrong? > > I hope you understood my problem (sorry for the bad English) and can > send me your suggestions > > Thanks in advance > > Sorin Marti -- Diana Soares - 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: How do I select 'all related' in a many-to-many relation?
Hi, On Thu, 2003-02-20 at 13:20, Mac wrote: > Hi, > > I am using MySQL v3.23 and have a many-to-many relationship (cars <-> > colors) that I have broken up into three tables as follows > > cars: > ID Manufacturer > 1 Volvo > 2 BMW > 3 Toyota > > colors: > ID Name > 1 Red > 2 Green > 3 Blue > 4 Black > > car_color_links: > car_ID color_ID > 1 3 > 1 4 > 2 1 > 2 3 > 1 2 > > Selecting manufacturers from just one color with join works like a charm. > (IIRC, untested code:) > > SELECT DISTINCT ca.Manufacturer, co.Name FROM car_color_links as l > JOIN colors as co ON (co.ID = l.color_ID) > JOIN cars as ca ON (ca.ID = l.car_ID) > WHERE co.Name = "Red" > > But I can't (understandably) just add an AND to the WHERE statement to select two >colors. I think that what you want is "OR" and not "AND". You want manufacturers which have green cars OR blue cars... > What I want to do is: > > 1. Select all manufacturer which have Green AND Blue cars (or Green >AND Blue AND Black cars, and so on). >i.e. Green AND Blue should return Volvo. SELECT DISTINCT ca.Manufacturer, co.Name FROM car_color_links as l JOIN colors as co ON (co.ID = l.color_ID) JOIN cars as ca ON (ca.ID = l.car_ID) WHERE co.Name = "Green" OR co.Name = "Blue" (is it JOIN or "INNER JOIN" ?) > 2. Select which other colors of cars, a manufacturer which match >criteria 1 above, has. >i.e. Green AND Blue should return Black (Volvo), SELECT DISTINCT ca.Manufacturer, co.Name FROM car_color_links as l JOIN colors as co ON (co.ID = l.color_ID) JOIN cars as ca ON (ca.ID = l.car_ID) WHERE co.Name <> "Green" AND co.Name <> "Blue" > could also return >Green, Blue and Black (Volvo) instead, if that is easier. SELECT DISTINCT ca.Manufacturer, co.Name FROM car_color_links as l JOIN colors as co ON (co.ID = l.color_ID) JOIN cars as ca ON (ca.ID = l.car_ID) And then, maybe you can order results, or do this last query and add ca.Manufacturer = 'Volvo' in the WHERE. > Perhaps this is not possible in single queries, but at least I would > like to be able to do it with just MySQL commands (so I can put them > in a command file). > > I have googled around but have not found any answers. The mysql > website mentions UNION, but as I said I am using MySQL v3.23. > > Could anyone point me in the right direction? > > /mac -- Diana Soares - 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: How to grab the latest record in one query?
Hi, I think that it's your GROUP BY clause that isn't correct. Try replacing "GROUP BY sys.name" with "GROUP BY error_t.sys_id" (ie, group using the same table where you applied the max() function) I didn't tryed, hope it solves. On Thu, 2003-02-20 at 15:02, Chris Czeyka wrote: > Hey all, > > My Question: > > 1. I got a table which tracks errors on systems: > > mysql> select id, sys_id, beginn, end, status from error_t; > +++-+-+-+ > | id | sys_id | begin | end | state | > +++-+-+-+ > | 1 | 1 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | > | 2 | 2 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | > | 3 | 3 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | > | 4 | 4 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | > | 5 | 1 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | > | 6 | 2 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | > | 7 | 3 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | > | 8 | 4 | 1970-01-01 01:01:01 | 1970-01-01 01:01:01 | BEHOBEN | > | 9 | 1 | 1971-01-01 01:01:01 | 1971-01-01 02:01:01 | AKUT| > | 10 | 2 | 1971-01-01 01:01:01 | 1971-01-01 02:01:01 | AKUT| > | 11 | 3 | 1971-01-01 01:01:01 | 1971-01-01 02:01:01 | AKUT| > | 12 | 4 | 1971-01-01 01:01:01 | 1971-01-01 02:01:01 | AKUT| > +++-+-+-+ > > BEHOBEN means SOLVED > AKUT means THERE_IS_STILL_AN_ERROR > > >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > 2. I got a table containing the systems: > > mysql> select * from > system_t; > +++-+--++ > | | id | name | deleted | position | group | > +++-+--++ > | 1 | www.test.de| 0 | NULL | NULL | > | 2 | partner.test.de| 0 | NULL | NULL | > | 3 | app.test.de| 0 | NULL | NULL | > | 4 | web.test.de| 0 | NULL | NULL | > +++-+--++ > > >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > 3. I got a table defining groups for systems with a certain weight for > sorting: > mysql> select * from group_t; > +++-+ > | id | description| order | > +++-+ > | 1 | test1 | 5 | > | 2 | test2 | 9 | > | 5 | another_group | 90 | > +++-+ > > > > Now I need the latest state. As the "id" of "error_t" is AUTO_INCREMENT, > the latest state of a system is the one with the MAX(error_t.id) out of > the group of similar sys_id, i.e. the line containing id = 9 AND sys_id > = 1. I also want to bring the status in a certain order > (-->group_t.order) . > > How can I tell mysql, to fetch the that row, which fits to the MAX(id) > of a system? The example below has been my failed try. I want the LATEST > row, i.e. something like "WHERE max_id = err.id" > > > mysql> SELECT DISTINCT > sys.name,err.state,err.end,err.begin,err.id,MAX(err.id) AS max_id FROM > system_t AS sys LEFT JOIN error_t AS err ON sys.id = err.sys_id LEFT > JOIN group_t AS gr ON sys.group = gr.id WHERE sys.deleted = 0 GROUP BY > sys.name ORDER BY gr.order DESC, gr.order DESC, sys.name ASC; > +-+-+++--++ > | name | state | end| begin | id | max_id | > +-+-+++--++ > | app.test.de | BEHOBEN | 1970-01-01 | 1970-01-01 |3 | 11 | > | partner.test.de | BEHOBEN | 1970-01-01 | 1970-01-01 |2 | 10 | > | web.test.de | BEHOBEN | 1970-01-01 | 1970-01-01 |4 | 12 | > | www.test.de | BEHOBEN | 1970-01-01 | 1970-01-01 |1 | 9 | > +-+-+++--++ > > Either I'm stupid or it's impossible. Let's see which case is true. :) > > > cheers & ThX for any help... > > Chris > -- Diana Soares - 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: strange mysql syntax error
Hi, md5 is a function name, maybe that's why you need to quote the field name. Check the MySQL manual about that. On Thu, 2003-02-20 at 10:51, [EMAIL PROTECTED] wrote: > >Description: > syntax checker reject length restriction when index char column > with ending by number. > > >How-To-Repeat: > alter table xmlmd5 add index md5 (md5(10)); > ERROR 1064: You have an error in your SQL syntax near 'md5(10))' at line 1 > column definition of md5 is md5 varchar(32) > >Fix: > when I use quoted version it is OK > alter table xmlmd5 add index md5 (`md5`(10)); > -- Diana Soares - 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: How to simulate "FULL JOIN" with MYSQL
Hi, On Wed, 2003-02-19 at 15:56, Michael Katzmann wrote: > I have a database with tables representing data from various years > 1997, 1998, 1999, 2000 etc. In the tables I have data representing > widget sales, zip codes, types of widgets, for example. > Not all widget types were sold in every year, so there are entries > in some tables that dont occur in others. > > If I use a left join: > > SELECT T2000.sales, T1999.sales, T1998.sales, T1997.sales > FROM T2000 LEFT JOIN T1999 USING( WidgetType, ZIP ) > LEFT JOIN T1998 USING( WidgetType, ZIP ) > LEFT JOIN T1997 USING( WidgetType, ZIP ) > WHERE T2000.ZIP = 20009 > > I might get something like... > > 1250 7800 NULL NULL > 7689 2434 8788 NULL > 1234 7878 2323 3434 > 7890 NULL NULL NULL > > What I want is data from all years where sales occurred. > with a left join I get diminishing returns. (if there is > a null entry to the left, I never get any output for > subsequent tables). > > What I want is... > > 1250 7800 NULL 6567 <== last value missing due to left join > 7689 2434 8788 NULL > NULL 5679 NULL 3434 <== row missing in 'left join' > 1234 7878 2323 3434 > 7890 NULL 5664 NULL <== third value missing because of left join I think the query you are doing doesn't reflect your purpose. I'm not shure that what i'm going to say is right, but have a thought about this. You're left joining T2000 with T1999, and T1999 with 1998, and T1998 with 1997. When a record exists in T2000 but does not exist in T1999, the T1999.sales values (for that record) will be null and then the join between and T1999 with T1998 (for that record) will only return NULL values and the same for subsequent tables... That's why: > if there is > a null entry to the left, I never get any output for > subsequent tables Maybe what you want would be something like: SELECT T2000.sales, T1999.sales, T1998.sales, T1997.sales FROM T2000 LEFT JOIN T1999 USING( WidgetType, ZIP ) LEFT JOIN T1998 ON (T2000.WidgetType=T1998.WidgetType AND T2000.ZIP=T1998.ZIP) LEFT JOIN T1997 ON (T2000.WidgetType=T1997.WidgetType AND T2000.ZIP=T1997.ZIP) WHERE T2000.ZIP = 20009 This query also is not 100% right because if there isn't a WidgetType in T2000 with ZIP=20009 that could exist, for example, in T1999, then that WidgetType would not appear at all. If this is a problem (there aren't all possible WidgetTypes for ZIP=20009 in T2000), maybe you should first get all possible WidgetType with ZIP=20009 to a temporary table and then substitute in the query above the table T2000 for that temporary table and add T2000 like the other years... > This I believe is a "Full Join". > > How can I simulate a full join with many tables with the current release of > MYSQL ? Can it be done with temorary tables ? Is the solution slow > (especially since the left join (with proper indexes) is very snappy)? > > I saw a reference to "FULL JOINS" being included in 4.1. Is it, in fact, > included in the 4.1 alpha release now available? I'm not shure to answer about this so i'll leave it for the MySQL "gurus" or others that can help.. > Michael Katzmann > -- Diana Soares - 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: stuck with converting a query from a sub query
Hi, you may try:
SELECT DISTINCT portlet.*
FROM portletAssignment PortletAssignment,
portlet LEFT JOIN UserTabAssignment UTA
ON (portlet.name=UTA.portlet_fk AND UTA.user_fk = 'guest' AND
UTA.portal_FK = 'mysqlPortal' AND UTA.tabNumber = 0 AND
UTA.subtabName = 'firstSubtab')
WHERE PortletAssignment.Group_FK in ('group1', 'group2')
AND UTA.portlet_fk IS NULL
Remember that your query (and also this) may have a problem because
there is no relation between portletAssignment and portlet (you didn't
specify any join, so i couldn't use one).
On Tue, 2003-02-18 at 17:11, Alice Farbles wrote:
> Hi,
>
> I am having trouble to convert the following query with a sub query to use
> a join, any advice I would be most grateful
>
> Select Distinct portlet.* from portlet, portletAssignment PortletAssignment
> where PortletAssignment.Group_FK in ('group1', 'group2')
> AND portlet.name not in (
> SELECT portlet_fk FROM UserTabAssignment WHERE user_fk = 'guest' AND
> portal_FK = 'mysqlPortal' AND tabNumber = 0 AND subtabName =
> 'firstSubtab')
>
> thanks
>
> Alice Farbles
--
Diana Soares
-
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: 'Disappearance of the mysql.sock when the computer is shut downand restarted'
Maybe you don't have your mysqld setup to start automatically when the system boots. Check the manual for help on setting up mysql daemon to start automatically after a boot: http://www.mysql.com/doc/en/Automatic_start.html On Tue, 2003-02-18 at 08:48, Fredrik Lundgren wrote: > Hello, > > I'm new to MySQL and to linux. I'm using an Omnibook XE3 with RAM 254 on SuSE linux >8.1. > In one of the slots of a Texas instrument pcmcia I have a Xircom PC card modem 56K. > During startup this has to be disconnected when linux is "starting hardware scan on >boot" > - but otherwise works OK. > > I have loaded MySQL 3.23.55 as an RPM for i386 an this works OK and I > logout and login as another user without problems > ### but when the computer is shutdown > and restarted it appears as if the mysql.sock disappears. > > Or am I unable to start the "mysql server" > > When I try the comand mysql etc i get > > Error 2002: Can't connect to local MySQL server through socket > '/var/lib/mysql/mysql.sock' (2) > > I have tried to stick the socket with > shell> chmod +t /var/lib/mysql > and > shell> ls -ld /var/lib/mysql > confirmes the last permission to t (as suggested onpage 724 in the ref manual). > > I have also made a binary installation > mysql-3.23.55-pc-linux-i686 or something > with pretty much the same result. > > In essens: > > I lose mysql.sock when the computer is restarted > > or > > I'm unable to start the msql server and "reestablish" the msql.sock after reboot > > and > > Before I restart the computer MySQL works very OK both with rpm install and binary >install > > What to do? > What do I do wrong? > > Fredrik Lundgren > Norrköping > > > > - > 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 > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > -- Diana Soares - Before posting, please check: http://www.mysql.com/doc/ (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem in connecting to DB
Everytime you use UPDATE, INSERT, DELETE to update user privileges you
must do a FLUSH PRIVILEGES for changes to take effect.
Check the manual:
http://www.mysql.com/doc/en/Privilege_changes.html
On Tue, 2003-02-18 at 11:57, Ganesh Rajan wrote:
> hello all,
>
> i would like to put a problem as what iam facing... i created a new database
> & new user, but i didn't give the password...
>
> then i logged in to mysql
> ie
> c:\mysql\bin> mysql
> then
> mysql> use mysql
> then
> mysql>update user set password=password('pswd') where user= 'username';
> then i got the result as 1row updated.
>
> then i quit from mysql using mysql>\q
> now when i want to connect
> i.e.
> c:\mysql\bin> mysql -u username -ppswd dbname;
>
> iam getting error stating "Error 1405:Access denied for user:
> 'username@localhost ' (user password :YES)'
>
> IAM WORKING ON COMMANDLINE @ MYSQL SERVER ITSELF
>
> can someone throw light onthis... it will be of grate help
>
> Thanks in advance
> Ganesh Rajan
>
>
> -
> 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
>
--
Diana Soares
-
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: How to move database from one directory to another
Hi, If you only want to move database "Protein" (and not all your databses) you can use symbolic links to do that. You can move your database to the desired path (external drive) and create a symbolic link in /var/lib/mysql/ to it like: localhost:/var/lib/mysql# ln -s /desired_path/Protein Protein Remember that /desired_path/Protein should be owned by "mysql" user.. Otherwise, if you want to move all databases, change the datadir of mysqld. You can do that starting mysqld with --datadir=desired_path or add datadir=desired_path to your /etc/my.cnf file in the [mysqld] section. See: http://www.mysql.com/doc/en/Configuring_MySQL.html http://www.mysql.com/doc/en/Symbolic_links.html On Sun, 2003-02-16 at 18:44, Liu, Zhu wrote: > Hi, everybody, I have installed the mysql server in /var/lib/mysql and > created my database "Protein" under the this path as required in mysql and linux(the operationg system is linux). But the problem is the space for /var is not enough, there exists an exteral driver to try to solve this problem. My question is how to move my database "Protein" into the exteral driver so I later can use the database in external driver instead of current database? Do I need change mysql server from /var/lib/mysql to new external drive? Any other thing I need care about? I look at the command mysqlhotcopy which only can copy existing database for backup purpose not for working database? Anybody know how to do it? Thanks a lot! > > zhu liu > -- Diana Soares - 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: Show record number
If your're using some other programming language to fetch the results, maybe it should be better to do it with that language. Otherwise, if you really need it to be in SQL you can, for example, use variables like this: SET @c:=0; SELECT @c:=@c+1, Name, St, PNumber FROM TABLE; On Fri, 2003-02-14 at 13:36, John Thorne wrote: > Hello > > Query: > > SELECT Name, St, PNumber > >From TABLE > WHERE St = FL > ORDER BY PNumber > > Would like output to be: > > 1 Joe Jones 123-123-1234 > 2 Mary Adams 123-123-4567 > 3 Ted Smith 145-456-8974 > etc > > How can I generate a record number > > SELECT recnu(), Name, St, PNumber ?? > > thanks > > jrt -- Diana Soares - 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: optimizing left join query
Your user_info table is not indexed... (user_info.uid should have an index) Check the manual: http://www.mysql.com/doc/en/MySQL_indexes.html On Tue, 2003-02-11 at 16:16, [EMAIL PROTECTED] wrote: > Hello All, > > I have the following 2 tables and "LEFT join" query as follows: > > > mysql> describe user_lic; > +++--+-+-+---+ > | Field | Type | Null | Key | Default | Extra | > +++--+-+-+---+ > | license_id | bigint(20) | | PRI | 0 | | > | user_id| bigint(20) | | PRI | 0 | | > | location | char(30) | YES | | NULL| | > +++--+-+-+---+ > > > mysql> describe user_info; > +--+--+--+-+-+---+ > | Field| Type | Null | Key | Default | Extra | > +--+--+--+-+-+---+ > | uid | int(10) unsigned | YES | | NULL| | > | location | char(30) | YES | | NULL| | > | mail | char(100)| YES | | NULL| | > +--+--+--+-+-+---+ > > query = SELECT * FROM user_lic LEFT JOIN user_info ON > user_lic.user_id=user_info.uid WHERE user_info.uid is NULL AND > (user_lic.location = 'Rochester, US') > > > When I run this query on a 'user_lic' table with 1000+ rows, it takes about > 20+ mins to complete the query. However a similar query on a Oracle DB > takes couple of minutes. > > I am using a high-end Sun Server, connected to a Xiotech SAN, using Fiber > Optics. I dont think the Hardware is the bottle neck. > > I am wondering how I can optimize the Query/MySQL DB to make this query go > faster. > > > > In Peace, > Saqib Ali > "I fear, if I rebel against my Lord, the retribution of an Awful Day (The > Day of Resurrection)" Al-Quran 6:15 > http://docbook.sc-icc.org > -- Diana Soares - 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: tricky regrex and case case query
Hi, you could try: SELECT name, SUBSTRING_INDEX(name,' ', 1) as first, SUBSTRING_INDEX(v2, ' ', -1) as last FROM table For the uppercase of the first letter, as i don't know a specific functions to do that in MySQL (it has ucase and lcase but for all the word), you must "play" with other functions in MySQL. See: http://www.mysql.com/doc/en/String_functions.html On Sat, 2003-02-08 at 23:33, Mike Blezien wrote: > Hi all, > > I think this can be done with a SQL query, but not have much luck getting it > right. Was hoping to get some suggestions on the best way to do this. > > I need to extract the 'name' column in a mysql database table. split the name so > we have a "first" and "last" name value, as the name was original entered into > the database as their full name instead of the first and last name. My question > is, using SQL, can I split the 'name' column to get the two value, then do an > UPPER case on the first character for the first name created from the split of > the 'name' columnm value. > > I can do this using the Perl programming I am writing, but was pretty sure I do > this within the SQL query, but haven't been able to get it to work properly. > > thanks for any help, > > > -- > MikeBlezien > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > Thunder Rain Internet Publishing > Providing Internet Solutions that work! > http://www.thunder-rain.com > Tel: 1(985)902-8484 > MSN: [EMAIL PROTECTED] > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- Diana Soares - 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: Merge table limit
Hi, On Sat, 2003-02-08 at 09:33, Jerry wrote: > Hello all. > > Does any one know, or can anyone point me in the direction of the limits of > a number of tables I can put in a union of a merge table, I can figure out > what the sql would be. Don't know if there is a limit.. But you must take into account the number of file descriptors a merge table uses... Instead of only two per user, it would be number_of_tables+1 file descriptors per user. So, maybe the limit is defined by the number of file descriptors your OS can have. (but i'm only assuming, i'm not shure). > I have daily tables for an application that average about 2500 rows (2.5 > million) , I want to make a merge table for a year that will encompass them > all (i.e. 9.1 billion rows). > > Has anyone had 365 tables in a merge table ? or more ? Only less than 10.. :-( > Thanks > Jerry > > - > Jeremy Hutchings > Apps R&D > Cable and Wireless > - > -- Diana Soares - 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: Help, Not sure my other email got through!
Assuming it is 5 fixed columns, you could try: SELECT t1.Name, SUM(t2.col1+t2.col2+t2.col3+t2.col4+t2.col5) as sum FROM t1, t2, t3 WHERE t1.id1 = t3.id1 AND t3.id2 = t2.id2 GROUP BY t2.id2 ORDER BY sum DESC Also, i'm assuming that all entries in t1 have a correspondent in t3 and t2. On Sat, 2003-02-08 at 04:47, Beauford wrote: > Not sure my other email got through, so here it is again, with an updated > description. > > Hi, > > I have a database with 3 names in it. In each of these names is 5 > categories that have numbers in them. I want to be able to do a select and > total these numbers for each name, and then sort them in descending order. I > have tried for over 6 hours tonight and no matter what I do, there is one > minor glitch. I am not supplying any code as I would like to know from you > people what the best way to go about this would be - as what I have done is > obviously not working. > > TIA > > i.e > > This is what is in the database. > > Table 1 > > ID1 NAME > > 1 Bill > 2 John > 3 Fred > > Table 2 > > ID2 Col1 Col2 Col3 Col4 > > 1 1456 > 2 3286 > 3 4922 > > Database 3 is a reference file that ties them together - so I would have to > use a join in the select statement. > > ID1 ID2 > 11 > 2 2 > 33 > > This is what I want to be able to show: > > John 21 > Fred 20 > Bill 16 > > MySQL -- Diana Soares - 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: Select question
Note that you're ordering by "goals", not MAX(goals). That's why you're not getting the results expected. Try: SELECT manager.name, position, MAX(goals) as goals FROM roster JOIN reference JOIN manager WHERE manager.idn=reference.idn AND reference.idp=roster.idp AND position like 'F' GROUP BY manager.name ORDER BY goals desc"; On Fri, 2003-02-07 at 19:14, C. Reeve wrote: > Hi again, > > After some struggling, I have managed to get the problem below 99% working, > the problem now is that I can't get them in descending order. Here is my > select statement. > >$query = "select manager.name, position, MAX(goals) from roster join > reference join manager where >manager.idn=reference.idn and reference.idp=roster.idp and position like > 'F' >GROUP BY manager.name order by goals desc"; > > Using the example below, this is what I get: > > Bill 70 > John 48 > Fred 87 > > This is what I want: > > Fred 87 > Bill 70 > John 48 > > TIA > - Original Message - > From: "C. Reeve" <[EMAIL PROTECTED]> > To: "MySQL List" <[EMAIL PROTECTED]> > Sent: Friday, February 07, 2003 1:57 PM > Subject: Select question > > > > Hi, > > > > I have a database with 3 names in it. In each of these names is 5 > > categories that have numbers in them. I want to be able to do a select > and > > get the top number from each category for each name and display them from > > most to least. I have checked all the docs on the select statement, but > > this is escaping me at the moment. > > > > TIA > > > > i.e > > > > This is what is in the database. > > > > Bill 3620504670 > > John2630324846 > > Fred8740196242 > > > > This is what I want to be able to show: > > > > Fred 87 > > Bill 70 > > John 48 > > -- Diana Soares - 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: sort problem
If your town field always uses the format "town number", you can do:
SELECT *
FROM town
ORDER BY substring(town,locate(' ',town))+0 ASC;
On Sat, 2003-02-08 at 11:40, Nicolas JOURDEN wrote:
> Hi,
>
> How can I fix an order by using numbers and letters ?
>
> Id Town
> 56 Paris 1
> 60 Paris 10
> 7 Paris 11
> 262 Paris 12
> 8 Paris 13
> 16 Paris 14
> 22 Paris 15
> 6 Paris 3
> 57 Paris 4
> 51 Paris 6
> 5 Paris 7
> 61 Paris 8
> 59 Paris 9
>
> I'd like to get :
>
> 56 Paris 1
> 6 Paris 3
> 57 Paris 4
> 51 Paris 6
> 5 Paris 7
> 61 Paris 8
> 59 Paris 9
> 60 Paris 10
> 7 Paris 11
> 262 Paris 12
> 8 Paris 13
> 16 Paris 14
> 22 Paris 15
>
> A this time I'm doing this sql syntax :
>
> SELECT *
> FROM town
> ORDER BY town ASC
>
>
> How can I do it ?
--
Diana Soares
-
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: order/group query
Yes, You can use the FIELD() function: SELECT * FROM table WHERE recordID IN ($string) ORDER BY FIELD(recordID,$string) On Tue, 2003-02-04 at 13:15, Wilbert Enserink wrote: > Hi All, > > I have a string containing recordID's in a specific order (e.g. > $string=17,2,33,5,99) > How can I select those records from a table in the right order (as > determined in $string)? > > SELECT * FROM table WHERE recordID IN ($string) > > results in the right records but not in the order I want, instead the order > is determined by recordID... meaning (2,5,17,33,99) > > > Any clues??:-) > > thx in advance! > Many regards, > > Wilbert Enserink > > > sql,query,queries,smallint > > - > Pas de Deux > Van Mierisstraat 25 > 2526 NM Den Haag > tel 070 4450855 > fax 070 4450852 > http://www.pdd.nl > [EMAIL PROTECTED] > - > > - > 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 > -- Diana Soares - 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: encrypted password
Hi, Use the PASSWORD() function to encrypt the password. There are other encryptation functions in MySQL like MD5(), SHA1(), ENCRYPT(). Have a look at functions you can use in SELECT statement at the manual. http://www.mysql.com/doc/en/Miscellaneous_functions.html On Tue, 2003-02-04 at 19:58, Jianping Zhu wrote: > > I have mysql in a redhat machine. I need to use mysql do user > authentication to a website. > I have a table like following. > > +--+--+ > | username | passwd | > +--+--+ > | jianping | jian1830 | > | chichi | jian1830 | > +--+--+ > > I want the passwd field not to be plain text but encrypted. how can i do > that? > > Thanks. > > > > - > 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 > -- Diana Soares - 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: Problem with a query - help!!?
Hi,
I think the problem is that your assuming that journoNatOpt.journoid is
'' (empty) when journoDetails.journoid has no entry in journoNatOpt.
But that's not true, the value is NULL.
So, try replacing
AND journoNatOpt.journoid = ''
with
AND journoNatOpt.journoid IS NULL
On Mon, 2003-02-03 at 13:46, David Phipps wrote:
> Hi,
>
> I am new to this list so I apologise if this is not the place to post this
> message.
>
> I am in the final stages of completing a project and I have one query that
> is causing problems.
>
> I need the query to get a story or stories from the story table and
> journalists from the journalist table where their respective id is not
> present in a table which links the journoid to a national option table and
> the storyid to the national option table.
>
> So far I have:
>
> SELECT storySub.storyid, storySub.headline, journoDetails.journoid,
> journoDetails.journoName, journoNatOpt.journoid AS natJid
> FROM releaseManager, journoDetails LEFT JOIN journoNatOpt ON
> journoDetails.journoid = journoNatOpt.journoid, storySub
> WHERE storySub.isNational = 1
> AND journoDetails.isNational = 1
> AND journoDetails.isApproved = 1
> AND journoNatOpt.journoid = ''
> AND releaseManager.storyid = storySub.storyid
> AND releaseManager.isReleased = 1
> AND releaseManager.immediateRelease = 0
> AND releaseManager.releaseTime BETWEEN #CreateODBCTime(Now())# AND
> #CreateODBCTime(DateAdd("n", 30, Now()))#
> AND releaseManager.releaseDate BETWEEN #CreateODBCDateTime(Now())# AND
> #CreateODBCDateTime(DateAdd("h", 24,Now()))#
>
> The above query seems to have trouble when I insert the following: AND
> journoNatOpt.journoid = ''
> If I remove this line then I see all the of national stories and any
> national journalists which is fine except that some of the stories and
> journalists are actually linked to a national option. What I want to have
> is all stories and journalists that are national but where they are not
> linked to a national option.
>
> If this makes any sense and you can see what I am doing wrong then please
> help!!
>
> Thanks in advance
>
> Dave Phipps
> MySQL new guy!!
--
Diana Soares
-
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: mysql.sock is missing - Please....
Hi, On Sun, 2003-02-02 at 00:25, B. van Ouwerkerk wrote: > >If it gives errors about creating/writing mysql.sock, check the > >permissions/ownership of directory /var/lib/mysql.. Check the manual for > >the right permissions... > > Might depend on your distro, AFAIK mysql.sock would normally live in /tmp Yes, you're right, i only used that directory (/var/lib/mysql) because he said: > > > I'm staring mysql with mysql -u root mysql but I'm geting this error > > > message: "Can't connect to local MySQL server through socket > > > '/var/lib/mysql/mysql.sock'. I cannot find Mysql.sock anywhere, but I so i thought his mysql server was configured to have the sock file there. :-) Thanx, -- Diana Soares - 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: mysql.sock is missing - Please....
> > Hi, I'm getting the same error message. mysql here is in /etc/rc.d/init.d, > > don't ask me why, I'm new to this things. That's because the rpm installation puts there a file "mysql" to be easier to configure starting services If you want mysql to start everytime your machine reboots, have a look at, for example "chkconfig" command or: http://www.mysql.com/doc/en/Automatic_start.html If you have /etc/rc.d/init.d/mysql i guess you can try the following: First be shure that mysqld is running (with "ps auxw | grep mysql" or "netstat -ta | grep mysql"...). You may also stop the server with /etc/rc.d/init.d/mysql stop Start the server: /etc/rc.d/init.d/mysql start If it gives errors about creating/writing mysql.sock, check the permissions/ownership of directory /var/lib/mysql.. Check the manual for the right permissions... Good luck, On Tue, 2003-01-28 at 00:38, Pedro Leite wrote: > Any help from good samaritans? I have no idea to solve this mess! > > TIA > - Original Message - > From: "Pedro Leite" <[EMAIL PROTECTED]> > To: "TdA" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Monday, January 27, 2003 10:23 AM > Subject: Re: mysql.sock is missing > > > > Hi, I'm getting the same error message. mysql here is in /etc/rc.d/init.d, > > don't ask me why, I'm new to this things. > > > > Any more help would be much appreciated. > > TIA > > - Original Message - > > From: "TdA" <[EMAIL PROTECTED]> > > To: "Pedro Leite" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > > Sent: Monday, January 27, 2003 7:45 AM > > Subject: Re: mysql.sock is missing > > > > > > > There are a couple of things you can try, first make sure your process > is > > > started. /etc/rc.d/ mysql start should do the trick. > > > TDA > > > - Original Message - > > > From: "Pedro Leite" <[EMAIL PROTECTED]> > > > To: <[EMAIL PROTECTED]> > > > Sent: Sunday, January 26, 2003 11:04 PM > > > Subject: mysql.sock is missing > > > > > > > > > > Hi, > > > > > > > > I'm staring mysql with mysql -u root mysql but I'm geting this error > > > > message: "Can't connect to local MySQL server through socket > > > > '/var/lib/mysql/mysql.sock'. I cannot find Mysql.sock anywhere, but I > > was > > > > running mysql before reboot the linux sytem. What can I do to > > > > recover/reinstall/run the mysql server again? I didn't find > > mysql.server, > > > > but I do have mysql.server* on dir /usr/share/mysql. I'm running > > Mandrake > > > 9 > > > > and Mysql-323.54a (rpm). > > > > > > > > Thank you in advance > > > > Pedro > > > > -- Diana Soares - 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: [OT] linux novice cannot resolve apache-php-mysql linkage 2.
This is OT... Seems an Apache configuration problem.. You compiled apache with > ./configure --prefix=/usr/local/apache --enable-module=so so i think your DocumentRoot directive in "httpd.conf" is DocumentRoot /usr/local/apache/htdocs (i'm not shure, but something like that) and that's why you didn't find a "www" directory other than /var/www... Check that. Be shure that DocumentRoot points to /var/www (or change your test1.php to /usr/local/apache/htdocs/.) Good luck, On Wed, 2003-01-29 at 22:31, [EMAIL PROTECTED] wrote: > dell 4100 256mb, 11gb hd, Redhat 8.0 grub-linux > linux novice cannot resolve apache-php-mysql linkage. > > local closed system (No modem, no www access, no internet) > > I have tried to create a database of 7 tables with about 840mb > of data since the 20th of November 2002. My own db handler (.cpp's) > will take years to code and test what the apache-php-mysql combo can do in > hours. > I've been able to load all mysql tables with about 30kb each of data as a > test. > I've made php test scripts and run them with apache server (local). > I have n-o-t been able to run apache-php-mysql as a single application. > > About the 22nd of January, 2003, I started over with: > file:/usr/local/src/httpd-2.0.44.tar.gz > file:/usr/local/mysql-3.23.55.tar.gz > file:/usr/local/php-4.3.0.tar.gz > > Using root, I entered the following commands: > > for apache: > > cd /usr/local/src > tar -xzvf httpd-2.0.44.tar.gz > cd httpd-2.0.44 > ./configure --prefix=/usr/local/apache --enable-module=so > make > make install > > for mysql: > > groupadd mysql > useradd -g mysql mysql > cd /usr/local > gunzip < mysql-3.23.55.tar.gz | tar -xvf - > ./configure --prefix=/usr/local/mysql > make > make install > /scripts/mysql_install_db > chown -R root /usr/local/mysql > chown -R mysql /usr/local/mysql/var > chgrp -R mysql /usr/local/mysql > cp support-files/my-medium.cnf /etc/my.cnf > /usr/local/mysql/bin/safe_mysqld --user=mysql & > mysql > quit; > > for php: > > cd /usr/local/ > tar -xzvf php-4.3.0.tar.gz > cd php-4.3.0 > ./configure --with-php=/usr/local/php --with-apxs2=/usr/local/apache/bin/apx > s > make > make install > > then changed "file:/usr/local/apache/conf/httpd.conf" > ServerAdmin root@localhost > ServerName root@localhost:80 > > then added to "file:/usr/local/apache/conf/httpd.conf" > LoadModule php4_module modules/libphp4.so > AddType application/x-httpd-php .php > AddType application/x-httpd-php-source .phps > then > /usr/local/apache/bin/apachectl stop > /usr/local/apache/bin/apachectl start > and file:/var/www/test1.php could not be found using the browser. > note that /var/www/test1.php is the only ?/www/? directory found. > > > changed ServerName root@localhost:80 > to 127.0.0.1:80 > then > /usr/local/apache/bin/apachectl stop > /usr/local/apache/bin/apachectl start > and file:/var/www/test1.php could not be found using the browser. > > any help or documentation pointers would be very > much appreciated. > > I've installed RH at least 20 times, and will continue > to install until I can run my personal scripts to test > that apache-php-mysql are doing what I want. Any suggestion > that I should (re)install should include the specific platform > to be selected. > > Thanks > > dander > > cut and paste of my version of test1.php > > > > PHP Test > > > ";?> > > > > > > - > 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 > -- Diana Soares - 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: # of connected user
Don't know if i understood your question very well, but try: # mysqladmin proc in command line or execute the query SHOW PROCESSLIST On Fri, 2003-01-31 at 11:49, Mustafa Yelmer wrote: > How i list connected users(active) to mysql server? > > it is important to know connected users for me? > Mysql runs in server-client system, and each host of clients are different > (i assume) I didn't understand this... > > > Mustafa Yelmer > Software Engineer > +90 212 2128200-1228 > [EMAIL PROTECTED] > -- Diana Soares - 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: Newbie Q: Booleans??
Hi, You have the BOOL type in MySQL which is a synonym for TINYINT(1). Maybe this is the best approach. You may see the manual in "Column types" section. On Fri, 2003-01-31 at 11:46, David Hefford wrote: > Have used many databases but am just getting into MySQL and am totally rapt > about it :) I am writing a front end in realBasic that needs to be able to > run on various backends, MySQL being the main one. All of the others have a > boolean type of column. What is the general practice to mimic¹ a BOOLEAN > field in MySQL? PS: I do not need NULL or date for example, I just need true > or false, yes or no etc... > > Regards > > David Hefford > Power On Solutions > Web: http://www.poweron.com.au > Email: [EMAIL PROTECTED] > > > > > > - > 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 > -- Diana Soares - 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: How can I start my mysql server with max_connections option
You have mistaken the syntax, use the option --set-variable max_connections=200 or add to your my.cnf file in one of [mysqld], [server] or [safe_mysqld] sections: set-variable = max_connections=200 On Thu, 2003-01-30 at 17:16, [EMAIL PROTECTED] wrote: > Hi Group, > > I want to start my mysql with max_connections option. Now I am getting To > many connction error. So I want to allow 200 concurrent connections... > > I tried with > safe_mysqld max_connections=200 & > But it is not coming up... > How can I set max_connections as 200. Do I need to specify that in my.cnf > > Please help.. > -- Diana Soares - 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: Fulltext Index
Hi, "The MATCH() column list must exactly match the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE." from: http://www.mysql.com/doc/en/Fulltext_Restrictions.html On Thu, 2003-01-30 at 21:40, R. Hannes Niedner wrote: > If I create a FULLTEXT index for 2 or more columns in a table will I be able > to use it for a MATCH only against a single column (of the above) or do I > have to create additional FULLTEXT indices for each of these columns? > > Thanks/h > -- Diana Soares - 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: Help with a select where an ID isn't in another table, but is avalid row.
Hi, I didn't test this. Assuming that Swordfish.scanner.scanner_id <> 0 for all rows (and that's why it doesn't appear in the results list from the first query): SELECT InteractV2.Job_Queue.TargetRange AS TargetIP, IFNULL(INET_NTOA(Swordfish.scanner.ipaddr),'ANY SCANNER') AS ScannerIP FROM InteractV2.Job_Queue LEFT JOIN Swordfish.scanner ON Swordfish.scanner.scanner_id = InteractV2.Job_Queue.ScannerID WHERE InteractV2.Job_Queue.CompanyID = '123'; Note the LEFT JOIN and the IFNULL. Hope it works! On Tue, 2003-01-21 at 08:14, Daevid Vincent wrote: > Having a bit of a sticky wicket here... I'm sure the answer is simple > but I'm just not seeing it. > > Basically I want to return all the TargetRanges and ScannerIDs. You > would think it's simple, here's the snag. See how ScannerID has 0 in > some rows. Well, we used 0 to mean "any scanner" in our PHP code. So, I > want a select query that will return me BOTH the rows for CompanyID = > 123. As for the 0 ScannerID row, well leave the column blank, or > populate it with "ANY SCANNER" or something, that would be awesome. > Currently I only get the one row that has a ScannerID = 5. The second > query below doesn't work as it gives me multiple permutations (of > course). Any of you gurus know how to resolve this? > > So the output should ideally look like: > +--++ > | TargetIP | ScannerIP | > +--++ > | www.interactnetworks.com | 66.150.172.129 | > | 192.168.30.1 | ANY SCANNER| > +--++ > > OR even this is cool > > +--++ > | TargetIP | ScannerIP | > +--++ > | www.interactnetworks.com | 66.150.172.129 | > | 192.168.30.1 || > +--++ > > mysql> select * from InteractV2.Job_Queue; > +-+---+--+---+ > | QueueID | ScannerID | TargetRange | CompanyID | > +-+---+--+---+ > | 3 | 0 | 65.121.191.46|89 | > | 8 | 0 | 12.228.139.218 |99 | > | 14 | 0 | 12.228.90.64 | 121 | > | 10 | 0 | 206.253.218.122 | 117 | > | 11 | 5 | 206.253.218.123 | 117 | > | 15 | 5 | www.interactnetworks.com | 123 | <-- > | 19 | 0 | 192.168.30.1 | 123 | <-- > | 17 | 0 | 207.13.196.235 | 125 | > +-+---+--+---+ > > mysql> SELECT InteractV2.Job_Queue.TargetRange AS TargetIP, > ->INET_NTOA(Swordfish.scanner.ipaddr) AS ScannerIP > -> FROM InteractV2.Job_Queue, Swordfish.scanner > -> WHERE Swordfish.scanner.scanner_id = > InteractV2.Job_Queue.ScannerID > -> AND InteractV2.Job_Queue.CompanyID = '123'; > +--++ > | TargetIP | ScannerIP | > +--++ > | www.interactnetworks.com | 66.150.172.129 | > +--++ > > mysql> SELECT InteractV2.Job_Queue.TargetRange AS TargetIP, > ->INET_NTOA(Swordfish.scanner.ipaddr) AS ScannerIP > -> FROM InteractV2.Job_Queue, Swordfish.scanner > -> WHERE Swordfish.scanner.scanner_id = > InteractV2.Job_Queue.ScannerID > -> OR InteractV2.Job_Queue.ScannerID = 0 > -> AND Swordfish.scanner.scanner_id <> > InteractV2.Job_Queue.ScannerID > -> AND InteractV2.Job_Queue.CompanyID = '123'; > +--+-+ > | TargetIP | ScannerIP | > +--+-+ > | 206.253.218.123 | 66.150.172.129 | > | www.interactnetworks.com | 66.150.172.129 | > | 192.168.30.1 | 66.150.172.129 | > [snipped for brevity sake] > | 192.168.30.1 | 192.168.10.70 | > | 192.168.30.1 | 192.168.10.80 | > | 192.168.30.1 | 192.168.25.4| > +--+-+ > 29 rows in set (0.00 sec) > > Other useful information: > > select scanner_id, ipaddr, inet_ntoa(ipaddr) as scanner_ip from > Swordfish.scanner order by scanner_id; > +++-+ > | scan
Re: Trying to update data in a table - getting an error
Hi, substr() doesn't exist in MySQL. Use substring(). UPDATE DocComms SET ItemHSCode = concat(substring( ItemHSCode, 1, 4 ), substring( ItemHSCode, 6, 2 ), substring( ItemHSCode, 9, 2 )) On Tue, 2003-01-21 at 15:34, Don wrote: > Hi, > RESENT DFUE TO TYPO > > I want to change a field in a table. Currently the data is in theformat --> > 4818.50.00 > I want to remove the decimal points so that it is like --> 48185000 > I tried the sql code below but am getting a syntax error. Can someone > please point out the error of my ways? > > UPDATE DocComms SET ItemHSCode = concat(substr( ItemHSCode, 1, 4 ), > substr( ItemHSCode, 6, 2 ), substr( ItemHSCode, 9, 2 )) > > Thanks, > Don -- Diana Soares - 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: select query to give sum and distinct values
I didn't test it but you may try something like: SELECT ddi, sum(tot_dur) FROM table GROUP BY ddi ORDER BY ddi On Mon, 2003-01-20 at 12:59, Steve Mansfield wrote: > Using mysql 3.23.51 > I have a mysql table that holds records for telephone traffic. > The table fields are as follows: > > idstartstop > clidditot_dur > day_dureve_durwkd_dur > 1 08/12/2002--23:50:0009/12/2002--02:23:551507608105 > 08451340206923508635600 > 2 09/12/2002--00:14:1509/12/2002--00:15:081634324824 > 0845134012053.1 053.1 0 > 3 09/12/2002--00:20:1709/12/2002--00:20:591634324824 > 0845134012041.9 041.9 0 > 4 09/12/2002--00:28:0009/12/2002--00:28:461634324824 > 08451340120460460 > 5 09/12/2002--00:30:2509/12/2002--00:31:041634324824 > 0845134012038.9 038.9 0 > 6 09/12/2002--03:22:3009/12/2002--05:08:431507608105 > 084513402066372.9 06372.9 0 > 7 09/12/2002--05:25:2509/12/2002--05:35:561622859384 > 08451340214631.6 0631.6 0 > > > What i'm trying to do is run a query that will give me the sum tot_dur > for each distinct ddi > ie: distinct ddi will give me the list of all the ddi numbers that were > called but i need the sum of tot_dur for each distinct ddi. > so it should produce an output like: > > dditot_dur > 08451340120179.9 > 0845134020615607.9 > 08451340214631.6 > > Anyone have any ideas as i just cannot get the query correct. > > > Steve Mansfield > [EMAIL PROTECTED] > > http://www.getreal.co.uk > Real Data Services Ltd 117-119 Marlborough Road Romford Essex RM7 8AP > [Office] 0870 757 7900 [Fax] 0870 757 8900 > http://www.be-an-isp.comhttp://www.isdn4free.co.uk >http://signup.getreal.co.uk > For our email disclaimer please see the url below. > http://www.getreal.co.uk/disclaimer.htm -- Diana Soares - 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 Update Help / Problem
Check the REPLACE command... http://www.mysql.com/doc/en/REPLACE.html I think it suits your problem... On Mon, 2003-01-20 at 08:14, Michael Watson wrote: > This may be very simple, but the solution eludes me.. > > I have two tables Products and NewProducts, The Products table contains a > list of all the current product line and prices Products.ProdCode, > Products.Price (amongst other details not relevent here). NewProducts > contains a list of changes to be made to the database, ie New Product lines, > changes of prices to existing lines. (NewProducts.ProdCode, > NewProducts.Price). > > How can I update existing records in Products if ProdCode are equal in both > tables, and if ProdCode exists in NewProducts but Not in Products, insert > that records? > > We are using MySQL Version 3.23.54a > > Thanks > > Michael Watson > MBW Computing > [EMAIL PROTECTED] > Tel: 03 9782-0427 Fax: 03 9782-0657 > Mob: 0425 788-185 -- Diana Soares - 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: Problem localhost connect
Hi, [root@localhost root]# perror 111 Error code 111: Connection refused You don't seem to have the mysql server running. First, be shure that your mysqld is up and running. Then, correct your command: to reload the server from the command line you should use "mysqladmin" command instead of "mysql". Like: # mysqladmin -hlocalhost -p reload On Mon, 2003-01-20 at 13:22, [EMAIL PROTECTED] wrote: > Hello, > When I reload mysql server settings with > 'mysql -hlocalhost -p reload --port 3306' > 'Enter password: *' > > or > > 'mysql -hlocalhost -p reload' > 'Enter password: *' > > I get error: 'ERROR 2003: Can't connect to MySQL server on 'localhost' (111)' > > I tried with new, old and no password! > > So? -- Diana Soares - 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: Problem with mysqlimport
Hi, I didn't notice in the other e-mail, but the syntax of mysqlimport says everything.. :-) mysqlimport is like "LOAD DATA INFILE" and imports data to ONE table. To use your UM.txt you may use: # mysql test < UM.txt For further explanation of mysqlimport: # mysqlimport --help Loads tables from text files in various formats. The base name of the text file must be the name of the table that should be used. OR http://www.mysql.com/doc/en/mysqlimport.html shell> mysqlimport [options] database textfile1 [textfile2 ...] For each text file named on the command-line, mysqlimport strips any extension from the filename and uses the result to determine which table to import the file's contents into. For example, files named `patient.txt', `patient.text', and `patient' would all be imported into a table named patient. ... On Fri, 2003-01-17 at 13:45, Shripal Shah wrote: > Hi, > > I am using version 3.23.47. > Yes, DROP TABLE COMMAND IS PERFECT. > > See, here I am mentioning my steps: > > 1> mysqldump --opt test patient > UM.txt > > 2> mysqlimport test UM.txt > > THEN IT GIVES ME FOLLOWING ERROR: > > mysqlimport: Error: Table 'test.UM' doesn't exist, when using table: UM > > So, this is my problem. > > Thanks for your immediate reply > > Thanks & Regards, > > Shripal. > > - Original Message - > From: "Diana Soares" <[EMAIL PROTECTED]> > To: "Shripal Shah" <[EMAIL PROTECTED]> > Sent: Friday, January 17, 2003 6:48 PM > Subject: Re: Problem with mysqlimport > > > > Hi, > > What version of MySQL do you use ? > > Check if the "DROP TABLE" command on file "UM.txt" looks like this: > > DROP TABLE IF EXISTS ; > > > > Also, is it a NOTE or mysqlimport really exits ? Does it create any > > table ? > > > > http://www.mysql.com/doc/en/DROP_TABLE.html > > > > > > On Fri, 2003-01-17 at 12:33, Shripal Shah wrote: > > > Dear Sir/Madam, > > > > > > I've one data file which is built with the help of mysqldump. This file > > > contains structure & data of 2 tables of test database. Like this.. > > > > > > mysqldump -C --add-drop-table -e test patient hospital > UM.txt > > > > > > then I am trying to import data using this statement.. > > > > > > mysqlimport test UM.txt > > > > > > It give me error for table not found... > > > > > > Please guide me regarding this matter.. > > > > > > Thanks & Regards, > > > > > > Shripal. > > > -- Diana Soares - 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: root host privlidge
Hi, Start the mysqld with --skip-grant-tables. This way the server will not use the privilege tables... Then alter the data as you want and do a mysqladmin flush-privileges (or reload) to restart using them. More info: http://www.mysql.com/doc/en/Privileges_options.html On Fri, 2003-01-17 at 03:36, CyberCub wrote: > I have managed using the phpmyadmin for editing the root user to allow > another host to connect (home ip), what ever it did it messed it up, root no > longer aloud to connect to the mysql database at all. Any way to fix this? > Before I go and delete the user.MDY file and re-run the mysql_install_db to > restore it and re-add the usernames/passes and priv's. Any suggestions are > welcomed. Thanks Bill > - 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: help w/join condition
Do you mean that you want all units to appear, even if they have items not defined (like having 4 defined and the other 5 with no value) ? If is that, you may try using LEFT JOIN, instead of INNER JOIN. Columns which are not defined will appear NULL (I did not test this): SELECT u.name, item1.name AS item1_name,item2.name AS item2_name, item3.name AS item3_name,item4.name AS item4_name, item5.name AS item5_name,item6.name AS item6_name,item7.name AS item7_name, item8.name AS item8_name,item9.name AS item9_name,item1.item_id as item1_id, item2.item_id as item2_id, item3.item_id as item3_id,item4.item_id as item4_id, item5.item_id as item5_id,item6.item_id as item6_id, item7.item_id as item7_id,item8.item_id as item8_id,item9.item_id as item9_id FROM units as u LEFT JOIN items as item1 ON (u.item1_id = item1.item_id) LEFT JOIN items as item2 ON (u.item2_id = item2.item_id) LEFT JOIN items as item9 ON (u.item9_id = item9.item_id) ORDER BY unit_id DESC limit 1; On Wed, 2003-01-15 at 02:10, mike wrote: > Hello, > > Is it possible to have a join condition where the condition is not > always me? Here is my query: > > SELECT u.name, item1.name AS item1_name,item2.name AS item2_name, > item3.name AS item3_name,item4.name AS item4_name, > item5.name AS item5_name,item6.name AS item6_name,item7.name AS > item7_name, > item8.name AS item8_name,item9.name AS item9_name,item1.item_id as > item1_id, > item2.item_id as item2_id, item3.item_id as item3_id,item4.item_id > as item4_id, > item5.item_id as item5_id,item6.item_id as item6_id, > item7.item_id as item7_id,item8.item_id as item8_id,item9.item_id as > item9_id > FROM items AS item1,items AS item2,items AS item3,items AS item4,items > AS item5, > items AS item6,items AS item7,items AS item8,items AS item9 > INNER JOIN units as u > ON u.item1_id=item1.item_id > AND u.item2_id=item2.item_id > AND u.item3_id=item3.item_id > AND u.item4_id=item4.item_id > AND u.item5_id=item5.item_id > AND u.item6_id=item6.item_id > AND u.item7_id=item7.item_id > AND u.item8_id=item8.item_id > AND u.item9_id=item9.item_id > ORDER BY unit_id DESC limit 1; > > This works great as long as each unit record has a item_id in the > units_item_id field. My probelms is > not all units will have 9 items. Most units will have less than 9 items. > > Any help would be greatly appreciated, > Thanks in advance, > Mike > > > > - > 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 > -- Diana Soares - 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: three table join
Try adding a "T2.PK IS NOT NULL" or "T2.FK1 IS NOT NULL": SELECT SomeStuff FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1 RIGHT JOIN Table3 AS T3 ON T2.FK2 = T3.PK WHERE T1.PK=999 AND T2.FK1 IS NOT NULL Example with old tables i have: mysql> select * from t1; +++ | id | v | +++ | 1 | 23 | | 2 | 18 | | 3 | 6 | +++ 3 rows in set (0.00 sec) mysql> select * from t2; ++---+---++ | id | id_t1 | id_t3 | v | ++---+---++ | 1 | 0 | 0 | 2 | | 2 | 1 | 1 | 89 | | 3 | 2 | 3 | 8 | ++---+---++ 3 rows in set (0.00 sec) mysql> select * from t3; ++++ | id | v1 | v2 | ++++ | 1 | 8 | 6 | | 2 | 28 | 12 | | 3 | 56 | 23 | | 4 | 2 | 34 | ++++ 4 rows in set (0.00 sec) mysql> select * from t1 as T1 LEFT JOIN t2 AS T2 ON (T1.id = T2.id_t1) RIGHT JOIN t3 AS T3 ON (T2.id_t3 = T3.id) WHERE T1.id=2 AND T2.id IS NOT NULL; +++--+---+---+--++++ | id | v | id | id_t1 | id_t3 | v| id | v1 | v2 | +++--+---+---+--++++ | 2 | 18 |3 | 2 | 3 |8 | 3 | 56 | 23 | +++--+---+---+--++++ 1 row in set (0.00 sec) On Wed, 2003-01-15 at 16:53, Tab Alleman wrote: > Nice that this came up when it did.. I'm currently struggling with a > three-table join. > > Table1.PK = Table2.FK1 > Table3.PK = Table2.FK2 > > My last effort looks something like: > > SELECT SomeStuff > FROM Table1 AS T1 > LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1 > RIGHT JOIN Table3 AS T3 ON T2.FK2 = T3.PK > WHERE T1.PK=999 > > I want it to return 1 row, but it's returning as many rows as there are > in Table3. Where am I goofing? > > TIA, > Tab > mysql > > -Original Message- > From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 15, 2003 8:30 AM > To: Josh L Bernardini > Cc: [EMAIL PROTECTED] > Subject: Re: three table join > > > I've wanted to post this query example a few times (and I hope I got it > right; mornings aren't my best time) ... multiple JOINs: > > SELECT stuff >FROM table1 > LEFT JOIN table2 > ON table1.fk = table2.pk > LEFT JOIN table3 > ON table2.fk = table3.pk > WHERE other_conditions > ... > > You can repeat that as many levels as you want (performance depends on > indexing and the optimizer). You need to think in terms of what would > be equal to what between tables in the correct result row. So if you > would do a secondary sub-select of "SELECT fk from table2 where ..." > then you end up with a left join like above. > -- Diana Soares - 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: myisamchk warnings -- open tables
Hi, Some time ago i've read that we shouldn't use myisamchk while the mysql server was running. Now, i don't find where in the manual says something about it. I think you should do a "FLUSH TABLES" and ensure that no one touches the tables (including mysqld) before using myisamchk. Coudn't you use "CHECK/OPTIMIZE/REPAIR TABLES" (or whatever you're doing with myisamchk) instead of myisamchk ? I think it's safer... :-) This short text is more specific: http://www.mysql.com/doc/en/myisamchk_syntax.html " Note that if you get an error like: myisamchk: warning: 1 clients is using or hasn't closed the table properly This means that you are trying to check a table that has been updated by the another program (like the mysqld server) that hasn't yet closed the file or that has died without closing the file properly. If you mysqld is running, you must force a sync/close of all tables with FLUSH TABLES and ensure that no one is using the tables while you are running myisamchk. In MySQL Version 3.23 the easiest way to avoid this problem is to use CHECK TABLE instead of myisamchk to check tables. " On Mon, 2003-01-13 at 17:09, havoc wrote: > Since the http://lists.mysql.com/ archives site is down, I'll have to > ask this question without the benifit of the archives... > > I have a (very large) Perl script that has a bad tendency to cause the > following warning from myisamchk: > > myisamchk: MyISAM file /var/lib/mysql//table_name.MYI > myisamchk: warning: 1 clients is using or hasn't closed the table properly > MyISAM-table '/var/lib/mysql//table_name.MYI' is usable but should > be fixed > > It does this for table after table (I'm running multiple instances of > the same database for diffrerent sites). > > It's fine to go back and run myisamchk --recover -- quick, but I'd > rather stop the bleeding than continue applying bandages. > > I have started through the code making sure there is a $sth->finish(); > after every table query/insert/update, but I seem to have made the > problem worse! > > Is there something I should be paying special attention to? Is there > something that I might just plain be missing? > > Many thanks in advance! > > Jody Harris > -- -- Diana Soares - 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: MySQL Select SUM Function
On Mon, 2003-01-13 at 14:11, Stevens, Ryan wrote: > I understand the SQL statement in MySQL to be > "SELECT sum(column name) FROM table;" but I would like to get a > SUM of multiple columns. Is this possible?? Yes, you may use: SELECT sum(c1+c2) FROM table; or even: SELECT sum(table1.v + table2.v) FROM table1,table2 [WHERE ] Don't know if this was what you're looking for... ds - 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: install problem... (permissions?)
[root@localhost ~]# perror 13 Error code 13: Permission denied Also check the permissions and ownership of /usr/local/var . See the "Post installation setup" chapter from the manual. http://www.mysql.com/doc/en/Post-installation.html and http://www.mysql.com/doc/en/File_permissions.html On Sat, 2003-01-11 at 22:12, Stefan Hinz, iConnect (Berlin) wrote: > Christopher, > > > 030111 13:41:11 /usr/local/libexec/mysqld: Can't find file: > > './mysql/host.frm' (errno: 13) > > Did you run the install_db script? If not, MySQL can't find the grant > tables. > > See http://www.mysql.com/doc/en/Post-installation.html for details. > > Regards, > -- > Stefan Hinz <[EMAIL PROTECTED]> > Geschäftsführer / CEO iConnect GmbH <http://iConnect.de> > Heesestr. 6, 12169 Berlin (Germany) > Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 > > - Original Message - > From: "Christopher Bergeron" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Saturday, January 11, 2003 7:29 PM > Subject: install problem... (permissions?) > > > > Hey guys, I'm having a little problem with my new mysql installation. > I > > built from source, and everything built find and installed okay (I > > think). Here's the problem: when I start safe_mysqld it just ends. > > Here's the snippet from my error log: > > > > /usr/local/libexec/mysqld: File './proto2-bin.1' not found (Errcode: > 13) > > 030111 13:41:11 Could not use proto2-bin for logging (error 13) > > 030111 13:41:11 /usr/local/libexec/mysqld: Can't create/write to file > > '/usr/local/var/proto2.pid' (Errcode: 13) > > 030111 13:41:11 /usr/local/libexec/mysqld: Can't find file: > > './mysql/host.frm' (errno: 13) > > 030111 13:41:11 /usr/local/libexec/mysqld: Error on delete of > > '/usr/local/var/proto2.pid' (Errcode: 13) > > 030111 13:41:11 mysqld ended > > > > Is there an ownership or permission problem here? > > > > Can anyone help me out here? > > > > Much thanks in advance, > > CB > > > > > > - > > 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 > -- Diana Soares - 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: Thanks and SQL Syntax help
I don't know if i understood you very well, but here's a try.. mysql> select * from Classes; ++-+ | ID | Name| ++-+ | 1 | XO-312 | | 2 | PA-211a | | 3 | XUL-001 | ++-+ 3 rows in set (0.00 sec) mysql> select * from Workshops order by ClassID,Date; ++-++ | ID | ClassID | Date | ++-++ | 1 | 1 | 2002-05-15 | | 8 | 1 | 2002-09-22 | | 7 | 1 | 2002-10-29 | | 2 | 1 | 2003-02-20 | | 3 | 2 | 2002-05-15 | | 9 | 2 | 2003-01-01 | | 4 | 2 | 2003-02-17 | | 5 | 3 | 2002-05-15 | | 10 | 3 | 2002-12-16 | | 6 | 3 | 2003-01-01 | ++-++ 10 rows in set (0.00 sec) mysql> select ClassID, MIN(Date) min, MAX(Date) max, Classes.Name -> FROM Workshops LEFT JOIN Classes ON (ClassID=Classes.ID) -> GROUP BY ClassID HAVING now() BETWEEN min and max; +-+++-+ | ClassID | min| max| Name| +-+++-+ | 1 | 2002-05-15 | 2003-02-20 | XO-312 | | 2 | 2002-05-15 | 2003-02-17 | PA-211a | +-+++-+ 2 rows in set (0.00 sec) Hope this helps... On Sat, 2003-01-11 at 16:25, Steve Lefevre wrote: > First of, thanks to all who replied to my questions earlier! > > Now I have another problem. I have a table of Classes and Workshops. Each > Class has a number of workshops. Each workshop has a date. > > I have a query that gives me the date range of a class - the min and max > dates of its workshops. > > "select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN > Classes ON ClassID=Classes.ID GROUP BY ClassID;" > > gives me: > > +-+++-+ > | ClassID | MIN(Date) | MAX(Date) | Name| > +-+++-+ > | 56 | 2002-05-15 | 2002-12-29 | XO-312 | > | 408 | 2002-05-15 | 2002-05-17 | PA-211a | > | 600 | 2002-05-15 | 2002-05-16 | XUL-001 | > +-+++-+ > 3 rows in set (0.00 sec) > > Now I want to get *active* classes - WHERE Now() Between MIN(Date) and > Max(Date) -- but I can't figure out where to put the friggin clause. I get > errors all over the place. Can I use the between function with a group by > function? > > select ClassID, MIN(Date), MAX(Date), Classes.Name > FROM Workshops > LEFT JOIN Classes ON ClassID=Classes.ID > WHERE Now() BETWEEN MIN(Date) and MAX(Date) > GROUP BY ClassID; > > What am I doing wrong? > > > > - > 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 > -- Diana Soares - 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: Starting MySQL
You should check the manual: http://www.mysql.com/doc/en/Can_not_connect_to_server.html. It shows the steps you can do to solve the problem. On Thu, 2003-01-09 at 23:45, cam vong wrote: > I'm currently using Red Hat 7.1. I have loaded the apache, php, and mysql > rpm. I seem to be having trouble starting MySQL. It gives me the following > error: Can not connect to localhost MySQL server through socket > /var/lib/mysql/mysql.sock (111). Can some help me, please? > > > > > > _ > Protect your PC - get McAfee.com VirusScan Online > http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 > > > - > 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 > -- Diana Soares - 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: mysql 4.0.8 crashes and refuses to restart after crash.
2002 /lib/libc-2.2.5.so > -rw-r--r--1 root root 2310808 Apr 15 2002 /usr/lib/libc.a > -rw-r--r--1 root root 178 Apr 15 2002 /usr/lib/libc.so > Configure command: ./configure '--disable-shared' >'--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' >'--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' >'--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' >'--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' >'--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' >'--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' >'--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' >'--with-embedded-server' '--enable-thread-safe-client' '--with-comment=Official MySQL >RPM' 'CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 >-fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti >-mpentium' 'CXX=gcc' > -- Diana Soares - 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: May I raise a question?
Your mysql server does not seem to be running... Did you execute safe_mysqld ? Check the "Post installation setup" chapter from the manual. http://www.mysql.com/doc/en/Post-installation.html On Thu, 2003-01-09 at 01:16, [EMAIL PROTECTED] wrote: > Dear Editor, > > After installed the MySQL version 3.23.53, I tried to test the MySQL by > typing the following command, relevant error message appears, please advise > how to fix. > > Command: Display Error Message: > > mysqlshowmysqlshow: Can't connect to MySQL server on > 'localhost' (10061) > > mysqladmin CREATE test mysqladmin: connect to server at 'localhost' failed > error: 'Can't connect to MySQL server on > 'localhost' (10061)' > Check that mysqld is running on localhost and that > the port is 3306. > You can check this by doing 'telnet localhost 3306' > > mysql test ERROR 2003: Can't connect to MySQL server on > 'localhost' (10061) > > telnet localhost 3306Connecting To localhost...Could not open a > connection to host on port 3306 : Connect failed > > Best rgds, > > Seed Chan > > > > Seed Chan > System Services Management > Branch Services > * 2962 7431 > * 29173503 / 29173504 > * [EMAIL PROTECTED] > > > > > - > 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 > -- Diana Soares - 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: Still Cannot Get MySQL To Run
On Wed, 2003-01-08 at 04:55, CM Miller wrote: > Have a new years resolution, to get MySQL to run > Here are the errors that I keep getting... > > I don't understand what this is telling me? > > from /var/log > > 021214 19:13:21 mysqld started > 021214 19:13:21 /usr/local/libexec/mysqld: Can't find > file: './mysql/host.frm' (er > rno: 13) > 021214 19:13:21 mysqld ended [root@cartman mysql]# perror 13 Error code 13: Permission denied > I haven't messed around with this since late Nov., but > I really don't want to give up on this. > > Any ideas? I wasn't subscribed in Nov so i don't know what topics people gave you.. Check permissions and ownership of the file './mysql/host.frm' and directory './mysql' Here's how i have: [root@cartman mysql]# pwd /var/lib/mysql [root@cartman mysql]# [root@cartman mysql]# ll -d mysql drwx--2 mysqlmysql4096 Nov 21 12:24 mysql [root@cartman mysql]# [root@cartman mysql]# ll -d mysql/host.* -rw---1 mysqlmysql8958 Oct 31 18:25 mysql/host.frm -rw---1 mysqlmysql 0 Nov 21 12:24 mysql/host.MYD -rw---1 mysqlmysql1024 Nov 21 12:26 mysql/host.MYI > So, it was recommended to try this-run 'install_db' > from the scripts directory > > So I tried that and here is my output: > > Installing all prepared tables > 030107 22:35:39 /usr/local/libexec/mysqld: Shutdown > Complete > > > To start mysqld at boot time you have to copy > support-files/mysql.server > to the right place for your system > > PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root > USER ! > This is done with: > /usr/local/bin/mysqladmin -u root password > 'new-password' > /usr/local/bin/mysqladmin -u root -h > phoenix.miller.org password 'new-password' > See the manual for more instructions. > > NOTE: If you are upgrading from a MySQL <= 3.22.10 > you should run > the /usr/local/bin/mysql_fix_privilege_tables. > Otherwise you will not be > able to use the new GRANT command! > > You can start the MySQL daemon with: > cd /usr/local ; /usr/local/bin/safe_mysqld & > > You can test the MySQL daemon with the benchmarks in > the 'sql-bench' directory: > cd sql-bench ; run-all-tests > > Please report any problems with the > /usr/local/bin/mysqlbug script! > > The latest information about MySQL is available on the > web at > http://www.mysql.com > Support MySQL by buying support/licenses at > https://order.mysql.com > > > what in the hell am I doing wrong here, this should > not be that hard to set up and run? Nothing, it all went ok. Hope that above helps. ds - 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: Weird WHERE clause possible?
Try: SELECT * FROM Students WHERE CONCAT(LastName, ', ', FirstName) LIKE 'Smith, J%' or SELECT * FROM Students WHERE CONCAT(LastName, ', ', SUBSTRING(FirstName,1,1))='Smith, J' On Tue, 2003-01-07 at 15:37, Lefevre, Steven wrote: > I'm developing a web-site database. There is a form on one page where a user > can search for a student by typing in a last name and submitting it. > > Sometimes there can be more than one student with the same last name, so I > want the user to be able to type in "Smith, J" (where normally they type > 'Smith'). > > The problem (as I see it) is that I'm storing the last name and the first > name in two seperate fields. I can make an SQL statement like "Select * from > Students Where LastName Like "Smith%";", but can I make something like > > "SELECT * FROM Students WHERE (LastName, ", ", FirstName) AS Name LIKE > "Smith, J%";" > ? -- Diana Soares - 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: max_connect_errors & wait_timeout problem?
On Wed, 2002-07-31 at 19:09, Troy Hakala wrote: > I decreased wait_timeout (from 28,800 to 300) to kill off sleeping threads. > This morning, the server stopped accepting connections and gave me this > error: > > ERROR 1129: Host 'hostname' is blocked because of many connection errors. > Unblock with 'mysqladmin flush-hosts' > > flush-hosts worked and I increased the max_connect_errors from 10 to 1000 > to kind of prevent this in the future. But what causes this error? If you > read the comments on http://www.mysql.com/doc/B/l/Blocked_host.html it > seems that decreasing wait_timeout does cause this. Is there a bug in > mysql where it thinks that killing off sleeping threads is really a > connect error? I'm using 3.23.45, btw. I'm not shure, but i think it is not a bug. I think a connection is only successfull when it is closed by the same entity who opened it. If it was mysql closing the connection, then it should increment its counter of connection_errors. It tells that someone opened a connection and did not close it. It's only an opinion. -- Diana Soares - 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: Query problem
Forget everything i said, now i realize that i didn't understand the problem very well. Francisco Reinaldo is right, use LEFT JOIN. Sorry for having replied that! On Thu, 2002-07-25 at 20:40, Kevin wrote: > Hello, > > I have a query that runs perfectly until one of the items has no value or is > set to 0: > > SELECT > item.*, color.Name AS COLOR, > shapecode.Shape AS SHAPE, > clarity.Name AS CLARITY > FROM item, color, shapecode, clarity > WHERE > clarity.ID = item.CLARITY_ID > AND shapecode.ID = item.SHAPE_ID > AND item.COLOR_ID = color.ID > AND ITEM_ID='MA603' > > If the item.CLARITY_ID has no value or a value of 0 (which there is no > defined value for in the clarity table), then the query fails. > > Can anything be done without adding more login in the code? > > Thanks -- Diana Soares - 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: mysql sub query
On Fri, 2002-07-26 at 15:46, Aman Raheja wrote: > I have the following situation > > IDGRP PRC CHECK > 1 3 10.0Y > 2 2 12.0Y > 3 3 32.0N > 4 1 5.0 N > 5 1 7.0 Y > 6 2 9.0 N > 7 3 17.0N > 8 1 11.0N > > Problem is that the sql version I am running does not support sub-queries. > Is there another way of getting around the following situation to wirte a > query so that > > select * from tab_name where > one item from each GRP 1, 2, and 3 - whichever is highest priced (PRC) and > has CHECK set to N. Hi, Try this: SELECT ID,GRP,max(PRC) FROM tab_name WHERE GRP IN (1,2,3) and CHECK='N' GROUP BY GRP; Hope this helps, -- Diana Soares - 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: Fw: Complex SQL assistance
Take a look at REPLACE: http://www.mysql.com/doc/R/E/REPLACE.html On Fri, 2002-07-26 at 10:17, Corey Wallis wrote: > Peoples, > > I'm currently trying to work out if this is possible by SQL. I have the need > to use SQL and only SQL to achieve the following. > > If a record exists and meets a certain criteria (i.e. field X = '1234') then > update the record. If the record doesn't exist then insert it. > > For reasons too complex to go into at this stage using anything except SQL > is not possible. > > Any and all suggestions welcome. > > -Corey -- Diana Soares - 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: Need help with Tables/Attributes
Hi, I suppose that what you want would be something like: Mysql> create table test(ID int(4), Name varchar(20), Sex varchar(1)); Mysql> alter table test change Name FullName varchar(40); You should read this for more information about ALTER TABLE: http://www.mysql.com/doc/A/L/ALTER_TABLE.html On Thu, 2002-07-25 at 23:45, Nitesh Divecha wrote: > Hello All, > > I need help with tables. I have created my table with some attributes in > it. > > I need to modify my table attributes, can any one help me with the > modify command or can give me the short cut to the web. > > Example: > Mysql> create table test(ID int(4), Name varchar(20), Sex varchar(1)); > > Now I need to change the attribute NAME TO Full name, HOW? > > Please help, Thanking in Advance. > > Regards > Nitesh -- Diana Soares - 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: Query problem
Hi, On Thu, 2002-07-25 at 20:40, Kevin wrote: > Hello, > > I have a query that runs perfectly until one of the items has no value or is > set to 0: > > SELECT > item.*, color.Name AS COLOR, > shapecode.Shape AS SHAPE, > clarity.Name AS CLARITY > FROM item, color, shapecode, clarity > WHERE > clarity.ID = item.CLARITY_ID > AND shapecode.ID = item.SHAPE_ID > AND item.COLOR_ID = color.ID > AND ITEM_ID='MA603' > > If the item.CLARITY_ID has no value or a value of 0 (which there is no > defined value for in the clarity table), then the query fails. > > Can anything be done without adding more login in the code? Hi, I don't know if CLARITY_ID can be NUll, so this will be just a guess. Try to use the "NULL safe equal" operator '<=>' between clarity.ID and item.CLARITY_ID like ... WHERE clarity.ID <=> item.CLARITY_ID ... Info: 6.3.1.2 Comparison Operators http://www.mysql.com/doc/C/o/Comparison_Operators.html --- - 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: inner join query don't work
Hi, Why do you think this query is wrong ? Does it give a message error ? Doesn't return what you expected ? We can't help if you don't tell us what the problem is. On Fri, 2002-07-26 at 03:23, gregor gede wrote: > hi what's wrong with this query? is it because mysql > doesn't support multiple table selection? I use > mysql-3.23.49a. > > SELECT Buku.Judul_Buku, Buku.ISBN, Buku.Kolasi, > Buku.Abstraksi, Pengarang.Pengarang, > Penerbit.Penerbit, Jenis_Buku.Jenis, Subjek.Subjek > FROM Buku, Pengarang_Buku, Pengarang, Penerbit, > Jenis_Buku, Subjek_Buku, Subjek > WHERE Buku.ID_Buku = Pengarang_Buku.ID_Buku and > Pengarang_Buku.No_Pengarang = Pengarang.No_Pengarang > and Buku.No_Penerbit = Penerbit.No_Penerbit and > Buku.No_Jenis_Buku = Jenis_Buku.No_Jenis_Buku and > Buku.ID_Buku = Subjek_Buku.ID_Buku and > Subjek_Buku.No_Subjek = Subjek.No_Subjek and > Buku.Judul_Buku like '%pascal%' and > Pengarang.Pengarang like '%$lukito%' and Subjek.Subjek > like '%$computer%' > > than's a lot. > -- Diana Soares - 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: Query problem..
Hi, If you want the last date, isn't it just: SELECT clientid,max(datedone) FROM table_name WHERE done = 'true' and x = '' GROUP BY clientid; ? Hope it helps, On Fri, 2002-07-26 at 08:27, Nixarlidis Aris wrote: > Hi, > I face the following situation.I have a number of clients-each with a > unique clientid.I have a table with some records -one after > the other in time order(datedone field) with actions checked as 'done' or > not 'done', > in the 'done' field which is either true or false.What I want to to, is to > specify in my query the last record-date that has an action marked as > done at the same time when the value x='' for every clientid.I have > tried what you see but didin't help. > > SELECT clientid FROM table_name WHERE datedone = 'max(datedone)' and done > = 'true' and x = '' group by clientid; > > I am new to all this, so any help would be usefull > Thanks -- Diana Soares - 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: Mysqld crash
On Fri, 2002-07-26 at 18:43, Mike Wexler wrote: > > > Diana Soares wrote: > > >Hi, just sending a reply in case that someone else has the same problem. > >I solved the problem by decreasing the key-buffer from 320M to 288M. > > > We tried that among many other things. It turns out that their is some > kind of problem related to temporary tables. MySQL AB is investigating. Yes, it could be that. I use many temporary tables to generate my final reports. I'll wait... Until there, i'll keep the key-buffer as is and "pray" for good luck! :-) Thank you for reply, > >On Wed, 2002-07-03 at 12:24, Diana Soares wrote: > > > > > >>Hi, > >> > >>I have 2 machine dual-processor Pentium III, with 1G of memory. > >>They have the same software, same architecture, with one-way replication > >>beetween. Versions: > >> > >>[root@localhost tmp]# mysql -V > >>mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686) > >>[root@localhost tmp]# cat /etc/redhat-release > >>Red Hat Linux release 7.3 (Valhalla) > >> > >>Since i've installed mysql 3.23.51 (mysql binaries) that i'm having some > >>problems, the worst is the slave's mysqld crashes all days. What > >>happens: > >> > >>Every day, a cron job in the slave starts at 3:30AM to generate some > >>reports. The maximum load is about 2.0. Mysqld daemon crashes and > >>restarts itself. > >> > >>All queries in this cron job are done in the master. The slave only > >>replicates... I don't understand why the slave keeps crashing. The > >>master logs are clean, with no error at all, the reports are ok. > >> > >> > >>* The log error at the slave: > >> > >>mysqld got signal 11; > >>This could be because you hit a bug. It is also possible that this > >>binary or one of the libraries it was linked agaist is corrupt, > >>improperly built,or misconfigured. This error can also be caused by > >>malfunctioning hardware.We will try our best to scrape up some info that > >>will hopefully help diagnose the problem, but since we have already > >>crashed, something is definitely wrong and this may fail > >> > >>key_buffer_size=335540224 > >>record_buffer=2093056 > >>sort_buffer=2097144 > >>max_used_connections=1 > >>max_connections=150 > >>threads_connected=0 > >>It is possible that mysqld could use up to > >>key_buffer_size + (record_buffer + sort_buffer)*max_connections = 941474 > >>K > >>bytes of memory > >>Hope that's ok, if not, decrease some variables in the equation > >> > >>Attempting backtrace. You can use the following information to find out > >>where mysqld died. If you see no messages after this, something went > >>terribly wrong... > >>Stack range sanity check OK, backtrace follows: > >>0x806edf4 > >>0x811fd28 > >>0x81050f3 > >>0x810410f > >>0x8103df9 > >>0x80b5f04 > >>0x809524c > >>0x80943b7 > >>0x80940c3 > >>0x808cf67 > >>0x80760ce > >>0x8079a8c > >>0x80cfb31 > >>0x80d1249 > >>Stack trace seems successful - bottom reached > >>... > >>Trying to get some variables. > >>Some pointers may be invalid and cause the dump to abort... > >>thd->query at 0x8287e59 = CREATE TEMPORARY TABLE IF NOT EXISTS tmp_uv > >>SELECT campaign_id , user, count(user) as views FROM Log_Impr_all > >>WHERE campaign_id IN (9) GROUP BY campaign_id , user HAVING views>0 > >>thd->thread_id=18 > >>... > >> > >> > >>* Stack resolved: > >> > >>0x806edf4 handle_segfault__Fi + 428 > >>0x811fd28 pthread_sighandler + 184 > >>0x81050f3 _hp_movelink + 11 > >>0x810410f _hp_write_key + 595 > >>0x8103df9 heap_write + 73 > >>0x80b5f04 write_row__7ha_heapPc + 72 > >>0x809524c end_update__FP4JOINP13st_join_tableb + 440 > >>0x80943b7 sub_select__FP4JOINP13st_join_tableb + 255 > >>0x80940c3 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 415 > >>0x808cf67 > >>>mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UiP13select_result > + 4055 > >>0x80760ce mysql_execute_command__Fv + 2570 > >>0x8079a8c mysql_parse__FP3THDPcUi + 216 > >>0x80cfb31 exec_event__FP3THDP6st_netP14st_master_infoi + 1133 > >>0x80d1249 handle_slave__FPv + 2309 > >> > >> > >>(i don't understand what this means..) > >>Thank you for reading this, i hope someone can give me a light. > >> > >> > > > > > > > > -- Diana Soares Websolut - Soluções Internet Email: [EMAIL PROTECTED] - 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: Mysqld crash
Hi, just sending a reply in case that someone else has the same problem. I solved the problem by decreasing the key-buffer from 320M to 288M. On Wed, 2002-07-03 at 12:24, Diana Soares wrote: > Hi, > > I have 2 machine dual-processor Pentium III, with 1G of memory. > They have the same software, same architecture, with one-way replication > beetween. Versions: > > [root@localhost tmp]# mysql -V > mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686) > [root@localhost tmp]# cat /etc/redhat-release > Red Hat Linux release 7.3 (Valhalla) > > Since i've installed mysql 3.23.51 (mysql binaries) that i'm having some > problems, the worst is the slave's mysqld crashes all days. What > happens: > > Every day, a cron job in the slave starts at 3:30AM to generate some > reports. The maximum load is about 2.0. Mysqld daemon crashes and > restarts itself. > > All queries in this cron job are done in the master. The slave only > replicates... I don't understand why the slave keeps crashing. The > master logs are clean, with no error at all, the reports are ok. > > > * The log error at the slave: > > mysqld got signal 11; > This could be because you hit a bug. It is also possible that this > binary or one of the libraries it was linked agaist is corrupt, > improperly built,or misconfigured. This error can also be caused by > malfunctioning hardware.We will try our best to scrape up some info that > will hopefully help diagnose the problem, but since we have already > crashed, something is definitely wrong and this may fail > > key_buffer_size=335540224 > record_buffer=2093056 > sort_buffer=2097144 > max_used_connections=1 > max_connections=150 > threads_connected=0 > It is possible that mysqld could use up to > key_buffer_size + (record_buffer + sort_buffer)*max_connections = 941474 > K > bytes of memory > Hope that's ok, if not, decrease some variables in the equation > > Attempting backtrace. You can use the following information to find out > where mysqld died. If you see no messages after this, something went > terribly wrong... > Stack range sanity check OK, backtrace follows: > 0x806edf4 > 0x811fd28 > 0x81050f3 > 0x810410f > 0x8103df9 > 0x80b5f04 > 0x809524c > 0x80943b7 > 0x80940c3 > 0x808cf67 > 0x80760ce > 0x8079a8c > 0x80cfb31 > 0x80d1249 > Stack trace seems successful - bottom reached > ... > Trying to get some variables. > Some pointers may be invalid and cause the dump to abort... > thd->query at 0x8287e59 = CREATE TEMPORARY TABLE IF NOT EXISTS tmp_uv > SELECT campaign_id , user, count(user) as views FROM Log_Impr_all > WHERE campaign_id IN (9) GROUP BY campaign_id , user HAVING views>0 > thd->thread_id=18 > ... > > > * Stack resolved: > > 0x806edf4 handle_segfault__Fi + 428 > 0x811fd28 pthread_sighandler + 184 > 0x81050f3 _hp_movelink + 11 > 0x810410f _hp_write_key + 595 > 0x8103df9 heap_write + 73 > 0x80b5f04 write_row__7ha_heapPc + 72 > 0x809524c end_update__FP4JOINP13st_join_tableb + 440 > 0x80943b7 sub_select__FP4JOINP13st_join_tableb + 255 > 0x80940c3 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 415 > 0x808cf67 > >mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UiP13select_result > + 4055 > 0x80760ce mysql_execute_command__Fv + 2570 > 0x8079a8c mysql_parse__FP3THDPcUi + 216 > 0x80cfb31 exec_event__FP3THDP6st_netP14st_master_infoi + 1133 > 0x80d1249 handle_slave__FPv + 2309 > > > (i don't understand what this means..) > Thank you for reading this, i hope someone can give me a light. -- Diana Soares - 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: mysqldump question
On Tue, 2002-07-23 at 21:04, Bill Bernat wrote: > Hey, thanks *so* much for checking into this. > > Your message did help. In case you're curious. First, I had the admin > create a 777 dump directory from root. Still couldn't write out. Then, > though, I just tried going to /tmp as you had done, and that was fine. > /tmp -- I feel like an idiot, but at least I'm an idiot who can dump the > database now:) > > Also, do you happen to know how old is 3.23.21-beta-log? Our MYSQL is > old, but I can't find out how old, is there a version history online > anywhere? Check this: http://www.mysql.com/documentation/mysql/bychapter/manual_News.html#News-3.23.x It shows some release dates. > -Original Message- > From: Diana Soares [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, July 16, 2002 3:22 AM > To: Bill Bernat > Cc: [EMAIL PROTECTED] > Subject: Re: mysqldump question > > > On Tue, 2002-07-16 at 02:31, Bill Bernat wrote: > > Question: is there anything I need to be aware of when writing dump > > files to a local directory for my user, I'm having the following > > problem. > > > > 1. I create a directory in my own home directory (linux, red hat 7.2) > > ~/dumps and give it 777 permissions > > > > 2. I run " /usr/local/mysql/bin/mysqldump -uroot -p<> --tab > > ~/dumps dbname table1 table2" > > > > 3. The .sql file for table1 gets created, correctly, in the dumps > > directory fine. > > > > 4. mysqldump pukes when trying to write the .txt file: > > > > /usr/local/mysql/bin/mysqldump: Got error: 1: Can't create/write to > > file '/home/bbernat/dumps/customer_relations_database.txt' (Errcode: > > 13) when executing 'SELECT INTO OUTFILE' > > > > QUESTION: > > What have I done wrong and how can I fix it? > > Hi, > > I was curious about your problem and decided to try it. > As user root (in the filesystem, redhat7.2), i created the dir ~/dumps > with 777 permissions and have run > root@cartman:~# mysqldump -uroot -p --tab ~/dumps/ test > (root mysqluser has FILE permissions). It gave me the same error. > > Then i tried with a common filesystem user (dsoares): dsoares@cartman:~$ > mysqldump -uroot -p --tab ~/dumps/ test > > And it worked! > I went to see my /root permissions and it was: dsoares@cartman:~$ ll -d > /root/ > drwxr-x--- 31 root root 4096 Jul 16 11:09 /root/ > > So the problem was there! mysql user doesn't have permissions to access > /root directory. Maybe this is your problem. > > (also as root i tried with /tmp/dumps directory, 777 permissions, and it > worked.) > -- Diana Soares - 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: Mysql wil lnot load
Error code 13: Permission denied Check if the user that runs mysql (usually user mysql) has permissions on those directories. Usually, the ownership is mysql. An example (i also have the mysql group): [root@cartman mysql]# pwd /var/lib/mysql [root@cartman mysql]# ll -dh . .. *.* mysql drwxr-xr-x 21 mysqlmysql Jul 1 22:44 . drwxr-xr-x 15 root root Apr 1 15:55 .. -rw-rw1 mysqlmysql May 23 17:54 cartman-bin.009 -rw-rw1 mysqlmysql May 23 18:15 cartman-bin.010 -rw-rw1 mysqlmysql May 23 18:20 cartman-bin.011 -rw-rw1 mysqlmysql May 23 18:15 cartman-bin.index -rw-rw-r--1 mysqlmysql Jul 8 14:43 cartman.err -rw-rw-r--1 mysqlmysql Jan 16 20:10 cartman.log -rw-rw1 mysqlmysql Jun 27 03:05 cartman.pid -rw-rw-r--1 mysqlmysql Jul 16 11:10 cartman-slow.log -rw-rw1 mysqlmysql Jun 27 03:05 master.info drwx--x--x2 mysqlmysql Dec 5 2001 mysql srwxrwxrwx1 mysqlmysql Jun 27 03:05 mysql.sock [root@cartman mysql]# On Tue, 2002-07-16 at 11:25, Aaron Axelsen wrote: > The instructions where followed from the website exaactly and all the > file premissions are correct, but I still get the following errors in > ther log file: > > > 020716 05:23:59 mysqld started > ^G/usr/local/mysql/libexec/mysqld: File './Alpha-bin.1' not found > (Errcode: 13) > 020716 5:23:59 Could not use Alpha-bin for logging (error 13) > 020716 5:23:59 /usr/local/mysql/libexec/mysqld: Can't create/write to > file '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) > 020716 5:23:59 /usr/local/mysql/libexec/mysqld: Can't find file: > './mysql/host.frm' (errno: 13) > 020716 5:23:59 /usr/local/mysql/libexec/mysqld: Error on delete of > '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) > 020716 05:23:59 mysqld ended > > > --- > Aaron Axelsen > AIM: AAAK2 > Email: [EMAIL PROTECTED] > URL: www.amadmax.com > > "It said, ""Insert disk #3,"" but only two will fit!" > "One picture is worth 128K words." > > > -Original Message- > From: Georg Richter [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, July 16, 2002 3:29 AM > To: Aaron Axelsen; [EMAIL PROTECTED] > Subject: Re: Mysql wil lnot load > > > On Tuesday, 16. July 2002 07:11, Aaron Axelsen wrote: > > Hi, > > > 020715 23:40:08 mysqld started > > 020715 23:40:08 /usr/local/mysql/libexec/mysqld: Can't create/write > > to file '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) 020715 23:40:08 > > > /usr/local/mysql/libexec/mysqld: Can't find file: './mysql/host.frm' > > (errno: 13) 020715 23:40:08 /usr/local/mysql/libexec/mysqld: Error on > > > delete of '/usr/local/mysql/var/Alpha.pid' (Errcode: 13) > > 020715 23:40:08 mysqld ended > > > > any suggestions? > > perror 13 ==> Error code 13: Permission denied > So you have to change the file privileges, or you should run mysqld > under > another user. > > Regards > > Georg > > > - > 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 > -- Diana Soares Websolut - Soluções Internet Email: [EMAIL PROTECTED] - 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: mysqldump question
On Tue, 2002-07-16 at 02:31, Bill Bernat wrote: > Question: is there anything I need to be aware of when writing dump > files to a local directory for my user, I'm having the following > problem. > > 1. I create a directory in my own home directory (linux, red hat 7.2) > ~/dumps and give it 777 permissions > > 2. I run " /usr/local/mysql/bin/mysqldump -uroot -p<> --tab ~/dumps > dbname table1 table2" > > 3. The .sql file for table1 gets created, correctly, in the dumps directory > fine. > > 4. mysqldump pukes when trying to write the .txt file: > > /usr/local/mysql/bin/mysqldump: Got error: 1: Can't create/write to file > '/home/bbernat/dumps/customer_relations_database.txt' (Errcode: 13) when > executing 'SELECT INTO OUTFILE' > > QUESTION: > What have I done wrong and how can I fix it? Hi, I was curious about your problem and decided to try it. As user root (in the filesystem, redhat7.2), i created the dir ~/dumps with 777 permissions and have run root@cartman:~# mysqldump -uroot -p --tab ~/dumps/ test (root mysqluser has FILE permissions). It gave me the same error. Then i tried with a common filesystem user (dsoares): dsoares@cartman:~$ mysqldump -uroot -p --tab ~/dumps/ test And it worked! I went to see my /root permissions and it was: dsoares@cartman:~$ ll -d /root/ drwxr-x--- 31 root root 4096 Jul 16 11:09 /root/ So the problem was there! mysql user doesn't have permissions to access /root directory. Maybe this is your problem. (also as root i tried with /tmp/dumps directory, 777 permissions, and it worked.) -- Diana Soares - 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: Help with Insert.. Select From... Query
On Sun, 2002-07-14 at 03:59, karl wrote: > Hello, > I need help. I am trying to insert into TABLEA records from TABLEB where the >records do not exist in TABLEA (the primary key in TABLEA works fine). I am trying >to limit the result set each time (as an example to 10 records from TABLEB). > The first time I perform the query, I get the number of records limited (10), but >when I perform the same query again - HOPING TO GET 10 NEW RECORDS - I get none. I'm >left with a total of 10 records in TABLEA. I know what is happening, I am looking up >the exact same records (using same where clause) each time in TABLEB, and because the >query is limited (to 10 for example), no new records are retrieved for TABLEA. But >this is precisely what I need to do, and that is fill TABLEA 10 records at a time >from TABLEB. > There are certain limitations, as in I can't update TABLEB, TABLEB must be left >alone and can only be queried from. > I thought a complex left join would do the trick, but it seems that you are not able >to perform a query of this sort when doing an insert into... select from.. I've >included this code, along with the error produced. If you have any ideas, please >shoot them my way! > > Thanks, Karl > > insert into rep > (repid,submitid,lastactv,status) > select 88,submit_idx2.submitid,now(),1 > from submit_idx2 > left join rep on submit_idx2.submitid = rep.submitid > where rep.submitid is null > and TO_DAYS(NOW()) - TO_DAYS(submitdate) <= 1 > order by submit_idx2.submitid asc > limit 10; > > The above SQL errors. Here is that error message: > > C:\mysql\My Queries>c:\mysql\bin\mysql > earch 0 ERROR 1066 at line 5: Not unique table/alias: 'rep' Hi, You can't use the table you are "updating" in the select clause: http://www.mysql.com/doc/I/N/INSERT_SELECT.html "* The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query because it's forbidden in ANSI SQL to SELECT from the same table into which you are inserting."... I don't know if i understood you, but if you can only insert 10 records per time, can't you use: insert into rep (repid,submitid,lastactv,status) select 88,submit_idx2.submitid,now(),1 from submit_idx2 order by submit_idx2.submitid asc limit $var,10; where $var will be: * 0 for the first time you run the query, * 10 for the second time, * 20 for the third -- Diana Soares - 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: NATURAL JOIN
On Fri, 2002-07-12 at 22:41, Richard Clarke wrote:
> So this works as expected,
>
> mysql> create table test_1 (id int,value char);
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> create table test_2 (id int,val char);
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into test_1 values (1,a),(2,b),(3,c);
> ERROR 1054: Unknown column 'a' in 'field list'
> mysql> insert into test_1 values (1,'a'),(2,'b'),(3,'c');
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> insert into test_2 values (2,'d'),(3,'e'),(4,'f');
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Duplicates: 0 Warnings: 0
>
> mysql> select * from test_1 NATURAL JOIN test_2;
> +--+---+--+--+
> | id | value | id | val |
> +--+---+--+--+
> |2 | b |2 | d|
> |3 | c |3 | e|
> +--+---+--+--+
> 2 rows in set (0.00 sec)
>
> --
>
> But what is wrong with this:
>
> CREATE TABLE `items` (
> `item_id` int(11) NOT NULL auto_increment,
> `centro_id` int(11) NOT NULL default '0',
> `name` varchar(50) default NULL,
> `description` varchar(255) default NULL,
> `url` varchar(255) default NULL,
> `directory_id` int(11) default NULL,
> `cost` int(11) default NULL,
> PRIMARY KEY (`item_id`),
> KEY `directory_id` (`directory_id`)
> ) TYPE=MyISAM;
>
> CREATE TABLE `shop_directory` (
> `directory_id` int(11) NOT NULL auto_increment,
> `short_name` varchar(32) default NULL,
> `long_name` varchar(128) default NULL,
> `description` text,
> `parent_id` int(11) NOT NULL default '0',
> PRIMARY KEY (`directory_id`)
> ) TYPE=MyISAM;
>
> INSERT INTO items VALUES (5,58,'Swim','wet','http://www.bri.com/wet',3,50);
> INSERT INTO items VALUES
> (4,58,'Beach','beach','http://www.bri.com/beach',3,50);
> INSERT INTO items VALUES (7,58,'Bed','bed','http://www.bri.com/bed',2,60);
> INSERT INTO items VALUES (9,58,'sun','sun sun sun','www.bri.com/sun',1,34);
>
> INSERT INTO shop_directory VALUES ('','a','aaa','a',0);
> INSERT INTO shop_directory VALUES ('','b','bbb','b',1);
> INSERT INTO shop_directory VALUES ('','c','ccc','c',2);
>
> mysql> select * from items NATURAL JOIN shop_directory;
> Empty set (0.00 sec)
>
> What gives... why isn't it joining on directory_id. Using an inner join
> with where condition it works fine.. but INNER JOIN should work also.. no?
>
> "The NATURAL [LEFT] JOIN of two tables is defined to be semantically
> equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that
> names all columns that exist in both tables."
>
> Richard
Hi,
See: "..with a USING clause that names all columns that exist in
both tables."
Your query is giving an empty result because it is using 2 columns that
exist in both tables: directory_id and description, and the description
value in both tables isn't equal. If you change the column name
"description" in one of your tables it will work -- or just do the
select * from items JOIN shop_directory USING (directory_id);
Your first example worked because the only column used to join was the
id (since you gave different names to the other column).
--
Diana Soares
-
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: select query
MySQL doesn't support sub-selects...You must use joins and temporary tables. Check: http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html it gives some info about it. On Fri, 2002-07-12 at 16:37, Narcis GRATIANU wrote: > > > When I try this: > > SELECT article, dealer, price > FROM shop s1 > WHERE price=(SELECT MAX(s2.price) > FROM shop s2 > WHERE s1.article = s2.article) LIMIT 0, 100 > > I got this error message: > > You have an error in your SQL syntax near 'SELECT MAX(s2.price) > FROM shop s2 > WHERE s1.article' at line 3 > > > My shop table contains this data: > +-++---+ > > | article | dealer | price | > > +-++---+ > |0001 | A | 3.45 | > |0001 | B | 3.99 | > |0002 | A | 10.99 | > |0003 | B | 1.45 | > |0003 | C | 1.69 | > |0003 | D | 1.25 | > |0004 | D | 19.95 | > +-++---+ > > and the table was created with this commnad: > > CREATE TABLE shop ( > article INT(4) UNSIGNED ZEROFILL DEFAULT '' NOT NULL, > dealer CHAR(20) DEFAULT '' NOT NULL, > price DOUBLE(16,2) DEFAULT '0.00' NOT NULL, > PRIMARY KEY(article, dealer)); > > Thank You ! > -- Diana Soares - 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: server_error description needed
On Thu, 2002-07-11 at 06:47, [EMAIL PROTECTED] wrote: > Hello MySQL list, > > I have set up a master-slave replication and am reading the following in the > .err file of the slave: > > Error reading packet from server (server_errno=1159) > > Where can I find a list of server_errno's and their text descriptions ? http://www.mysql.com/doc/n/o/node_556.html 8.4.3.52 Description .. Client error message numbers are listed in the MySQL `errmsg.h' header file. Server error message numbers are listed in `mysqld_error.h'. In the MySQL source distribution you can find a complete list of error messages and error numbers in the file `Docs/mysqld_error.txt'. For me, those files are in: /usr/include/mysql/errmsg.h /usr/include/mysql/mysqld_error.h /usr/src/redhat/BUILD/mysql-3.23.50/Docs/mysqld_error.txt > Is it possible perhaps in an sql query ? I think it's not possible... -- Diana Soares - 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
Mysqld crash
Hi, I have 2 machine dual-processor Pentium III, with 1G of memory. They have the same software, same architecture, with one-way replication beetween. Versions: [root@localhost tmp]# mysql -V mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686) [root@localhost tmp]# cat /etc/redhat-release Red Hat Linux release 7.3 (Valhalla) Since i've installed mysql 3.23.51 (mysql binaries) that i'm having some problems, the worst is the slave's mysqld crashes all days. What happens: Every day, a cron job in the slave starts at 3:30AM to generate some reports. The maximum load is about 2.0. Mysqld daemon crashes and restarts itself. All queries in this cron job are done in the master. The slave only replicates... I don't understand why the slave keeps crashing. The master logs are clean, with no error at all, the reports are ok. * The log error at the slave: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built,or misconfigured. This error can also be caused by malfunctioning hardware.We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=335540224 record_buffer=2093056 sort_buffer=2097144 max_used_connections=1 max_connections=150 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 941474 K bytes of memory Hope that's ok, if not, decrease some variables in the equation Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x806edf4 0x811fd28 0x81050f3 0x810410f 0x8103df9 0x80b5f04 0x809524c 0x80943b7 0x80940c3 0x808cf67 0x80760ce 0x8079a8c 0x80cfb31 0x80d1249 Stack trace seems successful - bottom reached ... Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x8287e59 = CREATE TEMPORARY TABLE IF NOT EXISTS tmp_uv SELECT campaign_id , user, count(user) as views FROM Log_Impr_all WHERE campaign_id IN (9) GROUP BY campaign_id , user HAVING views>0 thd->thread_id=18 ... * Stack resolved: 0x806edf4 handle_segfault__Fi + 428 0x811fd28 pthread_sighandler + 184 0x81050f3 _hp_movelink + 11 0x810410f _hp_write_key + 595 0x8103df9 heap_write + 73 0x80b5f04 write_row__7ha_heapPc + 72 0x809524c end_update__FP4JOINP13st_join_tableb + 440 0x80943b7 sub_select__FP4JOINP13st_join_tableb + 255 0x80940c3 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 415 0x808cf67 mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UiP13select_result + 4055 0x80760ce mysql_execute_command__Fv + 2570 0x8079a8c mysql_parse__FP3THDPcUi + 216 0x80cfb31 exec_event__FP3THDP6st_netP14st_master_infoi + 1133 0x80d1249 handle_slave__FPv + 2309 (i don't understand what this means..) Thank you for reading this, i hope someone can give me a light. -- Diana Soares - 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: left join and three ids problem
Hi, try this: SELECT courses.coursetitle,reservationid FROM courses,applicant LEFT JOIN reservation ON (reservation.applicantid = applicant.applicantid AND reservation.coursesid = courses.coursesid) WHERE applicant.applicantid = 1 This gives you all courses an reservationid>0 if applicant has signed, NULL if hasn't signed. On Thu, 2002-04-25 at 22:47, tgharris wrote: > Hi -- > > I have been trying to solve this problem with a left join, and wonder what > I am missing: > > I have three tables: > - applicant (applicantid firstname lastname etc) > - courses(coursesid coursetitle etc) > - reservation (reservationid, applicantid,coursesid etc) > > What I want to do is get a list of the courses an applicant has signed > up for AND the list of courses he hasn't (from the reservation table). > so far I thought a left join would work; however since there is more > than one > applicant in the reservation table, using NULL and NOT NULL don't work, > neither does WHERE reservation.applicantid= '1' (with the '1' to be > changed to the the applicant's id number) > > this is as close as I have gotten(using two queries- the first query > works: > first query: > SELECT courses.coursetitle, courses.coursesid from courses > LEFT JOIN > reservation ON courses.coursesid=reservation.coursesid where > reservation.applicantid = 1 > order by coursesid > > +--+---+ > | coursetitle | coursesid | > +--+---+ > | dreamweaver1 | 3 | > | coursename1 |13 | > | cname12 |14 | > +--+---+ > > but I have not suceeded in getting the courses the applicant has NOT > signed up for... > thes query doesn't work, since it gives courses other applicants have > signed up for that are the same as applicant 1...: > > second query: > select courses.coursesid, courses.coursetitle > from courses > LEFT JOIN reservation ON courses.coursesid=reservation.coursesid > where reservation.applicantid != 1 group by coursesidorder by coursesid > > I looked into temp tables and > select as well, but didn't get any closer. It seems one query should solve > this. Hopefully someone with more > experience can help. > > thanks, > > thomas > > (sql 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 > -- Diana Soares Websolut - Soluções Internet Email: [EMAIL PROTECTED] - 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: Help with MySQL: Bind on unix socket error
On Tue, 2002-02-05 at 02:42, Joe Villari wrote: > I'm hoping I can get some help here, I'm trying to get MySQL running > on YellowDog Linux 2.4.17 kernel. I've installed the mysql, > mysql-server and mysql-devel-3.23.32-1.7a rpms from my install CD. > Ran mysql_install_db then changed the group and owner on > /var/lib/mysql to root. I think that, at least, the owner of /var/lib/mysql should be "mysql". This way, "mysql" can create the /var/lib/mysql/mysql.sock Hope this helps.. Also, be shure that there is no other mysqld process runnig. -- Diana Soares (sql) - 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
[Fwd: Compile options]
-Forwarded Message- > From: Diana Soares <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Compile options > Date: 08 Feb 2002 15:35:57 + > > Hi, > > Is there a way of knowing with wich options mysql was compiled ? > Thanx, > > -- > Diana Soares > > (sql) > > - > 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 > -- Diana Soares (sql) - 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
Compile options
Hi, Is there a way of knowing with wich options mysql was compiled ? Thanx, -- Diana Soares (sql) - 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
[Fwd: Compile options]
-Forwarded Message- > From: Diana Soares <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Compile options > Date: 08 Feb 2002 15:35:57 + > > Hi, > > Is there a way of knowing with wich options mysql was compiled ? > Thanx, > > -- > Diana Soares > > (sql) > > - > 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 > -- Diana Soares (sql) - 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
Compile options
Hi, Is there a way of knowing with wich options mysql was compiled ? Thanx, -- Diana Soares (sql) - 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: visualizing my queries
On Wed, 2002-02-06 at 11:52, Nick Wilson wrote: > > You can always use INSERT for the "content" table and REPLACE for the > > "author" table. > > I don't think that would work as it will change the AuthId and effect > any other 'tips' I've written. > Each author should have one entry in 'author' and as many in 'content' > as tips they have written. > Am I talking rubbish? No. You're right! Unless you generate your own AuthId (with date, not auto-incremented), i think you will have to do a SELECT in the "author" table to get his ID /you will need it to insert a new tip in the "content" table), and then you'll know if the author exists or not. Hope someone gives a better solution! -- Diana Soares (sql) - 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
