Chapter 8 404 error on automatic route, 500 error on manual route.

[ezazpi]

Using the GitHub code, after Listing 8.5, I tried URL http://localhost:8072/organization-service/v1/organization/958aa1bf-18dc405c-b84a-b69f04d98d4fwith Postman and got 404 returned: { "timestamp": "2020-09-13T17:52:13.413+0000", "path": "/organization-service/v1/organization/958aa1bf-18dc405c-b84a-b69f04d98d4f", "status": 404, "error": "Not Found", "message": null, "requestId": "4b1374e2-6" } Is the above error caused by the GitHub code supporting automatic and manual routing? When I changed the URL to use manual routing http://localhost:8072/organization/v1/organization/958aa1bf-18dc405c-b84a-b69f04d98d4f, a 500 error was returned: { "timestamp": "2020-09-13T17:55:50.256+0000", "status": 500, "error": "Internal Server Error", "message": "could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet", "path": "/v1/organization/958aa1bf-18dc405c-b84a-b69f04d98d4f" } The latter error is caused by, "org.postgresql.util.PSQLException: ERROR: relation "organizations" does not exist"

[ezazpi]

There may be a couple of issues; one having to do with manual routing (no mapping error) and the other having to do with the organizations table (organizations does not exist). I checked the repository code and the table in the annotation is organizations. I followed the chapter 8 .md mvn clean and docker UP using docker/docker-compose.yml instructions.

licensingservice_1 | 2020-09-13 20:12:20.196 WARN 1 --- [nio-8080-exec-1] o.s.web.servlet.PageNotFound : No mapping for GET /organization/v1/organization/958aa1bf-18dc405c-b84a-b69f04d98d4f gatewayserver_1 | 2020-09-13 20:12:30.680 DEBUG 1 --- [or-http-epoll-3] c.o.gateway.filters.TrackingFilter : tmx-correlation-id found in tracking filter: TEST-CORRELATION-ID. organizationservice_1 | 2020-09-13 20:12:31.083 INFO 1 --- [nio-8081-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet' organizationservice_1 | 2020-09-13 20:12:31.083 INFO 1 --- [nio-8081-exec-1] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet' organizationservice_1 | 2020-09-13 20:12:31.101 INFO 1 --- [nio-8081-exec-1] o.s.web.servlet.DispatcherServlet : Completed initialization in 18 ms organizationservice1 | Hibernate: select organizati0.organization_id as organiza1_00, organizati0_.contact_email as contact_2_00, organizati0_.contact_name as contact_3_00, organizati0_.contact_phone as contact_4_00, organizati0_.name as name5_00 from organizations organizati0 where organizati0.organization_id=? database_1 | 2020-09-13 20:12:31.223 UTC [54] ERROR: relation "organizations" does not exist at character 230 database1 | 2020-09-13 20:12:31.223 UTC [54] STATEMENT: `select organizati0.organization_id as organiza1_00, organizati0_.contact_email as contact_2_00, organizati0_.contact_name as contact_3_00, organizati0_.contact_phone as contact_4_00, organizati0_.name as name5_00 from organizations organizati0 where organizati0.organization_id=$1 organizationservice_1 | 2020-09-13 20:12:31.229 WARN 1 --- [nio-8081-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42P01 organizationservice_1 | 2020-09-13 20:12:31.229 ERROR 1 --- [nio-8081-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR:relation "organizations" does not exist organizationservice_1 | Position: 230 organizationservice_1 | 2020-09-13 20:12:31.242 INFO 1 --- [nio-8081-exec-1] o.h.e.internal.DefaultLoadEventListener : HHH000327:Error performing load command organizationservice_1 | organizationservice_1 |org.hibernate.exception.SQLGrammarException: could not extract ResultSet`

[ihuaylupo]

Please verify that the organization and license tables are created in the PostgreSQL database. In order to use the services you have to create the database tables, you can find the script in the docker's folder.

[ezazpi]

I didn't have any problems with those tables in previous chapters. I thought the database.sql script was automatically processed. Is there something different I need to do that I haven't done previously?

I was going to also mention that with respect to routing, I also used the URL in Figure 8.14, but also got the 404 error. I know that's unrelated to the missing organizations table. Thank you.

[ihuaylupo]

The URL can change depending on the organization and license ID records you have on the database. Remember, the URL is created by using http://localhost:8072/organization-service/v1/organization/${Organization_ID}

The organization_ID is the auto-generated ID of the record in the database.

The database.sql script is not automatically processed instead you have to run it the first time you execute the docker-compose command, in other words, the first time the database container is going to be created.

Please check, the other comments I left on the previous issues, they are also database issues.

[ezazpi]

Hi Illary,

Here's the resolution and someone who knows more about postgresql than me can provide an explanation.  We're using the same postgresql Docker image so one would think things would be consistent.  I am running Docker on Windows 10 with the Windows Subsystem for Linux 2 enabled.  However, this problem wouldn't seem to be related to the host machine.  Either the postgresql image requires a compatible template or the database.sql script requires the LC_COLLATE and LC_CTYPE changes I I outlined below.  Of course, there's no need to separately create the database and the tables - I only did that for troubleshooting.

The database.sql script creates the database as follows:

CREATE DATABASE ostock_dev WITH OWNER = postgres ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' <- change to 'en_US.utf8' LC_CTYPE = 'en_US.UTF-8' <- change to 'en_US.utf8' TABLESPACE = pg_default CONNECTION LIMIT = -1;

The above changes allow the ostock_dev database to be created and the tables to be created in the right place.

The LC_COLLATE and LC_CTYPE values are considered to be incompatible as you can see in the next two create database commands (nice when error messages provide a working solution!):

C:\Users\ezazpi\Documents\IntelliJ\manning-smia\chapter9>echo create database ostock_dev with owner = postgres encoding = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TABLESPACE = pg_default CONNECTION LIMIT = -1; | docker exec -i 2352673e6083 psql -U postgres ERROR:  new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf8)HINT:  Use the same collation as in the template database, or use template0 as template.

C:\Users\ezazpi\Documents\IntelliJ\manning-smia\chapter9>echo create database ostock_dev with owner = postgres encoding = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.UTF-8' TABLESPACE = pg_default CONNECTION LIMIT = -1; | docker exec -i 2352673e6083 psql -U postgres ERROR:  new LC_CTYPE (en_US.UTF-8) is incompatible with the LC_CTYPE of the template database (en_US.utf8)HINT:  Use the same LC_CTYPE as in the template database, or use template0 as template.