Unknown column 'state' in 'field list'

[localheinz]

Following https://github.com/zendframework//pull/250 I tried to sign in into the app, which resulted in the following exception being displayed:

Exception

Zend\Db\Adapter\Exception\InvalidQueryException

File

vendor/zendframework/zendframework/library/Zend/Db/Adapter/Driver/Pdo/Statement.php:244

Message

Statement could not be executed (42S22 - 1054 - Unknown column 'state' in 'field list')

Stack Trace

#0 vendor/zf-commons/zfc-base/src/ZfcBase/Mapper/AbstractDbMapper.php(141): Zend\Db\Adapter\Driver\Pdo\Statement->execute()
#1 vendor/zf-commons/zfc-user/src/ZfcUser/Mapper/User.php(55): ZfcBase\Mapper\AbstractDbMapper->insert(Object(User\Entity\User), NULL, NULL)
#2 vendor/socalnick/scn-social-auth/src/ScnSocialAuth/Authentication/Adapter/HybridAuth.php(534): ZfcUser\Mapper\User->insert(Object(User\Entity\User))
#3 vendor/socalnick/scn-social-auth/src/ScnSocialAuth/Authentication/Adapter/HybridAuth.php(450): ScnSocialAuth\Authentication\Adapter\HybridAuth->insert(Object(User\Entity\User), 'github', Object(Hybrid_User_Profile))
#4 vendor/socalnick/scn-social-auth/src/ScnSocialAuth/Authentication/Adapter/HybridAuth.php(110): ScnSocialAuth\Authentication\Adapter\HybridAuth->githubToLocalUser(Object(Hybrid_User_Profile))
#5 [internal function]: ScnSocialAuth\Authentication\Adapter\HybridAuth->authenticate(Object(ZfcUser\Authentication\Adapter\AdapterChainEvent))
#6 vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(468): call_user_func(Array, Object(ZfcUser\Authentication\Adapter\AdapterChainEvent))
#7 vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(207): Zend\EventManager\EventManager->triggerListeners('authenticate', Object(ZfcUser\Authentication\Adapter\AdapterChainEvent), Object(Closure))
#8 vendor/zf-commons/zfc-user/src/ZfcUser/Authentication/Adapter/AdapterChain.php(56): Zend\EventManager\EventManager->trigger('authenticate', Object(ZfcUser\Authentication\Adapter\AdapterChainEvent), Object(Closure))
#9 vendor/zf-commons/zfc-user/src/ZfcUser/Controller/UserController.php(139): ZfcUser\Authentication\Adapter\AdapterChain->prepareForAuthentication(Object(ZF\ContentNegotiation\Request))
#10 vendor/zendframework/zendframework/library/Zend/Mvc/Controller/AbstractActionController.php(83): ZfcUser\Controller\UserController->authenticateAction()
#11 [internal function]: Zend\Mvc\Controller\AbstractActionController->onDispatch(Object(Zend\Mvc\MvcEvent))
#12 vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(468): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
#13 vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(207): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
#14 vendor/zendframework/zendframework/library/Zend/Mvc/Controller/AbstractController.php(117): Zend\EventManager\EventManager->trigger('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
#15 vendor/zendframework/zendframework/library/Zend/Mvc/Controller/Plugin/Forward.php(142): Zend\Mvc\Controller\AbstractController->dispatch(Object(ZF\ContentNegotiation\Request), Object(Zend\Http\PhpEnvironment\Response))
#16 vendor/socalnick/scn-social-auth/src/ScnSocialAuth/Controller/UserController.php(144): Zend\Mvc\Controller\Plugin\Forward->dispatch('zfcuser', Array)
#17 vendor/zendframework/zendframework/library/Zend/Mvc/Controller/AbstractActionController.php(83): ScnSocialAuth\Controller\UserController->providerAuthenticateAction()
#18 [internal function]: Zend\Mvc\Controller\AbstractActionController->onDispatch(Object(Zend\Mvc\MvcEvent))
#19 vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(468): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
#20 vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(207): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
#21 vendor/zendframework/zendframework/library/Zend/Mvc/Controller/AbstractController.php(117): Zend\EventManager\EventManager->trigger('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
#22 vendor/zendframework/zendframework/library/Zend/Mvc/DispatchListener.php(114): Zend\Mvc\Controller\AbstractController->dispatch(Object(ZF\ContentNegotiation\Request), Object(Zend\Http\PhpEnvironment\Response))
#23 [internal function]: Zend\Mvc\DispatchListener->onDispatch(Object(Zend\Mvc\MvcEvent))
#24 vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(468): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
#25 vendor/zendframework/zendframework/library/Zend/EventManager/EventManager.php(207): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
#26 vendor/zendframework/zendframework/library/Zend/Mvc/Application.php(313): Zend\EventManager\EventManager->trigger('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
#27 //public/index.php(21): Zend\Mvc\Application->run()
#28 {main}

Given that I'm working with a database dump generated by @GeeH yesterday, I understand this means that currently nobody can sign in who hasn't signed in before.

I added a column state locally (just used VARCHAR(255)), then signing in worked.

Checking with the database again:

{
    "data":
    [
        {
            "user_id": 99999,
            "username": null,
            "email": "email@example.com",
            "display_name":"Andreas Möller",
            "password": "githubToLocalUser",
            "photo_url": null,
            "created_at": "2014-12-06 19:16:03",
            "state": null
        }
    ]
}

So apparently:

• username was not fetched properly
• photo_url wasn't fetched
• email was fetched, but not my primary email address, unfortunately
• state wasn't updated to whatever it should be, unless null is what it should be

[localheinz]

I can confirm that signing in at http://modules.zendframework.com doesn't work either.

[localheinz]

@GeeH

❗ Someone with database access should at least execute this for now:

ALTER TABLE `user` ADD `state` INT(2);

In the long run we should probably use database migrations, no?

[gianarb]

mm for my login now works..
I believe after this pull #239

[localheinz]

@gianarb

But you have been signed in before, haven't you?

I've never before signed in into the app, just tried to do so today.

[ins0]

yesterday i setup a clean first fork of this repo and the registration process worked for me with the clean database dump

[localheinz]

@ins0

Seems the the SQL was updated with 7870422, but apparently it was never executed on the server.

[localheinz]

index 177e85c..6efd7dd 100644
--- a/2014-12-06 zfmodules-what-it-is.sql       
+++ b/2014-12-06 zfmodules-what-it-should-be.sql   
@@ -7,7 +7,7 @@
 #
 # Host: 127.0.0.1 (MySQL 5.6.21)
 # Database: zfmodules
-# Generation Time: 2014-12-06 20:00:48 +0000
+# Generation Time: 2014-12-06 20:00:04 +0000
 # ************************************************************


@@ -24,13 +24,13 @@
 # ------------------------------------------------------------

 CREATE TABLE `module` (
-  `module_id` int(11) NOT NULL AUTO_INCREMENT,
+  `module_id` int(11) NOT NULL,
   `name` varchar(255) NOT NULL,
   `description` text NOT NULL,
   `url` varchar(500) NOT NULL,
   `meta_data` blob,
-  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
+  `updated_at` timestamp NULL DEFAULT NULL,
   `photo_url` varchar(255) DEFAULT NULL,
   `owner` varchar(255) NOT NULL,
   PRIMARY KEY (`module_id`)
@@ -56,13 +56,14 @@ CREATE TABLE `module_admin` (
 # ------------------------------------------------------------

 CREATE TABLE `user` (
-  `user_id` int(11) NOT NULL AUTO_INCREMENT,
+  `user_id` int(11) NOT NULL,
   `username` varchar(255) DEFAULT NULL,
   `email` varchar(255) DEFAULT NULL,
   `display_name` varchar(50) DEFAULT NULL,
   `password` varchar(128) NOT NULL,
   `photo_url` varchar(255) DEFAULT NULL,
-  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
+  `state` int(2) DEFAULT NULL,
   PRIMARY KEY (`user_id`),
   UNIQUE KEY `username` (`username`),
   UNIQUE KEY `email` (`email`)

[gianarb]

@localheinz yes I m already registered
I'm trying in local login into app and works.. I have same problem of sync modules caused my Github Api limit..

[Lansoweb]

The problem still persists. Today i tried to login:

Zend\Db\Adapter\Exception\InvalidQueryException

File:
/var/www/vendor/zendframework/zendframework/library/Zend/Db/Adapter/Driver/Pdo/Statement.php:244
Message:
Statement could not be executed (42S22 - 1054 - Unknown column 'state' in 'field list')

[snapshotpl]

Same here

[sandrokeil]

I have the same issue and I'm already registered.

[snapshotpl]

Hey guys @localheinz @GeeH @Ocramius ! You are doing great job in last days but this issue it's really annoying. How can I help you with this one?

[localheinz]

@snapshotpl

Sorry, I wish I could fix this issue, but I don't have access to the server! All that is needed is that the state column is added. I guess we all have to bug @GeeH about this!

[ins0]

It looks like the complete deployment doesn't work or need triggered manually, error pages still throwing hard exceptions after the fix was merged today.

[localheinz]

@ins0

I so wish the deployment was triggered by merges to master - I heard it's a thing, LOL.

[Ocramius]

I probably need @GeeH to look at this when he has time

[Lansoweb]

Since yesterday, the error changed and the same appears when i click in any project, not just Login:

This view is generated by the Error Module
An error occurred

An error occurred during execution; please try again later.

[GeeH]

Apologies, I'm busy as shot at the moment
On 23 Dec 2014 22:57, "Leandro Silva" notifications@github.com wrote:

Since yesterday, the error changed and the same appears when i click in
any project, not just Login:

This view is generated by the Error Module
An error occurred

An error occurred during execution; please try again later.

—
Reply to this email directly or view it on GitHub
#251 (comment)
.

[Lansoweb]

I understand @GeeH. But could at least give database access (and server access if necessary) to someone you trust? At least to fix this issue.

If necessary i can host a temporary clone in one of my servers, but without the database, the users will have to create their projects again.

[gerardZf2Comments]

The comiit 7870422 changes the module_id column of module table from being auto incrementing. Why was it done? Does it cause an error when adding a new module?

[gianarb]

I don't remember but maybe this bug is a fix ( 💥 ) of this #206..
@ins0 do you remember? :D

[ins0]

don't know but some of the changes improve the sql but some changes like the auto_increment looks like a misstake to me - can't be a fix for #206 as it is a 4 months old commit. @Danielss89 may have some ìnfo about these changes back in the days?

but to be honest - the complete db design can be updated - I am not really happy with the current structure but there are so many important improvements right now.

this is only a sql copy so it isn't much important to be updated - but should be fixed indeed.

[localheinz]

Closing as I just added the column to the table in the production system a few minutes ago.