texts: Disable SQL Server Log Shipping to nonexistent destination

texts:
I am administering a SQL Server 2008 instance in a dev environment. At some point before I became an administrator, someone enabled log shipping from one of the databases on this dev instance to another dev instance. Now, the log shipping destination server no longer exists, and the source instance is creating hourly error log entries indicating the transaction log backups are severely out of sync. When I attempt to remove the destination server or disable log shipping for this database, I receive an error, since I cannot login to the nonexistent destination server.

Short of rebuilding the destination server, which is the last resort option, what can I do to disable log shipping without having to login to the destination server?


texts: Entering multiple entries in SQL where the count increases by one

texts:
I do apologize for the uninspired title, I'm not sure how best to put it.

Another person helped me massively in another question I posted regarding the structuring of data.

The solution has left me with another bit of a brain ache. I will paste some of that users response to me as it reflects what I currently have, I do apologize if that is poor etiquette.

Basically I'm allowing users to store a certain amount of text information each day, for example, 7 entries daily. Entries referenced as things.

The current structure works as follows (UID is Users ID + Date):

Things
PK          PK
UID         thing_id  thing
71420150404 0000001   First thing
71420150404 0000002   Second thing

The user that helped me before came up with the following way to enter data:

INSERT INTO test.Things(UID,thing_id,thing,thing_date)
SELECT
    :uid,
    MAX(IFNULL(thing_id,0))+1,
    :thing1,
    NOW()
FROM test.Things
WHERE UID=:uid
ON DUPLICATE KEY UPDATE thing=:thing1,thing_date=NOW();

This works perfectly, however the problem is only one entry can be added at a time. A workaround I've found is to take all the entries (things) as an array in my PHP and execute the SQL statement over and over for each entry. This seems highly inefficient.

Though I have been getting the basics of SQL through online courses I haven't been able to work out how to a better solution to this through SQL itself.


texts: When to use a TCP dynamic port and when TCP Port?

texts:
I have a new server that is configured using a dynamic port as you can see below:

The application that connects to this server uses the following connection string, but I am getting the following error:

I asked the developer to change the connection port from 61844 to 53187, which is my dynamic port.

After done that and restarting their services, the application could connect to SQL Server.

Is there any specific reason it needs to be using a dynamic port?


texts: Issues with setting a global setting in PostgreSQL 9.5/PostGIS2.2

texts:
I am trying to set a global setting/GUC variable postgis.gdal_enabled_drivers from the PostGIS extension for a while. I am a non-admin user but can ask admin to change settings if needed. But I tried several different methods following the instructions but none worked.

I tried the old fashion way to change environmental variables. I had the following added to /etc/environment:

POSTGIS_ENABLE_OUTDB_RASTERS=1
POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

so that echo $POSTGIS_GDAL_ENABLED_DRIVERS returns ENABLE_ALL

Now, in my non-admin PostreSQL account,

SELECT short_name, long_name FROM ST_GdalDrivers();

returns 0 rows, meaning no GDAL drivers are enabled.

I also tried to have postgres to:

ALTER SYSTEM SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL';

but I got an error:

ERROR:  unrecognized configuration parameter "postgis.gdal_enabled_drivers"

I tried to:

ALTER DATABASE my_db SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL';

but I got:

ERROR:  permission denied to set parameter "postgis.gdal_enabled_drivers"

In sum, I can ask admin to change some settings once as in methods 1 and 2, but not every time e.g. when I do a full database restore (method 3).

My question is:

How can I make the above methods work?

FYI, my PostgreSQL version is 9.5.1, OS is Lubuntu 16.04 beta

    postgis_version
---------------------------------------
 2.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

In method 3, I can use postgres to ALTER DATABASE but I can't ask amin to do that every time I restore db, where I do something like:

dropdb my_db && createdb -T template my_db $$ psql < my_pg_dump_file


texts:
I'm working on loading data from various sources into a db for subsequent NLP applications, and I'm wondering if I could get my db schema critiqued.

gd_id and tc_id each stand in a many-many relationship to nyt_id.
The data will probably be used in two main ways

via grouping the docs by their topic_groups, and then feeding various topic_groups of docs into another software package
using all the docs in the db that meet some sort of filter to train a language model

I will probably also want to do a couple queries as I try to better understand the data. For instance, I'll be sorting the data into topic_groups based on metadata from the specific platforms (in particular, by using metadata like gd_id, tc_id, and subreddit), and I'll want to play with the data to get a sense for what heuristics would and would not work well, and for what natural topic_groups there might be (it'd probably be too much work to do actual topic modelling). The topic_groups I'm thinking of are topics like "politics" or "philosophy"; depending on what the data is like and what results I get, I might go for finer-grained topics.
Thanks in advance!


texts: Automatically execute query in MS SQL Studio every hour

texts:
I support an application in a big enterprise, one of my roles is to clean-up data. There is a query I need to execute every hour, and I would like to automate it. Due to organization policies, I can't create SQL Server Agent jobs or modify schema, I can only manipulate data.

An endless

WHILE(1=1)
BEGIN
WAITFOR DELAY '01:00';
--do work
END

does the job for me, but I shrug at the thought of a perma-open connection.

Ideally, I would script the MS SS itself to execute a given piece of code every hour, but I'm not sure if that is possible.

Is there any solution to this problem?


texts: MySQL server wont' start. Reason: Internal error

texts:
The very vague error message

Reason: Internal error

I've had this happen before, and it usually fixes itself after I restart my machine, but after the 4th time now it still is showing the error :(

Update: Found my.cnf file, details below:

port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 128M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 4
innodb = on
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:512M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 256M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
"my.cnf" [readonly] 67L, 1336C
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
default-storage-engine = innodb
datadir=/var/lib/mysql
user = mysql
symbolic-links = 0
transaction-isolation = READ-COMMITTED
max_length_for_sort_data = 8388608
max_sort_length = 8388608
group_concat_max_len = 32M

net_buffer_length = 1M
max_connections = 32768
back_log = 32768

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
#no-auto-rehash
database = whoat
#safe-updates
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Any idea what is going on? Here is the server log

I was able to start SQL from MySQL Workbench, would like to know how I breached the max open file limit however


texts: Create local table and foreign table from the same SQL

texts:
I want to build a set of PostgreSQL databases that derive from a separate structure definition. The structure is defined as a set of .sql files that will be imported using docker-entrypoint-initdb.d during start.

One of the databases will have regular tables that are created e.g. like this:

CREATE TABLE data."origin" (
    origin_id serial PRIMARY KEY,
    name text UNIQUE
);

Another database needs to be connected to this one using foreign tables that should have the same structure, e.g.

CREATE FOREIGN TABLE data."origin" (
    origin_id serial,
    name text
)
    SERVER localdb
    OPTIONS (schema_name 'data', table_name 'origin');

So the question is, whether there is an elegant way to use the original .sql files from the first database to construct the second one as well.

The only way that we have in mind is to fiddle with sed or similar magic during the image build to add the FOREIGN and the SERVER … stuff in there, remove the PRIMARY KEY etc. This will most likely break sooner or later.


texts: Problem with data insertion in Oracle table from SQL Server table

texts:
We have SQL server and Oracle server, and have configured a linked server from SQL Server to Oracle.

I'm trying to insert 10,000 rows from SQL Server into a table in Oracle.

When I insert 100,000 rows it takes 2 minutes, when I try to insert 1 million rows it can run for more than 10 hours and at the end of this I need to stop the insert because nothing is happening.

Could you please give me answer why it takes so long for 1 million rows? It should take approximately 1 hour if it runs the same speed as 10k rows.

What I'm trying to do:

INSERT INTO [linksrvname]..[schemaname].[oracletablename ] (
  column1,
  column2
)
SELECT top 10000
  column1,
  column2
FROM sqlservertable
;

...completed in few seconds. After that I have deleted all rows from the Oracle table. When I'm trying to insert 100,000 it is completed in few minutes. When I'm trying to insert 500,000 it is running for a few hours now; SQL Server hangs with OLEDB wait type. Unfortunately, I don't have access to the Oracle server.

What could the problem be?


texts:
Trying to understand different memory clerks reported by sys.dm_os_memory_clerk  however, not able to find description of the each individual memory clerk either on web (google and binged) or in SQL BOL. Some of the clerk can be self explanatory based on the name e.g. MEMORYCLERK_SQLXP (assuming it is for extended proc) or SQLCLR etc.

However, some of the memory clerk confused me for e.g. MEMORYCLERK_SQLGENERAL vs MEMORYCLERK_SQLBUFFERPOOL?  OR SQLUTILITIES.

Is there an online reference that tells description of these memory clerks in SQL Server?

ps. The question is not how memory management works in SQL Server. Slava Oak has good blog posts regarding how memory management works but no details on different clerks.


texts: How does MySQL skip ranges (nodes) in a loose index scan?


texts: MySQL Database unresponsive with high network connections but low CPU usage, read and write operations

texts:
Had a spike in traffic that led up to 1.3k open connections for about an 8 minute window during which the database was mostly unresponsive. The CPU usage didn't go up over 10% and nothing extra ordinary in the read and write operations count or memory usage in that window.
FWIW it was an AWS db.m5.xlarge MySQL installation with 4 vCPUs and 16 GB memory.
Having difficulty finding correlated data points on this outage due to spike in network connections. Any ideas? Thanks.


texts: Is this a good way to model course assignments?

texts:
I'm having trouble designing a data model that allows for the following:

Courses can have many assignments.
Assignments can be defined recursively. e.g. "There is reading homework every tuesday".
Users can each complete an instance of the recurrent assignment at their own time and with their own grade and their own priority.

So, focusing on the assignment aspect of these requirements I came up with the following partial ER model: (It's missing how users are related to courses, teachers, etc...):

*There's a mistake in the picture, an event is related to 0 or 1 assignment

A teacher would then create an assignment for their course, let's say: 'Hand in math lab every tuesday due for tuesday next week'.

A single assignment will be created along with all the events for each occurrence.

When a user interacts with a specific event instance of the assignment. The assignmentUser will be created with the corresponding data.

Is this a good way to model this? I'm worried about the recurrent events side of this. Maybe it would be better to only have one event per assignment and generate all assignments that fit the recurrence rule on creation?


texts:
I have a CSV with columns of various data types, including what i would interpret as nested data.

The nested data comes in many forms.

For example:
[13,11,23,18,18,21,23,16,8,20,22,18,20,17,13,10,8,19,16,21,15,12,8,12,14,26,26,20,12]

Another example in a different form:
{"<5":1,"5-20":36,"21-60":149,"61-240":215,">240":86}

I'm currently storing these as strings but i was hoping there was a more flexible data type i could store them as to allow more powerful selection that could allow me to query the nested data.  I'm quite new to postgres but from the research ive done it looks like i have two options

For the first example I could use arrays (https://www.postgresqltutorial.com/postgresql-array/) and for the second example json (https://www.postgresqltutorial.com/postgresql-json/).

So my questions are:

Am I using the right data types or will I run into issues down the line from using these?
Whats the right way to import this?

UPDATE:
When importing this from CSV after trying to use array and json I now get an error 'Malformed array literal'.  I made the quote and escape character " after getting an 'unterminated CSV quoted field error' which now leaves me with this new error.

Found the issue on the above. The CSV I was importing from used brackets ([ and ]) to hold the arrays which was causing the issue.  I changed to curly braces ({ and }) and this fixed the error.

This (https://stackoverflow.com/questions/11170099/copy-import-data-into-postgresql-array-column) got me in the right direction.


texts: Syncing Uncontained Objects with AlwaysOn Availability Groups

texts:
In setting up our HA environment (AlwaysOn AG), I came across the add-in ssms_2012_ag_addin_setup.exe that I got from SQLSkills. http://www.sqlskills.com/blogs/jonathan/synchronize-availability-group-logins-and-jobs/

This is very helpful in tackling the challenge of syncing the uncontained objects from the primary to secondary replicas. This add-in does not include the sync of operators, alerts or credentials. It also does not account for the need to allow any SQL job to execute on failover ONLY. I can add the following to the first job-step to accomplish this.

DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
 DECLARE @RoleDesc NVARCHAR(60)

SELECT @RoleDesc = a.role_desc
 FROM sys.dm_hadr_availability_replica_states AS a
 JOIN sys.availability_replicas AS b
 ON b.replica_id = a.replica_id
 WHERE b.replica_server_name = @ServerName

IF @RoleDesc = ‘PRIMARY’

BEGIN

— Job Step Logic

END

ELSE
 EXEC msdb..sp_stop_job –Job Name

Do you have any better solutions to automating these needs when using AwaysOn Availability Groups as a HA solution.


texts: Does SQL Server require more RAM to hold columnstore indexed tables than traditional rowstore?

texts: I would like to implement (SQL Server 2014) clustered columnstore indexes on some very large, wide tables.  Will I need more RAM to support this?  If so, how do I determine how much?


texts: update column without auto timestamp update

texts:
Hello everyone I have got a product stock table with a time-stamp column so the so any updated to that row will update the time stamp ,I'm happy with this setting but sometimes I want to update the row without updating the time stamp,so how to write mysql statement  that update a specific column without updating the lastUpdate column ?

this my statement:
update productsStock set productName="HP PRINTER 20000" where product_reference="001";

productsStock table description

describe productsStock;

+--------------------+-----------------------+------+-----+-------------------+-----------------------------+
| Field              | Type                  | Null | Key | Default           | Extra                       |
+--------------------+-----------------------+------+-----+-------------------+-----------------------------+
| productID          | int(10)               | NO   | PRI | NULL              | auto_increment              |
| product_reference  | varchar(255)          | NO   | PRI | NULL              |                             |
| productName        | varchar(255)          | NO   | UNI | NULL              |                             |
| brand              | varchar(45)           | NO   |     | NULL              |                             |
| model              | varchar(100)          | NO   | PRI | NULL              |                             |
| productDescription | varchar(255)          | YES  |     | NULL              |                             |
| physicalStock      | int(10) unsigned      | YES  |     | 0                 |                             |
| price              | decimal(7,2) unsigned | YES  |     | 0.00              |                             |
| location           | varchar(45)           | NO   |     | NULL              |                             |
| lowStockAlert      | int(10) unsigned      | YES  |     | 0                 |                             |
| lastUpdateBy       | varchar(45)           | NO   |     | NULL              |                             |
| lastUpdate         | timestamp             | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------------+-----------------------+------+-----+-------------------+-----------------------------+


texts: Table with two customer IDs that I want to relate to the entity

texts:
Let's say I have a transaction where Party1 and Party2 are associated, eg by a transaction - let's say Party1 sells something to Party2.

I have a list of transaction IDs, eg transaction1 is Party1 sells goods to Party2, transaction2 is Party3 sells goods to Party4 and so on.

I have another table, Entities, that tells me that Party1 is 'Smith Enterprises' and Party2 is 'Jones Corp' etc.

I want to create a query where Access puts the name of the party in for both parties, eg the transaction now says 'Smith Enterprises' sells goods to 'Jones Corp'.

I can't figure out how to get a query to look up Party1 and Party2 in Entities and insert the entity name for both. I can do them one at a time and create a list of identified sellers or a list of identified buyers, then join them back together, but I feel sure there must be a more efficient way of doing this.

Apologies if this is either a trivial request or unclear; I'm a 'semi-competent amateur' trying to help myself in the absence of organisational coding resources. But if you tell me which command(s) I need to use, I can probably figure it out from tutorials.


texts: What precautions to take when moving a mirrored server

texts:
I have a SQL Server 2008 R2 mirror setup running in high safety with automatic failover (synchronous, witness).  Over two different nights, the mirror and the primary will be shut down and re-racked.  (Mirror on the first night, Primary on the second night)

My original thought is that maybe I should remove the witness prior to shut down so failover would be avoided during/after this process unless I initiated it.  But, I’m wondering whether there are any other precautions I should take.  The server move should take ~1 hour which is the frequency of log backups as well, so I’m not worried about the transaction log filling on the primary during this time.

Is there any reason to switch from high safety to high performance mode during them move?  I’m a little bit unsure of how these modes might behave differently when the mirroring state is DISCONNECTED.


texts: Information on Oracle MKSTORE utility for creating wallet

texts: I'm looking for more information about the utility MKSTORE that can be used for creating and modifying a Wallet. I would like to know things like what the -createALO option is and what the difference is between -createSSO and CreateLSSO. A link to the information would be fine or a document number on MOS. My goal is to script the Wallet creation and am wondering if these options can help me in any way.


texts: Is there anyway to find the closest matching JSON entry inside a database

texts:
Scenario: suppose I have a list of numbers written in something like JSON stored in a database field. Something like this:

ids{1,2,3}

Let's suppose the database is filled with rows that each have a an IDs JSON field like this

entry1: someUniqueID, ids{1,2,4}

entry2: someUniqueID, ids{1,2,5}

entry3: someUniqueID, ids{1,2,3}

Is it possible with any type of database (sql, nosql, graph etc.) to efficiently structure the database for finding the entry with the closest match? As in
looking for the field ids inside a database that most closely resembles

ids{1,2,3}

entry1 has 2 numbers inside that match which are 1 and 2. therefore 66,6% match

entry2 has 2 numbers inside that match which are 1 and 2. therefore 66,6% match

entry3 has 3 numbers inside that match which are 1,2 and 3. therefore 100% match

the Query would return entry3

I know it's of course possible to read each row and compare the values using code and find the closest match. Of course if you have a large database with many thousands or even millions of rows each holding the ids field. Getting result would take ages.

Is there any database technology that would allow for fast, sub 1 seconds result of this kind, even if ids holds much more than 3 values.

By the way the values don't have to be stored using JSON.  Any Direction to for me to look into would be appreciated.


texts: Self-join example review in Books Online

texts:
Here in this example it states that performs a self-join, but it joins Sales.SalesPerson to Sales.SalesTerritory. I knew that self join means joining the same table to itself but here aren't Sales.SalesPerson and Sales.SalesTerritory different tables? So is this considered self join though the tables are different? or is there a mistake?

source link


texts: MYSQL - Unable to change 'old_passwords' variable

texts:
I have full access to a 'MYSQL 5.0.27-community-nt-log' database one of our new updates for a core app requires PHP 5.4, after installing and configuring I'm stuck at the error regarding old vs. new passwords.

I've followed every thread I can find and I'm pretty sure I understand the process:

Disable 'old_passwords'
Reset the password to the new 41 encryption

But I cannot seem to get 'old_passwords' to be set to 'OFF'. Logging in as root I've tried the following:

I've removed it from the my.cnf file (I've also tried setting it to '0', 'False' etc)
Manually setting it in the query browser both globally and session
Turning the option on/off in MYSQL administrator

and restarted the service but 'show variables like 'old_passwords' always reports as 'ON'.

Changing the password still remains at a 16 length.

I'm sure once I'm able to turn this variable off I should be fine, Any advice?


texts: Confusion with Tuple Relation Calculus Operations

texts:
I came upon a question like,

Given the following relational schemas

Student (studId, name, age, sex, deptNo, advisor)

Department (deptId, DName, hod, phoneNo)

Which of the following will be the tuple relational calculus query to obtain the department names that do not have any girl students?

The correct answer given is

{d.Dname | department(d) ∧ ~ ((∃(s)) student(s) ∧ s.sex = ‘F’ ∧ s.deptNo = d.deptId)}

My interpretation to it  is the for every department, the inner logic is trying to find at least one student who is from the same department and is a female. Now if it fails to find one, it return FALSE and the negation will make it TRUE and thus that Dname will be printed. This is perfectly fine.

But if I negate the logic, i.e flow the negate sign then the inner logic becomes,

((∀s ∈ student) s.sex ≠ ‘F’ ∨ s.deptNo ≠ d.deptId)

How does this mean the same as above? I cannot imagine it.

Any help is appreciated. Thanks in advance.


texts: Is GRANT DELETE TABLE t TO PUBLIC and the revoke the right to DELETE TABLE t FROM user1 possible?

texts:
I have a production database that has GRANTs TO PUBLIC for INSERT, UPDATE, DELETE on specific tables of the production schema, table t1 being one of these tables.
Say I want to create a user user1 that cannot INSERT, UPDATE, DELETE on table t1. Is there a way to revoke these privileges for user1 while keeping the GRANTs TO PUBLIC?


texts: How to setup an ssh connection to a postgres 12 db using dbeaver

texts:
I am currently in the process of trying to connect to my local postgres 12 db. In the past (9.3/9.6) you were able to edit the .pg_hba file to modify which connections were allowed but that file doesn't no exist in 12 (it has been over a year since I have used postgres). I am unsure what the equivalent is or if there is a different method that should be used to connect to the db.

I am struggling to find any tutorials for how to accomplish this. Every one that I have seen so far just assumes that you already have something ready to connect and simply provides the necessary command needed to connect. I do have an ssh key on my local computer which I assume should be sufficient to use to connect.

dbeaver requires the following:

host/ip - localhost
post - defaults to 22
user name - postgres (default)
authentication method - Public Key
Private Key - path to my private key in .ssh

What here am I missing to connect to my postgres db?

Side note - I am unable to connect using ident either since dbeaver always requests a password even if there isn't one


texts:
I am not sure whether this question is suitable for this forum, but since it says Database Administrator I hope I am right. So my question is, whats the database design or business logic for creating an app for messaging between users?

I am having difficulties with choosing how to approach the relationship between each Conversation of User Thread.

If anyone could guide me through as to how all this threading message works (like Gmail or Facebook), I will be really grateful.

Edit:

With such a helpful guide and the tables below, I came up with my models. But having just a slight confusion, as to how to start a new thread.

Tables:

My models:

class Thread(models.Model):
    pass

class ThreadParticipant(models.Model):
    thread = models.ForeignKey(Thread)
    user = models.ForeignKey(User)

class Message(models.Model):
    thread = models.ForeignKey(Thread)
    sent_date = models.DateTimeField(default=datetime.now)
    body = models.TextField()
    user = models.ForeignKey(User)

class MessageReadState(models.Model):
    message = models.ForeignKey(Message)
    user = models.ForeignKey(User)
    read_date = models.DateTimeField()

As the Thread class has nothing in it, how do I create a new thread? Do I place an integer in Thread and increment it? Or do I use the Primary Key itself as the attribute to create a new thred? Please kindly explain it to me. Thank you agian!


texts: How to use two actions and a function in an Extended Events Histogram for deadlocks?

texts:
I'd like to get a histogram through extended events that shows the count of deadlocks per databasename by date, but I'm hitting two walls:

How get a histogram to include two actions
How to get the collect_system_time rounded to just the date -- I don't see a way to use a function on an action.

The (relatively) easy answer would be to query and shred the xel files and produce the result set I want (and then copy/paste to Excel if I want a chart), but I was curious if there was a way to do it directly within an XE session histogram.


texts: Are there practical scenarios where inserts + deletes are faster than updates? (SQL Server)

texts:
I'm just wondering if there are common scenarios where an insert/ delete combination is faster than an update else insert function.

Here is my specific example.

I have to update a database with pages that contain 1000 records at a time. (I cannot merge the pages).

About 5% of these records, or 50 rows, are duplicates that need to be 'updated' rather than inserted as brand new.

I figure instead of an "update based on ID, else insert new row" typical function it might be faster to "insert everything" and delete the duplicates, in one shot, at the very end.

Two reasons:

parallelism. If I want multiple processes working on this task at the same time, well ... I can run into row locks if I have a big commit size and transactions searching for, and updating, IDs at the same time. With the "insert everything" and delete the 'older' records later, I can have unlimited processes writing data at the same time.
I feel it's easy to optimize one big "delete lookup" at the very end. It looks like the following:

with CTE as (
   select primary_id,update_date,
          rn = row_number()over(partition by primary_id order by update_date desc)
   from MyTable
)
delete from CTE where rn > 1

I mean the performance gains are there -- I'm just wondering if this goes against best practices. Can someone see why inserts + delete duplicates seems to work faster than 'update, if not found, insert'?

I can see one danger is that there is a period of time while the Data Load is running that the Table is not accurate (before the deletions). But isn't this sort of true in the middle of any update process?

This would also be a staging table for a data warehouse, not live in-use data. I'm just wondering why I haven't seen this method more often.


texts: Syncronizing process difference between replication and alwayson high availability

texts:
Just wondering if SQL Server use different technology for Transactional Replication and Alwayson High Availability or is it same behind the scene.
Basically I need to know if SQL Server use same or different technology(protocol/process) for Replication and Synchronizing the secondary replicas in High Availability in SQL Server 2016.
Thanks in advance.


texts: Insert JSONL with two columns of comma separated fields into separate rows in mysql

texts:
I have json datas in the format as shown below.
**the number of objects under 'images'(VIEWS, ROOMS, etc.) varied for each JSON object. Not all objects have VIEWS...POOL, it may only have ROOMS.
    "propertyId":{"ea":"12345678","h":"","vo":""},
    "thumbnail":{"width":1000,"height":500,"link":"xxx.jpg"},
    "hero":{"title":"Featured Image","width":1000,"height":669,"link":"yyy.jpg"},
    "images":{
       "VIEWS":[
        {"title":"View from Property","width":1000,"height":666,"link":"a1.jpg"},
        {"title":"View from Property","width":1000,"height":666,"link":"a2.jpg"}
       ],
        "ROOMS":[
            {"title":"Room","width":1000,"height":669,"link":"b1.jpg"},
            {"title":"Room","width":1000,"height":662,"link":"b2.jpg"}
       ],
        "EXTERIOR":[
            {"title":"Terrace","width":1000,"height":666,"link":"c1.jpg"},
            {"title":"Terrace","width":1000,"height":666,"link":"c2.jpg"}
       ],
        "AMENITIES":[
             {"title":"Property Amenity","width":1000,"height":666,"link":"d1.jpg"}
       ],
        "POOL":[
            {"title":"Outdoor Pool","width":1000,"height":666,"link":"e1"},
            {"title":"Outdoor Pool","width":1000,"height":666,"link":"e2.jpg"}
    ]}
    }

I wanted to insert the following data into the table with the following format in MySQL Ver 8.0.23 database.

JSON -> Table Reference
    HotelId = propertyId.ea
    hero_url = hero.link
    hero_title = hero.title
    media_title = images.(VIEWS/ROOMS/EXTERIOR/...).title
    media_url = images.(VIEWS/ROOMS/EXTERIOR/...).link
    >> ...other objects may have different category (VIEWS/ROOMS...)
    created_at and updated_at = date

After searching through the old topics, I was able to use the select statement below to display the format above. However, I'm confused as to it how works when using INSERT.
  SELECT t.HotelID, t.hero_url, t.hero_title,SUBSTRING_INDEX(SUBSTRING_INDEX(t.media_title, ',', n.n), ',', -1) media_title, SUBSTRING_INDEX(SUBSTRING_INDEX(t.images, ',', n.n), ',', -1) images
  FROM hotel_sample.property_photos t CROSS JOIN
  (
   SELECT a.N + b.N * 10 + 1 n
     FROM
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
   ) n
 WHERE n.n <= 1 + (LENGTH(t.media_title) - LENGTH(REPLACE(t.media_title, ',', '')))
 ORDER BY HotelID

Would appreciate if anyone could show me how I could insert the above JSON data into the table. (MySQL Ver 8.0.23)
CREATE TABLE `property_photos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `HotelID` varchar(10) DEFAULT NULL,
  `hero_url` varchar(255) DEFAULT NULL,
  `hero_title` text,
  `category` varchar(30) DEFAULT NULL,
  `media_url` varchar(255) DEFAULT NULL,
  `media_title` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
  PRIMARY KEY (`id`)


texts:
I am new to this platform & community. I'd like to know could there be major issues and bugs when it comes to creating a single database (with, or without the use of SSO) when it comes to scaling or speed.
Is it better to work with SSO, and have the current databases working in parallel, or is it better to deploy the data from those DB's into a new one, and stoping the old databases?

Both are RDB written in SQL. One is gathering the data for 2 web pages, part of a platform and the second DB is gathering the data from a mobile app.
Thank you for your answers.


texts:
We have four nodes in our AG. After the weekly index maintenance job, the log drive became full. We enabled CDC in one of our database, so we disabled CDC to truncate the log. Then we ran 'checkpoint' and took log backup. But we were not able to truncate the log file. The log_reuse_wait_desc status of the database shows AVAILABILITY_REPLICA. We don't see anything in log send and redo queues.

Is there another solution to fix this issue instead of removing the DB from the AG?

Upon troubleshoot suggestion, I have checked everything. There is no blocking in all three secondary nodes. The last_redone_time and redo_queue_size are all in sync with primary replica.

I am not able to suspend and resume the data movement on working days for the time being since it is heavily being utilized. I have checked the log_reuse_wait status of the database, and it is still showing 'AVAILABILITY_REPLICA':

And the loginfo shows that 99% of the log is used.


texts:
I've configured a postgres cluster with pgpool2 using the following link.
The replication seems to be ok, but I ran some benchmarks (pg_bench) and the performance is lower than a single node, for example:

pgbench -c 16 -j 16 -T 600 -S bench2 -h "ONE SINGLE POSTGRES NODE" -p
5432 number of transactions actually processed: 7752147 tps =
12920.095988 (including connections establishing) tps = 12921.661448 (excluding connections establishing)
pgbench -c 16 -j 16 -T 600 -S bench2 -h "PGPOOLNODE" -p 5432 number of
transactions actually processed: 389800 tps = 648.857810 (including
connections establishing) tps = 648.886713 (excluding connections
establishing)
pgbench -c 16 -j 16 -T 600 -S bench2 -h "MASTERNODE"-p 5432 number of
transactions actually processed: 7093473 tps = 11822.379159 (including
connections establishing) tps = 11823.337051 (excluding connections
establishing)

Each node is configured default.
All nodes are identical virtaul machines and over the same network.
This is my pgpool.conf:
listen_addresses = '*'
port = 5432
socket_dir = '/var/run/postgresql'
pcp_port = 9898
pcp_socket_dir = '/var/run/postgresql'
backend_hostname0 = '192.168.1.177'
backend_port0 = 5432
backend_weight0 = 0
backend_data_directory0 = '/var/lib/postgresql/9.1/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '192.168.1.175'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/9.1/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_hostname2 = '192.168.1.176'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/postgresql/9.1/main'
backend_flag2 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = off
authentication_timeout = 60
ssl = off
num_init_children = 32
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
log_destination = 'stderr'
print_timestamp = on
log_connections = on
log_hostname = off
log_statement = on
log_per_node_statement = on
log_standby_delay = 'none'
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
debug_level = 0
pid_file_name = '/var/run/postgresql/pgpool.pid'
logdir = '/var/log/postgresql'
connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'
replication_mode = off
replicate_select = off
insert_lock = on
lobj_lock_table = ''
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off
load_balance_mode = on
ignore_leading_white_space = on
white_function_list = ''
black_function_list = 'nextval,setval'
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 0
sr_check_user = 'ZZZ'
sr_check_password = 'YYY'
delay_threshold = 0
follow_master_command = ''
parallel_mode = off
enable_query_cache = off
pgpool2_hostname = ''
system_db_hostname  = 'localhost'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = 'zzz'
health_check_period = 0
health_check_timeout = 20
health_check_user = 'pgpool'
health_check_password = 'zzz'
failover_command = '/var/lib/postgresql/bin/failover.sh %d %M %m'
failback_command = ''
fail_over_on_backend_error = on
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
relcache_expire = 0

Any suggestion? Thanks


texts:
I have the following sql query:

 SELECT gp.group_name as `Group Name`,
   CONCAT(lc.first_name, ' ', lc.last_name) as `Client Names`
FROM `group` gp
INNER JOIN cbo ON (gp.cbo_cbo_id = cbo.cbo_id)
INNER JOIN credit_officer_has_cbo cocbo ON (cbo.cbo_id = cocbo.cbo_cbo_id)
INNER JOIN credit_officers co ON (cocbo.credit_officers_id_credit_officer = co.id_credit_officer)
INNER JOIN user usr ON (co.user_id = usr.id)
INNER JOIN branches br ON (co.branches_br_id = br.br_id)
INNER JOIN client_has_group chg ON (chg.group_gr_id = gp.gr_id)
INNER JOIN loan_client lc ON (chg.loan_client_id = lc.id)

To Generate the following output

  +-----------+-------------+
  | Group Name| Client Name |
  +-----------+------+------+
  |  Group A  | Joan        |
  |  Group A  | Patrick     |
  |  Group A  | Smith       |
  |  Group B  | Samuel      |
  |  Group B  | Elise       |
  |  Group C  | Marco       |
  +-----------+-------------+

But i need to arrange the 'Client name as rows' as below.

+----------+-------------+-------------+-------------+
|Group Name|   Member 1  |   Member 2  |    Member 2 |
+----------+-------------+-------------+-------------+
|Group A   |   Joan      |   Patrick   |    Smith    |
+----------+-------------+-------------+-------------+
|Group B   |   Samuel    |   Elise     |             |
+----------+-------------+-------------+-------------+
|Group C   |   Marco     |             |             |
+----------+-------------+-------------+-------------+

As you can see the 'Client Name' becomes individual columns. The number of 'Member' columns is unpredictable. I couldn't find a break through to solve this query. Any help would be much appreciated. Thanks in advance!


texts: What does "_localserver_view" mean after an MSDB Database table

texts:
What is equal to the database table
[msdb]. [dbo]. [sysschedules_localserver_view], which contains the suffix _localserver_view?

I am having a permissions problem with a [msdb].[Dbo].[Sp_help_schedule], and I noticed the user I chose to run this stored procedure, select access in the table [msdb].[dbo].[sysbobschedules] more than when accessing the table [msdb]. [dbo].[sysschedules_localserver_view] (which is executed by an internal command from [msdb].[dbo].[sp_help_schedule]) is nothing.

Also note that this table does not exist in the msdb database, or in the table, view, or stored procedure folders.

Why would you want to know what this _localserver_view extension changes in the permissions or in the table view.


texts: Confused by GRANT ALL PRIVILEGES causing Access Denied for database 'mysql'


texts:
okay trying again.  I have a trigger that I want to fire off whenever my order status changes to 'wc-processing'.  So that My trigger doesnt copy ALL data onto this new table (vr_key) I want to use a WHERE clause.  I've tried different combinations but can't seem to get it working right.  Here's the code in the trigger itself that's failing:

INSERT INTO vr_key(order_id,vrkey)
VALUES (wp_posts.ID, stringRand(12))
WHERE wp_posts.post_status='wc-processing'

As soon as I remove WHERE, the trigger saves okay, but if I put in the WHERE clause it breaks.

stringRand is a stored function that is supposed to generate a random 12 digit alphanumeric string of characters. Any ideas?


texts: Replace "-" with NULL with Derived Column in SSIS

texts:
I am extracting information from an excel sheet and loading it into a database. Before I load it into the database, I must transform all of the "-" in the excel sheet to NULL values.

I am currently trying to do this with the derived column transformation in a data flow object. It works if the data type is a Unicode string [DT_WSTR], however it will not work with a double-precision float [DT_R8].

The expression that works for the unicode strings is below.

[EquipmentID]  == "-" ? (DT_WSTR,50)NULL(DT_WSTR,50) : [EquipmentID]

The expression that is not working for the double-precision floats is below.

[SideDoorType] == "-" ? (DT_R8)NULL(DT_R8) : [SideDoorType]


texts: Alternative to Mysql that handles increment/decrement with new or existing keys

texts:
In our application we're looking to store values by ID. In mysql, I might make a table like this:

create table balances {
  address bigint NOT NULL PRIMARY KEY,
  balance double NOT NULL
}

The problem is how we would interact with it. If I move 15 units from address=5 to address=12, address 12 may not exist. So it takes two or three operations:

begin transaction;
subtract 15 from balance where address=5
if address 12 exists
  add 15 to address 12
else
  insert balance=15, address=12
end transaction

Is there a database or storage system that is designed for this sort of tracking that could do this sort of operation more atomically without if-checks?


texts: Tracking MongoDB execution times using PHP

texts:
I am currently working on a project using PHP, I have done research and found that microtime() helps with tracking the time of a query in a MySQL database..

how do I do the same for MongoDB, I have a PHP interface and I have also inserted data into the MongoDB database using this but how do I show how long the time of the query took to insert into the database.

PHP uses microtime()
MongoDB uses ????


texts:
On my site I log every pageview (date, ip, referrer, page, etc) in a simple mysql table.

This table has the following activity:

1 SELECT per minute
1 DELETE per minute
no UPDATE queries
Lots of INSERTS (about 300 per second).

This table is never bigger than 200MB.

Today, I changed this table from an InnoDB table to a MEMORY table, this made sense to me to prevent unnecessary hard disk IO. I also prune this table once per minute, to make sure it never get's too big. And this information is not very important, so on restart of MySQL it does not matter that it is removed.

--

Performance wise, things are running fine. But I noticed that while running tuning-primer, that my Current Lock Wait ratio is quite high using MEMORY table.

Current Lock Wait ratio = 1 : 561

If I change this table to InnoDB the ratio is:

Current Lock Wait ratio = 0 : 78600946

My question: Should I worry about this Lock Wait Ratio? And is there something I can change in my configuration to improve things so that the lock wait ratio isn't so high using MEMORY tables?


texts: Oracle Instant Client 64-bit "Unable to connect, SQLState=" Windows Server 2012

texts:
I have installed the Oracle 12.2 Instant Client ODBC 32 & 64 bit drivers and required software (VS C++ redist 2013 32 & 64 bit) on Windows Server 2012. I can create a connection and connect successfully via the 32 bit driver but when using the 64 bit driver I get the error below. I have turned on tracing at the trace log is below. Using the 32bit driver is not an option nor is any other method ODP.net as we are using 3rd party software. The server itself is Oracle Database 12c Standard Edition  12.2.0.1.0

odbcad32        e40-129c    ENTER SQLAllocEnv
        HENV *              0x000000BA3CB98940
odbcad32        e40-129c    EXIT  SQLAllocEnv  with return code 0 (SQL_SUCCESS)
        HENV *              0x000000BA3CB98940 ( 0x000000BA3CFCEA50)
odbcad32        e40-129c    ENTER SQLAllocConnect
        HENV                0x000000BA3CFCEA50
        HDBC *              0x000000BA3CB98948
odbcad32        e40-129c    EXIT  SQLAllocConnect  with return code 0 (SQL_SUCCESS)
        HENV                0x000000BA3CFCEA50
        HDBC *              0x000000BA3CB98948 ( 0x000000BA3CFCEB30)
odbcad32        e40-129c    ENTER SQLDriverConnectW
        HDBC                0x000000BA3CFCEB30
        HWND                0x00000000000304CE
        WCHAR *             0x00007FF855AFADB8 [      -3] "******\ 0"
        SWORD                       -3
        WCHAR *             0x00007FF855AFADB8
        SWORD                       -3
        SWORD *             0x0000000000000000
        UWORD                        1 <SQL_DRIVER_COMPLETE>
odbcad32        e40-129c    EXIT  SQLDriverConnectW  with return code -1 (SQL_ERROR)
        HDBC                0x000000BA3CFCEB30
        HWND                0x00000000000304CE
        WCHAR *             0x00007FF855AFADB8 [      -3] "******\ 0"
        SWORD                       -3
        WCHAR *             0x00007FF855AFADB8
        SWORD                       -3
        SWORD *             0x0000000000000000
        UWORD                        1 <SQL_DRIVER_COMPLETE>
odbcad32        e40-129c    ENTER SQLGetDiagRecW
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x000000BA3CFCEB30
        SQLSMALLINT                  1
        SQLWCHAR *          0x000000BA3CB98830
        SQLINTEGER *        0x000000BA3CB988F0
        SQLWCHAR *          0x000000BA40865140
        SQLSMALLINT               1024
        SQLSMALLINT *       0x000000BA3CB988F4
odbcad32        e40-129c    EXIT  SQLGetDiagRecW  with return code 100 (SQL_NO_DATA_FOUND)
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x000000BA3CFCEB30
        SQLSMALLINT                  1
        SQLWCHAR *          0x000000BA3CB98830
        SQLINTEGER *        0x000000BA3CB988F0
        SQLWCHAR *          0x000000BA40865140
        SQLSMALLINT               1024
        SQLSMALLINT *       0x000000BA3CB988F4
odbcad32        e40-129c    ENTER SQLDisconnect
        HDBC                0x000000BA3CFCEB30
odbcad32        e40-129c    EXIT  SQLDisconnect  with return code -1 (SQL_ERROR)
        HDBC                0x000000BA3CFCEB30
DIAG [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
odbcad32        e40-129c    ENTER SQLFreeConnect
        HDBC                0x000000BA3CFCEB30

odbcad32        e40-129c    EXIT  SQLFreeConnect  with return code 0 (SQL_SUCCESS)
        HDBC                0x000000BA3CFCEB30
odbcad32        e40-129c    ENTER SQLFreeEnv
        HENV                0x000000BA3CFCEA50

odbcad32        e40-129c    EXIT  SQLFreeEnv  with return code 0 (SQL_SUCCESS)
        HENV                0x000000BA3CFCEA50


texts: How to copy data from one database to another database efficiently?

texts:
I have a conceptual question.

I have two databases which theoretically have the same structure but one of them is a SQLBase database and the other one is a SQL Server database.

One database (the SQLBase database) already contains a lot of data. This data should be transferred to the other (SQL Server) database via Select and Insert.

How can I do this data migration with the highest performance?

My first approach was to sort all the tables in a list where the tables which contain foreign keys will be stored behind the referenced tables. But with this solution it will be impossible to start parallel processing.

The second idea was to create a custom type which contains the tablename and the tablenames of the referenced tables and a bool flag which stores whether the data in the table have been copied. This type is stored for each table in a list. Then I start a new thread that checks before copying whether the referenced tables have already been created for each table. If not, I execute Thread.Sleep() after which I will check it again.

Is there a well performing approach to this problem?


texts: Is Fuzzy Matching available in SQL Server 2019 Standard?

texts:
I can't seem to find a definitive answer to what seems like it ought to be a really simple question. Previously fuzzy matching was an enterprise only feature, but I've heard it mentioned that this is now available with the standard edition. Can someone please confirm whether this is the case?
Edit: for clarity, I am specifically referring to SSIS fuzzy grouping / lookup


texts: Data Flow Transform Not Automatically Mapping Columns

texts:
I'm in the midst of creating a set of SSIS 2014 packages (using Visual Studio 2013) importing data from 50 tables. Since most of the tables are being filtered on the same column and I'm importing all columns into my staging area, I opted for a reusable design where my package name matches the table name, my OLE DB source query is defined with an expression-based variable (starting with SELECT * FROM <tablename>...) and my OLE DB destination table is also defined with an expression-based variable (in this case schema.tablename).

Both the source query and target table have matching column names and match in most cases on data types and sizes.

As I'm cloning my packages for each table, I copy/paste a previously-created package and visually inspect the mappings to confirm the expressions and the data flow mappings are valid for the new package name. I'm seeing that the expressions are working as planned, but I have to manually do the drag/drop mapping of most columns in the destination editor.

Is this expected behavior of the editor as the design metadata of the original package is invalid under the new package name and is there a way to have Visual Studio delete and remap all columns in the data flow based on column name?


texts:
Does anyone else's phpmyadmin take an incredibly long time to load? like 68 seconds after i click a table it finally gets there...!

its also very slow when i click the "add data from text file into table" button...

is there anything that can be done to speed it up?


texts: How to fully dump mySQL database via workbench?

texts: I am exporting database in SQL file using workbench. Export is done successfully, but when I import data and compare record counts with older database, some of the records are missing. Not all records are exported in some table. I don't know what i can do so no records are missed.


texts: Who changed/modified stored procedure in SSMS 2005 or 2008?

texts:
Possible Duplicate:
  Determining how a schema change occurred?

I have a stored procedure that was modified late last night and just spent the majority of the morning tracing issues back to this change.

Basically no one is owning up to this change, so I am trying to figure out how to find who modified this stored procedure.  I have researched out on the web to see how to find this but it looks like this is not possible?

I figured if anyone knows how to do this it would be someone here.

I did try this. :

"In SSMS, right click on Server Name, choose Reports / Standard Reports / Schema Changes History."  --> Which didn't give me anything but I was at least able to run this which provided me with a time.

select name, create_date, modify_date
from sys.procedures
where name = 'mystoredprocedure'

Thanks for any advice or direction.


texts: How can I represent discrete ages in a relational database?

texts:
I am using PostgreSQL to store data on a Person. I need to store whether each Person is capable of teaching a Child of each age year, from 0-17. The ages are discrete values, and a Person can have any number of the 18 (assuming 0-17 years old) assigned to their account. This will be used in a booking system, and also needs to form part of the search results.

Initially I considered creating 18 boolean fields, but this seems inefficient. Is there a better way to do it? I understand that Postgres supports JSONB, so this is an option, but I am unsure of the implications.

Is there a better way to store this data?


texts: Searching 100000´s of Varchar(max) columns for 1000´s of keywords/sentences

texts:
I need some advice/ideas.

I have a table containing job announcements approx. 250,000 records

In another table is 6000 keywords/sentences from applicant cv's historically regarded by the government as typical

I want to find job announcements where the text contains one or more of the keywords

I have been doing this, using full-text-search, but firstly, the variable, can´t hold all keywords and secondly it is very slow

DECLARE  @Keywords NVARCHAR(MAX)

SELECT   @Keywords = STUFF((SELECT '|' + Word
                            FROM  dbo.Keywords
                            FOR XML PATH('') ), 1, 1, '')

SELECT *
FROM   dbo.Notes
WHERE  CONTAINS(Note, @Keywords)

Do you have any bright suggestions of how to solve this challenge ?


texts: What is SQL Server doing in the background once a Backup/Restore is complete?

texts:
When I run a backup or restore through the SSMS GUI, the dialog shows Executing with 20% increments rather than every 1% tick. To get round this, we can use DMV's. Thing is, when the query shows 100% complete, the SSMS GUI remains for a good 5 or so minutes before the 'Restore complete' dialog appears.

Question is, what is SQL server doing during this time? Some kind of integrity check? Index rebuild? Activity monitor shows 0 R/W on the .MDF whilst the GUI dialog persists.


texts:
I have table that contain one is event Id ,second is school Id and third is game ID I want to count number of school who has participated in that event with unique game ID (One school can participate in multiple event with different game)

Example

Event Id   school ID   Game ID
1          20          1
3          21          2
1          22          3
2          22          4
3          21          5
1          20          6

I need the output is

Event Id        schoolCount
1               2
3               1
2               1


texts: Detecting SQL Server Utilization with a query

texts:
My current project will send queries to an sql server constantly and It may use 100% of the memory or CPU.

How can I check if the server is nearing full utilization in a stored procedure so that I can decide whether to execute the queries or not or save some settings in a table so that the following queries can know the workload is high and decide what to do
If not, how can I prevent SQL server to reach full utilization?

More information about the case:
Right now I know our current test server can process 40-50 queries per second (one specific stored procedure). And now we'll decide how many queries are sent to the server every second. If we set the amount even 1 higher than the expected, in the long run, the queries will eventually fill the virtual memory and out client will have to restart their sql server instance periodically.

Expected Results (For bounty hunters):

@memory_usage float, @cpu_usage float; /* in percentage */

Any ideas are welcomed. Thanks.


texts: How to use MAX() to select a single record but prevent GROUP BY from selecting all distinct values and multiple records

texts:
I have some Line items that have version numbers:

[Doc No]    [Version No]    [Line No]    [Data-a]    [Data-b]
1           1               1000         abc         abc
1           2               1000         xyz         abc
1           1               2000         null        lmnop
1           2               2000         ggg         lmnop

I want to only select the highest version number per [Line No]:

[Doc No]    [Version No]    [Line No]    [Data-a]    [Data-b]
1           2               1000         xyz         abc
1           2               2000         ggg        lmnop

I am doing this using this tsql:

SELECT [Doc No]
      ,max([Version No])
      ,[Line No]
      ,[Data-a]
      ,[Data-b]
FROM [table]
GROUP BY [Doc No], [Line No], [Data-a], [Data-b]

The problem obviously is ... if you notice in the first table the [Data-a] column has changing values (hence the need for version numbers.  So I want to see the latest version and the value contained in [Data-a] for that record. But since I am using max() to get the latest version, I have to put [Data-a] in the GROUP BY clause - which causes each two distinct values to be pulled and thus two records showing both version numbers. How can I solve this problem?


texts: Split Vector is taking too much time in mongo shard

texts:
I am using mongo shard distribution system. This system performs slow as the data size increases. I checked with profiler and saw split vector is taking to much time while running.

It happened after a day when one of my shard replica set went down and i had to remove that from cluster. Now i am running a single shard replica cluster. I have disabled the balancer as currently there is no second shard. Could you please help me to under stand why this process is slow.


texts: On which node first transaction will be executed in Always-on setup?

texts:
I have a scenario where SQL Server 2012 - Always-on feature configured.The primary and secondary are synchronous for failover purposes. I understand that we will have node 1 (PRIMARY) and node 2 . Also I know during fail over node 2 becomes PRIMARY.

My question is, (synchronous commit setup)

When application send INSERT or any SQL transactions, which node first the execution happens? Is it on PRIMARY ?
When node 1 fails then node 2 becomes PRIMARY, but who will make node 1 up?  do I need to setup alerts? if node 1 dies then how HA and DR works?
What is difference between active-passive or active-active?

Where I can find detailed information on always-on architecture? I have read many article but none explains the internals.


texts: In a data warehouse with small amount of writes, would RAID 1 be better than RAID 1+0?

texts:
We are using HP Vertica database 7.2 community version.  300GB of data on one node. Thats a small amount of data.

HP officially recommends RAID 1+0.
https://community.dev.hpe.com/t5/Vertica-Knowledge-Base/RAID-Storage-for-HPE-Vertica/ta-p/234225

But I do not envision a problem with write performance which is the point of RAID 0.  I am not planning to load, insert or write large amounts of data.  I am planning to query large amounts of data, but not write a lot.  It seems to me that RAID 1 is sufficient.

Is there anything wrong with using RAID 1 instead of 1+0?


texts: Oracle 12c (RDS) Questions from a SQL Server DBA

texts:
I am transitioning into Oracle on RDS (should make it easier than Linux based!) from a SQL Server background and have a few queries;

Index Maintenance. In SQL Server dependant on maintenance window I reorganise anything over x amount of rows with fragmentation > 10% but < 30%. Anything > 30% gets a rebuild. I can't seem to find any advice on this sort of thing in Oracle
Integrity check. In SQL Server I do these often as possible, usually every 24 hours. Again struggling to find best practice for Oracle here. I've seen DBVERIFY mentioned but that's about it
Statistics - I wont do routine maintenance on this (unless done via index rebuild) unless I find an issue. In SQL Server stats are automatically taken care of when a table grows by 500 rows + 20%, is there an Oracle equivalent?
TABLESPACES - not so much of a maintenance issue, however I believe these are equivalent to databases files in SQL SErver which can grow in a similar fashion, I believe by "AutoExtend". However how do I set what percent or size a TABLESPACE should grow by? And also is it not better practice to presize these to avoid any growth during production hours?
What are the best "DBA" and performance views to use? i.e. in SQL Server I will use sys.dm_exec_Requests or sp_who2 to see what is hitting the server, sys.dm_os_performance_counters to see performance counters and will log into the box to use perfmon for a graphic interface, what are the rough equivalents of this?
On the above point, are the key metrics in Oracle the same as SQL Server, roughly? I.e. PLE, buffer hit cache, disk latency etc

Any other links or advice which will give me enough knowledge to "Keep the lights on" and know where to look if an issue arises would be greatly appreciated.


texts: I need help with a SQL-query that results in an error

texts:
I get the following error when I try to delete a record in the user table that has an entry in the feedback table:

e.g. when a record in the feedback table has a reference to the user table

  1451 - Cannot delete or update a parent row: a foreign key constraint
  fails (kbs.feedback, CONSTRAINT fk_feedback_user1 FOREIGN KEY
  (user_id) REFERENCES user (user_id) ON DELETE NO ACTION ON
  UPDATE NO ACTION)

The relationship between the two tables:

I hope someone could help me into the right direction and that my question is clear enough. I've followed a database design course but forgot a lot of stuff.


texts: Database - limit of reading a table with a lot of concurrent batch read and writes

texts:
We have this table. It contains data which will be picked up by an hourly batch job for processing. Five times a day, there are also multiple batch ETL jobs which get data from other source table and transfers it to this table.

Originally, that was it's only function. Now we were told that there will be a new page in our web application that would read data from this table. My worry is that due to the number of batch job read and writes to the table,
would it still be feasible to read data  from it and show it in our web page? Our web app has about 30,000 users that would be able to access that new page and it is estimated they most users use the web page everyday and
they would want to view the contents as quickly as possible (there's an SLA). Due to the number of read and writes to the table from the batch jobs, achieving the desired page response time in the SLA might not be possible

We are thinking twice of creating a read replicate since the hourly batch job that reads from this table updates statuses of records that are processed. There would also be additional synch jobs needed if we would use a read replica.

Where do we draw the line between saying "OK we need a read replica for this" and "No problem we still can have one table to do all that" ?  What kind of data do we need to determine this? or is there no choice but to do load testing
using production data?

Would appreciate any help. Thanks

EDIT:
Additional information

Total no of records now in table is 2.3 million.
Total number of records added by all the batch jobs: ave of 5000 per day


texts: SQL Server 2016: Granting security to do everything but read / write data

texts:
We have an offshore support team coming online to support our servers as DBAs for 24x7 support.  However, there are several instances that have a limitation due to contracts that limit offshore access to our data.

What security needs to be added to give them effective control over the servers as DBAs, but limits them to not being able to read/write data?

The instances are in mixed mode, but it would most likely be applied through a AD users group.  SQL Server 2016 and Windows Server 2016.


texts:
I am looking for to print two variables  of same datatype I have something like this

declare @one int ,@two int
set @one=1
set @two=2
print @one+@two

obviously it will give a result 3 but
I need result 1 2

On search I got to convert the any one of these variable and come up this query.

print convert(varchar(10),@one)+@two

but the result was same 3!!
What should I do,to get the result?
NB :I am using sql server


texts:
I want GROUP BY jalali's month, all date save as gregorian format in my database, I found following sql query but group by gregorian date
SELECT COUNT(reserverow.id), EXTRACT(MONTH FROM reserverow.used_date) as "Month"
FROM reserverow
WHERE reserverow.used_date > '2000-09-28 01:00:00'
GROUP BY EXTRACT(MONTH FROM reserverow.used_date)


texts: Replication error from Mariadb 10.1 to Mysql 5.1/5.0/5/5 when master's logging format is set to row based

texts:
While replicating from Mariadb 10.1 to MySQL (5.0, 5.1, 5.5) or Mariadb (5.2, 5.5) lower versions, if master's binlog_format is set to row,  the replication failure occurs with the following message at slave (show slave status \G;):

  Last_Error: Table definition on master and slave does not match: Column 18 type mismatch - received type 19, rtmariadb10.empdetails
  has type 11

Here

Master: Mariadb 10.1,binlog_format: row ;
Slave : Mysql 5.1, binlog_format=statement/row/mixed(any one of these)

Can someone please help to solve this issue?


texts:
In SQL Server Management Studio (SSMS) 2008 R2, I wish to import a not-quite-properly formatted CSV into a database table. I was trying to use the wizard at <database name>->Tasks->Import Data.

Differences from the CSV "standard"

null values are represented by a space between commas
some fields have commas in the middle of them and are not quote-escaped
there is a terminal comma on all lines except the header line (WTF??)

I eventually addressed these issues manually by editing the CSV prior to import. Issue 2 was fixed by replacing commas with dashes for the 4 families of cell values that were causing it. Issue 3 I addressed with a search-and-replace for those terminal commas. Issue 1, which was a problem for numeric fields, also with a search-and-replace , , -> ,,.

When my file did eventually import properly, null values were replaced with zeros, even though I had allowed null values in the field definitions.

My basic question: is there a way that I can alter the code for CAST/CONVERT operations during an SSMS import? The only stage at which I could get to SQL code was in the field definitions of the created table (from Select Source Tables and Views->Edit Mappings->Edit SQL - which was available if and only if I hadn't changed the destination table name on the Select Source Tables page), but that was just for the CREATE TABLE definition and didn't address the import process at all. It really seems to me that I should be able to allow a single-character space string to be cast to a null value when converted to a number (instead the wizard fails and states that a value is out of bounds). Further, the null string certainly should be cast to the null value integer, rather than 0.

I can save a SSIS package but it wasn't clear to me how to edit and run that to allow for what I want to do.  Is there a template file somewhere that I can edit to allow for null strings to be cast to null integer values (like something here....)? Is there a way to use parts of the Import Wizard to generate tSQL code? Should I instead use a BULK INSERT statement rather than the wizard?


texts: Creating a partial unique constraint for MySQL

texts:
I have the same question as asked in a previous post: PostgreSQL multi-column unique constraint and NULL values. But the solution there is not applicable as I am using MySQL instead of PostgreSQL.

My table looks like this:

CREATE TABLE `my_table` (
  `id` bigint(20) NOT NULL,
  `col1` bigint(20) NOT NULL,
  `col2` bigint(20) NOT NULL,
  `col3` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

I want the three columns col1, col2 and col3 to be unique. But a normal unique constraint

UNIQUE KEY `unique_constraint` (`col1`,`col2`,`col3`)

still allows me to insert identical rows whose col3 is NULL. I want to forbid entries like these:

1  | 1 | 1 | NULL
2  | 1 | 1 | NULL

I was searching for a partial unique constraint in MySQL as proposed in the post above but apparently there are not supported in MySQL 5.5.

Can somebody give me a hint how to solve this best in mysql, I am also hoping there will be a performant solution!

Thanks from a newbie in mysql (coming from PostgreSQL)!


texts: Read-only database: Normalize or not for best query performace

texts:
I have a pandas DataFrame that looks a bit like this:
         id        name       date     col1     col2  total
0 123456748 EXAMPLENAME 2020-09-01 SOMEDATA MOREDATA   5.99
1 123456748 EXAMPLENAME 2020-09-01 SOMEDATA OTHERDATA 25.99

There are 15 columns, the name values are associated with the ID, and the rest is some data relevant for that person. col2 would have about 400 unique values. The database would be about 300,000,000 rows to start with, and then will grow at about 500,000 records per week.
The records in the database will never be updated or deleted, only new ones will be added. Final purpose of the database is to have a web app in which the user can select the ID of the person. The database would retrieve the information, and the website would render a graph and a dataframe. The expected traffic of the website is very low, so I was thinking about using SQLite.
Based on that, I have two questions:

Should I use Relational Databases, like PostgreSQL or SQLite, or should I try MongoDB? I'm interest on the performance of the database to select and retrieve the data; don't care too much about insert time as it won't be done very often (once per week).
Based on performance to query, in case you select Relational Databases, should I have all data in one table or should I split it (normalize it)? I read that normalizing a database when its purpose is only to query and store the data, could lead to worse performance than having it all in one table. However, I do not know much about databases and would prefer an expert opinion, or resources to learn more about the correct implementation and maintenance.

Thanks.


texts: re-write active record query to PostgreSQL

texts:
I want to rewrite the active record query to Actual PostgreSQL query

Camera.joins("left JOIN users on cameras.owner_id = users.id")
                    .joins("left JOIN vendor_models vm on cameras.model_id = vm.id")
                    .joins("left JOIN vendors v on vm.vendor_id = v.id")
                    .where(condition).order(sorting(col_for_order, order_for))

as you can see how these tables are related in the above joins.

also conditions could be

"lower(cameras.exid) like lower('%#{params[:fquery]}%') OR lower(cameras.name) like lower('%#{params[:fquery]}%') OR
      lower(vm.name) like lower('%#{params[:fquery]}%') OR lower(v.name) like lower('%#{params[:fquery]}%')
      OR lower(users.firstname || ' ' || users.lastname) like lower('%#{params[:fquery]}%') OR
      lower(cameras.config->>'external_host') like lower('%#{params[:fquery]}%')

Reason of writing this query, I have another table camera_shares which is related to cameras table with camera_id, I also want to get the count of camera_shares against a camera_id, How could that be possible to geth the similar results in PostgreSQL


texts: How do I disable foreign key constraints in my Postgres dump file?

texts:
I’m using Postgres 9.5.  I have created a dump file of INSERTs for three of my tables.  To avoid getting foreign key errors, I added the below to the top and bottom of my file (the “…” is where all the INSERTs are) …

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = public, pg_catalog;
ALTER TABLE addresses DISABLE TRIGGER ALL;
ALTER TABLE table1 DISABLE TRIGGER ALL;
ALTER TABLE table2 DISABLE TRIGGER ALL;

…

ALTER TABLE addresses ENABLE TRIGGER ALL;
ALTER TABLE table1 ENABLE TRIGGER ALL;
ALTER TABLE table2 ENABLE TRIGGER ALL;

However, even when i run this file, I get errors like

ERROR:  insert or update on table "table2" violates foreign key constraint "fk_rails_ba656ceafa"
DETAIL:  Key (table1_id)=(f62c5fee-1031-4d5e-a084-9210f052a2d1) is not present in table "table1".

Why am I getting these errors despite disabling foreign keys and more importantly, how can I prevent them?  I want to INSERT all the data and then re-enable foreign keys.


texts: What is the fate of VODKA Indexing, is it dead?

texts:
I see numerous mentions online of Vodka indexing with regard to PostgreSQL.

On the Wiki it's defined as,

  (Korotkov) Vodka maintains two index files, one for keys and one for heap item pointers (postings). Each index file can be, in effect, managed by a different access method. This is a generalization of GIN, which uses btree (albeit its own btree code, not src/backend/access/nbtree) for both keys and postings. The keys index file has item pointers that refer to the postings index file. When the postings file's access method needs to move an index item, it must arrange to update any item pointer in the keys file. To that end, proposing a new "amupdateiptr" access method function. Calls to that function, when necessary, will arrive right after an amgettuple call.

Aside from that, you can see this post on the list

  We are on the way to postgres-centric company and we'll continue our
  development in that company. We certainly don't need funding for the VODKA, our national product :)  I thought about crowd-funding, but didn't find any good cases for products similar to postgres. So, we decided to go
  traditional way - built our own company and get money from industry. In
  short perspective we delay our development, since many-many other
  non-developers business, but in long perspective we'll have more russian
  contributors for the postgres project.  Postgres in Russia was popular
  database, but now it gets much more interests from business and goverment,
  so I'm quite optimistic.

What ever happened to this and is it dead or did it get merged into something else?


texts: SQL Server Merge Replication - Safely shrink transaction log

texts:
I have a couple of SQL databases running in merge replication.  My publishers HDD is full due to the transaction log of the database being replicated which is causing my snapshot agent to fail (out of disk space).

I want to shrink my transaction logs.  If I do, will it break my replication?  If it will break my replication, is there a way I can tell my publisher to put its files on a different HDD without having to restart replication.

Thanks!


texts: Help with tuning this UPDATE Query or possible re-write

texts:
I have below SQL query which runs on avg. 40 secs and need some guidance where can i improve it or possibly re-write:
   Declare @status_D INT,
      Declare @StatusIP INT,
      Declare @Status_F INT,

    SELECT @status_D = Id From Batchtablestatus (NOLOCK) Where desc ='Value1';
    SELECT @StatusIP = Id From Batchtablestatus (NOLOCK) Where desc ='Value2';
    SELECT @Status_F = Id From Batchtablestatus (NOLOCK) Where desc ='Value3'

BEGIN TRAN

 UPdate U
        SET
         U.[id]= @StatusIP,
         U.[EnabledDate]= Getdate()
         OUTPUT deleted.col1,
         deleted.col2,
         deleted.col3,
         deleted.col4,
         Inserted.id

FROM (

SELECT TOP 5000 * FROM BatchChangeTable WITH (ROWLOCK,UPDLOCK)
WHERE [id] IN ( @status_D ,Status_F)
ORDER BY [ModifiedTime] )U;

  COMMIT TRAN

I am getting warning from blitzcache as Forced serialization,
Expensive sort, non-sargable and Merge join "Mant to Many" = True

The only index we had is on id which is a NC index with id as leading key column.
I will try to update my question if execution plan if i can. Table in SELECT is around 100 million as of now. Also it has PK/CI on col1.
I have asked users why we do * in that TOP which pulls almost 50+ columns but in update use just fewer, may b i am thinking wrong but just came to my mind.
Adding index to modified time also does not seem to help.
Please suggest what else can be checked. Thanks
plan-
Index seek has happened on NC index id


texts: Oracle 12.1 optimize_adative_features = FALSE ; using new statistics

texts: In Oracle 12.1, does setting optimize_adaptive_features to FALSE mean that the optimizer will no longer use new statistics generated by dbms_stats.gather_schema_stats?


texts: Equivalent of utf8_general_ci in Postgres/ICU?

texts:
In MySQL there is a collation utf8_general_ci which provides case-insensitive comparisons in a variety of languages. For example, these are all 1 (true):
SELECT 'ı' = 'I' SOLLATE 'utf8_general_ci';
SELECT 'i' = 'I' COLLATE 'utf8_general_ci';
SELECT 'ä' = 'Ä' COLLATE 'utf8_general_ci';

Can I define a similar collation using PostgreSQL's ICU?
I tried
CREATE COLLATION "undefined_ci_nondet_old" (
  PROVIDER = 'icu',
  LOCALE = "@colStrength=secondary",
  DETERMINISTIC = false
);

But that doesn't seem to include the Turkish I/ı conversion:
SELECT 'ı' = 'I' COLLATE undefined_ci_nondet_old; -- false


texts: Join JSONB column to normal PK column in Postgres

texts:
I am trying to join a normal table to a table with a jsonb field via a property on that jsonb field in Postgres.  Example:

Normal table:

create table Res
(
    id uuid default uuid_generate_v4() not null
        constraint res_pkey
            primary key
)
;

Table with jsonb:

create table res_rem_sent
(
    body jsonb not null,
    search tsvector,
    created_at timestamp with time zone default now(),
    id uuid default uuid_generate_v4() not null
        constraint res_rem_sent_pkey
            primary key
);

create index idx_res_rem_sent
    on res_rem_sent (body)
;

create index idx_search_res_rem_sent
    on res_rem_sent (search)
;

an the body field could look like: {"resId": <GUID>, ....}

I would like to join the res table to the res_rem_sent table on resId where a res id is equal to a give GUID id.


texts: Is there any name for common sense / knowledge integrity constraints?

texts:
The Wikipedia page on data integrity lists 4 types of integrity constraints: entity integrity, referential integrity, domain integrity, and user-defined integrity.

Is there a name for integrity constraints that stem from common sense / knowledge?

Example :

I have one table with 6 columns containing some data about hospital admissions:

hospital_admission_id (integer field)
patient_id (integer field)
patient_date_of_birth (date field -> I am aware that it should be in some patient table but ignore this in this example)
date_of_hospital_admission (date field)
date_of_hospital_discharge (date field)
hospital_admission_note (text field: a relatively long text by the medical staff summarizing some data about the patient when he was admitted at the hospital)

One integrity constraint stemming from common sense / knowledge is that the patient more than one year old at the time of hospital admission (i.e. date_of_hospital_admission - patient_date_of_birth > 1 year), hospital_admission_note shouldn't say that the patient is a neonate.

Another integrity constraint from common sense / knowledge is that a patient cannot be admitted in the hospital if he already is in the hospital (i.e. some checks would have to be done around date_of_hospital_admission and date_of_hospital_discharge).

I feel that:

It's not an entity integrity constraint since it's not an issue around primary keys.
It's not a referential integrity constraint since it's not an issue around foreign keys.
It's not a domain integrity constraint since it's not about checking whether values are in some list.
I guess it could be qualified as a user-defined integrity constraint but I thought they might be some more precise technology to specifically designate integrity constraints stemming from common sense / knowledge and not just a specific user's preferences.


texts: Get IO stats request sql like activity monitor

texts:
Is it possible to get the same value as in the activity monitor (SSMS) for the "Database IO" as a result of a SQL request?

  click to enlarge


texts: Repartition Primary Key Index online in Oracle

texts: I have an 24x7 OLTP (11gR2) application that endures heavy inserts on particular tables, traffic has increased to the point where we are getting heavy index contention.  We use sequences for the primary key on this particular table and in order to reduce the index contention I want to repartition the primary key index to have 8 hash partitions (currently the index and table are not partitioned).  Can I rebuild the index converting it to partitioned in an online fashion, i.e. not stopping DML on the table?


texts: Logging the Execution times of dynamically constructed mysql stored procedures

texts:
I have a MySQL Server.

The MySQL server contains a huge network data collection.
The client API's will connect to the MySQL server and execute some dynamically created stored procedures and delete it on the fly after successful execution.
There is no tracking mechanism currently available.
I wont be able to modify the stored procedures, as it is dynamically created by the client API's

Is there any mechanism to track the execution of such dynamically created stored procedures ?


texts: How to store user selected value, single choice or all choices

texts:
I have a form where a user is able to select a single value from a dropdown. The first option in the dropdown is "All", followed by all of the individual options that they can pick. So, they can either pick an individual thing, or they can pick "All" which will effectively mean that they have picked everything in the list.

All Options
Option 1
Option 2
Option 3

The database is storing this currently with an ID for the selection, or when they pick all, we store a value of -1. Is this anywhere close to best practice, or should we have something like a boolean column "all_options", and another column "option" which would be forced to be null if "all_options" is true?

user  option
1     1
2     3
3     -1 (or null?)

vs

user  all_options  option
1     false        1
2     false        3
3     true         null

I can't find anything about this, and I think it has to do what not knowing what to search for.

A more concrete example

The options are say, items for sale on a website. The user is setting up alerts on price drops. One use case is that the user wants to know when a particular item is 50% off or more. They select the individual item from the dropdown of say 50 items. Another use case is, the user wants to know anything that is on sale in general. They pick the "All Items" option. The list of items changes, so if I create the relationship between this alert and every single item then:

That could be an extremely large number of relationships for just one alert, depending on the size of my list of items.
New items wouldn't be in the alert. It would only be items that existed at the time of the creation of the alert.


texts: Postgres - how to tell when the last VACUUM FULL was run?

texts:
If I run the following
VACUUM myTable;

this gets recorded in the postgres system tables and can be seen with
select last_vacuum, vacuum_count from pg_stat_all_tables where relname= 'mytable';

However, doing a VACUUM FULL seems to go unrecorded.
How can I tell when the last VACUUM FULL was run against a table?


texts: Login failed for user xxx because the account is currently locked out

texts: Sometimes, my database user is locked out for no reason, and seconds after then unlocked, I was trying to found a reason around www, but I just found a manner to correct the intermitent error each time that it happens. Can someone please explain for what this happen and how resolve it totally.


texts:
I see that: ‘When an availability group failover occurs, existing persistent connections to the availability group are terminated and the client must establish a new connection in order to continue working with the same primary database or read-only secondary database.’  - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b8857583-0ad7-419c-904b-e9e2a13511c0/sql-connections-to-the-sql-2012-always-on-availability-group?forum=sqldisasterrecovery
And the fix – setting multisubnetfailover = true
https://technet.microsoft.com/en-us/library/hh213417.aspx#CCBehaviorOnFailover
Is there a way to get this to work using a single subnet. Is there a way to detect such failures by the application and have it retry.


texts: MySQL SELECT slow, but only 2 x 300K rows and indexes

texts:
Have the MySQL SELECT query below that is awfully slow.
It takes ~1.0 seconds to execute despite having only 300K rows and indexes, so I would love to find a way to get it to execute faster since it's a query that needs to be run again and again.
The query:
SELECT p.id, p.image, c.name, s.name, MIN(p.saleprice)
FROM products p
JOIN shops s ON p.shopid = s.id
JOIN products_category pc ON p.id = pc.product_id
JOIN categories c ON pc.category_id = c.id
WHERE p.brand_id > 0
AND pc.category_id = 46
AND pc.active = 1
AND p.price > 0
AND p.saleprice > 0
AND p.saleprice < p.price
AND (p.last_seen > DATE_SUB(NOW(), INTERVAL 2 DAY))
GROUP BY p.image

The query returns 960 rows.
The table products has 300.000 rows and these columns + one index:
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(512) DEFAULT NULL,
  `shopid` int(11) DEFAULT NULL,
  `image` varchar(512) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `saleprice` int(11) DEFAULT NULL,
  `last_seen` datetime DEFAULT NULL,
  `brand_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `brand_id etc` (`brand_id`,`shopid`,`last_seen`,`price`,`saleprice`)
) ENGINE=InnoDB AUTO_INCREMENT=391671 DEFAULT CHARSET=utf8

The table products_categories also has 300.000 rows and these columns + two indexes:
CREATE TABLE `products_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `active` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id etc` (`category_id`,`active`),
  KEY `product_id etc` (`product_id`,`active`)
) ENGINE=InnoDB AUTO_INCREMENT=373364 DEFAULT CHARSET=utf8

The shops table. It has 15 rows and these columns:
CREATE TABLE `shops` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `active` int(11) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8

Based on similar questions here, I have tried nesting things with an inner select:
SELECT p.id, p.image, c.name, s.name, MIN(p.saleprice)
FROM
(SELECT * FROM products WHERE p.brand_id > 0 AND price > 0 AND saleprice > 0 AND saleprice < price AND (p.last_seen > DATE_SUB(NOW(), INTERVAL 3 DAY))) p
JOIN shops s ON p.shopid = s.id
JOIN products_category pc ON p.id = pc.product_id
JOIN categories c ON pc.category_id = c.id
WHERE pc.category_id = 46
AND pc.active = 1
GROUP BY p.image

It didn't help. The version with the inner select takes ~1,3 seconds to execute.
The problem seems to be the join between products and products_category, i.e. the two big tables with 300K rows each.
The GROUP BY p.image is necessary (some products are just different sizes, so we use p.image to filter these out).
Maybe there's a trick I can do with my indexes? Or can any of you spot something else I should optimize?
EXPLAIN of the query:
id  select_type table   partitions  type    possible_keys                   key             key_len ref             rows    filtered    Extra
1   SIMPLE      c       \N          const   PRIMARY                         PRIMARY         4       const           1       100.00      Using temporary; Using filesort
1   SIMPLE      pc      \N          ref     category_id etc,product_id etc  category_id etc 10      const,const     43104   100.00      Using where
1   SIMPLE      p       \N          eq_ref  PRIMARY,brand_id etc            PRIMARY         4       pc.product_id   1       5.00        Using where
1   SIMPLE      s       \N          eq_ref  PRIMARY                         PRIMARY         4       p.shopid        1       100.00      \N


texts: Is there a way to set variable in UPDATE statement in PostgreSQL (plpgsql)?

texts:
Something like this:

DECLARE
c INTEGER;
UPDATE table SET col = (c := col + 1) WHERE id = 1;

This is wrong:

ERROR:  syntax error at or near ":="

how to do it right?


texts: Possible Inconsistency Among System Tables

texts: Is there a reason that there would be a significant difference in the number of records in the DBA_USERS table and the USER$ table?


texts: How to Import mongodb database from development Environment to local env by using Commmand?

texts:
I have lots of collections in my development server. I need to use the same data for testing scenarios. I am trying to import all the data of development environment to local by command is it possible?

I successfully imported to a file by using below command:

mongoimport -d dbName -c collectionName dataFile.js

But how can we do same to import DB from development environment to local Env command-line?


texts: galera cluster with mariadb 10.3.13 is not replicating on RHEL 8.1

texts:
I'm installing a galera cluster using mariadb 10.3.13 with two nodes in redhat 8.1 server.
I enabled ports 3306, 4567, 4568 y 4444 ports. selinux is disabled. firewall disabled.
The first node is started by galera_new_cluster. It works.
When I try to start the second node by systemctl start mysql. It delays to finish the process. The data is not replicating but I see no errors.
By tcpdump I see traffic incomming to second node but it's not replicating.

What can I do?

server.cnf configuration:

[server]

[mysqld]
log_error=/var/log/mysql/mysql_error.log

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://172.24.10.53,172.24.10.54
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

mysql_error.log

2020-01-30 23:39:37 0 [Note] WSREP: STATE EXCHANGE: got state msg: afb4ec56-43e3-11ea-9fc0-c7a8579dc264 from 1 (dbaCluNodo1.pki)
2020-01-30 23:39:37 0 [Note] WSREP: Quorum results:
    version    = 4,
    component  = PRIMARY,
    conf_id    = 1,
    members    = 1/2 (joined/total),
    act_id     = 0,
    last_appl. = 0,
    protocols  = 0/9/3 (gcs/repl/appl),
    group UUID = 5a3e63d8-43db-11ea-9acf-7a26185f2888
2020-01-30 23:39:37 0 [Note] WSREP: Flow-control interval: [23, 23]
2020-01-30 23:39:37 0 [Note] WSREP: Trying to continue unpaused monitor
2020-01-30 23:39:37 2 [Note] WSREP: New cluster view: global state: 5a3e63d8-43db-11ea-9acf-7a26185f2888:0, view# 2: Primary, number of nodes: 2, my index: 1, protocol version 3
2020-01-30 23:39:37 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2020-01-30 23:39:37 2 [Note] WSREP: REPL Protocols: 9 (4, 2)
2020-01-30 23:39:37 2 [Note] WSREP: Assign initial position for certification: 0, protocol version: 4
2020-01-30 23:39:37 0 [Note] WSREP: Service thread queue flushed.
2020-01-30 23:39:39 0 [Note] WSREP: (e88922b7, 'tcp://0.0.0.0:4567') turning message relay requesting off


texts: Can I modify the fill factor value while a database is in use?

texts:
I'm using SQL server 2008 R2.  I asked a similar question before on this forum, now I'm asking:

First, can I change the fill factor while the database is in use?
Second, how can I determine the most suitable value for our server fill factor?  On what does this depend?


texts: MySQL performance impact of increasing innodb_buffer_pool_size

texts:
I have two databases one of size 170 MB and second one is of 15 MB, both having INNODB tables and innodb_buffer_pool_size is set to 8 MB.

My queries are responding well now i.e under mili seconds.
Is there any impact on performance if I set innodb_buffer_pool_size more than the default?


texts: Deleting records does not affect unused space in data file

texts: I have a SQL Server 2014 database where one table had around 485 million records.  After deleting around 120 million records, the amount of free space in the data file did not change.  I figured I should have around 80GB more unused space.  Data gets loaded into the table daily and I want to keep the data file from auto growing and using up more disk space.  I did rebuild the only index on the table after the delete, but that didn't help.  Is there a reason I am not seeing more space available in the data file?


texts: Patterns for Deleting large amounts of data

texts:
So I'm looking at how to delete a large amount of data from a handful of tables that do not have the key I need to easily isolate the rows that need to be deleted.  The situation is as so:

I have a ID let's say a RequestID.  I've decided that all of an arbitrary RequestID's data entries are invalid and so I want to purge them out of my tables to clean up.

Table A is a dimensional table that has my RequestID and it also has all the associated URL IDs for any particular RequestID.

Table B also contains dimensional data but does not have RequestID, so I have to use Table A to look up which records in Table B are valid delete candidates.

These tables are anywhere from 1 million to a billion rows, so the deletes have to be batched to work properly.

My thought was to do something like this but it doesn't seem very performant:

WHILE EXISTS (SELECT TOP 1 1 FROM TableB JOIN TableA ON TableA.URLID = TableB.URLID)
BEGIN
DELETE TOP 50000 a
FROM TableB a
JOIN TableA ON TableB.URLID = TableA.URLID
WHERE TableA.RequestID = <some_value>
END

I'm not sure how better I could delete the data

Edit: sorry I forgot to include requestID in the delete code example


texts:
I have a large table, about 750M rows.

I have an application using Entity Framework to load and parse data, a query that returns 2 million rows takes 4 seconds.

The same exact query takes 8 seconds using SQL Server Management Studio.

I noticed this happens with other queries (that all returns large data sets).
Both of the connection are using TCP/IP.

When including client statistics, I see this line (no idea what it means):

  Client processing time  2539        2539.0000

Why would a SSMS query be slower ?


texts: Possible Causes of a Slow Sql View When Filtered

texts:
I have a view in Sql Server 2008 that when performing just a:

SELECT * FROM [Table]

Will return roughly 40000 rows by 130 columns. Here are 6 runs with client statistics on (roughly 7 seconds average):

Let's just assume those times are fine, the problem comes when a filter is applied. Here are 6 runs with client statistics on when I filter (roughly 6 seconds average):

There are a few things that are annoying / frustrating about this:

The time difference is only about 1300ms on average.
Only 16 rows are returned in the filtered version.
The column that I am filtering is an int column, that has proper indexes.
The filtered SQL literally looks like:

SELECT * FROM [Table] Where [IntColumn] = 15 -- returns 16 rows

I am coming to you guys because I am lost as to why the filtered version could possibly take so long. It should be extremely simple for the SQL engine to find those 16 rows right from the start of the execution and then calculate / look-up the values for the 130 columns. My questions really are:

What can cause this?
What should I be looking for to try and fix this?

Here are the anonymous execution plans (.sqlplan in .zip):

Anonymous Sql Plans (Zippyshare)
Anonymous Sql Plans (Dropbox)


texts: Extended event (sp_statement_completed) firing multiple times

texts:
I'm using SQL Server 2008 R2 enterprise and this is my first foray into extended events. It's pretty cool stuff. I'm wanting to create a session that will run basically all the time to audit query times on our SQL Server. I will then be able to pipe that data into a product called Splunk and correlate that data with all sorts of other metrics.

Anyway, I'm having some odd things happen (odd to me ... I'm sure there is a reason) with the sp_statement_completed event. The .xel file is growing SUPER fast ... like 1GB in a minute fast. Looking at the data I see the same SQL statements duplicated sometimes 75-100 times. What can I do to deduplicate/filter this data prior to going into the file? Many of them have a cpu of zero and duration of zero. I think I might even be seeing a little duplication using sql_statement_completed. I've also noticed that I do see stored procedure end times using the sql_statement_completed event. So, is there even a need to use sp_statement_completed?

Any thoughts would be most welcomed and thanks in advance.


texts: What is the best way to know the structure of a database

texts:
One of the things that I spend a lot of time when i start some proyect is to understand the relationships of the database provided, and the results of update or delete some foreign key

what is your way of learn a database structure? maybe you draw on paper? or some software


texts:
In our setup, we currently have 3 shard set sharded cluster, each shard being a replica set of 3. Our writes are about to go up significantly to implement a new feature, and we know the extra data will be necessary. The nature of our writes are basically all upserts(which will likely be updates) and updates where we increment a particular field by 1.

Our updates are always being incremented by 1 and the way our data is distributed, not all documents are treated equally, some get their fields incremented a lot more. An alternative solution that I thought could be effective is to have some type of middle man, like a few Redis databases (or some smaller mongods) where we do the updates to them first and after about 5 minutes (or use some queueing system), we have a bunch of workers consume the data and update the actual live cluster with the documents. This would save our main cluster a ton of writes as it would allow certain update heavy documents to accumulate their updates and could save us a ton of writes (exact numbers I will post shortly in an edit).

So bottom line, when is adding another shard not the right solution?