Re: How to get the MySQL Command-Line Tool to display Unicode properly?

[Johan De Meersman]

Probably the dumbest suggestion yet, but have you tried "set names utf8" in the 
client?

On 20 March 2018 20:50:08 CET, Roger House  wrote:
>
>
>On 03/15/2018 02:06 PM, Roger House wrote:
>>
>>
>> On 03/15/2018 11:30 AM, shawn l.green wrote:
>>> Hi Roger,
>>>
>>> (please note, this is a bottom-post forum)
>>>
>>> On 3/13/2018 7:54 PM, Roger House wrote:
>>> >
>>> > On 03/13/2018 03:11 PM, Reindl Harald wrote:
>>> >>
>>> >>
>>> >> Am 13.03.2018 um 22:59 schrieb Roger House:
>>> >>> In all respects except one, the treatment of Unicode works just 
>>> fine.
>>> >>> I can write Unicode to database tables, read it, display it,
>etc.,
>>> >>> with no problems. The exception is mysql, the MySQL Command-Line
>>> >>> Tool. When I execute a SELECT statement to see rows in a table
>>> >>> containing the Venus and Mars Unicode characters, here is what I
>see
>>> >>> on the screen:
>>> >>>
>>> >>> || Venus | ♀ | | Mars | ♂ | |
>>> >>>
>>> >>> What I should see in the right column are the standard glyphs
>for
>>> >>> Venus and Mars.
>>> >>>
>>> >>> Any ideas about how to get the MySQL Command-Line Tool to
>display
>>> >>> Unicode properly?
>>> >> what operating system
>>> >> what terminal
>>> >>
>>> >> all recent Linux systems have UTF8 as default
>>> >>
>>> >
>>> >
 I am running Ubuntu MATE 16.04.  I have the problem also on Windows
>7
 and on Mac OS Version 10.11.6.  I do not think that the problem has
>to
 do with the operating system nor the terminal.  Everything about
>the
 Unicode text works fine in all tools such as editors, the cat
>command,
 etc.  It is only when I am running mysql and I issue a SELECT 
 command to
 see what is in a row.  Then the UTF-8 is not rendered properly. I
 believe the problem is with mysql.

 Roger

>>>
>>> If I presume that your terminal has a code page that is 
>>> utf8-compatible (you say that cat command renders the multibyte 
>>> characters just fine) then it could be your client-side mysql 
>>> settings that are rendering those multibyte characters into 
>>> individual glyphs based on their individual byte values.
>>>
>>> The next time you are in mysql and have a chance to look at some
>utf8 
>>> data, please collect and share these two reports:
>>>
>>> status
>>> SHOW GLOBAL VARIABLES like '%haracter%';
>>>
>>> (you can obfuscate any sensitive details like server names or
>addresses)
>>>
>>> Yours,
>> Here is the requested info:
>>
>> mysql> status
>> --
>> mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine 
>> wrapper
>>
>> Connection id:        5
>> Current database:    ephemeris
>> Current user:        root@localhost
>> SSL:            Not in use
>> Current pager:        stdout
>> Using outfile:        ''
>> Using delimiter:    ;
>> Server version:        5.7.21-0ubuntu0.16.04.1 (Ubuntu)
>> Protocol version:    10
>> Connection:        Localhost via UNIX socket
>> Server characterset:    utf8mb4
>> Db characterset:    latin1
>> Client characterset:    utf8mb4
>> Conn.  characterset:    utf8mb4
>> UNIX socket:        /var/run/mysqld/mysqld.sock
>> Uptime:            6 hours 17 min 8 sec
>>
>> Threads: 1  Questions: 28  Slow queries: 0  Opens: 122 Flush tables: 
>> 1  Open tables: 41  Queries per second avg: 0.001
>> --
>>
>> mysql> show global variables like '%haracter%';
>> +--++
>> | Variable_name    | Value  |
>> +--++
>> | character_set_client | utf8mb4    |
>> | character_set_connection | utf8mb4    |
>> | character_set_database   | utf8mb4    |
>> | character_set_filesystem | binary |
>> | character_set_results    | utf8mb4    |
>> | character_set_server | utf8mb4    |
>> | character_set_system | utf8   |
>> | character_sets_dir   | /usr/share/mysql/charsets/ |
>> +--++
>> 8 rows in set (0.01 sec)
>>
>>
>Update
>
>I noticed that the status command shows
>
>     Db characterset:    latin1
>
>whereas all the other charactersets are utf8mb4.  So I looked around to
>see
>how to change the Db characterset, and came up with this:
>
>     ALTER DATABASE ephemeris CHARACTER SET utf8mb4 COLLATE 
>utf8mb4_general_ci;
>
>After which, status shows
>
>     Db characterset:    utf8mb4
>
>So the database characterset has now been changed.  But
>
>     select * from planet_desc;
>
>still shows
>
>     Venus | ♀
>     Mars  | ♂
>
>I shut down the mysql service and restarted it, but this did not change
>anything.
>
>I'm beginning to think that the command line tool mysql does not pay
>any
>attention to character sets when the SELECT command displays on the
>terminal.
>
>Roger

-- 
Sent from my Android device with K-9 Mail. Please excuse my 

Re: Examples of savepoints and transactions

[Johan De Meersman]

What you're looking for is simple backup and restore :-)

Savepoints are, simply put, markers within a transaction; allowing you to 
rollback only part of a transaction instead of the whole thing. A commit will 
inevitably commit the ENTIRE transactions, and thus remove the savepoints.

A typical workflow for the kind of thing you're trying to do is to have your 
(automated) testing framework restore last night's backup after the test run. 
You could also make a backup before the test run and restore that afterwards; 
have an automated nightly db copy from prod to dev; or in very specific cases 
you could simply have your test system revert the data by issuing the "reverse" 
queries - although that one is rarely an option in real life.

Another alternative would be to take a filesystem (or virtual machine) 
snapshot, and revert to that after the tests. Filesystem snapshots will require 
your database to be stopped and started, though.

/Johan


- Original Message -
> From: "Lars Nielsen" 
> To: "MySql" 
> Sent: Tuesday, 23 January, 2018 23:19:29
> Subject: Re: Examples of savepoints and transactions

> Den 22-01-2018 kl. 22:01 skrev shawn l.green:
>> Hello Lars,
>>
>> On 1/21/2018 3:37 PM, Lars Nielsen wrote:
>>> Hi,
>>> I have a system that uses begin and commit transactions. It works
>>> like a dream! ;)
>>> Now I want to test it by creating test data. This how ever cannot be
>>> rolled back. I think the solution for rolling back test data is to
>>> use savepoints and rollback. I think it is hard to find examples of
>>> this scenario. Are there some good guides or tutorials out there
>>> somewhere? Any suggestions are welcome.
>>>
>>> Best regards
>>> Lars Nielsen
>>>
>>
>> Can you mock up an example (a simple text walkthrough) of how you
>> think a savepoint should work with what you are calling "test data" ?
>> I think that the term "test data" is too general to make much sense to
>> most of us in the context you described.
>>
>>
>> Yours,
> Hello Shawn,
> Thanks for your interest. Here is an example of my idea.
> 
> I have a php site working through PDO connections. I insert some data
> through php like this :
> 
>|START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
> UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x,
> y, z); COMMIT; |||
> 
>||Now I want to do automated tests that create "dummy" data that i want
> to remove after the test has finished:
> like this :
> 
>|SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM
> table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT
> INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL
> OPERATIONS ROLLBACK TO autotest1; |||
> 
>||All done. I have tested the application and have cleaned up the dummy
> test-data.
> 
> The issue is that when I call the first commit then the savepoint is
> deleted.
> 
> Is this possible at all?
> 
> Regards Lars
> 
>||
> 

-- 
The bay-trees in our country are all wither'd
And meteors fright the fixed stars of heaven;
The pale-faced moon looks bloody on the earth
And lean-look'd prophets whisper fearful change.
These signs forerun the death or fall of kings.
  -- Wm. Shakespeare, "Richard II"

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

Re: mysqld_multi

[Johan De Meersman]

He means a database, not a schema. Instance would have been clearer as 
terminology goes, admittedly, but in a MySQL context the two are identical.
-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

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

Re: Can not add foreign key constraint

[Johan De Meersman]

That is quite different, as I suspected :-)

Referential keys require an index on the target table that begins with the 
referenced field, so you'll need to add one on user, as was specified in the 
create table you originally posted.

On 28 April 2017 01:21:39 CEST, David Mehler <dave.meh...@gmail.com> wrote:
>Hello,
>
> Here's the output of the command show create table virtual_users:
>
>  create table virtual_users;
>+---+-+
> | Table | Create Table
>
>
>
>
>
>
>  |
>+---+-+
> | virtual_users | CREATE TABLE `virtual_users` (
>   `id` int(11) NOT NULL AUTO_INCREMENT,
>   `domain_id` int(11) NOT NULL,
>   `user` varchar(40) NOT NULL,
>   `password` varchar(128) NOT NULL,
>   `quota` bigint(20) NOT NULL DEFAULT '0',
>   `quota_messages` int(11) NOT NULL DEFAULT '0',
>   PRIMARY KEY (`id`),
>   UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`),
>   CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`)
> REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
> ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
>+---+-----+
> 1 row in set (0.00 sec)
>
>Thanks.
>Dave.
>
>>
>>
>> On 4/26/17, Johan De Meersman <vegiv...@tuxera.be> wrote:
>>>
>>> I note that the innodb status says it couldn't find an index on the
>>> referenced column. Did the create statements come from your create
>>> scripts
>>> or from a show create table statement? I'm suspicious about the
>index on
>>> virtual_users(user).
>>>
>>>
>>> - Original Message -
>>>> From: "David Mehler" <dave.meh...@gmail.com>
>>>> To: "MySql" <mysql@lists.mysql.com>
>>>> Sent: Tuesday, 25 April, 2017 23:07:19
>>>> Subject: Re: Can not add foreign key constraint
>>>
>>>> Hello,
>>>>
>>>> Tried recreating the virtual_users table didn't solve anything.
>Would
>>>> it be possible for anyone to check out my box directly?
>>>>
>>>> Thanks.
>>>> Dave.
>>>
>>> --
>>> The bay-trees in our country are all wither'd
>>> And meteors fright the fixed stars of heaven;
>>> The pale-faced moon looks bloody on the earth
>>> And lean-look'd prophets whisper fearful change.
>>> These signs forerun the death or fall of kings.
>>>   -- Wm. Shakespeare, "Richard II"
>>>
>>
>> Thanks.
>> Dave.
>>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/mysql

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

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

Re: kill query and prepared statements

[Johan De Meersman]

Seb,

You should log a bug at http://bugs.mysql.com - this is not a developer list.

/Johan

- Original Message -
> From: "Sebastien FLAESCH" 
> To: "MySql" 
> Sent: Tuesday, 10 January, 2017 14:55:42
> Subject: kill query and prepared statements

> Hi all,
> 
> I have reported this problem before, but I raise it again, since I still get
> this problem with 5.7.17
> 
> See attached code:
> 
> I want to interrupt a long running statement with CTRL-C by starting a new
> connect to make a KILL QUERY.
> 
> I am using the same technique as the mysql client code.
> 
> The difference here is that my code is using PREPARED STATEMENTS with
> mysql_stmt_prepare() etc.
> 
> Problem: After interrupting the first query with CTRL-C, the call to
> mysql_stmt_close() hangs...
> 
> Maybe I am missing some new connection or statement option...?!?
> 
> IMPORTANT: This problem appeared in a 5.7.x, same code is working fine with
> 5.6(.16) 
> 
> Please can someone from MySQL C API team try to reproduce and confirm?
> 
> Thanks!
> Seb
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql

-- 
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: audit trails

[Johan De Meersman]

Triggers are not the best way to go about this. Consider:

 * What is to stop a malicious user from truncating the audit table?
 * Triggers may fail (corrupt target table, for instance) and a trigger failure 
may cancel the source statement
 * Triggers have a performance impact - you're basically doubling every DML 
action.
 * Triggers get executed sequentially for multi-inserts, slowing the whole 
operation down

I suggest having a look at one of the available audit plugins.

 * Percona has one that can iirc also be compiled against the standard (oracle) 
community edition
 * Oracle have one too in newer versions (I think from 5.7?) but it's 
enterprise licensed
 * MariaDB has one in the community version, but only works against MariaDB 
server
 * McAfee also had one, but I'm unsure about it's current status

The benefit of a plugin is that the code runs out of the "userspace", has lower 
performance impact (parallelism) and cannot cause originating statements to 
fail. Additionally, I would assume that some of these, if not all, can also log 
towards an external target (file, network, ...).


/Johan

- Original Message -
> From: "Sándor Halász" 
> To: "MySql" 
> Sent: Wednesday, 7 December, 2016 14:56:55
> Subject: Re: audit trails

> 2016/12/07 01:26 ... mach...@seworx.co.za:
>>   well in essence the following is required.
>>
>> we need to know who made what changes to tables.
> 
> There is a machination that you can try in every trigger that will add
> the user-name to the binary log:
> 
> set @asdfasdfasd = CURRENT_USER();
> INSERT INTO T VALUE ( ... @asdfasdfasd, UNIX_TIMESTAMP() ... );
> 
> The value assigned the variable @asdfasdfasd, since it is used to change
> a table, will show up in the binary log. The function "UNIX_TIMESTAMP"
> yields a number that matches TIMESTAMP in the binary log.
> 
>>   we recently had a case of important data being deleted, however
>> finding it i binary logs proved 2 things :
>>
>> 1. it takes very long to find as we did not know in which file the
>> details were.
> 
> You did not know where the binary log was saved? That is set by you in
> the global variables "log_bin_basename" and "log_bin_index".
> 
>> I have managed to figure that part out almost fully and have one or two
>> more kinks to work out. We will be adding an error in the trigger for
>> deletes, however it should still log the delete attempts to audit table
>> and this is where I am stuck now.
>>
>> I hit the error, however the attempt to delete is not being logged to
>> the audit table.
> 
> Only if the DELETE looks valid is the BEFORE DELETE trigger triggered.
> If the deletion would yield inconsistency according to the constraints
> that you set up and MySQL supports then the deletion is aborted and
> rolled back ere AFTER DELETE trigger is triggered.
> 
>> The problen however is now that they would like to know what query was
>> run. i.e. was it a straight query or was it run by calling some
>> procedure. I am however not sure if this will even be something that can
>> be logged.
> 
> I use
> binlog_format=STATEMENT
> ; then the transaction is logged--but MySQL Cluster does not support this.
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql

-- 
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: Update to Percona CVE-2016-6662 Vulnerability Communication

[Johan De Meersman]

- Original Message -
> From: "Reindl Harald" 
> Sent: Friday, 16 September, 2016 00:12:26
> 
> frankly - mysqld_safe needs to go away and life is beautiful without for
> years here and yes taht worked for mysql too before switch to MariaDB
> 
> to say it clear: running *any* code as root for a service binding to a
> port above 1024 is idiotic by definition

I agree, and I'm happy to say that the release notes for 8.0 mention that it 
has finally gone away. That doesn't change the fact, however, that there are 
countless installations out there that use it, which is whom I was targeting 
with that mail :-)

Is that service description you pasted the one that comes with the MariaDB 
package, or did you roll it yourself ?


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

Fwd: Update to Percona CVE-2016-6662 Vulnerability Communication

[Johan De Meersman]

This is probably of interest to many of you, and I've not seen it on the list 
yet.

Kenny Gryp's blog about the vulnerability is at 
https://www.percona.com/blog/2016/09/12/database-affected-cve-2016-6662/ . 

For those who use it, there's an ansible playbook to patch the workaround into 
mysqld_safe at https://github.com/meersjo/ansible-mysql-cve-2016-6662 .

/Johan

- Forwarded Message -
From: "Percona" 
To: perc...@tuxera.be
Sent: Wednesday, 14 September, 2016 00:42:18
Subject: Update to Percona CVE-2016-6662 Vulnerability Communication


Earlier yesterday, via blog post and email, we alerted people to CVE-2016-6662 
. 

This vulnerability meant that certain Percona Server (and MySQL) scenarios 
could allow a remote root code execution. 

As of late 9/12, we added a new blog post that explains the vulnerability, if 
it affects you, how to prevent the vulnerability from affecting older versions 
of MySQL and which versions of Percona Server have been updated with a fix: 

Is Your Database Affected by CVE-2016-6662? 


Click through to that blog for more information on how to protect your 
environment from CVE-2016-6662. 

We will release an update to Percona XtraDB Cluster versions 5.5 and 5.6 with a 
fix for CVE-2016-6662 next week. In the meantime, the steps outlined in the 
“Configuration files permissions” section in the post above should protect your 
servers from the vulnerability. 

Contact us if you need more information or assistance . 

If you are a customer, please contact support via normal support channels . 

Thank You, 
Support 












Percona 8081 Arco Corporate Drive Suite 170 Raleigh, NC 27617 United States 

You received this email because you are subscribed to Tell Me Everything! from 
Percona. 

Update your email preferences to choose the types of emails you receive. 

Unsubscribe from all future emails 




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

[Johan De Meersman]

Right... if even a fraction of that is true (I have no reason to doubt you, but 
I can also not verify any of it, obviously), that is imo certainly something to 
take up with a list moderator (Shawn ?). That kind of behaviour is unacceptable.

- Original Message -
> From: "Reindl Harald" <h.rei...@thelounge.net>
> To: "MySql" <mysql@lists.mysql.com>
> Sent: Tuesday, 13 September, 2016 13:39:59
> Subject: Re: MySQL Platform Migration

> Am 13.09.2016 um 12:13 schrieb Johan De Meersman:
>> - Original Message -
>>> From: "Ryan Coleman" <ryan.cole...@cwis.biz>
>>> Subject: Re: MySQL Platform Migration
>>>
>>> Because they want to be belittled by european jackasses online.
>>
>> The thing is, while he has a bit of a rough edge, his technical advice is 
>> always
>> solid. What has your contribution been, outside of insulting him, and for 
>> some
>> reason, an entire continent?
> 
> this guy is just mentally ill and the next steps are:
> 
> * starting off-list mails (already happened)
> * can't stand the repsone
> * add a list-moderator to CC
> * setup something in his MUA which send all read messages back
>   to me (includig old ones from other lists he reads again)
> * can't stand the response to that action
> * forwards that responses to my boss
> * after having enough of the game and reject his
>   envelope asking for list-removal by pretending
>   that my server sends bounces to all his list mails while
>   in fact it only rejects private email
> 
> https://mail-archives.apache.org/mod_mbox/spamassassin-users/201608.mbox/%3cbb0c1584-f56a-4c1e-8b58-0d4b7d9a8...@cwis.biz%3E
> 
> 
> the reason for that personal hate (statet on a different list) is that
> repsone from last year: https://marc.info/?l=mysql=144526386203911=4
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql

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

[Johan De Meersman]

- Original Message -
> From: "Reindl Harald" 
> Subject: Re: MySQL Platform Migration
> 
> we discussed this multiple times here

True; but new people on the list may not have seen that.


> when you *copy* the datadir on the target machine nobody eats your data
> on the old one away and hence you can try out how it behaves on the new
> machine without any risk

Again, true; but I can well imagine scenarios with relatively huge datadirs and 
very stringent uptime requirements, so you only get the one window to take the 
system down and cold-copy the datafiles. And, yes, that's what slaves are for, 
but if there is no existing slave you run into the same problem again.

Circumstances vary, and asking doesn't hurt on most mailing lists :-p


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

[Johan De Meersman]

- Original Message -
> From: "Matthias Schmidt" 
> Subject: Re: MySQL Platform Migration
> 
> I had to make certain adjustements to the config file thou.

Interesting - what changes did you have to make? I would expect most things to 
just keep working as they are, unless you were using something like the 
Federated engine that got disabled by default quite a while ago.


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

[Johan De Meersman]

- Original Message -
> From: "Ryan Coleman" 
> Subject: Re: MySQL Platform Migration
> 
> Because they want to be belittled by european jackasses online.

The thing is, while he has a bit of a rough edge, his technical advice is 
always solid. What has your contribution been, outside of insulting him, and 
for some reason, an entire continent?


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

[Johan De Meersman]

- Original Message -
> From: "Reindl Harald" 
> Subject: Re: MySQL Platform Migration
> 
> why shouldn't it when the identical software is running?
> it's just a bunch of files used by mysql

Little/big endianness, for one, although I seem to recall, and your later mail 
confirms, that that is not an issue for MySQL datafiles - and Mac has gone 
Intel years ago anyway.

Some software, but again, not MySQL, also writes datafiles differently 
depending on the architecture they're on. Had shenanigans some years ago simply 
moving (non-mysql) datafiles between 32- and 64-bit platforms.

There's no issue in this case; but it's not always as simple as 'same software, 
same binary files'.


One thing that is worth looking at, however, is users with the IP/hostname of 
the old host in them - those may need to be changed if the related software is 
also moved.

Also, some Linux distributions do specific setup - Debian, for one, sets up a 
debian-sys-maint user that gets used for clean startup/shutdown/upgrade tasks. 
Both copying over the datafiles and importing a mysqldump from the old db will 
destroy that user entry, so it needs to be recreated manually afterwards. Other 
distributions may or may not have their own quirks.


> as said copy it to the new machine and you are done - why do people
> these days not just try out things, look if it works and when it don't
> asking questions?

Eh. Sometimes it's comforting to get someone else's opinion before messing with 
production systems you're not an expert on.



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

[Johan De Meersman]

- Original Message -
> From: "Machiel Richards" 
> Subject: Re: MySql Swapping issues
> 
> When I monitor the MySQL processlists, there is constantly about 30-35 
> processes
> and half of them never change simply showing as :
> 
> Connect | 124678 | Waiting for an event from Coordinator

15-ish? That feels like a lot, but then I'm not particularly familiar with 
parallel replication. Might be normal for your configuration.


> The other half of the processes are queries and inserts and I can see those
> being processed and going off the list fairly regularly.

So those disconnect normally, which /should/ clean up any session memory.

I'm pretty much out of obvious things to point at. Random toughts:
 - typo in some dynamically-assigned buffer sizing (not a lot of those, though)
 - I have in the past seen memory use increase at times when the logs couldn't 
get flushed to disk; but I assume something like that would become apparent at 
restart time anyway
 - A memory leak of that size seems improbable, but it's always worth checking 
the bug reports for your server version or test other versions

Maybe someone else has some more ideas, or has seen similar behaviour?

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

[Johan De Meersman]

- Original Message -
> From: "Machiel Richards" 
> Subject: MySql Swapping issues
> 
>I had a look at the settings and the innodb buffer pool on one server
> is set to about 70% of the physical memory and the others to about 30% of
> physical memory.

Not unreasonable, especially given the memory sizes you give.


>All other buffer and memory relevant parameters are set to fairly low
> values as they were recently decreased with no difference in the symptoms.

Good, although 'fairly low' is vague :-) For most purposes, there's no need to 
tune the specialised buffers at all. I assume you mean myisam key cache and the 
like.


> In terms of server and queries, The smallest server have 64Gb of
> physical memory and the biggest server have 128Gb of physical memory and
> the biggest server database size is about 600Gb odd.

That's a large difference,but not necessarily a problem, as long as your active 
dataset fits in the bufferpool.


> I had a look at the processes running and there are at best 38
> processes running including the replication processes.

Is that what you see whenever you look, or is it from a trending tool like 
Munin or Cacti?

The former can be very deceiving, especially with connect-select-quit 
applications like PHP sites. I strongly recommend setting up proper trending if 
you don't have it, so you can see what's going on when you're not looking, too 
- and compare to past activity.

Personally I use Munin; the standard plugins in there are a good base, but 
there's a very good one at https://github.com/kjellm/munin-mysql. I have my own 
fork of that, too, which contains a number of extra graphs that depend on 
considerable modifications of the main module.

Cacti is pretty much just as good (and iirc the kjellm plugin is actually based 
on a cacti plugin); I just prefer the way munin is managed.


>I do not see any other hardware related issues and swappiness settings
> have been configured to 1.

For DB-only servers (and really, production servers in general) I generally opt 
to not have any swap at all. Once you start using it, it's a slow death 
struggle anyway; better off to just have it die immediately and fix the 
configuration.


>Any ideas , links, advice, etc... will be appreciated.

Memory creep is often hard to diagnose; set up a simple cronjob that runs PS; 
sorts by memory use and outputs the top lines to a log every half hour or so. 
You can then do some sed/awk/gnuplot magic on that to see what process keeps 
growing.

If it turns out that it actually *is* the mysql server, that may be a memory 
leak, but just as probably it could be a maintenance schedule somewhere that 
suddenly bursts a couple of dozen connections, exhausting server memory. Pretty 
hard to tell you more without telemetry :-)


-- 
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: filename-safe conversion of database-/tablenames

[Johan De Meersman]

- Original Message -
> From: "Simon Fromme" 
> Subject: filename-safe conversion of database-/tablenames
> 
> I need to convert both the names of databases and tables in a
> filename-safe way (escaping "/" and other characters as in [1]). The

I don't know what strange table names you're expecting, but under *nix almost 
anything short of / (directory separator) is valid in a filename, even the 
wildcard characters ? and *.


-- 
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: find any row with NULL

[Johan De Meersman]

- Original Message -
> From: "lejeczek" 
> Subject: Re: find any row with NULL

> ok, whereas that fist example was about all (and find
> columns' names without knowing) columns - would there be a
> simple(r) syntax to find that (not)NULLs in a list of given
> column names? Or it has to be multiple ORs ?

Alas, no. If you're handling NULLvalues, all you have is  IS NULL and 
 IS NOT NULL. No other operators work.

-- 
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: find any row with NULL

[Johan De Meersman]

- Original Message -
> From: "lejeczek" 
> Subject: Re: find any row with NULL
> 
> shame NULL won't work as in:
> > select user_id,completetion_time from depression where
> NULL in(email_me, other_diagnosis);
> or does it?

No, because NULL != NULL - it is why there are dedicated operators.

What could work, is that you alter your table to [field NOT NULL DEFAULT ''] - 
that should transform all the NULL values for field to empty strings.

Not that reverse-in is horribly ineffective as it uses no indices at all; but 
it might work for you in this case provided you have no empty-stringed fields 
you need to preserve. If you do, just use a custom impossible value like 
'deleteme'.


-- 
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: check_mysql_health poll

[Johan De Meersman]

Useful knowledge, thank you, Shawn. Good to see confirmed that qcache_hits + 
com_select is the global total - that's not always very clear in the docs.

I just noticed that when I copied the list for extra exposure, I didn't 
actually say what I was exposing :-p

Gerhard Laußer, who maintains the check_mysql_health nagios plug-in, is 
currently re-factoring the code and has a poll open about whether to change the 
logic behind the query cache hit rate, and to what. The poll (and check) are at 
https://labs.consol.de/nagios/check_mysql_health/ .

/Johan

- Original Message -
> From: "Shawn Green" 
> To: "MySql" 
> Sent: Friday, 15 July, 2016 18:30:39
> Subject: Re: check_mysql_health poll

> Excellent advice.
> 
> If you read through the code, you will find that every SELECT command
> will either hit the query cache (incrementing Qcache_hits) or require
> execution to evaluate (incrementing Com_select).  So for an average of
> your Query Cache efficiency since the last restart (or the last
> statistics reset) use this formula
> 
> Efficiency in % = (Qcache_hits)/(Qcache_hits + Com_select) * 100
> 
> To get an average efficiency over a span of time, execute a SHOW GLOBAL
> STATUS report at the start of the span and another at the end of the
> span then compute that formula comparing the changes in those counters
> (the deltas).
> 
> Another way to look at reuse rate is to estimate how quickly you are
> turning over the content of the Query Cache.  Let's say your
> Qcache_inserts rate is about 500/sec and on average you have about 5000
> queries in the cache.  This gives you a very rough lifetime of about 10
> seconds for any single query result in the cache.  If you are not seeing
> a lot of lowmem prunes during this period, then those existing query
> results are not being forced out of the cache due to space restrictions
> (age), they are most likely being removed automatically due to changes
> happening to the tables they are based on.
> 
> In most cases, you gain efficiency by removing the mutex that protects
> the content of the Query Cache and allowing all incoming commands to
> execute in parallel rather than being serialized via that cache mutex.
> You do this by setting --query-cache-type=0 (or OFF) not just by
> allocating no space to the buffer. This is particularly true if you
> * have a low reuse rate
> * have a high churn rate
> * do not have a large population of queries that are repeated (exactly)
> against sets of tables that change rarely.
> 
> --
> 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

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

check_mysql_health poll

[Johan De Meersman]

Hey, 

I just happened upon your poll, so I'm sending you brief mail because I have a 
different opinion still :-) I'm also CCing the MySQL list, as I feel that more 
input on this might be a good thing - and it's worth some exposure anyway. 

I believe there are two distinct measures that can be taken: 
* Ratio of selects that were returned from the cache against total server 
queries (caching ratio) 
* Ratio of selects that were served from cache against selects that were 
inserted into the cache (statement reuse ratio) 

The former gives an indication of how many queries were served from the cache 
against the total number of questions asked. It's a useful measure to see 
wether it's worth the effort to see if there's ways to rewrite queries or code 
so that more queries become cacheable. Given that 
https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Com_xxx
 explicitly states that queries served from cache do NOT increment com_select, 
I believe that calculation requires qcache_hits, qcache_inserts AND com_select. 
I'm not clear on wether qcache_not_cached augments com_select, though I would 
suspect it does. Even if this ratio is relatively low, it's not necessarily a 
problem - every query served from cache is a parse/exec saved. On multitenancy 
you could have a database that benefits hugely from the cache, and ten others 
that hardly use it, and that is not a problem as such. 

The latter, on the other hand, tells you how many of the queries that were 
inserted into the cache, are actually served from cache afterwards. This 
requires only qcache_hits and qcache_inserts; but it is a very good measure of 
wether your query cache is actually providing any benefit - THIS is the ratio 
that should be high - if it's close to 1, it may mean you spend more time 
inserting and clearing than you save by the occasional cache hit. 


So, my suggestion would be to certainly use the latter option for the 
check_mysql_health check; but it may be useful in some scenarios to have a 
separate check for the former, too. 

/johan 

-- 
What's tiny and yellow and very, very dangerous? 
A canary with the root password. 

Re: find any row with NULL

[Johan De Meersman]

- Original Message -
> From: "Sándor Halász" 
> Subject: Re: find any row with NULL
> 
> from information_schema.columns where (table_schema, table_name,

You could, but information_schema queries can get pretty slow on large 
databases, especially so with InnoDB. Recent versions support explain on those 
queries, too, I believe.

-- 
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: find any row with NULL

[Johan De Meersman]

You will have to repeat all the column names - no wildcards in where clause 
fieldnames - but the clause you're looking for is WHERE field IS NULL. Or IS 
NOT NULL if you want those :-)

Remember, NULL is a special value that is not the same as zero or the empty 
string; nor to itself: NULL != NULL, by definition. Personally, I mostly 
discourage the use of it (use DEFAULT VALUE in your table definition wherever 
possible) except in circumstances where it really is necessary to know the 
difference between 'nothing here' and 'we have no information about this at 
all'.

As a clear example of what it is useful for, imagine a situation where you're 
performing an inventory on an existing warehouse. You have the list of all the 
products they've ever sold, but you need to differentiate between 'this product 
is not in stock' (count = 0) and 'I have not counted this product yet' (count 
IS NULL).

/Johan

- Original Message -
> From: "lejeczek" 
> To: "MySql" 
> Sent: Friday, 8 July, 2016 14:27:45
> Subject: find any row with NULL

> hi there,
> 
> I've been searching the vastness of the net but cannot find
> - how - to get all the rows with a NULL.
> 
> And like any novice I wonder if this can be done without
> reiterating all the columns names(not manually at least)?
> 
> Some expert would say it is easy, how easy is it? with an
> example?
> 
> many thanks
> 
> L.
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql

-- 
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: call the appropriate and correct database

[Johan De Meersman]

- Original Message -
> From: "HaidarPesebe" 
> Subject: call the appropriate and correct database

> How do I call first database table as follows :
> 
> id | country | province | distric | cost
> 
> 1 | USA | Alanama | distrik | 20
> 2 | USA | Alabama | distrik2 | 22
> 3 | USA | Alabama | distrik3 | 22
> 4 | France | Paris | disrik4 | 30

You want to normalize your data by splitting that out into separate tables for 
country, province and district, and referencing the higher level instead. You'd 
get something like this:

| COUNTRIES |
| c_id | name   |
|1 | USA|
|2 | France |

| PROVINCES |
| p_id | c_id | name|
|1 |1 | Alabama |
|2 |1 | Washington  |
|3 |2 | Paris   |
|4 |2 | Nord-Calais |

| DISTRICTS  |
| d_id | p_id | name |
|1 |1 | distrik  |
|2 |1 | distrik2 |
|3 |1 | distrik3 |
|4 |3 | distrik4 |


That way, you can fill your dropdowns by a simple, fast select statement. 
Whenever you need more complex bits, you can just join the tables as necessary.

Google for "database normalisation" for more info about this practice, and find 
information about "foreign keys constraints" to ensure consistency in your 
database.



-- 
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: slave to master

[Johan De Meersman]

> From: "Mahmoud Alshinhab" 
> Subject: Re: slave to master

> I think you should have a look at MariaDB Connector[1].

> It provides Load balancing and failover as Failover occurs when a connection 
> to
> a primary database server fails and the connector will open up a connection to
> another database server.

Hmm, I didn't know that they built that into it, interesting. Does it require 
server features, or would it work with any mysql-compatible protocol ? 

> Load balancing allows load (read and write) to be distributed over multiple
> servers.

Is read-write splitting also built-in, then? 

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

Re: slave to master

[Johan De Meersman]

- Original Message -
> From: "Jason Mallory" 
> Subject: RE: slave to master
>
> Master-master with load balancer would be best

That's a bit brief, isn't it? :-)

It's more than worth pointing out that your loadbalancer should not actually be 
loadbalancing the connections; master-master replication doesn't quite work the 
way you think it does in most scenarios.

You only want the loadbalancer for the automated failover; but it should never 
send requests to more than one master at any given time. Have it send 
everything to your primary master only; and when that host fails, have it send 
everything to the secondary master only, and never fail back automatically.

It's also worth noting that master-master is still not an officially supported 
replication topology. Regular master-slave also works fine with the above 
loadbalancer configuration; in that case you'll just treat the slave as the new 
primary after failover; and will manually reconfigure the broken master to be a 
slave (and adapt the loadbalancer config accordingly) as repair.

There does exist software that can do those reconfigurations by itself, MMM is 
one such example.

/Johan

-- 
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: parallel installations of mysql

[Johan De Meersman]

That works nicely, you just need to make sure that you set up the second 
instance on a different port, with different data and log directories etc.

Do you expect many issues from the upgrade? In most cases, an in-place upgrade 
should work the same or better than the old version :-)


- Original Message -
> From: "Martin Mueller" 
> To: "MySql" 
> Sent: Wednesday, 20 April, 2016 20:04:57
> Subject: parallel installations of mysql

> I am running MySQL 5.6.22 on an iMac as a desktop database. I would like to
> install 5.7.12. Can I install it as a parallel and independent  instance? And
> if so, are there special problems to watch out for?
> 
> 
> 
> 
> 
> Why would I want to do this? Well, I have a set of databases and tables on the
> old installations that have grown over the years. Given the way I work, the
> simplest thing would be install the new database and then work through my
> existing tables over a number of weeks and transfer stuff as I go along.   
> That
> may not be very professional but it works for me, and it would let me keep the
> old along the new, just in case something goes wrong/
> 
> My friends tell me to use sqlite, and they are probably right since file
> management is so much simpler. But I find the many builtin functions of MySQL
> very helpful and don't particularly want to learn a new set.
> 
> Martin Mueller
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql

-- 
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: dump, drop database then merge/aggregate

[Johan De Meersman]

- Original Message -
> From: "lejeczek" 
> Subject: Re: dump, drop database then merge/aggregate
> 
> today both databases are mirrored/identical
> tonight awkward end will dump then remove all the data, then
> collect some and again, dump then remove
> and these dumps should reconstruct the database on the other
> box.

It sounds like a horrible mess, to be honest. It's also pretty hard to 
recommend possible paths without knowing what's inside. Is it an option for you 
to simply import the distinct dumps into different schemas? That way there 
would be no need for merging the data, you just query the particular dataset 
you're interested in.


-- 
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: my.cnf authencication

[Johan De Meersman]

- Original Message -
> From: "Harrie Robins" 
> Subject: my.cnf authencication
> 
> mysqldump --defaults-file  dbase > c:\sql\dbase.sql 2>> c:\log.tct

Might just be a typo in your mail, but you'll need to actually pass the 
defaults-file, too: --defaults-file=c:\sql\dump.cnf .

I think there may be another typo somewhere, too, as it seems to think that 
lts-file is the user you're passing. I'm wondering if you haven't accidentally 
put only a single - in front of defaults-file.


> 
> 
> My log shows:
> 
> mysqldump: Got error: 1045: Access denied for user
> 'lts-file=c:\sql\dump.cnf'@'localhost' (using password: NO) when trying to
> connect
> 
> It looks like credentials are not filled in!?
> 
> Regards,
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql

-- 
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: Query optimizer-miss with unqualified expressions, bug or feature?

[Johan De Meersman]

- Original Message -
> From: "Shawn Green" 
> Subject: Re: Query optimizer-miss with unqualified expressions, bug or 
> feature?
> 
> On a more serious note, indexes with limited cardinality are less useful
> than those with excellent cardinality. Cardinality is an approximation
> (or calculation. It depends on your storage engine) of how many unique
> values there are in the index.

On a related note, are there any plans (and could you offer a rough timeframe?) 
to include bitmap indices in MySQL?


Thanks,
Johan

-- 
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: When to create a new database

[Johan De Meersman]

- Original Message -
> From: "Ron Piggott" 
> Subject: Re: When to create a new database
>
> I would lean towards keeping it all together because of the speed
> decrease between connecting to different databases.

Heh, that consideration is a matter of semantics, and I'd guess you're used to 
Oracle? :-p

What OP (presumably) meant was "in different schemas". Terminology is 
important, y'all.


-- 
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: When to create a new database

[Johan De Meersman]

- Original Message -
> From: "Reindl Harald" 
> Subject: Re: When to create a new database
> 
> it makes zero sense since you can use different users for the same
> database down to table and even column permissions

No, it does make some sense in the case where part of the dataset is going to 
be accessed by multiple independent applications, and I think the generic 
sports bits may actually fit that. It's cleaner from a design point of view, 
and it prevents accidentally deleting that data when the original application 
is taken out of production.

In my particular environment, we have quite a few of these generic databases; 
although from similar design ideology, they are also accessed only through 
their own REST interfaces, and not directly.

/Johan

-- 
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: table desin question

[Johan De Meersman]

- Original Message -
 From: Richard Reina gatorre...@gmail.com
 Subject: table desin question
 
 Would this be the best way to design the schema and would it be best to
 make the client ID and technician ID the same as the user ID as they relate
 to the same person?


Close enough; but I think it would be preferrable to use a unique 
(autoincrement) PK for all three tables; and use a referential key in client 
and technician to point at user.

-- 
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: Changing storage engine in dump file.

[Johan De Meersman]

- 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: MySQL 5.5 Slow performance to insert

[Johan De Meersman]

- Original Message -
 From: Camilo Vieira camilo.vie...@gmail.com
 Subject: Re: MySQL 5.5 Slow performance to insert

 $ ./mysqltuner.pl --user root --pass abril@123

Thank you for that password :-) I don't particularly like MySQLtuner myself, it 
makes assumptions about your workload that are, imo, false more often than not. 
Anyway...

 [!!] Total fragmented tables: 284

*shrug* Small issue on modern storage, pretty much no issue on solid state.

 [!!] Key buffer used: 18.2% (3M used / 16M cache)

*shrug* You could shrink that if you have no MyISAM at all, but it's a 
neglectable amount of memory.

 [!!] Query cache is disabled

Which is pretty much irrelevant for an insert workload :-)

 [!!] Table cache hit rate: 1% (400 open / 30K opened)

That might be worth growing. Exact numbers are hard to point at, use munin or 
similar to figure out the growth rate of opened_tables.

 [!!] InnoDB  buffer pool / data size: 4.0G/52.4G

If possible, size your buffer pool to at least your active dataset. This will 
have a considerable impact on your initial insert speed.

 [!!] InnoDB buffer pool instances: 1

From 5.6 (I think) this defaults to 8. The benefit is less contention for 
mutexes and the like. Upgrading to 5.6 is worthwile in most cases anyway.

 [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)

Hmm. That's peculiar for an insert workload... did you run that after (or 
during) the workload?


Your original mail also doesn't seem to specify the workload beyond 'inserts'. 
Details?

-- 
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: Unable to start mysql after power Failure

[Johan De Meersman]

Just to be sure, is that first sentence supposed to be reading node-2 and not 
node-1? The datafiles should not be in use anywhere, on any node, or disaster 
will be your share.

Additionally, what is your shared filesystem? Is it possible that there is 
still a lock on the datafiles at that level after the crash on node-1?


- Original Message -
 From: Jatin Davey jasho...@cisco.com
 To: MySql mysql@lists.mysql.com
 Sent: Saturday, 4 July, 2015 11:04:29
 Subject: Re: Unable to start mysql after power Failure

 Yes , But i dont have any mysqld process running on the node-2 as i have
 mentioned above and attempting to start mysqld on node-2 is when i face
 this issue for which the logs are showing the above error message.
 
 Thanks
 Jatin
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

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

[Johan De Meersman]

 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?

[Johan De Meersman]

None that I'm aware of. 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.

Maybe one of the proxies out there has support for such logging, I'm not really 
familiar with any of them.


- Original Message -
 From: Tomasz Chmielewski man...@wpkg.org
 To: MySql mysql@lists.mysql.com
 Sent: Tuesday, 23 June, 2015 09:35:46
 Subject: server-side logging of query errors?

 Suppose I run a query which has a syntax error:
 
 mysql blah;
 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 'blah' at line 1
 
 
 How can I get mysql server to log this error?
 
 
 According to the documentation:
 
 http://dev.mysql.com/doc/refman/5.5/en/server-logs.html
 
 - Error log - will only log mysqld errors - so, it won't log syntax
 errors in the query
 - General query log - it will log all queries, but without indicating
 if it was an error or not
 
 
 Is there a way to log query syntax errors on the server? Please assume
 that connection and the query can be coming from PHP, perl etc. code, so
 any /usr/bin/mysql stderr output redirecting is not helping here.
 
 
 --
 Tomasz Chmielewski
 http://wpkg.org
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

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

[Johan De Meersman]

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

[Johan De Meersman]

That sounds logical. I have, however, also had Martin's experience where 
create_time seemed improbable; 

- Original Message -
 From: Pothanaboyina Trimurthy skd.trimur...@gmail.com
 To: Martin Mueller martinmuel...@northwestern.edu
 Cc: MySql mysql@lists.mysql.com
 Sent: Friday, 1 May, 2015 17:13:27
 Subject: Re: create_time

 If you run any DDL (add column, drop column etc..) commands against
 particular table then create_time will update to the latest time when the
 DDL performed.
 
 If you run any DML statements (insert,update,delete), then update_time
 colum's value chages, If you restart the DB instance then update_time
 columns will be set to NULL till you run the first query against that
 particular table after the reboot.

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

[Johan De Meersman]

...sigh.

That sounds logical. I have, however, also had Martin's experience where 
create_time seemed improbable; and the structure is unlikely to have changed 
without my knowledge as user accounts don't have DML privileges.

I didn't pay any further attention to it, though, as it wasn't important to me 
at the time. I'll be monitoring this thread with interest :-)


- Original Message -
 From: Johan De Meersman vegiv...@tuxera.be
 To: Pothanaboyina Trimurthy skd.trimur...@gmail.com
 Cc: Martin Mueller martinmuel...@northwestern.edu, MySql 
 mysql@lists.mysql.com
 Sent: Monday, 4 May, 2015 16:11:24
 Subject: Re: create_time

 That sounds logical. I have, however, also had Martin's experience where
 create_time seemed improbable;

-- 
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: duplicate rows in spite of multi-column unique constraint

[Johan De Meersman]

Please do 

  select id, customer_id, concat('-', group_id, '-') 
from app_customergroupmembership 
where customer_id ='ajEiQA';

I suspect one of those group IDs has a trailing space or similar 'invible' 
character that makes it not identical.


- Original Message -
 From: Chris Hornung chris.horn...@klaviyo.com
 To: MySql mysql@lists.mysql.com
 Sent: Monday, 23 March, 2015 18:20:36
 Subject: duplicate rows in spite of multi-column unique constraint

 Hello,
 
 I'm come across a situation where a table in our production DB has a
 relatively small number of duplicative rows that seemingly defy the
 unique constraint present on that table.
 
 We're running MySQL 5.6.19a via Amazon RDS. The table in question is
 ~250M rows.
 
 `show create table` gives:
 
 app_customergroupmembership | CREATE TABLE `app_customergroupmembership` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `customer_id` varchar(6) COLLATE utf8_bin NOT NULL,
   `group_id` varchar(6) COLLATE utf8_bin NOT NULL,
   `created` datetime NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `app_customergroupmembership_customer_id_31afe160_uniq`
 (`customer_id`,`group_id`),
   KEY `app_customergroupmembership_group_id_18aedd38e3f8a4a0`
 (`group_id`,`created`)
 ) ENGINE=InnoDB AUTO_INCREMENT=21951158253 DEFAULT CHARSET=utf8
 COLLATE=utf8_bin
 
 
 Despite that, records with duplicate customer_id/group_id do exist:
 
 mysql select * from app_customergroupmembership where customer_id =
 'ajEiQA';
 +-+-+--+-+
| id  | customer_id | group_id | created |
 +-+-+--+-+
| 20279608258 | ajEiQA  | ddH6Ev   | 2015-02-17 00:14:54 |
| 20279608269 | ajEiQA  | ddH6Ev   | 2015-02-17 00:14:54 |
 +-+-+--+-+
 
 Interestingly, these dupe records can't seem to be queried when using
 both columns from the unique constraint in the WHERE clause:
 
 mysql select * from app_customergroupmembership where customer_id =
 'ajEiQA' and group_id = 'ddH6Ev';
 +-+-+--+-+
| id  | customer_id | group_id | created |
 +-+-+--+-+
| 20279608258 | ajEiQA  | ddH6Ev   | 2015-02-17 00:14:54 |
 +-+-+--+-+
 
 
 Any thoughts on how this situation came to pass, and how to prevent it
 from happening?
 
 Thanks,
 --
 Chris Hornung

-- 
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: ssh basics

[Johan De Meersman]

- Original Message -
 From: Thufir hawat.thu...@gmail.com
 Subject: Re: ssh basics

 On Mon, 09 Mar 2015 16:00:08 +0100, Johan De Meersman wrote:
 
 All in all, you've done the opposite of what I asked - you've told me
 what you know and tried, but not what you were trying to figure out with
 your original question :-p

Sounds like you mostly have a lot of fun, then :-p


-- 
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: ssh basics

[Johan De Meersman]

- Original Message -
 From: Lucio Chiappetti lu...@lambrate.inaf.it
 
 I may like a way to have a program (namely RSI IDL) to connect (bypassing
 the line mode mysql client) to the mysqld socket and issuing commands to
 it (essentially I want to issue a select into a table, and read back the
 output in an IDL structure) but I never managed to do it. But I did not
 try hard.

The unix socket, just like a tcp socket, is an appendage of the mysql daemon, 
and would thus require you to speak the mysql protocol. You can point the 
appropriate driver for your application to the socket file in roughly the same 
way as you point it to an ip/port combo (see the driver's documentation); or 
you can tell the commandline client to execute the query you want using the -e 
parameter, and optionally -b, -s or a number of others that modify the output 
format.

Sometimes the simplest things work best :-)


-- 
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: LISTMASTER: remove @naver.com addresses

[Johan De Meersman]

- Original Message -
 From: Reindl Harald h.rei...@thelounge.net
 Subject: LISTMASTER: remove @naver.com addresses

 i am really pissed of by get backscatters from hip...@naver.com or
 webmas...@naver.com after each mail to this list over years containing
 japanse crap and Your mail was denied from the receiver

Interestingly, I have never had a mail from either of those addresses. While 
undoubtedly their client/server is at fault for sending those messages in 
response to well-marked list messages; maybe it's worth finding out why your 
adress or domain is not accepted by their mailserver, too?

/johan

-- 
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: ssh basics

[Johan De Meersman]

- Original Message -
 From: Thufir hawat.thu...@gmail.com
 Subject: Re: ssh basics
 
 Thank you for explaining that.

I can't quite tell wether you're being sarcastic or not, so I'm going to give 
you the benefit of the doubt :-)

Can you explain what you're trying to accomplish, without referencing sites 
you've found or things you've tried? I think I have a fair idea, but I'd like 
to hear it in your own words.


-- 
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: Remote Access to MySQL

[Johan De Meersman]

- Original Message -
 From: Rafael Ribeiro rafaelribeiro...@gmail.com
 Subject: Remote Access to MySQL
 
 After move this Virtual Machine to a new one (got a new IP - 2.2.2.2), we lost
 the ability to connect to mysql remotely, from external IPs.

It doesn't work is not a helpful comment :-) What error message are you 
getting? Can you connect to other services on the host? Can you connect from a 
local shell?


-- 
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: ssh basics

[Johan De Meersman]

- Original Message -
 From: Emil Oppeln-Bronikowski e...@fuse.pl
 Subject: Re: ssh basics

 Please, people, do we need that kind of thread?

Most action this list has seen since we had a thread about how little action 
this list sees... :-p


-- 
The idea that Bill Gates appeared like a knight in shining armour to lead all
customers out of a mire of technological chaos neatly ignores the fact that it
was he who, by peddling second-rate technology, led them into it in the first
place.

 - Douglas Adams in The Guardian, 25th August 1995

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

Re: ssh basics

[Johan De Meersman]

- Original Message -
 From: Thufir hawat.thu...@gmail.com
 Subject: Re: ssh basics
 
 I was being serious, I always appreciate a reply.  I know it was worded
 oddly, but, yes, just take it at face value, please.

Oh, I didn't see anything wrong with wording, but text doesn't convey a lot of 
inflexions :-p


 I read multiple suggestions to connect with that approach, the -L switch,
 none of the blogs/etc I saw explained why.  I suppose they assume that
 you know why already so don't say.  It's possible I overlooked the
 context, but I don't think so.

-L is local port forwarding; so -L localhost:3306:localhost:3306 will forward 
localhost:3306 on your machine (first two parameters) to localhost:3306 on the 
remote machine (second two parameters). Remote port forwarding with -R works 
the other way round.


 In and of itself, I'm fine with ssh into the host and then using the mysql
 console.  Since I'm not running scripts, it's moot whether the connection
 is through mysql directly, if that's the correct terminology, or just
 regular ssh.

From a security point of view it isn't moot, as the MySQL protocol is 
unencrypted unless you've set up SSL - but that's probably out of your scope 
atm.


 I'm interested in how ssh is used by MySQL.  While I have a pdf book on
 ssh, I don't, at the moment, have the time to just sit down and read it
 cover to cover.

SSH is not used by MySQL at all :-) SSH is a way of connecting to a remote 
machine. MySQL is a database. You can use SSH to do things with the database, 
but they're not intrinsically related.


 In the longer term, I'm considering whether or not to backup a small db,
 with cron, master/slave, or replicate it -- or something else.  I need to
 do some reading on that.  I've always done fine with just using mysqldump,
 but will probably need some additional tools to use.

Replication is not backup, it's high availability. Backup entails having 
multiple versions; or at the very least one copy that is not automatically 
updated with users errors from the primary system :-p


 Yes, I'm still figuring out the mechanics of ssh.  It was more of a ssh
 question than a MySQL question, fair enough, pardon about that, but was
 within the specific context of MySQL.

All in all, you've done the opposite of what I asked - you've told me what you 
know and tried, but not what you were trying to figure out with your original 
question :-p

Under the assumption that you wanted to use ssh to magically have a mysql 
prompt appear on your console:
* [ssh -t user@host mysql] will log in to your machine and start the mysql 
client. The -t tells SSH to allocate a pseudoterminal even though you specified 
a command to run.
* SSH keys (using ssh-agent or passwordless keys) will authenticate you against 
the remote OS, but NOT against the mysql auth system. You could use a .my.cnf 
file in your remote homedir for that; but that will have to hold the password 
in plaintext. Not particularly secure.
* [ssh -L 3307:localhost:3306 user@host] (3307 because you apparently had a 
local mysql on 3306 already) will forward as above, so you can do [mysql 
-hlocalhost -p3307] /on another terminal/ to connect to the remote mysql.

Personally I find the last one only useful if I want to use other things than 
the commandline on remote servers; but ymmv :-)

-- 
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: Users and Groups

[Johan De Meersman]

That reminds me, this may be of interest: 
http://www.percona.com/blog/2015/03/02/emulating-roles-percona-pam-plugin-proxy-users/

- Original Message -
 From: Lucio Chiappetti lu...@lambrate.inaf.it
 To: MySql mysql@lists.mysql.com
 Sent: Wednesday, 4 March, 2015 12:39:01
 Subject: Re: Users and Groups

 When several years ago a colleague here set up the user interface for an
 astronomical database (originally using servlets now with Tomcat, and
 anyhow accessing mysql in JDBC) he considered the internal mysql privilege
 system, and for some reasons decided not to use it.
 
 He wrote an additional layer inside our java front end. We have
 workspaces, each workspace can access a number of advertised tables, and
 view advertised columns (but other columns remain accessible if called by
 name). Users belong to one (or more workspaces) and specify it when
 logging in our system.  All workspaces have readonly access (we do not
 consider user-writable tables).   Our java engine communicates with mysql
 as a single user.
 
 This way we do not have to care about granting access to the mysql server
 to external hosts.
 
 Anyhow I presume that playing around with the grants and privileges
 tables, one could find a way to write a template set of privileges for a
 typical user and replicate it for all users of same logical group
 
 
 
 --
 
 Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
 For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html
 
 Do not like Firefox =29 ?  Get Pale Moon !  http://www.palemoon.org
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

-- 
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: Capturing milestone data in a table

[Johan De Meersman]

- Original Message -
 From: Phil pchap...@nc.rr.com
 Subject: Capturing milestone data in a table

 user_credits where metric1  $mile and (metric1 - lastupdate)  $mile)

That second where condition is bad. Rewrite it as metric1  ($mile + 
lastupdate). Better yet, combine them into a between comparison.

As it is, it can't use the index for that because the lefthand is a computed 
field; the optimiser can only pick an index scan or a full tablescan.
If you keep the lhs expression index-enabled, the optimiser gets the additional 
option for an index range scan, which is more performant.

It's still not going to be extremely performant, though, because it still has 
to calculate for each row based on the lastupdate value. Look at converting 
that to a (pseudo)constant, so only one index scan is needed instead of one per 
row.


-- 
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: Users and Groups

[Johan De Meersman]

- Original Message -
 From: Reindl Harald h.rei...@thelounge.net
 Subject: Re: Users and Groups

 Am 01.03.2015 um 20:07 schrieb Steffan A. Cline:
 Has anyone seen a plugin for MySQL that will allow you to set up users and
 groups for access where you can have a user who can login, create db etc but
 
 MySQL don't support user groups

There is a plugin for LDAP authentication out there; I haven't played with it 
myself but I suppose you could use LDAP functionality to emulate groups?


-- 
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: Merging multiple SQL requests

[Johan De Meersman]

- Original Message -
 From: Jay Ess li...@netrogenic.com
 Subject: Re: Merging multiple SQL requests

 UNION is used to combine the result from multiple SELECT statements into a
 single result set.

Yes, but only if your queries return the same number of fields; and you get a 
single resultset out of it.

I was under the impression that OP wanted to simply batch multiple unrelated 
small resultsets in a single network packet.


-- 
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: is a .sql file transactional

[Johan De Meersman]

- Original Message -
 From: thufir hawat.thu...@gmail.com
 Subject: is a .sql file transactional

 when you run a .sql file, which modifies a schema, is it transactional?
 Specifically, is it an all-or-nothing proposition?  Or, can some commands
 get executed, some fail?

A file is nothing more than just another input method, it has no impact on 
transactionality or any other parameters.

DDL (schema modification) cannot be done inside a transaction - mysql will 
automatically commit your open transaction before proceeding, if you have one.

Every DDL statement individually is, however, all-or-nothing - the server 
(mostly) makes a copy of the table you're operating on, and only switches them 
out if the change was successful.


-- 
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: Merging multiple SQL requests

[Johan De Meersman]

...it sounds to me like you're trying to figure out the small things when 
there's probably an elephant standing around somewhere :-)

You can quickly see if your network is troublesome using:
 * traceroute
 * ping
 * network copy (ssh/nfs/samba/whatever) of a large file

Usually, however, it's not the network :-)

Open up a console on your server and run dstat, see what resources are being 
taxed. Turn on the slowlog at 1s (or even 0s) and use pt-query-digest to figure 
out what queries are slow. Profile your application to see which bit takes 
longest to execute.



- Original Message -
 From: Learner Study learner.st...@gmail.com
 To: Stewart Smith stew...@linux.vnet.ibm.com
 Cc: MySql mysql@lists.mysql.com, internals intern...@lists.mysql.com
 Sent: Monday, 16 February, 2015 02:07:45
 Subject: Re: Merging multiple SQL requests

 I meant that can MySQL server combine multiple responses for a client
 and send a single TCP packet back to the client.
 But based on your response, I don't think that is possible - please correct?
 
 Are there are any gothas to debug/investigate MySQL latency? I have
 checked TCP tunables, kernel timer ticks etc. Is there anything else
 to watch out for..pointers would be appreciated.
 
 Thanks

-- 
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: command is not allowed with this MySQL version

[Johan De Meersman]

- Original Message -
 From: yoku ts. yoku0...@gmail.com
 Subject: Re: command is not allowed with this MySQL version
 
 Christophe has already told,
  The used MySQL version is 5.5.40 from Debian Wheezy package.

No, that's the new version. It'd be fun to know what the OLD version was, too.

 Maybe you missed *mysql command-line client's --local-infile option*

Not particularly helpful, since Christophe already said it's a PHP application 
:-)


-- 
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: Xml data import

[Johan De Meersman]

- Original Message -
 From: Sayth Renshaw flebber.c...@gmail.com
 Subject: Xml data import
 
 I have an xml data feed with xsd, it's complex in elements not size. Wray
 are the best way to get data into mysql, do I have to hack with xquery?

That's going to depend on the complexity of your XML and how much of that 
complexity you want preserved. There's definitely libraries out there that can 
map XML (tree) structures onto relational structures; although it's been so 
long since I played with XML that I really couldn't tell you which ones.

 My goal is to be able create queries and send csv files out for analysis in
 R and plots in ggplot2. Also render done other off the initial days to the
 Web usually xslt from xml.

I suppose that worstcase you could use XSLT to transform into a flatter 
structure, maybe even CSV?


-- 
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: forum vs email

[Johan De Meersman]

- Original Message -
 From: Sándor Halász h...@tbbs.net
 Subject: Re: forum vs email

 Something more sophisticated than grouping messages by trimmed subject-lines?
 maybe involving such header lines as were used in the old netnews (if e-mail 
 is

That's only a single level. Fora are more often than not split into entire 
trees, sometimes four or more levels deep.


-- 
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: How to retrieve next record?

[Johan De Meersman]

- Original Message -
 From: Wm Mussatto mussa...@csz.com
 Subject: Re: How to retrieve next record?

 Related what is the form of the prmary key.  If its numeric something like
 $sDBQuery1 = SELECT * FROM kentekenlogtest WHERE kenteken 
 '$sActueelkenteken' limit 1
 might work.

No, kenteken is dutch for license plate. If so, not numeric, although 
greater/less comparisons do work on strings, too. My guess, from the sample 
queries, would be that this is processing for some form of automated number 
plate recognition system :-)

Now, Hans, besides pointing you in the right direction, I'm going to be whining 
a bit about some pet peeves of mine. I'm waiting for the start of a midnight 
intervention, anyway :-p

That query, as pointed out already, is only asking for a single kenteken. I'll 
stick to the dutch column names for clarity for other readers, btw - although 
one of the aforementioned pet peeves is nonenglish variable names. Makes code 
an absolute bitch to maintain for someone who doesn't speak that language. 
That's from experience; I've had to debug crap in french and spanish, among 
other languages.

Your code (or, more precisely, the DB driver) is only going to make those 
records available to your program that you have explicitly asked for, so that 
query will only ever make the one record available. You will need to build a 
query that returns all the records you want to access, or, alternatively, make 
repeated queries. The former is more efficient by far; the latter is useful if 
the next set depends on what you find in the previous set.

Another pet peeve: don't use select *. Explicitly select the columns you're 
looking for. It a) saves network bandwith; b) guards against later table 
structure changes; c) potentially allows the use of covering indexes and d) 
reduces the server memory footprint required for sorting etc.

Once you built the correct query, you'll need to have a cursor to loop through 
it. Your DB driver will probably refer to it as a resultset or a similar 
denomination. The typical buildup for a database connection (bar advanced 
abstraction layers) is db_connect (returns a database handle); 
dbh-execute(sql) (returns a resultset handle); loop using rs-fetch_next 
(probably returns an array or hash with the data). See your language's db class 
documentation for the gritty details there. You may also find a fetch_all or 
similar which returns you the entire resultset in a single call. Can be useful, 
but remember that that means allocating memory clientside for the entire 
dataset in one go, instead of reusing the same variables row for row.

A further pet peeve: don't just dump variables into your sql string, use bind 
variables. The easy method opens you up for little Bobby Tables. Google that, 
if you're unfamiliar with it. Then weep in despair :-p

The idea of bind variables is fairly simple: you stick placeholders in your sql 
string where you would otherwise use string interpolation; then tell the 
statement handle the variables that should go in there. The database is 
actually aware of this method, so there is no chance that the variables might 
get interpreted as part of the SQL - it KNOWS they're variables, not keywords.

Additionally, if you're going to be executing the same statement repeatedly, 
use prepared statements instead of regular executes. On MySQL the benefit is 
marginal (but still noticeable), on other databases it might be considerable - 
sometimes orders of magnitude faster. Oracle, for instance, has an execution 
plan cache; so if you use prepared statements, it can skip the whole parse - 
analyze - pick plan bit and skip straight to the next execution round with the 
new values you provided. On fast statements (like primary key lookups) that can 
sometimes save 80% and more of the roundtrip time.

The abovementioned where-clause with limit is probably also going to work; but 
then you'll need to re-query time after time; and limit does not always work 
quite intuitively - although in this simple case, it does. If you *must* 
re-query time after time, do a speed comparison with and without prepared 
statements; otherwise do go for the fetch_next loop.


Now, you've got documentation to read, I believe. Off you go :-)

/johan

-- 
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: command is not allowed with this MySQL version

[Johan De Meersman]

- Original Message -
 From: Christophe t...@stuxnet.org
 Subject: command is not allowed with this MySQL version
 
 'The used command is not allowed with this MySQL version'

Out of sheer morbid curiosity, what version were they running?

-- 
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: forum vs email

[Johan De Meersman]

- Original Message -
 From: Sándor Halász h...@tbbs.net
 Subject: Re: forum vs email

 I believ that one could both by e-mail and through a webbrowser comment on a
 Google group.

True; and before that there was yahoo groups, and others. Those are not fora, 
however, merely web interfaces to mailing lists / newsgroups. One of the (for 
me, at least) defining features of a forum, is that the subjects tend to be 
divided up into a tree structure, which has it's own benefits; although as 
Shawn says, there is also benefit to the broad exposure you get on a mailing 
list.


 And one who reads  sends e-mail through a webbrowser surely considers
 discussion through e-mail simplie more overhead than using his webbrowser for

I use webmail - to the point where I host my own domains. Still vastly prefer 
email over fora, as interfaces go. Better read/unread views, proper filtering, 
sorting stuff into folder structures that are convenient for me instead of for 
the administrator, etc.


 discussion, too. I further suspect e-mail clients on own computers are not in
 fashion.

That does seem to be the case; although I think in a corporate setting the 
situation is different.


-- 
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: forum vs email [was: Re: table-for-column]

[Johan De Meersman]

- Original Message -
 From: Jigal van Hemert ji...@xs4all.nl
 Subject: Re: forum vs email [was: Re: table-for-column]
 
 On typo3.org there used to be mailing lists only in a distant past.
 Later on newsgroups were set up which communicate with the mailing lists
 (newsgroups are the central source of messages).
 Rather recently a forum was built on top of the newsgroup data (FUD
 forum was used). Users on all three message sources can easily
 communicate with eachother.

Hmm. That sounds interesting, I'll have a look. I don't suppose the software is 
available under a foss license? :-p


-- 
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: forum vs email [was: Re: table-for-column]

[Johan De Meersman]

- Original Message -
 From: Johan De Meersman vegiv...@tuxera.be
 Sent: Wednesday, 10 December, 2014 09:02:45
 Subject: Re: forum vs email [was: Re: table-for-column]

 Hmm. That sounds interesting, I'll have a look. I don't suppose the software 
 is
 available under a foss license? :-p

Hm. Typo3 is a CMS; I take it the integration you're speaking of is specific to 
their support environment, and not part of the CMS?


-- 
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: signal handling in mysql cli

[Johan De Meersman]

- Original Message -
 From: Sebastien FLAESCH s...@4js.com
 Subject: Re: signal handling in mysql cli

 Nobody concerned by this case?
 Simple question: is it safe or not to do a KILL QUERY in a SIGINT signal
 handler?

I don't see what the extra risk would be as opposed to doing so in any other 
thread of your code, or in another program entirely. As long as you're sure 
about which particular query you're shooting in the face, it should be 
perfectly fine.


-- 
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: Specking a small MySQL server

[Johan De Meersman]

- Original Message -
 From: Richard Reina gatorre...@gmail.com
 Subject: Specking a small MySQL server
 
 somewhat of an energy hog and is due to be replaced. I was considering
 replacing it with a lap-top so as to conserve energy and because a laptop
 has a built in battery backup. Currently I have a couple of laptops running

For a long time, my go-to recommendation for reliable laptops would've been 
thinkpads; but I haven't had any experience with the new chinese versions.

I've been pretty happy with my (employer-issued) HP ProBooks, but that' a 
sample of one, of course :-)

One thing to keep in mind for your particular usage, though, is that consumer 
drives, and, especially, laptop drives, are not designed for 24/7 operation. I 
would strongly recommend to go for SSD storage even if you don't need the 
speed, as those at least don't have moving parts.

/johan


-- 
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: forum vs email

[Johan De Meersman]

- Original Message -
 From: Sándor Halász h...@tbbs.net
 Subject: Re: forum vs email

 That is, this list, right? What does it lack (besides readers)?

This list interacts with the forums on mysql.com? Every thread here matches one 
on there, and vice versa? (Honest question; I hardly ever visit the fora - but 
Shawn's earlier mention that he only time for one, not both, makes me think not 
so.)


-- 
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: forum vs email [was: Re: table-for-column]

[Johan De Meersman]

- Original Message -
 From: Jan Steinman j...@ecoreality.org
 Subject: Re: forum vs email [was: Re: table-for-column]

 There actually seem to be a lot of these around. I'm on several that send me
 email when there are new forum postings.

Yes, that bit is pretty standard functionality; but usually they're little more 
than a notification that something was posted, maybe the first few lines of a 
post.

I want:
 * The entire post, and as little notification-type content as possible,
 * headers and subjects so that mail clients that support threading will thread 
everything from a single forum topic in a mail thread and vice versa,
 * and, most importantly, the ability to also *reply* through mail and have it 
appear in the forum thread at the appropriate place in the conversation

Those things are what would make it a proper mailing list integration, instead 
of just another notification tool.


-- 
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: table-for-column

[Johan De Meersman]

- Original Message -
 From: Shawn Green shawn.l.gr...@oracle.com
 Subject: Re: table-for-column
 
 My problem is a lack of time. I can monitor the mailing lists or the
 forums but rarely both while still doing my regular job of handling the
 official service requests.

I've long wanted to - but never quite got around to - write a forum that 
integrated a mailing list. Bar mail clients that don't handle list threads 
well, it really doesn't seem such a difficult task.

I never understood why nobody ever did it, as it allows members of a community 
to interface with it through their preferred means.

Perhaps one of those web2.0 types will eventually get around to it :-)



-- 
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: signal handling in mysql cli

[Johan De Meersman]

- Original Message -
 From: wharms wha...@bfs.de
 Subject: signal handling in mysql cli
 
 when i use CTRL-C to break a query that works fine in interactive mode.
 
 but when i use the noninteractive mode
 i looks like that  but show full processlist; shows otherwise and that is 
 true

This may sound silly, but you're basically saying that you can't interact with 
it while in non-interactive mode...

My understanding may be wrong, but this is how it works in my mind:
 * when in the client, the client intercepts the ctrl-c and interprets it as 
kill this query on the server.
 * when in non-interactive mode, the client is not actually reading your 
keypresses. Thus, the ctrl-c gets intercepted by the *shell*, which does 
exactly what you ask by killing the MySQL client process.

Now, if the mysql client does not explicitly intercept kill signals - which, 
honestly, most programs have no reason to - that means it doesn't even get a 
chance to send the cancel that query order, it just dies.

A lot of debate could be put in on wether or not the client needs to intercept 
and send a cancel, but personally I'm leaning towards no, it doesn't - if you 
don't have a transaction open, or don't even have a transactional engine (which 
used to be default!) cancelling would leave your database in an inconsistent 
state. Much better, then, to allow whatever query you sent to continue, on the 
off chance that it's something that it does not damage, or only does things 
that you can reverse afterwards. If you really want to kill that query, you 
always have the option of logging into the server and shooting it yourself.

/johan


-- 
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: table-for-column

[Johan De Meersman]

- Original Message -
 From: peter brawley peter.braw...@earthlink.net
 Subject: Re: table-for-column

 I wonder if anyone knows why sites like Stack Overflow and those of ours
 I mentioned are seeing more volume, while this list and all MySQL fora
 are seeing much, much less.

The major benefit I see on StackOverflow and the like, is the rating system on 
the comments, and to some extent the rating system for users.

On the other hand, I find that the signal-to-noise ratio on older media like 
mailing lists and IRC tends to be much more favourable, presumably because it 
is where the dinosaurs dwell :-)


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

[Johan De Meersman]

- Original Message -
 From: Ruben Safir ru...@mrbrklyn.com
 Subject: Re: MySQL dying?
 
 Well, this mailing list is dead.  This is a mailing list that used to
 handle 70+ questions a day, or more.


Is that why you feel the need to troll on posts from two years ago?

If you think it's dead, unsubscribe and go install MSSQL. If not, either ask a 
question or stop wasting bandwidth.


Bye now.

-- 
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: Which replication solution should I choose?

[Johan De Meersman]

 From: Walter Heck walterh...@olindata.com
 Subject: Re: Which replication solution should I choose?

 Hi Johan,
 it'll be a good ol' war story of the transition of a large 130k QPS MMM 
 cluster
 to PXC, so come visit for sure. Here's the link to the session:
 http://www.percona.com/live/london-2014/sessions/moving-mysql-infrastructure-130k-qps-galera

It was pretty interesting, indeed, especially the bit about the spontaneous 
resyncs :-p 

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

Re: Multi-Master Asynchronous Replication

[Johan De Meersman]

- Original Message -
 From: Rodrigo Ferreira rodrigof_si...@yahoo.com
 Subject: Multi-Master Asynchronous Replication

 Hi,
 Is that a way to make multi-master asynchronous replication with mysql ou
 external lib?
 I know galera cluster but it is synchronous.
 The problem is a set of eventually disconected nodes need to send all changes
 (when connected) to a master always connected node.

Yes, async is the default setup with MySQL replication; multimaster is possible 
using a circular setup (or, with recent MariaDB, a slave can have multiple 
masters); but it's not officially supported, and it's tricky to get right 
because of concurrent updates etc.

Careful with that :-)


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

[Johan De Meersman]

- Original Message -
 From: Ruben Safir ru...@mrbrklyn.com
 Subject: Re: MySQL dying?

 abandoned.  What would you have done in those days when we handled so
 much mail in this list that there was no time to answer trolls... the
 real trolls?

We? You mean the two mails you sent back in 2011 trying to figure out how to 
install the C++ connector?

*plonk*


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

[Johan De Meersman]

- Original Message -
 From: Ruben Safir ru...@mrbrklyn.com
 Subject: Re: MySQL dying?

 where were you in 2000, youngerman?

Busy writing WAP backends powered out of MySQL and Oracle, if I remember 
correctly :-) But, indeed, not on this list; and if you were here back then I 
may have severly misjudged you. My apologies for that.

However, I find it hard to believe that you are suggesting that MySQL is in a 
worse state now than it was back then, let alone that there were more users 
back then than now...

What exactly do you see going wrong under Oracle stewardship? There haven't 
been this many bugs fixed and new features implemented in years.


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

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

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

[Johan De Meersman]

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

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

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

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


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

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

Re: Which replication solution should I choose?

[Johan De Meersman]

- Original Message -
 From: Walter Heck walterh...@olindata.com
 Subject: Re: Which replication solution should I choose?
 
 If you happen to be at the Percona Live conference next week in London I'm
 speaking about Galera in a high performance setup as well.


Ooo, very interested, especially if you're going to be talking about the 
gotcha's - I'm considering switching our master-master setups out for Galera 
clusters, and it'd be good to have an idea what the side effects are going to 
be :-)


-- 
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: Need a short directive

[Johan De Meersman]

- Original Message -
 From: Trianon 33 triano...@gmail.com
 Subject: Need a short directive
 
 Values from yearanddate look like this: 2013-12-11 00:00:00. I want to

That's only a display format; internally it's an integer (well, presumably a 
struct time_t) counting the seconds since epoch. Not especially relevant except 
to say that, since it's only an output format, it can easily be changed.

 copy the 2013 and put that into the yearfield, for each record.
 
 Can that be done by just using SQL statements?

I believe the year() function is pretty much what you're looking for; complexer 
things can be handled through date_format().

Something along the lines of 

 UPDATE table
   SET year = year(yearanddate);

should do nicely; I'm sure you can fix up a where clause as appropriate.


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

Possible bug with event and delete...limit ?

[Johan De Meersman]

Hey list, 

I noticed a table that was trying to fill the disk before the weekend, so I 
quickly set up an event to gradually clean it out. Yesterday, however, I 
returned to find 400+ jobs in state updating. I disabled the event, but the 
jobs hadn't cleared up today, so I had to kill them. 

I noticed, however, that the LIMIT statement I specified in the event wasn't 
present in the actual queries... Could that be a parser bug, or does the limit 
simply not show up in the process lists? Has anyone seen this before ? 

This is 5.5.30-1.1-log on Debian 64-bit. 

Thanks, 
Johan 


mysql show create event jdmsyslogcleaner\G 
*** 1. row *** 
Event: jdmsyslogcleaner 
sql_mode: 
time_zone: SYSTEM 
Create Event: CREATE DEFINER=`root`@`localhost` EVENT `jdmsyslogcleaner` ON 
SCHEDULE EVERY 30 SECOND STARTS '2014-09-19 19:14:21' ON COMPLETION PRESERVE 
DISABLE COMMENT 'Cleanup to not kill the disk' DO delete from syslog where 
logtime  2014-07-20 limit 1 
character_set_client: latin1 
collation_connection: latin1_swedish_ci 
Database Collation: latin1_swedish_ci 
1 row in set (0.00 sec) 


mysql select * from information_schema.processlist WHERE `INFO` LIKE 'DELETE 
FROM `cacti%' order by time; 
+---+---++---+-+---+--++
 
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | 
+---+---++---+-+---+--++
 
| 149192515 | cacti_net | host:49225 | cacti_net | Query | 21 | init | DELETE 
FROM `cacti_net`.`syslog` WHERE logtime  '2014-06-24 08:48:28' | 
[...] 
| 148845878 | cacti_net | host:50186 | cacti_net | Query | 47345 | updating | 
DELETE FROM `cacti_net`.`syslog` WHERE logtime  '2014-06-23 17:13:51' | 
+---+---++---+-+---+--++
 
411 rows in set (13.66 sec) 




-- 
What's tiny and yellow and very, very dangerous? 
A canary with the root password. 

Re: converting numeric to date-time?

[Johan De Meersman]

- Original Message -
 From: Jan Steinman j...@ecoreality.org
 Subject: RE: converting numeric to date-time?
 
 I don't think the OP has a Unix timestamp.

OP explicitly says epoch including milliseconds - so it's going to be three 
digits too long :-)

divide by 1000; split off decimal; from_unixtime(epoch) and add a millisecond 
display.

-- 
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: fragmentation in innodb index

[Johan De Meersman]

 Senior Oracle and MySQL DBA Corporate Trainer and Database Security

Am I the only one worried about that line, then?

-- 
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: Stored procedure debuggers

[Johan De Meersman]

- Original Message -
 From: Larry Martell larry.mart...@gmail.com
 Subject: Stored procedure debuggers
 
 Does anyone know of any debuggers for stored procs that run on Mac and/or
 Linux?

Not aware of native ones, but I seem to remember that I managed to get the one 
that occasionally gets advertised on this list (can't even remember the name) 
to work under Wine at some point. Took some messing with .net things, though.


-- 
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: sql syntax error

[Johan De Meersman]

- Original Message -
 From: florent larose florent.lar...@hotmail.com
 Subject: sql syntax error
 
 near ''membres2' WHERE
 [...]
  FROM 'espace_membre2'.'membres2' WHERE

You were on the right path - mysql is wibbly about quotes. Either remove the 
quotes entirely ( espace_membre2.membres2 ) or try backticks ( ` ). They're 
MySQL's favourite quote, presumably because they were convenient to type on 
whatever abomination Monty used to type code :-)

As your code is french, I'll assume you're on Azerty; the backtick is Alt-Gr 
plus the rightmost key (right next to return) on the middle row. Enjoy 
spraining your fingers :-p

/johan

-- 
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: Avoiding table scans...

[Johan De Meersman]

- Original Message -
 From: Chris Knipe sav...@savage.za.org
 To: mysql@lists.mysql.com
 Sent: Thursday, 24 July, 2014 11:17:50 AM
 Subject: Avoiding table scans...
 
 mysql SELECT MIN(ArtNumber) AS ArtNumber, MessageID FROM
 78168ea0a9b3b513a1f2d39b559b406e WHERE ArtNumber  '2118806';

You're putting quotes around ArtNumber in your where clause, where it really is 
a bigint. Thus, you're forcing implicity conversion in the parser, instead of 
simply doing an index lookup.


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

Proxy / connected failover question

[Johan De Meersman]

Hullo peoples, 

I'm not usually an advocate of MySQL Proxy and the like, but I'm stuck with one 
shitty application that utterly breaks whenever the database goes away 
unexpectedly. I can't change the application itself, so I find myself looking 
for options that allow the heathen contraption to not notice it's connection 
has switched. 

I am aware that connection state etc is likely to be lost anyway; I'll have to 
see wether or not that's going to be an issue during testing. 

I have two main questions: 
* am I remembering right that MySQL Proxy provides transparent failover ? 
* Are there other contenders in the same field, or alternate solutions ? 

Ideally I'm looking for a hyper-stable tool that can run on it's own VM, so the 
application doesn't notice when I switch backends. All the other applications 
play nice, in that they simply reconnect and go on with business, so it doesn't 
even *have* to take improbably loads. 


Thank you for any and all suggestions and information, 
Johan 

-- 
What's tiny and yellow and very, very dangerous? 
A canary with the root password. 

Re: Proxy / connected failover question

[Johan De Meersman]

- Original Message -

 From: Martin Gainty mgai...@hotmail.com
 Subject: Proxy / connected failover question

   I'm not usually an advocate of MySQL Proxy and the like, but I'm stuck
   with one shitty application that utterly breaks whenever the database
   goes
   away unexpectedly. I can't change the application itself, so I find
   myself
   looking for options that allow the heathen contraption to not notice
   it's
   connection has switched.
 MGhow could a proxy server re-route a downed DB connection to another
 server?
 MGdoes Proxy have a heartbeat on 3306 and reroute somewhere else when 3306
 connection goes silent?
 MGexactly how does either MySQLProxy or HAProxy handle these detection and
 reroute scenarios?

Unlike a router, a proxy does not route packets; it acts as terminator for the 
client connection, interprets the question and then asks that same question on 
a separate connection to the actual server that it initiated itself. 

Thus, if the proxy notices that the backend is gone, it can simply establish a 
new connection to the same or a different host without having to break the 
connection with the client. 

It's a simple idea that works extremely well with stateless protocols like 
HTTP; but for a stateful procotol like MySQL it's rather more complex :-) 

Thank you all for the input, I'll have a look at your suggestions and report 
back with what I came up with :-) 

/johan 

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

Re: Doubts tuning MySQL Percona Server 5.5

[Johan De Meersman]

- Original Message -

 From: Antonio Fernández Pérez antoniofernan...@fabergroup.es
 Subject: Re: Doubts tuning MySQL Percona Server 5.5

 ​I was checking MySQL performance ... Sometimes my database could be working
 slow. I have some queries that spend 9-10 seconds updating some columns by
 primary key. I'm not sure if is a data base problem ...

If the same query is sometimes OK and sometimes not, that's usually a 
consequence of varying load, or possibly rushes on various resources. Those are 
typically things that are, honestly, rather hard to figure out over email. The 
Percona boys have some rather good blog posts and tutorials that may be of 
interest. 

Do an explain of the naughty queries, if the explain comes up good, there's 
going to be an underlying cause. 

 Moreover, I have checked tuning scripts and appear these variables.

Tuning scripts are a good first look, but they're just stupid little things, 
they have no idea about the baseline performance for your environment. They 
also mostly don't look at performance over time, they just see an average from 
start of server until now - which is obviously mostly useless if you have 
several months of uptime. 

 InnoDB log waits is 103; innodb_log_buffer_size is 8M -- Maybe the next 
 best value could be 16M? 

8M is not a bad value, but you'd have to have a look at how much logging you 
push to disk, and how fast that goes. Incidentally, as this is a theme in your 
questions, you don't always have to double the values :-) 

 Table cache hit rate is 12%; Open_tables is 627; table_open_cache is 1024 

Well, the table cache isn't full, so I suspect not. Keep an eye on 
Opened_tables - that tells you how many tables have been opened since service 
start, so it shouldn't increase dramatically once it's up to speed. 

 Key buffer hit rate is 93.7%; I have some queries that not using indexes .. 

I keep coming back to the same question: why do you think that's a bad number? 
:-) 

Have a look at wether you can add indexes or otherwise optimise those queries, 
but it's perfectly possible and acceptable if that's not possible - maybe it's 
possible to offload the hard queries to a separate slave? Optimise in 
function of your environment. Only your can define what constitutes acceptable 
performance in your environment. 

 join_buffer_size is 4M -- Next best value? Maybe 8M and then check it again? 

I strongly recommend not touching those at all - oftentimes, those kind of 
variables either don't do quite what you think at first glance, or are part of 
a more complex system. 

Specifically for the join_buffer_size, note that: 

* it is the MINIMUM that gets allocated, wether or not it's needed 
* it is not allocated per-session, but PER-JOIN, so a single complex query 
may allocate several. 

So, that means that even the smallest query that needs a join buffer will 
allocate 4M, and while it depends on your environment, it's very probably that 
you have more small queries than large ones :-) 

Keep it small by default, and if you know a query is going to need big buffers, 
you can still set it larger as a session variable when you need it. 

The best advice I can give you is to set up Munin, Cacti or another tool to 
monitor server status and performance over time; that way you will get a 
baseline for what's normal; see any behaviour that deviates from the 
baseline, *and* can meaningfully see the impact over time of any changes you 
make. 

/johan 

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

Re: Doubts tuning MySQL Percona Server 5.5

[Johan De Meersman]

I'm missing something rather essential in your mail... are you actually 
experiencing performance problems, or are you just looking at variables and 
randomly deciding you don't like their value? 

Always remember the golden rule: if it ain't broken, don't fix it. 

On July 4, 2014 8:00:31 PM CEST, Antonio Fernández Pérez 
antoniofernan...@fabergroup.es wrote:
Hi list,

I have some doubts adjusting some MySQL variables.
​ I have checked MySQL status and maybe I should to increase some
variables
...

For example:
InnoDB log waits is 103; innodb_log_buffer_size is 8M -- Maybe the
next
best value could be 16M?
Table cache hit rate is 12%; Open_tables is 627; table_open_cache is
1024
-- Maybe the next best value could be 2048?
Key buffer hit rate is 93.7%; I have some queries that not using
indexes ..
join_buffer_size is 4M -- Next best value? Maybe 8M and then check it
again?

That's all.

I hope your advices.

Regards,

Antonio.

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: Optimizing InnoDB tables

[Johan De Meersman]

- Original Message -
 From: Antonio Fernández Pérez antoniofernan...@fabergames.com
 Subject: Re: Optimizing InnoDB tables
 
 I would like to know, if is possible, why after execute an analyze table
 command on some fragmented table, after that, appears fragmented again.

Simple question: why do you believe this is a problem?


-- 
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: Optimizing InnoDB tables

[Johan De Meersman]

- Original Message -
 From: Antonio Fernández Pérez antoniofernan...@fabergames.com
 Subject: Re: Optimizing InnoDB tables
 
 I have enabled innodb_file_per_table (Its value is on).
 I don't have clear what I should to do ...

Then all new tables will be created in their own tablespace now. It's easy to 
convert an existing table, too, simply do alter table yourtable 
engine=innodb - but that will of course take a while on large tables.

The problem, however, is that there is no way to shrink the main tablespace 
afterwards. Your tables will all be in their own space, but the ibdata1 will 
still be humoungous, even though it's close to empty. Don't just delete it, 
btw, as it still contains metadata.

The only way to get rid of those, is to export ALL innodb tables, shut down 
mysqld, delete all innodb files (iblog0/1, ibdata1 etc, but also db/*.ibd and 
the associated db/*.frm files; then start the server (it'll recreate ibdata1 
as specified in your my.cnf, so shrink there, too, if required) and then import 
the lot again.

Note that, if you have the space, you don't *have* to do that - the huge 
ibdata1 file doesn't do any harm; but do consider that as your dataset grows 
over the years, it'll become more and more of a bother to actually do it.

Make sure you have backups when attempting :-)

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

[Johan De Meersman]

- Original Message -
 From: Johan De Meersman vegiv...@tuxera.be
 Subject: Re: SHOW FULL COLUMNS QUERIES hogging my CPU
 
 In any case, this is nothing that can be fixed on the database level.

I may or may not have to swallow that :-p

I've been hammering a munin plugin that graphs schema sizes (yay capacity 
management); and obviously it's dragged down by an information_schema query. I 
stumbled upon this, which may or may not be helpful for your situation, too: 
http://www.mysqlperformanceblog.com/2011/12/23/solving-information_schema-slowness/

No guarantees, but it doesn't seem to have any significant downsides.


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

[Johan De Meersman]

- Original Message -
 From: Jatin Davey jasho...@cisco.com
 Subject: Re: SHOW FULL COLUMNS QUERIES hogging my CPU
 
 Certain part of our code uses DataNucleas while other parts of the code

A data persistence product... there's your problem.

Persisting objects into a relational database is like fitting a square peg in a 
round hole: it works if you have a big enough hammer, but something is going to 
break.

I'm not going to go into the myriad ways such layers add inefficiencies that 
only get worse with scale; but I suggest you log a ticket with your vendor; 
they're bound to have encountered the problem before. There's probably some 
setting that caches the db metadata instead of requesting it over and over 
again.

In any case, this is nothing that can be fixed on the database level.


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

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

Re: access 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

Re: blob data types

[Johan De Meersman]

- Original Message -
 From: Reindl Harald h.rei...@thelounge.net
 To: mysql@lists.mysql.com
 Sent: Monday, 26 May, 2014 11:56:26 AM
 Subject: Re: blob data types
 
 
 Am 26.05.2014 11:40, schrieb geetanjali mehra:
  I want to know where does MyISAM and innodb stores its BLOB data ; inside
  the table or outside the table.  I tried to understand BLOB using MySQL
  online docs but failed.
 
 inside the table, it's just a field type

Didn't InnoDB store them out of line (but still inside the datafile, of course) 
? I might be remembering wrong, though.

You could design your application to store blobs in files instead; but you 
preferably also need to use redundant/shared storage, then. It's a common 
enough design, but it needs though and understanding :-)

As a tangential thought, there was also a thirdparty plugin engine at some 
point that streamed blobs outside of the normal MySQL protocol. Can't remember 
the name.


-- 
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: Case sensitivity

[Johan De Meersman]

- Original Message -
 From: Reindl Harald h.rei...@thelounge.net
 Subject: Re: Case sensitivity
 
 ALTER TABLE `my_table` DEFAULT CHARACTER SET latin1 COLLATE
 latin1_general_ci;

Purely from memory, doesn't that change the table but add the old setting to 
individual text columns?

I seem to recall running into 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: Big innodb tables, how can I work with them?

[Johan De Meersman]

- Original Message -
 From: Manuel Arostegui man...@tuenti.com
 Subject: Re: Big innodb tables, how can I work with them?
 
 noSQL/table sharding/partitioning/archiving.

I keep wondering how people believe that NoSQL solutions magically don't need 
RAM to work. Nearly all of them slow down to a crawl, many even worse than an 
SQL database, as soon as the full or working set no longer fits in memory, too.

Don't get me wrong - they have certain benefits and definite usecases, but it's 
time people stop presenting them as a magic bullet. They require understanding 
and work, just like any other technology.


-- 
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: Advices for work with big tables

[Johan De Meersman]

- Original Message -
 From: Antonio Fernández Pérez antoniofernan...@fabergroup.es
 Subject: Advices for work with big tables
 
 Hi,
 
 I write to the list because I need your advices.
 
 I'm working with a database with some tables that have a lot of rows, for
 example I have a table with 8GB of data.
 
 How can I do to have a fluid job with this table?

The two easiest points of optimisation are:
 * Make sure your queries can use indexes as much as possible
   * Percona Toolkit has good stuff for figuring that out. Have a look at 
pt-query-digest.
 * Provide enough memory to your MySQL server to fit the entire database (and 
don't forget to allocate it to your InnoDB bufferpool if that applies :-) )
   * failing that, at least enough memory to keep the most frequently used 
dataset in memory
 * failing *that*, but here you're running into disk bottlenecks already, 
enough memory to keep your indexes in memory
   * faster disks for data that doesn't fit in memory (SSD, FusionIO etc)

Memory required for the full dataset: select sum(data_length+index_length) from 
information_schema.tables;
Memory required for indexes: select sum(index_length) from 
information_schema.tables;

There's no easy way to figure out your active data set size, that depends on 
what queries are performed most often.


Depending on what type of disk cabinet you have, it may be possible to replace 
some drives with full SSDs, or with disks that have a built-in SSD cache.


-- 
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: Performance boost by splitting up large table?

[Johan De Meersman]

You've already had some good advice, but there's something much more simpler 
that will also give you a significant boost: a covering index.

Simply put, the engine is smart enough to not bother with row lookups if 
everything you asked for is already in the index it was using. You'll need to 
keep the index requirements in mind, of course (most selective fields first, 
order fields after selection fields, etc) and then append any other fields to 
the same index.

Hard to say more without actual use cases, of course, but it's well worth 
looking into as it requires no other changes in application or schema.

-- 
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: Performance boost by splitting up large table?

[Johan De Meersman]

- Original Message -
 From: Larry Martell larry.mart...@gmail.com
 Subject: Re: Performance boost by splitting up large table?
 
 This table is queried based on requests from the users. There are 10
 different lookup columns they can specify, and they can provide any or

That makes it rather more of a bother, as MySQL can't (yet) skip columns in an 
index, as far as I'm aware. Someone please correct me if I'm wrong here.


 all of these. Currently each one of the columns has an index on it.

I'm a bit fuzzy on multiple-index queries, but I think support isn't all too 
sharp.


 Would it be beneficial to create an index with all 10? Rarely are all
 10 specified in the query. Typically it's 3 or 4. Would it be
 worthwhile to see which are much commonly specified and create an
 index with just those? Or would it be better to put the commonly
 selected columns on the index with the lookup columns?


You may want to grab a day or week's worth of queries (either general_log with 
all the overhead and disk space that entails, or tcpdump) and pump that through 
pt-query-digest (Percona Toolkit) to see which combinations of fields are most 
often used, and add the necessary covering indices to help those queries.

A few points to keep in mind during analysis:
 * order of fields in the where clause is largely irrelevant (although 
most-selective-first is preferred)
 * not all the fields in the index must be queried; but you MUST query a full 
prefix set 
   - ie, for index (a, b, c, d) you can query (a, b) but for (a, c) only (a) 
will be used
 * every index constitutes a (small) performance penalty upon table updates, so 
don't go too wild either :-)

Also helpful:
 * plenty of memory, at least enough to keep the working set in memory
 * faster disks (SSD, or FusionIO things) helps bundles for data that doesn't 
fit in memory
 * if you notice a tendency for multiple users (say, a dozen) to run identical 
queries (and that means /bitwise/ identical query text, down to the last 
space), the query cache might help. Don't make it too big, though, a couple of 
meg should suffice.


-- 
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 Access denied error

[Johan De Meersman]

- Original Message - 
 From: Reindl Harald h.rei...@thelounge.net 
 
 i know that, but it does not change the fact that here 

Either you didn't know that but have trouble admitting it; or you did but 
conciously chose to be rude and condescending instead of helpful. 

Your choice. In the second scenario, I can only wonder what you're doing on 
this list. 

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

Re: Things to be considered before/after the OS patch or upgrade

[Johan De Meersman]

- Original Message -
 From: Dimitre Radoulov cichomit...@gmail.com
 Sent: Tuesday, 15 April, 2014 12:17:54 PM
 
 for major release upgrades - 5.x to 6.x - we'll use a different hosts.

I would like to point out that where MySQL is concerned, the minor versions are 
a major upgrade - 5.0-5.1, 5.1-5.5 and 5.5-5.6 are all MAJOR changes, and 
you would do well to go through the release notes for each version.


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