Re: next-key lock
Dear Akshay, ASFIK, normal selects are always non-locking read and they do not put any locks. Select..., Select..where..,Select where..between Does above select statement will use next-key locking and/or gap locking? I dont think so. Please correct me if I am wrong. Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Trainer and Database Security Specialist On Fri, Aug 29, 2014 at 11:46 AM, Akshay Suryavanshi < akshay.suryavansh...@gmail.com> wrote: > Geetanjali, > > There is a difference between next-key locking, gap locking and locking > reads. > > Next-key locking and gap-locking are used with normal Selects statement in > Innodb, whereas locking reads wont release a lock on the whole column until > transaction completed, and not just selected values. > > May be you can try your example with SELECT... LOCK IN SHARE MODE; > > Cheers!!! > Akshay Suryawanshi > > > On Fri, Aug 29, 2014 at 11:22 AM, geetanjali mehra < > mailtogeetanj...@gmail.com> wrote: > >> Thanks for your reply. >> >> I read those docs. Still my doubt is at the same stage. Please clarify >> the >> same to me. >> Should not other sessions be allowed to insert the rows beyond that >> range.? >> >> As far as I understand, Innodb brought the concept of next-key locks so as >> to prevent phantom problem. So, it is clear to me that issuing the below >> query >> >> Select * from new where c1 between 12 and 17 for update; >> >> will not allow other sessions to insert any value between 12 and 17. >> >> But if i am trying to insert 20 from other session, it is not allowed. Why >> this is so? The session is hanging. >> >> Best Regards, >> Geetanjali Mehra >> Senior Oracle and MySQL DBA Corporate Trainer and Database Security >> Specialist >> >> >> >> On Thu, Aug 28, 2014 at 2:26 AM, shawn l.green >> wrote: >> >> > >> > >> > On 8/26/2014 1:12 AM, geetanjali mehra wrote: >> > >> >> Hello to all, >> >> In repeatable read isolation level, when we issue: >> >> >> >> Select * from new where c1 between 12 and 17 for update; >> >> >> >> this range will be locked by innodb by using next-key locks. >> >> >> >> But, why is is preventing any other session to insert any value beyond >> >> that >> >> range; any value above the range and any value below the range. I am >> >> unable >> >> to understand this. >> >> >> > >> > I believe you are confusing gap locking (the space between the values) >> and >> > next-key locking (the space after the range). >> > >> > http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html >> > >> > See also: >> > http://dev.mysql.com/doc/refman/5.6/en/innodb-next-key-locking.html >> > http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html >> > >> > >> > >> >> Best Regards, >> >> Geetanjali Mehra >> >> Senior Oracle and MySQL DBA Corporate Trainer and Database Security >> >> Specialist >> >> >> >> >> > Yours, >> > -- >> > Shawn Green >> > MySQL Senior Principal Technical Support Engineer >> > Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. >> > Office: Blountville, TN >> > >> > -- >> > MySQL General Mailing List >> > For list archives: http://lists.mysql.com/mysql >> > To unsubscribe:http://lists.mysql.com/mysql >> > >> > >> > >
Re: Query regarding implementation of parallel-replication
Ping !! :) On Mon, Sep 1, 2014 at 8:27 AM, Ajay Garg wrote: > Hi all. > > > We have replication set-up, where we cater to HUUGEE amounts of data. > Since quite some time, we have been facing issues wherein the slave > lags behind master quite a lot. > > > So, yesterday we were able to setup parallel replication, by > incorporating the following changes :: > > a) > To begin with, partitioned some tables into dedicated databases. > > b) > Set up the "slave-parallel-workers" parameter. > > > The above seems to work functionally fine, but we have one doubt/query > about the scalability of this solution. > > > > > First, I will jot down the flow as far as I understand (please correct > if wrong) :: > > """ > Even in parallel-replication scenario, the master writes all the > binlog (combined for all databases) in just one file, which then gets > passed onto the slave as single-file itself. Thereafter, all the > replication commands (combined for all databases) are written > sequentially onto one slave-relay file. > > Thereafter, as per the documentation, the slave-SQL-Thread acts as the > manager, handing over commands to worker-threads depending upon the > databases on which the commands run. > """ > > > > So far, so good. > However, what would happen if the slave-relay file contains the following :: > > > db1-statement-1 (short-running) > db2-statement-1 (very, very long-running) > db2-statement-2 (short-running) > db1-statement-2 (short-running) > db1-statement-3 (short-running) > > > We will be grateful if someone could please clarifiy, as to how the > above statements will be managed amongst the Manager and the > Worker-Threads (let's say there is just one worker-thread-per-db) ? > > In particular, does the Manager thread creates internal > slave-relay-log-files, one for per database-statements? > > > > Thanks and Regards, > Ajay -- Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: converting numeric to date-time?
* Rajeev Prasad [2014-09-01 17:55]: > I have a column in a table which is epoch time including milliseconds. > > e.g. = 1409304102153 > > > now i want to display all fields in the table but this field as: "2014-8-29 > Fri 09:21:42: GMT" (whatever comes in ) > > > and i am not finding anything on web about how to do that. > > can anyone help please. > > ty. > Rajeev I do not know how to do it directly in MySQL, but if you can dump the table and post-process, this may be helpful on Linux: The date(1) cmd can translate between formats, e.g. (taking the above value), date -d "@1409304102.153" "+%Y-%m-%d %a %H:%M:%S.%N" 2014-08-29 Fri 04:21:42.15300 --Phil -- Philip Amadeo Saeli openSUSE, CentOS, RHEL psa...@zorodyne.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
converting numeric to date-time?
I have a column in a table which is epoch time including milliseconds. e.g. = 1409304102153 now i want to display all fields in the table but this field as: "2014-8-29 Fri 09:21:42: GMT" (whatever comes in ) and i am not finding anything on web about how to do that. can anyone help please. ty. Rajeev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: ubuntu 12.04 not running latest phomyadmin, even though installed.
when i run the application like this: http://mywebsite.com/phpMyAdmin/index.php on the home page it shows i am using old version: "Version information: 3.4.3.1, latest stable version: 4.2.7.1" but i have latest phpmyadmin installed (see below) why is it not appearing? MySQL client version is 5.5.38 $ cat /etc/phpmyadmin/apache.conf # phpMyAdmin default Apache configuration Alias /phpmyadmin /usr/share/phpmyadmin Options FollowSymLinks DirectoryIndex index.php AllowOverride All AddType application/x-httpd-php .php php_flag magic_quotes_gpc Off php_flag track_vars On php_flag register_globals Off php_admin_flag allow_url_fopen Off php_value include_path . php_admin_value upload_tmp_dir /var/lib/phpmyadmin/tmp php_admin_value open_basedir /usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/ # Authorize for setup AuthType Basic AuthName "phpMyAdmin Setup" AuthUserFile /etc/phpmyadmin/htpasswd.setup Require valid-user # Disallow web access to directories that don't need it Order Deny,Allow Deny from All Order Deny,Allow Deny from All $ $ $ dpkg -s phpmyadmin Package: phpmyadmin Status: install ok installed Priority: extra Section: web Installed-Size: 15113 Maintainer: Ubuntu Developers Architecture: all Version: 4:3.4.10.1-1 Depends: libapache2-mod-php5 | libapache2-mod-php5filter | php5-cgi | php5-fpm | php5, php5-mysql | php5-mysqli, php5-mcrypt, perl, debconf (>= 0.5) | debconf-2.0, dbconfig-common, ttf-dejavu-core, ucf (>= 0.28) Recommends: apache2 | lighttpd | httpd, php5-gd, mysql-client Suggests: mysql-server, www-browser Conffiles: /etc/phpmyadmin/apache.conf 840caa9c589c9a84213c2c6ef9fb5a39 /etc/phpmyadmin/config.footer.inc.php f59a8fe4a05431392ad1ef4e0b36278f /etc/phpmyadmin/config.header.inc.php ff471619d3280ef72b7b0641bab4ba27 /etc/phpmyadmin/config.inc.php f5201f1a5c501d2f0bacc551d9bfedce /etc/phpmyadmin/lighttpd.conf 72d500d79840a38eff5dca64bfe02198 /etc/phpmyadmin/phpmyadmin.desktop 18948893751469fa6a6a910db9714909 /etc/phpmyadmin/phpmyadmin.service f79a2a07e93b221f646a75e894496ef0 Description: MySQL web administration tool This package allows administering of MySQL with a web interface. . It allows administrators to: - browse through databases and tables; - create, copy, rename, alter and drop databases; - create, copy, rename, alter and drop tables; - perform table maintenance; - add, edit and drop fields; - execute any SQL-statement, even multiple queries; - create, alter and drop indexes; - load text files into tables; - create and read dumps of tables or databases; - export data to SQL, CSV, XML, Word, Excel, PDF and LaTeX formats; - administer multiple servers; - manage MySQL users and privileges; - check server settings and runtime information with configuration hints; - check referential integrity in MyISAM tables; - create complex queries using Query-by-example (QBE), automatically connecting required tables; - create PDF graphics of database layout; - search globally in a database or a subset of it; - transform stored data into any format using a set of predefined functions, such as displaying BLOB-data as image or download-link; - manage InnoDB tables and foreign keys; and is fully internationalized and localized in dozens of languages. Original-Maintainer: Thijs Kinkhorst Homepage: phpMyAdmin phpMyAdmin Download 4.2.8 Try demo Donate GSoC 2014 Platinum sponsor Gold sponsor Silver sponsors Sponsorship See other sponsors and information for new sponsors. About View on www.phpmyadmin.net Preview by Yahoo under my web folder tree, under www, i have folder for phpmyadmin - everything is there - i think i have to do something to it?? mywebsite/www/phpmyadmin.. I even copied all of /usr/share/phpmyadmin to replace this, but it still shows old version :(( pl help. ty. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: fragmentation in innodb index
Thanks to all, Best Regards, Geetanjali Mehra Senior Oracle and MySQL DBA Corporate Trainer and Database Security Specialist On Fri, Aug 29, 2014 at 11:59 AM, Hartmut Holzgraefe wrote: > On 08/29/2014 08:15 AM, geetanjali mehra wrote: > > But after doing *optimize table*, the value inside > > data_free is still the same. If there is no change in the value of > > data_free, then what *optimize table* does here? > > Without seeing actual values I can only give an educated > guess: > > * If you are not using innodb_file_per_table then > data_free is for the whole table space, not just > the individual table you're looking at. > > Even if that table was fragmented before running > OPTIMIZE the overall unallocated storage space > within the table space won't change much ... > > * If you have innodb_file_per_table=1 and you are > seeing data_free values round about 4MB what > you're seeing is simply pre-allocated space. > > See also the last paragraph on > > > http://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html > > File-per-table tablespace files are auto-extending regardless > of the value of innodb_autoextend_increment. The initial extensions > are by small amounts, after which extensions occur in increments > of 4MB. > > So unless you have innodb_file_per_table set and > see data_free values substantially larger than 4MB > there's nothing to worry about ... > > -- > Hartmut Holzgraefe, Principal Support Engineer (EMEA) > SkySQL - The MariaDB Company | http://www.skysql.com/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > >
