Is something like this even possible? (Result set ordering of grouped data)

[Andrew Quap]

Hello,

I have a table of items that change over time.  These items are in a 
few categories.  So my table looks something like the following simple 
version.
+--++--+-+
| category | itemid | timemodified | currentdata |
+--++--+-+
| c01  | i01| 1| created |
| c01  | i02| 2| created |
| c02  | i03| 3| created |
| c02  | i04| 4| created |
| c01  | i01| 5| update 1|
| c01  | i01| 6| update 2|
| c02  | i04| 7| update 1|
+--++--+-+

I would like to get a set results sorted like the following:

+--++--+-+
| category | itemid | timemodified | currentdata |
+--++--+-+
| c01  | i01| 6| update 2|
| c01  | i01| 5| update 1|
| c01  | i01| 1| created |
| c01  | i02| 2| created |
| c02  | i04| 7| update 1|
| c02  | i04| 4| created |
| c02  | i03| 3| created |
+--++--+-+

The road I was going down was 'order by category, Max(timemodified) 
desc, timemodified desc group by category, itemid' but that is wrong on 
several levels.  Plus I don't want to sort via itemid directly, only 
via the max timemodified of the group.

Let me word that different I want to sort first by category, then sort 
the groups of itemid (each sorted by timemodified) by the max of each 
group's timemodified.

Currently I'm developing with version 4.0.14-log but I could upgrade to 
4.1 if I needed to.

I can get a list of the records of the max timemodified only, sorted by 
max timemodified for each group with something like the following, but 
this leaves out the historical records of the items, and I haven't been 
able to find how to include them in the output without doing many more 
queries, there are over 33k records in my real table and about 15k 
unique itemids.

CREATE TEMPORARY TABLE tmp ( tmpitemid varchar(20), tmptimemodified 
INT(11));

LOCK TABLES testtable READ;

INSERT INTO tmp (SELECT itemid, MAX(timemodified) FROM testtable GROUP 
BY itemid);

SELECT testtable.* FROM testtable, tmp WHERE itemid = tmpitemid AND 
timemodified = tmptimemodified;

UNLOCK TABLES;

DROP TABLE tmp;


If this isn't even possible then I'll go back and redesign my approach 
to this.

Thank you,
-Andrew

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can I misuse the list for a brief minute? - ODBC => MSSQL

[Rusty Wright]

Not sure if this is what you're looking for but try

http://www.freetds.org

I use it with php to talk to an MS SQL server.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Wrong Thread: (sorry) I need to know which id is not present in the other table.

[Randy Chrismon]

Response should have been to problem connecting to 4.1.0
Sorry about that.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

I need to know which id is not present in the other table.

[Randy Chrismon]

Do you use password when you connect to the local 4.1 MySQL server?
Yes. No password, no entry.

This is the answer why you can connect to the local 4.1 MySQL server with MySQLCC. Set up a password and >you should get "Client does not support .." error, too.
I guess I didn't make myself clear... I DO use a password to connect to my local database; it's set up in the database connection dialog box in MySQLCC. Using MySQLCC I connect without problems to my local 4.1.0 database and to a remote 4.0.15 database. From the command line, I connect without problems to my local 4.1.0 database and to the remote 4.0.15 database WITHOUT using --protocol=TCP. I CANNOT connect the command line to the remote 4.1.0 database without using --protocol=TCP. Passwords are required for root and user IDs in all cases. 

Very strange.

Randy



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Can I misuse the list for a brief minute? - ODBC => MSSQL

[Andrew Braithwaite]

Hi,
 
I know this is cheeky to ask but I want to connect to a MS SQL Server and
integrate the info into MySQL using perl on Linux 7.x using ODBC
 
I don't want to pay for a driver and I'm au fait with linux/perl/MySQL and
windows ODBC.
 
I don't want to tax the list too much but if anyone has any pointers to
"Howto's" or readme's then I would be grateful.
 
Cheers,
 
Andrew
 
SQL, Query
 
 

Re: Select statement to get the difference

[Randy Chrismon]

>I need to know which id is not present in the other table.

Stefan:
A left join should do it:
select t1.id from t1 left join t2 on t1.id=t2.id where t2.id is null

I learned this from this mailing list about two weeks ago.

HTH.

Randy

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: "query time" of 4294967294 with 4.0.14

[Jeremy Zawodny]

On Wed, Oct 01, 2003 at 05:54:25PM -0700, Ask Bjørn Hansen wrote:
> Hi,
> 
> Using 4.0.14 on Linux.
> 
> Often very small queries are reported in processlist and in the "slow 
> log" to have taken about 136 years.  Fortunately they don't really! ;-)
> 
> I thought it was curious and I didn't see it in the change log for 
> 4.0.15.

If it helps, we've also seen insanely long query times on FreeBSD with
recent 4.0.xx versions.  I haven't had a chance to track it down yet,
but you're not alone.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 18 days, processed 642,211,210 queries (412/sec. avg)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

"query time" of 4294967294 with 4.0.14

[Ask Bjørn Hansen]

Hi,

Using 4.0.14 on Linux.

Often very small queries are reported in processlist and in the "slow 
log" to have taken about 136 years.  Fortunately they don't really! ;-)

I thought it was curious and I didn't see it in the change log for 
4.0.15.

From the slow log:

# Time: 031001 15:39:59, # [EMAIL PROTECTED]: blaze[blaze] @ host.beep 
[192.168.134.111],
# Query_time: 4294967294  Lock_time: 0  Rows_sent: 0  Rows_examined: 0,
SELECT value,
FROM  options,
WHERE name='max_current_crawls';



 - ask

--
http://www.askbjoernhansen.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Any ideas on how to authenticate to mysql thru PAM?

[Jeremy Zawodny]

On Sun, Sep 21, 2003 at 11:42:49PM -0700, Mike Klein wrote:
> Sorry for repeat email...but this seems like an omission in mysql
> functionality.
> 
> Sooo many apps come w/pam support, or the ability to use ssl.
> 
> I realize that in order to use an ssl cert, you'd somehow need to lookup the
> subject dn in the cert and go against ldap to get a uname/pwd, etc.
> 
> >From a web application, like php, I know I can use existing auth name/pwd
> vars and pass them thru to mysql...works great. Other web content mgmt
> systems (Cocoon) and things like JSP could easily do the same thing.
> 
> But I'm tired of entering my uname/pwd on the command line!!
> 
> There must be something I'm missing in getting this to happen.
> 
> I really don't want to write a script that does this as I generally don't
> like to keep creds in anything except root-owned /etc/shadow, etc.
> 
> Then again, a user's private certs are only protected by the user's own
> credentials...so I guess it wouldn't be TOO stupid to create a script owned
> by user that passes user password thru to mysql...but this smells hacky (not
> in good sense).

MySQL *does* have SSL support, but not PAM.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 18 days, processed 642,017,328 queries (412/sec. avg)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Changing the sort ordering of an existing ENUM field

[Avram Aelony]

hi,

I have a table with a 'priority' field defined as an ENUM:
priority enum('SOMEDAY','NOW','SOON') NOT NULL
I would like to change it to the following so the sort order is more 
intuitive/desirable.
priority enum('NOW','SOON', 'SOMEDAY') NOT NULL

I am guessing that some form of  ALTER TABLE  t MODIFY command is 
required, but I am hesitant as the database is populated and I do not 
wish to lose data if I can help it.  :)

Any help would be appreciated.

thanks,

Avram

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Sig 4 on DEC ALPHA running RedHat 7.2

[Chris Schmidt]

I installed MySQL 4.0.15-MAX on my DEC AlphaServer 1000a running Redhat 7.2 and the 
compile and install went perfect, without any problems.

My problem is this: MySQL will allow me to input data into Databases, however if I try 
to extract information from said databases, MySQL blows up with a Signal 4 and puts a 
very long paragraph that tells me absolutely nothing in my error log. It doesn't 
matter which database I try to get data from, I get this even if I try to get data 
from MySQL's build in databases. So anyone got a clue what is up with this thing?

Thanks..

The Kernel - Chris Schmidt

Re: Updated: How to write this query

[Michael Brunson]

On Wed, 1 Oct 2003 16:58:26 -0500, sean peters
<[EMAIL PROTECTED]> wrote:
[...]
| So ive been running a query like:
| SELECT A_data, B_data, C_data FROM A, B, C
| WHERE A.A_ID = B.A_ID
| AND A.A_ID = C.A_ID
| AND A.A_ID = 4;
| 
[...]
| 
| What i really want is to get the A_data from A, and if there are cooresponding 
| records in B and/or C, get B_data and/or C_data, respectively.
| 
| This works fine if there are cooresponding records in tables B and C for each 
| record in A, but if not, this returns nothing.
| 
| So, short of querying each table, i cant come up with a good solution to my 
| problem.
| 
| If there were only 2 tables, a LEFT JOIN would work fine, but both B and C 
| want to be left joined to A, which i dont know how to do.


SELECT A_data, B_data, C_data 
  FROM
A
  LEFT JOIN 
B ON A.A_ID = B.A_ID 
  LEFT JOIN 
C ON A.A_ID = C.A_ID
  WHERE A.A_ID = 4;


That should do it.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Fwd: [ANN] INTRODUCING LDMLMySQL

[Bill Doerrfeld]

FYI. A 3rd party developer has just come out with an awesome Web 
browser based GUI for managing MySQL databases. Check it out!


To: <[EMAIL PROTECTED]>
Date: Wed, 01 Oct 2003 15:15:51 -0400
Subject: [ANN] INTRODUCING LDMLMySQL
From: Jim Van Heule <[EMAIL PROTECTED]>

INTRODUCING LDMLMYSQL THE OPEN SOURCE MYSQL DATABASE MANAGER AND QUERY
BUILDER FOR LASSO PROFESSIONAL
Grand Haven, Michigan-October 1, 2003-LDMLMySQL.com has opened it's doors to
introduce a new open source MySQL database manager and query builder tool
designed exclusively for Lasso Professional.
LDMLMySQL is a Lasso driven solution designed to manage MySQL databases via
a web browser and provide the Lasso optimized code for use in your own
solutions. The code is open source using standard Lasso syntax to allow
Lasso Developer's the ability to review the code source for use in their own
Lasso projects.
Pricing (Its Free!)

LDMLMySQL is free. You can best support LDMLMySQL development efforts by
supporting our sponsors.
Minimal Restrictions

The code is open source so go ahead and revise it to meet your needs. Feel
free to study the code base as both a learning tool and time saver by
copying and pasting code that you find useful for your own projects. The one
restriction in place does not allow you to use LDMLMySQL and it's code base
to market a similar Lasso-based MySQL tool. Instead, you're encouraged you
to share your code to be added to future LDMLMySQL versions making it freely
available to all. Contributors receive credit and thanks for material that
makes it into LDMLMySQL.
Availability

LDMLMySQL is now available at: 

Contact Information

For questions about LDMLMySQL, visit 

Updated: How to write this query

[sean peters]

Sorry, I had an error in my query. The fixed query with the entire post 
follows. 

Thanks for the responses to the incorrect one, im pretty sure that the 
suggestions will still fail for the previously indicated reasons, even with 
the modified query.

ORIGINAL POST: (fixed)

I've run into a situation where i dont know how to best write a query. For a 
base example, consider these 3 tables:

CREATE TABLE A (
A_IDINT NOT NULL PRIMARY KEY,
A_data  text
);

CREATE TABLE B (
B_IDINT NOT NULL PRIMARY KEY,
A_IDINT NOT NULL,
B_data  text
);

CREATE TABLE C (
C_IDINT NOT NULL PRIMARY KEY,
A_IDINT NOT NULL,
C_data  text
);

So ive been running a query like:
SELECT A_data, B_data, C_data FROM A, B, C
WHERE A.A_ID = B.A_ID
AND A.A_ID = C.A_ID
AND A.A_ID = 4;

*** ORIGINAL INCORRECT QUERY :( ***
SELECT A_data, B_data, C_data FROM A, B, C
WHERE A.A_ID = B.B_ID
AND A.A_ID = C.C_ID
AND A.A_ID = 4;


What i really want is to get the A_data from A, and if there are cooresponding 
records in B and/or C, get B_data and/or C_data, respectively.

This works fine if there are cooresponding records in tables B and C for each 
record in A, but if not, this returns nothing.

So, short of querying each table, i cant come up with a good solution to my 
problem.

If there were only 2 tables, a LEFT JOIN would work fine, but both B and C 
want to be left joined to A, which i dont know how to do.

thanks
sean peters
[EMAIL PROTECTED]


---
mysql, query


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: How to write this query

[Kevin Fries]

Now I'm lost.  Do you really mean to be joining A.A_ID against B.B_ID?
Seems like it should be A.A_ID = B.A_ID... That's a traditional naming
condition.

If so, my recommendation should have been:
SELECT A_data, B_data, C_data
FROM A LEFT JOIN B ON A.A_ID = B.A_ID LEFT JOIN C ON A.A_ID = C.A_ID 
WHERE A.A_ID = 4;

If not, maybe you can describe your data better, with examples.

Regarding:
> When A left joins B, there is no real B record, so any B 
> columns are populated 
> with null, as per left join. 
> Then, table B is left joined to C on A_ID, which is null, and 
> no C record will 

That shouldn't be true.  The join was (A left-join B), then that result
set joined to C.
And the comparison was A.A_ID = C.C_ID.  If the resultset's A.A_ID has
data, the C comparison 
will succeed regardless of B.B_ID being null.

Kevin Fries

> -Original Message-
> From: sean peters [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, October 01, 2003 2:23 PM
> To: Kevin Fries; [EMAIL PROTECTED]
> Subject: Re: How to write this query
> 
> 
> Unfortunately that wont always work either.
> 
> For instance, assume that there is an A record with A_ID = 4 
> And that there is a C record where A_ID = 4, but NO B record 
> where A_ID = 4
> 
> So, executing the query:
> > SELECT A_data, B_data, C_data
> > FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID 
> = C.C_ID 
> > WHERE A.A_ID = 4;
> 
> When A left joins B, there is no real B record, so any B 
> columns are populated 
> with null, as per left join. 
> Then, table B is left joined to C on A_ID, which is null, and 
> no C record will 
> properly match the B.A_ID = NULL, so the C record is filled 
> with nulls.
> 
> If we were to join A to C then to B, a similar problem would 
> occur if there 
> was a cooresponding B record, but no C record. 
> 
> Thanks anyway.
> 
> 
> On Wednesday 01 October 2003 14:25, Kevin Fries wrote:
> > You're on the right track with LEFT JOIN.  Just continue the 
> > thought...
> > Try:
> > SELECT A_data, B_data, C_data
> > FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID
> > WHERE A.A_ID = 4;
> >
> > > -Original Message-
> > > From: sean peters [mailto:[EMAIL PROTECTED]
> > > Sent: Wednesday, October 01, 2003 12:07 PM
> > > To: [EMAIL PROTECTED]
> > > Subject: How to write this query
> > >
> > >
> > > I've run into a situation where i dont know how to best write a 
> > > query. For a base example, consider these 3 tables:
> > >
> > > CREATE TABLE A (
> > >   A_IDINT NOT NULL PRIMARY KEY,
> > >   A_data  text
> > > );
> > >
> > > CREATE TABLE B (
> > >   B_IDINT NOT NULL PRIMARY KEY,
> > >   A_IDINT NOT NULL,
> > >   B_data  text
> > > );
> > >
> > > CREATE TABLE C (
> > >   C_IDINT NOT NULL PRIMARY KEY,
> > >   A_IDINT NOT NULL,
> > >   C_data  text
> > > );
> > >
> > > So ive been running a query like:
> > > SELECT A_data, B_data, C_data FROM A, B, C
> > > WHERE A.A_ID = B.B_ID
> > > AND A.A_ID = C.C_ID
> > > AND A.A_ID = 4;
> > >
> > > What i really want is to get the A_data from A, and if there are 
> > > cooresponding records in B and/or C, get B_data and/or C_data, 
> > > respectively.
> > >
> > > This works fine if there are cooresponding records in 
> tables B and C 
> > > for each record in A, but if not, this returns nothing.
> > >
> > > So, short of querying each table, i cant come up with a good 
> > > solution to my problem.
> > >
> > > If there were only 2 tables, a LEFT JOIN would work fine, 
> but both B 
> > > and C want to be left joined to A, which i dont know how to do.
> > >
> > > thanks
> > > sean peters
> > > [EMAIL PROTECTED]
> > >
> > >
> > > ---
> > > mysql, query
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > > http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
> 
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: newbie SELECT question

[Michael Johnson]

How about this?

SELECT
  SUBSTRING_INDEX(SUBSTRING(url, LOCATE("//", url) + 2), '/', 1) AS domain
FROM referals
Michael

On Wed, 1 Oct 2003 14:54:24 +0100, Graham Nichols 
<[EMAIL PROTECTED]> wrote:

Hi,

I have a table containing page referral URLs gleaned from users browsing 
my website. Is there a way for me to use SELECT based on a portion 
record's contents?

eg

table contents:

http://www.yahoo.com/adirectory/apage.htm
http://google.net/adirectory/anotherpage.php
I wish to return only the portion between the // and /. Sort of a //*/ 
thing so I can use COUNT with this to allow me to build a table in PHP 
of the most popular root domains which go to my site.

I hope this all makes sense ;-)

kind regards,  Graham Nichols.


--
Michael Johnson < [EMAIL PROTECTED] >
Internet Application Programmer, Pitsco, Inc.
620-231-2424x516
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to write this query

[sean peters]

Unfortunately that wont always work either.

For instance, assume that there is an A record with A_ID = 4
And that there is a C record where A_ID = 4,
but NO B record where A_ID = 4

So, executing the query:
> SELECT A_data, B_data, C_data
> FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID
> WHERE A.A_ID = 4;

When A left joins B, there is no real B record, so any B columns are populated 
with null, as per left join. 
Then, table B is left joined to C on A_ID, which is null, and no C record will 
properly match the B.A_ID = NULL, so the C record is filled with nulls.

If we were to join A to C then to B, a similar problem would occur if there 
was a cooresponding B record, but no C record. 

Thanks anyway.


On Wednesday 01 October 2003 14:25, Kevin Fries wrote:
> You're on the right track with LEFT JOIN.  Just continue the thought...
> Try:
> SELECT A_data, B_data, C_data
> FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID
> WHERE A.A_ID = 4;
>
> > -Original Message-
> > From: sean peters [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, October 01, 2003 12:07 PM
> > To: [EMAIL PROTECTED]
> > Subject: How to write this query
> >
> >
> > I've run into a situation where i dont know how to best write
> > a query. For a
> > base example, consider these 3 tables:
> >
> > CREATE TABLE A (
> > A_IDINT NOT NULL PRIMARY KEY,
> > A_data  text
> > );
> >
> > CREATE TABLE B (
> > B_IDINT NOT NULL PRIMARY KEY,
> > A_IDINT NOT NULL,
> > B_data  text
> > );
> >
> > CREATE TABLE C (
> > C_IDINT NOT NULL PRIMARY KEY,
> > A_IDINT NOT NULL,
> > C_data  text
> > );
> >
> > So ive been running a query like:
> > SELECT A_data, B_data, C_data FROM A, B, C
> > WHERE A.A_ID = B.B_ID
> > AND A.A_ID = C.C_ID
> > AND A.A_ID = 4;
> >
> > What i really want is to get the A_data from A, and if there
> > are cooresponding
> > records in B and/or C, get B_data and/or C_data, respectively.
> >
> > This works fine if there are cooresponding records in tables
> > B and C for each
> > record in A, but if not, this returns nothing.
> >
> > So, short of querying each table, i cant come up with a good
> > solution to my
> > problem.
> >
> > If there were only 2 tables, a LEFT JOIN would work fine, but
> > both B and C
> > want to be left joined to A, which i dont know how to do.
> >
> > thanks
> > sean peters
> > [EMAIL PROTECTED]
> >
> >
> > ---
> > mysql, query
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?> [EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

[Stats] MySQL List Stats; September 2003

[Bill Doerrfeld]

--
Searchable archives for this list are available at

--
==
MySQL List Stats
September, 2003
==
Note: Up/Down % as compared with August, 2003

Posts:   2038 (Down 5%)
Authors:  597 (Down 2%)
Threads:  740 (Up   7%)
Top 20 Contributors by Number of Posts
--
Jeremy Zawodny  84
Paul DuBois 82
Victoria Reznichenko59
Heikki Tuuri48
Egor Egorov 41
Dathan Vance Pattishall 37
Director General: NEFACOMP  34
Matt W  32
Antony Dovgal   24
Dan Greene  23
Randy Chrismon  19
Lenz Grimmer18
Fortuno, Adam   17
Tbird67ForSale  17
daniel  16
Kelley Lingerfelt   16
Keith C. Ivey   15
Dan Nelson  14
Andy Eastham14
Sergei Golubchik14
Top 20 Threads by Number of Posts
--
Does NULL == ""?29
Table is full error 17
Show database problem   12
Platform vs. Performance12
Ideas on creating connections   12
How to enable General Query_log?11
insert ... select .. order by, problem  10
SubQueries and IN   10
"ERROR 1030: Got error 127 from table handler"  10
OS X Installation and Setup 10
Lock tables in myisam   10
innodb  10
Select from one table where ID not in another table  9
Compling on RedHat 9 9
SELECT only unique records   9
Unique Key Violation - How to determine which key9
Usage Monitoring 9
MySQL newbie: table gone after reboot9
FOREIGN KEY Weirdness in mySQL 4.1 with VARCHAR  9
'IF NOT EXISTS' ignored? 9
Top 20 Search Terms by Number of Requests
--
Can't   32
thread  26
create  26
a   26
new 26
Error   16
delete  15
mysql   12
query_cache  9
fulltext 9
null 9
select   8
multiple 8
value8
join 7
set  6
to   6
update   6
increment 

RE: How to write this query

[Kevin Fries]

You're on the right track with LEFT JOIN.  Just continue the thought...
Try:
SELECT A_data, B_data, C_data 
FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID
WHERE A.A_ID = 4;

> -Original Message-
> From: sean peters [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, October 01, 2003 12:07 PM
> To: [EMAIL PROTECTED]
> Subject: How to write this query
> 
> 
> I've run into a situation where i dont know how to best write 
> a query. For a 
> base example, consider these 3 tables:
> 
> CREATE TABLE A (
>   A_IDINT NOT NULL PRIMARY KEY,
>   A_data  text
> );
> 
> CREATE TABLE B (
>   B_IDINT NOT NULL PRIMARY KEY,
>   A_IDINT NOT NULL,
>   B_data  text
> );
> 
> CREATE TABLE C (
>   C_IDINT NOT NULL PRIMARY KEY,
>   A_IDINT NOT NULL,
>   C_data  text
> );
> 
> So ive been running a query like:
> SELECT A_data, B_data, C_data FROM A, B, C
> WHERE A.A_ID = B.B_ID
> AND A.A_ID = C.C_ID
> AND A.A_ID = 4;
> 
> What i really want is to get the A_data from A, and if there 
> are cooresponding 
> records in B and/or C, get B_data and/or C_data, respectively.
> 
> This works fine if there are cooresponding records in tables 
> B and C for each 
> record in A, but if not, this returns nothing.
> 
> So, short of querying each table, i cant come up with a good 
> solution to my 
> problem.
> 
> If there were only 2 tables, a LEFT JOIN would work fine, but 
> both B and C 
> want to be left joined to A, which i dont know how to do.
> 
> thanks
> sean peters
> [EMAIL PROTECTED]
> 
> 
> ---
> mysql, query
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
> 
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How to write this query

[sean peters]

I've run into a situation where i dont know how to best write a query. For a 
base example, consider these 3 tables:

CREATE TABLE A (
A_IDINT NOT NULL PRIMARY KEY,
A_data  text
);

CREATE TABLE B (
B_IDINT NOT NULL PRIMARY KEY,
A_IDINT NOT NULL,
B_data  text
);

CREATE TABLE C (
C_IDINT NOT NULL PRIMARY KEY,
A_IDINT NOT NULL,
C_data  text
);

So ive been running a query like:
SELECT A_data, B_data, C_data FROM A, B, C
WHERE A.A_ID = B.B_ID
AND A.A_ID = C.C_ID
AND A.A_ID = 4;

What i really want is to get the A_data from A, and if there are cooresponding 
records in B and/or C, get B_data and/or C_data, respectively.

This works fine if there are cooresponding records in tables B and C for each 
record in A, but if not, this returns nothing.

So, short of querying each table, i cant come up with a good solution to my 
problem.

If there were only 2 tables, a LEFT JOIN would work fine, but both B and C 
want to be left joined to A, which i dont know how to do.

thanks
sean peters
[EMAIL PROTECTED]


---
mysql, query

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL and mounted /mysql/data/ directories

[Matt Gostick]

Hm.  I think this is more of a mount question.  If you have two
machines, each with a /mysql/data directory then you cannot mount
/mysql/data from one machine to the other machine and have the
directories 'merge' into one big tree including 'all' contents.  If you
mount overtop of a directory that has contents then the old contents
will seem to disappear and you will only see the contents of the mounted
drive.

If you know the specific databases then it is possible though.  Just
mount each individual database in the /mysql/data directory of the other
machine (as long as the database names arent' the same).

I've used NFS for database sharing before... in a production
environment.  I've since switched to builtin mysql replication... and
prefer it 100x's more.  It's easy to setup will do exactly what you
want.

Matt

On Wed, 2003-10-01 at 10:05, Eric Dickner wrote:
> Hello All,
> 
> I want MySQL to see several drives scattered on several machines, all with
> MySQL installed on them.  If I mount all of the /mysql/data/ directories to
> each of the machines will all the separate machines be able to see each
> others' databases in a "transparant" way?  Or, alternatively, will I have to
> mount each database directory to each
> machines' /mysql/data/ directory?
> 
> Failing both of those will symbolic links
> or hard links to each of the other machines' data directories allow me to
> see all of the separate directories on the separate machines at once?
> Thanks...I don't have a Lin/Unix network to find this out by myself
> 
> Eric D
> 
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Blob fields

[Jeremy Zawodny]

On Wed, Oct 01, 2003 at 05:51:18PM +0100, Angelo Carmo wrote:
> I people,
> 
> Who knows how to insert an image file  into blob fileds.

Lots of us know how.  And we've discussed it on the list about 600 times
already.  I'm sure you'll find an answer in the list archives.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/

MySQL 4.0.15-Yahoo-SMP: up 17 days, processed 630,933,987 queries (412/sec. avg)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Blob fields

[Angelo Carmo]

I people,
 
 
Who knows how to insert an image file  into blob fileds.
 
Thanks.
 
Angelo Carmo.

VI-Veiculo de Ideias / Supermercados Horta

Tel: 289899670 Tlm: 912179154

[EMAIL PROTECTED]

Re: strange date problem SOLVED

[Chris Edwards]

> I'm running this query:
>
> INSERT INTO `events`( `domain`, `title`, `body`, `begin`, `end`,
> `frequency`, `author` ) VALUES( 01, 'Test 2', 'Event is not displaying
> correct date.', '2003-10-25 24:00:00', '2003-10-01 24:00:00', '0',
> 'Rachel' )
>
> I get no errors from mysql. (MySQL 3.23.54) on a freebsd system.
>
> But I do get:
> 03 01 Test 2 Event is not displaying correct date. -00-00
> 00:00:00 -00-00 00:00:00 0 Rachel
>
> in mysql.
>
> The dates are all zero.
>
> Whats going on?

I guess I should have looked at this alittle longer before posting.  no such
thing as 24th hour 23.59 then 0.

-- 
Chris Edwards
Web Application Developer
Outer Banks Internet, Inc.
252-441-6698
[EMAIL PROTECTED]
http://www.OuterBanksInternet.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SHOW FIELDS FROM table?

[Mike Griffin]

My code is shown below, I can select * from employees but I cannot get
any data back from SHOW FIELDS, I do not receive an error, in fact,
something is returned but I'm not sure what. I can execute the
command:
SHOW FIELDS FROM test.employees

in the Control Center just fine, even cmd.ExecuteReader will enter
reader.Read() once, something is returned but what does it look like?
This is .NET code



OleDbConnection cn = new OleDbConnection(@"Provider=MySQLProv;Data
Source=test;DB=test;UID=;PWD=;PORT=3306");
cn.Open();
OleDbCommand cmd = new OleDbCommand("SHOW FIELDS FROM test.employees",
cn);
cmd.CommandType = CommandType.Text;
DataTable metaData = new DataTable();
OleDbDataAdapter ad = new OleDbDataAdapter();
ad.SelectCommand = cmd;
ad.Fill(metaData);
===

_
Help protect your PC.  Get a FREE computer virus scan online from McAfee. 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

strange date problem

[Chris Edwards]

Hi

I'm running this query:

INSERT INTO `events`( `domain`, `title`, `body`, `begin`, `end`,
`frequency`, `author` ) VALUES( 01, 'Test 2', 'Event is not displaying
correct date.', '2003-10-25 24:00:00', '2003-10-01 24:00:00', '0',
'Rachel' )

I get no errors from mysql. (MySQL 3.23.54) on a freebsd system.

But I do get:
03 01 Test 2 Event is not displaying correct date. -00-00
00:00:00 -00-00 00:00:00 0 Rachel

in mysql.

The dates are all zero.

Whats going on?

-- 
Chris Edwards
Web Application Developer
Outer Banks Internet, Inc.
252-441-6698
[EMAIL PROTECTED]
http://www.OuterBanksInternet.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

locked threads

[Don Vu]

Hi guys,
 
Do you have any thoughts on how to track down a locked thread/query?
Basically we've had instances where a query locks up and causes a
cascade of locked queries and the db freezes up. By the time it's had
enough of an effect for us to see the symptoms (no new queries run)
there are a bunch of locked queries and no real way to see which one is
the initial culprit. So I was wondering:
 
1) is there an easy way to monitor whether or not a query is locking up
the db?  does anyone have a script handy that checks for locks that can
be cronned and then an email is sent?
 
2) in the instance of having a bunch of locks, is there any way to track
down the query that orignated a cascade of locks?
 
thanks in advance for the help.
 
-Don
 
--
Don Vu > Madstone Theaters 
85 fifth avenue, 12th floor > new york > new york > 10003  
p 212.379.1545 > f 212.989.7744 > http://www.madstonefilms.com
  > http://
 www.madstonetheaters.com
 

 

RE: DATE_ADD Dynamic Interval

[Adam Carmichael]

Sorry, I realise I made a mistake in my schema and my query below, they
should read:
-
UPDATE foo_table
SET NextDate=DATE_ADD(foo_table.NextDate,Period)
WHERE NextDate<=NOW()
-
CREATE TABLE `foo_table` (
  `ID` bigint(20) NOT NULL auto_increment,
  `Period` enum('INTERVAL 30 SECOND','INTERVAL 1 DAY','INTERVAL 1
WEEK','INTERVAL 2 WEEK','INTERVAL 1 MONTH','INTERVAL 2 MONTH','INTERVAL 3
MONTH','INTERVAL 1 YEAR','INTERVAL 2 YEAR') NOT NULL default 'INTERVAL 1
MONTH',
  `NextDate` datetime NOT NULL default '-00-00 00:00:00',
  PRIMARY KEY  (`ID`)
) TYPE=InnoDB;
-
> I have also considered trying something along the lines of:
>
> UPDATE foo_table
> SET NextDate=DATE_ADD(foo_table.NextDate,(SELECT foo_table.Period))
> WHERE NextDate<=NOW()
-
The more I look at this, the more I think I may be forced to select the date
interval to add and then build a query and execute it rather than do that in
just one statement. I would have preferred to have this work (or something
similar).

--
Adam Carmichael
[EMAIL PROTECTED]

- Original Message - 
From: "Adam Carmichael" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, September 30, 2003 9:45 PM
Subject: DATE_ADD Dynamic Interval


> Hi All,
>
> I am trying to create a table with a list of records where a script runs
> about once every 10 minutes that will update a certain field by an
interval
> set (by an enum) in that particular record.
>
> My Script looks as follows:
>
> UPDATE foo_table
> SET NextDate=DATE_ADD(foo_table.NextDate,Period)
> WHERE NextDate<=NOW()
>
> And the schema approximates:
> CREATE TABLE `Records` (
>   `ID` bigint(20) NOT NULL auto_increment,
>   `Period` enum('INTERVAL 30 SECOND','INTERVAL 1 DAY','INTERVAL 1
> WEEK','INTERVAL 2 WEEK','INTERVAL 1 MONTH','INTERVAL 2 MONTH','INTERVAL 3
> MONTH','INTERVAL 1 YEAR','INTERVAL 2 YEAR') NOT NULL default 'INTERVAL 1
> MONTH',
>   `NextDate` datetime NOT NULL default '-00-00 00:00:00',
>   PRIMARY KEY  (`ID`)
> ) TYPE=InnoDB;
>
> I have also considered trying something along the lines of:
>
> UPDATE foo_table
> SET NextDate=DATE_ADD(foo_table.NextDate,(SELECT foo_table.Period))
> WHERE NextDate<=NOW()
>
> Has anybody tried anything like this before with any luck?
>
> Regards,
>
> Adam Carmichael
> [EMAIL PROTECTED]
>
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How can i make mysql to print date and time automatically?

[woody at nfri dot com]

On Wed, 2003-10-01 at 10:09, Emilio Ruben Estevez wrote:
> Hi, im develping an application, and was wondering how can i make mysql get 
> time and date from pc and print it automatically in the time field and date 
> field so the user dont have to worry about entering the coorect time and 
> date. Is this posible, ive created a databse with fields hour(time) and 
> Date(date) like type but i dont know how to do the mysql to get time and 
> date and print it!
> 
> Any hints?

Heres a hint...type "date" into the search field on www.mysql.com
instead of asking the good people here to hold your hand.

Yes I am a little irritated at people who don't even make the simplest
of efforts to help themselves.

Stepping down...and apologizing for the attitude to the good and helpful
people here.
-- 
Woody

In a world without boundaries why
do we need Gates and Windows?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

setting an alias to a table column

[sean hayes]

hi,

Is there a way of setting an alias to a column name when you create the 
table or by using the "alter" command.

My problem is i have 2 versions of the same table  with one column having a 
slightly different name (e.g old column name is id, new column name is 
a_id). Now, to save having to amend (& test) 20 or so scripts running off 
the old version, i would like to set an alias against "a_id" so it is 
aliased as "id". therefore both old & new code will be able to run off the 
same (new) table version.

If i could do the above it would mean i could amend the older scripts to run 
on the newer version code over a period of time.



Any ideas ??



Regards



Sean

_
Get MSN 8 Dial-up Internet Service FREE for one month.  Limited time offer-- 
sign up now!   http://join.msn.com/?page=dept/dialup

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How can i make mysql to print date and time automatically?

[Emilio Ruben Estevez]

Hi, im develping an application, and was wondering how can i make mysql get 
time and date from pc and print it automatically in the time field and date 
field so the user dont have to worry about entering the coorect time and 
date. Is this posible, ive created a databse with fields hour(time) and 
Date(date) like type but i dont know how to do the mysql to get time and 
date and print it!

Any hints?

Thaks in advance.
Emilio.
_
Add MSN 8 Internet Software to your existing Internet access and enjoy 
patented spam protection and more.  Sign up now!   
http://join.msn.com/?page=dept/byoa

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problem deleting data

[Andrew Pattison]

Just to confirm that this is not a file permissions problem, I can use this
statement:

DELETE FROM stock WHERE supplier LIKE '%'

and it doesn't exhibit this problem, but a plain:

DELETE FROM stock

does. I'm thinking that perhaps this is a bug in MySQL but I'm not sure. If
anyone has any pointers it would be much appreciated.

Cheers

Andrew.

- Original Message - 
From: "Andrew Pattison" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, October 01, 2003 12:18 PM
Subject: Fw: Problem deleting data


>
> > I have a strange problem with MySQL 4.0.15 . When I delete an entire
> table,
> > then run a repair on that table, it recovers all the rows which I just
> > deleted! Also, if I do a "check table" I can see that the files on disk
do
> > not appear to have been altered in any way by the delete. Does anyone
have
> > any clues as to what is going on here?
> >
> > Thanks
> >
> > Andrew P.
> >
> > Andrew Pattison, IT Support
> > Sterling Furniture Group Ltd
> > 01259 75 5135
> >
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: newbie SELECT question

[Percy Williams]

Could look at instr?

> -Original Message-
> From: Graham Nichols [mailto:[EMAIL PROTECTED]
> Sent: 01 October 2003 14:54
> To: [EMAIL PROTECTED]
> Subject: newbie SELECT question
> 
> Hi,
> 
> I have a table containing page referral URLs gleaned from users
browsing
> my website. Is there a way for me to use SELECT based on a portion
> record's contents?
> 
> eg
> 
> table contents:
> 
> http://www.yahoo.com/adirectory/apage.htm
> http://google.net/adirectory/anotherpage.php
> 
> I wish to return only the portion between the // and /. Sort of a //*/
> thing so I can use COUNT with this to allow me to build a table in PHP
of
> the most popular root domains which go to my site.
> 
> I hope this all makes sense ;-)
> 
> kind regards,  Graham Nichols.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Rolling back DDL statements

[Chris Nolan]

Harald Fuchs wrote:

In article <[EMAIL PROTECTED]>,
Chris Nolan <[EMAIL PROTECTED]> writes:
 

Hi all,
I was wondering, would there be any point at all in being able to have
DDL statements as part of a transaction? There is one database I know
of that has this as a big selling point.
   

PostgreSQL has it, Sybase AFAIK doesn't.

Probably not of much use to you, but as a bit of additional knowledge, 
Gupta's SQLBase has it. It is the RDBMS I
was referring to above.

 

Would any readers of the list be able to find a use for such an animal?
   

I find it natural.  In MySQL/InnoDB you need to remember what you
mustn't do within a transaction.
 

I think the implications for replicated sites and rolling out updates 
are pretty important.

Regards,

Chris

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL and mounted /mysql/data/ directories

[Eric Dickner]

Hello All,

I want MySQL to see several drives scattered on several machines, all with
MySQL installed on them.  If I mount all of the /mysql/data/ directories to
each of the machines will all the separate machines be able to see each
others' databases in a "transparant" way?  Or, alternatively, will I have to
mount each database directory to each
machines' /mysql/data/ directory?

Failing both of those will symbolic links
or hard links to each of the other machines' data directories allow me to
see all of the separate directories on the separate machines at once?
Thanks...I don't have a Lin/Unix network to find this out by myself

Eric D



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: can NOT drop the database

[Wang Feng]

> As long as two databases are on the same disk you can also rename from one
> database to another:
> RENAME TABLE current_db.tbl_name TO other_db.tbl_name;


Works!! :-)   But the thing is: by doing this, although the tables have been
*moved* from the old database to the new one, the old database still exists
and I'll have to drop it separately.   So, this should NOT be called
*rename*. The procedures involved are: create a new database -> move the
tables from the old_database to the new_database.  Funny. :-)

thanks Heikki.







-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't Access DB from MySQLCC

[Victoria Reznichenko]

Randy Chrismon <[EMAIL PROTECTED]> wrote:
>>>Don't think this is the issue. If it were, I wouldn't be able to use 
>>> MySQLCC on my own local database which is also 4.1.0 alpha, would I?
> 
> 
>> Do you use password when you connect to the local 4.1 MySQL server?
> 
> Yes. No password, no entry.

This is the answer why you can connect to the local 4.1 MySQL server with MySQLCC. Set 
up a password and you should get "Client does not support .." error, too.

> 
>> Can you connect without --protocol option using 4.1 mysql command-line client 
>> program?
> 
> No. If I leave off the --protocol option, I get the error message saying I should 
> upgrade my client, even though both sides of the transaction are 4.1.0 alpha. 
> 
> Well, now that I read your response more carefully, no, I don't need the --protocol 
> option if I am connecting to my local 4.1.0 database using the command line. I DO 
> need it, however, to connect to the 4.1.0 database on the other machine. On the 
> other hand, I do NOT need the option if I connect to a 4.0.15 database on yet 
> another machine. With MySQLCC, I can connect to the local 4.1.0 database and the 
> remote 4.0.15 database. Trying to connect to the 4.1.0 database yields the same 
> upgrade-your-client error message I get with the command line if I leave off the 
> ---protocol=TCP option.

Hmm .. strange I can connect with 4.1 client to the 4.1 server without any problem 
(with or without --protocol option).


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

newbie SELECT question

[Graham Nichols]

Hi,

I have a table containing page referral URLs gleaned from users browsing my website. 
Is there a way for me to use SELECT based on a portion record's contents?

eg

table contents:

http://www.yahoo.com/adirectory/apage.htm
http://google.net/adirectory/anotherpage.php

I wish to return only the portion between the // and /. Sort of a //*/ thing so I can 
use COUNT with this to allow me to build a table in PHP of the most popular root 
domains which go to my site.

I hope this all makes sense ;-)

kind regards,  Graham Nichols.

RE: Sql question

[Dan Greene]

you want to do

insert into mytable (column1, column2, column3)
(select thiscolumn, '1', now() from anotherTable);



> Mysql 3..
> 
> I can't figure this one out
> 
> 
> 
> I need to move data from one mysql table to another
> 
> The hurdle for me is adding additional column values.
> 
> Here is what I have.
> 
> 
> insert into mytable (column1, column 2, column3)
> (Select thiscolumn 
> From anotherTable), '1', now();
> 
> 
> 
> It's the 1 and the now() I can't insert.
> 
> Anyone have an idea how to do this?
> 
> 
> 
> 
> ---
> Keith Schuster
> Schuster & Company LLC
> ph:704-799-2438
> fx:704-799-0779
> iChat/AIM:FSHSales
> 
> WWW.FlagShipHosting.com
> WWW.Schusterandcompany.com
> WWW.Vsheet.net
> 
> 
> 
> 
> -- 
> 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: Sql question

[Victoria Reznichenko]

Keith Schuster <[EMAIL PROTECTED]> wrote:
> Mysql 3..
> 
> I can't figure this one out
> 
> I need to move data from one mysql table to another
> 
> The hurdle for me is adding additional column values.
> 
> Here is what I have.
> 
> 
> insert into mytable (column1, column 2, column3)
> (Select thiscolumn 
> From anotherTable), '1', now();
> 
> It's the 1 and the now() I can't insert.
> 
> Anyone have an idea how to do this?
> 

INSERT INTO mytable(column1, column2, column3) SELECT thiscolumn, '1', NOW() FROM 
anothertable


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: can NOT drop the database

[Heikki Tuuri]

Feng,

- Alkuperäinen viesti - 
Lähettäjä: "Wang Feng" <[EMAIL PROTECTED]>
Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>
Lähetetty: Wednesday, October 01, 2003 11:26 AM
Aihe: Re: can NOT drop the database


>
> > > BTW, how can I rename a database?
>
>
> > if you only have MyISAM tables, you can shut down mysqld and rename the
> > directory.
>
> Works!! :-)
>
>
> >But that does not work for InnoDB tables. For them, the only way
> > is to use the command RENAME to raname each table individually.
>
>  Heikki, what do you mean 'rename each table individually'? I want to
rename
> the Database, not the tables.
>
> Assume I have 2 tables, one is called table_1 (MyISAM type), the other is
> table_2 (InnoDB type). They are all stored in the database called db1, Now
I
> want to change the database name to db1_new. How can I achieve that by
> 'renaming' the table_2 individually?
>
> Please help me to explainn.

http://www.mysql.com/doc/en/RENAME_TABLE.html

"
As long as two databases are on the same disk you can also rename from one
database to another:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
"

Best regards,

Heikki
Innobase Oy
http://www.innodb.com
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL
Order MySQL support from http://www.mysql.com/support/index.html


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: can NOT drop the database

[Heikki Tuuri]

Adam,

you can use the innodb_table_monitor

http://www.innodb.com/ibman.html#InnoDB_Monitor

and the advice at

http://www.innodb.com/ibman.html#InnoDB_troubleshooting_dict

to resolve the problem.

Best regards,

Heikki
Innobase Oy
http://www.innodb.com
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL
Order MySQL support from http://www.mysql.com/support/index.html

- Alkuperäinen viesti - 
Lähettäjä: "Adam Hardy" <[EMAIL PROTECTED]>
Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>
Kopio: <[EMAIL PROTECTED]>
Lähetetty: Wednesday, October 01, 2003 3:24 PM
Aihe: Re: can NOT drop the database


> Hi Heikki,
>
> a similar problem happened to me and I got the error:
>
> ERROR 1051: Unknown table '#sql-ffa_2,#sql-2b2_30'
>
> After reading your post, I checked in the data directory and there are
> two files there, both of file type data:
>
> #sql-2b2_30.frm
> #sql-ffa_2.frm
>
> I certainly didn't put them there myself - I think they must have come
> from mySQL automatically at some point.
>
> Are they real tables in the database? I can't see them when I use 'show
> tables'. Is it a permissions problem? I have this on another database
> that I was trying to restore from a dump. I had to rename the database
> in the end.
>
> I am using InnoDB tables for some of my data.
>
> Regards
> Adam
>
> On 10/01/2003 09:09 AM Heikki Tuuri wrote:
> > Feng,
> >
> > do you have some non-MySQL file in that database directory under the
datadir
> > of MySQL?
> >
> > Naturally, MySQL will not drop the directory if it contains something
more
> > than just MySQL tables.
> >
> > Best regards,
> >
> > Heikki Tuuri
> > Innobase Oy
> > http://www.innodb.com
> > Foreign keys, transactions, and row level locking for MySQL
> > InnoDB Hot Backup - a hot backup tool for MySQL
> >
> > Order MySQL technical support from https://order.mysql.com/
> >
> >
> > - Original Message - 
> > From: ""Wang Feng"" <[EMAIL PROTECTED]>
> > Newsgroups: mailing.database.myodbc
> > Sent: Wednesday, October 01, 2003 8:37 AM
> > Subject: Re: can NOT drop the database
> >
> >
> >
> >>Not at all!!
> >>
> >>after I typed the 'drop database my_account_database', I got the
following
> >>message:
> >>
> >>
> >>>Query OK, 0 rows affected (0.00sec).
> >>
> >>and I do the 'show databases', that one is still there.
> >>
> >>
> >>cheers,
> >>
> >>feng
> >>
> >>
> >>- Original Message -
> >>From: <[EMAIL PROTECTED]>
> >>To: <[EMAIL PROTECTED]>
> >>Sent: Wednesday, October 01, 2003 3:31 PM
> >>Subject: Re: can NOT drop the database
> >>
> >>
> >>
> >>>no permissions errors ?
> >>>
> >>>
> Greetings,
> 
> 
> I have a database called 'my_account_database' and I tried to drop it
> by the command 'drop database my_account_database', it doesn't work.
> The database is still there, can not be dropped although the tables
> contained in the database have gone.
> 
> I subsequently created another 2 databases and could drop them
> successfully.
> 
> Tried the 'drop database my_account_database' again before sending
> >
> > this
> >
> message, still doesn't work.
> 
> 
> So, what could be the problem? Have you seen this situation before?
> 
> 
> btw, I can't find out any command which can be used to simply rename a
> database, please advise!!
> 
> 
> cheers,
> 
> feng
> 
> 
> 
> --
> 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]
> >
> >
> >
> >
>
> -- 
> Running mySQL 4.1.0 on Linux 2.4.20 RH9
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't connect to MySQL from remote

[Egor Egorov]

[EMAIL PROTECTED] wrote:
> I have some problems with MySQL 3.23.52 on RedHat Linux 8.0.
> 
> I can connect to mysql when I am on the same machine but I can't connect to it from 
> another machine. Here is the error I get:
> 
>> mysqladmin -u cedric -h gdvi02 version
> mysqladmin: connect to server at 'gdvi02' failed
> error: 'Lost connection to MySQL server during query'
> 
> What's the problem ?

Seems it's related to the old problem in Red Hat glibc. Install recent version of 
MySQL server and the problem should go away.



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Sql question

[Keith Schuster]

Mysql 3..

I can't figure this one out



I need to move data from one mysql table to another

The hurdle for me is adding additional column values.

Here is what I have.


insert into mytable (column1, column 2, column3)
(Select thiscolumn 
>From anotherTable), '1', now();



It's the 1 and the now() I can't insert.

Anyone have an idea how to do this?




---
Keith Schuster
Schuster & Company LLC
ph:704-799-2438
fx:704-799-0779
iChat/AIM:FSHSales

WWW.FlagShipHosting.com
WWW.Schusterandcompany.com
WWW.Vsheet.net




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

[Fwd: SHOW FIELDS FROM myTable]

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

This belongs on the [EMAIL PROTECTED] mailing list.

Thanks!

-Mark
-  Original Message 
Subject:SHOW FIELDS FROM myTable
Date:   Tue, 30 Sep 2003 22:46:00 -0500
From:   Mike Griffin <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>



What does 'SHOW FIELDS FROM myTable' return?  I don't get an error, I
just seem to get no data back, however, I can change the code below to
'SELECT * FROM myTable' and get my actual data.  I'm using .NET here
but everything else works, just the show and describe things seem to not
return result sets?

   OleDbConnection cn = new OleDbConnection(@"Provider=MySQLProv;Data
Source=test;DB=test;UID=;PWD=;PORT=3306");
   cn.Open();
   OleDbCommand cmd = new OleDbCommand("SHOW FIELDS FROM
test.employees", cn);
   cmd.CommandType = CommandType.Text;
   DataTable metaData = new DataTable();
   OleDbDataAdapter ad = new OleDbDataAdapter();
   ad.SelectCommand = cmd;
   ad.Fill(metaData);


- --
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: +1 708 557 2388
www.mysql.com

Are you MySQL Certified?
http://www.mysql.com/certification/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQE/etH9tvXNTca6JD8RArXIAKCdXBTGU8U969SEVVldAIfDQU4UFwCgyMRa
VaevphDUSW5FzSYCUwISN7c=
=Zryt
-END PGP SIGNATURE-


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Rolling back DDL statements

[Chris Nolan]

Hi all,

I was wondering, would there be any point at all in being able to have 
DDL statements as part of a transaction? There is one database I know of 
that has this as a big selling point.

Would any readers of the list be able to find a use for such an animal?

Regards,

Chris

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Creating indeces with Innodb

[aguia]

Hi

Somebody knows what are the buffers that MySQL uses when creating indeces in
InnoDB tables? 

I increased the sort buffer, the tmp_table_size, the buffer_pool, the
buffer_log, the log_file but i didn't have performance increase...


Anyone have this problem too?
What buffers are used?

I'm creating indeces in populated tables of course (16 and 11 million rows).


Thx 
Alexis

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: NOT problems

[Ed Smith]

> >Here's my schema and data:
> >
> >create table person (name char(5));
> >insert into person values ('Bob');
> >insert into person values ('Jane');
> >
> >In mySQL 4.1-alpha, 4.0.15a, and 3.23.58, I get the
> >following results:
> >
> >mysql> SELECT * FROM person WHERE NOT name = 'Bob';
> >Empty set (0.00 sec)
> >
> >mysql> SELECT * FROM person WHERE NOT (name =
> 'Bob');
> >+--+
> >| name |
> >+--+
> >| Jane |
> >+--+
> >1 row in set (0.00 sec)
> >
> >Why do I need the parentheses?  They are not
> required
> >by the SQL specification.  What is the first query
> >really answering?
> 
> In MySQL, NOT has higher precedence than =, so your
> first
> query is equivalent to (NOT name) = 'Bob'.
> 
> Which will compare 1, 0, or NULL to 'Bob', depending
> on the
> value of name.

Two questions (with justifications):

1.  Is giving NOT higher precedence than comparison
operators correct?  My answer:  No.

Based on my reading of the specification, giving NOT a
higher precedence than comparison operators is not
standards compliant.  At the end of this message is
the grammar for the WHERE clause from the 1999 SQL
specification.  Note that the precedence order is
(from highest to lowest) , NOT,
AND, OR.  

2.  What does "NOT x" mean where x is non-NULL and
some type other than boolean?  You said it means "1,
0, or NULL".  NOT NULL returning NULL appears to be
standard complaint, but how would you get 1 or 0?

In addition, consider the following from the 1999
spec:

"4.6.2.1 Operations on booleans that return booleans

The monadic boolean operator NOT and the dyadic
boolean operators AND and OR take boolean operands and
produce a boolean result."

>From this, it seems that NOT only takes booleans
(unless there is another monadic boolean operator
not).  

*

 ::= WHERE 

 ::=


 ::=

|  OR 

 ::=

|  AND 

 ::=
[ NOT ] 

 ::=
 [ IS [ NOT ]  ]

 ::=
TRUE
| FALSE
| UNKNOWN

 ::=

| 
| 

 ::=
  

 ::=

| 
| 
| 
| 
| 
| 
| 
| 
| 
| 
| 
| 


 ::=
  
 ::=

| 
| 
| 
| 
| 

__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: can NOT drop the database

[Adam Hardy]

Hi Heikki,

a similar problem happened to me and I got the error:

ERROR 1051: Unknown table '#sql-ffa_2,#sql-2b2_30'

After reading your post, I checked in the data directory and there are 
two files there, both of file type data:

#sql-2b2_30.frm
#sql-ffa_2.frm
I certainly didn't put them there myself - I think they must have come 
from mySQL automatically at some point.

Are they real tables in the database? I can't see them when I use 'show 
tables'. Is it a permissions problem? I have this on another database 
that I was trying to restore from a dump. I had to rename the database 
in the end.

I am using InnoDB tables for some of my data.

Regards
Adam
On 10/01/2003 09:09 AM Heikki Tuuri wrote:
Feng,

do you have some non-MySQL file in that database directory under the datadir
of MySQL?
Naturally, MySQL will not drop the directory if it contains something more
than just MySQL tables.
Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/

- Original Message - 
From: ""Wang Feng"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Wednesday, October 01, 2003 8:37 AM
Subject: Re: can NOT drop the database



Not at all!!

after I typed the 'drop database my_account_database', I got the following
message:

Query OK, 0 rows affected (0.00sec).
and I do the 'show databases', that one is still there.

cheers,

feng

- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, October 01, 2003 3:31 PM
Subject: Re: can NOT drop the database


no permissions errors ?


Greetings,

I have a database called 'my_account_database' and I tried to drop it
by the command 'drop database my_account_database', it doesn't work.
The database is still there, can not be dropped although the tables
contained in the database have gone.
I subsequently created another 2 databases and could drop them
successfully.
Tried the 'drop database my_account_database' again before sending
this

message, still doesn't work.

So, what could be the problem? Have you seen this situation before?

btw, I can't find out any command which can be used to simply rename a
database, please advise!!
cheers,

feng



--
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]




--
Running mySQL 4.1.0 on Linux 2.4.20 RH9
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Fw: Problem deleting data

[Andrew Pattison]

- Original Message - 
From: "Andrew Pattison" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, October 01, 2003 12:02 PM
Subject: Problem deleting data


> I have a strange problem with MySQL 4.0.15 . When I delete an entire
table,
> then run a repair on that table, it recovers all the rows which I just
> deleted! Also, if I do a "check table" I can see that the files on disk do
> not appear to have been altered in any way by the delete. Does anyone have
> any clues as to what is going on here?
>
> Thanks
>
> Andrew P.
>
> Andrew Pattison, IT Support
> Sterling Furniture Group Ltd
> 01259 75 5135
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to specify --local-infile[=1]?

[Victoria Reznichenko]

Victor Sp?ng Arthursson <[EMAIL PROTECTED]> wrote:
> The manual tells that the option LOAD LOCAL INFIL can be specified at 
> the command line client by setting the --local-infile[=1] -flag.
> 
> How do I do that?

You can put local-infile in the my.cnf:

[mysqld]
local-infile
...

[mysql]
local-infile

You can also specify this option in the command line:
mysql --local-infile



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB speed problems

[mk-my]

Heikki,

> if you can tolerate losing a few last transactions in a power outage or an
> OS crash, you can set
>
> innodb_flush_log_at_trx_commit=2

Does that speed up the thing? I should make some testing.

> Have you shut down mysqld and restarted it after populating the tables?
> MySQL only updates index cardinality statistics when you run ANALYZE TABLE
> or restart the mysqld server.

Sure, I had several restarts while changing settings and testing
again...

Best regards
Matthias



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How to specify --local-infile[=1]?

[Victor Spång Arthursson]

The manual tells that the option LOAD LOCAL INFIL can be specified at 
the command line client by setting the --local-infile[=1] -flag.

How do I do that?

Sincerely

Victor

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select statement to get the difference

[Lourdes Millán]

Stefan Berger escribió:

How to tell it in MySQL

I have 2 tables and i want to find difference (the id exists in one
table but in the other not) between them. 

With SELECT attachment_id from tbl_attachment, tbl_msg2atta where
attachment_id = atta_id
i receive all id wich are equals in both tables.
I need to know which id is not present in the other table.   

In some other database i can use something like not exists but here in
Mysql i have troubles to deal it.
Thanks for hints.

Regards



 

Try this:

SELECT attachment_id from tbl_attachment Left Join tbl_msg2atta On 
tbl_attachment.attachment_id = tbl_msg2atta.atta_id
where IsNull(tbl_msg2atta.atta_id)

Lourdes
--
Sus tiendas favoritas, útiles herramientas de compra y grandes ideas 
para regalos. ¡Compre en línea cómodamente con [EMAIL PROTECTED] 
http://shopnow.netscape.com/



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Counting from diffrent tables, problem

[Egor Egorov]

"Vaidas Zilionis aka Why2liz" <[EMAIL PROTECTED]> wrote:
> I had wroted query
> 
> SELECT r.`id`, COUNT(s.`id`) AS `sk` FROM `sodybos` as s RIGHT OUTER JOIN
> `rajonai` as r ON (s.`rajonai_id` = r.`id`) GROUP BY r.`id`
> 
> But this query run's slow, now I want to optimize it, maybe write it without
> JOIN command
> 
> If I wrote new query (it's more faster)
> 
> select r.id, count(s.id) as sk from rajonai as r, sodybos as s WHERE
> s.rajonai_id = r.id group by r.id
> 
> It's work good, but I can't get rows which count(s.id) = 0
> 
> Example with 1 query I get
> 
> 1|12
> 2|0
> 3|15
> 4|5
> 5|1
> 
> With second only
> 
> 1|12
> 3|15
> 4|5
> 5|1
> 
> How to get and 2 row? Which count result = 0

You got different results because you use different JOINs. In your case you need RIGHT 
or LEFT JOIN:
http://www.mysql.com/doc/en/JOIN.html

How MySQL optimises RIGHT and LEFT JOIN is described at:
http://www.mysql.com/doc/en/LEFT_JOIN_optimisation.html



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL sorts Norwegian/Scandinavian Characters wrong

[Håkon Nilsen \(Exinet AS\)]

Hi,

When I try to sort the alphabet, the three characters only used for the
norwegian language, Æ, Ø and Å, are sorted wrong. They should be sorted in
the order ÆØÅ, but they're sorted ÅÆØ.

I read some place that I could change the character-set to danish, and that
that would solve the issue (as danish and norwegian as the same alphabet),
but it didn't. Anyone know how to do this?


Best regards,
Haakon Nilsen


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select statement to get the difference

[Antony Dovgal]

On Wed, 1 Oct 2003 10:56:59 +0200
"Stefan Berger" <[EMAIL PROTECTED]> wrote:

>  
> I have 2 tables and i want to find difference (the id exists in one
> table but in the other not) between them. 
>  
> With SELECT attachment_id from tbl_attachment, tbl_msg2atta where
> attachment_id = atta_id
> i receive all id wich are equals in both tables.
>  
> I need to know which id is not present in the other table.   

SELECT
attachment_id
FROM
tbl1 LEFT JOIN tbl2
ON 
tbl1.field = tbl2.field
WHERE 
tbl2.field IS NULL;


P.S. read about SELECT & LEFT JOIN in manual.

---
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Select statement to get the difference

[Stefan Berger]

How to tell it in MySQL
 
 
I have 2 tables and i want to find difference (the id exists in one
table but in the other not) between them. 
 
With SELECT attachment_id from tbl_attachment, tbl_msg2atta where
attachment_id = atta_id
i receive all id wich are equals in both tables.
 
I need to know which id is not present in the other table.   
 
In some other database i can use something like not exists but here in
Mysql i have troubles to deal it.
 
Thanks for hints.
 
Regards
 
 

Can't connect to MySQL from remote

[Cedric.Pillonel]

I have some problems with MySQL 3.23.52 on RedHat Linux 8.0.

I can connect to mysql when I am on the same machine but I can't connect to it from 
another machine. Here is the error I get:

> mysqladmin -u cedric -h gdvi02 version
mysqladmin: connect to server at 'gdvi02' failed
error: 'Lost connection to MySQL server during query'

What's the problem ?

Cédric Pillonel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: can NOT drop the database

[Heikki Tuuri]

Wang,

- Original Message - 
From: "Wang Feng" <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, October 01, 2003 10:30 AM
Subject: Re: can NOT drop the database


> Awesome
>
> I did create some text files in that database directory few days ago. And
> you are right, that does the trick.
>
> After I moved those text files, the *drop* works!!! :-)
>
> thanks Heikki.
>
> BTW, how can I rename a database?

if you only have MyISAM tables, you can shut down mysqld and rename the
directory. But that does not work for InnoDB tables. For them, the only way
is to use the command RENAME to raname each table individually.

> feng

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL

Order MySQL technical support from https://order.mysql.com/


> - Original Message -
> From: "Heikki Tuuri" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Wednesday, October 01, 2003 5:09 PM
> Subject: Re: can NOT drop the database
>
>
> > Feng,
> >
> > do you have some non-MySQL file in that database directory under the
> datadir
> > of MySQL?
> >
> > Naturally, MySQL will not drop the directory if it contains something
more
> > than just MySQL tables.
> >
> > Best regards,
> >
> > Heikki Tuuri
> > Innobase Oy
> > http://www.innodb.com
> > Foreign keys, transactions, and row level locking for MySQL
> > InnoDB Hot Backup - a hot backup tool for MySQL
> >
> > Order MySQL technical support from https://order.mysql.com/
> >
> >
> > - Original Message -
> > From: ""Wang Feng"" <[EMAIL PROTECTED]>
> > Newsgroups: mailing.database.myodbc
> > Sent: Wednesday, October 01, 2003 8:37 AM
> > Subject: Re: can NOT drop the database
> >
> >
> > > Not at all!!
> > >
> > > after I typed the 'drop database my_account_database', I got the
> following
> > > message:
> > >
> > > > Query OK, 0 rows affected (0.00sec).
> > >
> > > and I do the 'show databases', that one is still there.
> > >
> > >
> > > cheers,
> > >
> > > feng
> > >
> > >
> > > - Original Message -
> > > From: <[EMAIL PROTECTED]>
> > > To: <[EMAIL PROTECTED]>
> > > Sent: Wednesday, October 01, 2003 3:31 PM
> > > Subject: Re: can NOT drop the database
> > >
> > >
> > > > no permissions errors ?
> > > >
> > > > > Greetings,
> > > > >
> > > > >
> > > > > I have a database called 'my_account_database' and I tried to drop
> it
> > > > > by the command 'drop database my_account_database', it doesn't
work.
> > > > > The database is still there, can not be dropped although the
tables
> > > > > contained in the database have gone.
> > > > >
> > > > > I subsequently created another 2 databases and could drop them
> > > > > successfully.
> > > > >
> > > > > Tried the 'drop database my_account_database' again before sending
> > this
> > > > > message, still doesn't work.
> > > > >
> > > > >
> > > > > So, what could be the problem? Have you seen this situation
before?
> > > > >
> > > > >
> > > > > btw, I can't find out any command which can be used to simply
rename
> a
> > > > > database, please advise!!
> > > > >
> > > > >
> > > > > cheers,
> > > > >
> > > > > feng
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > MySQL General Mailing List
> > > > > For list archives: http://lists.mysql.com/mysql
> > > > > To unsubscribe:
> > > > > http://lists.mysql.com/[EMAIL PROTECTED]
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: can NOT drop the database

[Wang Feng]

Awesome

I did create some text files in that database directory few days ago. And
you are right, that does the trick.

After I moved those text files, the *drop* works!!! :-)

thanks Heikki.

BTW, how can I rename a database?



feng


- Original Message -
From: "Heikki Tuuri" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, October 01, 2003 5:09 PM
Subject: Re: can NOT drop the database


> Feng,
>
> do you have some non-MySQL file in that database directory under the
datadir
> of MySQL?
>
> Naturally, MySQL will not drop the directory if it contains something more
> than just MySQL tables.
>
> Best regards,
>
> Heikki Tuuri
> Innobase Oy
> http://www.innodb.com
> Foreign keys, transactions, and row level locking for MySQL
> InnoDB Hot Backup - a hot backup tool for MySQL
>
> Order MySQL technical support from https://order.mysql.com/
>
>
> - Original Message -
> From: ""Wang Feng"" <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.myodbc
> Sent: Wednesday, October 01, 2003 8:37 AM
> Subject: Re: can NOT drop the database
>
>
> > Not at all!!
> >
> > after I typed the 'drop database my_account_database', I got the
following
> > message:
> >
> > > Query OK, 0 rows affected (0.00sec).
> >
> > and I do the 'show databases', that one is still there.
> >
> >
> > cheers,
> >
> > feng
> >
> >
> > - Original Message -
> > From: <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Wednesday, October 01, 2003 3:31 PM
> > Subject: Re: can NOT drop the database
> >
> >
> > > no permissions errors ?
> > >
> > > > Greetings,
> > > >
> > > >
> > > > I have a database called 'my_account_database' and I tried to drop
it
> > > > by the command 'drop database my_account_database', it doesn't work.
> > > > The database is still there, can not be dropped although the tables
> > > > contained in the database have gone.
> > > >
> > > > I subsequently created another 2 databases and could drop them
> > > > successfully.
> > > >
> > > > Tried the 'drop database my_account_database' again before sending
> this
> > > > message, still doesn't work.
> > > >
> > > >
> > > > So, what could be the problem? Have you seen this situation before?
> > > >
> > > >
> > > > btw, I can't find out any command which can be used to simply rename
a
> > > > database, please advise!!
> > > >
> > > >
> > > > cheers,
> > > >
> > > > feng
> > > >
> > > >
> > > >
> > > > --
> > > > MySQL General Mailing List
> > > > For list archives: http://lists.mysql.com/mysql
> > > > To unsubscribe:
> > > > http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> > >
> > >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: can NOT drop the database

[Heikki Tuuri]

Feng,

do you have some non-MySQL file in that database directory under the datadir
of MySQL?

Naturally, MySQL will not drop the directory if it contains something more
than just MySQL tables.

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL

Order MySQL technical support from https://order.mysql.com/


- Original Message - 
From: ""Wang Feng"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Wednesday, October 01, 2003 8:37 AM
Subject: Re: can NOT drop the database


> Not at all!!
>
> after I typed the 'drop database my_account_database', I got the following
> message:
>
> > Query OK, 0 rows affected (0.00sec).
>
> and I do the 'show databases', that one is still there.
>
>
> cheers,
>
> feng
>
>
> - Original Message -
> From: <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Wednesday, October 01, 2003 3:31 PM
> Subject: Re: can NOT drop the database
>
>
> > no permissions errors ?
> >
> > > Greetings,
> > >
> > >
> > > I have a database called 'my_account_database' and I tried to drop it
> > > by the command 'drop database my_account_database', it doesn't work.
> > > The database is still there, can not be dropped although the tables
> > > contained in the database have gone.
> > >
> > > I subsequently created another 2 databases and could drop them
> > > successfully.
> > >
> > > Tried the 'drop database my_account_database' again before sending
this
> > > message, still doesn't work.
> > >
> > >
> > > So, what could be the problem? Have you seen this situation before?
> > >
> > >
> > > btw, I can't find out any command which can be used to simply rename a
> > > database, please advise!!
> > >
> > >
> > > cheers,
> > >
> > > feng
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > > http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> >
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB speed problems

[Heikki Tuuri]

Matthias,

if you can tolerate losing a few last transactions in a power outage or an
OS crash, you can set

innodb_flush_log_at_trx_commit=2

Have you shut down mysqld and restarted it after populating the tables?
MySQL only updates index cardinality statistics when you run ANALYZE TABLE
or restart the mysqld server.

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL

Order MySQL technical support from https://order.mysql.com/


- Original Message - 
From: <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Wednesday, October 01, 2003 6:04 AM
Subject: InnoDB speed problems


>
> Hi all,
>
> Because I want to use transactions in the future I have converted all
> tables of a copy of our production database server (1800+, 512 MB RAM,
> Linux) to InnoDB format. No problem until now. First, let me show you
> settings in my.cnf:
>
> key_buffer= 16M
> table_cache   = 128
> sort_buffer_size  = 1M
> read_buffer_size  = 1M
> myisam_sort_buffer_size   = 64M
> thread_cache  = 8
> thread_concurrency= 8
>
> innodb_buffer_pool_size  = 256M
> innodb_additional_mem_pool_size  = 20
> innodb_log_file_size = 64M
> innodb_log_buffer_size   = 8M
> innodb_flush_log_at_trx_commit   = 1
> innodb_lock_wait_timeout = 50
>
> Question: Is sort_buffer_size and read_buffer_size relevant to InnoDB?
>
> All these settings seem to be fine for me. With MyISAM I have used a
> key_buffer of 256M and sort_buffer_size of 4M which procuded very fast
> database accesses. mytop's output:
>
> MySQL on localhost (4.0.15-standard-log)
up 0+23:14:39 [04:23:24]
>  Queries: 5.7M   qps:   72 Slow:34.0   Se/In/Up/De(%): 63/10/15/05
>  Cache Hits: 1005.2k Hits/s: 12.3   Ratio: 27.3%
>  Key Efficiency: 100.0%  Bps in/out:  8.0k/33.8k
>
> But now everything is slow, I don't know why. Even without load each
> query takes a bit longer. Shouldn't it be vice versa? Then I did some
> load testing: CPU usage and system load raised by 100 percent. That's
> not normal for me, does InnoDB need more power, more momory? While
> testing MySQL was able to handle all the queries but, well, not as
> fast as I would like to have it in productive environment.
>
> I have also noticed that some more complex queries (select with 4
> joins and 2 orders) last much too long. With MyISAM everything was <
> 0.5s but now I sometimes have a strange one that is listed for several
> houndred seconds (?!) in the process list. That's not normal, isn't
> it? Something strange is going on here and I do not have a clue what I
> could be. Playing around with the settings and raising InnoDB's pool
> size to 80% of memory didn't change anything.
>
> So, I'm not familiar with this great InnoDB thing, maybe you have some
> ideas. :)
>
> Thanks in advance!
> Matthias
>
>
>
>
>
>
>
>
>
>
> -- 
> 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]