× Welcome to the ToolsJX forum!

Error 1064 after upgrade from 1.4 to 1.5.12

djb
Topic Author
Offline
Senior Member

12 years 3 months ago #1485

Hi,

I have been running Tablejx v1.4 for quite a while on J1.5.22.

I upgraded to 1.5.12 today on my test site.

The upgrade was successful:

Insert Grid: Install Plugin Success
Display Grid: Install Plugin Success
Plugins Enabled
Install Component Success

When I opened the articles containing tables and found that the grids do not display. In place of the grid is the message:

Error: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 'FROM `vw_abw_basic` ORDER BY `surname` ,`forename` ASC LIMIT 0, 10' at line 1 SQL=SELECT FROM `vw_abw_basic` ORDER BY `surname` ,`forename` ASC LIMIT 0, 10'.

As this is a local install, I reloaded the backup, upgraded to J1.5.25 but had the same problem.

When I go into Components | Grid, all my grid definitions are present but when I open the definitions, the Columns section is blank.

If I try to re-create the columns data and save the definition, I see the message:

Error Saving Grid
store error
Unknown column 'shAtrib' in 'field list' SQL=UPDATE `jos_grids` SET `typejx`='grid',`tableName`='vw_abw_basic',`showTitle`='0',`tableCaption`='Search using article id',`connection`='joomla',`shAtrib`='1',`paging`='1',`nrPages`='5',`default_order`='surname|ASC',`nrRows`='20',`searchF`='display=1\noptions=0\nadvanced=0\nsubmitFirst=0\noperator=1\n',`nrRecSelect`='1',`nrRecords`='1',`lineNr`='0',`showtime`='0',`poweredBy`='0',`hColor`='FFFFFF',`sColor`='FFFFFF',`rColor1`='FFFFFF',`rColor2`='EEEEEE',`rColorMO`='F5F5BA',`PBColor`='F7F7F7',`lineHeight`='0',`whereCond`='vw_abw_basic.article_id = @article_id',`caseSensitive`='0',`secOrder`='forename|ASC|0' WHERE idGrid='3

Can you suggest how I can remedy this error please?

Thank you
David

Last edit: 12 years 3 months ago by djb.

Please Log in or Create an account to join the conversation.

tomaz
Offline
Administrator

12 years 3 months ago #1509

Hi,

In 1.5.9 there were some changes in database schema.

I suggest that you delete the database tables where Table JX stores its settings and make a clean installation, but in this case all the settings that you have done till now will be lost. These tables are: prefix_grids and prefix_grid_conn.

There is also an update package from 1.5.8 to 1.5.9, but it was only tested to work on upgrade from 1.5.8. You can contact us by email if you want to try it. If this works you will not lose the settings.

Please Log in or Create an account to join the conversation.

djb
Topic Author
Offline
Senior Member

12 years 3 months ago #1518

Thank you for your reply.

I have 36 grids defined so I would like to keep the data if possible.

Is it possible to download the update from 1.5 to 1.5.8 and then apply the 1.5.12 update?

Alternatively, are there any SQL scripts to make the database changes?

With thanks
David

Please Log in or Create an account to join the conversation.

tomaz
Offline
Administrator

12 years 3 months ago #1526

Hi,

There was a special update package from 1.4 to 1.5, and another one from 1.5.8 to 1.5.9. I suggest that you apply them one after another and then install 1.5.22. Contact us on This email address is being protected from spambots. You need JavaScript enabled to view it. and we will send you the mentioned packages.

I am also posting the sql scripts which are included in those packages.

1.4 to 1.5:

ALTER TABLE `ulj2z_grids` CHANGE `link` `link` TEXT , ADD `cardsPerRow` TINYINT( 1 ) NOT NULL, ADD `shAtrib` TINYINT( 1 ) NOT NULL, ADD `cardBorder` INT(1) NOT NULL;

in 1.5.8 to 1.5.9 there is also a php script using Joomla framework, since the transformation was a bit more complicated... Maybe you find it useful anyway.

CREATE TABLE IF NOT EXISTS `ulj2z_grid_columns` ( `id` INT NOT NULL AUTO_INCREMENT , `idGrid` INT( 11 ) NOT NULL, `order` INT NOT NULL , `columnName` VARCHAR( 255 ) NOT NULL , `columnLabel` VARCHAR( 255 ) NOT NULL , `linkType` INT NOT NULL , `linkColumn` VARCHAR( 255 ) NOT NULL , `customLink` VARCHAR( 255 ) NOT NULL , `columnWidth` INT NOT NULL , `columnAling` VARCHAR( 6 ) NOT NULL , `displayLabel` BOOL NOT NULL , `displayFilter` BOOL NOT NULL , `filterType` VARCHAR( 16 ) NOT NULL , PRIMARY KEY ( `id` ) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

#UPDATE DATABASE v1.5.8 TO v1.5.9
	
	$query="SELECT * FROM ulj2z_grids";
	$db->setQuery($query);
	$grids = $db->loadObjectList();
	
	foreach ($grids as $grid){
		$columnNames=explode('|', $grid->columnNames);
		$columnCaptions=explode('|', $grid->columnCaptions);
		$links=explode('|', $grid->link);
		$widths=explode('|', $grid->columnWidth);
		
		foreach ($columnNames as $key=>$name){
			
			$linkType=$links[$key*2];
			$linkCol=$links[$key*2+1];
			$customLink="";
			if (!is_numeric($linkType)){
				$customLink=$linkType;
				$linkType=3;
			}
			
			$query="INSERT INTO ulj2z_grid_columns 
					(idGrid, `order`, columnName, columnLabel, linkType, linkColumn, customLink, columnWidth)
			VALUES('$grid->idGrid', '$key', '$name', '$columnCaptions[$key]', '$linkType', '$linkCol', '$customLink', '$widths[$key]')
			";
			
			$db->setQuery($query);
			echo $db->getQuery().'</br>';
			if (!$db->query())
				die($db->getErrorMsg());
		}
		
	}

Regards,
Tomaž

Last edit: 12 years 3 months ago by tomaz.

Please Log in or Create an account to join the conversation.

djb
Topic Author
Offline
Senior Member

12 years 3 months ago #1531

Tomaž,

Thank you for sending the file last night. That was very kind.

The two installs were successful and I am now running on 1.5.9.

I have not upgraded to the latest version as I have a small problem.

Most of my grids work fine but there is one that does not. It gives the error:

Error:1054-Unknown column '|' in 'field list' SQL=SELECT `surname`, `forename`, `reg_no`, `rank`, `notes`, `name`, `|` FROM `vw_abw_basic` ORDER BY `surname` ASC,`forename` ASC LIMIT 0, 10

There is no column '|' in my view.

The grid with the error includes a link to article ids. If I include the article id field in the list of fields in the grid (which I don't want to when live) the view works. When I do not include the article_id field in the list of fields, I see the above error.

This problem only affects one grid but it is the one on my home page.

I have tried opening and saving the grid but that did not make any difference.

The only other anomaly I noted as part of the upgrades is that table header labels were turned off. I have turned then on again in the grids.

If you have a moment, can you suggest how I can best remedy this please?

Many thanks
David

Please Log in or Create an account to join the conversation.

tomaz
Offline
Administrator

12 years 3 months ago #1536

Hi,

Try running the following command.

DELETE FROM prefix_grid_columns WHERE columnName = '|'

Before running, change the table prefix according to your settings.

Please Log in or Create an account to join the conversation.

Moderators: tomaz

Time to create page: 0.158 seconds

Error 1064 after upgrade from 1.4 to 1.5.12

Contact Us

Contact us on Discord

Contact us on Facebook

Forum

Error 1064 after upgrade from 1.4 to 1.5.12

Contact Us

Contact us on Discord

Contact us on Facebook