Django 1.7, 1.8 - queryset.extra is deprecated, how to do group by on datetime with date

In Django 1.5/1.6 version -

signup_count = list(User.objects.filter(
            profile__user_type='learner').order_by(
                '-id').extra({
                             'date_only': "date(date_joined)"}).values(
                                 'date_only').annotate(
                                     signup_count=Count('id'))[:40])

It used to return list with
[{ 'date_only': , 'signup_count': }]

Now, as the extra is deprecated in newer version of Django, here the work around to get the same -

from django.db.models.expressions import Func

# Create custom sql function
class ExtractDateFunction(Func):

    function = "DATE"

signup_signup = list(User.objects.filter(
            profile__user_type='learner').order_by('-id')annotate(
date_only=ExtractDateFunction("date_joined")).values(
'date_only').annotate(
teacher_count=Count('id'))[:40])

This should give you the same results as before. 

There is no South database module 'south.db.mysql' for your database - Django

Django 1.8+

Recently I come across this error, while running my Django application 

python manage.py runserver

There is no South database module 'south.db.mysql' for your database. Please either choose a supported database, check for SOUTH_DATABASE_ADAPTER[S] settings, or remove South from INSTALLED_APPS.

To fix it, you would try to lookup for south.db.mysql or try to search if you SOUTH_DATABASE_ADAPTERS. But you won't find it in your solution. To fix it you have two choices -

1. Manually downgrade to lower Django version i.e.1.6 or so. 

pip install Django==1.6.10

2. Uninstall South from your environment (virtual environment) and move to built-in migration process. 

pip uninstall south

Good luck!

Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.

Today while accessing django-admin to update certain information I was keep getting this error on Save - 

Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction.

Platform - django 1.3.1, mysql 5.5

While doing some initial research I came across its happening because of, when you are trying to insert something from admin into the table, first its inserting into temp table or you are reading from temp table and trying to insert into main table. It considers temp table as transactional table. according to following article -

http://bugs.mysql.com/bug.php?id=53259

However, it wasn't much of help. 

You should read about different types of binlog format - http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html

you can simply switch to 'MIXED' and that might solve your problem -

SET GLOBAL binlog_format = 'MIXED'; 

If it doesn't then, read further and see different scenarios why it was happening.

I did further research and come across another thread where it was mentioned that, it might be happening because of certain tables are belog to MyISAM Engine, whereas certain tables belong to InnoDB.  I checked my db, and found out that it was correct! 

Tables for which I had downloaded script from server (which runs under mysql 5.0) had default Engine as MyISAM, and my local mysql is 5.5 which has default Engine listed as InnoDB. 

Unless you have specific table that needs a MyISAM specific feature such as fulltext indexes, you should always user InnoDB as Engine as per the most of the recommendations online and by mysql.

As of MySQL 5.5, InnoDB is the default storage Engine. and previous versions had MyISAM as default storage Engine. (reference http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html)

to check if your tables using InnoDB as default engine, you can try below command -

SHOW VARIABLES LIKE 'have_innodb';

Below will show all engines for your mysqldb, and which one is default:

  

SHOW ENGINES;

You can check by generating create table script to check if its running under InnoDB or MyISAM.

CREATE TABLE `table_name` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(500) NOT NULL

  PRIMARY KEY (`id`) 

) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1$$

That means its running under MyISAM engine.

In order to change it to run under innodb, you can execute below alter statement -

ALTER TABLE table_name ENGINE=InnoDB;

On running above you might run into,

0 20:55:09 ALTER TABLE table_name ENGINE=InnoDB Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails

This might be because foreign key been used by other tables, if we are updating the Engine for the table, you might want to go in order of foreign key reference.

Find out which tables are on InnoDB -


SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'innodb'

If you running into this issue, follow above steps and it should be fine! Comments, Feedback welcome.

MySQLWorkbench - error on DELETE, UPDATE

Sample query –

Delete from MyDB.SourceUrls

Error -

Error Code: 1175 You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

Here is the solution -

By default MySQL editor runs in Safe Mode, which means it restrict users from comprehensive delete and update operation. You need to put the where clause in your query. You can follow any of below steps to avoid the error -

1. Add SET SQL_SAFE_UPDATES=0; before your update query. -

SET SQL_SAFE_UPDATES=0;

Delete from MyDB.SourceUrls

2. Follow below steps –

MySQLWorkbench > Preferences > SQL Editor > Query Editor
uncheck option – “Safe Updates”. Forbid UPDATEs and DELETEs with no key in WHERE clause or no LIMIT clause.