Re: Best way to store numeric data?

[Michael Dykman]

Of course, all engineering is balancing the trade-offs.

The maximum positive value of a BIGINT is 9223372036854775807, which
suggests that it can store any 18 digit value accurately, which perhaps
solves the problem.  BIGINT, like DOUBLE, requires 8 bytes storage, so
you are neutral in that regard, but using BIGINT you are adding the CPU
overhead  of multiplying and dividing by 10^n on every store/fetch (not
to mention remembering to apply it everywhere).  Also, if the code
manipulating this data is C, C++, perl, PHP, Java, lisp or any other
commonly used language, it is very highly probable that the code is
using the IEEE double-precision format (in place since 1985), so any
precision you are worried about losing has already been lost long before
it gets to the SQL engine. If the code is manipulating the data in this
format, that is likely why all your numbers are coming up with exactly
that many digits.  

It is possible that the software is using a high-precision library;
check with the developers.. if it's off-the shelf software and it
doesn't mention super-precision as a feature, it's likely using the good
old 8 byte double precision (like 99.99% of all software written in the
last 20 years) and a MySQL DOUBLE will store exactly the value it has
been given.

FYI:  the float (IEEE single precision) only guarantees 8 (or 9?) digits
of accuracy if I remember my researches from earlier in the day.  MySQL
DECIMAL type serves as a wrapper for both float and double and will
choose the underlaying type depending on the the requested precision.

On Wed, 2005-02-02 at 22:54, Galen wrote:
> First, I'm storing all my data and I want it stored accurately. I don't 
> know that I will need quite this much precision in the long run, but 
> because I am working with highly detailed statistics generated from 
> hundreds of millions of measurements, I do not want to risk throwing 
> away information that could prove useful, as this table is only one 
> step of many.
> 
> Regarding the idea of storing as a bigint and simply multiplying the 
> value the appropriate amount, that's a possibility. It feels a bit 
> cumbersome, but would work well enough.
> 
> I'm still not completely clear on float vs double. My goal is decent 
> efficiency in storage and very good efficiency in sorting and such. It 
> seems like all numeric values want to be value(16,15) - meaning an 
> apparent excess of, oh, 12 places to the left of the decimal. It seems 
> like a waste.
> 
> -Galen
> 
> 
> On Feb 2, 2005, at 7:02 PM, Michael Dykman wrote:
> 
> > I stand corrected..  I thought I recalled that the IEEE for double
> > precision offered 18 digits of accuracy (been years since I looked at
> > it) but a little research shows me 15.
> >
> >  - michael dykman
> >
> > On Wed, 2005-02-02 at 14:02, Roger Baklund wrote:
> >> Michael Dykman wrote:
> >> [...]
> >>> The MySQL implementation also supports this optional
> >>> precision specification, but the precision value is used only to
> >>> determine storage size.
> >>
> >> Right. This means you can not have 15 decimals precision using DOUBLE:
> >>
> >> mysql> use test
> >> Database changed
> >> mysql> create table dtest(d double(18,15));
> >> Query OK, 0 rows affected (0.01 sec)
> >>
> >> mysql> insert into dtest values 
> >> (6.984789027653891),(39.484789039053891);
> >> Query OK, 2 rows affected (0.02 sec)
> >> Records: 2  Duplicates: 0  Warnings: 0
> >>
> >> mysql> select * from dtest;
> >> ++
> >> | d  |
> >> ++
> >> |  6.984789027653892 |
> >> | 39.484789039053894 |
> >> ++
> >> 2 rows in set (0.00 sec)
> >>
> >> Last digit is "wrong" in both test rows. Increasing precision does 
> >> not help:
> >>
> >> mysql> create table d2test(d double(18,16));
> >> Query OK, 0 rows affected (0.02 sec)
> >>
> >> mysql> insert into d2test values 
> >> (6.984789027653891),(39.484789039053891);
> >> Query OK, 2 rows affected (0.00 sec)
> >> Records: 2  Duplicates: 0  Warnings: 0
> >>
> >> mysql> select * from d2test;
> >> +-+
> >> | d   |
> >> +-+
> >> |  6.9847890276538909 |
> >> | 39.4847890390538940 |
> >> +-+
> >> 2 rows in set (0.00 sec)
> >>
> >> This is no error, it is the approximate data type at work... it simply
> >> can not store the exact value.
> > -- 
> >  - michael dykman
> >  - [EMAIL PROTECTED]
> >
> >
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> >
-- 
 - michael dykman
 - [EMAIL PROTECTED]


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

Re: Best way to store numeric data?

[Michael Dykman]

I stand corrected..  I thought I recalled that the IEEE for double
precision offered 18 digits of accuracy (been years since I looked at
it) but a little research shows me 15.

 - michael dykman

On Wed, 2005-02-02 at 14:02, Roger Baklund wrote:
> Michael Dykman wrote:
> [...]
> > The MySQL implementation also supports this optional
> > precision specification, but the precision value is used only to
> > determine storage size.
> 
> Right. This means you can not have 15 decimals precision using DOUBLE:
> 
> mysql> use test
> Database changed
> mysql> create table dtest(d double(18,15));
> Query OK, 0 rows affected (0.01 sec)
> 
> mysql> insert into dtest values (6.984789027653891),(39.484789039053891);
> Query OK, 2 rows affected (0.02 sec)
> Records: 2  Duplicates: 0  Warnings: 0
> 
> mysql> select * from dtest;
> ++
> | d  |
> ++
> |  6.984789027653892 |
> | 39.484789039053894 |
> ++
> 2 rows in set (0.00 sec)
> 
> Last digit is "wrong" in both test rows. Increasing precision does not help:
> 
> mysql> create table d2test(d double(18,16));
> Query OK, 0 rows affected (0.02 sec)
> 
> mysql> insert into d2test values (6.984789027653891),(39.484789039053891);
> Query OK, 2 rows affected (0.00 sec)
> Records: 2  Duplicates: 0  Warnings: 0
> 
> mysql> select * from d2test;
> +-+
> | d   |
> +-+
> |  6.9847890276538909 |
> | 39.4847890390538940 |
> +-+
> 2 rows in set (0.00 sec)
> 
> This is no error, it is the approximate data type at work... it simply 
> can not store the exact value.
-- 
 - michael dykman
 - [EMAIL PROTECTED]


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

Re: Pulling a database schema out of a unknown MySQL database?

[Michael Dykman]

The easiest route is to use mysqldump with the --no-data option.  it
will dump the full schema info including feign keys, etc.

 -- michael dykman


On Wed, 2005-02-02 at 13:31, Dan Stromberg wrote:
> I have a system with set of web pages that use PHP and a MySQL database.
> 
> Apparently the old webmaster has disappeared, and a new webmaster has
> been hired.  She needs to know the schema of the database.
> 
> So my question is: Is there a way of querying MySQL not for values of
> fields, but rather for the schema of the database?
> 
> Thanks!
-- 
 - michael dykman
 - [EMAIL PROTECTED]


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

RE: Relationship between 'table_cache' and 'max_connections'

[Mark]

> -Original Message-
> From: Dathan Pattishall [mailto:[EMAIL PROTECTED] 
> Sent: donderdag 3 februari 2005 1:21
> To: Mark; mysql@lists.mysql.com
> Subject: RE: Relationship between 'table_cache' and 'max_connections'
> 
> Max_connections relates to table_cache because they both use
> descriptors. Thus at least the sum of the 2 is used by mySQL 
> to set the ulimit of file descriptors higher then the default if 
> executed as a user that can change the value.

Thanks for the reply. So, with max_connections set to 512,
and having 256 table caches, I would need a minimum of
768 open files, right? (or x2 that, I read somewhere). Well,
with 7408 available, it would seem I am in the clear. :)

- Mark


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

RE: Relationship between 'table_cache' and 'max_connections'

[Dathan Pattishall]

Table_cache is a pool of file descriptors held open, so the over head of
opening table is not necessary since the open is cached. 

Max_connections relates to table_cache because they both use
descriptors. Thus at least the sum of the 2 is used by mySQL to set the
ulimit of file descriptors higher then the default if executed as a user
that can change the value.



DVP

Dathan Vance Pattishall http://www.friendster.com

 

> -Original Message-
> From: Mark [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, February 02, 2005 4:09 PM
> To: mysql@lists.mysql.com
> Subject: Relationship between 'table_cache' and 'max_connections'
> 
> I hope someone can clarify the relationship between 
> 'table_cache' and 'max_connections' (I use MySQL 4.0.23). The 
> manual says:
> 
>"table_cache is related to max_connections. For example, for
>200 concurrent running connections, you should have a table
>cache size of at least 200 * N, where N is the maximum
>number of tables in a join. You also need to reserve some
>extra file descriptors for temporary tables and files."
> 
> Does that mean, that when table_cache is set to, say, 256, 
> only 256 concurrent connections can be made? I mean, is it 
> restrictive for the amount of connections? It seems to say 
> so, but since this is also said to be a CACHE value, maybe not.
> 
> I have set table_cache to 256, max_connections to 512, and I 
> have an open_files_limit of 7408. Does that suffice?
> 
> Thanks,
> 
> - Mark
> 
> 
> --
> 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]

Relationship between 'table_cache' and 'max_connections'

[Mark]

I hope someone can clarify the relationship between 'table_cache' and
'max_connections' (I use MySQL 4.0.23). The manual says:

   "table_cache is related to max_connections. For example, for
   200 concurrent running connections, you should have a table
   cache size of at least 200 * N, where N is the maximum
   number of tables in a join. You also need to reserve some
   extra file descriptors for temporary tables and files."

Does that mean, that when table_cache is set to, say, 256, only 256
concurrent connections can be made? I mean, is it restrictive for the
amount of connections? It seems to say so, but since this is also said to
be a CACHE value, maybe not.

I have set table_cache to 256, max_connections to 512, and I have an
open_files_limit of 7408. Does that suffice?

Thanks,

- Mark


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

Re: Social Networking querys

[listsql listsql]

>i'm not sure who the original poster was
I'm Fasani, I work in a spanish telecommunications company on the
statistics department as a DBA/developer. I'm on this list because we
use mysql for the online statistics and I'm always reading this when I
have some free time.

Thanks all who commented on this, I 'm researching now because I'm
making something different that I want to build on, it's kind of
different from friendster.
I know that I will face several problems in making what I want to
achieve. But mysql is powerfull enough to be the DB engine.

I hope that this not become off topic, I will search more on link
analisys and social networking, if there is any other thing related
that is not regarding mysql please send me to my personal email and
not to all the list.
Best regards,
--
Martin F.
www.movil.be

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

use of embedded server with 4.1.9

[Joseph Garry]

I had written a windows app using the C api that used
the embedded server for 4.0.18, and it worked fine. Now I am trying to upgrade
to 4.1.9, and it is not working. When I hit the statement mysql_real_connect,
it gpf's. This behaviour is the same as what I experience when I try to run the
original app built for 4.0.18 against a db that was built with 4.1.9.

 

 

RE: embedded server for 4.1.9

[Joe Garry]

I put something out on the general forum. Perhaps I could try the
general list. Thanks for your help.

 

Joe Garry

Software Engineer 

US Biometrics

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 02, 2005 4:09 PM
To: Joe Garry
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: RE: embedded server for 4.1.9

 


Yep, you seem to have a good handle on the authentication issue. Have
you tried compiling a DEBUG version and tracing through the code? Maybe
you can catch the GPF when it happens and figure out what's messing up.
Since it's failing early (during a connection) this should be rather
easy to capture and debug. 

I don't know how many people on this list actually compile their own
servers let alone deal with the embedded version. You may have better
luck with someone on the GENERAL discussion list. (I CC:ed that list
with my response) 

Sorry, but compiling/building MySQL is not something I have done yet, I
defer to all the others with more experience. I am sure someone can help
you run this fault to ground. ;-) 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 


"Joseph Garry" <[EMAIL PROTECTED]> wrote on 02/02/2005 04:00:25
PM:

> Thanks for your response. From what I read, this should be a problem
if
> you attempt to connect a client built with earlier versions of the
> client libraries with a db built with 4.1.9. But in this case I have
> rebuilt the app with the client libraries for 4.1.9. Also, the app
> gpf's. If it were an authentication issue, I would expect that it
would
> simply fail to connect. As well, I have downloaded the source for
4.1.9,
> and have tried to run the test for the embedded server, and I am
getting
> similar results. H.
> 
> Joe Garry
> Software Engineer 
> US Biometrics
> 
> 
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, February 02, 2005 2:22 PM
> To: Joe Garry
> Cc: [EMAIL PROTECTED]
> Subject: Re: embedded server for 4.1.9
> 
> "Joseph Garry" <[EMAIL PROTECTED]> wrote on 02/02/2005 03:20:19
> PM:
> 
> > 
> > I had written a windows app using the C api that used the embedded 
> > server for 4.0.18, and it worked fine. Now I am trying to upgrade to
> > 4.1.9, and it is not working. When I hit the statement 
> > mysql_real_connect, it gpf's. This behaviour is the same as what I 
> > experience when I try to run the original app built for 4.0.18 
> > against a db that was built with 4.1.9.
> > Any ideas?
> > 
> 
> I can't tell you why it's gpf-ing but have to ask if you factored in
the
> 
> change in authentication methods that happened at version 4.1 into
your 
> design? You are all around that breaking point (working in both 
> directions).  There is a lot of information on that change in the
manual
> 
> and in this list's archive. Just my $.02
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 


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

RE: embedded server for 4.1.9

[SGreen]

Yep, you seem to have a good handle on the authentication issue. Have you 
tried compiling a DEBUG version and tracing through the code? Maybe you 
can catch the GPF when it happens and figure out what's messing up. Since 
it's failing early (during a connection) this should be rather easy to 
capture and debug. 

I don't know how many people on this list actually compile their own 
servers let alone deal with the embedded version. You may have better luck 
with someone on the GENERAL discussion list. (I CC:ed that list with my 
response)

Sorry, but compiling/building MySQL is not something I have done yet, I 
defer to all the others with more experience. I am sure someone can help 
you run this fault to ground. ;-)

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


"Joseph Garry" <[EMAIL PROTECTED]> wrote on 02/02/2005 04:00:25 PM:

> Thanks for your response. From what I read, this should be a problem if
> you attempt to connect a client built with earlier versions of the
> client libraries with a db built with 4.1.9. But in this case I have
> rebuilt the app with the client libraries for 4.1.9. Also, the app
> gpf's. If it were an authentication issue, I would expect that it would
> simply fail to connect. As well, I have downloaded the source for 4.1.9,
> and have tried to run the test for the embedded server, and I am getting
> similar results. H.
> 
> Joe Garry
> Software Engineer 
> US Biometrics
> 
> 
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, February 02, 2005 2:22 PM
> To: Joe Garry
> Cc: [EMAIL PROTECTED]
> Subject: Re: embedded server for 4.1.9
> 
> "Joseph Garry" <[EMAIL PROTECTED]> wrote on 02/02/2005 03:20:19
> PM:
> 
> > 
> > I had written a windows app using the C api that used the embedded 
> > server for 4.0.18, and it worked fine. Now I am trying to upgrade to
> > 4.1.9, and it is not working. When I hit the statement 
> > mysql_real_connect, it gpf's. This behaviour is the same as what I 
> > experience when I try to run the original app built for 4.0.18 
> > against a db that was built with 4.1.9.
> > Any ideas?
> > 
> 
> I can't tell you why it's gpf-ing but have to ask if you factored in the
> 
> change in authentication methods that happened at version 4.1 into your 
> design? You are all around that breaking point (working in both 
> directions).  There is a lot of information on that change in the manual
> 
> and in this list's archive. Just my $.02
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 

Fw: case select

[SGreen]

Always include the list in your responses. It will get you more results 
faster than if you have to wait on just me. - Shawn
- Forwarded by Shawn Green/Unimin on 02/02/2005 04:53 PM -

"Ferhat Bingol  webmail " <[EMAIL PROTECTED]> wrote on 02/02/2005 
03:55:36 PM:

> Hi,
> 
> This is a mode selection for a wind turbine measurement. 
> 
> This is the case I will check. I use php but do notnot take care of the 
> errors in it. I am just making a block diagram for my self now.
> 
> $SQL=âSELECT name,mean FROM f_ssh WHERE (Mode=ââ) â;
> 
> Result=mysql_query($SQL);
> 
> While ()
> {
> 
> 
> $pdate=date("YmdHis", strtotime("v[0] - 10 minutes"));
> $pnext=date("YmdHis", strtotime("v[0] + 10 minutes"));
> 
> 
> SQLp=âSELECT name, mean,mode FROM f_ssh WHERE name=$pdateâ;
>  SQLn=âSELECT name, mean,mode FROM f_ssh WHERE name=$ndateâ;
> 
>While()
>{
>   pdate=x[0];
>   pmean=x[1];
>   pmode=x[2];
>}
> 
> 
>While()
>{
>   ndate=y[0];
>   nmean=y[1];
>   nmode=y[2];
>}
> 
> if (pmode=2) & (nmode=6)
>{
>   if (v[1]<200) { new_mode=78;}
>   if (v[1]>200) { new_mode=4;}
>}
> if (pmode=6) & (nmode=2)
>{
>   if (v[1]<200) { new_mode=9;}
>   if (v[1]>200) { new_mode=5;}
>}
> }
> 
> 
> 
> > This is a multipart message in MIME format.
> > 
> > "Ferhat Bingol  webmail " <[EMAIL PROTECTED]> wrote on 
> 02/02/2005 
> > 03:31:00 PM:
> > 
> > > Hi all, 
> > > 
> > > I have case like this.
> > > 
> > > 
> > > 200312211750   14.65   0   14.65   14.65   6
> > > 200312211800   118.9   119.41   14.65   697.17 
> > > 200312211810   1324.1   122.6   697.17   1502.3   2
> > > 
> > > Last field is MODE. That case is at the middle of the database. But 
> > > these 3 are continues tripel. 
> > > 
> > > When MODE='' I need to know all the values of the previous and the 
> next 
> > > rows.
> > > 
> > > Is there a SQL statement I can use, or do I need to write a code?
> > > 
> > > Regards,
> > > Ferhat
> > > 
> > > Is there a way to do that 
> > > -- 
> > > 
> > > 
> > 
> > UNLESS you provide an "order" to your data, there is no concept 
> of "row 
> > before", or "row after" in SQL (excluding the CURSOR structure which 
> MySQL 
> > does not support, yet). Results are, unless explicitly sorted, 
> UNORDERED 
> > and are not guaranteed to appear in the same sequence even if you use 
> the 
> > same SQL statement against the same data.
> > 
> > You can sort your results on one or more columns by using the ORDER 
> BY 
> > clause. However it is a function of your database connection library 
> (not 
> > the database server) to handle the results in some semblance of a 
> > sequence.  That means that if you want to find a record in your query 
> > results then look at the records that occur just before and just 
> after, 
> > you will need to use a client-side recordset structure (what 
> programming 
> > language and connection library are you using?) to do that sort of 
> data 
> > navigation. 
> > 
> > There may also be other ways to approach this issue than through 
> recordset 
> > navigation. What problem(s) are you trying to solve?
> > 
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> > 
> > 
> > 
> 
> -- 
> 

RE: Why does dropping indexes takes such a long time?

[Tom Crimmins]

I guess this should be a reminder to everyone that your out of office
replies should not go to mailing lists :)

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
 

> -Original Message-
> From: Homam S.A. [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, February 02, 2005 2:56 PM
> To: Keith Ivey; mysql@lists.mysql.com
> Cc: Homam S.A.
> Subject: Re: Why does dropping indexes takes such a long time?
> 
> This is the reply I got when I posted the same message
> last week. I checked the list for hours and my message
> didn't show up. This is why I assumed it never made
> it. I have no idea what this auto-reply is saying. I
> tried the AltaVista bablefish with Spanish. It was the
> closest, but it gave me garbled English.
> 
> 
> 
>   -  The following text is an automated response
> to your message  -
> Sres.
> Debido a que me encuentro de vacaciones les informo
> que las 
> actividades seguidas por nuestro grupo de trabajo
> seran atendidas por 
> las siguientes personas:
> Contacto con clientes externos : Enrique Diaz.
> Modificacion y control de accesos y permisos a
> sistemas y bases de 
> datos : Enrique Diaz.
> Coordinacion de cambios y mantencion de la red :
> Mauricio Guajardo.
> Administracion de cambios en servidores de produccion
> : Mauricio 
> Guajardo.
> Supervision pauta diaria Operaciones : Enrique Diaz.
> Administracion de sistema de respaldo : Mauricio
> Guajardo.
> Saludos,
> Alvaro Avello
> Administrador de Red.
> Servinco S.A.
> 
> 

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

Re: Why does dropping indexes takes such a long time?

[Homam S.A.]

This is the reply I got when I posted the same message
last week. I checked the list for hours and my message
didn't show up. This is why I assumed it never made
it. I have no idea what this auto-reply is saying. I
tried the AltaVista bablefish with Spanish. It was the
closest, but it gave me garbled English.



  -  The following text is an automated response
to your message  -
Sres.
Debido a que me encuentro de vacaciones les informo
que las 
actividades seguidas por nuestro grupo de trabajo
seran atendidas por 
las siguientes personas:
Contacto con clientes externos : Enrique Diaz.
Modificacion y control de accesos y permisos a
sistemas y bases de 
datos : Enrique Diaz.
Coordinacion de cambios y mantencion de la red :
Mauricio Guajardo.
Administracion de cambios en servidores de produccion
: Mauricio 
Guajardo.
Supervision pauta diaria Operaciones : Enrique Diaz.
Administracion de sistema de respaldo : Mauricio
Guajardo.
Saludos,
Alvaro Avello
Administrador de Red.
Servinco S.A.



  -  Original message follows  -







--- Keith Ivey <[EMAIL PROTECTED]> wrote:

> Homam S.A. wrote:
> 
> > I have a non-primary-key index on a large MyISAM
> > table, and dropping the index takes a long time,
> in
> > addition to maxing out the CPU utilization in its
> > final 1/3 interval.
> > 
> > Why is that?
> 
> You asked the question last week, and several people
> answered. 
> Do you think the answer has changed since then?
> 
> -- 
> Keith Ivey <[EMAIL PROTECTED]>
> Smokefree DC
> http://www.smokefreedc.org
> Washington, DC
> 




__ 
Do you Yahoo!? 
Read only the mail you want - Yahoo! Mail SpamGuard. 
http://promotions.yahoo.com/new_mail 

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

Re: case select

[SGreen]

"Ferhat Bingol  webmail " <[EMAIL PROTECTED]> wrote on 02/02/2005 
03:31:00 PM:

> Hi all, 
> 
> I have case like this.
> 
> 
> 200312211750   14.65   0   14.65   14.65   6
> 200312211800   118.9   119.41   14.65   697.17 
> 200312211810   1324.1   122.6   697.17   1502.3   2
> 
> Last field is MODE. That case is at the middle of the database. But 
> these 3 are continues tripel. 
> 
> When MODE='' I need to know all the values of the previous and the next 
> rows.
> 
> Is there a SQL statement I can use, or do I need to write a code?
> 
> Regards,
> Ferhat
> 
> Is there a way to do that 
> -- 
> 
> 

UNLESS you provide an "order" to your data, there is no concept of "row 
before", or "row after" in SQL (excluding the CURSOR structure which MySQL 
does not support, yet). Results are, unless explicitly sorted, UNORDERED 
and are not guaranteed to appear in the same sequence even if you use the 
same SQL statement against the same data.

You can sort your results on one or more columns by using the ORDER BY 
clause. However it is a function of your database connection library (not 
the database server) to handle the results in some semblance of a 
sequence.  That means that if you want to find a record in your query 
results then look at the records that occur just before and just after, 
you will need to use a client-side recordset structure (what programming 
language and connection library are you using?) to do that sort of data 
navigation. 

There may also be other ways to approach this issue than through recordset 
navigation. What problem(s) are you trying to solve?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: performance on query with ORDER BY clause

[Dathan Pattishall]

This tells the optimizer to do a table scan. If you used INNODB it's
already sorted by the primary key since INNODB supports clustered
indexes. Doing a table scan on innodb is very slow due to it's MVCC
control.

It's going to take a long time.



DVP

Dathan Vance Pattishall http://www.friendster.com

 

> -Original Message-
> From: Marc Dumontier [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, February 02, 2005 12:02 PM
> To: mysql@lists.mysql.com
> Subject: performance on query with ORDER BY clause
> 
> Hi,
> 
> I have a simple query with an ORDER BY clause, and it's 
> taking forever to run on this table. I hope i've included all 
> relevent information...it might just be one of the4 server 
> variables which need adjustment.
> 
> the query is
> 
> SELECT SubmitId from BINDSubmit ORDER BY SubmitId
> 
> SubmitId is the primary Key, about 150,000 records table type 
> is INNODB
> 
> mysql> describe BINDSubmit;
> +-+-+--+-+
> -++
> | Field   | Type| Null | Key | 
> Default | Extra  |
> +-+-+--+-+
> -++
> | SubmitId| int(10) unsigned|  | PRI | 
> NULL| auto_increment |
> | BindId  | int(10) unsigned|  | MUL | 
> 0   ||
> | UserId  | int(10) unsigned|  | MUL | 
> 0   ||
> | Delegate| int(10) unsigned|  | MUL | 
> 0   ||
> | Visible | tinyint(1)  |  | | 
> 1   ||
> | Private | tinyint(1)  |  | | 
> 0   ||
> | Compressed  | tinyint(1)  |  | | 
> 0   ||
> | Verified| tinyint(1)  |  | | 
> 0   ||
> | Status  | tinyint(3) unsigned |  | MUL | 
> 0   ||
> | CurationType| tinyint(3) unsigned |  | | 
> 1   ||
> | RecordType  | tinyint(3) unsigned |  | MUL | 
> 0   ||
> | DateCreated | datetime|  | MUL | -00-00 
> 00:00:00 ||
> | DateLastRevised | datetime|  | MUL | -00-00 
> 00:00:00 ||
> | XMLRecord   | longblob|  | 
> | ||
> +-+-+--+-+
> -++
> 14 rows in set (0.00 sec)
> 
> mysql> select count(*) from BINDSubmit;
> +--+
> | count(*) |
> +--+
> |   144140 |
> +--+
> 1 row in set (5.09 sec)
> 
> mysql> explain select SubmitId from BINDSubmit ORDER BY SubmitId;
> ++---+---+-+-+
> --++-+
> | table  | type  | possible_keys | key | key_len | 
> ref  | rows   
> | Extra   |
> ++---+---+-+-+
> --++-+
> | BINDSubmit | index | NULL  | PRIMARY |   4 | 
> NULL | 404947 
> | Using index |
> ++---+---+-+-+
> --++-+
> 1 row in set (0.00 sec)
> 
> 
> 
> # The MySQL server
> [mysqld]
> port= 3306
> socket  = /tmp/mysql.sock
> skip-locking
> key_buffer = 128M
> max_allowed_packet = 40M
> table_cache = 256
> sort_buffer_size = 1M
> read_buffer_size = 1M
> myisam_sort_buffer_size = 64M
> thread_cache = 8
> query_cache_size= 16M
> # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4
> 
> # Uncomment the following if you are using InnoDB tables 
> innodb_data_home_dir = /usr/local/mysql/data/ 
> innodb_data_file_path = ibdata1:100M:autoextend 
> innodb_log_group_home_dir = /usr/local/mysql/data/ 
> innodb_log_arch_dir = /usr/local/mysql/data/ # You can set 
> .._buffer_pool_size up to 50 - 80 % # of RAM but beware of 
> setting memory usage too high innodb_buffer_pool_size = 512M 
> innodb_additional_mem_pool_size = 20M # Set .._log_file_size 
> to 25 % of buffer pool size innodb_log_file_size = 64M 
> innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 
> 1 innodb_lock_wait_timeout = 50
> 
> 
> 
> 
> Any help would be appreciated, so far query has been running 
> for 3000 seconds
> 
> Marc Dumontier
> 
> 
> --
> 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]

case select

[Ferhat Bingol webmail]

Hi all, 

I have case like this.


20031221175014.65   0   14.65   14.65   6
200312211800118.9   119.41  14.65   697.17   
2003122118101324.1  122.6   697.17  1502.3  2

Last field is MODE. That case is at the middle of the database. But 
these 3 are continues tripel. 

When MODE='' I need to know all the values of the previous and the next 
rows.

Is there a SQL statement I can use, or do I need to write a code?

Regards,
Ferhat

Is there a way to do that 
-- 


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

RE: Why does dropping indexes takes such a long time?

[Dathan Pattishall]

DVP

Dathan Vance Pattishall http://www.friendster.com

 

> -Original Message-
> From: Homam S.A. [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, February 02, 2005 11:50 AM
> To: mysql@lists.mysql.com
> Subject: Why does dropping indexes takes such a long time?
> 
> I have a non-primary-key index on a large MyISAM table, and 
> dropping the index takes a long time, in addition to maxing 
> out the CPU utilization in its final 1/3 interval.
> 
> Why is that

Almost all alters causes mysql to rebuild the table. So, it has to
remove the binary tree for that key and at the same time it fixes the
other keys. As a result it re-builds the table, take a look at your
datadir/database look for #sql that is the consistent copy that will get
swapped into the true table name.


> 
> In MS SQL Server for example, dropping the index is almost 
> instantaeous, unless it's clustered and you have other 
> indexes on the same table, because they have to be rebuilt. 
> But we're talking here about MyISAM tables with non-clustered indexes.
> 
> This defeats the whole idea of dropping an index on a table 
> before a large batch update.
> 
> 
> 
>   
> __
> Do you Yahoo!? 
> The all-new My Yahoo! - Get yours free! 
> http://my.yahoo.com 
>  
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 

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

Re: Why does dropping indexes takes such a long time?

[Keith Ivey]

Homam S.A. wrote:
I have a non-primary-key index on a large MyISAM
table, and dropping the index takes a long time, in
addition to maxing out the CPU utilization in its
final 1/3 interval.
Why is that?
You asked the question last week, and several people answered. 
Do you think the answer has changed since then?

--
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

performance on query with ORDER BY clause

[Marc Dumontier]

Hi,
I have a simple query with an ORDER BY clause, and it's taking forever 
to run on this table. I hope i've included all relevent information...it 
might just be one of the4 server variables which need adjustment.

the query is
SELECT SubmitId from BINDSubmit ORDER BY SubmitId
SubmitId is the primary Key, about 150,000 records
table type is INNODB
mysql> describe BINDSubmit;
+-+-+--+-+-++
| Field   | Type| Null | Key | 
Default | Extra  |
+-+-+--+-+-++
| SubmitId| int(10) unsigned|  | PRI | 
NULL| auto_increment |
| BindId  | int(10) unsigned|  | MUL | 
0   ||
| UserId  | int(10) unsigned|  | MUL | 
0   ||
| Delegate| int(10) unsigned|  | MUL | 
0   ||
| Visible | tinyint(1)  |  | | 
1   ||
| Private | tinyint(1)  |  | | 
0   ||
| Compressed  | tinyint(1)  |  | | 
0   ||
| Verified| tinyint(1)  |  | | 
0   ||
| Status  | tinyint(3) unsigned |  | MUL | 
0   ||
| CurationType| tinyint(3) unsigned |  | | 
1   ||
| RecordType  | tinyint(3) unsigned |  | MUL | 
0   ||
| DateCreated | datetime|  | MUL | -00-00 
00:00:00 ||
| DateLastRevised | datetime|  | MUL | -00-00 
00:00:00 ||
| XMLRecord   | longblob|  | 
| ||
+-+-+--+-+-++
14 rows in set (0.00 sec)

mysql> select count(*) from BINDSubmit;
+--+
| count(*) |
+--+
|   144140 |
+--+
1 row in set (5.09 sec)
mysql> explain select SubmitId from BINDSubmit ORDER BY SubmitId;
++---+---+-+-+--++-+
| table  | type  | possible_keys | key | key_len | ref  | rows   
| Extra   |
++---+---+-+-+--++-+
| BINDSubmit | index | NULL  | PRIMARY |   4 | NULL | 404947 
| Using index |
++---+---+-+-+--++-+
1 row in set (0.00 sec)


# The MySQL server
[mysqld]
port= 3306
socket  = /tmp/mysql.sock
skip-locking
key_buffer = 128M
max_allowed_packet = 40M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:100M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data/
innodb_log_arch_dir = /usr/local/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Any help would be appreciated, so far query has been running for 3000 
seconds

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

RE: Pulling a database schema out of a unknown MySQL database?

[Erich Beyrent]

> I have a system with set of web pages that use PHP and a MySQL database.
> 
> Apparently the old webmaster has disappeared, and a new webmaster has been
hired.  She needs to know the schema of the database.
> 
> So my question is: Is there a way of querying MySQL not for values of
fields, but rather for the schema of the database?
> 
> Thanks!

If you use DBDesigner by fabForce (open source), it will connect to your
MySQL database and reverse engineer it for you, building both the sql and a
graphical representation of the database.

Pretty cool stuff...  And it's free!

-Erich- 


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

Why does dropping indexes takes such a long time?

[Homam S.A.]

I have a non-primary-key index on a large MyISAM
table, and dropping the index takes a long time, in
addition to maxing out the CPU utilization in its
final 1/3 interval.

Why is that?

In MS SQL Server for example, dropping the index is
almost instantaeous, unless it's clustered and you
have other indexes on the same table, because they
have to be rebuilt. But we're talking here about
MyISAM tables with non-clustered indexes.

This defeats the whole idea of dropping an index on a
table before a large batch update.




__ 
Do you Yahoo!? 
The all-new My Yahoo! - Get yours free! 
http://my.yahoo.com 
 


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

RE: Pulling a database schema out of a unknown MySQL database?

[Tim Hayes]

Dan

Please try MYdbPAL for MySQL - www.it-map.com. It is FREE, and you can
reverse-engineer the schema; view, model, forward engineer etc.

Tim Hayes


-Original Message-
From: Dan Stromberg [mailto:[EMAIL PROTECTED]
Sent: 02 February 2005 12:31
To: mysql@lists.mysql.com
Cc: [EMAIL PROTECTED]
Subject: Pulling a database schema out of a unknown MySQL database?



I have a system with set of web pages that use PHP and a MySQL database.

Apparently the old webmaster has disappeared, and a new webmaster has
been hired.  She needs to know the schema of the database.

So my question is: Is there a way of querying MySQL not for values of
fields, but rather for the schema of the database?

Thanks!



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



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

RE: Pulling a database schema out of a unknown MySQL database?

[John Trammell]

I'd do something like 'mysqldump -d foo'; the -d option only dumps the
table defs.

> -Original Message-
> From: Dan Stromberg [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, February 02, 2005 12:31 PM
> To: mysql@lists.mysql.com
> Cc: [EMAIL PROTECTED]
> Subject: Pulling a database schema out of a unknown MySQL database?
> 
> 
> I have a system with set of web pages that use PHP and a 
> MySQL database.
> 
> Apparently the old webmaster has disappeared, and a new webmaster has
> been hired.  She needs to know the schema of the database.
> 
> So my question is: Is there a way of querying MySQL not for values of
> fields, but rather for the schema of the database?
> 
> Thanks!
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 

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

Re: Social Networking querys

[Balazs Rauznitz]

On Wed, Feb 02, 2005 at 10:55:41AM -0800, Bruce Douglas wrote:
> balazs...
> 
> cool... makes sense. then if that's the case, share away how's 
> the weather over in fl. i noticed on your site that you guys are fairly 
> small, makes for a good environment when getting work done!

One thing I definitely cannot share is the number of users, but I 
wouldn't say we're small at all.
 
> how does your app compare to ryze or linkedin, regarding the underlying 
> networking engine...
> 
> ryze appears to be a rather simple/straight-forward list of 
> lists/links approach, whereas linkedin has the ability to allow msgs to 
> only proceed between nodes if one is a 'trusted' node within the 
> chain

Honestly I haven't used the sites you mentioned. These are Multiply's 
capabilities:

There are different relationship types (about 50) that describe the 
relationship with your contacts. Such as father, fraternity brother, 
co-worker, etc... You can send messages and post content to people in 
your network described based on the relationship. For example "my 
family members", "my friends and their friends", you get the idea... 
The relationship graph engine can tell the shortest (most meaningful) 
connection or all possible connections between any two people. It can 
efficiently check if a piece of content is viewable by a user based on 
what permissions the creator of the content gave. It's very flexible, 
which is a great thing if you work in a startup and bizdev people throw 
curveballs at you all the time ;-)

Balazs


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

Re: Best way to store numeric data?

[Roger Baklund]

Keith Ivey wrote:
Roger Baklund wrote:
Galen wrote:
I've got a huge table going, and it's storing a load of numeric data. 
Basically, a percentage or single digit rank, one or two digits 
before the decimal and fifteen after, like this:

6.984789027653891
39.484789039053891
[snip]
You should not use FLOAT, it is an approximate type, not storing the 
exact values you enter, but an approximation:

But it's unlikely that numbers like that *are* exact values. Your advice 
would be applicable for prices or other situations where you're storing 
an exact number that has a decimal part, but these look more like 
measurements, so they're not exact in the first place, and any 
inexactness in calculations is fine as long as it's below the error in 
the measurements.
Well, only Galen knows for sure, but if he did not need 15 decimal 
digits, why did he say so? ;)

(My guess is you are right, by the way...)
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Best way to store numeric data?

[Roger Baklund]

Michael Dykman wrote:
[...]
The MySQL implementation also supports this optional
precision specification, but the precision value is used only to
determine storage size.
Right. This means you can not have 15 decimals precision using DOUBLE:
mysql> use test
Database changed
mysql> create table dtest(d double(18,15));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into dtest values (6.984789027653891),(39.484789039053891);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from dtest;
++
| d  |
++
|  6.984789027653892 |
| 39.484789039053894 |
++
2 rows in set (0.00 sec)
Last digit is "wrong" in both test rows. Increasing precision does not help:
mysql> create table d2test(d double(18,16));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into d2test values (6.984789027653891),(39.484789039053891);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from d2test;
+-+
| d   |
+-+
|  6.9847890276538909 |
| 39.4847890390538940 |
+-+
2 rows in set (0.00 sec)
This is no error, it is the approximate data type at work... it simply 
can not store the exact value.

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

Re: Pulling a database schema out of a unknown MySQL database?

[SGreen]

Dan Stromberg <[EMAIL PROTECTED]> wrote on 02/02/2005 01:31:17 PM:

> 
> I have a system with set of web pages that use PHP and a MySQL database.
> 
> Apparently the old webmaster has disappeared, and a new webmaster has
> been hired.  She needs to know the schema of the database.
> 
> So my question is: Is there a way of querying MySQL not for values of
> fields, but rather for the schema of the database?
> 
> Thanks!
> 
> 

If I want a quick-and-dirty, plain-text dump of the schema, I prefer using 
mysqldump with the -d option (no data).

mysqldump -q -Q -A -d --user=user_name --password -r dbschema.sql
(it should prompt you for the password)

see "mysqldump --help" for more options/details.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Social Networking querys

[Mark Papadakis]

We have implemented a system which uses 'swappable' in-memory data,
binary trees and hashtables to do things like:

o Return 'best' possible route from one person to another based on
weight of relationships between links
o Return 'shortest' possible route from one person to another
o Return total persons within N degrees of seperation
and so forth.

The in-memory data are flushed when memory is tight or when they get
too old, and get replaced with more data from InnoDB tables.
Essentially, it is quite rate to need to swap or load db data.given
that the data structures are not heavy in memory requirements.

Its writen in C++. Our tests with 10 million users (random set)
indicated less than 1 second  response time for any of the above
operations (when no mem-disk swaps take place ).

MarkP



On Wed, 2 Feb 2005 13:44:52 -0500, Balazs Rauznitz <[EMAIL PROTECTED]> wrote:
> On Wed, Feb 02, 2005 at 10:19:45AM -0800, Bruce Douglas wrote:
> > dathan...
> >
> > given that you work at friendster, aren't you kind of restricted from 
> > commenting on how one would go about creating/implementing this kind of 
> > system??
> 
> I thought about this too when describing my company's solution
> (http://multiply.com/ just for full disclosure). Considering that
> technology is just one of many keys to the success of a company, I
> think techies benefit much more from sharing some experiences with
> peers rather than keep everything secret and hope that the other guy
> screws up. Of course as long as help is reciprocal...
> 
> Balazs
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
Mark Papadakis
Head of R&D
Phaistos Networks, S.A

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

Re: Social Networking querys

[Bruce Douglas]

balazs...

cool... makes sense. then if that's the case, share away how's the weather 
over in fl. i noticed on your site that you guys are fairly small, makes for a 
good environment when getting work done!

how does your app compare to ryze or linkedin, regarding the underlying 
networking engine...

ryze appears to be a rather simple/straight-forward list of lists/links 
approach, whereas linkedin has the ability to allow msgs to only proceed 
between nodes if one is a 'trusted' node within the chain

-bruce


-Original Message-
From: Balazs Rauznitz <[EMAIL PROTECTED]>
Sent: Feb 2, 2005 10:44 AM
To: Bruce Douglas <[EMAIL PROTECTED]>
Cc: Dathan Pattishall <[EMAIL PROTECTED]>, mysql@lists.mysql.com
Subject: Re: Social Networking querys

On Wed, Feb 02, 2005 at 10:19:45AM -0800, Bruce Douglas wrote:
> dathan...
> 
> given that you work at friendster, aren't you kind of restricted from 
> commenting on how one would go about creating/implementing this kind of 
> system??
 
I thought about this too when describing my company's solution
(http://multiply.com/ just for full disclosure). Considering that 
technology is just one of many keys to the success of a company, I 
think techies benefit much more from sharing some experiences with 
peers rather than keep everything secret and hope that the other guy 
screws up. Of course as long as help is reciprocal...

Balazs



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

RE: question

[Dathan Pattishall]

If you want to do raw writes to the disk device yes you can. This option is 
available with innodb. Search on www.innodb.com it should be in section 15 (or 
11 I forgot) of the innodb manual.


DVP

Dathan Vance Pattishall http://www.friendster.com

 

> -Original Message-
> From: João Borsoi Soares [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, February 02, 2005 8:57 AM
> To: mysql@lists.mysql.com
> Subject: question
> 
> Is it possible to use for example /dev/hdc directly as my 
> innodb database file? Is it worth?
> 
> Thanks,
> Joao.
> mysql
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 

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

RE: Social Networking querys

[Dathan Pattishall]

DVP

Dathan Vance Pattishall http://www.friendster.com

 

> -Original Message-
> From: Bruce Douglas [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, February 02, 2005 10:20 AM
> To: Dathan Pattishall
> Cc: mysql@lists.mysql.com
> Subject: RE: Social Networking querys
> 
> dathan...
> 
> given that you work at friendster, aren't you kind of 
> restricted from commenting on how one would go about 
> creating/implementing this kind of system??

We are going to present at the mySQL conference, and let people know how
we created the storage engine not how we interact with our API via our
storage engine. I could talk to the business guys to see if we can open
up the API to allow other companies to use our sauce to run a social
network application-but it will require a partnership etc.

> 
> if you aren't and you have information to share, then we'd 
> appreciate hearing it!!!
> 
> regards,
> 
> bruce
> 
> 
> -Original Message-
> From: Dathan Pattishall <[EMAIL PROTECTED]>
> Sent: Feb 2, 2005 10:04 AM
> To: Balazs Rauznitz <[EMAIL PROTECTED]>, listsql listsql 
> <[EMAIL PROTECTED]>
> Cc: mysql 
> Subject: RE: Social Networking querys
> 
> 
> 
> DVP
> 
> Dathan Vance Pattishall http://www.friendster.com
> 
>  
> 
> > -Original Message-
> > From: Balazs Rauznitz [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, February 02, 2005 9:06 AM
> > To: listsql listsql
> 
> > I work at a social networking company. We store the social 
> network as 
> > an edge graph too, but do not use SQL to run queries on it. It'd be 
> > just way too slow in huge networks.
> > There's a custom application creted in-house written in C 
> to do that. 
> > Hope this was at least a little useful.
> > 
> > Also there's somebody from Friendster here, he might be 
> able to help 
> > you also.
> 
> We have our own storage engine that talks an API to our own 
> C++ server that stores the graph. Doing it with a pure myISAM 
> engine is possible but it's way to slow. Doing a self table 
> join or subselect is really the only way doing it, less you 
> want to have some application logic that issues 1000s of selects.
> 
> 
> > 
> > Balazs
> > 
> > 
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> > 
> > 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 
> 

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

Re: Best way to store numeric data?

[Keith Ivey]

Roger Baklund wrote:
Galen wrote:
I've got a huge table going, and it's storing a load of numeric data. 
Basically, a percentage or single digit rank, one or two digits before 
the decimal and fifteen after, like this:

6.984789027653891
39.484789039053891
[snip]
You should not use FLOAT, it is an approximate type, not storing the 
exact values you enter, but an approximation:
But it's unlikely that numbers like that *are* exact values. 
Your advice would be applicable for prices or other situations 
where you're storing an exact number that has a decimal part, 
but these look more like measurements, so they're not exact in 
the first place, and any inexactness in calculations is fine as 
long as it's below the error in the measurements.

--
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Pulling a database schema out of a unknown MySQL database?

[Balazs Rauznitz]

You mean "show databases", "show tables" and "describe $table_name" for 
every table in the database ?

Balazs

On Wed, Feb 02, 2005 at 10:31:17AM -0800, Dan Stromberg wrote:
> 
> I have a system with set of web pages that use PHP and a MySQL database.
> 
> Apparently the old webmaster has disappeared, and a new webmaster has
> been hired.  She needs to know the schema of the database.
> 
> So my question is: Is there a way of querying MySQL not for values of
> fields, but rather for the schema of the database?
> 
> Thanks!
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

Re: Social Networking querys

[Balazs Rauznitz]

On Wed, Feb 02, 2005 at 10:19:45AM -0800, Bruce Douglas wrote:
> dathan...
> 
> given that you work at friendster, aren't you kind of restricted from 
> commenting on how one would go about creating/implementing this kind of 
> system??
 
I thought about this too when describing my company's solution
(http://multiply.com/ just for full disclosure). Considering that 
technology is just one of many keys to the success of a company, I 
think techies benefit much more from sharing some experiences with 
peers rather than keep everything secret and hope that the other guy 
screws up. Of course as long as help is reciprocal...

Balazs


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

Re: Best way to store numeric data?

[Michael Dykman]

On Wed, 2005-02-02 at 12:46, Roger Baklund wrote:
> Galen wrote:
> > I've got a huge table going, and it's storing a load of numeric data. 
> > Basically, a percentage or single digit rank, one or two digits before 
> > the decimal and fifteen after, like this:
> > 
> > 6.984789027653891
> > 39.484789039053891
> > 
> > What is the most efficient way to store these values? I will be 
> > frequently sorting results by them or using math with them, so speed is 
> > important, but I also don't want to be wasteful of disk space as I 
> > currently have over three quarters of a million records, with more to come.
> 
> I think the most efficient way is to multiply all values with 100 before 
> you insert, and store as a bigint. Integer math operations are fast.
> 
> You should not use FLOAT, it is an approximate type, not storing the 
> exact values you enter, but an approximation:
> 
Actually, while it is true that MySQL uses the platform's C library for
the type, all modern libraries use the IEEE standards for floating
point.. in 'C' terms.  The approximate values you might see in a casual
query are the result of the default format mask used in MySQL.  Modern
floating point units (a subsystem of the CPU) perform basic math
(addition, multiplication) with very nearly the same speed as integer
types, so I would not worry about that too much..  

The MySQL 'DOUBLE' type accepts scale and precision as arguments when
created.

from http://dev.mysql.com/doc/mysql/en/numeric-types.html

The precision represents the number of significant decimal digits that
will be stored for values, and the scale represents the number of digits
that will be stored following the decimal point.
...
The FLOAT type is used to represent approximate numeric data types. The
SQL standard allows an optional specification of the precision (but not
the range of the exponent) in bits following the keyword FLOAT in
parentheses. The MySQL implementation also supports this optional
precision specification, but the precision value is used only to
determine storage size. A precision from 0 to 23 results in four-byte
single-precision FLOAT column. A precision from 24 to 53 results in
eight-byte double-precision DOUBLE column.


-- 
 - michael dykman
 - [EMAIL PROTECTED]


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

Pulling a database schema out of a unknown MySQL database?

[Dan Stromberg]

I have a system with set of web pages that use PHP and a MySQL database.

Apparently the old webmaster has disappeared, and a new webmaster has
been hired.  She needs to know the schema of the database.

So my question is: Is there a way of querying MySQL not for values of
fields, but rather for the schema of the database?

Thanks!



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

RE: Social Networking querys

[Bruce Douglas]

dathan...

given that you work at friendster, aren't you kind of restricted from 
commenting on how one would go about creating/implementing this kind of system??

if you aren't and you have information to share, then we'd appreciate hearing 
it!!!

regards,

bruce


-Original Message-
From: Dathan Pattishall <[EMAIL PROTECTED]>
Sent: Feb 2, 2005 10:04 AM
To: Balazs Rauznitz <[EMAIL PROTECTED]>, listsql listsql <[EMAIL PROTECTED]>
Cc: mysql 
Subject: RE: Social Networking querys



DVP

Dathan Vance Pattishall http://www.friendster.com

 

> -Original Message-
> From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, February 02, 2005 9:06 AM
> To: listsql listsql

> I work at a social networking company. We store the social 
> network as an edge graph too, but do not use SQL to run 
> queries on it. It'd be just way too slow in huge networks. 
> There's a custom application creted in-house written in C to 
> do that. Hope this was at least a little useful.
> 
> Also there's somebody from Friendster here, he might be able 
> to help you also.

We have our own storage engine that talks an API to our own C++ server
that stores the graph. Doing it with a pure myISAM engine is possible
but it's way to slow. Doing a self table join or subselect is really the
only way doing it, less you want to have some application logic that
issues 1000s of selects.


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

question

[João Borsoi Soares]

Is it possible to use for example /dev/hdc directly as my innodb
database file? Is it worth?

Thanks,
Joao.
mysql


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

RE: Social Networking querys

[Dathan Pattishall]

DVP

Dathan Vance Pattishall http://www.friendster.com

 

> -Original Message-
> From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, February 02, 2005 9:06 AM
> To: listsql listsql

> I work at a social networking company. We store the social 
> network as an edge graph too, but do not use SQL to run 
> queries on it. It'd be just way too slow in huge networks. 
> There's a custom application creted in-house written in C to 
> do that. Hope this was at least a little useful.
> 
> Also there's somebody from Friendster here, he might be able 
> to help you also.

We have our own storage engine that talks an API to our own C++ server
that stores the graph. Doing it with a pure myISAM engine is possible
but it's way to slow. Doing a self table join or subselect is really the
only way doing it, less you want to have some application logic that
issues 1000s of selects.


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

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

Re: [PHP] RE: Problem with SELECT SQL_CALC_FOUND_ROWS

[Richard Lynch]

Matt Babineau wrote:
>  Ok I installed PHP 4.3.10 and it still has not fixed the problem. If I
> remove the SQL_CALC_FOUND_ROWS from the query, it works no problems! This
> is
> very strange behavior!

Not really that strange, I think...

While you might want to read this:
http://us4.php.net/manual/en/faq.databases.php#faq.databases.upgraded

It sounds like your problem is more closely related to this:
http://bugs.php.net/bug.php?id=16906&edit=1

paying particular attention to this bit:
[1 Oct 2002 4:37am CEST] g at firebolt dot com

I was able to solve this bug by doing the following... granted, the bug
only existed for me once I had a table with > 9 rows.

Run a:
SET SQL_BIG_TABLES=1;

And MySQL will utilize more memory and be able to save the result set.

Optionally, when done, do this:

SET SQL_BIG_TABLES=0;

(tip courtesy of:
http://www.faqts.com/knowledge_base/view.phtml/aid/9824)

Keep in mind that when you do SQL_CALC_FOUND_ROWS MySQL has to do a BUNCH
more work and MySQL and PHP have to save a TON of temporary somewhere for
a large table.

So if your tables are large, or if you are doing a JOIN between two
moderate sized tables, it seems quite possible to me that
SQL_CALC_FOUND_ROWS will trigger a problem with running out of storage
space, when the same query without it won't trigger that problem.

-- 
Like Music?
http://l-i-e.com/artists.htm


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

Re: Social Networking querys

[Bruce Douglas]

nick...

just from curiousity.. is the code that you've alluded to available for public 
play. or is it yours/your company's?

thanks

bruce


-Original Message-
From: Nick Arnett <[EMAIL PROTECTED]>
Sent: Feb 2, 2005 9:08 AM
To: listsql listsql <[EMAIL PROTECTED]>
Cc: mysql 
Subject: Re: Social Networking querys

listsql listsql wrote:

>Since I read about Foaf [ http://www.foaf-project.org/ ], I become
>interested with Social Networking, 
>
What you're doing is often called link analysis -- searches on that term 
may yield more for you to chew on.  There are software tools and 
visualization tools for answering the kind of questions this data 
covers.  For the latter, Pajek 
(http://vlado.fmf.uni-lj.si/pub/networks/pajek/) is one of the most 
interesting.

More generally, you're storing a graph in a relational database.  
Searching on that subject will show you several approaches.

The way I deal with this is to create tables pretty much as you have, 
but instead of self-joins in MySQL, I load it all into a program that 
stores the graph in hashes, then uses recursion to get the kind of 
answers you want.  This is much, much, much faster than doing the same 
in the database, though it might become memory constrained if you have a 
big network.  In that case, my next step is to do all of the recursions 
and store the results in the database as pairs and their distances from 
one another.  Then it's a simple lookup.

If you do find a way to do this efficiently in MySQL, I'll be interested!

Nick


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



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

Re: Best way to store numeric data?

[Roger Baklund]

Galen wrote:
I've got a huge table going, and it's storing a load of numeric data. 
Basically, a percentage or single digit rank, one or two digits before 
the decimal and fifteen after, like this:

6.984789027653891
39.484789039053891
What is the most efficient way to store these values? I will be 
frequently sorting results by them or using math with them, so speed is 
important, but I also don't want to be wasteful of disk space as I 
currently have over three quarters of a million records, with more to come.
I think the most efficient way is to multiply all values with 100 before 
you insert, and store as a bigint. Integer math operations are fast.

You should not use FLOAT, it is an approximate type, not storing the 
exact values you enter, but an approximation:

mysql> use test
Database changed
mysql> create table ftest(f float);
Query OK, 0 rows affected (0.06 sec)
mysql> insert into ftest values (6.984789027653891),(39.484789039053891);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from ftest;
+-+
| f   |
+-+
| 6.98479 |
| 39.4848 |
+-+
2 rows in set (0.06 sec)
mysql> select round(f,15) from ftest;
++
| round(f,15)|
++
|  6.984788894653320 |
| 39.484790802001953 |
++
2 rows in set (0.01 sec)
Note that the values selected are not equal to the values inserted. What 
values you get will depend on the underlaying c library used by mysql, 
which again is plattform dependant.

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

Re: Social Networking querys

[Bruce Douglas]

we're going to need this kind of functionality as we're starting to architect 
an app that will be a kind of hybrid between a directory listing and 
ryze/linkedin, so we're going to need the ability to do the foaf/node links. we 
were actually hoping that someone had already produced a kind of 'open source' 
algorithm describing/explaining/demonstrating how this process can be 
implemented!!!

yeah.. i know.. but it's nice to dream!!

anybody interested in sharing ideas!!!

-bruce


-Original Message-
From: Balazs Rauznitz <[EMAIL PROTECTED]>
Sent: Feb 2, 2005 9:33 AM
To: Bruce Douglas <[EMAIL PROTECTED]>
Cc: listsql listsql <[EMAIL PROTECTED]>, mysql 
Subject: Re: Social Networking querys

On Wed, Feb 02, 2005 at 09:20:28AM -0800, Bruce Douglas wrote:
> hi...
> 
> i'm not sure who the original poster was... but, there is also an open source 
> app (yogurt) that claims to be used to deal/implement a version of a foaf 
> system, much like okurt/google. i'm not sure how the underlying 'special 
> sauce' is implemented, but it might be worth checking out...
> 
> let us know what you find. this seems to be the kind of thing that has 
> probably been described in the comp sci literature as graph theory/edge graph 
> theory has been around for awhile. also the ability to describe realtionships 
> between nodes within a graph is a topic that i believe has had a good deal of 
> research from an academic point of view...

Cool. Let us know if you find something interesting. Are you planning 
to allow different colors for the edges (relationship types) ? 
I'll  "race" any library that's been written :-)

Balazs

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



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

Re: Social Networking querys

[Balazs Rauznitz]

On Wed, Feb 02, 2005 at 09:20:28AM -0800, Bruce Douglas wrote:
> hi...
> 
> i'm not sure who the original poster was... but, there is also an open source 
> app (yogurt) that claims to be used to deal/implement a version of a foaf 
> system, much like okurt/google. i'm not sure how the underlying 'special 
> sauce' is implemented, but it might be worth checking out...
> 
> let us know what you find. this seems to be the kind of thing that has 
> probably been described in the comp sci literature as graph theory/edge graph 
> theory has been around for awhile. also the ability to describe realtionships 
> between nodes within a graph is a topic that i believe has had a good deal of 
> research from an academic point of view...

Cool. Let us know if you find something interesting. Are you planning 
to allow different colors for the edges (relationship types) ? 
I'll  "race" any library that's been written :-)

Balazs

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

Re: Social Networking querys

[Michael Dykman]

During a lengthy stint at a major provider of financial data, I came
across this concept generic modelling..  The idea was to have one tables
of 'things' and a second one of 'relationships'..  In concept, it's a
very powerful and elegant idea, but it tends to be recursion-heavy. 
Depending on what you want to do with it and the expected load it might
not be feasible for real-world use.

I think your model is quite elegant and I doubt that XML will improve
the robustness, but bear in mind the Kevin Bacon Postulate:  given a
population of 7 billion everyone is related to everyone else (many
several times) by the time you are down to the 6th degree.  The
efficiency has an exponential inverse relationship to the degree.


 - michael dykman

On Wed, 2005-02-02 at 10:47, listsql listsql wrote:
> Since I read about Foaf [ http://www.foaf-project.org/ ], I become
> interested with Social Networking, and readed a lot about it.
> I 've been playing as well with mysql and join querys to represent
> network's of people. As I made some queries in google and didn't came
> with a lot interesting info about this, now I will comment here my own
> querys.
> 
> urelation table:  (this kind of relation is called the adjacency list
> model, or recursion)
> +-+-+
> | uid | fid |
> +-+-+
> |   1 |   2 |
> |   2 |   3 |
> |   1 |   3 |
> |   3 |   2 |
> |   3 |   0 |
> |   0 |   2 |
> |   3 |   1 |
> +-+-+
> This represent's the id of  people and the id of their friend ('s)
> 
> uprofile table: 
> +-+---+
> | uid | name  |
> +-+---+
> |   0 | martin 0  |
> |   1 | pedro 1   |
> |   2 | pablo 2   |
> |   3 | matias 3  |
> |   4 | mateo 4   |
> |   5 | claudio 5 |
> +-+---+
> 
> 
> 
> So if I want to get the friend's and friend's of friend's  of  pablo:
> 
> SELECT p1.name  p1,p2.name  p2
> FROM uprofile p1,uprofile p2
> left join urelation r1 ON r1.fid=p1.uid 
> and r1.uid =2 
> left join urelation r2 ON r2.fid=p2.uid 
> where r2.uid =r1.fid 
> 
> +--+--+
> | p1   | p2   |
> +--+--+
> | matias 3 | martin 0 |
> | matias 3 | pedro 1  |
> | matias 3 | pablo 2  |
> +--+--+
> 
> And I add logically one join more if I want to get deeper in the network.
> The obvious problem is that for the first table p1 I will get the
> repeating Id, but that is not an issue now.
> 
> Where I wanted to get more feedback is, there is some method to
> iterate in this relation to avoid joining the table in itself each
> time ?
> Or also:
> Is this the best way to store human-like social relations, or there is
> a better way to do this ?
> 
> Thanks in advance,
> -- 
> Martin
>  ("Welcome to saving hierarchical data in mysql: Recursion until your
> head explodes")
-- 
 - michael dykman
 - [EMAIL PROTECTED]


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

Re: Social Networking querys

[Bruce Douglas]

hi...

i'm not sure who the original poster was... but, there is also an open source 
app (yogurt) that claims to be used to deal/implement a version of a foaf 
system, much like okurt/google. i'm not sure how the underlying 'special sauce' 
is implemented, but it might be worth checking out...

let us know what you find. this seems to be the kind of thing that has probably 
been described in the comp sci literature as graph theory/edge graph theory has 
been around for awhile. also the ability to describe realtionships between 
nodes within a graph is a topic that i believe has had a good deal of research 
from an academic point of view...

looks like it's time for a google search!

regards,

bruce

ps. i know, this has only a tangental relationship to mysql!!!




-Original Message-
From: Balazs Rauznitz <[EMAIL PROTECTED]>
Sent: Feb 2, 2005 9:05 AM
To: listsql listsql <[EMAIL PROTECTED]>
Cc: mysql 
Subject: Re: Social Networking querys

On Wed, Feb 02, 2005 at 04:47:31PM +0100, listsql listsql wrote:
> Since I read about Foaf [ http://www.foaf-project.org/ ], I become
> interested with Social Networking, and readed a lot about it.
> I 've been playing as well with mysql and join querys to represent
> network's of people. As I made some queries in google and didn't came
> with a lot interesting info about this, now I will comment here my own
> querys.
> 
> urelation table:  (this kind of relation is called the adjacency list
> model, or recursion)
> +-+-+
> | uid | fid |
> +-+-+
> |   1 |   2 |
> |   2 |   3 |
> |   1 |   3 |
> |   3 |   2 |
> |   3 |   0 |
> |   0 |   2 |
> |   3 |   1 |
> +-+-+
> This represent's the id of  people and the id of their friend ('s)
> 
> uprofile table: 
> +-+---+
> | uid | name  |
> +-+---+
> |   0 | martin 0  |
> |   1 | pedro 1   |
> |   2 | pablo 2   |
> |   3 | matias 3  |
> |   4 | mateo 4   |
> |   5 | claudio 5 |
> +-+---+
> 
> 
> 
> So if I want to get the friend's and friend's of friend's  of  pablo:
> 
> SELECT p1.name  p1,p2.name  p2
> FROM uprofile p1,uprofile p2
> left join urelation r1 ON r1.fid=p1.uid 
> and r1.uid =2 
> left join urelation r2 ON r2.fid=p2.uid 
> where r2.uid =r1.fid 
> 
> +--+--+
> | p1   | p2   |
> +--+--+
> | matias 3 | martin 0 |
> | matias 3 | pedro 1  |
> | matias 3 | pablo 2  |
> +--+--+
> 
> And I add logically one join more if I want to get deeper in the network.
> The obvious problem is that for the first table p1 I will get the
> repeating Id, but that is not an issue now.
> 
> Where I wanted to get more feedback is, there is some method to
> iterate in this relation to avoid joining the table in itself each
> time ?
> Or also:
> Is this the best way to store human-like social relations, or there is
> a better way to do this ?

I work at a social networking company. We store the social network as 
an edge graph too, but do not use SQL to run queries on it. It'd be 
just way too slow in huge networks. There's a custom application creted 
in-house written in C to do that. Hope this was at least a little 
useful.

Also there's somebody from Friendster here, he might be able to help 
you also.

Balazs


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



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

Re: Social Networking querys

[Nick Arnett]

Peter Brawley wrote:
is, there is some method to iterate in this relation to avoid joining 
the table in itself each time ?

Exactly the problem with trying to model an anything-goes network, a 
"world of ends", in a relational model of a directed graph. I think 
you need an XML layer in there, eg see 
http://iswc2004.semanticweb.org/demos/03/.

XML isn't necessary and probably just makes it more complicated.  As 
someone wrote, if you have a problem and decide to solve it with XML, 
now you have two problems.

On the other hand, since the semantic web relies on graphs, approaches 
that work for it do apply... but unless interoperability matters, it's 
hard for me to imagine a good reason to bother with XML.

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

Re: Social Networking querys

[Nick Arnett]

listsql listsql wrote:
Since I read about Foaf [ http://www.foaf-project.org/ ], I become
interested with Social Networking, 

What you're doing is often called link analysis -- searches on that term 
may yield more for you to chew on.  There are software tools and 
visualization tools for answering the kind of questions this data 
covers.  For the latter, Pajek 
(http://vlado.fmf.uni-lj.si/pub/networks/pajek/) is one of the most 
interesting.

More generally, you're storing a graph in a relational database.  
Searching on that subject will show you several approaches.

The way I deal with this is to create tables pretty much as you have, 
but instead of self-joins in MySQL, I load it all into a program that 
stores the graph in hashes, then uses recursion to get the kind of 
answers you want.  This is much, much, much faster than doing the same 
in the database, though it might become memory constrained if you have a 
big network.  In that case, my next step is to do all of the recursions 
and store the results in the database as pairs and their distances from 
one another.  Then it's a simple lookup.

If you do find a way to do this efficiently in MySQL, I'll be interested!
Nick
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Social Networking querys

[Balazs Rauznitz]

On Wed, Feb 02, 2005 at 04:47:31PM +0100, listsql listsql wrote:
> Since I read about Foaf [ http://www.foaf-project.org/ ], I become
> interested with Social Networking, and readed a lot about it.
> I 've been playing as well with mysql and join querys to represent
> network's of people. As I made some queries in google and didn't came
> with a lot interesting info about this, now I will comment here my own
> querys.
> 
> urelation table:  (this kind of relation is called the adjacency list
> model, or recursion)
> +-+-+
> | uid | fid |
> +-+-+
> |   1 |   2 |
> |   2 |   3 |
> |   1 |   3 |
> |   3 |   2 |
> |   3 |   0 |
> |   0 |   2 |
> |   3 |   1 |
> +-+-+
> This represent's the id of  people and the id of their friend ('s)
> 
> uprofile table: 
> +-+---+
> | uid | name  |
> +-+---+
> |   0 | martin 0  |
> |   1 | pedro 1   |
> |   2 | pablo 2   |
> |   3 | matias 3  |
> |   4 | mateo 4   |
> |   5 | claudio 5 |
> +-+---+
> 
> 
> 
> So if I want to get the friend's and friend's of friend's  of  pablo:
> 
> SELECT p1.name  p1,p2.name  p2
> FROM uprofile p1,uprofile p2
> left join urelation r1 ON r1.fid=p1.uid 
> and r1.uid =2 
> left join urelation r2 ON r2.fid=p2.uid 
> where r2.uid =r1.fid 
> 
> +--+--+
> | p1   | p2   |
> +--+--+
> | matias 3 | martin 0 |
> | matias 3 | pedro 1  |
> | matias 3 | pablo 2  |
> +--+--+
> 
> And I add logically one join more if I want to get deeper in the network.
> The obvious problem is that for the first table p1 I will get the
> repeating Id, but that is not an issue now.
> 
> Where I wanted to get more feedback is, there is some method to
> iterate in this relation to avoid joining the table in itself each
> time ?
> Or also:
> Is this the best way to store human-like social relations, or there is
> a better way to do this ?

I work at a social networking company. We store the social network as 
an edge graph too, but do not use SQL to run queries on it. It'd be 
just way too slow in huge networks. There's a custom application creted 
in-house written in C to do that. Hope this was at least a little 
useful.

Also there's somebody from Friendster here, he might be able to help 
you also.

Balazs


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

Re: Social Networking querys

[Peter Brawley]

is, there is some method to iterate in this relation 
to avoid joining the table in itself each time ?
Exactly the problem with trying to model an anything-goes network, a 
"world of ends", in a relational model of a directed graph. I think you 
need an XML layer in there, eg see 
http://iswc2004.semanticweb.org/demos/03/.

PB
-
listsql listsql wrote:
Since I read about Foaf [ http://www.foaf-project.org/ ], I become
interested with Social Networking, and readed a lot about it.
I 've been playing as well with mysql and join querys to represent
network's of people. As I made some queries in google and didn't came
with a lot interesting info about this, now I will comment here my own
querys.
urelation table:  (this kind of relation is called the adjacency list
model, or recursion)
+-+-+
| uid | fid |
+-+-+
|   1 |   2 |
|   2 |   3 |
|   1 |   3 |
|   3 |   2 |
|   3 |   0 |
|   0 |   2 |
|   3 |   1 |
+-+-+
This represent's the id of  people and the id of their friend ('s)
uprofile table: 
+-+---+
| uid | name  |
+-+---+
|   0 | martin 0  |
|   1 | pedro 1   |
|   2 | pablo 2   |
|   3 | matias 3  |
|   4 | mateo 4   |
|   5 | claudio 5 |
+-+---+


So if I want to get the friend's and friend's of friend's  of  pablo:
SELECT p1.name  p1,p2.name  p2
FROM uprofile p1,uprofile p2
left join urelation r1 ON r1.fid=p1.uid 
and r1.uid =2 
left join urelation r2 ON r2.fid=p2.uid 
where r2.uid =r1.fid 

+--+--+
| p1   | p2   |
+--+--+
| matias 3 | martin 0 |
| matias 3 | pedro 1  |
| matias 3 | pablo 2  |
+--+--+
And I add logically one join more if I want to get deeper in the network.
The obvious problem is that for the first table p1 I will get the
repeating Id, but that is not an issue now.
Where I wanted to get more feedback is, there is some method to
iterate in this relation to avoid joining the table in itself each
time ?
Or also:
Is this the best way to store human-like social relations, or there is
a better way to do this ?
Thanks in advance,
 


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.4 - Release Date: 2/1/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Social Networking querys

[listsql listsql]

Since I read about Foaf [ http://www.foaf-project.org/ ], I become
interested with Social Networking, and readed a lot about it.
I 've been playing as well with mysql and join querys to represent
network's of people. As I made some queries in google and didn't came
with a lot interesting info about this, now I will comment here my own
querys.

urelation table:  (this kind of relation is called the adjacency list
model, or recursion)
+-+-+
| uid | fid |
+-+-+
|   1 |   2 |
|   2 |   3 |
|   1 |   3 |
|   3 |   2 |
|   3 |   0 |
|   0 |   2 |
|   3 |   1 |
+-+-+
This represent's the id of  people and the id of their friend ('s)

uprofile table: 
+-+---+
| uid | name  |
+-+---+
|   0 | martin 0  |
|   1 | pedro 1   |
|   2 | pablo 2   |
|   3 | matias 3  |
|   4 | mateo 4   |
|   5 | claudio 5 |
+-+---+



So if I want to get the friend's and friend's of friend's  of  pablo:

SELECT p1.name  p1,p2.name  p2
FROM uprofile p1,uprofile p2
left join urelation r1 ON r1.fid=p1.uid 
and r1.uid =2 
left join urelation r2 ON r2.fid=p2.uid 
where r2.uid =r1.fid 

+--+--+
| p1   | p2   |
+--+--+
| matias 3 | martin 0 |
| matias 3 | pedro 1  |
| matias 3 | pablo 2  |
+--+--+

And I add logically one join more if I want to get deeper in the network.
The obvious problem is that for the first table p1 I will get the
repeating Id, but that is not an issue now.

Where I wanted to get more feedback is, there is some method to
iterate in this relation to avoid joining the table in itself each
time ?
Or also:
Is this the best way to store human-like social relations, or there is
a better way to do this ?

Thanks in advance,
-- 
Martin
 ("Welcome to saving hierarchical data in mysql: Recursion until your
head explodes")

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

Re: Ignoring username parameter when connecting via ssh tunnel

[Richard Curtis]

> > I am trying to connect to my mysql server through an SSH tunnel.
> > On the server, I have a local instance of mysql running, but one of the
> > hosted domains needs to access another remote mysql server.  For security,
> > I want to connect to the remote server via an ssh tunnel.
> > I am creating the tunnel using the following command:
> > ssh -L 3307:xxx.xxx.xxx.xxx:3306 -l root -N xxx.xxx.xxx.xxx -f
> > This creates a tunnel so I can connect to port 3307 on the local server,
> > and end up talking to the remote server on 3306.
> > Telneting to 127.0.0.1:3307 gives me the mysql handshake.
> >
> > Now the fun begins when I try to use the connection.  If I do:
> >
> > mysql -h xxx.xxx.xxx.xxx -u leg_shop -p, I can log into the remote server
> > using the username leg_shop.
> > This works fine with no problems except the fact the traffic is not
> > encrypted as it isnt using the ssh tunnel.
> >
> > If I do:
> > mysql -h 127.0.0.1 -P 3307 -u leg_shop -p, it connects to the remote
> server
> > through the ssh tunnel, but for some insane reason, it ignores the "-u
> > leg_shop".
> >
> > I can enter any username of my choice (e.g. a user which is DEFINATELY not
> > valid on the remote server), and yet it still connects.
> >
> > Am I missing something here ?
> > On the server where I am trying to connect FROM, it has mysql client
> "mysql
> >  Ver 14.7 Distrib 4.1.7, for pc-linux (i686)", and on the remote server I
> > am trying to connect to via the tunnel, it is running "mysql  Ver 12.22
> > Distrib 4.0.16, for pc-linux (i686)"
> >
> > Any ideas or suggestions welcome.
> >
> > Richard
> 
> I believe that your mysql server gets the local ip address (127.0.0.1) as 
> connection source and not the one from your remote host which you are 
> connecting from.
> 
> You're granted access from the anonymous user which is default entered in the
> 
> mysql.user table.
> 
> I would suggest to 
> 1.) delete this users with
> use mysql; delete from user where user=''; flush privileges;
> 2.) Try to reconnect via ssh tunnel. This should be denied now.
> 3.) alter the host-entry from the leg_shop user to 'localhost' and try 
> connecting again.
> 
> Regards,
> bh

Absolutely right.
By removing the anonymous entry, it now works as I'd expected :)

Thanks all
Richard

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

Still can't connect to MySQL 5 on FC3

[Whil Hentzen]

Hi folks,
Been doing a lot of noodlin' and googlin' yesterday and today, and 
thought I found the problem... but alas!

To recap:
I just installed MySQL 5 on a freshly updated FC3 box. I initially was 
getting this error:

[EMAIL PROTECTED] ~] /usr/bin/mysqld_safe
Starting mysqld daemon with databases from /var/lib/mysql
/usr/bin/mysqld_safe: line 302: /var/lib/mysql/mybox.err: Permission denied
/usr/bin/mysqld_safe: line 308: /var/lib/mysql/mybox.err: Permission denied
STOPPING server from pid file /var/lib/mysql/mybox.pid
tee: /var/lib/mysql/mybox.err: Permission denied
050202 09:21:36  mysqld ended
tee: /var/lib/mysql/mybox.err: Permission denied
and this one:
[EMAIL PROTECTED] ~] mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket 
'/var/lib/mysql/mysql.sock' (2)
[EMAIL PROTECTED] ~]

Then I found the notes about selinux interferring with mysql starting up 
at http://forums.mysql.com/read.php?11,7164,7164

I tried
 setenforce 0
but I'm still getting the same errors.
Permissions are:
[EMAIL PROTECTED] /var/lib/mysql] I AM ROOT: ls -al
total 20572
drwxr-xr-x   4 mysql root  4096 Feb  2 08:30 .
drwxr-xr-x  35 root  root  4096 Feb  1 13:45 ..
-rw-rw   1 mysql root  1844 Feb  2 08:30 mybox.err
-rw-rw   1 mysql mysql 10485760 Feb  2 08:30 ibdata1
-rw-rw   1 mysql mysql  5242880 Feb  2 08:30 ib_logfile0
-rw-rw   1 mysql mysql  5242880 Feb  2 08:30 ib_logfile1
drwx--x--x   2 mysql root  4096 Feb  1 13:45 mysql
drwxr-xr-x   2 mysql root  4096 Feb  1 13:45 test
[EMAIL PROTECTED] /var/lib/mysql]
And the contents of the err file:
[EMAIL PROTECTED] /var/lib/mysql] tail mybox.err
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
050202  8:30:51  InnoDB: Started; log sequence number 0 0
050202  8:30:51 [ERROR] Fatal error: Can't open privilege tables: Table 
'mysql.host' doesn't exist
050202 08:30:51  mysqld ended

I'm stuck. Suggestions?
Whil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Ignoring username parameter when connecting via ssh tunnel

[Bernhard Fiser]

On Wednesday 02 February 2005 15:39, Richard Curtis wrote:
> I am trying to connect to my mysql server through an SSH tunnel.
> On the server, I have a local instance of mysql running, but one of the
> hosted domains needs to access another remote mysql server.  For security,
> I want to connect to the remote server via an ssh tunnel.
> I am creating the tunnel using the following command:
> ssh -L 3307:xxx.xxx.xxx.xxx:3306 -l root -N xxx.xxx.xxx.xxx -f
> This creates a tunnel so I can connect to port 3307 on the local server,
> and end up talking to the remote server on 3306.
> Telneting to 127.0.0.1:3307 gives me the mysql handshake.
>
> Now the fun begins when I try to use the connection.  If I do:
>
> mysql -h xxx.xxx.xxx.xxx -u leg_shop -p, I can log into the remote server
> using the username leg_shop.
> This works fine with no problems except the fact the traffic is not
> encrypted as it isnt using the ssh tunnel.
>
> If I do:
> mysql -h 127.0.0.1 -P 3307 -u leg_shop -p, it connects to the remote server
> through the ssh tunnel, but for some insane reason, it ignores the "-u
> leg_shop".
>
> I can enter any username of my choice (e.g. a user which is DEFINATELY not
> valid on the remote server), and yet it still connects.
>
> Am I missing something here ?
> On the server where I am trying to connect FROM, it has mysql client "mysql
>  Ver 14.7 Distrib 4.1.7, for pc-linux (i686)", and on the remote server I
> am trying to connect to via the tunnel, it is running "mysql  Ver 12.22
> Distrib 4.0.16, for pc-linux (i686)"
>
> Any ideas or suggestions welcome.
>
> Richard

I believe that your mysql server gets the local ip address (127.0.0.1) as 
connection source and not the one from your remote host which you are 
connecting from.

You're granted access from the anonymous user which is default entered in the 
mysql.user table.

I would suggest to 
1.) delete this users with
use mysql; delete from user where user=''; flush privileges;
2.) Try to reconnect via ssh tunnel. This should be denied now.
3.) alter the host-entry from the leg_shop user to 'localhost' and try 
connecting again.

Regards,
bh

-- 

Bernhard Fiser [EMAIL PROTECTED]
Telekommunikation und Medien
Fachhochschule St. Pölten/St. Poelten University of Applied Sciences
Herzogenburger Straße 68 | 3100 St. Pölten | +43 (0) 2742 313228 48


pgpLt1CauEqfO.pgp
Description: PGP signature

RE: Ignoring username parameter when connecting via ssh tunnel

[Tom Crimmins]

> I am trying to connect to my mysql server through an SSH tunnel.
> On the server, I have a local instance of mysql running, but 
> one of the hosted
> domains needs to access another remote mysql server.  For 
> security, I want to
> connect to the remote server via an ssh tunnel.
> I am creating the tunnel using the following command:
> ssh -L 3307:xxx.xxx.xxx.xxx:3306 -l root -N xxx.xxx.xxx.xxx -f
> This creates a tunnel so I can connect to port 3307 on the 
> local server, and end
> up talking to the remote server on 3306.
> Telneting to 127.0.0.1:3307 gives me the mysql handshake.
> 
> Now the fun begins when I try to use the connection.  If I do:
> 
> mysql -h xxx.xxx.xxx.xxx -u leg_shop -p, I can log into the 
> remote server using
> the username leg_shop.  
> This works fine with no problems except the fact the traffic 
> is not encrypted as
> it isnt using the ssh tunnel.
> 
> If I do:
> mysql -h 127.0.0.1 -P 3307 -u leg_shop -p, it connects to the 
> remote server
> through the ssh tunnel, but for some insane reason, it ignores the "-u
> leg_shop".
> 
> I can enter any username of my choice (e.g. a user which is 
> DEFINATELY not valid
> on the remote server), and yet it still connects.
> 
> Am I missing something here ?
> On the server where I am trying to connect FROM, it has mysql 
> client "mysql  Ver
> 14.7 Distrib 4.1.7, for pc-linux (i686)", and on the remote 
> server I am trying
> to connect to via the tunnel, it is running "mysql  Ver 12.22 
> Distrib 4.0.16,
> for pc-linux (i686)"
> 
> Any ideas or suggestions welcome.
> 
> Richard

Since you are using an ssh tunnel, you will be able to connect with any
username if you have not removed the anonymous accounts from the user table.
This is because to mysql, I believe that it will look like these connections
are coming for the local machine. You could test this by putting -p
nothepassword on your connect line and look at the access denied message to
see who you are connecting as. What I am trying to say is that connecting
from the remote machine with out the tunnel is not the same as connecting
with the tunnel as far as permissions are concerned.

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

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

Re: Ignoring username parameter when connecting via ssh tunnel

[Michael Stassen]

Richard Curtis wrote:
I am trying to connect to my mysql server through an SSH tunnel.
On the server, I have a local instance of mysql running, but one of the hosted
domains needs to access another remote mysql server.  For security, I want to
connect to the remote server via an ssh tunnel.
I am creating the tunnel using the following command:
ssh -L 3307:xxx.xxx.xxx.xxx:3306 -l root -N xxx.xxx.xxx.xxx -f
This creates a tunnel so I can connect to port 3307 on the local server, and end
up talking to the remote server on 3306.
Telneting to 127.0.0.1:3307 gives me the mysql handshake.
Now the fun begins when I try to use the connection.  If I do:
mysql -h xxx.xxx.xxx.xxx -u leg_shop -p, I can log into the remote server using
the username leg_shop.  
This works fine with no problems except the fact the traffic is not encrypted as
it isnt using the ssh tunnel.

If I do:
mysql -h 127.0.0.1 -P 3307 -u leg_shop -p, it connects to the remote server
through the ssh tunnel, but for some insane reason, it ignores the "-u
leg_shop".
What, exactly, do you mean by this?  Do you get an error message, an 
authentication failed message, or do you get in as the wrong user?

I can enter any username of my choice (e.g. a user which is DEFINATELY not valid
on the remote server), and yet it still connects.
Are you prompted for a password?  What user does it connect as?  That is, 
what does

  SELECT CURRENT_USER();
say?
Am I missing something here ?
On the server where I am trying to connect FROM, it has mysql client "mysql  Ver
14.7 Distrib 4.1.7, for pc-linux (i686)", and on the remote server I am trying
to connect to via the tunnel, it is running "mysql  Ver 12.22 Distrib 4.0.16,
for pc-linux (i686)"
Any ideas or suggestions welcome.
Richard
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Nested query bug

[SGreen]

matt_lists <[EMAIL PROTECTED]> wrote on 02/02/2005 08:50:16 AM:

> Jay Blanchard wrote:
> 
> >[snip]
> > 
> >
> >>>No, it isn't ignored...it just returns a FALSE for the IN statement
> >>> 
> >>>
> >[/snip]
> >
> >More info 
> >
> >"The word IN is an alias for = ANY. Thus these two statements are the
> >same: 
> >
> >SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
> >SELECT s1 FROM t1 WHERE s1 IN(SELECT s1 FROM t2);
> >
> >However, NOT IN is not an alias for <> ANY, but for <> ALL. See Section
> >13.1.8.4, "Subqueries with ALL". "
> >
> >>From http://dev.mysql.com/doc/mysql/en/any-in-some-subqueries.html
> >
> > 
> >
> I can tell you the sql works fine when I alias the column
> 
> the original sql should not work at all, the column does not exist in 
> the table, yet it returns no query error
> 


I don't think the column alias is what made the query work. You should be 
able to call the column in your subquery 'moo_cow' and it should still 
work. The "fieldname IN (subquery)" construct works because the subquery 
returns a single column that acts as a list of values (even if that list 
is possibly empty).

What I think you two are cross-communicating about is that for an INVALID 
subquery, MySQL threw no errors and no warnings. What I have heard 
discussed was that the clause "fieldname IN (failed subquery)" should 
evaluate as FALSE for the IN clause. Then I heard that the "AND fieldname 
IN (failed subquery)" term didn't restrict results, effectively saying 
that it evaluated as TRUE. Both cannot be correct.

What I believe is that error was probably just ignored (that the 
processing of that branch of the WHERE tree failed silently) when it 
should have thrown an error for the entire statement. An invalid subquery 
should cause the entire enclosing statement to fail, but it didn't. It 
shouldn't matter whether the results of the subquery *could* be ignored, 
the syntax error (invalid column name) should have been raised and it 
should have cancelled the query.  IMHO, that's the problem. I suggest that 
the OP report it as a bug.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

I disagree that the error should be evaluated as FALSE

Ignoring username parameter when connecting via ssh tunnel

[Richard Curtis]

I am trying to connect to my mysql server through an SSH tunnel.
On the server, I have a local instance of mysql running, but one of the hosted
domains needs to access another remote mysql server.  For security, I want to
connect to the remote server via an ssh tunnel.
I am creating the tunnel using the following command:
ssh -L 3307:xxx.xxx.xxx.xxx:3306 -l root -N xxx.xxx.xxx.xxx -f
This creates a tunnel so I can connect to port 3307 on the local server, and end
up talking to the remote server on 3306.
Telneting to 127.0.0.1:3307 gives me the mysql handshake.

Now the fun begins when I try to use the connection.  If I do:

mysql -h xxx.xxx.xxx.xxx -u leg_shop -p, I can log into the remote server using
the username leg_shop.  
This works fine with no problems except the fact the traffic is not encrypted as
it isnt using the ssh tunnel.

If I do:
mysql -h 127.0.0.1 -P 3307 -u leg_shop -p, it connects to the remote server
through the ssh tunnel, but for some insane reason, it ignores the "-u
leg_shop".

I can enter any username of my choice (e.g. a user which is DEFINATELY not valid
on the remote server), and yet it still connects.

Am I missing something here ?
On the server where I am trying to connect FROM, it has mysql client "mysql  Ver
14.7 Distrib 4.1.7, for pc-linux (i686)", and on the remote server I am trying
to connect to via the tunnel, it is running "mysql  Ver 12.22 Distrib 4.0.16,
for pc-linux (i686)"

Any ideas or suggestions welcome.

Richard


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

InnoDB locking issues

[Dobromir Velev]

Hi,
I'm running quite a large database - mostly inserts (4-5 million rows a day) 
and an occasional select to generate some report based on the inserted data 
which works very well.
Once a month I need to generate a larger number of reports. The SELECT queries 
are optimized quite well but some of the reports require to group a large 
number of records together which could take up to 5 minutes, but that is OK 
for me. The problem is that when more than a certain number (between 4 and 8 
- but I can find any stable pattern) of SELECT queries are working 
concurrently InnoDB starts to lock the threads that insert data and in a very 
short time MySQL uses all available connections. I couldn't find any reasons 
why InnoDB is locking INSERT threads which try to insert in tables different 
then the ones that the reports are SELECT-ing from.

The InnoDB monitor shows a lot of transactions similar to this

---TRANSACTION 1 4271824648, ACTIVE 51 sec, process no 12904, OS thread id 
712286614 setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 320
MySQL thread id 485399224, query id 3713824274 69.59.185.156 websitepulse9 
update
INSERT INTO slogs8 VALUES 
(NULL,24801,'OK','0.00','62.40','62.56','62.82','N',Now(),0.479633*1000)
--- TRX HAS BEEN WAITING 51 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `websitepulse/slogs8` trx id 1 4271824648 lock mode AUTO-INC 
waiting

Normally there are less than 200 running threads (1-2 active) but to avoid 
hitting the connections limit due to this locking problem I had to increase 
the maximum connections number to 600 and respectively had to decrease the 
size of the per-thread memory buffers  which slows down the selects 
additionaly.

I'll probably use a second server to replicate the database and run the 
reports from there but I wanted to see if somebody might had a different 
suggestion.


Here is the servers info

MySQL: 4.0.21-standard Official MySQL RPM
OS : Red Hat Enterprise Linux ES 3  
Memory : 4 GB DELL RAM
Processor : Dual 3.06 GHz Intel Xeon
RAID Configuration : RAID 1 146 GB SCSI

Here is my /etc/my.cnf file
[mysqld]
port=3306
skip-name-resolve
log-bin=/var/lib/mysql/mysql
log-slow-queries=/var/lib/mysql/slow.queries.log
socket=/var/lib/mysql/mysql.sock
myisam-recover=BACKUP,FORCE
set-variable = max_connect_errors=10
innodb_data_home_dir =
innodb_data_file_path =/var/lib/mysql/innodbfile:100M:autoextend
innodb_log_group_home_dir = /var/log/innologs
innodb_log_arch_dir = /var/log/innologs
set-variable = innodb_buffer_pool_size=1700M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_log_file_size=150M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=0
set-variable = innodb_lock_wait_timeout=50
set-variable = key_buffer_size=500M
set-variable = read_buffer_size=500K
set-variable = read_rnd_buffer_size=1200K
set-variable = sort_buffer_size=1M
set-variable = thread_cache=256
set-variable = thread_concurrency=8
set-variable = thread_stack=126976
set-variable = myisam_sort_buffer_size=64M
set-variable = max_connections=600
set-variable = table_cache=1
set-variable = wait_timeout=2000

Any suggestions are welcome.
-- 
Mark J.

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

RE: List of connection error

[Tom Crimmins]

> Is there any documentation where I can find a list of
> all connection related error/error codes returned by
> MySQL?

OS error codes :
http://dev.mysql.com/doc/mysql/en/operating-system-error-codes.html

Server error messages :
http://dev.mysql.com/doc/mysql/en/error-handling.html

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

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

List of connection error

[Karam Chand]

Hello,

Is there any documentation where I can find a list of
all connection related error/error codes returned by
MySQL?

Regards,
Karam



__ 
Do you Yahoo!? 
Meet the all-new My Yahoo! - Try it today! 
http://my.yahoo.com 
 


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

Re: mysqldump and Foreign Keys

[Duncan Hill]

On Wednesday 02 February 2005 14:11, Michael Satterwhite might have typed:
> mysqldump backs all of the tables up in alphabetic order, including the
> table create statements. The problem is that if foreign keys exist on
> the tables, the reload will not work unless the backup file is hand
> edited to insure that tables depended upon are created first. Is there a
> way around this? If not, shouldn't the tables be created first and the

From mysqldump that ships with 4.1:

-- MySQL dump 10.8
--
-- Host: localhostDatabase:
-- --
-- Server version   4.1.7

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
.
.
.
.
SET [EMAIL PROTECTED];
SET [EMAIL PROTECTED];

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

Re: help please : ERROR 2006: MySQL server has gone away

[Marois, David]

And 
 
max_allowed_packet  = 16776192 
 
 
David

 
Hi, 
my interactive_timeout variable is
 
interactive_timeout 3600
 
Thanks !
 
David
 
 
Hello.
 
I've asked you about interactive_timeout, not wait_timeout.
 

Mark <[EMAIL PROTECTED]> wrote:
>> -Original Message-
>> From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
> 
>> Sent: woensdag 2 februari 2005 12:46
>> To: [EMAIL PROTECTED]
>> Subject: Re: help please : ERROR 2006: MySQL server has gone away
>> 
>> Hello.
>> 
>> What's the value of the interactive_timeout system variable? See:
>> http://dev.mysql.com/doc/mysql/en/server-system-variables.html
>> http://dev.mysql.com/doc/mysql/en/gone-away.html
> 
> I believe he already answered that in part:
> 
>> > Also, my variable wait_timeout = 3600.
> 
> 1 hour, that is, instead of the default 8 (28800).
> 
> - Mark
> 
> 

 
David Marois
  [EMAIL PROTECTED]
 

mysqldump and Foreign Keys

[Michael Satterwhite]

mysqldump backs all of the tables up in alphabetic order, including the 
table create statements. The problem is that if foreign keys exist on 
the tables, the reload will not work unless the backup file is hand 
edited to insure that tables depended upon are created first. Is there a 
way around this? If not, shouldn't the tables be created first and the 
foreign keys created at the end of the backup? I *REALLY don't want to 
hack mysqldump and am hoping that there's already a way around this - or 
that a way is being developed.

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

Re: help please : ERROR 2006: MySQL server has gone away

[Marois, David]

Hi, 
my interactive_timeout variable is
 
interactive_timeout 3600
 
Thanks !
 
David
 
 
Hello.
 
I've asked you about interactive_timeout, not wait_timeout.
 

Mark <[EMAIL PROTECTED]> wrote:
>> -Original Message-
>> From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
> 
>> Sent: woensdag 2 februari 2005 12:46
>> To: [EMAIL PROTECTED]
>> Subject: Re: help please : ERROR 2006: MySQL server has gone away
>> 
>> Hello.
>> 
>> What's the value of the interactive_timeout system variable? See:
>> http://dev.mysql.com/doc/mysql/en/server-system-variables.html
>> http://dev.mysql.com/doc/mysql/en/gone-away.html
> 
> I believe he already answered that in part:
> 
>> > Also, my variable wait_timeout = 3600.
> 
> 1 hour, that is, instead of the default 8 (28800).
> 
> - Mark
> 
> 

 
David Marois
  [EMAIL PROTECTED]
 

Re: Nested query bug

[matt_lists]

Jay Blanchard wrote:
[snip]
 

No, it isn't ignored...it just returns a FALSE for the IN statement
 

[/snip]
More info 
"The word IN is an alias for = ANY. Thus these two statements are the
same: 

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN(SELECT s1 FROM t2);
However, NOT IN is not an alias for <> ANY, but for <> ALL. See Section
13.1.8.4, "Subqueries with ALL". "
From http://dev.mysql.com/doc/mysql/en/any-in-some-subqueries.html
 

I can tell you the sql works fine when I alias the column
the original sql should not work at all, the column does not exist in 
the table, yet it returns no query error

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

Re: help please : ERROR 2006: MySQL server has gone away

[Gleb Paharenko]

Hello.



I've asked you about interactive_timeout, not wait_timeout.





Mark <[EMAIL PROTECTED]> wrote:

>> -Original Message-

>> From: Gleb Paharenko [mailto:[EMAIL PROTECTED] 

>> Sent: woensdag 2 februari 2005 12:46

>> To: mysql@lists.mysql.com

>> Subject: Re: help please : ERROR 2006: MySQL server has gone away

>> 

>> Hello.

>> 

>> What's the value of the interactive_timeout system variable? See:

>> http://dev.mysql.com/doc/mysql/en/server-system-variables.html

>> http://dev.mysql.com/doc/mysql/en/gone-away.html

> 

> I believe he already answered that in part:

> 

>> > Also, my variable wait_timeout = 3600.

> 

> 1 hour, that is, instead of the default 8 (28800).

> 

> - Mark

> 

> 



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




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

err-report help me

[张海军]

mysql，您好！

 when click mysql.exe on $home\bin ,the report err is
 "D:\Program Files\mysql\bin>mysql
ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) "

please help me

致
礼！


 zhanghjun
[EMAIL PROTECTED]
　　2005-02-02



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

Re: Removing 3.23, installing 5.02

[Coz Web]

I know it's not quite the same, but I recently upgraded 2 FC2 boxes
from MySQL3.23.xx to MySQL4.0.23 simply by using (as Gleb suggested) :

 rpm -U mysql*

I had downloaded the following rpm files from the MySQL site :

Linux x86 RPM downloads
---
Server  4.0.23-0 10.6M  
Client programs4.0.23-0   2.6M  
Libraries and header files   4.0.23-0  1005.8K  
Dynamic client libraries 4.0.23-0258.9K 
Dynamic client libraries 4.0.23-0480.9K
(including 3.23.x libraries)

HTH

Coz

On Wed, 02 Feb 2005 10:40:52 +0200, Gleb Paharenko
<[EMAIL PROTECTED]> wrote:
> Hello.
> 
> > the Add/Remove Packages (and the manual RPM command) both failed on
> 
> > multiple dependencies,
> 
> rpm has --force and --nodeps flags and you can
> 
> use -U instead of -i. See:
> 
>   man rpm
> 
> > little fingers and tried removing all the MySQL files manually. I think
> 
> > I got them all except the ones in /sur/lib; 'rm' on mysqlbug and
> 
> > mysql_config failed with a 'cannot lstat - no such file or directory'
> 
> Do these files really exist in that directory?
> 
> > (except those lib files). Are those what's causing this problem? How do
> 
> I think, that your rpm database has records about old packages. You can
> 
> remove them using rpm -e --force --nodeps pkg_name.
> 
> Whil Hentzen <[EMAIL PROTECTED]> wrote:
> 
> > Hi folks,
> 
> >
> 
> > I've got a Fedora Core 3 box that came with MySQL 3.23.xx, recently
> 
> > updated to 3.23.58. I wanted to wipe the box clean and install 5.0, but
> 
> > the Add/Remove Packages (and the manual RPM command) both failed on
> 
> > multiple dependencies, so I (foolishly?) took matters into my own fat
> 
> > little fingers and tried removing all the MySQL files manually. I think
> 
> > I got them all except the ones in /sur/lib; 'rm' on mysqlbug and
> 
> > mysql_config failed with a 'cannot lstat - no such file or directory'
> 
> > message.
> 
> >
> 
> > Anyway, running
> 
> >  [EMAIL PROTECTED]  rpm -i MySQL-server-5.0.2-0.i386.rpm
> 
> > failed with
> 
> >  warning: MySQL-server-5.0.2-0.i386.rpm: V3 DSA signature: NOKEY, key
> 
> > ID 5072e1f5
> 
> >  error: Failed dependencies:
> 
> >MySQL conflicts with mysql-3.23.58-14.i386
> 
> >MySQL-server conflicts with mysql-server-3.23.58-14.i386
> 
> >
> 
> > And I am stuck. I can't find any more files having to do with 3.23
> 
> > (except those lib files). Are those what's causing this problem? How do
> 
> > I get rid of them?
> 
> >
> 
> > Thanks,
> 
> >
> 
> > Whil
> 
> >
> 
> --
> For technical support contracts, goto https://order.mysql.com/?ref=ensita
> This email is sponsored by Ensita.NET http://www.ensita.net/
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
>  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
> /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
><___/   www.mysql.com
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
CozWeb Solutions Ltd
http://www.cozweb.net

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

Re: SHOW TABLES Problem

[shaun thornburgh]

Thanks for your reply,
I have a query where I select all table names where the table name has PID_1
in i.e.
SHOW TABLES LIKE '%PID_1%';
However there may be cases where I need to search for tables where the table
name is PID_1 or PID_2. Do you another way I might be able to do this?

From: "Gabriel PREDA" <[EMAIL PROTECTED]>
Reply-To: "Gabriel PREDA" <[EMAIL PROTECTED]>
To: "shaun thornburgh" 
<[EMAIL PROTECTED]>,
Subject: Re: SHOW TABLES Problem
Date: Wed, 2 Feb 2005 13:48:43 +0200

Because it does not know REGEXP... only LIKE patterns...
  %  Matches any number of characters, even zero characters
  _  Matches exactly one character
Taake a look in chapter 13 "String Comparison Functions".
Gabriel PREDA
- Original Message -
From: "shaun thornburgh" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, February 02, 2005 1:03 PM
Subject: SHOW TABLES Problem
> Hi,
>
> The syntax for show tables from the manual is:
>
> SHOW [FULL|OPEN] TABLES [FROM db_name] [LIKE 'pattern']
>
> so whay doesnt the following query work:
>
> SHOW TABLES LIKE REGEXP 'PID_[0-9]+';
>
> Thanks for your help
>
> Shaun
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

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

RE: help please : ERROR 2006: MySQL server has gone away

[Mark]

> -Original Message-
> From: Gleb Paharenko [mailto:[EMAIL PROTECTED] 
> Sent: woensdag 2 februari 2005 12:46
> To: mysql@lists.mysql.com
> Subject: Re: help please : ERROR 2006: MySQL server has gone away
> 
> Hello.
> 
> What's the value of the interactive_timeout system variable? See:
> http://dev.mysql.com/doc/mysql/en/server-system-variables.html
> http://dev.mysql.com/doc/mysql/en/gone-away.html

I believe he already answered that in part:

> > Also, my variable wait_timeout = 3600.

1 hour, that is, instead of the default 8 (28800).

- Mark


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

Re: SHOW TABLES Problem

[Gabriel PREDA]

Because it does not know REGEXP... only LIKE patterns...

  %  Matches any number of characters, even zero characters  
  _  Matches exactly one character  


Taake a look in chapter 13 "String Comparison Functions".

Gabriel PREDA

- Original Message - 
From: "shaun thornburgh" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, February 02, 2005 1:03 PM
Subject: SHOW TABLES Problem


> Hi,
> 
> The syntax for show tables from the manual is:
> 
> SHOW [FULL|OPEN] TABLES [FROM db_name] [LIKE 'pattern']
> 
> so whay doesnt the following query work:
> 
> SHOW TABLES LIKE REGEXP 'PID_[0-9]+';
> 
> Thanks for your help
> 
> Shaun
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

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

Re: What are the reasons?

[Gleb Paharenko]

Hello.



Not enough information to make a conclusion. Please, send us 

information about MySQL and operating system versions, contents

of your configuration file, output of the following statements:

  show processlist;

  show variables;

  show status;



Log the slow queries. See:

  http://dev.mysql.com/doc/mysql/en/slow-query-log.html



 



[snip]

Hi everybody,

What are the possible reasons for runaway MySQL process on Sun Solaris, beyond 
joins on

fields with NULLs.

Regards,

Mikhail Berman

"Mikhail Berman" <[EMAIL PROTECTED]> wrote:

[snip]



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




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

Re: help please : ERROR 2006: MySQL server has gone away

[Gleb Paharenko]

Hello.



What's the value of the interactive_timeout system variable? See:

  http://dev.mysql.com/doc/mysql/en/server-system-variables.html

  http://dev.mysql.com/doc/mysql/en/gone-away.html





"Marois, David" <[EMAIL PROTECTED]> wrote:

> [-- text/plain, encoding 7bit, charset: iso-8859-1, 19 lines --]

> 

> Hi,

> I have this error when I am connected in mysql:

> 

> ERROR 2006: MySQL server has gone away. No connection. Trying to reconnect...

> 

> I only did this command: show databases;

> and received the error.

> 

> I noticed that if I don't send a command since 25 sec, I have the error.

> 

> Also, my variable wait_timeout = 3600.

> 

> Any ideas ?

> 

> 

> David Marois

>  [EMAIL PROTECTED]

> 

> 



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




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

Re: Removing 3.23, installing 5.02

[Gleb Paharenko]

Hello.



> the Add/Remove Packages (and the manual RPM command) both failed on

> multiple dependencies,



rpm has --force and --nodeps flags and you can

use -U instead of -i. See:

  man rpm



> little fingers and tried removing all the MySQL files manually. I think

> I got them all except the ones in /sur/lib; 'rm' on mysqlbug and

> mysql_config failed with a 'cannot lstat - no such file or directory'



Do these files really exist in that directory?



> (except those lib files). Are those what's causing this problem? How do



I think, that your rpm database has records about old packages. You can 

remove them using rpm -e --force --nodeps pkg_name.







Whil Hentzen <[EMAIL PROTECTED]> wrote:

> Hi folks,

> 

> I've got a Fedora Core 3 box that came with MySQL 3.23.xx, recently 

> updated to 3.23.58. I wanted to wipe the box clean and install 5.0, but 

> the Add/Remove Packages (and the manual RPM command) both failed on 

> multiple dependencies, so I (foolishly?) took matters into my own fat 

> little fingers and tried removing all the MySQL files manually. I think 

> I got them all except the ones in /sur/lib; 'rm' on mysqlbug and 

> mysql_config failed with a 'cannot lstat - no such file or directory' 

> message.

> 

> Anyway, running

>  [EMAIL PROTECTED]  rpm -i MySQL-server-5.0.2-0.i386.rpm

> failed with

>  warning: MySQL-server-5.0.2-0.i386.rpm: V3 DSA signature: NOKEY, key 

> ID 5072e1f5

>  error: Failed dependencies:

>MySQL conflicts with mysql-3.23.58-14.i386

>MySQL-server conflicts with mysql-server-3.23.58-14.i386

> 

> And I am stuck. I can't find any more files having to do with 3.23 

> (except those lib files). Are those what's causing this problem? How do 

> I get rid of them?

> 

> Thanks,

> 

> Whil

> 



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




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

SHOW TABLES Problem

[shaun thornburgh]

Hi,
The syntax for show tables from the manual is:
SHOW [FULL|OPEN] TABLES [FROM db_name] [LIKE 'pattern']
so whay doesnt the following query work:
SHOW TABLES LIKE REGEXP 'PID_[0-9]+';
Thanks for your help
Shaun

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

Res: Re: mysqld ended Can't get mysql running

[Debora Gomes.unitech]

050201 02:54:05  mysqld started
050201  2:54:05  InnoDB: Operating system error number 13 in a file 
operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
050201 02:54:05  mysqld ended

Att.

Débora S. Gomes
Analista de Tecnologia  - Redes
Agência Nacional do Petróleo - ANP
Tel.: 21 3804-0786




Michael Dykman <[EMAIL PROTECTED]>
01/02/2005 17:40

 
Para:   "Debora Gomes.unitech" <[EMAIL PROTECTED]>
cc: "\"MySQL List\"" 
Assunto:Re: mysqld ended Can't get mysql running
ANP


Debra,

 look in /opt/mysql/data for an error file which should 
contain some
hint of why it stopped..  could be many things..  permissions on the
data files, full disk broken paths, etc...

 - michael dykman

On Tue, 2005-02-01 at 15:43, Debora Gomes.unitech wrote:
> Hi ALL
> 
> 
> it reports back
> 
> # Starting mysqld daemon with databases from /opt/mysql/data
> STOPPING server from pid file /opt/mysql/data/acetona.anp.net.pid
> 050201 02:43:51  mysqld ended
> 
> 
> Att.
> 
> Débora S. Gomes
> Analista de Tecnologia  - Redes
> Agência Nacional do Petróleo - ANP
> Tel.: 21 3804-0786
-- 
 - michael dykman
 - [EMAIL PROTECTED]

Syntax diagram, where is it located in the doc?

[Thomas Sundberg]

Hi!

I'm looking for the syntax diagram for MySQL and can't find it. I have
downloaded the entire MySQL manual as one html page and searched it for the
definition of where_definition and I cant find it. Could somebody please
point me to a location where the complete syntax diagram can be found?

/Thomas


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

Cannot run MySQL

[A Z]

MySql 4.0.14

We tried to convert MyISAM table format to INNODB
format, it took forever to finish the process, someone
intervened and killed the process through Task
Manager.

Now can't run Mysqld-nt, running it with the --console
reports the followings.  Your help is appreciated.

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

c:\MySql\mysqld-nt --console
050201 11:26:22  InnoDB: Database was not shut down
normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 799702164
050201 11:26:22  InnoDB: Starting an apply batch of
log records to the database.
..
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16 17 18 19
20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
38 39 40 41 42 43 44 45 46
 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
65 66 67 68 69 70 71 72 7
3 74 75 76 77 78 050201 11:26:24  InnoDB: Assertion
failure in thread 1344 in fi
le ../innobase/include\page0page.ic line 482
InnoDB: Failing assertion: offs < UNIV_PAGE_SIZE
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to
mysql@lists.mysql.com






___ 
ALL-NEW Yahoo! Messenger - all new features - even more fun! 
http://uk.messenger.yahoo.com

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

Re: Best way to store numeric data?

[Gabriel PREDA]

You could use
DECIMAL(17,15) - but it will take you 17+2bytes per record...

but you could split the number in 2 parts
before the DOT . because you have 2 number use TINYINT-  takes 1 byte

BIGINT  - takes 8 bytes
  So
you have a total of 9 bytes
DOUBLE  - and it will take you 8 bytes per record

So DOUBLE it's a good choice...
Of course depending on what you do with the numbers the separation could
provve better even if you loose one byte pe record !

Gabriel PREDA

- Original Message - 
From: "Galen" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, February 02, 2005 9:15 AM
Subject: Best way to store numeric data?


> I've got a huge table going, and it's storing a load of numeric data.
> Basically, a percentage or single digit rank, one or two digits before
> the decimal and fifteen after, like this:
>
> 6.984789027653891
> 39.484789039053891
>
> What is the most efficient way to store these values? I will be
> frequently sorting results by them or using math with them, so speed is
> important, but I also don't want to be wasteful of disk space as I
> currently have over three quarters of a million records, with more to
> come.
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


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

Re: Problem with SELECT SQL_CALC_FOUND_ROWS

[Jigal van Hemert]

A quick look at the PHP site...

http://www.php.net/manual/en/faq.databases.php#faq.databases.upgraded

Regards, Jigal.

- Original Message - 
From: "Matt Babineau" <[EMAIL PROTECTED]>
To: "'MySQL General'" 
Sent: Tuesday, February 01, 2005 9:20 PM
Subject: Problem with SELECT SQL_CALC_FOUND_ROWS


> Hi All-
>
> I'm running a query that uses SQL_CALC_FOUND_ROWS for my search engine on
a
> real estate site. The problem is that I get an error when I run my query:
>
> "Warning mysql_query(): Unable to save result set in /clients/search.php"
>
> My Query is:
>
> SELECT SQL_CALC_FOUND_ROWS propertyData.*, propertyDataBulk.propertyDesc
> FROM propertyData LEFT JOIN propertyDataBulk ON propertyData.id =
> propertyDataBulk.propertyID WHERE state = 'CA' limit 0, 5
>
> Very odd that this happens, I am running MySQL 4.1.9
>
> Thanks,
>
> Matt Babineau
> Criticalcode
> w: http://www.criticalcode.com
> p: 858.733.0160
> e: [EMAIL PROTECTED]
>
>
>
> !DSPAM:41ffe4c9213322003044064!
>


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