Question on threads displayed in linux

[Thomas]

Recently I noticed a changed behavior in how linux utilities (ps ans  
pstree) report mysql usage.


Previously a ps output would list all active mysql threads, and a  
pstree would list the number of mysql threads currently running.


Now I merely see a pstree output such as:

├─mysqld_safe───mysqld

With no number of threads.

Was there a change in mysql reporting behavior?  This is for MySQL  
4.1.19 on CentOS 4.3 and Red Hat ES 3 systems.


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

OR isn't optimised

[Taras D]

Hi everyone,

I have the following schema:

create table l
(
 aId int unsigned not null auto_increment primary key,
 sId smallint unsigned not null,
 dId smallint unsigned,
 index(sId,dId)
);

create table d
(
 sId smallint unsigned not null,
 dId smallint unsigned not null,
 primary key(sId, dId)
);

The d table has 10 000 entries, the l table has 100 entries.

The following query, which consist of an OR. Each part of the OR
specifies exactly one row in the d table.

explain select straight_join * from l,d where (l.sId = d.dId and d.sId
= 1) or (l.sId = d.dId and d.sId = 2);

++-+---+---+---+-+-+--+--+--+
| id | select_type | table | type  | possible_keys | key | key_len
| ref  | rows | Extra|
++-+---+---+---+-+-+--+--+--+
|  1 | SIMPLE  | l | index | sId   | sId | 5
| NULL |  100 | Using index  |
|  1 | SIMPLE  | d | range | PRIMARY   | PRIMARY | 2
| NULL |  200 | Using where; Using index |
++-+---+---+---+-+-+--+--+--+

So why is only half of the index being used to reduce the d table? I
would think if it used both indices, at most two matching rows would
be found, reducing the  number of rows that have to be inspected from
20 000 to 100. In fact, UNIONing the two queries shows just this:

select straight_join * from l,d where (l.sId = d.dId and d.sId = 1)
union select straight_join * from l,d where (l.sId = d.dId and d.sId =
2);
++--+++---+-+-++--+-+
| id | select_type  | table  | type   | possible_keys | key |
key_len | ref| rows | Extra
|
++--+++---+-+-++--+-+
|  1 | PRIMARY  | l  | index  | sId   | sId |
5   | NULL   |  100 | Using index
|
|  1 | PRIMARY  | d  | eq_ref | PRIMARY   | PRIMARY |
4   | const,radius_searching_test_case.l.sId |1 | Using index
|
|  2 | UNION| l  | index  | sId   | sId |
5   | NULL   |  100 | Using index
|
|  2 | UNION| d  | eq_ref | PRIMARY   | PRIMARY |
4   | const,radius_searching_test_case.l.sId |1 | Using index
|
|| UNION RESULT |  | ALL| NULL  | NULL|
NULL| NULL   | NULL |
|
++--+++---+-+-++--+-+

So why isn't the optimiser picking up that it can use both parts of
the keys and significantly reduce the number of rows it must inspect?
I have tried inserting 'force index(primary) after 'from l, d', but
that doesn't help. A work around *could* be to just union the results,
but in the real case the 'l' table can have upto 100 000 entries, and
there can be upto 16 independant ORs, which means that I would be
looking at 100 000 * 16 = 1.6 million rows (using UNIONs), instead of
100 000 rows (if I could get this OR stuff to work). (PHP code to
insert data into the tables is appended)

Does anyone know what's happening?

Thanks,
Taras

php code
-
= $INSERT_AT_A_TIME)
   {
 mysql_query($query) or die(mysql_error().''.$query);
 echo "Done another 1000\n\r";
 $query = 'INSERT INTO d VALUES';
 $done = 0;
   }
   else
   {
 $query .= ', ';
   }
 }
}

// now do the l table
for($j = 1; $j <= 100; $j++)
{
 $s = mt_rand(1,100);
 $d = mt_rand(1,100);
 $query = "INSERT INTO l VALUES(NULL,$s,$d)";
 mysql_query($query);
}

echo 'FINISHED';

?>

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

Re: MySQL (GPL License)

[Douglas Sims]

Oh, one other thing.

The group that I work with at the one large company (call it company  
"X") which Randy the MS rep was taking to lunch... is about to  
upgrade some servers and spend somewhere around $250k on new MS SQL  
server licenses.  I'm not really sure why.  (Oxygen deprivation could  
explain it, except we seem to have plenty of oxgyen.)  How can I  
possibly explain to them that we can port all of these web apps to  
MySQL or some other database (I don't care... MySQL is my first  
choice but Postgres is very nice but we could use database ABC or  
Wally's DB or use flat files written in Mandarin Chinese for all I  
care) for less money than we will spend on software on one upgrade  
cycle?


Does anyone else have similar experiences?

Douglas Sims
[EMAIL PROTECTED]



On Jun 7, 2006, at 11:15 PM, Douglas Sims wrote:


Ouch.

Thanks for the clarification.  Two additional thoughts:

1) Does this apply (I think not) even if you don't compile with or  
link with the MySQL database?  If you just connect to it with ports  
or sockets, as we usually do with web applications, you still don't  
need a commercial license?


2) $595/year is still a lot cheaper than most of the alternatives  
such as MS-SQL or Oracle.


I got into this debate with our Microsoft rep over lunch about a  
year ago.  My company does consulting with a couple of large (US  
Fortune-500 companies) and unfortunately one of these is wedded to  
MS.  I work with MS-SQL quite a lot and generally find it inferior  
to MySQL and as Randy (the MS rep) was talking how you really did  
need to buy commercial licenses for MySQL, I pointed out that no  
you really didn't for web applications.  (He went on to point out  
what great support MS SQL had - I quickly agreed and said that MS  
SQL had the best support we could ever ask for... it's called  
"Google."  Randy still paid for the lunch :-)



Douglas Sims
[EMAIL PROTECTED]




On Jun 7, 2006, at 10:20 PM, mos wrote:


At 08:15 PM 6/7/2006, you wrote:

I believe that if you are only using MySQL for your company's
internal needs, whether from a web server or for deployment to other
company-owned locations, you don't need a commercial license.  For
example, if your company owns fifiteen stores, you could set up a
MySQL-based point-of-sale system at each one without needing a
commercial license.  You only need to release your source code if  
you

release your compiled code.

Also, I believe the GPL requirement for sharing only applies if you
have modified MySQL's object code, i.e. compiled your code into  
it or

it into your code or linked object code to it.  If you are simply
installing it as a database and communicating to it through DBI or
ODBC or some other means which uses sockets or ports, you don't need
to release your code under the GPL.

Thus, you hardly ever need to purchase a commercial license.

Please note that this is just my understanding.  I hope someone will
correct if I have misstated anything here.


Unfortunately that's not what MySQL AB licensing person told me.  
The license is more strict than that. If your company distributes  
an application that uses MySQL database inside the company (even  
inside the same building), and you don't give the other dept the  
source code (so it falls outside the gpl license) then the dept  
receiving the application needs to have a MySQL license. In other  
words, the complete application source code must follow the  
application.


If you have a commercial application running in Windows, and  
expect to sell a lot of applications, it will cost you $595 per  
database server *per year*.
See https://shop.mysql.com/network.html?rz=s2. I didn't realize  
myself it is now a per server/per year pricing either and it came  
as quite a shock to me system. This can add up if you have a  
thousand applications in circulation because each customer needs  
to pay $595/year. If this is too pricey for you, there are open  
source databases out there that are free to use and free to  
distribute. FireBird and ProgreSQL come to mind. And there are  
other commercial databases where you pay up front and have no  
distribution fees whatsoever.


Mike


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





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

Re: MySQL (GPL License)

[Douglas Sims]

Ouch.

Thanks for the clarification.  Two additional thoughts:

1) Does this apply (I think not) even if you don't compile with or  
link with the MySQL database?  If you just connect to it with ports  
or sockets, as we usually do with web applications, you still don't  
need a commercial license?


2) $595/year is still a lot cheaper than most of the alternatives  
such as MS-SQL or Oracle.


I got into this debate with our Microsoft rep over lunch about a year  
ago.  My company does consulting with a couple of large (US  
Fortune-500 companies) and unfortunately one of these is wedded to  
MS.  I work with MS-SQL quite a lot and generally find it inferior to  
MySQL and as Randy (the MS rep) was talking how you really did need  
to buy commercial licenses for MySQL, I pointed out that no you  
really didn't for web applications.  (He went on to point out what  
great support MS SQL had - I quickly agreed and said that MS SQL had  
the best support we could ever ask for... it's called "Google."   
Randy still paid for the lunch :-)



Douglas Sims
[EMAIL PROTECTED]




On Jun 7, 2006, at 10:20 PM, mos wrote:


At 08:15 PM 6/7/2006, you wrote:

I believe that if you are only using MySQL for your company's
internal needs, whether from a web server or for deployment to other
company-owned locations, you don't need a commercial license.  For
example, if your company owns fifiteen stores, you could set up a
MySQL-based point-of-sale system at each one without needing a
commercial license.  You only need to release your source code if you
release your compiled code.

Also, I believe the GPL requirement for sharing only applies if you
have modified MySQL's object code, i.e. compiled your code into it or
it into your code or linked object code to it.  If you are simply
installing it as a database and communicating to it through DBI or
ODBC or some other means which uses sockets or ports, you don't need
to release your code under the GPL.

Thus, you hardly ever need to purchase a commercial license.

Please note that this is just my understanding.  I hope someone will
correct if I have misstated anything here.


Unfortunately that's not what MySQL AB licensing person told me.  
The license is more strict than that. If your company distributes  
an application that uses MySQL database inside the company (even  
inside the same building), and you don't give the other dept the  
source code (so it falls outside the gpl license) then the dept  
receiving the application needs to have a MySQL license. In other  
words, the complete application source code must follow the  
application.


If you have a commercial application running in Windows, and expect  
to sell a lot of applications, it will cost you $595 per database  
server *per year*.
See https://shop.mysql.com/network.html?rz=s2. I didn't realize  
myself it is now a per server/per year pricing either and it came  
as quite a shock to me system. This can add up if you have a  
thousand applications in circulation because each customer needs to  
pay $595/year. If this is too pricey for you, there are open source  
databases out there that are free to use and free to distribute.  
FireBird and ProgreSQL come to mind. And there are other commercial  
databases where you pay up front and have no distribution fees  
whatsoever.


Mike


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

Re: Windows Compiled Help MySQL Reference Manual -- Error

[Jake Peavy]

On 6/7/06, Paul DuBois <[EMAIL PROTECTED]> wrote:


At 17:30 -0600 6/7/06, Jake Peavy wrote:
>Hey yall,
>
>I'm unable to open the .chm I just downloaded for 5.1.  I get the
following
>error:
>
>Cannot open the file: mk:@MSITStore:C:\Documents and Settings\jpeavy1\My
>>Documents\refman-5.1-en.chm.
>>
>
>Any ideas?

It does seem to be corrupt.  We'll take a look into it.  Thanks.



Great.  Can you respond to this when you have an updated file up?

--
-jp


Chuck Norris sold his soul to the devil for his rugged good looks and
unparalleled martial arts ability. Shortly after the transaction was
finalized, Chuck roundhouse kicked the devil in the face and took his soul
back. The devil, who appreciates irony, couldn't stay mad and admitted he
should have seen it coming. They now play poker every second Wednesday of
the month.

Re: Copying tables sans data from one database to another

[Dilipkumar]

Hi,

It is

Rename table name to database.tablename.

db-1
db-2
rename db1.tablename to db2.tablename.

This might help you out.

murthy gandikota wrote:


How can I copy tables from one database to another on the same host?
  
 Thanks for your help

 Murthy

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
 




--
Thanks & Regards,
Dilipkumar
DBA Support

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


Watch India vs. England LIVE, Hot videos and more only on Sify Max! Click Here. 
www.sifymax.com

Get to see what's happening in your favourite City on Bangalore Live! 
www.bangalorelive.in


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

Re: MySQL (GPL License)

[mos]

At 08:15 PM 6/7/2006, you wrote:

I believe that if you are only using MySQL for your company's
internal needs, whether from a web server or for deployment to other
company-owned locations, you don't need a commercial license.  For
example, if your company owns fifiteen stores, you could set up a
MySQL-based point-of-sale system at each one without needing a
commercial license.  You only need to release your source code if you
release your compiled code.

Also, I believe the GPL requirement for sharing only applies if you
have modified MySQL's object code, i.e. compiled your code into it or
it into your code or linked object code to it.  If you are simply
installing it as a database and communicating to it through DBI or
ODBC or some other means which uses sockets or ports, you don't need
to release your code under the GPL.

Thus, you hardly ever need to purchase a commercial license.

Please note that this is just my understanding.  I hope someone will
correct if I have misstated anything here.


Unfortunately that's not what MySQL AB licensing person told me. The 
license is more strict than that. If your company distributes an 
application that uses MySQL database inside the company (even inside the 
same building), and you don't give the other dept the source code (so it 
falls outside the gpl license) then the dept receiving the application 
needs to have a MySQL license. In other words, the complete application 
source code must follow the application.


If you have a commercial application running in Windows, and expect to sell 
a lot of applications, it will cost you $595 per database server *per year*.
See https://shop.mysql.com/network.html?rz=s2. I didn't realize myself it 
is now a per server/per year pricing either and it came as quite a shock to 
me system. This can add up if you have a thousand applications in 
circulation because each customer needs to pay $595/year. If this is too 
pricey for you, there are open source databases out there that are free to 
use and free to distribute. FireBird and ProgreSQL come to mind. And there 
are other commercial databases where you pay up front and have no 
distribution fees whatsoever.


Mike


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

Re: MySQL crashes

[Dilipkumar]

Hi,

This might be a bug try to create mysql stack trace file and find out 
what is causing for mysql
crash, this might be a query hitting mysql and also memory related 
issues.Try to upgrade your

mysql and report back.


Daniel da Veiga wrote:


On 6/7/06, Ian Collins <[EMAIL PROTECTED]> wrote:


I have a site running MySQL 4.1.19.
When I was first given the problem, the site only stayed up for a couple
of days - they then found they had to reboot (as opposed to just
restarting MySQL).
The error message they were getting is below (between the snip's).

After looking at the site, I altered some my.cnf parameters, and then
the site stayed up for 14 days before doing the same.

This site is identical to others I have seen - that are running with no
issues.
Could this be hardware (memory related)?

Regards,
Ian Collins.


 snip
===
Version: '4.1.9-standard'  socket: '/tmp/mysql.sock'  port: 3306
Official MySQL RPM
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this 
binary
or one of the libraries it was linked against is corrupt, improperly 
built,
or misconfigured. This error can also be caused by malfunctioning 
hardware.

We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail.

key_buffer_size=134217728
read_buffer_size=131072
max_used_connections=80
max_connections=400
threads_connected=68
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
= 1001468 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8e6d0c0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfb1f5d8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x808e1b7
0x82e5a08
0x80b68f6
0x804c0bd
0x804bfb3
0x808473c
0x808ddb3
0x809b8e6
0x82e31bc
0x830ca8a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at (nil)  is invalid pointer
thd->thread_id=52266
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
 snip
===




You mean 4.1.9, not 4.1.19 right? Try upgrading your server to the
latest 4.1, I believe its 4.1.20, and report back... I`ve seen various
issues with versions below 4.1.12...




--
Thanks & Regards,
Dilipkumar
DBA Support

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


Watch India vs. England LIVE, Hot videos and more only on Sify Max! Click Here. 
www.sifymax.com

Get to see what's happening in your favourite City on Bangalore Live! 
www.bangalorelive.in


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

Re: functions in AS

[Peter Brawley]

Jay,

  [snip]
  
  
We all know that you cannot do something like this;
sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate()
nor can you use user variables even though they get set properly
set @d1 = curdate();
sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1
So, does anyone know of a work around? 

  
  How about PREPARE?
[/snip]

PREPARE treats SELECT statements the same, unless I am missing
something. I have done some testing, and have been able to obtain the
desired results. Even the simplest example;

PREPARE stmt1 FROM 'SELECT curdate() AS ?';
SET @a = curdate();
EXECUTE stmt1 USING @a;

Gives a syntax error on the PREPARE statement since AS cannot be a
function. Perhaps there is something to a prepare that I should be more
aware of?
  

SET @sql=CONCAT('SELECT SUM(IF(SUBSTRING(updated,1,10)=CURDATE(),
1, 0)) AS ',
    char(39),
    CURDATE(),
    CHAR(39),
    ' FROM tablename GROUP BY group by psDealer' );
PREPARE stmt FROM @sql;

PB

-    ;


  


  



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.3/358 - Release Date: 6/7/2006


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

Re: MySQL crashes

[Daniel da Veiga]

On 6/7/06, Ian Collins <[EMAIL PROTECTED]> wrote:

I have a site running MySQL 4.1.19.
When I was first given the problem, the site only stayed up for a couple
of days - they then found they had to reboot (as opposed to just
restarting MySQL).
The error message they were getting is below (between the snip's).

After looking at the site, I altered some my.cnf parameters, and then
the site stayed up for 14 days before doing the same.

This site is identical to others I have seen - that are running with no
issues.
Could this be hardware (memory related)?

Regards,
Ian Collins.


 snip
===
Version: '4.1.9-standard'  socket: '/tmp/mysql.sock'  port: 3306
Official MySQL RPM
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail.

key_buffer_size=134217728
read_buffer_size=131072
max_used_connections=80
max_connections=400
threads_connected=68
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
= 1001468 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8e6d0c0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfb1f5d8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x808e1b7
0x82e5a08
0x80b68f6
0x804c0bd
0x804bfb3
0x808473c
0x808ddb3
0x809b8e6
0x82e31bc
0x830ca8a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at (nil)  is invalid pointer
thd->thread_id=52266
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
 snip
===




You mean 4.1.9, not 4.1.19 right? Try upgrading your server to the
latest 4.1, I believe its 4.1.20, and report back... I`ve seen various
issues with versions below 4.1.12...

--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--

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

RE: MySQL (GPL License)

[Jimmy Guerrero]

Hello,

If you are still unsure about the licensing, this may help...

http://www.mysql.com/company/legal/licensing/ 

http://www.mysql.com/company/legal/licensing/faq.html

There are also related links off to the right concerning the Open Source
License and Commercial License.

Thanks,

Jimmy Guerrero
Sr Product Manager
MySQL, Inc




> -Original Message-
> From: Michael Louie Loria [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, June 07, 2006 8:42 PM
> To: mysql@lists.mysql.com
> Subject: Re: MySQL (GPL License)
> 
> Ok thanks, I'm somehow clarified.
> 
> Mic
> 
> 
> Douglas Sims wrote:
> > I believe that if you are only using MySQL for your 
> company's internal 
> > needs, whether from a web server or for deployment to other 
> > company-owned locations, you don't need a commercial license.  For 
> > example, if your company owns fifiteen stores, you could set up a 
> > MySQL-based point-of-sale system at each one without needing a 
> > commercial license.  You only need to release your source 
> code if you 
> > release your compiled code.
> > 
> > Also, I believe the GPL requirement for sharing only applies if you 
> > have modified MySQL's object code, i.e. compiled your code 
> into it or 
> > it into your code or linked object code to it.  If you are simply 
> > installing it as a database and communicating to it through DBI or 
> > ODBC or some other means which uses sockets or ports, you 
> don't need 
> > to release your code under the GPL.
> > 
> > Thus, you hardly ever need to purchase a commercial license.
> > 
> > Please note that this is just my understanding.  I hope 
> someone will 
> > correct if I have misstated anything here.
> > 
> > However, it is very reasonable and desirable to support MySQL as a 
> > company, as they save us all tons of money over Oracle, 
> MS-SQL, etc., 
> > in addition to providing an excellent product.  So even if 
> you don't 
> > need the commercial license, if your company depends upon MySQL, 
> > buying a commercial license, paying for training, attending 
> > conferences, or buying lots of t-shirts is nice.
> > 
> > Douglas Sims
> > [EMAIL PROTECTED]
> > 
> > 
> > 
> > On Jun 7, 2006, at 7:58 PM, Logan, David (SST - Adelaide) wrote:
> > 
> >> Hi Michael,
> >>
> >> I believe you can purchase a commercial license taking 
> away the GPL 
> >> provisions from your software if you do not wish to GPL your own 
> >> software. You can enquire on the MySQL website.
> >>
> >> 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: Michael Louie Loria [mailto:[EMAIL PROTECTED]
> >> Sent: Thursday, 8 June 2006 9:45 AM
> >> To: mysql@lists.mysql.com
> >> Subject: MySQL (GPL License)
> >>
> >> Hello,
> >>
> >> I would like to inquire about the GPL License used by MySQL.
> >>
> >> Here's our scenario
> >>
> >> We developed our owned software needed by our operations 
> using MySQL 
> >> community edition under Windows platform.
> >>
> >> GPL says that we should distribute/share the source code. 
> But I think 
> >> it isn't even of interest or beneficial to others because 
> it was done 
> >> on the company specs.
> >>
> >> If we were required to distribute/share our source code. What 
> >> distribution methods can be used? like uploading the 
> source code in a 
> >> site? or when someone walks in and asks for the source code, we 
> >> should share it to them.
> >>
> >> Thanks, I just need to have some clarifications about the GPL
> >>
> >> Mic
> 
> 


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

Re: How to find matching tables that have specific field name.

[Frank]

William Scott wrote:

> Dear Sir,
>
>   I have a database with over 80 tables.  Is there an easy way to find
>   table names that has PERSON_ID field using SELECT query?
>
>   Thanks in advance for any help.
>
>
Hi Scott,

if you are using version 5.0 or higher, you can query the schema tables in
the information_schema database:
http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

In case you are using an older version, you need to issue a "show tables"
query, iterate over all the tables, execute a "describe TABLE" statement
for each and parse the output.

Cheers
Frank


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

RE: MySQL (GPL License)

[Daevid Vincent]

> However, it is very reasonable and desirable to support MySQL as a  
> company, as they save us all tons of money over Oracle, 
> MS-SQL, etc.,  
> in addition to providing an excellent product.  So even if you don't  
> need the commercial license, if your company depends upon MySQL,  
> buying a commercial license, paying for training, attending  
> conferences, or buying lots of t-shirts is nice.

Amen brutha.

DÆVID  


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

Re: MySQL (GPL License)

[Michael Louie Loria]

Ok thanks, I'm somehow clarified.

Mic


Douglas Sims wrote:
> I believe that if you are only using MySQL for your company's internal
> needs, whether from a web server or for deployment to other
> company-owned locations, you don't need a commercial license.  For
> example, if your company owns fifiteen stores, you could set up a
> MySQL-based point-of-sale system at each one without needing a
> commercial license.  You only need to release your source code if you
> release your compiled code.
> 
> Also, I believe the GPL requirement for sharing only applies if you have
> modified MySQL's object code, i.e. compiled your code into it or it into
> your code or linked object code to it.  If you are simply installing it
> as a database and communicating to it through DBI or ODBC or some other
> means which uses sockets or ports, you don't need to release your code
> under the GPL.
> 
> Thus, you hardly ever need to purchase a commercial license.
> 
> Please note that this is just my understanding.  I hope someone will
> correct if I have misstated anything here.
> 
> However, it is very reasonable and desirable to support MySQL as a
> company, as they save us all tons of money over Oracle, MS-SQL, etc., in
> addition to providing an excellent product.  So even if you don't need
> the commercial license, if your company depends upon MySQL, buying a
> commercial license, paying for training, attending conferences, or
> buying lots of t-shirts is nice.
> 
> Douglas Sims
> [EMAIL PROTECTED]
> 
> 
> 
> On Jun 7, 2006, at 7:58 PM, Logan, David (SST - Adelaide) wrote:
> 
>> Hi Michael,
>>
>> I believe you can purchase a commercial license taking away the GPL
>> provisions from your software if you do not wish to GPL your own
>> software. You can enquire on the MySQL website.
>>
>> 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: Michael Louie Loria [mailto:[EMAIL PROTECTED]
>> Sent: Thursday, 8 June 2006 9:45 AM
>> To: mysql@lists.mysql.com
>> Subject: MySQL (GPL License)
>>
>> Hello,
>>
>> I would like to inquire about the GPL License used by MySQL.
>>
>> Here's our scenario
>>
>> We developed our owned software needed by our operations using MySQL
>> community edition under Windows platform.
>>
>> GPL says that we should distribute/share the source code. But I think it
>> isn't even of interest or beneficial to others because it was done on
>> the company specs.
>>
>> If we were required to distribute/share our source code. What
>> distribution methods can be used? like uploading the source code in a
>> site? or when someone walks in and asks for the source code, we should
>> share it to them.
>>
>> Thanks, I just need to have some clarifications about the GPL
>>
>> Mic



signature.asc
Description: OpenPGP digital signature

MySQL crashes

[Ian Collins]

I have a site running MySQL 4.1.19.
When I was first given the problem, the site only stayed up for a couple 
of days - they then found they had to reboot (as opposed to just 
restarting MySQL).

The error message they were getting is below (between the snip's).

After looking at the site, I altered some my.cnf parameters, and then 
the site stayed up for 14 days before doing the same.


This site is identical to others I have seen - that are running with no 
issues.

Could this be hardware (memory related)?

Regards,
Ian Collins.


 snip 
===
Version: '4.1.9-standard'  socket: '/tmp/mysql.sock'  port: 3306  
Official MySQL RPM

mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help 
diagnose
the problem, but since we have already crashed, something is definitely 
wrong

and this may fail.

key_buffer_size=134217728
read_buffer_size=131072
max_used_connections=80
max_connections=400
threads_connected=68
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections 
= 1001468 K

bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8e6d0c0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfb1f5d8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x808e1b7
0x82e5a08
0x80b68f6
0x804c0bd
0x804bfb3
0x808473c
0x808ddb3
0x809b8e6
0x82e31bc
0x830ca8a
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and 
follow instructions on how to resolve the stack trace. Resolved

stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at (nil)  is invalid pointer
thd->thread_id=52266
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
 snip 
===




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

Re: Copying tables sans data from one database to another

[Douglas Sims]

Perhaps the easiest way is with CREATE TABLE... SELECT.  For example,  
if I have a database called "NYCNH" (by coincidence, I do!) which  
contains a table called "checks" and I want to copy that table to a  
database called "TEST" I could do this:


mysql> create table test.checks select * from nycnh.checks;
Query OK, 80030 rows affected (0.88 sec)
Records: 80030  Duplicates: 0  Warnings: 0

and that would create a copy of the structure and data from "checks"  
and put it in the database "test."


I believe this will not copy triggers or indexes from the original  
table.  If you need to do this, you can do it by using the mysqldump  
program to dump the table (just the structure or the structure and  
data) from the original database and then load it into the new one:  
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html


Good luck!

Douglas Sims
[EMAIL PROTECTED]



On Jun 7, 2006, at 8:09 PM, murthy gandikota wrote:


How can I copy tables from one database to another on the same host?

  Thanks for your help
  Murthy

 __
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com



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

Re: MySQL (GPL License)

[Douglas Sims]

I believe that if you are only using MySQL for your company's  
internal needs, whether from a web server or for deployment to other  
company-owned locations, you don't need a commercial license.  For  
example, if your company owns fifiteen stores, you could set up a  
MySQL-based point-of-sale system at each one without needing a  
commercial license.  You only need to release your source code if you  
release your compiled code.


Also, I believe the GPL requirement for sharing only applies if you  
have modified MySQL's object code, i.e. compiled your code into it or  
it into your code or linked object code to it.  If you are simply  
installing it as a database and communicating to it through DBI or  
ODBC or some other means which uses sockets or ports, you don't need  
to release your code under the GPL.


Thus, you hardly ever need to purchase a commercial license.

Please note that this is just my understanding.  I hope someone will  
correct if I have misstated anything here.


However, it is very reasonable and desirable to support MySQL as a  
company, as they save us all tons of money over Oracle, MS-SQL, etc.,  
in addition to providing an excellent product.  So even if you don't  
need the commercial license, if your company depends upon MySQL,  
buying a commercial license, paying for training, attending  
conferences, or buying lots of t-shirts is nice.


Douglas Sims
[EMAIL PROTECTED]



On Jun 7, 2006, at 7:58 PM, Logan, David (SST - Adelaide) wrote:


Hi Michael,

I believe you can purchase a commercial license taking away the GPL
provisions from your software if you do not wish to GPL your own
software. You can enquire on the MySQL website.

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: Michael Louie Loria [mailto:[EMAIL PROTECTED]
Sent: Thursday, 8 June 2006 9:45 AM
To: mysql@lists.mysql.com
Subject: MySQL (GPL License)

Hello,

I would like to inquire about the GPL License used by MySQL.

Here's our scenario

We developed our owned software needed by our operations using MySQL
community edition under Windows platform.

GPL says that we should distribute/share the source code. But I  
think it

isn't even of interest or beneficial to others because it was done on
the company specs.

If we were required to distribute/share our source code. What
distribution methods can be used? like uploading the source code in a
site? or when someone walks in and asks for the source code, we should
share it to them.

Thanks, I just need to have some clarifications about the GPL

Mic


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

Copying tables sans data from one database to another

[murthy gandikota]

How can I copy tables from one database to another on the same host?
   
  Thanks for your help
  Murthy

 __
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: mysqldump locks dumping db "mysql" in 5.1.9

[Greg 'groggy' Lehey]

On Wednesday,  7 June 2006 at 11:35:56 -0700, James Barros wrote:
> Hello,
>
> I've got 5.1.9 running on FreeBSD and I'm trying to do some backups,
> but mysqldump is locking up on me.
>
> when I run:
> mysqldump -uroot --opt mysql
> (and yes, it's a test box, so there is no root password. I can
> duplicate this behavior with other accounts with sufficient priv's
> specifying a password)
>
> I get:
>
> 
> /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
>
> and then it just freezes there till I +c out.
>
> Is this a legit bug I should report to the buglist or did I screw
> something up?

mysqldump should not hang, so it's a bug.  The real question is how
you managed to trip it.  Please enter a bug report and I'll look at it
as soon as possible.

Greg
--
Greg Lehey, Senior Software Engineer
MySQL AB, http://www.mysql.com/
Echunga, South Australia
Phone: +61-8-8388-8286   Mobile: +61-418-838-708
VoIP:  sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED]

Are you MySQL certified?  http://www.mysql.com/certification/


pgprqsw4l2kvI.pgp
Description: PGP signature

Re: How to find matching tables that have specific field name.

[Douglas Sims]

Hello William

If you are using MySQL version 5.0 and up, you can select from the  
TABLE table in the INFORMATION_SCHEMA database.  This database  
contains information about all of the structures in the MySQL server.


For example, if you are looking for all of the tables in a database  
called "PHXTC," you could do this:


mysql> select table_name from information_schema.tables where  
table_schema='phxtc';

++
| table_name |
++
| accounts   |
| check_items|
| checks |
| discounts  |
| employees  |
| familygroups   |
| items  |
| items_accounts |
| majorgroups|
| periods|
| refs   |
| rev_period |
| revenuecenters |
| servicecharges |
| t  |
| taxclass_rates |
| taxclasses |
| taxrates   |
| tendermedia|
++
19 rows in set (0.11 sec)

Note that there is quite a lot of other useful information in  
INFORMATION_SCHEMA also.  In this example i ust selected table names  
from one database.  You can do this to see all of the tables in  
INFORMATION_SCHEMA (or any other MySQL database):


mysql> show tables from information_schema;
+---+
| Tables_in_information_schema  |
+---+
| CHARACTER_SETS|
| COLLATIONS|
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS   |
| COLUMN_PRIVILEGES |
| KEY_COLUMN_USAGE  |
| ROUTINES  |
| SCHEMATA  |
| SCHEMA_PRIVILEGES |
| STATISTICS|
| TABLES|
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES  |
| TRIGGERS  |
| USER_PRIVILEGES   |
| VIEWS |
+---+
16 rows in set (0.00 sec)

And then to see specific information about one of these structures,  
the MySQL-specific command is quite useful:


mysql> describe tables;
+-+--+--+-+-+---+
| Field   | Type | Null | Key | Default | Extra |
+-+--+--+-+-+---+
| TABLE_CATALOG   | varchar(512) | YES  | | |   |
| TABLE_SCHEMA| varchar(64)  | NO   | | |   |
| TABLE_NAME  | varchar(64)  | NO   | | |   |
| TABLE_TYPE  | varchar(64)  | NO   | | |   |
| ENGINE  | varchar(64)  | YES  | | |   |
| VERSION | bigint(21)   | YES  | | |   |
| ROW_FORMAT  | varchar(10)  | YES  | | |   |
| TABLE_ROWS  | bigint(21)   | YES  | | |   |
| AVG_ROW_LENGTH  | bigint(21)   | YES  | | |   |
| DATA_LENGTH | bigint(21)   | YES  | | |   |
| MAX_DATA_LENGTH | bigint(21)   | YES  | | |   |
| INDEX_LENGTH| bigint(21)   | YES  | | |   |
| DATA_FREE   | bigint(21)   | YES  | | |   |
| AUTO_INCREMENT  | bigint(21)   | YES  | | |   |
| CREATE_TIME | datetime | YES  | | |   |
| UPDATE_TIME | datetime | YES  | | |   |
| CHECK_TIME  | datetime | YES  | | |   |
| TABLE_COLLATION | varchar(64)  | YES  | | |   |
| CHECKSUM| bigint(21)   | YES  | | |   |
| CREATE_OPTIONS  | varchar(255) | YES  | | |   |
| TABLE_COMMENT   | varchar(80)  | NO   | | |   |
+-+--+--+-+-+---+
21 rows in set (0.07 sec)


Good luck!

Douglas Sims
[EMAIL PROTECTED]



On Jun 7, 2006, at 3:19 PM, William Scott wrote:


Dear Sir,

  I have a database with over 80 tables.  Is there an easy way to  
find table names that has PERSON_ID field using SELECT query?


  Thanks in advance for any help.





 __
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com



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

RE: MySQL (GPL License)

[Logan, David (SST - Adelaide)]

Hi Michael,

I believe you can purchase a commercial license taking away the GPL
provisions from your software if you do not wish to GPL your own
software. You can enquire on the MySQL website.

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: Michael Louie Loria [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 8 June 2006 9:45 AM
To: mysql@lists.mysql.com
Subject: MySQL (GPL License)

Hello,

I would like to inquire about the GPL License used by MySQL.

Here's our scenario

We developed our owned software needed by our operations using MySQL
community edition under Windows platform.

GPL says that we should distribute/share the source code. But I think it
isn't even of interest or beneficial to others because it was done on
the company specs.

If we were required to distribute/share our source code. What
distribution methods can be used? like uploading the source code in a
site? or when someone walks in and asks for the source code, we should
share it to them.

Thanks, I just need to have some clarifications about the GPL

Mic


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

How to find matching tables that have specific field name.

[William Scott]

Dear Sir,
   
  I have a database with over 80 tables.  Is there an easy way to find table 
names that has PERSON_ID field using SELECT query?
   
  Thanks in advance for any help.
   
   
   
   

 __
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

MySQL (GPL License)

[Michael Louie Loria]

Hello,

I would like to inquire about the GPL License used by MySQL.

Here's our scenario

We developed our owned software needed by our operations using MySQL
community edition under Windows platform.

GPL says that we should distribute/share the source code. But I think it
isn't even of interest or beneficial to others because it was done on
the company specs.

If we were required to distribute/share our source code. What
distribution methods can be used? like uploading the source code in a
site? or when someone walks in and asks for the source code, we should
share it to them.

Thanks, I just need to have some clarifications about the GPL

Mic



signature.asc
Description: OpenPGP digital signature

Re: Windows Compiled Help MySQL Reference Manual -- Error

[Paul DuBois]

At 17:30 -0600 6/7/06, Jake Peavy wrote:

Hey yall,

I'm unable to open the .chm I just downloaded for 5.1.  I get the following
error:

Cannot open the file: mk:@MSITStore:C:\Documents and Settings\jpeavy1\My

Documents\refman-5.1-en.chm.



Any ideas?


It does seem to be corrupt.  We'll take a look into it.  Thanks.

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

Windows Compiled Help MySQL Reference Manual -- Error

[Jake Peavy]

Hey yall,

I'm unable to open the .chm I just downloaded for 5.1.  I get the following
error:

Cannot open the file: mk:@MSITStore:C:\Documents and Settings\jpeavy1\My

Documents\refman-5.1-en.chm.



Any ideas?

TIA

--
-jp


One time in an airport a guy accidently called Chuck Norris "Chick Norris".
He explained it was an honest mistake and apologized profusely. Chuck
accepted his apology and politely signed an autograph. Nine months later,
the guy's wife gave birth to a bearded baby. The guy knew exactly what had
happened, and blames nobody but himself.

RE: functions in AS

[Jay Blanchard]

[snip]
>We all know that you cannot do something like this;
>sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate()
>nor can you use user variables even though they get set properly
>set @d1 = curdate();
>sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1
>So, does anyone know of a work around? 

How about PREPARE?
[/snip]

PREPARE treats SELECT statements the same, unless I am missing
something. I have done some testing, and have been able to obtain the
desired results. Even the simplest example;

PREPARE stmt1 FROM 'SELECT curdate() AS ?';
SET @a = curdate();
EXECUTE stmt1 USING @a;

Gives a syntax error on the PREPARE statement since AS cannot be a
function. Perhaps there is something to a prepare that I should be more
aware of?


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

Table size, db size and hence disk space.

[Vidya Biju]

I am new to databases and mysql in particular. I need to provide the
specifications for database size for a new design...

Lets assume one of my tables has the following structure


column1  int
column2  int
column3  varchar(200)
column4  char(1)
column5  int
column6  text
column7  datetime
column8  datetime

which gives an approximate value of 630 bytes for a row in this table  and
given that I can have 48000 such rows in this table taking the table size to
30MB. Note that I have not taken into account the indexes and foreign
keys... How much space do these take? I have 6 such innodb tables more or
less the same size.

How can I reach to a number for database size  and hence disk space?

Thanks

Re: ERROR 1045 (28000): Access denied for user (Using password: YES) from outside localhost

[James Barros]

Can this use log in from localhost successfully?
Try flushing privileges again?
If that failed, I'd drop the user and recreate?
( Theres really not alot to this, sorry I don't have any more ideas :( )

-- James

On Jun 7, 2006, at 1:07 PM, Aron Levy wrote:

Hi James i follow your recomendation and verify that the hash  
produced  matches the password field in user exactly.


-- Aron L.

James Barros wrote:

Well, for starters don't worry about ports being open. If you  
werent  able to connect, it would give you a different error message.


select password('yourpassword'); and verify that the hash  
produced  matches the password field in user exactly.


-- James


On Jun 7, 2006, at 11:48 AM, Aron Levy wrote:

Hi good afternoon, i am triying to connect with my company's  
mysql  server using a client program from shell and I keep  
receiving the  following answer:


[EMAIL PROTECTED] ~]# mysql -u user1 -p -h serverCompany.com
Enter password:
ERROR 1045 (28000): Access denied for user   
'user1'@'200.93.42.209' (using password: YES)

[EMAIL PROTECTED] ~]#

I already checked the user was configured for any host ("%"),  
the  port 3306 is open (checked with nmap), but i dont know what  
i need  to do or see other variables.  I am 100% sure of the  
passwords i am  using for this users, and i am attaching some  
info about the database.


Thank you in advance,


mysql> select * from user;
+---+--+--+-  
+-+-+-+- 
+--- +-+---+-- 
+--- ++-+ 
++
| Host  | User | Password | Select_priv |   
Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv  
|  Reload_priv | Shutdown_priv |Process_priv | File_priv |  
Grant_priv  | References_priv | Index_priv | Alter_priv |
+---+--+--+-  
+-+-+-+- 
+--- +-+---+-- 
+--- ++-+ 
++
| % | user1  | 5804b8eebr456240 | N   |  
N| N   | N   | N   |  
N | N| N | N|  
N | N  |  N   | N  |  
N  |
| % | user4 | 6f37d73jsdred201d | N   |  
N| N   | N   | N   |  
N | N| N | N|  
N | N  |  N   | N  |  
N  |


[EMAIL PROTECTED] ~]# nmap -A -T4 my.domain.com

Starting Nmap 4.03 ( http://www.insecure.org/nmap/ ) at  
2006-06-07  09:57 VET

Interesting ports on xx.xx.xx.xx:
(The 1653 ports scanned but not shown below are in state: closed)
PORT STATESERVICEVERSION
21/tcp   open ftpProFTPD 1.2.10
22/tcp   open sshOpenSSH 3.9p1 (protocol 1.99)
25/tcp   open smtp   netqmail smtpd 1.04
53/tcp   open domain
80/tcp   open http   Apache httpd 2.0.53 ((Fedora))
106/tcp  open pop3pw poppassd
110/tcp  open pop3   Courier pop3d
111/tcp  open rpcbind 2 (rpc #10)
135/tcp  filtered msrpc
139/tcp  filtered netbios-ssn
143/tcp  open imap   Courier Imapd (released 2004)
443/tcp  open ssl/http   Apache httpd 2.0.53 ((Fedora))
445/tcp  filtered microsoft-ds
465/tcp  open ssl/smtp   netqmail smtpd 1.04
593/tcp  filtered http-rpc-epmap
993/tcp  open ssl/imap   Courier Imapd (released 2004)
995/tcp  open ssl/pop3   Courier pop3d
1720/tcp filtered H.323/Q.931
3306/tcp open mysql  MySQL 4.1.15
/tcp filtered krb524
8443/tcp open http   Apache httpd 1.3.33 ((Unix)   
mod_ssl/2.8.22 OpenSSL/0.9.7e PHP/5.0.4)

Device type: general purpose
Running: Linux 2.6.X
OS details: Linux 2.6.5 - 2.6.11
Uptime 26.627 days (since Thu May 11 18:56:31 2006)
Service Info: OS: Unix

Nmap finished: 1 IP address (1 host up) scanned in 63.549 seconds

mysql

MySQL Support
Active Persistent Links
Active Links
Client API version 4.1.15
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /var/lib/mysql/mysql.sock
MYSQL_INCLUDE -I/usr/include/mysql
MYSQL_LIBS -L/usr/lib/mysql -lmysqlclient

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









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






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

Re: ERROR 1045 (28000): Access denied for user (Using password: YES) from outside localhost

[Daniel da Veiga]

On 6/7/06, Aron Levy <[EMAIL PROTECTED]> wrote:

Hi James i follow your recomendation and verify that the hash produced
matches the password field in user exactly.

-- Aron L.



Well, that's weird, should be working. A shot in the dark: try
starting your server with "--old-passwords" as an option and see if it
works.

--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--

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

Re: ERROR 1045 (28000): Access denied for user (Using password: YES) from outside localhost

[Aron Levy]

Hi James i follow your recomendation and verify that the hash produced  
matches the password field in user exactly.


-- Aron L.

James Barros wrote:

Well, for starters don't worry about ports being open. If you werent  
able to connect, it would give you a different error message.


select password('yourpassword'); and verify that the hash produced  
matches the password field in user exactly.


-- James


On Jun 7, 2006, at 11:48 AM, Aron Levy wrote:

Hi good afternoon, i am triying to connect with my company's mysql  
server using a client program from shell and I keep receiving the  
following answer:


[EMAIL PROTECTED] ~]# mysql -u user1 -p -h serverCompany.com
Enter password:
ERROR 1045 (28000): Access denied for user  'user1'@'200.93.42.209' 
(using password: YES)

[EMAIL PROTECTED] ~]#

I already checked the user was configured for any host ("%"), the  
port 3306 is open (checked with nmap), but i dont know what i need  
to do or see other variables.  I am 100% sure of the passwords i am  
using for this users, and i am attaching some info about the database.


Thank you in advance,


mysql> select * from user;
+---+--+--+- 
+-+-+-+-+--- 
+-+---+--+--- 
++-+++
| Host  | User | Password | Select_priv |  
Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv |  
Reload_priv | Shutdown_priv |Process_priv | File_priv | Grant_priv  | 
References_priv | Index_priv | Alter_priv |
+---+--+--+- 
+-+-+-+-+--- 
+-+---+--+--- 
++-+++
| % | user1  | 5804b8eebr456240 | N   | N
| N   | N   | N   | N | N
| N | N| N | N  |  
N   | N  | N  |
| % | user4 | 6f37d73jsdred201d | N   | N
| N   | N   | N   | N | N
| N | N| N | N  |  
N   | N  | N  |


[EMAIL PROTECTED] ~]# nmap -A -T4 my.domain.com

Starting Nmap 4.03 ( http://www.insecure.org/nmap/ ) at 2006-06-07  
09:57 VET

Interesting ports on xx.xx.xx.xx:
(The 1653 ports scanned but not shown below are in state: closed)
PORT STATESERVICEVERSION
21/tcp   open ftpProFTPD 1.2.10
22/tcp   open sshOpenSSH 3.9p1 (protocol 1.99)
25/tcp   open smtp   netqmail smtpd 1.04
53/tcp   open domain
80/tcp   open http   Apache httpd 2.0.53 ((Fedora))
106/tcp  open pop3pw poppassd
110/tcp  open pop3   Courier pop3d
111/tcp  open rpcbind 2 (rpc #10)
135/tcp  filtered msrpc
139/tcp  filtered netbios-ssn
143/tcp  open imap   Courier Imapd (released 2004)
443/tcp  open ssl/http   Apache httpd 2.0.53 ((Fedora))
445/tcp  filtered microsoft-ds
465/tcp  open ssl/smtp   netqmail smtpd 1.04
593/tcp  filtered http-rpc-epmap
993/tcp  open ssl/imap   Courier Imapd (released 2004)
995/tcp  open ssl/pop3   Courier pop3d
1720/tcp filtered H.323/Q.931
3306/tcp open mysql  MySQL 4.1.15
/tcp filtered krb524
8443/tcp open http   Apache httpd 1.3.33 ((Unix)  
mod_ssl/2.8.22 OpenSSL/0.9.7e PHP/5.0.4)

Device type: general purpose
Running: Linux 2.6.X
OS details: Linux 2.6.5 - 2.6.11
Uptime 26.627 days (since Thu May 11 18:56:31 2006)
Service Info: OS: Unix

Nmap finished: 1 IP address (1 host up) scanned in 63.549 seconds

mysql

MySQL Support
Active Persistent Links
Active Links
Client API version 4.1.15
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /var/lib/mysql/mysql.sock
MYSQL_INCLUDE -I/usr/include/mysql
MYSQL_LIBS -L/usr/lib/mysql -lmysqlclient

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









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

Re: functions in AS

[Peter Brawley]

Jay,


We all know that you cannot do something like this;
sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate()
nor can you use user variables even though they get set properly
set @d1 = curdate();
sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1
So, does anyone know of a work around? 



How about PREPARE?

PB
-

Jay Blanchard wrote:
select psDealerID, 
sum(if(substring(updated, 1, 10) = curdate(), 1, 0)), 
sum(if(substring(updated, 1, 10) = date_sub(curdate(), interval 1 day),
1, 0)) 
from provision 
group by psDealerID


I love crosstab queries, but one thing really eats at me. I'd like to be
able to add significance to the AS with a function. So that the return
would look something like;

++++
| psDealerID | 2006-06-07 | 2006-06-06 |
++++
|| 0  | 4  |
| 301AA  | 0  | 0  |
| 301AB  | 2  | 0  |
| 302AA  | 0  | 0  |
| 303AA  | 0  | 1  |

We all know that you cannot do something like this;

sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate()

nor can you use user variables even though they get set properly

set @d1 = curdate();

sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1

So, does anyone know of a work around? I have RTFM and STFW...but to no
avail.

Thanks!


  



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.3/358 - Release Date: 6/7/2006


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

TABLE and VIEW have same IDENTIFIER: PLEASE HELP

[murthy gandikota]

I created a sql file for database sfg as follows:

mysqldump -uuser -ppassword sfg > backup.sql

Then I tried to reload it in another database sfg2

mysql -uuser -ppassword sfg2 < backup.sql

I get the error message saying the table already exists. I traced the error to
the lines where the views have the same identifier as the tables. I don't
know if it is possible to have views and tables the same identifier. I didn't
create these views. 

Can someone please help me figure this out?

Thanks
Murthy

 __
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: ERROR 1045 (28000): Access denied for user (Using password: YES) from outside localhost

[James Barros]

Well, for starters don't worry about ports being open. If you werent  
able to connect, it would give you a different error message.


select password('yourpassword'); and verify that the hash produced  
matches the password field in user exactly.


-- James


On Jun 7, 2006, at 11:48 AM, Aron Levy wrote:

Hi good afternoon, i am triying to connect with my company's mysql  
server using a client program from shell and I keep receiving the  
following answer:


[EMAIL PROTECTED] ~]# mysql -u user1 -p -h serverCompany.com
Enter password:
ERROR 1045 (28000): Access denied for user  
'user1'@'200.93.42.209' (using password: YES)

[EMAIL PROTECTED] ~]#

I already checked the user was configured for any host ("%"), the  
port 3306 is open (checked with nmap), but i dont know what i need  
to do or see other variables.  I am 100% sure of the passwords i am  
using for this users, and i am attaching some info about the database.


Thank you in advance,


mysql> select * from user;
+---+--+--+- 
+-+-+-+-+--- 
+-+---+--+--- 
++-+++
| Host  | User | Password | Select_priv |  
Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv |  
Reload_priv | Shutdown_priv |Process_priv | File_priv | Grant_priv  
| References_priv | Index_priv | Alter_priv |
+---+--+--+- 
+-+-+-+-+--- 
+-+---+--+--- 
++-+++
| % | user1  | 5804b8eebr456240 | N   | N
| N   | N   | N   | N | N
| N | N| N | N  |  
N   | N  | N  |
| % | user4 | 6f37d73jsdred201d | N   | N
| N   | N   | N   | N | N
| N | N| N | N  |  
N   | N  | N  |


[EMAIL PROTECTED] ~]# nmap -A -T4 my.domain.com

Starting Nmap 4.03 ( http://www.insecure.org/nmap/ ) at 2006-06-07  
09:57 VET

Interesting ports on xx.xx.xx.xx:
(The 1653 ports scanned but not shown below are in state: closed)
PORT STATESERVICEVERSION
21/tcp   open ftpProFTPD 1.2.10
22/tcp   open sshOpenSSH 3.9p1 (protocol 1.99)
25/tcp   open smtp   netqmail smtpd 1.04
53/tcp   open domain
80/tcp   open http   Apache httpd 2.0.53 ((Fedora))
106/tcp  open pop3pw poppassd
110/tcp  open pop3   Courier pop3d
111/tcp  open rpcbind 2 (rpc #10)
135/tcp  filtered msrpc
139/tcp  filtered netbios-ssn
143/tcp  open imap   Courier Imapd (released 2004)
443/tcp  open ssl/http   Apache httpd 2.0.53 ((Fedora))
445/tcp  filtered microsoft-ds
465/tcp  open ssl/smtp   netqmail smtpd 1.04
593/tcp  filtered http-rpc-epmap
993/tcp  open ssl/imap   Courier Imapd (released 2004)
995/tcp  open ssl/pop3   Courier pop3d
1720/tcp filtered H.323/Q.931
3306/tcp open mysql  MySQL 4.1.15
/tcp filtered krb524
8443/tcp open http   Apache httpd 1.3.33 ((Unix)  
mod_ssl/2.8.22 OpenSSL/0.9.7e PHP/5.0.4)

Device type: general purpose
Running: Linux 2.6.X
OS details: Linux 2.6.5 - 2.6.11
Uptime 26.627 days (since Thu May 11 18:56:31 2006)
Service Info: OS: Unix

Nmap finished: 1 IP address (1 host up) scanned in 63.549 seconds

mysql

MySQL Support
Active Persistent Links
Active Links
Client API version 4.1.15
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /var/lib/mysql/mysql.sock
MYSQL_INCLUDE -I/usr/include/mysql
MYSQL_LIBS -L/usr/lib/mysql -lmysqlclient

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






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

functions in AS

[Jay Blanchard]

select psDealerID, 
sum(if(substring(updated, 1, 10) = curdate(), 1, 0)), 
sum(if(substring(updated, 1, 10) = date_sub(curdate(), interval 1 day),
1, 0)) 
from provision 
group by psDealerID

I love crosstab queries, but one thing really eats at me. I'd like to be
able to add significance to the AS with a function. So that the return
would look something like;

++++
| psDealerID | 2006-06-07 | 2006-06-06 |
++++
|| 0  | 4  |
| 301AA  | 0  | 0  |
| 301AB  | 2  | 0  |
| 302AA  | 0  | 0  |
| 303AA  | 0  | 1  |

We all know that you cannot do something like this;

sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate()

nor can you use user variables even though they get set properly

set @d1 = curdate();

sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1

So, does anyone know of a work around? I have RTFM and STFW...but to no
avail.

Thanks!


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

ERROR 1045 (28000): Access denied for user (Using password: YES) from outside localhost

[Aron Levy]

Hi good afternoon, i am triying to connect with my company's mysql 
server using a client program from shell and I keep receiving the 
following answer:


[EMAIL PROTECTED] ~]# mysql -u user1 -p -h serverCompany.com
Enter password:
ERROR 1045 (28000): Access denied for user 'user1'@'200.93.42.209' 
(using password: YES)

[EMAIL PROTECTED] ~]#

I already checked the user was configured for any host ("%"), the port 
3306 is open (checked with nmap), but i dont know what i need to do or 
see other variables.  I am 100% sure of the passwords i am using for 
this users, and i am attaching some info about the database.


Thank you in advance,


mysql> select * from user;
+---+--+--+-+-+-+-+-+---+-+---+--+---++-+++
| Host  | User | Password | Select_priv | 
Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | 
Reload_priv | Shutdown_priv |Process_priv | File_priv | Grant_priv | 
References_priv | Index_priv | Alter_priv |

+---+--+--+-+-+-+-+-+---+-+---+--+---++-+++
| % | user1  | 5804b8eebr456240 | N   | N   | 
N   | N   | N   | N | N   | 
N | N| N | N  | N   
| N  | N  |
| % | user4 | 6f37d73jsdred201d | N   | N   | 
N   | N   | N   | N | N   | 
N | N| N | N  | N   
| N  | N  |


[EMAIL PROTECTED] ~]# nmap -A -T4 my.domain.com

Starting Nmap 4.03 ( http://www.insecure.org/nmap/ ) at 2006-06-07 09:57 VET
Interesting ports on xx.xx.xx.xx:
(The 1653 ports scanned but not shown below are in state: closed)
PORT STATESERVICEVERSION
21/tcp   open ftpProFTPD 1.2.10
22/tcp   open sshOpenSSH 3.9p1 (protocol 1.99)
25/tcp   open smtp   netqmail smtpd 1.04
53/tcp   open domain
80/tcp   open http   Apache httpd 2.0.53 ((Fedora))
106/tcp  open pop3pw poppassd
110/tcp  open pop3   Courier pop3d
111/tcp  open rpcbind 2 (rpc #10)
135/tcp  filtered msrpc
139/tcp  filtered netbios-ssn
143/tcp  open imap   Courier Imapd (released 2004)
443/tcp  open ssl/http   Apache httpd 2.0.53 ((Fedora))
445/tcp  filtered microsoft-ds
465/tcp  open ssl/smtp   netqmail smtpd 1.04
593/tcp  filtered http-rpc-epmap
993/tcp  open ssl/imap   Courier Imapd (released 2004)
995/tcp  open ssl/pop3   Courier pop3d
1720/tcp filtered H.323/Q.931
3306/tcp open mysql  MySQL 4.1.15
/tcp filtered krb524
8443/tcp open http   Apache httpd 1.3.33 ((Unix) 
mod_ssl/2.8.22 OpenSSL/0.9.7e PHP/5.0.4)

Device type: general purpose
Running: Linux 2.6.X
OS details: Linux 2.6.5 - 2.6.11
Uptime 26.627 days (since Thu May 11 18:56:31 2006)
Service Info: OS: Unix

Nmap finished: 1 IP address (1 host up) scanned in 63.549 seconds

mysql

MySQL Support
Active Persistent Links
Active Links
Client API version 4.1.15
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /var/lib/mysql/mysql.sock
MYSQL_INCLUDE -I/usr/include/mysql
MYSQL_LIBS -L/usr/lib/mysql -lmysqlclient

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

mysqldump locks dumping db "mysql" in 5.1.9

[James Barros]

Hello,

I've got 5.1.9 running on FreeBSD and I'm trying to do some backups,  
but mysqldump is locking up on me.


when I run:
mysqldump -uroot --opt mysql
(and yes, it's a test box, so there is no root password. I can  
duplicate this behavior with other accounts with sufficient priv's  
specifying a password)


I get:

-- MySQL dump 10.10
--
-- Host: localhostDatabase: mysql
-- --
-- Server version   5.1.9-beta

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,  
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,  
SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

and then it just freezes there till I +c out.

It should be noted that I can dump all other db's just fine.

This version was installed using ports.

mysqldump -V returns:
mysqldump  Ver 10.10 Distrib 5.1.6-alpha, for portbld-freebsd6.1 (i386)

which appears to be 0.0.3 versions out of date compared to the  
server. Was there some change in DB structure that might cause this  
issue?


Other suggestions?

Is this a legit bug I should report to the buglist or did I screw  
something up?


Thanks :)

-- James

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

Re: Database Performance Tweaking

[Dan Buettner]

Craig, this is the right place, but performance is a pretty broad topic. 
 I personally highly recommend Jeremy Zawodny's book, 'High Performance 
MySQL'.  It's based on 4.1, so the info will be well suited to your 
install (I don't know if it will be updated for 5 ... anyone?).


You can also glean quite a bit of useful info from the docs on 
optimization, at:

http://dev.mysql.com/doc/refman/4.1/en/optimization.html

As a first stab, you might evaluate which of MySQL's included 'my.cnf' 
files fits your situation (read comments in file), and implement that 
(backing up your current file first just in case).


Good luck,
Dan



Cx Cx wrote:

Hi All,

Hope this is the right list to post to, excuse if it is not ;-)

Server scenario:

Dedicated server running MySQL 4 and secondary process inserting data into
MySQL

How does one calculate what the values should be for the following areas in
the my.cfg file to give optimal performance:

  - Key Buffer Size
  - Maximum Packet Size
  - Sort Buffer Size
  - Network Buffer Size
  - MyISAM Sort Buffer Size
  - Tables to Cache
  - etc

Basically anything that can be tweaked to enhance the servers perfomance.

Thanks In Advance

Craig



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

Re: Joins - Multiple rows from a single table

[Geoffrey Sneddon]

On 7 Jun 2006, at 18:56, Peter Brawley wrote:


Join users a second time for bugs.submitter, eg ...

SELECT
 bugs.id, bugs.title, UNIX_TIMESTAMP(bugs.submitted), bugs.fix,  
bugs.assignee,

 users.username,
 users2.username AS Submitter,
 bugs.category,
 category.name,
 bugs.version,
 version.name,
 bugs.priority, bugs.haspatch
FROM fb_bugs AS bugs
INNER JOIN fb_users AS users ON bugs.assignee = users.id
INNER JOIN fb_users AS users2 ON bugs.submitter = users2.id
INNER JOIN fb_category AS category ON bugs.category = category.id
INNER JOIN fb_versions AS version ON bugs.version = version.id

PB


Much thanks, that's saved me… this time. :)

All the best,

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

Re: Joins - Multiple rows from a single table

[Peter Brawley]

Geoffrey,

>SELECT
>  bugs.id, bugs.assignee, users.username, bugs.submitter
>FROM fb_bugs AS bugs,
>fb_users AS users
>WHERE users.id = bugs.assignee

>My problem is that I also want the users.username for bugs.submitter. 
How can I do that?


Join users a second time for bugs.submitter, eg ...

SELECT
 bugs.id, bugs.title, UNIX_TIMESTAMP(bugs.submitted), bugs.fix, 
bugs.assignee,

 users.username,
 users2.username AS Submitter,
 bugs.category,
 category.name,
 bugs.version,
 version.name,
 bugs.priority, bugs.haspatch
FROM fb_bugs AS bugs
INNER JOIN fb_users AS users ON bugs.assignee = users.id
INNER JOIN fb_users AS users2 ON bugs.submitter = users2.id
INNER JOIN fb_category AS category ON bugs.category = category.id
INNER JOIN fb_versions AS version ON bugs.version = version.id

PB

-

Geoffrey Sneddon wrote:

I've got the following query:

SELECT `bugs`.`id`, `bugs`.`assignee`, `users`.`username`, 
`bugs`.`submitter` FROM `fb_bugs` AS `bugs`, `fb_users` AS `users` 
WHERE `users`.`id` = `bugs`.`assignee`


My problem is that I also want the `users`.`username` for 
`bugs`.`submitter`. How can I do that?


Also, anyway to make the entire query better (the above is just the 
relevant part):


SELECT `bugs`.`id`, `bugs`.`title`, 
UNIX_TIMESTAMP(`bugs`.`submitted`), `bugs`.`fix`, `bugs`.`assignee`, 
`users`.`username`, `bugs`.`category`, `category`.`name`, 
`bugs`.`version`, `version`.`name`, `bugs`.`priority`, 
`bugs`.`haspatch`FROM `fb_bugs` AS `bugs`, `fb_users` AS `users`, 
`fb_category` AS `category`, `fb_versions` AS `version` WHERE 
`users`.`id` = `bugs`.`assignee` AND `category`.`id` = 
`bugs`.`category` AND `version`.`id` = `bugs`.`version`


Help will very much be appreciated, as SQL like this really isn't what 
I'm good at :)


- Geoffrey Sneddon




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




--No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.3/358 - Release Date: 6/7/2006





--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.3/358 - Release Date: 6/7/2006


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

Re: MYSQL->on cascade no action

[Jo�o C�ndido de Souza Neto]

I´ve never seen on cascade no action, you want to say on delete cascade or 
something like this?

"Nenad Bosanac" <[EMAIL PROTECTED]> escreveu na mensagem 
news:[EMAIL PROTECTED]
> Hi all
> I have problem in MYSQL query tool.
> Well i put on foreign key on cascade no action
> but next time when i edit that table
> it said that is on cascade is restrict.
> Why is that ?
> I use  MYSQL Query tool and my database is
> INNODB.
> Is there some kind of way to make it work
> or it is some kind of default value for
> innodb type of database?
> Thanks
>
> __
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com 



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

Re: Transfer users and permissions between servers

[Keith Roberts]

Are you moving to a new unpopulated server, or are there 
already mysql users on the new server?

If it is a new server with no other mysql users onboard, 
then I guess it is as easy as copying the mysql system 
database (/var/lib/mysql/mysql on my system) across to the 
new server, with the other databases you are moving.

If both servers are connected by LAN, you could set up FTP 
to transfer everything you need from the old server to the 
new one. I find Kbear is a very easy to use GUI front end to 
FTP.

HTH

Keith

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

On Wed, 7 Jun 2006 [EMAIL PROTECTED] wrote:

> To: mysql@lists.mysql.com
> From: [EMAIL PROTECTED]
> Subject: Transfer users and permissions between servers
> 
> Is there an easy way to transfer users and their 
> permissions from one server to another?  We are moving 
> databases to a new server and have a lot of users to move 
> as well.
> 
> Thanks in advance!
> 
> Donna 

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

MYSQL->on cascade no action

[Nenad Bosanac]

Hi all
I have problem in MYSQL query tool.
Well i put on foreign key on cascade no action
but next time when i edit that table
it said that is on cascade is restrict.
Why is that ?
I use  MYSQL Query tool and my database is 
INNODB.
Is there some kind of way to make it work
or it is some kind of default value for 
innodb type of database?
Thanks

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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

Joins - Multiple rows from a single table

[Geoffrey Sneddon]

I've got the following query:

SELECT `bugs`.`id`, `bugs`.`assignee`, `users`.`username`,  
`bugs`.`submitter` FROM `fb_bugs` AS `bugs`, `fb_users` AS `users`  
WHERE `users`.`id` = `bugs`.`assignee`


My problem is that I also want the `users`.`username` for  
`bugs`.`submitter`. How can I do that?


Also, anyway to make the entire query better (the above is just the  
relevant part):


SELECT `bugs`.`id`, `bugs`.`title`, UNIX_TIMESTAMP 
(`bugs`.`submitted`), `bugs`.`fix`, `bugs`.`assignee`,  
`users`.`username`, `bugs`.`category`, `category`.`name`,  
`bugs`.`version`, `version`.`name`, `bugs`.`priority`,  
`bugs`.`haspatch`FROM `fb_bugs` AS `bugs`, `fb_users` AS `users`,  
`fb_category` AS `category`, `fb_versions` AS `version` WHERE  
`users`.`id` = `bugs`.`assignee` AND `category`.`id` =  
`bugs`.`category` AND `version`.`id` = `bugs`.`version`


Help will very much be appreciated, as SQL like this really isn't  
what I'm good at :)


- Geoffrey Sneddon




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

Transfer users and permissions between servers

[ddevaudreuil]

Is there an easy way to transfer users and their permissions from one 
server to another?  We are moving databases to a new server and have a lot 
of users to move as well. 

Thanks in advance!

Donna 

building index table, duplicate .MYD

[Gaspar Bakos]

Hi,

I wonder if anyone could explain the rational behind the following:
(MySQL-5.0.22)

We have a MyISam table with ~100Gb size.
We start creating an index.
The procedure starts making temporary files:

-rw-rw  1 mysql mysql11430 Jun  7 10:27 #sql-79d6_cc.frm
-rw-rw  1 mysql mysql 1024 Jun  7 10:27 #sql-79d6_cc.MYI
-rw-rw  1 mysql mysql  80824238080 Jun  7 11:35 #sql-79d6_cc.MYD
(this is while the indexing is still running)

I understand the first two, but i was surprised that the entire 100Gb
.MYD gets duplicated during the indexing. At the end, I believe the
#sql-79d6_cc.MYD table replaces the original.

Is indexing also performing an "optimize table" at the same time?
Naively i would have tought that when we index, only an extra index
file is created, and the original table data is not touched.

Gas

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

Self join group by issue...

[Dan]

I have a PHP driven site where I have a internal messaging type  
system for registered users.  Each time a message is sent I create a  
log entry in a table with the from and to user id and an id from the  
original message to maintain a message 'thread'.  Example (my  
timestamps do not display properly in this mysql client):


select * from mail_log

+--+--+-- 
+--+--+
|id|id_from   |id_to | 
id_thread |date_timestamp|
+--+--+-- 
+--+--+
|5 |8 |1  
|   21 | 0020-06-00 06:07:00  |
|6 |1 |8  
|   48 | 0020-06-00 06:07:00  |
|7 |8 |3  
|   27 | 0020-06-00 06:07:00  |
|8 |1 |3  
|   29 | 0020-06-00 06:07:00  |
|9 |1 |2  
|   31 | 0020-06-00 06:07:00  |
|   10 |1 |8  
|   50 | 0020-06-00 06:07:00  |
|   11 |8 |1  
|   33 | 0020-06-00 06:07:00  |
|   12 |8 |6  
|   54 | 0020-06-00 06:07:00  |
|   13 |1 |8  
|   33 | 0020-06-00 06:07:00  |
+--+--+-- 
+--+--+


On this table I created a UNIQUE index on id_to and id_thread and my  
idea was to only track the initial message and first thread reply to  
create a crude index of a users % to reply to messages.  So you see  
that id 11 and 13 are the start of an exchange of messages between  
users.  So user 1 has been sent 2 messages (id 5 and 11) and only  
replied to 1 of them (50% reply rate) all other users are at 0%.


Now as I am just typing this I am thinking that I am going about this  
the wrong way.  Perhaps I should just track the id_thread and the  
id_to and then another 1 or 0 column (reply) to indicate if the  
initial message was replied to.  That would be an easy GROUP BY with  
a ratio of a COUNT(id) and a SUM(reply).


The other option would be to log everything... each and every message  
and do a simple total received / sent.


Also I envision wanting to have this data included in a query to my  
users table - something like:


SELECT id
FROM members
LEFT JOIN mail_log ON...

where I end up with something like this:

+--+--+
|id| rate |
+--+--+
|1 |  100 |
|2 |   98 |
|3 |   56 |
|4 |   87 |
|6 |   93 |
|7 |   92 |
|8 |   88 |
|9 |   10 |
+--+--+

I have been able to pretty much figure out any other complex query on  
my own or found help from the manual but this one has me stumped.   
Any help, direction or suggestions would be great.


Thanks,

Dan T

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

Re: MySQL

[Jørn Dahl-Stamnes]

On Wednesday 07 June 2006 17:04, Kaushal Shriyan wrote:
> Hi ALL
>
> [EMAIL PROTECTED] mysql]# mysql -u root -h saabworld.com -p
> Enter password:
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 20 to server version: 3.23.54
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> use mysql;
> Can't read dir of './mysql/' (Errcode: 13)
> Database changed
> mysql> show tables;
> ERROR 12: Can't read dir of './mysql/' (Errcode: 13)
> mysql>

A quick check with the perror command:

# perror 13
Error code  13:  Permission denied

-- 
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

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

Re: MySQL

[Duncan Hill]

On Wednesday 07 June 2006 16:04, Kaushal Shriyan wrote:

> mysql> use mysql;
> Can't read dir of './mysql/' (Errcode: 13)
> Database changed
> mysql> show tables;
> ERROR 12: Can't read dir of './mysql/' (Errcode: 13)
> mysql>
>
> Kaushal

$ perror 13
OS error code  13:  Permission denied

Your file system permissions are wrong, or you're using the SELinux 
enhancements.

Scanned by mailCritical.

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

MySQL

[Kaushal Shriyan]

Hi ALL

[EMAIL PROTECTED] mysql]# mysql -u root -h saabworld.com -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20 to server version: 3.23.54

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

mysql> use mysql;
Can't read dir of './mysql/' (Errcode: 13)
Database changed
mysql> show tables;
ERROR 12: Can't read dir of './mysql/' (Errcode: 13)
mysql>

Kaushal

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

Re: Selecting Common Data

[John Nichel]

Dan Buettner wrote:

Here's one way:

create table tbl (
col1 char(1),
col2 int );

insert into tbl (col1, col2)
VALUES
("a",1),
("a",2),
("a",3),
("b",4),
("b",2),
("b",7),
("c",1),
("c",2);


select col2
from tbl
group by col2
having count(col2) >= 2
order by col2;

+--+
| col2 |
+--+
|1 |
|2 |
+--+
2 rows in set (0.00 sec)

Dan


Thanks Dan, that's what I'm looking for


John Nichel wrote:

Running MySQL 4.0.20 on a RHEL3 box.

Hi,

  I'm trying to find the data in a table which is common to two or 
more ids.  Let's say my table looks like this...


---
| col1 | col2 |
---
|  a   |  1   |
|  a   |  2   |
|  a   |  3   |
|  b   |  4   |
|  b   |  2   |
|  b   |  7   |
|  c   |  1   |
|  c   |  2   |
---

I'm trying to get the data in col2 that is common for 
col1basically, I want it to return '2' when I do something like:


select col2 from table where col1 = 'a' || col1 = 'b' || col1 = 'c'

(obviously that select doesn't work).

I know this probably sounds confusing, but I'm can't find a better way 
to word it...guess that's why my Google searches turned up nothing I 
could use.  It would be much appreciated if someone could point me to 
the documentation on something like this, or give me a nudge in the 
right direction.  TIA





--
John C. Nichel IV
Programmer/System Admin
Dot Com Holdings of Buffalo
716.856.9675
[EMAIL PROTECTED]

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

Re: Sad, I know...

[tomáz rezistänz]

OK, thanks. Will do my research.


On 6/7/06, J.R. Bullington <[EMAIL PROTECTED]> wrote:


You really should learn some of the command line items before delving into
the GUIs.

MySQL has the Administrator and Query Browser for MacOS X. If you need to
start with any GUI, these are probably the ones you should start with.
It's
a nice blend of command line and GUI tools.

J.R.

-Original Message-
From: tomáz rezistänz [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 07, 2006 10:15 AM
To: Daniel da Veiga
Cc: mysql@lists.mysql.com
Subject: Re: Sad, I know...

I understans, but within phpmyadmin I was given the option to add
passwords
to 4 different accounts, including root. That's where I locked myself out,
so that's where I tried getting back in..


On 6/7/06, Daniel da Veiga <[EMAIL PROTECTED]> wrote:
>
> On 6/7/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:
> > OK, thanks for the guidance. I will try once more to make sense of
> > the
> mySQL
> > documentation.
> >
> > I thought I was going in the right direction when I clicked on
> > "setup script" (below) and went to the security panel, but then I
> > wasn't sure
> what
> > to enter as far as blowfish question, socket, folder, etc, etc..
> >
>
> You started wrong by thinking PHPMyAdmin was necessary in the first
> place, or that it was a step to configure MySQL. The server can work
> alone, PHPMyAdmin is simply a frontend. You must learn how to use the
> console (mysql) and use SQL commands on it. After that, you can use
> any GUI for a faster access to commands you already know.
>
> >
> >
> --
> 
> >
> > Probably reason of this is that you did not create configuration file.
> You
> > might want to use setup
> > scriptto create one.
> > Error
> >
> > *MySQL said: *[image: Documentation]
> > 
> >  #1045 - Access denied for user 'root'@'localhost' (using password:
NO)
> >Open new phpMyAdmin
> > window<
> http://localhost/phpmyadmin/index.php?target=index.php&lang=en-iso-885
> 9-1&convcharset=iso-8859-1&token=19846bf63e5f9bcd5a2eaf9e658d377c
> >
> >
> >
> --
> 
> >
> >
> >
> >
> >
> >
> > On 6/7/06, J.R. Bullington <[EMAIL PROTECTED]> wrote:
> > >
> > > So, by using MacOS X, you are using a *nix based system.
> > >
> > > Everything posted to this list is usually based in code (as in
> WebApps) or
> > > by the MySQL command line. You need to learn how to use the
> > > command
> line
> > > (terminal.app) and SQL. There are lots of books out there,
> > > including
> on
> > > using and installing MySQL. Might be advantageous to buy one. Or
> > > you
> can
> > > read the free and wonderful manual provided by MySQL on their
website.
> > >
> > > So, the command written by Daniel is used on the command line for
> > > accessing the database(s).
> > >
> > > To dump all your databases and "uninstall" MySQL, just stop the
> service
> > > terminal> mysqladmin shutdown
> > >
> > > and then delete the installation directory. Then restart the
> installation.
> > >
> > > PLEASE PLEASE PLEASE --- Read the manual before reinstalling! Even
> > > if
> you
> > > only read the section on Installing MySQL
> > > (http://dev.mysql.com/doc/refman/5.0/en/installing.html) that will
> help
> > > immensely with your original problems.
> > >
> > > Hope this helps!
> > >
> > > J.R.
> > >
> > > -Original Message-
> > > From: tomáz rezistänz [mailto:[EMAIL PROTECTED]
> > > Sent: Wednesday, June 07, 2006 12:34 AM
> > > To: mysql@lists.mysql.com
> > > Subject: Re: Sad, I know...
> > >
> > > I wish I could uninstall mySQL and start over but I don't know how..
> > >
> > > On 6/7/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:
> > > >
> > > > !!
> > > >
> > > > I apologize..
> > > >
> > > >  I should have mentioned that I am using a Mac in OSX 10.4.5
> > > >
> > > > My main concern is should I begin logging into mSQL using the
> terminal
> > > > or is this done from a browser??... Yes, I am know nothing.. no
> > > commands..
> > > > nada..
> > > >
> > > >
> > > >
> > > >
> > > > On 6/6/06, Daniel da Veiga <[EMAIL PROTECTED]> wrote:
> > > > >
> > > > > On 6/6/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:
> > > > > 
> > > > > >
> > > > > > I have no idea how to log in to mySQL... can you help?
> > > > >
> > > > > /bin/mysql.exe for Windows /usr/bin/mysql (AFAIK)
> > > > > for
> *ix
> > > > > like YMMV
> > > > >
> > > > > Run it with "-u root -p" and you can provide your password.
> > > > > That's the problem with wizards and howtos, you gotta KNOW
> > > > > what you're installing and how to deal with errors.
> > > > >
> > > > > >
> > > > > > I used to be able to log straight into phpmyadmin, but now I
> > > > > > get
> > > this:
> > > > > > 

Re: Selecting Common Data

[Dan Buettner]

Here's one way:

create table tbl (
col1 char(1),
col2 int );

insert into tbl (col1, col2)
VALUES
("a",1),
("a",2),
("a",3),
("b",4),
("b",2),
("b",7),
("c",1),
("c",2);


select col2
from tbl
group by col2
having count(col2) >= 2
order by col2;

+--+
| col2 |
+--+
|1 |
|2 |
+--+
2 rows in set (0.00 sec)

Dan



John Nichel wrote:

Running MySQL 4.0.20 on a RHEL3 box.

Hi,

  I'm trying to find the data in a table which is common to two or more 
ids.  Let's say my table looks like this...


---
| col1 | col2 |
---
|  a   |  1   |
|  a   |  2   |
|  a   |  3   |
|  b   |  4   |
|  b   |  2   |
|  b   |  7   |
|  c   |  1   |
|  c   |  2   |
---

I'm trying to get the data in col2 that is common for col1basically, 
I want it to return '2' when I do something like:


select col2 from table where col1 = 'a' || col1 = 'b' || col1 = 'c'

(obviously that select doesn't work).

I know this probably sounds confusing, but I'm can't find a better way 
to word it...guess that's why my Google searches turned up nothing I 
could use.  It would be much appreciated if someone could point me to 
the documentation on something like this, or give me a nudge in the 
right direction.  TIA


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

Re: Selecting Common Data

[John Nichel]

Adrian Bruce wrote:

John

I think you are saying you want the "Mode"? if you are then the 
following might work:


SELECT col2 as val, count(col2) AS cc
FROM my_table
GROUP BY val
ORDER BY cc DESC
LIMIT 1;

in your case this will retrun the value '2'

Regards
Ade


Thanks for the reply Ade.  That's not quite what I'm trying to do 
though.  Allow me to expand on the table...


---
| col1 | col2 |
---
|  a   |  1   |
|  a   |  2   |
|  a   |  3   |
|  a   |  4   |
|  b   |  4   |
|  b   |  2   |
|  b   |  3   |
|  b   |  7   |
|  c   |  1   |
|  c   |  2   |
|  d   |  3   |
|  d   |  4   |
---

If I select col2 where col1 equals 'a', 'c' or 'd', I want it to return 
nothing, because there's no value in col2 that is common to 'a', 'c', 
and d.  If I select col2 where col1 equals 'a', 'b' or 'd', I want it to 
return 3 and 4 only, as that is common to 'a', 'b', and 'd'.


I could just query each col1 that I need, and compare the results in my 
app, but I hope it can be done in just one query.




John Nichel wrote:

Running MySQL 4.0.20 on a RHEL3 box.

Hi,

  I'm trying to find the data in a table which is common to two or 
more ids.  Let's say my table looks like this...


---
| col1 | col2 |
---
|  a   |  1   |
|  a   |  2   |
|  a   |  3   |
|  b   |  4   |
|  b   |  2   |
|  b   |  7   |
|  c   |  1   |
|  c   |  2   |
---

I'm trying to get the data in col2 that is common for 
col1basically, I want it to return '2' when I do something like:


select col2 from table where col1 = 'a' || col1 = 'b' || col1 = 'c'

(obviously that select doesn't work).

I know this probably sounds confusing, but I'm can't find a better way 
to word it...guess that's why my Google searches turned up nothing I 
could use.  It would be much appreciated if someone could point me to 
the documentation on something like this, or give me a nudge in the 
right direction.  TIA





--
John C. Nichel IV
Programmer/System Admin
Dot Com Holdings of Buffalo
716.856.9675
[EMAIL PROTECTED]

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

Re: Selecting Common Data

[Adrian Bruce]

John

I think you are saying you want the "Mode"? if you are then the 
following might work:


SELECT col2 as val, count(col2) AS cc
FROM my_table
GROUP BY val
ORDER BY cc DESC
LIMIT 1;

in your case this will retrun the value '2'

Regards
Ade

John Nichel wrote:

Running MySQL 4.0.20 on a RHEL3 box.

Hi,

  I'm trying to find the data in a table which is common to two or 
more ids.  Let's say my table looks like this...


---
| col1 | col2 |
---
|  a   |  1   |
|  a   |  2   |
|  a   |  3   |
|  b   |  4   |
|  b   |  2   |
|  b   |  7   |
|  c   |  1   |
|  c   |  2   |
---

I'm trying to get the data in col2 that is common for 
col1basically, I want it to return '2' when I do something like:


select col2 from table where col1 = 'a' || col1 = 'b' || col1 = 'c'

(obviously that select doesn't work).

I know this probably sounds confusing, but I'm can't find a better way 
to word it...guess that's why my Google searches turned up nothing I 
could use.  It would be much appreciated if someone could point me to 
the documentation on something like this, or give me a nudge in the 
right direction.  TIA


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

RE: Sad, I know...

[J.R. Bullington]

You really should learn some of the command line items before delving into
the GUIs.

MySQL has the Administrator and Query Browser for MacOS X. If you need to
start with any GUI, these are probably the ones you should start with. It's
a nice blend of command line and GUI tools.

J.R.

-Original Message-
From: tomáz rezistänz [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 07, 2006 10:15 AM
To: Daniel da Veiga
Cc: mysql@lists.mysql.com
Subject: Re: Sad, I know...

I understans, but within phpmyadmin I was given the option to add passwords
to 4 different accounts, including root. That's where I locked myself out,
so that's where I tried getting back in..


On 6/7/06, Daniel da Veiga <[EMAIL PROTECTED]> wrote:
>
> On 6/7/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:
> > OK, thanks for the guidance. I will try once more to make sense of 
> > the
> mySQL
> > documentation.
> >
> > I thought I was going in the right direction when I clicked on 
> > "setup script" (below) and went to the security panel, but then I 
> > wasn't sure
> what
> > to enter as far as blowfish question, socket, folder, etc, etc..
> >
>
> You started wrong by thinking PHPMyAdmin was necessary in the first 
> place, or that it was a step to configure MySQL. The server can work 
> alone, PHPMyAdmin is simply a frontend. You must learn how to use the 
> console (mysql) and use SQL commands on it. After that, you can use 
> any GUI for a faster access to commands you already know.
>
> >
> >
> --
> 
> >
> > Probably reason of this is that you did not create configuration file.
> You
> > might want to use setup
> > scriptto create one.
> > Error
> >
> > *MySQL said: *[image: Documentation] 
> > 
> >  #1045 - Access denied for user 'root'@'localhost' (using password: NO)
> >Open new phpMyAdmin
> > window<
> http://localhost/phpmyadmin/index.php?target=index.php&lang=en-iso-885
> 9-1&convcharset=iso-8859-1&token=19846bf63e5f9bcd5a2eaf9e658d377c
> >
> >
> >
> --
> 
> >
> >
> >
> >
> >
> >
> > On 6/7/06, J.R. Bullington <[EMAIL PROTECTED]> wrote:
> > >
> > > So, by using MacOS X, you are using a *nix based system.
> > >
> > > Everything posted to this list is usually based in code (as in
> WebApps) or
> > > by the MySQL command line. You need to learn how to use the 
> > > command
> line
> > > (terminal.app) and SQL. There are lots of books out there, 
> > > including
> on
> > > using and installing MySQL. Might be advantageous to buy one. Or 
> > > you
> can
> > > read the free and wonderful manual provided by MySQL on their website.
> > >
> > > So, the command written by Daniel is used on the command line for 
> > > accessing the database(s).
> > >
> > > To dump all your databases and "uninstall" MySQL, just stop the
> service
> > > terminal> mysqladmin shutdown
> > >
> > > and then delete the installation directory. Then restart the
> installation.
> > >
> > > PLEASE PLEASE PLEASE --- Read the manual before reinstalling! Even 
> > > if
> you
> > > only read the section on Installing MySQL
> > > (http://dev.mysql.com/doc/refman/5.0/en/installing.html) that will
> help
> > > immensely with your original problems.
> > >
> > > Hope this helps!
> > >
> > > J.R.
> > >
> > > -Original Message-
> > > From: tomáz rezistänz [mailto:[EMAIL PROTECTED]
> > > Sent: Wednesday, June 07, 2006 12:34 AM
> > > To: mysql@lists.mysql.com
> > > Subject: Re: Sad, I know...
> > >
> > > I wish I could uninstall mySQL and start over but I don't know how..
> > >
> > > On 6/7/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:
> > > >
> > > > !!
> > > >
> > > > I apologize..
> > > >
> > > >  I should have mentioned that I am using a Mac in OSX 10.4.5
> > > >
> > > > My main concern is should I begin logging into mSQL using the
> terminal
> > > > or is this done from a browser??... Yes, I am know nothing.. no
> > > commands..
> > > > nada..
> > > >
> > > >
> > > >
> > > >
> > > > On 6/6/06, Daniel da Veiga <[EMAIL PROTECTED]> wrote:
> > > > >
> > > > > On 6/6/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:
> > > > > 
> > > > > >
> > > > > > I have no idea how to log in to mySQL... can you help?
> > > > >
> > > > > /bin/mysql.exe for Windows /usr/bin/mysql (AFAIK) 
> > > > > for
> *ix
> > > > > like YMMV
> > > > >
> > > > > Run it with "-u root -p" and you can provide your password.
> > > > > That's the problem with wizards and howtos, you gotta KNOW 
> > > > > what you're installing and how to deal with errors.
> > > > >
> > > > > >
> > > > > > I used to be able to log straight into phpmyadmin, but now I 
> > > > > > get
> > > this:
> > > > > > Welcome to phpMyAdmin 2.8.1
> > > > > >
> > > > > #
> > > > > Probably 

re: ERROR 1064 (42000): You have an error in your SQL syntax;

[Chris Sansom]

At 15:19 +0100 7/6/06, Rob Desbois wrote:
With the CHANGE clause of ALTER TABLE statement, you must provide 
the column definition, so something like this is what you need:

   ALTER TABLE actors CHANGE director_id actor_id MEDIUMINT UNSIGNED NOT NULL;
or whatever your original definition is.


Wow! I was right. I'm learning... :-)

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

If at first you don't succeed, try, try again.
Then quit. No use being a damn fool about it.
   -- W.C. Fields

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

RE: ERROR 1064 (42000): You have an error in your SQL syntax;

[J.R. Bullington]

You can't just change the name without changing (or stating) the type.

ALTER TABLE actors CHANGE director_id actos_id varchar(96) default NULL;

J.R.

-Original Message-
From: Mark Sargent [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 07, 2006 10:10 AM
To: mysql@lists.mysql.com
Subject: ERROR 1064 (42000): You have an error in your SQL syntax; 

Hi All,

gee I really hate bugging you all for this. I looked at this page,

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

which has this,

To change column |a| from |INTEGER| to |TINYINT NOT NULL| (leaving the name
the same), and to change column |b| from |CHAR(10)| to |CHAR(20)| as well as
renaming it from |b| to |c|:

ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);


for changing the name of a column, right? So, why doesn't the below work?

mysql> ALTER TABLE actors CHANGE director_id actor_id;

I get this,

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near '' at line 1

Sorry, little confused right about now, eh. Cheers.

Mark Sargent.


--
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: ERROR 1064 (42000): You have an error in your SQL syntax;

[Rob Desbois]

Mark,
With the CHANGE clause of ALTER TABLE statement, you must provide the column 
definition, so something like this is what you need:
   ALTER TABLE actors CHANGE director_id actor_id MEDIUMINT UNSIGNED NOT NULL;
or whatever your original definition is.

AFAIK there is no way to rename a column without giving the column type.
--Rob

> Hi All,

gee I really hate bugging you all for this. I looked at this page,

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

which has this,

To change column |a| from |INTEGER| to |TINYINT NOT NULL| (leaving the 
name the same), and to change column |b| from |CHAR(10)| to |CHAR(20)| 
as well as renaming it from |b| to |c|:

ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);


for changing the name of a column, right? So, why doesn't the below work?

mysql> ALTER TABLE actors CHANGE director_id actor_id;

I get this,

ERROR 1064 (42000): You have an error in your SQL syntax; check the 
manual that corresponds to your MySQL server version for the right 
syntax to use near '' at line 1

Sorry, little confused right about now, eh. Cheers.

Mark Sargent.


-- 

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


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__


<

-- Original Message --

FROM:  Mark Sargent <[EMAIL PROTECTED]>
TO:mysql@lists.mysql.com
DATE:  Thu, 08 Jun 2006 00:09:45 +1000

SUBJECT:   ERROR 1064 (42000): You have an error in your SQL syntax;

Hi All,

gee I really hate bugging you all for this. I looked at this page,

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

which has this,

To change column |a| from |INTEGER| to |TINYINT NOT NULL| (leaving the 
name the same), and to change column |b| from |CHAR(10)| to |CHAR(20)| 
as well as renaming it from |b| to |c|:

ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);


for changing the name of a column, right? So, why doesn't the below work?

mysql> ALTER TABLE actors CHANGE director_id actor_id;

I get this,

ERROR 1064 (42000): You have an error in your SQL syntax; check the 
manual that corresponds to your MySQL server version for the right 
syntax to use near '' at line 1

Sorry, little confused right about now, eh. Cheers.

Mark Sargent.


-- 

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


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__

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

Re: ERROR 1064 (42000): You have an error in your SQL syntax;

[Chris Sansom]

At 0:09 +1000 8/6/06, Mark Sargent wrote:

ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

for changing the name of a column, right? So, why doesn't the below work?

mysql> ALTER TABLE actors CHANGE director_id actor_id;


I'm no great expert myself, but off the top of my head, maybe you 
need to specify the type even if it's unchanged (I assume all you 
want to do is rename the column?). So supposing director_id was a 
SMALLINT(3) UNSIGNED, try:


ALTER TABLE actors CHANGE director_id actor_id SMALLINT(3) UNSIGNED;

Any good?

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

If at first you don't succeed, try, try again.
Then quit. No use being a damn fool about it.
   -- W.C. Fields

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

Re: Sad, I know...

[tomáz rezistänz]

I understans, but within phpmyadmin I was given the option to add passwords
to 4 different accounts, including root. That's where I locked myself out,
so that's where I tried getting back in..


On 6/7/06, Daniel da Veiga <[EMAIL PROTECTED]> wrote:


On 6/7/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:
> OK, thanks for the guidance. I will try once more to make sense of the
mySQL
> documentation.
>
> I thought I was going in the right direction when I clicked on "setup
> script" (below) and went to the security panel, but then I wasn't sure
what
> to enter as far as blowfish question, socket, folder, etc, etc..
>

You started wrong by thinking PHPMyAdmin was necessary in the first
place, or that it was a step to configure MySQL. The server can work
alone, PHPMyAdmin is simply a frontend. You must learn how to use the
console (mysql) and use SQL commands on it. After that, you can use
any GUI for a faster access to commands you already know.

>
>
--
>
> Probably reason of this is that you did not create configuration file.
You
> might want to use setup
> scriptto create one.
> Error
>
> *MySQL said: *[image: Documentation]
> 
>  #1045 - Access denied for user 'root'@'localhost' (using password: NO)
>Open new phpMyAdmin
> window<
http://localhost/phpmyadmin/index.php?target=index.php&lang=en-iso-8859-1&convcharset=iso-8859-1&token=19846bf63e5f9bcd5a2eaf9e658d377c
>
>
>
--
>
>
>
>
>
>
> On 6/7/06, J.R. Bullington <[EMAIL PROTECTED]> wrote:
> >
> > So, by using MacOS X, you are using a *nix based system.
> >
> > Everything posted to this list is usually based in code (as in
WebApps) or
> > by the MySQL command line. You need to learn how to use the command
line
> > (terminal.app) and SQL. There are lots of books out there, including
on
> > using and installing MySQL. Might be advantageous to buy one. Or you
can
> > read the free and wonderful manual provided by MySQL on their website.
> >
> > So, the command written by Daniel is used on the command line for
> > accessing
> > the database(s).
> >
> > To dump all your databases and "uninstall" MySQL, just stop the
service
> > terminal> mysqladmin shutdown
> >
> > and then delete the installation directory. Then restart the
installation.
> >
> > PLEASE PLEASE PLEASE --- Read the manual before reinstalling! Even if
you
> > only read the section on Installing MySQL
> > (http://dev.mysql.com/doc/refman/5.0/en/installing.html) that will
help
> > immensely with your original problems.
> >
> > Hope this helps!
> >
> > J.R.
> >
> > -Original Message-
> > From: tomáz rezistänz [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, June 07, 2006 12:34 AM
> > To: mysql@lists.mysql.com
> > Subject: Re: Sad, I know...
> >
> > I wish I could uninstall mySQL and start over but I don't know how..
> >
> > On 6/7/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:
> > >
> > > !!
> > >
> > > I apologize..
> > >
> > >  I should have mentioned that I am using a Mac in OSX 10.4.5
> > >
> > > My main concern is should I begin logging into mSQL using the
terminal
> > > or is this done from a browser??... Yes, I am know nothing.. no
> > commands..
> > > nada..
> > >
> > >
> > >
> > >
> > > On 6/6/06, Daniel da Veiga <[EMAIL PROTECTED]> wrote:
> > > >
> > > > On 6/6/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:
> > > > 
> > > > >
> > > > > I have no idea how to log in to mySQL... can you help?
> > > >
> > > > /bin/mysql.exe for Windows /usr/bin/mysql (AFAIK) for
*ix
> > > > like YMMV
> > > >
> > > > Run it with "-u root -p" and you can provide your password.
> > > > That's the problem with wizards and howtos, you gotta KNOW what
> > > > you're installing and how to deal with errors.
> > > >
> > > > >
> > > > > I used to be able to log straight into phpmyadmin, but now I get
> > this:
> > > > > Welcome to phpMyAdmin 2.8.1
> > > > >
> > > > #
> > > > Probably reason of this is that you did not create configuration
file.
> > > > You might want to use setup script
> > > >  to create one.
> > > >
> > > > #1045 - Access denied for user 'root'@'localhost' (using password:
> > > > NO) #
> > > >
> > > > It can't be more verbose than that...
> > > >
> > > > So, you do have MySQL installed, you've setup a password for it,
and
> > > > for some reason (probably a lack of a config file, as PHPMyAdmin
> > > > told
> > > > you) PHPMyAdmin tries to connect to mysql without a password, and
> > > > its kicked in the butt by trying. You could try running the script
> > > > it told you (setup.php) to let PHPMyAdmin know that you must
provide
> > > > a password to MySQL. You can also run the console application
(m

RE: MySQL Server

[Jimmy Guerrero]

Hello,

Is there a particular reason you need to be working with version 3.23
(besides it being part of the RHL distro?)

You might want to start with version 5.0 or 4.1.
http://dev.mysql.com/downloads/

Thanks,

Jimmy Guerrero
Sr Product Manager
MySQL, Inc

 

> -Original Message-
> From: Kaushal Shriyan [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, June 07, 2006 9:05 AM
> To: mysql@lists.mysql.com
> Subject: MySQL Server
> 
> Hi ALL
> 
> I have RHL 9.0 installed on my box, and I have installed 
> mysql-3.23.54a-11.i386.rpm  mysql-server-3.23.54a-11.i386.rpm,
> due to some reason i deleted the folders from /var/lib/mysql, 
> I mean mysql and test folder located under /var/lib/mysql
> 
> I have re-installed the package mysql and mysql-server 
> package but i am not able to see the test and mysql database 
> which comes by default while installing mysql and 
> mysql-server package.
> 
> Any help will be really appreciated
> 
> Thanks
> 
> Kaushal
> 
> --
> 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]

ERROR 1064 (42000): You have an error in your SQL syntax;

[Mark Sargent]

Hi All,

gee I really hate bugging you all for this. I looked at this page,

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

which has this,

To change column |a| from |INTEGER| to |TINYINT NOT NULL| (leaving the 
name the same), and to change column |b| from |CHAR(10)| to |CHAR(20)| 
as well as renaming it from |b| to |c|:


ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);


for changing the name of a column, right? So, why doesn't the below work?

mysql> ALTER TABLE actors CHANGE director_id actor_id;

I get this,

ERROR 1064 (42000): You have an error in your SQL syntax; check the 
manual that corresponds to your MySQL server version for the right 
syntax to use near '' at line 1


Sorry, little confused right about now, eh. Cheers.

Mark Sargent.


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

MySQL Server

[Kaushal Shriyan]

Hi ALL

I have RHL 9.0 installed on my box, and I have installed
mysql-3.23.54a-11.i386.rpm  mysql-server-3.23.54a-11.i386.rpm,
due to some reason i deleted the folders from /var/lib/mysql, I mean
mysql and test folder located under /var/lib/mysql

I have re-installed the package mysql and mysql-server package but i
am not able to see the test and mysql database which comes by default
while installing
mysql and mysql-server package.

Any help will be really appreciated

Thanks

Kaushal

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

Selecting Common Data

[John Nichel]

Running MySQL 4.0.20 on a RHEL3 box.

Hi,

  I'm trying to find the data in a table which is common to two or more 
ids.  Let's say my table looks like this...


---
| col1 | col2 |
---
|  a   |  1   |
|  a   |  2   |
|  a   |  3   |
|  b   |  4   |
|  b   |  2   |
|  b   |  7   |
|  c   |  1   |
|  c   |  2   |
---

I'm trying to get the data in col2 that is common for col1basically, 
I want it to return '2' when I do something like:


select col2 from table where col1 = 'a' || col1 = 'b' || col1 = 'c'

(obviously that select doesn't work).

I know this probably sounds confusing, but I'm can't find a better way 
to word it...guess that's why my Google searches turned up nothing I 
could use.  It would be much appreciated if someone could point me to 
the documentation on something like this, or give me a nudge in the 
right direction.  TIA

--
John C. Nichel IV
Programmer/System Admin
Dot Com Holdings of Buffalo
716.856.9675
[EMAIL PROTECTED]

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

Re: Sad, I know...

[Daniel da Veiga]

On 6/7/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:

OK, thanks for the guidance. I will try once more to make sense of the mySQL
documentation.

I thought I was going in the right direction when I clicked on "setup
script" (below) and went to the security panel, but then I wasn't sure what
to enter as far as blowfish question, socket, folder, etc, etc..



You started wrong by thinking PHPMyAdmin was necessary in the first
place, or that it was a step to configure MySQL. The server can work
alone, PHPMyAdmin is simply a frontend. You must learn how to use the
console (mysql) and use SQL commands on it. After that, you can use
any GUI for a faster access to commands you already know.



--

Probably reason of this is that you did not create configuration file. You
might want to use setup
scriptto create one.
Error

*MySQL said: *[image: Documentation]

 #1045 - Access denied for user 'root'@'localhost' (using password: NO)
   Open new phpMyAdmin
window

--






On 6/7/06, J.R. Bullington <[EMAIL PROTECTED]> wrote:
>
> So, by using MacOS X, you are using a *nix based system.
>
> Everything posted to this list is usually based in code (as in WebApps) or
> by the MySQL command line. You need to learn how to use the command line
> (terminal.app) and SQL. There are lots of books out there, including on
> using and installing MySQL. Might be advantageous to buy one. Or you can
> read the free and wonderful manual provided by MySQL on their website.
>
> So, the command written by Daniel is used on the command line for
> accessing
> the database(s).
>
> To dump all your databases and "uninstall" MySQL, just stop the service
> terminal> mysqladmin shutdown
>
> and then delete the installation directory. Then restart the installation.
>
> PLEASE PLEASE PLEASE --- Read the manual before reinstalling! Even if you
> only read the section on Installing MySQL
> (http://dev.mysql.com/doc/refman/5.0/en/installing.html) that will help
> immensely with your original problems.
>
> Hope this helps!
>
> J.R.
>
> -Original Message-
> From: tomáz rezistänz [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, June 07, 2006 12:34 AM
> To: mysql@lists.mysql.com
> Subject: Re: Sad, I know...
>
> I wish I could uninstall mySQL and start over but I don't know how..
>
> On 6/7/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:
> >
> > !!
> >
> > I apologize..
> >
> >  I should have mentioned that I am using a Mac in OSX 10.4.5
> >
> > My main concern is should I begin logging into mSQL using the terminal
> > or is this done from a browser??... Yes, I am know nothing.. no
> commands..
> > nada..
> >
> >
> >
> >
> > On 6/6/06, Daniel da Veiga <[EMAIL PROTECTED]> wrote:
> > >
> > > On 6/6/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:
> > > 
> > > >
> > > > I have no idea how to log in to mySQL... can you help?
> > >
> > > /bin/mysql.exe for Windows /usr/bin/mysql (AFAIK) for *ix
> > > like YMMV
> > >
> > > Run it with "-u root -p" and you can provide your password.
> > > That's the problem with wizards and howtos, you gotta KNOW what
> > > you're installing and how to deal with errors.
> > >
> > > >
> > > > I used to be able to log straight into phpmyadmin, but now I get
> this:
> > > > Welcome to phpMyAdmin 2.8.1
> > > >
> > > #
> > > Probably reason of this is that you did not create configuration file.
> > > You might want to use setup script
> > >  to create one.
> > >
> > > #1045 - Access denied for user 'root'@'localhost' (using password:
> > > NO) #
> > >
> > > It can't be more verbose than that...
> > >
> > > So, you do have MySQL installed, you've setup a password for it, and
> > > for some reason (probably a lack of a config file, as PHPMyAdmin
> > > told
> > > you) PHPMyAdmin tries to connect to mysql without a password, and
> > > its kicked in the butt by trying. You could try running the script
> > > it told you (setup.php) to let PHPMyAdmin know that you must provide
> > > a password to MySQL. You can also run the console application (mysql
> > > or mysql.exe ) using "mysql -u root -p", provide your password and
> > > login, but that will not solve PHPMyAdmin, you MUST configure it.
> > > You can also login and reset your password, so PHPMyAdmin will log
> > > you in without a password and you can change your passwd from the
> > > Web Interface. You can also take a look at the MySQL Manual for a
> > > way to reset your password in case you can't login using console.
> > >
> > > --
> > > Daniel da 

Re: Sad, I know...

[tomáz rezistänz]

OK, thanks for the guidance. I will try once more to make sense of the mySQL
documentation.

I thought I was going in the right direction when I clicked on "setup
script" (below) and went to the security panel, but then I wasn't sure what
to enter as far as blowfish question, socket, folder, etc, etc..


--

Probably reason of this is that you did not create configuration file. You
might want to use setup
scriptto create one.
Error

*MySQL said: *[image: Documentation]

#1045 - Access denied for user 'root'@'localhost' (using password: NO)
  Open new phpMyAdmin
window

--






On 6/7/06, J.R. Bullington <[EMAIL PROTECTED]> wrote:


So, by using MacOS X, you are using a *nix based system.

Everything posted to this list is usually based in code (as in WebApps) or
by the MySQL command line. You need to learn how to use the command line
(terminal.app) and SQL. There are lots of books out there, including on
using and installing MySQL. Might be advantageous to buy one. Or you can
read the free and wonderful manual provided by MySQL on their website.

So, the command written by Daniel is used on the command line for
accessing
the database(s).

To dump all your databases and "uninstall" MySQL, just stop the service
terminal> mysqladmin shutdown

and then delete the installation directory. Then restart the installation.

PLEASE PLEASE PLEASE --- Read the manual before reinstalling! Even if you
only read the section on Installing MySQL
(http://dev.mysql.com/doc/refman/5.0/en/installing.html) that will help
immensely with your original problems.

Hope this helps!

J.R.

-Original Message-
From: tomáz rezistänz [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 07, 2006 12:34 AM
To: mysql@lists.mysql.com
Subject: Re: Sad, I know...

I wish I could uninstall mySQL and start over but I don't know how..

On 6/7/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:
>
> !!
>
> I apologize..
>
>  I should have mentioned that I am using a Mac in OSX 10.4.5
>
> My main concern is should I begin logging into mSQL using the terminal
> or is this done from a browser??... Yes, I am know nothing.. no
commands..
> nada..
>
>
>
>
> On 6/6/06, Daniel da Veiga <[EMAIL PROTECTED]> wrote:
> >
> > On 6/6/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:
> > 
> > >
> > > I have no idea how to log in to mySQL... can you help?
> >
> > /bin/mysql.exe for Windows /usr/bin/mysql (AFAIK) for *ix
> > like YMMV
> >
> > Run it with "-u root -p" and you can provide your password.
> > That's the problem with wizards and howtos, you gotta KNOW what
> > you're installing and how to deal with errors.
> >
> > >
> > > I used to be able to log straight into phpmyadmin, but now I get
this:
> > > Welcome to phpMyAdmin 2.8.1
> > >
> > #
> > Probably reason of this is that you did not create configuration file.
> > You might want to use setup script
> >  to create one.
> >
> > #1045 - Access denied for user 'root'@'localhost' (using password:
> > NO) #
> >
> > It can't be more verbose than that...
> >
> > So, you do have MySQL installed, you've setup a password for it, and
> > for some reason (probably a lack of a config file, as PHPMyAdmin
> > told
> > you) PHPMyAdmin tries to connect to mysql without a password, and
> > its kicked in the butt by trying. You could try running the script
> > it told you (setup.php) to let PHPMyAdmin know that you must provide
> > a password to MySQL. You can also run the console application (mysql
> > or mysql.exe ) using "mysql -u root -p", provide your password and
> > login, but that will not solve PHPMyAdmin, you MUST configure it.
> > You can also login and reset your password, so PHPMyAdmin will log
> > you in without a password and you can change your passwd from the
> > Web Interface. You can also take a look at the MySQL Manual for a
> > way to reset your password in case you can't login using console.
> >
> > --
> > Daniel da Veiga
> > Computer Operator - RS - Brazil
> > -BEGIN GEEK CODE BLOCK-
> > Version: 3.1
> > GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M-
> > V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
> > --END GEEK CODE BLOCK--
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> >
>

Re: Query performance.

[Eugene Kosov]

Thanks a lot!! :D

You were right. There was a bug. Upgrading to mysql 4.1.20 solved my 
problem.



Daniel da Veiga wrote:

Check http://bugs.mysql.com/bug.php?id=12915



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

RE: Sad, I know...

[J.R. Bullington]

So, by using MacOS X, you are using a *nix based system.

Everything posted to this list is usually based in code (as in WebApps) or
by the MySQL command line. You need to learn how to use the command line
(terminal.app) and SQL. There are lots of books out there, including on
using and installing MySQL. Might be advantageous to buy one. Or you can
read the free and wonderful manual provided by MySQL on their website.

So, the command written by Daniel is used on the command line for accessing
the database(s).

To dump all your databases and "uninstall" MySQL, just stop the service 
terminal> mysqladmin shutdown

and then delete the installation directory. Then restart the installation.

PLEASE PLEASE PLEASE --- Read the manual before reinstalling! Even if you
only read the section on Installing MySQL
(http://dev.mysql.com/doc/refman/5.0/en/installing.html) that will help
immensely with your original problems.

Hope this helps!

J.R.

-Original Message-
From: tomáz rezistänz [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 07, 2006 12:34 AM
To: mysql@lists.mysql.com
Subject: Re: Sad, I know...

I wish I could uninstall mySQL and start over but I don't know how..

On 6/7/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:
>
> !!
>
> I apologize..
>
>  I should have mentioned that I am using a Mac in OSX 10.4.5
>
> My main concern is should I begin logging into mSQL using the terminal 
> or is this done from a browser??... Yes, I am know nothing.. no commands..
> nada..
>
>
>
>
> On 6/6/06, Daniel da Veiga <[EMAIL PROTECTED]> wrote:
> >
> > On 6/6/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:
> > 
> > >
> > > I have no idea how to log in to mySQL... can you help?
> >
> > /bin/mysql.exe for Windows /usr/bin/mysql (AFAIK) for *ix 
> > like YMMV
> >
> > Run it with "-u root -p" and you can provide your password.
> > That's the problem with wizards and howtos, you gotta KNOW what 
> > you're installing and how to deal with errors.
> >
> > >
> > > I used to be able to log straight into phpmyadmin, but now I get this:
> > > Welcome to phpMyAdmin 2.8.1
> > >
> > #
> > Probably reason of this is that you did not create configuration file.
> > You might want to use setup script
> >  to create one.
> >
> > #1045 - Access denied for user 'root'@'localhost' (using password: 
> > NO) #
> >
> > It can't be more verbose than that...
> >
> > So, you do have MySQL installed, you've setup a password for it, and 
> > for some reason (probably a lack of a config file, as PHPMyAdmin 
> > told
> > you) PHPMyAdmin tries to connect to mysql without a password, and 
> > its kicked in the butt by trying. You could try running the script 
> > it told you (setup.php) to let PHPMyAdmin know that you must provide 
> > a password to MySQL. You can also run the console application (mysql 
> > or mysql.exe ) using "mysql -u root -p", provide your password and 
> > login, but that will not solve PHPMyAdmin, you MUST configure it. 
> > You can also login and reset your password, so PHPMyAdmin will log 
> > you in without a password and you can change your passwd from the 
> > Web Interface. You can also take a look at the MySQL Manual for a 
> > way to reset your password in case you can't login using console.
> >
> > --
> > Daniel da Veiga
> > Computer Operator - RS - Brazil
> > -BEGIN GEEK CODE BLOCK-
> > Version: 3.1
> > GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- 
> > V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ 
> > --END GEEK CODE BLOCK--
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> >
>


smime.p7s
Description: S/MIME cryptographic signature

Re: DateTime limits

[Ben Clewett]

Thanks for the information.

I agree with what you say.

There is just one comment I'd like to make.

You are right that the TIMESTAMP has a specific range.  I am comparing 
it to a date outside that range.  This could cause problems.


But I strongly believe that the SQL user, who in many cases is not a 
MySQL guru, should be abstracted from the mechanisms used within MySQL.


The SQL:

... WHERE [some_date_time_field] > '0001-01-01 00:00:00'

Is legal ANSI-SQL.  Therefore the user should not be told off because 
they haven't read that manual page.  (Which is good reading, thanks for 
the URI.)


But I am indebted to MySQL for kindly resolving this to work with the 
DATETIME and return valid data.


Possibly two mechanisms would be a 'perfect' solution.  The Warnings for 
the SQL user to show them where their syntax is bad.  Then a DBA log to 
show where the internal mechanisms of MySQL are being violated


But I love MySQL so people don't jump on me for suggesting changes!


You other point from my email:

>> ... WHERE '2006-06-06' > '2006-06-06 12:00:00'
>>
>> This will give a valid warning, this is bad SQL, time for a re-code!


I cannot replicate this.  I did have a query where a time & date field 
was compared to a date field which produced a warning, version 5.1.6. 
(I know about warnings, they crash my programs!)  So I'll have to leave 
this point.   I'll try and find it again if anybody is interested...


Thanks for the info,

Regards,

Ben







Michael Stassen wrote:

Ben Clewett wrote:

 > C# has two DateTime constants:
 >
 > DateTime.MinValue = '0001-01-01 00:00:00.000'
 > DateTime.MaxValue = '-12-31 23:59:59.999'
 >

 >
 > MySQL really doesn't like these values, it shows warnings:
 >
 > +-+--+-+
 > | Level   | Code | Message |
 > +-+--+-+
 > | Warning | 1292 | Incorrect datetime value: '0001-01-01 00:00:00' |
 > +-+--+-+
 >
 > The real problem with these warning is:
 >
 > 1. This date is legal, if a little unlikely.
 > 2. Any warning crash MySql.Data.dll!!!


Ben Clewett wrote:
 > Hi Barry,
 >
 > This will happen when comparing against a TIMESTAMP field.
 >
 > CREATE TABLE a ( t TIMESTAMP );
 >
 > SELECT * FROM a WHERE t > '0001-01-01 00:00:00';


Ben Clewett wrote:

Hi Barry,

 > Well removing 'explicit' warnings for every user having problems with
 > 3rd party modules would have mysql without any warnings nowadays ;)
 >
 > i think that your mono should get more stable.

I completely take this on board.  This is a bug outside MySQL.

Warnings are very useful.  When we test code and get a warning, this 
almost always results in us re-coding.  Eg:


... WHERE '2006-06-06' > '2006-06-06 12:00:00'

This will give a valid warning, this is bad SQL, time for a re-code!


This will not give a warning, as it is perfectly valid sql.  (It will 
simply return no rows, as the WHERE clause condition cannot be 
satisfied.)  Why do you think otherwise?



BUT my warning is a special case:
  - It's not bad SQL.


Yes, it is bad sql.  Your WHERE clause is

  WHERE t > '0001-01-01 00:00:00'

As t is a TIMESTAMP column, mysql must convert the DATETIME constant to 
a TIMESTAMP, but '0001-01-01 00:00:00' is not a valid TIMESTAMP.  Hence 
the warning.



  - Because of the C# DateTime object, this happens often.

Therefore a candidate for dropping.  Which will also avoid bugs in 
badly written MySQL clients :)


But, I've probably said enough on the point :)

Ben


According to the manual 
, 
the officially supported range for DATETIME columns is '1000-01-01 
00:00:00' to '-12-31 23:59:59', though '0001-01-01 00:00:00' seems 
to work in practice.


 From the same manual page, the officially supported range for TIMESTAMP 
columns is '1970-01-01 00:00:00' to partway through the year 2037.  In 
practice, TIMESTAMP columns take timezone into account.  Hence, as my 
offset from GMT is -5, the earliest TIMESTAMP I can get away with is 
'1969-12-31 19:00:01'.  That is,


  SELECT * FROM a WHERE t > '1969-12-31 19:00:00';

produes a warning, but

  SELECT * FROM a WHERE t > '1969-12-31 19:00:01';

does not.

I believe your problem begins with your assumption that DATETIME and 
TIMESTAMP are interchangeable.  They are not.  You would probably be 
fine using C#'s


  DateTime.MinValue = '0001-01-01 00:00:00.000'

for actual DATETIME columns, but it just isn't valid for TIMESTAMP 
columns. That said, mysql manages to do the right thing when faced with 
this query.  As '0001-01-01 00:00:00.000' is earlier than the first 
valid TIMESTAMP, it is converted to a TIMESTAMP of 0, one second before 
the earliest valid TIMESTAMP, and the query proceeds, giving the results 
you expected, I believe.  It also issues a warning t

Re: Sad, I know...

[John Meyer]

tomáz rezistänz wrote:

I wish I could uninstall mySQL and start over but I don't know how..

On 6/7/06, tomáz rezistänz <[EMAIL PROTECTED]> wrote:



Do you know how to dump your mySQL databases?

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