RE: Select older than minutes

[Donny Simonton]

SELECT * FROM `support_start` WHERE (NOW() - (60*7)) > TO_DAYS(date)

Not the most optimized way of doing it, but it would work.  60 seconds in a
minute and 7 minutes is the example.

Donny

> -Original Message-
> From: Scott Haneda [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, January 28, 2004 12:01 AM
> To: MySql
> Subject: Select older than minutes
> 
> I have done this so many times..
> SELECT * FROM `support_start` WHERE TO_DAYS(NOW()) - TO_DAYS(date) > 7
> 
> I have scoured (more like glanced :-)) at the docs on
>  and can not
> seem
> to find how to get all records where a timestamp(14) is older than x
> minutes.
> 
> 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]

Select older than minutes

[Scott Haneda]

I have done this so many times..
SELECT * FROM `support_start` WHERE TO_DAYS(NOW()) - TO_DAYS(date) > 7

I have scoured (more like glanced :-)) at the docs on
 and can not seem
to find how to get all records where a timestamp(14) is older than x
minutes.

Thanks


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

MYSQL Security

[Timotius Alfa]

Hi All, 

I'm newbie in MYSQL. Would please tell me about Mysql Security ? 

I used Windows2000 for mysql server. 

thank you 

 


-
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!

Re: Memory Problems on G5/OSX/MySql4.0.17

[Adam Goldstein]

I have had linux on soft-raid5 (6x18G, 8x9G, 4x18G) systems, and the 
load was even higher... The explanation for this could be that at high 
IO rates the data is not 100% synced across the spindles, and therefore 
smaller files (ie files smaller than the chunk size on each physical 
disk) must wait to be passed under the heads on all the disks... While 
larger chunk sizes may help this, I'm not sure.  A large ram buffer and 
read ahead on a dedicated raid system is more likely to work in that 
case, but, that would require either yet another fileserver (fairly 
expensive), or a hw dedicated Raid server (much more expensive), like 
the Xraid, which did not produce any real difference in the mysql bench 
results previously posted here. In fact, going by those simple 
benchmarks alone, my box already beat the Xserve/Xraid system in most 
of the tests.

Of course, the validity or relativity of those tests to a real world, 
heavily used server may be in question. :) I also am having trouble 
finding relative bench data to other good power systems (ie. I would 
like to see how this stacks up against an 8+G dual/quad xeon or sparc, 
etc)

I will ensure his nightly optimize/repair scripts feature the flush.

But, none of this yet explains why testing from the linux box using the 
remote G5/mysql server (over only 100Mbit switch) gives better results 
than testing directly on the server.

--
Adam Goldstein
White Wolf Networks
http://whitewlf.net
On Jan 27, 2004, at 9:45 AM, Brent Baisley wrote:

I don't think there would be any benefit to using InnoDB, at least not 
from a transaction support view.

After your nightly optimize/repair are you also doing a flush? That 
may help.

I haven't seen any direct comparisons between HFS+ and file systems 
supported by Linux. I would believe that Linux would be faster since 
Linux tends to be geared towards performance first rather than 
usability. But you shouldn't rely on disk caching only. The disks 
still need to be read in order to fill the cache, so you want to get 
the best disk performance you can. Based on your other email, it looks 
like you are using individual disks for storing your data. While I 
understand what you were trying to do by separating your data onto 
different disks, you would get far better performance by combining 
your disks in a RAID, even a software RAID.
If you are using software based RAID, you would need to choose between 
mirroring or striping. Both will give you better read speeds, 
mirroring will slow down writes. If you are striping, the more drives 
you use the better performance you'll get, although I wouldn't put 
more than 4 drives on a single SCSI card.
I think you can use Apple's RAID software for your SCSI disk, but 
SoftRAID (softraid.com) would give you more options. Moving to RAID 
should improve things across the board and will give the best bang for 
your buck (SoftRAID is $129). Personally, I think you should always 
use some form of RAID on all servers.

On Jan 26, 2004, at 5:41 PM, Adam Goldstein wrote:

I have added these settings to my newer my.cnf, including replacing 
the key_buffer=1600M with this 768M... It was a touch late today to 
see if it has a big effect during the heavy load period (~3am to 4pm 
EST, site has mostly european users)

I did not have any of these settings explicitly set in my latest 
my.cnf trialsm, except key_buffer, and I ommitted the innodb ones, as 
we are not (currently) using innodb... would there be any benefit? 
transactions are not a priority, so says my client, so he does not 
use them.

I see the query_cache_size is rather large here, but I am unsure what 
the default size would be. I do not know, yet, how large I can/should 
make either setting, but, it does appear to work without 
malloc/memory errors appearing in the log. Note: while it bitched in 
the logs about the malloc setting, the server did not crash, but, 
kept running. Obviously with an undetermined amount of cache. I 
cannot seem to find any good way to know how much ram 
(cache/buffer/other) mysql uses, as the top output from osx is not 
very appealing... not that linux top tells me much more either.  On 
average, on the old system (all on one box) mysql was said to be 
using about 350MB avg in top... except after the nightly 
optimize/repair script which left it using 1.2G of ram for hours, and 
making all queries rather slow.

Also- a more G5 specific question: as MySql is supposed to gain much 
from the OS disk caching, how does OSX/HFS+ compare to other *nIX 
filesystems... such as Linux 2.4 w/reiserfs?

--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577


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

Re: Reducing MySQL memory consumption?

[Dan Nelson]

In the last episode (Jan 27), Philip Mak said:
> > No, mysql is taking up 23MB.  Linux creates separate processes for
> > each thread, which is why you see lots of mysqlds in top.  They all
> > share the same address space, though.  Are you sure you don't maybe
> > have a couple dozen apache processes consuming your memory instead?
> 
> Well, here's a ps (sorted by memory, greatest memory usage is at the
> bottom):
> 
> http://lina.aaanime.net/~pmak/memory.txt
> 
> At the bottom, it's all mysqld processes. A total of about 1400 MB of
> RAM is being used... so if mysqld's memory is all shared, I guess
> it's all from the apache processes adding up?

Holy cow!

You have 1.2GB of process space being consumed by 181 httpd processes!! 
Only 547MB of that space is currently in core, which is probably why
you're thrashing so bad.  Some of that is shared space, but the size of
httpd+libc probably isn't more than 1mb total.

You need to start asking questions in an apache list :)  Can apache
serve all those virtual hosts under one process?  Or maybe switch to
something other than apache for the static content so you can collapse
those 129 plain httpd processes.

-- 
Dan Nelson
[EMAIL PROTECTED]

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

re: Query help

[Jeremy March]

No.  With the method you're using (storing the parent id with each record) 
you have to use a recursive series of queries to show the subtree levels.  
Take a look at this article--especially the second and third pages about the 
modified preorder tree traversal and nested sets.  There are many other 
articles on this, but I found this one especially helpful starting out.  It 
will let you do what you want, but this method has its drawbacks too.

http://www.sitepoint.com/article/1105

_
Let the new MSN Premium Internet Software make the most of your high-speed 
experience. http://join.msn.com/?pgmarket=en-us&page=byoa/prem&ST=1

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

questions about bind_param and mysql

[smrtalec]

I have a snippet of code below. I'm trying to use teh bind_param option to select 
multiple rows using a foreach loop. after doign some reading I realized this is 
supprted through DBD  are there any special measures that I need to take to make 
bind_param work.



## select rows in table based on search strings - only works with or
foreach $search(@search_pat){
my $sth = $dbh->prepare ("SELECT str_no_addr, str_name_addr, cit_addr
  FROM s3a_inglewood_project_info
  WHERE * LIKE ?;") or err_trap("failed to prepare 
statement\n");
$sth->bind_param(1,$search);

## exicute and fetch selected rows
$sth->execute or
err_trap("failed to execute statement\n");
}
my $array_ref = $sth->fetchall_arrayref();


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

Re: PHP connection to MySQL fails (newbie)

[drubl]

Thanks, Ligaya.

Yes, the system name is "rh01", according to localhosts and /etc/net and 
/etc/sysconfig/network.  Your question caused me to question my 
networking configuration, so I tried  connecting to "localhost".  That 
seems to work.  Was able to add a row to the table.

Cheers, Drubl

--

Also, made the code change you suggested.  Same basic info returned in 
the browser:

*Warning*: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) in 
*/var/www/html/mysql_connect.inc* on line *29*

*Warning*: MySQL Connection Failed: Access denied for user: '[EMAIL PROTECTED]' 
(Using password: YES) in */var/www/html/mysql_connect.inc* on line *29*
Could not connect to database. Please try again later

Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)

Ligaya Turmelle wrote:

Is your host name "rh01"?  That is supposted to be the host name
(www.rh01.com) or IP address(127.0.0.1).  also try adding this:
$db_connection = mysql_connect ("rh01", "llama", "camel") OR die ("Could not connect to database. Please try
again later". mysql_error());
At the very least it will allow the program to end gracefully and the
mysql_error() will tell you why.
"drubl" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
 

Trying to get make my first PHP connection to a DB.

The mysql_connect appears to be the point of failure. The line number in
the error output correlates to the call.
Am able to use the same user, password, etc, to connect to the
"accounting" database as either user "llama" and user "root" using the
"mysql" interface.
Also tried using the root account which had the same results.
What am I missing? Seems too simple.
Could it be a configuration issue?
Many thanks for any insights!
drubl
--
   

---
 

Configuration:
--
RH Linux 9
Apache 2.0.40
PHP 4.2.2
MySQL 3.23.54
Script:
--

// Connect to MYSQL
$db_connection = mysql_connect ("rh01", "llama", "camel");
// Select the DB
mysql_select_db ("accounting");
?>

Results displayed by browser:
-
Warning: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) in
/var/www/html/mysql_connect.inc on line 26
Warning: MySQL Connection Failed: Access denied for user: '[EMAIL PROTECTED]'
(Using password: YES) in /var/www/html/mysql_connect.inc on line 26
   



 



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

Query help

[Andrew Barnes]

Hi

I have a table called "sections" with the following structure:

+---++--+-+-++
| Field | Type   | Null | Key | Default | Extra  |
+---++--+-+-++
| secid | int(11)|  | PRI | NULL| auto_increment |
| title | mediumtext |  | | ||
| body  | longtext   |  | | ||
| parentid  | int(5) |  | | 0   ||
| treelevel | int(5) |  | | 0   ||
| ord   | int(11)|  | | 0   ||
| layoutid  | int(11)|  | | 0   ||
| accessid  | int(11)|  | | 0   ||
+---++--+-+-++


I uses the parentid and the treelevel to determine whether it is a section,
subsection, sub sub section and so on. Would anyone know of a query that
would allow me to find a section and all it's subsections and all the
subsections subsections down to lowest level in one go?

Thanks for any help in advance

Regards
Andy Barnes


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

Re: PHP connection to MySQL fails (newbie)

[Ligaya Turmelle]

Is your host name "rh01"?  That is supposted to be the host name
(www.rh01.com) or IP address(127.0.0.1).  also try adding this:

$db_connection = mysql_connect ("rh01", "llama", "camel") OR die ("Could not connect to database. Please try
again later". mysql_error());

At the very least it will allow the program to end gracefully and the
mysql_error() will tell you why.


"drubl" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Trying to get make my first PHP connection to a DB.
>
> The mysql_connect appears to be the point of failure. The line number in
> the error output correlates to the call.
>
> Am able to use the same user, password, etc, to connect to the
> "accounting" database as either user "llama" and user "root" using the
> "mysql" interface.
> Also tried using the root account which had the same results.
>
> What am I missing? Seems too simple.
> Could it be a configuration issue?
>
> Many thanks for any insights!
> drubl
>
> --
---
>
> Configuration:
> --
> RH Linux 9
> Apache 2.0.40
> PHP 4.2.2
> MySQL 3.23.54
>
> Script:
> --
> 
> // Connect to MYSQL
> $db_connection = mysql_connect ("rh01", "llama", "camel");
>
> // Select the DB
> mysql_select_db ("accounting");
>
> ?>
>
>
> Results displayed by browser:
> -
>
> Warning: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) in
> /var/www/html/mysql_connect.inc on line 26
>
> Warning: MySQL Connection Failed: Access denied for user: '[EMAIL PROTECTED]'
> (Using password: YES) in /var/www/html/mysql_connect.inc on line 26
>



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

Re: create threads rate

[Arnaud Pignard]

Hello,

Do you have add in your my.cnf skip-name-resolve ?
If not maybe the problem come from here.
Also upgrade to 4.0.17 will fix some bug on FreeBSD if i remenber right 
(check maybe changelog)

At 11:42 26/01/2004, Varshavchick Alexander wrote:
Hi,

I have a 4.0.7 mysql server which services requests from several virtual
web sites. Most of the times all works well but periodically the mysql
server suddently gets loaded to impossibly high values - load average up
to several hundred. During such periods the server sleeps and is not
responding. I suspect that the cause of this can be in a spontaneous very
high rate of new connections when many new threads are being created and
running, and it is too much for the server. The question is if there is
some mechanism in mysql which can control the rate in which the new
threads are created, something similar to MAX_SPAWN_RATE option in apache
web server daemon, or something of the sort?
Server mysql 4.0.7 with linux threads runs under FreeBSD 4.6.2.

Thanks


Alexander Varshavchick, Metrocom Joint Stock Company
Phone: (812)118-3322, 118-3115(fax)
--
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: Require greater than 255 in varchar?

[Bernard Clement]

Either a BLOB or TEXT field should be your solution.

See http://www.mysql.com/doc/en/BLOB.html for the description.

Bernard

On Tuesday 27 January 2004 22:29, Eve Atley wrote:
> I have a large chunk of text I've attempted to put into a varchar field,
> and it chopped off a chunk of it. If I need to enter text that is greater
> than the default 255, what choice should I use instead?
>
> Thanks,
> Eve



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

RE: Require greater than 255 in varchar?

[Donny Simonton]

Eve,
You can look at http://www.mysql.com/doc/en/Column_types.html

Or:

[NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE] 
A fixed-length string that is always right-padded with spaces to the
specified length when stored. The range of M is 0 to 255 characters (1 to
255 prior to MySQL Version 3.23). Trailing spaces are removed when the value
is retrieved. CHAR values are sorted and compared in case-insensitive
fashion according to the default character set unless the BINARY keyword is
given. From version 4.1.0, if the M value specified is greater than 255, the
column type is converted to TEXT. This is a compatibility feature. NATIONAL
CHAR (or its equivalent short form, NCHAR) is the SQL-99 way to define that
a CHAR column should use the default CHARACTER set. This is the default in
MySQL. CHAR is a shorthand for CHARACTER. From version 4.1.0, the ASCII
attribute can be specified which assigns the latin1 character set to a CHAR
column. From version 4.1.1, the UNICODE attribute can be specified which
assigns the ucs2 character set to a CHAR column. MySQL allows you to create
a column of type CHAR(0). This is mainly useful when you have to be
compliant with some old applications that depend on the existence of a
column but that do not actually use the value. This is also quite nice when
you need a column that only can take 2 values: A CHAR(0), that is not
defined as NOT NULL, will occupy only one bit and can take only 2 values:
NULL or "". See section 11.3.1 The CHAR and VARCHAR Types. 
CHAR 
This is a synonym for CHAR(1). 
[NATIONAL] VARCHAR(M) [BINARY] 
A variable-length string. Note: trailing spaces are removed when the value
is stored (this differs from the SQL-99 specification). The range of M is 0
to 255 characters (1 to 255 prior to MySQL Version 4.0.2). VARCHAR values
are sorted and compared in case-insensitive fashion unless the BINARY
keyword is given. See section 13.2.5.1 Silent Column Specification Changes.
>From version 4.1.0, if the M value specified is greater than 255, the column
type is converted to TEXT. This is a compatibility feature. VARCHAR is a
shorthand for CHARACTER VARYING. See section 11.3.1 The CHAR and VARCHAR
Types. 
TINYBLOB 
TINYTEXT 
A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters. See
section 13.2.5.1 Silent Column Specification Changes. See section 11.3.2 The
BLOB and TEXT Types. 
BLOB 
TEXT 
A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters.
See section 13.2.5.1 Silent Column Specification Changes. See section 11.3.2
The BLOB and TEXT Types. 
MEDIUMBLOB 
MEDIUMTEXT 
A BLOB or TEXT column with a maximum length of 16777215 (2^24 - 1)
characters. See section 13.2.5.1 Silent Column Specification Changes. See
section 11.3.2 The BLOB and TEXT Types. 
LONGBLOB 
LONGTEXT 
A BLOB or TEXT column with a maximum length of 4294967295 or 4G (2^32 - 1)
characters. See section 13.2.5.1 Silent Column Specification Changes. Up to
MySQL version 3.23 the server/client protocol and MyISAM tables had a limit
of 16M per communication packet / table row, from version 4.x the maximum
allowed length of LONGTEXT or LONGBLOB columns depends on the configured
maximum packet size in the client/server protocol and available memory. See
section 11.3.2 The BLOB and TEXT Types.


Hope this helps.

Donny

> -Original Message-
> From: Eve Atley [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, January 27, 2004 9:29 PM
> To: [EMAIL PROTECTED]
> Subject: Require greater than 255 in varchar?
> 
> 
> I have a large chunk of text I've attempted to put into a varchar field,
> and
> it chopped off a chunk of it. If I need to enter text that is greater than
> the default 255, what choice should I use instead?
> 
> Thanks,
> Eve
> 
> 
> 
> 
> 
> 
> --
> 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: Reducing MySQL memory consumption?

[Philip Mak]

On Sat, Jan 24, 2004 at 01:56:53PM -0600, Dan Nelson wrote:
> You didn't say what OS you're using, but the below top output looks
> like Linux's procps top, which doesn't tell you your paging rate.  Run
> "vmstat 1" and watch the si and so columns.  Just because swap is being
> used doesn't mean you're thrashing.  You need to know the rate.

It's Linux, yes.

The si and so columns were in the hundreds when I did "vmstat 1", but
I didn't save it at the moment the machine had a high load average and
lots of swap space used.

> No, mysql is taking up 23MB.  Linux creates separate processes for each
> thread, which is why you see lots of mysqlds in top.  They all share
> the same address space, though.  Are you sure you don't maybe have a
> couple dozen apache processes consuming your memory instead?

Well, here's a ps (sorted by memory, greatest memory usage is at the
bottom):

http://lina.aaanime.net/~pmak/memory.txt

At the bottom, it's all mysqld processes. A total of about 1400 MB of
RAM is being used... so if mysqld's memory is all shared, I guess it's
all from the apache processes adding up?

Oh, does anyone know how to make "ps" not show shared memory used by
processes more than once?

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

PHP connection to MySQL fails (newbie)

[drubl]

Trying to get make my first PHP connection to a DB.

The mysql_connect appears to be the point of failure. The line number in 
the error output correlates to the call.

Am able to use the same user, password, etc, to connect to the 
"accounting" database as either user "llama" and user "root" using the 
"mysql" interface.
Also tried using the root account which had the same results.

What am I missing? Seems too simple.
Could it be a configuration issue?
Many thanks for any insights!
drubl
-

Configuration:
--
RH Linux 9
Apache 2.0.40
PHP 4.2.2
MySQL 3.23.54
Script:
--

// Connect to MYSQL
$db_connection = mysql_connect ("rh01", "llama", "camel");
// Select the DB
mysql_select_db ("accounting");
?>

Results displayed by browser:
-
Warning: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) in 
/var/www/html/mysql_connect.inc on line 26

Warning: MySQL Connection Failed: Access denied for user: '[EMAIL PROTECTED]' 
(Using password: YES) in /var/www/html/mysql_connect.inc on line 26

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

Error log message on MySQL startup

[Kev]

On a new installation of mysql on a Mac OS X machine 10.3.x I am getting the
following in the .err log:

 

040127 17:23:29  mysqld started

/Library/MySQL/libexec/mysqld: ERROR: unknown variable
'innodb_buffer_pool_size = 70M'

040127 17:23:32  mysqld ended

 

040127 18:43:58  mysqld started

/Library/MySQL/libexec/mysqld: ERROR: unknown variable
'innodb_buffer_pool_size = 70M'

040127 18:44:04  mysqld ended

 

040127 19:33:57  mysqld started

/Library/MySQL/libexec/mysqld: ERROR: unknown variable
'innodb_buffer_pool_size = 70M'

040127 19:34:00  mysqld ended

 

and so on.

 

The server logistics package of 4.0.15 was used for the installation.

 

Now I have 3  my.cnf files in different locations, two are the same:

 

This is the my.cnf in the /etc and in the /Library/MySQL/libexec/ directory

 

# turn on binary logging and slow query logging log-bin log-slow-queries

 

# InnoDB config

# This is the basic config as suggested in the manual # Datafile(s) must be
able to # hold your data and indexes.

# make sure you have enough

# free disk space.

innodb_data_file_path = ibdata1:10M:autoextend # Set buffer pool size to #
50 - 80% of your computer's memory set-variable = innodb_buffer_pool_size =
70M set-variable = innodb_additional_mem_pool_size=10M

# set the log file size to about

# 25% of the buffer pool size

set-variable = innodb_log_file_size=20M

set_variable = innodb_log_buffer_size=8M 

# Set ..flush_log at_trx_commit # to 0 if you can afford losing 

# some last transactions 

innodb_flush_log_at_trx_commit=1

 

 

This is the third my.cnf with path:

 

Kevins-Computer:/usr/local/mysql/data kevinbarry$ more my.conf # Example
mysql config file for medium systems.

#

# This is for a system with little memory (32M - 64M) where MySQL plays 

# a important part and systems up to 128M very MySQL is used together with 

# other programs (like a web server) # # 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 /Library/MySQL/var) 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  = /tmp/mysql.sock

 

SECTION REMOVED TO REDUCE MESSAGE SIZE

 

# The MySQL server

[mysqld]

port= 3306

socket  = /tmp/mysql.sock

skip-locking

key_buffer = 16M

max_allowed_packet = 1M

table_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

myisam_sort_buffer_size = 8M

 

# 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 = 4M
#bdb_max_lock = 1

 

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /Library/MySQL/var/ #innodb_data_file_path =
ibdata1:10M:autoextend 

#innodb_log_group_home_dir = /Library/MySQL/var/ 

#innodb_log_arch_dir = /Library/MySQL/var/ 

# You can set .._buffer_pool_size up to 50 - 80 % 

# of RAM but beware of setting memory usage too high 

#innodb_buffer_pool_size = 16M 

#innodb_additional_mem_pool_size = 2M 

# Set .._log_file_size to 25 % of buffer pool size 

#innodb_log_file_size = 5M #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 are not familiar with SQL
#safe-updates

 

[isamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

 

[myisamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

 

[mysqlhotcopy]

interactive-timeout

 

I believe that the first my.cnf file above is sufficient but it contains the
line that is giving the error re. innodb_buffer_pool so is the location
incorrect or is it something else.

 

Thanks.

 

 

Re: Memory Problems on G5/OSX/MySql4.0.17

[Adam Goldstein]

I have managed to get what looks like >2G for the process, but, it does  
not want to do a key_buffer of that size

I gave it a Key_buffer of 768M and a query cache of 1024M, and it seems  
happier.. though, not noticeably faster.

[mysqld]
key_buffer   = 768M
max_allowed_packet = 8M
table_cache   = 512
sort_buffer_size = 2M
read_buffer_size   = 2M
myisam_sort_buffer_size = 512M
thread_cache = 8
thread_concurrency = 8
max_connections = 1000
skip-name-resolve
skip-bdb
skip-innodb
skip-locking
ft_min_word_len= 2
join_buffer_size = 3M
query_cache_size=1024M
bulk_insert_buffer_size=256M
tmp_table_size =128M
sort_buffer =8M
read_rnd_buffer_size=8M
record_buffer=32M
open_files_limit=15000
tmpdir  = /tmp/ 
log-bin = /var/log/mysql/raptor-bin.log
Benchmarks are just plain weird. Here is from the linux server to the  
G5:

alter-table: Total time: 11 wallclock secs ( 0.03 usr  0.02 sys +  0.00  
cusr  0.00 csys =  0.05 CPU)
ATIS: Failed  
(output/ATIS-mysql_fast-Linux_2.4.22_21mdkenterprise_i686-cmp-mysql- 
fink-64)
big-tables: Total time: 15 wallclock secs ( 4.31 usr  2.79 sys +  0.00  
cusr  0.00 csys =  7.10 CPU)
connect: Total time: 324 wallclock secs (46.64 usr 30.27 sys +  0.00  
cusr  0.00 csys = 76.91 CPU)
create: Total time: 105 wallclock secs ( 2.04 usr  1.10 sys +  0.00  
cusr  0.00 csys =  3.14 CPU)
insert: Total time: 1237 wallclock secs (295.16 usr 73.22 sys +  0.00  
cusr  0.00 csys = 368.38 CPU)
select: Total time: 134 wallclock secs (32.39 usr  6.77 sys +  0.00  
cusr  0.00 csys = 39.16 CPU)
wisconsin: Failed  
(output/wisconsin-mysql_fast-Linux_2.4.22_21mdkenterprise_i686-cmp- 
mysql-fink-64)

and here is on the G5 locally:

alter-table: Total time: 38 wallclock secs ( 0.07 usr  0.05 sys +  0.00  
cusr  0.00 csys =  0.12 CPU)
ATIS: Total time: 20 wallclock secs ( 7.90 usr  7.77 sys +  0.00 cusr   
0.00 csys = 15.67 CPU)
big-tables: Total time: 27 wallclock secs ( 6.49 usr 16.10 sys +  0.00  
cusr  0.00 csys = 22.59 CPU)
connect: Total time: 167 wallclock secs (39.79 usr 52.78 sys +  0.00  
cusr  0.00 csys = 92.57 CPU)
create: Total time: 106 wallclock secs ( 6.12 usr  2.94 sys +  0.00  
cusr  0.00 csys =  9.06 CPU)
insert: Total time: 1257 wallclock secs (388.48 usr 311.51 sys +  0.00  
cusr  0.00 csys = 699.99 CPU)
select: Total time: 132 wallclock secs (40.22 usr 27.92 sys +  0.00  
cusr  0.00 csys = 68.14 CPU)
wisconsin: Total time:  5 wallclock secs ( 1.89 usr  1.65 sys +  0.00  
cusr  0.00 csys =  3.54 CPU)

Some of the strangeness is due to it being a live server, tested during  
low use hours. How accurate are these bench
marks, and do they represent the overall strength of the mysql server  
to handle large loads?  I can't get a good idea a to how many  
queries/sec it should be able to handle, considering I can't tell how  
complex the queries are. All I can say is the site serves 12mil  
pages/month (~100mil hits/mo), 80% concentrated into 13H of the day,  
with perhaps 40% in just 4-5hours... About 1million hits to the  
heaviest sql page/month, broken up into the above portions.

Of course, that is also not including the amount of people potentially  
trying to access the site during this time, which by eyeball estimates  
on the graphs suggest easily 25-150% more, plus the amount more that  
would come if the site could handle them and they were happy. (We all  
know web users are a fickle bunch, and will drop a slow loading site  
like a hot potato.)

--
Adam Goldstein
White Wolf Networks
http://whitewlf.net
On Jan 26, 2004, at 12:05 PM, Brad Eacker wrote:

Adam,
 Off the wall question, but is White Wolf Networks related in
any way to White Wolf Publishing?
You may be hitting an OSX limit. While you can install more than 2GB
on a system, I don't think any one process is allowed to allocated
more than 2GB of RAM to itself. It's not a 64-bit OS yet. You should
be able to search the Apple website for this limit.
 Could you take a look at the ulimit man page to see if it will
allow greater than a signed 32 bit value (2G).  If it does not then
there is still a 32 bit limitation on process size due to this basic
constraint.
 Brad Eacker ([EMAIL PROTECTED])


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

Require greater than 255 in varchar?

[Eve Atley]

I have a large chunk of text I've attempted to put into a varchar field, and
it chopped off a chunk of it. If I need to enter text that is greater than
the default 255, what choice should I use instead?

Thanks,
Eve






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

Re: Index on a bit?

[mos]

At 04:13 PM 1/27/2004, Mike Boone wrote:
Hi all,

I was hoping to use a TINYINT to store 8 distinct boolean values. That works
fine, but is there any way to optimize searching on a specific bit? I didn't
see a way for MySQL to create an index on a specific bit of the TINYINT. Am
I better off "wasting" space by putting each boolean value in its own
TINYINT?
Thanks for the advice!
Mike Boone
http://boonedocks.net/mike
Mike,
You're making way too much work for yourself. Unless you're 
dealing with a hundred million rows and you are short of space, I'd use SET 
(if you need more than 1 of the 8 bits turned on at the same time) to store 
your values. If you need only 1 of the 8 turned on, use ENUM instead. 
Either option will make coding easier and people would actually understand 
your SQL statements. See http://www.mysql.com/doc/en/SET.html and 
http://www.mysql.com/doc/en/ENUM.html.

BTW, if you choose to use 8 different columns, and have an index 
on each column, MySQL won't use an index in the search if the value you're 
searching for appears in more than 30% of the rows. Since you're only 
storing two values in each column (0 or 1), the indexes probably won't work 
if the values are evenly distributed.

Mike 



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

Re: Index on a bit?

[Mark Warner]

Sounds to me like you are either trying to re-invent the wheel, or 
you're running your database on an old 10MB hard drive.
I would just use 8 discrete fields, indexed, and "waste" those 7 extra 
bytes.

-Mark

Mike Boone wrote:

Hi all,

I was hoping to use a TINYINT to store 8 distinct boolean values. That works
fine, but is there any way to optimize searching on a specific bit? I didn't
see a way for MySQL to create an index on a specific bit of the TINYINT. Am
I better off "wasting" space by putting each boolean value in its own
TINYINT?
Thanks for the advice!
Mike Boone
http://boonedocks.net/mike
 



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

Re: can't install DBI on panther

[tait sanders]

well I finally got DBI 1.40 installed thanks to all you lot.

FYI - I installed all the developer tools - not the select few I had  
chosen previously,
then modified the mysql_config and the makefile created.
this worked.

thanks heaps for all the help.

ta
tait
On 27/01/2004, at 6:00 PM, Steve Folly wrote:

On 27 Jan 2004, at 00:37, tait sanders wrote:

i've run both 'perl -MCPAN -eshell' and 'make DBI' and both come back  
reporting heaps of errors like the following:

"from Perl.xs:1:
/System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h: 
380:30: sys/types.h: No such file or directory
/System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h: 
411:19: ctype.h: No such file or directory
/System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h: 
423:23: locale.h: No such file or directory
/System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h: 
440:20: setjmp.h: No such file or directory
/System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h: 
446:26: sys/param.h: No such file or directory
/System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h: 
451:23: stdlib.h: No such file or directory


At a guess, I'd say you haven't installed the Developer Tools. That  
installs /usr/include which you appear to be missing.

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

tait sanders
computer technician
sir robert webster bldg
trc
unsw

Re: Using Wildcards in Query

[Jacque Scott]

This worked great.  Thank you very much.


>>> Douglas Sims <[EMAIL PROTECTED]> 1/27/2004 12:23:59 PM >>>

It sounds as if you need to use a regular expression.

For very simple string comparisons, use =, as in wbs='Fish'
For more complex string comparisions with simple wildcards, use LIKE as
in wbs LIKE "%fish%"
For most complex comparisions, use a regular expression, as in wbs
REGEXP ".\d"

In the REGEXP example I listed above, the pattern will match all
strings which contain one character (.) followed by one digit (\d).  If
you want to only match strings which start with one chracter followed by
one digit, for example, you would say "^.\d".
. represents any character
\d represents only characters in the digits class (0-9)
^ means at the start of the string (if it's at the start of the regexp,
otherwise it can mean "not")

Regular expressions are amazing things if used properly. 
http://www.mysql.com/doc/en/Pattern_matching.html

Note that in MySQL, the wildcard characters are _ and %, where _
represents a single character and % represents any number of characters,
unlike Access, where if I remember, # means one char and * means any
number of characters (?)

Good luck!



For Jacque Scott wrote:
I am converting over to mySQL from Access 2.0 and I am having a
littletrouble with a query.  Here it is: SELECT Max(WBS) AS LastOfWBS
FROM Projects Where((WBS)) Like """ &txtEntryData(0).Text & "#%"";I am
trying to get the last WBS ID starting with a particular letterthe user
will type in the textbox.  My criteria is that it has to startwith a
letter and the next character is a number.  There can be lettersor more
numbers to the right of the first number.  For example:  A01C orB001,
but not AB01.  In Access we could use the following query:SELECT
DISTINCT Max([Projects].[WBS]) AS LastOfWBS FROM Projects where
(Projects.WBS) Like """ & txtEntryData(0).Text &"#*""; How can I insure
when using mySQL that the second character is anumber?  

Replication corrupted

[Jeff McKeon]

I rebooted the slave machine after it went unresponsive and now I can't
get replication to start.

Here is the show slave status printout.  Anyone have any idea how to fix
this?

mysql> show slave status \G
*** 1. row ***
  Master_Host: 10.32.1.10
  Master_User: repl
  Master_Port: 3306
Connect_retry: 60
  Master_Log_File: DB01TC07927-bin.005
  Read_Master_Log_Pos: 723396631
   Relay_Log_File: MIS01TC07927-relay-bin.039
Relay_Log_Pos: 13876528
Relay_Master_Log_File: DB01TC07927-bin.005
 Slave_IO_Running: Yes
Slave_SQL_Running: No
  Replicate_do_db:
  Replicate_ignore_db:   
 Last_errno: 0
   Last_error: Could not parse relay log event entry. The
possible reasons are: the master's binary log is corrupted (you can
check this by running 'mysqlbinlog' on the binary log), the slave's
relay log is corrupted (you can check this by running 'mysqlbinlog' on
the relay log), a network problem, or a bug in the master's or slave's
MySQL code. If you want to check the master's binary log or slave's
relay log, you will be able to know their names by issuing 'SHOW SLAVE
STATUS' on this slave.
 Skip_counter: 0
  Exec_master_log_pos: 723207989
  Relay_log_space: 14059362

Jeff McKeon
IT Manager
Telaurus Communications LLC
[EMAIL PROTECTED]
(973) 889-8990 ex 209 

***The information contained in this communication is confidential. It
is intended only for the sole use of the recipient named above and may
be legally privileged. If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, distribution
or copying of this communication, or any of its contents or attachments,
is expressly prohibited. If you have received this communication in
error, please re-send it to the sender and delete the original message,
and any copy of it, from your computer system. Thank You.***


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

Index on a bit?

[Mike Boone]

Hi all,

I was hoping to use a TINYINT to store 8 distinct boolean values. That works
fine, but is there any way to optimize searching on a specific bit? I didn't
see a way for MySQL to create an index on a specific bit of the TINYINT. Am
I better off "wasting" space by putting each boolean value in its own
TINYINT?

Thanks for the advice!
Mike Boone
http://boonedocks.net/mike


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

Re: Using Wildcards in Query

[Douglas Sims]

It sounds as if you need to use a regular expression.

For very simple string comparisons, use =, as in _wbs='Fish'_
For more complex string comparisions with simple wildcards, use LIKE as 
in _wbs LIKE "%fish%"
_For most complex comparisions, use a regular expression, as in _wbs 
REGEXP ".\d"_

In the REGEXP example I listed above, the pattern will match all strings 
which contain one character (.) followed by one digit (\d).  If you want 
to only match strings which /start/ with one chracter followed by one 
digit, for example, you would say "^.\d".
. represents any character
\d represents only characters in the digits class (0-9)
^ means at the start of the string (if it's at the start of the regexp, 
otherwise it can mean "not")

Regular expressions are amazing things if used properly.  
http://www.mysql.com/doc/en/Pattern_matching.html

Note that in MySQL, the wildcard characters are _ and %, where _ 
represents a single character and % represents any number of characters, 
unlike Access, where if I remember, # means one char and * means any 
number of characters (?)

Good luck!



For Jacque Scott wrote:

I am converting over to mySQL from Access 2.0 and I am having a little
trouble with a query.  Here it is:
SELECT Max(WBS) AS LastOfWBS FROM Projects Where((WBS)) Like """ &
txtEntryData(0).Text & "#%"";
I am trying to get the last WBS ID starting with a particular letter
the user will type in the textbox.  My criteria is that it has to start
with a letter and the next character is a number.  There can be letters
or more numbers to the right of the first number.  For example:  A01C or
B001, but not AB01.  In Access we could use the following query:
SELECT DISTINCT Max([Projects].[WBS]) AS LastOfWBS 
FROM Projects where (Projects.WBS) Like """ & txtEntryData(0).Text &
"#*"";

How can I insure when using mySQL that the second character is a
number?
 

RE: Using Wildcards in Query

[HuMPie]

Think this is what you seek (sory but its copy-ed out the ms-sql
online books :))


This example displays the first initial and last name of each employee,
for example, A Fuller:

USE Northwind
SELECT SUBSTRING(FirstName, 1, 1), LastName
FROM Employees


Best Regards,
 
HuMPie @ Grunn.Org
-
 
Disclamer:
All you do with the suggestion in this mail is you responsibillity even
if your system will crash :)


-Original Message-
From: Jacque Scott [mailto:[EMAIL PROTECTED] 
Sent: dinsdag 27 januari 2004 19:57
To: [EMAIL PROTECTED]
Subject: Using Wildcards in Query


I am converting over to mySQL from Access 2.0 and I am having a little
trouble with a query.  Here it is:
 
SELECT Max(WBS) AS LastOfWBS FROM Projects Where((WBS)) Like """ &
txtEntryData(0).Text & "#%"";

I am trying to get the last WBS ID starting with a particular letter the
user will type in the textbox.  My criteria is that it has to start with
a letter and the next character is a number.  There can be letters or
more numbers to the right of the first number.  For example:  A01C or
B001, but not AB01.  In Access we could use the following query:

SELECT DISTINCT Max([Projects].[WBS]) AS LastOfWBS 
FROM Projects where (Projects.WBS) Like """ & txtEntryData(0).Text &
"#*"";
 
How can I insure when using mySQL that the second character is a number?


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

RE: wildcards the field

[HuMPie]

Select * from table where path like '/data1%' and id='jones'

This will serach in table 'table' all the records that match /data1* 

 
Best Regards,
 
HuMPie @ Grunn.Org
-
 
Disclamer:
All you do with the suggestion in this mail is you responsibillity even
if your system will crash :)


-Original Message-
From: Bob Ramsey [mailto:[EMAIL PROTECTED] 
Sent: maandag 12 januari 2004 19:26
To: [EMAIL PROTECTED]
Subject: wildcards the field


Hi,

I'd like to use a mysql database as a lookup to authorize people to edit
a 
webpage.  The idea is that people would go to a main login page for 
authentication and then when they visit a page they are authorized to
edit, 
they see an "edit this page" link.

What I'm envisioning is a really basic table structure like this:

id varchar (10)
path varchar (255)

id is a foreign key that links back to another table I have with more
user 
information, but that isn't really important.

Entries might look like this, with explanation in brackets []:

'ramsey', '/' [I am the webmaster, I can edit all files in all
directories] 'jones', '/data1' [jones can edit all files in the data1
directory] 'jones', '/data2/jonesdoc.php' [jones can also edit this one
file in data2] 'smith', '/data2' [smith can edit all files in data2]
'smith', '/data2' [smith can also edit all files in data3]

People will generally have access to an entire directory, but not 
necessarily.  Most people will have access to more than one directory.

So if jones goes to http://www.mysite.com/data1/index.php, the php code
on 
the page sees that the file is /data1/index.php.  It then needs to find
out 
if jones can edit that file.

I'd like to be able to do a single mysql query.

select * from table where path = '/data1/index.php' and user 
='jones';[obviously won't work]

I can use php and get take the current page and turn it into '/data1'
and 
then do:

select * from table where (path ='/data1/index.php' or path='/data1')
and 
user ='jones';

but I'm curious if there's a way to have '/data1/index.php' match
'/data1' 
using wildcards or something like that.

Thanks,

bob




==
Bob Ramsey   Applications Development & Support II
ph:  1(319)335-9956  187 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==


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

Using Wildcards in Query

[Jacque Scott]

I am converting over to mySQL from Access 2.0 and I am having a little
trouble with a query.  Here it is:
 
SELECT Max(WBS) AS LastOfWBS FROM Projects Where((WBS)) Like """ &
txtEntryData(0).Text & "#%"";

I am trying to get the last WBS ID starting with a particular letter
the user will type in the textbox.  My criteria is that it has to start
with a letter and the next character is a number.  There can be letters
or more numbers to the right of the first number.  For example:  A01C or
B001, but not AB01.  In Access we could use the following query:

SELECT DISTINCT Max([Projects].[WBS]) AS LastOfWBS 
FROM Projects where (Projects.WBS) Like """ & txtEntryData(0).Text &
"#*"";
 
How can I insure when using mySQL that the second character is a
number?

RE: FKs in InnoDB

[Don Vu]

Thanks, marc.

-Don

-Original Message-
From: Mechain Marc [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 27, 2004 12:23 PM
To: Don Vu; [EMAIL PROTECTED]
Subject: RE: FKs in InnoDB


SHOW TABLE STATUS

In the "Comment" Column you have the definition of the foreign keys.

Marc.

-Message d'origine-
De : Don Vu [mailto:[EMAIL PROTECTED]
Envoyé : mardi 27 janvier 2004 17:48
À : [EMAIL PROTECTED]
Objet : FKs in InnoDB


Hi,
 
I'm using MySQL 4.0.16 with InnoDB tables.
 
>From the command-line mysql console Is there an easy way to see all the names of the 
>foreign keys in my db? Basically I want to generate a file that drops all the foreign 
>keys currently in my db.
 
thanks,
Don
 

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

Re: If Else statement

[Mike Tuller]

What I have is a shell script that gathers information from a computer, and
I want the script to check to see if there is currently a record that
already exists that has a certain Ethernet(MAC) address. I would also like
to have an ID number for the record that auto-increments so that I can have
an asset number to display on a web page. (Some assets I enter may not have
an Ethernet address because I am creating a database for hardware assets.
Computers, printers, etc., so I need to have an asset ID)

I have ethernet_address set as a Unique Key, and asset_id set as Primary_Key
and to auto-increment.

If I just use replace like below, every time the record is updated, the
asset number that is set to auto-increment is updated. So if it was 5
before, it is now 6. I don't what the asset_id to change of course.

/usr/local/mysql/bin/mysql  --user=$username --password=$password
--host=$server cetechnology -e \
"REPLACE INTO hardware_assets (ethernet_address, operating_system)\
VALUES \
('$ethernet_address', '$operating_system')";

I don't currently have MySql 4.1 running on my server, I have the 3.23.53
that comes with OS X Server 10.2, so if the suggestion you gave me is the
only way (or the best) I will have to upgrade the server. If there is
another way to do the same thing, please let me know.


Mike

> From: Egor Egorov <[EMAIL PROTECTED]>
> Date: Mon, 26 Jan 2004 15:40:54 +0200
> To: [EMAIL PROTECTED]
> Subject: Re: If Else statement
> 
> Mike Tuller <[EMAIL PROTECTED]> wrote:
>> I am trying write a shell script to check to see if a record exists and if
>> it does, update information, and if it doesn't insert information. Is there
>> a way to do an if else statement in MySql?
>> 
> 
> If you have PRIMARY KEY or UNIQUE index, take a look at REPLACE and INSERT ..
> ON DUPLICATE KEY UPDATE statements:
> http://www.mysql.com/doc/en/REPLACE.html
> http://www.mysql.com/doc/en/INSERT.html
> 
> INSERT .. ON DUPLICATE KEY UPDATE is supported since v4.1.0.
> 
> 
> 
> -- 
> 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]

wildcards the field

[Bob Ramsey]

Hi,

I'd like to use a mysql database as a lookup to authorize people to edit a 
webpage.  The idea is that people would go to a main login page for 
authentication and then when they visit a page they are authorized to edit, 
they see an "edit this page" link.

What I'm envisioning is a really basic table structure like this:

id varchar (10)
path varchar (255)
id is a foreign key that links back to another table I have with more user 
information, but that isn't really important.

Entries might look like this, with explanation in brackets []:

'ramsey', '/' [I am the webmaster, I can edit all files in all directories]
'jones', '/data1' [jones can edit all files in the data1 directory]
'jones', '/data2/jonesdoc.php' [jones can also edit this one file in data2]
'smith', '/data2' [smith can edit all files in data2]
'smith', '/data2' [smith can also edit all files in data3]
People will generally have access to an entire directory, but not 
necessarily.  Most people will have access to more than one directory.

So if jones goes to http://www.mysite.com/data1/index.php, the php code on 
the page sees that the file is /data1/index.php.  It then needs to find out 
if jones can edit that file.

I'd like to be able to do a single mysql query.

select * from table where path = '/data1/index.php' and user 
='jones';[obviously won't work]

I can use php and get take the current page and turn it into '/data1' and 
then do:

select * from table where (path ='/data1/index.php' or path='/data1') and 
user ='jones';

but I'm curious if there's a way to have '/data1/index.php' match '/data1' 
using wildcards or something like that.

Thanks,

bob



==
Bob Ramsey   Applications Development & Support II
ph:  1(319)335-9956  187 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: FKs in InnoDB

[Martijn Tonies]

Hi,

> SHOW TABLE STATUS
>
> In the "Comment" Column you have the definition of the foreign keys.

I have to disagree here.

The only reliable way to get FK info, is to use
SHOW CREATE TABLE tablename

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & 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: FKs in InnoDB

[Mechain Marc]

SHOW TABLE STATUS

In the "Comment" Column you have the definition of the foreign keys.

Marc.

-Message d'origine-
De : Don Vu [mailto:[EMAIL PROTECTED]
Envoyé : mardi 27 janvier 2004 17:48
À : [EMAIL PROTECTED]
Objet : FKs in InnoDB


Hi,
 
I'm using MySQL 4.0.16 with InnoDB tables.
 
>From the command-line mysql console Is there an easy way to see all the
names of the foreign keys in my db? Basically I want to generate a file
that drops all the foreign keys currently in my db.
 
thanks,
Don
 

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

Re: Syntax Check: Group BY with Joins

[Eric Ellsworth]

Thanks for the thought.  The HAVING clause, while it does belong behind the
group by clause doesn't seem to be the source of the problem - I get the
same error even when I take the HAVING clause out.

Thanks,

Eric

- Original Message - 
From: "Lopez David E-r9374c" <[EMAIL PROTECTED]>
To: "'Eric Ellsworth'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, January 27, 2004 10:09 AM
Subject: RE: Syntax Check: Group BY with Joins


> Eric
>
> Try putting the "HAVING" clause after group by. I believe
> "having" is the last clause (may be LIMIT).
>
> David
>
> > -Original Message-
> > From: Eric Ellsworth [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, January 27, 2004 8:01 AM
> > To: [EMAIL PROTECTED]
> > Subject: Syntax Check: Group BY with Joins
> >
> >
> > Hi,
> > I'm having trouble with this query:
> >
> > SELECT Max(DATE_ADD('1970-1-1',INTERVAL Lastupdate/3600/24
> > DAY)) as BaskDate, ofm_baskets.Code,
> > Sum(ofm_baskets.Quantity) AS TotalQty
> > FROM (ofm_basklist INNER JOIN ofm_basklupd ON
> > ofm_basklist.Session_id = ofm_basklupd.Session_id) INNER JOIN
> > ofm_baskets ON ofm_basklist.Basket_id = ofm_baskets.Id
> > GROUP BY ofm_baskets.Code
> > HAVING (((ofm_basklist.Order_id)=0))
> > ORDER BY Max(DATE_ADD('1970-1-1',INTERVAL Lastupdate/3600/24 DAY));
> >
> > I get the following error:
> > ERROR : Invalid use of group function
> >
> > Can someone give me an idea of what's wrong with this query?
> > I'm using MySQLd version 4.0.16.
> >
> > Thanks,
> >
> > Eric
> >
> >
> >
>
> -- 
> 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: data file too big

[Mikhail Entaltsev]

> Is there any chance of loosing any data if I do that. Its a production
machine
> with over 200 thousand customer tickets

don't think so...

> Is there any other suggestions ?

If you don't use InnoDB too much (you don't have a lot of data in InnoDB
tables)
then you can change size of InnoDB data and log files in my.cnf file.
But be careful. Use correct way.
http://www.innodb.com/ibman.php#Adding_and_removing

Mikhail.


- Original Message - 
From: "Asif Iqbal" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, January 23, 2004 8:18 PM
Subject: Re: data file too big


> On Fri, 23 Jan 2004, Mikhail Entaltsev wrote:
>
> > Date: Fri, 23 Jan 2004 09:34:52 +0100
> > From: Mikhail Entaltsev <[EMAIL PROTECTED]>
> > To: Gregory Newby <[EMAIL PROTECTED]>, Asif Iqbal <[EMAIL PROTECTED]>
> > Cc: [EMAIL PROTECTED]
> > Subject: Re: data file too big
> >
> > > I believe that this will flush those logs:
> > >
> > > mysql> reset master;
>
> Is there any chance of loosing any data if I do that. Its a production
machine
> with over 200 thousand customer tickets
>
> >
> > No.. It won't shrink any of InnoDB datafiles:
> >
> > > > 101Mib_logfile0
> > > > 101Mib_logfile1
> > > > 1.9Gibdata1
> > > > 1.5Gibdata2
> >
> > It could help if you have some MySQL binary logs:
> > -bin.001
> > ...
> > -bin.00N
>
> I don't. Is there any other suggestions ?
>
> >
> > Best regards,
> > Mikhail.
> >
> > - Original Message -
> > From: "Gregory Newby" <[EMAIL PROTECTED]>
> > To: "Asif Iqbal" <[EMAIL PROTECTED]>
> > Cc: <[EMAIL PROTECTED]>
> > Sent: Thursday, January 22, 2004 11:32 PM
> > Subject: Re: data file too big
> >
> >
> > > I believe that this will flush those logs:
> > >
> > > mysql> reset master;
> > >
> > >   -- Greg
> > >
> > > On Thu, Jan 22, 2004 at 05:23:07PM -0500, Asif Iqbal wrote:
> > > > Hi All
> > > >
> > > > My data file has all these files
> > > >
> > > > (root)@webrt:/usr/local/mysql/data# du -sh *
> > > > 25K ib_arch_log_00
> > > > 3.0Kib_arch_log_02
> > > > 3.0Kib_arch_log_04
> > > > 101Mib_logfile0
> > > > 101Mib_logfile1
> > > > 1.9Gibdata1
> > > > 1.5Gibdata2
> > > > 2.0Kmy.cnf
> > > > 70K mysql
> > > > 2.0Knewdb
> > > > 39M rt3
> > > > 12K test
> > > > 67K webrt.err
> > > > 1.0Kwebrt.pid
> > > >
> > > > Is there anyway I can remove some of them so I can get some space
back ?
> > > >
> > > > I am using mysql -4.0.13 on Solaris 8
> > > >
> > > > --
> > > > Asif Iqbal
> > > > PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu
> > > > There's no place like 127.0.0.1
> > > >
> > > > --
> > > > 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]
> > >
> > >
> >
> >
>
> -- 
> Asif Iqbal
> PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu
> There's no place like 127.0.0.1
>
> -- 
> 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]

FKs in InnoDB

[Don Vu]

Hi,
 
I'm using MySQL 4.0.16 with InnoDB tables.
 
>From the command-line mysql console Is there an easy way to see all the
names of the foreign keys in my db? Basically I want to generate a file
that drops all the foreign keys currently in my db.
 
thanks,
Don
 

Stored Procedure and the procs.db column

[Martijn Tonies]

Hi all,

I'm investigating the Stored Procedures in MySQL 5, and I'm
wondering...

I've been looking through the "procs" table in "mysql" -
what does column "db" do? It stays "null" when I defined
a procedure.

And how can you get a list of available procedures?
SHOW PROCEDURE doesn't seem to work?


With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & 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: updates on slave server??

[John McCaskey]

The updates on the slave will not syncronize to the master if you have a one
direction master->slave relationship setup.  It is possible however to setup
each server as a master and as a slave so that server 1 is the master for
server 2 and server 2 is the master for server 1.  Then queries will
propogate in both directions.  This may be what you are looking for.  

If you setup such bi-directional replication and you are executing queries
at the same time on both servers you need to be careful about things like
auto-index collumns getting out of sync and causing conflicts due to the
latency in replication.

John A. McCaskey


-Original Message-
From: Mike Mapsnac [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 26, 2004 4:50 PM
To: [EMAIL PROTECTED]
Subject: updates on slave server?? 


Hello

I found today that some data on slave server were different than on master 
server. I don't know how that can happen. Slave server was running, "show 
slave statu"s show no errors

If I make some updates on slave server. Does this updates will be reflected 
on
master server? Or slave cannot synchronize master server?

Thanks

_
Rethink your business approach for the new year with the helpful tips here. 
http://special.msn.com/bcentral/prep04.armx


-- 
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 3.23.58 seg faults occasionally

[Michael Bacarella]

> I guess the table is corrupt and its row contains a wrong trx id. InnoDB
> tries to retrieve an earlier version of the row, but the roll ptr in the row
> is garbage too, and it contains a nonexistent rollback segment id.
>
> "
> InnoDB: Error: trying to return an SQL NULL field in a non-null innoDB:
> column! Table name
> secret/tablename
> "
> 
> The above is also a clear indication of table corruption. The NULL bit in an
> InnoDB column is set, though the field should never be NULL.

Dropping the table and restoring it from a dump seems to be the ticket.

> You should upgrade to MySQL-4.0.17, which has better data page checksum
> checking. Do you get corruption with the same application on earlier Linux
> kernels, or could there be something wrong with Linux-2.4.23?

We've never experienced table corruption like this on previous kernels.
The biggest change we've introduced (besides Linux 2.4.23) is MD
instead of hardware RAID.

A MySQL 4 migration is in the works.

> /**
> Copies an undo record to heap. This function can be called if we know that
> the undo log record exists. */
> 
> trx_undo_rec_t*
> trx_undo_get_undo_rec_low(
> /*==*/
> /* out, own: copy of the record */
> dulint  roll_ptr,   /* in: roll pointer to record */
> mem_heap_t* heap)   /* in: memory heap where copied */
> {
> trx_undo_rec_t* undo_rec;
> ulint   rseg_id;
> ulint   page_no;
> ulint   offset;
> page_t* undo_page;
> trx_rseg_t* rseg;
> ibool   is_insert;
> mtr_t   mtr;
> 
> trx_undo_decode_roll_ptr(roll_ptr, &is_insert, &rseg_id, &page_no,
> &offset);
> rseg = trx_rseg_get_on_id(rseg_id);
> 
> ...
> 
> /**
> Looks for a rollback segment, based on the rollback segment id. */
> 
> trx_rseg_t*
> trx_rseg_get_on_id(
> /*===*/
> /* out: rollback segment */
> ulint   id) /* in: rollback segment id */
> {
> trx_rseg_t* rseg;
> 
> rseg = UT_LIST_GET_FIRST(trx_sys->rseg_list);
> ut_ad(rseg);
> 
> while (rseg->id != id) {
> rseg = UT_LIST_GET_NEXT(rseg_list, rseg);
> ut_ad(rseg);
> }
> 
> return(rseg);
> }
> 
> (gdb) disassemble trx_rseg_get_on_id
> Dump of assembler code for function trx_rseg_get_on_id:
> 0x81fa930 : push   %ebp
> 0x81fa931 :   mov0x83f3190,%eax
> 0x81fa936 :   mov0x24(%eax),%eax
> 0x81fa939 :   mov%esp,%ebp
> 0x81fa93b :  mov0x8(%ebp),%edx
> 0x81fa93e :  cmp%edx,(%eax)
> 0x81fa940 :
> je 0x81fa94c 
> 0x81fa942 :  mov0xa0(%eax),%eax
> 0x81fa948 :  cmp%edx,(%eax)
> 0x81fa94a :
> jne0x81fa942 
> 0x81fa94c :  mov%ebp,%esp
> 0x81fa94e :  pop%ebp
> 0x81fa94f :  ret
> End of assembler dump.
> (gdb)
> 
> 
> List:MySQL General Discussion« Previous MessageNext Message »
> From:Michael BacarellaDate:January 26 2004 6:21pm
> Subject:Re: MySQL 3.23.58 seg faults occasionally
> 
> > > the crashes below happen in independent areas of code. The 2 first are
> > > inside InnoDB, and the third inside MySQL. This looks like random thread
> > > crashes, or random memory corruption.
> >
> > My colleague tells me that the third stack trace (the one inside MySQL)
> > is from a RESET MASTER.  This server used to be a slave that was promoted
> > to master.  My apologies for not making this distinction.
> >
> > The ones inside InnoDB have been triggered by querying disk-resident
> > low use tables.
> 
> We can get SIGSEGV almost every hour (from an hourly maintenance script).
> This is the stack trace:
> 
> 0x80c23d5 handle_segfault__Fi + 425
> 0x40022f54 _end + 935506260
> 0x823d4a8 trx_rseg_get_on_id + 24
> 0x823952d trx_undo_get_undo_rec_low + 45
> 0x823977d trx_undo_get_undo_rec + 49
> 0x82399c0 trx_undo_prev_version_build + 548
> 0x81f3f35 row_vers_build_for_consistent_read + 641
> 0x81e9d5e row_sel_build_prev_vers_for_mysql + 226
> 0x81ecda4 row_search_for_mysql + 6580
> 0x852 general_fetch__11ha_innobasePcUiUi + 322
> 0x8111373 rnd_next__11ha_innobasePc + 83
> 0x8103da6 rr_sequential__FP14st_read_record + 150
> 0x80e74d9 sub_select__FP4JOINP13st_join_tableb + 341
> 0x80e7190 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 412
> 0x80df9d7
> mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UiP1
> 3select_result
> + 4167
> 0x80c8aba mysql_execute_command__Fv + 806
> 0x80cbd88 mysql_parse__FP3THDPcUi + 72
> 0x80c7c74 do_command__FP3THD + 1324
> 
> Attempting to convert the table that caused the crash to MyISAM
> resulted in:
> 
> InnoDB: Error: try

RE: Syntax Check: Group BY with Joins

[Lopez David E-r9374c]

Eric

Try putting the "HAVING" clause after group by. I believe
"having" is the last clause (may be LIMIT).

David

> -Original Message-
> From: Eric Ellsworth [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, January 27, 2004 8:01 AM
> To: [EMAIL PROTECTED]
> Subject: Syntax Check: Group BY with Joins
> 
> 
> Hi,
> I'm having trouble with this query:
> 
> SELECT Max(DATE_ADD('1970-1-1',INTERVAL Lastupdate/3600/24 
> DAY)) as BaskDate, ofm_baskets.Code, 
> Sum(ofm_baskets.Quantity) AS TotalQty
> FROM (ofm_basklist INNER JOIN ofm_basklupd ON 
> ofm_basklist.Session_id = ofm_basklupd.Session_id) INNER JOIN 
> ofm_baskets ON ofm_basklist.Basket_id = ofm_baskets.Id
> GROUP BY ofm_baskets.Code
> HAVING (((ofm_basklist.Order_id)=0))
> ORDER BY Max(DATE_ADD('1970-1-1',INTERVAL Lastupdate/3600/24 DAY));
> 
> I get the following error:
> ERROR : Invalid use of group function
> 
> Can someone give me an idea of what's wrong with this query?  
> I'm using MySQLd version 4.0.16.
> 
> Thanks,
> 
> Eric
> 
> 
> 

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

Re: Hi all.

[mos]

At 03:41 AM 1/27/2004, you wrote:
I am just starting to work with SQL and Mysql server language and am 
embedding some queries into another program I am developing.

The database I have created needs to store Documents and I have used Blob 
type columns for this. If I want to:

Select USERID,NAME,LNAME Where DOC contains "what ever words or phrases" ;

How would I do this ?

Many thanks for information on this.

Here from you soon I hope,
Best regards
Andy Fletcher
Andy,
You probably want to use FullText searching on text fields which 
is much faster than using wild card searches using LIKE '%something%'. 
Please see the manual http://www.mysql.com/doc/en/Fulltext_Search.html.

Mike 



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

Syntax Check: Group BY with Joins

[Eric Ellsworth]

Hi,
I'm having trouble with this query:

SELECT Max(DATE_ADD('1970-1-1',INTERVAL Lastupdate/3600/24 DAY)) as BaskDate, 
ofm_baskets.Code, Sum(ofm_baskets.Quantity) AS TotalQty
FROM (ofm_basklist INNER JOIN ofm_basklupd ON ofm_basklist.Session_id = 
ofm_basklupd.Session_id) INNER JOIN ofm_baskets ON ofm_basklist.Basket_id = 
ofm_baskets.Id
GROUP BY ofm_baskets.Code
HAVING (((ofm_basklist.Order_id)=0))
ORDER BY Max(DATE_ADD('1970-1-1',INTERVAL Lastupdate/3600/24 DAY));

I get the following error:
ERROR : Invalid use of group function

Can someone give me an idea of what's wrong with this query?  I'm using MySQLd version 
4.0.16.

Thanks,

Eric

Re: Hi all.

[Chris Nolan]

MySQL can help you out here, but there are many questions you need to 
answer first.

1. What exactly is in the DOC column? Binary data of some disgusting, 
inferior proprietary word
processor document format, plain text, XML, LaTeX files?
2. If you answered "But I like Clippy!" to the above, go to jail! Go 
directly to jail! Do not pass go,
do not collect any cash! This goes for Word, WordPerfect, 
OpenOffice/StarOffice's default saves
(please read on before flaming me!) etc. To continue, be a true geek and 
switch to something that claims
to emphasise substance over structure, like LaTeX or DocBook XML.
3. If you've gotten to here, it's just a matter of conjugating your query:

SELECT userid, name, lname FROM funky_table WHERE doc LIKE 
'%search_string%';

SQL uses the percentage character (%) as a wildcard to match 0 or more 
of any character. Use it to
fill in the space in and around your search string. The LIKE predicate 
above tells MySQL to ignore case
when doing comparisons.

If you want to use a word processor document format, look at 
decompressing OpenOffice files before
inserting them, and you get XML to play with.

Additional things that may be of use to you:

1. TEXT fields and BLOB fields are basically identical, except that 
comparisons done with regard to TEXT
fields are case-insensitive. The manual calls them "case-insensitive BLOBs".
2. The above works on all MySQL table types but can be slow as all 
buggery. Populate that WHERE clause with
everything you can to reduce the rows examined. Also, avoid strings that 
are less than 3 characters long, otherwise
MySQL 4.0.x and above won't use one of the faster search techniques that 
the optimiser has at it's disposal.
3. If you can work without transactions (many applications can) or you 
are happy to have at least the document field
in a MyISAM table, look into FULLTEXT searches. Of course, this only 
really works nicely for plain text, and
has a new set of caveats to deal with. MySQL 4.1.x brings rather nice 
enhancements to this part of MySQL, but
don't bug poor Heikki about adding FULLTEXT to InnoDB as some of us are 
of the opinion that no one
at Innobase Oy ever sleeps or goes home (or MySQL AB for that matter).

Regards,

Chris

Ugo Bellavance wrote:

 

-Message d'origine-
De : Andy Fletcher [mailto:[EMAIL PROTECTED]
Envoyé : Tuesday, January 27, 2004 4:42 AM
À : [EMAIL PROTECTED]
Objet : Hi all.
I am just starting to work with SQL and Mysql server language 
and am embedding some queries into another program I am developing.

The database I have created needs to store Documents and I 
have used Blob type columns for this. If I want to:

Select USERID,NAME,LNAME Where DOC contains "what ever words 
or phrases" ; 

How would I do this ?
   

First, try to use a significant subjet to your messages.  It helps when searching the archive.
Then, I don't think your needs can be fullfilled, since I don't think mysql can tell what is in a Blob, except that it is a series of 1 and 0.
hth
 

Many thanks for information on this.

Here from you soon I hope,
Best regards
Andy Fletcher
   

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

Persistent connection with Apache 2.0 and perl

[Vincent]

Hi,

How I can make a persistent connection in httpd.conf and get
the $dbh in my perl script ?

Thanks for help

Accédez au courrier électronique de La Poste : www.laposte.net ; 
3615 LAPOSTENET (0,34€/mn) ; tél : 08 92 68 13 50 (0,34€/mn)




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

Re: Memory Problems on G5/OSX/MySql4.0.17

[Brent Baisley]

I don't think there would be any benefit to using InnoDB, at least not 
from a transaction support view.

After your nightly optimize/repair are you also doing a flush? That may 
help.

I haven't seen any direct comparisons between HFS+ and file systems 
supported by Linux. I would believe that Linux would be faster since 
Linux tends to be geared towards performance first rather than 
usability. But you shouldn't rely on disk caching only. The disks still 
need to be read in order to fill the cache, so you want to get the best 
disk performance you can. Based on your other email, it looks like you 
are using individual disks for storing your data. While I understand 
what you were trying to do by separating your data onto different 
disks, you would get far better performance by combining your disks in 
a RAID, even a software RAID.
If you are using software based RAID, you would need to choose between 
mirroring or striping. Both will give you better read speeds, mirroring 
will slow down writes. If you are striping, the more drives you use the 
better performance you'll get, although I wouldn't put more than 4 
drives on a single SCSI card.
I think you can use Apple's RAID software for your SCSI disk, but 
SoftRAID (softraid.com) would give you more options. Moving to RAID 
should improve things across the board and will give the best bang for 
your buck (SoftRAID is $129). Personally, I think you should always use 
some form of RAID on all servers.

On Jan 26, 2004, at 5:41 PM, Adam Goldstein wrote:

I have added these settings to my newer my.cnf, including replacing 
the key_buffer=1600M with this 768M... It was a touch late today to 
see if it has a big effect during the heavy load period (~3am to 4pm 
EST, site has mostly european users)

I did not have any of these settings explicitly set in my latest 
my.cnf trialsm, except key_buffer, and I ommitted the innodb ones, as 
we are not (currently) using innodb... would there be any benefit? 
transactions are not a priority, so says my client, so he does not use 
them.

I see the query_cache_size is rather large here, but I am unsure what 
the default size would be. I do not know, yet, how large I can/should 
make either setting, but, it does appear to work without malloc/memory 
errors appearing in the log. Note: while it bitched in the logs about 
the malloc setting, the server did not crash, but, kept running. 
Obviously with an undetermined amount of cache. I cannot seem to find 
any good way to know how much ram (cache/buffer/other) mysql uses, as 
the top output from osx is not very appealing... not that linux top 
tells me much more either.  On average, on the old system (all on one 
box) mysql was said to be using about 350MB avg in top... except after 
the nightly optimize/repair script which left it using 1.2G of ram for 
hours, and making all queries rather slow.

Also- a more G5 specific question: as MySql is supposed to gain much 
from the OS disk caching, how does OSX/HFS+ compare to other *nIX 
filesystems... such as Linux 2.4 w/reiserfs?

--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Using raw partitions

[Chris Nolan]

Hi all,

Does anyone on the list have experience storing their tables and 
associated bits and pieces on raw partitions? The reason I ask is two fold:

1. Did you notice much in the way of performance difference (which OS 
you're using is probably very relevant here).
2. Have you had to deal with hard discs developing bad sectors and, if 
so, did using raw partitions make anything more difficult?

Most of my clients don't have RAID setups in place, so the answer to the 
second question is something I'd need to consider. All comments,
thoughts and experiences invited and welcome!

Regards,

Chris

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

RE: Hi all.

[Ugo Bellavance]

> -Message d'origine-
> De : Andy Fletcher [mailto:[EMAIL PROTECTED]
> Envoyé : Tuesday, January 27, 2004 4:42 AM
> À : [EMAIL PROTECTED]
> Objet : Hi all.
> 
> 
> I am just starting to work with SQL and Mysql server language 
> and am embedding some queries into another program I am developing.
> 
> The database I have created needs to store Documents and I 
> have used Blob type columns for this. If I want to:
> 
> Select USERID,NAME,LNAME Where DOC contains "what ever words 
> or phrases" ; 
> 
> How would I do this ?

First, try to use a significant subjet to your messages.  It helps when searching the 
archive.
Then, I don't think your needs can be fullfilled, since I don't think mysql can tell 
what is in a Blob, except that it is a series of 1 and 0.
hth
> 
> Many thanks for information on this.
> 
>  
> Here from you soon I hope,
> Best regards
> Andy Fletcher
> 

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

Re: Inserting One new User = 2 entries in mysql.user when using MySQL 4.0.17?!

[Lutz Maibach]

Hi,

finally I solved the problem by myself, yippiee :-)

During the upgrade from 3.x to 4.x I didn't create the indizes for the
user, host und db-tables (I didn't use the mysql-fix-privilege-tables
skript cause I did not whether there was one at all). The missing
indizes caused  MySQL to create a  new user every time I granted rights
to an user,  whether it was existing or not.

Greetings

Lutz

- Original Message - 
From: "Lutz Maibach" <[EMAIL PROTECTED]>
To: "mysql Liste" <[EMAIL PROTECTED]>
Sent: Monday, January 26, 2004 3:01 PM
Subject: Inserting One new User = 2 entries in mysql.user when using
MySQL 4.0.17?!


> Hello,
>
> I'm using the same sqls to append a new user to the MySQL-DB since 2
> years.After upgrading from 3.23.57 to 4.0.17 I added a
replication-user
> with the same script that always worked right. When controlling the
new
> entry I was really surprised that I found two entries for the new user
> instead of one.
>
> To get rid of this "uninvited guest" I studied the manual again and
> found out that in MySQL 4.0.x a replication user now longer got the
> file_priv but the replication_slave_priv instead. So I deleted the two
> user-entries, changed the script to the one shown below
and.got
> again 2 entries for the new user which are different in the privileges
> they own.
>
> Here are the SQL-Statements I used:
> insert into user (host,user,password) values
> ('%','repltest2',password('test1234'));
> flush privileges;
> grant super, reload, replication slave on *.* to repltest2@'%'
> identified by 'test1234';
> flush privileges;
>
> ...and here is what I see in the mysql.user-table:
>
+--+-+--+-+-+---
> --+-+-+---+-+-
--
> +--+---++-+---
--
> ---++--++---+-
--
> ---+--+-+--+--
--
> --++-+--+---+-
--
> --+-+
> | Host | User| Password | Select_priv | Insert_priv |
> Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv |
> Shutdown_priv | Process_priv | File_priv | Grant_priv |
References_priv
> | Index_priv | Alter_priv | Show_db_priv | Super_priv |
> Create_tmp_table_priv | Lock_tables_priv | Execute_priv |
> Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher |
x509_issuer
> | x509_subject | max_questions | max_updates | max_connections |
>
+--+-+--+-+-+---
> --+-+-+---+-+-
--
> +--+---++-+---
--
> ---++--++---+-
--
> ---+--+-+--+--
--
> --++-+--+---+-
--
> --+-+
> | %| repltest2 | 38fb843c55682a6f | N   | N   | N
> | N   | N   | N | Y   | N
|
> N| N | N  | N   | N  |
N
> | N| Y  | N | N
|
> N| Y   | N|  |
> | |  | 0 |   0 |
> 0 |
> | %| repltest2 | 38fb843c55682a6f | N   | N   | N
> | N   | N   | N | N   | N
|
> N| N | N  | N   | N  |
N
> | N| N  | N | N
|
> N| N   | N|  |
> | |  | 0 |   0 |
> 0 |
>
+--+-+--+-+-+---
> --+-+-+---+-+-
--
> +--+---++-+---
--
> ---++--++---+-
--
> ---+--+-+--+--
--
> --++-+--+---+-
--
> --+-+
>
> "User 1" got the right privileges while "User 2" haven't got a single
> privilege.
>
> Anyone knows what's going wrong?
>
> Greetings from Germany
>
> Lutz Maibach
>
>
> -- 
> 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]

InnoDB file format changes 2004 - 2005; Re: InnoDB development

[Heikki Tuuri]

Chris,

InnoDB file format changes:

4.1.0 -> 4.1.1 introduced multiple tablespaces;
4.1.1 -> 4.1.2 or .3allow multiple charsets in the same database
installation (currently only the default charset is used in InnoDB);
5.0.0 -> 5.0.x create all new InnoDB tables in a space-saving
format; this will also slightly change the InnoDB log format, and users of
InnoDB Hot Backup should upgrade ibbackup at the same time.

I hope there will not be more InnoDB file format changes in 2004 - 2005. In
the MySQL tradition, an upgrade is always painless with no data conversion.
But a downgrade is tricky if the file format has changed.

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

Order MySQL support from http://www.mysql.com/support/index.html

.
List:MySQL General Discussion« Previous MessageNext Message »
From:Chris NolanDate:January 27 2004 1:29am
Subject:InnoDB development



Hi all,

I've been away from the list for a while and am wondering if the
following question has been answered:

Given that the extremely funky InnoDB is going to gain a new file format
in the future, would it be a fair guess to say that any additions to
InnoDB requiring file format changes would be made after the release of
the new compressed format?

Regards,

Chris


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

Re: MySQL 3.23.58 seg faults occasionally

[Heikki Tuuri]

Michael,

I guess the table is corrupt and its row contains a wrong trx id. InnoDB
tries to retrieve an earlier version of the row, but the roll ptr in the row
is garbage too, and it contains a nonexistent rollback segment id.

"
InnoDB: Error: trying to return an SQL NULL field in a non-null innoDB:
column! Table name
secret/tablename
"

The above is also a clear indication of table corruption. The NULL bit in an
InnoDB column is set, though the field should never be NULL.

You should upgrade to MySQL-4.0.17, which has better data page checksum
checking. Do you get corruption with the same application on earlier Linux
kernels, or could there be something wrong with Linux-2.4.23?

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

Order MySQL support from http://www.mysql.com/support/index.html


/**
Copies an undo record to heap. This function can be called if we know that
the undo log record exists. */

trx_undo_rec_t*
trx_undo_get_undo_rec_low(
/*==*/
/* out, own: copy of the record */
dulint  roll_ptr,   /* in: roll pointer to record */
mem_heap_t* heap)   /* in: memory heap where copied */
{
trx_undo_rec_t* undo_rec;
ulint   rseg_id;
ulint   page_no;
ulint   offset;
page_t* undo_page;
trx_rseg_t* rseg;
ibool   is_insert;
mtr_t   mtr;

trx_undo_decode_roll_ptr(roll_ptr, &is_insert, &rseg_id, &page_no,
&offset);
rseg = trx_rseg_get_on_id(rseg_id);

...

/**
Looks for a rollback segment, based on the rollback segment id. */

trx_rseg_t*
trx_rseg_get_on_id(
/*===*/
/* out: rollback segment */
ulint   id) /* in: rollback segment id */
{
trx_rseg_t* rseg;

rseg = UT_LIST_GET_FIRST(trx_sys->rseg_list);
ut_ad(rseg);

while (rseg->id != id) {
rseg = UT_LIST_GET_NEXT(rseg_list, rseg);
ut_ad(rseg);
}

return(rseg);
}

(gdb) disassemble trx_rseg_get_on_id
Dump of assembler code for function trx_rseg_get_on_id:
0x81fa930 : push   %ebp
0x81fa931 :   mov0x83f3190,%eax
0x81fa936 :   mov0x24(%eax),%eax
0x81fa939 :   mov%esp,%ebp
0x81fa93b :  mov0x8(%ebp),%edx
0x81fa93e :  cmp%edx,(%eax)
0x81fa940 :
je 0x81fa94c 
0x81fa942 :  mov0xa0(%eax),%eax
0x81fa948 :  cmp%edx,(%eax)
0x81fa94a :
jne0x81fa942 
0x81fa94c :  mov%ebp,%esp
0x81fa94e :  pop%ebp
0x81fa94f :  ret
End of assembler dump.
(gdb)


List:MySQL General Discussion« Previous MessageNext Message »
From:Michael BacarellaDate:January 26 2004 6:21pm
Subject:Re: MySQL 3.23.58 seg faults occasionally



> > the crashes below happen in independent areas of code. The 2 first are
> > inside InnoDB, and the third inside MySQL. This looks like random thread
> > crashes, or random memory corruption.
>
> My colleague tells me that the third stack trace (the one inside MySQL)
> is from a RESET MASTER.  This server used to be a slave that was promoted
> to master.  My apologies for not making this distinction.
>
> The ones inside InnoDB have been triggered by querying disk-resident
> low use tables.

We can get SIGSEGV almost every hour (from an hourly maintenance script).
This is the stack trace:

0x80c23d5 handle_segfault__Fi + 425
0x40022f54 _end + 935506260
0x823d4a8 trx_rseg_get_on_id + 24
0x823952d trx_undo_get_undo_rec_low + 45
0x823977d trx_undo_get_undo_rec + 49
0x82399c0 trx_undo_prev_version_build + 548
0x81f3f35 row_vers_build_for_consistent_read + 641
0x81e9d5e row_sel_build_prev_vers_for_mysql + 226
0x81ecda4 row_search_for_mysql + 6580
0x852 general_fetch__11ha_innobasePcUiUi + 322
0x8111373 rnd_next__11ha_innobasePc + 83
0x8103da6 rr_sequential__FP14st_read_record + 150
0x80e74d9 sub_select__FP4JOINP13st_join_tableb + 341
0x80e7190 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 412
0x80df9d7
mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UiP1
3select_result
+ 4167
0x80c8aba mysql_execute_command__Fv + 806
0x80cbd88 mysql_parse__FP3THDPcUi + 72
0x80c7c74 do_command__FP3THD + 1324

Attempting to convert the table that caused the crash to MyISAM
resulted in:

InnoDB: Error: trying to return an SQL NULL field in a non-null innoDB:
column! Table name
secret/tablename

Also, duplicate keys.  We're deleting the table and recreating it.

Any thoughts?


> > You are running a relatively new Linux kernel, 2.4.23. Did the 

Support for Motoroloa Coldfire with uCLinux?

[HJ Frei]

Hi,
is there  or will be a support for Coldfire Processors Platforms using
uCLinux as OS ?
 
thanks in advance
 
Hans

Load data infile

[Dušan Pavlica]

Hi,

I'm trying to figure out how to extract only some of the columns from text file. I 
know that I can use LOAD DATA INFILE but I didn't find the way how to use it to load 
data only from for example 1st, 3rd and 6th column. 

Could someone give me some tips, please?

TIA

Dusan Pavlica

tables_priv not read after restarting server

[Steven Hentschel]

Can anyone tell me why the following behaviour occurs. There is no change to
the contents of tables_priv after adding the table grant to some_user before
and after the database restart. The database has been upgraded from 3.23 to
4.1.1 and the
mysql_fix_privilege_tables script has been run.

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.1-alpha-standard

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

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show grants for [EMAIL PROTECTED];
+---
-+
| Grants for [EMAIL PROTECTED]
|
+---
-+
| GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO
'some_user'@'localhost' IDENTIFIED BY PASSWORD
'*43933BDF3E95B05EC8BE52E6AEE83DB1B1E309CA' |
| GRANT SELECT ON `some_db`.* TO 'some_user'@'localhost'
|
+---
-+
2 rows in set (0.00 sec)

mysql> grant all on some_db.some_table to 'some_user'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for [EMAIL PROTECTED];
+---
--+
| Grants for [EMAIL PROTECTED]
|
+---
--+
| GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO
'some_user'@'localhost' |
| GRANT SELECT ON `some_db`.* TO 'some_user'@'localhost'
|
| GRANT ALL PRIVILEGES ON `some_db`.`some_table` TO 'some_user'@'localhost'
|
+---
--+
3 rows in set (0.00 sec)

mysql> exit
Bye
# /etc/init.d/mysql restart
Killing mysqld with pid 4103
Wait for mysqld to exit\c
040127 12:33:04  mysqld ended

 done
# Starting mysqld daemon with databases from /var/lib/mysql

# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.1-alpha-standard

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

mysql> show grants for [EMAIL PROTECTED];
+---
-+
| Grants for [EMAIL PROTECTED]
|
+---
-+
| GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO
'some_user'@'localhost' IDENTIFIED BY PASSWORD
'*43933BDF3E95B05EC8BE52E6AEE83DB1B1E309CA' |
| GRANT SELECT ON `some_db`.* TO 'some_user'@'localhost'
|
+---
-+
2 rows in set (0.00 sec)

mysql>

Steven Hentschel





--
Live Life in Broadband
www.telewest.co.uk


The information transmitted is intended only for the person or entity to which it is 
addressed and may contain confidential and/or privileged material.
Statements and opinions expressed in this e-mail may not represent those of the 
company. Any review, retransmission, dissemination or other use of, or taking of any 
action in reliance upon, this information by persons or entities other than the 
intended recipient is prohibited. If you received this in error, please contact the 
sender immediately and delete the material from any computer.

==


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

Problem with timestamp default values

[Predrag Malicevic]

Hi,

There seems to be a problem with setting default values for the timestamp
column type in version 4.0.17:

mysql> create table test (t timestamp default '2001-01-01 00:00:00' not null);
ERROR 1067: Invalid default value for 't'


In 4.0.16 this works normally:

mysql> create table test (t timestamp default '2001-01-01 00:00:00' not null);
Query OK, 0 rows affected (0.00 sec)


This also occurs when using other formats (MMDDHHMMSS and smaller), so
the timestamp column is practically broken when it comes to default
values.


Regards,

--
Predrag Malicevic
BLIC.NET ISP
+387-51-240-830
http://www.blic.net/

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

update or replace with select statement

[Bob Ramsey]

Hi,

I know I can do

insert into mytable select * from some_other_table;

but I can't find the right syntax to use with either an update or 
replace.  Ideally I'd like to do something like this:

update mytable set mytable.a=(select other_table.some_column from 
other_table where other_table.some_column=mytable.a+1) where mytable.b=6;

The goal is to set a column in a to a value based on a column in another 
table when b in the same record has a specific attribute.

Can I do this?

Thanks,

bob



==
Bob Ramsey   Applications Development & Support II
ph:  1(319)335-9956  187 Boyd Law Building
fax: 1(319)335-9019  University of Iowa College of Law
mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
==
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

reg: Can't open named pipe to host: . pipe: MySQL (2)

[Arunachalam]

Hi!
May I have the reason why this error occured in my Localhost.

When I try to connect to MySQL on the Localhost I met the error

*Can't open named pipe to host: .  pipe: MySQL (2)*

I have used;
 Windows 2000 SP 4
 MySQL Server 4.0.17 max-debug

Upon looking the documentation from MySQL website I have also set 
[client]
host=localhost

in my.ini file. but still the error happen why?

Regards,
Arun.


Yahoo! India Mobile: Download the latest polyphonic ringtones.
Go to http://in.mobile.yahoo.com

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

Re: Simple question : Find older CHILD for each PARENT

[Chuck Gadd]

Benjamin PERNOT wrote:

I want to get a list of all the parents with the age and the name of the older 
child they've got. Let's say that a parent can't have 2 children with the same 
age.
I can solve my problem by using multiple queries but that's not very clean and a 
bit heavy (especially if there are lots of parents).
This is a perfect example of a "max-concat" query.

Look up "max concat" on the mysql website, and it should show you a similar
problem with solution.




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

Simple question : Find older CHILD for each PARENT

[Benjamin PERNOT]

Hi,
I have a simple problem that I don't know how to solve with mysql.
I have 2 tables, a parent table and a child table.

parent:
---
| p_id  |   name  |
---
|  1| A   |
|  2| B   |
|  ...  |...  |
|  112  | C   |
|  113  | D   |
---
child:
--
| c_id  |  p_id  |  name |  age  |
--
|  1|1   |  BP   |  15   |
|  2|56  |  AW   |  12   |
|  ...  |   ...  |  GH   |  19   |
|299|2   |  RT   |  14   |
|300|56  |  FG   |  18   |
--

I want to get a list of all the parents with the age and the name of the older 
child they've got. Let's say that a parent can't have 2 children with the same 
age.
I can solve my problem by using multiple queries but that's not very clean and a 
bit heavy (especially if there are lots of parents).

Any idea?

Thank you
Benjamin

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

my.cnf on Shared Hosting

[Hassan Shaikh]

Hi,

I want "--ansi" option for MySQL setting to ensure ANSI compatibility. Most shared 
hosting providers don't allow access to my.cnf. Is there anyway to do this on the fly 
in my script, PHP/Perl sample would be appreciated.

Thanks.


Hassan

Hi all.

[Andy Fletcher]

I am just starting to work with SQL and Mysql server language and am embedding some 
queries into another program I am developing.

The database I have created needs to store Documents and I have used Blob type columns 
for this. If I want to:

Select USERID,NAME,LNAME Where DOC contains "what ever words or phrases" ; 

How would I do this ?

Many thanks for information on this.

 
Here from you soon I hope,
Best regards
Andy Fletcher

Re: Installing from SOURCE or from RPM: your recommendation

[Yuriy]

IMHO, best way is building RPM from the SOURCE with fine tuning and 
install it.
Download the RPM from mysql.com and just issue command:
"rpm -ivh MySQL-xxx.rpm" is wery easy & instant solution.
Regards.
Yuriy.

Hi group?

What do you recommend someone who wants to install MySQL on RedHat?
Using RPMs or installing from SOURCE?
What are the main differences and/or benefits?

Thanks,

off-topic but need help : [FWD: ezmlm warning]

[Veysel Harun Sahin]

Hello,

Today i have received the attached message from mysql-ezmlm. But I could not 
understand what is going on. Is there anyone who will be able to explain what the 
ezmlm tries to tell me and what I have to do?

Thanks in advance.

__
New! Unlimited Netscape Internet Service.
Only $9.95 a month -- Sign up today at http://isp.netscape.com/register
Act now to get a personalized email address!

Netscape. Just the Net You Need.
--- Begin Message ---

Hi! This is the ezmlm program. I'm managing the
[EMAIL PROTECTED] mailing list.

I'm working for my owner, who can be reached
at [EMAIL PROTECTED]


Messages to you from the mysql mailing list seem to
have been bouncing. I've attached a copy of the first bounce
message I received.

If this message bounces too, I will send you a probe. If the probe bounces,
I will remove your address from the mysql mailing list,
without further notice.


I've kept a list of which messages from the mysql mailing list have 
bounced from your address.

Copies of these messages may be in the archive.

To retrieve a set of messages 123-145 (a maximum of 100 per request),
send an empty message to:
   <[EMAIL PROTECTED]>

To receive a subject and author list for the last 100 or so messages,
send an empty message to:
   <[EMAIL PROTECTED]>

Here are the message numbers:

   157680

--- Enclosed is a copy of the bounce message I received.

Return-Path: <>
Received: (qmail 4069 invoked from network); 15 Jan 2004 10:25:25 -
Received-SPF: unknown
Received: from omr-m07.mx.aol.com (HELO omr-m07.mx.aol.com) (64.12.138.19)
  by lists.mysql.com (qpsmtpd/0.27-dev) with ESMTP; Thu, 15 Jan 2004 11:25:25 +0100
Received: from  rly-nc01.mx.aol.com (rly-nc01.mail.aol.com [172.18.151.198]) by 
omr-m07.mx.aol.com (v97.10) with ESMTP id RELAYIN5-640066a893e; Thu, 15 Jan 2004 
05:25:13 -0500
Received: from localhost (localhost)
  by rly-nc01.mx.aol.com (8.8.8/8.8.8/AOL-5.0.0)
  with internal id FAC23082;
  Thu, 15 Jan 2004 05:25:13 -0500 (EST)
Date: Thu, 15 Jan 2004 05:25:13 -0500 (EST)
From: Mail Delivery Subsystem <[EMAIL PROTECTED]>
Message-Id: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
MIME-Version: 1.0
Content-Type: multipart/report; report-type=delivery-status;
boundary="FAC23082.1074162313/rly-nc01.mx.aol.com"
Subject: Returned mail: Host unknown (Error 275)
Auto-Submitted: auto-generated (failure)
X-AOL-IP: 172.18.151.198

This is a MIME-encapsulated message

--FAC23082.1074162313/rly-nc01.mx.aol.com

The original message was received at Thu, 15 Jan 2004 05:24:56 -0500 (EST)
from lists2.mysql.com [213.136.52.31]


*** ATTENTION ***

Your e-mail is being returned to you because there was a problem with its
delivery.  The address which was undeliverable is listed in the section
labeled: "- The following addresses had permanent fatal errors -".

The reason your mail is being returned to you is listed in the section
labeled: "- Transcript of Session Follows -".

The line beginning with "<<<" describes the specific reason your e-mail could
not be delivered.  The next line contains a second error message which is a
general translation for other e-mail servers.

Please direct further questions regarding this message to your e-mail
administrator.

--AOL Postmaster



   - The following addresses had permanent fatal errors -
<[EMAIL PROTECTED]>

   - Transcript of session follows -
550 <[EMAIL PROTECTED]>... Host unknown (Error 275)

--FAC23082.1074162313/rly-nc01.mx.aol.com
Content-Type: message/delivery-status

Reporting-MTA: dns; rly-nc01.mx.aol.com
Arrival-Date: Thu, 15 Jan 2004 05:24:56 -0500 (EST)

Final-Recipient: RFC822; [EMAIL PROTECTED]
Action: failed
Status: 5.1.2
Remote-MTA: DNS; air-nc02.mail.aol.com
Last-Attempt-Date: Thu, 15 Jan 2004 05:25:13 -0500 (EST)

--FAC23082.1074162313/rly-nc01.mx.aol.com
Content-Type: text/rfc822-headers

Received: from  lists.mysql.com (lists2.mysql.com [213.136.52.31]) by 
rly-nc01.mx.aol.com (v97.10) with ESMTP id MAILRELAYINNC19-66740066a741ab; Thu, 15 Jan 
2004 05:24:52 -0500
Received: (qmail 308 invoked by uid 109); 15 Jan 2004 10:23:24 -
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
List-ID: 
Precedence: bulk
List-Help: 
List-Unsubscribe: 
List-Post: 
Delivered-To: mailing list [EMAIL PROTECTED]
Received: (qmail 32756 invoked from network); 15 Jan 2004 10:23:24 -
Received-SPF: unknown (domain of sender [EMAIL PROTECTED] does not designate mailers: 
NXDOMAIN)
Message-ID: <[EMAIL PROTECTED]>
Date: Thu, 15 Jan 2004 02:23:19 -0800 (PST)
From: Aron Bereket <[EMAIL PROTECTED]>
Subject: Mysql problem + Problem in loading data SOLVED
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED]
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-AOL-IP: 213.136.52.31
X-AOL-SCOLL-SCORE: 0:XXX:XX
X-AOL-SCOLL-URL_COUNT: 0
X-AOL-INR

naeem bhatti(software eng)

[naeem]

i am facing a problem in mysql unicode supported or not
,acutally i would like to use utf8 but it does not save data in mysql in correct 
format 

means it changes the lang and set some dummy values
plz kindly inform me i am waiting from ur response

Installation on Embedded Platform

[Ramesha, NP (IE10)]

hi All,

i am planning to write an embedded application which will be running on
customized OS(Red Hat Linux)  & x86 architecture.
i heard MySQL is fast & real time database, so i am planning to use the same
in my application.
can i install MySQL on such a platform, if so where could i get the
patches?.

thanks in advance

regards
ramesh

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

Re: can't install DBI on panther

[Steve Folly]

On 27 Jan 2004, at 00:37, tait sanders wrote:

i've run both 'perl -MCPAN -eshell' and 'make DBI' and both come back  
reporting heaps of errors like the following:

"from Perl.xs:1:
/System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:380: 
30: sys/types.h: No such file or directory
/System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:411: 
19: ctype.h: No such file or directory
/System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:423: 
23: locale.h: No such file or directory
/System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:440: 
20: setjmp.h: No such file or directory
/System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:446: 
26: sys/param.h: No such file or directory
/System/Library/Perl/5.8.1/darwin-thread-multi-2level/CORE/perl.h:451: 
23: stdlib.h: No such file or directory


At a guess, I'd say you haven't installed the Developer Tools. That  
installs /usr/include which you appear to be missing.

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