[Pgpool-general] large records and autocommit set to False causes
replication error
Christopher Gorge Marges
christophergorge at gmail.com
Tue Feb 7 10:00:53 GMT 2006
Using a java program that creates separate threads that write to one
table, I notice that when the thread is using autocommit(false), when
each thread will write to the database, it causes an error that will
shutdown the master and result in degeneration mode. If I comment the
lines that cause the autocommit, there are no replication mismatch
errors.
The key is the combination of the following:
large data stored in bytea field using setBinaryStream() method in jdbc driver
autoCommit(false)
All must be met for this to happen, either using smaller data in bytea
field or setting autoCommit to true will make the program work. I
believe the problem is in pgpool because when I use the jdbc driver
directly there are no problems with the original test.
Here is the test driver program with the lines that use
autocommit=false are commented out so it will work, just remove the
comment to make the program create and commit transactions on its own
(also the data to save is quite large):
package ph.scn.msh;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import ph.scn.util.ConnectionManager;
class WriterThread extends Thread {
private static final String INSERT_STR =
"INSERT INTO outbox (msg_id, msg, timestamp, status, server_id)" +
"VALUES ( ?, ?, ?, false, ?)";
private Connection conn = null;
private int id = 0;
public WriterThread(int i) {
super();
id = i;
}
public void run() {
String messageId = "";
Calendar EggCal = Calendar.getInstance();
long duration = EggCal.getTimeInMillis();
int imax = 4;
for (int i = 0; i < imax; i++) {
try {
conn = ConnectionManager.getInstance().getConnection();
// conn.setAutoCommit(false);
create();
// conn.commit();
System.out.println("Message no [Thread-" + id + "]: " + i);
sleep(500);
} catch (Exception e) {
// try {
// conn.rollback();
// } catch (SQLException e1) {}
e.printStackTrace();
} finally {
try {
// conn.setAutoCommit(true);
conn.close();
} catch (SQLException e2) {}
}
}
EggCal = Calendar.getInstance(); duration =
EggCal.getTimeInMillis() - duration;
System.out.println(imax + " rows. " + duration/1000 + " secs elapsed.");
}
public void create() throws IOException, SQLException {
PreparedStatement stmt = conn.prepareStatement(INSERT_STR);
try {
stmt.setString(1, (new
Long(System.currentTimeMillis())).toString() + id);
String s = "this is supposed to be a blob stored to bytea in
postgresthis is " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"supposed to be a blob stored to bytea in postgresthis is
supposed to be a " +
"blob stored to bytea in postgresthis is supposed to be a
blob stored to " +
"bytea in postgresthis is supposed to be a blob stored to bytea in " +
"postgresthis is supposed to be a blob stored to bytea in postgres";
ByteArrayInputStream bais = new ByteArrayInputStream(s.getBytes());
stmt.setBinaryStream(2, bais, bais.available());
stmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
stmt.setLong(4, 1);
stmt.executeUpdate();
bais.close();
} finally {
stmt.close();
}
}
}
public class TestWriter {
public static void main(String[] args) {
for (int i = 0; i < 10; i++) {
WriterThread writerThread = new WriterThread(i);
writerThread.start();
}
}
}
The output if the program is set to use autocommit(false) is as follows:
java.lang.ArrayIndexOutOfBoundsException: 127
at org.postgresql.util.ServerErrorMessage.<init>(ServerErrorMessage.java:47)
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1507)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:346)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:300)
at ph.scn.msh.WriterThread.create(TestWriter.java:157)
at ph.scn.msh.WriterThread.run(TestWriter.java:33)
org.postgresql.util.PSQLException: An I/O error occured while sending
to the backend.
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:214)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:346)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:300)
at ph.scn.msh.WriterThread.create(TestWriter.java:157)
at ph.scn.msh.WriterThread.run(TestWriter.java:33)
Caused by: java.io.EOFException
at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:256)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1163)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
... 5 more
org.postgresql.util.PSQLException: An I/O error occured while sending
to the backend.
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:214)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:346)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:300)
at ph.scn.msh.WriterThread.create(TestWriter.java:157)
at ph.scn.msh.WriterThread.run(TestWriter.java:33)
Caused by: java.io.EOFException
at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:256)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1163)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
... 5 more
org.postgresql.util.PSQLException: An I/O error occured while sending
to the backend.
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:214)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:346)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:300)
at ph.scn.msh.WriterThread.create(TestWriter.java:157)
at ph.scn.msh.WriterThread.run(TestWriter.java:33)
Caused by: java.io.EOFException
at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:256)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1163)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
... 5 more
Message no [Thread-6]: 0
Message no [Thread-7]: 0
Message no [Thread-8]: 0
Message no [Thread-9]: 0
Message no [Thread-0]: 0
Message no [Thread-1]: 0
Message no [Thread-3]: 1
Message no [Thread-5]: 1
Message no [Thread-4]: 1
Message no [Thread-2]: 1
Message no [Thread-6]: 1
Message no [Thread-7]: 1
Message no [Thread-8]: 1
Message no [Thread-9]: 1
Message no [Thread-0]: 1
Message no [Thread-1]: 1
Message no [Thread-3]: 2
Message no [Thread-5]: 2
Message no [Thread-4]: 2
Message no [Thread-2]: 2
Message no [Thread-6]: 2
The program continues since it was then running in degenerate mode.
The errors at the start was occured during the replication mismatch
and when pgpool was shutting down the master.
More information about the Pgpool-general
mailing list