From: Honza M. <hon...@ec...> - 2013-03-01 08:15:26
|
Hola Misi, > I am after upgrade of MySQL to 5.0.96. Great. We do not use PEAR - you do not need it. The script (your and mine) is part of AA and is used just to run some SQL commands. You can extract the SQL commands from it and just run them in MySQL. The extracted SQL commands are below. After runing this commands, the old version of AA should be working well on new MySQL >5. Then you can upgrade AA to the latest version and run https://example.org/apc-aa/service/sql_update.php script. > Is it possible to upgrade directly from 2.8.1 to 2.50 ? I never tested it, but the sql_update.php script should do everything necessary, so I'm quite sure it is possible. We are trying to be backward compatible as much as possible. As allways, the backup of AA and DB is essential. Honza -------------------------------------------------------------------------------------------------------- SQL commnds to fix table fields after upgrade from MySQL 4 to 5 (if some command is not working, don't worry - you have older AA and some tables could not be present in the database. Just skip it) -------------------------------------------------------------------------------------------------------- ALTER TABLE `active_sessions` CHANGE `sid` `sid` varbinary(32) NOT NULL default '' UPDATE `active_sessions` SET sid=TRIM(TRAILING '' FROM sid) ALTER TABLE `change` CHANGE `id` `id` varbinary(32) NOT NULL default '' UPDATE `change` SET id=TRIM(TRAILING '' FROM id) ALTER TABLE `change` CHANGE `resource_id` `resource_id` varbinary(32) NOT NULL default '' UPDATE `change` SET resource_id=TRIM(TRAILING '' FROM resource_id) ALTER TABLE `change_record` CHANGE `change_id` `change_id` varbinary(32) NOT NULL default '' UPDATE `change_record` SET change_id=TRIM(TRAILING '' FROM change_id) ALTER TABLE `change_record` CHANGE `selector` `selector` varbinary(255) default NULL UPDATE `change_record` SET selector=TRIM(TRAILING '' FROM selector) ALTER TABLE `central_conf` CHANGE `dns_conf` `dns_conf` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET dns_conf=TRIM(TRAILING '' FROM dns_conf) ALTER TABLE `central_conf` CHANGE `dns_web` `dns_web` varbinary(15) NOT NULL default '' UPDATE `central_conf` SET dns_web=TRIM(TRAILING '' FROM dns_web) ALTER TABLE `central_conf` CHANGE `dns_mx` `dns_mx` varbinary(15) NOT NULL default '' UPDATE `central_conf` SET dns_mx=TRIM(TRAILING '' FROM dns_mx) ALTER TABLE `central_conf` CHANGE `dns_db` `dns_db` varbinary(15) NOT NULL default '' UPDATE `central_conf` SET dns_db=TRIM(TRAILING '' FROM dns_db) ALTER TABLE `central_conf` CHANGE `dns_prim` `dns_prim` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET dns_prim=TRIM(TRAILING '' FROM dns_prim) ALTER TABLE `central_conf` CHANGE `dns_sec` `dns_sec` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET dns_sec=TRIM(TRAILING '' FROM dns_sec) ALTER TABLE `central_conf` CHANGE `web_conf` `web_conf` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET web_conf=TRIM(TRAILING '' FROM web_conf) ALTER TABLE `central_conf` CHANGE `web_path` `web_path` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET web_path=TRIM(TRAILING '' FROM web_path) ALTER TABLE `central_conf` CHANGE `db_server` `db_server` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET db_server=TRIM(TRAILING '' FROM db_server) ALTER TABLE `central_conf` CHANGE `db_name` `db_name` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET db_name=TRIM(TRAILING '' FROM db_name) ALTER TABLE `central_conf` CHANGE `db_user` `db_user` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET db_user=TRIM(TRAILING '' FROM db_user) ALTER TABLE `central_conf` CHANGE `db_pwd` `db_pwd` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET db_pwd=TRIM(TRAILING '' FROM db_pwd) ALTER TABLE `central_conf` CHANGE `AA_SITE_PATH` `AA_SITE_PATH` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET AA_SITE_PATH=TRIM(TRAILING '' FROM AA_SITE_PATH) ALTER TABLE `central_conf` CHANGE `AA_BASE_DIR` `AA_BASE_DIR` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET AA_BASE_DIR=TRIM(TRAILING '' FROM AA_BASE_DIR) ALTER TABLE `central_conf` CHANGE `AA_HTTP_DOMAIN` `AA_HTTP_DOMAIN` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET AA_HTTP_DOMAIN=TRIM(TRAILING '' FROM AA_HTTP_DOMAIN) ALTER TABLE `central_conf` CHANGE `AA_ID` `AA_ID` varbinary(32) NOT NULL default '' UPDATE `central_conf` SET AA_ID=TRIM(TRAILING '' FROM AA_ID) ALTER TABLE `central_conf` CHANGE `ORG_NAME` `ORG_NAME` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET ORG_NAME=TRIM(TRAILING '' FROM ORG_NAME) ALTER TABLE `central_conf` CHANGE `ERROR_REPORTING_EMAIL` `ERROR_REPORTING_EMAIL` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET ERROR_REPORTING_EMAIL=TRIM(TRAILING '' FROM ERROR_REPORTING_EMAIL) ALTER TABLE `central_conf` CHANGE `ALERTS_EMAIL` `ALERTS_EMAIL` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET ALERTS_EMAIL=TRIM(TRAILING '' FROM ALERTS_EMAIL) ALTER TABLE `central_conf` CHANGE `IMG_UPLOAD_URL` `IMG_UPLOAD_URL` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET IMG_UPLOAD_URL=TRIM(TRAILING '' FROM IMG_UPLOAD_URL) ALTER TABLE `central_conf` CHANGE `IMG_UPLOAD_PATH` `IMG_UPLOAD_PATH` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET IMG_UPLOAD_PATH=TRIM(TRAILING '' FROM IMG_UPLOAD_PATH) ALTER TABLE `central_conf` CHANGE `FILEMAN_BASE_DIR` `FILEMAN_BASE_DIR` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET FILEMAN_BASE_DIR=TRIM(TRAILING '' FROM FILEMAN_BASE_DIR) ALTER TABLE `central_conf` CHANGE `FILEMAN_BASE_URL` `FILEMAN_BASE_URL` varbinary(255) NOT NULL default '' UPDATE `central_conf` SET FILEMAN_BASE_URL=TRIM(TRAILING '' FROM FILEMAN_BASE_URL) ALTER TABLE `central_conf` CHANGE `AA_ADMIN_USER` `AA_ADMIN_USER` varbinary(30) NOT NULL default '' UPDATE `central_conf` SET AA_ADMIN_USER=TRIM(TRAILING '' FROM AA_ADMIN_USER) ALTER TABLE `central_conf` CHANGE `AA_ADMIN_PWD` `AA_ADMIN_PWD` varbinary(30) NOT NULL default '' UPDATE `central_conf` SET AA_ADMIN_PWD=TRIM(TRAILING '' FROM AA_ADMIN_PWD) ALTER TABLE `content` CHANGE `item_id` `item_id` varbinary(16) NOT NULL default '' UPDATE `content` SET item_id=TRIM(TRAILING '' FROM item_id) ALTER TABLE `content` CHANGE `field_id` `field_id` varbinary(16) NOT NULL default '' UPDATE `content` SET field_id=TRIM(TRAILING '' FROM field_id) ALTER TABLE `discussion` CHANGE `id` `id` varbinary(16) NOT NULL default '' UPDATE `discussion` SET id=TRIM(TRAILING '' FROM id) ALTER TABLE `discussion` CHANGE `parent` `parent` varbinary(16) NOT NULL default '' UPDATE `discussion` SET parent=TRIM(TRAILING '' FROM parent) ALTER TABLE `discussion` CHANGE `item_id` `item_id` varbinary(16) NOT NULL default '' UPDATE `discussion` SET item_id=TRIM(TRAILING '' FROM item_id) ALTER TABLE `ef_categories` CHANGE `category_id` `category_id` varbinary(16) NOT NULL default '' UPDATE `ef_categories` SET category_id=TRIM(TRAILING '' FROM category_id) ALTER TABLE `ef_categories` CHANGE `target_category_id` `target_category_id` varbinary(16) NOT NULL default '' UPDATE `ef_categories` SET target_category_id=TRIM(TRAILING '' FROM target_category_id) ALTER TABLE `ef_permissions` CHANGE `slice_id` `slice_id` varbinary(16) NOT NULL default '' UPDATE `ef_permissions` SET slice_id=TRIM(TRAILING '' FROM slice_id) ALTER TABLE `email` CHANGE `owner_module_id` `owner_module_id` varbinary(16) NOT NULL default '' UPDATE `email` SET owner_module_id=TRIM(TRAILING '' FROM owner_module_id) ALTER TABLE `external_feeds` CHANGE `slice_id` `slice_id` varbinary(16) NOT NULL default '' UPDATE `external_feeds` SET slice_id=TRIM(TRAILING '' FROM slice_id) ALTER TABLE `external_feeds` CHANGE `remote_slice_id` `remote_slice_id` varbinary(16) NOT NULL default '' UPDATE `external_feeds` SET remote_slice_id=TRIM(TRAILING '' FROM remote_slice_id) ALTER TABLE `event` CHANGE `id` `id` varbinary(32) NOT NULL default '' UPDATE `event` SET id=TRIM(TRAILING '' FROM id) ALTER TABLE `feedmap` CHANGE `from_slice_id` `from_slice_id` varbinary(16) NOT NULL default '' UPDATE `feedmap` SET from_slice_id=TRIM(TRAILING '' FROM from_slice_id) ALTER TABLE `feedmap` CHANGE `from_field_id` `from_field_id` varbinary(16) NOT NULL default '' UPDATE `feedmap` SET from_field_id=TRIM(TRAILING '' FROM from_field_id) ALTER TABLE `feedmap` CHANGE `to_slice_id` `to_slice_id` varbinary(16) NOT NULL default '' UPDATE `feedmap` SET to_slice_id=TRIM(TRAILING '' FROM to_slice_id) ALTER TABLE `feedmap` CHANGE `to_field_id` `to_field_id` varbinary(16) NOT NULL default '' UPDATE `feedmap` SET to_field_id=TRIM(TRAILING '' FROM to_field_id) ALTER TABLE `feedperms` CHANGE `from_id` `from_id` varbinary(16) NOT NULL default '' UPDATE `feedperms` SET from_id=TRIM(TRAILING '' FROM from_id) ALTER TABLE `feedperms` CHANGE `to_id` `to_id` varbinary(16) NOT NULL default '' UPDATE `feedperms` SET to_id=TRIM(TRAILING '' FROM to_id) ALTER TABLE `feeds` CHANGE `from_id` `from_id` varbinary(16) NOT NULL default '' UPDATE `feeds` SET from_id=TRIM(TRAILING '' FROM from_id) ALTER TABLE `feeds` CHANGE `to_id` `to_id` varbinary(16) NOT NULL default '' UPDATE `feeds` SET to_id=TRIM(TRAILING '' FROM to_id) ALTER TABLE `feeds` CHANGE `category_id` `category_id` varbinary(16) NOT NULL default '' UPDATE `feeds` SET category_id=TRIM(TRAILING '' FROM category_id) ALTER TABLE `feeds` CHANGE `to_category_id` `to_category_id` varbinary(16) NOT NULL default '' UPDATE `feeds` SET to_category_id=TRIM(TRAILING '' FROM to_category_id) ALTER TABLE `field` CHANGE `id` `id` varbinary(16) NOT NULL default '' UPDATE `field` SET id=TRIM(TRAILING '' FROM id) ALTER TABLE `field` CHANGE `slice_id` `slice_id` varbinary(16) NOT NULL default '' UPDATE `field` SET slice_id=TRIM(TRAILING '' FROM slice_id) ALTER TABLE `field` CHANGE `content_id` `content_id` varbinary(16) default NULL UPDATE `field` SET content_id=TRIM(TRAILING '' FROM content_id) ALTER TABLE `jump` CHANGE `slice_id` `slice_id` varbinary(16) NOT NULL default '' UPDATE `jump` SET slice_id=TRIM(TRAILING '' FROM slice_id) ALTER TABLE `jump` CHANGE `dest_slice_id` `dest_slice_id` varbinary(16) NOT NULL default '' UPDATE `jump` SET dest_slice_id=TRIM(TRAILING '' FROM dest_slice_id) ALTER TABLE `object_float` CHANGE `object_id` `object_id` varbinary(16) NOT NULL default '' UPDATE `object_float` SET object_id=TRIM(TRAILING '' FROM object_id) ALTER TABLE `object_float` CHANGE `property` `property` varbinary(32) NOT NULL default '' UPDATE `object_float` SET property=TRIM(TRAILING '' FROM property) ALTER TABLE `object_integer` CHANGE `object_id` `object_id` varbinary(16) NOT NULL default '' UPDATE `object_integer` SET object_id=TRIM(TRAILING '' FROM object_id) ALTER TABLE `object_integer` CHANGE `property` `property` varbinary(32) NOT NULL default '' UPDATE `object_integer` SET property=TRIM(TRAILING '' FROM property) ALTER TABLE `object_text` CHANGE `object_id` `object_id` varbinary(16) NOT NULL default '' UPDATE `object_text` SET object_id=TRIM(TRAILING '' FROM object_id) ALTER TABLE `object_text` CHANGE `property` `property` varbinary(32) NOT NULL default '' UPDATE `object_text` SET property=TRIM(TRAILING '' FROM property) ALTER TABLE `pagecache` CHANGE `id` `id` varbinary(32) NOT NULL default '' UPDATE `pagecache` SET id=TRIM(TRAILING '' FROM id) ALTER TABLE `pagecache_str2find` CHANGE `pagecache_id` `pagecache_id` varbinary(32) NOT NULL default '' UPDATE `pagecache_str2find` SET pagecache_id=TRIM(TRAILING '' FROM pagecache_id) ALTER TABLE `polls` CHANGE `id` `id` varbinary(32) NOT NULL default '' UPDATE `polls` SET id=TRIM(TRAILING '' FROM id) ALTER TABLE `polls` CHANGE `module_id` `module_id` varbinary(16) NOT NULL default '' UPDATE `polls` SET module_id=TRIM(TRAILING '' FROM module_id) ALTER TABLE `polls` CHANGE `design_id` `design_id` varbinary(32) NOT NULL default '' UPDATE `polls` SET design_id=TRIM(TRAILING '' FROM design_id) ALTER TABLE `polls` CHANGE `aftervote_design_id` `aftervote_design_id` varbinary(32) NOT NULL default '' UPDATE `polls` SET aftervote_design_id=TRIM(TRAILING '' FROM aftervote_design_id) ALTER TABLE `polls_answer` CHANGE `id` `id` varbinary(32) NOT NULL default '' UPDATE `polls_answer` SET id=TRIM(TRAILING '' FROM id) ALTER TABLE `polls_answer` CHANGE `poll_id` `poll_id` varbinary(32) NOT NULL default '' UPDATE `polls_answer` SET poll_id=TRIM(TRAILING '' FROM poll_id) ALTER TABLE `polls_design` CHANGE `id` `id` varbinary(32) NOT NULL default '' UPDATE `polls_design` SET id=TRIM(TRAILING '' FROM id) ALTER TABLE `polls_ip_lock` CHANGE `poll_id` `poll_id` varbinary(32) NOT NULL default '' UPDATE `polls_ip_lock` SET poll_id=TRIM(TRAILING '' FROM poll_id) ALTER TABLE `polls_log` CHANGE `answer_id` `answer_id` varbinary(32) NOT NULL default '' UPDATE `polls_log` SET answer_id=TRIM(TRAILING '' FROM answer_id) ALTER TABLE `polls_design` CHANGE `module_id` `module_id` varbinary(16) NOT NULL default '' UPDATE `polls_design` SET module_id=TRIM(TRAILING '' FROM module_id) ALTER TABLE `polls_ip_lock` CHANGE `voters_ip` `voters_ip` varbinary(16) NOT NULL UPDATE `polls_ip_lock` SET voters_ip=TRIM(TRAILING '' FROM voters_ip) ALTER TABLE `polls_log` CHANGE `voters_ip` `voters_ip` varbinary(16) NOT NULL default '' UPDATE `polls_log` SET voters_ip=TRIM(TRAILING '' FROM voters_ip) ALTER TABLE `post2shtml` CHANGE `id` `id` varbinary(32) NOT NULL default '' UPDATE `post2shtml` SET id=TRIM(TRAILING '' FROM id) ALTER TABLE `profile` CHANGE `slice_id` `slice_id` varbinary(16) NOT NULL default '' UPDATE `profile` SET slice_id=TRIM(TRAILING '' FROM slice_id) ALTER TABLE `relation` CHANGE `source_id` `source_id` varbinary(16) NOT NULL default '' UPDATE `relation` SET source_id=TRIM(TRAILING '' FROM source_id) ALTER TABLE `relation` CHANGE `destination_id` `destination_id` varbinary(32) NOT NULL default '' UPDATE `relation` SET destination_id=TRIM(TRAILING '' FROM destination_id) ALTER TABLE `rssfeeds` CHANGE `slice_id` `slice_id` varbinary(16) NOT NULL default '' UPDATE `rssfeeds` SET slice_id=TRIM(TRAILING '' FROM slice_id) ALTER TABLE `site` CHANGE `id` `id` varbinary(16) NOT NULL default '' UPDATE `site` SET id=TRIM(TRAILING '' FROM id) ALTER TABLE `site_spot` CHANGE `site_id` `site_id` varbinary(16) NOT NULL default '' UPDATE `site_spot` SET site_id=TRIM(TRAILING '' FROM site_id) ALTER TABLE `slice` CHANGE `id` `id` varbinary(16) NOT NULL default '' UPDATE `slice` SET id=TRIM(TRAILING '' FROM id) ALTER TABLE `slice` CHANGE `type` `type` varbinary(16) default NOT UPDATE `slice` SET type=TRIM(TRAILING '' FROM type) ALTER TABLE `slice` CHANGE `mlxctrl` `mlxctrl` varbinary(32) NOT NULL default '' UPDATE `slice` SET mlxctrl=TRIM(TRAILING '' FROM mlxctrl) ALTER TABLE `view` CHANGE `slice_id` `slice_id` varbinary(16) NOT NULL default '' UPDATE `view` SET slice_id=TRIM(TRAILING '' FROM slice_id) ALTER TABLE `view` CHANGE `order1` `order1` varbinary(16) default NULL UPDATE `view` SET order1=TRIM(TRAILING '' FROM order1) ALTER TABLE `view` CHANGE `order2` `order2` varbinary(16) default NULL UPDATE `view` SET order2=TRIM(TRAILING '' FROM order2) ALTER TABLE `view` CHANGE `group_by1` `group_by1` varbinary(16) default NULL UPDATE `view` SET group_by1=TRIM(TRAILING '' FROM group_by1) ALTER TABLE `view` CHANGE `group_by2` `group_by2` varbinary(16) default NULL UPDATE `view` SET group_by2=TRIM(TRAILING '' FROM group_by2) ALTER TABLE `view` CHANGE `cond1field` `cond1field` varbinary(16) default NULL UPDATE `view` SET cond1field=TRIM(TRAILING '' FROM cond1field) ALTER TABLE `view` CHANGE `cond1op` `cond1op` varbinary(10) default NULL UPDATE `view` SET cond1op=TRIM(TRAILING '' FROM cond1op) ALTER TABLE `view` CHANGE `cond2field` `cond2field` varbinary(16) default NULL UPDATE `view` SET cond2field=TRIM(TRAILING '' FROM cond2field) ALTER TABLE `view` CHANGE `cond2op` `cond2op` varbinary(10) default NULL UPDATE `view` SET cond2op=TRIM(TRAILING '' FROM cond2op) ALTER TABLE `view` CHANGE `cond3field` `cond3field` varbinary(16) default NULL UPDATE `view` SET cond3field=TRIM(TRAILING '' FROM cond3field) ALTER TABLE `view` CHANGE `cond3op` `cond3op` varbinary(10) default NULL UPDATE `view` SET cond3op=TRIM(TRAILING '' FROM cond3op) ALTER TABLE `view` CHANGE `field1` `field1` varbinary(16) default NULL UPDATE `view` SET field1=TRIM(TRAILING '' FROM field1) ALTER TABLE `view` CHANGE `field2` `field2` varbinary(16) default NULL UPDATE `view` SET field2=TRIM(TRAILING '' FROM field2) ALTER TABLE `view` CHANGE `field3` `field3` varbinary(16) default NULL UPDATE `view` SET field3=TRIM(TRAILING '' FROM field3) Dne Čt 28. února 2013 Bako Mihaly napsal(a): > Thank you very much Honza! > > I am after upgrade of MySQL to 5.0.96. > > My current working version of AA is 2.8.1. Is this script applicable > within 2.8.1 ? > > Apparently is working under 2.50, however I don't have PEAR - therefore > could not run test.php from 2.50 version package yet. I have to include > the script somewhere in test.php, right? > > I am on a server with cpanel+WHM, I have to figure out how to install PEAR. > > I understood that I have to include this script within test.php file. > > Do I have to run it before sql_update.php? > > Is it possible to upgrade directly from 2.8.1 to 2.50 ? > > Is it possible to go around PEAR somehow, or PEAR must be installed? > > Sorry for all these questions .... > > Thank you, > > Misi > > On 2013-02-27 16:52, Honza Malik wrote: > > Hola Misi, > > > > I would suggest to go step after step, not to do two steps in one > > time. So, the first thing is to upgrade MySQL and then AA (or > > > > oposite, but I think the first order could be easier). > > > > For the upgrade of MySQL. Yes, there was big changes in handling of > > encoding between MySQL 4.x and 5.x, so the script you > > > > mentioned is needed for removing trailing zeros. The current version of > > this fix is a bit updated, so you can use the latest version of this > > script which is in current AA - I'm attaching it at the end of the mail. > > > > There is no need to convert whole database to utf-8. Our older slices are > > in windows-1250 encoding and it works all the time we use AA. So, there > > must be the way, how to configure the connection between AA and MySQL > > properly. > > > > There is configuration option in AA config.php3 file, which we use this way (for windows-1250 encoding): > > ------------------------------------------------------------------ > > /** MySQL 4.1 is able to use different character sets for the > > communication. > > > > * Standard for MySQL client communication in PHP5 is UTF (probably), > > but if * you are using another character sets (maybe for historical > > reason), then you * need to specify it by "SET CHARACTER SET" and > > "SET COLLATION_CONNECTION" SQL * commands. Just set the right values > > to following variables. * We use (for czech character set "Windows > > 1250"): > > * define("DB_CHARACTER_SET", "cp1250"); > > * define("DB_COLLATION_CONNECTION", "cp1250_czech_cs"); > > * Default is: commented out > > */ > > > > define("DB_CHARACTER_SET", "cp1250"); > > define("DB_COLLATION_CONNECTION", "cp1250_czech_cs"); > > ------------------------------------------------------------------ > > > > This options should match the database encoding - so latin2 in your > > case. > > > > After this step you should be able to run old AA on newer MySQL > > without any problems. > > > > Then you can upgrade AA - the description of upgrade process is on > > wiki: http://actionapps.org/en/Upgrade_Guide. Current AA are > > > > tested (at least) with PHP 5.2 - 5.3 and MySQL 5.x - 5.5 or MariaDB 5.5 > > > > One more note: You mentioned, you do not understand the new > > config.php3 file. It was my fault - the config.php3 script should be > > > > the same as for previous version of AA - there are not so many changes. > > The problem is, that I submited wrong file to SVN. Now it is fixed, so > > if you look on the current config.php3 file in SVN, you will find old > > familiar version. > > > > Honza > > > > ------------------------------------------------------------------ > > Fix for trailing zeros > > ------------------------------------------------------------------ > > /** Fix user login problem, constants editiong problem, ... > > > > * Replaces binary fields by varbinary and removes trailing zeros > > * Needed for MySQL > 5.0.17 > > */ > > > > class AA_Optimize_Db_Binary_Traing_Zeros extends AA_Optimize { > > > > /** Name function > > * @return a message > > */ > > function name() { > > > > return _m("Fix user login problem, constants editiong problem, > > ..."); > > > > } > > > > /** Description function > > * @return a message > > */ > > function description() { > > > > return _m("Replaces binary fields by varbinary and removes > > trailing zeros. Needed for MySQL > 5.0.17"); > > > > } > > > > /** implemented actions within this class */ > > function actions() { return array('repair'); } > > > > /** Test function > > * @return true > > */ > > function test() { > > > > return true; > > > > } > > > > /** Repair function > > * repairs tables > > * @return true > > */ > > function repair() { > > > > $this->_fixTable('active_sessions','sid',"varbinary(32) NOT NULL > > default ''"); $this->_fixTable('change','id',"varbinary(32) NOT > > NULL default ''"); > > $this->_fixTable('change','resource_id',"varbinary(32) NOT NULL > > default ''"); > > $this->_fixTable('change_record','change_id',"varbinary(32) NOT > > NULL default ''"); > > $this->_fixTable('change_record','selector',"varbinary(255) > > default NULL"); > > $this->_fixTable('central_conf','dns_conf',"varbinary(255) NOT > > NULL default ''"); > > $this->_fixTable('central_conf','dns_web',"varbinary(15) NOT > > NULL default ''"); > > $this->_fixTable('central_conf','dns_mx',"varbinary(15) NOT > > NULL default ''"); > > $this->_fixTable('central_conf','dns_db',"varbinary(15) NOT > > NULL default ''"); > > $this->_fixTable('central_conf','dns_prim',"varbinary(255) NOT > > NULL default ''"); > > $this->_fixTable('central_conf','dns_sec',"varbinary(255) NOT > > NULL default ''"); > > $this->_fixTable('central_conf','web_conf',"varbinary(255) NOT > > NULL default ''"); > > $this->_fixTable('central_conf','web_path',"varbinary(255) NOT > > NULL default ''"); > > $this->_fixTable('central_conf','db_server',"varbinary(255) NOT > > NULL default ''"); > > $this->_fixTable('central_conf','db_name',"varbinary(255) NOT > > NULL default ''"); > > $this->_fixTable('central_conf','db_user',"varbinary(255) NOT > > NULL default ''"); > > $this->_fixTable('central_conf','db_pwd',"varbinary(255) NOT > > NULL default ''"); > > $this->_fixTable('central_conf','AA_SITE_PATH',"varbinary(255) > > NOT NULL default ''"); > > $this->_fixTable('central_conf','AA_BASE_DIR',"varbinary(255) > > NOT NULL default ''"); > > $this->_fixTable('central_conf','AA_HTTP_DOMAIN',"varbinary(255 > > ) NOT NULL default ''"); > > $this->_fixTable('central_conf','AA_ID',"varbinary(32) NOT NULL > > default ''"); > > $this->_fixTable('central_conf','ORG_NAME',"varbinary(255) NOT > > NULL default ''"); > > $this->_fixTable('central_conf','ERROR_REPORTING_EMAIL',"varbin > > ary(255) NOT NULL default ''"); > > $this->_fixTable('central_conf','ALERTS_EMAIL',"varbinary(255) > > NOT NULL default ''"); > > $this->_fixTable('central_conf','IMG_UPLOAD_URL',"varbinary(255 > > ) NOT NULL default ''"); > > $this->_fixTable('central_conf','IMG_UPLOAD_PATH',"varbinary(25 > > 5) NOT NULL default ''"); > > $this->_fixTable('central_conf','FILEMAN_BASE_DIR',"varbinary(2 > > 55) NOT NULL default ''"); > > $this->_fixTable('central_conf','FILEMAN_BASE_URL',"varbinary(2 > > 55) NOT NULL default ''"); > > $this->_fixTable('central_conf','AA_ADMIN_USER',"varbinary(30) > > NOT NULL default ''"); > > $this->_fixTable('central_conf','AA_ADMIN_PWD',"varbinary(30) > > NOT NULL default ''"); > > $this->_fixTable('content','item_id',"varbinary(16) NOT NULL > > default ''"); > > $this->_fixTable('content','field_id',"varbinary(16) NOT NULL > > default ''"); $this->_fixTable('discussion','id',"varbinary(16) > > NOT NULL default ''"); > > $this->_fixTable('discussion','parent',"varbinary(16) NOT NULL > > default ''"); > > $this->_fixTable('discussion','item_id',"varbinary(16) NOT NULL > > default ''"); > > $this->_fixTable('ef_categories','category_id',"varbinary(16) > > NOT NULL default ''"); > > $this->_fixTable('ef_categories','target_category_id',"varbinar > > y(16) NOT NULL default ''"); > > $this->_fixTable('ef_permissions','slice_id',"varbinary(16) NOT > > NULL default ''"); > > $this->_fixTable('email','owner_module_id',"varbinary(16) NOT > > NULL default ''"); > > $this->_fixTable('external_feeds','slice_id',"varbinary(16) NOT > > NULL default ''"); > > $this->_fixTable('external_feeds','remote_slice_id',"varbinary( > > 16) NOT NULL default ''"); > > $this->_fixTable('event','id',"varbinary(32) NOT NULL default > > ''"); $this->_fixTable('feedmap','from_slice_id',"varbinary(16) > > NOT NULL default ''"); > > $this->_fixTable('feedmap','from_field_id',"varbinary(16) NOT > > NULL default ''"); > > $this->_fixTable('feedmap','to_slice_id',"varbinary(16) NOT > > NULL default ''"); > > $this->_fixTable('feedmap','to_field_id',"varbinary(16) NOT > > NULL default ''"); > > $this->_fixTable('feedperms','from_id',"varbinary(16) NOT NULL > > default ''"); > > $this->_fixTable('feedperms','to_id',"varbinary(16) NOT NULL > > default ''"); $this->_fixTable('feeds','from_id',"varbinary(16) > > NOT NULL default ''"); > > $this->_fixTable('feeds','to_id',"varbinary(16) NOT NULL > > default ''"); > > $this->_fixTable('feeds','category_id',"varbinary(16) NOT NULL > > default ''"); > > $this->_fixTable('feeds','to_category_id',"varbinary(16) NOT > > NULL default ''"); $this->_fixTable('field','id',"varbinary(16) > > NOT NULL default ''"); > > $this->_fixTable('field','slice_id',"varbinary(16) NOT NULL > > default ''"); > > $this->_fixTable('field','content_id',"varbinary(16) default > > NULL"); $this->_fixTable('jump','slice_id',"varbinary(16) NOT > > NULL default ''"); > > $this->_fixTable('jump','dest_slice_id',"varbinary(16) NOT NULL > > default ''"); > > $this->_fixTable('object_float','object_id',"varbinary(16) NOT > > NULL default ''"); > > $this->_fixTable('object_float','property',"varbinary(32) NOT > > NULL default ''"); > > $this->_fixTable('object_integer','object_id',"varbinary(16) > > NOT NULL default ''"); > > $this->_fixTable('object_integer','property',"varbinary(32) NOT > > NULL default ''"); > > $this->_fixTable('object_text','object_id',"varbinary(16) NOT > > NULL default ''"); > > $this->_fixTable('object_text','property',"varbinary(32) NOT > > NULL default ''"); > > $this->_fixTable('pagecache','id',"varbinary(32) NOT NULL > > default ''"); > > $this->_fixTable('pagecache_str2find','pagecache_id',"varbinary > > (32) NOT NULL default ''"); > > $this->_fixTable('polls','id',"varbinary(32) NOT NULL default > > ''"); $this->_fixTable('polls','module_id',"varbinary(16) NOT > > NULL default ''"); > > $this->_fixTable('polls','design_id',"varbinary(32) NOT NULL > > default ''"); > > $this->_fixTable('polls','aftervote_design_id',"varbinary(32) > > NOT NULL default ''"); > > $this->_fixTable('polls_answer','id',"varbinary(32) NOT NULL > > default ''"); > > $this->_fixTable('polls_answer','poll_id',"varbinary(32) NOT > > NULL default ''"); > > $this->_fixTable('polls_design','id',"varbinary(32) NOT NULL > > default ''"); > > $this->_fixTable('polls_ip_lock','poll_id',"varbinary(32) NOT > > NULL default ''"); > > $this->_fixTable('polls_log','answer_id',"varbinary(32) NOT > > NULL default ''"); > > $this->_fixTable('polls_design','module_id',"varbinary(16) NOT > > NULL default ''"); > > $this->_fixTable('polls_ip_lock','voters_ip',"varbinary(16) NOT > > NULL"); $this->_fixTable('polls_log','voters_ip',"varbinary(16) > > NOT NULL default ''"); > > $this->_fixTable('post2shtml','id',"varbinary(32) NOT NULL > > default ''"); > > $this->_fixTable('profile','slice_id',"varbinary(16) NOT NULL > > default ''"); > > $this->_fixTable('relation','source_id',"varbinary(16) NOT NULL > > default ''"); > > $this->_fixTable('relation','destination_id',"varbinary(32) NOT > > NULL default ''"); > > $this->_fixTable('rssfeeds','slice_id',"varbinary(16) NOT NULL > > default ''"); $this->_fixTable('site','id',"varbinary(16) NOT > > NULL default ''"); > > $this->_fixTable('site_spot','site_id',"varbinary(16) NOT NULL > > default ''"); $this->_fixTable('slice','id',"varbinary(16) NOT > > NULL default ''"); > > $this->_fixTable('slice','type',"varbinary(16) default NOT"); > > $this->_fixTable('slice','mlxctrl',"varbinary(32) NOT NULL > > default ''"); $this->_fixTable('view','slice_id',"varbinary(16) > > NOT NULL default ''"); > > $this->_fixTable('view','order1',"varbinary(16) default NULL"); > > $this->_fixTable('view','order2',"varbinary(16) default NULL"); > > $this->_fixTable('view','group_by1',"varbinary(16) default > > NULL"); $this->_fixTable('view','group_by2',"varbinary(16) > > default NULL"); > > $this->_fixTable('view','cond1field',"varbinary(16) default > > NULL"); $this->_fixTable('view','cond1op',"varbinary(10) > > default NULL"); > > $this->_fixTable('view','cond2field',"varbinary(16) default > > NULL"); $this->_fixTable('view','cond2op',"varbinary(10) > > default NULL"); > > $this->_fixTable('view','cond3field',"varbinary(16) default > > NULL"); $this->_fixTable('view','cond3op',"varbinary(10) > > default NULL"); $this->_fixTable('view','field1',"varbinary(16) > > default NULL"); $this->_fixTable('view','field2',"varbinary(16) > > default NULL"); $this->_fixTable('view','field3',"varbinary(16) > > default NULL"); > > > > return true; > > > > } > > > > /** Helper _fixTable function */ > > function _fixTable($table, $field, $definition) { > > > > $db = getDb(); > > $SQL = "ALTER TABLE `$table` CHANGE `$field` `$field` > > $definition"; $this->message($SQL); > > $db->query($SQL); > > $SQL = "UPDATE `$table` SET $field=TRIM(TRAILING '\0' FROM > > $field)"; $this->message($SQL); > > $db->query($SQL); > > freeDb($db); > > > > } > > > > } > > > > Dne St 27. února 2013 Bako Mihaly napsal(a): > >> I moved an older copy of a smaller database to another server MySQL > >> v.4.21, and upgraded to 2.10 - a version used by Dan as well. > >> > >> Is there a safe way to convert the data from latin1 to UTF-8? How can I > >> bring that back safely to MySQL 5.0.96? Or it is better to upgrade to > >> current version od AA 2.50 and bring back to 5.0.96 afterwards? > >> > >> Best, > >> > >> Misi > >> > >> On 2013-02-26 14:11, Bako Mihaly wrote: > >>> Hi Honza! > >>> > >>> We have a serious problem upgrading from 2.8.1 to 2.10 - we tried the > >>> script you provided below - constants did not work, however we can log > >>> in after we run it. But we don't see the drop down menu with the slices > >>> - therefore we can not edit anything. The site is still working. > >>> > >>> Is it possible to go all the way to 2.50? Or to any intermediate > >>> version which has proper sql_update script? I did not understand > >>> config.php in 2.50 at first look, did not try until now. > >>> > >>> We had to upgrade MySQL - and from that moment things went unstable for > >>> editing. > >>> > >>> We might have a code page problem as well, apparently this new version > >>> of MySQL is latin1, with swedish_ci, however we shall have latin2 for > >>> our characters. I had to use latin1 in config.php3 > >>> > >>> Now we have: MySQL 5.0.96 (Client API version), PHP Version 5.2.17, > >>> server CENTOS 5.9 i686 standard. > >>> > >>> Also some hint how to copy safely the database to have a proper test > >>> version would help. I was afraid to do all that on the production > >>> database - and maybe there was some problem with database replication > >>> as well. > >>> > >>> > >>> Any help is welcome, > >>> > >>> thank you. > >>> > >>> Best, > >>> > >>> Misi > >>> > >>> On 2006-08-19 00:01, Honza Malik wrote: > >>>> Hi, > >>>> > >>>> there is problem with MySQL > 5.0.17, which adds to all binary > >>>> data > >>>> > >>>> trailing zeros, which results in the quite strange behaviour: > >>>> INSERT INTO table SET some_binary_field='text'; > >>>> SELECT FROM table WHERE some_binary_field='text'; > >>>> > >>>> 0 Rows returned. > >>>> > >>>> The solution is to change all binary fields to varbinary and > >>>> then > >>>> > >>>> remove all trailing zeros from the field - like: > >>>> ALTER TABLE `users` CHANGE `uid` `uid` varbinary(40) NOT NULL > >>>> default ''; UPDATE `users` SET uid=TRIM(TRAILING '\0' FROM uid); > >>>> > >>>> There is 'optimize' class, which should be placed in the > >>>> /test.php3 > >>>> > >>>> file and which repairs all the tables. > >>>> > >>>> Honza > >>>> > >>>> /** Fix user login problem, constants editiong problem, ... > >>>> > >>>> * Replaces binary fields by varbinary and removes trailing zeros > >>>> * Needed for MySQL > 5.0.17 > >>>> */ > >>>> > >>>> class Optimize_db_binary_traing_zeros extends Optimize { > >>>> > >>>> function name() { > >>>> > >>>> return _m("Fix user login problem, constants editiong > >>>> problem, ..."); > >>>> > >>>> } > >>>> > >>>> function description() { > >>>> > >>>> return _m("Replaces binary fields by varbinary and removes > >>>> trailing zeros. Needed for MySQL > 5.0.17"); > >>>> > >>>> } > >>>> > >>>> function test() { > >>>> > >>>> return true; > >>>> > >>>> } > >>>> > >>>> function repair() { > >>>> > >>>> $this->_fixTable('constant','id','varbinary(16) NOT NULL > >>>> default \'\''); > >>>> $this->_fixTable('constant','group_id','varbinary(16) NOT > >>>> NULL default \'\''); > >>>> $this->_fixTable('constant','class','varbinary(16) default > >>>> NULL'); > >>>> $this->_fixTable('alerts_collection','module_id',"varbinary > >>>> (1 6) NOT NULL default ''"); > >>>> $this->_fixTable('alerts_collection','slice_id',"varbinary( > >>>> 16 ) default NULL"); > >>>> $this->_fixTable('alerts_collection_filter','collectionid', > >>>> "v arbinary(6) NOT NULL default ''"); > >>>> $this->_fixTable('alerts_collection_howoften','collectionid > >>>> ', "varbinary(6) NOT NULL default ''"); > >>>> $this->_fixTable('constant_slice','slice_id',"varbinary(16) > >>>> default NULL"); > >>>> $this->_fixTable('constant_slice','group_id',"varbinary(16) > >>>> NOT NULL default ''"); > >>>> $this->_fixTable('email_notify','slice_id',"varbinary(16) > >>>> NOT NULL default ''"); > >>>> $this->_fixTable('item','id',"varbinary(16) NOT NULL > >>>> default ''"); > >>>> $this->_fixTable('item','slice_id',"varbinary(16) NOT NULL > >>>> default ''"); > >>>> $this->_fixTable('links','id',"varbinary(16) NOT NULL > >>>> default ''"); > >>>> $this->_fixTable('membership','memberid','varbinary(32) > >>>> NOT NULL'); $this->_fixTable('module','id',"varbinary(16) > >>>> NOT NULL default ''"); > >>>> $this->_fixTable('module','owner',"varbinary(16) NOT NULL > >>>> default ''"); > >>>> $this->_fixTable('module','app_id',"varbinary(16) default > >>>> NULL"); $this->_fixTable('offline','id',"varbinary(16) NOT > >>>> NULL default ''"); > >>>> $this->_fixTable('offline','digest',"varbinary(32) NOT NULL > >>>> default ''"); > >>>> $this->_fixTable('perms','objectid',"varbinary(32) NOT NULL > >>>> default ''"); > >>>> $this->_fixTable('perms','userid',"varbinary(32) NOT NULL > >>>> default '0'"); > >>>> $this->_fixTable('perms','perm',"varbinary(32) NOT NULL > >>>> default ''"); > >>>> $this->_fixTable('polls_log','votersIP',"varbinary(16) NOT > >>>> NULL default ''"); > >>>> $this->_fixTable('slice_owner','id',"varbinary(16) NOT NULL > >>>> default ''"); > >>>> $this->_fixTable('subscriptions','slice_owner',"varbinary(1 > >>>> 6) default NULL"); > >>>> $this->_fixTable('users','type',"varbinary(10) NOT NULL > >>>> default ''"); > >>>> $this->_fixTable('users','password',"varbinary(30) NOT NULL > >>>> default ''"); $this->_fixTable('users','uid',"varbinary(40) > >>>> NOT NULL default ''"); return true; > >>>> > >>>> } > >>>> > >>>> function _fixTable($table, $field, $definition) { > >>>> > >>>> $db = getDb(); > >>>> $SQL = "ALTER TABLE `$table` CHANGE `$field` `$field` > >>>> $definition"; $this->messages($SQL); > >>>> $db->query($SQL); > >>>> $SQL = "UPDATE `$table` SET $field=TRIM(TRAILING '\0' FROM > >>>> $field)"; $this->messages($SQL); > >>>> $db->query($SQL); > >>>> freeDb($db); > >>>> > >>>> } > >>>> > >>>> } > >>>> > >>>> Marek Tichy píše v Pá 18. 08. 2006 v 15:54 +0200: > >>>>> Had the same problem with Mysql 5.0. > >>>>> Any solutions ? > >>>>> Marek > >>>>> > >>>>>> Hi, > >>>>>> > >>>>>> El 24/07/2006 a les 14:58 -0500, Adam Sanchez va escriure: > >>>>>>> After executing sql_update.php3 for the first time, and creating > >>>>>>> the account of the administrator, the listing of slices cannot be > >>>>>>> seen. I have 4 PHP and MySQL 4. I conducted the operation in two > >>>>>>> different servers and it happens to me the same. What can be? > >>>>>> > >>>>>> I also found problems: the setup.php3 script fails saying that > >>>>>> it "Can't assign super access permission". > >>>>>> > >>>>>> This seems a problem with database types / conversion. Here is what > >>>>>> the GetObjectsPerms call gets at setup.php3 line 266: > >>>>>> > >>>>>> Array ( > >>>>>> > >>>>>> [1 ] => Array > >>>>>> > >>>>>> ( > >>>>>> > >>>>>> [id] => 1 * > >>>>>> [type] => User * > >>>>>> [name] => Pep Turro > >>>>>> [mail] => pe...@tu... > >>>>>> [perm] => 4 * > >>>>>> > >>>>>> ) > >>>>>> > >>>>>> Note the spaces around... I have added stars (*) to mark end of > >>>>>> lines where additional spaces are present. > >>>>>> > >>>>>> Server info: MySQL 5.0.22, PHP 4.4.2 on Apache 1.3.34. > >>>> > >>>> ---------------------------------------------------------------------- > >>>> -- - Using Tomcat but need to do more? Need to support web services, > >>>> security? Get stuff done quickly with pre-integrated technology to > >>>> make your job easier Download IBM WebSphere Application Server > >>>> v.1.0.1 based on Apache Geronimo > >>>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=1216 > >>>> 4 2 _______________________________________________ > >>>> "Did you get answers to your ActionApps-related queries? If yes, > >>>> please help the ActionApps community by uploading the answers onto > >>>> appropriate space in the ActionApps documentation wiki. See the *How > >>>> to contribute* section today > >>>> http://actionapps.org/en/How_To_Contribute" > >>>> _______________________________________________ > >>>> apc-aa-general mailing list > >>>> apc...@li... > >>>> https://lists.sourceforge.net/lists/listinfo/apc-aa-general > >>> > >>> ----------------------------------------------------------------------- > >>> -- ----- Everyone hates slow websites. So do we. > >>> Make your web apps faster with AppDynamics > >>> Download AppDynamics Lite for free today: > >>> http://p.sf.net/sfu/appdyn_d2d_feb > >>> _______________________________________________ > >>> "Did you get answers to your ActionApps-related queries? If yes, please > >>> help the ActionApps community by uploading the answers onto appropriate > >>> space in the ActionApps documentation wiki. See the *How to contribute* > >>> section today http://actionapps.org/en/How_To_Contribute" > >>> _______________________________________________ > >>> apc-aa-general mailing list > >>> apc...@li... > >>> https://lists.sourceforge.net/lists/listinfo/apc-aa-general > >> > >> ------------------------------------------------------------------------ > >> --- --- Everyone hates slow websites. So do we. > >> Make your web apps faster with AppDynamics > >> Download AppDynamics Lite for free today: > >> http://p.sf.net/sfu/appdyn_d2d_feb > >> _______________________________________________ > >> "Did you get answers to your ActionApps-related queries? If yes, please > >> help the ActionApps community by uploading the answers onto appropriate > >> space in the ActionApps documentation wiki. See the *How to contribute* > >> section today http://actionapps.org/en/How_To_Contribute" > >> _______________________________________________ > >> apc-aa-general mailing list > >> apc...@li... > >> https://lists.sourceforge.net/lists/listinfo/apc-aa-general > > > > ------------------------------------------------------------------------- > > ----- Everyone hates slow websites. So do we. > > Make your web apps faster with AppDynamics > > Download AppDynamics Lite for free today: > > http://p.sf.net/sfu/appdyn_d2d_feb > > _______________________________________________ > > "Did you get answers to your ActionApps-related queries? If yes, please > > help the ActionApps community by uploading the answers onto appropriate > > space in the ActionApps documentation wiki. See the *How to contribute* > > section today http://actionapps.org/en/How_To_Contribute" > > _______________________________________________ > > apc-aa-general mailing list > > apc...@li... > > https://lists.sourceforge.net/lists/listinfo/apc-aa-general > > --------------------------------------------------------------------------- > --- Everyone hates slow websites. So do we. > Make your web apps faster with AppDynamics > Download AppDynamics Lite for free today: > http://p.sf.net/sfu/appdyn_d2d_feb > _______________________________________________ > "Did you get answers to your ActionApps-related queries? If yes, please > help the ActionApps community by uploading the answers onto appropriate > space in the ActionApps documentation wiki. See the *How to contribute* > section today http://actionapps.org/en/How_To_Contribute" > _______________________________________________ > apc-aa-general mailing list > apc...@li... > https://lists.sourceforge.net/lists/listinfo/apc-aa-general |