Re: MacOS Server 5 problem after upgrade

[Matthias Schmidt]

> Am 04.09.2016 um 23:35 schrieb Reindl Harald :
> 
> 
> 
> Am 04.09.2016 um 16:17 schrieb Matthias Schmidt:
>>> Am 04.09.2016 um 16:29 schrieb Reindl Harald :
>>> 
>>> Am 04.09.2016 um 08:40 schrieb Matthias Schmidt:
 2016-09-04 15:25:19 85518 [ERROR] /usr/local/mysql/bin/mysqld: Can't 
 create/write to file '/usr/local/mysql/data/mysqld.local.pid' (Errcode: 
 102 - Operation not supported on socket)
 2016-09-04 15:25:19 85518 [ERROR] Can't start server: can't create PID 
 file: Operation not supported on socket
 
 the socket is created but then something goes wrong
>>> 
>>> that sounds like socket and pid are configured for the same location
>>> what about "rm -f /usr/local/mysql/data/mysqld.local.pid" and show us your 
>>> config
>>> 
>> 
>> thanks, that did the trick and made it run, but now I just discovered that 
>> my databases are all gone …
>> Of course I have backups, but really no experience how to get the stuff back 
>> :(
> 
> just configure “datadir” to the correct location and get familar with mysql 
> basics and your operating system

there have been 2 problems:
- the last mysql update didn’t do the update and the data folder didn’t contain 
the databases :-0
- the sock file was created in the temp folder instead of the var folder, after 
setting the sock file to the var/mysql folder all works fine

besides the control panel, it “thinks" the server is not running, although it 
is, so I guess I can delete it, as it is also not anymore distributed with the 
current versions for OS10.11


so, thanks especially to Harald, much appreciated :-)
Matthias



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: MacOS Server 5 problem after upgrade

[Reindl Harald]

Am 04.09.2016 um 16:17 schrieb Matthias Schmidt:

Am 04.09.2016 um 16:29 schrieb Reindl Harald :

Am 04.09.2016 um 08:40 schrieb Matthias Schmidt:

2016-09-04 15:25:19 85518 [ERROR] /usr/local/mysql/bin/mysqld: Can't 
create/write to file '/usr/local/mysql/data/mysqld.local.pid' (Errcode: 102 - 
Operation not supported on socket)
2016-09-04 15:25:19 85518 [ERROR] Can't start server: can't create PID file: 
Operation not supported on socket

the socket is created but then something goes wrong


that sounds like socket and pid are configured for the same location
what about "rm -f /usr/local/mysql/data/mysqld.local.pid" and show us your 
config



thanks, that did the trick and made it run, but now I just discovered that my 
databases are all gone …
Of course I have backups, but really no experience how to get the stuff back :(


just configure "datadir" to the correct location and get familar with 
mysql basics and your operating system


the content of datadir can be moved between differen toperating systems 
and usually even different mysql versions (given the ones of our 
mainserver was created on windows, moved to osx and finally moved to 
linux in mysql4 times while now running with maridab10.0)





signature.asc
Description: OpenPGP digital signature

Re: MacOS Server 5 problem after upgrade

[Matthias Schmidt]

> Am 04.09.2016 um 16:29 schrieb Reindl Harald :
> 
> 
> 
> Am 04.09.2016 um 08:40 schrieb Matthias Schmidt:
>> 2016-09-04 15:25:19 85518 [ERROR] /usr/local/mysql/bin/mysqld: Can't 
>> create/write to file '/usr/local/mysql/data/mysqld.local.pid' (Errcode: 102 
>> - Operation not supported on socket)
>> 2016-09-04 15:25:19 85518 [ERROR] Can't start server: can't create PID file: 
>> Operation not supported on socket
>> 
>> the socket is created but then something goes wrong
> 
> that sounds like socket and pid are configured for the same location
> what about "rm -f /usr/local/mysql/data/mysqld.local.pid" and show us your 
> config
> 
> 

thanks, that did the trick and made it run, but now I just discovered that my 
databases are all gone …
Of course I have backups, but really no experience how to get the stuff back :(
thanks
Matthias



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: MacOS Server 5 problem after upgrade

[Reindl Harald]

Am 04.09.2016 um 08:40 schrieb Matthias Schmidt:

2016-09-04 15:25:19 85518 [ERROR] /usr/local/mysql/bin/mysqld: Can't 
create/write to file '/usr/local/mysql/data/mysqld.local.pid' (Errcode: 102 - 
Operation not supported on socket)
2016-09-04 15:25:19 85518 [ERROR] Can't start server: can't create PID file: 
Operation not supported on socket

the socket is created but then something goes wrong


that sounds like socket and pid are configured for the same location
what about "rm -f /usr/local/mysql/data/mysqld.local.pid" and show us 
your config





signature.asc
Description: OpenPGP digital signature

MacOS Server 5 problem after upgrade

[Matthias Schmidt]

Hello,
sorry if this question has been asked already - at least google din’t find a 
sufficient answer :(

I have been upgrading a MacOS 10.6 server to serverv5, which is MacOS 10.11

I was running before: mysql-5.5.40-osx10.6-x86_64

upgrade has been always a no-brainer: download the dmg and run the installer

but now after upgrading to mysql-5.6.31-osx10.11-x86_64
mysql is crashing:
09-03 18:23:07 51218 [Note] IPv6 is available.
2016-09-03 18:23:07 51218 [Note]   - '::' resolves to '::';
2016-09-03 18:23:07 51218 [Note] Server socket created on IP: '::'.
2016-09-03 18:23:07 51218 [ERROR] /usr/local/mysql/bin/mysqld: Can't 
create/write to file '/usr/local/mysql/data/mysqld.local.pid' (Errcode: 102 - 
Operation not supported on socket)
2016-09-03 18:23:07 51218 [ERROR] Can't start server: can't create PID file: 
Operation not supported on socket

I set the bind to the local ip, but it doesn’t change anything

2016-09-03 19:16:08 2125 [Note]   - '192.168.2.10' resolves to '192.168.2.10';
2016-09-03 19:16:08 2125 [Note] Server socket created on IP: '192.168.2.10'.
2016-09-03 19:16:08 2125 [ERROR] /usr/local/mysql/bin/mysqld: Can't 
create/write to file '/usr/local/mysql/data/mysqld.local.pid' (Errcode: 102 - 
Operation not supported on socket)
2016-09-03 19:16:08 2125 [ERROR] Can’t start server: can't create PID file: 
Operation not supported on socket

same happens with local ip:
2016-09-04 15:25:19 85518 [Note] Server hostname (bind-address): '127.0.0.1'; 
port: 3306
2016-09-04 15:25:19 85518 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2016-09-04 15:25:19 85518 [Note] Server socket created on IP: '127.0.0.1'.
2016-09-04 15:25:19 85518 [ERROR] /usr/local/mysql/bin/mysqld: Can't 
create/write to file '/usr/local/mysql/data/mysqld.local.pid' (Errcode: 102 - 
Operation not supported on socket)
2016-09-04 15:25:19 85518 [ERROR] Can't start server: can't create PID file: 
Operation not supported on socket

the socket is created but then something goes wrong.

as I couldn’t find anything, which really would relate to the problem, I guess 
I miss something here.

thanks for any input
Matthias


signature.asc
Description: Message signed with OpenPGP using GPGMail

compile problem with newest version of mysql-server

[Zhudacai]

Hi all,
Recently, I got the newest version of mysql-server from the github,
Bug I got some compile problem as follows:

cmake -DCMAKE_INSTALL_PREFIX=/u04/my3306 \
-DMYSQL_DATADIR=/u04/my3306/data -DMYSQL_USER=mysql \
-DSYSCONFDIR=/etc  -DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DMYSQL_UNIX_ADDR=/u04/my3306/run/mysql.sock \
-DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1  \
-DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci -DCMAKE_C_FLAGS_RELWITHDEBINFO="-O2 -g" \
-DCMAKE_CXX_FLAGS_RELWITHDEBINFO="-O2 -g" \
-DCMAKE_C_FLAGS="-O2 -g" -DCMAKE_CXX_FLAGS="-O2 -g" -DWITH_BOOST=/root/


-- Looking for kqueue
-- Looking for kqueue - not found
-- Looking for EVFILT_TIMER
-- Looking for EVFILT_TIMER - not found
CMake Error at configure.cmake:540 (MESSAGE):
  No mysys timer support detected!
Call Stack (most recent call first):
  CMakeLists.txt:443 (INCLUDE)


-- Configuring incomplete, errors occurred!
See also "/root/mysql/mysql-server-mysql-5.7.8/CMakeFiles/CMakeOutput.log".
See also "/root/mysql/mysql-server-mysql-5.7.8/CMakeFiles/CMakeError.log".

And end of CMakeError.log as follows, and it seems like I had miss event.h file.

/root/mysql/mysql-server-mysql-5.7.8/CMakeFiles/CMakeTmp/CheckSymbolExists.c:3:23:
 fatal error: sys/event.h: No such file or directory
#include 
   ^
compilation terminated.
gmake[1]: *** [CMakeFiles/cmTryCompileExec2077689452.dir/CheckSymbolExists.c.o] 
Error 1
gmake[1]: Leaving directory 
`/root/mysql/mysql-server-mysql-5.7.8/CMakeFiles/CMakeTmp'
gmake: *** [cmTryCompileExec2077689452/fast] Error 2

File 
/root/mysql/mysql-server-mysql-5.7.8/CMakeFiles/CMakeTmp/CheckSymbolExists.c:
/* */
#include 
#include 
#include 

int main(int argc, char** argv)
{
  (void)argv;
#ifndef EVFILT_TIMER
  return ((int*)(_TIMER))[argc];
#else
  (void)argc;
  return 0;
#endif
}

Can anyone knows how to fix this problem ?

Dacai.

Re: innodb log sequence problem

[Csepregi Árpád]

Thank you for answer. The problem is that I wrote in previous message 
that there is no sql backup just the files for binary backup. Hardware 
we are using is a simple laptop with Windows 7 that runs 5.1 server in 
case the originally installed files are in use. It runs an 5.5 server 
paralelly as well without any problems.


2015.08.05. 17:17 keltezéssel, Reindl Harald írta:



Am 05.08.2015 um 17:06 schrieb Csepregi Árpád:

150805 17:02:31  InnoDB: Page dump in ascii and hex (16384 bytes):
hex...
150805 17:02:31  InnoDB: Page checksum 1094951825, prior-to-4.0.14-form
checksum 1449969277
InnoDB: stored checksum 1467223489, prior-to-4.0.14-form stored checksum
87759728
InnoDB: Page lsn 1054562273 1692468334, low 4 bytes of lsn at page end
3304862103
InnoDB: Page number (if stored to page already) 544833488,
InnoDB: space id (if created with = MySQL-4.1.1 and stored already)
960999566
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup

Does anyone have any idea how to recover?


check hardware, especially memory and restore your backup!



--
Csepregi Árpád

Integ Rendszerház Kft.
Az Ön szolgálatában mindennap.

06-70-629-2114
www.integ.hu

Online pénztárgép naplófájl kiolvasó rendszer
WWW.KONTROLLSZALAG.HU


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

innodb log sequence problem

[Csepregi Árpád]

Hello,

We are facing a strange innodb related problem. Our client ran mysql 5.1 
on WinXP having file_per_table disabled. OS crashed after 5 years 
continuous running and our client of course does not have any backup 
(big company with own IT department so we do not have acces to their 
system and IT policy).

We received the mysql folders to see whether we can recover data somehow.

We installed a new myql 5.1 instance. Changed my.ini in program 
files\mysql\msql server 5.1 either changed bin folder all in all. We 
changed data folder as well to the crashed on containing all database 
folders with all .frm files ib_logfile0, ib_logfile1 and ibdata1 as 
well. Trying to start mysql service log says the following:


50805 16:58:28 [Note] Plugin 'FEDERATED' is disabled.
150805 16:58:28  InnoDB: Initializing buffer pool, size = 47.0M
150805 16:58:28  InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file .\ib_logfile0 is of different size 0 10485808 bytes
InnoDB: than specified in the .cnf file 0 25165824 bytes!
150805 16:58:28 [ERROR] Plugin 'InnoDB' init function returned error.
150805 16:58:28 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE 
failed.

150805 16:58:28 [ERROR] Unknown/unsupported table type: INNODB
150805 16:58:28 [ERROR] Aborting

Strange is that ib_logfile0 is 10MB+48 bytes however as far as I know 
innodb_log_file_size system variable can only be added in M.


We tryed to remove ib_logfile0 and ib_logfile1 from data folder and 
tryed to start the instance again. Logfiles were created but got the 
following error mesage in error log:


150805 17:02:29 [Note] Plugin 'FEDERATED' is disabled.
150805 17:02:30  InnoDB: Initializing buffer pool, size = 47.0M
150805 17:02:30  InnoDB: Completed initialization of buffer pool
150805 17:02:30  InnoDB: Log file .\ib_logfile0 did not exist: new to be 
created

InnoDB: Setting log file .\ib_logfile0 size to 24 MB
InnoDB: Database physically writes the file full: wait...
150805 17:02:30  InnoDB: Log file .\ib_logfile1 did not exist: new to be 
created

InnoDB: Setting log file .\ib_logfile1 size to 24 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
150805 17:02:31  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
150805 17:02:31  InnoDB: Error: space id and page n:o stored in the page
InnoDB: read in are 960999566:544833488, should be 0:7!
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
150805 17:02:31  InnoDB: Page dump in ascii and hex (16384 bytes):
hex...
150805 17:02:31  InnoDB: Page checksum 1094951825, prior-to-4.0.14-form 
checksum 1449969277
InnoDB: stored checksum 1467223489, prior-to-4.0.14-form stored checksum 
87759728
InnoDB: Page lsn 1054562273 1692468334, low 4 bytes of lsn at page end 
3304862103

InnoDB: Page number (if stored to page already) 544833488,
InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 
960999566

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also 
http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html

InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.

We tried to start with system variable innodb_force_recovery set 1-6 
none of them helped.


Does anyone have any idea how to recover?

Many thanks in advance.

Arpad Csepregi


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

Re: innodb log sequence problem

[Reindl Harald]

Am 05.08.2015 um 17:06 schrieb Csepregi Árpád:

150805 17:02:31  InnoDB: Page dump in ascii and hex (16384 bytes):
hex...
150805 17:02:31  InnoDB: Page checksum 1094951825, prior-to-4.0.14-form
checksum 1449969277
InnoDB: stored checksum 1467223489, prior-to-4.0.14-form stored checksum
87759728
InnoDB: Page lsn 1054562273 1692468334, low 4 bytes of lsn at page end
3304862103
InnoDB: Page number (if stored to page already) 544833488,
InnoDB: space id (if created with = MySQL-4.1.1 and stored already)
960999566
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup

Does anyone have any idea how to recover?


check hardware, especially memory and restore your backup!



signature.asc
Description: OpenPGP digital signature

Re: password problem

[Martin Mueller]

Data directory path mention in cnf is of old mysql.

Make a fresh data directory, configure it in configuration file and execute 
mysqlinstall_db,


I don't understand the sentence about the data directory path mention.  The 
my.cnf file is at /etc/my.cnf . It doesn't have any data directory path 
mention, but neither does the my.cnf file on a laptop, which works. So there 
seems to be nothing wrong with the location or content of the my.cnf file.

On Fri, Jul 31, 2015 at 5:11 AM, Martin Mueller 
martinmuel...@northwestern.edumailto:martinmuel...@northwestern.edu wrote:
I have installed mysql 5.1.73 on an old Mac Pro running OS Lion. I cannot
run the mysql command because it challenges me for a password. But I did
not set any password, either for the root, for mysql, or for myself as a
user.

So the installation has somehow installed passwords about which I know
nothing or there is some error in the installation process.

There is a lot on the Web about resetting a forgotten password. But the
assumption is always that you can get at the program via some other
password. But in this case every door is shut.

Does anybody recognize this problem? I've uninstalled and re-installed the
program, but the results are always the same.


Martin Mueller
Professor emeritus of English and Classics
Northwestern University



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




--
Thanks and Regards:

Nikhil Anand

+91 9650024197

Re: password problem

[Reindl Harald]

Am 31.07.2015 um 14:40 schrieb Martin Mueller:

Sorry  for the off-list reply. It was an oversight.

That said, the instructions for resetting a forgotten root password have a
section for Windows and a section for Unix. The Unix section begins as
follows:

1. Log on to your system as the Unix user that the MySQL server runs as
(for example, mysql).

But if I do this with the command 'mysql -u mysql I get the answer

Access denied for user 'mysql'@'localhost' (using password: NO)

I can do this as super user or normal, and I can try passwords from
earlier installations, but none of them work. So I am stopped dead in my
tracks, am I not?


what do you not understand in:

 Resetting the Root Password: Generic Instructions
 Stop the MySQL server if necessary, then restart it with the
 --skip-grant-tables option

jesus christ, put skip-grant-tables in your my.cnf, make sure the 
server is not reachable from outside and just type myysql -u root and 
don't forget remove skip-grant-tables after you defined a password you 
are knowing and restarting the server again



As for the datadir, the command update db locate mysql works on the Mac
and gives me info about a whole set of files in
/usr/local/mysql-5.1.73-osx10.6-x86_64. That's where I thought it was, and
I deleted a previous installation because I had moved the data I needed to
another machine.

I'm not a very experienced programmer and have trouble wrestling with the
command line. But I think I did my due diligence and didn't find any open
doors.


well, you have a bad mix

* missing knowledge
* a blackbox with a installer
* refusing to read more than the begin of docs


On 7/31/15 3:36 AM, Reindl Harald h.rei...@thelounge.net wrote:


first: don't reply off-list, a answer on a mailing-list is no invitation
for private support!

Am 31.07.2015 um 02:34 schrieb Martin Mueller:

I read that section but was stopped in my tracks by

   Log on to your system as the Unix user that the MySQL server runs as
(for example, mysql)

Because I have no password for ANY thing.


read the f**ng
https://dev.mysql.com/doc/refman5.0/en/resetting-permissions.html
https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html -
unbelievebale that users these days need anything ready chewed and are
too lazy to click on a link and read more than 5 lines

Resetting the Root Password: Generic Instructions
Stop the MySQL server if necessary, then restart it with the
--skip-grant-tables option


I used the uninstall routine recommended by Rob Allen, in which you
remove
the directories /usr/local/mysql as well as /usr/local/mysql* and a lot
of
other library and etc files. So there is no trace of the old system on
my
machine. How come a routine installation of mysql then locks up the
application.


the datadir is *not* removed by any sane installer, dunno where it lives
on Apple machines since i banned them 5 years ago for good reasons

on a non-OSX i would just type updatedb; locate mysql als root


On 7/30/15, 19:22, Reindl Harald h.rei...@thelounge.net wrote:


Am 31.07.2015 um 01:41 schrieb Martin Mueller:

I have installed mysql 5.1.73 on an old Mac Pro running OS Lion. I
cannot
run the mysql command because it challenges me for a password. But I
did
not set any password, either for the root, for mysql, or for myself
as a
user.

So the installation has somehow installed passwords about which I know
nothing or there is some error in the installation process.

There is a lot on the Web about resetting a forgotten password. But
the
assumption is always that you can get at the program via some other
password. But in this case every door is shut.

Does anybody recognize this problem? I've uninstalled and re-installed
the
program, but the results are always the same


* install and uninstall *never* removes the datadir
* users and permissions are in the DB mysql
* https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html




signature.asc
Description: OpenPGP digital signature

Re: password problem

[Reindl Harald]

Am 31.07.2015 um 16:23 schrieb Martin Mueller:

Dear Mr Harald,

I've learned some things from your responses and even more from shawn
green's. You might learn a lot from him about patience and courtesy, which
make life on a technical forum a lot easier. You clearly know a lot about
technical stuff, but you're short on patience, and it would help you a lot
to practice a little courtesy and refrain from vulgar language.


well, i am developer and sysadmin, not a politican

my first response pointed again to the docs and quotet that:
 Resetting the Root Password: Generic Instructions
 Stop the MySQL server if necessary, then restart it
 with the --skip-grant-tables option

https://www.google.at/search?q=skip-grant-tables would have flooded you 
with informations


P.S.: on the right side of the docs page is a Section Navigation with 
a link 
https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-generic



On 7/31/15 9:12 AM, Reindl Harald h.rei...@thelounge.net wrote:


Am 31.07.2015 um 15:40 schrieb shawn l.green:

1. Log on to your system as the Unix user that the MySQL server runs as
(for example, mysql).


Everything that executes on a Linux/Unix/Mac machine executes in the
context of some kind of user account (the system login). By default,
mysqld (the database server daemon) is installed to run under the host
machine user account 'mysql'. It can be changed if you want to change it
but that is the default. That is why 'mysql' was listed in the for
example section of that instruction


but this part of the docs is completly bullshit

a) on no sane system the user mysql has a password, hence
no login possible and typically it has also no shell
configured

b) for what reason mysql -u root and you are done with
skip-grant-tables (and skip-grant-tables is the only
relevant point)

why in the world should i need to logon as the user mysqld runs for
connect to mysqld? but anyways, mysql -u mysql would have worked also
as well as mysql -u bullshit because skip-grant-tables does what it
says, you can do anything you like to do




signature.asc
Description: OpenPGP digital signature

Re: password problem

[Reindl Harald]

Am 31.07.2015 um 14:45 schrieb Martin Mueller:

Data directory path mention in cnf is of old mysql.

Make a fresh data directory, configure it in configuration file and execute 
mysqlinstall_db,
I don't understand the sentence about the data directory path mention.  The 
my.cnf file is at /etc/my.cnf . It doesn't have any data directory path 
mention, but neither does the my.cnf file on a laptop, which works. So there 
seems to be nothing wrong with the location or content of the my.cnf file.


your current problem is that you have no clue where your mysql-datadir 
is *because* it's some random default, from the moment on you specify it


a) you know it - good for a million reasons
b) it is empty and you can start from scratch

or you seek the current one and make the folder empty and start with

mysql_install_db initializes the MySQL data directory and creates the 
system tables that it contains, if they do not exist.



On Fri, Jul 31, 2015 at 5:11 AM, Martin Mueller 
martinmuel...@northwestern.edumailto:martinmuel...@northwestern.edu wrote:
I have installed mysql 5.1.73 on an old Mac Pro running OS Lion. I cannot
run the mysql command because it challenges me for a password. But I did
not set any password, either for the root, for mysql, or for myself as a
user.

So the installation has somehow installed passwords about which I know
nothing or there is some error in the installation process.

There is a lot on the Web about resetting a forgotten password. But the
assumption is always that you can get at the program via some other
password. But in this case every door is shut.

Does anybody recognize this problem? I've uninstalled and re-installed the
program, but the results are always the same.


Martin Mueller
Professor emeritus of English and Classics
Northwestern University



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




--
Thanks and Regards:

Nikhil Anand

+91 9650024197



--

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



signature.asc
Description: OpenPGP digital signature

Re: password problem

[Reindl Harald]

Am 31.07.2015 um 15:40 schrieb shawn l.green:

1. Log on to your system as the Unix user that the MySQL server runs as
(for example, mysql).


Everything that executes on a Linux/Unix/Mac machine executes in the
context of some kind of user account (the system login). By default,
mysqld (the database server daemon) is installed to run under the host
machine user account 'mysql'. It can be changed if you want to change it
but that is the default. That is why 'mysql' was listed in the for
example section of that instruction


but this part of the docs is completly bullshit

a) on no sane system the user mysql has a password, hence
   no login possible and typically it has also no shell
   configured

b) for what reason mysql -u root and you are done with
   skip-grant-tables (and skip-grant-tables is the only
   relevant point)

why in the world should i need to logon as the user mysqld runs for 
connect to mysqld? but anyways, mysql -u mysql would have worked also 
as well as mysql -u bullshit because skip-grant-tables does what it 
says, you can do anything you like to do




signature.asc
Description: OpenPGP digital signature

Re: password problem

[Martin Mueller]

Sorry  for the off-list reply. It was an oversight.

That said, the instructions for resetting a forgotten root password have a
section for Windows and a section for Unix. The Unix section begins as
follows:


1. Log on to your system as the Unix user that the MySQL server runs as
(for example, mysql).

But if I do this with the command 'mysql -u mysql I get the answer

Access denied for user 'mysql'@'localhost' (using password: NO)

I can do this as super user or normal, and I can try passwords from
earlier installations, but none of them work. So I am stopped dead in my
tracks, am I not?

As for the datadir, the command update db locate mysql works on the Mac
and gives me info about a whole set of files in
/usr/local/mysql-5.1.73-osx10.6-x86_64. That's where I thought it was, and
I deleted a previous installation because I had moved the data I needed to
another machine. 

I'm not a very experienced programmer and have trouble wrestling with the
command line. But I think I did my due diligence and didn't find any open
doors. 
 




Martin Mueller

Professor emeritus of English and Classics
Northwestern University




On 7/31/15 3:36 AM, Reindl Harald h.rei...@thelounge.net wrote:

first: don't reply off-list, a answer on a mailing-list is no invitation
for private support!

Am 31.07.2015 um 02:34 schrieb Martin Mueller:
 I read that section but was stopped in my tracks by

   Log on to your system as the Unix user that the MySQL server runs as
 (for example, mysql)

 Because I have no password for ANY thing.

read the f**ng 
https://dev.mysql.com/doc/refman5.0/en/resetting-permissions.html
https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html -
unbelievebale that users these days need anything ready chewed and are
too lazy to click on a link and read more than 5 lines

Resetting the Root Password: Generic Instructions
Stop the MySQL server if necessary, then restart it with the
--skip-grant-tables option

 I used the uninstall routine recommended by Rob Allen, in which you
remove
 the directories /usr/local/mysql as well as /usr/local/mysql* and a lot
of
 other library and etc files. So there is no trace of the old system on
my
 machine. How come a routine installation of mysql then locks up the
 application.

the datadir is *not* removed by any sane installer, dunno where it lives
on Apple machines since i banned them 5 years ago for good reasons

on a non-OSX i would just type updatedb; locate mysql als root

 On 7/30/15, 19:22, Reindl Harald h.rei...@thelounge.net wrote:

 Am 31.07.2015 um 01:41 schrieb Martin Mueller:
 I have installed mysql 5.1.73 on an old Mac Pro running OS Lion. I
 cannot
 run the mysql command because it challenges me for a password. But I
did
 not set any password, either for the root, for mysql, or for myself
as a
 user.

 So the installation has somehow installed passwords about which I know
 nothing or there is some error in the installation process.

 There is a lot on the Web about resetting a forgotten password. But
the
 assumption is always that you can get at the program via some other
 password. But in this case every door is shut.

 Does anybody recognize this problem? I've uninstalled and re-installed
 the
 program, but the results are always the same

 * install and uninstall *never* removes the datadir
 * users and permissions are in the DB mysql
 * https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html



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

Re: password problem

[Martin Mueller]

Dear Mr Harald,

I've learned some things from your responses and even more from shawn
green's. You might learn a lot from him about patience and courtesy, which
make life on a technical forum a lot easier. You clearly know a lot about
technical stuff, but you're short on patience, and it would help you a lot
to practice a little courtesy and refrain from vulgar language.


Martin Mueller

Professor emeritus of English and Classics
Northwestern University




On 7/31/15 9:12 AM, Reindl Harald h.rei...@thelounge.net wrote:



Am 31.07.2015 um 15:40 schrieb shawn l.green:
 1. Log on to your system as the Unix user that the MySQL server runs as
 (for example, mysql).

 Everything that executes on a Linux/Unix/Mac machine executes in the
 context of some kind of user account (the system login). By default,
 mysqld (the database server daemon) is installed to run under the host
 machine user account 'mysql'. It can be changed if you want to change it
 but that is the default. That is why 'mysql' was listed in the for
 example section of that instruction

but this part of the docs is completly bullshit

a) on no sane system the user mysql has a password, hence
no login possible and typically it has also no shell
configured

b) for what reason mysql -u root and you are done with
skip-grant-tables (and skip-grant-tables is the only
relevant point)

why in the world should i need to logon as the user mysqld runs for
connect to mysqld? but anyways, mysql -u mysql would have worked also
as well as mysql -u bullshit because skip-grant-tables does what it
says, you can do anything you like to do



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

Re: password problem

[shawn l.green]

On 7/31/2015 8:40 AM, Martin Mueller wrote:

Sorry  for the off-list reply. It was an oversight.

That said, the instructions for resetting a forgotten root password have a
section for Windows and a section for Unix. The Unix section begins as
follows:


1. Log on to your system as the Unix user that the MySQL server runs as
(for example, mysql).



Everything that executes on a Linux/Unix/Mac machine executes in the 
context of some kind of user account (the system login). By default, 
mysqld (the database server daemon) is installed to run under the host 
machine user account 'mysql'. It can be changed if you want to change it 
but that is the default. That is why 'mysql' was listed in the for 
example section of that instruction.




But if I do this with the command 'mysql -u mysql I get the answer



No. That is how you log into mysqld to open a MySQL client session. The 
instruction was to login to your operating system as the user that 
mysqld operates as.  These are fundamentally different accounts at two 
very different levels.





Access denied for user 'mysql'@'localhost' (using password: NO)

I can do this as super user or normal, and I can try passwords from
earlier installations, but none of them work. So I am stopped dead in my
tracks, am I not?



That is because you didn't add this line to the [mysqld] section of your 
configuration file before you started mysqld.


skip-grant-tables

If you had, you would not have needed to use any passwords at all. This 
command (on the system prompt) would be all you need to connect to your 
now completely-unlocked database server (see the third section of 
generic instructions that work on any platform).


mysql



As for the datadir, the command update db locate mysql works on the Mac
and gives me info about a whole set of files in
/usr/local/mysql-5.1.73-osx10.6-x86_64. That's where I thought it was, and
I deleted a previous installation because I had moved the data I needed to
another machine.

I'm not a very experienced programmer and have trouble wrestling with the
command line. But I think I did my due diligence and didn't find any open
doors.



The door is there, you just just need to be able to see it as a door. 
Just a little more experience working on the command line will help.

... remainder snipped ...

--
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: password problem

[Reindl Harald]

first: don't reply off-list, a answer on a mailing-list is no invitation 
for private support!


Am 31.07.2015 um 02:34 schrieb Martin Mueller:

I read that section but was stopped in my tracks by

  Log on to your system as the Unix user that the MySQL server runs as
(for example, mysql)

Because I have no password for ANY thing.


read the f**ng 
https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html - 
unbelievebale that users these days need anything ready chewed and are 
too lazy to click on a link and read more than 5 lines


Resetting the Root Password: Generic Instructions
Stop the MySQL server if necessary, then restart it with the 
--skip-grant-tables option



I used the uninstall routine recommended by Rob Allen, in which you remove
the directories /usr/local/mysql as well as /usr/local/mysql* and a lot of
other library and etc files. So there is no trace of the old system on my
machine. How come a routine installation of mysql then locks up the
application.


the datadir is *not* removed by any sane installer, dunno where it lives 
on Apple machines since i banned them 5 years ago for good reasons


on a non-OSX i would just type updatedb; locate mysql als root


On 7/30/15, 19:22, Reindl Harald h.rei...@thelounge.net wrote:


Am 31.07.2015 um 01:41 schrieb Martin Mueller:

I have installed mysql 5.1.73 on an old Mac Pro running OS Lion. I
cannot
run the mysql command because it challenges me for a password. But I did
not set any password, either for the root, for mysql, or for myself as a
user.

So the installation has somehow installed passwords about which I know
nothing or there is some error in the installation process.

There is a lot on the Web about resetting a forgotten password. But the
assumption is always that you can get at the program via some other
password. But in this case every door is shut.

Does anybody recognize this problem? I've uninstalled and re-installed
the
program, but the results are always the same


* install and uninstall *never* removes the datadir
* users and permissions are in the DB mysql
* https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html




signature.asc
Description: OpenPGP digital signature

Re: password problem

[nikhil anand]

Data directory path mention in cnf is of old mysql.

Make a fresh data directory, configure it in configuration file and execute
mysqlinstall_db,

On Fri, Jul 31, 2015 at 5:11 AM, Martin Mueller 
martinmuel...@northwestern.edu wrote:

 I have installed mysql 5.1.73 on an old Mac Pro running OS Lion. I cannot
 run the mysql command because it challenges me for a password. But I did
 not set any password, either for the root, for mysql, or for myself as a
 user.

 So the installation has somehow installed passwords about which I know
 nothing or there is some error in the installation process.

 There is a lot on the Web about resetting a forgotten password. But the
 assumption is always that you can get at the program via some other
 password. But in this case every door is shut.

 Does anybody recognize this problem? I've uninstalled and re-installed the
 program, but the results are always the same.


 Martin Mueller
 Professor emeritus of English and Classics
 Northwestern University



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




-- 
*Thanks and Regards:*

*Nikhil Anand*

*+91 9650024197*

Re: password problem

[Reindl Harald]

Am 31.07.2015 um 01:41 schrieb Martin Mueller:

I have installed mysql 5.1.73 on an old Mac Pro running OS Lion. I cannot
run the mysql command because it challenges me for a password. But I did
not set any password, either for the root, for mysql, or for myself as a
user.

So the installation has somehow installed passwords about which I know
nothing or there is some error in the installation process.

There is a lot on the Web about resetting a forgotten password. But the
assumption is always that you can get at the program via some other
password. But in this case every door is shut.

Does anybody recognize this problem? I've uninstalled and re-installed the
program, but the results are always the same


* install and uninstall *never* removes the datadir
* users and permissions are in the DB mysql
* https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html



signature.asc
Description: OpenPGP digital signature

password problem

[Martin Mueller]

I have installed mysql 5.1.73 on an old Mac Pro running OS Lion. I cannot
run the mysql command because it challenges me for a password. But I did
not set any password, either for the root, for mysql, or for myself as a
user. 

So the installation has somehow installed passwords about which I know
nothing or there is some error in the installation process.

There is a lot on the Web about resetting a forgotten password. But the
assumption is always that you can get at the program via some other
password. But in this case every door is shut.

Does anybody recognize this problem? I've uninstalled and re-installed the
program, but the results are always the same.


Martin Mueller
Professor emeritus of English and Classics
Northwestern University



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

RE: installation problem of MySQL on OS Lion

[Divesh]

Share error log file

-Original Message-
From: Martin Mueller martinmuel...@northwestern.edu
Sent: ‎29-‎07-‎2015 21:05
To: mysql@lists.mysql.com mysql@lists.mysql.com
Subject: installation problem of MySQL on OS Lion

I installed MySQL on a Mac Pro running OS Lion.

The installation was successful, but starting the mysql server generated the 
following error message:

Martin-Muellers-Mac-Pro:~ martin$ sudo 
/usr/local/mysql/support-files/mysql.server start
Starting MySQL
... ERROR! Manager of pid-file quit without updating file.


I'll be grateful for any help


Martin Mueller
Professor emeritus of English and Classics
Northwestern University

installation problem of MySQL on OS Lion

[Martin Mueller]

I installed MySQL on a Mac Pro running OS Lion.

The installation was successful, but starting the mysql server generated the 
following error message:

Martin-Muellers-Mac-Pro:~ martin$ sudo 
/usr/local/mysql/support-files/mysql.server start
Starting MySQL
... ERROR! Manager of pid-file quit without updating file.


I'll be grateful for any help


Martin Mueller
Professor emeritus of English and Classics
Northwestern University

Re: Replication problem

[william drescher]

On 8/29/2014 7:40 PM, Suresh Kuna wrote:

You can paste the show slave status\G here for us to review  and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )


Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 |  107 |  |  |
+--+--+--+--+
1 row in set (0.00 sec)

Slave
* 1. row ***
   Slave_IO_State: Waiting for master to send event
  Master_Host: 192.168.10.101
  Master_User: repl
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: mysql-bin.001225
  Read_Master_Log_Pos: 107
   Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
 Slave_IO_Running: Yes
Slave_SQL_Running: No
  Replicate_Do_DB:
  Replicate_Ignore_DB:
   Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
   Last_Errno: 1062
   Last_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

 Skip_Counter: 0
  Exec_Master_Log_Pos: 90012430
  Relay_Log_Space: 29727610
  Until_Condition: None
   Until_Log_File:
Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
  Master_SSL_Cert:
Master_SSL_Cipher:
   Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
   Last_SQL_Errno: 1062
   Last_SQL_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

  Replicate_Ignore_Server_Ids:
 Master_Server_Id: 3
1 row in set (0.00 sec)


1 transaction entered:
Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 1837 |  |  |
+--+--+--+--+
1 row in set (0.00 sec)

SLave
*** 1. row ***
   Slave_IO_State: Waiting for master to send event
  Master_Host: 192.168.10.101
  Master_User: repl
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: mysql-bin.001225
  Read_Master_Log_Pos: 1837
   Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
 Slave_IO_Running: Yes
Slave_SQL_Running: No
  Replicate_Do_DB:
  Replicate_Ignore_DB:
   Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
   Last_Errno: 1062
   Last_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

 Skip_Counter: 0
  Exec_Master_Log_Pos: 90012430
  Relay_Log_Space: 29729340
  Until_Condition: None
   Until_Log_File:
Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
  Master_SSL_Cert:
Master_SSL_Cipher:
   Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
   Last_SQL_Errno: 1062
   Last_SQL_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

  Replicate_Ignore_Server_Ids:
 Master_Server_Id: 3
1 row in set (0.00 sec)

Re: Replication problem

[william drescher]

On 8/29/2014 5:11 PM, wagnerbianchi.com wrote:

Hello guys, some points to check here:

1-) Is the master server configured with sync_binlog=1 ?


It was not, I reconfigured and restarted mysql and...


2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading
events from master, is the Exec_Master_Log_Pos incrementing or not?

Not - see prior post

3-) Why are you reconfiguring all the replication just because the link
went down?


AFAIK, I am no reconfiguring,  after the link comes back up, the 
slave does not start replicating - sometimes.  Happens quite 
infrequently.  I intend to be just restoring the database and 
restarting replication




Cheers,


Thanks, I need the cheers.






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

Re: Replication problem

[william drescher]

On 8/29/2014 7:40 PM, Suresh Kuna wrote:

You can paste the show slave status\G here for us to review  and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )

after a more complex transaction;
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 5952 |  |  |
+--+--+--+--+

*** 1. row ***
   Slave_IO_State: Waiting for master to send event
  Master_Host: 192.168.10.101
  Master_User: repl
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: mysql-bin.001225
  Read_Master_Log_Pos: 5952
   Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
 Slave_IO_Running: Yes
Slave_SQL_Running: No
  Replicate_Do_DB:
  Replicate_Ignore_DB:
   Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
   Last_Errno: 1062
   Last_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

 Skip_Counter: 0
  Exec_Master_Log_Pos: 90012430
  Relay_Log_Space: 29733455
  Until_Condition: None
   Until_Log_File:
Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
  Master_SSL_Cert:
Master_SSL_Cipher:
   Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
   Last_SQL_Errno: 1062
   Last_SQL_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

  Replicate_Ignore_Server_Ids:
 Master_Server_Id: 3



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

Re: Replication problem

[Johnny Withers]

There's a duplicate key on the audit table, 18699. Delete it and restart
slave (start slave). Check slave status again,  might be more rows in there
duplicated.

You might want to compare the row to master to ensure it's a duplicate
before deleting from slave.
 On Aug 30, 2014 7:52 AM, william drescher will...@techservsys.com
wrote:

 On 8/29/2014 7:40 PM, Suresh Kuna wrote:

 You can paste the show slave status\G here for us to review  and on
 Master,
 show global variables like 'log-bin%'; show master status ( 3 to 4 times
 continuously )

 after a more complex transaction;
 +--+--+--+--+
 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +--+--+--+--+
 | mysql-bin.001225 | 5952 |  |  |
 +--+--+--+--+

 *** 1. row ***
Slave_IO_State: Waiting for master to send event
   Master_Host: 192.168.10.101
   Master_User: repl
   Master_Port: 3306
 Connect_Retry: 60
   Master_Log_File: mysql-bin.001225
   Read_Master_Log_Pos: 5952
Relay_Log_File: ubuntu2-relay-bin.02
 Relay_Log_Pos: 16426
 Relay_Master_Log_File: mysql-bin.001139
  Slave_IO_Running: Yes
 Slave_SQL_Running: No
   Replicate_Do_DB:
   Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
   Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '18699' for key
 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert
 into audit  (audit_type, subtype, user_Id, ptNum, data) values ('login',
 'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
  Skip_Counter: 0
   Exec_Master_Log_Pos: 90012430
   Relay_Log_Space: 29733455
   Until_Condition: None
Until_Log_File:
 Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
   Master_SSL_Cert:
 Master_SSL_Cipher:
Master_SSL_Key:
 Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699' for key
 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert
 into audit  (audit_type, subtype, user_Id, ptNum, data) values ('login',
 'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
   Replicate_Ignore_Server_Ids:
  Master_Server_Id: 3



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

Re: Replication problem

[william drescher]

On 8/29/2014 7:40 PM, Suresh Kuna wrote:

You can paste the show slave status\G here for us to review  and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously ) global var, below


+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 8919 |  |  |
+--+--+--+--+

*** 1. row ***
   Slave_IO_State: Waiting for master to send event
  Master_Host: 192.168.10.101
  Master_User: repl
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: mysql-bin.001225
  Read_Master_Log_Pos: 8919
   Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
 Slave_IO_Running: Yes
Slave_SQL_Running: No
  Replicate_Do_DB:
  Replicate_Ignore_DB:
   Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
   Last_Errno: 1062
   Last_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

 Skip_Counter: 0
  Exec_Master_Log_Pos: 90012430
  Relay_Log_Space: 29736422
  Until_Condition: None
   Until_Log_File:
Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
  Master_SSL_Cert:
Master_SSL_Cipher:
   Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
   Last_SQL_Errno: 1062
   Last_SQL_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

  Replicate_Ignore_Server_Ids:
 Master_Server_Id: 3


mysql show global variables like 'log-bin%';
Empty set (0.01 sec)





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

Re: Replication problem

[wagnerbianchi.com]

Based on the SHOW SLAVE STATUS output you've sent us, I'd suggest that you
check what the application is doing, understand *why* the application is
violating the PK of the table information_server.audit, repair the possible
application problem and the, reconfigure the replication.

It seems that there's something not really good happening on the
application side since the error 1062 is being threw out by a INSERT sent
to the database ... by the application.

*PS.: looking at the purpose of information_server.audit, I wonder if this
is not a *design* problem!*

1-) Fix the application if you can, investigating the problem the
replication is showing up on SHOW SLAVE STATUS - remember that the
column Last_SQL_Error
is one of SHOW SLAVE STATUS best friends in this context;

2-) After to fix the problem, if design or application, recreate the slave
and then, start replication again;


It's possible to use another things to make the replication to bypass this
kind of problem, but, it's not that cool to have it configured. due to
that, I'd like to omit it at this point.

Let's keep in touch, happy mysql'ing!!

--

*Wagner Bianchi*


2014-08-30 9:54 GMT-03:00 Johnny Withers joh...@pixelated.net:

 There's a duplicate key on the audit table, 18699. Delete it and restart
 slave (start slave). Check slave status again,  might be more rows in there
 duplicated.

 You might want to compare the row to master to ensure it's a duplicate
 before deleting from slave.
  On Aug 30, 2014 7:52 AM, william drescher will...@techservsys.com
 wrote:

  On 8/29/2014 7:40 PM, Suresh Kuna wrote:
 
  You can paste the show slave status\G here for us to review  and on
  Master,
  show global variables like 'log-bin%'; show master status ( 3 to 4 times
  continuously )
 
  after a more complex transaction;
  +--+--+--+--+
  | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  +--+--+--+--+
  | mysql-bin.001225 | 5952 |  |  |
  +--+--+--+--+
 
  *** 1. row ***
 Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
  Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 5952
 Relay_Log_File: ubuntu2-relay-bin.02
  Relay_Log_Pos: 16426
  Relay_Master_Log_File: mysql-bin.001139
   Slave_IO_Running: Yes
  Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
 Last_Errno: 1062
 Last_Error: Error 'Duplicate entry '18699' for key
  'PRIMARY'' on query. Default database: 'Information_server'. Query:
 'insert
  into audit  (audit_type, subtype, user_Id, ptNum, data) values ('login',
  'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
   Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29733455
Until_Condition: None
 Until_Log_File:
  Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
Master_SSL_Cert:
  Master_SSL_Cipher:
 Master_SSL_Key:
  Seconds_Behind_Master: NULL
  Master_SSL_Verify_Server_Cert: No
  Last_IO_Errno: 0
  Last_IO_Error:
 Last_SQL_Errno: 1062
 Last_SQL_Error: Error 'Duplicate entry '18699' for key
  'PRIMARY'' on query. Default database: 'Information_server'. Query:
 'insert
  into audit  (audit_type, subtype, user_Id, ptNum, data) values ('login',
  'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
   Master_Server_Id: 3
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 

Re: Replication problem

[Jose Julian Buda]

On 30/08/14 09:39, william drescher wrote:

On 8/29/2014 7:40 PM, Suresh Kuna wrote:

You can paste the show slave status\G here for us to review and on
Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )


Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 107 | | |
+--+--+--+--+
1 row in set (0.00 sec)

Slave
* 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 107
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query.
Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29727610
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on
query. Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)


1 transaction entered:
Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 1837 | | |
+--+--+--+--+
1 row in set (0.00 sec)

SLave
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 1837
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query.
Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29729340
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on
query. Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)










Well , it seems that you have a 'Duplicate entry ' issue on slave, so 
the Slave_SQL_Running threads is locked :) , there's something wrong 
here with the initial slave status, maybe you have to rebuild the slave 
from scratch(backup or a dump from master), and be sure that you don't 
change anything on slave( phpmyadmin? )


Bye

Julian

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

Re: Replication problem

[william drescher]

On 8/29/2014 7:40 PM, Suresh Kuna wrote:

You can paste the show slave status\G here for us to review  and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )


mysql show global variables like 'log_bin%';
+-+---+
| Variable_name   | Value |
+-+---+
| log_bin | ON|
| log_bin_trust_function_creators | OFF   |
+-+---+




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

Re: Replication problem -solved

[william drescher]

Thanks for pointing out the importance of the last error

I resynced the slave to the master, reset the master position, 
and restarted the slave.


Now all works fine and I am much better equipped next time to 
debug the loss of the link.


When is the Last Error data deleted from the show slave data ?

--bill



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

Re: Replication problem -solved

[Jose Julian Buda]

On 30/08/14 12:56, william drescher wrote:

Thanks for pointing out the importance of the last error

I resynced the slave to the master, reset the master position, and
restarted the slave.

Now all works fine and I am much better equipped next time to debug the
loss of the link.

When is the Last Error data deleted from the show slave data ?

--bill






Just after you resync the slave, last error should be empty.


Bye
Julian

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

Re: Replication problem -solved

[william drescher]

On 8/30/2014 12:53 PM, Jose Julian Buda wrote:



On 30/08/14 12:56, william drescher wrote:

Thanks for pointing out the importance of the last error

I resynced the slave to the master, reset the master position, and
restarted the slave.

Now all works fine and I am much better equipped next time to
debug the
loss of the link.

When is the Last Error data deleted from the show slave data ?

--bill






Just after you resync the slave, last error should be empty.


Bye
Julian



Thanks Julian, it is.


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

Replication problem

[william drescher]

Replication novice

I have a master server at the office and a replication server at 
home.  This setup has been working for a couple of years. 
Occasionally the replication server gets out of sync (usually 
following a internet problem and the vpn going down.)
I just stop the slave, make sure there is nothing going to the 
master (when the office is closed),

copy the database,
transfer the file,
load the backup, and
start the slave and all is well.

This time there was not a communications problem of which I am 
aware.  The slave status said the slave_IO_state was Waiting for 
master to send event but it was not replicating.


I did the usual

now it is not updating the replication database (transactions 
made on the master do not show on the slave - using phpMyAdmin on 
both servers) BUT show master status shows the correct log file 
and the position is incrementing AND show slave status shows the 
same master log file and the same position as the master.  So, 
looking at the status info it seems to be running fine, but the 
transactions do not appear to appear on the slave.


I seek suggestions how to 1) find out what goes wrong when the 
vpn goes down, and 2) (much more important now) how to find out 
whether or not the slave is actually replicating or not.


--bill


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

Re: Replication problem

[Andrew Moore]

Whilst there are a few possibilities, check on the master that your binary
logs are being written to. Another possible reason could be filtering.
On 29 Aug 2014 21:36, william drescher will...@techservsys.com wrote:


 Replication novice

 I have a master server at the office and a replication server at home.
 This setup has been working for a couple of years. Occasionally the
 replication server gets out of sync (usually following a internet problem
 and the vpn going down.)
 I just stop the slave, make sure there is nothing going to the master
 (when the office is closed),
 copy the database,
 transfer the file,
 load the backup, and
 start the slave and all is well.

 This time there was not a communications problem of which I am aware.  The
 slave status said the slave_IO_state was Waiting for master to send event
 but it was not replicating.

 I did the usual

 now it is not updating the replication database (transactions made on the
 master do not show on the slave - using phpMyAdmin on both servers) BUT
 show master status shows the correct log file and the position is
 incrementing AND show slave status shows the same master log file and the
 same position as the master.  So, looking at the status info it seems to be
 running fine, but the transactions do not appear to appear on the slave.

 I seek suggestions how to 1) find out what goes wrong when the vpn goes
 down, and 2) (much more important now) how to find out whether or not the
 slave is actually replicating or not.

 --bill


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

Re: Replication problem

[wagnerbianchi.com]

Hello guys, some points to check here:

1-) Is the master server configured with sync_binlog=1 ?
2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading
events from master, is the Exec_Master_Log_Pos incrementing or not?
3-) Why are you reconfiguring all the replication just because the link
went down?

Cheers,
--
*WB*

2014-08-29 17:46 GMT-03:00 Andrew Moore eroomy...@gmail.com:

 Whilst there are a few possibilities, check on the master that your binary
 logs are being written to. Another possible reason could be filtering.
 On 29 Aug 2014 21:36, william drescher will...@techservsys.com wrote:

 
  Replication novice
 
  I have a master server at the office and a replication server at home.
  This setup has been working for a couple of years. Occasionally the
  replication server gets out of sync (usually following a internet problem
  and the vpn going down.)
  I just stop the slave, make sure there is nothing going to the master
  (when the office is closed),
  copy the database,
  transfer the file,
  load the backup, and
  start the slave and all is well.
 
  This time there was not a communications problem of which I am aware.
 The
  slave status said the slave_IO_state was Waiting for master to send
 event
  but it was not replicating.
 
  I did the usual
 
  now it is not updating the replication database (transactions made on the
  master do not show on the slave - using phpMyAdmin on both servers) BUT
  show master status shows the correct log file and the position is
  incrementing AND show slave status shows the same master log file and the
  same position as the master.  So, looking at the status info it seems to
 be
  running fine, but the transactions do not appear to appear on the slave.
 
  I seek suggestions how to 1) find out what goes wrong when the vpn goes
  down, and 2) (much more important now) how to find out whether or not the
  slave is actually replicating or not.
 
  --bill
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 

Re: Replication problem

[Suresh Kuna]

You can paste the show slave status\G here for us to review  and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )


On Fri, Aug 29, 2014 at 5:11 PM, wagnerbianchi.com m...@wagnerbianchi.com
wrote:

 Hello guys, some points to check here:

 1-) Is the master server configured with sync_binlog=1 ?
 2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading
 events from master, is the Exec_Master_Log_Pos incrementing or not?
 3-) Why are you reconfiguring all the replication just because the link
 went down?

 Cheers,
 --
 *WB*

 2014-08-29 17:46 GMT-03:00 Andrew Moore eroomy...@gmail.com:

  Whilst there are a few possibilities, check on the master that your
 binary
  logs are being written to. Another possible reason could be filtering.
  On 29 Aug 2014 21:36, william drescher will...@techservsys.com
 wrote:
 
  
   Replication novice
  
   I have a master server at the office and a replication server at home.
   This setup has been working for a couple of years. Occasionally the
   replication server gets out of sync (usually following a internet
 problem
   and the vpn going down.)
   I just stop the slave, make sure there is nothing going to the master
   (when the office is closed),
   copy the database,
   transfer the file,
   load the backup, and
   start the slave and all is well.
  
   This time there was not a communications problem of which I am aware.
  The
   slave status said the slave_IO_state was Waiting for master to send
  event
   but it was not replicating.
  
   I did the usual
  
   now it is not updating the replication database (transactions made on
 the
   master do not show on the slave - using phpMyAdmin on both servers) BUT
   show master status shows the correct log file and the position is
   incrementing AND show slave status shows the same master log file and
 the
   same position as the master.  So, looking at the status info it seems
 to
  be
   running fine, but the transactions do not appear to appear on the
 slave.
  
   I seek suggestions how to 1) find out what goes wrong when the vpn goes
   down, and 2) (much more important now) how to find out whether or not
 the
   slave is actually replicating or not.
  
   --bill
  
  
   --
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:http://lists.mysql.com/mysql
  
  
 




-- 
--
Thanks
Suresh Kuna
MySQL Database Consutant  MongoDB DBA
Hadoop Admin

Problem with INSERT INTO and UPDATE queries

[Antonio Fernández Pérez]

​Hi list,

I have some problems with INSERT INTO and UPDATE queries on a big table.
Let me put the code and explain it ...

I have copied the create code of the table. This table has more than
1500 rows.

​Create Table: CREATE TABLE `radacct` (
  `RadAcctId` bigint(21) NOT NULL AUTO_INCREMENT,
  `AcctSessionId` varchar(32) NOT NULL DEFAULT '',
  `AcctUniqueId` varchar(32) NOT NULL DEFAULT '',
  `UserName` varchar(64) NOT NULL DEFAULT '',
  `Realm` varchar(64) DEFAULT '',
  `NASIPAddress` varchar(15) NOT NULL DEFAULT '',
  `NASPortId` varchar(15) DEFAULT NULL,
  `NASPortType` varchar(32) DEFAULT NULL,
  `AcctStartTime` datetime NOT NULL DEFAULT '-00-00 00:00:00',
  `AcctStopTime` datetime NOT NULL DEFAULT '-00-00 00:00:00',
  `AcctSessionTime` int(12) DEFAULT NULL,
  `AcctAuthentic` varchar(32) DEFAULT NULL,
  `ConnectInfo_start` varchar(50) DEFAULT NULL,
  `ConnectInfo_stop` varchar(50) DEFAULT NULL,
  `AcctInputOctets` bigint(20) DEFAULT NULL,
  `AcctOutputOctets` bigint(20) DEFAULT NULL,
  `CalledStationId` varchar(50) NOT NULL DEFAULT '',
  `CallingStationId` varchar(50) NOT NULL DEFAULT '',
  `AcctTerminateCause` varchar(32) NOT NULL DEFAULT '',
  `ServiceType` varchar(32) DEFAULT NULL,
  `FramedProtocol` varchar(32) DEFAULT NULL,
  `FramedIPAddress` varchar(15) NOT NULL DEFAULT '',
  `AcctStartDelay` int(12) DEFAULT NULL,
  `AcctStopDelay` int(12) DEFAULT NULL,
  `XAscendSessionSvrKey` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`RadAcctId`),
  KEY `user_start` (`UserName`,`AcctStartTime`),
  KEY `nasip_starttime` (`NASIPAddress`,`AcctStartTime`),
  KEY `stop_nasip_start` (`AcctStopTime`,`NASIPAddress`,`AcctStartTime`),
  KEY `acctsesid_user_nasip` (`UserName`,`AcctSessionId`,`NASIPAddress`),
  KEY `user_stop` (`UserName`,`AcctStopTime`)
) ENGINE=InnoDB AUTO_INCREMENT=17694651 DEFAULT CHARSET=utf8

###

The next text shows the entries in mysql-slow.log.

###

# Time: 140625  9:37:45
# User@Host: radius[radius] @  [192.168.0.30]
# Thread_id: 94892163  Schema: radius  Last_errno: 0  Killed: 0
# Query_time: 2.327159  Lock_time: 0.86  Rows_sent: 0  Rows_examined:
0  Rows_affected: 1  Rows_read: 0
# Bytes_sent: 19
use radius;
SET timestamp=1403681865;
INSERT INTO radacct (acctsessionid,acctuniqueid,
username,  realm,nasipaddress,
nasportid,  nasporttype,  acctstarttime,
acctstoptime,  acctsessiontime,  acctau
thentic,connectinfo_start,  connectinfo_stop,
acctinputoctets,  acctoutputoctets,  calledstationid,
callingstationid, acctterminatecause,  servicetype,
framedprotocol,   framedipaddress,
   acctstartdelay,   acctstopdelay,xascendsessionsvrkey)
VALUES ('80004ef0', '78d3fc2661258da5',
'zu629LAYUT',  '', '178.136.71.251', '2147503856',
'Wireless-802.11', '2014
-06-25 09:37:26', '-00-00 00:00:00',  '0', '',
'',  '', '0', '0',  'tururu', '00-00-11-11-11-11',
'',  '', '', '178.136.71.1',  '0', '0', '');
# User@Host: radius[radius] @  [192.168.0.31]
# Thread_id: 97905294  Schema: radius  Last_errno: 0  Killed: 0
# Query_time: 2.397604  Lock_time: 0.62  Rows_sent: 0  Rows_examined:
1  Rows_affected: 1  Rows_read: 1
# Bytes_sent: 52
SET timestamp=1403681865;
UPDATE radacct   SET  framedipaddress =
'182.138.214.240',  acctsessiontime = '4199',
acctinputoctets = '0'   32 |
'12327909',  acctoutputo
ctets= '0'  32 |
'294177486'   WHERE acctsessionid = '805063b1'   AND
username= 'fa239DADUX'   AND nasipaddress=
'182.138.214.50';

###

The previous query is converted because I want to use EXPLAIN ...

###

SELECT framedipaddress = '172.21.13.152',  acctsessiontime
= '4199',  acctinputoctets = '0'   32
|'12327909',  acctoutputo
ctets= '0'  32 |
'294177486'
FROM radacct
WHERE acctsessionid = '805063b1'   AND username=
'fa239DADUX'   AND nasipaddress= '192.168.254.10';

++-+-+--+---+--+-+---+--+-+
| id | select_type | table   | type |
possible_keys |
key  | key_len | ref   | rows | Extra   |
++-+-+--+---+--+-+---+--+-+
|  1 | SIMPLE  | radacct | 

Re: access problem for a particular table

[Divesh Kamra]

Hi 
Run mysql_upgrade command at OS shell 

DK Sent from Phone

 On 28-May-2014, at 1:40 pm, Lentes, Bernd 
 bernd.len...@helmholtz-muenchen.de wrote:
 
 Hi,
 
 we just migrated from 5.0 to 5.5. Nearly everything went well. But we can't 
 access one particular table.
 Automysqlback 3.0 says: mysqldump: Got error: 1142: SELECT,LOCK TABL command 
 denied to user 'backup'@'localhost' for table 'cond_instances' when using 
 LOCK TABLES.
 
 root has these rights:
 
 ++
 | Grants for root@localhost   
   
|
 ++
 | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 
 '*948BD740C15428999D549B9632F3C432415E93A4' WITH GRANT OPTION |
 | GRANT SELECT, LOCK TABLES ON `performance_schema`.* TO 'root'@'localhost' 
 WITH GRANT OPTION|
 | GRANT SELECT ON `vectordb%`.* TO 'root'@'localhost' 
   
  |
 | GRANT USAGE ON `performance_schema`.`cond_instances` TO 'root'@'localhost' 
 WITH GRANT OPTION |
 | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
|
 ++
 
 backup has these rights:
 
 ++
 | Grants for backup@localhost 
|
 ++
 | GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'backup'@'localhost' 
 IDENTIFIED BY PASSWORD '*1827DC630AAEB1E997DB2B212CC94EFD9C431555' |
 | GRANT SELECT, LOCK TABLES ON `performance_schema`.* TO 'backup'@'localhost' 
|
 | GRANT SELECT ON `vectordb%`.* TO 'backup'@'localhost'   
|
 ++
 
 If I try to specify (as root) exactly the select and lock tables right to 
 user backup, I get the following error:
 mysql grant select, lock tables on performance_schema.cond_instances to 
 'backup'@'localhost';
 ERROR 1142 (42000): SELECT,GRANT,LOC command denied to user 
 'root'@'localhost' for table 'cond_instances'
 
 What I understood is that the usage right for root on 
 performance_schema.cond_instances means no rights.
 Trying to revoke seems to work:
 mysql revoke usage on performance_schema.cond_instances from 
 'root'@'localhost';
 Query OK, 0 rows affected (0.00 sec)
 
 But the usage right remains, it does not disappear.
 How can I grant these rights to user backup ?
 
 Thanks for any hint.
 
 Bernd
 
 
 --
 Bernd Lentes
 
 Systemadministration
 Institut für Entwicklungsgenetik
 Gebäude 35.34 - Raum 208
 HelmholtzZentrum münchen
 bernd.len...@helmholtz-muenchen.de
 phone: +49 89 3187 1241
 fax:   +49 89 3187 2294
 http://www.helmholtz-muenchen.de/idg
 
 Die Freiheit wird nicht durch weniger Freiheit verteidigt
 
 
 
 Helmholtz Zentrum München
 Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH)
 Ingolstädter Landstr. 1
 85764 Neuherberg
 www.helmholtz-muenchen.de
 Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe
 Geschäftsführer: Prof. Dr. Günther Wess, Dr. Nikolaus Blum, Dr. Alfons Enhsen
 Registergericht: Amtsgericht München HRB 6466
 USt-IdNr: DE 129521671
 
 -- 
 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

access problem for a particular table

[Lentes, Bernd]

Hi,

we just migrated from 5.0 to 5.5. Nearly everything went well. But we can't 
access one particular table.
Automysqlback 3.0 says: mysqldump: Got error: 1142: SELECT,LOCK TABL command 
denied to user 'backup'@'localhost' for table 'cond_instances' when using LOCK 
TABLES.

root has these rights:

++
| Grants for root@localhost 

   |
++
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD 
'*948BD740C15428999D549B9632F3C432415E93A4' WITH GRANT OPTION |
| GRANT SELECT, LOCK TABLES ON `performance_schema`.* TO 'root'@'localhost' 
WITH GRANT OPTION|
| GRANT SELECT ON `vectordb%`.* TO 'root'@'localhost'   

 |
| GRANT USAGE ON `performance_schema`.`cond_instances` TO 'root'@'localhost' 
WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION  
 |
++

backup has these rights:

++
| Grants for backup@localhost   
 |
++
| GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'backup'@'localhost' 
IDENTIFIED BY PASSWORD '*1827DC630AAEB1E997DB2B212CC94EFD9C431555' |
| GRANT SELECT, LOCK TABLES ON `performance_schema`.* TO 'backup'@'localhost'   
 |
| GRANT SELECT ON `vectordb%`.* TO 'backup'@'localhost' 
 |
++

If I try to specify (as root) exactly the select and lock tables right to user 
backup, I get the following error:
mysql grant select, lock tables on performance_schema.cond_instances to 
'backup'@'localhost';
ERROR 1142 (42000): SELECT,GRANT,LOC command denied to user 'root'@'localhost' 
for table 'cond_instances'

What I understood is that the usage right for root on 
performance_schema.cond_instances means no rights.
Trying to revoke seems to work:
mysql revoke usage on performance_schema.cond_instances from 
'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)

But the usage right remains, it does not disappear.
How can I grant these rights to user backup ?

Thanks for any hint.

Bernd


--
Bernd Lentes

Systemadministration
Institut für Entwicklungsgenetik
Gebäude 35.34 - Raum 208
HelmholtzZentrum münchen
bernd.len...@helmholtz-muenchen.de
phone: +49 89 3187 1241
fax:   +49 89 3187 2294
http://www.helmholtz-muenchen.de/idg

Die Freiheit wird nicht durch weniger Freiheit verteidigt



Helmholtz Zentrum München
Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH)
Ingolstädter Landstr. 1
85764 Neuherberg
www.helmholtz-muenchen.de
Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe
Geschäftsführer: Prof. Dr. Günther Wess, Dr. Nikolaus Blum, Dr. Alfons Enhsen
Registergericht: Amtsgericht München HRB 6466
USt-IdNr: DE 129521671

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

Re: access problem for a particular table

[Johan De Meersman]

- Original Message -
 From: Bernd Lentes bernd.len...@helmholtz-muenchen.de
 To: mysql@lists.mysql.com
 Sent: Wednesday, 28 May, 2014 10:10:33 AM
 Subject: access problem for a particular table
 
 we just migrated from 5.0 to 5.5. Nearly everything went well. But we can't
 access one particular table.
 Automysqlback 3.0 says: mysqldump: Got error: 1142: SELECT,LOCK TABL command
 denied to user 'backup'@'localhost' for table 'cond_instances' when using
 LOCK TABLES.

You don't need to backup performance_schema or information_schema. Fix the 
backup tool.


 root has these rights:
 
 [...]

Why does the root user have such specific rights? It suggests that you use it 
for application purposes. Typically you'd set up root or another user as admin 
with all privileges on *.*; and NEVER use that for anything but administrative 
purposes.


 backup has these rights:
 
 [...]

As said above, no need to back up performance_schema or information_schema - 
they're dynamically generated by the MySQL server. You've already granted the 
necessary rights (well, there could be more, but you've probably got what you 
need) on *.*, so no more need for all the specifics. Get rid of them, they only 
confuse people looking at them.


 What I understood is that the usage right for root on
 performance_schema.cond_instances means no rights.
[...]
 But the usage right remains, it does not disappear.
 How can I grant these rights to user backup ?

Well, yes and no. It does mean a user has no rights, but it is really something 
implicit that comes with the very existence of a user. Thus, it's only visible 
when a user has no other rights; and you can't revoke it short of dropping the 
user entirely.



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

Mysql into outfile problem

[Machiel Richards]

Hi guys

  I am hoping that someone might have experienced this before or 
might know why we are getting this.


 We regularly need to run some queries and export the results to a 
csv file.


   However we seem to be experiencing the following issues:

- when we run an explain on the query it shows that it is 
using indexes and the amount of rows it accesses is about 165000 rows 
out of a 90mil+ rows table


- When we run the query however and output to a file, it 
takes about 10-15 minutes to start writing to the file, then once it 
starts, it writes 28Mb to the file, then it waits again for another 
10-15 minutes, and writes another 28Mb's and so it continues until it 
eventually completes.



  for the amount of records and the fact that it uses indexes, 
this should be running quite fast, however we cant seem to figure out 
this behaviour.



 Can anyone perhaps assist me with this as the help woul dbe 
greatly appreciated.



Regards

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

Re: Mysql into outfile problem

[Carsten Pedersen]

If you're doing this from the cmd-line client, try running it using --quick.

Best,

/ Carsten

On 19-02-2014 09:03, Machiel Richards wrote:

Hi guys

   I am hoping that someone might have experienced this before or
might know why we are getting this.

  We regularly need to run some queries and export the results to a
csv file.

However we seem to be experiencing the following issues:

 - when we run an explain on the query it shows that it is
using indexes and the amount of rows it accesses is about 165000 rows
out of a 90mil+ rows table

 - When we run the query however and output to a file, it
takes about 10-15 minutes to start writing to the file, then once it
starts, it writes 28Mb to the file, then it waits again for another
10-15 minutes, and writes another 28Mb's and so it continues until it
eventually completes.


   for the amount of records and the fact that it uses indexes,
this should be running quite fast, however we cant seem to figure out
this behaviour.


  Can anyone perhaps assist me with this as the help woul dbe
greatly appreciated.


Regards



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

Re: Mysql into outfile problem

[Machiel Richards]

Hi,

   the queries are done by connecting to the database using mysql 
workbench or otherwise after ssh to server by using straight mysql 
connection.


regards



On 19/02/2014 12:51, Carsten Pedersen wrote:
If you're doing this from the cmd-line client, try running it using 
--quick. th


Best,

/ Carsten

On 19-02-2014 09:03, Machiel Richards wrote:

Hi guys

   I am hoping that someone might have experienced this before or
might know why we are getting this.

  We regularly need to run some queries and export the results to a
csv file.

However we seem to be experiencing the following issues:

 - when we run an explain on the query it shows that it is
using indexes and the amount of rows it accesses is about 165000 rows
out of a 90mil+ rows table

 - When we run the query however and output to a file, it
takes about 10-15 minutes to start writing to the file, then once it
starts, it writes 28Mb to the file, then it waits again for another
10-15 minutes, and writes another 28Mb's and so it continues until it
eventually completes.


   for the amount of records and the fact that it uses indexes,
this should be running quite fast, however we cant seem to figure out
this behaviour.


  Can anyone perhaps assist me with this as the help woul dbe
greatly appreciated.


Regards




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

Re: Mysql into outfile problem

[Johan De Meersman]

- Original Message -
 From: Machiel Richards machiel.richa...@gmail.com
 Subject: Mysql into outfile problem
 
  - when we run an explain on the query it shows that it is
 using indexes and the amount of rows it accesses is about 165000 rows
 out of a 90mil+ rows table

Not too bad, in and of itself, but explain only gives an estimate, and the 
actual query plan could potentially differ a lot. Have a look at 
https://dev.mysql.com/doc/refman/5.5/en/show-profile.html for some more 
performance debugging tools. If your query ends up in the slowlog (or you can 
turn on the general log for a moment), you can also look at Percona Toolkit 
(pt-query-digest etc) for some more toys.

-- 
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: Mysql into outfile problem

[Machiel Richards]

I have checked now and there is nothing in the Slow logs.




On 19/02/2014 15:33, Johan De Meersman wrote:


- Original Message -

From: Machiel Richards machiel.richa...@gmail.com
Subject: Mysql into outfile problem

  - when we run an explain on the query it shows that it is
using indexes and the amount of rows it accesses is about 165000 rows
out of a 90mil+ rows table

Not too bad, in and of itself, but explain only gives an estimate, and the 
actual query plan could potentially differ a lot. Have a look at 
https://dev.mysql.com/doc/refman/5.5/en/show-profile.html for some more 
performance debugging tools. If your query ends up in the slowlog (or you can 
turn on the general log for a moment), you can also look at Percona Toolkit 
(pt-query-digest etc) for some more toys.




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

Re: Mysql into outfile problem

[Johan De Meersman]

- Original Message -
 From: Machiel Richards machiel.richa...@gmail.com
 Subject: Re: Mysql into outfile problem
 
 I have checked now and there is nothing in the Slow logs.

It may be turned off, then. Depending on your version you can change the 
settings without having to restart the service, have a look at the 
documentation.


-- 
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: Mysql into outfile problem

[Carsten Pedersen]

I don't know what you mean by straight mysql connection. At any rate, 
the idea is to use --quick or otherwise using a connection which uses 
mysql_use_result over mysql_store_result.


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

Best,

/ Carsten

On 19-02-2014 12:02, Machiel Richards wrote:

Hi,

the queries are done by connecting to the database using mysql
workbench or otherwise after ssh to server by using straight mysql
connection.

regards



On 19/02/2014 12:51, Carsten Pedersen wrote:

If you're doing this from the cmd-line client, try running it using
--quick. th

Best,

/ Carsten

On 19-02-2014 09:03, Machiel Richards wrote:

Hi guys

   I am hoping that someone might have experienced this before or
might know why we are getting this.

  We regularly need to run some queries and export the results to a
csv file.

However we seem to be experiencing the following issues:

 - when we run an explain on the query it shows that it is
using indexes and the amount of rows it accesses is about 165000 rows
out of a 90mil+ rows table

 - When we run the query however and output to a file, it
takes about 10-15 minutes to start writing to the file, then once it
starts, it writes 28Mb to the file, then it waits again for another
10-15 minutes, and writes another 28Mb's and so it continues until it
eventually completes.


   for the amount of records and the fact that it uses indexes,
this should be running quite fast, however we cant seem to figure out
this behaviour.


  Can anyone perhaps assist me with this as the help woul dbe
greatly appreciated.


Regards






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

RE: Problem with having

[Rick James]

Still more to this saga

Comment 1:
... HAVING x;
The expression ( x ) is evaluated as a true/false value, based on whether x is 
nonzero (true) or zero (false).  Your 'x' is  MIN(date_time) , which is very 
likely to be nonzero, hence TRUE.  That is, the HAVING does nothing useful.

Comment 2:
This shows
1. a technique
2. how MariaDB optimizes it away, and
3. how you can get MariaDB to still do the group by trick:
https://mariadb.com/kb/en/group-by-trick-has-been-optimized-away/
(I do not know of other MySQL variants that have any trouble with the trick.)

 -Original Message-
 From: Larry Martell [mailto:larry.mart...@gmail.com]
 Sent: Tuesday, September 24, 2013 1:44 PM
 To: shawn green
 Cc: mysql mailing list
 Subject: Re: Problem with having
 
 On Tue, Sep 24, 2013 at 9:05 AM, shawn green
 shawn.l.gr...@oracle.comwrote:
 
  Hello Larry,
 
 
  On 9/23/2013 6:22 PM, Larry Martell wrote:
 
  On Mon, Sep 23, 2013 at 3:15 PM, shawn green
  shawn.l.gr...@oracle.com**
  wrote:
 
   Hi Larry,
 
 
  On 9/23/2013 3:58 PM, Larry Martell wrote:
 
   On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
  narula...@gmail.comwrote:
 
Hi,
 
 
  In your second query, you seem to have MIN(date_time), but you are
  talking about maximum. So your group by query is actually pulling
  the minimum date for this recipe.
 
 
   I pasted the wrong query in. I get the same results regardless of
  if I
  have
  MIN or MAX - I get the id of the max, but the date_time of the min.
 
 
 
On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell 
  larry.mart...@gmail.com
 
  **wrote:
 
 
I want to find the rows from a table that have the max date_time
  for
 
  each
  recipe. I know I've done this before with group by and having,
  but I can't seem to get it to work now. I get the correct row id,
  but not the correct date_time. I'm sure I'm missing something
  simple.
 
  For purposes of showing an example, I'll use one recipe, 19166.
 
 
  For that recipe here's the row I would want:
 
  mysql select id, MAX(date_time) from data_cstmeta  where
  mysql recipe_id =
  19166;
  +-+-----+
  | id  | MAX(date_time)  |
  +-+-----+
 
  | 1151701 | 2013-02-07 18:38:13 |
  +-+-----+
 
  1 row in set (0.01 sec)
 
  I would think this query would give me that - it gives me the
  correct id, but not the correct date_time:
 
  mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta
  mysql where
  recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
  +-+-----+
  | id  | MaxDateTime |
  +-+-----+
 
  | 1151701 | 2010-12-13 16:16:55 |
  +-+-----+
 
  1 row in set (0.01 sec)
 
  How can I fix this?
 
  Thanks!
  -larry
 
 
   You have to do a two-stage match. One stage to find the MAX() of
  a
  value
  for each recipe_id, the other to match that MAX() to one or more
  rows to give you the best ID values.
 
  Here's a subquery method of doing it.  There are many many others
  (google for groupwize maximum)
 
  SELECT a.id, b.MaxDateTime
  FROM data_cstmeta a
  INNER JOIN (
   SELECT MAX(date_time) MaxDateTime
   FROM data_cstmeta
   WHERE recipe_id = 19166
  ) b
 on b.MaxDateTime = a.date_time
  WHERE recipe_id = 19166;
 
 
   Having the recipe_id in the query was just to show an example. I
  really
  want the id's with the max date for each recipe_id:
 
  This is what I changed it to, which works, but is too slow. I need to
  find a more efficient solution:
 
  SELECT d1.id, d1.date_time as MaxDateTime
  FROM data_cstmeta d1
  LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND
  d1.date_time  d2.date_time
  WHERE d2.recipe_id IS NULL
 
 
  As I said, there are many many ways to solve this problem. Here is one
  that is going to perform much better for the generic case than what
  you are doing.
 
  CREATE TEMPORARY TABLE tmpMaxDates (key(recipe_id,maxdatetime)) SELECT
  recipe_id, max(date_time) maxdatetime FROM data_cstmeta GROUP BY
  recipe_id;
 
  SELECT a.id, b.maxdatetime
  FROM data_cstmeta a
  INNER JOIN tmpMaxDates b
on a.recipe_id = b.recipe_id
and a.date_time = b.maxdatetime;
 
  DROP TEMPORARY TABLE tmpMaxDates;
 
 
  Of course, an appropriate multi-column index on data_cstmeta would
  also make your technique much faster than it is today.
 
 
 Thanks much Shawn! This ran in a few  seconds vs. 30 minutes for my
 solution.

Re: Problem with having

[rob.poll...@gmail.com]

I

Sent from my D

- Reply message -
From: Rick James rja...@yahoo-inc.com
To: Larry Martell larry.mart...@gmail.com, shawn green 
shawn.l.gr...@oracle.com
Cc: mysql mailing list mysql@lists.mysql.com
Subject: Problem with having
Date: Thu, Sep 26, 2013 12:11 PM


Still more to this saga

Comment 1:
... HAVING x;
The expression ( x ) is evaluated as a true/false value, based on whether x is 
nonzero (true) or zero (false).  Your 'x' is  MIN(date_time) , which is very 
likely to be nonzero, hence TRUE.  That is, the HAVING does nothing useful.

Comment 2:
This shows
1. a technique
2. how MariaDB optimizes it away, and
3. how you can get MariaDB to still do the group by trick:
https://mariadb.com/kb/en/group-by-trick-has-been-optimized-away/
(I do not know of other MySQL variants that have any trouble with the trick.)

 -Original Message-
 From: Larry Martell [mailto:larry.mart...@gmail.com]
 Sent: Tuesday, September 24, 2013 1:44 PM
 To: shawn green
 Cc: mysql mailing list
 Subject: Re: Problem with having
 
 On Tue, Sep 24, 2013 at 9:05 AM, shawn green
 shawn.l.gr...@oracle.comwrote:
 
  Hello Larry,
 
 
  On 9/23/2013 6:22 PM, Larry Martell wrote:
 
  On Mon, Sep 23, 2013 at 3:15 PM, shawn green
  shawn.l.gr...@oracle.com**
  wrote:
 
   Hi Larry,
 
 
  On 9/23/2013 3:58 PM, Larry Martell wrote:
 
   On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
  narula...@gmail.comwrote:
 
Hi,
 
 
  In your second query, you seem to have MIN(date_time), but you are
  talking about maximum. So your group by query is actually pulling
  the minimum date for this recipe.
 
 
   I pasted the wrong query in. I get the same results regardless of
  if I
  have
  MIN or MAX - I get the id of the max, but the date_time of the min.
 
 
 
On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell 
  larry.mart...@gmail.com
 
  **wrote:
 
 
I want to find the rows from a table that have the max date_time
  for
 
  each
  recipe. I know I've done this before with group by and having,
  but I can't seem to get it to work now. I get the correct row id,
  but not the correct date_time. I'm sure I'm missing something
  simple.
 
  For purposes of showing an example, I'll use one recipe, 19166.
 
 
  For that recipe here's the row I would want:
 
  mysql select id, MAX(date_time) from data_cstmeta  where
  mysql recipe_id =
  19166;
  +-+-----+
  | id  | MAX(date_time)  |
  +-+-----+
 
  | 1151701 | 2013-02-07 18:38:13 |
  +-+-----+
 
  1 row in set (0.01 sec)
 
  I would think this query would give me that - it gives me the
  correct id, but not the correct date_time:
 
  mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta
  mysql where
  recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
  +-+-----+
  | id  | MaxDateTime |
  +-+-----+
 
  | 1151701 | 2010-12-13 16:16:55 |
  +-+-----+
 
  1 row in set (0.01 sec)
 
  How can I fix this?
 
  Thanks!
  -larry
 
 
   You have to do a two-stage match. One stage to find the MAX() of
  a
  value
  for each recipe_id, the other to match that MAX() to one or more
  rows to give you the best ID values.
 
  Here's a subquery method of doing it.  There are many many others
  (google for groupwize maximum)
 
  SELECT a.id, b.MaxDateTime
  FROM data_cstmeta a
  INNER JOIN (
   SELECT MAX(date_time) MaxDateTime
   FROM data_cstmeta
   WHERE recipe_id = 19166
  ) b
 on b.MaxDateTime = a.date_time
  WHERE recipe_id = 19166;
 
 
   Having the recipe_id in the query was just to show an example. I
  really
  want the id's with the max date for each recipe_id:
 
  This is what I changed it to, which works, but is too slow. I need to
  find a more efficient solution:
 
  SELECT d1.id, d1.date_time as MaxDateTime
  FROM data_cstmeta d1
  LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND
  d1.date_time  d2.date_time
  WHERE d2.recipe_id IS NULL
 
 
  As I said, there are many many ways to solve this problem. Here is one
  that is going to perform much better for the generic case than what
  you are doing.
 
  CREATE TEMPORARY TABLE tmpMaxDates (key(recipe_id,maxdatetime)) SELECT
  recipe_id, max(date_time) maxdatetime FROM data_cstmeta GROUP BY
  recipe_id;
 
  SELECT a.id, b.maxdatetime
  FROM data_cstmeta a
  INNER JOIN tmpMaxDates b
on a.recipe_id = b.recipe_id
and a.date_time = b.maxdatetime;
 
  DROP TEMPORARY TABLE tmpMaxDates;
 
 
  Of course, an appropriate multi-column index on data_cstmeta would
  also make your technique much faster than it is today.
 
 
 Thanks much Shawn! This ran in a few  seconds vs. 30 minutes for my
 solution.

Re: Problem with having

[shawn green]

Hello Larry,

On 9/23/2013 6:22 PM, Larry Martell wrote:

On Mon, Sep 23, 2013 at 3:15 PM, shawn green shawn.l.gr...@oracle.comwrote:


Hi Larry,


On 9/23/2013 3:58 PM, Larry Martell wrote:


On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
narula...@gmail.comwrote:

  Hi,


In your second query, you seem to have MIN(date_time), but you are
talking about maximum. So your group by query is actually pulling the
minimum date for this recipe.



I pasted the wrong query in. I get the same results regardless of if I
have
MIN or MAX - I get the id of the max, but the date_time of the min.



  On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.com

**wrote:

  I want to find the rows from a table that have the max date_time for

each
recipe. I know I've done this before with group by and having, but I
can't
seem to get it to work now. I get the correct row id, but not the
correct
date_time. I'm sure I'm missing something simple.

For purposes of showing an example, I'll use one recipe, 19166.


For that recipe here's the row I would want:

mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
19166;
+-+---**--+
| id  | MAX(date_time)  |
+-+---**--+
| 1151701 | 2013-02-07 18:38:13 |
+-+---**--+
1 row in set (0.01 sec)

I would think this query would give me that - it gives me the correct
id,
but not the correct date_time:

mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
+-+---**--+
| id  | MaxDateTime |
+-+---**--+
| 1151701 | 2010-12-13 16:16:55 |
+-+---**--+
1 row in set (0.01 sec)

How can I fix this?

Thanks!
-larry



You have to do a two-stage match. One stage to find the MAX() of a value
for each recipe_id, the other to match that MAX() to one or more rows to
give you the best ID values.

Here's a subquery method of doing it.  There are many many others (google
for groupwize maximum)

SELECT a.id, b.MaxDateTime
FROM data_cstmeta a
INNER JOIN (
 SELECT MAX(date_time) MaxDateTime
 FROM data_cstmeta
 WHERE recipe_id = 19166
) b
   on b.MaxDateTime = a.date_time
WHERE recipe_id = 19166;



Having the recipe_id in the query was just to show an example. I really
want the id's with the max date for each recipe_id:

This is what I changed it to, which works, but is too slow. I need to find
a more efficient solution:

SELECT d1.id, d1.date_time as MaxDateTime
FROM data_cstmeta d1
LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND
d1.date_time  d2.date_time
WHERE d2.recipe_id IS NULL



As I said, there are many many ways to solve this problem. Here is one 
that is going to perform much better for the generic case than what you 
are doing.


CREATE TEMPORARY TABLE tmpMaxDates (key(recipe_id,maxdatetime))
SELECT recipe_id, max(date_time) maxdatetime
FROM data_cstmeta
GROUP BY recipe_id;

SELECT a.id, b.maxdatetime
FROM data_cstmeta a
INNER JOIN tmpMaxDates b
  on a.recipe_id = b.recipe_id
  and a.date_time = b.maxdatetime;

DROP TEMPORARY TABLE tmpMaxDates;


Of course, an appropriate multi-column index on data_cstmeta would also 
make your technique much faster than it is today.


Yours,
--
Shawn Green
MySQL 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: Problem with having

[Larry Martell]

On Tue, Sep 24, 2013 at 9:05 AM, shawn green shawn.l.gr...@oracle.comwrote:

 Hello Larry,


 On 9/23/2013 6:22 PM, Larry Martell wrote:

 On Mon, Sep 23, 2013 at 3:15 PM, shawn green shawn.l.gr...@oracle.com**
 wrote:

  Hi Larry,


 On 9/23/2013 3:58 PM, Larry Martell wrote:

  On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
 narula...@gmail.comwrote:

   Hi,


 In your second query, you seem to have MIN(date_time), but you are
 talking about maximum. So your group by query is actually pulling the
 minimum date for this recipe.


  I pasted the wrong query in. I get the same results regardless of if I
 have
 MIN or MAX - I get the id of the max, but the date_time of the min.



   On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell 
 larry.mart...@gmail.com

 **wrote:


   I want to find the rows from a table that have the max date_time for

 each
 recipe. I know I've done this before with group by and having, but I
 can't
 seem to get it to work now. I get the correct row id, but not the
 correct
 date_time. I'm sure I'm missing something simple.

 For purposes of showing an example, I'll use one recipe, 19166.


 For that recipe here's the row I would want:

 mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
 19166;
 +-+-----+
 | id  | MAX(date_time)  |
 +-+-----+

 | 1151701 | 2013-02-07 18:38:13 |
 +-+-----+

 1 row in set (0.01 sec)

 I would think this query would give me that - it gives me the correct
 id,
 but not the correct date_time:

 mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
 recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
 +-+-----+
 | id  | MaxDateTime |
 +-+-----+

 | 1151701 | 2010-12-13 16:16:55 |
 +-+-----+

 1 row in set (0.01 sec)

 How can I fix this?

 Thanks!
 -larry


  You have to do a two-stage match. One stage to find the MAX() of a
 value
 for each recipe_id, the other to match that MAX() to one or more rows to
 give you the best ID values.

 Here's a subquery method of doing it.  There are many many others (google
 for groupwize maximum)

 SELECT a.id, b.MaxDateTime
 FROM data_cstmeta a
 INNER JOIN (
  SELECT MAX(date_time) MaxDateTime
  FROM data_cstmeta
  WHERE recipe_id = 19166
 ) b
on b.MaxDateTime = a.date_time
 WHERE recipe_id = 19166;


  Having the recipe_id in the query was just to show an example. I really
 want the id's with the max date for each recipe_id:

 This is what I changed it to, which works, but is too slow. I need to find
 a more efficient solution:

 SELECT d1.id, d1.date_time as MaxDateTime
 FROM data_cstmeta d1
 LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND
 d1.date_time  d2.date_time
 WHERE d2.recipe_id IS NULL


 As I said, there are many many ways to solve this problem. Here is one
 that is going to perform much better for the generic case than what you are
 doing.

 CREATE TEMPORARY TABLE tmpMaxDates (key(recipe_id,maxdatetime))
 SELECT recipe_id, max(date_time) maxdatetime
 FROM data_cstmeta
 GROUP BY recipe_id;

 SELECT a.id, b.maxdatetime
 FROM data_cstmeta a
 INNER JOIN tmpMaxDates b
   on a.recipe_id = b.recipe_id
   and a.date_time = b.maxdatetime;

 DROP TEMPORARY TABLE tmpMaxDates;


 Of course, an appropriate multi-column index on data_cstmeta would also
 make your technique much faster than it is today.


Thanks much Shawn! This ran in a few  seconds vs. 30 minutes for my
solution.

Re: Problem with having

[Sukhjinder K. Narula]

Hi,

In your second query, you seem to have MIN(date_time), but you are talking
about maximum. So your group by query is actually pulling the minimum date
for this recipe.

Regards.

On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.comwrote:

 I want to find the rows from a table that have the max date_time for each
 recipe. I know I've done this before with group by and having, but I can't
 seem to get it to work now. I get the correct row id, but not the correct
 date_time. I'm sure I'm missing something simple.

 For purposes of showing an example, I'll use one recipe, 19166.


 For that recipe here's the row I would want:

 mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
 19166;
 +-+-+
 | id  | MAX(date_time)  |
 +-+-+
 | 1151701 | 2013-02-07 18:38:13 |
 +-+-+
 1 row in set (0.01 sec)

 I would think this query would give me that - it gives me the correct id,
 but not the correct date_time:

 mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
 recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
 +-+-+
 | id  | MaxDateTime |
 +-+-+
 | 1151701 | 2010-12-13 16:16:55 |
 +-+-+
 1 row in set (0.01 sec)

 How can I fix this?

 Thanks!
 -larry

Problem with having

[Larry Martell]

I want to find the rows from a table that have the max date_time for each
recipe. I know I've done this before with group by and having, but I can't
seem to get it to work now. I get the correct row id, but not the correct
date_time. I'm sure I'm missing something simple.

For purposes of showing an example, I'll use one recipe, 19166.


For that recipe here's the row I would want:

mysql select id, MAX(date_time) from data_cstmeta  where recipe_id = 19166;
+-+-+
| id  | MAX(date_time)  |
+-+-+
| 1151701 | 2013-02-07 18:38:13 |
+-+-+
1 row in set (0.01 sec)

I would think this query would give me that - it gives me the correct id,
but not the correct date_time:

mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
+-+-+
| id  | MaxDateTime |
+-+-+
| 1151701 | 2010-12-13 16:16:55 |
+-+-+
1 row in set (0.01 sec)

How can I fix this?

Thanks!
-larry

Re: Problem with having

[Larry Martell]

On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
narula...@gmail.comwrote:

 Hi,

 In your second query, you seem to have MIN(date_time), but you are
 talking about maximum. So your group by query is actually pulling the
 minimum date for this recipe.


I pasted the wrong query in. I get the same results regardless of if I have
MIN or MAX - I get the id of the max, but the date_time of the min.



 On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.comwrote:

 I want to find the rows from a table that have the max date_time for each
 recipe. I know I've done this before with group by and having, but I can't
 seem to get it to work now. I get the correct row id, but not the correct
 date_time. I'm sure I'm missing something simple.

 For purposes of showing an example, I'll use one recipe, 19166.


 For that recipe here's the row I would want:

 mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
 19166;
 +-+-+
 | id  | MAX(date_time)  |
 +-+-+
 | 1151701 | 2013-02-07 18:38:13 |
 +-+-+
 1 row in set (0.01 sec)

 I would think this query would give me that - it gives me the correct id,
 but not the correct date_time:

 mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
 recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
 +-+-+
 | id  | MaxDateTime |
 +-+-+
 | 1151701 | 2010-12-13 16:16:55 |
 +-+-+
 1 row in set (0.01 sec)

 How can I fix this?

 Thanks!
 -larry

Re: Problem with having

[shawn green]

Hi Larry,

On 9/23/2013 3:58 PM, Larry Martell wrote:

On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
narula...@gmail.comwrote:


Hi,

In your second query, you seem to have MIN(date_time), but you are
talking about maximum. So your group by query is actually pulling the
minimum date for this recipe.



I pasted the wrong query in. I get the same results regardless of if I have
MIN or MAX - I get the id of the max, but the date_time of the min.




On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.comwrote:


I want to find the rows from a table that have the max date_time for each
recipe. I know I've done this before with group by and having, but I can't
seem to get it to work now. I get the correct row id, but not the correct
date_time. I'm sure I'm missing something simple.

For purposes of showing an example, I'll use one recipe, 19166.


For that recipe here's the row I would want:

mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
19166;
+-+-+
| id  | MAX(date_time)  |
+-+-+
| 1151701 | 2013-02-07 18:38:13 |
+-+-+
1 row in set (0.01 sec)

I would think this query would give me that - it gives me the correct id,
but not the correct date_time:

mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
+-+-+
| id  | MaxDateTime |
+-+-+
| 1151701 | 2010-12-13 16:16:55 |
+-+-+
1 row in set (0.01 sec)

How can I fix this?

Thanks!
-larry



You have to do a two-stage match. One stage to find the MAX() of a value 
for each recipe_id, the other to match that MAX() to one or more rows to 
give you the best ID values.


Here's a subquery method of doing it.  There are many many others 
(google for groupwize maximum)


SELECT a.id, b.MaxDateTime
FROM data_cstmeta a
INNER JOIN (
SELECT MAX(date_time) MaxDateTime
FROM data_cstmeta
WHERE recipe_id = 19166
) b
  on b.MaxDateTime = a.date_time
WHERE recipe_id = 19166;

Yours,
--
Shawn Green
MySQL 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: Problem with having

[Ananda Kumar]

select recipe_id,max(maxdatetime) from data_csmeta group by recipe_id
having recipe_id=19166;


On Mon, Sep 23, 2013 at 4:15 PM, shawn green shawn.l.gr...@oracle.comwrote:

 Hi Larry,


 On 9/23/2013 3:58 PM, Larry Martell wrote:

 On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
 narula...@gmail.comwrote:

  Hi,

 In your second query, you seem to have MIN(date_time), but you are
 talking about maximum. So your group by query is actually pulling the
 minimum date for this recipe.


 I pasted the wrong query in. I get the same results regardless of if I
 have
 MIN or MAX - I get the id of the max, but the date_time of the min.



  On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.com
 **wrote:

  I want to find the rows from a table that have the max date_time for
 each
 recipe. I know I've done this before with group by and having, but I
 can't
 seem to get it to work now. I get the correct row id, but not the
 correct
 date_time. I'm sure I'm missing something simple.

 For purposes of showing an example, I'll use one recipe, 19166.


 For that recipe here's the row I would want:

 mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
 19166;
 +-+---**--+
 | id  | MAX(date_time)  |
 +-+---**--+
 | 1151701 | 2013-02-07 18:38:13 |
 +-+---**--+
 1 row in set (0.01 sec)

 I would think this query would give me that - it gives me the correct
 id,
 but not the correct date_time:

 mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
 recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
 +-+---**--+
 | id  | MaxDateTime |
 +-+---**--+
 | 1151701 | 2010-12-13 16:16:55 |
 +-+---**--+
 1 row in set (0.01 sec)

 How can I fix this?

 Thanks!
 -larry


 You have to do a two-stage match. One stage to find the MAX() of a value
 for each recipe_id, the other to match that MAX() to one or more rows to
 give you the best ID values.

 Here's a subquery method of doing it.  There are many many others (google
 for groupwize maximum)

 SELECT a.id, b.MaxDateTime
 FROM data_cstmeta a
 INNER JOIN (
 SELECT MAX(date_time) MaxDateTime
 FROM data_cstmeta
 WHERE recipe_id = 19166
 ) b
   on b.MaxDateTime = a.date_time
 WHERE recipe_id = 19166;

 Yours,
 --
 Shawn Green
 MySQL 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: Problem with having

[Larry Martell]

On Mon, Sep 23, 2013 at 3:15 PM, shawn green shawn.l.gr...@oracle.comwrote:

 Hi Larry,


 On 9/23/2013 3:58 PM, Larry Martell wrote:

 On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
 narula...@gmail.comwrote:

  Hi,

 In your second query, you seem to have MIN(date_time), but you are
 talking about maximum. So your group by query is actually pulling the
 minimum date for this recipe.


 I pasted the wrong query in. I get the same results regardless of if I
 have
 MIN or MAX - I get the id of the max, but the date_time of the min.



  On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.com
 **wrote:

  I want to find the rows from a table that have the max date_time for
 each
 recipe. I know I've done this before with group by and having, but I
 can't
 seem to get it to work now. I get the correct row id, but not the
 correct
 date_time. I'm sure I'm missing something simple.

 For purposes of showing an example, I'll use one recipe, 19166.


 For that recipe here's the row I would want:

 mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
 19166;
 +-+---**--+
 | id  | MAX(date_time)  |
 +-+---**--+
 | 1151701 | 2013-02-07 18:38:13 |
 +-+---**--+
 1 row in set (0.01 sec)

 I would think this query would give me that - it gives me the correct
 id,
 but not the correct date_time:

 mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
 recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
 +-+---**--+
 | id  | MaxDateTime |
 +-+---**--+
 | 1151701 | 2010-12-13 16:16:55 |
 +-+---**--+
 1 row in set (0.01 sec)

 How can I fix this?

 Thanks!
 -larry


 You have to do a two-stage match. One stage to find the MAX() of a value
 for each recipe_id, the other to match that MAX() to one or more rows to
 give you the best ID values.

 Here's a subquery method of doing it.  There are many many others (google
 for groupwize maximum)

 SELECT a.id, b.MaxDateTime
 FROM data_cstmeta a
 INNER JOIN (
 SELECT MAX(date_time) MaxDateTime
 FROM data_cstmeta
 WHERE recipe_id = 19166
 ) b
   on b.MaxDateTime = a.date_time
 WHERE recipe_id = 19166;


Having the recipe_id in the query was just to show an example. I really
want the id's with the max date for each recipe_id:

This is what I changed it to, which works, but is too slow. I need to find
a more efficient solution:

SELECT d1.id, d1.date_time as MaxDateTime
   FROM data_cstmeta d1
   LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND
d1.date_time  d2.date_time
   WHERE d2.recipe_id IS NULL

Re: Problem with having

[Larry Martell]

On Mon, Sep 23, 2013 at 2:17 PM, Sukhjinder K. Narula
narula...@gmail.comwrote:

 Hi,

 I see that. So the query seems to be picking the first entry out of the
 after grouping by a field and displaying it. And it seems to make sense
 since Having clause seems incomplete. I believe we need to complete the
 condition by HAVING MIN(date_time) ,  or = something.


After reading this, I see what the problem is:

http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

Then I read this:

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

and changed it to this:

SELECT d1.id, d1.date_time as MaxDateTime
   FROM data_cstmeta d1
   LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND
d1.date_time  d2.date_time
   WHERE d2.recipe_id IS NULL

Which works, but is painfully slow. For a table with 200k rows it's been
running for 25 minutes and isn't done yet. That will be unacceptable to my
users.


 On Mon, Sep 23, 2013 at 3:58 PM, Larry Martell larry.mart...@gmail.comwrote:

 On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula 
 narula...@gmail.com wrote:

 Hi,

 In your second query, you seem to have MIN(date_time), but you are
 talking about maximum. So your group by query is actually pulling the
 minimum date for this recipe.


 I pasted the wrong query in. I get the same results regardless of if I
 have MIN or MAX - I get the id of the max, but the date_time of the min.



 On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell 
 larry.mart...@gmail.comwrote:

 I want to find the rows from a table that have the max date_time for
 each
 recipe. I know I've done this before with group by and having, but I
 can't
 seem to get it to work now. I get the correct row id, but not the
 correct
 date_time. I'm sure I'm missing something simple.

 For purposes of showing an example, I'll use one recipe, 19166.


 For that recipe here's the row I would want:

 mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
 19166;
 +-+-+
 | id  | MAX(date_time)  |
 +-+-+
 | 1151701 | 2013-02-07 18:38:13 |
 +-+-+
 1 row in set (0.01 sec)

 I would think this query would give me that - it gives me the correct
 id,
 but not the correct date_time:

 mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
 recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
 +-+-+
 | id  | MaxDateTime |
 +-+-+
 | 1151701 | 2010-12-13 16:16:55 |
 +-+-+
 1 row in set (0.01 sec)

 How can I fix this?

 Thanks!
 -larry

restore problem

[Trianon33]

Hello all,As this is my first post to this list (though reading for some time yet), I maybe not complete in asking my question. Apologies for that.On the other hand, this is not a prio 1 problem, so if it takes some mailing, that's ok.I have a website on my private webserver, which consist of some kind of a LAMP-server on a raspberry. Previously it ran on a laptop with Ubuntu and the same kind of LAMP-server.I'm trying to restore the SQL-backup from the old machine to restore with the raspberry. While finding the file and starting the restore is no problem, I get the following message and a full stop:SELECTMAX(version)FROM`phpmyadmin`.`pma_tracking`WHERE`db_name`='bbz'AND`table_name`='wp_links VALUES'ANDFIND_IN_SET('INSERT',tracking)0MySQL retourneerde:#1100 - Table 'pma_tracking' was not locked with LOCK TABLESSince I do a full restore I'm processing the younameit.SQL fileI'm bad in interpreting the error messag, don't understand what it exactly mens and what I can do to cure this problem.Anyone a suggestion?Thanks in advance, BR

Re: restore problem

[Trianon33]

Hello,

Thanks for your quick reply.

I use WEBMIN and within the Webmin modules you can make simply a SQL backup, 
which is in fact a flat file consisting of MYSQL commands.

Explains this enough?

Thanks, BR



Op 8 sep. 2013, om 21:02 heeft Luis H. Forchesatto 
luisforchesa...@gmail.com het volgende geschreven:

 How did you backed up the MySQL?
 
 Att.
 Luis H. Forchesatto
 
 Em 08/09/2013 16:00, Trianon33 triano...@gmail.com escreveu:
 Hello all,
 
 As this is my first post to this list (though reading for some time yet), I 
 maybe not complete in asking my question. Apologies for that.
 
 On the other hand, this is not a prio 1 problem, so if it takes some mailing, 
 that's ok.
 
 I have a website on my private webserver, which consist of some kind of a 
 LAMP-server on a raspberry. Previously it ran on a laptop with Ubuntu and the 
 same kind of LAMP-server.
 
 I'm trying to restore the SQL-backup from the old machine to restore with the 
 raspberry. While finding the file and starting the restore is no problem, I 
 get the following message and a full stop:
 
 SELECT MAX( version ) 
 FROM `phpmyadmin`.`pma_tracking` 
 WHERE `db_name` = 'bbz'
 AND `table_name` = 'wp_links VALUES'
 AND FIND_IN_SET( 'INSERT', tracking ) 0
 
 MySQL retourneerde: b_help.png
 
 #1100 - Table 'pma_tracking' was not locked with LOCK TABLES
 
 Since I do a full restore I'm processing the younameit.SQL file
 
 I'm bad in interpreting the error messag, don't understand what it exactly 
 mens and what I can do to cure this problem.
 
 Anyone a suggestion?
 
 Thanks in advance, BR

Re: restore problem

[Michael Dykman]

I would suggest that you test your backup file on another full-featured
server to determine that it is a valid first.  I have done a little work
with the raspberry pi and I doubt that the mysql distribution for that
platform comes with all the features your server-class ubuntu does, so it
is quite possible that you backup file is trying to take advantage of some
facilities available on the source host that are not available on your
lightweight target.



On Sun, Sep 8, 2013 at 3:05 PM, Trianon33 triano...@gmail.com wrote:

 Hello,

 Thanks for your quick reply.

 I use WEBMIN and within the Webmin modules you can make simply a SQL
 backup, which is in fact a flat file consisting of MYSQL commands.

 Explains this enough?

 Thanks, BR



 Op 8 sep. 2013, om 21:02 heeft Luis H. Forchesatto 
 luisforchesa...@gmail.com het volgende geschreven:

  How did you backed up the MySQL?
 
  Att.
  Luis H. Forchesatto
 
  Em 08/09/2013 16:00, Trianon33 triano...@gmail.com escreveu:
  Hello all,
 
  As this is my first post to this list (though reading for some time
 yet), I maybe not complete in asking my question. Apologies for that.
 
  On the other hand, this is not a prio 1 problem, so if it takes some
 mailing, that's ok.
 
  I have a website on my private webserver, which consist of some kind of
 a LAMP-server on a raspberry. Previously it ran on a laptop with Ubuntu and
 the same kind of LAMP-server.
 
  I'm trying to restore the SQL-backup from the old machine to restore
 with the raspberry. While finding the file and starting the restore is no
 problem, I get the following message and a full stop:
 
  SELECT MAX( version )
  FROM `phpmyadmin`.`pma_tracking`
  WHERE `db_name` = 'bbz'
  AND `table_name` = 'wp_links VALUES'
  AND FIND_IN_SET( 'INSERT', tracking ) 0
 
  MySQL retourneerde: b_help.png
 
  #1100 - Table 'pma_tracking' was not locked with LOCK TABLES
 
  Since I do a full restore I'm processing the younameit.SQL file
 
  I'm bad in interpreting the error messag, don't understand what it
 exactly mens and what I can do to cure this problem.
 
  Anyone a suggestion?
 
  Thanks in advance, BR




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

 May the Source be with you.

Re: restore problem

[Trianon33]

H, this remark makes sense, thanks for reminding that.

Since I have a backup in SQl format (it is readable statements), I was thinking 
on splitting the backup file in several separate restores and execute them 
manually in consecutive order.

Hmmm, the idea attracts me.

Wait for some results. BR.


Op 8 sep. 2013, om 23:16 heeft Michael Dykman mdyk...@gmail.com het volgende 
geschreven:

 I would suggest that you test your backup file on another full-featured
 server to determine that it is a valid first.  I have done a little work
 with the raspberry pi and I doubt that the mysql distribution for that
 platform comes with all the features your server-class ubuntu does, so it
 is quite possible that you backup file is trying to take advantage of some
 facilities available on the source host that are not available on your
 lightweight target.
 
 
 
 On Sun, Sep 8, 2013 at 3:05 PM, Trianon33 triano...@gmail.com wrote:
 
 Hello,
 
 Thanks for your quick reply.
 
 I use WEBMIN and within the Webmin modules you can make simply a SQL
 backup, which is in fact a flat file consisting of MYSQL commands.
 
 Explains this enough?
 
 Thanks, BR
 
 
 
 Op 8 sep. 2013, om 21:02 heeft Luis H. Forchesatto 
 luisforchesa...@gmail.com het volgende geschreven:
 
 How did you backed up the MySQL?
 
 Att.
 Luis H. Forchesatto
 
 Em 08/09/2013 16:00, Trianon33 triano...@gmail.com escreveu:
 Hello all,
 
 As this is my first post to this list (though reading for some time
 yet), I maybe not complete in asking my question. Apologies for that.
 
 On the other hand, this is not a prio 1 problem, so if it takes some
 mailing, that's ok.
 
 I have a website on my private webserver, which consist of some kind of
 a LAMP-server on a raspberry. Previously it ran on a laptop with Ubuntu and
 the same kind of LAMP-server.
 
 I'm trying to restore the SQL-backup from the old machine to restore
 with the raspberry. While finding the file and starting the restore is no
 problem, I get the following message and a full stop:
 
 SELECT MAX( version )
 FROM `phpmyadmin`.`pma_tracking`
 WHERE `db_name` = 'bbz'
 AND `table_name` = 'wp_links VALUES'
 AND FIND_IN_SET( 'INSERT', tracking ) 0
 
 MySQL retourneerde: b_help.png
 
 #1100 - Table 'pma_tracking' was not locked with LOCK TABLES
 
 Since I do a full restore I'm processing the younameit.SQL file
 
 I'm bad in interpreting the error messag, don't understand what it
 exactly mens and what I can do to cure this problem.
 
 Anyone a suggestion?
 
 Thanks in advance, BR
 
 
 
 
 -- 
 - 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: restore problem

[Andrew Moore]

I would suggest making a physical backup. Shutdown MySQL on source, copy
datadir and start on the the destination server. (observe configuration
differences between the two machines)

*Benefits;* consistent backup of non-transactional files.
*Drawbacks;* downtime required.


On Sun, Sep 8, 2013 at 10:16 PM, Michael Dykman mdyk...@gmail.com wrote:

 I would suggest that you test your backup file on another full-featured
 server to determine that it is a valid first.  I have done a little work
 with the raspberry pi and I doubt that the mysql distribution for that
 platform comes with all the features your server-class ubuntu does, so it
 is quite possible that you backup file is trying to take advantage of some
 facilities available on the source host that are not available on your
 lightweight target.



 On Sun, Sep 8, 2013 at 3:05 PM, Trianon33 triano...@gmail.com wrote:

  Hello,
 
  Thanks for your quick reply.
 
  I use WEBMIN and within the Webmin modules you can make simply a SQL
  backup, which is in fact a flat file consisting of MYSQL commands.
 
  Explains this enough?
 
  Thanks, BR
 
 
 
  Op 8 sep. 2013, om 21:02 heeft Luis H. Forchesatto 
  luisforchesa...@gmail.com het volgende geschreven:
 
   How did you backed up the MySQL?
  
   Att.
   Luis H. Forchesatto
  
   Em 08/09/2013 16:00, Trianon33 triano...@gmail.com escreveu:
   Hello all,
  
   As this is my first post to this list (though reading for some time
  yet), I maybe not complete in asking my question. Apologies for that.
  
   On the other hand, this is not a prio 1 problem, so if it takes some
  mailing, that's ok.
  
   I have a website on my private webserver, which consist of some kind of
  a LAMP-server on a raspberry. Previously it ran on a laptop with Ubuntu
 and
  the same kind of LAMP-server.
  
   I'm trying to restore the SQL-backup from the old machine to restore
  with the raspberry. While finding the file and starting the restore is no
  problem, I get the following message and a full stop:
  
   SELECT MAX( version )
   FROM `phpmyadmin`.`pma_tracking`
   WHERE `db_name` = 'bbz'
   AND `table_name` = 'wp_links VALUES'
   AND FIND_IN_SET( 'INSERT', tracking ) 0
  
   MySQL retourneerde: b_help.png
  
   #1100 - Table 'pma_tracking' was not locked with LOCK TABLES
  
   Since I do a full restore I'm processing the younameit.SQL file
  
   I'm bad in interpreting the error messag, don't understand what it
  exactly mens and what I can do to cure this problem.
  
   Anyone a suggestion?
  
   Thanks in advance, BR
 
 


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

  May the Source be with you.

Re: InnoDB problem.

[Johan De Meersman]

What's the MySQL error log have to say? 

- Original Message -

 From: Luis H. Forchesatto luisforchesa...@gmail.com
 To: Johan De Meersman vegiv...@tuxera.be
 Sent: Tuesday, 23 July, 2013 3:39:55 PM
 Subject: Re: InnoDB problem.

 Yep, I do backup of /home/mysql/ib* files too :D

 What it occurs is that even with ibdata1, ib_logfile0 and ib_logfile1
 in it's due place, MySQL (provided by xampp) shows me the following
 messages, when trying to open InnoDB tables:

 SHOW FULL FIELDS FROM `my_innodb_table` ;

 #1286 - Unknown table engine 'InnoDB'

 skin-innodb is commented but either way InnoDB engine are not shown
 when I execute show engines command.

 2013/7/22 Johan De Meersman  vegiv...@tuxera.be 

 --

 Att.

 Luis H. Forchesatto
 http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67

-- 

Unhappiness is discouraged and will be corrected with kitten pictures. 

Re: InnoDB problem.

[Johan De Meersman]

Eek. 

No immediate clue here, but maybe someone else does - so please keep the list 
in CC at all times :-p 

Random question: were the files backed up from a different version? I'd expect 
some kind of warning about that in the logs, really, but you never know. 

- Original Message -

 From: Luis H. Forchesatto luisforchesa...@gmail.com
 To: Johan De Meersman vegiv...@tuxera.be
 Sent: Tuesday, 23 July, 2013 6:34:47 PM
 Subject: Re: InnoDB problem.

 The error log:

 130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error.
 130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE
 ENGINE failed.
 130723 10:04:23 [Note] Event Scheduler: Loaded 0 events
 130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for
 connections.
 Version: '5.1.41' socket: '' port: 3306 Source distribution

 2013/7/23 Johan De Meersman  vegiv...@tuxera.be 

 --

 Att.

 Luis H. Forchesatto
 http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67

-- 

Unhappiness is discouraged and will be corrected with kitten pictures. 

RE: InnoDB problem.

[Rick James]

Did you change innodb_log_file_size?

 -Original Message-
 From: Johan De Meersman [mailto:vegiv...@tuxera.be]
 Sent: Tuesday, July 23, 2013 9:57 AM
 To: Luis H. Forchesatto; mysql list
 Subject: Re: InnoDB problem.
 
 Eek.
 
 No immediate clue here, but maybe someone else does - so please keep the
 list in CC at all times :-p
 
 Random question: were the files backed up from a different version? I'd
 expect some kind of warning about that in the logs, really, but you never
 know.
 
 - Original Message -
 
  From: Luis H. Forchesatto luisforchesa...@gmail.com
  To: Johan De Meersman vegiv...@tuxera.be
  Sent: Tuesday, 23 July, 2013 6:34:47 PM
  Subject: Re: InnoDB problem.
 
  The error log:
 
  130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error.
  130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE
  ENGINE failed.
  130723 10:04:23 [Note] Event Scheduler: Loaded 0 events
  130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for
  connections.
  Version: '5.1.41' socket: '' port: 3306 Source distribution
 
  2013/7/23 Johan De Meersman  vegiv...@tuxera.be 
 
  --
 
  Att.
 
  Luis H. Forchesatto
  http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67
 
 --
 
 Unhappiness is discouraged and will be corrected with kitten pictures.

Re: InnoDB problem.

[Manuel Arostegui]

2013/7/23 Rick James rja...@yahoo-inc.com

 Did you change innodb_log_file_size?


innodb_log_file_size error always appears in the logs...he only posted a
few lines of his log...but I guess (or I want to believe) he's gone through
the whole log before starting the thread :-)




Manuel






  -Original Message-
  From: Johan De Meersman [mailto:vegiv...@tuxera.be]
  Sent: Tuesday, July 23, 2013 9:57 AM
  To: Luis H. Forchesatto; mysql list
  Subject: Re: InnoDB problem.
 
  Eek.
 
  No immediate clue here, but maybe someone else does - so please keep the
  list in CC at all times :-p
 
  Random question: were the files backed up from a different version? I'd
  expect some kind of warning about that in the logs, really, but you never
  know.
 
  - Original Message -
 
   From: Luis H. Forchesatto luisforchesa...@gmail.com
   To: Johan De Meersman vegiv...@tuxera.be
   Sent: Tuesday, 23 July, 2013 6:34:47 PM
   Subject: Re: InnoDB problem.
 
   The error log:
 
   130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error.
   130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE
   ENGINE failed.
   130723 10:04:23 [Note] Event Scheduler: Loaded 0 events
   130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for
   connections.
   Version: '5.1.41' socket: '' port: 3306 Source distribution
 
   2013/7/23 Johan De Meersman  vegiv...@tuxera.be 
 
   --
 
   Att.
 
   Luis H. Forchesatto
   http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67
 
  --
 
  Unhappiness is discouraged and will be corrected with kitten pictures.




-- 
Manuel Aróstegui
Systems Team
tuenti.com

RE: InnoDB problem.

[Rick James]

Either change it back, or delete the log files so that they will be built in 
the new size.  (Backup the entire tree, just in case.)

From: Manuel Arostegui [mailto:man...@tuenti.com]
Sent: Tuesday, July 23, 2013 1:05 PM
To: Rick James
Cc: Johan De Meersman; Luis H. Forchesatto; mysql list
Subject: Re: InnoDB problem.



2013/7/23 Rick James rja...@yahoo-inc.commailto:rja...@yahoo-inc.com
Did you change innodb_log_file_size?

innodb_log_file_size error always appears in the logs...he only posted a few 
lines of his log...but I guess (or I want to believe) he's gone through the 
whole log before starting the thread :-)




Manuel





 -Original Message-
 From: Johan De Meersman [mailto:vegiv...@tuxera.bemailto:vegiv...@tuxera.be]
 Sent: Tuesday, July 23, 2013 9:57 AM
 To: Luis H. Forchesatto; mysql list
 Subject: Re: InnoDB problem.

 Eek.

 No immediate clue here, but maybe someone else does - so please keep the
 list in CC at all times :-p

 Random question: were the files backed up from a different version? I'd
 expect some kind of warning about that in the logs, really, but you never
 know.

 - Original Message -

  From: Luis H. Forchesatto 
  luisforchesa...@gmail.commailto:luisforchesa...@gmail.com
  To: Johan De Meersman vegiv...@tuxera.bemailto:vegiv...@tuxera.be
  Sent: Tuesday, 23 July, 2013 6:34:47 PM
  Subject: Re: InnoDB problem.

  The error log:

  130723 10:04:23 [ERROR] Plugin 'InnoDB' init function returned error.
  130723 10:04:23 [ERROR] Plugin 'InnoDB' registration as a STORAGE
  ENGINE failed.
  130723 10:04:23 [Note] Event Scheduler: Loaded 0 events
  130723 10:04:23 [Note] C:\xampp\mysql\bin\mysqld.exe: ready for
  connections.
  Version: '5.1.41' socket: '' port: 3306 Source distribution

  2013/7/23 Johan De Meersman  vegiv...@tuxera.bemailto:vegiv...@tuxera.be 
  

  --

  Att.

  Luis H. Forchesatto
  http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67

 --

 Unhappiness is discouraged and will be corrected with kitten pictures.



--
Manuel Aróstegui
Systems Team
tuenti.comhttp://tuenti.com

InnoDB problem.

[Luis H. Forchesatto]

Greetings.

I've restored an MySQL backup from our MySQL server into another server.
The backup includes InnoDB tables. After the import, MySQL recognized the
innodb tables fine but when I try to do a check table ir returns that the
table doesn't exists.

Permission and owner of the table files (.frm files) are ok, since it
recognizes MyISAM tables (they have the same permission). Innodb engine is
enabled..

Which can cause the tables to appears as non existent, as far as they do
really exist?

-- 
Att.*
***
Luis H. Forchesatto
http://br.linkedin.com/pub/luis-henrique-forchesatto/73/684/a67

Re: InnoDB problem.

[Johan De Meersman]

- Original Message -
 From: Luis H. Forchesatto luisforchesa...@gmail.com
 Subject: InnoDB problem.
 
 Permission and owner of the table files (.frm files) are ok, since it
 recognizes MyISAM tables (they have the same permission). 

Oops. You should always read the fine manual.

You took file-level backups, yes? Did they include the ibdata1 and similar 
files? Those contain innodb's dictionary - and in default installs also all the 
actual tables. The database/* files only contain the .frm, for innodb.

If I'm right, you haven't got a backup at all. I'm crossing my fingers that I'm 
wrong...


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

Edit MySQL Trigger in Workbench problem

[Neil Tompkins]

Hi,

I've created a Trigger and want to edit it.  Using MySQL Workbench, I can
Alter the table, and click Triggers and select the trigger action I want to
edit (on my local database, MySQL running on same PC)

However, if I try the exact same procedure on a Trigger on a remote
database, I don't see the Triggers under each action.

The only way these can be edited is to drop and create it again.

Thanks
Neil

Fwd: character set problem

[Napster Cao]

Begin forwarded message:

 From: Napster Cao tx...@hotmail.com
 Subject: character set problem
 Date: June 11, 2013 11:04:18 PM GMT+08:00
 To: mysql@lists.mysql.com
 
 Hi Guys,
 
 I installed a new CentOS server (6.4 x86_64), and when I try to log into 
 phpmyadmin, there's an ERROR:
 Can't initialize character set utf-8 (path: /usr/local/mysql/share/charsets/)
 
 BTW: Everytime I logged into system, I got:
 -bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8): No such 
 file or directory
 and I cannot find locale-gen on my system, the latest version of glibc is 
 installed.
 
 [admin@zxue /]# rpm -qa | grep glibc
 glibc-devel-2.12-1.107.el6.x86_64
 glibc-2.12-1.107.el6.x86_64
 glibc-headers-2.12-1.107.el6.x86_64
 glibc-common-2.12-1.107.el6.x86_64
 
 and here's the output of locale command:
 [admin@zxue /]# locale
 LANG=en_US
 LC_CTYPE=en_US.utf-8
 LC_NUMERIC=en_US.utf-8
 LC_TIME=en_US.utf-8
 LC_COLLATE=en_US.utf-8
 LC_MONETARY=en_US.utf-8
 LC_MESSAGES=en_US.utf-8
 LC_PAPER=en_US.utf-8
 LC_NAME=en_US.utf-8
 LC_ADDRESS=en_US.utf-8
 LC_TELEPHONE=en_US.utf-8
 LC_MEASUREMENT=en_US.utf-8
 LC_IDENTIFICATION=en_US.utf-8
 LC_ALL=en_US.utf-8
 
 How to resolve those two problems?(or maybe they are the same problem?)
 
 Thanks in advance!

Re: character set problem

[Reindl Harald]

independent how often you re-post it will not become magically
a MySQL problem if you have messed up your OS environment

Am 12.06.2013 15:27, schrieb Napster Cao:
 BTW: Everytime I logged into system, I got:
 -bash: warning: setlocale: LC_CTYPE: cannot change locale (UTF-8): No such 
 file or directory
 and I cannot find locale-gen on my system, the latest version of glibc is 
 installed



signature.asc
Description: OpenPGP digital signature

Temporary Tables with Triggers Problem

[Neil Tompkins]

Hi,

I've a trigger that writes some data to a temporary table; and at the end
of the trigger writes all the temporary table data in one insert to our
normal Innodb table.

However, for some reason the trigger isn't copying the table from the
temporary table to the Innodb table.  If I write in the trigger the inserts
to the Innodb table, it works fine.

Any ideas why.  I'm running MySQL 5.6.

Thanks
Neil

Re: Temporary Tables with Triggers Problem

[Ananda Kumar]

can you please share the code of the trigger. Any kind of error your getting


On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins neil.tompk...@googlemail.com
 wrote:

 Hi,

 I've a trigger that writes some data to a temporary table; and at the end
 of the trigger writes all the temporary table data in one insert to our
 normal Innodb table.

 However, for some reason the trigger isn't copying the table from the
 temporary table to the Innodb table.  If I write in the trigger the inserts
 to the Innodb table, it works fine.

 Any ideas why.  I'm running MySQL 5.6.

 Thanks
 Neil

Re: Temporary Tables with Triggers Problem

[Neil Tompkins]

This is my Trigger which doesn't seem to work; but doesn't cause a error

DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;

CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
varchar(36),UserId bigint(20),ActionType
enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
varchar(255),FieldName varchar(36),OldValue text,NewValue text);
IF NEW.RoomsToSell  OLD.RoomsToSell THEN
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
END IF;

IF SELECT COUNT(*) FROM tempHotelRateAvailability  0 THEN
INSERT INTO AuditTrail
SELECT tempHotelRateAvailability.* FROM tempHotelRateAvailability;

END IF;

DROP TEMPORARY TABLE tempHotelRateAvailability;

However if I use this call in the Trigger and change a value in the table
it works fine;

INSERT INTO AuditTrail
(AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
LoggedOn)
 VALUES (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2,
NOW());


On Wed, May 29, 2013 at 2:49 PM, Ananda Kumar anan...@gmail.com wrote:

 can you please share the code of the trigger. Any kind of error your
 getting


 On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 Hi,

 I've a trigger that writes some data to a temporary table; and at the end
 of the trigger writes all the temporary table data in one insert to our
 normal Innodb table.

 However, for some reason the trigger isn't copying the table from the
 temporary table to the Innodb table.  If I write in the trigger the
 inserts
 to the Innodb table, it works fine.

 Any ideas why.  I'm running MySQL 5.6.

 Thanks
 Neil

Re: Temporary Tables with Triggers Problem

[Ananda Kumar]

did u check if data is getting inserted into tempHotelRateAvailability


On Wed, May 29, 2013 at 7:21 PM, Neil Tompkins neil.tompk...@googlemail.com
 wrote:

 This is my Trigger which doesn't seem to work; but doesn't cause a error

 DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;

 CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
 varchar(36),UserId bigint(20),ActionType
 enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
 varchar(255),FieldName varchar(36),OldValue text,NewValue text);
 IF NEW.RoomsToSell  OLD.RoomsToSell THEN
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
 CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
 END IF;

 IF SELECT COUNT(*) FROM tempHotelRateAvailability  0 THEN
 INSERT INTO AuditTrail
 SELECT tempHotelRateAvailability.* FROM tempHotelRateAvailability;

 END IF;

 DROP TEMPORARY TABLE tempHotelRateAvailability;

 However if I use this call in the Trigger and change a value in the table
 it works fine;

 INSERT INTO AuditTrail
 (AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
 LoggedOn)
  VALUES (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2,
 NOW());


 On Wed, May 29, 2013 at 2:49 PM, Ananda Kumar anan...@gmail.com wrote:

 can you please share the code of the trigger. Any kind of error your
 getting


 On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 Hi,

 I've a trigger that writes some data to a temporary table; and at the end
 of the trigger writes all the temporary table data in one insert to our
 normal Innodb table.

 However, for some reason the trigger isn't copying the table from the
 temporary table to the Innodb table.  If I write in the trigger the
 inserts
 to the Innodb table, it works fine.

 Any ideas why.  I'm running MySQL 5.6.

 Thanks
 Neil

Re: Temporary Tables with Triggers Problem

[Neil Tompkins]

I took the following lines of code slightly modified and it returned some
data using a normal Query Editor

CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
varchar(36),UserId bigint(20),ActionType varchar(36),TableName
varchar(36),RowKey varchar(255),FieldName varchar(36),OldValue
varchar(255),NewValue varchar(255), LoggedOn TIMESTAMP);
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2, NOW());
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',4,4, NOW());
SELECT * FROM tempHotelRateAvailability;


On Wed, May 29, 2013 at 2:57 PM, Ananda Kumar anan...@gmail.com wrote:

 did u check if data is getting inserted into tempHotelRateAvailability


 On Wed, May 29, 2013 at 7:21 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 This is my Trigger which doesn't seem to work; but doesn't cause a error

  DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;

 CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
 varchar(36),UserId bigint(20),ActionType
 enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
 varchar(255),FieldName varchar(36),OldValue text,NewValue text);
 IF NEW.RoomsToSell  OLD.RoomsToSell THEN
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
 CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
 END IF;

 IF SELECT COUNT(*) FROM tempHotelRateAvailability  0 THEN
 INSERT INTO AuditTrail
 SELECT tempHotelRateAvailability.* FROM
 tempHotelRateAvailability;
 END IF;

 DROP TEMPORARY TABLE tempHotelRateAvailability;

 However if I use this call in the Trigger and change a value in the table
 it works fine;

 INSERT INTO AuditTrail
 (AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
 LoggedOn)
  VALUES (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2,
 NOW());


 On Wed, May 29, 2013 at 2:49 PM, Ananda Kumar anan...@gmail.com wrote:

 can you please share the code of the trigger. Any kind of error your
 getting


 On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 Hi,

 I've a trigger that writes some data to a temporary table; and at the
 end
 of the trigger writes all the temporary table data in one insert to our
 normal Innodb table.

 However, for some reason the trigger isn't copying the table from the
 temporary table to the Innodb table.  If I write in the trigger the
 inserts
 to the Innodb table, it works fine.

 Any ideas why.  I'm running MySQL 5.6.

 Thanks
 Neil

Re: Temporary Tables with Triggers Problem

[Ananda Kumar]

But, does it work inside the trigger. If not, then based on the logic,
there will not be any data, and data goes not get inserted from temp table
to innodb table


On Wed, May 29, 2013 at 7:29 PM, Neil Tompkins neil.tompk...@googlemail.com
 wrote:

 I took the following lines of code slightly modified and it returned some
 data using a normal Query Editor

 CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
 varchar(36),UserId bigint(20),ActionType varchar(36),TableName
 varchar(36),RowKey varchar(255),FieldName varchar(36),OldValue
 varchar(255),NewValue varchar(255), LoggedOn TIMESTAMP);
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2, NOW());
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',4,4, NOW());
 SELECT * FROM tempHotelRateAvailability;


 On Wed, May 29, 2013 at 2:57 PM, Ananda Kumar anan...@gmail.com wrote:

 did u check if data is getting inserted into tempHotelRateAvailability


 On Wed, May 29, 2013 at 7:21 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 This is my Trigger which doesn't seem to work; but doesn't cause a error

  DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;

 CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
 varchar(36),UserId bigint(20),ActionType
 enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
 varchar(255),FieldName varchar(36),OldValue text,NewValue text);
 IF NEW.RoomsToSell  OLD.RoomsToSell THEN
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
 CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
 END IF;

 IF SELECT COUNT(*) FROM tempHotelRateAvailability  0 THEN
 INSERT INTO AuditTrail
 SELECT tempHotelRateAvailability.* FROM
 tempHotelRateAvailability;
 END IF;

 DROP TEMPORARY TABLE tempHotelRateAvailability;

 However if I use this call in the Trigger and change a value in the
 table it works fine;

 INSERT INTO AuditTrail
 (AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
 LoggedOn)
  VALUES (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2,
 NOW());


 On Wed, May 29, 2013 at 2:49 PM, Ananda Kumar anan...@gmail.com wrote:

 can you please share the code of the trigger. Any kind of error your
 getting


 On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 Hi,

 I've a trigger that writes some data to a temporary table; and at the
 end
 of the trigger writes all the temporary table data in one insert to our
 normal Innodb table.

 However, for some reason the trigger isn't copying the table from the
 temporary table to the Innodb table.  If I write in the trigger the
 inserts
 to the Innodb table, it works fine.

 Any ideas why.  I'm running MySQL 5.6.

 Thanks
 Neil

Re: Temporary Tables with Triggers Problem

[Neil Tompkins]

OK, the data is going into the temp table.  But when I run the
command INSERT INTO AuditTrail SELECT tempHotelRateAvailability.* FROM
tempHotelRateAvailability;   from the TRIGGER it does not copy the data.

However if I run this query INSERT INTO AuditTrail SELECT
tempHotelRateAvailability.* FROM tempHotelRateAvailability;   from the
MySQL query editor (not the trigger), it copies the data find.

Any ideas ?


On Wed, May 29, 2013 at 3:02 PM, Ananda Kumar anan...@gmail.com wrote:

 But, does it work inside the trigger. If not, then based on the logic,
 there will not be any data, and data goes not get inserted from temp table
 to innodb table


 On Wed, May 29, 2013 at 7:29 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 I took the following lines of code slightly modified and it returned some
 data using a normal Query Editor

 CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
 varchar(36),UserId bigint(20),ActionType varchar(36),TableName
 varchar(36),RowKey varchar(255),FieldName varchar(36),OldValue
 varchar(255),NewValue varchar(255), LoggedOn TIMESTAMP);
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2, NOW());
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',4,4, NOW());
 SELECT * FROM tempHotelRateAvailability;


 On Wed, May 29, 2013 at 2:57 PM, Ananda Kumar anan...@gmail.com wrote:

 did u check if data is getting inserted into tempHotelRateAvailability


 On Wed, May 29, 2013 at 7:21 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 This is my Trigger which doesn't seem to work; but doesn't cause a error

  DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;

 CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
 varchar(36),UserId bigint(20),ActionType
 enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
 varchar(255),FieldName varchar(36),OldValue text,NewValue text);
 IF NEW.RoomsToSell  OLD.RoomsToSell THEN
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
 CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
 END IF;

 IF SELECT COUNT(*) FROM tempHotelRateAvailability  0 THEN
 INSERT INTO AuditTrail
 SELECT tempHotelRateAvailability.* FROM
 tempHotelRateAvailability;
 END IF;

 DROP TEMPORARY TABLE tempHotelRateAvailability;

 However if I use this call in the Trigger and change a value in the
 table it works fine;

 INSERT INTO AuditTrail
 (AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
 LoggedOn)
  VALUES (UUID(),1,'UPDATE','HotelRateAvailability',
 1,'RoomsToSell',1,2, NOW());


 On Wed, May 29, 2013 at 2:49 PM, Ananda Kumar anan...@gmail.comwrote:

 can you please share the code of the trigger. Any kind of error your
 getting


 On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 Hi,

 I've a trigger that writes some data to a temporary table; and at the
 end
 of the trigger writes all the temporary table data in one insert to
 our
 normal Innodb table.

 However, for some reason the trigger isn't copying the table from the
 temporary table to the Innodb table.  If I write in the trigger the
 inserts
 to the Innodb table, it works fine.

 Any ideas why.  I'm running MySQL 5.6.

 Thanks
 Neil

Re: Temporary Tables with Triggers Problem

[hsv]

 2013/05/29 14:51 +0100, Neil Tompkins 
This is my Trigger which doesn't seem to work; but doesn't cause a error

DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;

CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
varchar(36),UserId bigint(20),ActionType
enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
varchar(255),FieldName varchar(36),OldValue text,NewValue text);
IF NEW.RoomsToSell  OLD.RoomsToSell THEN
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
END IF;

IF SELECT COUNT(*) FROM tempHotelRateAvailability  0 THEN
INSERT INTO AuditTrail
SELECT tempHotelRateAvailability.* FROM tempHotelRateAvailability;

END IF;

DROP TEMPORARY TABLE tempHotelRateAvailability;

However if I use this call in the Trigger and change a value in the table
it works fine;

INSERT INTO AuditTrail
(AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
LoggedOn)
 VALUES (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2,
NOW()); 


You have left out the opening line, but it looks like AFTER UPDATE; is the 
table AuditTrail, or another?

How did this pass the parser,
IF SELECT COUNT(*) FROM tempHotelRateAvailability  0 THEN
? If Workbench corrected it, there is no knowing what the code really is. This 
is correct,
IF (SELECT COUNT(*) FROM tempHotelRateAvailability)  0 THEN
but it is just as well to write
IF EXISTS(SELECT * FROM tempHotelRateAvailability) THEN
.

Why bother with the temporary table? It never has more rows; it is just as well 
to insert straight into AuditTrail if NEW.RoomsToSell  OLD.RoomsToSell.


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

Long integer constant problem in views

[Martin Koch]

Hi List

I have a table with a primary key with type binary(16) and a few columns.
I'd trying to create a view that looks up all rows with a particular key,
i.e. something like

CREATE OR REPLACE VIEW foo AS
  SELECT *
  FROM mytable
  WHERE id = X'36a461c81cab40169791f49ad65a3728';

I use the mysql command line client to create the view. When I now inspect
the view using, say, mysql workbench, the id has been mangled in the where
clause which now reads

WHERE (`mytable`.`id` = 0x9791f49ad65a37)

I then tried expressing the id as something that will fit within 8 bytes

WHERE id = X'36a461c81cab4016'  16 | X'9791f49ad65a3728';

Unfortunately, this makes the query much slower.

I have now worked around this in another way, but I'd like to know if there
is a way of doing this?

Thanks,
/Martin Koch

RE: Long integer constant problem in views

[Rick James]

  WHERE id = UNHEX('36a461c81cab40169791f49ad65a3728')

 -Original Message-
 From: Martin Koch [mailto:m...@issuu.com]
 Sent: Tuesday, April 30, 2013 8:18 AM
 To: mysql@lists.mysql.com
 Subject: Long integer constant problem in views
 
 Hi List
 
 I have a table with a primary key with type binary(16) and a few
 columns.
 I'd trying to create a view that looks up all rows with a particular
 key, i.e. something like
 
 CREATE OR REPLACE VIEW foo AS
   SELECT *
   FROM mytable
   WHERE id = X'36a461c81cab40169791f49ad65a3728';
 
 I use the mysql command line client to create the view. When I now
 inspect the view using, say, mysql workbench, the id has been mangled
 in the where clause which now reads
 
 WHERE (`mytable`.`id` = 0x9791f49ad65a37)
 
 I then tried expressing the id as something that will fit within 8
 bytes
 
 WHERE id = X'36a461c81cab4016'  16 | X'9791f49ad65a3728';
 
 Unfortunately, this makes the query much slower.
 
 I have now worked around this in another way, but I'd like to know if
 there is a way of doing this?
 
 Thanks,
 /Martin Koch

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

Re: Long integer constant problem in views

[hsv]

 2013/04/30 17:17 +0200, Martin Koch 
CREATE OR REPLACE VIEW foo AS
  SELECT *
  FROM mytable
  WHERE id = X'36a461c81cab40169791f49ad65a3728';

Try this: _binary X'36a461c81cab40169791f49ad65a3728'

SHOW CREATE VIEW is the command for the client.


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

Please Help. selectcol_arrayref problem

[Richard Reina]

use DBI;
my $dbh = DBI-connect( DBI:mysql:rushload;192.168.0.1, $usrr, $passw, {
RaiseError = 3 } );
my $dbs = $dbh-selectcol_arrayref(show databases);

#my $dsn = dbi:mysql:information_schema:192.168.0.1:3306;
#my $dbh = DBI-connect($dsn, $usrr, $passw);

my $dbs = $dbh-selectcol_arrayref('show databases');

print @$dbs\n;

When I query the server for a list of databases with the code above it
returns the name of just two and there are over 10.

Any ideas?

Thanks

Re: Please Help. selectcol_arrayref problem

[Richard Reina]

I did a GRANT REPLICATION SLAVE ON *.* TO 'user'@'192.168.0.23' IDENTIFIED
BY 'psswd';

on the master. Doesn't *.* mean everything? Why would it just show me to
databases?






2013/4/2 Larry Martell larry.mart...@gmail.com

 On Tue, Apr 2, 2013 at 11:30 AM, Richard Reina gatorre...@gmail.com
 wrote:
  use DBI;
  my $dbh = DBI-connect( DBI:mysql:rushload;192.168.0.1, $usrr, $passw,
 {
  RaiseError = 3 } );
  my $dbs = $dbh-selectcol_arrayref(show databases);
 
  #my $dsn = dbi:mysql:information_schema:192.168.0.1:3306;
  #my $dbh = DBI-connect($dsn, $usrr, $passw);
 
  my $dbs = $dbh-selectcol_arrayref('show databases');
 
  print @$dbs\n;
 
  When I query the server for a list of databases with the code above it
  returns the name of just two and there are over 10.
 
  Any ideas?

 Permissions - the user you're logging in as probably only has
 permission to see the 2 that are being returned.

Re: Please Help. selectcol_arrayref problem

[Larry Martell]

On Tue, Apr 2, 2013 at 11:30 AM, Richard Reina gatorre...@gmail.com wrote:
 use DBI;
 my $dbh = DBI-connect( DBI:mysql:rushload;192.168.0.1, $usrr, $passw, {
 RaiseError = 3 } );
 my $dbs = $dbh-selectcol_arrayref(show databases);

 #my $dsn = dbi:mysql:information_schema:192.168.0.1:3306;
 #my $dbh = DBI-connect($dsn, $usrr, $passw);

 my $dbs = $dbh-selectcol_arrayref('show databases');

 print @$dbs\n;

 When I query the server for a list of databases with the code above it
 returns the name of just two and there are over 10.

 Any ideas?

Permissions - the user you're logging in as probably only has
permission to see the 2 that are being returned.

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

Re: Please Help. selectcol_arrayref problem

[Stillman, Benjamin]

Nope. That's just granting replication privileges so it can read updates
on all tables on all databases. It cannot select anything.

Why are you trying to connect with a replication slave user?







On 4/2/13 1:47 PM, Richard Reina gatorre...@gmail.com wrote:

I did a GRANT REPLICATION SLAVE ON *.* TO 'user'@'192.168.0.23'
IDENTIFIED
BY 'psswd';

on the master. Doesn't *.* mean everything? Why would it just show me to
databases?






2013/4/2 Larry Martell larry.mart...@gmail.com

 On Tue, Apr 2, 2013 at 11:30 AM, Richard Reina gatorre...@gmail.com
 wrote:
  use DBI;
  my $dbh = DBI-connect( DBI:mysql:rushload;192.168.0.1, $usrr,
$passw,
 {
  RaiseError = 3 } );
  my $dbs = $dbh-selectcol_arrayref(show databases);
 
  #my $dsn = dbi:mysql:information_schema:192.168.0.1:3306;
  #my $dbh = DBI-connect($dsn, $usrr, $passw);
 
  my $dbs = $dbh-selectcol_arrayref('show databases');
 
  print @$dbs\n;
 
  When I query the server for a list of databases with the code above it
  returns the name of just two and there are over 10.
 
  Any ideas?

 Permissions - the user you're logging in as probably only has
 permission to see the 2 that are being returned.





Notice: This communication may contain privileged and/or confidential 
information. If you are not the intended recipient, please notify the sender by 
email, and immediately delete the message and any attachments without copying 
or disclosing them. LBI may, for any reason, intercept, access, use, and 
disclose any information that is communicated by or through, or which is stored 
on, its networks, applications, services, and devices.

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

Re: 答复: PreparedStatement problem

[Johan De Meersman]

- Original Message -
 From: ZhangFangXue zhangfang...@sogou-inc.com
 
 well, the problem is that I used it like this:
 prep_stmt = con - prepareStatement (INSERT INTO City (CityNumber)

The statement in your original mail used CityName, which sounds like it should 
be a string. Fieldname confusion?


-- 
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: PreparedStatement problem

[Johan De Meersman]

- Original Message -
 From: ZhangFangXue zhangfang...@sogou-inc.com
 
 Hi, when I use PreparedStatement in c++ connector, I find some
 unexcepted error,

Well, first of all, you don't actually say what the error is that you're 
seeing. This tends to be on the rather helpful side when trying to diagnose it.

 prep_stmt = con - prepareStatement (INSERT INTO City (CityName)
 VALUES (?));
 prep_stmt - setInt (1, 23); //this statement didn't act normally!!!

However, if you're inserting into a text field, it may well be that the parser 
will balk at you trying to bind an integer to that, no? If you want to insert a 
string, it's fairly common practice to provide an actual string to insert. 
Quite the novel idea, I know.

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

答复: PreparedStatement problem

[ZhangFangXue]

well, the problem is that I used it like this:
prep_stmt = con - prepareStatement (INSERT INTO City (CityNumber) VALUES 
(?));
prep_stmt - setInt (1, 23); //this statement didn’t act normally!!!

then when I execute the statement, the result does not appear to be correct, as 
CityNumber is not 23, I am sure that the field is integer.

I guess that something about number is not well delt in the PreparedStatement 
class.
If I use setString(1, 23), the result is correct!!

thank you for your response!


-邮件原件-
发件人: Johan De Meersman [mailto:vegiv...@tuxera.be]
发送时间: 2013年2月19日 16:12
收件人: ZhangFangXue
抄送: mysql@lists.mysql.com
主题: Re: PreparedStatement problem

- Original Message -
 From: ZhangFangXue zhangfang...@sogou-inc.com

 Hi, when I use PreparedStatement in c++ connector, I find some
 unexcepted error,

Well, first of all, you don't actually say what the error is that you're 
seeing. This tends to be on the rather helpful side when trying to diagnose it.

 prep_stmt = con - prepareStatement (INSERT INTO City (CityName)
 VALUES (?));
 prep_stmt - setInt (1, 23); //this statement didn't act normally!!!

However, if you're inserting into a text field, it may well be that the parser 
will balk at you trying to bind an integer to that, no? If you want to insert a 
string, it's fairly common practice to provide an actual string to insert. 
Quite the novel idea, I know.

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

答复: 答复: PreparedStatement problem

[ZhangFangXue]

yes, it is CityName, but it is not the point, setInt works abnormally..

By the way, I found there is not a thorough introduction to the mysql c++ 
connector, can you give me some hint?

-邮件原件-
发件人: Johan De Meersman [mailto:vegiv...@tuxera.be]
发送时间: 2013年2月19日 16:36
收件人: ZhangFangXue
抄送: mysql@lists.mysql.com
主题: Re: 答复: PreparedStatement problem

- Original Message -
 From: ZhangFangXue zhangfang...@sogou-inc.com

 well, the problem is that I used it like this:
 prep_stmt = con - prepareStatement (INSERT INTO City (CityNumber)

The statement in your original mail used CityName, which sounds like it should 
be a string. Fieldname confusion?


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

RE: SELECT subquery problem

[cl]

  You can do:
  
  
  SELECT last_name, first_name, phone, if(pub_email=Y,email,) as email
  FROM `mydatabasetable` WHERE `current_member` = Y AND `pub_name` = Y
  ORDER BY last_name ASC
  

Gracias, Carlos. This worked fine!

---Fritz


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

SELECT subquery problem

[cl]

De-lurking here.

I am trying to figure out how to return results from a query. What I need to do 
is to return 4 columns from a database. This is easy:

SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE 
`current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC

This works fine, as expected.

But, I want to only display the value in `email` if the value in another field, 
`pub_email` = Y  So, the resultant output would look like this, for instance, 
if the value of `pub_email` =N for Mr. Wills:

Jones  John 555-555-   johnjo...@nowhere.com
Smith  Jim   555-222-   jimsm...@nothing.com
WillsChill  555-111-
Zorro  Felicity  555-999-  felicityzo...@madeup.com

Can't quite figure out how to express this.

TIA for your suggestions!


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

Re: SELECT subquery problem

[Andrew Moore]

Try using a CASE construct in the select. Should work for this.

A


On Tue, Feb 5, 2013 at 3:25 PM, Stefan Kuhn stef...@web.de wrote:

 You cannot do this. A sql result alwas has the same number of columns in
 each row. You could have null or  in the column, though. This could be
 done via the if(,,)-statement of mysql or by using a union and two selects,
 one for pub_email=n and the other for the rest.


 Gesendet: Dienstag, 05. Februar 2013 um 15:49 Uhr
 Von: cl c...@nimbleeye.com
 An: mysql@lists.mysql.com
 Betreff: SELECT subquery problem
 De-lurking here.

 I am trying to figure out how to return results from a query. What I need
 to do is to return 4 columns from a database. This is easy:

 SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE
 `current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC

 This works fine, as expected.

 But, I want to only display the value in `email` if the value in another
 field, `pub_email` = Y So, the resultant output would look like this, for
 instance, if the value of `pub_email` =N for Mr. Wills:

 Jones John 555-555- johnjo...@nowhere.com
 Smith Jim 555-222- jimsm...@nothing.com
 Wills Chill 555-111-
 Zorro Felicity 555-999- felicityzo...@madeup.com

 Can't quite figure out how to express this.

 TIA for your suggestions!


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




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

Aw: SELECT subquery problem

[Stefan Kuhn]

You cannot do this. A sql result alwas has the same number of columns in each 
row. You could have null or  in the column, though. This could be done via 
the if(,,)-statement of mysql or by using a union and two selects, one for 
pub_email=n and the other for the rest.


Gesendet: Dienstag, 05. Februar 2013 um 15:49 Uhr
Von: cl c...@nimbleeye.com
An: mysql@lists.mysql.com
Betreff: SELECT subquery problem
De-lurking here.

I am trying to figure out how to return results from a query. What I need to do 
is to return 4 columns from a database. This is easy:

SELECT last_name, first_name, phone, email FROM `mydatabasetable` WHERE 
`current_member` = Y AND `pub_name` = Y ORDER BY last_name ASC

This works fine, as expected.

But, I want to only display the value in `email` if the value in another field, 
`pub_email` = Y So, the resultant output would look like this, for instance, 
if the value of `pub_email` =N for Mr. Wills:

Jones John 555-555- johnjo...@nowhere.com
Smith Jim 555-222- jimsm...@nothing.com
Wills Chill 555-111-
Zorro Felicity 555-999- felicityzo...@madeup.com

Can't quite figure out how to express this.

TIA for your suggestions!


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




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

Mysqldump routines dump, problem with lock tables.

[Rafał Radecki]

Hi All.

I use:

# rpm -qa | grep -i percona-server-server
Percona-Server-server-55-5.5.28-rel29.3.388.rhel6.x86_64

My system:

# uname -a;cat /etc/redhat-release
Linux prbc01.mg.local 2.6.32-279.19.1.el6.centos.plus.x86_64 #1 SMP
Wed Dec 19 06:20:23 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
Red Hat Enterprise Linux Server release 6.3 (Santiago)

I have a backup script which at some point calls:

mysqldump --default-character-set=utf8 --routines --no-data
--no-create-info --skip-triggers -S /mysql/database.sock -u backup
-pxxx database

and I have error:

mysqldump: Got error: 1045: Access denied for user 'yyy'@'zzz' (using
password: YES) when using LOCK TABLES

So I thinke that mysqldump locks the table (--add-locks) by default.

But for this user:

mysql show grants for yyy@'zzz';
++
| Grants for backup@localhost

   |
++
| GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW ON *.* TO 'yyy'@'zzz'
IDENTIFIED BY PASSWORD ... |
  |
++
2 rows in set (0.00 sec)

So why is this error showing?
When I add --single-transaction to mysqldump everything is ok. But I
would like to have this table locked because:

mysql SELECT ENGINE
- FROM information_schema.TABLES
- WHERE TABLE_SCHEMA = 'information_schema'
- AND TABLE_NAME = 'routines';
++
| ENGINE |
++
| MyISAM |
++

so information_schema.tables is myisam.

So why do I get the error about LOCK TABLES?

Best regards,
Rafal Radecki.

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

RE: Mysqldump routines dump, problem with lock tables.

[Rick James]

Do not try to dump or reload information_schema.  It is derived meta 
information, not real tables.

 -Original Message-
 From: Rafał Radecki [mailto:radecki.ra...@gmail.com]
 Sent: Monday, February 04, 2013 12:17 AM
 To: mysql@lists.mysql.com
 Subject: Mysqldump routines dump, problem with lock tables.
 
 Hi All.
 
 I use:
 
 # rpm -qa | grep -i percona-server-server
 Percona-Server-server-55-5.5.28-rel29.3.388.rhel6.x86_64
 
 My system:
 
 # uname -a;cat /etc/redhat-release
 Linux prbc01.mg.local 2.6.32-279.19.1.el6.centos.plus.x86_64 #1 SMP Wed
 Dec 19 06:20:23 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux Red Hat
 Enterprise Linux Server release 6.3 (Santiago)
 
 I have a backup script which at some point calls:
 
 mysqldump --default-character-set=utf8 --routines --no-data --no-
 create-info --skip-triggers -S /mysql/database.sock -u backup -pxxx
 database
 
 and I have error:
 
 mysqldump: Got error: 1045: Access denied for user 'yyy'@'zzz' (using
 password: YES) when using LOCK TABLES
 
 So I thinke that mysqldump locks the table (--add-locks) by default.
 
 But for this user:
 
 mysql show grants for yyy@'zzz';
 +--
 ---
 ---+
 | Grants for backup@localhost
 
|
 +--
 ---
 ---+
 | GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW ON *.* TO 'yyy'@'zzz'
 IDENTIFIED BY PASSWORD ... |
   |
 +--
 ---
 ---+
 2 rows in set (0.00 sec)
 
 So why is this error showing?
 When I add --single-transaction to mysqldump everything is ok. But I
 would like to have this table locked because:
 
 mysql SELECT ENGINE
 - FROM information_schema.TABLES
 - WHERE TABLE_SCHEMA = 'information_schema'
 - AND TABLE_NAME = 'routines';
 ++
 | ENGINE |
 ++
 | MyISAM |
 ++
 
 so information_schema.tables is myisam.
 
 So why do I get the error about LOCK TABLES?
 
 Best regards,
 Rafal Radecki.
 
 --
 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