Re: case sensitive table names in mysql-5.0.21

[Digvijoy Chatterjee]

Hello,

I am using MySql-5.0.21 on Suse-Linux-10, i created tables using
lowercase names
for example ;
mysql> create table a (id int);
Query OK, 0 rows affected (0.04 sec)

mysql> select * from A;
ERROR 1146 (42S02): Table 'mysql.A' doesn't exist
mysql> select * from a;
Empty set (0.01 sec)

I have upgraded from mysql-4.0.17 to 5.0.21 ,and all the ANSI-SQL
queries from my application using UPPERCASE table names are bombing, i
immediately had to stop 5.0.21 and restart mysql-4.0.17.
Is there any way to get around this problem and have a smooth upgrade.

Regards
Digz


The previous version of the App was on Windows(mysql-4.0.17) .this
ones on Linux .Does that make a difference ?

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

Re: ugly SQL for a report...

[Cory Robin]

Slightly new query..  Here's the new query and results of an explain..  
I'm thinking that some indexing would help..  However, I don't really 
know where to start.


---

EXPLAIN
   SELECT
   pnr.ID ID_pnr,
   pnr.reservationdatetime,
   pnr.conf_number,
   pnr.created_by,
   ( SELECT @pp_cnt := COUNT(1)
 FROM pnr_passengers pp
 WHERE pp.ID_pnr = pnr.ID
   ) AS pp_count,
   GROUP_CONCAT(pp.name_last,', ',pp.name_first
 ORDER BY name_last DESC SEPARATOR '') names,
   (SELECT SUM(pf.base_fare*( @pp_cnt ))
   FROM pnr_fares pf
   WHERE pf.ID_pnr=pnr.ID ) base_fare,
   (SELECT SUM(pf.tax*( @pp_cnt ))
 FROM pnr_fares pf
 WHERE pf.ID_pnr=pnr.ID ) tax ,
   (SELECT SUM(psf.segfee_amount*( @pp_cnt ))
 FROM pnr_segments_fees psf
 INNER JOIN pnr_segments pss
 ON psf.ID_segments=pss.ID
 WHERE psf.segfee_code='XF'
 AND pss.ID_pnr=pnr.ID ) XF ,
   (SELECT SUM(psf.segfee_amount*( @pp_cnt ))
 FROM pnr_segments_fees psf
 INNER JOIN pnr_segments pss
 ON psf.ID_segments=pss.ID
 WHERE psf.segfee_code='AY'
 AND pss.ID_pnr=pnr.ID ) AY ,
   (SELECT SUM(psf.segfee_amount*( @pp_cnt ))
 FROM pnr_segments_fees psf
 INNER JOIN pnr_segments pss
 ON psf.ID_segments=pss.ID
 WHERE psf.segfee_code='FS'
 AND pss.ID_pnr=pnr.ID ) FS ,
   (SELECT SUM(psf.segfee_amount*( @pp_cnt ))
 FROM pnr_segments_fees psf
 INNER JOIN pnr_segments pss
 ON psf.ID_segments=pss.ID
 WHERE psf.segfee_code='ZP'
 AND pss.ID_pnr=pnr.ID ) ZP ,
 (SELECT SUM(amount_value) service_fees
 FROM pnr_service_fees
 WHERE ID_pnr=pnr.ID) services ,
   (SELECT SUM(amount)
 FROM pnr_payments_credits
 WHERE ID_pnr=pnr.ID
 AND bln_payment='1'
 AND transaction_type='sale') payments,
   (SELECT SUM(amount)
 FROM pnr_payments_credits
 WHERE ID_pnr=pnr.ID
 AND bln_payment='1'
 AND transaction_type='credit') credits
   FROM pnr
   INNER JOIN pnr_passengers pp
   ON pnr.ID=pp.ID_pnr
   WHERE pnr.reservationdatetime>='2006-05-01 00:00:00'
   AND pnr.reservationdatetime<='2006-05-29 23:59:59'
   GROUP BY pnr.ID
   ORDER BY reservationdatetime

---

*** 1. row ***
  id: 1
 select_type: PRIMARY
   table: pp
type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
rows: 5326
   Extra: Using temporary; Using filesort
*** 2. row ***
  id: 1
 select_type: PRIMARY
   table: pnr
type: eq_ref
possible_keys: PRIMARY,both_id,resdt
 key: PRIMARY
 key_len: 8
 ref: S6_AirlineData.pp.ID_pnr
rows: 1
   Extra: Using where
*** 3. row ***
  id: 11
 select_type: DEPENDENT SUBQUERY
   table: pnr_payments_credits
type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
rows: 4865
   Extra: Using where
*** 4. row ***
  id: 10
 select_type: DEPENDENT SUBQUERY
   table: pnr_payments_credits
type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
rows: 4865
   Extra: Using where
*** 5. row ***
  id: 9
 select_type: DEPENDENT SUBQUERY
   table: pnr_service_fees
type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
rows: 701
   Extra: Using where
*** 6. row ***
  id: 8
 select_type: DEPENDENT SUBQUERY
   table: psf
type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
rows: 20248
   Extra: Using where
*** 7. row ***
  id: 8
 select_type: DEPENDENT SUBQUERY
   table: pss
type: eq_ref
possible_keys: PRIMARY
 key: PRIMARY
 key_len: 16
 ref: S6_AirlineData.psf.ID_segments,S6_AirlineData.pnr.ID
rows: 1
   Extra: Using index
*** 8. row ***
  id: 7
 select_type: DEPENDENT SUBQUERY
   table: psf
type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
rows: 20248
   Extra: Using where
*** 9. row ***
  id: 7
 select_type: DEPENDENT SUBQUERY
   table: pss
type: eq_ref
possible_keys: PRIMARY
 key: PRIMARY
 key_len: 16
 ref: S6_AirlineData.psf.ID_segments,S6_AirlineData.pnr.ID
rows: 1
   Extra: Using i

case sensitive table names in mysql-5.0.21

[Digvijoy Chatterjee]

Hello,

I am using MySql-5.0.21 on Suse-Linux-10, i created tables using
lowercase names
for example ;
mysql> create table a (id int);
Query OK, 0 rows affected (0.04 sec)

mysql> select * from A;
ERROR 1146 (42S02): Table 'mysql.A' doesn't exist
mysql> select * from a;
Empty set (0.01 sec)

I have upgraded from mysql-4.0.17 to 5.0.21 ,and all the ANSI-SQL
queries from my application using UPPERCASE table names are bombing, i
immediately had to stop 5.0.21 and restart mysql-4.0.17.
Is there any way to get around this problem and have a smooth upgrade.

Regards
Digz

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

Re: where more than 2 column not in a table

[if04029]

CREATE TABLE `club_get_match` (
  `id_match` bigint(100) NOT NULL default '0',
  `name_club` varchar(30) NOT NULL default '',
  `status` varchar(30) default NULL,
  `prediction_match` varchar(5) NOT NULL default '',
  `equal_match` char(3) NOT NULL default '',
  `score` varchar(50) default NULL,
  `scorerr` varchar(10) default NULL,
  PRIMARY KEY  (`id_match`,`name_club`)
);

CREATE TABLE `detail_club_member` (
  `id_member` bigint(20) NOT NULL default '0',
  `choose` varchar(50) NOT NULL default '',
  `name_club` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`id_member`,`name_club`)
);

CREATE TABLE `wager` (
  `id_match` bigint(100) NOT NULL default '0',
  `id_member` varchar(8) NOT NULL default '',
  `total_wager` bigint(10) NOT NULL default '0',
  `total_money` bigint(20) NOT NULL default '0',
  `name_club` varchar(20) NOT NULL default '',
  `time_update` datetime default '-00-00 00:00:00',
  PRIMARY KEY  (`id_match`,`id_member`,`name_club`)
);

I want to select a list, where it was registered in tbl club_get_match with Id-
Member in tbl detail_club_member, but it's not registered in tbl wager.










Quoting Peter Brawley <[EMAIL PROTECTED]>:

> [EMAIL PROTECTED] wrote:
> > Sory if my question makes you confused
> > I've a problem with my sintax,
> > I don't know how to select a list, whether it's not registered in another
> 
> > table, but it have 2 IDs.
> >   
> Not enough info. Show the Create Table statements, a bit of data, and 
> the desired result.
> 
> PB
> > I hope you will help me because I really need it for my study.
> >
> > Thank you in advance,
> >
> > regards,
> > Eva Panjaitan
> > 11104029 
> > Student of Del Polytechnic of Informatics 
> > Sitoluama - Laguboti 
> > Toba Samosir 
> > Sumatera Utara 
> >
> >   
> 
> 
> -- 
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.7.3/350 - Release Date: 5/28/2006
> 
> 

regards,

Eva Panjaitan
11104029 
Student of Del Polytechnic of Informatics 
Sitoluama - Laguboti 
Toba Samosir 
Sumatera Utara 

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

select 9000000000000.10*19.80 wrong result 178200000000001.97

[wang shuming]

Hi,
 select 9.10*19.80 wrong result 1782001.97
 if <= 9000.10*19.80 right  result178201.98
This is similiar to sum(qty*unitpri) result .
Best regards
Shuming Wang

Re: MySql Limitations??

[peter lovatt]

Hi

We have tables of 1.5M rows 25 fields, in heavy use with frequent inserts
and updates on a P4 with 1GB of RAM. Performance is fine, and the table size
is 400MB, so you should not have any problems.

Peter


On 29/05/06, Chris W <[EMAIL PROTECTED]> wrote:


Harish TM wrote:

> hi...
>   I need to store something like a couple of million rows is a MySql
> table. Is that ok or do I have to split them up. I intend to index
> each of
> the columns that I will need to access so as to speed up access.
> Insertion
> will be done only when there is very little or no load on the server and
> time for this is not really a factor. I also do not have any
> constraints on
> disk space.  Please let me know if I can just use MySql as it is
> or if I
> need to make some changes

I have a table with around 900,000 rows and I know others have tables
with many millions of rows, I think I read some even have tables with
billions or rows.  The limitation you hit is generally because of your
OS.  Most OSs have an upper limit on file size.  Since a table is stored
in a file, that file size limitation of the OS is what generally limits
the table size.  In which case the number of rows depends on the size of
the rows.


--
Chris W
KE5GIX

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


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

Re: i'm at a complete loss?

[Daniel McQuay]

Well, good news! after renaming the mysql to mysql.old and running
mysql_install_db --user=mysql and then restarting the machine I am now able
to run mysql as root by typing mysql -u root -p and just hitting enter when
prompted for a password.

Now I guess I just got to set the password (currently reading about how to
do that) and I good from there.

Thanks for all your help guys.

On 5/29/06, Logan, David (SST - Adelaide) <[EMAIL PROTECTED]> wrote:


 Ah, this is a little different. I know there are a couple of OS's (redhat
AFAIK) that place the mysql socket in a different location

http://dev.mysql.com/doc/refman/4.1/en/problems-with-mysql-sock.html

Your socket has probably been placed in a spot where the server is not
expecting it. You may need to update the my.cnf files to reflect the
correct location or add a --socket option to the server when it starts.

Regards

---
** _/ **  David Logan
***   _/ ***  ITO Delivery Specialist – Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia
invent
---



 --
*From:* Daniel McQuay [mailto:[EMAIL PROTECTED]
*Sent:* Tuesday, 30 May 2006 10:39 AM
*To:* Logan, David (SST - Adelaide)

*Cc:* David Griffiths; mysql@lists.mysql.com
*Subject:* Re: i'm at a complete loss?

Nah, that's not the case my friend. I made sure that when I installed
mysql that the old one was completely removed. But, I do think I figured
something out. I was reading the MySQL documentation and it said that if I
was having problems with mysql_install_db that I should move the mysql
directory, the one with all my information, to mysql.old and try the
mysql_install_db. I did that and every thins seemed to go just fine.

Now my problem is with this:


boxster# /usr/local/bin/mysqladmin -u root password 'flNT3b4c'
/usr/local/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (2)'

Check that mysqld is running and that the socket: '/tmp/mysql.sock'
exists!


Have you ever seen that error before? Now I cant connect at all. :(


On 5/29/06, Logan, David (SST - Adelaide) <[EMAIL PROTECTED]> wrote:
>
> Hi Daniel,
>
> Is this actually the instance that you have just installed? I know that
> sounds trite but if it is running during the installation maybe it
> hasn't been able to start the new server and you are still trying to
> connect to the old one.
>
> Kill the server currently running, restart it and try again.
>
> Regards
>
> ---
> ** _/ **  David Logan
> ***   _/ ***  ITO Delivery Specialist - Database
> *_/*  Hewlett-Packard Australia Ltd
> _/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
>    _/  _/  _/  _/     Desk:   +618 8408 4273
>   _/  _/  _/_/_/  Mobile: 0417 268 665
> *_/   **
> **  _/    Postal: 148 Frome Street,
>    _/ **  Adelaide SA 5001
>   Australia
> invent
> ---
>
> -Original Message-
> From: Daniel McQuay [mailto: [EMAIL PROTECTED]
> Sent: Tuesday, 30 May 2006 10:20 AM
> Cc: David Griffiths; mysql@lists.mysql.com
> Subject: Re: i'm at a complete loss?
>
> Yeah when I ran it the first time I get this message:
>
> 
> boxster# /usr/local/bin/mysql_install_db --user=mysql
> Installing all prepared tables
> Fill help tables
>
> To start mysqld at boot time you have to copy support-files/mysql.server
> to the right place for your system
>
> PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
> To do so, start the server, then issue the following commands:
> /usr/local/bin/mysqladmin -u root password 'new-password'
> /usr/local/bin/mysqladmin -u root -h boxster.mydomain.com password
> 'new-password'
> See the manual for more instructions.
>
> NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
> the /usr/local/bin/mysql_fix_privilege_tables. Otherwise you will not be
>
> able to use the new GRANT command!
>
> You can start the MySQL daemon with:
> cd /usr/local ; /usr/local/bin/mysqld_safe &
>
> You can test the MySQL daemon with the benchmarks in the 'sql-bench'
> directory:
> cd sql-bench ; perl run-all-tests
>
> Please report any problems with the /usr/local/bin/mysqlbug script!
>
> The late

RE: i'm at a complete loss?

[Logan, David (SST - Adelaide)]

Ah, this is a little different. I know there are a couple of OS's
(redhat AFAIK) that place the mysql socket in a different location
 
http://dev.mysql.com/doc/refman/4.1/en/problems-with-mysql-sock.html
 
Your socket has probably been placed in a spot where the server is not
expecting it. You may need to update the my.cnf files to reflect the
correct location or add a --socket option to the server when it starts.
 
Regards
---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia 
invent   
---
 



From: Daniel McQuay [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 30 May 2006 10:39 AM
To: Logan, David (SST - Adelaide)
Cc: David Griffiths; mysql@lists.mysql.com
Subject: Re: i'm at a complete loss?


Nah, that's not the case my friend. I made sure that when I installed
mysql that the old one was completely removed. But, I do think I figured
something out. I was reading the MySQL documentation and it said that if
I was having problems with mysql_install_db that I should move the mysql
directory, the one with all my information, to mysql.old and try the
mysql_install_db. I did that and every thins seemed to go just fine.

Now my problem is with this:


boxster# /usr/local/bin/mysqladmin -u root password 'flNT3b4c'
/usr/local/bin/mysqladmin: connect to server at 'localhost' failed 
error: 'Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (2)'

Check that mysqld is running and that the socket: '/tmp/mysql.sock'
exists!


Have you ever seen that error before? Now I cant connect at all. :( 



On 5/29/06, Logan, David (SST - Adelaide) <[EMAIL PROTECTED]> wrote: 

Hi Daniel,

Is this actually the instance that you have just installed? I
know that
sounds trite but if it is running during the installation maybe
it
hasn't been able to start the new server and you are still
trying to 
connect to the old one.

Kill the server currently running, restart it and try again.

Regards

---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED] 
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001 
  Australia
invent
---

-Original Message-
From: Daniel McQuay [mailto: [EMAIL PROTECTED]
 ]
Sent: Tuesday, 30 May 2006 10:20 AM
Cc: David Griffiths; mysql@lists.mysql.com
Subject: Re: i'm at a complete loss?

Yeah when I ran it the first time I get this message: 


boxster# /usr/local/bin/mysql_install_db --user=mysql
Installing all prepared tables
Fill help tables

To start mysqld at boot time you have to copy
support-files/mysql.server
to the right place for your system 

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/bin/mysqladmin -u root password 'new-password'
/usr/local/bin/mysqladmin -u root -h boxster.mydomain.com
password
'new-password'
See the manual for more instructions.

NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should
run
the /usr/local/bin/mysql_fix_privilege_tables. Otherwise you
will not be 
able to use the new GRANT command!

You can start the MySQL daemon with:
cd /usr/local ; /usr/local/bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the
'sql-bench'
directory: 
cd sql-bench ; perl run-all-tests

Please report any problems with the /usr/local/bin/mysqlbug
script!

The la

Re: i'm at a complete loss?

[Daniel McQuay]

Nah, that's not the case my friend. I made sure that when I installed mysql
that the old one was completely removed. But, I do think I figured something
out. I was reading the MySQL documentation and it said that if I was having
problems with mysql_install_db that I should move the mysql directory, the
one with all my information, to mysql.old and try the mysql_install_db. I
did that and every thins seemed to go just fine.

Now my problem is with this:


boxster# /usr/local/bin/mysqladmin -u root password 'flNT3b4c'
/usr/local/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock'
(2)'

Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!


Have you ever seen that error before? Now I cant connect at all. :(


On 5/29/06, Logan, David (SST - Adelaide) <[EMAIL PROTECTED]> wrote:


Hi Daniel,

Is this actually the instance that you have just installed? I know that
sounds trite but if it is running during the installation maybe it
hasn't been able to start the new server and you are still trying to
connect to the old one.

Kill the server currently running, restart it and try again.

Regards

---
** _/ **  David Logan
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia
invent
---

-Original Message-
From: Daniel McQuay [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 30 May 2006 10:20 AM
Cc: David Griffiths; mysql@lists.mysql.com
Subject: Re: i'm at a complete loss?

Yeah when I ran it the first time I get this message:


boxster# /usr/local/bin/mysql_install_db --user=mysql
Installing all prepared tables
Fill help tables

To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/bin/mysqladmin -u root password 'new-password'
/usr/local/bin/mysqladmin -u root -h boxster.mydomain.com password
'new-password'
See the manual for more instructions.

NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
the /usr/local/bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!

You can start the MySQL daemon with:
cd /usr/local ; /usr/local/bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the 'sql-bench'
directory:
cd sql-bench ; perl run-all-tests

Please report any problems with the /usr/local/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
boxster#


I then follow the directions on setting a password for root but I get
basically the same error message.


boxster# /usr/local/bin/mysqladmin -u root password ''
/usr/local/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
boxster#


Even though I get an error message I tried running cd /usr/local ;
/usr/local/bin/mysqld_safe but I then get this error:


boxster# cd /usr/local ; /usr/local/bin/mysqld_safe &
[1] 33209
boxster# Starting mysqld daemon with databases from /usr/local/var
STOPPING server from pid file /usr/local/var/boxster.mydomain.com.pid
060529 20:45:47  mysqld ended


[1]Done  ( cd /usr/local;
/usr/local/bin/mysqld_safe )
boxster#


I went to that directory and read the error log and it says I have an
instance of mysqld running, which I do. ps auxw | grep myslqd show it
there
running.

Thanks again!

On 5/29/06, Logan, David (SST - Adelaide) <[EMAIL PROTECTED]> wrote:
>
> Hi Daniel,
>
> Sounds like there are a few records missing out of the mysql tables.
Did
> you run mysql_install_db?
>
> http://dev.mysql.com/doc/refman/4.1/en/unix-post-installation.html
>
> Regards
>
> ---
> ** _/ **  David Logan
> ***   _/ ***  ITO Delivery Specialist - Database
> *_/*  Hewlett-Packard Australia Ltd
> _/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
>    _/  _/  _/  _/     Desk:   +618 8408 4273
>   _/  _/  _/_/_/  Mobile: 0417 268 665
> *_/   **
> **  _/    Postal: 148 Frome Street,
>    _/ **  Adelaid

RE: i'm at a complete loss?

[Logan, David (SST - Adelaide)]

Hi Daniel,

Is this actually the instance that you have just installed? I know that
sounds trite but if it is running during the installation maybe it
hasn't been able to start the new server and you are still trying to
connect to the old one.

Kill the server currently running, restart it and try again.

Regards

---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia 
invent   
---

-Original Message-
From: Daniel McQuay [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 30 May 2006 10:20 AM
Cc: David Griffiths; mysql@lists.mysql.com
Subject: Re: i'm at a complete loss?

Yeah when I ran it the first time I get this message:


boxster# /usr/local/bin/mysql_install_db --user=mysql
Installing all prepared tables
Fill help tables

To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/bin/mysqladmin -u root password 'new-password'
/usr/local/bin/mysqladmin -u root -h boxster.mydomain.com password
'new-password'
See the manual for more instructions.

NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
the /usr/local/bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!

You can start the MySQL daemon with:
cd /usr/local ; /usr/local/bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the 'sql-bench'
directory:
cd sql-bench ; perl run-all-tests

Please report any problems with the /usr/local/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
boxster#


I then follow the directions on setting a password for root but I get
basically the same error message.


boxster# /usr/local/bin/mysqladmin -u root password ''
/usr/local/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
boxster#


Even though I get an error message I tried running cd /usr/local ;
/usr/local/bin/mysqld_safe but I then get this error:


boxster# cd /usr/local ; /usr/local/bin/mysqld_safe &
[1] 33209
boxster# Starting mysqld daemon with databases from /usr/local/var
STOPPING server from pid file /usr/local/var/boxster.mydomain.com.pid
060529 20:45:47  mysqld ended


[1]Done  ( cd /usr/local;
/usr/local/bin/mysqld_safe )
boxster#


I went to that directory and read the error log and it says I have an
instance of mysqld running, which I do. ps auxw | grep myslqd show it
there
running.

Thanks again!

On 5/29/06, Logan, David (SST - Adelaide) <[EMAIL PROTECTED]> wrote:
>
> Hi Daniel,
>
> Sounds like there are a few records missing out of the mysql tables.
Did
> you run mysql_install_db?
>
> http://dev.mysql.com/doc/refman/4.1/en/unix-post-installation.html
>
> Regards
>
> ---
> ** _/ **  David Logan
> ***   _/ ***  ITO Delivery Specialist - Database
> *_/*  Hewlett-Packard Australia Ltd
> _/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
>    _/  _/  _/  _/     Desk:   +618 8408 4273
>   _/  _/  _/_/_/  Mobile: 0417 268 665
> *_/   **
> **  _/    Postal: 148 Frome Street,
>    _/ **  Adelaide SA 5001
>   Australia
> invent
> ---
>
> -Original Message-
> From: Daniel McQuay [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, 30 May 2006 9:53 AM
> To: David Griffiths
> Cc: mysql@lists.mysql.com
> Subject: Re: i'm at a complete loss?
>
> When I log in using mysql which by the way is the only user that I can
> log
> into with I get
> 
> mysql> USE mysql;
> ERROR 1044 (42000): Access denied for user ''@'localhost' to database
> 'mysql'
> 
> I have to log in to using mysql -u mysql and it drops me into the
mysql
> command line. It seems as though the user "mysql" has absolutely no
> privileges to do any thing and I cant log in as root. So I have no
idea
> what
> I did wrong but this is the third time I installed MySQL

Re: i'm at a complete loss?

[Daniel McQuay]

Just for shits and giggles I tried boxster# /usr/local/bin/mysql_install_db
--user=root and then tried logging in as root mysql -u root and now it says


ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (2)


I googled that and found no resolve to the problem. FYI I had seen that
error before on my machine and tried to solve, but with no luck.

On 5/29/06, Daniel McQuay <[EMAIL PROTECTED]> wrote:


Yeah when I ran it the first time I get this message:


boxster# /usr/local/bin/mysql_install_db --user=mysql
Installing all prepared tables
Fill help tables

To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/bin/mysqladmin -u root password 'new-password'
/usr/local/bin/mysqladmin -u root -h boxster.mydomain.com password
'new-password'
See the manual for more instructions.

NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
the /usr/local/bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!

You can start the MySQL daemon with:
cd /usr/local ; /usr/local/bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the 'sql-bench'
directory:
cd sql-bench ; perl run-all-tests

Please report any problems with the /usr/local/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
boxster#


I then follow the directions on setting a password for root but I get
basically the same error message.


boxster# /usr/local/bin/mysqladmin -u root password ''
/usr/local/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
boxster#


Even though I get an error message I tried running cd /usr/local ;
/usr/local/bin/mysqld_safe but I then get this error:


boxster# cd /usr/local ; /usr/local/bin/mysqld_safe &
[1] 33209
boxster# Starting mysqld daemon with databases from /usr/local/var
STOPPING server from pid file /usr/local/var/boxster.mydomain.com.pid
060529 20:45:47  mysqld ended


[1]Done  ( cd /usr/local;
/usr/local/bin/mysqld_safe )
boxster#


I went to that directory and read the error log and it says I have an
instance of mysqld running, which I do. ps auxw | grep myslqd show it there
running.

Thanks again!


On 5/29/06, Logan, David (SST - Adelaide) <[EMAIL PROTECTED]> wrote:
>
> Hi Daniel,
>
> Sounds like there are a few records missing out of the mysql tables. Did
> you run mysql_install_db?
>
> http://dev.mysql.com/doc/refman/4.1/en/unix-post-installation.html
>
> Regards
>
> ---
> ** _/ **  David Logan
> ***   _/ ***  ITO Delivery Specialist - Database
> *_/*  Hewlett-Packard Australia Ltd
> _/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
>    _/  _/  _/  _/     Desk:   +618 8408 4273
>   _/  _/  _/_/_/  Mobile: 0417 268 665
> *_/   **
> **  _/    Postal: 148 Frome Street,
>    _/ **  Adelaide SA 5001
>   Australia
> invent
> ---
>
> -Original Message-
> From: Daniel McQuay [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, 30 May 2006 9:53 AM
> To: David Griffiths
> Cc: mysql@lists.mysql.com
> Subject: Re: i'm at a complete loss?
>
> When I log in using mysql which by the way is the only user that I can
> log
> into with I get
> 
> mysql> USE mysql;
> ERROR 1044 (42000): Access denied for user ''@'localhost' to database
> 'mysql'
> 
> I have to log in to using mysql -u mysql and it drops me into the mysql
> command line. It seems as though the user "mysql" has absolutely no
> privileges to do any thing and I cant log in as root. So I have no idea
> what
> I did wrong but this is the third time I installed MySQL41 and I don't
> think
> reinstalling would do any thing different.
>
> Thanks go out to every one for trying to help me, but like I said I have
> no
> idea as to whats wrong.
>
> On 5/29/06, David Griffiths <[EMAIL PROTECTED]> wrote:
> >
> > Log into the server, and type, "use mysql;" without the quotes.
> >
> > Look at the "user" table - that defines what user can connect to the
> > database, the ip addresses they can use, and the password
> > they must provide.
> >
> > For example, you could enter,
> >
> > "INSERT INTO USER (host, user, password)
> > values ('127.0.0.1', 'mysql', password(mysql));
> >
> > and
> >
> > "INSERT INTO USER (host, user, password)
> > values ('localhost', 'mysql', password(mysql));
> >
> > Don't forget to do a "flush privileges;" after

Re: i'm at a complete loss?

[Daniel McQuay]

Yeah when I ran it the first time I get this message:


boxster# /usr/local/bin/mysql_install_db --user=mysql
Installing all prepared tables
Fill help tables

To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/bin/mysqladmin -u root password 'new-password'
/usr/local/bin/mysqladmin -u root -h boxster.mydomain.com password
'new-password'
See the manual for more instructions.

NOTE:  If you are upgrading from a MySQL <= 3.22.10 you should run
the /usr/local/bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!

You can start the MySQL daemon with:
cd /usr/local ; /usr/local/bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the 'sql-bench'
directory:
cd sql-bench ; perl run-all-tests

Please report any problems with the /usr/local/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
boxster#


I then follow the directions on setting a password for root but I get
basically the same error message.


boxster# /usr/local/bin/mysqladmin -u root password ''
/usr/local/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
boxster#


Even though I get an error message I tried running cd /usr/local ;
/usr/local/bin/mysqld_safe but I then get this error:


boxster# cd /usr/local ; /usr/local/bin/mysqld_safe &
[1] 33209
boxster# Starting mysqld daemon with databases from /usr/local/var
STOPPING server from pid file /usr/local/var/boxster.mydomain.com.pid
060529 20:45:47  mysqld ended


[1]Done  ( cd /usr/local;
/usr/local/bin/mysqld_safe )
boxster#


I went to that directory and read the error log and it says I have an
instance of mysqld running, which I do. ps auxw | grep myslqd show it there
running.

Thanks again!

On 5/29/06, Logan, David (SST - Adelaide) <[EMAIL PROTECTED]> wrote:


Hi Daniel,

Sounds like there are a few records missing out of the mysql tables. Did
you run mysql_install_db?

http://dev.mysql.com/doc/refman/4.1/en/unix-post-installation.html

Regards

---
** _/ **  David Logan
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia
invent
---

-Original Message-
From: Daniel McQuay [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 30 May 2006 9:53 AM
To: David Griffiths
Cc: mysql@lists.mysql.com
Subject: Re: i'm at a complete loss?

When I log in using mysql which by the way is the only user that I can
log
into with I get

mysql> USE mysql;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database
'mysql'

I have to log in to using mysql -u mysql and it drops me into the mysql
command line. It seems as though the user "mysql" has absolutely no
privileges to do any thing and I cant log in as root. So I have no idea
what
I did wrong but this is the third time I installed MySQL41 and I don't
think
reinstalling would do any thing different.

Thanks go out to every one for trying to help me, but like I said I have
no
idea as to whats wrong.

On 5/29/06, David Griffiths <[EMAIL PROTECTED]> wrote:
>
> Log into the server, and type, "use mysql;" without the quotes.
>
> Look at the "user" table - that defines what user can connect to the
> database, the ip addresses they can use, and the password
> they must provide.
>
> For example, you could enter,
>
> "INSERT INTO USER (host, user, password)
> values ('127.0.0.1', 'mysql', password(mysql));
>
> and
>
> "INSERT INTO USER (host, user, password)
> values ('localhost', 'mysql', password(mysql));
>
> Don't forget to do a "flush privileges;" afterwards (again, no
quotes);
>
> The mysql schema is thoroughly (but dryly) documented @
> http://dev.mysql.com
>
> David
>
> Daniel McQuay wrote:
> > Thanks Greg, I did try that here is what happened.
> >
> > boxster# mysql -u root -p
> > Enter password:
> > ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
> > password: NO)
> > boxster#
> >
> > So I really just don't know what to do from here. I'm up for any
ideas
> > if
> > any one has 'em.
> >
> > On 5/29/06, Greg Maruszeczka <[EMAIL PROTECTED]> wrote:
> >>
> >> On Sun, 28 May 2006 20:17:53 -0400

RE: i'm at a complete loss?

[Logan, David (SST - Adelaide)]

Hi Daniel,

Sounds like there are a few records missing out of the mysql tables. Did
you run mysql_install_db?

http://dev.mysql.com/doc/refman/4.1/en/unix-post-installation.html

Regards

---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia 
invent   
---

-Original Message-
From: Daniel McQuay [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 30 May 2006 9:53 AM
To: David Griffiths
Cc: mysql@lists.mysql.com
Subject: Re: i'm at a complete loss?

When I log in using mysql which by the way is the only user that I can
log
into with I get

mysql> USE mysql;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database
'mysql'

I have to log in to using mysql -u mysql and it drops me into the mysql
command line. It seems as though the user "mysql" has absolutely no
privileges to do any thing and I cant log in as root. So I have no idea
what
I did wrong but this is the third time I installed MySQL41 and I don't
think
reinstalling would do any thing different.

Thanks go out to every one for trying to help me, but like I said I have
no
idea as to whats wrong.

On 5/29/06, David Griffiths <[EMAIL PROTECTED]> wrote:
>
> Log into the server, and type, "use mysql;" without the quotes.
>
> Look at the "user" table - that defines what user can connect to the
> database, the ip addresses they can use, and the password
> they must provide.
>
> For example, you could enter,
>
> "INSERT INTO USER (host, user, password)
> values ('127.0.0.1', 'mysql', password(mysql));
>
> and
>
> "INSERT INTO USER (host, user, password)
> values ('localhost', 'mysql', password(mysql));
>
> Don't forget to do a "flush privileges;" afterwards (again, no
quotes);
>
> The mysql schema is thoroughly (but dryly) documented @
> http://dev.mysql.com
>
> David
>
> Daniel McQuay wrote:
> > Thanks Greg, I did try that here is what happened.
> >
> > boxster# mysql -u root -p
> > Enter password:
> > ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
> > password: NO)
> > boxster#
> >
> > So I really just don't know what to do from here. I'm up for any
ideas
> > if
> > any one has 'em.
> >
> > On 5/29/06, Greg Maruszeczka <[EMAIL PROTECTED]> wrote:
> >>
> >> On Sun, 28 May 2006 20:17:53 -0400
> >> "Daniel McQuay" <[EMAIL PROTECTED]> wrote:
> >>
> >> > Hello list,
> >> >
> >> > I just installed MySQL on my FreeBSD box here at home and am
having
> > a
> >> > few problems with privileges. I can connect to mysql using; mysql
-u
> >> > mysql and there is no password needed. However, when I try to
> > connect
> >> > to the server using root; mysql -u root I get an error;
> >> > 
> >> > ERROR 1045 (28000): Access denied for user 'root'@'localhost'
(using
> >> > password: NO)
> >> > 
> >> > so reading that it appears that I need a password so i try; mysql
-u
> >> > root -p it prompts me for my root pass and when I put it in it
does
> >> > that same thing above but with (using password: YES).
> >> >
> >> > I went to the the MySQL web site and read 2.10.3 Securing the
> > Initial
> >> > MySQL Accounts and tried following along with that but with no
luck.
> >> > When checking google for help I read a lot about the initial
> >> > installation. Something about /usr/local/bin/mysql_install_db
will
> >> > install a privilege table.
> >> >
> >> > I installed mysql using this guide here
> >> > http://raybdbomb.com/p/mysql-install-on-freebsd.html and every
thing
> >> > seemed to go well but like I said I keep getting this error. Is
> > there
> >> > something else I should do? Any help on this would be MUCH
> >> > appreciated.
> >> >
> >>
> >>
> >> Hi,
> >>
> >> Wasn't clear to me in reading your post that you did this so here
> >> goes:
> >>
> >> Did you actually set a root password for mysql using a GRANT
> >> statement after logging in with the default BLANK password?
> >>
> >> mysql -u root -p [then just hit enter]
> >>
> >> HTH,
> >> G
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> >>
> >>
> >
> >
>



-- 
Daniel McQuay
[EMAIL PROTECTED]
boxster.homelinux.org
814.825.0847

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

Re: i'm at a complete loss?

[Daniel McQuay]

When I log in using mysql which by the way is the only user that I can log
into with I get

mysql> USE mysql;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database
'mysql'

I have to log in to using mysql -u mysql and it drops me into the mysql
command line. It seems as though the user "mysql" has absolutely no
privileges to do any thing and I cant log in as root. So I have no idea what
I did wrong but this is the third time I installed MySQL41 and I don't think
reinstalling would do any thing different.

Thanks go out to every one for trying to help me, but like I said I have no
idea as to whats wrong.

On 5/29/06, David Griffiths <[EMAIL PROTECTED]> wrote:


Log into the server, and type, "use mysql;" without the quotes.

Look at the "user" table - that defines what user can connect to the
database, the ip addresses they can use, and the password
they must provide.

For example, you could enter,

"INSERT INTO USER (host, user, password)
values ('127.0.0.1', 'mysql', password(mysql));

and

"INSERT INTO USER (host, user, password)
values ('localhost', 'mysql', password(mysql));

Don't forget to do a "flush privileges;" afterwards (again, no quotes);

The mysql schema is thoroughly (but dryly) documented @
http://dev.mysql.com

David

Daniel McQuay wrote:
> Thanks Greg, I did try that here is what happened.
>
> boxster# mysql -u root -p
> Enter password:
> ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
> password: NO)
> boxster#
>
> So I really just don't know what to do from here. I'm up for any ideas
> if
> any one has 'em.
>
> On 5/29/06, Greg Maruszeczka <[EMAIL PROTECTED]> wrote:
>>
>> On Sun, 28 May 2006 20:17:53 -0400
>> "Daniel McQuay" <[EMAIL PROTECTED]> wrote:
>>
>> > Hello list,
>> >
>> > I just installed MySQL on my FreeBSD box here at home and am having
> a
>> > few problems with privileges. I can connect to mysql using; mysql -u
>> > mysql and there is no password needed. However, when I try to
> connect
>> > to the server using root; mysql -u root I get an error;
>> > 
>> > ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
>> > password: NO)
>> > 
>> > so reading that it appears that I need a password so i try; mysql -u
>> > root -p it prompts me for my root pass and when I put it in it does
>> > that same thing above but with (using password: YES).
>> >
>> > I went to the the MySQL web site and read 2.10.3 Securing the
> Initial
>> > MySQL Accounts and tried following along with that but with no luck.
>> > When checking google for help I read a lot about the initial
>> > installation. Something about /usr/local/bin/mysql_install_db will
>> > install a privilege table.
>> >
>> > I installed mysql using this guide here
>> > http://raybdbomb.com/p/mysql-install-on-freebsd.html and every thing
>> > seemed to go well but like I said I keep getting this error. Is
> there
>> > something else I should do? Any help on this would be MUCH
>> > appreciated.
>> >
>>
>>
>> Hi,
>>
>> Wasn't clear to me in reading your post that you did this so here
>> goes:
>>
>> Did you actually set a root password for mysql using a GRANT
>> statement after logging in with the default BLANK password?
>>
>> mysql -u root -p [then just hit enter]
>>
>> HTH,
>> G
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>>
>>
>
>





--
Daniel McQuay
[EMAIL PROTECTED]
boxster.homelinux.org
814.825.0847

Re: i'm at a complete loss?

[David Griffiths]

Log into the server, and type, "use mysql;" without the quotes.

Look at the "user" table - that defines what user can connect to the database, the ip addresses they can use, and the password 
they must provide.


For example, you could enter,

"INSERT INTO USER (host, user, password)
values ('127.0.0.1', 'mysql', password(mysql));

and

"INSERT INTO USER (host, user, password)
values ('localhost', 'mysql', password(mysql));

Don't forget to do a "flush privileges;" afterwards (again, no quotes);

The mysql schema is thoroughly (but dryly) documented @ http://dev.mysql.com

David

Daniel McQuay wrote:

Thanks Greg, I did try that here is what happened.

boxster# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: NO)
boxster#

So I really just don't know what to do from here. I'm up for any ideas
if
any one has 'em.

On 5/29/06, Greg Maruszeczka <[EMAIL PROTECTED]> wrote:


On Sun, 28 May 2006 20:17:53 -0400
"Daniel McQuay" <[EMAIL PROTECTED]> wrote:

> Hello list,
>
> I just installed MySQL on my FreeBSD box here at home and am having

a

> few problems with privileges. I can connect to mysql using; mysql -u
> mysql and there is no password needed. However, when I try to

connect

> to the server using root; mysql -u root I get an error;
> 
> ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
> password: NO)
> 
> so reading that it appears that I need a password so i try; mysql -u
> root -p it prompts me for my root pass and when I put it in it does
> that same thing above but with (using password: YES).
>
> I went to the the MySQL web site and read 2.10.3 Securing the

Initial

> MySQL Accounts and tried following along with that but with no luck.
> When checking google for help I read a lot about the initial
> installation. Something about /usr/local/bin/mysql_install_db will
> install a privilege table.
>
> I installed mysql using this guide here
> http://raybdbomb.com/p/mysql-install-on-freebsd.html and every thing
> seemed to go well but like I said I keep getting this error. Is

there

> something else I should do? Any help on this would be MUCH
> appreciated.
>


Hi,

Wasn't clear to me in reading your post that you did this so here
goes:

Did you actually set a root password for mysql using a GRANT
statement after logging in with the default BLANK password?

mysql -u root -p [then just hit enter]

HTH,
G

--
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: i'm at a complete loss?

[Daniel McQuay]

Thanks Greg, I did try that here is what happened.

boxster# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: NO)
boxster#

So I really just don't know what to do from here. I'm up for any ideas if
any one has 'em.

On 5/29/06, Greg Maruszeczka <[EMAIL PROTECTED]> wrote:


On Sun, 28 May 2006 20:17:53 -0400
"Daniel McQuay" <[EMAIL PROTECTED]> wrote:

> Hello list,
>
> I just installed MySQL on my FreeBSD box here at home and am having a
> few problems with privileges. I can connect to mysql using; mysql -u
> mysql and there is no password needed. However, when I try to connect
> to the server using root; mysql -u root I get an error;
> 
> ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
> password: NO)
> 
> so reading that it appears that I need a password so i try; mysql -u
> root -p it prompts me for my root pass and when I put it in it does
> that same thing above but with (using password: YES).
>
> I went to the the MySQL web site and read 2.10.3 Securing the Initial
> MySQL Accounts and tried following along with that but with no luck.
> When checking google for help I read a lot about the initial
> installation. Something about /usr/local/bin/mysql_install_db will
> install a privilege table.
>
> I installed mysql using this guide here
> http://raybdbomb.com/p/mysql-install-on-freebsd.html and every thing
> seemed to go well but like I said I keep getting this error. Is there
> something else I should do? Any help on this would be MUCH
> appreciated.
>


Hi,

Wasn't clear to me in reading your post that you did this so here
goes:

Did you actually set a root password for mysql using a GRANT
statement after logging in with the default BLANK password?

mysql -u root -p [then just hit enter]

HTH,
G

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





--
Daniel McQuay
[EMAIL PROTECTED]
boxster.homelinux.org
814.825.0847

Re: ugly SQL for a report...

[Peter Brawley]

Cory,

One way to lose the duplicate queries is to assign the count per pnr.id 
to a user var, and calculate via that value:


SELECT
 pnr.ID AS ID_pnr,
 pnr.reservationdatetime,
 pnr.conf_number,
 pnr.created_by,
 ( SELECT @pp_cnt := COUNT(1)
   FROM pnr_passengers pp
   WHERE pp.ID_pnr = pnr.ID
 ) AS pp_count,
 GROUP_CONCAT(pp.name_last,', ',pp.name_first
  ORDER BY name_last DESC SEPARATOR '') AS names,
 ( SELECT SUM( pf.base_fare * ( @pp_cnt )
   FROM pnr_fares pf
   WHERE pf.ID_pnr=pnr.ID
 ) AS base_fare,
 ( SELECT SUM( pf.tax * ( @pp_cnt )
   FROM pnr_fares pf
   WHERE pf.ID_pnr=pnr.ID
 ) AS tax ,
 ( SELECT SUM(psf.segfee_amount * ( @pp_cnt )
   FROM pnr_segments_fees psf
   INNER JOIN pnr_segments pss ON psf.ID_segments=pss.ID
   WHERE psf.segfee_code='XF' AND pss.ID_pnr=pnr.ID ) AS xf ,
 ( SELECT SUM(amount_value) service_fees
   FROM pnr_service_fees
   WHERE ID_pnr=pnr.ID
 ) AS services ,
 ( SELECT SUM(amount)
   FROM pnr_payments_credits
   WHERE ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='sale'
 ) AS payments,
 ( SELECT SUM(amount)
   FROM pnr_payments_credits
   WHERE ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='credit'
 ) AS credits
FROM pnr
INNER JOIN pnr_passengers pp ON pnr.ID=pp.ID_pnr
WHERE pnr.reservationdatetime>='2000-05-29 00:00:00'
 AND pnr.reservationdatetime<='2006-05-29 23:59:59'
GROUP BY pnr.ID
ORDER BY reservationdatetime;


PB


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.4/351 - Release Date: 5/29/2006


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

Re: MySql GUI

[Chris Sansom]

At 14:02 -0700 29/5/06, Dan Trainor wrote:

I highly suggest staying away from PHPMyAdmin.


Why in particular?

--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/

The world is proof that God is a committee.
   -- Bob Stokes

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

Re: MySql Limitations??

[Chris W]

Harish TM wrote:


hi...
  I need to store something like a couple of million rows is a MySql
table. Is that ok or do I have to split them up. I intend to index 
each of
the columns that I will need to access so as to speed up access. 
Insertion

will be done only when there is very little or no load on the server and
time for this is not really a factor. I also do not have any 
constraints on
disk space.  Please let me know if I can just use MySql as it is 
or if I

need to make some changes


I have a table with around 900,000 rows and I know others have tables 
with many millions of rows, I think I read some even have tables with 
billions or rows.  The limitation you hit is generally because of your 
OS.  Most OSs have an upper limit on file size.  Since a table is stored 
in a file, that file size limitation of the OS is what generally limits 
the table size.  In which case the number of rows depends on the size of 
the rows.



--
Chris W
KE5GIX

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

http://thewishzone.com


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

Re: MySql GUI

[Dan Trainor]

AndrewMcHorney wrote:

Hello

I understand that there is a free gui that will allow an administrator 
or user to create databases and maybe even add, update and modify rows 
in tables. It is something like MySQL Controller. Does this still 
exist and what is the link?


Andrew




Hi -

MySQL Query Browser and MySQL Administrator can both be found on MySQL's 
Download section.  I believe this is what you are looking for.


I highly suggest staying away from PHPMyAdmin.

Thanks
-dant

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

Re: MySql GUI

[Peter M. Groen]

On Monday 29 May 2006 21:31, AndrewMcHorney wrote:
> Hello
>
> I understand that there is a free gui that will allow an
> administrator or user to create databases and maybe even add, update
> and modify rows in tables. It is something like MySQL Controller.
> Does this still exist and what is the link?
>
> Andrew

Try searching for MySQL Control Centre. IIRC it's not maintained anymore but 
still useable. ( Qt-based so Cross-platform )

Kind Regards,

Peter
--
This message was scanned with clamAV version 0.88, clamav-milter version 0.87.
and is guaranteed free of viruses.

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

Re: MySql GUI

[Chris Sansom]

At 12:31 -0700 29/5/06, AndrewMcHorney wrote:
I understand that there is a free gui that will allow an 
administrator or user to create databases and maybe even add, update 
and modify rows in tables. It is something like MySQL Controller. 
Does this still exist and what is the link?


Maybe you're thinking of pypMyAdmin? 

--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/

I've  never had major knee surgery on any other part of my  body.
   -- Winston  Bennett, University of Kentucky basketball forward

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

Running Two Different Versions of MySQL

[Michael Monaghan]

Hi,

I have a machine [Solaris 9 x86] that has MySQL v3.22.xx installed.
I am not familiar with the applications [quite a few] that use this
instance.

I need to install an application that requires a newer version of MySQL.

I plan to install v5.x. separately - rather than upgrade the old version.

What sort of gotchas/possible conflicts should I be aware of? Port numbers,
config files etc.?

Thanks,
~mm

MySql Limitations??

[Harish TM]

hi...
  I need to store something like a couple of million rows is a MySql
table. Is that ok or do I have to split them up. I intend to index each of
the columns that I will need to access so as to speed up access. Insertion
will be done only when there is very little or no load on the server and
time for this is not really a factor. I also do not have any constraints on
disk space.  Please let me know if I can just use MySql as it is or if I
need to make some changes

harish

MySql GUI

[AndrewMcHorney]

Hello

I understand that there is a free gui that will allow an 
administrator or user to create databases and maybe even add, update 
and modify rows in tables. It is something like MySQL Controller. 
Does this still exist and what is the link?


Andrew



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

Re: text datatype

[Jo�o C�ndido de Souza Neto]

When you´ll catch data for a text field, you ought to use a 
.

When you´ll pull it from the database to the screen, you have to use 
nl2br($field) to conver \n to .


"ali asghar torabi parizy" <[EMAIL PROTECTED]> escreveu na mensagem 
news:[EMAIL PROTECTED]
> hi
> i am creating web newsprogram by php and mysql.
> how to i can use text data type for saving contents of articles in 
> database?
> when i use text format,contents save in the tables unless eny new line.
> howto i can insert \n element in text format?
>
>
>
> -
> How low will we go? Check out Yahoo! Messenger's low  PC-to-Phone call 
> rates. 



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

text datatype

[ali asghar torabi parizy]

hi
i am creating web newsprogram by php and mysql.
how to i can use text data type for saving contents of articles in database?
when i use text format,contents save in the tables unless eny new line.
howto i can insert \n element in text format?



-
How low will we go? Check out Yahoo! Messenger’s low  PC-to-Phone call rates.

RE: max size of TEXT columns

[Robert DiFalco]

FWIW, IMO LOB handling is really where MySQL lags behind all other
enterprise database solutions. Firebird, DB2, Oracle, et al all are able
to stream LOB data to and from disk so that it does not all need to be
loaded in memory (multiple times for a single LOB). I would call this a
bug but others would probably prefer to call it a limitation. :) 

-Original Message-
From: schlubediwup [mailto:[EMAIL PROTECTED] 
Sent: Monday, May 29, 2006 8:07 AM
To: mysql@lists.mysql.com
Subject: max size of TEXT columns

hi listers,

1. environment:

[EMAIL PROTECTED] ~> uname -a
Linux myhost.mydomain.com 2.6.16-1.2080_2.rhfc5.cubbi_suspend2 #1 Wed
Mar 29 12:54:32 CEST 2006 i686 i686 i386 GNU/Linux [EMAIL PROTECTED] ~>



localhost.(none)> show variables like "version%";
+-+--+
| Variable_name | Value |
+-+--+
| version | 5.0.18-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
+-+--+
4 rows in set (0.00 sec)

localhost.(none)>



localhost.mydb> show variables like "max%";
++--+
| Variable_name | Value |
++--+
| max_allowed_packet | 1048576 |





localhost.mydb> select max(char_length(history)) from my_contacts2;
+---+
| max(char_length(history)) |
+---+
| 65535 |
+---+
1 row in set (0.01 sec)

localhost.mydb>


2. problem
according do the mysql docu TEXT/BLOB fields depend solely on the db 
environment and can grow to any length:


  11.4.3. The |BLOB| and |TEXT| Types



The maximum size of a |BLOB| or |TEXT| object is determined by its type,

but the largest value you actually can transmit between the client and 
server is determined by the amount of available memory and the size of 
the communications buffers. You can change the message buffer size by 
changing the value of the |max_allowed_packet| variable, but you must do

so for both the server and your client program. For example, both 
*mysql* and *mysqldump* allow you to change the client-side 
|max_allowed_packet| value. See Section 7.5.2, "Tuning Server 
Parameters" 
, Section

8.3, "mysql - The MySQL Command-Line Tool" 
, and Section 
8.8, "mysqldump - A Database Backup Program" 
.



BUT:

The table in consideration contains a column named history containing 
all message text which has been sent to the mail-address in the row in 
question. i ment to have observed that this column in reality does not 
contain all message text from the very beginning. today, i found out 
that the maximum lenght of the history fields in all tables of this kind

is 65535.

when an update is ocurring, the column history is recreated using 
concat(new_text, history) so that the newest text is always at the 
beginning of the column. but the oldest text at the end of the column 
apparently is lost.

3. question

which parameter do i have to change in order to get this TEXT column 
really to any length?

any hint is very much appreciated, thanks in advance.

suomi


-- 
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: max size of TEXT columns

[Paul DuBois]

At 17:06 +0200 5/29/06, schlubediwup wrote:

2. problem
according do the mysql docu TEXT/BLOB fields depend solely on the db 
environment and can grow to any length:



 11.4.3. The |BLOB| and |TEXT| Types



The maximum size of a |BLOB| or |TEXT| object is determined by its type, but


"determined by its type" is correct.
"to any length" is incorrect.

The maximum lengths are described here:

http://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html



the largest value you actually can transmit between the client and 
server is determined by the amount of available memory and the size 
of the communications buffers. You can change the message buffer 
size by changing the value of the |max_allowed_packet| variable, but 
you must do so for both the server and your client program. For 
example, both *mysql* and *mysqldump* allow you to change the 
client-side |max_allowed_packet| value. See Section 7.5.2, "Tuning 
Server Parameters" 
, 
Section 8.3, "mysql - The MySQL Command-Line Tool" 
, and 
Section 8.8, "mysqldump - A Database Backup Program" 
.




BUT:

The table in consideration contains a column named history 
containing all message text which has been sent to the mail-address 
in the row in question. i ment to have observed that this column in 
reality does not contain all message text from the very beginning. 
today, i found out that the maximum lenght of the history fields in 
all tables of this kind is 65535.


when an update is ocurring, the column history is recreated using 
concat(new_text, history) so that the newest text is always at the 
beginning of the column. but the oldest text at the end of the 
column apparently is lost.


3. question

which parameter do i have to change in order to get this TEXT column 
really to any length?


any hint is very much appreciated, thanks in advance.

suomi


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



--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

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

Re: ugly SQL for a report...

[Jochem van Dieten]

On 5/29/06, Cory Robin wrote:


SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number,
pnr.created_by,
GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last
DESC SEPARATOR '') names,
(SELECT SUM(pf.base_fare*(SELECT COUNT(1) FROM pnr_passengers pp
WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares pf WHERE pf.ID_pnr=pnr.ID )
base_fare,
(SELECT SUM(pf.tax*(SELECT COUNT(1) FROM pnr_passengers pp WHERE
pp.ID_pnr=pnr.ID )) FROM pnr_fares pf WHERE pf.ID_pnr=pnr.ID ) tax ,
(SELECT SUM(psf.segfee_amount*(SELECT COUNT(1) FROM
pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_segments_fees psf
INNER JOIN pnr_segments pss ON psf.ID_segments=pss.ID WHERE
psf.segfee_code='XF' AND pss.ID_pnr=pnr.ID ) XF ,
(SELECT SUM(amount_value) service_fees FROM pnr_service_fees
WHERE ID_pnr=pnr.ID) services ,
(SELECT SUM(amount) FROM pnr_payments_credits WHERE
ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='sale') payments,
(SELECT SUM(amount) FROM pnr_payments_credits WHERE
ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='credit') credits
FROM pnr INNER JOIN pnr_passengers pp
ON pnr.ID=pp.ID_pnr
WHERE pnr.reservationdatetime>='2000-05-29 00:00:00'
AND pnr.reservationdatetime<='2006-05-29 23:59:59'
GROUP BY pnr.ID
ORDER BY reservationdatetime;


One way to avoid the repeated COUNT might be to use a nested FROM that
pre-calculates the count as a pseudo-column:
SELECT
pnr_precalc.ID ID_pnr,
pnr_precalc.reservationdatetime,
pnr_precalc.conf_number,
pnr_precalc.created_by,
GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last DESC
SEPARATOR '') names,
(SELECT SUM(pf.base_fare * pnr_precalc.passengercount) FROM pnr_fares
pf WHERE pf.ID_pnr=pnr_precalc.ID ) base_fare,
(SELECT SUM(pf.tax*pnr_precalc.passengercount) FROM pnr_fares pf
WHERE pf.ID_pnr=pnr_precalc.ID ) tax ,
(
SELECT SUM(psf.segfee_amount*pnr_precalc.passengercount)
FROM pnr_segments_fees psf INNER JOIN pnr_segments pss ON
psf.ID_segments=pss.ID
WHERE psf.segfee_code='XF' AND pss.ID_pnr=pnr_precalc.ID
) XF,
(SELECT SUM(amount_value) service_fees FROM pnr_service_fees WHERE
ID_pnr=pnr_precalc.ID) services ,
(SELECT SUM(amount) FROM pnr_payments_credits WHERE
ID_pnr=pnr_precalc.ID AND bln_payment='1' AND transaction_type='sale')
payments,
(SELECT SUM(amount) FROM pnr_payments_credits WHERE
ID_pnr=pnr_precalc.ID AND bln_payment='1' AND
transaction_type='credit') credits

FROM
(
SELECT
pnr.ID ID_pnr,
pnr.reservationdatetime,
pnr.conf_number,
pnr.created_by,
(SELECT COUNT(1) FROM pnr_passengers pp WHERE 
pp.ID_pnr=pnr.ID )
passengercount
FROM
pnr
) pnr_precalc
INNER JOIN pnr_passengers
pp
ON pnr.ID=pp.ID_pnr
WHERE
pnr_precalc.reservationdatetime >= '2000-05-29 00:00:00'
AND
pnr_precalc.reservationdatetime <= '2006-05-29 23:59:59'
GROUP BY
pnr_precalc.ID
ORDER BY
reservationdatetime;
(You probably have to fix this since I can not test it.)


If you see too many loops over pnr_service_fees and
pnr_payments_credits in your explain output you can rework them in the
same way. If this doesn't help, we are going to need a lot more from
the schema and the explain output.

Jochem

Re: i'm at a complete loss?

[Greg Maruszeczka]

On Sun, 28 May 2006 20:17:53 -0400
"Daniel McQuay" <[EMAIL PROTECTED]> wrote:

> Hello list,
> 
> I just installed MySQL on my FreeBSD box here at home and am having a
> few problems with privileges. I can connect to mysql using; mysql -u
> mysql and there is no password needed. However, when I try to connect
> to the server using root; mysql -u root I get an error;
> 
> ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
> password: NO)
> 
> so reading that it appears that I need a password so i try; mysql -u
> root -p it prompts me for my root pass and when I put it in it does
> that same thing above but with (using password: YES).
> 
> I went to the the MySQL web site and read 2.10.3 Securing the Initial
> MySQL Accounts and tried following along with that but with no luck.
> When checking google for help I read a lot about the initial
> installation. Something about /usr/local/bin/mysql_install_db will
> install a privilege table.
> 
> I installed mysql using this guide here
> http://raybdbomb.com/p/mysql-install-on-freebsd.html and every thing
> seemed to go well but like I said I keep getting this error. Is there
> something else I should do? Any help on this would be MUCH
> appreciated.
> 


Hi,

Wasn't clear to me in reading your post that you did this so here
goes:

Did you actually set a root password for mysql using a GRANT
statement after logging in with the default BLANK password?

mysql -u root -p [then just hit enter]

HTH,
G

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

max size of TEXT columns

[schlubediwup]

hi listers,

1. environment:

[EMAIL PROTECTED] ~> uname -a
Linux myhost.mydomain.com 2.6.16-1.2080_2.rhfc5.cubbi_suspend2 #1 Wed 
Mar 29 12:54:32 CEST 2006 i686 i686 i386 GNU/Linux

[EMAIL PROTECTED] ~>



localhost.(none)> show variables like "version%";
+-+--+
| Variable_name | Value |
+-+--+
| version | 5.0.18-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
+-+--+
4 rows in set (0.00 sec)

localhost.(none)>



localhost.mydb> show variables like "max%";
++--+
| Variable_name | Value |
++--+
| max_allowed_packet | 1048576 |





localhost.mydb> select max(char_length(history)) from my_contacts2;
+---+
| max(char_length(history)) |
+---+
| 65535 |
+---+
1 row in set (0.01 sec)

localhost.mydb>


2. problem
according do the mysql docu TEXT/BLOB fields depend solely on the db 
environment and can grow to any length:



 11.4.3. The |BLOB| and |TEXT| Types



The maximum size of a |BLOB| or |TEXT| object is determined by its type, 
but the largest value you actually can transmit between the client and 
server is determined by the amount of available memory and the size of 
the communications buffers. You can change the message buffer size by 
changing the value of the |max_allowed_packet| variable, but you must do 
so for both the server and your client program. For example, both 
*mysql* and *mysqldump* allow you to change the client-side 
|max_allowed_packet| value. See Section 7.5.2, “Tuning Server 
Parameters” 
, Section 
8.3, “mysql — The MySQL Command-Line Tool” 
, and Section 
8.8, “mysqldump — A Database Backup Program” 
.




BUT:

The table in consideration contains a column named history containing 
all message text which has been sent to the mail-address in the row in 
question. i ment to have observed that this column in reality does not 
contain all message text from the very beginning. today, i found out 
that the maximum lenght of the history fields in all tables of this kind 
is 65535.


when an update is ocurring, the column history is recreated using 
concat(new_text, history) so that the newest text is always at the 
beginning of the column. but the oldest text at the end of the column 
apparently is lost.


3. question

which parameter do i have to change in order to get this TEXT column 
really to any length?


any hint is very much appreciated, thanks in advance.

suomi


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

What's wrong in this Innodb status log?

[Nico Sabbi]

Hi,
after having noticed occasional load spikes I created an Innodb monitor;
follows an excerpt of the output where I can read that certain 
transactions don't start
and that accessing a table (H.albero) with a very low amount of records 
(185)

seems to take a lot of time.

What's wrong with these logs? why those transactions don't start?

Thanks,

   Nico

=
060529 16:27:40 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 16 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 44025032, signal count 43473647
Mutex spin waits 794955636, rounds 1522695948, OS waits 9162350
RW-shared spins 66265323, OS waits 32297148; RW-excl spins 1644124, OS 
waits 379573


TRANSACTIONS

Trx id counter 0 678088914
Purge done for trx's n:o < 0 678083128 undo n:o < 0 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 678088912, not started, process no 30735, OS thread id 
1210591600

MySQL thread id 6460882, query id 173503655 192.168.65.105 pat_trento0844
---TRANSACTION 0 678088911, not started, process no 30735, OS thread id 
1207380336

MySQL thread id 6460881, query id 173503653 192.168.65.105 pat_trento0844
---TRANSACTION 0 678088913, not started, process no 30735, OS thread id 
1193732464 starting index read, thread declared inside InnoDB 500

mysql tables in use 1, locked 0
MySQL thread id 6460880, query id 173503656 192.168.65.107 h3r4user 
Sorting result

SELECT
   H.albero.ordinamento,
   H.albero.REF,
   H.albero.titolo,
   H.albero.categoria,
   H.albero.tabella,
   H.albero.url,

---TRANSACTION 0 678087842, not started, process no 30735, OS thread id 
1175267696

MySQL thread id 6460844, query id 173502395 192.168.65.107 r1minse4
---TRANSACTION 0 678087713, not started, process no 30735, OS thread id 
1164028272

MySQL thread id 6460837, query id 173502245 192.168.65.105 pat_trento0844
---TRANSACTION 0 678082967, not started, process no 30735, OS thread id 
1204169072

MySQL thread id 6460594, query id 173496341 192.168.65.107 h3r4user
---TRANSACTION 0 678081965, not started, process no 30735, OS thread id 
1179281776

MySQL thread id 6460592, query id 173495169 192.168.65.105 pat_trento0844
---TRANSACTION 0 678081879, not started, process no 30735, OS thread id 
1150380400

MySQL thread id 6460570, query id 173495043 192.168.65.108 cntdb3
---TRANSACTION 0 678057066, not started, process no 30735, OS thread id 
1192126832

MySQL thread id 6459537, query id 173464972 192.168.65.105 pat_trento0844
---TRANSACTION 0 678051381, not started, process no 30735, OS thread id 
1195137392

MySQL thread id 6459290, query id 173458071 192.168.65.108 cntdb3
---TRANSACTION 0 678045374, not started, process no 30735, OS thread id 
1182894448

MySQL thread id 6459003, query id 173450765 192.168.65.105 prgdb_1

FILE I/O

I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
99506233 OS file reads, 1810896 OS file writes, 808231 OS fsyncs
266.30 reads/s, 33214 avg bytes/read, 1.75 writes/s, 1.12 fsyncs/s
-
INSERT BUFFER AND ADAPTIVE HASH INDEX
-
Ibuf for space 0: size 1, free list len 5, seg size 7,
195942 inserts, 195942 merged recs, 7446 merges
Hash table size 34679, used cells 6972, node heap has 13 buffer(s)
4850.07 hash searches/s, 1614.59 non-hash searches/s
---
LOG
---
Log sequence number 4 1251143669
Log flushed up to   4 1251143659
Last checkpoint at  4 1251140386
0 pending log writes, 0 pending chkp writes
1203908 log i/o's done, 1.12 log i/o's/second
--
BUFFER POOL AND MEMORY
--
Total memory allocated 364710794; in additional pool allocated 25165824
Buffer pool size   1024
Free buffers   0
Database pages 1011
Modified db pages  11
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 234480945, created 286926, written 1159843
539.84 reads/s, 0.00 creates/s, 0.62 writes/s
Buffer pool hit rate 995 / 1000
--
ROW OPERATIONS
--
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 30735, id 1147169136, state: sleeping
Number of rows inserted 16849245, updated 39287, deleted 16105063, read 
32922121960

0.06 inserts/s, 0.06 updates/s, 0.00 deletes/s, 135719.89 reads/s

END

Re: LOAD DATA FROM MASTER stops unfinished with Query OK

[Bgs]

It seems nobody has a clue here :(

I've given up with MySQL replication...

Hope it will work in 5.1 ...

BTW: Any official info or estimate about the production release?

Bgs wrote:

Nope... pure myisam...

sheeri kritzer wrote:


yeah, I'd be willing to guess that you're mostly innodb.  LOAD DATA
FROM MASTER only works for MYISAM.

http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html

-Sheeri

On 5/24/06, Dan Trainor <[EMAIL PROTECTED]> wrote:


Bgs wrote:
>
> No ideas?
>
> I tried playing around with read/write timeouts (even thought the
> replication is fast), all size limits are greater than the whole
> replicated db. The last table with accesses MYD and zero size is a 
small

> one (a couple of dozens kBs).
>
> Bgs wrote:
>
>>
>>  Greetings,
>>
>> I played around with load data from master (ldfm) and it worked fine
>> in test environment. Now I want to replicate our actual db to a 
slave.

>> When I issue the ldfm command, it starts the replication. I get Query
>> OK, but only about 5% of the db is replicated. Apparently all tables
>> that are on the slave in the end are exact copies of the master
>> tables, but most MYD files are zero sized
>>
>>
>> Any ideas?
>>
>> Thanks in advance
>> Bgs
>>
>>
>

Hi -

Which storage engine are you using for the tables or database which
you're trying to replicate?

Thanks
-dant

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

Binding mysql to more than 1 ip address but not to all

[Ghaffar]

Hello all,

I have seen that there are some people asking the same question.
How to bind to multiple ip addresses but not to all?

I have the need to do the same.

I am running multiple Mysql servers on a cluster (Not mysql cluster).
The cluster has 2 nodes and they are connected to a SAN to get disks for 
each database instance.


There are around 4-5 instances running on the cluster binding to a 
specific address.

For example
192.168.100.n1
192.168.100.n2
192.168.100.n3  etc

The 192.168.100 is the network which connects to all production 
hosts/switches and I dont want to saturate the traffic on this network.


I have another 192.168.50 network which connects to backup switches. 
Here is all the traffice related to backup aggregation.


I would like to back the mysql servers over the backup network.
For example the server that listens to 192.168.100.110, I would like to 
connect to 192.168.50.110 etc.


Okay, so from the source it seems that this is not possible.

Would anyone advice using tricks with netfilter to be able to do this?

Or. Would it be very difficult to patch mysql to listen to a list of ip 
addresses?


Thanks and best regards.

Atif



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

Re: ugly SQL for a report...

[Cory Robin]

SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number, 
pnr.created_by,
   GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last 
DESC SEPARATOR '') names,


   (SELECT SUM(pf.base_fare*(SELECT COUNT(1) FROM pnr_passengers pp 
WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares pf WHERE pf.ID_pnr=pnr.ID ) 
base_fare,


   (SELECT SUM(pf.tax*(SELECT COUNT(1) FROM pnr_passengers pp WHERE 
pp.ID_pnr=pnr.ID )) FROM pnr_fares pf WHERE pf.ID_pnr=pnr.ID ) tax ,


   (SELECT SUM(psf.segfee_amount*(SELECT COUNT(1) FROM 
pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_segments_fees psf 
INNER JOIN pnr_segments pss ON psf.ID_segments=pss.ID WHERE 
psf.segfee_code='XF' AND pss.ID_pnr=pnr.ID ) XF ,


   (SELECT SUM(amount_value) service_fees FROM pnr_service_fees 
WHERE ID_pnr=pnr.ID) services ,


   (SELECT SUM(amount) FROM pnr_payments_credits WHERE 
ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='sale') payments,


   (SELECT SUM(amount) FROM pnr_payments_credits WHERE 
ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='credit') credits


   FROM pnr INNER JOIN pnr_passengers pp
   ON pnr.ID=pp.ID_pnr

   WHERE pnr.reservationdatetime>='2000-05-29 00:00:00'
   AND pnr.reservationdatetime<='2006-05-29 23:59:59'

GROUP BY pnr.ID

ORDER BY reservationdatetime;


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

Re: where more than 2 column not in a table

[Peter Brawley]

[EMAIL PROTECTED] wrote:

Sory if my question makes you confused
I've a problem with my sintax,
I don't know how to select a list, whether it's not registered in another 
table, but it have 2 IDs.
  
Not enough info. Show the Create Table statements, a bit of data, and 
the desired result.


PB

I hope you will help me because I really need it for my study.

Thank you in advance,

regards,
Eva Panjaitan
11104029 
Student of Del Polytechnic of Informatics 
Sitoluama - Laguboti 
Toba Samosir 
Sumatera Utara 

  



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.3/350 - Release Date: 5/28/2006


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

Re: Keyword - MySQL 4.1

[Keith Roberts]

>From the manual:

http://dev.mysql.com/doc/refman/4.1/en/legal-names.html

An identifier may be quoted or unquoted. If an identifier 
is a reserved word or contains special characters, you must 
quote it whenever you refer to it.

(Exception: A word that follows a period in a qualified name 
must be an identifier, so it is not necessary to quote it, 
even if it is a reserved word.) For a list of reserved 
words, see Section 9.5, Treatment of Reserved Words in 
MySQL.

Special characters are those outside the set of alphanumeric 
characters from the current character set, _, and $.

The identifier quote character is the backtick (`):

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

Or could you just use an ALTER TABLE statement to change the 
name in your database, and then change the div name in your 
application code?

HTH

Keith Roberts

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

On Mon, 29 May 2006, ManojW wrote:

> To: mysql@lists.mysql.com
> From: ManojW <[EMAIL PROTECTED]>
> Subject: Keyword - MySQL 4.1
> 
> Dear All,
> While migrating from MySQL 4.0 to MySQL 4.1, I found 
> that the database could not be loaded in 4.1 because MySQL 
> 4.1 uses a reserved keyword "div" that was not "reserved" 
> in 4.0.
> 
> Is there anyway to get around the issue?
> 
> Thanks in advance.
> 
> Manoj

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

Keyword - MySQL 4.1

[ManojW]

Dear All,
While migrating from MySQL 4.0 to MySQL 4.1, I found that the database
could not be loaded in 4.1 because MySQL 4.1 uses a reserved keyword "div"
that was not "reserved" in 4.0.

Is there anyway to get around the issue?

Thanks in advance.

Manoj


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

Re: ugly SQL for a report...

[Jochem van Dieten]

On 5/29/06, Cory wrote:

I have the following query that is running VERY slowly.   Anyone have
any suggestions?

---
SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number,
pnr.created_by, GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY
name_last DESC SEPARATOR '') names, (SELECT SUM(pf.base_fare*(SELECT
COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares
pf WHERE pf.ID_pnr=pnr.ID ) base_fare, (SELECT SUM(pf.tax*(SELECT
COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares
pf WHERE pf.ID_pnr=pnr.ID ) tax , (SELECT SUM(psf.segfee_amount*(SELECT
COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM
pnr_segments_fees psf INNER JOIN pnr_segments pss ON
psf.ID_segments=pss.ID WHERE psf.segfee_code='XF' AND pss.ID_pnr=pnr.ID
) XF ,(SELECT SUM(amount_value) service_fees FROM pnr_service_fees WHERE
ID_pnr=pnr.ID) services ,(SELECT SUM(amount) FROM pnr_payments_credits
WHERE ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='sale')
payments, (SELECT SUM(amount) FROM pnr_payments_credits WHERE
ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='credit') credits
FROM pnr INNER JOIN pnr_passengers pp ON pnr.ID=pp.ID_pnr WHERE
pnr.reservationdatetime>='2000-05-29 00:00:00' AND
pnr.reservationdatetime<='2006-05-29 23:59:59' GROUP BY pnr.ID ORDER BY
reservationdatetime;


That is indeed very ugly. If you prettify it by adding proper
indenting etc. I might be inclined to read it and think about an
answer.

Jochem