We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Vökuró: PDOException: SQLSTATE[22003]: Numeric value out of range

I installed Vökuró and its database. When I try to login with invalid credentials I get the following error:

[FATAL] Unhandled PDOException: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value adjusted for column 'attempted' at row 1
in F:\Projekte\phalcon\sample-apps\vokuro\app\library\Auth\Auth.php on line 87

 PDOStatement->execute()                              # line 87, file: ...\vokuro\app\library\Auth\Auth.php
 phalcon\db\adapter\Pdo->executePrepared()                       [php-phalcon]
 phalcon\db\adapter\Pdo->execute()                               [php-phalcon]
 phalcon\db\Adapter->insert()                                    [php-phalcon]
 phalcon\mvc\Model->_doLowInsert()                               [php-phalcon]
 phalcon\mvc\Model->save()                                       [php-phalcon]
 vokuro\auth\Auth->registerUserThrottling()           # line 87, file: ...\vokuro\app\library\Auth\Auth.php
 vokuro\auth\Auth->check()                            # line 29, file: ...\vokuro\app\library\Auth\Auth.php
 vokuro\controllers\SessionController->loginAction()  # line 90, file: ...\vokuro\app\controllers\SessionController.php
 phalcon\Dispatcher->dispatch()                                  [php-phalcon]
 phalcon\mvc\Application->handle()                               [php-phalcon]
 {main}                                               # line 36, file: ...\vokuro\public\index.php

Question 1: What am I doing wrong here?

I believe Phalcon trys to save a failed login attempt. The provided DDL looks as follows:

CREATE TABLE IF NOT EXISTS `failed_logins` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `usersId` int(10) unsigned DEFAULT NULL,
  `ipAddress` char(15) NOT NULL,
  `attempted` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `usersId` (`usersId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

However I can't figure out what Phalcon actually tries to execute, therefore

Question 2: Can I configure the PDO adapter to show the failed SQL statement if an error occurres? If so, how?

Thank's a lot.

In between I can answer question 2 by myself. It was obvious :-) Phalcon tries to insert a timestamp into a smallint coloumn. Is this a bug in the provided schema of Vökuró?

I guess you have hit limit for int data type with wrong password? The integer field is a field type INT (integer) and feature in MySQL is that it can store up to 10 digits (4 bytes = 4x8 = 32 bits ==> 4,294,967,296 values); who are in the range - 2.147 .483.648 and 2.147.483.647

so, if the number is less than < -2.147.483.648 OR GREATER THAN> 2.147.483.647; an overflow occurs and the error appears.

try to change data type to BIGINT data type in db, that can store up to 20 digits (8 bytes = 8x8 = 64 bits ==> 18,446,744,073,709,551,616 values) which are in the range from - 9,223,372,036,854,775,808 And 9,223,372,036,854,775,807.

edited Jun '16

Thank you for the response. As you can see from the DDL the column is defined as

attempted smallint(5) unsigned NOT NULL,

which is to small for a regular int. I fixed the bug by correcting it to a regular one and was surprised to realize that the app is online since 3 years and nobody ever tested it or clicked the login button.

attempted int(10) unsigned NOT NULL,