[Techtalk] Difficulties creating a MySQL database

[Brianna Laugher]

Hi all,

I'm having trouble with installing some software that requires setting
up a MySQL database. My webhost has disallowed certain commands in the
command-line MySQL and I get syntax errors when I try and paste the
install .sql files into PHPMyAdmin, possibly because of MySQL version
differences. I'm not familiar enough with MySQL to be able to figure
out what to try next.

The software is MemberDB <http://flamingspork.com/projects/memberdb/>,
which I want to use to manage membership of an organisation I'm on the
committee of. We're just starting to get members, so it would be nice
to use one solution from the start rather than double-handle data.
I think the install procedure is perhaps a little old-fashioned. If
anyone knows of other good open source web-based software for managing
memberships, I'd be glad to hear of it...

OK, so MemberDB comes with four *.sql files, which I'm instructed to
run via command-line mysql to create the database and tables and all
that. I soon found out that "create table" "create view" and "grant
all" statements wouldn't work, causing the error "ERROR 1044 (42000):
Access denied for user". I contacted my webhost's tech support and
they informed me that these commands were disabled because "then the
databases wouldn't be properly set up in CPanel and PHPMyAdmin".

So the only way to create a database is via CPanel. Doing so
automatically attaches an affix to my desired db name which is
probably why they have those commands disabled in command-line.
Whatever.

So I create the table in CPanel, tweak the sql instructions to match
the actual created db name, and paste them into PHPMyAdmin's "SQL" tab
textarea thingy.

-------------------
create table qualifications (
        id integer auto_increment not null,
        name varchar(100) not null,
        department varchar(100),
        institution varchar(100),
        detail varchar(250),
        CONSTRAINT qualifications_pkey PRIMARY KEY(id)
);
----------------------

It created this table fine, but it choked on the next one:


---------------------------------
Error

SQL query:

CREATE TABLE org_types(
id integer AUTO_INCREMENT NOT NULL ,
name varchar( 50 ) ,
description text,
CONSTRAINT "org_types_pkey" PRIMARY KEY ( id )
)

MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near '"org_types_pkey" PRIMARY KEY (id)
)' at line 5
-----------------------------------------

OK so I just now figured out it was unhappy about the quotes " . I did
a search and replace and removed them from the file and that stopped
that error.

Latest error:

-------------------------------------
Error

SQL query:

-- Create Views -- ------------ -- -- A bunch or really convenient
views for dealing with data -- -- current_memberships --
------------------- -- -- current memberships of all members of all
orgs, -- of all types, which provide membership CREATE or replace view
current_memberships AS select distinct members.*, orgs.id as org_id,
orgs.name as org_name, member_types.id as member_type_id,
member_types.type as member_type from members, orgs, org_members,
member_types where members.id = org_members.member_id AND
org_members.org_id = orgs.id AND org_members.member_type_id =
member_types.id AND member_types.org_id = orgs.id and ( -- -- you are
a current member if your membership: -- - period started before now,
and ends after now -- - never ends, always existed -- - started before
now, never ends -- - started at unknown, ends after now
(org_members.start_date < now() and org_membe[...]

MySQL said:
#1142 - CREATE VIEW command denied to user 'wikimaus'@'localhost' for
table 'current_memberships'
-----------------------

See that looks like a permission problem...but why would I be able to
create tables but not views? Hmm...

I will point out now that PHPMyAdmin reports this version info:
phpMyAdmin - 2.11.6
    * MySQL client version: 4.1.22

whereas commandline mysql is 5-something, and this software does say
it requires mysql 5. I wrote to tech support about this version
difference (are you really supporting "mysql 5" if you disable the
most important command-line commands...) and they just said something
about waiting for a cpanel upgrade.

Basically I am tearing my hair out because I haven't even got past the
install instructions. I'm reluctant to break them all up anyway
because I think it will likely make upgrading much more difficult.

Does anyone have any ideas?
If I create the DB on my own computer with the vanilla install
instructions, is there some way of like importing it wholesale into
PHPMyAdmin? I notice it has an "Import" tab but then it mentions a
text file, so... not sure what that's really doing.
I know one answer is probably "switch hosts" and maybe that is the
right longterm answer, but that would be a lot of work and I want a
solution this week or so.

Cpanel also has something called 'Remote MySQL' - could I use that somehow?
Or something else I could ask my host to enable?

Thanks for any ideas you have... :(

Brianna

-- 
They've just been waiting in a mountain for the right moment:
http://modernthings.org/