MyCATApache/Mycat-Server · error · RuntimeException

not where of sql

Error message

not where of sql

What it means

MongoSQLParser.DeleteDate() refuses to translate a SQL DELETE statement into a MongoDB remove() call when the statement has no WHERE clause. The library deliberately forbids unbounded deletes: without a where expression there is no query DBObject to pass to DBCollection.remove(), and executing it would wipe the whole collection. It throws a plain RuntimeException with the message 'not where of sql'.

Solutions

1. Add a WHERE clause to the DELETE statement that identifies the rows/documents to remove (e.g. `DELETE FROM t WHERE _id = ...`).
2. If the intent is to empty the collection, drop or clear the collection directly in MongoDB instead of routing a bare DELETE through MyCat.
3. If unfiltered deletes are genuinely required, patch DeleteDate() to throw a clearer IllegalArgumentException or to reject at SQL-parse time with a descriptive message.

Example fix

// before
DELETE FROM users;

// after
DELETE FROM users WHERE last_login < '2020-01-01';

String sql = sqlText.trim().toUpperCase();
if (sql.startsWith("DELETE") && !sql.contains("WHERE")) {
    throw new IllegalArgumentException("Refusing to run DELETE without WHERE against MongoDB backend");
}
// only then hand the statement to the MongoDB-backed connection

When it happens

Trigger: Executing a DELETE statement without a WHERE clause through MyCat's MongoDB backend, e.g. `DELETE FROM mycollection`. DeleteDate() checks state.getWhere() == null and throws before building the query.

Common situations: Developers or tools issuing unconditional DELETEs (cleanup scripts, ORMs generating bulk deletes) against a MongoDB collection via MyCat; migrations expecting MySQL-like behavior where such a statement would at least be explicit; tests that truncate data with bare DELETE statements.

Understand the failure class

Background: "missing required argument" and "the following required arguments were not provided": what required-argument errors mean and how to fix them — this error's family across 20 libraries.

Related errors

• not a query sql statement
• number of columns error
• number of values and columns have to match
• like SQL error
• Can't identify the operation of of where

AI-assisted analysis of MyCATApache/Mycat-Server@65f8d8beb7 (2026-09-11). Data as JSON: /api/errors/5b54676a57d2698f. Report an issue: GitHub.

Appendix: source

Thrown at src/main/java/io/mycat/backend/jdbc/mongodb/MongoSQLParser.java:221

		SQLExpr expr=state.getWhere();
		DBObject query = parserWhere(expr);
		
		BasicDBObject set = new BasicDBObject();
		for(SQLUpdateSetItem col : state.getItems()){
			set.put(getFieldName2(col.getColumn()), getExpValue(col.getValue()));	
		}
		DBObject mod = new BasicDBObject("$set", set);
		WriteResult result = coll.updateMulti(query, mod);
		//System.out.println("changs count:"+coll.getStats().size());
		return result.getN();
	}
	private int DeleteDate(SQLDeleteStatement state) {
		SQLTableSource table=state.getTableSource();
		DBCollection coll =this._db.getCollection(table.toString());
		
		SQLExpr expr=state.getWhere();
		if (expr==null) {
			throw new RuntimeException("not where of sql");
		}
		DBObject query = parserWhere(expr);

		WriteResult result = coll.remove(query);
		return result.getN();
	}
	private int dropTable(SQLDropTableStatement state) {		
		for (SQLTableSource table : state.getTableSources()){
			DBCollection coll =this._db.getCollection(table.toString());
			coll.drop();
		}
		return 1;
		
	}
	
	private int getSQLExprToInt(SQLExpr expr){
		if (expr instanceof SQLIntegerExpr){
			return ((SQLIntegerExpr)expr).getNumber().intValue();

View on GitHub (pinned to 65f8d8beb7)