several tables not getting created

[magicmissile72]

Fresh build on Debian 9.3
Followed directions and install went well. The only deviation I made from the install was i created a user account and database for both streamer and encoder rather than use the root.

CREATE DATABASE youPHPTube; CREATE USER 'youphptube'@'localhost' IDENTIFIED BY 'sanitized'; GRANT ALL PRIVILEGES ON youPHPTube.* TO youphptube@localhost; FLUSH PRIVILEGES;

CREATE DATABASE youPHPTubeEncoder;
CREATE USER 'youphptubecoder'@'localhost' IDENTIFIED BY 'sanitized';
GRANT ALL PRIVILEGES ON youPHPTubeEncoder.* TO youphptubecoder@localhost;
FLUSH PRIVILEGES;

I added both accounts on the install page and it 'appears' to have installed correctly, however several options in the streamer admin portal generate db errors. Example:
Login as admin
click "My Video's"
click "Video Chart"
generates this error:

SELECT u., v., c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified, (SELECT count(id) FROM video_ads as va where va.videos_id = v.id) as videoAdsCount, (SELECT count(id) FROM likes as l where l.videos_id = v.id AND like = 1 ) as likes, (SELECT count(id) FROM likes as l where l.videos_id = v.id AND like = -1 ) as dislikes FROM videos as v LEFT JOIN categories c ON categories_id = c.id LEFT JOIN users u ON v.users_id = u.id WHERE 1=1 AND v.status IN ('a','xmp4','xwebm','xmp3','xogg') having videoAdsCount = 0 AND v.users_id = '1'\nError : (1146) Table 'youPHPTube.videos' doesn't exist

Similarly, clicking "Dashboard" generates this:

SELECT u., v., c.iconClass, c.name as category, c.clean_name as clean_category,c.description as category_description, v.created as videoCreation, v.modified as videoModified, (SELECT count(id) FROM video_ads as va where va.videos_id = v.id) as videoAdsCount, (SELECT count(id) FROM likes as l where l.videos_id = v.id AND like = 1 ) as likes, (SELECT count(id) FROM likes as l where l.videos_id = v.id AND like = -1 ) as dislikes FROM videos as v LEFT JOIN categories c ON categories_id = c.id LEFT JOIN users u ON v.users_id = u.id WHERE 1=1 AND v.status IN ('a','xmp4','xwebm','xmp3','xogg') having videoAdsCount = 0 AND v.users_id = '1'\nError : (1146) Table 'youPHPTube.videos' doesn't exist

Looks to be the same...

[DanielnetoDotCom]

Hi, why didnt you let the script creates your database?

[magicmissile72]

I did that the first time, and same effect.
Build attempt 1
a. Changed user from root to my created user for the database
b. built DB in Maria and added user (see above commands)
c. selected the 'create database and tables' options

This did not work and I get the missing tables.

So I rebuilt it with the same process, but selected 'create only the tables' option.

Are you suggesting I should be giving the script 'root' to create and use the database?

[DanielnetoDotCom]

@magicmissile72 I dont know what is happening.

Usually is very simple, you just let the script creates the database.

and that is the way I usually do.

[magicmissile72]

I tried it again with a fresh install. Attempted to use root...but, if I run the 'mysql_secure_installtion', the root@localhost fails. So I continued with the install like before, and this time captured the error the install page gives:

Sorry!

Error performing query '

CREATE TABLE IF NOT EXISTS videos (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
clean_title VARCHAR(255) NOT NULL,
description TEXT NULL,
views_count INT NOT NULL DEFAULT 0,
status ENUM('a', 'i', 'e', 'x', 'd', 'xmp4', 'xwebm', 'xmp3', 'xogg', 'ximg') NOT NULL DEFAULT 'e' COMMENT 'a = active\ni = inactive\ne = encoding\nx = encoding error\nd = downloading\nxmp4 = encoding mp4 error \nxwebm = encoding webm error \nxmp3 = encoding mp3 error \nxogg = encoding ogg error \nximg = get image error',
created DATETIME NOT NULL,
modified DATETIME NOT NULL,
users_id INT NOT NULL,
categories_id INT NOT NULL,
filename VARCHAR(255) NOT NULL,
duration VARCHAR(15) NOT NULL,
type ENUM('audio', 'video', 'embed') NOT NULL DEFAULT 'video',
videoDownloadedLink VARCHAR(255) NULL,
order INT UNSIGNED NOT NULL DEFAULT 1,
rotation SMALLINT NULL DEFAULT 0,
zoom FLOAT NULL DEFAULT 1,
youtubeId VARCHAR(45) NULL,
videoLink VARCHAR(255) NULL,
next_videos_id INT NULL,
isSuggested INT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (id),
INDEX fk_videos_users_idx (users_id ASC),
INDEX fk_videos_categories1_idx (categories_id ASC),
UNIQUE INDEX clean_title_UNIQUE (clean_title ASC),
INDEX index5 (order ASC),
INDEX fk_videos_videos1_idx (next_videos_id ASC),
CONSTRAINT fk_videos_users
FOREIGN KEY (users_id)
REFERENCES users (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_videos_categories1
FOREIGN KEY (categories_id)
REFERENCES categories (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_videos_videos1
FOREIGN KEY (next_videos_id)
REFERENCES videos (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
': Specified key was too long; max key length is 767 bytes

It seems to create everythine else correctly...

root@video:/var/www/html# mysql -u youphptube -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 17 Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| youPHPTube |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> use youPHPTube
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [youPHPTube]> show tables;
+------------------------+
| Tables_in_youPHPTube |
+------------------------+
| categories |
| category_type_cache |
| comments |
| comments_likes |
| configurations |
| likes |
| playlists |
| playlists_has_videos |
| plugins |
| subscribes |
| users |
| users_groups |
| users_has_users_groups |
| video_ads |
| video_ads_logs |
| video_documents |
| videos_group_view |
| videos_statistics |
+------------------------+
18 rows in set (0.00 sec)

MariaDB [youPHPTube]>

The Encoder seems to install fine and works...that is I can login, upload a video and it makes three versions and dumps in the video directory.

I will try again without the secure install...but for security sake, requiring an insecure installation and requiring 'root' access is a very poor practice. It also makes it next to impossible to extract the DB function into it's own tier.

[magicmissile72]

Did a fresh install again, installed as root, left mysql alone, etc...exactly like your directions. I am still getting the install error it is not making the video directory...

Sorry!

Error performing query '

CREATE TABLE IF NOT EXISTS videos (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
clean_title VARCHAR(255) NOT NULL,
description TEXT NULL,
views_count INT NOT NULL DEFAULT 0,
status ENUM('a', 'i', 'e', 'x', 'd', 'xmp4', 'xwebm', 'xmp3', 'xogg', 'ximg') NOT NULL DEFAULT 'e' COMMENT 'a = active\ni = inactive\ne = encoding\nx = encoding error\nd = downloading\nxmp4 = encoding mp4 error \nxwebm = encoding webm error \nxmp3 = encoding mp3 error \nxogg = encoding ogg error \nximg = get image error',
created DATETIME NOT NULL,
modified DATETIME NOT NULL,
users_id INT NOT NULL,
categories_id INT NOT NULL,
filename VARCHAR(255) NOT NULL,
duration VARCHAR(15) NOT NULL,
type ENUM('audio', 'video', 'embed') NOT NULL DEFAULT 'video',
videoDownloadedLink VARCHAR(255) NULL,
order INT UNSIGNED NOT NULL DEFAULT 1,
rotation SMALLINT NULL DEFAULT 0,
zoom FLOAT NULL DEFAULT 1,
youtubeId VARCHAR(45) NULL,
videoLink VARCHAR(255) NULL,
next_videos_id INT NULL,
isSuggested INT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (id),
INDEX fk_videos_users_idx (users_id ASC),
INDEX fk_videos_categories1_idx (categories_id ASC),
UNIQUE INDEX clean_title_UNIQUE (clean_title ASC),
INDEX index5 (order ASC),
INDEX fk_videos_videos1_idx (next_videos_id ASC),
CONSTRAINT fk_videos_users
FOREIGN KEY (users_id)
REFERENCES users (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_videos_categories1
FOREIGN KEY (categories_id)
REFERENCES categories (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_videos_videos1
FOREIGN KEY (next_videos_id)
REFERENCES videos (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
': Specified key was too long; max key length is 767 bytes

[DanielnetoDotCom]

Looks like the problem is the MySQL/MariaDB version
try to search on google for "Specified key was too long; max key length is 767 bytes"

[magicmissile72]

Thank you Daniel, I did some research and found that this is in fact a problem with MariaDB 10.1...the best fix for Debian 9 users which installs version 10.1 is to manually install 10.2. I did that on a fresh install of Debian 9.3, added the Maria DB repository, installed, the continued on with your original config minus the 'mysql-server' and 'mysql-client' files. Also I did not install as 'root', but created the DB manually.

It works like a CHAMP!!!! Wow!!!

I am super impressed with what you have done and you should be quite proud of your accomplishment.

Next step is to separate the encoder and DB and build as a multi-tier application.

For anyone wanting to follow, the Debian 9.3 build is...

1. Install Debian 9.3 from net-install cd or dvd1

2. Select only 'ssh server' and 'common tools'

3. Finish install and secure/customize with any additional apps you need (like htop, vmware-tools, etc)

4. ssh in and configure IP and DNS

5. Install MariaDB 10.2 from the repository

apt-get install software-properties-common dirmngr
(wait for install to complete...)

apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xF1656F24C74CD1D8
(wait for install to complete...)

add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://ftp.osuosl.org/pub/mariadb/repo/10.2/debian stretch main'

apt-get update

apt-get install mariadb-server

6. MariaDB will ask for a password during install...so enter one

7. Run the 'secure install' for MySQL/MariaDB

mysql_secure_installation

8. Login to MariaDB

mysql -u root -p

9. Create the Main Database (youPHPTube)

CREATE DATABASE youPHPTube;
CREATE USER 'youphptube'@'localhost' IDENTIFIED BY '---------------';
GRANT ALL PRIVILEGES ON youPHPTube.* TO youphptube@localhost;
FLUSH PRIVILEGES;

10. Create the Encoder Database (youPHPTube-Encoder)
    note: no hyphen, Maria with barf on that...

CREATE DATABASE youPHPTubeEncoder;
CREATE USER 'youphptubecoder'@'localhost' IDENTIFIED BY '---------------';
GRANT ALL PRIVILEGES ON youPHPTubeEncoder.* TO youphptubecoder@localhost;
FLUSH PRIVILEGES;

quit;

11. Continue with main install script sans mysql (we installed that above)
    removed 'mysql-server mysql-client' from snippet
    use this instead...

apt-get install curl apache2 php7.0 libapache2-mod-php7.0 php7.0-mysql \
php7.0-curl php7.0-gd php7.0-intl ffmpeg git libimage-exiftool-perl \
&& cd /var/www/html && sudo git clone https://github.com/DanielnetoDotCom/YouPHPTube.git \
&& cd /var/www/html && sudo git clone https://github.com/DanielnetoDotCom/YouPHPTube-Encoder.git \
&& sudo apt-get install python \
&& sudo curl -L https://yt-dl.org/downloads/latest/youtube-dl -o /usr/local/bin/youtube-dl \
&& sudo chmod a+rx /usr/local/bin/youtube-dl && sudo a2enmod rewrite

note: should be all one line...

12. restart Apache
    systemctl restart apache2

13. Pre-configre YouPHPTube
    copy-n-paste the following in the CLI and wait...
    The 'cat' show command before and after as a sort of error checking...so you can see if it changed the value correctly.
    note: I need to make this into a shell script...

copy below and start pasting

mkdir /var/www/html/YouPHPTube/videos

mkdir /var/www/html/YouPHPTube-Encoder/videos

chown -R www-data:www-data /var/www/html/YouPHPTube/

chmod 755 /var/www/html/YouPHPTube/videos

chown -R www-data:www-data /var/www/html/YouPHPTube-Encoder/

chmod 755 /var/www/html/YouPHPTube-Encoder/videos

cat /etc/php/7.0/apache2/php.ini | grep post_max_size

sed -i -e 's/post_max_size = 8M/post_max_size = 1000M/g' /etc/php/7.0/apache2/php.ini

cat /etc/php/7.0/apache2/php.ini | grep post_max_size

cat /etc/php/7.0/apache2/php.ini | grep upload_max_filesize

sed -i -e 's/upload_max_filesize = 2M/upload_max_filesize = 1000M/g' /etc/php/7.0/apache2/php.ini

cat /etc/php/7.0/apache2/php.ini | grep upload_max_filesize

cat /etc/php/7.0/apache2/php.ini | grep max_execution_time

sed -i -e 's/max_execution_time = 30/max_execution_time = 7200/g' /etc/php/7.0/apache2/php.ini

cat /etc/php/7.0/apache2/php.ini | grep max_execution_time

cat /etc/php/7.0/apache2/php.ini | grep memory_limit

sed -i -e 's/memory_limit = 128M/memory_limit = 512M/g' /etc/php/7.0/apache2/php.ini

cat /etc/php/7.0/apache2/php.ini | grep memory_limit

systemctl restart apache2

End pasting

14. Edit Apache...
    hint: edit line 172, change 'None' to 'all'

vi /etc/apache2/apache2.conf

<Directory /var/www/>
Options Indexes FollowSymLinks
AllowOverride All
Require all granted

15. Restart Apache...

systemctl restart apache2

16. Go to your main site URL: /YouPHPTube/install

17. Go to your encoder site URL: /YouPHPTube-Encoder/install

18. Enjoy!

---

Thanks again!

[DanielnetoDotCom]

@magicmissile72 I really apprecciate your tutorial, I am positive it will help others

[hersche]

@DanielnetoDotCom can you paste it in the wiki beside the ubunut 16.04-article? i think that would be the best place for the tutorial :)

[hersche]

i made this wiki-entry for it

https://github.com/DanielnetoDotCom/YouPHPTube/wiki/Install-YouPHPTube-in-Debian-9.3

so i think this can be closed. thank you for this, @magicmissile72 !