Backup problem

[Manish]

This is what I need to do, what is the best approach for doing this?

I need to take backup of few tables in mysql, then I need to drop that
entire DB, recreate it, (and sth else that's not relevant here) and then
restore these backed up tables. This backup and restoring should be as fast
as possible. How can I do this?

TIA,
- Manish


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

Re: 2 questions: hostname variable and exit if slave

[Ted Byrne]

Regarding number 1, you might be able to do something with this:
mysql> show variables like 'pid%';
+---+-+
| Variable_name | Value   |
+---+-+
| pid_file  | /var/lib/mysql/tatooine.xyzcorp.com.pid |
+---+-+
Regarding number 2 (as well as #1), my inclination is to reach for perl, 
but I don't know if that's an option for you.

HTH,
Ted
At 11:01 PM 10/6/2004, you wrote:
I have 2 unrelated questions:
1) Is there any way to get the hostname that the server is
running on from within mysql?
All I can come up with is (pseudo coded):
mysql> system echo insert mysql.hostname `hostname` ... > 
hostname.sql
mysql> source hostname.sql

Does mysql make it available as a variable somewhere? I have scoured
the docs to find nothing obvious.
Note that I am not interested in where I am connected from
(select user();).
2) Is there a way to exit an sql script at the top of the
script if it turns out that it is running on a slave in a
replication scenario?
... something like:
if slave then exit;
(followed by more sql)
A 'show slave status' dumps something like a table, but I don't
see how to save any of the info in a variable that I might
later use in a case statement. Basically, if the results of
show slave status aren't zip, I should exit because I am the/a
slave.
Thanks for any clues!
Brian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

2 questions: hostname variable and exit if slave

[Brian C. Hill]

I have 2 unrelated questions:

1) Is there any way to get the hostname that the server is
running on from within mysql?

All I can come up with is (pseudo coded):

mysql> system echo insert mysql.hostname `hostname` ... > hostname.sql
mysql> source hostname.sql

Does mysql make it available as a variable somewhere? I have scoured
the docs to find nothing obvious.

Note that I am not interested in where I am connected from
(select user();).

2) Is there a way to exit an sql script at the top of the
script if it turns out that it is running on a slave in a
replication scenario?

... something like:

if slave then exit;
(followed by more sql)

A 'show slave status' dumps something like a table, but I don't
see how to save any of the info in a variable that I might
later use in a case statement. Basically, if the results of
show slave status aren't zip, I should exit because I am the/a
slave.

Thanks for any clues!

Brian

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

Delete duplicate entry

[Batara Kesuma]

Hi,
I have a table that looks like:
CREATE TABLE `message_inbox` (
  `member_id` mediumint(8) unsigned NOT NULL default '0',
  `message_id` int(10) unsigned NOT NULL default '0',
  `new` enum('y','n','replied') NOT NULL default 'y',
  `datetime` datetime default NULL,
  KEY `idx_1` (`member_id`,`new`),
  KEY `idx_2` (`member_id`,`datetime`)
) TYPE=InnoDB


Now, I want to add a primary key to it.
ALTER TABLE message_inbox ADD PRIMARY KEY (member_id, message_id)

But there are already some duplicated entries. 
ERROR 1062: Duplicate entry '10244-80871' for key 1

How can I tell MySQL to delete the duplicated entry and continue to make primary key? 
Is there any efficient way to do this? Thank you very much.

Regards,
bk

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

Re: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes

[Christopher L. Everett]

Ed Lazor wrote:
-Original Message-
From: Christopher L. Everett [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 06, 2004 1:47 AM
To: Mysql List
Subject: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes
I have an application where I create a faily large table (835MB) with a
fulltext index.  One of our development workstations and our production
server will run the script to load the table, but afterwards we have a
pervasive corruption, with out of range index index pointer errors.
Oddly, my development workstation doesn't have those problems.
My box and the ones having the problems have the following differences:
 - my box runs ReiserFS, the problem boxes run XFS
 - my box has a nice SCSI HD subsystem, the problem boxes do IDE.
All three boxes run Linux 2.6.x kernels, and my workstation and production
server share the same mobo.  Come to think of it, I saw similar corruption
issues under 2.4.x series kernels and MySQL v4.0.x, it just wasn't the
show stopper it is now.
Also, on all three boxes, altering the table to drop an index and create
a new one requires a "myisamchk -rq" run afterwards when a fulltext index
either exists or gets added or dropped, which I'd also call a bug.
The problems you're describing are similar to what I've run into when there
have been hardware related problems.  

One system had a problem with ram.  Memory tests would test and report ram
as ok, but everything started working when I replaced the ram.  I think it
was just brand incompatibility or something odd, because the ram never gave
any problems in another system.
I can generate the problem on much smaller data sets, in the mid tens of
thousands of records rather than the millions of records.
I'll do a memtest86 run on the development boxes overnight, but as I did 
that
just after I installed linux on them and used the linux badram patch to 
exclude
iffy sections of RAM, I don't think thats a problem.

One system had hard drive media slowly failing and this wasn't obvious until
we ran several full scan chkdsks.
3 hard drives all of different brand, model & size, and the problem 
happening
in the same place on both?  Not likely.

The funniest situation was where enough dust had collected in the CPU fan to
cause slight over heating, which resulted in oddball errors.
This isn't a problem on my box.  I have a 1.5 pound copper heatsink with a
90mm heat sensitive fan and a fan+heatsink for the hard drive, and I saw
myisamchk consistently generate the same error in the same place over and
over.  The sensors report my CPU running in the 45 degree centigrade range
on my box pretty consistently.
In each of these cases, everything would work fine until the system would
start processing larger amounts of data.  Small amounts of corruption began
to show up that seemed to build on itself.
This may or may not relate to what you're dealing with, but maybe it will
help =)
I'll look, but I don't think that's the problem.   I'm going to see how 
small
of a data set will cause this problem and file a bug report.

--
Christopher L. Everett
Chief Technology Officer   www.medbanner.com
MedBanner, Inc.  www.physemp.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Mysql Query Browser is not launch

[Daniel Kasak]

Spenser wrote:
I just tried installing it on my RedHat Linux 8 laptop and it complained
about libXcursor.so.  This is the message:
error while loading shared libraries: libXcursor.so.1: cannot open
shared object file: No such file or directory
I downloaded the full tar ball version.  I'm using the Gnome 2 desktop. 
Do I need to download libXcursor.so from some where and install it?
 

libXcursor is a part of my xorg-x11 installation:
dkasak ~ # locate libXcursor.so
/usr/X11R6/lib/libXcursor.so
/usr/X11R6/lib/libXcursor.so.1.0.2
/usr/X11R6/lib/libXcursor.so.1
dkasak ~ # qpkg -f /usr/X11R6/lib/libXcursor.so
x11-base/xorg-x11 *
dkasak ~ #
I assume it also comes with XFree86. Try the above 'locate' command ( 
locate libXcursor.so ) and see if it's on your system or not. You may 
have to upgrade to a later version of XFree86 ( or jump ship to xorg-x11 
like most ).

That's well outside the scope of the mailing list, and I've never really 
used RedHat apart from my first 3 months of Linux, so I don't want to 
give you advice that will mess up your system, but it looks like either 
your X installation is broken, or too old.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Replication problem with a cross database query

[Bill Thomason]

I originally posted a query about a problem entitled "Table doesn't
exist on query replication problem..."

The original title might be a little misleading.  The slave replication
is halting on a transaction that contains a query that spans two
databases - one that is being replicated and the other is not.

Could anyone provide me with some general rules of thumb about breaking
down such a query?  This may sound like a vague or possibly stupid
question since this predicament is probably very specific to the
situation.

I didn't write the original query but I am establishing the master/slave
relationships and uncovered the bug in doing so.

Thanks in advance,
-Bill Thomason


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

Re: Mysql Query Browser is not launch

[Spenser]

I just tried installing it on my RedHat Linux 8 laptop and it complained
about libXcursor.so.  This is the message:

error while loading shared libraries: libXcursor.so.1: cannot open
shared object file: No such file or directory

I downloaded the full tar ball version.  I'm using the Gnome 2 desktop. 
Do I need to download libXcursor.so from some where and install it?

Re: Mysql Query Browser is not launch

[Daniel Kasak]

[EMAIL PROTECTED] wrote:
Dear list.
I´m trying to work with Mysql-Query Browser Application. So, I 
installed it following the manual instructions but when I try to run 
it cannot be launched.

Into the ./mysql-query-browser/bin directory I type 
mysql-query-browser and  I receive this error message:

bash:mysql-query-browser: command not found
I tried too from RUN tab from GNOME task bar and the problem is the same.
Excuse me if this ask is stupid.
The current directory is not in your path.
Either type the *whole* path to the binary, or cd into the directory 
with the binary, and *preceed* the name of it with ./   eg:

cd /path/to/binary
./mysql-query-browser
--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Mysql Query Browser is not launch

[elimachi]

Dear list.


I´m trying to work with Mysql-Query
Browser Application. So, I installed it following the manual instructions
but when I try to run it cannot be launched.

Into the ./mysql-query-browser/bin directory
I type mysql-query-browser and  I receive this error message:

bash:mysql-query-browser: command not
found

I tried too from RUN tab from GNOME
task bar and the problem is the same.

Excuse me if this ask is stupid.

Any suggestions?


Thank you.

EDWIN LIMACHI N.

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

Re: COMPRESS engine

[Richard Jacobsen]

Thanks,  I'll give it at try.

Richard

On Wed, Oct 06, 2004 at 05:14:01PM -0500, Dan Nelson wrote:
> In the last episode (Oct 06), Richard Jacobsen said:
> > I've got some large tables, ~160GB each with indexes, which are only
> > used for read only access.  Since obviously all table space doesn't
> > fit in memory, Will using the compress engine speed reads/seeks on
> > these tables up at all, or will they slow them down considerably?
> 
> It won't speed up seeks, but it will increase the chances that two
> nearby records are on the same disk block, potentially saving you a
> seek to fetch the 2nd one.  Full table scans may be faster or slower,
> depending on your disk speed and CPU.
> 
> -- 
>   Dan Nelson
>   [EMAIL PROTECTED]
> 

-- 
"a professional is simply one who gets paid for doing what an amateur does for
love."
   
 -- Ursula K. Le Guin

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

Re: COMPRESS engine

[Dan Nelson]

In the last episode (Oct 06), Richard Jacobsen said:
> I've got some large tables, ~160GB each with indexes, which are only
> used for read only access.  Since obviously all table space doesn't
> fit in memory, Will using the compress engine speed reads/seeks on
> these tables up at all, or will they slow them down considerably?

It won't speed up seeks, but it will increase the chances that two
nearby records are on the same disk block, potentially saving you a
seek to fetch the 2nd one.  Full table scans may be faster or slower,
depending on your disk speed and CPU.

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Re: Connection Errors

[Dave Goodrich]

Dave Goodrich wrote:
[Previously posted to MySQL forum]
Howdy the group,
I have a cluster of email servers all using mysql for authentication. 
Using vpopmail and spamassassin, all auth requests, pop, delivery 
instructions, etc, go to mysql. Currently we process between 50k and 65k 
messages a day inbound. Each delivery requires a query, and each pop to 
download messages requires a query. Approximately 50% of those messages 
will also require a query for spamassassin preferences.

I am having complaints from users that they are reprompted for their 
password several times a day. So, I am trying to see if I can identify 
the failure from the host end.

1) I have restarted safe_mysqld with the warning option "--warnings". I 
can see the server.log shows aborted client errors, but not aborted 
connection errors. How do I get the aborted connection errors to display 
in the logs? ( see approx 30 client errors a day, but in excess of 2500 
connection errors)
Is it not possible to log the reason for an aborted connection?
DAve
2) I have read many places that there is potential for communication 
errors due to ether settings. I currently have the host running 1000fdx 
to a 1000fdx port on the switch, the clients are running 100fdx to a 
100fdx port on the same switch. Should I be running both host and 
clients at the same speed?

3) In my mail logs I have failures logged which state that the user 
doesn't exist. From the vpopmail maillist, this means the connection 
failed (provided the user does in fact exist).

Any advice, help, would be appreciated. Thanks,
DAve

Some background,
### Host server: Sun Enterprise 450, 2 gb ram. 1000fdx connection to a 
1000fdx port on a netgear switch.

# mysqladmin -uroot -p version
mysqladmin Ver 8.23 Distrib 3.23.53, for sun-solaris2.8 on sparc
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 3.23.53-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 2 days 11 hours 21 min 9 sec
Threads: 1 Questions: 1591388 Slow queries: 24 Opens: 0 Flush tables: 1 
Open tables: 7 Queries per second avg: 7.448

### /etc/my.cnf
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=256M
set-variable = max_allowed_packet=16M
set-variable = table_cache=256
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=4
set-variable = connect_timeout=15
set-variable = back_log=50
set-variable = max_connections=500
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=4
server-id = 1
# lets have a bin log
#log-bin
# Lets have a query log as well
log
# Lets save slow queries
set-variable = long_query_time=20
log-long-format
log-slow-queries = /usr/local/mysql/var/slow_queries.log
## Status after 30 minutes running
#mysqladmin -uroot -p extended-status
+--+-+
| Variable_name | Value |
+--+-+
| Aborted_clients | 0 |
| Aborted_connects | 51 | <- can I log these?
| Bytes_received | 1892430 |
| Bytes_sent | 2955038 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 6356 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 3 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 4025 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 6932 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 7 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 6 |
| Connections | 471898 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 8014 |
| Handler_read_next | 1651 |
| Handler_

COMPRESS engine

[Richard Jacobsen]

Hi everyone,

I've got some large tables, ~160GB each with indexes, which are only used for
read only access.  Since obviously all table space doesn't fit in memory, Will 
using the compress engine speed reads/seeks on these tables up at all, or will 
they slow them down considerably?

Thanks,
Richard

-- 
"a professional is simply one who gets paid for doing what an amateur does for
love."
   
 -- Ursula K. Le Guin

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

init.d - need 'status' option

[Marc Knoop]

I am implementing mysql on a Redhat Cluster for high availability and will 
be using replication.  I wanted to use 4.1.5, but learned that the cluster 
suite requires an init.d script that will answer to the 'status' command. 

'status' does not seem to be included anymore.  [RH will only support an 
older 3.* version on the CS.] 

1)  Is it possible to add it to the included mysql.server init.d script? 

2a)  What was the last version of mysql that included status?
2b)  Is replication stable on that version? 

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

RE: Long Running Queries

[Randy Clamons]

Take a look at the docs for EXPLAIN (manual.html#IDX853). Use the 'possible_keys' 
column of the results to determine which column indexes will improve your query 
performance.

Make sure your table has a primary key. Whenever possible, use the primary key in your 
WHERE clause when you SELECT or UPDATE a single row.

You might get better results from this list if you include your table structure and 
your queries along with your question.

Randy Clamons
Systems Programming
Astro-auction.com

> Original Message
> From: "Danny Willis" <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED]
> Date: Wed, Oct-6-2004 1:02 PM
> Subject: RE: Long Running Queries
>
> What do you mean by "appropriate indexes?"
> 
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, October 06, 2004 3:43 PM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: RE: Long Running Queries
> 
> You need Appropriate Indexes on the tables. 
> 
> -Original Message-
> From: Jason Williard [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, October 07, 2004 1:11 AM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Long Running Queries
> 
> Hello,
> 
> I recently began experiencing issues that I would like some assistance
> with.
> 
> Server Details:
>   - Windows 2003
>   - MySQL Max 4.0.20a
> 
> I have a table with 450,000+ records in it.  When I try to run a single
> query, such as selecting 1 row or deleting 1 row using the WHERE 
> clause,
> the query can take forever to run.  In a specific case, it took 57
> seconds to complete the SELECT query.
> 
> Any ideas on how to speed this up?
> -- 
> 
> Thank You,
> Jason Williard


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

corruption after database restore

[Baba Buehler]

I'm having a corruption problem after doing a backup and then a restore 
with ibbackup (v1.40).

After restoring from the backup, when mysqld starts I get:
041006 07:46:53  mysqld started
InnoDB: Warning: an inconsistent page in the doublewrite buffer
InnoDB: space id 0 page number 5877102, 7'th page in dblwr buf.
InnoDB: Warning: an inconsistent page in the doublewrite buffer
InnoDB: space id 0 page number 5877103, 8'th page in dblwr buf.
InnoDB: Warning: an inconsistent page in the doublewrite buffer
InnoDB: space id 0 page number 5877104, 9'th page in dblwr buf.
InnoDB: Warning: an inconsistent page in the doublewrite buffer
InnoDB: space id 0 page number 5877105, 10'th page in dblwr buf.
InnoDB: Warning: an inconsistent page in the doublewrite buffer
InnoDB: space id 0 page number 5877106, 11'th page in dblwr buf.
InnoDB: Error: tablespace size stored in header is 6029312 pages, but
InnoDB: the sum of data file sizes is 5767168 pages
041006  7:46:54  InnoDB: Started
/usr/sbin/mysqld: ready for connections.
Version: '4.0.18-standard'  socket: '/tmp/mysql4.sock'  port: 3306

Then when queries start hitting the database, I start getting a lot of 
errors like (full backtrace included below):
 InnoDB: Assertion failure in thread 36874 in file fil0fil.c line 1204

I've restored from this backup multiple times with the same results, so 
I'm presuming its the backup itself that is corrupt.  Does anyone have 
any ideas on what might cause ibbackup to corrupt files, as the backup 
appeared to complete successfully?


Thanks,
baba

InnoDB: Error: trying to access page number 141623 in space 0
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10
041006  7:49:38  InnoDB: Assertion failure in thread 36874 in file 
fil0fil.c line 1204
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help 
diagnose
the problem, but since we have already crashed, something is definitely 
wrong
and this may fail.

key_buffer_size=1048576
read_buffer_size=4190208
max_used_connections=1
max_connections=35
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections 
= 287603 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x87b2120
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfe7df28, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8071f44 handle_segfault + 420
0x82a0e38 pthread_sighandler + 184
0x81edc77 fil_io + 1287
0x81b416b buf_read_page_low + 203
0x81b45c9 buf_read_page + 41
0x81a6fb8 buf_page_get_gen + 888
0x8167771 btr_cur_open_at_rnd_pos + 897
0x8171a4e btr_estimate_number_of_different_key_vals + 670
0x8101c99 dict_update_statistics_low + 89
0x8101d04 dict_update_statistics + 20
0x80f78f8 dict_table_get_and_increment_handle_count + 552
0x80ccc5b open__11ha_innobasePCciUi + 203
0x80c6b54 ha_open__7handlerPCcii + 36
0x8094595 openfrm__FPCcT0UiUiUiP8st_table + 5317
0x8090d27 open_unireg_entry__FP3THDP8st_tablePCcN22 + 87
0x8090178 open_table__FP3THDPCcN21Pb + 888
0x809102b open_tables__FP3THDP13st_table_list + 75
0x8091308 open_and_lock_tables__FP3THDP13st_table_list + 24
0x807ccb3 mysql_execute_command__Fv + 947
0x8080735 mysql_parse__FP3THDPcUi + 149
0x807be13 dispatch_command__F19enum_server_commandP3THDPcUi + 1443
0x807b85e do_command__FP3THD + 158
0x807b088 handle_one_connection + 648
0x829e5ec pthread_start_thread + 220
0x82c7dea thread_start + 4
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and 
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x87b2af8 = SELECT DISTINCT serial FROM alert WHERE 
resolve_time IS NULL
thd->thread_id=3
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

--
Baba Buehler - NetBotz, Inc. - <[EMAIL PROTECTED]>

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

select query does not work :(

[Alaios]

Hi we need to order a field in a select query... The
criteria is based 
on greek language... The problem  seems to be no
support for greek 
language

I can change everything in the my.cnf so tell what to
do





___
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com

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

Re: Long Running Queries

[Spenser]

I wish we had thought to have had him benchmark a query before and after
he added an index.  It would be interesting to see the difference in
actual time that an index can make on a table with 450,000 records.  

On Wed, 2004-10-06 at 15:31, Jason Williard wrote:

> Amit,
> 
> You are awesome!  That fixed it quite nicely.  Our system is screaming 
> now :-)
> 
> Thank You VERY MUCH!,
> Jason Williard
> Client Services
> 
> 
> 
> [EMAIL PROTECTED] wrote:
> > Do this and tell me if it helped
> > 
> > ALTER TABLE `asticketsdata` ADD INDEX ( `ticketidchar` ) 
> > 
> > Regards,
> > Amit
> > 
> > -Original Message-
> > From: Jason Williard [mailto:[EMAIL PROTECTED] 
> > Sent: Thursday, October 07, 2004 1:42 AM
> > To: Wadhwa, Amit
> > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> > Subject: Re: Long Running Queries
> > 
> > Thank you for your assistance with this.  Here's what you were asking
> > for.
> > 
> > Structure Output:
> > #
> > # Table structure for table 'asticketsdata'
> > #
> > 
> > CREATE TABLE asticketsdata (
> >ticketdataid int(11) NOT NULL auto_increment,
> >ticketidchar varchar(255) NOT NULL default '',
> >ticketidno int(11) default NULL,
> >contents text,
> >emailbody text,
> >PRIMARY KEY  (ticketdataid),
> >KEY ticketdata1 (ticketidno)
> > ) TYPE=MyISAM;
> > 
> > Query:
> > SELECT * FROM asticketsdata WHERE ticketidchar = 'IAZ-16393';
> > 
> > Thank You,
> > Jason Williard
> > Client Services
> > 
> > 
> > 
> > [EMAIL PROTECTED] wrote:
> > 
> >>I have tables with millions of records.
> >>
> >>1. I use Query-Caching and that speeds up all my selects/searches 
> >>after the first time, no matter how complicated the criteria.
> >>2. Build Indexes on the columns that are in your 'where' Criteria, 
> >>that will help you big time.
> >>
> >>If you post the table structure, and the query, I can help you with 
> >>your index.
> >>
> >>-Original Message-
> >>From: Jason Williard [mailto:[EMAIL PROTECTED]
> >>Sent: Thursday, October 07, 2004 1:23 AM
> >>To: Wadhwa, Amit
> >>Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> >>Subject: Re: Long Running Queries
> >>
> >>I'm sorry to sound like a novice, but could you explain that?  Are you
> > 
> > 
> >>recommending that I set the column I am searching to an Index?
> >>
> >>Thank You,
> >>Jason Williard
> >>Client Services
> >>
> >>
> >>
> >>[EMAIL PROTECTED] wrote:
> >>
> >>
> >>>You need Appropriate Indexes on the tables. 
> >>>
> >>>-Original Message-
> >>>From: Jason Williard [mailto:[EMAIL PROTECTED]
> >>>Sent: Thursday, October 07, 2004 1:11 AM
> >>>To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> >>>Subject: Long Running Queries
> >>>
> >>>Hello,
> >>>
> >>>I recently began experiencing issues that I would like some assistance
> >>
> >>
> >>>with.
> >>>
> >>>Server Details:
> >>> - Windows 2003
> >>> - MySQL Max 4.0.20a
> >>>
> >>>I have a table with 450,000+ records in it.  When I try to run a 
> >>>single query, such as selecting 1 row or deleting 1 row using the 
> >>>WHERE clause, the query can take forever to run.  In a specific case, 
> >>>it took 57 seconds to complete the SELECT query.
> >>>
> >>>Any ideas on how to speed this up?
> >>
> >>
> >>
> >>
> >>
> > 
> > 
> > 
> > 
> 

terrible time installing MySql

[Jeff]

Please help I'm have a terrible time installing MySql 
 
 
SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and `>').
SEND-PR:
From: j2d
To: [EMAIL PROTECTED]
Subject: [50 character or so descriptive subject here (for reference)]
 
>Description:

>How-To-Repeat:

>Fix:

 
>Submitter-Id:
>Originator:   Jeff
>Organization:
 
>MySQL support: [none | licence | email support | extended email support
]
>Synopsis:
>Severity:  <[ non-critical | serious | critical ] (one line)>
>Priority:   <[ low | medium | high ] (one line)>
>Category:mysql
>Class: <[ sw-bug | doc-bug | change-request | support ]
(one line)>
>Release: mysql-4.0.21-standard (Official MySQL RPM)
 
>C compiler:2.95.3
>C++ compiler:  2.95.3
>Environment:

System: Linux localhost.localdomain 2.4.20-6 #1 Thu Feb 27 10:01:19 EST
2003 i686 athlon i386 GNU/Linux
Architecture: i686
 
Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake
 
Compilation info: CC='gcc'  CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce'
CXX='gcc'  CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce
-felide-constructors -fno-exceptions -fno-rtti
'  LDFLAGS=''  ASFLAGS=''
LIBC: 
lrwxrwxrwx1 root root   13 Dec 23  2002 /lib/libc.so.6
-> libc-2.3.2.so
-rwxr-xr-x1 root root  1566428 Feb 27  2003
/lib/libc-2.3.2.so
-rw-r--r--1 root root  2321334 Feb 27  2003 /usr/lib/libc.a
-rw-r--r--1 root root  204 Feb 27  2003 /usr/lib/libc.so
Configure command: ./configure '--disable-shared'
'--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static'
'--with-server-suffix=-standard' '--without-embedded-server'
'--without-berkeley-db' '--with-innodb' '--without-vio'
'--without-openssl' '--enable-assembler' '--enable-local-infile'
'--with-mysqld-user=mysql'
'--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/'
'--with-extra-charsets=complex' '--exec-prefix=/usr'
'--libexecdir=/usr/sbin' '--libdir=/usr/lib' '--sysconfdir=/etc'
'--datadir=/usr/share' '--localstatedir=/var/lib/mysql'
'--infodir=/usr/share/info' '--includedir=/usr/include'
'--mandir=/usr/share/man' '--enable-thread-safe-client'
'--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O2 -mcpu=i486
-fno-strength-reduce' 'CXXFLAGS=-O2 -mcpu=i486 -fno-strength-reduce
-felide-constructors -fno-exceptions -fno-rtti
' 'CXX=gcc'
 

Re: Date Type Probelm

[Thomas Trutt]

Yep.. I tried a simpler query and it worked as well as working fine in 
MySQL Control Center as it is written here. The problem arose when I 
moved the DB from 4.1?? to 3.23.37, and that is when this showed up. I 
was wondering it is has to do with the way MySQL 3.23 is formating the 
date field, and was hoping there was some sort of "ToString" functions 
that could be used in the SQL statement it's self??

Again many thanks,
Tom T
Ed Lazor wrote:
Have you tried a more simple query to make sure that communication between
your application and the database is working properly?
Have you tried a variable name for 'Tick Date' that does not include spaces?
-Ed
 

-Original Message-
Hello all.. I'm running into a little bit of a problem.. I'm writing a
program in VB.NET and every time i try to load query results into my
dataset i keep getting:
/An Unhandled Exception of type 'System.InvalidOperationException'
occurred in system.data.dll
Additional information: Inconvertable type mismatch between SourceColumn
'Tick Date' of Byte[] and the Datacolumn 'Tick Date' of DateTime.
/The Query I'm using is:
/SELECT DATE_FORMAT(date,'% %M %e %Y') as 'Tick Date', SUM( IF "
(Ticktype='1',1,0)) as 'Technical', SUM( IF (Ticktype='2',1,0)) as
'Reference', SUM( IF (Ticktype='3',1,0)) as 'Directional', SUM( IF
(Ticktype='4',1,0)) as 'Search' FROM tickcount GROUP BY
DATE_FORMAT(date,'% %M %e %Y')
/ I have tried changing the datatype in the dataset definition but the
only one that works is unasignedbyte, and that returns " Byte [] Array"
instead of the date that I'm formating..
I'm using MySQL version 3.23.37, and VB.NET v.1.2
Any help would be great,
Many thanks,
Tom T
   


 

Re: Long Running Queries

[Jason Williard]

Before: 57 seconds
After: <1 second  :-D
Thank You,
Jason Williard
Client Services

Spenser wrote:
I wish we had thought to have had him benchmark a query before and after 
he added an index.  It would be interesting to see the difference in 
actual time that an index can make on a table with 450,000 records. 

On Wed, 2004-10-06 at 15:31, Jason Williard wrote:
/Amit,
You are awesome!  That fixed it quite nicely.  Our system is screaming 
now :-)

Thank You VERY MUCH!,
Jason Williard
Client Services

[EMAIL PROTECTED] wrote:
Do this and tell me if it helped
ALTER TABLE `asticketsdata` ADD INDEX ( `ticketidchar` ) 

Regards,
Amit
-Original Message-
From: Jason Williard [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 07, 2004 1:42 AM
To: Wadhwa, Amit
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Long Running Queries

Thank you for your assistance with this.  Here's what you were asking
for.
Structure Output:
#
# Table structure for table 'asticketsdata'
#
CREATE TABLE asticketsdata (
   ticketdataid int(11) NOT NULL auto_increment,
   ticketidchar varchar(255) NOT NULL default '',
   ticketidno int(11) default NULL,
   contents text,
   emailbody text,
   PRIMARY KEY  (ticketdataid),
   KEY ticketdata1 (ticketidno)
) TYPE=MyISAM;
Query:
SELECT * FROM asticketsdata WHERE ticketidchar = 'IAZ-16393';
Thank You,
Jason Williard
Client Services

[EMAIL PROTECTED] wrote:
I have tables with millions of records.
1. I use Query-Caching and that speeds up all my selects/searches 
after the first time, no matter how complicated the criteria.
2. Build Indexes on the columns that are in your 'where' Criteria, 
that will help you big time.

If you post the table structure, and the query, I can help you with 
your index.

-Original Message-
From: Jason Williard [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 07, 2004 1:23 AM
To: Wadhwa, Amit
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Long Running Queries
I'm sorry to sound like a novice, but could you explain that?  Are you

recommending that I set the column I am searching to an Index?
Thank You,
Jason Williard
Client Services

[EMAIL PROTECTED] wrote:

You need Appropriate Indexes on the tables. 

-Original Message-
From: Jason Williard [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 07, 2004 1:11 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Long Running Queries
Hello,
I recently began experiencing issues that I would like some assistance

with.
Server Details:
- Windows 2003
- MySQL Max 4.0.20a
I have a table with 450,000+ records in it.  When I try to run a 
single query, such as selecting 1 row or deleting 1 row using the 
WHERE clause, the query can take forever to run.  In a specific case, 
it took 57 seconds to complete the SELECT query.

Any ideas on how to speed this up?





/

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

Re: User Authentication

[James Weisensee]

Brandon,
You'll have to create a user for the PHP script.

example:

 

In the above code you'll have to create a user for the
PHP script (mysql_user and msyql_password) and make
sure that it has the proper permissions.  At minimum I
would think SELECT, INSERT, UPDATE and DELETE, for
that user with that password and allowed from that
host.  This is the information PHP will use when
connecting to MySQL.

HTH,
James


--- Brandon Carter <[EMAIL PROTECTED]> wrote:

> I have never set up a web site running a mysql
> server,
> so I am little fuzzy on details concerning user
> authentication.  Let's say I am creating a page
> where
> the user will enter his/her information.  I write a
> PHP script to update the mysql table when they click
> 'submit'.  Do I have to grant priveleges to anyone
> but
> myself on that table?  The script is running from my
> directory, so it's really me who is updating the
> table, and it's my authentication information that
> the
> mysql server gets, right?
> 
> Sorry if that's a totally naïve question.  Gotta
> start
> soemwhere.
> 
> --Brandon
> 
> 
>   
> ___
> Do you Yahoo!?
> Declare Yourself - Register online to vote today!
> http://vote.yahoo.com
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


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

Re: Table doesn't exist on query replication problem...

[Bismarck Rojas]

Hello, 

I suggest to you have the same version of mysql in both servers.  

regards

El Miércoles, 6 de Octubre de 2004 13:07, Bill Thomason escribió:
> Hi,
>
> I have a mysql 4.0.21 master and a 4.0.18 slave.
>
> I am trying to maintain a replication of a DB named masterdb.
>
> My goal is to have multiple slaves with a copy of masterdb.
>
> On the master I specified in my.cnf binlog-do-db=masterdb.
>
> On the slave I specified replicate-do-db=masterdb in my.cnf.
>
> I took a snap shot of masterdb as per the user documentation.  I loaded
> this on the slave server and set up the slave to point to the master
> using the changer master to ... following the proper syntax.
>
> The slave halts with the following messages in the .err file:
>
> 041006 12:06:06  Slave I/O thread: connected to master
> '[EMAIL PROTECTED]:3306',  replication started in log 'aaa-bin.008'
> at position 79
> ERROR: 1146  Table 'reporting.linkdata' doesn't exist
>
> 041006 12:06:37  Slave: Error 'Table 'reporting.linkdata' doesn't exist'
> on query 'replace into linkfilters (ip,sstatus,cstatus,dusers)  select
> ip, sum(spam), sum(total), sum(dusers)  from reporting.linkdata as a
> left join reporting.users as u on u.eid=a.eid  where
> unix_timestamp(u.lastupdate) >= 0  group by ip having sum(a.total)>=15
> and sum(a.dusers)>=6'. Default database: 'masterdb', Error_code: 1146
>
> 041006 12:06:37  Error running query, slave SQL thread aborted. Fix the
> problem, and restart the slave SQL thread with "SLAVE START". We stopped
> at log 'aaa-bin.007' position 73927
>
> On the master server there is a database named reporting that does not
> exist on the slave and should not exist because it is used by the master
> server only.
>
> >From my reading of the MySQL documentation on the binary log files
>
> works, (correct me if I am wrong.  PLEASE!) the master binary log file
> records all transactions needed to rebuild the database(s) in the event
> of a catastrophe.
>
> In the context of replication, (from a high level view) the slave server
> reaches into the master and executes transactions to update itself
> (something like 'mysqlbinlog aaa-bin.007 |mysql -u rep -h slave'),
> correct?
>
> If this assumption about replication is correct then the query:
>
> 'replace into linkfilters (ip,sstatus,cstatus,dusers)  select ip,
> sum(spam), sum(total), sum(dusers)  from reporting.linkdata as a left
> join reporting.users as u on u.eid=a.eid  where
> unix_timestamp(u.lastupdate) >= 0  group by ip having sum(a.total)>=15
> and sum(a.dusers)>=6'
>
> holds the key to my problem because reporting.linkdata and
> reporting.users do not exist on the slave since it has no concept of the
> database named reporting.
>
> Is there a way to rework this query so that either there are no
> references to the reporting database or to configure my.cnf on the
> master and slave to handle this query?
>
> Thanks in advance,
> -Bill Thomason


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

Re: Long Running Queries

[Jason Williard]

Amit,
You are awesome!  That fixed it quite nicely.  Our system is screaming 
now :-)

Thank You VERY MUCH!,
Jason Williard
Client Services

[EMAIL PROTECTED] wrote:
Do this and tell me if it helped
ALTER TABLE `asticketsdata` ADD INDEX ( `ticketidchar` ) 

Regards,
Amit
-Original Message-
From: Jason Williard [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 07, 2004 1:42 AM
To: Wadhwa, Amit
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Long Running Queries

Thank you for your assistance with this.  Here's what you were asking
for.
Structure Output:
#
# Table structure for table 'asticketsdata'
#
CREATE TABLE asticketsdata (
   ticketdataid int(11) NOT NULL auto_increment,
   ticketidchar varchar(255) NOT NULL default '',
   ticketidno int(11) default NULL,
   contents text,
   emailbody text,
   PRIMARY KEY  (ticketdataid),
   KEY ticketdata1 (ticketidno)
) TYPE=MyISAM;
Query:
SELECT * FROM asticketsdata WHERE ticketidchar = 'IAZ-16393';
Thank You,
Jason Williard
Client Services

[EMAIL PROTECTED] wrote:
I have tables with millions of records.
1. I use Query-Caching and that speeds up all my selects/searches 
after the first time, no matter how complicated the criteria.
2. Build Indexes on the columns that are in your 'where' Criteria, 
that will help you big time.

If you post the table structure, and the query, I can help you with 
your index.

-Original Message-
From: Jason Williard [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 07, 2004 1:23 AM
To: Wadhwa, Amit
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Long Running Queries
I'm sorry to sound like a novice, but could you explain that?  Are you

recommending that I set the column I am searching to an Index?
Thank You,
Jason Williard
Client Services

[EMAIL PROTECTED] wrote:

You need Appropriate Indexes on the tables. 

-Original Message-
From: Jason Williard [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 07, 2004 1:11 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Long Running Queries
Hello,
I recently began experiencing issues that I would like some assistance

with.
Server Details:
- Windows 2003
- MySQL Max 4.0.20a
I have a table with 450,000+ records in it.  When I try to run a 
single query, such as selecting 1 row or deleting 1 row using the 
WHERE clause, the query can take forever to run.  In a specific case, 
it took 57 seconds to complete the SELECT query.

Any ideas on how to speed this up?






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

RE: Long Running Queries

[Amit_Wadhwa]

Do this and tell me if it helped

ALTER TABLE `asticketsdata` ADD INDEX ( `ticketidchar` ) 

Regards,
Amit

-Original Message-
From: Jason Williard [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 07, 2004 1:42 AM
To: Wadhwa, Amit
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Long Running Queries

Thank you for your assistance with this.  Here's what you were asking
for.

Structure Output:
#
# Table structure for table 'asticketsdata'
#

CREATE TABLE asticketsdata (
   ticketdataid int(11) NOT NULL auto_increment,
   ticketidchar varchar(255) NOT NULL default '',
   ticketidno int(11) default NULL,
   contents text,
   emailbody text,
   PRIMARY KEY  (ticketdataid),
   KEY ticketdata1 (ticketidno)
) TYPE=MyISAM;

Query:
SELECT * FROM asticketsdata WHERE ticketidchar = 'IAZ-16393';

Thank You,
Jason Williard
Client Services



[EMAIL PROTECTED] wrote:
> I have tables with millions of records.
> 
> 1. I use Query-Caching and that speeds up all my selects/searches 
> after the first time, no matter how complicated the criteria.
> 2. Build Indexes on the columns that are in your 'where' Criteria, 
> that will help you big time.
> 
> If you post the table structure, and the query, I can help you with 
> your index.
> 
> -Original Message-
> From: Jason Williard [mailto:[EMAIL PROTECTED]
> Sent: Thursday, October 07, 2004 1:23 AM
> To: Wadhwa, Amit
> Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Re: Long Running Queries
> 
> I'm sorry to sound like a novice, but could you explain that?  Are you

> recommending that I set the column I am searching to an Index?
> 
> Thank You,
> Jason Williard
> Client Services
> 
> 
> 
> [EMAIL PROTECTED] wrote:
> 
>>You need Appropriate Indexes on the tables. 
>>
>>-Original Message-
>>From: Jason Williard [mailto:[EMAIL PROTECTED]
>>Sent: Thursday, October 07, 2004 1:11 AM
>>To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
>>Subject: Long Running Queries
>>
>>Hello,
>>
>>I recently began experiencing issues that I would like some assistance
> 
> 
>>with.
>>
>>Server Details:
>>  - Windows 2003
>>  - MySQL Max 4.0.20a
>>
>>I have a table with 450,000+ records in it.  When I try to run a 
>>single query, such as selecting 1 row or deleting 1 row using the 
>>WHERE clause, the query can take forever to run.  In a specific case, 
>>it took 57 seconds to complete the SELECT query.
>>
>>Any ideas on how to speed this up?
> 
> 
> 
> 
> 



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

RE: Speeding up index creation

[Amit_Wadhwa]

Use Alter table Disable Keys, before loading the Data to your system,
and then Alter table enable keys,
This way you wont have to recreate your indexes
 

-Original Message-
From: Ananth Reddy [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 06, 2004 10:08 PM
To: [EMAIL PROTECTED]
Subject: Speeding up index creation

We have MyISAM table with 150 million rows. The data is being laoded
from other system.
When I load data without indices, it is fast (30 minutes) but creating
index is taking 15 hours.
Load data with indices is taking 30 hours (worse than the other option)

I am wondering if there is a way to speed up the creation of index on a
MyISAM table.
I am specially looking for any memory parameters in config file or any
tweaking at linux level.


Appreciate any help

TIA

ananth


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

Re: Long Running Queries

[Jason Williard]

Thank you for your assistance with this.  Here's what you were asking for.
Structure Output:
#
# Table structure for table 'asticketsdata'
#
CREATE TABLE asticketsdata (
  ticketdataid int(11) NOT NULL auto_increment,
  ticketidchar varchar(255) NOT NULL default '',
  ticketidno int(11) default NULL,
  contents text,
  emailbody text,
  PRIMARY KEY  (ticketdataid),
  KEY ticketdata1 (ticketidno)
) TYPE=MyISAM;
Query:
SELECT * FROM asticketsdata WHERE ticketidchar = 'IAZ-16393';
Thank You,
Jason Williard
Client Services

[EMAIL PROTECTED] wrote:
I have tables with millions of records.
1. I use Query-Caching and that speeds up all my selects/searches after
the first time, no matter how complicated the criteria.
2. Build Indexes on the columns that are in your 'where' Criteria, that
will help you big time.
If you post the table structure, and the query, I can help you with your
index.
-Original Message-
From: Jason Williard [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 07, 2004 1:23 AM
To: Wadhwa, Amit
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Long Running Queries

I'm sorry to sound like a novice, but could you explain that?  Are you
recommending that I set the column I am searching to an Index?
Thank You,
Jason Williard
Client Services

[EMAIL PROTECTED] wrote:
You need Appropriate Indexes on the tables. 

-Original Message-
From: Jason Williard [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 07, 2004 1:11 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Long Running Queries
Hello,
I recently began experiencing issues that I would like some assistance

with.
Server Details:
 - Windows 2003
 - MySQL Max 4.0.20a
I have a table with 450,000+ records in it.  When I try to run a 
single query, such as selecting 1 row or deleting 1 row using the 
WHERE clause, the query can take forever to run.  In a specific case, 
it took 57 seconds to complete the SELECT query.

Any ideas on how to speed this up?




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

RE: Long Running Queries

[Danny Willis]

Sorry this is still new for me and I'm learning as I go.  As I become more
knowledgeable my questions will become more specific and detailed.

Next question:
If I have certain fields within tables that I will be querying often it is
beneficial to set them as indexes for speedier lookups correct?  If so, why
is that?

Thanks!
Dan

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 06, 2004 3:59 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Long Running Queries

"appropriate indexes" would mean indexes on columns specified in your
where criteria.
On this List, people mostly post table structures, as well as the
queries they are firing, or else it really doesn't help. 

-Original Message-
From: Danny Willis [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 07, 2004 1:24 AM
To: Wadhwa, Amit; [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Subject: RE: Long Running Queries

What do you mean by "appropriate indexes?"

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 06, 2004 3:43 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Long Running Queries

You need Appropriate Indexes on the tables. 

-Original Message-
From: Jason Williard [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 07, 2004 1:11 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Long Running Queries

Hello,

I recently began experiencing issues that I would like some assistance
with.

Server Details:
  - Windows 2003
  - MySQL Max 4.0.20a

I have a table with 450,000+ records in it.  When I try to run a single
query, such as selecting 1 row or deleting 1 row using the WHERE clause,
the query can take forever to run.  In a specific case, it took 57
seconds to complete the SELECT query.

Any ideas on how to speed this up?
-- 

Thank You,
Jason Williard



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



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





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

RE: show processlist state value 'statistics'

[Amit_Wadhwa]

I have seen a similar option in PHPMyAdmin which says 'Enable
Statistics' 
I suppose when the status shows statistics, mysql is just updating the
statistics in there for the query just run? 

-Original Message-
From: Ananth Reddy [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 06, 2004 11:13 PM
To: [EMAIL PROTECTED]
Subject: show processlist state value 'statistics'

Does anyone know what is the meaning of value 'statistics' in state
column of show processlist?
It is displaying this state during a SELECT query.
I noticed lot of them in DB while our application is being run in a
stress mode.
This is not documented in Mysql documentation for show processlist

TIA

ananth



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

RE: Long Running Queries

[Danny Willis]

What do you mean by "appropriate indexes?"

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 06, 2004 3:43 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Long Running Queries

You need Appropriate Indexes on the tables. 

-Original Message-
From: Jason Williard [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 07, 2004 1:11 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Long Running Queries

Hello,

I recently began experiencing issues that I would like some assistance
with.

Server Details:
  - Windows 2003
  - MySQL Max 4.0.20a

I have a table with 450,000+ records in it.  When I try to run a single
query, such as selecting 1 row or deleting 1 row using the WHERE clause,
the query can take forever to run.  In a specific case, it took 57
seconds to complete the SELECT query.

Any ideas on how to speed this up?
-- 

Thank You,
Jason Williard



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



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



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

Re: Tying records together across multiple tables.

[Stuart Felenstein]

See interspersed.
--- [EMAIL PROTECTED] wrote:

> Let's do this verbally and maybe the database
> structure will become 
> intuitively apparent
> 
> You are hosting a website that supports job seekers
> (maybe more but let's 
> concentrate on just this side of it)

Yep, there is the employer side.  Hopefully I can
easily translate my knowledge base over.
> 
> Each seeker (a User) can have a profile in the
> system

Each user (job seeker) can have 5 profiles.  


> Each profile describes who the User is (name), where
> they are (address, 
> email address) and how to contact them (main phone,
> cell phone)

Yes and no.  Profiles maybe created to target
different types of jobs.  i.e.  I'm a salesman, I've
worked in the software industry (don't laugh), but
also in the Human Resources industry.  Profile 1
reflects that I'm interested in software, Profile 2
reflects that I want to go back to HR.  Another
exmaple, I want full time work as a salesman, but
until the right spot comes along I'll take part time
work as a consultant.  
Profile 1 reflect that I'm interested in full time
sales work. Profile 2 that I want part time
consulting.  
Some of the fields are optional.  Last example - I was
a research scientist at NASA, but out of work, still
interested in science, but will take anything. Profile
1 reflects Multiple Doctorates, Profile 2 shows High
School Diploma , highest level.
Now no one is forced to enter 5, it's an option.

> Associated with each user is a list of up to 10
> industries they have 
> belonged to.

Now here is where each profile has another level to
it.  I'm a C## developer, and I've worked in the
Airlines, Hospitality and Financial Industry.  Maybe
Fiancial pays best, but I'm willing to explore other
industries, or due to an employers orientation, best
to choose as many possible industries that may result
in a match.  
So there are mulitple options on certain criteria
within each profile. Industry, Location and Pay type
(tax term, i.e. contract, full time, contract too full
time)
They may not have been associated with the industries
before or live in a particular location but they are
throwing their gloves into those areas to increase
their chance of employment.

> Associated with each user is up to 5 resumes.

Each profile has 1 resume.  5 Profiles = 5 resumes,
but each resume should be tied into the particular
profile it's part of. 

> Each user can be looking for employment in any of 5
> locations

Well 2, 5 , haven't made my mind up, but it will be
multiple

> Each user can be looking for employment in any of 5
> industries

See above and way above.  Yes, multiple industries.  


> Each user can look for employment under up to 5
> different job titles.

Slight different here.  There is a "current" or most
recent , a desired one, maybe the same as current.
Also an alternate.
> 
> Please take the time to correct me and fill in any
> gaps. Please DO NOT  attempt to make any data 
> definitions at this stage.
> I need to understand 
> what data you are going to maintain verbally before
> I can help you 
> translate it into storage requirements.

Up to you, only a suggestion if you haven't seen a job
board or looked at one closer. Dice.com , Monster,
CareerBuilders, all have pretty much the same
standard.  
Essentially , the more a user fills in on their
profile, the more they answer, better the chances of
finding opportunities.
At the same time, the more field information avaiable
about users/job seekers, the more refined a search can
be before resumes are text searched or pulled up.
Hopefully now my original question about tying all the
responses / data from one profile together is
important.

Let me know if this is clearer.
Thank you.
Stuart
-

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

Re: Tying records together across multiple tables.

[Stuart Felenstein]

--- [EMAIL PROTECTED] wrote:

> Let's do this verbally and maybe the database
> structure will become 
> intuitively apparent
> 
> You are hosting a website that supports job seekers
> (maybe more but let's 
> concentrate on just this side of it)
> 
> Each seeker (a User) can have a profile in the
> system
> Each profile describes who the User is (name), where
> they are (address, 
> email address) and how to contact them (main phone,
> cell phone)
> Associated with each user is a list of up to 10
> industries they have 
> belonged to.
> Associated with each user is up to 5 resumes.
> Each user can be looking for employment in any of 5
> locations
> Each user can be looking for employment in any of 5
> industries
> Each user can look for employment under up to 5
> different job titles.
> 
> Please take the time to correct me and fill in any
> gaps. Please DO NOT 
> attempt to make any data definitions at this stage.
> I need to understand 
> what data you are going to maintain verbally before
> I can help you 
> translate it into storage requirements.
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 
> Stuart Felenstein <[EMAIL PROTECTED]> wrote on
> 10/06/2004 02:47:44 PM:
> 
> > Shawn, 
> > 
> > When I read your examples, it is clear as a bell. 
> > When I try to map it into my situation it becomes
> more
> > difficult to clarify :)
> > Using other job boards as examples, there are a
> couple
> > of ways to go.
> > Most of the bigger players offer job seekers the
> > ability to store 5 resumes online.  These are not
> just
> > the actual resume though and are part of a
> "profile". 
> >5 profiles can be targeting or positioning the
> job
> > seeker to 5 different types, jobs, locations. 
> > 
> > Method 1:
> > User Table: MemberID, username, password,.
> > 
> > Profile_Table: RecordID, MemberID, locationfield,
> > jobtitle field
> > 
> > Industry_Table: MemberID, LocationID, ProfileID
> > 
> > User_Profile_Table: MemberID, ProfileID,
> LocationID
> > 
> > Then I wonder about the "job_titles" field.
> > There is a "current" job title, a "seeking" job
> title
> > and an alternate job title.
> > 
> > Do I implement: 
> > JT_Current_Table
> > JT_Seeking_Table
> > JT_Alternate_Table
> > 
> > Same thing with inudstries. Database designers can
> > work in many industries,so can other workers.
> Users
> > can choose 10 inudstries:
> > 
> > Is there a need to have 
> > Industry_1_Table
> > Industry_2_Table
> > Industry_3_Table
> > Industry_4_Table.
> > 
> > All I can say is I believe this would be a better
> > design, but I can't get passed thinking, what for
> ?
> > Why not 
> > 
> > Profile_Table:
> > RecordID:
> > MemberID:
> > Industry1
> > Industry2
> > 
> > JobTitle1
> > JobTitle2
> > ..
> > 
> > Like I say at the start here, difficult to apply
> to my
> > situation.  Maybe because it doesn't apply, but at
> the
> > same time I can't see the implications of doing it
> via
> > the last method.
> > 
> > So, as painful as it's been for you with me, I'll
> ask
> > if you see potential problems or what the issues
> could
> > be to point them out to me.
> > 
> > Thank you,
> > Stuart
> > 
> > 
> > 
> > 
> > 
> > --- [EMAIL PROTECTED] wrote:
> > 
> > > (I guess this means it's  example-counterexample
> > > time)
> > > 
> > 
> 


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

Re: Long Running Queries

[Michael Stassen]

Not without more information.  At the very least, we need to see the query. 
 Better yet, show us what EXPLAIN has to say about your query 
.

Michael
Jason Williard wrote:
Hello,
I recently began experiencing issues that I would like some assistance 
with.

Server Details:
 - Windows 2003
 - MySQL Max 4.0.20a
I have a table with 450,000+ records in it.  When I try to run a single 
query, such as selecting 1 row or deleting 1 row using the WHERE clause, 
the query can take forever to run.  In a specific case, it took 57 
seconds to complete the SELECT query.

Any ideas on how to speed this up?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Long Running Queries

[Amit_Wadhwa]

"appropriate indexes" would mean indexes on columns specified in your
where criteria.
On this List, people mostly post table structures, as well as the
queries they are firing, or else it really doesn't help. 

-Original Message-
From: Danny Willis [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 07, 2004 1:24 AM
To: Wadhwa, Amit; [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]
Subject: RE: Long Running Queries

What do you mean by "appropriate indexes?"

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 06, 2004 3:43 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Long Running Queries

You need Appropriate Indexes on the tables. 

-Original Message-
From: Jason Williard [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 07, 2004 1:11 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Long Running Queries

Hello,

I recently began experiencing issues that I would like some assistance
with.

Server Details:
  - Windows 2003
  - MySQL Max 4.0.20a

I have a table with 450,000+ records in it.  When I try to run a single
query, such as selecting 1 row or deleting 1 row using the WHERE clause,
the query can take forever to run.  In a specific case, it took 57
seconds to complete the SELECT query.

Any ideas on how to speed this up?
-- 

Thank You,
Jason Williard



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



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




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

Weirdness (or bug) with DROP TABLE

[Adolfo Bello]

I am using Mandrake 10, MySQL 4.1.5 from RPM downloaded from
dev.mysql.com.

Look at this session:



$ mysql -u root -p permarn
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.5-gamma-standard

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

mysql> select * from tblinstructivos;
Empty set (0.00 sec)

mysql> drop table tblinstructivos;
ERROR 1051 (42S02): Unknown table 'tblinstructivos'
mysql> select * from `tblinstructivos`;
Empty set (0.00 sec)

mysql> drop table `tblinstructivos`;
ERROR 1051 (42S02): Unknown table 'tblinstructivos'
mysql>

=

Why I can not drop this particular table? No problem with any other
table.

Every table in this database is InnoDB. I started mysql with the
"--user=root" flag.


Adolfo


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

Re: Long Running Queries

[Jason Williard]

I'm sorry to sound like a novice, but could you explain that?  Are you 
recommending that I set the column I am searching to an Index?

Thank You,
Jason Williard
Client Services

[EMAIL PROTECTED] wrote:
You need Appropriate Indexes on the tables. 

-Original Message-
From: Jason Williard [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 07, 2004 1:11 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Long Running Queries

Hello,
I recently began experiencing issues that I would like some assistance
with.
Server Details:
  - Windows 2003
  - MySQL Max 4.0.20a
I have a table with 450,000+ records in it.  When I try to run a single
query, such as selecting 1 row or deleting 1 row using the WHERE clause,
the query can take forever to run.  In a specific case, it took 57
seconds to complete the SELECT query.
Any ideas on how to speed this up?

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

Re: Long Running Queries

[Ted Byrne]

Is the table indexed in any way?
At 03:40 PM 10/6/2004, you wrote:
Hello,
I recently began experiencing issues that I would like some assistance with.
Server Details:
 - Windows 2003
 - MySQL Max 4.0.20a
I have a table with 450,000+ records in it.  When I try to run a single 
query, such as selecting 1 row or deleting 1 row using the WHERE clause, 
the query can take forever to run.  In a specific case, it took 57 seconds 
to complete the SELECT query.

Any ideas on how to speed this up?
--
Thank You,
Jason Williard

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

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

RE: Long Running Queries

[Amit_Wadhwa]

You need Appropriate Indexes on the tables. 

-Original Message-
From: Jason Williard [mailto:[EMAIL PROTECTED] 
Sent: Thursday, October 07, 2004 1:11 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Long Running Queries

Hello,

I recently began experiencing issues that I would like some assistance
with.

Server Details:
  - Windows 2003
  - MySQL Max 4.0.20a

I have a table with 450,000+ records in it.  When I try to run a single
query, such as selecting 1 row or deleting 1 row using the WHERE clause,
the query can take forever to run.  In a specific case, it took 57
seconds to complete the SELECT query.

Any ideas on how to speed this up?
-- 

Thank You,
Jason Williard



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



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

Long Running Queries

[Jason Williard]

Hello,
I recently began experiencing issues that I would like some assistance with.
Server Details:
 - Windows 2003
 - MySQL Max 4.0.20a
I have a table with 450,000+ records in it.  When I try to run a single 
query, such as selecting 1 row or deleting 1 row using the WHERE clause, 
the query can take forever to run.  In a specific case, it took 57 
seconds to complete the SELECT query.

Any ideas on how to speed this up?
--
Thank You,
Jason Williard

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

Re: update MySQL

[Jeff Smelser]

On Wednesday 06 October 2004 02:10 pm, Scott Hamm wrote:

> Like I said before it "seems" to group by "threads".  Therefore, it is
> close enough.

Right! thats why Microsoft thrives. Because as long as it appears to work, its 
all good.. :)

Jeff


pgpAXe4d5h6QD.pgp
Description: PGP signature

Re: Tying records together across multiple tables.

[SGreen]

Let's do this verbally and maybe the database structure will become 
intuitively apparent

You are hosting a website that supports job seekers (maybe more but let's 
concentrate on just this side of it)

Each seeker (a User) can have a profile in the system
Each profile describes who the User is (name), where they are (address, 
email address) and how to contact them (main phone, cell phone)
Associated with each user is a list of up to 10 industries they have 
belonged to.
Associated with each user is up to 5 resumes.
Each user can be looking for employment in any of 5 locations
Each user can be looking for employment in any of 5 industries
Each user can look for employment under up to 5 different job titles.

Please take the time to correct me and fill in any gaps. Please DO NOT 
attempt to make any data definitions at this stage. I need to understand 
what data you are going to maintain verbally before I can help you 
translate it into storage requirements.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Stuart Felenstein <[EMAIL PROTECTED]> wrote on 10/06/2004 02:47:44 PM:

> Shawn, 
> 
> When I read your examples, it is clear as a bell. 
> When I try to map it into my situation it becomes more
> difficult to clarify :)
> Using other job boards as examples, there are a couple
> of ways to go.
> Most of the bigger players offer job seekers the
> ability to store 5 resumes online.  These are not just
> the actual resume though and are part of a "profile". 
>5 profiles can be targeting or positioning the job
> seeker to 5 different types, jobs, locations. 
> 
> Method 1:
> User Table: MemberID, username, password,.
> 
> Profile_Table: RecordID, MemberID, locationfield,
> jobtitle field
> 
> Industry_Table: MemberID, LocationID, ProfileID
> 
> User_Profile_Table: MemberID, ProfileID, LocationID
> 
> Then I wonder about the "job_titles" field.
> There is a "current" job title, a "seeking" job title
> and an alternate job title.
> 
> Do I implement: 
> JT_Current_Table
> JT_Seeking_Table
> JT_Alternate_Table
> 
> Same thing with inudstries. Database designers can
> work in many industries,so can other workers. Users
> can choose 10 inudstries:
> 
> Is there a need to have 
> Industry_1_Table
> Industry_2_Table
> Industry_3_Table
> Industry_4_Table.
> 
> All I can say is I believe this would be a better
> design, but I can't get passed thinking, what for ?
> Why not 
> 
> Profile_Table:
> RecordID:
> MemberID:
> Industry1
> Industry2
> 
> JobTitle1
> JobTitle2
> ..
> 
> Like I say at the start here, difficult to apply to my
> situation.  Maybe because it doesn't apply, but at the
> same time I can't see the implications of doing it via
> the last method.
> 
> So, as painful as it's been for you with me, I'll ask
> if you see potential problems or what the issues could
> be to point them out to me.
> 
> Thank you,
> Stuart
> 
> 
> 
> 
> 
> --- [EMAIL PROTECTED] wrote:
> 
> > (I guess this means it's  example-counterexample
> > time)
> > 
> 

Re: update MySQL

[Jim Winstead]

Hey folks.

Apparently I need to say it again: this discussion is off-topic for this
mailing list. Please either let it die or take the discussion off-list.

Thanks.

Jim Winstead
MySQL Inc.

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

RE: update MySQL

[Scott Hamm]

Like I said before it "seems" to group by "threads".  Therefore, it is close
enough.

-Original Message-
From: Michael Satterwhite [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 06, 2004 3:05 PM
To: [EMAIL PROTECTED]
Subject: Re: update MySQL


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Tuesday 05 October 2004 14:39, Scott Hamm wrote:
> Jeff,
>
>   If you sort it by conversation topic, then it will seem to group by
> "threads".
> I'm running Outlook 2000.

Not the same thing. Threading uses the "In-reply-to" header. Messages will 
stay in the correct thread even if the subject changes - which is why it 
makes a difference.

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFBZEH2jeziQOokQnARAmKiAJ4ww4xc4BOGxnOoXaXlNPxOYvM18gCfVQy1
E+90ewHDuSc7AKQGkjBzIEc=
=1mEs
-END PGP SIGNATURE-

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

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

Table doesn't exist on query replication problem...

[Bill Thomason]

Hi,

I have a mysql 4.0.21 master and a 4.0.18 slave.

I am trying to maintain a replication of a DB named masterdb.

My goal is to have multiple slaves with a copy of masterdb.

On the master I specified in my.cnf binlog-do-db=masterdb.

On the slave I specified replicate-do-db=masterdb in my.cnf.

I took a snap shot of masterdb as per the user documentation.  I loaded
this on the slave server and set up the slave to point to the master
using the changer master to ... following the proper syntax.

The slave halts with the following messages in the .err file:

041006 12:06:06  Slave I/O thread: connected to master 
'[EMAIL PROTECTED]:3306',  replication started in log 'aaa-bin.008'
at position 79
ERROR: 1146  Table 'reporting.linkdata' doesn't exist

041006 12:06:37  Slave: Error 'Table 'reporting.linkdata' doesn't exist'
on query 'replace into linkfilters (ip,sstatus,cstatus,dusers)  select
ip, sum(spam), sum(total), sum(dusers)  from reporting.linkdata as a
left join reporting.users as u on u.eid=a.eid  where
unix_timestamp(u.lastupdate) >= 0  group by ip having sum(a.total)>=15
and sum(a.dusers)>=6'. Default database: 'masterdb', Error_code: 1146

041006 12:06:37  Error running query, slave SQL thread aborted. Fix the
problem, and restart the slave SQL thread with "SLAVE START". We stopped
at log 'aaa-bin.007' position 73927

On the master server there is a database named reporting that does not
exist on the slave and should not exist because it is used by the master
server only.

>From my reading of the MySQL documentation on the binary log files
works, (correct me if I am wrong.  PLEASE!) the master binary log file
records all transactions needed to rebuild the database(s) in the event
of a catastrophe.

In the context of replication, (from a high level view) the slave server
reaches into the master and executes transactions to update itself
(something like 'mysqlbinlog aaa-bin.007 |mysql -u rep -h slave'),
correct?

If this assumption about replication is correct then the query:

'replace into linkfilters (ip,sstatus,cstatus,dusers)  select ip,
sum(spam), sum(total), sum(dusers)  from reporting.linkdata as a left
join reporting.users as u on u.eid=a.eid  where
unix_timestamp(u.lastupdate) >= 0  group by ip having sum(a.total)>=15
and sum(a.dusers)>=6' 

holds the key to my problem because reporting.linkdata and
reporting.users do not exist on the slave since it has no concept of the
database named reporting.

Is there a way to rework this query so that either there are no
references to the reporting database or to configure my.cnf on the
master and slave to handle this query?

Thanks in advance,
-Bill Thomason 


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

Re: update MySQL

[Michael Satterwhite]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Tuesday 05 October 2004 14:39, Scott Hamm wrote:
> Jeff,
>
>   If you sort it by conversation topic, then it will seem to group by
> "threads".
> I'm running Outlook 2000.

Not the same thing. Threading uses the "In-reply-to" header. Messages will 
stay in the correct thread even if the subject changes - which is why it 
makes a difference.

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFBZEH2jeziQOokQnARAmKiAJ4ww4xc4BOGxnOoXaXlNPxOYvM18gCfVQy1
E+90ewHDuSc7AKQGkjBzIEc=
=1mEs
-END PGP SIGNATURE-

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

Re: update MySQL

[Michael Satterwhite]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Tuesday 05 October 2004 14:23, David Brodbeck wrote:
> Well, that's nice...
>
> I just don't see what difference it makes.  As far as I can see, the
> outcome is identical either way...

If the recipients email program threads messages, it makes a big difference.

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFBZEG6jeziQOokQnARAq2qAJ9dHUqz+mmTGTNp7xRiu4hZipIBcwCgpfxX
lnNkjyvux/GUi/pZCgNzmJ8=
=f0bF
-END PGP SIGNATURE-

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

Re: User Authentication

[SGreen]

Try to imagine your PHP code as a "middleman" or "clerk" working a walk-up 
window for some kind of office. 

It is that clerk's job to handle incoming requests, file the correct 
paperwork, hand out a receipts, perform searches, and hand out the 
requested information.  Your clerk (the application) checks to see if a 
visitor (a web user) is already registered and gives every new person to 
the office (your site) a blank form to fill out. When the visitor turns in 
the form (hits submit) the clerk (your application) transfers the 
information into the correct places (database tables) so that the user's 
information is now stored for future reference.

On a return visit, the clerk (your application) asks the visitor if they 
are new or already in the system. If they say they are already registered, 
the clerk asks them to provide some kind of secret information and 
compares their information with what is on file (your application requests 
username and password and compares it against what is stored in the 
database). If everything checks out, the visitor is then allowed to make a 
request of the clerk (use the protected area of your web site)

Your PHP code needs permission to use the database so that it can move 
data into and out of certain tables. Your application is what maintains 
each user's credentials in whatever tables you see fit to design.  Access 
to the database is controlled by database permissions. You application 
needs permission (not each user) to do whatever you need it to do with 
whatever tables you design.

May I suggest some reading:
http://dev.mysql.com/doc/mysql/en/Privilege_system.html
Start with that and follow the links. If you run into problems with the 
GRANT/REVOKE statements (if that is the method you choose) come back to 
the list and we can help you set it up.

Keeping login information secure and authenticating users through a web 
interface is a subject unto itself. Search the web and I will bet you find 
several hundred methods. Use whatever method you think fits your needs.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

PS - It is always better if you CC the list on all responses as I could 
have been unavailable for an extended period and anyone else on the list 
could have picked up your question and answered it.


Brandon Carter <[EMAIL PROTECTED]> wrote on 10/06/2004 02:40:03 PM:

> 
> Thank you for the thorough reply. Allow me to clarify.
> 
> Go to
> http://meta.wikimedia.org/wiki/The_provisional_portal_of_Wikipedia.
>  This is, as any other Wiki, an open source project
> where anyone from anywhere without any sort of
> credentials whatsoever has the permission to write to
> the website.  All of the information is stored in a
> MySQL database.  So, the user fills in a form, clicks
> submit, and the information is sent to the database. 
> The user has no need to log in or anything.
> 
> Taking a simpler example, a website that sends coupons
> to a user.  The user must first sign up to get the
> coupons.  So, the user goes to the web site, fills in
> a form, that user information is added to the
> database.
> 
> The person who fills in this form is anonymous to
> MySQL.  When he hits the submit button, his
> information is put in the system without him every
> entering a password for access.
> 
> So, is it the PHP script (or whatever script) that has
> to have permission to access the database?  If not,
> what permissions need to be set on the database to
> allow unfettered updating permission for the user?
> 
> The user never sees the structure of the database, and
> is not permitted to see or change other users'
> information.  So, how are the permitted to put their
> information in the database?
> 
> 
> 
> 
> --- [EMAIL PROTECTED] wrote:
> 
> > Security is built in shells (much like onions). 
> > 
> > 
> > Application layer-
> > This is where your user interacts with your code. In
> > your case, I believe 
> > that you have a web server running code you wrote in
> > PHP responding to 
> > user requests.  Your web server uses a user account
> > to interact with the 
> > operating system. Your user must authenticate with
> > the web server (even if 
> > they authenticate as "anonymous") in order for the
> > server to know what 
> > pages that visitor has rights to see. Some web
> > servers check user 
> > credentials against a list of users they maintain,
> > others allow your users 
> > to use a system login (if they have one)
> > 
> > Your application can also maintain a set of login
> > credentials for the 
> > users of your site. You can chose to inherit the
> > username from the Web 
> > Server, the Operating system, or from an
> > authentication process written 
> > specifically for you application. This is the step
> > where your application 
> > verifies that the current user is authorized to use
> > your application. Your 
> > application itself has several options for how it is
> > recognized by the 
> > operating system.  Since 

Re: Tying records together across multiple tables.

[Stuart Felenstein]

Shawn, 

When I read your examples, it is clear as a bell. 
When I try to map it into my situation it becomes more
difficult to clarify :)
Using other job boards as examples, there are a couple
of ways to go.
Most of the bigger players offer job seekers the
ability to store 5 resumes online.  These are not just
the actual resume though and are part of a "profile". 
   5 profiles can be targeting or positioning the job
seeker to 5 different types, jobs, locations.  

Method 1:
User Table: MemberID, username, password,.

Profile_Table: RecordID, MemberID, locationfield,
jobtitle field

Industry_Table: MemberID, LocationID, ProfileID

User_Profile_Table: MemberID, ProfileID, LocationID

Then I wonder about the "job_titles" field.
There is a "current" job title, a "seeking" job title
and an alternate job title.

Do I implement: 
JT_Current_Table
JT_Seeking_Table
JT_Alternate_Table

Same thing with inudstries. Database designers can
work in many industries,so can other workers. Users
can choose 10 inudstries:

Is there a need to have 
Industry_1_Table
Industry_2_Table
Industry_3_Table
Industry_4_Table.

All I can say is I believe this would be a better
design, but I can't get passed thinking, what for ?
Why not 

Profile_Table:
RecordID:
MemberID:
Industry1
Industry2

JobTitle1
JobTitle2
..

Like I say at the start here, difficult to apply to my
situation.  Maybe because it doesn't apply, but at the
same time I can't see the implications of doing it via
the last method.

So, as painful as it's been for you with me, I'll ask
if you see potential problems or what the issues could
be to point them out to me.

Thank you,
Stuart





--- [EMAIL PROTECTED] wrote:

> (I guess this means it's  example-counterexample
> time)
> 


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

Re: update MySQL

[Michael Satterwhite]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Tuesday 05 October 2004 15:18, Ed Lazor wrote:

> Also, back to my original question, what are you seeing that denotes the
> difference between whether I reply or create a new message when starting a
> new topic?  After all, I took care of changing the recipient list and the
> subject field.  Is header information different?  Does your email client
> sort or group messages differently?

I haven't been following this, but I can make a good guess.

Some email clients can thread messages - my client (KMail) is one of them. If 
you hit reply, your message gets placed in the thread that you replied to. It 
is *NOT* based on the subject, it uses the "In-Reply-To" header. If you enter 
a new message, it begins a new thread.

If it is really a new message, it is out of context to put it into an existing 
thread. If it is really a reply, it may be lost in a new thread.

Because of threaded emails, it is considered bad practice to start new threads 
by a reply - or use "New" to reply to a thread. I won't claim I've never done 
it, but it is bad practice.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFBZC6mjeziQOokQnARAp5wAJ9QehvPkNRVwKGgUkAjAm7jkQfRowCgrWaH
BwWO1G8aButW0ejjh3P2O4w=
=EkBR
-END PGP SIGNATURE-

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

Connection Errors

[Dave Goodrich]

[Previously posted to MySQL forum]
Howdy the group,
I have a cluster of email servers all using mysql for authentication. 
Using vpopmail and spamassassin, all auth requests, pop, delivery 
instructions, etc, go to mysql. Currently we process between 50k and 65k 
messages a day inbound. Each delivery requires a query, and each pop to 
download messages requires a query. Approximately 50% of those messages 
will also require a query for spamassassin preferences.

I am having complaints from users that they are reprompted for their 
password several times a day. So, I am trying to see if I can identify 
the failure from the host end.

1) I have restarted safe_mysqld with the warning option "--warnings". I 
can see the server.log shows aborted client errors, but not aborted 
connection errors. How do I get the aborted connection errors to display 
in the logs? ( see approx 30 client errors a day, but in excess of 2500 
connection errors)

2) I have read many places that there is potential for communication 
errors due to ether settings. I currently have the host running 1000fdx 
to a 1000fdx port on the switch, the clients are running 100fdx to a 
100fdx port on the same switch. Should I be running both host and 
clients at the same speed?

3) In my mail logs I have failures logged which state that the user 
doesn't exist. From the vpopmail maillist, this means the connection 
failed (provided the user does in fact exist).

Any advice, help, would be appreciated. Thanks,
DAve

Some background,
### Host server: Sun Enterprise 450, 2 gb ram. 1000fdx connection to a 
1000fdx port on a netgear switch.

# mysqladmin -uroot -p version
mysqladmin Ver 8.23 Distrib 3.23.53, for sun-solaris2.8 on sparc
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 3.23.53-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 2 days 11 hours 21 min 9 sec
Threads: 1 Questions: 1591388 Slow queries: 24 Opens: 0 Flush tables: 1 
Open tables: 7 Queries per second avg: 7.448

### /etc/my.cnf
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=256M
set-variable = max_allowed_packet=16M
set-variable = table_cache=256
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=4
set-variable = connect_timeout=15
set-variable = back_log=50
set-variable = max_connections=500
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=4
server-id = 1
# lets have a bin log
#log-bin
# Lets have a query log as well
log
# Lets save slow queries
set-variable = long_query_time=20
log-long-format
log-slow-queries = /usr/local/mysql/var/slow_queries.log
## Status after 30 minutes running
#mysqladmin -uroot -p extended-status
+--+-+
| Variable_name | Value |
+--+-+
| Aborted_clients | 0 |
| Aborted_connects | 51 | <- can I log these?
| Bytes_received | 1892430 |
| Bytes_sent | 2955038 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 6356 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 3 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 4025 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 6932 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 7 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 6 |
| Connections | 471898 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 8014 |
| Handler_read_next | 1651 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 4102 |
| Handler_read_rnd_next | 13010 |
| Handler_u

Re: User Authentication

[SGreen]

Security is built in shells (much like onions). 


Application layer-
This is where your user interacts with your code. In your case, I believe 
that you have a web server running code you wrote in PHP responding to 
user requests.  Your web server uses a user account to interact with the 
operating system. Your user must authenticate with the web server (even if 
they authenticate as "anonymous") in order for the server to know what 
pages that visitor has rights to see. Some web servers check user 
credentials against a list of users they maintain, others allow your users 
to use a system login (if they have one)

Your application can also maintain a set of login credentials for the 
users of your site. You can chose to inherit the username from the Web 
Server, the Operating system, or from an authentication process written 
specifically for you application. This is the step where your application 
verifies that the current user is authorized to use your application. Your 
application itself has several options for how it is recognized by the 
operating system.  Since your application is "hosted" by your web server, 
it (the server) has the option of either starting your application as a 
child process of itself (meaning that your application has the same user 
rights as your web server) or as a stand-alone process (your application 
needs its own operating system account complete with its own set of 
permissions)

Data Access layer - 
This is where the database server handles requests for data and 
connections. Connections can come from just about anywhere: web servers, 
php applications, other external programs (like the MySQL client, or 
another MySQL server), etc.).  Before a client (a client is anything that 
needs a connection) is permitted to connect to the database server, that 
client must first prove to the server that it is permitted to make a 
connection. It does this by validating a username and password with the 
server. Once the connection is established, all rights, privileges, and 
restrictions are now in effect for the account that was used to establish 
the connection.

Operating system layer - 
Any program that needs CPU time, access to files, or access to memory must 
authenticate itself to the operating system before it can run. The 
operating system has the last word when it comes to permissions. If an 
applications "user" account does not have the correct privileges to do 
what it wants to do (like read a file from a certain directory) the 
operating system says "no" and errors abound.

So, when you mention "user authentication" it makes me wonder
1 - Are you trying to let the user see your web pages (Web server settings 
and maybe OS permissions, too)
2 - Are you trying to let your application know who a visitor is (comes 
from either web server information or application information or both)
3 - Are you trying to make a PHP connection to a database server. (MySQL 
user setting + PHP connection code. This is almost always different your 
OS user information)

I think what you are running into is the 3rd issue because you seem to 
think that PHP may be logging into the database (creating a database 
connection) with your OS credentials. While I believe it is possible to 
script that, I don't think that is the default behavior. You should 
probably review the PHP function that you are using to create your 
connection to MySQL (mysql_connect()) and review the parameters it takes. 

You may need to create a new MySQL account, modify an existing MySQL 
account, GRANT privileges to an account to the tables it needs access to, 
and/or  use the correct MySQL account in the mysql_connect() function. 

DISCLAIMER - Different web servers operate differently and expose 
different security APIs so your mileage may vary.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Brandon Carter <[EMAIL PROTECTED]> wrote on 10/06/2004 01:02:02 PM:

> I have never set up a web site running a mysql server,
> so I am little fuzzy on details concerning user
> authentication.  Let's say I am creating a page where
> the user will enter his/her information.  I write a
> PHP script to update the mysql table when they click
> 'submit'.  Do I have to grant priveleges to anyone but
> myself on that table?  The script is running from my
> directory, so it's really me who is updating the
> table, and it's my authentication information that the
> mysql server gets, right?
> 
> Sorry if that's a totally naïve question.  Gotta start
> soemwhere.
> 
> --Brandon
> 
> 
> 
> ___
> Do you Yahoo!?
> Declare Yourself - Register online to vote today!
> http://vote.yahoo.com
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 

RE: Date Type Probelm

[Ed Lazor]

Have you tried a more simple query to make sure that communication between
your application and the database is working properly?

Have you tried a variable name for 'Tick Date' that does not include spaces?

-Ed


> -Original Message-
> Hello all.. I'm running into a little bit of a problem.. I'm writing a
> program in VB.NET and every time i try to load query results into my
> dataset i keep getting:
> 
> /An Unhandled Exception of type 'System.InvalidOperationException'
> occurred in system.data.dll
> 
> Additional information: Inconvertable type mismatch between SourceColumn
> 'Tick Date' of Byte[] and the Datacolumn 'Tick Date' of DateTime.
> 
> /The Query I'm using is:
> 
> /SELECT DATE_FORMAT(date,'% %M %e %Y') as 'Tick Date', SUM( IF "
> (Ticktype='1',1,0)) as 'Technical', SUM( IF (Ticktype='2',1,0)) as
> 'Reference', SUM( IF (Ticktype='3',1,0)) as 'Directional', SUM( IF
> (Ticktype='4',1,0)) as 'Search' FROM tickcount GROUP BY
> DATE_FORMAT(date,'% %M %e %Y')
> 
> / I have tried changing the datatype in the dataset definition but the
> only one that works is unasignedbyte, and that returns " Byte [] Array"
> instead of the date that I'm formating..
> I'm using MySQL version 3.23.37, and VB.NET v.1.2
> 
> Any help would be great,
> 
> Many thanks,
> 
> Tom T


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

RE: User Authentication

[Ed Lazor]

Are you talking about user authentication for the purpose of people
accessing the MySQL database or are you talking about user authentication in
terms of people accessing restricted areas on a website?


> -Original Message-
> I have never set up a web site running a mysql server,
> so I am little fuzzy on details concerning user
> authentication.  Let's say I am creating a page where
> the user will enter his/her information.  I write a
> PHP script to update the mysql table when they click
> 'submit'.  Do I have to grant priveleges to anyone but
> myself on that table?  The script is running from my
> directory, so it's really me who is updating the
> table, and it's my authentication information that the
> mysql server gets, right?
> 
> Sorry if that's a totally naove question.  Gotta start
> soemwhere.
> 
> --Brandon


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

show processlist state value 'statistics'

[Ananth Reddy]

Does anyone know what is the meaning of value 'statistics' in state column of show 
processlist?
It is displaying this state during a SELECT query.
I noticed lot of them in DB while our application is being run in a stress mode.
This is not documented in Mysql documentation for show processlist

TIA

ananth

User Authentication

[Brandon Carter]

I have never set up a web site running a mysql server,
so I am little fuzzy on details concerning user
authentication.  Let's say I am creating a page where
the user will enter his/her information.  I write a
PHP script to update the mysql table when they click
'submit'.  Do I have to grant priveleges to anyone but
myself on that table?  The script is running from my
directory, so it's really me who is updating the
table, and it's my authentication information that the
mysql server gets, right?

Sorry if that's a totally naïve question.  Gotta start
soemwhere.

--Brandon



___
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com

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

Date Type Probelm

[Thomas Trutt]

Hello all.. I'm running into a little bit of a problem.. I'm writing a 
program in VB.NET and every time i try to load query results into my 
dataset i keep getting:

/An Unhandled Exception of type 'System.InvalidOperationException' 
occurred in system.data.dll

Additional information: Inconvertable type mismatch between SourceColumn 
'Tick Date' of Byte[] and the Datacolumn 'Tick Date' of DateTime.

/The Query I'm using is:
/SELECT DATE_FORMAT(date,'% %M %e %Y') as 'Tick Date', SUM( IF " 
(Ticktype='1',1,0)) as 'Technical', SUM( IF (Ticktype='2',1,0)) as 
'Reference', SUM( IF (Ticktype='3',1,0)) as 'Directional', SUM( IF 
(Ticktype='4',1,0)) as 'Search' FROM tickcount GROUP BY 
DATE_FORMAT(date,'% %M %e %Y')

/ I have tried changing the datatype in the dataset definition but the 
only one that works is unasignedbyte, and that returns " Byte [] Array" 
instead of the date that I'm formating..
I'm using MySQL version 3.23.37, and VB.NET v.1.2

Any help would be great,
Many thanks,
Tom T

Speeding up index creation

[Ananth Reddy]

We have MyISAM table with 150 million rows. The data is being laoded from other system.
When I load data without indices, it is fast (30 minutes) but creating index is taking 
15 hours.
Load data with indices is taking 30 hours (worse than the other option)

I am wondering if there is a way to speed up the creation of index on a MyISAM table.
I am specially looking for any memory parameters in config file or any tweaking at 
linux level.


Appreciate any help

TIA

ananth

Benchmaking queries...

[m . muller]

Hi MySql freaks !

Does someone know if it exists a more precise way to compare two queries on an
other base than the time to process the request?
For example, is it possible to know the numbers of flops needed or memory
allocated to process the query ?

Thanks in advance!

Michael

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

mysql embedded with TCP support

[roland]

Hello

I am a newbie @ mysql so excuse me in advance for silly questions.

I am preparing to develop an embedded application on a PC104 board with linux.

I was hoping to use mysql embedded but have disovered in the documentation 
that it cannot be accessed via TCP from another process. 

I suppose unless the above is an outdated fact that I will ahve to install 
MySQL server on the board access it from the local application with the C API 
for client applications and can then also access it from other hosts via TCP. 

There are two (major) problems I face however:

1- I only have 16MB flash to install it on of which I require at least 8 for 
other applications. How small can I make the MySQL server and how do Igo 
about doing this?

2- All flash storage I have been told has limited life in terms of writing on 
it. For my applicaition which will be write to the database intensively, ti 
would be ill-advised to use mysql unless I can load its file to RAM. Loss of 
data on power-down is not a problem as long as system integrity is retained 
and I can start up again all applications including the Mysql databse.

Is it possible (or does this happen automatically) to have all reading and 
writing take place in volatile memory instead of accessing the HD every time?

Regards and thanks in advance for all replies.

Roland.


-- 
-"I have not failed. I've just found 10,000 ways that won't work."

-(Thomas Alva Edison 1847-1931)


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

RE: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes

[Ed Lazor]

> -Original Message-
> From: Christopher L. Everett [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 06, 2004 1:47 AM
> To: Mysql List
> Subject: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes
> 
> I have an application where I create a faily large table (835MB) with a
> fulltext index.  One of our development workstations and our production
> server will run the script to load the table, but afterwards we have a
> pervasive corruption, with out of range index index pointer errors.
> Oddly, my development workstation doesn't have those problems.
> 
> My box and the ones having the problems have the following differences:
> 
>   - my box runs ReiserFS, the problem boxes run XFS
>   - my box has a nice SCSI HD subsystem, the problem boxes do IDE.
> 
> All three boxes run Linux 2.6.x kernels, and my workstation and production
> server share the same mobo.  Come to think of it, I saw similar corruption
> issues under 2.4.x series kernels and MySQL v4.0.x, it just wasn't the
> show stopper it is now.
> 
> Also, on all three boxes, altering the table to drop an index and create
> a new one requires a "myisamchk -rq" run afterwards when a fulltext index
> either exists or gets added or dropped, which I'd also call a bug.

The problems you're describing are similar to what I've run into when there
have been hardware related problems.  

One system had a problem with ram.  Memory tests would test and report ram
as ok, but everything started working when I replaced the ram.  I think it
was just brand incompatibility or something odd, because the ram never gave
any problems in another system.

One system had hard drive media slowly failing and this wasn't obvious until
we ran several full scan chkdsks.

The funniest situation was where enough dust had collected in the CPU fan to
cause slight over heating, which resulted in oddball errors.

In each of these cases, everything would work fine until the system would
start processing larger amounts of data.  Small amounts of corruption began
to show up that seemed to build on itself.

This may or may not relate to what you're dealing with, but maybe it will
help =)

-Ed



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

Lost Connection to MySQL server during Data Load

[Roy Harrell]

I'm trying to import data in a sparsely populated table
from a text file. I've attempted this with mysqlimport
and the LOAD DATA command. In each case I get an
ERROR 2013: Lost connection to MySQL server during
query. My tables are InnoDB type and the database
is small. I'm running Fedora Linux 2.6.6-1.435 and
mysql server 4.0.20-standard.

Thanks,

Roy


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

Re: Error message on windows xp install

[Martin Gainty]

Also keep in mind that if you have XP SP2 installed all of your ports are
blocked (except Port 80 of course)
You will need to reconfig the Firewall available in Control Panel
specifically TCP 3306 4759
BTW: It would be helpful to place Port Assignment information in System
Requirements
I found it by digging thru reams of doc
Martin-
- Original Message -
From: <[EMAIL PROTECTED]>
To: "Brian Menke" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, October 05, 2004 12:08 PM
Subject: Re: Error message on windows xp install


> I have had much better luck unzipping the Win32 binaries into their own
> folder and updating the my.ini file to match.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> "Brian Menke" <[EMAIL PROTECTED]> wrote on 10/05/2004 11:32:49 AM:
>
> > Hi everyone,
> >
> > I'm running windows XP professional and just downloaded the latest
> binary
> > installer. I have used MySQL for a while (this is a new install), but
> for
> > some reason when I double click the setup.exe icon, I get the following
> > error message: "The system file is not suitable for running MS-DOS and
> > Microsoft Windows applications. Choose 'Close' to terminate the
> > application".
> >
> >
> >
> > Any ideas? I was very careful to grab the windows version off a mirror
> site
> > (linked for mysql.com).
> >
> >
> >
> > Thanks!
> >
> >
> >
> > -Brian Menke
> >
>

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

Re: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes

[Christopher L. Everett]

I've also found a problem with "myisamchk --sort-keys":
christopher:/var/lib/mysql/dmoz# myisamchk -rq xurls
- check record delete-chain
- recovering (with sort) MyISAM-table 'xurls'
Data records: 1981904
- Fixing index 1
- Fixing index 2
- Fixing index 3
Data records: 4332227
christopher:/var/lib/mysql/dmoz# myisamchk -ce xurls
Checking MyISAM file: xurls
Data records: 4332227   Deleted blocks:   0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
- check records and index references
christopher:/var/lib/mysql/dmoz# myisamchk -aS xurls
- Sorting index for MyISAM-table 'xurls'
christopher:/var/lib/mysql/dmoz# myisamchk -ce xurls
Checking MyISAM file: xurls
Data records: 4332227   Deleted blocks:   0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check data record references index: 3
myisamchk: error: Found key at page 208005120 that points to record 
outside datafile
- check records and index references
1307000^C

Also, I gt a segfault trying to use "myisamchk --sort-records".
Christopher L. Everett wrote:
I have an application where I create a faily large table (835MB) with a
fulltext index.  One of our development workstations and our production
server will run the script to load the table, but afterwards we have a
pervasive corruption, with out of range index index pointer errors. 
Oddly, my development workstation doesn't have those problems.

My box and the ones having the problems have the following differences:
 - my box runs ReiserFS, the problem boxes run XFS
 - my box has a nice SCSI HD subsystem, the problem boxes do IDE.
All three boxes run Linux 2.6.x kernels, and my workstation and 
production
server share the same mobo.  Come to think of it, I saw similar 
corruption
issues under 2.4.x series kernels and MySQL v4.0.x, it just wasn't the
show stopper it is now.

Also, on all three boxes, altering the table to drop an index and create
a new one requires a "myisamchk -rq" run afterwards when a fulltext index
either exists or gets added or dropped, which I'd also call a bug.

--
Christopher L. Everett
Chief Technology Officer   www.medbanner.com
MedBanner, Inc.  www.physemp.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

need documentation of MyISAM file structures

[Indra Polak]

Hi,
we are recovering from a drop database and we need a clear description
of the structures of all MyISAM data files (especially the dynamic table 
structure)
in order to speed up our present reverse-engineering activities.

We are trying to recognize unallocated (disk) blocks to determine 
whether they
belong to a  MyISAM data file to put together in this way all deleted 
tables.

If anyone knows of such a description or can provide us  with pointers 
how to
obtain it, we would be very grateful.

We looked at the documentation and the source code ofcourse but that was 
not clear enough.

If anyone has a better idea how to recover from a drop database command 
(apart from using a backup)
we would be interested as well.

Have a nice day,
Indra Polak
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Repeated corruption with MySQL 4.1.x using FULLTEXT indexes

[Christopher L. Everett]

I have an application where I create a faily large table (835MB) with a
fulltext index.  One of our development workstations and our production
server will run the script to load the table, but afterwards we have a
pervasive corruption, with out of range index index pointer errors. 
Oddly, my development workstation doesn't have those problems.

My box and the ones having the problems have the following differences:
 - my box runs ReiserFS, the problem boxes run XFS
 - my box has a nice SCSI HD subsystem, the problem boxes do IDE.
All three boxes run Linux 2.6.x kernels, and my workstation and production
server share the same mobo.  Come to think of it, I saw similar corruption
issues under 2.4.x series kernels and MySQL v4.0.x, it just wasn't the
show stopper it is now.
Also, on all three boxes, altering the table to drop an index and create
a new one requires a "myisamchk -rq" run afterwards when a fulltext index
either exists or gets added or dropped, which I'd also call a bug.
--
Christopher L. Everett
Chief Technology Officer   www.medbanner.com
MedBanner, Inc.  www.physemp.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Problem with order by .......... asc

[Alaios]

Hi we need to order a field in a select query... The criteria is based on greek 
language... The problem  seems to be no support for greek language

I can change everything in the my.cnf so tell what to do


-
Do you Yahoo!?
vote.yahoo.com - Register online to vote today!