Re: Install MySQL and MS SQL on Same Server

[Martijn Tonies]

> Will be any problem if I install MySQL 4.0.15 for Win on the Server that
> already has MS SQL 2K under W2K Server?

Except that they may be battling for resources: no.

You can also adjust the MS SQL 2K settings to have a maximum memory
and CPU usage.


With regards,

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


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

Linux (x86, libc6) Standard 4.0.15 quesiton?

[Clive Luk]

Hi all,

Just some simple questions. The Linux (x86, libc6) Standard 4.0.15 14.5M.

from http://www.mysql.com/downloads/mysql-4.0.html

Is that version I donwloaded a binary verison?
Does that version support Chinese character?



Cheers,
Clive

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

Re: Access my mysql db remotely...

[Kelley Lingerfelt]

Or just use an ssh tunnel to access it and then the firewall stays intact, and with 
the tunnel, it will be a local user, and it can come from any
address, and all the database is concerned with is localhost access.

Kelley



Antony Dovgal wrote:

> On Wed, 22 Oct 2003 14:00:13 +0800
> "Hiu Yen Onn" <[EMAIL PROTECTED]> wrote:
>
> > i want to create a user who access from internet remotely to my db (outside
> > firewall)?
> > what is the solution then? how can i define the user accoutn?
> > [EMAIL PROTECTED]
>
> if your firewall allows other users to connect to your host using port 3306 (which 
> is default for MySQL), then you should put there user's hostname.
> smthng like this:
> [EMAIL PROTECTED]
>
> ---
> WBR,
> Antony Dovgal aka tony2001
> [EMAIL PROTECTED]
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


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

mysql server setting

[Pey Ling]

hi, there.

i have a question.  i am looking for the solution to put this into mysql server: 
[EMAIL PROTECTED]  can i know what does this mean?  normally, it is [EMAIL PROTECTED]  
how can i set this?  what does % mean?  can anyone help me out?  

thank you very much.

best regards,
 
Pey Ling

Re: Access my mysql db remotely...

[Antony Dovgal]

On Wed, 22 Oct 2003 14:00:13 +0800
"Hiu Yen Onn" <[EMAIL PROTECTED]> wrote:

> i want to create a user who access from internet remotely to my db (outside
> firewall)?
> what is the solution then? how can i define the user accoutn?
> [EMAIL PROTECTED]

if your firewall allows other users to connect to your host using port 3306 (which is 
default for MySQL), then you should put there user's hostname.
smthng like this:
[EMAIL PROTECTED]


---
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]

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

Install MySQL and MS SQL on Same Server

[William IT]

Will be any problem if I install MySQL 4.0.15 for Win on the Server that
already has MS SQL 2K under W2K Server?



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

Access my mysql db remotely...

[Hiu Yen Onn]

i want to create a user who access from internet remotely to my db (outside
firewall)?
what is the solution then? how can i define the user accoutn?
[EMAIL PROTECTED]

Cheers,
yenonn




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

[SubQuery] BUG ??

[Kim G. Pedersen]

hello

I have a very strange case where the my result depend
on the fields I want return from query :-(

A simplified query (Extracted the Sub queries out for read easyness):

select POL1.*,PRO.ID from oPositionLine   POL1 , (SUB2) T4
join pproduct PRO on POL1.ProductID=PRO.ID
wherePOL1.positionID = T4.PositionID


this works perfect, BUT if I just  change to

   vv
select POL1.*,PRO.ID from oPositionLine   POL1 , (SUB2) T4
join pproduct PRO on POL1.ProductID=PRO.ID
wherePOL1.positionID = T4.PositionID

I get empty result



SUB2 = Select OPOL.PositionID  from oPositionLine OPOL,oeventtype OET
, (SUB1)  t3 where OPOL.PositionID = t3.positionID and
OET.ID=OPOL.EventtypeID
group by OPOL.PositionID,OPOL.ProductID
having sum(OET.SignValue*OPOL.LocalPrice) <>0


Sub1 = select PositionID  from opositionline OPOL ,oeventtype OET
,oposition POS
where OET.ID=OPOL.EventtypeID and OET.kind=1 and POS.ID=OPOL.positionID
and POS.finish='false' group by OPOL.PositionID  having
sum(OET.signvalue*OPOL.ProductAmount) =0



** FULL QUERY **

select POL1.ID,PRO.ID from oPositionLine   POL1 ,


(Select OPOL.PositionID  from oPositionLine OPOL,oeventtype OET
,(select OPOL.PositionID  from opositionline OPOL ,oeventtype OET
,oposition POS
where OET.ID=OPOL.EventtypeID and OET.kind=1 and POS.ID=OPOL.positionID
and POS.finish='false' group by OPOL.PositionID  having
sum(OET.signvalue*OPOL.ProductAmount) =0)  t3

where OPOL.PositionID = t3.positionID and OET.ID=OPOL.EventtypeID

group by OPOL.PositionID,OPOL.ProductID
having sum(OET.SignValue*OPOL.LocalPrice) <>0 ) T4

POS2.InvoiceAddrID = A.ID
 join pproduct PRO on POL1.ProductID=PRO.ID

wherePOL1.positionID = T4.PositionID

** FULL QUERY END **

Using 4.1.0-alfa-log

regards
Kim G. Pedersen
macaos/elprint Development
+45 35373808

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

Re: Mysql Performance Question

[Rainer Sip]

Thanks for your input. I'll try give my.cnf a try.

I'm not technical at all and am unable to describe the queries myself.
Attached some output from the server (Sorry for the long post):


SHOW STATUS
-
+--++
| Variable_name| Value  |
+--++
| Aborted_clients  | 106|
| Aborted_connects | 1  |
| Bytes_received   | 1386152010 |
| Bytes_sent   | 1017147314 |
| Com_alter_table  | 0  |
| Com_analyze  | 0  |
| Com_backup_table | 0  |
| Com_change_db| 404084 |
| Com_delete   | 954139 |
| Com_flush| 0  |
| Com_insert   | 38972  |
| Com_insert_select| 0  |
| Com_kill | 0  |
| Com_load | 0  |
| Com_select   | 5274720|
| Com_set_option   | 0  |
| Com_show_binlog_events   | 0  |
| Com_show_binlogs | 0  |
| Com_show_create  | 0  |
| Com_show_databases   | 4  |
| Com_show_fields  | 2  |
| Com_show_grants  | 0  |
| Com_show_keys| 0  |
| Com_show_logs| 0  |
| Com_show_master_status   | 0  |
| Com_show_new_master  | 0  |
| Com_show_open_tables | 0  |
| Com_show_processlist | 8  |
| Com_show_slave_hosts | 0  |
| Com_show_slave_status| 0  |
| Com_show_status  | 9  |
| Com_show_innodb_status   | 0  |
| Com_show_tables  | 56 |
| Com_show_variables   | 20 |
| Com_slave_start  | 0  |
| Com_slave_stop   | 0  |
| Com_truncate | 0  |
| Com_unlock_tables| 0  |
| Com_update   | 1412924|
| Connections  | 403875 |
| Created_tmp_disk_tables  | 688|
| Created_tmp_tables   | 105261 |
| Created_tmp_files| 0  |
| Delayed_insert_threads   | 0  |
| Delayed_writes   | 0  |
| Delayed_errors   | 0  |
| Flush_commands   | 1  |
| Handler_commit   | 0  |
| Handler_delete   | 27226  |
| Handler_read_first   | 611592 |
| Handler_read_key | 2133888023 |
| Handler_read_next| 2198593130 |
| Handler_read_prev| 278|
| Handler_read_rnd | 34278228   |
| Handler_read_rnd_next| 249766468  |
| Handler_rollback | 0  |
| Handler_update   | 1007151|
| Handler_write| 184271195  |
| Key_blocks_used  | 106432 |
| Key_read_requests| 3936331483 |
| Key_reads| 100865 |
| Key_write_requests   | 1897202|
| Key_writes   | 168|
| Max_used_connections | 234|
| Not_flushed_key_blocks   | 0  |
| Not_flushed_delayed_rows | 0  |
| Open_tables  | 1024   |
| Open_files   | 1055   |
| Open_streams | 0  |
| Opened_tables| 6601   |
| Questions| 17691302   |
| Qcache_queries_in_cache  | 1564   |
| Qcache_inserts   | 5109816|
| Qcache_hits  | 9173397|
| Qcache_lowmem_prunes | 89550  |
| Qcache_not_cached| 183073 |
| Qcache_free_memory   | 4490312|
| Qcache_free_blocks   | 1239   |
| Qcache_total_blocks  | 4393   |
| Rpl_status   | NULL   |
| Select_full_join | 712|
| Select_full_range_join   | 119|
| Select_range | 24655  |
| Select_range_check   | 0  |
| Select_scan  | 407032 |
| Slave_open_temp_tables   | 0  |
| Slave_running| OFF|
| Slow_launch_threads  | 0  |
| Slow_queries | 2892   |
| Sort_merge_passes| 0  |
| Sort_range   | 244321 |
| Sort_rows| 2279797622 |
| Sort_scan| 151217 |
| Table_locks_immediate| 7551643|
| Table_locks_waited   | 957623 |
| Threads_cached   | 220|
| Threads_created  | 235|
| Threads_connected| 15 |
| Threads_running  | 7  |
| Uptime   | 155131 |
+--++
132 rows in set (0.00 sec)


Snapshot of MYTOP
--
MySQL on localhost (4.0.14-Max)
up 1+19:18:02 [12:20:47]
 Queries: 17.0M  qps:  114 Slow:31.0 Se/In/Up/De(%):
00/00/00/00
 qps now:   87 Slow qps: 3.2  Threads:  129 ( 119/ 106)
75/01/09/07
 Cache Hits: 7.9k  Hits/s:  0.1 Hits now:  43.7  Ratio: 61.0% Ratio now:
66.7%
 Key Efficiency: 100.0%  Bps in/out:   8.

storage of values in mysql

[Uma Shankari T.]

Hello,

  I need to store the charater value as well as some float value in the
varchar defined field..is there any other way of decalaration of the field
that i can store the character as well as float value ?

Regards,
Uma


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

Re: What is best hardware for server performance

[Daniel Kasak]

Travis Reeder wrote:

It seems mostly to be mysql pinned, not the app.  like 99% mysql until 
all data is processed and keeps going up when data coming in is more 
than can be processed.

What could I change in my.cnf to get better performance?  I just have 
a default mysql install (4.0).

Travis
Faster & more CPUs then.
If the CPU usage were hovering around, say, 75%, you could assume you 
have an I/O issue that you could solve by upgrading to SCSI.
But if the CPU is at 99% while MySQL is running a query, upgrading your 
CPU(s) is the only hardware upgrade path.

You should also check, of course, that MySQL has indexes in the right 
fields, and that you're queries are linking on numeric fields ( as 
opposed to character fields ).
Also, check that you have the query cache enabled. It _really_ speeds 
things up.

And maybe post back with some specs and table defs and queries and the 
output of:

describe 

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

cnf file reference

[Dan Lampkin]

Hello All,
 
I've looked online and searched through many threads in the lists and
can't find a good reference for the cnf file.  I've found the page with
the command line options, but there are options I see in the list
threads that aren't in the page.
 
Is there a reference that lists all of them and what each one is for?
I've seen some threads that list several and tell exactly what they do,
I'd like to find all of the possible options and exactly what each one
does?
 
Thanks,
 
Dan

SOLVED Re: Strange results

[Joakim Ryden]

This was indeed the problem. See below. 

It turns out MySQL's BIGINT doesn't handle all that big of an INT.  :-/

Many thanks to bluejack.

--Jo

On Tuesday 21 October 2003 12:51 pm, bluejack wrote:
> On Tue, 21 Oct 2003 11:58:57 -0700, Joakim Ryden <[EMAIL PROTECTED]> 
wrote:
> > It's a BIGINT(25).
>
> Well there you go. Your number is too big for the field. BIGINT(25)
> can (theoretically) DISPLAY up to 25 digits, but it is still bounded
> by MySQL's internal limits, as documented here:
>
> http://www.mysql.com/doc/en/Numeric_types.html
>
> TypeBytes  MinimumMaximum
> BIGINT8-9223372036854775808   9223372036854775807
>
> Your number is bigger:
>
> 14243385100413147136
>   9223372036854775807
>
> Behavior beyond the maximum is undefined, and it looks like
> you are hitting that undefined behavior.
>
> Unless you are actually doing numeric things with this token,
> you might want to store it as a string.
>
> -Bluejack
>
>
>
>
>
> !DSPAM:3f958e4f36899526667875!


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

Re: What is best hardware for server performance

[Travis Reeder]

It seems mostly to be mysql pinned, not the app.  like 99% mysql until 
all data is processed and keeps going up when data coming in is more 
than can be processed.

What could I change in my.cnf to get better performance?  I just have a 
default mysql install (4.0).

Travis



Daniel Kasak wrote:

Travis Reeder wrote:

So I think these are the only options, but if someone has another idea 
I'd be glad to hear it.  Our current server just isn't handling the 
load anymore so, it has single processor

Option 1:

Single server with dual processor and change from IDE to SCSI

Option 2:

One server with mysql on it, a second one to handle application 
(collecting and reporting on data)

Which do you think would be better?

Travis


I would do some more benchmarking to find out where your bottleneck is. 
You don't give any hardware specs, so it's hard to say...
Run 'top' and see how much CPU / memory is being used by each ( mysql 
and 'application' ).
If you CPU usage isn't high, maybe you would benefit from adding some 
more RAM. MySQL will run a LOT faster if all the tables can fit in RAM.
Also check out the options in my.cnf to see that you're actually using 
your resources. Maybe MySQL is limited by options in here?

Personally, I would go with a dual CPU system with PLENTY of RAM, and 
some SATA disks. But as I said, you may get away with just giving it 
more RAM.



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

Re: Update Syntax

[Kelley Lingerfelt]

Can you use REPLACE?

Kelley



Randy Chrismon wrote:

> Please tell me it ain't so...
>
> I am writing a Lotus Nots agent to feed data directly into a MySQL
> table. The agent needs to either insert a new record or update an
> existing record depending on whether a document in Nots is new or
> updated. Because this app may be ported over to a DB2 server, I am
> avoiding the "on duplicate key update" option. So, I intended to test
> for the existence of a MySQL record and, depending on the result,
> prepend either "Update my_table" to a build SQL string, or prepend
> "Insert into table". And then I read the documentation... It looks
> like Update MUST use the set column_1=new_value1,
> column_2=new_value2,
> etc. format. I was hoping to do something like "update my_table
> values(newValue1, newValue2...)" making sure to have a value or
> holder
> for each column. In other words, I was expecting insert and update to
> look pretty much the same except for the prefix and a where clause on
> the end of the update. Am I right that I can't do this??
>
> Thanks.
> Randy
>
> --
> 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]

Update Syntax

[Randy Chrismon]

Please tell me it ain't so...

I am writing a Lotus Nots agent to feed data directly into a MySQL
table. The agent needs to either insert a new record or update an
existing record depending on whether a document in Nots is new or
updated. Because this app may be ported over to a DB2 server, I am
avoiding the "on duplicate key update" option. So, I intended to test
for the existence of a MySQL record and, depending on the result,
prepend either "Update my_table" to a build SQL string, or prepend
"Insert into table". And then I read the documentation... It looks
like Update MUST use the set column_1=new_value1,
column_2=new_value2,
etc. format. I was hoping to do something like "update my_table
values(newValue1, newValue2...)" making sure to have a value or
holder
for each column. In other words, I was expecting insert and update to
look pretty much the same except for the prefix and a where clause on
the end of the update. Am I right that I can't do this?? 

Thanks.
Randy

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

Re: Mysql Performance Question

[Matt W]

Hi Rainer,

You might get more improvement by optimizing your application and
queries than by tuning hardware or MySQL. :-)

About switching to InnoDB, are you doing lots of writes that are causing
locked tables? e.g. What's the ratio of Table_locks_immediate to
Table_locks_waited in SHOW STATUS? If you aren't doing lots of UPDATEs
or DELETEs that cause locking problems, InnoDB won't really give "higher
concurrency." Remember, INSERTs aren't usually a problem, since SELECTs
can run at the same time with MyISAM as long as there's no free space in
the data file.

Anyway, about your my.cnf. I don't think it's as important as people
make it that the key_buffer be so large. If it's unnecessarily big, it's
taking away free memory that the OS could use to cache *file* data
(key_buffer only caches indexes, not the data file). It's a lot faster
to read indexes "from disk" (they may be still cached by the OS anyway
if not by MySQL) than it is to read data rows!

I don't know how big your indexes are and if only the same small
portions are usually used or the whole thing? In other words, no matter
the size of the indexes, how much of them is actually accessed? If it's
less than key_buffer size, key_buffer is too big. e.g. If
Key_blocks_read is not much more than Key_blocks_used, key_buffer is too
big. Of course, Key_blocks_read will go up if a table is closed/flushed
and indexes are reloaded, so this could be artificially high. Again, I
don't know how much of your indexes are frequently used, but a
key_buffer of 64-128M is probably plenty. If Key_reads divided by
Key_read_requests is less than 0.005-0.01, it's definitely OK.

Also, right now, MySQL uses a global mutex lock or something in the key
buffer (e.g. exclusive lock even when *reading* keys!) which hurts
MyISAM concurrency. I believe the new rewritten key cache system is
implemented in version 4.1.1, which should be out within a month. Sounds
like good news.

Is your table_cache always full (Open_tables is 1024?) and Opened_tables
status variable "high?" If so, you may want to increase the table_cache
to 1536 or something.

For sort, join, and record/read buffers, I don't know if the full amount
is allocated right away, or as needed up to the limit. Anyone know? If
it's all at once, the system may be allocating and releasing too much
memory all the time. See
http://jeremy.zawodny.com/blog/archives/34.html

Why do you have thread_cache_size set to 512 when max_connections is
only 500? :-) I think you should set thread_cache_size to a little less
than the amount of clients that are usually connected.

tmp_table_size seems a bit big. query_cache_size seems a bit small,
assuming you have lots of queries that could be cached. In MySQL 4,
skip-locking is the default; "set-variable =" syntax is deprecated;
record_buffer is now read_buffer; and it's mysqld_safe instead of
safe_mysqld. And thread_concurrency only applies to Solaris, BTW.

Without knowing about your workload, you might try something like this
for your my.cnf:

[client]
socket=/var/lib/mysql/mysql.sock

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-innodb
max_connections=500
max_allowed_packet=1M
key_buffer_size=96M
sort_buffer_size=2M
join_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=1M
table_cache=1024
thread_cache_size=64
tmp_table_size=48M
myisam_sort_buffer_size=512M
query_cache_size=32M
query_cache_limit=2M
#max_connect_errors=1000
#back_log=100
#log-bin

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit=8192

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=384M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[myisamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=384M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[mysqlhotcopy]
interactive-timeout


Hope that helps.


Matt

> - Original Message -
> From: "Rainer Sip"
> Sent: Tuesday, October 21, 2003 9:48 AM
> Subject: Mysql Performance Question
>
>
> I'm running a community site (Xoops) on Mysql 4.0.14.
>
> I found that the speed of my site is slow during peak hours, when
there
> are 450 concurrent uers hanging on the site. Mytop showed that the
> queries per second maxed at 500. I believe this could be higher,
> provided that I have it running on a dedicated machine. I also noticed
> the load average is very high (12+ during peak hours)
>
> In the mid run I'm planning to mirgrate to innodb for higher
concurrency
> (I'm currently using myisam). However, I'm seeking suggestions in fine
> tuning the parameters.
>
> The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB
> of memory. There are 4 disks running raid 0+1. Attached the my.cnf for
> your easy review. Thanks a l

Re: Need help constructing query ...

[John Kelly]

: > -Original Message-
: > From: John Kelly [mailto:[EMAIL PROTECTED]
: > Sent: Tuesday, October 21, 2003 3:45 PM
: > To: [EMAIL PROTECTED]
: > Cc: [EMAIL PROTECTED]
: > Subject: Re: Need help constructing query ...
: >
: >
: > - Original Message - 
: > From: "Daniel Clark" <[EMAIL PROTECTED]>
: > To: <[EMAIL PROTECTED]>
: > Cc: <[EMAIL PROTECTED]>
: > Sent: Tuesday, October 21, 2003 2:33 PM
: > Subject: Re: Need help constructing query ...
: >
: >
: > : > Hi, I have a table full of logged urls and ip addresses.
: > The following
: > : > query returns all the urls and the number of requests. How would I
: > : > modify it to return unique requests based on distinct ip
: > addresses?
: > : >
: > : > select url, count(*) as pageviews from table group by url order by
: > : > pageviews desc
: > :
: > : How about:
: > :
: > : SELECT ip_address, url, count(*)
: > : FROM tablename
: > : GROUP BY ip_adress, url
: > :
: > Thanks but I could not get that to work. It does not appear
: > to count the number of page requests by distinct IPs anyway
: > does it? Don't you need something like a
: > count(distinct(ip_address)) somewhere in there?
: >
: > -- 

- Original Message - 
From: "Kevin Fries" <[EMAIL PROTECTED]>
To: "'John Kelly'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 3:57 PM
Subject: RE: Need help constructing query ...


: Then I think you want
: SELECT url, COUNT(DISTINCT ip_address)
: FROM tablename
: GROUP BY url;

Thanks, this must be a resource intensive query as it works in a few seconds on
a small table but takes 6+ minutes when done on a table with just 100,000
records. Anyway, thanks again.

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

Re: What is best hardware for server performance

[Daniel Kasak]

Travis Reeder wrote:

So I think these are the only options, but if someone has another idea 
I'd be glad to hear it.  Our current server just isn't handling the 
load anymore so, it has single processor

Option 1:

Single server with dual processor and change from IDE to SCSI

Option 2:

One server with mysql on it, a second one to handle application 
(collecting and reporting on data)

Which do you think would be better?

Travis


I would do some more benchmarking to find out where your bottleneck is. 
You don't give any hardware specs, so it's hard to say...
Run 'top' and see how much CPU / memory is being used by each ( mysql 
and 'application' ).
If you CPU usage isn't high, maybe you would benefit from adding some 
more RAM. MySQL will run a LOT faster if all the tables can fit in RAM.
Also check out the options in my.cnf to see that you're actually using 
your resources. Maybe MySQL is limited by options in here?

Personally, I would go with a dual CPU system with PLENTY of RAM, and 
some SATA disks. But as I said, you may get away with just giving it 
more RAM.

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

What is best hardware for server performance

[Travis Reeder]

So I think these are the only options, but if someone has another idea 
I'd be glad to hear it.  Our current server just isn't handling the load 
anymore so, it has single processor

Option 1:

Single server with dual processor and change from IDE to SCSI

Option 2:

One server with mysql on it, a second one to handle application 
(collecting and reporting on data)

Which do you think would be better?

Travis



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

How do I have 3.23.54 and 4.0.15 running at the same time?

[Juan M. Quiroz]

Hi,
That's basically my question. I want to be able to run the 2 releases
on the same OSX 10.2.8 machine.

Is there a way?

Thanks

Juan

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

RE: Need help constructing query ...

[Kevin Fries]

Then I think you want
SELECT url, COUNT(DISTINCT ip_address)
FROM tablename
GROUP BY url;

> -Original Message-
> From: John Kelly [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, October 21, 2003 3:45 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: Need help constructing query ...
> 
> 
> - Original Message - 
> From: "Daniel Clark" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Tuesday, October 21, 2003 2:33 PM
> Subject: Re: Need help constructing query ...
> 
> 
> : > Hi, I have a table full of logged urls and ip addresses. 
> The following
> : > query returns all the urls and the number of requests. How would I
> : > modify it to return unique requests based on distinct ip 
> addresses?
> : >
> : > select url, count(*) as pageviews from table group by url order by
> : > pageviews desc
> :
> : How about:
> :
> : SELECT ip_address, url, count(*)
> : FROM tablename
> : GROUP BY ip_adress, url
> :
> Thanks but I could not get that to work. It does not appear 
> to count the number of page requests by distinct IPs anyway 
> does it? Don't you need something like a
> count(distinct(ip_address)) somewhere in there?
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
> 
> 


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

Very large table with broken index

[Matt Sturtz]

Hello All--

We have a particularly large MyISAM table on an older MySQL (3.23.56) on
an older Linux (RedHat 6.2).  The table is always growing (data is never
removed, except when we move it and start a new one).  Yesterday one of
the indexes started acting weird, so I took a look, and the .MYI file was
2.0G.  I suspect it hit the 2G file size limit on Linux.  We moved the
broken table out of the way and started a new one with exactly the same
spec.

It's now in a "crashed" state...  I've been running myisamchk with some
optimization options, but the table has almost 7.88M rows.  After nearly
24 hours, it's currently working on 3.4M rows, in the first of 5 indexes.

Is there a simple way I can drop the indexes and get access to the table? 
We need to move the last 3 months of data (insert ... select ?) to the new
table we started, and then we can 'myisampack' this one and archive it... 
Or am I stuck waiting for myisamchk to finish?

myisamchk --force --fast --update-state -O key_buffer=128M -O
sort_buffer=128M -O read_buffer=4M -O write_buffer=4M 

Any help appreciated...

-Matt-

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

Re: Need help constructing query ...

[John Kelly]

- Original Message - 
From: "Daniel Clark" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 2:33 PM
Subject: Re: Need help constructing query ...


: > Hi, I have a table full of logged urls and ip addresses. The following
: > query returns all the urls and the number of requests. How would I
: > modify it to return unique requests based on distinct ip addresses?
: >
: > select url, count(*) as pageviews from table group by url order by
: > pageviews desc
:
: How about:
:
: SELECT ip_address, url, count(*)
: FROM tablename
: GROUP BY ip_adress, url
:
Thanks but I could not get that to work. It does not appear to count the number
of page requests by distinct IPs anyway does it? Don't you need something like a
count(distinct(ip_address)) somewhere in there?

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

Config problems - logging

[Matt Babineau]

Hey all-

I've been trying to track down some slow queries but I cant get the
logging turned on properly, for some reason my server just wont create a
slow query log and then when I tried mysqldumpslow, it gave me an error
that I have no basedir setup. Has anyone had any similar problem that
can shed some light on my situation? I have attached my cnf file

thanks-
Matt



# Example mysql config file for large systems.
#
# This is for large system with memory = 512M where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.

# The following options will be passed to all MySQL clients
[client]
#password   = your_password
port= 3306
socket  = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port= 3306
socket  = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
max_connections = 1000
table_cache = 256
sort_buffer_size = 8M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
wait_timeout = 60
log_slow_queries = 1
log = 1

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 3

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id   = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#the syntax is:
#
#CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
#MASTER_USER=, MASTER_PASSWORD= ;
#
#where you replace , ,  by quoted strings and
# by the master's port number (3306 by default).
#
#Example:
#
#CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#start replication for the first time (even unsuccessfully, for example
#if you mistyped the password in master-password and the slave fails to
#connect), the slave will create a master.info file, and any later
#change in this file to the variables' values below will be ignored and
#overridden by the content of the master.info file, unless you shutdown
#the slave server, delete master.info and restart the slaver server.
#For that reason, you may want to leave the lines below untouched
#(commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id   = 2
#
# The replication master for this slave - required
#master-host =   
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user =   
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port =  
#
# binary logging - not required for slaves, but recommended
#log-bin

# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 64M
#bdb_max_lock = 10

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you a

RE: mysql to mssql query

[Dan Lampkin]

Oops, you are subtracting the date, so use - 7 instead of + 7...

Dan

-Original Message-
From: Dan Lampkin [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 21, 2003 5:05 PM
To: 'DevList'; 'mySQL Mailing List'
Subject: RE: mysql to mssql query

If I understand your question correctly, you're wanting to grab the
records where the date is greater than or equal to a date plus 7 days.
If so, then:

SELECT * FROM myTable WHERE date_field >= CAST('2003-10-19' AS DATETIME)
+ 7 

Or you can use the DATEADD function:

SELECT * FROM myTable WHERE date_field >= DATEADD(DAY, 7, '2003-10-19')


Dan
-Original Message-
From: DevList [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 21, 2003 4:49 PM
To: mySQL Mailing List
Subject: mysql to mssql query

Does anybody know the equivalent of this query in mssql:

SELECT * FROM myTable WHERE date_field >= DATE_SUB('2003-10-19',
INTERVAL 7
DAY);

For this project we are pulling records off a MS SQL db, and I need to
figure a way to return all records for the week.

Any help is appreciated.




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


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

RE: mysql to mssql query

[Dan Lampkin]

If I understand your question correctly, you're wanting to grab the
records where the date is greater than or equal to a date plus 7 days.
If so, then:

SELECT * FROM myTable WHERE date_field >= CAST('2003-10-19' AS DATETIME)
+ 7 

Or you can use the DATEADD function:

SELECT * FROM myTable WHERE date_field >= DATEADD(DAY, 7, '2003-10-19')


Dan
-Original Message-
From: DevList [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 21, 2003 4:49 PM
To: mySQL Mailing List
Subject: mysql to mssql query

Does anybody know the equivalent of this query in mssql:

SELECT * FROM myTable WHERE date_field >= DATE_SUB('2003-10-19',
INTERVAL 7
DAY);

For this project we are pulling records off a MS SQL db, and I need to
figure a way to return all records for the week.

Any help is appreciated.




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


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

Re: mysql to mssql query

[Gabriel Ricard]

Well, even if you don't find an alternative for that syntax, you could 
always do the date subtraction in application space and make the query 
like this:

SELECT * FROM myTable WHERE date_field >= '2003-10-12'

- Gabriel

On Tuesday, October 21, 2003, at 05:49  PM, DevList wrote:

Does anybody know the equivalent of this query in mssql:

SELECT * FROM myTable WHERE date_field >= DATE_SUB('2003-10-19', 
INTERVAL 7
DAY);

For this project we are pulling records off a MS SQL db, and I need to
figure a way to return all records for the week.
Any help is appreciated.


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

mysql to mssql query

[DevList]

Does anybody know the equivalent of this query in mssql:

SELECT * FROM myTable WHERE date_field >= DATE_SUB('2003-10-19', INTERVAL 7
DAY);

For this project we are pulling records off a MS SQL db, and I need to
figure a way to return all records for the week.

Any help is appreciated.




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

Replication RO/RW DBs on same server.

[Jonathan Tullett]

Hello,

I have spent the last few hours searching through the archives for help
with this question, and have also been reading the MySQL documentation, so
please excuse me if I am being monumentally blind and missing something
obvious.

I am trying to set up a MySQL installation like so:

   R/W DBRO DB
Machine A: fred   hank
Machine B: hank   fred

So, Machine A will be Master for 'fred' and slave for 'hank' and Machine B
will be Master for 'hank' and slave for 'fred'

My theory is that in worst case scenario and one of the machines goes
belly up, it would be a relatively easy change to switch the readonly DB
on the other machine into RW mode.

I cant see a way in the config to enable this kind of setup.  Am I missing
something very obvious, or is it more complicated than I thought?

It is purely the setup config/theory I am looking for here, once that is
sorted ill work on the fail over mechanisms myself.

Any help/advice/assistance you can provide will be greatly appreciated.

Many thanks,
Jonathan Tullett



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

Re: Need help constructing query ...

[Daniel Clark]

> Hi, I have a table full of logged urls and ip addresses. The following
> query returns all the urls and the number of requests. How would I
> modify it to return unique requests based on distinct ip addresses?
>
> select url, count(*) as pageviews from table group by url order by
> pageviews desc



How about:


SELECT ip_address, url, count(*)
FROM tablename
GROUP BY ip_adress, url





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

Need help constructing query ...

[John Kelly]

Hi, I have a table full of logged urls and ip addresses. The following query
returns all the urls and the number of requests. How would I modify it to return
unique requests based on distinct ip addresses?

select url, count(*) as pageviews from table group by url order by pageviews
desc


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

gemini

[Gabriel Ricard]

Someone recently asked about Gemini tables on this list and it got me 
curious about what ever happened to that technology... NuSphere still 
seems to exist, and they still sell their PHPEd product along with the 
"NuSphere Technology Platform" which appears to contain a build of 
MySQL. Did they ever GPL Gemini? Or did they just take it away?

Not that it matters a whole lot since InnoDB has continuously improved 
in Gemini's absence...

Anyone know what the story is?

- Gabriel

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

how to change border color and type of mysql_row command!!!!?

[Emilio Ruben Estevez]

Hi, im using this script to show in a table the results of a query how can i 
change the border color and type is this posible??

if (mysql_num_rows($qry) > 0) {
   for ($i = 0; $i
   echo "" . mysql_field_name($qry, $i) . 
"";
   }
}

?>

if (mysql_num_rows($qry) > 0) {
   for ($j = 0; $j
   ?>

   for ($k = 0; $k" . mysql_result($qry,$j, $k) . "";
   }
Thanks.
Emilio
_
Surf and talk on the phone at the same time with broadband Internet access. 
Get high-speed for as low as $29.95/month (depending on the local service 
providers in your area).  https://broadband.msn.com

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

unable to connect after log in

[chilie palmer]

I have a connection problem that I have been unable to solve after searching 
the web and the mysql mailing-lists.  I'm connecting to a local database 
using PHP.  It's a pretty simple and straightfoward setup, a user logs in 
and the links they have access to are displayed (change user info, update 
password, etc).  I can log in with the initial links displayed (database 
connection is made), but after that (either by clicking a link or hitting 
refresh on the same page I just logged in to) I get the following error:

Access denied for user: '@localhost' to database 'my_DB_name'

The only way I have been able to work around this is by giving Select, 
Insert, Update, Delete permissions to the host 'localhost' (no user or 
password) in the mysql.users table.  If I give this host no permissions, the 
script works as described above, only logging in once.  Below is the PHP db 
connect function that I am using (included via require_once on all pages).  
I have tried to connect both with root and another user I created with all 
permissions.  I have also tried creating a user in the mysql.db table with 
'localhost - my_DB_name - my_DB_user' and all permissions.


  if (!mysql_select_db('my_DB_name'))
 return false;
  return $result;
}
?>
Is there something I've overlooked here in my connection setup?  I can't 
give the localhost host permissions in my production environment for 
security reasons.  Any help, suggestions, or ideas are appreciated. (Using - 
MySQL 4.0.13, PHP 4.3.2)

Thanks

_
Want to check if your PC is virus-infected?  Get a FREE computer virus scan 
online from McAfee.
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

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

Re: Uninstalling MySQL

[gerald_clark]

It all depends on how you installed it.
If it is an rpm, use rpm -e to un-install it.
If from a tarfile, find out where the files were written, and remove them.
[EMAIL PROTECTED] wrote:

Hi, I´ve just installed MySQL and I wanna know how to uninstall it.

I tried to look for uninstall in the manual and at MySQL WebSite but I did not found.

Thanks.

  Tag Line 
 
   Francis Fernandes Ferreira   
   [EMAIL PROTECTED]  tel: 
 mobile:  +55 41 3019-8630
 +55 41 9616-1370  
  

   

   Powered by Plaxo Want a signature like this? 


 



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

Updating help.

[Mike Nelson]

Hi,

Can somebody tell me if there is a way to accomplish this update without
locking both tables.

UPDATE t1, t2 SET t2.a = t1.a WHERE t2.id = t1.id

t1 is not being updated yet it becomes locked and does not allow any
read queries until it is done.

Can somebody help?

Thanks,

 Mike.

 



**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**

Re: Strange results

[bluejack]

On Tue, 21 Oct 2003 11:58:57 -0700, Joakim Ryden <[EMAIL PROTECTED]> wrote:

It's a BIGINT(25).
Well there you go. Your number is too big for the field. BIGINT(25)
can (theoretically) DISPLAY up to 25 digits, but it is still bounded
by MySQL's internal limits, as documented here:
http://www.mysql.com/doc/en/Numeric_types.html

TypeBytes  MinimumMaximum
BIGINT8-9223372036854775808   9223372036854775807
Your number is bigger:

14243385100413147136
 9223372036854775807
Behavior beyond the maximum is undefined, and it looks like
you are hitting that undefined behavior.
Unless you are actually doing numeric things with this token,
you might want to store it as a string.
-Bluejack

 

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

Re: Use of uninitialized value in join or string at ./dump_members.pl line 35.

[gerald_clark]

My Guess:

Jerry Rocteur wrote:

Hi,

I'm reading MySQL by Paul DuBois

When I run dump_members.pl I get:

Use of uninitialized value in join or string at ./dump_members.pl line 
35.

Line 35 is the print line before. 
Your select probably failed,



while (my @ary = $sth->fetchrow_array ()) 
and here was no data to fetch,

{
print join ("\t",  @ary), "\n"; 
so @ary is not initialized.

}

I just can't see which value is not initialized...

As I'd like to use the script for my own use and I would prefer using 
perl -w than not using it what do I change in the script in order NOT 
to get the error ??

I'm including the whole script below..

Thanks in advance,

Jerry
#! /usr/bin/perl  -w
#@ _COMMENT_
# dump_members.pl - dump Historical League's membership list
#@ _COMMENT_
#@ _USE_
use strict;
use DBI;
#@ _USE_
#@ _VARDECL_
my $dsn = "DBI:mysql:sampdb:incc-test"; # data source name
my $user_name = 
"jerry";# user name
my $password = "secret";  
# password
#@ _VARDECL_

#@ _CONNECT_
# connect to database
my $dbh = DBI->connect ($dsn, $user_name, $password,
{ RaiseError => 1, 
PrintError => 0 });
#@ _CONNECT_

#@ _ISSUE_QUERY_
# issue query
my $sth = $dbh->prepare ("SELECT last_name, first_name, suffix, email,"
. "street, city, state, zip, phone FROM member ORDER BY 
last_name");
$sth->execute ();
#@ _ISSUE_QUERY_

#@ _FETCH_LOOP_
# read results of query, then clean up
#
while (my @ary = $sth->fetchrow_array ())
{
print join ("\t",  @ary), "\n";
}
$sth->finish ();
#@ _FETCH_LOOP_
#@ _TERMINATE_
$dbh->disconnect ();
exit (0);
#@ _TERMINATE_



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

Re: libmysqlclient.so

[Matt Babineau]

Thanks everyone! I installed MySQL-shared-X and it worked!

On Tue, 2003-10-21 at 15:48, Kelley Lingerfelt wrote:
> the mysql-devel rpm installs it.
> 
> Kelley
> 
> 
> Matt Babineau wrote:
> 
> > Any ideas what packages install this library? I used the RPM to try and
> > install MySQL-client on my redhat machine, but the libmysqlclient did
> > not show up in /usr/lib - so I am wondering which RPM I need to get this
> > file?
> >
> > Thanks,
> > Matt
> >
> > --
> > 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: libmysqlclient.so

[Kelley Lingerfelt]

the mysql-devel rpm installs it.

Kelley


Matt Babineau wrote:

> Any ideas what packages install this library? I used the RPM to try and
> install MySQL-client on my redhat machine, but the libmysqlclient did
> not show up in /usr/lib - so I am wondering which RPM I need to get this
> file?
>
> Thanks,
> Matt
>
> --
> 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: libmysqlclient.so

[Eric L. Howard]

At a certain time, now past [Oct.21.2003-12:24:30PM -0400], [EMAIL PROTECTED] spake 
thusly:
> Any ideas what packages install this library? I used the RPM to try and
> install MySQL-client on my redhat machine, but the libmysqlclient did
> not show up in /usr/lib - so I am wondering which RPM I need to get this
> file?

Did you search http://www.rpmfind.net?  The package that supplies this file
can change from one Linux distro to the next.  On some distros it's in
mysql-shared..rpm - or just mysql.xxx.rpm on others.

   ~elh

-- 
Eric L. Howard   e l h @ o u t r e a c h n e t w o r k s . c o m

www.OutreachNetworks.com313.297.9900

JabberID: [EMAIL PROTECTED] Advocate of the Theocratic Rule

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

Use of uninitialized value in join or string at ./dump_members.pl line 35.

[Jerry Rocteur]

Hi,

I'm reading MySQL by Paul DuBois

When I run dump_members.pl I get:

Use of uninitialized value in join or string at ./dump_members.pl line 
35.

Line 35 is the print line before.

while (my @ary = $sth->fetchrow_array ())
{
print join ("\t",  @ary), "\n";
}
I just can't see which value is not initialized...

As I'd like to use the script for my own use and I would prefer using 
perl -w than not using it what do I change in the script in order NOT 
to get the error ??

I'm including the whole script below..

Thanks in advance,

Jerry
#! /usr/bin/perl  -w
#@ _COMMENT_
# dump_members.pl - dump Historical League's membership list
#@ _COMMENT_
#@ _USE_
use strict;
use DBI;
#@ _USE_
#@ _VARDECL_
my $dsn = "DBI:mysql:sampdb:incc-test"; # data source name
my $user_name = "jerry";
# user name
my $password = "secret";  # 
password
#@ _VARDECL_

#@ _CONNECT_
# connect to database
my $dbh = DBI->connect ($dsn, $user_name, $password,
{ RaiseError => 1, 
PrintError => 0 });
#@ _CONNECT_

#@ _ISSUE_QUERY_
# issue query
my $sth = $dbh->prepare ("SELECT last_name, first_name, suffix, email,"
. "street, city, state, zip, phone FROM member ORDER BY 
last_name");
$sth->execute ();
#@ _ISSUE_QUERY_

#@ _FETCH_LOOP_
# read results of query, then clean up
#
while (my @ary = $sth->fetchrow_array ())
{
print join ("\t",  @ary), "\n";
}
$sth->finish ();
#@ _FETCH_LOOP_
#@ _TERMINATE_
$dbh->disconnect ();
exit (0);
#@ _TERMINATE_
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB or OS restriction?

[Ken Menzel]

Hi Alex,
What user is the mysql daemon running as?  Are there any limits on
that user or user class (/etc/login.conf).  Just because you have
raised the kernel limits does not mean they are raised for the user.
Can you login as that user and type "limit" and get something like -
%limit
cputime unlimited
filesizeunlimited
datasize1581056 kbytes
stacksize   524288 kbytes
coredumpsizeunlimited
memoryuse   unlimited
vmemoryuse  unlimited
descriptors 19000
memorylockedunlimited
maxproc 5547
sbsize  unlimited
%

Hope this helps,
Ken
- Original Message - 
From: "Heikki Tuuri" <[EMAIL PROTECTED]>
To: "Varshavchick Alexander" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 9:54 AM
Subject: Re: InnoDB or OS restriction?


> Alex,
>
> because 512 MB is not an InnoDB or MySQL restriction, it must be an
OS
> restriction :).
>
> I assume you have not allocated 1.5 GB of MySQL key_buffer.
>
> Best regards,
>
> Heikki
> Innobase Oy
> http://www.innodb.com
> InnoDB - transactions, row level locking, and foreign keys for MySQL
> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
> MyISAM tables
>
>
> - Alkuperäinen viesti - 
> Lähettäjä: "Varshavchick Alexander" <[EMAIL PROTECTED]>
> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>
> Kopio: <[EMAIL PROTECTED]>
> Lähetetty: Tuesday, October 21, 2003 2:55 PM
> Aihe: Re: InnoDB or OS restriction?
>
>
> > Hi Heikki,
> >
> > here is a snip from the original posting which you probably have
> > overlooked:
> >
> > > > > The first strange thing is that MAXDSIZ and DFLDSIZ in
kernel config
> are
> > > > > setted to 1536M
> >
> > Regards
> >
> > 
> > Alexander Varshavchick, Metrocom Joint Stock Company
> > Phone: (812)118-3322, 118-3115(fax)
> >
> > On Mon, 20 Oct 2003, Heikki Tuuri wrote:
> >
> > > Date: Mon, 20 Oct 2003 22:05:07 +0300
> > > From: Heikki Tuuri <[EMAIL PROTECTED]>
> > > To: [EMAIL PROTECTED]
> > > Subject: Re: InnoDB or OS restriction?
> > >
> > > Alex,
> > >
> > > in FreeBSD user process memory space is often restricted to 512
MB. You
> have
> > > to reconfigure or recompile the FreeBSD kernel to increase that
limit.
> > >
> > > Best regards,
> > >
> > > Heikki Tuuri
> > > Innobase Oy
> > > http://www.innodb.com
> > > Foreign keys, transactions, and row level locking for MySQL
> > > InnoDB Hot Backup - a hot backup tool for InnoDB: now
> > > also backs up your MyISAM tables
> > >
> > >
> > > - Original Message -
> > > From: "alex" <[EMAIL PROTECTED]>
> > > Newsgroups: mailing.database.myodbc
> > > Sent: Monday, October 20, 2003 1:30 PM
> > > Subject: Re: InnoDB or OS restriction?
> > >
> > >
> > > > Hi again,
> > > >
> > > > as there was not a single answer to my question I can imagine
that no
> one
> > > > encountered the same issue, but anyways, can there be any
hints? First
> of
> > > > all, are there any means of looking at mysql memory allocation
list
> > > > grouped by some major parts - for example,
> > > > innodb main pool - can be retrieved from innodb monitor
> > > > innodb additional pool - can be retrieved from innodb monitor
> > > > myisam main cache - ...?
> > > > myisam sort buffer - ...?
> > > > memory allocated from OS - ...?
> > > >
> > > > The questiion is why mysql is trying to allocate memory via
malloc
> from OS
> > > > while the innodb additional pool is occupied only by 50%? Or
is it
> myisam
> > > > buffer that mysql is trying to extend?
> > > >
> > > > Regards
> > > >
> > > > ---
> > > > Alex
> > > >
> > > > On Tue, 14 Oct 2003, alex wrote:
> > > >
> > > > > Hi people,
> > > > >
> > > > > I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD
4.6.2
> > > (server
> > > > > has 4G phisical memory), and occasionally mysql traps with
the
> message:
> > > > >
> > > > > InnoDB: Fatal error: cannot allocate 1064960 bytes of
> > > > > InnoDB: memory with malloc! Total allocated memory
> > > > > InnoDB: by InnoDB 513951016 bytes. Operating system errno:
12
> > > > > InnoDB: Cannot continue operation!
> > > > >
> > > > > The first strange thing is that MAXDSIZ and DFLDSIZ in
kernel config
> are
> > > > > setted to 1536M, and the second one is that each time it
happens,
> while
> > > > > the number of bytes reported to cannot be allocated is
different,
> total
> > > > > memory allocated by InnoDB is exactly the same - 513951016
bytes.
> Which
> > > > > restrictions are the cause of this - InnoDB's or FreeBSD's?
> > > > >
> > > > > Thanks in advance
> > > > >
> > > > > 
> > > > > Alex
> > > > >
> > > > > --
> > > > > MySQL General Mailing List
> > > > > For list archives: http://lists.mysql.com/mysql
> > > > > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> > > > >
> > > >
> > > > --
> > > > MySQL General Mailing List
> > > > For list archives: http://lists.mysql.com/mysql
> > > > To unsubscribe:
> > >
http://lists.mysql.com/[EMAIL PROTECTED]
> > > >
> > >
> > >
> > >
> > > --
> > > MySQL General Maili

libmysqlclient.so

[Matt Babineau]

Any ideas what packages install this library? I used the RPM to try and
install MySQL-client on my redhat machine, but the libmysqlclient did
not show up in /usr/lib - so I am wondering which RPM I need to get this
file?

Thanks,
Matt


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

Re: Mysql Performance Question

[David Griffiths]

You need to figure out what's slowing down your application. It could be
expensive queries which in turn could be caused by missing indexes. It could
be that the machine is too slow or the configuration of MySQL is
sub-optimal.

In this case, the machine looks fine.

I can't comment on queries or indexes.

Your tuning, I can.

set-variable= key_buffer=512M

This one is important; it caches data from the database in memory. How big
is your database? Is it bigger than 512 meg? If so, is there free memory on
the machine? If so, I'd recommend bumping it up as much as you can; it's
faster to get data from memory than it is from disk.


set-variable= sort_buffer=16M

This is used to sort the results of queries; it's per-connection. I believe
it is allocated on an as-needed basis. Regardless, are you doing a lot of
ORDER BY/GROUP BY in your queries? This might be a little high.


set-variable= table_cache=1024

How many tables do you have? This tells the database how many tables to keep
open. It probably won't make much of a difference lowering it.


set-variable= join_buffer=8M

This one is used to join tables where no indexes exist. In otherwords, if
you're using indexes, it won't be used.


set-variable= record_buffer=8M

This is used for reading in rows after a sort (from the sort_buffer). Again,
per client. Do you need it?


set-variable= query_cache_size=6M

This one can be a waste of memory, or a huge bonus. Queries and their result
sets are stored here. If you need to run the same query a second time, the
database just pulls the results from the cache.

There are a few issues, tho.

If you have a query,

"SELECT * FROM table_1 WHERE condition_1 = 12"

then the result of the query will be stored. But if the next statement is,
"UPDATE TABLE table_1..." then the data in the cache relating to table_1
have to be unloaded, as the UPDATE statement could have invalidated all of
it.

But, if you have some stock queries that constantly read data from tables
that, in the business logic of your application, are read-only (ie you
rarely, if ever, update the data in them), then the query cache can be a big
bonus.

Another interesting note is the query_cache_type variable. You can set it to
2 in the my.cnf file; this means that in your select statements, you add a
hint to tell the database to cache or not cache the results of the query. So
if you know a query and it's results are very dynamic, then it's not much
use to use the query cache and you can tell the database to not put it in
the query cache. The SELECT statement would look like, 'SELECT SQL_CACHE *
FROM table_1 WHERE...'. Here's the page:
http://www.mysql.com/doc/en/Query_Cache.html



Next, moving to InnoDB. I found that, properly tuned, InnoDB is almost as
fast as MyISAM. You'll want to set the innodb_buffer_pool_size as large as
possible (it's the MyISAM key_buffer) to cache as much data.

You'll need to set up a tablespace; you can put them all over your disks.
Finally, play with innodb_flush_method. I set it to O_DSYNC and got a
substantial increase in performance. Search the MySQL list-archives for
discussions on the options, or check out http://www.innodb.com


Hope that helps,
David



- Original Message -
From: "Rainer Sip" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 9:48 AM
Subject: Mysql Performance Question


I'm running a community site (Xoops) on Mysql 4.0.14.

I found that the speed of my site is slow during peak hours, when there
are 450 concurrent uers hanging on the site. Mytop showed that the
queries per second maxed at 500. I believe this could be higher,
provided that I have it running on a dedicated machine. I also noticed
the load average is very high (12+ during peak hours)

In the mid run I'm planning to mirgrate to innodb for higher concurrency
(I'm currently using myisam). However, I'm seeking suggestions in fine
tuning the parameters.

The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB
of memory. There are 4 disks running raid 0+1. Attached the my.cnf for
your easy review. Thanks a lot in advance.

Cheers,
Rainer


[client]
socket=/var/lib/mysql/mysql.sock

[client]
socket=/var/lib/mysql/mysql.sock

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
set-variable= key_buffer=512M
set-variable= max_allowed_packet=1M
set-variable= table_cache=1024
set-variable= sort_buffer=16M
set-variable= join_buffer=8M
set-variable= record_buffer=8M
set-variable= thread_cache=512
set-variable= max_connections=500
set-variable= tmp_table_size=128M
set-variable= thread_concurrency=4
set-variable= myisam_sort_buffer_size=512M
set-variable= query_cache_size=6M
#set-variable   = max_connect_errors=1000
#set-variable   = back_log=100
#log-bin
skip-innodb

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
set-v

Re: Strange results

[Joakim Ryden]

It's a BIGINT(25).

On Tuesday 21 October 2003 11:58 am, gerald_clark wrote:
> Is that a character field?
> If it is, you forgot the quotes, and the string was converted to a number.
> 16 significant digits match.
>
> Joakim Ryden wrote:
> >Hey folks -
> >
> >can anyone explain this behaviour:
> >
> >mysql> select token, spam_hits, innocent_hits from dspam_token_data where
> >uid=500 and token=14243385100413148122;
> >
> >Returns this result, which is a completely different record.
> >
> >+---+-++
> >
> >| token   | spam_hits  | innocent_hits |
> >
> >+---+-++
> >
> >| 14243385100413147136 |  0  |  1 |
> >
> >+---+-++
> >
> >??
> >
> >--Jo
>
> !DSPAM:3f9581bb310893224619726!


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

Re: C API

[Hardik Doshi]

Hi Priyanka,
 
BTW, which programming language you are using? In PHP there is a PEAR DB  utitlity 
which acts as the database abstraction layer.
 
Regards,
Hardik Doshi

walt <[EMAIL PROTECTED]> wrote:
Priyanka Gupta wrote:
> 
> Is there a way to have a common C API for MySQL and Oracle. I am writing
> some software that I would like to work with both MYSQL or Oracle as the
> backend server?
> 
> priyanka
> 
> _
> Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet
> Service. Try it FREE for one month! http://join.msn.com/?page=dept/dialup
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]

Priyanka,
This might work for you - http://otl.sourceforge.net/home.htm

walt

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


-
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

Re: Strange results

[gerald_clark]

Is that a character field?
If it is, you forgot the quotes, and the string was converted to a number.
16 significant digits match.
Joakim Ryden wrote:

Hey folks -

can anyone explain this behaviour:

mysql> select token, spam_hits, innocent_hits from dspam_token_data where
uid=500 and token=14243385100413148122;
Returns this result, which is a completely different record.

+---+-++
| token   | spam_hits  | innocent_hits |
+---+-++
| 14243385100413147136 |  0  |  1 |
+---+-++
??

--Jo

 



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

Uninstalling MySQL

[francis.fernandes]

Hi, I´ve just installed MySQL and I wanna know how to uninstall it.

I tried to look for uninstall in the manual and at MySQL WebSite but I did not found.

Thanks.

   Tag Line 
  
Francis Fernandes Ferreira   
[EMAIL PROTECTED]  tel: 
  mobile:  +55 41 3019-8630
  +55 41 9616-1370  
   
 

 
Powered by Plaxo Want a signature like this? 
 

RE: mysql Digest 21 Oct 2003 17:33:01 -0000 Issue 2672

[Prashant Pai]

Hi,

I have a table SalaryInfo as below

Salary | Department | Level
50 | R&D| Director
3  | Maintenance| Groundsman
..

I want to know what level in each department makes the highest salary
and how much that salary is? Something like:

SELECT Salary, Level, Department FROM SalaryInfo WHERE
Salary=Max(Salary)

Would using MySQL 4.1 that has support for nested select help my case?

Thanks in advance
prashant



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

Datanamic works great for attractive schema diagrams

[Jeff Weeks]

It reads a MySQL database and will even form relationships based on 
matching column names.  Totally cool.

Thanks, Victor.

On Tuesday, October 21, 2003, at 09:13 AM, Victor Pendleton wrote:

Have you tried datanamic.com?

-Original Message-
From: Jeff Weeks [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 1:50 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: How to get a nice schema diagram
Looking for an open source or commercial tool to help with database
visualization for a presentation we are to give.  Can't seem to find a
simple way to generate a nice poster of our database design.
Any suggestions?

Thanks.

--
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: C API

[walt]

Priyanka Gupta wrote:
> 
> Is there a way to have a common C API for MySQL and Oracle. I am writing
> some software that I would like to work with both MYSQL or Oracle as the
> backend server?
> 
> priyanka
> 
> _
> Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet
> Service.  Try it FREE for one month!   http://join.msn.com/?page=dept/dialup
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Priyanka,
This might work for you - http://otl.sourceforge.net/home.htm

walt

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

Re: Mysql Performance Question

[walt]

Rainer Sip wrote:
> 
> I'm running a community site (Xoops) on Mysql 4.0.14.
> 
> I found that the speed of my site is slow during peak hours, when there are 450 
> concurrent uers hanging on the site. Mytop showed that the queries per second maxed 
> at 500. I believe this could be higher, provided that I have it running on a 
> dedicated machine. I also noticed the load average is very high (12+ during peak 
> hours)
> 
> In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm 
> currently using myisam). However, I'm seeking suggestions in fine tuning the 
> parameters.
> 
> The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB of memory. 
> There are 4 disks running raid 0+1. Attached the my.cnf for your easy review. Thanks 
> a lot in advance.
> 


Rainer,
The "12+" load avg. is HIGH. What is % idle when the load average is
high? 

walt

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

Strange results

[Joakim Ryden]

Hey folks -

can anyone explain this behaviour:

mysql> select token, spam_hits, innocent_hits from dspam_token_data where
uid=500 and token=14243385100413148122;

Returns this result, which is a completely different record.

+---+-++
| token   | spam_hits  | innocent_hits |
+---+-++
| 14243385100413147136 |  0  |  1 |
+---+-++

??

--Jo


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

Re: Multithreaded?

[Mihai RUSU]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Tue, 21 Oct 2003, Greg(Bear) Casad wrote:

> All installations of mysql I have run ps -fax will show about 5 mysql
> proccess running, even at idle.

Yep.

> I compiled on a Redhat 9 box, it did find pthreads however ps -fax
> show 1 mysqld process running.

NPTL (RedHat9 comes with NPTL, among other differences between NPTL and 
old Linuxthreads was this that the threads show as a single process now).

http://people.redhat.com/drepper/nptl-design.pdf

(btw, can you get a working mysql 4.0.x statically compiled mysqld version
on RedHat9 ? I have problems with statically linked programms on NPTL
enabled glibc, including mysql. Please compile a 4.0.x version, run it and
try to just enter a session with mysql CLI and exit the session and check
if the mysqld has crashed/restarted on your exit, check the uptime by
"status" command" thanks!)

> Is there a spot to configure spare proccess? -- Or verify that it is
> indeed compiled muli-threaded??
> 
I dont know, Im very curious about that too...

- 
Mihai RUSUEmail: [EMAIL PROTECTED]
GPG : http://dizzy.roedu.net/dizzy-gpg.txtWWW: http://dizzy.roedu.net
   "Linux is obsolete" -- AST
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQE/lXWpPZzOzrZY/1QRAoaVAKCmY9lS2sho+m4Qov46HyF38ef5NgCgzOs/
lVFCmBm75xAqgpwTJd0nD5c=
=hItY
-END PGP SIGNATURE-

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

RE: Find non-unique values

[Kevin Fries]

Randy,

For "general" SQL questions, you might find something in a newsgroup,
such as comp.databases.*, but there are so many peculiarities, even
among the most compliant datbases, that it's hard to really ask a
"general" question.

As for your unique values...

> -Original Message-
> From: Randy Chrismon [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, October 21, 2003 10:15 AM
> 
> I have a table wherein one column is SUPPOSED to be unique 
> but I strongly suspect isn't. Because I had this suspicion, I 
> did not apply a unique index to the column. Assuming there 
> are no null values in that column how would I find the 
> instances of non-unique values? 

You can use a query such as :
SELECT SupposedlyUniqueColumn, count(*) 
FROM myTable
GROUP BY SupposedlyUniqueColumn
HAVING COUNT(*) > 1;

This will return all the values which occur more than once, and, for
free, also tells you how many times it occurs.
Further queries using that value can show you the particular rows.

> If I try to alter table add 
> unique... will it abort if the values are non-unique, or will 
> it drop the non-unique rows? 

Yes.  You'll get a fairly useful error message, as in the following:

create table bob ( i int );
insert into bob values ( 1 );
insert into bob values ( 2 );
insert into bob values ( 2 );
create unique index bob_ux01 on bob ( i );
ERROR 1062: Duplicate entry '2' for key 1

So you can actually make progress this way, since you've got at least
one duplicated key to go tackle.



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

RE: Find non-unique values

[Dan Lampkin]

You can use the group by and having clause:

use test;

drop table if exists duptest;

create table duptest(
x int
, z varchar(25)
) type=innodb;

insert into duptest(x, z) values(1, 'Val1');
insert into duptest(x, z) values(2, 'Val2');
insert into duptest(x, z) values(3, 'Val3');
insert into duptest(x, z) values(4, 'Val3');
insert into duptest(x, z) values(5, 'Val3');
insert into duptest(x, z) values(6, 'Val4');
insert into duptest(x, z) values(7, 'Val4');
insert into duptest(x, z) values(8, 'Val5');
insert into duptest(x, z) values(9, 'Val6');
insert into duptest(x, z) values(10, 'Val6');

select z, count(*) as numrecords
from duptest
group by z
having numrecords > 1;

This shows us that Val3, Val4, and Val6 are duplicated.  You can leave
off the having clause and order by and see the counts of all of them (I
added an order by so that the ones with 1 show up first) as follows:

select z, count(*) as numrecords
from duptest
group by z
order by numrecords;

hth,

Dan

-Original Message-
From: Randy Chrismon [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 21, 2003 12:15 PM
To: [EMAIL PROTECTED]
Subject: Find non-unique values


I have a table wherein one column is SUPPOSED to be unique but I
strongly suspect isn't. Because I had this suspicion, I did not apply
a unique index to the column. Assuming there are no null values in
that column how would I find the instances of non-unique values? If I
try to alter table add unique... will it abort if the values are
non-unique, or will it drop the non-unique rows? (MyISAM tables).
There is a valid primary key on the table but (obviously) it does not
include the column in question.
Also, as this is really a SQL question (I think), is there a good
mailing list, forum, or web site devoted to SQL questions --
particularly the dialect of SQL spoken by MySQL?
Thanks.
Randy

-- 
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: Find non-unique values

[Peter Brawley]

Assuming 'id' is an identifying column and 'dupe_col' is the column with
duplicate values...

SELECT id, COUNT( dupe_col ) AS cnt
FROM tbl
GROUP BY dupe_col
HAVING cnt > 1

HTH

PB

-
  - Original Message -
  From: Randy Chrismon
  To: [EMAIL PROTECTED]
  Sent: Tuesday, October 21, 2003 12:14 PM
  Subject: Find non-unique values



  I have a table wherein one column is SUPPOSED to be unique but I
  strongly suspect isn't. Because I had this suspicion, I did not apply
  a unique index to the column. Assuming there are no null values in
  that column how would I find the instances of non-unique values? If I
  try to alter table add unique... will it abort if the values are
  non-unique, or will it drop the non-unique rows? (MyISAM tables).
  There is a valid primary key on the table but (obviously) it does not
  include the column in question.
  Also, as this is really a SQL question (I think), is there a good
  mailing list, forum, or web site devoted to SQL questions --
  particularly the dialect of SQL spoken by MySQL?
  Thanks.
  Randy

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

Re: Mysql Performance Question

[Rainer Sip]

Thanks a lot guys.

Haydies:

>Just out of wondering, are you using PHP and if so do you use
mysql_pconnect
>rather then mysql_connect because that would really speed things up.

I tired pconnect before but it didn't help but using up all the available
memory. It speeds up things until the disks started swapping, which happened
in a few hours after the server was up and running.

Gabriel:

>Can you make changes to the site's software? Perhaps more aggressive
>caching would help. It looks like Xoops is using PHP, so you could try
>using something like php memcache (http://lucifer.intercosmos.net/) or
>Turck MMCache (http://sourceforge.net/projects/turck-mmcache) or any
>other PHP cache / accelerator product.

I'm now using PHPA to cache the scripts.

>Will Xoops take advantage of InnoDB's transactional capabilities?

I probably don't need the transactional capabilities of innodb. I'm just
thinking the row level locking of innodb will bring some more speed to the
server.

>Can you add more RAM? What type of disks are you using in the RAID
>setup? Is it hardware or software RAID?

Due to limited budget I'm unable to add more memory in the short future
(really don't want to replace 4 512MB modules with 1G modules). In fact
there are always about 5-800MB of unused/buffered memory. The machine is
equipped with hardware raid, and 4 19GB SCSI disks.

Cheers,
Rainer


- Original Message - 
From: "Rainer Sip" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 5:48 PM
Subject: Mysql Performance Question


I'm running a community site (Xoops) on Mysql 4.0.14.

I found that the speed of my site is slow during peak hours, when there are
450 concurrent uers hanging on the site. Mytop showed that the queries per
second maxed at 500. I believe this could be higher, provided that I have it
running on a dedicated machine. I also noticed the load average is very high
(12+ during peak hours)

In the mid run I'm planning to mirgrate to innodb for higher concurrency
(I'm currently using myisam). However, I'm seeking suggestions in fine
tuning the parameters.

The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB of
memory. There are 4 disks running raid 0+1. Attached the my.cnf for your
easy review. Thanks a lot in advance.

Cheers,
Rainer


[client]
socket=/var/lib/mysql/mysql.sock

[client]
socket=/var/lib/mysql/mysql.sock

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
set-variable= key_buffer=512M
set-variable= max_allowed_packet=1M
set-variable= table_cache=1024
set-variable= sort_buffer=16M
set-variable= join_buffer=8M
set-variable= record_buffer=8M
set-variable= thread_cache=512
set-variable= max_connections=500
set-variable= tmp_table_size=128M
set-variable= thread_concurrency=4
set-variable= myisam_sort_buffer_size=512M
set-variable= query_cache_size=6M
#set-variable   = max_connect_errors=1000
#set-variable   = back_log=100
#log-bin
skip-innodb

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
set-variable= max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable= key_buffer=256M
set-variable= sort_buffer=124M
set-variable= read_buffer=2M
set-variable= write_buffer=2M

[myisamchk]
set-variable= key_buffer=256M
set-variable= sort_buffer=256M
set-variable= read_buffer=8M
set-variable= write_buffer=4M

[mysqlhotcopy]
interactive-timeout



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

RE: Multithreaded?

[Dathan Vance Pattishall]

On red hat systems threads show up in a normal processlist. Thoese are
threads that your seeing, some for connections others to do some innodb
tasks and/or replication. 

- Dathan Vance Pattishall
  - Sr. Programmer and mySQL DBA for FriendFinder Inc.
  - http://friendfinder.com/go/p40688


-->-Original Message-
-->From: Greg(Bear) Casad [mailto:[EMAIL PROTECTED]
-->Sent: Tuesday, October 21, 2003 10:14 AM
-->To: [EMAIL PROTECTED]
-->Subject: Multithreaded?
-->
-->All installations of mysql I have run ps -fax will show about 5 mysql
-->proccess running, even at idle.
-->I compiled on a Redhat 9 box, it did find pthreads however ps
-fax
-->show 1 mysqld process running.
-->Is there a spot to configure spare proccess? -- Or verify that it is
-->indeed compiled muli-threaded??



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

Find non-unique values

[Randy Chrismon]

I have a table wherein one column is SUPPOSED to be unique but I
strongly suspect isn't. Because I had this suspicion, I did not apply
a unique index to the column. Assuming there are no null values in
that column how would I find the instances of non-unique values? If I
try to alter table add unique... will it abort if the values are
non-unique, or will it drop the non-unique rows? (MyISAM tables).
There is a valid primary key on the table but (obviously) it does not
include the column in question.
Also, as this is really a SQL question (I think), is there a good
mailing list, forum, or web site devoted to SQL questions --
particularly the dialect of SQL spoken by MySQL?
Thanks.
Randy

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

Multithreaded?

[Greg\(Bear\) Casad]

All installations of mysql I have run ps -fax will show about 5 mysql proccess 
running, even at idle.
I compiled on a Redhat 9 box, it did find pthreads however ps -fax show 1 mysqld 
process running.
Is there a spot to configure spare proccess? -- Or verify that it is indeed compiled 
muli-threaded??

Re: Mysql Performance Question

[bluejack]

On Wed, 22 Oct 2003 00:48:29 +0800, Rainer Sip <[EMAIL PROTECTED]> wrote:

In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm currently using myisam). However, I'm seeking suggestions in fine tuning the parameters.

I know you want to tune the parameters, but if you haven't
already, I'd also recommend taking a quick survey of your
most common queries, making sure there is no low-hanging
fruit available in terms of tuning your queries/indices/etc.
--bluejack, who knows very little about tuning the parameters.

 

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

Re: Mysql Performance Question

[Gabriel Ricard]

On Tuesday, October 21, 2003, at 12:48  PM, Rainer Sip wrote:

I'm running a community site (Xoops) on Mysql 4.0.14.

I found that the speed of my site is slow during peak hours, when 
there are 450 concurrent uers hanging on the site. Mytop showed that 
the queries per second maxed at 500. I believe this could be higher, 
provided that I have it running on a dedicated machine. I also noticed 
the load average is very high (12+ during peak hours)
Can you make changes to the site's software? Perhaps more aggressive 
caching would help. It looks like Xoops is using PHP, so you could try 
using something like php memcache (http://lucifer.intercosmos.net/) or 
Turck MMCache (http://sourceforge.net/projects/turck-mmcache) or any 
other PHP cache / accelerator product.

In the mid run I'm planning to mirgrate to innodb for higher 
concurrency (I'm currently using myisam). However, I'm seeking 
suggestions in fine tuning the parameters.
Will Xoops take advantage of InnoDB's transactional capabilities?

The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB 
of memory. There are 4 disks running raid 0+1. Attached the my.cnf for 
your easy review. Thanks a lot in advance.
Can you add more RAM? What type of disks are you using in the RAID 
setup? Is it hardware or software RAID?

- Gabriel

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

Mysql Performance Question

[Rainer Sip]

I'm running a community site (Xoops) on Mysql 4.0.14.

I found that the speed of my site is slow during peak hours, when there are 450 
concurrent uers hanging on the site. Mytop showed that the queries per second maxed at 
500. I believe this could be higher, provided that I have it running on a dedicated 
machine. I also noticed the load average is very high (12+ during peak hours)

In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm 
currently using myisam). However, I'm seeking suggestions in fine tuning the 
parameters.

The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB of memory. 
There are 4 disks running raid 0+1. Attached the my.cnf for your easy review. Thanks a 
lot in advance.

Cheers,
Rainer


[client]
socket=/var/lib/mysql/mysql.sock

[client]
socket=/var/lib/mysql/mysql.sock

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
set-variable= key_buffer=512M
set-variable= max_allowed_packet=1M
set-variable= table_cache=1024
set-variable= sort_buffer=16M
set-variable= join_buffer=8M
set-variable= record_buffer=8M
set-variable= thread_cache=512
set-variable= max_connections=500
set-variable= tmp_table_size=128M
set-variable= thread_concurrency=4
set-variable= myisam_sort_buffer_size=512M
set-variable= query_cache_size=6M
#set-variable   = max_connect_errors=1000
#set-variable   = back_log=100
#log-bin
skip-innodb

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
set-variable= max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable= key_buffer=256M
set-variable= sort_buffer=124M
set-variable= read_buffer=2M
set-variable= write_buffer=2M

[myisamchk]
set-variable= key_buffer=256M
set-variable= sort_buffer=256M
set-variable= read_buffer=8M
set-variable= write_buffer=4M

[mysqlhotcopy]
interactive-timeout

Re: Recovering data from ISD/ISM/frm files

[gerald_clark]

Check the ownership and permissions on the files.
Since you copied them over, perhaps they are owned by root, and not mysql.
You did shut down the server before copyng the files?
Harrell, Roger wrote:

Ok, well I have managed to recover the data from my backup, but now when I
try to update the table, or insert rows I get the error:
table 'tablename' is read only. 

trying to unlock the table doesn't seem to help. I have stopped and
restarted mysql.
Thanks,
Roger
-Original Message-
From: Harrell, Roger [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 7:59 AM
To: [EMAIL PROTECTED]
Subject: Recovering data from ISD/ISM/frm files
I need to restore a table from backup copies of the ISD/ISM/frm files. I
can't seem to get it to work. I have copied over the files, I have tried
restoring using isamchk. I'm sure it has something to do with the temporary
tables, but I can't find anything in the docs about this. All I keep getting
is the current messed up data. I have a clean backup from earlier today, but
can't get them to restore. Any thoughts/help appreciated.
Thanks,
Roger
 



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

RE: Recovering data from ISD/ISM/frm files

[Harrell, Roger]

Ok, got it worked out. I had to dump, drop and recreate the table, but it's
working.

Roger

-Original Message-
From: Harrell, Roger [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 8:49 AM
To: [EMAIL PROTECTED]
Subject: RE: Recovering data from ISD/ISM/frm files


Ok, well I have managed to recover the data from my backup, but now when I
try to update the table, or insert rows I get the error:
table 'tablename' is read only. 

trying to unlock the table doesn't seem to help. I have stopped and
restarted mysql.

Thanks,
Roger

-Original Message-
From: Harrell, Roger [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 7:59 AM
To: [EMAIL PROTECTED]
Subject: Recovering data from ISD/ISM/frm files


I need to restore a table from backup copies of the ISD/ISM/frm files. I
can't seem to get it to work. I have copied over the files, I have tried
restoring using isamchk. I'm sure it has something to do with the temporary
tables, but I can't find anything in the docs about this. All I keep getting
is the current messed up data. I have a clean backup from earlier today, but
can't get them to restore. Any thoughts/help appreciated.

Thanks,
Roger

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

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

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

RE: How to get a nice schema diagram

[Victor Pendleton]

Have you tried datanamic.com?

-Original Message-
From: Jeff Weeks [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 1:50 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: How to get a nice schema diagram


Looking for an open source or commercial tool to help with database 
visualization for a presentation we are to give.  Can't seem to find a 
simple way to generate a nice poster of our database design.

Any suggestions?

Thanks.


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

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

How to get a nice schema diagram

[Jeff Weeks]

Looking for an open source or commercial tool to help with database 
visualization for a presentation we are to give.  Can't seem to find a 
simple way to generate a nice poster of our database design.

Any suggestions?

Thanks.

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

RE: Recovering data from ISD/ISM/frm files

[Harrell, Roger]

Ok, well I have managed to recover the data from my backup, but now when I
try to update the table, or insert rows I get the error:
table 'tablename' is read only. 

trying to unlock the table doesn't seem to help. I have stopped and
restarted mysql.

Thanks,
Roger

-Original Message-
From: Harrell, Roger [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 7:59 AM
To: [EMAIL PROTECTED]
Subject: Recovering data from ISD/ISM/frm files


I need to restore a table from backup copies of the ISD/ISM/frm files. I
can't seem to get it to work. I have copied over the files, I have tried
restoring using isamchk. I'm sure it has something to do with the temporary
tables, but I can't find anything in the docs about this. All I keep getting
is the current messed up data. I have a clean backup from earlier today, but
can't get them to restore. Any thoughts/help appreciated.

Thanks,
Roger

-- 
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: GEMINI

[Chris Nolan]

Hi!

Thanks to the god of multiversioned databases, creator of InnoDB and 
all-round good human being
Heikki, I can tell you the following things with confidence:

1. InnoDB is the fastest disk-based transactional database engine on the 
planet, period. No ifs,
no buts, no conditions at all.
2. InnoDB is solid. Unless you do things like have two mysqld processes 
access the same table
space over a Sun NFS setup at the same time, things just work.
3. Hot backups are an option you can acquire, while still paying much 
less than you would for
any other DB.

Admittedly, I know very little about Gemini other than what's in some 
old versions of the MySQL
documentation (anyone want to point me in the direction of some solid 
info on what made this
table type stand out?). However, from what I can tell, Gemini was closer 
to MS SQL and DB2,
in that it depended on row locks for almost every operation. InnoDB 
doesn't depend on row
locks anywhere near as much (thanks to Oracle-style consistant reads) 
and the lack of phantoms
is quite nice. Foreign keys are also nice.

If performance is a serious issue, I'd recommend you set yourself up a 
test box, start dumping
data into it and throwing some queries at it (perhaps by setting up 
replication from the current
Gemini box to an InnoDB setup?).

Hope my ramblings are of some use! We currently host 20 GB of random 
garbage in InnoDB
tables, and the database takes up less time than my Samba installation!

Regards,

Chris

Nihal wrote:

Can anyone tell me what happened to GEMINI?

We've been customers of MySQL for a while and about two years ago
started using Nusphere's version to take advantage of their row level
locking/ACID transaction safe table type GEMINI. Things went well for a
while but one day I came back for help and poof, Nusphere has moved all
support to Russia, then a couple weeks later no more support exists. I
understand this was due to a violation on their part of the GPL.
So here I am stuck with 80GB's of GEMINI data and an aging version of
MySQL prone to crash every couple of months.
Does anyone know, are the GEMINI developers from Nusphere somewhere else
legally continuing this development?
If not can someone give me some migration suggestions? I've looked at
InnoDB some, but am worried, will it provide equal performance to
GEMINI? Also to migrate this I have found one copy of MySQL, version
4.00beta, that has support for both GEMINI and INNODB, will using this
version cause problems for me?
Thanks,
Nihal 

 



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

Re: Resume indexing after powerloss?

[Dan Nelson]

In the last episode (Oct 21), Peer Reiser said:
> Someone knows if its possible to resume indexing a base where it left
> due to a power failure? The process was copying to tmp file when the
> power went away, having achieved 5GB of total 6GB in 6 days..

Nope; you'll have to start over.  That's an awful long time to generate
indexes.  You may benefit from raising key_buffer_size and
myisam_sort_buffer_size during the index operation.

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Recovering data from ISD/ISM/frm files

[Harrell, Roger]

I need to restore a table from backup copies of the ISD/ISM/frm files. I
can't seem to get it to work. I have copied over the files, I have tried
restoring using isamchk. I'm sure it has something to do with the temporary
tables, but I can't find anything in the docs about this. All I keep getting
is the current messed up data. I have a clean backup from earlier today, but
can't get them to restore. Any thoughts/help appreciated.

Thanks,
Roger

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

Digest Problem?

[Paul Fine]

I cannot seem to get this list as a digest :(

I have unsubscribed and re-subscribed multiple times choosing the digest
option to no avail. I keep receiving single e-mails.

Thanks for any help!


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

Re: InnoDB or OS restriction?

[Heikki Tuuri]

Alex,

because 512 MB is not an InnoDB or MySQL restriction, it must be an OS
restriction :).

I assume you have not allocated 1.5 GB of MySQL key_buffer.

Best regards,

Heikki
Innobase Oy
http://www.innodb.com
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM tables


- Alkuperäinen viesti - 
Lähettäjä: "Varshavchick Alexander" <[EMAIL PROTECTED]>
Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>
Kopio: <[EMAIL PROTECTED]>
Lähetetty: Tuesday, October 21, 2003 2:55 PM
Aihe: Re: InnoDB or OS restriction?


> Hi Heikki,
>
> here is a snip from the original posting which you probably have
> overlooked:
>
> > > > The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config
are
> > > > setted to 1536M
>
> Regards
>
> 
> Alexander Varshavchick, Metrocom Joint Stock Company
> Phone: (812)118-3322, 118-3115(fax)
>
> On Mon, 20 Oct 2003, Heikki Tuuri wrote:
>
> > Date: Mon, 20 Oct 2003 22:05:07 +0300
> > From: Heikki Tuuri <[EMAIL PROTECTED]>
> > To: [EMAIL PROTECTED]
> > Subject: Re: InnoDB or OS restriction?
> >
> > Alex,
> >
> > in FreeBSD user process memory space is often restricted to 512 MB. You
have
> > to reconfigure or recompile the FreeBSD kernel to increase that limit.
> >
> > Best regards,
> >
> > Heikki Tuuri
> > Innobase Oy
> > http://www.innodb.com
> > Foreign keys, transactions, and row level locking for MySQL
> > InnoDB Hot Backup - a hot backup tool for InnoDB: now
> > also backs up your MyISAM tables
> >
> >
> > - Original Message -
> > From: "alex" <[EMAIL PROTECTED]>
> > Newsgroups: mailing.database.myodbc
> > Sent: Monday, October 20, 2003 1:30 PM
> > Subject: Re: InnoDB or OS restriction?
> >
> >
> > > Hi again,
> > >
> > > as there was not a single answer to my question I can imagine that no
one
> > > encountered the same issue, but anyways, can there be any hints? First
of
> > > all, are there any means of looking at mysql memory allocation list
> > > grouped by some major parts - for example,
> > > innodb main pool - can be retrieved from innodb monitor
> > > innodb additional pool - can be retrieved from innodb monitor
> > > myisam main cache - ...?
> > > myisam sort buffer - ...?
> > > memory allocated from OS - ...?
> > >
> > > The questiion is why mysql is trying to allocate memory via malloc
from OS
> > > while the innodb additional pool is occupied only by 50%? Or is it
myisam
> > > buffer that mysql is trying to extend?
> > >
> > > Regards
> > >
> > > ---
> > > Alex
> > >
> > > On Tue, 14 Oct 2003, alex wrote:
> > >
> > > > Hi people,
> > > >
> > > > I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2
> > (server
> > > > has 4G phisical memory), and occasionally mysql traps with the
message:
> > > >
> > > > InnoDB: Fatal error: cannot allocate 1064960 bytes of
> > > > InnoDB: memory with malloc! Total allocated memory
> > > > InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12
> > > > InnoDB: Cannot continue operation!
> > > >
> > > > The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config
are
> > > > setted to 1536M, and the second one is that each time it happens,
while
> > > > the number of bytes reported to cannot be allocated is different,
total
> > > > memory allocated by InnoDB is exactly the same - 513951016 bytes.
Which
> > > > restrictions are the cause of this - InnoDB's or FreeBSD's?
> > > >
> > > > Thanks in advance
> > > >
> > > > 
> > > > Alex
> > > >
> > > > --
> > > > MySQL General Mailing List
> > > > For list archives: http://lists.mysql.com/mysql
> > > > To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
> > > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> >
> >
> >
> > --
> > 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: Multiple Image inserts into a MySQL BLOB column

[gerald_clark]

Make sure that the mysql server has read permission for the file.

Zafar wrote:

Hello

Having trouble inserting images into a BLOB column. No problems doing 
this 'one at a time' via
a third party MySQL GUI Manager, but I need to create an indexed table 
containing some
1K + images. Using the recommeded method from the MySQL manaual ie.

UPDATE tbl_name
SET blob_column=LOAD_FILE("/tmp/picture")
WHERE id=1;   
only sets the blob field to NULL (0 bytes) ! - even where an image is 
stored perfectly well.
There has to a SAFE METHOD of doing these image inserts from within a 
'while' 'for' or some other loop statement in a shell script or perl 
routine. Appreciate enlighenment on this topic from
someone who has been down this road and figured it out.

Rgds, ZCH





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

Moving to Linux and NetApps

[Barry Cornelius]

Previously, we have installed MySQL 4.0.x server on a Solaris box where
the MySQL binaries, MySQL databases and MySQL logs are stored on local
discs.  We now want to move to a Linux box (Redhat 9) where the MySQL
binaries, MySQL databases and MySQL logs are provided by a NetApps server
using NFS.

Are there problems with doing this?
Are people using NetApps to store the databases for MySQL?
Are people using Linux and NetApps together for MySQL?

Thanks.

--
Barry Cornelius   Telephone: (0191 or +44 191) 334 2757
User Services, Information Technology Service, Office: 334 2700
Science Site, University of Durham, Durham, DH1 3LE, UK   Fax: 334 2701
www.dur.ac.uk/barry.cornelius/ [EMAIL PROTECTED]

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

Re: What is SQL Standard: ISNUL()L, IFNULL() ?

[Victoria Reznichenko]

"Holly Chamberlain" <[EMAIL PROTECTED]> wrote:
> 
> Does anyone have a good site that contains the current SQL standard? Or
> does anyone know is ISNULL() and IFNULL(), and similar null testing
> functions, standard SQL or extensions to SQL?

No, there are no such functions in the SQL-99. In the standard you can find IS [NOT] 
NULL to see if value is NULL or not.

> 
> Thanks! I'm porting from Sybase SQLAnywhere to MySQL and just found what
> worked in Sybase (ISNULL()) now appears to have to be IFNULL() --- this
> is a real bummer because I didn't want to have to change ANY of my
> application code..
> 


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





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

RE: C API

[Brad Teale]

There is a C++ package called OTL (http://otl.sourceforge.net/home.htm).
It supports both MySQL through MyODBC, and Oracle.  It works great with
Oracle applications, but we have not used it with MySQL.

Thanks,
Brad Teale
Universal Weather and Aviation, Inc.
mailto:[EMAIL PROTECTED]
713-944-1440 ext. 3623 

Arrange things so that a person needs to know nothing, and you'll end
up with a person who is capable of nothing. -- K. Brown

-Original Message-
From: Priyanka Gupta [mailto:[EMAIL PROTECTED]
Sent: Monday, October 20, 2003 7:14 PM
To: [EMAIL PROTECTED]
Subject: C API


Is there a way to have a common C API for MySQL and Oracle. I am writing 
some software that I would like to work with both MYSQL or Oracle as the 
backend server?

priyanka

_
Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet 
Service.  Try it FREE for one month!   http://join.msn.com/?page=dept/dialup


-- 
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: export to textfile -solved :)

[Bernd Tannenbaum]

Hi and thx to all who answered :)

Okay, finally found my mistake with ur help.
One can add the needed options within the "select" statement, not within the 
"mysql -e" statement as i tried.

Working solution is now:
./mysql --skip-column-names -e "select ek_satzart,[...] into outfile 'File' 
FIELDS TERMINATED BY ';' from table" dbname --password=

Thx all for help,
Bernd





-- 

One OS to rule them all, one OS to find them.
One OS to bring them all, and in the darkness bind them
In the land of Redmond, where the shadows lie.

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

Re: export to textfile

[Rory McKinley]

Hi Bernd

Will the query be static? E.g. could you put the SELECT ..INTO OUTFILE query
into a batch file and just call that batch file from the command line?

mysql -u user -p < arb_file.bat

Obviously this will be a little more of a problem if you generate the query
dynamically each time.

Rory McKinley
Nebula Solutions
+27 82 857 2391
[EMAIL PROTECTED]
"There are 10 kinds of people in this world,
those who understand binary and those who don't" (Unknown)
- Original Message - 
From: "Bernd Tannenbaum" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 1:40 PM
Subject: Re: export to textfile


Helloand ty for the fast answers, but

Am Dienstag, 21. Oktober 2003 13:10 schrieben Sie:
> Hi Bernd
> If you are just interested in dumping the data in CSV (or whatever) format
> you can do it like so:
> SELECT *
> INTO OUTFILE 'arb_file.csv' FIELDS TERMINATED BY ',' LINE TERMINATED BY
> '\n' FROM  arb_table
> WHERE arb_conditions

Well, my problem is i need to export from a script (currently bash-script in
Linux) so im looking for the options to give with "mysql -e".
What u wrote in ur mail, i found too. But how do i give the "FIELDS
TERMINATED
BY" option in the command line of a script.

Example for import (working fine):
./mysqlimport --local --fields-terminated-by=';'
--columns=record_type,...[...] dbname /PATH/file --password=xxx

Now the export (not working)
./mysql FIELDS TEMINATED BY ';' --skip-column-names -e "select
ek_satzart,[...] from table" dbname > /PATH/file --password=

See, me wants to add options like "Fields terminated by" or other stuff to
the
command-line-export and i cannot find an explanation of Synatx here. In ur
example u just export from within the db which is explained fine in the
dokumentation but not what i need

Hope i made the problem more clear now.
Ty,
Bernd








-- 

One OS to rule them all, one OS to find them.
One OS to bring them all, and in the darkness bind them
In the land of Redmond, where the shadows lie.

-- 
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: InnoDB or OS restriction?

[Varshavchick Alexander]

Hi Heikki,

here is a snip from the original posting which you probably have
overlooked:

> > > The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are
> > > setted to 1536M

Regards


Alexander Varshavchick, Metrocom Joint Stock Company
Phone: (812)118-3322, 118-3115(fax)

On Mon, 20 Oct 2003, Heikki Tuuri wrote:

> Date: Mon, 20 Oct 2003 22:05:07 +0300
> From: Heikki Tuuri <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Subject: Re: InnoDB or OS restriction?
>
> Alex,
>
> in FreeBSD user process memory space is often restricted to 512 MB. You have
> to reconfigure or recompile the FreeBSD kernel to increase that limit.
>
> Best regards,
>
> Heikki Tuuri
> Innobase Oy
> http://www.innodb.com
> Foreign keys, transactions, and row level locking for MySQL
> InnoDB Hot Backup - a hot backup tool for InnoDB: now
> also backs up your MyISAM tables
>
>
> - Original Message -
> From: "alex" <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.myodbc
> Sent: Monday, October 20, 2003 1:30 PM
> Subject: Re: InnoDB or OS restriction?
>
>
> > Hi again,
> >
> > as there was not a single answer to my question I can imagine that no one
> > encountered the same issue, but anyways, can there be any hints? First of
> > all, are there any means of looking at mysql memory allocation list
> > grouped by some major parts - for example,
> > innodb main pool - can be retrieved from innodb monitor
> > innodb additional pool - can be retrieved from innodb monitor
> > myisam main cache - ...?
> > myisam sort buffer - ...?
> > memory allocated from OS - ...?
> >
> > The questiion is why mysql is trying to allocate memory via malloc from OS
> > while the innodb additional pool is occupied only by 50%? Or is it myisam
> > buffer that mysql is trying to extend?
> >
> > Regards
> >
> > ---
> > Alex
> >
> > On Tue, 14 Oct 2003, alex wrote:
> >
> > > Hi people,
> > >
> > > I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2
> (server
> > > has 4G phisical memory), and occasionally mysql traps with the message:
> > >
> > > InnoDB: Fatal error: cannot allocate 1064960 bytes of
> > > InnoDB: memory with malloc! Total allocated memory
> > > InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12
> > > InnoDB: Cannot continue operation!
> > >
> > > The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are
> > > setted to 1536M, and the second one is that each time it happens, while
> > > the number of bytes reported to cannot be allocated is different, total
> > > memory allocated by InnoDB is exactly the same - 513951016 bytes. Which
> > > restrictions are the cause of this - InnoDB's or FreeBSD's?
> > >
> > > Thanks in advance
> > >
> > > 
> > > Alex
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
>
>
> --
> 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: WANTED: Betatesters for a MySQL Developer Tool

[Martijn Tonies]

Hi Minky,

From: <[EMAIL PROTECTED]>


> Hi
> Regards
> Minky

You're email address fails - can you re-contact me with a valid
email address?


With regards,

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


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

Re: export to textfile

[Bernd Tannenbaum]

Helloand ty for the fast answers, but

Am Dienstag, 21. Oktober 2003 13:10 schrieben Sie:
> Hi Bernd
> If you are just interested in dumping the data in CSV (or whatever) format
> you can do it like so:
> SELECT *
> INTO OUTFILE 'arb_file.csv' FIELDS TERMINATED BY ',' LINE TERMINATED BY
> '\n' FROM  arb_table
> WHERE arb_conditions

Well, my problem is i need to export from a script (currently bash-script in 
Linux) so im looking for the options to give with "mysql -e".
What u wrote in ur mail, i found too. But how do i give the "FIELDS TERMINATED 
BY" option in the command line of a script.

Example for import (working fine):
./mysqlimport --local --fields-terminated-by=';' 
--columns=record_type,...[...] dbname /PATH/file --password=xxx

Now the export (not working)
./mysql FIELDS TEMINATED BY ';' --skip-column-names -e "select 
ek_satzart,[...] from table" dbname > /PATH/file --password=

See, me wants to add options like "Fields terminated by" or other stuff to the 
command-line-export and i cannot find an explanation of Synatx here. In ur 
example u just export from within the db which is explained fine in the 
dokumentation but not what i need

Hope i made the problem more clear now.
Ty,
Bernd








-- 

One OS to rule them all, one OS to find them.
One OS to bring them all, and in the darkness bind them
In the land of Redmond, where the shadows lie.

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

RE: SELECT 9 BETWEEN 1 AND 0

[Tom Horstmann]

> > The question is how can I use some sort of statement using 
> mysql and php
> > to select all rows between 1 and 0 NOT 0 and 9 like the 
> above statement
> > performs.

Sure, an option was nicer, but isn't the following possible?

SELECT ... WHERE ... AND ( x BETWEEN 1 AND 9 OR x BETWEEN 0 AND 1 )

Regards,

TomH


--
PROSOFT EDV-Lösungen GmbH & Co. KG
Geschäftsführer: Axel-Wilhelm Wegmann
AG Regensburg HRA 6608 USt.183/68311
Verwaltung : 93053 Regensburg, Stadlerstraße 13
office : 93049 Regensburg, Ladehofstraße 28
www: http://www.proSoft-Edv.de
email  : [EMAIL PROTECTED]
phone  : +49 941 / 78 88 7 - 121
fax: +49 941 / 78 88 7 - 20
cellphone  : +49 174 / 41 94 97 0

--


> -Original Message-
> From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, October 21, 2003 11:11 AM
> To: Steven Ducat; [EMAIL PROTECTED]
> Subject: Re: SELECT 9 BETWEEN 1 AND 0
> 
> 
> I am sorry to say that your question is not clear.
> Why don't you send us a simple Table with few data and what 
> you want to
> achieve in simple terms? I mean a more clear question!!!
> Have tried using IN().
> In your subject, you said 9 BETWEEN 1AND 0? I am confused.
> 
> 
> Thanks
> Emery
> - Original Message -
> From: "Steven Ducat" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, October 21, 2003 08:12
> Subject: SELECT 9 BETWEEN 1 AND 0
> 
> 
> > I am trying to create a select query to find the post town 
> of a users
> > post code. I am using Royal Mails (UK) post town gazetteer. 
> UK Postcode
> > (eg. RH6 9XJ). The first column contains the first half of 
> the post code
> > (eg. RH6) and the next column holds the range of the second 
> half (eg.
> > 2AA-6PP).
> >
> > I have some code as follows:
> >  > $pc = explode(" ","RH6 9XJ");
> >
> > "SELECT postTown, postCode, SUBSTRING(postSector,1,3) as a,
> > SUBSTRING(postSector,5,3) as b, postCounty FROM postCode 
> WHERE postCode
> > = \"".$pc[0]."\" AND \"".$pc[1]."\" BETWEEN 
> SUBSTRING(postSector,1,3)
> > AND SUBSTRING(postSector,5,3)";
> > ?>
> >
> > This will return 1 row for example if the first column was 
> "RH6" and the
> > second column was "8ZZ-9ZZ".
> >
> > But in the post town list the range goes from 1 - 0 (eg. 1AA-0ZZ) so
> > what I find is if I use the postcode "RH6 9XJ" and a first 
> column of RH6
> > and a second column of 7AA-0BW it will not return the row 
> as it does not
> > count from 1 - 0.
> >

> >
> >
> > What I really need is some sort of function where I can set 
> the range
> > that the between option sorts from.
> >
> > I have been trying to solve this for some weeks now.
> >
> > Thank You.
> >
> > Steve.
> >
> >
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=mysql-list@> 7thweb.de
> 


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

FW: SELECT 9 BETWEEN 1 AND 0

[Andy Eastham]

This went direct and not to the list.

Andy

-Original Message-
From: Andy Eastham [mailto:[EMAIL PROTECTED]
Sent: 21 October 2003 08:19
To: Steven Ducat
Subject: RE: SELECT 9 BETWEEN 1 AND 0


Steve,

I'd add an extra column with "modified code" in it, where I subtracted 1
from the number bit in the second field,  So 1 would become 0, and 0 would
become 9.

I'd search against that field ( modifying my search terms accordingly), and
retrieve the proper value from the original column.

Being from the UK and knowing how these codes work, I think I would also
break the code up into four columns (eg from RH6 9XJ, ie

1 - first two letters of first field eg "RH",
2 - one or two numbers of first field eg "6",
3 - one number of second field (this is - the one I'd modify as above) eg
"9"
4 - two letters from second field eg XZ

Then add the extra modified field 3.

I'd index all of these fields.  Because you wouldn't have to perform
substring searches on them, this should be a lot faster.

Hope this helps,

Andy

> -Original Message-
> From: Steven Ducat [mailto:[EMAIL PROTECTED]
> Sent: 21 October 2003 07:13
> To: [EMAIL PROTECTED]
> Subject: SELECT 9 BETWEEN 1 AND 0
>
>
> I am trying to create a select query to find the post town of a users
> post code. I am using Royal Mails (UK) post town gazetteer. UK Postcode
> (eg. RH6 9XJ). The first column contains the first half of the post code
> (eg. RH6) and the next column holds the range of the second half (eg.
> 2AA-6PP).
>
> I have some code as follows:
>  $pc = explode(" ","RH6 9XJ");
>
> "SELECT postTown, postCode, SUBSTRING(postSector,1,3) as a,
> SUBSTRING(postSector,5,3) as b, postCounty FROM postCode WHERE postCode
> = \"".$pc[0]."\" AND \"".$pc[1]."\" BETWEEN SUBSTRING(postSector,1,3)
> AND SUBSTRING(postSector,5,3)";
> ?>
>
> This will return 1 row for example if the first column was "RH6" and the
> second column was "8ZZ-9ZZ".
>
> But in the post town list the range goes from 1 - 0 (eg. 1AA-0ZZ) so
> what I find is if I use the postcode "RH6 9XJ" and a first column of RH6
> and a second column of 7AA-0BW it will not return the row as it does not
> count from 1 - 0.
>
> The question is how can I use some sort of statement using mysql and php
> to select all rows between 1 and 0 NOT 0 and 9 like the above statement
> performs.
>
>
> What I really need is some sort of function where I can set the range
> that the between option sorts from.
>
> I have been trying to solve this for some weeks now.
>
> Thank You.
>
> Steve.
>
>



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

Re: export to textfile

[Victoria Reznichenko]

Bernd Tannenbaum <[EMAIL PROTECTED]> wrote:
> 
> Have only a small problem with my mysql and hope that i can get a lil hint 
> from ya how to go on.
> Currently i`m importing Log-files in the mysql-database with a cronjob 
> (bashscript in Linux). After some processing in the db itself i try now to 
> export the results into a textfile.
> But i must be blind reading the mysql documentation cause i cannot find a 
> mysqlexport. Hmm, Mysqldump can be used to backup the db, thats not what i 
> need. Then i found "mysql -e" and it rly writes the chosen fields to a 
> textfile but i dont know how to get options to that (like 
> fileds-terminated-by=';' or stuff like that).
> 
> So did i miss something?
> Is there a better way to go than "mysql -e"?
> If not, where can i find a good explanation of the possible options of this, 
> every hint welcome.
> 

Take a look at SELECT .. INTO OUTFILE:
http://www.mysql.com/doc/en/SELECT.html


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





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

Re: export to textfile

[Alec . Cawley]

Possibly you need the SELECT ... INTO OUTFILE command
(http://www.mysql.com/doc/en/SELECT.html) which has export options
(explained on http://www.mysql.com/doc/en/LOAD_DATA.html, because the
command is the complement to LOAD DATA INFILE).



|-+-->
| |   Bernd Tannenbaum   |
| |   <[EMAIL PROTECTED]|
| |   .itenos.de>|
| |  |
| |   21/10/2003 11:42   |
| |  |
|-+-->
  
>--|
  |
  |
  |   To:   [EMAIL PROTECTED]  
  |
  |   cc:  
  |
  |   Subject:  export to textfile 
  |
  
>--|




Hello all,

Have only a small problem with my mysql and hope that i can get a lil hint
from ya how to go on.
Currently i`m importing Log-files in the mysql-database with a cronjob
(bashscript in Linux). After some processing in the db itself i try now to
export the results into a textfile.
But i must be blind reading the mysql documentation cause i cannot find a
mysqlexport. Hmm, Mysqldump can be used to backup the db, thats not what i
need. Then i found "mysql -e" and it rly writes the chosen fields to a
textfile but i dont know how to get options to that (like
fileds-terminated-by=';' or stuff like that).

So did i miss something?
Is there a better way to go than "mysql -e"?
If not, where can i find a good explanation of the possible options of
this,
every hint welcome.







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

Re: export to textfile

[Rory McKinley]

Hi Bernd

If you are just interested in dumping the data in CSV (or whatever) format
you can do it like so:

SELECT *
INTO OUTFILE 'arb_file.csv' FIELDS TERMINATED BY ',' LINE TERMINATED BY '\n'
FROM  arb_table
WHERE arb_conditions

There are quite a few options available, so I suggest you check the MySQL
manual, to see them all.

HTH.
Rory McKinley
Nebula Solutions
+27 82 857 2391
[EMAIL PROTECTED]
"There are 10 kinds of people in this world,
those who understand binary and those who don't" (Unknown)
- Original Message - 
From: "Bernd Tannenbaum" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 12:42 PM
Subject: export to textfile


Hello all,

Have only a small problem with my mysql and hope that i can get a lil hint
from ya how to go on.
Currently i`m importing Log-files in the mysql-database with a cronjob
(bashscript in Linux). After some processing in the db itself i try now to
export the results into a textfile.
But i must be blind reading the mysql documentation cause i cannot find a
mysqlexport. Hmm, Mysqldump can be used to backup the db, thats not what i
need. Then i found "mysql -e" and it rly writes the chosen fields to a
textfile but i dont know how to get options to that (like
fileds-terminated-by=';' or stuff like that).

So did i miss something?
Is there a better way to go than "mysql -e"?
If not, where can i find a good explanation of the possible options of this,
every hint welcome.

Hope for a lil help,
Bernd




-- 

One OS to rule them all, one OS to find them.
One OS to bring them all, and in the darkness bind them
In the land of Redmond, where the shadows lie.

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

export to textfile

[Bernd Tannenbaum]

Hello all,

Have only a small problem with my mysql and hope that i can get a lil hint 
from ya how to go on.
Currently i`m importing Log-files in the mysql-database with a cronjob 
(bashscript in Linux). After some processing in the db itself i try now to 
export the results into a textfile.
But i must be blind reading the mysql documentation cause i cannot find a 
mysqlexport. Hmm, Mysqldump can be used to backup the db, thats not what i 
need. Then i found "mysql -e" and it rly writes the chosen fields to a 
textfile but i dont know how to get options to that (like 
fileds-terminated-by=';' or stuff like that).

So did i miss something?
Is there a better way to go than "mysql -e"?
If not, where can i find a good explanation of the possible options of this, 
every hint welcome.

Hope for a lil help,
Bernd




-- 

One OS to rule them all, one OS to find them.
One OS to bring them all, and in the darkness bind them
In the land of Redmond, where the shadows lie.

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

Re: Reliability of _rowid over multiple database accesses

[Thierno Cissé]

Hi Smith,
the fact that _rowid "may change over time depending on many factors"
doesn't concern MySQL,
but it is valable for mSQL.

Regards.
Thierno 6C - MySQL 4.0.15


- Original Message - 
From: "Roger Baklund" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: "Guy Smith" <[EMAIL PROTECTED]>
Sent: Monday, October 20, 2003 11:48 PM
Subject: Re: Reliability of _rowid over multiple database accesses


> * Guy Smith
> > I'd like to use the _rowid capability of MySQL to improve the
> > efficiency of a project, but I have concerns about the
> > reliability of the number it returns.  I understand that _rowid
> > "may change over time depending on many factors," but I'd like to
> > know whether this is true if the row is locked.  In particular,
> > can I perform a select on a row which returns a _rowid, modify
> > the row data, and later do an UPDATE using the _rowid as index,
> > without worrying that the _rowid has changed?  Any help will be
> > much appreciated.
>
> The _rowid in MySQL is an alias for any field of a table with an unique
> integer index, for instance a primary key. It is usefull when you don't
> remember the name of the key, but it will not "improve efficiency" in any
> other way, AFAIK.
>
> The value of a key will not change "over time", unless you explicitly
change
> it.
>
> There should be nothing to worry about. :)
>
> --
> Roger
>
>
> -- 
> 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]

Need a Tutorial on RAID with MySQL

[Director General: NEFACOMP]

Hi group,

Does anyone know of a good tutorial on RAID? When used with MySQL.


Thanks,
__
NZEYIMANA Emery Fabrice
NEFA Computing Services, Inc.
P.O. Box 5078 Kigali
Office Phone: +250-51 11 06
Office Fax: +250-50 15 19
Mobile: +250-08517768
Email: [EMAIL PROTECTED]
http://www.nefacomp.net/

Resume indexing after powerloss?

[Peer Reiser]

Someone knows if its possible to resume indexing a base where it left 
due to a power failure?
The process was copying to tmp file when the power went away, having 
achieved 5GB of total 6GB in 6 days..

thanks

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

Re: type data

[Director General: NEFACOMP]

If you want to save your BMP picture in the database and save the extension
as well you will need separate fields.
One for the picture content (BLOB) and another one for the extension (CHAR
or VARCHAR as Egor said)


Thanks
Emery
- Original Message -
From: "Egor Egorov" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 09:10
Subject: Re: type data


> DWI CAHYO <[EMAIL PROTECTED]> wrote:
> >
> > can everybody help me, as i would like to save
> > extention (*.bmp.jpg) on mysql, which i use type data
> > ?
> >
>
> Use CHAR/VARCHAR column types to store extention:
> http://www.mysql.com/doc/en/CHAR.html
>
>
>
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.net http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Egor Egorov
>  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
><___/   www.mysql.com
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>



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

Re: SELECT 9 BETWEEN 1 AND 0

[Director General: NEFACOMP]

I am sorry to say that your question is not clear.
Why don't you send us a simple Table with few data and what you want to
achieve in simple terms? I mean a more clear question!!!
Have tried using IN().
In your subject, you said 9 BETWEEN 1AND 0? I am confused.


Thanks
Emery
- Original Message -
From: "Steven Ducat" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 08:12
Subject: SELECT 9 BETWEEN 1 AND 0


> I am trying to create a select query to find the post town of a users
> post code. I am using Royal Mails (UK) post town gazetteer. UK Postcode
> (eg. RH6 9XJ). The first column contains the first half of the post code
> (eg. RH6) and the next column holds the range of the second half (eg.
> 2AA-6PP).
>
> I have some code as follows:
>  $pc = explode(" ","RH6 9XJ");
>
> "SELECT postTown, postCode, SUBSTRING(postSector,1,3) as a,
> SUBSTRING(postSector,5,3) as b, postCounty FROM postCode WHERE postCode
> = \"".$pc[0]."\" AND \"".$pc[1]."\" BETWEEN SUBSTRING(postSector,1,3)
> AND SUBSTRING(postSector,5,3)";
> ?>
>
> This will return 1 row for example if the first column was "RH6" and the
> second column was "8ZZ-9ZZ".
>
> But in the post town list the range goes from 1 - 0 (eg. 1AA-0ZZ) so
> what I find is if I use the postcode "RH6 9XJ" and a first column of RH6
> and a second column of 7AA-0BW it will not return the row as it does not
> count from 1 - 0.
>
> The question is how can I use some sort of statement using mysql and php
> to select all rows between 1 and 0 NOT 0 and 9 like the above statement
> performs.
>
>
> What I really need is some sort of function where I can set the range
> that the between option sorts from.
>
> I have been trying to solve this for some weeks now.
>
> Thank You.
>
> Steve.
>
>



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

Error encountered when Connect remotely in mySQL 4.0.15

[Alice Tan]

Hi, all,
I had just upgrade my mySQL to version 4.0.15 
yesterday.
I have now encountered error in remotely connect to mysql 
database with the user only have a particular db access 
rights.
 
The scenerio is like this, 
in my mysql user db, i have one user call UserA,
which this UserA only have the rights to access 1 of my 
database called Database1,
when i connect remotely using my front end tool (mySQL 
Front),
it able to access and see ALL the databases NAME(although 
it shd only see Database1).
 
However, when i click on the Database1 or any of the 
databases,
i got the following error and not able to 
proceed to see my data.
 
Access Violation at address 10007504 in module 'libmysql.dll'. 
Read of address 
 
Pls help !! 
 
Note : if the user have no restriction in db, then will be no 
problem in accessing. It only happend if the user have restriction in db 
access.
 
regards,
alice
/---\

Confidential and/ or privileged information may be contained in this
e-mail and any attachments transmitted with it ('Message'). If you are
not the addressee indicated in this Message (or responsible for 
delivery of this Message to such person),you are hereby notified that
any dissemination, distribution, printing or copying of this Message or
any part thereof is prohibited. Please delete this Message if received 
in  error and advise the sender by return e-mail. Opinions, conclusions
and other information in this Message that do not relate to the 
official business of this company shall be understood as neither given
nor endorsed by this company.

This mail is certified Virus Free by *ProtectNow! (InternetNow Sdn Bhd) 
*Scanner Engine powered by Norman Virus Control

\--/

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

Re: Starting the mySQL Server

[Director General: NEFACOMP]

Those are the ones. What about host.FRM ?
Normally
*.FRM are the ones that contain table definitions.
*.MYD are the ones that contain Table Data
*.MYI are the ones that contain Index information
I think for every table, the above files should be there.

How did you install MySQL? From an installer? Or you just unzipped the
files?
Both methods should work on Windows.

Try to reinstall and see if the problem will be solved



Thanks
Emery
- Original Message -
From: "Larry Wasserman" <[EMAIL PROTECTED]>
To: "Director General: NEFACOMP" <[EMAIL PROTECTED]>
Sent: Monday, October 20, 2003 18:31
Subject: RE: Starting the mySQL Server


What if it isn't there.  How do I create it?  I have some files such as
host.MYD, and host.MYI; are these related?

_
Larry Wasserman Two Prudential Plaza
Enterprise Marketing Automation180 North Stetson, Suite 5200
SAS Institute Inc.  Chicago, IL  60601
<>  Phone: 312-819-6800 x8826
Pager Phone: 888-446-6597 Fax: 312-240-0342

SAS ... The Power to Know



-Original Message-
From: Director General: NEFACOMP [mailto:[EMAIL PROTECTED]
Sent: Monday, October 20, 2003 10:50 AM
To: Larry Wasserman; [EMAIL PROTECTED]
Subject: Re: Starting the mySQL Server


That is one of the tables in MySQL database (for privileges and other server
stuffs).

It is located in
MySQL DATA --->mysql --->
MySQL data is the directory containing all data.
On windows systems that is normally c:\mysql\data
So the table file should be in c:\mysql\data\mysql


Thanks
Emery
- Original Message -
From: "Larry Wasserman" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 20, 2003 17:29
Subject: Starting the mySQL Server


I cannot start the MySQl Server.  I am running Windows XP.  I checked the
error file and get the following message.

031020 10:11:56  InnoDB: Started
031020 10:11:56  Fatal error: Can't open privilege tables: Table
'mysql.host' doesn't exist 031020 10:11:56  Aborting

 What do I need to do for my mysql.hosts file?  Where should it be located?

_
Larry Wasserman Two Prudential Plaza
Enterprise Marketing Automation180 North Stetson, Suite 5200
SAS Institute Inc.  Chicago, IL  60601
<>  Phone: 312-819-6800 x8826 Pager Phone:
888-446-6597 Fax: 312-240-0342

SAS ... The Power to Know



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










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

Re: MySQL DLL for windows

[Egor Egorov]

Prem Soman <[EMAIL PROTECTED]> wrote:
> 
>  i have a problem compiling a simple mysql client
> program in windows. 
> 
> what are the compilation options needed and also how
> the Linking is made .
> 
> The manual says to include my_global.h in the header
> but i dont find that under include folder. also i
> searched in the net , can anyone provide me the link
> from where it could be downloaded ,.. i am using mysql
> 3.23.40

Upgrade MySQL to the recent version and you can find my_global.h in the include dir.



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




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

Re: type data

[Egor Egorov]

DWI CAHYO <[EMAIL PROTECTED]> wrote:
> 
> can everybody help me, as i would like to save
> extention (*.bmp.jpg) on mysql, which i use type data
> ?
> 

Use CHAR/VARCHAR column types to store extention:
http://www.mysql.com/doc/en/CHAR.html



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




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