SQLSTATE[42000]
Posted by: Claudius (---.bardstown.com)
Date: August 09, 2018 07:57PM

OK...

Here we go. I read the "all you need to know post" and did all the stuff contained on this post. What I did is as follows:

* A clean install of Win 10 Pro V 1803 OS17134.191
* Uninstalled Skype
* Uninstall and then reinstall all the C++ Redistributables from your repository
* Check and confirm the presence of all the required programs, all present and no problems indicated
* Installed WAMP as an Administrator
* Unzipped Humhub into a folder apart from the folder that the Unzip program created

incidental stuff I did:

* Configured router port forwarding
* Installed and configured a DuckDNS domain
* assigned a static IP address to the server NIC

The Information on the third configuration page is, Host = "Localhost," my username and password and the database name, 'rrb." I also tried the database name "redrubberball."


Page three of the HumHub configuration I get the following:


Database Exception – yii\db\Exception
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes
The SQL being executed was: ALTER TABLE `contentcontainer_permission` ADD CONSTRAINT `contentcontainer_permission_pk` PRIMARY KEY (`permission_id`, `group_id`, `module_id`, `contentcontainer_id`)
Error Info: Array
(
[0] => 42000
[1] => 1071
[2] => Specified key was too long; max key length is 1000 bytes
)

Caused by: PDOException
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes
in C:\wamp64\www\RedRubberBall\protected\vendor\yiisoft\yii2\db\Command.php at line 1258
1. in C:\wamp64\www\RedRubberBall\protected\vendor\yiisoft\yii2\db\Schema.php
at line 664




















655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673

$exceptionClass = '\yii\db\Exception';
foreach ($this->exceptionMap as $error => $class) {
if (strpos($e->getMessage(), $error) !== false) {
$exceptionClass = $class;
}
}
$message = $e->getMessage() . "\nThe SQL being executed was: $rawSql";
$errorInfo = $e instanceof \PDOException ? $e->errorInfo : null;
return new $exceptionClass($message, $errorInfo, (int) $e->getCode(), $e);
}

/**
* Returns a value indicating whether a SQL statement is for read purpose.
* @param string $sql the SQL statement
* @return bool whether a SQL statement is for read purpose.
*/
public function isReadQuery($sql)
{

2. in C:\wamp64\www\RedRubberBall\protected\vendor\yiisoft\yii2\db\Command.php
at line 1263
– yii\db\Schema::convertException(PDOException, 'ALTER TABLE `contentcontainer_pe...')
3. in C:\wamp64\www\RedRubberBall\protected\vendor\yiisoft\yii2\db\Command.php
at line 1075
– yii\db\Command::internalExecute('ALTER TABLE `contentcontainer_pe...')
4. in C:\wamp64\www\RedRubberBall\protected\vendor\yiisoft\yii2\db\Migration.php
at line 437
– yii\db\Command::execute()
5. in C:\wamp64\www\RedRubberBall\protected\humhub\modules\content\migrations\m150928_103711_permissions.php
at line 20
– yii\db\Migration::addPrimaryKey('contentcontainer_permission_pk', 'contentcontainer_permission', ['permission_id', 'group_id', 'module_id', 'contentcontainer_id'])














14
15
16
17
18
19
20
21
22
23
24
25
26
'contentcontainer_id' => Schema::TYPE_INTEGER,
'group_id' => $this->string(50)->notNull(),
'module_id' => $this->string(50)->notNull(),
'class' => Schema::TYPE_STRING,
'state' => Schema::TYPE_BOOLEAN,
]);
$this->addPrimaryKey('contentcontainer_permission_pk', 'contentcontainer_permission', ['permission_id', 'group_id', 'module_id', 'contentcontainer_id']);
}

public function down()
{
echo "m150928_103711_permissions cannot be reverted.\n";


6. in C:\wamp64\www\RedRubberBall\protected\vendor\yiisoft\yii2\console\controllers\BaseMigrateController.php
at line 725
– m150928_103711_permissions::up()
7. in C:\wamp64\www\RedRubberBall\protected\vendor\yiisoft\yii2\console\controllers\BaseMigrateController.php
at line 199
– yii\console\controllers\BaseMigrateController::migrateUp('m150928_103711_permissions')
8.

yii\console\controllers\BaseMigrateController::actionUp(0)
9. in C:\wamp64\www\RedRubberBall\protected\vendor\yiisoft\yii2\base\InlineAction.php
at line 57
– call_user_func_array([humhub\commands\MigrateController, 'actionUp'], [0])
10. in C:\wamp64\www\RedRubberBall\protected\vendor\yiisoft\yii2\base\Controller.php
at line 157
– yii\base\InlineAction::runWithParams([])
11. in C:\wamp64\www\RedRubberBall\protected\vendor\yiisoft\yii2\console\Controller.php
at line 148
– yii\base\Controller::runAction('up', [])
12. in C:\wamp64\www\RedRubberBall\protected\humhub\commands\MigrateController.php
at line 184
– yii\console\Controller::runAction('up')














178
179
180
181
182
183
184
185
186
187
188
189
190
ob_start();
$controller = new self('migrate', Yii::$app);
$controller->db = Yii::$app->db;
$controller->interactive = false;
$controller->includeModuleMigrations = true;
$controller->color = false;
$controller->runAction('up');

return ob_get_clean();
}

/**
* Executes migrations in a specific folder

13. in C:\wamp64\www\RedRubberBall\protected\humhub\modules\installer\controllers\SetupController.php
at line 127
– humhub\commands\MigrateController::webMigrateAll()














121
122
123
124
125
126
127
128
129
130
131
132
133
Yii::$app->cache->flush();

// Disable max execution time to avoid timeouts during database installation
@ini_set('max_execution_time', 0);

// Migrate Up Database
\humhub\commands\MigrateController::webMigrateAll();

DynamicConfig::rewrite();

$this->module->setDatabaseInstalled();

return $this->redirect(['/installer/config/index']);

14.

humhub\modules\installer\controllers\SetupController::actionInit()
15. in C:\wamp64\www\RedRubberBall\protected\vendor\yiisoft\yii2\base\InlineAction.php
at line 57
– call_user_func_array([humhub\modules\installer\controllers\SetupController, 'actionInit'], [])
16. in C:\wamp64\www\RedRubberBall\protected\vendor\yiisoft\yii2\base\Controller.php
at line 157
– yii\base\InlineAction::runWithParams(['r' => 'installer/setup/init'])
17. in C:\wamp64\www\RedRubberBall\protected\vendor\yiisoft\yii2\base\Module.php
at line 528
– yii\base\Controller::runAction('init', ['r' => 'installer/setup/init'])
18. in C:\wamp64\www\RedRubberBall\protected\vendor\yiisoft\yii2\web\Application.php
at line 103
– yii\base\Module::runAction('installer/setup/init', ['r' => 'installer/setup/init'])
19. in C:\wamp64\www\RedRubberBall\protected\vendor\yiisoft\yii2\base\Application.php
at line 386
– yii\web\Application::handleRequest(humhub\components\Request)
20. in C:\wamp64\www\RedRubberBall\index.php
at line 25
– yii\base\Application::run()








19
20
21
22
23
24
25
require(__DIR__ . '/protected/humhub/config/web.php'),
(is_readable(__DIR__ . '/protected/config/dynamic.php')) ? require(__DIR__ . '/protected/config/dynamic.php') : [],
require(__DIR__ . '/protected/config/common.php'),
require(__DIR__ . '/protected/config/web.php')
);

(new humhub\components\Application($config))->run();

$_GET = [
'r' => 'installer/setup/init',
];

$_COOKIE = [
'_csrf' => 'd46f47fe0f5e7f79311615bdc4357aca3e22426d47b7bd9a5a64662f390fc255a:2:{i:0;s:5:"_csrf";i:1;s:32:"WF4WY8cyAHBG8ec5j6YVb7uYEYx7_rDa";}',
];

Options: ReplyQuote
Re: SQLSTATE[42000]
Posted by: Claudius (---.bardstown.com)
Date: August 09, 2018 08:25PM

Additional information...

I use the Zonealarm firewall and anti malware system. I configured the firewall to trust and allow the Apache server and the MySQL database to communicate through the firewall but got the sale error.

Options: ReplyQuote
Re: SQLSTATE[42000]
Posted by: Claudius (---.bardstown.com)
Date: August 10, 2018 02:39AM

How should my database be collated? I'm using "utf8mb4_inicode_ci." Is that the right collation?

Options: ReplyQuote
Re: SQLSTATE[42000]
Posted by: RiggsFolly (Moderator)
Date: August 10, 2018 10:20AM

This error

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes
The SQL being executed was: ALTER TABLE `contentcontainer_permission` ADD CONSTRAINT `contentcontainer_permission_pk` PRIMARY KEY (`permission_id`, `group_id`, `module_id`, `contentcontainer_id`)
Error Info: Array
(
[0] => 42000
[1] => 1071
[2] => Specified key was too long; max key length is 1000 bytes

I have seen before.

This is not a WAMPServer error !!!
This is a limitation of MySQL. We dont write MySQL



The problem is that the compound key contains more than 1000 characters when using the Unicode charset (up to 4 bytes per character).

One possible solution (but not necessarily a good one) is to change the collation to `utf8`.
Another would be to reduce the size of one or more of the key parts, I normally see a VARCHAR(255) in these situations, and normally this is because the ORM (Doctrine for example) defaults a string to VARCHAR(255) unless otherwise instructed to use a different more appropriate size.

Other can be found on GOOGLE using the search string `yii SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes`

But all of these solutions need to be understood BY YOU so that they fit with your application needs and requirements. If you are getting something off the shelf, contact the developer for advice

---------------------------------------------------------------------------------------------
(Windows 10 Pro 64bit) (Wampserver 3.1.3 32bit & 64bit)
<Apache 2.4.29/2.2.31> <PHP 7.2/7.1/7.0/5.6/5.5/5.4> <MySQL 5.7.19/5.6.39/5.5.28>
<MariaDB 10.2.14> <phpMyAdmin4.8.1> <MySQL Workbench 6.3.10>

Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin
Get your Apache/MySQL/mariaDB/PHP ADDONs here from the WAMPServer alternate Repo
-X-X-X- Backup your MySQL databases regularly Here is How dont regret it later! Yes even when developing -X-X-X-

Options: ReplyQuote
Re: SQLSTATE[42000]
Posted by: Claudius (---.bardstown.com)
Date: August 10, 2018 01:56PM

Yes Sir, I understand about the 1K character error, I did google up the error.

What I did was use the utf8mb4_unicode-ci collation on the advice of a post I read in the dim, dark past before yesterday when I was trying to set up HumHub on a Ubuntu LAMP server inside of a VMWare Player sandbox. I'll give utf8 a spin.

Thank you for your assistance, I appreciate the effort!

Options: ReplyQuote
Re: SQLSTATE[42000]
Posted by: Claudius (---.bardstown.com)
Date: August 10, 2018 02:04PM

The database collation was the problem. I set up a database with utf8 like you suggested and everything worked as advertised! thank you so very much for your advice!

Options: ReplyQuote


Sorry, only registered users may post in this forum.