MySQL database issues

[clemper]

Hi,

I am planning to use MySQL with Bots instead of the default sqlite3 database.  I followed the instructions at http://code.google.com/p/bots/wiki/DeploymentOtherDatabase and ran into a few issues:

1. The syntax for the create user SQL is incorrect (at least for my version 5.0.88):

   CREATE USER bots WITH PASSWORD 'botsbots'

Should be:

   CREATE USER 'bots'@'localhost' IDENTIFIED BY 'botsbots';

If you want to connect from a separate host, you also have to create a separate user:

   CREATE USER 'bots'@'%' IDENTIFIED BY 'botsbots';

2. The syntax for the grant SQL is incorrect:

   GRANT ALL PRIVILEGES ON DATABASE botsdb TO bots

Should be: 

   GRANT ALL PRIVILEGES ON botsdb.* TO 'bots'@'localhost'

3. The 'errortext' fields in ta and filereport tables get created with a type of VARCAR(1024).  This causes failures when attempting to insert a stack trace > 1024.  I worked around this by altering the tables:

   ALTER TABLE ta MODIFY errortext TEXT;

   ALTER TABLE filereport MODIFY errortext TEXT;

4. The desc fields on the various configration fields get created with a type of LONGTEXT which allow up to 2GB.  This is probably not what is wanted.

5. The final issue is not related specifically to MySQL, but I am not able to use the Django dumpdata/loaddata to sync the configuration data for partnergroup because it is not defined as a separate model in models.py  

Thanks,

Chris Lemper

[henk-jan ebbers]

hi chris,

thank you!

will check this out.
on 1 & 2: will try this.
on 3: I see this is a bug. in bots/sql are the definitions used for ta-table; looks like I forgot to update this to 2048.
(the the length of errortext is checked, can be max 2048)
for filereport is is 2048 in local MySQl-database.

on 4; that is strange. don't know what causes this.

on 5; i tried to use this once, but ran into a lot more problems.
AFAIK you can use 'plugins' for this; these are easy to make and read.

kind regards,
henk-jan

[clemper]

Regarding 4: It looks like Django hard codes the type model.TextField to use longtext and ignores the max_length attribute in this case.

From django/db/backends/mysql/creation.py:

    # This dictionary maps Field objects to their associated MySQL column

    # types, as strings. Column-type strings can contain format strings; they'll

    # be interpolated against the values of Field.__dict__ before being output.

    # If a column type is set to None, it won't be included in the output.

    data_types = {

        'AutoField':         'integer AUTO_INCREMENT',

        'BooleanField':      'bool',

        'CharField':         'varchar(%(max_length)s)',

        'CommaSeparatedIntegerField': 'varchar(%(max_length)s)',

        'DateField':         'date',

        'DateTimeField':     'datetime',

        'DecimalField':      'numeric(%(max_digits)s, %(decimal_places)s)',

        'FileField':         'varchar(%(max_length)s)',

        'FilePathField':     'varchar(%(max_length)s)',

        'FloatField':        'double precision',

        'IntegerField':      'integer',

        'BigIntegerField':   'bigint',

        'IPAddressField':    'char(15)',

        'NullBooleanField':  'bool',

        'OneToOneField':     'integer',

        'PositiveIntegerField': 'integer UNSIGNED',

        'PositiveSmallIntegerField': 'smallint UNSIGNED',

        'SlugField':         'varchar(%(max_length)s)',

        'SmallIntegerField': 'smallint',

        'TextField':         'longtext',

        'TimeField':         'time',

    }

    

    

Regarding 5: is there a command line interface/hook that I could use to load just the botsindex.py from the plugin?  This actually might be more flexible as it is a Python script 

so I could more easily include dynamic configuration values.   In general the zipped binary is somewhat more difficult for me to integrate into my source control and build configuration.

Thansks!

Chris Lemper

[henk-jan ebbers]

I see.
I am wondering why I used Textfield here (only for desc).
normally I use CharField....

regarding 5: there is no clear hook now, but it would not be that hard to provide it.
this would be interesting for source control, indeed.

kind regards,
henk-jan

ps it's getting quite late over here ;-) (europe, amsterdam/paris time)

[BikeMike]

Hi Chris,
Regarding your first 2 points, I found the same thing. I added the
section "MySQL on Windows" to that wiki page, with the commands I
tested. As I have only tried it on windows, I left the other section
as it was.

Kind Regards,
Mike