Re: Limit characters in a long text

[Bill Adams]

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
I recommend "LEFT( )".

If you are looking to do it in PHP then this is the wrong email list.

Good luck.

b.


CodeHeads wrote:


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hello all,

Have a question:
I would like to limit the amount of data viewed. I searched but did not
find anything.
Here is my code:

$get_c = "select * from $table ORDER BY news_id DESC LIMIT 5";
$get_c_res = mysql_query($get_c) or die(mysql_error());

   while ($c = mysql_fetch_array($get_c_res)) {
$news_id = $c['news_id'];
   $news_posted = $c['news_posted'];
$news_subject = $c['news_subject'];
$NEWS_D = nl2br(stripslashes($c['news_desc']));
}

I would like to limit $NEWS_D to say 100 characters.

- --
Best regards,
~WILL~
Key: http://code-heads.com/keys/ch1.asc
Key: http://code-heads.com/keys/ch2.asc
Linux Commands: http://code-heads.com/commands
Linux Registered User: 406084 (http://counter.li.org/)
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFEIaDzfw3TK8jhZrsRAuZsAJ9S6QCvo6Pyru45qh1dzzANyD/OUgCgpuTG
RqcZaNUSgSA4TvHRusMzn18=
=P+Is
-END PGP SIGNATURE-

 



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

Re: copy one field value to another

[Bill Adams]

UPDATE table SET field_a=field_b;

b.


Ravi Malghan wrote:


Hi: is there a way to copy all field values from one
field to another from the mysql prompt?

Thanks
Ravi

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

 



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

Re: innodb in 4.1.18

[Bill Adams]

InnoDB always needs the shared tablespace because it puts its  
internal data dictionary and undo logs there. The .ibd files are  
not sufficient for InnoDB to operate.


well, thats what I found before. But it doesn't explain why InnoDB 
does need a logfile even when all transactions are committed or rolled 
back and mysql is shut down.


More is stored in the innodb log files besides a strict log of events.

It does explain, that I can use innodb_file_per_table which gives the 
impression that with that option a shared ibdata isn't required. Or 
maybe it is required during runtime, but not for a backup.


You might be able to set e.g. "innodb_data_file_path = ibdata:0M" but in 
any event the innodb log files track the existence of the different 
ibdata files (size, if they have been formatted, etc).


So with InnoDB it is not possible to shutdown the database server, 
backup the files and maybe use them on another server if the other 
server already has an ibdata and ib_logfile? Do I always have to do a 
mysqldump then? That takes much longer.


You can. But direct file system operations on MySQL (really any 
database) are dangerous. Same thing with why IMHO you should use "PURGE 
MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY);" rather than just 
deleting your binary logs directly (if you are using replication and all 
that). If you are going to copy the InnoDB files you probably need to 
have an exact setup. And as I am sure you have figured out you have to 
back up the .idb files and the innodb log files at the same time.


AFAIK, You cannot take the .idb file from one server and copy it to 
another server and have it just work (like you can with the MyISAM files).


b.


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

Re: innodb in 4.1.18

[Bill Adams]

Marten Lehmann wrote:

I had a lot of trouble today because the InnoDB integration in MySQL 
is lousy. I read the manual and worked with innodb_per_file_table. So 
when I shutdown mysql I should be able to delete ib_logfile0, 
ib_logfile1 and ibdata1, because all table-data should be stored in 
the .idb and .frm files. But this obviously is not the case! 


The ib_logfile* stores, among other things, transaction history and 
whatnot. If you delete the log files or they otherwise become corrupted, 
your InnoDB tables spaces become unusable and you have to rebuild the 
table space.


MySQL always reports errors that it can't find the table files, 
although they are in the directory of the database.
And additionally: If I'm removing the files from a databases and try 
to create a table that existed before (but now doesn't exist because 
the files are removed), I can't create it any more. What is the 
function of ib_logfile and ibdata? I expected them to store 
transaction data only, but the seem to store more. But documentation is 


The ibdata file(s) contain the table data and indexes. You need both the 
ibdata file(s) and the iblog file(s). The table definitions are still 
stored in the .frm files.


At this point it sounds like you will need to delete the .frm files for 
the tables and recreate the innodb files.


Good luck.

b.


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

Upper Limit to max_join_size? (4.1.18)

[Bill Adams]

I am having a problem where I cannot seem to increase the max_join_size 
of 4.1.18 above a hard limit that is way too low for my use. Has anyone 
run into this and know of a solution that does not involve upgrading to 
5.x.x? (That is presently not an option for another month or so.) I also 
want to avoid using SET SQL_BIG_SELECTS=1. I know in earlier versions 
(perhaps 4.0.x?) I could increase the value to what I wanted.


I will happily add the output from "show variables" if that will help.

Thanks in advance for your help.

--Bill


Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26460 to server version: 4.1.18-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select @@max_join_size;
+-+
| @@max_join_size |
+-+
|   705032704 |
+-+
1 row in set (0.00 sec)

mysql> SET sql_max_join_size=50;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@max_join_size;
+-+
| @@max_join_size |
+-+
|   705032704 |
+-+
1 row in set (0.00 sec)

mysql> SET sql_max_join_size=5000;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@max_join_size;
+-+
| @@max_join_size |
+-+
|5000 |
+-+
1 row in set (0.00 sec)

mysql> SET sql_max_join_size=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@max_join_size;
+-+
| @@max_join_size |
+-+
|   705032704 |
+-+
1 row in set (0.00 sec)

mysql>


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

RE: 'GROUP BY' behavior

[Bill Adams]

 
Shawn,

That's a very reasonable answer.  Thanks for pointing me to the
examples.  This one addresses the second question:
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.
html .  There is no example answering both questions in one query.

Regards,
Bill



From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, October 28, 2005 11:14 AM
I do not think you are posing a single-statment question to the
database (I count subqueries as second statements). 
You would like to find the averages of (used/allocated) and
(used) for all (qtreename,hostname) pairs. That's one question. 

The second question is to return the row with the greatest
timestamp for each (qtreename,hostname) pair along with the averages
calculated in the first question. 

To find the answer your first question is a simple GROUP BY
query. To find the answer to your second takes a max-of-group-pattern
query: 
http://dev.mysql.com/doc/refman/4.1/en/examples.html 

If it were me, I would use a temporary table for each stage and
combine them to form the final report. If you need more help, just come
back. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 
 


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

'GROUP BY' behavior

[Bill Adams]

All, 

In the following query, some of the values are averaged over several
rows, but some are not:

SELECT hostname, volname, qtreename, round(avg(used/allocated*100),0), 
round(avg(used)), allocated, available
FROM quota_entries
WHERE date_sub(now(), interval 1 day) < timestamp
GROUP BY qtreename,hostname

My questions: 
>From which of the several averaged rows do 'allocated' and 'available'
come in the results?
Can I control this?  I would like the row with maximum timestamp.

Thanks,
Bill

+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra  |
+---+--+--+-+-++
| id| int(10) unsigned |  | PRI | NULL| auto_increment |
| hostname  | varchar(32)  |  | | ||
| volname   | varchar(32)  |  | | ||
| qtreename | varchar(32)  |  | | ||
| allocated | int(10) unsigned |  | | 0   ||
| used  | int(10) unsigned |  | | 0   ||
| available | int(10) unsigned |  | | 0   ||
| files | int(10) unsigned | YES  | | 0   ||
| timestamp | datetime | YES  | MUL | NULL||
+---+--+--+-+-++

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

RE: MySQL + Access + MyODBC + LARGE Tables

[Bill Adams]

All, there were many emails posted about this on the MyODBC list which,
of course, can be viewed via the archive on the mysql.com site.  For the
most part I will neither quote nor repeat the information from those
emails here.


The conclusion is that MySQL + Merge Tables is perfectly capable of
being a data warehouse and is in fact better in most regards when
compared to other RDMBS.  One example: For similar record counts and
identical index definitions, speed wise MySQL and the "other" rdbms are
about the same when the query is disk bound (e.g. the index is not
cached). MySQL is 5-10x faster than the other rdbms in the cached index
case. There are many other benefits as well.  

(I will not name the other commercial RDBMS out of fear of lawyers, the
DCMA, and posting unauthorized benchmarks. You will have to trust me
that it is a major RDBMS, MySQL is /fast/ comparatively, and that I am
not an idiot at setting up and optimizing databases.)

Using MyODBC-3.51.01.01 works fine to access the MySQL database via MS
Access.  Venu (bless him for all of his help) is going to add
information to the FAQ as such: In the large table case one needs to
check off three options "Return Matching Rows", "Allow BIG Results", and
"Enable Dynamic Cursor".  I needed to do one last truly terrible hack to
MyODBC (patch below) so that if someone tries to open a very long table
(>43M rows in my test case) bad things don't happen as MySQL tries to
copy the results to a temporary table/file. Perhaps there could be a
config for "Max Rows When There Is No Criteria" in MyODBC?

In the next month or two I will try to write an article describing what
I did in more detail so that everyone may benefit.

b.

[bill@badams bill]$ cat myodbchack.patch 
--- ../myodbc-3.51.orig/execute.c   Fri Feb 22 10:55:35 2002
+++ execute.c   Fri Feb 22 10:53:48 2002
@@ -72,7 +72,26 @@
   query=tmp_buffer;
   }
 }
-  }
+  } 
+  /* Terrible hack by Bill Adams */
+  else if( 
+ !my_casecmp(query, "select", 6) &&
+ my_casecmp(query, "where", 5)   &&
+ my_casecmp(query, " limit ", 7) 
+ ){
+/* Limit the number of rows when someone does a query without
+   any criteria */
+char *tmp_buffer;
+uint length=strlen(query);
+if ((tmp_buffer=my_malloc(length+30,MYF(0
+  {
+   memcpy(tmp_buffer,query,length);
+   sprintf(tmp_buffer+length," limit %lu", 2); /* Arbitrary */
+   if (query != stmt->query)
+ my_free((gptr) query,MYF(0));
+   query=tmp_buffer;
+  }
+  }/* End Terrible Hack */
   pthread_mutex_lock(&stmt->dbc->lock);
   if (check_if_server_is_alive(stmt->dbc) ||
   mysql_query(&stmt->dbc->mysql,query))



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: MySQL + Access + MyODBC + LARGE Tables

[Bill Adams]

Spoiler: Venu's Suggestion about "Dynamic Cursor" is the answer


On Thu, 2002-02-14 at 20:34, Venu wrote:
> > MyODBC, as compiled today, uses mysql_store_result to get records.  This
> > is fine for reasonably sized tables.  However, if the table has millions
> > of records, writing the results to a temporary table has many
> > detrimental effects, e.g.: Access seems to hang from the user's
> > perspectiv, Access crashes because there are too many records for it to
> > handle at once (data requirements to great); MySQL creates HUGE
> > temporary tables or bombs if SQL_BIG_RESULT was not set.  
> 
> Probably we can add extra DSN options, to make use of either 
> mysql_store_result() or mysql_use_result(). In the second 
> case, lot of code change is needed in all result set dependency 
> APIs too.  

That would be nice but perhaps unneeded (see below about your
suggestion).


> > So in the case of a very long table, it is important to use
> > mysql_use_result instead.  This makes it so that results are returned
> > right away and eases the load on all programs involved.  The astute
> > reader will realize that if one uses mysql_use_result and does not fetch
> > all of the records, the next query will return the remaining records
> > from the previous query first.  It follows that Access bombs because in
> > statement #2 it is getting results from statement #1. (This is seen from
> > the myodbc.log line: " | error: message: Commands out of sync;  You
> > can't run this command now" in the myodbc3.dll changed to use the said
> > function.)
> 
> Can you be more specific on this ? And a MS ODBC DM trace will be better 
> to analyze.

Sorry, I should have been clearer about this.  Yesterday (Thursday) I
downloaded the bk source.  Aside from many other hacks, I changed
execute.c:do_query to use mysql_use_result() instead of
mysql_store_result().  In THIS version, I got the "Commands out of sync"
error.  To better show what is happening, I just got the souce again,
made the said modification and a couple of more verbose debugging output
modifications.  In the setup, I had checked off "Return Matching
Records" and "Trace...". Here is the sequence of what is happening:


[bill@badams myodbc-3.51]$ grep -E 'SQLFree|SQLPre|sync' myodbc.log 
>SQLFreeHandle
| info: SQLFreeHandle: 157150
| >SQLFreeConnect
| SQLFreeHandle
| info: SQLFreeHandle: 154988
SQLPrepare
| | info: SQLPrepare: 15bd68  SELECT Config, nValue FROM MSysConf
| SQLFreeStmt
| | enter: SQLFreeStmt: 15bd68  option: 1000
| SQLFreeHandle
| info: SQLFreeHandle: 15bd68
| >SQLFreeStmt
| | enter: SQLFreeStmt: 15bd68  option: 1
| SQLPrepare
| | info: SQLPrepare: 15bd68  SELECT
`pcm_test_header_200202`.`serial_hi`,`pcm_test_header_200202`.`ymd_ts`
FROM `pcm_test_header_200202` 
| SQLFreeStmt
| | enter: SQLFreeStmt: 15bd68  option: 1000
| SQLPrepare
| info: SQLPrepare: 15c780  SELECT [column names removed --bill] FROM
`pcm_test_header_200202`  WHERE `serial_hi` = ? AND `ymd_ts` = ? OR
`serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR
`serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR
`serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR
`serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR
`serial_hi` = ? AND `ymd_ts` = ?
SQLFreeStmt
| | enter: SQLFreeStmt: 15c780  option: 1000
| SQLFreeStmt
| enter: SQLFreeStmt: 15c780  option: 0
SQLFreeHandle
| info: SQLFreeHandle: 15c780
| >SQLFreeStmt
| | enter: SQLFreeStmt: 15c780  option: 1
| SQLFreeHandle
| info: SQLFreeHandle: 15bd68
| >SQLFreeStmt
| | enter: SQLFreeStmt: 15bd68  option: 1
|  > The bottom line is that in order for MySQL + Access + MyODBC to be
> > usable as a datawarehouse MySQL/MyODBC (a) must be able to return
> > uncached results; and (b) be able to have multiple statements open,
> > active, and with pending data to be fetched at the same time.
> 
> Try to use Dynamic Cursor Type (OPTION=32) in MyODBC 3.51.

YES!  The stock 3.51.01.01 myodbc3.dll with Dynamic Cursor Type, Allow
BIG Results, and Return Matching rows is the ticket. AFAIK, this
satisfies my needs.  I will get back later next week after I do some
more testing.


b.





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MySQL + Access + MyODBC + LARGE Tables

[Bill Adams]

Monty, Venu, I hope you read this... :)


I really, really want to use MySQL as the database backend for my
datawarehouse.  Mind you I have played around with merge tables quite a
bit and know that MySQL is more than up to the task.  There are numerous
(not necessarily cost related) reasons as to why MySQL is better for my
application. If it were just me, it would be a slam-dunk as I only use
perl, etc. to extract data from the database.  However most of my users
use MS Access as a front end and extraction tool.

When pulling datasets from a database, Access tries to be smart and if
there is what it thinks is a primary key on a table, it will extract the
values of the primary key for the matching records and then re-query the
table with a parameterized query to get the rest of the values.  This is
true in both the case where a user tries to view a table or runs a
simple query.

Taking a simple case of the user opening the table in data sheet view
(if this is solved, the other cases will be solved too), the following
happens -- okay, this is a bit simplified, see my message "Large
Datasets w/Access" for better background:
http://lists.mysql.com/cgi-ez/ezmlm-cgi?5:mss:4918:200202:bjcebaokcknfmaldpokp

-- Access opens a statement handle (#1) and queries the table for the
primary key values.  E.g. It would pass "SELECT idx FROM TABLE".  Note
that it only cares about getting a partial list here.  I.e. if the
screen only shows 10 records, Access only cares about 10 primary key
values.

-- Access opens a second statement handle (#2) without closing the first
handle and then gets the values in a parameterized query. E.g.: "SELECT
a, b, idx FROM table WHERE idx=? OR idx=?...".  It then pulls the
records it cares about with this statement and closes the statement.

-- If, say, the user presses "page down", [I think] access then gets the
next set of primary key values from statement handle #1, sets up another
prepared query and gets the values as above.


MyODBC, as compiled today, uses mysql_store_result to get records.  This
is fine for reasonably sized tables.  However, if the table has millions
of records, writing the results to a temporary table has many
detrimental effects, e.g.: Access seems to hang from the user's
perspectiv, Access crashes because there are too many records for it to
handle at once (data requirements to great); MySQL creates HUGE
temporary tables or bombs if SQL_BIG_RESULT was not set.  

So in the case of a very long table, it is important to use
mysql_use_result instead.  This makes it so that results are returned
right away and eases the load on all programs involved.  The astute
reader will realize that if one uses mysql_use_result and does not fetch
all of the records, the next query will return the remaining records
from the previous query first.  It follows that Access bombs because in
statement #2 it is getting results from statement #1. (This is seen from
the myodbc.log line: " | error: message: Commands out of sync;  You
can't run this command now" in the myodbc3.dll changed to use the said
function.)

The bottom line is that in order for MySQL + Access + MyODBC to be
usable as a datawarehouse MySQL/MyODBC (a) must be able to return
uncached results; and (b) be able to have multiple statements open,
active, and with pending data to be fetched at the same time.

SO

Does anyone have any suggestions on how to accomplish this?  

How difficult would it be (for a relatively good C/C++ programmer) to
alter mysqld so that mysql_use_result could handle multiple statements
open at the same time?

Other suggestions...?


Thanks for reading this and your time.


--Bill
(all opinions are mine, bla bla bla)
(I am on the MyODB list but not the MySQL list at the moment)




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: index questions 2nd request

[Bill Adams]

rick herbel wrote:

> Question - Why is key len,ref  null ?? Is it not using my key? Why does it
> say type range
> here and below it says type ref the table has 134000 records in it so it
> should be using key.

Have you run myisamchk -a on the table? (Or ANALYZE TABLE from the sql prompt?)

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: error codes question

[Bill Adams]

Cindy wrote:

> I tried checking for error codes in the documentation, but didn't find
> anything particularly useful.
>
> I'm getting this:
> mysql> load data infile '/export/home/Scratch/WordIndex.txt' into table wordindex 
>(aid, wid, wordform, start, length);
> ERROR 1030: Got error 28 from table handler

Try "perror 28" (under ./mysql/bin/perror)

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Another basic question - index()

[Bill Adams]

Etienne Marcotte wrote:

> What is the exact difference between
> this
> index(col1,col2).

Create a single index on col1 and col2.

>
> that
> index(col1),index(col2).

Creat two indexes, one on col1, one on col2.

>
> and this
> index(col1),index(col2),index(col1,col2).

Do all three.  Note that index(col1) is covered by index(col1, col2).

b.
mysql


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Should I use a MERGE table?

[Bill Adams]

"Anthony R. J. Ball" wrote:

>   Ok, so to follow... Paul Dubois told me the little
> RENAME TABLE trick to swap out a table... Will that work
> on a table in a MERGE, or are there different hoops to
> jump through?

Your table names need to be unique.  Then it should work just fine.

o If you do rename a table that is part of the merge, you will need to rebuild
the merge table.

o If you want to reindex the tables that are merged, be sure to either drop the
index in the merge table or drop the merge table BEFORE you reindex the real
tables.  This is not fatal if you forget: just you might get weird errors.

o If you do a SELECT ... WHERE indexed_column=value and get back a bunch of
records where everything is blank, your indexes (or possibly columns) are not the
same between the real tables and/or merge table.  Remember ORDER MATTERS!

b.




>
>
> On Thu, Nov 15, 2001 at 02:01:44PM -0800, Bill Adams wrote:
> > "Anthony R. J. Ball" wrote:
> >
> > >   I was just reading DOCS for MERGE tables... and they
> > > look like exactly what I want.
> > >
> > >   I have a large lookup table that I use to track down
> > > where I want to find my data, which gets loaded into
> > > different tables at different times of the day
> > > (financial data). Now I am loading all the data into
> > > one table, but since this table needs to stay available
> > > I have to insert into it with lots of keyed data.
> > >
> > >   I am thinking that I can create a new table, and
> > > replace the proper underlying table with the new
> > > data. That way I don't slow doen the lookup table
> > > when I am loading, and I can load my data MUCH faster
> > > by loading with no keys.
> > >
> > >   Am I right in thinking this way. So I can make
> > > a muni lookup and a CMO lookup and an MBS lookup
> > > but just select from the merged table for a cusip
> > > in any of them?
> > >
> > >   This is all read only data (except the initial load)
> > >
> > >   This would be very good... since I want the data to
> > > load quick, and may be loading hundreds of thousands
> > > of records at a time.
> > >
> >
> > Merge table sound like they fit the bill.  Beware that they are very finicky
> > and the columns MUST be the same and the index order must be the same.
> >  Search the lists (esp. for my name) for more info.
> >
> > b.
> >
> >
>
> --
>  ___  __  ____  _  _  _  _    
> / __)(  )(  )  /__\( \/ )( ___)  ( \( )( ___)(_  _)
> \__ \ )(__)(  /(__)\\  /  )__))  (  )__)   )(
> (___/(__)(__)(__)\/  ()()(_)\_)() (__)
> Once I thought I was wrong - but I was mistaken

--
Bill Adams
TriQuint Semiconductor




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Should I use a MERGE table?

[Bill Adams]

"Anthony R. J. Ball" wrote:

>   I was just reading DOCS for MERGE tables... and they
> look like exactly what I want.
>
>   I have a large lookup table that I use to track down
> where I want to find my data, which gets loaded into
> different tables at different times of the day
> (financial data). Now I am loading all the data into
> one table, but since this table needs to stay available
> I have to insert into it with lots of keyed data.
>
>   I am thinking that I can create a new table, and
> replace the proper underlying table with the new
> data. That way I don't slow doen the lookup table
> when I am loading, and I can load my data MUCH faster
> by loading with no keys.
>
>   Am I right in thinking this way. So I can make
> a muni lookup and a CMO lookup and an MBS lookup
> but just select from the merged table for a cusip
> in any of them?
>
>   This is all read only data (except the initial load)
>
>   This would be very good... since I want the data to
> load quick, and may be loading hundreds of thousands
> of records at a time.
>

Merge table sound like they fit the bill.  Beware that they are very finicky
and the columns MUST be the same and the index order must be the same.
 Search the lists (esp. for my name) for more info.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: getting multiple columns per row from databse query

[Bill Adams]

Try:

$i++;
if( $i % 3 == 0 ){
  echo "\n\n";
}


b.
mysql

[EMAIL PROTECTED] wrote:

> I have a database that is names and addresses. I want to make a web page
> that will display those
> names and addresses in a table with 4 columns per row, as many rows as necessary. 
>The little
> if statement below works to make 2 columns, but when I change the number to 3 or 
>higher it no longer
> works properly - I get the 3 or more columns wrapped to look like multiple rows. 
>(there's on long line,
> hopefully it will wrap properly).
>
>  (html and connection lines snipped)...
> $sql = "select * from brochures";
> $result = mysql_query($sql);
> while ($row = mysql_fetch_array($result))
> {
> printf("%s%s%s%s%s%s, %s %s\n", $row["Account"], 
>$row["name"], $row["email"], $row["Address"], $row["Address2"], $row
> ["City"], $row["State"], $row["Zip"]);
> if ($i % 2)
> {
> echo "\n\n";
> }
> $i++;
> }
> ?>
>
> --
> Chip W
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

--
Bill Adams
TriQuint Semiconductor




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Text qualifiers

[Bill Adams]

Won Lee wrote:

> Perhaps...
>
> I may be missing something here.
> Because I have about 15 seperate Access DBs on my workstation and don't want
> to create 15 system ODBC connections for once in a life import, I thought
> doing it via a text file would be easier.
>
> Unless you are talking about something else.

No.  Because you have memo fields in Access with funny characters (new-lines),
this may be your easiest option.

You only have to create one DSN.  Make it to a temporary database that has your
new tables in it.  Export the data with the select * into  Then if you want
to move the data to a different db you can move either the .MYI, .MYD, and .frm
to the right place OR you can use mysqldump.

b.



>
>
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bill Adams
> Sent: Wednesday, November 14, 2001 5:31 PM
> To: Won Lee
> Cc: mysqlList
> Subject: Re: Text qualifiers
>
> Won Lee wrote:
>
> > I have an slew of Access DBs that I need to port some of the info into a
> > MySQL DB.
> > I guess my main problem is my I have no idea in what format to export my
> > data from the Access DB.
> > In general I export it as a tab delimited file with no text qualifiers.
> > Unfortunately, much of the data has a carriage return as part of the data.
> > The carriage returns terminate the line and my import fails.
> >
> > I tried to wrap the text in double quotes to tell it not to terminate the
> > line.  However, my imports have still failed.  This could be due to my
> lack
> > of experience or wrong syntax.  But I did everything as told in the
> > documntations.
>
> Not really an answer to your question but you can also "select * into table"
> from access.
>
> b.
> mysql

--
Bill Adams
TriQuint Semiconductor




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Text qualifiers

[Bill Adams]

Won Lee wrote:

> I have an slew of Access DBs that I need to port some of the info into a
> MySQL DB.
> I guess my main problem is my I have no idea in what format to export my
> data from the Access DB.
> In general I export it as a tab delimited file with no text qualifiers.
> Unfortunately, much of the data has a carriage return as part of the data.
> The carriage returns terminate the line and my import fails.
>
> I tried to wrap the text in double quotes to tell it not to terminate the
> line.  However, my imports have still failed.  This could be due to my lack
> of experience or wrong syntax.  But I did everything as told in the
> documntations.

Not really an answer to your question but you can also "select * into table"
from access.

b.
mysql



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Non-Buffered mysqld

[Bill Adams]

Jeremy Zawodny wrote:

> On Wed, Nov 14, 2001 at 12:07:04PM -0800, Bill Adams wrote:
> > The most important exception is the mysql_use_result attribute: This
> > forces the driver to use mysql_use_result rather than
> > mysql_store_result. The former is faster and less memory consuming,
> > but tends to block other processes. (That's why mysql_store_result
> > is the default.)
>
> What does "block other processes" really mean?  Does it mean "MySQL
> will hold the lock longer for the query because of the network I/O" or
> something completely different?

I think it is referring to the situation where the table only has table level
locking and the client fetches some returned rows --but not all rows-- and holds
the statement handle open.  Then the table may be locked until the client
program closes the handle or finishes fetching the results.

But I could be 100% wrong on this.  I have not tested this theory.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Non-Buffered mysqld

[Bill Adams]

Man I just cannot stop replying to myself...


Bill Adams wrote:

> Bill Adams wrote:
>
> > Rick Emery wrote:
> >
> > > If you use PHP, the answer is:mysql_unbuffered_query()
> >
> > Well, I get the delay if I query with perl DBI or enter the query directly in
> > the mysql client.   perl DBI + Informix does not buffer so I know that it is
> > not that combo. (I guess it could be the DBD::mysql.)
> >
> > While the query is running w/o any results being returned yet, the process
> > list shows "sending data".
>
> Ah yes, I see now there is a -q option for the mysql client.  (Searched docs for
> 'cache' instead of '[un]buffered'.)
>
> I do not see anything for DBD::mysql (anyone?)

Rick, thanks for the hint.  With it and google, I found that for DBD::mysql it is a
bit different:

The most important exception is the mysql_use_result attribute: This forces the
driver to use mysql_use_result rather than mysql_store_result. The former is faster
and less memory consuming, but tends to block other processes. (That's why
mysql_store_result is the default.)

   To set the mysql_use_result attribute, use either of the following:

 my $sth = $dbh->prepare("QUERY", { "mysql_use_result" => 1});

   or

 my $sth = $dbh->prepare("QUERY");
 $sth->{"mysql_use_result"} = 1;


Thanks again Rick.

b.


> > > -Original Message-
> > > From: Bill Adams [mailto:[EMAIL PROTECTED]]
> > > Sent: Wednesday, November 14, 2001 11:38 AM
> > > To: Mysql List
> > > Subject: Non-Buffered mysqld
> > >
> > > MySQL 4.0
> > >
> > > Is there a way to prevent mysql(d) from buffering
> > > the output ala 'mysqldump -q'?
> > >
> > > The specific case I am thinking of is a simple
> > > SELECT ... FROM ... WHERE (with no ORDER BY, no
> > > GROUP BY, etc.).  In this case there is no reason
> > > [that I can think of] why mysqld cannot start
> > > returning records as it finds them.  Informix
> > > behaves this way.
> > >
> > > The reason this is important is there is real
> > > speed and perceived speed.  I am looking at
> > > replacing my current data warehouse with MySQL.
> > > Although MySQL and that other database have about
> > > the same overall rows/sec when nothing is cached
> > > (actually MySQL is probably about 1.25-2 x faster
> > > overall in the non-cached/no order by clause), the
> > > delay with MySQL makes it SEEM slower because the
> > > user is sitting there waiting for anything to
> > > happen.  When the rows retrieved counter is, well,
> > > counting up, albeit even slowly, the user at least
> > > knows she is getting results.
> > >
> > > It would also take less memory if the results were
> > > not cached.
> > >
> > > If someone that knows the source want to point me
> > > to the right source file(s), I can take a look at
> > > fixing this myself. *eek*
> > >
> > > b.
> > >
> > > -
> > > Before posting, please check:
> > >http://www.mysql.com/manual.php   (the manual)
> > >http://lists.mysql.com/   (the list archive)
> > >
> > > To request this thread, e-mail <[EMAIL PROTECTED]>
> > > To unsubscribe, e-mail <[EMAIL PROTECTED]>
> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> > --
> > Bill Adams
> > TriQuint Semiconductor
>
> --
> Bill Adams
> TriQuint Semiconductor

--
Bill Adams
TriQuint Semiconductor




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Non-Buffered mysqld

[Bill Adams]

Bill Adams wrote:

> Rick Emery wrote:
>
> > If you use PHP, the answer is:mysql_unbuffered_query()
>
> Well, I get the delay if I query with perl DBI or enter the query directly in
> the mysql client.   perl DBI + Informix does not buffer so I know that it is
> not that combo. (I guess it could be the DBD::mysql.)
>
> While the query is running w/o any results being returned yet, the process
> list shows "sending data".

Ah yes, I see now there is a -q option for the mysql client.  (Searched docs for
'cache' instead of '[un]buffered'.)

I do not see anything for DBD::mysql (anyone?)

Is there a general option or a query that can be run to have the same effect?

b.


> > -Original Message-
> > From: Bill Adams [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, November 14, 2001 11:38 AM
> > To: Mysql List
> > Subject: Non-Buffered mysqld
> >
> > MySQL 4.0
> >
> > Is there a way to prevent mysql(d) from buffering
> > the output ala 'mysqldump -q'?
> >
> > The specific case I am thinking of is a simple
> > SELECT ... FROM ... WHERE (with no ORDER BY, no
> > GROUP BY, etc.).  In this case there is no reason
> > [that I can think of] why mysqld cannot start
> > returning records as it finds them.  Informix
> > behaves this way.
> >
> > The reason this is important is there is real
> > speed and perceived speed.  I am looking at
> > replacing my current data warehouse with MySQL.
> > Although MySQL and that other database have about
> > the same overall rows/sec when nothing is cached
> > (actually MySQL is probably about 1.25-2 x faster
> > overall in the non-cached/no order by clause), the
> > delay with MySQL makes it SEEM slower because the
> > user is sitting there waiting for anything to
> > happen.  When the rows retrieved counter is, well,
> > counting up, albeit even slowly, the user at least
> > knows she is getting results.
> >
> > It would also take less memory if the results were
> > not cached.
> >
> > If someone that knows the source want to point me
> > to the right source file(s), I can take a look at
> > fixing this myself. *eek*
> >
> > b.
> >
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
> --
> Bill Adams
> TriQuint Semiconductor

--
Bill Adams
TriQuint Semiconductor




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Non-Buffered mysqld

[Bill Adams]

Rick Emery wrote:

> If you use PHP, the answer is:mysql_unbuffered_query()

Well, I get the delay if I query with perl DBI or enter the query directly in
the mysql client.   perl DBI + Informix does not buffer so I know that it is
not that combo. (I guess it could be the DBD::mysql.)

While the query is running w/o any results being returned yet, the process
list shows "sending data".

b.


>
>
> -Original Message-----
> From: Bill Adams [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, November 14, 2001 11:38 AM
> To: Mysql List
> Subject: Non-Buffered mysqld
>
> MySQL 4.0
>
> Is there a way to prevent mysql(d) from buffering
> the output ala 'mysqldump -q'?
>
> The specific case I am thinking of is a simple
> SELECT ... FROM ... WHERE (with no ORDER BY, no
> GROUP BY, etc.).  In this case there is no reason
> [that I can think of] why mysqld cannot start
> returning records as it finds them.  Informix
> behaves this way.
>
> The reason this is important is there is real
> speed and perceived speed.  I am looking at
> replacing my current data warehouse with MySQL.
> Although MySQL and that other database have about
> the same overall rows/sec when nothing is cached
> (actually MySQL is probably about 1.25-2 x faster
> overall in the non-cached/no order by clause), the
> delay with MySQL makes it SEEM slower because the
> user is sitting there waiting for anything to
> happen.  When the rows retrieved counter is, well,
> counting up, albeit even slowly, the user at least
> knows she is getting results.
>
> It would also take less memory if the results were
> not cached.
>
> If someone that knows the source want to point me
> to the right source file(s), I can take a look at
> fixing this myself. *eek*
>
> b.
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

--
Bill Adams
TriQuint Semiconductor




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: myisamchk -a + indexes + hidden...?

[Bill Adams]

Jon Gardiner wrote:

> Analyzing a table requires looking at every
> record in the table.  If the
> table you are dealing with is a large one then
> there is a good chance that
> after analyzing the table your disk cache will
> not contain the records that
> you are trying to grab.  Once you run the query
> it will almost certainly
> contain those records.  You could test this
> theory by rebooting your machine
> (if that is an option) and seeing if it takes
> the longer amount of time for
> the first query.

*sigh*  I think you are right.  I cannot reboot
this machine durring the day. But I wrote a script
to rewrite the MyISAM files and it seems to have
the same effect as myisamchk -a was having.



b.


#!/bin/bash
#
DISKS="3p0 3p1 4p0 4p1 5p0 5p1"
for disk in $DISKS; do
  PATH=/var/flashdisk/$disk/mysql/pcm_test
  for file in $PATH/*.MYI $PATH/*.MYD; do
TMP=$file.tmp
echo
echo $file
/bin/mv -v $file $TMP
if [ $? != 0 ]; then
  echo "Could not move";
  exit;
fi
/bin/cat $TMP > $file
if [ $? != 0 ]; then
  echo "Could not cat the file?"
  exit
fi
/bin/rm -fv $TMP
if [ $? != 0 ]; then
  echo "Could not remove temp file?"
  exit
fi
  done
done


>
>
> Or I could be completely off base.  It wouldn't
> be the first time.  :-)
>
> Jon Gardiner.
>
> > -Original Message-
> > From: Bill Adams [mailto:[EMAIL PROTECTED]]
> > Sent: Tuesday, November 13, 2001 2:47 PM
> > To: Mysql List
> > Subject: myisamchk -a + indexes + hidden...?
> >
> >
> > It seems like myisamchk -a is hosing some
> > statistic in a MyISAM table that gets
> re-created
> > and stored permanently as once a query is run
> that
> > uses that index, it always runs well until
> > myisamchk -a is run again even between
> restarts of
> > mysqld.  It also seems that key_buffer_size
> has no
> > effect on the results.
> >
> > Can someone explain this to me?
> >
> > There are a a bunch of tables merged into
> three
> > main tables.  The query does a two column join
>
> > between the tables, e.g.: a.1=b.1 AND a.2=b.2
> AND
> > b.1=c.1 AND b.3=c.3
> >
> > --Bill
> >
> >
> > [root@host /usr/local/mysql-4.0/var]#
> > ../bin/mysqladmin -uroot -p version
> > ../bin/mysqladmin  Ver 8.22 Distrib
> 4.0.0-alpha,
> > for pc-linux-gnu on i686
> > Copyright (C) 2000 MySQL AB & MySQL Finland AB
> &
> > TCX DataKonsult AB
> > This software comes with ABSOLUTELY NO
> WARRANTY.
> > This is free software,
> > and you are welcome to modify and redistribute
> it
> > under the GPL license
> >
> > Server version  4.0.0-alpha-log
> > Protocol version10
> > Connection  Localhost via UNIX
> socket
> > UNIX socket /tmp/mysql-4.0.sock
> > Uptime: 7 min 2 sec
> >
> > Threads: 3  Questions: 103  Slow queries: 0
> > Opens: 12  Flush tables: 46  Open tables: 3
> > Queries per second avg: 0.244
> > [root@host /usr/local/mysql-4.0/var]# uname -a
>
> > Linux host.tqs.com 2.2.19 #6 SMP Wed Jul 11
> > 10:55:03 PDT 2001 i686 unknown
> > [root@host /usr/local/mysql-4.0/var]# vmstat
> >procs  memory
> > swap  io system cpu
> >  r  b  w   swpd   free   buff  cache  si  so
> > bibo   incs  us  sy  id
> >  2  1  0  4   2612  40752 1450688   0   0
> > 4 33 6  10   2   6
> > [root@host /usr/local/mysql-4.0/var]# ldd
> > ../libexec/mysqld
> > librt.so.1 => /lib/librt.so.1
> (0x2aac8000)
> >
> > libdl.so.2 => /lib/libdl.so.2
> (0x2aacc000)
> >
> > libpthread.so.0 =>
> /lib/libpthread.so.0
> > (0x2aad)
> > libz.so.1 => /usr/lib/libz.so.1
> > (0x2aae3000)
> > libcrypt.so.1 => /lib/libcrypt.so.1
> > (0x2aaf3000)
> > libnsl.so.1 => /lib/libnsl.so.1
> > (0x2ab2)
> > libstdc++-libc6.1-1.so.2 =>
> > /usr/lib/libstdc++-libc6.1-1.so.2 (0x2ab36000)
>
> > libm.so.6 => /lib/libm.so.6
> (0x2ab78000)
> > libc.so.6 => /lib/libc.so.6
> (0x2ab95000)
> > /lib/ld-linux.so.2 =>
> /lib/ld-linux.so.2
> > (0x2aaab000)
> > [root@host /usr/local/mysql-4.0/var]#
> >
> >
> > myisamchk -a (on all tables)
> > set-variable= key_buffer=32M
> > Time to start getting results: 127 seconds.
> > Total Time: 146 seconds (18251 rows, 125
> rows/sec
>

Re: Alphabetizing book titles

[Bill Adams]

Christian Stromberger wrote:

> Is there any way to build an index like this (to ignore A, An, The) for
> faster title sorts?  Any helpful advice for a newbie appreciated!

Not really.

You could create a new column that has the sort name and put an index on
that.

But really, if you are going to search the title for key words you want a
fulltext index which will ignore words less than four characters by default
anyway.

b.


>
>
> -Chris
>
> > -Original Message-
> > From: Denis Rudakov [mailto:[EMAIL PROTECTED]]
> > Sent: Tuesday, November 13, 2001 2:03 AM
> > To: [EMAIL PROTECTED]
> > Subject: Re: Alphabetizing book titles
> >
> >
> > Hi.
> >
> > Try this:
> >
> > SELECT title FROM titles
> > ORDER BY
> >   IF(SUBSTRING(title,1,4)="The ",SUBSTRING(title,5),
> >   IF(SUBSTRING(title,1,2)="A ",SUBSTRING(title,3),
> >   IF(SUBSTRING(title,1,3)="An ",SUBSTRING(title,4),
> > title)));
> >
> > But in version 3.23.36 the next:
> > SELECT title FROM titles
> > ORDER BY
> >   CASE
> >   WHEN SUBSTRING(title,1,4)="The "
> >   THEN SUBSTRING(title,5)
> >   WHEN SUBSTRING(title,1,2)="A "
> >   THEN SUBSTRING(title,3)
> >   WHEN SUBSTRING(title,1,3)="An "
> >   THEN SUBSTRING(title,4)
> >   ELSE title
> >   END;
> >
> > works right.
> >
> > Goodbye.
> > Dannis.
> >
> > On Thu, Nov 01, 2001 at 01:09:52PM -0500, Ian M. Evans wrote:
> > > Back when I was working with MSSQL I needed to alphabetize
> > movie titles in
> > > the proper library format where 'A' 'An" and 'The" are ignored.
> > >
> > > For MSSQL I was told to use:
> > > select * from titles order by case when title like 'The %' then
> > substring
> > > (title, 5, 255) when title like 'A %' then substring (title, 3,
> > 255) when
> > > title like 'An %' then substring (title, 4, 255) else Title end
> > >
> > > That worked like a charm, yet MySQL doesn't seem to accept that. Any
> > > solutions or advice?
> > >
> > > --
> > > Ian Evans
> > > Digital Hit Entertainment
> > > http://www.digitalhit.com
> > >
> > >
> >
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

--
Bill Adams
TriQuint Semiconductor




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Non-Buffered mysqld

[Bill Adams]

MySQL 4.0

Is there a way to prevent mysql(d) from buffering
the output ala 'mysqldump -q'?

The specific case I am thinking of is a simple
SELECT ... FROM ... WHERE (with no ORDER BY, no
GROUP BY, etc.).  In this case there is no reason
[that I can think of] why mysqld cannot start
returning records as it finds them.  Informix
behaves this way.


The reason this is important is there is real
speed and perceived speed.  I am looking at
replacing my current data warehouse with MySQL.
Although MySQL and that other database have about
the same overall rows/sec when nothing is cached
(actually MySQL is probably about 1.25-2 x faster
overall in the non-cached/no order by clause), the
delay with MySQL makes it SEEM slower because the
user is sitting there waiting for anything to
happen.  When the rows retrieved counter is, well,
counting up, albeit even slowly, the user at least
knows she is getting results.

It would also take less memory if the results were
not cached.

If someone that knows the source want to point me
to the right source file(s), I can take a look at
fixing this myself. *eek*


b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqld shutdown

[Bill Adams]

>
> PS:  If I run the mysqladmin shutdown command, then check "top" in
> another console, both the daemon and the mysqladmin command continue to
> run indefinitely -- and I have waited an hour to see if it's just slow.
>  That doesn't seem to be the case.
>

This seems like an OS bug to me.  All of the things you mention in your last
email should stop mysql from running.

What happens if you do (where safe_mysqld is running) 'fg' and
then CTRL+c?  Does that just hang too?

Do you need the debugging for mysql? (it slows it down).  Perhaps there is a
problem with that and recompiling w/o --with-debug would help.


b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqld shutdown

[Bill Adams]

Erik Price wrote:

> All,
>
> Well, I solved the mystery of the missing Unix socket.  It needs to be
> defined at the time the daemon is started with:
> --socket=/path/to/socket.   Well, at least in my installation it does.
>
> But now I'm having the OPPOSITE problem -- shutting down the server
> (the daemon).  I've searched the issue at length in the documentation,
> as well as in DuBois' "MySQL" (New Riders), and found quite a bit of
> information about shutting down the server.  One option is to use
>
> /usr/local/mysql/bin:mysql$ mysqladmin -u root -p shutdown
>
> But this one doesn't work for me, even though I am using the proper
> password and am the proper user.  Executing this command just leaves me
> hanging at the prompt (and the only way to get out of the hang is to
> either suspend the job, which also has no effect).

Do you have a query still running? E.g. does mysqladmin -uroot -p
processlist show any locks?  If so, can you kill the thread with mysqladmin
kill?

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Search Engines

[Bill Adams]

"Karl J. Stubsjoen" wrote:

> Hello,
>
> I need to create a search engine out of a few MySQL tables  I should
> say:  I need to search MySQL records like a search engin might.  However, my
> first go ended up as a complete failure because it is highly un-optimized to
> search for (as an example) %apple% in all of the available text fields.
>
> Any ideas about where I can look to set up a database optimized for
> searching in this way?

Read the manual and list threads on fulltext indexes.

b.
mysql



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Show query page by page

[Bill Adams]

Auri Net SAC wrote:

> Hi,
>
> I have a query result with 50 register and i want to show them in 5 pages,
> every page show 10 register.

LIMIT (see the manual) will help you.

b.
mysql



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Same table aliased twice causes infinite loop

[Bill Adams]

Sinisa Milivojevic wrote:

> Nick de Voil writes:
> > I am running MySQL 3.23 on Windows 2000.
> >
> > I have a SQL statement which looks fine to me, although it does reference
> > the same table twice.
> >
> > Here it is:
> >
> > SELECT DISTINCT u.inserted_usr_id, g.inserted_ugp_id FROM raw_users u,
> > raw_data d, raw_groups g, raw_groups gg WHERE u.forename = d.forename AND
> > u.surname = d.surname AND g.group_name = CONCAT(d.subject,' ',d.set_name)
> > AND d.yr_and_reg = gg.group_name AND gg.parent_ugp_id = g.parent_ugp_id;
> >
> > When I try to run this, either from the Java servlet where it lives, or just
> > via the mysql command line, my PC goes into a tailspin.
> >
> > Does MySQL  not support aliasing the same table twice in one SQL statement?
> >
> > Thanks
> >
> > Nick
> >
>
> Hi!
>
> MySQL allows as many aliases as there could be tables in the join.
>
> Look for the tailspin somewhere else ...

For instance, a missing join clause.  It could be trying to do every permutation
of the results wich N! gets big fast.

b.
mysql



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Timezone offset question

[Bill Adams]

Jeremy Wilson wrote:

> At 01:07 PM 11/13/01 -0600, Gerald Clark wrote:
> >Start the server with the desired TZ set.
>
> That's all fine and good, but difficult switch back and forth every 10
> minutes while that query runs for local data, then for the remote data.

The way I deal with it (and I don't know that it will help you) is that I
store datetime as an integer from time() GMT.  Then in the report I convert
the time to the correct timezone based on the user's IP (internal network,
different subnets for different TZ/locations).  Of course this is a bad idea
sometime in 2039 (?) or the end of Unix time as we know it.

b.
mysql


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: table corrupted after an error free load

[Bill Adams]

Riccardo Cohen wrote:

> Hi,
> Sorry to disturb, but I cannot find any answer in online doc and web archive.
>
> >Description:
> I insert 34000 rows in a simple table with all text fields, and myisamchk 
>tells the table is corrupted, while a select into outfile does not give any error 
>compared to the original file.
>

select * into outfile... does not use any indexes (I think) so if it is only your 
index that is corrupted then there will be no problem.

If the data file is corrupted such that there is an extra bad record, I would bet that 
it just gets skipped over.  MySQL is good at giving you everything it possibly can 
even if there are 'issues'
with the tables/indexes.


Have you tried 'myisamchk -r table' to try to repair it?


b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

myisamchk -a + indexes + hidden...?

[Bill Adams]

It seems like myisamchk -a is hosing some
statistic in a MyISAM table that gets re-created
and stored permanently as once a query is run that
uses that index, it always runs well until
myisamchk -a is run again even between restarts of
mysqld.  It also seems that key_buffer_size has no
effect on the results.

Can someone explain this to me?

There are a a bunch of tables merged into three
main tables.  The query does a two column join
between the tables, e.g.: a.1=b.1 AND a.2=b.2 AND
b.1=c.1 AND b.3=c.3

--Bill


[root@host /usr/local/mysql-4.0/var]#
../bin/mysqladmin -uroot -p version
../bin/mysqladmin  Ver 8.22 Distrib 4.0.0-alpha,
for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB &
TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY.
This is free software,
and you are welcome to modify and redistribute it
under the GPL license

Server version  4.0.0-alpha-log
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /tmp/mysql-4.0.sock
Uptime: 7 min 2 sec

Threads: 3  Questions: 103  Slow queries: 0
Opens: 12  Flush tables: 46  Open tables: 3
Queries per second avg: 0.244
[root@host /usr/local/mysql-4.0/var]# uname -a
Linux host.tqs.com 2.2.19 #6 SMP Wed Jul 11
10:55:03 PDT 2001 i686 unknown
[root@host /usr/local/mysql-4.0/var]# vmstat
   procs  memory
swap  io system cpu
 r  b  w   swpd   free   buff  cache  si  so
bibo   incs  us  sy  id
 2  1  0  4   2612  40752 1450688   0   0
4 33 6  10   2   6
[root@host /usr/local/mysql-4.0/var]# ldd
../libexec/mysqld
librt.so.1 => /lib/librt.so.1 (0x2aac8000)

libdl.so.2 => /lib/libdl.so.2 (0x2aacc000)

libpthread.so.0 => /lib/libpthread.so.0
(0x2aad)
libz.so.1 => /usr/lib/libz.so.1
(0x2aae3000)
libcrypt.so.1 => /lib/libcrypt.so.1
(0x2aaf3000)
libnsl.so.1 => /lib/libnsl.so.1
(0x2ab2)
libstdc++-libc6.1-1.so.2 =>
/usr/lib/libstdc++-libc6.1-1.so.2 (0x2ab36000)
libm.so.6 => /lib/libm.so.6 (0x2ab78000)
libc.so.6 => /lib/libc.so.6 (0x2ab95000)
/lib/ld-linux.so.2 => /lib/ld-linux.so.2
(0x2aaab000)
[root@host /usr/local/mysql-4.0/var]#


myisamchk -a (on all tables)
set-variable= key_buffer=32M
Time to start getting results: 127 seconds.
Total Time: 146 seconds (18251 rows, 125 rows/sec
overall)

Run the query again:
Time to start getting results: 11 seconds.
Total Time: 30 seconds (18251 rows, 608 rows/sec)

shutdown mysql
set-variable = key_buffer=1M
start mysql
myisamchk -a (on all tables)
Time to start getting results: 121 seconds.
Total Time: 141 seconds (18251 rows, 129 rows/sec)

Second Run:

Run the query again:
Time to start getting results: 10 seconds.
Total Time: 29 seconds (18251 rows, 629 rows/sec)


Shutdown and restart MySQL.
Note: key_buffer still at 1M
Time to start getting results: 12 seconds.
Total Time: 31 seconds (18251 rows, 588 rows/sec)

Shutdown MySQL
Set key buffer size to 0
start mysql
Time to start getting results: 10 seconds.
Total Time: 29 seconds (18251 rows, 629 rows/sec)

myisamchk -a
Time to start getting results: 145 seconds.
Total Time: 164 seconds (18251 rows, 111 rows/sec)



--
Bill Adams
TriQuint Semiconductor






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: creating a Unix socket

[Bill Adams]

Erik Price wrote:

> Hello,
>
> Despite hours plumbing the MySQL documentation and Paul DuBois' book
> "MySQL" (New Riders), I can't figure out what exactly creates the Unix
> socket for local connections.  For some reason no socket was created
> during my setup, and I'm not sure how to go about making one.
>
> I compiled 3.23.44 from source on Darwin 1.4.1, using these options:
>
> Bash 2.05 $ ./configure --prefix=/usr/local/mysql
> --with-unix-socket-path=/usr/local/mysql/run/mysql_socket
> --with-mysqld-user=mysql --with-comment --with-debug
>
> I am able to run ' /usr/local/mysql/bin/safe_mysqld --with-user=mysql &
> '  with no problem.  However, I cannot get any of the client programs
> to communicate with the MySQL server.  I believe that my Unix socket
> (for local connections) is not set up properly, since mysqld runs fine.
>
> I believe that I made a mistake in not having a /usr/local/mysql/run
> directory set up at setup time.  Instead, I created the "run" directory
> after running "mysql_install_db" but before running "safe_mysqld".  for
> some reason I assumed that the socket would be created dynamically when
> I started the mysql daemon.

It does.

Try chown mysql:mysql /usr/local/mysql/run

Then restart mysqld.

b.


>
>
> Is there a script or program that I can use to create the socket?  Any
> advice would be greatly appreciated, I'd like to avoid recompiling the
> whole distribution if possible.  (If that is not possible, is it simply
> a matter of not having the /usr/local/mysql/run directory set up at
> compile time?)
>
> Thank you,
>
> Erik
>
> =
> -- Is this where you really wanted to go today ? --
>
> http://www.redhat.com/about/opinions/xp.html
>
> __
> Do You Yahoo!?
> Find a job, post your resume.
> http://careers.yahoo.com
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

--
Bill Adams
TriQuint Semiconductor




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Optimizing by drop then create an index

[Bill Adams]

The MySQL manual
http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#MySQL_indexes

states "All MySQL indexes (PRIMARY, UNIQUE, and
INDEX) are stored in B-trees. Strings are
automatically prefix- and end-space compressed.".

Other RDBMSes out there state in the their
respective manuals that if your table size (number
of records) changes by more than N% (usually 5% i
think) that it is a good idea to drop and
re-create any indexes to help re-balance the
potentially unbalanced B-tree.  Is this true with
MySQL too?  Does 'myisamchk -S' take care of that?



Also: Is this covered in the manual and I am just
missing it?  If not, can it be added?

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Sub Count -- Correction

[Bill Adams]

In the select statement, MySQL returns 1 for true so:


SELECT LOCATION_T.ADDRESS,
  LOCATION_T.CITY,
 COUNT(DISTINCT HARDWARE_T.IP),
 COUNT(DISTINCT HARDWARE_T.SLOT),
 COUNT(DISTINCT HARDWARE_T.PORT),
-
 SUM( HARDWARE_T.PORT_STATUS = 'up' ) AS up_cnt,
 SUM( HARDWARE_T.PORT_STATUS = 'down' ) as dn_cnt,

FROM LOCATION_T, HARDWARE_T
WHERE LOCATION_T.IP = HARDWARE_T.IP
GROUP BY LOCATION_T.ADDRESS, LOCATION_T.CITY


b.


Bradley wrote:

> Sorry, for some reason, the select statement was cut off. Here is the
> complete statement (below):
>
> Thanks,
> Brad
>
> I'm trying to write a select statement that produces a SUB COUNT of column
> PORT_STATUS where (A) PORT_STATUS = 'up' and (B) PORT_STATUS = 'down'. I'd
> like to do this in one statment with GROUP by LOCATION_T.ADDRESS,
> LOCATION_T.CITY  without altering the outer select. This sort of thing is
> simple to do with PL/SQL. However, this is a mysql database with select
> only. Is it somehow possible to do a sub select into a variable i.e. -->
> SELECT COUNT(DISTINCT COLUMN) INTO VARIABLE WHERE COLUMN = 'up'  ?
>
> SELECT LOCATION_T.ADDRESS
> , LOCATION_T.CITY
> , COUNT(DISTINCT HARDWARE_T.IP)  -- COUNT NUMBER OF ROUTERS AT EACH LOCATION
> , COUNT(DISTINCT HARDWARE_T.SLOT)  -- COUNT TOTAL # OF SLOTS AT EACH
> LOCATION
> , COUNT(DISTINCT HARDWARE_T.PORT)  -- COUNT TOTAL # OF PORTS AT EACH
> LOCATION
> , COUNT(DISTINCT HARDWARE_T.PORT_STATUS)  -- ??  SEPARATE COUNT WHERE
> HARDWARE_T.PORT_STATUS = 'up' FOR EACH LOCATION  ??
> , COUNT(DISTINCT HARDWARE_T.PORT_STATUS)  -- ??  SEPARATE COUNT WHERE
> HARDWARE_T.PORT_STATUS = 'down' FOR EACH LOCATION  ??
> FROM LOCATION_T, HARDWARE_T
> WHERE LOCATION_T.IP = HARDWARE_T.IP
> GROUP BY LOCATION_T.ADDRESS, LOCATION_T.CITY
>
> Output i.e:
>
> ADDRESS  ||  CITY  ||  NUMBER OF ROUTERS  ||  NUMBER OF SLOTS  ||  NUMBER OF
> PORTS  ||  NUMBER OF PORTS UP  ||  NUMBER OF PORTS DOWN
>
> 32 Street  ||  New York  ||  8  ||  90  ||  300  ||  150  ||  150
> 52 Street  ||  New York  ||  12  ||  120  ||  400  ||  200  ||  200
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

--
Bill Adams
TriQuint Semiconductor




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Mysqladmin help please help

[Bill Adams]

"Wells, Kenneth L" wrote:

> Please help
>
> I'm at the end of my rope...
>
> I just ran a script to create databases in my SQl server, it runs fine
>
> When it completes it says remember to set a password for the mysql root
> user!
>
> I entered this?
>
> /usr/local/mysql/bin/mysqladmin -u root -p password password  (I want
> password to be the password)
> It returns Enter password: I entered password again
>
> Is this the password it wants???

You just set the password to your root account to 'password'.

This is a common mistake: There is no space between the -p and the password.

Right after you have installed MySQL, there is no password so you run:
mysqladmin -u root password your?new!password

Then to connect to a server:
mysql -u root -pyour?new!password db

Note: No space after -p.

The manual has info if you need  help resetting the password.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: how to move db

[Bill Adams]

[EMAIL PROTECTED] wrote:

> I'm switching over to a bigger better faster server. My old server is running
> version 3.23.22-6 mysql on RH and I need to move all the db's to my new box
> using 3.23.41 installed on RH7.2. So do I just simply move all of the db
> directories from /var/lib/mysql from one box to the next or are there other
> files that I need to copy as well.

Just move all of the files, watch those permissions.

e.g.:

oldbox:
mysqladmin shutdown


newbox:
adduser mysql ...bla bla bla
mysqladmin shutdown (if it is running)
cd /var/lib
mv mysql mysql.orig
cp -Rva /net/oldbox/var/lib/mysql .
safe_mysqld &

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Losing data

[Bill Adams]

(Sorry for the double email Simon, forgot to
switch the email to text...)

Simon Windsor wrote:

> Hi
>
> The OS is Redhat 7.1 on a dual processor Pentium box, running MySQL 3.23.36,
> the standard RedHat version.
>
> The machine is running two databases, one is a full archive while the other
> ones holds current data. The same five records are unavailable using SQL in
> the two databases, but using mysqldump I can see the records.
>
> Obviously the data file is OK, but the index records are corrupt.
>
> What can I do ?
>
> - Export the databases, drop originals and reload.
> - Drop indexes and rebuild ?
> - Repair files ? Which ones, data or index or both ?
>
> What is advisable and will involve the least work ?

First answer these three+ questions:

1) You are /not/ using merge tables, right?  And
what table-type are you
using? InnoDB? MyISAM? ISAM? BDB?

2) Do your records show up if you do something
like:
echo "SELECT * FROM table" |mysql db |grep "the
text you seek"

3) What is the sql you are using to find the
data?  Is it on a text field with
a 'like' clause?   Can you include at least the
pertinent part of the record
and SQL and the column definition as reported by
"SHOW TABLE table"?



- Export the databases, drop originals and reload.

You can use mysqldump and then mysql to rebuild
the tables.


- Drop indexes and rebuild ?

You could do that with alter table drop index...


- Repair files ? Which ones, data or index or both
?

myisamchk only runs on the index file (.MYI) and
does things to the data file
when the right options are given. you can try
myisamchk -r
/path/to/mysqlvar/db/table.MYI to repair the
table.  There are other options.


If "myisamchk table.MYI" reports your table as
corrupt, I would try "myisamchk
-r  table.MYI".  If that does not seem to repair
it you can "mysqldump -opt
db >/tmp/table.sql; mysql db http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Losing data

[Bill Adams]

Simon Windsor wrote:

> Hi
>
> The OS is Redhat 7.1 on a dual processor Pentium box, running MySQL 3.23.36,
> the standard RedHat version.
>
> The machine is running two databases, one is a full archive while the other
> ones holds current data. The same five records are unavailable using SQL in
> the two databases, but using mysqldump I can see the records.
>
> Obviously the data file is OK, but the index records are corrupt.
>
> What can I do ?
>
> - Export the databases, drop originals and reload.
> - Drop indexes and rebuild ?
> - Repair files ? Which ones, data or index or both ?
>
> What is advisable and will involve the least work ?

First answer these three+ questions:

1) You are /not/ using merge tables, right?  And what table-type are you
using? InnoDB? MyISAM? ISAM? BDB?

2) Do your records show up if you do something like:
echo "SELECT * FROM table" |mysql db |grep "the text you seek"

3) What is the sql you are using to find the data?  Is it on a text field with
a 'like' clause?   Can you include at least the pertinent part of the record
and SQL and the column definition as reported by "SHOW TABLE table"?



- Export the databases, drop originals and reload.

You can use mysqldump and then mysql to rebuild the tables.


- Drop indexes and rebuild ?

You could do that with alter table drop index...


- Repair files ? Which ones, data or index or both ?

myisamchk only runs on the index file (.MYI) and does things to the data file
when the right options are given. you can try myisamchk -r
/path/to/mysqlvar/db/table.MYI to repair the table.  There are other options.


If "myisamchk table.MYI" reports your table as corrupt, I would try "myisamchk
-r  table.MYI".  If that does not seem to repair it you can "mysqldump -opt
db >/tmp/table.sql; mysql db http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Can't stop mysql / possible bug?

[Bill Adams]

[EMAIL PROTECTED] wrote:

> I changed the script and now all is well. However I have two concerns:
> 1) Paranoid about the password being in this script. Is there a way around this.

chown root:root /etc/rc.d/init.d/mysqld
chmod go-rx /etc/rc.d/init.d/mysqld


> 2) Since I had to change the script to make it work, Is there a bug in 3.23.41??

It is a bug in the script NOT in MySQL its self. (Note that this script AFAIK is
not a standard script that comes with MySQL, your distribution probably added it.)
Killing safe_mysqld or the mysqld processes is dangerous and wrong.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: primary key based on unique value for two columns

[Bill Adams]

Brendin wrote:

> I would like to have a table that has a primary key defined on a
> combination of two columns in the table.  In other words a unique key
> based upon the values in two columns.
>
> I don't think I am able to do this in mysql.  I think you can only have
> a primary key on one column and not on a combination of columns.  If I
> am wrong please correct me.

At least in 3.23.x (x>?) and higher you CAN have a primary key on multiple
columns.


> I am looking for work arounds.  I have thought of one.  That would be to
> concatenate the columns and use a field terminator such as a - to
> separate the values or (columns) in the single column.  Then you could
> use string functions to parse the columns.  Ex:

[snip]
There is a maximum key lenth so if you have two char(255) columns you might
need to do something like:

ALTER TABLE table ADD PRIMARY KEY( char_col1(100), char_col2(100));

However, this also means that the combination of the first 100 chars from
each column must be unique.

b.




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Can't stop mysql

[Bill Adams]

[EMAIL PROTECTED] wrote:

> The only reference to mysqladmin is for the reload at the end. I think this
> whole thing started after doing the mysql_install_db and then creating the root
> password - but it may be a coincidence.
>
> Any and all help is welcome as to why /etc/init.d/mysqld stop fails.
>
> The /etc/init.d/mysql is as follows:

I would update the script:


>
> stop(){

 /path/to/mysqladmin -uroot -pyour?root.password shutdown > /dev/null
2>&1
ret=$?

> if [ $ret -eq 0 ]; then
> action $"Stopping $prog: " /bin/true
> else
> action $"Stopping $prog: " /bin/false
> fi
> [ $ret -eq 0 ] && rm -f /var/lock/subsys/mysqld
> [ $ret -eq 0 ] && rm -f /var/lib/mysql/mysql.sock
> return $ret
> }
>
> restart(){
> stop
> start
> }
>
> condrestart(){
> [ -e /var/lock/subsys/mysqld ] && restart || :
> }
>
> reload(){

   [ -e /var/lock/subsys/mysqld ] && mysqladmin -uroot -pyour?root.password reload


> }
>

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Can't stop mysql

[Bill Adams]

[EMAIL PROTECTED] wrote:

> I'll agree with you but - when I shutdown my linux box or restart it, it tries
> to stop the mysql server and it can't. The command /etc/init.d/mysqld stop
> should work and it isn't. The question is why can't I stop the mysql server with
> this command. As a matter of fact, using /etc/init.d/mysqld stop restart  fails
> when it tries the stop. There has to be something convoluted in one of the
> script files. Besides that, I'm not the only one with this issue.
> thanks

This is really a distribution issue then.  But I would look inside the
init.d/mysqld script and see if it is calling mysqladmin or not.  If it is: Does it
have the full path to mysqladmin?  Did you set the root password in MySQL and now
need to specify it in the file, e.g.: "mysqladmin -pthe.root?password shutdown"?

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Losing data

[Bill Adams]

Simon Windsor wrote:

> Hi
>
> I appear to have lost several records, but on doing mysqldump  the
> records are there.
>
> I have tried optimize|repair and the data hasn't re-appeared.
>
> Any ideas ?

Not with this level of information.

(Try including some sql, what you are trying to match, etc.. And post to the
list!)


b.
mysql



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Fulltext search variable

[Bill Adams]

Craig Issod wrote:

> >Craig Issod wrote:
> >
> >>  Using 3.23.32 on FreeBsd 3.2
> >>
> >>  Have gotten a sample fulltext search going, but cannot figure out how
> >>  to shorten the word length to 2 o3 3, from the default 4.
> >>
> >  > Yes, I've read the docs, and tried the following:
> >>  Setting variable using mysqld on command line...it won't take it.
> >>
> >>  Looking at the variables - SHOW VARIABLES - it's not in there.
> >>  ft_min_word_length does not seem to even exist. Can I simply create
> >  > it somehow?
> >
> >The file needs to be named 'my.cnf'.  Please Read:
> 
>>http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html#Option_files
> >
>
> Will adding variable line to one of the sample .cnf files, moving and
> renaming that file to my data directory and then restarting the
> server work?

I think so.  You want to add it as "set-variable = ft_min_word_len=3" without the 
quotes AND (if you check
out the docs on said variable) you /must/ rebuild the index before it will take 
effect.  Restarting the
server will have it re-read the config file, then you want to rebuild the index.

b.
mysql



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Can't stop mysql

[Bill Adams]

[EMAIL PROTECTED] wrote:

> Howdy,
> I'm running version 3.23.41 on RH7.2. For the life of me I can't figure out why
> I can't stop mysqld. Linuxconf was where I first noticed this where mysqld would
> not respond to the stop request. I then tried to reboot the box and watched the
> shutdown process and noticed that mysqld failed the stop request. The only way
> to 'stop' mysqld is to use the 'killall mysqld' command. Any ideas on what could
> be wrong. If there is scripting changes, please give me very specific

The program safe_mysqld will restart mysqld everytime it crashes or is killed.  You
really should stop mysql with 'mysqladmin shutdown'.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Fulltext search variable

[Bill Adams]

Craig Issod wrote:

> Using 3.23.32 on FreeBsd 3.2
>
> Have gotten a sample fulltext search going, but cannot figure out how
> to shorten the word length to 2 o3 3, from the default 4.
>
> Yes, I've read the docs, and tried the following:
> Setting variable using mysqld on command line...it won't take it.
>
> Looking at the variables - SHOW VARIABLES - it's not in there.
> ft_min_word_length does not seem to even exist. Can I simply create
> it somehow?

Search:
http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html

for ft_min_word_len (not _length).


> Looking for the config files mentioned in the docs...there is no
> directory called myisam on my directory tree.
>
> looking for the .cnf files...I found:
> /usr/local/mysql/share/mysql/my-huge.cnf
> /usr/local/mysql/share/mysql/my-large.cnf
> /usr/local/mysql/share/mysql/my-medium.cnf
> /usr/local/mysql/share/mysql/my-small.cnf
> /usr/local/share/mysql/my-example.cnf
>
> Since I was not the original installer of mySQL on the system, I
> don't know which, if any, conf file is being used.

The file needs to be named 'my.cnf'.  Please Read:
http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html#Option_files

b.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Unique and case-insensitivity with indexes

[Bill Adams]

Fulko Hew wrote:

> I am using mySQL 3.22.4a-beta
> yes, I know its old :-(
>
> I have just stumbled across a problem with how it
> treats 'uniqueness' in table contents.
>
> I have a table with a column defined as:
>
>   create table test (name varchar(80) not null);
>   alter table test ADD UNIQUE (name), ADD INDEX (name);

If you want this to be case sensitive you need to add "BINARY" to any '*char'
columns:

  create table test (name varchar(80) BINARY not null);
  alter table test ADD UNIQUE (name), ADD INDEX (name);

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Faking MS Access on MySQL linux box

[Bill Adams]

mweb wrote:

> Thanks for the shameless plug, I'll look into your code. However,
> shouldn't something like what I want be possible in PHP with ODBC?

Yes, you can install UnixODBC or similar and use the odbc_* calls in PHP on
both os's.  I just do not know how reliable ODBC is on the *nix.

b.
mysql


>
>
> mweb
>
> On Fri, Nov 09, 2001 15:22:11 at 03:22:11PM -0800, Bill Adams wrote:
> > mweb wrote:
> >
> > > >mweb,
> > > > To make sure I understand you correctly, you want to create
> > > >and
> > > >test your PHP/MySQL database on your Linux/Apache box, then upload it
> > > >to
> > > >your ISP server that is running PHP/Access? I'm sorry but that's not
> > > >going
> > > >to work. You can't transparently switch a PHP application from MySQL
> > > >to
> > > >Access.
> > >
> > > Yes, you did understand me correctly.
> > >
> > > What I understand ( and could certainly be wrong) is that there are in
> > > PHP layers of abstraction/wrappers/APIs or whatever you call them,
> > > that allow you to do just that, i.e. to write code that is obviously
> > > not as efficient as one written specifically for one single database,
> > > but does work on more of them.
> > >
> > > As a matter of fact, after posting the first message, I bought the
> > > book PHP developers cookbook which has section 16.0 named more or less
> > > How to create a DB independent API with PHP, and that says "at the
> > > beginning of the script is included a file containing all the wrapper
> > > function."
> >
> > 
> > http://evilbill.org/php/DBI.php3
> > I don't have an ODBC module for use under NT/Access but I would love a
> > contribution.
> > 
> >
> >
> >
> > > That's exactly what I was hoping to do. THe site has ~4000
> > > accesses/month, and just some hundred records in the DB, so performance
> > > is not really an issue here, is it?
> >
> > IMHO, no.
> >
> > b.
> > mysql
> >
> >
> >
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
> --
> We need to focus on how to be productive, not just active.
> Scott McNealy, chairman, CEO, and cofounders, Sun Microsystems.

--
Bill Adams
TriQuint Semiconductor




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Faking MS Access on MySQL linux box

[Bill Adams]

mweb wrote:

> >mweb,
> > To make sure I understand you correctly, you want to create
> >and
> >test your PHP/MySQL database on your Linux/Apache box, then upload it
> >to
> >your ISP server that is running PHP/Access? I'm sorry but that's not
> >going
> >to work. You can't transparently switch a PHP application from MySQL
> >to
> >Access.
>
> Yes, you did understand me correctly.
>
> What I understand ( and could certainly be wrong) is that there are in
> PHP layers of abstraction/wrappers/APIs or whatever you call them,
> that allow you to do just that, i.e. to write code that is obviously
> not as efficient as one written specifically for one single database,
> but does work on more of them.
>
> As a matter of fact, after posting the first message, I bought the
> book PHP developers cookbook which has section 16.0 named more or less
> How to create a DB independent API with PHP, and that says "at the
> beginning of the script is included a file containing all the wrapper
> function."


http://evilbill.org/php/DBI.php3
I don't have an ODBC module for use under NT/Access but I would love a
contribution.




> That's exactly what I was hoping to do. THe site has ~4000
> accesses/month, and just some hundred records in the DB, so performance
> is not really an issue here, is it?

IMHO, no.

b.
mysql



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Newbie MySQL Install Question

[Bill Adams]

Dan Tappin wrote:

> Ok that worked... but now my SQL utility program returns a 'Host
> 192.168.0.74 (my local IP) is not allowed to connect to this MySQL server'
>
> I assume that I need to get into the grant tables.  Is this just via
> mysqladmin -u root -p???

To get into the grant tables, as it were, you need to get into the mysql db:
'mysql -u root -pyour?new.password mysql'. Note that there is NO space between
the '-p' and the password.

b.


>
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bill Adams
> > Sent: Friday, November 09, 2001 2:26 PM
> > To: [EMAIL PROTECTED]
> > Cc: [EMAIL PROTECTED]
> > Subject: Re: Newbie MySQL Install Question
> >
> >
> > Dan Tappin wrote:
> >
> > > I tried that but it came back with a 'mysqld is running already' error.
> >
> > Actually your command is wrong. If you never set the password
> > before it should
> > be (without the -p):
> > mysqladmin -u root password your?new.password
> >
> > note that 'password' IS the command.
> >
> > > Side Question:  How can I allow a SSH connection from a remote
> > machine?  My
> > > linux box is in a locked server room and I would like to
> > administer it from
> > > my office via a terminal.  I tried via SSH from my MacOS X
> > laptop but I get
> > > a  secure connection refused message.
> >
> > 1) Make sure you have a recent openssh that fixes the host access bug.
> >
> > 2) Check another list as I don't actually know off the top of my head.
> > Basically you want to install openssh and run
> > /etc/rc.d/init.d/sshd start (on
> > a RH system).  And read the man pages about how to limit access.
> > And please
> > direct further questions about ssh elsewhere. Thanks.
> >
> > b.
> >

--
Bill Adams
TriQuint Semiconductor




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Newbie MySQL Install Question

[Bill Adams]

Dan Tappin wrote:

> I tried that but it came back with a 'mysqld is running already' error.

Actually your command is wrong. If you never set the password before it should
be (without the -p):
mysqladmin -u root password your?new.password

note that 'password' IS the command.




> Side Question:  How can I allow a SSH connection from a remote machine?  My
> linux box is in a locked server room and I would like to administer it from
> my office via a terminal.  I tried via SSH from my MacOS X laptop but I get
> a  secure connection refused message.

1) Make sure you have a recent openssh that fixes the host access bug.

2) Check another list as I don't actually know off the top of my head.
Basically you want to install openssh and run /etc/rc.d/init.d/sshd start (on
a RH system).  And read the man pages about how to limit access.  And please
direct further questions about ssh elsewhere. Thanks.


b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Newbie MySQL Install Question

[Bill Adams]

Dan Tappin wrote:

> I have some experience with MySQL on MacOS X but that was with a pretty GUI
> installer.
>
> I am now trying to get MySQL installed on Redhat 7.0.  I download the client
> and server versions and followed the install procedures.
>
> The problem I am having is with mysqladmin.  The docs state that you need to
> set the root password with 'mysqladmin -u root -p password'.  I enter this
> command, I am then prompted for the new password and then I get a 'could not
> connect to local host' error.
>
> I think I am missing something here.  To make matters worse my Linux
> experience is pretty limited as well.

You need to start mysql first with either safe_mysqld (3.23.x) or
mysqld_safe (4.0).

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: show processlist

[Bill Adams]

Stuart Scamman wrote:

> How do I show processlist from a specific computer, not everything ?
> Thanks.

mysqladmin processlist |grep 'the.host.you.care.about'




--
Bill Adams
TriQuint Semiconductor




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Large MySQL setup

[Bill Adams]

Aaron Williams wrote:

> The tables run upwards of around 13-15 gigs each. The nature of the
> data forces me to search the entire contents (looking for unique
> values) around 3000 times a day. The server is running Solaris 2.8,

Do you have an index on the columns you are querying for unique values?  That
will help.

If you do not need live and instant unique lists, you could (once every 30
minutes say) populate a special table with the unique values.  With locking,
etc. of course.



>
> with MySQL 3.9.43 compiled in 64bit mode. All tables are currently
> MyISAM tables. (Will probably be going with InnoDB, but large file
> support was -just- released).

InnoDB might help because it does have row-level locking.


> isn't disk bound. Running multiple intenses of the queries shows the
> same results, 75% idle, meaning only one CPU is being used.

AFAIK, 3.23.x only supports one CPU per thread/select.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysql corruption tables in production environment (Urgent)

[Bill Adams]

Rui Barreiros wrote:

> Hi,
>
> We have several mysql server in production evironment, and most of them
> when they have millions of rows, they all get MyIsam tables corrupted.
>
> One of the servers has ext2 filesystem with scsi harddrives, after the
> myisamchk -r it repairs the table, but after a few inserts it will
> corrupt again. the file is not bigger than 2gb.

When I occasionally get tables like this:

o Use mysqldump --opt db table >/tmp/table.sql; mysql db http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: String composite key vs auto_increment

[Bill Adams]

Christian Stromberger wrote:

> Disclaimer: SQL/database newbie here.
>
> Let's say I have a table of authors with columns for last name and first
> name.  Is there any general guideline re using a separate integer for the
> primary key as opposed to a combination of the last and first names as the
> key?  I ask because by using the names, this would prevent duplicate entries
> into the db, right?  Whereas using a separate integer key would not prevent
> this--you'd have to search for the author to see if it was already in the db
> before inserting to avoid dupes, right?

Yes. Yes.  On the second point, you would want to make a [non-unique] key on
the first and last name columns.



> Assume I am not concerned about there being two different "Joe Smith"
> authors that are different people.  I only want to associate an author name
> with a book.

The column you seek is an integer column with the auto_increment flag, e.g.:

CREATE TABLE authors (
  last_name char(64) DEFAULT '' NOT NULL,
  first_name char(64) DEFAULT '' NOT NULL,
  author_idx integer NOT NULL AUTO_INCREMENT,
  PRIMARY KEY( author_idx ),
  KEY( last_name(20), first_name(20)),
  KEY( first_name(20))
);

The key on only first_name is there in case you query on just the first_name.

The manual will tell you lots more about auto_increment and how keys are used.

b.




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: HOW do I return the results of a count to a variable

[Bill Adams]

Paul DuBois wrote:

> [snip]
>
> >Of all the methods suggested I like the look of the one above and will
> >try that one first.
> >
> >$count = $sth->fetchrow_array();
> >
> >$rows = $sth->rows()
>
> Note that use of rows() to get the row count for a SELECT is deprecated
> in the DBI docs, which say that if you want to know the number of rows
> in a result set, fetch and count them.  (The reason is that rows()
> just doesn't work at all for some database engines.  On the other hand,
> it appears to work just fine for MySQL...)

Yes, MySQL will return the number of rows you /will/ get en total.  Informix,
Oracle, etc.. return the number of rows you have retrieved so far.

This is a database implementation issue and not a DBI/DBD issue. (PHP follows the
same results.)  Therefore if you want your code to work across dbs, you should
not rely on the value of ->rows().

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: key question

[Bill Adams]

Federico Schwindt wrote:

> hi,
>
>   i'm not sure if this belongs here, but i cannot seem to find the
> answer anywhere else.
>   first, what's the difference between:
>
>   PRIMARY KEY (key1, key2)
>   PRIMARY KEY (key1), KEY (key2)

The first line creates a single, unique key on the columns key1 and key2.

The second line creates two keys, the first a unique key on key1 and the
second on just key2 that allows duplicates.



>   second, let's suppose the following table:
>
>   owner_id int(11),
>   customer_id int(11),
>   customer_info varchar(100)
>
>   and i want to search either by owner_id and customer_id.
>   can i do this w/o creating the indexes by hand? or do i have to
> create'em explicity and specify which one i'm gonna use before
> performing a query?

You never need keys to query a table.  It just makes the query much faster.
 For a query like "WHERE owner_id=X AND custoerm_id=Y" you would want a
composite index on both columns, e.g. KEY( owner_id, customer_id ).  This
key would also cover a query that had just "WHERE owner_id=X".  If, then,
you wanted to query on just the customer_id, you would want to add another
key on just customer_id.

E.g.:
CREATE TABLE tbl (
  owner_id int(11),
  customer_id int(11),
  customer_info varchar(100)
  key( owner_id, customer_id ),
  key( customer_id )
);

Of course, study the documentation for further explanation info.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Left join w/o on or using join_condition

[Bill Adams]

Rick Emery wrote:

> Try going with simply:
> SELECT  T1.*, T2.* FROM tbl1 T1, tbl2 T2
> WHERET1.a=1AND T1.b=2 AND T2.a=2 AND T2.b=T1.b;

Well, if I resort to that then the documentation is wrong.

Here is what I am really trying to do.  Say you have a table and data:

DROP TABLE IF EXISTS tbl2;
CREATE TABLE tbl1 ( a int, c char, val int );

INSERT INTO tbl1 VALUES
 ( 1, 'R', 10 ),
 ( 2, 'R', 11 ),
 ( 3, 'R', 12 ),
 ( 1, 'T', 20 ),
 ( 3, 'T', 21 )
;


Here is a ugly, self-join select that gives me the output that I want:

SELECT T1.*, T2.*
FROM tbl1 T1 LEFT JOIN tbl1 T2 ON( T1.a=T2.a
AND ( T1.c<>T2.c OR ( T1.c='R' AND T2.c='T' )))
WHERE T1.c='R'
;

+--+--+--+--+--+--+
| a| c| val  | a| c| val  |
+--+--+--+--+--+--+
|1 | R|   10 |1 | T|   20 |
|2 | R|   11 | NULL | NULL | NULL |
|3 | R|   12 |3 | T|   21 |
+--+--+--+--+--+--+
3 rows in set (0.00 sec)


What I really want is a simpler select like informix allows, e.g.:
SELECT T1.*, T2 FROM tbl1 T1, OUTER tbl1 T2
WHERE T1.a=T2.a AND T1.c='R' AND T2.c='T';

My real query is much worse than this.

Any suggestions?

--Bill


>
>
> -Original Message-
> From: Bill Adams [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, October 31, 2001 12:21 PM
> To: Mysql List
> Subject: Left join w/o on or using join_condition
>
> My mind is not working with me this morning...
>
> According to the manual about join syntax:
> http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#JOI
> N
>
> One should be able to do a LEFT JOIN without a
> join_condition, eg. an "ON" or a "USING":
> > table_reference LEFT [OUTER] JOIN
> table_reference
>
> However a query like:
>
> SELECT  T1.*, T2.* FROM tbl1 T1 LEFT JOIN tbl2 T2
> WHERET1.a=1AND T1.b=2AND T2.a=2 AND T2.b=T1.b;
>
> Gives me a  "ERROR 1064: You have an error in your
> SQL syntax near 'WHERE..."
>
> What am I doing wrong?  You used to be able to do
> that.  I know the conditional can be moved into a
> ON( ... ) but I do not want to do that for
> compatibility with other DBs.
>
> MySQL 3.23.41
>
> --Bill
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

--
Bill Adams
TriQuint Semiconductor




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Alternative for UNION

[Bill Adams]

Harpreet wrote:

> I had a view on sqlserver which i am trying to convert to work with mysql.
> It uses a union which i believe does not work with mysql. What is another
> alternative for union.
>
> Sql with union:
>
> select a.asset_id,a.material_id,b.material_id as parent_material_id from
> lib_asset_tbl a inner join lib_asset_tbl b on a.parent_id=b.asset_id
> union
> select asset_id,material_id,'' from lib_asset_tbl where virtual_flag='N'

MySQL 4.0 supports unions.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Left join w/o on or using join_condition

[Bill Adams]

My mind is not working with me this morning...

According to the manual about join syntax:
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#JOIN

One should be able to do a LEFT JOIN without a
join_condition, eg. an "ON" or a "USING":
> table_reference LEFT [OUTER] JOIN
table_reference

However a query like:


SELECT  T1.*, T2.* FROM tbl1 T1 LEFT JOIN tbl2 T2
WHERET1.a=1AND T1.b=2AND T2.a=2 AND T2.b=T1.b;


Gives me a  "ERROR 1064: You have an error in your
SQL syntax near 'WHERE..."

What am I doing wrong?  You used to be able to do
that.  I know the conditional can be moved into a
ON( ... ) but I do not want to do that for
compatibility with other DBs.

MySQL 3.23.41

--Bill



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: PHP/MySQL Problem

[Bill Adams]

rc wrote:

> Mysql_Insert_ID($dbhandle->connection);
>
> gets the last id of the last insert statement -
> if you do any db activity
> after the insert, this won't work.

In other words:

> $res = $dbhandle->query("select
last_insert_id()");
> $dbhandle->query("UNLOCK TABLES");
> $result = $res->fetchRow();

Needs to be:

> $res = $dbhandle->query("select
last_insert_id()");
> $result = $res->fetchRow();
> $dbhandle->query("UNLOCK TABLES");


Also, if this were production code you would want
to check the value of $CustomerID, e.g. if someone
choose or passed a CustomerID of ");DELETE * FROM
Orders; "(1 they could delete your entire table.

b.


>
>
> On Tue, 30 Oct 2001, Matthew Walker wrote:
>
> > Does anyone see anything wrong with the
> following code/query syntax? I'm
> > using the PEAR DB class for PHP. When this
> runs, it correctly inserts
> > the row to the table, but is failing to
> retrieve the last_insert_id().
> >
> > function StartOrder()
> > {
> >   global $dbhandle;
> >   global $OrderID;
> >   global $CustomerID;
> >
> >   if (!($OrderID)) {
> > $query = "INSERT INTO Orders (CustomerID)
> VALUES(";
> > if ($CustomerID) {
> >   $query .= "\"" . $CustomerID . "\"";
> > } else {
> >   $query .= "\"" . "\"";
> > }
> > $query .= ")";
> > $dbhandle->query("LOCK TABLES Orders
> WRITE");
> > $dbhandle->query($query);
> > $res = $dbhandle->query("select
> last_insert_id()");
> > $dbhandle->query("UNLOCK TABLES");
> > $result = $res->fetchRow();
> > return $OrderID = $result[0];
> >   } else {
> > return $OrderID;
> >   }
> > } // end func
> >
> > --
> > Matthew Walker
> > Ecommerce Project Manager
> > Mountain Top Herbs
> >
> >
> >
> > ---
> >
> >
> > --
> > Matthew Walker
> > Ecommerce Project Manager
> > Mountain Top Herbs
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system
> (http://www.grisoft.com).
> > Version: 6.0.286 / Virus Database: 152 -
> Release Date: 10/9/2001
> >
> >
> >
> >
> -
>
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the
> manual)
> >http://lists.mysql.com/   (the list
> archive)
> >
> > To request this thread, e-mail
> <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
>
> > Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
> >
>
> -
> ---
>
> Before posting, please check:
>http://www.mysql.com/manual.php   (the
> manual)
>http://lists.mysql.com/   (the list
> archive)
>
> To request this thread, e-mail
> <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php

--
Bill Adams
TriQuint Semiconductor






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: busy site w/ high cpu usage on mysql

[Bill Adams]

alexus wrote:

> Hello
>
> I have a very busy site (www site) w/ a lot of hits
>
> my site is very heavily integrated w/ mysql
>
> and when i do top it shows me like over 80% of cpu
>
> even though i have dual p3 850mhz w/ 1.5 gig of ram
>
> any ideas what to do about it so it'll drop down?

Have you inspected your process list e.g. mysqladmin processlist?

Do you log slow queries?

Have you explored indexes that you can add for any slow/common queries? E.g.
use "EXPLAIN SELECT" on common queries.

Have you done a 'myisamchk -a'?

If you are suffering from table locking issues, perhaps you should convert
to Innodb.


b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Optimizing query (2nd attempt)

[Bill Adams]

David Wolf wrote:

> Maybe I'm missing something here--I don't know
> of a way to create an index
> on TWO tables at once? Also, when I do:

My bad.  You cant.

Since you are joining on the primary key, you want
to add an index like:
ALTER TABLE users ADD UNIQUE ( id, username(10));

Since you are using a left-join and a criteria on
a table that is left-joined, it can be difficult
to get a good index.

b.
(Sorry to spam you for a second time, David)



>
>
> EXPLAIN SELECT log.entity, log.action,
>   LEFT(users.username,10) AS username,
>   LEFT(boards.title,15) AS Board,
>   LEFT(topics.subject,22) as Subject,
>   log.postid, log.extraid,
>   LEFT(from_unixtime(log.logtime),19) AS time,
> log.ip
> FROM log LEFT JOIN users ON log.userid =
> users.id
>   LEFT JOIN boards ON log.boardid=boards.id
>   LEFT JOIN topics ON log.topicid = topics.id
> WHERE users.username="testuser";
>
> (users.username is indexed), I get the
> following:
>
> ++
> ---+---+-+-+-+---
>
> --++
> | table  | type   | possible_keys | key |
> key_len | ref | rows
> | Extra  |
> ++
> ---+---+-+-+-+---
>
> --++
> | log| ALL| NULL  | NULL|
> NULL | NULL|
> 1199187 ||
> | users  | eq_ref | PRIMARY   | PRIMARY
> |   4 | log.userId  |
> 1 | where used |
> | boards | eq_ref | PRIMARY   | PRIMARY
> |   4 | log.boardId |
> 1 ||
> | topics | eq_ref | PRIMARY   | PRIMARY
> |   4 | log.topicId |
> 1 ||
> ++--
> -+---+-+-+-+---
>
> --+----+
> 4 rows in set (0.00 sec)
>
> It's just simply not using the index on users..
> Did I miss something?
>
> David
>
> --
> -- Original Message -
> From: "Bill Adams" <[EMAIL PROTECTED]>
> To: "David Wolf" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Monday, October 29, 2001 9:21 AM
> Subject: Re: Optimizing query (2nd attempt)
>
> > David Wolf wrote:
> >
> > >Not quite fixed.. When I run the query
> without limiting by time, it still
> > >fails to use the userid key. i.e. if I only
> select where
> > >users.username="testuser", I'd expect that
> users.username to return the
> > >users.id=2, and to search using the indexed
> log.userid=2
> >
> >
> > MySQL can only use one index on a table at a
> time.  It also uses the
> columns in
> > the order in which they are defined.  ORDER
> MATTERS!
> >
> > The manual does not seem to cover this, but at
> least Informix will stop
> using
> > an index when an inequality is hit.  E.g.: if
> you have an index on (a, b,
> c )
> > and the query has WHERE a=5 AND b>2 AND c=10,
> the only part of the index
> that
> > will be used is (a, b).  (Monty & co, is this
> true with MySQL? Can you add
> > something to the manual either way?)
> >
> > So assuming this is true in your where clause:
>
> >
> > > WHERE log.logTime >
> UNIX_TIMESTAMP("2000-10-26 23:00:00")
> > >   AND users.username="testuser";
> >
> > If you have an index on ( logTime, username),
> since you have an inequality
> for
> > lotTime in the query, username will NOT be
> used.  However if you have the
> index
> > on (username, logTime)  --or even just the
> first 10 chars or so of
> username +
> > logTime-- then both username AND logTime will
> be used in the index.
> >
> > You may want to try this to see if it makes
> any difference.
> >
> > And, of course, run myisamchk -a on the tables
> after you build indexes.

--
Bill Adams
TriQuint Semiconductor






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Optimizing query (2nd attempt)

[Bill Adams]

; 27198
> > > | where used |
> > > | users  | eq_ref | PRIMARY   | PRIMARY |   4 | log.userId  |
> > 1
> > > ||
> > > | boards | eq_ref | PRIMARY   | PRIMARY |   4 | log.boardId |
> > 1
> > > ||
> > > | topics | eq_ref | PRIMARY   | PRIMARY |   4 | log.topicId |
> > 1
> > > ||
> > >
> >
> +++---+-+-+-+---
> > > ++
> > > 4 rows in set (0.00 sec)
> > >
> > > Big difference from 1.19million rows to 27198 rows... My question is
> this.
> > > How can I optimize the query with the left joins so that the optimizer
> > will
> > > first grab the userid from the username and then use the userid index on
> > log
> > > to return the results fast?
> > >
> > > Thanks in advance,
> > >
> > > David
> > >
> > >
> > >
> > >
> > > -
> > > Before posting, please check:
> > >http://www.mysql.com/manual.php   (the manual)
> > >    http://lists.mysql.com/   (the list archive)
> > >
> > > To request this thread, e-mail <[EMAIL PROTECTED]>
> > > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> > >
> > >
> >
> >
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

--
Bill Adams
TriQuint Semiconductor




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Generate random, unique value...

[Bill Adams]

Kyle Hayes wrote:

> On Friday 26 October 2001 10:00, Dana Holt wrote:
> > Can I automatically generate a random, unique, integer value in a certain
> > range when inserting data into a column using SQL?
> >
> > If so, how?
>
> Random is easy.  Just find a good RNG (random number generator) somewhere
> (there are many available on the 'net, search on Google).  Or, use the RAND
> function that comes with MySQL.  You can find RNGs that have extremely long
> periods.
>
> Unique is easy.  Just use an auto-increment field in MySQL.
>
> Random _and_ unique are not that simple.

You could put both a number from RAND and an auto-increment field to ensure
uniqueness when combining the two.

But of course crypographicly, that is not a good idea.

b.
mysql



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Autoincrement question

[Bill Adams]

Bill Adams wrote:

> Demirchyan Oganes-AOD098 wrote:
>
> > Hello everyone,
> >
> > I wanted to ask you 2 questions.
> >
> > 1. I have a table that has 2 columns, user_id,
> user_name.
> > User_id has been setup to a default value 1000
> and to AUTO_INCREMENT.
> >
> > When I insert a record, Insert Into User_Table
> (user_id,user_name) Values (Null,'Jon Doe');
> > I get  1  John Doe.  But I'm expecting
> something like 1000 John Doe. And when I do
> another insert, then it will be 1001.  Why this
> Default Value definition not making sure that I
> start with 1000?
>
> The documentation or FAQ found on mysql.com
> should answer how to get your auto_increment
> column to start at a value other than 1.

For those of you who asked me for a url, I was
hoping you would go to http://mysql.com/, click on
the big "Documentation" link at the top of the
page and try either or both of the the "Search
able, with user comments" or the "Dynamic FAQ"
links and do a search.

That is what I have just done.  The FAQ does not
have the answer to your question but searching the
documentation for 'auto_increment" returns a
number of results.  Looking through a few pages
manually, as I did, along with the browser's
search function reveals that the information is in
the page about alter table at the bottom.

I am trying to teach you to fish so you are fed
for life.  And I was trying to avoid spending the
effort searching for the information myself.  I
just happen to know it existed in the docs and was
trying to point you in the right direction.

b.







>
>
> b.
>
> --
> --
>
> Before posting, please check:
>http://www.mysql.com/manual.php   (the
> manual)
>http://lists.mysql.com/   (the list
> archive)
>
> To request this thread, e-mail
> <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php

--
Bill Adams
TriQuint Semiconductor






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Autoincrement question

[Bill Adams]

Demirchyan Oganes-AOD098 wrote:

> Hello everyone,
>
> I wanted to ask you 2 questions.
>
> 1. I have a table that has 2 columns, user_id, user_name.
> User_id has been setup to a default value 1000 and to AUTO_INCREMENT.
>
> When I insert a record, Insert Into User_Table (user_id,user_name) Values (Null,'Jon 
>Doe');
> I get  1  John Doe.  But I'm expecting something like 1000 John Doe. And when I do 
>another insert, then it will be 1001.  Why this Default Value definition not making 
>sure that I start with 1000?

The documentation or FAQ found on mysql.com should answer how to get your 
auto_increment column to start at a value other than 1.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: copying database from linux server to NT server

[Bill Adams]

[EMAIL PROTECTED] wrote:

> Hi. I've been reading for several hours on this one. I've tried everything
> I've read. I'm either doing something really stupid or I've missed the
> point completely.
>
> I have a db on a linux server. I used phpAdmin to create a dump file
> (pe.sql) and retrieved it on my local machine. I've tried what I thought
> was the simplest way through mysql monitor to build the db locally:
>
> mysql new_DB < pe.sql
>
> and
>
> mysql new_DB < 'pe.sql'
>
> but I get a syntax error.
>
> The dump file looks good with the CREATE TABLE statements and all the
> INSERT INTO  as comma delimited.
>
> Once I get the db, I can just copy it over to the server. But I can't get
> the db.

Did you remeber to create the database on the NT box?

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQL problem with Traffic and Updates

[Bill Adams]

jim barchuk wrote:

> Hi Ryan!
>
> > Today after my server got a real traffic hit for the first time since I
> > installed PhpAdsNew (a MySQL/PHP Ad software), MySQL was crashed. Here's how
> > it happened: watching the "top" load levels, I saw it slow creep up about to
> > 3, then rockets quickly to 30s, 40s, and on!! MySQL was at this time taking
> > up 100% of both of the processors in the server. There were about 40 or so
> > MySQL processes spawned at this point. Doing a 'mysqladmin processlist'
> > command on the server showed me a VERY LONG LIST of processes that were
> > open, a lot of which were PhpAdsNew UPDATE commands.
> >
> > The system it is on is a Dual-Athlon 1.2 GHz with 1 GB of memory.  I have
> > heard of systems that run at 450 MHz to be able to handle more ad views than
> > this system is taking.  I have the latest MySQL and PHP, all on Red Hat 7.1.
> > This is the second ad software that has done this, so I don't think the
> > problem is so much in the software, but something about my MySQL config or
> > setup.
>
> Obviously the system should handle the load.
>
> At http://www.mysql.com/doc/ do a search for multiple processors. There's
> a refence to http://www.mysql.com/Downloads/Patches/linux-fork.patch that
> might help.

And don't forget to make sure you have good/needed indexes.
(e.g See the manual entry on "EXPLAIN", run myisamchk -a, etc..).

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Request for information (MS Access & MySQL)

[Bill Adams]

Rob Vonsee wrote:

> Dear developer,
>
> In the past several years, we have developed a database application in
> MicroSoft's Access '97.
> With our user base growing, we're encountering more and more limitations in
> the Microsoft development environment, especially in the stability and
> number of simultaneous sessions of the database.
> Years ago, one of the main reasons for us to choose the Microsoft Access
> database was the fact that it can be distributed license free, giving us a
> leading edge in the market.

Um, that is patently false.  MS Access is anything but FREE.  You can develop
free applications to run on it but you cannot give Access away for free.
Unless there is a special embeded developers version that you can license.
But again, that is not free.  Please feel free to correct any misunderstanding
I might have.

And not to discourage you from using MySQL, but if you are writing a
commercial application you are going to sell that sits on top of MySQL, you
need to license MySQL for a very low price.  Compared to other dbs out there,
it is quite inexpensive.  See mysql.com for more info.



> >From what we hear, MySQL can be a good option for us to do our future
> development with.
> In order not to waste our Access development knowledge, we would like to
> continue developing in Access, but then use MySQL as database instead.
>
> Our question to you is:
> Do you know of any party that has gone this way before us and where we can
> continue our inquiries?

I do not know any specific people you can contact, but search the list
(lists.mysql.com) for 'access convert'.  There are a few threads about this.

Assuming you want to convert your tables over to MySQL, there are a few
utilities to do that.  The biggest problem is that people develop MS Access
applications with table and column names with funny characters in them e.g.
spaces, #, others.  You can probably get around this with the backtick for the
table/column name but I do not know how MyODBC handles that if at all.  I used
find and replace http://www.rickworld.com/products.html to change the
table/column names for an Access db I needed to convert to MySQL.  There is
another tool too.




> Not only to answer our questions, but perhaps they could also do the first
> conversions/engineering/development with us.
> Since we are a Dutch company, preferably a contact in The Netherlands, but
> please don't hesistate to answer if you have another lead.

If you want to ask a few questions privately, I am willing to answer.

b.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: query memory problem

[Bill Adams]

Elm Gysel wrote:

>/* perform query */
>query.execute(nr, Date.str(), Op, Hi, Lo, Cl, Vo);
> The problem is in the above line I think. If I comment it out the memory
> usage doesn't explode.
> I get around 1.5MB more mem usage each time I insert 2000 records.
> I have no clue what might go wrong here.
>}
> }
>
> After 10 times adding 2000records I have a memory usage of like 30MB. Are
> there any memory leaks involved in using the query object? Am I doing
> something wrong?

AFAIK MySQL does not have a memory leak.  I insert millions of records from a
single perl program without any problems.  It is probably a problem/leak within
the language you are using.  Possibly the Date.str() function too.

b.





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: FW: pls help for index problem

[Bill Adams]

Well, how many records are in the table?  How many would be returned by the
query you present?

Is the SQL you show the FULL sql?

What is the output of the EXPLAIN SELECT

What about "SHOW INDEX FROM TABLE gw".

b.


kmlau wrote:

> -Original Message-
> From: kmlau [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, October 24, 2001 9:42 AM
> To: 'Bill Adams'
> Subject: RE: pls help for index problem
>
> Thanks yr promptly reply !!
>
>   It seems no any change(improvement) by running explain again after erase
> quotes.   I also ran the command 'myisamchk -a gw.MYI'  before sending this
> consulting mail !!
> Would U give me more advice ?
>
> regards,
> kmlau
>
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bill Adams
> Sent: Tuesday, October 23, 2001 11:11 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: pls help for index problem
>
> kmlau wrote:
>
> >  I encountered a problem about indexing.  I want to add index on
> timerecord
> > field in table gw (shown as below) to speed up query relating with time.
> > However, I use explain command (explain select * from gw where timerecord
> =
> > '010902') to analyze the performace. As a result, it seems the query
> do
> > not use this index. Would U tell me why and how to correct this !!
>
> U do not need to specify the timestamp as a string, e.g. remove the
> quotes: timerecord=10902.  But more importantly run 'myisamchk -a' on
> the index
> (.MYI) file.  Doing both of these will help.
>
> b.
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

--
Bill Adams
TriQuint Semiconductor




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Connecting to a remote database.

[Bill Adams]

Jason Whitlow wrote:

> Sorry for for the dumb question buuut.
>
> I have been connecting to a database on my localhost using perl's DBI.pm
>
> This is how I currently do it.
>
> use DBI;
> $data = "databasename";
> $driver = "DBI:mysql";
> my $dbh = DBI->connect("$driver:database=$data", "username", "password")
> or die "Can't connect";
>
> How would I connect to the same database on a remote server.

>From the top of 'man DBD::mysql':
   use DBI;


   $driver = "mysql";
   $dsn =
"DBI:$driver:database=$database;host=$hostname;port=$port";


You can also do $dbh= DBI->connect( "dbi:mysql:$db;host=$host", ...);

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Sessions

[Bill Adams]

"Ing. Gustavo Edelstein" wrote:


> Thanks for your answer, but I need to know the names of the users currently
> logged in the database.
> Regards,

SHOW will do that as will 'mysqladmin processlist'.
http://www.mysql.com/doc/S/H/SHOW_PROCESSLIST.html

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Install help

[Bill Adams]

Tom Hicks wrote:

> I am thinking of running MySQL on my box for some work with PHP. I am
> concerned about the mods to the directory structure. I can't seem to
> find a list for mods it will make on RedHat7.1
> Could you please help me out with that?

Not sure what you mean by 'mod', but you can either:

o run 'rpm -qlp /path/to/therpm.rpm' to see what files are going to get
installed before installing it.

o D/L the binary from mysql.com and put the tar in a single directory.

o Compile the source with ./configure --prefix=/usr/local/mysql to keep all
of the mysql files in one location.

RPMs are probably your best bet.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: forgot manager password

[Bill Adams]

"Oscar Castaneda V." wrote:

> Hi,
>
> i ran into a pretty boxy problem this morning, as I tried to login as mysql manager
> with
> #mysql -u root -p
> # (i forgot the password)
>
> its either this, or the other administrator, whom i cannot contact right now, 
>changed the password without my knowledge of it.
>
> What can i do? is there some kind of recovery procedure? Or can i reinstall without 
>affecting already present databases?

The searchable documentation and/or the searchable faq on the mysql.com website will 
answer your question.  Try searching on
"forgot password".

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Table Crashes Constantly on prod server!!

[Bill Adams]

Michael Blood wrote:

> I am running 3.23.40 on a dual pentium III 800 with 1 GB Ram.
>
> I have been getting an table handler returns error 127 error
>
> If I fix the table with myisamchk -r or -o it will work for a while and then
> I will get the same error again.

Try dumping and restroing the table.
(backup your database files)
mysqldump --add-drop-table -q db table >tmp.sql
(possibly remove the table.* files here)
mysql db http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: concatenating fields

[Bill Adams]

Harpreet wrote:

> I am using concat in my sql and it runs fine on mysql client. But when use
> din php it gives me an error:
> Supplied argument is not a valid MySQL result resource in
> /var/www/html/scripts/cfg_code_delete.php on line <
>
> $ssql="select concat(category, "-", code) as fill_column, code_id as
> submit_column from sys_code_tbl order by category";
> echo "";
> $result = mysql_query($ssql);
> while ($row = mysql_fetch_array($result))
>   {
>   echo " value='".$row["submit_column"]."'>'".$row["fill_column"]."'";
>   }

>
>
> Help is appreciated.

1) Check your return values:
$result = mysql_query( $ssql );
if( ! $result ){
  print "Query Failed: $query\n";
  exit;
}
while( ... )


2) You did not escape the quote in the query string. It shold be like:
$ssql="select concat(category, \"-\", code) as fill_column, code_id as

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Printing

[Bill Adams]

"Wix,Christian XCW" wrote:

> Hi!
>
> How print the result from a query on my printer? I'm using the command line
> on linux.
> Lets say that I want to print everything from the table "Mercedes" in the
> database "Cars".

Pipes.

> How do I adjust the lenght of the lines?

man nenscript

Basically:
echo "SELECT * FROM Mercedes" |mysql Cars |nenscript




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysql 4.0

[Bill Adams]

Sommai Fongnamthip wrote:

> Hi,
> If I install mysql-4.0 to upgrade in mysql-3.23.xx with old setting value
> (use myisam type), Could I need to change or re-load my old db?

AFAIK, If you are keeping the table type, you can just copy the .MYI, .MYD, and .frm
files to the new location.  Or leave them where they are and point 4.0 to them.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Length limit of 500 on primary keys?

[Bill Adams]

Steve Meyers wrote:

> > > At a previous job, we tested a 32-bit hash function by running it
> > > against hundreds of thousands of unique URL's stored in our
> > > database.  We found one collision.  A 64-bit hash is billions of
> > > times better (4 billion, to be exact).
> >
> > Good to know.  I wonder how many collisions I'd find if I ran it over
> > every URL listed in the directory www.yahoo.com.
> >
> > Which 64 bit hash function did you use?  Invent your own, or something
> > "off the shelf"?
> >
>
> We found a public domain one on the net see 
>http://www.burtleburtle.net/bob/hash/evahash.html for some sample code.  It's only a 
>32-bit hash though.  However, that same page appears to have instructions for a 
>64-bit hash function as well, but I haven't tried it at all.  I'd be curious to know 
>how many collisions you find hashing all the URL's in yahoo's database :)  I don't 
>know how long that would take, but if you do it I'd like to hear the results.
>
> Since the hash function takes a key and an initial value, you could try running it 
>with two different initial values and/or keys.  This would give you effectively a 
>128-bit hash, which you could store across two fields in MySQL.  I'm guessing that 
>the 64-bit hash will probably be good enough though.

I am not understanding why having a hash and the full url in the database would not 
take care of the collisions.  Even if you had 10 collisions for a 16 bit hash (say), 
if your query was:
SELECT ... WHERE hash=thehashvalue AND url='theurl' you would get very fast lookups on 
the hash and the url comparison would not add much to the query at that point.  You 
could even do a partial index on the url, e.g.  "KEY( hash, url(200))".

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Rotating error log?

[Bill Adams]

Rafal Jank wrote:

> Hi!
> Is there a possibility to rotate mysql error log without stoping the server?

I was waiting for someone else to respond. But some possibilities are 1: use
the apache program 'rotatelog'.  Not sure how you would do this.

In Linux you can rename the file while the server is running.  Doing that and
then 'killall -HUP mysqld' might cause mysql to reopen the log files under the
original name.

But both of these are untested by me.

b.




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: SQL String max length ?

[Bill Adams]

TOMASSONI Dominique wrote:

> I stored a sql string in a file and when I launch it, it doesn't perform
> because the string is truncated before the end.
>
> Does mysql limit the length of an sql string ?

Not that I have ever run into.  But ODBC does.

What command are you running?  E.g. on linux "mysql db < somequery.sql".
 How long is the sql? (run wc somequery.sql".

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Best Table And Index Structure

[Bill Adams]

Martyn Wendon wrote:

> Hi,
>
> I'm new to MySQL and indeed SQL in general (coming from an MS Access and
> Flat Text File background).
>
> I have data in text form at the moment of the following fields:
>
> ID (Auto increment)
> ARTIST (Text up to 255 characters long)
> DESCRIPTION (Text up to 255 characters long)
> PRICE (Decimal up to 00.00 long)

CREATE TABLE table (
  id integer DEFAULT 0 NOT NULL AUTO_INCREMENT PRIMARY KEY,
  artistvarchar(255),
  description varchar(255),
  price   decimal( 8,2 )
  key( artist(20), description(20)),  -- for queries on just artist or artist
and description.
  key( description(20)),  --for queries on just desctiption
  fulltext( artist, description) -- for queries on FULL words anywhere in
artist and description
);

Read http://www.mysql.com/doc/C/R/CREATE_INDEX.html and
http://www.mysql.com/doc/F/u/Fulltext_Search.html to understand the indexes
better.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Length of columns and attachents as a column...

[Bill Adams]

[EMAIL PROTECTED] wrote:

> Hi all,
> Is it possible to have an external file as an attachment in MySQL?
> An example would be a .gif, .doc, .ppt, .ram etc.
> If there is no way directly have MySQL table to store an attachment, is there
> any work around?
> Any guidance will be appreciated.

To store it in the database you should use a blob:
http://www.mysql.com/doc/B/L/BLOB.html

I usually store the path to the file in the database 'though.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: pls help for index problem

[Bill Adams]

kmlau wrote:

>  I encountered a problem about indexing.  I want to add index on timerecord
> field in table gw (shown as below) to speed up query relating with time.
> However, I use explain command (explain select * from gw where timerecord =
> '010902') to analyze the performace. As a result, it seems the query do
> not use this index. Would U tell me why and how to correct this !!

U do not need to specify the timestamp as a string, e.g. remove the
quotes: timerecord=10902.  But more importantly run 'myisamchk -a' on the index
(.MYI) file.  Doing both of these will help.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Fw: Can't Connect To MySQL

[Bill Adams]

"M. Alageswaran" wrote:

> Thanks man, I tried that but still no hope..
> when I did a ps -ef | grep mysqld there is nothing in my output!

-ef is for HP-UX (I think).  For Linux you want 'ps ax |grep mysqld' note
there is no dash in front of the ax.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

[OT] Re: Antigen found VBS/VBSWG_based@mm.Worm (Norman) virus

[Bill Adams]

Carl Troein wrote:

> Bill Adams writes:
>
> > > This is odd.  The only machine in cricalix.net (which I own/run) is a
> > > linux box.  Who has a badly configured server out there?
> > Even more odd is that the 'from' address I saw was [EMAIL PROTECTED]
> >  I thought my company server was spamming the list.
>
> It appears that your software tries to act smart by
> replacing the from header of the mail with something
> else. What was actually there was just the string
> "Antigen", without a host or domain. You appear to
> be using different mailers, and I'm rather surprised
> to see that this problem is not caused by outlook.

I am tied to an exchange server.  I just happen to use Netscape as my client
right now until evolution is a smidgen better.

b.
mysql



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Could not load module libz.a(shr.o) for run scripts/mysql_install_db

[Bill Adams]

[EMAIL PROTECTED] wrote:

> >Description:
>The following is the error message:
> #scripts/mysql_install_db
> Could not load program ./bin/my_print_defaults:
> Dependent module libz.a(shr.o) could not be loaded.
> Could not load module libz.a(shr.o).
> Error was: No such file or directory

Don't know if there is a pre-packaged aix version, but you can get it from 
http://www.gzip.org/zlib/

b.

>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Frequently corrupt tables

[Bill Adams]

I realized that I was using an older version of libmysqlclient.  So I recompiled and
linked the msql-mysql-modules against mysql-3.23.41...

Bill Adams wrote:

> o If there is no call to 'flush tables', even a small data load will cause
> myisamcheck to report "warning: 1 clients is using or hasn't closed the table
> properly" when I know there is no client accessing it.  In this case myisamcheck
> does fix the problem.

I still get this error.  However, it does not seem like my tables are getting
corrupted anymore.

For now I am done looking for the source of this bug as the 'flush tables' takes care
of them all.

--Bill



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Antigen found VBS/VBSWG_based@mm.Worm (Norman) virus

[Bill Adams]

Duncan Hill wrote:

> On 22 Oct 2001 [EMAIL PROTECTED] wrote:
>
> > Antigen for Exchange found Unknown infected with
> > [EMAIL PROTECTED] (Norman) virus. The file is currently
> > Removed.  The message, "SV: Here you have, ;o)", was sent from Pål
> > Wester and was discovered in Public Folders\Mailing Lists/MySQL
> > located at nascom/First Administrative Group/ET.
>
> This is odd.  The only machine in cricalix.net (which I own/run) is a
> linux box.  Who has a badly configured server out there?
>
> (sql, database)

Even more odd is that the 'from' address I saw was [EMAIL PROTECTED]
 I thought my company server was spamming the list.

??

--Bill
mysql



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Frequently corrupt tables

[Bill Adams]

Bill Adams wrote:

> Spoiler: You may be right about the bad libs...

[snip]

> *** OMG ***
> But haha I cannot believe this, I was just looking at the libraries linked by
> mysqld with ldd and it is using the informix libpthread.so.  Hmm, crap. *me
> slaps head*

Small Update:

o If there is no call to 'flush tables', even a small data load will cause
myisamcheck to report "warning: 1 clients is using or hasn't closed the table
properly" when I know there is no client accessing it.  In this case myisamcheck
does fix the problem.

o If I call 'flush tables' even at the program exit, I do not get the warning.

o Using the statically linked 4.0 binary from mysql.com had no effect on the
results.

o Upgrading DBI to the latest version had no effect on the results.

o Upgrading to the latest msql-mysql-moudles did not effect the results.


I was playing around with the 'flush tables' and managed to quickly corrupt a table
without any flush call.  I saved a copy of the table if the mysql folks should want
to take a look for some unknown reason.

[bill@host ~/dev]$ /usr/local/mysql-4.0/bin/myisamchk -e
../bad-tables/pcm_test_site_200105.MYI
Checking MyISAM file: ../bad-tables/pcm_test_site_200105.MYI
Data records:   58923   Deleted blocks:   0
/usr/local/mysql-4.0/bin/myisamchk: warning: 1 clients is using or hasn't closed
the table properly
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check records and index references
/usr/local/mysql-4.0/bin/myisamchk: error: Record-count is not ok; is 58328
Should be: 58923
/usr/local/mysql-4.0/bin/myisamchk: warning: Found595 deleted blocks
Should be: 0
MyISAM-table '../bad-tables/pcm_test_site_200105.MYI' is corrupted
Fix it using switch "-r" or "-o"

But running myisamchk without the -e just gives the 'clients using' warning.  Also,
'-r' will repair the table in this case and adding the flush back in seems to
prevent the error from happening.

Well, it is almost beer o'clock.  I will try this on other machines on Monday.

--Bill







-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysql.sock

[Bill Adams]

cedric wrote:

> /usr/bin/mysql start returns the error message:
> Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock.
> It was there, but now it's not.

That is the wrong command, use either safe_mysqld or mysqld_safe.  Or possibly
/etc/rc.d/init.d/mysql start


> Also, all the files in /var/lib/mysql now have green question marks over them.

Uh, It sounds like your file system is corrupt.


> The did not before. I was able to open *.err and read it.
> '/usr/bin/mysql_install_db' did not change anything.

Of course not, that only boot straps the db.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Mysql server doesn't run

[Bill Adams]

Mark Coldheart wrote:

> I've a little bit problem about running mysqld
> server. I've run "/etc/rc.d/init.d/mysqld start" it
> will appear "mysqld dead but subsys locked". But if i
> stop the service, it will fail. It is also happened
> when i kill the service.

Mm, redhat question.

Make sure mysql is not running:
ps ax |grep mysql

If it is not, the lock file is in /var/lock/subsys.  Remove the mysql one
and start it.


>The other problem is when i run mysql it will
> appear "can't connect to Mysql server through socked
> "/var/lib/mysql/mysql.sock"(111) "
>What i must do now ??? Which part that i have to
> configure ?

Without a running mysqld, you cannot connect.

b.




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: join tables on UPDATE

[Bill Adams]

JohnHomer wrote:

> can mysql allow table joins when using UPDATE query? like

The manual answers these questions.

> update tranfer left join transferdetails
> on transfer.docno = transferdetails.docno
> set transferdetails.docno = concat('SJ',transferdetails.DocNo)
> where
> transferdetails.docno not regexp '^SJ' and
> lower(xfrom) = 'san juan';
>
> i get an error
>
> ERROR 1064 at line 1: You have an error in your SQL syntax near 'left join trans 
>ferdetails on transfer.docno = transferdetails.docno set transfer' at line 1
>
> im using mysql 3.23.41 on win32

The manual states clearly that 3.23 does not support joins with UPDATE.  And RTM to 
find out if 4.0 does.

b.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Frequently corrupt tables

[Bill Adams]

Spoiler: You may be right about the bad libs...


Kyle Hayes wrote:

> On Thursday 18 October 2001 12:31, Bill Adams wrote:
> Hmm, 2.2 doesn't do SMP really well.  However, its drawbacks are limited to
> underuse of the CPUs rather than any kind of corruption or other issue.  You
> would get much better performance with 2.4, but 2.2 is probably a little more
> stable.

2.4 is not an option for me because:
o Right not I use Informix as my production database.  Until they officially
support 2.4 or I 'upgrade' to MySQL I am stuck in the 2.2.x series.

o Until the VM crap is worked out, I am not installing the 2.5, er. 2.4 kernels
on any production machines unless it comes with the distribution.


> Is this a DAC960 or something similar?  If so, make sure you have the
> absolute latest drivers.  We have some dual processor machines with those
> controllers (or something closely related) and had to do many driver updates
> before it stabilized.  And, we're still not totally convinced.  If this is a
> big SCSI RAID card, I would definitely check the drivers and make sure that
> there isn't something newer/more stable out there.

I have a Mylex DAC1164P for the /, /home, etc. using RAID5.  All of the MySQL
tables are on an "Adaptec AIC-7899 Ultra 160/m SCSI host adapter" which is a
dual channel UW controller.


> > Statistics:
> >
> > (scsi0:0:0:0)
> >   Device using Wide/Sync transfers at 80.0 MByte/sec, offset 31
> >   Transinfo settings: current(10/31/1/0), goal(10/127/1/0), user(9/127/1/2)
> >   Total transfers 36738885 (18761976 reads and 17976909 writes)
>
> Waiter!  I'll have two of what that gentleman over there is having.

:)


> > > What filesystem are you running?
> >
> > ext2. At least that is what linux sees.  The disks are actually hardware
> > raid0 winchester flashdisks.
>
> Flash?  I.e. these are solid state disks?  If that is true, then maybe that
> is part of the problem.  Flash is different from "normal" disk.

No, that is the product name. http://www.winsys.com/products/  Basically, it is
a box with 12 drives in it and a dual channel scsi controller (in my model).  As
far as Linux is concerned, each box appears as two very large, very fast drives
on two channels.  You can partition in different ways and get them with one
channel, etc..


> Can these disks correct for bad sectors?  If so, the usual method to force
> remapping of bad sectors is to use dd:

AFAIK, the flash controller corrects for that.  But then again I am running
RAID0 and winchester systems does not officially support that level (they do
0+1, 5, others) because part of what they are selling besides blazingly fast
raid boxes is data security and integrity.  Obviously you do not get that with
RAID0.  For my application that is not an issue.  I care only about speed and
volume: my raw data is backed up elsewhere.  But I digress


> dd if=/dev/zero of=/dev/XXX bs=1M count=YYY
>
> Where XXX is the RAID device and YYY is the number of megabytes of storage.
>
> Please make a backup of your data first :-)
>
> On a "normal" disk, this causes a write to each sector on the whole drive.
> That in turn causes the firmware on the drive to remap any bad sectors found
> this way.  If your disks support this, you might be unpleasantly surprized
> how many problems go away after this.  Most newer drives do this
> automatically, but it can still trash your data.  By doing the line above,
> you force the issue before you have valid data on the disk.

I in my case I just 'login' to the controller on the flashdisk to get statistics
such as bad sectors and such.

Not to sound too much like an advertisement for Winchester Systems but these
people have been around for a long time and the controllers I have have been too
and are well tested and used by many other companies/people with much more
critical needs than I have.  I also do not have problems with the Informix
tables on the same disks using the same dataloader under the same conditions.
And it happens on different enclosures/disks/etc..


> We've done 7M rows in one single input file (just a hair under the 2GB limit
> for the older ext2 filesystem we have on that particular machine).  No
> problems at all.  That was with MySQL 3.23.26 or something close to that.
> We've done tests much larger than this that were either driver via Perl and
> DBI, or from a flat file.

Well, I am running an ancient version of DBI. I will upgrade to a more modern
version of DBI and msql-mysql-modules; reload data; and report back.


> > > Is the data getting mangled or the index?  If myisamchk can fix the
> > > problem,
> >
> > That is the funny thing, I had to do a mysqldump > file; mysql  > the table.  myisamchk w

Re: Frequently corrupt tables

[Bill Adams]

Kyle Hayes wrote:

> > I found yesterday (at the advice of this list) that adding an occasional
> > call to "FLUSH TABLES" fixed my corruption problems.  I would do that right
> > before the disconnect or program exit.
>
> What kernel are you using?  Some of the 2.4 series have... odd... behavior
> with regards to caching.

Linux host 2.2.19 #6 SMP Wed Jul 11 10:55:03 PDT 2001 i686 unknown
2GB Memory, 4 CPUs.
(It happened on other systems with different kernel versions too.)

> Are you using SCSI or IDE.  We've run many tests with both and not had any
> corruption problems unless we did something whacked like pull the power for
> the machine while it was running the test.

SCSI.  (Had problem with different controllers on different systems)

Three dual channel controllers, all the same:

[bill@host ~/dev]$ cat /proc/scsi/aic7xxx/0
Adaptec AIC7xxx driver version: 5.1.33/3.2.4
Compile Options:
  TCQ Enabled By Default : Disabled
  AIC7XXX_PROC_STATS : Disabled
  AIC7XXX_RESET_DELAY: 5

Adapter Configuration:
   SCSI Adapter: Adaptec AIC-7899 Ultra 160/m SCSI host adapter
   Ultra-160/m LVD/SE Wide Controller Channel A at PCI
2/6/0
PCI MMAPed I/O Base: 0xf9dfa000
 Adapter SEEPROM Config: SEEPROM found and used.
  Adaptec SCSI BIOS: Disabled
IRQ: 21
   SCBs: Active 0, Max Active 1,
 Allocated 15, HW 32, Page 255
 Interrupts: 36738969
  BIOS Control Word: 0xb8f8
   Adapter Control Word: 0x7c5d
   Extended Translation: Enabled
Disconnect Enable Flags: 0x
 Ultra Enable Flags: 0x
 Tag Queue Enable Flags: 0x
Ordered Queue Tag Flags: 0x
Default Tag Queue Depth: 8
Tagged Queue By Device array for aic7xxx host instance 0:
  {255,255,255,255,255,255,255,255,255,255,255,255,255,255,255,255}
Actual queue depth per device for aic7xxx host instance 0:
  {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}

Statistics:

(scsi0:0:0:0)
  Device using Wide/Sync transfers at 80.0 MByte/sec, offset 31
  Transinfo settings: current(10/31/1/0), goal(10/127/1/0), user(9/127/1/2)
  Total transfers 36738885 (18761976 reads and 17976909 writes)


> What filesystem are you running?

ext2. At least that is what linux sees.  The disks are actually hardware raid0
winchester flashdisks.


> Just running FLUSH TABLES sounds like it is only going to make the problem
> less common, not fix it.  Something is corrupting your indexes/data.

I loaded three big tables last night with no problems (after adding the
occasional $dbh->do( "FLUSH TABLES" ).  Before it would happen at least once
when doing a large (re)load of data.



> Is the data getting mangled or the index?  If myisamchk can fix the problem,

That is the funny thing, I had to do a mysqldump > file; mysql 
> it is likely that the index is the problem.  MySQL will cache the index in
> memory, but not the data.  Thus, if you see data mangling problems and
> possibly index problems, I would look at the kernel, disk etc.  If you are
> only see index problems, but the data looks OK, then the version of MySQL
> might be a problem or maybe you have a bad build.  MySQL builds more cleanly

It happened with 3.23.41.


> than most OSS projects, but it is a big complex beastie and can build
> incorrectly without obvious errors sometimes in our experience.  Bad library
> versions can also be a factor.

I did build/run this on a RH6.2 system.


> We've run tests with 1000 hits per second on a database on a cheasy IDE drive
> without a problem.  We've run those tests for hours at a time with no
> problems.  SCSI definitely works better than IDE, but the newer IDE drives
> aren't that bad anymore.  They still use a lot of CPU.

It is not the selects that cause the problems, it is lots of inserts.  Again, it
only seems to happen on large loads.  I have three main tables and a large load
means:
mysql> select count(*) from pcm_test_header_200109;
+--+
| count(*) |
+--+
| 5844 |
+--+
1 row in set (0.07 sec)

mysql> select count(*) from pcm_test_summary_200109;
+--+
| count(*) |
+--+
|   840413 |
+--+
1 row in set (0.04 sec)

mysql> select count(*) from pcm_test_site_200109;
+--+
| count(*) |
+--+
|  7248366 |
+--+
1 row in set (0.02 sec)

mysql>

Any of the three tables can have problems but it is usually the site table.



> If your drives to write caching, that can be a problem if you have a power
> drop.  Most IDE drives (all?) will cache writes to allow the disk firmware to

This is not a power or crash problem.  It happens WHILE the loader is running.

It could be a DBI/DBD bug.  I [try to] insert all of the above records with a
single database handle (connection).

b.




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To reque