SQL woes (See also: "I have absolutely no idea what I'm doing.")

[Garacesh]

Trying to get Booked set up on a VM to test its capabilities and I've hit a wall with SQL, best described as "Duurrrrrrr... what?"

 

I know literally nothing of SQL. Zip. Nil. Nada. I've never used it before. And documentation online seems to be lacking.

 

Luckily this comes with what I assume is a script/pre-programmed sequence of commands to automatically set up the database for me. Awesome.

DROP TABLE IF EXISTS `announcements`;
CREATE TABLE `announcements` (
`announcementid` mediumint(8) unsigned NOT NULL auto_increment,
`announcement_text` text NOT NULL,
`priority` mediumint(8),
`start_date` datetime,
`end_date` datetime,
PRIMARY KEY (`announcementid`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8;
[b]Etcetera etcetera[/b]

 

Their readme states:

If you have database creation privileges in MySQL

Open "/database_schema/full-install.sql" and edit the database name and username/password to match your config.php database values [Done that, thanks NotePad++]

Run or import "/database_schema/full-install.sql"

Optionally - run/import "/database_schema/testdata-utf8.sql" to bookedscheduler (sample application data will be created with 2 users: admin/password and user/password). These users are available for testing your installation.

 

However Microsoft SQL Server Management Studio wants me to connect to a SQL server and I just can't seem to get that to work..

I've tried localhost, hostname, IP address, Windows Authentication, SQL server authentication (root//[root password specified when installing MySQL Essentials 5.0.41]).. nothing works. And all the things I'm reading all gloss over the bit where you need to connect to a database engine, they just assume you've done that bit.

I've installed MySQL Essentials which had me set up a root password, so I assume that's made a database? But I don't even know how I'd check that.

 

Help me Obi Wan Kenobi Edugeek, you're my only hope!

[Steve21]

Unless they added it in recently you'd need to download and install the drivers for ODBC and connect it via data sources blahblah (Remember mysql isnt mssql )

 

If you want to just do it you should be able to do it via command line using mysql, install the tools and use the gui, or use phpmyadmin. Not sure if any of that installed as part of your install? (As a test if you open cmd and just type mysql does it try to load? Will complain of password but the principle)

 

Steve

Edited September 21, 2017 by Steve21

[Garacesh]

(Remember mysql isnt mssql )

 

Wait, wut?

Because naming software with separate names is just too easy

 

I thought the 'Microsoft' bit in 'Microsoft SQL Server Management Studio' just meant it was for managing SQL servers on Microsoft OS' >.<

 

Maybe I need to go back to basics..

 

The ReadMe gives the following prerequisites:

PHP 5.4 or greater.

MySQL 5.0 or greater.

Web server (Apache2, IIS).

OS - Windows, Mac, *nix

 

I have Windows Server 2016, IIS 10 and PHP 7.1.9 set up and they all appear to be working fine now. I can get to the login page for Booked, but when I try to register a user I just get 'Unknown Error', which I assume is because it's trying to read/write from/to a SQL database that is either not properly configured or simply doesn't exist (since other static pages like the Help and About pages load fine).

 

I installed MySQL Essentials 5.0.41 and MySQL Community 5.7.19 x86, 64-bit in my endeavour to get this working but truth be told I have no idea if I'm on the right track or not.

 

There is a 'MySQL Command Line Client' in the start menu, yeah. So it looks like I have that.

[Steve21]

It can be made to work if you install drivers etc But no it's the silly old of MYSQL vs MSSQL

 

If you open a normal admin command prompt, or the mysql command, you should be able to do: (The first bit depends which one you opened should already show it in mysql line)

 

mysql -u user -p databasename < myfile.sql

 

So guess for you would be like: mysql -u username -p "premadedatabase" < /database_schema/full-install.sql etc

 

Edit - actually sounds like it might be creating the database in your script if so would just be mysql -u username < /database_schema/full-install.sql etc might work, been awhile since I did command line

 

Steve

Edited September 21, 2017 by Steve21

[Garacesh]

Edit - actually sounds like it might be creating the database in your script

Looks like it, yeah.. It begins:

DROP DATABASE IF EXISTS booked;

CREATE DATABASE booked;

USE booked;
GRANT ALL on booked.* to 'booked_user'@'localhost' identified by '[b]REDACTED[/b]';
GRANT ALL on booked.* to 'booked_user'@'127.0.0.1' identified by '[b]REDACTED[/b]';SET foreign_key_checks = 0;

 

 

So guess for you would be like: mysql -u username -p "premadedatabase" < /database_schema/full-install.sql etc

 

mysql> mysql -u booked_user -p booked < C:\inetpub\Booked\database_schema\full-install.sql

ERROR:

Unknown command '\i'.

ERROR:

Unknown command '\B'.

->

_

[Steve21]

mySQL is linuxy, either needs / (But might still need " etc) or you could just open it from cmd and cd down to the folder it's in I'm lazy so would try 2nd

 

Edit or might work with double \\ to escape it again sorry don't really use mysql much

 

Steve

Edited September 21, 2017 by Steve21

[Garacesh]

mySQL is linuxy, either needs / (But might still need " etc)

Edit or might work with double \\ to escape it again sorry don't really use mysql much

mysql> mysql -u booked_user -p booked < "C:/inetpub/Booked/database_schema/full-install.sql"

->

_

(Just hangs here until aborted)

 

Same result with mysql> mysql -u booked_user -p booked < "C:\\inetpub\\Booked\\database_schema\\full-install.sql"

 

or you could just open it from cmd and cd down to the folder it's in I'm lazy so would try 2nd

'mysql' is not recognized as an internal or external command

 

c:\inetpub\Booked\database_schema>"C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin\mysql.exe" -u booked_user -p booked < "C:/inetpub/booked/database_schema/full-install.sql"

Enter password: *****************

ERROR 1045 (28000): Access denied for user 'booked_user'@'localhost' (using password: YES)

 

Which doesn't make sense, because the script is supposed to make booked_user for me....

Wait a minute...

I'm a idiot

Why am I specifying -u booked_user when that hasn't been created yet?!

And specifying the 'booked' database that hasn't been created yet either...

 

c:\inetpub\Booked\database_schema>"C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin\mysql.exe" -u root -p < "C:/inetpub/booked/database_schema/full-install.sql"

Enter password: *********************

[Wait]

c:\inetpub\Booked\database_schema>_

 

No error! Progress!

 

Still getting Unknown Error on the Register.php page though D:

 

mysql> mysql -u booked_user -p

doesn't get me a password prompt, though.. Just a -> _.. and if I finish it with ; I get ERROR 1064 (Syntax)

Damnit, thought I might have had it, then!

 

(It would be really nice if SQL didn't quit itself after I abort the current command.. )

 

Edit:

mysql> mysql -u root -p < "C:/inetpub/Booked/database_schema/full-install.sql"

gets me:

->

_

instead of the password challenge for root.

Edited September 21, 2017 by Garacesh

[Garacesh]

Hmm...

 

mysql> SHOW SCHEMAS;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| booked             |
| mysql              |
| test               |
+--------------------+

 

mysql> USE booked;

mysql> SHOW TABLES;

+----------------------------+
| Tables_in_booked           |
+----------------------------+
| Lots                       |
| of                         |
| tables                     |
+----------------------------+

 

So it looks like the SQL database is set up now, and my issue is elsewhere.

Alas, it's still progress. Thank you (again) @Steve21!

[Steve21]

I take it you modified both files to match before you ran the install? Else might be something silly like they aren't matching what you installed user/password vs the config etc

 

Also dig out the sql logs to check what's erroring

 

Steve

[Garacesh]

I'm starting to suspect it might be that I'm using too new a version of PHP..

It states to PHP 5.4+ but apparently php_mysql.dll was depreciated in v5.5 and completely removed in 7.0, replaced with php_mysqli.dll and I'm wondering if that might be part of the problem..

extension=php_mysqli.dll isn't commented out in my php.ini, but there's no extension=php_mysql.dll

 

So..

If I change config.php so that $conf['settings']['database']['type'] = 'mysql'; reads $conf['settings']['database']['type'] = 'mysqli';

 

I get:

Warning: require_once(../lib/external/pear/MDB2.php): failed to open stream: No such file or directory in C:\inetpub\Booked\lib\Database\MDB2\Mdb2Connection.php on line 23

Fatal error: require_once(): Failed opening required '../lib/external/pear/MDB2.php' (include_path='C:\inetpub\Booked\lib\Database\MDB2/../../../lib/external/pear/;../lib/external/pear/;.;C:\php\pear') in C:\inetpub\Booked\lib\Database\MDB2\Mdb2Connection.php on line 23

 

Which doesn't really help me make heads nor tails of it.

I wonder if it's worth downgrading the PHP version the server uses..

Edited September 21, 2017 by Garacesh

[Garacesh]

Hmm. Nope.

 

With PHP 5.6 which includes php_mysql.dll:

 

Fatal error: Call to undefined function mysqli_connect() in C:\inetpub\Booked\lib\Database\MySQL\MySqlConnection.php on line 53

 

edit:

 

uncommenting both extension=php_mysql.dll and extension=php_mysqli.dll gets me the registration form! Woo!

Edited September 21, 2017 by Garacesh

[mavhc]

Does the Mdb2Connection.php file exist in that folder? or anywhere?

[ADMaster]

This does nothing to help your current problem but...

I've always found it best to run php/mysql on Linux. Ubuntu has a install option titled LAMP (Linux Apache MySql PHP)

[Garacesh]

This does nothing to help your current problem but...

I've always found it best to run php/mysql on Linux. Ubuntu has a install option titled LAMP (Linux Apache MySql PHP)

Yeah, I've heard from so many sources that LAMP servers are better suited to it than IIS, but the problem is I just don't know anything about Linux and I just end up confused and frustrated.

 

Edit: After finally getting Booked working though.. It turns out it doesn't support multi-week schedules (at least, not that I could see...) so now I'm looking into MRBS and fighting with it to get LDAP working xD

Edited September 22, 2017 by Garacesh