texts: why are my Database backups occasionally failing?

texts:
Part of our back up routine, on our main production server, I nightly take a full backup of all of the databases.  These databases get restored on several dev severs and data  is obfuscated.  These are for Developers to work on, test on, and our staging/QA environment.    These are mostly sql server 2016 (a couple of the dev environments are 2019 as we prepare to make that switch.)  The code which does these backups and restores has not changed in months (close to a year).  These instances are spread over three different servers.

About two weeks ago I started getting errors that  the lower environments were getting errors trying to restore some of  the backup files. I have scoured the logs and found nothing. The restore is done in an agent job in a try- catch block. The try is “restore” , the catch is “send email to dba”.  That email is the only thing I could find.  However when I looked at those dbs, they indeed did not restore.
this is the error message I get when trying to restore them manually :
 Msg 3013, Level 16, State 1, Line 21
RESTORE DATABASE is terminating abnormally.

When a backup for a database failed, it failed for all of the instances to where it was going to get restored. Obviously a bad backup.  I scoured the log files on the production server and found nothing.

I could take a new backup of the offending databases and restore them in the lower environments with no issue.

There are only about 10 databases (A B C D E F G H I J)  that are affected.  Each day between 1 and 4 of these databases fails to back up. It is random how many and which databases.
Day one could be A G J,  day 2 E  day 3 B C G H  day 4 B E C I  etc.    There are about 50 databases on the server.  The ten that take turns failing are ten of the  smallest of the 50 databases.  two of the ten are relatively new (maybe a year old) while the others are ancient.

Now as part of the backup process I have changed it so that after each database backs up I run a restore with verify only with checksum on the database.  If the verify fails, it sends me an email and redoes the backup.  This has taken care of the problem of the restores and everything is working smoothly.  However I am still getting the emails saying that the verify of the backup has failed.  I only get the email once.  I would get the email for each time the verify fails. So the second attempt is always working.

Whereas checking the backups has solved the restoring problem,  I have not solved the problem  The problem reamains that bad backups are being taken.  I am worried that something is wrong with our prod server in General or those databases in particular.  I am afraid that my work around will only hide the problem and that we have a train wreck coming down the line.

I run DBCC CHECKDB on the entire server daily, and I have run it on those specific databases and results say that all is normal.  There is plenty of space on all of the lower level instances so space contention  is not an issue.


texts: Why does SQL authentication sometimes fail and how can I make it work reliably?

texts:
I have SQL Server Express installed locally on a machine and it works most of the time but occasionally it fails to make a connection with this error:

  Could not obtain information about Windows NT group/user
  'MYDOMAIN\myUser', error code 0x6fd.

I'm at a loss for what to change. I can't guarantee the laptop can see the domain controller at all times because - well - it's a laptop! But it would seem that has no effect anyway (it works when disconnected from the network usually; it fails when connected sometimes).

Here's the SQL Server connection string:

Data Source=localhost\MYINSTANCE;Integrated Security=True;Initial Catalog=myDbName;Connection Timeout=15

What should I do?

Edit:
I've tried moving it to a localdb and using SQL authentication to the SQL Server Express instance. Both ways STILL FAIL with the "could not obtain information..." message. They succeed if there is no network at all, but they all fail if there is a network that isn't the domain one. Not sure what the next move should be...


texts: Prematurely exiting a while loop in T-SQL and viewing the output

texts: I let a while loop run over the weekend with a CURRENT_TIMESTAMP condition on it, problem is I accidentally set the condition to 2020-08-06 instead of 2020-06-08 (oops). Is there any way to manually exit the loop and view the results so far? I don't think it's possible for me to change the system time in order for the loop to exist as the SQL server isn't run locally.


texts: Why does Oracle yield multiple levels per row in this hierarchical query?

texts:
I'm trying to reproduce the first simple example from this tutorial.

However, I don't understand why I get so many rows back. I would expect that 1 reports to 2 only at level 1 and not at level 2 as well.

SQL> select employee_id, reports_to from employees;

EMPLOYEE_ID REPORTS_TO
----------- ----------
      1      2
      2
      3      2
      4      2
      5      2
      6      5
      7      5
      8      2
      9      5

9 rows selected.

SQL> select employee_id, reports_to, LEVEL from employees connect by prior employee_id = reports_to;

EMPLOYEE_ID REPORTS_TO      LEVEL
----------- ---------- ----------
      1      2      1
      8      2      1
      5      2      1
      6      5      2
      9      5      2
      7      5      2
      4      2      1
      3      2      1
      6      5      1
      9      5      1
      7      5      1

EMPLOYEE_ID REPORTS_TO      LEVEL
----------- ---------- ----------
      2         1
      1      2      2
      8      2      2
      5      2      2
      6      5      3
      9      5      3
      7      5      3
      4      2      2
      3      2      2

20 rows selected.


texts: How can I wipe all databases and users from a SQL Server database?

texts:
Okay, here is the story.

I have a Windows 7 Ultimate box with a SQL Server 2008 R2 Standard installation that I inherited from a person who left the company long before I started at my company. No documentation about the users has been left.

I don't need any of users other than having the ability to set a sa password and don't need any of the databases currently on the box.

The only reason I can't remove SQL Server from the box is because I don't have the original media in this office and would have trouble getting the media from the other office. I need the standard install because one of the databases I want to load is 70GB in size (they stored images in tables).

Is there a way to just start fresh?


texts: When upgrading config servers to use WireTiger, the running config server with wiredtiger does not authenticate the existing pass and user?

texts:
In order to upgrade mongodb to 3.0.0, I am upgrading my config servers to use WireTiger. I followed the subsection of Change Config Server to Use WiredTiger. There are 18 steps. I run mongodump --port 27019 --username user --password pass --authenticationDatabase admin --out /var/data/config1/mongodump-15-03-16/ to bump the data. When I went to step 6, I tried to use "mongorestore --port 27019 --username user --password pass --authenticationDatabase admin --authenticationMechanism MONGODB-CR /var/data/config1/mongodump-15-03-16/" to restore the data.
The error is

  2015-03-17T17:37:51.963Z I NETWORK  [conn14] end connection 127.0.0.1:56093 (5 connections now open)

  2015-03-17T17:38:01.685Z I NETWORK  [initandlisten] connection accepted from 127.0.0.1:56095 #15 (6 connections now open)

  2015-03-17T17:38:01.687Z I ACCESS   [conn15]  authenticate db: admin { authenticate: 1, nonce: "xxx", user: "user", key: "xxx" }

  2015-03-17T17:38:01.687Z I ACCESS   [conn15] Failed to authenticate user@admin with mechanism MONGODB-CR: AuthenticationFailed UserNotFound Could not find user user@admin

  2015-03-17T17:38:01.687Z I NETWORK  [conn15] end connection 127.0.0.1:56095 (5 connections now open)

I have no idea. But when I start the config server with MMAP storage engine, no errors. My config file is

storage:
    dbPath: "/var/data/config1/db"
    engine: "wiredTiger"
    directoryPerDB: true
    journal:
        enabled: true
systemLog:
    destination: file
    path: "/var/data/config1/log/config1.log"
    logAppend: true
    timeStampFormat: iso8601-utc
processManagement:
    fork: true
net:
    bindIp: 0.0.0.0
    port : 27019
    wireObjectCheck : false
    unixDomainSocket:
        enabled : true
security:
    keyFile: "/var/data/config1/keys/mongodb-keyfile"
    authorization: "enabled"
sharding:
    clusterRole: "configsvr"

Any idea? Should I upgrade the authentication before upgrade the config servers to user WiredTiger?  In addition, I used the same binary /usr/bin/mongod to generate the config server process and the data server process. Does this matter?


texts: In Postgres, is it possible for me to have a table partitioning constraint based on data type?

texts:
Say I have the following schema for my Postgres database:

CREATE TABLE sources (
  source  TEXT  NOT NULL,
  PRIMARY KEY (source)
);

CREATE TABLE ipaddrs (
  addr  INET
) INHERITS (source):

CREATE TABLE domains (
  domain  TEXT
) INHERITS (source);

Is it possible for me to use a CHECK constraint somewhere that would add a source that is a valid IP address to the ipaddrs partition, and otherwise add it  to the domains partition?

Ultimately, I'm wanting to be able to leverage Postgres' built-in INET/CIDR capabilities on sources that are IP addresses.


texts: HashBytes Computed Column: Exclude Column

texts:
We want to create Hashbytes computed column Non-Persisted. Table has twenty columns, and would like to exclude IdentityId column. How could I do this in computed column? Is there a SQL library function to exclude certain column? Purpose is for easy maintenance and as columns get added, don't want developers to forget adding extra columns. So hashbyte should always take all columns, exclude identity.

This is manual way typing all the columns:

CREATE TABLE [dbo].[CustomerTransactiont]
(
    CustomerTransactionId int primary key identity(1,1),
    CustomerName varchar(255),
    Price decimal(10,2),
    Quantity int,...
    .....
    RowHash as hashbytes('SHA2_512', CONCAT(CustomerName,'|'
                                                ,Price), '|'
                                                ,Quantity), '|'...
                                                ))
)

Reference:
How to create a Hash Computed Column for Many Columns?


texts: MySQL row does not persist, but primary key with auto-increment is incremented

texts:
I have a MySQL database that is acting oddly. I insert a new row and observe that for a brief period of time (less than 30 seconds), the row persists as expected in the table. After this time, though, the row vanishes (despite no other queries being executed).

This table has an integer ID as a primary key, with auto-increment set, and the primary key is auto-incremented as expected for new rows. This leads me to believe there is not some kind of transactional rollback (and my table is MyISAM, so that shouldn't be possible anyways) or anything else that is somehow reverting the database to a previous state.

What logs should I be checking to see what is going on here? The contents of my '/var/log/mysql/error.log' are below, but I don't see anything unusual.

120815 21:01:01 [Note] Plugin 'FEDERATED' is disabled.
120815 21:01:02  InnoDB: Initializing buffer pool, size = 8.0M
120815 21:01:02  InnoDB: Completed initialization of buffer pool
120815 21:01:03  InnoDB: Started; log sequence number 0 44233
120815 21:01:03 [Note] Event Scheduler: Loaded 0 events
120815 21:01:03 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.63-0ubuntu0.11.10.1'  socket: '/var/run/mysqld/mysqld.sock'      port: 3306  (Ubuntu)
120815 21:01:04 [ERROR] /usr/sbin/mysqld: Table './appname/users' is marked as crashed and should be repaired
120815 21:01:04 [Warning] Checking table:   './appname/users'
120815 21:10:34 [Note] /usr/sbin/mysqld: Normal shutdown

120815 21:10:34 [Note] Event Scheduler: Purging the queue. 0 events
120815 21:10:34  InnoDB: Starting shutdown...
120815 21:10:39  InnoDB: Shutdown completed; log sequence number 0 44233
120815 21:10:39 [Note] /usr/sbin/mysqld: Shutdown complete

I noted the 'crashed' mark on the appname/users table, but mysqlcheck suggests the table is OK.

Any thoughts?


texts: Changing data type from mediumint to int without downtime

texts:
My current table is using MEDIUMINT for ID column ‘primary key auto increment’. The current table records soon will reach the maximum value for mediumint which is 8388607. That being the case, I need to change the type to int or change it to unsigned without any downtime.

My setup is master-slave MySQL 5.6 on Amazon RDS. I have one master and 4 slaves.

I tried to change the column type to int on slave but it break the replication.
Records are changing, because of this it is not possible to copy the table to another table.
I cannot use pt-table-sync on Amazon RDS to sync another table and then do the changes on new table.

Is there any way to change to int without locking the table?

Update: I am not sure if the attribute is signed or unsigned. I am not able to find where it signed or not. I run show columns from table and here is the result:

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
|     id         | mediumint(8) | NO   | PRI | NULL    | auto_increment |


texts: SQL Server 2012 CDC not working on certain tables only

texts:
I am using SQL 2012 sp3.  I have CDC working on many tables and DBs without issue.

Last night a change was put into the system and 2 of my CDC'd tables were dropped and modified without properly removing CDC on those tables beforehand (it was done without my knowledge).  I can no longer get CDC to work on those 2 tables.

I found the old capture instances for the tables and dropped them.  When I add them back in, everything goes in error free, but CDC captures no data and select sys.fn_cdc_get_min_lsn returns NULL.

I have tried adding/dropping the captures several times, adding data to the tables myself just to make sure some changes were going in, stop/starting SQLAgent and running the cleanup job.  I also tried adding the captures back in under different a different name.  Nothing works.

When I add both captures back in;
sys.sp_cdc_help_change_data_capture shows both of my captures.  select sys.fn_cdc_get_min_lsn('Capture_table1') returns null. sys.dm_cdc_errors is empty.   select * from  cdc.Capture_Table1_CT has no data in it (even though I made sure to add some data changes).  My Agent and SQL logs are clean.

I read that running sp_repldone can cleanup the log and may help CDC, however I hesitate to do that as this is running on an Availability Group and I am concerned it may have negative effects.

Is there anything else to try?


texts:
I cannot get mysqldump to generate a dump that will properly restore.  The dump is being generated to setup a new slave, but whenever the slave starts up, it gets foreign key constraint failures.

I'm generating the dump with slave positions and as a single transaction so that the dump is (allegedly) atomic, but when I essentially execute the following:

mysqldump \
    -h master \
    --flush-logs --single-transaction --master-data \
    --apply-slave-statements \
    --databases my-db \
    | mysql -h slave

I get the following error when I "SHOW SLAVE STATUS":

Last_Errno: 1452
Last_Error: Error 'Cannot add or update a child row: a foreign key constraint fails ...

All of the tables use the InnoDB engine, and the foreign key is an integer.

Using MariaDB 10.3.


texts: Database design that requires an index over two tables

texts:
Using PostgreSQL (currently 9.6, but upgrades are possible), I currently have the following database layout where customers can order products, which are themselves sorted into categories (products may be in multiple categories):
Orders
id -- PRIMARY KEY
customer_id -- FOREIGN KEY (Customer - id)
product_id -> FOREIGN KEY (Product - id)

Products
id -- PRIMARY KEY

Categories
id -- PRIMARY KEY

Product_Categories
product_id -- FOREIGN KEY (Product - id)
category_id -- FOREIGN KEY (Category - id)

Data volume
Now, I have a fairly large amount of orders (~30M) and a reasonable number of categories (~1K) and customers (~10K). There is around 30K Products, with an average of 3 products by category. Products may be moved from a category to another occasionally (let's say a once per month shuffle)
Query tendencies
My problem is that I want to have the following type of query run fast: "Get all Orders for customer whose product is in Category C". That would look like:
SELECT * FROM Orders
JOIN Product_Categories ON Orders.product_id = Product_Categories.product_id
WHERE Orders.customer_id = X AND Product_Categories.category_id = Y

Indexing considerations
The best index I can think of is an index on customer_id in Orders, supported by a secondary index on Product_Categories.product_id. This leads to the following plan (not a real plan since the design I showed above is a very large simplification of the actual case):
 - Index Scan on Orders using index on customer_id ---> Returns ~10K Rows
 - 10K Joins done by Index Lookup on the product_id index of Product_Categories (MAIN TIME CONSUMER)
 - 9990 Rows Filtered Out.
 - 10 Rows Returned

I would like to have an index over (customer_id, category_id), but I haven't been able to find a way to do this. The best solutions I can think of is to add a column categories_id INTEGER[] and then either:

Add a GIN index using categories_id and customer_id with the inclusion in list operator.
Create 1000 Partial indexes on order_id.

In both cases, I would have to synchronize categories_id with the updates in the category ↔ product association tables, which is unfortunate.
Questions
My questions are:

Am I overthinking? Is the "filtering out 10k" rows not that bad of a
problem  and any solution I can think of will make the problem worse?
Am I missing something? Can I be efficient without changing my database schema?
Assuming I should change my database schema, what is the best way to do so?


texts: I need to find when and which user created full text catalog, and who added/modified/deleted the columns (that were full text indexed) in that full text catalog.


texts: Foreign Key constraint on fixed value field

texts:
Note: I am a developer...

I have an Asset table that has many codes that are foreign keys into a second table OutlineFiles. The OutlineFiles table has Type and Code as the primary key. The foreign key constraint need to look like this...

ALTER TABLE "dbo"."Assets"
ADD CONSTRAINT "Locations" FOREIGN KEY ( "LN", "LocationCode" ASC )
REFERENCES "dbo"."OutlineFiles" ( "Type", "Code" );

ALTER TABLE "dbo"."Assets"
ADD CONSTRAINT "CostCentre" FOREIGN KEY ( "CC", "CostCentreCode" ASC )
REFERENCES "dbo"."OutlineFiles" ( "Type", "Code" );

So there will be a relationship to the outlines table via a fixed value of "LN" and "CC". Or will it be easier to change the database to have two table and remove the Type column on the OutlineFiles table? I am attempting this in Sybase BUT we support many databases.


texts: How do I preserve the leading zeros when I insert a number into this table?

texts:
I have inserted two records into a table.

create table num(id int)
insert into num values(0023)
insert into num values(23)
select * from num

When I query them, they all display as 23. It means that SQL Server ignores the leading 0s. What's the mechanism behind that? How can I have SQL Server return the values as I inserted them (i.e. 0023 and 23)?


texts: Postgres automatic failover, multiple clients to multiple masters

texts:
I'm looking into providing a method of having multiple hot spare PostgreSQL instances to our website.

The majority of implementations that I have found require a single point of failure with PgPool II or PGBouncer proxying the connection for every app server to the correct database.

Example:

App Server 1 \              Spare
App Server 2 -  PGPool II - Master
App Server 3 /              Spare

I'm looking to try a method of automatic fail-over, where each app server will fail-over automatically to a new master DB quickly and correctly. It can still use PGPool or PGBouncer, but each app server should be separate and autonomous with no message/state passing from app server to app server.

Example:

App Server 1 -  PGPool II \ Master 1
App Server 2 -  PGPool II - Master 2
App Server 3 -  PGPool II / Master 3

A maximum time for switch over should be 1-2 seconds.

Does this make sense and is it possible?
Is this an easy to implement via some tool/configuration? If so, how? If not, why?


texts: How to insert values to a table from 2 foreign keys

texts:
Hey guys was wanting some help. I want to add the same value for Student_ID that is in the Studentinfo table in the GradeInfo and also wanted to add the same value for CourseNo that is in the CourseInfo table and add it to GradeInfo.  Keep in mind that CourseNo in GradeInfo table is a foreign key from CourseInfo and Student_ID in GradeInfo is a foreign key from CourseInfo.

What I was wanting to know is how do I Insert these values from the foreign keys from other tables? I kept getting an error message of  "foreign key mismatch - "GradeInfo" referencing "CourseInfo"

CREATE TABLE CourseInfo (           /*Created 2nd*/
    CourseNo Text,
    Year NUMERIC,
    Title TEXT,
    PRIMARY KEY (CourseNo, Year)
)

CREATE TABLE GradeInfo (                    /*Created 3rd*/
    Student_ID NUMERIC AUTO_INCREMENT,
    CourseNo Text,
    Pts NUMERIC,
    Grade TEXT,
    FOREIGN KEY (Student_ID) REFERENCES StudentInfo (Student_ID)
    FOREIGN KEY (CourseNo) REFERENCES CourseInfo (CourseNo),
    PRIMARY KEY (Student_ID, CourseNo)
)

CREATE TABLE StudentInfo (            /*Created 1st*/
    Name TEXT,
    Student_ID NUMERIC AUTO_INCREMENT,
    Date_of_Birth NUMERIC,
    Gender TEXT,
    Address TEXT,
    Email TEXT,
    Degree TEXT,
    PRIMARY KEY (Student_ID)
)

This is my DML for the tables

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("INFO151", "Databases", "2019")

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("ECON130", "Microeconomic Principles", "2019")

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("FCOM111", "Government, Law and Business", "2019")

NSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("INFO101", "Foundations of Info Systems", "2019")

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("INFO141", "Systems Analysis", "2018")

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("INFO151", "Databases & SQL", "2018")

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("MGMT101", "Introduction to Management", "2018"),

INSERT INTO CourseInfo( CourseNo, Title, Year)
VALUES("QUAN102", "Statistics for Business", "2018")

This is is already in my table as Data. It works fine.

INSERT INTO StudentInfo (Name, Student_ID, Date_of_Birth, Gender, Address, Email, Degree)
Values ("James bond", "007007", "07-07-1977", "Male", "10 Downing Street", "JB007@gmail.com", "Bachelor of Technology")

This is is already in my table as Data. It works fine.

INSERT INTO GradeInfo(Student_ID, CourseNo, Pts, Grade)
VALUES("7007", "INFO151", "15", "A+")

This is where I am having the problem. The values for student_ID and courseNo are already in the table but still get the error message of "foreign key mismatch - "GradeInfo" referencing "CourseInfo".

Note that Student_ID is a foreign key from StudentInfo and CourseNo is a foreign key from CourseInfo

Any help would be appreciated. Thank you:)


texts: Insert into table select * from table vs bulk insert

texts:
I just wanted to know that SQL Statement INSERT INTO TABLE1 SELECT * FROM TABLE2, will work same like bulk insert ?

If no, Is there any way exclude index while inserting records. Process is inserting 150 Millions data in one execution.

We are planning to create stage table (Which will not have any index Table1), then transfer it from stage table to target table (Which will have index Table2)

We are not in the situation to create flat file from the process.

But we are looking something that can speed up process when we transfer data from Table1(not indexed) to Table2 (indexed).

Any way to use BulkInsert from Table1 to Table2?


texts: Which options to enable to guarantee full recovery in SQL Server Express 2005?

texts:
I am using SQL Server 2005 Express Edition. I noticed that Recovery Model is set to Simple, by default. I changed this option to Full but want to know which other options to enable to guarantee full-recovery if there is no backup.

Is there any way to take scheduled backups of SQL Server 2005 Express databases?


texts: Why do LocalTemp table showing with lengthy underscore under Temporary Tables of tempdb in SSMS 2017 Express Edition?

texts:
In my Microsoft SQL Server 2017 Express Edition, I have created a local temp table using below create statement,

create table #localtemptable
(tempid int,tempname varchar(20) )

After creating #table, I  checked the tempdb -> Temporary Table , it is showing the table name along with a lengthy underscore and 0000000004 number. What is it meaning and why it is showing like this?


texts: SSMS cannot connect remote server by IP through Windows authentication

texts:
I'm trying to connect a remote server by IP,port via Windows authentication.
It will end out with the following error

  Connection Timeout Expired.  The timeout period elapsed while
  attempting to consume the pre-login handshake acknowledgement.  This
  could be because the pre-login handshake failed or the server was
  unable to respond back in time.  The duration spent while attempting
  to connect to this server was - [Pre-Login] initialization=41306;
  handshake=27677;  (Microsoft SQL Server, Error: -2)

However, if connect to IP,port but login with sa through SQL Server authentication then I can connect to the server correctly.

Moreover, if I switch to MachineName,port and login via Windows authentication then I can successfully connect too.

Not sure what kind of setting/adjustment I need to make this use case work.

Environment

My laptop is Win10 and the SQL Server 2012 is on a Windows server 2012 R2
These two machines are in the same domain.
We are using another port instead of default 1433.

Case I tried

remote IP,port via Windows authentication - failed
remote MachineName,port via Windows authentication - successful
remote IP,port via SQL Server authentication(sa account) - successful
remote IP\MSSQLSERVER,port via Windows authentication - failed
RDP to remote then use ssms there IP,port via Windows authentication - successful

Setting I have tried

Add the SQL Server exe to the inbound rule
Launch SQL Server browser
Change IP1 to the IPv4 I'm using and Active/Enabled it(restart service too)
Make sure TCP/IP are enabled on Protocols of MSSQLSERVER and Client Protocols(/32bit)
Add the port I'm using to IPAll/TCP Port and keep Dynamic ports blank


texts:
I cannot seem to change bytea_output to hex.

I have tried this method:

ALTER DATABASE dbname SET bytea_output TO 'hex';

I have tried to edit postgresql.conf

bytea_output = 'hex'

and restarted the server.

I have tried

SET bytea_output = 'hex'

and it works per transaction, but that is very tedious.

However, whenever I browse my bytea columns in phpPgAdmin, the columns are outputted escaped.

This

SELECT setting FROM pg_settings WHERE name = 'bytea_output'

returns escape.

How can bytea_output be set permanently to hex?


texts: How can I execute multiple DDL statements within a transaction in Oracle?

texts:
Is it possible to execute multiple DDL statements under a single transaction with Oracle database (10g etc.?) What I'm trying to accomplish is some sort of "upgrade" functionality of the database schema. I have a working implementation of this in SQL Server and I'm planning to port it to Oracle.

p-code follows:

start transaction

 CREATE TABLE A .....
 CREATE TABLE B ......

 INSERT INTO A....
 INSERT INTO B...

 commit transaction

 or error rollback transaction


texts: for Insert multiple rows in one single insert MySQL

texts:
I have done bulk-insertion in MySQL using multiple row insertions method by using python script. My testing csv file consists of 100,000 lines only (real one consists of millions lines).
so I perform bulk-insertion.

and by using unix time command the results looks like

for 10 lines            100 lines               1000 lines

user 13.675 seconds     user 11.948 seconds     user 9.908 seconds
sys  0.192 seconds      sys  0.076 seconds      sys  0.080 seconds

for 10,000 lines        100,000 lines

user 11.672 seconds     user 12.024 seconds
sys  0.072 seconds      sys  0.079 seconds

to make it more simple I add user + sys and results are

10 rows           13.867 seconds
100 rows          12.024 seconds
1000 rows         9.988 seconds
10000 rows        11.744 seconds
100,000 rows      12.103 seconds

I am trying to figure out what is main reason that multiple rows insertion is better around 500-1000 lines and it becomes counter productive below 500 lines and above 1000 lines. I search over internet and found different answer.
some mention that it depends on

max_allowed_packet, bulk_insert_buffer_size, key_buffer_size .

I try these parameters but unable to see any effects.

My question is why bulk-insertion is optimum between 500-1000 rows per insert and its counter productive other than this range and what are the main factors.
I am already using this settings

 max_allowed_packet=16M

there are some parameters also.

Table     Non_unique Key_name  Seq_in_index  Column_name
roy_table    0       PRIMARY   1                id

Collation Cardinality  Sub_part  Packed  Null  Index_type
 A       100650        NULL     NULL           BTREE

Can some body please point out a benchmark about bulk insertion efficieny or any idea how can I address why this is counter productive above certain point. I had to give solid reason in my report. I will really thankful for any small hint or idea. thanx


texts: Getting the error log location in an efficient way

texts:
I am using this sql to get my error log location:

EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file'

However, sometimes I have very large log file (about 1gb, and I do not have control over the cycling of these files) the query above takes about 10 seconds to run on these large files, which is too long.

Given that this piece of text is always in the first few lines of the file, it would be good if I could say "give me top 1 and then stop reading", but I don't think the xp_readerrorlog support this?

Or, alternatively, if I knew the date of the first line in the file, I could specify a date range like so:

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
SET @startDate = '2016-01-28 12:00:00'
SET @endDate = DATEADD(mi, 5, @startDate)
EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, @startDate, @endDate

Which is much quicker. However, I don't know how I can work out this date - in many cases it is nearly the same as the start time of the instance process (SELECT login_time FROM sysprocesses WHERE spid = 1 but this is not guaranteed)

I have also tried using master..xp_instance_regread to read the log file location from SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters, but this does not always seem to be accurate.

Does anyone have any suggestions how I could work out the log file location in an efficient way, even when my log files are large.


texts: Restrict access to new database by default

texts:
I am administering a Postgres server for students on a very basic database course. Students get there own accounts, and are allowed to create databases. The problem: when a student creates a database, the default is that another student can connect to it, and create their own tables in it. (They can't select or insert on each other's tables, however.)

They can prevent this themselves with:

revoke all privileges on database the_database_i_just_created from public;

But what I'd like is for that behavior to be default, so that they don't have to remember to issue the revoke command. There doesn't seem to be anything in alter default privileges that does this. Is there any way to accomplish what I want? It doesn't have to be exactly this, just something that prevents users to mess with each other's databases by default.

I have complete control over the server, so changing configuration files or whatever is fine. It's also fine if I have to issue one command for each user – I can do that in the script that creates the students' accounts.


texts: Half of a transaction completes, when the other half fails?

texts:
I have done some searching on SO, MSDN, and various other sources, and I found plenty of questions about transactions, but none that seem to be exactly what I'm dealing with.

Admittedly I'm still very much a junior DBA, though I do understand the concept of ACID and transactions.  I'm working on a SQL script that:

Adds a row to 2 different tables, provided the values aren't already
there
Drops a column if it exists
Adds a column if it doesn't exist
Alters 2 stored procedures that generate dynamic SQL to build a report query

All of the above operations work on their own, and they also work together when wrapped up in (what I believe to be) a single transaction.  What I'm trying to understand is how "part" of the transaction completes (and presumably commits) when another part of it fails.

For the sake of brevity, assume I have working procedures called "ProcessReport" and "RetrieveReport."  The dynamic SQL in both of these procedures starts with "SELECT" and then builds from there.  Test cases:

CREATE TABLE ReportTable (FirstName VARCHAR(100), LastName VARCHAR(100), OrderId INT);
GO
CREATE PROCEDURE ProcessReport
AS
BEGIN
  DECLARE @SQL VARCHAR(4000)

  -- start creating dynamic sql
  SET @SQL = 'SELECT FirstName, LastName, '
  --create rest of dynamic SQL here...

  INSERT INTO ReportTable (FirstName, LastName) EXEC(@SQL)
END
GO

CREATE PROCEDURE RetrieveReport
AS
BEGIN
  DECLARE @SQL VARCHAR(4000)

  -- start creating dynamic sql
  SET @SQL = 'SELECT FirstName, LastName '
  --create rest of dynamic SQL here...

  SET @SQL = @SQL + ' FROM ReportTable '
  EXEC (@SQL)
END
GO

Now that the procedures exist, here's what has me kerfuffled:  when I purposely enter the wrong name for the first ALTER PROCEDURE, the edits I made to the second procedure are completed.  Below is the short version of the transaction I've written:

BEGIN TRANSACTION
GO

ALTER PROCEDURE ProcessReport2  -- this procedure does not exist, causing an error
AS
BEGIN
  DECLARE @SQL VARCHAR(4000)

  -- start creating dynamic sql
  SET @SQL = 'SELECT FirstName, LastName, '
  --create rest of dynamic SQL here...

  INSERT INTO ReportTable(FirstName, LastName) EXEC(@SQL)
END
GO
ALTER PROCEDURE RetrieveReport
AS
BEGIN
  DECLARE @SQL VARCHAR(4000)

  -- start creating dynamic sql
  SET @SQL = 'SELECT DISTINCT FirstName, LastName '  --DISTINCT added
  --create rest of dynamic SQL here...

  SET @SQL = @SQL + ' FROM ReportTable '
  EXEC (@SQL)

END
GO

IF @@ERROR = 0
  COMMIT TRANSACTION;
ELSE
  ROLLBACK TRANSACTION;

When running the above statement, I get the below error:

  Msg 208, Level 16, State 6, Procedure ProcessReport2, Line 2
  Invalid object name 'ProcessReport2'.

  Msg 3902, Level 16, State 1, Line 3
  The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

With regard to ACID and transactions, it was my understanding that either everything between BEGIN TRANSACTION and COMMIT TRANSACTION completes, or nothing does.  In SSMS, when I click "Modify" on the RetrieveReport procedure, I now see the dynamic SQL starting with "SELECT DISTINCT..."

Can anyone tell me where I'm going wrong here?  Is it something in my

IF @@ERROR = 0
COMMIT TRANSACTION;

ELSE
ROLLBACK TRANSACTION;

My intent with that was "if there are no errors, commit the whole thing, if there are errors, don't do anything."

I'm using SQL Server 2012 Enterprise.

Any help and/or insight will be greatly appreciated.


texts: Repacking RDS database with pg_repack isn't online

texts:
While running pg_repack on a RDS Postgresql 9.6.3, got this failure for one of writers:

ProgrammingError: permission denied for relation log_24034858
CONTEXT:  SQL statement "INSERT INTO repack.log_24034858(pk, row)
VALUES(CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.id)::repack.pk_24034858) END, $2)"

But pg_repack should allow writes to repacked tables, why did it fail here?


texts: ORA-01031: insufficient privileges, cannot login as any user

texts:
I ran below command and restarted docker container.
alter system set processes = 1 scope = spfile;

after this I am not able to login to DB at all.
root@30b2f9030f89:/u01/app/oracle/product/11.2.0/xe/bin# sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Wed Aug 5 14:59:14 2020

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges

SQL>

I tried to resolve it with below command, tried login again, still getting same error
orapwd file=filename password=password entries=100

Also tried editting /u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora file
and manually assign processes=100 and back to processes=1
this didnt help.
can someone guide here please?


texts:
I'm trying to figure out how to write this query.

Table 1 - Booking

BOOKING_ID | BOOKING_DATE | etc...
1          | 2013/01/01   |
2          | 2013/01/01   |

Table 2 - BOOKING_PROCEDURE_TYPE

BOOKING_PROCEDURE_TYPE_ID | BOOKING_ID | LAST_UPDATE_TIME | COMMENTS
1                         | 1          | 10:00            | blah blah
2                         | 1          | 08:45            |
3                         | 1          | 06:00            | referred by

What I want is to join table two where the booking ID matches, but only include the most recent update for each row.

I've written the query 20 different ways, but I keep ending up with a row for every instance of BOOKING_ID.

This is the closest I've gotten so far

SELECT bpt.*
FROM pas.BOOKING_PROCEDURE_TYPE bpt
INNER JOIN (SELECT
                b.BOOKING_ID,
                MAX(b.LAST_UPDATE_TIME) as LUP
FROM pas.BOOKING_PROCEDURE_TYPE b

GROUP BY b.BOOKING_ID)x on bpt.BOOKING_ID = x.BOOKING_ID AND bpt.LAST_UPDATE_TIME = x.LUP

I know I'm probably making some really obvious stuff up and would appreciate some help from better people than I


texts:
I have a Access 2013 frontend linked to a 2014 SQL server. When users leave their workstation with their copy of the frontend open their computer will go often into sleep mode and lose their ODBC connection to the SQL server. When they return their session on the database comes to a violent end causing them to open the task manager and close the application down. Not a pleasant user experience.
How can this situation be trapped? Does something exist that upon return with an ODBC call failure a message box can appear with the button to close file? Ideas?


texts: How to deal with `.partial` WAL segments generated by Postgres `pg_receivewal` command?

texts:
pg_receivewal creates a .partial file for incomplete WAL segment. My question is how to deal with this file during recovery. If I just leave it as it is, last transactions don't recover. If I rename the file removing .partial post-fix, the recovery procedure crashes with the error:

FATAL:  archive file "000000010000000C00000080" has wrong size: 152 instead of 16777216

The only way I found is to remove this .partial segment after the previous recovery crash and start the procedure again. This way db seems fully recovered.

What the proper way to deal with these .partial segments?

My db version is 11.5.

Update:

Following Laurenz advice I ended up with running next command right before starting the recovery.

it testes if there's no another version of the partial segment
decompresses it (as I use pg_receivewal with --compress flag)
augments it with nulls
and copies the fixed segment into pg_wal folder

find /backup/wal -name "*.partial" -exec sh -c ' \
    NAME=`basename "$0" .gz.partial`; \
    DEST=$PGDATA/pg_wal/$NAME; \
    test ! -f $DEST && \
    zcat $0 > $NAME && \
    truncate -s16M $NAME && \
    mv $NAME $DEST \
' '{}' \;

Notice it's relying on $PGDATA environment variable which usually isn't set.


texts: What is the difference between xact_start and query_start in postgresql?

texts:
I am reviewing over the pg_stat_activity view. And there are two fields that are indistinguishable to me:

  xact_start:The time at which the transaction started to run which
  could be different than the query_start but should always have
  occurred before the query_start time.

  query_start: The time at which the query started to run. This can be
  useful to determine if a query has been running for a long time.

It says they both determine the start time of the query. It also says that xact_start can be different than query_start, but doesn't explain why - only that it should start prior to query_start. But what is the actual difference between the two? Why would I care about xact_start?


texts: Write update in more efficient way with joins

texts:
How can I write this update with joins in 'more sql' way? I have been told that this is weird coding:

-- Updates recipient's account
    UPDATE table1
    SET balance = balance + @amount
    WHERE accountID = @recipientID
        AND -- check if a currency is the same in both accounts
        (
            SELECT t.CurrencyID
            FROM table1 t
            WHERE t.accountID = @payerID
            ) = (
            SELECT t.CurrencyID
            FROM table1
            WHERE t.accountID = @recipientID
            );


texts: Browser service in sql server clustering

texts: Straightforward this one - when failing over from one sql server 2008 node to another, should the browser service be running on the passive node ? At the moment, on attempting to failover, a an error is produced saying that certain clustering related dlls can't be found. The dlls are present, I just wondered whether this browser service was stopping access.


texts: Display values from two tables with multiple conditions

texts:
table1

 id | course
----------
 1  | A
 2  | B
 3  | C
 4  | D

table2

id | course |     date    | flag
-------------------------------
1  |    A   | 02-10-2020  | 1
2  |    A   | 03-10-2020  | 0
3  |    B   | 21-01-2019  | 1
4  |    C   | 12-12-2001  | 1
5  |    A   | 30-12-2019  | 1
6  |    C   | 02-10-2019  | 0
7  |    A   | 01-11-2019  | 1

I want output to be like

 course | Date
------------------------
    B   | 21-01-2019
    A   | 01-11-2019

The conditions are
1) only the courses of table1 that are present in table 2
2) date of the courses should be greater than today
3) only the courses with flag 1 has to be shown
4) choose the closest date to today when there are multiple flag=1 entries
5) output has to be in order by date asc


texts: How do I troubleshoot FILESTREAM_WORKITEM_QUEUE waits in SQL Server 2014?

texts:
Env: SQL Server 2014 SP2 on Windows Server 2012 R2.

I am seeing our top wait as FILESTREAM_WORKITEM_QUEUE, with average waits near 1s and over 5000 waits during a 2-hour period of time.

I haven't been able to find out much on this wait type.  Some recommend to ignore it.  Any more info on this wait and how to address it (or not)?

TIA,
-Peter


texts: Load Testing When SQL Server Caching Is Disabled

texts:
I was doing a load test by sending the same web request 5000 times one after the other. Here are the total data transfer amounts I observed:

From SQL Server to SAN: 6 MB
From SAN back to SQL Server: 1.5 MB
From SQL Server back to IIS Server: 71 MB

And here are the mdf and ldf file size changes in full recovery mode:

mdf size increase: 1 MB
ldf size increase: 4 MB

As you can notice, despite the 71 MB returned to IIS Server, the total data from SAN to SQL Server was 1.5 MB. After doing some research, I figured that this was due to the SQL Server caching data and not hitting the disk for each request.

So, in order to disable this behavior, I used CHECKPOINT; DBCC DROPCLEANBUFFERS; after each request. Here are the results after this change:

From SQL Server to SAN: 390 MB
From SAN back to SQL Server: 11 GB
From SQL Server back to IIS Server: 71 MB

mdf size increase: 1 MB
ldf size increase: 30 MB

My questions are:

1. What is the reason that data transfer from SQL Server to SAN increased from 6 MB to 390 MB? Shouldn't disabling caching have only affected reading from the disk, not writing to it?

2. Data from SAN to SQL Server became 11 GB. Is this a better indicator of the production environment rather than the 1.5 MB?

3. Data from SAN to SQL Server became 11 GB. And I wonder why SQL Server needs to fetch that much data from disk even though it is only returning 71 MB back to the IIS Server. Since I am using appropriate indexes, I assume SQL Server shouldn't be caching a lot of pages.

4. ldf size increase in cache disabled case is almost 8 times the increase in the cached case. Why is this?


texts: One time database shrink catch-22 solution?

texts:
I am developing a plan for SQL Server 2012 EE to properly downsize and type (nchar to char types) some needlessly Unicode nvarchar(max) fields and would like to optimize the database size as part of the downtime by doing a one time shrink. Experiments have shown a 50% allocated space savings which is 11G of data.

After reading and experimenting, it's evident that shrinking a database causes index fragmentation and rebuilding indexes causes the database to expand. A real Catch-22 situation. I don't want to leave 50% free space in the database which is 11G of disk storage in this case.

Would the following be a decent approach for a one time shrink that would allow ending up with non-fragged indexes and up to date index statistics?

o   Backup w/ verification and duplicate backups.

o   Drop all indexes.

o   Rebuild any too fat tables via copy into new tables, then drop and rename tables. This is currently working well.

o   Shrink the database leaving a reasonable amount of free space.

o   Recreate all indexes that were dropped.

o   Validate the database and check fragmentation.

Pointing out any caveats, suggestions, gotchas or alternatives to consider much appreciated.

Thx, Dave


texts:
I have two databases (Mongodb 3.6.5) using ReplicaSet, when I made a split in the network, I am forcing the secondary database to be the primary.

cfg = rs.conf()
cfg.members[0].votes = 0
cfg.members[1].votes = 1
cfg.members[0].priority = 0
cfg.members[1].priority = 1
rs.reconfig(cfg, { force: true })

I already tested insert in the secondary acting as primary and when returning the network and back to secondary to be secondary, the bases were replicated, the original primary was equal to secondary.

Now I unplugged the network again and did several operations on the secondary machine transformed into primary until the size of the Oplog exceeds the maxSize. After some time without doing operations, the size of the Oplog decreased somewhat. I did some more operations to increase the size value of Oplog but the same is not growing as before.

My next step is to return the network, return the secondary as a secondary, and see if it synchronizes.

I would like to know how does this Oplog work?


texts: Database is not reflecting in sh.status()

texts:
I have a 2 shard (s1 and s0 )servers with repl set. s1 has 2 dbs (db-A and db-B) and one of the db is sharded (db-B).

When i do sh.status() from mongos only (db-B) is displayed. I am wondering what i need to do for identifying of db-A by mongos.


texts: Copying a table in SQL Server database to Oracle database

texts:
I am new to database management. I need to copy a particular table "Attendance" in SQL Server database to Oracle database. There is no connection between the two databases. Also, "Attendance" is updated in real-time in the original SQL database.

I have two options:

To pull the data from SQL Server -> Store it -> Push to oracle.
Connect the two databases so as the updates are synchronised in real-time.

Unfortunately, I don't know how to go about this. I have researched on this since a week but haven't found exactly what needs to be done.


texts: Difference between Hash, Merge and Loop join?

texts:
In SQL Server you can specify the join hints:

HASH JOIN
MERGE JOIN
LOOP JOIN

What is the definition of those three join hints, and when should each be used?


texts: For a current task and future references, I am wondering how pl/sql grants (select, insert, update, delete, references, alter, index, all) are categorized from a read/write/execute or read/read-write/read-write-execute point of view? A lot of end users or non-dba folks tend to use the later language when asking for access or grant privileges where I work. Any feedback is appreciated :)


texts:
I understand that to convert to BCNF, we need to take into account all violations and decompose as necessary with each FD.

For example, if we had R(A,B,C,D) with FD's AB->C, B->D, C->A. We can compute the closures of each, {AB}+ = ABCD, {B}+ = BD, {C}+ = CA.

And after going through the algorithm, a valid decomposition into BCNF would be R1(B,D), R2(C,A), R3(B,C).

Where I'm confused is that while this decomposition seems correct following the algorithm, how can it be correct if the first functional dependency AB->C does not seem to be satisfied?


texts:
I use the following INNER JOIN to get my values from SQLite tables books and book_authors:
SELECT *
FROM (SELECT book_authors.book_id,
             book_authors.author_id,
             book_authors.author_name,
             books.title,
             books.rating
      FROM book_authors
      INNER JOIN books
          on books.id = book_authors.book_id)
ORDER BY author_id DESC;

It gives me all the authors info with the extra books and ratings from books table.  Then I realized I want to add an extra book_count column for each author.  So I add COUNT(book_authors.author_id) AS book_count:
SELECT *
FROM (SELECT book_authors.book_id,
             book_authors.author_id,
             COUNT(book_authors.author_id) AS book_count,
             book_authors.author_name,
             books.title,
             books.rating
      FROM book_authors
      INNER JOIN books
          on books.id = book_authors.book_id)
ORDER BY author_id DESC;

Now I only see one row returned to me.  What did I do wrong?


texts: SQL Server returns "Arithmetic overflow error converting expression to data type int."

texts:
When I run this command with SUM()

SELECT COUNT(*) AS [Records], SUM(t.Amount) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 101;

I'm getting,

Arithmetic overflow error converting expression to data type int.

Any idea on what is the cause of it?

I'm just following the instructions in this answer.


texts: How to set Postgresql database to see date as "MDY" permanently

texts:
How can I set my database to see the 'date' as "MDY" without running:

SET datestyle = "ISO, MDY";

every-time I'm trying to access it?
I'm using Postgresql version 9.1, Ubuntu 12.04.
My system locale, at the time of the database installation was set to en_CA.utf8 and I recently changed it to en_US.utf8.

show lc_CTYPE

returns: --> "en_CA.UTF-8"

but

show LC_CoLLATE

returns: --> "en_CA.UTF-8"


texts: Shrinking Databases in a maintenance plan

texts:
Everything I've heard (including from Microsoft) says you should never shrink a database. Yet a software vendor (Symantec) is telling us to do DBCC SHRINKFILE on the SQL 2012 databases and logs related to a product of theirs called Enterprise Vault.

Should I follow the recommendation of Symantec and shrink or the recommendation of Microsoft and everyone else to not shrink?


texts: How statistics play a major role in legacy CE vs new CE

texts:
We have quite some issues going on for one of our SQL server 2014 SP3 after compatibility change to 120 from 100.
Yes i am aware of few hints to make use of legacy CE and have implemented as success. However i am trying to understand exaclty what changes with stats for table over 1 Billion rows and approx some 500 Gb size
For the database in question we have some 8-10 VLtables starting with 500 M rows gows all the way to 4 billion rows. Update stats had been a problem with higher sample rate so we just do default sample rate.
like UPDATE STATS "IX_NAME"
I have couple of questions - does new CE prefers to have better stats like updating stats on higher sample rate?
If yes how better can i plan to update stats for very large tables so that they do not run for hours and just finish in time if we go with daily stats?
FYI- Auto update stats is on but for VLTB they hardly come into effect because of old algorithim


texts: Looking at the sys.service_queues DMV, I see that there are columns called "is_enqueue_enabled" and "is_receive_enabled".  However, in the "ALTER QUEUE" DDL, there only seems to be a "STATUS" that can be toggled.  Similarly, in the SMO for a queue, there seems to be only a "IsEnqueueEnabled" property.  My question is: will it always be the case that is_enqueue_enabled and is_receive_enabled have the same value?  If not, how does one set them independently?


texts: Cannot view SQL Server Agent in SSMS v17

texts:
I'm logged into two accounts. The SA account and my personal account. On my SA account, I can see the SQL Server Agent node, and on the other I cannot. They are both connecting to the exact same server and instance.

In my personal user properties I have all roles including SQLAgentOperatorRole, SQLAgentReaderRole and SQLAgentUserRole checked. I'm also a sysadmin.


texts:
I'm trying to optimize several queries that all use a similar pattern on one of the WHERE clauses:

AND (DATEADD(DAY
            , ISNULL(a.[due_days], 30) + 30
            , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL))
            ) < GETDATE()

The CalcDate udf based on the type field value makes some comparisons and returns a date. Then adds an amount of days to that date and compares to current date. In order to be able to use an existing index on due_days I want to transform the operation to apply all the transformations to GETDATE(), let's say I want to make it sargable, if possible. Also, if there is some recommendation on what can be done to improve the use of the udf even better.


texts:
This is a follow-on from a previous question.

I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is for coupons. The coupons are to be issued periodically, usually every six weeks although there will also be ad-hoc issuance - eg for a special event. There are 15 million customers and for each issuance event, every customer will receive 6 different coupon types, giving a total of 90 million coupon instances. We need to track the coupon instance redemption data and maintain this for 6 months, although typically a coupon is only valid for six weeks. Any redemption request for an invalid coupon will not reach the database because it will be validated by the POS till.

Over a six month period we'll need to store up 1 billion rows in the Coupon Instance table and up to 200 million (assuming max 20% redemption rate) in the Redemption table.

There will be two main queries to these tables:
1. select from coupon instance where coupon barcode = x (for redemption)
2. select from coupon instance where loyalty card number = x (to issue a coupon via till printer)

To help with the 1st query I could partition the instance table by issuance event and then embed the issuance event in the barcode. So, if we have an issuance event that creates 50m records in the instance table, we have a single partition for these records - SQL is then able to go to the correct partition for a given barcode substring (a 2 digit issuance event number)

What about the 2nd query? I guess it's not possible to partition two ways? If I were to implement this using database sharding then my sharding algorithm would dictate that I need to store the same record on multiple shards in 50% of records. This would work but it's expensive in storage space and I'd like to avoid sharding because of the "re-balancing" required when an addition shard is added

Is there any special way partitioning can help here or should I simply partition to help with the 1st query and resign myself to seek of a full table high index for the 2nd?

Thanks

Rob


texts: How to update group no incremental by 1 based on every group from 1 to 3?

texts:
I work on SQL server 2012 I face issue i can't update group no to every steps from 1 to 3
meaning i need every group from 1 to 3 take identity number incremental by 1 as 1,2,3 etc ..
create table #Replacement
       (
       PartIDC  INT,
       PartIDX  INT,
       FlagStatus nvarchar(50),
       HasReplacement nvarchar(50),
       groupId int,
       step tinyint
       )
    insert into #Replacement (PartIDC,PartIDX,FlagStatus,HasReplacement,groupId,step)
    values

 (1222, 3421, 'Invalid', 'Critical', NULL, 1),
 (3421, 6421, 'Valid' ,    'Active' ,   NULL, 2),
 (1222, 6421, 'Valid' ,    'Chain',     NULL, 3),
 (5643, 2243, 'Invalid', 'Critical', NULL, 1),
 (2243, 3491, 'Valid' ,    'Active',   NULL, 2),
 (5643, 3491, 'Valid' ,    'Chain',     NULL, 3)

select * from #Replacement
Expected result
   PartIDC PartIDX FlagStatus HasReplacement groupId step
    1222 3421 Invalid           Critical      1      1
    3421 6421 Valid              Active       1      2
    1222 6421 Valid           Chain           1      3
    5643 2243 Invalid           Critical      2      1
    2243 3491 Valid           Active          2      2
    5643 3491 Valid           Chain           2      3

always step 1 and step 3 are both equal on PartID C
always step 1 and step 2 are both equal on PartID x from step 1 equal to PartID C from step 2 .
so How to do Expected result above by update statement to group Id ?


texts:
from the error message below:

  A compressed index is not supported on table that contains sparse
  columns or a column set column

as I was rebuilding an index and changing the data_compression to page

This was after making good use of sparse column or compression data wonderful scripts.

Question:

what are column set columns and how to find tables with either sparse or a column set column?


texts: How do I force windows to use a specific version of SQLCMD?

texts:
I have several versions of SSMS installed on my machine. We have legacy servers running several versions of SQL Server, mainly 2008, 2012, and 2014. I use SQLCMD all the time, but it seems to default to the Version 10 version of the command.

  C:\Program Files\Microsoft SQL Server\100\Tools\Binn

I need to use the one from the newer version for some of the new servers that don't have backwards compatibility enabled.

  C:\Program Files\Microsoft SQL Server\110\Tools\Binn

What settings do I need to change so that I don't have to use the full path every time I want to use SQLCMD?


texts: Sync logins and Agent jobs across all replicas in Distributed Availability Group

texts:
I am using dba_CopyLogins stored proc to sync logins on replicas in Distributed Availability groups, but the database level permissions are not transferred due to database sync operation is going on..
Is there any way to sync all logins and permissions from global primary to forwarder and other replicas?
Also, how to sync SQL agent jobs using any automated way?


texts: Shared network location in Availability Groups configuration

texts:
I am configuring Availability Groups feature in azure virtual machines nodes. In the below screen wizard, it's asking me for shared location. where and how I need to create this shared location?

Do I need to have new VM with disk attached, which can be used as shared location?

OR

Do I have to create FILE SHARE feature in azure storage?


texts: Most efficient way to set up a database handling millions of links?

texts:
I'm looking at making a site that will potentially have up to a million users, and each user will have hundreds/thousands of links connected to a set of sites, as well as other info. I'm using mysql and was thinking of setting up the database with tables for each site in the set along with tables for the other info...

Table 1

-----------------------------
|UserID  |  Links for site 1|
-----------------------------
  512    |  www.site1.com/4564
  512    |  www.site1.com/1298
  512    |  www.site1.com/09312
  512    |  www.site1.com/126543
  512    |  www.site1.com/231
  6143   |  www.site1.com/32
  6143   |  www.site1.com/566478

Table 2

-----------------------------
|UserID  |  Links for site 2|
-----------------------------
  512    |  www.site2.com/click/45564
  512    |  www.site2.com/click/898
  512    |  www.site2.com/click/7312
  512    |  www.site2.com/click/56543
  512    |  www.site2.com/click/4331
  6143   |  www.site2.com/click/9832
  6143   |  www.site2.com/click/78

.......

Table 20

-----------------------------
|UserID  | Links for site 20|
-----------------------------
  512    |  www.site20.com/564
  512    |  www.site20.com/125498
  512    |  www.site20.com/479312
  512    |  www.site20.com/6543
  512    |  www.site20.com/2713
  6143   |  www.site20.com/32346
  6143   |  www.site20.com/6478

I probably won't be storing the whole link, just the bit on the end and then rebuilding the link before I display it... unless storing the whole link makes my site load faster.

Then of course:

-----------------------------
|UserID  |  referral links  |
-----------------------------
  512    |  www.site1.com/join/4564
  512    |  www.site2.com/register/1298
  512    |  www.site3.com/register/09312
  512    |  www.site4.com/join/126543
  512    |  www.site5.com/referral/231
  6143   |  www.site1.com/join/32
  6143   |  www.site2.com/register/566478

And

-----------------------------
|UserID  |  Profile Links   |
-----------------------------
  512    |  www.site1.com/profile/4564
  512    |  www.site2.com/user/1298
  512    |  www.site3.com/user/09312
  512    |  www.site4.com/profile/126543
  512    |  www.site5.com/user/231
  6143   |  www.site1.com/profile/32
  6143   |  www.site2.com/user/566478

And a few more for wishlists, About Me, Profile Pic, etc.

My question is, is this the most efficient way to do something like this?  Or would it be better to use 1 row for all of user 512's site1.com links, and 1 row for 512's site2.com links, etc, since I'll usually be pulling all of them almost every time I pull any of them?  If so, how would I do that?
Please explain your answers well, and links to more info would be appreciated, since I'm a complete newbie to databases.

Thanks!

More info...

The average user (95%) will probably have 500-10,000 links in each table (if I don't decide to limit it) and there will be probably between 60-100 site tables. I don't see ever having over a million users, and I'll probably be lucky to have 20,000, but better to plan ahead just in case.

Not all users will have links for all sites.

Data usage-

Most of the time I'll be doing something along the lines of:

Display all site3.com links for user 512.

I'd also like to be able to display a random set of (for example) all site5.com links.

I assume this means indexing both columns of my table?


texts:
Is it possible to pipe the result of a mysqldump straight to rsync as the source argument?

Conceptually, I was thinking something like:

mysqldump -u root -p database_to_backup > db_backup_file.sql | sudo rsync -avz db_backup_file.sql myuser@mysite.com:/var/www/db_backup_file.sql

I've seen people pipe the result to mysql for their one liner backup solution, but I was curious if it was possible with rsync. You know--- cause rsync is magic :)

Thanks for your time!


texts:
I have two SQL Server 2016 Always On nodes - (VMware Virtual Machines). Each node has 250GB RAM, 46 vCPU @2.5GHz. Each of the MS SQL VM is dedicated to 1 ESXi Host so there is no resource contention.

The application that would connect to the Database is expected to be: 70% WRITE and 30% READ.

I need advise on the best way to:

Configure MS SQL nodes to use the 250GB RAM, 46 vCPU efficiently and optimally.
Perform Write and Read as fast as possible using all the hardware resources.


texts: I need query to provide distinct number of choices

texts:
I have eight numbers in my database.
I need to return 28 distinct combinations.
I thought I had the code correct but it returns an infinite number of combinations.

Here is the code.

SELECT t1.ID AS Number_1, t2.ID AS Number_2, t3.ID AS Number_3
     , t4.ID AS Number_4, t5.ID AS Number_5, t6.ID AS Number_6
FROM cTop8 AS t1, cTop8 as t2, cTop8 as t3, cTop8 as t4, cTop8 as t5, cTop8 as t6
where t2.id<>t1.id and t3.id<>t2.id
AND t3.id<>t1.id and t4.id<>t3.id
AND t4.id<>t2.id AND t4.id<>t1.id
and t5.id<>t4.id AND t5.id<>t3.id
AND t5.id<>t2.id AND t5.id<>t1.id
and t6.id<>t5.id AND t6.id<>t4.id
AND t6.id<>t3.id AND t6.id<>t2.id
AND t6.id<>t1.id ORDER BY rand()


texts: Oracle sequence generation performance during persists of large data sets

texts: In the context of an Oracle sequence, when persisting > 200 million records, is there a performance hit on the database if the sequence cache is the default value of 10000?  That is to say does modifying this value to something around 1,000,000 give me any benefit during a transaction where > 200 million sequences will be used?


texts: Oracle Discoverer like app native for SQL Server?

texts:
Can someone point me at some web based apps that work like Oracle Discoverer but native for SQL Server.  We already the SQL connector running to actually use disco, but was wondering what other options there were.  Not really interested in setting up connections inside of excel.

thanks.

--update 3/15/12

Oracle Discoverer is a set of BI tools that enable web based access to your data.  I'm looking specifically for the ad hoc data manipulation it does, as well as the admin tool set that exposes data, defines relationships, manaages data access, and defines calculated data items.


texts: Oracle: Delete row in a table using triggers while deleting value in another table

texts:
So, I basically have those 3 tables:

tbl_albums ----> (id, name)

tbl_album_has_songs ----> (id_album, id_song)

tbl_songs -----> (id, name)

I need to write a trigger that deletes a row from table tbl_album when there are no more songs associated with that album on tbl_album_has_songs.

To do this, I need to count how many times a certain tbl_albums.id = tbl_album_has_songs.id_album appears on tbl_album_has_songs. If count is 0, then I delete the album from tbl_albums.

I've made the following trigger:

CREATE OR REPLACE TRIGGER delete_album after update or delete on tbl_album_has_songs
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
declare lv NUMBER;
begin
select count(*) into lv from tbl_album_has_songs where id_album =:old.id_album;
if lv = 0 then
delete from tbl_album where album.id = :old.id_album;
end if;
commit;
end;

but when I tried to delete some songs from tbl_album_has_songs it throws the following error:

Error starting at line 1 in command:
INSERT INTO tbl_equiporder VALUES (1,101,'11-sep-13',1000,1)
Error report:
SQL Error: ORA-04091: table S24585181.TBL_EQUIPORDER is mutating, trigger/function may not see it
ORA-06512: at "S24585181.TRIGGER_EQUIPORDER", line 9
ORA-04088: error during execution of trigger 'S24585181.TRIGGER_EQUIPORDER'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

Any help?


texts: MySQL: Show list of tables with paging in Terminal

texts:
I am using MySQL Ver 14.14 Distrib 5.5.44 for debian-linux-gnu.

How can I show my tables page wise in a terminal?


texts: Impact of additional int field as primary key

texts:
Directly to the point, at one time I have a table like this:

create table item(
    item_id varchar(50),
    constraint pk_item1 primary key (item_id)
}

However from my discussion one day, there is other option to include another id in int datatype as the primary key. This is to make the design as uniform as it can (every table has a primary key named id).

create table item(
    id int,
    item_id varchar(50),
    constraint pk_item1 primary key (id)
}

I don't seems agree with the second design as it add additional uneccessary cost and space. However I cannot find any silver bullet reason for the refusal. What is the worst impact of this design?

Please note I understand very well with the duplicated value in which can be handled with unique index. I also understand the header-detail problem, in which can also be handled with unique index.


texts:
Let's say we've logged on under the A schema.

I am able to execute the following statement

grant select on wph_pden to LANDING_R;

However, the LANDING_R object can not be located in the schema A. It is absent from all the system tables and views.

How do I find out which schema owns the LANDING_R (which I know is a role, not user)???


texts: Alternative to cross server query in SSIS

texts:
I need to do a cross-server query in SSIS and have been having a hard time figuring out how to do it. Here is what I am trying to achieve:
SELECT Name, Email
FROM Server1.dbo.table1
WHERE UniqueID IN (SELECT UniqueID FROM Server2.dbo.table2)

The inner query (which is on a separate database on a separate server) will return a small list of identifiers - no more than a few dozen. Instead of explicitly doing a cross-server query, I would like to do the following:

Fetch the inner query and save it in a variable (var1).
Execute a second query that like this:

SELECT Name, Email
FROM Server1.dbo.table1
WHERE UniqueID IN var1

I think this should be pretty simple to do, but I don't know how to implement it. I am comfortable setting up database connections and doing some configurations in the db source object. The parts I am not sure about is how to save the result set from the first query to a variable, and then how to use that variable in the second query. For example I think I need to pass it as a parameter, but I don't know if I have to format the result set into a list of comma separated values, or if I can just pass the object directly to the second query.
If someone could give me some pointers, or maybe even a couple screenshots to demonstrate how to set this up I would be very appreciative.


texts: How to use insert delay with the InnoDB engine and use less connection for insert statements?

texts:
I'm working on an application which involves a lot of database writes, approximately ~70% inserts and 30% reads. This ratio would also include updates which I consider to be one read and one write. Through insert statements multiple clients insert data in database via insert statement below:

$mysqli->prepare("INSERT INTO `track` (user, uniq_name, ad_name, ad_delay_time ) values (?, ?, ?, ?)");

The question is should I use either insert_delay or use mysqli_multi_query mechanism because insert statement utilizes ~100% cpu on server. I am using the InnoDB engine on my database so insert delayed is not possible. Insertion on server is ~36k/hr and 99.89% read, also i am using select statement there retrieve data seven times in single query, this query takes 150 seconds on server to execute. What kind of technique or mechanism can I use for this task? My server memory is 2 gb, should i expand memory?.  Have a look on this problem, any suggestion will be grateful to me.

Table's structure:

+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL              | auto_increment |
| user            | varchar(100) | NO   |     | NULL              |                |
| uniq_name       | varchar(200) | NO   |     | NULL              |                |
| ad_name         | varchar(200) | NO   |     | NULL              |                |
| ad_delay_time   | int(11)      | NO   |     | NULL              |                |
| track_time      | timestamp    | NO   | MUL | CURRENT_TIMESTAMP |                |
+-----------------+--------------+------+-----+-------------------+----------------+

My database present status, it shows 41k insertions (writes), which is very slow for my database.


texts: Normalising a database for monitoring stations

texts:
I have data about monitoring stations. For now it is stored in Excel tabs. I have decided to try to make a database. It will be my first contact with databases. Here is an example how it looks now in Excel

Typ   Station name   Typ of area     Location     long    lat    Time/date/concent
pm      Kittsee        suburban     background
SO2   Oberschützen      rural        traffic
SO2     Kittsee        suburban     background
....
....

As you can see some stations occur two or more times. So I have decided to put them in another tab as

typ_id  typ
 1       pm
 2       SO2

then put station_id and typ_id together in the next tab

typ_id  station_id
 1         1
 2         1
 2         2

So my question is can I use this approach for location and area? So columns in my main table will look like this:

station_id typ_id station_name area_id location_id long lat time_date_conc


texts: Selecting rows with condition on two columns together

texts:
i have table with columns
Number and VAT
How can i select all rows without row which is Number=123 and VAT=15 ?
I tried something like this

SELECT * FROM tablex WHERE <.. zilions of conditions..> AND (Number=123 AND VAT!=15)

But it does not work, i need every rows and with Number 123 too but not in case if VAT is 15.
Any idea ? Thank you


texts: Check if a user exists in a SQL Server database

texts:
I'm working with SQL Server 2012. I want to check if a user exists before adding it to a database.

This is what I have tested:

USE [MyDatabase]
GO

IF NOT EXISTS (SELECT name
                FROM [sys].[server_principals]
                WHERE name = N'IIS APPPOOL\MyWebApi AppPool')
Begin
    CREATE USER [IIS APPPOOL\MyWebApi AppPool]
    FOR LOGIN [IIS APPPOOL\MyWebApi AppPool] WITH DEFAULT_SCHEMA=[dbo]
end
ALTER ROLE [db_owner] ADD MEMBER [IIS APPPOOL\MyWebApi AppPool]
GO

But, this code SELECT name FROM [sys].[server_principals] doesn't return if that user exists in MyDatabase.

How can I check if an user exists in MyDatabase?


texts:
I found this script and find it is very helpful to me. I am new here and not good in coding. Can someone explain to me the below script the **'{All Users}'means? represent of? Appreciate your answer and reply. Thank you very much!
--List all access provisioned to the public role, which everyone gets by default
SELECT
    [UserName] = **'{All Users}',**
    [UserType] = **'{All Users}'**,
    [DatabaseUserName] = '{All Users}',
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Roles
    sys.database_principals roleprinc
LEFT JOIN


texts: problem running xp_cmdshell when Application Name set in connection string

texts:
I have an app on 10 or so servers that runs a few xp_cmdshell statements against MSSQL 2008. It works fine on all the servers except for one. To make things worse, I can run all the commands in SQL Management Studio, but in the app, they don't work. I even made a temporary app to test and it works fine! But in the deployed app, I get a simple SQL error "Access Denied". I have narrowed it down to the connection string, if I include the application name

Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};Application Name=TheGroovyApp

It throws access denied only when calling xp_cmdshell, normal SQL statements works fine. But if I remove the application name

Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}

It works fine for both normal SQL statements and calls to xp_cmdshell. The strange thing is, its only happening on one of the ten servers. The only difference being that the server has SP1 and the others do not.

@@VERSION returns

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

I am thinking that there is some sort of authentication that can be granted to applications, but I cant seem to find anything. I can replicate it in SQL Managment Studio by adding

Application Name=TheGroovyApp

to the Additional Connection Parameters tab on the Connect to Database Engine dialog when you create a new query or change its connection.

The simple test statement I use is

EXEC master..xp_cmdshell 'DIR F:\SomeDirectory'

If anyone could shed some light on what is happening it would be much appreciated.

EDIT:

OK after a bit more investigation its even more confusing.

If I set the Application Name to the following which is the default for .Net connections, it works fine.

Application Name=".Net SqlClient Data Provider"

I can run xp_subdirs without any problems no matter which settings I use

EXEC master..xp_subdirs 'F:\SomeDirectory'

Now this is where it gets really weird. The first two fail, but the last one succeeds with the application name being set to my application name. But only if its xp_cmdshell that get called, xp_subdirs works with all three.

With Application Name in connection set

EXEC master..xp_cmdshell 'DIR F:\SomeDirectory' - Fails
master..xp_cmdshell 'DIR F:\SomeDirectory' - Fails
xp_cmdshell 'DIR F:\SomeDirectory' - Works
EXEC master..xp_subdirs 'F:\SomeDirectory' - Works
master..xp_subdirs 'F:\SomeDirectory' - Works
xp_subdirs 'F:\SomeDirectory' - Works

With Application Name not set in connection

EXEC master..xp_cmdshell 'DIR F:\SomeDirectory' - Works
master..xp_cmdshell 'DIR F:\SomeDirectory' - Works
xp_cmdshell 'DIR F:\SomeDirectory' - Works
EXEC master..xp_subdirs 'F:\SomeDirectory' - Works
master..xp_subdirs 'F:\SomeDirectory' - Works
xp_subdirs 'F:\SomeDirectory' - Works

The error returned in the query messages area in SQLMS when it fails

Msg 10011, Level 16, State 1, Line 1
Access denied.

This error message is only happening on the one server, I cannot replicate this on any others.


texts: How to compare time portion of dates for multiple dates

texts:
I have a postgresql database with various records which have a date column which looks like 2020-10-12 12:45:55. I am wanting to construct a query that looks for activity with timestamps that are on any day, but between 22:00:00 of one day and 05:00:00 the next day in the actual time portion.
How do I ignore the date and just focus on the time portion of this column? I think I need to split up the date column, but still be able to use < and > operators on the time string which is left over.


texts: Why is the Postgresql planner choosing the slower merge join (instead of hash join)

texts:
I'm using Postgres 9.6 on AWS

I've run into an issue where depending on the data range for the query the planner will chose to do merge joins (requiring slower sort operations). When the date interval for the query is 2 months it does a hash join.

set enable_mergejoin = off forces the planner back to hashjoin but is not something I want to do in production.

The query in question looks like this:

select  groupname,  count(person_id) as thecount
from (
  select distinct  S.first_device_type  as groupname, A.person_id
  from event_page as O
    join alias as A
    on (O.person_alias = A.alias)
    left outer join (
      session as S
        join alias as A1
        on (A1.alias = S.person_alias)
    )
    on (
      (O.session_id = S.session_id)
      and (A.person_id = A1.person_id)
      and S.first_seen between '2017-01-12 23:24:57.896' and  '2017-04-13 22:24:57.896'
    )
  where
    O.timestamp_ between '2017-01-12 23:24:57.896' and '2017-04-13 22:24:57.896'
) as B
group by groupname;

The above query is from analytics database and is joining page views to sessions and counting the unique people that used a given device type.

When I run the above query for two month I get this plan:
https://explain.depesz.com/s/tREc

When I run the above query for four months I get this plan:https://explain.depesz.com/s/yqV

Why does the planner choose the less efficient (in this case) merge join which requires sorting for the longer query? Is there anything I can do to optimize the above query without doing set enable_mergejoin = off;?


texts: Teradata SQL Assistant - List tables under DBC in the Database Explorer

texts: I am able to query, but under DBC, in the Database Explorer, I do not see the list of tables. That also makes it impossible for me to qualify name and get any kind of ide support while writing my queries.


texts: Replacing Database in High Availability Group Enviroment

texts:
Looking for some advice regarding replacing a database within a SQL 2012 High Availability Group environment setup as synchronous with auto failover. A little bit of background on the reason for this.

Every couple of months we get a MDB database from a supplier which then need to be imported into a SQL database. This is done by 2 step job that first truncates current database and then recreates and then a SSIS package is run to fill the database.

As I'm trying to automate the full process any suggestions and advice would be greatly appreciated.

I know I need to first remove the database from the availability group and then apply the changes, but was interested to find out what the best approach with the secondary is and best practice. Im guessing I can either delete the database from the secondary and then add it back into the Availability group or is it better to take a backup of primary and restore over secondary before adding back to HA


texts: Why does START TRANSACTION WITH CONSISTENT SNAPSHOT not work with SERIALIZABLE isolation level?

texts:
The MySQL doc for START TRANSACTION says:

  The only isolation level that permits a consistent read is REPEATABLE READ. For all other isolation levels, the WITH CONSISTENT SNAPSHOT clause is ignored. A warning is generated when the WITH CONSISTENT SNAPSHOT clause is ignored.

I'm surprised, as I thought that the SERIALIZABLE isolation level, being above REPEATABLE READ, would also offer a consistent read.

What prevents WITH CONSISTENT SNAPSHOT from working with SERIALIZABLE transactions?

I even found this bug report from 2012 where the doc originally said that this worked with both REPEATABLE READ and SERIALIZABLE, and they explicitly changed it to REPEATABLE READ only, so it looks like there is a technical reason for this. I tested it and MySQL does generate a warning when used with SERIALIZABLE.


texts: When does creating a .sql backup with mysqldump become too large?

texts:
Is there a limit to how large a database can be when executing a .sql backup using mysqldump, or will you run into server problems before that - will the server not be able to take such a large backup file?

I'm thinking in the levels of:

1 - 10GB
10 - 100GB
100 - 500GB


texts:
Are database options backed up in a full backup?

For instance, I have a database which has AUTO_UPDATE_STATISTICS disabled (0) (AFAIK, default is 1).
When I restore a full backup of that database, does this option stay disabled(0)?

Can you point me to any reference that mentions that?


texts: Clustered monotonically increased index insert performance

texts:
I have a table with field Id (bigint, IDENTITY) as primary key and clustered index on it.
I inserted 400 rows and saw execution plan. I got: the relative query cost for this insertion 36% and for this query "Clustered Index Insert"'s cost 97%. What's going on? Why does it work so slow?

ADDED:

continue

See also SQL Server insert performance


texts: Optimising MySQL query with lots of self-joins

texts:
I have an entity_relationship table that describes relationships between entities. Each relationship will have 2+ entities involved.

In some cases it can be said that one entity is a constituent of a constituency. This is stored in the entity_relationship table.

I then intend to add entities to the relationships based on this: If entity Joe Bloggs is a constituent of constituency University of Life, and the University of Life is itself a constituent of constituency Made Up Universities then I will add Joe Bloggs into the 2nd relationship as an implied-constituent.

When all the implied-consituent relationships are populated (by running the query until no records are added) I'll have a quick way to find out who's linked to what without needing to do recursion at that stage.

The entity_relationship table looks like:

+-----------------+---------------+
| Field           | Type          |
+-----------------+---------------+
| entity_id       | int(10)       |
| relationship_id | int(10)       |
| type            | enum(...)     |
+-----------------+---------------+

and has keys (currently LOADS of 'em, trying to optimise!)

+------------+----------+--------------+-----------------+-------------+------+
| Non_unique | Key_name | Seq_in_index | Column_name     | Cardinality | Null |
+------------+----------+--------------+-----------------+-------------+------+
|          0 | PRIMARY  |            1 | entity_id       |      179429 |      |
|          0 | PRIMARY  |            2 | relationship_id |      179429 |      |
|          1 | r_t_e    |            1 | relationship_id |      179429 |      |
|          1 | r_t_e    |            2 | type            |      179429 | YES  |
|          1 | r_t_e    |            3 | entity_id       |      179429 |      |
|          1 | t_r_e    |            1 | type            |           8 | YES  |
|          1 | t_r_e    |            2 | relationship_id |      179429 |      |
|          1 | t_r_e    |            3 | entity_id       |      179429 |      |
|          1 | t_e_r    |            1 | type            |           6 | YES  |
|          1 | t_e_r    |            2 | entity_id       |      179429 |      |
|          1 | t_e_r    |            3 | relationship_id |      179429 |      |
+------------+----------+--------------+-----------------+-------------+------+

And then the query I'm trying is:

INSERT INTO entity_relationship
SELECT lt.entity_id entity_id,
       py.relationship_id relationship_id,
       'implied-constituent' `type`
FROM entity_relationship lt,
     entity_relationship ly,
     entity_relationship pt,
     entity_relationship py
WHERE lt.type='constituent'
  AND lt.relationship_id = ly.relationship_id
  AND ly.type='constituency'
  AND ly.entity_id = pt.entity_id
  AND pt.type='constituent'
  AND pt.relationship_id = py.relationship_id
  AND py.type='constituency';

The problem is this is taking 42s to run (even when the query results in zero rows to insert). The output of EXPLAIN (on the SELECT) shows:

+--------+-----+----+-------+---+------------------------+-----+------------------------+
| s._type|table|type|key    |len|ref                     |rows |Extra                   |
+--------+-----+----+-------+---+------------------------+-----+------------------------+
| SIMPLE |lt   |ref |t_r_e  |2  |const                   |89714|Using where; Using index|
| SIMPLE |ly   |ref |r_t_e  |6  |lt.relationship_id,const|    1|Using where; Using index|
| SIMPLE |pt   |ref |PRIMARY|4  |ly.entity_id            |    1|Using where             |
| SIMPLE |py   |ref |r_t_e  |6  |pt.relationship_id,const|    1|Using where; Using index|
+--------+-----+----+-------+---+------------------------+-----+------------------------+

Which looks OK - except perhaps the 3rd line where it does not say Using Index.

Can anyone see a way to optimise this?


texts:
I am trying to pass an variable value in my Select statement where clause, I tried a very simple query like below

Declare v_WK varchar(20);
exec :v_WK :='Wk 6'
Select * from myTable Where Column = :v_Wk

In toad it pop up the filtering window(How do I avoid that)

DATE_DIM table is like below

UNIQUE_ID   WEEK_NAME   WEEK_BEGIN  WEEK_END
1   Week 6, 2018    20180205    20180211
2   Week 5, 2018    20180129    20180204
3   Week 4, 2018    20180122    20180128
4   Week 3, 2018    20180115    20180121

And Fact Table is like

ACCT_ID WEEK_NAME   SALES
10001   20180205    10
10001   20180206    20
10001   20180207    10
10001   20180208    15
1002    20180129    100
1002    20180130    100
1002    20180131    100
1002    20180132    100

Here I need an Output like

ACCT_ID WEEK_NAME   SALES
10001   Week 6, 2018    55
1002    Week 5, 2018    406

So Basically I wanna loop each ACCT_ID like

SELECT *
FROM Fact_Table
where Week_Name between (Select Week_Begin from DATE_DIM WHERE WEEk_NAME =: v_Wknm) /* Here I will have to loop Each Week_name for Each Acct_ID */

This is what I have tried

DECLARE v_Init_Cnt NUMBER(10):=1;
        v_Tot_Cnt NUMBER(10);

BEGIN

    SELECT COUNT(*) INTO v_Tot_Cnt FROM DIM_DATE;

    WHILE(v_Init_Cnt <= v_Tot_Cnt)
    LOOP

    dbms_output.put_line(v_Init_Cnt);

    VARIABLE v_Id NUMBER(10);
    exec :v_Id:= v_Init_Cnt;

    dbms_output.put_line('var' || v_Id);

    SELECT * FROM FACT_TABLE
    WHERE WEEK_NAME between (select WEEk_BEGIN FROM DIM_DATE WHERE UNIQUE_ID :=v_Init_Cnt) and (select WEEk_END FROM DIM_DATE WHERE UNIQUE_ID :=v_Init_Cnt)

    v_Init_Cnt :=  v_Init_Cnt +1;
    END LOOP;

END;

Is there any efficient and correct way to do this in oracle? Thanks in Advance


texts: I have created a website using SQL. I want to know whether my website is safe from the SQL injections or not?

texts:
The basic code which I used for the admin login and admin password is:

</style>

Login

  <form action="login.php" method="post">

      Username:
      <input type="text" name="username" id="username"
class="form-control" value="">

      Password:
      <input type="password" name="password" id="password"
class="form-control" value="">

      <input type="submit" value="Login" class="btn btn-primary">

I am not a security expert but I know some minor attacks like " admin' or 1=1#", "Admin' order by 1=1#" OR "test' or 5=5--" etc.
But none of them could breach the website.

I am always getting the following errors:-

1). You must provide username/mobile number and password
2). Password must be at least 6 characters long
3). This username doesn't exist in our database
4). The password does not match our record

Altogether.

I want to know whether my website is vulnerable to other attacks or not. If yes, then how can I protect it from those attacks.

It is important for me because I am designing this website myself for my new startup business and I don't have much funds yet so I cannot afford any security expert or web developer. So, I myself learned how to design a basic website and made it.

Any kind of help would be appreciated.
Thank you so much in advance!
Have a great day ahead.


texts:
Has anyone seen this before?

When doing a simple sum on a table:

select sum(column)
  from table
 where column is not null

I'm receiving the data 1558.39999999999
However, investigation of the data shows that all data points only go out to two decimal places.

In this case it should be impossible to receive the number above.

using another query:

select x.id, x.value, SUM(x2.value)
from x
     join x2 on x.id >= x2.id
group by x.id, x.value
order by x.id

I was able to find the specific record where this occurs.  The values are:

id, x.value, SUM
id, 3,       1557.68
id, 0.08,    1557.75999999999

When I ask sql server to sum these values I get

select 1557.68 + 0.08
1557.76

Has anyone seen anything like this?  Is there a bug with SUM in t SQL I'm not aware of?


texts: I am using MS SQL Server 2014. Have some kind of DWH. Is there some way to find some kind of stats, which columns from database are used in select clause and how many times, and which columns are used in where clause and how many times ?


texts: Slow Trigger performance when big batches (take2)

texts:
take2 of this post since it was missing information and it was closed prior to the update that I added.

I have an update trigger that inserts into auditing tables. We had no problem until someone decides to update over 1 million records... (That's my bad. I didn't think it would be a problem when developing). Now facing reality, I need to find a solution...

This is a simplified version of the real thing

-- drop trigger PriceHist_trig_U
-- drop table MyPriceTable
-- drop table price_history
Create Table MyPriceTable (SKU varchar(13), PriceGroup varchar(5), PriceLevel int, Price float, Qty float, ManyOtherColumns Varchar(100)
CONSTRAINT [PRICE_TAB_P01] PRIMARY KEY CLUSTERED
(
    SKU ASC,
    PriceGroup ASC,
    PriceLevel ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Declare @Id int
Set @Id = 1

While @Id <= 1000000
Begin
   insert into MyPriceTable values (right('000000000000' + CAST(@Id as nvarchar(10)),13),'Grp ' + CAST(@Id%10 as nvarchar(10)), @id%3, RAND()*(25-10)+10, 1, 'there are many other columns')
   Print @Id
   Set @Id = @Id + 1
End

-- Drop table   price_history
create table price_history (SKU varchar(13), PriceGroup varchar(5), PriceLevel int, Price float, Qty float, ManyOtherColumns Varchar(100), historyDate datetime, ChangedColumns varchar(Max))
CREATE NONCLUSTERED INDEX price_history_nc1 ON price_history
(
    HistoryDate ASC,
    SKU ASC,
    PriceGroup ASC,
    PriceLevel ASC
)

go
Create TRIGGER PriceHist_trig_U ON MyPriceTable FOR UPDATE
AS
INSERT INTO price_history (SKU, PriceGroup, PriceLevel, price, Qty, ManyOtherColumns, HistoryDate, ChangedColumns)
            SELECT INS.SKU,INS.PriceGroup,INS.PriceLevel,INS.Price,INS.Qty,INS.ManyOtherColumns, getdate(),
CASE WHEN update(Price) and INS.Price<>DEL.Price THEN 'Price-' ELSE '' END +
CASE WHEN update(Qty) and INS.Qty<>DEL.Qty THEN 'Qty-' ELSE '' END +
CASE WHEN update(ManyOtherColumns) and INS.ManyOtherColumns<>DEL.ManyOtherColumns THEN 'other-' ELSE '' END
FROM INSERTED INS
JOIN DELETED DEL ON DEL.sku=INS.sku AND DEL.PriceGroup=INS.PriceGroup AND DEL.PriceLevel=INS.PriceLevel
WHERE  (update(Price) and INS.Price<>DEL.Price)
    OR (update(Qty) and INS.Qty<>DEL.Qty)
    OR (update(ManyOtherColumns) and INS.ManyOtherColumns<>DEL.ManyOtherColumns)

/* tests */
update MyPriceTable set price = price-1

I've been doing many tests and researches to try to figure out how to solve my issue of having a trigger perform poorly... I've come to the conclusion that to minimize the bad performance of the "Table Insert" in the execution plan, I need to insert in smaller batches.

The question is: Since I'm not sure of where all the different updates can come from, I'm trying to figure out how I can insert the auditing records in batches within the trigger?

example, The update of the main table for 1 million records would happen and call the trigger, which would insert 100 thousand records at a time in some type of loop.

Is this possible? If so, how do you suggest? If not, how else can I improve the table insert of the execution plan?


texts: Find Candidate Keys by looking to Functional Dependencies

texts:
I'm studying Database Design and I am in trouble with the Candidate Keys and the Functional Dependencies.

I have these Functional Dependences:

B → CEF

G → AD

B → DG

GA → B

My idea is: Since GA is the only one attribute that is not functionally determined by anyone, then, by using Armstrong's Axioms, I see that GA determines all attribute of the Schema S.

So there is only one Candidate Key: GA.

Am I right? Thanks in advance.


texts: Dirty buffer pages after issuing CHECKPOINT

texts:
I am currently working on a test system and due to the nature of the queries I want to optimise, I am trying to simulate a "cold" read a well as I can. Part of that is clearing the buffer cache before performing the queries. From everything I can find dirty buffer pages are supposed to be written during a checkpoint. However, even after issuing a CHECKPOINT, there still seem to be 169 dirty pages of my database in the buffer pool (assessed via SELECT * FROM sys.dm_os_buffer_descriptors WHERE database_id=7 AND is_modified=1).
Is there anything I am misunderstanding about checkpoints or the content of sys.dm_os_buffer_descriptors? If not, why do I still have dirty pages after they were supposedly written away?


texts: How do I automate converting heaps into clustered indexes?

texts:
I have around 40 tables in one of our production databases that, for varying reasons, where not created with a clustered index.

What is the best automated method for converting these heaps?

Since I'm a developer by nature, I really really don't want to do this manually.

I started creating a procedure for this, as documented in Why does this cursor produce results in the incorrect order?, however the responses I got to that post made me question what I was doing.