Re: tellme Timestamp

[Dan J. Rychlik]

I apologize for this,

But how exactly do I that?

When I run a query that has a timestamp field, How can I query on just the
date.  Time stamp puts in the min, hour, and sec.  All I need is the year,
month, and day.

SELECT * FROM table_name WHERE date = '2003-08-25';


- Original Message -
From: "Antony Dovgal" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, August 23, 2003 9:19 AM
Subject: Re: tellme Timestamp


> On Fri, 22 Aug 2003 17:14:38 -0500
> "Dan J. Rychlik" <[EMAIL PROTECTED]> wrote:
>
> > Hello All,
> >
> > I am trying to find out how I can change my timestamp(14) to
timestamp(8).
> use DATE fields instead.
> or use DATE_FORMAT() with TIMESTAMP to get only date.
>
> ---
> 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]
>


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

Re: MySQL 3.23.54 + update

[Dan Jones]

On Thu, 2003-08-21 at 20:21, Taylor Sittler wrote:
> Is there any way to update table values based on values in another table?
> 
> For instance, given:
> 
> Table 1 (jobid, jobname)
> Table 2 (person, jobid, jobname)
> 
> could I update Table 2, setting jobname=Table1.jobname where 
> Table1.jobid=Table.jobid?  Is it possible to do this in one SQL command, 
> so that SQL matches the updating set to the stored set?

UPDATE Table1, Table2 SET Table2.jobname=Table1.jobname WHERE
Table2.jobid=Table1.jobid;




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

RE: Lots of FULLTEXT stuff (suggestions)

[Steven Roussey]

> Thanks for replying. Your posts that I've found when searching for
> FULLTEXT information have had great ideas. :-) Searching millions of
> posts efficiently and effectively isn't easy. :-( Heh.

FULLTEXT does not scale very well once the files get bigger than your
RAM.

The redesign of the index where it gets normalized will help quite a bit
in reducing the size of the files. For large tables, it will help
immensely.

> Most 1-3 letter words that you don't want indexed should be
> stopwords anyway, right? So why NOT index the ones that are left?
> Doesn't seem like it'd make the index much larger to me. BTW, what is
> your min_word_len value?

I haven't really thought about it. Although I don't see any value in
one-letter words (or numbers). I use min_word_len=3 and my own stop
list, which is merge of stopwords in many languages. I made both changes
at the same time and ended up with a slightly smaller index.

--steve-


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

Re: mysql server crashes - high context switch rate - large 'merge' table

[Heikki Tuuri]

Johannes,

InnoDB asserts because it has waited for a page to be read from a data file
for  > 600 seconds.

I fixed a bug to 4.0.14 where a big index scan on an InnoDB table could
cause this phenomenon. You can try upgrading to 4.0.14.

If the problem is in the MERGE table, then InnoDB is innocent. If the
computer really badly 'freezes' for 600 seconds, then a file read might take
that long.

I do not see how queries waiting for table locks on the MERGE table could
cause context switches inside mysqld. They should just sit and wait.

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: ""Johannes B. Ullrich"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Monday, August 25, 2003 5:51 PM
Subject: mysql server crashes - high context switch rate - large 'merge'
table


> --=-WKgoK98ejo9BZyGYc3N/
> Content-Type: text/plain
> Content-Transfer-Encoding: quoted-printable
>
>
> I am having problems with MySQL 4.0.12 on RedHat Advanced Server 2.1
> using a dual Xeon with 8 GByte of RAM.
>
> I have a database collecting logs. Each day, a new table is created. In
> order to allow for queries across more than one day, I use 'MERGE'
> tables. Each individual table contains 10-30 Million rows. The merge
> table covers 30 days (one month).
>
> At the start of a new day, the merge table is rebuild using an 'alter
> table' statement. However, this statement is sometimes locked for quite
> a while, if queries against the table are pending. In this case,
> the context switch rate of the server 'explodes' to 100,000 and higher.
> The server becomes essentially unresponsive.
>
> Usually, I can 'fix' things by killing some queries that lock the 'alter
> table' querie. However, this causes the server to crash in some cases
> (not well reproducable).
>
> Error message from log:
> =20
>
> --Thread 17924200 has waited at ../../innobase/btr/../include/btr0btr.ic
> line 28 for 1288.00 seconds the semaphore:
> S-lock on RW-latch at 4d0b43b4 created in file buf0buf.c line 369
> a writer (thread id 17567808) has reserved it in mode exclusive
> number of readers 0, waiters flag 1
> Last time read locked in file ../../innobase/btr/../include/btr0btr.ic
> line 28
> Last time write locked in file buf0buf.c line 1332
> InnoDB: Error: semaphore wait has lasted > 600 seconds
> InnoDB: We intentionally crash the server, because it appears to be
> hung.
> 030824 23:48:00  InnoDB: Assertion failure in thread 24583 in file
> sync0arr.c line 934
> InnoDB: We intentionally generate a memory trap.
> InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
> 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 against 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.
> =20
> key_buffer_size=3D209715200
> read_buffer_size=3D10481664
> =20
> Number of processes running now: 1
> mysqld process hanging, pid 26963 - killed
> 030824 23:48:01  mysqld restarted
> 030824 23:48:02  InnoDB: Database was not shut down normally.
> InnoDB: Starting recovery from log files...
> InnoDB: Starting log scan based on checkpoint at
> InnoDB: log sequence number 0 1245512626
> InnoDB: Doing recovery: scanned up to log sequence number 0 1245512626
> InnoDB: In a MySQL replication slave the last master binlog file
> InnoDB: position 0 200671951, file name Sundown2-bin.019
> InnoDB: Last MySQL binlog file position 0 624229439, file name
> ./bob-bin.052
> 030824 23:48:03  InnoDB: Flushing modified pages from the buffer pool...
> 030824 23:48:03  InnoDB: Started
> /usr/sbin/mysqld: ready for connections.
> Version: '4.0.12-log'  socket: '/tmp/mysql.sock'  port: 3306
>
>
>
> --=-WKgoK98ejo9BZyGYc3N/
> Content-Type: application/pgp-signature; name=signature.asc
> Content-Description: This is a digitally signed message part
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.2.1 (GNU/Linux)
>
> iD8DBQA/Sh7bR1p7hYJvB/wRAkQ8AJ9Xa2z5v+gAzTrQPDeQfuIS+BJQ+ACcDrzf
> cmTpPV+y7+j2yGK3kPox+jM=
> =a0zb
> -END PGP SIGNATURE-
>
> --=-WKgoK98ejo9BZyGYc3N/--
>



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

Re: mysql server crashes - high context switch rate - large 'merge' table

[Chad Hanna]

In message <[EMAIL PROTECTED]>, Johannes B. Ullrich 
<[EMAIL PROTECTED]> writes
I am having problems with MySQL 4.0.12 on RedHat Advanced Server 2.1
using a dual Xeon with 8 GByte of RAM.
I have a database collecting logs. Each day, a new table is created. In
order to allow for queries across more than one day, I use 'MERGE'
tables. Each individual table contains 10-30 Million rows. The merge
table covers 30 days (one month).
At the start of a new day, the merge table is rebuild using an 'alter
table' statement. However, this statement is sometimes locked for quite
a while, if queries against the table are pending. In this case,
the context switch rate of the server 'explodes' to 100,000 and higher.
The server becomes essentially unresponsive.
Usually, I can 'fix' things by killing some queries that lock the 'alter
table' querie. However, this causes the server to crash in some cases
(not well reproducable).
Error message from log:
:Snipped.

Can I suggest you try creating a second merge table with a different 
name and then doing a double rename to swap the two merge tables i.e.
"rename table merge to merge_old, merge_new to merge".

Anyway that's what I'd try next.

Cheers

--
Chad Hanna
Chairman Berkshire Family History Society www.berksfhs.org.uk
Quality Family History Data www.familyhistoryonline.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Raising Relevance

[Stuart Gagnon]

Dear MySQL developers:

This is probably a little surprising for you -- getting a question like this
from someone who is not a software developer.  But, I saw that this list
looked at a 'weighted average' question back in July of last year, and
various other 'weighting' issues. Maybe people from this list can help me
with a 'raising relevance' question.

I'm a librarian working on a database of bibliographic information for
agriculture and water quality.  [Sorry about any incorrect nomenclature, if
I start speaking librarian-ese.]  

Our MySQL Db (offline) is the newest version of this searchable Web
directory (at this point -- under 2000 records with fewer than 30 fields
configured in one table):

http://www.nal.usda.gov/wqic/wqdb/esearch.html

Here is my problem:  I want highly relevant results at the top of the
results list from a relevance search.  Out on the Web, most of us expect
this from search engines.  I'm certain it's not magic; but it may be
difficult to design into open source software.

Here is the scenario: Search terms contained in the Title column are the
most important.  If both queried terms (below example: nutrient* and
management*) show up in Title, I want those rows to return high up in the
results.  To me, this is a weighting question: As in applying a weighted
average to a set of fields prior to searching for data in those fields.
With a weighted average, someone might indicate that the Title field is the
more important of several fields in a search and change the average for that
field to 50%.  The other fields could be adjusted as applicable.  Retrieval
on specific terms would lift records with those terms in Title field to the
top of the results list.  I hope to make this the default search for
first-time users of the database.

We haven't been able to find out how it works, or if it's possible, in
MySQL.  

I've provided one SQL statement from a search page that may illustrate my
need.  This is the statement created by our developer to run what we are
calling a 'best match' (relevance) search.  I would like the Title column to
be the most heavily weighted.

SELECT *,MATCH
(Title,Subject_NALThesauru,Subject_ControlledV,Coverage_Jurisdicti)
AGAINST ('nutrient* management*' IN BOOLEAN MODE ) as Relevance FROM WQTest2
WHERE MATCH
(Title,Subject_NALThesauru,Subject_ControlledV,Coverage_Jurisdicti)
AGAINST('nutrient* management*' IN BOOLEAN MODE) having Relevance > 0.2
ORDER BY Relevance DESC LIMIT 0,20

Questions:  Can 'relevance weighting' be changed for specific fields within
one MySQL table?   Or, is a 'weighting command' executed through the SQL
statement?  Specifically, how would we set up this search to produce
better-organized and more-relevant results?

I don't know the specifics of the hardware and other configuration factors,
but CAN find out.  Just ask me what you need to know.  

Any advice would be appreciated.  Thanks very much.

Best regards,

--Stu Gagnon
==
Stuart Gagnon, University of Maryland Cooperator Librarian 
Water Quality Information Center, National Agricultural Library 
10301 Baltimore Avenue, 130 West Wing 
Beltsville, Maryland 20705-2351 
[EMAIL PROTECTED]




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

Re: UPDATE based on value in another table

[Dan Jones]

On Sat, 2003-08-23 at 22:19, Rajesh Kumar wrote:
> Dan Jones unknowingly asked us:
> 
> > UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN
> > table2.ID;
> > 
> > The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go
> > there.
> 
> In MySql, its 'NOT IN'
> 
> and not
> 
> 'NOT FOUND IN'.

To make this a tad clearer, I have two tables: Title and Author.  Author
consists of the Author's name and an AuthorID.  Title includes an
AuthorID from the Author table.  Some of the Author's have been deleted
from the Author table.  I want to remove any AuthorID entries from the
Title table that no longer exists in the Author table.

UPDATE Title
SET AuthorID=NULL 
WHERE AuthorID NOT IN Author.AuthorID;

This gives a syntax error "...near Author.AuthorID."

UPDATE Title
SET AuthorID=NULL 
WHERE AuthorID NOT IN (Author.AuthorID);

This seems to treat (Author.AuthorID) as a list of data rather than a
field, and sets every Title.AuthorID to NULL.

My SQL book, a generic reference not specific to any particular
database, indicates the way to do this is via UPDATE with a subquery,
like so:

UPDATE Title
SET AuthorID=NULL 
WHERE NOT IN (SELECT AuthorID FROM Author);

This gives me a syntax error "...near SELECT AuthorID FROM Author)"

Does MySQL not support UPDATE with subqueries or am I screwing up the syntax somehow?




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

Re: how to show all locks on a table?

[Bennett Haselton]

At 03:49 PM 8/25/2003 +0300, Egor Egorov wrote:
Bennett Haselton <[EMAIL PROTECTED]> wrote:
>
> I found a way to do this before, but I didn't write down how I did it, 
so I
> don't remember it now.  And I've searched http://www.mysql.com/doc/ in
> vain.
>
> What's the command to show all current locks on a database table?
>

There is no command to show locks on the table.
Ah, OK.  I'm trying to think of what I must have seen earlier that made me 
think there was a command to do this.

I think I was thinking of the SHOW PROCESSLIST command, which shows a list 
of threads and indicates whether they are waiting for a lock.  I must have 
looked at that output and figured that I knew what the locked tables were.

-Bennett

[EMAIL PROTECTED] http://www.peacefire.org
(425) 497 9002
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Finding warnings/skips from mysqlimport

[gord barq]

Hi,

I'm importing ~2000 rows using mysqlimport with the following syntax:

mysqlimport -d  -v --ignore  -u root -p[rootpw] [database] [tablename].txt

Connecting to localhost
Selecting database [database]
Deleting the old data from table [tablename]
Loading data from SERVER file: /[path]/[tablename] into [tablename]
[database].[tablename]: Records: 1780  Deleted: 0  Skipped: 39  Warnings: 
1178
Disconnecting from localhost

But even with verbose on it doesn't tell me what the warnings are, or what 
rows were skipped. Where can I find that information?

Thanks.

_
MSN 8: Get 6 months for $9.95/month. 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]

Re: Optimize Question

[Dan Nelson]

In the last episode (Aug 25), Patrick Crowley said:
> I'm running 3.23.52 locally, but I just noticed my production server
> is running 4.0.12.
> 
> Given the complexity of this query, should I use subqueries instead
> of joins? Would that make any difference?

The subquery equivalent to your joins should be processed exactly the
same way (barring optimizer bugs).  You would have to use 4.1.0 to get
subqueries anyway.

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Re: simple query

[Roger Baklund]

* Pag
>   Imagine i have a table "phrases" with a field "ph" with
> contents like these:
>
>   - who was it
>   - who wasnt it
>   - no i didnt
>   - yes i was
>   - dont know who
>
>   I want to make a SELECT that gives me only the entries that
> have the word "who":
>
>   Something like
>
>   SELECT * FROM `phrases` WHERE ph="who*";
>
>   I tried the manual but cant make sense of it. How can we
> use wildcards on selects?

See the LIKE operator:

http://www.mysql.com/doc/en/String_comparison_functions.html#IDX1234 >
http://www.mysql.com/doc/en/MySQL_indexes.html#IDX905 >

SELECT * FROM `phrases` WHERE ph LIKE "who%";

However, this would not return your last example, "dont know who". To match
any occurences of "who", you can use this:

SELECT * FROM `phrases` WHERE ph LIKE "%who%";

Note that an index can not be used in this case, making it slower when you
have a lot of data. Another problem is words containing other words: the
last SELECT statement would also match "knowhow".

To only match the word "who", you could try something like this:

SELECT * FROM `phrases` WHERE
  ph = "who" OR
  ph LIKE "who %" OR
  ph LIKE "% who" OR
  ph LIKE "% who %";

This statement would however not find this value: "who, if any".

You could take a look at regular expressions:

http://www.mysql.com/doc/en/String_comparison_functions.html#IDX1239 >

And finally, the FULLTEXT feature could possibly be of use for you:

http://www.mysql.com/doc/en/Fulltext_Search.html >

HTH,

--
Roger


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

Re: Multi-table query in one select - possible?

[Roger Baklund]

* Mike Morton 
> Runnint Mysql 3.23.47
> 
> I have the following two tables:
> backorder_notification
> ->product_code varchar
> ->email varchar
> ->date_added date
> Products
> ->code varchar
> ->name varchar
> ->other stuff that is not important
> 
> Essencially - I want to get a count of the product codes that are in the
> backorder_notification table ordered by count then by product name and
> output the display:
> 
> CodeNameCount

You can join them all, group, count and order in the same query:

SELECT 
product_code, 
name, 
count(product_code) as total  
  FROM backorder_notifications,products
  WHERE
backorder_notifications.product_code = products.code
  GROUP BY product_code, name
  ORDER BY total

HTH,

-- 
Roger

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

RE: simple query

[Allen Weeks]

Try "SELECT * FROM `phrases` WHERE ph like "%who%";

The percent symbol is the wildcard character for mysql queries

HTH



> -Original Message-
> From: Pag [mailto:[EMAIL PROTECTED]
> Sent: Monday, August 25, 2003 11:08 AM
> To: [EMAIL PROTECTED]
> Subject: simple query
> 
> 
> 
> 
>   Imagine i have a table "phrases" with a field "ph" with 
> contents like these:
> 
>   - who was it
>   - who wasnt it
>   - no i didnt
>   - yes i was
>   - dont know who
> 
>   I want to make a SELECT that gives me only the entries that 
> have the word 
> "who":
> 
>   Something like
> 
>   SELECT * FROM `phrases` WHERE ph="who*";
> 
>   I tried the manual but cant make sense of it. How can we 
> use wildcards on 
> selects?
> 
>   Thanks  
> 
>   Pag
> 
> 
> 
> -- 
> 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: Restricting user access to databases

[Yves Goergen]

you give the root user all privileges (see GRANT command) (but this should be set by 
default, anyway) and no (global) privileges (that is, 'usage') for other users. then 
you give the users only contents/structure related rights on their databases.

did you try phpmyadmin for this yet? it's a bit tricky, but it's very close to mysql's 
view of things, so you can learn easily by looking at the mysql commands generated.

--
Yves Goergen
[EMAIL PROTECTED]
Please don't CC me (causes double mails)


-Ursprüngliche Nachricht- 
Von: "System Administrator a.k.a. The Root of the Problem" <[EMAIL PROTECTED]>
An: <[EMAIL PROTECTED]>
Gesendet: Montag, 25. August 2003 19:12
Betreff: Restricting user access to databases


> Just wondering, how do I limit a user to one database but
> let the administrator have universal access?
> 
> -- 
> 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]

simple query

[Pag]

	Imagine i have a table "phrases" with a field "ph" with contents like these:

- who was it
- who wasnt it
- no i didnt
- yes i was
- dont know who
	I want to make a SELECT that gives me only the entries that have the word 
"who":

	Something like

	SELECT * FROM `phrases` WHERE ph="who*";

	I tried the manual but cant make sense of it. How can we use wildcards on 
selects?

	Thanks	

	Pag



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

Re: Optimize Question

[Patrick Crowley]

I'm running 3.23.52 locally, but I just noticed my production server is
running 4.0.12.

Given the complexity of this query, should I use subqueries instead of
joins? Would that make any difference?

Best,
Patrick

> What version of mysql is this?  If you're running 3.23, does 4.0.14 do the
> same thing?  And if you can try it, does 4.1.0?


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

Re: Optimize Question

[Dan Nelson]

In the last episode (Aug 25), Patrick Crowley said:
> I just tried that, but with mixed results.
> 
> Like before, when I added an index on publish, I get a 'range' join, but
> it's still doing filesort.
> 
> > | theater | range | status_publish | 1 | NULL | 4279 | where used; Using filesort
> 
> Any way around that?

Hm.  With a compound index on all those fields, it shouldn't have to
sort anything.  I wonder why keylen is '1' in what you pasted there. 
That sounds like it decided it didn't need (or couldn't use) the rest
of the index.

What version of mysql is this?  If you're running 3.23, does 4.0.14 do the
same thing?  And if you can try it, does 4.1.0?

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Multi-table query in one select - possible?

[Mike Morton]

Runnint Mysql 3.23.47

I have the following two tables:
backorder_notification
->product_code varchar
->email varchar
->date_added date
Products
->code varchar
->name varchar
->other stuff that is not important

Essencially - I want to get a count of the product codes that are in the
backorder_notification table ordered by count then by product name and
output the display:

CodeNameCount

I am using PHP to do the connect, query and display.

Right now I have the following code:
$res=mysql_query("select distinct product_code from backorder_notifications
order by product_code");

while($row=mysql_fetch_array($res)) {
$pres=mysql_query("select name from products where
code='$row[product_code]'",$c)
 $products=mysql_fetch_array($pres);
 $cres=mysql_query("select count(product_code) as total from
backorder_notifications where product_code='$row[0]'",$c);
 $counts=mysql_fetch_array($cres);

  print "  
  $row[product_code]
  $products[name]
  $counts[total]";
}

And while this works, it sorts only by product code.  I am looking for
help/advice on how to put this into one query so I only have to loop through
a result set once without doing all sorts of other connections.

As you can see I am using MYSQL 3 - so no union queries - and I cannot
upgrade the database since it is on a host provider.

Any assistance is appreciated!

TIA

--
Cheers

Mike Morton


*
* Tel: 905-465-1263
* Email: [EMAIL PROTECTED]
*


"Indeed, it would not be an exaggeration to describe the history of the
computer industry for the past decade as a massive effort to keep up with
Apple."
- Byte Magazine

Given infinite time, 100 monkeys could type out the complete works of
Shakespeare. Win 98 source code? Eight monkeys, five minutes.
-- NullGrey 


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

Re: MySQL 3.23.54 + update

[Taylor Sittler]

Is there any way to update table values based on values in another table?

For instance, given:

Table 1 (jobid, jobname)
Table 2 (person, jobid, jobname)
could I update Table 2, setting jobname=Table1.jobname where 
Table1.jobid=Table.jobid?  Is it possible to do this in one SQL command, 
so that SQL matches the updating set to the stored set?

Thus far I have been performing the updates 1 row at a time using 
python.  This process is exceedingly slow for large tables.  Any help 
would be greatly appreciated.
Thanks
Taylor

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

Re: Optimize Question

[Patrick Crowley]

I just tried that, but with mixed results.

Like before, when I added an index on publish, I get a 'range' join, but
it's still doing filesort.

> | theater | range | status_publish | 1 | NULL | 4279 | where used; Using
filesort

Any way around that?

Best,
Patrick

> An index on t (publish,photo,name_present,location_city) would be the
> most effiecient, I think.


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

Restricting user access to databases

[System Administrator a.k.a. The Root of the Problem]

Just wondering, how do I limit a user to one database but
let the administrator have universal access?

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

Re: Optimize Question

[Dan Nelson]

In the last episode (Aug 25), Patrick Crowley said:
> Hi all!
> 
> I'm trying to optimize the statement below, but I can't figure out why this
> query is still doing an ALL join with a filesort.
> 
> There are several indexes on both my main table ('t'), and all the tables
> I'm trying to join. (I also tried adding an index for t.publish, which
> bumped my join type to 'range' but still gave me a 'filesort' and seemed to
> produce slower query results.)

An index on t (publish,photo,name_present,location_city) would be the
most effiecient, I think.

> **SELECT STATEMENT**
> 
> SELECT t.id, t.name_present, t.location_city, t.photo, a.name_short,
> a.name_long, b.name as b_name, c.name as c_name, d.id as d_id, d.name
> as d_name
> FROM t
> LEFT JOIN a ON t.a_id = a.id LEFT JOIN b ON t.b_id = b.id 
> LEFT JOIN c ON t.c_id = c.id LEFT JOIN d ON t.d_id = d.id 
> LEFT JOIN e_t ON e_t.t_id = t.id LEFT JOIN e ON e_t.e_id = e.id
> WHERE  t.publish = 'Yes' AND e.id = '2'
> ORDER  BY t.photo, t.name_present, t.location_city
> LIMIT  0,25
> 
> **EXPLAIN RESULTS FOR THIS QUERY**
> +-++-+--+--+--+-+
> | tbl | type   | key |keylen| ref  | rows | Extra   |
> +-++-+--+--+--+-+
> | t   | ALL| NULL| NULL | NULL | 4279 | where used; filesort|
> | a   | eq_ref | PRIMARY |2 | t.a_id   |1 | |
> | b   | eq_ref | PRIMARY |2 | t.b_id   |1 | |
> | c   | eq_ref | PRIMARY |1 | t.c_id   |1 | |
> | d   | eq_ref | PRIMARY |1 | t.d_id   |1 | |
> | e   | eq_ref | PRIMARY |1 | e_t.e_id |1 | where used; index   |
> | e_t | ref| t_id|2 | t.id |1 | |
> +-++-+--+--+--+-+
> ('possible_keys' was identical to 'key', but i removed it due to space
> issues)
> 
> **INDEXES ON 't'**
> +---+---+
> | Key_name  | Column_name   |
> +---+---+
> | PRIMARY   | id|
> | location_city | location_city |
> | name_present  | name_present  |
> | a_id  | a_id  |
> | b_id  | b_id  |
> | c_id  | c_id  |
> | d_id  | d_id  |
> +---+---+

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Optimize Question

[Patrick Crowley]

Hi all!

I'm trying to optimize the statement below, but I can't figure out why this
query is still doing an ALL join with a filesort.

There are several indexes on both my main table ('t'), and all the tables
I'm trying to join. (I also tried adding an index for t.publish, which
bumped my join type to 'range' but still gave me a 'filesort' and seemed to
produce slower query results.)

What can I do to further optimize?

Thanks,
Patrick

...

**SELECT STATEMENT**

SELECT
t.id,
t.name_present,
t.location_city,
t.photo,
a.name_short,
a.name_long,
b.name as b_name,
c.name as c_name,
d.id as d_id,
d.name as d_name

FROM t

LEFT JOIN a
ON t.a_id = a.id
LEFT JOIN b
ON t.b_id = b.id
LEFT JOIN c
ON t.c_id = c.id
LEFT JOIN d
ON t.d_id = d.id
LEFT JOIN e_t
ON e_t.t_id = t.id
LEFT JOIN e
ON e_t.e_id = e.id

WHERE  t.publish = 'Yes' AND e.id = '2'
ORDER  BY t.photo, t.name_present, t.location_city
LIMIT  0,25

**EXPLAIN RESULTS FOR THIS QUERY**
+-++-+-+--+--+-+
| tbl | type   | key | key_len | ref  | rows | Extra   |
+-++-+-+--+--+-+
| t   | ALL| NULL|NULL | NULL | 4279 | where used; filesort|
| a   | eq_ref | PRIMARY |   2 | t.a_id   |1 | |
| b   | eq_ref | PRIMARY |   2 | t.b_id   |1 | |
| c   | eq_ref | PRIMARY |   1 | t.c_id   |1 | |
| d   | eq_ref | PRIMARY |   1 | t.d_id   |1 | |
| e   | eq_ref | PRIMARY |   1 | e_t.e_id |1 | where used; index   |
| e_t | ref| t_id|   2 | t.id |1 | |
+-++-+-+--+--+-+
('possible_keys' was identical to 'key', but i removed it due to space
issues)

**INDEXES ON 't'**
+---+---+
| Key_name  | Column_name   |
+---+---+
| PRIMARY   | id|
| location_city | location_city |
| name_present  | name_present  |
| a_id  | a_id  |
| b_id  | b_id  |
| c_id  | c_id  |
| d_id  | d_id  |
+---+---+


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

Upgrade from 3.23 to 4 in Linux

[Patricio E. Herrero Ducloux]

Hi,

  I have an installation of MySQL 3.23 located in /usr/local/mysql.3.23.51, 
which is working perfectly. Now I installed in /usr/local/mysql.4.0.14 the 
binaries for a new version (4.0.14 of course), and don't know how to start 
using it instead of the older one. I have a link: /usr/local/mysql pointing 
to /usr/local/mysql.3.23.51 and some few databases in that directory.
  My data files are in MyISAM format.
  How do I migrate the database files? How do I tell Linux to use this 
version?
  I never did a task like this in Linux, for I always used MS Operating 
Systems, so please be as clear as possible... In fact, I didn't understand 
the MySQL manual about this topic.
  Thank you very much in advance,

Patricio.

_
Add photos to your messages with MSN 8. Get 2 months FREE*. 
http://join.msn.com/?page=features/featuredemail

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

RE: Master-Slave Replication

[Sanya Shaik]

Got it to  work after the restart. Now works fine. 
 
SO now back to the Master-Master replication 
 
 
Dathan Vance Pattishall <[EMAIL PROTECTED]> wrote:
Type show slave status to figure out what the problem is on the slave.

Type show full processlist on the master to see if the slave is
connected waiting for binlog updates.

Make sure the master is replicating..

Etc.

-->-Original Message-
-->From: Sanya Shaik [mailto:[EMAIL PROTECTED]
-->Sent: Thursday, August 21, 2003 12:51 PM
-->To: [EMAIL PROTECTED]
-->Subject: Master-Slave Replication
-->
-->Hi all,
-->
--> Thanks for the answers for Master-Master replication. Right now i
want
-->to try the Master-Slave replication first and then do a circular
-->replication.
-->
-->Unfortunately, I am facing problems with updating slave
automatically.
-->
-->I started the slave and loaded the data from the master, later any
-->changes made to master are not reflected on slave.
-->
-->Please HELP me in this regard.
-->
-->
-->-
-->Do you Yahoo!?
-->The New Yahoo! Search - Faster. Easier. Bingo.



-
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.

Re: pswd hlp!!!!

[Victoria Reznichenko]

"Silent Bob" <[EMAIL PROTECTED]> wrote:
> 
> I need to change my mysql password but don't know how to do it! My server crashed 
> because of power failure and when I turn it on the MySQL service won't start. It 
> only says "
> 
> "Starting MySQL ServerYou are required to change your password immediately (password 
> aged)"
> 
> I don't know how to change the password. I tried userconf but it freezes when I try 
> to change MySQL user password.
> 
> Please I need help with this urgently. 

Use SET PASSWORD command or GRANT command or mysqladmin password:
http://www.mysql.com/doc/en/Passwords.html


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

pswd help

[Silent Bob]

Hello

I need to change my mysql password but don't know how to do it! My
server crashed because of power failure and when I turn it on the MySQL
service won't start. It only says "

"Starting MySQL ServerYou are required to change your password
immediately (password aged)"

I don't know how to change the password. I tried userconf but it freezes
when I try to change MySQL user password.

Please I need help with this urgently. 

Thank You

Kalle

mysql server crashes - high context switch rate - large 'merge' table

[Johannes B. Ullrich]

I am having problems with MySQL 4.0.12 on RedHat Advanced Server 2.1
using a dual Xeon with 8 GByte of RAM.

I have a database collecting logs. Each day, a new table is created. In
order to allow for queries across more than one day, I use 'MERGE'
tables. Each individual table contains 10-30 Million rows. The merge
table covers 30 days (one month).

At the start of a new day, the merge table is rebuild using an 'alter
table' statement. However, this statement is sometimes locked for quite
a while, if queries against the table are pending. In this case,
the context switch rate of the server 'explodes' to 100,000 and higher.
The server becomes essentially unresponsive.

Usually, I can 'fix' things by killing some queries that lock the 'alter
table' querie. However, this causes the server to crash in some cases
(not well reproducable).

Error message from log:
 

--Thread 17924200 has waited at ../../innobase/btr/../include/btr0btr.ic
line 28 for 1288.00 seconds the semaphore:
S-lock on RW-latch at 4d0b43b4 created in file buf0buf.c line 369
a writer (thread id 17567808) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file ../../innobase/btr/../include/btr0btr.ic
line 28
Last time write locked in file buf0buf.c line 1332
InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be
hung.
030824 23:48:00  InnoDB: Assertion failure in thread 24583 in file
sync0arr.c line 934
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
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 against 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=209715200
read_buffer_size=10481664
 
Number of processes running now: 1
mysqld process hanging, pid 26963 - killed
030824 23:48:01  mysqld restarted
030824 23:48:02  InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 1245512626
InnoDB: Doing recovery: scanned up to log sequence number 0 1245512626
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 200671951, file name Sundown2-bin.019
InnoDB: Last MySQL binlog file position 0 624229439, file name
./bob-bin.052
030824 23:48:03  InnoDB: Flushing modified pages from the buffer pool...
030824 23:48:03  InnoDB: Started
/usr/sbin/mysqld: ready for connections.
Version: '4.0.12-log'  socket: '/tmp/mysql.sock'  port: 3306




signature.asc
Description: This is a digitally signed message part

RE: "union" question

[Marek Lewczyk]

One more thing: the question isn't "how to solve the problem" - becouse
it's very easy, but "wy it's working on 4.0.14 and not on 4.1.0".

> -Original Message-
> From: Marek Lewczyk [mailto:[EMAIL PROTECTED] 
> Sent: Monday, August 25, 2003 4:15 PM
> To: [EMAIL PROTECTED]
> Subject: "union" question
> 
> 
> Hello,
> Currently I'm testing my app using MySQL 4.1.0 version, and I 
> have a strange error during execution a union query.
> 
> (SELECT IF(_DAT.pri <=> null, null, ROUND(_DAT.pri/1.22)) AS 
> pri_net, IF(_DAT.pri <=> null, null, ROUND(_DAT.price*1)) AS 
> pri_gross, _DAT.cuid, _CUR.code FROM tab1 _DAT, tab2 _CUR 
> WHERE _DAT.eid = '6925' AND _DAT.did = '3' AND _CUR.cuid = 
> _DAT.cuid ) 
> UNION 
> (SELECT IF(pri <=> null, null, ROUND(pri/1.22)) AS prinet, 
> IF(pri <=> null, null, ROUND(pri*1)) AS pri_gross, null AS 
> cuid, null AS code FROM tab3 WHERE eid = '6925' AND did = '3' 
> AND (enid = '1358' OR boid = '1004' OR trid = '779' OR coid = 
> '9773') ORDER BY coid DESC, veid DESC, yeid DESC, trid DESC, 
> boid DESC, enid DESC LIMIT 1 )
> 
> In version 4.0.14 all was working fine, but there is an error 
> in 4.1.0: "Column 'code' cannot be null"
> 
> Any ideas ??
> 
> Regards,
> ML
> 
> 
>  
> 
> -- 
> 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]

pswd hlp!!!!

[Silent Bob]

Hello

I need to change my mysql password but don't know how to do it! My server crashed 
because of power failure and when I turn it on the MySQL service won't start. It only 
says "

"Starting MySQL ServerYou are required to change your password immediately (password 
aged)"

I don't know how to change the password. I tried userconf but it freezes when I try to 
change MySQL user password.

Please I need help with this urgently. 

Thank You

Kalle

Re: Difficulty with thai columns

[Egor Egorov]

John Francis Lee <[EMAIL PROTECTED]> wrote:
> 
> I've downloaded 4.1 and am experimenting with a thai-english word list
> to discover just how this will work with mysql.
> 
> So far I'm not having success.
> 
> First I tried 
> create table words (
>   thai varchar(30) character set tis620,
>   thai_sense int,
>   english varchar(30) character set latin1,
>   english_sense int
>  )  ; 
> 
> But when I tried
> insert into words_utf8 (
> thai_word,
> thai_sense,
> english_word,
> english_sense
> ) values ( '???', 1, 'water', 1) ;
> 
> I got a broken connection.

You created a table 'words', but tried to insert data into table words_utf8.

> 
> I figured this was because I was entering utf8 and expecting tis620.
> 
> So I
> CREATE TABLE words_utf8 (
>  thai_word varchar(30),
>  thai_sense int,
>  english_word varchar(30),
>  english_sense int
> ) TYPE=MyISAM CHARSET=utf8
> 
> (coincidentally I had to edit the 'invariant' output of 'show create
> table' to make this work. There were extraneous forward quotes around
> the table and column names that made mysql choke.)
> 
> And then the simple insertion worked ok, but the output from a select 
> 
> mysql> select * from words_utf8 ;
> +---++--+---+
> | thai_word | thai_sense | english_word | english_sense |
> +---++--+---+
> | ??? |  1 | water| 1 |
> | ??? |  1 | water| 1 |
> | ??? |  1 | water| 1 |
> +---++--+---+
> 3 rows in set (0.00 sec)
> 
> shows unrenderable characters for the Thai.
> 
> So what do I do about this?
> 

What is your client character set?



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

"union" question

[Marek Lewczyk]

Hello,
Currently I'm testing my app using MySQL 4.1.0 version, and I have a
strange error during execution a union query.

(SELECT IF(_DAT.pri <=> null, null, ROUND(_DAT.pri/1.22)) AS pri_net,
IF(_DAT.pri <=> null, null, ROUND(_DAT.price*1)) AS pri_gross,
_DAT.cuid, _CUR.code FROM tab1 _DAT, tab2 _CUR WHERE _DAT.eid = '6925'
AND _DAT.did = '3' AND _CUR.cuid = _DAT.cuid ) 
UNION 
(SELECT IF(pri <=> null, null, ROUND(pri/1.22)) AS prinet, IF(pri <=>
null, null, ROUND(pri*1)) AS pri_gross, null AS cuid, null AS code FROM
tab3 WHERE eid = '6925' AND did = '3' AND (enid = '1358' OR boid =
'1004' OR trid = '779' OR coid = '9773') ORDER BY coid DESC, veid DESC,
yeid DESC, trid DESC, boid DESC, enid DESC LIMIT 1 )

In version 4.0.14 all was working fine, but there is an error in 4.1.0:
"Column 'code' cannot be null"

Any ideas ??

Regards,
ML


 

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

OLAP Proposal for MySQL

[Philip Stoev]

Hi all,

Please tell me if any of this makes sense. Any pointers to relevant
projects/articles will be much appreciated.

Philip Stoev
http://www.stoev.org/pivot/manifest.htm

===

OLAP PROPOSAL FOR MYSQL

The goal is to create an OLAP engine coupled with a presentation layer that
will be easy enough for normal people to use, with no MDX experience
required. While it is probably a fact that Wal-Mart has 70 GB of data, this
does not mean that all people have such data sets, so the goal is reasonable
performance for reasonably-sized datasets. Most people do not join 30 tables
together either. Also, it is pre-supposed that Wal-Mart engage in
extra-complex calculations to determine business strategies, most people are
often content to know "How much I sold yesterday".

I. OLAP ENGINE AND CACHING

The OLAP "engine" takes a standard SQL query with GROUP BY statements and
aggregate functions, executes it, and saves the entire resulting dataset in
the cache. A cache index entry is then created, noting what the source
tables, the GROUP_BY columns, the aggregate functions and the WHERE
conditions that were used.

Upon execution of further queries, the OLAP engine checks the cache whether
there is a cached dataset that can be used to answer the query immediately.
This would include any of the following:

1. The query's GROUP BY columns are equal or a sub-set of the cached query.
So, a query like:
SELECT salesman, state, SUM(sales) FROM company.sales GROUP BY
salesman, state
provides the answer for
SELECT salesman, SUM(sales) FROM company.sales GROUP BY salesman

2. The query's WHERE clause is equal or more restrictive to the WHERE clause
of a cached query, and contains columns that were GROUP BY-ed.
A query like:
SELECT date, salesman, SUM(sales) FROM company.sales GROUP BY
date, salesman WHERE date > '2003-01-01'
provides the answer for:
SELECT date, salesman, SUM(sales) FROM company.sales GROUP BY
date, salesman WHERE date > '2003-01-01' AND date > '2003-06-01'
Obviously, a human will not write a query with such a WHERE statement,
however a graphical Pivot tool may be explicitly designed to create such a
query when drilling-down so that a cache hit is scored.

3. The query's source tables are equal or a sub-set of the cached query's
source tables.
So, the query:
SELECT salesman, gender, SUM(sales) FROM company.sales INNER JOIN salesman
USING (salesman_id) GROUP BY salesman, gender
or even something very complex with 10 joined tables, can be used to answer:
SELECT salesman, SUM(sales) FROM company.sales GROUP BY salesman
or even something even more complex with 5 joined tables

4. The query's aggregate functions are equal of a sub-set of the cached
query's. Certain aggregate functions may not be cached like COUNT(DISTINCT),
and others require special care (AVERAGE(value) must be translated to
SUM(value)/COUNT(value)).

The benefits of such a cache implementation is that is it data-independent.
You do not have to describe your data prior to executing your queries. It
also does not rely on creating your own cache structure and your own cache
index - a few tables can be used to hold the cache index and can be then
queried by SQL themselves to determine a hit.

If an interactive Pivoting tool is executing those queries, the cache should
(hopefully) soon fill with entries that allow most, if not all, of the
queries resulting from interactive browsing to be served from the cache.
Additionally, the tool can apply for pre-fetching of relevant data by
drilling down a bit more than the user has requested, resulting in a cache
hit when the user indeed drills deeper. Also, the tool does not have to
cache data to sort it on its own, since queries that differ only in their
SORT BY are cached. An additional enhancement would be the ability to serve
a hit from the cache using more than one cached table.

Example:

A. No cache hit, so we just populate the cache
Initial query:
SELECT salesman, state, COUNT(*) FROM sales GROUP BY salesman,
state
The server does:
CREATE TABLE 1234567 SELECT salesman, COUNT(*) FROM sales GROUP
BY salesman, state
SELECT * FROM 1234567

B. A cache hit
Initial query:
SELECT state, COUNT(*) FROM sales GROUP BY state
The server does:
SELECT state, SUM(`COUNT(*)`) AS `COUNT(*)` FROM 1234567 GROUP
BY state
[`COUNT(*)` being a valid column name for table 1234567]

II. DATA DESCRIPTION AND MANIPULATION

1. In my humble opinion, people do not think in MDX. Instead, they think in
terms of GROUP BY. So, for most uses, it should be sufficient to allow the
user to construct his own GROUP BY statement and specify the aggregate
functions that he is interested in, rather than asking him to create a cube,
an axis, a view, a measure, etc, etc.

2. People also think in terms of everyday phrases, like "last 7 days" or
"all Mondays". A pre-compiled dictionary of such phrases will be imme

Re: own privileges

[Victoria Reznichenko]

[EMAIL PROTECTED] wrote:
> Is there a way for a user to get knowledge about his own privileges?
> For example can a user know if he/she has rights to INSERT INTO some table?
> I'm using PHP4
> 

User can use SHOW GRANTS command:
http://www.mysql.com/doc/en/SHOW_GRANTS.html


-- 
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: UPDATE based on value in another table

[Victoria Reznichenko]

Dan Jones <[EMAIL PROTECTED]> wrote:
> How do I update a table to remove orphaned references to a second
> table?  I've deleted rows in the second table, which has a unique
> auto_increment key.  The first table now has references to keys that no
> longer exist.  I need to update the first table, setting the value to
> NULL where the referenced key no longer exists.  Something like:
> 
> UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN
> table2.ID;
> 
> The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go
> there.

If you have MySQL server version 4.0.4 or newer, you can do something like:

UPDATE table1 LEFT JOIN table2 ON table1.table2ID=table2.ID SET table1.table2ID=NULL 
WHERE table2.ID IS NULL.


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

Explanation of multiple-column indexes

[Jesse Sheidlower]

After some discussion in a separate thread, I've been trying
to get a better understanding of the workings of multiple-column
indexes, and think I'm still missing the point. I understand
indexing (last_name, first_name) in that order if you'd always
be searching last names and only be searching first names in
combination.

What I'm trying to understand is how you would set up these
indexes when you'd always be doing joins with another table.
Suppose you have The Canonical CD Database, and you have a
table "songs" with fields "song_id", "album_id", "song_title",
and "song_length". Suppose you're often doing searches of
song_title or (for some reason) song_length, and that any time
you'd do such a search, you'd _always_ be joining it to the 
"album" table.

It would seem that you'd want at least two multiple-indexes in
the "song" table, one of them including "song_title" and
"album_id", the other including "song_length" and "album_id".
Is this correct? Do you need "song_id" (which would be a
primary key on that table) in there too? What order should
the indexes be in?

If every search for song_title or song_length must be joined
against the album table, it's not clear which should be the
first named column in this index. The experiments I've done
so far have been inconclusive, and I don't think I'm understanding
the process in the first place.

Thanks very much.

Jesse Sheidlower


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

Re: Version 3.x tables with a new 4.0.x engine

[Egor Egorov]

Yrj? M?enp?? <[EMAIL PROTECTED]> wrote:
> 
> Can I expect newly upgraded MySQL 4.0.1x engine to
> read my old version 3.x tables without trouble, so I can
> optimize them after I have installed the the new engine.

Yes.
You should make database backup before upgrading in any case.

> 
> I am using MyISAM tables in both systems.



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




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

Re: own privileges

[Nils Valentin]

SHOW GRANTS FOR [EMAIL PROTECTED];

It is important that the mix of username and hostname is the one which is 
specified in the privilege database.

Please see the manual for details.

 show grants for [EMAIL PROTECTED];
++
| Grants for [EMAIL PROTECTED] 
 
|
++
| GRANT ALL PRIVILEGES ON *.* TO 'root 
  
|
++
1 row in set (0.09 sec)


Best regards

Nils Valentin
Tokyo/Japan


2003年 8月 25日 月曜日 21:[EMAIL PROTECTED] さんは書きました:
> Is there a way for a user to get knowledge about his own privileges?
> For example can a user know if he/she has rights to INSERT INTO some table?
> I'm using PHP4

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


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

Re: utf8 support

[Egor Egorov]

"Li, John" <[EMAIL PROTECTED]> wrote:
> 
> MySql 4.1 should have utf8 charset support, I downloaded the alpha version
> but don't seem to have the utf8.xml file comes with it.
> 
> Any one know how to get the needed charset support files?

utf8 is a built in character set.



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




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

RE: own privileges

[Jay Blanchard]

[snip]
Is there a way for a user to get knowledge about his own privileges?
For example can a user know if he/she has rights to INSERT INTO some
table?
I'm using PHP4
[/snip]

Of course! If they have access to a query on the mysql.user table they
can see their permissions.

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

Re: how to show all locks on a table?

[Egor Egorov]

Bennett Haselton <[EMAIL PROTECTED]> wrote:
> 
> I found a way to do this before, but I didn't write down how I did it, so I 
> don't remember it now.  And I've searched http://www.mysql.com/doc/ in 
> vain.
> 
> What's the command to show all current locks on a database table?
> 

There is no command to show locks on the table.



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

own privileges

[P.Gertman]

Is there a way for a user to get knowledge about his own privileges?
For example can a user know if he/she has rights to INSERT INTO some table?
I'm using PHP4

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

Re: user types

[Ge?????d?? G??????]

- Original Message - 
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: ???, 25 ? 2003 2:58 ??
Subject: Re: user types


> 1.) > [EMAIL PROTECTED]
> 2.) > [EMAIL PROTECTED]
> 3.) > [EMAIL PROTECTED]
>
> No. 1 means user "root" at ALL servers,
> No. 2 means user "root" at server localhost (127.0.0.1) via TCP/IP,
actually [EMAIL PROTECTED] means user "root" at server localhot but not via
TCP/IP but through the mysql.sock socket
> No. 3 means user "root" at server www,
> and so on,. Is that clear to you?
>
> HTH,
>
> Jakob
>
> -- 
> COMPUTERBILD 15/03: Premium-e-mail-Dienste im Test
> --
> 1. GMX TopMail - Platz 1 und Testsieger!
> 2. GMX ProMail - Platz 2 und Preis-Qualitätssieger!
> 3. Arcor - 4. web.de - 5. T-Online - 6. freenet.de - 7. daybyday - 8.
e-Post
>
>
> -- 
> 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: mysqlbinlog question

[Victoria Reznichenko]

"Nils Valentin" <[EMAIL PROTECTED]> wrote:
> Hi Victoria,
> 
> 2003? 8? 21? ??? 20:20?Victoria Reznichenko :
>> "Nils Valentin" <[EMAIL PROTECTED]> wrote:
>> > 2003? 8? 21? ??? 16:09?Victoria Reznichenko :
>> >> "Nils Valentin" <[EMAIL PROTECTED]> wrote:
>> >> > This time I am using 4.0.13-max (tar format from www.mysql.com) on
>> >> > Redhat 9.
>> >> >
>> >> > When I issue the command mysqlbinlog it does not show me the whole
>> >> > binary logfile but just the first line or so (below).
>> >> >
>> >> >  mysqlbinlog baby-bumble-bee-bin.09
>> >> > # at 4
>> >> > #030814 14:38:17 server id  1   Start: binlog v 4, server v  created
>> >> > 700101  9:00:00
>> >> >
>> >> > I understood that I can use the tool to view the whole binary log
>> >> > data.
>> >>
>> >> Yes.
>> >>
>> >> > Am I mistaken ?
>> >>
>> >> Probably your version of mysqlbinlog can't read binary logs 4.0.x MySQL
>> >> server.
>> >
>> > Thank you for the reply. I just had a chance to double check on a Suse
>> > Linux 8.1 with mysql 4.1-alpha (rpm format) and it works there.
>> >
>> > H. so must be a bug ??
>>
>> Nils, what is the version of mysqlbinlog and what is the version of MySQL
>> server, that produces binary logs?
> 
> Thank you for asking the right questions ;-).
> 
> the mysqlbinlog version 1.6 came with Mysql -4.0.13 (tar format from 
> www.mysql.com) - used on RH9


Hmm.. strange .. I have mysqlbinlog v2.6 in my 4.0.13 binary distribution.

> the mysqlbinlog version 2.3 came with MySQL-4.1-alpha (rpm format from 
> www.mysql.com) - used on Suse 8.1
> 
> After a long follow up I reallized that I must have used the mysqlbinlog 
> version 1.6 on the file produced from the mysqbinlog version 2.3.

mysqlbinlog v1.6 can read binary log files produced 3.23 MySQL server.

> 
> However looking inside of the binary logfile itself I found this in the very 
> first line now:
> 
> #030814 14:38:17 server id 1  log_pos 4 Start: binlog v 3, server v 
> 4.1.0-alpha-Max-log created 030814 14:38:17
> # at 79
> 
> 
> It is saying "...binlog v 3... " does that signify the binlog protocol version 
> ?

It's a version of binary log format.

> 
> If yes, is there a command line option to inquire this info ?
> 


-- 
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: user types

[Sebastian Haag]

Andrew,

Andrew Simpson said:
> [EMAIL PROTECTED]
> [EMAIL PROTECTED]
> [EMAIL PROTECTED]
>
>
> can anyone tell me the difference between the above 3. These are for the
> root user.
>
> i need to create a newuser and im not sure which of the above they are
> supposed to have
>
> thanks
>

Your question is a bit confusing.

I suggest you take a look at the user's manual.
GRANT is what you want to do.

http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html#User_Account_Management

-- 

Once a problem is defined - it is half way solved. (Henry Ford)

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

Re: user types

[alligott]

1.) > [EMAIL PROTECTED]
2.) > [EMAIL PROTECTED]
3.) > [EMAIL PROTECTED]

No. 1 means user "root" at ALL servers, 
No. 2 means user "root" at server localhost (127.0.0.1) via TCP/IP,
No. 3 means user "root" at server www,
and so on,. Is that clear to you?

HTH,

Jakob

-- 
COMPUTERBILD 15/03: Premium-e-mail-Dienste im Test
--
1. GMX TopMail - Platz 1 und Testsieger!
2. GMX ProMail - Platz 2 und Preis-Qualitätssieger!
3. Arcor - 4. web.de - 5. T-Online - 6. freenet.de - 7. daybyday - 8. e-Post


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

Fw: PHP or Perl? (or Rebol via TCP/IP)

[D De Villiers \(Work- AKURA\)]

Hello Tim,

 Do've an example on using Rebol to connect to mySQL via TCP/IP?

How do you do it? How do you execute SQL quieries? Return results etc ?
Do you use some kind of "middleware" that your communicating to from Rebol
via TCP/IP which gets the data for you?

I'm actually a Delphi, Java, VB, Perl & Ruby developer (done alittle Rebol
before) but found it strange that your using TCP/IP to communicate with
mySQL.

Kind Regards,

Lennie De Villiers

(nRs - Please don't remove this when replying!)



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

Re: Moving to version 4.1

[Andreas]

Robert Mark Bram wrote:

and Clients 3.23.55" and then in stall 4.1, how will my "MySQL ODBC 3.51
Driver" driver know to connect to 4.1 now? Is there anything else I 
need to do?
If you also dumped and removed the database files then you probaply have 
to reinstall the 3.23.xx or an 4.0.xx to create user accounts with the 
old less secure password storage format.

Then you can install the 4.1 and let it use the old data files.
Read the docu about upgrading to 4.1.
With a bit of brain work you should even be able to have both server 
versions running on your box to do some serious playing around.
(NOT ON THE SAME DATA DIRECTORY)

Daniel Kasak wrote:

 From what I gather, you can't use MySQL-4.1 with MyODBC-3.51. You get 
an error message telling you that the authentication method requested by 
the server is not available, and to upgrade your client libraries (or 
something like that).
There is a parameter for mysql 4.1 that allows keeping the old password 
format for existing user accounts.

In /etc/my.cnf   or   c:\winnt\my.ini

[mysqld]
old-passwords = 1
This will be necessary for some time as long as you use mostly 
precompiled client apps on the server like PHP and what not...

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

user types

[Andrew Simpson]

[EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]


can anyone tell me the difference between the above 3. These are for the root user.

i need to create a newuser and im not sure which of the above they are supposed to have

thanks

Re: Union and Order By give strange results in Mysql 4.0.13

[Hans van Harten]

Neculai Macarie wrote:
>> To my observations constants in a column declaration limit the
>> column width to just fit the initial constant.
>> Your choice of values 'gallary' and 'gallery-categ' just masked that
>> out ;-)
> Yes, you are right. Based on your observation I was able to trick him
> with this query:
> select Trim( 'gallery' )
> as table_name, d_image_small, d_image_big
> from gallery
> UNION
> select 'gallery_categ' as table_name, d_image, NULL
> from gallery_categ
>  order by table_name;
Well, it works for you, but preserving the original length don't feel
logical.
I fear this might change without notice.

Come to think of "select  'gallery   '  as table_name, .."
Yes, MySQL silently trims the trailing spaces for you!!

HansH
-- 
MySQL 4.1.0-alpha-max-nt on Windows XP using MySLQcc 0.9.2.B


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

Re: Lots of FULLTEXT stuff (suggestions)

[Santino]

My TODO List:

1. Speed
More speed in inserts, deletes and selects.
2. Stoplist
1 stoplist for each index
create fulltext index on x( y) stoplist 'mystop'
the stoplist could be divided in section like my.cfg:
[STOPWORD]

[VARIABLES]
ft_min_word_len=3

[CLASSES]
...
[SPECIAL CHARS]
...
The classes section defines word boundary; assigning chars or group
of chars to one class (Alphanumeric, Word Break, punctuation, etc.).
The parser uses this section to extract words to index.
SPECIAL CHARS = replacement of some character ( ASCII > 128) to an
equivalent 7bit character ( eg é=e --> index resumé as resume).
3. Proximity

4. TAGs
Recognize tags to stop/restart index: inside the text I can insert
tags to skip indexing a part of text (stop index, start index).
5. UDF or Filter
Cascade UDF. The output of one function can be sent to input of
another function.
Every record can have a different filter.
This solution allows to handle external documents( Text, Word, Excel,
XML, PDF, etc) without storing their data in a table (just store path
& type) crypt them (usefull in CD-ROM distribution).
This filter can also fill other columns with data from the text.
These features with no great speed degrade can improve MySql and open
new opportunity to MySql.
Santino

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

Re: Union and Order By give strange results in Mysql 4.0.13

[Neculai Macarie]

> Neculai Macarie wrote:
> []
> > select 'gallery' as table_name, d_image_small, d_image_big
> > from gallery
> > UNION
> > select 'gallery_categ' as table_name, d_image, NULL
> > from gallery_categ
> > order by table_name;
> []
> > select 'gallery' as table_name, d_image_small, d_image_big
> > from gallery
> > union
> > select 'categ' as table_name, d_image, NULL
> > from gallery_categ
> > order by table_name;
> Making a twist to the samples provided
> select 'categ' as table_name, d_image, '.' as big
> from gallery_categ
> union
> select 'gallery' as table_name, d_image_small , d_image_big
> from gallery
> order by table_name;
> the results are amazing
> 'table_name','d_image','big'
> 'categ','img22.jpg','.'
> 'categ','img23.jpg','.'
> 'categ','img21.jpg','.'
> 'galle','img3.jpg','i'
> 'galle','img6.jpg','i'
> 'galle','img1.jpg','i'
>
>
> To my observations constants in a column declaration limit the column
width
> to just fit the initial constant.
> Your choice of values 'gallary' and 'gallery-categ' just masked that out
;-)

Yes, you are right. Based on your observation I was able to trick him with
this query:

select Trim( 'gallery' ) as
table_name, d_image_small, d_image_big
from gallery
UNION
select 'gallery_categ' as table_name, d_image, NULL
from gallery_categ
 order by table_name;





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

Re: newbie > mysql table size?

[Antony Dovgal]

On Mon, 25 Aug 2003 15:17:25 +0700
Alf Koswara <[EMAIL PROTECTED]> wrote:

> Hi, 
> i read the mysql manual and it's explained the maximum table size in many 
> operating system, but i can't find the maximum size for MS window (win32), 
> what's the maximum table size for win32?
> 
> My office want to migrate the database server to mysql, and it's contains a 
> millions record. Can mysql hold up tables with each have millions record?
> 
> Thanks,
> Alfha K.

You can have up to 4 billion rows in one MyISAM table.
Table size is limited only by your filesystem (as you understand, limits of your 
filesystem is not the topic to be discussed on this list). 
In case of win32, I suppose, the best place to search info about filesystem limits 
would be microsoft.com.

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

Version 3.x tables with a new 4.0.x engine

[Yrjö Mäenpää]

Hi

Can I expect newly upgraded MySQL 4.0.1x engine to
read my old version 3.x tables without trouble, so I can
optimize them after I have installed the the new engine.

I am using MyISAM tables in both systems.

thanks


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

newbie > mysql table size?

[Alf Koswara]

Hi, 
i read the mysql manual and it's explained the maximum table size in many 
operating system, but i can't find the maximum size for MS window (win32), 
what's the maximum table size for win32?

My office want to migrate the database server to mysql, and it's contains a 
millions record. Can mysql hold up tables with each have millions record?

Thanks,
Alfha K.


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

Re: utf8 support

[Jon Haugsand]

* John Li
> MySql 4.1 should have utf8 charset support, I downloaded the alpha version
> but don't seem to have the utf8.xml file comes with it.
>  
> Any one know how to get the needed charset support files?

It looks like a bug.  Try version 4.1.1.  See e.g.:



-- 
 Jon Haugsand, [EMAIL PROTECTED]
 http://www.norges-bank.no


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

Re: Lots of FULLTEXT stuff (suggestions)

[Matt W]

Hi Steven,

Thanks for replying. Your posts that I've found when searching for
FULLTEXT information have had great ideas. :-) Searching millions of
posts efficiently and effectively isn't easy. :-( Heh.


Thinking about ft_min_word_len some more (and how I wish they'd lower
the default), I don't really see that much use/purpose for a minimum
word length. Most 1-3 letter words that you don't want indexed should be
stopwords anyway, right? So why NOT index the ones that are left?
Doesn't seem like it'd make the index much larger to me. BTW, what is
your min_word_len value?

- Original Message -
From: "Steven Roussey"
Sent: Sunday, August 24, 2003 3:39 PM
Subject: RE: Lots of FULLTEXT stuff (suggestions)

> > And the FULLTEXT index shouldn't always be chosen
> > for non-const join types when another index would find less rows
> first.
>
> The short answer is that it doesn't work that way (also, I think this
is
> why there are no composite indexes between integer and fulltext
> indexes). The two systems don't know anything about each other.

Yeah...


> > Also, are the current MySQL versions using the "2 level" full-text
> > index format yet? I'm thinking not?
>
> No. MySQL 4.1.0 has some low-level support for this, but FTS needs to
> altered (quite a bit I'd guess) to use it. So there is hope that it
will
> come in the 4.1.x line, but no guarantee.

I'm hoping for 4.1.x!

BTW, I wonder if Sergei is the [only] one who handles all the full-text
coding?


> > In November 2001, he said the new .frm format would be here "this
> > year." It's been almost 2 years since then, so when is it do? ;-/
>
> I think it was pushed back to version 5.1. I'd figure another two
years.

Aww. :-( Yeah, now I remember seeing that in "Features planned for 5.1."

Since the acquisition of SAP DB, I think they're supposed to have more
resources for faster development. Maybe some of these things will come
sooner than expected. That's what I'm hoping anyway.

Matt


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

Re: Re: Approved

[closed]

Due to a dramatic increase in the volume of spam and virus-related messages received 
at this email address, this address has been disabled.  Please resend your inquiry to 
the new address:

[EMAIL PROTECTED]

We apologize for any inconvenience this may have caused.

High-Logic




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

REPLICATION PROBLEM ON 4.0.14 (see prevoius e-mail)

[Massimo Petrini]

When the 4.0.15 version will be avalaible ?  The problem fo replication make
impossible to install the .14 version and I came back to .13 release.

Best regards
Massimo


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

Re: How many records can a single MySql Table Hold.

[Antony Dovgal]

On Mon, 25 Aug 2003 10:05:32 +0530 (IST)
"Rupak Banerjee" <[EMAIL PROTECTED]> wrote:

> 
> Hi,
>   I have a mysql database running on Mysql version 3.23.53 on a Red Hat
> Linux 7.2. In the database there is a single table with over 150,000
> records. Unfortunately, the performance of that table is very poor,
> comparing to other tables in the same database.
>   Can somebody help me out.
> Thanks,
> Rupak Banerjee.
read about query optimization and table indexes.
use EXPLAIN SELECT .. to see what happends with your query.

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

Re: differance between InnoDB and MyISAM tables ?

[sanjay gupta]

Hi  Nils Valentin,
Thanks for the sincere efforts and valuable tips

sanjay gupta
- Original Message -
From: "Nils Valentin" <[EMAIL PROTECTED]>
To: "sanjay gupta" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Sunday, August 24, 2003 6:31 PM
Subject: Re: differance between InnoDB and MyISAM tables ?


> Hi Sanjay,
>
> I made a chart which shows the tables and their supported functions.
>
> Please have a look here:
>
> http://www.knowd.co.jp/staff/nils/
>
> I hope this helps.
>
> Best regards
>
> Nils Valentin
> Tokyo/Japan
>
>
> 2003年 8月 22日 金曜日 18:45、sanjay gupta さんは書きました:
> > Hi all ,
> >
> > can anybody tell me the differance between the innidb and myisam tables
> > types .
> >
> > sanjay
>
> --
> ---
> Valentin Nils
> Internet Technology
>
>  E-Mail: [EMAIL PROTECTED]
>  URL: http://www.knowd.co.jp
>  Personal URL: http://www.knowd.co.jp/staff/nils
>
>


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

How many records can a single MySql Table Hold.

[Rupak Banerjee]

Hi,
  I have a mysql database running on Mysql version 3.23.53 on a Red Hat
Linux 7.2. In the database there is a single table with over 150,000
records. Unfortunately, the performance of that table is very poor,
comparing to other tables in the same database.
  Can somebody help me out.
Thanks,
Rupak Banerjee.

-- 
Visit http://www.brandemataram.com
Login To The Future




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

Re: How many records can a single MySql Table Hold.

[Rupak Banerjee]

Hi Jeremy,


Thankx for the solution..but can you please tell
me how to increase the key buffer fo mysql ??

Its urgent...we are facing a lot of problems.

Thankx once again for the solution.

> On Fri, Aug 22, 2003 at 10:55:49AM +0530, Rupak Banerjee wrote:
>> Hi,
>>
>>   We are using MySql version 3.27.53 on a Red Hat Linux platform
>> version
>> 7.2. For the past couple of months we are noticing that the
>> performance of the server has gone down very badly. Every, single
>> insertion is taking a hell lot of time.The particular table has only
>> 150,000 records.
>>  Is there any remedy for this problem.
>
> Perhpas you need a larger key buffer because your indexes are too
> large to fit in memory?
>
> Jeremy
> --
> Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
> <[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
>
> MySQL 4.0.13: up 20 days, processed 1,000,169,580 queries (554/sec. avg)


-- 
Visit http://www.brandemataram.com
Login To The Future




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

Re: Replication from 2 Master

[Jeremy Zawodny]

On Sun, Aug 24, 2003 at 02:45:01PM -0400, Eric Frazier wrote:
> Hi,
> 
> That makes a lot of sense, in fact we are using two servers as
> "Masters" replicating to each other in a circle, but one has an
> extra slave which is in our office. So in effect that does what this
> guy was looking for, without doing anything weird and strange.

Well, maybe.  One problem with doing that is the that one of the two
masters depends on the other for replication data to reach the slave.
It's not clear whether or not that's an option in his case.

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

MySQL 4.0.13: up 23 days, processed 1,075,421,522 queries (533/sec. avg)

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

Re: Upgrading 3.23->4.0 under WinXP ?

[Matt W]

- Original Message -
From: "Thomas Andersson"
Sent: Saturday, August 23, 2003 8:15 AM
Subject: Upgrading 3.23->4.0 under WinXP ?


> Hi!
>
> I'm pretty green at this and I'm a bit clueless here, After
reinstalling my
> system I upgraded from 3.23 to 4.0 of MySQL, then I moved my old dbs
in
> there and most seems to work OK. Now PHPMyAdmin tells me my userbase
is out
> of date and that I need to run mysql_fix_privilege_tables...
>
> HOW do I run this on my WinXP system? What else do I need to do?
>
> Is it DOs, should it run from within SQl?

mysql_fix_privilege_tables is a shell script for *nix. You can open it
in a text editor and see the ALTER and UPDATE queries that it would run.
I will post below the ones that I think are needed to upgrade the system
tables from 3.23 -> 4.0. BTW, if you haven't modified the default
permissions or grants on anything, you can just drop the "mysql"
database (delete it from the data directory after stopping MySQL), and
then just reinstall MySQL 4.0 and it will put the correct default tables
there. Again, don't do this if you've changed the default MySQL
permissions (or you don't want to recreate them :-)).

Anyway, here's the queries from mysql_fix_privilege_tables that I think
you need to run on the mysql database (as the root user; and you ignore
any duplicate column errors, etc.):

USE mysql;
alter table user change password password char(16) NOT NULL;
alter table user add File_priv enum('N','Y') NOT NULL;
alter table user add Grant_priv enum('N','Y') NOT NULL,add
References_priv enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT
NULL,add Alter_priv enum('N','Y') NOT NULL;
alter table host add Grant_priv enum('N','Y') NOT NULL,add
References_priv enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT
NULL,add Alter_priv enum('N','Y') NOT NULL;
alter table db add Grant_priv enum('N','Y') NOT NULL,add References_priv
enum('N','Y') NOT NULL,add Index_priv enum('N','Y') NOT NULL,add
Alter_priv enum('N','Y') NOT NULL;
UPDATE user SET
Grant_priv=File_priv,References_priv=Create_priv,Index_priv=Create_priv,
Alter_priv=Create_priv;
UPDATE db SET
References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_pri
v;
UPDATE host SET
References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_pri
v;
ALTER TABLE user
ADD ssl_type enum('','ANY','X509', 'SPECIFIED') NOT NULL,
ADD ssl_cipher BLOB NOT NULL,
ADD x509_issuer BLOB NOT NULL,
ADD x509_subject BLOB NOT NULL;
alter table user
add Show_db_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER alter_priv,
add Super_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER Show_db_priv,
add Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Super_priv,
add Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Create_tmp_table_priv,
add Execute_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Lock_tables_priv,
add Repl_slave_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Execute_priv,
add Repl_client_priv enum('N','Y') DEFAULT 'N' NOT NULL AFTER
Repl_slave_priv;
update user set show_db_priv= select_priv, super_priv=process_priv,
execute_priv=process_priv, create_tmp_table_priv='Y',
Lock_tables_priv='Y', Repl_slave_priv=file_priv,
Repl_client_priv=file_priv where user<>"";
alter table user
add max_questions int(11) NOT NULL AFTER x509_subject,
add max_updates   int(11) unsigned NOT NULL AFTER max_questions,
add max_connections int(11) unsigned NOT NULL AFTER max_updates;
alter table db
add Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,
add Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL;
alter table host
add Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,
add Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL;
FLUSH PRIVILEGES;


> (Everything seems to work except my guestbooks after the reinstall).

Not sure about that not working. :-/

Matt


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

Re: Replication from 2 Master

[Eric Frazier]

Hi,

That makes a lot of sense, in fact we are using two servers as "Masters"
replicating to each other in a circle, but one has an extra slave which is
in our office. So in effect that does what this guy was looking for, without
doing anything weird and strange. 


Thanks,

Eric 


At 02:42 PM 8/24/03 -0700, Jeremy Zawodny wrote:
>On Sun, Aug 24, 2003 at 02:02:06PM -0400, Eric Frazier wrote:
>> Sounds very biblical. :) 
>
>Yeah, I have it etched on a pair of stone tablets around here
>somewhere... :-)
>
>> Wouldn't there be a way to do this with two copies of mysql that
>> share a common data dir?
>
>Maybe.  But that's not what he's asking about.  It's a bit tricky to
>do correctly, doesn't work in all cases, and is often more trouble
>that it's worth.
>
>> I don't know if you could do that with InnoDB, but I wonder if you
>> could with myisam?
>
>You can do it with MyISAM, but not InnoDB or BDB.
>
>> At least if you had a system where the two sets of tables came from
>> a different master, and there was no overlap, or if the slave was
>> just functioning as a backup, maybe it would be possible and not
>> lead to too much horror?
>
>Wel, that's the trick.  What I've found is that in order to understand
>the possible "horrors", you end up having to bump into numerous
>problems along the way.  In the end you realize that it probably would
>have been better to look at the problem a bit different, such as
>"chaining" together the two masters, or running completely separate
>instances of MySQL on the slave machine rather than trying to mix and
>match the data.
>
>I guess that what it comes down to is this.  MySQL's replication was
>designed for relatively simple master/slave setups with 1 master and 1
>or more slaves.  By taking advantage of the simplicity of MyISAM
>tables and really knowing how replication works, MySQL does locking,
>and so on... you can often use it in ways that were not intended.
>
>The problem with doing so is that you *are* using it ways that were
>not intended.  That may cause strange problems down the line.
>
>Now I've done more than my fair share of abusing MySQL in strange
>configurations.  Some have worked quite well and others have not.  I'm
>not saying "don't do this" but it's not something to simply dive into
>either.
>
>Jeremy
>-- 
>Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
><[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
>
>MySQL 4.0.13: up 23 days, processed 1,073,046,344 queries (536/sec. avg)
>

(250) 655 - 9513 (PST Time Zone)

"Inquiry is fatal to certainty." -- Will Durant 





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