I have a simple, single-page Spring-Boot application to which I'm trying to add 6 columns to the "licenses" Entity.
On startup, it executes a search, but the new columns apparently aren't yet in the database. So it's failing on on SQL grammar exception, as follows:
java.sql.SQLSyntaxErrorException: Unknown column 'license0_.max_ipcam_users' in 'field list'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:974) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1024) ~[mysql-connector-java-8.0.13.jar!/:8.0.13]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.2.0.jar!/:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.2.0.jar!/:na]
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.loader.Loader.getResultSet(Loader.java:2167) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1930) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.loader.Loader.doQuery(Loader.java:937) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2689) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2672) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.loader.Loader.list(Loader.java:2501) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:504) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:395) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:220) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1508) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1537) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.query.Query.getResultList(Query.java:135) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:74) ~[hibernate-core-5.3.7.Final.jar!/:5.3.7.Final]
at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:306) ~[spring-data-jpa-2.1.2.RELEASE.jar!/:2.1.2.RELEASE]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:359) ~[spring-data-commons-2.1.2.RELEASE.jar!/:2.1.2.RELEASE]
at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:200) ~[spring-data-commons-2.1.2.RELEASE.jar!/:2.1.2.RELEASE]
at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:644) ~[spring-data-commons-2.1.2.RELEASE.jar!/:2.1.2.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.2.RELEASE.jar!/:5.1.2.RELEASE]
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:608) ~[spring-data-commons-2.1.2.RELEASE.jar!/:2.1.2.RELEASE]
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$invoke$3(RepositoryFactorySupport.java:595) ~[spring-data-commons-2.1.2.RELEASE.jar!/:2.1.2.RELEASE]
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:595) ~[spring-data-commons-2.1.2.RELEASE.jar!/:2.1.2.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.2.RELEASE.jar!/:5.1.2.RELEASE]
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59) ~[spring-data-commons-2.1.2.RELEASE.jar!/:2.1.2.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.2.RELEASE.jar!/:5.1.2.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) ~[spring-tx-5.1.2.RELEASE.jar!/:5.1.2.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.1.2.RELEASE.jar!/:5.1.2.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.2.RELEASE.jar!/:5.1.2.RELEASE]
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.1.2.RELEASE.jar!/:5.1.2.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.2.RELEASE.jar!/:5.1.2.RELEASE]
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135) ~[spring-data-jpa-2.1.2.RELEASE.jar!/:2.1.2.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.2.RELEASE.jar!/:5.1.2.RELEASE]
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93) ~[spring-aop-5.1.2.RELEASE.jar!/:5.1.2.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.2.RELEASE.jar!/:5.1.2.RELEASE]
at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61) ~[spring-data-commons-2.1.2.RELEASE.jar!/:2.1.2.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.2.RELEASE.jar!/:5.1.2.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.1.2.RELEASE.jar!/:5.1.2.RELEASE]
at com.sun.proxy.$Proxy97.findAll(Unknown Source) ~[na:na]
at com.license.gen.app.web.SearchController.getAll(SearchController.java:52) ~[classes!/:0.0.1-SNAPSHOT]
This is the license entity, with the six new fields, starting with maxIPCamUsers. I added the @Column annotation when it failed initially, but that doesn't make a difference:
package com.license.gen.app.model;
import com.license.gen.app.dtos.LicenseDTO;
import com.license.gen.app.utils.Utils;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.NonNull;
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;
import javax.persistence.*;
import java.util.Date;
@Data
@NoArgsConstructor
@Entity
@Table(name = "licenses")
public class License {
@Id
@GeneratedValue
private Long id;
@NonNull
private String fullName;
private String contact;
private String requester;
private String tag;
private String company;
private String companyId;
private String template;
private String product;
private String expiration;
private String macs;
private String dashboardIDs;
private String typeId;
private String family;
private String systems;
private String attributes;
@Column(name = "max_ipcam_users")
private String maxIPCamUsers;
@Column(name = "max_icr_users")
private String maxIcrUsers;
@Column(name = "max_lector_users")
private String maxLectorUsers;
@Column(name = "max_other_ftp_user")
private String maxOtherFTPUser;
@Column(name = "max_dws_users")
private String maxDwsUsers;
@Column(name = "max_rfid_users")
private String maxRfidUsers;
private String numRDKids;
private String persistVariablesLimit;
private String dataSourceLimit;
@Column(name = "license_filename", nullable = false)
private String licenseFilename;
@Column(name = "url", nullable = false)
private String url;
@Column(name = "md5_url")
private String md5Url;
@Temporal(TemporalType.TIMESTAMP)
@CreationTimestamp
private Date dateCreated = new Date();
@UpdateTimestamp
@Temporal(TemporalType.TIMESTAMP)
private Date dateUpdated = new Date();
@ManyToOne(cascade = CascadeType.PERSIST)
private User user;
public License(LicenseDTO licenseDTO) {
this.setFieldsFromDTO(licenseDTO);
}
public void setFieldsFromDTO(LicenseDTO licenseDTO) {
this.fullName = licenseDTO.getFullName();
this.contact = licenseDTO.getContact();
this.requester = licenseDTO.getRequester();
this.tag = licenseDTO.getTag();
this.company = licenseDTO.getCompany();
this.companyId = licenseDTO.getCompanyId();
this.template = licenseDTO.getTemplate();
this.product = licenseDTO.getProduct();
this.expiration = licenseDTO.getExpiration();
this.macs = licenseDTO.getMacs();
this.numRDKids = licenseDTO.getNumRDKids();
this.dashboardIDs = licenseDTO.getDashboardIDs();
this.typeId = licenseDTO.getTypeId();
this.family = licenseDTO.getFamily();
this.systems = licenseDTO.getSystems();
this.attributes = Utils.convertArrayToString(licenseDTO.getAttributesArray());
this.licenseFilename = licenseDTO.getLicenseFilename();
this.url = licenseDTO.getUrl();
this.md5Url = licenseDTO.getMd5url();
this.persistVariablesLimit = licenseDTO.getPersistVariablesLimit();
this.dataSourceLimit = licenseDTO.getDataSourceLimit();
this.maxIPCamUsers = licenseDTO.getMaxIPCamUsers();
this.maxIcrUsers = licenseDTO.getMaxIcrUsers();
this.maxLectorUsers = licenseDTO.getMaxLectorUsers();
this.maxOtherFTPUser = licenseDTO.getMaxOtherFTPUser();
this.maxDwsUsers = licenseDTO.getMaxDwsUsers();
this.maxRfidUsers = licenseDTO.getMaxRfidUsers();
}
}
This is the search class with the "findAll" method that's invoking SQL call that crashes:
package com.license.gen.app.web;
@RestController
@RequestMapping("/api")
class SearchController {
private final Logger log = LoggerFactory.getLogger(SearchController.class);
private LicenseRepository licenseRepository;
private UserRepository userRepository;
public static String bucket;
public SearchController(LicenseRepository licenseRepository,
UserRepository userRepository) {
this.licenseRepository = licenseRepository;
this.userRepository = userRepository;
}
@GetMapping("/search")
Collection<LicenseDTO> getAll() {
Collection<License> licenses = licenseRepository.findAll();
ArrayList<LicenseDTO> licenseDTOS = new ArrayList<LicenseDTO>();
for (License license : licenses) {
licenseDTOS.add(new LicenseDTO(license));
}
Collections.sort(licenseDTOS, Collections.reverseOrder());
return licenseDTOS;
}
@GetMapping("/search/{searchString}")
Collection<LicenseDTO> searchlicenses(@PathVariable String searchString)
{
Collection<License> licenses = licenseRepository.findBySearchString(
searchString,
searchString,
searchString,
searchString,
searchString,
searchString,
searchString,
searchString,
searchString,
searchString);
ArrayList<LicenseDTO> licenseDTOS = new ArrayList<LicenseDTO>();
for (License license : licenses) {
licenseDTOS.add(new LicenseDTO(license));
}
Collections.sort(licenseDTOS, Collections.reverseOrder());
return licenseDTOS;
}
}
I have added fields before in this way and Spring Boot has handled it automatically.
An ideas why it's not longer doing so?
CodePudding user response：
try adding this property to your application.properties it updated your schema when you change your entities without deleting data
spring.jpa.hibernate.ddl-auto=update
you can read more about this property and the available options about initializing your database here :
CodePudding user response：
I had exceeded the max row size. I had made one field huge like 10000 because it truncated once, but I reduced it to 1000 and that did the trick.