Hibernate provides SQL logging out of the box, but such logging only shows prepared statements, and not the actual SQL queries sent to the database. It also does not log the execution time of each query, which is useful for performance troubleshooting. This blog post will go over how to setup Hibernate query logging, and then compare it to the logging that can be obtained with log4jdbc . The Hibernate query logging functionality Hibernate does not log the real SQL queries sent to the database. This is because Hibernate interacts with the database via the JDBC driver, to which it sends prepared statements but not the actual queries. So Hibernate can only log the prepared statements and the values of their binding parameters, but not the actual SQL queries themselves. This is how a query looks like when logged by Hibernate: select /* load your.package.Employee */ this_.code, ... from employee this_ where this_.employee_id=? TRACE 12-04-2014@16:06:02 BasicBinder - binding parameter [1] as [NUMBER] - 1000 See this post Hibernate Debugging - Finding the origin of a Query for how to setup this type of logging. Using log4jdbc For a developer it's useful to be able to copy paste a query from the log and be able to execute the query directly in an SQL client, but the variable placeholders ? make that unfeasible. Log4jdbc in an open source tool that allows to do just that, and more. Log4jdbc is a spy driver that will wrap itself around the real JDBC driver, logging queries as they go through it. The version linked from this post provides Spring integration, unlike several other log4jdbc forks. Setting up log4jdbc First include the log4jdbc-remix library in your pom.xml. This library is a fork of the original log4jdbc: org.lazyluke log4jdbc-remix0.2.7 Next, find in the Spring configuration the definition of the data source. As an example, when using the JNDI lookup element this is how the data source looks like: After finding the data source definition, rename it to the following name: Then define a new log4jdbc data source that wraps the real data source, and give it the original name: With this configuration, the query logging should already be working. It's possible to customize the logging level of the several log4jdbc loggers available. The original log4jdbc documentation provides more information on the available loggers: jdbc.sqlonly: Logs only SQL jdbc.sqltiming: Logs the SQL, post-execution, including timing execution statistics jdbc.audit: Logs ALL JDBC calls except for ResultSets jdbc.resultset: all calls to ResultSet objects are logged jdbc.connection: Logs connection open and close events The jdbc.audit logger is especially useful to validate the scope of transactions, as it logs the begin/commit/rollback events of a database transaction. This is the proposed log4j configuration that will print only the SQL queries together with their execution time: Conclusion Using log4jdbc does imply some initial setup, but once it's in place it's really convenient to have. Having a true query log is also useful for performance troubleshooting (to be described in a future post).

Introduction When I am looking for a forum post related to SQL Server, one of the junior professional is asking how to use a DO…WHILE loop is MS SQL Server. Several people wrote their opinion related to it. Everyone is saying to use WHILE loop and some of them suggesting with T-SQL structure of CURSOR with WHILE LOOP. Obviously, when a junior professional is learning MS SQL server, the question in mind arises: is there DO… WHILE, REPEAT … UNTIL loop present in MS SQL Server as there is in C or C++ etc? No one is answering directly on the forum whether we can use DO… WHILE or REPEAT … UNTIL in MS SQL Server or NOT. If yes, how can we implement them? DO… WHILE in MS SQL Sever First we look at the algorithm of DO… WHILE. SET X = 1 DO PRINT X SET X = X + 1 WHILE X <= 10 Now we try to implement it in MS SQL Server. DECLARE @X INT=1; WAY: --> Here the DO statement PRINT @X; SET @X += 1; IF @X<=10 GOTO WAY; --> Here the WHILE @X<=1 REPEAT… UNTIL First we look at the algorithm of REPEAT... UNTIL SET X = 1 REPEAT PRINT X SET X = X + 1 UNTIL X > 10 Now we try to implement it in MS SQL Server DECLARE @X INT = 1; WAY: -- Here the REPEAT statement PRINT @X; SET @X += 1; IFNOT(@X >1 0) GOTO WAY; -- Here the UNTIL @X>10 So we see that it is possible, but a little complicated… So most developers prefer the WHILE loop in MS SQL Server.

Cloudfoundry's java buildpack is supporting some popular jvm based applications. This article is oriented to the audiences already with experience of cloudfoundry/heroku buildpack who want to have more understanding of how buildpack and cloudfoundry works internally. cf push app -p app.war -b build-pack-url The above command demonstrates the usage of pushing a war file to cloudfoundry by using a custom buildpack (E.g. https://github.com/cloudfoundry/java-buildpack). However, what exactly happens inside, or how cloudfoundry bootstrap the war file with tomcat? There are three contracts phase that bridge communication between buildpack and cloudfoundry. The three phases are detect, compile and release, which are three ruby shell scripts: Java buildpack has multiple sub components, while each of them has all of these three phases (E.g. tomcat is one of the sub components, while it contained another layer of sub components). Detect Phase: detect phase is to check whether a particular buildpack/component applies to the deployed application. Take the war file example, tomcat applies only when https://github.com/cloudfoundry/java-buildpack/blob/master/lib/java_buildpack/container/tomcat.rb is true: def supports? web_inf? && !JavaBuildpack::Util::JavaMainUtils.main_class(@application) end The above code means, the tomcat applies when the application has a WEB-INF folder andthisisnot a main class bootstrapped application. Compile Phase: Compile phase would be the major/comprehensive work for a customized buildpack, while it is trying to build a file system on a lxc container. Take the example of our war application and tomcat example. In https://github.com/cloudfoundry/java-buildpack/blob/master/lib/java_buildpack/container/tomcat/tomcat_instance.rb def compile download(@version, @uri) { |file| expand file } link_to(@application.root.children, root) @droplet.additional_libraries << tomcat_datasource_jar if tomcat_datasource_jar.exist? @droplet.additional_libraries.link_to web_inf_lib end def expand(file) with_timing "Expanding Tomcat to #{@droplet.sandbox.relative_path_from(@droplet.root)}" do FileUtils.mkdir_p @droplet.sandbox shell "tar xzf #{file.path} -C #{@droplet.sandbox} --strip 1 --exclude webapps 2>&1" @droplet.copy_resources end The above code is all about preparing the tomcat and link the application files, so the application files will be available for the tomcat classpath. Before going to the code, we have to understand the working directory when the above code executes: . => working directory .app => @application, contains the extracted war archive .buildpack/tomcat => @droplet.sandbox .buildpack/jdk .buildpack/other needed components Inside compile method: download method will download tomcat binary file (specified here: https://github.com/cloudfoundry/java-buildpack/blob/master/config/tomcat.yml), and then extract the archive file to @droplet.sandbox directory. Then copy the resources folder's files to https://github.com/cloudfoundry/java-buildpack/tree/master/resources/tomcat/conf to @droplet.sandbox/conf Symlink the @droplet.sandbox/webapps/ROOT to .app/ Symlink additional libraries (comes from other component rather than application) to the WEB-INF/lib Note: All the symlinks use relative path, since when the container deployed to DEA, the absolute paths would be different. RELEASE PHASE: Release phase is to setup instructions of how to start tomcat. Look at the code in :https://github.com/cloudfoundry/java-buildpack/blob/master/lib/java_buildpack/container/tomcat.rb def command @droplet.java_opts.add_system_property 'http.port', '$PORT' [ @droplet.java_home.as_env_var, @droplet.java_opts.as_env_var, "$PWD/#{(@droplet.sandbox + 'bin/catalina.sh').relative_path_from(@droplet.root)}", 'run' ].flatten.compact.join(' ') end The above code does: Add java system properties http.port (referenced in tomcat server.xml) with environment properties ($PORT), this is the port on the DEA bridging to the lxc container already setup when the container was provisioned. instruction of how to run the tomcat Eg. "./bin/catalina.sh run"

Cyclop is a web-based tool for querying Cassandra databases with features like syntax highlighting and query completion.

It's not always immediate why and in which part of the program is Hibernate generating a given SQL query, especially if we are dealing with code that we did not write ourselves. This post will go over how to configure Hibernate query logging, and use that together with other tricks to find out why and where in the program a given query is being executed. What does the Hibernate query log look like Hibernate has built-in query logging that looks like this: select /* load your.package.Employee */ this_.code, ... from employee this_ where this_.employee_id=? TRACE 12-04-2014@16:06:02 BasicBinder - binding parameter [1] as [NUMBER] - 1000 Why can't Hibernate log the actual query ? Notice that what is logged by Hibernate is the prepared statement sent by Hibernate to the JDBC driver plus it's parameters. The prepared statement has ? in the place of the query parameters, and the parameter values themselves are logged just bellow the prepared statement. This is not the same as the actual query sent to the database, as there is no way for Hibernate to log the actual query. The reason for this is that Hibernate only knows about the prepared statements and the parameters that it sends to the JDBC driver, and it's the driver that will build the actual queries and then send them to the database. In order to produce a log with the real queries, a tool like log4jdbc is needed, which will be the subject of another post. How to find out the origin of the query The logged query above contains a comment that allows to identify in most cases the origin of the query: if the query is due to a load by ID the comment is /* load your.entity.Name */, if it's a named query then the comment will contain the name of the query. If it's a one to many lazy initialization the comment will contain the name of the class and the property that triggered it, etc. In many cases the query comment created by is enough to identify the origin of the query. Setting up the Hibernate query log In order to obtain a query log, the following flags need to be set in the configuration of the session factory: ... true true true The example above is for Spring configuration of an entity manager factory. This is the meaning of the flags: show_sql enables query logging format_sql pretty prints the SQL use_sql_comments adds an explanatory comment In order to log the query parameters, the following log4j or equivalent configuration is needed: If everything else fails If the query comment added by the option use_sql_comments is not sufficient, then we can start by identifying the entity returned by the query based on the table names involved, and put a breakpoint in the constructor of the returned entity. If the entity does not have a constructor, then we can create one and put the breakpoint in the call to super(): @Entity public class Employee { public Employee() { super(); // put the breakpoint here } ... } When the breakpoint is hit, go to the IDE debug view containing the stack call of the program and go through it from top to bottom. The place where the query was made in the program will be there in the call stack.

Take another look at @OneToOne with a shared primary key.

this post was originally written by muhammad irfan here on the percona mysql support team, we often see issues where a customer is complaining about replication delays – and many times the problem ends up being tied to mysql replication slave lag. this of course is nothing new for mysql users and we’ve had a few posts here on the mysql performance blog on this topic over the years (two particularly popular post in the past were: “ reasons for mysql replication lag ” and “ managing slave lag with mysql replication ,” both by percona ceo peter zaitsev) . in today’s post, however, i will share some new ways of identifying delays in replication – including possible causes of lagging slaves – and how to cure this problem. how to identify replication delay mysql replication works with two threads, io_thread & sql_thread. io_thread connects to a master, reads binary log events from the master as they come in and just copies them over to a local log file called relaylog . on the other hand, sql_thread reads events from a relay log stored locally on the replication slave (the file that was written by io thread) and then applies them as fast as possible. whenever replication delays, it’s important to discover first whether it’s delaying on slave io_thread or slave sql_thread. normally, i/o thread would not cause a huge replication delay as it is just reading the binary logs from the master. however, it depends on the network connectivity, network latency… how fast is that between the servers. the slave i/o thread could be slow because of high bandwidth usage. usually, when the slave io_thread is able to read binary logs quickly enough it copies and piles up the relay logs on the slave – which is one indication that the slave io_thread is not the culprit of slave lag. on the other hand, when the slave sql_thread is the source of replication delays it is probably because of queries coming from the replication stream are taking too long to execute on the slave. this is sometimes because of different hardware between master/slave, different schema indexes, workload. moreover, the slave oltp workload sometimes causes replication delays because of locking. for instance, if a long-running read against a myisam table blocks the sql thread, or any transaction against an innodb table creates an ix lock and blocks ddl in the sql thread. also, take into account that slave is single threaded prior to mysql 5.6, which would be another reason for delays on the slave sql_thread. let me show you via master status/slave status example to identify either slave is lagging on slave io_thread or slave sql_thread. mysql-master> show master status; +------------------+--------------+------------------+------------------------------------------------------------------+ | file | position | binlog_do_db | binlog_ignore_db | executed_gtid_set | +------------------+--------------+------------------+------------------------------------------------------------------+ | mysql-bin.018196 | 15818564 | | bb11b389-d2a7-11e3-b82b-5cf3fcfc8f58:1-2331947 | +------------------+--------------+------------------+------------------------------------------------------------------+ mysql-slave> show slave status\g *************************** 1. row *************************** slave_io_state: queueing master event to the relay log master_host: master.example.com master_user: repl master_port: 3306 connect_retry: 60 master_log_file: mysql-bin.018192 read_master_log_pos: 10050480 relay_log_file: mysql-relay-bin.001796 relay_log_pos: 157090 relay_master_log_file: mysql-bin.018192 slave_io_running: yes slave_sql_running: yes replicate_do_db: replicate_ignore_db: replicate_do_table: replicate_ignore_table: replicate_wild_do_table: replicate_wild_ignore_table: last_errno: 0 last_error: skip_counter: 0 exec_master_log_pos: 5395871 relay_log_space: 10056139 until_condition: none until_log_file: until_log_pos: 0 master_ssl_allowed: no master_ssl_ca_file: master_ssl_ca_path: master_ssl_cert: master_ssl_cipher: master_ssl_key: seconds_behind_master: 230775 master_ssl_verify_server_cert: no last_io_errno: 0 last_io_error: last_sql_errno: 0 last_sql_error: replicate_ignore_server_ids: master_server_id: 2 master_uuid: bb11b389-d2a7-11e3-b82b-5cf3fcfc8f58:2-973166 master_info_file: /var/lib/mysql/i1/data/master.info sql_delay: 0 sql_remaining_delay: null slave_sql_running_state: reading event from the relay log master_retry_count: 86400 master_bind: last_io_error_timestamp: last_sql_error_timestamp: master_ssl_crl: master_ssl_crlpath: retrieved_gtid_set: bb11b389-d2a7-11e3-b82b-5cf3fcfc8f58:2-973166 executed_gtid_set: bb11b389-d2a7-11e3-b82b-5cf3fcfc8f58:2-973166, ea75c885-c2c5-11e3-b8ee-5cf3fcfc9640:1-1370 auto_position: 1 this clearly suggests that the slave io_thread is lagging and obviously because of that the slave sql_thread is lagging, too, and it yields replication delays. as you can see the master log file is mysql-bin.018196 (file parameter from master status) and slave io_thread is on mysql-bin.018192 ( master_log_file from slave status ) which indicates slave io_thread is reading from that file, while on master it’s writing on mysql-bin.018196 , so the slave io_thread is behind by 4 binlogs. meanwhile, the slave sql_thread is reading from same file i.e. mysql-bin.01819 2 (relay_master_log_file from slave status) this indicates that the slave sql_thread is applying events fast enough, but it’s lagging too, which can be observed from the difference between read_master_log_pos & exec_master_log_pos from show slave status output. you can calculate slave sql_thread lag from read_master_log_pos – exec_master_log_pos in general as long as master_log_file parameter output from show slave status and relay_master_log_file parameter from show slave status output are the same. this will give you rough idea how fast slave sql_thread is applying events. as i mentioned above, the slave io_thread is lagging as in this example then off course slave sql_thread is behind too. you can read detailed description of show slave status output fields here. also, the seconds_behind_master parameter shows a huge delay in seconds. however, this can be misleading, because it only measures the difference between the timestamps of the relay log most recently executed, versus the relay log entry most recently downloaded by the io_thread. if there are more binlogs on the master, the slave doesn’t figure them into the calculation of seconds_behind_master. you can get a more accurate measure of slave lag using pt-heartbeat from percona toolkit. so, we learned how to check replication delays – either it’s slave io_thread or slave sql_thread. now, let me provide some tips and suggestions for what exactly causing this delay. tips and suggestions what causing replication delay & possible fixes usually, the slave io_thread is behind because of slow network between master/slave. most of the time, enabling slave_compressed_protocol helps to mitigate slave io_thread lag. one other suggestion is to disable binary logging on slave as it’s io intensive too unless you required it for point in time recovery. to minimize slave sql_thread lag, focus on query optimization. my recommendation is to enable the configuration option log_slow_slave_statements so that the queries executed by slave that take more than long_query_time will be logged to the slow log. to gather more information about query performance, i would also recommend setting the configuration option log_slow_verbosity to “full”. this way we can see if there are queries executed by slave sql_thread that are taking long time to complete. you can follow my previous post about how to enable slow query log for specific time period with mentioned options here . and as a reminder, log_slow_slave_statements as variable were first introduced in percona server 5.1 which is now part of vanilla mysql from version 5.6.11 in upstream version of mysql server log_slow_slave_statements were introduced as command line option. details can be found here while log_slow_verbosity is percona server specific feature. one another reason for delay on slave sql_thread if you use row based binlog format is that if your any database table missing primary key or unique key then it will scan all rows of the table for dml on slave and causes replication delays so make sure all your tables should have primary key or unique key. check this bug report for details http://bugs.mysql.com/bug.php?id=53375 you can use below query on slave to identify which of database tables missing primary or unique key. mysql> select t.table_schema,t.table_name,engine from information_schema.tables t inner join information_schema .columns c on t.table_schema=c.table_schema and t.table_name=c.table_name group by t.table_schema,t.table_name having sum(if(column_key in ('pri','uni'), 1,0)) =0; one improvement is made for this case in mysql 5.6, where in memory hash is used slave_rows_search_algorithms comes to the rescue. note that seconds_behind_master is not updated while we read huge rbr event, so, “lagging” may be related to just that – we had not completed reading of the event. for example, in row based replication huge transactions may cause delay on slave side e.g. if you have 10 million rows table and you do “delete from table where id < 5000000″ 5m rows will be sent to slave, each row separately which will be painfully slow. so, if you have to delete oldest rows time to time from huge table using partitioning might be good alternative for this for some kind of workloads where instead using delete use drop old partition may be good and only statement is replicated because it will be ddl operation. to explain it better, let suppose you have partition1 holding rows of id’s from 1 to 1000000 , partition2 – id’s from 1000001 to 2000000 and so on so instead of deleting via statement “delete from table where id<=1000000;” you can do “alter table drop partition1;” instead. for alter partitioning operations check manual – check this wonderful post too from my colleague roman explaining possible grounds for replication delays here pt-stalk is one of finest tool from percona toolkit which collects diagnostics data when problems occur. you can setup pt-stalk as follows so whenever there is a slave lag it can log diagnostic information which we can be later analyze to check to see what exactly causing the lag. here is how you can setup pt-stalk so that it captures diagnostic data when there is slave lag: ------- pt-plug.sh contents #!/bin/bash trg_plugin() { mysqladmin $ext_argv ping &> /dev/null mysqld_alive=$? if [[ $mysqld_alive == 0 ]] then seconds_behind_master=$(mysql $ext_argv -e "show slave status" --vertical | grep seconds_behind_master | awk '{print $2}') echo $seconds_behind_master else echo 1 fi } # uncomment below to test that trg_plugin function works as expected #trg_plugin ------- -- that's the pt-plug.sh file you would need to create and then use it as below with pt-stalk: $ /usr/bin/pt-stalk --function=/root/pt-plug.sh --variable=seconds_behind_master --threshold=300 --cycles=60 [email protected] --log=/root/pt-stalk.log --pid=/root/pt-stalk.pid --daemonize you can adjust the threshold, currently its 300 seconds, combining that with –cycles, it means that if seconds_behind_master value is >= 300 for 60 seconds or more then pt-stalk will start capturing data. adding –notify-by-email option will notify via email when pt-stalk captures data. you can adjust the pt-stalk thresholds accordingly so that’s how it triggers to collect diagnostic data during problem. conclusion a lagging slave is a tricky problem but a common issue in mysql replication. i’ve tried to cover most aspects of replication delays in this post. please share in the comments section if you know of any other reasons for replication delay.

This post will go through how to setup the Hibernate Second-Level and Query caches, how they work and what are their most common pitfalls.

The Open Session in View (OSIV) pattern gives rise to different opinions in the Java development community. Let's go over OSIV and some of the pros and cons of this pattern. The problem The problem that OSIV solves is a mismatch between the Hibernate concept of session and it's lifecycle and the way that many server-side view technologies work. In a typical Java frontend application the service layer starts by querying some of the data needed to build the view. The remaining data needed can be lazy-loaded later, with the condition that the Hibernate session remains open - and there lies the problem. Between the moment that the service layer method finishes it's execution and the moment that the view is rendered, Hibernate has already committed the transaction and closed the session. When the view tries to lazy load the extra data that it needs, if finds the Hibernate session closed, causing a LazyInitializationException. The OSIV solution OSIV tackles this problem by ensuring that the Hibernate session is kept open all the way up to the rendering of the view - hence the name of the pattern. Because the session is kept open, no more LazyInitializationExceptions occur. The session or entity manager is kept open by means of a filter that is added to the request processing chain. In the case of JPA the OpenEntityManagerInViewFilter will create an entity manager at the beginning of the request, and then bind it to the request thread. The service layer will then be executed and the business transaction committed or rolled back, but the transaction manager will not remove the entity manager from the thread after the commit. When the view rendering starts, the transaction manager will then check if there is already an entity manager binded to the thread, and if so use it instead of creating a new one. After the request is processed, the filter will then unbind the entity manager from the thread. The end result is that the same entity manager used to commit the business transaction was kept around in the request thread, allowing the view rendering code to lazy load the needed data. Going back to the original problem Let's step back a moment and go back to the initial problem: the LazyInitializationException. Is this exception really a problem? This exception can also be seen as a warning sign of a wrongly written query in the service layer. When building a view and it's backing services, the developer knows upfront what data is needed, and can make sure that the needed data is loaded before the rendering starts. Several relation types such as one-to-many use lazy-loading by default, but that default setting can be overridden if needed at query time using the following syntax: select p FROM Person p left join fetch p.invoices This means that the lazy loading can be turned off on a case by case basis depending on the data needed by the view. OSIV in projects I've worked In projects I have worked that used OSIV, we could see via query logging that the database was getting hit with a high number of SQL queries, sometimes to the point that developers had to turn off the Hibernate SQL logging. The performance of these application was impacted, but it was kept manageable using second-level caches, and due to the fact that these where intranet-based applications with a limited number of users. Pros of OSIV The main advantage of OSIV is that it makes working with ORM and the database more transparent: Less queries need to be manually written Less awareness is required about the Hibernate session and how to solve LazyInitializationExceptions. Cons of OSIV OSIV seems to be easy to misuse and can accidentally introduce N+1 performance problems in the application. On projects I've worked OSIV did not work out well in the long-term. The alternative of writing custom queries that eager fetch data depending on the use case is manageable and turned out well in other projects I've worked. Alternatives to OSIV Besides the application-level solution of writing custom queries to pre-fetch the needed data, there are other framework-level aproaches to OSIV. The Seam Framework was built by some of the same developers as Hibernate , and solves the problem by introducing the notion of conversation. Can you let me know in the comments bellow your thoughts and experiences with OSIV, thanks for reading.

wanted to go through some of the basic tenets, the technical terminology related to java ee. for many people, java ee/j2ee still mean servlets, jsps or maybe struts at best. no offence or pun intended! this is not a java ee 'bible' by any means. i am not capable enough of writing such a thing! so let us line up the 'keywords' related to java ee and then look at them one by one java ee java ee apis (specifications) containers services multitiered applications components let's try to elaborate on the above mentioned points. ok. so what is java ee? 'ee' stands for enterprise edition. that essentially makes java ee - java enterprise edition. if i had to summarize java ee in a couple of sentences, it would go something like this "java ee is a platform which defines 'standard specifications/apis' which are then implemented by vendors and used for development of enterprise (distributed, 'multi-tired', robust) 'applications'. these applications are composed of modules or 'components' which use java ee 'containers' as their run-time infrastructure." what is this 'standardized platform' based upon? what does it constitute? the platform revolves around 'standard' specifications or apis . think of these as contracts defined by a standard body e.g. enterprise java beans (ejb), java persistence api (jpa), java message service (jms) etc. these contracts/specifications/apis are implemented by different vendors e.g. glassfish, oracle weblogic, apache tomee etc alright. what about containers? containers can be visualized as 'virtual/logical partitions' . each container supports a subset of the apis/specifications defined by the java ee platform they provide run-time 'services' to the 'applications' which they host the java ee specification lists 4 types of containers ejb container web container application client container applet container java ee containers i am not going to dwell into details of these containers in this post. services?? well, 'services' are nothing but a result of the vendor implementations of the standard 'specifications' (mentioned above). examples of specifications are - jersey for jax-rs (restful services), tyrus (web sockets), eclipselink (jpa), weld (cdi) etc. the 'container' is the interface between the deployed application ('service' consumer) and the application server. here is a list of 'services' which are rendered by the 'container' to the underlying 'components' (this is not an exhaustive list) persistence - offered by the java persistence api (jpa) which drives object relational mapping (orm) and an abstraction for the database operations. messaging - the java message service (jms) provides asynchronous messaging between disparate parts of your applications. contexts & dependency injection - cdi provides loosely coupled and type safe injection of resources. web services - jaxrs and jaxws provide support for rest and soap style services respectively transaction - provided by the java transaction api (jta) implementation what is a typical java ee 'application'? what does it comprise of? applications are composed of different ' components ' which in turn are supported by their corresponding ' container ' supported 'component' types are: enterprise applications - make use of the specifications like ejb, jms, jpa etc and are executed within an ejb container web applications - they leverage the servlet api, jsp, jsf etc and are supported by a web container application client - executed in client side. they need an application client container which has a set of supported libraries and executes in a java se environment. applets - these are gui applications which execute in a web browser. how are java ee applications structured? as far as java ee 'application' architecture is concerned, they generally tend follow the n-tier model consisting of client tier, server tier and of course the database (back end) tier client tier - consists of web browsers or gui (swing, java fx) based clients. web browsers tend to talk to the 'web components' on the server tier while the gui clients interact directly with the 'business' layer within the server tier server tier - this tier comprises of the dynamic web components (jsp, jsf, servlets) and the business layer driven by ejbs, jms, jpa, jta specifications. database tier - contains 'enterprise information systems' backed by databases or even legacy data repositories. generic 3-tier java ee application architecture java ee - bare bones, basics.... as quickly and briefly as i possibly could. that's all for now! :-) stay tuned for more java ee content, specifically around the latest and greatest version of the java ee platform --> java ee 7 happy reading!

Michael recently wrote a blog post showing how to create a time tree representing time down to the second using Neo4j’s Cypher query language, something I built on top of for a side project I’m working on. The domain I want to model is RSVPs to meetup invites – I want to understand how much in advance people respond and how likely they are to drop out at a later stage. For this problem I only need to measure time down to the day so my task is a bit easier than Michael’s. After a bit of fiddling around with leap years, I believe the following query will create a time tree representing all the days from 2011 – 2014, which covers the time the London Neo4j meetup has been running: WITH range(2011, 2014) AS years, range(1,12) as months FOREACH(year IN years | MERGE (y:Year {year: year}) FOREACH(month IN months | CREATE (m:Month {month: month}) MERGE (y)-[:HAS_MONTH]->(m) FOREACH(day IN (CASE WHEN month IN [1,3,5,7,8,10,12] THEN range(1,31) WHEN month = 2 THEN CASE WHEN year % 4 <> 0 THEN range(1,28) WHEN year % 100 <> 0 THEN range(1,29) WHEN year % 400 <> 0 THEN range(1,29) ELSE range(1,28) END ELSE range(1,30) END) | CREATE (d:Day {day: day}) MERGE (m)-[:HAS_DAY]->(d)))) The next step is to link adjacent days together so that we can easily traverse between adjacent days without needing to go back up and down the tree. For example we should have something like this: (jan31)-[:NEXT]->(feb1)-[:NEXT]->(feb2) We can build this by first collecting all the ‘day’ nodes in date order like so: MATCH (year:Year)-[:HAS_MONTH]->(month)-[:HAS_DAY]->(day) WITH year,month,day ORDER BY year.year, month.month, day.day WITH collect(day) as days RETURN days And then iterating over adjacent nodes to create the ‘NEXT’ relationship: MATCH (year:Year)-[:HAS_MONTH]->(month)-[:HAS_DAY]->(day) WITH year,month,day ORDER BY year.year, month.month, day.day WITH collect(day) as days FOREACH(i in RANGE(0, length(days)-2) | FOREACH(day1 in [days[i]] | FOREACH(day2 in [days[i+1]] | CREATE UNIQUE (day1)-[:NEXT]->(day2)))) Now if we want to find the previous 5 days from the 1st February 2014 we could write the following query: MATCH (y:Year {year: 2014})-[:HAS_MONTH]->(m:Month {month: 2})-[:HAS_DAY]->(:Day {day: 1})<-[:NEXT*0..5]-(day) RETURN y,m,day If we want to we can create the time tree and then connect the day nodes all in one query by using ‘WITH *’ like so: WITH range(2011, 2014) AS years, range(1,12) as months FOREACH(year IN years | MERGE (y:Year {year: year}) FOREACH(month IN months | CREATE (m:Month {month: month}) MERGE (y)-[:HAS_MONTH]->(m) FOREACH(day IN (CASE WHEN month IN [1,3,5,7,8,10,12] THEN range(1,31) WHEN month = 2 THEN CASE WHEN year % 4 <> 0 THEN range(1,28) WHEN year % 100 <> 0 THEN range(1,29) WHEN year % 400 <> 0 THEN range(1,29) ELSE range(1,28) END ELSE range(1,30) END) | CREATE (d:Day {day: day}) MERGE (m)-[:HAS_DAY]->(d)))) WITH * MATCH (year:Year)-[:HAS_MONTH]->(month)-[:HAS_DAY]->(day) WITH year,month,day ORDER BY year.year, month.month, day.day WITH collect(day) as days FOREACH(i in RANGE(0, length(days)-2) | FOREACH(day1 in [days[i]] | FOREACH(day2 in [days[i+1]] | CREATE UNIQUE (day1)-[:NEXT]->(day2)))) Now I need to connect the RSVP events to the tree!

I was playing around with Neo4j’s Cypher last weekend and found myself accidentally running some queries against an earlier version of the Neo4j 2.0 series (2.0.0). My first query started with a map and I wanted to create a person from an identifier inside the map: WITH {person: {id: 1} AS params MERGE (p:Person {id: params.person.id}) RETURN p When I ran the query I got this error: ==> SyntaxException: Type mismatch: expected Map but was Boolean, Number, String or Collection (line 1, column 62) ==> "WITH {person: {id: 1} AS params MERGE (p:Person {id: params.person.id}) RETURN p" If we try the same query in 2.0.1 it works as we’d expect: ==> +---------------+ ==> | p | ==> +---------------+ ==> | Node[1]{id:} | ==> +---------------+ ==> 1 row ==> Nodes created: 1 ==> Properties set: 1 ==> Labels added: 1 ==> 47 ms My next query was the following which links topics of interest to a person: WITH {topics: [{name: "Java"}, {name: "Neo4j"}]} AS params MERGE (p:Person {id: 2}) FOREACH(t IN params.topics | MERGE (topic:Topic {name: t.name}) MERGE (p)-[:INTERESTED_IN]->(topic) ) RETURN p In 2.0.0 that query fails like so: ==> InternalException: Query not prepared correctly! but if we try it in 2.0.1 we’ll see that it works as well: ==> +---------------+ ==> | p | ==> +---------------+ ==> | Node[4]{id:2} | ==> +---------------+ ==> 1 row ==> Nodes created: 1 ==> Relationships created: 2 ==> Properties set: 1 ==> Labels added: 1 ==> 53 ms So if you’re seeing either of those errors, then get yourself upgraded to 2.0.1 as well!

Recently needed to update the last inserted row of a table but didn't have anyway in knowing what the highest ID in the table was. I can easily do this by using the max() function to select the highest ID in the table. SELECT MAX(id) FROM table; Then I can use the result of this query in the UPDATE query to edit the record with the highest ID. But this is quite a easy query so I should be able to do this in one query by using a nested select query on the UPDATE. UPDATE table SET name='test_name' WHERE id = (SELECT max(id) FROM table) But the problem with this is that the MAX() function doesn't work inside a nested select so had to find another way of doing this. I found out that you can use an ORDER BY and a LIMIT in an UPDATE query therefore I can use a combination of these in the UPDATE query to make sure I only update the record with the highest ID, by doing a descendant order on the ID and limiting the return to only 1 record. UPDATE table SET name='test_name' ORDER BY id DESC LIMIT 1;

There are many instances where we will have to write custom queries with hibernate. I always hated writing custom queries due to following reasons Hibernate returns List of Object arrays (List getSalaryByDepartment() { Session session = HibernateUtil.getSessionFactory().openSession(); try { List salaryByDepartments = new ArrayList(); for (Object object : contactList) { Object[] result = (Object[]) object; SalaryByDepartment salaryByDepartment = new SalaryByDepartment(); salaryByDepartment.setDeptId((Integer) result[0]); salaryByDepartment.setDepartmentName((String) result[1]); salaryByDepartment.setSalary((Double) result[2]); salaryByDepartments.add(salaryByDepartment); } return salaryByDepartments; } catch (HibernateException e) { e.printStackTrace(); return null; } finally { session.close(); } } There is a better mechanism for writing the custom queries called 'select new' but it is not widely used for some reason. It might be an overkill to write all custom queries using this mechanism, but is good for queries which return more than 2 columns. public List getNewSalaryByDepartment() { Session session = HibernateUtil.getSessionFactory().openSession(); try { List salaryByDept = session.createQuery("select " + "new hsqldb.results.SalaryByDepartment(department.id, department.departmentName, sum(employee.salary)) " + "from Employee employee, Department department " + "where employee.department.id = department.id group by department.id") .list(); return salaryByDept; } catch (HibernateException e) { e.printStackTrace(); return null; } finally { session.close(); } }

After reading the interesting blog post of my colleague Rik van Bruggen on “Media, Politics and Graphs” I thought it would be really cool to render it as a GrapGist. Especially, as he already shared all the queries as a GitHub Gist. Unfortunately the dataset was a bit large for a sensible GraphGist representation, so I thought about means of extracting a smaller sample of his raw data that he made available (see his blog post for the link). Considering my last blog post on creating data from sampling a cross product, this should be much easier. We know we want to have all nodes with the labels PARTY, SHOW and GENDER in our graph as well as a sample of GUEST nodes with their relationships. The first part is easy: MATCH (n) WHERE n:PARTY OR n:SHOW OR n:GENDER RETURN n; The second part uses something that was not helpful in my last exploration, namely that random sampling when applied directly to a match, is used to filter the first node-pattern in the match and then still traverse all relationships/paths emanating from that node. MATCH(n:GUEST)-[r]->() WHERE rand() < 0.1 RETURN n,r; The number you compare rand() to is the percentage you want to get back, in this example 10%. Now I have two nice queries, that can get me the data, how can I bring them together? With UNION ALL MATCH (n) WHERE n:PARTY OR n:SHOW OR n:GENDER RETURN n, null as r UNION ALL MATCH(n:GUEST)-[r]->() WHERE rand() < 0.1 RETURN n,r; And where do I get the Cypher statements from, that I can use to populate my GraphGist database setup? Fortunately my dump command made it into the Neo4j-Shell, so that we can just run it on the command-line and redirect the output into a file: bin/neo4j-shell -path talkshow/graph.db \ -c 'dump MATCH (n) WHERE n:PARTY OR n:SHOW OR n:GENDER RETURN n, null as r UNION ALL MATCH(n:GUEST)-[r]->() WHERE rand() < 0.1 RETURN n,r;' \ > talkshow/sample.cql Don’t forget the semicolon at the end! Looking at sample.cql we see something like: begin create (_0:`SHOW` {`Modularity Name`:"B&vD", `id`:"B&vD", `label`:"B&vD", `modularity_class`:3, `weighted outdegree`:0.000000}) create (_1:`SHOW` {`Modularity Name`:"P&W", `id`:"P&W", `label`:"P&W", `modularity_class`:4, `weighted outdegree`:0.000000}) create (_2:`SHOW` {`Modularity Name`:"DWDD", `id`:"DWDD", `label`:"DWDD", `modularity_class`:5, `weighted outdegree`:0.000000}) ... ... create _509-[:`VISITED` {`quantity`:1}]->_5 create _509-[:`VISITED` {`quantity`:1}]->_2 create _509-[:`VISITED` {`quantity`:1}]->_1 create _509-[:`VISITED` {`quantity`:1}]->_0 ; commit Which we can now use to populate our database for our GraphGist, and here it is in all its beauty – GraphGist: “Media, Politics and Graphs”. But actually I chose not to use Rik’s GitHub Gist with the queries, but to copy the nice text and pictures from his blog post into the GraphGist. You might notice that some of the parties go without connections. That would need some tweaking of the sampling which I leave as exercise for you. Have fun Michael

What's New in this Release? Aspose development team is happy to announce the monthly release of Aspose.Words for Java &.NET 14.3.0. Aspose.Words now supports insertion of OLE objects such as another Microsoft Word document or a Microsoft Excel chart. A new public method, InsertOleObject, has been introduced in the DocumentBuilder class. This method can be used to insert an embedded or linked OLE object from a file into a Word document. Aspose.Words’ rendering engine now partially supports EUDC (End-User-Defined-Characters) fonts. Please find below the description of how EUDC fonts works on Windows. In this first implementation, Aspose.Words uses a single EUDC font. When rendering a document to fixed-page formats, this font is searched among the specified font sources by “EUDC” family name. Starting from Aspose.Words 14.3.0, Best Fit position of data labels in pie charts is partially supported. In previous versions labels with best fit position were rendered as if they had the inside end position. Currently we use a modified Open Office algorithm to set the best fit position of data labels. The list of new and improved features in this release are listed below Public API for insertion of OLE objects both linked and embedded Outline, Shadow, Reflection, Glow and Fill text effects for rendering text inside DrawingML shapes EUDC fonts rendering partially supported “PDF Logical Structure” export reworked, significantly improving memory usage Support OLE embedding of documents and files Feature Write an article about how to work with Table of contents in Aspose.Words Add tag support Support BestFit position of Pie chart's data labels. Support style:text-decoration attribute of Paragraph tag. Preserve private characters in EUDC.TTE during rendering to Pdf Support HTML table row borders in HTML import Support text outline effect. Support text fill effect. Support text shadow effect. Support text reflection effect Support text glow effect. Support text effects applied to text in Dml Shape or in SmartArt. Implement reading text effects from rPr in DML. Support inheriting styles from parent objects. Add image compression options for different image types Add a link to the online documentation in the DLLs only release Default run properties lose lang attribute value on DOCX to DOC conversions Padding for image in table is lost when rendering to Pdf Consider using "Don't vertically align cells containing floating objects" compatibility option when exporting to HTML Shape in table's cell is improperly horizontally aligned to center after export to HTML is now fixed Floating shape is now properly vertically positioned after export to HTML Warning : Unkno/wn ProgId value 'Visio.Drawing.11'. This might cause inaccessible OLE embedding Charts (DrawingML) issue fixed and now render correctly in Pdf file Aspose.Words now properly work in Jdeveloper IDE OLE object cannot be edited after re saving the document Diagram connectors are inverted/flipped after conversion from Docx to pdf WordArt letters are condensing issue is resolved Condensed character spacing is lost is fixed Relative hyperlink with Unicode is now properly saved to Pdf Add pre-built Document Explorer JAR to Java release. Aspose.Words does not take in account style set in . Table now looks correctly while converting html to doc. Hyperlinks split into multiple fragments/links in output PDF Horizontal table position is corrected. Document.UpdateFields Does not Update TOC in DOCX Content in the output html is overlapped at many places in Html Different table justify alignment for different compatibilityMode values. CSS selectors now work for , , and elements Chart Legend/Series now render correctly in output Pdf file Data labels with best fit position are rendering at correct places in Chart Offset of the hyperlink text line is off by a few pixels in output Pdf Hyperlinks split into multiple fragments/links in output PDF is fixed Document.UpdateFields is enhanced and update SUM formula field Paragraph's first line indent increases is fixed when exported to HTM Space before a paragraph following a floating element is too large after HTML to DOCX conversion is now fixd Block-level SVG image become inline after HTML to DOCX conversion Problem with vertical paragraph spacing is resolved when importing HTML using InsertHtml method Shape rotation is fixed after conversion from Doc to Pdf Text color is changed after conversion from Docx to WordML/Doc Word Table indentation is now corrected when is placed inside Arrows on the Lines gets distorted when converting to Pdf is now fixed A space character is exported to PDF output between Japanese and Numeric characters is fixed Line Shape from Header is merged with the top border of Table in Body Docx to WordML conversion issue resolved with content control Junk text is rendered in fixed page formats Positions of some DrawingML circles are now preserved during rendering A row and some content is rendering at the bottom of previous page is fixed Header table rows and images are now preserved in PDF List items issue fixed, now line up correctly after conversion from RTF to HTML Conversion from Docm to Doc creates corrupted document now fixed Hyperlink for an icon is now preserved during HTML to PDF conversion Text overlapping is fixed after conversion from Docx to Pdf Text position change is fixed after conversion from Docx to Pdf Other most recent bug fixes are also included in this release Newly added documentation pages and articles Some new tips and articles have now been added into Aspose.Words for .NET documentation that may guide you briefly how to use Aspose.Words for performing different tasks like the followings. How Aspose.Words Uses True Type Fonts How to Extract Images from a Document Overview: Aspose.Words Aspose.Words is a word processing component that enables .NET, Java & Android applications to read, write and modify Word documents without using Microsoft Word. Other useful features include document creation, content and formatting manipulation, mail merge abilities, reporting features, TOC updated/rebuilt, Embedded OOXML, Footnotes rendering and support of DOCX, DOC, WordprocessingML, HTML, XHTML, TXT and PDF formats (requires Aspose.Pdf). It supports both 32-bit and 64-bit operating systems. You can even use Aspose.Words for .NET to build applications with Mono. More about Aspose.Words Homepage Aspose.Words for .NET Homepage Java Word Library Download Aspose.Words for .NET Download Aspose.Words for Java Demos for Aspose.Words Contact Information Aspose Pty Ltd Suite 163, 79 Longueville Road Lane Cove, NSW, 2066 Australia Aspose - Your File Format Experts [email protected] Phone: 888.277.6734 Fax: 866.810.9465

It is always recommended to use Spring properties with Mule, to externalize any configuration parameters (URLs, ports, user names, passwords, etc.). For example, the Acme APIfrom my previous post connects to an external database. So instead of hard-coding connectivity options inside my application code, I would create a properties file, e.g. acme.properties, as follows: acme.jdbc.host=acmedb acme.jdbc.port=3306 acme.jdbc.database=acmeProducts acme.jdbc.user=WileECoyote acme.jdbc.password=GeeWhizz Obviously, as a developer, I would use a test instance of Acme database to test my application. I’d commit the code to the version control system, including the properties file. Then my application would begin its journey from the automated build system to the Dev environment, to QA, Pre-Prod, and finally Prod – and fail to deploy on production because it wouldn’t be able to connect to the test database! Or even worse, it would connect to the test database and use it and no one would notice the problem until customers placed $0 order for an Acme widget which would normally cost $1000, all because the test database didn’t contain actual prices! Sure, I could just follow the recommendations on our web site and create multiple sets of properties, e.g. acme.dev.properties, acme.qa.properties, acme.prod.properties etc. But instead of solving the problem, it would create a few new ones. First, those properties must still be packaged within the application. Needless to say, IT guys would never give me the credentials for the production database, so I’d have to provide instructions for them on how to modify the properties file AFTER the application is deployed on the prod platform. Second, if (or rather WHEN) any of those properties will need to be changed (for example, the production DB is migrated to a new server), the whole process has to be repeated. And don’t forget about passwords and other sensitive data that should never appear in the code as open text and have to be encrypted. It seems like every single customer I’ve worked with has this problem. And there was no convincing solution until one of our customers told me about an application called Zuul. As the description on the Zuul web site says, “Zuul is a free, open source web application which can be used to centralize and manage configuration for your internal applications. It enables your operations team to control changes and your developers a centralized place to organize settings.” Of course, I couldn’t resist the urge to download it and try it out with Mule. The installation and configuration of the Zuul server was pretty straightforward. After all, Zuul is a standard web application, so I just deployed it to my local Tomcat instance, alongside with MMC which was already deployed on it. I configured the database settings to point to my local MySQL instance. For the LDAP server I used OpenLDAP. I had to download and install the Unlimited Strength JCE Policy Files. Then I started Tomcat and opened the Zuul URL in my browser and logged in as administrator. The first task is to create my environments. Navigating to Administration->Environments menu, I see three environments, prod, qa, and dev, which Zuul creates by default. Just what I need! Moreover, the prod environment is red – which means, only someone with Administrator privileges can mess with it. And while we are in the Administration screen, let’s create a new encryption key for our password values. Administration->Key Management, then click on Create New... button and populate the form: And now we can create our properties. Select Settings->Create New, give it a name, e.g. AcmeProperties. On the next screen, you’re given the option to create a new properties set from scratch, or to upload an existing properties file. Since we already have acme.properties for our dev environment, let’s just use it. Select dev environment on the left tab, then click Upload File button: Upload acme.properties and you’ll see the following screen: Now we can encrypt the database password. Just make sure the correct key is selected, then click Edit and select Encrypt. To finish the server setup, we replicate this set of properties on the qa and prod environments. Select qa tab, then click Copy Existing, then in the Search text box type dev. Your properties set "/dev/AcmeProperties.properties" will be highlighted. Click Copy button and now you have the identical set of properties in qa. Repeat the process for the prod environment. Change properties values on each environment accordingly. This concludes the server setup procedure. In the next post, I will show you how to configure Mule to use Zuul properties management. UPDATE: Zuul can be downloaded at http://www.devnull.org/zuul

This post was originally written by Vlad Lesin for the MySQL Performance Blog. Percona Server 5.6.11-60.3 introduces a new “log archiving” feature. Percona XtraBackup 2.1.5 supports “apply archived logs.” What does it mean and how it can be used? Percona products propose three kinds of incremental backups. The first is full scan of data files and comparison the data with backup data to find some delta. This approach provides a history of changes and saves disk space by storing only data deltas. But the disadvantage is a full-data file scan that adds load to the disk subsystem. The second kind of incremental backup avoids extra disk load during data file scans. The idea is in reading only changed data pages. The information about what specific pages were changed is provided by the server itself which writes files with the information during work. It’s a good alternative but changed-pages tracking adds some small load. And Percona XtraBackup’s delta reading leads to non-sequential disk io. This is good alternative but there is one more option. The Innodb engine has a data log. It writes all operations which modify database pages to log files. This log is used in the case of unexpected server terminating to recover data. The Innodb log consists of the several log files which are filled sequentially in circular. The idea is to save those files somewhere and apply all modifications from archived logs to backup data files. The disadvantage of this approach is in using extra disk space. The advantage is there is no need to do an “explicit” backup on the host server. A simple script could sit and wait for logs to appear then scp/netcat them over to another machine. But why not use good-old replication? Maybe replication does not have such performance as logs recovering but it is more controlled and well-known. Archived logs allows you to do any number of things with them from just storing them to doing periodic log applying. You can not recover from a ‘DROP TABLE’, etc with replication. But with this framework one could maintain the idea of “point in time” backups. So the “archived logs” feature is one more option to organize incremental backups. It is not widely used as it was issued not so far and there is not A good understanding of how it works and how it can be used. We are open to any suggestions about its suggest improvements and use cases. The subject of this post is to describe how it works in depth. As log archiving is closely tied with innodb redo logs the internals of redo logs will be covered too. This post would be useful not only for DBA but also for Software Engineers because not only common principles are considered but the specific code too, and knowledge from this post can be used for further MySQL code exploring and patching. What is the innodb log and how it is written? Let’s remember what are innodb logs, why they are written, what they are used for. The Innodb engine has buffer pool. This is a cache of database pages. Any changes are done on page in buffer pool, then page is considered as “dirty,” which means it must be flushed, and pushed to the flush list which is processed periodically by special thread. If pages are not flushed to disk and server is terminated unexpectedly the changes will be lost. To avoid this innodb writes changes to redo log and recover data from redo log during start. This technique allows to delay buffer pool pages flushing. It can increase performance because several changes of one page can be accumulated in memory and then flushed by one io. Except that flushed pages can be grouped to decrease the number of non-sequential io’s. But the down-side of this approach is time for data recovering. Let’s consider how this log is stored, generated and used for data recovering. Log files Redo log consists of a several log files which are treated as a circular buffer. The number and the size of log files can be configured. Each log file has a header. The description of this header can be found in “storage/innobase/include/log0log.h” by “LOG_GROUP_ID” keyword. Each log file contains log records. Redo log records are written sequentially by log blocks of OS_FILE_LOG_BLOCK_SIZE size which is equal to 512 bytes by default and can be changed with innodb option. Each record has its LSN. LSN is a “Log Sequence Number” – the number of bytes written to log from the log creation to the certain log record. Each block consists of header, trailer and log records. Log blocks Let’s consider log block header. The first 4 bytes of the header is log block number. The block number is very similar as LSN but LSN is measured in bytes and block number is measured by OS_FILE_LOG_BLOCK_SIZE. Here is the simple formula how LSN is converted to block number: return(((ulint) (lsn / OS_FILE_LOG_BLOCK_SIZE) & 0x3FFFFFFFUL) + 1); This formula can be found in log_block_convert_lsn_to_no() function. The next two bytes is the number of bytes used in the block. The next two bytes is the offset of the first MTR log record in this block. What is MTR will be described below. Currently it can be considered as a synonym of bunch of log records which are gathered together as a description of some logical operation. For example it can be a group of log records for inserting new row to some table. This field is used when there are records of several MTR’s in one block. The next four bytes is a checkpoint number. The trailer is four bytes of log block checksum. The above description can be found in “storage/innobase/include/log0log.h” by “LOG_BLOCK_HDR_NO” keyword. Before writing to disk log blocks must be somehow formed and stored. And the question is: How log blocks are stored in memory and on disk? Where log blocks are stored before flushing to disk and how they are written and flushed? Global log object and log buffer The answer to the first part of the question is log buffer. Server holds very important global object log_sys in memory. It contains a lot of useful information about logging state. Log buffer is pointed by log_sys->buf pointer which is initialized in log_init(). I would highlight the following log_sys fields that are used for work with log buffer and flushing: log_sys->buf_size – the size of log buffer, can be set with innodb-log-buffer-size variable, the default value is 8M; log_sys->buf_free – the offset from which the next log record will be written; log_sys->max_buf_free – if log_sys->buf_free is greater then this value log buffer must be flushed, see log_free_check(); log_sys->buf_next_to_write – the offset of the next log record to write to disk; log_sys->write_lsn – the LSN up to which log is written; log_sys->flushed_to_disk_lsn – the LSN up to which log is written and flushed; log_sys->lsn – the last LSN in log buffer; So log_sys->buf_next_to_write is between 0 and log_sys->buf_free, log_sys->write_lsn is equal or less log_sys->lsn, log_sys->flushed_to_disk_lsn is less or equal to log_sys->write_lsn. The relationships for those fields can be easily traced with debugged by setting up watchpoints. Ok, we have log buffer, but how do log records come to this buffer? Where log records come from? Innodb has special objects that allow you to gather redo log records for some operations in one bunch before writing them to log buffer. These objects are called “mini-transactions” and corresponding functions and data types have “mtr” prefix in the code. The objects itself are described in mtr_t “c” structure. The most interesting fields of this structure are the following: mtr_t::log – contains log records for the mini-transaction, mtr_t::memo – contains pointers to pages which are changed or locked by the mini-transaction, it is used to push pages to flush list and release locks after logs records are copied to log buffer in mtr_commit() (see mtr_memo_pop_all() called in mtr_commit()). mtr_start() function initializes an object of mtr_t type and mtr_commit() writes log records from mtr_t::log to log_sys->buf + log_sys->buf_free. So the typical sequence of any operation which changes data is the following: mtr_start(); // initialize mtr object some_ops... // operations on data which are logged in mtr_t::log mtr_commit(); // write logged operations from mtr_t::log to log buffer log_sys->buf page_cur_insert_rec_write_log() is a good example of how mtr records can be written and mtr::memo can be filled. The low-level function which writes data to log buffer is log_write_low(). This function is invoked inside of mtr_commit() and not only copy the log records from mtr_t object to log buffer log_sys->buf but also creates a new log blocks inside of log_sys->buf, fills their header, trailer, calculates checksum. So log buffer contains log blocks which are sequentially filled with log records which are grouped in “mini-transactions” which logically can be treated as some logical operation over data which consists of a sequence of mini-operations(log records). As log records are written sequentially in log buffer one mini-transaction and even one log record can be written in two neighbour blocks. That is why the header field which would contain the offset of the first MTR in the block is necessary to calculate the point from which log records parsing can be started. This field was described in 2.2. So we have a buffer of log blocks in a memory. How is data from this buffer written to disk? The mysql documentation says that this depends on innodb_flush_log_at_trx_commit option. There can be three cases depending on the value of this option. Let’s consider each of them. Writing log buffer to disk: innodb_flush_log_at_trx_commit is 1 or 2. The first two cases is when innodb_flush_log_at_trx_commit is 1 or 2. In these cases flush log records are written for 2 and flushed for 1 on each transaction commit. If innodb_flush_log_at_trx_commit is 2 log records are flushed periodically by special thread which will be considered later. The low-level function which writes log records from buffer to file is log_group_write_buf(). But in the most cases it is not called directly but it is called from more high level log_write_up_to(). For the current case the calling stack is the following: (trx_commit_in_memory() or trx_commit_complete_for_mysql() or trx_prepare() e.t.c)-> trx_flush_log_if_needed()-> trx_flush_log_if_needed_low()-> log_write_up_to()-> log_group_write_buf(). It is quite easy to find the higher levels of calling stack, just set up breakpoint on log_group_write_buf() and execute any sql query that modifies innodb data. For example for the simple “insert” sql query the higher levels of calling stack are the following: mysql_execute_command()-> trans_commit_stmt()-> ha_commit_trans()-> TC_LOG_DUMMY::commit()-> ha_commit_low()-> innobase_commit()-> trx_commit_complete_for_mysql()-> trx_flush_log_if_needed()-> ... . log_io_complete() callback is invoked when i/o is finished for log files (see fil_aio_wait()). log_io_complete() flushes log files if this is not forbidden by innodb_flush_method or innodb_flush_log_at_trx_commit options. Writing log buffer to disk: innodb_flush_log_at_trx_commit is equal to 0 The third case is when innodb_flush_log_at_trx_commit is equal to 0. For this case log buffer is NOT written to disk on transaction commit, it is written and flushed periodically by separate thread “srv_master_thread”. If innodb_flush_log_at_trx_commit = 0 log files are flushed in the same thread by the same calls. The calling stack is the following: srv_master_thread()-> (srv_master_do_active_tasks() or srv_master_do_idle_tasks() or srv_master_do_shutdown_tasks())-> srv_sync_log_buffer_in_background()-> log_buffer_sync_in_background()->log_write_up_to()->... . Special cases for logs flushing While log_io_complete() do flushing depending on innodb_flush_log_at_trx_commit value among others log_write_up_to() has it’s own flushing criteria. This is flush_to_disk function argument. So it is possible to force log files flushing even if innodb_flush_log_at_trx_commit = 0. Here are examples of such cases: 1) buf_flush_write_block_low() Each page contains information about the last applied LSN(buf_flush_write_block_low::newest_modification), each log record is a description of change on certain page. Imagine we flushed some changed pages but log records for these pages were not flushed and server goes down. After starting the server some pages will have the newest modifications, but some of them were not flushed and the correspondent log records are lost too. We will have inconsistent database in this case. That is why log records must be flushed before the pages they refer. 2) srv_sync_log_buffer_in_background() As it was described above this function is called periodically by special thread and forces flushing. 3) log_checkpoint() When checkpoint is made log files must be reliably flushed. 4) The special handlerton innobase_flush_logs() which can be called through ha_flush_logs() from mysql server. For example ha_flush_logs() is called from MYSQL_BIN_LOG::reset_logs() when “RESET MASTER” or “RESET SLAVE” are executed. 5) srv_master_do_shutdown_tasks() – on shutdown, ha_innobase::create() – on table creating, ha_innobase::delete_table() – on table removing, innobase_drop_database() – on all database tables removing, innobase_rename_table() – on table rename e.t.c If log files are treated as circular buffer what happens when the buffer is overflown? Briefly. Innodb has a mechanism which allows you to avoid overflowing. It is called “checkpoints.” The checkpoint is a state when log files are synchronized with data files. In this case there is no need to keep the history of changes before checkpoint because all pages with the last modifications LSN less or equal to checkpoint LSN are flushed and the log files space from the last written LSN to the last checkpoint LSN can be reused. We will not describe a checkpoint process here because it is a separate interesting subject. The only thing we need to know is when checkpoint happens all pages with modification LSN less or equal to checkpoint LSN are reliably flushed. How archived logs are written by server. So the log contains information about page changes. But as we said, log files are the circular buffer. This means that they occupy fixed disk size and the oldest records can be rewritten by the newest ones as there are points when data files are synchronized with log files called checkpoints and there is no need to store the previous history of log records to guarantee database consistency. The idea is to save somewhere all log records to have the possibility of applying them to backuped data to have some kind if incremental backup. For example if we want to have an archive of log records. As log consists of log files it is reasonable to store log records in such files too, and these files are called “archived logs.” Archived log files are written to the directory which can be set with special innodb option. Each file has the same size as innodb log size and the suffix of each archived file is the LSN from which it is started. As well as log writing system log archiving system stores its data in global log_sys object. Here are the most valuable fields in log_sys from my point of view: log_sys->archive_buf, log_sys->archive_buf_size – logs archive buffer and its size, log records are copied from log buffer log_sys->buf to this buffer before writing to disk; log_sys->archiving_phase – the current phase of log archiving: LOG_ARCHIVE_READ when log records are being copied from log_sys->buf to log_sys->archive_buf, LOG_ARCHIVE_WRITE when log_sys->archive_buf is being written to disk; log_sys->archived_lsn – the LSN to which log files are written; log_sys->next_archived_lsn – the LSN to which write operations was invoked but not yet finished; log_sys->max_archived_lsn_age – the maximum difference between log_sys->lsn and log_sys->archived_lsn, if this difference exceeds the log are being archived synchronously, i.e. the difference is decreased; log_sys->archive_lock – this is rw-lock which is used for synchronizing LOG_ARCHIVE_WRITE and LOG_ARCHIVE_READ phases, it is x-locked on LOG_ARCHIVE_WRITE phase. So how is data copied from log_sys->buf to log_sys->archived_buf? log_archive_do() is used for this. It is not only set the proper state for archived log fields in log_sys but also invokes log_group_read_log_seg() with corresponding arguments which not only copy data from log buffer to archived log buffer but also invokes asynchronous write operation for archived log buffer. log_archive_do() can wait until io operations are finished using log_sys->archive_lock if corresponding function parameter is set. The main question is on what circumstances log_archive_do() is invoked, i.e. when log records are being written to archived log files. The first call stack is the following: log_free_check()-> log_check_margins()-> log_archive_margin()-> log_archive_do(). Here is text of log_free_check() with comments: /*********************************************************************// Checks if there is need for a log buffer flush or a new checkpoint, and does this if yes. Any database operation should call this when it has modified more than about 4 pages. NOTE that this function may only be called when the OS thread owns no synchronization objects except the dictionary mutex. */ UNIV_INLINE void log_free_check(void) /*================*/ { #ifdef UNIV_SYNC_DEBUG ut_ad(sync_thread_levels_empty_except_dict()); #endif /* UNIV_SYNC_DEBUG */ if (log_sys->check_flush_or_checkpoint) { log_check_margins(); } } log_sys->check_flush_or_checkpoint is set when there is no enough free space in log buffer or it is time to do checkpoint or any other bound case. log_archive_margin() is invoked only if the limit if the difference between log_sys->lsn and log_sys->archived_lsn is exceeded. Let’s refer to this difference as archived lsn age. One more call log_archive_do() is from log_open() when archived lsn age exceeds some limit. log_open() is called on each mtr_commit(). And for this case archived logs are written synchronously. The next synchronous call is from log_archive_all() during shutdown. Summarizing all above archived logs begins to be written when the log buffer is full enough to be written or when checkpoint happens or when the server is in the process of shut down. And there is no any delay between writing to archive log buffer and writing to disk. I mean there is no way to say that archived logs must be written once a second as it is possible for redo logs with innodb_flush_log_at_trx_commit = 0. As soon as data is copied to the buffer the write operation is invoked immediately for this buffer. Archived log buffer is not filled on each mtr_commit() so it does not slow down the usual logging process. The exception is when there are a lot of io operations what can be the reason of archive log age is too big. The result of big archive log age is the synchronous archived logs writing during mtr_commit(). Memory to memory copying is quite fast operation that is why the data is copied to archived log buffer and is written to disk asynchronously minimizing delays which can be caused by logs archiving. PS: Here is another call stack for writing archived log buffer to archived log files: log_io_complete()->log_io_complete_archive()->log_archive_check_completion_low()->log_archive_groups(). I propose to explore this stack yourself. Logs recovery process, how it is started and works inside. Archived logs applying. So we discovered how innodb redo logging works, and how redo logs are archived. And the last uncovered thing is how recovery works and how archived logs are applied. These two processes are very similar – that is why they are discussed in one section of this post. The story begins with innobase_start_or_create_for_mysql() which is invoked from innobase_init(). The following trident in innobase_start_or_create_for_mysql() can be used to search the relevant code: if (create_new_db) { ... } else if (srv_archive_recovery) { ... } else { ... } The second condition and the last one is the place from which archived logs applying and innodb logs recovery processes correspondingly start. These two blocks wrap two pairs of functions: recv_recovery_from_archive_start() recv_recovery_from_archive_finish() and recv_recovery_from_checkpoint_start() recv_recovery_from_checkpoint_finish() And all the magic happens in these pairs. As well as global log_sys object for redo logging there is global recv_sys object for innodb recovery and archived logs applying. It is created and initialized in recv_sys_create() and recv_sys_init() functions correspondingly. The following fields if recv_sys object are the most important from my point: recv_sys->limit_lsn – the LSN up to which recovery should be made, this value is initialized with the maximum value of uint64_t(see #define LSN_MAX) for the recovery process and with certain value which is passed as an argument of recv_recovery_from_archive_start() function and can be set via xtrabackup option for log applying; recv_sys->parse_start_lsn – the LSN from which logs parsing is started, for the the logs recovery this value equals to the last checkpoint LSN, for logs applying this is last applied LSN; recv_sys->scanned_lsn – the LSN up to which log files are scanned; recv_sys->recovered_lsn – the LSN up to which log records are applied, this value <= recv_sys->scanned_lsn; The first thing that must be done for starting recovery process is to find out the point in log files where the recovery must be started from. This is the last checkpoint LSN. recv_find_max_checkpoint() proceed this. As we can see in log_group_checkpoint() the following code writes checkpoint info into two places in the first log file depending on the checkpoint number: /* We alternate the physical place of the checkpoint info in the first log file */ if ((log_sys->next_checkpoint_no & 1) == 0) { write_offset = LOG_CHECKPOINT_1; } else { write_offset = LOG_CHECKPOINT_2; } So recv_find_max_checkpoint() reads checkpoint info from both places and selects the latest checkpoint. The same idea is applied for logs, too, but the last applied LSN instead of last checkpoint LSN must be found. Here is the call stack for reading last applied LSN: innobase_start_or_create_for_mysql()-> open_or_create_data_files()-> fil_read_first_page(). The last applied LSN is stored in the first page of data files in (min|max)_flushed_lsn fields(see FIL_PAGE_FILE_FLUSH_LSN offset). These values are written in fil_write_flushed_lsn_to_data_files() function on server shutdown. So the main difference between logs applying and recovery process at this stage is the manner of calculating LSN from which log records will be read. For logs applying the last flushed LSN is used but for recovery process it is the last checkpoint LSN. Why does this difference take place? Logs can be applied periodically. Assume we gather archived logs and apply them once an hour to have fresh backup. After applying the previous bunch of log files there can be unfinished transactions. For the recovery process any unfinished transactions are rolled back to have consistent db state at server starting. But for the logs applying process there is no need to roll back them because any unfinished transactions can be finished during the next logs applying. After calculating the start LSN the sequence of actions is the same for both recovering and applying. The next step is reading and parsing log records. See recv_group_scan_log_recs() which is invoked from recv_recovery_from_checkpoint_start_func() for logs recovering and recv_recovery_from_archive_start()->log_group_recover_from_archive_file() for logs applying. The first we read log records to some buffer and then invoke recv_scan_log_recs() to parse them. recv_scan_log_recs() checks each log block on consistency(checksum + comparing the log block number written in log block with log block number calculated from log block LSN) and other edge cases and copy it to parsing buffer recv_sys->buf with recv_sys_add_to_parsing_buf() function. The parsing buffer is then parsed by recv_parse_log_recs(). Log records are stored in hash table recv_sys->addr_hash. The key for this hash table is calculated basing on space id and page number pair. This pair refers to the page to which log records must be applied. The value of the hash table is object of recv_addr_t type. recv_addr_t type contains rec_list field which is the list of log records for applying to the (space id, page num) page (see recv_add_to_hash_table(). After parsing and storing log record in hash table recv_sys->addr_hash log records are applied. The function which is responsible for log records applying is recv_apply_hashed_log_recs(). It is invoked from recv_scan_log_recs() if there is no enough memory to store log records and at the end of recovering/applying process. For each element of recv_sys->addr_hash, i.e. for each DB page which must be changed with log records recv_recover_page() is invoked. It can be invoked as from recv_apply_hashed_log_recs() in the case if page is already in buffer pool of from buf_page_io_complete() on io completion, i.e. just after page was read from storage. Applying log records on page read completion is necessary and very convenient. Assume log records have not yet applied as we had enough memory to store the whole recovery log records. But we want for example to boot DB dictionary. I this case any records that concern to the pages of the dictionary will be applied to those pages just after reading them from storage to buffer pool. The function which applies log records to the certain page is recv_recover_page_func(). It gets the list of log records for the certain page from recv_sys->addr_hash hash table, for each element of this list it compares the lsn of last page changes with the LSN of the record, and if the former is greater the later it applies log record to the page. After applying all log records from archived logs xtrabackup writes last applied LSN to (min|max)_flushed LSN fields of each data file and finishes execution. The logs recovery process rollbacks all unfinished transactions unless this is forbidden with innodb-force-recovery parameter. Conclusion We covered the processes of redo logs writing and recovery in depth. These are very important processes as they provide data consistency on crashes. These two processes became a base for logs archiving and applying features. As log records can describe any data changes the idea is to store these records somewhere and then apply them to backups for organizing some kind of incremental backup. The features were implemented a short time ago and currently they are not widely used. So if you have something to say about them you are welcome to comment for discussion.

In the last week I was working on a key project to migrate a BI platform from MySQL to MongoDB. We chose that database due to its support and scalability.

Introduction All developers are very much concerned about performance. If someone say that this increase performance, all the developer are running behind it. It is not a bad practice at all. Rather as per my point of view we must span all our effort related improve the performance of query. “One common question that we find that, if we change the ordering of table join in case of inner join will effect or increase performance” To understand it lets take a simple example of Inner join. There is two tables named Table-A and Table-B. We can us the Inner Join on both the table. Like this FROM [Table-A] AS a INNER JOIN [Table-B] AS b ON a.IDNO = b.IDNO OR FROM [Table-B] AS a INNER JOIN [Table-A] AS b ON a.IDNO = b.IDNO Which one is best for performance? To answer this question we all know that whenever a SQL Query is executed the MS SQL server create several query plans with different join Order and choose the best one. That means the Join order that we are writing in the query may not be executed by execution plan. May be different join order is used by the execution plan. In the above case the execution plan decide which Join order he will chose depends on best possible costing of execution. Here [Table-A] JOIN [Table-B] or [Table-B] JOIN [Table-A], MS SQL Server knows it well that both are same. To understand it Details Lets take an Example Step-1 [ Create Base Table and Insert Some Records ] -- Item Master IF OBJECT_ID(N'dbo.tbl_ITEMDTLS', N'U')IS NOT NULL BEGIN DROP TABLE [dbo].[tbl_ITEMDTLS]; END GO CREATE TABLE [dbo].[tbl_ITEMDTLS] ( ITEMCD INT NOT NULL IDENTITY PRIMARY KEY, ITEMNAME VARCHAR(50) NOT NULL ) GO -- Inserting Records INSERT INTO [dbo].[tbl_ITEMDTLS] (ITEMNAME) VALUES ('ITEM-1'),('ITEM-2'),('ITEM-3'); -- Item UOM Master IF OBJECT_ID(N'dbo.tbl_UOMDTLS', N'U')IS NOT NULL BEGIN DROP TABLE [dbo].[tbl_UOMDTLS]; END GO CREATE TABLE [dbo].[tbl_UOMDTLS] ( UOMCD INT NOT NULL IDENTITY PRIMARY KEY, UOMNAME VARCHAR(50) NOT NULL ) GO -- Inserting Records INSERT INTO [dbo].[tbl_UOMDTLS] (UOMNAME) VALUES ('KG'),('LTR'),('GRM'); GO -- Transaction Table IF OBJECT_ID(N'dbo.tbl_SBILL', N'U')IS NOT NULL BEGIN DROP TABLE [dbo].[tbl_SBILL]; END GO CREATE TABLE [dbo].[tbl_SBILL] ( TRID INT NOT NULL IDENTITY PRIMARY KEY, ITEMCD INT NOT NULL, UOMCD INT NOT NULL, QTY DECIMAL(18,3) NOT NULL, RATE DECIMAL(18,2) NOT NULL, AMOUNT AS QTY * RATE ); GO -- Foreign Key Constraint ALTER TABLE [dbo].[tbl_SBILL] ADD CONSTRAINT FK_ITEM_tbl_SBILL FOREIGN KEY(ITEMCD) REFERENCES [dbo].[tbl_ITEMDTLS](ITEMCD); GO ALTER TABLE [dbo].[tbl_SBILL] ADD CONSTRAINT FK_UOMCD_tbl_SBILL FOREIGN KEY(UOMCD) REFERENCES [dbo].[tbl_UOMDTLS](UOMCD); -- Insert Records INSERT INTO [dbo].[tbl_SBILL] (ITEMCD, UOMCD, QTY, RATE) VALUES (1, 1, 20, 2000),(2, 3, 23, 1400); Step-2 [ Now Make Some JOIN ] SELECT b.TRID, b.ITEMCD, a.ITEMNAME, b.UOMCD, c.UOMNAME, b.QTY, b.RATE, b.AMOUNT FROM [dbo].[tbl_ITEMDTLS] AS a INNER JOIN [dbo].[tbl_SBILL] AS b ON a.ITEMCD = b.ITEMCD INNER JOIN [dbo].[tbl_UOMDTLS] AS c ON b.UOMCD = c.UOMCD; Here [tbl_ITEMDETAILS] JOIN [tbl_SALES] JOIN [tbl_UOMDETAILS] If we look at the Execution Plan We find that [tbl_SALES] JOIN [tbl_ITEMDETAILS] JOIN [tbl_UOMDETAILS] Step-2 [ Now we need to Force Order Hint to maintain Join Order ] SELECT b.TRID, b.ITEMCD, a.ITEMNAME, b.UOMCD, c.UOMNAME, b.QTY, b.RATE, b.AMOUNT FROM [dbo].[tbl_ITEMDTLS] AS a INNER JOIN [dbo].[tbl_SBILL] AS b ON a.ITEMCD = b.ITEMCD INNER JOIN [dbo].[tbl_UOMDTLS] AS c ON b.UOMCD = c.UOMCD OPTION ( QUERYRULEOFF JoinCommute); For this we need the FORCE ORDER Hint. The query optimizer uses different rules to evaluate different plan and one of the rules is called JoinCommute. We can turn it off using the undocumented query hint QUERYRULEOFF. Hope you like it.