• From: UK
  • Registered: 10/02/2012
  • Posts: 146

Topic: Query page size bug

Hi
I am getting this error when I am trying update Query page size above 127.

DATABASE ERROR :
At file C:\Frontaccounting\admin\db\users_db.inc:65:
could not update user display prefs for 2
error code : 1264
error message : Out of range value for column 'query_size' at row 1
sql that failed was : UPDATE 0_users SET prices_dec='2',qty_dec='2',rates_dec='4',percent_dec='2',date_format='1',date_sep='0',tho_sep='0',dec_sep='0',print_profile='',theme='cool',page_size='A4',language='C',startup_tab='orders',show_gl='1',show_codes='1',show_hints='1',rep_popup='1',graphic_links='1',sticky_doc_date='0',query_size='200' WHERE id='2'

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Query page size bug

Page Size should be either A4 or Letter

apmuthu's Website

  • From: UK
  • Registered: 10/02/2012
  • Posts: 146

Re: Query page size bug

I think you have misunderstood my error. It is the field in preferences which takes number of rows value to displayed for on-screen transaction report ie custom transaction inquiry.

  • From: Indonesia, Jakarta
  • Registered: 05/08/2013
  • Posts: 28

Re: Query page size bug

dvarsani, i ran your sql query in my test database and it ran without problem. may be something wrong with your table structure?

  • tm
  • Senior Member
  • Offline
  • From: Sweden
  • Registered: 05/13/2013
  • Posts: 142

Re: Query page size bug

On my system (2.3.15) the query_size column is defined as a TINYINT, which is a signed 8 bit integer. So, 128 would indeed be outside its range.

Since mfaa98 didn't have this problem, I guess it's been fixed in a later version. Easiest solution would be upgrading. (I'm just waiting for the new year, before I do that.)

If that's not an option, I suggest you google "mysql alter table".

6 Reply by apmuthu (edited by apmuthu 12/30/2013 05:10:36 am)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Query page size bug

The current version on the Official GitHub is still TINYINT for query_size and is VARCHAR(20) for page_size.

CREATE TABLE IF NOT EXISTS `0_users` (
  `id` smallint(6) NOT NULL auto_increment,
  `user_id` varchar(60) NOT NULL default '',
  `password` varchar(100) NOT NULL default '',
  `real_name` varchar(100) NOT NULL default '',
  `role_id` int(11) NOT NULL default '1',
  `phone` varchar(30) NOT NULL default '',
  `email` varchar(100) default NULL,
  `language` varchar(20) default NULL,
  `date_format` tinyint(1) NOT NULL default '0',
  `date_sep` tinyint(1) NOT NULL default '0',
  `tho_sep` tinyint(1) NOT NULL default '0',
  `dec_sep` tinyint(1) NOT NULL default '0',
  `theme` varchar(20) NOT NULL default 'default',
  `page_size` varchar(20) NOT NULL default 'A4',
  `prices_dec` smallint(6) NOT NULL default '2',
  `qty_dec` smallint(6) NOT NULL default '2',
  `rates_dec` smallint(6) NOT NULL default '4',
  `percent_dec` smallint(6) NOT NULL default '1',
  `show_gl` tinyint(1) NOT NULL default '1',
  `show_codes` tinyint(1) NOT NULL default '0',
  `show_hints` tinyint(1) NOT NULL default '0',
  `last_visit_date` datetime default NULL,
  `query_size` tinyint(1) default '10',
  `graphic_links` tinyint(1) default '1',
  `pos` smallint(6) default '1',
  `print_profile` varchar(30) NOT NULL default '1',
  `rep_popup` tinyint(1) default '1',
  `sticky_doc_date` tinyint(1) default '0',
  `startup_tab` varchar(20) NOT NULL default '',
  `inactive` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=MyISAM  AUTO_INCREMENT=2 ;

The column query_size has not changed since FA v2.1 as seen from:

File: sql/alter2.1.sql
Line 74: ALTER TABLE `0_users` ADD `query_size` TINYINT(1) DEFAULT '10';

apmuthu's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Query page size bug

Wiki-ed it with screenshot.

apmuthu's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Query page size bug

Thanks apmuthu.
We nevewr thought that the Query size should be that big. with 127 items on page you don't see all lines. In my opinion, suitable values would be 10-30 or 40 rows.

/Joe

  • tm
  • Senior Member
  • Offline
  • From: Sweden
  • Registered: 05/13/2013
  • Posts: 142

Re: Query page size bug

Well... Just make it an unsigned TINYINT, then. That really should be enough for anybody. (I would have thought 7 bits would be enough, but...)

There is no reason to allow negative values for any "size", and it shouldn't affect data already stored in the database.

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Query page size bug

Will do that for next major, 2.4

For those who need this now, please run an ALTER command on the user table.

Joe

11 Reply by apmuthu (edited by apmuthu 12/30/2013 05:55:23 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Query page size bug

ALTER TABLE `0_users` CHANGE `query_size` `query_size` TINYINT(1) UNSIGNED NOT NULL DEFAULT 10;

Do that for each of the companies (replacing the table prefix "0_" with the appropriate one for each company) and for all Charts of Accounts in the sql folder.

apmuthu's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Query page size bug

Updated in my personal GitHub Repo.

For those not intending to wait for FA v2.4, grab the code from here.

apmuthu's Website

  • From: UK
  • Registered: 10/02/2012
  • Posts: 146

Re: Query page size bug

Hi
I sorted this issue with updating my column query_size to SMALLINT.
My users prefer viewing 200 lines per page (less pages to view) rather than clicking "Next" for 20/25 times. These days everybody uses mouse wheel to scroll up/down. I think we should change this in FA's new version.
Thanks