Re: Federated servers

[mysql]

Hi! 

Ok, but is it possible to have the same table on booth servers and split the 
data by rows? Like you can with ms-sql and the federated servers technique? 

Can you be more specific and maybe guide me to some documentation? 

Thanks! 

andy thomas writes: 

On Tue, 29 Jun 2004 [EMAIL PROTECTED] wrote: 

Hello! 

We are currently running with one big ms sql-server. 

Is it possible to do federated servers on mysql? 

What I want to do is to purchase one more server and split the work load on
the two servers.
Yes, you can use master/slave replication in MySQL to share the load
across multiple serevrs. 

Andy 


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

Re: MySQL and Macs

[Matt MacLeod]

Marc Liyanage has taken much of the effort out of php and mysql  
installation for the mac.

 http://www.entropy.ch/software/welcome.html

On 30 Jun 2004, at 04:41, Kieran Kelleher wrote:
Mac OS X is well supported by MySQL. MySQL is even preinstalled in Mac  
OS X Server, although we choose to ignore the OS X Server installation  
in favor of installing the binary so that development and servers all  
have the MySQL files in the same paths. Developers in our company run  
MySQL on our Powerbooks and we have a dedicated master XServe running  
MySQL with another XServe acting as a slave replicating to the master.

It is easy to install. Here are my installation notes for Panther (OS  
X 10.3)

http://homepage.mac.com/kelleherk/iblog/C711669388/E733468496/ 
index.html

The www.mysql.com site has information on Mac OS X too aswell as the  
read me file in the download.

Once you are up and running, I recommend CocoaMySQL as a complementary  
GUI (note complementary, you still need to do stuff on the command  
line ... easy after a little while) if you are still learning  
MySQL command line.

-Kieran
On Jun 29, 2004, at 9:14 PM, Jim Carwardine wrote:
Im new to the list and new to mySQL.  Im a Mac user and would like  
to set
up a DB on my Mac.  When I look at the MySQL web site, I cant seem  
to find
any info on what hardware can be used.  Can mySQL be run on a Mac?   
If so,
what do I need to know about how to set it up?  Can anyone point me  
to a
setup procedure?   Jim
--

OYF is... Highly resourceful people working together.
http://www.OwnYourFuture-net.com
Own Your Future Consulting Services Limited,
1959 Upper Water Street, Suite 407, Halifax, Nova Scotia. B3J 3N2
Info Line: 902-823-2477, Phone: 902-823-2339. Fax: 902-823-2139



--
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: Using REGEXP

[zzapper]

Michael

Ignoring my attempt at a query, I'll restate the problem

T1.devtxtpostcode contains full UK Postcodes eg OX14 5RA, OX14 5BH, Se1 1AH, etc
I want to check if a particular postcode is within a list of postcode areas,  these 
postcode areas
are naturally shorter ie ox14 5,ox14 6 etc. So I need to write a query that will check 
if OX14 5RA
matches one of the postcode areas 

If UK Postcodes had a fixed structure I could write

select * from ytbl_development as t1
where mid(t1.txtDevPostCode,1,5) in ('ox14 1','ox14 2','se1 1')

unfortunately I can't use mid as I can't guarantee that the length of a short postcode 
is 5 chars

How would you solve this problem

(The list of short Area Postcodes is generated by an earlier query)
zzapper (vim, cygwin, wiki  zsh)
--

vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?

http://www.vim.org/tips/tip.php?tip_id=305  Best of Vim Tips


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

Cant grant lock to root

[Scott Haneda]

Ok, I am stumped, mysql  4.0.18-standard, which was upgraded from 3.x, I ran
the fix_privs_something_or_other as well, has been working just fine for
ages.

Tonight I decided it would be a good idea to test one of the mysqldump's and
actually try to load it in.  Well, I cant.

I send in mysql -uuser-ppass datbase_name  back.sql and I get a
ERROR 1044 at line 28: Access denied for user: '[EMAIL PROTECTED]' to database 'zzz'

Strange part is, one table is made, then it chokes, I think it is getting
about as far as LOCK TABLES news WRITE;

So, I try and I try:
grant all on *.* to [EMAIL PROTECTED] identified by 'mypass';
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)

How do I get lock table granted to root as a global priv, I can not even add
other users as this priv since root is not allowed to do it either.
-- 
-
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]

Shutdown in progress error on Slaves.

[Cemal Dalar]

What is the procedure for altering tables in MASTER in a master, slave
replication configuration. For example I made some test and I see that if I
made a change in a table on master I got an error like Shutdown in
progress on SLAVES. I also tested to make slave stop and make the
change.. At this time no error occurred when I do slave start..
Also when I do slave stop;slave start after the error occurs.. I
starts working without any errors. Why this happens?

Thanks,

Best Regards,
Cemal Dalar a.k.a Jimmy
System Administrator  Web Developer
http://www.gittigidiyor.com  http://www.dalar.net


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

Re: MySQL and Macs

[Jim Carwardine]

Thanks so much to everyone for all your help... Jim

on 6/29/04 11:31 PM, Paul DuBois wrote:

 At 22:14 -0300 6/29/04, Jim Carwardine wrote:
 I'm new to the list and new to mySQL.  I'm a Mac user and would like to set
 up a DB on my Mac.  When I look at the MySQL web site, I can't seem to find
 any info on what hardware can be used.  Can mySQL be run on a Mac?  If so,
 what do I need to know about how to set it up?  Can anyone point me to a
 setup procedure?   Jim
 
 You need Mac OS X, not Mac OS.
 
 http://dev.mysql.com/doc/mysql/en/Installing.html
 http://dev.mysql.com/doc/mysql/en/Mac_OS_X_installation.html

-- 

OYF is... Highly resourceful people working together.
http://www.OwnYourFuture-net.com

Own Your Future Consulting Services Limited,
1959 Upper Water Street, Suite 407, Halifax, Nova Scotia. B3J 3N2
Info Line: 902-823-2477, Phone: 902-823-2339. Fax: 902-823-2139




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

threading problems in linux C client

[darren]

Hi all,

I am writing a multi-threaded client that requires shared access to a single MySQL 
connection. The environment is Redhat 7.3 with the patched mysql-3.23.58-2.71.i386.rpm 
installed and compiled with -l libmysqlclient_r.

The connection is made from the main thread and a MYSQL_RES* myres declared from the 
child thread is used to fetch results back to the thread. I get a Segmentation fault 
the first instance my child thread (only one) accesses MYSQL_RES through 
mysql_num_rows(myres). (gdb informed me that this is due to  mysql_num_rows from 
libmysqlclient_r.so.10)

I have read a few docs about writing MySQL multi-threaded clients and have called 
my_init() in my main prog and my_thread_init() at the first line of my thread handler.

Any ideas?


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

RE: Server tuning

[Chip Bell]

Victor, 

Thanks for all your help with the authentication issue.  I still have it, but at least 
I know what to look for.

My server is running RH9 with 3 gigs ram, PIII.  I have about 150 users at any given 
time and they're all coming via IMAP or HTTPD.  They all authenticate against MySQL 
but the mail is not stored there, it's in virtual domain files.  

My /etc/my.cnf

Set-variabe = max_connections=1000
  Key_buffer_size=512M
  Sort_buffer=20M
  Join_buffer=1M
Record_buffer=1M
  Max_allowed_packet=2M
  Table_cache=1024
  Innodb_buffer_pool_size=256M

Are these variables ok?  Reading my MySql manual, these were the best I could come up 
with.

Thanks!

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 4:41 PM
To: 'João Paulo Vasconcellos '; 'Mysql-general Mailing List '
Subject: RE: Server tuning

max_memory is roughly equivalent to  == key_buffer_size + (read_buffer_size
+ sort_buffer_size) * max_connections

-Original Message-
From: João Paulo Vasconcellos
To: Mysql-general Mailing List
Sent: 6/29/04 1:09 PM
Subject: Server tuning

Hello everybody,

  I am setting up a server to do POP/SMTP authentication using
vpopmail. I took a look at the variables at global my.cnf and wondered
what would be the best values for things like key_buffer,
read_buffer_size and table_cache. I searched the manual, but it does
not go deep in this subject, or I was not capable of finding the right
place. What I want to know is how can I calculate how much memory I
should give to key_buffer before I start to give away too much memory.
That's because I got only 1GB of RAM and I have about 34k domains in
my database, averaging from 8 to 15 accounts each. In a normal
situation, there are ~400 simultaneous clients. I was wanting to know
how can I estimate the memory usage for this scenario, if exists some
kind of formula to answer this, like:

clients * total size of key fields used in query

or if this is some thing that is clear in the manual (if so, my
apologies, but I could not find).

TIA,
-- 
João Paulo Vasconcellos

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

Best table structure

[Tom Chubb]

Please can someone let me know their opinion on the following.
I am new to MySQL and can't seem to find the right info anywhere.

I have written some code for submitting a top 20 music chart online.
I use the following to insert into mysql:

INSERT INTO chart (name, chartpos, artist, title, label) VALUES

('$name', '1', '$artist', '$title', '$label'),
('$name', '2', '$artist2', '$title2', '$label2'),
('$name', '3', '$artist3', '$title3', '$label3'),

 -- repeat til -

('$name', '20', '$artist20', '$title20', '$label20'),


Another page queries the table and sorts by name (multiple people submit
charts) and latest date.

My question is this:
Would I be better keeping this format and inserting multiple rows on each
submit, or to have one row for all 81 variables ($name, 20 x Position, 20 x
Artists, 20 x Titles, 20 x Labels.)
I know that the latter will be easier to query.

Also, without maintenance, the size of the table for the current method will
get extremely large. Will that affect server performance?

I am still a newbie, so plain explanations would be most appreciated.

Thanks very much in advance.


Tom

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




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

Re: Best table structure

[Richard Davey]

Hello Tom,

Wednesday, June 30, 2004, 1:26:52 PM, you wrote:

TC My question is this:
TC Would I be better keeping this format and inserting multiple rows on each
TC submit, or to have one row for all 81 variables ($name, 20 x Position, 20 x
TC Artists, 20 x Titles, 20 x Labels.)
TC I know that the latter will be easier to query.

Table design aside - you need to ask yourself this:

How often will this multiple insert be occurring? Sure, you are
performing 20 inserts one after the other, but if you only do this
once an hour then it really isn't as much of an issue as you think I
don't believe.

Try and balance out the admin / code headache that would be an 81 column
table vs. exactly how often this is even an issue anyway.

Best regards,

Richard Davey
-- 
 http://www.launchcode.co.uk - PHP Development Services
 I am not young enough to know everything. - Oscar Wilde



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

R: Best table structure

[Leonardo Francalanci]

 Would I be better keeping this format and inserting multiple rows on each
 submit, or to have one row for all 81 variables ($name, 20 x
 Position, 20 x
 Artists, 20 x Titles, 20 x Labels.)
 I know that the latter will be easier to query.

Are you sure? Why?


 Also, without maintenance, the size of the table for the current
 method will
 get extremely large. Will that affect server performance?

There is no difference: the size is the same for the 2 tables (actually the
current
one has one tinyint more which is also part of an index, but that is almost
nothing).

The current method (IMHO) is much better.
What if you want to perform a search for artist in the other case?
What if you want to change the design to top 10 instead of top 20?
And so on...



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

mysql-4.0.20 get my_thr_init.c:60: `pthread_destructor_t' undeclared (first use in this function) on HP-UX 10.20

[David Almada]

Hi,
I am trying to compile mysql-4.0.20 on my HP-UX 10.20 hppa2.0 with 
gcc-2.95.3 but it allways stop
this point:
gcc -DDEFAULT_BASEDIR=\/usr/local/mysql\ -DDATADIR=\/homed2/mysql\ 
-DDEFAULT_CHARSET_HOME=\/usr/local/mysql\ 
-DSHAREDIR=\/usr/local/mysql/share/mysql\ -DHAVE_CONFIG_H -I. -I. 
-I.. -I./../include -I../include -I.. -I.  -D__STDC_EXT__  -O3 
-DDBUG_OFF   -DHAVE_BROKEN_SNPRINTF -DSIGNALS_DONT_BREAK_READ 
-DDO_NOT_REMOVE_THREAD_WRAPPERS -DHPUX10 -DSIGNAL_WITH_VIO_CLOSE 
-DHAVE_BROKEN_PTHREAD_COND_TIMEDWAIT -DHAVE_POSIX1003_4a_MUTEX -c `test 
-f 'my_thr_init.c' || echo './'`my_thr_init.c
my_thr_init.c: In function `my_thread_global_init':
my_thr_init.c:60: `pthread_destructor_t' undeclared (first use in this 
function)
my_thr_init.c:60: (Each undeclared identifier is reported only once
my_thr_init.c:60: for each function it appears in.)
my_thr_init.c:60: parse error before `pthread_dummy'
my_thr_init.c: At top level:
my_thr_init.c:75: parse error before `'
my_thr_init.c:76: parse error before `'
my_thr_init.c:77: parse error before `'
my_thr_init.c:78: parse error before `'
my_thr_init.c:79: parse error before `'
my_thr_init.c:80: parse error before `'
my_thr_init.c:81: parse error before `'
my_thr_init.c:82: parse error before `'
my_thr_init.c:83: parse error before `'
gmake[2]: *** [my_thr_init.o] Error 1
gmake[2]: Leaving directory `/homed2/mysql/mysql-4.0.20/mysys'
gmake[1]: *** [all-recursive] Error 1
gmake[1]: Leaving directory `/homed2/mysql/mysql-4.0.20'
gmake: *** [all] Error 2
.
Could you guys help me to step over this problem.

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

Re: Using REGEXP

[SGreen]

I think I understand the problem now... You generate a list of postal
prefixes (the first portion of a full postal code) whose items may or may
not be all the same length. Then you could want to do either of two things:
1) compare a given full postal code to the list to see if matches any of
the short codes (the prefixes)
 - or -
2) Scan a list of all full codes to see which ones are covered by your
short codes

Bad news: In either case, you will most likely _not_ be able to use an
index in the search
Good news: this is a solvable problem

Instead of converting your previous query (the one that generates the
short codes) into a comma delimited list, I would put that list into a
temporary table

CREATE TEMPORARY TABLE tmpShortCodes
SELECT short_code
FROM  (--- this is your query that creates your short code
list.)

Then we can do a bulk comparison of the columns in tmpShortCodes to one or
many full codes. What makes this simpler to achieve is the fact that you
need to match only the beginning characters of the full code to an entire
short code. MySQL has 2 nearly identical functions for this: INSTR() and
LOCATE()

SELECT t1.*
FROM ytbl_development t1
INNER JOIN tmpShortCodes sc
ON INSTR(t1.txtDevPostCode, sc.short_code) =1

Like I said, it won't be fast but it should find the matches.

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   

  zzapper  

  [EMAIL PROTECTED]To:   [EMAIL PROTECTED]
 
  cc: 

  Sent by: newsFax to: 

  [EMAIL PROTECTED]Subject:  Re: Using REGEXP 
 
  rg  

   

   

  06/30/2004 04:31 

  AM   

   

   





Michael

Ignoring my attempt at a query, I'll restate the problem

T1.devtxtpostcode contains full UK Postcodes eg OX14 5RA, OX14 5BH, Se1
1AH, etc
I want to check if a particular postcode is within a list of postcode
areas,  these postcode areas
are naturally shorter ie ox14 5,ox14 6 etc. So I need to write a query that
will check if OX14 5RA
matches one of the postcode areas

If UK Postcodes had a fixed structure I could write

select * from ytbl_development as t1
where mid(t1.txtDevPostCode,1,5) in ('ox14 1','ox14 2','se1 1')

unfortunately I can't use mid as I can't guarantee that the length of a
short postcode is 5 chars

How would you solve this problem

(The list of short Area Postcodes is generated by an earlier query)
zzapper (vim, cygwin, wiki  zsh)
--

vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?

http://www.vim.org/tips/tip.php?tip_id=305  Best of Vim Tips


--
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: Cant grant lock to root

[Victor Pendleton]

do a show grants for root@'localhost' and look at it privileges. If your
root account has been altered you can started the database with the skip
grant table option, log, fix the grant tables, and flush privileges. 

-Original Message-
From: Scott Haneda
To: MySql
Sent: 6/30/04 4:30 AM
Subject: Cant grant lock to root

Ok, I am stumped, mysql  4.0.18-standard, which was upgraded from 3.x, I
ran
the fix_privs_something_or_other as well, has been working just fine for
ages.

Tonight I decided it would be a good idea to test one of the mysqldump's
and
actually try to load it in.  Well, I cant.

I send in mysql -uuser-ppass datbase_name  back.sql and I get a
ERROR 1044 at line 28: Access denied for user: '[EMAIL PROTECTED]' to database 'zzz'

Strange part is, one table is made, then it chokes, I think it is
getting
about as far as LOCK TABLES news WRITE;

So, I try and I try:
grant all on *.* to [EMAIL PROTECTED] identified by 'mypass';
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)

How do I get lock table granted to root as a global priv, I can not even
add
other users as this priv since root is not allowed to do it either.
-- 
-
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]

Re: Best table structure

[SGreen]

Tom,

I would keep your current fiveish-column design with the 20 inserts. It
will save you a LOT of headache in the future when you want to search for
things like particular artists or titles or to compute a sort of most
popular top twenty based on everyone else's rankings.

SELECT artist, title, SUM(25-chartpos)
FROM chart
GROUP BY artist, title
ORDER BY SUM(25-chartpos)
LIMIT 20

That statement would have been a coding nightmare to write if you stored a
chart in 81 columns in just one row. (I used 25 so that the index scores
run from 5 to 24. The highest total score _should_ represents a song that
is listed frequently and near the tops of the charts. There are MANY other
ways to index your songs to generate a composite ranking like this. You
will have to experiment to determine what works best for you. Of course,
spelling and naming differences will affect the outcome unless you took
pains to standardize those...)

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


   

  Tom Chubb  

  [EMAIL PROTECTED]To:   [EMAIL PROTECTED]  
 
  o.ukcc: 

   Fax to: 

  06/30/2004 08:26 Subject:  Best table structure  

  AM   

   

   





Please can someone let me know their opinion on the following.
I am new to MySQL and can't seem to find the right info anywhere.

I have written some code for submitting a top 20 music chart online.
I use the following to insert into mysql:

INSERT INTO chart (name, chartpos, artist, title, label) VALUES

('$name', '1', '$artist', '$title', '$label'),
('$name', '2', '$artist2', '$title2', '$label2'),
('$name', '3', '$artist3', '$title3', '$label3'),

 -- repeat til -

('$name', '20', '$artist20', '$title20', '$label20'),


Another page queries the table and sorts by name (multiple people submit
charts) and latest date.

My question is this:
Would I be better keeping this format and inserting multiple rows on each
submit, or to have one row for all 81 variables ($name, 20 x Position, 20 x
Artists, 20 x Titles, 20 x Labels.)
I know that the latter will be easier to query.

Also, without maintenance, the size of the table for the current method
will
get extremely large. Will that affect server performance?

I am still a newbie, so plain explanations would be most appreciated.

Thanks very much in advance.


Tom

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




--
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: Federated servers

[SGreen]

Have you looked at MySQL Cluster as a solution? Does this meet your
definition of federated?
http://www.mysql.com/products/cluster/

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   

  [EMAIL PROTECTED]
  
   To:   [EMAIL PROTECTED] 
 
  06/30/2004 03:19 cc:   [EMAIL PROTECTED] 

  AM   Fax to: 

   Subject:  Re: Federated servers 

   





Hi!

Ok, but is it possible to have the same table on booth servers and split
the
data by rows? Like you can with ms-sql and the federated servers technique?


Can you be more specific and maybe guide me to some documentation?

Thanks!

andy thomas writes:

 On Tue, 29 Jun 2004 [EMAIL PROTECTED] wrote:


 Hello!

 We are currently running with one big ms sql-server.

 Is it possible to do federated servers on mysql?

 What I want to do is to purchase one more server and split the work load
on
 the two servers.

 Yes, you can use master/slave replication in MySQL to share the load
 across multiple serevrs.

 Andy



--
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 and VBquestion - problem with query that returns 100000+ records

[Greg Zimmermack]

I have an SQL which looks something like:
SELECT S.FirstName, T.CreateDateTime, T.TranDateTime, P.FirstName, 
P.LastName, D.DiagnosisIDString, T.CheckNumber, T.StmtDesc, 
T.ServPayAdjIDString, T.PatientAmt, T.InsuranceAmt, T.ClaimID, 
T.AuditDateTime, T.TransactionType, T.ProviderID, PR.FirstName, PR.LastName 
FROM (((Provider  PR  INNER JOIN (Person P INNER JOIN Transaction T ON 
P.PersonID = T.PersonID) ON PR.ProviderID = T.ProviderID) LEFT JOIN 
PatientService PS ON T.TransactionID = PS.TransactionID) LEFT JOIN Diagnosis 
D ON PS.DiagnosisID1 = D.DiagnosisID) INNER JOIN Staff S ON T.CreatedBy = 
S.StaffID WHERE (((T.ServPayAdjIDString)'inibal') AND ((T.IsDeleted)=0)) 
ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC

This query is invoked from a Visual Basic 6.0 application that uses ADO  
ODBC to connect to a MySQL database.

The query runs fine on a small test database but when i run it on the 
production database the application just hangs up without generating any 
error messages.

The query is expected to return some 196000 records from the production 
database. Can this (large number of records) be causing the problem?

Is there any setting in MySQL that I could change ?
If i run the query on the server i can see upto 1000 records but no more.
This is my first attempt at MySQL hence the newbie type questions.
I should also mention that the application in question originally used an MS 
Access database and it can pull all the 196000 records from it.

Any help will be greatly appreciated.
Thanks
Greg
_
Get fast, reliable Internet access with MSN 9 Dial-up – now 3 months FREE! 
http://join.msn.click-url.com/go/onm00200361ave/direct/01/

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

RE: MySQL and VBquestion - problem with query that returns 100000 + records

[Victor Pendleton]

While the Visual Basic application is querying the database can you log into
the MySQL via the mysql monitor and do a show processlist to see if the
query is executing? If not I would suggest you view the mysql error log or
set up Visual basic to throw and error when it is getting disconnected. 

-Original Message-
From: Greg Zimmermack
To: [EMAIL PROTECTED]
Sent: 6/30/04 9:13 AM
Subject: MySQL and VBquestion - problem with query that returns 10+
records

I have an SQL which looks something like:

SELECT S.FirstName, T.CreateDateTime, T.TranDateTime, P.FirstName, 
P.LastName, D.DiagnosisIDString, T.CheckNumber, T.StmtDesc, 
T.ServPayAdjIDString, T.PatientAmt, T.InsuranceAmt, T.ClaimID, 
T.AuditDateTime, T.TransactionType, T.ProviderID, PR.FirstName,
PR.LastName 
FROM (((Provider  PR  INNER JOIN (Person P INNER JOIN Transaction T ON 
P.PersonID = T.PersonID) ON PR.ProviderID = T.ProviderID) LEFT JOIN 
PatientService PS ON T.TransactionID = PS.TransactionID) LEFT JOIN
Diagnosis 
D ON PS.DiagnosisID1 = D.DiagnosisID) INNER JOIN Staff S ON T.CreatedBy
= 
S.StaffID WHERE (((T.ServPayAdjIDString)'inibal') AND
((T.IsDeleted)=0)) 
ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC

This query is invoked from a Visual Basic 6.0 application that uses ADO
 
ODBC to connect to a MySQL database.

The query runs fine on a small test database but when i run it on the 
production database the application just hangs up without generating any

error messages.

The query is expected to return some 196000 records from the production 
database. Can this (large number of records) be causing the problem?

Is there any setting in MySQL that I could change ?

If i run the query on the server i can see upto 1000 records but no
more.

This is my first attempt at MySQL hence the newbie type questions.

I should also mention that the application in question originally used
an MS 
Access database and it can pull all the 196000 records from it.

Any help will be greatly appreciated.
Thanks
Greg

_
Get fast, reliable Internet access with MSN 9 Dial-up - now 3 months
FREE! 
http://join.msn.click-url.com/go/onm00200361ave/direct/01/


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

debug

[Bob Lockie]

I'm running a ton of sql statements to load data.
Is there a way to not display successes:
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0
and display failures and the query statement that failed?
There are 60 000+ of these and I'd ideally like to debug the inserts 
without actually doing them.

It fails on duplicate keys but I have no idea where the data is flawed.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Using REGEXP

[Michael Stassen]

In all of your examples so far, the short postcode ends with the first 
character after the space.  If that is true for all short postcodes, we 
could take the portion of the full postcode up to the first character after 
the space, then compare that to the list.  I think that's what you were 
hoping to do with the regexp.  Since your list is comma-separated, we can 
use FIND_IN_SET to compare the portion of the postcode to the list.  So,

 SELECT * FROM ytbl_development AS t1
 WHERE FIND_IN_SET(LEFT(t1.txtDevPostCode,LOCATE(' ',t1.txtDevPostCode)+1),
   'OX14 1','OX14 2','SE1 1');
This won't use an index on txtDevPostCode, so it will require a full table scan.
Michael
zzapper wrote:
Michael
Ignoring my attempt at a query, I'll restate the problem
T1.devtxtpostcode contains full UK Postcodes eg OX14 5RA, OX14 5BH, Se1 1AH, etc
I want to check if a particular postcode is within a list of postcode areas,  these postcode areas
are naturally shorter ie ox14 5,ox14 6 etc. So I need to write a query that will check if OX14 5RA
matches one of the postcode areas 

If UK Postcodes had a fixed structure I could write
select * from ytbl_development as t1
where mid(t1.txtDevPostCode,1,5) in ('ox14 1','ox14 2','se1 1')
unfortunately I can't use mid as I can't guarantee that the length of a short postcode 
is 5 chars
How would you solve this problem
(The list of short Area Postcodes is generated by an earlier query)
zzapper (vim, cygwin, wiki  zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305  Best of Vim Tips

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

Re: MySQL and VBquestion - problem with query that returns 100000+ records

[SGreen]

Your SQL syntax is fine, I just get a better view of what is joining to
what if I format it a little differently (I also avoid a lot of line
wrapping this way too):

SELECT S.FirstName
  , T.CreateDateTime
  , T.TranDateTime
  , P.FirstName
  , P.LastName
  , D.DiagnosisIDString
  , T.CheckNumber
  , T.StmtDesc
  , T.ServPayAdjIDString
  , T.PatientAmt
  , T.InsuranceAmt
  , T.ClaimID
  , T.AuditDateTime
  , T.TransactionType
  , T.ProviderID
  , PR.FirstName
  , PR.LastName
FROM Provider  PR
INNER JOIN Transaction T
  ON PR.ProviderID = T.ProviderID
INNER JOIN Person P
  ON P.PersonID = T.PersonID
INNER JOIN Staff S
  ON T.CreatedBy = S.StaffID
LEFT JOIN PatientService PS
  ON T.TransactionID = PS.TransactionID
LEFT JOIN Diagnosis D
  ON PS.DiagnosisID1 = D.DiagnosisID
WHERE T.ServPayAdjIDString)'inibal'
  AND T.IsDeleted=0
ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC

It may not be hung but just taking a *long* time to resolve this query. Did
the error log show anything? Do all of your tables have the appropriate
indexes to speed up your JOIN statements? Could you post the results of an
EXPLAIN on your query?  What version of MySQL are you using?

You might be able to speed this up by running it as two queries (using a
temp table to store the results of the first part of the query) rather than
one large query. Depending on how many columns you have in each table, your
internal tableset (the result of all of those joins) could contain hundreds
of columns. By splitting it into a couple of smaller steps you help keep
the number of unused columns per stage to a reasonable number.

You may also get more speed out of this if you move your WHERE constraints
into the appropriate ON clauses:

FROM Provider  PR
INNER JOIN Transaction T
  ON PR.ProviderID = T.ProviderID
  AND T.ServPayAdjIDString)'inibal'
  AND T.IsDeleted=0
INNER JOIN Person P
  ON P.PersonID = T.PersonID
  AND T.ServPayAdjIDString)'inibal'
  AND T.IsDeleted=0
INNER JOIN Staff S
  ON T.CreatedBy = S.StaffID
  AND T.ServPayAdjIDString)'inibal'
  AND T.IsDeleted=0
LEFT JOIN PatientService PS
  ON T.TransactionID = PS.TransactionID
  AND T.ServPayAdjIDString)'inibal'
  AND T.IsDeleted=0
LEFT JOIN Diagnosis D
  ON PS.DiagnosisID1 = D.DiagnosisID
ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC

It looks bulkier but if you do it this way you give the MySQL engine every
possible opportunity to pare down your JOINed datasets. Generally, the less
data you have to process, the faster everything will go. You *cannot*
always move a WHERE condition into an ON clause, but in this case it was
possible to do so with all of your WHERE conditions. This *is* a
case-by-case optimization.

And, last but not least, you may want to read up on an ongoing discussion
of ways to reduce the speed hit when using ORDER BY...DESC. It is a known
issue.

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   

  Greg Zimmermack

  [EMAIL PROTECTED]To:   [EMAIL PROTECTED]
 
  ail.com cc: 

   Fax to: 

  06/30/2004 10:13 Subject:  MySQL and VBquestion - 
problem with query that returns
  AM10+ records

   

   





I have an SQL which looks something like:

SELECT S.FirstName, T.CreateDateTime, T.TranDateTime, P.FirstName,
P.LastName, D.DiagnosisIDString, T.CheckNumber, T.StmtDesc,
T.ServPayAdjIDString, T.PatientAmt, T.InsuranceAmt, T.ClaimID,
T.AuditDateTime, T.TransactionType, T.ProviderID, PR.FirstName, PR.LastName

FROM (((Provider  PR  INNER JOIN (Person P INNER JOIN Transaction T ON
P.PersonID = T.PersonID) ON PR.ProviderID = T.ProviderID) LEFT JOIN
PatientService PS ON T.TransactionID = PS.TransactionID) LEFT JOIN
Diagnosis
D ON PS.DiagnosisID1 = D.DiagnosisID) INNER JOIN Staff S ON T.CreatedBy =
S.StaffID WHERE (((T.ServPayAdjIDString)'inibal') AND ((T.IsDeleted)=0))
ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC

This query is invoked from a Visual Basic 6.0 application that uses ADO 
ODBC to connect to a MySQL database.

Re: Using REGEXP

[Harald Fuchs]

In article [EMAIL PROTECTED],
[EMAIL PROTECTED] writes:

 SELECT t1.*
 FROM ytbl_development t1
 INNER JOIN tmpShortCodes sc
 ON INSTR(t1.txtDevPostCode, sc.short_code) =1

This is the same as

  SELECT t1.*
  FROM ytbl_development t1
  INNER JOIN tmpShortCodes sc
  ON t1.txtDevPostCode LIKE concat(sc.short_code, '%')

and this query would use indexes on txtDevPostCode and short_code.


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

RE: MySQL and VBquestion - problem with query that returns 100000+ records

[Greg Zimmermack]

Victor
Thanks for your reply.
I checked the processlist and it continues to execute even after the VB 
application is hung. I know the application is hung by checking its status 
in the Windows Task manager.

The 'hostname.err' file did not show any errors. Is there another file i 
should be looking at?

The error handler in in VB could not handle the error.
Is there some ODBC setting I need to optimize?
Any help will be appreciated.
Thanks
Greg

From: Victor Pendleton [EMAIL PROTECTED]
To: 'Greg Zimmermack ' [EMAIL PROTECTED],'[EMAIL PROTECTED] ' 
[EMAIL PROTECTED]
Subject: RE: MySQL and VBquestion - problem with query that returns 10+ 
records
Date: Wed, 30 Jun 2004 09:19:11 -0500

While the Visual Basic application is querying the database can you log 
into
the MySQL via the mysql monitor and do a show processlist to see if the
query is executing? If not I would suggest you view the mysql error log or
set up Visual basic to throw and error when it is getting disconnected.

-Original Message-
From: Greg Zimmermack
To: [EMAIL PROTECTED]
Sent: 6/30/04 9:13 AM
Subject: MySQL and VBquestion - problem with query that returns 10+
records
I have an SQL which looks something like:
SELECT S.FirstName, T.CreateDateTime, T.TranDateTime, P.FirstName,
P.LastName, D.DiagnosisIDString, T.CheckNumber, T.StmtDesc,
T.ServPayAdjIDString, T.PatientAmt, T.InsuranceAmt, T.ClaimID,
T.AuditDateTime, T.TransactionType, T.ProviderID, PR.FirstName,
PR.LastName
FROM (((Provider  PR  INNER JOIN (Person P INNER JOIN Transaction T ON
P.PersonID = T.PersonID) ON PR.ProviderID = T.ProviderID) LEFT JOIN
PatientService PS ON T.TransactionID = PS.TransactionID) LEFT JOIN
Diagnosis
D ON PS.DiagnosisID1 = D.DiagnosisID) INNER JOIN Staff S ON T.CreatedBy
=
S.StaffID WHERE (((T.ServPayAdjIDString)'inibal') AND
((T.IsDeleted)=0))
ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC
This query is invoked from a Visual Basic 6.0 application that uses ADO

ODBC to connect to a MySQL database.
The query runs fine on a small test database but when i run it on the
production database the application just hangs up without generating any
error messages.
The query is expected to return some 196000 records from the production
database. Can this (large number of records) be causing the problem?
Is there any setting in MySQL that I could change ?
If i run the query on the server i can see upto 1000 records but no
more.
This is my first attempt at MySQL hence the newbie type questions.
I should also mention that the application in question originally used
an MS
Access database and it can pull all the 196000 records from it.
Any help will be greatly appreciated.
Thanks
Greg
_
Get fast, reliable Internet access with MSN 9 Dial-up - now 3 months
FREE!
http://join.msn.click-url.com/go/onm00200361ave/direct/01/
--
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]

_
From ‘will you?’ to ‘I do,’ MSN Life Events is your resource for Getting 
Married. http://lifeevents.msn.com/category.aspx?cid=married
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: MySQL and VBquestion - problem with query that returns 100000 + records

[Victor Pendleton]

In ODBC applications, I have seen the application give a not responding
message when the database is taking longer than expected to return a result
set.
...
You say you see the query still executing? So the select statement is just
taking a long time to process? If this is so I would try to optimize the
query. Have you run an explain plan on the query to see the execution path?
...
You could also try using the ODBC trace feature in the 'ODBC Data Source
Administrator'

-Original Message-
From: Greg Zimmermack
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: 6/30/04 10:52 AM
Subject: RE: MySQL and VBquestion - problem with query that returns 10+
records

Victor
Thanks for your reply.
I checked the processlist and it continues to execute even after the VB 
application is hung. I know the application is hung by checking its
status 
in the Windows Task manager.

The 'hostname.err' file did not show any errors. Is there another file i

should be looking at?

The error handler in in VB could not handle the error.

Is there some ODBC setting I need to optimize?

Any help will be appreciated.
Thanks
Greg


From: Victor Pendleton [EMAIL PROTECTED]
To: 'Greg Zimmermack '
[EMAIL PROTECTED],'[EMAIL PROTECTED] ' 
[EMAIL PROTECTED]
Subject: RE: MySQL and VBquestion - problem with query that returns
10+ 
records
Date: Wed, 30 Jun 2004 09:19:11 -0500

While the Visual Basic application is querying the database can you log

into
the MySQL via the mysql monitor and do a show processlist to see if the
query is executing? If not I would suggest you view the mysql error log
or
set up Visual basic to throw and error when it is getting disconnected.

-Original Message-
From: Greg Zimmermack
To: [EMAIL PROTECTED]
Sent: 6/30/04 9:13 AM
Subject: MySQL and VBquestion - problem with query that returns 10+
records

I have an SQL which looks something like:

SELECT S.FirstName, T.CreateDateTime, T.TranDateTime, P.FirstName,
P.LastName, D.DiagnosisIDString, T.CheckNumber, T.StmtDesc,
T.ServPayAdjIDString, T.PatientAmt, T.InsuranceAmt, T.ClaimID,
T.AuditDateTime, T.TransactionType, T.ProviderID, PR.FirstName,
PR.LastName
FROM (((Provider  PR  INNER JOIN (Person P INNER JOIN Transaction T ON
P.PersonID = T.PersonID) ON PR.ProviderID = T.ProviderID) LEFT JOIN
PatientService PS ON T.TransactionID = PS.TransactionID) LEFT JOIN
Diagnosis
D ON PS.DiagnosisID1 = D.DiagnosisID) INNER JOIN Staff S ON T.CreatedBy
=
S.StaffID WHERE (((T.ServPayAdjIDString)'inibal') AND
((T.IsDeleted)=0))
ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC

This query is invoked from a Visual Basic 6.0 application that uses ADO

ODBC to connect to a MySQL database.

The query runs fine on a small test database but when i run it on the
production database the application just hangs up without generating
any

error messages.

The query is expected to return some 196000 records from the production
database. Can this (large number of records) be causing the problem?

Is there any setting in MySQL that I could change ?

If i run the query on the server i can see upto 1000 records but no
more.

This is my first attempt at MySQL hence the newbie type questions.

I should also mention that the application in question originally used
an MS
Access database and it can pull all the 196000 records from it.

Any help will be greatly appreciated.
Thanks
Greg

_
Get fast, reliable Internet access with MSN 9 Dial-up - now 3 months
FREE!
http://join.msn.click-url.com/go/onm00200361ave/direct/01/


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


_
From 'will you?' to 'I do,' MSN Life Events is your resource for Getting

Married. http://lifeevents.msn.com/category.aspx?cid=married

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

Re: Using REGEXP

[SGreen]

Harold,

THANK YOU!! As I was writing that bit of code I had that creepy feeling
that knew that I was overlooking something simple. I guess I win the
D'OH prize for today.  8-D. (Maybe I shouldn't write any more SQL until
*after* the coffee kicks in..hmmm...)

Nice catch!
Shawn


   

  Harald Fuchs 

  [EMAIL PROTECTED]To:   [EMAIL PROTECTED]
 
  .netcc: 

  Sent by: newsFax to: 

  [EMAIL PROTECTED]Subject:  Re: Using REGEXP 
 
  rg  

   

   

  06/30/2004 11:45 

  AM   

  Please respond to

  hf517

   

   





In article
[EMAIL PROTECTED],

[EMAIL PROTECTED] writes:

 SELECT t1.* FROM ytbl_development t1
 INNER JOIN tmpShortCodes sc
 ON INSTR(t1.txtDevPostCode, sc.short_code) =1

This is the same as

  SELECT t1.*  FROM ytbl_development t1
  INNER JOIN tmpShortCodes sc
  ON t1.txtDevPostCode LIKE concat(sc.short_code, '%')

and this query would use indexes on txtDevPostCode and short_code.


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

making lower case then first char to upper case?

[Aaron Wolski]

Hi Guys,
 
I'm trying to figure out of this is possible. I know I could do it in
PHP but I am dealing with a ton of records and would rather put the
processing on the DB than PHP/client side.
 
Question is. can I do a SELECT query on a column that changes all the
results to lower case and THEN changes the first character of each
result to an upper case?
 
Example:
 
Currently in DB: AARON
to Lowercase: aaron
to Uppercase: Aaron
 
 
Any idea on if I can do this and how I might approach it?
 
Thanks so much
 
Aaron
 

Minitoring mysqld process activities

[Mohammad shojatalab]

Hi all,
I'm running a small database as backend of a relatively quiet website,
This is the version Im running:
mysql  Ver 11.15 Distrib 3.23.47, for dec-osf5.1 (alphaev6)
yesterday for the first time, mysql process response time dropped 
significantly and when I monitored running processes I realized that 
mysql process is very busy, utilizing %95 of CPU time,
and I was unable to refresh or reload,... so I shut it down and restart 
it and everything works fine again.

I was wondering If there is a way to monitor what mysql process is doing 
at anytime 

Thanks in advanced for your answers.
Regards
Mohammad
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Replication corruption and 64 bit mysql

[Matthew Kent]

For the record/list archives,

The solution seems to have been upgrading to Fedora Core 2
kernel-smp-2.6.6-1.435.x86_64.rpm. What fix it contained that affected
my case... I'm not sure :)

Been running okay for 18 hours at high volume!

- Matt

 -Original Message-
 From: Matthew Kent
 Sent: Monday, June 28, 2004 4:11 PM
 To: [EMAIL PROTECTED]
 Subject: Replication corruption and 64 bit mysql
 
 After several long days trying to fix this I'm running out of ideas.
 
 Master: RedHat 7.3 kernel 2.4, MySQL 4.0.20 32 bit (mysql.com rpm) -
 Slave: Fedora Core 2 64 bit kernel 2.6.5, MySQL-Max-4.0.20-0 64 bit
 (mysql.com rpm)
 
 In a varying amount of time after a few hundred thousand queries
 replication dies with
 
 snippy
 040625 16:19:12  Error in Log_event::read_log_event(): 'Event too
 small', data_len: 0, event_type: 0
 040625 16:19:12  Error reading relay log event: slave SQL thread
 aborted
 because of I/O error
 /snipped
 
 Using instructions from Sasha Pachev
 http://groups.google.ca/groups?hl=enlr=ie=UTF-
 8selm=c400pk%245pd%241%
 40FreeBSD.csie.NCTU.edu.tw I've looked at the binlog on the slave and
 can indeed verify a large chunk of empty space and that query is
 indeed
 logged on the master.
 
 Fun part is that it does work when I point our 32 bit master to
 different 32 bit slave. So I know it's not a problem with our old
 servers, just this fancy new one.
 
 So far I've
 
 - Tried a different master (we have a pool of 5 similar servers to use
 as a master).
 - Tried 32-bit server instead of 64-bit Max on the slave (couldn't get
 64 bit non-Max to start at all, would just dump).
 - Tried swapping nic to a different brand.
 - Used tcpdump to attempt to spot any network level issues.
 - Tried pointing the binlogs on the master to another local disk
 separate from the data.
 - Examined the changelogs for the nic drivers.
 - Googled this to no end.
 
 With no luck.
 
 I'm open for suggestions.
 
 I suppose the next step is to install core 2 32-bit and try again.
 
 Thanks,
 
 Matthew Kent \ SA \ bravenet.com \ 1-250-954-3203 ext 108
 
 --
 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]

how to switch off logging?

[J S]

Hi,
I would like to switch off the logging that creates the large files below 
(in the mysql data directory)
Could someone tell me how to do this please?

Thanks,
js.
# ls -l
total 5418648
-rw-rw   1 mysqlmysql  25088 Jun 28 14:27 ib_arch_log_00
-rw-rw   1 mysqlmysql5242880 Jun 29 11:47 ib_logfile0
-rw-rw   1 mysqlmysql5242880 Jun 28 14:27 ib_logfile1
-rw-rw   1 mysqlmysql10485760 Jun 29 11:47 ibdata1
-rw-rw   1 mysqlmysql   2345 Jun 30 17:28 innodb.status.21716
-rw-rw   1 mysqlmysql   2345 Jun 28 15:37 innodb.status.4980
-rw-rw   1 mysqlmysql   2345 Jun 28 20:32 innodb.status.5022
-rw-rw   1 mysqlmysql   2345 Jun 28 21:49 innodb.status.58892
drwxr-s---   2 mysqlmysql512 Jun 28 14:24 mysql
drwx--S---   2 mysqlmysql   1024 Jun 30 13:50 proxy_logs
-rw-rw   1 mysqlmysql1074696691 Jun 30 15:09 rsl156-bin.001
-rw-rw   1 mysqlmysql1073819496 Jun 30 17:22 rsl156-bin.002
-rw-rw   1 mysqlmysql60454 Jun 30 17:27 rsl156-bin.003
-rw-rw   1 mysqlmysql 51 Jun 30 17:22 rsl156-bin.index
-rw-rw   1 mysqlmysql   8596 Jun 30 14:03 rsl156.err
-rw-rw   1 mysqlmysql  6 Jun 29 11:46 rsl156.pid
drwxr-s---   2 mysqlmysql512 May 14 10:54 test
# pwd
/proxydb/mysql/data
_
It's fast, it's easy and it's free. Get MSN Messenger today! 
http://www.msn.co.uk/messenger

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

Re: making lower case then first char to upper case?

[Wesley Furgiuele]

Someone else hopefully has something more efficient:
UPDATE table SET field = CONCAT( UPPER( LEFT( field, 1 ) ), LOWER( 
SUBSTRING( field, 2 ) ) )

Wes
On Jun 30, 2004, at 12:46 PM, Aaron Wolski wrote:
Hi Guys,
I'm trying to figure out of this is possible. I know I could do it in
PHP but I am dealing with a ton of records and would rather put the
processing on the DB than PHP/client side.
Question is. can I do a SELECT query on a column that changes all the
results to lower case and THEN changes the first character of each
result to an upper case?
Example:
Currently in DB: AARON
to Lowercase: aaron
to Uppercase: Aaron
Any idea on if I can do this and how I might approach it?
Thanks so much
Aaron

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

AW: making lower case then first char to upper case?

[Freddie Sorensen]

Aaron,

How about something like :

SELECT CONCAT(UCASE(LEFT(FieldName, 1)),
LCASE(RIGHT(FieldName,LENGTH(FieldName)-1)))

Right out of my head, I didn't count all the parantheses :-)

Freddie

 -Ursprüngliche Nachricht-
 Von: Aaron Wolski [mailto:[EMAIL PROTECTED] 
 Gesendet: Mittwoch, 30. Juni 2004 18:46
 An: [EMAIL PROTECTED]
 Betreff: making lower case then first char to upper case?
 
 Hi Guys,
  
 I'm trying to figure out of this is possible. I know I could 
 do it in PHP but I am dealing with a ton of records and would 
 rather put the processing on the DB than PHP/client side.
  
 Question is. can I do a SELECT query on a column that changes 
 all the results to lower case and THEN changes the first 
 character of each result to an upper case?
  
 Example:
  
 Currently in DB: AARON
 to Lowercase: aaron
 to Uppercase: Aaron
  
  
 Any idea on if I can do this and how I might approach it?
  
 Thanks so much
  
 Aaron
  
 



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

Re: how to switch off logging?

[Jeff Smelser]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Wednesday 30 June 2004 12:10 pm, J S wrote:
 Hi,

 I would like to switch off the logging that creates the large files below
 (in the mysql data directory)
 Could someone tell me how to do this please?

take out log-bin from my.cnf. But you better not have replication as that is 
the files used for replication.

Jeff
- -- 
===
Jabber: tradergt@(smelser.org|jabber.org)
Quote:   Stale tagline. Go get some new ones.
 While you're at it, would you get me a beer?
===
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFA4vY1ld4MRA3gEwYRAhsaAJ926pG0lmu+B9EbzM+dY/cTm+35sgCeO72h
mw7XIXyBQ1i2uo8TiIWNwVI=
=0S5D
-END PGP SIGNATURE-

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

Re: how to switch off logging?

[J S]

On Wednesday 30 June 2004 12:10 pm, J S wrote:
 Hi,

 I would like to switch off the logging that creates the large files 
below
 (in the mysql data directory)
 Could someone tell me how to do this please?

take out log-bin from my.cnf. But you better not have replication as that 
is
the files used for replication.

Jeff
- --
Thanks for your reply.
By replication do you mean do I have tables with duplicate rows? If that's 
the case then I do have one table with duplicate rows.

_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!  
http://toolbar.msn.co.uk/

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

query problem

[auslander]

using mysql 4.0.x
please review the following sql then see below for the problem:
DROP TABLE IF EXISTS inbound_fax_info;
CREATE TABLE inbound_fax_info (
 fax_id int(10) unsigned NOT NULL auto_increment,
 barcode varchar(100) default '',
 document_id int(10) unsigned default NULL,
 department_id int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (fax_id)
) TYPE=MyISAM;
INSERT INTO inbound_fax_info VALUES (1,'12345',1,5);
INSERT INTO inbound_fax_info VALUES (2,'67890',2,5);
INSERT INTO inbound_fax_info VALUES (3,'',NULL,5);
INSERT INTO inbound_fax_info VALUES (4,'',NULL,8);
DROP TABLE IF EXISTS outbound_fax_info;
CREATE TABLE outbound_fax_info (
 fax_id int(10) unsigned NOT NULL auto_increment,
 barcode varchar(100) default '',
 customer_name varchar(100) default NULL,
 customer_fax varchar(100) default NULL,
 document_id int(10) unsigned default NULL,
 department_id int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (fax_id)
) TYPE=MyISAM;
INSERT INTO outbound_fax_info VALUES (1,'','Bob Smith1','555-1313',1,5);
INSERT INTO outbound_fax_info VALUES (2,'','Bob Smith2','555-1414',2,6);
INSERT INTO outbound_fax_info VALUES (3,'','Bob Smith3','555-1515',3,7);
INSERT INTO outbound_fax_info VALUES (4,'12345','Bob Smith','555-1212',1,5);
INSERT INTO outbound_fax_info VALUES (5,'67890','Joe Blow','555-3355',2,5);
INSERT INTO outbound_fax_info VALUES (6,'45678','John 
Google','555-',3,5);
INSERT INTO outbound_fax_info VALUES (7,'','Bob Smith4','555-1616',1,5);
INSERT INTO outbound_fax_info VALUES (8,'','Bob Smith5','555-1717',2,6);
INSERT INTO outbound_fax_info VALUES (9,'','Bob Smith6','555-1818',3,7);

DROP TABLE IF EXISTS inbound_fax_info_tmp;
CREATE TABLE inbound_fax_info_tmp (
 fax_id int(10) unsigned NOT NULL auto_increment,
 barcode varchar(100) default '',
 document_id int(10) unsigned NOT NULL default '0',
 department_id int(10) unsigned NOT NULL default '0',
 customer_name varchar(100) NOT NULL default '',
 customer_fax varchar(100) NOT NULL default '',
 PRIMARY KEY  (fax_id)
) TYPE=MyISAM;
INSERT INTO inbound_fax_info_tmp (
 fax_id,
 barcode,
 document_id,
 department_id,
 customer_name,
 customer_fax)
SELECT
 a.fax_id,
 a.barcode,
 b.document_id,
 a.department_id,
 b.customer_name,
 b.customer_fax
FROM
 inbound_fax_info a
LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode);
what i am attempting to do is create a 3rd table that contains all the 
info from inbound_fax_info plus a couple columns from outbound_fax_info 
based on the barcode column they both contain.  problem comes when the 
barcode is '' (blank).  barcodes can be blank in outbound and inbound 
(for specific reasons).  is there a way to write this INSERT INTO query 
where it will select the appropriate data from inbound and outbound if a 
barcode exists and insert into tmp inbound, otherwise just insert a new 
row with inbound data and defaults only in tmp inbound?

any help would be appreciated
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Index problem

[Oropeza Querejeta, Alejandro]

Hi, i'm trying to create an index on a table with 199 million records.
The problem is that is taking too long (8 hours and is not yet
finnished).
 
does anyone have any idea?
 
the server is 2Xeon 2.8 gigs with 6 Gb of ram.
 
Best regards
 
Alejandro

Re: how to switch off logging?

[Jeff Smelser]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Wednesday 30 June 2004 12:46 pm, J S wrote:

 Thanks for your reply.
 By replication do you mean do I have tables with duplicate rows? If that's
 the case then I do have one table with duplicate rows.

No.. Replication, meaning, you have the same data being transfered from a 
server to another using myself. You don't seem to know what it is, so I doubt 
your doing it.

- -- 
===
Jabber: tradergt@(smelser.org|jabber.org)
Quote:  For a good time, type rm -rf ~
===
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFA4wHqld4MRA3gEwYRAqbYAKCtEMNcwTc/4DAkHmxXzKlGlaqxowCdGLYA
y0Oqe5/Tfeh4P/elKLw4VtI=
=OGYE
-END PGP SIGNATURE-

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

Re: Index problem

[David Griffiths]

What's the definition of the table? IE are you indexing an INT, VARCHAR, 
etc?

What's the definition of the index? Is it unique, composite, etc?
What's the storage engine in use? InnoDB? MyISAM?
Can you show the relevant parts of your my.cnf file?
What operating system are you using?
David
Oropeza Querejeta, Alejandro wrote:
Hi, i'm trying to create an index on a table with 199 million records.
The problem is that is taking too long (8 hours and is not yet
finnished).
does anyone have any idea?
the server is 2Xeon 2.8 gigs with 6 Gb of ram.
Best regards
Alejandro
 


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

Re: query problem

[SGreen]

Eliminate the rows from outbound_fax_info where the barcode is blank. The
result of the JOIN will be all of the rows of inbound_fax_info matched up
to:
  a) information form outbound_fax_info except where the barcodes match
  b) blank columns where the barcodes didn't match.

Use the COALESCE() function (it returns the first non-null value from a
list of values) and you gain the ability to replace missing values with
something else.

INSERT INTO inbound_fax_info_tmp (
  fax_id,
  barcode,
  document_id,
  department_id,
  customer_name,
  customer_fax)
SELECT
  a.fax_id,
  a.barcode,
  b.document_id,
  a.department_id,
  COALESCE(b.customer_name,'no customer')
  COALESCE(b.customer_fax,'no customer fax')
FROM
  inbound_fax_info a
LEFT JOIN outbound_fax_info b
  on ucase(a.barcode) = ucase(b.barcode)
  AND b.barcode  ''

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   

  auslander

  [EMAIL PROTECTED]To:   [EMAIL PROTECTED]
 
  ay.rr.com   cc: 

   Fax to: 

  06/30/2004 01:50 Subject:  query problem 

  PM   

   

   





using mysql 4.0.x
please review the following sql then see below for the problem:

DROP TABLE IF EXISTS inbound_fax_info;
CREATE TABLE inbound_fax_info (
  fax_id int(10) unsigned NOT NULL auto_increment,
  barcode varchar(100) default '',
  document_id int(10) unsigned default NULL,
  department_id int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (fax_id)
) TYPE=MyISAM;

INSERT INTO inbound_fax_info VALUES (1,'12345',1,5);
INSERT INTO inbound_fax_info VALUES (2,'67890',2,5);
INSERT INTO inbound_fax_info VALUES (3,'',NULL,5);
INSERT INTO inbound_fax_info VALUES (4,'',NULL,8);

DROP TABLE IF EXISTS outbound_fax_info;
CREATE TABLE outbound_fax_info (
  fax_id int(10) unsigned NOT NULL auto_increment,
  barcode varchar(100) default '',
  customer_name varchar(100) default NULL,
  customer_fax varchar(100) default NULL,
  document_id int(10) unsigned default NULL,
  department_id int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (fax_id)
) TYPE=MyISAM;

INSERT INTO outbound_fax_info VALUES (1,'','Bob Smith1','555-1313',1,5);
INSERT INTO outbound_fax_info VALUES (2,'','Bob Smith2','555-1414',2,6);
INSERT INTO outbound_fax_info VALUES (3,'','Bob Smith3','555-1515',3,7);
INSERT INTO outbound_fax_info VALUES (4,'12345','Bob
Smith','555-1212',1,5);
INSERT INTO outbound_fax_info VALUES (5,'67890','Joe Blow','555-3355',2,5);
INSERT INTO outbound_fax_info VALUES (6,'45678','John
Google','555-',3,5);
INSERT INTO outbound_fax_info VALUES (7,'','Bob Smith4','555-1616',1,5);
INSERT INTO outbound_fax_info VALUES (8,'','Bob Smith5','555-1717',2,6);
INSERT INTO outbound_fax_info VALUES (9,'','Bob Smith6','555-1818',3,7);

DROP TABLE IF EXISTS inbound_fax_info_tmp;
CREATE TABLE inbound_fax_info_tmp (
  fax_id int(10) unsigned NOT NULL auto_increment,
  barcode varchar(100) default '',
  document_id int(10) unsigned NOT NULL default '0',
  department_id int(10) unsigned NOT NULL default '0',
  customer_name varchar(100) NOT NULL default '',
  customer_fax varchar(100) NOT NULL default '',
  PRIMARY KEY  (fax_id)
) TYPE=MyISAM;

INSERT INTO inbound_fax_info_tmp (
  fax_id,
  barcode,
  document_id,
  department_id,
  customer_name,
  customer_fax)
SELECT
  a.fax_id,
  a.barcode,
  b.document_id,
  a.department_id,
  b.customer_name,
  b.customer_fax
FROM
  inbound_fax_info a
LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode);


what i am attempting to do is create a 3rd table that contains all the
info from inbound_fax_info plus a couple columns from outbound_fax_info
based on the barcode column they both contain.  problem comes when the
barcode is '' (blank).  barcodes can be blank in outbound and inbound
(for specific reasons).  is there a way to write this INSERT INTO query
where it will select the appropriate data from inbound and outbound if a
barcode exists and insert into tmp inbound, otherwise just insert a new
row with inbound data and defaults only in tmp inbound?

any help would be appreciated

Chris

--
MySQL 

RE: Minitoring mysqld process activities

[Victor Pendleton]

Have you checked the slow query log and the error log? 

-Original Message-
From: Mohammad shojatalab
To: [EMAIL PROTECTED]
Sent: 6/30/04 11:58 AM
Subject: Minitoring mysqld process activities

Hi all,

I'm running a small database as backend of a relatively quiet website,
This is the version Im running:
mysql  Ver 11.15 Distrib 3.23.47, for dec-osf5.1 (alphaev6)

yesterday for the first time, mysql process response time dropped 
significantly and when I monitored running processes I realized that 
mysql process is very busy, utilizing %95 of CPU time,
and I was unable to refresh or reload,... so I shut it down and restart 
it and everything works fine again.

I was wondering If there is a way to monitor what mysql process is doing

at anytime 


Thanks in advanced for your answers.

Regards
Mohammad


-- 
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: how to switch off logging?

[Jeff Smelser]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Wednesday 30 June 2004 01:09 pm, Jeff Smelser wrote:
 On Wednesday 30 June 2004 12:46 pm, J S wrote:
  Thanks for your reply.
  By replication do you mean do I have tables with duplicate rows? If
  that's the case then I do have one table with duplicate rows.

 No.. Replication, meaning, you have the same data being transfered from a
 server to another using myself. You don't seem to know what it is, so I
 doubt your doing it.

Myself? thats mysql.. Sorry, bad typo
- -- 
===
Jabber: tradergt@(smelser.org|jabber.org)
Quote:  124672900742 eye rays can't be wrong!
===
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFA4wdWld4MRA3gEwYRAuFTAJ4r4HHLLDv0Cz9kl84d2lnYUDykPACg2FKM
VbkOsu70uTMBbNra5gbhNNM=
=LrYZ
-END PGP SIGNATURE-

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

Merge multiple databases into one

[Murray Scoulding]

Hello,
I am trying to see if replication (or some open source software) can 
help me

I have a multiple external databases that have the exact same table 
structure. They need to be merged into a central database on a nightly 
basis. The only difference between the external and central database 
table structure is that every table in the central database has an 
additional column indicating which external database the row came from.

I've looked into replication on the mysql website (also google groups) 
and it seems that replication only supports a loop type structure ( 
ABCA).  I am using more of a A  C  B type structure. I am trying to 
stay away from using queries and inserts into the central database if 
possible.

If anyone has dealth with this scenario before or know a good place to 
look, please let me know.

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

Re: query problem

[auslander]

Actually, i figured it out.  don't know why it was so hard to see it.  
all i did was change:

LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode)
to:
LEFT JOIN outbound_fax_info b on (b.barcode != '' and ucase(a.barcode = 
b.barcode).

if barcode was blank in outbound (b) then the data was skipped and only 
the data from inbound (a) was inserted into the new table.

thanks for your assistance tho. much appreciated.
[EMAIL PROTECTED] wrote:
Eliminate the rows from outbound_fax_info where the barcode is blank. The
result of the JOIN will be all of the rows of inbound_fax_info matched up
to:
 a) information form outbound_fax_info except where the barcodes match
 b) blank columns where the barcodes didn't match.
Use the COALESCE() function (it returns the first non-null value from a
list of values) and you gain the ability to replace missing values with
something else.
INSERT INTO inbound_fax_info_tmp (
 fax_id,
 barcode,
 document_id,
 department_id,
 customer_name,
 customer_fax)
SELECT
 a.fax_id,
 a.barcode,
 b.document_id,
 a.department_id,
 COALESCE(b.customer_name,'no customer')
 COALESCE(b.customer_fax,'no customer fax')
FROM
 inbound_fax_info a
LEFT JOIN outbound_fax_info b
 on ucase(a.barcode) = ucase(b.barcode)
 AND b.barcode  ''
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

  
 auslander
 [EMAIL PROTECTED]To:   [EMAIL PROTECTED] 
 ay.rr.com   cc: 
  Fax to: 
 06/30/2004 01:50 Subject:  query problem 
 PM   
  
  


using mysql 4.0.x
please review the following sql then see below for the problem:
DROP TABLE IF EXISTS inbound_fax_info;
CREATE TABLE inbound_fax_info (
 fax_id int(10) unsigned NOT NULL auto_increment,
 barcode varchar(100) default '',
 document_id int(10) unsigned default NULL,
 department_id int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (fax_id)
) TYPE=MyISAM;
INSERT INTO inbound_fax_info VALUES (1,'12345',1,5);
INSERT INTO inbound_fax_info VALUES (2,'67890',2,5);
INSERT INTO inbound_fax_info VALUES (3,'',NULL,5);
INSERT INTO inbound_fax_info VALUES (4,'',NULL,8);
DROP TABLE IF EXISTS outbound_fax_info;
CREATE TABLE outbound_fax_info (
 fax_id int(10) unsigned NOT NULL auto_increment,
 barcode varchar(100) default '',
 customer_name varchar(100) default NULL,
 customer_fax varchar(100) default NULL,
 document_id int(10) unsigned default NULL,
 department_id int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (fax_id)
) TYPE=MyISAM;
INSERT INTO outbound_fax_info VALUES (1,'','Bob Smith1','555-1313',1,5);
INSERT INTO outbound_fax_info VALUES (2,'','Bob Smith2','555-1414',2,6);
INSERT INTO outbound_fax_info VALUES (3,'','Bob Smith3','555-1515',3,7);
INSERT INTO outbound_fax_info VALUES (4,'12345','Bob
Smith','555-1212',1,5);
INSERT INTO outbound_fax_info VALUES (5,'67890','Joe Blow','555-3355',2,5);
INSERT INTO outbound_fax_info VALUES (6,'45678','John
Google','555-',3,5);
INSERT INTO outbound_fax_info VALUES (7,'','Bob Smith4','555-1616',1,5);
INSERT INTO outbound_fax_info VALUES (8,'','Bob Smith5','555-1717',2,6);
INSERT INTO outbound_fax_info VALUES (9,'','Bob Smith6','555-1818',3,7);
DROP TABLE IF EXISTS inbound_fax_info_tmp;
CREATE TABLE inbound_fax_info_tmp (
 fax_id int(10) unsigned NOT NULL auto_increment,
 barcode varchar(100) default '',
 document_id int(10) unsigned NOT NULL default '0',
 department_id int(10) unsigned NOT NULL default '0',
 customer_name varchar(100) NOT NULL default '',
 customer_fax varchar(100) NOT NULL default '',
 PRIMARY KEY  (fax_id)
) TYPE=MyISAM;
INSERT INTO inbound_fax_info_tmp (
 fax_id,
 barcode,
 document_id,
 department_id,
 customer_name,
 customer_fax)
SELECT
 a.fax_id,
 a.barcode,
 b.document_id,
 a.department_id,
 b.customer_name,
 b.customer_fax
FROM
 inbound_fax_info a
LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode);
what i am attempting to do is create a 3rd table that contains all the
info from inbound_fax_info plus a couple columns from outbound_fax_info
based on the barcode column they both contain.  problem comes when the
barcode is 

What fornt programing language should be used with MySql?

[Li, Dahuan]

Hello,  Everyone,

I'd like to get some comments form you.

I have developed some window database applications from MS Access with VB.
My boss wanted me use mysql to develope new web database application and
rewrite my current database application using MySql.
Any one can point me the right direction to go?

I used ODBC to get Mysql database from Access. I looked at the database from
SQLyog. The relationship gone and always get error to set it.


Thanks for any comments or suggestion.

Daphne

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

Packet Errors

[Mike Blezien]

Hello,
I recently noticed this error in our mysql error log file:

Aborted connection 5439 to db: 'database_name' user: 'someuser' host:
`localhost' (Got an error reading communication packets)
---
we're running MySQL 4.0.20, for pc-linux (i686) on a RH7.3, build from the 
standard RPM's

it seems this just standard recently from what I can see in the logs. Is there 
something we can do to eliminate this or prevent it, if possible. ??

TIA
MikemickaloBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: debug

[Andrew Pattison]

The way I do this is within PHP is to echo the value stored in mysql_error
after each SQL statement. If you're not using PHP then this probably doesn't
help though ;-)

Cheers

Andrew.

- Original Message - 
From: Bob Lockie [EMAIL PROTECTED]
To: MySQL Mailing List [EMAIL PROTECTED]
Sent: Wednesday, June 30, 2004 3:38 PM
Subject: debug


 I'm running a ton of sql statements to load data.

 Is there a way to not display successes:
 Query OK, 1 row affected (0.02 sec)
 Records: 1  Duplicates: 0  Warnings: 0

 and display failures and the query statement that failed?

 There are 60 000+ of these and I'd ideally like to debug the inserts
 without actually doing them.

 It fails on duplicate keys but I have no idea where the data is flawed.

 -- 
 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: Minitoring mysqld process activities

[Andrew Pattison]

I've not seen this on MySQL but under Informix 7.24 on Solaris 2.6 I noticed
a similar problem when doing a lot of number crunching and transaction
logging was turned on. If you are running with transaction logging switched
on then you might want to try turning it off. For some reason the database
seems to get backed up writing to the log and this stalls everything else on
the system, iowait figures go sky-high and I can only stop the process by
doing 'kill -9 pid.

Cheers

Andrew.

- Original Message - 
From: Victor Pendleton [EMAIL PROTECTED]
To: 'Mohammad shojatalab ' [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, June 30, 2004 7:27 PM
Subject: RE: Minitoring mysqld process activities


 Have you checked the slow query log and the error log?

 -Original Message-
 From: Mohammad shojatalab
 To: [EMAIL PROTECTED]
 Sent: 6/30/04 11:58 AM
 Subject: Minitoring mysqld process activities

 Hi all,

 I'm running a small database as backend of a relatively quiet website,
 This is the version Im running:
 mysql  Ver 11.15 Distrib 3.23.47, for dec-osf5.1 (alphaev6)

 yesterday for the first time, mysql process response time dropped
 significantly and when I monitored running processes I realized that
 mysql process is very busy, utilizing %95 of CPU time,
 and I was unable to refresh or reload,... so I shut it down and restart
 it and everything works fine again.

 I was wondering If there is a way to monitor what mysql process is doing

 at anytime 


 Thanks in advanced for your answers.

 Regards
 Mohammad


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

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





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

Mysql growing pains, 4 days to create index on one table!

[matt ryan]

Rebuilding index takes 3 1/2 days!!!  Growing pains with mysql..
I've got 2 dedicated servers, each with a slave, all run 32gig 15k rpm
raid 5 on u320 perc raid cards, dell 2600/4600's with single channel
backplanes (new ones will have dual channel)
All have 2 gig of ram, but I've never seen mysql use more than 600mb
of ram.
The servers handle huge loads, each day there are 30 1-2 gig files
loaded into large tables, total mysql data size is 96 gig, the large
tables are 2-6 gig.
Inserts are done on dupe key ignore, this takes hours on the large
files, it barely keeps up with input files.
At the bottom of this post I've got the mysql.ini config lines, any
suggestions are welcome, I'm already beyond the mysql huge sample
they used to include in the program.
Sample table that I load is as follows.
each day I get 40 % new records on the text file, the input file is
normally 20mb, once a week I get one that's 1-2 gig, these take all
day to load.
I need more multiple column indexes, as some querys return millions of
rows that must be scanned, but the index size already exceeds the
table size, and the combinations I need would result in an myi that's
5x larger than the data itself.
Here's an example of the speed problem, the index was corrupt so I
dropped all and recreated, rather than a myisam repair. I think 3 days
is a little excessive for a table that's only 3.428 gig, index is
2.729 gig.  I cant remove the primary key, as it keeps duplicates out
of the system, the input files are from old database's, we use mysql
to store the data for the web frontend, mostly done in ASP, most
queries take less than a second, unforuntatly we have big queries that
take way more than the IIS timeout setting all the time, but no way
around it, I cant add more indexes without making it even slower :(

I cant tell if it's mysql that's the problem, or the hardware, Here's a 
screenshot of the disk IO, if I copy a file while mysql is doing the 
build index, the io shoots way up, which tells me, mysql is NOT maxing 
out the drives, and it's also not maxing out the memory.

Unless it's doing lots and lots of seeks on the drive, which is harder 
to test using perfmon, are there any mysql test setups that would help 
identify where the bottleneck is?

screenshot of disk io usage
http://www.geekopolis.com/pics/diskio.jpg
I'm all out of ideas, other than switching to another db, and the table 
 indexes split across drives, maybe a 2 channel setup, 4 drives per 
channel, each 4 is a separate raid 5 setup, one holds data one holds 
indexes, cant do this with mysql though

mysql alter table hood_stat add primary key
(dic,niin,fr_ric,don,suf,dte_txn,sta) , add index `don` (`don`), add
index `niin` (`niin`), add index `stor` (`stor`), add index `dic`
(`dic`), add index `ctasc` (`ctasc`);
Query OK, 45449534 rows affected (3 days 19 hours 6 min 34.94 seconds
Records: 45449534 Duplicates: 0 Warnings: 0
CREATE TABLE `hood_stat` (
`dic` char(3) NOT NULL default '',
`fr_ric` char(3) NOT NULL default '',
`niin` char(11) NOT NULL default '',
`ui` char(2) NOT NULL default '',
`qty` char(5) NOT NULL default '',
`don` char(14) NOT NULL default '',
`suf` char(1) NOT NULL default '',
`dte_txn` char(5) NOT NULL default '',
`ship_to` char(3) NOT NULL default '',
`sta` char(2) NOT NULL default '',
`lst_sos` char(3) NOT NULL default '',
`esd` char(4) NOT NULL default '',
`stor` char(3) NOT NULL default '',
`d_t` char(4) NOT NULL default '',
`ctasc` char(10) NOT NULL default '',
PRIMARY KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta` ),
KEY `don` (`don`),
KEY `niin` (`niin`),
KEY `stor` (`stor`),
KEY `dic` (`dic`),
KEY `ctasc` (`ctasc`)
) TYPE=MyISAM MAX_ROWS=10 PACK_KEYS=1
skip-locking
set-variable=delay_key_write=ALL
set-variable= key_buffer_size=1500M
set-variable=join_buffer=512M
set-variable= max_allowed_packet=256M
set-variable= table_cache=512
set-variable= sort_buffer=256M
set-variable=tmp_table_size=400M
set-variable= record_buffer=512M
set-variable= thread_cache=8
set-variable=myisam_sort_buffer_size=256M
myisam-recover=BACKUP,FORCE
set-variable=read_buffer_size=512M
set-variable=interactive_timeout=7200
set-variable=wait_timeout=7200
log-bin
server-id=1
replicate-do-db=finlog
set-variable=open-files-limit=500
set-variable=table-cache=400
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query problem

[SGreen]

What is wrong with me today?!?! I explained myself incorrectly:

a) information from outbound_fax_info where the barcodes DO match.

Sorry all!!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


   

  [EMAIL PROTECTED]

   To:   auslander [EMAIL PROTECTED] 

  06/30/2004 02:22 cc:   [EMAIL PROTECTED] 

  PM   Fax to: 

   Subject:  Re: query problem 

   






Eliminate the rows from outbound_fax_info where the barcode is blank. The
result of the JOIN will be all of the rows of inbound_fax_info matched up
to:
  a) information form outbound_fax_info except where the barcodes match
  b) blank columns where the barcodes didn't match.

Use the COALESCE() function (it returns the first non-null value from a
list of values) and you gain the ability to replace missing values with
something else.

INSERT INTO inbound_fax_info_tmp (
  fax_id,
  barcode,
  document_id,
  department_id,
  customer_name,
  customer_fax)
SELECT
  a.fax_id,
  a.barcode,
  b.document_id,
  a.department_id,
  COALESCE(b.customer_name,'no customer')
  COALESCE(b.customer_fax,'no customer fax')
FROM
  inbound_fax_info a
LEFT JOIN outbound_fax_info b
  on ucase(a.barcode) = ucase(b.barcode)
  AND b.barcode  ''

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine




  auslander

  [EMAIL PROTECTED]To:
[EMAIL PROTECTED]
  ay.rr.com   cc:

   Fax to:

  06/30/2004 01:50 Subject:  query problem

  PM







using mysql 4.0.x
please review the following sql then see below for the problem:

DROP TABLE IF EXISTS inbound_fax_info;
CREATE TABLE inbound_fax_info (
  fax_id int(10) unsigned NOT NULL auto_increment,
  barcode varchar(100) default '',
  document_id int(10) unsigned default NULL,
  department_id int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (fax_id)
) TYPE=MyISAM;

INSERT INTO inbound_fax_info VALUES (1,'12345',1,5);
INSERT INTO inbound_fax_info VALUES (2,'67890',2,5);
INSERT INTO inbound_fax_info VALUES (3,'',NULL,5);
INSERT INTO inbound_fax_info VALUES (4,'',NULL,8);

DROP TABLE IF EXISTS outbound_fax_info;
CREATE TABLE outbound_fax_info (
  fax_id int(10) unsigned NOT NULL auto_increment,
  barcode varchar(100) default '',
  customer_name varchar(100) default NULL,
  customer_fax varchar(100) default NULL,
  document_id int(10) unsigned default NULL,
  department_id int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (fax_id)
) TYPE=MyISAM;

INSERT INTO outbound_fax_info VALUES (1,'','Bob Smith1','555-1313',1,5);
INSERT INTO outbound_fax_info VALUES (2,'','Bob Smith2','555-1414',2,6);
INSERT INTO outbound_fax_info VALUES (3,'','Bob Smith3','555-1515',3,7);
INSERT INTO outbound_fax_info VALUES (4,'12345','Bob
Smith','555-1212',1,5);
INSERT INTO outbound_fax_info VALUES (5,'67890','Joe Blow','555-3355',2,5);
INSERT INTO outbound_fax_info VALUES (6,'45678','John
Google','555-',3,5);
INSERT INTO outbound_fax_info VALUES (7,'','Bob Smith4','555-1616',1,5);
INSERT INTO outbound_fax_info VALUES (8,'','Bob Smith5','555-1717',2,6);
INSERT INTO outbound_fax_info VALUES (9,'','Bob Smith6','555-1818',3,7);

DROP TABLE IF EXISTS inbound_fax_info_tmp;
CREATE TABLE inbound_fax_info_tmp (
  fax_id int(10) unsigned NOT NULL auto_increment,
  barcode varchar(100) default '',
  document_id int(10) unsigned NOT NULL default '0',
  department_id int(10) unsigned NOT NULL default '0',
  customer_name varchar(100) NOT NULL default '',
  customer_fax varchar(100) NOT NULL default '',
  PRIMARY KEY  (fax_id)
) TYPE=MyISAM;

INSERT INTO inbound_fax_info_tmp (
  fax_id,
  barcode,
  document_id,
  department_id,
  customer_name,
  customer_fax)
SELECT
  a.fax_id,
  a.barcode,
  b.document_id,
  a.department_id,
  b.customer_name,
  b.customer_fax
FROM
  inbound_fax_info a
LEFT JOIN outbound_fax_info b on ucase(a.barcode) = ucase(b.barcode);


what i am attempting to do is create a 3rd table that contains all the
info from inbound_fax_info plus a couple columns from outbound_fax_info
based on the barcode column they both contain.  problem comes when the
barcode is '' (blank).  barcodes can be blank in outbound and inbound
(for specific reasons).  is there a way 

Re: Replication corruption and 64 bit mysql

[Andrew Pattison]

I've a funny feeling the kernel authors re-wrote much of the SMP code for
2.6 with the aim of getting it to scale better to 8 processor systems, so I
would expect there to be a few stray bugs in it. You could always downgrade
to 2.4 if it doesn't work out ;-)

Cheers

Andrew.

- Original Message - 
From: Matthew Kent [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, June 30, 2004 6:08 PM
Subject: RE: Replication corruption and 64 bit mysql


For the record/list archives,

The solution seems to have been upgrading to Fedora Core 2
kernel-smp-2.6.6-1.435.x86_64.rpm. What fix it contained that affected
my case... I'm not sure :)

Been running okay for 18 hours at high volume!

- Matt

 -Original Message-
 From: Matthew Kent
 Sent: Monday, June 28, 2004 4:11 PM
 To: [EMAIL PROTECTED]
 Subject: Replication corruption and 64 bit mysql

 After several long days trying to fix this I'm running out of ideas.

 Master: RedHat 7.3 kernel 2.4, MySQL 4.0.20 32 bit (mysql.com rpm) -
 Slave: Fedora Core 2 64 bit kernel 2.6.5, MySQL-Max-4.0.20-0 64 bit
 (mysql.com rpm)

 In a varying amount of time after a few hundred thousand queries
 replication dies with

 snippy
 040625 16:19:12  Error in Log_event::read_log_event(): 'Event too
 small', data_len: 0, event_type: 0
 040625 16:19:12  Error reading relay log event: slave SQL thread
 aborted
 because of I/O error
 /snipped

 Using instructions from Sasha Pachev
 http://groups.google.ca/groups?hl=enlr=ie=UTF-
 8selm=c400pk%245pd%241%
 40FreeBSD.csie.NCTU.edu.tw I've looked at the binlog on the slave and
 can indeed verify a large chunk of empty space and that query is
 indeed
 logged on the master.

 Fun part is that it does work when I point our 32 bit master to
 different 32 bit slave. So I know it's not a problem with our old
 servers, just this fancy new one.

 So far I've

 - Tried a different master (we have a pool of 5 similar servers to use
 as a master).
 - Tried 32-bit server instead of 64-bit Max on the slave (couldn't get
 64 bit non-Max to start at all, would just dump).
 - Tried swapping nic to a different brand.
 - Used tcpdump to attempt to spot any network level issues.
 - Tried pointing the binlogs on the master to another local disk
 separate from the data.
 - Examined the changelogs for the nic drivers.
 - Googled this to no end.

 With no luck.

 I'm open for suggestions.

 I suppose the next step is to install core 2 32-bit and try again.

 Thanks,

 Matthew Kent \ SA \ bravenet.com \ 1-250-954-3203 ext 108

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

[Oropeza Querejeta, Alejandro]

Below are the answers

Best Regards
-Mensaje original-
De: David Griffiths [mailto:[EMAIL PROTECTED] 
Enviado el: Miércoles, 30 de Junio de 2004 01:29 p.m.
Para: [EMAIL PROTECTED]
Asunto: Re: Index problem


What's the definition of the table? IE are you indexing an INT, VARCHAR, 
etc?
3 fields
Folio, Vacante, int
Folio2 char(10)

What's the definition of the index? Is it unique, composite, etc?
Nonunique, single column (folio2)

What's the storage engine in use? InnoDB? MyISAM?

Myisam

Can you show the relevant parts of your my.cnf file?

I have the standard My-huge.cnf

What operating system are you using?

Redhat Linux 7.3 
David

Oropeza Querejeta, Alejandro wrote:

Hi, i'm trying to create an index on a table with 199 million records. 
The problem is that is taking too long (8 hours and is not yet 
finnished).
 
does anyone have any idea?
 
the server is 2Xeon 2.8 gigs with 6 Gb of ram.
 
Best regards
 
Alejandro

  



-- 
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: Packet Errors

[David Griffiths]

These errors could mean a connection timed out, or a mysql-client didn't 
properly close the connection, or possibly a network error.

I went to mysql.com and looked in the searchable docs:
http://dev.mysql.com/doc/mysql/en/Communication_errors.html

If |Aborted connections| messages appear in the error log, the cause can 
be any of the following:

   * The client program did not call |mysql_close()| before exiting.
   * The client had been sleeping more than |wait_timeout| or
 |interactive_timeout| seconds without issuing any requests to the
 server. See section 5.2.3 Server System Variables
 http://dev.mysql.com/doc/mysql/en/Server_system_variables.html.
   * The client program ended abruptly in the middle of a data transfer.
When any of these things happen, the server increments the 
|Aborted_clients| status variable.


Those searchable docs are very handy for looking up error codes, etc.
David.
Mike Blezien wrote:
Hello,
I recently noticed this error in our mysql error log file:

Aborted connection 5439 to db: 'database_name' user: 'someuser' host:
`localhost' (Got an error reading communication packets)
---
we're running MySQL 4.0.20, for pc-linux (i686) on a RH7.3, build from 
the standard RPM's

it seems this just standard recently from what I can see in the logs. 
Is there something we can do to eliminate this or prevent it, if 
possible. ??

TIA
MikemickaloBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=


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

Re: Minitoring mysqld process activities

[auslander]

You could also use 'show full processlist;' at the mysql prompt.
even better is an app called mytop whis looks/acts like top but is 
specifically used for mysql.

Victor Pendleton wrote:
Have you checked the slow query log and the error log? 
you could also che
-Original Message-
From: Mohammad shojatalab
To: [EMAIL PROTECTED]
Sent: 6/30/04 11:58 AM
Subject: Minitoring mysqld process activities

Hi all,
I'm running a small database as backend of a relatively quiet website,
This is the version Im running:
mysql  Ver 11.15 Distrib 3.23.47, for dec-osf5.1 (alphaev6)
yesterday for the first time, mysql process response time dropped 
significantly and when I monitored running processes I realized that 
mysql process is very busy, utilizing %95 of CPU time,
and I was unable to refresh or reload,... so I shut it down and restart 
it and everything works fine again.

I was wondering If there is a way to monitor what mysql process is doing
at anytime 
Thanks in advanced for your answers.
Regards
Mohammad
 


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

Creating users for local databases

[BillB]

Hi,
This is a dumb newbie question, so please bear with me:) I'm trying to
connect to an existing database called 'billblac_recovery'.
I want the username to be 'billblac_bill' and the password to be 'wgb'. I'm
having no luck with the CLI
or MySQLAdministrator. I'm using MySQL 4.1, upgraded from 3.x. I'd LIKE to
grant full permissions to ALL databases on the server
localhost to this user. Is there a single command that will do this?

Also, Can someone recommend a good GUI tool that allows me to CRUD
databases, tables etc., query, modify and delete records, add and remove
permissions - perform ALL necessary operations for a MySQL Server and its
associated databases VISUALLY? Someone recommended NAVICAT. Anymore
suggestions? Also, can someone recommend a good 3rd party book with a lot of
CLI examples for Win 2K? I have the MySQL Bible
but the screenshots are illegible.

In Java, when trying to connect to my database with the following statement:
Class.forName(org.gjt.mm.mysql.Driver).newInstance();
con =
DriverManager.getConnection(jdbc:mysql://localhost/billblac_recovery?user=b
illblac_billpassword=wgb);

The error is:
Access denied for user: '@localhost' to database 'billblac_recovery'. I
understand the message. I can't seem to
get around it.

Thanks,
Bill


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

RE: What fornt programing language should be used with MySql?

[James]

It really boils down to what is your favorite programming language? You
say you are using vb? If so, there isn't a lot that would need to be
changed in your current vb app (if that is what you have and not vba in
access).  The key will be to get the odbc driver, and use ado (at least
that's what I use).  I wrote a few cross-db apps to accept using mysql,
mssql, and access.  There are a few things different that would need
changing (cursors is one thing that comes to mind). HTH.
Oh, 
Here's a connect string to the db (with ado)
DBConnectionString=driver={MySQL ODBC 3.51
Driver};server=[server-ip-or-name];port=3306;uid=[username];pwd=[passwor
d];database=[database];stmt=;OPTION=17346

Watch for line wraps, and what's inside the []'s are what you need added

Thanks, 
James 


-Original Message-
From: Li, Dahuan [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 30, 2004 2:29 PM
To: [EMAIL PROTECTED]
Subject: What fornt programing language should be used with MySql?

Hello,  Everyone,

I'd like to get some comments form you.

I have developed some window database applications from MS Access with
VB.
My boss wanted me use mysql to develope new web database application and
rewrite my current database application using MySql.
Any one can point me the right direction to go?

I used ODBC to get Mysql database from Access. I looked at the database
from
SQLyog. The relationship gone and always get error to set it.


Thanks for any comments or suggestion.

Daphne

-- 
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: What fornt programing language should be used with MySql?

[SGreen]

Daphne,

There are two or three parts to this application. It all depends on how you
are designing it. You put so many things in one sentence that I cannot
figure out what you have already done and what you have been asked to do.

You mentioned web database. Does that mean that you will have this
application structure -

user  web browser  web server  database

- or is it -

user  vb application  database

- where  the communication between your VB application is and the server is
accomplished over a network? If you need to develop the first scenario:
What web server will you be using? Will it be scripted or CGI? If scripted,
in what language?

Please, take your time and explain your project requirements and your
available resources.

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



   

  Li, Dahuan 

  [EMAIL PROTECTED]To:   [EMAIL PROTECTED]
 
  a.Uscc: 

   Fax to: 

  06/30/2004 02:28 Subject:  What fornt programing 
language should be used with MySql? 
  PM   

   

   





Hello,  Everyone,

I'd like to get some comments form you.

I have developed some window database applications from MS Access with VB.
My boss wanted me use mysql to develope new web database application and
rewrite my current database application using MySql.
Any one can point me the right direction to go?

I used ODBC to get Mysql database from Access. I looked at the database
from
SQLyog. The relationship gone and always get error to set it.


Thanks for any comments or suggestion.

Daphne

--
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: Packet Errors

[Mike Blezien]

Thx's :)

David Griffiths wrote:
These errors could mean a connection timed out, or a mysql-client didn't 
properly close the connection, or possibly a network error.

I went to mysql.com and looked in the searchable docs:
http://dev.mysql.com/doc/mysql/en/Communication_errors.html

If |Aborted connections| messages appear in the error log, the cause can 
be any of the following:

   * The client program did not call |mysql_close()| before exiting.
   * The client had been sleeping more than |wait_timeout| or
 |interactive_timeout| seconds without issuing any requests to the
 server. See section 5.2.3 Server System Variables
 http://dev.mysql.com/doc/mysql/en/Server_system_variables.html.
   * The client program ended abruptly in the middle of a data transfer.
When any of these things happen, the server increments the 
|Aborted_clients| status variable.


Those searchable docs are very handy for looking up error codes, etc.
David.
Mike Blezien wrote:
Hello,
I recently noticed this error in our mysql error log file:

Aborted connection 5439 to db: 'database_name' user: 'someuser' host:
`localhost' (Got an error reading communication packets)
---
we're running MySQL 4.0.20, for pc-linux (i686) on a RH7.3, build from 
the standard RPM's

it seems this just standard recently from what I can see in the logs. 
Is there something we can do to eliminate this or prevent it, if 
possible. ??

TIA
MikemickaloBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=



--
MikemickaloBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL Security

[Sheraz]

How can i achieve Security acpect in mysql?
How secure can we make transactions over internet for
3306 ?


Thanks
Sak



__
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

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

RE: Query on large text field

[Schalk]

Hey there everyone

 

I have tried a couple of things but would like to know what suggestions
people on the list may have. What would be the best query term or string is
to use when searching a field, using a keyword(s), in the database that
contains a large amount of text, for example an article's content?

 

Any pointers and suggestions will be welcomed.

 

Kind Regards

Schalk Neethling

Web Developer.Designer.Programmer.President

Volume4.Development.Multimedia.Branding

emotionalize.conceptualize.visualize.realize

Tel: +27125468436

Fax: +27125468436

email:[EMAIL PROTECTED]

web: www.volume4.co.za

 

This message contains information that is considered to be sensitive or
confidential and may not be forwarded or disclosed to any other party
without the permission of the sender. If you received this message in error,
please notify me immediately so that I can correct and delete the original
email. Thank you.

 

Re: Index problem

[David Griffiths]

So the table is,
folio int
vacante int
folio2 char(10)
and the table type is MyISAM
create index some_index on table(folio2);
and the table has about 200,000,000 rows.
MyISAM creates a file per table for table data, and for index data. You 
can find the files created underneath the mysql install directory in a 
directory with the database name (mysql/var if you are using 
source-compiled and mysql/data if you are using pre-compiled binaries). 
To quote the docs,

Each |MyISAM| table is stored on disk in three files. The files have 
names that begin with the table name and have an extension to indicate 
the file type. An `.frm' file stores the table definition. The data file 
has an `.MYD' (MYData) extension. The index file has an `.MYI' (MYIndex) 
extension,

What's the max file size on your system? I suspect it's greater than 2 
gigabytes if you have 200 million rows. But something to check.

You might be exceeding the capabilities of the MyISAM storage engine, or 
the version of MySQL you are using (which version *are* you using? 3.23 
or a 4.0.x, or 4.1?).

Can you reduce the size of the index by creating a partial index, like
create index some_index on table(folio2(5));
to only index part of the data?
David
Oropeza Querejeta, Alejandro wrote
Below are the answers
Best Regards
-Mensaje original-
De: David Griffiths [mailto:[EMAIL PROTECTED] 
Enviado el: Miércoles, 30 de Junio de 2004 01:29 p.m.
Para: [EMAIL PROTECTED]
Asunto: Re: Index problem

What's the definition of the table? IE are you indexing an INT, VARCHAR, 
etc?
3 fields
Folio, Vacante, int
Folio2 char(10)

What's the definition of the index? Is it unique, composite, etc?
Nonunique, single column (folio2)
What's the storage engine in use? InnoDB? MyISAM?
Myisam
Can you show the relevant parts of your my.cnf file?
I have the standard My-huge.cnf
What operating system are you using?
Redhat Linux 7.3 
David

Oropeza Querejeta, Alejandro wrote:
 

Hi, i'm trying to create an index on a table with 199 million records. 
The problem is that is taking too long (8 hours and is not yet 
finnished).

does anyone have any idea?
the server is 2Xeon 2.8 gigs with 6 Gb of ram.
Best regards
Alejandro

   


 


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

Syncing DB's

[John Nichel]

Hi list,
  I'm currently in the middle of planning a server migration and am 
trying to figure out how I am going to keep the databases in sync.  The 
problem lies in DNS.  When I make the DNS change to switch our site from 
one host to another, it's not automatic.  For a period of a few days, 
customers will be hitting the site on both boxes, depending on if their 
ISP has updated their DNS as of yet.  So my problem is that box 1 will 
still be taking orders (and inserting them into it's local MySQL 
database), at the same time box 2 will be taking different orders (and 
inserting them into box 2's local MySQL db).  I've thought about just 
configuring box 1 to use the db on box 2, but testing has proven this to 
be a problem (timeouts from time to time).  Is there a way I can keep 
these two db's in sync all the while having them accept data which isn't 
put into both, while the DNS filters down?  TIA.

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

No suitable driver

[Sridevi Salagrama]

Hi,

I have just down loaded the mysql connector/j version 3.0.14-
production.  I have set the classpath for the jar file and 
the url that I am using for the driver is 
com.mysql.jdbc.driver.  

I have not been succesful.

Can any body please tell me what i might be doing wrong.

Thanks

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

RE: Query on large text field

[Victor Pendleton]

If the table type is MyISAM, have you tried full text indexing?

-Original Message-
From: Schalk
To: [EMAIL PROTECTED]
Sent: 6/30/04 2:31 PM
Subject: RE: Query on large text field

Hey there everyone

 

I have tried a couple of things but would like to know what suggestions
people on the list may have. What would be the best query term or string
is
to use when searching a field, using a keyword(s), in the database that
contains a large amount of text, for example an article's content?

 

Any pointers and suggestions will be welcomed.

 

Kind Regards

Schalk Neethling

Web Developer.Designer.Programmer.President

Volume4.Development.Multimedia.Branding

emotionalize.conceptualize.visualize.realize

Tel: +27125468436

Fax: +27125468436

email:[EMAIL PROTECTED]

web: www.volume4.co.za

 

This message contains information that is considered to be sensitive or
confidential and may not be forwarded or disclosed to any other party
without the permission of the sender. If you received this message in
error,
please notify me immediately so that I can correct and delete the
original
email. Thank you.

 


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

gcc version question

[Devananda]

Hi,
I'm trying to compile from the development source tree (bitkeeper) 
version 4.1.3-beta, on a Mandrake 9.2 box. It keeps failing to compile 
in ndb/src/kernel/ArrayPool.hpp. I am trying to compile it with cluster, 
since that is what I need to test. I've done all the autoconf/etc steps, 
and made sure to have the exact same versions of all those tools as is 
listed on dev.mysql.com. However, I'm running gcc 3.4.0 as opposed to 
2.95.4. I've tried to find 2.95.4, but can't seem to find anything 
besides 2.95.3 or 3.xxx.

Has anyone else had success with gcc 3.4.0 on Mandrake, or run into 
similar problems?

Thanks for you time,
Devananda
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: No suitable driver

[Schalk]

Try changing driver to Driver

 

Kind Regards

Schalk Neethling

Web Developer.Designer.Programmer.President

Volume4.Development.Multimedia.Branding

emotionalize.conceptualize.visualize.realize

Tel: +27125468436

Fax: +27125468436

email:[EMAIL PROTECTED]

web: www.volume4.co.za

 

This message contains information that is considered to be sensitive or
confidential and may not be forwarded or disclosed to any other party
without the permission of the sender. If you received this message in error,
please notify me immediately so that I can correct and delete the original
email. Thank you.

 

Re: MySQL and VBquestion - problem with query that returns 100000+ records

[Brent Baisley]

I have to ask, why would you even want to pull that many records at 
once? No user would want to sift through that many records. I always 
add a limit clause to all my queries. There is no harm in having a 
limit 500 when you are just receiving 10 records. But it's good to have 
the limit there in case your query has a bug.

It may be that things are hanging on the transfer of that much data 
from MySQL to ODBC to VB. If you add a LIMIT 25 at the end of the query 
statement, does it work then?

On Jun 30, 2004, at 11:52 AM, Greg Zimmermack wrote:
Victor
Thanks for your reply.
I checked the processlist and it continues to execute even after the 
VB application is hung. I know the application is hung by checking its 
status in the Windows Task manager.

The 'hostname.err' file did not show any errors. Is there another file 
i should be looking at?

The error handler in in VB could not handle the error.
Is there some ODBC setting I need to optimize?
Any help will be appreciated.
Thanks
Greg
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search  Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: authentication error

[Chip Bell]

Hi Victor,

I have stumbled on to something.  The server is bouncing back and forth
b/t fast and slow.  When I do show processlist and its above 100, it's
slow.  When it's below, it's fine.

What variable is wrong?  I have Max_connections = 250?

Thanks

Chip

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 3:12 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

You should just become familiar with your data and the queries that are
sent
to the database. You could turn on the slow query log and after a few
days
or hours or whatever see what queries are logged. 

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 1:03 PM
Subject: RE: authentication error

Is there anything I should set in my startup options to accommodate
this?

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:54 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

The values in the `State` and `Command` fields of the queries are what
you
should be looking at. For example if you have a select statement that is
running over an acceptable threshold you should look into that. If you
have
a query that is taking a `long` time to create a temp table , you should
look at that one. You just need to identify what is acceptable and
normal
behaviour and correct where possible.

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:43 PM
Subject: RE: authentication error

Ok, we might be on to something.  Right now, the server is running fine.
I did a show processlist and got back 138 rows.  There are times
ranging from 0-14556.  Granted, it's an email server so people are
staying logged in...

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:31 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

Do you have an `execessive` numer of processes running? Do you have any
processes that have been running for an `abnormal` length of time? 

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:12 PM
Subject: RE: authentication error

It's all on the same box.  I'm familiar with the show processlist but
I don't know what to look for.  See what I mean lol?  I know most of the
commands, but not quite what I'm looking for.



-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:11 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

What is the connection like between the two servers? Once you are logged
in
can you do a show processlist and see if anything is bottelnecking the
database?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:05 PM
Subject: RE: authentication error

I ran the FLUSH HOSTS and it said 0 rows affected  The authentication
goes against the mysql table, which is where I'm guessing the errors
would show.  When I try to log in during the slow down of the server,
it just hangs and hangs and finally will let me through.



-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:02 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

From the mysql monitor it is `FLUSH HOSTS;` . Does  the email server not
log
failed connection attempts?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 11:55 AM
Subject: RE: authentication error

Ok, I tried to run flush-hosts from both # and inside mysql..nada.
How can I do this exactly?  Sorry about my lack of knowledge, I'm still
green. I have my books though and am feverishly trying to find the
answer!

Thanks for your help!!

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 12:53 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

Can you view or log the errors that imap and the web authentication are
getting? Try issuing a  flush-hosts to see if the max_connect_errors was
reached. 

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 11:47 AM
Subject: RE: authentication error

Nothing actually.  I'm guessing I don't have enough logging turned on.
The only thing my servername.err files shows is

Cannot initialize InnoDB as 'innodb_data_file_path' is not set.  If you
do do not.etc



-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 12:04 PM
To: Chip Bell; '[EMAIL PROTECTED] '
Subject: RE: authentication error

What does the error log say? 

-Original Message-
From: Chip Bell
To: [EMAIL PROTECTED]

FULLTEXT

[Schalk]

I am using the following command on MySQL 4.0.18

 

ALTER TABLE tablename MODIFY columnname FULLTEXT;

 

I keep getting an error regarding FULLTEXT. Where am I going wrong?

 

Kind Regards

Schalk Neethling

Web Developer.Designer.Programmer.President

Volume4.Development.Multimedia.Branding

emotionalize.conceptualize.visualize.realize

Tel: +27125468436

Fax: +27125468436

email:[EMAIL PROTECTED]

web: www.volume4.co.za

 

This message contains information that is considered to be sensitive or
confidential and may not be forwarded or disclosed to any other party
without the permission of the sender. If you received this message in error,
please notify me immediately so that I can correct and delete the original
email. Thank you.

 

RE: FULLTEXT

[Victor Pendleton]

ALTER TABLE table_name ADD FULLTEXT INDEX index_name (col);

-Original Message-
From: Schalk
To: [EMAIL PROTECTED]
Sent: 6/30/04 3:28 PM
Subject: FULLTEXT

I am using the following command on MySQL 4.0.18

 

ALTER TABLE tablename MODIFY columnname FULLTEXT;

 

I keep getting an error regarding FULLTEXT. Where am I going wrong?

 

Kind Regards

Schalk Neethling

Web Developer.Designer.Programmer.President

Volume4.Development.Multimedia.Branding

emotionalize.conceptualize.visualize.realize

Tel: +27125468436

Fax: +27125468436

email:[EMAIL PROTECTED]

web: www.volume4.co.za

 

This message contains information that is considered to be sensitive or
confidential and may not be forwarded or disclosed to any other party
without the permission of the sender. If you received this message in
error,
please notify me immediately so that I can correct and delete the
original
email. Thank you.

 


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

RE: authentication error

[Victor Pendleton]

I would check to see if your server is swapping at this point.

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/30/04 3:27 PM
Subject: RE: authentication error

Hi Victor,

I have stumbled on to something.  The server is bouncing back and forth
b/t fast and slow.  When I do show processlist and its above 100, it's
slow.  When it's below, it's fine.

What variable is wrong?  I have Max_connections = 250?

Thanks

Chip

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 3:12 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

You should just become familiar with your data and the queries that are
sent
to the database. You could turn on the slow query log and after a few
days
or hours or whatever see what queries are logged. 

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 1:03 PM
Subject: RE: authentication error

Is there anything I should set in my startup options to accommodate
this?

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:54 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

The values in the `State` and `Command` fields of the queries are what
you
should be looking at. For example if you have a select statement that is
running over an acceptable threshold you should look into that. If you
have
a query that is taking a `long` time to create a temp table , you should
look at that one. You just need to identify what is acceptable and
normal
behaviour and correct where possible.

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:43 PM
Subject: RE: authentication error

Ok, we might be on to something.  Right now, the server is running fine.
I did a show processlist and got back 138 rows.  There are times
ranging from 0-14556.  Granted, it's an email server so people are
staying logged in...

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:31 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

Do you have an `execessive` numer of processes running? Do you have any
processes that have been running for an `abnormal` length of time? 

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:12 PM
Subject: RE: authentication error

It's all on the same box.  I'm familiar with the show processlist but
I don't know what to look for.  See what I mean lol?  I know most of the
commands, but not quite what I'm looking for.



-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:11 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

What is the connection like between the two servers? Once you are logged
in
can you do a show processlist and see if anything is bottelnecking the
database?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:05 PM
Subject: RE: authentication error

I ran the FLUSH HOSTS and it said 0 rows affected  The authentication
goes against the mysql table, which is where I'm guessing the errors
would show.  When I try to log in during the slow down of the server,
it just hangs and hangs and finally will let me through.



-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:02 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

From the mysql monitor it is `FLUSH HOSTS;` . Does  the email server not
log
failed connection attempts?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 11:55 AM
Subject: RE: authentication error

Ok, I tried to run flush-hosts from both # and inside mysql..nada.
How can I do this exactly?  Sorry about my lack of knowledge, I'm still
green. I have my books though and am feverishly trying to find the
answer!

Thanks for your help!!

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 12:53 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

Can you view or log the errors that imap and the web authentication are
getting? Try issuing a  flush-hosts to see if the max_connect_errors was
reached. 

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 11:47 AM
Subject: RE: authentication error

Nothing actually.  I'm guessing I don't have enough logging turned on.
The only thing my servername.err files shows is

Cannot initialize InnoDB as 'innodb_data_file_path' is not set.  If you
do do not.etc



-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 

RE: authentication error

[Victor Pendleton]

Do you have a high number of temp tables being created or high i/o?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/30/04 3:44 PM
Subject: RE: authentication error

No sir...

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 30, 2004 4:43 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

I would check to see if your server is swapping at this point.

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/30/04 3:27 PM
Subject: RE: authentication error

Hi Victor,

I have stumbled on to something.  The server is bouncing back and forth
b/t fast and slow.  When I do show processlist and its above 100, it's
slow.  When it's below, it's fine.

What variable is wrong?  I have Max_connections = 250?

Thanks

Chip

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 3:12 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

You should just become familiar with your data and the queries that are
sent
to the database. You could turn on the slow query log and after a few
days
or hours or whatever see what queries are logged. 

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 1:03 PM
Subject: RE: authentication error

Is there anything I should set in my startup options to accommodate
this?

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:54 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

The values in the `State` and `Command` fields of the queries are what
you
should be looking at. For example if you have a select statement that is
running over an acceptable threshold you should look into that. If you
have
a query that is taking a `long` time to create a temp table , you should
look at that one. You just need to identify what is acceptable and
normal
behaviour and correct where possible.

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:43 PM
Subject: RE: authentication error

Ok, we might be on to something.  Right now, the server is running fine.
I did a show processlist and got back 138 rows.  There are times
ranging from 0-14556.  Granted, it's an email server so people are
staying logged in...

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:31 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

Do you have an `execessive` numer of processes running? Do you have any
processes that have been running for an `abnormal` length of time? 

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:12 PM
Subject: RE: authentication error

It's all on the same box.  I'm familiar with the show processlist but
I don't know what to look for.  See what I mean lol?  I know most of the
commands, but not quite what I'm looking for.



-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:11 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

What is the connection like between the two servers? Once you are logged
in
can you do a show processlist and see if anything is bottelnecking the
database?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:05 PM
Subject: RE: authentication error

I ran the FLUSH HOSTS and it said 0 rows affected  The authentication
goes against the mysql table, which is where I'm guessing the errors
would show.  When I try to log in during the slow down of the server,
it just hangs and hangs and finally will let me through.



-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:02 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

From the mysql monitor it is `FLUSH HOSTS;` . Does  the email server not
log
failed connection attempts?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 11:55 AM
Subject: RE: authentication error

Ok, I tried to run flush-hosts from both # and inside mysql..nada.
How can I do this exactly?  Sorry about my lack of knowledge, I'm still
green. I have my books though and am feverishly trying to find the
answer!

Thanks for your help!!

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 12:53 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

Can you view or log the errors that imap and the web authentication are
getting? Try issuing a  flush-hosts to see if the max_connect_errors was
reached. 

Re: FULLTEXT

[Eric Bergen]

alter table t add fulltext (col) 
-Eric

On Wed, 30 Jun 2004 22:28:03 +0200, Schalk [EMAIL PROTECTED] wrote:
 
 I am using the following command on MySQL 4.0.18
 
 ALTER TABLE tablename MODIFY columnname FULLTEXT;
 
 I keep getting an error regarding FULLTEXT. Where am I going wrong?
 
 Kind Regards
 
 Schalk Neethling
 
 Web Developer.Designer.Programmer.President
 
 Volume4.Development.Multimedia.Branding
 
 emotionalize.conceptualize.visualize.realize
 
 Tel: +27125468436
 
 Fax: +27125468436
 
 email:[EMAIL PROTECTED]
 
 web: www.volume4.co.za
 
 This message contains information that is considered to be sensitive or
 confidential and may not be forwarded or disclosed to any other party
 without the permission of the sender. If you received this message in error,
 please notify me immediately so that I can correct and delete the original
 email. Thank you.
 


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

RE: authentication error

[Chip Bell]

No sir...

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 30, 2004 4:43 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

I would check to see if your server is swapping at this point.

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/30/04 3:27 PM
Subject: RE: authentication error

Hi Victor,

I have stumbled on to something.  The server is bouncing back and forth
b/t fast and slow.  When I do show processlist and its above 100, it's
slow.  When it's below, it's fine.

What variable is wrong?  I have Max_connections = 250?

Thanks

Chip

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 3:12 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

You should just become familiar with your data and the queries that are
sent
to the database. You could turn on the slow query log and after a few
days
or hours or whatever see what queries are logged. 

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 1:03 PM
Subject: RE: authentication error

Is there anything I should set in my startup options to accommodate
this?

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:54 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

The values in the `State` and `Command` fields of the queries are what
you
should be looking at. For example if you have a select statement that is
running over an acceptable threshold you should look into that. If you
have
a query that is taking a `long` time to create a temp table , you should
look at that one. You just need to identify what is acceptable and
normal
behaviour and correct where possible.

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:43 PM
Subject: RE: authentication error

Ok, we might be on to something.  Right now, the server is running fine.
I did a show processlist and got back 138 rows.  There are times
ranging from 0-14556.  Granted, it's an email server so people are
staying logged in...

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:31 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

Do you have an `execessive` numer of processes running? Do you have any
processes that have been running for an `abnormal` length of time? 

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:12 PM
Subject: RE: authentication error

It's all on the same box.  I'm familiar with the show processlist but
I don't know what to look for.  See what I mean lol?  I know most of the
commands, but not quite what I'm looking for.



-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:11 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

What is the connection like between the two servers? Once you are logged
in
can you do a show processlist and see if anything is bottelnecking the
database?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:05 PM
Subject: RE: authentication error

I ran the FLUSH HOSTS and it said 0 rows affected  The authentication
goes against the mysql table, which is where I'm guessing the errors
would show.  When I try to log in during the slow down of the server,
it just hangs and hangs and finally will let me through.



-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:02 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

From the mysql monitor it is `FLUSH HOSTS;` . Does  the email server not
log
failed connection attempts?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 11:55 AM
Subject: RE: authentication error

Ok, I tried to run flush-hosts from both # and inside mysql..nada.
How can I do this exactly?  Sorry about my lack of knowledge, I'm still
green. I have my books though and am feverishly trying to find the
answer!

Thanks for your help!!

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 12:53 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

Can you view or log the errors that imap and the web authentication are
getting? Try issuing a  flush-hosts to see if the max_connect_errors was
reached. 

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 11:47 AM
Subject: RE: authentication error

Nothing actually.  I'm guessing I don't have enough logging turned on.
The 

RE: authentication error

[Chip Bell]

After looking through, show variables;  the only one I found with a
value of 100 is the delayed_insert_limit

Could this be the issue?

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 30, 2004 4:45 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

Do you have a high number of temp tables being created or high i/o?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/30/04 3:44 PM
Subject: RE: authentication error

No sir...

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 30, 2004 4:43 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

I would check to see if your server is swapping at this point.

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/30/04 3:27 PM
Subject: RE: authentication error

Hi Victor,

I have stumbled on to something.  The server is bouncing back and forth
b/t fast and slow.  When I do show processlist and its above 100, it's
slow.  When it's below, it's fine.

What variable is wrong?  I have Max_connections = 250?

Thanks

Chip

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 3:12 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

You should just become familiar with your data and the queries that are
sent
to the database. You could turn on the slow query log and after a few
days
or hours or whatever see what queries are logged. 

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 1:03 PM
Subject: RE: authentication error

Is there anything I should set in my startup options to accommodate
this?

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:54 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

The values in the `State` and `Command` fields of the queries are what
you
should be looking at. For example if you have a select statement that is
running over an acceptable threshold you should look into that. If you
have
a query that is taking a `long` time to create a temp table , you should
look at that one. You just need to identify what is acceptable and
normal
behaviour and correct where possible.

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:43 PM
Subject: RE: authentication error

Ok, we might be on to something.  Right now, the server is running fine.
I did a show processlist and got back 138 rows.  There are times
ranging from 0-14556.  Granted, it's an email server so people are
staying logged in...

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:31 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

Do you have an `execessive` numer of processes running? Do you have any
processes that have been running for an `abnormal` length of time? 

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:12 PM
Subject: RE: authentication error

It's all on the same box.  I'm familiar with the show processlist but
I don't know what to look for.  See what I mean lol?  I know most of the
commands, but not quite what I'm looking for.



-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:11 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

What is the connection like between the two servers? Once you are logged
in
can you do a show processlist and see if anything is bottelnecking the
database?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:05 PM
Subject: RE: authentication error

I ran the FLUSH HOSTS and it said 0 rows affected  The authentication
goes against the mysql table, which is where I'm guessing the errors
would show.  When I try to log in during the slow down of the server,
it just hangs and hangs and finally will let me through.



-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:02 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

From the mysql monitor it is `FLUSH HOSTS;` . Does  the email server not
log
failed connection attempts?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 11:55 AM
Subject: RE: authentication error

Ok, I tried to run flush-hosts from both # and inside mysql..nada.
How can I do this exactly?  Sorry about my lack of knowledge, I'm still
green. I have my books though and am feverishly trying to find the
answer!

Thanks for your help!!

-Original Message-

RE: Production release of MySql 4.1

[Jon Frisby]

As I understand it, the particular cycle a release is in depends on how long
it's been since a major bug was reported.  So an alpha becomes a beta if
nobody reports a major bug after N days, and a beta becomes a production
release if goes N days without a major bug report.  Thus, even if 4.1.3 is
released as alpha, it could retroactively be declared beta, and then even
release -- although that's pretty unlikely.  The long and short of it
though, is that nobody can tell you how long until 4.1 will go beta.

-JF 

 -Original Message-
 From: Jonathan Soong [mailto:[EMAIL PROTECTED] 
 Sent: Tuesday, June 29, 2004 7:20 PM
 To: Jocelyn Fournier
 Cc: John Murphy; Emmanuel van der Meulen; [EMAIL PROTECTED]
 Subject: Re: Production release of MySql 4.1
 
 Jocelyn Fournier wrote:
  Hi,
  
  AFAIK 4.1.3 should be beta.
  
 
 It is a little frustrating,
 
 at Linux Conf Adelaide 2004 (January), the Mysql guy there 
 said that 4.1 
 would be in beta, in the next few weeks ...
 
 Its now July and its still in Alpha.
 
 It says on the webpage MySQL 4.1 -- Alpha release (use this for new 
 development) - and it has said that for 6months+
 
 So we did our development on 4.1, and were expecting it to be beta by 
 February 2004.
 
 We're ready to roll it out as soon as it hits beta, i told my boss it 
 would be in beta by March 2004 at the latest. We now have hardware 
 sitting for around with 4.1 alpha on it that cannot be deployed.
 
 Does anyone actually have a concrete date when 4.1 will go into beta?
 
 Cheers
 
 Jon
 
 
 
 -- 
 Jonathan Soong
 Information Services
 Institute of Medical and Veterinary Science (IMVS)
 Email:   [EMAIL PROTECTED]
 Web  :   http://www.imvs.sa.gov.au
 Tel  :   +61 8 82223095
 Fax  :   +61 8 82223147   
 
 
 -- 
 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: authentication error

[Victor Pendleton]

Are you using delayed insert statements?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/30/04 3:50 PM
Subject: RE: authentication error

After looking through, show variables;  the only one I found with a
value of 100 is the delayed_insert_limit

Could this be the issue?

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 30, 2004 4:45 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

Do you have a high number of temp tables being created or high i/o?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/30/04 3:44 PM
Subject: RE: authentication error

No sir...

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 30, 2004 4:43 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

I would check to see if your server is swapping at this point.

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/30/04 3:27 PM
Subject: RE: authentication error

Hi Victor,

I have stumbled on to something.  The server is bouncing back and forth
b/t fast and slow.  When I do show processlist and its above 100, it's
slow.  When it's below, it's fine.

What variable is wrong?  I have Max_connections = 250?

Thanks

Chip

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 3:12 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

You should just become familiar with your data and the queries that are
sent
to the database. You could turn on the slow query log and after a few
days
or hours or whatever see what queries are logged. 

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 1:03 PM
Subject: RE: authentication error

Is there anything I should set in my startup options to accommodate
this?

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:54 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

The values in the `State` and `Command` fields of the queries are what
you
should be looking at. For example if you have a select statement that is
running over an acceptable threshold you should look into that. If you
have
a query that is taking a `long` time to create a temp table , you should
look at that one. You just need to identify what is acceptable and
normal
behaviour and correct where possible.

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:43 PM
Subject: RE: authentication error

Ok, we might be on to something.  Right now, the server is running fine.
I did a show processlist and got back 138 rows.  There are times
ranging from 0-14556.  Granted, it's an email server so people are
staying logged in...

-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:31 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

Do you have an `execessive` numer of processes running? Do you have any
processes that have been running for an `abnormal` length of time? 

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:12 PM
Subject: RE: authentication error

It's all on the same box.  I'm familiar with the show processlist but
I don't know what to look for.  See what I mean lol?  I know most of the
commands, but not quite what I'm looking for.



-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:11 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

What is the connection like between the two servers? Once you are logged
in
can you do a show processlist and see if anything is bottelnecking the
database?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 12:05 PM
Subject: RE: authentication error

I ran the FLUSH HOSTS and it said 0 rows affected  The authentication
goes against the mysql table, which is where I'm guessing the errors
would show.  When I try to log in during the slow down of the server,
it just hangs and hangs and finally will let me through.



-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 29, 2004 1:02 PM
To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] '
Subject: RE: authentication error

From the mysql monitor it is `FLUSH HOSTS;` . Does  the email server not
log
failed connection attempts?

-Original Message-
From: Chip Bell
To: Victor Pendleton; [EMAIL PROTECTED]
Sent: 6/29/04 11:55 AM
Subject: RE: authentication error

Ok, I tried to run flush-hosts from both # and inside mysql..nada.
How can I do this 

RE: Best table structure

[Jon Frisby]

The current approach is better.  Having one row with 81 columns will be
harder to deal with in terms of writing code to display it.  The size of the
table will be roughly the same either way -- either you have a few very big
rows, or many small rows, but that shouldn't be a huge issue if you index
things properly.

You may want to introduce some way to let you cull old data automatically.
For instance, perhaps add a TIMESTAMP column and delete anything over 30
days old.  Or possibly an AUTO_INCREMENT column, and delete anything below
MAX(id) - (N * 20) to ensure only N top-20 lists are in the table.

-JF 

 -Original Message-
 From: Tom Chubb [mailto:[EMAIL PROTECTED] 
 Sent: Wednesday, June 30, 2004 5:27 AM
 To: [EMAIL PROTECTED]
 Subject: Best table structure
 
 Please can someone let me know their opinion on the following.
 I am new to MySQL and can't seem to find the right info anywhere.
 
 I have written some code for submitting a top 20 music chart online.
 I use the following to insert into mysql:
 
 INSERT INTO chart (name, chartpos, artist, title, label) VALUES
 
 ('$name', '1', '$artist', '$title', '$label'),
 ('$name', '2', '$artist2', '$title2', '$label2'),
 ('$name', '3', '$artist3', '$title3', '$label3'),
 
  -- repeat til -
 
 ('$name', '20', '$artist20', '$title20', '$label20'),
 
 
 Another page queries the table and sorts by name (multiple 
 people submit
 charts) and latest date.
 
 My question is this:
 Would I be better keeping this format and inserting multiple 
 rows on each
 submit, or to have one row for all 81 variables ($name, 20 x 
 Position, 20 x
 Artists, 20 x Titles, 20 x Labels.)
 I know that the latter will be easier to query.
 
 Also, without maintenance, the size of the table for the 
 current method will
 get extremely large. Will that affect server performance?
 
 I am still a newbie, so plain explanations would be most appreciated.
 
 Thanks very much in advance.
 
 
 Tom
 
 --
 PHP General Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php
 
 
 
 
 -- 
 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 growing pains, 4 days to create index on one table!

[Lopez David E-r9374c]

matt

1) inserts using this format is much faster:
 INSERT INTO table (col1, col2) VALUES (val1,val2), (val3,val4)
is much faster then single row insert. My experience is
2.5 hrs vs.. 36 hrs.

2) The PACK_KEYS=1 may be hurting you. I've never used it.

3) There may be a cache somewhere that's to small. You'll
   have to do some digging in this area.

4) dup key ignore - what does that mean exactly?

5) what is your OS  rev, mysql rev.

Please post any suggestions that you find valuable so we 
can all learn..

david
  

 -Original Message-
 From: matt ryan [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, June 30, 2004 11:51 AM
 To: [EMAIL PROTECTED]
 Subject: Mysql growing pains, 4 days to create index on one table!
 
 
 Rebuilding index takes 3 1/2 days!!!  Growing pains with mysql..
 
 I've got 2 dedicated servers, each with a slave, all run 32gig 15k rpm
 raid 5 on u320 perc raid cards, dell 2600/4600's with single channel
 backplanes (new ones will have dual channel)
 
 All have 2 gig of ram, but I've never seen mysql use more than 600mb
 of ram.
 
 The servers handle huge loads, each day there are 30 1-2 gig files
 loaded into large tables, total mysql data size is 96 gig, the large
 tables are 2-6 gig.
 
 Inserts are done on dupe key ignore, this takes hours on the large
 files, it barely keeps up with input files.
 
 At the bottom of this post I've got the mysql.ini config lines, any
 suggestions are welcome, I'm already beyond the mysql huge sample
 they used to include in the program.
 
 Sample table that I load is as follows.
 
 each day I get 40 % new records on the text file, the input file is
 normally 20mb, once a week I get one that's 1-2 gig, these take all
 day to load.
 
 I need more multiple column indexes, as some querys return millions of
 rows that must be scanned, but the index size already exceeds the
 table size, and the combinations I need would result in an myi that's
 5x larger than the data itself.
 
 Here's an example of the speed problem, the index was corrupt so I
 dropped all and recreated, rather than a myisam repair. I think 3 days
 is a little excessive for a table that's only 3.428 gig, index is
 2.729 gig.  I cant remove the primary key, as it keeps duplicates out
 of the system, the input files are from old database's, we use mysql
 to store the data for the web frontend, mostly done in ASP, most
 queries take less than a second, unforuntatly we have big queries that
 take way more than the IIS timeout setting all the time, but no way
 around it, I cant add more indexes without making it even slower :(
 
 
 
 I cant tell if it's mysql that's the problem, or the 
 hardware, Here's a 
 screenshot of the disk IO, if I copy a file while mysql is doing the 
 build index, the io shoots way up, which tells me, mysql is 
 NOT maxing 
 out the drives, and it's also not maxing out the memory.
 
 Unless it's doing lots and lots of seeks on the drive, which 
 is harder 
 to test using perfmon, are there any mysql test setups that 
 would help 
 identify where the bottleneck is?
 
 
 screenshot of disk io usage
 
 http://www.geekopolis.com/pics/diskio.jpg
 
 I'm all out of ideas, other than switching to another db, and 
 the table 
  indexes split across drives, maybe a 2 channel setup, 4 drives per 
 channel, each 4 is a separate raid 5 setup, one holds data one holds 
 indexes, cant do this with mysql though
 
 mysql alter table hood_stat add primary key
 (dic,niin,fr_ric,don,suf,dte_txn,sta) , add index `don` (`don`), add
 index `niin` (`niin`), add index `stor` (`stor`), add index `dic`
 (`dic`), add index `ctasc` (`ctasc`);
 
 Query OK, 45449534 rows affected (3 days 19 hours 6 min 34.94 seconds
 Records: 45449534 Duplicates: 0 Warnings: 0
 
 
 CREATE TABLE `hood_stat` (
 `dic` char(3) NOT NULL default '',
 `fr_ric` char(3) NOT NULL default '',
 `niin` char(11) NOT NULL default '',
 `ui` char(2) NOT NULL default '',
 `qty` char(5) NOT NULL default '',
 `don` char(14) NOT NULL default '',
 `suf` char(1) NOT NULL default '',
 `dte_txn` char(5) NOT NULL default '',
 `ship_to` char(3) NOT NULL default '',
 `sta` char(2) NOT NULL default '',
 `lst_sos` char(3) NOT NULL default '',
 `esd` char(4) NOT NULL default '',
 `stor` char(3) NOT NULL default '',
 `d_t` char(4) NOT NULL default '',
 `ctasc` char(10) NOT NULL default '',
 PRIMARY KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta` ),
 KEY `don` (`don`),
 KEY `niin` (`niin`),
 KEY `stor` (`stor`),
 KEY `dic` (`dic`),
 KEY `ctasc` (`ctasc`)
 ) TYPE=MyISAM MAX_ROWS=10 PACK_KEYS=1
 
 skip-locking
 set-variable=delay_key_write=ALL
 set-variable= key_buffer_size=1500M
 set-variable=join_buffer=512M
 set-variable= max_allowed_packet=256M
 set-variable= table_cache=512
 set-variable= sort_buffer=256M
 set-variable=tmp_table_size=400M
 set-variable= record_buffer=512M
 set-variable= thread_cache=8
 set-variable=myisam_sort_buffer_size=256M
 myisam-recover=BACKUP,FORCE
 set-variable=read_buffer_size=512M
 

mysqld_safe in 4.1.2

[Bill MacAllister]

Hello,
I am a bit confused by processing of user= specifications in the 
mysqld_safe script.  Here is what I see:

* If user= is specificed in the [mysqld] or [server] sections then it is
 ignored and a flag is set that prevents the specification of a user
 in any subsequent options parsing.
* Otherwise the user will be extracted and used from either the 
[mysqld_safe],
 [safe_mysqld], or command line arguements.

This is counter intuitive if nothing else, and I really don't see why user 
should be parsed any differently than any other options.  Am I missing 
something or is this a bug?

Bill
+
| Bill MacAllister, Senior Programmer
| PRIDE Industries
| 10030 Foothills Blvd, Dept 1150
| Roseville, CA  95747
| 916-788-2402
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

return substrings out of a string

[L a n a]

Hello,
I’m working on a search function using PHP4 and MYSQL4.
I’m looking for a function that allow to take a string value (consisted of 
several substrings separated by coma) out of  db field and return each 
substring one by one.

Could you please tell me if there is a function that can do something 
similar to

SELECT function_name (field_name, “,”)
(return)- substr1, substr2, substr3…
Thank you for your help,
Lana
_
Add photos to your messages with MSN Premium. Get 2 months FREE*  
http://join.msn.com/?pgmarket=en-capage=byoa/premxAPID=1994DI=1034SU=http://hotmail.com/encaHL=Market_MSNIS_Taglines

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

Re: Production release of MySql 4.1

[Andrew Pattison]

I saw reference somewhere (I believe it was either an Apache or PHP
discussion) to 4.1.3 being beta but I'm not sure if this was just wishful
thinking on the part of those particular developers. If this is the case
then going by the dates of previous releases in the 4.1 branch (not always a
good guide) then 4.1 will go beta somewhere around the period November 2004
to February 2005.

How long was 4.0 is alpha? I seem to recall it was more than 6 months. Are
you able to run 4.1 in some sort of test environment to see how it performs
for you? You may find it works well enough to deploy right now, you may not.
A major bug for one guy may not affect you at all - it could be platform
specific or affect a feature you don't use.

Cheers

Andrew.

- Original Message - 
From: Jon Frisby [EMAIL PROTECTED]
To: 'Jonathan Soong' [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, June 30, 2004 10:09 PM
Subject: RE: Production release of MySql 4.1


 As I understand it, the particular cycle a release is in depends on how
long
 it's been since a major bug was reported.  So an alpha becomes a beta if
 nobody reports a major bug after N days, and a beta becomes a production
 release if goes N days without a major bug report.  Thus, even if 4.1.3 is
 released as alpha, it could retroactively be declared beta, and then even
 release -- although that's pretty unlikely.  The long and short of it
 though, is that nobody can tell you how long until 4.1 will go beta.

 -JF

  -Original Message-
  From: Jonathan Soong [mailto:[EMAIL PROTECTED]
  Sent: Tuesday, June 29, 2004 7:20 PM
  To: Jocelyn Fournier
  Cc: John Murphy; Emmanuel van der Meulen; [EMAIL PROTECTED]
  Subject: Re: Production release of MySql 4.1
 
  Jocelyn Fournier wrote:
   Hi,
  
   AFAIK 4.1.3 should be beta.
  
 
  It is a little frustrating,
 
  at Linux Conf Adelaide 2004 (January), the Mysql guy there
  said that 4.1
  would be in beta, in the next few weeks ...
 
  Its now July and its still in Alpha.
 
  It says on the webpage MySQL 4.1 -- Alpha release (use this for new
  development) - and it has said that for 6months+
 
  So we did our development on 4.1, and were expecting it to be beta by
  February 2004.
 
  We're ready to roll it out as soon as it hits beta, i told my boss it
  would be in beta by March 2004 at the latest. We now have hardware
  sitting for around with 4.1 alpha on it that cannot be deployed.
 
  Does anyone actually have a concrete date when 4.1 will go into beta?
 
  Cheers
 
  Jon
 
 
 
  -- 
  Jonathan Soong
  Information Services
  Institute of Medical and Veterinary Science (IMVS)
  Email:   [EMAIL PROTECTED]
  Web  :   http://www.imvs.sa.gov.au
  Tel  :   +61 8 82223095
  Fax  :   +61 8 82223147
 
 
  -- 
  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: Re: Production release of MySql 4.1

[Jocelyn Fournier]

Hi,

4.1.3 is labeled beta in the bktree.
So I assume 4.1.3 will be beta when it will be released ;)

Regards,
  Jocelyn


- Original Message - 
From: Andrew Pattison [EMAIL PROTECTED]
To: Jon Frisby [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, June 30, 2004 11:45 PM
Subject: [SPAM] Re: Production release of MySql 4.1


 I saw reference somewhere (I believe it was either an Apache or PHP
 discussion) to 4.1.3 being beta but I'm not sure if this was just wishful
 thinking on the part of those particular developers. If this is the case
 then going by the dates of previous releases in the 4.1 branch (not always
a
 good guide) then 4.1 will go beta somewhere around the period November
2004
 to February 2005.

 How long was 4.0 is alpha? I seem to recall it was more than 6 months. Are
 you able to run 4.1 in some sort of test environment to see how it
performs
 for you? You may find it works well enough to deploy right now, you may
not.
 A major bug for one guy may not affect you at all - it could be platform
 specific or affect a feature you don't use.

 Cheers

 Andrew.

 - Original Message - 
 From: Jon Frisby [EMAIL PROTECTED]
 To: 'Jonathan Soong' [EMAIL PROTECTED]
 Cc: [EMAIL PROTECTED]
 Sent: Wednesday, June 30, 2004 10:09 PM
 Subject: RE: Production release of MySql 4.1


  As I understand it, the particular cycle a release is in depends on how
 long
  it's been since a major bug was reported.  So an alpha becomes a beta if
  nobody reports a major bug after N days, and a beta becomes a production
  release if goes N days without a major bug report.  Thus, even if 4.1.3
is
  released as alpha, it could retroactively be declared beta, and then
even
  release -- although that's pretty unlikely.  The long and short of it
  though, is that nobody can tell you how long until 4.1 will go beta.
 
  -JF
 
   -Original Message-
   From: Jonathan Soong [mailto:[EMAIL PROTECTED]
   Sent: Tuesday, June 29, 2004 7:20 PM
   To: Jocelyn Fournier
   Cc: John Murphy; Emmanuel van der Meulen; [EMAIL PROTECTED]
   Subject: Re: Production release of MySql 4.1
  
   Jocelyn Fournier wrote:
Hi,
   
AFAIK 4.1.3 should be beta.
   
  
   It is a little frustrating,
  
   at Linux Conf Adelaide 2004 (January), the Mysql guy there
   said that 4.1
   would be in beta, in the next few weeks ...
  
   Its now July and its still in Alpha.
  
   It says on the webpage MySQL 4.1 -- Alpha release (use this for new
   development) - and it has said that for 6months+
  
   So we did our development on 4.1, and were expecting it to be beta by
   February 2004.
  
   We're ready to roll it out as soon as it hits beta, i told my boss it
   would be in beta by March 2004 at the latest. We now have hardware
   sitting for around with 4.1 alpha on it that cannot be deployed.
  
   Does anyone actually have a concrete date when 4.1 will go into beta?
  
   Cheers
  
   Jon
  
  
  
   -- 
   Jonathan Soong
   Information Services
   Institute of Medical and Veterinary Science (IMVS)
   Email:   [EMAIL PROTECTED]
   Web  :   http://www.imvs.sa.gov.au
   Tel  :   +61 8 82223095
   Fax  :   +61 8 82223147
  
  
   -- 
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:
   http://lists.mysql.com/[EMAIL PROTECTED]
  
  
 
 
  -- 
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 
 



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




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

Re: Re: Production release of MySql 4.1

[Jeremy Zawodny]

On Wed, Jun 30, 2004 at 11:52:32PM +0200, Jocelyn Fournier wrote:
 Hi,
 
 4.1.3 is labeled beta in the bktree.
 So I assume 4.1.3 will be beta when it will be released ;)

And that looks to be soon, based on the commits I've seen.

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

[book] High Performance MySQL -- http://highperformancemysql.com/

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

Re: Re: Production release of MySql 4.1

[Jocelyn Fournier]

Yes indeed, Lenz is preparing the build and has updated the news section.
(let's hope no critical bugs will be discovered which could slip the release
:))

  Jocelyn

- Original Message - 
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: Andrew Pattison [EMAIL PROTECTED]; Jon Frisby
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, July 01, 2004 12:02 AM
Subject: Re: Re: Production release of MySql 4.1


 On Wed, Jun 30, 2004 at 11:52:32PM +0200, Jocelyn Fournier wrote:
  Hi,
 
  4.1.3 is labeled beta in the bktree.
  So I assume 4.1.3 will be beta when it will be released ;)

 And that looks to be soon, based on the commits I've seen.

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

 [book] High Performance MySQL -- http://highperformancemysql.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 growing pains, 4 days to create index on one table!

[Udikarni]

You may want more indexes but you might be getting killed because you already have too 
many.

To test - try loading into a table without indexes and see if it makes a difference.

At the very least - check to see if the primary index which starts with 'dic' can make 
your special 'dic' index superfluous.

If write speed is a bottleneck you might consider Raid-1 instead of Raid-5.

Reading lots of rows via index is a killer. Depending on your hardware it may be 
cheaper to table scan 50 rows than to read 1 via index. However, this requires 
partitioning of the data based on some column which appears in every query and acts as 
an initial filter. If you are lucky enough to be in that situation - consider a MERGE 
table.

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

Re: mysqld_safe in 4.1.2

[Paul DuBois]

At 13:58 -0700 6/30/04, Bill MacAllister wrote:
Hello,
I am a bit confused by processing of user= specifications in the 
mysqld_safe script.  Here is what I see:

* If user= is specificed in the [mysqld] or [server] sections then it is
 ignored and a flag is set that prevents the specification of a user
 in any subsequent options parsing.
* Otherwise the user will be extracted and used from either the [mysqld_safe],
 [safe_mysqld], or command line arguements.
Not quite.  For security reasons, only the *first* encountered instance
of the user option is used, and any others are ignored.
http://dev.mysql.com/doc/mysql/en/Server_options.html
See the descripion of --user in the option list.
This is counter intuitive if nothing else, and I really don't see 
why user should be parsed any differently than any other options. 
Am I missing something or is this a bug?
It's by intent.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: threading problems in linux C client

[Eric Bergen]

If you have multiple threads accessing the same connection remember to
make sure that only one thread enters mysql_query() mysql_connect() or
mysql_store_result() after you store the result another thread will be
able to use that connection without problems.

-Eric

On Wed, 30 Jun 2004 11:01:35 +, darren [EMAIL PROTECTED] wrote:
 
 Hi all,
 
 I am writing a multi-threaded client that requires shared access to a single MySQL 
 connection. The environment is Redhat 7.3 with the patched 
 mysql-3.23.58-2.71.i386.rpm installed and compiled with -l libmysqlclient_r.
 
 The connection is made from the main thread and a MYSQL_RES* myres declared from the 
 child thread is used to fetch results back to the thread. I get a Segmentation fault 
 the first instance my child thread (only one) accesses MYSQL_RES through 
 mysql_num_rows(myres). (gdb informed me that this is due to  mysql_num_rows from 
 libmysqlclient_r.so.10)
 
 I have read a few docs about writing MySQL multi-threaded clients and have called 
 my_init() in my main prog and my_thread_init() at the first line of my thread 
 handler.
 
 Any ideas?
 
 
 --
 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: return substrings out of a string

[Wesley Furgiuele]

Lana:
The easiest thing would be if your field always contained the same  
number of comma-separated strings. Then you could just do something  
like a SUBSTRING_INDEX() function to break out the field into separate  
values. But, assuming your field does not always contain the same  
number of strings...

I'm imagining the contents of field1 look like abc,def,ghi. You  
want to do:
SELECT FUNCTION_NAME( field1 )
and get back
Row1: abc
Row2: def
Row3: ghi

If that's right, off the top of my head you have two options. The first  
option I can think of would be to break out these values into another  
table. Instead of a comma-separated list inside a field, move that  
field to another table and break each value into it's own record,  
linked back to the parent table by ID. A quick search of the list  
archives will bring up better descriptions on how to normalize than I  
could give.

The other, possibly simpler, option is to handle it in PHP. You could  
just grab the field, containing a list of strings delimited by commas,  
and use the explode() function to get your results. The PHP site has  
plenty of examples on the usage of explode.
http://us2.php.net/explode

Wes
On Jun 30, 2004, at 5:35 PM, L a n a wrote:
Hello,
I’m working on a search function using PHP4 and MYSQL4.
I’m looking for a function that allow to take a string value  
(consisted of several substrings separated by coma) out of  db field  
and return each substring one by one.

Could you please tell me if there is a function that can do something  
similar to

SELECT function_name (field_name, “,”)
(return)- substr1, substr2, substr3
Thank you for your help,
Lana
_
Add photos to your messages with MSN Premium. Get 2 months FREE*   
http://join.msn.com/?pgmarket=en-capage=byoa/ 
premxAPID=1994DI=1034SU=http://hotmail.com/ 
encaHL=Market_MSNIS_Taglines



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

Auto Date selection and format

[Mike Koponick]

Hello all,

I would like to be able to select the certain dates within my script. 

select created_date, status, user, comment1,  comment7, comment8, action
from users WHERE customerid = 'Customer' AND created_date BETWEEN
'2004-05-31' AND '2004-07-01' ORDER BY created_date, status into outfile
'test5.txt' FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\' LINES
TERMINATED BY '\n';

I would like the first date to be the last day of the previous month and
the second date to be the first day of the current month. What is the
most effecient way to do this in my script rather than hard coding?

Also, I looked for a way to format the output date to MM/DD/YY rather
than -MM-DD. Any suggestions?

Thanks in advance.

Mike

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

Re: Auto Date selection and format

[Wesley Furgiuele]

I can help easily enough on formatting the dates...
The DATE_FORMAT( date, format ) function is what you want:
DATE_FORMAT( date, %m/%d/%Y )
Wes
On Jun 30, 2004, at 9:40 PM, Mike Koponick wrote:
Hello all,
I would like to be able to select the certain dates within my script.
select created_date, status, user, comment1,  comment7, comment8, 
action
from users WHERE customerid = 'Customer' AND created_date BETWEEN
'2004-05-31' AND '2004-07-01' ORDER BY created_date, status into 
outfile
'test5.txt' FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\' LINES
TERMINATED BY '\n';

I would like the first date to be the last day of the previous month 
and
the second date to be the first day of the current month. What is the
most effecient way to do this in my script rather than hard coding?

Also, I looked for a way to format the output date to MM/DD/YY rather
than -MM-DD. Any suggestions?
Thanks 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: Auto Date selection and format

[Michael Stassen]

Mike Koponick wrote:
Hello all,
I would like to be able to select the certain dates within my script. 

select created_date, status, user, comment1,  comment7, comment8, action
from users WHERE customerid = 'Customer' AND created_date BETWEEN
'2004-05-31' AND '2004-07-01' ORDER BY created_date, status into outfile
'test5.txt' FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\' LINES
TERMINATED BY '\n';
I believe your INTO OUTFILE clause is mispalced. 
http://dev.mysql.com/doc/mysql/en/SELECT.html

I would like the first date to be the last day of the previous month and
the second date to be the first day of the current month. What is the
most effecient way to do this in my script rather than hard coding?
Your description doesn't quite match your example.  I'll assume the example 
is right.  I don't know about most efficient, but you can do it with a 
combination of date functions. 
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

Also, I looked for a way to format the output date to MM/DD/YY rather
than -MM-DD. Any suggestions?
DATE_FORMAT()
Thanks in advance.
Mike
I'm doing this in 2 queries with user variables to try to cut down on 
ugliness.  You could do it in one query by replacing the variables with 
their definitions in the WHERE clause.

  SELECT @day:= DAYOFMONTH(CURDATE()) day,
 @start:= CURDATE() - INTERVAL 1 MONTH - INTERVAL @day DAY start,
 @end:= CURDATE() - INTERVAL (@day-1) DAY end;
  +--+++
  | day  | start  | end|
  +--+++
  |1 | 2004-05-31 | 2004-07-01 |
  +--+++
  1 row in set (0.00 sec)
  SELECT DATE_FORMAT(created_date, '%m/%d/%y') AS created,
 status, user, comment1,  comment7, comment8, action
  INTO OUTFILE 'test5.txt'
  FIELDS TERMINATED BY '\,'
  OPTIONALLY ENCLOSED BY '\'
  LINES TERMINATED BY '\n';
  FROM users
  WHERE customerid = 'Customer'
  AND created_date BETWEEN @start AND @end
  ORDER BY created_date, status
Are you aware that BETWEEN is inclusive?  That is, this query will include 
rows from 5/31 and 7/01.

With mysql 4.1.1 or later, you could simplify the variable definitions slightly:
SELECT @start:= LAST_DAY(CURDATE() - INTERVAL 2 MONTH),
   @end:= LAST_DAY(CURDATE() - INTERVAL 1 MONTH) + INTERVAL 1 DAY;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Managing table quota

[Shashi Kiran]

Hi

Is it possible to change the table memory usage quota through mysql scripts as we do 
in Oracle.
We are migrating our application from Oracle 9i to MySQL does any body have experience 
in this area and are there some common problems faced during this procedure.

Any information will be helpful.

Regards
Shashi Kiran

Replication Performance

[MaFai]

Dear, [EMAIL PROTECTED],

We have set up 1 master and 4 slave as replication.
Sometime,the slave need 4~10 minutes to synchronize the data with master database.
Do any way to tune the performance?
Or any other way to reduce the time to replicate?


Best regards. 

MaFai
[EMAIL PROTECTED]
2004-07-01

Re: creating log files

[David King]

I figured it out, I had compiled one of its dependencies and not compiled 
mysql itself afterwards, so it was seg faulting and leaving behind those 
logs as part of bail-out

On Tue, 29 Jun 2004, David King wrote:
Date: Tue, 29 Jun 2004 17:53:57 -0700 (PDT)
From: David King [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: creating log files
I apologise for not lurking for longer before posting, but this is becoming 
increasingly important. This is a FreeBSD system, but it's standard MySQL 
(mysql Ver 12.22 Distrib 4.0.20, for portbld-freebsd5.0 (i386)). This 
morning, a user of my network told me that the Internet is down. Quick 
inspection of the machine revealed that the WinXP box had failed to receive 
an IP address from the DHCP server, the machine in question. I plugged a 
monitor into it (since I now couldn't boot another Unix machine that required 
NFS and couldn't talk to it from a windows box) to reveal a screen full of 
errors, most of them saying no room left on device or not enough inodes. 
A quick df -i revealed that indeed, /var had no inodes left, which is weird 
since it usually sits at about 6% inodes used (obviously dhcpd couldn't write 
out the new lease file because it had no inodes left, explaining the internet 
being down). du -d2|sort -n revealed that 95% of the inodes used were in 
/var/db/mysql, and a directory listing revealed several thousand files named 
innodb.status. where ? is a number from 0-9. (They look suspiciously like 
PIDs.) I've noticed that past few days that mysql, while sitting idle, has 
been taking up as much as 30% CPU, and I can't track down why it would be 
doing that. It does it in spurts, taking 6%, and then 30% right around the 
time it creates the files. It seemed to be created about one every twenty 
seconds, without even querying the database! Here's an example:

[EMAIL PROTECTED]:/var/db/mysql# while true; do sleep 60; ls inno*|wc -l; done
 5
 8
12
16
19
23
26
30
34
37 ^C
Obviously, this is a bad thing. A typical innodb.status. looks like
this:
[EMAIL PROTECTED]:/var/db/mysql# cat innodb.status.1959
=
040614 20:55:02 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 16 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 4, signal count 4
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 6, OS waits 3; RW-excl spins 1, OS waits 1
Does anyone know what could cause this? It looks to be, for whatever reason 
writing out status information. But why it would do that, and why it would 
take 30% CPU idling is beyong me. Any ideas? (I wrote that email a few days 
ago, here is an update:) It seems that now another set of files is being 
created in the same directory, with names like ib_arch_log_050412, those 
numbers change as more files are created. I can't time how many are being 
created per minute exactly, but here's an example like above:

[EMAIL PROTECTED]:/var/db$ while true; do sudo ls mysql | grep 
'inno\|ib_arch_log' | wc -l; sleep 60; done
  0
  8
 14
 22
 28
 36
 42
 49
 56
 64
 70
 76
 84
 90 ^C

The ib_arch_log_050444 files are not printable, and are all 2560 bytes 
long, owned by mysql:mysql.

My server houses one small database with two tables, and then the mysql 
internal databse and an empty test database, and to my knowledge nothing is 
strange about its setup. I am using the default my_small.cnf that installed 
with it (from FreeBSD ports), and if it has any modifications they are only 
things like hostname and so on. I did turn off IP ports for it, so it's 
only using /tmp/mysql.sock for connection. If any more information is needed 
I can provide it. My current fix is walking up to the machine every few hours 
and deleting the log files, which definately isn't acceptable. I apologise if 
I am in the wrong group for this, I would like if I could be directed to the 
correct one. Thank you.


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