Transactions in the Store mysql – Stack Overflow in Spanish

Good morning my friends, I hope you can help me in this case:
I have a stored procedure named "sp_salida" that has another stored procedure named "sp_cursorProductoSalida" that executes a cursor. In the latter, there is also another cursor named "sp_cursorProductoSalidaKardex" that runs the iteration of the "sp_cursorProductoSalida" problem at home. What I have is that I use transactions and when testing my script I see that the transaction is only up to second SP is evaluated and in the last "sp_cursorProductoSalidaKardex" is no longer evaluated, although I suggest a mistake that the transactions only arrive there

How can I do it to get to the third SP and if I need to put more nested SPs than it would?

I say how the code is structured:

//SP QUE INICA
CREATE PROCEDURE sp_salida(a,b,c)
MODIFIES SQL DATA
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
SHOW ERRORS LIMIT 1;
RESIGNAL;
ROLLBACK;
END; 
START TRANSACTION;

//ejecuta codigo insert/update
CALL sp_cursorProductoSalida(a,b); //la transaccion reconoce los errores hasta este nivel

COMMIT;
END


//SP sp_cursorProductoSalida
CREATE PROCEDURE sp_cursorProductoSalida (a,b)
BEGIN

//ejecuta un cursor
INICIA cursor

//este store se ejecuta por cada iteraccion (es otro cursor)
CALL sp_cursorProductoSalidaKardex(a); // la transaccion ya no reconoce el error en este nivel

CLOSE cursor


END

I hope it is understood, and you can help me, I do not put the code the way it is because it is big. I want you to tell me how it is done in this case so that transactions in MySQL detect errors when more deals are nested.

Thanks, people.

mysql 5.6 – UPDATE Trigger Causes Deadlock

MySQL version: 5.6

I have a trigger for a table that has a number of conditions. However, the trigger does a few selections above. I get deadlocks on some.

DELIMITER //
DROP TRIGGER IF EXISTS pieces_after_update;

CREATE TRIGGER pieces_after_update
AFTER UPDATE
   ON pieces FOR EACH ROW
BEGIN
    SET @COMPANYID = (SELECT company_id FROM skus where id = NEW.sku_id);
    SET @CLOSINGSTOCK = (SELECT closing_stock FROM skus_warehouses WHERE sku_id=NEW.sku_id AND warehouse_id=NEW.warehouse_id ORDER BY ID DESC LIMIT 1);
    SET @CLOSINGPENDINGSTOCK = (SELECT closing_pending_stock FROM skus_warehouses WHERE sku_id=NEW.sku_id AND warehouse_id=NEW.warehouse_id ORDER BY ID DESC LIMIT 1);
    IF (@CLOSINGSTOCK IS NULL)
    THEN
        SET @CLOSINGSTOCK=0;
    END IF;

    ##movement from putaway_pending to putaway_done for good stock
    IF (NEW.stock_status_id=4 AND NEW.is_bad_stock=0 AND OLD.stock_status_id=3) 
    THEN        
        INSERT INTO skus_warehouses(sku_id, warehouse_id, company_id, comment, stock_changed_by, closing_stock, closing_pending_stock)
        VALUES(NEW.sku_id, NEW.warehouse_id, @COMPANYID, CONCAT_WS(' - ','Stock Added by putaway of serial no ', NEW.id), 1, @CLOSINGSTOCK+1, @CLOSINGPENDINGSTOCK); 
    END IF; 

    ##movement from putaway_done to blocked for an order/outward entity
    IF(NEW.stock_status_id=5  AND NEW.is_bad_stock=0 AND OLD.stock_status_id=4)
    THEN
        INSERT INTO skus_warehouses(sku_id, warehouse_id, company_id, comment, stock_changed_by, closing_stock, closing_pending_stock)
        VALUES(NEW.sku_id, NEW.warehouse_id, @COMPANYID, CONCAT_WS(' - ','Stock blocked for serial no ', NEW.id, ' for ', NEW.outward_entity_type, ' id ', NEW.outward_entity_id), -1, @CLOSINGSTOCK-1, @CLOSINGPENDINGSTOCK); 
    END  IF;

    ##movement from BLOCKED to PUTAWAY_DONE for an order/outward entity. Unblocking case, needs adjustment entry in SkuW
    IF(NEW.stock_status_id=4  AND OLD.stock_status_id=5 AND NEW.is_bad_stock=0)
    THEN        
        INSERT INTO skus_warehouses(sku_id, warehouse_id, company_id, comment, stock_changed_by, closing_stock, closing_pending_stock)
        VALUES(NEW.sku_id, NEW.warehouse_id, @COMPANYID, CONCAT_WS(' - ','Stock UNblocked for serial no ', NEW.id, ' for ', NEW.outward_entity_type, ' id ', NEW.outward_entity_id), 1, @CLOSINGSTOCK+1, @CLOSINGPENDINGSTOCK); 
    END  IF;  

    ##movement from PUTAWAY_DONE to PICKED (missed BLOCKED) for an order/outward entity
    IF(NEW.stock_status_id=6  AND NEW.is_bad_stock=0 AND OLD.stock_status_id=4)
    THEN
        INSERT INTO skus_warehouses(sku_id, warehouse_id, company_id, comment, stock_changed_by, closing_stock, closing_pending_stock)
        VALUES(NEW.sku_id, NEW.warehouse_id, @COMPANYID, CONCAT_WS(' - ','Stock blocked & picked for serial no ', NEW.id, ' for ', NEW.outward_entity_type, ' id ', NEW.outward_entity_id), -1, @CLOSINGSTOCK-1, @CLOSINGPENDINGSTOCK); 
    END  IF;

    ##movement from PICKED to PUTAWAY_DONE for an order/outward entity. Unpicking case, needs adjustment entry in SkuW
    IF(NEW.stock_status_id=4  AND OLD.stock_status_id=6 AND NEW.is_bad_stock=0)
    THEN        
        INSERT INTO skus_warehouses(sku_id, warehouse_id, company_id, comment, stock_changed_by, closing_stock, closing_pending_stock)
        VALUES(NEW.sku_id, NEW.warehouse_id, @COMPANYID, CONCAT_WS(' - ','Stock Unpicked & Unblocked for serial no ', NEW.id, ' for ', OLD.outward_entity_type, ' id ', OLD.outward_entity_id), 1, @CLOSINGSTOCK+1, @CLOSINGPENDINGSTOCK); 
    END  IF; 

    ##movement from PUTAWAY_DONE/good_stock to PUTAWAY_PENDING/bad_stock
    IF(NEW.is_bad_stock=1 AND OLD.stock_status_id=4 AND NEW.stock_status_id=3)
    THEN
        INSERT INTO skus_warehouses(sku_id, warehouse_id, company_id, comment, stock_changed_by, closing_stock, closing_pending_stock)
        VALUES(NEW.sku_id, NEW.warehouse_id, @COMPANYID, CONCAT_WS(' - ','Stock marked as BADSTOCK for serial no ', NEW.id), -1,@CLOSINGSTOCK-1, @CLOSINGPENDINGSTOCK);
    END IF;

    ##movement from PUTAWAY_DONE to INVALIDATED
    IF(OLD.stock_status_id=4 AND NEW.stock_status_id=10)
    THEN
        INSERT INTO skus_warehouses(sku_id, warehouse_id, company_id, comment, stock_changed_by, closing_stock, closing_pending_stock)
        VALUES(NEW.sku_id, NEW.warehouse_id, @COMPANYID, CONCAT_WS(' - ','Invalidated serial no ', NEW.id), -1,@CLOSINGSTOCK-1, @CLOSINGPENDINGSTOCK);
    END IF;

END; //

I know that I get deadlocks on the trigger because I found this SHOW INNODB ENGINE STATUS

*** (1) TRANSACTION:
TRANSACTION 635520869854, ACTIVE 0 sec fetching rows
mysql tables in use 5, locked 3
LOCK WAIT 1625 lock struct(s), heap size 177704, 7983 row lock(s), undo log entries 1
MySQL thread id 2085967891, OS thread handle 0x2b5e2d4d1700, query id 503500721288 10.0.10.59 primary_db_user Creating sort index
SET @CLOSINGSTOCK = (SELECT closing_stock FROM skus_warehouses WHERE sku_id=NEW.sku_id AND warehouse_id=NEW.warehouse_id ORDER BY ID DESC LIMIT 1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 139169 page no 7248658 n bits 216 index `PRIMARY` of table `primary_db`.`skus_warehouses` trx id 635520869854 lock_mode X locks rec but not gap waiting
Record lock, heap no 121 PHYSICAL RECORD: n_fields 14; compact format; info bits 0

The table skus_warehouses has over 100 million records but good indexing. All subsequent queries (which appear to be blocking) are completed in 0.5 to 1 second.

SET @CLOSINGSTOCK = (SELECT closing_stock FROM skus_warehouses WHERE sku_id=NEW.sku_id AND warehouse_id=NEW.warehouse_id ORDER BY ID DESC LIMIT 1)

mysql – storing geographic points in the controller, errors in Ajax

Enter image description here

I have some points on the map that are loaded by an Ajax request.

In my controller, I have the following:

public function recogerForm(Request $request)
{

    $marker = new Marker;
    $marker->name = $request->nombre;
    $marker->address = $request->direccion;

    $marker->idG = $request->idG;

    $pos = strpos($request->LatLng, ',');
    $latitud = substr($request->LatLng, 1, $pos-1);
    $longitud = substr($request->LatLng, $pos+1,strlen($request->LatLng));

    $marker->lat = $latitud;
    $marker->lng = $longitud;
    $marker->idMarker = '1';//$request->id;

    $marker->save();

    return $request;
}

On my route I have the following:

Route::post('/bus','MapaProcesarController@recogerForm');

In my js:

 function save_marker(marker, place, i) {
//Save new marker using jQuery Ajax
var mLatLang = marker.getPosition().toUrlValue(); //get marker position
var myData = { id: i, name: place.name, address: place.vicinity, latlong: mLatLang, idG: place.id }; //post variables
console.log(myData);
$.ajax({
  type: "POST",
  url: "/bus",
  data: myData,
  headers: { 'X-CSRF-TOKEN': $('meta(name="csrf-token")').attr('content') },
  success: function (data) {

    var url = "/guardar";
    $form = $('
') $form.append(''); $form.append(''); $form.append(''); $form.append(''); $form.append(''); $form.append(''); $form.append('
'); $('.preForm').append($form); $(".nombre" + data.id).val(data.name); $(".direccion" + data.id).val(data.address); $(".LatLng" + data.id).val(data.latlong); $(".idG" + data.id).val(data.idG); $(".idMarker" + data.id).val(data.id); //console.log(myData); }, error: function (xhr, ajaxOptions, thrownError) { alert(thrownError); //throw any errors } });

If I try to insert into my database, Ajax will make a mistake if I remove the code from the database insert and just leave it

return request

It does not make me a mistake. Why can not I save all the points in my database? But if I do not save it, will not I have any problems?

Xampp error: MySQL shut down unexpectedly. Please help me to fix this error. I have tried many options, but I can not fix it

Here is my log:

2019-09-22 10:15:37 0 (Note) InnoDB: Mutexes and rw_locks use Windows interlocked functions
2019-09-22 10:15:37 0 (Note) InnoDB: Uses event mutexes
2019-09-22 10:15:37 0 (Note) InnoDB: Compressed tables use zlib 1.2.11
2019-09-22 10:15:37 0 (Note) InnoDB: Number of pools: 1
2019-09-22 10:15:37 0 (Note) InnoDB: Using SSE2 crc32 instructions
2019-09-22 10:15:38 0 (Note) InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2019-09-22 10:15:38 0 (Note) InnoDB: Completed initialization of buffer pool
2019-09-22 10:15:38 0 (Note) InnoDB: 128 out of 128 rollback segments are active.
2019-09-22 10:15:38 0 (Note) InnoDB: Creating shared tablespace for temporary tables
2019-09-22 10:15:38 0 (Note) InnoDB: Setting file 'C:xamppmysqldataibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-09-22 10:15:38 0 (Note) InnoDB: File 'C:xamppmysqldataibtmp1' size is now 12 MB.
2019-09-22 10:15:38 0 (Note) InnoDB: Waiting for purge to start
2019-09-22 10:15:38 0 (Note) InnoDB: 10.3.16 started; log sequence number 1604656; transaction id 9
2019-09-22 10:15:38 0 (Note) InnoDB: Loading buffer pool(s) from C:xamppmysqldataib_buffer_pool
2019-09-22 10:15:38 0 (Note) Plugin 'FEEDBACK' is disabled.
2019-09-22 10:15:38 0 (Note) Server socket created on IP: '::'.

mysql – Are there any tools that support sending, validating, approving, and running database queries?

We are a very small e-commerce company. More often than not, data in our MySQL database must be corrected manually by executing SQL queries. In our setup is 1 database administrator. He is currently overwhelmed with the manual execution of queries and must be available every day for a portion of the day to run queries.

I'm looking for a database query management tool that can:

  1. Allows an end user (sender) to initiate a database query execution request
  2. Allows a DBA (auditor) to audit a database query and approve / reject a query
  3. Executes an approved query in the selected database and e-mail the results to the sender and reviewer.

A very simple tool can be created in-house, but the scope of the engineering team is not available.

mysql – I need to remove duplicate values. SQL left join

The display in the format you want is not possible (without much extra effort and the assumption that there are always as many locations as job categories).

However, with GROUP_CONCAT, you can return one row per user name containing all the information you are looking for, regardless of the number of job categories and locations.

SELECT
users.id,
users.username,
GROUP_CONCAT(job.category) AS job_categories,
GROUP_CONCAT(locations.location) AS locations
FROM users
INNER JOIN job ON users.username = job.username
INNER JOIN locations ON job.username = locations.username
GROUP BY users.id

The following results are returned:

5   Don   Fashion/Design/Beauty,Eng,Arts/Audio/Video Technology        Mr,Colombo,gm

mysql – Aggregates non-identical row values

Is there a way to aggregate sales and other columns based on different row values. I have a column with different line values
for example:

Product name state Sales1 Sales 2
AA1            CA    12     11
AA2            AL    11     15
AA3            NY    13     25

I want to group the three product names and call them AA and show the pivot sales. and leave the state and the other column as it is.

mysql – Unexpected slow queries

Use of LEMP on popular virtual cloud service servers with MyISAM tables.

The problem is that sometimes once in a matter of hours, simple MySQL queries (choose from, from where) run for several seconds (5 to 9) instead of milliseconds (.002) as in other times.

MySQL slow.log has these records:


Polling time: 5.369666 Blocking time: 0.000120 …

error.log is empty and there is nothing special in other system logs

Such slow queries are sent sequentially to multiple databases and tables, so the value for table lock and lock_time in slow.log also does not matter.

I activated the general protocol, but there was nothing strange. Just regular queries so that I can not find any correlation. I also did not find out memory or CPU leak. It seems that sometimes MySQL puts a lot of strain on it and slows it down.

What can be the reason for such tips of MySQL slowdown and how can you find out?