How to: Updating/Migrating DB from release 1.3.1

[beingalink]

I just tried on a test install with a copy of an existing bugs db from before the "takeover" but I failed to make it run. I also ran "MySQL DB Schema.sql" over the old db but there were lots of errors. Could this have sth to do with me using MariaDB? Perhaps a short update info for those coming from the old bugs project would be nice to have. Thanks in advance!

[Patriboom]

What do you mean by

a copy of an existing bugs db from before the "takeover" ?

I'll try again the MySQL_DB_Schema.sql it worked nice on PhpMyAdmin.

May send me your dabase structure. I'll have a look to build a bridge between yours and the actual one.

[beingalink]

Hi,

sorry for the rude expression "takeover". I just meant that I'm on one of the last versions of bugs before you started to maintain the project. So my database has an old structure. I attached my database structure to this post. Thanks for looking into it!

bugs_scheme.sql.zip

[Patriboom]

Hello, It's ok. I didn't take it rude, but just didn't understand what you were talking about. You were write, writing about the MySQL file. It did generate errors. It doesn't any more.

I'll have a look on your db structure tomorrow. Meanwhile, you may check the update_vx-y.z.sql files. One of them may help you.

[Patriboom]

A very quick look let me think you need to use the https://github.com/pixeline/bugs/blob/master/install/update_v1-3_3.sql patch.

[beingalink]

Hi,

thanks for your quick reply. I ran update_v1-3_3.sql over a copy of my current old install's database and got "ERROR 1060 (42S21) at line 1 in file: 'update_v1-3_3.sql': Duplicate column name 'language'". The language column is already part of the old db structure I'm having as it seems.

I saw that you were already changing stuff regarding db migration in the repository so I might perhaps just wait for the next release? But if you need me to test anything, just ping me here.

Thanks again for your effort!

[Patriboom]

Thanks beinggalink for your offer.
I'll first recommand to forget 1-3_3.sql file, which I'll remove from deposit. Writing the previous message, i was thinking about 1-3_2.sql file which I recommand you to try on your database. The other (_3) is file for next generation and should not appear here. I just let it pass. My mistake.

[beingalink]

I ran "update_v1-3_2.sql" (current git version) on a copy of my db and got following output:

MariaDB [bugs_new]> source update_v1-3_2.sql;
ERROR 1060 (42S21) at line 3 in file: 'update_v1-3_2.sql': Duplicate column name 'default_assignee'
ERROR 1060 (42S21) at line 4 in file: 'update_v1-3_2.sql': Duplicate column name 'weight'
Query OK, 315 rows affected (0.02 sec)             
Records: 315  Duplicates: 0  Warnings: 0

Query OK, 315 rows affected (0.02 sec)             
Records: 315  Duplicates: 0  Warnings: 0

ERROR 1091 (42000) at line 7 in file: 'update_v1-3_2.sql': Can't DROP 'datetime'; check that column/key exists
Query OK, 26 rows affected, 15 warnings (0.02 sec) 
Records: 26  Duplicates: 0  Warnings: 15

ERROR 1050 (42S01) at line 11 in file: 'update_v1-3_2.sql': Table 'projects_issues_tags' already exists
Query OK, 0 rows affected (0.04 sec)

ERROR 1050 (42S01) at line 34 in file: 'update_v1-3_2.sql': Table 'tags' already exists
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 5 warnings (0.00 sec)
Records: 5  Duplicates: 5  Warnings: 5

ERROR 1062 (23000) at line 66 in file: 'update_v1-3_2.sql': Duplicate entry '1' for key 'PRIMARY'
ERROR 1062 (23000) at line 77 in file: 'update_v1-3_2.sql': Duplicate entry '1-2' for key 'issue_tag'
ERROR 1062 (23000) at line 84 in file: 'update_v1-3_2.sql': Duplicate entry '6' for key 'PRIMARY'

I then started the install process which worked fine and then aborted at the user generation (as this should already be in the db). When I then visit the homepage I get following error message: The requested URL /login was not found on this server..

[Patriboom]

Thanks for your help. I corrected the sql file. May you check 1.3.2 again ?

[beingalink]

Hi!

It improved but there are still some errors:

MariaDB [bugs_new]> source update_v1-3_2.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 1

Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 1

Query OK, 315 rows affected (0.02 sec)             
Records: 315  Duplicates: 0  Warnings: 0

Query OK, 315 rows affected (0.03 sec)             
Records: 315  Duplicates: 0  Warnings: 0

ERROR 1091 (42000) at line 7 in file: 'update_v1-3_2.sql': Can't DROP 'datetime'; check that column/key exists
Query OK, 26 rows affected, 15 warnings (0.01 sec) 
Records: 26  Duplicates: 0  Warnings: 15

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 1064 (42000) at line 45 in file: 'update_v1-3_2.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IF NOT EXISTS `users_todos` (
  `id` bigint(20) unsigned NOT NULL auto_increment' at line 1
Query OK, 0 rows affected, 5 warnings (0.00 sec)
Records: 5  Duplicates: 5  Warnings: 5

Query OK, 0 rows affected, 8 warnings (0.01 sec)
Records: 8  Duplicates: 8  Warnings: 8

Query OK, 0 rows affected, 315 warnings (0.03 sec)
Records: 315  Duplicates: 315  Warnings: 315

Query OK, 0 rows affected, 1 warning (0.00 sec)

I can go thru the installation process till the end but I can't get to the login page afterwards. I still get an error page with the message The requested URL /login was not found on this server..

[Patriboom]

Ok, thanks. I'll be back after Easter.

[Patriboom]

I've checked every single SQL command and every one works good on Debian / adminer.php data administration interface. Thanks for your patience.

[Patriboom]

Update from release 1.3.1 to 1.3.2

delimiter '//' CREATE PROCEDURE addcolProjects() BEGIN IF NOT EXISTS( SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='default_assignee' AND TABLE_NAME='projects' ) THEN ALTER TABLE projects ADD default_assignee bigint(20) default '1' AFTER updated_at; END IF; END; //

CREATE PROCEDURE addcolWeight() BEGIN IF NOT EXISTS( SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='weight' AND TABLE_NAME='projects_issues' ) THEN ALTER TABLE projects_issues ADD weight bigint(20) NOT NULL DEFAULT '1' AFTER status; END IF; END; //

CREATE PROCEDURE addcolDuration() BEGIN IF NOT EXISTS( SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='duration' AND TABLE_NAME='projects_issues' ) THEN ALTER TABLE projects_issues ADD duration smallint(3) NOT NULL DEFAULT '30' AFTER created_at; END IF; END; //

CREATE PROCEDURE addcolCreated_at() BEGIN IF EXISTS( SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='created_at' AND TABLE_NAME='projects_issues' ) THEN ALTER TABLE projects_issues DROP created_at; END IF; END; //

CREATE PROCEDURE addcolDatetime() BEGIN IF EXISTS( SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='datetime' AND TABLE_NAME='projects_issues' ) THEN ALTER TABLE projects_issues DROP datetime; END IF; END; //

CREATE PROCEDURE addcolLanguage() BEGIN IF EXISTS( SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='language' AND TABLE_NAME='users' ) THEN ALTER TABLE users CHANGE language language VARCHAR( 5 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'en'; ELSE ALTER TABLE users ADD language VARCHAR(5) NOT NULL DEFAULT 'en' AFTER lastname; END IF; END; //

delimiter ';' CALL addcolProjects(); CALL addcolWeight(); CALL addcolDuration(); CALL addcolCreated_at(); CALL addcolDatetime(); CALL addcolLanguage(); DROP PROCEDURE addcolProjects; DROP PROCEDURE addcolWeight; DROP PROCEDURE addcolDuration; DROP PROCEDURE addcolCreated_at; DROP PROCEDURE addcolDatetime; DROP PROCEDURE addcolLanguage;

CREATE issue-tag relationship table

CREATE TABLE IF NOT EXISTS projects_issues_tags ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, issue_id bigint(20) unsigned NOT NULL, tag_id bigint(20) unsigned NOT NULL, created_at datetime DEFAULT NULL, updated_at datetime DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY issue_tag (issue_id,tag_id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE Projects Links Table

CREATE TABLE IF NOT EXISTS projects_links ( id_link int(11) NOT NULL AUTO_INCREMENT, id_project int(11) NOT NULL DEFAULT '1', category enum('dev','git','prod') NOT NULL DEFAULT 'dev', link varchar(100) NOT NULL, created date NOT NULL, desactivated date DEFAULT NULL, PRIMARY KEY (id_link), KEY id_project_category_desactivated_created (id_project,category,desactivated,created) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE tags table

CREATE TABLE IF NOT EXISTS tags ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, tag varchar(255) NOT NULL, bgcolor varchar(50) DEFAULT NULL, created_at datetime DEFAULT NULL, updated_at datetime DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY tag (tag) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE 'utf8_general_ci';

CREATE ToDo Table

CREATE TABLE IF NOT EXISTS users_todos ( id bigint(20) unsigned NOT NULL auto_increment, issue_id bigint(20) default NULL, user_id bigint(20) default NULL, status tinyint(2) default '1', weight bigint(20) default 1, created_at datetime default NULL, updated_at datetime default NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT Activity Types

INSERT IGNORE INTO activity (id, description, activity) VALUES (1,'Opened a new issue','create-issue'), (2,'Commented on a issue','comment'), (3,'Closed an issue','close-issue'), (4,'Reopened an issue','reopen-issue'), (5,'Reassigned an issue','reassign-issue');

INSERT default tags : id 9

INSERT IGNORE INTO tags (id, tag, bgcolor, created_at, updated_at) VALUES (1, 'status:open', '#c43c35', '2013-11-30 11:23:01', '2013-11-30 11:23:01'), (2, 'status:closed', '#46A546', '2013-11-30 11:23:01', '2013-11-30 11:23:01'), (3, 'type:feature', '#62cffc', '2013-11-30 11:23:01', '2013-11-30 11:23:01'), (4, 'type:bug', '#f89406', '2013-11-30 11:23:01', '2013-11-30 11:23:01'), (6, 'resolution:won`t fix','#812323', '2013-11-30 11:23:01', '2013-11-30 11:23:01'), (7, 'resolution:fixed', '#048383', '2013-11-30 11:23:01', '2013-11-30 11:23:01'), (8, 'status:testing', '#FCC307', '2013-11-30 11:23:01', '2016-11-30 23:11:01'), (9, 'status:inProgress','#FF6600', '2016-11-10 23:12:01', '2016-11-10 23:12:01');

INSERT open/closed states

INSERT IGNORE INTO projects_issues_tags (issue_id, tag_id, created_at, updated_at) ( SELECT id as issue_id, IF(status = 1, 1, 2) as tag_id, NOW(), NOW() FROM projects_issues );

INSERT activity type for tag update

INSERT IGNORE INTO activity (id, description, activity) VALUES ('6', 'Updated issue tags', 'update-issue-tags');

[beingalink]

Hi,

thanks for fixing this issue - "update_v1-3_2.sql" runs without any error now.

After installation, I still have the problem that I don't get to the login page though:

Not Found

The requested URL /login was not found on this server.

Perhaps you also have an idea what could be wrong here?

[Patriboom]

I'm confused ... It supposed to look for "/app/application/views/layouts/login.php" not for "/login" Maybe are you talking about "login" var from the language file.

May you describe what you've done as « Installation » ? You wrote that was an update, but an updated Bugs system should have the login.php file which exists since the begining of the project (or almost).

[beingalink]

If I visit bugs.example.de (domain where bugs is accessible) I get redirected to bugs.example.de/login. This works fine in my old install where it's the same mechanism but not with the fresh bugs install.

[Patriboom]

I tried again:

• deleted database

• deleted config file

• delete MySQL _ php file

• Re-installed

• Logged in

• Logged out

• Ask for simple address like bugs/

And Yes, I've been brought into bugs/login page.

What is the old version you tried with ?

Maybe have I erased some file.

[beingalink]

I just tried a clompletely fresh install from current git master with a fresh database but got the same result. Possibly I have some permissions wrong, no idea where though.

The old version is the install from before you started maintaining the project.

[Patriboom]

Thanks again. I'll try again, but ... yes, I guess permissions problem. What OS your computer runs under ? Windows? Linux? Mac ? Thanks for your patient help.

[beingalink]

I'm running bugs on my vps which is on fedora linux.

[Patriboom]

Does your Bugs have a .htaccess file ? Does your server read .htaccess file of every single sub-directory ? Or, as you wrote: permission issue. Bugs must be able to read AND write. If you can, set rights to user, and group = www-data for the entire Bugs' directory and open rights chown -R yourusername:www-data Bugs chmod -R 770 Bugs Once installed, you may change rights to something safer if you want like chmod -R 750 or chown -R www-data:www-data with chmod -R 740

[beingalink]

OK, it was my fault. I had to expand directory access rights in my apache config. Sorry for that! I can now access bugs just fine. One issue I experience when clicking on the "N open issues" (also "N closed issues") button in a project view is:

Unhandled Exception
Message:

Undefined offset: 7

Location:

/var/www/bugs/app/application/libraries/time.php on line 19

Stack Trace:

#0 /var/www/bugs/app/laravel/laravel.php(42): Laravel\Error::native(8, 'Undefined offse...', '/var/www/bugs/a...', 19)
#1 /var/www/bugs/app/application/libraries/time.php(19): Laravel\{closure}(8, 'Undefined offse...', '/var/www/bugs/a...', 19, Array)
#2 /var/www/bugs/app/laravel/view.php(386) : eval()'d code(69): Time::age(false)
#3 /var/www/bugs/app/laravel/view.php(386): eval()
#4 /var/www/bugs/app/laravel/view.php(353): Laravel\View->get()
#5 /var/www/bugs/app/laravel/view.php(447): Laravel\View->render()
#6 /var/www/bugs/app/laravel/view.php(372): Laravel\View->data()
#7 /var/www/bugs/app/laravel/view.php(353): Laravel\View->get()
#8 /var/www/bugs/app/laravel/view.php(447): Laravel\View->render()
#9 /var/www/bugs/app/laravel/view.php(372): Laravel\View->data()
#10 /var/www/bugs/app/laravel/view.php(353): Laravel\View->get()
#11 /var/www/bugs/app/laravel/view.php(590): Laravel\View->render()
#12 /var/www/bugs/app/laravel/response.php(246): Laravel\View->__toString()
#13 /var/www/bugs/app/laravel/laravel.php(177): Laravel\Response->render()
#14 /var/www/bugs/index.php(20): require('/var/www/bugs/a...')
#15 {main}

Clicking the "Administration" button results in:

Unhandled Exception
Message:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'bugs_new.update_history' doesn't exist

SQL: SELECT * FROM `update_history` WHERE `Description` LIKE ? ORDER BY `DteRelease` DESC

Bindings: array (
  0 => 'Version%',
)

Location:

/var/www/bugs/app/laravel/database/connection.php on line 263

Stack Trace:

#0 /var/www/bugs/app/laravel/database/connection.php(183): Laravel\Database\Connection->execute('SELECT * FROM `...', Array)
#1 /var/www/bugs/app/laravel/database/query.php(709): Laravel\Database\Connection->query('SELECT * FROM `...', Array)
#2 /var/www/bugs/app/laravel/view.php(386) : eval()'d code(38): Laravel\Database\Query->get()
#3 /var/www/bugs/app/laravel/view.php(386): eval()
#4 /var/www/bugs/app/laravel/view.php(353): Laravel\View->get()
#5 /var/www/bugs/app/laravel/view.php(447): Laravel\View->render()
#6 /var/www/bugs/app/laravel/view.php(372): Laravel\View->data()
#7 /var/www/bugs/app/laravel/view.php(353): Laravel\View->get()
#8 /var/www/bugs/app/laravel/view.php(590): Laravel\View->render()
#9 /var/www/bugs/app/laravel/response.php(246): Laravel\View->__toString()
#10 /var/www/bugs/app/laravel/laravel.php(177): Laravel\Response->render()
#11 /var/www/bugs/index.php(20): require('/var/www/bugs/a...')
#12 {main}

[Patriboom]

Thanks for your comments,

About:

Unhandled Exception Message:

Undefined offset: 7

Location:

/var/www/bugs/app/application/libraries/time.php on line 19

I'll check somewhen this week.

Abouth the other issue: it's fixed in /app/application/views/administration/index.php please download the file from the master branch.

[marcelloinfoweb]

This is happening to me.

I identified the problem, that's when Bugs was in an earlier version and was updated. The $timestamp variable is returning 0 (zero).

I'm working on this as soon as I resolve edict with the solution.

[Hazaels]

Any News about the solution?

[Patriboom]

Any news on that marcelloinfoweb ?

[marcelloinfoweb]

[ SOLUTION ]

In the file: /var/www/bug/app/application/libraries/times.php:15

add:

if (empty($timestamp)) {
    return "No Time";
}

[Patriboom]

Marcello, please confim me if what I show bellow is the solution you suggest for app/application/libraries/time.php

    public static function age($timestamp)  {
        if (empty($timestamp)) {
            return "No Time";
        }
        $timestamp = (int) $timestamp;
        $difference = time() - $timestamp;
        $periods = array(__('tinyissue.second'),__('tinyissue.minute'),__('tinyissue.hour'),__('tinyissue.day'),__('tinyissue.week'),__('tinyissue.month'),__('tinyissue.year'),__('tinyissue.decade'));
        $lengths = array('60','60','24','7','4.35','12','10');
(...)

[marcelloinfoweb]

exactly! 👍

[Patriboom]

I was to not sure i did or not integrate that into master. For now, it is in « Work 2018 » branch. Until the connection and work under PHP 7.2 works well. Then, I'll merge this and that into the « master » branch. Thanks.