Erratic query performance

[Leo Siefert]

I have a moderate sized database set up and a program that allows users to 
create ad-hoc queries into the data based on entries in a form, so that I, as 
the programmer, have control over the actual construction of the queries and 
can do what is needed to optimize queries. I also keep a log of all queries 
that are run so that I can easily see the exact query that may have caused a 
problem.

For the most part, unless a query is quite complex, there is no problem with 
the response time - from a few seconds up to a minute or two for more complex 
queries or one returning very large result sets. Recently a seemingly very 
simple query has resulted in unacceptably long processing times. 

After playing around with the query in PhpMyAdmin I am totally perplexed as to 
what could be causing the problem. Sometimes the query will execute in less 
than 30 seconds, but other times it takes from 4 to 10 or more minutes. It 
never seems to complete in between 30 seconds and 4 minutes. 

To try to isolate the problem today I did a lot of testing on an isolated 
server - nothing on it but MySql and this database and no one but me has access 
to it.  Tried rearranging the joins and eliminating one of the joins as well as 
everything else I could think of to figure out what could be causing the issue. 
Through all of the testing I got consistent results in the four minute range 
for all of the variations I tried - repeated attempts with the same query 
varied by only a second or two.

Then I want back to my program and ran the original query on the public 
database - the same place that the problem had been originally found and 
instead of timing out the gateway (five minute limit) as it had done 
consistently over the past few days it ran it successfully in about 20 seconds. 
I was able to repeat this many times both using the program and by entrering 
the query into PhpMyAdmin. Still takes 4 minutes on the private server, though.

A couple of hours later - shortly before starting this message - I tried again 
on the public server and again the response time was under 30 seconds. Trying 
again now and it's on its way to timing out again. Checked and there are no 
other processes running on the server - volume is usually low as there are less 
than 100 users total.

Any ideas of what could be causing the varied response time on a simple query 
when everything on the server appears to be identical from one run to another? 
Are there settings that can be made on the server to tweak response time for a 
database/query like this?

Here are stats on the files involved in the query and the actual query I am 
trying to run. Note that the number of receipts with amount = 1 is very 
smal compared to the total number of records.

Main: 900,000 records, 500 Mb (886,361 where recordtype = INDIVIDUAL)
  Primary key: id (int)
Receipt: 4,500,000 records, 700 Mb (6,817 where amount = 1)
  Primary key: id (int)
  Indexed on: mainid (int)
Committee: 4,500 records, 600Kb (1,476 where party = D)
  Primary key: id (int)
  Indexed on: Comm_id (varchar(6))

create temporary table tmp type = heap
select distinct 3 filterid, m.id,  GroupLevel, 0 GroupCum
from main m
left join receipt r on m.id = r.mainid
left join campaccommon.committee c on r.comm_id = c.Comm_id
where
   recordtype = INDIVIDUAL
   and c.party = D
   and r.amount = 1

Returns 294 records.

Thanks for any insight you can offer.

 - Leo Siefert

Re: Why innodb can give the same X gap lock to two transactions?

[Leo Huang]

Heikki,

Thanks for you help!

I also read the comment in file of innodbase/lock/lock0lock.c in which
you said Different transaction can have conflicting locks set on the
gap at the same time.. I think that the innodb gap lock's behavior
just like an IX lock's behavior. When a transaction want to insert a
record, it must also get the LOCK_INSERT_INTENTION of the gap, isn't
it?

I have read some source code in innodbase/lock/lock0lock.c. But I
can't get a clear view of innodb lock modes and lock ways?  Can you
give me more information?

PS: hi, Eric, Our MySQL version is 4.1.18. Thx!



--
Best regards,
Leo Huang

2006/12/18, Heikki Tuuri [EMAIL PROTECTED]:

Leo,

'gap' locks in InnoDB are purely 'inhibitive': they block inserts to the
locked gap. But they do not give the holder of the lock any right to
insert. Several transactions can own X-lock on the same gap. The reason
why we let 'conflicting' locks of different transactions on a gap is
that this way there are less lock waits and less deadlocks.

In Eric Bergen's example, there was a row with id 6, and there the locks
were not gap locks.

Best regards,

Heikki
Oracle Corp./Innobase Oy
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
http://www.innodb.com/order.php

.
From: leo huang Date: December 12 2006 7:46am
Subject: Why innodb can give the same X gap lock to two transactions?

Get Plain Text

Hi, all,

We have an innodb table named test. It has some rows as follow:
mysql show create table test;
+---+-+
| Table | Create Table

 |
+---+-+
| test  | CREATE TABLE `test` (
   `id` int(11) NOT NULL default '0',
   `name` char(20) default NULL,
   PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---+-+
1 row in set (1.75 sec)

mysql select * from test;
++-+
| id | name|
++-+
|  1 | huangjy |
|  2 | huangjy |
|  3 | huangjy |
|  4 | huangjy |
|  5 | huangjy |
|  7 | huangjy |
|  8 | huangjy |
|  9 | huangjy |
++-+
8 rows in set (1.98 sec)

When I start two transactions as follow:

Transaction 1:
mysql begin;
Query OK, 0 rows affected (2.51 sec)

mysql select * from test where id=6 for update;
Empty set (2.17 sec)

Transaction 2:
mysql begin;
Query OK, 0 rows affected (1.56 sec)

mysql select * from test where id=6 for update;
Empty set (2.27 sec)

Now, I use show engine innodb status to see the innodb lock status.
The output as follow:


TRANSACTIONS

Trx id counter 0 5168907
Purge done for trx's n:o  0 5168898 undo n:o  0 0
History list length 2
Total number of lock structs in row lock hash table 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 30668, OS thread id 2484620208
MySQL thread id 2, query id 46 localhost root
show engine innodb status
---TRANSACTION 0 5168906, ACTIVE 83 sec, process no 30668, OS thread
id 2484820912
2 lock struct(s), heap size 320
MySQL thread id 1, query id 45 localhost root
TABLE LOCK table `test/test` trx id 0 5168906 lock mode IX
RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table
`test/test` trx id 0 5168906 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE;
info bits 0
  0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc
N  ;; 2: len 7; hex 008013285c; asc  (\;; 3: len 20; hex
6875616e676a7920202020202020202020202020; asc huangjy ;;

---TRANSACTION 0 5168905, ACTIVE 120 sec, process no 30668, OS thread
id 2484419504
2 lock struct(s), heap size 320
MySQL thread id 3, query id 43 localhost root
TABLE LOCK table `test/test` trx id 0 5168905 lock mode IX
RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table
`test/test` trx id 0 5168905 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE;
info bits 0
  0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc
N  ;; 2: len 7; hex 008013285c; asc  (\;; 3: len 20; hex
6875616e676a7920202020202020202020202020; asc huangjy
  ...

As you can see, TRANSACTION 0 5168906 and TRANSACTION 0 5168905 both
get the X gap locks on the same record. The MySQL Manual said that X
lock is an exclusive lock. Why two transactions can get the same X
lock?

Any comment will be welcomed?

Best regards,
Leo Huang

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

Why innodb can give the same X gap lock to two transactions?

[leo huang]

Hi, all,

We have an innodb table named test. It has some rows as follow:
mysql show create table test;
+---+-+
| Table | Create Table

   |
+---+-+
| test  | CREATE TABLE `test` (
 `id` int(11) NOT NULL default '0',
 `name` char(20) default NULL,
 PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---+-+
1 row in set (1.75 sec)

mysql select * from test;
++-+
| id | name|
++-+
|  1 | huangjy |
|  2 | huangjy |
|  3 | huangjy |
|  4 | huangjy |
|  5 | huangjy |
|  7 | huangjy |
|  8 | huangjy |
|  9 | huangjy |
++-+
8 rows in set (1.98 sec)

When I start two transactions as follow:

Transaction 1:
mysql begin;
Query OK, 0 rows affected (2.51 sec)

mysql select * from test where id=6 for update;
Empty set (2.17 sec)

Transaction 2:
mysql begin;
Query OK, 0 rows affected (1.56 sec)

mysql select * from test where id=6 for update;
Empty set (2.27 sec)

Now, I use show engine innodb status to see the innodb lock status.
The output as follow:


TRANSACTIONS

Trx id counter 0 5168907
Purge done for trx's n:o  0 5168898 undo n:o  0 0
History list length 2
Total number of lock structs in row lock hash table 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 30668, OS thread id 2484620208
MySQL thread id 2, query id 46 localhost root
show engine innodb status
---TRANSACTION 0 5168906, ACTIVE 83 sec, process no 30668, OS thread
id 2484820912
2 lock struct(s), heap size 320
MySQL thread id 1, query id 45 localhost root
TABLE LOCK table `test/test` trx id 0 5168906 lock mode IX
RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table
`test/test` trx id 0 5168906 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE;
info bits 0
0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc
N  ;; 2: len 7; hex 008013285c; asc  (\;; 3: len 20; hex
6875616e676a7920202020202020202020202020; asc huangjy ;;

---TRANSACTION 0 5168905, ACTIVE 120 sec, process no 30668, OS thread
id 2484419504
2 lock struct(s), heap size 320
MySQL thread id 3, query id 43 localhost root
TABLE LOCK table `test/test` trx id 0 5168905 lock mode IX
RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table
`test/test` trx id 0 5168905 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE;
info bits 0
0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc
N  ;; 2: len 7; hex 008013285c; asc  (\;; 3: len 20; hex
6875616e676a7920202020202020202020202020; asc huangjy
...

As you can see, TRANSACTION 0 5168906 and TRANSACTION 0 5168905 both
get the X gap locks on the same record. The MySQL Manual said that X
lock is an exclusive lock. Why two transactions can get the same X
lock?

Any comment will be welcomed?

Best regards,
Leo Huang

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

Re: source compilatio or install precompiled packages

[Rocco Di Leo]

Hi Jose,

there are only a few reasons why you should NOT use a pre-compiled package:

1. The version of MySQL you want to use (e.g. Alpha-Version) does not exist
for your machine
2. You want to use features not available in the pre-compiled version.

On the other hand there are some very good reasons to use pre-compiled
packages

1. They are crafted by MySQL AB who exactly know how to build and configure
a stable and reliable package of MySQL
2. MySQL AB uses advanced commercial compilers for assembling the package
which are usually faster than opensource compilers
3. MySQL AB uses advanced libraries compiled into the distributions other
than the ones  your OS might use when compiling
4. In my Opinion it is easier to upgrade a pre-compiled package via your
operating systems upgrade system (e.g. FreeBSD Portmanager)

Greets
Rocco
--
New Identity AG
http://www.newidentity.de

On 10/24/06, Jose Manuel Peso [EMAIL PROTECTED] wrote:


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi list,

Why is recommended the precompiled package install way (as i read in the
downloads page)?


Thanks,
Jose
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFPm13a75uizYnLswRAm+eAJ495WwhxXxlZXimyatDv9zbHSSs0gCgnbcv
wZGKrlT07egY1zK04Zyq8W0=
=72XY
-END PGP SIGNATURE-

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

Re: Trying to run two mysql instances on one server

[Rocco Di Leo]

Hello Low Kian,

first, you cannot attach files to this mailing list, however from the error,
i assume that you have not specified different socks and ports for each
MySQL instance. You need to put that information into your configuration
file for each server , e.g.:

#server 1 option file
port=3306
socket=/tmp/mysql.sock


#server 2 option file
port=3406
socket=/tmp/mysql2.sock


Greets
Rocco

On 10/13/06, Low Kian Seong [EMAIL PROTECTED] wrote:


Dear all,

I am trying to run two mysql instances on one server using the
mysqld_multi command. Attached is my configuration file. The data
directory is at /var/lib/mysql and /var/lib/mysql2. When i try to run
mysqld_multi start 2,3 it won't start up and the error i get is that
something else is already running at mysql.sock socket.

I have already shut down all instances of mysql servers. I am trying
to get this setup working on my opensuse 10.1 box. Can someone please
tell me what is wrong ?

Thank you in advance.




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

Re: Moving DB to another System

[Rocco Di Leo]

Hello Ow Mun,

there are various ways to backup InnoDB tables

1. SELECT ... INTO OUTFILE statement for your tables and reimport them
2. ibbackup (a commercial tool to copy InnoDB Databases while the server is
running
3. Stop the server, copy the innodb tablespace files and logfiles to the new
location

Option 3 is probably what you want to do.

greets
Rocco

On 10/13/06, Ow Mun Heng [EMAIL PROTECTED] wrote:


On Thu, 2006-10-12 at 23:43 -0500, Brian Ivins wrote:
 If the tables are myisam (not innodb), and you're moving them to a
system with the same or newer version of mysql,
 it should work.  You have the best chance of it working if the tables
aren't being accessed, and you do a flush tables
  before you tar up the TEST_DB directory.

The tables are innodb.
I can stop the daemon before I do anything if it's needed.
I know that some other RDBMs has the feature whereby one can just copy
and attach the DB from A - B system


 On Fri, Oct 13, 2006 at 12:11:24PM +0800, Ow Mun Heng wrote:
  Subject: Moving DB to another System
  From: Ow Mun Heng [EMAIL PROTECTED]
  To: mysql@lists.mysql.com
  Date: Fri, 13 Oct 2006 12:11:24 +0800
  X-Mailer: Evolution 2.6.2
 
  Hi All,
 
  Wondering if it's possible for me to just tar up the DB (eg: TEST_DB)
  and then move it AS IS to another system?
 
  Is this possible or will I have to do a mysqldump (inclusive of create
  tables / data etc)??
 
  It would be good if I can just copy (tar) the DB to another
system  and
  then re-attach the DB to the new system.
 
  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]

dose mysqldump --single-transaction lock table?

[Leo]

Hi,all.I want to use mysqldump to backup a innodb table,and add the option 
--single-transaction,dose it lock all the table?thanks.

--
Leo
2006-09-13



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

Re: Can Innodb reuse the deleted rows disk space?

[leo huang]

hi, Chris



I'm sure it will, what makes you think it won't?

Because some paper say that when the row is deleted or update, Innodb
just make a mark that the row is deleted and it didn't delete the
rows. I can't find more information about the re-use tablespace. Can
you give me more?

Regards,
Leo Huang

2006/7/27, Chris [EMAIL PROTECTED]:

leo huang wrote:
 hi, Chris

 Thank you for your advice!

 I know that Innodb use the logfiles circularly. Can Innodb re-use  the
 deleted rows' disk space in tablespace?

I'm sure it will, what makes you think it won't?

You might need an 'optimize table' or something to see a reduction in
the on disk file size but mysql will reclaim that space as it needs to.



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

Re: Can Innodb reuse the deleted rows disk space?

[leo huang]

hi, Chris

So,  the deleted rows' disk space in tablespace can't re-use when I
use Innodb, can it? And the tablespace is growing when we update the
tables, even the amount of rows do not   increase.

Regards,
Leo Huang


2006/7/28, Chris [EMAIL PROTECTED]:

leo huang wrote:
 hi, Chris


 I'm sure it will, what makes you think it won't?
 Because some paper say that when the row is deleted or update, Innodb
 just make a mark that the row is deleted and it didn't delete the
 rows. I can't find more information about the re-use tablespace. Can
 you give me more?

That's the way MVCC works. If you need full acid/transaction support,
that's the only way it can do it (postgresql works exactly the same
way). It can't just delete the row because you might roll back the
transaction and it will have to undo that delete, or other transactions
might be using it for whatever purpose.

http://dev.mysql.com/doc/refman/5.1/en/innodb-multi-versioning.html
http://dev.mysql.com/doc/refman/5.1/en/file-space-management.html



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

Re: Can Innodb reuse the deleted rows disk space?

[leo huang]

hi, Chris

Thank you for your advice!

I know that Innodb use the logfiles circularly. Can Innodb re-use  the
deleted rows' disk space in tablespace?

Regards,
Leo Huang

2006/7/26, Chris [EMAIL PROTECTED]:

leo huang wrote:
 hi, Dilipkumar

 Thank you very much!

 I think I know the fact: The Innodb can't reuse the deleted rows' disk
 space. And a solution is: dump the data; shutdown mysql; delete the
 files; restart mysql; import the data.

InnoDB does re-use the space inside the database, it's the logfiles that
are growing. The logs are needed in case you need to replay transactions.


I suggest you read this page:

http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.html

and this page:

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html

Specify 2-3 entries in the innodb_data_file_path and mysql should (if
I'm reading it properly) rotate between the files and keep size under
control.



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

Re: Can Innodb reuse the deleted rows disk space?

[leo huang]

hi, Dilipkumar

Thank you very much!

I think I know the fact: The Innodb can't reuse the deleted rows' disk
space. And a solution is: dump the data; shutdown mysql; delete the
files; restart mysql; import the data.

Regards,
Leo Huang

2006/7/24, [EMAIL PROTECTED] [EMAIL PROTECTED]:

Hi,

Try using the optimize table tablename ,but this will keep the data 
accordingly,but really if it is  a disk space constraint you can go with re-org 
process in which you will have to get a down time for mysql db.Process is 
something like .
Dump all the Innodb tables drop the existing innodb tables  and shutdown mysql, clear the 
Innodb log-space as ibdata1  indata2  iblogfile0  iblogfile1 and also the 
redo logs of the innodb.
Then start the mysql this will create innodb logs 1  innodb2 as what u have 
mentioned in ur cnf file and import the dump .
In this case u can able to reduce the space usage of innodb.
Try this it might help u out.


With Regards
Dilipkumar


 [EMAIL PROTECTED]:

 Hi, all

   I know the Innodb use MVCC to achieve very high concurrency. Can
 Innodb reuse the deleted rows disk space? I have an database which
 have many update operation. If Innodb can\'t reuse the space of deleted
 rows, I worry about that MySQL will exhaust our disk space very
 quickly.

   Any recommend will be welcome!


 Regards,
 Leo Huang

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

Can Innodb reuse the deleted rows disk space?

[leo huang]

Hi, all

I know the Innodb use MVCC to achieve very high concurrency. Can
Innodb reuse the deleted rows disk space? I have an database which
have many update operation. If Innodb can't reuse the space of deleted
rows, I worry about that MySQL will exhaust our disk space very
quickly.

Any recommend will be welcome!


Regards,
Leo Huang

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

Is the fsync() fake on FreeBSD6.1?

[leo huang]

Hi,

I benchmarked MySQL 4.1.18 on FreeBSD 6.1 and Debian 3.1 using Super Smack
1.3 some days ago.

The benchmark table  is
CREATE TABLE `Account` (
 `aid` int(11) NOT NULL auto_increment,
 `name` char(20) NOT NULL default '',
 `flag` int(11) NOT NULL default '0',
 `uidcount` int(11) NOT NULL default '0',
 `balance` int(11) NOT NULL default '0',
 `point` int(11) NOT NULL default '0',
 `blocktm` int(11) NOT NULL default '0',
 `ipnum` int(10) unsigned default NULL,
 `newdate` datetime default NULL,
 PRIMARY KEY  (`aid`),
 UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And it has 10,000,000 rows.

The SQL statement is
update Account set balance= balance + 1 where aid=?;

The result is followed:
OSClientsResult(queries per second) TPS(got
from iostat)
FreeBSD6.150   516.1
about 2000
Debian3.1   50   49.8
about 200

The result surprise me. The MySQL Performance on FreeBSD6.1 is about 10
times of on Debian3.1，and the output of iostat also shows it.

I know that MySQL uses fsync() to flush both the data and log files at
default when using innodb engine(
http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html). Our
evaluating computer only has a 1RPM SCSI hard disk. I think it can do
about 200 sequential fsync() calls per second if the fsync() is real.

Is the fsync() on FreeBSD6.1 fake? I mean than the data is only written to
the drives memory and so can be lost if power goes down. And how I can
confirm this?

If the fsync() is fake, how can I get the real fsync?

Any comment is welcome!

PS:
1. Our evaluating computer is DELL PowerEdge 1650。Its hardware configuration
is followed:
   CPU: 2 * Intel Pentium III 1.33GHz 512KB Level 2 Cache(smp)
   Memory: 1024MB ECC SDRAM
   HD: SEAGATE ST336706LC（36GB Ultra160 SCSI 1RPM）
   NIC: Intel(R) PRO/1000 Network Connection

2. Some important parameters in MySQL configuration file are here:
   log-bin
   sync_binlog=1
   innodb_safe_binlog
   innodb_buffer_pool_size = 384M
   innodb_additional_mem_pool_size = 20M
   innodb_log_file_size = 100M
   innodb_log_buffer_size = 8M
   innodb_flush_log_at_trx_commit = 1
   innodb_lock_wait_timeout = 50


regards,
Leo Huang

Re: SELECT ALL and flag

[leo huang]

hi, Peter

Try this:

select a.name, sum(if (b.table_a.id is NULL,0,1)) as indid from table_a a
left join table_b b on(a.id=b.table_id.id) group by a.id;

best regards,
Leo Huang

2006/6/1, Peter Lauri [EMAIL PROTECTED]:


Hi,

I have a table table_a and table_b:

table_a {
id
name
}

table_b {
table_a_id
b_value
}

Table A is a table with names, and table B is a table with values for a
specific name (optional, therefore a specific table).

I would like to select all records in A, done by:

SELECT name FROM table_a;

Returns:
Peter
Johan
Fredrik

But then I also would like to have a flag that flags if table_b, I want it
to return this if Peters id is the only one matching in table_b:

Peter   1
Johan   0
Fredrik 0

I tried:

SELECT name, if(table_a.id=table_b.table_a_id, 1, 0) AS indid FROM table_a
JOIN table_b;

But that generates multiple rows of the records in table_a. I tried GROUP
BY
in combination with ORDER BY, but I did not manage to get it to work.

How can I do that if() without having to action do a join, I just want to
check if it exist, and then give value 1 or 0.

Anyone with ideas?

/Peter


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

[Leo]

Hello again :)

On 4/10/06, Shawn Green [EMAIL PROTECTED] wrote:

 First rule: Do NOT share data files between server processes.  Nothing
 should directly interact with a datafile other than the server to which
 it belongs. This includes other server processes as well as direct user
 actions or actions from a third-party program.

 There are structures and procedures that each MySQL process maintains
 that assumes that each process has exclusive control over every data
 file it is managing.  Sharing files between two processes is highly
 discouraged.



something still bothers me very much ..
here i quoted the manual .. (
http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html)

=

With very limited exceptions, each server should use a different data
directory, which is specified using the --datadir=*path* option.

*Warning*: Normally, you should never have two servers that update data in
the same databases. This may lead to unpleasant surprises if your operating
system does not support fault-free system locking. If (despite this warning)
you run multiple servers using the same data directory and they have logging
enabled, you must use the appropriate options to specify log filenames that
are unique to each server. Otherwise, the servers try to log to the same
files. Please note that this kind of setup only works with MyISAM and
MERGEtables, and not with any of the other storage engines.

=

so, mysql it self never mention that it could not be done ..

beside, there shouldn't be any issue with concurency .. one always write,
the other always read .. unless, mysql lock the tables to read it, and never
let it go

anyway .. while i am preparing to try FEDERATED .. can you guys please tell
me why is it impossible to be done?
and here is another quote (
http://dev.mysql.com/books/hpmysql-excerpts/ch07.html) :

=
 To get around that limitation, you can run two copies of MySQL on the slave
machine. Each MySQL instance is responsible for replicating a different
master. In fact, there's no reason you couldn't do this for 5 or 10 distinct
MySQL masters. As long as the slave has sufficient disk space, I/O, and CPU
power to keep up with all the masters, you shouldn't have any problems.

=
 jeremy even told something about writing to the same table from two master
:D


really .. i need someone's sucessful experience in multi mastering :)

thanks
-
Leo

Re: Problem running multi master replication

[Leo]

 Hold on, (it should be, its the same file), you're saying that db2 on
 server1 and server2 are using the same database files (datadir)? If that's
 the case, why? That doesn't make any sense.


 Atle
 -
 Flying Crocodile Inc, Unix Systems Administrator


Im replying from another account here ..
that's the whole point ...
server1 supposed to be read only .. while other server (2-4) the ones that
take the role in writing it
i know it's kind of weird, but hey, if it should work in theory, why not
trying it ..

that's why what i ask in the first place is is it a locking issue? is it
mysql part or filesystem issue)
i already found a work around for this problem with scheduling, but that's
not the prettiest solution that i hoped for :)


or maybe there are other solution?

Re: mysql restart error

[leo huang]

hi Dhandapani,

The 3306 port is not listening. But there are some connection whose state is
FIN_WAIT_2 as you can see in my first letter.

After about 10 minutes I shutdowned mysql, I restarted mysql as root using:
/usr/local/mysql/bin/mysqld_safe .  It worked.

Before it, I did this as mysql and I got the error.

Regards,
Leo Huang

2006/3/28, [S] Dhandapani [EMAIL PROTECTED] :

 Hi Leo,

 check for cnf file for which port you have configured the port .If it is
 in 3306 port then do netstat -an|grep LIST ,check for 3306 port is listening
 on your system .If yes you mysql process has not shutdown properly.

 shutdown the mysql process completely and start the mysql process by
 specifying your datadirectory.

  /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
 --datadir=/usr/local/mysql/data/  --user=mysql 

 port= 3306
 socket  = /tmp/mysql.sock

 Regards,
 Dhandapani


 leo huang wrote:

 hi, Lakshmi

  The mysql process had ended. I get it from both mysql err log and ps
 output.

 regards,
 Leo Huang

 2006/3/28, Lakshmi M P
 [EMAIL PROTECTED] [EMAIL PROTECTED]:

  Run   ps -ef | grep mysql and see any mysql process is running and if so
 kill the same and try to start mysql.It may help.
 leo huang wrote:

  hi,

 I met the MySQL restart error today.

 First, I stopped the running mysql server using
 /usr/local/mysql/bin/mysqladmin -uroot shutdown.

 After the server shutdowned, I restarted it using

 /usr/local/mysql/bin/mysqld_safe .

 Then, I got this error: [ERROR] Can't start server: Bind on TCP/IP port:
 Address already in use.

 There was no other process that was using the port 3306 which mysql

server

  use. But there were some mysql connect did not release because the

  shutdown.

  The error log is followed:
 060328  8:20:45 [Note] /usr/local/mysql/libexec/mysqld: Normal shutdown

 060328  8:20:47  InnoDB: Starting shutdown...
 060328  8:20:49  InnoDB: Shutdown completed; log sequence number 120

 2134241340
 060328  8:20:49 [Note] /usr/local/mysql/libexec/mysqld: Shutdown

  complete

  060328 08:20:49  mysqld ended

 060328 08:21:15  mysqld started
 060328  8:21:15 [ERROR] Can't start server: Bind on TCP/IP port: Address
 already in use
 060328  8:21:15 [ERROR] Do you already have another mysqld server

running on

  port: 3306 ?
 060328  8:21:15 [ERROR] Aborting

 060328  8:21:15 [Note] /usr/local/mysql/libexec/mysqld: Shutdown

  complete

  060328 08:21:15  mysqld ended

 The netstat outputs are followed:
 $ netstat -al
 Active Internet connections (including servers)
 Proto Recv-Q Send-Q  Local Address  Foreign

  Address(state)

  tcp4   0  0  bj.3306  s4.9405   FIN_WAIT_2
 tcp4   0  0  bj.3306  s4.5168   FIN_WAIT_2
 tcp4   0  0  bj.3306  s4.25007  FIN_WAIT_2

 tcp4   0  0  bj.3306  s4.9940   FIN_WAIT_2
 tcp4   0  0  bj.3306  s4.3916   FIN_WAIT_2
 tcp4   0  0  bj.3306  s4.15229  FIN_WAIT_2
 tcp4   0  0
 bj.3306  s4.6479   FIN_WAIT_2
 tcp4   0  0  bj.3306  s4.7873   FIN_WAIT_2

 Our mysql version is 4.1.18. It is running on FreeBSD 4.9-RELEASE.

 Any comment will be great thankful!


 Regards,
 Leo Huang


--
 regards,
 Lakshmi.M.P.
 DBA-Support
 Sify Limited.
 Ext:4134

 ** DISCLAIMER **
 Information contained and transmitted by this E-MAIL is proprietary to
 Sify Limited and is intended for use only by the individual or entity to

 which it is addressed, and may contain information that is privileged,
 confidential or exempt from disclosure under applicable law. If this is a
 forwarded message, the content of this E-MAIL may not have been sent with

 the authority of the Company. If you are not the intended recipient, an
 agent of the intended recipient or a  person responsible for delivering
 the
 information to the named recipient,  you are notified that any use,

 distribution, transmission, printing, copying or dissemination of this
 information in any way or in any manner is strictly prohibited. If you
 have
 received this communication in error, please delete this mail  notify us

 immediately at [EMAIL PROTECTED]
 www.sify.com - your homepage on the internet for news, sports, finance,
 astrology, movies, entertainment, food, languages etc

mysql restart error

[leo huang]

hi,

I met the MySQL restart error today.

First, I stopped the running mysql server using
/usr/local/mysql/bin/mysqladmin -uroot shutdown.

After the server shutdowned, I restarted it using
/usr/local/mysql/bin/mysqld_safe .

Then, I got this error: [ERROR] Can't start server: Bind on TCP/IP port:
Address already in use.

There was no other process that was using the port 3306 which mysql server
use. But there were some mysql connect did not release because the shutdown.


The error log is followed:
060328  8:20:45 [Note] /usr/local/mysql/libexec/mysqld: Normal shutdown

060328  8:20:47  InnoDB: Starting shutdown...
060328  8:20:49  InnoDB: Shutdown completed; log sequence number 120
2134241340
060328  8:20:49 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete

060328 08:20:49  mysqld ended

060328 08:21:15  mysqld started
060328  8:21:15 [ERROR] Can't start server: Bind on TCP/IP port: Address
already in use
060328  8:21:15 [ERROR] Do you already have another mysqld server running on
port: 3306 ?
060328  8:21:15 [ERROR] Aborting

060328  8:21:15 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete

060328 08:21:15  mysqld ended

The netstat outputs are followed:
$ netstat -al
Active Internet connections (including servers)
Proto Recv-Q Send-Q  Local Address  Foreign Address(state)
tcp4   0  0  bj.3306  s4.9405   FIN_WAIT_2
tcp4   0  0  bj.3306  s4.5168   FIN_WAIT_2
tcp4   0  0  bj.3306  s4.25007  FIN_WAIT_2
tcp4   0  0  bj.3306  s4.9940   FIN_WAIT_2
tcp4   0  0  bj.3306  s4.3916   FIN_WAIT_2
tcp4   0  0  bj.3306  s4.15229  FIN_WAIT_2
tcp4   0  0  bj.3306  s4.6479   FIN_WAIT_2
tcp4   0  0  bj.3306  s4.7873   FIN_WAIT_2

Our mysql version is 4.1.18. It is running on FreeBSD 4.9-RELEASE.

Any comment will be great thankful!

Regards,
Leo Huang

Re: mysql restart error

[leo huang]

hi, Lakshmi

 The mysql process had ended. I get it from both mysql err log and ps
output.

regards,
Leo Huang

2006/3/28, Lakshmi M P [EMAIL PROTECTED]:

 Run   ps -ef | grep mysql and see any mysql process is running and if so
 kill the same and try to start mysql.It may help.
 leo huang wrote:
  hi,
 
  I met the MySQL restart error today.
 
  First, I stopped the running mysql server using
  /usr/local/mysql/bin/mysqladmin -uroot shutdown.
 
  After the server shutdowned, I restarted it using
  /usr/local/mysql/bin/mysqld_safe .
 
  Then, I got this error: [ERROR] Can't start server: Bind on TCP/IP port:
  Address already in use.
 
  There was no other process that was using the port 3306 which mysql
 server
  use. But there were some mysql connect did not release because the
 shutdown.
 
 
  The error log is followed:
  060328  8:20:45 [Note] /usr/local/mysql/libexec/mysqld: Normal shutdown
 
  060328  8:20:47  InnoDB: Starting shutdown...
  060328  8:20:49  InnoDB: Shutdown completed; log sequence number 120
  2134241340
  060328  8:20:49 [Note] /usr/local/mysql/libexec/mysqld: Shutdown
 complete
 
  060328 08:20:49  mysqld ended
 
  060328 08:21:15  mysqld started
  060328  8:21:15 [ERROR] Can't start server: Bind on TCP/IP port: Address
  already in use
  060328  8:21:15 [ERROR] Do you already have another mysqld server
 running on
  port: 3306 ?
  060328  8:21:15 [ERROR] Aborting
 
  060328  8:21:15 [Note] /usr/local/mysql/libexec/mysqld: Shutdown
 complete
 
  060328 08:21:15  mysqld ended
 
  The netstat outputs are followed:
  $ netstat -al
  Active Internet connections (including servers)
  Proto Recv-Q Send-Q  Local Address  Foreign
 Address(state)
  tcp4   0  0  bj.3306  s4.9405   FIN_WAIT_2
  tcp4   0  0  bj.3306  s4.5168   FIN_WAIT_2
  tcp4   0  0  bj.3306  s4.25007  FIN_WAIT_2
  tcp4   0  0  bj.3306  s4.9940   FIN_WAIT_2
  tcp4   0  0  bj.3306  s4.3916   FIN_WAIT_2
  tcp4   0  0  bj.3306  s4.15229  FIN_WAIT_2
  tcp4   0  0  bj.3306  s4.6479   FIN_WAIT_2
  tcp4   0  0  bj.3306  s4.7873   FIN_WAIT_2
 
  Our mysql version is 4.1.18. It is running on FreeBSD 4.9-RELEASE.
 
  Any comment will be great thankful!
 
  Regards,
  Leo Huang
 
 


 --
 regards,
 Lakshmi.M.P.
 DBA-Support
 Sify Limited.
 Ext:4134

 ** DISCLAIMER **
 Information contained and transmitted by this E-MAIL is proprietary to
 Sify Limited and is intended for use only by the individual or entity to
 which it is addressed, and may contain information that is privileged,
 confidential or exempt from disclosure under applicable law. If this is a
 forwarded message, the content of this E-MAIL may not have been sent with
 the authority of the Company. If you are not the intended recipient, an
 agent of the intended recipient or a  person responsible for delivering
 the
 information to the named recipient,  you are notified that any use,
 distribution, transmission, printing, copying or dissemination of this
 information in any way or in any manner is strictly prohibited. If you
 have
 received this communication in error, please delete this mail  notify us
 immediately at [EMAIL PROTECTED]

 www.sify.com - your homepage on the internet for news, sports, finance,
 astrology, movies, entertainment, food, languages etc

Re: mySQL 5 and CPu at 99.99%

[leo huang]

hi, Taiyo
| innodb_buffer_pool_size | 8388608
| key_buffer_size | 8388600

Try to increase the innodb_buffer_pool_size if you use the innodb storage or
key_buffer_size if MyISAM storage is used or both.

You can get more information about innodb_buffer_pool_size and
key_buffer_size from this:
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Best regards,

Leo Huang

2006/2/28, Taiyo [EMAIL PROTECTED]:
 Greetings,

 We are running a server and the CPU is at %99.99 at all times, after about
 2-3 hours of processing queries just hang, sounds like our hardware is
weak
 but we are running a 4GB RAM Dual Xeons 3.4 at 64bit OS.

 I was hoping someone could look at our settings and would help us analyze
 this issue:

 Please advise.

 Here are the stats:

 Some version information:

 mySQL version: 5.0.16-standard
 Uname: Linux db.example.com
 2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:00:54 EDT 2005 x86_64 x86_64 x86_64
 GNU/Linux
 RAM:4GB
 SWAP  1GB
 HD:   2 SCSI 10k RPM on 2 separate
 controllers.

 Some information about the load:
 Queries per second avg: 16.346 (about)
 Our biggest table is 3.5 million records and we index 3 of the columns for
 fulltext search
 We do a lot of join queries on 2 tables.

 Some mySQL variables:
 [mysqld]
 tmpdir=/db.example.com/tmp
 query_cache_size=1048576
 query_cache_limit = 33554432
 query_cache_size = 33554432
 myisam_sort_buffer_size = 33554432
 sort_buffer_size = 33554432
 max_connections=500
 table_cache = 1000
 max_tmp_tables = 256

 Here is all of my mysql -e 'SHOW VARIABLES':

+-+-
 -+
 | Variable_name   | Value
 |

+-+-
 -+
 | auto_increment_increment| 1
 |
 | auto_increment_offset   | 1
 |
 | automatic_sp_privileges | ON
 |
 | back_log| 50
 |
 | basedir | /
 |
 | binlog_cache_size   | 32768
 |
 | bulk_insert_buffer_size | 8388608
 |
 | character_set_client| latin1
 |
 | character_set_connection| latin1
 |
 | character_set_database  | latin1
 |
 | character_set_results   | latin1
 |
 | character_set_server| latin1
 |
 | character_set_system| utf8
 |
 | character_sets_dir  | /usr/share/mysql/charsets/
 |
 | collation_connection| latin1_swedish_ci
 |
 | collation_database  | latin1_swedish_ci
 |
 | collation_server| latin1_swedish_ci
 |
 | completion_type | 0
 |
 | concurrent_insert   | 1
 |
 | connect_timeout | 5
 |
 | datadir | /var/lib/mysql/
 |
 | date_format | %Y-%m-%d
 |
 | datetime_format | %Y-%m-%d %H:%i:%s
 |
 | default_week_format | 0
 |
 | delay_key_write | ON
 |
 | delayed_insert_limit| 100
 |
 | delayed_insert_timeout  | 300
 |
 | delayed_queue_size  | 1000
 |
 | div_precision_increment | 4
 |
 | engine_condition_pushdown   | OFF
 |
 | expire_logs_days| 0
 |
 | flush   | OFF
 |
 | flush_time  | 0
 |
 | ft_boolean_syntax   | + -()~*:|
 |
 | ft_max_word_len | 84
 |
 | ft_min_word_len | 2
 |
 | ft_query_expansion_limit| 20
 |
 | ft_stopword_file| (built-in)
 |
 | group_concat_max_len| 1024
 |
 | have_archive| YES
 |
 | have_bdb| NO
 |
 | have_blackhole_engine   | NO
 |
 | have_compress   | YES
 |
 | have_crypt  | YES
 |
 | have_csv| NO
 |
 | have_example_engine | NO
 |
 | have_federated_engine   | NO
 |
 | have_geometry   | YES
 |
 | have_innodb | YES
 |
 | have_isam   | NO
 |
 | have_ndbcluster | NO
 |
 | have_openssl| NO
 |
 | have_query_cache| YES
 |
 | have_raid   | NO
 |
 | have_rtree_keys | YES
 |
 | have_symlink| YES
 |
 | init_connect|
 |
 | init_file   |
 |
 | init_slave  |
 |
 | innodb_additional_mem_pool_size | 1048576
 |
 | innodb_autoextend_increment | 8
 |
 | innodb_buffer_pool_awe_mem_mb   | 0
 |
 | innodb_buffer_pool_size | 8388608
 |
 | innodb_checksums| ON
 |
 | innodb_commit_concurrency   | 0

How to keep account independent in replication

[leo huang]

Hi,

How can I keep the account of MySQL independent in replication?

We have two MySQL 4.1.18 nodes: A and B. B replicate A. We want that the
account in A is independent. That is to said, it would not affect the
account in B when we add or delete the account in A.

We add the following option in B's my.cnf and use the INSERT or DELETE
statement in A to deal with the account management now.
  replicate-ignore-db=mysql

As you can see, it is ugly and discommodious.

Is there any better solution?


Best regards,

Leo Huang

Re: Same question, better example

[leo huang]

Ariel,

You can try this:

mysqlselect stri from prueba order by stri+0 desc;

Leo Huang



2006/2/17, Ariel Sánchez Mora [EMAIL PROTECTED]:

 mysql describe prueba;
 +---+-+--+-+-+---+
 | Field | Type| Null | Key | Default | Extra |
 +---+-+--+-+-+---+
 | inte  | int(2)  | YES  | | NULL|   |
 | stri  | char(2) | YES  | | NULL|   |
 +---+-+--+-+-+---+
 2 rows in set (0.00 sec)

 mysql select * from prueba;
 +--+--+
 | inte | stri |
 +--+--+
 |1 | 1|
 |2 | 2|
 |3 | 3|
 |4 | 4|
 |5 | 5|
 |6 | 6|
 |7 | 7|
 |8 | 8|
 |9 | 9|
 |   10 | 10   |
 +--+--+
 10 rows in set (0.00 sec)

 --Is there a way I can make this:

 mysql select stri from prueba order by stri desc;
 +--+
 | stri |
 +--+
 | 9|
 | 8|
 | 7|
 | 6|
 | 5|
 | 4|
 | 3|
 | 2|
 | 10   |
 | 1|
 +--+
 10 rows in set (0.00 sec)

 --come out like this:

 mysql select inte from prueba order by inte desc;
 +--+
 | inte |
 +--+
 |   10 |
 |9 |
 |8 |
 |7 |
 |6 |
 |5 |
 |4 |
 |3 |
 |2 |
 |1 |
 +--+
 10 rows in set (0.00 sec)

 I'm using MySQL 4.1.14 in windows 2000.

 Thanks!

 Ariel

Re: selecting min, max

[leo huang]

hi,

I think the following link would be some help for you!
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html

Leo Huang

2006/2/14, Octavian Rasnita [EMAIL PROTECTED]:

 Hi,

 From: Rhino [EMAIL PROTECTED]
 ...
   I need to extract a list which the following values from this table,
 for
   each hour (in date_time field):
  
   - symbol
   - min(price)
   - max(price)
   - price where date_time is the earliest for that certain hour.
   - price where the date_time is the last from that hour.
   - The sum of volume from that hour.
  
   I have tried to get the list of symbols, then get each hourly period
 and
   calculate those 6 values for each period, but there are many symbols
 and
   very many periods, and it takes very very much time.
  
   Is there a more intelligent way of getting those values in another way
   than
   symbol by symbol and period by period?
  
  It's hard to answer your question since you haven't given us any
 examples
 of
  the SQL you've already tried. You haven't told us which version of MySQL
 you
  are using, either. That makes a big difference since newer versions
 offer
  many more SQL capabilities like views and subqueries that could really
 help
  you.
 
  You certainly shouldn't have to write separate queries for each
 different
  symbol that you are using!
 

 Here is the table definition. The table is simple, but what I want is
 complicated:

 CREATE TABLE `tickers` (
 `symbol` varchar(20) NOT NULL,
 `last_volume` bigint(20) unsigned default NULL,
 `last_price` decimal(20,4) unsigned default NULL,
 `last_update` datetime default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 I want to get a list of values for more periods of time, 5 minutes, 15
 minutes, and hourly.

 I need to get:

 symbol
 date_format(last_update, '%Y-%m-%d') as date
 date_format(last_update, '%H:%i:%s') as time
 min(last_price) as low  (The min value of last_price for that period)
 max(last_price) as high  (the max price from that period)
 last_price as open  (where last_update=min(last_update) from that period)
 last_price as close  (where last_update=max(last_update) from that period)

 The result data should look something like:

 Symbol,data,time,low,high,open,close
 simb1,2006-02-08,10:15:00,1000,1200,1050,1150
 simb1,2006-02-08,10:30:00,1100,1150,1150,1150
 simb1,2006-02-08,10:45:00,1000,1200,1050,1150
 simb1,2006-02-08,11:00:00,1050,1200,1050,1150
 simb1,2006-02-08,11:15:00,1000,1200,1050,1150

 ... then here follow the rest of records for simb1 and for other symbols.

 You may see that the first time is 10:15:00, the next time is 10:30:00,
 the next is 10:45, so the period of time is 15 minutes.

 The first low is the lowest price between 10:15:00 and 10:30:00 and the
 high is the highest price in that period.
 The first open value is the last_price of the first trade from that
 period and the close price is the last_price of the latest trade from
 that
 period.

 I don't know if MySQL can create a query that can get those values fast
 enough.

 Thank you very much.


 Teddy


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

Problem with datetime value

[Leo]

Hi All,

I got a problem with a query that involved datetime field.
the table structure goes something like this :

CREATE TABLE `price_log` (  
 `Item` char(20) NOT NULL default '',  
 `Started` datetime NOT NULL default '-00-00 00:00:00', 
 `Price` decimal(16,3) NOT NULL default '0.000', 
 PRIMARY KEY  (`Item`,`Started`) 
) ENGINE=MyISAM;   


insert into price_log values
(A1,2005-11-01 08:00:00,1000),
(A1,2005-11-15 00:00:00,2000),
(A1,2005-12-12 08:00:00,3000),
(A2,2005-12-01 08:00:00,1000);

when i execute this query :

select *
from price_log
where
item like A%
and started=2005-12-01 24:00:00;

it will return this result set :
ItemStarted   Price 
--  ---  

A1  2005-11-01 08:00:00  1000.000
A1  2005-11-15 00:00:00  2000.000
A2  2005-12-01 08:00:00  1000.000


but, when i limited to certain item, like this :

select *
from price_log
where
item=A1
and started=2005-12-01 24:00:00;

it return an empty set.

What is going wrong?
I know it should return 2 rows, as the previous sql result set got two 
rows of item 'A1'.


is it because the value of 2005-12-01 24:00:00 which ofcourse is not a 
valid date time value.

but anyhow, the first query was succeded ??

im using mysql 4.1.11, 4.1.15, 4.0.1 .. and the result were all the same.

Thanks for any comment.


-
Leo

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

Re: how use sql_slave_skip_counter to restore slave replication

[Leo]

other than what Gleb says,
you can avoid the error using 'insert ignore into' or better yet 
'replace into'

for every insert statement

AESYS S.p.A. [Enzo Arlati] wrote:


I'm trying to use teh parameter sql_slave_skip_counter at run-time to
restore slave replication.
When a slave replication broke due some errors in code, my be a duplicate
key, the only working way to restore the replica where to delete the
existing record which conflicts whith the ones inserted by the replication
process.
So if I have a duplicate key 30020 ,I have to remove the record with the id
30020 and the replication can reinsert it's copy of record with id = 30020.
This should be difficult to automate so I try another way using the global
variable sql_slave_skip_counter.

I try to skip 5 records using a statemente like this:
set global sql_slave_skip_counter = 5;
and then restart the slavre
start slave;

but the problem is the same and the command 'show slave status' report a
skip_counter filed equals 0.

Where I am wrong , some can help me ?
Regards, Enzo

 



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

Re: Assigment sign not work on Delphi

[Leo]

cmiiw,
dont you think you are supposed to pass the query as string in delphi???

somewhat like this :

querystr:='SELECT @TOTAL:=PRICE * QTY FROM INV_PRODUCT';
mysql_query(@mysqlconnection,PChar(querystr));
... etc ...

so delphi will ignore the second ':=' because it's in a string, not an 
assignment operator
and dont forget to put '@' before TOTAL, or else you will get error from 
mysql

because it will think TOTAL as a global server variable

The Nice Spider wrote:


Using Delphi to with this query:  SELECT TOTAL :=
PRICE * QTY FROM 
INV_PRODUCT


will caused error Parameter object is improperly
defined. Inconsistent or 
incomplete information was provided. because Delphi
look it as Parameter (a 
parameter of query in Delphi using : at the

beginning).

Is it better for MySQL using = rather than := ? Or
is there any setting 
to set MySQL to accept the = sign?





__ 
Yahoo! FareChase: Search multiple travel sites in one click.

http://farechase.yahoo.com

 



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

Re: How to reload my.cnf?

[Leo]

Are you trying to restart the master server, or 'reseting' the master?
my master server always shutdown daily, and the slave happily catch up
the next day, it's been goin for some 6 months with heavy traffic

it's a different story if you want to reset the master, you should
capture the master and send it again to the slave (i've done this
several times too, and it's painfully slow)

changing the binlog-do-db afaik would not trouble the replication,
unless it's a new database that hasnt been replicated earlier

-
leo

Tedy Aulia wrote:

Hi Gleb,

I understand that the replication shouldn't break, but restarting master
means losing the updates made to the master database, which we can't
afford to do that.


TA

  



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

Max Connections of MySQL on Linux

[huang leo]

Hi, everyone:
   I had done a test on Linux2.6. I got the max connections of 1079 when I 
complied the MySQL with static link. But I got the max connections of 7159 
when I complied the MySQL with dynamic link. Why has so much difference 
between the static link and dynamic link? Has anybody know it?


　　

Best regards,
leo huang
2005-06-27

_
享用世界上最大的电子邮件系统― MSN Hotmail。  http://www.hotmail.com  



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

Does the kernel options MAXSSIZ affect the MySQL max connections on FreeBSD

[huang leo]

hi,everyone,
 I just did a test to find out the MySQL4.1.12 max connections on FreeBSD 
5.4 Release and FreeBSD 4.11 Release. 
 The first test, the MAXSSIZ is default(64MB), and MAXSDIZ is 1GB because 
I have 1GB memory. The second test, I setted the MAXSSIZ=1GB, and the 
MAXDSIZ is also 1GB. The two test show that the MySQL max connections is 
the same. The max connections is about 1000.
 Have anyone kown how to tune the FreeBSD and MySQL to increase the max 
connections?


Best regards,
leo huang
[EMAIL PROTECTED]
2005-06-17

_
 MSN Messenger:  http://messenger.msn.com/cn  



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

Problem with system tables

[Leo]

Hi, I'm working on a Linux Red Hat 7 (core 2.4.2-2), with Apache 2.0.53, PHP 
5.0.3 and I'm trying to upgrade MySQL to version 4.1.10 with the rpms (I 
downloaded all of them). When i try to install, it reports a couple of errors 
here is screen shot
 
sudo rpm --install -vv --force MySQL*.rpm
 
[...]

+ mysql_install_db --rpm --user=mysql
050226 12:28:01 [Warning] Asked for 196608 thread stack, but got 126976
/usr/sbin/mysqld: File '/var/mysql-bin.03' not found (Errcode: 13)
050226 12:28:01 [ERROR] Could not use /var/mysql-bin for logging (error 13). 
Turning logging off for the whole duration of the MySQL server process. To turn 
it on again: fix the cause, shutdown the MySQL server and restart it.
050226 12:28:01 [ERROR] Aborting
050226 12:28:01 [Note] /usr/sbin/mysqld: Shutdown complete
Installation of system tables failed!
Examine the logs in /var/lib/mysql for more information.
You can also try to start the mysqld daemon with:
/usr/sbin/mysqld --skip-grant 
You can use the command line tool
/usr/bin/mysql to connect to the mysql
database and look at the grant tables:
shell /usr/bin/mysql -u root mysql
mysql show tables
Try 'mysqld --help' if you have problems with paths. Using --log
gives you a log in /var/lib/mysql that may be helpful.
The latest information about MySQL is available on the web at
http://www.mysql.com
Please consult the MySQL manual section: 'Problems running mysql_install_db',
and the manual section that describes problems on your OS.
Another information source is the MySQL email archive.
Please check all of the above before mailing us!
And if you do mail us, you MUST use the /usr/bin/mysqlbug script!
+ chown -R mysql /var/lib/mysql
+ chmod -R og-rw /var/lib/mysql/mysql
+ /etc/init.d/mysql start


-
Do you Yahoo!?
 Yahoo! Mail - Easier than ever with enhanced search. Learn more.

Re: summing from multiple tables

[Leo]

- Original Message - 
From: Kevin Cagle [EMAIL PROTECTED]
To: MySql List [EMAIL PROTECTED]
Sent: Saturday, November 20, 2004 10:46 AM
Subject: summing from multiple tables


mysql select sum(amount), sum(totaltimesviewed) from bill, pageviewed;
+-+---+
| sum(amount) | sum(totaltimesviewed) |
+-+---+
|  154.00 |84 |
+-+---+
1 row in set (0.00 sec)
Why don't I get 30.80 and 12? How can I change the query so I do get 
that result? What in the world did I actually do to get 154.00 and 84? 
I can't see how anything adds up to those numbers...

that's because you didnt specify the join between two tables..
and so mysql will return a total of (number of records in bill) x (number of 
records in pageviewed)
and that's where the sum() value you got come from 

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

Re: what is wrong woth this statement?

[Leo]

i didnt fully catch you...
is this the kind of query statement you want?

INSERT INTO some_other_table
SELECT
some_field_list
FROM z_mail_systems
HAVING COUNT(any_field)0


On Tue, 19 Oct 2004 12:45:30 -0400, Josh Howe [EMAIL PROTECTED] wrote:
 if (select count(*) from z_mail_systems  0) then [insert statement]
 endif;
 
 How do I do this kind of conditional insert? Thanks.
 


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

OOT: Penawaran Untuk Subscriber Milis MySQL di Indonesia

[Leo]

Dear All,

Siapa mau account gmail?
Gratis 2 account.. Tanpa bayaran, tanpa uang.. 
Silahkan pm saya..

thanks


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

RE: Object pascal translation of mysql header

[Leo]

Im sorry if this classified as OOT

One example of the confussion

C Header File declaration of statement handler (taken from the 4.1.3)

typedef struct st_mysql_stmt
{
  MEM_ROOT   mem_root; /* root allocations */
  LIST   list; /* list to keep track of all stmts */
  MYSQL  *mysql;   /* connection handle */
  MYSQL_BIND *params;  /* input parameters */
  MYSQL_BIND *bind;/* output parameters */
  MYSQL_FIELD*fields;  /* result set metadata */
  MYSQL_DATA result;   /* cached result set */
  MYSQL_ROWS *data_cursor; /* current row in cached result */
   /* copy of
mysql-affected_rows after statement execution */
  my_ulonglong   affected_rows;
  my_ulonglong   insert_id;/* copy of mysql-insert_id */
  /*
mysql_stmt_fetch() calls this function to fetch one row (it's different
for buffered, unbuffered and cursor fetch).
  */
  int(*read_row_func)(struct st_mysql_stmt *stmt, 
  unsigned char **row);
  unsigned long  stmt_id;  /* Id for prepared statement */
  unsigned int   last_errno;   /* error code */
  unsigned int   param_count;  /* inpute parameters count */
  unsigned int   field_count;  /* number of columns in result set */
  enum enum_mysql_stmt_state state;/* statement state */
  char   last_error[MYSQL_ERRMSG_SIZE]; /* error message */
  char   sqlstate[SQLSTATE_LENGTH+1];
  /* Types of input parameters should be sent to server */
  my_boolsend_types_to_server;
  my_boolbind_param_done;  /* input buffers were supplied */
  my_boolbind_result_done; /* output buffers were supplied */
  /* mysql_stmt_close() had to cancel this result */
  my_bool   unbuffered_fetch_cancelled;  
  /*
Is set to true if we need to calculate field-max_length for 
metadata fields when doing mysql_stmt_store_result.
  */
  my_bool   update_max_length; 
} MYSQL_STMT; 

And the pascal version (taken from Jorge del Conde's)

PMYSQL_STMT = ^TMYSQL_STMT;
  TMYSQL_STMT = record
mysql: PMYSQL;  // connection handle
params: PMYSQL_BIND;// input parameters
result: PMYSQL_RES; // resultset
bind: PMYSQL_BIND;  // row binding
fields: PMYSQL_FIELD;   // prepare meta info
list: _TLIST;// list to keep track
of all stmts
query: PChar;   // query buffer
mem_root: TMEM_ROOT;// root allocations
param_count: Cardinal;  // parameters count
field_count: Cardinal;  // fields count
stmt_id: Cardinal;  // Id for prepared
statement
last_errno: Cardinal;   // error code
state: PREP_STMT_STATE; // statement state
last_error: array [0..MYSQL_ERRMSG_SIZE - 1] of char; // error message
long_alloced: my_bool;  // flag to indicate long
alloced
send_types_to_server: my_bool;  // Types sent to server
param_buffers: my_bool; // param bound buffers
res_buffers: my_bool;   // output bound buffers
result_buffered: my_bool;   // Results buffered
  end;

I see that the two declarations have different number of total
field/member..
And also some of the struct/record member has different order of placement..
Is it ok for me to add the other missing member??


 -Original Message-
 From: Leo [mailto:[EMAIL PROTECTED] 
 Sent: Saturday, July 24, 2004 9:42 AM
 To: [EMAIL PROTECTED]
 Subject: Object pascal translation of mysql header
 
 Dear All,
 
 Not long ago Jorge del Conde gave me his amazing translation 
 of mysql C header file to object pascal unit Including the 
 remade dll, It was able to solve my problem of migrating the 
 apps from using mysql 3.x to 4.x But now I am facing a new 
 difficulty of trying to implement mysql 4.1.3 prepared 
 statement, I already tried to edit the mysql.pas on my own, 
 adding the declaration of anything that has the word STMT on 
 the header file.. But instead it crashed my app I also do 
 some googling to find a lot of obsolote result, the mysql.pas 
 for mysql 3.X
 
 Would some one please help me.. I never did any translation 
 from C style to pascal before Or may be Jorge can help me 
 providing the newest mysql.pas
 
 Thanks in advance
  
 Regards
 --
 Leonardus Setiabudi
 IT Department
 PT Bina San Prima
 Jl Tamansari 10-12
 022-4207725 #316
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql

Object pascal translation of mysql header

[Leo]

Dear All,

Not long ago Jorge del Conde gave me his amazing translation of mysql C
header file to object pascal unit
Including the remade dll,
It was able to solve my problem of migrating the apps from using mysql 3.x
to 4.x
But now I am facing a new difficulty of trying to implement mysql 4.1.3
prepared statement,
I already tried to edit the mysql.pas on my own, adding the declaration of
anything that has the word STMT on the header file.. But instead it crashed
my app
I also do some googling to find a lot of obsolote result, the mysql.pas for
mysql 3.X

Would some one please help me.. I never did any translation from C style to
pascal before
Or may be Jorge can help me providing the newest mysql.pas

Thanks in advance
 
Regards
--
Leonardus Setiabudi
IT Department
PT Bina San Prima
Jl Tamansari 10-12
022-4207725 #316


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

Re: Nested query with GROUP BY ... HAVING COUNT(*) ... hangs 4.1.3 beta

[Leo Siefert]

Thanks for your response.

 [EMAIL PROTECTED] 7/19/2004 11:47:39 AM 
It looks like your IN statement is forcing your inner SELECT to
execute 
once PER ROW of your main table. It is asking the engine to make sure
that 
_each and every_  id value in main meets the condition in the inner 
select. So, for each and every value in the table main, it has to 
re-computing the inner query and scan the results for matches.

Not sure why this would happen.  The nested query is not correlated to
the outer query, so I would expect it to be executed only once.  I have
tried the same query with even larger file sizes on other data managers
and not had this problem. (In fact, I copied the query from an existing
FoxPro program.)

Also, in my production app, the actual queries being run ar much more
complex, including multiple nested queries, and only with the having
clause is there ever a problem.

I would change it to a JOIN against an anonymous view and test again
-

SELECT m.*
FROM main m
INNER JOIN (SELECT main_ID
FROM receipt
GROUP BY main_ID
HAVING COUNT(1)  5) as r
ON m.id = r.main_ID

This query actually does run quickly.  Thanks - I will try to work the
syntax into my query generator.

- or to break it into two tables for some real speed -

CREATE TEMPORARY TABLE tmpR
SELECT main_ID
FROM receipt
GROUP BY main_ID
HAVING COUNT(1)  5;

alter table tmpR add key(main_Id);

SELECT m.*
FROM main m
INNER JOIN tmpR r
on m.ID = r.main_ID;

DROP TABLE tmpR;

This is actually the second scenario I had tried, as noted in my
original post, and it does yeild better results than the nested query,
but still takes an incredibly long time to run.

Either method should avoid the re-execution of the subselect for every
row 
in your primary table. Adding the index to the temporary table will
make 
the last select really fly.

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Thanks for the info, and for the query syntax to work around the
problem.  I still think this is a bug in processing the nested query,
and if it is runing the subquery for each line in the master table, i
think that is incorrect.

 - Leo Siefert







Leo Siefert [EMAIL PROTECTED] wrote on 07/19/2004
11:22:39 
AM:

 OS: Win2k Server
 MySQL: 4.1.1 alpha / 4.1.3 beta
Table type: INNO DB
 
 In my production environment, running the query:
 
 select * from main where id in
 (select main_id from receipt group by main_id having COUNT(*)  5)
 
 will hang the server - sometimes for over a day, thugh it seems it
will
 eventually complete working on it if given enough time. Currently
main
 contains ~200,000 records and receipt contains ~16,000. Main records
 with any receipts have an average of ~10 receipts, but most have
none. 
 
 Created a smaller test database:
 
 master
id   int(6)  primary  autoincrement
name  varchar(25)  (filled with random 10 char strings)
 
 detail
id   int(6)  primary  autoincrement
master_id   int(6)  index  (filled with random ints =
 max(master.id))
detail  varchar(25)  (filled with random 10 char strings)
 
 temp
id int(6) index
 
 Fill master with 1,000 records, detail with 10,000.
 Clone and fill master with 10,000 records, detail with 100,000.
 
 Query:
 
 select * from master where master.id in
 (select master_id from detail group by master_id having COUNT(*) 
2)
 
 (small) returns 76 rows in 13 seconds.
 (large) returns 496 rows in 566 seconds. (COUNT(*)  15)
 
 Tried a two part query, sending the intermediate results to a
temporary
 table:
 
 create temporary table t select master_id from detail group by
 master_id having COUNT(*)  2;
 select * from master where master.id in (select master_id from t);
 drop table t;
 
 (small) returns 76 rows in 2.8 seconds.
 (large) returns 496 rows in 17 seconds.
 
 Running the intermediate results into a permanent table:
 
 truncate table temp;
 insert into temp select master_id from detail group by master_id
having
 COUNT(*)  2;
 select * from master where master.id in (select id from temp);
 
 (small) returns 76 rows in 0.16 seconds.
 (large) returns 496 rows in 0.17 seconds.
 
 Have tried playing around with some of the system variables:
 query_cache_size, innodb_buffer_pool_size with no real affect.
 
 In our production environment (record size is much larger, similar
 number of records to the large test set), both the nested query and
the
 two-part query using a temporary query hang for indeterminate ( 6
hrs)
 amounts of time, leaving the use of a permanent table as the only
 option. Of course, the only real way to manage this is to create a
 dedicated scratch table for each user of the system, a somewhat
onerous
 workaround.
 
 Anyone have an idea on a solution to this? Is there something in
 setting up for INNO DB that I am missing, or should I file this as a
 bug?
 
 Thanks.
 
  - Leo
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql 
 To unsubscribe:   
http://lists.mysql.com

Nested query with GROUP BY ... HAVING COUNT(*) ... hangs 4.1.3 beta

[Leo Siefert]

OS: Win2k Server
MySQL: 4.1.1 alpha / 4.1.3 beta
   Table type: INNO DB

In my production environment, running the query:

select * from main where id in
(select main_id from receipt group by main_id having COUNT(*)  5)

will hang the server - sometimes for over a day, thugh it seems it will
eventually complete working on it if given enough time. Currently main
contains ~200,000 records and receipt contains ~16,000. Main records
with any receipts have an average of ~10 receipts, but most have none. 

Created a smaller test database:

master
   id   int(6)  primary  autoincrement
   name  varchar(25)  (filled with random 10 char strings)

detail
   id   int(6)  primary  autoincrement
   master_id   int(6)  index  (filled with random ints =
max(master.id))
   detail  varchar(25)  (filled with random 10 char strings)

temp
   id int(6) index

Fill master with 1,000 records, detail with 10,000.
Clone and fill master with 10,000 records, detail with 100,000.

Query:

select * from master where master.id in
(select master_id from detail group by master_id having COUNT(*)  2)

(small) returns 76 rows in 13 seconds.
(large) returns 496 rows in 566 seconds. (COUNT(*)  15)

Tried a two part query, sending the intermediate results to a temporary
table:

create temporary table t select master_id from detail group by
master_id having COUNT(*)  2;
select * from master where master.id in (select master_id from t);
drop table t;

(small) returns 76 rows in 2.8 seconds.
(large) returns 496 rows in 17 seconds.

Running the intermediate results into a permanent table:

truncate table temp;
insert into temp select master_id from detail group by master_id having
COUNT(*)  2;
select * from master where master.id in (select id from temp);

(small) returns 76 rows in 0.16 seconds.
(large) returns 496 rows in 0.17 seconds.

Have tried playing around with some of the system variables:
query_cache_size, innodb_buffer_pool_size with no real affect.

In our production environment (record size is much larger, similar
number of records to the large test set), both the nested query and the
two-part query using a temporary query hang for indeterminate ( 6 hrs)
amounts of time, leaving the use of a permanent table as the only
option. Of course, the only real way to manage this is to create a
dedicated scratch table for each user of the system, a somewhat onerous
workaround.

Anyone have an idea on a solution to this? Is there something in
setting up for INNO DB that I am missing, or should I file this as a
bug?

Thanks.

 - Leo

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

RE: Combining multiple selects into 1 select.

[Leo]

Hi Andy,

First of all you can make a temporary table..

Query 1:
CREATE TABLE t_mytable
SELECT *
FROM myTable
WHERE OnSale = 'Y'
 
Query 2:
INSERT INTO t_mytable
SELECT *
FROM myTable
ORDER BY Category
 
Query 3:
INSERT INTO t_mytable
SELECT *
FROM myTable
WHERE Closeout = 'Y'

Query 4:
SELECT *
FROM t_mytable 

Or ... If it's ok for you to upgrade to newer version
That support the UNION syntax (4.0.x I guess) you can do the following

Query 1:
SELECT *
FROM myTable
WHERE OnSale = 'Y'
UNION
SELECT *
FROM myTable
ORDER BY Category
UNION
SELECT *
FROM myTable
WHERE Closeout = 'Y'

HTH.. :)
Leo

 -Original Message-
 From: Andy [mailto:[EMAIL PROTECTED] 
 Sent: Wednesday, June 02, 2004 1:34 AM
 To: [EMAIL PROTECTED]
 Subject: Combining multiple selects into 1 select.
 
 Hello,
 
 Is is possible to combine the results of multiple selects 
 into one query/result?  And if so, how do you implement it?  
 A simple example follows:
 
 Query 1:
 SELECT *
 FROM myTable
 WHERE OnSale = 'Y'
 
 Query 2:
 SELECT *
 FROM myTable
 ORDER BY Category
 
 Query 3:
 SELECT *
 FROM myTable
 WHERE Closeout = 'Y'
 
 I would like to combine the 3 queries into 1 result for easy 
 parsing/manipulation in my program.  Also, each of the 
 queries may/will have duplicate IDs/RecordsA record that 
 appears in Query 1 will also appear in Query 2.
 
 We are running version 3.23.54, for sun-solaris2.8 (sparc)
 
 Thanks for any help!
 
 -Andy


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

Another loss of mysql

[Leo]

After two years of developing a new system based on MySQL
for the company i work at...

it turned out to face a failure..
not because the performance.. nor the price...

finally the company choose Oracle Application Suite
because the Oracle brand it self is a guarantee to bussiness competition

i mourn for the dead of my mysql project

good bye.. good luck

--
Regards
Leonardus Setiabudi
IT Project Coordinator
PT Bina San Prima, 
www.binasanprima.com
http://gtw.binasanprima.com/~leo


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

mysql database, user table, two root accounts

[Leo Donahue]

I am less than 24 hours new to MySql.  I have executed the following sql
scripts:

use mysql;
delete from user where User='';
delete from db where User='';
flush privileges;

select host, user, password from user;

The last sql query yields the following:

hostuserpassword
-
localhost   roothexadecimal values.
%   rootnothing here.

Why are there two root accounts?

Thanks,
ld


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

RE: mysql database, user table, two root accounts

[Leo Donahue]

Yes, this helps thank you.

-Original Message-
From: Arjun Subramanian [mailto:[EMAIL PROTECTED]
Sent: Friday, January 09, 2004 2:16 PM
To: 'Leo Donahue'; [EMAIL PROTECTED]
Subject: RE: mysql database, user table, two root accounts


That's not two root accounts. What that means is this:

The first line defines privileges for root connecting from localhost
The second line defines privileges for root connecting from any remote
host. Hence the %. It implies [EMAIL PROTECTED]

Hope this helps.

Arjun Subramanian
Georgia Tech Station 32003
Atlanta GA 30332
Cell: +404.429.5513
http://www.arjunweb.com


-Original Message-
From: Leo Donahue [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 09, 2004 2:47 PM
To: [EMAIL PROTECTED]
Subject: mysql database, user table, two root accounts

I am less than 24 hours new to MySql.  I have executed the following sql
scripts:

use mysql;
delete from user where User='';
delete from db where User='';
flush privileges;

select host, user, password from user;

The last sql query yields the following:

hostuserpassword
-
localhost   roothexadecimal values.
%   rootnothing here.

Why are there two root accounts?

Thanks,
ld


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






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


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

One Slave Many Master

[Leo]

I know someone already ask this,
and the answer generally 'NO YOU CANT' :b

but, is there any work around so i can make a backup server (slave),
from many other server (master) through replication?

well, it's not just a backup server actually..
it really is the headquarter's db server,
and the branch offices can not directly post their transaction..
because the line is teriblyy sllw :D

so, im thinking of adding extra server on branches
and replicate what ever happens there to the main server

perhaps, if i can run multiple mysql server on different port on the main server,
with it's own database,
that act as a slave one to one to other server,
and add one more that handle all of the DB ..

Main ServerBranch
-----
DB1, 3306  DB1, 3306, BranchServer1
DB2, 3307  DB2, 3307, BranchServer2
DB3, 3308  DB3, 3308, BranchServer3
DB4, 3309  DB4, 3309, BranchServer4
and so on...

MASTER DB(DB1,DB2...DBn) on port 33xx

is this possible???
or maybe there are other solution.. pleasseee.. i really appreciate it

oh, one more thing, can the replication run both way?
what ever happened on machine A, replicated on machine B and vice versa..

thanks...

--
Regards
Leonardus Setiabudi
IT Project Coordinator
PT Bina San Prima, www.binasanprima.com

Re: One Slave Many Master

[Leo]

Thank You All for the response,
I'll try to set up your recomendation..
I'll post the result back in a few days..

thanks :)

--
Regards
Leonardus Setiabudi
IT Project Coordinator
PT Bina San Prima, www.binasanprima.com

MySQL user in Indonesia

[Leo]

Hi All,

im sorry if this mail a bit out of topic =)

im currently developing a new system based on MySQL for the company i work on.
and i have to report how wide and famous is mysql had been used,
especially in indonesia.

i need the fact that i can show to my boss,
why i choose mysql.. well i have a lot of it,
but he demand some example, which company in indonesia had successfully 
implemented mysql in their core bussiness

so please, let me know..
i already run a search at google, but i have trouble determining what kwyword should i 
use

if there is among you that work in a company here in indonesia,
please, would you kindly share your experience

thanks in advance

--
Regards
Leonardus Setiabudi
IT Project Coordinator
PT Bina San Prima, www.binasanprima.com

Problem with mysql_close() API

[Leo]

Hi All,

i have a problem everytime i call the function mysql_close(),
it result in Access Violation Error in module libmysql.dll

fyi,
im migrating from 3.23.52 to 4.0.x
server runs under linux,
im accesing the server through windows based application
built with delphi, using Object Pascal translation from C API (by Jorge del Conde)
with the library i took from mysql directory

the flow is similiar like this:

mysql_init(@myconnection);
mysql_real_connect(@myconnection,...);
...
some proccess
...
mysql_close(@myconnection); -- this is where the error raised


i slip in mysql_init before mysql_close, and the problem disappear..

mysql_init(@myconnection);
mysql_real_connect(@myconnection,...);
...
some proccess
...
mysql_init(@myconnection);
mysql_close(@myconnection); -- this is where the error raised

is this normal??
is this how it should be? i've red the manual, and there is none mentioning that i 
have to do init before cloaing one.

thanks for any comment

--
Regards
Leonardus Setiabudi
IT Project Coordinator
PT Bina San Prima, www.binasanprima.com

Re: Select with inner join NOT IN / IN

[Leo]

Hi Jens,

try to rewrite you query to

SELECT A.Kommentar, A.SequenzNr
FROM Aufgabe A LEFT JOIN Taetigkeiten
ON A.kommentar=Taetigkeiten.Bezeichnung
AND Taetigkeiten.inaktiv = '1'

WHERE isnull(Taetigkeiten.Bezeichnung)
LIMIT 0, 30 


--
Regards
Leonardus Setiabudi
IT Project Coordinator
PT Bina San Prima, www.binasanprima.com

  - Original Message - 
  From: Gronau, Jens 
  To: [EMAIL PROTECTED] 
  Sent: Friday, December 12, 2003 2:04 PM
  Subject: Select with inner join NOT IN / IN


  Hello I need help
  This Statement does not work. MySql Version is 3.23.47. On Oracle Instance it works.
  What can i do ? Whitch Version do i need ? MySql 4.xx ?

  MySQL 3.23.47 running on localhost 

  SELECT A.Kommentar, A.SequenzNr from Aufgabe A 
  LEFT JOIN Taetigkeiten ON Aufgabe.Kommentar = Taetigkeiten.Bezeichnung where inaktiv 
= '1' ) ;

  Error
  SQL-query : 
  SELECT A.Kommentar, A.SequenzNr
  FROM Aufgabe A
  WHERE A.kommentar NOT 
  IN ( 

  SELECT Bezeichnung
  FROM Taetigkeiten
  where inaktiv = '1')
  LIMIT 0, 30 
  MySQL said: 
  You have an error in your SQL syntax near 'SELECT Bezeichnung FROM Taetigkeiten 
where inaktiv = '1' )  LIMIT 0, 30' at line 1

  Thanks Jens Gronau

Re: Cannot find an index that will be used for SELECT

[Leo]

Hi Ed,

i tried to create this table

CREATE TABLE `rectangle` (
  `myname` char(5) NOT NULL default '',
  `minx` tinyint(3) unsigned default '0',
  `miny` tinyint(3) unsigned default '0',
  `maxx` tinyint(3) unsigned default '0',
  `maxy` tinyint(3) unsigned default '0',
  `miscfield1` tinyint(3) unsigned NOT NULL default '0',
  `miscfield2` tinyint(3) unsigned NOT NULL default '0',
  UNIQUE KEY `myindex` (`maxx`,`maxy`,`minx`,`miny`,`myname`)
) TYPE=MyISAM

and execute this query

explain
select myname 
from rectangle
where 
minx=2
and maxx=2
and miny=3
and maxy=3;

result:
table,type,possible_keys,key,key_len,ref,rows,Extra
rectangle,range,myindex,myindex,6,NULL,4,Using where; Using index

perhaps you have some other field as primary key..
adding an index on those four field only (top,left,bottom,right) wouldnt make much 
use..
try to create an index on those four plus one (or more) field 
that represent your record as unique as possible

i hope this help, cmiiw..
if ther's still a problem maybe you can mail me your table structure

regards
-leo-



  - Original Message - 
  From: Ed McNierney 
  To: [EMAIL PROTECTED] 
  Sent: Thursday, November 20, 2003 11:22 AM
  Subject: Cannot find an index that will be used for SELECT


  I'm completely stumped trying to create any index MySQL will use at all
  for my SELECT.
   
  I've got a table with four columns describing the upper-right and
  lower-left coordinates of a rectangle - RIGHT, LEFT (max X, min X), TOP,
  BOTTOM (max Y, min Y).  I have an X, Y coordinate and I want to select
  all rows for which the X, Y point is inside the rectangle.  In other
  words:
   
  SELECT * FROM ROWS WHERE X = RIGHT AND X = LEFT AND Y = BOTTOM AND Y
  = TOP
   
  (using BETWEEN didn't make any difference).
   
  I have tried indexes on RIGHT, LEFT, TOP, BOTTOM, RIGHT+LEFT,
  TOP+BOTTOM, and RIGHT+LEFT+TOP+BOTTOM and none of them get used.  All
  fields are FLOAT.  I can't figure out how to get any index to be used,
  nor how to restructure my query to improve things.  Thanks!
   

search engine

[Leo]

Hi All,

say i would like to search for customer, entrying some text
then i would like mysql to return all the customer which id and full name consist of 
the text

ex:

IDName
John01John The Junior
Abe01 Abe The Senior
Jo01  Johns The Best

if i enter the criteria 'john' i would like the result of John01 and Jo01
if i enter the criteria 'be' i would like the result of Abe01 and Jo01

if i use the fulltext index, then i could only match a full word, eg: 
match(id,name) against('John') only resulting the field John01

i can use the clause 

where id like '%text%'
or name like '%text%'

but it's wayyy to slow :)

anyone have a better idea.. i appreciate it so much

regards
-leo-

Re: How to move database to new server

[Leo]

i think this can be done by dumping your database to a file
and then fetch it to mysql cli

i make a batch script to do this daily...
i have not use replication, so i just copy the entire database
(in windows version):


c:\mysql\bin\mysqldump.exe -a -c -C -e --add-drop-table --add-locks 
-F -f -Q -h theSourceHost --user=theUser --password=thePassword -l -n 
-r c:\dumpFile.sql -B theDatabase

c:\mysql\bin\mysql.exe -h theTargetHost --user=theUser --password=thePassword  
c:\dumpfile.sql

i hope this help... cmiiw

regards
-leo-


ps: you can see the online help for all the parameter
 



- Original Message - 
  From: Andrew Simpson 
  To: [EMAIL PROTECTED] 
  Sent: Thursday, November 20, 2003 11:06 PM
  Subject: How to move database to new server


  Hi

  I have a mysql database with 20 tables containing data whch i need to transfer to a 
new hosting server
  (i access this server via webmin.)

  What's the easiest way to move a database from one server to the other?.

  Can you export full databases from mysql (this would be the easiest),  or do i need 
to create the database
  and all the tables on the new server first and then find a way to just export and 
import the data?.

  TIA

mysql API for Delphi

[Leo]

Hello All,

can someone tell me where to get the latest mysql API for Delphi (mysql.pas)
that works with mysql library version 4 ?

i already check the author's site.. and no update yet.

i also found some problem connecting to mysql version 4.1 alpha
with mysqlfront.. it says that the client did not support the authentication
(or something like that.. i forgot to write it down :) )
and replacing the libmysql.dll wont work

any idea?
tia

-leo-

Re: Some help with a complex query

[Leo]

it would help alot if you dump the table structure for us
  - Original Message - 
  From: Elisenda 
  To: [EMAIL PROTECTED] 
  Sent: Tuesday, November 11, 2003 7:10 PM
  Subject: Some help with a complex query


  I have a query which tries to select different fields from 5 different
  tables. 

  In WHERE part I have write all the conditions and relationships. Perhaps two
  many.

  The main table for me is FASE. From this table I try to find all the other
  information.

  I guess I'm doing something wrong but I don't know what.

  SELECT 

  CE.CE_CENTRO, 
  CE.CE_DOMICILIO, 
  CE.CE_CP,
  CE.CE_POBLACION, 
  CE.CE_PROV, 
  PP.PP_CONTACTO, 
  PP.PP_CARGO, 
  CA.CA_HORARIO,
  AU.AU_A_M, 
  AU.AU_A_F, 
  FASE.PR_DATE_VISITA_1

  FROM AU, CA, CE,FASE,PP

  WHERE

  FASE.SQL_ID_PY='P081' AND
  FASE.PR_FLAG= '1' AND
  CA.CA_ID_IDIOMA_A= '6' AND
  AU.AU_NIVEL= '13.14' AND
  AU.AU_Aula= fase.AU_PR_Aula AND
  AU.AU_ID_CE = CA.CA_ID_CE AND
  CE.CE_ID_CE = CA.CA_ID_CE AND
  CE.CE_ID_CE = Fase.SQL_ID_CE AND
  CE.CE_ID_CE = PP.PP_ID_CE AND
  Fase.PR_PP_ID_Coord = PP.PP_ID_PP


  Thank you fro your help in advanced.

  Eli


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

Re: SQL query question

[Leo]

try group by

SELECT firmal.beskrivelse as Businessline, lokasjon.navn as Location, 
count(person.name) as Sum People
FROM
firmal INNER JOIN (
person INNER JOIN lokasjon
ON person.lokid = lokasjon.lokid)
ON firmal.firmalid = person.firmalid
group by firmal.beskrivelse, lokasjon.navn

-leo-

From: Paal Eriksen 
  To: [EMAIL PROTECTED] 
  Sent: Tuesday, November 11, 2003 7:11 PM
  Subject: SQL query question
   
  SELECT person.name as Name, firmal.beskrivelse as Businessline, lokasjon.navn as 
Location
  FROM
  firmal INNER JOIN (
  person INNER JOIN lokasjon
  ON person.lokid = lokasjon.lokid)
  ON firmal.firmalid = person.firmalid
   
  Businessline, Location, Sum people
  A  AA   10
  A  AB   30
  B  AA   5
  B  AB   27
  B  AC   90

Re: Complex query woes

[Leo]

hi Steffan,

i already email you about the null result,
it should be eliminated with a where statement (not including null value)

and about the long time it took,
maybe you shuld add some index to your table...

i suggest

alter table customer add index parents (pid);
alter table customer add index my_id (id);

-leo-
  - Original Message - 
  From: Steffan A. Cline 
  To: Leo ; [EMAIL PROTECTED] 
  Sent: Monday, November 10, 2003 8:12 PM
  Subject: Re: Complex query woes


  Leo,

  Thanks for the quick reply. There was a typo but I fixed it. Below is
  what I used after correcting it :

  select mgr.company, building.bldgname, tenant.id from customers mgr left
  join customers building on building.pid=mgr.id left join customers tenant on
  tenant.pid=building.id group by mgr.id, building.id, tenant.id order by
  mgr.company, building.bldgname, tenant.company;

  This was closer. Problem now is that it took 6.56 seconds and returned 610
  rows. I have no idea how I now have 610 rows where there are only 279. Any
  thing else you would suggest?




  Thanks

  Steffan

  ---
  T E L  6 0 2 . 5 7 9 . 4 2 3 0 | F A X  6 0 2 . 9 7 1 . 1 6 9 4
  Steffan A. Cline
  [EMAIL PROTECTED] Phoenix, Az
  http://www.ExecuChoice.net  USA
  AIM : SteffanC  ICQ : 57234309
  The Executive's Choice in Lasso driven Internet Applications
Lasso Partner Alliance Member
  ---

Re: Maybe easy, maybe hard SELECT puzzle :)

[Leo]

SELECT DISTINCT f.id FROM Table1 AS t, Features AS f
WHERE f.id = t.id AND f.FeatureCode IN ('A01', 'B02');

HTH

what if i have a record that have 'A01' in the features, but not 'B02'
wouldnt it still be displayed in the result?

as far as i know, IN keyword act simply as OR keyword
didnt Jonathan wrote : 
But it I want all records from Table1 that have features 'A01' _and_ 'B02'

notice the _and_ ?

-leo-

Re: Aliases

[Leo]

what if we use the function at the right side of the equation?
such as

select anything
from table1, table2
where table1.id=left(table2.id,somenumber)

having both id in table were indexed
dont you think the index in table1 would still be used.. :)
cmiiw

-leo-

  - Original Message - 
  From: Erik Osterman 
  To: [EMAIL PROTECTED] 
  Sent: Wednesday, November 12, 2003 3:17 AM
  Subject: RE: Aliases



   From: Matt W [mailto:[EMAIL PROTECTED] 
   Sent: Monday, November 10, 2003 5:47 PM
   To: Erik Osterman; [EMAIL PROTECTED]

   No, Roger's method can't use an index. :-) But yes, using WHERE is
   better than HAVING.

  Ah... right indeed. In this case it wouldn't work. My mistake... :) 

   To get the WHERE to use an index, don't use a function in the
   comparison:

  Though you can use functions on indexed columns so long as you (generally)
  aren't using table columns which lie in your domain. 

  E.g. FROM_UNIXTIME(1068520546) or NOW() will use indexes, but
  FROM_UNIXTIME(col) will not -- since col is in your input domain. Unless
  we're talking about MIN/MAX functions and those are an exception!

  So for clarification, David, those functions that do operate on indexed
  columns will only work in WHERE clauses and not work in HAVING clauses.




  Thanks for the correction,


  Erik Osterman
  http://osterman.com/

Re: Complex query woes

[Leo]

have you try left join?

select 
mgr.company,
building.bldgname,
tenant.id
from
customers mgr
left join customers building on building.pid=mgr.id
left join costumers tenant on tenant.pid=building.id
group by mgr.id, building.id, tenant.id
order by mgr.company, building.bldgname, tenant.company

hopefully it work :)

-leo-

  - Original Message - 
  From: Steffan A. Cline 
  To: [EMAIL PROTECTED] 
  Sent: Monday, November 10, 2003 12:56 PM
  Subject: Complex query woes


  Basically I have a table that contains 3 types of records. Property
  managers, buildings and tenants. They are related upon insert by an ID and a
  PID (parent id). For example :

  ID  PID CategoryName
  -
  1   PM  ABC Management
  2   1   BldgGlen Heights
  3   2   tenant  Joe's salon

  Hopefully this shows how they are related. My goal is to ultimately on a
  Lasso (like php) page to render them like this :

  ABC Management
  Glen Heights
  Joe's salon
  Some other building
  Some other tenant

  I am able to handle the formatting fine the issue is how to get the data
  returned like this. I tried the following :

  select mgr.company, building.bldgname, tenant.company from customers
  as mgr,customers as building, customers as tenant where building.pid =
  mgr.id and tenant.pid  = building.id  order by
  mgr.company,building.bldgname,tenant.company;

  But it only returns 173 rows are there are 279. As you will see in
  http://phattwelve.hldns.com:90/workorder/findaccount2.lasso
  there are some property managers with buildings and no tenants, also
  property manager with no buildings. These get omitted by the above sql.
  Currently I am doing this with nested statements via lasso but is getting
  ridiculously slow on the live system as they add more and more clients.

Re: Maybe easy, maybe hard SELECT puzzle :)

[Leo]

But it I want all records from Table1 that have features 'A01' _and_ 'B02',
clearly
SELECT * from Table1 JOIN Features USING(id) WHERE
Features.FeatureCode='A01' AND Features.FeatureCode='B02' doesn't work.


okay maybe this one is a more stupid and complex solution :)
but i think it should work (sorry i havent test it yet :p )

select table1.id
from
table1
left join features f1 on table1.id=f1.id
and f1.featurecode='A01'
left join features f2 on table1.id=f2.id
and f1.featurecode='B02'

.
.
. (as many as you like)

where
not isnull(f1.id)
and not isnull(f2.id)
.
.
. (as many as you like)

you can generate the repeatance by some script
hope this help

-Leo-

RE:sql question

[Leo]

SELECT forum_categories.id AS  `id` , forum_categories.name AS  `name` ,
forum_categories.createdby AS  `createdby` ,
forum_categories.order AS  `order` ,DATE_FORMAT( forum_categories.created,%m/%d/%y 
%l:%i %p  )  AS  `created` ,
COUNT( forum_topics.id )  AS  `topics`, SUM(forum_posts.id) AS `posts`
FROM forum_categories
LEFT JOIN forum_topics ON forum_categories.id = forum_topics.category
LEFT JOIN forum_posts ON forum_topics.id = forum_posts.topic
WHERE forum_categories.domain = 01
GROUP BY forum_categories.id
ORDER BY forum_categories.order ASC

imho, you should have a reference field in table topics to table categories
so you wont end up with cross table query (multiplying the result)

LEFT JOIN forum_posts ON forum_topics.id = forum_posts.topic
left join forum_categories on forum_categories.id=forum_posts.id

-Leo-

Re: multitable sum problem

[Leo]

i think you should have a sales person table
that hold unique id for each sales force
such as

salesrep (id,etc..)
| 101 | ... |
| 102 | ... |

so you can alter the query into 
select 
salesrep.id,
sum(ifnull(salesinvoices.invamt,0)) as curramt,
sum(ifnull(lysalesinvoices.lyinvamt,0)) as lyamt
from 
salesrep
left join salesinvoices on salesrep.id=salesinvoices.salesrepid
left join lysalesinvoices on salesrep.id=lysalesinvoices.salesrepid
group by salesrep.id

hope this help..

-Leo-

Re: InnoDB Questions

[Leo Huang]

MySQL doesn't work.

I tried to modified the line:
innodb_data_file_path = ibdata1:10M:autoextend

to
innodb_data_file_path = ibdata1:500M
or
innodb_data_file_path = ibdata1:500M:autoextend
or
innodb_data_file_path = ibdata1:500M;ibdata2:1000M:autoextend

they all gave me the same error below.


InnoDB: Error: data file ./ibdata1 is of a different size
InnoDB: than specified in the .cnf file!
InnoDB: Could not open data files
031105 9:42:56 Can't init databases
031105 09:42:56 mysqld ended

The last one really does the matter!! That's if I run out of the space
on the current directory, I won't be able to put another file anywhere
else!?

Leo



Nitin wrote:

You're right, it wont decrease the physical size, but only free up the space
within file to optimize the tablespace, in case, you want to check the size
of this data file, you can remove autoextend from:

innodb_data_file_path = ibdata1:10M:autoextend

and specify the size limit in the place of 10M, but i guess, if you specify
the size to less than 790M (which is the current size of your datafile), to
say 500M and the space is free in that file, it will resize it. That's the
behaviour of Oracle datafiles (believe me, i'm oracle certified!). try it,
and let me know as i dont have my database on innodb yet.

'Tablespace is part of your database. database consists of at least one
tablespace. it's basically used to restrict users from seeing other user's
data. like, you can assign a tablespace to a user and none else (ofcourse
other than root) can see the data.

For more info, have a look at:
http://www.mysql.com/doc/en/InnoDB_File_space.html

Enjoy
Nitin


- Original Message - 
From: Leo Huang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 8:00 PM
Subject: Re: InnoDB Questions


  

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Thank you very much for your reply, Nitin.

I did read the Adding_and_removing in the manual, but it says
Currently you cannot remove a datafile from InnoDB. To decrease the
size of your database you have to use `mysqldump' to dump all your
tables, create a new database, and import your tables to the new
database.
It does reduce the size of the database(similar to optimize for
MyISAM), but it doesn't reduce the size of the file.

Could you explain a bit what is a tablespace?

Thanks,
Leo

/etc/my.cnf:
[mysqld]
datadir=/var/lib/mysql
set-variable=max_connections=300
innodb_data_file_path = ibdata1:10M:autoextend
default-table-type=InnoDB
set-variable = innodb_buffer_pool_size=512M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_log_file_size=10M
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1


[mysql.server]
user=mysql
basedir=/usr

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



- - Original Message - 
From: Nitin [EMAIL PROTECTED]
To: Leo Huang [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, November 05, 2003 1:01 AM
Subject: Re: InnoDB Questions




Hello,

first things first, you cann't resize your datafiles without
  

shutting down


your database. if it's ok with you, have a look at
http://www.mysql.com/doc/en/Adding_and_removing.html

you may want to have a look at you my.cnf file, stored in mysql data
  

dir or


in /etc dir, for the default options specified there fo the datafile
  

with:


innodb_data_file_path

Yes, you can add data file, just add another entry to above option.
  

option


entry is self-explainatory.

At last, ibdata1, ibdata2 are actual data files used to store
  

actual


data. one or more of these files are attached to one tablespace and
  

one file


cant span across tablespaces.

ib_logfile0, ib_logfile1 are log files, which are used to log sql
  

statements


applied to database. these files are used to restore data in case of
  

any


crash or mishap.

for further info, have a look at:
http://www.mysql.com/doc/en/InnoDB_start.html

Enjoy
Nitin


- Original Message - 
From: Leo Huang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 6:28 PM
Subject: InnoDB Questions


  

Hello,

I have a few questions about InnoDB.
I am new to InnoDB, and just converted my MyISAM tables into


InnoDB a


few days ago.
I notice that it generates these files

-rw-rw1 mysqlmysql2560 Nov  2 13:07
ib_arch_log_00
-rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
-rw-rw1 mysqlmysql10485760 Nov  4 20:44


ib_logfile0


-rw-rw1 mysqlmysql10485760 Nov  4 20:44


ib_logfile1


-rw-rw1 mysqlmysql10485760 Nov  3 00:02


ib_logfile2


But how can those files work together?

In InnoDB documentation, it suggests to add another file ibdata2


to


get higher performance. Can I do that now, after

Re: Mysql Stoping

[Leo Huang]

Hi Trevor,

I suggest you to compile and reinstall MySQL from the source distribution.

A suggested option is

CFLAGS=-O3 CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions \
   -fno-rtti ./configure --prefix=/usr/local/mysql --enable-assembler \
   --with-mysqld-ldflags=-all-static

For more information, please refer to
http://www.mysql.com/doc/en/Installing_source.html

If you are running a RH Linux try to modify the --prefix=/usr, so you
can use the RH scripts, and don't forget to set up something like --datadir

This is my configure options, hope it helps.

--prefix=/usr --datadir=/var/lib/mysql --with-innodb
--with-extra-charsets=complex --enable-thread-safe-client
--enable-local-infile --enable-assembler --disable-shared
--with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --quiet


Cheers,
Leo




Trevor wrote:

Hi All

Was wondering if someone could shef a bit of light on whats happening, as i keep 
loosing the connection to the mysql server, and i get the following error:
mysqld dead but subsys locked

Thanks in advance

Cheers

Trevor

  




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

Re: Problem with DELETE USING

[Leo Huang]

Bamelis,

The error message doesn't seem to match your SQL...

It only shows up to 'AND tblTest.URL = tblT'
but your SQL is 'AND tblTest.Comment = tblTest2.Comment'
Is that a problem??

An example from  MySQL manual is 'DELETE FROM t1,t2 USING t1,t2,t3 WHERE 
t1.id=t2.id AND t2.id=t3.id'

Leo

Bamelis Steve wrote:

Hi, 

I'm a newbie when it comes to mySQL.

I have the following command.



DELETE FROM tblTest2 

USING tblTest2,tblTest 

WHERE tblTest.Name = tblTest2.Name 

AND tblTest.URL = tblTest2.URL 

AND tblTest.Comment = tblTest2.Comment



I get the following error:

You have an error in your SQL syntax near 'USING tblTest2,tblTest WHERE
tblTest.Name = tblTest2.Name AND tblTest.URL = tblT' at line 1


I want to delete rows from tblTest2 where there are similarities in tblTest.

In fact using a subselect or something.



Could anyone help me please,

Thx





 



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

Re: Viruses from the list

[Leo Huang]

Al Bogner,

Thanks for you info.

Yes, I got quite a few as well. About Microsoft update stuff etc.

But I think emails with viruses are quite common, my mail server 
captures around 2,000 emails with virus everyday. Also this is an old 
virus(relatively speaking), so it should be fine, I think.

Leo

Al Bogner wrote:

I use an email-adress for this list only and since my first posting a few days 
ago I got viruses, while I didn't before.

VIRUS FROM mamo @ hvd.healthnet.lu (W32/[EMAIL PROTECTED])
VIRUS FROM kawamoto @ wave.tky.plala.or.jp ([EMAIL PROTECTED])
VIRUS FROM sales @ vulcanford.ca (W32/[EMAIL PROTECTED])/[EMAIL PROTECTED])
Wake up people, it was time enough to update virus-definitions. Clean your 
pcs.

I will disable my email-adress soon.

Al

 



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

InnoDB Questions

[Leo Huang]

Hello,

I have a few questions about InnoDB.
I am new to InnoDB, and just converted my MyISAM tables into InnoDB a
few days ago.
I notice that it generates these files

-rw-rw1 mysqlmysql2560 Nov  2 13:07
ib_arch_log_00
-rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile0
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile1
-rw-rw1 mysqlmysql10485760 Nov  3 00:02 ib_logfile2

But how can those files work together?

In InnoDB documentation, it suggests to add another file ibdata2 to
get higher performance. Can I do that now, after I have created the
ibdata1 and used it for a while?

The most important thing is I deleted a 300M database, but the ibdata1
remains the same size. MyPHPAdmin says 330,000KB free. How can I make
the data file smaller?

I will be really appreciated if someone can briefly describe what's
happening to those files or point me to some articles.

Thanks a lot,
Leo


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

Re: InnoDB Questions

[Leo Huang]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Thank you very much for your reply, Nitin.

I did read the Adding_and_removing in the manual, but it says
Currently you cannot remove a datafile from InnoDB. To decrease the
size of your database you have to use `mysqldump' to dump all your
tables, create a new database, and import your tables to the new
database.
It does reduce the size of the database(similar to optimize for
MyISAM), but it doesn't reduce the size of the file.

Could you explain a bit what is a tablespace?

Thanks,
Leo

/etc/my.cnf:
[mysqld]
datadir=/var/lib/mysql
set-variable=max_connections=300
innodb_data_file_path = ibdata1:10M:autoextend
default-table-type=InnoDB
set-variable = innodb_buffer_pool_size=512M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_log_file_size=10M
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1


[mysql.server]
user=mysql
basedir=/usr

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



- - Original Message - 
From: Nitin [EMAIL PROTECTED]
To: Leo Huang [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, November 05, 2003 1:01 AM
Subject: Re: InnoDB Questions


 Hello,

 first things first, you cann't resize your datafiles without
shutting down
 your database. if it's ok with you, have a look at
 http://www.mysql.com/doc/en/Adding_and_removing.html

 you may want to have a look at you my.cnf file, stored in mysql data
dir or
 in /etc dir, for the default options specified there fo the datafile
with:

 innodb_data_file_path

 Yes, you can add data file, just add another entry to above option.
option
 entry is self-explainatory.

 At last, ibdata1, ibdata2 are actual data files used to store
actual
 data. one or more of these files are attached to one tablespace and
one file
 cant span across tablespaces.

 ib_logfile0, ib_logfile1 are log files, which are used to log sql
statements
 applied to database. these files are used to restore data in case of
any
 crash or mishap.

 for further info, have a look at:
 http://www.mysql.com/doc/en/InnoDB_start.html

 Enjoy
 Nitin


 - Original Message - 
 From: Leo Huang [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Tuesday, November 04, 2003 6:28 PM
 Subject: InnoDB Questions


  Hello,
 
  I have a few questions about InnoDB.
  I am new to InnoDB, and just converted my MyISAM tables into
InnoDB a
  few days ago.
  I notice that it generates these files
 
  -rw-rw1 mysqlmysql2560 Nov  2 13:07
  ib_arch_log_00
  -rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
  -rw-rw1 mysqlmysql10485760 Nov  4 20:44
ib_logfile0
  -rw-rw1 mysqlmysql10485760 Nov  4 20:44
ib_logfile1
  -rw-rw1 mysqlmysql10485760 Nov  3 00:02
ib_logfile2
 
  But how can those files work together?
 
  In InnoDB documentation, it suggests to add another file ibdata2
to
  get higher performance. Can I do that now, after I have created
the
  ibdata1 and used it for a while?
 
  The most important thing is I deleted a 300M database, but the
ibdata1
  remains the same size. MyPHPAdmin says 330,000KB free. How can I
make
  the data file smaller?
 
  I will be really appreciated if someone can briefly describe
what's
  happening to those files or point me to some articles.
 
  Thanks a lot,
  Leo
 
 
  -- 
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 



-BEGIN PGP SIGNATURE-
Version: PGP 8.0.2

iQA/AwUBP6e4GMJH0J7PNsMYEQIXIQCdGgQEyxFfJ3Vk8wZBNIz7FT7ilF8AoIDN
h21IQZ8ozOUeELhvWSpznyTI
=H/2E
-END PGP SIGNATURE-


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

Re: InnoDB Questions

[Leo Huang]

I don't know.

I will get some time this week, shutdown MySQL, backup my binary files, 
have a go as what Nitin said and see what's going on there.

Leo

Gabriel Ricard wrote:

On Tuesday, November 4, 2003, at 07:58  AM, Leo Huang wrote:

In InnoDB documentation, it suggests to add another file ibdata2 to
get higher performance. Can I do that now, after I have created the
ibdata1 and used it for a while?


How exactly does this increase performance? Will InnoDB store some 
data in one data file and some in another (somewhat like RAID 1)?

- Gabriel




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

Re: InnoDB Questions

[Leo Huang]

Hello Nitin,

From the timestamp of the log files, it seems that the first two files
works together while the last one seems just sitting there, doesn't do
anything.

Also, will the log files getting bigger and bigger in the future?? If so
how should I deal with them?

For your last suggestion, what will Oracle do if I specify a datafile
size less than the database size, e.g. I specify 20M in the my.cnf while
there are actually 400M of data in my database?

Leo

Nitin wrote:

Hello,

first things first, you cann't resize your datafiles without shutting down
your database. if it's ok with you, have a look at
http://www.mysql.com/doc/en/Adding_and_removing.html

you may want to have a look at you my.cnf file, stored in mysql data dir or
in /etc dir, for the default options specified there fo the datafile with:

innodb_data_file_path

Yes, you can add data file, just add another entry to above option. option
entry is self-explainatory.

At last, ibdata1, ibdata2 are actual data files used to store actual
data. one or more of these files are attached to one tablespace and one file
cant span across tablespaces.

ib_logfile0, ib_logfile1 are log files, which are used to log sql statements
applied to database. these files are used to restore data in case of any
crash or mishap.

for further info, have a look at:
http://www.mysql.com/doc/en/InnoDB_start.html

Enjoy
Nitin


- Original Message - 
From: Leo Huang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 6:28 PM
Subject: InnoDB Questions


  

Hello,

I have a few questions about InnoDB.
I am new to InnoDB, and just converted my MyISAM tables into InnoDB a
few days ago.
I notice that it generates these files

-rw-rw1 mysqlmysql2560 Nov  2 13:07
ib_arch_log_00
-rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile0
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile1
-rw-rw1 mysqlmysql10485760 Nov  3 00:02 ib_logfile2

But how can those files work together?

In InnoDB documentation, it suggests to add another file ibdata2 to
get higher performance. Can I do that now, after I have created the
ibdata1 and used it for a while?

The most important thing is I deleted a 300M database, but the ibdata1
remains the same size. MyPHPAdmin says 330,000KB free. How can I make
the data file smaller?

I will be really appreciated if someone can briefly describe what's
happening to those files or point me to some articles.

Thanks a lot,
Leo


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


http://lists.mysql.com/[EMAIL PROTECTED]
  



  




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

MySQL Replicaiton

[Leo Genyuk]

I am having problems to start MySQL replication. I followed all the 
steps outlined on the website, but replicaiton is not working. slave 
status shows the following:

mysql show slave status\G
*** 1. row ***
  Master_Host: mail.dbi.tju.edu
  Master_User: repl
  Master_Port: 3306
Connect_retry: 60
  Master_Log_File: mail-bin.001
  Read_Master_Log_Pos: 3651
   Relay_Log_File: blade4-relay-bin.001
Relay_Log_Pos: 3133
Relay_Master_Log_File: mail-bin.001
 Slave_IO_Running: No
Slave_SQL_Running: Yes
  Replicate_do_db:
  Replicate_ignore_db:
   Last_errno: 0
   Last_error:
 Skip_counter: 0
  Exec_master_log_pos: 3651
  Relay_log_space: 3133
1 row in set (0.00 sec)
As you can see Slave_IO_Running is set to NO.

I tried to start it manually with the follwoing command:
	slave start IO_THREAD;
without any luck. I have also tried to start and stop the slave server 
also wihtout any luck.

Thank you in advance for any help.

Leo.



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

ERROR 1186: Binlog closed, cannot RESET MASTER

[Leo Genyuk]

I am getting the following error after trying to flush the master:

mysql flush master;
ERROR 1186: Binlog closed, cannot RESET MASTER
I am using mysql  Ver 12.20 Distrib 4.0.13, for sun-solaris2.9 (sparc)

Does anyone know what this might be?

Thank you in advance,

Leo.

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

Updating MySQL db's

[Leo Genyuk]

I have two server running Appache + PHP + MySQL.

Server #1 is production and server#2 is development.

I would like to keep MySQL DB on server#2 up to date. That is any 
changes happening on server#1 I would like to be reflected on server#2.

Does anyone know how to do this?

Thank you in advance.

Leo.

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

Can't drop a database

[Leo Genyuk]

Hello.

I am running MySQL database on Sun Solaris 9.
# mysql -V
mysql  Ver 11.18 Distrib 3.23.54, for sun-solaris2.8 (sparc)
I am having a problem dropping a database. All the tables in the 
database have been dropped successfully, but the database does not want 
to go away. To be more specific, when I execute:
drop database clone_updater;
query runs without errors, but database is still there.

mysql show databases;
+--+
| Database|
+--+
| clone_updater|
| mysql |
+--+
51 rows in set (0.01 sec)
mysql drop database clone_updater;
Query OK, 0 rows affected (0.00 sec)
mysql show databases;
+--+
| Database|
+--+
| clone_updater|
| mysql |
+--+
51 rows in set (0.01 sec)
Restarting mysql had no effect on this issue.

Can anyone please help?

Thank you in advance,

Leo.

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

Re: Trouble converting SQL from Access

[Leo G. Divingracia III]

Asendorf, John wrote:
.


SELECT Dealers.*, SQRT(POW((2285-Zips.North),2)+POW((4760-Zips.West),2)) AS
Distance
FROM Dealers 
INNER JOIN Zips ON Dealers.Zip = Zips.Zip
ORDER BY POW((2285-Zips.North),2)+POW((4760-Zips.West)),2)

Any suggestions to speed this guy up? 

yeah, do you really need ALL the columns returned?

also, if you can, offload the calculations to PHP*, like the POW() 
function...


grab the data from MYSQL and then use PHP to do the calcs...*


--
Leo G. Divinagracia III
[EMAIL PROTECTED]

z


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

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

JOIN based query problem (little help needed)

[Gigi Di Leo]

Hello list.

Please help me to refine a JOIN based query which I am not able to correct.
This the scenario (simplified).

Three tables:

Products
-
| id | description  |
-
| 01 | bread|
| 02 | milk |
| 03 | coffee   |
-

Purchases
-
| prod_id | quantities  |
-
|   01| 10  |
|   02| 5   |
|   03| 3   |
-

Sellings
-
| prod_id | quantities  |
-
|   01| 3   |
|   01| 1   |
|   02| 1   |
|   02| 1   |
-

This the query which I could figure out:

SELECT products.*, 
 SUM(purchases.quantity) AS purchases, 
 SUM(sellings.quantity) AS sellings,
 SUM(purchases.quantity)-SUM(sellings.quantity) AS inventory
FROM products
LEFT JOIN purchases ON products.id=purchases.prod_id
LEFT JOIN sellings ON products.id=sellings.prod_id
GROUP BY products.id
ORDER BY products.id

The query should return:
-
| prod_id | description  | purchases | sellings | inventory |
-
|01   | bread|10 |   4  | 6 |
|02   | milk |5  |   2  | 3 |
|03   | coffee   |3  |   0  | 3 |
-

This is what the query actually returns:
-
| prod_id | description  | purchases | sellings | inventory |
-
|01   | bread|20 |   4  |16 |
|02   | milk |10 |   2  | 8 |
|03   | coffee   |3  |   0  | 3 |
-

Thank you very much for your help.

Gigi


Here is the database dump if you wish to reproduce the scenario:

# phpMyAdmin MySQL-Dump
# version 2.3.2
# http://www.phpmyadmin.net/ (download page)
#
# Host: localhost
# Generato il: 17 Dic, 2002 at 04:45 PM
# Versione MySQL: 3.23.53
# Versione PHP: 4.2.3
# Database : `inventory`
# 

#
# Struttura della tabella `products`
#

CREATE TABLE products (
  id int(11) NOT NULL auto_increment,
  description varchar(64) NOT NULL default '',
  PRIMARY KEY  (id),
  KEY id (id)
) TYPE=MyISAM;

#
# Dump dei dati per la tabella `products`
#

INSERT INTO products VALUES (1, 'bread');
INSERT INTO products VALUES (2, 'milk');
INSERT INTO products VALUES (3, 'coffee');
# 

#
# Struttura della tabella `purchases`
#

CREATE TABLE purchases (
  prod_id int(11) NOT NULL default '0',
  quantity int(11) NOT NULL default '0',
  KEY prod_id (prod_id)
) TYPE=MyISAM;

#
# Dump dei dati per la tabella `purchases`
#

INSERT INTO purchases VALUES (1, 10);
INSERT INTO purchases VALUES (2, 5);
INSERT INTO purchases VALUES (3, 3);
# 

#
# Struttura della tabella `sellings`
#

CREATE TABLE sellings (
  prod_id int(11) NOT NULL default '0',
  quantity int(11) NOT NULL default '0',
  KEY prod_id (prod_id)
) TYPE=MyISAM;

#
# Dump dei dati per la tabella `sellings`
#

INSERT INTO sellings VALUES (1, 3);
INSERT INTO sellings VALUES (1, 1);
INSERT INTO sellings VALUES (2, 1);
INSERT INTO sellings VALUES (2, 1);

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

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

Segmentation fault when using MySQL++

[Leo Chow]

Hi all,
 
I am now writing an C++ application with the use of
MySQL. When I follow the tutorial in MySQL++ manual,
the application go into segmentation fault during
execution. What's the mistake I have made?

The followings are the codes and the error messages
during execution.
 
Codes:
#include iostream
#include iomanip
#include sqlplus.hh

using namespace std;
 
main()
{
  cout  Zeroth  endl; 
  
  Connection con(db, host, name, password);
  cout  First  endl;
   
  Query query = con.query();
  cout  Second  endl;
  
  query  select * from test;
  cout  Third  endl;
  
  Result res = query.store();
  cout  Fourth  endl;
  
  cout  Query:   query.preview()  endl;
  cout  Fifth  endl;
}

Error Message during Execution:
Zero
First
Second
Third
Segmentation fault

And the information from gdb about segmentation fault
is follow:
#0  0x4005f42f in SQLQuery::reset (this=0xbfffef48)
at sql_query1.hh:30
30  if (n = size()) insert(end(),(n+1) -
size(), );

I have tried both Red Hat 8.0 with g++ 3.2, MySQL
4.0, sqlplus 1.7.9, and Red Hat 7.3 with g++ 2.96,
MySQL 3.23, sqlplus 1.7.9

Thanks so much!

Regards, 
Leo

__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

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

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

Inventory Query

[Gigi Di Leo]

Hello list.

Could you please suggest me a one-line query to solve a problem of inventory ?

Three table:

Products
-
| id | description  |
-
| 01 | bread|
| 02 | milk |
| 03 | coffee   |
-

Purchases
-
| prod_id | quantities  |
-
|   01| 10  |
|   02| 5   |
|   03| 3   |
-

Sellings
-
| prod_id | quantities  |
-
|   01| 3   |
|   01| 1   |
|   02| 1   |
|   02| 1   |
-

The query should return
-
| prod_id | description  | purchases | sellings | inventory |
-
|01   | bread|10 |   4  | 6 |
|02   | milk |5  |   2  | 3 |
|03   | coffee   |3  |   0  | 3 |
-

Thank you very much for your help.

Gigi


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

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

R: Inventory Query

[Gigi Di Leo]

Thank you for your attention, Adolfo.

Sorry, it doesn't work.
It looks like quantities are added more times into the SUM function.

gigi

 -Messaggio originale-
 Da: Adolfo Bello [mailto:[EMAIL PROTECTED]]
 Inviato: lunedì 16 dicembre 2002 19.04
 A: Gigi Di Leo; [EMAIL PROTECTED]
 Oggetto: RE: Inventory Query
 
 
 Try this:
 
 SELECT t1.id AS prod_id, t1.description AS
 Description,SUM(t2.quantities) AS Purchases,
 SUM(t3.quantities) AS Sellings, (Purchases-Sellings) AS Inventory
 FROM Products t1 INNER JOIN Purchases t2 ON t1.id=t2.prod_id
 INNER JOIN Sellings t3 ON t1.id=t3.prod_id
 GROUP BY t1.id,t1.description
 
 
 
  -Original Message-
  From: Gigi Di Leo [mailto:[EMAIL PROTECTED]] 
  Sent: Monday, December 16, 2002 12:03 PM
  To: '[EMAIL PROTECTED]'
  Subject: Inventory Query
  
  
  Hello list.
  
  Could you please suggest me a one-line query to solve a 
  problem of inventory ?
  
  Three table:
  
  Products
  -
  | id | description  |
  -
  | 01 | bread|
  | 02 | milk |
  | 03 | coffee   |
  -
  
  Purchases
  -
  | prod_id | quantities  |
  -
  |   01| 10  |
  |   02| 5   |
  |   03| 3   |
  -
  
  Sellings
  -
  | prod_id | quantities  |
  -
  |   01| 3   |
  |   01| 1   |
  |   02| 1   |
  |   02| 1   |
  -
  
  The query should return
  
 -
  | prod_id | description  | purchases | sellings | 
 inventory |
  
 -
  |01   | bread|10 |   4  |   
   6 |
  |02   | milk |5  |   2  |   
   3 |
  |03   | coffee   |3  |   0  |   
   3 |
  
 -
  
  Thank you very much for your help.
  
  Gigi
  
  
  
 -
  Before posting, please check:
 http://www.mysql.com/manual.php   (the manual)
 http://lists.mysql.com/   (the list archive)
  
  To request this thread, e-mail [EMAIL PROTECTED]
  To unsubscribe, e-mail 
  [EMAIL PROTECTED]
  Trouble unsubscribing? Try: 
http://lists.mysql.com/php/unsubscribe.php
 
 
 
 


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

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

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

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

Re: Database Structure

[Leo Przybylski]

Stephanie,

Connector/J takes advantage of client/server architecture over TCP. This
means that the MySQL server could exist on one machine (a server), and
the client application(s) can connect to it via TCP and Connector/J from
anywhere that has a TCP route to the MySQL server.

If Connector/J is being used in the application then the answer is, no,
you do not have to install MySQL on the client machine unless the client
machine is also the server machine.

-Leo
On Thu, 2002-10-10 at 05:59, Stephanie Piet wrote:
 Does anyone know if there's a way in MySQL have the same functionality
 without installing the whole program on a users machine? I'm using a Java
 program along with Connector/J and a MySQL DB. We are trying to make it so
 the user doesn't have to install MySQL everytime they want to install the
 program on their machine.
 
 
 Thanks,
 Stephanie
 
 
 -
 Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before
 posting. To request this thread, e-mail [EMAIL PROTECTED]
 
 To unsubscribe, send a message to the address shown in the
 List-Unsubscribe header of this message. If you cannot see it,
 e-mail [EMAIL PROTECTED] instead.
 


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

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

Database documentation

[Gigi Di Leo]

Hello list.

At the end of a db programming job (using php) I have been asked for a document 
describing in details the structure of a MySQL database.
The purpose of the document is to make other DB people able to manage and interface 
the database (I can figure out that there is also the intent to impress the customer 
by graphic and heavy paper).
Could you please suggest me some source where I could learn about the best standard 
methods to describe a DB structure ?
Is there any tool which is able to produce this kind of document semi/automatically ?

gg


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

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

Query problem

[Gigi Di Leo]

Hello list.

I have to interface a database, which I cannot modify because it is maintained 
somewher else, where there are two tables:

BOOKS
author_code
publisher_code
book_title

BASIC_DATA
code
description
record_type

In the table BASIC_DATA records are classified on the flag value:
flag=1 - record is about Authors
flag=2 - record is about Publishers

Could you please suggest me the best query syntax to get BOOKS' data with Authors and 
Publishers decoded ?

Thank you very much for your help.

Gigi Di Leo


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

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

Query problem

[Gigi Di Leo]

Hello list.

I have to interface a database, which I cannot modify because it is maintained 
somewher else, where there are two tables:

BOOKS
author_code
publisher_code
book_title

BASIC_DATA
code
description
record_type

In the table BASIC_DATA records are classified on the flag value:
flag=1 - record is about Authors
flag=2 - record is about Publishers

Could you please suggest me the best query syntax to get BOOKS' data with Authors and 
Publishers decoded ?

Thank you very much for your help.

Gigi Di Leo


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

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

R: Query problem

[Gigi Di Leo]

I apologize to everybody, I wasn't very clear submitting my problem - Italian - 
English :-)

I try to give you an example:

table BOOKS
+-+++
| author_code | publisher_code | book_title |
+-+++
| 1   |   2| Title 01   |
| 3   |   4| Title 02   |
| 5   |   6| Title 03   |
| 7   |   8| Title 04   |
| 9   |  10| Title 05   |
|11   |  12| Title 06   |
+-+++

table BASIC_DATA (ordered by record_type): contains Authors and Publishers 
+--+---+-+
| code | description   | record_type |
+--+---+-+
|  1   | gigi  |  1  |
|  3   | andrea|  1  |
|  5   | marcello  |  1  |
|  7   | antonio   |  1  |
|  9   | rosa  |  1  |
| 11   | angelo|  1  |
|  2   | mc graw hill  |  2  |
|  4   | jsoft |  2  |
|  6   | apogeo|  2  |
|  8   | microsoft press   |  2  |
| 10   | mondadori |  2  |
| 12   | acme publisher|  2  |
+--+---+-+

In BASIC_DATA records with record_type = 1 are about Authors, records with record_type 
= 2 are about Publishers.

The query I am looking for should return:

+-++--+
| book_title  | author | publisher|
+-++--+
| Title 01| gigi   | mc graw hill |
| Title 02| andrea | jsoft|
| Title 03| marcello   | apogeo   |
| Title 04| antonio| microsoft press  |
| Title 05| rosa   | mondadori|
| Title 06| angelo | acme publisher   |
+-++--+

I hope this helps you to better understand my problem.
If I could touch the database I would split BASIC_DATA into two different tables 
AUTHORS and PUBLISHERS. But I cannot touch the DB structure.

Gigi

-Messaggio originale-
Da: Jed Verity [mailto:[EMAIL PROTECTED]]
Inviato: venerdì 5 luglio 2002 20.16
A: Gigi Di Leo; '[EMAIL PROTECTED]'
Oggetto: Re: Query problem


Hello, Gigi,

By decoded, do you mean that you want the words Author and Publisher
to appear in your table instead of 1 and 2? And you can't create or
modify tables? Short of replacing the codes in the columns, it seems to me
that you'd need to have a decode table. Something like:

+--+---+
| code | type  |
+--+---+
| 1| Author|
+--+---+
| 2| Publisher |
+--+---+

Right? Without this, or without inserting the actual values, or without
running conditionals in PHP or whatever environment you're accessing the
data in, you might be stuck. Maybe someone else has an idea...

HTH,
Jed

I liked it when Gigi Di Leo wrote this to me:

 Hello list.
 
 I have to interface a database, which I cannot modify because it is maintained
 somewher else, where there are two tables:
 
 BOOKS
 author_code
 publisher_code
 book_title
 
 BASIC_DATA
 code
 description
 record_type
 
 In the table BASIC_DATA records are classified on the flag value:
 flag=1 - record is about Authors
 flag=2 - record is about Publishers
 
 Could you please suggest me the best query syntax to get BOOKS' data with
 Authors and Publishers decoded ?
 
 Thank you very much for your help.
 
 Gigi Di Leo
 
 
 -
 Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 
 

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

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

problem with mysql threads on freebsd

[Leo De Geer]

im not abel to get mysql to use more then one thread on my freebsd 4.5-stable 
any one having a klue that wrong?

regards leo
-- 
MVH C. Leo De Geer
www.dinsignal.com
www.ktv.se
www.teknikshoppen.nu

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

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

Re: problem with mysql threads on freebsd

[Leo De Geer]

On Tuesday 11 June 2002 08.49, Dicky Wahyu Purnomo wrote:
 On Tue, 11 Jun 2002 08:36:55 +0200

 Leo De Geer [EMAIL PROTECTED] wrote:
  im not abel to get mysql to use more then one thread on my freebsd
  4.5-stable any one having a klue that wrong?

 from what method / tools, you get this information ?

 try mysqladmin -p status

yast by putting load to it. and by using the top.
then i get al the load on the master pid 
-- 
MVH C. Leo De Geer
www.dinsignal.com
www.ktv.se
www.teknikshoppen.nu

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

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

Re: problem with mysql threads on freebsd

[Leo De Geer]

On Tuesday 11 June 2002 08.49, Dicky Wahyu Purnomo wrote:
 On Tue, 11 Jun 2002 08:36:55 +0200

 Leo De Geer [EMAIL PROTECTED] wrote:
  im not abel to get mysql to use more then one thread on my freebsd
  4.5-stable any one having a klue that wrong?

 from what method / tools, you get this information ?

 try mysqladmin -p status
the mysqladmin dets the result

Uptime: 51804  Threads: 2  Questions: 1757  Slow queries: 0  Opens: 48  Flush 
tables: 1  Open tables: 37 Queries per second avg: 0.03
-- 
MVH C. Leo De Geer
www.dinsignal.com
www.ktv.se
www.teknikshoppen.nu

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

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

Re: problem with mysql threads on freebsd

[Leo De Geer]

On Tuesday 11 June 2002 09.26, Alexander V Zubchenko wrote:
 Greetings!

 On Tue, 11 Jun 2002, Leo De Geer wrote:
  On Tuesday 11 June 2002 08.49, Dicky Wahyu Purnomo wrote:
   On Tue, 11 Jun 2002 08:36:55 +0200
  
   Leo De Geer [EMAIL PROTECTED] wrote:
im not abel to get mysql to use more then one thread on my freebsd
4.5-stable any one having a klue that wrong?
  
   from what method / tools, you get this information ?

 Good question.

   try mysqladmin -p status
 
  yast by putting load to it. and by using the top.
  then i get al the load on the master pid

 Sorry, but, afaic, You don't understand what threads are. Thread is
 subprogram, which is executed simultaneously with main program (main
 thread) in _same_ process. PID is process parameter and additional
 threads don't create additional PID's. Multithreading,
 in fact, allow You to not create additional processes (which is
 resource-consuming task), but do parallel computations inside _one_
 process. So master pid _must_ take all load, if it act as
 multithreaded program.

 With respect,

 Alexander V Zubchenko,E-Mail: [EMAIL PROTECTED]
 System Administrator, WWW: http://www.hermes-comp.zp.ua/
 Hermes-comp,
 Ukraine,
 Zaporizhzhya,
 Geroev Stalingrada 50
 phone/fax: +380 612 64-19-72


 To Unsubscribe: send mail to [EMAIL PROTECTED]
 with unsubscribe freebsd-questions in the body of the message
but in my case its not good to have it that way im having one dedikated dual 
server for the sql and now im yust using one cpu for the sql questions. i 
nead it to start sub conections to the sql to serv all my conections good. 
-- 
MVH C. Leo De Geer
www.dinsignal.com
www.ktv.se
www.teknikshoppen.nu

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

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

Re: problem with mysql threads on freebsd

[Leo De Geer]

On Tuesday 11 June 2002 09.57, Alexander V Zubchenko wrote:
 Greetings!

 On Tue, 11 Jun 2002, Leo De Geer wrote:
  but in my case its not good to have it that way im having one dedikated
  dual server for the sql and now im yust using one cpu for the sql
  questions. i nead it to start sub conections to the sql to serv all my
  conections good.

 In such case provide, please additional info. AFAIU, You want to use
 both cpu for sql, in other words, optimal use of Your server. Than, do
 You recompile (or compile) kernel with SMP support (afaik, this is
 only arch for multicpu systems, supported by fbsd)? You can b sure,
 that if mysql will run separate process for each request, it will
 slowdown perfomance, but never increase it. In fact, balancing load on
 many cpus is OS task. And multithreaded processes _must_ b processed
 similarly to multiprocess environment (but i don't know, how exactly
 fbsd smp-support built). Check Your kernel config
 /usr/src/sys/i386/conf/name. You may find also this name
 interesting:
 LINT - list [almost?] all options recognized in config-file.

 With respect,

 Alexander V Zubchenko,E-Mail: [EMAIL PROTECTED]
 System Administrator, WWW: http://www.hermes-comp.zp.ua/
 Hermes-comp,
 Ukraine,
 Zaporizhzhya,
 Geroev Stalingrada 50
 phone/fax: +380 612 64-19-72
the kernel is runing both cpu. and the balancing is working good on everything 
but the sql!

i have on my linux sql server aproximatly 100 simultanius conections that du 
you think that the standard memory size of 128 do you think i nead to put it 
up to 256 meg in the kernel
-- 
MVH C. Leo De Geer
www.dinsignal.com
www.ktv.se
www.teknikshoppen.nu

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

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

Re: problem with mysql threads on freebsd

[Leo De Geer]

On Tuesday 11 June 2002 10.48, Simon Green wrote:
 We are ruing a box with freeBSD and MySQL with twin processors
 It looked like it was using only on processor but we decided that it was
 the way FreeBSD's threads work?
 There seems to be two problems with ruing MySQL on FreeBSD.
 One: Seems not to use both processors (on twin system)
 Two: All ways a load on 1 even when the system in not at hi load (seem to
 go down when it is!)

 What is up with freeBSD or MySQL.

 Simon

 -Original Message-
 From: Leo De Geer [mailto:[EMAIL PROTECTED]]
 Sent: 11 June 2002 09:35
 To: Alexander V Zubchenko
 Cc: Dicky Wahyu Purnomo; [EMAIL PROTECTED];
 [EMAIL PROTECTED]
 Subject: Re: problem with mysql threads on freebsd

 On Tuesday 11 June 2002 09.57, Alexander V Zubchenko wrote:
  Greetings!
 
  On Tue, 11 Jun 2002, Leo De Geer wrote:
   but in my case its not good to have it that way im having one dedikated
   dual server for the sql and now im yust using one cpu for the sql
   questions. i nead it to start sub conections to the sql to serv all my
   conections good.
 
  In such case provide, please additional info. AFAIU, You want to use
  both cpu for sql, in other words, optimal use of Your server. Than, do
  You recompile (or compile) kernel with SMP support (afaik, this is
  only arch for multicpu systems, supported by fbsd)? You can b sure,
  that if mysql will run separate process for each request, it will
  slowdown perfomance, but never increase it. In fact, balancing load on
  many cpus is OS task. And multithreaded processes _must_ b processed
  similarly to multiprocess environment (but i don't know, how exactly
  fbsd smp-support built). Check Your kernel config
  /usr/src/sys/i386/conf/name. You may find also this name
  interesting:
  LINT - list [almost?] all options recognized in config-file.
 
  With respect,
 
  Alexander V Zubchenko,  E-Mail: [EMAIL PROTECTED]
  System Administrator,   WWW: http://www.hermes-comp.zp.ua/
  Hermes-comp,
  Ukraine,
  Zaporizhzhya,
  Geroev Stalingrada 50
  phone/fax: +380 612 64-19-72

 the kernel is runing both cpu. and the balancing is working good on
 everything
 but the sql!

 i have on my linux sql server aproximatly 100 simultanius conections that
 du

 you think that the standard memory size of 128 do you think i nead to put
 it

 up to 256 meg in the kernel
the problen nr 2 i dont have but the server we have dont handle the load on 
one cpu. at the moment we are runing the sql on a linux instead (same 
hardwere) but i sucks. alla auter servers is freebsd and they run mutch beter

regards leo
-- 
MVH C. Leo De Geer
www.dinsignal.com
www.ktv.se
www.teknikshoppen.nu

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

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

Re: problem with mysql threads on freebsd

[Leo De Geer]

On Tuesday 11 June 2002 16.30, Dan Nelson wrote:
 In the last episode (Jun 11), Leo De Geer said:
  im not abel to get mysql to use more then one thread on my freebsd
  4.5-stable any one having a klue that wrong?

 Do not assume that the way Linux manages threads is the only one.
 Mysql on FreeBSD does create threads; they are just not visible via top
 (in fact Linux is the only OS that shows threads in top).  FreeBSD's
 threads system, however will not put those threads on multiple CPUs.
 You can work around this by building the mysql port with LinuxThreads:

 cd /usr/ports/databases/mysql323-server
 make WITH_LINUXTHREADS=yes

 This is only useful if your system is dedicated to mysql, though.  If
 it is a combination webserver/database, the other CPU will be busy
 enough serving webpages, and FreeBSD's regular threads will do fine.
thanks that is the thing i have looking for.

regards leo
-- 
MVH C. Leo De Geer
www.dinsignal.com
www.ktv.se
www.teknikshoppen.nu

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

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

Subqueries

[Leo Przybylski]

Hello all,

Does anyone know if MySQL can do subqueries?

I am trying to provide a SELECT subquery to an IN clause and I am getting 
errors. Is this possible?

-Leo

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

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

SV: problem with mysql3.23.49 server under freebsd 4.5

[Leo De Geer]

sql,query

I cant get the server to starts more than one thread. I have installed
the server from the port collections and im running freebsd 4.5 stable

 

Regards leo de geer




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

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

RE: Faliue: Write huge binary data to blob Field of mysql use jdbc

[leo li]

Thanks for your reply.

Use database to restore file is the requrement of our project ,So I must 
find the way of problem.
As you said ,I trust the problem is beacuse of mysql protocol that is the 
filed must be send as a single packet. I have same test in postgresql ,In 
postgresql the blob type is big object,with the same paramete (16 megs 
stack size)of jvm ,I can send 50 megs file to database! 
I think the mysql may be improve on this point.



From: Mark Matthews [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: RE: Faliue: Write huge binary data to blob Field of mysql use 
jdbc
Date: Fri, 21 Sep 2001 08:14:45 -0500

Hi, I'm the author of the JDBC driver. I'm not sure what the problem is, 
but
I am going to try and talk you out of storing multi-megabytes as blobs.
Here's whyIn most cases it is more overhead than it is worth. If 
you're
going to be serving the data from these blobs from some other type of 
server
(HTTP/NFS, etc), then you've more than doubled your overhead with the
over-the-wire costs.  RDBMS systems are not optimized for storing and
retrieving large binary data, filesystems are.

What you probably want to do is generate some unique identifier for the 
file
and store it on a filesystem that the software you are writing has access
to. Use MySQL to store metadata about these files (their identifier, 
author,
revision history, etc), and then use standard ways of providing the file
(http/nfs/smb, etc). You will find this much more performant.

If you still want to store large files in BLOBs, I would look at 
increasing
your JVM heap size (by default it's around 16megs, the VM won't allocate
more memory than that), using the -Xmx switch (see your JDK 
documentation).
Because of the way the MySQL protocol works, the entire BLOB must be 
created
in memory as a single packet to be sent to the server, so the driver needs
at least the amount of memory as the size of your BLOB, plus some overhead
for escaping binary characters, plus overhead for the driver itself.



 -Mark

   We are making a project about document manage  ,I use

   org.gjt.mm.mysql

   as jdbc to communicate with mysql. I can succeed in writing

   the binary data

   (the size about 14M) to database ,but when I write the binary

   data that's

   size exceed 20M I get a Exception. Please help me! Thanks!





_
Äú¿ÉÒÔÔÚ MSN Hotmail Õ¾µã http://www.hotmail.com/cn Ãâ·ÑÊÕ·¢µç×ÓÓʼþ


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

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

RE: Faliue: Write huge binary data to blob Field of mysql use jdbc

[leo li]

Thanks!
But my setting is max_allowed_packet=50M,I have changed the setting 
before I send the help. Someone tell me It may be the jdbc problem or the 
stack value of jvm is too small.
I'm very puzzle ! 


From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: 'Àî Çà' [EMAIL PROTECTED]
CC: mysql mailing list (E-Mail) [EMAIL PROTECTED]
Subject: RE: Faliue: Write huge binary data to blob Field of mysql use 
jdbc
Date: Thu, 20 Sep 2001 16:29:27 +0200

Hi ? ?

  -Original Message-
  From: ? ? [mailto:[EMAIL PROTECTED]]
  Sent: Wednesday, September 19, 2001 12:12 PM
  To: [EMAIL PROTECTED]
  Subject: Faliue: Write huge binary data to blob Field of
  mysql use jdbc
 
 
  Hi all:
 
We are making a project about document manage  ,I use
  org.gjt.mm.mysql
  as jdbc to communicate with mysql. I can succeed in writing
  the binary data
  (the size about 14M) to database ,but when I write the binary
  data that's
  size exceed 20M I get a Exception. Please help me! Thanks!

try setting
'set-variable=max_allowed_packet=20M'
in my.cnf

(take a look what's the current value with 'show variables')

Andy



_
Äú¿ÉÒÔÔÚ MSN Hotmail Õ¾µã http://www.hotmail.com/cn Ãâ·ÑÊÕ·¢µç×ÓÓʼþ


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

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

Re: Berkeley DBD and locking

[leo li]

I encounter the same problem . And I think the BDB take the table lock 
instead of row level lock.
To resolve the problem .You can use Innodb table. 

From: Monika Andr?Jönsson [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Berkeley DBD and locking
Date: Thu, 20 Sep 2001 11:28:18 +0200

Hello,

I have installed the Mysql Max version with DBD tables (3.23.40) and is
running the server on NT. I have a rather small database with lots of
tables (22) and liked the idea with transactions. This is my situation:
I have one application writing and reading data from my tables (using
transactions) and other applications that reads the data that is
committed. Now, I just found out that while one application is doing a
transaction the other applications can't even read data from the tables
and is just hanging in their database call. This is very unfortunate
since it can be hanging in the call for some time and until it is
released the program can't continue executing. I've read about the page
locking in DBD, is this the behaviour of that? It seems very weird that
one application must wait for another applications commit and not at
least get the last good data from the database. Why using transactions
in that case? I could just lock all tables, do my changes and then
release them, which in my case is unthinkable so I must use dirty data
and live with the inconsistency in the database.

Please, does anyone know of a workaround or a variable for the server or
something that can be done to ensure reading without hanging while using
transactions?

Very grateful for help,

/Monika A-J



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

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



_
Äú¿ÉÒÔÔÚ MSN Hotmail Õ¾µã http://www.hotmail.com/cn Ãâ·ÑÊÕ·¢µç×ÓÓʼþ


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

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

Innodb does not work with NT Max 3.23.39

[Leo Vanhatalo]

Hi!

I wanted to use row level locking. I modified the my.ini as follows :
#This File was made using the WinMySQLadmin 1.1 Tool
#7/10/2001 10:59:39 AM

[mysqld]
basedir=C:/mysql
datadir=C:/mysql/data
innodb_data_file_path = ibdata1:2000M;ibdata2:2000M
innodb_data_home_dir = e:\innodb\data
set-variable = innodb_mirrored_log_groups=1
innodb_log_group_home_dir = e:\innodb\logs
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=30M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_log_arch_dir = e:\innodb\arch
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=80M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50

[WinMySQLadmin]
Server=C:/mysql/bin/mysqld-max-nt.exe
user=system
password=manager
QueryInterval=10

---
But when I try to start the db with winmysqladmin, it does not start. No 
error is given. According innodb documentation starting the database should 
create innodb tablespaces, but nothing is created.
Mysql gives this error message :
ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)
---
Please help. And please send the message also directly to me because I am 
not in the list.

Leo
_
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.


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

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

Trying to compile/build MySQL on SUSEv7.0 running on S/390

[Leo McCarthy]

Hello,

The configure program doesnt recognize  my machine type. I am running
SuSe v7.0 on a S/390 mainframe. uname -m returns S390, uname -s returns
Linux and uname -p returns unknown. When I try to configure I get:

Checking host system type... Invalid configuration
`s390-unknown-linux-gnu': machine `s390-unknown' not recognized.

I tried adding --host=Linux to the configure command, but get similar
error and then it tells me I must specify Host type when using the
--no-verify option in ltconfig.

Is there a value I can plug in to get going? The other stuff configure
checks out seems OK.

Thanks for any help you can offer,

Leo McCarthy
Boston College Systems Services
Tel: 617 552-4629




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

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

How to put and get gziped file to/from mysql db

[Leo . BOROVINSKY]

Hello
I am looking for help how to put gziped plain data file to mysql DB
and then get it from there.
Every time I try OUTFILE to file and then gzip -d file name I got file corrupted
Any help will be appreciated

Leo





**
The information contained herein is confidential and is intended solely
for the addressee(s).  It shall not be construed as a recommendation to
buy or sell any security.  Any unauthorized access, use, reproduction,
disclosure or dissemination is prohibited.
Neither SOCIETE GENERALE nor any of its subsidiaries or affiliates
shall assume any legal liability or responsibility for any incorrect,
misleading or altered information contained herein. 
**

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

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

Re: Tuple length Question

[Leo Cambilargiu]

Hello Jason:

Thanks for your input.

I did think of this possibility; those hash tables MySQL has
available are certainly going to speed things up (if you
have the memory).

My mutlilanguage system could encorporate your idea; I will
give you a run down of how it works.

First you keep separate files with what are to be hash
entries of all _lines_ in the page, lets call this file
en-ints (it could be sp-ints for spanish).  The keys access
data which is language specific. This is because there is
lots of other text not in the database which appears in the
web page.

Suppose you have a header on the page H2Hello World/H2

en-ints could have an entry like "header = 'Hello World'"
sp-ints could have a similar entry "header = 'Olla Mondo'"
(my spanish is terrible)

You might write a script like (CGI.pm)
$obj-h2($language['header']);
This assumes %language is the hash loaded with data from
en-ints.

I am choosing this system because word for word translations
result in grammatical errors I do not want.

If you had one table with all attributes in all supported
languages (we would have a slight mess with (select * from
table_name) we could name the attributes according to the
language they represent.  The right attributes get accessed
when the CGI scrip loads ..-ints containing the language to
use because the key entries of ..-ints reflect the attribute
name change:)

You also might have to share attributes within the database
(like picture).  Having multiple tables creates a redundancy
of this data...  

My only concern in having tuples so long that I get other
unforseen side effects.  Also a limit in tuple size might
limit my ability to include additional languages.

Regards,

Leo Cambilargiu

Jason Landry wrote:
 
 Leo,
 
 There's another alternative that I found intriguing with MySQL that I
 haven't tried - but it might be a really good solution for you.  Mind you I
 haven't tried this, but I found the idea somewhat fascinating.
 
 Have you looked into the temporary table functionality of MySQL?
 
 Suppose you have n tables with languages like this:
 
 language_english
 language_french
 language_spanish
 language_german
 
 Now when you establish your connection to the database, do this:
 
 create temporary table language select * from lang_english // pick your
 lanuage in code
 
 Now the rest of you code can refer to "language" as a table -- and you can
 add as many languages as are needed modifying only the stub of code that
 initially selects the language.
 
 - Original Message -
 From: "Leo Cambilargiu" [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Sunday, March 18, 2001 8:30 AM
 Subject: Tuple length Question
 
  Hello All:
 
  I am looking into mysql as a web solution.  I am
  implementing a multilanguage scheme.
 
  I am considering separating the different languages by the
  following three methods.
 
  1.  Use different tables to hold different languages.
 
  2.  Use the same table with another key (lang).
 
  3.  Add extra attributes to a table and pick the ones
  containing the language I want.
 
  I am exploring possibility number 3 at the moment.
 
  My question is, how long can a Mysql myisam table tuple be?
 
  Currently I like the idea of increasing the number of
  attributes except for this  possible constraint.  I might
  have 6 to 10 languages supported max.  I'll start with 2.  I
  will have no more than 15 attributes total and possibly 5
  will be shared between languages.  This means i'll have to
  add another 10 attributes per language implemented.
 
  Thanks in advance.  Please email me at [EMAIL PROTECTED] or
  [EMAIL PROTECTED]
 
  Leo Cambilargiu
 
 
  -
  Before posting, please check:
 http://www.mysql.com/manual.php   (the manual)
 http://lists.mysql.com/   (the list archive)
 
  To request this thread, e-mail [EMAIL PROTECTED]
  To unsubscribe, e-mail
 [EMAIL PROTECTED]
  Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 

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

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

Tuple length Question

[Leo Cambilargiu]

Hello All:

I am looking into mysql as a web solution.  I am
implementing a multilanguage scheme.

I am considering separating the different languages by the
following three methods.

1.  Use different tables to hold different languages.

2.  Use the same table with another key (lang).

3.  Add extra attributes to a table and pick the ones
containing the language I want.

I am exploring possibility number 3 at the moment.

My question is, how long can a Mysql myisam table tuple be?

Currently I like the idea of increasing the number of
attributes except for this  possible constraint.  I might
have 6 to 10 languages supported max.  I'll start with 2.  I
will have no more than 15 attributes total and possibly 5
will be shared between languages.  This means i'll have to
add another 10 attributes per language implemented.

Thanks in advance.  Please email me at [EMAIL PROTECTED] or
[EMAIL PROTECTED]

Leo Cambilargiu


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

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

character sets error

[TM Safar Tibor Leo]

I work under WinNT with MySQLODBC cilent, and the mySQL 
server is installed to the linux server.
I receive an error message:
'can't initialize character set 21 (path: c:\mysql\\share\charsets)'.
I use the hungarian character set, and I try to put in the showed 
location the hungarian.conf file but the error message still appear.
What have I do to solve this problem?
Thanks!
Leo

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

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

character sets error

[TM Safar Tibor Leo]

Hi,

I work under WinNT with MySQLODBC cilent, and the mySQL 
server is installed to the linux server.
I receive an error message:
'can't initialize character set 21 (path: c:\mysql\\share\charsets)'.
I use the hungarian character set, and I try to put in the showed 
location the hungarian.conf file but the error message still appear.
What have I do to solve this problem?
Thanks!
Leo


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

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