I'm working on installing SAP Commerce 2105 locally on a mac, and I'm using mysql 8.0.31 community server. When running the 'ant initialize' command, it's failing with the following error:
bad SQL grammar [ CREATE TABLE cmscomponent ...
I have confirmed that it's failing on the creation of the cmscomponent table, and I've confirmed that other tables have been successfully created. I've tried running the 'ant initialize' command several times, and it fails at the same spot each time.
Doing some googling on the error that I'm getting, the problem seems to be related to the number and size of the columns that are being created. I can get a more specific error by manually executing the sql statement to create the cmscomponent table:
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs.
The solutions that I've seen for this problem say to change the data type for columns of type varchar to text. Unfortunately, I don't know how to do that with Commerce, and it seems to me like this would be a setting somewhere in the configuration of Commerce. Does anyone have an idea of what I should do here?
After getting a hint from an SAP consultant, I was able to fix my problem. In my case, I was getting the 'row size too large' error because of too many columns we've added to the cmscomponent table that ended up being created with a type of varchar(255). The total size being allocated for this one table was surpassing 65535, and that's why the command was failing.
The solution was to find the columns that were being created as varchar(255) in our extension's *core-items.xml file, such as this:
<attribute qualifier="compBackground"
type="java.lang.String">
<description>Background for the Component</description>
<persistence type="property" />
<modifiers optional="true" />
</attribute>
Note that we never actually specified 'varchar(255)' in the xml file, but that's what the column type ended up being in the mysql database. I was able to determine which columns had a type of 'varchar(255)' by examining the sql statement that the 'ant initialize' command had failed on.
I then added a qualifier to the persistence element so that it now looks like this:
<attribute qualifier="compBackground"
type="java.lang.String">
<description>Background for the Component</description>
<persistence type="property">
<columntype database="mysql">
<value>TEXT</value>
</columntype>
</persistence>
<modifiers optional="true" />
</attribute>
So you can see that when using a mysql database, this column is to be created with type 'TEXT'. It seems you can add qualifiers like this to have the column type be whatever you want for which ever type of database you're using. In my opinion, this is a pretty clever way that SAP came up with to handle this situation.
Note that I did not change all of the varchar(255) columns to have this qualifier in our *core-items.xml file, I only did about 30 or so (there's over 100 total columns of type varchar(255) for this table). I wanted to see if I could solve the problem without actually converting all of the columns, and it turns out I could.
Installing Commerce on a mac with MySQL is a new thing for us. We normally use Windows workstations with an MS SQL Server database, and I will make sure this change doesn't adversely affect our MS SQL Server installations before merging the code.
I will say that my initial experience with Commerce on the mac has been very positive. It's very fast compared to Windows.