Other than all that, I, Jay Jaeckel, hereby certify that this transcript, as shown here, substantially and correctly shows what actually happened.
Note that NONE of these steps are done as the Linux super-user. However, a few steps (that I thought I had already done) needed to be done in MySQL as the "MySQL root" super-user, which is unrelated to the Linux "root" account.
The database is created, and a table is made, with a SQL script mkusers.sql, which I created, developed, tested, and debugged earlier on frazzle. All that work isn't shown here.
Here, in more detail, is what this script does:
But first, a list of the complete script:
jeremy@debian5vm: /home/jeremy $ cd Imports
jeremy@debian5vm: /home/jeremy/Imports $ cat mkusers.sql
-- mkusers.sql -- March 3, 2011
-- Create classdb database with table "users" and insert some data.
CREATE DATABASE classdb ;
-- Creating a new database (as above) doesn't make it the
-- currently selected database. You still need to do
-- that with this statement:
USE classdb ;
CREATE TABLE users (
username char(32) NOT NULL PRIMARY KEY,
lastname char(32),
firstname char(32),
sdf_website char(32),
sdf_email char(32),
real_email char(32)
);
INSERT INTO users (username, firstname, lastname) VALUES ('profdkp', 'Dale', 'Phillips');
INSERT INTO users (username, firstname, lastname) VALUES ('anetp', 'Anet', 'Police');
INSERT INTO users (username, firstname, lastname) VALUES ('aquigley', 'Andrew', 'Quigley');
INSERT INTO users (username, firstname, lastname) VALUES ('brasudo', 'Octavio', 'Hernandez');
INSERT INTO users (username, firstname, lastname) VALUES ('brazils', 'Sean', 'Brazil');
INSERT INTO users (username, firstname, lastname) VALUES ('camery', 'Claudia', 'Mery');
INSERT INTO users (username, firstname, lastname) VALUES ('doggie', 'Jay', 'Jaeckel');
INSERT INTO users (username, firstname, lastname) VALUES ('ejamn', 'Elesh', 'Jamnadas');
INSERT INTO users (username, firstname, lastname) VALUES ('ericg', 'Eric', 'Greenberg');
INSERT INTO users (username, firstname, lastname) VALUES ('kewl', 'Andrew', 'Burton');
INSERT INTO users (username, firstname, lastname) VALUES ('javargas', 'Jesus', 'Vargas');
INSERT INTO users (username, firstname, lastname) VALUES ('jnicklas', 'Judson', 'Nicklas');
INSERT INTO users (username, firstname, lastname) VALUES ('mencinas', 'Margarita', 'Encinas');
INSERT INTO users (username, firstname, lastname) VALUES ('rrawers', 'Robert', 'Rawers');
INSERT INTO users (username, firstname, lastname) VALUES ('zerocnc', 'Richard', 'Neal');
-- Fill in the sdf_email field with address build from the
-- username and @freeshell.org
-- Note that the concat(...) function is apparently the only
-- reliable way to concatenate strings. The only string
-- concat operator supported is || and then only if MySQL
-- is configured to run in ANSI mode, otherwise there is
-- NO concat operator. Note that + doesn't work.
UPDATE users
SET sdf_email = concat( username, '@freeshell.org' ) ;
-- Likewise, fill in the sdb_website field with the presumed
-- URL for everybody's sites. As of 3 Mar 2011, these are
-- known to be mostly correct:
UPDATE users
SET sdf_website = concat( username, '.freeshell.org' ) ;
-- . . . and one known exception:
UPDATE users
SET sdf_website = 'profdkp.sdf1.org' WHERE username = 'profdkp' ;
-- I'm not going to get so nosy as to collect everybody's private
-- e-mail addresses here, although I'll insert my own:
UPDATE users
SET real_email = 'aceydogy@yahoo.com' WHERE username = 'doggie' ;
-- End of script ----------------------------------------------------
Fire up mysql (the front-end command-line interface to MySQL) and run the above script.
We note immediately that it has some problem:
jeremy@debian5vm: /home/jeremy/Imports $ mysql -p
Enter password:________
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.0.51a-24+lenny5 (Debian)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.03 sec)
Run that script already. Clearly, something ain't right:
mysql> source mkusers.sql
ERROR 1044 (42000): Access denied for user 'jeremy'@'localhost' to database 'classdb'
ERROR 1044 (42000): Access denied for user 'jeremy'@'localhost' to database 'classdb'
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
It didn't even try to create the database. Try doing it "by hand" to see what happens. Apparently I don't have all the necessary permissions I thought I had:
mysql> create database xxx ;
ERROR 1044 (42000): Access denied for user 'jeremy'@'localhost' to database 'xxx'
mysql> show grants;
+---------------------------------------------------------------------------------------------------------------+
| Grants for jeremy@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jeremy'@'localhost' IDENTIFIED BY PASSWORD '*35AD2E1F1BD1ADD4668B59CC18DE7F9273EE06C4' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Whatever that means, I guess it isn't good enough.
When I installed MySQL ( see Debian Mid-Term Log entry of 2011-03-01 ),
I created the jeremy MySQL account.
This seems good enough to let jeremy get in, but not good enough to actually do anything there.
mysql> \q
Bye
jeremy@debian5vm: /home/jeremy/Imports $
jeremy@debian5vm: /home/jeremy/Imports $ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
You can get into the MySQL "root" account, or any other MySQL
account, WITHOUT being the Linux super-user.
Just put the arguments: -u acct-name
on the command line, as above.
But furthermore, if a
password is needed, you have to ASK it to ask you for
the password, with the -p option:
jeremy@debian5vm: /home/jeremy/Imports $ mysql -u root -p
Enter password:________
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.0.51a-24+lenny5 (Debian)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*33352FB2412821F1DA8360D3693C222429DAF1DC' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> grant all on *.* to 'jeremy'@'%' identified by 'Yosemite7' ;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*33352FB2412821F1DA8360D3693C222429DAF1DC' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> \q
Bye
jeremy@debian5vm: /home/jeremy/Imports $
I thought the above would suffice, but after trying to run mkusers.sql again unsuccessfully (not shown here), I guess not. Let's try this then:
jeremy@debian5vm: /home/jeremy/Imports $ mysql -u root -p
Enter password:________
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.0.51a-24+lenny5 (Debian)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
After a bunch of playing around (not shown here), I finally did the following grants, one or more of which seems to be the right thing to make it work:
mysql> grant all on *.* to 'jeremy'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'jeremy'@'debian5vm';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'jeremy'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
Try again:
jeremy@debian5vm: /home/jeremy/Imports $ mysql -p
Enter password:________
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.0.51a-24+lenny5 (Debian)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.01 sec)
Try again to create any old database "by hand". This time, it works!
mysql> create database xxx ;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| xxx |
+--------------------+
4 rows in set (0.00 sec)
NOW, run that mkusers.sql script again already!
The command to run a script is either source scriptfile
or \. scriptfile
mysql> \. mkusers.sql
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.02 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 15 rows affected (0.01 sec)
Rows matched: 15 Changed: 15 Warnings: 0
Query OK, 15 rows affected (0.00 sec)
Rows matched: 15 Changed: 15 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Review what we've got there now:
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| classdb |
| mysql |
| test |
| xxx |
+--------------------+
5 rows in set (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_classdb |
+-------------------+
| users |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from users;
+----------+-----------+-----------+------------------------+------------------------+--------------------+
| username | lastname | firstname | sdf_website | sdf_email | real_email |
+----------+-----------+-----------+------------------------+------------------------+--------------------+
| profdkp | Phillips | Dale | profdkp.sdf1.org | profdkp@freeshell.org | NULL |
| anetp | Police | Anet | anetp.freeshell.org | anetp@freeshell.org | NULL |
| aquigley | Quigley | Andrew | aquigley.freeshell.org | aquigley@freeshell.org | NULL |
| brasudo | Hernandez | Octavio | brasudo.freeshell.org | brasudo@freeshell.org | NULL |
| brazils | Brazil | Sean | brazils.freeshell.org | brazils@freeshell.org | NULL |
| camery | Mery | Claudia | camery.freeshell.org | camery@freeshell.org | NULL |
| doggie | Jaeckel | Jay | doggie.freeshell.org | doggie@freeshell.org | aceydogy@yahoo.com |
| ejamn | Jamnadas | Elesh | ejamn.freeshell.org | ejamn@freeshell.org | NULL |
| ericg | Greenberg | Eric | ericg.freeshell.org | ericg@freeshell.org | NULL |
| kewl | Burton | Andrew | kewl.freeshell.org | kewl@freeshell.org | NULL |
| javargas | Vargas | Jesus | javargas.freeshell.org | javargas@freeshell.org | NULL |
| jnicklas | Nicklas | Judson | jnicklas.freeshell.org | jnicklas@freeshell.org | NULL |
| mencinas | Encinas | Margarita | mencinas.freeshell.org | mencinas@freeshell.org | NULL |
| rrawers | Rawers | Robert | rrawers.freeshell.org | rrawers@freeshell.org | NULL |
| zerocnc | Neal | Richard | zerocnc.freeshell.org | zerocnc@freeshell.org | NULL |
+----------+-----------+-----------+------------------------+------------------------+--------------------+
15 rows in set (0.00 sec)
mysql> \q
Bye
jeremy@debian5vm: /home/jeremy/Imports $
That's better. For our next miracle, we will try to build a PHP web page that does something with this database.
Stay tuned for further developments!