texts: How to use variables in a PL/pgSQL function with dynamic queries?

texts:
...and when must I use EXECUTE ... quote_ident(variable)?

Taking an input to the function as text and storing part as a variable:

wtype := split_part(message, ' ', 4);
realval := split_part(message, ' ', 6)::float8;

I then wish to use the text the variables hold in a statement,
I think this is the only way:

IF (OBJECT_ID('tempdb..temp_weather') IS NOT NULL) THEN
    -- exists: insert value into proper column
    EXECUTE 'UPDATE temp_weather
        SET ' || quote_ident(wtype) || ' = ' || quote_ident(realval) || '
        WHERE tID NOT NULL';

Is this effective/is this efficiently written or am I making this way overcomplicated?


texts: Calculate difference between current value and next value without CTE/ inline table

texts:
I have a table that has userid and his visit dates. I have created a CTE with adding a next visit column and using this to calculate day difference between visits.

With Visits AS
(
SELECT [uid],[visit_date]
            ,(SELECT MIN(visit_date)
             FROM Visit E2
             WHERE E2.visit_date > E1.visit_date AND
                   E2.uid= E1.uid ) AS next_visit_date
FROM Visit E1
)
SELECT uid, DATEDIFF(day, visit_date, next_visit_date)
FROM Visits

I know that a CTE or a temp table is not a memory efficient way, so looking to increase efficiency. Thank you


texts: Oracle DB refresh on 11g using 10g Cold backup

texts:
I am a sql dba, I am having only a little knowledge on Oracle. looking for help regarding oracle db restore.
I am having an Oracle 10g cold backup, i need to restore this on Oracle 11g setup, can you please help me understanding the process how to perform.


texts:
Is there any kind of equivalence between MariaDB versions and MySQL versions?
Obviously the 2 DBMS are different, but I was hoping the MariaDB team to "catch up" with MySQL when starting a new major version (like 10.2, 10.3...).
Equivalence in

errors, issues, bugs, ...
syntax,
features and behavior

On this MariaDB page all MariaDB versions are listed. Is there a way to know for each major version which MySQL release is the closest?


texts:
I have this trigger:

DELIMITER $$
CREATE TRIGGER geometrytrigger BEFORE INSERT ON users
FOR EACH ROW BEGIN
  SET NEW.user_geometry=GeomFromText(NEW.user_geometry);
END$$
DELIMITER ;

when I insert a row with this query:

INSERT INTO `users` (`id`, `name`, `email`, `password`, `user_geometry`) VALUES (NULL, 'arash', 'arash@host.com', 'sssssss', 'POINT(-0.23 169.93)');

this error has occurred:

 #1416 - Cannot get geometry object from data you send to the GEOMETRY field

edit:

show create table users;

result:

Table   Create Table
users   CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(127) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
 `email` varchar(127) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
 `password` binary(40) NOT NULL,
 `user_geometry` geometry DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`),
 UNIQUE KEY `email` (`email`),
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Also I use Percona 5.6.12 or MySQL 5.6.12


texts: MySQL: Assessing performance of a query by using EXPLAIN

texts:
It is said that the number of rows are scanned is smaller the performance is higher when use Explain to optimize query. So I wonder

When the EXPLAIN output includes many rows, is there any formula to
calculate the total number of rows which is scanned? After searching
on internet, it seems the total number of rows is the product of
values of all rows in EXPLAIN output. But does it makes sense in case
we use UNION?
Are there another criteria we should combine with 'rows' column in EXPLAIN output to know performance of query? Because if the formula is multiplying all number of rows, there are some cases the time execute the query is faster but the total number of rows is scanned is larger.


texts:
Here is some innodb table. It insert,update,query very frequently.

tn is product id

col is product sub id

tindex is product description.(store by each word)

date is product add date stored by strototime('now')

CREATE TABLE IF NOT EXISTS `mytable` (
  `tn` varchar(15) NOT NULL,
  `col` smallint(2) NOT NULL,
  `tindex` varchar(30) NOT NULL,
  `date` int(10) NOT NULL,
  KEY `date` (`date`),
  KEY `tn` (`tn`),
  KEY `tindex` (`tindex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

How I want to query some products which sub id contains words 'anticorrosive' and  'waterproof'

SELECT *
FROM mytable a
WHERE tindex =  'anticorrosive'
AND EXISTS (
SELECT 1
FROM mytable
WHERE tn = a.tn
AND col = a.col
AND tindex =  'waterproof'
)
ORDER BY DATE
LIMIT 10

full table is neally 12,700,000 lines, 1.1GB ,it cost 4.8356s, explian the query get

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY a   ref tn,tindex   tindex  92  const   1173    Using where; Using filesort
2   DEPENDENT SUBQUERY  mytable ref tn,tindex   tn  47  production.a.tn 161 Using where

And here is my.ini,

[mysqld]
character_set_server=utf8
skip-external-locking
skip-networking
key_buffer = 256M
tmp_table_size = 128M
max_connections = 1024
wait_timeout=10
back_log = 2048
key_buffer_size = 256M
max_allowed_packet = 2M
table_cache = 2048
table_open_cache = 1024
sort_buffer_size = 8M
read_buffer_size = 4M
net_buffer_length = 92K
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 256M
thread_cache = 512
query_cache_size= 256M
bulk_insert_buffer_size = 192M
ft_min_word_len=2
innodb_buffer_pool_size = 512M
innodb_flush_log_at_trx_commit = 2

How to speed up the query? re-do the index? query code? or change some value in my.ini

UPDATE 1
I have tried change index kye, and my table still grow up, now it has neally 20mln lines 2.2GB

ALTER TABLE mytable ADD UNIQUE INDEX (tn,col,tindex);
ALTER TABLE mytable ADD INDEX (date);

query 1

SELECT A.* FROM
(SELECT * FROM mytable WHERE tindex='words1') A
INNER JOIN
(SELECT tn,col FROM mytable WHERE tindex='words2') B
USING (tn,col) order by date limit 10

using 29.6278s

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    15674   Using temporary; Using filesort
1   PRIMARY <derived3>  ALL NULL    NULL    NULL    NULL    1716    Using where; Using join buffer
3   DERIVED mytable index   NULL    date    5   NULL    13918039    Using where; Using index
2   DERIVED mytable index   NULL    date    5   NULL    13918039    Using where; Using index

query 2

SELECT * FROM mytable
WHERE tindex IN ('words1','words2')
GROUP BY tn,col HAVING COUNT(1)=2 order by date limit 10;

using 23.8711s

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  mytable index   NULL    date    5   NULL    19359399    Using where; Using index; Using temporary


texts: Split rows and assign a specific value to each group

texts:
I have a scenario where i need to split rows in a table and assign certain values to a column for each group.
 The table name is customers(contains around 300K rows) and i need to split the customers in this table into 9 groups and update each group with a specific value in the "Value" column.

Anyone have any idea on how to do this using PostgreSQL?

This is the table structure(only a part)

Create table customers
(Customer_id  integer,
Customer_email TEXT,
updated_time TIME STAMP WITHOUT TIME ZONE,
Value TEXT
)

Insert into customers Values(1,'sss@gmail.com',now(),'')
Insert into customers Values(2,'abc@gmail.com',now(),'')
Insert into customers Values(3,'retro@gmail.com',now(),'')
Insert into customers Values(4,'als@gmail.com',now(),'')
Insert into customers Values(5,'s123@neferce.com',now(),'')
Insert into customers Values(6,'tt@gmail.com',now(),'')
Insert into customers Values(7,'a123@gmail.com',now(),'')
Insert into customers Values(8,'sabc@gmail.com',now(),'')
Insert into customers Values(9,'67890@gmail.com',now(),'')
Insert into customers Values(10,'qwert@gmail.com',now(),'')
Insert into customers Values(11,'dfg@gmail.com',now(),'')
Insert into customers Values(12,'fvc@gmail.com',now(),'')

The above is the sample table structure and values and now i want the customers in this table to be split into 4 groups(in real i have to split into more groups say like 9 or 10) and update the Value column with a specific text for the split groups.

so the first 4 rows will be assigned with a value 'dos-1-1'
second set of 4 rows with a value 'dos-2-2'
Third set of 4 rows with a value 'dos-3-3'
Fourth set of 4 rows with a value 'dos-4-4'

Below is the desired result,

ID        Email              Date         Value
1   "sss@gmail.com"     "2019-06-14"    "dos-1-1"
2   "abc@gmail.com"     "2019-06-14"    "dos-1-1"
3   "retro@gmail.com"   "2019-06-14"    "dos-1-1"
4   "als@gmail.com"     "2019-06-14"    "dos-2-2"
5   "s123@neferce.com"  "2019-06-14"    "dos-2-2"
6   "tt@gmail.com"      "2019-06-14"    "dos-2-2"
7   "a123@gmail.com"    "2019-06-14"    "dos-3-3"
8   "sabc@gmail.com"    "2019-06-14"    "dos-3-3"
9   "67890@gmail.com"   "2019-06-14"    "dos-3-3"
10  "qwert@gmail.com"   "2019-06-14"    "dos-4-4"
11  "dfg@gmail.com"     "2019-06-14"    "dos-4-4"
12  "fvc@gmail.com"     "2019-06-14"    "dos-4-4"

Above is the situation and in real the table consists of around 300k rows and need to split it into 9 groups and update each group with a  specific text.


texts: Disabling IPV6 on Always on availability group

texts: In our environment we only have one network adapters for the Replicas and IPV4 is being used. By default the IPV6 is also enabled. My question is will there be any problems if I unchecked IPV6 from the Adapter properties


texts: How to use 'mysql -uroot -pXXXX' on Debain 9.8?

texts:
My apologies for this lame question. Debian 9.8 changed some things and mysql is broke but I can't seem to fix it.

I installed MySQL on Debian 9.8, which now installs MariaDB 10.1. After install I ran mysql_secure_installation and set the root password, removed temporary databases and dis-allowed anonymous users. I also added my user to the mysql group, logged off and then logged back in.

The following fails on Debian with MariaDB 10.1, but works on Fedora 29 with MariaDB 10.1:

$ mysql -uroot -pXXXXXXXXXXXXXXXXXXXXXXXX
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
$ sudo mysql -uroot -pXXXXXXXXXXXXXXXXXXXXXXXX
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I thought the problem may be How to allow user to login to MySQL? so I used sudo su - to elevate, and then logged in to MySQL and executed the GRANT statements for root.

The Stack Overflow fix did not fix the problem. In fact I cannot seem to login from root now. Sigh...

I now think the problem may have something to do with this MariaDB knowledge base, but I don't know how to fix it. Secure passwordless root accounts only on new installs:

  Secure passwordless root accounts only on new installs

  Unlike the old MySQL packages in Debian, MariaDB 10.0 onwards in
  Debian uses unix socket authentication on new installs to avoid root
  password management issues and thus be more secure and easier to use
  with provision systems of the cloud age.

  This only affects new installs. Upgrades from old versions will
  continue to use whatever authentication and user accounts already
  existed. This is however good to know, because it can affect upgrades
  of dependant systems, typically e.g. require users to rewrite their
  Ansible scripts and similar tasks. The new feature is much easier than
  the old, so adjusting for it requires little work.

So they document they've done something but they don't document how to fix it. Sigh...

How do I run mysql from the command line with the root user and password? What needs to be fixed on Debian 9?


texts: all rows from left table with sum of amount from right table

texts:
I'm trying to return a list of customers and the sum of their transactions in the last week,   but have the query return customers who didn't wager at all. I am trying to do this with a left join, but the query only returns a row if a customer did wager, which is not how I expect the left join to work.   What can I do to make this work the way I want it?

select i.accountnumber, i.firstname, i.lastname, sum(a.amount)
from accountinformaton i
left join accountactivity a
    on i.accountnumber = a.accountnumber
where a.transactiontype = 'Bet'
  and a.transactiondate >='2015-07-31'
group by i.accountnumber, i.firstname, i.lastname

the 'accountinformation' table has thousands of rows,  the query above returns a few hundred.  I was expecting to be able to return all rows from 'accountinformation' and have the sum be null on accounts that didn't wager.


texts: What is a real-world use case for a marked transaction?

texts:
I have read BOL about marked transactions and I can see contrived examples, but what is a real world use case for MARK'ed TRANSACTIONS?

After reading this I was thinking I would implement marked transactions henceforth in our large batch jobs that run over night. We already use TRANSACTIONS and log backups, but I was hoping to get a use case where this saved time for someone as opposed to just adding it because I just learned about it.


texts: Index optimization for table that is truncated every day

texts:
I have a bunch of tables in SQL Server 2012.

These tables are fed by other tables. Every day there are routines that truncate (fully delete) them and run queries whose result are inserted into them. They are like static views, storing those processed data so that they are queried quicker.

And there are source tables, which receive millions of new records every day, these aren't deleted.

All these tables have a time when they get a lot of inserts, and in the rest of the day they are only read.

Even with these 'static view' tables, I think I can enhance overall performance. Is there any best practice to handle these behaviors?

For the first situation, I've run a few benchmarks and saw that the best performance comes from using nonclustered indexes on the most filtered field. I drop these indexes before truncating and recreate them after inserting. Dropping and recreating is faster than rebuilding, and inserts are faster too.

Should I keep PKs (without semantic meaning) and clustered index too? I suppose that with no index those inserts will be even faster, but will I have any issue if these tables have no PK? They are never used explicitly.

I ask this because when I create the table and set its PK with

idBenchmark int NOT NULL IDENTITY (1,1) PRIMARY KEY

clustered index is created automatically, with a random name, and therefore I can't drop and recreate it.


texts: PostgreSQL - Generate unique name on insert based on other columns

texts:
I have this table:

create table people (
    id serial primary key,
    name varchar,
    surname varchar,
    uname varchar unique
);

I want to generate the value of uname on insert, based on name and surname values, appending a number if there is a duplicate, for example:

insert into people (name, surname) values ('John', 'Doe');
insert into people (name, surname) values ('John', 'Doe');
insert into people (name, surname) values ('Sarah', 'Doe');
insert into people (name, surname) values ('Sarah', 'Doe');

will produce

 id | name  | surname | uname
----+-------+---------+----------
  1 | John  | Doe     | JohnDoe
  2 | John  | Doe     | JohnDoe1
  3 | Sarah | Doe     | SarahDoe
  4 | Sarah | Doe     | SarahDoe1

Is it possible to do it with PostgreSQL?


texts: Importing Oracle database from dump where source dump is spread across different tablespaces

texts:
How to import an Oracle schema from a dump file into another schema in new Oracle DB, where the target schema has only USERS and TEMP table spaces where as Source schema is having data distributed in several table spaces.

I mean I need to map all the tablespaces from source schema to USERS tablespace in target schema. How can I achieve this?

I am using this but not working.

impdp system/oracle@comverse2 schemas=epimart directory=DATA_PUMP_DIR dumpfile=mart.dmp logfile=epimart.log


texts: Records creates double entry, should only be one

texts:
Need an assist on a database design.

I'm creating a system to track students in a course.

Currently, I have courses.  Courses are made up of modules. A given module can be in multiple courses (eg 2 courses have the same module, so students from both course A and course B are in module Z.

Students are enrolled in modules.  So student john gets enrolled into all the modules that makeup course A.

However, when I put john into module Z, he shows up in course A and course B, even though he should only show in course A.

Currently, I have a student table, a module table, and a course table, linked together with a studentinmodule and moduleincourse table (both many to many).

Any insight into how to redesign so a module can be in multiple courses, but a student enrolled in a module only shows up in one course?


texts:
I'm using the following query to view files/folders in a given directory. It works locally, but when trying to view a mapped drive I get...

"The system cannot find the path specified."

I have the permissions for the whole mapped drive set to the service account associated with SQL, but it is still not working.

declare @files table (ID int IDENTITY, FileName varchar(100))
insert into @files execute xp_cmdshell 'dir Z:\ /b'
select * from @files

End goal is to create a stored procedure that will transact specific file names into a table.


texts: Best practices when storing logging information to table

texts:
A lot of applications store logging information in a log table.
These tables are special because you will only do inserts. You will never do updates. If you do deletes it will be a nightly job to get rid of old data.
The table has a datetime field which is typically strictly increasing. It is possible that the calling application will lose its connection to the db, and will stack up inserts, and at the point when it gets the connection it can't guarantee that it will do inserts in strict order. But in general I expect that it will be cheap to sort the table on insert if the datetime field is the clustered index.
Most queries will query on datetime, but these will be inequality queries.
With these special properties it feels like there should be a way to optimize it.
What are the best practices?
An example of a log table could be:
CREATE TABLE logMessages (
    logTime datetime2(6) NOT NULL,
    logSeverity varchar(10) NOT NULL,
    logStatus varchar(10) NOT NULL,
    logText varchar(255),
    processID bigint,
    processUser varchar(25)
)

A typical query:
SELECT logTime, logSeverity, logText
FROM logMessages
WHERE logTime >= '2020-10-01'
AND logTime < '2021-11-01'
AND logSeverity IN ('WARNING','ERROR','FATAL')

Unique id?
The datetime field is not unique, do we need a  unique identifier?
Option 1: add a logId BIGINT IDENTITY column
If we add a unique logId, it will not be used as a foreign key in any other tables.
If we use this as a clustered index alone and SQL Server decides that our query will retrieve too many rows for it to use a non-clustered index, it will do a full table scan.
Would it make sense to set the clustered index to (logTime, logId)? The reason I want this is that when the query optimizer will not use a non-clustered index on logTime because it expects too many rows, falling back on a good clustered index will reduce the number of rows to scan.
Option 2: not adding a logId
Set the clustered index to (logTime). Since logTime is not unique SQL Server will have to uniquify it by adding an invisible column. Is this a problem? At least I get a good clustered index that will help most of my queries.
Lookup tables
logSeverity is a small int with predefined values (DEBUG,INFO,WARNING,ERROR,FATAL), would it be better to move these to a different table and represent them with a tinyint?
Query hint: WITH (NOLOCK)
Experts typically say that you can use WITH (NOLOCK) if you don't need accurate data. Are there any situation where you can use it and still get accurate data? In this case we will not do UPDATE or DELETE, only INSERT. The inserts will also typically be at the end of the table (the datetime field will be increasing). A query that will not look at the last few seconds should not be affected by an insert.
To sum it up:

What should we do about unique key?
How should our clustered index be?
Should we use lookup tables?
Can we use WITH (NOLOCK)?
Any other best practices?

Thank you.


texts: Why the disparity between Filestream size and Container folder size?

texts:
I have an issue with one of my SQL Server 2014 databases. It appears to me that the Filestream size is significantly larger than the actual Filestream container folder size? ~360Mb larger.
The following query give me the location and size of the Filestream objects:
SELECT physical_name, (Size * 8 / 1024) AS SizeMb
FROM sys.database_files WHERE type = 2

C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\FilestreamDB_FS
867418

Yet if I go to file explorer right click and properties to view the size of the folder it is 502Gb:

These figures used to match until 2 weeks ago. Why the disparity?
I am not convinced that it is compression. The reason is that until about 2 weeks ago Filestream was growing slowly and was only ~490Gb but over the weekend of the 30th June the size suddenly jumped (see attached image) and no longer matches the folder size - which it used to. The only things running at the weekend would be maintenance jobs, such as index rebuild etc.
I have read How It Works: FileStream (RsFx) Garbage Collection and have tried to force the garbage collector to run; however, it did not free up any extra space. This database is in simple recovery mode.
Additional information
Database Size:

Database Size History:


texts: How do I automatically enable or disable report jobs at AOAG failover?

texts:
I have an AlwaysOn Availability Group with 2 DB servers. I'm attempting to use SQL Server Agent Alerts to programmatically disable a handful of SQL Server Agent jobs (that must not run on a secondary) when either of these DB servers is restarted (after it's been powered down for whatever reason).

I see that message 35266, e.g., AlwaysOn Availability Groups connection with primary database established for secondary database 'XXXX' ...... is logged when the DB server is powered up. So, I can set an alert for it and execute a job to disable the jobs I mentioned above. However, I've noticed that this message is consistently logged twice per database, one immediately after the other.

Why is message #35266 logged twice per database?


texts: What can data cache contain besides pages from physical disk?

texts:
While setting statistics io option ON, I see this result

physical_reads are 0 and read-ahead reads are 276 which means that 276 pages of that table which are on the disk drive were in data cache, but the whole number of reads from data cache is 794 as indicated in logical reads. So what is kept in data cache besides table pages from disk drive?
Thanks.


texts: updating 1st 5 digit of a column value in sql

texts:
I need assistance here for updating 1st 4 digit of a column value

for E.g I have value 999999999 (9 digit) and I need to mask 1st 5 digits with 11111

output should look like 111119999

However, in the same column I have a value called MIS I don't want to touch those value

What query should I use in Oracle?

What I have tried:

I have tried various update query like

UPDATE table SET TAX_ID = replace(TAX_ID, '4', '1111');


texts: Why "Begin Transaction" before "Insert Query" locks the entire table?

texts:
I am using SQL Server 2005 Express.

In a scenario, I added Begin Transaction command just before an INSERT statement in a stored procedure. When I executed this stored procedure, it locked the entire table and all concurrent connections showed a hung display till the time this INSERT finished.

Why does the entire table get locked and how do I overcome this problem in SQL Server 2005 Express?

Edited

Query is as below:

INSERT INTO <table2> SELECT * FROM <table1> WHERE table1.workCompleted = 'NO'


texts: Changing primary key from varchar to int

texts:
I have a user table where I am use varchar as primary key. Now from a different place I got the performance tip that int is better than varchar for primary key.

So I would like to change my primary key to int. For that I have created another user table and copied the data to that table except the previous primary key value.

Up to this there is no problem.

Now my problem is that I have many tables which are related to the user table through the previous primary key. Now I want to relate the rows of the other tables through to new primary key value.

How to do this?


texts:
I want to understand :

How index work in oracle.

How they are stored in the disk.

How they increase the efficiency of a query?

By third point I means how Index contribute to the efficiency of query such as in joins,group by clauses.

One more point is How index behave in Flashback queries.


texts:
I'd like to run a query every few seconds and show the output in a console window.  From bash, using watch:

watch "mysql -A -pXXXXX -h host -D dbname -u username --vertical -e \"SELECT rowID FROM IDs LIMIT 1\""

With the output:

*************************** 1. row ***************************
rowID: 68434810

I'd like to not disconnect and reconnect every 2 seconds though.  Is there a better way?


texts: ORA-00845 after deleting files accidentally at /dev/shm

texts:
I have two instances (ORA1 and ORA2) of Oracle 11g at the same machine. One is starting the other cannot startup anymore.

What happend:

First my disk was full, so I accidentally deleted all ora*-files under /dev/shm. After deleting other files I had enough disk space to start instance ORA2. But when I tried to start ORA1, I get ORA-00845: MEMORY_TARGET not supported on this system

I cannot even start ORA1 in nomount phase:
    SQL> startup nomount;
    ORA-00845: MEMORY_TARGET not supported on this system

ORA1 and ORA2 should be configurated equally. /dev/shm has 2G and following parameter are set in ORA2:

SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     32
pga_aggregate_target                 big integer 382M
sga_target                           big integer 1152M

What needs to be done? Any hints or explanation appreciated.

Thanks in advance!


texts:
After patching an Oracle 12c database (Windows environment, container DB and 3 PDBs), is there any way to predict or estimate how long it will take to run DataPatch utility? Or to see how far along it has gotten once I start it? I'd like to be able to schedule a downtime window for an environment, but I can't seem to find anything that indicates how long that utility will be processing or what step out of how many steps it has completed.

I have a test environment of roughly similar size, but DataPatch doesn't provide a "percent complete" indicator or anything to let me know how far along it is. Datapatch has been running for roughly 1.5 hours in this test environment. I don't know if it's nearly finished or stalled out/locked up, or what it is doing. The SqlPatch folder contains 11 text log files, but none has a timestamp that has changed in the last hour.


texts: PostgreSQL PSQL client-side commands and new lines (\COPY)

texts:
The error message is the same with super user using COPY. The files are  located on the same server as the postgres server. Saw many similar posting but not a single one answer my question.

\copy table_name from '/path/to/csv/file.csv'
with format csv, header true ;

ERROR: syntax error at or near "format"

Tied with parenthesis or not.  Read the manual at version 10 for postgres carefully.  Not helping.  I must be missing some minor point, please point out.


texts: Scripting all of available databases to .sql files containing schema+data+users with single command

texts:
I have a SQL 2008 Express with 80 databases in it. I want to move these databases completely to another server which is hosting a SQL 2008R2 Enterprise instance.

As i'm told it is better to script the databases and run the script on the new instance.

I can do this in 2 ways:
1. Running a script on the 2008 server to get all of users and passwords and user mapping to databases and then Script all of the databases (without users information) and then restore databases on new instance and then run the users script for logings and users mapping to databases. i have been told it is a better way

-2: Script all of the databases using Script to WIZARD and check-mark DATA+SCHEMA+USERS and then script the database to .SQL file and run this SQL script on the new instance.

What is your suggestions? Which is the best way to this without facing any issue.
Kind regards


texts:
I understand that the recommended way to backup MongoDB data is to use file system level tools. However, mongodump utility is promoted as a valid alternative for smaller instances, when file system snapshots are not available for some reason. Many resources recommend running it with --oplog option to enable point-in-time restores.

I tried to understand how --oplog could enable point-in-time snapshots without proper multi-version concurrency control from the database engine. I think it can only work in the following way:

We assume that _id values in all collections are monotonously increasing
Mongodump blocks all writes and records last operation id in the oplog
Mongodump starts dumping documents from all collections by navigating _id index in decreasing order
After dump has started, mongodump unlocks writes to the collections
We assume that all inserts from now on will have higher _id values and will appear only in oplog dump, not collections dump.

This system quickly breaks if _id values are not monotonously increasing or if write locks are not implemented and there is a race condition. Also, there is no support for secondary unique indexes.

From what I see, the following statements are true, meaning that mongodump cannot possibly enable point-in-time restores, so it's specification is misleading as it comes to its snapshot-like capabilities:

Even build-in _id values generators do not guarantee total order
mongodump does not issue any write locks
mongodump does not traverse _id index in descending order, so inserted records will appear both in collection dump and oplog dump.

Is my understanding correct? Is there any value in --oplog option of mongodump utility?

Following are the tickets which seem to enforce my suggestions:

https://jira.mongodb.org/browse/SERVER-24231
https://jira.mongodb.org/browse/TOOLS-176


texts: Putting an order in the way how locks are aquired in MySQL

texts:
From MySQL documentation:

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.

When I'm performing an UPDATE (although I would be interested about the others also, now I'm concerned with UPDATE statement), is there a way to put lock in the same order, so that to avoid deadlocks as much as possible?

N.B. Deadlocks occur in my table due to concurrent updates and inserts (and even deletes).


texts: Reducing size of datatype involved in index fails where as increasing size work

texts:
This question is related to my previous question Database events that will cause deletion of index

For example if i create a table like

CREATE TABLE dbo.Customers (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, CustomerName NVARCHAR(200));
CREATE INDEX IX_CustomerName ON dbo.Customers(CustomerName);

And then try to change column datatype like shown below to 230 from 200.

alter table Customers
alter column CustomerName nvarchar(230) null

The above alter command runs successfully and index IX_CustomerName is not dropped.

But if i try to reduce the size of column to 150 from 200 then it shows error.

alter table Customers
alter column CustomerName nvarchar(150) null

I get error like

Msg 5074, Level 16, State 1, Line 1
The index 'IX_CustomerName' is dependent on column 'CustomerName'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN CustomerName failed because one or more objects access this column.

So reducing the size(data may loss) of a column does not work if index is present on a column where as if the index not present then above query works successfully.
So why does it does not allow reducing the column when index present where as increasing size does not show any problem and works?


texts:
Here is my table structure:

create table table_a (emp_name varchar(100), dept_name varchar(100));
insert into table_a values ('amar', 'finance');
insert into table_a values ('akbar', 'human_resource');
insert into table_a values ('amar', 'finance');
insert into table_a values ('anthony', 'information_technology');

create table table_b (emp_name varchar(100), dept_name varchar(100));
insert into table_b values ('amar', 'finance');
insert into table_b values ('akbar', 'human_resource');

mysql> select * from table_a as a left join table_b as b on a.emp_name = b.emp_name and a.dept_name = b.dept_name where b.dept_name is null;
+----------+------------------------+----------+-----------+
| emp_name | dept_name              | emp_name | dept_name |
+----------+------------------------+----------+-----------+
| anthony  | information_technology | NULL     | NULL      |
+----------+------------------------+----------+-----------+
1 row in set (0.00 sec)

The left join mentioned above works as expected.

However, what I need is one record for "amar" as well because there are 2 entries for amar in table A and only 1 entry in table B. So, the result should look like this...

+----------+------------------------+----------+-----------+
| emp_name | dept_name              | emp_name | dept_name |
+----------+------------------------+----------+-----------+
| anthony  | information_technology | NULL     | NULL      |
| amar     | finance                | NULL     | NULL      |
+----------+------------------------+----------+-----------+
2 row in set (0.00 sec)

I need this in an environment where count accuracy is more important than data integrity. The table B record number (2) + query results (2) should match with the table A record numbers (4)


texts: Unable to view partition table in a schema

texts:
I am using oracle 11g enterprise edition,

I have created a schema as "TESTSCHEMA"  and in it i have created a partition table as "sales_test".

The query for the table,

create table sales_test (year number(4),
                    product varchar2(10),
                   amt number(10,2))
     partition by range (year)
     (partition p1 values less than (1992),
     partition p2 values less than (1993),
     partition p3 values less than (1994),
     partition p4 values less than (1995),
   partition p5 values less than (MAXVALUE));

The problem is,
After creating the table it does not display the table from dbvisualiser, but it can display unpartitioned tables.

I have created one user of my own as MDR_139 and it has ALL PRIVILEGES like what DBA has.


texts: In slow_log tables, sql_text column showing "throttle: index not used' warning(s) suppressed" instead of exact query

texts: I'm trying to find the slow running queries from slow_log table (as I don't have direct access to server to check slow log file). In sql_text column instead of query it is showing "throttle: index not used' warning(s) suppressed". Could someone help me how to find the slow running query.


texts:
EDIT] The query below is not the actual query which I am having issues with performance. It is only a simplified query. As I only would like to know if it is possible to store multiple results into a variable and used that variable into the IN condition.

I had this query below inside a procedure  which I would like to change for optimization.

SELECT * FROM request WHERE facility_id IN (SELECT facility_id FROM facility);

What I would like to do was to store the subquery into a variable and used that variable on the main query.

SET @facilities = (SELECT facility_id FROM facility);

SELECT * FROM request WHERE facility_id IN (@facilities);

But I got an error Subquery returns more than 1 row.
How should I do it?
Any help is appreciated.

I have found some link that suggest that way http://www.mysqlperformancetuning.com/how-to-speed-up-mysql-by-optimization?nocache=1#comment-961. But I cannot figure it out.


texts: PostgreSQL: initdb fails with: 'FATAL: could not access file "$libdir/euc_kr_and_mic": No such file or directory

texts:
I have installed postgis-bundle-pg96x64-2.3.1-1 on my Windows workstation. I'm trying to create a database but initdb fails with

  "FATAL:  could not access file "$libdir/euc_kr_and_mic": No such file or directory"

Any idea why this is happening?

I have uninstalled and installed the package just in case, and rebooted the machine...

Here is the full log:

$ initdb -D PGDATA -E utf8 --no-locale -U $PGUSER
The files belonging to this database system will be owned by user "PKuchta".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory PGDATA ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... windows
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... FATAL:  could not access file
"$libdir/euc_kr_and_mic": No such file or directory
STATEMENT:  -- EUC_KR --> MULE_INTERNAL
        CREATE OR REPLACE FUNCTION euc_kr_to_mic (INTEGER, INTEGER, CSTRING,
INTERNAL, INTEGER) RETURNS VOID AS '$libdir/euc_kr_and_mic', 'euc_kr_to_mic'
LANGUAGE C STRICT;
        COMMENT ON FUNCTION euc_kr_to_mic(INTEGER, INTEGER, CSTRING,
INTERNAL, INTEGER) IS 'internal conversion function for EUC_KR to
MULE_INTERNAL';
        CREATE DEFAULT CONVERSION pg_catalog.euc_kr_to_mic FOR 'EUC_KR' TO
'MULE_INTERNAL' FROM euc_kr_to_mic;
        COMMENT ON CONVERSION pg_catalog.euc_kr_to_mic IS 'conversion for
EUC_KR to MULE_INTERNAL';

child process exited with exit code 1
initdb: removing data directory "PGDATA"


texts: "Recover" data from running PostgreSQL instance; data directory was deleted

texts:
I accidentally removed data directory /var/lib/pgsql/9.1/data from running postgresql server.

I guess I'm going to lose my data when server will be restarted.

I'm wondering if postgresql has any tools to recover data from running server instance or I have to use system data recovery tools?


texts: Oracle 10g: query takes long time and adding index does not help

texts:
I have a query which takes indefinite time, taking almost 60% of CPU time for 3 hours then simply hangs the PLSQL process.
What I do is:

Get "explain plan" for the query
I noticed a "table scan" in the "explain plan" graph
I added an index for the specific column
I refreshed statistics for this table

I now get "explain plan" but the figures are the same and no execution optimization.

Any ideas?


texts: updatable entities in the design of databases

texts:
I have a question on Entity Relationship design.

It is a shop where products are kept in stock first and then go to the store. When the store has less than a certain amount of product, products that are in stock go to the store. How would you design these entities?

I thought two entities products_store and products_stock but I don't know if there is another, more practical way.

Thanks.


texts: When hash indexes should be used in Oracle?

texts:
Hash indexes are usefull when you have a large table with URLs and you need to query by them. So, a solution would be to have an additional column "url_crc32" that will be filled with hashed URL value via trigger on inserts. An index on url_crc32 would be definitely faster than index on URL column that is a type of text.

It's not very common case to query data by URL. The more frequent case would be to query text data by fragment and hash indexes are useless in such case. So, I'm curious do you use such hash indexes, and if so then when do you use? IMHO Oracle does not have native hash indexes, so that must be done manually.


texts: Getting an error ORA-00933 while using EXECUTE IMMEDIATE

texts:
Hi i am using 1 dynamic query which has a very big length size as below.

v_DymnamicQuery :=
    'WITH TempResult as
    (
        SELECT
        f.rbase_fmla_name as FilingName, f.user_comp_eff_dt, f.user_rnwl_dt, f.user_n_bus_dt, f.pf_wkstn_oid_lng, f.pf_wkstn_oid_sh, f.fmla_flng_u_exp_dt as expDate,
        rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn) as FileTag,
        ''Formula'' as DisplayType, null as Origin, f.wkstn_oid_lng, f.wkstn_oid_sh, CAST(f.iteration_number AS varchar(10) )||''.''||CAST(f.minor_version AS varchar(10)) as version, status_indc, f.status_indc as WIPStatus,
        upper(RTrim(f.rbase_fmla_name) ||''-''|| CAST(f.user_n_bus_dt AS varchar(10)) ||''-''|| CAST(f.user_comp_eff_dt as varchar(10)) ||''-''|| CAST(f.user_rnwl_dt AS varchar(10)) ||''-''|| CAST(f.iteration_number AS varchar(10)) ||''-''|| rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) as NameWithDates'
        || v_FmlaQuery ||
        ' from fmla_flng_adptn f '
        || v_FmlaCondition ||
        ' where '|| p_isFormulas ||'= 1 AND (rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) = COALESCE('||v_FileTag||', (rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)))
        and f.rbase_fmla_name like ''%''|| COALESCE('||v_name||', f.rbase_fmla_name)||''%''
    union all
        SELECT
        f.rbase_rttbl_name as FilingName, f.user_comp_eff_dt, f.user_rnwl_dt, f.user_n_bus_dt, f.pf_wkstn_oid_lng, f.pf_wkstn_oid_sh, f.rt_flng_u_exp_dt as ExpDate,
        rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn) as FileTag,
        (case f.rt_flng_type_indc when ''C'' then ''Constant Table'' else ''Keyed Table'' end) as DisplayType, null as Origin,f.wkstn_oid_lng, f.wkstn_oid_sh, CAST(f.iteration_number AS varchar(10))||''.''||CAST(f.minor_version AS varchar(10)) as version, status_indc, f.status_indc as WIPStatus,
        upper(RTrim(f.rbase_rttbl_name) ||''-''|| CAST(f.user_n_bus_dt AS varchar(10)) ||''-''|| CAST(f.user_comp_eff_dt AS varchar(10)) ||''-''|| CAST(f.user_rnwl_dt as varchar(10) ) ||''-''|| CAST(f.iteration_number as varchar(10)) ||''-''|| rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) as NameWithDates'
        || v_TableQuery ||
        ' from rt_flng_adptn f
        where '|| p_isTables ||'= 1 AND rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn) = COALESCE('||v_FileTag||', rtrim(f.flng_origntr) ||'' | ''||rtrim(f.flng_lob)||'' | ''||rtrim(f.flng_regn)) and f.rbase_rttbl_name like ''%''|| COALESCE('||v_name||', f.rbase_rttbl_name)||''%''
    ),
    UnionTable as
    (
        SELECT ROW_NUMBER() OVER(ORDER BY ' || p_SortColumn || ') as RowNumber,
        FilingName, user_comp_eff_dt, user_rnwl_dt, user_n_bus_dt, pf_wkstn_oid_lng, pf_wkstn_oid_sh, expDate,
        FileTag, DisplayType, Origin, wkstn_oid_lng, wkstn_oid_sh, version, status_indc, WIPStatus,NameWithDates '
        || v_UnionQuery ||
        ' from TempResult
    )
    SELECT *
    FROM UnionTable
    WHERE RowNum > '||v_FirstRec||'
    AND RowNum < '||v_LastRec||';
    --AND RowNum = '||v_LastRecForRow;

    dbms_output.put_line(v_DymnamicQuery);

    EXECUTE IMMEDIATE v_DymnamicQuery;

while running the EXECUTE IMMEDIATE it is throwing an error as:
ORA-00933: SQL command not properly ended
ORA-06512: at "RATABASE.GETFILINGSWITHPAGING", line 120
ORA-06512: at line 5

even when i am running the same query(string) which comes from
dbms_output.put_line(v_DymnamicQuery);
it is successfully running without any error.

can someone help me how to use this or handle this situation.


texts: How I can truncate and shrink the Logs file in mirroring (Full Recovery model)?

texts:
I am trying to truncate and shrink the database. I have taken the backup of transaction logs and now trying to shrink but it's executing with No effect.

I have used dbcc shrinkfile(). Please help


texts: SQL Server - Move data from secondary ndf file to primary mdf file

texts:
SQL Server 2016 - We have two files - one mdf on Primary and one ndf on Secondary filegroup.

We want to move data from secondary to primary and then delete secondary:

Approach 1:

Create clustered index on table which are on secondary and setting location of index to primary (Question: is clustered index only option or non clustered index will work too)

Approach 2:

DBCC SHRINKFILE (nameofdatafile, EMPTYFILE);
GO

-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2012
REMOVE FILE Test1data;
GO

But files are in different file group, will above works

Any suggestions


texts: syspolicy_purge_history job failed at step 3

texts:
In one of my SQL Server 2008 sp1 standalone instances, syspolicy_purge_history job is fails at step 3 Erase Phantom System Health Records.

Below is the command -

(Get-Item SQLSERVER:\SQLPolicy\servername\DEFAULT).EraseSystemHealthPhantomRecords()

Error :

  Executed as user: domain\xxxxx. The process could not be created for step 3 of job 0x3183E8EEDE9C3A4DA0F20ABD41B573DC (reason: %1 is not a valid Win32 application). The step

Service account is same for SQL Server and Agent and it is admin on the server. I'm aware that clustered instances usually fail because of virtual name mismatch but I tried searching Google but I don't see correct solution to this problem.


texts: MSSQL - Unable to find index entry in index ID 1

texts:
Currently keep getting this severity 21 error through for a database that is hosted on SQL 2014 Enterprise with SP3 CU1 applied:

  DESCRIPTION:  Unable to find index entry in index ID 1, of table 2130106629, in database 'DatabaseName'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.

I've run the following to get the table name that is associated with the number:

select object_name(2130106629)

This then gives me the table name within the database. Looking at the table, there is only 2 rows of data and only one index, which is clustered on this table. I have done the DBCC checks for DB and table and these both come back clear.

I've also tried dropping the clustered index, but the error still comes through as:

  DESCRIPTION: Unable to find index entry in index ID 0, of table 2130106629, in database 'DatabaseName'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.

I'm at a bit of a loss as to what to try next? Any ideas?


texts:
On some tables, an autovacuum: VACUUM ANALYZE public.some_table process will run for 30 min to as long as 10 hours. During this time, updates to the table in question are often locked for 10+ minutes.

As soon as I cancel the auto-vac process, the code that depends on updating those tables is suddenly able to finish executing.

The tables being affected range from 78 GB to 7 GB with millions of rows each on an AWS RDS db.m4.10xlarge with PostgreSQL 9.6.3.

What settings do I need on a per table or system basis to make sure we get a performance boost from auto-vac analyze without interfering with critical query execution speed? If that is too much of a shot in the dark, what query could I run to tell what settings would be optimal for our setup?

These tables have a LOT of updates/deletes/inserts every minute, would changing FILLFACTOR help here as well to reduce the need for auto-vac? I've been iffy on this because some of the most commonly updated columns have indexes and I also don't know how to analyze the tables to tell if fill factor would really be beneficial.

Currently, fill factor is 100% on all tables. Here are other relevant performance settings for the database (let me know if I missed anything salient):

autovacuum = on
autovacuum_analyze_scale_factor = 0.05
autovacuum_analyze_threshold = 50
autovacuum_freeze_max_age = 200000000
autovacuum_max_workers = 3
autovacuum_multixact_freeze_max_age = 400000000
autovacuum_naptime = 30
autovacuum_vacuum_cost_delay = 20
autovacuum_vacuum_cost_limit = -1
autovacuum_vacuum_scale_factor = 0.1
autovacuum_vacuum_threshold = 50
autovacuum_work_mem = -1
effective_io_concurrency = 1
log_autovacuum_min_duration = -1
maintenance_work_mem = 2702336
max_parallel_workers_per_gather = 0
max_worker_processes = 8
rds.force_autovacuum_logging_level = disabled
shared_buffers = 5151945
temp_buffers = 1024
vacuum_cost_delay = 0
vacuum_cost_limit = 200
vacuum_cost_page_dirty = 20
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_defer_cleanup_age = 0
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 150000000
vacuum_multixact_freeze_min_age = 5000000
vacuum_multixact_freeze_table_age = 150000000
work_mem = 4096


texts:
I have a development environment with two shards, one configuration server and one router as illustrated below. I have a sharded database "mydb2" and a sharded colleciton "mycollection2". When I insert data to mycollection2 from router, it is inserting to s1. And next time s2. And s1 again and so on. How can I insert data to both two shards same time. I am using MongoDb 4.2.3

UPDATE: I added s1 and s2 to the same zone. I declared a shard key range. But nothing changed. I insert data to the collection with ranged shard key, bu data is not distributed.


texts: Mysql show no more than 5 concurrent connection

texts:
I'm performing some load tests on my brand new server Apache/PHP/Mysql (Bitnami LAMP stack ). I'm using AWS with 1 EC2 instance behind a loadBalancer. At the moment I'm using loader.io for the tests. I'm testing a very simple API that perform this query:

select *,sleep(0.5) from debug limit 1

I added the sleep(0.5) because I wanted to see how the server behave with multiple concurrent connections and I found a bottleneck: if I run "SHOW PROCESSLIST" I can see only 5 process even if I have 10 concurrent user. The load test show that the connections are queued because the response time is growing during the test from 500 milliseconds to several seconds (depending on the duration of the test and the number of concurrent users).

I checked

select @@max_connections

and it's 151 (the default). max_user_connections is 0. What other parameter should I check to increase the number of concurrent connection on my DB?

If I run the test with 5 concurrent users, each one get a response in 500 milliseconds. If I add more concurrent users than the response time slow down.

If I run the load test on an API that does not access the DB there are no issues even with 400 concurrent users.

Monitoring with HTOP I see:

  Tasks: 34, 245 thr; 2 running

Could be here the issue?

Thanks a lot


texts: Which server to be master and which one slave database?

texts:
I have an application for which I want to set up a master/slave mysql database, because the current db struggles under high load.

I have two different servers for db replication. Lets call them A and B.
A is the current db server.

Let's score the CPU power of A as 10 and B as 7.
B has half the memory of A. Both servers run on SSD.

The read/write ratio is about 9 to 1.

Now, my question is which server should be master? and why?


texts: Postgres 9.5 import to MSSQL Server 2012 r2 verification issue

texts:
I have a postgresql table with 8 million rows. I need to copy it over to MSSQL. I do not have access to the console so I am unable to use PG_Dump. I have the ODBC driver, and have successfully linked up the servers. I was then able to copy the table over month by month. This was slow and painstaking. I need to create a query in postgresql that will list the year, month and total rows per month. The date field was actually created as text. I have tried this but do not get the results I am looking for.

SELECT distinct
        date_part('Year',case
        WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
        else
        to_date(trandate, 'MM/DD/YYYY')
        end) as Year
    ,date_part('Month',case
        WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
        else
        to_date(trandate, 'MM/DD/YYYY')
        end)
         as month
    ,count(date_part('year',case
        WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
        else
        to_date(trandate, 'MM/DD/YYYY')
        end)) as "trandate sum"--date_part('Day',case
    --  WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
    --  else
    --  to_date(trandate, 'MM/DD/YYYY')
    --  end))
  FROM SomeSchema.transaction_detail

  Group By trandate
  order by date_part('Year',case
        WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
        else
        to_date(trandate, 'MM/DD/YYYY')
        end)
        ,date_trunc('Mon'  date_part('Month',case
        WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
        else
        to_date(trandate, 'MM/DD/YYYY')
        end))

Is there a better way to do this?


texts: Ubuntu MS SQL Server RESTORE fails beacuse 'LOG_BACKUP' is full

texts:
I have a MS SQL SERVER 2008 backup (all files .bak). I have a computer with Ubuntu and I want to restore the backup.

First I installed the SQL Server on Ubuntu with this link

Then I installed DBeaver

Now I am trying to restore the databases with this command

RESTORE DATABASE dfy2_alquiler
FROM DISK = '/var/opt/mssql/backup/dfy2_alquiler.bak'
WITH MOVE 'dfy2_alquiler_Data' TO '/var/opt/mssql/data/dfy2_alquiler_Data.mdf',
MOVE 'dfy2_alquiler_Log' TO '/var/opt/mssql/data/dfy2_alquiler_Log.ldf';

But I am getting this error

  SQL Error [9002] [S0002]: The transaction log for database 'dfy2_alquiler' is full due to 'LOG_BACKUP'.
    com.microsoft.sqlserver.jdbc.SQLServerException: The transaction log for database 'dfy2_alquiler' is full due to 'LOG_BACKUP'.

I am not sure how can I solve this?


texts: How to link data from a table to another table in sqlite database?

texts: I need to link data from a table to another table. I'm not sure how to do this. I have never link a table to another table. Anyone can help me? Or maybe can give me examples or sample codes? Thanks =)


texts: Why does JSON_QUERY within CASE statement fail to remove escape characters?

texts:
It would seem that json_query fails to remove escape characters for the double quote character (") if the function is being applied within a case statement.

Example code below.

declare @data nvarchar(max);
declare @debug int = 0;

set @data = '{"id":10}';

set @debug = 0;
select
    isjson(@data) as 'validateData',
    @data as 'unprocessedSourceData',
    json_query(@data) as 'processedSourceData',
    case when @debug = 1 then json_query(@data) else null end as 'conditionallyProcessedSourceData'
    for json path, without_array_wrapper ;

set @debug = 1;
select
    isjson(@data) as 'validateData',
    @data as 'unprocessedSourceData',
    json_query(@data) as 'processedSourceData',
    case when @debug = 1 then json_query(@data) else null end as 'conditionallyProcessedSourceData'
    for json path, without_array_wrapper ;

Result from the first query:

{"validateData":1,"unprocessedSourceData":"{\"id\":10}","processedSourceData":{"id":10}}

Result from the second query:

{"validateData":1,"unprocessedSourceData":"{\"id\":10}","processedSourceData":{"id":10},"conditionallyProcessedSourceData":"{\"id\":10}"}

Note the escape characters toward the end: "{\"id\":10}"

1) Why would this be so?

2) How do I rework this query so that if @debug = 1 then the conditionallyProcessedSourceData element contains valid JSON, and if @debug = 0 then the element is null?


texts:
I have had three incidents in recent months where records in a table that have either been deleted or values updated to zero  across a whole table. We have a team of four people   who have the permission and who are responsible for updating the database who could have done this. Disappointingly no one has admitted to carrying out the changes.

Going forward I would like to be able to have a record of these transactions. I was wondering what do other people use to track these changes? Do they use software that tracks changes or do you create stored procedures or trace files? If anyone has this set up at their facility I would like to know what they use. The trace files do have the information I am after such as login name machine number and the sql statement so it will give me the information if I set them up in advance.

I have copies of the database and the transaction logs when these changes took place. Is there anything I can do with these old files to help track down the culprit? Thanks in advance to anyone who replies. We are using SQL server 2005.


texts: Literature about In-Memory Database Systems

texts:
I am currently writing my bachelor thesis on IMDBS and would like to ask if any of you have any recommendations for literature which summarizes the technical conception/architecture of IMDBS as apparently I am not able to find articles, papers, books etc which are covering the physical architecture or to In-Memory Database Systems but just on logical data storage, indexing etc.

Any recommendations would be highly appreciated.

Thanks in advance,
toni


texts:
Running the following...

SELECT
      a.allocation_date, a.trade_account, a.trade_broker, a.account,
      a.custodian, a.book, a.symbol, a.side,
      SUM(a.quantity)::integer,
      SUM(a.quantity*a.price) / SUM(a.quantity) AS price
    FROM backoffice.allocations a
    inner join symdb.products.product s
    on (symbol=products)
    WHERE a.allocation_date BETWEEN '2020-01-01' AND '2020-12-30'
    GROUP BY
      a.allocation_date, a.trade_account, a.trade_broker, a.account,
      a.custodian, a.book, a.symbol, a.side
    ORDER BY
      a.allocation_date, a.trade_account, a.trade_broker, a.account,
      a.custodian, a.book, a.symbol, a.side;

...is throwing...

  SQL Error [0A000]: ERROR: cross-database references are not implemented: "symdb.products.product"

The INNER JOIN part is what I can't get to work. How do I fix it?


texts: For SQL Server, What is the proper way to update a table?

texts:
I don't have a mentor to ask so I am seeking direction from the DBA community. I am in charge of a SQL SERVER 2008 R2 and SQL Azure databases that are the resources for a website and an ERP system for a small company. I've already made the mistake of updating without having a WHERE statement (luckily it only affected a table w/ 30 rows). But with some table containing 20K+ rows, what is the proper way to update tables in SQL Server? Do I create a test DB copy and perform the update on that first (but this doesn't have the website or ERP connected to it, so I can see the
results in regards to those), or is the TRANSACTION function the direction I should study and learn wit it's ROLLBACK feature that seems might be the right way, Or maybe there is some other process all SQL SERVER DBA follow. Any and all advice will be appreciated.


texts: Is it possible to scale-out with Microsoft SQL Server?

texts:
Is it possible to scale-out a SQL Server database?

There are some solutions for fail-over clustering and always on, but is there a solution for load balancing and horizontal partitioning on SQL Server?

There are some in Azure, but not in a standalone SQL Server instance...


texts: Start MongoDB With Authentication Enabled

texts:
Well I am new in MongoDB. Just started using 2 tutorials from tutorialspoint and w3resource. I am really enjoying learning it. I think it is really the future of database management. However I have faced some problems and I want to clear my conceptions on those issues. Here is the first question of the stack of noted questions. Any help will be highly appreciated.

I use Ubuntu 14.04 LTS (32bit machine and O/S). My MongoDB version is 2.6.11. I use terminal to toy with MongoDB. According to MongoDB website I start the server mongod using this command...

$sudo service mongod start

Now to start the server with authentication enabled what should be the command? I tried using

$sudo service mongod --auth start

but it doesn't work.


texts: Can a poor database layout cause performance issues for simple queries?

texts:
I have a database layout that is realy a mess and needs to be optimized. Does the layout of the database and some queries that are executed cause other queries to be slow too?

For example, an insert into table that has about 100 000 rows takes 9 seconds to execute. Could it be caused by a bad database layout, or should I search for performance bottlenecks elsewhere?

I have 3 tables:

aiki_users
ocal_files (connected with both id and username not foreign keys/refeerences) I have tags in field (sparated by coma instead of separateded table)
ocal_favs with favorites (suppose to connected with ocal_files and aiki_users) but no real connection (foreign keys)

There are slow queries that use LIKE in this way: upload_tags not like '%pd_issue%'

or

select *
from ocal_files
where upload_tags LIKE '%summer2010%'

These take 5-7 seconds to run (on heavy load). On heavy load (right now) we have 200 active users (shown by Google analytics).

Some of the most frequent queries that use a lot of LIKE and RLIKE searches were replaced by Sphinx; those pages run fast.


texts:
I have DB backup with size approx. 150 Mb.

I am trying to restore it and getting an error:
"System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'C:\' to create the database. The database requires 67771760640 additional free bytes, while only 45085224960 bytes are available. (Microsoft.SqlServer.Smo)"

Restore is about to create DB with much bigger size than backup has. Is there a possibility to reduce size of DB being restored?


texts: How to get CPU usage of PostgreSQL server?

texts:
I'm reading about PostgreSQL monitoring tool. I get a lot of information like database size etc. but can't find info about CPU and memory usage.
Are these informations provided by PostgreSQL server or do I have to get it by my native client application (for example, find PID of PostgreSQL and call OS API function).


texts: I need help on writing a server-side trace to check logins to try to determine if and when vendor accounts are used. The purpose is to remove sa access. These accounts sure have sa but I need to figure out if I could give them the least permission required to do the job.


texts: Creating range for special fields in SQL Server

texts:
I have two columns that look like this:

Start_Post  End_Post
----------  --------
102+20.45   153+19.22
120+21.25   220+25.30
...         ...

And I want to introduce a constraint to each column:

Start_Pos
Min Range: 100+50.30
Max Range: 150+20.65
End_Pos
Min Range: 150+60.30
Max Range: 500+20.75

All values are in feet and follow a special format. It's a construction convention and I am not an expert but to my knowledge, 1+00 would be 100 feet. Essentially, taking the above range of 100+50.30 to 150+20.65 would mean a distance of 4970 ft. What I did was just remove the plus sign and subtract the two numbers. Hope that made sense.

Is there a way to define this constraint whilst maintaining the format?


texts: Is DB2 more reliable and stable compared to PostgreSQL or MySQL?

texts:
Always i was using MySQL or PostgreSQL for my projects such as Web/Desktop apps.
I had data loss few times and thanks to old backup i was always up and running.

But for a recent project for Airport, i have to use something which never ever can lose its data at-least not database until it has power in the circuit..

So i am thinking DB2 should be the best for such risk less missions? Any idea or hints.

Thanks in advance.


texts:
Getting the following error when trying to install sp_Blitz on a Azure SQL Database

  Msg 40515, Level 15, State 1, Procedure sp_Blitz, Line 16 [Batch Start
  Line 15] Reference to database and/or server name in
  'master.sys.all_objects' is not supported in this version of SQL
  Server.

I can't see anything in the comments on the SP to suggest that Azure SQL Database isn't supported


texts: Foreign keys can't be added because they exist but they don't exist!

texts:
I have a conundrum. I am re-engineering a sqlserver 2005 database, and in the course of doing so have attempted to correct a simple lookup table flaw between tblUsers, tblUserRoles, and tblRoles (tblUserRoles had its own pk field).

In trying to make this change, foreign key relationships where dropped and recreated, except they weren't.

Whenever I try to:

ALTER TABLE [dbo].[tblUserRoles] ADD CONSTRAINT [Relationship26] FOREIGN KEY ([UserID]) REFERENCES [dbo].[tblUsers] ([UserID])
go
ALTER TABLE [dbo].[tblUserRoles] ADD CONSTRAINT [Relationship27] FOREIGN KEY ([RoleID]) REFERENCES [dbo].[tblRoles] ([RoleID])
go

I get:

Msg 547, Level 16, State 0, Line 230

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "Relationship26". The conflict occurred in database "AhelpDev", table "dbo.tblUsers", column 'UserID'.
Msg 547, Level 16, State 0, Line 232
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "Relationship27". The conflict occurred in database "AhelpDev", table "dbo.tblRoles", column 'RoleID'.

But when I:

ALTER TABLE [dbo].[tblUserRoles] Drop CONSTRAINT [Relationship26]
go
ALTER TABLE [dbo].[tblUserRoles] Drop CONSTRAINT [Relationship27]
go

I get:

Msg 3728, Level 16, State 1, Line 236
'Relationship26' is not a constraint.
Msg 3727, Level 16, State 0, Line 236
Could not drop constraint. See previous errors.
Msg 3728, Level 16, State 1, Line 238
'Relationship27' is not a constraint.
Msg 3727, Level 16, State 0, Line 238
Could not drop constraint. See previous errors.

When I run:

select * from  sys.Foreign_keys where name in ('relationship26', 'relationship27')

It returns no records. I'm stumped, I turn to you for help!

I've even attempted to copy the database to see if I could leave it behind...I can't!

I look forward to your answers.


texts: Optimized my.cnf for high-end and busy server

texts:
Currently my main culprit of poor performance is MySQL and I'm not DB savvy. So far, I've had little luck in optimizing MySQL configs on my server, which has a Sandy Bridge processor with 16GB RAM and SSD disks serving a Drupal 6 site running on top of Ubuntu. I have ~1300qps with 80/20 read/write ratio. MyISAM is the only engine that I use (My experience with InnoDB have been horrible, despite all buzz about it as being superior to MyISAM).
The original my.cnf shipped with MySQL 5.1 is abysmal and Debian's suggested configs for 'huge' servers seemed to be less than optimal for my case. After a lot of experimentations and tweaks I figured out that tuning-primer script is too generic to give sensible advice (if they are not outright misleading!). So regarding the plethora of parameters involved in an optimal MySQL config, it would be great if some DB-informed drupalers could share their my.cnf for similar Server/case.


texts:
I have a sql agent job setup for taking full backup every night. Last night the job failed with the following error message.

Sql Server Error Log Message

  BackupIoRequest::ReportIoError: write failure on backup device
  'H:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\My_Database_backup_2014_04_28_233008_2135500.bak'.
  Operating system error 2(failed to retrieve text for this error.
  Reason: 15100).

  Error: 3041, Severity: 16, State: 1.

SqlAgent Job History Log

  Executed as user: DomainName\sqlagent. Microsoft (R) SQL Server
  Execute Package Utility  Version 10.50.2500.0 for 64-bit  Copyright
  (C) Microsoft Corporation 2010. All rights reserved.    Started:
  23:30:01  Progress: 2014-04-28 23:30:06.81     Source:
  {123123123-F70A-430F-8041-12312312313}  Executing query "DECLARE @Guid
  UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End
  Progress  Error: 2014-04-28 23:30:16.19     Code: 0xC123123
  Source: Back Up Database (Full) Execute SQL Task     Description:
  Executing the query "BACKUP DATABASE [My_Database] TO  DISK =
  N'H:\MSS..." failed with the following error: "Write on
  "H:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MY_Database_backup_2014_04_28_233008_2135500.bak"
  failed: 2(failed to retrieve text for this error. Reason: 15100)
  BACKUP DATABASE is terminating abnormally.  10 percent processed.  20
  percent processed.  30 percent processed.  40 percent processed.  50
  percent processed.  60 percent processed.". Possible failure reasons:
  Problems with the query, "ResultSet" property not set correctly,
  parameters not set correctly, or connection not established correctly.
  End Error  DTExec: The package execution returned DTSER_FAILURE (1).
  Started:  23:30:01  Finished: 23:30:16  Elapsed:  14.844 seconds.  The
  package execution failed.  The step failed.

After doing the research on web mostly posts suggest I do not have enough space on the disks, I have checked the space on my disks I have have atleast 30 plus GB of free space on almost every drive, (disk for .mdf and disk for .ldf files). I have almost 15 gb of free space on my C drive. as One of the posts on web suggested not having enough space on C drive can also cause issues.

Has someelse ever faced the same issue, how you fixed it? what can be the reason for this error and how I can avoid it in the future. Any suggetions pointers in the right direction are most welcome. Thank you.


texts:
I have to update many records. I don't want to duplicate the entries.  The table concatenates the value to the end.  So in theory, I could add the value twice.

I want to put a check to make sure the entry is not already there; if it is, then skip the record.

update table1
set value=concat(value,',test_data')
where field1='abc';


texts: "tempdb metadata memory-optimized" configuration option

texts: Checking SQL Server 2019 CTP 2.5 (15.0.1500.28) I found this new configuration option and was wandering what is behind it. Couldn't find any information so far and was wondering if you know where to find more about it?


texts: ASM - How the asm search the disks and where is this parameter from?

texts:
I'm a begginer in Oracle ASM and when I will create a diskgroup the ASM shows me this parameter:

Where is this parameter from? I don't remember set any parameter with this name. I know that usually Oracle set this name as default when is not set a preferential parameter name, but in the creation process I don't saw this configuration.


texts: How to handle variations of the same data point?

texts:
I have a database with three tables:

books,
authors and
books_authors

books_authors is a “junction” table (i.e., it represents a conceptual level many-to-many relationship), so if it has a row with book_id=1 and author_id=5, it means that author 5 is an author of book 1.

Now, the problem is that author names are sometimes written differently depending on the book. For example, in book 1 the name of author 5 is written “洋子”, but in book 2 the name of author 5 is written “ようこ”.

Sometimes these are variations of the same name  (ようこ = 洋子) but sometimes they are completely different (e.g., an author changes his or her name after getting married.)

What's the best way to retain these name variations? Should I enter separate rows for the values “洋子” and “ようこ”, and then link them with another ID? Or should I make an array to keep all the variations in a single row?

Additional information

In response to some clarification requests made via comments, I am going to explain several significant aspects in order to contextualize the scenario more fully:

Authors can actually have six separate name values (First, First-reading, Last, Last-Reading, Full, Full-Reading) but the only one required is Full. The rest "can be" null.
An Author does not have a, say, "base name" in the business domain of interest.
I retain Author Names only when they are associated with a Book.
Authors can be Artists, Editors, etc. I use (what I call) the "junction" table to keep track of which Role they play for each Book.
I think that, usually, multiple Name Variations represent different values of the same domain, as in the case of “ようこ=洋子”, but I believe that sometimes they are different domains (e.g., as in the situation mentioned above, when an Author changes their Last Name after getting married).
I want to manipulate the corresponding tables via SELECT operations that, e.g., include the column(s) containing the Authors Names as (a) condition(s) in the WHERE clauses.
The full properties of interest regarding an Author are just the six Name (what I call) "fields", their Variants, and the Books with which they are associated.
The property whose values uniquely identify an Author instance is only the ID, as far as I can tell.

Note: I'm using Postgres but this question could apply to other relational database management systems.


texts: Excessive replication snapshot size causing failure

texts:
I am searching for an explanation of a replication snapshot being in excess of 5 times what we're predicting

Following a replication failure we are attempting to reinitialise subscriptions with a new snapshot. The size of the database is 135GB, one table within the database is 60GB - we have excluded this article from the publication. This meant we had an expectation of snapshot size being ~75GB.

We've had several attempts at creating the snapshot and these have failed due to lack of disk space. Last night the snapshot filled a disk with 340GB of free space.

I 'd welcome any explanation for this huge size of snapshot.

In my examination of possible causes I note that the snapshot format is set to "Character - Required if a publisher or subscriber is not running SQL Server". This despite the fact that at both ends of this transnational replication  is native SQL Server. Is there a different in size between formats?

Thanks in advance.

Native mode file Size:105 MB
C:>bcp IVM_ArchiveTest.dbo.Event out D:\NOBACKUP\UseOnce\EventNative.dat -T -n
218977 rows copied. Network packet size (bytes): 4096 Clock Time (ms.)
Total : 7878 Average : (27796.01 rows per sec.)

Character mode file Size:66 MB
C:\>bcp IVM_ArchiveTest.dbo.Event out D:\NOBACKUP\UseOnce\EventChar.dat -T -c
218977 rows copied. Network packet size (bytes): 4096 Clock Time (ms.)
Total : 1654 Average : (132392.38 rows per sec.)


texts:
I want to upgrade version 9.3 to 9.5 but after some steps I find that I upgraded to version 10, but I'm looking to go from 9.3 to 9.5.

When I type the pg_lsclusters command I find myself with three:

I used sudo pg_dropcluster 9.5 main --stop and sudo pg_dropcluster 10 main --stop to stop both, and sudo pg_upgradecluster 9.3 main command to set the 9.3 cluster to the latest version.

But I want to go to 9.5 instead of 10. The result after what I'm typing is:


texts: How do I demonstrate Transaction Log activity?

texts:
Say I have the following setup:

use tempdb
go

set nocount on
go

create table MyTest
(
    Column1 varchar(100),
    Column2 text
)
go

insert mytest (Column1, Column2)
select REPLICATE('a', 100), REPLICATE('a', 100)
from sys.syscolumns a, sys.syscolumns b

I'd like to convert each of the columns to varchar(max) like this:

-- processes every page:
alter table mytest
alter column Column1 varchar(max)

-- processes only metadata:
alter table mytest
alter column Column2 varchar(max)

How can I demonstrate that the first command processes the whole table while the second command only processes metadata. I was thinking of using SET STATISTICS IO which reports thousands of logical reads for the first command and nothing for the other. I was also thinking of using DBCC LOG or fn_dblog. But I wasn't sure how to interpret or tie the results to the queries I issued.


texts: Distributed AG on SQL Server 2017 Standard Edition?

texts: Can I configure Distributed AG with SQL Server 2017 Standard Edition?


texts: Trying to support unicode in MySQL database

texts:
I am doing a project in which I have to save tweets into my MySQL database.  The problem is some tweets contains smilies and other symbols which I am not able to save - it shows error like below:

  Incorrect string value: '\xF0\x9F\x98\x82. ...' for column 'text' at row 1

I was trying to convert to utf8mb4 using this link tutorial, and in step 5, I have changed my.ini file in my C:\ProgramData\MySQL\MySQL Server 5.6 directory. But when I do

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

in my MySQL console, I am not getting the desired result, meaning my character set is not changed to utf8mb4 - it is still utf8.

Please guide me how to resolve this, as I am new to this. I have attached an image to the step 5 (Modify connection, client, and server character sets) of the above tutorial.


texts:
I posted a question on how to create a relation between 2 tables based on a spatial field
https://stackoverflow.com/questions/63769885/t-sql-passing-column-to-subquery-for-filtering
The performance is terrible when the city has only 5k records and listing has 12k
(takes 54 seconds to run on sql server 2019, dual core i5)
Anything I can do to improve it? Execution plan is https://www.brentozar.com/pastetheplan/?id=BkiRk-74P
2nd Plan (based on the query in the answer by Kumar)
https://www.brentozar.com/pastetheplan/?id=HJSLs_L4D
This is the query
select l.*, city.*
from  listings l
cross apply (
     select  top (1)  c.UnicodeName, c.name, r.code as region, cn.code as country from cities c
     inner join regions r on r.regionid = c.regionid
     inner join Countries cn on cn.CountryId = r.countryid
     where c.location.STDistance(l.location) is not null
     order by c.Location.STDistance(l.location)
 ) as city

Indexes on cities and listings
CREATE SPATIAL INDEX [256_HHHH] ON [dbo].[Listings]
(
    [Location]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 256, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE SPATIAL INDEX [16_HHHH] ON [dbo].[Listings]
(
    [Location]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Updated Query based on Kumar's Suggestion, which does not bring back any results.  it seems this code is assuming that listing and city have the exact same position.  They dont, I just want to pick the nearest city for each listing.
I also had to change = to STEquals as there were syntax errors otherwise.
;With CTE as
(
select l.*, c.RegionId, c.UnicodeName, c.name,c.location.STDistance(l.location) Locationdistance
from  listings l
inner join  cities c on l.location.STEquals(c.Location)=1

)

select c.*,r.code as region, cn.code as country
from CTE C
inner join regions r on r.regionid = c.RegionId
     inner join Countries cn on cn.CountryId = r.countryid
     where Locationdistance is not null
order by Locationdistance


texts:
I have following objects:
(Rooms) which are (available in a certain period of time [in days]).
During the time of availability they can be (booked) by (users). There are basically these four tables. I know that there is a foreign key in (booked) for (users), I also have a foreign key for (rooms) in (booking) and I do have a foreign key of the (room) in (availability). But how can I connect my (availability)-table to avoid that the good is booked on a period of time it isn't available?

The refered question didn't lead to an answer in my matter.

To summarize:
(Rooms) - (availability) - (booking) - (User)

I have a hard time figuring out how I set the foreign keys for this transaction.


texts:
How to convert default instance to named instance in SQL Server 2012?

By mistake I have chosen default instance option and installed. Now I have to change to named instance. How to change?


texts:
have one problem with a replica set

There is an admin user with root permissions
It's fully working on config servers, but when I trying to auth with it on any RS - got:

2017-09-18T09:01:53.755-0400 E QUERY    Error: 18 Authentication failed.
at DB._authOrThrow (src/mongo/shell/db.js:1271:32)
at (auth):6:8
at (auth):7:2 at src/mongo/shell/db.js:1271
exception: login failed

User was added on config servers, after all Replica Set was deployed, maybe that is the problem?


texts: Selecting only overlapping elements from array of ranges

texts:
The table I need to search contains an array of numrange values illustrated by:

CREATE TABLE data ( sensor varchar(25), ranges numrange[] );
INSERT INTO data VALUES
  ('sensor0','{"[872985609.0,873017999.0]","[873021600.0,873035999.0]","[873039600.0,873072070.0]"}'::numrange[])
, ('sensor1','{"[872929250.000000,872985609.000000]"}'::numrange[]);

Searching for rows with elements in ranges that overlap a specified range is easy with ANY:

SELECT * FROM data WHERE '[873021700,873021800]'::numrange && ANY (ranges)

I would like a SELECT statement that returns fields from the table along with only elements from the array that overlap a specified range, aka:

SELECT sensor,[array of overlapping numranges] FROM data WHERE '[873021700,873021800]'::numrange && ANY (ranges)

resulting in:

sensor      ranges
sensor0     {"[873021600.0,873035999.0]"}

Is this possible without unnest()ing the array in a subselect?  If not, what would be an efficient way to unnest() when the arrays are potentially very large?


texts: Which domain to use for sql server developer

texts:
My current company does not have a QA domain in a hosting data enter where all the data bases are located . I want to know that in order to use developer editions of sql server, should I be using it in a domain which is different from production servers ? Or can I name the QA servers with a different naming convention and still use them in the same domain where prod is present? We have 3 data centers and in all the 3, the QA servers are in the same domain as prod . Is this ok ?

I noticed that my current client does have a QA.xx domain, but there are no servers hosted in it. Please advice which is the way to go to test newer configurations and setups because I want to take advantage of the developer editions of sql server


texts: Completion of ERD, uncertainties about generalization/specialization and primary keys

texts:
I'm practicing making conceptual databases and I would like to know of I'm on the right track. I've been trying to create an ERD for a scenario as follows:

A hotel can take bookings for weddings, conferences and theme nights, as well as regular regular rooms. All bookings have a date, time, booking code, name of staff member that took the booking, deposit paid and payment method.

Weddings bookings record the name of bride and groom, as well as client info (full name, address, phone number and email), who is making the booking and the date of the wedding reception. Number of guests, food menu (3 different alternatives), if the reception is in the evening, if it's open bar and if a photographer is needed. There is two available suites to hold the weddings, Plaza or Ballroom.

Conference bookings require the same client info, as well as name and address of the organisation that holds the conference. Number if participants and which food menu (4 different choices) is desired is also recorded. A conference have access to the same venues as weddings, but can book more than one. There is also 5 smaller conference rooms that can be booked. These can hold 15-20 people, have 2 different layouts and some contain electronic facilities and some have tea/coffee facilities.

Theme nights record the same client details, the number of participants and have access to one of the two venues.

Clients can also book individual rooms, either single or double sized room and most have en-suite facilities. The same client details are stored for this booking as well.

What I have managed to model so far is the one below, am I on the right track here?

I know I am missing cardinality, mainly because I haven't figured out any nice way to do it in draw.io which I'm using at the moment.

I feel rather unsure on how to use the generalization/specialization (the arrows), is this done in a good way?

EDIT: I´ve updated the ERD with Primary keys but I'm not very sure I'm taking the right approach with it...

EDIT 2: I have made some changes to the ERD with some consideration on the answers for this questions. I know some of the issues raised still are there, the one I mainly would consider is combining Meeting_Room and Suite but I'm not sure how to do it while keeping the constraint of the meeting rooms only being available to the conferences.

One thing that got misinterpreted was the Booker entity due to a mistake from my part. It didn't refer to the staff member taking the booking, but the person that did the booking.

  "Weddings bookings record the name of bride and groom, as well as client info (full name, address, phone number and email), who is taking making the booking and the date of the wedding reception."

I removed this entity and put it as a field referring to Person instead as the only relevant information about the person taking the booking is the name (for this scenario).


texts: Backup error: Failed to flush the commit table to disk

texts:
When I try to backup a SQL Server 2008 database, I receive an error:

Backup failed for Server 'SQL2008TEST'.  (Microsoft.SqlServer.SmoExtended)
Failed to flush the commit table to disk in dbid 32 due to error 2601.

Does anybody know the cause of this error?


texts: why is there an index range scan on max() query?

texts:
I have a table that logs the transmission of data to another system.
create table transfer_log (id number(10),
                           fk_data number(10),
                           first_sent_time date,
                           last_sent_time date,
                           transfer_status number(3),
                           error_message varchar2(250));

create unique index pk_transfer_log on transfer_log (id);
alter table transfer_log add constraint pk_transfer_log primary key (id);

create index idx_transfer_log_status on transfer_log (transfer_status, last_sent_time desc);

The transfer_status column stores the HTTP status from sending the data.
transfer_status is never null (initially 0), last_sent_time is initially null.
So I have a health check, to see when the last successful transmission was:
select
  max(d.last_sent_time)
from
  transfer_log d
where
  d.transfer_status = 200
;

and I hoped the index would make this query fast. But it's close to 1sec now and the execution plan doesn't look good:
----------------------------------------------------------------------------------------
| Id | Operation           | Name                    | Rows    | Bytes   | Cost | Time |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |                         |       1 |       6 | 2624 |      |
|  1 |   SORT AGGREGATE    |                         |       1 |       6 |      |      |
|  2 |    INDEX RANGE SCAN | IDX_TRANSFER_LOG_STATUS | 1272670 | 7636020 | 2624 |      |
----------------------------------------------------------------------------------------

I hoped the index (especially with the descending last_sent_time) would make this query fast as only the first entry needs to be read.
What did I do wrong?


texts: MySQL how to release memory used up by SELECT

texts:
I executed an INSERT IGNORE combined with SELECT from large table. The SELECT took up 16GB of memory and went into SWAP before I terminated it with CTRL+C.

INSERT IGNORE INTO sid(sid)
SELECT sid FROM data WHERE sid != "";

The data contains 64 million rows.

Data type of sid is VARCHAR(13). There were 28.2M records that matched the SELECT (I ran it afterwards). That results in around 350MB of data that needs to be SELECTed and INSERTed.

Is it possible to release memory allocated by MySQL? The query was terminated and there is no longer a need for that memory. Why is it still lingering?

Here are some config details:

max_allowed_packet=1024M

# Memory Table

max_heap_table_size=8192M

Everything else is default.

MySQL version 5.5.25, using MyISAM

Inserts into ndbcluster 7.2.7

NDB Config

SharedGlobalMemory=256M
DataMemory=2048M
IndexMemory=512M

My box runs 1/3 SQL nodes, management node. 2 other servers run data nodes.


texts: how to use is_srvrolemember to list all server roles of a user

texts:
For dealing with server permissions I am currently using
 a procedure called sp_SrvPermissions V6.0 by Kenneth Fisher thank you Kenneth, however,

while running a profile trace I often see the following line:

select is_srvrolemember('sysadmin') * 1 +
       is_srvrolemember('serveradmin') * 2 +
       is_srvrolemember('setupadmin') * 4 +
       is_srvrolemember('securityadmin') * 8 +
       is_srvrolemember('processadmin') * 16 +
       is_srvrolemember('dbcreator') * 32 +
       is_srvrolemember('diskadmin') * 64 +
       is_srvrolemember('bulkadmin') * 128

My question is:

How can I use the above script that uses is_srvrolemember to find out the server roles of mycompany\myuser?


texts: ETL, Production, Development -- Should these be different servers? Instances?

texts:
You can say I've become an "accidental DBA" as people have left this company and I'm managing many aspects of the BI system now suddenly.

My predecessor left quite the mess regardless but I'm trying to think of the best way forward in terms of hardware architecture and logical partitioning.

We have a SQL-Server Data Warehouse. We have ETL scripts (on the same server). We don't have a Test-Production split for database development.

My questions:

Should the ETL scripts (SSIS and Pentaho PDI) be located on the same server as the data warehouse?

I understand this is a complicated question. My understanding is this.

Pros for separate ETL server: Contrary to belief, SSIS and SQL Server don't play nicely via memory management and SQL Server plays best by itself. Scalability and optimization generally favor a separate ETL environment as ETL processes have entirely different needs (far fewer backup needs, far less storage needs).

Cons: Well, additional hardware costs. Additional SQL Server license costs in the case of SSIS (an additional one if separated) so for Standard edition it's an extra $900 and for enterprise and additional $13k or whatever. ALSO, our data warehouse is only used during the day (by developers) - ETL is only run overnight - there is little overlap. Only potentially backup processes may interfere. So maybe the 'competition' aspects are overblown and resource-pooling (for more CPU hardware, which our company IS FRUGAL when it comes to hardware) is advantageous.

Same question but for Test and Production environments.

What exactly is the point of an "instance" separation, as opposed to a database (one lower) or server (one higher) separation?

I guess they have wider configuration options, but -- well even servers can be running on the same physical hardware/ computer. What are the pros of separation Test/ Prod databases via 2 servers or just 2 instances? I take it with 2 instances you're sacrificing configuration for simplicity?


texts:
I am trying to understand what process is making my transaction log file to grow. For example, currently it is 120 GB and only 5-10 MB of it are in use.

Before:

FULL recovery mode
a backup of the transaction log file is made each 5 minutes
AlwaysOn Availability Groups is enabled
the transaction log file size is 128 MB (only 1-5 percent of it are in used)

After several days:

The transaction log file size is 120 GB (again only few MB of it are in use)

I am trying to detect what is causing this huge grow. One of the possible reasons explained here is The database is participating in an AlwaysOn availability group.

I have try to find this particular error:

  Error: 9002, Severity: 17, State: 9.  The transaction log for database
  '%.*ls' is full due to 'AVAILABILITY_REPLICA'

But I have only errors for the transaction lot of the tempdb:

The transaction log for database 'tempdb' is full due to 'active_transaction'

Is there any message or error which will help me to understand when or why this is happening?

Is there any other way to detect the event?

As far as I read, I can only use DBCC OPENTRAN but it will give me current transaction which is opened which is useless for me as I do not know when the event happened.


texts: How to share a unique value between tables efficiently [postgres]

texts:
We are modeling the concept of a global "reference code" for some of the records in different tables in our system (e.g., ABC123).  A "reference code" needs to be unique, easy to say over the phone, and can refer to a handful of different entities in our platform (e.g., people or places).
The criteria is:

one pool of codes that is unique across different tables of records
each record must have a code (not null)

How do I enforce uniqueness of this value across tables (either via a foreign key or just the value itself?)
CREATE TABLE reference_codes (
    code public.citext NOT NULL
);

CREATE UNIQUE INDEX index_reference_codes_on_code ON public.reference_codes USING btree (code);

CREATE TABLE people (
  id bigint NOT NULL,
  reference_code public.citext NOT NULL
)

CREATE TABLE places (
  id bigint NOT NULL,
  reference_code public.citext NOT NULL
)

-- now that I have two tables with foreign keys,
-- how do I ensure they do not point to the same reference_code row?

In this case, both a person and a place could end up referencing the same code (which we do not want).  Any suggestions?  My internet search kung fu isn't turning up enough on its own.
I thought about perhaps a custom constraint that would check each table that is foreign keyed to the reference_codes table to ensure uniqueness, but it seems cumbersome (especially when more and more tables are added).
Or perhaps a materialized view which combines all the codes across tables?  I am not sure what would be best.


texts:
I'm working on a "Event Management System" Online Software Project where the system requires the following:

Viewers can sign up for their user account
Events can be created by the admin from the admin panel
Each event requires that the student has a set of attributes
The required attribute for an event may or may not exist in the
user's details table
When the user wants to apply for participation in an event, the
system will prompt for the missing attributes
Once the user fills in the missing attributes, the system will
accept an application for the event, from the user.

To solve this one I created the following tables.

users (user_id [PK], username, password, user_level)
userdetails (user_id [FK], name, address, attr1, attr2, attr3, ... , attrN)
event (event_id [PK], event_start, event_end, event_title)
eventdetails (event_id [FK], ... )

How do I relate the eventdetails and the userdetails, such that the eventdetails

requires a tuple of n attributes from the userdetails table?

I was thinking something like:

The system will add n colums to the userdetails  field as soon as a new event is

created by the admin.

Am i thinking in the proper way?