database – Integrity constraint violation 1062 ALTER TABLE on setup:upgrade

Magento ver. 2.3.5
mariadb 10.2
PHP 7.2

We are hitting the constraint violation error below when running: php bin/magento setup:upgrade
catalog_product_entity_varchar ran out of space at 2147483647 so we increased the value_id int to Bigint.
We are able to edit and add products again. But now have the constraint error coming up when it reaches schema / update.
What is the best way to correct this, can we change the constraint rule?

SQLSTATE(23000): Integrity constraint violation:
1062 ALTER TABLE causes auto_increment resequencing,
resulting in duplicate entry ‘2147483647’ for key ‘PRIMARY’,
query was: ALTER TABLE catalog_product_entity_varchar MODIFY COLUMN
value_id int(11) NOT NULL AUTO_INCREMENT COMMENT “Value ID”

Thanks so much.
Tony

Is a Non-Clustered index needed when referencing a small table with a Foreign Key

I’m not a DBA, excuse me if this question sounds dumb. Hope I can get help from an experienced DBA, because I’m questioning this for a long time.

Assume there are 2 tables, one table with a lot of records, and one table is just static data with 2 records (for example: a Yes/No table).

As example take these 2 tables:

  • Table_Yes_No (only 2 records: Yes and No. Of course they have an incrementing clustered PK)
  • Table_Form (Very big table with lots of records, and one column has FK to the PK of Table_Yes_No).

Now my question; Is it worth putting an index on the FK (reference from Table_Form to the Table_Yes_No table)? The column will only contain 1 or 2 (but not sorted, because no index). Is it worth indexing such a FK?

An example in SQLFiddle: http://sqlfiddle.com/#!18/51614/3/0

That column will be queried anyway, the question is will the index help the performance or not.

usability – How to best display read and read/write permissions in a table

Our app consists of different sections (here Section 1, Section 2 etc.) where the users can view and edit their data. The user can also choose to give read or read/write access for each of these sections to other users.

We have to design a new page which acts as an overview of the given permissions. As there are multiple sections and potentially hundreds of users, we have decided to use a table. It will have a pagination with 20 users per page. Please note that the user won’t be able to change the permissions on this page, it only serves as an overview (there is a separate process for managing access).

We are trying to find the most efficient way to convey the different types of access and have so far created following design:

A table with the columns User, Section 1, Section 2, Section 3, Section 4. The permission for each section are displayed with three types of icons: a light horizontal line for no permissions, an eye for read and a pen for read/write access. A legend above the table explains the meaning of each icon.

Our worries with this design are that:

  1. The users may overlook the legend above the table
  2. The pen icon is usually associated with write/edit actions, but here it also implies read access, as you can not edit without being able to view. We are not sure if this will be clear for the users
  3. We already use a pen icon as an edit button in other parts of the application, although with a different design. The user may expect some edit action behind the icon and click on it

We have also tried following design but weren’t convinced of it as the icons have no meaning on their own and the page may look too cluttered:

The same concept as the first image, but with different icons: a light horizontal line for no permissions, a semi-filled circle for read and a fully filled circle for read/write access.

Do you have any ideas how we could improve our design?

Is it safe to delete entries for deleted order from inventory_reservation table magento 2?

I have delete test order data using mageplaza delete order plugin. All of the test orders state was processing. So now I am facing issue with salabale qty because of order deletion. To fix this issue i searched on the google then I found when order get placed magento will reserve qty in "inventory_reservation" table against the order placed sku. when i delete the order the from mageplaza delete order plugin "order_cancelled" event is not getting added into the reservation table. Because of this salable qty showing wrong and I am not able to add that item into the cart from store front. So I want delete such entries from the inventory reservation. So Is it safe to delete the records from inventory_reservation table?
Kindly advise
Thanks in advance

How to free space after I drop a column of a table in SQL Server?

You can run a shrink on your database or its individual files, but in general it’s not recommended to do because it’s a heavy operation and generally wasted efforts because the space will likely be re-used by your database anyway which will result in a growth operation which is also not a lightweight operation.

When you drop the column, the space it was previously using on disk will be recycled and re-used by your database as it needs more space anyway. It isn’t just wasted space just because the OS shows it consumed from the disk. SQL Server manages the space it reserves from the disk internally for the very reason of reducing those heavy growth operations I mentioned earlier. So need to worry about shrinking.

You can also find more good information on why shrinking is a wasteful operation here.

Also, last thing to note is that a VARCHAR(MAX) column doesn’t consume the entire ~2 GB it’s capable of maxing out at, for each row, unless you were filling it to the max. It’s approximately equal to 1 byte per character you’re actually using in it, in general (not counting any additional space saving features going on under the hood like compression). So the space savings you might anticipate by dropping it is potentially less than you think.

Mysql MYISAM table keeps crashing

Mysql MYISAM table keeps crashing – Database Administrators Stack Exchange

unity – Why is my table is not rotating aroung its center of mass, after a collision?

Using Unity 2020.

I have a character using CharacterController and Rigidbody, colliding into the end of a long table.

My table has, as a root object, the mesh and the Rigidbody.
It also has, as child objects, 5 box colliders (4 for the feet, and 1 for the table).

The problem is that when the character collides into the table on one end, the whole table translate in the same direction as the character’s travel direction.

But I want the table to move around its center of mass, which would be more realistic.

What could be the problem there?

sql server – Creating Temporal table without adding DateTime columns

I’m an SQL server DBA and new to Temporal tables. We have the following issue:
As part of implementing temporal tables mechanism , there’s a need to add to datetime columns (so DWH will have the ability to investigate only last modified columns).

  1. 1st problem is that our R&D are trying to avoid from altering table structure (adding columns) as it is a sensitive and big table.

  2. 2nd problem is that the original table contains MODIF DATE column.
    This column is being updated at the datetime value of the update transaction beginning time warriors I need the COMMIT time.(gaps are critical)

enter image description here

My question:

Is there a possibility to turn the original table to Temporal one , without adding columns and only by using the CREATION date and MODIF date built-in columns?

Will appreciate any support here!

select – MYSQL get rows not axisting in other table

I have following challenge (yes for me it is a challenge)

I have two tables with 1 identical data column for comparison.

One table “species” is a wide list of species and each data row has one unique id called “suid” add. to its primary id as usual.
The second table “printpipe” is a shopping cart for printouts and each
row has the suid from the species and a unique personal id from the user “guid”.

Now I want to get all rows from “species” having empty field “stockimg” and not “noimage.png” in field “stockimg” AND do not exist in table “printpipe” with same suid AND !!! having memb_guid identical to users guid I have as variable $guid

My query I tried is:
$get_allspecies = $mysqli->query(“SELECT * FROM species WHERE stockimg <> ‘noimage.png’ AND stockimg <> ” AND NOT EXISTS (SELECT * FROM printpipe WHERE printpipe.suid = species.suid AND memb_guid = ‘$guid’) ORDER BY name_sc, name_de”);

But it doesn’t work
What am I doin wrong?

Thanks for any help

Ingo

Calculating number of data blocks of a table

I’m trying to calculate the number of data blocks given the following table

custNr : 8 character customer number. 
vehType : 5 character type of vehicle (e.g., TRUCK, SUV, VAN, )
vehMake : 12 character make of vehicle 
ageRangeCd : customer's age range, this is 1 byte
lastActivityDate : 4 byte date representing the last activity for this row
customer's email :  max of 250 character, average of 36 characters 
customer's streetaddress : max of 150 characters. These average is 40 bytes.

Assumptions:

1. There are 10,000,000 rows and 1 row = 1 customer
2. Data and index blocks are 4096 bytes.
3. Row Id has 2 bytes.
4. email and streetAddress each will use a one byte length. Near the
beginning of the row, there will be an additional 2 byte offset to access the streetAddress that follows the email.

I have so far:
The average row size = 8 + 5 + 12 + 4 + 1 + 36 + 40 + 2 (2 bytes from row ID)+ 2(byte offset for streetAddress and email) = 110
rows per data block = (4096) / 110 = 37 (took the floor)
Therefore No. data blocks = 10,000,000 (size of record) / 37 (rows/db) = 270271

This is what I came up with, however please tell me if the way I calculated the number of data blocks is wrong.

DreamProxies - Cheapest USA Elite Private Proxies 100 Private Proxies 200 Private Proxies 400 Private Proxies 1000 Private Proxies 2000 Private Proxies ExtraProxies.com - Buy Cheap Private Proxies Buy 50 Private Proxies Buy 100 Private Proxies Buy 200 Private Proxies Buy 500 Private Proxies Buy 1000 Private Proxies Buy 2000 Private Proxies ProxiesLive Proxies-free.com New Proxy Lists Every Day Proxies123