How to use Django ORM to function on a field

This question is a follow-up to this one.

I'm running a Django application on top of a MySQL (actually MariaDB) database.

My Django Model looks like this:

from django.db import models
from django.db.models import Count, Sum

class myModel(models.Model):
    my_string = models.CharField(max_length=32,)
    my_date = models.DateTimeField()

    @staticmethod
    def get_stats():            
        logger.info(myModel.objects.values('my_string').annotate(
                count=Count("my_string"), 
                sum1=Sum('my_date'),
                sum2=Sum(# WHAT GOES HERE?? #),
            )
        )

When I call get_stats, it gives me the count and the sum1. However, for sum2, I want the sum of the following Database expression for each matching row: my_date + 0 (this converts it to a true integer value).

What should I put in the expression above to get that sum returned in sum2?

IllegalArgumentException: Type cannot be null

I am confronting with an issue and it seems that many people encountered it and probably couldn't solve it.

I have the following MYSQL stored procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_resource_types`()
BEGIN
    SELECT *
    FROM resource_types
    WHERE inactive = 0;
END

The entity which maps the resource_types table and the named stored procedure query.

@NamedStoredProcedureQuery(
        name="getResourceTypes",
        procedureName="get_resource_types",
        resultClasses = ResourceType.class,
        parameters = {}
)
@Entity
@Table(name = "resource_types")
public class ResourceType {
    ... fields with annotations used for validation + getters and setters ...
}

And here is my JpaRepository from which I make the call to the stored procedure

@Repository
public interface ResourceTypeRepository extends JpaRepository<ResourceType, Long> {
    @Procedure("ResourceType.getResourceTypes")
    List<ResourceType> getResourceTypes();

}

When I try to run this I get the following stack trace:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Type cannot be null; nested exception is java.lang.IllegalArgumentException: Type cannot be null
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:978)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:618)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
    at org.springframework.test.web.servlet.TestDispatcherServlet.service(TestDispatcherServlet.java:65)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
    at org.springframework.mock.web.MockFilterChain$ServletFilterProxy.doFilter(MockFilterChain.java:167)
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:134)
    at org.springframework.test.web.servlet.MockMvc.perform(MockMvc.java:144)
    at com.test.ihbs.controller.ResourceTypeControllerTest.test_getAll(ResourceTypeControllerTest.java:111)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:73)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:73)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:224)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:83)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:68)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:163)
    at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecuter.runTestClass(JUnitTestClassExecuter.java:86)
    at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecuter.execute(JUnitTestClassExecuter.java:49)
    at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassProcessor.processTestClass(JUnitTestClassProcessor.java:64)
    at org.gradle.api.internal.tasks.testing.SuiteTestClassProcessor.processTestClass(SuiteTestClassProcessor.java:50)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
    at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
    at org.gradle.messaging.dispatch.ContextClassLoaderDispatch.dispatch(ContextClassLoaderDispatch.java:32)
    at org.gradle.messaging.dispatch.ProxyDispatchAdapter$DispatchingInvocationHandler.invoke(ProxyDispatchAdapter.java:93)
    at com.sun.proxy.$Proxy2.processTestClass(Unknown Source)
    at org.gradle.api.internal.tasks.testing.worker.TestWorker.processTestClass(TestWorker.java:106)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
    at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
    at org.gradle.messaging.remote.internal.hub.MessageHub$Handler.run(MessageHub.java:360)
    at org.gradle.internal.concurrent.DefaultExecutorFactory$StoppableExecutorImpl$1.run(DefaultExecutorFactory.java:64)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: Type cannot be null; nested exception is java.lang.IllegalArgumentException: Type cannot be null
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:381)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:223)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:122)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy87.getResourceTypes(Unknown Source)
    at com.ihbs.service.ResourceTypeService.getAll(ResourceTypeService.java:34)
    at com.ihbs.controller.ResourceTypeController.getAllResourceTypes(ResourceTypeController.java:44)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:776)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)
    ... 58 more
Caused by: java.lang.IllegalArgumentException: Type cannot be null
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.setHibernateType(AbstractParameterRegistrationImpl.java:182)
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.<init>(AbstractParameterRegistrationImpl.java:131)
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.<init>(AbstractParameterRegistrationImpl.java:140)
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.<init>(AbstractParameterRegistrationImpl.java:77)
    at org.hibernate.procedure.internal.PositionalParameterRegistration.<init>(PositionalParameterRegistration.java:41)
    at org.hibernate.procedure.internal.ProcedureCallImpl.registerParameter(ProcedureCallImpl.java:275)
    at org.hibernate.jpa.internal.StoredProcedureQueryImpl.registerStoredProcedureParameter(StoredProcedureQueryImpl.java:128)
    at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.newAdhocStoredProcedureQuery(StoredProcedureJpaQuery.java:147)
    at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.createStoredProcedure(StoredProcedureJpaQuery.java:110)
    at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.doCreateQuery(StoredProcedureJpaQuery.java:68)
    at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.createQuery(StoredProcedureJpaQuery.java:58)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:295)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:74)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:97)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:88)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:395)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:373)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$DefaultMethodInvokingMethodInterceptor.invoke(RepositoryFactorySupport.java:486)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    ... 80 more

Any ideas why is this happening and how to fix it?

Export database record as CSV file

Is there any way to export database records as CSV file from Mysql Workbench by using command line syntax?

Basic program to fetch data from mysql using EBean

I have assigned a task to create DB layer using EBean. I have never used Ebean before so firstly I am trying to make a simple program to fetch data from a table. I got help from http://ift.tt/1ICNqKk to make sample program but I am unable to fetch data. I created new Entity Class from Database. Following are the few details about my first EBean project.

ebean.properties

ebean.ddl.generate=true  
ebean.ddl.run=true    
ebean.debug.sql=true  
ebean.debug.lazyload=false
ebean.logging=all  
ebean.logging.logfilesharing=all  
ebean.logging.directory=D:\\logs  
ebean.logging.iud=sql  
ebean.logging.query=sql  
ebean.logging.sqlquery=sql    
ebean.logging.txnCommit=none  
datasource.default=h2    
datasource.h2.username=sa  
datasource.h2.password=  
datasource.h2.databaseUrl=jdbc:h2:mem:tests;DB_CLOSE_DELAY=-1  
datasource.h2.databaseDriver=org.h2.Driver  
datasource.h2.minConnections=1  
datasource.h2.maxConnections=25  
datasource.h2.heartbeatsql=select 1  
datasource.h2.isolationlevel=read_committed    
datasource.mysql.username=root  
datasource.mysql.password=kalsym@123  
datasource.mysql.databaseUrl=jdbc:mysql://127.0.0.1:3306/wsp  
datasource.mysql.databaseDriver=com.mysql.jdbc.Driver  
datasource.mysql.minConnections=1  
datasource.mysql.maxConnections=25   
datasource.mysql.isolationlevel=read_committed

Code to fetch data

RoutingAlgoType routingObj = new RoutingAlgoType();
routingObj.setName("LCR");
RoutingAlgoType routingObj2  = Ebean.find(RoutingAlgoType.class, routingObj);
System.out.println("Got "+routingObj2.getDescription());

Table Data

Insert into routing_algo_type (name, description) values ('LCR', 'Least Cost Routing');

When I execute this code I get null pointer exception. It feels to me that mysql connection was not established successfully. Is there any way to verify db connection

Socialengine 4.8.6 on AWS (EC2, RDS (MYSQL/INNODB), S3 and CLOUDFRONT) - White Screen on Startup/ possible INNODB issue

Socialengine 4.8.6 - shows a 'white screen' on startup, only the http://ift.tt/1TWwvLy and sesystem.com/install can be accessed via the browser.

Problem started: I needed a new database with a copy of production data to test a new upgrade (Socialengine 4.8.9). I used PHPMYADMIN to copy the production db(PROD) to a new developement db (COPYOFPROD), both databases reside on AWS RDS, same instance and user/password, and both connect via INNODB.

Changed social/application/settings/database.php on my system from PROD to COPYOFPROD to test the database was correct (Yes - not very clever with aproduction system!). Got a white screen when trying to access the system via the browser with the new database.

Reverted the single config change on /application/settings/database.php to the original setting. The White screen remains and looking at the Socialengine error logs, it reports it doesn't recognise the PROD database anymore. No other file in the Socialengine install directories have changed.

Status : Access to the RDS databases on phpmyadmin is ok Accessing socialengine, http://ift.tt/1TWwvLA then select 'requirements and redundancy check' the System reports the following:

MySQL 'OK'

MySQL 4.1 'Unable to check. No database adapter was provided.'

MySQL InnoDB Storage Engine 'Unable to check. No database adapter was provided.'

I therefore summarise that i have crashed the INNODB service with my actions. I understand the service is sensitive and will crash if you change config entries.

I have read that the INNODB log files need to be removed before the service will resume, i have tried the procedure of removing the ib_logfiles and restarting mysql. Result : mysql start [ok], the ib_logfiles are recreated, but when i try Socialengine it still has a 'white screen' and still reports 'No database adaptor'.

My questions are :

1) How do i check INNODB services are running correctly on AWS EC2/RDSMYSQL? Note: I'm using Terminal from a OSX Machine connecting to EC2 via the standard ECUSER and a pemkey combination.

2) How do i access mysqlmonitor on AWS RDS with the appropriate permission problems to make checks on INNODB status. Currently the system reports - you need 'PROCESS' rights when trying show status commands.

3) Which are the best logs in Socialengine to see why the whitescreen is happening and are there any tips - because i am only assuming this is the INNODB issue but i need to confirm it.

I am a novice, so not sure what my next steps are..

Many Thanks

Convert 'yyyy-mm-dd' strings to date type variables for MySQL from Java

I am using JXL API for reading in the values from Excel File. I want to convert the above specified format into Date type Variable to load into MYSQL Database. Please Help me.

![Error Shown: Exception in thread "main" java.lang.Error: Unresolved compilation problem: 
Type mismatch: cannot convert from String to Date

at SoccerDatabase.read(SoccerDatabase.java:74)
at SoccerDatabase.main(SoccerDatabase.java:122)][1]


static Date[] dob=new Date[1000];
Cell cell5=sheet.getCell(4,i);
dob[i]=cell5.getContents();

Designing a database for storing 500 million domain names with full text search

I'm about to build an application that stores up to 500 million records of domain names. I'll index the '.net' or '.com' part and strip the 'www' at the beginning. So I believe the table would look like this:

domain_id | domain_name  | domain_ext
----------+--------------+-----------
1         | dropbox      | 2
2         | digitalocean | 2

domain_ext = 2 means it's a '.com' domain.

The queries I'm about to perform::

1. I need to be able to insert new domains easily.
2. I also need to make sure I'm not inserting a duplication (each domain should have only 1 record), so I think to make domain_name + domain_ext as UNIQUE index (with MySQL - InnoDB).
3. Query domains in batches. For example: SELECT * FROM tbl_domains LIMIT 300000, 600;

What do you think? will that table hold hundreds of millions of records? How about partitioning by first letter of the domain name, would that be good? Let me know your suggestions, I'm open minded.