Failure to create a database

When I use create database in HeidiSQL, the data base name, say xxx, doesn't appear on the tree on the left. However, when I use Tools>Create Table, xxx is in the upper 'Create in:' small window. If I then enter the different fields in the table and hit 'Create!' the following message appears:
SQL Error: no database selected, and after hitting OK:
ZQuery3: Cannot perform this operation on a closed data set. (?)

If in MySQL I enter the request create database xxx; I receive:
ERROR 1006 (HY000): Can't create database 'xxx' (errno: 9)
Very informative.
I suspect this has something to do with permisssions.
I uninstalled HeidiSQL, cleaned the register and reinstalled Heidi: same error messages. Needless to say that I tried drop table, drop database xxx, etc.
Unfortunately it appears that there is no decent user manual for HeidiSQL.
(I have some knowledge of software engineering, and I learned that ideally 95% of the manual should be written before writing the code. I should be glad to see those 95% now).

I just tried to reproduce that issue, but with no effort:

With root-privileges I have no problems creating a new database and after that creating a table in it.

After removing the "Create"-privilege for my username, I get the errormessage "Access denied for user root". After that I can click ok, the database list refreshes but as expected, the new db was not created and I also cannot create any table in a not-existing db.

After googling a bit I can't find much stuff to read about the message you got from the mysql-client "Can't create database 'xxx'". So I guess you hit a somewhat seldom error. You should definitely check the mysql-errorlog, which should be located in the "data"-dir of your server. At least on my windows-server its named like my computer plus ".err".

Btw, what are you trying to tell us with your statement about the manual?

Many thanks for your quick reply. When I first created my music database (1,1188 records) by recovering the text file output by my previous database (which is no longer maintained), I had no problem to see it displayed with HeidiSQL. So I did not suspect that the access violation messages could be due to a privilege access problem. When I used the User-Manager and ticked ALL Privileges, I recovered my database. This time, because I made a few experiments with this database, before losing it again.
These experiments were done to explain what I mean by 'please provide a user manual'. To set up the layout of the table 'albums' of the database 'classique', I used Create Table. In the window the 'Field_Name ' should be replaced with the name of the 1st field. The adjacent area ,'Column Properties', is greyed out. Hitting Add put the name down in the Column area, and the Column Properties are now active. I entered the attributes (VARCHAR, etc.) and so on for the 14 other fields. There was not any confirmation, no ok or save button for each field. If something was wrong you will only know when the table is created. I often discovered that the attributes were not the same as entered. In particular there was a field which was indexed and should not be. I could not succeed to correct that.
After another crash (violation access, etc.) I could not repair by changing privileges with the User-Manager. I recover the database by restoring the register with the backup I made before experimenting.
Now the Index Editor : the panels 'Available Columns' and 'Columns Used' mean what? I tried several combinations, but the indexes were not changed.
My remark on providing a manual had for origin my frustration after entering 4 times the 15 fields of the database with their attributes.
Ah! Another thing: When the database is displayed, in the Data tab, there is a small window on the top right labeled 'Find'. If I enter for example BERLIOZ (one of the values of the field Compositeur, which exists in the data base) -> the record is not found, instead: Access violation.
I have still other complaints but that 's enough for to-day. Keep working on your interface, that's better than issuing SQL requests on the command line. Perhaps make more intensive testing before releasing a new version (I know the linux, etc. philosophy: let the users do the the testing -- well, this should apply only when the application is mature).

..., I used Create Table. In the window the 'Field_Name ' should be replaced with the name of the 1st field. The adjacent area ,'Column Properties', is greyed out. Hitting Add put the name down in the Column area, and the Column Properties are now active.

So you created a new column-field. Great.

The "column properties" button refer to the field you have selected in the list. If none is selected, this button is greyed out. Quite logical, isn't it?

I entered the attributes (VARCHAR, etc.) and so on for the 14 other fields. There was not any confirmation, no ok or save button for each field. If something was wrong you will only know when the table is created.

Yes, that was a bug we just fixed in SVN trunk. See here for details:
http://sourceforge.net/tracker/index.php?func=detail&aid=1662001&group_id=164593&atid=832347

I often discovered that the attributes were not the same as entered. In particular there was a field which was indexed and should not be. I could not succeed to correct that.

Just click on the button with the flash on it (Manage Indexes), then select the desired index from the drop down and press "Delete" and "Save".

After another crash (violation access, etc.) I could not repair by changing privileges with the User-Manager. I recover the database by restoring the register with the backup I made before experimenting.

Sorry, I didn't get that. Was the database crashed somehow or did you see a message-dialog with an Access violation? These things are quite different.

Now the Index Editor : the panels 'Available Columns' and 'Columns Used' mean what? I tried several combinations, but the indexes were not changed.

Oh this is simple: Don't mix up the word Index with Column! An index can contain more than one column name. So the Index Editor enables you to add available columns from your selected table and delete those already selected.

My remark on providing a manual had for origin my frustration after entering 4 times the 15 fields of the database with their attributes.

OK, frustration is always a bad thing, I agree. As told above, this bug is fixed and with the RC5 you will get a reasonable error-dialog in case you entered some faulty data into the field-editor.

When the database is displayed, in the Data tab, there is a small window on the top right labeled 'Find'. If I enter for example BERLIOZ (one of the values of the field Compositeur, which exists in the data base) -> the record is not found, instead: Access violation.

Could you please post the content of the filter-editor on the bottom?

I have still other complaints but that 's enough for to-day. Keep working on your interface, that's better than issuing SQL requests on the command line. Perhaps make more intensive testing before releasing a new version

(I know the linux, etc. philosophy: let the users do the the testing ...

What??? Be a bit more careful in your statements please.

Testing is a fundamental part for all HeidiSQL-developers. I'm sure there are bugs left to fix, and if you would look at the bugtracker you will see that we're indeed more fixing bugs than implementing new features. You may have noticed the "RC" in your used HeidiSQL-version. RC means "Release candidate" and indicates that this is not a final mostly bugfree version.

And I'm sure, that "let the users do the testing" is not a linux-philosophy.

Hi
You said "Yes, that was a bug we just fixed in SVN trunk. " What is SVN trunk? Does that mean I should wait for a next release?
Also " An index can contain more than one column name". This puzzles me, because I am not a database expert. For me a field is indexed or not, I don't understand how a field may have several indexes.
The index Editor: I wanted to index the field (column) 'Instrument 1'. I move this field from Available Columns to Columns used and I hit Add. In the small window New Index...I enter 'Instrument 1' in Index-Name. Ok, this make sense. Now I am back in the Index Editor, with 'Instrument 1' on top in Index-Name. If I select now 'Instrument 1' in Available Columns and move it to Columns used and press 'Update', the field Instrument 1 still does not appear with a green diamond with a small 'i' meaning, I guess, that it is indexed. However the SQL Log at the screen bottom displays ALTER TABLE albums ADD INDEX 'Instrument 1' ()...

I said "When the database is displayed, in the Data tab, there is a small window on the top right labeled 'Find' (in fact it is 'Search'). If I enter for example BERLIOZ (one of the values of the field Compositeur, which exists in the data base) -> the record is not found, instead: Access violation.
Content of the filter-editor on the bottom:
SELECT COUNT(*) FROM albums
SHOW TABLES LIKE 'albums'
SHOW COLUMNS FROM `albums` LIKE '%'
SELECT CONNECTION_ID()
SELECT SQL_CALC_FOUND_ROWS * FROM albums WHERE Compositeur LIKE '%BERLIOZ%' OR Titre LIKE '%BERLIOZ%' OR Titre alternatif LIKE '%BERLIOZ%' OR No LP ou CD LIKE '%BERLIOZ%' OR Opus LIKE '%BERLIOZ%' OR Type (code) LIKE '%BERLIOZ%' OR Orchestre LIKE '%BERLIOZ%' OR Chef ou soliste(s) LIKE '%BERLIOZ%' OR R

You said "Yes, that was a bug we just fixed in SVN trunk. " What is SVN trunk? Does that mean I should wait for a next release?

SVN trunk means that you could download the sources from our SVN server and compile yourself a current version with that bugfix. Or simply wait for the next official release :)

Also " An index can contain more than one column name". This puzzles me, because I am not a database expert. For me a field is indexed or not, I don't understand how a field may have several indexes.

Hmm.. how to explain... It's definitely not that easy that a column is indexed or not. Maybe you should see an index more as a file on the servers harddisk. This index often relates to only one column, but can also have more than one, so that's how it is.

[quote:ca5e180aa6="tumlatum"]...
LIKE '%BERLIOZ%' OR Type (code) LIKE '%BERLIOZ%' OR Orchestre LIKE '%BERLIOZ%' OR Chef ou soliste(s) LIKE '%BERLIOZ%' OR R

Hello

You said "Maybe you should see an index more as a file on the servers harddisk". I know: in 1988 -- yes I am that old -- I programmed a large data base (7 tables, 7 indexes) in dBase III+, under MS-DOS. Support for SQL was announced for dBaseIV but then I discontinued this project. At that time indexes indexed only one field. I will lean more about indexes.
...The Search-function does not quote these columns correctly:
I tried to rename a field using Drop Field and recreating the field with a new name with Insert Field. Result: the field is renamed but its contents is lostI thought that restoring the data directory (specially the *.frm file) would restore the old name, but the new field was still empty. Its contents was recalled only by restoring the registry... Strange.
...Which version are you using, RC4? Yes. I will wait for RC5 (I don't have a Turbo Pascal compiler).
Thank you for your help.

...The Search-function does not quote these columns correctly:
I tried to rename a field using Drop Field and recreating the field with a new name with Insert Field. Result: the field is renamed but its contents is lost

Why didn't you just rename that field?

I thought that restoring the data directory (specially the *.frm file) would restore the old name, but the new field was still empty. Its contents was recalled only by restoring the registry... Strange.

The .frm file represents the table's format, the content is held by .myd files, at least for MyISAM tables.

...Which version are you using, RC4? Yes. I will wait for RC5 (I don't have a Turbo Pascal compiler).

Should not be too long until we release RC5.

It's definitely not that easy that a column is indexed or not.

Why is there both an INDEX and an INDEX UNIQUE check box in the CREATE TABLE dialog, then? :mrgreen:

how to explain...

Perhaps with an example using cardboard boxes and a list of fruits and their colours.

Table 'fruit':
============

type         color
------------------
Apple        Red
Apple        Green
Apple        Blue
Banana       Red
Banana       Yellow
Banana       Blue

If you look for all bananas: SELECT * FROM fruit WHERE sort='Banana', then MySQL has to plow through 6 records to find the 3 pieces of fruit you want.

Now add an index on "type". Think of that index as a box that MySQL pre-sorts all the fruit into when you insert them into the table. Adding an index on "type" creates two new boxes, because there are two types of fruit - apples and bananas. One box contains all the apples, the other all the bananas. Now do SELECT * FROM fruit WHERE sort='Banana', and MySQL will only have to look in one box to give you the results you want.

Getting a bit more advanced, try making an index over both "type" and "color". MySQL will now create an index consisting of two outer boxes (apples vs bananas). Inside the apple box will be three new boxes (red, green and blue) and inside the banana box will be three other boxes (red, yellow and blue). This is clearly not efficient here, because you have whole boxes with only one item in them, and boxes take up space. But with a large enough set of data, this strategy could provide you some extra performance.

MySQL can also use multiple indexes; if you make an index over "type", and another index over "color", and then ask SELECT * FROM fruit WHERE sort='Banana' AND color='Red', MySQL would look in the banana box, and the red box, and cross-reference the stuff it finds, giving you only the items that are in both boxes.

How can one item be in both boxes? Well, MySQL creates a duplicate of all inserted rows, or rather, a duplicate of the specific fields that it needs to put into a box, along with a pointer to the whole row. This means that indexes take up a lot of disk space.

With that in mind, you'll want as few indexes as possible. But which column(s) do you choose to index?

The first rule is, you'll want to create an index that correlates to the stuff you're searching for. If you're always looking for bananas or apples, but never care about their colour, then creating an index over "color" would be stupid. That index would just be useless boxes of replicated fruit, gathering dust on a shelf somewhere.

The second rule is, the greater the cardinality, the better. An index is said to have a high cardinality if there is a high variance among the items it contain. In our example, this means that the fewer items go into each box, the better the index is. An index over "type" yields two boxes (apples and bananas) with 3 items in each. Not so great, we've only cut the number of items we need to search by 50%. An index over "color" yields 4 boxes (red, green, blue and yellow). Much better! Now we have a theoretical performance improvement of between 66% and 83%.

You'll rarely need to create multi-column indexes for performance reasons; that's really only useful for very large datasets.

Sorting stuff into boxes happens to be a very efficient way to check for duplicates. So if you want to make sure that you'll ever always only have one blue banana, one green apple (etc.) listed, you create what is called a "unique index". For this kind of use, you really do want to use a multi-column (unique!) index. Every time you insert a record, MySQL will take a look in the appropriate boxes to see if there's something similar already there, instead of scanning the whole table.

Primary keys are fancy unique indexes. Like unique indexes, a primary key can be used to uniquely identify any row in the table. Besides that, primary keys are also an indication to the user or application that this is the combination of fields that you should really store if you want to identify records. Thus primary keys usually consist of very efficient data types, like integers.

(Hey, I've practically written the manual entry by now. There we go! Hope it helps.)

Thanks Rosenfield for the nice explanations about indexes.
I downloaded RC5 beta and I am still disappointed by the Manage Indexes > Index Editor feature. As a manual not yet exists one has to proceed by trial and error; unfortunately, no success.
I wanted to delete an index. In the Index Editor there is on top a drop-down list labeled Index-Name, with 3 buttons: Add Primary, Add, and Delete. In the horizontal window the Index name(s) are found, and when selected, the name(s) of the indexed columns are displayed in the left pane below (Available columns). If a column name is selected and moved to the right pane (Columns used) with the small arrow 'Remove field from index', when the Update button is clicked: "Error in your SQL syntax, etc."
If a name is selected in the left pane and, instead of pressing Update, Delete is clicked -> a window "Delete index (the name in the horizontal window)?" Yes or Cancel appears.
So the best way I found to delete an index is to go to a command window in MySQL, and to send the appropriate SQL request "drop index xxx on table_name;"
Incidentally if the explanations by Rosenfield were interesting and clear they may concern only the fraction of users with huge databases, such as airline reservations companies. With my (small?) database of 1,188 records, a typical request on 5 of the 16 columns takes 0,031 sec. (and the same duration without any index). I should be happy to gain 0,31 sec. if I do 10 requests during a day .
Please keep working on the manual along the same lines as Rosenfield. I did a lot of trials and errors experiments only to discover the differences between single quotes, backward quotes and double quotes --when they are needed or not needed. The manual may begin there.
Cheers