ERROR - Failed to extract the SQL Database Schema: Cannot handle repeated column sid in table addressable

[aruvic]

Below error is produced by JDK17 Spring Boot driver using EvoMaster 3.0.0

ERROR - Failed to extract the SQL Database Schema: Cannot handle repeated column sid in table addressable
java.lang.IllegalArgumentException: Cannot handle repeated column sid in table addressable
        at org.evomaster.client.java.sql.SchemaExtractor.handleTableEntry(SchemaExtractor.java:575)
        at org.evomaster.client.java.sql.SchemaExtractor.extract(SchemaExtractor.java:196)
        at org.evomaster.client.java.controller.internal.SutController.getSqlDatabaseSchema(SutController.java:639)
        at org.evomaster.client.java.controller.internal.SutController.initSqlHandler(SutController.java:296)
        at org.evomaster.client.java.controller.internal.EMController.lambda$runSut$20(EMController.java:372)
        at org.evomaster.client.java.controller.internal.EMController.noKillSwitch(EMController.java:126)
        at org.evomaster.client.java.controller.internal.EMController.runSut(EMController.java:372)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:568)
        at shaded.org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory.lambda$static$0(ResourceMethodInvocationHandlerFactory.java:52)
        at shaded.org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:124)
        at shaded.org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:167)
        at shaded.org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:176)
        at shaded.org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:79)
        at shaded.org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:475)
        at shaded.org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:397)
        at shaded.org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:81)
        at shaded.org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:255)
        at shaded.org.glassfish.jersey.internal.Errors$1.call(Errors.java:248)
        at shaded.org.glassfish.jersey.internal.Errors$1.call(Errors.java:244)
        at shaded.org.glassfish.jersey.internal.Errors.process(Errors.java:292)
        at shaded.org.glassfish.jersey.internal.Errors.process(Errors.java:274)
        at shaded.org.glassfish.jersey.internal.Errors.process(Errors.java:244)
        at shaded.org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:265)
        at shaded.org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:234)
        at shaded.org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:680)
        at shaded.org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:394)
        at shaded.org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:346)
        at shaded.org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:366)
        at shaded.org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:319)
        at shaded.org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:205)
        at shaded.org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:763)
        at shaded.org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:551)
        at shaded.org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
        at shaded.org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1369)
        at shaded.org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
        at shaded.org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:489)
        at shaded.org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
        at shaded.org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1284)
        at shaded.org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
        at shaded.org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
        at shaded.org.eclipse.jetty.server.Server.handle(Server.java:501)
        at shaded.org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)
        at shaded.org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:556)
        at shaded.org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)
        at shaded.org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:273)
        at shaded.org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
        at shaded.org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
        at shaded.org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
        at shaded.org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
        at shaded.org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
        at shaded.org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
        at shaded.org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.produce(EatWhatYouKill.java:135)
        at shaded.org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)
        at shaded.org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)
        at java.base/java.lang.Thread.run(Thread.java:840)

DB table:

driver:

package org.test.tmfopenapi.tmf632;

import org.evomaster.client.java.controller.EmbeddedSutController;
import org.evomaster.client.java.controller.InstrumentedSutStarter;
import org.evomaster.client.java.controller.api.dto.auth.AuthenticationDto;
import org.evomaster.client.java.controller.api.dto.SutInfoDto;
import org.evomaster.client.java.controller.api.dto.database.schema.DatabaseType;
import org.evomaster.client.java.sql.DbSpecification;
import org.evomaster.client.java.controller.problem.ProblemInfo;
import org.evomaster.client.java.controller.problem.RestProblem;
import org.springframework.boot.SpringApplication;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

public class EmbeddedEvoMasterController extends EmbeddedSutController {

    public static void main(String[] args) {

        int port = 40100;
        if (args.length > 0) {
            port = Integer.parseInt(args[0]);
        }

        EmbeddedEvoMasterController controller = new EmbeddedEvoMasterController(port);
        InstrumentedSutStarter starter = new InstrumentedSutStarter(controller);

        starter.start();
    }

    private ConfigurableApplicationContext ctx;
    private Connection sqlConnection;
    private List<DbSpecification> dbSpecification;

    public EmbeddedEvoMasterController() {
        this(40100);
    }

    public EmbeddedEvoMasterController(int port) {
        setControllerPort(port);
    }

    @Override
    public String startSut() {
        String jdbcUrl = "jdbc:mysql://localhost:3306/tmf632_party_management?allowMultiQueries=true&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true";
        String username = "username";
        String password = "password";

        ctx = SpringApplication.run(OpenApiGeneratorApplication.class, new String[] {
                "--server.port=8001",
                "--spring.datasource.url=" + jdbcUrl,
                "--spring.datasource.username=" + username,
                "--spring.datasource.password=" + password,
                "--spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver"
        });

        if (sqlConnection != null) {
            try {
                sqlConnection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        JdbcTemplate jdbc = ctx.getBean(JdbcTemplate.class);
        try {
            sqlConnection = jdbc.getDataSource().getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        dbSpecification = Arrays.asList(new DbSpecification(DatabaseType.MYSQL, sqlConnection));

        return "http://localhost:" + getSutPort();
    }

    protected int getSutPort() {
        return (Integer) ((Map) ctx.getEnvironment()
                .getPropertySources().get("server.ports").getSource())
                .get("local.server.port");
    }

    @Override
    public boolean isSutRunning() {
        return ctx != null && ctx.isRunning();
    }

    @Override
    public void stopSut() {
        ctx.stop();
    }

    @Override
    public String getPackagePrefixesToCover() {
        return "org.test.tmfopenapi.tmf632";
    }

    @Override
    public void resetStateOfSUT() {
        // DbCleaner.clearDatabase_H2(sqlConnection, null);
        // SqlScriptRunnerCached.runScriptFromResourceFile(sqlConnection,"/data.sql");
    }

    @Override
    public ProblemInfo getProblemInfo() {

        return new RestProblem("http://localhost:8001/openapi.json", (List)null);
    }

    @Override
    public SutInfoDto.OutputFormat getPreferredOutputFormat() {
        return SutInfoDto.OutputFormat.JAVA_JUNIT_5;
    }

    @Override
    public List<AuthenticationDto> getInfoForAuthentication() {
        return null;
    }

    @Override
    public List<DbSpecification> getDbSpecifications() {
        return dbSpecification;
    }
}

[arcuri82]

hi,
thanks for reporting this bug.
It is rather weird, as the involved code is:

        ResultSet columns = md.getColumns(null, schemaDto.name, tableDto.name, null);

        Set<String> columnNames = new HashSet<>();
        while (columns.next()) {

            ColumnDto columnDto = new ColumnDto();
            tableDto.columns.add(columnDto);

            columnDto.table = tableDto.name;
            columnDto.name = columns.getString("COLUMN_NAME");

            if (columnNames.contains(columnDto.name)) {
                /*
                 * Perhaps we should throw a more specific exception than IllegalArgumentException
                 */
                throw new IllegalArgumentException("Cannot handle repeated column " + columnDto.name + " in table " + tableDto.name);
            } else {
                columnNames.add(columnDto.name);
            }

it looks like from a SQL query on the schema, the column name sid is returned twice. Very weird.

Which version of MySQL are you using?

[arcuri82]

hmmm... I am trying to understand how something like this is even possible...

by any chance, is the combination of name schema/table for addressable repeated in more than 1 catalog in your database?

[arcuri82]

@jgaleotti do you have any insight on how this could be happening?

[aruvic]

Hi @arcuri82 @jgaleotti,
MySQL version: 8.0.32

desc tmf632_party_management.addressable;

desc tmf720_digital_identity_management.addressable;

desc tmf_620_product_catalog.addressable;

desc tmf_620_product_catalog_new.addressable;

[arcuri82]

@aruvic thanks for providing these extra details.

it seems related to issue #806

it seems like we do not properly handle the case of multiple schemas in the database.
in this particular case, the different schemas share at least 1 table with same name, having at least 1 column with same name, leading to that issue

[aruvic]

Hi @arcuri82 @jgaleotti ,

I just want to confirm that you are searching in all schemas instead in only one.
After I renamed all other occurrences of the table addressable and rerun the code, it failed on the next table in alphabetical order which is also present in multiple schemas:

ERROR - Failed to extract the SQL Database Schema: Cannot handle repeated column sid in table characteristic
java.lang.IllegalArgumentException: Cannot handle repeated column sid in table characteristic

the code is ignoring the provided schema name in via --spring.datasource.url=

        ctx = SpringApplication.run(OpenApiGeneratorApplication.class, new String[] {
                "--server.port=8001",
                "--spring.datasource.url=" + jdbcUrl,
                "--spring.datasource.username=" + username,
                "--spring.datasource.password=" + password,
                "--spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver"
        });

        private List<DbSpecification> dbSpecification;
    
        dbSpecification = Arrays.asList(new DbSpecification(DatabaseType.MYSQL, sqlConnection));
    
        @Override
        public List<DbSpecification> getDbSpecifications() {
            return dbSpecification;
        }

[arcuri82]

Hi @aruvic ,

indeed, there are quite a few issues related with the current handling of multi-schema databases. I looked into it, and it would take quite a bit of changes throughout the whole code-base of EvoMaster to fix it. But currently we are in the middle of other important features/fixes to complete first.

For the time being, until this is solved, I would suggest to simply return null in getDbSpecifications(). That would disable the advanced support for SQL handling (ie, analyses of SQL queries at runtime, and insertions into database as part of the test cases)