I have a monolithic service at work. There is a single mysql database say X to which it connects. X has a lot of tables. X has now become a bottleneck for scaling up and hence I am working on removing non critical tables from X to a separate instance say Y. Currently I have targetted some audit tables to be moved out from X to Y. The plan of action is as follows : 1) A new DB instance is being brought up by the DBA team with the target tables of X. Not sure about the exact details. 2) On the application side, I am thinking of creating multiple hibernate session factory which will connect to the new instance Y. Then I plan of having feature gate flags to modify the read/write behaviour of the application of the target tables. 2.1) The write feature flags would have values as 0(write to old db),1 (write to old and new db),2 (write to new db). 2.2) The read feature flags would have values as 0(read from old db), 1(read from new db).
On the day of deployment, 1) we plan to take a downtime 2) The DBA team will be ready with the new instance 3) I put the write feature flag of the application to mode 1 and read feature flag to still be 0. If the setup runs fine for probably a couple of weeks, then migrate read flag to 1 and write flag to 2.
Please do help me out about the flaws in the plan.
The monolithic service is legacy service and is a springboot 1.4.0 with spring orm. Mysql version is 5.7 Coming to my main question about enabling a setup like writes to tables in two separate db. I have followed the link Hibernate configuring multiple datasources and multiple session factories for application to connect to multiple databases. I have duplicated the entity and dao of the target tables. In the service method, I have added code to write based on the feature flags. But when I test the code with write mode 1, the data is getting persisted to X but it is not getting persisted in Y. I have debugged a lot with format_sql and show_sql true but I am unable to figure out the reason.
Accessing the db X and Y via sequel pro, I have verified that the data gets persisted in the target table in X but not in target table in Y. Though when I insert a row via query in the target table in Y, The primary id field which is an autogenerated field has values incremented.
@Entity
@Data
@Table(name = "time_audit")
public class TimeAudit {
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name="id") private long id;
@Column(name="order_id") private String orderId;
@Column(name = "status") private String status;
@Column(name = "duration") private Double duration;
@Column(name = "calculated_for") private String calculatedFor;
}
@Entity
@Data
@Table(name = "time_audit_v2")
public class TimeAuditV2 {
@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "id")
private long id;
@Column(name = "order_id")
private String orderId;
@Column(name = "status")
private String status;
@Column(name = "duration")
private Double duration;
@Column(name = "calculated_for")
private String calculatedFor;
}
@Repository
public class TimeAuditDaoImpl implements TimeAuditDao {
@Autowired
@Qualifier("sessionFactory") private SessionFactory sessionFactory;
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void saveTimeAudit(Double time, String orderId, String status, String calculatedFor){
Session session = sessionFactory.getCurrentSession();
TimeAudit timeAudit = new TimeAudit();
timeAudit.setDuration(time);
timeAudit.setOrderId(orderId);
timeAudit.setStatus(status);
timeAudit.setCalculatedFor(calculatedFor);
session.save(timeAudit);
}
}
@Repository
public class TimeAuditDaoImplV2 implements TimeAuditDaoV2 {
@Autowired
@Qualifier("taskSeqAuditSessionFactory")
private SessionFactory taskSeqAuditSessionFactory;
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void saveTimeAudit(Double time, String orderId, String status, String calculatedFor) {
Session session = taskSeqAuditSessionFactory.getCurrentSession();
TimeAuditV2 timeAudit = new TimeAuditV2();
timeAudit.setDuration(time);
timeAudit.setOrderId(orderId);
timeAudit.setStatus(status);
timeAudit.setCalculatedFor(calculatedFor);
session.save(timeAudit);
}
}
@Service
public class TimeAuditServiceImpl implements TimeAuditService {
@Autowired
private DeliveryConfigDao deliveryConfigDao;
@Autowired
private TimeAuditDao timeAuditDao;
@Autowired
private TimeAuditDaoV2 timeAuditDaoV2;
@Transactional(propagation = Propagation.REQUIRED)
@Override
public void saveTimeAudit(Double time, String orderId, String status, String calculatedFor) {
String taskSeqAuditSwitch = deliveryConfigDao.getConfig(GenericStringConstants.TASK_SEQ_AUDIT_WRITE_FEATURE_KEY, GenericStringConstants.TASK_SEQ_AUDIT_WRITE_DELIVERY_DB);
if (taskSeqAuditSwitch.equalsIgnoreCase(GenericStringConstants.TASK_SEQ_AUDIT_WRITE_DELIVERY_DB)) {
timeAuditDao.saveTimeAudit(time, orderId, status, calculatedFor);
} else if (taskSeqAuditSwitch.equalsIgnoreCase(GenericStringConstants.TASK_SEQ_AUDIT_WRITE_BOTH_DB)) {
timeAuditDao.saveTimeAudit(time, orderId, status, calculatedFor);
timeAuditDaoV2.saveTimeAudit(time, orderId, status, calculatedFor);
} else if (taskSeqAuditSwitch.equalsIgnoreCase(GenericStringConstants.TASK_SEQ_AUDIT_WRITE_TASK_SEQ_DB)) {
timeAuditDaoV2.saveTimeAudit(time, orderId, status, calculatedFor);
}
}
}
[database.xml]
<bean id="slaveSessionFactory"
class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">com.swiggy.delivery.deliveryboy.model.dao.CustomMySql5Dialect</prop>
<prop key="hibernate.show_sql">${hibernate.slave.showSql}</prop>
<prop key="hibernate.format_sql">${hibernate.slave.formatSql}</prop>
<prop key="hibernate.connection.url">${hibernate.slave.connection.url}</prop>
<prop key="hibernate.connection.driver_class">com.mysql.jdbc.ReplicationDriver</prop>
<prop key="hibernate.connection.username">${hibernate.slave.connection.username}</prop>
<prop key="hibernate.connection.password">${hibernate.slave.connection.password}</prop>
<prop key="hibernate.c3p0.min_size">${hibernate.slave.c3p0.min_size}</prop>
<prop key="hibernate.c3p0.max_size">${hibernate.slave.c3p0.max_size}</prop>
<prop key="hibernate.c3p0.timeout">300</prop>
<prop key="hibernate.c3p0.max_statements">0</prop>
<prop key="hibernate.c3p0.idle_test_period">3000</prop>
<prop key="hibernate.c3p0.max_idle_time_excess_connections">60</prop>
</props>
</property>
<property name="packagesToScan"
value="com.swiggy.delivery.data.sql.entities, com.swiggy.delivery.referral.data.sql.entities, com.swiggy.delivery.sno.data.sql.entities, com.swiggy.delivery.apartmentSecurityPartners.data.sql.entities, com.swiggy.delivery.callStatus.data.sql.entities,
com.swiggy.delivery.loyaltyProgram.entities,com.swiggy.delivery.daily.entities,com.swiggy.delivery.service_line.entities,com.swiggy.delivery.hub.entities, com.swiggy.delivery.userCityMapping.entities,com.swiggy.delivery.userZoneMapping.entities,com.swiggy.delivery.userHubMapping.entities,com.swiggy.delivery.nudge.de.sql.entities,com.swiggy.delivery.bank.account.validation.entities"/>
</bean>
<bean id="sessionFactory"
class="org.springframework.orm.hibernate4.LocalSessionFactoryBean" primary="true">
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.show_sql">${hibernate.showSql}</prop>
<prop key="hibernate.format_sql">${hibernate.formatSql}</prop>
<prop key="hibernate.connection.url">${hibernate.connection.url}</prop>
<prop key="hibernate.connection.driver_class">com.mysql.jdbc.Driver</prop>
<prop key="hibernate.connection.username">${hibernate.connection.username}</prop>
<prop key="hibernate.connection.password">${hibernate.connection.password}</prop>
<prop key="hibernate.c3p0.min_size">${hibernate.c3p0.min_size}</prop>
<prop key="hibernate.c3p0.max_size">${hibernate.c3p0.max_size}</prop>
<prop key="hibernate.c3p0.timeout">300</prop>
<prop key="hibernate.c3p0.max_statements">0</prop>
<prop key="hibernate.c3p0.idle_test_period">3000</prop>
</props>
</property>
</bean>
<bean id="taskSeqAuditSessionFactory"
class="org.springframework.orm.hibernate4.LocalSessionFactoryBean" >
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.show_sql">${hibernate.taskSeqAudit.showSql}</prop>
<prop key="hibernate.format_sql">${hibernate.taskSeqAudit.formatSql}</prop>
<prop key="hibernate.connection.url">${hibernate.taskSeqAudit.connection.url}</prop>
<prop key="hibernate.connection.driver_class">com.mysql.jdbc.Driver</prop>
<prop key="hibernate.connection.username">${hibernate.taskSeqAudit.connection.username}</prop>
<prop key="hibernate.connection.password">${hibernate.taskSeqAudit.connection.password}</prop>
<prop key="hibernate.c3p0.min_size">${hibernate.taskSeqAudit.c3p0.min_size}</prop>
<prop key="hibernate.c3p0.max_size">${hibernate.taskSeqAudit.c3p0.max_size}</prop>
<prop key="hibernate.c3p0.timeout">300</prop>
<prop key="hibernate.c3p0.max_statements">0</prop>
<prop key="hibernate.c3p0.idle_test_period">3000</prop>
</props>
</property>
<property name="packagesToScan" value="com.swiggy.delivery.data.sql.entities" />
</bean>
<bean id="slaveTaskSeqAuditSessionFactory"
class="org.springframework.orm.hibernate4.LocalSessionFactoryBean" >
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.show_sql">${hibernate.slave.taskSeqAudit.showSql}</prop>
<prop key="hibernate.format_sql">${hibernate.slave.taskSeqAudit.formatSql}</prop>
<prop key="hibernate.connection.url">${hibernate.slave.taskSeqAudit.connection.url}</prop>
<prop key="hibernate.connection.driver_class">com.mysql.jdbc.Driver</prop>
<prop key="hibernate.connection.username">${hibernate.slave.taskSeqAudit.connection.username}</prop>
<prop key="hibernate.connection.password">${hibernate.slave.taskSeqAudit.connection.password}</prop>
<prop key="hibernate.c3p0.min_size">${hibernate.slave.taskSeqAudit.c3p0.min_size}</prop>
<prop key="hibernate.c3p0.max_size">${hibernate.slave.taskSeqAudit.c3p0.max_size}</prop>
<prop key="hibernate.c3p0.timeout">300</prop>
<prop key="hibernate.c3p0.max_statements">0</prop>
<prop key="hibernate.c3p0.idle_test_period">3000</prop>
</props>
</property>
<property name="packagesToScan" value="com.swiggy.delivery.data.sql.entities" />
</bean>
<bean id="transactionManager"
class="org.springframework.orm.hibernate4.HibernateTransactionManager" primary="true">
<property name="sessionFactory" ref="sessionFactory" />
<property name="defaultTimeout" value="10" />
</bean>
<bean id="slaveTransactionManager"
class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="slaveSessionFactory" />
<property name="defaultTimeout" value="10" />
</bean>
<bean id="taskSeqAuditTransactionManager"
class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="taskSeqAuditSessionFactory" />
<property name="defaultTimeout" value="10" />
</bean>
<bean id="slaveTaskSeqAuditTransactionManager"
class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="slaveTaskSeqAuditSessionFactory" />
<property name="defaultTimeout" value="10" />
</bean>
<tx:annotation-driven transaction-manager="transactionManager" />
<tx:annotation-driven transaction-manager="slaveTransactionManager"/>
<tx:annotation-driven transaction-manager="taskSeqAuditTransactionManager"/>
<tx:annotation-driven transaction-manager="slaveTaskSeqAuditTransactionManager"/>
Assuming what you want is to write (hibernate) objects to tables in two different databases, maybe this old tutorial on springboot 1.4 relating to that may help:
http://roufid.com/spring-boot-multiple-databases-configuration/
You will have to create two datasources, entity managers and transaction managers.
Cheers
PS: I'm assuming that because there is no specific indication in your question, and in the scenario you describe there is no more indication of specific code questions, supported with any log and test result, and/or error message. Please remember the more specific a question is, the less ambiguous the answer is.