#phenonmenon

try..catch here does not print call stack. It is not friendly for debug.

		catch (SQLException ex) {
			log .error("--- Unexpected SQLException caught in NEW-ORDER Txn ---");
			while (ex != null) {
				log.error(ex.getMessage());
				ex = ex.getNextException();
			}

workaround

add Exception.printStackTrace() to it.

phenononon

newOrderTransaction() has a fixed(issue #3) JOIN SQL belows. But MyCat( 1.6.5 BETA ) cannot handle it properly, too.. Have to work around than just waiting MyCat's fix. Again.

.prepareStatement("SELECT c.c_discount, c.c_last, c.c_credit, w.w_tax"
    + "  FROM customer AS c, warehouse AS w"
    + " WHERE w.w_id = ? AND w.w_id = c.c_w_id"
    + " AND c.c_d_id = ? AND c.c_id = ?");

workaround

Split the JOIN into an equivalent two step SELECT sequence.

Firstly, get the w_id, w_tax from warehouse. Because w_id is the
primary key of warehouse, no more than one record will be returned. So we can use basic variables to cahce it.

.prepareStatement("SELECT w_id, w_tax"
    + " FROM warehouse"
    + " WHERE w_id = ? ");

// store w_id of the result set in Java variable w_w_id as a middle data

Secondly, try to get other fields from customer by cached w_id.

Java .prepareStatement("SELECT c_discount, c_last, c_credit" + " FROM customer" + " WHERE c_w_id = "+ w_w_id +" AND" + " c_d_id = ? AND c_id = ?");

Finally, return w_tax from step one, c_discount, c_last and c_credit from step two.

phenonmenon

When step in stmtUpdateStock.executeBatch() of newOrderTransaction() in jTPCCTerminal.java, MyCat alarms that it cannot handle such command( say, unkown command ).

workaround

Replace java.sql.preparedStatement.executeBatch() with java.sql.preparedStatement.executeUpdate().

phenononon

newOrderTransaction() has JOIN SQL belows. MyCat( 1.6.5 BETA ) cannot identify columns without explicitly assigned table name. Better to work around than just waiting MyCat's fix.

.prepareStatement("SELECT c_discount, c_last, c_credit, w_tax"
    + "  FROM customer, warehouse"
    + " WHERE w_id = ? AND w_id = c_w_id"
    + " AND c_d_id = ? AND c_id = ?");

workaround

Make columns having full qualified name with alias syntax in SQL.

.prepareStatement("SELECT c.c_discount, c.c_last, c.c_credit, w.w_tax"
    + "  FROM customer AS c, warehouse AS w"
    + " WHERE w.w_id = ? AND w.w_id = c.c_w_id"
    + " AND c.c_d_id = ? AND c.c_id = ?");

phenonmenon

orderStatusTransaction() has the SQL belows. MyCat (1.6.5 BETA) can not parse it well, because it is a JOIN with aggregate function COUNT(). Also we cannot just wait before MyCat fix it or do somethings.

.prepareStatement("SELECT COUNT(DISTINCT (s_i_id)) AS stock_count"
      + " FROM order_line, stock"
      + " WHERE ol_w_id = ?"
      + " AND ol_d_id = ?"
      + " AND ol_o_id < ?"
      + " AND ol_o_id >= ? - 20"
      + " AND s_w_id = ?"
      + " AND s_i_id = ol_i_id"
      + " AND s_quantity < ?");

workaround

It seems that the current MyCat is not good at subquery, so it is suggested to split the query into 2-step simple CRUD query like belows:

1. Get the list of ol_i_id from order_line

.prepareStatement("SELECT ol_i_id"
      + " FROM order_line"
      + " WHERE ol_w_id = ?"
      + " AND ol_d_id = ?"
      + " AND ol_o_id < ?"
      + " AND ol_o_id >= ? - 20");		

// concate ol_i_id with comma, like this: "ol_i_id1, ol_i_id2, ..."

2. Do SELECT on stock with ol_i_id list from above

.prepareStatement("SELECT COUNT(DISTINCT (s_i_id)) AS stock_count"
    + " FROM stock"
    + " WHERE s_w_id = ?"
    + " AND s_quantity < ?"
    + " AND s_i_id IN (" + in_list + ")");		

phenomenon

When MyCat(1.6.5) passes SQLs( example belows ) from front-end clients to back-end MySQLs, it will not drop or change the schema_name of the SQL.

SELECT ... FROM schema_name.table_name ...

If the virtual schema on MyCat is not as same as the physical schema on MySQL, there would be an error.

It surely is bug or missing point of MyCat, but we should do a workaround on our benchmarksql, instead of just waiting it to be fixed.

workaround

get rid of all schema_name from SQL text