Re: DISTINCT not working inside a CASE statement.

[Arup Rakshit]

On Saturday, July 19, 2014 02:56:24 PM Reindl Harald wrote:

> > ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> > that corresponds to your MariaDB server version for the right syntax to
> > use
> > near 'DISTINCT sum) THEN sum(cost) ELSE NULL END AS cost_sum
> > from prices group by name' at line 1
> 
> how do you imagine that to work?
> what is the distinct in that context supposed to do?

Ohh. God. So simple it is -

MariaDB [tutorial]> select name, sum(cost) AS cost_sum from prices group by 
name;
+--+--+
| name | cost_sum |
+--+--+
| A| 6700 |
| B|12000 |
| C| NULL |
+--+--+
3 rows in set (0.01 sec)

Thanks.

-- 

Regards,
Arup Rakshit

Debugging is twice as hard as writing the code in the first place. Therefore, 
if you write the code as cleverly as possible, you are, by definition, not 
smart enough to debug it.

--Brian Kernighan

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

Re: DISTINCT not working inside a CASE statement.

[Reindl Harald]

Am 19.07.2014 13:45, schrieb Arup Rakshit:
> Here is my simple table
> 
> MariaDB [tutorial]> select * from prices;
> ++--+--+
> | id | name | cost |
> ++--+--+
> |  1 | A| 1200 |
> |  2 | A| 2500 |
> |  3 | A| 3000 |
> |  4 | B| 5000 |
> |  5 | B| 7000 |
> |  6 | C| NULL |
> ++--+--+
> 6 rows in set (0.00 sec)
> 
> I want it to give me data as
> 
> name   cost
> A 6700
> B 12000
> C NULL
> 
> But my query is not working - 
> 
> MariaDB [tutorial]> select name, CASE WHEN ISNULL(DISTINCT sum) THEN 
> sum(cost) 
> ELSE NULL END AS cost_sum
> -> from prices group by name;
> 
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
> that corresponds to your MariaDB server version for the right syntax to use 
> near 'DISTINCT sum) THEN sum(cost) ELSE NULL END AS cost_sum
> from prices group by name' at line 1

how do you imagine that to work?
what is the distinct in that context supposed to do?



signature.asc
Description: OpenPGP digital signature

DISTINCT not working inside a CASE statement.

[Arup Rakshit]

Here is my simple table

MariaDB [tutorial]> select * from prices;
++--+--+
| id | name | cost |
++--+--+
|  1 | A| 1200 |
|  2 | A| 2500 |
|  3 | A| 3000 |
|  4 | B| 5000 |
|  5 | B| 7000 |
|  6 | C| NULL |
++--+--+
6 rows in set (0.00 sec)

I want it to give me data as

name   cost
A 6700
B 12000
C NULL

But my query is not working - 

MariaDB [tutorial]> select name, CASE WHEN ISNULL(DISTINCT sum) THEN sum(cost) 
ELSE NULL END AS cost_sum
-> from prices group by name;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MariaDB server version for the right syntax to use 
near 'DISTINCT sum) THEN sum(cost) ELSE NULL END AS cost_sum
from prices group by name' at line 1

-- 

Regards,
Arup Rakshit

Debugging is twice as hard as writing the code in the first place. Therefore, 
if you write the code as cleverly as possible, you are, by definition, not 
smart enough to debug it.

--Brian Kernighan

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

Re: User-defined variables not working ONLY on first query in chained replication

[Jesper Wisborg Krogh]

Hi Matthew,

On 16/07/2013 21:21, Matthew Ward wrote:

I've noticed a weird issue in our chained replication environment where when 
setting user-defined variables, the first time the variable is used in a 
session the value is NULL, but all uses thereafter work correctly.



The first time I run this insert, the value is correctly inserted in to master1 
and its slave, master3 (as you'd expect). However, a NULL value is inserted 
into master3-slave1. However, if I run the INSERT a second time (just the 
insert, no re-declaration of the user-defined variable), the value is correctly 
inserted in to all three servers, so that the contents of test_table on the 
three servers looks as follows:



Is this a known issue in MySQL with chained replication like this, or have I 
discovered a bug?


Do you happen to have any table level replication filters? If so it 
sounds like you are affected by a bug that was fixed in 5.5.32 
(https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-32.html):


*Replication:*Running the server with both the|--log-slave-updates| 
and|--replicate-wild-ignore-table| 
options 
in some cases caused updates to user variables not to be logged.


(Note: at least replicate-ignore-table is also triggering that bug).

Best regards,
Jesper Krogh
MySQL Support

User-defined variables not working ONLY on first query in chained replication

[Matthew Ward]

I've noticed a weird issue in our chained replication environment where when 
setting user-defined variables, the first time the variable is used in a 
session the value is NULL, but all uses thereafter work correctly.

The environment is such: we have a master (master1), which has a slave which is 
also a master (master3), which itself has slaves (master3-slave1), i.e.:

master1 -> master3 -> master3-slave1

I can replicate my issue with a very simple setup. I simply create a test table 
with one TEXT column, and I set a user-defined variable:

CREATE TABLE test_table (id INT(10) PRIMARY KEY AUTO_INCREMENT, result TEXT) 
ENGINE=InnoDB;
SET @mynewvariable = "testvalue"
And then insert the variable into the test table:

INSERT INTO test_table VALUES (NULL, @mynewvariable);
The first time I run this insert, the value is correctly inserted in to master1 
and its slave, master3 (as you'd expect). However, a NULL value is inserted 
into master3-slave1. However, if I run the INSERT a second time (just the 
insert, no re-declaration of the user-defined variable), the value is correctly 
inserted in to all three servers, so that the contents of test_table on the 
three servers looks as follows:

master1   master3   master3-slave1
- - --
testvalue testvalue NULL
testvalue testvalue testvalue
I don't believe this is related to replication delay, because even if I leave a 
while between setting the variable and running the first INSERT, the result is 
always the same. The problem is agnostic of table format or how complex the 
table is, we can reproduce it exactly like this all of the time.

Is this a known issue in MySQL with chained replication like this, or have I 
discovered a bug?

Server version information:

master1: Percona Server 5.5.28-29.1
master3: Percona Server 5.5.28-29.3
master3-slave1: Percona Server 5.5.20-55

-- 


Global Personals is a limited company registered in England and Wales. 
Registered number: 04880697. 
Registered office: Minton Place, Victoria Street, Windsor, Berkshire, SL4 
1EG, United Kingdom.

logging in using host alias not working

[Larry Martell]

We use host aliases to connect to MySQL all the time, never had an
issue before. Today we added a new alias, and we cannot connect to the
server using that one alias but only when we are on the local machine.

Here is the NIS entry for this host:

# ypmatch -k ubshp2 hosts
ubshp2 192.132.2.143ubshp2.predict.com ubshp2 ti-us-dev
intradb-au-qa intradb-us-alpha intradb-us-dev ti-test-dr
intradb-test-dr

I can connect from all these aliases, except intradb-test-dr when I am
on ubshp2, e.g.:

>From ubshp2 using the intradb-us-dev alias, works:

ti-test-dr@ubshp2: mysql -h intradb-us-dev -u tradeinfra -p intradb_test_dr
Welcome to the MySQL monitor.  Commands end with ; or \g.

>From ubshp2 using the intradb-au-qa alias, works:

ti-test-dr@ubshp2: mysql -h intradb-au-qa  -u tradeinfra -p intradb_test_dr
Welcome to the MySQL monitor.  Commands end with ; or \g.

>From ubshp2 using the intradb-test-dr alias, does not work:

ti-test-dr@ubshp2: mysql -h intradb-test-dr -u tradeinfra -p intradb_test_dr
ERROR 1045 (28000): Access denied for user
'tradeinfra'@'ubshp2.predict.com' (using password: YES)

But from a different host using the intradb-test-dr alias, works:

ti-test-dr@hades: mysql -h intradb-test-dr -u tradeinfra -p intradb_test_dr
Welcome to the MySQL monitor.  Commands end with ; or \g.

Anyone have any ideas as to why this one is not working? I've been
messing with this for 2 days. Nothing in the error log. I've dropped
and re-added the user, I've bounced the server, I've removed and
re-added the alias. I've googled and googled and found nothing.

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

Re: logging in using host alias not working

[Larry Martell]

Found the problem. There was cached DNS info on the host. Restarted
nscd and then it worked.

On Thu, Apr 18, 2013 at 10:59 AM, Larry Martell  wrote:
> We use host aliases to connect to MySQL all the time, never had an
> issue before. Today we added a new alias, and we cannot connect to the
> server using that one alias but only when we are on the local machine.
>
> Here is the NIS entry for this host:
>
> # ypmatch -k ubshp2 hosts
> ubshp2 192.132.2.143ubshp2.predict.com ubshp2 ti-us-dev
> intradb-au-qa intradb-us-alpha intradb-us-dev ti-test-dr
> intradb-test-dr
>
> I can connect from all these aliases, except intradb-test-dr when I am
> on ubshp2, e.g.:
>
> From ubshp2 using the intradb-us-dev alias, works:
>
> ti-test-dr@ubshp2: mysql -h intradb-us-dev -u tradeinfra -p 
> intradb_test_dr
> Welcome to the MySQL monitor.  Commands end with ; or \g.
>
> From ubshp2 using the intradb-au-qa alias, works:
>
> ti-test-dr@ubshp2: mysql -h intradb-au-qa  -u tradeinfra -p 
> intradb_test_dr
> Welcome to the MySQL monitor.  Commands end with ; or \g.
>
> From ubshp2 using the intradb-test-dr alias, does not work:
>
> ti-test-dr@ubshp2: mysql -h intradb-test-dr -u tradeinfra -p 
> intradb_test_dr
> ERROR 1045 (28000): Access denied for user
> 'tradeinfra'@'ubshp2.predict.com' (using password: YES)
>
> But from a different host using the intradb-test-dr alias, works:
>
> ti-test-dr@hades: mysql -h intradb-test-dr -u tradeinfra -p 
> intradb_test_dr
> Welcome to the MySQL monitor.  Commands end with ; or \g.
>
> Anyone have any ideas as to why this one is not working? I've been
> messing with this for 2 days. Nothing in the error log. I've dropped
> and re-added the user, I've bounced the server, I've removed and
> re-added the alias. I've googled and googled and found nothing.

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

Re: MUltiple value in single insert is not working in mysql procedure

[hsv]

 2012/12/03 19:10 +0530, amit 
Problem
mysql> call mobile_series1('(99889988),(12334565)');

You are expecting MySQL to turn one string operand into twain number operands. 
That does not happen, unless you use PREPARE, which, I suspect, is not part of 
your homework. 


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

Re: MUltiple value in single insert is not working in mysql procedure

[Peter Brawley]

On 2012-12-03 7:40 AM, amit wrote:

Hi Team,

I am not able to use multi value insert via argument in mysql stored 
procedure, Please help where am I wrong. Thanks in Advance !


mysql> insert into input_data1 values(),(),(),();
Query OK, 4 rows affected (0.00 sec)

*Problem*
mysql> call mobile_series1('(99889988),(12334565)');
+---+
| mobile|
+---+
| (99889988),(12334565) |
+---+
1 row in set (0.01 sec)

instead of 2 rows in table its only one row.

*Function Code:*
|DELIMITER|
DROP  PROCEDURE  IF  EXISTS  mobile_series1;
CREATE  PROCEDURE  mobile_series1(IN  str text)   
 LANGUAGE SQL READS SQL DATA
 BEGIN 
  DROP  TABLE  IF  EXISTS  input_data1;   
  CREATE  TEMPORARYTABLE  input_data1(mobile varchar(1000))  engine=memory;

  INSERT  INTO  input_data1(mobile)  VALUES  (str);
 SELECT * FROM input_data1;
  END  |
DELIMITER;|


|Dynamic SQL requires PREPARE, see the manual page for that cmd.

PB


|

|



|

--
Best Regards
Amit Jain
www.netcore.co.in
Ext - 4109
#9833777592


Banner plane



netCORE wins GOLD in 1^st Smarties India 2012 Awards
Organized by Mobile Marketing Association (MMA)
Cross Media Integration for 'Colgate Active Salt Taste Challenge 2012' 
Campaign

*Campaigns nominated for other categories *
*Brand Awareness* Active Wheel Mobile Marketing Campaign 	*Innovation* 
Active Wheel Mobile Marketing Campaign

Re: Autostart not working for me in 5.5.8 version

[Noel Butler]

Hi Joerg,

On Mon, 2010-12-20 at 18:59 +0100, Joerg Bruehe wrote:

> Hi Noel, all!
> 
> 
> Noel Butler wrote:
> > On Thu, 2010-12-16 at 10:08 -0300, Alejandro Bednarik wrote:
> >> You are right. In previous version init script have a default value, now is
> >> empty.
> >>
> >> mysql 5.1..53   -   basedir=/usr/local/mysql
> >>
> >> mysql-5.5.8  -   basedir=.
> >>
> > 
> > 
> > /me ponders at who the IDIOT is that decided that cmake is the way of
> > mysql now.
> >  I mean WTF...
> 
> It was the ones who realized that two different build mechanisms
> (autotools for Unix, cmake for Windows) will always cause divergence,
> when one is maintained with a change and the other isn't.
> 
> Being database people, we know the importance of consistency  ;)
> 


I can tell its done by windowsy people, it has pretty colours  :)
seriously though , it's almost as bad as trying to figure out what to
give postifx hehe
And in the real world, many sysadmins have to build the database servers
for the database admins, I've made my opinion known about cmake so I'll
leave my cursing at that.



> > 
> > /me sticks with 5.1
> 
> Do as you like, but realize that 5.1 won't see major development in the
> future and will be put on "extended support only" in due time.
> 


For what we use it for, it's likely fine, (user auth/radius/web/mail etc
etc etc) nothing complex.


> So if anybody encounters differences between 5.1 and 5.5 in such
> settings which aren't documented (= by intention, or unavoidable),
> please file bugs to get them fixed.
> 


Well, the documentation could be a little more in depth, if you remove
method A, and if method B is completely compatible, then there must be
detailed information, it is not very good for time management to spend
hours looking over the website, yes forge.mysql.com has a handy
reference, but the bit about replacing, for instance  plugins=max, it is
not clear what we need to include, we know what is default, and some
examples of to add given the example given, but no reference to the max
server, without time consuming research to see what plugins=max
includes, compare, then find equivalent DINSTALL_blah=1's to add, I
think it is a backwards step, and you're likely to see more cussing as
more go to upgrade.

Nice thing though, my original my.cnf didnt cause it to bail out upon
restart, however I only installed it on one very light use server.
want to lay with it a bit before it goes on anything too serious.

Cheers

<>

signature.asc
Description: This is a digitally signed message part

Re: Autostart not working for me in 5.5.8 version

[Joerg Bruehe]

Hi Noel, all!


Noel Butler wrote:
> On Thu, 2010-12-16 at 10:08 -0300, Alejandro Bednarik wrote:
>> You are right. In previous version init script have a default value, now is
>> empty.
>>
>> mysql 5.1..53   -   basedir=/usr/local/mysql
>>
>> mysql-5.5.8  -   basedir=.
>>
> 
> 
> /me ponders at who the IDIOT is that decided that cmake is the way of
> mysql now.
>  I mean WTF...

It was the ones who realized that two different build mechanisms
(autotools for Unix, cmake for Windows) will always cause divergence,
when one is maintained with a change and the other isn't.

Being database people, we know the importance of consistency  ;)

> 
> [[...]]
> 
> /me sticks with 5.1

Do as you like, but realize that 5.1 won't see major development in the
future and will be put on "extended support only" in due time.

So if anybody encounters differences between 5.1 and 5.5 in such
settings which aren't documented (= by intention, or unavoidable),
please file bugs to get them fixed.


Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@oracle.com
ORACLE Deutschland B.V. & Co. KG,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Autostart not working for me in 5.5.8 version

[Alejandro Bednarik]

You are right. In previous version init script have a default value, now is
empty.

mysql 5.1..53   -   basedir=/usr/local/mysql

mysql-5.5.8  -   basedir=.

Cheers.

On Thu, Dec 16, 2010 at 12:42 AM, Yangontha Maung
wrote:

> Hi,
>
> I will answer my own question.
>
> I have to add a line in (/etc/my.cnf) as follows:-
>
> basedir=/usr/local/mysql
>
> I have installed mysql in /usr/local/mysql. But the same was done in
> previous versions and I do not need to add this.
>
> Anyway, now that it is working.
>
> rgds,
>
>
>
> --- On Thu, 12/16/10, Yangontha Maung  wrote:
>
> > From: Yangontha Maung 
> > Subject: Autostart not working for me in 5.5.8 version
> > To: mysql@lists.mysql.com
> > Date: Thursday, December 16, 2010, 11:14 AM
> > Hi,
> >
> > Please help to set up the autostart of mysql when the Linux
> > server starts. It is not working for me after installing to
> > version 5.5.8.
> >
> > I have been using different versions of mysql for quite
> > some time with different servers, Fedora versions. All these
> > are working for autostart. But for this 5.5.8 I tried on two
> > servers and both are not working for autostart.
> >
> > Please help.
> >
> > The following are my details:=-
> >
> >
> >
> >
> > Installed on Fedora 12 Linux
> > Previously mysql 5.1.48 is working and autorestart is also
> > working.
> >
> > Now installed 5.5.8
> > Using manual starting is working
> > /usr/local/mysql/bin/mysqld_safe &
> >
> > But the auto start is not working.
> > I have done this
> > $ cp /usr/local/mysql/bin/support-files/mysql.server
> > /etc/init.d/mysql
> > $ chmod +x /etc/init.d/mysql
> > $ ln –s /etc/init.d/mysql /etc/rc3.d/S99mysql
> > $ ln –s /etc/init.d/mysql /etc/rc5.d/S99mysql
> > $ ln –s /etc/init.d/mysql /etc/rc0.d/K01mysql
> >
> >
> > thanks and rgds,
> >
> >
> >
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=yangontha5...@yahoo.com
> >
> >
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=alejand...@olx.com
>
>


-- 


Infrastructure Team

OLX Inc.

Buenos Aires - Argentina
Phone   : 54.11.4775.6696
Mobile : 54.911.50436059
Email: alejand...@olx.com

Re: Autostart not working for me in 5.5.8 version

[Yangontha Maung]

Hi,

I will answer my own question. 

I have to add a line in (/etc/my.cnf) as follows:-

basedir=/usr/local/mysql

I have installed mysql in /usr/local/mysql. But the same was done in previous 
versions and I do not need to add this.

Anyway, now that it is working. 

rgds,



--- On Thu, 12/16/10, Yangontha Maung  wrote:

> From: Yangontha Maung 
> Subject: Autostart not working for me in 5.5.8 version
> To: mysql@lists.mysql.com
> Date: Thursday, December 16, 2010, 11:14 AM
> Hi,
> 
> Please help to set up the autostart of mysql when the Linux
> server starts. It is not working for me after installing to
> version 5.5.8.
> 
> I have been using different versions of mysql for quite
> some time with different servers, Fedora versions. All these
> are working for autostart. But for this 5.5.8 I tried on two
> servers and both are not working for autostart. 
> 
> Please help.
> 
> The following are my details:=-
> 
> 
> 
> 
> Installed on Fedora 12 Linux
> Previously mysql 5.1.48 is working and autorestart is also
> working.
> 
> Now installed 5.5.8
> Using manual starting is working
> /usr/local/mysql/bin/mysqld_safe &
> 
> But the auto start is not working.
> I have done this
> $ cp /usr/local/mysql/bin/support-files/mysql.server
> /etc/init.d/mysql
> $ chmod +x /etc/init.d/mysql
> $ ln –s /etc/init.d/mysql /etc/rc3.d/S99mysql
> $ ln –s /etc/init.d/mysql /etc/rc5.d/S99mysql
> $ ln –s /etc/init.d/mysql /etc/rc0.d/K01mysql
> 
> 
> thanks and rgds,
> 
> 
> 
> 
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=yangontha5...@yahoo.com
> 
> 




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Autostart not working for me in 5.5.8 version

[Yangontha Maung]

Hi,

Please help to set up the autostart of mysql when the Linux server starts. It 
is not working for me after installing to version 5.5.8.

I have been using different versions of mysql for quite some time with 
different servers, Fedora versions. All these are working for autostart. But 
for this 5.5.8 I tried on two servers and both are not working for autostart. 

Please help.

The following are my details:=-




Installed on Fedora 12 Linux
Previously mysql 5.1.48 is working and autorestart is also working.

Now installed 5.5.8
Using manual starting is working
/usr/local/mysql/bin/mysqld_safe &

But the auto start is not working.
I have done this
$ cp /usr/local/mysql/bin/support-files/mysql.server /etc/init.d/mysql
$ chmod +x /etc/init.d/mysql
$ ln –s /etc/init.d/mysql /etc/rc3.d/S99mysql
$ ln –s /etc/init.d/mysql /etc/rc5.d/S99mysql
$ ln –s /etc/init.d/mysql /etc/rc0.d/K01mysql


thanks and rgds,







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Logs not working

[monloi perez]

Thansk,

Will try to turn on log_warnings.

-Mon





From: Nitin Mehta 
To: monloi perez ; Ananda Kumar 
Cc: mysql mailing list 
Sent: Sun, September 5, 2010 12:03:45 PM
Subject: Re: Logs not working

I believe that will not be logged unless you have enabled log_warnings. Too 
many 

connections would normally mean that the number is going beyond the configured 
limit and denying new sessions is an expected behavior and should not be an 
error really.

Regards,
Nitin





From: monloi perez 
To: Ananda Kumar 
Cc: mysql mailing list 
Sent: Sat, September 4, 2010 11:37:42 AM
Subject: Re: Logs not working

on the db server? meaning  the mysqld log right? THere is really no data for 
teh 


current error. The last error they said was too much connections and shouldnt 
that be logged in mysqld.log?

Thanks,
Mon





From: Ananda Kumar 
To: monloi perez 
Cc: mysql mailing list 
Sent: Thu, September 2, 2010 6:14:26 PM
Subject: Re: Logs not working


Did u check the logs on the db server, to see what the issue was.

regards
anandkl


On Thu, Sep 2, 2010 at 6:25 AM, monloi perez  wrote:

All,
>
>I'm not sure if this is the right mailing list since the specific mailing lists
>doesn't seem to meet my concern.
>
>For some reason mysql client stops logging to mysqd.log. We had an issue on Aug
>20. But when I checked the log, the latest was on Aug 2.
>Any idea on how to resolve this or what caused it?
>
>Thanks,
>Mon
>
>
>
>


  

Re: Logs not working

[Nitin Mehta]

I believe that will not be logged unless you have enabled log_warnings. Too 
many 
connections would normally mean that the number is going beyond the configured 
limit and denying new sessions is an expected behavior and should not be an 
error really.

Regards,
Nitin





From: monloi perez 
To: Ananda Kumar 
Cc: mysql mailing list 
Sent: Sat, September 4, 2010 11:37:42 AM
Subject: Re: Logs not working

on the db server? meaning  the mysqld log right? THere is really no data for 
teh 

current error. The last error they said was too much connections and shouldnt 
that be logged in mysqld.log?

Thanks,
Mon





From: Ananda Kumar 
To: monloi perez 
Cc: mysql mailing list 
Sent: Thu, September 2, 2010 6:14:26 PM
Subject: Re: Logs not working


Did u check the logs on the db server, to see what the issue was.

regards
anandkl


On Thu, Sep 2, 2010 at 6:25 AM, monloi perez  wrote:

All,
>
>I'm not sure if this is the right mailing list since the specific mailing lists
>doesn't seem to meet my concern.
>
>For some reason mysql client stops logging to mysqd.log. We had an issue on Aug
>20. But when I checked the log, the latest was on Aug 2.
>Any idea on how to resolve this or what caused it?
>
>Thanks,
>Mon
>
>
>
>    


  

Re: Logs not working

[monloi perez]

on the db server? meaning  the mysqld log right? THere is really no data for 
teh 
current error. The last error they said was too much connections and shouldnt 
that be logged in mysqld.log?

Thanks,
Mon





From: Ananda Kumar 
To: monloi perez 
Cc: mysql mailing list 
Sent: Thu, September 2, 2010 6:14:26 PM
Subject: Re: Logs not working


Did u check the logs on the db server, to see what the issue was.
 
regards
anandkl


On Thu, Sep 2, 2010 at 6:25 AM, monloi perez  wrote:

All,
>
>I'm not sure if this is the right mailing list since the specific mailing lists
>doesn't seem to meet my concern.
>
>For some reason mysql client stops logging to mysqd.log. We had an issue on Aug
>20. But when I checked the log, the latest was on Aug 2.
>Any idea on how to resolve this or what caused it?
>
>Thanks,
>Mon
>
>
>
> 



  

Re: Symlinks not working when pointing to another table.

[Shawn Green (MySQL)]

On 9/2/2010 3:31 PM, Julien Lory wrote:

 Hello,

I've done lot of researches and tests but can't find any answer. I need 
to share one table between two db, those two db are in the same path ( 
/var/lib/mysql/db1 & db2 ). I created symbolic links for db2 pointing to 
the table in db1.

When I query the table from db2 I get this error :
'ERROR 1030 (HY000): Got error 140 from storage engine'
I'm sure it was working in previous version of MySQL, but from the last 
version it's no longer working.


This is how it looks :

test-lan:/var/lib/mysql/test3# ls -alh
drwx-- 2 mysql mysql 4.0K 2010-08-30 13:28 .
drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 ..
lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm ->  
/var/lib/mysql/test/blbl.frm
lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD ->  
/var/lib/mysql/test/blbl.MYD
lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI ->  
/var/lib/mysql/test/blbl.MYI

-rw-rw 1 mysql mysql 65 2010-08-30 13:24 db.opt


I really need those symlinks, is there a way to make them working like 
before ? ( old MySQL-server is fine )




You cannot share one set of files (one table) between two different 
MySQL instances. That also means that you cannot share a table between 
the same instance by pretending it's something else by a symlink. The 
storage engines are designed with the premise that they have exclusive 
domain over the files they manage. Sharing is not part of their code.


If you need to expose a table from within a different database, you must 
use a view.


--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Symlinks not working when pointing to another table.

[Julien Lory]

I'm using Debian Lenny ( up to date ), so there is no app armor / 
selinux / grsec :/


ii  mysql-client-5.0  5.0.51a-24+lenny4 MySQL 
database client binaries
ii  mysql-common  5.0.51a-24+lenny4 MySQL 
database common files
ii  mysql-server-5.0  5.0.51a-24+lenny4 MySQL 
database server binaries



On 02/09/2010 16:18, Daevid Vincent wrote:



   

-Original Message-
From: Julien Lory [mailto:julien.l...@gmail.com]
Sent: Thursday, September 02, 2010 12:31 PM
To: mysql@lists.mysql.com
Subject: Symlinks not working when pointing to another table.

   Hello,

I've done lot of researches and tests but can't find any
answer. I need
to share one table between two db, those two db are in the
same path (
/var/lib/mysql/db1&  db2 ). I created symbolic links for db2
pointing to
the table in db1.
When I query the table from db2 I get this error :
'ERROR 1030 (HY000): Got error 140 from storage engine'
I'm sure it was working in previous version of MySQL, but
from the last
version it's no longer working.

This is how it looks :

test-lan:/var/lib/mysql/test3# ls -alh
drwx-- 2 mysql mysql 4.0K 2010-08-30 13:28 .
drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 ..
lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm ->
/var/lib/mysql/test/blbl.frm
lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD ->
/var/lib/mysql/test/blbl.MYD
lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI ->
/var/lib/mysql/test/blbl.MYI
-rw-rw 1 mysql mysql 65 2010-08-30 13:24 db.opt


I really need those symlinks, is there a way to make them
working like
before ? ( old MySQL-server is fine )
 

Just out of curiosity. If you're using apparmor and this new directory is
on another partion or mount point or anything, you might have to add a rule
in apparmor's config for them...

   


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Symlinks not working when pointing to another table.

[Daevid Vincent]

 

> -Original Message-
> From: Julien Lory [mailto:julien.l...@gmail.com] 
> Sent: Thursday, September 02, 2010 12:31 PM
> To: mysql@lists.mysql.com
> Subject: Symlinks not working when pointing to another table.
> 
>   Hello,
> 
> I've done lot of researches and tests but can't find any 
> answer. I need 
> to share one table between two db, those two db are in the 
> same path ( 
> /var/lib/mysql/db1 & db2 ). I created symbolic links for db2 
> pointing to 
> the table in db1.
> When I query the table from db2 I get this error :
> 'ERROR 1030 (HY000): Got error 140 from storage engine'
> I'm sure it was working in previous version of MySQL, but 
> from the last 
> version it's no longer working.
> 
> This is how it looks :
> 
> test-lan:/var/lib/mysql/test3# ls -alh
> drwx-- 2 mysql mysql 4.0K 2010-08-30 13:28 .
> drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 ..
> lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm ->  
> /var/lib/mysql/test/blbl.frm
> lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD ->  
> /var/lib/mysql/test/blbl.MYD
> lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI ->  
> /var/lib/mysql/test/blbl.MYI
> -rw-rw 1 mysql mysql 65 2010-08-30 13:24 db.opt
> 
> 
> I really need those symlinks, is there a way to make them 
> working like 
> before ? ( old MySQL-server is fine )

Just out of curiosity. If you're using apparmor and this new directory is
on another partion or mount point or anything, you might have to add a rule
in apparmor's config for them...


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Symlinks not working when pointing to another table.

[Julien Lory]

 Hello,

I've done lot of researches and tests but can't find any answer. I need 
to share one table between two db, those two db are in the same path ( 
/var/lib/mysql/db1 & db2 ). I created symbolic links for db2 pointing to 
the table in db1.

When I query the table from db2 I get this error :
'ERROR 1030 (HY000): Got error 140 from storage engine'
I'm sure it was working in previous version of MySQL, but from the last 
version it's no longer working.


This is how it looks :

test-lan:/var/lib/mysql/test3# ls -alh
drwx-- 2 mysql mysql 4.0K 2010-08-30 13:28 .
drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 ..
lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm ->  
/var/lib/mysql/test/blbl.frm
lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD ->  
/var/lib/mysql/test/blbl.MYD
lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI ->  
/var/lib/mysql/test/blbl.MYI
-rw-rw 1 mysql mysql 65 2010-08-30 13:24 db.opt


I really need those symlinks, is there a way to make them working like 
before ? ( old MySQL-server is fine )


Thanks,


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Logs not working

[Ananda Kumar]

Did u check the logs on the db server, to see what the issue was.

regards
anandkl

On Thu, Sep 2, 2010 at 6:25 AM, monloi perez  wrote:

> All,
>
> I'm not sure if this is the right mailing list since the specific mailing
> lists
> doesn't seem to meet my concern.
>
> For some reason mysql client stops logging to mysqd.log. We had an issue on
> Aug
> 20. But when I checked the log, the latest was on Aug 2.
> Any idea on how to resolve this or what caused it?
>
> Thanks,
> Mon
>
>
>
>

Logs not working

[monloi perez]

All,

I'm not sure if this is the right mailing list since the specific mailing lists 
doesn't seem to meet my concern.

For some reason mysql client stops logging to mysqd.log. We had an issue on Aug 
20. But when I checked the log, the latest was on Aug 2.
Any idea on how to resolve this or what caused it?

Thanks,
Mon



  

Re: Show Tables not working

[mos]

At 08:36 AM 1/13/2010, Intell! Soft wrote:

As I told, Backticks don't work.
But it is valid to use spaces in DB's because, mySQL Administrator can
handle it and my third party tool MySQL Maestro can also handle it. So,
when those two programs get the tables, then there has to be a solution
for it!?

THX



Replace the space in the database name with an underscore and save yourself 
a lot of grief. You can go to the data directory and see if you can browse 
the directory. Maybe the OS has a problem with spaces or maybe it isn't 
really a space in the database name. Maybe it is a special character or two 
spaces?


Mike




-Ursprüngliche Nachricht-
Von: Ananda Kumar [mailto:anan...@gmail.com]
Bereitgestellt: Mittwoch, 13. Jänner 2010 15:07
Bereitgestellt in: gmane.comp.db.mysql.general
Unterhaltung: Show Tables not working
Betreff: Re: Show Tables not working

i dont think "my design" is valid.
There should not be any SPACE for a db name.




On Wed, Jan 13, 2010 at 7:26 PM, Johan De Meersman
wrote:

> Try backticks.
>
> On Wed, Jan 13, 2010 at 2:44 PM, Intell! Soft  >wrote:
>
> > Hey
> >
> > Nobody any ideas?
> >
> >
> >
> >
> > -Ursprüngliche Nachricht-
> > Von: Intell! Soft [mailto:intellis...@fachoptiker.net]
> > Bereitgestellt: Freitag, 08. Jänner 2010 11:06
> > Bereitgestellt in: gmane.comp.db.mysql.general
> > Unterhaltung: Show Tables not working
> > Betreff: Show Tables not working
> >
> > Hey
> >
> > I try to use: Show Tables from My Design
> > Where "My Design" is the name of the Database. - But it is not
working
> > Even not with: Show Tables from 'My Design'
> >
> > How can I use a Database name wit 2 words in it!?
> >
> > THX
> >
> >
> >
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
> >
> >
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Show Tables not working

[Intell! Soft]

Yes, I am sorry, I didn't use Backticks.. - I used ' not `

THX - now it works!




-Ursprüngliche Nachricht-
Von: Johan De Meersman [mailto:vegiv...@tuxera.be]
Bereitgestellt: Mittwoch, 13. Jänner 2010 17:11
Bereitgestellt in: gmane.comp.db.mysql.general
Unterhaltung: Show Tables not working
Betreff: Re: Show Tables not working

Then you possibly aren't using backticks, because I just tested them :-)

On Wed, Jan 13, 2010 at 3:36 PM, Intell! Soft
wrote:

> As I told, Backticks don't work.
> But it is valid to use spaces in DB's because, mySQL Administrator can
> handle it and my third party tool MySQL Maestro can also handle it.
So,
> when those two programs get the tables, then there has to be a
solution
> for it!?
>
> THX
>
>
>
>
> -Ursprüngliche Nachricht-
> Von: Ananda Kumar [mailto:anan...@gmail.com]
> Bereitgestellt: Mittwoch, 13. Jänner 2010 15:07
> Bereitgestellt in: gmane.comp.db.mysql.general
> Unterhaltung: Show Tables not working
> Betreff: Re: Show Tables not working
>
> i dont think "my design" is valid.
> There should not be any SPACE for a db name.
>
>
>
>
> On Wed, Jan 13, 2010 at 7:26 PM, Johan De Meersman
> wrote:
>
> > Try backticks.
> >
> > On Wed, Jan 13, 2010 at 2:44 PM, Intell! Soft
 > >wrote:
> >
> > > Hey
> > >
> > > Nobody any ideas?
> > >
> > >
> > >
> > >
> > > -Ursprüngliche Nachricht-
> > > Von: Intell! Soft [mailto:intellis...@fachoptiker.net]
> > > Bereitgestellt: Freitag, 08. Jänner 2010 11:06
> > > Bereitgestellt in: gmane.comp.db.mysql.general
> > > Unterhaltung: Show Tables not working
> > > Betreff: Show Tables not working
> > >
> > > Hey
> > >
> > > I try to use: Show Tables from My Design
> > > Where "My Design" is the name of the Database. - But it is not
> working
> > > Even not with: Show Tables from 'My Design'
> > >
> > > How can I use a Database name wit 2 words in it!?
> > >
> > > THX
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
> > >
> > >
> >
> >
> > --
> > Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Show Tables not working

[Johan De Meersman]

Then you possibly aren't using backticks, because I just tested them :-)

On Wed, Jan 13, 2010 at 3:36 PM, Intell! Soft wrote:

> As I told, Backticks don't work.
> But it is valid to use spaces in DB's because, mySQL Administrator can
> handle it and my third party tool MySQL Maestro can also handle it. So,
> when those two programs get the tables, then there has to be a solution
> for it!?
>
> THX
>
>
>
>
> -Ursprüngliche Nachricht-
> Von: Ananda Kumar [mailto:anan...@gmail.com]
> Bereitgestellt: Mittwoch, 13. Jänner 2010 15:07
> Bereitgestellt in: gmane.comp.db.mysql.general
> Unterhaltung: Show Tables not working
> Betreff: Re: Show Tables not working
>
> i dont think "my design" is valid.
> There should not be any SPACE for a db name.
>
>
>
>
> On Wed, Jan 13, 2010 at 7:26 PM, Johan De Meersman
> wrote:
>
> > Try backticks.
> >
> > On Wed, Jan 13, 2010 at 2:44 PM, Intell! Soft  > >wrote:
> >
> > > Hey
> > >
> > > Nobody any ideas?
> > >
> > >
> > >
> > >
> > > -Ursprüngliche Nachricht-
> > > Von: Intell! Soft [mailto:intellis...@fachoptiker.net]
> > > Bereitgestellt: Freitag, 08. Jänner 2010 11:06
> > > Bereitgestellt in: gmane.comp.db.mysql.general
> > > Unterhaltung: Show Tables not working
> > > Betreff: Show Tables not working
> > >
> > > Hey
> > >
> > > I try to use: Show Tables from My Design
> > > Where "My Design" is the name of the Database. - But it is not
> working
> > > Even not with: Show Tables from 'My Design'
> > >
> > > How can I use a Database name wit 2 words in it!?
> > >
> > > THX
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
> > >
> > >
> >
> >
> > --
> > Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Show Tables not working

[Johan De Meersman]

It is valid, it's just not a very smart thing to do, as various tools may
not be very happy about it.

On Wed, Jan 13, 2010 at 3:07 PM, Ananda Kumar  wrote:

> i dont think "my design" is valid.
> There should not be any SPACE for a db name.
>
>
>
>
> On Wed, Jan 13, 2010 at 7:26 PM, Johan De Meersman  >wrote:
>
> > Try backticks.
> >
> > On Wed, Jan 13, 2010 at 2:44 PM, Intell! Soft  > >wrote:
> >
> > > Hey
> > >
> > > Nobody any ideas?
> > >
> > >
> > >
> > >
> > > -Ursprüngliche Nachricht-
> > > Von: Intell! Soft [mailto:intellis...@fachoptiker.net]
> > > Bereitgestellt: Freitag, 08. Jänner 2010 11:06
> > > Bereitgestellt in: gmane.comp.db.mysql.general
> > > Unterhaltung: Show Tables not working
> > > Betreff: Show Tables not working
> > >
> > > Hey
> > >
> > > I try to use: Show Tables from My Design
> > > Where "My Design" is the name of the Database. - But it is not working
> > > Even not with: Show Tables from 'My Design'
> > >
> > > How can I use a Database name wit 2 words in it!?
> > >
> > > THX
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
> > >
> > >
> >
> >
> > --
> > Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
> >
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Show Tables not working

[Intell! Soft]

As I told, Backticks don't work.
But it is valid to use spaces in DB's because, mySQL Administrator can
handle it and my third party tool MySQL Maestro can also handle it. So,
when those two programs get the tables, then there has to be a solution
for it!?

THX




-Ursprüngliche Nachricht-
Von: Ananda Kumar [mailto:anan...@gmail.com]
Bereitgestellt: Mittwoch, 13. Jänner 2010 15:07
Bereitgestellt in: gmane.comp.db.mysql.general
Unterhaltung: Show Tables not working
Betreff: Re: Show Tables not working

i dont think "my design" is valid.
There should not be any SPACE for a db name.




On Wed, Jan 13, 2010 at 7:26 PM, Johan De Meersman
wrote:

> Try backticks.
>
> On Wed, Jan 13, 2010 at 2:44 PM, Intell! Soft  >wrote:
>
> > Hey
> >
> > Nobody any ideas?
> >
> >
> >
> >
> > -Ursprüngliche Nachricht-
> > Von: Intell! Soft [mailto:intellis...@fachoptiker.net]
> > Bereitgestellt: Freitag, 08. Jänner 2010 11:06
> > Bereitgestellt in: gmane.comp.db.mysql.general
> > Unterhaltung: Show Tables not working
> > Betreff: Show Tables not working
> >
> > Hey
> >
> > I try to use: Show Tables from My Design
> > Where "My Design" is the name of the Database. - But it is not
working
> > Even not with: Show Tables from 'My Design'
> >
> > How can I use a Database name wit 2 words in it!?
> >
> > THX
> >
> >
> >
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
> >
> >
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Show Tables not working

[Ananda Kumar]

i dont think "my design" is valid.
There should not be any SPACE for a db name.




On Wed, Jan 13, 2010 at 7:26 PM, Johan De Meersman wrote:

> Try backticks.
>
> On Wed, Jan 13, 2010 at 2:44 PM, Intell! Soft  >wrote:
>
> > Hey
> >
> > Nobody any ideas?
> >
> >
> >
> >
> > -Ursprüngliche Nachricht-
> > Von: Intell! Soft [mailto:intellis...@fachoptiker.net]
> > Bereitgestellt: Freitag, 08. Jänner 2010 11:06
> > Bereitgestellt in: gmane.comp.db.mysql.general
> > Unterhaltung: Show Tables not working
> > Betreff: Show Tables not working
> >
> > Hey
> >
> > I try to use: Show Tables from My Design
> > Where "My Design" is the name of the Database. - But it is not working
> > Even not with: Show Tables from 'My Design'
> >
> > How can I use a Database name wit 2 words in it!?
> >
> > THX
> >
> >
> >
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
> >
> >
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

Re: Show Tables not working

[Johan De Meersman]

Try backticks.

On Wed, Jan 13, 2010 at 2:44 PM, Intell! Soft wrote:

> Hey
>
> Nobody any ideas?
>
>
>
>
> -Ursprüngliche Nachricht-
> Von: Intell! Soft [mailto:intellis...@fachoptiker.net]
> Bereitgestellt: Freitag, 08. Jänner 2010 11:06
> Bereitgestellt in: gmane.comp.db.mysql.general
> Unterhaltung: Show Tables not working
> Betreff: Show Tables not working
>
> Hey
>
> I try to use: Show Tables from My Design
> Where "My Design" is the name of the Database. - But it is not working
> Even not with: Show Tables from 'My Design'
>
> How can I use a Database name wit 2 words in it!?
>
> THX
>
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>
>


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Show Tables not working

[Intell! Soft]

Hey

Nobody any ideas?




-Ursprüngliche Nachricht-
Von: Intell! Soft [mailto:intellis...@fachoptiker.net]
Bereitgestellt: Freitag, 08. Jänner 2010 11:06
Bereitgestellt in: gmane.comp.db.mysql.general
Unterhaltung: Show Tables not working
Betreff: Show Tables not working

Hey

I try to use: Show Tables from My Design
Where "My Design" is the name of the Database. - But it is not working
Even not with: Show Tables from 'My Design'

How can I use a Database name wit 2 words in it!?

THX







-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

AW: InnoDB Corrupted databases (innodb_force_recovery not working)

[Lukas C. C. Hempel]

Baron,

Thanks alot for your reply - I am trying out these tools today.

Lukas


Lukas C. C. Hempel
CEO

Delux Group - Approaching future.
www.delux.me

Postfach 10 02 10
D-48051 Münster

Mail: lu...@delux.me

This e-mail may contain confidential and/or privileged information. If you
are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail. Any
unauthorised copying, disclosure or distribution of the material in this
e-mail is strictly forbidden.

Delux Credit: www.delux-credit.com   Delux Host: www.delux-host.com   Delux
Software: www.delux.me
Admin panel: http://admin.delux-host.com   Paypal: pay...@delux-host.com

Delux is a company of Lukas Hempel, Tax ID: 337/5105/2023

-Ursprüngliche Nachricht-
Von: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] Im Auftrag
von Baron Schwartz
Gesendet: Montag, 14. Dezember 2009 22:57
An: Lukas C. C. Hempel
Cc: mysql@lists.mysql.com
Betreff: Re: InnoDB Corrupted databases (innodb_force_recovery not working)

Lukas,

If you can't get innodb_force_recovery to work, then you might have to try
to recover the data with these tools:

http://code.google.com/p/innodb-tools/

Regards
Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=lu...@delux-host.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: InnoDB Corrupted databases (innodb_force_recovery not working)

[Baron Schwartz]

Lukas,

If you can't get innodb_force_recovery to work, then you might have to
try to recover the data with these tools:

http://code.google.com/p/innodb-tools/

Regards
Baron

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

InnoDB Corrupted databases (innodb_force_recovery not working)

[Lukas C. C. Hempel]

Hey there,

 

I have recently imported the database files from a crashed server and I am
currently trying to get the new server running with the old data.

 

However, after starting the MySQL Server, I only get the following error
message:

 

091214 20:51:46  mysqld started

InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on

InnoDB: Skipping log redo

InnoDB: Error: page n:o stored in the page read in is 538976288, should be
3!

InnoDB: Database page corruption on disk or a failed

InnoDB: file read of page 3.

InnoDB: You may have to recover from a backup.

091214 20:51:47  InnoDB: Page dump in ascii and hex (16384 bytes):

len 16384; hex
2020202020202020202020202020202020202020202020202020202020202020202020202020
20202020202020202020202020202020202020202020202020202020202$

091214 20:51:47  InnoDB: Page checksum 1605920614, prior-to-4.0.14-form
checksum 3161919040

InnoDB: stored checksum 538976288, prior-to-4.0.14-form stored checksum
236985105

InnoDB: Page lsn 538976288 538976288, low 4 bytes of lsn at page end 10651

InnoDB: Page number (if stored to page already) 538976288,

InnoDB: space id (if created with >= MySQL-4.1.1 and stored already)
538976288

InnoDB: Database page corruption on disk or a failed

InnoDB: file read of page 3.

InnoDB: You may have to recover from a backup.

InnoDB: It is also possible that your operating

InnoDB: system has corrupted its own file cache

InnoDB: and rebooting your computer removes the

InnoDB: error.

InnoDB: If the corrupt page is an index page

InnoDB: you can also try to fix the corruption

InnoDB: by dumping, dropping, and reimporting

InnoDB: the corrupt table. You can use CHECK

InnoDB: TABLE to scan your table for corruption.

InnoDB: See also http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html

InnoDB: about forcing recovery.



After that, the following error message is looped:

 

091214 20:51:47  InnoDB: Error: trying to access tablespace 538976288 page
no. 538976288,

InnoDB: but the tablespace does not exist or is just being dropped.

 

The MySQL server then is unusable and is taking 99% CPU ressources.

 

I tried to use the innodb_force_recovery option in the my.cfg file, but that
did not change this behavior.

 

Does anyone have an idea for me to restore the databases? I have no backup
and rely on getting this files back to work.

 

Any help is appreciated, thanks in advance,

 

Lukas

 



Lukas C. C. Hempel

 

Delux Group - Approaching future.

  www.delux.me

 

Postfach 10 02 10

D-48051 Münster

 

This e-mail may contain confidential and/or privileged information. If you
are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail. Any
unauthorised copying, disclosure or distribution of the material in this
e-mail is strictly forbidden.

 

Delux Credit:   www.delux-credit.com   Delux
Host:   www.delux-host.com   Delux Software:
 www.delux.me

Admin panel:   http://admin.delux-host.com
Paypal:   pay...@delux-host.com

 

Delux is a company of Lukas Hempel, Tax ID: 337/5105/2023

 

Re: Passwords not working

[John Oliver]

On Mon, Oct 19, 2009 at 07:45:11PM -0400, Michael Dykman wrote:
> The type of password instability you are talking about is pretty much
> unheard of in MySQL..

Yeah, well, I can have a real black thumb for this sort of thing :-)

I'm sure I read about at least two different ways to add passwords.  I
don't know... usually, it works, but on this particular machine... I
just tried to connect asa root again, and it's rejecting the password
that I KNOW I set... it's written down.  I am not mistyping it.  I'm
copy-and-pasting from the same text that I used to set the password.

I just don't know how to get MySQL to tell me exactly what it's unhappy
about.  I'm going to go and reset the root password again, and it'll
work for a while... but tomorrow, it almost certainly will not work
again, and I'll have to go back and reset it *again*.

> however, reverse DNS resolution is always
> messing up depending on the network setup.   From a console on your
> database host, how easily can you resolve the hostnames that your

The web server and database server (both VMs under VMware ESXi) each
have two network interfaces... one public, and one private.  The private
interfaces are connected to a private VLAN on a virtual switch that is
only for these two servers.  MySQL only listens on 172.16.1.1, and the
web server connects to that IP.  On each host, I have a hosts entry for
the other.

-- 
***
* John Oliver http://www.john-oliver.net/ *
* *
***

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Passwords not working

[Martin Gainty]

someone probably installed mysql for DHCP address e.g 192.168.fu.bar
then as luck would have it the IP address changed

if you pull all network connections everyone on that box should be able to 
access mysql

Salutations de l'état du chômage
Martin Gainty 
__ 
Note de déni et de confidentialité

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.




> Date: Mon, 19 Oct 2009 16:48:36 -0700
> From: listm...@websage.ca
> To: mysql@lists.mysql.com
> Subject: Re: Passwords not working
> 
> On Mon, 19 Oct 2009 16:30:47 -0700
> John Oliver  wrote:
> 
> > I have a problem with MySQL passwords... I set them, write them
> > down... and they stop working.  I have to go in and manually reset
> > them.
> > 
> > Right now, I have a database that, even after resetting the password,
> > I still cannot access it.
> > 
> > /var/log/mysql.log doesn't give me any useful information.  How can I
> > get MySQL to tell me what it's unhappy about, or get more information
> > from the client other than it just didn't work?
> > 
> > I also have problems with MySQL resolving names, or not resolving
> > names, or ???  I usually add 'user'@'ip.address' and
> > 'user'@'host.name'  But, more and more often, I've had to put
> > skip-name-resolve in my.cnf, but with my current problem, I'm still
> > seeing that 'user'@'host-name' is being rejected, even when I use -h
> > ip.address on the command line
> > 
> > And when I add those two users, and go to reset passwords, it doesn't
> > want to let me specify 'user'@'ip.address' or 'user'@'host.name' but
> > just 'user'  I *think* it's resetting the password for both... the
> > hashes are always the same.  But I just don't know.
> > 
> > What am I missing?
> > 
> 
> 
> Are you accessing MySQL from the same host? If so, you don't need the
> -h option unless that's the only entry in your grant table under that
> username (i.e. 'user'@'ip-address').
> 
> Can you give us an example of how you're setting the username and their
> permissions? Here's a typical example that gives access to an entire
> database to a single user provided they're accessing it on the same
> host:
> 
> GRANT ALL on .* to 'user'@'localhost' identified by
> 'foobar';
> 
> The username, password AND hostname have to match up for
> authentication to be successful: 'user'@'localhost' may be different
> than 'user'@'ip-address' even if they're intended to be the same person.
> 
> 
> -- 
>
> Greg Maruszeczka
> 
> Office:   250.412.9651  ||  Mobile: 250.886.4577
> Skype: websage.ca ||  GTalk IM: gmarus
> 
> http://websage.ca
> 
> GnuPG-ID: 0x4309323E, http://pgp.mit.edu
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
> 
  
_
Hotmail: Trusted email with Microsoft’s powerful SPAM protection.
http://clk.atdmt.com/GBL/go/177141664/direct/01/

Re: Passwords not working

[listmail]

On Mon, 19 Oct 2009 16:30:47 -0700
John Oliver  wrote:

> I have a problem with MySQL passwords... I set them, write them
> down... and they stop working.  I have to go in and manually reset
> them.
> 
> Right now, I have a database that, even after resetting the password,
> I still cannot access it.
> 
> /var/log/mysql.log doesn't give me any useful information.  How can I
> get MySQL to tell me what it's unhappy about, or get more information
> from the client other than it just didn't work?
> 
> I also have problems with MySQL resolving names, or not resolving
> names, or ???  I usually add 'user'@'ip.address' and
> 'user'@'host.name'  But, more and more often, I've had to put
> skip-name-resolve in my.cnf, but with my current problem, I'm still
> seeing that 'user'@'host-name' is being rejected, even when I use -h
> ip.address on the command line
> 
> And when I add those two users, and go to reset passwords, it doesn't
> want to let me specify 'user'@'ip.address' or 'user'@'host.name' but
> just 'user'  I *think* it's resetting the password for both... the
> hashes are always the same.  But I just don't know.
> 
> What am I missing?
> 


Are you accessing MySQL from the same host? If so, you don't need the
-h option unless that's the only entry in your grant table under that
username (i.e. 'user'@'ip-address').

Can you give us an example of how you're setting the username and their
permissions? Here's a typical example that gives access to an entire
database to a single user provided they're accessing it on the same
host:

GRANT ALL on .* to 'user'@'localhost' identified by
'foobar';

The username, password AND hostname have to match up for
authentication to be successful: 'user'@'localhost' may be different
than 'user'@'ip-address' even if they're intended to be the same person.


-- 
   
Greg Maruszeczka

Office: 250.412.9651  ||  Mobile: 250.886.4577
Skype: websage.ca ||  GTalk IM: gmarus

http://websage.ca

GnuPG-ID: 0x4309323E, http://pgp.mit.edu

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Passwords not working

[Michael Dykman]

The type of password instability you are talking about is pretty much
unheard of in MySQL.. however, reverse DNS resolution is always
messing up depending on the network setup.   From a console on your
database host, how easily can you resolve the hostnames that your
client is presenting?  What is your network setup?

Not the safest of practices, but for dev accounts, I usually create
one for user@'%' and sometimes one one for u...@localhost  if needed
and that works very well for me..


 - michael dykman



On Mon, Oct 19, 2009 at 7:30 PM, John Oliver  wrote:
> I have a problem with MySQL passwords... I set them, write them down...
> and they stop working.  I have to go in and manually reset them.
>
> Right now, I have a database that, even after resetting the password, I
> still cannot access it.
>
> /var/log/mysql.log doesn't give me any useful information.  How can I
> get MySQL to tell me what it's unhappy about, or get more information
> from the client other than it just didn't work?
>
> I also have problems with MySQL resolving names, or not resolving names,
> or ???  I usually add 'user'@'ip.address' and 'user'@'host.name'  But,
> more and more often, I've had to put skip-name-resolve in my.cnf, but
> with my current problem, I'm still seeing that 'user'@'host-name' is
> being rejected, even when I use -h ip.address on the command line
>
> And when I add those two users, and go to reset passwords, it doesn't
> want to let me specify 'user'@'ip.address' or 'user'@'host.name' but
> just 'user'  I *think* it's resetting the password for both... the
> hashes are always the same.  But I just don't know.
>
> What am I missing?
>
> --
> ***
> * John Oliver                             http://www.john-oliver.net/ *
> *                                                                     *
> ***
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com
>
>



-- 
 - michael dykman
 - mdyk...@gmail.com

"May you live every day of your life."
Jonathan Swift

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Passwords not working

[John Oliver]

I have a problem with MySQL passwords... I set them, write them down...
and they stop working.  I have to go in and manually reset them.

Right now, I have a database that, even after resetting the password, I
still cannot access it.

/var/log/mysql.log doesn't give me any useful information.  How can I
get MySQL to tell me what it's unhappy about, or get more information
from the client other than it just didn't work?

I also have problems with MySQL resolving names, or not resolving names,
or ???  I usually add 'user'@'ip.address' and 'user'@'host.name'  But,
more and more often, I've had to put skip-name-resolve in my.cnf, but
with my current problem, I'm still seeing that 'user'@'host-name' is
being rejected, even when I use -h ip.address on the command line

And when I add those two users, and go to reset passwords, it doesn't
want to let me specify 'user'@'ip.address' or 'user'@'host.name' but
just 'user'  I *think* it's resetting the password for both... the
hashes are always the same.  But I just don't know.

What am I missing?

-- 
***
* John Oliver http://www.john-oliver.net/ *
* *
***

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Password Reset Not Working

[Claudio Nanni]

Exactly, you need as many rows as many combination  user/host

we can also say that an account in MySQL is not the username BUT the  
username AND host combination.


If you want to duplicate any account (also the root/localhost) do this:

mysql> show grants for 'root'@'localhost';

then have fun!

Claudio



Carlos Williams wrote:

On Fri, May 15, 2009 at 3:57 PM, Claudio Nanni  wrote:
  

I don't know if you are now more ore less confused!

Claudio



I would say less because you basically explained that I need to have
localhost & 127.0.0.1.
Now my problem is that no longer have this and would like to know what
I can do to resolve
this by re-adding the 127.0.0.1 host / root user parameter back into MySQL.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select user, host, password from user;
+---++--+
| user  | host   | password |
+---++--+
| root   | host.domain.com  | 032c41e8435273a7 |
| root   | localhost  | 032c41e8435273a7 |
| roundcube  | localhost  | 032c41e8435273a7 |
| mrbs  | localhost  | 6322a1af59897de4 |
| phpbb| localhost  | 5d2e19393cc5ef67 |
+---++--+
5 rows in set (0.00 sec)

  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Password Reset Not Working

[Carlos Williams]

On Fri, May 15, 2009 at 3:57 PM, Claudio Nanni  wrote:
> I don't know if you are now more ore less confused!
>
> Claudio

I would say less because you basically explained that I need to have
localhost & 127.0.0.1.
Now my problem is that no longer have this and would like to know what
I can do to resolve
this by re-adding the 127.0.0.1 host / root user parameter back into MySQL.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select user, host, password from user;
+---++--+
| user  | host   | password |
+---++--+
| root   | host.domain.com  | 032c41e8435273a7 |
| root   | localhost  | 032c41e8435273a7 |
| roundcube  | localhost  | 032c41e8435273a7 |
| mrbs  | localhost  | 6322a1af59897de4 |
| phpbb| localhost  | 5d2e19393cc5ef67 |
+---++--+
5 rows in set (0.00 sec)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Password Reset Not Working

[Claudio Nanni]

It is important to understand deeply mysql client access control.

Basically you need only one root account from the localhost for 
administration purposes.
Keep in mind that when you login specifying 'localhost' (either by the 
-h flag or implicit) MySQL will use the mysql client socket interface,

if you specify '127.0.0.1' it will use the TCP/IP port (3306).
'localhost' is a sort of keyword telling the client to use the unix 
socket file, '127.0.0.1' is bound to the tcp/ip port.


Host matching is always done BEFORE user matching.
MySQL sorts HOSTS with more specific entries on top and less ones on 
bottom ('%' wildcarded entries)
MySQL sorts USERS with more specific entries on top and less ones on 
bottom ('' empty user)


Bottom line, you need a triplet of  USER-HOST-PASS for each account.

having localhost AND 127.0.0.1 it is only related to the different 
connection method (socket , tcpip)

for any other (remote) account tcp-ip will be used.

I don't know if you are now more ore less confused!

Claudio


Carlos Williams wrote:

Can someone please explain why I have 3 entries for root or if this is
normal behavior for MySQL? I thought after a installation of MySQL,
you normally have 2:

1 - localhost
2 - host.domain.com

For some reason I had a 3rd entry:

3 - 127.0.0.1

I don't know if I did the right thing but I ran the following command:

update user set host="localhost" where host='127.0.0.1';

This basically changed the 127.0.0.1 entry in mysql databased, host
section to localhost.

I am not sure if MySQL needs to have the 3rd host entry for root that
equals 127.0.0.1 or if it generally should just have the localhost and
fqdn.

Can anyone please clear this up for me?

  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Password Reset Not Working

[Carlos Williams]

Can someone please explain why I have 3 entries for root or if this is
normal behavior for MySQL? I thought after a installation of MySQL,
you normally have 2:

1 - localhost
2 - host.domain.com

For some reason I had a 3rd entry:

3 - 127.0.0.1

I don't know if I did the right thing but I ran the following command:

update user set host="localhost" where host='127.0.0.1';

This basically changed the 127.0.0.1 entry in mysql databased, host
section to localhost.

I am not sure if MySQL needs to have the 3rd host entry for root that
equals 127.0.0.1 or if it generally should just have the localhost and
fqdn.

Can anyone please clear this up for me?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Password Reset Not Working

[Carlos Williams]

On Thu, May 14, 2009 at 7:01 PM, Douglas Nelson  wrote:
> try running the command like this
>
> select * from user where user='root' \G
>
> Capital G is a must.

I did the following:

[r...@mysql ~]# /etc/init.d/mysqld stop
Stopping MySQL:[  OK  ]

[r...@mysql ~]# mysqld_safe --skip-grant-tables &
[1] 3072 Starting mysqld daemon with databases from /var/lib/mysql

[r...@mysql ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45 Source distribution

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

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from user where user='root' \G
*** 1. row ***
 Host: mysql.unixslut.com
 User: root
 Password: 6d24bd789879jhs
  Select_priv: Y
  Insert_priv: Y
  Update_priv: Y
  Delete_priv: Y
  Create_priv: Y
Drop_priv: Y
  Reload_priv: Y
Shutdown_priv: Y
 Process_priv: Y
File_priv: Y
   Grant_priv: Y
  References_priv: Y
   Index_priv: Y
   Alter_priv: Y
 Show_db_priv: Y
   Super_priv: Y
Create_tmp_table_priv: Y
 Lock_tables_priv: Y
 Execute_priv: Y
  Repl_slave_priv: Y
 Repl_client_priv: Y
 Create_view_priv: Y
   Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
 Create_user_priv: Y
 ssl_type:
   ssl_cipher:
  x509_issuer:
 x509_subject:
max_questions: 0
  max_updates: 0
  max_connections: 0
 max_user_connections: 0
*** 2. row ***
 Host: 127.0.0.1
 User: root
 Password: 6d24bd789879jhs
  Select_priv: Y
  Insert_priv: Y
  Update_priv: Y
  Delete_priv: Y
  Create_priv: Y
Drop_priv: Y
  Reload_priv: Y
Shutdown_priv: Y
 Process_priv: Y
File_priv: Y
   Grant_priv: Y
  References_priv: Y
   Index_priv: Y
   Alter_priv: Y
 Show_db_priv: Y
   Super_priv: Y
Create_tmp_table_priv: Y
 Lock_tables_priv: Y
 Execute_priv: Y
  Repl_slave_priv: Y
 Repl_client_priv: Y
 Create_view_priv: Y
   Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
 Create_user_priv: Y
 ssl_type:
   ssl_cipher:
  x509_issuer:
 x509_subject:
max_questions: 0
  max_updates: 0
  max_connections: 0
 max_user_connections: 0
2 rows in set (0.00 sec)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Password Reset Not Working

[Carlos Williams]

On Thu, May 14, 2009 at 7:06 PM, Carlos Proal  wrote:
>
> The machine  mysql.unixslut.com is not the same than localhost, right ??,
> If you only need root access from localhost you can delete the first row
> (delete from user where user='root' and host='mysql.unixslut.com';)

Carlos,

Yes, my machine  is localhost / 127.0.0.1/8.
They're both the same machine. It's just that I was told MySQL manages
connection for users on a local / domain basis so that is the reason
for both entries. In my opinion, both entries are valid. I could be
wrong.

**No, the server has not been hacked**

Thanks for your continued support!]

- Carlos

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Password Reset Not Working

[PJ]

Carlos Proal wrote:
>
> The machine  mysql.unixslut.com is not the same than localhost, right ??,
> If you only need root access from localhost you can delete the first
> row (delete from user where user='root' and host='mysql.unixslut.com';)
>
>
> Carlos
>
>
> On 5/14/2009 5:55 PM, Carlos Williams wrote:
>> On Thu, May 14, 2009 at 6:44 PM, Carlos Proal
>>  wrote:
>>  
>>> Check how many root rows do you have on the user table (select *
>>> from user
>>> where user='root';), some times there are several rows with
>>> different grants
>>> and probably you are going through and invalid rule.
>>> 
>>
>> I checked and when I ran the command you suggested:
>>
>> mysql> use mysql;
>> Database changed
>>
>> mysql> select * from user where user='root';
>>
>> I get a bunch of gibberish on the screen but the only thing I can make
>> out are two entries for root:
>>
>> | mysql.unixslut.com | root | 6d21bd9609b168e4 | Y   |
>> Y   | Y
>> | 127.0.0.1| root | 6d21bd9609b168e4 | Y   | Y
>>   | Y
>>
>> So what does this mean and how can I resolve this? I am trying this
>> from the machine locally so I would assume localhost works fine...
>>
>>  
Pardon, for butting in, but are you seriou? unix slut ? My first
impression based on that would be, man you've been hacked! :-D

-- 
Hervé Kempf: "Pour sauver la planète, sortez du capitalisme."
-
Phil Jourdan --- p...@ptahhotep.com
   http://www.ptahhotep.com
   http://www.chiccantine.com/andypantry.php


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Password Reset Not Working

[Carlos Proal]

The machine  mysql.unixslut.com is not the same than localhost, right ??,
If you only need root access from localhost you can delete the first row 
(delete from user where user='root' and host='mysql.unixslut.com';)



Carlos


On 5/14/2009 5:55 PM, Carlos Williams wrote:

On Thu, May 14, 2009 at 6:44 PM, Carlos Proal  wrote:
  

Check how many root rows do you have on the user table (select * from user
where user='root';), some times there are several rows with different grants
and probably you are going through and invalid rule.



I checked and when I ran the command you suggested:

mysql> use mysql;
Database changed

mysql> select * from user where user='root';

I get a bunch of gibberish on the screen but the only thing I can make
out are two entries for root:

| mysql.unixslut.com | root | 6d21bd9609b168e4 | Y   | Y   | Y
| 127.0.0.1| root | 6d21bd9609b168e4 | Y   | Y
  | Y

So what does this mean and how can I resolve this? I am trying this
from the machine locally so I would assume localhost works fine...

  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Password Reset Not Working

[Douglas Nelson]

try running the command like this

select * from user where user='root' \G

Capital G is a must.

thanks Doug



Carlos Williams wrote:

On Thu, May 14, 2009 at 6:44 PM, Carlos Proal  wrote:
  

Check how many root rows do you have on the user table (select * from user
where user='root';), some times there are several rows with different grants
and probably you are going through and invalid rule.



I checked and when I ran the command you suggested:

mysql> use mysql;
Database changed

mysql> select * from user where user='root';

I get a bunch of gibberish on the screen but the only thing I can make
out are two entries for root:

| mysql.unixslut.com | root | 6d21bd9609b168e4 | Y   | Y   | Y
| 127.0.0.1| root | 6d21bd9609b168e4 | Y   | Y
  | Y

So what does this mean and how can I resolve this? I am trying this
from the machine locally so I would assume localhost works fine...

  


--
 * Douglas Nelson *
Senior Technical Consultant

*Need Sun JES Help?*
Software Field Practice
Phone +1 877-234-2879/x51438
Mobile 919-259-3719
Email sfp-h...@sun.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Password Reset Not Working

[Carlos Williams]

On Thu, May 14, 2009 at 6:44 PM, Carlos Proal  wrote:
>
> Check how many root rows do you have on the user table (select * from user
> where user='root';), some times there are several rows with different grants
> and probably you are going through and invalid rule.

I checked and when I ran the command you suggested:

mysql> use mysql;
Database changed

mysql> select * from user where user='root';

I get a bunch of gibberish on the screen but the only thing I can make
out are two entries for root:

| mysql.unixslut.com | root | 6d21bd9609b168e4 | Y   | Y   | Y
| 127.0.0.1| root | 6d21bd9609b168e4 | Y   | Y
  | Y

So what does this mean and how can I resolve this? I am trying this
from the machine locally so I would assume localhost works fine...

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Password Reset Not Working

[Carlos Proal]

Check how many root rows do you have on the user table (select * from 
user where user='root';), some times there are several rows with 
different grants and probably you are going through and invalid rule.


Carlos

On 5/14/2009 5:39 PM, Carlos Williams wrote:

On Thu, May 14, 2009 at 6:31 PM, Carlos Proal  wrote:
  

Hi Carlos

Try this

mysql>  update user set password=password('letmein') where user='root';


This way the password is saved encrypted, thats the way is compared when you
try to log in.



Thanks for the reply! I followed both methods in the following URL / link:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-unix

After doing both successfully, I was unable to login over and over. I
think something is wrong with MySQL. I have never seen this before :(

  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Password Reset Not Working

[Carlos Williams]

On Thu, May 14, 2009 at 6:31 PM, Carlos Proal  wrote:
>
> Hi Carlos
>
> Try this
>
> mysql>  update user set password=password('letmein') where user='root';
>
>
> This way the password is saved encrypted, thats the way is compared when you
> try to log in.

Thanks for the reply! I followed both methods in the following URL / link:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-unix

After doing both successfully, I was unable to login over and over. I
think something is wrong with MySQL. I have never seen this before :(

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Password Reset Not Working

[Carlos Proal]

Hi Carlos

Try this

mysql>  update user set password=password('letmein') where user='root';


This way the password is saved encrypted, thats the way is compared when 
you try to log in.


Carlos

On 5/14/2009 5:28 PM, Carlos Williams wrote:

I noticed today that I strangely was unable to login to MySQL as root.
I just assumed I forgot the password and decided to reset my root
password:

1 - /etc/init.d/mysqld stop

2 - mysqld_safe --skip-grant-tables &

3 - mysql -u root

4 - mysql> use mysql;

mysql> mysql> update user set password='letmein' where user='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> flush privileges;

mysql> quit

5 - /etc/init.d/mysqld restart

*Now after I do all that, I get the following:*

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

I have done this over and over and can't login so I am wondering if
something is wrong with MySQL or am I just not properly resetting the
password...

Someone please help!

  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Password Reset Not Working

[Carlos Williams]

I noticed today that I strangely was unable to login to MySQL as root.
I just assumed I forgot the password and decided to reset my root
password:

1 - /etc/init.d/mysqld stop

2 - mysqld_safe --skip-grant-tables &

3 - mysql -u root

4 - mysql> use mysql;

mysql> mysql> update user set password='letmein' where user='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> flush privileges;

mysql> quit

5 - /etc/init.d/mysqld restart

*Now after I do all that, I get the following:*

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

I have done this over and over and can't login so I am wondering if
something is wrong with MySQL or am I just not properly resetting the
password...

Someone please help!

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

MySQL Installation Not Working

[DPD]

Hi,
I've tried several times now to get a local installation of MySQL  
going on my iMac (w/ OS X ver. 10.5.6).   I've carefully tried  
everything I could comprehend to do per the mysql.com resource pages/ 
manual.   I have also tried several procedures I discovered via Google  
searches that seemed to be promising/authoritative.   Throughout this  
process I have encountered so many obstacles I've lost count.  I have  
deleted and reinstalled the whole MySQL package (mysql-5.1.32-osx10.5- 
x86.dmg)
4 times while trying to follow the documentation with excruciating  
attention to the detail and nuances.  I think I have encountered every  
version of 'permission denied', 'unable to open socket', unable to  
find 'my_print_defaults', 'unable to bind on port 3306', 'unable to  
access pid file' type of error possible.  I am absolutely drowning in  
barely comprehended documentation, tips and tricks  that have taken me  
down one dead end after another.   Is there any way an ordinary person  
who doesn't walk on water with UNIX and is not able to manufacture a  
supercomputer with his pocketknife can get a simple 'local' MySQL  
installation going on his Mac?  This is an appeal for help from  
someone who has wrestled with the official documentation to the point  
that it's liable to become a textbook case of 'terminal computer- 
frustration'.


I wasn't able to figure out exactly how to comply with your 'MUST use  
the ./bin/mysqlbug script!' directive so I've copied and pasted the  
results of running that script below (please accept my apologies if  
these results were supposed to be transmitted in a different way).



SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and `>').
SEND-PR:
From: DPD
To: mysql@lists.mysql.com
Subject: [50 character or so descriptive subject here (for reference)]

>Description:

>How-To-Repeat:
lines)>

>Fix:
(multiple lines)>


>Submitter-Id:  
>Originator:
>Organization:
 
>MySQL support: [none | licence | email support | extended email  
support ]

>Synopsis:  
>Severity:  <[ non-critical | serious | critical ] (one line)>
>Priority:  <[ low | medium | high ] (one line)>
>Category:  mysql
>Class: <[ sw-bug | doc-bug | change-request | support ] (one  
line)>

>Release:   mysql-5.1.32 (MySQL Community Server (GPL))

>C compiler:i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc.  
build 5465)
>C++ compiler:  i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc.  
build 5465)

>Environment:

System: Darwin DPD-Mac.local 9.6.0 Darwin Kernel Version 9.6.0: Mon  
Nov 24 17:37\

:00 PST 2008; root:xnu-1228.9.59~1/RELEASE_I386 i386


Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Using built-in specs.
Target: i686-apple-darwin9
Configured with: /var/tmp/gcc/gcc-5490~1/src/configure --disable- 
checking -enabl\
e-werror --prefix=/usr --mandir=/share/man --enable-languages=c,objc,c+ 
+,obj-c++\
 --program-transform-name=/^[cg][^.-]*$/s/$/-4.0/ --with-gxx-include- 
dir=/includ\
e/c++/4.0.0 --with-slibdir=/usr/lib --build=i686-apple-darwin9 --with- 
arch=apple\
 --with-tune=generic --host=i686-apple-darwin9 --target=i686-apple- 
darwin9

Thread model: posix
gcc version 4.0.1 (Apple Inc. build 5490)
**
Regards,
Dennis

Re: Install problem: configure --prefix not working

[Walter Heck]

/var/lib/mysql is the standard loation for the data_file_path, the
data directory. change it in my.cnf.

var/log is the default location for log files, also changeable in the
config file.

Walter

OlinData: Professional services for MySQL
Support * Consulting * Administration
http://www.olindata.com



On Wed, Feb 11, 2009 at 5:35 PM, csego...@gmail.com  wrote:
> I am trying to install mysql in a non-standard directory - specifically
> /app/mysql
>
> Subsequently, I ran the following configure command:
>
> ./configure --prefix=/app/mysql --with-plugins=innobase,myisam
>
> There were no problems with the compile, but mysql failed to start.
> Furthermore, the following output was produced in the nohup.out file
>
> -bash-3.2$ cat nohup.out
> 090211 09:40:35 mysqld_safe Logging to '/var/log/mysqld.log'.
> mkdir: cannot create directory `/var/lib/mysql': Permission denied
> chown: cannot access `/var/lib/mysql': No such file or directory
> chmod: cannot access `/var/lib/mysql': No such file or directory
> 090211 09:40:35 mysqld_safe Starting mysqld daemon with databases from
> /var/lib/mysql
> /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied
> /app/mysql/bin/mysqld_safe: line 133: /var/log/mysqld.log: Permission denied
> 090211 09:40:35 mysqld_safe mysqld from pid file
> /var/run/mysqld/mysqld.pid ended
> /app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied
>
> For some reason, the mysql install is looking to /var even though I
> specificied /app/mysql in the compile.
>
> How do I fix this?
>
> Thanks!
> Colin
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@olindata.com
>
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Install problem: configure --prefix not working

[csego...@gmail.com]

I am trying to install mysql in a non-standard directory - specifically
/app/mysql

Subsequently, I ran the following configure command:

./configure --prefix=/app/mysql --with-plugins=innobase,myisam

There were no problems with the compile, but mysql failed to start.
Furthermore, the following output was produced in the nohup.out file

-bash-3.2$ cat nohup.out
090211 09:40:35 mysqld_safe Logging to '/var/log/mysqld.log'.
mkdir: cannot create directory `/var/lib/mysql': Permission denied
chown: cannot access `/var/lib/mysql': No such file or directory
chmod: cannot access `/var/lib/mysql': No such file or directory
090211 09:40:35 mysqld_safe Starting mysqld daemon with databases from
/var/lib/mysql
/app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied
/app/mysql/bin/mysqld_safe: line 133: /var/log/mysqld.log: Permission denied
090211 09:40:35 mysqld_safe mysqld from pid file
/var/run/mysqld/mysqld.pid ended
/app/mysql/bin/mysqld_safe: line 96: /var/log/mysqld.log: Permission denied

For some reason, the mysql install is looking to /var even though I
specificied /app/mysql in the compile.

How do I fix this?

Thanks!
Colin

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: % wildcard host permission not working

[Fish Kungfu]

Hmmm, that is strange indeed.  Hopefully one of the Level 70+ MySQL Wizards
on the list can explain why this might happen.

~~Fish~~



On Sat, Oct 25, 2008 at 4:44 PM, Joe Auty <[EMAIL PROTECTED]> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Fish Kungfu wrote:
> | I think I see the typo.  If that's an exact copy & paste of your GRANT
> | statement, then the problem is the spaces in the database designation:
> |
> | You have:  GRANT ALL PRIVILEGES ON * . * TOetc
> | But it should be: GRANT ALL PRIVILEGES ON *.* TOetc
> |
> | There shouldn't be any spaces in the *.* part.  I hope this fixes it for
> | you.
> |
>
> I just setup the MySQL client on another machine, and was able to
> connect just fine... Very odd. So:
>
> - - local connections as that user: fine
>
> - - connections from mydomain.com as that user: broken
>
> - - connections from mydomain.com as that user where is a specific MySQL
> user permitting the FQDN of mydomain.com: fine
>
> - - connections from newserver.mydomain.com (the aforementioned machine I
> just setup): fine
>
>
> It's almost as if the local privileges data on mydomain.com is
> conflicting or something? There was no data at all on
> newserver.mydomain.com - it was a complete virgin install.
>
>
>
>
>
>
> | CheersFish
> | 
> | ~ Second Life addict
> |
> |
> | On Sat, Oct 25, 2008 at 3:15 PM, Joe Auty <[EMAIL PROTECTED]> wrote:
> |
> |> Fish Kungfu wrote:
> |>
> |>> Would you mind posting what you tried?  Did you put single quotes
> around
> |>> the
> |>> wildcard like '%' ?
> |>>
> |>>For example:  GRANT ALL ON somedb.* TO 'someuser'@'%';
> |>>
> |>> Also see http://dev.mysql.com/doc/refman/5.0/en/grant.html
> |>>
> |>> CheersFish
> |>>
> |>>
> |> Sure!
> |>
> |> On what I'll call "mynewserver"
> |>
> |> CREATE USER 'testuser'@'%' IDENTIFIED BY '***';
> |>
> |> GRANT ALL PRIVILEGES ON * . * TO 'testuser'@'%' IDENTIFIED BY '***'
> WITH
> |> GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0
> |> MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
> |>
> |>
> |> FLUSH PRIVILEGES ;
> |>
> |>
> |> $ mysql -h mynewserver -u testuser -p
> |> Enter password: 
> |> ERROR 1045 (28000): Access denied for user 'testuser'@'myoldserver'
> (using
> |> password: YES)
> |>
> |>
> |>
> |>
> |>
> |>
> |>> On Sat, Oct 25, 2008 at 1:40 PM, Joe Auty <[EMAIL PROTECTED]> wrote:
> |>>
> |>>  I'm also having this strange problem just as stated here, anybody
> find a
> |>>> fix for this yet?
> |>>>
> |>>> Again, using wildcards (%) for the Host don't work while the FQDN does
> |>>> from
> |>>> the exact same remote machine, the exact same username, etc. There is
> |>>> something about the wildcard ACL that is not working properly for
> this or
> |>>> any other user. I have proper forward and reverse DNS entries for this
> |>>> machine I'm connecting from.
> |>>>
> |>>> Any ideas why this might be and where I ought to look?
> |>>>
> |>>>
> |>>>  Yes, I did "FLUSH PRIVILEGES" and I think I only have one
> username/host
> |>>>
> |>>>> entry for this user.
> |>>>>
> |>>>> Adam
> |>>>>
> |>>>>
> |>>>> Erik Giberti wrote:
> |>>>>
> |>>>>  Did you "FLUSH PRIVILEGES"?
> |>>>>> I'd also check that the username and passwords are the same for each
> |>>>>> host
> |>>>>> entry, I've had problems if passwords were different for a shared
> |>>>>> username
> |>>>>> from different hosts.
> |>>>>>
> |>>>>> On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote:
> |>>>>>
> |>>>>>  I set up a user and entered % for the host. I am not able to
> connect.
> |>>>>>
> |>>>>>> However, if I change the host value to my FQDN it works fine.
> |>>>>>> Shouldn't the
> |>>>>>> wildcard allow me to connect from any host?
> |>>>>>>
> |>>>>>> Thanks,
> |>>>>>> Ad

Re: % wildcard host permission not working

[Fish Kungfu]

I think I see the typo.  If that's an exact copy & paste of your GRANT
statement, then the problem is the spaces in the database designation:

You have:  GRANT ALL PRIVILEGES ON * . * TOetc
But it should be: GRANT ALL PRIVILEGES ON *.* TOetc

There shouldn't be any spaces in the *.* part.  I hope this fixes it for
you.

CheersFish

~ Second Life addict


On Sat, Oct 25, 2008 at 3:15 PM, Joe Auty <[EMAIL PROTECTED]> wrote:

> Fish Kungfu wrote:
>
>> Would you mind posting what you tried?  Did you put single quotes around
>> the
>> wildcard like '%' ?
>>
>>For example:  GRANT ALL ON somedb.* TO 'someuser'@'%';
>>
>> Also see http://dev.mysql.com/doc/refman/5.0/en/grant.html
>>
>> CheersFish
>>
>>
> Sure!
>
> On what I'll call "mynewserver"
>
> CREATE USER 'testuser'@'%' IDENTIFIED BY '***';
>
> GRANT ALL PRIVILEGES ON * . * TO 'testuser'@'%' IDENTIFIED BY '***' WITH
> GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0
> MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
>
>
> FLUSH PRIVILEGES ;
>
>
> $ mysql -h mynewserver -u testuser -p
> Enter password: 
> ERROR 1045 (28000): Access denied for user 'testuser'@'myoldserver' (using
> password: YES)
>
>
>
>
>
>
>>
>> On Sat, Oct 25, 2008 at 1:40 PM, Joe Auty <[EMAIL PROTECTED]> wrote:
>>
>>  I'm also having this strange problem just as stated here, anybody find a
>>> fix for this yet?
>>>
>>> Again, using wildcards (%) for the Host don't work while the FQDN does
>>> from
>>> the exact same remote machine, the exact same username, etc. There is
>>> something about the wildcard ACL that is not working properly for this or
>>> any other user. I have proper forward and reverse DNS entries for this
>>> machine I'm connecting from.
>>>
>>> Any ideas why this might be and where I ought to look?
>>>
>>>
>>>  Yes, I did "FLUSH PRIVILEGES" and I think I only have one username/host
>>>
>>>> entry for this user.
>>>>
>>>> Adam
>>>>
>>>>
>>>> Erik Giberti wrote:
>>>>
>>>>  Did you "FLUSH PRIVILEGES"?
>>>>> I'd also check that the username and passwords are the same for each
>>>>> host
>>>>> entry, I've had problems if passwords were different for a shared
>>>>> username
>>>>> from different hosts.
>>>>>
>>>>> On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote:
>>>>>
>>>>>  I set up a user and entered % for the host. I am not able to connect.
>>>>>
>>>>>> However, if I change the host value to my FQDN it works fine.
>>>>>> Shouldn't the
>>>>>> wildcard allow me to connect from any host?
>>>>>>
>>>>>> Thanks,
>>>>>> Adam
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Adam Gerson
>>>>>> Assistant Director of Technology
>>>>>> Apple Certified System Administrator (ACSA)
>>>>>> Columbia Grammar and Prep School
>>>>>> phone. 212-749-6200 ex. 321
>>>>>> fax.  212-428-6806
>>>>>> [EMAIL PROTECTED]
>>>>>> http://www.cgps.org
>>>>>> Public key - subkeys.pgp.net
>>>>>>
>>>>>>
>>>>>> --
>>>>>> MySQL General Mailing List
>>>>>> For list archives: http://lists.mysql.com/mysql
>>>>>> To unsubscribe:http://lists.mysql.com/mysql?unsub=1
>>>>>>
>>>>>>
>>>>>>
>>>>>  --
>>>> Adam Gerson
>>>> Assistant Director of Technology
>>>> Apple Certified System Administrator (ACSA)
>>>> Columbia Grammar and Prep School
>>>> phone. 212-749-6200 ex. 321
>>>> fax.  212-428-6806
>>>> [EMAIL PROTECTED]
>>>> http://www.cgps.org
>>>> Public key - subkeys.pgp.net
>>>>
>>>>
>>>
>>> --
>>> Joe Auty
>>> NetMusician: web publishing software for musicians
>>> http://www.netmusician.org
>>> [EMAIL PROTECTED]
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> http://lists.mysql.com/[EMAIL PROTECTED]
>>>
>>>
>>>
>>
>
> --
> Joe Auty
> NetMusician: web publishing software for musicians
> http://www.netmusician.org
> [EMAIL PROTECTED]
>

Re: % wildcard host permission not working

[Fish Kungfu]

I think I see the typo.  If that's an exact copy & paste of your GRANT
statement, then the problem is the spaces in the database designation:

You have:  GRANT ALL PRIVILEGES ON * . * TOetc
But it should be: GRANT ALL PRIVILEGES ON *.* TOetc

There shouldn't be any spaces in the *.* part.  I hope this fixes it for
you.

CheersFish

~ Second Life addict


On Sat, Oct 25, 2008 at 3:15 PM, Joe Auty <[EMAIL PROTECTED]> wrote:

> Fish Kungfu wrote:
>
>> Would you mind posting what you tried?  Did you put single quotes around
>> the
>> wildcard like '%' ?
>>
>>For example:  GRANT ALL ON somedb.* TO 'someuser'@'%';
>>
>> Also see http://dev.mysql.com/doc/refman/5.0/en/grant.html
>>
>> CheersFish
>>
>>
> Sure!
>
> On what I'll call "mynewserver"
>
> CREATE USER 'testuser'@'%' IDENTIFIED BY '***';
>
> GRANT ALL PRIVILEGES ON * . * TO 'testuser'@'%' IDENTIFIED BY '***' WITH
> GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0
> MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
>
>
> FLUSH PRIVILEGES ;
>
>
> $ mysql -h mynewserver -u testuser -p
> Enter password: 
> ERROR 1045 (28000): Access denied for user 'testuser'@'myoldserver' (using
> password: YES)
>
>
>
>
>
>
>>
>> On Sat, Oct 25, 2008 at 1:40 PM, Joe Auty <[EMAIL PROTECTED]> wrote:
>>
>>  I'm also having this strange problem just as stated here, anybody find a
>>> fix for this yet?
>>>
>>> Again, using wildcards (%) for the Host don't work while the FQDN does
>>> from
>>> the exact same remote machine, the exact same username, etc. There is
>>> something about the wildcard ACL that is not working properly for this or
>>> any other user. I have proper forward and reverse DNS entries for this
>>> machine I'm connecting from.
>>>
>>> Any ideas why this might be and where I ought to look?
>>>
>>>
>>>  Yes, I did "FLUSH PRIVILEGES" and I think I only have one username/host
>>>
>>>> entry for this user.
>>>>
>>>> Adam
>>>>
>>>>
>>>> Erik Giberti wrote:
>>>>
>>>>  Did you "FLUSH PRIVILEGES"?
>>>>> I'd also check that the username and passwords are the same for each
>>>>> host
>>>>> entry, I've had problems if passwords were different for a shared
>>>>> username
>>>>> from different hosts.
>>>>>
>>>>> On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote:
>>>>>
>>>>>  I set up a user and entered % for the host. I am not able to connect.
>>>>>
>>>>>> However, if I change the host value to my FQDN it works fine.
>>>>>> Shouldn't the
>>>>>> wildcard allow me to connect from any host?
>>>>>>
>>>>>> Thanks,
>>>>>> Adam
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Adam Gerson
>>>>>> Assistant Director of Technology
>>>>>> Apple Certified System Administrator (ACSA)
>>>>>> Columbia Grammar and Prep School
>>>>>> phone. 212-749-6200 ex. 321
>>>>>> fax.  212-428-6806
>>>>>> [EMAIL PROTECTED]
>>>>>> http://www.cgps.org
>>>>>> Public key - subkeys.pgp.net
>>>>>>
>>>>>>
>>>>>> --
>>>>>> MySQL General Mailing List
>>>>>> For list archives: http://lists.mysql.com/mysql
>>>>>> To unsubscribe:http://lists.mysql.com/mysql?unsub=1
>>>>>>
>>>>>>
>>>>>>
>>>>>  --
>>>> Adam Gerson
>>>> Assistant Director of Technology
>>>> Apple Certified System Administrator (ACSA)
>>>> Columbia Grammar and Prep School
>>>> phone. 212-749-6200 ex. 321
>>>> fax.  212-428-6806
>>>> [EMAIL PROTECTED]
>>>> http://www.cgps.org
>>>> Public key - subkeys.pgp.net
>>>>
>>>>
>>>
>>> --
>>> Joe Auty
>>> NetMusician: web publishing software for musicians
>>> http://www.netmusician.org
>>> [EMAIL PROTECTED]
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> http://lists.mysql.com/[EMAIL PROTECTED]
>>>
>>>
>>>
>>
>
> --
> Joe Auty
> NetMusician: web publishing software for musicians
> http://www.netmusician.org
> [EMAIL PROTECTED]
>

Re: % wildcard host permission not working

[Joe Auty]

Fish Kungfu wrote:

Would you mind posting what you tried?  Did you put single quotes around the
wildcard like '%' ?

For example:  GRANT ALL ON somedb.* TO 'someuser'@'%';

Also see http://dev.mysql.com/doc/refman/5.0/en/grant.html

CheersFish



Sure!

On what I'll call "mynewserver"

CREATE USER 'testuser'@'%' IDENTIFIED BY '***';

GRANT ALL PRIVILEGES ON * . * TO 'testuser'@'%' IDENTIFIED BY '***' WITH 
GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 
MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;



FLUSH PRIVILEGES ;


$ mysql -h mynewserver -u testuser -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'testuser'@'myoldserver' 
(using password: YES)








On Sat, Oct 25, 2008 at 1:40 PM, Joe Auty <[EMAIL PROTECTED]> wrote:


I'm also having this strange problem just as stated here, anybody find a
fix for this yet?

Again, using wildcards (%) for the Host don't work while the FQDN does from
the exact same remote machine, the exact same username, etc. There is
something about the wildcard ACL that is not working properly for this or
any other user. I have proper forward and reverse DNS entries for this
machine I'm connecting from.

Any ideas why this might be and where I ought to look?


 Yes, I did "FLUSH PRIVILEGES" and I think I only have one username/host

entry for this user.

Adam


Erik Giberti wrote:


Did you "FLUSH PRIVILEGES"?
I'd also check that the username and passwords are the same for each host
entry, I've had problems if passwords were different for a shared username
from different hosts.

On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote:

 I set up a user and entered % for the host. I am not able to connect.

However, if I change the host value to my FQDN it works fine. Shouldn't the
wildcard allow me to connect from any host?

Thanks,
Adam


--
Adam Gerson
Assistant Director of Technology
Apple Certified System Administrator (ACSA)
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax.  212-428-6806
[EMAIL PROTECTED]
http://www.cgps.org
Public key - subkeys.pgp.net


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





--
Adam Gerson
Assistant Director of Technology
Apple Certified System Administrator (ACSA)
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax.  212-428-6806
[EMAIL PROTECTED]
http://www.cgps.org
Public key - subkeys.pgp.net




--
Joe Auty
NetMusician: web publishing software for musicians
http://www.netmusician.org
[EMAIL PROTECTED]

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







--
Joe Auty
NetMusician: web publishing software for musicians
http://www.netmusician.org
[EMAIL PROTECTED]

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

Re: % wildcard host permission not working

[Fish Kungfu]

Would you mind posting what you tried?  Did you put single quotes around the
wildcard like '%' ?

For example:  GRANT ALL ON somedb.* TO 'someuser'@'%';

Also see http://dev.mysql.com/doc/refman/5.0/en/grant.html

CheersFish



On Sat, Oct 25, 2008 at 1:40 PM, Joe Auty <[EMAIL PROTECTED]> wrote:

> I'm also having this strange problem just as stated here, anybody find a
> fix for this yet?
>
> Again, using wildcards (%) for the Host don't work while the FQDN does from
> the exact same remote machine, the exact same username, etc. There is
> something about the wildcard ACL that is not working properly for this or
> any other user. I have proper forward and reverse DNS entries for this
> machine I'm connecting from.
>
> Any ideas why this might be and where I ought to look?
>
>
>  Yes, I did "FLUSH PRIVILEGES" and I think I only have one username/host
>> entry for this user.
>>
>> Adam
>>
>>
>> Erik Giberti wrote:
>>
>>> Did you "FLUSH PRIVILEGES"?
>>> I'd also check that the username and passwords are the same for each host
>>> entry, I've had problems if passwords were different for a shared username
>>> from different hosts.
>>>
>>> On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote:
>>>
>>>  I set up a user and entered % for the host. I am not able to connect.
>>>> However, if I change the host value to my FQDN it works fine. Shouldn't the
>>>> wildcard allow me to connect from any host?
>>>>
>>>> Thanks,
>>>> Adam
>>>>
>>>>
>>>> --
>>>> Adam Gerson
>>>> Assistant Director of Technology
>>>> Apple Certified System Administrator (ACSA)
>>>> Columbia Grammar and Prep School
>>>> phone. 212-749-6200 ex. 321
>>>> fax.  212-428-6806
>>>> [EMAIL PROTECTED]
>>>> http://www.cgps.org
>>>> Public key - subkeys.pgp.net
>>>>
>>>>
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe:http://lists.mysql.com/mysql?unsub=1
>>>>
>>>>
>>>
>>>
>>
>> --
>> Adam Gerson
>> Assistant Director of Technology
>> Apple Certified System Administrator (ACSA)
>> Columbia Grammar and Prep School
>> phone. 212-749-6200 ex. 321
>> fax.  212-428-6806
>> [EMAIL PROTECTED]
>> http://www.cgps.org
>> Public key - subkeys.pgp.net
>>
>
>
>
> --
> Joe Auty
> NetMusician: web publishing software for musicians
> http://www.netmusician.org
> [EMAIL PROTECTED]
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>

Re: % wildcard host permission not working

[Joe Auty]

I'm also having this strange problem just as stated here, anybody find a 
fix for this yet?


Again, using wildcards (%) for the Host don't work while the FQDN does 
from the exact same remote machine, the exact same username, etc. There 
is something about the wildcard ACL that is not working properly for 
this or any other user. I have proper forward and reverse DNS entries 
for this machine I'm connecting from.


Any ideas why this might be and where I ought to look?


Yes, I did "FLUSH PRIVILEGES" and I think I only have one username/host 
entry for this user.


Adam


Erik Giberti wrote:

Did you "FLUSH PRIVILEGES"?
I'd also check that the username and passwords are the same for each 
host entry, I've had problems if passwords were different for a shared 
username from different hosts.


On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote:

I set up a user and entered % for the host. I am not able to connect. 
However, if I change the host value to my FQDN it works fine. 
Shouldn't the wildcard allow me to connect from any host?


Thanks,
Adam


--
Adam Gerson
Assistant Director of Technology
Apple Certified System Administrator (ACSA)
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax.  212-428-6806
[EMAIL PROTECTED]
http://www.cgps.org
Public key - subkeys.pgp.net


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







--
Adam Gerson
Assistant Director of Technology
Apple Certified System Administrator (ACSA)
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax.  212-428-6806
[EMAIL PROTECTED]
http://www.cgps.org
Public key - subkeys.pgp.net




--
Joe Auty
NetMusician: web publishing software for musicians
http://www.netmusician.org
[EMAIL PROTECTED]

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

Re: create view not working

[Martijn Tonies]

Aaron,

> why doesn't this work? the select works perfectly

Define "doesn't work"? What is the error message?

> create view cost_report as
> SELECT c_name, d_manuf as Manufacturer, d_model as Model, count(1) as
> "Number Enrolled",
> d_price as "Monthly Price", count(1)*d_price as "Cost"
> FROM `b_devices` A
> left join b_device_types B
> on A.d_id = B.d_id
> left join b_prices C
> on A.d_id = C.d_id AND A.c_id = C.c_id
> left join b_company D
> on A.c_id = D.c_id
> group by A.d_id, A.c_id
> order by c_name

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


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

create view not working

[Aaron Ryden]

why doesn't this work? the select works perfectly

create view cost_report as
SELECT c_name, d_manuf as Manufacturer, d_model as Model, count(1) as 
"Number Enrolled",

d_price as "Monthly Price", count(1)*d_price as "Cost"
FROM `b_devices` A
left join b_device_types B
on A.d_id = B.d_id
left join b_prices C
on A.d_id = C.d_id AND A.c_id = C.c_id
left join b_company D
on A.c_id = D.c_id
group by A.d_id, A.c_id
order by c_name


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

Re: Function Still Not Working

[Jesse]

you not ADD Binary, you need to "remove" BINARY ... ;-)


Sorry, I misunderstood.


convert the string to latin1 or utf8 o.s.s.

"LOWER() (and UPPER()) are ineffective when applied to binary strings 
(BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the 
string to a non-binary string"


did you tried?


Well, I thought I had tried this, but with all the other things that I'd 
tried, I guess I had everything mixed up. I started with a clean routine, 
used Convert(... using UTF8), and it works perfectly now. That's what it 
was.


Thanks for the help!

Jesse 



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

RE: Function Still Not Working

[Boyd, Todd M.]

> -Original Message-
> From: Jesse [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 12, 2008 1:40 PM
> To: Martijn Tonies; MySQL List
> Subject: Re: Function Still Not Working
> 
> > Any difference in default collation?
> 
> Not sure what that is.  I'm using a visual tool (EMS) to create my
> function,
> and it doesn't offer that option.  I could update it using the command
> prompt, however.  I may try that later.

I think what he means is... in one instance of the function, the data is
collated as latin-iso-blahblah, perhaps, and a different collation (one
without case sensitivity, eh?) in the other table...

As I don't deal with letters/characters outside of the 'standard'
Latin-iso-asdfasdf collation, I'm afraid there's not much else I can
explain using my limited knowledge.

Hopefully, though, that helped to give you an idea of what he was
driving at. ;)


Todd Boyd
Web Programmer




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

Re: Function Still Not Working

[Jesse]

does it work outside the function?


Yes, If I run:
select CONCAT(UPPER(SUBSTRING('JESSE',1,1)),LOWER(SUBSTRING('JESSE',2)));

replacing cInput with 'JESSE', it returns "Jesse" as it should.


did you tried SUBSTRING(cInput, 2)?


Tried replacing SUBSTRING(cInput FROM 2) with SUBSTRING(cInput, 2) and it 
didn't make any difference.



did you tried with converting?


I have had issues with this in other areas before, but didn't think about it 
this time. However, I tred CONVERT with UTF8 and latin1 as you suggested.


"LOWER() (and UPPER()) are ineffective when applied to binary strings 
(BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the 
string to a non-binary string:


mysql> SET @str = BINARY 'New York';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));"


I converted the function over to use a variable, and got the same results. 
Here's the new function:

CREATE FUNCTION `ProperCase`(cInput TEXT)
   RETURNS text
   DETERMINISTIC
   CONTAINS SQL
   SQL SECURITY INVOKER
   COMMENT ''
BEGIN
  Declare str Text;
  Declare cReturn Text;
  Set @str=BINARY cInput;
  Set @cReturn = 
CONCAT(UPPER(SUBSTRING(@str,1,1)),LOWER(SUBSTRING(@str,2)));

  RETURN @cReturn;
END;

Still doesn't work. This is driving me NUTz 8-p

Jesse 



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

Re: Function Still Not Working

[Jesse]

Any difference in default collation?


Not sure what that is.  I'm using a visual tool (EMS) to create my function, 
and it doesn't offer that option.  I could update it using the command 
prompt, however.  I may try that later. 



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

Re: Function Still Not Working

[Jesse]

I am curious about that, as well. It brings to mind a discussion that
happened on this list last week (I believe) about case
sensitive/insensitive use of LIKE. I believe the synopsis was that
tables are either created as case-insensitive, or the search needs to be
specified as case sensitive (with BINARY).

Could this be a similar issue, perhaps? One table is specifically
case-insensitive with regard to the function, and the other is not?


In this particular case, we're not dealing with any tables.  I have also 
tried adding BINARY to the mix as well, and it didn't make any difference.


Jesse 



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

RE: Function Still Not Working

[Boyd, Todd M.]

> -Original Message-
> From: Martijn Tonies [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 12, 2008 6:23 AM
> To: MySQL List
> Subject: Re: Function Still Not Working
> 
> Hey,
> 
> > Sorry for posting this again, but I got only one response last time,
> and
> I'm
> > still having the problem.  I spent HOURS the other day manually
going
> > through the data and Properizing these things by hand. I don't want
> to do
> > that again if I can avoid it.  If anyone has any clues on this one,
I
> would
> > appreciate it.

---8<--- snip

> > It's a very simple function used to properize a string sent to it.
> When I
> do
> > a simple SELECT ProperCase('JESSE'); it returns "JESSE" on our
server
> that
> > is running 5.0.17-nt-log. On another server that I've got, running
> > 5.0.51a-community-nt, this function returns "Jesse" as it should.
> >
> > The only difference that I can think of is the version. Is there a
> problem
> > with the older version that would cause this function not to work
> properly?
> 
> Any difference in default collation?

I am curious about that, as well. It brings to mind a discussion that
happened on this list last week (I believe) about case
sensitive/insensitive use of LIKE. I believe the synopsis was that
tables are either created as case-insensitive, or the search needs to be
specified as case sensitive (with BINARY).

Could this be a similar issue, perhaps? One table is specifically
case-insensitive with regard to the function, and the other is not?

Just spit-balling...


Todd Boyd
Web Programmer




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

Re: Function Still Not Working

[Sebastian Mendel]

Jesse schrieb:
Sorry for posting this again, but I got only one response last time, and 
I'm still having the problem.  I spent HOURS the other day manually 
going through the data and Properizing these things by hand. I don't 
want to do that again if I can avoid it.  If anyone has any clues on 
this one, I would appreciate it.


The only difference in this and what I have now is that someone 
suggested changing it to Deterministic, which I did, and that didn't 
change the output.  I also changed "SQL SECURITY DEFINER" to "SQL 
SECURITY INVOKER", and that didn't make a difference either.


I have the following function on two servers:

CREATE FUNCTION `ProperCase`(cInput TEXT)
  RETURNS text
  NOT DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY DEFINER
  COMMENT ''
BEGIN
  Declare cReturn Text;
  Set cReturn = CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput
FROM 2)));
  RETURN cReturn;
END;

It's a very simple function used to properize a string sent to it. When 
I do

a simple SELECT ProperCase('JESSE'); it returns "JESSE" on our server that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns "Jesse" as it should.


does it work outside the function?

did you tried SUBSTRING(cInput, 2)?

did you tried with converting?

from the manual: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

"LOWER() (and UPPER()) are ineffective when applied to binary strings 
(BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the 
string to a non-binary string:


mysql> SET @str = BINARY 'New York';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));"

--
Sebastian Mendel

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

Re: Function Still Not Working

[Martijn Tonies]

Hey,

> Sorry for posting this again, but I got only one response last time, and
I'm
> still having the problem.  I spent HOURS the other day manually going
> through the data and Properizing these things by hand. I don't want to do
> that again if I can avoid it.  If anyone has any clues on this one, I
would
> appreciate it.
>
> The only difference in this and what I have now is that someone suggested
> changing it to Deterministic, which I did, and that didn't change the
> output.  I also changed "SQL SECURITY DEFINER" to "SQL SECURITY INVOKER",
> and that didn't make a difference either.
> 
> I have the following function on two servers:
>
> CREATE FUNCTION `ProperCase`(cInput TEXT)
>RETURNS text
>NOT DETERMINISTIC
>CONTAINS SQL
>SQL SECURITY DEFINER
>COMMENT ''
> BEGIN
>Declare cReturn Text;
>Set cReturn =
CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput
> FROM 2)));
>RETURN cReturn;
> END;
>
> It's a very simple function used to properize a string sent to it. When I
do
> a simple SELECT ProperCase('JESSE'); it returns "JESSE" on our server that
> is running 5.0.17-nt-log. On another server that I've got, running
> 5.0.51a-community-nt, this function returns "Jesse" as it should.
>
> The only difference that I can think of is the version. Is there a problem
> with the older version that would cause this function not to work
properly?

Any difference in default collation?

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


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

Function Still Not Working

[Jesse]

Sorry for posting this again, but I got only one response last time, and I'm 
still having the problem.  I spent HOURS the other day manually going 
through the data and Properizing these things by hand. I don't want to do 
that again if I can avoid it.  If anyone has any clues on this one, I would 
appreciate it.


The only difference in this and what I have now is that someone suggested 
changing it to Deterministic, which I did, and that didn't change the 
output.  I also changed "SQL SECURITY DEFINER" to "SQL SECURITY INVOKER", 
and that didn't make a difference either.


I have the following function on two servers:

CREATE FUNCTION `ProperCase`(cInput TEXT)
  RETURNS text
  NOT DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY DEFINER
  COMMENT ''
BEGIN
  Declare cReturn Text;
  Set cReturn = CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput
FROM 2)));
  RETURN cReturn;
END;

It's a very simple function used to properize a string sent to it. When I do
a simple SELECT ProperCase('JESSE'); it returns "JESSE" on our server that
is running 5.0.17-nt-log. On another server that I've got, running
5.0.51a-community-nt, this function returns "Jesse" as it should.

The only difference that I can think of is the version. Is there a problem
with the older version that would cause this function not to work properly?

Thanks,
Jesse 



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

Function Not Working

[Jesse]

I have the following function on two servers:

CREATE FUNCTION `ProperCase`(cInput TEXT)
  RETURNS text
  NOT DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY DEFINER
  COMMENT ''
BEGIN
  Declare cReturn Text;
  Set cReturn = CONCAT(UPPER(SUBSTRING(cInput,1,1)),LOWER(SUBSTRING(cInput 
FROM 2)));

  RETURN cReturn;
END;

It's a very simple function used to properize a string sent to it. When I do 
a simple SELECT ProperCase('JESSE'); it returns "JESSE" on our server that 
is running 5.0.17-nt-log. On another server that I've got, running 
5.0.51a-community-nt, this function returns "Jesse" as it should.


The only difference that I can think of is the version. Is there a problem 
with the older version that would cause this function not to work properly?


Thanks,
Jesse 



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

Re: % wildcard host permission not working

[Adam Gerson]

Yes, I did "FLUSH PRIVILEGES" and I think I only have one username/host 
entry for this user.


Adam


Erik Giberti wrote:

Did you "FLUSH PRIVILEGES"?
I'd also check that the username and passwords are the same for each 
host entry, I've had problems if passwords were different for a shared 
username from different hosts.


On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote:

I set up a user and entered % for the host. I am not able to connect. 
However, if I change the host value to my FQDN it works fine. 
Shouldn't the wildcard allow me to connect from any host?


Thanks,
Adam


--
Adam Gerson
Assistant Director of Technology
Apple Certified System Administrator (ACSA)
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax.  212-428-6806
[EMAIL PROTECTED]
http://www.cgps.org
Public key - subkeys.pgp.net


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







--
Adam Gerson
Assistant Director of Technology
Apple Certified System Administrator (ACSA)
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax.  212-428-6806
[EMAIL PROTECTED]
http://www.cgps.org
Public key - subkeys.pgp.net


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

Re: % wildcard host permission not working

[Erik Giberti]

Did you "FLUSH PRIVILEGES"?
I'd also check that the username and passwords are the same for each  
host entry, I've had problems if passwords were different for a shared  
username from different hosts.


On Apr 24, 2008, at 8:58 AM, Adam Gerson wrote:

I set up a user and entered % for the host. I am not able to  
connect. However, if I change the host value to my FQDN it works  
fine. Shouldn't the wildcard allow me to connect from any host?


Thanks,
Adam


--
Adam Gerson
Assistant Director of Technology
Apple Certified System Administrator (ACSA)
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax.  212-428-6806
[EMAIL PROTECTED]
http://www.cgps.org
Public key - subkeys.pgp.net


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

% wildcard host permission not working

[Adam Gerson]

I set up a user and entered % for the host. I am not able to connect. 
However, if I change the host value to my FQDN it works fine. Shouldn't 
the wildcard allow me to connect from any host?


Thanks,
Adam


--
Adam Gerson
Assistant Director of Technology
Apple Certified System Administrator (ACSA)
Columbia Grammar and Prep School
phone. 212-749-6200 ex. 321
fax.  212-428-6806
[EMAIL PROTECTED]
http://www.cgps.org
Public key - subkeys.pgp.net


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

Re: Newbie Q- Mac OSX install - login to mysql not working...

[Rob Wultsch]

On Dec 13, 2007 12:11 PM, Al <[EMAIL PROTECTED]> wrote:
> (Sorry if this is a duplicate post, email issues w/multiple accts)
>
> I have a MacBook Leopard OS 10.5.1 2 GB RAM. I've got a good amount of
> knowledge of computer systems, but haven't setup or worked with mySQL
> before. (usually use it preinstalled on my hosting service). I
> downloaded the package and installed it via the automated setup that
> comes for the MacOS. It started fine. The MySQL Sys prefs tool shows
> the status of the server as running. However, I want to now setup
> Wordpress on my localhost. I need to create a database on mysql. I
> opened the shell and navigate to the location of mysql. I have been
> using the instructions as shown below.
>
> $ mysql -u adminusername -p
> Enter password:
>
>
> This is where I'm stuck. No matter what account I use, I am unable to
> login to the database. My personal account is the only account on the
> machine, while it's not root, it has full admin privs, and I've tried
> everything to login, including no password, my account's password,
> etc. Since I've not activated root on this machine (I understand that
> Apple, by default disables it), perhaps there's something I'm missing
> in my lack of knowledge of the way the Mac OS uses root?
>
> I'm sure this is a stupid newbie issue, but I'm a bit wrapped around
> the axle here. Do I need to use sudo? (I've even attempted this with
> no success). I've searched on a few lists to see if anyone has had
> this problem, but not seen it yet.
>
> Should I just reinstall? Am I just missing something that passed by in
> install (I don't remember adding a password other than entering the
> password that I use to admin the machine.
>
> Thoughts? Thanks in advance for any help.
>
>
No idea about the particulars of OSX, however
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-unix
should work.

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

Newbie Q- Mac OSX install - login to mysql not working...

[Al]

(Sorry if this is a duplicate post, email issues w/multiple accts)

I have a MacBook Leopard OS 10.5.1 2 GB RAM. I've got a good amount of
knowledge of computer systems, but haven't setup or worked with mySQL
before. (usually use it preinstalled on my hosting service). I
downloaded the package and installed it via the automated setup that
comes for the MacOS. It started fine. The MySQL Sys prefs tool shows
the status of the server as running. However, I want to now setup
Wordpress on my localhost. I need to create a database on mysql. I
opened the shell and navigate to the location of mysql. I have been
using the instructions as shown below.

$ mysql -u adminusername -p
Enter password:


This is where I'm stuck. No matter what account I use, I am unable to
login to the database. My personal account is the only account on the
machine, while it's not root, it has full admin privs, and I've tried
everything to login, including no password, my account's password,
etc. Since I've not activated root on this machine (I understand that
Apple, by default disables it), perhaps there's something I'm missing
in my lack of knowledge of the way the Mac OS uses root?

I'm sure this is a stupid newbie issue, but I'm a bit wrapped around
the axle here. Do I need to use sudo? (I've even attempted this with
no success). I've searched on a few lists to see if anyone has had
this problem, but not seen it yet.

Should I just reinstall? Am I just missing something that passed by in
install (I don't remember adding a password other than entering the
password that I use to admin the machine.

Thoughts? Thanks in advance for any help.

Re: How to know the number of rows used in a SELECT MAX() query? FOUND_ROWS() not working.

[Baron Schwartz]

Hi,

Daevid Vincent wrote:
Is there a way to know how many rows were used in a computation? 


I tried this 'trick' but I still get 1, when I know that there are 3 rows
used...

SELECT SQL_CALC_FOUND_ROWS 
	MAX(DATE_ADD('2007-10-18 18:04:45', INTERVAL user_access_hours
HOUR)), 
	MAX(access_expire)

FROM
	end_user_groups 
	  JOIN end_user_group_links ON gid = id 
WHERE 
	enabled = 1 AND uid = 16;




You can use COUNT(*).  FOUND_ROWS() works a little differently, as you 
know -- it lets you know how many rows would have been returned without 
a LIMIT.  But this query has no LIMIT of course.



select FOUND_ROWS();









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

How to know the number of rows used in a SELECT MAX() query? FOUND_ROWS() not working.

[Daevid Vincent]

Is there a way to know how many rows were used in a computation? 

I tried this 'trick' but I still get 1, when I know that there are 3 rows
used...

SELECT SQL_CALC_FOUND_ROWS 
MAX(DATE_ADD('2007-10-18 18:04:45', INTERVAL user_access_hours
HOUR)), 
MAX(access_expire)
FROM
end_user_groups 
  JOIN end_user_group_links ON gid = id 
WHERE 
enabled = 1 AND uid = 16;

select FOUND_ROWS();






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

possible bug: general logging not working with 5.1.20-beta-log

[Paul Maunders]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

We have general logging enabled on our mysql server via a my.cnf
setting. The log records an entry when the server restarts, but does not
record any queries. Is this a bug? or have I missed something.

In my.cnf

[mysqld]
log=query.log

Contents of /var/lib/mysql/query.log after several queries have been run
is still:

/usr/sbin/mysqld, Version: 5.1.20-beta-log (MySQL Community Server
(GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time Id CommandArgument

Any help would be appreciated!

Regards,

Paul Maunders
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGxIF4sYtjtAM5Dp4RAvwsAKC30LP6RBvGYQkwmyd1qwfbwcnU0wCeLZhZ
ZkHs5Osxwa1LzARdumP+ILQ=
=5fzk
-END PGP SIGNATURE-

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

Plz Help!!! SELECT not working with Å Æ Ø Ä Ö Characters

[VeeJay]

*From: VeeJay* <[EMAIL PROTECTED]> To: mysql@lists.mysql.com,
[EMAIL PROTECTED]
Date: Fri, Aug 10, 2007 at 8:19 AM

   Hello there

I have a problem. When I try to select some names starting with extra
alphabets (Å Æ Ø Ä Ö, etc), I simply don't get required results i.e.,
if I give a select command like:

select * from employees where fname LIKE 'Å%';
I get results starting with English alphabet 'A' but not with 'Å'. Which
also exist in database.
It happens same with Ä...
for Ø or Ö, I get results starting with english O... :(

Here is the output of database characterset:

mysql> SHOW VARIABLES LIKE 'character_set%';
+--++
| Variable_name| Value  |
+--++
| character_set_client | utf8   |
| character_set_connection | utf8   |
| character_set_database   | utf8   |
| character_set_filesystem | binary |
| character_set_results| utf8   |
| character_set_server | utf8   |
| character_set_system | utf8   |
| character_sets_dir   | /usr/local/mysql/share/mysql/charsets/ |
+--++
8 rows in set (0.00 sec)

Operating system is FreeBSD 6.2.
MySQL 5.0


Another question: Is there any way to optimize the LIKE or is there any fast
method to select a column based on starting with a given character?

Thanks for your kind help!


-- 
Thanks!

BR / vj


*From: mysql* <[EMAIL PROTECTED]> To: VeeJay <[EMAIL PROTECTED]>
Cc: mysql@lists.mysql.com
Date: Fri, Aug 10, 2007 at 9:07 AM

  in addition to the correct character-set also set the appropriate
collation sequence

suomi
[Quoted text hidden]


*From: VeeJay* <[EMAIL PROTECTED]> To: mysql <[EMAIL PROTECTED]>
Date: Fri, Aug 10, 2007 at 5:38 PM

   Hi Suomi

Thanks for your response. But how to set collation sequence? Could you
please guide a little?

Thanks...

VJ


[Quoted text hidden]
-- 
Thanks!

BR / vj

Re: Plz Help!!! SELECT not working with Å Æ Ø Ä Ö Characters

[mysql]

in addition to the correct character-set also set the appropriate 
collation sequence


suomi

VeeJay wrote:

Hello there

I have a problem. When I try to select some names starting with extra
alphabets (Å Æ Ø Ä Ö, etc), I simply don't get required results i.e.,
if I give a select command like:

select * from employees where fname LIKE 'Å%';
I get results starting with English alphabet 'A' but not with 'Å'. Which
also exist in database.
It happens same with Ä...
for Ø or Ö, I get results starting with english O... :(

Here is the output of database characterset:

mysql> SHOW VARIABLES LIKE 'character_set%';
+--++
| Variable_name| Value  |
+--++
| character_set_client | utf8   |
| character_set_connection | utf8   |
| character_set_database   | utf8   |
| character_set_filesystem | binary |
| character_set_results| utf8   |
| character_set_server | utf8   |
| character_set_system | utf8   |
| character_sets_dir   | /usr/local/mysql/share/mysql/charsets/ |
+--++
8 rows in set (0.00 sec)

Operating system is FreeBSD 6.2.
MySQL 5.0


Another question: Is there any way to optimize the LIKE or is there any fast
method to select a column based on starting with a given character?

Thanks for your kind help!


  



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

Plz Help!!! SELECT not working with Å Æ Ø Ä Ö Characters

[VeeJay]

Hello there

I have a problem. When I try to select some names starting with extra
alphabets (Å Æ Ø Ä Ö, etc), I simply don't get required results i.e.,
if I give a select command like:

select * from employees where fname LIKE 'Å%';
I get results starting with English alphabet 'A' but not with 'Å'. Which
also exist in database.
It happens same with Ä...
for Ø or Ö, I get results starting with english O... :(

Here is the output of database characterset:

mysql> SHOW VARIABLES LIKE 'character_set%';
+--++
| Variable_name| Value  |
+--++
| character_set_client | utf8   |
| character_set_connection | utf8   |
| character_set_database   | utf8   |
| character_set_filesystem | binary |
| character_set_results| utf8   |
| character_set_server | utf8   |
| character_set_system | utf8   |
| character_sets_dir   | /usr/local/mysql/share/mysql/charsets/ |
+--++
8 rows in set (0.00 sec)

Operating system is FreeBSD 6.2.
MySQL 5.0


Another question: Is there any way to optimize the LIKE or is there any fast
method to select a column based on starting with a given character?

Thanks for your kind help!


-- 
Thanks!

BR / vj

RE: Using index for group-by: Not working?

[Andrew Armstrong]

It's just occurred to me that the IN clause is not a constant.

This probably throws out any chance of using an index for group by?

Cheers

-Original Message-
From: Andrew Armstrong [mailto:[EMAIL PROTECTED] 
Sent: Sunday, 29 July 2007 1:07 PM
To: mysql@lists.mysql.com
Subject: Using index for group-by: Not working?

Hi,

 

I have the following query:

 

SELECT c2, c3, c4, Count(DISTINCT c5)

FROM table1 

WHERE c1 IN (1, 2, 3...)

GROUP BY c2, c3, c4

order by null

 

Yet I can only get it at best to show (under extra): Using where, using
filesort.

 

I have read up on:
http://dev.mysql.com/doc/refman/5.0/en/loose-index-scan.html and
http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html and
http://dev.mysql.com/doc/refman/5.0/en/explain.html yet cannot get this
index to be used.

 

I am running MySQL '5.1.17-beta-community-nt-debug'

 

There are over 600,000 rows in table1 for my testing.

 

I have tried placing indexes on: idx(c2, c3, c4, c5) and idx(c1, c2, c3, c4,
c5) and indx(c5, c1, c2, c3, c4) with no result.

 

Is there a reason I cannot get this query to use an index for grouping?

 

Cheers,

Andrew



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

RE: Using index for group-by: Not working?

[Andrew Armstrong]

Hey Terry,

1) I've tried placing the Count/Distinct bit first - no change. Could you
elaborate on your "bottom up" parsing? I am not aware of this myself (and
have not read this anywhere). Would be interested if you could elaborate or
provide more info.

2) When a GROUP BY is performed, sorting is done also (usually because its
"free"). Sorting by NULL will tell MySQL to not even bother doing this. I've
seen 'sort by null' (as suggested elsewhere) avoid 'Using filesort' under
the Extra column when its not even needed.

Thanks for the response.

-Original Message-
From: Terry Mehlman [mailto:[EMAIL PROTECTED] 
Sent: Sunday, 29 July 2007 1:18 PM
To: Andrew Armstrong
Subject: Re: Using index for group-by: Not working?

just a shot in the dark, but i would suggest two changes to your query.

1) put the count (distinct c5) first rather than last.  as i'm sure
you know the parse happens from the bottom up.  so, the indexes you
are placing on c2, c3, and c4 aren't doing you any good if you put the
distinct clause in their way.

2) order by null?  take that out and see if your performance improves.

just a couple of thoughts.



On 7/28/07, Andrew Armstrong <[EMAIL PROTECTED]> wrote:
> Hi,
>
>
>
> I have the following query:
>
>
>
> SELECT c2, c3, c4, Count(DISTINCT c5)
>
> FROM table1
>
> WHERE c1 IN (1, 2, 3...)
>
> GROUP BY c2, c3, c4
>
> order by null
>
>
>
> Yet I can only get it at best to show (under extra): Using where, using
> filesort.
>
>
>
> I have read up on:
> http://dev.mysql.com/doc/refman/5.0/en/loose-index-scan.html and
> http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html and
> http://dev.mysql.com/doc/refman/5.0/en/explain.html yet cannot get this
> index to be used.
>
>
>
> I am running MySQL '5.1.17-beta-community-nt-debug'
>
>
>
> There are over 600,000 rows in table1 for my testing.
>
>
>
> I have tried placing indexes on: idx(c2, c3, c4, c5) and idx(c1, c2, c3,
c4,
> c5) and indx(c5, c1, c2, c3, c4) with no result.
>
>
>
> Is there a reason I cannot get this query to use an index for grouping?
>
>
>
> Cheers,
>
> Andrew
>
>


-- 
That which Voldemort does not value, he takes no trouble to
comprehend.  Of house-elves and children's tales, of love, loyalty,
and innocence, Voldemort knows and understands nothing.  Nothing.
That they all have a power beyond his own, a power beyond the reach of
any magic, is a truth he has never grasped.

- JK Rowling



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

Using index for group-by: Not working?

[Andrew Armstrong]

Hi,

 

I have the following query:

 

SELECT c2, c3, c4, Count(DISTINCT c5)

FROM table1 

WHERE c1 IN (1, 2, 3...)

GROUP BY c2, c3, c4

order by null

 

Yet I can only get it at best to show (under extra): Using where, using
filesort.

 

I have read up on:
http://dev.mysql.com/doc/refman/5.0/en/loose-index-scan.html and
http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html and
http://dev.mysql.com/doc/refman/5.0/en/explain.html yet cannot get this
index to be used.

 

I am running MySQL '5.1.17-beta-community-nt-debug'

 

There are over 600,000 rows in table1 for my testing.

 

I have tried placing indexes on: idx(c2, c3, c4, c5) and idx(c1, c2, c3, c4,
c5) and indx(c5, c1, c2, c3, c4) with no result.

 

Is there a reason I cannot get this query to use an index for grouping?

 

Cheers,

Andrew

RE: stored procedure not working in legacy ASP

[Jay Blanchard]

[snip]
The stored procedure is in MySQL, but when called using ASP it fails to 
return more than the first record.
Anyone?
[/snip]

You need a while loop. Does the SP work from the command line properly?

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

Re: stored procedure not working in legacy ASP

[Michael Dykman]

Would you care to send the source of that procedure plus tell us why
you believe it works when called by something other than C#?

On 5/30/07, Critters <[EMAIL PROTECTED]> wrote:

The stored procedure is in MySQL, but when called using ASP it fails to
return more than the first record.
Anyone?
--
Dave

Michael Dykman wrote:
> Surely, you don't have legacy stored procedure in ASP under MySQL?
> are you sure this is the right list to be asking?
>
> - michael
>
>
> On 5/30/07, Critters <[EMAIL PROTECTED]> wrote:
>> Hi
>> How do you get multiple record sets from a stored procedure in legacy
>> ASP? It doesn't seem to work for us.
>> The question is how to return multiple record sets from a single stored
>> procedure which myodbc doesn't seem to support?
>>
>> set rs = connection.execute(strSQLsp)
>> If not rs.EOF then
>> response.write rs(1)
>> End if
>> set rs = rs.NextRecordset
>> If not rs.EOF then
>> response.write rs(2)
>> End if
>>
>> We only get the first response.write
>>
>> Thanks
>> --
>> David Scott
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>>
>>
>
>




--
- michael dykman
- [EMAIL PROTECTED]

- All models are wrong.  Some models are useful.

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

Re: stored procedure not working in legacy ASP

[Critters]

The stored procedure is in MySQL, but when called using ASP it fails to 
return more than the first record.

Anyone?
--
Dave

Michael Dykman wrote:

Surely, you don't have legacy stored procedure in ASP under MySQL?
are you sure this is the right list to be asking?

- michael


On 5/30/07, Critters <[EMAIL PROTECTED]> wrote:

Hi
How do you get multiple record sets from a stored procedure in legacy
ASP? It doesn't seem to work for us.
The question is how to return multiple record sets from a single stored
procedure which myodbc doesn't seem to support?

set rs = connection.execute(strSQLsp)
If not rs.EOF then
response.write rs(1)
End if
set rs = rs.NextRecordset
If not rs.EOF then
response.write rs(2)
End if

We only get the first response.write

Thanks
--
David Scott

--
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: stored procedure not working in legacy ASP

[Michael Dykman]

Surely, you don't have legacy stored procedure in ASP under MySQL?
are you sure this is the right list to be asking?

- michael


On 5/30/07, Critters <[EMAIL PROTECTED]> wrote:

Hi
How do you get multiple record sets from a stored procedure in legacy
ASP? It doesn't seem to work for us.
The question is how to return multiple record sets from a single stored
procedure which myodbc doesn't seem to support?

set rs = connection.execute(strSQLsp)
If not rs.EOF then
response.write rs(1)
End if
set rs = rs.NextRecordset
If not rs.EOF then
response.write rs(2)
End if

We only get the first response.write

Thanks
--
David Scott

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





--
- michael dykman
- [EMAIL PROTECTED]

- All models are wrong.  Some models are useful.

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

stored procedure not working in legacy ASP

[Critters]

Hi
How do you get multiple record sets from a stored procedure in legacy 
ASP? It doesn't seem to work for us.
The question is how to return multiple record sets from a single stored 
procedure which myodbc doesn't seem to support?


set rs = connection.execute(strSQLsp)
If not rs.EOF then
response.write rs(1)
End if
set rs = rs.NextRecordset
If not rs.EOF then
response.write rs(2)
End if

We only get the first response.write

Thanks
--
David Scott

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

Re: select with like not working...

[Ricardo Conrado Serafim]

would you can post the table script?? create and insert...
because it should be work ...

Jason Pruim escreveu:
I have tried %jason%, %jason, & jason% all with the same result... Do 
you need to have an index of the column? Currently I didn't 
intentionally make one so I'm not sure if it's automatic or not...



On May 25, 2007, at 1:26 PM, Mike Lockhart wrote:


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Have you tried using 'jason%' instead of '%jason%'?  Also, do you have
an index on that column?

Jason Pruim wrote:

Hi all,

I'm new to the list so please excuse me if I make some newbie mistakes,
I am having trouble figuring out why a select statement won't work,
Here's the statement: "SELECT 'FName' FROM `current` WHERE `FName` like
'%jason%';".

if I run select 'FName' FROM current; then I get 6 rows that say
'jason'. but nothing is showing up when I use 'like'.

Any ideas?

Thanks for looking!





- --

Mike Lockhart  Information Engineer
ChoiceMed, Inc
Email:  [EMAIL PROTECTED]

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.7 (Darwin)

iD8DBQFGVxxcgCgDHkdt5m8RAqTOAKCAdoL4JSjUzsKG1Y9/wdcTCeSdswCffwOy
YO0ALUVivjv7ZDFfXUAbn1M=
=J52W
-END PGP SIGNATURE-

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








--
Ricardo Conrado Serafim
DBA Júnior (MySQL)
URANET - www.uranet.com.br


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

Re: select with like not working...

[Jason Pruim]

I have tried %jason%, %jason, & jason% all with the same result... Do  
you need to have an index of the column? Currently I didn't  
intentionally make one so I'm not sure if it's automatic or not...



On May 25, 2007, at 1:26 PM, Mike Lockhart wrote:


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Have you tried using 'jason%' instead of '%jason%'?  Also, do you have
an index on that column?

Jason Pruim wrote:

Hi all,

I'm new to the list so please excuse me if I make some newbie  
mistakes,

I am having trouble figuring out why a select statement won't work,
Here's the statement: "SELECT 'FName' FROM `current` WHERE `FName`  
like

'%jason%';".

if I run select 'FName' FROM current; then I get 6 rows that say
'jason'. but nothing is showing up when I use 'like'.

Any ideas?

Thanks for looking!





- --

Mike Lockhart  Information Engineer
ChoiceMed, Inc
Email:  [EMAIL PROTECTED]

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.7 (Darwin)

iD8DBQFGVxxcgCgDHkdt5m8RAqTOAKCAdoL4JSjUzsKG1Y9/wdcTCeSdswCffwOy
YO0ALUVivjv7ZDFfXUAbn1M=
=J52W
-END PGP SIGNATURE-

--
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: select with like not working...

[Mike Lockhart]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Have you tried using 'jason%' instead of '%jason%'?  Also, do you have
an index on that column?

Jason Pruim wrote:
> Hi all,
> 
> I'm new to the list so please excuse me if I make some newbie mistakes,
> I am having trouble figuring out why a select statement won't work,
> Here's the statement: "SELECT 'FName' FROM `current` WHERE `FName` like
> '%jason%';".
> 
> if I run select 'FName' FROM current; then I get 6 rows that say
> 'jason'. but nothing is showing up when I use 'like'.
> 
> Any ideas?
> 
> Thanks for looking!
> 
> 


- --

Mike Lockhart  Information Engineer
ChoiceMed, Inc
Email:  [EMAIL PROTECTED]

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.7 (Darwin)

iD8DBQFGVxxcgCgDHkdt5m8RAqTOAKCAdoL4JSjUzsKG1Y9/wdcTCeSdswCffwOy
YO0ALUVivjv7ZDFfXUAbn1M=
=J52W
-END PGP SIGNATURE-

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

select with like not working...

[Jason Pruim]

Hi all,

I'm new to the list so please excuse me if I make some newbie  
mistakes, I am having trouble figuring out why a select statement  
won't work, Here's the statement: "SELECT 'FName' FROM `current`  
WHERE `FName` like '%jason%';".


if I run select 'FName' FROM current; then I get 6 rows that say  
'jason'. but nothing is showing up when I use 'like'.


Any ideas?

Thanks for looking!


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

Re: NOT NULL = Not Working?

[Scott Baker]

I knew it was an option somewhere... Perfect!

Thanks

JamesDR wrote:
> JamesDR wrote:
>> Scott Baker wrote:
>>> If I create the following table, and then try and insert the following
>>> data both inserts work. It looks like the second one works (it shouldn't
>>> because Last is NULL) because it assumes Last = ''. Is there a way I can
>>> make it NOT assume that? If Last is not specified it should reject that
>>> command. Is that possible?
>>>
>>> ---
>>>
>>> DROP TABLE IF EXISTS foo;
>>>
>>> CREATE TABLE foo (
>>>ID INTEGER PRIMARY KEY AUTO_INCREMENT,
>>>First VarChar(30),
>>>Last VarChar(30) NOT NULL,
>>>Zip INTEGER
>>> );
>>>
>>> INSERT INTO foo (First, Last, Zip) VALUES ('Jason','Doolis',97013);
>>> INSERT INTO foo (Last) VALUES (17423);
>>>
>> In your last insert example, Last is inserted as 17423. Which is not null.
>>
> 
> Yup, empty string, the manual says this...
> http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html
> 
> However it does say that to enforce NOT NULL you would have to change
> the sql_mode
> http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
> EG:
> SET SESSION sql_mode='STRICT_ALL_TABLES';
> INSERT INTO foo (zip) VALUES (12345);
> SET SESSION sql_mode='';
> 
> I get an error on the insert statement:
> "Field 'Last' doesn't have a default value."
> 
> You may need to set sql_mode to STRICT_ALL_TABLES before the insert or
> do it in my.cnf or as a command line parameter.
> 
> 


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

Re: NOT NULL = Not Working?

[JamesDR]

JamesDR wrote:
> Scott Baker wrote:
>> If I create the following table, and then try and insert the following
>> data both inserts work. It looks like the second one works (it shouldn't
>> because Last is NULL) because it assumes Last = ''. Is there a way I can
>> make it NOT assume that? If Last is not specified it should reject that
>> command. Is that possible?
>>
>> ---
>>
>> DROP TABLE IF EXISTS foo;
>>
>> CREATE TABLE foo (
>>ID INTEGER PRIMARY KEY AUTO_INCREMENT,
>>First VarChar(30),
>>Last VarChar(30) NOT NULL,
>>Zip INTEGER
>> );
>>
>> INSERT INTO foo (First, Last, Zip) VALUES ('Jason','Doolis',97013);
>> INSERT INTO foo (Last) VALUES (17423);
>>
> 
> In your last insert example, Last is inserted as 17423. Which is not null.
> 

Yup, empty string, the manual says this...
http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

However it does say that to enforce NOT NULL you would have to change
the sql_mode
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
EG:
SET SESSION sql_mode='STRICT_ALL_TABLES';
INSERT INTO foo (zip) VALUES (12345);
SET SESSION sql_mode='';

I get an error on the insert statement:
"Field 'Last' doesn't have a default value."

You may need to set sql_mode to STRICT_ALL_TABLES before the insert or
do it in my.cnf or as a command line parameter.


-- 
Thanks,
James

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

Re: NOT NULL = Not Working?

[Ricardo Conrado Serafim]

Scott Baker escreveu:

If I create the following table, and then try and insert the following
data both inserts work. It looks like the second one works (it shouldn't
because Last is NULL) because it assumes Last = ''. Is there a way I can
make it NOT assume that? If Last is not specified it should reject that
command. Is that possible?

---

DROP TABLE IF EXISTS foo;

CREATE TABLE foo (
   ID INTEGER PRIMARY KEY AUTO_INCREMENT,
   First VarChar(30),
   Last VarChar(30) NOT NULL,
   Zip INTEGER
);

INSERT INTO foo (First, Last, Zip) VALUES ('Jason','Doolis',97013);
INSERT INTO foo (Last) VALUES (17423);

  
I think that you are confuse because in your second command you're 
setting Last=17423


try this command

INSERT INTO foo (Zip) VALUES (17423);

and you'll see the message error saying that Last can't be NULL

I hope that it helps

--
Ricardo Conrado Serafim
DBA Júnior (MySQL)
URANET - www.uranet.com.br


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