Re: Issue with em dash character

LENGTH() measures bytes, CHAR_LENGTH() measures characters. There's little use for LENGTH() for anything else then raw bytes. On Wed, Jun 3, 2015 at 10:29 PM, Robert Voliva rvol...@gmail.com wrote: information_schema.columns reports a character_set_name of 'utf8' and a collation_name of

Re: How to change character sets in InnoDB as fast as possible

Yes, normally convert from latin1 to binary, and from binary to utf8 (which would also be the recipe to convert actual utf8 data which accidentally ended up in latin1 columns to the proper definition without changing the content). I would not know why that would take overly long for you. An

Re: Fresh install of MySQL 5.6.23 fails to start on CentOS 7.0

for this as I'm having the exact same issue? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Regards, Rik Wasmus Head of Systems Administration T +31 53 480 58 63 M +31 6 380 67 332 F +31 53

Re: Fresh install of MySQL 5.6.23 fails to start on CentOS 7.0

Dammit, that should've come from the other account with that work-signature...

Re: unicode case insensitive but diacritics sensitive

Not a unicode one that I know of, converting it to latin1 for the grouping works for that particular use case, but I can't make any promises how it'd work on your entire set which may hold any unicode character, a lot of which cannot be converted to latin1: mysql SET NAMES utf8; Query OK, 0 rows

Re: Transfer ENCRYPT password field to another server

in mind password resets etc. also need to know about the multiple hashing methods in use.) -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql

Re: NOT_REGEXP Query Help

'^anonymous[[:digit:]]{3,}$'; Error: MySQL said #1064 - 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 'NOT_REGEXP ' It's NOT REGEXP ... without the underscore... -- Rik Wasmus -- MySQL General Mailing List

Re: Temporary table creation fails

the message ERROR 1005 (HY000): Can't create table '/tmp/#sql4a27_68eed1_0.frm' (errno: -1) Basics first: 1) Is the /tmp directory write readable for the user mysql runs as? 2) Has the /tmp directory enough free space? -- Rik Wasmus -- MySQL General Mailing List For list archives: http

Re: update doesn't

result... the problem ain't in MySQL itself. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql

Re: manage mysql-bin.xxxxxx files on mac

(1) What server fail to start, it always says that missing mysql.sock I seem to remember some distro's switching over from mysql.sock so mysqld.sock... is that mysqld.sock there after restart? If so, just update your socket configuration accordingly / make it consistent. -- Rik Wasmus

Re: manage mysql-bin.xxxxxx files on mac

server starting failure. Hm, what output -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql

Re: manage mysql-bin.xxxxxx files on mac

-bin declaration). If that fails to start your server, show us the actual error. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql

Re: why this query doesn't use index?

iploc WHERE start_ip 1902800418 ORDER BY start_ip DESC LIMIT 1 ... at least, that is how we solved our geoip-performance problems. The NON- overlappig part is crucial though. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http

Re: Subquery taking too much time on 5.5.18?

See The unbearable slowness of IN() at http://www.artfulsoftware.com/infotree/queries.php Do you read your own links? Excerpt: In 5.0, EXISTS() is much faster than IN(), but slower than JOIN. In 5.5, EXISTS() performs about as well as JOIN. So judging by the subject line... -- MySQL

Re: mixing and matching mysql mssql whileloop within an if case

mysqli (mysql Improved) or PDO_MySQL. Also, learn to love prepared statements. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql

Re: One inst has 39 columns- the other 40

You keep repeating that, and I already replied to it the first time. If my answer isn't acceptable, spamming the question isn't going to make it better - or make people more inclined to help you, for that matter. I concur -- Rik Wasmus -- MySQL General Mailing List For list archives: http

Re: Can't connect as non-root user to database

Hi Rik, Have you tried the _full_ user-identification (which is with host)? GRANT ALL ON somedb.* TO 'someone'@'%'; Yes I had (and just tried to verify) but it didn't help. Oops, didn't use reply-to-list instead of reply-to-author, my apologies... Could it be Fedora's mysql

Re: Can't connect as non-root user to database

Global options (as of MySQL 5.1.15) SYSCONFDIR/my.cnf Global options $MYSQL_HOME/my.cnf Server-specific options defaults-extra-file The file specified with --defaults-extra-file=path, if any ~/.my.cnf User-specific options -- Rik Wasmus -- MySQL

Re: weird difference in workbench and CLI query

my crystal ball tells me is probably a sign of improper escapes in shell scripts. What happens if you replace that line with: left join exams e on CONCAT('000',c.acc_number) = e.LastWordAccNum -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql

Re: Suggestions for ibdata files structure

throughput in virtue of spreading data seeks over multiple disks. Not implemented yet... I personally have those 2 high on my wishlist ;) -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql

Re: How to get a specific number of entries per one key ?

Taking the question _very_ literal: A quick guess, or at least a starting point: SELECT key, data , SUBSTRING_INDEX(GROUP_CONCAT(data ORDER BY data SEPARATOR ','),',',2) FROM myTable GROUP BY key remove limit here -- Rik Wasmus -- MySQL General Mailing List For list archives: http

Re: Question about deleting data and the undo log

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

Re: Global Variables

DO NOT REPLY OFF-LIST Also, do not shout :-) Ugh point taken, but why are you still replying to him off-list? Keep inboxes clean! ;) -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql

Re: Index question

) is good for searches on ONLY b or ONLY a, and is suboptimal for searching for BOTH a,b (although, faster then no index, but the query optimizer has to choose which index to use, can't use both). -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql

Re: Index question

, it depends on the data, the distribution of values, etc. No single answer here, test with your data and you'll have the results. If you need it often, I'd go for the combined index let MySQL do the work, which is probably fastest. -- Rik Wasmus -- MySQL General Mailing List For list

Re: Index question

), and index(b) by index(b,a), we don't need to add those, which saves time on modifications. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Why the same command have different results when it is in a bash script and when it is not?

monitor. Commands end with ; or \g. What does 'which mysql' give you? Is it perhaps in 'alias -p'? And is there perhaps a .my.cnf file in your users directory? -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql

Re: mysqldiff resurrected and 0.43 released

Looks very nice, Ill check it out next week. Thanks for the work! -- Rik Wasmus After a very long hiatus from maintainership (several years), I have finally released a new version of MySQL-Diff, the CPAN module suite which also contains mysqldiff, a CLI-based frontend tool for comparing

Re: 4 minute slow on select count(*) from table - myisam type

in memory could require another approach entirely for query optimization. Another good start would be to examine the output of mysqlreport, it will tel you a lot. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com

Re: Mysql user kill privilege

/kill.html If you have the PROCESS privilege, you can see all threads. If you have the SUPER privilege, you can kill all threads and statements. Otherwise, you can see and kill only your own threads and statements. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com

Re: running a duplicate database

. But on a heavily used db-server, it DOES make sense to run the backup on a (unused) slave, there's still some overhead locking involved, and if your DB is running hot 24/7 you don't want that one to do anything that can be done somewhere else. -- Rik Wasmus -- MySQL General Mailing List For list archives

Re: strange mysql update ..

: 'a' | +-+--+---+ 1 row in set (0.01 sec) -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: locked non-existent row

about I gather. Also: please respond to the list only, I have enough mail as it is, responding to the list keeps things nicely organized :) -- Rik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: locked non-existent row

-Original Message- From: Peter Brawley [mailto:peter.braw...@earthlink.net] Sent: Wednesday, August 31, 2011 10:40 AM To: r...@grib.nl; mysql@lists.mysql.com Subject: Re: locked non-existent row On 8/31/2011 4:50 AM, Rik Wasmus wrote: While a transaction in one thread tries

Re: locked non-existent row

range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub

Re: Best Way to store Hierarchical Data in Mysql

(nice concise comparison of 4 alternatives) - http://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management- Systems/dp/1558609202/ref=ntt_at_ep_dpt_2 -- Regards, Rik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql

Re: does mysql support master to master replication

, look at clustering instead. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Too many aliases

WHERE second.id IS NULL -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: index problem

, if the `start` `end` ranges cannot overlap, this is probably faster: SELECT * FROM geo_query WHERE 1988778880 start ORDER BY start DESC LIMIT 1. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch

Re: dumb question?

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

Re: dumb question?

On Wed, 2011-07-06 at 16:27 +0200, Rik Wasmus wrote: On Wed, 2011-07-06 at 07:15 -0700, XL Cordemans wrote: (error code) 1064 (error message) HY000 [Actual][MySQL] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

RE: dumb question?

On Wed, 2011-07-06 at 11:03 -0400, Martin Gainty wrote: Rik and Crew Please keep your replies to the list only, I don't need to double on e-mail... String values are always ticked VALUES('MYNAME') unless ANSI_QUOTES are enabled Column names are never surrounded by ticks or double quotes

Re: Data missing after field optimization

is you had a UNIQUE key somewhere that got violated when converting to NULL's to empty strings. If would require an ALTER IGNORE TABLE... instead of a normal ALTER TABLE though. That, or an outside source (code we cannot see querying for NOT NULLs etc.) -- Rik Wasmus -- MySQL General Mailing

Re: After long semaphore waits MySQL becomes unresponsive.

the error runes you posted. OK, will do, thanks for the help, maybe I'll also file a bug, seems something that should be fixed :) -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Innodb as its default storage engine for Mysql 5.0 / 5.1

://dev.mysql.com/doc/refman/5.1/en/server- options.html#option_mysqld_default-storage-engine -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: MySQL server has gone away

log of the server? That can probably shed more light on the issue... -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: After long semaphore waits MySQL becomes unresponsive.

there already, too. A plugin what for? Also time, money, effort. I didn't get hired as a MySQL- plugin writer here, and my list of pet-projects for my personal time is rather full at the moment :) -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe

Re: Joining tables from different Instances

the table_1a with table_2b. Options as far as I can see it: 1. Use a FEDERATED table (not advisable, excrutiatingly slow and inefficient). 2. Replicate table_2b from Server II to Server I if possible, and allow for a small delay, and just run the query locally on Server I -- Rik Wasmus -- MySQL

After long semaphore waits MySQL becomes unresponsive.

needed). Thanks, -- Rik Wasmus Some more InnoDB Monitor Output: = 110523 19:19:05 INNODB MONITOR OUTPUT = Per second averages calculated from the last 45 seconds - BACKGROUND THREAD

Re: After long semaphore waits MySQL becomes unresponsive.

8000; asc ;; 10: len 4; hex 8001; asc ;; 11: len 11; hex 76657273696f6e20322e30; asc version 2.0;; *** WE ROLL BACK TRANSACTION (1) Thanks, -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http

Re: After long semaphore waits MySQL becomes unresponsive.

then letting a database server with a light load grind to a halt. My main problem at hand is why the server did nothing but seize up gracelessly, rather then either dying (a last resort, but something we have failovers for) or killing queries (which we can handle). -- Rik Wasmus -- MySQL

Re: mysql database

I hope you went at least to www.mysql.com ;) - Second tab Developer Zone. - First tab Downloads from the second menubar. - Select version of your database - Select your OS - Pick a mirror - Have fun Rik Op donderdag 16 december 2004 14:28, schreef Serenity Schindler: Okay, extreme newbie. I

Re: phpMyAdmin and MySQL 4.1

runs without problems Notes: - this was on my development machine - I'm not pushing mysql to the end when I use it (in a basic way) - maybe not the nicest solution, but digging 2 days through documentation isn't nice either. - I got my lamp working in less then 2 hours. Rik You need to get

Re: is 'start' a keyword?

mentioned. Anyway; it works rik Op vrijdag 15 oktober 2004 04:35, schreef Brian Reichert: On Thu, Oct 14, 2004 at 10:33:45PM -0400, Brian Reichert wrote: I tried to create a table with a column named 'start'. This word does not appear here: http://dev.mysql.com/doc/mysql/en

Stuck in an upgrade to mysql 4.1 and other pains ...

googling many hours did'nt give me the right answer). Thanks, Rik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]