Re: MySQL 4.1.11 innodb cache can't be flushed after restart ?

[Philippe Poelvoorde]

2006/4/7, Charles Q. Shen [EMAIL PROTECTED]:
 Hi all,

 I am running MySQL 4.1.11 with an innoDB table holding about 17GB of
 records. I took a few hundreds of randomly selected records from the table
 and measured the average access time:

 1st test: average access time is 600ms
 2nd test: average access time is 30ms
 3rd test: average access time is 15ms
 Stop and restart MySQL
 4th test: average access time is 15ms

 Note that I stopped and restarted mysql between the 3rd and 4th test but the
 average access time does not change.

What OS do you use ? It's quiet likely you hit the FS cache, not the MySQL one.

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

Re: Determining if a trigger exists

[Adrian Co]

Hi,

Sorry if I wasn't very clear with my question. I was hoping to obtain 
the functionality such that I could do something similar to:


CREATE TRIGGER IF NOT EXISTS 

Because I usually get a trigger already exists in my script. I might be 
missing something.


The script basically does the basic things like create database if not 
exists, create table if not exists, etc. I was hoping the same thing for 
triggers maybe? Is this possible?


Regards,
Adrian Co

Jim wrote:

There is a TRIGGERS table in the information_schema 


Eg.
select Trigger_Name from TRIGGERS 
where trigger_name = 'TI_AGENT' AND trigger_schema = 'PROPLINK'



-Original Message-
From: Adrian Co [mailto:[EMAIL PROTECTED] 
Sent: Friday, 7 April 2006 1:54 PM

To: mysql@lists.mysql.com
Subject: Determining if a trigger exists

Hi,

Whats the simplest way to determine if a trigger already exists?

i.e. For tables you have: CREATE TABLE IF NOT EXISTS ...

Is there a way to do

CREATE TRIGGER IF NOT EXISTS

I'm using MySQL 5.0 btw.

Thanks!

Regards,
Adrian

 




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

Re: Replication for historical data

[Barry]

Ian Collins wrote:

Hi,
I have a customer who wants to be able to replicate their live MySQL 
database to a second server, but not to have any data deleted.

So there is data on the second server?
i.e., they want to accumulate the data. 


I don't believe you can do this with replication. Does anyone know a way 
of doing this?


Please give more info and i might help.

Greets
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

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

RE: MySQL 4.1.11 innodb cache can't be flushed after restart ?

[Charles Q. Shen]

The OS used are Mandriva and Fedora. 

Can you explain more?

Thanks.

Charles  

 -Original Message-
 From: Philippe Poelvoorde [mailto:[EMAIL PROTECTED] 
 Sent: Friday, April 07, 2006 2:43 AM
 To: MySQL General
 Subject: Re: MySQL 4.1.11 innodb cache can't be flushed after 
 restart ?
 
 2006/4/7, Charles Q. Shen [EMAIL PROTECTED]:
  Hi all,
 
  I am running MySQL 4.1.11 with an innoDB table holding 
 about 17GB of 
  records. I took a few hundreds of randomly selected records 
 from the 
  table and measured the average access time:
 
  1st test: average access time is 600ms 2nd test: average 
 access time 
  is 30ms 3rd test: average access time is 15ms Stop and 
 restart MySQL 
  4th test: average access time is 15ms
 
  Note that I stopped and restarted mysql between the 3rd and 
 4th test 
  but the average access time does not change.
 
 What OS do you use ? It's quiet likely you hit the FS cache, 
 not the MySQL one.
 
 --
 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: Replication for historical data

[Eric Braswell]

No, replication is not designed for this task.

It sounds like the Archive Storage Engine could be a good solution for you:

 http://dev.mysql.com/doc/refman/5.0/en/archive-storage-engine.html

Robin Schumacher has written a nice introductory article:
  http://dev.mysql.com/tech-resources/articles/storage-engine.html

Hope that helps,

Eric


--
Eric Braswell
Web Manager MySQL AB
Cupertino, USA


Ian Collins wrote:

Hi,
I have a customer who wants to be able to replicate their live MySQL 
database to a second server, but not to have any data deleted.

i.e., they want to accumulate the data.

I don't believe you can do this with replication. Does anyone know a way 
of doing this?


Cheers,
Ian.






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

Syntax Error

[Mark Sargent]

Hi All,

am trying to get up to speed on cli syntax again,

mysql show open tables from osc
   -

what is wrong with the command above and the one below,

mysql show tables from osc
   -

Why do I not get any output? I was following here,

http://dev.mysql.com/doc/refman/5.1/en/show-open-tables.html

I'm a Linux user, and wish to do everything via cli as opposed to 
phpmyadmin.


Cheers.

Mark Sargent.



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

upgrading problem

[balaraju mandala]

Hi Comunity,

I am getting problem while i am upgrading to MySql ver 4 to MySql ver 5. I
planned first to install MySQL-shared-compat-5.0.19-0.rhel4.i386.rpm, but
i am getting following error.

Preparing...###
[100%]
   1:MySQL-shared-compat###
[100%]
error: unpacking of archive failed on file
/usr/lib/libmysqlclient.so;44361c01: cpio: symlink failed - Permission
denied

i am unable to understand the problem. Please help me.

Re: Syntax Error

[Mark Sargent]

Hi All,

Ah, sorry, a semicolon makes it a whole new world, eh.

Cheers.

Mark Sargent.




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

Re: MySQL 4.1.11 innodb cache can't be flushed after restart ?

[Philippe Poelvoorde]

2006/4/7, Charles Q. Shen [EMAIL PROTECTED]:
 The OS used are Mandriva and Fedora.

 Can you explain more?


I'll make it quick, there is plenty of doc on a web that will explain
this better than I can.
Once you read few things from your hard drive (let's say the index
file for your table), it's kept in memory. Next time you access it
it's directly retrived from memory, not the hard drive, thus low
response time. shutting down MySQL won't clear that cache. If you want
to circumvent that, you need to umount the partition where data and
index are stored.
HIMH

http://www.tldp.org/LDP/tlk/fs/filesystem.html

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

Re: Syntax Error

[mysql]

Hi Mark.

On Fri, 7 Apr 2006, Mark Sargent wrote:

 To: mysql@lists.mysql.com
 From: Mark Sargent [EMAIL PROTECTED]
 Subject: Syntax Error
 
 Hi All,
 
 am trying to get up to speed on cli syntax again,
 
 mysql show open tables from osc
 - 
 
 what is wrong with the command above and the one below,
 
 mysql show tables from osc
 - 

Although it is not mentioned in the syntax diagram in the 
manual, you need to terminate a mysql command with ';', like 
this:

mysql show tables from osc;

The reason for this is that mysql allows you to spread a 
command over many lines, which can be helpfull, eg:


mysql show create table bible_quiz_question \G
*** 1. row ***
   Table: bible_quiz_question
Create Table: CREATE TABLE `bible_quiz_question` (
  `ID` mediumint(8) unsigned NOT NULL auto_increment,
  `question_text` text NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql select ID, question_text
- from bible_quiz_question
- where ID = 1
- ;
++---+
| ID | question_text |
++---+
|  1 | How old was the first man Adam, when he died? |
++---+
1 row in set (0.00 sec)

So mysql will not execute the select query above, untill it 
sees the ';' that terminates the command.

This is why you were getting:

 mysql show tables from osc
 -

because mysql was waiting for you to type something else in, 
or terminate the command with ';'.

If you have problems displaying output because it is to 
large to fit into the table output format, you can 
terminate the mysql command with:

mysql show tables from osc \G

instead of:

mysql show tables from osc;

HTH

Regards

Keith

 Why do I not get any output? I was following here,
 
 http://dev.mysql.com/doc/refman/5.1/en/show-open-tables.html
 
 I'm a Linux user, and wish to do everything via cli as opposed to
 phpmyadmin.

That's a good way to learn how to use mysql properly. 
phpmyadmin is a usefull tool for people that allready know 
how to use mysql via the mysql monitor program (CLI 
program).

 Cheers.
 
 Mark Sargent.

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

Prinitng MySQL Structure from ssh

[Mark Sargent]

Hi All,

we have cli access to our MySQL database via ssh in the Admin GUI and 
were wondering how to print the complete structure of a DB. I searched 
the documents on MySQL homepage but couldn't find anything specific. 
Anyone know how to do this? Cheers.


Mark Sargent.


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

Re: Prinitng MySQL Structure from ssh

[alexj]

try mysqldump --no-data database-name -u user-name

Thanx
Alex

On Fri, 7 Apr 2006, Mark Sargent wrote:

 Hi All,
 
 we have cli access to our MySQL database via ssh in the Admin GUI
 and were wondering how to print the complete structure of a DB. I
 searched the documents on MySQL homepage but couldn't find anything
 specific. Anyone know how to do this? Cheers.
 
 Mark Sargent.
 
 

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

Re: Prinitng MySQL Structure from ssh

[Mark Sargent]

alexj wrote:

try mysqldump --no-data database-name -u user-name

Hi All,

thanx, but get the following,

mysql mysqldump --no-data osc -u admin;
ERROR 1064: You have an error in your SQL syntax near 'mysqldump 
--no-data osc -u admin' at line 1


Cheers.

Mark Sargent


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

Problem running multi master replication

[Leonardus Setiabudi]

Hi All,

I have a problem with my multi master replication plan.
I have set my server (linux) to run 4 instance of mysqld, each with a 
different port, socket, tmpdir, log file, relay log file, master info 
and relay info.


the illustration as follow :

Server 1 (non slave)

db1
db2
db3
db4

Server 2 (slave from other machine)

db2

Server 3 (slave from other machine)

db3

Server 4 (slave from other machine)

db4

each slave run well and replicate successfully
but the problem is, Server 1 unable to see the update from the other 
slave server, although i can see the update from each originating slave 
server.


Is this a problem with mysql (locking issue?), or file system issue?
I am using mysql 4.1.11 on mandrake linux, with ext3 fs and all the 
tables were MYISAM


Server 1 was supposed to be somekind of data warehouse, to provide data 
from many distributed servers phisically separated.


Would someone please help me, i have googled this and red the manual, 
but couldnt find similiar situation.


TIA
-
Leo

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

Re: Prinitng MySQL Structure from ssh

[Duncan Hill]

On Friday 07 April 2006 10:30, Mark Sargent wrote:
 alexj wrote:
  try mysqldump --no-data database-name -u user-name

 Hi All,

 thanx, but get the following,

 mysql mysqldump --no-data osc -u admin;
 ERROR 1064: You have an error in your SQL syntax near 'mysqldump
 --no-data osc -u admin' at line 1

mysqldump is a shell command, not a mysql command.


Scanned by mailCritical.

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

Re: Prinitng MySQL Structure from ssh

[Dominik Klein]

mysql mysqldump --no-data osc -u admin;
ERROR 1064: You have an error in your SQL syntax near 'mysqldump 
--no-data osc -u admin' at line 1


You are supposed to execute that from a shell, not from within mysql Client.

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

Re: Prinitng MySQL Structure from ssh

[alexj]

hey,

   run mysqldump from your command prompt and not you mysql prompt.

[EMAIL PROTECTED]:~ mysqldump --no-data osc -u admin
-- MySQL dump 10.9
--
-- Host: localhostDatabase: osc


Thanx
Alex

On Fri, 7 Apr 2006, Mark Sargent wrote:

 alexj wrote:
  try mysqldump --no-data database-name -u user-name
 Hi All,
 
 thanx, but get the following,
 
 mysql mysqldump --no-data osc -u admin;
 ERROR 1064: You have an error in your SQL syntax near 'mysqldump
 --no-data osc -u admin' at line 1
 
 Cheers.
 
 Mark Sargent
 
 
 

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

writing to a database on a read-only medium

[Frank]

Hi,

is there a possibility to configure a MySQL database in such a way that I
can store the database on a read-only medium (say a CD), but still be able
to write to the database (without having to copy the datafiles to disk or
memory first)?

What I have in mind is something like telling MySQL to use a different
datafile for the new data. 
Could partitioning come to the rescue? Although the documentation mentions
that it is possible that partions reside in different physical locations
(Partitioning takes this notion a step further, by allowing you to
distribute portions of individual tables across a filesystem according to
rules which you can set largely as needed), I could not find any
description of how this could be done, or whether it has actually been
implemented.

The solution should work with either MyISAM or INNODB, or better still, be
independent of the engine.

Cheers,
Frank


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

Re: Many Attributes Required Design Query

[Mark Sargent]

Hi All,

I forgot to mention, although a general range exists for say, Jeans, 
36-44 for existence, there are variations for different areas for 
different labels. Example, a 34 for Jean A is say 42 in the waist, but 
Jean B is 44, within the same maker. I find that as making it more 
complexed. Any further thoughts on this? Cheers.


Mark Sargent.


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

Re: Prinitng MySQL Structure from ssh

[Mark Sargent]

Duncan Hill wrote:

mysqldump is a shell command, not a mysql command.

Hi All,

oops, what a blunder. lol. Cheers.

Mark Sargent.


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

stored proc/func

[[EMAIL PROTECTED]]

Hi!

Can I return a record set from a stored procedure/function in mysql?

Kind regards


Prueba el Nuevo Correo Terra; Seguro, Rápido, Fiable.


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

Re: need to select correct package

[balaraju mandala]

I am planning to upgrade mysql ver 4 to mysql ver 5 but i am getting this
errors

[EMAIL PROTECTED] trinity]$rpm -Uvh
MySQL-server-standard-5.0.19-0.rhel4.i386.rpm
warning: MySQL-server-standard-5.0.19-0.rhel4.i386.rpm: V3 DSA signature:
NOKEY, key ID 5072e1f5
error: Failed dependencies:
libmysqlclient.so.14 is needed by (installed) mod_auth_mysql-
2.6.1-2.2.i386
libmysqlclient.so.14 is needed by (installed)
cyrus-sasl-sql-2.1.19-5.EL4.i386
libmysqlclient.so.14 is needed by (installed)
dovecot-0.99.11-2.EL4.1.i386
libmysqlclient.so.14 is needed by (installed)
perl-DBD-MySQL-2.9004-3.1.i386
libmysqlclient.so.14 is needed by (installed)
php-mysql-4.3.9-3.8.i386

Please can anybody help me

Re: need to select correct package

[mysql]

Hi balaraju.

You could try:

Linux x86 generic RPM (statically linked against glibc 
2.2.5) downloads
Server  5.0.19-013.5M   Pick a mirror
MD5: 118abbb9c8ee5ff212fd2797fcde35e8
Max 5.0.19-02.8MPick a mirror
MD5: a49c484f561753678319678c1cfcc6c3
Benchmark/test suites   5.0.19-05.6MPick 
a mirror
MD5: ff11b603da7544c4bb7b8e2c7b93997c
Client programs 5.0.19-06.1MPick a 
mirror
MD5: 5b3e0d88e11ddc7f4a97aecefb12b36e
Libraries and header files  5.0.19-03.5M
Pick a mirror
MD5: 6a940c8a4123c4c733e55c77218e230f
Shared client libraries 5.0.19-01.7MPick 
a mirror
MD5: d7a4016797424da3dc957643b45e3076
Shared compatibility libraries
(3.23, 4.x, 5.x libs in same package)   5.0.19-0
3.3MPick a mirror
MD5: b55dd27aafeb22b22c3cd8a26120dfa4

Or if that does not work, go for:

Linux (non RPM package) downloads (platform notes)
Linux (x86, glibc-2.2, standard is static, gcc)   
Standard5.0.19  28.4M   Pick a mirror
MD5: c979236136e416497d951a84e28c676e | Signature
Max 5.0.19  36.8M   Pick a mirror
MD5: 524f6e26065aaf0ed5e55d77aef81305 | Signature
Debug   5.0.19  53.4M   Pick a mirror
MD5: 5cd9e1694b8b20443613627755260f3b | Signature

Make sure you download the statically linked versions, as 
they do not rely on external libraries to work.

Both the above packages are pre-compiled and either of them 
should work ok.

The RPM version is the easiest to install, but IMO using the 
non-RPM version will give you the most flexibility if you 
want to upgrade to a later version of mysql. I guess you 
could try and install the RPM version first, and then try 
the non-RPM version later on if you want to upgrade.

Regards

Keith 

In theory, theory and practice are the same;
in practice they are not.

On Fri, 7 Apr 2006, balaraju mandala wrote:

 To: mysql@lists.mysql.com mysql@lists.mysql.com
 From: balaraju mandala [EMAIL PROTECTED]
 Subject: Re: need to select correct package
 
 I am planning to upgrade mysql ver 4 to mysql ver 5 but i am getting this
 errors
 
 [EMAIL PROTECTED] trinity]$rpm -Uvh
 MySQL-server-standard-5.0.19-0.rhel4.i386.rpm
 warning: MySQL-server-standard-5.0.19-0.rhel4.i386.rpm: V3 DSA signature:
 NOKEY, key ID 5072e1f5
 error: Failed dependencies:
 libmysqlclient.so.14 is needed by (installed) mod_auth_mysql-
 2.6.1-2.2.i386
 libmysqlclient.so.14 is needed by (installed)
 cyrus-sasl-sql-2.1.19-5.EL4.i386
 libmysqlclient.so.14 is needed by (installed)
 dovecot-0.99.11-2.EL4.1.i386
 libmysqlclient.so.14 is needed by (installed)
 perl-DBD-MySQL-2.9004-3.1.i386
 libmysqlclient.so.14 is needed by (installed)
 php-mysql-4.3.9-3.8.i386
 
 Please can anybody help me

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

Re: stored proc/func

[Michael Louie Loria]

 Hi!

 Can I return a record set from a stored procedure/function in mysql?

 Kind regards

yes - stored procedures
no - stored function


Michael Louie Loria
LoRz Technology Solutions
http://www.lorztech.com



signature.asc
Description: OpenPGP digital signature

Re: select all events from (today-N) days

[Joerg Bruehe]

Hi Ravi, all!


Ravi Malghan wrote:

Ok. I found something. But wondering if this is most
efficient
Events for yesterday:
select count(*) from EVENT_DATA where
FROM_UNIXTIME(utime,'%Y-%m-%d') = (CURDATE() -
INTERVAL 1 DAY);

Events for last week
select count(*) from EVENT_DATA where
FROM_UNIXTIME(utime,'%U') =
(DATE_FORMAT(CURDATE(),'%v')-1);

TIA
Ravi
--- Ravi Malghan [EMAIL PROTECTED] wrote:


Hi: I have a date/time field (utime) which has unix
time in epoch time. I want to select events from
yesterday and another statement for all events from
previous week.



No, that is not the most efficient way.

If you have any sizable amount of data, you need an index to allow your 
where condition to be evaluated without accessing all records (also 
called table scan).
For the efficient use of that index, you should ensure that the where 
condition is of the form

   column_value   comparison_operator  expression

It does not matter whether expression is complicated, it needs to be 
computed only once, but column_value should just be the column name 
and not a function / expression using it.


So what you need is
   select count(*) from EVENT_DATA
  where utime   (seconds of your period start)

Sorry, I lack the time to scan the manual for the correct expression to 
calculate that start value.



HTH,
Joerg

--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com


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

A doubt in SELECT query

[subramani]

hello all,

In which order the datas are displayed, when the SELECT quey is used ?
Is it random or the order in which the datas are inserted?

-- r.subramani
My log file: http://subramanitce.blogspot.com

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

Re: A doubt in SELECT query

[Barry]

subramani wrote:

hello all,

In which order the datas are displayed, when the SELECT quey is used ?
Is it random or the order in which the datas are inserted?

-- r.subramani
My log file: http://subramanitce.blogspot.com


Random

--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

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

Re: Problem running multi master replication

[SGreen]

Leonardus Setiabudi [EMAIL PROTECTED] wrote on 04/07/2006 05:33:50 
AM:

 Hi All,
 
 I have a problem with my multi master replication plan.
 I have set my server (linux) to run 4 instance of mysqld, each with a 
 different port, socket, tmpdir, log file, relay log file, master info 
 and relay info.
 
 the illustration as follow :
 
 Server 1 (non slave)
 
 db1
 db2
 db3
 db4
 
 Server 2 (slave from other machine)
 
 db2
 
 Server 3 (slave from other machine)
 
 db3
 
 Server 4 (slave from other machine)
 
 db4
 
 each slave run well and replicate successfully
 but the problem is, Server 1 unable to see the update from the other 
 slave server, although i can see the update from each originating slave 
 server.
 
 Is this a problem with mysql (locking issue?), or file system issue?
 I am using mysql 4.1.11 on mandrake linux, with ext3 fs and all the 
 tables were MYISAM
 
 Server 1 was supposed to be somekind of data warehouse, to provide data 
 from many distributed servers phisically separated.
 
 Would someone please help me, i have googled this and red the manual, 
 but couldnt find similiar situation.
 
 TIA
 -
 Leo
 


Just so that we are on the same page, let's review a few definitions as 
they relate to database replication:

master - the server from which changes will propogate during replication. 
slave - the server to which replicated changes will be applied during 
replication.

According to the rules of MySQL replication, as I understand them, each 
master can be a host to many slaves but each slave can only receive 
changes from a single master. 

You said:
 each slave run well and replicate successfully

I assume you mean servers 2-4?

 but the problem is, Server 1 unable to see the update from the other 
 slave server, although i can see the update from each originating slave 

other slave server?  I don't quite understand. If Server 1 is supposed 
to be receiving updates then it is the slave and the process it is getting 
the changes from is called the master. A single server process can 
actually have both roles as in this situation:

Server A - replicates to - Server B - replicates to - Server C

In this case A is the master of B. B is a slave of A.  B is also a master 
to C which is the slave of B. Multi-master replication would be something 
like this:

Server A - Server C
Server B - Server C

In this plan, Server C would be recieving changes from both A and B. 
However the current design of MySQL limits you to having only *ONE* master 
per slave for many excellent reasons (search the recent archives of this 
list for a few). 

Can you possibly re-explain your situation using the terms master and 
slave as I just used them?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

New MySQL Transaction Engine Announced

[mos]

In case anyone is interested in where MySQL is going with transactions

http://www.computerworld.com.au/index.php/id;1262876365;relcomp;1

Mike


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

Re: A doubt in SELECT query

[Rhino]

If the sequence in which the result set is displayed is important to you, 
you will need to use SQL clauses to force the sequence you want. ORDER BY is 
the main way of accomplishing this although other clauses, like GROUP BY and 
DISTINCT, can also affect the sequence. But ORDER BY is the normal method of 
forcing the output to be in a specific order.


--
Rhino

- Original Message - 
From: subramani [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Friday, April 07, 2006 10:22 AM
Subject: A doubt in SELECT query


hello all,

In which order the datas are displayed, when the SELECT quey is used ?
Is it random or the order in which the datas are inserted?

-- r.subramani
My log file: http://subramanitce.blogspot.com

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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.5/303 - Release Date: 06/04/2006




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.5/303 - Release Date: 06/04/2006


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

Re: New MySQL Transaction Engine Announced

[Martijn Tonies]

 In case anyone is interested in where MySQL is going with transactions
 
 http://www.computerworld.com.au/index.php/id;1262876365;relcomp;1

As expected :-)

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

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

UNSIGNED_FLAG returned for column type timestamp ?

[Barbara Deaton]

I'm using the MySQL 5.0.15 client to a 5.0 server all 32-bit windows.

It appears that the UNSIGNED_FLAG is being returned for column types of 
timestamp.

Checking the flags variable of the MYSQL_FIELD structure indicates that the 
following flags are returned:

Field-flags = 1249

NOT_NULL_FLAG = 1
UNSIGNED_FLAG = 32
ZEROFILL_FLAG = 64
BINARY_FLAG = 128
TIMESTAMP_FLAG = 1024

Flag value totals = 1249.

I thought the TIMESTAMP_FLAG was deprecated so I was surprised to see it 
returned and I'm not sure why the UNSIGNED_FLAG is returned.

You can create an unsigned timestamp column:

mysql create table barbts (col1 timestamp unsigned, col2 int unsigned);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'unsig
ned, col2 int unsigned)' at line 1

Is this correct behavior that the UNSIGNED_FLAG is returned and if so can you 
tell me the justification?

Thanks for any information.
Barbara

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

Re: Replication for historical data

[Atle Veka]

Do you have control over your DELETE queries? If so, 'SET SQL_LOG_BIN=0'
would be an easy implementation.

A common approach in my experience is to have a script query the master
and archive data to a second database before any deletes are done.

If you have no control over the DELETE queries and when they occur, you
may have to look into parsing the binlogs yourself...


Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Fri, 7 Apr 2006, Ian Collins wrote:

 Hi,
 I have a customer who wants to be able to replicate their live MySQL
 database to a second server, but not to have any data deleted.
 i.e., they want to accumulate the data.

 I don't believe you can do this with replication. Does anyone know a way
 of doing this?

 Cheers,
 Ian.




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

Reserevd Error -7776 -- Urgent

[C K]

Dear Friends,
I have linked tables from MySQL through ODBC DSN in Access 2003.
when I go for updating any record in any linked table then it gives me
error as follows:
Reserved Error (-7776); there is no message for this error.
What will be the problem? Please help.
Is this error from MS ACCESS  or for MySQL.
Thanks  Regards,
CPK

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

Re: Reserevd Error -7776 -- Urgent

[SGreen]

C K [EMAIL PROTECTED] wrote on 04/07/2006 01:35:22 PM:

 Dear Friends,
 I have linked tables from MySQL through ODBC DSN in Access 2003.
 when I go for updating any record in any linked table then it gives me
 error as follows:
 Reserved Error (-7776); there is no message for this error.
 What will be the problem? Please help.
 Is this error from MS ACCESS  or for MySQL.
 Thanks  Regards,
 CPK
 

Have you read through this:
http://dev.mysql.com/doc/refman/4.1/en/msaccess.html

or this:
http://dev.mysql.com/doc/refman/4.1/en/myodbc-with-vb.html

or checked here:
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp
(quoted)
-7776   SQLGetData(SQL_C_TIMESTAMP) Illegal date/time value returned.


My quick suggestion: Make sure that all of your timestamp and datetime 
fields are within their expected ranges.

Sorry I can't be more helpful but it's a busy day here

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: upgrading problem

[Eric Braswell]

You need root privileges.

Login as root or use sudo. E.g.

sudo rpm -Uvh MySQL-shared-compat-5.0.19-0.rhel4.i386.rpm

If you don't have root or sudo access, you can't perform this upgrade. 
You could possibly use the --prefix and --relocate rpm install options 
to install mysql into a directory for which you do have write 
permissions, but that gets complicated.


Eric


balaraju mandala wrote:

Hi Comunity,

I am getting problem while i am upgrading to MySql ver 4 to MySql ver 5. I
planned first to install MySQL-shared-compat-5.0.19-0.rhel4.i386.rpm, but
i am getting following error.

Preparing...###
[100%]
   1:MySQL-shared-compat###
[100%]
error: unpacking of archive failed on file
/usr/lib/libmysqlclient.so;44361c01: cpio: symlink failed - Permission
denied

i am unable to understand the problem. Please help me.




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

Re: Reserevd Error -7776 -- Urgent

[C K]

On 4/7/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:


 C K [EMAIL PROTECTED] wrote on 04/07/2006 01:35:22 PM:


   Dear Friends,
   I have linked tables from MySQL through ODBC DSN in Access 2003.
   when I go for updating any record in any linked table then it gives me
   error as follows:
   Reserved Error (-7776); there is no message for this error.
   What will be the problem? Please help.
   Is this error from MS ACCESS  or for MySQL.
   Thanks  Regards,
   CPK
  


 Have you read through this:
 http://dev.mysql.com/doc/refman/4.1/en/msaccess.html

 or this:
 http://dev.mysql.com/doc/refman/4.1/en/myodbc-with-vb.html

 or checked here:
 http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp
 (quoted)
 -7776 SQLGetData(SQL_C_TIMESTAMP) Illegal date/time value
 returned.
 

 My quick suggestion: Make sure that all of your timestamp and datetime
 fields are within their expected ranges.
 I am using MySQL server 5.0.17 and ODBC 3.51.12 for ODBC connection.
When I update an y data from MySQL Administrator or SQLYog it gets updated.

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

Re: Reserevd Error -7776 -- Urgent

[SGreen]

C K [EMAIL PROTECTED] wrote on 04/07/2006 01:59:39 PM:

 On 4/7/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 
 
  C K [EMAIL PROTECTED] wrote on 04/07/2006 01:35:22 PM:
 
 
Dear Friends,
I have linked tables from MySQL through ODBC DSN in Access 2003.
when I go for updating any record in any linked table then it gives 
me
error as follows:
Reserved Error (-7776); there is no message for this error.
What will be the problem? Please help.
Is this error from MS ACCESS  or for MySQL.
Thanks  Regards,
CPK
   
 
 
  Have you read through this:
  http://dev.mysql.com/doc/refman/4.1/en/msaccess.html
 
  or this:
  http://dev.mysql.com/doc/refman/4.1/en/myodbc-with-vb.html
 
  or checked here:
  http://msdn.microsoft.com/archive/default.asp?url=/archive/en-
 us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp
  (quoted)
  -7776 SQLGetData(SQL_C_TIMESTAMP) Illegal date/time 
value
  returned.
  
 
  My quick suggestion: Make sure that all of your timestamp and datetime
  fields are within their expected ranges.
  I am using MySQL server 5.0.17 and ODBC 3.51.12 for ODBC connection.
 When I update an y data from MySQL Administrator or SQLYog it gets 
updated.
 

I see your point?  That must be why I *didn't* give you links that 
document working with MySQL with MySQL Administor OR SQLYog. I gave you 
links to using MS Access and ODBC with MySQL. 

Those pages (and the ones they link to) contain valuable information to 
help you learn how limited those products are compared with MySQL and how 
you need to adjust your data so that it fits within the boundaries that MS 
sets for *their* data manipulation tools.

The short explanation is that MySQL has more range and capacity than ODBC 
and Access can handle and you have to work within the MS limits if you 
want to use with those tools.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Reserevd Error -7776 -- Urgent

[C K]

I also tried folloing and got results as below:
1) I updated the default for timestamp filed as CURRENT TIMESTAMP as
mentioned in MySQL 5.1 manual.
But this not worked.
2) I dropped timestamp fiield from that table and refreshed link, then
I can ork properly. Now I can insert and update reocrds without any
problem.  But this against the comment and responce from MySQL
community that it is necessary to have a timestamp field to properly
view and edit data in linked MysQL table. Else it will give he error
as #deleted, But isn't it strange that now it is not giving me any 
such error. ? Why?
Thanks.

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

Re: Many Attributes Required Design Query

[SGreen]

Mark Sargent [EMAIL PROTECTED] wrote on 04/06/2006 10:45:43 PM:

 [EMAIL PROTECTED] wrote:
  I agree with the basic design: one table for all of your basic objects 

  (shirts, pants, coats, shoes, etc), one table for all of your 
attributes 
  (see Barry's response), a sku table equating objects (differentiated 
by 
  their attributes) and their inventory quantities (on hand, 
backordered, 
  etc), and one more to relate SKU to all applicable attributes.
  
 Hi All,
 
 Shawn, what is a SKU?

I can't explain it any better than this:  
http://en.wikipedia.org/wiki/Stock_Keeping_Unit

To borrow an analogy from biology SKU relates to model as species relates 
to genus. Each SKU uniquely identifies a variation of a basic object. 
Those variations can be due to size changes, colorations, decorations, or 
style. One model number may have dozens of SKUs associated with it. Each 
SKU number is used to track how many of each size/style/etc is in stock or 
on order.

In the grocery store, SKU numbers are the barcodes on the labels. Del 
Monte makes several sizes of canned whole tomatoes (a product). They pack 
those in different sizes of cans. Each can gets its own barcode (SKU) so 
that the store can assign the correct price during checkout. These 
barcodes are also used for inventory control (Imagine the thought process 
of the manager We have 6 cases of #10 cans but we are down to only 2 case 
of 12oz cans. We probably need to order more 12oz cans.)

You probably need to be able to provide the same level of detail in your 
inventory control system.

  Each SKU represents one combination of a base object with a particular 
set 
  of attributes. IT's the SKU number that important for inventory 
control 
  and that will uniquely identify a size 8 pair of jeans from a size 9 
pair 
  or a pair of black size 8s from a pair of red size 8s all in the same 
  style (cut) from the same manufacturer
  
 Makers:
 maker_id
 maker_desc
 
 Products:
 prod_id
 prod_code
 maker_id
 prod_desc
 attri_id
 object_id
 
 Attributes:
 attri_id
 attri_desc
 
 Objects:
 object_id
 object_desc
 
 That is where I got to, as I've never done this kinda design before. 
 Thrown into the deep end, I guess. May I ask for more assistance with 
 this? Where does the quantity go? Any tutorials on this kind of design? 
 Cheers.
 
 Mark Sargent.

I dont know about tutorials but I Googled SKU and got appx 88 million 
hits. I also Googled for inventory control schema and got over 900 
thousand hits. Some of them may give you some ideas of what your database 
needs to track or how to organize your tables.

Most user mangement front-ends hide a lot of the complexity that goes into 
a database design like this. Make sure you can store and retrieve the 
information you will need in order to answer the questions your users want 
to be able to ask your system. I know that sounds circuituous but if you 
know what your customers want to know, you can create what they need to be 
able to know it.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Mysql over HTTP

[Jangita]

Hi all,

It's all well and good having mysql using port 3309 (or any other port for
that matter); I'm writing an application that will be used by loads of users
off the internet - so I'm expecting firewall issues.

One easy way is to have the mysql traffic flow over port 80; that works
sometimes but not with intelligent firewalls that only allow http traffic or
companies that have only http ports open (yes there are many of these)

One method I've thought about that goes around this is to write an interface
that sits in-front of the mysql client and translates the mysql traffic into
http get or put requests and use wininet.dll to send these requests to the
server. Since these are get and put requests I'll have to write and install
a cgi or isapi dll on the webserver which translates these requests into
normal traffic and relays it to the mysql server and vice versa.

This would in effect produce an environment where as long as you can browse
you can use the mysql client application (with iexplore because of wininet)
- and with linux to some extent and this will also be able to go thru http
proxies etc (basically anything that ie can go thru)

Before I get my hands dirty; is there anything like this that exists out
there? I have a week leave from Monday and if there isn't well im about to
start writing one.

Jangita


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

mysql on tmpfs

[Atle Veka]

Anyone have any experience to share about running mysql on a linux tmpfs
(using memory)?

For us it's worked out pretty well and is normally operating lightning
fast compared to an identical SCSI based system. However, even though
there is plenty of free memory linux makes weird decisions from time to
time, temporarily killing performance, swapping in/out to make room.

debian w/ 2.6.15 kernel


Thanks,

Atle
-
Flying Crocodile Inc, Unix Systems Administrator

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

Re: Return virtual records

[Ed Reed]

Anyone have an idea on this?

Can anyone explain how I might be able to return a numbers of records based on 
the sum of a quantity in a field in the same table? (After I read that it 
sounds even confusing to me).
 
Let me explain. I have records like this,
 
Part#Qty
1254  5
1414  2
147581
1254  6
1024  3
1254  1
 
 
Now if I did a query like this
Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254
 
I would expect my results to look like this
Part#   Sum(Qty)
1254   12
 
But what I really want is this
Part#
1254
1254
1254
1254
1254
12541254
1254
12541254
1254
1254
 
So 12 virtual records for the count of the records returned from the Sum()
 
Can someone help me with this?
 
- Thanks






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

Re: Mysql over HTTP

[Eric Braswell]

No need to re-invent that wheel:

Just use port=80 in my.cnf or start with --port=80

See http://dev.mysql.com/doc/refman/5.0/en/server-options.html

Eric


--
Eric Braswell
Web Manager MySQL AB
Cupertino, USA


Jangita wrote:

Hi all,

It's all well and good having mysql using port 3309 (or any other port for
that matter); I'm writing an application that will be used by loads of users
off the internet - so I'm expecting firewall issues.

One easy way is to have the mysql traffic flow over port 80; that works
sometimes but not with intelligent firewalls that only allow http traffic or
companies that have only http ports open (yes there are many of these)

One method I've thought about that goes around this is to write an interface
that sits in-front of the mysql client and translates the mysql traffic into
http get or put requests and use wininet.dll to send these requests to the
server. Since these are get and put requests I'll have to write and install
a cgi or isapi dll on the webserver which translates these requests into
normal traffic and relays it to the mysql server and vice versa.

This would in effect produce an environment where as long as you can browse
you can use the mysql client application (with iexplore because of wininet)
- and with linux to some extent and this will also be able to go thru http
proxies etc (basically anything that ie can go thru)

Before I get my hands dirty; is there anything like this that exists out
there? I have a week leave from Monday and if there isn't well im about to
start writing one.

Jangita





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

Re: writing to a database on a read-only medium

[sheeri kritzer]

Frank,

1)  Check out the FEDERATED storage engine, that might help.

2)  You cannot specify a different location for a read database and a
write database.

3)  Another solution is to use multiple databases -- each database is
just a directory, so you could try to symlink:

ie, if your datadir is /var/lib/mysql, and the cd has the files at
/mnt/cdrom/dbfiles/,
then

ln -s /var/lib/mysql/dbname/ /mnt/cdrom/dbfiles

This method is not recommended, but it's a way to do it.

-Sheeri

On 4/7/06, Frank [EMAIL PROTECTED] wrote:
 Hi,

 is there a possibility to configure a MySQL database in such a way that I
 can store the database on a read-only medium (say a CD), but still be able
 to write to the database (without having to copy the datafiles to disk or
 memory first)?

 What I have in mind is something like telling MySQL to use a different
 datafile for the new data.
 Could partitioning come to the rescue? Although the documentation mentions
 that it is possible that partions reside in different physical locations
 (Partitioning takes this notion a step further, by allowing you to
 distribute portions of individual tables across a filesystem according to
 rules which you can set largely as needed), I could not find any
 description of how this could be done, or whether it has actually been
 implemented.

 The solution should work with either MyISAM or INNODB, or better still, be
 independent of the engine.

 Cheers,
 Frank


 --
 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: Determining if a trigger exists

[sheeri kritzer]

Try the documentation.

http://mysql.com/triggers
sends you to
http://dev.mysql.com/doc/refman/5.0/en/triggers.html
from there you can click on CREATE TRIGGER SYNTAX
to get to
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
which clearly shows that syntax is not valid.

Alternatively, you could try it yourself and see that it fails.

-Sheeri

On 4/7/06, Adrian Co [EMAIL PROTECTED] wrote:
 Hi,

 Sorry if I wasn't very clear with my question. I was hoping to obtain
 the functionality such that I could do something similar to:

 CREATE TRIGGER IF NOT EXISTS 

 Because I usually get a trigger already exists in my script. I might be
 missing something.

 The script basically does the basic things like create database if not
 exists, create table if not exists, etc. I was hoping the same thing for
 triggers maybe? Is this possible?

 Regards,
 Adrian Co

 Jim wrote:

 There is a TRIGGERS table in the information_schema
 
 Eg.
 select Trigger_Name from TRIGGERS
 where trigger_name = 'TI_AGENT' AND trigger_schema = 'PROPLINK'
 
 
 -Original Message-
 From: Adrian Co [mailto:[EMAIL PROTECTED]
 Sent: Friday, 7 April 2006 1:54 PM
 To: mysql@lists.mysql.com
 Subject: Determining if a trigger exists
 
 Hi,
 
 Whats the simplest way to determine if a trigger already exists?
 
 i.e. For tables you have: CREATE TABLE IF NOT EXISTS ...
 
 Is there a way to do
 
 CREATE TRIGGER IF NOT EXISTS
 
 I'm using MySQL 5.0 btw.
 
 Thanks!
 
 Regards,
 Adrian
 
 
 


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



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

Re: How could I know which transaction or thread hold the lock

[sheeri kritzer]

While the query is still running, type

mysql show processlist
or
mysql show full processlist

find the query(ies) with the State column having a value of Locked

-Sheeri

On 4/7/06, 古雷 [EMAIL PROTECTED] wrote:
 Hello:

 How could I know  which transaction or thread hold the lock?

 For example:

 show innodb status\G

 ---TRANSACTION 0 78076313, ACTIVE 3906 sec, process no 12729, OS thread id 
 2952076208 starting index read
 mysql tables in use 1, locked 1
 LOCK WAIT 2 lock struct(s), heap size 1024
 MySQL thread id 268, query id 40997 localhost root Updating
 UPDATE CORPSMSINFO SET PERMISSIONS='1000' WHERE 
 CUSTOMERID='100010A'
 --- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 0 page no 20032 n bits 192 index `PRIMARY` of table 
 `ea191/CORPSMSINFO` trx id 0 78076313 lock_mode X locks rec but not gap 
 waiting
 Record lock, heap no 122 PHYSICAL RECORD: n_fields 19; compact format; info 
 bits 0
  0: len 11; hex 3130303030303030313041; asc 100010A;; 1: len 6; hex 
 04a755e1; asc U ;; 2: len 7; hex 00562927be; ascV)' ;; 3: len 
 3; hex 415350; asc ASP;; 4: len 4; hex 4435c9e1; asc D5  ;; 5: len 5; hex 
 61646d696e; asc admin;; 6: len 4; hex 4434cb9b; asc D4  ;; 7: len 4; hex 
 4434cb9b; asc D4  ;; 8: len 1; hex 30; asc 0;; 9: SQL NULL; 10: SQL NULL; 11: 
 SQL NULL; 12: len 4; hex 4434cb9b; asc D4  ;; 13: len 4; hex 4434cb9b; asc D4 
  ;; 14: len 1; hex 30; asc 0;; 15: len 2; hex 3220; asc 2 ;; 16: SQL NULL; 
 17: len 30; hex 313030303030303030303030303030303030303030303030303030303030; 
 asc 10;...(truncated); 18: len 4; hex 8000; 
 asc ;;

 --

 How could I find which one hold that lock?

 Regards,

 gu lei

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

Re: ~How to load a mysql data file into sybase~

[sheeri kritzer]

Try the sybase lists, or googling for sybase import file.

-Sheeri

On 4/6/06, Mohammed Abdul Azeem [EMAIL PROTECTED] wrote:
 Hello,

 I have a mysql data bcp file ( using select into outfile ) from a table
 in mysql database. I have a similar table existing in a sybase database.
 I need to bcp in the mysql data file into sybase. Can anyone help me on
 how to go about the same ?

 Thanks in advance,
 Abdul.

 
 This email has been Scanned for Viruses!
   www.newbreak.com

 

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



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

Re: Return virtual records

[sheeri kritzer]

Here's an idea -- patience is a virtue.  Some of us read the list when
we get a chance, and can't just jump on every e-mail because we
actually do work at work.

mysql create table Orders (partno int unsigned not null, quant
tinyint unsigned not null);
Query OK, 0 rows affected (0.07 sec)

mysql insert into Orders
VALUES(1254,5),(1414,5),(14758,1),(1254,6),(1024,3),(1254,1);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql select partno,SUM(quant) from Orders where partno=1254 group by partno;
+++
| partno | SUM(quant) |
+++
| 1254   | 12 |
+++
1 row in set (0.00 sec)

mysql select repeat(concat(partno,' '),SUM(quant)) from Orders where
partno=1254 group by partno;
+--+
| repeat(concat(partno,' '),SUM(quant))|
+--+
| 1254 1254 1254 1254 1254 1254 1254 1254 1254 1254 1254 1254  |
+--+
1 row in set (0.00 sec)

So something like that.  I don't know that you can create more than
one record, though.

-Sheeri

On 4/7/06, Ed Reed [EMAIL PROTECTED] wrote:
 Anyone have an idea on this?

 Can anyone explain how I might be able to return a numbers of records based 
 on the sum of a quantity in a field in the same table? (After I read that it 
 sounds even confusing to me).

 Let me explain. I have records like this,

 Part#Qty
 1254  5
 1414  2
 147581
 1254  6
 1024  3
 1254  1


 Now if I did a query like this
 Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254

 I would expect my results to look like this
 Part#   Sum(Qty)
 1254   12

 But what I really want is this
 Part#
 1254
 1254
 1254
 1254
 1254
 12541254
 1254
 12541254
 1254
 1254

 So 12 virtual records for the count of the records returned from the Sum()

 Can someone help me with this?

 - Thanks






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



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

RE: Return virtual records

[Jay Blanchard]

[snip]
Anyone have an idea on this?

Can anyone explain how I might be able to return a numbers of records
based on the sum of a quantity in a field in the same table? (After I
read that it sounds even confusing to me).
 
Let me explain. I have records like this,
 
Part#Qty
1254  5
1414  2
147581
1254  6
1024  3
1254  1
 
 
Now if I did a query like this
Select Part#, Sum(Qty) From table1 Group By Part# Where Part#=1254
 
I would expect my results to look like this
Part#   Sum(Qty)
1254   12
 
But what I really want is this
Part#
1254
1254
1254
1254
1254
12541254
1254
12541254
1254
1254
 
So 12 virtual records for the count of the records returned from the
Sum()
[/snip]

Please do not hijack threads, open a new e-mail and send it to the list
address.

SELECT REPEAT(Part#, count(Part#)) FROM table WHERE Part# = '1254'


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

Re: Mysql over HTTP

[Eric Braswell]

Jangita wrote:
 Yes this is very true Eric, thanks.

 BUT there are LOADS of firewalls that don't allow non http traffic (like
 mysql) over port 80; and your method wont work if all that's 
available is a

 http proxy!


Ah, so you want to be able to bypass a firewall that does stateful 
inspection and -only- allows plain old http traffic?


http://www.vbmysql.com/articles/security/bypass-firewall-libmywitch.html

Not really aware of any other options, but I'm assuming others have 
solved this problem. I know there are some fairly generic HTTP tunneling 
tools available, but I don't know how well they would work with MySQL 
(if at all).


I would imagine it would suck no matter the solution. Are you really 
sure this is an issue for your application?


Eric



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

RE: Reserevd Error -7776 -- Urgent

[Gordon]

I have been linking MySQL tables into access for versions 3.23 thorough
5.17. The only way I have been able to deal with insert/update tables with a
timestamp field is to build a query which includes all of the fields except
the timestamp field. You can then do your insert or update against the
query. ACCESS/ODBC does not know about the timestamp field so they are
happy. If you specify CURRENT TIMESTAMP on the timestamp field in the MySQL
definition, MySQL will populate the field every time the row is changed or a
new row added.

 -Original Message-
 From: C K [mailto:[EMAIL PROTECTED]
 Sent: Friday, April 07, 2006 1:50 PM
 To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
 Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com; [EMAIL PROTECTED]
 Subject: Re: Reserevd Error -7776 -- Urgent
 
 I also tried folloing and got results as below:
 1) I updated the default for timestamp filed as CURRENT TIMESTAMP as
 mentioned in MySQL 5.1 manual.
 But this not worked.
 2) I dropped timestamp fiield from that table and refreshed link, then
 I can ork properly. Now I can insert and update reocrds without any
 problem.  But this against the comment and responce from MySQL
 community that it is necessary to have a timestamp field to properly
 view and edit data in linked MysQL table. Else it will give he error
 as #deleted, But isn't it strange that now it is not giving me any
 such error. ? Why?
 Thanks.
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


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

Re: Determining if a trigger exists

[Adrian Co]

Hi,

Yes, the syntax is not valid. I have tried it. :-)

I was hoping for maybe a workaround to simulate the described 
functionality. There might also be something wrong with the structure of 
my script. Maybe I shouldn't be creating triggers there? But I was 
thinking if the CREATE DATABASE and CREATE TABLE have a IF NOT EXISTS 
functionality, why should the triggers be any different? Maybe this is 
just an oversight, maybe the CREATE TRIGGER should have a IF NOT EXISTS 
functionality also? or I'm just doing something very wrong.


Maybe someone can point me in the right direction?

sheeri kritzer wrote:


Try the documentation.

http://mysql.com/triggers
sends you to
http://dev.mysql.com/doc/refman/5.0/en/triggers.html
from there you can click on CREATE TRIGGER SYNTAX
to get to
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
which clearly shows that syntax is not valid.

Alternatively, you could try it yourself and see that it fails.

-Sheeri

On 4/7/06, Adrian Co [EMAIL PROTECTED] wrote:
 


Hi,

Sorry if I wasn't very clear with my question. I was hoping to obtain
the functionality such that I could do something similar to:

CREATE TRIGGER IF NOT EXISTS 

Because I usually get a trigger already exists in my script. I might be
missing something.

The script basically does the basic things like create database if not
exists, create table if not exists, etc. I was hoping the same thing for
triggers maybe? Is this possible?

Regards,
Adrian Co

Jim wrote:

   


There is a TRIGGERS table in the information_schema

Eg.
select Trigger_Name from TRIGGERS
where trigger_name = 'TI_AGENT' AND trigger_schema = 'PROPLINK'


-Original Message-
From: Adrian Co [mailto:[EMAIL PROTECTED]
Sent: Friday, 7 April 2006 1:54 PM
To: mysql@lists.mysql.com
Subject: Determining if a trigger exists

Hi,

Whats the simplest way to determine if a trigger already exists?

i.e. For tables you have: CREATE TABLE IF NOT EXISTS ...

Is there a way to do

CREATE TRIGGER IF NOT EXISTS

I'm using MySQL 5.0 btw.

Thanks!

Regards,
Adrian



 


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

Social Network, linking members

[Martin Gallagher]

Hi,

I'm trying to find the most efficient way of linking members to one
another in a social networking application.

Currently I link them using 2 separate fields for the members: id1, id2. So,
to find people in your network you would do:

WHERE id1=%ID OR id2=%ID

This uses indexes inefficiently, especially when I add a JOIN to the
mixture. The JOIN must really slow down the query because I use an IF
control to get the right ID e.g.:

JOIN members ON members.id=IF(social_networking.id1=%ID,
social_networking.id2, social_networking.id1)

I believed in my ignorance that I could just use the SET data type, like
so:

WHERE FIND_IN_SET(%ID, link)

link being 2 integers separated by a comma: 5,19

However, SET only allows defined values :-(

A dynamic SET data type that supported integers would be sweet, but
doesn't seem to exist.

Does anybody have an efficient solution to this problem?

Cheers


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

Re: Social Network, linking members

[Jad madi]

How about something like this 


Users table
user_id
user_name
user_password
user_whatever



and then 
Relation table 
rel_id
user_id
friend_id


and then selecting from relations table



On Fri, 2006-04-07 at 14:47 -0700, Martin Gallagher wrote:
 urrently I link them using 2 separate fields for the members: id1,
 id2. So,


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

RE: Social Network, linking members

[Martin Gallagher]

That's similar to what I currently have set up.

The problem is it is a request based system.

Example table:
id1
id2
group
flag

When a person makes a request something such as the following is insert:

MYID, REQUEST_USER_ID, 'family', 0

If user REQUEST_USER_ID accepts it will be flagged as 1:

MYID, REQUEST_USER_ID, 'family', 1

Using 2 tables would help the indexing problem, but it's definetly NOT how I
want to achieve this.

-Original Message-
From: Jad madi [mailto:[EMAIL PROTECTED] 
Sent: 07 April 2006 15:00
To: Martin Gallagher
Cc: mysql@lists.mysql.com
Subject: Re: Social Network, linking members

How about something like this 


Users table
user_id
user_name
user_password
user_whatever



and then 
Relation table 
rel_id
user_id
friend_id


and then selecting from relations table



On Fri, 2006-04-07 at 14:47 -0700, Martin Gallagher wrote:
 urrently I link them using 2 separate fields for the members: id1,
 id2. So,


-- 
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: Social Network, linking members

[2wsxdr5]

Martin Gallagher wrote:


Hi,

I'm trying to find the most efficient way of linking members to one
another in a social networking application.

Currently I link them using 2 separate fields for the members: id1, id2. So,
to find people in your network you would do:

I'm not sure exactly what it is you are doing but I think this may be 
it.  You have a table of people and you want to know who is friends with 
who.  I know 'friend' may not be the best term to use but it is easier 
to type.  So I have my people table.


People{
 *PID,
 Name,
. . .
}

Then the Friend Table,

Friend{
*PID,
*FID
}

If you have person, John, with ID 234, and you want to know all his 
friends you can do this...

SELECT  f.FID, p.Name
FROM Friend f JOIN People p ON f.FID = p.PID
WHERE f.PID = 234

of course you have the problem where john has Joe as a friend but Joe 
doesn't have john as a friend.  This seeming inconsistency, may or may 
not be a problem depending on exactly what kind of a relationship you 
are trying to define. 


--
Chris W
KE5GIX

Gift Giving Made Easy
Get the gifts you want  
give the gifts they want
One stop wish list for any gift, 
from anywhere, for any occasion!

http://thewishzone.com


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

RE: Social Network, linking members

[Martin Gallagher]

of course you have the problem where john has Joe as a friend but Joe
doesn't have john as a friend.  This seeming inconsistency, may or may not
be a problem depending on exactly what kind of a relationship you are trying
to define.

You've just hit the nail on the head! That's exactly the problem.

I think I might just have to grin and bear what I already have :-(

-Original Message-
From: 2wsxdr5 [mailto:[EMAIL PROTECTED] 
Sent: 07 April 2006 15:11
To: Martin Gallagher; mysql@lists.mysql.com
Subject: Re: Social Network, linking members

Martin Gallagher wrote:

Hi,

I'm trying to find the most efficient way of linking members to one
another in a social networking application.

Currently I link them using 2 separate fields for the members: id1, id2.
So,
to find people in your network you would do:

I'm not sure exactly what it is you are doing but I think this may be 
it.  You have a table of people and you want to know who is friends with 
who.  I know 'friend' may not be the best term to use but it is easier 
to type.  So I have my people table.

People{
  *PID,
  Name,
. . .
}

Then the Friend Table,

Friend{
 *PID,
 *FID
}

If you have person, John, with ID 234, and you want to know all his 
friends you can do this...
SELECT  f.FID, p.Name
FROM Friend f JOIN People p ON f.FID = p.PID
WHERE f.PID = 234

of course you have the problem where john has Joe as a friend but Joe 
doesn't have john as a friend.  This seeming inconsistency, may or may 
not be a problem depending on exactly what kind of a relationship you 
are trying to define. 

-- 
Chris W
KE5GIX

Gift Giving Made Easy
Get the gifts you want  
give the gifts they want
One stop wish list for any gift, 
from anywhere, for any occasion!
http://thewishzone.com


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





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

Re: Social Network, linking members

[Matt Richards]

probuly sounds like noobish! or silly but what i would do is have a new 
table with 2 colums the first will be a login id and the second will be 
a login id


and i would just do like ...

id1   id2
JohnJoe
JohnAlex
JohnFred
Joe  Fred
FredAlex

would mean John as Alex, Fred and Joe as his friends
Joe has Fred as a friend
and Fred had Alex as a friend

and would just do like
SELECT `id2` FROM `freiends` WHERE `id1` = 'John';
to get all of Johns friends
and
SELECT `id1` FROM `friends` WHERE `id2` = 'Fred';
to get people that had fred set as their friends

and just work around that, of cause you can have id or account numbers 
and not names, can anybody see any problems with that ?


Martin Gallagher wrote:

of course you have the problem where john has Joe as a friend but Joe
doesn't have john as a friend.  This seeming inconsistency, may or may not
be a problem depending on exactly what kind of a relationship you are trying
to define.

You've just hit the nail on the head! That's exactly the problem.

I think I might just have to grin and bear what I already have :-(

-Original Message-
From: 2wsxdr5 [mailto:[EMAIL PROTECTED] 
Sent: 07 April 2006 15:11

To: Martin Gallagher; mysql@lists.mysql.com
Subject: Re: Social Network, linking members

Martin Gallagher wrote:

  

Hi,

I'm trying to find the most efficient way of linking members to one
another in a social networking application.

Currently I link them using 2 separate fields for the members: id1, id2.


So,
  

to find people in your network you would do:


I'm not sure exactly what it is you are doing but I think this may be 
it.  You have a table of people and you want to know who is friends with 
who.  I know 'friend' may not be the best term to use but it is easier 
to type.  So I have my people table.


People{
  *PID,
  Name,
. . .
}

Then the Friend Table,

Friend{
 *PID,
 *FID
}

If you have person, John, with ID 234, and you want to know all his 
friends you can do this...

SELECT  f.FID, p.Name
FROM Friend f JOIN People p ON f.FID = p.PID
WHERE f.PID = 234

of course you have the problem where john has Joe as a friend but Joe 
doesn't have john as a friend.  This seeming inconsistency, may or may 
not be a problem depending on exactly what kind of a relationship you 
are trying to define. 

  




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

RE: Social Network, linking members

[Dan Buettner]

Seems like what you're looking for is a way to query your database 
more efficiently/quickly, and still find all links in either 
direction.


I think the use of a UNION statement should allow this.  Basically 
write your query twice, joining on id1 the first time and id2 the 
second time, with UNION in the middle.


http://dev.mysql.com/doc/refman/5.0/en/union.html

I put together a quick setup like so:

** start of SQL **

create table people (
peopleid int unsigned not null auto_increment primary key,
name varchar(50) );

insert into people (name) values ('Dan');
insert into people (name) values ('Matt');
insert into people (name) values ('Jim');
insert into people (name) values ('Mark');
insert into people (name) values ('Chris');
insert into people (name) values ('Deanna');
insert into people (name) values ('Toni');
insert into people (name) values ('Teri');

create table friends (
id1 int not null,
id2 int not null,
key (id1),
key (id2) );

insert into friends (id1, id2) values (1, 2); /* Dan, Matt */
insert into friends (id1, id2) values (2, 1); /* Matt, Dan */
insert into friends (id1, id2) values (1, 4); /* Dan, Mark */
insert into friends (id1, id2) values (1, 5); /* Dan, Chris */
insert into friends (id1, id2) values (1, 6); /* Dan, Deanna */
insert into friends (id1, id2) values (1, 7); /* Dan, Toni */
insert into friends (id1, id2) values (8, 1); /* Teri, Dan */

/* This should mean that Dan (id 1) has designated friends of Matt, 
Mark, Chris, Deanna, and Toni, while Matt and Teri have both 
designated Dan as a friend.

Jim, if you're listening, sorry, nobody had said you're a friend.  ;)
What we should see from the query below is everyone who has a link in 
either direction with Dan (6 total) */


select p.name from friends f, people p
where p.peopleid = f.id1
and f.id2 = 1
UNION
select p.name from friends f, people p
where p.peopleid = f.id2
and f.id1 = 1;

++
| name   |
++
| Matt   |
| Teri   |
| Mark   |
| Chris  |
| Deanna |
| Toni   |
++
6 rows in set (0.00 sec)

EXPLAIN select p.name from friends f, people p
- where p.peopleid = f.id1
- and f.id2 = 1
- UNION
- select p.name from friends f, people p
- where p.peopleid = f.id2
- and f.id1 = 1
- ;
++--+++---+-+-++--+-+
| id | select_type  | table  | type   | possible_keys | key | 
key_len | ref| rows | Extra   |

++--+++---+-+-++--+-+
|  1 | PRIMARY  | f  | ref| id1,id2   | id2 | 
4   | const  |2 | |
|  1 | PRIMARY  | p  | eq_ref | PRIMARY   | PRIMARY | 
4   | misc.f.id1 |1 | Using where |
|  2 | UNION| f  | ref| id1,id2   | id1 | 
4   | const  |4 | |
|  2 | UNION| p  | eq_ref | PRIMARY   | PRIMARY | 
4   | misc.f.id2 |1 | Using where |
|NULL | UNION RESULT | union1,2 | ALL| NULL  | NULL 
| NULL| NULL   | NULL | |

++--+++---+-+-++--+-+
5 rows in set (0.00 sec)

** END OF SQL **

You can see from the results of the EXPLAIN query that MySQL (5.0.16 
in my case) uses two primary key lookups, so appears to be operating 
pretty efficiently.  I think even with a lot more data this is going 
to remain a very efficient query in MySQL.


MySQL before 4.0 did not support UNION.

Hope this helps!

Dan





of course you have the problem where john has Joe as a friend but Joe
doesn't have john as a friend.  This seeming inconsistency, may or may not
be a problem depending on exactly what kind of a relationship you are trying
to define.

You've just hit the nail on the head! That's exactly the problem.

I think I might just have to grin and bear what I already have :-(

-Original Message-
From: 2wsxdr5 [mailto:[EMAIL PROTECTED]
Sent: 07 April 2006 15:11
To: Martin Gallagher; mysql@lists.mysql.com
Subject: Re: Social Network, linking members

Martin Gallagher wrote:


Hi,

I'm trying to find the most efficient way of linking members to one
another in a social networking application.

Currently I link them using 2 separate fields for the members: id1, id2.

So,

to find people in your network you would do:


I'm not sure exactly what it is you are doing but I think this may be
it.  You have a table of people and you want to know who is friends with
who.  I know 'friend' may not be the best term to use but it is easier
to type.  So I have my people table.

People{
  *PID,
  Name,
. . .
}

Then the Friend Table,

Friend{
 *PID,
 *FID
}

If you have person, John, with ID 234, and you want to know all his
friends you can do this...
SELECT  f.FID, p.Name
FROM Friend f JOIN People p ON f.FID = p.PID
WHERE f.PID = 

Bulk Duplicate Inserts

[Martin Gallagher]

Hi,

In a messaging system I'm working on, I will allow user's to send the same
identical message to numerous other people. Kind of like in email you can
separate recipients with ; or use CC.

It would be nice to be able to perform a query like so:

INSERT INTO messages, social_networking (author, recipient, subject, body,
messages.timestamp) VALUES(1, social_networking.user_id, 'dfdf', 'adfgdf',
1144463208) WHERE social_networking.id=1 AND social_networking.user_id
IN(11,10,24,43,124,2,7)

However it doesn't appear that can be done.

Does anyone know a method to do bulk dupe inserts without using an INSERT,
compacted INSERT for each individual recipient or require the use of a temp
table?

Cheers


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

Insert speed on table with 7M rows but small row size.

[Kevin Burton]

I have a fairly small table WRT the data size.  Its about 300M of  
data.  Right now it has about 6M rows.


The schema is pretty simple.  It has one 64bit ID column.  Basically  
its for checking the existence of an object in our DB and is designed  
to work very fast.


One the table was FIRST created I could do inserts very fast.Now  
that its grown in size inserts are taking a LOT longer.  Sometimes 60  
seconds to 2-3 minutes.


I've migrated to using bulk inserts of 1k rows or more but in  
production this might be a bit difficult.


Is there anyway I can tune MySQL to improve this operation?

Kevin

Kevin A. Burton, Location - San Francisco, CA
  AIM/YIM - sfburtonator,  Web - http://www.feedblog.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04



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

Re: Problem running multi master replication

[Leonardus Setiabudi]

[EMAIL PROTECTED] wrote:



Leonardus Setiabudi [EMAIL PROTECTED] wrote on 04/07/2006 
05:33:50 AM:


 Hi All,

 I have a problem with my multi master replication plan.
 I have set my server (linux) to run 4 instance of mysqld, each with a
 different port, socket, tmpdir, log file, relay log file, master info
 and relay info.

 the illustration as follow :

 Server 1 (non slave)
 
 db1
 db2
 db3
 db4

 Server 2 (slave from other machine)
 
 db2

 Server 3 (slave from other machine)
 
 db3

 Server 4 (slave from other machine)
 
 db4

 each slave run well and replicate successfully
 but the problem is, Server 1 unable to see the update from the other
 slave server, although i can see the update from each originating slave
 server.

 Is this a problem with mysql (locking issue?), or file system issue?
 I am using mysql 4.1.11 on mandrake linux, with ext3 fs and all the
 tables were MYISAM

 Server 1 was supposed to be somekind of data warehouse, to provide data
 from many distributed servers phisically separated.

 Would someone please help me, i have googled this and red the manual,
 but couldnt find similiar situation.

 TIA
 -
 Leo



Just so that we are on the same page, let's review a few definitions 
as they relate to database replication:


master - the server from which changes will propogate during replication.
slave - the server to which replicated changes will be applied during 
replication.


According to the rules of MySQL replication, as I understand them, 
each master can be a host to many slaves but each slave can only 
receive changes from a single master.


You said:
 each slave run well and replicate successfully

I assume you mean servers 2-4?

yes that's correct


 but the problem is, Server 1 unable to see the update from the other
 slave server, although i can see the update from each originating slave

other slave server?  I don't quite understand. If Server 1 is 
supposed to be receiving updates then it is the slave and the process 
it is getting the changes from is called the master. A single server 
process can actually have both roles as in this situation:


Server A - replicates to - Server B - replicates to - Server C

In this case A is the master of B. B is a slave of A.  B is also a 
master to C which is the slave of B. Multi-master replication would be 
something like this:


Server A - Server C
Server B - Server C

In this plan, Server C would be recieving changes from both A and B. 
However the current design of MySQL limits you to having only *ONE* 
master per slave for many excellent reasons (search the recent 
archives of this list for a few).


Can you possibly re-explain your situation using the terms master and 
slave as I just used them?

no, that's not the situation ..
ok, maybe i leave some detail here ...
let me re-explain my situation ..

the mysql server was on /home/mysql/mysql4111
and the data directory  was on /home/mysql/mysql4111/data
in data directory, there are 4 databases, db1, db2, db3, db4
all configurations resided in /home/mysql/replicate.. those are my.cnf, 
replicate1.cnf, replicate2.cnf and replicate3.cnf


servers, run with this command :
/home/mysql/mysql4111/bin/mysqld 
--defaults-file=/home/mysql/replicate/my.cnf
/home/mysql/mysql4111/bin/mysqld 
--defaults-file=/home/mysql/replicate/replicate1.cnf
/home/mysql/mysql4111/bin/mysqld 
--defaults-file=/home/mysql/replicate/replicate2.cnf
/home/mysql/mysql4111/bin/mysqld 
--defaults-file=/home/mysql/replicate/replicate3.cnf


server 1 is just a plain server, not a master nor a slave
other are slaves to each of their own master (hence the different 
configuration files)



so if i draw it it would be something like this

---
| |  db1 |--- server 1 port 3306
|D|  | 
| |  db2 | |- server 2

|A|  |
| |  db3 | |- server 3
|T|  |
| |  db4 | |- server 4
|A|
| |
---

server 2, only replicates db2 from its master, runs on port 3307
server 3, only replicates db3 from its master, runs on port 3308
server 4, only replicates db4 from its master, runs on port 3309

when i log into server 2 using port 3307, i can see the data in db2 
updated, which means the replication succeed (show slave status tells 
the same thing), and the same thing with server 3, i can see db3 
updated, also with server 4 where db4 updated


but when i log into server1, where i can access all the dbs, db2-4 were 
never updated (it should be, its the same file)
in this time, if i run repair table on server1 to all tables in db2-4 .. 
bingo, the data appears ..

with the info, found xxx rows of 0 rows

so, to be clear ... server1 is NOT a SLAVE, its just plain server
server2-4 runs well, and replicate well  ... the problem lies within 
server1, where it supposed to be able to read the FILE (tables) without 
the need to issue repair command (at least in my assumption :) )


i hope this will bring more