several tables not getting created

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:

Looks to be the same...

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

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?

@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.

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.

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...

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"

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...

Install Debian 9.3 from net-install cd or dvd1
Select only 'ssh server' and 'common tools'
Finish install and secure/customize with any additional apps you need (like htop, vmware-tools, etc)
ssh in and configure IP and DNS
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

MariaDB will ask for a password during install...so enter one
Run the 'secure install' for MySQL/MariaDB

mysql_secure_installation

mysql -u root -p

Create the Main Database (youPHPTube)

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

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;

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...

restart Apache systemctl restart apache2
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

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

Restart Apache...

systemctl restart apache2

Go to your main site URL: /YouPHPTube/install
Go to your encoder site URL: /YouPHPTube-Encoder/install
Enjoy!

Thanks again!

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

@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 :)

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 !

WWBN / AVideo