sql server – Deleting all the content from the database

We want to clean a database (delete all the records) with around 25 tables and a size of around 650+ GB in production. We are currently using MS SQL 2016 Standard Edition with AlwaysON HA.

Is truncating all the tables the best option, if not what is the best and fastest way to do this?

What is the potential time it is going to take for cleaning approx 650 GB of data?

ruby – Gem::LoadError: Specified ‘mysql’ for database adapter,

I am trying to run rake db:setup for my old rails project and it is throwing the following error.

`rake aborted!
Gem::LoadError: Specified 'mysql' for database adapter, but the gem is not loaded. Add `gem 'mysql'` to your Gemfile (and ensure its version is at the minimum required by ActiveRecord).


Caused by:
Gem::LoadError: mysql is not part of the bundle. Add it to your Gemfile.

Tasks: TOP => db:setup => db:schema:load_if_ruby => db:create
(See full trace by running task with --trace)`

this is my ruby version ruby 2.3.8p459 (2018-10-18 revision 65136) (x86_64-linux)

my rails version Rails 4.2.7.1

and mysql version mysql Ver 14.14 Distrib 5.5.56, for linux-glibc2.5 (x86_64) using readline 5.1

Thank you

python – In Spark can the data be processed by querying database before transforming to RDD

I am very new to Spark.

My System

  1. The data from kafka is consumed in Spark
  2. This data next needs to be processed by querying database to get the relevant formula for manipulation
  3. The thus processed data is then used in getting RDD – mapaggregatereduce… to make the resultant data
  4. The resultant data and processed data are then stored in DB.

My Query

Is it right to do this in Spark? i.e can we query database to get a formula to process the data consumed in Spark

If not, please guide me on how I need to be achieving this.

What is the current recommended way of changing the collation of an Azure SQL database?

We would like to switch to using the Latin1_General_100_CI_AI_SC_UTF8 collation in our Azure SQL database. Altering the collation of individual columns aside, if this were on-premise SQL Server 2019, changing the collation of the database would just be a matter of running ALTER DATABASE CURRENT COLLATE Latin1_General_100_CI_AI_SC_UTF8 (with the database in single-user mode). I understand that ALTER DATABASE with COLLATE is not supported on Azure SQL, the collation can only be set when the database is created. I found this workaround from 2 years ago, but exporting the entire database to on-premise and importing it back to Azure seems impractical for a large production database. Are there any other ways of doing it as of now?

php – What is an efficient way to create, temporarily store, and search user-created database tables?

I am working on a web project, creating a tool that allows the user to submit some small amount of data (a ~30mb text file), from which I want to create several thousand database entries that can be searched through.

The database entries will then be searched through depending on the user’s search parameters.

Right now my idea for implementing this is to accept the text file onto my server –> process the data within the file using Python –> create a MySQL database table from the Python output –> search this table with the user’s parameters –> delete the table when the job is finished and the data is outputted.

The problem that I foresee is that each time a user creates a submission, a new table will be created, so I will need to have some way of cleaning these tables up (deleting them) after they are no longer needed. On top of that, if I want to store user jobs, my database will get cluttered with hundreds and hundreds of tables from user submissions, so I feel like my architecture/design is totally wrong.

I haven’t implemented the database/backend fully yet but wanted to know if there was a more efficient way of creating, storing, and searching ‘temporary’ tables. My experience is with PHP/MySQL, but if anyone knows of a better way to do this, I would appreciate the info.

How to Stop Inappropriate Contents to Insert in Database (PHP)

Hi,

Created a website for my client https://www.jeenmount.com/ and client need to see what people searching for on his site. So we are inserting each search in database or increase number of search if Key already exist there.

My issue is inappropriate search like below

“>alert(String.fromCharCode(88,83,83))
place for destination wedding’A=0
pink city mall in jaipur” or (1,2)=(select*from(se
pink city mall in jaipur99999′ union select unhex(
heritage wedding resort hotel jaipur” and “x”=”y
heritage wedding resort hotel jaipur and 1>1

I think this types of search are doing by Bots or any other things.

What i am looking for is …… If search queries are with these types of strings or inappropriate characters then either clear code and adjust in proper format or avoid to insert in database.

Please help us that how can we do that.

Using already “mysql_real_escape_string” “htmlspecialchars” for security but unable to stop inappropriate contents to insert in database.
SEMrush

Hope for quick response.

 

I have added My data in mysql database is not coming

ackage com.snmz.KitapEvi.Entity;

import java.math.BigDecimal;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

@Entity
@Table(name="snmz_book")
@Setter
@Getter
@ToString
public class Kitap {
    @Id
    @GeneratedValue(strategy =GenerationType.IDENTITY)
    private Long id;

    @Column(name="stok_kodu")
    private String stok_kodu;

    @Column(name="adi")
    private String adi;

    @Column(name="aciklama")
    private String aciklama;

    @Column(name="birim_fiyat")
    private BigDecimal birim_fiyat;

    @Column(name="image_url")
    private String image_url;

    @Column(name="aktif")
    private Boolean aktif;

    @Column(name="stoktaki_birimler")
    private int stoktaki_birimler;

    @Column(name="tarih_oluşturuldu")
    private Date tarih_olusturuldu;

    @Column(name="Son_güncelleme")
    private Date Son_güncelleme;



    @ManyToOne
    @JoinColumn(name="category_id",nullable=false)
    private SnmzCategory category;


}

package com.snmz.KitapEvi.Repository;

**----repository-------**
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import org.springframework.web.bind.annotation.CrossOrigin;

import com.snmz.KitapEvi.Entity.Kitap;

@CrossOrigin("http://localhost:4200")
@Repository
public interface KitapRepository extends JpaRepository<Kitap, Long>{

}

**------application.properties------**
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/blogdb?useSSL=false&serverTimezone=UTC&useLegacyDatetimeCode=false
spring.datasource.username=root
spring.datasource.password=190325
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

spring.data.rest.base-path=/api/v1

—-http://localhost:8080/api/v1/kitaps——/
/*The information I added in the database should have come
but not coming
{
“_embedded” : {
“kitaps” : ( {
“_links” : {
“self” : {
“href” : “http://localhost:8080/api/v1/kitaps/1”
},
“kitap” : {
“href” : “http://localhost:8080/api/v1/kitaps/1”
},
“category” : {
“href” : “http://localhost:8080/api/v1/kitaps/1/category”
}
}
}, {
“_links” : {
“self” : {
“href” : “http://localhost:8080/api/v1/kitaps/2″strong text
},
“kitap” : {
“href” : “http://localhost:8080/api/v1/kitaps/2”
},
“category” : {strong text
“href” : “http://localhost:8080/api/v1/kitaps/2/category”
}
}
}, {
“_links” : {
“self” : {
“href” : “http://localhost:8080/api/v1/kitaps/3”
},
“kitap” : {
“href” : “http://localhost:8080/api/v1/kitaps/3”
},
“category” : {
“href” : “http://localhost:8080/api/v1/kitaps/3/category”
}
}
}, {
“_links” : {
“self” : {
“href” : “http://localhost:8080/api/v1/kitaps/4”
},
“kitap” : {
“href” : “http://localhost:8080/api/v1/kitaps/4”
},
“category” : {
“href” : “http://localhost:8080/api/v1/kitaps/4/category”
}
}
}, {
“_links” : {
“self” : {
“href” : “http://localhost:8080/api/v1/kitaps/5”
},
“kitap” : {
“href” : “http://localhost:8080/api/v1/kitaps/5”
},
“category” : {
“href” : “http://localhost:8080/api/v1/kitaps/5/category”
}
}
}, {
“_links” : {strong text
“self” : {
“href” : “http://localhost:8080/api/v1/kitaps/6”
},
“kitap” : {
“href” : “http://localhost:8080/api/v1/kitaps/6”
},
“category” : {
“href” : “http://localhost:8080/api/v1/kitaps/6/category”
}
}
}, {
“_links” : {strong text
“self” : {
“href” : “http://localhost:8080/api/v1/kitaps/7”
},
“kitap” : {
“href” : “http://localhost:8080/api/v1/kitaps/7”
},
“category” : {
“href” : “http://localhost:8080/api/v1/kitaps/7/category”
}
}
}, {
“_links” : {strong text
“self” : {
“href” : “http://localhost:8080/api/v1/kitaps/8”
},
“kitap” : {
“href” : “http://localhost:8080/api/v1/kitaps/8”
},
“category” : {
“href” : “http://localhost:8080/api/v1/kitaps/8/category”
}
}
}, {
“_links” : {
“self” : {
“href” : “http://localhost:8080/api/v1/kitaps/9”
},
“kitap” : {
“href” : “http://localhost:8080/api/v1/kitaps/9”
},
“category” : {
“href” : “http://localhost:8080/api/v1/kitaps/9/category”
}
}
}, {
“_links” : {
“self” : {
“href” : “http://localhost:8080/api/v1/kitaps/10”
},
“kitap” : {
“href” : “http://localhost:8080/api/v1/kitaps/10”
},
“category” : {
“href” : “http://localhost:8080/api/v1/kitaps/10/category”
}
}
} )
},
“_links” : {strong text
“self” : {
“href” : “http://localhost:8080/api/v1/kitaps”
},
“profile” : {
“href” : “http://localhost:8080/api/v1/profile/kitaps”
}
},
“page” : {
“size” : 20,
“totalElements” : 10,strong text
“totalPages” : 1,
“number” : 0
}
}
*/

sql server – Role to grant a DBA who should not access confidential Database

I am a junior DBA and I am in a bit of a pickle. I need to grant elevated privileges to our new DBA but he hasn’t signed consent forms yet so I cannot grant him access to a specific database in our server.

How can I grant someone enough access to fulfill a DBA role but not access to one specific database?

As in, I don’t want to add the person to sysadmin role which will give him access to this confidential database, but I also want him to be able to work (do his dba job that he was hired for).

Thank you

database – Magento v1.9 cmd line sql query to find certain scenarios backorder out of stock

I have a real odd issue
Magento v1.9.4.5

I have a ton like thousands of products that are set to back order allowed

However they also say out of stock

I need help I not sql expert

I need a sql query that i can run via cmd line ssh sql that will list all products which backorder are enabled

Then another to list those which backorder enabled and are out of stock

Then Lastly to prove fix one that list backorder enabled and are in stock :]

Thanks

SQL Server 2019 Database stuck In Recovery state after restart

There’s not really anything you can do. Your database is going through crash recovery, and it needs to process the transaction log. I’ve written about this exact scenario more in depth here.

If SQL Server hadn’t completed a CHECKPOINT recently, there would be database changes that were committed, but had only been made in memory & in the transaction log, but not in the data files. These changes need to be redone from the log file so that the changes aren’t lost. This is what your server is doing right now. I describe the phases in more detail in the article I linked to earlier.

If your transaction log is unnecessarily very large, this can cause SQL Server to do checkpoints less frequently. A too large log file, or too many VLFs can both result in a longer recovery time after a crash.

For a planned restart you can minimize/eliminate the delay by performing a manual CHECKPOINT; before shutting down and by allowing the SQL Server service to gracefully stop. Rebooting the server without first stopping the service can result in a forced termination of the sqlservr.exe process, leading to recovery like you’re seeing on startup.

To get your database back online, just be patient. Don’t restart, don’t try to get rid of your log file. Just be patient. That estimated recovery time is notoriously incorrect, so hope that it’s online sooner, rather than later.