mysql – How do I get a record by date from a spreadsheet and update another spreadsheet in Postgresql?

I have two tables. In a table (order_produt) have multiple records of
Date and other table (Transfer_product) also multiple records by date.
The order_product table contains the correct record. I want to update my
Table transfer_product with table order_product by date range.

order_product_table
-------------------------

id | Date | Product_id | value
-------------------------------------------
1 | 2017-07-01 | 2 | 53
2 | 05.08.2017 | 2 | 67
3 | 2017-10-02 | 2 | 83
4 | 2018-01-20 | 5 | 32
5 | 2018-05-01 | 5 | 53
6 | 2008-08-05 | 6 | 67


Transfer_product_table
----------------------------

id | Date | Product_id | value
--------------------------------------------
1 | 01.08.2017 | 2 | 10
2 | 2017-10-06 | 2 | 20
3 | 2017-12-12 | 2 | 31
4 | 2018-06-25 | 5 | 5

Result (Transfer_product_table)
--------------------------------
id | Date | Product_id | value
--------------------------------------------
1 | 01.08.2017 | 2 | 53
2 | 2017-10-06 | 2 | 83
3 | 2017-12-12 | 2 | 83
4 | 2018-06-25 | 5 | 53

I want to update the value by date, as you can see in the results table.

I use Query Partion from, but that's not what I want.

                UPDATE transfer_product_table imp
SET value = sub.value
FROM (SELECT product_id, value
, ROW_NUMBER () OVER (PARTITION BY product_id ORDER BY orderdate DESC) AS Rno
FROM order_product_table
where order date is between & # 39; 2017-07-01 & # 39; and & # 39; 2019-10-31 & # 39;) sub

WO imp.product_id = sub.product_id
and sub.Rno = 1
and Imp.date between & # 39; 2017-07-01 & # 39; and & # 39; 2019-10-31 & # 39;

API – Android + MySQL + PHP

I do not ask for help often, but I'm stuck here.
I need to access my remote database through an Android application.
For this I have selected a MySQL database and a PHP script.

PHP code

setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);

$ sql = "SELECT * FROM score";

if ($ rs = $ conn-> query ($ sql)) {
$ response["data"] = array ();
while ($ row = $ rs-> fetch (PDO :: FETCH_ASSOC)) {
// temporary user array
$ ligne = array ();
$ ligne["key"] = $ row["key"];
$ ligne["date"] = $ row["date"];
$ ligne["heure"] = $ row["heure"];
$ ligne["score"] = $ row["score"];
$ ligne["theme"] = $ row["theme"];

// Move a single line to the final response array
array_push ($ response["data"], $ ligne);
}
}

}
catch (PDOException $ e)
{
echo $ sql. "
". $ e-> getMessage (); $ response["success"] = 1; } // Echo the JSON response echo json_encode ($ response); $ conn = null; ?>

JAVA code

Package com.raz.appliandroid;

import android.content.Context;
import android.os.AsyncTask;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import okhttp3.OkHttpClient;
import okhttp3.Request;
import okhttp3.Response;

public class NoticeActivity extends AppCompatActivity {

OkHttpClient client = new OkHttpClient ();
// public string url = "https://reqres.in/api/users";
public string url = "http: //localhost/appliandroid/get_all_scores.php";

@Override
protected void onCreate (bundle savedInstanceState) {
final Context context = this;
super.onCreate (savedInstanceState);
setContentView (R.layout.activity_notice);

OkHttpHandler okHttpHandler = new OkHttpHandler ();
okHttpHandler.execute (url);
}


public class OkHttpHandler extends AsyncTask {
protected String doInBackground (String ... params) {

Request.Builder builder = new Request.Builder ();
builder.url (params[0]);
Request request = builder.build ();

To attempt {
Response: response = client.newCall (request) .execute ();
return response.body (). string ();

} catch (exception e) {
e.printStackTrace ();
}
return zero;
}

protected void onPostExecute (String s) {
super.onPostExecute (s);
Log.i ("Result API", s);
}
}
}

When I call an API that's available on the Web so developers can test their code and I did not create it, my Android application works well and restores JSON-style data.
In the JAVA code, it is the API "https://reqres.in/api/users" that is included in the comment and works.

When I call my PHP script, it does not work, I always go into the "catch".

However, looking at the results of the two APIs in an Internet Explorer, the structures are similar.

With API "https://reqres.in/api/users"

{"page": 1, "per_page": 3, "total": 12, "total_pages": 4, "data":[{"id":1,"email":"george.bluth@reqres.in","first_name":"George","last_name":"Bluth","avatar":"https://s3.amazonaws.com/uifaces/faces/twitter/calebogden/128.jpg"},{"id":2,"email":"janet.weaver@reqres.in","first_name":"Janet","last_name":"Weaver","avatar":"https://s3.amazonaws.com/uifaces/faces/twitter/josephstein/128.jpg"},{"id":3,"email":"emma.wong@reqres.in","first_name":"Emma","last_name":"Wong","avatar":"https://s3.amazonaws.com/uifaces/faces/twitter/olegpogodaev/128.jpg"}]}

With my PHP API

{"Dates":[{"key":"1","date":"toto","heure":"tata","score":"152","theme":"2"},{"key":"2","date":"toto","heure":"tata","score":"152","theme":"2"},{"key":"3","date":"toto","heure":"tata","score":"1516","theme":"2"},{"key":"4","date":"toto","heure":"tata","score":"5458458","theme":"5"}]}

Can you help me to solve this problem? It has been a few days since I surf the Internet and change my code in all directions, without success.

Thank you in advance.

mysql – How do I get the corrupted table from a log file in hex?

I run a Magento 2 site, MySQL shuts down every day due to some bad data. We do not know the table that causes the problem. This is what is displayed in the error logs:

    Version: & # 39; 5.7.26-0ubuntu0.18.04.1 & # 39; Socket: & # 39; /tmp/tmp.c5OkKsNidi/mysqld.sock' Port: 0 (Ubuntu)
2019-05-19T06: 17: 30.001609Z 0 [Note] InnoDB: Loading buffer pools completed at 190519 6:17:29
2019-05-19T06: 17: 37.299075Z 2 [ERROR] InnoDB: Corruption of the database page on the hard disk or error reading the page [page id: space=28974, page number=1], You may need to restore a backup.
2019-05-19T06: 17: 37.299098Z 2 [Note] InnoDB: Page Dump in ASCII and Hex (16384 bytes):
16384; hex asc u6 q. & # 39; x & # 39; x & # 39; x In case you do not want to get any further In case you do not want to get any further In case you do not want to get any further In case you do not want to get any further In case that you do not want to go any further In case you do not want to get any further In case you do not want to get any further In case you do not want to get any further In case you do not want to get any further In case that you do not want to get any further In case you do not want to get any further u6;
InnoDB: End of page dump

Any ideas? If you do a MySQL restart, do the trick, but I have to do it manually. We need to be sure that the database is fixed, but we can not get the name of the table.

mysql – What can I do if my query keeps adding the same data to me?

The thing is, I want to do a query to calculate how much it costs to realize a product in my C # program, but in the mysql query, I want to sum up the spend on products and use the query:

use bd_production;
select detain.DateIngresoDetInv, inv.PK_codInv, inv.DescriptionInv, detailQuantityDetOrdMedida, detailCodUnMed, med.DescriptionAnMed, detailQuantityDetOrd, sum (cosG.TotalCosGas), sum (work.HoHoHoWork) COUNT (trab.CostoHoHo) from tbl_ordenproduction prod
INNER JOIN tbl_detalleordeproduction deta ON prod.PK_CodOrd = deta.CodOrd
INNER JOIN Tbl_Detail Inventory detainv ON detainv.FechaIngresoDetInv = prod.DateFinalizationOrd
INNER JOIN tbl_Inventory inv ON detainv.CodInv = inv.PK_CodInv
INNER JOIN tbl_unidadmedida med ON med.PK_CodUnMed = deta.CodUnMed
INNER JOIN tbl_receta rec ON inv.PK_CodInv = rec.CodInv
INNER JOIN tbl_costosgastosfabrication cosG ON rec.PK_CodRec = cosG.CodRec
INNER JOIN tbl_personalordenproduction of ON deta.CodOrd = por.CodOrd
INNER JOIN tbl_costoshorahombre trb ON por.PK_CodPerOrd = trab.CodPerOrd
WO month (detainv.FechaIngresoDetInv) = 03 and year (detainv.FechaIngresoDetInv) = 2019
AND deta.CodInv = inv.PK_CodInv
AND inv.PK_CodInv = 9
AND month (FechaCosGas) = ​​03 and year (FechaCosGas) = ​​2019
AND prod.PK_CodOrd = 10; 

The values ​​I give for date, order, and product are used to test if the query works because I will send it from the program. The problem, however, is that the same data is added more than once to lines I've taken because there are only two lines that contain data, but take it as 6. So I do not know what to do so that I do not take the same line several times and add those values ​​to each query. It picks up the correct data, but adds it several times.

These are the tables I use:

Create table TBL_Bodega (
PK_CodBod int,
NameBod varchar (45),
DirectionBod varchar (60),
int status,
PRIMARY KEY (PK_CodBod)
);
TROPICAL TABLE WHEN TBL_Measure Unit EXISES CASCADE;
Create table TBL_UnityMeasure (
PK_CodUnMed int,
DescriptionUnMed varchar (25),
int status,
PRIMARY KEY (PK_CodUnMed)
);

DROP TABLE IF EXISTS TBL_Inventario CASCADE;
Create table TBL_Inventario (
PK_CodInv int,
DescriptionInv varchar (45),
CostoInv swimmer,
CodUnMed int,
int status,
PRIMARY KEY (PK_CodInv),
CONSTRAINT FK_CodUnMed_Inv
FOREIGN KEY (CodUnMed)
Information sources TBL_Measurement Unit (PK_CodUnMed)
);

DROP TABLE IF EXISTS TBL_Classification CASCADE;
Create table TBL_Classification (
PK_CodClas int,
DescriptionVarray Beads (45),
int status,
PRIMARY KEY (PK_CodClas)
);

TROPICAL TABLE, IF EXISTS TBL_Department Inventory CASCADE;
Create a TBL_DetailInventory table (
CodInv int,
Int CodClas,
CodBod int,
DateIncomeDetInv date,
DateVentureDetInv date,
AmountDetInv int,
PRIMARY KEY (CodInv, CodClas, CodBod),
CONSTRAINT FK_CodInv_DetInv
FOREIGN KEY (CodInv)
Information sources TBL_Inventario (PK_CodInv),
CONSTRAINT FK_CodClas_DetInv
FOREIGN KEY (CodClas)
Information sources TBL_Classification (PK_CodClas),
CONSTRAINT FK_CodBod_DetInv
FOREIGN KEYS (CodBod)
Information sources TBL_Bodega (PK_CodBod)
);
DROP TABLE IF EXISTS TBL_AuthorizationOrderProduction CASCADE;
Create table TBL_AuthorizationOrderProduction (
PK_CodAut int,
DescriptionAut varchar (20),
int status,
PRIMARY KEY (PK_CodAut)
);

TROPICAL TABLE WHEN TBL_Avance EXISTS CASCADE;
Create table TBL_Avance (
PK_CodAva int,
DescriptionAva varchar (20),
int status,
PRIMARY KEY (PK_CodAva)
);

TROPICAL TABLE WHEN TBL_Priority CASCADE EXISTS;
Create table TBL_Priority (
PK_CodPrio int,
Description varchar (45),
int status,
PRIMARY KEY (PK_CodPrio)
);

DROP TABLE IF EXISTS TBL_OrderProduction CASCADE;
Create table TBL_OrdenProduction (
PK_CodOrd int,
Start Date Date,
End DateOrd date,
CodAva int,
CodAut int,
CodPrio int,
int status,
PRIMARY KEY (PK_CodOrd),
CONSTRAINT FK_CodPrio_Ord
FOREIGN KEY (CodPrio)
Information sources TBL_Priority (PK_CodPrio),
CONSTRAINT FK_CodAut_Ord
FOREIGN KEY (CodAut)
REFERENCES TBL_AuthorizationOrderProduction (PK_CodAut),
CONSTRAINT FK_CodAva_Ord
OUTER KEY (CodAva)
Information sources TBL_Avance (PK_CodAva)
);

TROPICAL TABLE WHEN EXISTS TBL_DetalleOrdenProduction CASCADE;
Create table TBL_DetalleOrdenProduction (
CodOrd int,
CodInv int,
AmountDetOrd int,
AmountDetOrd float,
CodUnMed int,
PRIMARY KEY (CodOrd, CodInv),
CONSTRAINT FK_CodOrd_DetOrd
FOREIGN KEY (CodOrd)
Information sources TBL_OrderProduction (PK_CodOrd),
CONSTRAINT FK_CodInv_DetOrd
FOREIGN KEY (CodInv)
Information sources TBL_Inventario (PK_CodInv),
CONSTRAINT FK_CodUnMed_DetOrd
FOREIGN KEY (CodUnMed)
Information sources TBL_Measurement Unit (PK_CodUnMed)
);

DROP TABLE IF EXISTS TBL_Employee CASCADE;
Create a TBL_Employee table (
PK_CodEmp int,
NameEmp varchar (45),
Last nameEmp varchar (45),
SalaryEmp float,
NitEmp varchar (9),
DirectionEmp varchar (60),
Int Telephone,
MailEmp varchar (40),
int status,
PRIMARY KEY (PK_CodEmp)
);

DROP TABLE IF EXISTS TBL_PersonalOrderProduction CASCADE;
Create table TBL_PersonalOrdenProduction (
PK_CodPerOrd int,
CodOrd int,
CodEmp int,
Working hours PerOrd int,
AreaPerOrd varchar (20),
int status,
PRIMARY KEY (PK_CodPerOrd),
CONSTRAINT FK_CodOrd_PerOrd
FOREIGN KEY (CodOrd)
Information sources TBL_OrderProduction (PK_CodOrd),
CONSTRAINT FK_CodEmp_PerOrd
FOREIGN KEYS (CodEmp)
Information sources TBL_Employee (PK_CodEmp)
);

TROPICAL TABLE WHEN TBL_Receta CASCADE EXISTS;
Create table TBL_Receta (
PK_CodRec int,
CodInv int, - description of which product is being produced
AmountPersonalRec int,
HoursRequiredRec int,
int status,
PRIMARY KEY (PK_CodRec),
CONSTRAINT FK_CodInv_Rec
FOREIGN KEY (CodInv)
Information sources TBL_Inventario (PK_CodInv)
);

TROPICAL TABLE WHEN TBL_DetalleReceta CASCADE EXISTS;
Create table TBL_DetalleReceta (
CodRec int,
CodUnMed int,
CodInv int,
AmountDetRec int,
PRIMARY KEY (CodRec, CodInv),
CONSTRAINT FK_CodInv_DetRec
FOREIGN KEY (CodInv)
Information sources TBL_Inventario (PK_CodInv),
CONSTRAINT FK_CodRec_DetRec
FOREIGN KEYS (CodRec)
Information sources TBL_Receta (PK_CodRec),
CONSTRAINT FK_CodUnMed_DetRec
FOREIGN KEY (CodUnMed)
Information sources TBL_Measurement Unit (PK_CodUnMed)
);
TROPICAL TABLE, IF EXISTENT TBL_GastosFabrication CASCADE;
Create table TBL_GastosFabricacion (
PK_CodGas int,
DescriptionGas varchar (30),
CodUnMed int,
int status,
PRIMARY KEY (PK_CodGas),
CONSTRAINT FK_CodUnMed_Gas
FOREIGN KEY (CodUnMed)
Information sources TBL_Measurement Unit (PK_CodUnMed)
);

TROPICAL TABLE, IF EXISTS TBL_CostosGastosFabrication CASCADE;
Create table TBL_CostosGastosFabricacion (
PK_CodCosGas int,
CodRec int,
DateCosGas date,
TotalCosGas float,
int status,
PRIMARY KEY (PK_CodCosGas),
CONSTRAINT FK_CodRec_CosGas
FOREIGN KEYS (CodRec)
Information sources TBL_Receta (PK_CodRec)
);
TROPICAL TABLE WHEN TBL_CostosHoraHombre CASCADE EXISTS;
Create Table TBL_CostosHoraHombre (
PK_CodHoHo int,
CodPerOrd int,
CodEmp int,
Hours WorkedHoHo float,

CostHoHo swimmer,
int status,
PRIMARY KEY (PK_CodHoHo),
CONSTRAINT FK_CodPerOrd_HoHo
FOREIGN KEY (CodPerOrd)
Information sources TBL_PersonalOrderProduction (PK_CodPerOrd),
CONSTRAINT FK_CodEmp_HoHo
FOREIGN KEYS (CodEmp)
Information sources TBL_Employee (PK_CodEmp)
);

amazon web services – How do I resolve an unknown_ca error in WSO2IS-5.7 using MySQL RDS as a backstore?

I tried to install an instance under AWS with aws RDS as
My data source via the mysql-connector-java-5.1.45-bin.jar
with jdbc url

jdbc: mysql: //< instance>,< zone>.rds.amazonaws.com: 3306 / carbon_db

I have an exception

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communication Connection Error com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communication Connection Error

The last packet successfully received by the server was 7 milliseconds ago. The last packet successfully sent to the server was 7 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0 (native method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance (NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance (DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance (Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance (Util.java:425)
,
,
,
Trigger: javax.net.ssl.SSLHandshakeException: Fatal warning received: unknown_ca

I tried to run

keytool-importcert -keystore  -Geschäftspass  -noprompt -file rds-combined-ca-bundle.pem

on cacerts.jks in JVM and client-trustore.jks in the WSO2 repository without change

mysql – In what order are statements executed in this use case?

Let's say I'm performing Select * from users at t1, which ends at t3. At time t2 (between t1 and t3), an update is performed for some of the records in the user table, e.g.
User update set address = & # 39; xyz & # 39; where name = & # 39; abc & # 39 ;. Selects in advance the instruction "acquire shared lock for all lines" or it will be recorded for each line
regardless when it starts to read this line?

My understanding is, who (under Select or Update) acquires the common or exclusive lock earlier, will execute it, wait others?

I use MySQL Innodb.

How can I find out the cause of the "out of range for column" error in MySQL

I wrote a console app with me C # on the .Net Core 2.2 Framework with EntityFrameworkCore as ORM. My application performs various tasks that run every 15 minutes. Each task calls an external API server and updates my internal MySQL database (MySQL Version 8.16). If the order fails for any reason, I will receive an email with the error.

I received the error email with the following error

Error message: Value out of range for column & # 39; value & # 39; in line 1

I try to find out what value The column causes this error and tries to find out which query caused this problem.

As it stands, MySQL does not log the invalid queries anywhere, which causes me to wildly search for the problem column.

I searched all columns named "value" in my database with the following query

SELECT table_name, is_nullable, data_type, column_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Table_Schema = & # 39; my_table_name & # 39; and Column_Name = & # 39; Value & # 39;

The above query gave the following results

Table name is_nullable Data type Column type
field_values ​​NO varchar varchar (128)
boolean_fields YES tinyint tinyint (1)
lookup_fields YES text text
numeric_fields YES decimal decimal (30.6)
Text fields YES Long text Long text
time_fields YES datetime datetime (6)
user_tokens YES longtext longtext

I think if the error was caused by a column with the type varchar / longtext, I would get the error "cut text" instead of "out of range". In that sense, I have the following columns as suspects

Table name is_nullable Data type Column type
boolean_fields YES tinyint tinyint (1)
numeric_fields YES decimal decimal (30.6)
time_fields YES datetime datetime (6)

In addition, since my application is written in C # and EntityFrameworkCore is used as the ORM for interacting with the database. I am convinced that both are true boolean_fields.Value and time_fields.Value do not cause the problem. My conclusion is because the type of property is Data Time? in that Time Field Entity model with the property type fool for my BooleanField Entity model.

Given the above assumption, I am limited to an area that could cause my problem.

Table name is_nullable Data type Column type
numeric_fields YES decimal decimal (30.6)

I store all numeric values ​​in numeric_fields.Value Column (ie int, decimal and long)

The API is expected to return numeric data as follows

data_type MinValue MaxValue
"Int", "-2147483648.0000", "2147483647.0000"
"Decimal", "-999999999999.0000", "999999999999.0000"
& # 39; Long, & # 39; 9223372036854775807.0000 & # 39;

I should be able to save all the numbers above decimal (30.6) Column without problem.

How can I tell what could be causing this problem?

mysql – HAPROXY-supported Mariadb master-master replication for automatic failover

We have implemented the master-master setup in maria-db. Master A is used for read / write and Master B for backups and MIS operations. We have set up this setup in such a way that we do not have to transport a slave in case of an error.

Now we want to implement HA PROXY for automatic failover. In the event that Master A fails, the application should automatically switch to Master B.

My question now is that if Master A fails and applications of Master B are deployed. However, if Master A is brought online and is not synchronized with Master B, which HA PROXY will do, applications will immediately connect to Master A, or HA Proxy will do so. Check the synchronization status and have Master A synced with Master B. Applications can then connect to Master A.

Which parameters need to be added to achieve this.

HA PROXY configuration:

Enter image description here

mysql – Advice on the booking scheme

I'm starting to put together a database to book students for a week's activities. A tutor needs to log in and have access to all students in his tutor group. There are 4 activity slots per day (Mon – Fri) from which students can choose. One of these days (different day for each group of years (the first year of high school is referred to in the UK as Year 7)) is booked out for a trip, so that each student has access to a group of grades.

I created the schema as a prototype, but I'm convinced that there is a better way, so any help would be appreciated. I have included the proposed scheme (not the complete data in the tables, only the important details).

Any advice would be helpful

Staff table

| ID | STAFF_NAME |

student table

| ID | STUDENT_NAME |

activity table

| ID | ACTIVITY_NAME |

Year group table

| ID | YEAR_GROUP_NAME |

days table

| ID | DAY_NAME |

session table

| ID | SESSION_NAME |

Tutoring group table

| ID | STAFF_ID: FK | STUDENT_ID: FK | YEAR_GROUP_ID: FK |

booking table

| ID | STUDENT_ID: FK | DAY_ID: FK | SESSION_ID: FK | ACTIVITY_ID: FK