System exception when image is uploaded

[sfl0r3nz05]

Version: 2.5 Platform: Docker Container on GNS3 network simulator

Description

I'd experimented with this exception when I upload an image to the Graphical View window:

I am following the next steps:

1. Once Scada-LTS container is running:

   • Access to Scada-LTS GUI: http://localhost:8080/ScadaBR/.
   • Login with username: admin password: admin.
   • Create empty script in "Scripting".

2. Go to the "SQL" tab. E.g:

   • Paste content scripts-one-insert.txt file into the text field "SQL".
   • Click "Submit update"
   • If the operation is successful, the information about adding 12 records will be displayed.

3. Import the project (.json file). E.g.:

4. Add the background image at "Graphical views". E.g:

After step 4, I receive the exception mentioned above. Then the logs of the container are inspected and I get an error related to: scadalts.liveAlarms

Feb 08, 2023 10:56:12 AM org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReport reportFailedQuery
WARNING: Failed Query Report SQL=SELECT la.id, la.`activation-time`, la.`inactivation-time`, la.level, la.name FROM liveAlarms la LIMIT ? OFFSET ?;; time=1 ms;
ERROR 2023-02-08T10:56:12,676 (org.scada_lts.dao.alarms.PlcAlarmsDAO.getLiveAlarms:101) - PreparedStatementCallback; bad SQL grammar [SELECT la.id, la.`activation-time`, la.`inactivation-time`, la.level, la.name FROM liveAlarms la LIMIT ? OFFSET ?;]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'scadalts.liveAlarms' doesn't exist 
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT la.id, la.`activation-time`, la.`inactivation-time`, la.level, la.name FROM liveAlarms la LIMIT ? OFFSET ?;]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'scadalts.liveAlarms' doesn't exist
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:237) ~[spring-jdbc-3.2.17.RELEASE.jar:3.2.17.RELEASE]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-3.2.17.RELEASE.jar:3.2.17.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) ~[spring-jdbc-3.2.17.RELEASE.jar:3.2.17.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:639) ~[spring-jdbc-3.2.17.RELEASE.jar:3.2.17.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:668) ~[spring-jdbc-3.2.17.RELEASE.jar:3.2.17.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:676) ~[spring-jdbc-3.2.17.RELEASE.jar:3.2.17.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:716) ~[spring-jdbc-3.2.17.RELEASE.jar:3.2.17.RELEASE]
        at org.scada_lts.dao.alarms.PlcAlarmsDAO.getLiveAlarms(PlcAlarmsDAO.java:97) [classes/:?]
        at org.scada_lts.dao.alarms.PlcAlarmsService.getLiveAlarms(PlcAlarmsService.java:47) [classes/:?]
        at org.scada_lts.web.mvc.api.alarms.PlcAlarmsAPI.getLiveAlarms(PlcAlarmsAPI.java:129) [classes/:?]
        at sun.reflect.GeneratedMethodAccessor161.invoke(Unknown Source) ~[?:?]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_275]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_275]
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) [spring-web-4.3.16.RELEASE.jar:4.3.16.RELEASE]
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) [spring-web-4.3.16.RELEASE.jar:4.3.16.RELEASE]
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE]
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE]
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE]
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE]
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE]
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE]
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE]
        at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:624) [servlet-api.jar:?]
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) [spring-webmvc-4.3.16.RELEASE.jar:4.3.16.RELEASE]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) [servlet-api.jar:?]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) [catalina.jar:7.0.85]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.85]
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) [tomcat7-websocket.jar:7.0.85]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.85]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.85]
        at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:201) [javamelody-1.59.0.jar:?]
        at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:178) [javamelody-1.59.0.jar:?]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.85]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.85]
        at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:162) [javamelody-1.59.0.jar:?]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.85]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.85]
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) [spring-web-4.3.16.RELEASE.jar:4.3.16.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.16.RELEASE.jar:4.3.16.RELEASE]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.85]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.85]
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219) [catalina.jar:7.0.85]
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110) [catalina.jar:7.0.85]
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:498) [catalina.jar:7.0.85]
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169) [catalina.jar:7.0.85]
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) [catalina.jar:7.0.85]
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:962) [catalina.jar:7.0.85]
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) [catalina.jar:7.0.85]
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445) [catalina.jar:7.0.85]
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1115) [tomcat-coyote.jar:7.0.85]
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637) [tomcat-coyote.jar:7.0.85]
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318) [tomcat-coyote.jar:7.0.85]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_275]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_275]
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-coyote.jar:7.0.85]
        at java.lang.Thread.run(Thread.java:748) [?:1.8.0_275]
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'scadalts.liveAlarms' doesn't exist
        at sun.reflect.GeneratedConstructorAccessor92.newInstance(Unknown Source) ~[?:?]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_275]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_275]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
        at com.mysql.jdbc.Util.getInstance(Util.java:387) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962) ~[mysql-connector-java-5.1.38-bin.jar:5.1.38]
        at sun.reflect.GeneratedMethodAccessor27.invoke(Unknown Source) ~[?:?]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_275]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_275]
        at org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$StatementProxy.invoke(AbstractQueryReport.java:212) ~[tomcat-jdbc.jar:?]
        at com.sun.proxy.$Proxy26.executeQuery(Unknown Source) ~[?:?]
        at sun.reflect.GeneratedMethodAccessor27.invoke(Unknown Source) ~[?:?]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_275]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_275]
        at org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$StatementProxy.invoke(AbstractQueryReport.java:212) ~[tomcat-jdbc.jar:?]
        at com.sun.proxy.$Proxy26.executeQuery(Unknown Source) ~[?:?]
        at sun.reflect.GeneratedMethodAccessor27.invoke(Unknown Source) ~[?:?]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_275]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_275]
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[tomcat-jdbc.jar:?]
        at com.sun.proxy.$Proxy26.executeQuery(Unknown Source) ~[?:?]
        at sun.reflect.GeneratedMethodAccessor27.invoke(Unknown Source) ~[?:?]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_275]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_275]
        at net.bull.javamelody.JdbcWrapper.doExecute(JdbcWrapper.java:404) ~[javamelody-1.59.0.jar:?]
        at net.bull.javamelody.JdbcWrapper$StatementInvocationHandler.invoke(JdbcWrapper.java:129) ~[javamelody-1.59.0.jar:?]
        at net.bull.javamelody.JdbcWrapper$DelegatingInvocationHandler.invoke(JdbcWrapper.java:286) ~[javamelody-1.59.0.jar:?]
        at com.sun.proxy.$Proxy27.executeQuery(Unknown Source) ~[?:?]
        at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:646) ~[spring-jdbc-3.2.17.RELEASE.jar:3.2.17.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589) ~[spring-jdbc-3.2.17.RELEASE.jar:3.2.17.RELEASE]

These are the actual initialized tables:

mysql> show TABLES;
+--------------------------------+
| Tables_in_scadalts             |
+--------------------------------+
| category_views_hierarchy       |
| compoundEventDetectors         |
| dataPointUsers                 |
| dataPointUsersProfiles         |
| dataPoints                     |
| dataSourceUsers                |
| dataSourceUsersProfiles        |
| dataSources                    |
| eventDetectorTemplates         |
| eventHandlers                  |
| events                         |
| flexProjects                   |
| mailingListInactive            |
| mailingListMembers             |
| mailingLists                   |
| maintenanceEvents              |
| mangoViewUsers                 |
| mangoViews                     |
| pointEventDetectors            |
| pointHierarchy                 |
| pointLinks                     |
| pointValueAnnotations          |
| pointValues                    |
| publishers                     |
| reportInstanceData             |
| reportInstanceDataAnnotations  |
| reportInstanceEvents           |
| reportInstancePoints           |
| reportInstanceUserComments     |
| reportInstances                |
| reports                        |
| scheduledEvents                |
| schema_version                 |
| scripts                        |
| systemSettings                 |
| templatesDetectors             |
| userComments                   |
| userEvents                     |
| users                          |
| usersProfiles                  |
| usersUsersProfiles             |
| viewUsersProfiles              |
| views_category_views_hierarchy |
| watchListPoints                |
| watchListUsers                 |
| watchListUsersProfiles         |
| watchLists                     |
+--------------------------------+
47 rows in set (0.00 sec)

Any suggestions to solve this issue?

[Limraj]

Hi @sfl0r3nz05,

1. What do you have in the schema_version table? Looks like you didn't migrate, so you don't have a view created. It has nothing to do with uploading images.
2. I suppose this System Exception is for a different place in the log, please post the full log. (kamil.jarmusik@softq.pl)
3. Did you use docker-compose to run the application? If so, upload the docker-compose.yml configuration file you used.

Best regards, Kamil Jarmusik

[sfl0r3nz05]

Thanks @Limraj,

Response to: What do you have in the schema_version table? Looks like you didn't migrate, so you don't have a view created.

mysql> SELECT * FROM schema_version;
+----------------+---------+-----------------------+------+---------------------------------------------------------------+----------+--------------+---------------------+----------------+---------+
| installed_rank | version | description           | type | script                                                        | checksum | installed_by | installed_on        | execution_time | success |
+----------------+---------+-----------------------+------+---------------------------------------------------------------+----------+--------------+---------------------+----------------+---------+
|              1 | 1       | BaseVersion           | JDBC | org.scada_lts.dao.migration.mysql.V1__BaseVersion             |     NULL | root         | 2021-04-14 17:54:22 |          48385 |       1 |
|              2 | 1.1     | ViewsHierarchy        | JDBC | org.scada_lts.dao.migration.mysql.V1_1__ViewsHierarchy        |     NULL | root         | 2021-04-14 17:54:23 |           1052 |       1 |
|              3 | 1.2     | SetViewSizeProperties | JDBC | org.scada_lts.dao.migration.mysql.V1_2__SetViewSizeProperties |     NULL | root         | 2021-04-14 17:54:23 |            615 |       1 |
|              4 | 1.3     | SetXidPointHierarchy  | JDBC | org.scada_lts.dao.migration.mysql.V1_3__SetXidPointHierarchy  |     NULL | root         | 2021-04-14 17:54:30 |             92 |       0 |
+----------------+---------+-----------------------+------+---------------------------------------------------------------+----------+--------------+---------------------+----------------+---------+
4 rows in set (0.00 sec)

Response to: I suppose this System Exception is for a different place in the log, please post the full log. Please, find the log uploaded as a file: log.txt.

Response to: Did you use docker-compose to run the application? If so, upload the docker-compose.yml configuration file you used. I'm importing from GNS3, so I have added the images directly on the software network simulator.

[sfl0r3nz05]

[Update]

I am getting same error when I have updated the latest version of ScadaLTS.

• Some configurations added in GNS3:

[Limraj]

1. We can see that for some reason you did not migrate, if the database was read and this is the first run of the container, then probably someone or another process interrupted the migration process. Without completing the migration process, Scada-LTS will not function properly. You can try to stop tomcat with Scada-LTS, delete the last entry in this table, and restart to migrate again, but it can fail for many reasons. Probably the best way is to start with a clean base and configure everything from scratch. It is essential that it undergoes migration.

2. I'm importing from GNS3, so I have added the images directly on the software network simulator. So running tomcat with Scada is handled by some external process related to this environment? Because I don't understand what that means here. I mean how do you use this docker and Scada-LTS application in this configuration of yours. Also, how did you install the database server? These things can be handled with docker-compose. Which instruction to configure and run the application did you use? In addition, what is worth remembering is that if you want to use docker in production for database, you need to configure volumes so that data from the database is permanently stored, even if you remove the container. In the future version 2.7.5 we migrate to mysql 8.0.32 (from 5.7) and configure the default volumes in the docker-compose.yml file. docker-compose.yml from 2.7.5 docker volumes documentation

3. If you use the latest tag, you also need to remember to update in the local environment, if latest changes and you don't execute the command: docker-compose pull scadalts or docker pull scadalts/scadalts:latest It is by using the latest tag that you will have an old version of Scada. You still haven't answered whether you are using docker-compose to launch the application or docker itself.

4. This entry in the logs also raises concern: INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: /usr/java/packages/lib/amd64:/usr/lib/x86_64-linux-gnu/jni:/lib/x86_64-linux-gnu:/usr/lib/x86_64-linux-gnu:/usr/lib/jni:/lib:/usr/lib But it should work anyway: What does "The APR based Apache Tomcat Native library was not found" mean?

[sfl0r3nz05]

[Update] Thanks again @Limraj,

Previously I have used the version with docker compose properly. Now I need to add the container in the GNS3 network simulator which also uses Docker containers, but the deployment of these is without using docker compose.

I am trying to use the mysql server containing the scadalts container itself, i.e. without using a dedicated database container. Is this possible?

[Limraj]

@sfl0r3nz05 You have to lead to a situation in which you will pass all migrations. For this to succeed, Scada-LTS must have access to the mysql server, and this process cannot be interrupted.

By default, Scada-LTS expects a mysql server with parameters: https://github.com/SCADA-LTS/Scada-LTS/blob/develop/docker/config/context.xml

Repeat all the steps you have taken so far, making sure that the migration is successful. The earlier logs show that Scada had access to the database, but something interrupted the process.

The most important thing is to make the application work for you. Your setup is quite unusual, so it will be a learning experience for us too if we find that we need to go deeper into this setup when you fail with your migrations. At the moment we don't have an environment like yours set up and I can only rely on what you present here.

[sfl0r3nz05]

Thanks again @Limraj,

I'll let the issue open until figure out a solution. I will try to deploy a MySQL container on GNS3 with the information included in the docker-compose so that the migrations be performed correctly.

We will be using the Scada-LTS integration in these two projects: ICSsVirtualForCiberSec and OT-NWbasedOnGNS3

FYI: I have reviewed the state of the art and found a quite similar work that integrates a ScadaBR VM into GNS3: Development of an Open-Source Testbed Based on the Modbus Protocol for Cybersecurity Analysis of Nuclear Power Plants.

[Limraj]

@sfl0r3nz05 Do exactly as you did before. Again, from what you sent, it looks like the database was configured correctly, because Scada could write something in it, so repeat what you did before. If the migration doesn't go through the second time, they'll send me the logs from that first run.

The first run means the first run of the application on a clean database, without tables. You need to remove the old scadalts database and add a new one, then start tomcat with the application.

Use the latest stable version, i.e. the tag: scadalts/scadalts:v2.6.18

Here you have a list of all available tags: https://hub.docker.com/r/scadalts/scadalts/tags

[sfl0r3nz05]

Thanks @Limraj,

Steps followed:

1. Go into the container:

   cserver@daimserver:~$ docker ps
   CONTAINER ID        IMAGE                         COMMAND                  CREATED             STATUS              PORTS               NAMES
   d9f6ecf9e3e3        scadalts/scadalts:latest      "/gns3/init.sh /root…"   5 minutes ago       Up 5 minutes                            stupefied_mccarthy
   8dba4a457182        sflorenz05/open-plc:v0.3      "/gns3/init.sh ./sta…"   12 minutes ago      Up 11 minutes                           heuristic_kapitsa
   e23bb11c4223        gitlab/gitlab-runner:latest   "/usr/bin/dumb-init …"   12 months ago       Up 3 weeks                              gitlab-runner

2. Drop the database:

   mysql> DROP DATABASE scadalts;
   Query OK, 47 rows affected (10.66 sec)

3. Verification:

4. Stop ScadaLTS container:

   cserver@daimserver:~$ docker ps
   CONTAINER ID        IMAGE                         COMMAND                  CREATED             STATUS              PORTS               NAMES
   8dba4a457182        sflorenz05/open-plc:v0.3      "/gns3/init.sh ./sta…"   17 minutes ago      Up 17 minutes                           heuristic_kapitsa
   e23bb11c4223        gitlab/gitlab-runner:latest   "/usr/bin/dumb-init …"   12 months ago       Up 3 weeks                              gitlab-runner

5. Restart container:

   cserver@daimserver:~$ docker ps
   CONTAINER ID        IMAGE                         COMMAND                  CREATED              STATUS              PORTS               NAMES
   679ecf189873        scadalts/scadalts:latest      "/gns3/init.sh /root…"   About a minute ago   Up 34 seconds                           vigorous_wescoff
   8dba4a457182        sflorenz05/open-plc:v0.3      "/gns3/init.sh ./sta…"   18 minutes ago       Up 18 minutes                           heuristic_kapitsa
   e23bb11c4223        gitlab/gitlab-runner:latest   "/usr/bin/dumb-init …"   12 months ago        Up 3 weeks                              gitlab-runner

6. Test Database:

7. Collect logs: logs_after_restart.txt

8. Upload image:

9. Log after error: logs_after_error.txt

[Limraj]

Thanks @sfl0r3nz05,

A running container means = running the application. When you have Scada running, you can't delete the database that the application uses.

1. Remove scadalts/scadalts:latest container. docker container rm container_id To make sure that the container has been removed, run the following command: docker container ls --all
2. On database you do: drop scadalts then: create database scadalts
3. You create and run the scadalts/scadalts:latest container. Scada-LTS-on-Docker-tutorial Scada-LTS-docker-compose-tutorial -> You provide logs from this run of container.
4. If there is a problem then try to do it with scadalts/scadalts:v2.6.18. Your latest may be outdated, I don't know if you did what I said before, but if you use this tag, you will have the current stable version.

The problem is related to the fact that the migration process has not been completed, so the database is missing either tables or columns. The application tries to write/read missing tables or columns and this leads to errors in the logs. So we need to focus on making the migration successful. If you upload any logs, then only complete ones.

[sfl0r3nz05]

Thanks @Limraj,

I currently run the database and the ScadaLTS application in the same container. To use step 2 ("On database you do") I will have to run DB and ScadaLTS separately.

Yes, I tried to use version 2.6.18 but as I saw that it does not have a mysql server I went back to the latest one. Now, since I separate the services then I'll use this version.

[Limraj]

How do you log into the server database? In the container sacadalts/scadalts:latest? Show me what commands you use to do this. In my opinion you have mysql server installed on your system, or some other container provides a mysql server. Theoretically it should now be: scadalts/scadalts:latest = scadalts/scadalts:v2.6.18. What errors do you get when you try to use v2.6.18 instead of latest?

Because I understand that you followed these 4 steps with the v2.6.18 tag but Scada did not start because the application could not connect to the database?

We need to know how you installed the mysql server, where you have access to it, otherwise there may be a port conflict. Of course, this is not a problem (you can change the ports for the container), but since you have a server installed, it might be worth trying to use it, if there is a problem, then you will do it as a separate container.

You should see this information yourself, is your version identical? (v2.6.18 build 3789793762)

[sfl0r3nz05]

1. ScadaLTS:latest -> running

   cserver@daimserver:~$ docker ps
   CONTAINER ID        IMAGE                         COMMAND                  CREATED             STATUS              PORTS               NAMES
   679ecf189873        scadalts/scadalts:latest      "/gns3/init.sh /root…"   23 seconds ago      Up 21 seconds                           vigorous_wescoff

2. ScadaLTS:latest -> first logs

This log shows that the MySQL server is started.

cserver@daimserver:~$ docker logs 679ecf189873
 * Starting MySQL database server mysqld                                        No directory, logging in with HOME=/
                                                                         [ OK ]
mysql: [Warning] Using a password on the command line interface can be insecure.
Using CATALINA_BASE:   /opt/scadalts
Using CATALINA_HOME:   /opt/scadalts
Using CATALINA_TMPDIR: /opt/scadalts/temp
Using JRE_HOME:        /usr
Using CLASSPATH:       /opt/scadalts/bin/bootstrap.jar:/opt/scadalts/bin/tomcat-juli.jar
Tomcat started.
Using CATALINA_BASE:   /opt/scadalts
Using CATALINA_HOME:   /opt/scadalts
Using CATALINA_TMPDIR: /opt/scadalts/temp
Using JRE_HOME:        /usr
Using CLASSPATH:       /opt/scadalts/bin/bootstrap.jar:/opt/scadalts/bin/tomcat-juli.jar
Tomcat started.

3. ScadaLTS: v2.6.18

   cserver@daimserver:~$ docker ps
   CONTAINER ID        IMAGE                         COMMAND                  CREATED             STATUS              PORTS               NAMES
   9a5d43b22f61        scadalts/scadalts:v2.6.18     "/gns3/init.sh catal…"   22 minutes ago      Up 6 minutes                            lucid_elbakyan

4. ScadaLTS: v2.6.18 -> first logs

This log does not show that the MySQL server is started.

cserver@daimserver:~$ docker logs 9a5d43b22f61
Using CATALINA_BASE:   /usr/local/tomcat
Using CATALINA_HOME:   /usr/local/tomcat
Using CATALINA_TMPDIR: /usr/local/tomcat/temp
Using JRE_HOME:        /usr/local/openjdk-11
Using CLASSPATH:       /usr/local/tomcat/bin/bootstrap.jar:/usr/local/tomcat/bin/tomcat-juli.jar
Using CATALINA_OPTS:   -Xmx512m -Xms512m
NOTE: Picked up JDK_JAVA_OPTIONS:  --add-opens=java.base/java.lang=ALL-UNNAMED --add-opens=java.base/java.io=ALL-UNNAMED --add-opens=java.base/java.util=ALL-UNNAMED --add-opens=java.base/java.util.concurrent=ALL-UNNAMED --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED

5. I confirm that I don't have a mysql server on the host, that's why I thought that the latest version of ScadaLTS included a MySQL server by default.

6. "Because I understand that you followed these 4 steps with the v2.6.18 tag but Scada did not start because the application could not connect to the database?" Exactly, in the version (v2.6.18) at the moment I can't connect to the DB so I get connection error.

[Limraj]

Interesting...

1. Try to do these 4 steps (specifically 3) on this latest. (The logs you've sent so far don't look like the first run of the application/container, try to do it, this information may be helpful)
2. Check what version you have there.
3. If that doesn't work, or other version, try v2.6.18 and separate these containers.

The success of the migration is crucial for the application to work properly. Unfortunately, I don't have time to look into it right now, but I'll look into it tomorrow.

Best regards, Kamil Jarmusik

[Limraj]

Hi @sfl0r3nz05, very strange as you will see for yourself here: https://hub.docker.com/r/scadalts/scadalts/tags It's the latest and v2.6.18 has an identical digest, so these gates must be identical.

When I don't use docker-compose (which is what we're using now) but docker itself, then when I run this one container, mysql server is not installed, etc. the Scada-LTS just can't connect to the server.

To verify that there is no mysql server in the container, you can log in to it: docker exec -it scadalts_container_id /bin/bash And try execute command: mysql -u root -proot (you will get mysql not found, mysql server is installed with comandline client by default) or ps aux

Then it's your environment that has to do some extra work. Here you write that you have configured mysql server in gns3: https://stackoverflow.com/questions/75124932/import-scadalts-and-mysql-on-gns3

edit

Best regards, Kamil Jarmusik

[sfl0r3nz05]

Hi @Limraj, thank you for your help.

First of all, I found to projects based on ScadaBR (I guess that are old version of ScadaLTS): bitelxux/scadabr and carloskze/scadabr (This second one is an update of the first one). I have been able to test both and they work properly. Hence, I think it might be more appropriate to follow the line of these projects using the v2.6.18 version of ScadaLTS and integrate everything in the same container.

I do not know if you think it is an appropriate idea to work on this project that works properly on my environment and grow from it?

[Limraj]

Scada-LTS is several years of development. During this time, we ran many improvements, new features, and bug fixes. We cannot enter your computer and check what and how you have it configured, and the information you provided was not true. You also didn't want to follow the instructions too much and I couldn't get the information I asked for from you. We are unable to guarantee that in every configuration (yours is unusual), the application will work without additional steps.

To simplify the installation process and reduce it to a minimum number of steps, we introduced configuration with docker-compose. Following the instructions on our wiki, along with docker-compose, you should be able to run our application without much difficulty, although to use it in a production environment you need to configure volumes. From version 2.7.5 we introduce mysql 8.0.32 and the default setup with volumes so it should be simpler.

edit

Nevertheless, we encourage you to follow the project.

Best regards, Kamil Jarmusik

[sfl0r3nz05]

Hi @Limraj, my previous post was just a suggestion but not at all with the intention of minimizing your work. Indeed, we are having the problem for about 1 month (stackoverflow post) and we have tried different tests.

The solution outlined above will allow us to move forward with other project tasks. However, I agree with the points regarding security, stability, and performance that you raise. If you agree, I would like to try to perform the corresponding troubleshooting following your indications.

As I told you before, the ScadaLTS project has been used until now from docker-compose. The problems started when we started to migrate everything to GNS3. Migration is necessary because of the number of network devices we need to add, which is more complex without the abstraction offered by GNS3.

It is quite possible that I am introducing some configuration errors that I have not seen yet. If you agree, return to this step to perform the tests from this point.

BR,

Santiago.

[Limraj]

Let me know what system you are installing this simulator on. Maybe it doesn't take much to make it work with Scada-LTS. I'll let you know what came of it.

[sfl0r3nz05]

Sure, thanks. We commonly run GNS3 on an Ubuntu 20.04 server. To install GNS3 we have documented the steps here.

[Limraj]

Hi @sfl0r3nz05, prepare image Scada-LTS with mysql 8.0: https://hub.docker.com/r/kamiljarmusik/scadalts-2.6.18-mysql-8/tags

Regards, Kamil Jarmusik

[sfl0r3nz05]

Hi @Limraj,

Thank you very much for the effort. Here are the tests applied on the image:

1. We have deployed the image provided on GNS3.

2. We have created the next sample script:

3. We pasted the next inserts:

INSERT INTO scripts (userId, xid, name, script, data) VALUES 
((SELECT id FROM users WHERE username='admin'), 'SC_290169', '', '', (SELECT sc.data FROM scripts sc LIMIT 1)),
((SELECT id FROM users WHERE username='admin'), 'SC_256909', '', '', (SELECT sc.data FROM scripts sc LIMIT 1)),
((SELECT id FROM users WHERE username='admin'), 'SC_831503', '', '', (SELECT sc.data FROM scripts sc LIMIT 1)),
((SELECT id FROM users WHERE username='admin'), 'SC_586810', '', '', (SELECT sc.data FROM scripts sc LIMIT 1)),
((SELECT id FROM users WHERE username='admin'), 'SC_290170', '', '', (SELECT sc.data FROM scripts sc LIMIT 1)),
((SELECT id FROM users WHERE username='admin'), 'SC_256910', '', '', (SELECT sc.data FROM scripts sc LIMIT 1)),
((SELECT id FROM users WHERE username='admin'), 'SC_831504', '', '', (SELECT sc.data FROM scripts sc LIMIT 1)),
((SELECT id FROM users WHERE username='admin'), 'SC_586811', '', '', (SELECT sc.data FROM scripts sc LIMIT 1)),
((SELECT id FROM users WHERE username='admin'), 'SC_290171', '', '', (SELECT sc.data FROM scripts sc LIMIT 1)),
((SELECT id FROM users WHERE username='admin'), 'SC_256911', '', '', (SELECT sc.data FROM scripts sc LIMIT 1)),
((SELECT id FROM users WHERE username='admin'), 'SC_831505', '', '', (SELECT sc.data FROM scripts sc LIMIT 1)),
((SELECT id FROM users WHERE username='admin'), 'SC_586812', '', '', (SELECT sc.data FROM scripts sc LIMIT 1));

3.a The MySQL query reports this error:

4. The import of configuration works, except for the “Incorrect result size” that is caused by the MySQL error above.

5. The Graphical View is excellent. The temperature is correct.

5a. To test the ScadaLTS we have used OpenPLC project sample

6. We change the “setpoint” value to “200”, and openplc monitor also changes.

7. The buttons in graphical view don’t work. We think this is also caused by the MySQL error:

To conclude, everything works well except for the MySQL “INSERT” command. I guess this must be more of an application problem than a problem with the created image.

[Limraj]

3.a The MySQL query reports this error: -> We actually removed the ability to change the database state from the application(SQL tab), for security reasons, you have to log in to the database and execute these commands in the database. This only applies to the SQL tab. Data Source SQL should work as before. 4. The import of configuration works, except for the “Incorrect result size” that is caused by the MySQL error above. -> Please send me the configuration part that leads to this incorrect error. (as json) Will debug this error.

[Limraj]

Alright now I know what it is. You have a script attached to the buttons that you have not imported, you will need to insert them in the database. Generally Script import is fixed from 2.7.4.

[sfl0r3nz05]

Thank you very much @Limraj.

I guess we can close the issue now. When we have our use case closed I will get in touch in case we can give some feedback that might be useful. We will add your input in the project documentation and also in the GNS3 community in case someone has the same problem. Please let me know if we can contribute in any other way.

[Limraj]

Switch to this tag: scadalts/scadalts:v2.6.18-latest-mysql-8

If there are any problems with this image, please let me know. That 'kamiljarmusik/scadalts-2.6.18-mysql-8' tag is not for use or distribution. It will be removed soon.

edit: removed latest, latest is confusing: scadalts/scadalts:v2.6.18-mysql-8

[Limraj]

@sfl0r3nz05 I made a small update of this tag, so to download the current one, you need to use the pull command. I think you can close this topic for now, if something happens, we will reopen or open a new issue.

If you want to use Docker in production and you care about keeping data after removing the container (this does not apply to the image), then also check this: https://docs.docker.com/storage/volumes/

edit: I improved remote database access so that you can connect to a database server from outside the container.

[sfl0r3nz05]

Hi, @Limraj, I am going to leave here a video demonstration of the ScadaLTS that you provide us integrated in our GNS3 environment: https://youtu.be/zfWXR3ZAG7I

[Limraj]

@sfl0r3nz05 What is the purpose of your activity?

[sfl0r3nz05]

@Limraj, I was writing so that you could see how we are integrating your contribution.

• In the short/medium term, we simulate an ICS network on which to perform security tests. Specifically, we will experiment with attacks on Modbus (ScadaLTS - OpenPLC) and OSPF (routing protocol). We will then apply SDN-based countermeasures.

• In the longer term, we will integrate more complex industrial processes on this ICS (agnostic) network, and we will try to repeat the cycle by changing attack tools and using other types of countermeasures.

[Limraj]

Hi @sfl0r3nz05, I have doubts that posting attack instructions is the right approach. A better solution is to provide instructions on how to protect yourself and then demonstrate such an attack, which is then ineffective, or privately direct bugs to software vendors who can prepare appropriate patches.

Regards, Kamil Jarmusik