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 'utf8_general_ci' On Wed, Jun 3, 2015 at 3:14 PM, Emil Oppeln-Bronikowski e...@fuse.pl wrote: Is this a bug? If it's a feature, what could we do instead to get around this issue? Is your column set to unicode? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Fresh install of MySQL 5.6.23 fails to start on CentOS 7.0
Have you looked at the possible errors in http://man7.org/linux/man-pages/man3/pthread_create.3.html ? Seems like either a resource / ulimit thing, or perhaps limited by SELinux in some way? On Thu, Feb 19, 2015 at 12:11 AM, mike mdel...@rydin.com wrote: Cameron Mann cameron.mann at cybera.ca writes: Hi all, I've encountered a problem with MySQL 5.6.23 on CentOS 7.0 and would greatly appreciate any advice on what to do next. Synopsis: 1. Fresh install of CentOS 7.0 using minimal install ISO 2. yum update -y 3. rpm -i http://dev.mysql.com/get/mysql-community-release-el7- 5.noarch.rpm 4. yum install mysql-community-server -y 5. service mysqld start After installing mysql-community-server 5.6.23 on a fresh minimal install of CentOS 7.0 (running in VirtualBox 4.3.20) it will fail to start with the following error: Have you found any resolution 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 480 58 61 rik.was...@takeaway.com Takeaway.com B.V. - Brouwerijstraat 10, 7523 XD Enschede, The Netherlands -- Takeaway.com http://www.takeaway.com/ | Thuisbezorgd.nl http://www.thuisbezorgd.nl/ | Lieferando.de http://www.lieferando.de/ | Lieferservice.de http://www.lieferservice.de/ | Lieferservice.at http://www.lieferservice.at/ | Lieferservice.ch http://www.lieferservice.ch/ | Pyszne.pl http://www.pyszne.pl/ | Pizza.be http://www.pizza.be/ | Pizza.fr http://www.pizza.fr/ | Pizza.lu http://www.pizza.lu/ No rights can be derived from this message.
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: Transfer ENCRYPT password field to another server
From: Johan De Meersman vegiv...@tuxera.be To:J Gao j...@veecall.com @ 2013-07-19 From: J Gao j...@veecall.com Subject: Transfer ENCRYPT password field to another server So, is there a way to make the MySQL encrypted password string 2I6JOeg.JukJ. convert to MD5 hash $1$.? Nope. Encrypt() calls unix crypt(), which is really more a hash - you can't go back to the original. The proper way to handle this, is to notify the users that passwords will expire upon moving to the new system, and sending each of them a personal, unique link to set their new password. If in a hurry, yes indeed. To limit impact on your most active users, you can usually do something like on the application side for a while before migrating: 1) On login, try new hash method first 2) On fail, try old hash method 3) If old hash method succeeds, update hash to new hash method (or store it in a new location), as you now temporarily have the actual password. 4) After most active users have thus 'upgraded their password by just logging in', send out an e-mail to those who haven't with the (re)set password link Johan mentions, and retire the old hash method. (Do keep 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
I'm having an issue using regular expressions, and I was hoping someone
can see the syntax error that I'm missing (Monday morning, not enough
coffee, etc). Running the following query returns the error:
SELECT `a.custid`,`a.login` FROM ol.customers a WHERE `a.login` NOT_REGEXP
'^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
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
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://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: update doesn't
I get 1 row affected, but the status does not change when I look at the row. If I set it to 'X' it does change. To make it even more wacky, if I (using phpMyAdmin) change it to 'H' it will change and the row is shown change, but when I go to examine the row (using the pencil icon=Edit) it changes back to 'W'. Either there is something really strange or my mysql is possessed. I am using Server version: 5.1.63-0ubuntu0.10.04. Anyone have any thoughts about this or suggestions on how to debug it? 1) One thing that _could_ do this is a trigger. Does SHOW TRIGGERS; show any that could be doing this? 2) However, in 99.999% of cases, it is just a logic error in the application (be it your application or PHPMyAdmin), not anything in MySQL. Can you connect with the command line client, run the UPDATE statement, en then check what the SELECT shows? If it shows a correct 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 -- 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
I don't really know how to use those files and so like to know how to stop populating them. or at least control the number and size of them. Used for: disaster recovery / replay modifications on backup, or replication afaik. Days kept: expire_log_days: http://dev.mysql.com/doc/refman/5.5/en/server-system- variables.html#sysvar_expire_logs_days Max size: max_binlog_size: I'm running os x 10.6.8, mysql Server version: 5.1.61-log MySQL Community Server (GPL) I tried comment out the line log-bin=mysql-bin and those mysql-bin.xx files in the data directory. It causes mysql 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
Hm, due to some popups that was a premature sending... Mea culpa I don't really know how to use those files and so like to know how to stop populating them. or at least control the number and size of them. Used for: disaster recovery / replay modifications on backup, or replication afaik. Days kept: expire_log_days: http://dev.mysql.com/doc/refman/5.5/en/server-system- variables.html#sysvar_expire_logs_days Max size: max_binlog_size: http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary- log.html#sysvar_max_binlog_size (this is per file) I'm running os x 10.6.8, mysql Server version: 5.1.61-log MySQL Community Server (GPL) I tried comment out the line log-bin=mysql-bin and those mysql-bin.xx files in the data directory. It causes mysql server starting failure. Don't just delete the bin-files. First start with log-bin=off (or no log-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?
can you tell me why my this query doesn't use the index? mysql explain select * from iploc where 1902800418 between start_ip and end_ip; Hazarding a very quick guess: if this table is what I think it is (NON- overlapping IP ranges + (geo)location), you might want to try: SELECT * FROM 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://lists.mysql.com/mysql
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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mixing and matching mysql mssql whileloop within an if case
I could suggest a number of ways around the if/else construct, such as creating a subclass for the various servers with a uniform interface and hiding the differences inside the class. This, but I'd prefer using one of the already available abstractions for PHP: - PDO: http://www.php.net/PDO - DB: http://pear.php.net/package/DB - MDB2: http://pear.php.net/package/MDB2 - DBAL: http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/ ... let's not re-re-reinvent the wheel ;) I would also highly recommend NOT using the mysql extension anymore in PHP for new projects. Use 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://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
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 packages are special somehow? Re-installing mysql after deletig its data-directory didn't help unfourtunatly. I also don't use fancy configuration, all I did was to copy my-medium.cnf to /etc/my.cnf and enabled innodb. Hm, is the mysql-client library the same as the mysql-server? And does mysql --host=127.0.0.1 --user=someone -p somedb work (or it's actual IP-address, forcing the TCP/IP connect instead of possible sockets) ? -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can't connect as non-root user to database
Hm, is the mysql-client library the same as the mysql-server? Yes. Aight... And does mysql --host=127.0.0.1 --user=someone -p somedb work (or it's actual IP-address, forcing the TCP/IP connect instead of possible sockets) ? This is really strange - with -h127.0.0.1 I get the same error: ERROR 1045 (28000): Access denied for user 'someone'@'localhost' (using password: YES) However with -h192.168.1.102 everything works as expected, although I used 'someone'@'%' everywhere. Does '%' not include local connections / unix domain sockets? Hm, is a configuration file loaded? A ~/.my.cnf or something? And is there a socket defined there? See also http://dev.mysql.com/doc/refman/5.1/en/option-files.html, and check any of the mentioned files for 'socket' definitions: File Name Purpose /etc/my.cnf Global options /etc/mysql/my.cnf 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: weird difference in workbench and CLI query
When I run this query in workbench:
select c.acc_number 'Accession Number', e.DateExam 'MBI Exam Date',
s.ExamDate 'SJH Exam Date' from chestcon_log c
left join exams e on CONCAT(000,c.acc_number) = e.LastWordAccNum
left join sjhreports_ s on c.acc_number = s.AccessionNumber
WHERE
c.timestamp_exam = CAST(DATE_ADD(CONCAT(CURDATE(), ' 23:59:59'), INTERVAL
'-1' DAY) AS DATETIME) AND c.timestamp_exam =
CAST(DATE_ADD(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL '-14' DAY) AS
DATETIME)
I get this:
7330565 NULL 2012-02-01
66044192011-01-25 NULL
but when I run the same query in a shell script, on a Linux box or OS X, I
get this: 7330565 NULL2012-02-01
6604419 NULLNULL
I see mixes of single ' and double ... And this is the line that fails, the
only occurance of :
left join exams e on CONCAT(000,c.acc_number) = e.LastWordAccNum
Which 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
To unsubscribe:http://lists.mysql.com/mysql
Re: Suggestions for ibdata files structure
Dear all, I have some doubts regarding the configuration of Innodb files structure. As we know all myisam tables are stored in database directory and innodb use tablespaces ( ibdata1 ). In My application , databases are the mixture of mysql innodb storage engines. In my database ,size of innodb tables may be between 100 200 Gb may be some tables 300Gb.I have 4 Hds. /dev/sdb1 274G 225G 36G 87% /hdd2-1 /dev/sdc5 274G 225G 36G 87% /hdd3-1 /dev/sdd5 274G 219G 42G 85% /hdd4-1 /dev/sde1 266G 184G 69G 73% /hdd5-1 /hdd5-1/innodb_data5/ibdata100:8G;/hdd5-1/innodb_data5/ibdata101:8G:autoex tend [...snip...] But I don't think this is the correct way to store data for better performance. Do i need to set partitioning for bigger tables or some standard ways to configure table spaces in mysql. If you really want to partition it, I'd use table-level paritioning (http://dev.mysql.com/doc/refman/5.5/en/partitioning-overview.html), with a custom data-directory for different partitions. Keeps lookups on keys (assuming there is 1 major key by which records are fetched, otherwise, this might even degrade performance). Pros: - Per table config - Easily portable - Easy dropping recreating partitions - Can result in great performance gains due to partition pruning. Cons: - Could result in a degrade of performance when keys to partition by are chosen incorrectly, or the table needs a lot of lookups on different keys - Partitioning is done per table, so new tables need to have their config explicitly set. - Harder to control which percentages are on which disk (as it is data- related) However, the last part of that page should not be taken lightly: Other benefits usually associated with partitioning include those in the following list. These features are *not currently implemented in MySQL* Partitioning, but are high on our list of priorities. - Queries involving aggregate functions such as SUM() and COUNT() can easily be parallelized. A simple example of such a query might be SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id;. By “parallelized,” we mean that the query can be run simultaneously on each partition, and the final result obtained merely by summing the results obtained for all partitions. - Achieving greater query 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://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Question about deleting data and the undo log
Does drop table use the undo log (rollback segment) to temporarily store records to be purged later, the way delete from table does? As 'DROP TABLE' causes an implicit commit (http://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html), I would highly suspect that it doesnt. You cannot roll 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
Just to clarify having key indexes of (a,b) or (b,a) have no difference ?
They DO.
See it as lookup table which starts with 'a' in the first case, and 'b' in the
second one. Looking for anything that matches 'b' for an index (a,b) requires
a full scan as you don't know 'a', likewise searching for 'a' in an index
(b,a) requires a full scan. See it as looking through a phonebook trying to
locate someone by first- rather then lastname. It's in there, just not easily
accessible.
However, if you have an index on (a,b) and DO know which 'a' you want
('Smith'), looking for 'Smith, John' is faster with an index (a,b) then with
only an index on (a).
Johan was trying to explain this distinction:
- index (a,b) is good for searches on ONLY a or BOTH a b, but bad for ONLY
b
- index (b,a) is good for searches on ONLY b or BOTH a b, but bad for ONLY
a
- index (a) index (b) 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
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
Next question. If you have the two separate indexes and then do two queries, one for a and one for b. If you then get a list of unique id's of both, would it be faster to create an intersection yourself rather than have the server do the legwork? If you only have 2 unrelated indexes on a b, 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 archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index question
In this instance would you create four indexes key(a) key(b) key(a,b) key (b,a) ? Or is the decision based on the query response time ? Depends on the data and usage, but probably I'd go for a index(a,b) index(b,a) if reads heavily outnumber writes. As index(a) is covered by index(a,b), 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?
It is seems strange to me why the same command mysql -hlocalhost -uxxx when it is run from the command line, it will show an error. When it is run in a bash script, it runs fine. I guess this might be described somewhere, but I'm yet to find the answer. Does anybody know why this happens and how to make the command line version working? ~$ mysql -hlocalhost -uxxx ERROR 1045 (28000): Access denied for user 'xxx'@'localhost' (using password: YES) ~$ cat `which mysqllocalhostxxx.sh ` #!/usr/bin/env bash mysql -hlocalhost -uxxx ~$ mysqllocalhostxxx.sh Welcome to the MySQL 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?unsub=arch...@jab.org
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 the table schema of a pair of MySQL databases. Its output is a sequence of MySQL statements (CREATE/ALTER/DROP TABLE/COLUMN etc.), which if applied to the first database of the pair, will make its schema match that of the second. The web page is here: http://adamspiers.org/computing/mysqldiff/ and the manual page is here: http://search.cpan.org/dist/MySQL-Diff/bin/mysqldiff There are still a few open bugs in the tracker which need ironing out, mostly due to the evolution of MySQL itself since I first wrote this utility in 2000. Nevertheless I hope that some of you will already be able to benefit from the new life I'm trying to breathe into this little project. github forks are of course welcome too ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 4 minute slow on select count(*) from table - myisam type
thanks for the response - but do not believe queries are the issue because - Like I said - i have other websites doing the same exact queries as I am doing on the site with the 9gig table. Contrary to popular believe, size DOES matter... And having a table large enough so it doesn't fit 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/mysql?unsub=arch...@jab.org
Re: Mysql user kill privilege
I got a request from a client for the rights to kill his queries if something goes wrong. Long story short, he doesn't want to have to phone. I see via the MySQL documentation, that the client would need PROCESS and SUPER privileges. Im not happy with that. Would know of an alternative solution. I could reply, write better queries, but somehow I dont think the client would be to happy with that. A user can always kill his own queries. As long as he can have 2 simultaneous connections or more, killing his own queries should be simple. http://dev.mysql.com/doc/refman/5.1/en/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/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: running a duplicate database
Am 09.09.2011 11:09, schrieb Dave Dyer: Is there a halfway house between a single database and a full master-slave setup? I have a database with one piggish table, and I'd like to direct queries that search the pig to a duplicate database, where it won't affect all the routine traffic. I could definitely do this by setting up a slave server, but for my purposes it would be just as effective, and lots easier, if mysql could automatically maintain a duplicate of the database. Presumably this would work internally like an internal auto-slave, with a binary log of changes to the master database self-consumed to maintain the duplicate. As a bonus, I could backup the duplicate instead of the master, so that won't affect the routine traffic either. you can run as many slaves on the same machine as you want by using a different port for all instances and stop/backup/start one of them per script - doing this since years No problem indeed. it makes no sense maintain a duplicate of the databasefor backups becasue mysqld have to be stooped for effective rsync-backups which are much faster as dumps and here are we again at the point using a slave on a different port I concur that dumps are not an effective way of backup, they take ages when any decent size database. We have had great experiences with Percona's Xtrabackup (http://www.percona.com/docs/wiki/percona-xtrabackup:start) for hotcopies, which also work with InnoDB. 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: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: strange mysql update ..
I fired the update statement in a wrong way ..like this .. update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1 ; ( I forgot to use where . instead of where I used and ) update user_info set login_date='2011-08-05 04:15:05' where user_id =16078845 limit 1 ; ( this is the query intended ) after the update ..I got this message .. mysql update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1; Query OK, 1 row affected, 1 warning (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 It shows that one record is affected and one row changed .. I did show warnings ..the output is like this .. | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05' So my question is what happened exactly ? Why no records updated ? A lot of casting: (1) login_date='2011-08-05 04:15:05' and user_id =16078845; And implies boolean, so the result is the either true or false. MySQL doesn't like using non-numbers as booleans (the '2011-08-05 04:15:05') , this is the double spoken of. (2) login_date = false (or true, but that doesn't matter) But MySQL doesn't know booleans, to a number it is: (3) login_date = 0 But the column is a DATETIME (or TIMESTAMP) column, 0 or 1 is an incorrect value, cast to: (4) login_date = -00-00 00:00:00 So, somewhere there's (or was, may be overwritten) a record with that value, just 1 due to the limit 1, otherwise, the whole table would have that as a login_date (doesn't matter wether it was true or false). Check out: DB 5.1.58-1-log:(none) mysql SELECT 1 AND 1; +-+ | 1 AND 1 | +-+ | 1 | +-+ 1 row in set (0.00 sec) DB 5.1.58-1-log:(none) mysql SELECT 0 AND 1; +-+ | 0 AND 1 | +-+ | 0 | +-+ 1 row in set (0.01 sec) DB 5.1.58-1-log:(none) mysql SELECT '1' AND 1; +---+ | '1' AND 1 | +---+ | 1 | +---+ 1 row in set (0.03 sec) DB 5.1.58-1-log:(none) mysql SELECT 'a' AND 1; +---+ | 'a' AND 1 | +---+ | 0 | +---+ 1 row in set, 1 warning (0.03 sec) DB 5.1.58-1-log:(none) mysql SHOW WARNINGS; +-+--+---+ | Level | Code | Message | +-+--+---+ | Warning | 1292 | Truncated incorrect DOUBLE value: '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
On Wednesday 31 August 2011 16:39:52 Peter Brawley wrote:
While a transaction in one thread tries to update a non-existent InnoDB
row with a given key value, an attempt to insert that value in another
thread is locked out. Does anyone know where this behaviour is
documented?
-- connection 1
drop table if exists t;
create table t( lockid char(3), lockinfo char(8), primary
key(lockid,lockinfo) );
insert into t values('abc','def');
begin work;
update t set lockinfo='bar' where lockid='foo';
-- connection 2:
insert into t values('aaa','bbb');
Query OK, 1 row affected (0.00 sec)
insert into t values('foo','bar'); -- waits for connection 1 transaction
It has to do with transaction isolation levels. I assume REPEATABLE READ
by default for InnoDB:
http://dev.mysql.com/doc/refman/5.0/en/set-
transaction.html#isolevel_repeatable-read
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE),
UPDATE, and DELETE statements, locking depends on whether the statement
uses a unique index with a unique search condition, or a range-type
search condition. For a unique index with a unique search condition,
InnoDB locks only the index record found, not the gap before it. For
other search conditions, InnoDB locks the index 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.
Yes, that's what the question is about, it says for a unique key value,
as in this case, it locks only the index record found. There is no
index record. InnoDb behaves, though, as if there is one. Where is the
documentation for that?
Is says it employs different strategies, I see it as:
(unique index with a unique search condition = InnoDB locks only the index
record found)
OR
(other search conditions = InnoDB locks the index range scanned, using gap
locks or next-key )
And 'lockinfo' is most certainly in your index, a primary key is a special
kind of UNIQUE, but still unique. The INSERT cannot complete as the
(lockid,lockinfo) location (foo,bar) is locked, until commit or rollback. See
the index as a seperate storage from your normale table-rows which your INSERT
needs to put something in to complete the insert. The UPDATE has locked that
location, so the INSERT waits for that. That's the 'index record' they're
talking 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 to update a non-existent InnoDB row with a given key value, an attempt to insert that value in another thread is locked out. Does anyone know where this behaviour is documented? [JS] Forgive my ignorance, but I thought that was standard behavior for a row- or row-range lock (not just MySQL) in any DBMS that supported row locking. (Back when these things were first being invented, one term was predicate locking.) The general idea was that you are locking rows that meet certain criteria, whether any or all of them exist or not. You're locking not only the existence, but the potential existence, of those rows. I would expect it to apply not only to keys, but to any set. For example, SELECT * FROM `t` WHERE `t`.`x` 3 FOR UPDATE; should lock all rows where `t`.`x` 3 for update, insertion, or deletion -- regardless of whether or not `x` is a key. Otherwise you have no way of knowing who wins. The ability to lock non-existent records is critical. I concur, although this is just a transaction consisting of 1 statement :). -- 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
While a transaction in one thread tries to update a non-existent InnoDB
row with a given key value, an attempt to insert that value in another
thread is locked out. Does anyone know where this behaviour is documented?
-- connection 1
drop table if exists t;
create table t( lockid char(3), lockinfo char(8), primary
key(lockid,lockinfo) );
insert into t values('abc','def');
begin work;
update t set lockinfo='bar' where lockid='foo';
-- connection 2:
insert into t values('aaa','bbb');
Query OK, 1 row affected (0.00 sec)
insert into t values('foo','bar'); -- waits for connection 1 transaction
It has to do with transaction isolation levels. I assume REPEATABLE READ by
default for InnoDB:
http://dev.mysql.com/doc/refman/5.0/en/set-
transaction.html#isolevel_repeatable-read
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE,
and DELETE statements, locking depends on whether the statement uses a unique
index with a unique search condition, or a range-type search condition. For a
unique index with a unique search condition, InnoDB locks only the index
record found, not the gap before it. For other search conditions, InnoDB locks
the index 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=arch...@jab.org
Re: Best Way to store Hierarchical Data in Mysql
I researched a lot on storing Hierarchical data in mysql tables. Below links : http://scvinodkumar.wordpress.com/2011/01/30/managing-hierarchical-data-in- mysql/ http://www.sitepoint.com/hierarchical-data-database-2/ shows different ways to store. But is there any standard way of doing this fore.g storing twitter tweets or comments on a link etc. Most used is de adjacency model, but it depends on how you are going to use it, and how deep hierarchical data goes / can go. Also a good source of information: - http://www.slideshare.net/billkarwin/models-for-hierarchical-data (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?unsub=arch...@jab.org
Re: does mysql support master to master replication
Any mysql instance can replicate from any other as long as binary logging is enabled on the designated master. Two instances can replicate from one another yes, but precautions must be taken especially in the case of active-active configuration. Works perfectly indeed, to name the precautions to take: 1, Set proper server_id's 2. When using auto-incrementing fields, set the auto_increment_offset auto_increment_increment. 3. If you write on 2 servers simultaneously, UNIQUE indexes can break replication. If your application requires both enforcing them AND writing to both servers, 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
2011/08/02 12:11 +0530, Adarsh Sharma select p.* from table A p, B q where p.id=q.id or select p.* from table B q , A p where q.id=p.id Why do people constantly change table names for queries, although, as here, it gain them nothing? It often makes for less clarity (for which table is this an alias???). Depens on your table names. I rather like being able to give a short description rather then long table names if someone decided that as a tablename. I doubt your example with already short tablenames is one from real life, but if you saw someone doing it would indeed be a waste of time. But the main thing is it helps to distinguish tables in joins having the same table more then once (and of course results from subqueries etc.): SELECT first.* FROM tablename first LEFT JOIN tablename second ONfirst.some_id = second.some_id AND first.id != second.id 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
[Note to self, reply to mailinglist, not to author ;) ] i found a strange problem . when i using index for 'select' , i got a slower result than without index . i have a tabe : create table geo_query ( `id` int(10) unsigned not null auto_increment , `start` bigint(20) unsigned not null , `end` bigint(20) unsigned not null, `desc` varchar(1000) not null, primary key (`id`) , key `range` (`start`,`end`) ) engine=myisam ; the whole table contains 43 rows . 1, the query ' select * from geo_query where 1988778880 between start and end ;' used 0.15 second ; and i used 'explain' and found that it didn't use index and scanned the whole table . 2, so i changed the query for ' select * from geo_query force index(`range`) where 1988778880 between start and end ;' . it used 0.36 second . i can't figure it out .why the query used index spend more time than not ? any comment appreciate : ) The query optimizer examined your answer, and decided a full-table scan was faster then using an index. It estimated it would require less IO operations to read the table in sequence in this case then reading the index fetching the appropriate records from the table. Turned out if was right. This is often the case when large portions of a table (or index) could possibly matched by the first guess. Here, the first 'guess' is that everything below start = 1988778880 is a possible match (as it's first field of the index `range`). Every one of them has to be verified of having an `end` your number, and has to fetch the appropriate record if it does which is costly in harddisk IO. BTW: as this looks as a GeoIP query, based on IP, 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...@jab.org
Re: dumb question?
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 to use near 'DMPPRuser) VALUES ('MYNAME')' at line 1
You'll need to enable ANSI_QUOTES to be able the use '' as an
identifier quote character rather then MySQL's default '`'.
See:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_ansi_quotes
This can be done in the server configuration, so no client/lasso
alterations are necessary.
--
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 to use near 'DMPPRuser) VALUES ('MYNAME')' at line 1
You'll need to enable ANSI_QUOTES to be able the use '' as an
identifier quote character rather then MySQL's default '`'.
See:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_ansi_quotes
Oh, and if possible, go for even more standards-compliant:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_ansi
--
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 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
They surely can, and in this case, are.
so
TABLE table_name
FOO VARCHAR(30)
/snip
where the INSERT statement for table table_name column FOO would look like:
INSERT INTO table_name FOO VALUES('VALUES ARE ALWAYS SURROUNDED BY TICK MARKS
UNLESS ANSI_QUOTES ARE ENABLED');
May I quote:
(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 to use near 'DMPPRuser) VALUES ('MYNAME')' at
line 1
See the double quotes around the column name? Aye? 'nuff said.
Bedankt,
Martin
Graag gedaan,
--
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: Data missing after field optimization
On Jun 6, 2011, at 10:06 PM, Johan De Meersman wrote: What exactly do you mean by removing the NULL default? Did you set your colums NOT NULL? Yes. That's all I did. In stead of getting info drop-by-drop, you might want to share the output of SHOW CREATE TABLE...,, but my guess 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 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.
On 2011-05-24 18:25:07 Johan De Meersman wrote: OK, but that would mean that the answer to the question: I may be wrong here, but I tend to interpret this as '140054029002496' is trying to get an exclusive lock on 0x78733f8, on which it already has an exclusive lock, and hence is deadlocked in some manner is 'no there is another query' (i.e.: it isn't locked on mistakingly acquiring a lock it already has) rather then 'that seems likely' :) Ah, misread that. Yes, the former behaviour seems more like a bug; which is not entirely impossible of course. Ack. And in my case, the server became unusable (kept running into semaphore locks at 769 seconds before a kill start was given). Query timeouts / crashes I can live with, an unresponsive server I cannot... Which is what kind of mystifies me - it should detect deadlocks as soon as they happen. Well, usually it does :) OK, let's hope I never get to show that output (i.e: that the problem doesn't reoccur). Since the server has been restarted since-start counters will probably be pretty useless. Yups. A trending database (munin, cacti or something) may or may not offer much hindsight in this case (mostly a matter of luck at when it last checked); but it's definitely something useful to have at hand for plenty of other purposes. Cacti does store a lot of things by snmp, that's the way I know memory, CPU usage average load never showed a hitch, all's well according to the OS, only MySQL is slowly dying... Yup, right there it did, And that's the way I like it: kill the/a query, which issues an error somewhere else we know if and how to handle in some application, rather 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). Uhuh. You may want to take this to the mysql-dev mailinglist, the good people there might have a bit more insight about 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
On 2011-05-25 10:35:45 Brent Clark wrote: I think I read somewhere that Mysql 5.5 is defaulting on Innodb as its default storage engine. The question I would like to ask is. For those still running Mysql 5.0 / 5.1. Have any of you set the mysql default variable to be / use Innodb? Yes, I have, it's one of the first things I usually do/did in a new 5.0/1 setup, and it works out OK, allthough there are major improvements in 5.5 for InnoDB, so an upgrade from 5.0/1 to 5.5 may result in a lot less resource usage. It's just a setting, can be set globally or in a session: http://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
failed to execute SELECT * FROM cluster_info WHERE cluster = ?: MySQL server has gone away The error MySQL server has gone away is the error from the db handle. Can anyone give me any pointers on why that happens? I looked up the documentation in MySQL docs and the most common reason seems to be that it happens if the query size is very large or if there is a timeout. None of them seems to be a probable cause. The max_allowed_packet on the server is 16 MB and as can be seen in the query above, the query is very small and nowhere near the size limit. We also have a timeout setting (wait_timeout) of 10 minutes and the above query for us cannot possibly take that amount of time. In any case, given the same query, it executes correctly 99% of time (so to speak). It fails intermittently with the above error. What possibly could be the reason? I also looked at the max connections on the server at that time (around ~80) and it is much less than the limit we have (limit is 1000). How can I extract more information when this happens? This error message sucks since it does not tell me what exactly happened. The server version is 5.1.45. Can you access the error 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.
On 2011-05-25 12:24:37 you wrote: Cacti does store a lot of things by snmp, that's the way I know memory, CPU usage average load never showed a hitch, all's well according to the OS, only MySQL is slowly dying... So what stops you from writing MySQL plugins? Probably plenty out 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:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Joining tables from different Instances
On 2011-05-25 13:50:32 Ramesh wrote: I have a different schemas in different instances. I would like to join the tables in different instances for the required result. Is it possible? Example = Server I - table_1a, table_2b Server II - table_2a,table_2b. I want to join 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 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.
389;; 7: len 4; hex 8003; asc ;; 8: len 4; hex 8000; asc ;; 9: len 4; hex 8000; asc ;; 10: len 4; hex 8000; asc ;; 11: len 11; hex 76657273696f6e20322e30; asc version 2.0;; *** (2) TRANSACTION: TRANSACTION CE26923, ACTIVE 0 sec, process no 11546, OS thread id 139921021904640 starting index read, thread declared inside InnoDB 362 mysql tables in use 2, locked 2 81 lock struct(s), heap size 14776, 2663 row lock(s) MySQL thread id 1022784, query id 5537983 46.144.148.174 utr-fax Sending data UPDATE `order` o, connectie c SET o.datum=o.datum, o.bcDeviceId=11, o.bcStatus= IF(o.bcStatus = 9, 10, 8), o.bcInBehandeling='2011-05-24 01:30:47' WHERE o.order_statusOrder_status_id=2 AND o.connectieId=c.id AND (c.bellen=2 OR c.bellen=3) AND o.bcDeviceId=0 AND ( o.bcStatus=0 OR o.bcStatus=9 ) AND o.bevestigingsId=0 AND o.datum = '2011-05-23 01:30:47' AND o.datum '2011-05-24 01:30:37' *** (2) HOLDS THE LOCK(S): SNIP LIST OF LOCKS *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 987 page no 48 n bits 328 index `PRIMARY` of table `tname`.`connectie` trx id CE26923 lock mode S locks rec but not gap waiting Record lock, heap no 241 PHYSICAL RECORD: n_fields 12; compact format; info bits 0 0: len 4; hex 8000146a; ascj;; 1: len 6; hex 0ce26925; asc i%;; 2: len 7; hex 5c1ba11c2a; asc \ *;; 3: len 4; hex 8010; asc ;; 4: len 9; hex 70697a7a6174657374; asc rtttest;; 5: len 9; hex 70697a7a6174657374; asc rtttest;; 6: len 3; hex 333638; asc 368;; 7: len 4; hex 8002; asc ;; 8: len 4; hex 8000; asc ;; 9: len 4; hex 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://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: After long semaphore waits MySQL becomes unresponsive.
, but something we have failovers for) or killing queries (which we can handle). -- 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
