Re: How do I determine if versions of phpMyAdmin before 4.8.5 is SQL Injectable using sqlmap?

[shawn l.green]

Hello,

On 4/17/2019 10:29 AM, Turritopsis Dohrnii Teo En Ming wrote:

Subject/Topic: How do I determine if versions of phpMyAdmin before 4.8.5 is SQL 
Injectable using sqlmap?

Good evening from Singapore,

Our customer (company name is Confidential/not disclosed) reported that their 
MySQL database has been found missing or was deleted a few times.


While it is bad form to explain how to break into anyone's software 
(including our own), there are places you can look to get a better idea 
about what might have happened:


1 - the database may have been removed by a DROP DATABASE command.

General Query Log - this will show you which session issued the command 
and the command itself.


Audit log (only for commercial releases) - same thing

Binary Log - Should have a record of the command executing. But, 
depending on which account was used or if Binary Log filtering is in 
place, it may not. This presumes that the Binary Log is even enabled on 
this system.  Many people mistakenly believe it is only for Replication 
when its other primary use is for point-in-time recovery. If your 
customer has a recent backup and all of the Binary Log files created 
since that backup, they could return the system to the point it was at 
just before that database went missing, skip that DROP command, then 
continue rolling forward the changes to the other tables to return to a 
"current" state of their data.


2 - The database was "dropped" by either changing privileges to the 
folder or by removing it from disk or some other file-level or 
system-level operation. Either of those would cause errors to start 
appearing in the MySQL Error Log because a resource that mysqld thinks 
should exist is no longer available.   While the Error Log can't tell 
you which operation made those files "no longer available" it will have 
a fingerprint that such an action happened outside of mysqld.



Have you determined which method was used to make that database/schema 
disappear?


A normal DROP command (which could happen through an SQL injection 
attack) would not leave messages in the Error Log about "unable to 
access ..." or something similar. The server (mysqld) would know that 
the database was gone (because it removed it) and it wouldn't be trying 
to find it or the tables within it for your clients to use it.





... snip ...
No matter how many commands I try, sqlmap always report that phpMyAdmin 4.8.4 
is *NOT* SQL injectable. Perhaps I was using the wrong sqlmap commands all the 
time? The following is one of the many sqlmap commands I have used.

$ python sqlmap.py -u "https://www.EXAMPLE.com/phymyadmin/index.php?id=1; --level=1 
--dbms=mysql --sql-query="drop database"



Privately asking phpMyAdmin may be a better source of information about 
how to hack their system to do things it was not intended to do. This 
list is not about phpMyAdmin and it is very public.  They may also have 
a way of showing you some kind of trace or log that serves as a 
fingerprint for that happening.



--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


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

How do I determine if versions of phpMyAdmin before 4.8.5 is SQL Injectable using sqlmap?

[Turritopsis Dohrnii Teo En Ming]

Subject/Topic: How do I determine if versions of phpMyAdmin before 4.8.5 is SQL 
Injectable using sqlmap?

Good evening from Singapore,

Our customer (company name is Confidential/not disclosed) reported that their 
MySQL database has been found missing or was deleted a few times. They are 
using Ubuntu 16.04 LTS Linux server with Apache2 Web Server, MySQL and PHP 
(LAMP).

We responded to these security incidents by changing the passwords of the 
regular user, root user, and MySQL database user root. We have also examined 
/var/log/auth.log and think that the hacker could not have come in through ssh 
or sftp over ssh. From /var/log/mysql/error.log, we can ascertain that the 
MySQL database has been deleted at certain timings. We have also found nothing 
abnormal after examining /var/log/apache2/access.log.

Even though we have secured the Ubuntu Linux server by changing passwords, the 
hacker was still able to delete our customer's MySQL database again and again. 
I have already proposed to install ModSecurity Open Source Web Application 
Firewall (WAF) to defend against web application attacks but my boss has told 
me to put that on hold at the moment. In fact, I have already deployed 
ModSecurity 2.9.0 on a Ubuntu 16.04 LTS *Testing* server and found that it 
actively detects and logs Nessus and sqlmap vulnerability scans in blocking 
mode.

Since we did not find any evidence that the hacker had breached our customer's 
Ubuntu 16.04 LTS production server through ssh or Teamviewer, we suspect that 
the hacker could have achieved it by SQL injection. I took the initiative of 
downloading and installing Nessus Professional 8.3.1 Trial version for Windows 
64-bit. The vulnerability scan report generated by Nessus Web Application Tests 
shows that our customer is using a version of phpMyAdmin prior to 4.8.5 which 
could be vulnerable to SQL injection using the designer feature.

Further research shows that I can use sqlmap to determine if phpMyAdmin is SQL 
injectable. I already have a Testing Ubuntu 16.04 LTS Linux server with a 
Testing MySQL database and a Testing phpMyAdmin 4.8.4. I have purposely 
installed phpMyAdmin 4.8.4 because this version was reported to be vulnerable 
to SQL injection using the designer feature, and our customer is using a 
vulnerable version, according to CVE-2019-6798 ( 
https://nvd.nist.gov/vuln/detail/CVE-2019-6798 ). Then I proceeded to download 
and execute sqlmap on our Ubuntu Linux desktop against our Testing server.

No matter how many commands I try, sqlmap always report that phpMyAdmin 4.8.4 
is *NOT* SQL injectable. Perhaps I was using the wrong sqlmap commands all the 
time? The following is one of the many sqlmap commands I have used.

$ python sqlmap.py -u "https://www.EXAMPLE.com/phymyadmin/index.php?id=1; 
--level=1 --dbms=mysql --sql-query="drop database"

Replace database by database name.

May I know what is the correct sqlmap command that I should use to determine 
that my Testing phpMyAdmin 4.8.4 is SQL injectable? I would like to know if I 
can successfully drop/delete the Testing database on our Testing server. If I 
can successfully drop/delete the Testing MySQL database using sqlmap, I would 
be able to conclude that the hacker must have carried out SQL injection to 
drop/delete the customer's database. I have already turned off the Testing 
ModSecurity Web Application Firewall on our Testing server to allow sqlmap to 
go through.

Please point me to any good tutorial on SQL injection using sqlmap. Maybe I do 
not understand SQL injection well enough. Our customer is also using a 
customised in-house inventory management system that relies on PHP application 
and MySQL database.

Would open source Snort Intrusion Detection System (IDS) and Intrusion 
Prevention System (IPS) be able to detect and block SQL injection as well?

Please advise.

Thank you very much.

-BEGIN EMAIL SIGNATURE-

The Gospel for all Targeted Individuals (TIs):

[The New York Times] Microwave Weapons Are Prime Suspect in Ills of
U.S. Embassy Workers

Link: 
https://www.nytimes.com/2018/09/01/science/sonic-attack-cuba-microwave.html



Singaporean Mr. Turritopsis Dohrnii Teo En Ming's Academic
Qualifications as at 14 Feb 2019

[1] https://tdtemcerts.wordpress.com/

[2] https://tdtemcerts.blogspot.sg/

[3] https://www.scribd.com/user/270125049/Teo-En-Ming

-END EMAIL SIGNATURE-


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

Step-by-Step Tutorial: How to Setup Your Own e-Commerce Online Store using WooCommerce 3.4.5, Wordpress 4.9.8, and CentOS 1805 (LAMP) in Amazon AWS Cloud

[Turritopsis Dohrnii Teo En Ming]

Good morning from Singapore,


You can read my step-by-step tutorial on How to Setup Your Own e-Commerce 
Online Store using WooCommerce 3.4.5, Wordpress 4.9.8, and CentOS 1805 (LAMP) 
in Amazon AWS Cloud at any one of my two redundant blogs. My blogs were 
configured in RAID 1 mirroring array.


https://tdtemcerts.wordpress.com/2018/09/29/step-by-step-tutorial-how-to-setup-your-own-e-commerce-online-store-using-woocommerce-3-4-5-wordpress-4-9-8-and-centos-1805-lamp-in-amazon-aws-cloud/


https://tdtemcerts.blogspot.com/2018/09/step-by-step-tutorial-how-to-setup-your.html


Thanks for reading! If there are any mistakes, please do let me know!





===BEGIN SIGNATURE===

Turritopsis Dohrnii Teo En Ming's Academic Qualifications as at 30 Oct 2017

[1] https://tdtemcerts.wordpress.com/

<https://tdtemcerts.wordpress.com/>[2] http://tdtemcerts.blogspot.sg/

<http://tdtemcerts.blogspot.sg/>[3] 
https://www.scribd.com/user/270125049/Teo-En-Ming

<https://www.scribd.com/user/270125049/Teo-En-Ming>===END SIGNATURE===

Re: Query interruption with MySQL 5.7 using KILL QUERY in signal handler does no more work

[Sebastien FLAESCH]

I did the following test:

My program still uses MySQL 5.7 libmysqlclient.so, but I connect now to a 
5.6.16 server.

And the SQL interrupt works fine... so I suspect there is a MySQL server issue 
in 5.7.

Seb

On 07/12/2016 01:01 PM, Sebastien FLAESCH wrote:

About:

 > B) For some reason, the program does not want to exit() - (must investigate)

In fact we use prepared statements with a sequence of mysql_stmt_init,
mysql_stmt_prepare, mysql_stmt_execute, mysql_stmt_fetch, ... and
mysql_stmt_close.

After the statement was interrupted, we try to free the MySQL statement hanlde 
with:

mysql_stmt_close();

But this API call hangs... (below the strace output)

This was working fine in older versions...

Seb

strace log:

...

sendto(3, "s\0\0\0\26select COUNT(*) from   t2 a"..., 119, 0, NULL, 0) = 119
recvfrom(3, "\f\0\0\1\0\1\0\0\0\1\0\0\0\0\0\0\36\0\0\2\3def\0\0\0\10COUN"..., 
16384, 0, NULL, NULL) = 50
sendto(3, "\n\0\0\0\27\1\0\0\0\1\1\0\0\0", 14, 0, NULL, 0) = 14
recvfrom(3, 0x62905220, 16384, 0, 0, 0) = ? ERESTARTSYS (To be restarted if 
SA_RESTART is set)

...

When mysql_stmt_close() is called, hangs in recvfrom():

recvfrom(3,





On 07/03/2016 06:55 PM, Sebastien FLAESCH wrote:

Hi all,

I use the following technique to cancel a long running query:

In the SIGINT signal handler, I restart a connection and I perform a

KILL QUERY mysql-process-id-of-running-query

This was working find with MySQL 5.6.

But with 5.7 (5.7.11), we get now a different result:

A) The query is still interrupted, but we no longer get an SQL error -1317.

B) For some reason, the program does not want to exit() - (must investigate)

Any clues?


With mysql it's working fine:

mysql> select sleep(10);
^C^C -- query aborted
+---+
| sleep(10) |
+---+
+---+
1 row in set (2.79 sec)

mysql> \q
Bye


Thanks!
Seb







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

Re: Query interruption with MySQL 5.7 using KILL QUERY in signal handler does no more work

[Sebastien FLAESCH]

About:

> B) For some reason, the program does not want to exit() - (must investigate)

In fact we use prepared statements with a sequence of mysql_stmt_init,
mysql_stmt_prepare, mysql_stmt_execute, mysql_stmt_fetch, ... and
mysql_stmt_close.

After the statement was interrupted, we try to free the MySQL statement hanlde 
with:

mysql_stmt_close();

But this API call hangs... (below the strace output)

This was working fine in older versions...

Seb

strace log:

...

sendto(3, "s\0\0\0\26select COUNT(*) from   t2 a"..., 119, 0, NULL, 0) = 119
recvfrom(3, "\f\0\0\1\0\1\0\0\0\1\0\0\0\0\0\0\36\0\0\2\3def\0\0\0\10COUN"..., 
16384, 0, NULL, NULL) = 50
sendto(3, "\n\0\0\0\27\1\0\0\0\1\1\0\0\0", 14, 0, NULL, 0) = 14
recvfrom(3, 0x62905220, 16384, 0, 0, 0) = ? ERESTARTSYS (To be restarted if 
SA_RESTART is set)

...

When mysql_stmt_close() is called, hangs in recvfrom():

recvfrom(3,





On 07/03/2016 06:55 PM, Sebastien FLAESCH wrote:

Hi all,

I use the following technique to cancel a long running query:

In the SIGINT signal handler, I restart a connection and I perform a

KILL QUERY mysql-process-id-of-running-query

This was working find with MySQL 5.6.

But with 5.7 (5.7.11), we get now a different result:

A) The query is still interrupted, but we no longer get an SQL error -1317.

B) For some reason, the program does not want to exit() - (must investigate)

Any clues?


With mysql it's working fine:

mysql> select sleep(10);
^C^C -- query aborted
+---+
| sleep(10) |
+---+
+---+
1 row in set (2.79 sec)

mysql> \q
Bye


Thanks!
Seb




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

Query interruption with MySQL 5.7 using KILL QUERY in signal handler does no more work

[Sebastien FLAESCH]

Hi all,

I use the following technique to cancel a long running query:

In the SIGINT signal handler, I restart a connection and I perform a

   KILL QUERY mysql-process-id-of-running-query

This was working find with MySQL 5.6.

But with 5.7 (5.7.11), we get now a different result:

A) The query is still interrupted, but we no longer get an SQL error -1317.

B) For some reason, the program does not want to exit() - (must investigate)

Any clues?


With mysql it's working fine:

mysql> select sleep(10);
^C^C -- query aborted
+---+
| sleep(10) |
+---+
+---+
1 row in set (2.79 sec)

mysql> \q
Bye


Thanks!
Seb

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

using indices with SMALL tables

[Lucio Chiappetti]

I am regularly using indices on medium-big tables (1000 to > 5 
entries), and even on temporary tables (which I use a lot) in joins 
(EXPLAIN SELECT is your friend).


But I'd never thought indices were needed for small tables (100-200 
entries). I recently found they are useful too, and I'd like to share.


I have one largish table (~5 entries) for which I have to compute some 
probabilities and likelihoods which depend on two columns, distance d and 
magnitude mag. While the dependency on d is given by a simple formula, the 
dependency on mag requires a lookup and a linear interpolation in another 
SMALL table. The small table has 190 elements.


I created a stored function to do the lookup and interpolation.

create function lookup (x float)
returns float
 begin
 declare yr float default -1.0;
 declare y1 float default 0;
 declare y2 float default -1.0;
 declare x1 float default 0;
 declare x2 float default 0;
 select mag,y from xyview where mag > x limit 1 into x2,y2;
 select mag,y from xyview where mag < x order by mag desc limit 1 into x1,y1;
 set yr=y1 ;
 if x1 <> x2 then
  set yr = yr + (x-x1)*(y2-y1)/(x2-x1) ;
 end if;
 return yr;

Then I attempted to update the big table with statements like

 update t set lr1  = lookup(mag)*exp(-0.5*d*d)/2/pi() ;

This was taking a long time, despite the fact that an
 explain select t.*,lookup(mag)
shows nothing peculiar.

I found that a single lookup call takes 0.05 sec, and scaling that for
5 elements would take 38 min. And at the end, I'd have to repeat the 
process for 48 times (each time changing the table xyview, since prepared 
statements are not allowed in stored functions).


Well, it is enough to add an index on mag on the small table xyview, to 
cut the processing time BY A FACTOR 736.


Now what had taken 38 minutes takes 3.02 sec !!! Great !

--
 
Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy) For 
more info : http://www.iasf-milano.inaf.it/~lucio/personal.html 
 
Do not like Firefox >=29 ?  Get Pale Moon !  http://www.palemoon.org


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

Re: using alias in where clause

[shawn l.green]

On 1/28/2016 6:30 PM, Larry Martell wrote:

On Thu, Jan 28, 2016 at 5:45 PM, shawn l.green <shawn.l.gr...@oracle.com> wrote:



On 1/28/2016 3:32 PM, Larry Martell wrote:


On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green <shawn.l.gr...@oracle.com>
wrote:




On 1/28/2016 1:14 PM, Larry Martell wrote:



On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <h...@tbbs.net> wrote:



2016/01/25 19:16 ... Larry Martell:




SELECT IFNULL(f_tag_bottom,
IFNULL(f_tag_bottom_major_axis,
 IFNULL(f_tag_bottom_minor_axis,
  IFNULL(f_tag_ch_x_bottom,
   IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
   STDDEV(ch_x_top)





Of course, this isn't your real problem, but you could use COALESCE
instead
of all those IFNULLs (and you don't need the last one):
   SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag,
   STDDEV(ch_x_top)
   

As Johnny Withers points out, you may repeat the expression in the
WHERE-clause:
   WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) =
'E-CD7'
If really only one of those is not NULL, it is equivalent to this:
   'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom)




Many thanks to Hal.sz and Johnny - I had forgotten about coalesce and
I didn't know I could use that in a where clause. This worked great
for the requirement I had, but of course, once that was implemented my
client changed the requirements. Now they want to know which of the 5
f_tag_* columns was matched. Not sure how I'll do that. Probably need
another query.


One option to consider is to add another column to the query with a CASE
similar to this...

SELECT
, ... original fields ...
, CASE
WHEN f_tag_bottom THEN 'f_tag_bottom'
WHEN f_tag_bottom_major_axis THEN 'f_tag_bottom_major_axis'
... repeat for the rest of the fields to test ...
ELSE 'none'
END as match_flag
FROM ...

Technically, the term in the WHERE clause should prevent a 'none' result
but
I put it there to help future-proof the code.



Won't that find the first one of the f_tags that is not null, but not
necessarily the one that was matched by the where clause?



I slightly cheated in my example.

My CASE...END was listing terms in the same order as the COALESCE() function
you were using in the WHERE clause. The cheat was that only a non-null value
could be TRUE. To be more accurate, I should have used
... WHEN f_tag_bottom IS NOT NULL THEN ...
That way you end up with a true boolean check within the CASE decision tree.

As the COALESCE() is testing its terms in the same sequence as the
CASE...END, there should be no difference between the two checks.  But, that
also adds to the maintenance cost of this query. If you should change the
order of the f_tag checks in the COALESCE() function, you would need to
change the CASE...END to the same sequence.


Yes, I see that, but does the case only look at the filtered rows? For
example, lets say there's this data:

row 1: f_tag_bottom = "ABC"
row 2: f_tag_bottom_major_axis = "XYZ"

and my where clause has this:

WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'XYZ'

won't the CASE pick up row 1? Whereas I want it to pick up row 2.



Yes it would.

Just modify the check in the case from "IS NOT NULL" to "='XYZ'" to make 
both functions (CASE and COALESCE) find the same field value in the same 
row at the same time.


--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


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

Re: using alias in where clause

[Larry Martell]

On Fri, Jan 29, 2016 at 11:15 AM, shawn l.green
<shawn.l.gr...@oracle.com> wrote:
>
>
> On 1/28/2016 6:30 PM, Larry Martell wrote:
>>
>> On Thu, Jan 28, 2016 at 5:45 PM, shawn l.green <shawn.l.gr...@oracle.com>
>> wrote:
>>>
>>>
>>>
>>> On 1/28/2016 3:32 PM, Larry Martell wrote:
>>>>
>>>>
>>>> On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green
>>>> <shawn.l.gr...@oracle.com>
>>>> wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> On 1/28/2016 1:14 PM, Larry Martell wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <h...@tbbs.net> wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> 2016/01/25 19:16 ... Larry Martell:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> SELECT IFNULL(f_tag_bottom,
>>>>>>>> IFNULL(f_tag_bottom_major_axis,
>>>>>>>>  IFNULL(f_tag_bottom_minor_axis,
>>>>>>>>   IFNULL(f_tag_ch_x_bottom,
>>>>>>>>IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
>>>>>>>>STDDEV(ch_x_top)
>>>>>>>> 
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> Of course, this isn't your real problem, but you could use COALESCE
>>>>>>> instead
>>>>>>> of all those IFNULLs (and you don't need the last one):
>>>>>>>SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
>>>>>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as
>>>>>>> ftag,
>>>>>>>STDDEV(ch_x_top)
>>>>>>>
>>>>>>>
>>>>>>> As Johnny Withers points out, you may repeat the expression in the
>>>>>>> WHERE-clause:
>>>>>>>WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
>>>>>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) =
>>>>>>> 'E-CD7'
>>>>>>> If really only one of those is not NULL, it is equivalent to this:
>>>>>>>'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis,
>>>>>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom)
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> Many thanks to Hal.sz and Johnny - I had forgotten about coalesce and
>>>>>> I didn't know I could use that in a where clause. This worked great
>>>>>> for the requirement I had, but of course, once that was implemented my
>>>>>> client changed the requirements. Now they want to know which of the 5
>>>>>> f_tag_* columns was matched. Not sure how I'll do that. Probably need
>>>>>> another query.
>>>>>>
>>>>> One option to consider is to add another column to the query with a
>>>>> CASE
>>>>> similar to this...
>>>>>
>>>>> SELECT
>>>>> , ... original fields ...
>>>>> , CASE
>>>>> WHEN f_tag_bottom THEN 'f_tag_bottom'
>>>>> WHEN f_tag_bottom_major_axis THEN 'f_tag_bottom_major_axis'
>>>>> ... repeat for the rest of the fields to test ...
>>>>> ELSE 'none'
>>>>> END as match_flag
>>>>> FROM ...
>>>>>
>>>>> Technically, the term in the WHERE clause should prevent a 'none'
>>>>> result
>>>>> but
>>>>> I put it there to help future-proof the code.
>>>>
>>>>
>>>>
>>>> Won't that find the first one of the f_tags that is not null, but not
>>>> necessarily the one that was matched by the where clause?
>>>>
>>>
>>> I slightly cheated in my example.
>>>
>>> My CASE...END was listing terms in the same order as the COALESCE()
>>> function
>>> you were using in the WHERE clause. The cheat was that only a non-null
>>> value
>>> could be TRUE. To be more accurate, I should have used
>>> ... WHEN f_tag_bottom IS NOT NULL THEN ...
>>> That way you end up with a true boolean check within the CASE decision
>>> tree.
>>>
>>> As the COALESCE() is testing its terms in the same sequence as the
>>> CASE...END, there should be no difference between the two checks.  But,
>>> that
>>> also adds to the maintenance cost of this query. If you should change the
>>> order of the f_tag checks in the COALESCE() function, you would need to
>>> change the CASE...END to the same sequence.
>>
>>
>> Yes, I see that, but does the case only look at the filtered rows? For
>> example, lets say there's this data:
>>
>> row 1: f_tag_bottom = "ABC"
>> row 2: f_tag_bottom_major_axis = "XYZ"
>>
>> and my where clause has this:
>>
>> WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'XYZ'
>>
>> won't the CASE pick up row 1? Whereas I want it to pick up row 2.
>>
>
> Yes it would.
>
> Just modify the check in the case from "IS NOT NULL" to "='XYZ'" to make
> both functions (CASE and COALESCE) find the same field value in the same row
> at the same time.

Thanks very much Shawn.

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

Re: using alias in where clause

[Larry Martell]

On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor  wrote:
> 2016/01/25 19:16 ... Larry Martell:
>>
>> SELECT IFNULL(f_tag_bottom,
>>  IFNULL(f_tag_bottom_major_axis,
>>   IFNULL(f_tag_bottom_minor_axis,
>>IFNULL(f_tag_ch_x_bottom,
>> IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
>> STDDEV(ch_x_top)
>> 
>
> Of course, this isn't your real problem, but you could use COALESCE instead
> of all those IFNULLs (and you don't need the last one):
> SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag,
> STDDEV(ch_x_top)
> 
>
> As Johnny Withers points out, you may repeat the expression in the
> WHERE-clause:
> WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'E-CD7'
> If really only one of those is not NULL, it is equivalent to this:
> 'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis,
> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom)

Many thanks to Hal.sz and Johnny - I had forgotten about coalesce and
I didn't know I could use that in a where clause. This worked great
for the requirement I had, but of course, once that was implemented my
client changed the requirements. Now they want to know which of the 5
f_tag_* columns was matched. Not sure how I'll do that. Probably need
another query.

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

Re: using alias in where clause

[shawn l.green]

On 1/28/2016 1:14 PM, Larry Martell wrote:

On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor  wrote:

2016/01/25 19:16 ... Larry Martell:


SELECT IFNULL(f_tag_bottom,
  IFNULL(f_tag_bottom_major_axis,
   IFNULL(f_tag_bottom_minor_axis,
IFNULL(f_tag_ch_x_bottom,
 IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
 STDDEV(ch_x_top)



Of course, this isn't your real problem, but you could use COALESCE instead
of all those IFNULLs (and you don't need the last one):
 SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag,
 STDDEV(ch_x_top)
 

As Johnny Withers points out, you may repeat the expression in the
WHERE-clause:
 WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'E-CD7'
If really only one of those is not NULL, it is equivalent to this:
 'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom)


Many thanks to Hal.sz and Johnny - I had forgotten about coalesce and
I didn't know I could use that in a where clause. This worked great
for the requirement I had, but of course, once that was implemented my
client changed the requirements. Now they want to know which of the 5
f_tag_* columns was matched. Not sure how I'll do that. Probably need
another query.

One option to consider is to add another column to the query with a CASE 
similar to this...


SELECT
, ... original fields ...
, CASE
  WHEN f_tag_bottom THEN 'f_tag_bottom'
  WHEN f_tag_bottom_major_axis THEN 'f_tag_bottom_major_axis'
  ... repeat for the rest of the fields to test ...
  ELSE 'none'
  END as match_flag
FROM ...

Technically, the term in the WHERE clause should prevent a 'none' result 
but I put it there to help future-proof the code.


--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


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

Re: using alias in where clause

[shawn l.green]

On 1/28/2016 3:32 PM, Larry Martell wrote:

On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green <shawn.l.gr...@oracle.com> wrote:



On 1/28/2016 1:14 PM, Larry Martell wrote:


On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <h...@tbbs.net> wrote:


2016/01/25 19:16 ... Larry Martell:



SELECT IFNULL(f_tag_bottom,
   IFNULL(f_tag_bottom_major_axis,
IFNULL(f_tag_bottom_minor_axis,
 IFNULL(f_tag_ch_x_bottom,
  IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
  STDDEV(ch_x_top)




Of course, this isn't your real problem, but you could use COALESCE
instead
of all those IFNULLs (and you don't need the last one):
  SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag,
  STDDEV(ch_x_top)
  

As Johnny Withers points out, you may repeat the expression in the
WHERE-clause:
  WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'E-CD7'
If really only one of those is not NULL, it is equivalent to this:
  'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom)



Many thanks to Hal.sz and Johnny - I had forgotten about coalesce and
I didn't know I could use that in a where clause. This worked great
for the requirement I had, but of course, once that was implemented my
client changed the requirements. Now they want to know which of the 5
f_tag_* columns was matched. Not sure how I'll do that. Probably need
another query.


One option to consider is to add another column to the query with a CASE
similar to this...

SELECT
, ... original fields ...
, CASE
   WHEN f_tag_bottom THEN 'f_tag_bottom'
   WHEN f_tag_bottom_major_axis THEN 'f_tag_bottom_major_axis'
   ... repeat for the rest of the fields to test ...
   ELSE 'none'
   END as match_flag
FROM ...

Technically, the term in the WHERE clause should prevent a 'none' result but
I put it there to help future-proof the code.


Won't that find the first one of the f_tags that is not null, but not
necessarily the one that was matched by the where clause?



I slightly cheated in my example.

My CASE...END was listing terms in the same order as the COALESCE() 
function you were using in the WHERE clause. The cheat was that only a 
non-null value could be TRUE. To be more accurate, I should have used

   ... WHEN f_tag_bottom IS NOT NULL THEN ...
That way you end up with a true boolean check within the CASE decision 
tree.


As the COALESCE() is testing its terms in the same sequence as the 
CASE...END, there should be no difference between the two checks.  But, 
that also adds to the maintenance cost of this query. If you should 
change the order of the f_tag checks in the COALESCE() function, you 
would need to change the CASE...END to the same sequence.


Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


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

Re: using alias in where clause

[Larry Martell]

On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green  wrote:
>
>
> On 1/28/2016 1:14 PM, Larry Martell wrote:
>>
>> On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor  wrote:
>>>
>>> 2016/01/25 19:16 ... Larry Martell:


 SELECT IFNULL(f_tag_bottom,
   IFNULL(f_tag_bottom_major_axis,
IFNULL(f_tag_bottom_minor_axis,
 IFNULL(f_tag_ch_x_bottom,
  IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
  STDDEV(ch_x_top)
 
>>>
>>>
>>> Of course, this isn't your real problem, but you could use COALESCE
>>> instead
>>> of all those IFNULLs (and you don't need the last one):
>>>  SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag,
>>>  STDDEV(ch_x_top)
>>>  
>>>
>>> As Johnny Withers points out, you may repeat the expression in the
>>> WHERE-clause:
>>>  WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'E-CD7'
>>> If really only one of those is not NULL, it is equivalent to this:
>>>  'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis,
>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom)
>>
>>
>> Many thanks to Hal.sz and Johnny - I had forgotten about coalesce and
>> I didn't know I could use that in a where clause. This worked great
>> for the requirement I had, but of course, once that was implemented my
>> client changed the requirements. Now they want to know which of the 5
>> f_tag_* columns was matched. Not sure how I'll do that. Probably need
>> another query.
>>
> One option to consider is to add another column to the query with a CASE
> similar to this...
>
> SELECT
> , ... original fields ...
> , CASE
>   WHEN f_tag_bottom THEN 'f_tag_bottom'
>   WHEN f_tag_bottom_major_axis THEN 'f_tag_bottom_major_axis'
>   ... repeat for the rest of the fields to test ...
>   ELSE 'none'
>   END as match_flag
> FROM ...
>
> Technically, the term in the WHERE clause should prevent a 'none' result but
> I put it there to help future-proof the code.

Won't that find the first one of the f_tags that is not null, but not
necessarily the one that was matched by the where clause?

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

Re: using alias in where clause

[Larry Martell]

On Thu, Jan 28, 2016 at 5:45 PM, shawn l.green <shawn.l.gr...@oracle.com> wrote:
>
>
> On 1/28/2016 3:32 PM, Larry Martell wrote:
>>
>> On Thu, Jan 28, 2016 at 2:13 PM, shawn l.green <shawn.l.gr...@oracle.com>
>> wrote:
>>>
>>>
>>>
>>> On 1/28/2016 1:14 PM, Larry Martell wrote:
>>>>
>>>>
>>>> On Tue, Jan 26, 2016 at 8:40 AM, Hal.sz S.ndor <h...@tbbs.net> wrote:
>>>>>
>>>>>
>>>>> 2016/01/25 19:16 ... Larry Martell:
>>>>>>
>>>>>>
>>>>>>
>>>>>> SELECT IFNULL(f_tag_bottom,
>>>>>>IFNULL(f_tag_bottom_major_axis,
>>>>>> IFNULL(f_tag_bottom_minor_axis,
>>>>>>  IFNULL(f_tag_ch_x_bottom,
>>>>>>   IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
>>>>>>   STDDEV(ch_x_top)
>>>>>> 
>>>>>
>>>>>
>>>>>
>>>>> Of course, this isn't your real problem, but you could use COALESCE
>>>>> instead
>>>>> of all those IFNULLs (and you don't need the last one):
>>>>>   SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
>>>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag,
>>>>>   STDDEV(ch_x_top)
>>>>>   
>>>>>
>>>>> As Johnny Withers points out, you may repeat the expression in the
>>>>> WHERE-clause:
>>>>>   WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
>>>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) =
>>>>> 'E-CD7'
>>>>> If really only one of those is not NULL, it is equivalent to this:
>>>>>   'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis,
>>>>> f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom)
>>>>
>>>>
>>>>
>>>> Many thanks to Hal.sz and Johnny - I had forgotten about coalesce and
>>>> I didn't know I could use that in a where clause. This worked great
>>>> for the requirement I had, but of course, once that was implemented my
>>>> client changed the requirements. Now they want to know which of the 5
>>>> f_tag_* columns was matched. Not sure how I'll do that. Probably need
>>>> another query.
>>>>
>>> One option to consider is to add another column to the query with a CASE
>>> similar to this...
>>>
>>> SELECT
>>> , ... original fields ...
>>> , CASE
>>>WHEN f_tag_bottom THEN 'f_tag_bottom'
>>>WHEN f_tag_bottom_major_axis THEN 'f_tag_bottom_major_axis'
>>>... repeat for the rest of the fields to test ...
>>>ELSE 'none'
>>>END as match_flag
>>> FROM ...
>>>
>>> Technically, the term in the WHERE clause should prevent a 'none' result
>>> but
>>> I put it there to help future-proof the code.
>>
>>
>> Won't that find the first one of the f_tags that is not null, but not
>> necessarily the one that was matched by the where clause?
>>
>
> I slightly cheated in my example.
>
> My CASE...END was listing terms in the same order as the COALESCE() function
> you were using in the WHERE clause. The cheat was that only a non-null value
> could be TRUE. To be more accurate, I should have used
>... WHEN f_tag_bottom IS NOT NULL THEN ...
> That way you end up with a true boolean check within the CASE decision tree.
>
> As the COALESCE() is testing its terms in the same sequence as the
> CASE...END, there should be no difference between the two checks.  But, that
> also adds to the maintenance cost of this query. If you should change the
> order of the f_tag checks in the COALESCE() function, you would need to
> change the CASE...END to the same sequence.

Yes, I see that, but does the case only look at the filtered rows? For
example, lets say there's this data:

row 1: f_tag_bottom = "ABC"
row 2: f_tag_bottom_major_axis = "XYZ"

and my where clause has this:

WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis,
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'XYZ'

won't the CASE pick up row 1? Whereas I want it to pick up row 2.

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

Re: using alias in where clause

[Hal.sz S.ndor]

2016/01/25 19:16 ... Larry Martell:

SELECT IFNULL(f_tag_bottom,
 IFNULL(f_tag_bottom_major_axis,
  IFNULL(f_tag_bottom_minor_axis,
   IFNULL(f_tag_ch_x_bottom,
IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
STDDEV(ch_x_top)

Of course, this isn't your real problem, but you could use COALESCE 
instead of all those IFNULLs (and you don't need the last one):
	SELECT COALESCE(f_tag_bottom, f_tag_bottom_major_axis, 
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) as ftag,

STDDEV(ch_x_top)


As Johnny Withers points out, you may repeat the expression in the 
WHERE-clause:
	WHERE COALESCE(f_tag_bottom, f_tag_bottom_major_axis, 
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom) = 'E-CD7'

If really only one of those is not NULL, it is equivalent to this:
	'E-CD7' IN (f_tag_bottom, f_tag_bottom_major_axis, 
f_tag_bottom_minor_axis, f_tag_ch_x_bottom, f_tag_ch_y_bottom)


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

Re: using alias in where clause

[Johnny Withers]

On Mon, Jan 25, 2016 at 9:32 PM, Larry Martell 
wrote:

> On Mon, Jan 25, 2016 at 8:26 PM, Johnny Withers 
> wrote:
> > You should probably turn this into a UNION and put an index on each
> column:
> >
> > SELECT f_tag_ch_y_bottom AS ftag FROM data_cst WHERE f_tag_ch_y_bottom =
> > 'E-CD7'
> > UNION ALL
> > SELECT f_tag_ch_x_bottom AS ftag FROM data_cst WHERE f_tag_ch_x_bottom =
> > 'E-CD7'
> > UNION ALL
> > SELECT f_tag_bottom_minor_axis AS ftag FROM data_cst WHERE
> > f_tag_bottom_minor_axis = 'E-CD7'
> > UNION ALL
> > SELECT f_tag_bottom_major_axis AS ftag FROM data_cst WHERE
> > f_tag_bottom_major_axis = 'E-CD7'
> > UNION ALL
> > SELECT f_tag_bottom FROM data_cst AS ftag WHERE f_tag_bottom = 'E-CD7'
> > ;
>
> This may work for me, but I need to explore this more tomorrow. I need
> the select to control the rows included in the aggregation. For
> example, the rows where the f_tag_* col that is used does not =
> 'E-CD7' should not be included in the aggregation. Also, I grossly
> simplified the query for this post. In reality I have 15 items in the
> where clause and a having as well.
>

I see, I missed the STDDEV() function you had, perhaps you could add that
column to each SELECT in the untion, then wrap the entire union inside
another select:

SELECT ftag, STDDEV(ch_x_top) FROM (
..union stuff here...
)


>
> > Doing this any other way will prevent index usage and a full table scan
> will
> > be required.
>
> Yes, I will be adding indices - right now I am just worried about
> getting the query to work. But this is important as it's a big table.
> So I do appreciate you mentioning it.
>

In reality, you could repeat the IFNULL(...) in the where clause the same
way you have it in the column list. Not the optimal solution but it'd work
for a proof of concept.


>
> > Is there a possibility of more than one column matching? How would you
> > handle that?
>
> I was told only 1 of the 5 will be populated and the other 4 will be
> null. But still, I said I have to code for the case where that is not
> true. So then I was told to use the first one I find that is not null,
> looking in the order I had in my original post.
>

You could also wrap another select around the union to handle more than one
of the columns having a value. You could use the outer select to pick the
one you wanted, something similar to:

SELECT
IFNULL(f_tag_ch_y_bottom,IFULL(f_tag_ch_x_bottom,IFNULL(,STDEV(ch_x_top)
FROM (
SELECT f_tag_ch_y_bottom,NULL as f_tag_ch_x_bottom,NULL AS
f_tag_bottom_minor,..., ch_x_top FROM data_cst WHERE f_tag_ch_y_bottom =
'E-CD7'
UNION ALL
SELECT NULL AS f_tag_ch_y_bottom, f_tag_ch_x_bottom,NULL AS
f_tag_bottom_minor,...,ch_x_top FROM data_cst WHERE f_tag_ch_x_bottom
= 'E-CD7'
UNION
)

And so on for each column/query.


>
>
> > On Mon, Jan 25, 2016 at 6:16 PM, Larry Martell 
> > wrote:
> >>
> >> I know I cannot use an alias in a where clause, but I am trying to
> >> figure out how to achieve what I need.
> >>
> >> If I could have an alias in a where clause my sql would look like this:
> >>
> >> SELECT IFNULL(f_tag_bottom,
> >> IFNULL(f_tag_bottom_major_axis,
> >>  IFNULL(f_tag_bottom_minor_axis,
> >>   IFNULL(f_tag_ch_x_bottom,
> >>IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
> >>STDDEV(ch_x_top)
> >> FROM data_cst
> >> WHERE ftag = 'E-CD7'
> >> GROUP BY wafer_id, lot_id
> >>
> >> But I can't use ftag in the where clause. I can't put it in a having
> >> clause, as that would exclude the already aggregated rows and I want
> >> to filter then before the aggregation. Anyone have any idea how I can
> >> do this?
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:http://lists.mysql.com/mysql
> >>
> >
> >
> >
> > --
> > -
> > Johnny Withers
> > 601.209.4985
> > joh...@pixelated.net
>



-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

Re: using alias in where clause

[Larry Martell]

On Mon, Jan 25, 2016 at 8:26 PM, Johnny Withers  wrote:
> You should probably turn this into a UNION and put an index on each column:
>
> SELECT f_tag_ch_y_bottom AS ftag FROM data_cst WHERE f_tag_ch_y_bottom =
> 'E-CD7'
> UNION ALL
> SELECT f_tag_ch_x_bottom AS ftag FROM data_cst WHERE f_tag_ch_x_bottom =
> 'E-CD7'
> UNION ALL
> SELECT f_tag_bottom_minor_axis AS ftag FROM data_cst WHERE
> f_tag_bottom_minor_axis = 'E-CD7'
> UNION ALL
> SELECT f_tag_bottom_major_axis AS ftag FROM data_cst WHERE
> f_tag_bottom_major_axis = 'E-CD7'
> UNION ALL
> SELECT f_tag_bottom FROM data_cst AS ftag WHERE f_tag_bottom = 'E-CD7'
> ;

This may work for me, but I need to explore this more tomorrow. I need
the select to control the rows included in the aggregation. For
example, the rows where the f_tag_* col that is used does not =
'E-CD7' should not be included in the aggregation. Also, I grossly
simplified the query for this post. In reality I have 15 items in the
where clause and a having as well.

> Doing this any other way will prevent index usage and a full table scan will
> be required.

Yes, I will be adding indices - right now I am just worried about
getting the query to work. But this is important as it's a big table.
So I do appreciate you mentioning it.

> Is there a possibility of more than one column matching? How would you
> handle that?

I was told only 1 of the 5 will be populated and the other 4 will be
null. But still, I said I have to code for the case where that is not
true. So then I was told to use the first one I find that is not null,
looking in the order I had in my original post.


> On Mon, Jan 25, 2016 at 6:16 PM, Larry Martell 
> wrote:
>>
>> I know I cannot use an alias in a where clause, but I am trying to
>> figure out how to achieve what I need.
>>
>> If I could have an alias in a where clause my sql would look like this:
>>
>> SELECT IFNULL(f_tag_bottom,
>> IFNULL(f_tag_bottom_major_axis,
>>  IFNULL(f_tag_bottom_minor_axis,
>>   IFNULL(f_tag_ch_x_bottom,
>>IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
>>STDDEV(ch_x_top)
>> FROM data_cst
>> WHERE ftag = 'E-CD7'
>> GROUP BY wafer_id, lot_id
>>
>> But I can't use ftag in the where clause. I can't put it in a having
>> clause, as that would exclude the already aggregated rows and I want
>> to filter then before the aggregation. Anyone have any idea how I can
>> do this?
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql
>>
>
>
>
> --
> -
> Johnny Withers
> 601.209.4985
> joh...@pixelated.net

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

Re: using alias in where clause

[Johnny Withers]

You should probably turn this into a UNION and put an index on each column:

SELECT f_tag_ch_y_bottom AS ftag FROM data_cst WHERE f_tag_ch_y_bottom =
'E-CD7'
UNION ALL
SELECT f_tag_ch_x_bottom AS ftag FROM data_cst WHERE f_tag_ch_x_bottom =
'E-CD7'
UNION ALL
SELECT f_tag_bottom_minor_axis AS ftag FROM data_cst WHERE
f_tag_bottom_minor_axis = 'E-CD7'
UNION ALL
SELECT f_tag_bottom_major_axis AS ftag FROM data_cst WHERE
f_tag_bottom_major_axis = 'E-CD7'
UNION ALL
SELECT f_tag_bottom FROM data_cst AS ftag WHERE f_tag_bottom = 'E-CD7'
;

Doing this any other way will prevent index usage and a full table scan
will be required.

Is there a possibility of more than one column matching? How would you
handle that?


On Mon, Jan 25, 2016 at 6:16 PM, Larry Martell 
wrote:

> I know I cannot use an alias in a where clause, but I am trying to
> figure out how to achieve what I need.
>
> If I could have an alias in a where clause my sql would look like this:
>
> SELECT IFNULL(f_tag_bottom,
> IFNULL(f_tag_bottom_major_axis,
>  IFNULL(f_tag_bottom_minor_axis,
>   IFNULL(f_tag_ch_x_bottom,
>IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
>STDDEV(ch_x_top)
> FROM data_cst
> WHERE ftag = 'E-CD7'
> GROUP BY wafer_id, lot_id
>
> But I can't use ftag in the where clause. I can't put it in a having
> clause, as that would exclude the already aggregated rows and I want
> to filter then before the aggregation. Anyone have any idea how I can
> do this?
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>


-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

Re: using alias in where clause

[Reindl Harald]

Am 26.01.2016 um 01:16 schrieb Larry Martell:

I know I cannot use an alias in a where clause, but I am trying to
figure out how to achieve what I need.

If I could have an alias in a where clause my sql would look like this:

SELECT IFNULL(f_tag_bottom,
 IFNULL(f_tag_bottom_major_axis,
  IFNULL(f_tag_bottom_minor_axis,
   IFNULL(f_tag_ch_x_bottom,
IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
STDDEV(ch_x_top)
FROM data_cst
WHERE ftag = 'E-CD7'
GROUP BY wafer_id, lot_id

But I can't use ftag in the where clause. I can't put it in a having
clause, as that would exclude the already aggregated rows and I want
to filter then before the aggregation. Anyone have any idea how I can
do this?


why would you want to do that?
it would make the where clause not be able to use proper indexes



signature.asc
Description: OpenPGP digital signature

Re: using alias in where clause

[Rebecca Love]

Have you tried using a select case statement for ftag?


> On Jan 25, 2016, at 6:39 PM, Larry Martell <larry.mart...@gmail.com> wrote:
> 
> On Mon, Jan 25, 2016 at 7:27 PM, Reindl Harald <h.rei...@thelounge.net> wrote:
>> 
>> 
>> Am 26.01.2016 um 01:16 schrieb Larry Martell:
>>> 
>>> I know I cannot use an alias in a where clause, but I am trying to
>>> figure out how to achieve what I need.
>>> 
>>> If I could have an alias in a where clause my sql would look like this:
>>> 
>>> SELECT IFNULL(f_tag_bottom,
>>> IFNULL(f_tag_bottom_major_axis,
>>>  IFNULL(f_tag_bottom_minor_axis,
>>>   IFNULL(f_tag_ch_x_bottom,
>>>IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
>>>STDDEV(ch_x_top)
>>> FROM data_cst
>>> WHERE ftag = 'E-CD7'
>>> GROUP BY wafer_id, lot_id
>>> 
>>> But I can't use ftag in the where clause. I can't put it in a having
>>> clause, as that would exclude the already aggregated rows and I want
>>> to filter then before the aggregation. Anyone have any idea how I can
>>> do this?
>> 
>> 
>> why would you want to do that?
>> it would make the where clause not be able to use proper indexes
> 
> I do not know which of the 5 f_tag_* columns will be populated and I
> need to filter on the one that is populated.
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
> 


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

using alias in where clause

[Larry Martell]

I know I cannot use an alias in a where clause, but I am trying to
figure out how to achieve what I need.

If I could have an alias in a where clause my sql would look like this:

SELECT IFNULL(f_tag_bottom,
IFNULL(f_tag_bottom_major_axis,
 IFNULL(f_tag_bottom_minor_axis,
  IFNULL(f_tag_ch_x_bottom,
   IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
   STDDEV(ch_x_top)
FROM data_cst
WHERE ftag = 'E-CD7'
GROUP BY wafer_id, lot_id

But I can't use ftag in the where clause. I can't put it in a having
clause, as that would exclude the already aggregated rows and I want
to filter then before the aggregation. Anyone have any idea how I can
do this?

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

Re: using alias in where clause

[Larry Martell]

On Mon, Jan 25, 2016 at 7:27 PM, Reindl Harald  wrote:
>
>
> Am 26.01.2016 um 01:16 schrieb Larry Martell:
>>
>> I know I cannot use an alias in a where clause, but I am trying to
>> figure out how to achieve what I need.
>>
>> If I could have an alias in a where clause my sql would look like this:
>>
>> SELECT IFNULL(f_tag_bottom,
>>  IFNULL(f_tag_bottom_major_axis,
>>   IFNULL(f_tag_bottom_minor_axis,
>>IFNULL(f_tag_ch_x_bottom,
>> IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
>> STDDEV(ch_x_top)
>> FROM data_cst
>> WHERE ftag = 'E-CD7'
>> GROUP BY wafer_id, lot_id
>>
>> But I can't use ftag in the where clause. I can't put it in a having
>> clause, as that would exclude the already aggregated rows and I want
>> to filter then before the aggregation. Anyone have any idea how I can
>> do this?
>
>
> why would you want to do that?
> it would make the where clause not be able to use proper indexes

I do not know which of the 5 f_tag_* columns will be populated and I
need to filter on the one that is populated.

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

Re: using alias in where clause

[Larry Martell]

On Mon, Jan 25, 2016 at 8:01 PM, Rebecca Love <wacce...@gmail.com> wrote:
> Have you tried using a select case statement for ftag?

How would that help? Unless I'm missing something, I'd still have to
have a reference to the column alias in the where clause.


>> On Jan 25, 2016, at 6:39 PM, Larry Martell <larry.mart...@gmail.com> wrote:
>>
>> On Mon, Jan 25, 2016 at 7:27 PM, Reindl Harald <h.rei...@thelounge.net> 
>> wrote:
>>>
>>>
>>> Am 26.01.2016 um 01:16 schrieb Larry Martell:
>>>>
>>>> I know I cannot use an alias in a where clause, but I am trying to
>>>> figure out how to achieve what I need.
>>>>
>>>> If I could have an alias in a where clause my sql would look like this:
>>>>
>>>> SELECT IFNULL(f_tag_bottom,
>>>> IFNULL(f_tag_bottom_major_axis,
>>>>  IFNULL(f_tag_bottom_minor_axis,
>>>>   IFNULL(f_tag_ch_x_bottom,
>>>>IFNULL(f_tag_ch_y_bottom, NULL) as ftag,
>>>>STDDEV(ch_x_top)
>>>> FROM data_cst
>>>> WHERE ftag = 'E-CD7'
>>>> GROUP BY wafer_id, lot_id
>>>>
>>>> But I can't use ftag in the where clause. I can't put it in a having
>>>> clause, as that would exclude the already aggregated rows and I want
>>>> to filter then before the aggregation. Anyone have any idea how I can
>>>> do this?
>>>
>>>
>>> why would you want to do that?
>>> it would make the where clause not be able to use proper indexes
>>
>> I do not know which of the 5 f_tag_* columns will be populated and I
>> need to filter on the one that is populated.
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql
>>
>

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

Re: Using Joins/Unions

[Wm Mussatto]

On Tue, August 4, 2015 16:05, Ryan Coleman wrote:
 No but there should be. If there's not my task is useless.

 Secondly yes. Unique name on it too.

 --
 Ryan Coleman
 Publisher, d3photography.com
 ryan.cole...@cwis.biz
 m. 651.373.5015
 o. 612.568.2749

 On Aug 4, 2015, at 17:33, Wm Mussatto mussa...@csz.com wrote:

 On Tue, August 4, 2015 11:19, Ryan Coleman wrote:
 I have been a MySQL user and supporter for over a decade (since 2001)
 and
 I am almost ashamed to admit that I haven’t the faintest idea on how to
 do
 joins and unions.

 I have a specific query I would love to run…

 I have two tables, one with Unique data (“images”) and one with
 corresponding paths but many replicated records (“files”).

 I want to run a query that takes the results from /images/ and also
 searches /images.file/ as a LIKE statement from /files.path/, sort by
 /files.filesize/ in descending order returning just the first record
 (largest file size).  There may be up to 750 records from /images/ and
 thusly could be 3000+ from /files/.

 How on earth do I do this?

 —
 Ryan
 First question, will there always be at least one record in the files
 table for every record in the images table?  That controls the kind of
 join you will use.  I don't think that a union is a player. Also, is
 there
 a unique record ID in each of the table?

Sorry, been swamped.  If you can ignore the cases where there are not any
entry in the 'Files' table then a simple join will work. Otherwise you
would need a LEFT JOIN

Assume a structure
images.id - unique record ID
images.commonName - this will be the common reference name in both
...
files.id - unique record ID
files.commmonName - this will be the common reference name in both
files.location -where this record is on the disk/system
files.filesize - numeric field in whatever units you want (say bytes)


select images.commonName, files.location,   MAX(files.filesize)
FROM images, files
WHERE images.commonName =  files.commonName
GROUP BY files.commonName
ORDER BY images.commonName

Here is my test structure.   No doubt someone else can get it optimized. 
This seems to use a temp table
--
-- Table structure for table `files`
--

CREATE TABLE `files` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CommonName` varchar(255) NOT NULL,
  `Location` varchar(255) NOT NULL,
  `filesize` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `CommonName` (`CommonName`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

--
-- Dumping data for table `files`
--

INSERT INTO `files` (`ID`, `CommonName`, `Location`, `filesize`) VALUES
(1, 'Image1', 'FileLoc/1/image1.jpg', 1000),
(2, 'Image1', 'FileLoc/2/image1.jpg', 5),
(3, 'Image2', 'FileLoc/1/image2.jpg', 25000),
(4, 'Image2', 'FileLoc/2/image2.jpg', 5000);

-- 

--
-- Table structure for table `images`
--

CREATE TABLE `images` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CommonName` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `CommonName` (`CommonName`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

--
-- Dumping data for table `images`
--

INSERT INTO `images` (`ID`, `CommonName`) VALUES
(1, 'Image1'),
(2, 'Image2');

Hope this helps a bit.

---

William R. Mussatto, Senior Systems Engineer
http://www.csz.com
Ph. 909-920-9154 ext 101
Direct: 909-962-8547
__
CONFIDENTIALITY NOTICE:
This communication, including attachments, is for the exclusive use of the
person or entity to which it is addressed and may contain confidential,
proprietary and/or privileged information. Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient
is prohibited. If you received this by mistake, please contact the sender
immediately.


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

Re: Using Joins/Unions

[Ryan Coleman]

No but there should be. If there's not my task is useless. 

Secondly yes. Unique name on it too. 

--
Ryan Coleman
Publisher, d3photography.com
ryan.cole...@cwis.biz
m. 651.373.5015
o. 612.568.2749

 On Aug 4, 2015, at 17:33, Wm Mussatto mussa...@csz.com wrote:
 
 On Tue, August 4, 2015 11:19, Ryan Coleman wrote:
 I have been a MySQL user and supporter for over a decade (since 2001) and
 I am almost ashamed to admit that I haven’t the faintest idea on how to do
 joins and unions.
 
 I have a specific query I would love to run…
 
 I have two tables, one with Unique data (“images”) and one with
 corresponding paths but many replicated records (“files”).
 
 I want to run a query that takes the results from /images/ and also
 searches /images.file/ as a LIKE statement from /files.path/, sort by
 /files.filesize/ in descending order returning just the first record
 (largest file size).  There may be up to 750 records from /images/ and
 thusly could be 3000+ from /files/.
 
 How on earth do I do this?
 
 —
 Ryan
 First question, will there always be at least one record in the files
 table for every record in the images table?  That controls the kind of
 join you will use.  I don't think that a union is a player. Also, is there
 a unique record ID in each of the table?
 --
 William R. Mussatto
 Systems Engineer
 http://www.csz.com
 909-920-9154
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 

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

Using Joins/Unions

[Ryan Coleman]

I have been a MySQL user and supporter for over a decade (since 2001) and I am 
almost ashamed to admit that I haven’t the faintest idea on how to do joins and 
unions.

I have a specific query I would love to run…

I have two tables, one with Unique data (“images”) and one with corresponding 
paths but many replicated records (“files”).

I want to run a query that takes the results from /images/ and also searches 
/images.file/ as a LIKE statement from /files.path/, sort by /files.filesize/ 
in descending order returning just the first record (largest file size).  There 
may be up to 750 records from /images/ and thusly could be 3000+ from /files/.

How on earth do I do this?

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

Re: Using Joins/Unions

[Wm Mussatto]

On Tue, August 4, 2015 11:19, Ryan Coleman wrote:
 I have been a MySQL user and supporter for over a decade (since 2001) and
 I am almost ashamed to admit that I haven’t the faintest idea on how to do
 joins and unions.

 I have a specific query I would love to run…

 I have two tables, one with Unique data (“images”) and one with
 corresponding paths but many replicated records (“files”).

 I want to run a query that takes the results from /images/ and also
 searches /images.file/ as a LIKE statement from /files.path/, sort by
 /files.filesize/ in descending order returning just the first record
 (largest file size).  There may be up to 750 records from /images/ and
 thusly could be 3000+ from /files/.

 How on earth do I do this?

 —
 Ryan
First question, will there always be at least one record in the files
table for every record in the images table?  That controls the kind of
join you will use.  I don't think that a union is a player. Also, is there
a unique record ID in each of the table?
--
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154


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

ORDER BY not using index?

[Chris Knipe]

Hi,

Can someone perhaps assist with the below...  I'm not sure at all why my
index aren't being used for the ORDER BY.  Currently some 443K records in
the table, but this will grow to a good few million.  I simply cannot,
afford a filesort.


mysql SELECT COUNT(*) FROM myTable;
+--+
| COUNT(*) |
+--+
|   443808 |
+--+
1 row in set (0.00 sec)

mysql EXPLAIN SELECT * FROM myTable ORDER BY DateAccessed;
++-+--+--+---+--+-+--+++
| id | select_type | table| type | possible_keys | key  | key_len |
ref  | rows   | Extra  |
++-+--+--+---+--+-+--+++
|  1 | SIMPLE  | myTable | ALL  | NULL  | NULL | NULL| NULL
| 443808 | Using filesort |
++-+--+--+---+--+-+--+++
1 row in set (0.00 sec)

mysql EXPLAIN SELECT * FROM myTable FORCE INDEX (idx_DateAccessed) ORDER
BY DateAccessed;
++-+--+---+---+--+-+--++---+
| id | select_type | table| type  | possible_keys | key
 | key_len | ref  | rows   | Extra |
++-+--+---+---+--+-+--++---+
|  1 | SIMPLE  | myTable | index | NULL  | idx_DateAccessed | 4
  | NULL | 443808 |   |
++-+--+---+---+--+-+--++---+
1 row in set (0.00 sec)

mysql SHOW CREATE TABLE myTable;
+--+-+
| Table| Create Table



 |
+--+-+
| myTable | CREATE TABLE `myTable` (
  `ArticleID` char(32) NOT NULL,
  `DateObtained` int(10) unsigned NOT NULL,
  `DateAccessed` int(10) unsigned NOT NULL,
  `TimesAccessed` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ArticleID`),
  KEY `idx_DateAccessed` (`DateAccessed`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1 |
+--+-+
1 row in set (0.00 sec)

mysql SHOW INDEX FROM myTable;
+--++--+--+--+---+-+--++--++-+---+
| Table| Non_unique | Key_name | Seq_in_index | Column_name
 | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment | Index_comment |
+--++--+--+--+---+-+--++--++-+---+
| myTable |  0 | PRIMARY  |1 | ArticleID| A
|  443808 | NULL | NULL   |  | BTREE  | |
|
| myTable |  1 | idx_DateAccessed |1 | DateAccessed | A
|  147936 | NULL | NULL   |  | BTREE  | |
|
+--++--+--+--+---+-+--++--++-+---+
2 rows in set (0.00 sec)


-- 

Regards,
Chris Knipe

Re: ORDER BY not using index?

[yoku ts.]

Hi,

Your query have to access all rows in `myTable`, thus MySQL optimizer
guesses reading sequentially is faster than working through an
index.
http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html


The case of not using index,
* Reading whole myTable.MYD sequentially
* Sorting 443k rows

The case of using index,
* Reading all of idx_DateAccessed
* Reading whole myTable.MYD *randomly*

MySQL would like to avoid randomly scan, maybe.
You can avoid filesort with FORCE INDEX, as you tell.


Regards,



2015-07-18 16:32 GMT+09:00 Chris Knipe sav...@savage.za.org:
 Hi,

 Can someone perhaps assist with the below...  I'm not sure at all why my
 index aren't being used for the ORDER BY.  Currently some 443K records in
 the table, but this will grow to a good few million.  I simply cannot,
 afford a filesort.


 mysql SELECT COUNT(*) FROM myTable;
 +--+
 | COUNT(*) |
 +--+
 |   443808 |
 +--+
 1 row in set (0.00 sec)

 mysql EXPLAIN SELECT * FROM myTable ORDER BY DateAccessed;
 ++-+--+--+---+--+-+--+++
 | id | select_type | table| type | possible_keys | key  | key_len |
 ref  | rows   | Extra  |
 ++-+--+--+---+--+-+--+++
 |  1 | SIMPLE  | myTable | ALL  | NULL  | NULL | NULL| NULL
 | 443808 | Using filesort |
 ++-+--+--+---+--+-+--+++
 1 row in set (0.00 sec)

 mysql EXPLAIN SELECT * FROM myTable FORCE INDEX (idx_DateAccessed) ORDER
 BY DateAccessed;
 ++-+--+---+---+--+-+--++---+
 | id | select_type | table| type  | possible_keys | key
  | key_len | ref  | rows   | Extra |
 ++-+--+---+---+--+-+--++---+
 |  1 | SIMPLE  | myTable | index | NULL  | idx_DateAccessed | 4
   | NULL | 443808 |   |
 ++-+--+---+---+--+-+--++---+
 1 row in set (0.00 sec)

 mysql SHOW CREATE TABLE myTable;
 +--+-+
 | Table| Create Table



  |
 +--+-+
 | myTable | CREATE TABLE `myTable` (
   `ArticleID` char(32) NOT NULL,
   `DateObtained` int(10) unsigned NOT NULL,
   `DateAccessed` int(10) unsigned NOT NULL,
   `TimesAccessed` int(10) unsigned NOT NULL,
   PRIMARY KEY (`ArticleID`),
   KEY `idx_DateAccessed` (`DateAccessed`) USING BTREE
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1 |
 +--+-+
 1 row in set (0.00 sec)

 mysql SHOW INDEX FROM myTable;
 +--++--+--+--+---+-+--++--++-+---+
 | Table| Non_unique | Key_name | Seq_in_index | Column_name
  | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
 Comment | Index_comment |
 +--++--+--+--+---+-+--++--++-+---+
 | myTable |  0 | PRIMARY  |1 | ArticleID| A
 |  443808 | NULL | NULL   |  | BTREE  | |
 |
 | myTable |  1 | idx_DateAccessed |1 | DateAccessed | A
 |  147936 | NULL | NULL   |  | BTREE  | |
 |
 +--++--+--+--+---+-+--++--++-+---+
 2 rows in set (0.00 sec)


 --

 Regards,
 Chris Knipe

--
MySQL General Mailing List
For list archives: http

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Roberta Jask�lski]

 2014/11/04 11:04 -0800, Jan Steinman 
I was not suggesting it for dates. The OP appeared to have a well-defined set 
of strings in a VARCHAR field — THAT is what I suggested ENUMs for!



What is the update frequency of those VARCHARs? If you're adding them often — 
or if you need to occasionally change their value — I'd use another table with 
a reference.

If they're immutable and new ones are not added often, there's no design cost 
at all to using ENUMs. I'd argue there's a higher maintenance cost to NOT using 
them! 

Ugh--I missed the discussion shift from the DATEs to the VARCHAR labels ... and 
now I wholeheartedly agree with you.

As for the DATEs, I yet suspect that for performance maybe TIMESTAMP is 
slightly better than DATE.


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

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Zbigniew]

2014-11-06 21:49 GMT+01:00, Roberta Jaskólski h...@tbbs.net:

 Ugh--I missed the discussion shift from the DATEs to the VARCHAR labels ...
 and now I wholeheartedly agree with you.

 As for the DATEs, I yet suspect that for performance maybe TIMESTAMP is
 slightly better than DATE.

Well what I'm interested in - and I was asking my original question
about - is SIGNIFICANT difference. If everything I can count for is
just slight improvements (by which I understand difference that can
be detected only by benchmarks, but not really during database
operation), then actually it doesn't make much sense, it seems.
-- 
Zbig

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

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Johan De Meersman]

- Original Message -
 From: Zbigniew zbigniew2...@gmail.com
 Subject: Using INTEGER instead of VARCHAR/DATE - is this a way to faster 
 access?

 What about using ENUMs? They have nearly the performance of INTEGERs, but
 you don't have to maintain a string mapping in your programming logic.
 
 But are you able to estimate, what boost can i notice? 5% - or 50%,
 or maybe even 500%?

I'll give you an interesting reason to switch to ENUM (or smallint, if so 
inclined): Your data fields will be smaller. That not only means more records 
in a page (might be negligable), but more importantly, it'll make the index on 
that field smaller, meaning a) more of it will remain in memory and b) lookups 
on it will be marginally faster, too.

I have no hard data on how it'll impact index performance (your dataset is 
yours to benchmark), but on one million of records (and you were talking 
several), a each byte saved is a megabyte of memory that can be used for other 
purposes, like data cache, which will speed up other things, too.


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

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

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Jan Steinman]

 From: (Halász Sándor) h...@tbbs.net
 
 2014/10/29 20:56 +0100, Zbigniew 
 if instead of textual labels I'll use SMALLINT as simple integer code for 
 each different label (and in my program a translation table)? 
 
 This, in effect, is ENUM...

Well, not really! With INTEGERs, your referential integrity is external to 
MySQL, and has to be managed.

 ... and maybe is useful if the set of dates is well determined...

I was not suggesting it for dates. The OP appeared to have a well-defined set 
of strings in a VARCHAR field — THAT is what I suggested ENUMs for!

 There is a design cost in using ENUM: If you find that your first set of 
 dates is too small, later, with ALTER TABLE, you have to change the type.

Again, the suggestion for ENUM was to replace a constrained set of VARCHARs, 
and yet, you raise a valid point.

What is the update frequency of those VARCHARs? If you're adding them often — 
or if you need to occasionally change their value — I'd use another table with 
a reference.

If they're immutable and new ones are not added often, there's no design cost 
at all to using ENUMs. I'd argue there's a higher maintenance cost to NOT using 
them!

 Jan Steinman, EcoReality Co-op 


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

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Hal�sz S�ndor]

 2014/11/02 13:19 +0100, Zbigniew 
So you guys (Jan and hsv) suggest, that switching from DATE to more
numeric data type may not be necessary, but using ENUM instead of
VARCHAR can be real performance gain, right?

But are you able to estimate, what boost can i notice? 5% - or 50%,
or maybe even 500%? 

_Maybe_! but I will not venture to measure it. I doubt that it would be big. I 
just looked ENUM up: it allows 65535 distinct values, which sounds like 16 
bits, usually two bytes, and numeric operators would be used.

 2014/11/02 11:19 -0800, Jan Steinman 
I would hope that the query optimizer converts '2014-11-02' to the three-bytes 
internal representation of DATE before doing the query, in which case, DATE 
should actually be a tiny bit faster than TIMESTAMP. 

That is doubtful. In the processors that I know, one built-in numeric operation 
is enough for either 2 bytes of ENUM or 4 bytes of TIMESTAMP, but three are 
needed for DATE. In any case, the C-overhead, since MySQL is implemented in C, 
overwhelms the cost of any single comparison. The equality comparison is at 
least simpler than an ordered comparison.

 2014/10/29 20:56 +0100, Zbigniew 
if instead of textual labels I'll use SMALLINT as simple integer code for 
each different label (and in my program a translation table)? 

This, in effect, is ENUM, and maybe is useful if the set of dates is well 
determined. If you use ENUM at least the overhead of translation is built in 
into MySQL, and, one hopes, costs less than doing it for oneself.

There is a design cost in using ENUM: If you find that your first set of dates 
is too small, later, with ALTER TABLE, you have to change the type. If you add 
the new string to the end, there is, maybe, no great cost to the adding, but if 
in the middle If every new date-string is added to the end, it will, maybe, 
be in random order. The same applys to your translation table.

I suggested TIMESTAMP because I suspect that one built-in comparison (after the 
optimizer is done with it) is enough, and it allows the appearance of real 
dates. (If the processor fetches 32 bits at a time (nowadays 64 or more is 
likly) then a 32-bit type is fetched as swiftly as any other. Both shorter and 
longer types take longer.) The more I debate this, the better I like TIMESTAMP 
for your problem.


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

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Zbigniew]

2014-10-31 5:29 GMT+01:00, Jan Steinman j...@ecoreality.org:

 What about using ENUMs? They have nearly the performance of INTEGERs, but
 you don't have to maintain a string mapping in your programming logic.

So you guys (Jan and hsv) suggest, that switching from DATE to more
numeric data type may not be necessary, but using ENUM instead of
VARCHAR can be real performance gain, right?

But are you able to estimate, what boost can i notice? 5% - or 50%,
or maybe even 500%?
-- 
Zbig

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

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Jan Steinman]

 From: Zbigniew zbigniew2...@gmail.com
 
 switching from DATE to more numeric data type may not be necessary...

I would hope that the query optimizer converts '2014-11-02' to the three-bytes 
internal representation of DATE before doing the query, in which case, DATE 
should actually be a tiny bit faster than TIMESTAMP.

 using ENUM instead of VARCHAR can be real performance gain, right?

Not just in performance, but it appears to simply be The Right Thing To Do(TM) 
in your case. (Codd Rule #10: referential integrity.)

Consider an insert into a day-of-week column (for instance) that somehow got 
Sudnay in the VARCHAR field instead of Sunday. Using an ENUM eliminates the 
possibility of a typo at a more fundamental level than your programming logic. 
If you do a massive insert with Sudnay in the ENUM field, the entire 
transaction will fail, which is really what you want rather than having to 
track down bad data after the fact, no?

If it REALLY is one value out of a known set, it SHOULD be either an ENUM, or a 
reference to another table. Use the latter technique if you need to add new 
values very often.

 But are you able to estimate, what boost can i notice? 5% - or 50%, or 
 maybe even 500%?

Very hard to say. That's like saying, If I eat well and get enough exercise, 
will I live 5% or 50% or 500% longer? Probably more like 5%, but it may FEEL 
like 500%! :-)

If the value is constrained to a set, having it as an ENUM (or reference to 
another table) will save you grief in many other ways besides boosting 
performance.

 Private enterprise, indeed, became too private. It became privileged 
enterprise, not private enterprise. -- Franklin Delano Roosevelt
 Jan Steinman, EcoReality Co-op 


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

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[hsv]

 2014/10/29 20:56 +0100, Zbigniew 
Now to the point: considering, that the second column shall contain
about 100-200 different labels - so in average many of such labels
can be repeated one million times (or even more) - will it speed-up
the selection done with something like ...WHERE label='xyz' AND
date='2013-02-25'  (and maybe the insertion as well?), if instead of
textual labels I'll use SMALLINT as simple integer code for each
different label (and in my program a translation table)?

If so - will I have further performance gain, if instead of
human-readable DATE for the third column I'll use Unix time put into
INTEGER type column, not DATE-type? 

I really cannot answer your real question, but say only that DATE and other 
time types are numeric, although mostly constructed.
DATE takes three bytes with range '1000-01-01' to '-12-31'.
TIMESTAMP (which has special features that one can suppress) is a four-byte 
integer that is a 31-bit Unix timestamp with range '1970-01-01 00:00:01.00' 
UTC to '2038-01-19 03:14:07.99' UTC.

Maybe TIMESTAMP, which doubtless uses the underlying integer mechanism for 
comparison, is best for you. Consider also the functions UNIX_TIMESTAMP and 
FROM_UNIXTIME.

The zone is not involved in DATE, but is involved in the rest aforesaid.


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

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Jan Steinman]

 From: Zbigniew zbigniew2...@gmail.com
 
 Now to the point: considering, that the second column shall contain
 about 100-200 different labels - so in average many of such labels
 can be repeated one million times (or even more) 

What about using ENUMs?

They have essentially the performance of INTEGERs, but you don't have to 
maintain a string mapping in your programming logic.

 Yes'm, old friends is always best, 'less you can catch a new one that's 
fit to make an old one out of. -- Sarah Jewett
 Jan Steinman, EcoReality Co-op 


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

Re: Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Jan Steinman]

 From: Zbigniew zbigniew2...@gmail.com
 
 Now to the point: considering, that the second column shall contain
 about 100-200 different labels - so in average many of such labels
 can be repeated one million times (or even more) 

What about using ENUMs? They have nearly the performance of INTEGERs, but you 
don't have to maintain a string mapping in your programming logic.

 Yes'm, old friends is always best, 'less you can catch a new one that's 
fit to make an old one out of. -- Sarah Jewett
 Jan Steinman, EcoReality Co-op 


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

Using INTEGER instead of VARCHAR/DATE - is this a way to faster access?

[Zbigniew]

I'm going to establish a large database (ISAM) of simple structure,
say the table with only 3 columns:

- VARCHAR(80)
- VARCHAR(40)
- DATE

The number of rows can be quite large, about hundred million or so.
The first column, containing actual information, will contain unique
values, unlike the two others - but the two others shall be used for
data selection (and I'll index them).

Now to the point: considering, that the second column shall contain
about 100-200 different labels - so in average many of such labels
can be repeated one million times (or even more) - will it speed-up
the selection done with something like ...WHERE label='xyz' AND
date='2013-02-25'  (and maybe the insertion as well?), if instead of
textual labels I'll use SMALLINT as simple integer code for each
different label (and in my program a translation table)?

If so - will I have further performance gain, if instead of
human-readable DATE for the third column I'll use Unix time put into
INTEGER type column, not DATE-type?

And the final question: even, if so - is it worthy? I mean: will the
supposed performance gain be significant (e.g. 2-3 times faster
selection) - and not, say, just 5% faster (only possible to detect by
using benchmarking tools)?

Thanks in advance for your opinions.
-- 
Zbig

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

how to improve mysql's query speed in table 'token' of keystone,when using in openstack with lots of VMs?

[曾国仕]

when i used mysql as the keystone's backend in openstack ,i found that the 
'token' table saved 29 millions record (using myisam as engine,the size of 
token.MYD is 100G) and have 4 new token save per second. That result to the 
slow query of a token .since of inserting new token frequently,how could i set 
the configure to speed up the query operation. 


the token's struct is id,expires,extra,valid,user_id with index {expires,valid}
and  the select sql is select id,expires,extra,valid,user_id from token where 
valid=1 and expires ='-XX-XX XX:XX:XX' and user_id 
='XXX';with often return 2 results.


Here is some db status data in a real  openstack environment with 381 active 
VMs: 
+---+-+ 
| Variable_name | Value | 
+---+-+ 
| Handler_read_first | 259573419 | 
| Handler_read_key | 1344821219 | 
| Handler_read_next | 3908969530 | 
| Handler_read_prev | 1235 | 
| Handler_read_rnd | 1951101 | 
| Handler_read_rnd_next | 48777237518 | 
+---+-+ 

and 
+-++ 
| Variable_name | Value | 
+-++ 
| Qcache_free_blocks | 498 | 
| Qcache_free_memory | 1192512 | 
| Qcache_hits | 1122242834 | 
| Qcache_inserts | 352700155 | 
| Qcache_lowmem_prunes | 34145019 | 
| Qcache_not_cached | 1529123943 | 
| Qcache_queries_in_cache | 1681 | 
| Qcache_total_blocks | 4949 | 
+-++ ‍


it seems that the 'insert' operation of saving new token affects the query 
buffer,and result of a low-level of query-hit's rate.


please give me some help,thanks.

access denied fpr user 'root'@'localhost' (using password: NO)

[Érico]

I have ran the following to test a fix for an app issue :

delete from mysql.user where user='';
2lines got effected

after this I can´t connect through command line anymore :

./mysqladmin -u root password pwd

I get access denied for user 'root'@'localhost (using password:'NO')

how can I restore the db so I can connect through command line again ?

thks

Re: access denied fpr user 'root'@'localhost' (using password: NO)

[Reindl Harald]

Am 29.05.2014 20:22, schrieb Érico:
 I have ran the following to test a fix for an app issue :
 
 delete from mysql.user where user='';
 2lines got effected
 
 after this I can´t connect through command line anymore :
 
 ./mysqladmin -u root password pwd
 
 I get access denied for user 'root'@'localhost (using password:'NO')

your command line is plain wrong
as you can see in the response you are *not* using a password

./mysqladmin -u root --password=pwd

*don't do that at all* your password ends in the history
./mysqladmin -u root -p

after that you get a pwd-prompt

 how can I restore the db so I can connect through command line again ?

if you really need to login with a destroyed userdb make sure
that nobody else can access the server and use skip grant

http://stackoverflow.com/questions/1708409/how-to-start-mysql-with-skip-grant-tables



signature.asc
Description: OpenPGP digital signature

Re: access denied fpr user 'root'@'localhost' (using password: NO)

[Kishore Vaishnav]

Did you tried this..
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html


*thanks,*
*-- *Kishore Kumar Vaishnav

On Thu, May 29, 2014 at 11:22 AM, Érico erico...@gmail.com wrote:

 I have ran the following to test a fix for an app issue :

 delete from mysql.user where user='';
 2lines got effected

 after this I can´t connect through command line anymore :

 ./mysqladmin -u root password pwd

 I get access denied for user 'root'@'localhost (using password:'NO')

 how can I restore the db so I can connect through command line again ?

 thks

Re: access denied fpr user 'root'@'localhost' (using password: NO)

[Érico]

Hi

thanks but it is not working either

I wonder if the 2 rows I removed (with empty users for localhost and my
computer names) were responsilbe for allowing the autentication ?

how this table works ?

what mysql checks on it ? the pwd column ? if it is filled ?

what if I have 2 records for root/localhost ... ?

one with pwd filled
and the second with the pwd empty

how can I insert on this table with the mandatory column ssl_cipher as blob
... ? what value must I provide in a insert like :

insert into mysql.user(host,user,ssl_cipher) values('localhost','root' ,
??? )

thks !!


2014-05-29 15:36 GMT-03:00 Kishore Vaishnav kish...@railsfactory.org:

 Did you tried this..
 http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html


 *thanks,*
 *-- *Kishore Kumar Vaishnav

 On Thu, May 29, 2014 at 11:22 AM, Érico erico...@gmail.com wrote:

 I have ran the following to test a fix for an app issue :

 delete from mysql.user where user='';
 2lines got effected

 after this I can´t connect through command line anymore :

 ./mysqladmin -u root password pwd

 I get access denied for user 'root'@'localhost (using password:'NO')

 how can I restore the db so I can connect through command line again ?

 thks

Re: access denied fpr user 'root'@'localhost' (using password: NO)

[Érico]

running this :

./mysqladmin -u root -p

at this momento it thorws me to mysqladmin  man page


2014-05-29 15:35 GMT-03:00 Reindl Harald h.rei...@thelounge.net:


 Am 29.05.2014 20:22, schrieb Érico:
  I have ran the following to test a fix for an app issue :
 
  delete from mysql.user where user='';
  2lines got effected
 
  after this I can´t connect through command line anymore :
 
  ./mysqladmin -u root password pwd
 
  I get access denied for user 'root'@'localhost (using password:'NO')

 your command line is plain wrong
 as you can see in the response you are *not* using a password

 ./mysqladmin -u root --password=pwd

 *don't do that at all* your password ends in the history
 ./mysqladmin -u root -p

 after that you get a pwd-prompt

  how can I restore the db so I can connect through command line again ?

 if you really need to login with a destroyed userdb make sure
 that nobody else can access the server and use skip grant


 http://stackoverflow.com/questions/1708409/how-to-start-mysql-with-skip-grant-tables

Re: access denied fpr user 'root'@'localhost' (using password: NO)

[Reindl Harald]

well, i am mysql administrator over 10 years now
and never needed the mysqladmin command because
the mysql command line client offers anything i
ever needed

mysql -u root -p

[harry@srv-rhsoft:~]$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16056
Server version: 5.5.37-MariaDB-log thelounge

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]

Am 29.05.2014 21:19, schrieb Érico:
 running this :
 
 ./mysqladmin -u root -p
 
 at this momento it thorws me to mysqladmin  man page
 
 
 2014-05-29 15:35 GMT-03:00 Reindl Harald h.rei...@thelounge.net:
 

 Am 29.05.2014 20:22, schrieb Érico:
 I have ran the following to test a fix for an app issue :

 delete from mysql.user where user='';
 2lines got effected

 after this I can´t connect through command line anymore :

 ./mysqladmin -u root password pwd

 I get access denied for user 'root'@'localhost (using password:'NO')

 your command line is plain wrong
 as you can see in the response you are *not* using a password

 ./mysqladmin -u root --password=pwd

 *don't do that at all* your password ends in the history
 ./mysqladmin -u root -p

 after that you get a pwd-prompt

 how can I restore the db so I can connect through command line again ?

 if you really need to login with a destroyed userdb make sure
 that nobody else can access the server and use skip grant


 http://stackoverflow.com/questions/1708409/how-to-start-mysql-with-skip-grant-tables



signature.asc
Description: OpenPGP digital signature

Re: access denied fpr user 'root'@'localhost' (using password: NO)

[shawn l.green]

Hello Érico

On 5/29/2014 2:22 PM, Érico wrote:

I have ran the following to test a fix for an app issue :

delete from mysql.user where user='';
2lines got effected

after this I can´t connect through command line anymore :

./mysqladmin -u root password pwd

I get access denied for user 'root'@'localhost (using password:'NO')

how can I restore the db so I can connect through command line again ?

thks



What that tells me is that you were never actually logging in as root 
but the system was authenticating you as the 'anonymous' user. Quoting 
from the very fine manual:


http://dev.mysql.com/doc/refman/5.6/en/account-names.html

A user name is either a nonblank value that literally matches the user 
name for incoming connection attempts, or a blank value (empty string) 
that matches any user name. An account with a blank user name is an 
anonymous user. To specify an anonymous user in SQL statements, use a 
quoted empty user name part, such as ''@'localhost'.




http://dev.mysql.com/doc/refman/5.6/en/connection-access.html

Identity checking is performed using the three user table scope columns 
(Host, User, and Password). The server accepts the connection only if 
the Host and User columns in some user table row match the client host 
name and user name and the client supplies the password specified in 
that row.

...
If the User column value is nonblank, the user name in an incoming 
connection must match exactly. If the User value is blank, it matches 
any user name. If the user table row that matches an incoming connection 
has a blank user name, the user is considered to be an anonymous user 
with no name, not a user with the name that the client actually 
specified. This means that a blank user name is used for all further 
access checking for the duration of the connection (that is, during 
Stage 2).

...
If you are able to connect to the server, but your privileges are not 
what you expect, you probably are being authenticated as some other 
account. To find out what account the server used to authenticate you, 
use the CURRENT_USER() function.



That same page in the manual (and its siblings) should also answer your 
questions as to how MySQL uses the `user` table, what the empty `user` 
and `password` column mean to login attempts, and how to configure 
SSL-based connections.


If you have forgotten your actual root@localhost password, you can reset 
it following one of the procedures provided here.

http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html

Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

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

Re: access denied fpr user 'root'@'localhost' (using password: NO)

[Érico]

I am really sorry  about this one ..
the connection is ok ...

I had not checked that I was using mysqladmin instead of mysql

now please how can I check what is wrong with my application ( My SQL Admin
)

at its login page it asks for user / pwd / server and db

using both localhost and 127.0.01 ... it gets the same error :
access denied for user  'root'@'localhost'  

the app has a php config page where it fills these info

I am able to connect to it manually too using :
./mysql -h localhost -u root -pmy_pwd mysql-admin

but the app keeps geting the access denied error

would it be sometihng related to my /et/hosts ?

its content :

127.0.0.1 localhost
255.255.255.255 broadcasthost
::1 localhost
fe80::1%lo0 localhost
127.0.0.1 mysqld
127.0.0.1 mac
localhost mac


my SO is a mac os 10.6.8

Thks Again !!



2014-05-29 16:26 GMT-03:00 Reindl Harald h.rei...@thelounge.net:

 well, i am mysql administrator over 10 years now
 and never needed the mysqladmin command because
 the mysql command line client offers anything i
 ever needed

 mysql -u root -p

 [harry@srv-rhsoft:~]$ mysql -u root -p
 Enter password:
 Welcome to the MariaDB monitor.  Commands end with ; or \g.
 Your MariaDB connection id is 16056
 Server version: 5.5.37-MariaDB-log thelounge

 Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

 Type 'help;' or '\h' for help. Type '\c' to clear the current input
 statement.

 MariaDB [(none)]

 Am 29.05.2014 21:19, schrieb Érico:
  running this :
 
  ./mysqladmin -u root -p
 
  at this momento it thorws me to mysqladmin  man page
 
 
  2014-05-29 15:35 GMT-03:00 Reindl Harald h.rei...@thelounge.net:
 
 
  Am 29.05.2014 20:22, schrieb Érico:
  I have ran the following to test a fix for an app issue :
 
  delete from mysql.user where user='';
  2lines got effected
 
  after this I can´t connect through command line anymore :
 
  ./mysqladmin -u root password pwd
 
  I get access denied for user 'root'@'localhost (using password:'NO')
 
  your command line is plain wrong
  as you can see in the response you are *not* using a password
 
  ./mysqladmin -u root --password=pwd
 
  *don't do that at all* your password ends in the history
  ./mysqladmin -u root -p
 
  after that you get a pwd-prompt
 
  how can I restore the db so I can connect through command line again ?
 
  if you really need to login with a destroyed userdb make sure
  that nobody else can access the server and use skip grant
 
 
 
 http://stackoverflow.com/questions/1708409/how-to-start-mysql-with-skip-grant-tables

Re: access denied fpr user 'root'@'localhost' (using password: NO)

[shawn l.green]

Hello Érico,

On 5/29/2014 3:51 PM, Érico wrote:

I am really sorry  about this one ..
the connection is ok ...

I had not checked that I was using mysqladmin instead of mysql

now please how can I check what is wrong with my application ( My SQL Admin
)

at its login page it asks for user / pwd / server and db

using both localhost and 127.0.01 ... it gets the same error :
access denied for user  'root'@'localhost'  

the app has a php config page where it fills these info

I am able to connect to it manually too using :
./mysql -h localhost -u root -pmy_pwd mysql-admin

but the app keeps geting the access denied error

would it be sometihng related to my /et/hosts ?

its content :

127.0.0.1 localhost
255.255.255.255 broadcasthost
::1 localhost
fe80::1%lo0 localhost
127.0.0.1 mysqld
127.0.0.1 mac
localhost mac


my SO is a mac os 10.6.8

Thks Again !!
... snip ...


What is the result of this query:

SELECT user, host, length(password) from mysql.user;

What hapens if you change your login to this? (you should not put your 
passwords on your command lines if you can avoid it

http://dev.mysql.com/doc/refman/5.6/en/password-security-user.html
http://dev.mysql.com/doc/refman/5.6/en/connecting.html
)

./mysql -h 127.0.01 --port=3306 --protocol=TCP -u root -p mysql-admin

See also:
http://dev.mysql.com/doc/refman/5.6/en/access-denied.html

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

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

[5.1 Vs 5.5 ] ERROR 1045 (28000): Access denied for user 'testuser'@'Serv1.corp.domain.in' (using password: YES)

[Vinay Gupta]

Hi,

I am trying to connect two mysql servers with different versions ( 5.1 
5.5 ) . But in Mysql 5.1 i am facing strange issues.

Below testuser exists in both Mysql Versions :


mysql select host,user,password from mysql.user where user='testuser';
+---++---+
| host  | user   |
password  |
+---++---+
| localhost | testuser   | *FJHHEU5746DDHDUDYDH66488 |
| %.corp.domain.in| testuser   | *FJHHEU5746DDHDUDYDH66488 |
+---++---+

and skip_networking is OFF


*Mysql Version : 5.1.58-log*

root@Serv1:~# mysql -utestuser -p@8AsnM0! -h $(hostname)
ERROR 1045 (28000): Access denied for user 'testuser'@'Serv1.corp.domain.in'
(using password: YES)

It connect successfully if i remove -h option because it connects by
localhost then

*Mysql version : 5.5.36-log *

root@Serv2:~# mysql -utestuser -p@8AsnM0! -h $(hostname)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Server version: 5.5.36-log MySQL Community Server (GPL)
mysql

mysql select user(),current_user();
+--+---+
| user()   |
current_user()|
+--+---+
| testu...@serv2.corp.domain.in| testuser@%.corp.domain.in
   |
+--+---+

Is dere some bug in Mysql5.1 or i need to set bind_address parameter in it.

Thanks

Install mysql server using RPM

[Asma rabe]

Hi all,

I am trying installing Mysql server using RPM bundle


rpm -i MySQL-server-5.6.16-1.el6.x86_64.rpm
rpm -i MySQL-client-5.6.16-1.el6.x86_64.rpm

next i would like to start the server, i followed the mysql docs:

The server RPM places data under the /var/lib/mysql directory. The RPM also
creates a login account for a user named mysql (if one does not exist) to
use for running the MySQL server, and creates the appropriate entries in
/etc/init.d/ to start the server automatically at boot time. (This means
that if you have performed a previous installation and have made changes to
its startup script, you may want to make a copy of the script so that you
do not lose it when you install a newer RPM.

when i checked /var/lib i found non for mysql,Any idea??

Thank you very much in advance.

Best Regards,
Rabe

Re: Install mysql server using RPM

[geetanjali mehra]

issue:
mysql
at the command prompt
and let me know.
also post the output of
ls /var/lib


On Mon, Mar 3, 2014 at 1:53 PM, Asma rabe asma.r...@gmail.com wrote:

 Hi all,

 I am trying installing Mysql server using RPM bundle


 rpm -i MySQL-server-5.6.16-1.el6.x86_64.rpm
 rpm -i MySQL-client-5.6.16-1.el6.x86_64.rpm

 next i would like to start the server, i followed the mysql docs:

 The server RPM places data under the /var/lib/mysql directory. The RPM also
 creates a login account for a user named mysql (if one does not exist) to
 use for running the MySQL server, and creates the appropriate entries in
 /etc/init.d/ to start the server automatically at boot time. (This means
 that if you have performed a previous installation and have made changes to
 its startup script, you may want to make a copy of the script so that you
 do not lose it when you install a newer RPM.

 when i checked /var/lib i found non for mysql,Any idea??

 Thank you very much in advance.

 Best Regards,
 Rabe




-- 
Geetanjali Mehra
Oracle DBA Corporate Trainer
Koenig-solutions
Moti Nagar,New Delhi

Re: Install mysql server using RPM

[Johan De Meersman]

- Original Message -
 From: Asma rabe asma.r...@gmail.com
 Subject: Install mysql server using RPM
 
 rpm -i MySQL-server-5.6.16-1.el6.x86_64.rpm
 rpm -i MySQL-client-5.6.16-1.el6.x86_64.rpm


I seem to recall that Oracle's Debian -server package included the clients 
already. You may not need to install the -client package if the same goes for 
the RPMs.

-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

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

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Érico]

ok

I have tried these :

ifconfig -a
lo0: flags=8049UP,LOOPBACK,RUNNING,MULTICAST mtu 16384
inet6 ::1 prefixlen 128
inet6 fe80::1%lo0 prefixlen 64 scopeid 0x1
inet 127.0.0.1 netmask 0xff00
gif0: flags=8010POINTOPOINT,MULTICAST mtu 1280
stf0: flags=0 mtu 1280
fw0: flags=8863UP,BROADCAST,SMART,RUNNING,SIMPLEX,MULTICAST mtu 4078
lladdr 70:cd:60:ff:fe:eb:72:ea
media: autoselect full-duplex
status: inactive
en1: flags=8863UP,BROADCAST,SMART,RUNNING,SIMPLEX,MULTICAST mtu 1500
ether e4:ce:8f:04:7c:f8
inet6 fe80::e6ce:8fff:fe04:7cf8%en1 prefixlen 64 scopeid 0x5
inet 169.254.99.150 netmask 0x broadcast 169.254.255.255
media: autoselect
status: active
en0: flags=8863UP,BROADCAST,SMART,RUNNING,SIMPLEX,MULTICAST mtu 1500
ether c8:2a:14:1a:47:ea
media: autoselect (none)
status: inactive
wc2: flags=822BROADCAST,SMART,SIMPLEX mtu 1500
ether 00:02:55:11:19:76
media: 1000baseT (unknown type)
=


mac:bin ericomtx$ nslookup localhost
;; connection timed out; no servers could be reached

=


mac:bin ericomtx$ netstat -an | grep 3306
tcp46  0  0  *.3306 *.*LISTEN
tcp4   0  0  *.3306 *.*LISTEN

=

mac:bin ericomtx$ netstat -ln | grep mysql
ff801403c280 stream  0  00
ff801403b20000 /tmp/mysql.sock
ff801403a9c0 stream  0  0 ff801bbb78b8
000 /tmp/mysql.sock
ff8013fb0bc0 stream  0  0 ff8014aa8078
000 /opt/local/var/run/mysql5/mysqld.sock
*** here ... this last one is related to a previous mysql macports version
***

=

mac:lib ericomtx$ ps xa | grep mysqld
  231   ??  S  0:00.01 /bin/sh /opt/local/lib/mysql5/bin/mysqld_safe
--datadir=/opt/local/var/db/mysql5
--pid-file=/opt/local/var/db/mysql5/mac.local.pid
  295   ??  S  0:00.74 /opt/local/libexec/mysqld --basedir=/opt/local
--datadir=/opt/local/var/db/mysql5 --user=_mysql
--log-error=/opt/local/var/db/mysql5/mac.local.err
--pid-file=/opt/local/var/db/mysql5/mac.local.pid
--socket=/opt/local/var/run/mysql5/mysqld.sock
 2175 s000  S  0:00.02 /bin/sh
/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/bin/mysqld_safe
--datadir=/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/data
--pid-file=/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/data/mac.local.pid
 2273 s000  S  0:00.35
/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/bin/mysqld
--basedir=/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86
--datadir=/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/data
--plugin-dir=/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/lib/plugin
--user=mysql
--log-error=/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/data/mac.local.err
--pid-file=/Users/ericomtx/development/mysql/mysql-5.6.15-osx10.6-x86/data/mac.local.pid


=

I have uninstalled everything related to mysql ports

these dirs no longer exist :
/opt/local/lib/mysql5


/opt/local/libexec

I don't know from where they are getting called



2014/1/15 Claudio Nanni claudio.na...@gmail.com

 Hi


   |  | ericomtxmacbookpro.local |
  *E85DC00A0137C6171923BE35EDD809573FB3AB4F |
  


 mysql DELETE FROM mysql.user WHERE user='';
 mysql FLUSH PRIVILEGES;

 maybe helps?

 Cheers

 --
 Claudio

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Claudio Nanni]

Hi


   |  | ericomtxmacbookpro.local |
  *E85DC00A0137C6171923BE35EDD809573FB3AB4F |
  


mysql DELETE FROM mysql.user WHERE user='';
mysql FLUSH PRIVILEGES;

maybe helps?

Cheers

-- 
Claudio

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Érico]

 yes it is ...

  also , the eclipse is also local ...

  in both ... mysql-admin php application , plus inside eclispe plugin ...

  same behavior in both


2014/1/13 Reindl Harald h.rei...@thelounge.net

 i doubt that the webserver is running on the same
 machine as your mysql command shell

 Am 13.01.2014 23:59, schrieb Érico:
  true
 
  but please check this out :
 
  mysql Select user, host, password from mysql.user;
 
 +--+--+---+
  | user | host | password
  |
 
 +--+--+---+
  | root | localhost|
 *E85DC00A0137C6171923BE35EDD809573FB3AB4F |
  | root | ericomtxmacbookpro.local |
 *E85DC00A0137C6171923BE35EDD809573FB3AB4F |
  | root | 127.0.0.1|
 *E85DC00A0137C6171923BE35EDD809573FB3AB4F |
  | root | ::1  |
   |
  |  | ericomtxmacbookpro.local |
 *E85DC00A0137C6171923BE35EDD809573FB3AB4F |
 
 +--+--+---+
  5 rows in set (0.00 sec)
 
  all passwords are filled in the db ...
 
  also ... I can connect through command line
 
  what I can't do is :
  1. connect or even ping inside eclipse using jconnector
 
  2. connect from a php app (mysql adim) with or with out pwd ...
  providing the error
 
 
Access denied for user 'root'@'localhost' (using password: YES)
 
 
Access denied for user 'root'@'localhost' (using password: NO)
 
 
  2.1 when I use 127.0.0.1 on mysql admin url ... I get a connection closed
 
  so ... my point is :
 
  in both cases  when using 127.0.0.1 or localhost ...
 
  can the OS be blocking the connection ?
 
  if so , how could I check this ?
 
  Regards
  Érico
 
 
  2014/1/13 Reindl Harald h.rei...@thelounge.net mailto:
 h.rei...@thelounge.net
 
  WTF - we are talking about *database connections* and *not*
 http-URL's
  the webserver is only the *messenger*
 
  Am 13.01.2014 18:54, schrieb Érico:
   using both urls I get the same error :
  
   http://localhost/mysql/index.php
   http://127.0.0.1/mysql/index.php
  
   in 127.0.0.1... after I submit the index.php ... it redirects to
 localhost
   too ..
  
   2014/1/13 Reindl Harald h.rei...@thelounge.net mailto:
 h.rei...@thelounge.net
  
  
  
   Am 13.01.2014 18:28, schrieb Érico:
   ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 31
   Server version: 5.6.15 MySQL Community Server (GPL)
  
   but in the browser I get the error :
  
   Access denied for user 'root'@'localhost' (using password: YES)
  
   I can't find any information in error log and access log
  
   is there any command parameter that I should use when starting
 mysql so
   this doesn't happen?
  
   are you using localhost or 127.0.0.1 in the web-application
   root@localhost != root@127.0.0.1 mailto:root@127.0.0.1 =
 different users
  
   localhost: Unix-Socket
   127.0.0.1 http://127.0.0.1: TCP
 
 

 --

 Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, Hofmühlgasse 17
 CTO / CISO / Software-Development
 m: +43 (676) 40 221 40, p: +43 (1) 595 3999 33
 icq: 154546673, http://www.thelounge.net/

 http://www.thelounge.net/signature.asc.what.htm

Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Érico]

Hi

I have installed mysql admin on my local environment

I am able to connect to mysql through command line :

ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.

mysql

...

but in the browser I get the error :

Access denied for user 'root'@'localhost' (using password: YES)

I can't find any information in error log and access log

is there any command parameter that I should use when starting mysql so
this doesn't happen ?

I am starting it this way :

sudo ./mysql.server start

my /etc/hosts file :

##
# Host Database
#
# localhost is used to configure the loopback interface
# when the system is booting.  Do not change this entry.
##
127.0.0.1   localhost
255.255.255.255 broadcasthost
::1 localhost
#fe80::1%lo0localhost
127.0.0.1   ericomtxmacbookpro.local
127.0.0.1   mysqld


Thks
Érico

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Reindl Harald]

Am 13.01.2014 18:28, schrieb Érico:
 ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin
 Enter password:
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 31
 Server version: 5.6.15 MySQL Community Server (GPL)

 but in the browser I get the error :
 
 Access denied for user 'root'@'localhost' (using password: YES)
 
 I can't find any information in error log and access log
 
 is there any command parameter that I should use when starting mysql so
 this doesn't happen?

are you using localhost or 127.0.0.1 in the web-application
root@localhost != root@127.0.0.1 = different users

localhost: Unix-Socket
127.0.0.1: TCP





signature.asc
Description: OpenPGP digital signature

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Erick Ocrospoma]

From that error I would suspect you are trying to access to a database
where has not external access. And yeah, try by doing

$ mysql -u root -p database -h localhost -P 3306

change localhost by 127.0.0.1 in order to test if both cases work, and
see which of them (localhost/127.0.0.1) is defined in your web app as
you've been told in the mail before.

On 13 January 2014 12:36, Reindl Harald h.rei...@thelounge.net wrote:


 Am 13.01.2014 18:28, schrieb Érico:
 ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin
 Enter password:
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 31
 Server version: 5.6.15 MySQL Community Server (GPL)

 but in the browser I get the error :

 Access denied for user 'root'@'localhost' (using password: YES)

 I can't find any information in error log and access log

 is there any command parameter that I should use when starting mysql so
 this doesn't happen?

 are you using localhost or 127.0.0.1 in the web-application
 root@localhost != root@127.0.0.1 = different users

 localhost: Unix-Socket
 127.0.0.1: TCP






-- 





~ Happy install !



Erick.


---

Cellphone   :  +51 950307809
Blog:  http://zerick.me/
LUG:  http://www.utpinux.org
IRC :   zerick
About :  http://about.me/zerick
Linux User ID :  549567

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

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Érico]

using both urls I get the same error :

http://localhost/mysql/index.php
http://127.0.0.1/mysql/index.php

in 127.0.0.1... after I submit the index.php ... it redirects to localhost
too ..


2014/1/13 Reindl Harald h.rei...@thelounge.net



 Am 13.01.2014 18:28, schrieb Érico:
  ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin
  Enter password:
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 31
  Server version: 5.6.15 MySQL Community Server (GPL)
 
  but in the browser I get the error :
 
  Access denied for user 'root'@'localhost' (using password: YES)
 
  I can't find any information in error log and access log
 
  is there any command parameter that I should use when starting mysql so
  this doesn't happen?

 are you using localhost or 127.0.0.1 in the web-application
 root@localhost != root@127.0.0.1 = different users

 localhost: Unix-Socket
 127.0.0.1: TCP

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Érico]

using localhost the coonection works ...
ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h
localhost -P 3306
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 97
Server version: 5.6.15 MySQL Community Server (GPL)




but using 127.0.0.1 no :

ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h
127.0.0.1 -P 3306
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: YES)
ericomtxmacbookpro:bin ericomtx$



2014/1/13 Erick Ocrospoma zipper1...@gmail.com

 From that error I would suspect you are trying to access to a database
 where has not external access. And yeah, try by doing

 $ mysql -u root -p database -h localhost -P 3306

 change localhost by 127.0.0.1 in order to test if both cases work, and
 see which of them (localhost/127.0.0.1) is defined in your web app as
 you've been told in the mail before.

 On 13 January 2014 12:36, Reindl Harald h.rei...@thelounge.net wrote:
 
 
  Am 13.01.2014 18:28, schrieb Érico:
  ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin
  Enter password:
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 31
  Server version: 5.6.15 MySQL Community Server (GPL)
 
  but in the browser I get the error :
 
  Access denied for user 'root'@'localhost' (using password: YES)
 
  I can't find any information in error log and access log
 
  is there any command parameter that I should use when starting mysql so
  this doesn't happen?
 
  are you using localhost or 127.0.0.1 in the web-application
  root@localhost != root@127.0.0.1 = different users
 
  localhost: Unix-Socket
  127.0.0.1: TCP
 
 
 



 --





 ~ Happy install !



 Erick.


 ---

 Cellphone   :  +51 950307809
 Blog:  http://zerick.me/
 LUG:  http://www.utpinux.org
 IRC :   zerick
 About :  http://about.me/zerick
 Linux User ID :  549567

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

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Erick Ocrospoma]

I presume your index.php file uses 127.0.0.1. After login to MySQL try this:

$ grant all privileges to *.* 'root'@'127.0.0.1' identified by yourpassword;

Of course this could not be the best solution, it's just to skip it,
you must look at the query/connection on your php file.

On 13 January 2014 12:57, Érico erico...@gmail.com wrote:
 using localhost the coonection works ...
 ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h localhost
 -P 3306
 Enter password:
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A


 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 97

 Server version: 5.6.15 MySQL Community Server (GPL)


 

 but using 127.0.0.1 no :

 ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h 127.0.0.1
 -P 3306
 Enter password:
 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
 password: YES)
 ericomtxmacbookpro:bin ericomtx$



 2014/1/13 Erick Ocrospoma zipper1...@gmail.com

 From that error I would suspect you are trying to access to a database
 where has not external access. And yeah, try by doing

 $ mysql -u root -p database -h localhost -P 3306

 change localhost by 127.0.0.1 in order to test if both cases work, and
 see which of them (localhost/127.0.0.1) is defined in your web app as
 you've been told in the mail before.

 On 13 January 2014 12:36, Reindl Harald h.rei...@thelounge.net wrote:
 
 
  Am 13.01.2014 18:28, schrieb Érico:
  ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin
  Enter password:
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 31
  Server version: 5.6.15 MySQL Community Server (GPL)
 
  but in the browser I get the error :
 
  Access denied for user 'root'@'localhost' (using password: YES)
 
  I can't find any information in error log and access log
 
  is there any command parameter that I should use when starting mysql so
  this doesn't happen?
 
  are you using localhost or 127.0.0.1 in the web-application
  root@localhost != root@127.0.0.1 = different users
 
  localhost: Unix-Socket
  127.0.0.1: TCP
 
 
 



 --





 ~ Happy install !



 Erick.


 ---

 Cellphone   :  +51 950307809
 Blog:  http://zerick.me/
 LUG:  http://www.utpinux.org
 IRC :   zerick
 About :  http://about.me/zerick
 Linux User ID :  549567

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





-- 





~ Happy install !



Erick.


---

Cellphone   :  +51 950307809
Blog:  http://zerick.me/
LUG:  http://www.utpinux.org
IRC :   zerick
About :  http://about.me/zerick
Linux User ID :  549567

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

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Erick Ocrospoma]

I forgot this. Do it too.

On 13 January 2014 13:01, Erick Ocrospoma zipper1...@gmail.com wrote:
 I presume your index.php file uses 127.0.0.1. After login to MySQL try this:

 $ grant all privileges to *.* 'root'@'127.0.0.1' identified by 
 yourpassword;
$ flush privileges;

 Of course this could not be the best solution, it's just to skip it,
 you must look at the query/connection on your php file.

 On 13 January 2014 12:57, Érico erico...@gmail.com wrote:
 using localhost the coonection works ...
 ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h localhost
 -P 3306
 Enter password:
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A


 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 97

 Server version: 5.6.15 MySQL Community Server (GPL)


 

 but using 127.0.0.1 no :

 ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h 127.0.0.1
 -P 3306
 Enter password:
 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
 password: YES)
 ericomtxmacbookpro:bin ericomtx$



 2014/1/13 Erick Ocrospoma zipper1...@gmail.com

 From that error I would suspect you are trying to access to a database
 where has not external access. And yeah, try by doing

 $ mysql -u root -p database -h localhost -P 3306

 change localhost by 127.0.0.1 in order to test if both cases work, and
 see which of them (localhost/127.0.0.1) is defined in your web app as
 you've been told in the mail before.

 On 13 January 2014 12:36, Reindl Harald h.rei...@thelounge.net wrote:
 
 
  Am 13.01.2014 18:28, schrieb Érico:
  ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin
  Enter password:
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 31
  Server version: 5.6.15 MySQL Community Server (GPL)
 
  but in the browser I get the error :
 
  Access denied for user 'root'@'localhost' (using password: YES)
 
  I can't find any information in error log and access log
 
  is there any command parameter that I should use when starting mysql so
  this doesn't happen?
 
  are you using localhost or 127.0.0.1 in the web-application
  root@localhost != root@127.0.0.1 = different users
 
  localhost: Unix-Socket
  127.0.0.1: TCP
 
 
 



 --





 ~ Happy install !



 Erick.


 ---

 Cellphone   :  +51 950307809
 Blog:  http://zerick.me/
 LUG:  http://www.utpinux.org
 IRC :   zerick
 About :  http://about.me/zerick
 Linux User ID :  549567

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





 --





 ~ Happy install !



 Erick.


 ---

 Cellphone   :  +51 950307809
 Blog:  http://zerick.me/
 LUG:  http://www.utpinux.org
 IRC :   zerick
 About :  http://about.me/zerick
 Linux User ID :  549567



-- 





~ Happy install !



Erick.


---

Cellphone   :  +51 950307809
Blog:  http://zerick.me/
LUG:  http://www.utpinux.org
IRC :   zerick
About :  http://about.me/zerick
Linux User ID :  549567

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

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Érico]

the granting is not affecting the tables:

mysql grant all privileges on *.* to root@localhost identified by 'pwd';
Query OK, 0 rows affected (0.00 sec)

mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

...

and if I try the granting in 127.0.0.1 :
mysql grant all privileges to *.* 'root'@'127.0.0.1' identified by
kernel26;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'to *.* 'root'@'127.0.0.1' identified by kernel26' at line 1

...


in eclipse using jconnector ... I get the same error :
when pinging :

java.sql.SQLException: Access denied for user 'root'@'localhost' (using
password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)



Could not connect to New MySQL.
Error creating SQL Model Connection connection to New MySQL. (Error: Access
denied for user 'root'@'localhost' (using password: YES))
Access denied for user 'root'@'localhost' (using password: YES)




2014/1/13 Erick Ocrospoma zipper1...@gmail.com

 I forgot this. Do it too.

 On 13 January 2014 13:01, Erick Ocrospoma zipper1...@gmail.com wrote:
  I presume your index.php file uses 127.0.0.1. After login to MySQL try
 this:
 
  $ grant all privileges to *.* 'root'@'127.0.0.1' identified by
 yourpassword;
 $ flush privileges;
 
  Of course this could not be the best solution, it's just to skip it,
  you must look at the query/connection on your php file.
 
  On 13 January 2014 12:57, Érico erico...@gmail.com wrote:
  using localhost the coonection works ...
  ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h
 localhost
  -P 3306
  Enter password:
  Reading table information for completion of table and column names
  You can turn off this feature to get a quicker startup with -A
 
 
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 97
 
  Server version: 5.6.15 MySQL Community Server (GPL)
 
 
  
 
  but using 127.0.0.1 no :
 
  ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h
 127.0.0.1
  -P 3306
  Enter password:
  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
  password: YES)
  ericomtxmacbookpro:bin ericomtx$
 
 
 
  2014/1/13 Erick Ocrospoma zipper1...@gmail.com
 
  From that error I would suspect you are trying to access to a database
  where has not external access. And yeah, try by doing
 
  $ mysql -u root -p database -h localhost -P 3306
 
  change localhost by 127.0.0.1 in order to test if both cases work, and
  see which of them (localhost/127.0.0.1) is defined in your web app as
  you've been told in the mail before.
 
  On 13 January 2014 12:36, Reindl Harald h.rei...@thelounge.net
 wrote:
  
  
   Am 13.01.2014 18:28, schrieb Érico:
   ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 31
   Server version: 5.6.15 MySQL Community Server (GPL)
  
   but in the browser I get the error :
  
   Access denied for user 'root'@'localhost' (using password: YES)
  
   I can't find any information in error log and access log
  
   is there any command parameter that I should use when starting
 mysql so
   this doesn't happen?
  
   are you using localhost or 127.0.0.1 in the web-application
   root@localhost != root@127.0.0.1 = different users
  
   localhost: Unix-Socket
   127.0.0.1: TCP
  
  
  
 
 
 
  --
 
 
 
 
 
  ~ Happy install !
 
 
 
  Erick.
 
 
  ---
 
  Cellphone   :  +51 950307809
  Blog:  http://zerick.me/
  LUG:  http://www.utpinux.org
  IRC :   zerick
  About :  http://about.me/zerick
  Linux User ID :  549567
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 
 
 
 
  --
 
 
 
 
 
  ~ Happy install !
 
 
 
  Erick.
 
 
  ---
 
  Cellphone   :  +51 950307809
  Blog:  http://zerick.me/
  LUG:  http://www.utpinux.org
  IRC :   zerick
  About :  http://about.me/zerick
  Linux User ID :  549567



 --





 ~ Happy install !



 Erick.


 ---

 Cellphone   :  +51 950307809
 Blog:  http://zerick.me/
 LUG:  http://www.utpinux.org
 IRC :   zerick
 About :  http://about.me/zerick
 Linux User ID :  549567

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Erick Ocrospoma]

On 13 January 2014 13:25, Érico erico...@gmail.com wrote:
 the granting is not affecting the tables:

 mysql grant all privileges on *.* to root@localhost identified by 'pwd';
 Query OK, 0 rows affected (0.00 sec)

 mysql FLUSH PRIVILEGES;
 Query OK, 0 rows affected (0.00 sec)

 ...

 and if I try the granting in 127.0.0.1 :
 mysql grant all privileges to *.* 'root'@'127.0.0.1' identified by
 kernel26;
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
 that corresponds to your MySQL server version for the right syntax to use
 near 'to *.* 'root'@'127.0.0.1' identified by kernel26' at line 1
It's my error :)  It should be:

mysql grant all privileges on *.* to 'root'@'127.0.0.1' identified by
'kernel26';

 ...


 in eclipse using jconnector ... I get the same error :
 when pinging :

 java.sql.SQLException: Access denied for user 'root'@'localhost' (using
 password: YES)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)

 

 Could not connect to New MySQL.
 Error creating SQL Model Connection connection to New MySQL. (Error: Access
 denied for user 'root'@'localhost' (using password: YES))
 Access denied for user 'root'@'localhost' (using password: YES)




 2014/1/13 Erick Ocrospoma zipper1...@gmail.com

 I forgot this. Do it too.

 On 13 January 2014 13:01, Erick Ocrospoma zipper1...@gmail.com wrote:
  I presume your index.php file uses 127.0.0.1. After login to MySQL try
 this:
 
  $ grant all privileges to *.* 'root'@'127.0.0.1' identified by
 yourpassword;
 $ flush privileges;
 
  Of course this could not be the best solution, it's just to skip it,
  you must look at the query/connection on your php file.
 
  On 13 January 2014 12:57, Érico erico...@gmail.com wrote:
  using localhost the coonection works ...
  ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h
 localhost
  -P 3306
  Enter password:
  Reading table information for completion of table and column names
  You can turn off this feature to get a quicker startup with -A
 
 
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 97
 
  Server version: 5.6.15 MySQL Community Server (GPL)
 
 
  
 
  but using 127.0.0.1 no :
 
  ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h
 127.0.0.1
  -P 3306
  Enter password:
  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
  password: YES)
  ericomtxmacbookpro:bin ericomtx$
 
 
 
  2014/1/13 Erick Ocrospoma zipper1...@gmail.com
 
  From that error I would suspect you are trying to access to a database
  where has not external access. And yeah, try by doing
 
  $ mysql -u root -p database -h localhost -P 3306
 
  change localhost by 127.0.0.1 in order to test if both cases work, and
  see which of them (localhost/127.0.0.1) is defined in your web app as
  you've been told in the mail before.
 
  On 13 January 2014 12:36, Reindl Harald h.rei...@thelounge.net
 wrote:
  
  
   Am 13.01.2014 18:28, schrieb Érico:
   ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 31
   Server version: 5.6.15 MySQL Community Server (GPL)
  
   but in the browser I get the error :
  
   Access denied for user 'root'@'localhost' (using password: YES)
  
   I can't find any information in error log and access log
  
   is there any command parameter that I should use when starting
 mysql so
   this doesn't happen?
  
   are you using localhost or 127.0.0.1 in the web-application
   root@localhost != root@127.0.0.1 = different users
  
   localhost: Unix-Socket
   127.0.0.1: TCP
  
  
  
 
 
 
  --
 
 
 
 
 
  ~ Happy install !
 
 
 
  Erick.
 
 
  ---
 
  Cellphone   :  +51 950307809
  Blog:  http://zerick.me/
  LUG:  http://www.utpinux.org
  IRC :   zerick
  About :  http://about.me/zerick
  Linux User ID :  549567
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 
 
 
 
  --
 
 
 
 
 
  ~ Happy install !
 
 
 
  Erick.
 
 
  ---
 
  Cellphone   :  +51 950307809
  Blog:  http://zerick.me/
  LUG:  http://www.utpinux.org
  IRC :   zerick
  About :  http://about.me/zerick
  Linux User ID :  549567



 --





 ~ Happy install !



 Erick.


 ---

 Cellphone   :  +51 950307809
 Blog:  http://zerick.me/
 LUG:  http://www.utpinux.org
 IRC :   zerick
 About :  http://about.me/zerick
 Linux User ID :  549567




-- 





~ Happy install !



Erick.


---

Cellphone   :  +51 950307809
Blog:  http://zerick.me/
LUG:  http://www.utpinux.org
IRC :   zerick
About :  http://about.me/zerick
Linux User ID :  549567

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

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Érico]

Hi

didn't work

look ... isn't this something related to this :

are you using localhost or 127.0.0.1 in the web-application
root@localhost != root@127.0.0.1 = different users

localhost: Unix-Socket
127.0.0.1: TCP

...

since I am not able to stabilsh a connection even with using eclipse ...

my /etc/hosts file :

##
127.0.0.1   localhost
255.255.255.255 broadcasthost
::1 localhost
#fe80::1%lo0localhost
127.0.0.1   ericomtxmacbookpro.local
#127.0.0.1   mysqld





2014/1/13 Erick Ocrospoma zipper1...@gmail.com

 On 13 January 2014 13:25, Érico erico...@gmail.com wrote:
  the granting is not affecting the tables:
 
  mysql grant all privileges on *.* to root@localhost identified by
 'pwd';
  Query OK, 0 rows affected (0.00 sec)
 
  mysql FLUSH PRIVILEGES;
  Query OK, 0 rows affected (0.00 sec)
 
  ...
 
  and if I try the granting in 127.0.0.1 :
  mysql grant all privileges to *.* 'root'@'127.0.0.1' identified by
  kernel26;
  ERROR 1064 (42000): You have an error in your SQL syntax; check the
 manual
  that corresponds to your MySQL server version for the right syntax to use
  near 'to *.* 'root'@'127.0.0.1' identified by kernel26' at line 1
 It's my error :)  It should be:

 mysql grant all privileges on *.* to 'root'@'127.0.0.1' identified by
 'kernel26';
 
  ...
 
 
  in eclipse using jconnector ... I get the same error :
  when pinging :
 
  java.sql.SQLException: Access denied for user 'root'@'localhost' (using
  password: YES)
  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
 
  
 
  Could not connect to New MySQL.
  Error creating SQL Model Connection connection to New MySQL. (Error:
 Access
  denied for user 'root'@'localhost' (using password: YES))
  Access denied for user 'root'@'localhost' (using password: YES)
 
 
 
 
  2014/1/13 Erick Ocrospoma zipper1...@gmail.com
 
  I forgot this. Do it too.
 
  On 13 January 2014 13:01, Erick Ocrospoma zipper1...@gmail.com wrote:
   I presume your index.php file uses 127.0.0.1. After login to MySQL
 try
  this:
  
   $ grant all privileges to *.* 'root'@'127.0.0.1' identified by
  yourpassword;
  $ flush privileges;
  
   Of course this could not be the best solution, it's just to skip it,
   you must look at the query/connection on your php file.
  
   On 13 January 2014 12:57, Érico erico...@gmail.com wrote:
   using localhost the coonection works ...
   ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h
  localhost
   -P 3306
   Enter password:
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
  
  
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 97
  
   Server version: 5.6.15 MySQL Community Server (GPL)
  
  
   
  
   but using 127.0.0.1 no :
  
   ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin -h
  127.0.0.1
   -P 3306
   Enter password:
   ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
   password: YES)
   ericomtxmacbookpro:bin ericomtx$
  
  
  
   2014/1/13 Erick Ocrospoma zipper1...@gmail.com
  
   From that error I would suspect you are trying to access to a
 database
   where has not external access. And yeah, try by doing
  
   $ mysql -u root -p database -h localhost -P 3306
  
   change localhost by 127.0.0.1 in order to test if both cases work,
 and
   see which of them (localhost/127.0.0.1) is defined in your web app
 as
   you've been told in the mail before.
  
   On 13 January 2014 12:36, Reindl Harald h.rei...@thelounge.net
  wrote:
   
   
Am 13.01.2014 18:28, schrieb Érico:
ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.6.15 MySQL Community Server (GPL)
   
but in the browser I get the error :
   
Access denied for user 'root'@'localhost' (using password: YES)
   
I can't find any information in error log and access log
   
is there any command parameter that I should use when starting
  mysql so
this doesn't happen?
   
are you using localhost or 127.0.0.1 in the web-application
root@localhost != root@127.0.0.1 = different users
   
localhost: Unix-Socket
127.0.0.1: TCP
   
   
   
  
  
  
   --
  
  
  
  
  
   ~ Happy install !
  
  
  
   Erick.
  
  
   ---
  
   Cellphone   :  +51 950307809
   Blog:  http://zerick.me/
   LUG:  http://www.utpinux.org
   IRC :   zerick
   About :  http://about.me/zerick
   Linux User ID :  549567
  
   --
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:http://lists.mysql.com/mysql
  
  
  
  
  
   --
  
  
  
  
  
   ~ Happy install

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[shawn l.green]

Hello Érico,

On 1/13/2014 1:49 PM, Érico wrote:

Hi

didn't work

look ... isn't this something related to this :

are you using localhost or 127.0.0.1 in the web-application
root@localhost != root@127.0.0.1 = different users

localhost: Unix-Socket
127.0.0.1: TCP

...

since I am not able to stabilsh a connection even with using eclipse ...

my /etc/hosts file :

##
127.0.0.1   localhost
255.255.255.255 broadcasthost
::1 localhost
#fe80::1%lo0localhost
127.0.0.1   ericomtxmacbookpro.local
#127.0.0.1   mysqld



The thing to remember is that 'localhost' or '127.0.0.1' is where the 
server believes the connection is coming from. The password you must be 
using in your client connection attempt must match the one used on the 
server for the mysql user 'root'@'localhost' or you will not authenticate.


You also need to remember that if your .php page is on a different 
machine, it is not connecting to the same mysqld that you have running 
in your development machine (your personal environment). Your .PHP page 
is trying to connect to the one running on its host machine. That user 
'root' may have an entirely different password.


Your programs are connecting fine. If they didn't you would get a 
different message. They are failing to authenticate which means that the 
mysqld they are connecting to does not recognize the password you are 
using for the account you are trying to authenticate as.


Regards,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

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

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Érico]

Hi

no connections outside command line are being accepted . I have connected
through command line , but not using eclipse for example ... it gets the
same error from the web app

my apache and pages are in the same computer that mysql

I am not getting password issues.. otherwise I would not connect through
command line

Thks


2014/1/13 shawn l.green shawn.l.gr...@oracle.com

 Hello Érico,


 On 1/13/2014 1:49 PM, Érico wrote:

 Hi

 didn't work

 look ... isn't this something related to this :

 are you using localhost or 127.0.0.1 in the web-application
 root@localhost != root@127.0.0.1 = different users

 localhost: Unix-Socket
 127.0.0.1: TCP

 ...

 since I am not able to stabilsh a connection even with using eclipse ...

 my /etc/hosts file :

 ##
 127.0.0.1   localhost
 255.255.255.255 broadcasthost
 ::1 localhost
 #fe80::1%lo0localhost
 127.0.0.1   ericomtxmacbookpro.local
 #127.0.0.1   mysqld


 The thing to remember is that 'localhost' or '127.0.0.1' is where the
 server believes the connection is coming from. The password you must be
 using in your client connection attempt must match the one used on the
 server for the mysql user 'root'@'localhost' or you will not authenticate.

 You also need to remember that if your .php page is on a different
 machine, it is not connecting to the same mysqld that you have running in
 your development machine (your personal environment). Your .PHP page is
 trying to connect to the one running on its host machine. That user 'root'
 may have an entirely different password.

 Your programs are connecting fine. If they didn't you would get a
 different message. They are failing to authenticate which means that the
 mysqld they are connecting to does not recognize the password you are using
 for the account you are trying to authenticate as.

 Regards,
 --
 Shawn Green
 MySQL Senior Principal Technical Support Engineer
 Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
 Office: Blountville, TN


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

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Reindl Harald]

WTF - we are talking about *database connections* and *not* http-URL's
the webserver is only the *messenger*

Am 13.01.2014 18:54, schrieb Érico:
 using both urls I get the same error :
 
 http://localhost/mysql/index.php
 http://127.0.0.1/mysql/index.php
 
 in 127.0.0.1... after I submit the index.php ... it redirects to localhost
 too ..
 
 2014/1/13 Reindl Harald h.rei...@thelounge.net
 


 Am 13.01.2014 18:28, schrieb Érico:
 ericomtxmacbookpro:bin ericomtx$ ./mysql -u root -p mysql-admin
 Enter password:
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 31
 Server version: 5.6.15 MySQL Community Server (GPL)

 but in the browser I get the error :

 Access denied for user 'root'@'localhost' (using password: YES)

 I can't find any information in error log and access log

 is there any command parameter that I should use when starting mysql so
 this doesn't happen?

 are you using localhost or 127.0.0.1 in the web-application
 root@localhost != root@127.0.0.1 = different users

 localhost: Unix-Socket
 127.0.0.1: TCP



signature.asc
Description: OpenPGP digital signature

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Reindl Harald]

*your application* is connecting to mysql
*your application* is using a hostname
*your application* *may* use 127.0.0.1
*your application* should use localhost to *connect to the databse*
*your application* can only use TCP *if there is* a *mysql user* with *that 
host*

http://dev.mysql.com/doc/refman/5.5/en/can-not-connect-to-server.html

Am 13.01.2014 20:38, schrieb Érico:
 no connections outside command line are being accepted . I have connected
 through command line , but not using eclipse for example ... it gets the
 same error from the web app
 
 my apache and pages are in the same computer that mysql
 
 I am not getting password issues.. otherwise I would not connect through
 command line
 
 2014/1/13 shawn l.green shawn.l.gr...@oracle.com
 
 On 1/13/2014 1:49 PM, Érico wrote:

 Hi

 didn't work

 look ... isn't this something related to this :

 are you using localhost or 127.0.0.1 in the web-application
 root@localhost != root@127.0.0.1 = different users

 localhost: Unix-Socket
 127.0.0.1: TCP

 ...

 since I am not able to stabilsh a connection even with using eclipse ...

 my /etc/hosts file :

 ##
 127.0.0.1   localhost
 255.255.255.255 broadcasthost
 ::1 localhost
 #fe80::1%lo0localhost
 127.0.0.1   ericomtxmacbookpro.local
 #127.0.0.1   mysqld


 The thing to remember is that 'localhost' or '127.0.0.1' is where the
 server believes the connection is coming from. The password you must be
 using in your client connection attempt must match the one used on the
 server for the mysql user 'root'@'localhost' or you will not authenticate.

 You also need to remember that if your .php page is on a different
 machine, it is not connecting to the same mysqld that you have running in
 your development machine (your personal environment). Your .PHP page is
 trying to connect to the one running on its host machine. That user 'root'
 may have an entirely different password.

 Your programs are connecting fine. If they didn't you would get a
 different message. They are failing to authenticate which means that the
 mysqld they are connecting to does not recognize the password you are using
 for the account you are trying to authenticate as.



signature.asc
Description: OpenPGP digital signature

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[shawn l.green]

Hello Reindl,

On 1/13/2014 3:01 PM, Reindl Harald wrote:

*your application* is connecting to mysql
*your application* is using a hostname
*your application* *may* use 127.0.0.1
*your application* should use localhost to *connect to the databse*
*your application* can only use TCP *if there is* a *mysql user* with *that 
host*

http://dev.mysql.com/doc/refman/5.5/en/can-not-connect-to-server.html




... snip ...

The problem is, his other clients (his php page,his eclipse environment) 
is where he has problems making the connection from. His direct 
connection (using the mysql command line client) is having no problems.


So, the issues are:
* Why is a client connection, from the same host as the mysqld server, 
initiated from his PHP code failing to connect?


* Why is a client connection, from the same host machine as his mysqld 
server, initiated from his Eclipse programming tool failing to connect?



We are not saying he is using HTTP commands to log into his database.
We have clarified that both his client and his server are on the same host.

My last advice is that the password he is providing through his other 
clients must be incorrect. I even suggested that he may be validating 
his account on one instance but his tools are attempting to connect to a 
different instance.


And whether I say
   mysql -u root -h localhost  (via Unix socket)

or I say
   mysql -u root -h 127.0.0.1  (via TCP socket)

they may /both/ be reported as 'localhost' in the error message because 
of how the reverse DNS lookup happens during the user authentication 
process and the contents of his local /etc/hosts file.

http://dev.mysql.com/doc/refman/5.6/en/connection-access.html


Does that give you a better image of the current problem?
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

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

RE: Access denied for user 'root'@'localhost' (using password:

[Vikas Shukla]

 YES) on mysql admin
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol=application/pgp-signature;
 boundary=wmcsLTXQx7E3jAVxUD1b39Xfw2SDoi7cu

--wmcsLTXQx7E3jAVxUD1b39Xfw2SDoi7cu
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Please provide the output of the below query.

Select user, host, password from mysql.user;

Thanks

Vikas Shukla
Mail Sent from my Windows Phone From: Reindl Harald
Sent: =E2=80=8E14-=E2=80=8E01-=E2=80=8E2014 01:38
To: mysql@lists.mysql.com
Subject: Re: Access denied for user 'root'@'localhost' (using password:
YES) on mysql admin
*your application* is connecting to mysql
*your application* is using a hostname
*your application* *may* use 127.0.0.1
*your application* should use localhost to *connect to the databse*
*your application* can only use TCP *if there is* a *mysql user* with *that=
 host*

http://dev.mysql.com/doc/refman/5.5/en/can-not-connect-to-server.html

Am 13.01.2014 20:38, schrieb =C3=89rico:
 no connections outside command line are being accepted . I have connected
 through command line , but not using eclipse for example ... it gets the
 same error from the web app
=20
 my apache and pages are in the same computer that mysql
=20
 I am not getting password issues.. otherwise I would not connect through
 command line
=20
 2014/1/13 shawn l.green shawn.l.gr...@oracle.com
=20
 On 1/13/2014 1:49 PM, =C3=89rico wrote:

 Hi

 didn't work

 look ... isn't this something related to this :

 are you using localhost or 127.0.0.1 in the web-application
 root@localhost !=3D root@127.0.0.1 =3D different users

 localhost: Unix-Socket
 127.0.0.1: TCP

 ...

 since I am not able to stabilsh a connection even with using eclipse ..=
.

 my /etc/hosts file :

 ##
 127.0.0.1   localhost
 255.255.255.255 broadcasthost
 ::1 localhost
 #fe80::1%lo0localhost
 127.0.0.1   ericomtxmacbookpro.local
 #127.0.0.1   mysqld


 The thing to remember is that 'localhost' or '127.0.0.1' is where the
 server believes the connection is coming from. The password you must be
 using in your client connection attempt must match the one used on the
 server for the mysql user 'root'@'localhost' or you will not authenticat=
e.

 You also need to remember that if your .php page is on a different
 machine, it is not connecting to the same mysqld that you have running i=
n
 your development machine (your personal environment). Your .PHP page is
 trying to connect to the one running on its host machine. That user 'roo=
t'
 may have an entirely different password.

 Your programs are connecting fine. If they didn't you would get a
 different message. They are failing to authenticate which means that the
 mysqld they are connecting to does not recognize the password you are us=
ing
 for the account you are trying to authenticate as.


--wmcsLTXQx7E3jAVxUD1b39Xfw2SDoi7cu--

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

Re: Access denied for user 'root'@'localhost' (using password: YES) on mysql admin

[Reindl Harald]

Am 13.01.2014 21:47, schrieb shawn l.green:
 Hello Reindl,
 We are not saying he is using HTTP commands to log into his database

we excludes obviously the OP or his overall understanding :-)

Am 13.01.2014 18:54, schrieb Érico:
 using both urls I get the same error :

 http://localhost/mysql/index.php
 http://127.0.0.1/mysql/index.php

 in 127.0.0.1... after I submit the index.php ... it redirects to localhost
 too ..



signature.asc
Description: OpenPGP digital signature

RE: Performance of delete using in

[Rick James]

Please provide
   SHOW CREATE TABLE cdsem_event_message_idx \G
   EXPLAIN SELECT * FROM cdsem_event_message_idx where event_id in () \G
   SHOW VARIABLES LIKE 'autocommit';

These can impact DELETE speed:
   * secondary indexes
   * whether event_id is indexed.
   * disk type and speed -- ordinary SATA vs RAID vs SSD vs ...
   * ENGINE -- SHOW CREATE will provide that info
   * MySQL version -- perhaps IN optimization has improved over time

Rule of Thumb:  100 iops.  Hence 1500 deletes is likely to take 15 seconds if 
they are randomly place, no secondary keys, and on non-RAIDed SATA drive.

DELETEing one row at a time incurs network and parsing overhead, so it is not 
surprising that it is slower.  That seems like a lot of overhead, so I would 
guess you are using InnoDB and have most of autocommit=1 and sync_binlog=1 and 
innodb_flush_log_at_trx_commit=1

 -Original Message-
 From: Denis Jedig [mailto:d...@syneticon.net]
 Sent: Wednesday, April 24, 2013 10:50 PM
 To: mysql@lists.mysql.com
 Subject: Re: Performance of delete using in
 
 Larry,
 
 Am 25.04.2013 02:19, schrieb Larry Martell:
 
  delete from cdsem_event_message_idx where event_id in ()
 
  The in clause has around 1,500 items in it.
 
 Consider creating a temporary table, filling it with your IN
 values and joining it to cdsem_event_message_idx ON event_id for
 deletion.
 
 Kind regards,
 
 Denis Jedig
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


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

Re: Performance of delete using in

[Denis Jedig]

Larry,

Am 25.04.2013 02:19, schrieb Larry Martell:


delete from cdsem_event_message_idx where event_id in ()

The in clause has around 1,500 items in it.


Consider creating a temporary table, filling it with your IN 
values and joining it to cdsem_event_message_idx ON event_id for 
deletion.


Kind regards,

Denis Jedig

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

Re: Performance of delete using in

[Larry Martell]

That is the entire sql statement - I didn't think I needed to list the
1500 ints that are in the in clause.

Also want to mention that I ran explain on it, and it is using the
index on event_id.

On Wed, Apr 24, 2013 at 6:49 PM, Michael Dykman mdyk...@gmail.com wrote:
 You would have to show us the whole sql statement but often 'in' clauses can
 be refactored into equivalent joins which tend to improve performance
 tremendously.

  - michael dykman


 On Wed, Apr 24, 2013 at 8:19 PM, Larry Martell larry.mart...@gmail.com
 wrote:

 I have a table that has 2.5 million rows and 9 columns that are all
 int except for 2 varchar(255) - i.e. not that big of a table. I am
 executing a delete from that table like this:

 delete from cdsem_event_message_idx where event_id in ()

 The in clause has around 1,500 items in it. event_id is an int, and
 there is an index on event_id. This statement is taking 1 hour and 5
 minutes to run. There is nothing else hitting the database at that
 time, and the machine it's running on is 97% idle and has plenty of
 free memory. This seems extremely excessive to me. I would guess it's
 because of the in clause. Is there some better way to do a delete like
 this?

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




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

  May the Source be with you.

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

Re: Performance of delete using in

[Larry Martell]

I changed it to delete one row at a time and it's taking 3 minutes.


On Wed, Apr 24, 2013 at 6:52 PM, Larry Martell larry.mart...@gmail.com wrote:
 That is the entire sql statement - I didn't think I needed to list the
 1500 ints that are in the in clause.

 Also want to mention that I ran explain on it, and it is using the
 index on event_id.

 On Wed, Apr 24, 2013 at 6:49 PM, Michael Dykman mdyk...@gmail.com wrote:
 You would have to show us the whole sql statement but often 'in' clauses can
 be refactored into equivalent joins which tend to improve performance
 tremendously.

  - michael dykman


 On Wed, Apr 24, 2013 at 8:19 PM, Larry Martell larry.mart...@gmail.com
 wrote:

 I have a table that has 2.5 million rows and 9 columns that are all
 int except for 2 varchar(255) - i.e. not that big of a table. I am
 executing a delete from that table like this:

 delete from cdsem_event_message_idx where event_id in ()

 The in clause has around 1,500 items in it. event_id is an int, and
 there is an index on event_id. This statement is taking 1 hour and 5
 minutes to run. There is nothing else hitting the database at that
 time, and the machine it's running on is 97% idle and has plenty of
 free memory. This seems extremely excessive to me. I would guess it's
 because of the in clause. Is there some better way to do a delete like
 this?

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




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

  May the Source be with you.

-- 
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 larry.mart...@gmail.com 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

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: How to return resultset from MySQL Stored Procedure using prepared statement?

[Rick James]

What language are you using?
In Perl, there is
   $sth-more_results;

 -Original Message-
 From: Girish Talluru [mailto:girish.dev1...@gmail.com]
 Sent: Wednesday, March 13, 2013 5:24 AM
 To: mysql@lists.mysql.com
 Subject: How to return resultset from MySQL Stored Procedure using
 prepared statement?
 
 DELIMITER $$
 CREATE PROCEDURE List_IL()
 BEGIN
 
   DECLARE Project_Number_val VARCHAR( 255 );
   DECLARE Temp_List_val VARCHAR(255);
   DECLARE Project_List_val VARCHAR(255);
   DECLARE FoundCount INT;
   DECLARE Project_Number INT;
   DECLARE db_Name VARCHAR(255);
 
 
 
   DECLARE no_more_rows BOOLEAN;
   DECLARE loop_cntr INT DEFAULT 0;
   DECLARE num_rows INT DEFAULT 0;
 
 
   DECLARE   projects_curCURSOR FOR
 SELECT  Project_Id
 FROMProject_Details;
 
 
   DECLARE CONTINUE HANDLER FOR NOT FOUND
 SET no_more_rows = TRUE;
 
 
   OPEN projects_cur;
   select FOUND_ROWS() into num_rows;
 
   the_loop: LOOP
 
 FETCH  projects_cur
 INTO   Project_Number_val;
 
 
 IF no_more_rows THEN
 CLOSE projects_cur;
 LEAVE the_loop;
 END IF;
 
 SET Project_List_val = CONCAT(Project_Number_val, '_List');SET
 db_Name='panel';
 
 SELECT COUNT(1) INTO  FoundCount  FROM information_schema.tables WHERE
 table_schema = `db_Name`  AND table_name = `Project_List_val`;
 
  IF FoundCount = 1 THENSET @Project_Number=Project_Number_val;
 SET @sql = CONCAT(' SELECT Panel_Id,', Project_Number_val,'
 FROM ', @Project_List_val,' Where status=1');
 
 PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;END IF;
 
 SET loop_cntr = loop_cntr + 1;
   END LOOP the_loop;
 
 END $$
 *
 **In the above stored procedure How can I get the all the rows selected
 during execution of prepared statement and after the loop terminates I
 want to return the entire result set whichever calls the stored
 procedure. Can you please help me how to do this?*

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

Re: using LIMIT without ORDER BY

[Johan De Meersman]

- Original Message -
 From: Akshay Suryavanshi akshay.suryavansh...@gmail.com
 
 I am not sure, but if its a MyISAM table, it should be ordered by the
 records insertion order, and in case of InnoDB it should be ordered
 by the clustered index, not necessarily it should be a defined one.

No.

The optimizer may choose to do a full table scan, or it may choose to use an 
index scan. That decision may change due to changes in the data, or because the 
next version of mysql you upgrade to has different (and hopefully better...) 
alghorithms, et cetera.

The ONLY way to ensure consecutive queries return your data in the same order, 
is specifying an order by clause.

Apart from that, I personally prefer to avoid the limit 0,10 /limit 11/20 
technique, because a) rows might have gotten inserted and/or deleted, and b) 
limit is applied to the full resultset.

Instead, order by the PK (or another unique index or combination of indices), 
remember the last record's value(s) and use that as starting point for your 
next query.



-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

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

Re: using LIMIT without ORDER BY

[Akshay Suryavanshi]

Well Johan,

I was referring to a condition when there is no index on the tables, not
even primary keys. Your explanation makes complete sense about the
optimizer and the pagination queries.

Thanks,
Akshay S

On Thu, Dec 13, 2012 at 2:34 PM, Johan De Meersman vegiv...@tuxera.bewrote:



 - Original Message -
  From: Akshay Suryavanshi akshay.suryavansh...@gmail.com
 
  I am not sure, but if its a MyISAM table, it should be ordered by the
  records insertion order, and in case of InnoDB it should be ordered
  by the clustered index, not necessarily it should be a defined one.

 No.

 The optimizer may choose to do a full table scan, or it may choose to use
 an index scan. That decision may change due to changes in the data, or
 because the next version of mysql you upgrade to has different (and
 hopefully better...) alghorithms, et cetera.

 The ONLY way to ensure consecutive queries return your data in the same
 order, is specifying an order by clause.

 Apart from that, I personally prefer to avoid the limit 0,10 /limit 11/20
 technique, because a) rows might have gotten inserted and/or deleted, and
 b) limit is applied to the full resultset.

 Instead, order by the PK (or another unique index or combination of
 indices), remember the last record's value(s) and use that as starting
 point for your next query.



 --
 Unhappiness is discouraged and will be corrected with kitten pictures.

Re: using LIMIT without ORDER BY

[Johan De Meersman]

- Original Message -

 From: Akshay Suryavanshi akshay.suryavansh...@gmail.com

 I was referring to a condition when there is no index on the tables,
 not even primary keys.
If you have a lot of data in there, may I suggest you (temporarily) add a 
unique index and benchmark both methods? As I said, limit n,m is the last 
operation that gets executed, so it requires rows 0-n to be fetched, too. On 
deep pages that can be quite a lot of data needlessly fetched. 

You might find that the expense of maintaining that index may be well worth the 
speedup when fetching pages. 

 Your explanation makes complete sense about the optimizer and the
 pagination queries.

 Thanks,
You're welcome. 

-- 

Unhappiness is discouraged and will be corrected with kitten pictures. 

Re: using LIMIT without ORDER BY

[Akshay Suryavanshi]

I am not sure, but if its a MyISAM table, it should be ordered by the
records insertion order, and in case of InnoDB it should be ordered by the
clustered index, not necessarily it should be a defined one.


On Thu, Dec 13, 2012 at 12:58 PM, jiangwen jiang jiangwen...@gmail.comwrote:

 hi, all:

 There's a confusion. I want to get all the data in table t by pages, using
 Limit SQL without ORDER BY:
 SELECT * FROM t Limit 0,10
 SELECT * FROM t Limit 10, 10
 ...

 Is it right without ORDER BY?
 Is there any default order in table t, to make suer I can get all data in
 the table?

 Thanks
 Regards!

 White

Re: Problem accessing phpmyadmin using IP from remote machine

[Girish Talluru]

Hi Abhishek, Yes I can do that.

On Tue, Dec 11, 2012 at 12:50 PM, abhishek jain
abhishek.netj...@gmail.comwrote:

 Can you ping ip 192.168.1.9 from other machines?
 On Dec 11, 2012 11:21 AM, Girish Talluru girish.dev1...@gmail.com
 wrote:

 Hi Guys,

 I have environment as specified below.

 Main server: Windows Server 2008
 Virtual Box:  Oracle VM
 VM OS: Ubuntu

 Inside Ubuntu I downloaded and configured Apache, MySql, PHP and
 PhpMyadmin
 individually following an article which is mentioned below.

 https://help.ubuntu.com/community/ApacheMySQLPHP

 IP Configuration:
 LAN Address: 192.168.1.9
 VirtualBox Address: 192.168.56.1

 As mentioned in the file I commented the bind address in my.cnf file.

 Using http://localhost/phpmyadmin or http://127.0.0.1/phpmyadmin I could
 able to access the phpmyadmin but when I tried with the LAN/VM address
 http://192.168.1.0/phpmyadmin or http://192.168.56.1/phpmyadmin

 I'm trying this because I have requirement to access phpmyadmin from other
 machines in LAN.

 I tried to change the bind address to VM and LAN address and checked it is
 also not working.

 Can anyone suggest where am I thinking wrong?

 Suggestions please.

 Thanks,
 Girish Talluru

Re: Problem accessing phpmyadmin using IP from remote machine

[abhishek jain]

Can you ping ip 192.168.1.9 from other machines?
On Dec 11, 2012 11:21 AM, Girish Talluru girish.dev1...@gmail.com wrote:

 Hi Guys,

 I have environment as specified below.

 Main server: Windows Server 2008
 Virtual Box:  Oracle VM
 VM OS: Ubuntu

 Inside Ubuntu I downloaded and configured Apache, MySql, PHP and PhpMyadmin
 individually following an article which is mentioned below.

 https://help.ubuntu.com/community/ApacheMySQLPHP

 IP Configuration:
 LAN Address: 192.168.1.9
 VirtualBox Address: 192.168.56.1

 As mentioned in the file I commented the bind address in my.cnf file.

 Using http://localhost/phpmyadmin or http://127.0.0.1/phpmyadmin I could
 able to access the phpmyadmin but when I tried with the LAN/VM address
 http://192.168.1.0/phpmyadmin or http://192.168.56.1/phpmyadmin

 I'm trying this because I have requirement to access phpmyadmin from other
 machines in LAN.

 I tried to change the bind address to VM and LAN address and checked it is
 also not working.

 Can anyone suggest where am I thinking wrong?

 Suggestions please.

 Thanks,
 Girish Talluru

How to start a cluster using MCM after crash and prepare for restore using ndb_restore?

[Bheemsen Aitha]

Hi,

We have MySQL cluster 7.2.7 with the following setup.

1 ndb_mgmd, 1 mysqld on one host
2 ndbmtd on another host

We use MySQL cluster Manager known as MCM (v: 1.1.6) to manage the cluster.
We don't use ndb_mgm client at all.

I wanted to simulate and test the restore using ndb_restore.

In order to test the restore script, here are the steps I followed.

1. Created couple of test tables with data in test database.
2. Took an online backup using START BACKUP at ndb_mgm prompt.
3. Shutdown the cluster using mcm.
4. renamed the datadir folders for ndbmtd.
5. started ndb_mgmd using mcm.
6. Tried start the first ndbmtd with --initial option in mcm.
7. start mysqld using mcm
8. do restore using ndb_restore

My step #6 is always stuck and not working.

Here is my exact command for step #6 in mcm.

step 5:

mcm stop process 49 -f attcluster;
+--+
| Command result   |
+--+
| Process stopped successfully |
+--+
1 row in set (17.47 sec)

step 6:

mcm start process -i -B -f 1 attcluster;
+--+
| Command result
|
+--+
| Operation started successfully. Please check the operation result before
continuing. |
+--+
1 row in set (0.21 sec)

mcm show status -r attcluster;
++--+--+--+---+-+
| NodeId | Process  | Host | Status   | Nodegroup | Package |
++--+--+--+---+-+
| 49 | ndb_mgmd | ut06sandboxdb01  | running  |   | 7.2.7   |
| 50 | mysqld   | ut06sandboxdb01  | stopped  |   | 7.2.7   |
| 1  | ndbmtd   | ut06sandboxdb02  | starting | 0 | 7.2.7   |
| 2  | ndbmtd   | ut06sandboxdb02  | starting | 0 | 7.2.7   |
| 51 | ndbapi   | *ut06sandboxdb01 | added|   | |
| 52 | ndbapi   | *ut06sandboxdb01 | added|   | |
| 53 | ndbapi   | *ut06sandboxdb01 | added|   | |
| 54 | ndbapi   | *ut06sandboxdb01 | added|   | |
++--+--+--+---+-+
8 rows in set (0.05 sec)

I see the following in mcmd.log

[1,ndb_mgmd,0]: 2012-10-27 01:42:28 [MgmtSrvr] INFO -- Node 1: Initial
start, waiting for 2 to connect,  nodes [ all: 1 and 2 connected: 1
no-wait:  ]

I even tried using --nowait-nodes=2 option, but it is not recognized by mcm.
I can't use nodegroup=65536 and StartNoNodegroupTimeout options, as they
require editing of config.ini and mcm does not like that.


My questions is did anyone using mcm simulate restore? If yes, how did you
do it?

Any help is appreciated?

Thanks
-BA

user accounts using Active Directory

[Aastha]

Hello,

I want to create users and give access to different schema within the
database.
I also want to give them selective privileges to access the table in
different schema.

One way is to create users in MySQL and then grant with privileges.

Is there any way to give them access to MySQL database and the tables
within the schema using active directory.
Also. is there a way to create group of privileges and assign users to the
group required.


Thanks,
Aastha Gupta

Re: user accounts using Active Directory

[Ignacio Ocampo]

Hi Aastha,

I think that you can develop a Plugin to do it.

Recently, I post a plugin that allow authenticate users through LDAP Server
(
http://nafiux.com/blog/2012/08/16/mysql-ldap-authentication-plugin-clear-password-client-plugin/
).

More info about MySQL Authentication Plugins:
http://dev.mysql.com/doc/refman/5.5/en/authentication-plugins.html

You can build your own plugin with your needs.

Best regards.

On Wed, Aug 22, 2012 at 9:13 AM, Aastha aast...@gmail.com wrote:

 Hello,

 I want to create users and give access to different schema within the
 database.
 I also want to give them selective privileges to access the table in
 different schema.

 One way is to create users in MySQL and then grant with privileges.

 Is there any way to give them access to MySQL database and the tables
 within the schema using active directory.
 Also. is there a way to create group of privileges and assign users to the
 group required.


 Thanks,
 Aastha Gupta




-- 
Ignacio Ocampo Millán

RE: Best design for a table using variant data

[Rick James]

Even if you get past those issues, you will get to other nasty problems...  
Ugly JOINs, terrible performance, huge disk footprint, etc.

Split the attributes into two groups:
* The ones you _really_ need to search on.
* The rest.
Build columns for the first group; use the appropriate datatypes.
Throw the rest into a JSON string, put that into another column.  (I like to 
compress the JSON and put it into a MEDIUMBLOB.)

For searching,...
Use SQL (WHERE ...) to filter on any attributes in the first group.
Then use your programming language (PHP, etc) to finish the filtering, after 
fetching (and uncompressing) the JSON.

 -Original Message-
 From: Gaston Gloesener [mailto:gaston.gloese...@web.de]
 Sent: Sunday, August 12, 2012 10:58 PM
 To: mysql@lists.mysql.com
 Subject: RE: Best design for a table using variant data
 
  You don't specify how many different types (including min/max values)
  you
 expect to be using. If you expect to end up with a few hundred, then
 you should perhaps consider using an ENUM or SET column directly in the
 data table.
 
 I do not quite understand where the use of enums/sets could be help
 here.
 
 Regarding the number of basic types there are only expected to be a few
 similar to a programming language: At the beginning there will be 4
 types (integer, 64-bit integer, floating point (probably double) and
 strings with their associated constraints. Each single attribute (i.e.
 table row) would only use one of these types.
 
 Possibly my explanations where not clear enough, so let me take a
 virtual example which would match my needs: A database which represents
 a second hand shop. This shop would contain a number of things of
 different types.
 Let's take cars and clothes as example. Those two would have a
 number off attributes which depend on the type. I.e. the car would have
 Brand (String), Model (String), manufacturing year (Inetger, max
 2012), horsepower (integer), ... while some of these are meaningless
 for the clothes which will have size, ...
 
 These attributes are not known at development type and are to be
 defined by the users of the database and may be extended over time. As
 said above this is a virtual example which does not match the actual
 much more complex database use.
 
 / Gaston
 
 -Original Message-
 From: Carsten Pedersen [mailto:cars...@bitbybit.dk]
 Sent: Friday, August 10, 2012 15:33
 To: Gaston Gloesener
 Cc: mysql@lists.mysql.com
 Subject: Re: Best design for a table using variant data
 
 You don't specify how many different types (including min/max values)
 you expect to be using. If you expect to end up with a few hundred,
 then you should perhaps consider using an ENUM or SET column directly
 in the data table.
 
 / Carsten
 
 On 10.08.2012 10:51, Gaston Gloesener wrote:
  Hello,
 
 
 
  I am currently facing a design where a table (virtually) needs to
  store attributes of a topic (related table). The attributes can be
  user defined, i.e. not known at development type and depend on other
  factors. Each attributes value can be one of different types (int,
  int64, double, string) and may have constraints like min, max or
  length
 (string).
 
 
 
  Thus the data type would be modeled as variant in some programming
  languages, but this is not an option in SQL (beside the MS SQLserver
  sql_variant extension).
 
 
 
  So, how to simulate this in SQL.
 
 
 
  Basically there would be one table describing the attributes type
  (Type identifier, min/max,.) and one table for the values itself.
 
 
 
  The design I am currently thinking of would be to make exactly these
  two tables, with the attributes having a Dataype column and iMin,iMax
  for integer, i64Min, i64Max, fMin,fMax for double , sMinLen, sMaxLen
  for strings. The same applies to the value table which will have
  iValue, i64Value, fValue, string columns to hold the actual data.
 
 
 
  Now the columns will be filled according to the data type, columns
 not
  matching the type will be NULL.
 
 
 
  This means that each row in the table will have virtual space for any
  data type which violates database normalization. However it seems to
  me to be the best deal for performance and data space as NULL takes
  virtually no room
  (4/8 bytes in total for a number of fields in some circumstances) and
  requires no complex queries.
 
 
 
  One could also imagine to have the constraints moved to a separate
  table and interpreted according to the data type. Also a table for
  each type could be imagined but this will make the queries very
  complicated working against performance.
 
 
 
  Note: The model has to work for huge databases
 
 
 
  Anybody has a better alternative ?
 
 
 
 
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


--
MySQL

RE: Best design for a table using variant data

[Gaston Gloesener]

 You don't specify how many different types (including min/max values) you
expect to be using. If you expect to end up with a few hundred, then you
should perhaps consider using an ENUM or SET column directly in the data
table.

I do not quite understand where the use of enums/sets could be help here. 

Regarding the number of basic types there are only expected to be a few
similar to a programming language: At the beginning there will be 4 types
(integer, 64-bit integer, floating point (probably double) and strings with
their associated constraints. Each single attribute (i.e. table row) would
only use one of these types.

Possibly my explanations where not clear enough, so let me take a virtual
example which would match my needs: A database which represents a second
hand shop. This shop would contain a number of things of different types.
Let's take cars and clothes as example. Those two would have a number
off attributes which depend on the type. I.e. the car would have Brand
(String), Model (String), manufacturing year (Inetger, max 2012),
horsepower (integer), ... while some of these are meaningless for the
clothes which will have size, ... 

These attributes are not known at development type and are to be defined by
the users of the database and may be extended over time. As said above this
is a virtual example which does not match the actual much more complex
database use. 

/ Gaston

-Original Message-
From: Carsten Pedersen [mailto:cars...@bitbybit.dk] 
Sent: Friday, August 10, 2012 15:33
To: Gaston Gloesener
Cc: mysql@lists.mysql.com
Subject: Re: Best design for a table using variant data

You don't specify how many different types (including min/max values) you
expect to be using. If you expect to end up with a few hundred, then you
should perhaps consider using an ENUM or SET column directly in the data
table.

/ Carsten

On 10.08.2012 10:51, Gaston Gloesener wrote:
 Hello,



 I am currently facing a design where a table (virtually) needs to 
 store attributes of a topic (related table). The attributes can be 
 user defined, i.e. not known at development type and depend on other 
 factors. Each attributes value can be one of different types (int, 
 int64, double, string) and may have constraints like min, max or length
(string).



 Thus the data type would be modeled as variant in some programming 
 languages, but this is not an option in SQL (beside the MS SQLserver 
 sql_variant extension).



 So, how to simulate this in SQL.



 Basically there would be one table describing the attributes type 
 (Type identifier, min/max,.) and one table for the values itself.



 The design I am currently thinking of would be to make exactly these 
 two tables, with the attributes having a Dataype column and iMin,iMax 
 for integer, i64Min, i64Max, fMin,fMax for double , sMinLen, sMaxLen 
 for strings. The same applies to the value table which will have 
 iValue, i64Value, fValue, string columns to hold the actual data.



 Now the columns will be filled according to the data type, columns not 
 matching the type will be NULL.



 This means that each row in the table will have virtual space for any 
 data type which violates database normalization. However it seems to 
 me to be the best deal for performance and data space as NULL takes 
 virtually no room
 (4/8 bytes in total for a number of fields in some circumstances) and 
 requires no complex queries.



 One could also imagine to have the constraints moved to a separate 
 table and interpreted according to the data type. Also a table for 
 each type could be imagined but this will make the queries very 
 complicated working against performance.



 Note: The model has to work for huge databases



 Anybody has a better alternative ?







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




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

Best design for a table using variant data

[Gaston Gloesener]

Hello,

 

I am currently facing a design where a table (virtually) needs to store
attributes of a topic (related table). The attributes can be user defined,
i.e. not known at development type and depend on other factors. Each
attributes value can be one of different types (int, int64, double, string)
and may have constraints like min, max or length (string). 

 

Thus the data type would be modeled as variant in some programming
languages, but this is not an option in SQL (beside the MS SQLserver
sql_variant extension).

 

So, how to simulate this in SQL.

 

Basically there would be one table describing the attributes type (Type
identifier, min/max,.) and one table for the values itself.

 

The design I am currently thinking of would be to make exactly these two
tables, with the attributes having a Dataype column and iMin,iMax for
integer, i64Min, i64Max, fMin,fMax for double , sMinLen, sMaxLen for
strings. The same applies to the value table which will have iValue,
i64Value, fValue, string columns to hold the actual data.

 

Now the columns will be filled according to the data type, columns not
matching the type will be NULL.

 

This means that each row in the table will have virtual space for any data
type which violates database normalization. However it seems to me to be the
best deal for performance and data space as NULL takes virtually no room
(4/8 bytes in total for a number of fields in some circumstances) and
requires no complex queries.

 

One could also imagine to have the constraints moved to a separate table and
interpreted according to the data type. Also a table for each type could be
imagined but this will make the queries very complicated working against
performance.

 

Note: The model has to work for huge databases

 

Anybody has a better alternative ?

 

 

Re: Best design for a table using variant data

[Carsten Pedersen]

You don't specify how many different types (including min/max values) 
you expect to be using. If you expect to end up with a few hundred, then 
you should perhaps consider using an ENUM or SET column directly in the 
data table.


/ Carsten

On 10.08.2012 10:51, Gaston Gloesener wrote:

Hello,



I am currently facing a design where a table (virtually) needs to store
attributes of a topic (related table). The attributes can be user defined,
i.e. not known at development type and depend on other factors. Each
attributes value can be one of different types (int, int64, double, string)
and may have constraints like min, max or length (string).



Thus the data type would be modeled as variant in some programming
languages, but this is not an option in SQL (beside the MS SQLserver
sql_variant extension).



So, how to simulate this in SQL.



Basically there would be one table describing the attributes type (Type
identifier, min/max,.) and one table for the values itself.



The design I am currently thinking of would be to make exactly these two
tables, with the attributes having a Dataype column and iMin,iMax for
integer, i64Min, i64Max, fMin,fMax for double , sMinLen, sMaxLen for
strings. The same applies to the value table which will have iValue,
i64Value, fValue, string columns to hold the actual data.



Now the columns will be filled according to the data type, columns not
matching the type will be NULL.



This means that each row in the table will have virtual space for any data
type which violates database normalization. However it seems to me to be the
best deal for performance and data space as NULL takes virtually no room
(4/8 bytes in total for a number of fields in some circumstances) and
requires no complex queries.



One could also imagine to have the constraints moved to a separate table and
interpreted according to the data type. Also a table for each type could be
imagined but this will make the queries very complicated working against
performance.



Note: The model has to work for huge databases



Anybody has a better alternative ?








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

Unresolved symbols with mysqlclient in DEBUG mode using VC++ 2010 Express

[Miguel Cardenas]

Hello friends

I'm back to MySQL programming using the C API... it works fine when I
compile using the RELEASE mode and C:\Program Files (x86)\MySQL\MySQL
Server 5.5\lib but if I choose the DEBUG mode and C:\Program Files
(x86)\MySQL\MySQL Server 5.5\lib\debug it fails at link time:

1mysqlclient.lib(dbug.obj) : error LNK2019: unresolved external
symbol __CrtSetReportFile referenced in function _DbugExit
1mysqlclient.lib(my_init.obj) : error LNK2001: unresolved external
symbol __CrtSetReportFile
1mysqlclient.lib(dbug.obj) : error LNK2019: unresolved external
symbol __CrtSetReportMode referenced in function _DbugExit
1mysqlclient.lib(my_init.obj) : error LNK2001: unresolved external
symbol __CrtSetReportMode
1mysqlclient.lib(my_init.obj) : error LNK2019: unresolved external
symbol __CrtDumpMemoryLeaks referenced in function _my_end
1mysqlclient.lib(my_init.obj) : error LNK2019: unresolved external
symbol __CrtCheckMemory referenced in function _my_end

I read some posts that found in google about this error and found that
it should be used the dynamic library libmysql instead of the static
mysqlclient when debugging, but the lib\debug directory contains just
the static library mysqlclient...

Do I have to include an aditional library so the symbols are resolved
correctly? The release mode works perfect and the application runs
fine in that way only...

Thanks,
Miguel

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

Re: Unresolved symbols with mysqlclient in DEBUG mode using VC++ 2010 Express

[Lars Nilsson]

On Tue, Jun 19, 2012 at 2:48 AM, Miguel Cardenas renit...@gmail.com wrote:
 Hello friends
 1mysqlclient.lib(dbug.obj) : error LNK2019: unresolved external
 symbol __CrtSetReportFile referenced in function _DbugExit
 1mysqlclient.lib(my_init.obj) : error LNK2001: unresolved external
 symbol __CrtSetReportFile
 1mysqlclient.lib(dbug.obj) : error LNK2019: unresolved external
 symbol __CrtSetReportMode referenced in function _DbugExit
 1mysqlclient.lib(my_init.obj) : error LNK2001: unresolved external
 symbol __CrtSetReportMode
 1mysqlclient.lib(my_init.obj) : error LNK2019: unresolved external
 symbol __CrtDumpMemoryLeaks referenced in function _my_end
 1mysqlclient.lib(my_init.obj) : error LNK2019: unresolved external
 symbol __CrtCheckMemory referenced in function _my_end

 Do I have to include an aditional library so the symbols are resolved
 correctly? The release mode works perfect and the application runs
 fine in that way only...

The missing functions should be provided by the Visual Studio debug
runtime library. Are all source files in your own code compiled and
program linked with one, and only one, of the flags /MDd (dynamic
linking) or /MTd (static linking)?

Lars Nilsson

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

RE: Unresolved symbols with mysqlclient in DEBUG mode using VC++ 2010 Express

[Martin Gainty]

Miguel..

i do not have VC2010 but as my memory recalls the
C runtime library (MSVCRT*.dll) would be the first library on %PATH% .. and all 
missing functions *should* be located inside the dll (e.g. __CrtSetReportFile )

can you
1) locate MSVCRT*.dll on your %PATH%
2) dumpbin -exports MSVCRT*.dll

Saludos Cordiales (desde EEUU)
Martin 
__ 
Porfavor no altere esta communicacion..Gracias

 Date: Tue, 19 Jun 2012 10:24:48 -0400
 Subject: Re: Unresolved symbols with mysqlclient in DEBUG mode using VC++ 
 2010 Express
 From: chamael...@gmail.com
 To: renit...@gmail.com
 CC: mysql@lists.mysql.com
 
 On Tue, Jun 19, 2012 at 2:48 AM, Miguel Cardenas renit...@gmail.com wrote:
  Hello friends
  1mysqlclient.lib(dbug.obj) : error LNK2019: unresolved external
  symbol __CrtSetReportFile referenced in function _DbugExit
  1mysqlclient.lib(my_init.obj) : error LNK2001: unresolved external
  symbol __CrtSetReportFile
  1mysqlclient.lib(dbug.obj) : error LNK2019: unresolved external
  symbol __CrtSetReportMode referenced in function _DbugExit
  1mysqlclient.lib(my_init.obj) : error LNK2001: unresolved external
  symbol __CrtSetReportMode
  1mysqlclient.lib(my_init.obj) : error LNK2019: unresolved external
  symbol __CrtDumpMemoryLeaks referenced in function _my_end
  1mysqlclient.lib(my_init.obj) : error LNK2019: unresolved external
  symbol __CrtCheckMemory referenced in function _my_end
 
  Do I have to include an aditional library so the symbols are resolved
  correctly? The release mode works perfect and the application runs
  fine in that way only...
 
 The missing functions should be provided by the Visual Studio debug
 runtime library. Are all source files in your own code compiled and
 program linked with one, and only one, of the flags /MDd (dynamic
 linking) or /MTd (static linking)?
 
 Lars Nilsson
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql
 
  

Re: Unresolved symbols with mysqlclient in DEBUG mode using VC++ 2010 Express

[Lars Nilsson]

On Tue, Jun 19, 2012 at 10:47 AM, Martin Gainty mgai...@hotmail.com wrote:
 Miguel..

 i do not have VC2010 but as my memory recalls the
 C runtime library (MSVCRT*.dll) would be the first library on %PATH% .. and
 all missing functions *should* be located inside the dll (e.g.
 __CrtSetReportFile )

 can you
 1) locate MSVCRT*.dll on your %PATH%
 2) dumpbin -exports MSVCRT*.dll

The problem is at compile/link-time not run-time dll lookup, so he'll
first need to make sure he can link. Once it links, I'd hazard a guess
it'll run ok (assuming the program is correct..)

Lars Nilsson

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

Re: Unresolved symbols with mysqlclient in DEBUG mode using VC++ 2010 Express

[Miguel Cardenas]

Hello Lars

After hours of testing different project configurations, finally I was
able to compile with debug mode... still pending the test of
debugging, but just created the DLL (it was a library that I'm
developing) without errors...

The configurations I used (VC++ 2010 Express) were:

RELEASE:
- Runtime: /MT (static threaded)
- Library: mysqlclient.lib (...\mysql\lib)
- Ignore library: LIBCMTD.lib (without this does not link the release)
- Debug: NO

RELEASE:
- Runtime: /MTd (static threaded debug)
- Library: mysqlclient.lib (...\mysql\lib\debug)
- Ignore library: empty (now allowed LIBCMTD.lib or does not link the debug)
- Debug: YES

If I try to use /MD or /MDd does not compile, if it works with /MT(d)
will leave it that way XD

I'm going to test the debug version (one thing is that compiled and
another one that works =) and if get stuck again will post it...

Thanks for your response

P.D. One last question, is it right to use ...\mysql\lib\debug
directory for debug version? or it should be set to ...\mysql\lib
in both cases and the linker chooses the right one? If compiling in
debug mode it works with both ...\mysql\lib and
...\mysql\lib\debug in DEBUG mode... maybe running the debugger will
not work with the lib, still have to test, but at least it compiles
with no errors... can you comment something about this?

On Tue, Jun 19, 2012 at 2:24 PM, Lars Nilsson chamael...@gmail.com wrote:
 On Tue, Jun 19, 2012 at 2:48 AM, Miguel Cardenas renit...@gmail.com wrote:
 Hello friends
 1mysqlclient.lib(dbug.obj) : error LNK2019: unresolved external
 symbol __CrtSetReportFile referenced in function _DbugExit
 1mysqlclient.lib(my_init.obj) : error LNK2001: unresolved external
 symbol __CrtSetReportFile
 1mysqlclient.lib(dbug.obj) : error LNK2019: unresolved external
 symbol __CrtSetReportMode referenced in function _DbugExit
 1mysqlclient.lib(my_init.obj) : error LNK2001: unresolved external
 symbol __CrtSetReportMode
 1mysqlclient.lib(my_init.obj) : error LNK2019: unresolved external
 symbol __CrtDumpMemoryLeaks referenced in function _my_end
 1mysqlclient.lib(my_init.obj) : error LNK2019: unresolved external
 symbol __CrtCheckMemory referenced in function _my_end

 Do I have to include an aditional library so the symbols are resolved
 correctly? The release mode works perfect and the application runs
 fine in that way only...

 The missing functions should be provided by the Visual Studio debug
 runtime library. Are all source files in your own code compiled and
 program linked with one, and only one, of the flags /MDd (dynamic
 linking) or /MTd (static linking)?

 Lars Nilsson

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

Re: Unresolved symbols with mysqlclient in DEBUG mode using VC++ 2010 Express

[Lars Nilsson]

On Tue, Jun 19, 2012 at 11:56 AM, Miguel Cardenas renit...@gmail.com wrote:
 RELEASE:
 - Runtime: /MT (static threaded)
 - Library: mysqlclient.lib (...\mysql\lib)
 - Ignore library: LIBCMTD.lib (without this does not link the release)
 - Debug: NO

 RELEASE:
 - Runtime: /MTd (static threaded debug)
 - Library: mysqlclient.lib (...\mysql\lib\debug)
 - Ignore library: empty (now allowed LIBCMTD.lib or does not link the debug)
 - Debug: YES

 If I try to use /MD or /MDd does not compile, if it works with /MT(d)
 will leave it that way XD

 I'm going to test the debug version (one thing is that compiled and
 another one that works =) and if get stuck again will post it...

 Thanks for your response

 P.D. One last question, is it right to use ...\mysql\lib\debug
 directory for debug version? or it should be set to ...\mysql\lib
 in both cases and the linker chooses the right one? If compiling in
 debug mode it works with both ...\mysql\lib and
 ...\mysql\lib\debug in DEBUG mode... maybe running the debugger will
 not work with the lib, still have to test, but at least it compiles
 with no errors... can you comment something about this?

In general, you'd want to ensure every object file and static library
linked into your program is compiled with the same runtime library
selected /MT[d] or /MD[d], to ensure a proper working environment. If
you were to link with libmysql.lib you'd avoid having to match up
these flags for this particular library for your own code. Mixing and
matching these flags among things linked into a executable or dll is a
recipe for a headache on Windows.

Regarding your question about the directory, you'd more than likely
want to link with the one in the debug directory, I think, to get
useful stack traces into library code when debugging.

Lars Nilsson

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