Removing empty space from database

Removing empty space from database

Let me explain the situation.
Suppose I create a new table with an auto_increment column, and I make 3 new inserts in it.

Table: id, name
1, john
2, Alex
3, jeet

Then I delete the last record and create a new one.
Now, mysql will remove the entry at record id “3”, but it will not add the new record at id “3”.
The new record will be added at record ID “4”.
So table will look like this:

1, john
2, Alex
4, Sam

I am thinking that if lots of deletes happen in a table, then lots of such empty spaces will get created in the database overtime, which would slow down the “select” queries eventually.
Is that correct?

How can I fix this issue?

I tried exporting the whole table and then rebuilding it.
But the export dump has the record ID field in it.
So even after exporting and importing, the table still looks like this:

1, john
2, alex
4, sam

I am not sure if the null record ID “3” is still present or not…

optimize table is not working either. It takes lot of time, and PHP time_limit expires or mysql goes away while doing that.

Any ideas?
Thanks
SEMrush

 

php – I’ve heard the opinion that my code is making too many database queries

I’ve heard the opinion that my code is making too many database queries. Probably needs to move some code before sending the request, but I don’t know how.

        $this->data('reviews') = Review::query()
        ->orderBy("id", 'desc')
        ->whereNotNull('published_at')
        ->with('answers')
        ->withCount((
            'likes as likesCount' => function (Builder $builder) {
                $builder
                    ->where('rating', AppReviewQuestionLike::LIKE);
            },
            'likes as dislikesCount' => function (Builder $builder) {
                $builder
                    ->where('rating', AppReviewQuestionLike::DISLIKE);
            },
        ))
        ->get()
        ->map(function (Review $review) {
            $customer_name = $review->customer->firstname . ' ' . mb_strimwidth($review->customer->lastname, 0, 2, '.');
            $anonymous_name = $review->anonymous == true ? 'ANONIM' : $customer_name;

            return (
                
                'id' => $review->id,
                'customer_id' => $review->customer->id,
                
                'customer_name' => $anonymous_name,
                'recommendation' => $review->recommendation,
                'rating' => $review->rating,
                'advantages' => $review->advantages,
                'flaws' => $review->flaws,
                'text' => $review->text,
                'type' => 'review',
                'product' => $review->product->title,
                'created_at' => $review->created_at->format('d-m-Y'),
                'likesCount' => $review->likesCount,
                'dislikesCount' => $review->dislikesCount,
                'answers' => $review->answers
                    ->whereNotNull('published_at')
                    ->map(function (Answer $answer) {
                        $customer_answer = $answer->customer->firstname . ' ' . mb_strimwidth($answer->customer->lastname, 0, 2, '.');
                        $anonymous_answer = $answer->anonymous == true ? 'ANONIM' : $customer_answer;

                        /** @var Review $interlocutor_message */
                        $interlocutor_message = Review::query()->find($answer->model_id);
                        $interlocutor = $interlocutor_message->customer->firstname . ' ' . mb_strimwidth($interlocutor_message->customer->lastname, 0, 2, '.');
                        $interlocutor_name = $interlocutor_message->anonymous == true ? 'ANONIM' : $interlocutor;
                        $interlocutor_prefix_message_id = 'review_';

                        return (
                            
                            'id' => $answer->id,
                            'customer_id' => $answer->customer->id,
                            
                            'customer_name' => $anonymous_answer,
                            'interlocutor_message_id' => $interlocutor_prefix_message_id . $answer->model_id,
                            'interlocutor_name' => $interlocutor_name,
                            'text' => $answer->text,
                            'type' => 'answer',
                            'created_at' => $answer->created_at->format('d-m-Y'),
                            'likesCount' => count($answer->likes->where('rating', AppReviewQuestionLike::LIKE)),
                            'dislikesCount' => count($answer->likes->where('rating', AppReviewQuestionLike::DISLIKE)),
                            'comments' => $answer->comments
                                ->whereNotNull('published_at')
                                ->map(function (AnswerComment $comment) {
                                    $customer_comment = $comment->customer->firstname . ' ' . mb_strimwidth($comment->customer->lastname, 0, 2, '.');
                                    $anonymous_comment = $comment->anonymous == true ? 'ANONIM' : $customer_comment;
                                    if ($comment->model_type == 'AppAnswer') {
                                        /** @var Answer $interlocutor_message */
                                        $interlocutor_message = Answer::query()->find($comment->model_id);
                                        $interlocutor = $interlocutor_message->customer->firstname . ' ' . mb_strimwidth($interlocutor_message->customer->lastname, 0, 2, '.');
                                        $interlocutor_name = $interlocutor_message->anonymous == true ? 'ANONIM' : $interlocutor;
                                        $interlocutor_prefix_message_id = 'answer_';
                                    }
                                    if ($comment->model_type == 'AppAnswerComment') {
                                        /** @var AnswerComment $interlocutor_message */
                                        $interlocutor_message = AnswerComment::query()->find($comment->model_id);
                                        $interlocutor = $interlocutor_message->customer->firstname . ' ' . mb_strimwidth($interlocutor_message->customer->lastname, 0, 2, '.');
                                        $interlocutor_name = $interlocutor_message->anonymous == true ? 'ANONIM' : $interlocutor;
                                        $interlocutor_prefix_message_id = 'comment_';
                                    }

                                    return (
                                        
                                        'id' => $comment->id,
                                        'customer_id' => $comment->customer->id,
                                        'answer_id' => $comment->answer_id,
                                        
                                        'interlocutor_message_id' => $interlocutor_prefix_message_id . $comment->model_id,
                                        'interlocutor_name' => $interlocutor_name,
                                        'customer_name' => $anonymous_comment,
                                        'text' => $comment->text,
                                        'type' => 'comment',
                                        'likesCount' => count($comment->likes->where('rating', AppReviewQuestionLike::LIKE)),
                                        'dislikesCount' => count($comment->likes->where('rating', AppReviewQuestionLike::DISLIKE)),
                                        'created_at' => $comment->created_at->format('d-m-Y'),
                                    );
                                })->toArray(),
                        );
                    })->toArray(),
            );
        })->toArray();

python – How to delete database rows according to special conditions and orders?

I’m using the Ubuntu distribution for the GridDB Python Client, I have a dataset that has been converted to a database and is stored in the container. The database has three columns, the columns are keyword, noOfOccurences and class.

I need to delete a specific row which has the noOfOccurences less than 5 and is immediately preceded by a def keyword.

For example, here are five rows and five keywords from the rows are assert, if, def, for and break. The database looks somewhat like this:

keyword noOfOccurences Class
assert 6 1
if 10 0
def 9 3
for 4 2
break 7 1

The code should delete the row containing for since the noOfOccurences is less than 5 and it’s immediately preceded by a def keyword (else, do not delete). The output should be:

keyword noOfOccurences Class
assert 6 1
if 10 0
def 9 3
break 7 1

Is there a way I could do that using a function that takes in the result of an SQL query selecting the rows and columns from the database? Here is the code for the database connection and SQL query:

#...
import griddb_python as griddb
import pandas as pd

factory = griddb.StoreFactory.get_instance()

# Initialize container
try:
  gridstore = factory.get_store(host="127.0.0.1", port="8080",
  cluster_name="hp", username="root",
  password="")
  conInfo = griddb.ContainerInfo("CodeAnalysis ",
    (("keyword", griddb.Type.STRING),
    ("noOfOccurences",griddb.Type.INTEGER),
    ("class", griddb.Type.STRING)
  ),

  griddb.ContainerType.COLLECTION, True)
  cont = gridstore.put_container(conInfo)
  data = pd.read_csv("c_analysis.csv")
  #Add data

for i in range(len(data)):
  ret = cont.put(data.iloc(i, :))
  print("Data has been added successfully")
except griddb.GSException as e:
  print(e)

sql_statement = ('SELECT * FROM CodeAnalysis')
sql_res = pd.read_sql_query(sql_statement, cont)
def function(sql_res):
#...
return newResult

magento2.4.2 – ERROR IMPORT DATABASE MYSQL MAGENTO 2.4

I’m getting an error after importing mysql database

I exported the database from the old database with the same settings as the new one

Server version 8.0.23

Has anyone come across this error, or do you know how to solve it?

Thank you in advance! Hugs

SQL Server 2017 Enterprise – Database Administrators Stack Exchange

SQL Server 2017 Enterprise – Database Administrators Stack Exchange

database – Can’t restore existing Drupal 9 site (no route found for “GET /”)

I have a site with Drupal 9.2.5 and the site was clobbered after installing WordPress in the web root.

I have a backup of the site database, and the sites/default/* files, but I just can’t get Drupal up and running again to restore it to the way it was.

I have tried installing again and placing the files back, but this doesn’t work.

I have looked at the Drupal docs and searched for migrating or restoring a Drupal site, but nothing seems to be working out.

Once I create a new database, and import all data from the old one, update my settings and restore the folders and files, I get this error:

The website encountered an unexpected error. Please try again later.

SymfonyComponentHttpKernelExceptionNotFoundHttpException: No route found for "GET /"
in SymfonyComponentHttpKernelEventListenerRouterListener->onKernelRequest()
(line 136 of vendor/symfony/http-kernel/EventListener/RouterListener.php).

I am not sure what to do here: the issue is obviously with routing but I am stuck.

I can get to the install script, but it tells me Drupal is already installed, and to empty the database and start again (not what I want!)

I have tried the upgrade script, but I get an error:

The website encountered an unexpected error. Please try again later.
SymfonyComponentDependencyInjectionExceptionServiceNotFoundException: You have requested a
non-existent service "access_check.db_update". in
SymfonyComponentDependencyInjectionContainerBuilder->getDefinition() (line 1032 of vendor/symfony/dependency-injection/ContainerBuilder.php).
SymfonyComponentDependencyInjectionContainerBuilder->getDefinition('access_check.db_update') (Line: 600)
SymfonyComponentDependencyInjectionContainerBuilder->doGet('access_check.db_update', 1) (Line: 558)
SymfonyComponentDependencyInjectionContainerBuilder->get('access_check.db_update') (Line: 186)
DrupalCoreUpdateUpdateKernel->handleAccess(Object, Object) (Line: 101)
DrupalCoreUpdateUpdateKernel->handleRaw(Object) (Line: 75)
DrupalCoreUpdateUpdateKernel->handle(Object) (Line: 27)

This is very frustrating. I can’t find any useful information on restoring an existing Drupal site and how to do this.

Exception “org.h2.jdbc.JdbcSQLSyntaxErrorException: Table “TABLE_NAME” not found ” during initiolizing H2 database for testing Spring Boot application

I write integration tests annotated with @SpringBootTest. To run tests I insert data to the H2 database from the data.sql in test resources.

I have a situation when firstly tests run successfully, and after a number of times, I have an error, which tells me, that H2 cannot insert data in a table because that table is not found. The error comes with different raws every next launch of tests. In the same log, I see that this table was created and other insertions performed successfully before the exception encountered. I see that 4 of my 5 tests are green and one is red. It can be different test in different times.

Reboot of an IDE makes tests run successfully again for 3-4 times. After that the error returns.

I tried to use @DirtiesContext() with my test class and with test methods, but it didn’t solve the problem.

I have a guess that the source of the problem may be in the way I initialize databases. For both databases I use a reference for one data.sql file. I didn’t manage to find way to separate them into different *.sql files.

The second guess is that insertions into databases begin before tables are created. I’m checking this theory now by moving all insertions into test code. But I’m not sure that it’ll help as I sow log that tables was created before insertion started.

I use mvn clean before every test run.

I would be very grateful for your recommendations in solving this problem.

My entities:

@Entity
@Table(name = "entity1", schema = "schema1")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class SomeEntityClass extends GenericEntity<Long> { ...}


@MappedSuperclass
public abstract class GenericEntity<ID extends Serializable> implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column

    protected ID id;

//getter, setter
}

In my data.sql file initialization looks like that:

create schema if not exists schema1;
create schema if not exists schema2; 

drop table if exists schema1.table1;
create table schema1.table1
(....structure of the table...)
 
drop table if exists schema2.table2;
create table schema2.table2
(....structure of the table...)
 
INSERT INTO schema1.table1
(...)
VALUES (...)
 
... many insertions
 
 
INSERT INTO schema2.table2
(...)
VALUES (...)
 
... many insertions

This is H2 configuration in application.properties in test/resources:

# The first database
spring.datasource.url=jdbc:h2:mem:database01;INIT=RUNSCRIPT FROM 'src/test/resources/schema.sql'
spring.datasource.username=user1
spring.datasource.password=abc
spring.datasource.driver-class-name=org.h2.Driver
spring.jpa.generate-ddl=true

# The second database
spring.datasource.security.url=
  jdbc:h2:mem:dbo_security_db:database02;INIT=RUNSCRIPT FROM 'src/test/resources/schema.sql'
spring.datasource.security.username=user1
spring.datasource.security.password=abc
spring.datasource.security.driver-class-name=org.h2.Driver

Log with error:

08-09-2021 09:00:08.729 DEBUG (o.s.jdbc.datasource.init.ScriptUtils) - 0 returned as update count for SQL: create table schema1.table1( ….)
08-09-2021 09:00:08.730 DEBUG (o.s.jdbc.datasource.init.ScriptUtils) - 1 returned as update count for SQL: INSERT INTO schema1.table1(...) VALUES ()
///...other insertions into this table performed well
08-09-2021 09:00:08.735 WARN  (o.s.b.w.s.c.AnnotationConfigServletWebServerApplicationContext) - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource (org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class): Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #36 of URL (file:/home/nikiforov-java/Documents/.../target/test-classes/data.sql): INSERT INTO schema1.table1(...) VALUES (...); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "TABLE1" not found; SQL statement: ...

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
08-09-2021 09:00:08.782 ERROR (o.s.boot.SpringApplication) - Application run failed

database – Taxonomy terms that have a many to one relationship

Do I need a many to one relationship?
I’m think I’m going about it the wrong way and don’t know the correct way.
Let’s say I have content type of ‘Mushroom Species’.
Inside this content type, I have Taxonomy term fields for for the features of a mushroom.
Ideally these features will be populated from a single list of colors.

Spore Print Color: White
Bruising
-Cap: Black
-Stem: Black
-Pores: Black
-Flesh: Pink, Red, Black
Chemical Reactions:
-Cap:
--Ammonia: Green, Blue
--Iron salts: Blue
--Potassium hydroxide: Brown
-Stem:
--Ammonia: NA
--Iron salts: NA
--Potassium hydroxide: NA
-Flesh:
--Ammonia: NA
--Iron salts: NA
--Potassium hydroxide: NA

My main concern is that I want to create filters for these fields so users can easy narrow down the possible mushrooms. What should be my approach on this?

database – Handle Foreign Keys in microservices (microservice id from another microservice DB)

I have an application made of several microservices with independent databases
So let’s consider 2 microservices
Users and Documents
The users microservice manages the users and handles everything related to them and the documents microservice contains the document info, properties, file ….
Now each document belongs to a user and in the document database we have the user_id
I know usually you should make a call to users to get the users data but imagine this scenario :

I have an api call that gets all the documents that are shared for a user, each document belongs to a user so if i fetch a thousand documents i have to make a thousand additional call to get the user data.

I thought of several options :

  • have “common” tables like users replicated to all databases through a queue system (kafka for example) -> this will need to be maintained but gives the flexibility of having joins and foreign keys which makes it more performant
  • Make an api route to take an array of ids and send back the data and handle this each time in the documents api to build back the object -> this will take additional work each time and less performance but it will be the “clean” way to work with microservices
  • link each microservice to a “common” database in addition to its own database for easy access -> this is a shortcut to replicate tables but won’t solve the issue and we can’t join on it using ORMs

I imagine it’s a common case, but I can’t see anywhere what’s the best practice for this use case

database – How to black-box test transaction atomicity?

Suppose we need to ensure that a sequence of database operations – currently implemented as A followed by B followed by C – executes either all together, or not at all. We can achieve this using a database transaction – but how can we write an automated test to verify that this atomicity property is satisfied, which is not brittle?

We can fairly easily write a white-box test for this: given that we know that A is executed first, then B, then C, we can write a test which injects a fake C and makes the fake C throw a runtime exception instead of issuing a database update query, to simulate a system crash. Then we catch that exception in the test, and assert that nothing in the database has changed since the test began running.

However, if a developer later comes along and rewrites the implementation to call C first, our test will become invalid – it will only test that “if nothing happens, then nothing happens” or in other words “if the system crashes immediately, then nothing will be written to the database”. Not a very useful test, and not testing what we want to test! This is because it’s a white-box test which is predicated on an assumption about the order of operations inside the implementation – namely, that C is executed last (or at least, not first).

Is it possible to avoid the test turning into a bogus test in this way if the order of database operations in the implementation is changed?

DreamProxies - Cheapest USA Elite Private Proxies 100 Cheap USA Private Proxies Buy 200 Cheap USA Private Proxies 400 Best Private Proxies Cheap 1000 USA Private Proxies 2000 USA Private Proxies 5000 Cheap USA 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.com Proxies-free.com New Proxy Lists Every Day Proxies123.com Best Quality USA Private Proxies