What is the recommended column type for the data-column when using MySql? I looked at the Modeshape code base and the mysql maven profile uses 'BLOB' which is the same as above.
I tried changing the value to 'LONGBLOB' and was able to create 10000 child nodes, I have yet to find the limit with 'LONGBLOB'.
It's difficult to translate the size of a node (e.g., number of children and number of properties) into number of characters used in the persisted representation. It certainly won't hurt to use LONGBLOB (and it costs only 3 extra bytes per value compared to BLOB), but if you're trying to really minimize storage space you might try MEDIUMBLOB and see if you run into a limit.
You need to be careful about creating so many children under a single parent. ModeShape is hierarchical, and the more you get away from hierarchical (by creating tons of children in parent nodes), the farther away from ModeShape's sweet spot you're going to get. It will work, but be cognizant of what you're doing and how it affects performance.