Re: Last established connection timestamp by a specific user

[Singer Wang]

Unfortunately not with the standard configuration.

You're best bet going forward would be to look at
MySQL Enterprise Audit -
https://www.mysql.com/products/enterprise/audit.html


On Tue, Oct 10, 2017 at 3:54 PM, Gone, Sajan  wrote:

> Hi,
>
>We have a MySQL instance which is currently running on version
> `5.7.11-enterprise-commercial-advanced-log`.  On this instance I am
> trying to figure out the most recent timestamp at which a specific user has
> established a connection to this instance (or) performed any DML operations
> which might have changed the status of the database.
>
> Is there any way I can get such information from the 
> information_schema/performance_schema
> tables (or) from any of the mysql logs?
>
> Thank You,
> Sajan Gone
> Database Administrator.
>
> 
>
> 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. LB 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.
>

Re: Changing storage engine in dump file.

[Singer Wang]

Depending on the version of MySQL and InnoDB engine, the max key length can
be 3072 for InnoDB..

On Wed, Aug 12, 2015 at 9:21 AM, Johan De Meersman vegiv...@tuxera.be
wrote:


 - Original Message -
  From: geetanjali mehra mailtogeetanj...@gmail.com
  Subject: Changing storage engine in dump file.
 
  Is there any implications in doing so. Is this approach correct? Will I
  face any problem in syncing the slave?

 The first thing that occurs to me, is that the maximum key lenght for
 MyISAM is 1000 bytes, but for InnoDB it is only 786 bytes...

 Depending on your server version, InnoDB may not yet have fulltext
 indices, and even if it does, the behaviour is different from the MyISAM
 ones.

 You are likely to run into a myriad of tiny little differences, and it
 seems to me like a fairly bad plan. Why do you want this?


 --
 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: server-side logging of query errors?

[Singer Wang]

The performance hit of the Percona Audit is 15% for disk logging and for
remote syslog we found it is lower. It logs everything including bad
queries (eg. select fark from fark from fark fark fark from frak). You
should be able to write a JSON parser that extracts what you want based on
the log (eg. STATUS, COMMAND, NAME).

On Wed, Jun 24, 2015 at 11:05 AM, Johan De Meersman vegiv...@tuxera.be
wrote:


 --

 *From: *Singer X.J. Wang w...@singerwang.com
 *Subject: *Re: server-side logging of query errors?

 You could log all queries using the audit plugin, 15% hit..

 Fair point, though: maybe one of the different audit plugins has the
 capability to specifically log faulty requests. Have a look through the
 slides from Percona Live London 2014, there was a talk about auditing.



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

Re: server-side logging of query errors?

[Singer Wang]

Yep, as shown below:

root@audit-db.ec2:(none) select fark from fark from fark fark fark from
frak;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'from fark fark fark from frak' at line 1
root@audit-db.ec2:(none)

Jun 24 16:29:52 audit-db percona-audit:
{audit_record:{name:Query,record:217_2015-06-24T16:29:52,timestamp:2015-06-24T16:29:52
UTC,command_class:error,connection_id:59,status:1064,sqltext:select
fark from fark from fark fark fark from frak,user:root[root] @
localhost [],host:localhost,os_user:,ip:}}

error 1064


On Wed, Jun 24, 2015 at 11:40 AM, Tomasz Chmielewski man...@wpkg.org
wrote:

 Normal general log also logs everything including bad queries (i.e. SLCECT
 BLAH BLAH;) - although does not say if it was an error (i.e. syntax) or not.

 Does the audit plugin log the actual error?


 Tomasz

 On 2015-06-25 00:32, Singer Wang wrote:

 The performance hit of the Percona Audit is 15% for disk logging and
 for remote syslog we found it is lower. It logs everything including
 bad queries (eg. select fark from fark from fark fark fark from frak).
 You should be able to write a JSON parser that extracts what you want
 based on the log (eg. STATUS, COMMAND, NAME).

 On Wed, Jun 24, 2015 at 11:05 AM, Johan De Meersman
 vegiv...@tuxera.be wrote:

  -

  FROM: Singer X.J. Wang w...@singerwang.com
 SUBJECT: Re: server-side logging of query errors?


  You could log all queries using the audit plugin, 15% hit..

 Fair point, though: maybe one of the different audit plugins has
 the capability to specifically log faulty requests. Have a look
 through the slides from Percona Live London 2014, there was a talk
 about auditing.

 --

 Unhappiness is discouraged and will be corrected with kitten
 pictures.

Re: server-side logging of query errors?

[Singer Wang]

You could log all queries using the audit plugin, 15% hit..

On Tue, Jun 23, 2015 at 4:54 AM, Johan De Meersman vegiv...@tuxera.be
wrote:

 - Original Message -
  From: Tomasz Chmielewski man...@wpkg.org

  It would be a mild security risk; a malicious
  (or just stupid, see Hanlon's razor) user could spam your server with
  malformed requests until the logging disk runs full, at which point
  the daemon would suspend operations until space is freed.
 
  I don't think it's a valid argument - the same is true right now for
  general query log. Any stupid/malicious user can produce loads of
  queries and fill the disk if one has general query log enabled.
 
  In short, anyone enabling any logging should consider what limitations
  it brings.

 Including quite a bit of overhead, which is why its use is discouraged in
 production :-)


 --
 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: SHOW FULL COLUMNS QUERIES hogging my CPU

[Singer Wang]

What's your technology stack?


On Mon, Jun 2, 2014 at 10:21 AM, Jatin Davey jasho...@cisco.com wrote:

 On 6/2/2014 7:18 PM, Reindl Harald wrote:


 Am 02.06.2014 15:35, schrieb Jatin Davey:

 I am no expert with mysql and databases. Hence seeking out some help on
 this forum.

 Basically i got a query dump of my application during its operation. I
 had collected the queries for about 4 hours.
 Ran some scripts on the number of queries being sent to the databases.

 The query file was a whopping 4 GB is size. Upon analyzing the queries i
 found that there were a total of 30
 million queries made to the Database out of which 10 million queries
 were only doing SHOW FULL COLUMN queries.

 The SHOW FULL COLUMN queries were of the format as below:

 SHOW FULL COLUMNS FROM `APIC_PHY_FABRIC_PHYSICAL_INTERFACE` FROM
 `db_private_admin` LIKE '%'

 This is causing considerable cpu usage in %user_time in my system

 fix your application - there is no single reason to run such
 queries 10 million times because the result won't change all
 the time

 and avoid like in general


 Our application does not send such queries to the DB. I have searched
 through my entire code and we dont run such queries. It has something to do
 with a layer below our application. But i am not sure as to where it is.

 Thanks
 Jatin


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

Re: database perfomance worries

[Singer Wang]

O
On 2013-07-02 5:31 PM, Andy Wallace awall...@ihouseweb.com wrote:

 We are on a quest to improve the overall performance of our database. It's
 generally
 working pretty well, but we periodically get big slowdowns for no apparent
 reason. A
 prime example today - in the command line interface to the DB, I tried to
 update one
 record, and got:

 mysql update agent set number_of_emails = 5 where acnt = 'AR287416';
 Query OK, 1 row affected (36.35 sec)
 Rows matched: 1  Changed: 1  Warnings: 0

 36 seconds to update one table? The primary key is `acnt`. If I run the
 same (basic)
 command again a few seconds later, I get:

 mysql update agent set number_of_emails = 15 where acnt = 'AR287416';
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1  Changed: 1  Warnings: 0

 Why would we be getting such huge variations? We're running Solaris 10 on
 i386, with
 4 processors and 16GB of memory, MySQL 5.1.46-log. We are working out a
 plan to upgrade
 to MySQL 5.6, but I certainly don't want to depend on that upgrade to
 solve all performance
 problems.

 CREATE TABLE `agent`
 (
   `acnt` varchar(20) NOT NULL,
   `passwd` varchar(20) NOT NULL,
   `package` char(2) DEFAULT NULL,
   `data_template` varchar(20) DEFAULT 'NULL',
   `default_search_type` enum('1','2','3') NOT NULL DEFAULT '1',
   `status` enum('A','T','P','C','D','X','**S') NOT NULL
   `flags` varchar(10) DEFAULT NULL COMMENT 'pipe-separated flags',
   `aliases` varchar(4000) NOT NULL DEFAULT '',
   `offices` varchar(4000) NOT NULL DEFAULT '',
   `license_no` varchar(40) NOT NULL DEFAULT '',
   `agent_code` varchar(20) DEFAULT NULL,
   `office_code` varchar(20) DEFAULT NULL,
   `parent_acnt` varchar(20) DEFAULT NULL,
   `number_of_agentlinks` int(11) DEFAULT NULL,
   `number_of_emails` int(11) DEFAULT NULL,
   `fname` varchar(30) DEFAULT NULL,
   `lname` varchar(30) DEFAULT NULL,

 whole bunch of other fields

   PRIMARY KEY (`acnt`),
   KEY `parent_acnt` (`parent_acnt`),
   KEY `status` (`status`),
   KEY `email` (`email`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8


 --
 Andy Wallace
 iHOUSEweb, Inc.
 awall...@ihouseweb.com
 (866) 645-7700 ext 219
 --
 Sometimes it pays to stay in bed on Monday, rather than spending the rest
 of the week debugging Monday's code.
 - Christopher Thompson

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

Re: slave replication with lots of 'duplicate entry' errors

[Singer Wang]

Are you using all InnoDB?

S


On Thu, Feb 14, 2013 at 5:55 PM, Robert Citek robert.ci...@gmail.comwrote:

 On Thu, Feb 14, 2013 at 4:54 PM, Manuel Arostegui man...@tuenti.com
 wrote:
  2013/2/13 Robert Citek robert.ci...@gmail.com
 
  On Wed, Feb 13, 2013 at 8:59 AM, Robert Citek robert.ci...@gmail.com
  wrote:
   Any other possibilities?  Do other scenarios become likely if there
   are two or more tables?
  
   Of those, which are the most likely?
 
  [from off-list responder]:
   Other possibility: The replication is reading from master not from the
   point when the dump was done, but some time before and is fetching
 insert
   statements which are already in the dump.
 
  To prevent that I used the coordinates in the dump file included with
  --master-data=2.  Could the coordinates still be off?
 
 
  Hello,
 
  Are you sure nothing is getting inserted directly into the slave? Is it
 in
  read only mode?
  If you're starting replication using the values provided by
 --master-data=2
  (which should be something like):
 
   -- Position to start replication or point-in-time recovery from
 
  -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000974',
  MASTER_LOG_POS=240814775;
 
  And if you're using the right IP, there's no reason to have duplicate
  entries unless someone is writing directly into the slave.
 
  Manuel.

 According to the client, nothing is writing to the slave and
 everything is being logged at the master.  I have not had the
 opportunity to independently verified any of this, yet.  I do know
 that the slave is not in read-only mode, but rather we promise not to
 write to it mode.

 At the moment, I am trying to come up with plausible explanations for
 the observations.

 Thanks for your feedback.

 Regards,
 - Robert

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

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Singer Wang]

Its a very pedantic case, but we had a few instances where it was an issue
at my last job. It basically involved multi-table deletes and aliasing.. I
quote the change notes for MySQL 5.5.3

*Incompatible Change:* Several changes were made to alias resolution in
multiple-table DELETE statements so that it is no longer possible to have
inconsistent or ambiguous table aliases.

   -

   In MySQL 5.1.23, alias declarations outside the *table_references* part
   of the statement were disallowed for theUSING variant of multiple-table
   DELETE syntax, to reduce the possibility of ambiguous aliases that could
   lead to ambiguous statements that have unexpected results such as deleting
   rows from the wrong table.

   Now alias declarations outside *table_references* are disallowed for all
   multiple-table DELETE statements. Alias declarations are permitted only
   in the *table_references* part.

   Incorrect:

   DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2;
   DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2;

   Correct:

   DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2;
   DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2;

   -

   Previously, for alias references in the list of tables from which to
   delete rows in a multiple-table delete, the default database is used unless
   one is specified explicitly. For example, if the default database is db1,
   the following statement does not work because the unqualified alias
   reference a2 is interpreted as having a database of db1:

   DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2
   WHERE a1.id=a2.id;

   To correctly match an alias that refers to a table outside the default
   database, you must explicitly qualify the reference with the name of the
   proper database:

   DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2
   WHERE a1.id=a2.id;

   Now alias resolution does not require qualification and alias references
   should not be qualified with the database name. Qualified names are
   interpreted as referring to tables, not aliases.

Statements containing alias constructs that are no longer permitted must be
rewritten. (Bug #27525)



On Thu, Feb 14, 2013 at 6:11 PM, Rick James rja...@yahoo-inc.com wrote:

 Singer, do you have some examples?

  -Original Message-
  From: Singer Wang [mailto:w...@singerwang.com]
  Sent: Thursday, February 14, 2013 2:59 PM
  To: Mihail Manolov
  Cc: Mike Franon; Akshay Suryavanshi; mysql@lists.mysql.com
  Subject: Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6
 
  There are queries that works with 5.1/5.0 that do not work with 5.5, I
  would test extensively..
 
  S
 
 
  On Thu, Feb 14, 2013 at 5:22 PM, Mihail Manolov 
  mihail.mano...@liquidation.com wrote:
 
   You could jump from 5.0 directly to 5.5 and skip 5.1. I have without
   any issues. There are some configuration file change, which you may
   want to consider checking. I definitely recommend upgrading your
   development servers for an extensive testing. Some queries _may_ run
   slower or not work at all and you may have to rearrange how you join
  tables in your queries.
  
   The upgrade from 5.5 to 5.6 should me smoother, though.
  
  
   On Feb 14, 2013, at 4:28 PM, Mike Franon wrote:
  
Great thanks for the info, I guess the best way to do this is take
  a
spare server, set it up with our standard setup, and then start the
upgrade as you said 5.0 - 5.1 - 5.5, test and then upgrade to 5.6
and test.
   
   
   
   
   
   
   
   
On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi
akshay.suryavansh...@gmail.com wrote:
Mike,
   
5.6 is GA now, so its stable release. Also you should not jump to
5.6 directly, atleast from 5.0.
   
There are many bug fixes and changes in 5.1, so you should
  consider
this way.
   
5.0--5.1--5.5 (all slaves first, and then the master)
   
And further 5.5 -- 5.6 (again all slaves first and then the
master)
   
Hope this helps.
   
Cheers!
   
On Fri, Feb 15, 2013 at 2:38 AM, Mike Franon
  kongfra...@gmail.com
   wrote:
   
I have 1 master with many slaves, using the master only for
inserts and the rest are readers.
   
   
Is 5.6 stable?  Or better off to go to 5.5?
   
If so do I need to make a few steps or can go straight from 5.0
  to 5.6?
   
   
Any best practices and recommendations?
   
Thanks
   
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
   
   
   
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
   
  
  
   --
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:http://lists.mysql.com/mysql
  
  

Re: Upgrading form mysql 5.0.90 to 5.5 or 5.6

[Singer Wang]

There are queries that works with 5.1/5.0 that do not work with 5.5, I
would test extensively..

S


On Thu, Feb 14, 2013 at 5:22 PM, Mihail Manolov 
mihail.mano...@liquidation.com wrote:

 You could jump from 5.0 directly to 5.5 and skip 5.1. I have without any
 issues. There are some configuration file change, which you may want to
 consider checking. I definitely recommend upgrading your development
 servers for an extensive testing. Some queries _may_ run slower or not work
 at all and you may have to rearrange how you join tables in your queries.

 The upgrade from 5.5 to 5.6 should me smoother, though.


 On Feb 14, 2013, at 4:28 PM, Mike Franon wrote:

  Great thanks for the info, I guess the best way to do this is take a
  spare server, set it up with our standard setup, and then start the
  upgrade as you said 5.0 - 5.1 - 5.5, test and then upgrade to 5.6
  and test.
 
 
 
 
 
 
 
 
  On Thu, Feb 14, 2013 at 4:22 PM, Akshay Suryavanshi
  akshay.suryavansh...@gmail.com wrote:
  Mike,
 
  5.6 is GA now, so its stable release. Also you should not jump to 5.6
  directly, atleast from 5.0.
 
  There are many bug fixes and changes in 5.1, so you should consider this
  way.
 
  5.0--5.1--5.5 (all slaves first, and then the master)
 
  And further 5.5 -- 5.6 (again all slaves first and then the master)
 
  Hope this helps.
 
  Cheers!
 
  On Fri, Feb 15, 2013 at 2:38 AM, Mike Franon kongfra...@gmail.com
 wrote:
 
  I have 1 master with many slaves, using the master only for inserts
  and the rest are readers.
 
 
  Is 5.6 stable?  Or better off to go to 5.5?
 
  If so do I need to make a few steps or can go straight from 5.0 to 5.6?
 
 
  Any best practices and recommendations?
 
  Thanks
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 


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

Re: MySQL dying?

[Singer Wang]

Lol! Good point Karen!


On Tue, Dec 4, 2012 at 1:02 PM, Karen Abgarian a...@apple.com wrote:

 A touch of realism: we are all dying.   For some, it may take a while,
 hopefully.

 On 04.12.2012, at 9:53, Tim Pownall wrote:

  Mysql is used by just about every web host and is one of the most common
  database servers around the world. I do not have any intent to stop using
  mysql unless they start charging for it which I do not think will happen.
 
  Thanks,
 
  Tim Pownall
  Sr. Linux Systems Monitoring
  Hostgator.com LLC
 
  On Tue, Dec 4, 2012 at 11:45 AM, Anthony Pace anthony.p...@utoronto.ca
 wrote:
 
  I have heard that due to Oracle taking over, the OS community is
 shifting
  to other type of DB's .
 
  Any thoughts?
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 
 
 
  --
 
  Thanks,
 
  Tim Pownall
  GNU/Linux Systems Monitoring
  610-621-9712
  pownall...@gmail.com


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

Re: Mysql backup for large databases

[Singer Wang]

On Thu, Nov 1, 2012 at 11:34 AM, Rick James rja...@yahoo-inc.com wrote:

 Full backup:
 * Xtrabackup (Backup: slight impact on source; more if you have MyISAM (as
 mentioned))
 * Slave (Backup: zero impact on Master -- once replication is set up)
 * LVM -- a minute of server down; see below

Why do you need downtime?



 Full restore:
 * Xtrabackup - Takes time
 * Slave - minute(s) to failover, mostly dealing with clients pointing to
 the new master.
 * LVM -- a minute? see below

 With LVM you are taking a filesystem snapshot.  This requires a brief
 restart of mysqld to assure that anything cached is sync'd to disk.  After
 the snapshot is taken, you are at liberty to copy the snapshot to somewhere
 else.  (This must be done before you fill up the volume used for
 copy-on-write stuff, etc.)  The snapshot can be used to instantly restore
 the entire system on this or some other server.

 For partial backups...
 * Xtrabackup - already discussed
 * Slave -- You are free to construct whatever slicing and dicing, even
 changing engines to MyISAM and copying files.
 * LVM -- probably not useful.

 Consider using PARTITIONing.  With it, you could split up a table
 according to time, copy (row by row) the oldest partition to somewhere
 else, DROP PARTITION (instantaneous).  Restoring is no better than LOAD
 DATA.  In the near future (5.6.x?), you can disconnect a partition from a
 table and move it to another table; this will greatly speed up archiving.

  -Original Message-
  From: Reindl Harald [mailto:h.rei...@thelounge.net]
  Sent: Thursday, November 01, 2012 4:47 AM
  To: mysql@lists.mysql.com
  Subject: Re: Mysql backup for large databases
 
 
 
  Am 01.11.2012 11:28, schrieb Machiel Richards - Gmail:
   Using mysqldump and restores on an 80-100GB database seems a bit
   unpractical as the restore times seems to get quite long as well as the
  backup times.
 
  * setup a master/slave configuration
  * stop the slave
  * rsync the raw datadir to whatever backup-medium/location
  * start the salve again
 


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

Re: Mysql backup for large databases

[Singer Wang]

Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and
etc, you should be fine. We have been using the trio: flush tables with
read lock, xfs_freeze, snapshot for months now without any issues. And we
test the backups (we load the backup into a staging once a day, and dev
once a week)

S


S



On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.netwrote:



 Am 01.11.2012 16:36, schrieb Singer Wang:
  On Thu, Nov 1, 2012 at 11:34 AM, Rick James rja...@yahoo-inc.commailto:
 rja...@yahoo-inc.com wrote:
 
  Full backup:
  * Xtrabackup (Backup: slight impact on source; more if you have
 MyISAM (as mentioned))
  * Slave (Backup: zero impact on Master -- once replication is set up)
  * LVM -- a minute of server down; see below
 
  Why do you need downtime?

 because mysqld has many buffers in memory and there
 is no atomic flush buffers in daemon and freeze backend FS

 short ago there was a guy on this list which had to realize
 this the hard way with a corrupt slave taken from a snapshot

 that's why i would ALWAYS do master/slave what means ONE time
 down (rsync; stop master; rsync; start master) for a small
 timewindow and after that you can stop the slave, take a
 100% consistent backup of it's whole datadir and start
 the slave again which will do all transactions from the
 binarylog happened in the meantime

MySQL Thread Pool Plugin + Percona Server?

[Singer Wang]

Hey,

I'm looking to combine the benefits of the MySQL Thread Pool Plugin and
Percona-Server. All the benchmarks show that the Thread Pool Plugin handles
higher concurrency much better then just setting innodb_thread_concurrency.
Alas Percona server has many tweaks that improve performance.

Does anyone have any experience running Percona Server (obviously 5.5) with
the Thread Pool Plugin? I got it running but was wondering if there's any
caveats..

S

Re: Extract text from string

[Singer Wang]

Of course...

There's many libraries for REGEX which you can use, link it with an UDF and
boom!
http://dev.mysql.com/doc/refman/5.1/en/adding-udf.html

S



On Fri, Oct 12, 2012 at 12:56 PM, Neil Tompkins 
neil.tompk...@googlemail.com wrote:

 Hi,

 Is there such a way in a MySQL query to extract the text this is a test
 from the following strings as a example

 stronga href=http://www.domain.com/; class=linkthis is a
 test/a/strong
 stronga href=http://www.domain.com/; title=this is a test
 class=linklink/a/strong

 Thanks
 Neil

Re: user last activity and log in

[Singer Wang]

Mellow greetings,

Enhance your calm. Lets get our facts straight and not go off our
rockers. MySQL 5.6 Enterprise edition will be able to do this natively (
https://blogs.oracle.com/MySQL/entry/new_in_mysql_enterprise_edition), but
otherwise you cannot do it natively. This does not mean
its impossible, though as there's a few easy work arounds.

1) Force all logins to use the PAM or AD authentication plugin -- if the
authentication is success then log it in AD or PAM
2) use a init-connect to log logins but that doesn't work for users with
super privileges as Keith mentioned below (thanks Keith for actually trying
to help!)
3) Write your own plugin using the MySQL Plugin APIs
4) use the McAfee Audit Plugin for MySQL (Free:
http://www.devshed.com/c/a/MySQL/McAfee-Releases-Audit-Plugin-for-MySQL-Users-86548/
)


Singer Wang
(yes, I just watched Demolition Man)



On Thu, Oct 4, 2012 at 11:29 PM, Keith Murphy bmur...@paragon-cs.comwrote:

 My friend Dave Holoboff wrote this up some time ago:


 http://mysqlhints.blogspot.com/2011/01/how-to-log-user-connections-in-mysql.html

 You know you people sound like children.

 Really  unprofessional.

 Go ahead --- call me names. i left middle school almost 30 years ago. It
 won't bother me.

 Can we knock off the name calling and actually offer advice and possible
 solutions? I thought that was what this list was for.

 For those of us out in the field doing things ... This might be your
 ticket. It requires a restart of MySQL (which may or may not be acceptable)
 bit it's a fairly clean solution.

 Minimal load, easy to query for your last connection time and how often
 connections are made by a user.

 Again, requires a restart to enable (and disable) . Oh, and users with
 super privileges won't be logged.

 Thanks,

 Keith

 --

 Keith Murphy
 Senior MySQL DBA
 Principal Trainer
 Paragon Consulting Services
 http://www.paragon-cs.com
 850-637-3877







 --

 *
 *
 (c) 850-637-3877

Re: user last activity and log in

[Singer Wang]

My bad actually, the MySQL Enterprise Audit is available in MySQL 5.5

S

On Fri, Oct 5, 2012 at 2:14 AM, Singer Wang w...@singerwang.com wrote:

 Mellow greetings,

 Enhance your calm. Lets get our facts straight and not go off our
 rockers. MySQL 5.6 Enterprise edition will be able to do this natively (
 https://blogs.oracle.com/MySQL/entry/new_in_mysql_enterprise_edition),
 but otherwise you cannot do it natively. This does not mean
 its impossible, though as there's a few easy work arounds.

 1) Force all logins to use the PAM or AD authentication plugin -- if the
 authentication is success then log it in AD or PAM
 2) use a init-connect to log logins but that doesn't work for users with
 super privileges as Keith mentioned below (thanks Keith for actually trying
 to help!)
 3) Write your own plugin using the MySQL Plugin APIs
 4) use the McAfee Audit Plugin for MySQL (Free:
 http://www.devshed.com/c/a/MySQL/McAfee-Releases-Audit-Plugin-for-MySQL-Users-86548/
 )


 Singer Wang
 (yes, I just watched Demolition Man)



 On Thu, Oct 4, 2012 at 11:29 PM, Keith Murphy bmur...@paragon-cs.comwrote:

 My friend Dave Holoboff wrote this up some time ago:


 http://mysqlhints.blogspot.com/2011/01/how-to-log-user-connections-in-mysql.html

 You know you people sound like children.

 Really  unprofessional.

 Go ahead --- call me names. i left middle school almost 30 years ago. It
 won't bother me.

 Can we knock off the name calling and actually offer advice and possible
 solutions? I thought that was what this list was for.

 For those of us out in the field doing things ... This might be your
 ticket. It requires a restart of MySQL (which may or may not be
 acceptable)
 bit it's a fairly clean solution.

 Minimal load, easy to query for your last connection time and how often
 connections are made by a user.

 Again, requires a restart to enable (and disable) . Oh, and users with
 super privileges won't be logged.

 Thanks,

 Keith

 --

 Keith Murphy
 Senior MySQL DBA
 Principal Trainer
 Paragon Consulting Services
 http://www.paragon-cs.com
 850-637-3877







 --

 *
 *
 (c) 850-637-3877

Re: user last activity and log in

[Singer Wang]

It is possible in MySQL 5.6

S


On Thu, Oct 4, 2012 at 11:30 AM, List Man list@bluejeantime.com wrote:

 There is no such thing.  Your application has to deal with such info.

 LS


 On Oct 4, 2012, at 11:28 AM, Aastha wrote:

  Hello,
 
  I want to find the last time the given list of users logged in.
  Is there any mysql table from where i can retrieve the data or anyt
  specific sql
 
 
  Aastha Gupta


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

Re: Help with mysql connect_timeout

[Singer Wang]

Also check for any firewall or NAT
On Sep 5, 2012 5:17 PM, indrani gorti indrani.go...@gmail.com wrote:

 Hi all,
 I am a newbie to work with the settings in mysql although I have used in
 very small applications before
 I am currently experimenting with very large tables and a few complicated
 queries.  I am using mysql 5.5.  My tables are of size of abt 1 million
 tuples.  The queries upon these tables take more than 10 minutes and hence
 the connection timeout.
 I learnt that there are various timeouts .  I tried using
 SHOW VARIABLES LIKE '%TIMEOUT%';  and found that the connect-timeout is 10
 ( about 600.495 secs)

 Later I changed it to  SET GLOBAL connect_timeout=60;
 However, I still find that the query loses the connection to mysql server
 after about 10 mins( 600.495 secs)
 I see that the connect_time is 60 though.

 Can anyone please let me know whats wrong?
 Thanks in advance for the help.

 Regards,
 Indrani Gorti

Re: Relational Integrity

[Singer Wang]

RAM is Cheap, so is a faster processor.. (InnoDB requires more RAM/Processor then
the simpler MyISAM)..

but your data and downtime is probably a lot more expensive. Its well worth it
going with InnoDB. 

For most of what I do, I use a combination of InnoDB and HEAP Tables. 


On Tue, Jul 20, 2004 at 09:43:40AM +0200, Martijn Tonies ([EMAIL PROTECTED]) wrote:
 Hi Roy,
 
 If this is a business application, don't go without
 transactions and foreign keys. Plain and simple.
 
 Use InnoDB.
 
 With regards,
 
 Martijn Tonies
 Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
 Server.
 Upscene Productions
 http://www.upscene.com
 
 
 
  I need so general guidance on relational integrity.
  I'm setting up a reasonably small DB with 30 or so
  tables for a machine control application. Several of
  the tables will have referential links to each other
  (e.g. a finished part table will link to a master
  part type table via the product ID number).
  None of my table will ever contain more than a few
  hundred thousand records.
 
  This database is a conversion from an existing MS SQL7
  system in which I made extensive use of foreign keys.
  SQL7 has worked out well in the past but Windows and
  VBNet has ceased to be an efficient machine control
  development environment. We have decided
  to migrate to Linux on all of our new systems where
  practical.
 
  My first stab at a MySQL implementation is to use the
  MyISAM table structure and not the InnoDB structure,
  foregoing the use of explicit foreign keys and letting
  my apps take care of the relational integrity. I gathered
  from reading DuBois that this is not an uncommon approach
  to a MySQL implementation. Question: Are the advantages
  of MyISAM tables vs. InnoDB tables sufficient for me
  to continue this approach or am I better off setting
  up InnoDB tables throughout?
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
Singer X.J. Wang, Ph.D. Candidate
Faculty of Computer Science, Dalhousie University
6050 University Avenue, Halifax, NS, Canada, B3H 1W5
  Email:[EMAIL PROTECTED]
  Fax:  (902) 492-1517
  WWW:  http://www.singerwang.com
  Schedule: http://schedule.singerwang.com
---
  Tobor, its Robot spelled backwards.
 - Product slogan for a toy called Tobor (circa 1978)

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

join synatx

[Singer Wang]

hello,

reading the documentation I'm confused to as which is better:

SELECT * 
FROM T1,T2
where T1.K1 = T2.K2 AND T1.K2=T2.K2 AND ...otherconditions...

or should I do

SELECT *
FROM T1 NATURAL JOIN T2
WHERE ..other conditions..

or should I

SELECT *
FROM T1 JOIN T2 USING (K1,K2)
WHERE ..other conditions...

thanks,
Singer

-- 
Singer X.J. Wang, Ph.D. Candidate
Faculty of Computer Science, Dalhousie University
6050 University Avenue, Halifax, NS, Canada, B3H 1W5
  Email:[EMAIL PROTECTED]
  Fax:  (902) 492-1517
  WWW:  http://www.singerwang.com
  Schedule: http://schedule.singerwang.com
--
Your reading of this email is contributing to the eventual heat death of 
the universe.


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

AES_Encrypt

[Singer Wang]

Hello,

I'm tempted to use the AES_ENCRYPT/AES_DECRYPT function in MySQL; as
documentation says the implementation is 128-bit AES. Thus the keys
are 16bytes in length. Now the 'key_str' in the manual can be
any length; how is the key generated from that?

also, for multi block encryptions what mode is used? ECB? CBC? CFB? OFB?


-- 
Singer X.J. Wang, Ph.D. Candidate
Faculty of Computer Science, Dalhousie University
6050 University Avenue, Halifax, NS, Canada, B3H 1W5
  Email:[EMAIL PROTECTED]
  Fax:  (902) 492-1517
  WWW:  http://www.singerwang.com
  Schedule: http://schedule.singerwang.com
---
  One word sums up probably the responsibility of any vice president,
  and that one word is 'to be prepared'.
 - Dan Quayle

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