TshepoMokgoatjane
commented
5 months ago Managed to do some digging and confirmed that there was a problem with my tenant table as it wasn't updating the column data type from LocalDate (date) to String (date) on my Amazon RDS via the EC2
=========================
Microsoft Windows [Version 10.0.19045.4412] (c) Microsoft Corporation. All rights reserved.
C:\Users\F5103762>mysql -h awseb-e-sczc8xbr3g-stack-awsebrdsdatabase-szoj5v1zew8q.clsrv15lu23s.us-east-1.rds.amazonaws.com:3306 -u tenantsuser -pin28minutes ebdb 'mysql' is not recognized as an internal or external command, operable program or batch file.
C:\Users\F5103762>mysqlsh MySQL Shell 8.0.27
Copyright (c) 2016, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
MySQL JS > \connect tenantsuser@awseb-e-sczc8xbr3g-stack-awsebrdsdatabase-szoj5v1zew8q.clsrv15lu23s.us-east-1.rds.amazonaws.com:3306
Creating a session to 'tenantsuser@awseb-e-sczc8xbr3g-stack-awsebrdsdatabase-szoj5v1zew8q.clsrv15lu23s.us-east-1.rds.amazonaws.com:3306'
Please provide the password for 'tenantsuser@awseb-e-sczc8xbr3g-stack-awsebrdsdatabase-szoj5v1zew8q.clsrv15lu23s.us-east-1.rds.amazonaws.com:3306':
MySQL Error 2003 (HY000): Can't connect to MySQL server on 'awseb-e-sczc8xbr3g-stack-awsebrdsdatabase-szoj5v1zew8q.clsrv15lu23s.us-east-1.rds.amazonaws.com:3306' (10060)
MySQL JS > \connect tenantsuser@awseb-e-sczc8xbr3g-stack-awsebrdsdatabase-szoj5v1zew8q.clsrv15lu23s.us-east-1.rds.amazonaws.com:330\connect tenantsuser@awseb-e-sczc8xbr3g-stack-awsebrdsdatabase-szoj5v1zew8q.clsrv15lu23s.us-east-1.rds.amazonaws.com:3306
Creating a session to 'tenantsuser@awseb-e-sczc8xbr3g-stack-awsebrdsdatabase-szoj5v1zew8q.clsrv15lu23s.us-east-1.rds.amazonaws.com:3306'
Please provide the password for 'tenantsuser@awseb-e-sczc8xbr3g-stack-awsebrdsdatabase-szoj5v1zew8q.clsrv15lu23s.us-east-1.rds.amazonaws.com:3306':
Save password for 'tenantsuser@awseb-e-sczc8xbr3g-stack-awsebrdsdatabase-szoj5v1zew8q.clsrv15lu23s.us-east-1.rds.amazonaws.com:3306'? [Y]es/[N]o/Ne[v]er (default No): N
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 32789
Server version: 8.0.35 Source distribution
No default schema selected; type \use ebdb.
Fetching table and column names from ebdb for auto-completion... Press ^C to stop.
MySQL awseb-e-sczc8xbr3g-stack-awsebrdsdatabase-szoj5v1zew8q.clsrv15lu23s.us-east-1.rds.amazonaws.com:3306 ssl ebdb SQL > show tables;
+----------------+
| Tables_in_ebdb |
+----------------+
| contact_us |
| tenant |
+----------------+
2 rows in set (0.5295 sec)
MySQL awseb-e-sczc8xbr3g-stack-awsebrdsdatabase-szoj5v1zew8q.clsrv15lu23s.us-east-1.rds.amazonaws.com:3306 ssl ebdb SQL > desc tenant;
+----------------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| created_at | datetime(6) | NO | | NULL | |
| updated_at | datetime(6) | NO | | NULL | |
| alternative_cell_phone_number | varchar(255) | YES | | NULL | |
| cell_phone_number | varchar(255) | YES | | NULL | |
| deposit_paid | bit(1) | NO | | NULL | |
| email | varchar(255) | YES | | NULL | |
| lease_end_date | date | YES | | NULL | |
| lease_start_date | date | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| number_of_tenants_in_unit | int | NO | | NULL | |
| payment_date | date | YES | | NULL | |
| prepaid_electricity_meter_number | varchar(255) | YES | | NULL | |
| rental | double | NO | | NULL | |
| room_number | varchar(255) | YES | | NULL | |
| surname | varchar(255) | YES | | NULL | |
| tenant_behaviour | smallint | YES | | NULL | |
| title | varchar(255) | YES | | NULL | |
| tenant_status | int | NO | | NULL | |
+----------------------------------+--------------+------+-----+---------+----------------+
19 rows in set (0.6015 sec)
=========================
Investigate issue below error from web.stdout.log file in AWS Elastic Beanstalk logs tab:
May 17 10:30:05 ip-172-31-15-212 web[2396228]: 2024-05-17T10:30:05.918Z WARN 2396228 --- [nio-5000-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1292, SQLState: 22001 May 17 10:30:05 ip-172-31-15-212 web[2396228]: 2024-05-17T10:30:05.918Z ERROR 2396228 --- [nio-5000-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper : Data truncation: Incorrect date value: '31st' for column 'payment_date' at row 1 May 17 10:30:05 ip-172-31-15-212 web[2396228]: 2024-05-17T10:30:05.939Z ERROR 2396228 --- [nio-5000-exec-3] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]] with root cause May 17 10:30:05 ip-172-31-15-212 web[2396228]: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect date value: '31st' for column 'payment_date' at row 1 May 17 10:30:05 ip-172-31-15-212 web[2396228]: #011at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104) ~[mysql-connector-j-8.0.31.jar!/:8.0.31]
Production Error:
Talend API Response throws same error: