Answers.Body: <p>Adding the following to <code>db.php</code> fixed the error:</p>
  
  <pre><code>'collation' =&gt; 'utf8_unicode_ci',
  'charset' =&gt; 'utf8'
  </code></pre>
  
  <p>My guess is that there was some permission issue that was stopping it from getting those automatically, but I'm not entirely sure how they grab the values if its not set by default.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-11-13T18:19:00.787Z
Answers.LastActivityDate: 2017-11-13T18:19:00.787Z
Answers.UserDisplayName: jassok
Body: <p>Running Craft Version 2.6.2994 on an EC2 instance that has PHP 7.0.21, and MySQL v 5.6.37 installed. When I try to create a new Matrix, I get the following error when saving.</p>
  
  <p>When I test this on my localhost, which has PHP 7.1.5 and MySQL 5.6.35 running Valet, everything seems to work fine so I'm not sure if this is an issue with the version of PHP/MySQL, or in the code. I've also tried it with the root MySQL user having full permissions and it still gives the same error.</p>
  
  <pre><code>CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: 
  Syntax error or access violation: 1064 You have an error in your SQL syntax; 
  check the manual that corresponds to your MySQL server version for the right 
  syntax to use near 'COLLATE=' at line 9
  </code></pre>
  
  <p>The entire project shows <code>'COLLATE='</code> appears in ~2 locations:</p>
  
  <p>MysqlSchema.php, LN 198:</p>
  
  <pre><code>$options = 'ENGINE='.$engine.' DEFAULT CHARSET='.craft()-&gt;config-&gt;get('charset', ConfigFile::Db).' COLLATE='.craft()-&gt;config-&gt;get('collation', ConfigFile::Db).($options ? ' '.$options : '');`
  </code></pre>
  
  <p>And DbHelper.php, LN 219:</p>
  
  <pre><code>$def .= ' COLLATE '.$config['collation'];
  </code></pre>
  
  <p>Current Plugins Installed:</p>
  
  <ul>
  <li><a href="https://github.com/verbb/expanded-singles/blob/master/README.md" rel="nofollow noreferrer">Expanded Singles 0.2.5</a></li>
  <li>JSON Decode Twig Filter 1.0.9</li>
  <li><a href="https://github.com/benjamminf/craft-neo/wiki" rel="nofollow noreferrer">Neo 1.4.1</a></li>
  <li><a href="https://github.com/mmikkel/Reasons-Craft/blob/master/README.md" rel="nofollow noreferrer">Reasons 1.0.10</a></li>
  <li><a href="https://github.com/benjamminf/craft-relabel/blob/master/README.md" rel="nofollow noreferrer">Relabel 0.1.3</a></li>
  <li>SEO 1.4.7</li>
  <li><a href="https://github.com/pennebaker/craftcms-thearchitect/wiki" rel="nofollow noreferrer">Sidebar enhancer 1.0.9</a></li>
  <li><a href="https://github.com/pennebaker/craftcms-thearchitect/wiki" rel="nofollow noreferrer">The Architect 1.6.0</a></li>
  </ul>
  
  <p>Edit: Full stack trace:</p>
  
  <pre><code>2017/11/07 16:28:18 [error] [system.db.CDbCommand] CDbCommand::execute() failed: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE=' at line 9. The SQL statement executed was: CREATE TABLE `craft_matrixcontent_test` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `elementId` INT(11) NOT NULL,
          `locale` CHAR(12) NOT NULL,
          `dateCreated` datetime NOT NULL,
          `dateUpdated` datetime NOT NULL,
          `uid` CHAR(36) NOT NULL DEFAULT 0,
          PRIMARY KEY (id)
  ) ENGINE=InnoDb DEFAULT CHARSET= COLLATE=.
  2017/11/07 16:28:18 [error] [exception.CDbException] CDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE=' at line 9 in /var/www/craft/app/framework/db/CDbCommand.php:358
  Stack trace:
  #0 /var/www/craft/app/framework/db/CDbCommand.php(1352): CDbCommand-&gt;execute()
  #1 /var/www/craft/app/etc/db/DbCommand.php(507): CDbCommand-&gt;createTable('craft_matrixcon...', Array, NULL)
  #2 /var/www/craft/app/services/MatrixService.php(1071): Craft\DbCommand-&gt;createTable('craft_matrixcon...', Array)
  #3 /var/www/craft/app/services/MatrixService.php(535): Craft\MatrixService-&gt;_createContentTable('matrixcontent_t...')
  #4 /var/www/craft/app/fieldtypes/MatrixFieldType.php(152): Craft\MatrixService-&gt;saveSettings(Object(Craft\MatrixSettingsModel), false)
  #5 /var/www/craft/app/services/FieldsService.php(646): Craft\MatrixFieldType-&gt;onAfterSave()
  #6 /var/www/craft/app/controllers/FieldsController.php(122): Craft\FieldsService-&gt;saveField(Object(Craft\FieldModel))
  #7 /var/www/craft/app/framework/web/actions/CInlineAction.php(49): Craft\FieldsController-&gt;actionSaveField()
  #8 /var/www/craft/app/framework/web/CController.php(308): CInlineAction-&gt;runWithParams(Array)
  #9 /var/www/craft/app/framework/web/CController.php(286): CController-&gt;runAction(Object(CInlineAction))
  #10 /var/www/craft/app/framework/web/CController.php(265): CController-&gt;runActionWithFilters(Object(CInlineAction), Array)
  #11 /var/www/craft/app/framework/web/CWebApplication.php(282): CController-&gt;run('saveField')
  #12 /var/www/craft/app/etc/web/WebApp.php(823): CWebApplication-&gt;runController('fields/saveFiel...')
  #13 /var/www/craft/app/etc/web/WebApp.php(287): Craft\WebApp-&gt;_processActionRequest()
  #14 /var/www/craft/app/framework/base/CApplication.php(185): Craft\WebApp-&gt;processRequest()
  #15 /var/www/craft/app/index.php(62): CApplication-&gt;run()
  #16 /var/www/public/index.php(31): require_once('/var/www/craft/...')
  #17 {main}
  REQUEST_URI=/admin/settings/fields/new?groupId=3
  HTTP_REFERER=http://ipaddress/admin/settings/fields/new?groupId=3
  ---
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-07T17:03:23.710Z
Comments.Text: MysqlSchema.php, the $options variable is returning `ENGINE=InnoDb DEFAULT CHARSET= COLLATE=` with `craft()->config->get('collation',ConfigFile::Db)` returning Null. Not sure where thats supposed to be getting its value. On local host, it returns the correct value, but not in the EC2 instance..
Comments.UserDisplayName: jassok
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-08T03:46:27.007Z
Comments.Text: Super weird... are you setting any of those config settings in `craft/config/db.php`?
Comments.UserDisplayName: Brad Bell
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-08T03:50:45.163Z
Comments.Text: Also... everything else works fine?  It's just when creating a new Matrix field?  Maybe you're running into your php.ini file's `post_max_size`/`max_input_vars` values?
Comments.UserDisplayName: Brad Bell
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-13T18:17:02.143Z
Comments.Text: Not entirely sure why, perhaps a permission error, but the `craft()->config->get()` function wasn't able to pull in the default values like it normally does for `collation` and `charset`. Adding those both into the db.php settings fixed the issue though.
Comments.UserDisplayName: jassok
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-13T18:20:36.540Z
Comments.Text: Yeah... that's strange.  Regardless, glad you got it sorted!
Comments.UserDisplayName: Brad Bell
Tags: <matrix><error-message><mysql>
Title: MySQL Error 1064 when trying to create a Matrix
ThreadText: # MySQL Error 1064 when trying to create a Matrix
  
  (Asked by: username_0 on 2017-11-07)
  
  Running Craft Version 2.6.2994 on an EC2 instance that has PHP 7.0.21, and MySQL v 5.6.37 installed. When I try to create a new Matrix, I get the following error when saving.
  
  When I test this on my localhost, which has PHP 7.1.5 and MySQL 5.6.35 running Valet, everything seems to work fine so I'm not sure if this is an issue with the version of PHP/MySQL, or in the code. I've also tried it with the root MySQL user having full permissions and it still gives the same error.
  
  ```
  CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: 
  Syntax error or access violation: 1064 You have an error in your SQL syntax; 
  check the manual that corresponds to your MySQL server version for the right 
  syntax to use near 'COLLATE=' at line 9
  
  ```
  
  The entire project shows `'COLLATE='` appears in ~2 locations:
  
  MysqlSchema.php, LN 198:
  
  ```
  $options = 'ENGINE='.$engine.' DEFAULT CHARSET='.craft()->config->get('charset', ConfigFile::Db).' COLLATE='.craft()->config->get('collation', ConfigFile::Db).($options ? ' '.$options : '');`
  
  ```
  
  And DbHelper.php, LN 219:
  
  ```
  $def .= ' COLLATE '.$config['collation'];
  
  ```
  
  Current Plugins Installed:
  
  * [Expanded Singles 0.2.5](https://github.com/verbb/expanded-singles/blob/master/README.md)
  * JSON Decode Twig Filter 1.0.9
  * [Neo 1.4.1](https://github.com/benjamminf/craft-neo/wiki)
  * [Reasons 1.0.10](https://github.com/mmikkel/Reasons-Craft/blob/master/README.md)
  * [Relabel 0.1.3](https://github.com/benjamminf/craft-relabel/blob/master/README.md)
  * SEO 1.4.7
  * [Sidebar enhancer 1.0.9](https://github.com/pennebaker/craftcms-thearchitect/wiki)
  * [The Architect 1.6.0](https://github.com/pennebaker/craftcms-thearchitect/wiki)
  
  Edit: Full stack trace:
  
  ```
  2017/11/07 16:28:18 [error] [system.db.CDbCommand] CDbCommand::execute() failed: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE=' at line 9. The SQL statement executed was: CREATE TABLE `craft_matrixcontent_test` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `elementId` INT(11) NOT NULL,
          `locale` CHAR(12) NOT NULL,
          `dateCreated` datetime NOT NULL,
          `dateUpdated` datetime NOT NULL,
          `uid` CHAR(36) NOT NULL DEFAULT 0,
          PRIMARY KEY (id)
  ) ENGINE=InnoDb DEFAULT CHARSET= COLLATE=.
  2017/11/07 16:28:18 [error] [exception.CDbException] CDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE=' at line 9 in /var/www/craft/app/framework/db/CDbCommand.php:358
  Stack trace:
  #0 /var/www/craft/app/framework/db/CDbCommand.php(1352): CDbCommand->execute()
  #1 /var/www/craft/app/etc/db/DbCommand.php(507): CDbCommand->createTable('craft_matrixcon...', Array, NULL)
  #2 /var/www/craft/app/services/MatrixService.php(1071): Craft\DbCommand->createTable('craft_matrixcon...', Array)
  #3 /var/www/craft/app/services/MatrixService.php(535): Craft\MatrixService->_createContentTable('matrixcontent_t...')
  #4 /var/www/craft/app/fieldtypes/MatrixFieldType.php(152): Craft\MatrixService->saveSettings(Object(Craft\MatrixSettingsModel), false)
  #5 /var/www/craft/app/services/FieldsService.php(646): Craft\MatrixFieldType->onAfterSave()
  #6 /var/www/craft/app/controllers/FieldsController.php(122): Craft\FieldsService->saveField(Object(Craft\FieldModel))
  #7 /var/www/craft/app/framework/web/actions/CInlineAction.php(49): Craft\FieldsController->actionSaveField()
  #8 /var/www/craft/app/framework/web/CController.php(308): CInlineAction->runWithParams(Array)
  #9 /var/www/craft/app/framework/web/CController.php(286): CController->runAction(Object(CInlineAction))
  #10 /var/www/craft/app/framework/web/CController.php(265): CController->runActionWithFilters(Object(CInlineAction), Array)
  #11 /var/www/craft/app/framework/web/CWebApplication.php(282): CController->run('saveField')
  #12 /var/www/craft/app/etc/web/WebApp.php(823): CWebApplication->runController('fields/saveFiel...')
  #13 /var/www/craft/app/etc/web/WebApp.php(287): Craft\WebApp->_processActionRequest()
  #14 /var/www/craft/app/framework/base/CApplication.php(185): Craft\WebApp->processRequest()
  #15 /var/www/craft/app/index.php(62): CApplication->run()
  #16 /var/www/public/index.php(31): require_once('/var/www/craft/...')
  #17 {main}
  REQUEST_URI=/admin/settings/fields/new?groupId=3
  HTTP_REFERER=http://ipaddress/admin/settings/fields/new?groupId=3
  ---
  
  ```
  
  **Tags:** matrix, error-message, mysql
  
  > **Comments:**
  > **username_0**: MysqlSchema.php, the $options variable is returning `ENGINE=InnoDb DEFAULT CHARSET= COLLATE=` with `craft()->config->get('collation',ConfigFile::Db)` returning Null. Not sure where thats supposed to be getting its value. On local host, it returns the correct value, but not in the EC2 instance..  
  > **username_1**: Super weird... are you setting any of those config settings in `craft/config/db.php`?  
  > **username_1**: Also... everything else works fine?  It's just when creating a new Matrix field?  Maybe you're running into your php.ini file's `post_max_size`/`max_input_vars` values?  
  > **username_0**: Not entirely sure why, perhaps a permission error, but the `craft()->config->get()` function wasn't able to pull in the default values like it normally does for `collation` and `charset`. Adding those both into the db.php settings fixed the issue though.  
  > **username_1**: Yeah... that's strange.  Regardless, glad you got it sorted!  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2017-11-13. Score: 2 | Accepted answer
  
  Adding the following to `db.php` fixed the error:
  
  ```
  'collation' => 'utf8_unicode_ci',
  'charset' => 'utf8'
  
  ```
  
  My guess is that there was some permission issue that was stopping it from getting those automatically, but I'm not entirely sure how they grab the values if its not set by default.
  
  
  ---
  
  