mysql5.6 – The MySQL server has disappeared: Two APIs, a single database, one API triggers the error, the other does not

I have two bottle-quiet APIs. Both work well.
API1 is only for reading the database, API2 deals with update / insert. Both access the same database.

Problem:
API1 doesn't work every morning and I have to restart it every time (once in the morning). Error is – MySQL server is gone.
API2 always works and does not give an error.

I used to think wait_timeout (8 hours by default) could be the reason why API1 throws this error. But why does API2 work then?
(I am using a class for database operations and I have a connection string in the init method. Also, I am not closing any of the database connections. I will edit them very soon to correct them, and only provide the information here to get the exact information for this error)

Question – Why does only API1 report this error while API2 never?

PS – I asked this about the stackoverflow, but it was recommended to post it here as I never found an answer here that was republished

ubuntu 16.04 – Reinstall MySQL

We are currently trying to reinstall MySQL 5.7 on an Ubuntu 16.04.6 LTS box

However, the installation fails with the following error:

#sudo apt-get install mysql-server mysql-client --fix-broken --fix-missing

(...)
dpkg: error processing package mysql-server (--configure):
 dependency problems — leaving unconfigured
Processing triggers for libc-bin (2.23-0ubuntu11) ...
Processing triggers for ureadahead (0.100.0-19.1) ...
Processing triggers for systemd (229-4ubuntu21.27) ...
Errors were encountered while processing:
 mysql-server-5.7
 mysql-server
E: Sub-process /usr/bin/dpkg returned an error code (1)

We deleted the previous installation.
sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*

Seems to work well, no error messages. Then we check with:

#dpkg-query -l mysql-server
dpkg-query: no packages found matching mysql-server

In addition, we manually removed the remaining folders to ensure that there are no remnants that could affect reinstallation.

rm -rf /etc/mysql /var/lib/mysql

However, any new attempt to install will fail with this error.
Does anyone know what we can do to fix this situation?

Python – insert data from the client side in MySQL via Flask-Sqlalchemy

I am creating a parts list web application (BOM) with Flask as backend. As part of this app, I insert data into BomStructure Table out FpbChildrenAssociation.
Your models:

class FpbChildrenAssociation(db.Model):
    __tablename__ = "fpbChildrenAssociation"
    id = db.Column(db.String(36), default=lambda: str(uuid.uuid4()), primary_key=True)
    ParentPart = db.Column(
        db.String(30), db.ForeignKey("fpb.stockCode"), nullable=False
    )
    baseColorParentPart = db.Column(db.String(2), nullable=True)
    Component = db.Column(
        db.String(30), db.ForeignKey("allRawMaterials.stockCode"), nullable=False,
    )
    qty = db.Column(db.Float, nullable=True)
    __table_args__ = (
        db.UniqueConstraint(
            "ParentPart",
            "baseColorParentPart",
            "Component",
            name="_parentpart_baseColorParentPart_component_uc",
        ),
    )

    def __repr__(self):
        return "".format(self.id)

class BomStructure(db.Model):
    __tablename__ = "BomStructure"
    ParentPart = db.Column(db.String(30), primary_key=True, nullable=False)
    Version = db.Column(db.String(3), primary_key=True, nullable=False, default='   ')
    Release = db.Column(db.String(3), primary_key=True, nullable=False, default='   ')
    Route = db.Column(db.String(1), primary_key=True, nullable=False, default='0')
    SequenceNum = db.Column(db.String(6), primary_key=True, nullable=False)
    Component = db.Column(db.String(30), primary_key=True, nullable=False)
    QtyPer = db.Column(types.DECIMAL(12, 6))
    TimeStamp = db.Column(
        types.TIMESTAMP, default=datetime.datetime.now
    )  # In MSSQl this

    def insert_data(self, data, sequence_start):
        self.ParentPart = data(0)
        self.SequenceNum = str(sequence_start)
        self.Component = data(1)
        self.QtyPer = data(2)

So what happens that the data comes from the frontend to the backend? For example:

{"stockcodeData":(("FPB.20.551.07.02.999.31", "ARM XXX", null, "20LX239", "00"), ("FPB.20.551.07.01.449.61", "ARM XXX", null, "20MX229", "00")),"projectID": '34432'}

The piston code used to process and insert the data BomStructure Table is below:

@app.route("/createbom/fpb-create-update", methods=("POST"))
@login_required
def create_update_bom_fpb():
    """
    Description: Create new BOMs for FSBs and also FPBs and Update the already 
                 created boms.
    POST:
        params:
            projectID : str
            stockcodeData : array of stockcodes need to be inserted in different BOM tables.
        responses:
            success : Bool
            message : str
            error : str         
    """
    client_data = request.get_json(force=True)  # get the ajax data
    id = client_data("projectID")
    fpb_stockcode_data = client_data("stockcodeData")
    try:
        for stockCode_datum in fpb_stockcode_data:
            insert_data_bomstructure_fpb(stockCode_datum, db)
            #adding fpb stockcode and the color for which the bom created to
            # fpbBomCreated table
            new_row = FpbBomCreated() #this table helps in checking what BOM are being created.
            new_row.add_data(
                (
                    (stockCode_datum(0)(0:14) + stockCode_datum(0)(17:)),
                    stockCode_datum(4),
                    stockCode_datum(0)(14:16),
                    id,
                )
            )
            try:
                db.session.add(new_row)
                db.session.commit()
            except:
                db.session.rollback()
        return jsonify(
            {"success": True, "message": "BOM is created.", "error": None,}
        )
    except Exception as error:
        db.session.rollback()
        return jsonify(
            {"success": False, "message": "BOM is not created.", "error": error,}
        )

def insert_data_bomstructure_fpb(stockCode_datum, db):
    """
    This function helps the flask method create_update_bom_fpb to insert data 
    into bomstructure table for fpb stockcode.

    params:
        stockCode_datum : stockcode data for one stockcode
        db : flask_sqlalchemy.SQLAlchemy instance
    returns:
        None
    """
    print(" ------------insert_data_bomstructure_fpb-------------------------")
    # fca is short for FpbChildrenAssociation table
    #Removing color from the stockcode so that it can be used in FpbChildrenAssociation table
    stockCode_fca = stockCode_datum(0)(0:14) + stockCode_datum(0)(17:) 
    baseColor = stockCode_datum(4)
    # data to be added to the bomstructure table from FpbChildrenAssociation table
    data_fca = (
        db.session.query(
            FpbChildrenAssociation.ParentPart,
            FpbChildrenAssociation.Component,
            FpbChildrenAssociation.qty,
        )
        .filter(
            and_(
                FpbChildrenAssociation.ParentPart == stockCode_fca,
                FpbChildrenAssociation.baseColorParentPart == baseColor,
            )
        )
        .all()
    )
    data_fca = list(map(list, data_fca))
    # data which is already present in bomstructure table for the stockcode being added
    data_bomstructure = (
        db.session.query(
            (
                func.concat(
                    func.substr(BomStructure.ParentPart, 1, 14),
                    func.substr(BomStructure.ParentPart, 18),
                )
            ),
            BomStructure.Component,
            cast(BomStructure.QtyPer, db.Float),
        )
        .filter(BomStructure.ParentPart == stockCode_datum(0))
        .all()
    )
    # Question: Why we are getting data from bomstructure table?
    # Answer: Becasue we need to compare the data between the data already in
    #         BomStructure table and the data to be added from the FpbChildrenAssociation
    #         table. Only the difference between the two will be added. Also if 
    #         any row (parentpart and component) is not present in the 
    #         FpbChildrenAssociation table and it is present in bomstructure table 
    #         then it would be deleted from the Bomstructure table as well.
    #          
    #         
    data_bomstructure = list(map(list, data_bomstructure))
    # data which needs to be inserted in the Bomstructure table
    insert_data = list(filter(lambda x: x not in data_bomstructure, data_fca))
    #print(insert_data)
    # data which needs to be deleted from the bomstrucuture table. I have noted
    # above that why data deletion will happen. 
    delete_data = list(filter(lambda x: x not in data_fca, data_bomstructure))
    #print(delete_data)
    # There is a sequence number column in the bomstructure table 
    sequence_start = (
        db.session.query(func.max(cast(BomStructure.SequenceNum, db.Integer)))
        .filter(BomStructure.ParentPart == stockCode_datum(0))
        .scalar()
    )
    if sequence_start is None:
        sequence_start = 10
    else:
        sequence_start = sequence_start + 10

    sequence_delete_list = () # sequence number which are deleted as part of data deletion
    sequence_list = ()  # so that it is not assigned before the assignment
    #loop for deleting the data from delete_data list
    for datum in delete_data:
        q = db.session.query(BomStructure.ParentPart, BomStructure.Component).filter(
            and_(
                BomStructure.ParentPart == stockCode_datum(0),
                BomStructure.Component == datum(1),
            )
        )
        sequence_delete = (
            db.session.query(cast(BomStructure.SequenceNum, db.Integer))
            .filter(
                and_(
                    BomStructure.ParentPart == stockCode_datum(0),
                    BomStructure.Component == datum(1),
                )
            )
            .scalar()
        )
        q.delete(synchronize_session=False)
        db.session.commit()
        sequence_delete_list.append(sequence_delete)
    if len(insert_data) > len(sequence_delete_list):
        sequence_list = (
            i
            for i in range(
                (sequence_start ),
                (sequence_start ) * (len(insert_data) - len(sequence_delete_list) + 1),
                10,
            )
        )
    new_sequence_list = sequence_delete_list + sequence_list
    if not insert_data:
        new_sequence_list = ()
    # loop for inserting data or in some cases updating data from insert_data
    for datum, sequence in zip(insert_data, new_sequence_list):
        datum = list(datum)
        datum(0) = stockCode_datum(0)  # convert back to the stockcode with color
        q = db.session.query(BomStructure.ParentPart, BomStructure.Component).filter(
            and_(
                BomStructure.ParentPart == datum(0), BomStructure.Component == datum(1)
            )
        )

        data_exists = db.session.query(q.exists()).scalar()
        if data_exists:
            q.update(
                {"QtyPer": datum(2),}
            )
        else:
            try:
                add_new_data = BomStructure()
                add_new_data.insert_data(datum, str(sequence).zfill(6))
                db.session.add(add_new_data)
                db.session.commit()
            except exc.IntegrityError:
                print("Already available in the table")
                db.session.rollback()
            except Exception as e:
                print(e)
                db.session.rollback()
    print("------------------------------------------------------------------")

The code above retrieves the data from the client and then inserts it BomStructureTable. In the insertion process if the data already exists in the BomStructureThe table then checks the data for a particular stock code against the data in FpbChildrenAssociation Table and insert only the data that is not in the BomStructureTable. An example of this would be when a new line is added FpbChildrenAssociation Table, but it doesn't exist in the BomStructureIn the table, the above code is then used to compare both data and add / delete data BomStructureTable for a specific stock code so that the data is similar to the data in FpbChildrenAssociation Table for a specific stock code.

The code above works fine, but is a bit slow if large data comes from the client (lots of stock codes) and is also a bit overloaded. I want to optimize the code. Is there also a Python library that can do the above task easily and efficiently?

php – Compare the bank's birth date with a personalized date – mySQL

Hello, I'm showing a report to show customers over 60 years old.
Like me No I have a field idade in bd i had to "discover" it by the date of birth and did it like this:

SELECT TIMESTAMPDIFF(YEAR, data_de_nascimento, CURDATE()) as idade FROM clientes

Now I have to show off selectwhat I just want to show who is older than 60.

I tried to sit down WHERE idade >= 60 however, it didn't work.

I'm trying to compare Date of birth with the year of 1960 (2020 – 1960 = 60), but I want to get that Day and the current month, so that I don’t have to keep changing and doing an "error-free" search, but I can’t …
I try it like this:

WHERE data_de_nascimento <= '1960-Month(data)-Day(now))'

Can someone help me?

MySQL Control Shutdown – Database Administrators Stack Exchange

We run a financial application with MySQL as a database.

For performance reasons, we use memory tables for some of our live tables, in which the data is populated every few milliseconds.

To ensure data consistency in all scenarios (planned and unplanned outages), we must ensure that the data in the storage tables is always preserved so that we can implement shutdown hooks, with the data being written normally in the event of a shutdown, tables and at startup restored.

Unfortunately I can't find a way to do this.

Any suggestions would be appreciated.

We use MySQL ver 5.7

mysql – Woocommerce PHP site that displays a table with orders from a specific category

I would like to create a PHP site for each of my deliverers showing orders for them. I use such a query to display orders:

select
    p.ID as order_id,
    p.post_date,
    max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as Mail,
    max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as Imie,
    max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as Nazwisko,
    max( CASE WHEN pm.meta_key = '_billing_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as KodPocztowy,
    max( CASE WHEN pm.meta_key = '_shipping_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as Imie,
    max( CASE WHEN pm.meta_key = '_shipping_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as Nazwisko,
    max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as Suma,
    max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as Zaplacono,
    ( select group_concat( order_item_name separator '|' ) from wp_woocommerce_order_items where order_id = p.ID ) as Zamowiono
from
    wp_posts as p,
    wp_postmeta as pm
where
    post_type = 'shop_order' and
    p.ID = pm.post_id and
    post_date BETWEEN '2020-01-01' AND '2021-07-08'
group by
    p.ID

And I use this query to display my categories

SELECT wp_terms.term_id,wp_terms.name 
FROM wp_terms 
LEFT JOIN wp_term_taxonomy 
ON wp_terms.term_id = wp_term_taxonomy.term_id 
WHERE wp_term_taxonomy.taxonomy = 'product_cat'

How can I join these two queries to only display orders from certain categories such as clothing, etc.?

mysql – ActiveRecord :: StatementInvalid: Mysql2 :: Error :: TimeoutError – batch overflow

Periodically, when there are a lot of people (~ 60) on the website, I get such errors:

ActiveRecord::ConnectionTimeoutError: could not obtain a connection from the pool within 5.000 seconds (waited 5.000 seconds); all pooled connections were in use
ActiveRecord::StatementInvalid: Mysql2::Error::TimeoutError: Lock wait timeout exceeded; try restarting transaction: UPDATE rooms SET room_profiles_count = COALESCE(`room_profiles_count`, 0) + 1 WHERE rooms.id = 1363
ActiveRecord::StatementInvalid: Mysql2::Error::TimeoutError: Lock wait timeout exceeded; try restarting transaction: DELETE FROM room_profiles WHERE room_profiles.id = 3942

I tried to increase the pool to 100 but still get an error message
My stack: Rails 5, Puma, MySQL

here my configuration puma & database.yml

Please tell me how can I solve this problem?

mysql – How do I update multiple database structures based on one database without losing data?

We have multiple database servers for our customers, each of which has multiple databases (MySQL). All Databases should have the same structure.

Now we have a database that is used for testing in a staging environment. Basically, this is always the most current and should be the structural basis for all databases. This is because when developing a new function, the new tables and columns are created here first.

The client databases were very They also differ from each other because different clients first require different functions and most client database updates were carried out manually. So it's actually a bit messy.

We have a tool that does a query against all databases, but the thing is, I have to be careful not to lose any data that the databases already have.