punctuation limitation in fulltext search

[Daniel Rossi]

Can someone please tell me if my problem with the punctuation in a fulltext search is 
a bug or limitation? Ultimately the users of the system i built do not want to have to 
try using the + search then trying the * search for all the words which will 
obviouslly return a lot of results, is there a work around please let me know thanks.


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

Re: month by month count

[Terence]

select count(*), monthname(yourfield)
from yourtable
where year = '2003'
group by monthname(yourfield)

- Original Message - 
From: "Scott Haneda" <[EMAIL PROTECTED]>
To: "MySql" <[EMAIL PROTECTED]>
Sent: Wednesday, August 20, 2003 12:38 PM
Subject: month by month count


I need to generate record counts for each month in a year, just one year at
a time, but a full year at a glance.

I am thinking 12 hits to the DB is perhaps not the most idea way to do it,
is there a way to rip this out in one go?
-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com   Fax: 313.557.5052
[EMAIL PROTECTED]Novato, CA U.S.A.


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

month by month count

[Scott Haneda]

I need to generate record counts for each month in a year, just one year at
a time, but a full year at a glance.

I am thinking 12 hits to the DB is perhaps not the most idea way to do it,
is there a way to rip this out in one go?
-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com   Fax: 313.557.5052
[EMAIL PROTECTED]Novato, CA U.S.A.


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

RE: marking all records based on a text file

[Michael S. Fischer]

The MySQL client won't be able to do that on its own; you'll need to
write a script in your favourite scripting language to do that.

--Michael

> -Original Message-
> From: Scott Haneda [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, August 19, 2003 9:00 PM
> To: MySql
> Subject: marking all records based on a text file
> 
> 
> I have a large list of email addresses that I have found are 
> undeliverable to users, I want to mark a field in mysql to 1 
> where there is this email address, how can I do this in one 
> go from a \r file.
> 
> Something like
> update mytable set bad_email = 1 where email_address =
> readFile(my_return_sep_file)
> 
> 
> -- 
> -
> Scott HanedaTel: 415.898.2602
> http://www.newgeo.com   Fax: 313.557.5052
> [EMAIL PROTECTED]Novato, CA U.S.A.
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
> 
> 


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

database reverted to 18hr old state after power outage

[Brian Chan]

hi,

the power outage in ontario caused my server to crash.  after i booted up
again it was as if the past 18 hrs never happened.  all the changes to the
database were gone.  is there anyway to recover the missing data?  it's
probably too late now since new data has been put into the database since
then...

I now also have errors with my database.  I run myisamchk
/var/lib/mysql/*/*.MYI -e -o -r
and I get many errors such as:
Found block that points outside data file at 9464
or
Found block with too small length at 980; Skipped

Is there anyway I can resolve these errors?

Thanks,
Brian.

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

marking all records based on a text file

[Scott Haneda]

I have a large list of email addresses that I have found are undeliverable
to users, I want to mark a field in mysql to 1 where there is this email
address, how can I do this in one go from a \r file.

Something like
update mytable set bad_email = 1 where email_address =
readFile(my_return_sep_file)


-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com   Fax: 313.557.5052
[EMAIL PROTECTED]Novato, CA U.S.A.


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

Auto-Confirmation

[Quest Software]

Thank you for submitting your request to Quest Software Technical Support.  We are 
unable to process your request because our records indicate that you are not 
registered for Technical Support.

To register, logon to our Supportlink web site http://www.quest.com/support , and  
self-register from the web.  Or, you can phone Quest at one of the numbers below to 
register.

Once you have registered, you can submit your request within Supportlink, by email at 
[EMAIL PROTECTED], by phone at one of the numbers below.

Please do not reply to this email.

If this is an URGENT matter please contact Quest Technical Support via telephone at 
one of the numbers listed below.

Thank you,

Quest Software Technical Support
www.quest.com/support

Quest Software Technical Support - Canada   902.442.5700
Quest Software Technical Support - United Kingdom  44.1628.601007
Quest Software Technical Support - United States  949.754.8000
>  Original Message
>  From: "[EMAIL PROTECTED]" [mailto:[EMAIL PROTECTED]
>  Sent: Tue, 19 Aug 2003 23:07:39 --0400
>  To: [EMAIL PROTECTED]
>  Subject: Re: Thank you!
>  
>  

RE: Oracle DBA here looking for advice on MySQL ....

[Brian Austin]

Just look at amazon.  There are more books popping up all the time.

The manual with user comments is the best.
http://www.mysql.com/doc/en/

Paul DuBois wrote a good book that helped me get started on a few things.
This one is a newer one I think.
http://www.amazon.com/exec/obidos/tg/detail/-/0735709211/ref=pd_sim_books_1/
103-1999417-1493463?v=glance&s=books

or for 4.0 by Ian Gilfillan
http://www.amazon.com/exec/obidos/tg/detail/-/0782141625/ref=pd_sbs_b_1/103-
1999417-1493463?v=glance&s=books


I've noticed a lot of Oracle people don't like MySQL for one reason or
another.  I don't understand why that is, but in my opion MySQL is easier,
faster and more fun to use.  Granted there are not as many features
available in MySQL, but having the ability to create my own solutions to
problems is what I enjoy most about it.  That's the reason I became a
programmer, not an app user.


Brian

-Original Message-
From: Andy Jackman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 19, 2003 2:29 AM
To: Johnson, Michael
Cc: MySQL Users
Subject: Re: Oracle DBA here looking for advice on MySQL 


I've used a lot of Oracle, some MS Access and I'm newish to MySQL. I
found it easy to write an abstraction layer for Ms Access and Oracle
despite their different approaches to some important things. I find
MySQL very sparse by comparison and I spend more time working round the
db than working with it. Unlike Oracle the richness and integrity of
language is simply missing - these people have lived without something
as useful as sub-queries for a long time. (The argument being that speed
and data integrity are all-important). It's more a file system than a
relational database. I know you asked about books rather than a
comparison of the products, but the software philosophy is reflected in
the documentation. If someone else pays you to be an Oracle Dba then I
bet you have at least a 10 foot shelf of comprehensive documentation.
This list is about as good as it gets (see your previous response).
There is a PDF copy of the manual somewhere and setting up MySql was
accompilshed by a colleague who wouldn't have known where to start with
Oracle, so it has that in its favour.

So, if you're thinking of migrating, think carefully! If I could get
Oracle to give me a sensible price (say USD 1000) to sell their db with
my product I would be out of here so fast. So far with mySQL i've
written my own database for a particular (simple) structure that it
wouldn't handle to my satisfaction; I've written my own date/time
routines to calculate things like seconds between 2 datetimes (despite a
wealth of datatime functions, this one isn't available unless you
convert to 'Unix' dates which expire in 2036) and I've written functions
to handle the fact that in 'C' all data is returned as strings rather
than as native data types. Sigh.
- Andy

"Johnson, Michael" wrote:
>
> What is the best book on MySQL with regard
> to its Architecture and how it starts up, shutdowns,
> processes queries, rolls back data, etc etc. ?
>
> I am not looking for a SQL book here.
>
> What is the best My SQL book you have read ?
>
> Thank you in advance.
>
> Mike
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

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




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

Re: PHP mysql_connect randomly failing

[Jon Drukman]

unix domain.

Michael S. Fischer wrote:
Are you connecting through a TCP or a UNIX domain socket?

--Michael


-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Jon Drukman
Sent: Tuesday, August 19, 2003 2:36 PM
To: [EMAIL PROTECTED]
Subject: PHP mysql_connect randomly failing
I've got a library of PHP code whose first line is a mysql_connect 
statement, like this:

$dbh=mysql_connect() or die("mysql connect failed: $php_errmsg");

Approximately 1% of the time it just fails, for no stated reason:

Warning: mysql_connect() [http://www.php.net/function.mysql-connect]: 
in /var/httpd/htdocs/pi/pi.php on line 3
mysql connect failed:


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

Re: JOINs - More Info

[Jason Soza]

So my problem below still exists, but I think I have a better way (and SQL example) to 
describe what's going on. Consider the following SQL:

SELECT gc_info.award,amends.amend,payments.paid
FROM gc_info
LEFT JOIN amends ON gc_info.gc_number=amends.gc_number
LEFT JOIN payments ON amends.gc_number = payments.gc_number
WHERE gc_info.gc_number = "06-8479"

This gives me 3 columns, award, amend, and paid.

There is 1 record in the gc_info table for 06-8479, 1 record in amends for 06-8479, 
and there are 12 records in payments.

The result is 12 columns, each with the same award and amend record repeated for each 
payment record.

How can I arrange the SQL so that the award and amend show up in the first record, 
then are NULL for the remaining. My problem is resulting from doing a SUM(amend) and 
getting a result that's 12x what it should be - if I could eliminate the repeated 
values, I would be okay.

Thanks again! 

Jason
- Original Message -
From: Jason Soza <[EMAIL PROTECTED]>
Date: Tuesday, August 19, 2003 11:18 am
Subject: JOINs

> I may be trying to do too much in a single query here, but it 
> would be nice if I could get it working! Apparently, the version 
> of MySQL used by my work does not support the WITH ROLLUP feature, 
> otherwise I think that would work.
> 
> What I have are 3 tables: gc_info, amends, payments.
> 
> 'gc_info' contains a number that represents the original award 
> given to a contractor. If there are any increases, these are kept 
> in 'amends', and all payments are kept in 'payments'.
> 
> What I need to do is get three numbers: current total award 
> (award+amendments), current total paid (SUM(payments)), and 
> current balance ((awards + SUM(amendments)) - SUM(payments)).
> 
> This almost works with the below query, except that the where 
> there should only be 1 amendment, 11 more show up, or one for each 
> of the 12 payments made to the contractor:
> 
> SELECT org_name,FORMAT(award+amend,2) AS current_total, 
> FORMAT((award+SUM(amend))-SUM(paid),2) AS balance, 
> FORMAT(SUM(paid),2) AS total_paid FROM contact_info LEFT JOIN 
> gc_info ON contact_info.id=gc_info.contact_id LEFT JOIN amends ON 
> gc_info.gc_number=amends.gc_number LEFT JOIN payments ON 
> gc_info.gc_number = payments.gc_number WHERE gc_info.gc_number = 
> "06-8479" GROUP BY gc_info.gc_number
> 
> For example, on a contract with an original award of 168,000, one 
> amendment of 168,000, and 12 payments of 14,000, this gives me:
> 
> |org_name|current_total   |balance |total_paid 
> |
> |Org Name|336,000.00 (correct)|2,016,000.00 (incorrect)|168,000.00 
> (correct)|
> So in FORMAT((award+SUM(amend))-SUM(paid),2), SUM(amend) is adding 
> up 12 instances of amend instead of 1.
> 
> Any advice would be helpful. I've read through the JOIN section in 
> the MySQL manual, tried different JOINs, GROUP BYs, etc. but 
> cannot figure this one out. 
> 
> Thank you very much,
> Jason


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

PROBLEM when installing mysql!

[hypet.com freewawe]

Dear you!
 
I try to install MySQL Server and Client 4.0.14b.
I will use this together with the program ZOBB.
(http://www.z-wp.com/zobb/index.php)
 
This ZOBB need to leg SQL be placed at C:\ but I selected D:\
Then I install at C:\ but get problem.
 
Now I can not UNINSTALL and I can not DELETE this directory!
AND I can not use ZOBB!
 
Please tell me how to delete EVERYTHING about SQL so I can be able to
Install one more time.
 
I are not alloved to use START/ ……./ Edit or Delete new software
I can see that MySQL Server and Client 4.0.14b. is still there 
But I can not UNINSTALL
 
Plese help
 
Best regards
Knut Hans Røed
Hypet Data AS
www.hypet.com  
[EMAIL PROTECTED]
 
 
 
 

Re: Details

[ListManager-confirm-7510695I]

See the attached file for details
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: JOINs

[Jason Soza]

Patrick,

Thanks! But - same result... It was worth a shot, however I still got 12 instances of 
'amend' instead of 1.

If I can't work it out, I'll just split up this one query into two and see about using 
PHP to do the math. Not something I wanted to do, but it should work just as well.

Thanks again,

Jason

- Original Message -
From: Patrick Shoaf <[EMAIL PROTECTED]>
Date: Tuesday, August 19, 2003 12:07 pm
Subject: Re: JOINs

> At 03:18 PM 8/19/2003, you wrote:
> >I may be trying to do too much in a single query here, but it 
> would be 
> >nice if I could get it working! Apparently, the version of MySQL 
> used by 
> >my work does not support the WITH ROLLUP feature, otherwise I 
> think that 
> >would work.
> >
> >What I have are 3 tables: gc_info, amends, payments.
> >
> >'gc_info' contains a number that represents the original award 
> given to a 
> >contractor. If there are any increases, these are kept in 
> 'amends', and 
> >all payments are kept in 'payments'.
> >
> >What I need to do is get three numbers: current total award 
> >(award+amendments), current total paid (SUM(payments)), and 
> current 
> >balance ((awards + SUM(amendments)) - SUM(payments)).
> >
> >This almost works with the below query, except that the where 
> there should 
> >only be 1 amendment, 11 more show up, or one for each of the 12 
> payments 
> >made to the contractor:
> >
> >SELECT org_name,FORMAT(award+amend,2) AS current_total, 
> >FORMAT((award+SUM(amend))-SUM(paid),2) AS balance, 
> FORMAT(SUM(paid),2) AS 
> >total_paid FROM contact_info LEFT JOIN gc_info ON 
> >contact_info.id=gc_info.contact_id LEFT JOIN amends ON 
> >gc_info.gc_number=amends.gc_number LEFT JOIN payments ON 
> gc_info.gc_number 
> >= payments.gc_number WHERE gc_info.gc_number = "06-8479" GROUP BY 
> >gc_info.gc_number
> >
> >For example, on a contract with an original award of 168,000, one 
> >amendment of 168,000, and 12 payments of 14,000, this gives me:
> >
> >|org_name|current_total   |balance 
> |total_paid  |
> >|Org Name|336,000.00 (correct)|2,016,000.00 
> (incorrect)|168,000.00 (correct)|
> >
> >So in FORMAT((award+SUM(amend))-SUM(paid),2), SUM(amend) is 
> adding up 12 
> >instances of amend instead of 1.
> >
> >Any advice would be helpful. I've read through the JOIN section 
> in the 
> >MySQL manual, tried different JOINs, GROUP BYs, etc. but cannot 
> figure 
> >this one out.
> >
> >Thank you very much,
> >Jason
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> Try the following (not sure why, but this is how I set my queries 
> up)
> SELECT org_name,FORMAT(award+amend,2) AS current_total, 
> FORMAT((award+SUM(amend))-SUM(paid),2) AS balance, 
> FORMAT(SUM(paid),2) AS 
> total_paid FROM contact_info LEFT JOIN gc_info ON 
> contact_info.id=gc_info.contact_id LEFT JOIN amends ON 
> contact_info.gc_number=amends.gc_number LEFT JOIN payments ON 
> contact_info.gc_number = payments.gc_number WHERE 
> contact_info.gc_number = 
> "06-8479" GROUP BY contact_info.gc_number
> 
> I always use the same field (from 1st table) when I do JOIN / 
> GROUP BY / 
> WHERE / etc.  This way I know I should always receive the same 
> results.
> I can't guarantee this, but try it
> 


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

RE: PHP mysql_connect randomly failing

[Michael S. Fischer]

Are you connecting through a TCP or a UNIX domain socket?

--Michael

> -Original Message-
> From: news [mailto:[EMAIL PROTECTED] On Behalf Of Jon Drukman
> Sent: Tuesday, August 19, 2003 2:36 PM
> To: [EMAIL PROTECTED]
> Subject: PHP mysql_connect randomly failing
> 
> 
> I've got a library of PHP code whose first line is a mysql_connect 
> statement, like this:
> 
> $dbh=mysql_connect() or die("mysql connect failed: $php_errmsg");
> 
> Approximately 1% of the time it just fails, for no stated reason:
> 
> Warning: mysql_connect() [http://www.php.net/function.mysql-connect]: 
> in /var/httpd/htdocs/pi/pi.php on line 3
> mysql connect failed:
> 
> Any ideas why this would be happening?  PHP is version 4.3.1 (same 
> results with the latest 4.3.3 release candidate), Mysql is 4.0.12
> 
> thanks
> -jsd-
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
> 
> 


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

RE: Simple error ... Im sure

[Allen Weeks]

If you are using a MySQL version allowing subselects, try this:

SELECT * FROM temp_hits WHERE url NOT IN(SELECT * FROM hits)

use parenthesis not brackets

hope it helps

Allen

-Original Message-
From: news [mailto:[EMAIL PROTECTED] Behalf Of Soren O'Neill
Sent: Tuesday, August 19, 2003 2:32 PM
To: [EMAIL PROTECTED]
Subject: Simple error ... Im sure



I keep getting a syntax error, when trying to run this query (perl script):

SELECT * FROM temp_hits WHERE url NOT IN SELECT * FROM hits

Ive tried with brackets around the last SELECT statement ... no better ...






(Essentially, the tables hits, new_hits and temp_hits hold the same columns,
what I want to do is extract any rows from new_hits, where the url field is
not found in the hits table...)


--
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: mysqldump of DB vs. individual tables

[Dan F]

Thanks for your reply! Very helpful.

Presumably, I can read about SET FOREIGN_KEY_CHECKS = 0; and it will 
only apply as long as the file is running, or I can turn it back on at 
the end of the whole process.

Out of curiosity, are you saying mysqldump of a whole DB does some sort 
of global lock and/or reads state at a given point in time?

Dan

Paul DuBois wrote:

At 8:37 -0500 8/18/03, Dan F wrote:

Folks,

I find it useful for browsing to dump each table of a DB, i.e. 
psuedo-code like

foreach table
 mysqldump --add-drop-table DB table > table
This produces a file per table, and that file has the DDL and data.

My question: does this per-table version capture all the information 
that doing a full-DB dump (as follows) would?

 mysqldump --add-drop-table DB > DB


Not necessarily, because you may have updates occuring to other tables,
while a given table is being dumped.  If the database is being used in
read-only fashion during the dump, the information should be the same
in both cases.
I tested, and noticed that foreign key constraints and indexes (in my 
InnoDB database) are appearing nicely in the per-table version. Is 
anything missing?


Shouldn't be.  If you have problems reloading the table due to the order
in which the InnoDB tables appear in the dump files, add
SET FOREIGN_KEY_CHECKS = 0;

to the beginning of the file before reloading it.

I'm using mysql "Ver 12.18 Distrib 4.0.12" (I assume 4.0.12) for 
Linux. Thanks for any thoughts.

Dan

--
Dan Frankowski[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: Specifying INNODB without configuration

[Paul DuBois]

At 16:36 -0400 8/19/03, [EMAIL PROTECTED] wrote:
Is it a bug or a feature that with the conditions

1.  INNODB is not configured in my.cnf
2.  Table type is specified as INNODB
that when you create the table there is no warning,
and no syntax error, and the table type silently
and Microsoft-like, defaults to MyISAM?
It is the documented behavior:

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

See just below the table that lists available table types.

In 4.1.1, the behavior will change such that a warning is
generated. You can see the warning message with SHOW WARNINGS.
Now, if you specify table type as TYPE=bullshit,
you get a nice (and expected) syntax error.
_justin

--
Justin Farnsworth
Eye Integrated Communications
321 South Evans - Suite 203
Greenville, NC 27858 | Tel: (252) 353-0722
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified?  http://www.mysql.com/certification/

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

Re: mysqldump of DB vs. individual tables

[Dan F]

Dan F wrote:

Thanks for your reply! Very helpful.

Presumably, I can read about SET FOREIGN_KEY_CHECKS = 0; and it will 
only apply as long as the file is running, or I can turn it back on at 
the end of the whole process.

Out of curiosity, are you saying mysqldump of a whole DB does some 
sort of global lock and/or reads state at a given point in time? 


Whoops, answered my own question: another one of these threads talked 
about mysqldump --single-transaction, and presumably that would only 
work properly if the whole DB was dumped at once.

FYI, my current app is used to dump and restore a non-production 
instance into CVS for purposes of quickly recreating a development 
environment. Thus, per-table dumping is sufficient for my needs.

Thanks again.

Dan



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

Re: Query question

[Patrick Shoaf]

Try

SELECT * FROM contact INNER JOIN contact_account ON 
contact.contact_id=contact_account.contact_id WHERE 
contact_account.account_id = 13



At 04:17 PM 8/19/2003, Jack Lauman wrote:
I have two tables, contact_account and contact.

contact_account has two fields: contact_id (pk) and account_id

contact has a PK of contact_id

I to select all the columns in contact where account_id=13

I tried:

SELECT * FROM (contacts INNER JOIN account on contact.contact_id =
account.account_id) WHERE account.account_id = 13;
One I get the query to work right I neet to write it to a file in CSV
format.
Any help would be appreciated.

Thanks,

Jack

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


Patrick J. Shoaf, Systems Engineer
[EMAIL PROTECTED]
Midmon Internet Services, LLC
100 Third Street
Charleroi, PA 15022
http://www.midmon.com
Phone: 724-483-2400 ext. 105
 or888-638-6963
Fax:   724-489-4386


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

Simple error ... Im sure

[Soren O'Neill]

I keep getting a syntax error, when trying to run this query (perl script):

SELECT * FROM temp_hits WHERE url NOT IN SELECT * FROM hits

Ive tried with brackets around the last SELECT statement ... no better ...






(Essentially, the tables hits, new_hits and temp_hits hold the same columns,
what I want to do is extract any rows from new_hits, where the url field is
not found in the hits table...)


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

PHP mysql_connect randomly failing

[Jon Drukman]

I've got a library of PHP code whose first line is a mysql_connect 
statement, like this:

$dbh=mysql_connect() or die("mysql connect failed: $php_errmsg");

Approximately 1% of the time it just fails, for no stated reason:

Warning: mysql_connect() [http://www.php.net/function.mysql-connect]: 
in /var/httpd/htdocs/pi/pi.php on line 3
mysql connect failed:

Any ideas why this would be happening?  PHP is version 4.3.1 (same 
results with the latest 4.3.3 release candidate), Mysql is 4.0.12

thanks
-jsd-


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

Re: MySQL and shared storage

[Colbey]

I'm sure there's gonna be some file locking issues.. If you just trying to
get some scalability, might want to look at replication instead, with the
SAN hosting 1 copy of the database for each server..



On Tue, 19 Aug 2003, Scott Pippin wrote:

> I would like to set up a round robin cluster with mysql.  The round robin would be 
> done through
the DNS to two different servers.  Each of those servers would have mysql installed 
and each
would reference the same data directory on our SAN.  Are there any problems with doing 
this?
>
> Thanks in advance
> Scott Pippin
> [EMAIL PROTECTED]
>
>

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

Re: "hard" sort table order

[Rajesh Kumar]

anders thoresson unknowingly asked us:
Is it possible to resort the rows in a table like in a query where using 
ORDER BY, but have to new sort order stored in the table structure?
If you use PhpMyAdmin (latest version), follow these steps:

1. Select your table by clicking on the left.
2. Choose 'Operations' from the top.
3. And the first bullet, you will find something that says: Alter table 
by _ (singly).

4. Just choose the appropriate field and click 'Go'

Ofcourse this feature will be more robust, if you constructed your own 
query.

--
Think to think more, work to work more.
__
Meet the guy at http://www.meetRajesh.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Mast-Master Replication

[Sanya Shaik]

I am unable to find any information about master-master replication. I 
need to replicate 1 mysql server over to other as a standby master 
server.
 
Any help ? Any ideas at all ?




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

Re: mysqlbinlog wrong output

[Jan Josefowicz]

Hi,


This version of mysqlbinlog can work properly only with 3.23 server binary logs.
is there a mysqlbinlog version that matches to MySQL 4.0.xx?

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

Specifying INNODB without configuration

[jef]

Is it a bug or a feature that with the conditions

1.  INNODB is not configured in my.cnf
2.  Table type is specified as INNODB

that when you create the table there is no warning,
and no syntax error, and the table type silently
and Microsoft-like, defaults to MyISAM?

Now, if you specify table type as TYPE=bullshit,
you get a nice (and expected) syntax error.

_justin

-- 
Justin Farnsworth
Eye Integrated Communications
321 South Evans - Suite 203
Greenville, NC 27858 | Tel: (252) 353-0722

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

Re: Query question

[Roger Baklund]

* Jack Lauman 
> I have two tables, contact_account and contact.
> 
> contact_account has two fields: contact_id (pk) and account_id
> 
> contact has a PK of contact_id
> 
> I to select all the columns in contact where account_id=13
> 
> I tried:
> 
> SELECT * FROM (contacts INNER JOIN account on contact.contact_id =
> account.account_id) WHERE account.account_id = 13;

SELECT * FROM contacts 
  INNER JOIN account ON 
contact.contact_id = account.contact_id
  WHERE account.account_id = 13;

> One I get the query to work right I neet to write it to a file in CSV
> format.

See the SELECT ... INTO OUTFILE:

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

-- 
Roger

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

Re: "hard" sort table order

[Dan Nelson]

In the last episode (Aug 19), anders thoresson said:
> Is it possible to resort the rows in a table like in a query where using 
> ORDER BY, but have to new sort order stored in the table structure?

CREATE TABLE newtable AS SELECT * FROM oldtable ORDER BY myfield

newtable will have its records physically sorted.

You can also use myisamchk -R on a table to force the records to be
physically sorted by a particular index.  This only works on MyISAM
tables.

-- 
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: "hard" sort table order

[Yves Goergen]

ALTER TABLE table ORDER BY col

--from the manual--
ORDER BY allows you to create the new table with the rows in a specific order. Note 
that the table will not remain in this order after inserts and deletes. In some cases, 
it may make sorting easier for MySQL if the table is in order by the column that you 
wish to order it by later. This option is mainly useful when you know that you are 
mostly going to query the rows in a certain order; by using this option after big 
changes to the table, you may be able to get higher performance. 

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

 
-Ursprüngliche Nachricht- 
Von: "anders thoresson" <[EMAIL PROTECTED]>
An: <[EMAIL PROTECTED]>
Gesendet: Dienstag, 19. August 2003 22:06
Betreff: "hard" sort table order


> Is it possible to resort the rows in a table like in a query where using 
> ORDER BY, but have to new sort order stored in the table structure?
> 
> -- 
> anders thoresson
> 
> -- 
> 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: Query question

[Keith C. Ivey]

On 19 Aug 2003 at 13:17, Jack Lauman wrote:

> I have two tables, contact_account and contact.
> 
> contact_account has two fields: contact_id (pk) and account_id
> 
> contact has a PK of contact_id
> 
> I to select all the columns in contact where account_id=13
> 
> I tried:
> 
> SELECT * FROM (contacts INNER JOIN account on contact.contact_id =
> account.account_id) WHERE account.account_id = 13;

If you want help, you need to provide the exact queries you're using 
(copied and pasted, not retyped) and the exact error messages you're 
getting.  If you're not getting an error message, say what result you 
got and how it differs from what you expected.

In this case, you keep changing the names of your tables (contact/
contacts, contact_account/account), so we can't know whether that's 
the problem or just a distraction.  Also, you're joining on 
contact_id in one table and account_id in the other; presumably you 
want contact_id in both.


-- 
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org


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

MySQL and shared storage

[Scott Pippin]

I would like to set up a round robin cluster with mysql.  The round robin would be 
done through the DNS to two different servers.  Each of those servers would have mysql 
installed and each would reference the same data directory on our SAN.  Are there any 
problems with doing this?
 
Thanks in advance
Scott Pippin
[EMAIL PROTECTED]

Query question

[Jack Lauman]

I have two tables, contact_account and contact.

contact_account has two fields: contact_id (pk) and account_id

contact has a PK of contact_id

I to select all the columns in contact where account_id=13

I tried:

SELECT * FROM (contacts INNER JOIN account on contact.contact_id =
account.account_id) WHERE account.account_id = 13;

One I get the query to work right I neet to write it to a file in CSV
format.

Any help would be appreciated.

Thanks,

Jack


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

"hard" sort table order

[anders thoresson]

Is it possible to resort the rows in a table like in a query where using 
ORDER BY, but have to new sort order stored in the table structure?

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

Re: JOINs

[Patrick Shoaf]

At 03:18 PM 8/19/2003, you wrote:
I may be trying to do too much in a single query here, but it would be 
nice if I could get it working! Apparently, the version of MySQL used by 
my work does not support the WITH ROLLUP feature, otherwise I think that 
would work.

What I have are 3 tables: gc_info, amends, payments.

'gc_info' contains a number that represents the original award given to a 
contractor. If there are any increases, these are kept in 'amends', and 
all payments are kept in 'payments'.

What I need to do is get three numbers: current total award 
(award+amendments), current total paid (SUM(payments)), and current 
balance ((awards + SUM(amendments)) - SUM(payments)).

This almost works with the below query, except that the where there should 
only be 1 amendment, 11 more show up, or one for each of the 12 payments 
made to the contractor:

SELECT org_name,FORMAT(award+amend,2) AS current_total, 
FORMAT((award+SUM(amend))-SUM(paid),2) AS balance, FORMAT(SUM(paid),2) AS 
total_paid FROM contact_info LEFT JOIN gc_info ON 
contact_info.id=gc_info.contact_id LEFT JOIN amends ON 
gc_info.gc_number=amends.gc_number LEFT JOIN payments ON gc_info.gc_number 
= payments.gc_number WHERE gc_info.gc_number = "06-8479" GROUP BY 
gc_info.gc_number

For example, on a contract with an original award of 168,000, one 
amendment of 168,000, and 12 payments of 14,000, this gives me:

|org_name|current_total   |balance |total_paid  |
|Org Name|336,000.00 (correct)|2,016,000.00 (incorrect)|168,000.00 (correct)|
So in FORMAT((award+SUM(amend))-SUM(paid),2), SUM(amend) is adding up 12 
instances of amend instead of 1.

Any advice would be helpful. I've read through the JOIN section in the 
MySQL manual, tried different JOINs, GROUP BYs, etc. but cannot figure 
this one out.

Thank you very much,
Jason
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Try the following (not sure why, but this is how I set my queries up)

SELECT org_name,FORMAT(award+amend,2) AS current_total, 
FORMAT((award+SUM(amend))-SUM(paid),2) AS balance, FORMAT(SUM(paid),2) AS 
total_paid FROM contact_info LEFT JOIN gc_info ON 
contact_info.id=gc_info.contact_id LEFT JOIN amends ON 
contact_info.gc_number=amends.gc_number LEFT JOIN payments ON 
contact_info.gc_number = payments.gc_number WHERE contact_info.gc_number = 
"06-8479" GROUP BY contact_info.gc_number

I always use the same field (from 1st table) when I do JOIN / GROUP BY / 
WHERE / etc.  This way I know I should always receive the same results.

I can't guarantee this, but try it

Patrick J. Shoaf, Systems Engineer
[EMAIL PROTECTED]
Midmon Internet Services, LLC
100 Third Street
Charleroi, PA 15022
http://www.midmon.com
Phone: 724-483-2400 ext. 105
 or888-638-6963
Fax:   724-489-4386


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

Re: Server to Server Connection

[Rajesh Kumar]

Thomas Deliduka unknowingly asked us:
and then it ran the mysql_select_db which selected the wrong table. 
Please re-read, re-phrase. I have an uncomfortable feeling when people 
use the wrong function for the wrong purpose.

I'm an idiot, I should have looked for this before.
Think to think more.

I recoded it to use my DB API that I created and it works perfectly now.  I
wasted 4 hours on that. I kept saying to myself, "It's something simple
you're not looking at." and there it was.
Perfect. You just let me revise a programmer's usual cries.

--
Think to think more, work to work more.
__
Meet the guy at http://www.meetRajesh.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

JOINs

[Jason Soza]

I may be trying to do too much in a single query here, but it would be nice if I could 
get it working! Apparently, the version of MySQL used by my work does not support the 
WITH ROLLUP feature, otherwise I think that would work.

What I have are 3 tables: gc_info, amends, payments.

'gc_info' contains a number that represents the original award given to a contractor. 
If there are any increases, these are kept in 'amends', and all payments are kept in 
'payments'.

What I need to do is get three numbers: current total award (award+amendments), 
current total paid (SUM(payments)), and current balance ((awards + SUM(amendments)) - 
SUM(payments)).

This almost works with the below query, except that the where there should only be 1 
amendment, 11 more show up, or one for each of the 12 payments made to the contractor:

SELECT org_name,FORMAT(award+amend,2) AS current_total, 
FORMAT((award+SUM(amend))-SUM(paid),2) AS balance, FORMAT(SUM(paid),2) AS total_paid 
FROM contact_info LEFT JOIN gc_info ON contact_info.id=gc_info.contact_id LEFT JOIN 
amends ON gc_info.gc_number=amends.gc_number LEFT JOIN payments ON gc_info.gc_number = 
payments.gc_number WHERE gc_info.gc_number = "06-8479" GROUP BY gc_info.gc_number

For example, on a contract with an original award of 168,000, one amendment of 
168,000, and 12 payments of 14,000, this gives me:

|org_name|current_total   |balance |total_paid  |
|Org Name|336,000.00 (correct)|2,016,000.00 (incorrect)|168,000.00 (correct)|

So in FORMAT((award+SUM(amend))-SUM(paid),2), SUM(amend) is adding up 12 instances of 
amend instead of 1.

Any advice would be helpful. I've read through the JOIN section in the MySQL manual, 
tried different JOINs, GROUP BYs, etc. but cannot figure this one out. 

Thank you very much,
Jason


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

Re: Replication Binlog Corruption - REPOST

[Jason McCormick]

> Please don't take this the wrong way, but mission-critical problems
> should be sent thru MySQL's official support channels.  Otherwise
> you're at the mercy of a bunch of volunteers...

I don't. :)  I use a lot of free/open software.  I've found that the 
volunteers are often better then paid support - however I've never used 
MySQL's support which I've heard is excellent.  If this becomes 
problematic or more frequent I'll probably avail myself to that.

> Had the machine crashed and restarted recently?

No.  The machine has not had any problems.  I checked for any disk 
anomolies but since this has occurred on 3 separate machines, I ruled 
out hardware problem pretty quickly.

Thanks,
Jason


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

[kapper.net #16695] Re: Another Replication Problem

[info via RT]

Hallo User,
diese eMail wurde automatisch als Antwort auf Deine Nachricht generiert und bestaetigt 
Dir die Uebernahme in unser Bearbeitungssystem.
Wir werden schnellstmoeglich antworten. Bitte ein wenig Geduld, wir vergessen 
niemanden. Solltest Du Information sofort benoetigen, rufe uns bitte an.

Bitte behalte diese Ticket-ID im Betreff der eMail bei:
 [kapper.net #16695]
nur so ist unsere Korrespondenz eindeutig für alle beteiligten nachvollziehbar.
   Danke für Dein Interesse,
   die kapper.net::verwaltung ;-)
   [EMAIL PROTECTED]

-

Hi User,
this email is an automatically generated reply that confirms the receipt of your 
mesage and tells you we're already working on your request now.
We will respond ASAP, please give us a little time. If you need your information right 
now, please call us and let us know.

Please do also keep this messages Ticket-ID in the subject for further emails:
 [kapper.net #16695]
only this makes sure our communication is as accurate as possible.
   Thank you for talking to us,
   the kapper.net::administration ;-)
   [EMAIL PROTECTED]

[...deutsche version am beginn...]

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

[kapper.net #16695] Re: Another Replication Problem

[general]

Hallo User,
diese eMail wurde automatisch als Antwort auf Deine Nachricht generiert und bestaetigt 
Dir die Uebernahme in unser Bearbeitungssystem.
Wir werden schnellstmoeglich antworten. Bitte ein wenig Geduld, wir vergessen 
niemanden. Solltest Du Information sofort benoetigen, rufe uns bitte an.

Bitte behalte diese Ticket-ID im Betreff der eMail bei:
 [kapper.net #16695]
nur so ist unsere Korrespondenz eindeutig für alle beteiligten nachvollziehbar.
   Danke für Dein Interesse,
   die kapper.net::verwaltung ;-)
   [EMAIL PROTECTED]

-

Hi User,
this email is an automatically generated reply that confirms the receipt of your 
mesage and tells you we're already working on your request now.
We will respond ASAP, please give us a little time. If you need your information right 
now, please call us and let us know.

Please do also keep this messages Ticket-ID in the subject for further emails:
 [kapper.net #16695]
only this makes sure our communication is as accurate as possible.
   Thank you for talking to us,
   the kapper.net::administration ;-)
   [EMAIL PROTECTED]

[...deutsche version am beginn...]

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

Re: Another Replication Problem

[Jeremy Zawodny]

On Tue, Aug 19, 2003 at 02:51:28PM -0400, [EMAIL PROTECTED] wrote:
> 
> I am having another problem with replication in mysql 4.0.13. The 
> slave server crashed and upon re-starting there was a new relay-bin 
> log file created and the old relay-bin log file remained.

Right.  It needs to finish with the old relay file before it can
remove it and move on to the new one.

> MySQL, however seemed to be stuck on the old one. The slave reported
> to be running fine according to the command SHOW SLAVE STATUS, but
> there is definitely an inconsistency in the data between the two
> servers.

Show SHOW SLAVE STATUS reported no errors?

What about the error log?

> Is there any way to tell MySQL to process the other relay-bin files? 

It should do so automatically.

> I have tried using CHANGE MASTER TO to change the relay log, but
> then I get the error that the position doesn't exist in the new
> log. When I change the position to 0 it flushes the logs and deletes
> all of the relay-bin files.

Yeah, don't do that.

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

MySQL 4.0.13: up 18 days, processed 902,497,489 queries (578/sec. avg)

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

Another Replication Problem

[ed]

I am having another problem with replication in mysql 4.0.13. The 
slave server crashed and upon re-starting there was a new relay-bin 
log file created and the old relay-bin log file remained. MySQL, 
however seemed to be stuck on the old one. The slave reported to be 
running fine according to the command SHOW SLAVE STATUS, but there is 
definitely an inconsistency in the data between the two servers.

Is there any way to tell MySQL to process the other relay-bin files? 
I have tried using CHANGE MASTER TO to change the relay log, but then 
I get the error that the position doesn't exist in the new log. When 
I change the position to 0 it flushes the logs and deletes all of the 
relay-bin files.

Also I would like to find a better book for MySQL. I have MySQL 
second addition by Paul DuBois which is fine for most administrative 
duties, but I need to know more of the inner workings to solve 
problems more efficiently when they arise. 

Any help would be greatly appreciated.

Thanks in advance,
Ed

[EMAIL PROTECTED]


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

memory leaks

[Johannes Philipp Grohs]

Hello,

I am using the mysqlclient library 4.0.14 on win2k.
It works fine but there are memory leaks I have no solutions for.
I have tried this:

int _tmain(int argc, TCHAR* argv[], TCHAR* envp[])
{
MYSQL MySQL;
mysql_init(&MySQL);
mysql_close(&MySQL);
mysql_thread_end();
return 0;
}
... but in the debug mode I get results like this:

	Detected memory leaks!
	Dumping objects ->
	{52} normal block at 0x00974088, 556 bytes long.
	 Data: <È   > 00 00 00 00 C8 00 00 00 00 00 00 00 00 00 00 00 
	Object dump complete.

Do you know how to avoid this problem?

Thank you very much

	Philipp Grohs

PS: I am working with MS Visual C++ 6.0

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

Re: Replication Binlog Corruption - REPOST

[Jeremy Zawodny]

On Tue, Aug 19, 2003 at 09:12:03AM -0400, Jason McCormick wrote:
> Hello all,
> 
>   Sorry to post this again, but I go not response and was really hoping 
> someone can help me as this is a mission-critical problem.

Please don't take this the wrong way, but mission-critical problems
should be sent thru MySQL's official support channels.  Otherwise
you're at the mercy of a bunch of volunteers...

> I have 3 slave MySQL servers (4.0.13) replicating from a master
> machine (4.0.13).  The master machine is running only MySQL while
> the slave machines are an Apache + Tomcat + MySQL web service.  All
> slave machines are precisely identical.  I'm running into a
> situation where (seemly at random) after a month or two of
> consistently flawless performance, just ONE of the slave machines
> will report the following error in the .err log:
> 
> 030807  9:22:35  Error in Log_event::read_log_event(): 
> 'Event too big', data_len=1869425764,event_type=114
> 030807  9:22:35  Error reading relay log event: slave SQL thread 
> aborted because of I/O error
> 030807  9:22:35  Could not parse log event entry, check the master 
> for binlog corruption This may also be a network problem, 
> or just a bug in the master or slave code.
> 030807  9:22:35  Error running query, slave SQL thread aborted. Fix 
> the problem, and restart the slave SQL thread with "SLAVE 
> START". 
> We stopped at log 'oakpub-bin.002' position 25239274

Had the machine crashed and restarted recently?

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

MySQL 4.0.13: up 18 days, processed 902,078,317 queries (579/sec. avg)

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

Re: Limiting output of Text when retriving data via PHP

[Antony Dovgal]

On Tue, 19 Aug 2003 12:42:44 -0500
"Sean Meegan" <[EMAIL PROTECTED]> wrote:

> I have a table with a Longtext field in it.  This is a field which I would
> like to print out, but I only wish to print a couple of lines of data from
> this field for each record.
> 
> I'm using PHP with $row=mysql_fetch_row function and then printing out that
> row as one of of the fields.  
> 
> Can someone aid me with this?  
don't retreive all this text.
get some string from it using SUBSTRING() in MySQL.

---
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: scroll problem

[Rob A. Brahier]

If you are using *nix, fire up the mysql command-line client and type the
following:

\P more

Then hit enter.  That forces the client to pass all of its output through
the more command and will thus return a screen's worth of records at a time.
\P will pipe the client's output through whatever you wish.  If you want to
turn this feature off, just type \n on a new line all by itself. (Note that
this must be typed each time you start up the client app...)

-Rob

-Original Message-
From: Roger Baklund [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 19, 2003 8:42 AM
To: [EMAIL PROTECTED]
Cc: sanjay gupta
Subject: Re: scroll problem


* sanjay gupta
> i have tried limit , but i want 10 records at a time and when i
> hit "return key" i will show next 10 records, and so on. how i
> can achieve this .

Depending on your OS, you could try something like this:

$> mysql -e "SELECT * FROM tablename" database | more

It won't give you 10 rows, but the number of rows that fits your screen.

Note that 'more' is a OS utility, not a part of mysql.

This should work on win, linux & unix.

If you run unix/linux, try 'less' instead of 'more'... should give you also
page-up/page-down... ;)

--
Roger



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

Limiting output of Text when retriving data via PHP

[Sean Meegan]

I have a table with a Longtext field in it.  This is a field which I would
like to print out, but I only wish to print a couple of lines of data from
this field for each record.

I'm using PHP with $row=mysql_fetch_row function and then printing out that
row as one of of the fields.  

Can someone aid me with this?  


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

RE: Oracle DBA here looking for advice on MySQL ....

[Michael S. Fischer]

Michael,

The reason you think there's not much to this database is that compared
to Oracle, there really is not much to this database.  :-)  

>From a 50,000-foot point of view, it's really just a nice SQL interface
to ISAM files (it started out that way, anyway).  It is designed for
high-performance in read-intensive environments, and so it doesn't
really need to be that complicated.

MySQL is a single-process, multithreaded server.  That means you start
mysqld through the wrapper script and that's all that runs.  It doesn't
have separate specialized daemons for doing various tasks like Oracle
and PostgreSQL do.  All data is stored in MyISAM files (assuming you're
not using InnoDB or BDB), two per table (one data, one index file), and
tables are stored in directories corresponding to the database name in
the configured data directory.

Some basic knowledge of system performance tuning for your given OS and
reading the manual, particularly Chapter 5 (MySQL Optimization) should
put you well ahead of the pack.

Best,

--Michael 

> -Original Message-
> From: Johnson, Michael [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, August 19, 2003 9:48 AM
> To: 'Andy Jackman'
> Cc: MySQL Users
> Subject: RE: Oracle DBA here looking for advice on MySQL 
> 
> 
> I went thru the documentation this weekend on it and found 
> that there is really not to much to this database. One thing 
> we learn as Oracle DBA's is how the whole  database starts up 
> and how all those processes work  together
> and where to find bottlenecks when things start to bog down.  
>   It didnt see
> any of that in the MySQL docs I read.
> 
> I appears that MySQL has some potential though.
> 
> Mike
> 
> 
> 
> -Original Message-
> From: Andy Jackman [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 19, 2003 12:29 AM
> To: Johnson, Michael 
> Cc: MySQL Users
> Subject: Re: Oracle DBA here looking for advice on MySQL 
> 
> 
> I've used a lot of Oracle, some MS Access and I'm newish to 
> MySQL. I found it easy to write an abstraction layer for Ms 
> Access and Oracle despite their different approaches to some 
> important things. I find MySQL very sparse by comparison and 
> I spend more time working round the db than working with it. 
> Unlike Oracle the richness and integrity of language is 
> simply missing - these people have lived without something as 
> useful as sub-queries for a long time. (The argument being 
> that speed and data integrity are all-important). It's more a 
> file system than a relational database. I know you asked 
> about books rather than a comparison of the products, but the 
> software philosophy is reflected in the documentation. If 
> someone else pays you to be an Oracle Dba then I bet you have 
> at least a 10 foot shelf of comprehensive documentation. This 
> list is about as good as it gets (see your previous 
> response). There is a PDF copy of the manual somewhere and 
> setting up MySql was accompilshed by a colleague who wouldn't 
> have known where to start with Oracle, so it has that in its favour.
> 
> So, if you're thinking of migrating, think carefully! If I 
> could get Oracle to give me a sensible price (say USD 1000) 
> to sell their db with my product I would be out of here so 
> fast. So far with mySQL i've written my own database for a 
> particular (simple) structure that it wouldn't handle to my 
> satisfaction; I've written my own date/time routines to 
> calculate things like seconds between 2 datetimes (despite a 
> wealth of datatime functions, this one isn't available unless 
> you convert to 'Unix' dates which expire in 2036) and I've 
> written functions to handle the fact that in 'C' all data is 
> returned as strings rather than as native data types. Sigh.
> - Andy
> 
> "Johnson, Michael" wrote:
> > 
> > What is the best book on MySQL with regard
> > to its Architecture and how it starts up, shutdowns, processes 
> > queries, rolls back data, etc etc. ?
> > 
> > I am not looking for a SQL book here.
> > 
> > What is the best My SQL book you have read ?
> > 
> > Thank you in advance.
> > 
> > Mike
> > 
> > --
> > 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/mysql?> [EMAIL PROTECTED]
> 
> 


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

RE: Oracle DBA here looking for advice on MySQL ....

[Wendell Dingus]

You might also like the INNODB table type for which there is a fairly
large online manual at http://www.innodb.com/ibman.html


-Original Message-
From: Johnson, Michael [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 19, 2003 12:48 PM
To: 'Andy Jackman'
Cc: MySQL Users
Subject: RE: Oracle DBA here looking for advice on MySQL 

I went thru the documentation this weekend on it and found that there is
really not to much to this database. One thing we learn as Oracle DBA's
is
how the whole  database starts up and how all those processes work
together
and where to find bottlenecks when things start to bog down.It didnt
see
any of that in the MySQL docs I read.

I appears that MySQL has some potential though.

Mike







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

RE: Oracle DBA here looking for advice on MySQL ....

[Johnson, Michael]

I went thru the documentation this weekend on it and found that there is
really not to much to this database. One thing we learn as Oracle DBA's is
how the whole  database starts up and how all those processes work  together
and where to find bottlenecks when things start to bog down.It didnt see
any of that in the MySQL docs I read.

I appears that MySQL has some potential though.

Mike



-Original Message-
From: Andy Jackman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 19, 2003 12:29 AM
To: Johnson, Michael 
Cc: MySQL Users
Subject: Re: Oracle DBA here looking for advice on MySQL 


I've used a lot of Oracle, some MS Access and I'm newish to MySQL. I
found it easy to write an abstraction layer for Ms Access and Oracle
despite their different approaches to some important things. I find
MySQL very sparse by comparison and I spend more time working round the
db than working with it. Unlike Oracle the richness and integrity of
language is simply missing - these people have lived without something
as useful as sub-queries for a long time. (The argument being that speed
and data integrity are all-important). It's more a file system than a
relational database. I know you asked about books rather than a
comparison of the products, but the software philosophy is reflected in
the documentation. If someone else pays you to be an Oracle Dba then I
bet you have at least a 10 foot shelf of comprehensive documentation.
This list is about as good as it gets (see your previous response).
There is a PDF copy of the manual somewhere and setting up MySql was
accompilshed by a colleague who wouldn't have known where to start with
Oracle, so it has that in its favour.

So, if you're thinking of migrating, think carefully! If I could get
Oracle to give me a sensible price (say USD 1000) to sell their db with
my product I would be out of here so fast. So far with mySQL i've
written my own database for a particular (simple) structure that it
wouldn't handle to my satisfaction; I've written my own date/time
routines to calculate things like seconds between 2 datetimes (despite a
wealth of datatime functions, this one isn't available unless you
convert to 'Unix' dates which expire in 2036) and I've written functions
to handle the fact that in 'C' all data is returned as strings rather
than as native data types. Sigh.
- Andy

"Johnson, Michael" wrote:
> 
> What is the best book on MySQL with regard
> to its Architecture and how it starts up, shutdowns,
> processes queries, rolls back data, etc etc. ?
> 
> I am not looking for a SQL book here.
> 
> What is the best My SQL book you have read ?
> 
> Thank you in advance.
> 
> Mike
> 
> --
> 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: Thank you!

[tps]

See the attached file for details
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re[2]: SHOW DATABASES works in 4.0.14

[Vladimir Trebicky]

VR> What do you mean "didn't work properly'? What exactly was
VR> wrong with mysql_fix_privilege_tables?

Sorry, cannot reproduce the error. The result was that some of the
tables were corrected, some not. Anyway, even old privilege tables
shouldn't IMHO cause the SHOW DATABASES to work. In addition, I
corrected the tables manually and are ok now.

VR> In 4.0.14 --safe-show-database is enabled by default.

I know, another reason why I'm so confused ;-)

Is there any way to debug this?

I attach my SHOW VARIABLES. (which are btw also accessable by normal
users)Variable_name   Value
back_log50
basedir /usr/local/mysql-standard-4.0.14-pc-linux-i686/
binlog_cache_size   32768
bulk_insert_buffer_size 8388608
character_set   latin1
character_sets  latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 
dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia 
hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent_insert   ON
connect_timeout 5
convert_character_set   
datadir /usr/local/mysql/data/
default_week_format 0
delay_key_write ON
delayed_insert_limit100
delayed_insert_timeout  300
delayed_queue_size  1000
flush   OFF
flush_time  0
ft_boolean_syntax   + -><()~*:""&|
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort20
ft_stopword_file(built-in)
have_bdbNO
have_crypt  YES
have_innodb DISABLED
have_isam   YES
have_raid   NO
have_symlinkYES
have_opensslNO
have_query_cacheYES
init_file   
innodb_additional_mem_pool_size 1048576
innodb_buffer_pool_size 8388608
innodb_data_file_path   
innodb_data_home_dir
innodb_file_io_threads  4
innodb_force_recovery   0
innodb_thread_concurrency   8
innodb_flush_log_at_trx_commit  1
innodb_fast_shutdownON
innodb_flush_method 
innodb_lock_wait_timeout50
innodb_log_arch_dir 
innodb_log_archive  OFF
innodb_log_buffer_size  1048576
innodb_log_file_size5242880
innodb_log_files_in_group   2
innodb_log_group_home_dir   
innodb_mirrored_log_groups  1
innodb_max_dirty_pages_pct  90
interactive_timeout 28800
join_buffer_size131072
key_buffer_size 16777216
language/usr/local/mysql-standard-4.0.14-pc-linux-i686/share/mysql/english/
large_files_support ON
local_infileON
locked_in_memoryOFF
log ON
log_update  OFF
log_bin ON
log_slave_updates   OFF
log_slow_queriesOFF
log_warningsOFF
long_query_time 10
low_priority_updatesOFF
lower_case_table_names  OFF
max_allowed_packet  1047552
max_binlog_cache_size   4294967295
max_binlog_size 1073741824
max_connections 100
max_connect_errors  10
max_delayed_threads 20
max_heap_table_size 16777216
max_join_size   4294967295
max_relay_log_size  0
max_seeks_for_key   4294967295
max_sort_length 1024
max_user_connections0
max_tmp_tables  32
max_write_lock_count4294967295
myisam_max_extra_sort_file_size 268435456
myisam_max_sort_file_size   2147483647
myisam_repair_threads   1
myisam_recover_options  OFF
myisam_sort_buffer_size 8388608
net_buffer_length   8192
net_read_timeout30
net_retry_count 10
net_write_timeout   60
new OFF
open_files_limit0
pid_file/usr/local/mysql/data/master.pid
log_error   
port3306
protocol_version10
read_buffer_size131072
read_only   OFF
read_rnd_buffer_size262144
rpl_recovery_rank   0
query_cache_limit   1048576
query_cache_size0
query_cache_typeON
server_id   1
slave_net_timeout   3600
skip_external_locking   ON
skip_networking OFF
skip_show_database  OFF
slow_launch_time2
socket  /tmp/mysql.sock
sort_buffer_size524280
sql_mode0
table_cache 64
table_type  MYISAM
thread_cache_size   0
thread_stack126976
tx_isolationREPEATABLE-READ
timezoneCEST
tmp_table_size  33554432
tmpdir  /tmp/
version 4.0.14-standard-log
wait_timeout28800
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Need help optimizing query, awfully slow on only 20000 records

[Apollo (Carmel Entertainment)]

1.2Ghz Pentium, with 1/2Gig of ram, 4.0.14MySQL, RedHat9.0

I have about 20K records that result from the following query. Front end for the
database is ACCESS97 and pulling up 20K records makes a huge performance hit.
For the form in question I am using PASSTHROUGH type query (the one that just
passes everything straight to server without ODBC).
NOTE: souce_for_inquiries_form is the join table and is searchable in the from
(it feeds a pull-down list).

SELECT inquiries.inquiry_id, inquiries.contact_id, inquiries.indiv_contact_id,
inquiries.phone, inquiries.fax, inquiries.agent_id, inquiries.inquiry_date,
inquiries.event_type, inquiries.letter_type, inquiries.event_date,
inquiries.event_date_general, inquiries.event_location, inquiries.guests,
inquiries.hours, inquiries.budget, inquiries.event_description,
inquiries.talent_description, inquiries.past_use, inquiries.referred_by,
inquiries.date_sent, inquiries.end_user, inquiries.event_id, inquiries.notes,
source_for_inquiries_form.organization,
source_for_inquiries_form.mailing_address_1,
source_for_inquiries_form.mailing_address_2, source_for_inquiries_form.city,
source_for_inquiries_form.state, source_for_inquiries_form.zip,
source_for_inquiries_form.contact_type, individual_contacts.contact_name_first,
individual_contacts.contact_name_last, individual_contacts.contact_prefix,
individual_contacts.contact_title, individual_contacts.email
FROM inquiries 
LEFT JOIN individual_contacts ON inquiries.indiv_contact_id =
individual_contacts.indiv_contact_id 
INNER JOIN contacts AS source_for_inquiries_form ON inquiries.contact_id =
source_for_inquiries_form.contact_id
ORDER BY  inquiries.inquiry_id DESC;

-
Visit CARMEL MUSIC & ENTERTAINMENT website http://carmelme.com

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

RE: Import from Excel to MySQL

[Landers, Jason]

I am also working on something like this but am taking a different approach.
Basically I'm creating an Excell Add-in in VBA that adds a button to the
Excel toolbar. When a user clicks it I take the highlighted rows and columns
and verify that they are in the proper format and everything looks good then
import them into the database with the MySQL ODBC driver and VBA. It's more
work up front than your solution but for the PHB it's a lot more comfortable
than learning a new custom application and process. To them it's just Office
;)

-Original Message-
From: Jackson Miller [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 19, 2003 12:16 PM
To: Jay Blanchard; Phil Perrin; [EMAIL PROTECTED]
Subject: Re: Import from Excel to MySQL


I just had to solve this problem.

I needed to be able to have users load an excel file from a browser and have

it import.

To do this I used perl to have the excel convert to csv on the server.  This

was easy to accomplish using the CPAN module ParseExcel.  The module comes 
with a sample script that basically does the trick.

I am planning to use perl2exe to create an executable of my xls2csv perl 
script.  But the perl script is working fine.  I use exec() to call the perl

script.

-Jackson


On Tuesday 19 August 2003 10:49, Jay Blanchard wrote:
> [snip]
> Basically I need a way to import them from the Excel sheet to the
> database so at that point I can manipulate the data and use a php script
> of some sort to run a whois after extracting the domain name, and then
> return the results to the database and have it attached to the domain
> name.
> [/snip]
>
> Export the Excel to csv, then import the csv to MySQL. There are classes
> for PHP that will read the binary data directly from Excel, but they are
> costly...i.e. http://www.web-aware.com/biff/
>
> HTH!

-- 
jackson miller
 
cold feet creative
615.321.3300 / 800.595.4401
[EMAIL PROTECTED]
 
 
cold feet presents Emma
the world's easiest email marketing
Learn more @  http://www.myemma.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]

insert field won't insert but rec does

[Robb Garrioch]

Hi, I am writing a Web app that inserts Referring and Requesting 
addresses into a MySQL database.  On some of these URL strings the 
INSERT statement will insert all the other fields but not the URL.  
There are no errors and 95% of the time the entire record inserts 
perfectly.  What the?  Anyone have any ideas as to why it randomly 
decides not to insert these URLs?  It is not a string length issue or 
'grabbing the right string' issue.  Thanks for any ideas!

Robb



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

Re: Import from Excel to MySQL

[Jackson Miller]

I just had to solve this problem.

I needed to be able to have users load an excel file from a browser and have 
it import.

To do this I used perl to have the excel convert to csv on the server.  This 
was easy to accomplish using the CPAN module ParseExcel.  The module comes 
with a sample script that basically does the trick.

I am planning to use perl2exe to create an executable of my xls2csv perl 
script.  But the perl script is working fine.  I use exec() to call the perl 
script.

-Jackson


On Tuesday 19 August 2003 10:49, Jay Blanchard wrote:
> [snip]
> Basically I need a way to import them from the Excel sheet to the
> database so at that point I can manipulate the data and use a php script
> of some sort to run a whois after extracting the domain name, and then
> return the results to the database and have it attached to the domain
> name.
> [/snip]
>
> Export the Excel to csv, then import the csv to MySQL. There are classes
> for PHP that will read the binary data directly from Excel, but they are
> costly...i.e. http://www.web-aware.com/biff/
>
> HTH!

-- 
jackson miller
 
cold feet creative
615.321.3300 / 800.595.4401
[EMAIL PROTECTED]
 
 
cold feet presents Emma
the world's easiest email marketing
Learn more @  http://www.myemma.com

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

RE: Import from Excel to MySQL

[Jay Blanchard]

[snip]
Basically I need a way to import them from the Excel sheet to the
database so at that point I can manipulate the data and use a php script
of some sort to run a whois after extracting the domain name, and then
return the results to the database and have it attached to the domain
name.
[/snip]

Export the Excel to csv, then import the csv to MySQL. There are classes
for PHP that will read the binary data directly from Excel, but they are
costly...i.e. http://www.web-aware.com/biff/

HTH!

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

RE: Cache problem in 4.1

[Landers, Jason]

You guess correctly :) I set the variable in my config file and the query
cache does seem to be working. The counters are incrementing and it's
caching the majority of my queries (it says). 

I do notice a very slight improvement in the performance but it's not as
fast as I was expecting. I guess since the system I'm hosting on is only a
P3600 with 128MB RAM it's probably the interpreted Perl CGI code that's
bogging it down now. 

By the way, what is an appropriate cache memory size for such a system? Is
there a point where too much allocated memory does more harm than good?

-Original Message-
From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 19, 2003 11:18 AM
To: [EMAIL PROTECTED]
Subject: Re: Cache problem in 4.1


"Landers, Jason" <[EMAIL PROTECTED]> wrote:
> My web site is pulling from several tables that don't change all that
often
> and seems to be an ideal candidate for query caching. I tried to enable it
> by copying the my-medium.cnf to /etc/my.cnf and restarted the service.
From
> my query editor I typed SHOW VARIABLES LIKE 'QUERY_CACHE_TYPE' and it
> returns ON which is exactly what I want.
> 
> I then executed several queries from my site but none of them are being
> cached. They're just plain SELECTs in most cases, any ideas why the DB
won't
> cache them? Do they have to be executed several times first?
> 
> When I try and check Qcache_queries_in_cache I get an empty set, likewise
> for Qcache_not_cached... so it's not caching any and it's not not caching
> any
> 

What is the values of query_cache_size variable? I guess that
query_cache_size=0 ...


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

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

Import from Excel to MySQL

[Phil Perrin]

I'm a relative newbie to MySql, and I've got a small project I'm working
on and hopefully you folks can either point me in the proper direction
or give me a little help.
 
I have multiple spreadsheets in Excel format and in .csv format too,
that I would like to possibly import to a mysql database. All of the
excel and csv files have is one huge colum and they are only 1 cell on
each line. An estimate of 7k-8k domain names I need to run a whois on.
 
Basically I need a way to import them from the Excel sheet to the
database so at that point I can manipulate the data and use a php script
of some sort to run a whois after extracting the domain name, and then
return the results to the database and have it attached to the domain
name.
 
Any help would be greatly appreciated!
 
~Phil

Re: Cache problem in 4.1

[Victoria Reznichenko]

"Landers, Jason" <[EMAIL PROTECTED]> wrote:
> My web site is pulling from several tables that don't change all that often
> and seems to be an ideal candidate for query caching. I tried to enable it
> by copying the my-medium.cnf to /etc/my.cnf and restarted the service. From
> my query editor I typed SHOW VARIABLES LIKE 'QUERY_CACHE_TYPE' and it
> returns ON which is exactly what I want.
> 
> I then executed several queries from my site but none of them are being
> cached. They're just plain SELECTs in most cases, any ideas why the DB won't
> cache them? Do they have to be executed several times first?
> 
> When I try and check Qcache_queries_in_cache I get an empty set, likewise
> for Qcache_not_cached... so it's not caching any and it's not not caching
> any
> 

What is the values of query_cache_size variable? I guess that query_cache_size=0 ...


-- 
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: Cache problem in 4.1

[Landers, Jason]

Further investigation shows that Qcache_free_memory = 0,
Qcache_free_blocks=0, and Qcache_total_blocks=0. So it seems that the cache
is enabled but no sizes/limits have been allocated? Could someone tell me
what I need to add/modify to my my.cnf file to set those? Or are these
variables indicating some other problem? Thanks.

-Original Message-
From: Landers, Jason [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 19, 2003 10:57 AM
To: '[EMAIL PROTECTED]'
Subject: Cache problem in 4.1


My web site is pulling from several tables that don't change all that often
and seems to be an ideal candidate for query caching. I tried to enable it
by copying the my-medium.cnf to /etc/my.cnf and restarted the service. From
my query editor I typed SHOW VARIABLES LIKE 'QUERY_CACHE_TYPE' and it
returns ON which is exactly what I want.

I then executed several queries from my site but none of them are being
cached. They're just plain SELECTs in most cases, any ideas why the DB won't
cache them? Do they have to be executed several times first?

When I try and check Qcache_queries_in_cache I get an empty set, likewise
for Qcache_not_cached... so it's not caching any and it's not not caching
any

JAson

-- 
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: SHOW DATABASES works in 4.0.14

[Victoria Reznichenko]

Vladimir Trebicky <[EMAIL PROTECTED]> wrote:
> 
>   I've been trying to solve this problem for a long time. Only thing
> I did when upgrading from 3.25 to 4.0.14 I took the default my.cnf. I
> did not convert the privilege tables with mysql_fix_privilege_tables.
> 
> Suddenly, the mysql started to show all databases to all users (even
> though the user had access denied and could not do show tables on
> them). So I started to find help and tried the
> mysql_fix_privilege_tables. It didn't work properly (more than just
> access denied and errors, that are ok) so I did the changes manually
> conforming to source code of mysql_fix_privilege tables.

What do you mean "didn't work properly'? What exactly was wrong with 
mysql_fix_privilege_tables?

> 
> PhpMyAdmin stopped its warning about privilege tables not conforming
> to current mysql version. Everything seemed ok except that SHOW
> DATABASES still worked.
> 
> The users naturally all have Show_db_priv="N" in the tables. Also
> trying "safe_show_database", "skip_show_database" in my.cnf and
> "--skip-show-database" witch mysqld didn't help.

In 4.0.14 --safe-show-database is enabled by default.

> 
> All *.frm files in mysql database have the same md5 as those generated
> by scripts/mysql_install_db.
> 


-- 
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: bad data in mysql tables in replication server

[Egor Egorov]

"Luc Foisy" <[EMAIL PROTECTED]> wrote:
> 
> Last week many of our server and client servers had a power problem. Not quite sure 
> how the servers were handled, wasn't on site, but I don't think some of these 
> servers got shut down gracefully. but anyways that shouldn't matter to my question
> 
> I ran myisamchk on the data directories and I get a large report containing things 
> such as
> 
> myisamchk: MyISAM file /usr/data/mysql/qbslive/MANIFESTSPOOL.MYI
> myisamchk: warning: 1 clients is using or hasn't closed the table properly
> MyISAM-table '/usr/data/mysql/qbslive/MANIFESTSPOOL.MYI' is usable but should be 
> fixed
> myisamchk: MyISAM file /usr/data/mysql/qbslive/MARKETING.MYI
> myisamchk: warning: 1 clients is using or hasn't closed the table properly
> MyISAM-table '/usr/data/mysql/qbslive/MARKETING.MYI' is usable but should be fixed
> myisamchk: MyISAM file /usr/data/mysql/qbslive/ORDERHEADER.MYI
> myisamchk: warning: 1 clients is using or hasn't closed the table properly
> MyISAM-table '/usr/data/mysql/qbslive/ORDERHEADER.MYI' is usable but should be fixed
> 
> I am assuming I can just run myisamchk -r on these tables to fix them up on the 
> master server... but I have multiple replication servers of databases...
> Can I repair these in the same way, or will the system fix it up or what? The slave 
> servers are definately not running at the moment, have show slave status on one rep 
> server of 
> | 1062   | error 'Duplicate entry '5486435' for key 1' on query 'INSERT INTO 
> DISPATCHLOG (CreateStamp) VALUES (NOW())' |
> 
> Not quite sure what to do here, is my replicant toast?
> 

If you run myisamchk -r on the master, copy data from the master to the slave to make 
sure that master and slave have exactly the same data.



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

Cache problem in 4.1

[Landers, Jason]

My web site is pulling from several tables that don't change all that often
and seems to be an ideal candidate for query caching. I tried to enable it
by copying the my-medium.cnf to /etc/my.cnf and restarted the service. From
my query editor I typed SHOW VARIABLES LIKE 'QUERY_CACHE_TYPE' and it
returns ON which is exactly what I want.

I then executed several queries from my site but none of them are being
cached. They're just plain SELECTs in most cases, any ideas why the DB won't
cache them? Do they have to be executed several times first?

When I try and check Qcache_queries_in_cache I get an empty set, likewise
for Qcache_not_cached... so it's not caching any and it's not not caching
any

JAson

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

Re: Max size

[Miguel Perez]

No, it doesn't realize that the file size is about 330GB. To best 
performance is better split the ibdata in several files? or should I have 
only one?



Thanx


From: Jeremy Zawodny <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Miguel Perez <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED]
Subject: Re: Max size
Date: Mon, 18 Aug 2003 23:43:19 -0700
On Mon, Aug 18, 2003 at 02:33:04PM -0500, Miguel Perez wrote:
>
> I have an ibdata file that its size is 4.5GB, and it will increase
> every day the max size of the hard disk is about 330GB, the question
> is should I split this ibdata file in several files in a way that I
> can reach this size?
Does your operating system prevent you from creating 330GB files?
--
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
<[EMAIL PROTECTED]>  |  http://jeremy.zawodny.com/
MySQL 4.0.13: up 17 days, processed 883,556,901 queries (583/sec. avg)
_
MSN Fotos: la forma más fácil de compartir e imprimir fotos.  
http://photos.msn.es/support/worldwide.aspx

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

SHOW DATABASES works in 4.0.14

[Vladimir Trebicky]

Hi,

   I've been trying to solve this problem for a long time. Only thing
I did when upgrading from 3.25 to 4.0.14 I took the default my.cnf. I
did not convert the privilege tables with mysql_fix_privilege_tables.

Suddenly, the mysql started to show all databases to all users (even
though the user had access denied and could not do show tables on
them). So I started to find help and tried the
mysql_fix_privilege_tables. It didn't work properly (more than just
access denied and errors, that are ok) so I did the changes manually
conforming to source code of mysql_fix_privilege tables.

PhpMyAdmin stopped its warning about privilege tables not conforming
to current mysql version. Everything seemed ok except that SHOW
DATABASES still worked.

The users naturally all have Show_db_priv="N" in the tables. Also
trying "safe_show_database", "skip_show_database" in my.cnf and
"--skip-show-database" witch mysqld didn't help.

All *.frm files in mysql database have the same md5 as those generated
by scripts/mysql_install_db.

-- 
Thanks for help,
 Vladimir Trebicky  mailto:[EMAIL PROTECTED]


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

What's the meaning of: "InnoDB: Warning: using a partial-field key prefix in search"

[Mechain Marc]

In one of my logfiles I have quite repeatedly the following message:

InnoDB: Warning: using a partial-field key prefix in search

What does this warning mean ?

Regards,

Marc Mechain
Atos Origin






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

myisamchk question (important)

[Luc Foisy]

Would anything happen to the database if I ran "myisamchk --silent 
/usr/data/mysql/*/*.MYI" when I havent run "FLUSH TABLES" first?

As it states in the documentation:
If mysqld is running, you must force a sync/close of all tables with FLUSH TABLES and 
ensure that no one is using the tables while you are running myisamchk. In MySQL 
Version 3.23 the easiest way to avoid this problem is to use CHECK TABLE instead of 
myisamchk to check tables. 

Or is that only to avoid seeing:
myisamchk: warning: 1 clients is using or hasn't closed the table properly

Its very important that I get an answer soon... Please and Thank you

Luc

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

Replication Binlog Corruption - REPOST

[Jason McCormick]

Hello all,

  Sorry to post this again, but I go not response and was really hoping 
someone can help me as this is a mission-critical problem. I have 3 
slave MySQL servers (4.0.13) replicating from a master machine 
(4.0.13).  The master machine is running only MySQL while the slave 
machines are an Apache + Tomcat + MySQL web service.  All slave 
machines are precisely identical.  I'm running into a situation where 
(seemly at random) after a month or two of consistently flawless 
performance, just ONE of the slave machines will report the following 
error in the .err log:

030807  9:22:35  Error in Log_event::read_log_event(): 
'Event too big', data_len=1869425764,event_type=114
030807  9:22:35  Error reading relay log event: slave SQL thread 
aborted because of I/O error
030807  9:22:35  Could not parse log event entry, check the master 
for binlog corruption This may also be a network problem, 
or just a bug in the master or slave code.
030807  9:22:35  Error running query, slave SQL thread aborted. Fix 
the problem, and restart the slave SQL thread with "SLAVE 
START". 
We stopped at log 'oakpub-bin.002' position 25239274

However the other two slaves are up-to-date on replication with no error 
messages and mysqlbinlog output shows no obvious malformed statements.  
This problem is not specific to just one machine but has happened on 
all 3 slave nodes in the past few months, all at different times. Trying 
to restart the slave process results in the same error.  Manually 
performing the statement in the binlog works fine and then the slave 
can be played forward to the current master logfile location with no 
problems.  I've also tried deleting the entire database on the slave 
and recreating everything with LOAD DATA FROM MASTER and that goes 
smoothly as well. Any thoughts?

Thanks,
Jason

-- 
Jason McCormick
Network & Systems Administrator - Lexi-Comp, Inc.
[EMAIL PROTECTED] - 330.656.0239


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

Re: scroll problem

[Roger Baklund]

* sanjay gupta
> i have tried limit , but i want 10 records at a time and when i
> hit "return key" i will show next 10 records, and so on. how i
> can achieve this .

Depending on your OS, you could try something like this:

$> mysql -e "SELECT * FROM tablename" database | more

It won't give you 10 rows, but the number of rows that fits your screen.

Note that 'more' is a OS utility, not a part of mysql.

This should work on win, linux & unix.

If you run unix/linux, try 'less' instead of 'more'... should give you also
page-up/page-down... ;)

--
Roger


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

RE: Moving MySQL data to RAMDisk

[Wendell Dingus]

MySQL is pretty good about following symlinks. Move /var/lib/mysql to
/mnt/ramdisk/mysql and then symlink to it.

# ln -s /mnt/ramdisk/mysql /var/lib/mysql

Changing where everything resides in my.cnf should have been sufficient
but the symlink way works great as well. And there should be no
performance hit. I'm fairly certain I've read in the docs that it
traverses a symlink to it's source directory and proceeds to access the
files there instead of hitting the symlink every time. 

Also, take PHP out of the loop at first. Try accessing it via the
command-line mysql client first and after you get that working, then
introduce extra variables like apache/php into the mix.

PS. Check permissions as well.. /mnt/ramdisk/mysql needs to very closely
match the permissions of /var/lib/mysql (as well as owner/group). 



-Original Message-
From: Creigh Shank [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 19, 2003 8:35 AM
To: MySQL Users
Subject: Moving MySQL data to RAMDisk

On moving my data files from /var/lib to /mnt/ramdisk I seem to be
missing 
something.  I've changed every .ini and .conf file I can find
(/etc/my.cnf, 
/etc/init.d/mysql, /etc/init.d/mysqld, /etc/php.ini and 
/etc/httpd/conf/httpd.conf (for DAV-lock)).  MySQL seems to start ok,
but 
when I use my application through Apache the error message is that MySQL
is 
not running (I think it's just not finding it).

Any suggestions?

Creigh


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

Moving MySQL data to RAMDisk

[Creigh Shank]

On moving my data files from /var/lib to /mnt/ramdisk I seem to be missing 
something.  I've changed every .ini and .conf file I can find (/etc/my.cnf, 
/etc/init.d/mysql, /etc/init.d/mysqld, /etc/php.ini and 
/etc/httpd/conf/httpd.conf (for DAV-lock)).  MySQL seems to start ok, but 
when I use my application through Apache the error message is that MySQL is 
not running (I think it's just not finding it).

Any suggestions?

Creigh

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

Replication question

[Jeff McKeon]

Hey all,

I have 3 databases replicating (ver 3.23)  A to B and B to C

On C I want to modify one of the tables and add a column.  Tables A and
B will not have this new column added.  Will this cause a problem
replicating data form B to C?

Thanks,

Jeff

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

RE: Import CSV file into MySQL table via phpMyAdmin

[Jay Blanchard]

[snip]
Table name "campuses"
inst_id, name, zip_id, state (inst_id is an auto-increment integer data
type)

The CSV file has the following format
Name, zip, state

For example:
ACADEMY OF ART COLLEGE,94105,CA

In phpMyAdmin I go to SQL -> Insert data from a textfile into table,
then
fill in the fields with the following values:

Location: CSV path/file
Replace table data with file: unchecked
Fields terminated by: ,
Fields enclosed by: 
Fields escaped by: 
Lines terminated by: 
Column names: 
LOAD method: DATA LOCAL
[/snip]

Change Column names:  to name, zip_id, state 


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

Re: Help with SELECT statement for date range

[shaag]

Hi,

try this:

SELECT * FROM your_table
WHERE StartDate > NOW()
AND EndDate < NOW()



> Hello,
>
> I am having a problem when doing a SELECT. Here is the
> scenerio:
>
> I have a table that has an event StartDate and
> EndDate, based on the current Date "NOW()" I need to
> know which records are currently active. Can anyone
> help with a quick SELECT statement?
>
> Thanks.
>
> =
>
>
> __
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> http://sitebuilder.yahoo.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 

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: Help with SELECT statement for date range

[Ralph Guzman]

SELECT * FROM table_name WHERE EndDate < now();

Is this what you need?

-Original Message-
From: Rob Sirota [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 19, 2003 4:12 AM
To: [EMAIL PROTECTED]
Subject: Help with SELECT statement for date range

Hello,

I am having a problem when doing a SELECT. Here is the
scenerio:

I have a table that has an event StartDate and
EndDate, based on the current Date "NOW()" I need to
know which records are currently active. Can anyone
help with a quick SELECT statement?

Thanks.

=


__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.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: Help with SELECT statement for date range

[Antony Dovgal]

On Tue, 19 Aug 2003 04:11:32 -0700 (PDT)
Rob Sirota <[EMAIL PROTECTED]> wrote:

> Hello,
> 
> I am having a problem when doing a SELECT. Here is the
> scenerio:
> 
> I have a table that has an event StartDate and
> EndDate, based on the current Date "NOW()" I need to
> know which records are currently active. Can anyone
> help with a quick SELECT statement?

I suppose you need this:
SELECT * FROM table WHERE NOW() BETWEEN StartDate AND EndDate;

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

Help with SELECT statement for date range

[Rob Sirota]

Hello,

I am having a problem when doing a SELECT. Here is the
scenerio:

I have a table that has an event StartDate and
EndDate, based on the current Date "NOW()" I need to
know which records are currently active. Can anyone
help with a quick SELECT statement?

Thanks.

=


__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

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

Re: scroll problem

[Victoria Reznichenko]

"sanjay gupta" <[EMAIL PROTECTED]> wrote:
> thanks for the reply,
> 
> i have tried limit , but i want 10 records at a time and when i hit "return
> key" i will show next 10 records, and so on. how i can achieve this .

Use SELECT  LIMIT 10, 10 to retrieve rows 11-20 

> 
> sanjay
> - Original Message -
> From: "Victoria Reznichenko" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, August 19, 2003 2:32 PM
> Subject: Re: scroll problem
> 
> 
>> "sanjay gupta" <[EMAIL PROTECTED]> wrote:
>> >
>> >I have lot of problem due to scrolling .e.g.  When  i
> give
>> > command " select * from table-name" .if the records are more than the
> srceen
>> > width then i am unable to see all the records ,only last records are
>> > visible.Is there any command suffix to SQL commands so that i may able
> to
>> > see all the records,
>>
>> Use LIMIT clause to retrieve only certain number of records. You can also
> use SELECT .. INTO OUTFILE command:
>> http://www.mysql.com/doc/en/SELECT.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: punctuation in fulltext searching

[electroteque]

hmm well sorry to be unclear i know this works but it would return more
results than needed also i cant expect users to add this themselves, like i
would have to add the astrerix to every word in that case like i do to get
all words ;\

-Original Message-
From: Egor Egorov [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 19, 2003 7:41 PM
To: [EMAIL PROTECTED]
Subject: Re: punctuation in fulltext searching


Daniel Rossi <[EMAIL PROTECTED]> wrote:
> Hi i have just come across an issue where a word is not being searched up
if there is any punctuation ie. AMROZI'S will not be search upon if you type
AMROZI , please help

Take a look at * operator which you can use in BOOLEAN MODE.



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




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


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

Re: scroll problem

[shaag]

I guess MySQL is a DBMS ans not a frontend.

> thanks for the reply,
>
> i have tried limit , but i want 10 records at a time and when i hit
> "return
> key" i will show next 10 records, and so on. how i can achieve this .
>
> sanjay
> - Original Message -
> From: "Victoria Reznichenko" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, August 19, 2003 2:32 PM
> Subject: Re: scroll problem
>
>
>> "sanjay gupta" <[EMAIL PROTECTED]> wrote:
>> >
>> >I have lot of problem due to scrolling .e.g.  When  i
> give
>> > command " select * from table-name" .if the records are more than the
> srceen
>> > width then i am unable to see all the records ,only last records are
>> > visible.Is there any command suffix to SQL commands so that i may able
> to
>> > see all the records,
>>
>> Use LIMIT clause to retrieve only certain number of records. You can
>> also
> use SELECT .. INTO OUTFILE command:
>> http://www.mysql.com/doc/en/SELECT.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]
>>
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>


-- 

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: mysqlbinlog wrong output

[Egor Egorov]

Jan Josefowicz <[EMAIL PROTECTED]> wrote:
>> Does the version of mysqlbinlog match that of the server that wrote
>> the binlog?
> 
> I'm using MySQL 4.0.14 [linux]
> mysqlbinlog version 1.6 [linux]

This version of mysqlbinlog can work properly only with 3.23 server binary logs.



-- 
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: scroll problem

[sanjay gupta]

thanks for the reply,

i have tried limit , but i want 10 records at a time and when i hit "return
key" i will show next 10 records, and so on. how i can achieve this .

sanjay
- Original Message -
From: "Victoria Reznichenko" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, August 19, 2003 2:32 PM
Subject: Re: scroll problem


> "sanjay gupta" <[EMAIL PROTECTED]> wrote:
> >
> >I have lot of problem due to scrolling .e.g.  When  i
give
> > command " select * from table-name" .if the records are more than the
srceen
> > width then i am unable to see all the records ,only last records are
> > visible.Is there any command suffix to SQL commands so that i may able
to
> > see all the records,
>
> Use LIMIT clause to retrieve only certain number of records. You can also
use SELECT .. INTO OUTFILE command:
> http://www.mysql.com/doc/en/SELECT.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]
>
>


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

Re: mysql-standard-4.1.0-alpha-pc-linux-i686.tar.gz changed since April 12 2003?

[Victoria Reznichenko]

"Terence" <[EMAIL PROTECTED]> wrote:
> 
> Has the alpha release mysql-standard-4.1.0-alpha-pc-linux-i686.tar.gz
> changed since April 12 2003? Just want to save myself a few hours
> downloading...

4.1.1 will be released in September.

> 
> I am putting it into production shortly, and was wondering too if there's
> any news on the stable release of the same?
> 


-- 
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: punctuation in fulltext searching

[Egor Egorov]

Daniel Rossi <[EMAIL PROTECTED]> wrote:
> Hi i have just come across an issue where a word is not being searched up if there 
> is any punctuation ie. AMROZI'S will not be search upon if you type AMROZI , please 
> help

Take a look at * operator which you can use in BOOLEAN MODE.



-- 
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: SELECT with 1,000,000 ROWS

[Arno]

Un beau jour, Victoria Reznichenko a écrit:

> "Arno" <[EMAIL PROTECTED]> wrote:
>>
>> I have aproximately 1,000,000 rows and I would like to do some
>> query. The
>> first one is to get the number of row so I do :
>>
>> mysql> SELECT COUNT(*) FROM `Log`;
>> +--+
>>> COUNT(*) |
>> +--+
>>>   969129 |
>> +--+
>> 1 row in set (0.00 sec)
>>
>> mysql> SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49;
>> ++
>>> Nb |
>> ++
>>> 969129 |
>> ++
>> 1 row in set (1 min 20.99 sec)
>>
>> But like you can see it, it take a long with the WHERE clause. I use
>> Pentium
>> III at 650 Mhz with 48 Mb of ram. I think that the probleme come
>> from the
>> computer but I'm not shure (I need more RAM ?).
>
> SELECT COUNT(*) without WHERE clause for only one MyISAM/ISAM table
> works very quickly. Because number of rows for this table is stored.
>
> As to the second query, do you have an index on column 'ID'?
>

There are any index or key for this table. It's table for log so there are
any ID.

-- 
Arno


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

Re: SELECT with 1,000,000 ROWS

[Victoria Reznichenko]

"Arno" <[EMAIL PROTECTED]> wrote:
> 
> I have aproximately 1,000,000 rows and I would like to do some query. The
> first one is to get the number of row so I do :
> 
> mysql> SELECT COUNT(*) FROM `Log`;
> +--+
> | COUNT(*) |
> +--+
> |   969129 |
> +--+
> 1 row in set (0.00 sec)
> 
> mysql> SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49;
> ++
> | Nb |
> ++
> | 969129 |
> ++
> 1 row in set (1 min 20.99 sec)
> 
> But like you can see it, it take a long with the WHERE clause. I use Pentium
> III at 650 Mhz with 48 Mb of ram. I think that the probleme come from the
> computer but I'm not shure (I need more RAM ?).

SELECT COUNT(*) without WHERE clause for only one MyISAM/ISAM table works very 
quickly. Because number of rows for this table is stored.

As to the second query, do you have an index on column 'ID'?


-- 
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: SELECT with 1,000,000 ROWS

[Arno]

Un beau jour, Nick Gaugler a écrit:

>> Hello,
>>
>> I have aproximately 1,000,000 rows and I would like to do some
>> query. The first one is to get the number of row so I do :
>>
>> mysql> SELECT COUNT(*) FROM `Log`;
>> +--+
>>> COUNT(*) |
>> +--+
>>>   969129 |
>> +--+
>> 1 row in set (0.00 sec)
>
> MyISAM tables keep a specific count of the number of rows in the
> table, that is why this query is extremely fast.
>
>
>> mysql> SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49;
>> ++
>>> Nb |
>> ++
>>> 969129 |
>> ++
>> 1 row in set (1 min 20.99 sec)
>
> This query is slow, presumably, because MySQL must read all 969129
> rows off of the disk and count them, which will take some time
> depending on the size of the rows and the speed of the system.

Ok, I thought that but I was not sure.

>> But like you can see it, it take a long with the WHERE clause. I use
>> Pentium
>> III at 650 Mhz with 48 Mb of ram. I think that the probleme come
>> from the computer but I'm not shure (I need more RAM ?).
>
> Yes, your ram will make a difference because the file system will do
> caching.  You may want to read up on Indexes in MySQL and see how they
> are used to optimize queries and how they can also be cached in the
> key_buffer.
>
> http://www.mysql.com/doc/en/MySQL_indexes.html
>

Thanks for the link, I go to see it immediately.

PS: For information, I had forgotten to say that I used MySQL 4.1.0-alpha on
Linux
Slackware 9.0

--
Arno


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

RE: SELECT with 1,000,000 ROWS

[Nick Gaugler]

> Hello,
> 
> I have aproximately 1,000,000 rows and I would like to do some query. 
> The first one is to get the number of row so I do :
> 
> mysql> SELECT COUNT(*) FROM `Log`;
> +--+
> | COUNT(*) |
> +--+
> |   969129 |
> +--+
> 1 row in set (0.00 sec)

MyISAM tables keep a specific count of the number of rows in the table,
that is why this query is extremely fast.

 
> mysql> SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49;
> ++
> | Nb |
> ++
> | 969129 |
> ++
> 1 row in set (1 min 20.99 sec)

This query is slow, presumably, because MySQL must read all 969129 rows
off of the disk and count them, which will take some time depending on
the size of the rows and the speed of the system.

> But like you can see it, it take a long with the WHERE clause. I use 
> Pentium III at 650 Mhz with 48 Mb of ram. I think that the probleme 
> come from the computer but I'm not shure (I need more RAM ?).

Yes, your ram will make a difference because the file system will do
caching.  You may want to read up on Indexes in MySQL and see how they
are used to optimize queries and how they can also be cached in the
key_buffer.

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

 
> Thanks in advance.
> 
> --
> Arno


nickg


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

Re: scroll problem

[Victoria Reznichenko]

"sanjay gupta" <[EMAIL PROTECTED]> wrote:
> 
>I have lot of problem due to scrolling .e.g.  When  i give
> command " select * from table-name" .if the records are more than the srceen
> width then i am unable to see all the records ,only last records are
> visible.Is there any command suffix to SQL commands so that i may able to
> see all the records,

Use LIMIT clause to retrieve only certain number of records. You can also use SELECT 
.. INTO OUTFILE command:
http://www.mysql.com/doc/en/SELECT.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]

SELECT with 1,000,000 ROWS

[Arno]

Hello,

I have aproximately 1,000,000 rows and I would like to do some query. The
first one is to get the number of row so I do :

mysql> SELECT COUNT(*) FROM `Log`;
+--+
| COUNT(*) |
+--+
|   969129 |
+--+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49;
++
| Nb |
++
| 969129 |
++
1 row in set (1 min 20.99 sec)

But like you can see it, it take a long with the WHERE clause. I use Pentium
III at 650 Mhz with 48 Mb of ram. I think that the probleme come from the
computer but I'm not shure (I need more RAM ?).

Thanks in advance.

-- 
Arno


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

compiling mysql for arm on i386-linux

[Vangelis Pappas]

I am hopping to get mysql compiled for arm in an i386 redhat pc.

I have the cross compiler and some arm distribution, the version of the
gcc is 2.95.3,
and mysql distribution is 3.23.57. (I also tried 4.0.14 withthe same
results/error)

I do a:
./configure --prefix=/usr/local/arm/arm-linux --host=arm-linux
--build=i386-linux

and after a while I get an error:
===
:
checking "return type of sprintf"... configure: error: cannot run test
program while cross compiling
=

I would appreciate any help/clue on this one. It might be a mysql
error, but
even a confirmation on that would help.

Regards,

Vangelis Pappas


__
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com

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

mysql-standard-4.1.0-alpha-pc-linux-i686.tar.gz changed since April 12 2003?

[Terence]

Dear All,

Has the alpha release mysql-standard-4.1.0-alpha-pc-linux-i686.tar.gz
changed since April 12 2003? Just want to save myself a few hours
downloading...

I am putting it into production shortly, and was wondering too if there's
any news on the stable release of the same?

Thanks
Terence


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

scroll problem

[sanjay gupta]

Dear all,

I have lot of problem due to scrolling .e.g.  When  i give
command " select * from table-name" .if the records are more than the srceen
width then i am unable to see all the records ,only last records are
visible.Is there any command suffix to SQL commands so that i may able to
see all the records,

thanks in advance,

sanjay


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

Re: Oracle DBA here looking for advice on MySQL ....

[Andy Jackman]

I've used a lot of Oracle, some MS Access and I'm newish to MySQL. I
found it easy to write an abstraction layer for Ms Access and Oracle
despite their different approaches to some important things. I find
MySQL very sparse by comparison and I spend more time working round the
db than working with it. Unlike Oracle the richness and integrity of
language is simply missing - these people have lived without something
as useful as sub-queries for a long time. (The argument being that speed
and data integrity are all-important). It's more a file system than a
relational database. I know you asked about books rather than a
comparison of the products, but the software philosophy is reflected in
the documentation. If someone else pays you to be an Oracle Dba then I
bet you have at least a 10 foot shelf of comprehensive documentation.
This list is about as good as it gets (see your previous response).
There is a PDF copy of the manual somewhere and setting up MySql was
accompilshed by a colleague who wouldn't have known where to start with
Oracle, so it has that in its favour.

So, if you're thinking of migrating, think carefully! If I could get
Oracle to give me a sensible price (say USD 1000) to sell their db with
my product I would be out of here so fast. So far with mySQL i've
written my own database for a particular (simple) structure that it
wouldn't handle to my satisfaction; I've written my own date/time
routines to calculate things like seconds between 2 datetimes (despite a
wealth of datatime functions, this one isn't available unless you
convert to 'Unix' dates which expire in 2036) and I've written functions
to handle the fact that in 'C' all data is returned as strings rather
than as native data types. Sigh.
- Andy

"Johnson, Michael" wrote:
> 
> What is the best book on MySQL with regard
> to its Architecture and how it starts up, shutdowns,
> processes queries, rolls back data, etc etc. ?
> 
> I am not looking for a SQL book here.
> 
> What is the best My SQL book you have read ?
> 
> Thank you in advance.
> 
> Mike
> 
> --
> 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]