1. 

   karl lauritzen, Member

   I have installed IIS on my Windows 7 laptop, PHP 7.1 mysql 8 and ioncube.

   I try to install mydbr and it gets stuck on the table mydbr_templates. Does not like the column row since row is a reserved word so I put ticks on it and manually added this table. But mydbr still thinks it is not there and will not complete install. Says there is an error installing report database and repeats the mysql error 1064 about that row is a reserved word and to fix the table.

   This is the URL using to install and get the error below.

   http://localhost/mydbr/install/index.php?controller=install&action=database

   Running script failed with error
   MySQL 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 'row text NULL, footer text NULL, folder_id int NULL, creation_date datetime null' at line 5

   CREATE TABLE IF NOT EXISTS mydbr_templates (
   id int NOT NULL auto_increment,
   name varchar(128) NOT NULL,
   header text NULL,
   row text NULL,
   footer text NULL,
   folder_id int NULL,
   creation_date datetime null,
   PRIMARY KEY USING BTREE (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=0
2. 

   myDBR Team, Key Master

   Hi,
   'row' was added as a reversed word in MySQL 8.0.2. The conflict has now been resolved and you can download the latest version.

   --
   myDBR Team
3. 

   karl lauritzen, Member

   Ok downloaded new version for PHP 7.1. Got a little further now this error.

   Checking if myDBR reporting objects exist ToDo
   Running script failed with error
   MySQL 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 'admin=1; if (vCnt=0) then INSERT IGNORE INTO `mydbr_userlogin` ( user, passwo' at line 308

   CREATE PROCEDURE `sp_MyDBR_FixTables` ()
   BEGIN
   declare vCnt int;

   select count(*) into vCnt
   from mydbr_snippets;

   if (vCnt=0) then
   insert into mydbr_snippets (name, code, shortcut, cright, cdown)
   values
   ('select-clause', 'select \nfrom \nwhere ', 'Ctrl-Alt-S', 7, 0),
   ('if-clause', 'if () then\nend if;', 'Ctrl-I', 4, 0),
   ('if-else-clause', 'if () then\nelse\nend if;', 'Ctrl-Alt-I', 4, 0),
   ('while-clause', 'while () do\nend while;', 'Ctrl-Alt-W', 7, 0),
   ('create procedure', 'create procedure sp_DBR_()\nbegin\n\nend', 'Ctrl-P', 24, 0),
   ('create function', 'create function fn_() \nreturns varchar(255)\ndeterministic\nbegin\n\ndeclare v_ret varchar(255);\n\nreturn v_ret;\n\nend\n', '', 19,0),
   ('cursor', 'declare done int default 0;\n\ndeclare c_cursor cursor for\n select \n from \n where \ndeclare continue handler for not found set done = 1;\n\nopen c_cursor;\nrepeat\n fetch c_cursor into ;\n if not done then\n end if;\nuntil done end repeat;\n\nclose c_cursor;', '', 9, 3),
   ('case when', 'case \n when then \n when then \n else \nend case', '', 5, 0);

   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_param_queries' and column_name='options';

   if (vCnt=0) then
   alter table mydbr_param_queries add options varchar(255) null;

   update mydbr_param_queries set coltype=4, options = '{"scroll":true,"find":true}' where coltype=5;
   update mydbr_param_queries set coltype=4, options = '{"scroll":true}' where coltype=6;
   update mydbr_param_queries set coltype=4, options = '{"find":true}' where coltype=7;
   update mydbr_param_queries set coltype=4, options = '{"collapse":true}' where coltype=8;
   update mydbr_param_queries set coltype=4, options = '{"scroll":true,"find":true,"collapse":true}' where coltype=9;
   update mydbr_param_queries set coltype=4, options = '{"scroll":true,"collapse":true}' where coltype=10;
   update mydbr_param_queries set coltype=4, options = '{"find":true,"collapse":true}' where coltype=11;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_templates' and column_name='row';

   if (vCnt>0) then
   alter table mydbr_templates change `row` rowdata text null;
   end if;

   select CHARACTER_MAXIMUM_LENGTH into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_param_queries' and column_name='name';

   if (vCnt!=50) then
   alter table mydbr_param_queries modify column name varchar(50) not null;
   end if;

   select CHARACTER_MAXIMUM_LENGTH into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_userlogin' and column_name='password';

   if (vCnt!=255) then
   alter table mydbr_userlogin modify column password varchar(255) null;
   end if;

   select CHARACTER_MAXIMUM_LENGTH into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_params' and column_name='title';

   if (vCnt!=255) then
   alter table mydbr_params modify column title varchar(255) null;
   end if;

   select CHARACTER_MAXIMUM_LENGTH into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_params' and column_name='query_name';

   if (vCnt!=50) then
   alter table mydbr_params modify column query_name varchar(50) null;
   end if;

   select CHARACTER_MAXIMUM_LENGTH into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_params' and column_name='default_value';

   if (vCnt!=50) then
   alter table mydbr_params modify column default_value varchar(50) null;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_folders' and column_name='explanation';
   if (vCnt=0) then
   alter table mydbr_folders add explanation varchar(4096) null;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_folders' and column_name='reportgroup';

   if (vCnt=0) then
   alter table mydbr_folders add reportgroup int not null default 1;
   alter table mydbr_folders add FOREIGN KEY (reportgroup) REFERENCES mydbr_reportgroups (id);
   end if;

   select CHARACTER_MAXIMUM_LENGTH into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_folders' and column_name='explanation';

   if (vCnt!=4096) then
   alter table mydbr_folders modify column explanation varchar(4096) null;
   end if;

   select CHARACTER_MAXIMUM_LENGTH into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_reports' and column_name='explanation';

   if (vCnt!=4096) then
   alter table mydbr_reports modify column explanation varchar(4096) null;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_userlogin' and column_name='authentication';

   if (vCnt=0) then
   alter table mydbr_userlogin add authentication int not null default 2;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_userlogin' and column_name='ask_pw_change';

   if (vCnt=0) then
   alter table mydbr_userlogin add ask_pw_change int not null default 0;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_reports' and column_name='reportgroup';

   if (vCnt=0) then
   alter table mydbr_reports add reportgroup int not null default 1;
   alter table mydbr_reports add FOREIGN KEY (reportgroup) REFERENCES mydbr_reportgroups (id);
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_reports' and column_name='sortorder';

   if (vCnt=0) then
   alter table mydbr_reports add sortorder int null;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_reports' and column_name='runreport';

   if (vCnt=0) then
   alter table mydbr_reports add runreport varchar(50) null;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_reports' and column_name='autoexecute';

   if (vCnt=0) then
   alter table mydbr_reports add autoexecute tinyint null;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_reports' and column_name='parameter_help';

   if (vCnt=0) then
   alter table mydbr_reports add parameter_help varchar(10000) null;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_reports' and column_name='export';

   if (vCnt=0) then
   alter table mydbr_reports add export varchar(10) null;
   end if;

   update mydbr_folders set name='#{MYDBR_AMAIN_HOME}' where folder_id=1;

   ALTER TABLE mydbr_userlogin MODIFY COLUMN `user` varchar(128);
   ALTER TABLE mydbr_reports_priv MODIFY COLUMN username varchar(128);
   ALTER TABLE mydbr_statistics MODIFY COLUMN username varchar(128);
   ALTER TABLE mydbr_groupsusers MODIFY COLUMN `user` varchar(128);
   ALTER TABLE mydbr_log MODIFY COLUMN `user` varchar(128);
   ALTER TABLE mydbr_options MODIFY COLUMN `user` varchar(128);
   ALTER TABLE mydbr_param_queries MODIFY COLUMN `query` varchar(4096);
   ALTER TABLE mydbr_reports MODIFY COLUMN `parameter_help` varchar(10000);
   ALTER TABLE mydbr_statistics MODIFY COLUMN `query` text;
   ALTER TABLE mydbr_options MODIFY COLUMN `value` varchar(512) not null;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_statistics' and column_name='authentication';

   if (vCnt=0) then
   alter table mydbr_statistics add authentication int not null default 2;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_statistics' and column_name='ip_address';

   if (vCnt=0) then
   alter table mydbr_statistics add ip_address varchar(255) null;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_statistics' and column_name='user_agent_hash';

   if (vCnt=0) then
   alter table mydbr_statistics add user_agent_hash varchar(50) null;
   end if;

   select count(*) into vCnt
   from information_schema.KEY_COLUMN_USAGE
   where table_schema=database() and table_name='mydbr_userlogin';

   if (vCnt<2) then
   ALTER TABLE mydbr_userlogin DROP PRIMARY KEY;
   ALTER TABLE mydbr_userlogin add primary key (`user`, `authentication`);
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_params' and column_name='optional';

   if (vCnt=0) then
   alter table mydbr_params add optional int not null default 0;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_params' and column_name='only_default';

   if (vCnt=0) then
   alter table mydbr_params add only_default int not null default 0;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_params' and column_name='suffix';

   if (vCnt=0) then
   alter table mydbr_params add suffix varchar(255) default NULL;
   end if;

   select CHARACTER_MAXIMUM_LENGTH into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_params' and column_name='suffix';

   if (vCnt!=255) then
   alter table mydbr_params modify column suffix varchar(255) null;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_params' and column_name='options';

   if (vCnt=0) then
   alter table mydbr_params add options varchar(1024) null;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_userlogin' and column_name='passworddate';

   if (vCnt=0) then
   alter table mydbr_userlogin add passworddate datetime null;
   update mydbr_userlogin set passworddate = now();
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_userlogin' and column_name='email';

   if (vCnt=0) then
   alter table mydbr_userlogin add email varchar(100) NULL;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_userlogin' and column_name='telephone';

   if (vCnt=0) then
   alter table mydbr_userlogin add telephone varchar(100) NULL;
   end if;

   select count(*) into vCnt
   from mydbr_userlogin
   where admin=1;

   if (vCnt=0) then
   INSERT IGNORE INTO `mydbr_userlogin` ( user, password, name, admin, passworddate, email, authentication, telephone)
   VALUES ('dba',md5('dba'),'myDBR Administrator',1, now(), null, 2, null);
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_licenses' and column_name='type';

   if (vCnt=0) then
   alter table mydbr_licenses add `type` varchar(255) default null;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_licenses' and column_name='version';

   if (vCnt=0) then
   alter table mydbr_licenses add `version` varchar(255) default null;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_languages' and column_name='date_format';

   if (vCnt=0) then
   alter table mydbr_languages add `date_format` varchar(10) null;
   alter table mydbr_languages add `time_format` varchar(10) null;
   alter table mydbr_languages add `thousand_separator` varchar(2) null;
   alter table mydbr_languages add `decimal_separator` varchar(2) null;
   end if;

   select count(*) into vCnt
   from mydbr_folders_priv;

   if (vCnt=0) then
   insert into mydbr_folders_priv
   select folder_id, 'PUBLIC', 0, 0
   from mydbr_folders
   where invisible = 0;

   /* We'll take invisible out of use */
   update mydbr_folders
   set invisible = 2
   where invisible = 0;
   end if;

   select count(*) into vCnt
   from mydbr_template_folders;

   if (vCnt=0) then
   insert into mydbr_template_folders ( name, parent_id )
   values ('Main', null );
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_templates' and column_name='folder_id';

   if (vCnt=0) then
   alter table mydbr_templates add folder_id int null;
   update mydbr_templates set folder_id = 1;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_templates' and column_name='creation_date';

   if (vCnt=0) then
   alter table mydbr_templates add creation_date datetime null;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_localization' and column_name='creation_date';

   if (vCnt=0) then
   alter table mydbr_localization add creation_date datetime null;
   end if;

   select count(*) into vCnt
   from information_schema.columns
   where table_schema=database() and table_name='mydbr_sync_exclude' and column_name='type';

   if (vCnt=0) then
   alter table mydbr_sync_exclude add type varchar(20) null;
   end if;

   /* 4.0 -> 4.2.1 user belonging to bogus 0 group */
   delete from mydbr_groupsusers where group_id = 0;

   END
4. 

   myDBR Team, Key Master

   OK,
   one more update. Should work now.

   --
   myDBR Team
5. 

   karl lauritzen, Member

   yes works fine now.

Reply

You must log in to post.