Re: Problem with 2GB limit.

[Dan Nelson]

In the last episode (Apr 13), Cesar Bonavides Martinez said:
> I'm working with Solaris 8, and MySQL 4.0.17-standard.
> 
> I was trying to upload data into a single table database and when it
> reached 2GB it stopped uploading sending the error message:
> 
> ERROR 1030 at line 2450: Got error 27 from table handler
> 
> After more than 4 hours digging into the FAQs and some of the digests
> of the mailing list, and a real headacke, I come to ask for help.
> 
> I can say that this is not a problem of OS limits, since the file
> containing the SQL commands is more than 3GB.
> 
> When I get into the folder of my database (named superfamily), I see that
> the file *.MYD is exactly 2GB (results in bytes):
> 
> -rw-rw   1 mysqlmysql2147483647 Apr 13 22:26 align.MYD

Two things to check.  First run 

  SHOW VARIABLES LIKE "large_files_support";

and verify that it's set to ON.  If it's OFF, then for some reason your
mysqld wasn't compiled with large file support.  If it's ON, run

  SHOW TABLE STATUS LIKE "align";

and check the Max_data_length column.  If it's 2147483647, then you
simply have to let MySQL know that your table needs to be larger than
2gb.  I usually see 4294967295 as a limit here, though, not 2147483647.
To raise the max filesize, run

  ALTER TABLE align AVG_ROW_LENGTH= MAX_ROWS=

and use reasonable guesses for each.  You can use the current average
rowlength value from the previous SHOW TABLE STATUS command.  It's not
a hard limit, so it's ok if you guess too low for MAX_ROWS, as long as
MAX_ROWS*AVG_ROW_LENGTH is larger than 2gb.

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Average of every X number of rows?

[Peter Janett]

I searched all over and can't seem to find out how to take an average of
every x number of rows from a result set.

I have a table with over 700,000 rows, and I want to display an x and y
graph of that data on the web.  What is happening now is I'm creating 2
arrays, one for the x axis and one for the y axis of the graph, each with ~
700,000 elements.  All that data is used to plot a 600 pixel wide graph, so
in the process of creating the web images, most of the detail is lost, and
it takes ~ 30 seconds to render.  (I'm using jpgraph in PHP, and have
confirmed that the majority of the time needed to render is creating the
image, not populating the large arrays.)

So, I figure rather than plot all that detail, only to have most of it
thrown out when the image is created, I need to throw out the data before
the image is created, in hopes that doing so will greatly decrease the
amount of time needed to render the graph.

So, what I need to do is get the average of a column ever x rows.

This is as far as I got:
SELECT AVG(value) AS every_ten_row_average FROM table GROUP BY [every 10
rows]

The part I'm stuck on is the [every 10 rows].

I think I could use a limit statement, and loop the query over and over,
each time adjusting the limit values by 10, but that would mean a whole lot
of queries. :)

My guess is that I need to create a temp table or a virtual column based on
current row, and use that as my group by.

Any help GREATLY appreciated.

If anyone has solutions for creating web image graphs from MySQL that would
be faster than using the php based jpgraph setup, I'd love to hear about
them as well.

Thank you!

Peter Janett

New Media One Web Services, LLC
http://www.newmediaone.net
(303)828-9882




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

Error: 5 - Out of memory (Needed 2298807288 bytes)

[Terence]

Hi All,

I am trying to perform an update:

UPDATE helpdesk_tickets ht, helpdesk_status_master hsm
SET ht.status_id = (SELECT status_id FROM helpdesk_status_master WHERE
is_closed = 'y')
WHERE ht.submit_date < DATE_ADD(curdate(), INTERVAL -7 day)
AND ht.status_id = hsm.status_id
AND hsm.is_closed = 'y'
AND hsm.final_closed = 'n'

When I get the above error. Is the above query allowed? Has it perhaps been
fixed?

This is the sequence of events
1) Ran the above query
2) The server shutdown
3) Started the server again
4) I get the out of memory error

Running Mysql 4.1
1Gig Ram (2 Gig SWAP)
2X 2.4GhZ Xeon
RH9

Here's the log:

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=402653184
read_buffer_size=2093056
sort_buffer_size=2097144
max_used_connections=72
max_connections=150
threads_connected=22
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections =
1007014 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x5fbf7ea0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfabeed8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x807e89b
0x829e7f8
0x80a0d7c
0x80a111a
0x804c3bc
0x80a1565
0x80a547e
0x806c970
0x806abc8
0x80a1565
0x80bbadc
0x80a5914
0x80a7b62
0x80bb95f
0x808b3a2
0x808dd99
0x8088c91
0x808847d
0x8087c39
0x829bfac
0x82d187a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x88b9cc8 = UPDATE helpdesk_tickets ht, helpdesk_status_master
hsm
SET ht.status_id = (SELECT status_id FROM helpdesk_status_master WHERE
is_closed = 'y')
WHERE ht.submit_date < DATE_ADD(curdate(), INTERVAL -7 day)
AND ht.status_id = hsm.status_id
AND hsm.is_closed = 'y'
AND hsm.final_closed = 'n'
thd->thread_id=237423

Successfully dumped variables, if you ran with --log, take a look at the
details of what thread 237423 did to cause the crash.  In some cases of
really
bad corruption, the values shown above may be invalid.

The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
040414 13:34:27  mysqld restarted
040414 13:34:27  Can't start server: Bind on TCP/IP port: Address already in
use
040414 13:34:27  Do you already have another mysqld server running on port:
3306 ?
040414 13:34:27  Aborting

040414 13:34:27  /usr/local/mysql/bin/mysqld: Shutdown Complete

040414 13:34:27  mysqld ended

040414 13:35:13  mysqld started
040414 13:35:14  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 157493553
InnoDB: Doing recovery: scanned up to log sequence number 0 157493553
040414 13:35:14  InnoDB: Flushing modified pages from the buffer pool...
040414 13:35:14  InnoDB: Started
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.1.0-alpha-standard-log'  socket: '/tmp/mysql.sock'  port: 
040414 13:36:07  Out of memory;  Check if mysqld or some other process uses
all available memory. If not you may have to use 'ulimit' to allow mysqld to
use more memory or you can add more swap space

Any help is appreciated.
Thanks
Terence


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

Re: Multiple SELECTs in one query

[Michael Stassen]

A * by itself must come first, so

  SELECT *, "A" AS SortCode FROM Jobs

will work.

[EMAIL PROTECTED] wrote:

I am not sure about MySQL but in Oracle this will NOT work:  "SELECT "A" AS SortCode,  
* FROM Jobs"
However, this WILL:  "SELECT "A" AS SortCode, 
Jobs.* FROM Jobs"
Try adding the table or alias in front of the "*".
This works in MySQL, as well.

In general, however, I will repeat my suggestion from before - try to do
everything in one pass - it's much more efficient.
Something like this:

"SELECT (CASE 
  WHEN Jobs.Project =  MyProject AND Jobs.JobType =  MyJobType  then "A"
  WHEN Jobs.Project => MyProject AND Jobs.JobType <> MyJobType  then "B"
  WHEN Jobs.Project <> MyProject AND Jobs.JobType =  MyJobType  then "C"
  WHEN Jobs.Project <> MyProject AND Jobs.JobType <> MyJobType  then "D"
 END CASE
)  SortCode,
Jobs.*
ORDER BY SortCode  ASC,
 Jobs.Priority ASC


You might have to tinker with the syntax if CASE is not available in
MySQL to this extent but that's the general idea.
MySQL has CASE, with almost the same syntax you describe, except it ends 
with END rather than END CASE.  See 
.

Michael



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

Problem with 2GB limit.

[Cesar Bonavides Martinez]

Hi,

I'm working with Solaris 8, and MySQL 4.0.17-standard.

I was trying to upload data into a single table database and when it reached
2GB it stopped uploading sending the error message:

ERROR 1030 at line 2450: Got error 27 from table handler

After more than 4 hours digging into the FAQs and some of the digests of the
mailing list, and a real headacke, I come to ask for help.

I can say that this is not a problem of OS limits, since the file containing
the SQL commands is more than 3GB.

When I get into the folder of my database (named superfamily), I see that
the file *.MYD is exactly 2GB (results in bytes):

-rw-rw   1 mysqlmysql2147483647 Apr 13 22:26 align.MYD
-rw-rw   1 mysqlmysql   1024 Apr 13 22:26 align.MYI
-rw-rw   1 mysqlmysql   8616 Apr 13 22:19 align.frm

Then after all what I read, I think this is useful to know that my "ibdata1"
file is not too big (results in bytes):

-rw-rw   1 mysqlmysql10485760 Apr 13 21:09 ibdata1


Also, that my innodb was created as default:

innodb_data_file_path   ibdata1:10M:autoextend




And that none of the logfiles is greater than 6MB (results in bytes):

-rw-rw   1 mysqlmysql  25088 Jan 29 21:33 ib_arch_log_00
-rw-rw   1 mysqlmysql5242880 Apr 13 21:25 ib_logfile0
-rw-rw   1 mysqlmysql5242880 Jan 29 21:33 ib_logfile1



And if you ask me to send you anything else that would help you to help me,
I will send it of course.  I am new to MySQL, so please if you ask me to run
any command please give me a hint (or better the command itself) so I can
run (the exact way) whatever you think would help.

I will appreciate any help/advice, I'm kind of disappointed, I know there is
someone that had the same problem or that know how to solve it.

Regards, and thank you guys in advance.

César
Nitrogen Fixation Research Center.
RegulonDB staff.




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

Fun Query with Question

[J.R. Bullington]

Greetings!

I have a query that I would like to refine a little more. I am using this to
send email to a directory with so many users in it that it causes my SMTP
server to clog up. I want to break up the database send module so that it
sends to only last names with the letters A - K first, do a pause, and then
send the email to letters L - Z. Any help would be appreciated!

Here's the original query (please ignore the session("MemberType"), as it is
a session variable chosen from a screen prior):

select last, email from tblMembers where email is not null and MemberDesc "
& session("MemberType")


Thank you for your help in advance!

J.R.

Data typing calculation results in SELECT?

[David L. Van Brunt, Ph.D.]

I'm doing a select where I lag across records, and would like to compute
some differences. It seems to do the calcs right if I evaluate the result in
an IF statement, but if I just want to get the calculation result stored, it
seems to default to a data type that only stores on digit. Here's the
offending code:

create table rfdata
SELECT 
t1.*,
dayname( t1.tradedate )  AS tradedayofweek,
dayofmonth( t1.tradedate )  AS trademonthday,
dayofyear( t1.tradedate )  AS tradedoy,
monthname( t1.tradedate )  AS trademonth,
((t2.currentPrice - t1.currentPrice) / t1.currentPrice) AS d1closechange,
((t2.dayMinPrice - t1.currentPrice) / t1.currentPrice) AS d1lowchange,
((t2.dayMaxPrice - t1.currentPrice) / t1.currentPrice) AS d1highchange,
if(((t2.dayMaxPrice-t1.currentPrice)/t1.currentPrice)>.03,1,0) AS
d1threepcthit,
((t3.open - t1.currentPrice) / t1.currentPrice) AS d2openchange,
if(((t3.open-t1.currentPrice)/t1.currentPrice)>.02,1,0) AS d2twopct
FROM sorted_data AS t1
LEFT  JOIN sorted_data AS t2 ON t2.newid = t1.newid +1 AND t2.symbol =
t1.symbol
LEFT  JOIN sorted_data AS t3 ON t3.newid = t1.newid +2 AND t3.symbol =
t1.symbol;

This give me a able with the following offending results:
+--+---+
| d1highchange | d1threepcthit |
+--+---+
|  0.0 | 0 |
|  0.0 | 1 |
|  0.0 | 0 |

Where d1highchange should have  been a decimal that was over .03, given the
"1" in the second column. If I look at the 1st column in phpMyAdmin, it
appears to be type "Double", with "25,1" in the defaults/format display.

Any way I can rewrite my table creation code to be sure that value gets
stored out several decimal places?

Thanks, I looked in the manual but didn't see anything about column typing
in a CREATE ... SELECT query.


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

about flush logs

[Tang, Grace H]

In my box, mysql server version is 4.0.18.
 
I tried "> flush logs". Nothing happened in the mysql data directory. All the log 
files were not replaced.
 
Does "flush logs" rename the old log files and create new log files?
Thanks.
 
Grace Tang
Software engineer
Computer Associates (China) Co., Ltd.
Beijing R&D Department
Units 7-10, 19/F, Tower E3, Oriental Plaza
1 East Chang An Ave. Dong Cheng District
Beijing 100738, China
Tel:   (86 10)  8518 5358  Ext. 273
Fax:   (86 10)  8518 8453
Mail: [EMAIL PROTECTED], [EMAIL PROTECTED]
 

Populating database...

[beginner]

Hi,
i just created a db with around 30 tables and i need
to populate it. Is there any software or special
technic for doing that automatically (using random
characters for example)?
Thanks,
ltcmelo

__

Yahoo! Messenger - Fale com seus amigos online. Instale agora! 
http://br.download.yahoo.com/messenger/

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

Re: First letter only of a column

[Tim Johnson]

* Peter Lovatt <[EMAIL PROTECTED]> [040413 16:27]:
> Hi
> 
> select left(field, 1) from table where field = "something"
> 
> http://dev.mysql.com/doc/mysql/en/String_functions.html

* Kevin Carlson <[EMAIL PROTECTED]> [040413 16:27]:
> try this:
>
> select  LEFT(names, 1) from table

 Thanks folks.
 I love it!

 tim

<..> > another way of asking my questions would be,
> > Is it possible to truncate columns in selection
> > set to a specific length (in the case: 1)
> > 
> > Pointers to relevant documents are welcome.

-- 
Tim Johnson <[EMAIL PROTECTED]>
  http://www.alaska-internet-solutions.com

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

Re: First letter only of a column

[Kevin Carlson]

try this:

select  LEFT(names, 1) from table

Tim Johnson wrote:

Hello:
   Is it possible to use mysql to select only
the first letter of a string in a column?
IOWS "select names from table" -> 
"select  names from table"
another way of asking my questions would be,
Is it possible to truncate columns in selection
set to a specific length (in the case: 1)

Pointers to relevant documents are welcome.

Thanks
tim
 

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

Re: How can I detect the end of a replication cycle?

[Garth Webb]

On Tue, 2004-04-13 at 13:13, [EMAIL PROTECTED] wrote:
[snip]
> >> I could frequently poll the slave servers using SHOW SLAVE STATUS or
> SHOW
> >> PROCESS LIST and watch for their status to change.
> >>
> >> I could start each slave with "--log-slave-updates" and watch the
> slaves'
> >> binlogs for changes.
> >>
> >> I could watch for the creation/deletion of the slave's relay logs.
> 
> > This seems to indicate that you are afraid of selecting rows on the
> > slave that are in the middle of being updated from the master.  A single
> > update statement is still atomic, so you don't need to poll log files to
> > determine if an update statement has finished.
> 
> > On the other hand, if there is some set of multiple updates and inserts
> > that constitute a collection of data that you want to merge only when
> >this collection is complete, you're better off finding a way to signal
> > this through the database.  You could have the master lock the tables in
> > question until its finished and then the program quering the slave knows
> > that when it gets a read lock, its will see the full set of data.  You
> > could also have a status column or a status table that has a flag
> > letting the program on the slave side know when the data is ready.
> 
> > If this is off the mark, maybe some example statements would help...
> 
> [more snipping]
> 
> Yes, I AM concerned about getting a partial update to the warehouse. I know
> that transactions aren't logged until after they commit. If I use
> transactional boundaries to post multitable reports (for instance: an
> invoice takes two tables, one for the base information and one for the line
> items) into the branch masters then they will arrive intact and I won't
> corrupt the central slaves. (That's NOT the issue I am worried about!)
> 
> I am worried that if I start processing those new records from the slave
> database to the Warehouse before all of the records have been processed
> from the Relay Logs (lets say I started trying to merge records when I see
> the relay log being created), I could miss some data (like the last few
> items on the invoice). That's why I am so worried about not merging until
> the END of the cycle. I need to be sure that everything has been committed
> to my central slave BEFORE I can merge the latest updates with the
> warehouse database.
> 
> I can lock a slave database so that it won't replicate in the middle of my
> merging so I know that if I can catch a slave when it goes back to "sleep"
> ("Waiting for master to send event"), I would have a complete set of data.
> I could use "--log-slave-updates" to copy the updates to the slave's binlog
> and check that to see if I need to merge records( if slave status is
> "waiting" and the binlog is not "empty" then merge). Each merge could flush
> the binlog. However, there was a post from someone using binlogs for
> similar purpose that said that for 4.1.x+ the binlogs vary in size after
> flushing so I don't know how reliable that would be as a check. How could I
> tell when a binlog is empty?

Using the binlog in this way will lead to a race condition.  What if
another update comes in during the few milliseconds between your 'ready'
check (an empty binlog and a 'waiting' status) and when you select those
rows for processing?  Plus if MySQL does any buffering of its output to
the binlog, you could be basing your check on seconds old data, further
aggravating the problem.

The only solution I can think of that won't cause a race condition is to
lock your tables, but your program running on the slave database would
have to be able to connect to the master.  Your slave program would lock
the necessary tables on the master side, wait to receive the lock, wait
until the slave had caught up, do your merge, then release the lock. 
Likewise, the code updating tables on the master would need to lock the
tables while they write.  You make the process a little friendlier by
creating a separate control table that the slave and master alternately
locked, rather than locking all the table you'll use.  That way
processes on the master that just want to read the data don't have to
wait for a lock.


-- 

 |- Garth Webb   -|
 |- [EMAIL PROTECTED] -|


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

REPLACE query

[Kevin Carlson]

I have a table with four columns, the first three of which are combined 
into a unique key:

create table Test {
  cid int(9) NOT NULL default '0',
  sid int(9) NOT NULL default '0',
  uid int(9) NOT NULL default '0',
  rating tinyint(1) NOT NULL default '0',
  UNIQUE KEY csu1 (cid,sid,uid),
  KEY cid1 (sid),
  KEY sid1 (sid),
  KEY uid1 (sid),
} TYPE=InnoDB;
I am using a REPLACE query to insert a row if it doesn't exist and 
replace an existing row if one does exist:

  REPLACE into TEST (cid, sid, uid, rating) values (580, 0, 205, 1)

In the case of this particular row, a row already exists with the 
concatenated key of 580-0-205 and I am getting a duplicate key error.  I 
thought REPLACE was supposed to actually replace the contents of the row 
if one exists.  Does anyone have any ideas as to why this would be 
causing a duplicate key error?

Thanks,

Kevin

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

First letter only of a column

[Tim Johnson]

Hello:
Is it possible to use mysql to select only
the first letter of a string in a column?
IOWS "select names from table" -> 
 "select  names from table"
another way of asking my questions would be,
Is it possible to truncate columns in selection
set to a specific length (in the case: 1)

Pointers to relevant documents are welcome.

Thanks
tim
-- 
Tim Johnson <[EMAIL PROTECTED]>
  http://www.alaska-internet-solutions.com

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

Re: User variables + SUM + GROUP BY = strange behavior

[Emmett Bishop]

Vadim,

if I'm not mistaken, you can't set a variable then use
it in the same statement. 

See http://dev.mysql.com/doc/mysql/en/Variables.html

A little ways down the page...
"The general rule is to never assign and use the same
variable in the same statement."

-- Tripp

--- "Vadim P." <[EMAIL PROTECTED]> wrote:
> 
> Sorry, the message got garbled, here is a more
> digestible look:
> 
> -Original Message-
> 
> Hello all,
> 
> Could anyone comment on User Variable behavior in
> the example below?
> 
> Thanks,
> Vadim.
> 
>
=
> 
> mysql> SELECT
> ->LEFT(CallTime,10) AS CallDate,
> ->@a := SUM(Charge),
> ->@b := SUM(Cost),
> ->@a - @b,
> ->@a,
> ->@b
> -> FROM Calls
> -> GROUP by CallDate
> -> ORDER BY CallDate DESC;
> 
>
++--++-++-
> | CallDate   | @a:= SUM(Charge) | @b:= SUM(Cost) |
> @a - @b | @a | @b
>
++--++-++-
> ...
> | 2004-03-01 |  621.059 |249.310 | 
> 30.882 | 39.512 | 8.63
> | 2004-02-29 |   54.620 | 17.660 | 
> 30.882 | 39.512 | 8.63
> | 2004-02-28 |  205.581 | 17.460 | 
> 30.882 | 39.512 | 8.63
> | 2004-02-27 |  622.282 |248.920 | 
> 30.882 | 39.512 | 8.63
> | 2004-02-26 |  607.274 |277.100 | 
> 30.882 | 39.512 | 8.63
> | 2004-02-25 |  709.698 |308.580 | 
> 30.882 | 39.512 | 8.63
> | 2004-02-24 |  783.210 |298.560 | 
> 30.882 | 39.512 | 8.63
> | 2004-02-23 |  799.764 |252.890 | 
> 30.882 | 39.512 | 8.63
> ...
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 





__
Do you Yahoo!?
Yahoo! Tax Center - File online by April 15th
http://taxes.yahoo.com/filing.html

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

Re: Multiple SELECTs in one query

[Udikarni]

I am not sure about MySQL but in Oracle this will NOT work:  "SELECT "A" AS SortCode,  
* FROM Jobs"
However, this WILL:  "SELECT "A" AS SortCode, 
Jobs.* FROM Jobs"

Try adding the table or alias in front of the "*".

In general, however, I will repeat my suggestion from before - try to do everything in 
one pass - it's much more efficient.

Something like this:

"SELECT (CASE 
  WHEN Jobs.Project =  MyProject AND Jobs.JobType =  MyJobType  then "A"
  WHEN Jobs.Project => MyProject AND Jobs.JobType <> MyJobType  then "B"
  WHEN Jobs.Project <> MyProject AND Jobs.JobType =  MyJobType  then "C"
  WHEN Jobs.Project <> MyProject AND Jobs.JobType <> MyJobType  then "D"
 END CASE
)  SortCode,
Jobs.*
ORDER BY SortCode  ASC,
 Jobs.Priority ASC

Instead of reading the table 4 times, each time taking a slice and labeling it A/B/C/D 
you read it once and during that one pass you attach to each row the code of A/B/C/D 
depending on its content and you're done. One pass instead of 4 and no UNIONs.

You might have to tinker with the syntax if CASE is not available in MySQL to this 
extent but that's the general idea.






In a message dated 4/13/2004 6:50:33 PM Eastern Daylight Time, [EMAIL PROTECTED] 
writes:

> 
> Hey gang, many thanks to all for pointing me in the right direction for 
> my previous "multiple selects" question.  I moved to 4.1.1 and 
> implemented Udikarni's use of multiple sum()s instead of multiple 
> selects() and that stuff is all groovy now!
> 
> Of course, I'm beating my head on *another* wall now...wouldn't ya just 
> know it?
> 
> My client code checks the main table for a few different criteria, and I 
> used an additional "hard" select for a sorting method.  Basically, each 
> client looks for jobs to process, starting with jobs under its "default 
> project" and "default jobtype", and then by its "default project" and 
> all other jobtypes, and finally everything else.  Within each of these 
> sets, jobs are sorted by a "Priority" field.
> 
> My previous query looked like this (butchered pseudocode follows):
> 
> "SELECT "A" AS SortCode, * FROM Jobs WHERE Jobs.Project = MyProject AND 
> Jobs.JobType = MyJobType
> UNION ALL SELECT "B" AS SortCode, * FROM Jobs WHERE Jobs.Project = 
> MyProject AND Jobs.JobType <> MyJobType
> UNION ALL SELECT "C" AS SortCode, * FROM Jobs WHERE Jobs.Project <> 
> MyProject AND Jobs.JobType = MyJobType
> UNION ALL SELECT "D" AS SortCode, * FROM Jobs WHERE Jobs.Project <> 
> MyProject AND Jobs.JobType <> MyJobType
> ORDER BY SortCode ASC, Jobs.Priority ASC
> 
> Now, in MySQL 4.1.1, I can't even get the first line to work - I suspect 
> that I'm doing something wrong with that pesky asterisk, because the 
> following works:
> 
> "SELECT "A" AS SortCode, JobName FROM Jobs"
> 
> But the following does not:
> 
> "SELECT "A" AS SortCode, * FROM Jobs"
> 
>  From what I can see in the MySQL.org docs, this should work...any 
> ideas?   If I can get around that, I suspect that my UNIONS will work OK 
> and all will be well in the worldone can hope?!?
> 
> As before, many thanks for any insight that y'all can provide!!
> 
>   Steve
> 
> 
> -- 
> 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]

Suse 9, chroot mysql [long]

[Troy Coulombe]

OS: suse 9
Mysql: 4.0.15
Googled: yep, but didn't find anything conclusive

I'm attempting to chroot mysql, and I'm failing [miserabaly].  I realize
that this is a mysql list, but I currently believe it's either a dependency
of mysql I forgot or something something I'm not aware of w/ mysql. I also
figured I'd get a better response from people who also have chroot'd mysql
rather than asking the chroot folks.

Excuting mysql non-chroot'd works fine.  I'm using the stock mysql from
Suse, and therefore it's dynam linked.  I've 'ldd /usr/sbin/myslqd' as well
as 'ldd /usr/bin/mysql'

It [chroot] fails stating "permision denied" w/o giving anymore info [is
there some hidden switch for chroot?]
When I strace it [strace -o fail.log chroot /chroot/mysql/ mysql
/usr/sbin/mysqld &] I get this in the output log:::


execve("/usr/bin/chroot", ["chroot", "/chroot/mysql/", "mysql",
"/usr/sbin/mysqld"], [/* 41 vars */]) = 0
uname({sys="Linux", node="template", ...}) = 0
brk(0)  = 0x804b9ac
old_mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0)
= 0x40019000
open("/etc/ld.so.preload", O_RDONLY)= -1 ENOENT (No such file or
directory)
open("/etc/ld.so.cache", O_RDONLY)  = 3
fstat64(3, {st_mode=S_IFREG|0644, st_size=12190, ...}) = 0
old_mmap(NULL, 12190, PROT_READ, MAP_PRIVATE, 3, 0) = 0x4001a000
close(3)= 0
open("/lib/i686/libc.so.6", O_RDONLY)   = 3
read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\320]\1"..., 512) =
512
fstat64(3, {st_mode=S_IFREG|0755, st_size=1461208, ...}) = 0
old_mmap(NULL, 1256644, PROT_READ|PROT_EXEC, MAP_PRIVATE, 3, 0) = 0x4001d000
old_mmap(0x40149000, 20480, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED, 3,
0x12c000) = 0x40149000
old_mmap(0x4014e000, 7364, PROT_READ|PROT_WRITE,
MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x4014e000
close(3)= 0
munmap(0x4001a000, 12190)   = 0
open("/usr/lib/locale/locale-archive", O_RDONLY|O_LARGEFILE) = -1 ENOENT (No
such file or directory)
brk(0)  = 0x804b9ac
brk(0x806c9ac)  = 0x806c9ac
brk(0)  = 0x806c9ac
brk(0x806d000)  = 0x806d000
open("/usr/share/locale/locale.alias", O_RDONLY) = 3
fstat64(3, {st_mode=S_IFREG|0644, st_size=2601, ...}) = 0
mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) =
0x4001a000
read(3, "# Locale name alias data base.\n#"..., 4096) = 2601
read(3, "", 4096)   = 0
close(3)= 0
munmap(0x4001a000, 4096)= 0
open("/usr/lib/locale/en_US/LC_CTYPE", O_RDONLY) = 3
fstat64(3, {st_mode=S_IFREG|0644, st_size=178468, ...}) = 0
mmap2(NULL, 178468, PROT_READ, MAP_PRIVATE, 3, 0) = 0x4015
close(3)= 0
chroot("/chroot/mysql/")= 0
chdir("/")  = 0
execve("/sbin/mysql", ["mysql", "/usr/sbin/mysqld"], [/* 41 vars */]) = -1
ENOENT (No such file or directory)
execve("/usr/sbin/mysql", ["mysql", "/usr/sbin/mysqld"], [/* 41 vars */]) =
-1 ENOENT (No such file or directory)
execve("/usr/local/sbin/mysql", ["mysql", "/usr/sbin/mysqld"], [/* 41 vars
*/]) = -1 ENOENT (No such file or directory)
execve("/root/bin/mysql", ["mysql", "/usr/sbin/mysqld"], [/* 41 vars */]) =
-1 ENOENT (No such file or directory)
execve("/usr/local/bin/mysql", ["mysql", "/usr/sbin/mysqld"], [/* 41 vars
*/]) = -1 ENOENT (No such file or directory)

execve("/usr/bin/mysql", ["mysql", "/usr/sbin/mysqld"], [/* 41 vars */]) =
-1 EACCES (Permission denied)

execve("/usr/X11R6/bin/mysql", ["mysql", "/usr/sbin/mysqld"], [/* 41 vars
*/]) = -1 ENOENT (No such file or directory)
execve("/bin/mysql", ["mysql", "/usr/sbin/mysqld"], [/* 41 vars */]) = -1
ENOENT (No such file or directory)
execve("/usr/games/mysql", ["mysql", "/usr/sbin/mysqld"], [/* 41 vars */]) =
-1 ENOENT (No such file or directory)
execve("/opt/gnome/bin/mysql", ["mysql", "/usr/sbin/mysqld"], [/* 41 vars
*/]) = -1 ENOENT (No such file or directory)
write(2, "chroot: ", 8) = 8
write(2, "mysql", 5)= 5
write(2, ": Permission denied", 19) = 19
write(2, "\n", 1)   = 1
exit_group(126) = ?



Now I'm definitely not a 'strace guru' but the last set of::: execve
"/sbin/mysql" seems to be chroot looking for 'mysqld' and not finding
it...But then it finds it [in the jail [/chroot/mysql/usr/bin/mysql] ] but
doesn't like the permissions. :(  Which follows the 'error message' that I
get when just using chroot w/o strace.

Here is a listing of my /chroot/mysql/* w/ permissions.  Am I missing a
dependency? Any thoughts? 


/chroot/mysql/dev:
total 8
drwxr-xr-x2 mysqlmysql4096 Apr 13 13:14 .
drwxr-xr-x9 mysqlmysql4096 Apr 13 14:49 ..
crw-rw-rw-1 mysqlmysql  1,   3 Apr 13 13:14 null

/chroot/mysql/etc:
total 36

User variables + SUM + GROUP BY = strange behavior

[Vadim P.]

Sorry, the message got garbled, here is a more digestible look:

-Original Message-

Hello all,

Could anyone comment on User Variable behavior in the example below?

Thanks,
Vadim.

=

mysql> SELECT
->  LEFT(CallTime,10) AS CallDate,
->  @a := SUM(Charge),
->  @b := SUM(Cost),
->  @a - @b,
->  @a,
->  @b
-> FROM Calls
-> GROUP by CallDate
-> ORDER BY CallDate DESC;

++--++-++-
| CallDate   | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b
++--++-++-
...
| 2004-03-01 |  621.059 |249.310 |  30.882 | 39.512 | 8.63
| 2004-02-29 |   54.620 | 17.660 |  30.882 | 39.512 | 8.63
| 2004-02-28 |  205.581 | 17.460 |  30.882 | 39.512 | 8.63
| 2004-02-27 |  622.282 |248.920 |  30.882 | 39.512 | 8.63
| 2004-02-26 |  607.274 |277.100 |  30.882 | 39.512 | 8.63
| 2004-02-25 |  709.698 |308.580 |  30.882 | 39.512 | 8.63
| 2004-02-24 |  783.210 |298.560 |  30.882 | 39.512 | 8.63
| 2004-02-23 |  799.764 |252.890 |  30.882 | 39.512 | 8.63
...



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

User variables + SUM + GROUP BY = strange behavior

[Vadim P.]

Hello all,

 

Could anyone comment on User Variable behavior in the example below?

 

Thanks,

Vadim.

 


=

 

mysql> SELECT

-> LEFT(CallTime,10) AS CallDate,

-> @a := SUM(Charge),

-> @b := SUM(Cost),

-> @a - @b,

-> @a,

-> @b

-> FROM Calls

-> GROUP by CallDate

-> ORDER BY CallDate DESC;

 

++---+-+-++-
-+

| CallDate   | @a := SUM(Charge) | @b := SUM(Cost) | @a - @b | @a | @b
|

++---+-+-++-
-+

.

| 2004-03-01 |   621.059 | 249.310 |  30.882 | 39.512 | 8.63
|

| 2004-02-29 |54.620 |  17.660 |  30.882 | 39.512 | 8.63
|

| 2004-02-28 |   205.581 |  17.460 |  30.882 | 39.512 | 8.63
|

| 2004-02-27 |   622.282 | 248.920 |  30.882 | 39.512 | 8.63
|

| 2004-02-26 |   607.274 | 277.100 |  30.882 | 39.512 | 8.63
|

| 2004-02-25 |   709.698 | 308.580 |  30.882 | 39.512 | 8.63
|

| 2004-02-24 |   783.210 | 298.560 |  30.882 | 39.512 | 8.63
|

| 2004-02-23 |   799.764 | 252.890 |  30.882 | 39.512 | 8.63
|

.

Re: Multiple SELECTs in one query

[Steve Pugh]

Hey gang, many thanks to all for pointing me in the right direction for 
my previous "multiple selects" question.  I moved to 4.1.1 and 
implemented Udikarni's use of multiple sum()s instead of multiple 
selects() and that stuff is all groovy now!

Of course, I'm beating my head on *another* wall now...wouldn't ya just 
know it?

My client code checks the main table for a few different criteria, and I 
used an additional "hard" select for a sorting method.  Basically, each 
client looks for jobs to process, starting with jobs under its "default 
project" and "default jobtype", and then by its "default project" and 
all other jobtypes, and finally everything else.  Within each of these 
sets, jobs are sorted by a "Priority" field.

My previous query looked like this (butchered pseudocode follows):

"SELECT "A" AS SortCode, * FROM Jobs WHERE Jobs.Project = MyProject AND 
Jobs.JobType = MyJobType
UNION ALL SELECT "B" AS SortCode, * FROM Jobs WHERE Jobs.Project = 
MyProject AND Jobs.JobType <> MyJobType
UNION ALL SELECT "C" AS SortCode, * FROM Jobs WHERE Jobs.Project <> 
MyProject AND Jobs.JobType = MyJobType
UNION ALL SELECT "D" AS SortCode, * FROM Jobs WHERE Jobs.Project <> 
MyProject AND Jobs.JobType <> MyJobType
ORDER BY SortCode ASC, Jobs.Priority ASC

Now, in MySQL 4.1.1, I can't even get the first line to work - I suspect 
that I'm doing something wrong with that pesky asterisk, because the 
following works:

   "SELECT "A" AS SortCode, JobName FROM Jobs"

But the following does not:

   "SELECT "A" AS SortCode, * FROM Jobs"

From what I can see in the MySQL.org docs, this should work...any 
ideas?   If I can get around that, I suspect that my UNIONS will work OK 
and all will be well in the worldone can hope?!?

As before, many thanks for any insight that y'all can provide!!

 Steve

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

Re: Altering MAX_DATA_LENGTH

[Matt W]

Hi Dan,

(Sending to the General list too, since this isn't a Windows-specific
thing.)

SHOW TABLE STATUS LIKE 'tbl_name'

will show you the current Avg_row_length.

But the specific values for MAX_ROWS and AVG_ROW_LENGTH when you CREATE
or ALTER the table don't matter (except for "looking correct" :-)) as
long as their product is greater than 4GB.  BTW, you can't have the
limit be 8GB -- when you go greater than 4GB, the Max_data_length will
be 1TB.


Hope that helps.


Matt


- Original Message -
From: "Dan"
Sent: Tuesday, April 13, 2004 3:58 PM
Subject: Altering MAX_DATA_LENGTH


> If I have a table that has two fields: Char(100), Blob
>
> How do I determine the avg_row_length value if I want to increase the
size limit to 8GB?
>
> Thanks
> Dan


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

Re: MySQL function

[java_nutt]

> 
> From: "Gordon" <[EMAIL PROTECTED]>
> Date: 2004/04/13 Tue PM 06:45:17 GMT
> To: <[EMAIL PROTECTED]>
> Subject: MySQL function
> 
> 
> Has anyone out there written a Credit Card Validation routine as a
> user-definable function (UDF)?
> 
> We now have a requirement to collect credit card data through our Web
> Site.
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
>

This sounds like it should be handled more by your server-side software (PHP, ASP, 
JSP, etc.) rather than MySQL.  I'm no web guy, and don't claim to be, but to me it 
would make more sense to have your server side programming language handle that, 
rather than MySQL.  I believe you can find some pre-written code if you hit Google.  
Something tells me I've seen it out there, just don't remember where.

James


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

Replication and Error 1200

[jim]

Hi,

I'm trying to get replication set up on a slave, and getting the
error: "Error 1200: The server is not configured as slave, fix in
config file or with CHANGE MASTER TO".

The master machine is set up already, and there is already
one database replicating off of it (a second instance of mysql on
the same machine as the master).

In master, FILE, SUPER, RELOAD, and SELECT have all been GRANTed
to the slave user, and that user can log in using the
command-line client.  The GRANTs look like

grant select on *.* to '[EMAIL PROTECTED]' identified by 'foobar';

Here is the relevant part of the my.cnf:
[mysqld]
port= 3306
socket  = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
myisam_sort_buffer_size = 8M
set-variable= default-character-set=cp1251
log-bin
log-warnings
log-slow-queries = /usr/local/mysql/data/slowqueries.log
server-id   = 75
replicate-do-table=master.data
replicate-wild-do-table=search.%
replicate-do-table=profile.digest
replicate-do-table=profile.digestdata
replicate-do-table=profile.user
master-host=192.168.2.2
master-user=root
master-password=foobar
master-connect-retry=10

This is the SQL that should start the replication on the slave:
mysql> CHANGE MASTER TO MASTER_HOST='dbhost',
-> MASTER_USER='root', MASTER_PASSWORD='foobar',
-> MASTER_LOG_FILE='dbhost-bin.045',
-> MASTER_LOG_POS=4606; Query OK, 0 rows affected (0.00 sec)

This runs ok, but slave start; gives the error.

This is in mySQL 4.0.12, on Linux.


Sorry if that's too much information.  Thanks to any and all for
comments or help.

Regards,
Jim N.



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

RE: Reinstall Windows.

[Amit_Wadhwa]

Backup the data folder under the mysql folder, that's it
 

-Original Message-
From: Alejandro C. Garrammone [mailto:[EMAIL PROTECTED] 
Sent: Monday, April 12, 2004 10:11 AM
To: MySQL Mailing List
Subject: Reinstall Windows.

I need to re-install windows, so I need to re-install mysql. How can I
backup my databases so when I reinstall mysql put them to work again?,

Thx in advance,

Alex


--
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: ORDER BY alias

[Michael Stassen]

As far as I know, you can't use an alias in a calculation outside of a 
HAVING clause.  You could work around this by adding the calculation to your
SELECT clause:

  SELECT kills.PlayerID, player.DeathsPerMinute,
 SUM(kills.Kills) AS Total,
 SUM(kills.Kills) * (1-player.DeathsPerMinute) AS rank
  FROM playerweaponkills AS kills, ETPlayerSummary AS player
  WHERE kills.PlayerID=player.PlayerID AND kills.WeaponID=17
  GROUP BY kills.PlayerID
  ORDER BY rank DESC LIMIT 5
Michael

Danielb wrote:

I'm trying to order by an alias in a multi table SELECT statement(Note
I've cut the statement down a bit to make it more readable):
SELECT kills.PlayerID, player.DeathsPerMinute, SUM(kills.Kills) AS Total
FROM playerweaponkills AS kills, ETPlayerSummary AS player WHERE
kills.PlayerID=player.PlayerID AND kills.WeaponID=17 GROUP BY kills.PlayerID
ORDER BY (Total*(1-player.DeathsPerMinute)) DESC LIMIT 5
When I run this I get the error:
#1054 - Unknown column 'Total' in 'order clause'
I take it the problem is that MySQL is unable to resolve the alias Total
when its used in this way with player.DeathsPerMinute? Is there any way I
can prefix Total to help it be resolved? The statement works fine with
ordering by either Total or (1-player.DeathsPerMinute) its when you try and
combine them in the above statement it freaks out.
Any ideas? I ideal want to order by:
(Total*(1-player.DeathsPerMinute))
Cheers,

Daniel



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

Re: Strange Index Usage: select ... where foo = 90 on a varchar

[Michael Stassen]

You shouldn't be surprised.  This is normal behavior.  interchangeStatus is 
a varchar, so

  select fileName from outDocInterchange where interchangeStatus = 91;

requires that interchangeStatus be converted to an int for each row so it 
can be compared to 91, rendering the index useless.  On the other hand,

  select fileName from outDocInterchange where interchangeStatus =  '91';

compares interchangeStatus to a string, which the index is designed to do. 
In general, an index on a column won't help if the column is input to a 
function.

Michael

Max Campos wrote:

Lucy, you've got some EXPLAINing to do... (sorry, couldn't resist)

A) select fileName from outDocInterchange where interchangeStatus = 91;
B) select fileName from outDocInterchange where interchangeStatus =  '91';
(A) Runs unindexed, (B) runs with the istat_date index.  Can anyone  
explain why?

My table (other columns/keys removed):
Create Table: CREATE TABLE `outDocInterchange` (
  `dateReceived` datetime default '-00-00 00:00:00',
  `interchangeStatus` varchar(20) default NULL,
  KEY `istat_date` (`interchangeStatus`,`dateReceived`),
) TYPE=MyISAM
Obviously I need to change interchangeStatus to an int, but I was still  
suprised to see the results:

mysql> explain select fileName from outDocInterchange where  
interchangeStatus = 91;
+---+--+---+--+-+-- 
+---+-+
| table | type | possible_keys | key  | key_len | ref  |  
rows  | Extra   |
+---+--+---+--+-+-- 
+---+-+
| outDocInterchange | ALL  | istat_date| NULL |NULL | NULL |  
37223 | Using where |
+---+--+---+--+-+-- 
+---+-+
1 row in set (0.08 sec)

mysql> explain select fileName from outDocInterchange where  
interchangeStatus = '91';
+---+--+---++- 
+---+--+-+
| table | type | possible_keys | key| key_len | ref  
  | rows | Extra   |
+---+--+---++- 
+---+--+-+
| outDocInterchange | ref  | istat_date| istat_date |  21 |  
const |1 | Using where |
+---+--+---++- 
+---+--+-+
1 row in set (0.08 sec)

I'm using MySQL 4.0.18 for Solaris 8.

Can anyone explain this?  Or is this a bug (or missing optimization)?

Thanks.

- Max




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

Re: How can I detect the end of a replication cycle?

[SGreen]

> On Tue, 2004-04-13 at 15:11, [EMAIL PROTECTED] wrote:

>> On Tue, 2004-04-13 at 11:21, [EMAIL PROTECTED] wrote:
[more snip]
>> Somehow, I need to detect the
>> end of my central slaves' replication cycle so that I can trigger the
>> "merge processing".

> Its not clear what you mean by 'replication cycle'.

A replication cycle starts when the slave is notified by the master that
there are binlog entries it needs to process and ends when the slave has
finished processing those entries and returns to waiting for more updates
from the master.

>> I could frequently poll the slave servers using SHOW SLAVE STATUS or
SHOW
>> PROCESS LIST and watch for their status to change.
>>
>> I could start each slave with "--log-slave-updates" and watch the
slaves'
>> binlogs for changes.
>>
>> I could watch for the creation/deletion of the slave's relay logs.

> This seems to indicate that you are afraid of selecting rows on the
> slave that are in the middle of being updated from the master.  A single
> update statement is still atomic, so you don't need to poll log files to
> determine if an update statement has finished.

> On the other hand, if there is some set of multiple updates and inserts
> that constitute a collection of data that you want to merge only when
>this collection is complete, you're better off finding a way to signal
> this through the database.  You could have the master lock the tables in
> question until its finished and then the program quering the slave knows
> that when it gets a read lock, its will see the full set of data.  You
> could also have a status column or a status table that has a flag
> letting the program on the slave side know when the data is ready.

> If this is off the mark, maybe some example statements would help...

[more snipping]

Yes, I AM concerned about getting a partial update to the warehouse. I know
that transactions aren't logged until after they commit. If I use
transactional boundaries to post multitable reports (for instance: an
invoice takes two tables, one for the base information and one for the line
items) into the branch masters then they will arrive intact and I won't
corrupt the central slaves. (That's NOT the issue I am worried about!)

I am worried that if I start processing those new records from the slave
database to the Warehouse before all of the records have been processed
from the Relay Logs (lets say I started trying to merge records when I see
the relay log being created), I could miss some data (like the last few
items on the invoice). That's why I am so worried about not merging until
the END of the cycle. I need to be sure that everything has been committed
to my central slave BEFORE I can merge the latest updates with the
warehouse database.

I can lock a slave database so that it won't replicate in the middle of my
merging so I know that if I can catch a slave when it goes back to "sleep"
("Waiting for master to send event"), I would have a complete set of data.
I could use "--log-slave-updates" to copy the updates to the slave's binlog
and check that to see if I need to merge records( if slave status is
"waiting" and the binlog is not "empty" then merge). Each merge could flush
the binlog. However, there was a post from someone using binlogs for
similar purpose that said that for 4.1.x+ the binlogs vary in size after
flushing so I don't know how reliable that would be as a check. How could I
tell when a binlog is empty?




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

Strange Index Usage: select ... where foo = 90 on a varchar

[Max Campos]

Lucy, you've got some EXPLAINing to do... (sorry, couldn't resist)

A) select fileName from outDocInterchange where interchangeStatus = 91;
B) select fileName from outDocInterchange where interchangeStatus =  
'91';

(A) Runs unindexed, (B) runs with the istat_date index.  Can anyone  
explain why?

My table (other columns/keys removed):
Create Table: CREATE TABLE `outDocInterchange` (
  `dateReceived` datetime default '-00-00 00:00:00',
  `interchangeStatus` varchar(20) default NULL,
  KEY `istat_date` (`interchangeStatus`,`dateReceived`),
) TYPE=MyISAM
Obviously I need to change interchangeStatus to an int, but I was still  
suprised to see the results:

mysql> explain select fileName from outDocInterchange where  
interchangeStatus = 91;
+---+--+---+--+-+-- 
+---+-+
| table | type | possible_keys | key  | key_len | ref  |  
rows  | Extra   |
+---+--+---+--+-+-- 
+---+-+
| outDocInterchange | ALL  | istat_date| NULL |NULL | NULL |  
37223 | Using where |
+---+--+---+--+-+-- 
+---+-+
1 row in set (0.08 sec)

mysql> explain select fileName from outDocInterchange where  
interchangeStatus = '91';
+---+--+---++- 
+---+--+-+
| table | type | possible_keys | key| key_len | ref  
  | rows | Extra   |
+---+--+---++- 
+---+--+-+
| outDocInterchange | ref  | istat_date| istat_date |  21 |  
const |1 | Using where |
+---+--+---++- 
+---+--+-+
1 row in set (0.08 sec)

I'm using MySQL 4.0.18 for Solaris 8.

Can anyone explain this?  Or is this a bug (or missing optimization)?

Thanks.

- Max

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

What distribution should I use for Mac OS X 10.3.x (Panther?

[Daniel Lahey]

Which distribution should I install on Mac OS X Panther?  Thanks.

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

Re: Arguments to fight against Ms SQL Server and Oracle

[Alvaro Avello]

Leonardo : La verdad es que debes analizar lo que yo llamo "¿donde te 
aprieta el zapato?" , esto quiere decir , que si requieres 
procedimientos almacenados y triggers y vistas , quizas oracle haga el 
trabajo. Si por otro lado la logica del negocio esta en tus 
aplicacciones y no requieres de estas caracteristicas que te nombre 
anteriormente Mysql es una buena opcion . la verdad es que debes leer 
respecto de las caracteristicas que hoy en dia tiene Mysql y comparar 
con los otros motores ya que como dicen por alli "/la ignorancia es 
insolente/".

Saludos,
Alvaro Avello.
Jochem van Dieten wrote:

Leonardo Javier Belén wrote:

The staff on the other hand, is thinking in using ORACLE, and I need 
very solid arguments to beet them. Any ideas I can borrow?


If *you* don't know any arguments, then maybe Oracle is the best 
solution.

Jochem

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

Re: How can I detect the end of a replication cycle?

[Garth Webb]

On Tue, 2004-04-13 at 11:21, [EMAIL PROTECTED] wrote:
> Hello List:
[snip]
> Here is where things get sticky. I would like to be able to merge the
> replicated reports into the warehouse database with the smallest practical
> delay (I have some very process-driven branch mangers and they feel they
> "must have" this data sooner than later).  I think that I will need to hold
> off merging records from any particular branch until replication completes
> with that branch (to maintain relational integrity, transaction boundaries,
> etc. Nobody said the reports were simple.). Somehow, I need to detect the
> end of my central slaves' replication cycle so that I can trigger the
> "merge processing".

Its not clear what you mean by 'replication cycle'.

> I could frequently poll the slave servers using SHOW SLAVE STATUS or SHOW
> PROCESS LIST and watch for their status to change.
> 
> I could start each slave with "--log-slave-updates" and watch the slaves'
> binlogs for changes.
> 
> I could watch for the creation/deletion of the slave's relay logs.

This seems to indicate that you are afraid of selecting rows on the
slave that are in the middle of being updated from the master.  A single
update statement is still atomic, so you don't need to poll log files to
determine if an update statement has finished.

On the other hand, if there is some set of multiple updates and inserts
that constitute a collection of data that you want to merge only when
this collection is complete, you're better off finding a way to signal
this through the database.  You could have the master lock the tables in
question until its finished and then the program quering the slave knows
that when it gets a read lock, its will see the full set of data.  You
could also have a status column or a status table that has a flag
letting the program on the slave side know when the data is ready.

If this is off the mark, maybe some example statements would help...

> Basically I wind up using a timer to check the status of (something) and I
> am afraid that I will miss a status flip between timer ticks. Setting my
> timer too short will just consume excessive CPU cycles and also be counter
> productive. The target platform for my central server is tentatively Redhat
> 9.x with MySQL 4.1.xxx (the stable version when we go live). Can Linux help
> me here to hook into one of those events? If another OS can provide better
> hooks into this I can work that into the plan.
> 
> Can anyone tell me why these ideas would or would not work? Is there a
> better way to synchronize an external process with the end of a replication
> event? Are there other options I didn't think of? (I even thought of
> modifying the MySQL source to produce a special "merge" slave but that will
> take too many people, too much time, be too task-specific,  and may not be
> compatible with future versions of MySQL to be a good choice.) Please help!
> 
> 
> Thanks in advance!
> Shawn
> [EMAIL PROTECTED]
> 
> ** delete all the parts with "no" in them to reveal my real address. **
-- 

 |- Garth Webb   -|
 |- [EMAIL PROTECTED] -|


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

Re: Arguments to fight against Ms SQL Server and Oracle

[Jochem van Dieten]

Leonardo Javier Belén wrote:
The staff on the other hand, is thinking in using ORACLE, and I need very solid arguments to beet them. Any ideas I can borrow?
If *you* don't know any arguments, then maybe Oracle is the best 
solution.

Jochem

--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Arguments to fight against Ms SQL Server and Oracle

[Leonardo Javier Belén]

Hi folks!
I am sorry to bother but I need your help. At work I need to upgrade the data 
management the place is using (very old FOXPRO DOS) with something more modern. The 
use is only to store data and run multiple querys in a post mortem fashion. The data 
source is a Ms SQL server so management is thinking on upgrading directly to redmond's 
soft. The staff on the other hand, is thinking in using ORACLE, and I need very solid 
arguments to beet them. Any ideas I can borrow?
Thanks
Leo.

MYSQL - QUERY - SQL - MYSQL - QUERY - SQL - MYSQL - QUERY - SQL - MYSQL - QUERY - SQL 
- MYSQL - QUERY - SQL - MYSQL - QUERY - SQL - MYSQL - QUERY - SQL 

MySQL function

[Gordon]

Has anyone out there written a Credit Card Validation routine as a
user-definable function (UDF)?

We now have a requirement to collect credit card data through our Web
Site.



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

How to replace a installed mysql version?

[Walter Andreas]

Hi there,

I am a bit confused. It is not clear to me that the version I am currently 
running is the one I compiled last. How can I check the date of compiling?
In order to get more performance I did try to install mysql 4.0.18 with different
configure commands, now I am not sure if the make install did really override the
already installed version of 4.0.18.

Is there a possibility to find this out? Maybe with a variable called build time or 
similar?

Thanx for any hint,

Andy
_
Der WEB.DE Virenschutz schuetzt Ihr Postfach vor dem Wurm Netsky.A-P!
Kostenfrei fuer alle FreeMail Nutzer. http://f.web.de/?mc=021157


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

How can I detect the end of a replication cycle?

[SGreen]

Hello List:

I have been crawling the docs, the mailing lists, and the discussion groups
looking for options to my issues for several weeks and have found answers
to most of them. I have one last nut to crack and if the answer is out
there I must be blind. That's why I finally came to the list for help.

Let me give a little background:
I need to replicate a "warehouse" database (sort of an OLAP summary) that
combines the reporting output from several branch offices back to those
offices. Because of our WAN architecture (and other more political
reasons), each branch will write their "reports" to a local database (on
the branch's LAN) that  replicates their information to a central office.
The central office will have a script/daemon/program (whatever) that will
combine the various reports from each of the branches into a coordinated
warehouse database. This warehouse database would be replicated in
hub-and-spoke fashion back to each of the various branches that need access
to that information.

I have already determined that I will need multiple instances of MySQL
running at the central office to act as slaves to the branches' "reporting"
masters (one instance per branch) and one more  instance to act as the
master of the compiled "warehouse" database. Each branch *could* run just
one MySQL instance  and be both a master to their reports database and a
slave to the warehouse (at least that is the working plan). If I have to
use two instances of MySQL in each branch, that's acceptable, too.

Here is where things get sticky. I would like to be able to merge the
replicated reports into the warehouse database with the smallest practical
delay (I have some very process-driven branch mangers and they feel they
"must have" this data sooner than later).  I think that I will need to hold
off merging records from any particular branch until replication completes
with that branch (to maintain relational integrity, transaction boundaries,
etc. Nobody said the reports were simple.). Somehow, I need to detect the
end of my central slaves' replication cycle so that I can trigger the
"merge processing".

I could frequently poll the slave servers using SHOW SLAVE STATUS or SHOW
PROCESS LIST and watch for their status to change.

I could start each slave with "--log-slave-updates" and watch the slaves'
binlogs for changes.

I could watch for the creation/deletion of the slave's relay logs.

Basically I wind up using a timer to check the status of (something) and I
am afraid that I will miss a status flip between timer ticks. Setting my
timer too short will just consume excessive CPU cycles and also be counter
productive. The target platform for my central server is tentatively Redhat
9.x with MySQL 4.1.xxx (the stable version when we go live). Can Linux help
me here to hook into one of those events? If another OS can provide better
hooks into this I can work that into the plan.

Can anyone tell me why these ideas would or would not work? Is there a
better way to synchronize an external process with the end of a replication
event? Are there other options I didn't think of? (I even thought of
modifying the MySQL source to produce a special "merge" slave but that will
take too many people, too much time, be too task-specific,  and may not be
compatible with future versions of MySQL to be a good choice.) Please help!


Thanks in advance!
Shawn
[EMAIL PROTECTED]

** delete all the parts with "no" in them to reveal my real address. **



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

Re: Best practice on table design

[Brad Eacker]

Ciprian Trofin writes:
>Basically I have some tables with only 2 fields (ID and name), and a
>central table, joined by a one-to-many relation. The key point here are the
>2-field tables. If I keep them separate, I can extend them (add new fields)
>without problem when need arise. But if there is no need for an extension
>(my case), all I get is a greater number of tables that I have to take care
>of. Wouldn't be better (maybe more efficient ?) to put all the 2-field
>tables in only table, with a separate ENUM field to separate the records on
>categories ?

Ciprian,
 There are two main purposes for normalization in this case.  The first
is to provide consistency of data.  Going back to your example, placing the
city name in each record allows the possibility of multiple spellings for
the city name, since each record has its own copy of the data.  The second
is space savings, since storing an int is usually 4 bytes at worst while a
city name is definitely more than 4 bytes.  Yes it does generate a second
table that only has the mappings from cityID to cityName, but you will likely
find it well worth the effort to use the mapping.
   Brad Eacker ([EMAIL PROTECTED])



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

Re: ADO driver?

[Giulio]

Il giorno 13/apr/04, alle 16:01, Martijn Tonies ha scritto:

Hi,

I've found VBMySQLDirect,

go to http://vbmysql.com and look for VBMySQLDirect under the
'Projects' section.
As the author itself explains on the documentation, it is not ADO, but
very very near to it. I'm using it succesfully on some applications on
VB 6, without problems.
Thanks for the message... but, I'm not using VB... Can this project
be used in place of an ADO driver? I guess not, right?
Well, I think yes,
It should follow quite well standard ADO methods and functions,
You must test it. anyway...
regards,

 Giulio


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & 
MS SQL
Server.
Upscene Productions
http://www.upscene.com

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




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

Re: Constraining MySQL Replication

[Egor Egorov]

Gowtham Jayaram <[EMAIL PROTECTED]> wrote:
> 
> I understand that MySQL Replication can be configured
> to replicate selected tables in a Database.  Is there
> anyway to further constrain the replicate, say based
> on a query of these tables etc..?
> 

No.



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




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

Re: Mysql MAtch against query help

[beacker]

>> I have a query that searches my database for people
>> with C++ on their resume .
...
>Hehe I've also had a problem with searching for something like it's ,
>anything with a single quote doesnt return anything, maybe try adding a
>slash , C\+\+ ?? heheh maybe i'm wrong, it could be a limitation.

Couldn't say for sure, but the '+' is definitely a special character
in regexp syntax.  Another possibility is how text gets broken down
into tokens.  The '+' symbol will likely be separated from the C when
parsing the text into tokens.
 Brad Eacker ([EMAIL PROTECTED])



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

RE: What is the difference Between the mysql HEAP Table type and Views

[adburne]

A heap table isn't a view, it's just a table in memory, when you stop mysql the table disappear. Are usefull for speed selects; you don't need access hd.
In the other hand views are definitions from other(s) table(s) stored in the db, are permanent and can be updated
 
 
Alejandro.
 
 
---Mensaje original---
 

De: Victor Pendleton
Fecha: 04/13/04 11:30:05
Para: 'Abiola Aluko '; '[EMAIL PROTECTED] '
Asunto: RE: What is the difference Between the mysql HEAP Table type and Views
 
Have you tried to update an underlying heap table? The heap table will not
be updated. A view is updated when any of the underlying table(s) are
updated.
 
-Original Message-
From: Abiola Aluko
To: [EMAIL PROTECTED]
Sent: 4/13/04 8:01 AM
Subject: What is the difference Between the mysql HEAP Table type and Views
 
I know this might sound like a rather funny question to many gurus out
here, but I'm a bit confused.
 
The example give in the mysql manual is:
 
mysql> CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) AS down
->   FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;
 
ironically the example given in the postgresql manual for views looks
like it does the same things as the above
sql statements:
 
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
 
SELECT * FROM myview;
 
Please enlighten me.
 
Thanks
 
Abiola Aluko.
 
Lycos Email has 10 MB of FREE storage space. http://mail.lycos.co.uk
 
 
  <>
 
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]







_  IncrediMail - El Email ha evolucionado finalmente - Haga clic aquí

RE: Mysql MAtch against query help

[Haitao Jiang]

+ is not part of the definition of a word in MySQL.
One solution is to normalize "C++" into "CPLUSPLUS"
both during index and query time.

Haitao
--- electroteque <[EMAIL PROTECTED]> wrote:
> Hehe I've also had a problem with searching for
> something like it's ,
> anything with a single quote doesnt return anything,
> maybe try adding a
> slash , C\+\+ ?? heheh maybe i'm wrong, it could be
> a limitation.
> 
> > -Original Message-
> > From: David Taiwo
> [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, April 13, 2004 9:32 PM
> > To: [EMAIL PROTECTED]
> > Subject: Mysql MAtch against query help
> >
> >
> > I have a query that searches my database for
> people
> > with C++ on their resume .
> >
> > Query
> > =
> > select *  ,match(Res_resume) AGAINST ('C++') as
> > kewyordscore from member,memberprofile,resume left
> > join stateprovince on stateid = Res_state
> > where mem_id = mempf_memid and
> >  match(Res_resume) AGAINST ('+C++' IN BOOLEAN
> MODE)
> >
> > I have the ft_min_word_len set to 2 , but for some
> > reason , it still does not return a record when i
> > search for C++. Is there a way to escape the ++
> when i
> > do a search , or is there something i am not doing
> > right.
> > The query works when i do a search for 'PR' or
> 'P*' .
> > so i am sure that i reindexed my database.
> > I am using Mysql version 4.1.1 on windows.
> > Any help owuld be great ... thx
> >
> > Dave
> >
> >
> >
> >
> >
> >
> >
> > __
> > Do you Yahoo!?
> > New DSL Internet Access from SBC & Yahoo!
> > http://sbc.yahoo.com
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 





__
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway 
http://promotions.yahoo.com/design_giveaway/

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

Re: sql bench problems

[Brad Eacker]

Yonah Russ writes:
>here is a sample output line:
>
>Time for alter_table_drop (91): 19 wallclock secs ( 0.02 usr  0.00 sys +  
0.00 cusr  0.00 csys =  0.02 CPU)
>
>
>here is the regexp:
>
>/^(estimated |)time (to|for) ([^\s:]*)\s*\((\d*)(:\d*)*\)[^:]*:\s*([\d.]+) 
.*secs \(\s*([^\s]*) usr\s*\+*\s*([^\s]*) sys.*=\s+([\d.]*)\s+cpu/i

Yonah,
 It appears that the source has been modified from the original regexp.
Primary changes are:
time -> Time
cpu -> CPU

You may want to run the output thru
tr '[A-Z]' '[a-z]'
to change upper case to lower case to allow the regexp to work properly.

Brad Eacker ([EMAIL PROTECTED])



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

Re: ADO driver?

[Martijn Tonies]

Hi,

> I've found VBMySQLDirect,
>
> go to http://vbmysql.com and look for VBMySQLDirect under the
> 'Projects' section.
>
> As the author itself explains on the documentation, it is not ADO, but
> very very near to it. I'm using it succesfully on some applications on
> VB 6, without problems.

Thanks for the message... but, I'm not using VB... Can this project
be used in place of an ADO driver? I guess not, right?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


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

RE: What is the difference Between the mysql HEAP Table type and Views

[Victor Pendleton]

Have you tried to update an underlying heap table? The heap table will not
be updated. A view is updated when any of the underlying table(s) are
updated.

-Original Message-
From: Abiola Aluko 
To: [EMAIL PROTECTED]
Sent: 4/13/04 8:01 AM
Subject: What is the difference Between the mysql HEAP Table type and Views

I know this might sound like a rather funny question to many gurus out
here, but I'm a bit confused. 

The example give in the mysql manual is:

mysql> CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) AS down
->   FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;

ironically the example given in the postgresql manual for views looks
like it does the same things as the above 
sql statements:

CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;

SELECT * FROM myview;

Please enlighten me.

Thanks

Abiola Aluko.

Lycos Email has 10 MB of FREE storage space. http://mail.lycos.co.uk


 <> 

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

Re: Finding configure command after installation

[beacker]

>I just recompiled mysql and I am wondering if there is something like in php
>(phpinfo();) where you can see the configure command after the db is 
installed.
>It would just be nice to have that in a later time, or even to make sure that
>the new version has replaced the old one.

The initial portion of config.log in the directory where the source was built
contains the initial config line:

>It was created by configure, which was
>generated by GNU Autoconf 2.53.  Invocation command line was
>
>  $ ./configure --prefix=/usr/local/mysql4

Brad Eacker ([EMAIL PROTECTED])


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

Re: Best practice on table design

[Ciprian Trofin]

Carsten,

Thanks for the answer (and other thanks go to the other guys that answered
me).

I think normalization is the way to go. I think it is the right thing to
do (in theory). The problem is that theory doesn't fit all.

Basically I have some tables with only 2 fields (ID and name), and a
central table, joined by a one-to-many relation. The key point here are the
2-field tables. If I keep them separate, I can extend them (add new fields)
without problem when need arise. But if there is no need for an extension
(my case), all I get is a greater number of tables that I have to take care
of. Wouldn't be better (maybe more efficient ?) to put all the 2-field
tables in only table, with a separate ENUM field to separate the records on
categories ?

--
 Cip



CRD> Hi Ciprian,

CRD> OK,  I'm  by  no means a DB guru, so a) take this with a grain of salt
CRD> and b) feel free to tear it apart if I'm completely wrong! ;]

CRD> If  in  fact  your  people and city tables aren't going to change very
CRD> often,  then  why  don't  you  just  go  all  the  way  and  keep that
CRD> information  somewhere  else in your application and write it straight
CRD> to your travel_expenditures table, e.g.:

[..]


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

What is the difference Between the mysql HEAP Table type and Views

[Abiola Aluko]

I know this might sound like a rather funny question to many gurus out here, but I'm a 
bit confused. 

The example give in the mysql manual is:

mysql> CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) AS down
->   FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;

ironically the example given in the postgresql manual for views looks like it does the 
same things as the above 
sql statements:

CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;

SELECT * FROM myview;

Please enlighten me.

Thanks

Abiola Aluko.

Lycos Email has 10 MB of FREE storage space. http://mail.lycos.co.uk


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

ADO driver?

[Martijn Tonies]

Hi all,

Is there an ADO (NOT ADO.NET) driver for MySQL?

If so, where?


Thanks in advance.


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


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

RE: Mysql MAtch against query help

[electroteque]

Hehe I've also had a problem with searching for something like it's ,
anything with a single quote doesnt return anything, maybe try adding a
slash , C\+\+ ?? heheh maybe i'm wrong, it could be a limitation.

> -Original Message-
> From: David Taiwo [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 13, 2004 9:32 PM
> To: [EMAIL PROTECTED]
> Subject: Mysql MAtch against query help
>
>
> I have a query that searches my database for people
> with C++ on their resume .
>
> Query
> =
> select *  ,match(Res_resume) AGAINST ('C++') as
> kewyordscore from member,memberprofile,resume left
> join stateprovince on stateid = Res_state
>   where mem_id = mempf_memid and
>match(Res_resume) AGAINST ('+C++' IN BOOLEAN MODE)
>
> I have the ft_min_word_len set to 2 , but for some
> reason , it still does not return a record when i
> search for C++. Is there a way to escape the ++ when i
> do a search , or is there something i am not doing
> right.
> The query works when i do a search for 'PR' or 'P*' .
> so i am sure that i reindexed my database.
> I am using Mysql version 4.1.1 on windows.
> Any help owuld be great ... thx
>
> Dave
>
>
>
>
>
>
>
> __
> Do you Yahoo!?
> New DSL Internet Access from SBC & Yahoo!
> http://sbc.yahoo.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


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

RE: turning off binary logging

[Russell Horn]

> I just found that mysql 4.0.18 is doing binary logging. How can I
> turn this off?

> I do not see a nead for this, plus I fear that
> it might fill up the file system, plus I think it is reducing performance.
>

The binary log is primarily there to let you restore data. Say you backup
every night at 04:00 and your database crashes at 15:00 you can restore from
your backup but what about all those changes in the 11 hours after the
backup was made?

Instead you can use mysqlbinlog to run all the changes made in those 11
hours and get your data back to pretty much exactly where it was before the
crash.

According to the manual, the performance hit is about 1% - that's peanuts in
exchange for the ability to recover your data.

The other function of the binary log is to store statements that will be
replicated on slave servers. That might not be relevant to you at the
moment, but will perhaps be something you need later?

If you really want to disable it, read the manual page at:

http://dev.mysql.com/doc/mysql/en/Binary_log.html

Russell.


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

Mysql MAtch against query help

[David Taiwo]

I have a query that searches my database for people
with C++ on their resume . 

Query
=
select *  ,match(Res_resume) AGAINST ('C++') as
kewyordscore from member,memberprofile,resume left
join stateprovince on stateid = Res_state
where mem_id = mempf_memid and
 match(Res_resume) AGAINST ('+C++' IN BOOLEAN MODE)

I have the ft_min_word_len set to 2 , but for some
reason , it still does not return a record when i
search for C++. Is there a way to escape the ++ when i
do a search , or is there something i am not doing
right.
The query works when i do a search for 'PR' or 'P*' .
so i am sure that i reindexed my database.
I am using Mysql version 4.1.1 on windows.
Any help owuld be great ... thx 

Dave







__
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

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

turning off binary logging

[Walter Andreas]

Hi there,

I just found that mysql 4.0.18 is doing binary logging. How can I turn this off?
I outcommented the line in my.cnf and restarted the server, but it is still creating
those binary loggs inside the data dir. I do not see a nead for this, plus I fear that
it might fill up the file system, plus I think it is reducing performance.

Thanx for any advice,

Andy
_
Der WEB.DE Virenschutz schuetzt Ihr Postfach vor dem Wurm Netsky.A-P!
Kostenfrei fuer alle FreeMail Nutzer. http://f.web.de/?mc=021157


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

Re: free software and open source

[Joao Miguel Ferreira]

On Sun, 2004-04-11 at 15:58, Saurabh Data wrote:
> Dear Users
> 
> Can anyone in your own words clarify the difference between "open source" 
> and "free software".

Go see the GNU.org site.

http://www.gnu.org/home.html

and also check this out

http://www.gnu.org/licenses/licenses.html

(If you don't know GNU is the name of the FSF (Free Software Foundation)
project for free and/or open-source software, they are the ones behind
great lot of the importance of free software today)

Regards

jmf



> 
> Many Thanks
> 
> Saurabh Data
> 
> 
> 
> ___
> 
> Saurabh Data
> School of Computing
> University of Leeds
> Leeds LS2 9JT
> U.K.
> 
> one who seeketh , will findeth and all door shall open - james Allen
> ___
> 
> _
> It's fast, it's easy and it's free. Get MSN Messenger today! 
> http://www.msn.co.uk/messenger
> 


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

Re: compiling mysql on a pentium

[Victor Medina]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi!

CFLAGS="-O3 -march=i686 -mcpu=i686 -funroll-loops -fomit-frame-pointer
- -fno-exceptions -fno-ftti -felide-constructors"
CXXFLAGS the same as above.

Check out "Securing and Optimizing Linux" in the tldp.org i think is
just what you nedd.
Best Regards!



- --

~ |...|
~ |  _    _|Victor Medina M   |
~ |\ \ \| |  _ \ / \   |Linux - Java - MySQL  |
~ | \ \ \  _| | |_) / _ \  |Dpto. Sistemas - Ferreteria EPA   |
~ | / / / |___|  __/ ___ \ |[EMAIL PROTECTED]  |
~ |/_/_/|_|_| /_/   \_\|Tel: +58-241-8507325 - ext. 325   |
~ ||Cel: +58-412-8859934  |
~ ||geek by nature - linux by choice  |
~ |...|
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFAe7068WJSBCrOXJ4RAvJCAJ0QIctA0Ov/gPzQww/hE1SFvphNMQCfYPl/
EJLxkX28Kc9Q67z29fHqJx4=
=6vBO
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to protect MySQL server from intruders ?

[Victor Medina]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi!

If the application is _NOT_ local you can restrict complete acces to one
and only one machine, host, or network mask using the "grant" statement.
Also changing mysql default port is not a bad idea, at least it make it
~ more dificult to guess
Best Regards!

- --

~ |...|
~ |  _    _|Victor Medina M   |
~ |\ \ \| |  _ \ / \   |Linux - Java - MySQL  |
~ | \ \ \  _| | |_) / _ \  |Dpto. Sistemas - Ferreteria EPA   |
~ | / / / |___|  __/ ___ \ |[EMAIL PROTECTED]  |
~ |/_/_/|_|_| /_/   \_\|Tel: +58-241-8507325 - ext. 325   |
~ ||Cel: +58-412-8859934  |
~ ||geek by nature - linux by choice  |
~ |...|
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFAe7xN8WJSBCrOXJ4RAieIAKDRprMb6XdpL0gknILE1iwyusf1VACgvO7K
SLbas9lteCXTAv2yVCBSeqk=
=Z6Vj
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Space in multi-byte character set

[Hirofumi Fujiwara]

Here are charcodes of full-width space (IDSP,Ideographic Space)
of sjis, ujis and utf8:

  sjis   81 40
  ujis   A1 A1
  utf8   E3 80 80

String processing functions TRIM, LTRIM, and RTRIM don't recognize
full-width space in a string.  They don't trim the full-width space
and leave it in string.

It seems that the functions don't process Japanese full-width space
as space.


Hirofumi Fujiwara (Tokyo JAPAN)  enjoy JAVA and Puzzle World
[EMAIL PROTECTED]http://www.pro.or.jp/~fuji/index-eng.html
[EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/


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

sql bench problems

[Yonah Russ]

I've been trying to compile a mysql server optimized for a zeon 
processor and a specific application- I'm compiling with icc.

the problem is with the sql benchmarks- the perl regexp for making the 
detailed report of the benchmark doesn't match the output from the 
benchmarks so it doesn't create the report but instead says that 
everything failed-

Has anyone had this problem? I could futz around with the regexp but if 
there is a more correct solution, I'd rather do that.

here is a sample output line:

Time for alter_table_drop (91): 19 wallclock secs ( 0.02 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.02 CPU)

here is the regexp:

/^(estimated |)time (to|for) ([^\s:]*)\s*\((\d*)(:\d*)*\)[^:]*:\s*([\d.]+) .*secs \(\s*([^\s]*) usr\s*\+*\s*([^\s]*) sys.*=\s+([\d.]*)\s+cpu/i

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

ORDER BY alias

[Danielb]

I\'m trying to order by an alias in a multi table SELECT statement(Note I\'ve cut the 
statement down a bit to make it more readable):

SELECT kills.PlayerID, player.DeathsPerMinute, SUM(kills.Kills) AS Total FROM 
playerweaponkills AS kills, ETPlayerSummary AS player WHERE 
kills.PlayerID=player.PlayerID AND kills.WeaponID=17 GROUP BY kills.PlayerID ORDER BY 
(Total*(1-player.DeathsPerMinute)) DESC LIMIT 5

When I run this I get the error:
#1054 - Unknown column \'Total\' in \'order clause\'

I take it the problem is that MySQL is unable to resolve the alias Total when its used 
in this way with player.DeathsPerMinute? Is there any way I can prefix Total to help 
it be resolved? The statement works fine with ordering by either Total or 
(1-player.DeathsPerMinute) its when you try and combine them in the above statement it 
freaks out.

Any ideas? I ideal want to order by:
(Total*(1-player.DeathsPerMinute))

Cheers,

Daniel

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

Re: compiling mysql on a pentium

[Yonah Russ]

There does seem to be a bug in icc- I've posted on intel's message 
boards and they've confirmed a problem. There is a work around - here is 
the post:

Hi Yonah,
I can see the same problem on my system.
I will create a support issue for you and will let you when
this get fixed.
I found that if you take out libmysql.o from libmysqlclient.a and then 
link it as -

icpc -O3 -DDBUG_OFF -O3 -ipo -axWN -march=pentium4 -mcpu=pentium4 
-fno-implicit-templates -fno-exceptions
-fno-rtti -rdynamic -o mysql mysql.o readline.o sql_string.o 
completion_hash.o -lreadline -lncurses
../libmysql/.libs/libmysqlclient.a ../libmysql/libmysql.o -lz -lcrypt 
-lnsl -lm

it works fine.

you can try this as a workaround for your problem, till it get fixed.
The problem is you have to include ../libmysql/libmysql.o explicitly 
whereever
../libmysql/.libs/libmysqlclient.a is linked.

HTH,
CP
in addition- I got these helpful instructions from the folks on the 
mysql-packagers list- I specifically asked about 4.0.18 so they should 
work- I have successfully compiled 4.1.1 several times and the binaries 
passed the tests although I'm having trouble with the benchmark suite.

good luck
yonah

That really sounds like an icc bug. Here's the line we currently use for 
our binaries on icc:

CFLAGS="-O3 -unroll2 -ip -mp -no-gcc -restrict" CC=icc CXX=icc
CXXFLAGS="-O3 -unroll2 -ip -mp -no-gcc -restrict" ./configure
- --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data
- --libexecdir=/usr/local/mysql/bin --with-extra-charsets=complex
- --enable-thread-safe-client --enable-local-infile --enable-assembler
- --disable-shared --with-client-ldflags=-all-static
- --with-mysqld-ldflags=-all-static --with-readline --with-embedded-server
- --with-innodb
You may be able to use higher optimization levels (e.g. by removing "-mp", 
which will however cause some loss in floatingpoint accuracy - some of the 
test suite tests will fail). I assume the "--no-gcc" is key here.

Bye,
	LenZ
- -- 
Lenz Grimmer <[EMAIL PROTECTED]>
Senior Production Engineer



Walter Andreas wrote:

Hi there,

how to compile mysql 4.0.18 on a pentium for best performance? I searched the net now 
for 2 days and found lots of hints on compiling with icc and pgcc, but it looks to me 
that icc is not working with mysql 4.0.18 and pgcc is out of date (maybe gcc already 
catched up with pgcc?).
Setting compiler flags is also a miraqle for me. This is going to be a production 
server, so it should be really stable and not the trade for performance.
Can anybody advice a configure command with compiler settings for a p4 machine with 1G ram?

Thank you for your advice,

Andy
_
Der WEB.DE Virenschutz schuetzt Ihr Postfach vor dem Wurm Netsky.A-P!
Kostenfrei fuer alle FreeMail Nutzer. http://f.web.de/?mc=021157
 

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

Finding configure command after installation

[Walter Andreas]

Hi there,

I just recompiled mysql and I am wondering if there is something like in php 
(phpinfo();) where you can see the configure command after the db is installed.
It would just be nice to have that in a later time, or even to make sure that the new 
version has replaced the old one.

thanx for and advice on that,

Andy
___
... and the winner is... WEB.DE FreeMail! - Deutschlands beste E-Mail
ist zum 39. Mal Testsieger (PC Praxis 03/04) http://f.web.de/?mc=021191


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