Beginner: Cant make a simple Update work
Hi!
Update wont work with me.
I am getting as close as to being able to update an entry when no rows are updated:
UPDATE ct_content SET title="Rekordinvesteringar i ren teknologi" WHERE id=48124;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
Rows matched: 1 Changed: 0 Warnings: 0
But trying to change that field into a different value from what it actually has, gives an error:
mysql> UPDATE ct_content SET title="xxx" where id=48124;
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1064: You have an error in your SQL syntax; check the manual that correspond' from FEDERATED
The warnings mentioned above are
| Warning | 1366 | Incorrect string value: '\xF6r sol...' for column 'introtext' at row 1 |
| Warning | 1366 | Incorrect string value: '\xF6r sju...' for column 'fulltext' at row 1 |
.. this makes me suspect this is a character set problem. Both he original and the federated tables use utf8 haracter sets.
I defined the federated one from a "show create table", adding the ENGINE and CONNECTION args. This is the federated definition:
CREATE TABLE etnjoomla.ct_content (
`id` int(11) unsigned NOT NULL auto_increment,
`title` text NOT NULL,
`alias` varchar(255) NOT NULL default '',
`title_alias` text NOT NULL,
`introtext` mediumtext NOT NULL,
`fulltext` mediumtext NOT NULL,
`state` tinyint(3) NOT NULL default '0',
`sectionid` int(11) unsigned NOT NULL default '0',
`mask` int(11) unsigned NOT NULL default '0',
`catid` int(11) unsigned NOT NULL default '0',
`created` datetime NOT NULL default '0000-00-00 00:00:00',
`created_by` int(11) unsigned NOT NULL default '0',
`created_by_alias` text NOT NULL,
`modified` datetime NOT NULL default '0000-00-00 00:00:00',
`modified_by` int(11) unsigned NOT NULL default '0',
`checked_out` int(11) unsigned NOT NULL default '0',
`checked_out_time` datetime NOT NULL default '0000-00-00 00:00:00',
`publish_up` datetime NOT NULL default '0000-00-00 00:00:00',
`publish_down` datetime NOT NULL default '0000-00-00 00:00:00',
`images` text NOT NULL,
`urls` text NOT NULL,
`attribs` text NOT NULL,
`version` int(11) unsigned NOT NULL default '1',
`parentid` int(11) unsigned NOT NULL default '0',
`ordering` int(11) NOT NULL default '0',
`metakey` text NOT NULL,
`metadesc` text NOT NULL,
`access` int(11) unsigned NOT NULL default '0',
`hits` int(11) unsigned NOT NULL default '0',
`metadata` text NOT NULL,
`accumulated_hits` int(11) unsigned default NULL,
`garbage_hits` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `idx_section` (`sectionid`),
KEY `idx_access` (`access`),
KEY `idx_checkout` (`checked_out`),
KEY `idx_state` (`state`),
KEY `idx_catid` (`catid`),
KEY `idx_mask` (`mask`)
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8
AUTO_INCREMENT=48934
CONNECTION='mysql://etn@ctsweden.se:3306/cleantech/jos_content';