texts: AWS Aurora with Postgres, ERROR: invalid locale name: "und-x-icu"

texts:
I got into picking collation for our new project and seems based on this document
https://www.postgresql.org/docs/10/collation.html
und-x-icu seems like a good choice because it is culture agnostic.
Whem I'm checking if und-x-icu is present with:
SELECT * FROM pg_collation where collname = 'und-x-icu' indeed returns desired collation, but when I run
CREATE DATABASE FooBar
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'und-x-icu'
    LC_CTYPE = 'und-x-icu'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    TEMPLATE=template0
    ;

I get error:
ERROR: invalid locale name: "und-x-icu"
I found this answer
Specify ICU collations as `ENCODING`, `LC_COLLATE`, and `LC_CTYPE` in Postgres 10
which says its not possible. It was 3 years and linked email list mentions it as "not-done-yet feature". I'm wondering if there is any development. From my experimentation seems like it's not possible. Can someone please confirm it? Is my understanding correct that UCI collation is only useful when used directly with every query?


texts: Is the idea of 'pages' and 'extents' unique to SQL Server or shared by other database management systems?

texts:
I know that a database management system like MySQL can make use of multiple storage engines - i.e. InnoDB, MyISAM, etc.

And reading into how SQL Server stores data (Understanding Pages and Extents), I can summarize (hopefully correctly):

SQL Server data storage consists of 'pages' (the smallest unit of data that SQL Server handles),
8 contiguous pages makes a structure called an 'Extent' (SQL Server accesses an Extent per read according to this article)
Extents can either be dedicated to a single object or shared by as many as 8 objects (a page can only belong to a single object). Uniform extents are preferred and mixed extents are refactored to be uniform when an object becomes large enough.
Objects include pretty much every everything in SQL Server (table, types, functions, indexes, etc. etc.)
An extent == 8 pages = 64KB == 64 000 bytes == 512 000 bits = 2 extents per MB
Log files do not use pages / extents

Questions:

Does SQL Server write to disk an extent at a time? (since an extent is the smallest 'read' unit)?
Are SQL Server metaphors such as 'pages', 'objects' and 'extents' applicable to other DB engines.
Alternatively, are pages and extents at a different abstraction level to talking about 'engines' such as InnoDB and MyISAM? If so, would these engines use something other than blocks of 8 * 8KB storage units (what I surmise pages and extents to be)?

Also, why is Google's conversion of KB to bytes different to MS? i.e. Google converts 8KB to 8000 bytes, MS converts 8KB to 8192 bytes.


texts: Does AT TIME ZONE automatically take care of DST conversion?

texts:
I have to convert UTC dates for some reports.  We are using SQL Server 2016 and all my report queries are SPs, so I was able to do this:

SELECT MyDateTime AT TIME ZONE 'Pacific Standard Time' AS 'MyLocalDateTime'

This returns the expected value, but I have not been able to determine if this will still work during daylight savings time.

By querying the time zone info DMV:

 select * from sys.time_zone_info

I see there is a column for 'Is DST', so I am assuming the server is tracking that.  So can I safely assume sql server will adjust the time as needed during DST?


texts: Unable to add records to query in Access 2007

texts:
I've found this excellent resource that covers why a query might be read-only. I have a query that is not read only, yet I'm unable to add new records to it.

There are three tables. Let's call them A, B, and C. Each has a unique primary key, but B and C also contain a foreign key that indexes into A. If building a query that does a inner join between A and B, the query is both editable and I can add new records to it. Similarly, a query that inner joins A and C is editable and supports adding new records. However, when doing inner join on A and B, then inner joining result with C, I can edit records, but not add new records. There is presumably something amiss with the "one-to-manyness" of this query, but I'm having a hard time seeing it, and also thinking through possible remedies.

With typical usage, tables A, B, and C should all have 1:1 relationships (though this is not enforced). Any ideas?

Here's the SQL syntax for the join

SELECT [Contact Information].[Record #],
        [Contact Information].[Last Name],
        [Contact Information].[First Name],
        [Study Information].[Study Group],
        [Study Information].[Study Status],
        [Clinical Information].[Contact Name],
        [Study Information].[Contact Name2]
FROM ([Contact Information]
        INNER JOIN [Study Information]
            ON [Contact Information].[Record #] = [Study Information].[Contact Name2])
        INNER JOIN [Clinical Information]
            ON [Contact Information].[Record #] = [Clinical Information].[Contact Name];

and here's a link to image that shows how Access is visually interpreting the query

Any ideas? Thanks!


texts: How can I copy the MySQL EXPLAIN result into a table?

texts:
I'd like to archive the results of a series of query EXPLAIN plans into a table for later analysis.

I created a table that contains all the fields from an EXPLAIN result, but I can't figure out how to populate it from executing an EXPLAIN command.

I figured out how to export a .sql file in MySQL Workbench that contains INSERT commands, but I have to edit this manually to make it work.  Is the EXPLAIN information stored somewhere so I could write queries to do it automatically?  I can't find it in either the information_schema or performance_schema.


texts: ROWNUM equivalent in Centura SQLBase (aka Gupta)

texts:
I have a Centura SQLBase (version 7, so it is over 10 years old; now apparently they changed the name back to Gupta) where I need to put a running number into a column while copying data into a temporary table.

The running number has to restart when a grouping code changes (so each group has its own counter), the data has to be ordered by a date column.

This is a conversion task. For testing (UAT) I only have to convert parts of the database (though it isn't really big, so I just want to convert all). For go-live, I will do a final conversion and the database will be disabled. So to be exact: it is a two-times task. Though not mission critical any outage is annoying for the users, as they have to switch to papers and then later key-in (instead of immediately key-in)

In essence I have this data:

employee_no,date,group_code

and I have to generate:

employee_no,date,group_code,group_running_number

The employee_no + date + group_code is the PK.

In Oracle, I would use an analytic function and get it done in one step, but in SQLBase I even cannot find something like "rownum".

I considered using the SYSDBSequence.nextval sequence, but it stayed the same for a single insert (which probably makes sense).

I considered using their ROWID (which contains a unique row number and I know how to pick that out), but I cannot use the ORDER BY in the INSERT-SELECT combo. (When creating views ORDER BY isn't allowed either)

One last resort, if I cannot solve this with SQL, would be using a spreadsheet. There are only about 400,000 rows in total, but it certainly would be rather slow and error prone. On the final conversion day I want to have as few steps as possible.

If I cannot find a rownum equivalent I might try writing a procedure in sqltalk doing RBAR processing.


texts: How to import Azure blob file into Sql Server 2008

texts:
Is there a way to import Blob files into sql server 2008 other than using CLR ?

The file size is around 300MB so CLR isn't efficient enough. Is there a possibility to use BULK INSERT or OPENROWSET in 2008 server?

Example in Sql Server 2017 we can do this

CREATE DATABASE SCOPED CREDENTIAL AzureDevBlobImportCred
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'st=2018-12-05T.....'

go

CREATE EXTERNAL DATA SOURCE AzureDevBlobImportExternalSrc
WITH  (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://myStorageAccount.blob.core.windows.net', -- storage account URL
    CREDENTIAL = AzureDevBlobImportCred -- External Data Source
);

then we can use the external data source in bulk import

BULK INSERT #Mytemptable
FROM 'filename'
WITH (DATA_SOURCE = 'AzureDevBlobImportExternalSrc'
);

So do we have something like above to import blob files in Sql Server 2008 ?


texts: Analyse differential backup to see why it is so big

texts:
We quickly have very big diff files. Although the database is a highly transactional database, there is also a lot of data at rest.
1 or 2 days after a full backup was taken, the diff backup is bigger than the full backup. (2 days there because the full backup failed one day , so I was able to witness a second day).
The current backup plan is: every day full, every 2 hour diff backup, and every minute log backup. All backups are in separate files.
Is there a way to analyse what is blowing up the diff backup?
As we take every minute a tran backup, it doesn't need so many restores if we have a diff backup every 2 hours. We took the advice of https://www.brentozar.com/archive/2014/02/back-transaction-logs-every-minute-yes-really/


texts:
I've found some exec(sql) statements buried in the code.  They're there for good reason, because these statements couldn't be written directly, however they are an obvious attack vector.

Is there a safe alternative to exec(some sql)?  Something that will be paremetrised correctly, including table names in statements?


texts:
When starting pgadmin3 I get the following error messages. This is the log file placed in my documents folder after starting:

2018-06-04 13:30:26 ERROR  :
ERROR:  function pg_last_xlog_receive_location() does not exist
LINE 1: ...E NULL END as inrecovery, CASE WHEN usesuper THEN
pg_last_xl...
^ HINT:  No function matches the given name and argument types. You might need
to add explicit type casts. 2018-06-04 13:30:34
ERROR  : Column not
found in pgSet: usecreatedb 2018-06-04 13:30:36
ERROR  : Column not
found in pgSet: usesuper 2018-06-04 13:30:37
ERROR  : Column not found
in pgSet: upsince 2018-06-04 13:30:37
ERROR  : Column not found in
pgSet: confloadedsince 2018-06-04 13:30:37
ERROR  : Column not found
in pgSet: inrecovery 2018-06-04 13:30:38
ERROR  : Column not found in
pgSet: replayloc 2018-06-04 13:30:39
ERROR  : Column not found in
pgSet: receiveloc 2018-06-04 13:30:39
ERROR  : Column not found in
pgSet: replay_timestamp 2018-06-04 13:30:40
ERROR  : Column not found
in pgSet: isreplaypaused 2018-06-04 13:30:41
ERROR  : Column not found
in pgSet: rolcatupdate 2018-06-04 13:30:41
ERROR  : Column not found
in pgSet: rolcatupdate 2018-06-04 13:30:42
ERROR  : Column not found
in pgSet: rolcatupdate 2018-06-04 13:30:43
ERROR  : Column not found
in pgSet: rolcatupdate 2018-06-04 13:30:44
ERROR  : Column not found
in pgSet: rolcatupdate 2018-06-04 13:30:45
ERROR  : Column not found
in pgSet: rolcatupdate

What do I have to do to avoid these errors in the future?
EDIT I am running on MacOS 10.13


texts: Oracle Enterprise Manager Database Console - default login and password?

texts:
I've just installed an Oracle 11g Standard Edition Database. It ran successfully and everything, however when going to Oracle Enterprise Manager Im not really sure what my "username" would be.

I entered a password during the installer, and I tried SYS and my password connecting as "Normal" and "SYSDBA". However, I'm not really sure what my username is meant to be. Is it just that I haven't set up a username/password for this yet?

Thanks


texts: Optimizing PostgreSQL where clause with OR

texts:
I have a query that uses an or in the where clause. It is slow. It seems that re-writing the query using a union could get the same results much quicker. But I'm in an environment where we generate SQL for different technologies. The or clause works well in Oracle, for example. I'd like to consider other options besides union that might be less radical if there are any good alternatives.

Here's the minimal setup to make the requirement clear.

create table my_table as
select
    'EXISTING_RECORDS' as s
  , generate_series(10001,30000) as id
  , chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) as name
  , chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) as other_name
;
insert into my_table
select
    'NEW_RECORDS' as s
  , generate_series(1001,3000) as id
  , chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) as name
  , chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) || chr( ascii('a') + trunc(random() * 26)::int ) as other_name
;

That create and insert gives us 20k EXISTING_RECORDS and 2k NEW_RECORDS. The name fields are 5 random characters, so we'll end up with a handful of matches when matching based on the following query.

select t1.s, t1.id, t1.name, t1.other_name, t2.name, t2.other_name, t2.s, t2.id
from
    my_table t1
  , my_table t2
where
  t1.s = 'NEW_RECORDS'
  and (
    t1.name = t2.other_name
    or
    t2.name = t1.other_name
  )
;

If I reduce the where clause to just t1.name = t2.other_name or the reverse, then it executes in about 7 ms in my environment. But with the or as shown it takes over 11500 ms.

I posted the explain plan in case that's helpful.

Is there any reasonable pattern I should consider in order to get a good execution plan besides converting this to two separate queries that I union together? For example: I'm happy to add indexes. But as far as I can tell, that doesn't provide any benefit.


texts: SQL Server dates - can I get date and hour?

texts: In SQL Server, can I do the equivalent of to_char(date_field, 'YYYY-MM-DD:HH24') in Oracle to get 2012-01-30:23?


texts: Updating rows conditionally with a gapless ascending value

texts:
A user has a table they would like to update.  Depending on the data in other columns they want a particular column to be updated with an ascending value starting from 1 that is gap-less.  The rows that will not contain this ascending value also need to be updated.  Is there a way to do this with a single UPDATE statement?

Create sample data:

CREATE TABLE t1 AS (
   SELECT CHR(ASCII('a')+rownum-1) letter, rownum Number1, 0 Number2
   FROM dual connect by rownum<=7);

Update statement that assigns an ascending rownum, but it isn't gap-less:

UPDATE t1 SET Letter=’x’,
   Number2 = DECODE(letter,’a’,rownum,’d’,rownum,’e’,rownum,NULL);

Using a sequence returns the same results:

CREATE SEQUENCE s1;
UPDATE t1 SET letter=’x’,
   number2 = DECODE(letter,’a’,s1.nextval,’d’, s1.nextval,’e’, s1.nextval,NULL);

The data should look like this when complete:

L    NUMBER1    NUMBER2
- ---------- ----------
x          1          1
x          2
x          3
x          4          2
x          5          3
x          6
x          7

I'm not looking for a trick applicable only to my specific test case, but a general solution to the problem.


texts: Dependency Preservation and Normal Forms

texts:
Suppose a set of functional dependencies F holds over a relation R . We find that the dependencies violate the conditions of 2NF and decompose the relation into R1 and R2. After the process what can i say about the set R. In other words are all the dependencies preserved if not how to we find the new set of dependencies.
Do we need to specify the dependency set for each of the decomposed relation?

As an example let us consider the set

R = {a, b, c, d, e, f, g , h , i , j}

F = {AB -> C, A -> DE, B -> F, F -> GH, D -> IJ }

Computing the closure we find AB to be a candidate key of R
this leads to the violation of 2NF by the dependency A -> DE We compute A+ = {A , D , E , I , J }

the Decomposition therefore is R1 = {B , C , F , G ,H}  R2 = { A , D , E , I , J}

After this step i am unsure how to check the new relations for violations , which set of dependencies do i take as a reference for checking for violations.

The way we have constructed R2 ensures no violation of 2NF  but what about the Relation R1?


texts:
As per MySql documentation link:

https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Handler_read_key

  Handler_read_key - The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.

Can someone please explain / simplify / elaborate this definition.


texts: How do I do an UPSERT in SQL Server, returning the pre-updated values?

texts:
All of the syntax is valid PostgreSQL as an example of an UPSERT that returns the old and new values for the field. Let's say I have a table foo with (1,A)...(5,E).

CREATE TEMP TABLE foo
AS
  SELECT id, chr(id+64)
  FROM generate_series(1,5) AS t(id);
CREATE UNIQUE INDEX ON foo(id);

 id | chr
----+-----
 1  | A
 2  | B
 3  | C
 4  | D
 5  | E
(5 rows)

Now, let's say I want to UPSERT 6 rows. A few colliding, a few new rows.

SELECT id, chr(id+74)
FROM generate_series(3,8) AS t(id);
 id | chr
----+-----
  3 | M
  4 | N
  5 | O
  6 | P
  7 | Q
  8 | R

How would I get,

id
The old value of chr
The new value of chr

In PostgreSQL, I would do..

WITH t1 AS (
  SELECT id, foo.chr AS oldchr, chr(id+74)
  FROM generate_series(3,8) AS t(id)
  LEFT OUTER JOIN foo USING (id)
),
tupdate AS (
  UPDATE foo
    SET chr = t1.chr
  FROM t1
  WHERE foo.id = t1.id
  RETURNING foo.id, t1.chr, t1.oldchr
),
tinsert AS (
  INSERT INTO foo (id, chr)
  SELECT id, chr
  FROM t1
  WHERE t1.oldchr IS NULL
  RETURNING id, chr, null::text
)
SELECT * FROM tupdate
UNION ALL
SELECT * FROM tinsert;

 id | chr | oldchr
----+-----+--------
  3 | M   | C
  4 | N   | D
  5 | O   | E
  6 | P   |
  7 | Q   |
  8 | R   |
(6 rows)

That table above is returned by those CTEs. And, that's the output I'm looking for.


texts:
Query for insert:

LOCK TABLE tbl WRITE;
ALTER TABLE tbl DISABLE KEYS;
load data local infile "E:/One.txt" into table tbl fields terminated by "\t" lines terminated by "\n" (user_id, sexid, cityid);
ALTER TABLE tbl ENABLE KEYS;
UNLOCK TABLES;

First 5 million inserted per 2 min, second 3 million per 20 min, after 200k per min.
Why decline perfomance?
How to recover insertion speed?
~200 million of records.
Type table InnoDB


texts: Postgres - copy table INCLUDING CONSTRAINT doesn't include constraint

texts:
How come that statement don't copy constraints if I explicitly ask for it?

CREATE TABLE backup_schema.permissions (LIKE public.permissions INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS);
INSERT INTO backup_schema.permissions
    SELECT *
    FROM public.permissions
    WHERE community_id=123;

verifying with pgadmin3 I discovered that constraints on the new table(eg.:backup_schema) was:

but in the original table in public schema was like this:


texts: SQL Server 2016: sp_send_dbmail, Failed to initialize sqlcmd library with error number -2147467259

texts:
I have a quite frustrating problem and I am not sure I have done this correctly.
I don't have a lot of experience with T-SQL but here is what I am trying to do:

IF a query returns values THEN send an email, otherwise do nothing.
I am going to schedule the query to be run as a job every 5 minutes or so to check if the query has returned results (and if so send an email as per the script).

Below is the script which is work in progress and taken from here:

https://serverfault.com/questions/165597/send-sql-server-job-alert-only-when-a-query-has-rows-to-return

So I understand that the script won't do exactly what I want but since I get stuck when trying to run it I want to solve that before modifying it further.

create procedure [dbo].[sp_alert_nav_jobqueue_test]
as

Begin

declare @alerttriggered AS nvarchar(max);

set @alerttriggered = 'SELECT "ID", "Description", "Start Date_Time", "Status", "E-Mail Sent", "Job Queue Category Code"
FROM [MyDatabaseTEST].[dbo].[TEST My Company $Job Queue Log Entry]
WHERE cast("Start Date_Time" as date) = cast(getdate() as date) AND "Status" != 0 AND "E-Mail Sent" >0'

IF (@alerttriggered IS NOT NULL)
begin

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Primary SMTP Profile',
    @recipients = 'randomemail@address.com',
    @query = 'SELECT "ID", "Description", "Start Date_Time", "Status", "E-Mail Sent", "Job Queue Category Code"
FROM [MyDatabaseTEST].[dbo].[TEST My Company $Job Queue Log Entry]
WHERE cast("Start Date_Time" as date) = cast(getdate() as date) AND "Status" != 0 AND "E-Mail Sent" >0' ,
      @subject = 'Alert from TEST NAV Job Queue Entry ',
       @Body = 'Asdf' ,
    @attach_query_result_as_file = 0 ;

End
else
begin

      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Primary SMTP Profile',
       @recipients = 'randomemail@address.com',
            @BODY = 'No data returned ',
            @subject = 'Merchant Email'

End
End;

Now if I try and execute it via:

EXEC [dbo].[sp_alert_nav_jobqueue_test]
GO

I get this error:

Msg 22050, Level 16, State 1, Line 0
Failed to initialize sqlcmd library with error number -2147467259.

Now I am trying to execute it via my user and I've created an AD-group which I am a member of under the "msdb"-datbase and "Security" > "Users" which have "DatabaseMailUserRole" and "db_owner" under "Membership" and also have "Alter","Control" and "Execute" for the stored procedure I created.

When I am running SQL Profiler to further troubleshoot I see this:

exec sp_executesql N'INSERT sysmail_query_transfer(uid, text_data) VALUES((@P1), (@P2))',N'@P1 nchar(72),@P2 ntext',N'29E86B54-AC35-4944-B700-EBD9BED57A22                                    ',N'Msg 241, Level 16, State 1, Server MyServer, Line 1
Conversion failed when converting date and/or time from character string.
'

If I comment out the "WHERE"-clause, modify the "SELECT"-statement to "TOP 1" and set "@attach_query_result_as_file = 0" to "1" I can successfully send an email. This leads me to believe that there is something wrong with my "WHERE"-clause but what?

Guessing it is that the value that gets returned from the "WHERE"-clause is not of a "varchar" type?

Really appreciate any and all help


texts: Auto increment value needs to be changed inside trigger

texts:
I have a slight problem with a table that I have converted which used to be MyISAM, I have now made it a InnoDB table.

Original structure:

id varchar(15),
seqno int(11) auto increment
Joint PK index on the above 2 fields

New structure:

auto_id INT (11) auto increment,
id varchar(15),
seqno int(11)

The original structure had a joint PK on id and seqno with seqno being auto incremented for each unique id value. It would then return the seqno as the last_insert_id in my web application.

With the new innodb structure, because innodb doesnt support joint primary keys, I've had to add in an auto_id which now has the auto increment.

I then implemented a trigger to generate the seqno unique per id value. My question is how do I set the auto increment id so that it returns in place of the auto_id value? This trigger would be called when I insert into my_table, so that it can calculate the next seqno value to insert with.

DROP TRIGGER IF EXISTS `innodb_seqno_trigger`;
//

CREATE TRIGGER `innodb_seqno_trigger` BEFORE INSERT ON `my_table`
 FOR EACH ROW BEGIN

SET @newseqno = 0;

SELECT MAX(seqno) INTO @newseqno
FROM my_table
WHERE id = NEW.id;

SET NEW.seqno = (@newseqno + 1);
/* HERE I NEED HELP PLEASE TO GET THE AUTO INCREMENT VALUE TO ACTUALLY RETURN NEW.seqno */
END;
//

Thanks Peter


texts: Best way to Design Key,value pairs in relational database?

texts:
I have a table which has a list of all attributes that any object can have:

attr_id    attr_name    attr_data_type
------      -----        --------------

I have a table [object_key] in which each object_key_id has an unique list i.e set of attr_id,attr_value pairs.

object_key_id   attr_id   attr_value
---------       -------   ----------
1               1          'value1'
1               2          'value2'
2               1          'value3'
2               2          'value4'
3               1          'value1'
3               2          'value2'
3               3          'value3'

In this case, what is the best way to get the object_key_id for a given set of attr_id,attr_value pairs.

Ex:  Input: [{1, 'value1'}, {2, 'value2'}]
     Output: 1

What's the best way to build a unique index i.e to have unique object_key_id for a set of attr_id,attr_value pairs?


texts: Oracle Query execution finishes first time but hangs up from second run

texts:
We have a very complex view that finishes execution only on first time, but hungs up when run from the same session second time.

complex view - view with nested views, lots of inner and outer joins, multiple union all etc... But no locking statements, only pure read.

$ sqlplus sys/system as sysdba;
SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0  Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> alter session set current_schema = DB_2017_2018;

SQL> alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD';

SQL> select 1 from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- runs in 33 sec

SQL> select 1 from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- hangs up.. Ctrl + C after 3 minutes

SQL> select * from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- runs in 1m 30 sec

SQL> select * from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- hangs up.. Ctrl + C after 5 - 10 minutes

-- ALL THESE COMMANDS / QUERIES RUN IN SAME SESSION

I figured that running the exact same query second time is when the problem occurs. If I simply change any select column or where condition, then it runs for first time and hangs up from second time (see the above commands).

Suspect 1

One thing I suspect from this is may be it's related to oracle's cache. So when I run the following commands before each query execution, I didn't get any query hang up.

SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;

Suspect 2 (root cause might be Suspect 1)

Once any query hangs up, even if I run the same query in another session, it still hangs up. So If I kill the active sessions that's running the hang up query, then I can run the query successfully only for first time but stuck with same problem from second run.

SQL> ALTER SYSTEM KILL SESSION '12,256' IMMEDIATE;

AFAIK, only thing I can be sure is that something is badly wrong with oracle's buffer_cache and shared_pool in our version.

This problem doesn't happen for tables or simple views, only for this complex view (didn't tested other complex views). Also we were facing this kind of strange issue only in last 2 - 3 weeks. It's hosted in cloud (AWS EC2), so it may be related to spectre / meltdown patches by Amazon as that's what changed in last 2 - 3 weeks (unrelated ?!). Or might be due to some db data threshold level reached in that period.

Ready to provide any other analysis or metrics


texts: Alternative to SQL Server Management Studio and Azure Data Studio to initially change my password

texts:
Background

I'm having to connect to a customer's Microsoft SQL Server. I generally don't need to do much in databases besides your average SQL, so using the database features in IntelliJ IDEA have so far been more than sufficient for various PostgreSQL and Orcacle databases. In this case though I was given a heads-up that I will have to change my password first, and when I use IDEA to connect with the credentials I received it does indeed reject them and gives me an error message saying I need to change my password. I was also told to use SQL Server Management Studio (SSMS) to do so.

Problem

SSMS is only for Windows, I'm running a Mac. The SSMS download page refers to Azure Data Studio for use on a different OS. An initial try of the latter did not let me change the password though. Even if I eventually find a way to do it, is there really no other easier way to do this that doesn't involve installing another full-blown db tool that I have no intention of using in the long run? Or worst case: buy a windows computer for the sole purpose of installing SSMS so I can change a password? Surely there must be a bunch of Mac- or Linux-using DBA's that know of a simple command-line tool or alike that allows them to change their password initially on a Microsoft SQL Server, after which they just use their favorite db management tool to access it?

Edit: Note on "duplicate"

I disagree that this is a duplicate of Handle expired SQL login's passwords in SQL Server because:

That question involves an expired password, while I was never able to log in in the first place, nor able to run any sort of SQL prior to logging in
That question involves someone already using SSMS, whereas I've specifically pointed out I cannot use it and I'm asking for an alternative

Additional clarification

When I said customer, I meant a client that has data that I need to pull. I do not have any sort of admin access to the VM or SQL Server beyond the credentials I was provided
The error I'm getting is not a notification while I'm logged in. It's an error preventing me from even connecting, so I cannot run any other SQL or commands whatsoever


texts: Only join to latest entry in subselect in a given range determined by the main select

texts:
I have one table Haul and one table ChargeCarrier_Storage. The latter contains movements of charge carriers between storage locations. Haul has an arrival and a departure time. I want to join together these two tables to get a list of all charge carriers which have been loaded onto a haul (=trailer).

My select works fine in most cases. There are some rare cases, though, when a worker books a piece onto the trailer, then back into the storage and then back into the trailer again (in between that time span between arrival and departure). In such a case, the same charge carrier will appear multiple times in the list with all data identical except for the StoredOn date.

This is not wrong per se, but the view we're talking about is used to create loading lists. And on there, only the latest entry should appear. So if you load it on, off and on again, it should be in the list once. If you load it on and off again, it should not be in the list at all.

Here's my current statement:

SELECT
    h.ID AS ID_Haul,
    h.DescriptiveID,
    ROW_NUMBER() OVER(PARTITION BY h.ID ORDER BY ccs.StoredOn ASC) AS Pos,
    cc.PickingNo,
    pin.PickingStorage,
    pin.TargetCountry,
    cc.[ObjID],
    cc.Customer,
    cc.Article,
    cc.Quantity,
    cc.QuantityUoM,
    cc.PackageLength,
    cc.PackageWidth,
    cc.PackageHeight,
    cc.PackageWeight,
    ccs.StoredOn,
    CAST(CASE WHEN h.Depature IS NULL THEN 0 ELSE 1 END AS BIT) AS Departured,
    ROW_NUMBER() OVER (PARTITION BY ccs.ID_ChargeCarrier ORDER BY ccs.StoredOn DESC) AS ORD -- TEST
FROM dbo.Haul h
INNER JOIN dbo.ChargeCarrier_Storage ccs ON
    ccs.ID_Storage = h.ID_Storage
    AND (
        h.Depature IS NULL AND ccs.StoredOn >= h.Arrival
        OR ccs.StoredOn BETWEEN h.Arrival AND h.Depature
    )
INNER JOIN dbo.ChargeCarrier cc ON cc.ID = ccs.ID_ChargeCarrier
INNER JOIN dbo.PickingInfo pin ON pin.ID_ChargeCarrier = ccs.ID_ChargeCarrier
WHERE
    h.ID = 1167
order by PickingNo

How can I make it work properly? Right now, it displays the same charge carrier multiple times, and I also don't know how it responds, when you unload the piece again.

The tables look like this:

CREATE TABLE dbo.Haul
(
    ID INT NOT NULL PRIMARY KEY IDENTITY,
    ID_Trailer INT NOT NULL,
    ID_LoadingBay INT NOT NULL,
    ID_Storage INT NOT NULL,
    ID_DestinationAddress INT NOT NULL DEFAULT 4,
    DescriptiveID CHAR(18) NULL,
    Arrival DATETIME2(2) NOT NULL DEFAULT SYSDATETIME(),
    Depature DATETIME2(2) NULL,

    CONSTRAINT FK_Haul_to_Trailer FOREIGN KEY (ID_Trailer) REFERENCES Trailer(ID),
    CONSTRAINT FK_Haul_to_LoadingBay FOREIGN KEY (ID_LoadingBay) REFERENCES LoadingBay(ID),
    CONSTRAINT FK_Haul_to_Storage FOREIGN KEY (ID_Storage) REFERENCES Storage(ID),
    CONSTRAINT FK_Haul_to_DestinationAddress FOREIGN KEY (ID_DestinationAddress) REFERENCES [Address](ID)
)
GO

CREATE TABLE dbo.ChargeCarrier_Storage
(
    ID_ChargeCarrier INT NOT NULL,
    ID_Storage INT NOT NULL,
    ID_User INT NULL,
    StoredOn DATETIME2(2) NOT NULL DEFAULT GetDate(),

    CONSTRAINT PK_ChargeCarrier_Storage PRIMARY KEY (ID_ChargeCarrier, ID_Storage, StoredOn),
    CONSTRAINT FK_ChargeCarrier_Storage_to_Storage FOREIGN KEY (ID_Storage) REFERENCES dbo.Storage(ID),
    CONSTRAINT FK_ChargeCarrier_Storage_to_ChargeCarrier FOREIGN KEY (ID_ChargeCarrier) REFERENCES dbo.ChargeCarrier(ID) ON DELETE CASCADE,
    CONSTRAINT FK_ChargeCarrier_Storage_to_User FOREIGN KEY (ID_User) REFERENCES dbo.[User](ID)
)
GO


texts: SQL Server DB becomes unusable overnight

texts:
Yesterday, my SQL Server database was fine. Today it is almost unusable - it's slowed down by a factor of between five to twenty, depending on when I hit it.

Some data was added to the server in an overnight load process, but nothing like a volume that should impact a database that much. About 50,000 plain text records (no XML or other frippery).

The server was patched this morning before we rebooted it. However, none of our other database servers which also got patched are behaving differently.

Resource Monitor would seem to suggest that its disk IO that's at fault. It's running at close 100% of capacity on the .mdf file the whole time, even when there's not much actually happening in the database. Access to Templog.ldf is also running quite high.

No-one here is an expert DBA (we're all developers with varying amount of SQL skills) and we're all baffled by what's happened. We've tried running sp_updatestats and moving some of the big indexes to different discs, to no avail.

I think this must have something to do with the patch - it seems too much of a co-incidence. A colleague is convinced that it's the data load having caused the size of the mdf to increase to a point where it's caused execution plans to become inefficient.

What on earth has caused this? How can we find out, and what can we do to fix it?

EDIT:

Using sp_WhoIsActive reveals nothing out of the ordinary. It registers my own use of the sproc and some commands from a colleague who is currently trying to move another index. That's probably holding up the DB right now but it was running just as badly before.

It's the Standard version of SQL Server 2008 R2. SELECT @@VERSION gives:

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64)
  Jul  9 2014 16:04:25
  Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

The server has 72GB of RAM and three quad-core 2GHz processors.

The patching was only applied to Windows. There were no changes other than the patch.

Selected settings:

_id     name                        value   minimum     maximum     value_in_use    description                                 is_dynamic  is_advanced
1540    min memory per query (KB)   1024    512         2147483647  1024            minimum memory per query (kBytes)           1           1
1541    query wait (s)              -1      -1          2147483647  -1              maximum time to wait for query memory (s)   1           1
1543    min server memory (MB)      0       0           2147483647  16              Minimum size of server memory (MB)          1           1
1544    max server memory (MB)      65536   16          2147483647  65536           Maximum size of server memory (MB)          1           1

UPDATE: Shifting indexes and tables to different disk partitions seems to be improving things. I'm still confused over how we could have reached a tipping point so suddenly with such drastic results.


texts:
I have two PostgreSQL servers running on my local Ubuntu machine (port 5437 is master, port 5438 is standby).

While following the tutorial on
http://wiki.postgresql.org/wiki/Hot_Standby

I get this in my log2:

LOG:  database system was interrupted; last known up at 2013-02-23 20:48:03 PST
LOG:  creating missing WAL directory "pg_xlog/archive_status"
LOG:  entering standby mode
LOG:  restored log file "000000010000000000000002" from archive
LOG:  redo starts at 0/2000020
LOG:  consistent recovery state reached at 0/20000C4
LOG:  database system is ready to accept read only connections
cp: cannot stat `/home/ga/p1/000000010000000000000003': No such file or directory
cp: cannot stat `/home/ga/p1/000000010000000000000003': No such file or directory

and the last line repeats indefinitely ...

The file is missing. With WAL, when would the new file be created?


texts: Ola's Maintenance Solution Plan script newer version

texts:
We have very old version of Ola's Maintenance Solution Plan script implemented in our many servers and wondering if i have to update to his latest scriptm how can i upgrade easily on all my servers?

Thank you!


texts: MySQL InnoDB Reset Existing Auto-Increment Records

texts:
Using MySQL Workbench, I have a MySQL InnoDB with a few hundred records.

The records table has the following format (id is auto_increment and unique);

+------+-------+
| id   | name  |
+------+-------+
| 2500 | jo    |
+------+-------+
| 2511 | jim   |
+------+-------+
| 2512 | sarah |
+------+-------+
| 2513 | jane  |
+------+-------+

Notice my first record begins at 2500. Presumably this is because I have dropped and re-imported the table data numerous times.

Is there any way I can 'reset' the id field so that the records begin at 1 and not 2500?

I want the table to look like this:

+----+-------+
| id | name  |
+----+-------+
| 1  | jo    |
+----+-------+
| 2  | jim   |
+----+-------+
| 3  | sarah |
+----+-------+
| 4  | jane  |
+----+-------+

Any advice is appreciated.


texts: Single row update using primary key affects many rows

texts:
I'm doing a very simple datetime update in a table using its primary key:

update TABLE
set END_DATE = '2015-10-14 00:00:00'
where ID = 1165027;

I received the message (1 row(s) affected) 183 times!

When doing a select only one row is returned:

select * from TABLE where ID = 1165027;

What am I missing?


texts:
Can't create temporary files on /var/tmp.

mysqldump: Error: 'Can't create/write to file '/var/tmp/mysql.k6NKGk/#sql_5dc_2.MAI' (Errcode: 2)' when trying to dump tablespaces

On my OS.

 id mysql
uid=60(mysql) gid=105(mysql) groups=105(mysql)

ls -l /var/
drwxrwxrwt  2 root root  72 oct 24 16:43 tmp

What can I do?


texts: Copying (hundreds of) tables from one server to another (with SSMS)

texts:
I have several hundred (currently 466, but ever growing) tables I have to copy from one server to another.

I have never had to do this before, so I'm not sure at all on how to approach it. All the tables are in the same format: Cart<Eight character customer number>

This is part of a larger project of which I am merging all these Cart<Number> tables to one Carts table, but that's a whole different question altogether.

Does anyone have a best-practice method I can use to copy all these tables over? The database names on both servers are the same, if that helps. And as I said earlier, I have the sa account so I can do whatever is necessary to get the data from A to B. Both servers are in the same server farm, as well.


texts: How do you reset the master key in sql server 2005?

texts:
Our old DB admin is gone, any ideas?

It looks it was set up many years ago.


texts: Not able to Create Database or Restore Database on the Azure SQL Managed Instance

texts:
I am trying to restore database on the Azure SQL Managed Instance using Azure blob container URL.

Reference

CREATE CREDENTIAL [https://zzdevbackup.blob.core.windows.net/dbbackup]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE'
, SECRET = 'sv=2017-11-09&ss=b&srt=sco&sp=rwdlacg=XzzcvDrhcmdldnfQ9Opu4M4Kqq1MT3tsxOADiRCQBf%2BXo%3D'

RESTORE FILELISTONLY FROM URL =
   'https://zzdevbackup.blob.core.windows.net/dbbackup/Azure_Data_Sync_Test.bak'

RESTORE DATABASE [Azure_Data_Sync_Test] FROM URL = 'https://zzdevbackup.blob.core.windows.net/dbbackup/Azure_Data_Sync_Test.bak'

I am getting the below error

  Msg 3201, Level 16, State 2, Line 7
  Cannot open backup device ‘https://ZZZdevbackup.blob.core.windows.net/dbbackup/Azure_Data_Test.bak’. Operating system error 2(The system cannot find the file specified.).

Please help me to figure out this issue.


texts:
I have the following query:

SELECT substr("Timestamp", 1, 16) "Timestamp", "Action", count(*) "Count"
FROM "MyTable"
WHERE substr("Timestamp", 1, 13) <= substr(CURRENT_DATE, 1, 13)
GROUP BY substr("Timestamp", 1, 16), "Action"
ORDER BY substr("Timestamp", 1, 16) DESC, "Action"

I want to do paging on the group, so that I e.g. get with page 1 and page-size 2 the first 2 groups with the corresponding elements.

I set up a fiddle for a better understanding. Please see: http://rextester.com/GLY48637

How can I get this paging working?


texts: table_open_cache won't increase above 431 - MariaDB 5.5

texts:
Moved a few sites to a new server running CentOS 7, Plesk Onyx, 32gb, 2x480gb SSD, Intel(R) Xeon(R) CPU D-1521 @ 2.40GHz.

[root@server ~]# mysql -V
mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Trying to tune MySQL, but am stuck on the table_open_cache not going above 431.

| Open_tables                              | 431         |

My /etc/my.cnf.d/server.cnf as follows:

skip-name-resolve               = 1

innodb_file_per_table           = 1
innodb_buffer_pool_size         = 16G
innodb_buffer_pool_instances    = 16
innodb_log_file_size            = 2G

tmp_table_size                  = 256M
max_heap_table_size             = 256M

thread_cache_size               = 4
table_open_cache                = 2000

query_cache_type                = 0
query_cache_size                = 12M

Already checked the OS limit:

[root@server /]# ulimit -n
4096

Not sure where to look next.  Any ideas or help would be awesome.


texts: MySQL replication: 'Duplicated entry for PRIMARY key'

texts:
Could you please help me to understand why I'm receiving 'Duplicated entry for PRIMARY key' on a slave server after a full re-sync.

Basically 'mysqldump' was running almost whole night and then the restore process took a couple of hours so when I've started the slave it was ~63874 seconds behind master.

The slave server is read only (read_only) and there was no any writes during re-sync process so I don't understand why there are duplicated keys.

Binary log format is set to MIXED on master.

Command used to backup DB:

mysqldump --opt --single-transaction -Q --master-data=2 db | bzip2 -cz > db.sql.bz2

The slave is replicating only one database from master (db -> db_backup) with the following options:

replicate-wild-do-table = db_backup.%
replicate-rewrite-db = db->db_backup


texts: Designing Simple Schema for Disaggregation of Demand Forecast

texts:
I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case:

I have a parent-child hierarchy of products (example, Raw Material > Work in Progress > End Product).

Orders are placed at each level.
Number of orders shall be viewable in weekly buckets for the next 6 months.
Demand forecast can be done for each product level.
Demand forecast for any week within next 6 months can be done today.
Demand forecast is done for weekly buckets, for the next 6 months.

Demand Forecast is usually done at the higher level in hierarchy (Raw Material or Work in Progress level) It has to be disaggregated to a lower level (End Product).

There are 2 ways in which demand forecast can be disaggregated from a higher level to lower level:

User specifies percentage distribution for end product. Say, there's a forecast of 1000 for Work In Progress.. and user says I want 40% for End Product 1 and 60% for End Product 2 in bucket 10.. Then for 10th week (Sunday to Saturday) from now, forecast value for End Product 1 would be 400 and, for End Product 2 would be 600.
User says, just disaggregate according to orders placed against end products in Bucket 5, and orders in bucket 5 for End Product 1 and 2 are 200 and 800 respectively, then forecast value for EP1 would be ((200/1000) * 100)% and for EP2 would be ((800/1000) * 100)% of forecast for 'Work in Progress'.

Forecast shall be viewable in weekly buckets for the next 6 months and the ideal format should be:

product name | bucket number | week start date | week end date | forecast value | created_on

PRODUCT_HIERARCHY table could look like this:

id  |   name                |   parent_id
__________________________________________
1   |   raw material        |   (null)
2   |   work in progress    |   1
3   |   end product 1       |   2
4   |   end product 2       |   2

ORDERS table might look like this:

id | prod_id | order_date | delivery_date | delivered_date

where,

prod_id is foreign key that references id of PRODUCT_HIERARCHY table,

How to store forecast?
What would be a good basic schema for such a requirement?

My idea to select orders for 26 weekly buckets is:

SELECT
    COUNT(*) TOTAL_ORDERS,
    WIDTH_BUCKET(
        delivery_date,
        SYSDATE,
        ADD_MONTHS(sysdate, 6),
        TO_NUMBER( TO_CHAR(SYSDATE,'DD-MON-YYYY') - TO_CHAR(ADD_MONTHS(sysdate, 6),'DD-MON-YYYY') ) / 7
    ) BUCKET_NO
FROM
    orders_table
WHERE
    delivery_date BETWEEN SYSDATE AND ADD_MONTHS(sysdate, 6);

But this will give weekly buckets starting from today irrespective of the day. How can I convert them to Sunday to Saturday weeks in Oracle?

Please help designing this database structure.

(will be using Oracle 11g)


texts:
When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table?

One reason that I would prefer to use views is that the database schema has been developed by our administrator from within Ruby, and I am not familiar with Ruby. I can request that tables be created, but requires an additional step and I would like more flexibility when developing / testing new joins.

I started using views following the answer to a related question on SO (When to use R, when to use SQL). The top-voted answer begins "do the data manipulations in SQL until the data is in a single table, and then do the rest in R."

I have started using views, but I have run into a few issues with views:

queries are much slower
Views do not get dumped from the production to backup database that I use for analysis.

Are views appropriate for this use? If so, should I expect a performance penalty? Is there a way to speed up queries on views?


texts: What would be the best way to show table/database growth?

texts:
I've created a table that stores top 10 biggest tables on a database. it's schema is like this:
(example with 3 tables only)
| Table | size_mb | Date       |
|-------|---------|------------|
| tb1   | 2       | 01/01/2020 |
| tb2   | 5       | 01/01/2020 |
| tb3   | 1       | 01/01/2020 |
| tb1   | 4       | 02/01/2020 |
| tb2   | 12      | 02/01/2020 |
| tb3   | 2       | 02/01/2020 |
| tb1   | 10      | 03/01/2020 |
| tb2   | 20      | 03/01/2020 |
| tb3   | 5       | 03/01/2020 |

as our tables grow really fast, I have table size every day. in the example above, theres only the first day of the month, because this is what I'm trying to achieve with some kind of pivot or something.
What I would really want is to pivor this table by month.
I mean, I would like to have something like this:
| Table | size_JAN | size_FEV | size_MAR |
|-------|----------|----------|----------|
| tb1   | 2        | 5        | 6        |
| tb2   | 5        | 7        | 10       |
| tb3   | 1        | 2        | 6        |

I'm trying to think not about pivot itself but, if there's a good way to send a monthly report with this information, together with historical data.
EDIT:
the best I could do is to separate by month:
DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Tabela)
                    from tbldatabasesize_tables
                    group by Tabela
                    order by Tabela
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT Ano,
                    case when mes = 1 then ''Jan''
                         when mes = 2 then ''Fev''
                         when mes = 3 then ''Mar''
                         when mes = 4 then ''Abr''
                         when mes = 5 then ''Mai''
                         when mes = 6 then ''Jun''
                         when mes = 7 then ''Jul''
                         when mes = 8 then ''Ago''
                         when mes = 9 then ''Set''
                         when mes = 10 then ''Out''
                         when mes = 11 then ''Nov''
                         when mes = 12 then ''Dez''
end as Mes,' + @cols + ' from
             (
                select
                    year(DATE_REPORT) as Ano,
                    month(DATE_REPORT) as Mes,
                    Tabela,
                    convert(int,Tamanho_Da_Tabela_em_MB) as Tamanho_Da_Tabela_em_MB
                from tbldatabasesize_tables
            ) x
            pivot
            (
                avg(Tamanho_Da_Tabela_em_MB)
                for Tabela in (' + @cols + ')
            ) p '

execute(@query);

| Ano  | Mes | TB1| TB2 | TB3 | TB4 | TB5 | TB6 | TB7 | TB8 | TB9| TB10 |
|------|-----|-------------|-------------------|---------------------|----------------|------------|-----------|---------------|--------------|--------------------------------|------------------|
| 2020 | Mar | 3439        | 26363             | 77232               | 235643         | 32718      | 196380    | 11141         | 6570         | 9606                           | 24820            |
| 2020 | Abr | 3445        | 26413             | 77660               | 239613         | 32718      | 199713    | 11210         | 6591         | 9926                           | 24952            |
| 2020 | Mai | 3453        | 26493             | 78479               | 244071         | 32718      | 203283    | 11341         | 6613         | 10270                          | 25084            |
| 2020 | Jun | 3472        | 26583             | 79440               | 247916         | 32718      | 197185    | 11482         | 6630         | 10514                          | 25217            |


texts: Why would a select on a single table refer to another table?

texts: I have a simple select on a table, but the execution plan shows that another table is involved, in another database. This is definitely a table and not a view and I can't see database auditing turned on so what could cause this?


texts:
I had to restore a PostgreSQL 9.6 database from dump files (created with the command pg_dump --dbname=$DATABASE_URL --username=postgres --file=/usr/postgres-dump --format=directory).
After the restore (pg_restore --dbname=$DATABASE_URL --username=postgres /usr/postgres-dump), all the sequences are set to 1 (i.e. SELECT last_value FROM issues_id_seq; returns 1). However, the issues table has thousands of records (the latest record has id = 9331).
From my understanding, I could do something like
ALTER SEQUENCE issues_id_seq RESTART WITH 9332;

for all the tables in my database.
However, is there a more programmatic way to reset all the sequences based on the actual values of the id fields in all the tables?


texts:
I'm using Ola Hallengren's backup script.
I've added a network share location on the sqlcmd so that when the full backup job runs, the backup file will be saved in two different locations: 1 on the Z drive of the VM, and another one on the network share location. Here’s what the sqlcmd looks like:(please note that I've changed the file path of the network share...)

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d LSDBA_DB -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'Z:\Backups, \\, @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 192, @CheckSum = 'Y', @LogToTable = 'Y'" –b

After the job runs successfully, I now see that the backup file (.bak) is saved on both places. However, I've observed that there's a difference in backup file size between the one saved on the Z: drive and the one saved on the Network share.

This is the same case for the rest of the backup files (the one being saved on the network share are larger size). My thinking is that it should be the same size.

Any ideas why the size difference between the two backup files?

Thanks for the help.


texts: SQL Server 2008 R2 recognizes a domain account as a SQL Server account

texts:
After migrating from SQL Server 2000 to SQL Server 2008 R2, we have a few domain accounts that appear to the new server as SQL Server accounts. These accounts can SELECT from tables in the database they are assigned to, but they are not able to write to them as they could under SQL Server 2000. The SQL Server 2008 R2 log shows that at least one account failed at its attempt to INSERT into a table with this message (excerpt):

  Attempting to use NT account name with SQL Server
  Authentication....

This specific account had no problems doing its thing on SQL Server 2000, and nothing has changed at the account level, but the database it is assigned to "thinks" this account is a SQL Server account.  Has anybody experienced this issue and resolved it?


texts: Run a SQL statement on all databases monitored by OEM

texts:
I want to write a select SQL statement but I want it to run on all databases being monitored by Oracle Enterprise Manager.

I also would like to know how to run 'alter statements' on all database being monitored by the OEM. For eg. I would like to block/remove all users that have left the company from the database.


texts: Central Management Server - Query against host server

texts:
I like the idea of setting up a Central Management Server to run queries against other servers that I have to maintain. I'd like to be able to run one query to get information from all servers, like log sizes, backup history, index fragmentation, etc.

I'm just not sure how to set it up correctly so that I can also run these queries against the host database.  It seems crazy that you can't manage your Central Management Server.

How do people get around this?  Do you create a specialized instance whose sole purpose is acting as the management server?  Or pick a server that you just don't care about the health?

I don't want to have to run one query for the single server, and a second for the groups.  That seems to defeat the purpose.

Am I missing something?

Update with solution copied from comment below

Oh snap!  I stand corrected.

My original attempt was to register the CNAME/Alias as the CMS, then register the server by name.  This always failed.

Second attempt, registered the CMS using the server name. I then registered the server using the IP address and this worked.  I don't have clue how they're different, but it works so I'm happy.


texts:
Can some one please tell me the exact difference between the following statements ,

INSERT INTO table1 VALUES ((select count(column-name) from table-name2)+1);

here i am using the count function to auto-increment the value..

The same functionality can be done with sequences..

what will happen when both of this queries are executed and also please tell me the drawbacks of using the count over sequence


texts: Why is Microsoft SQL Server creating a ton of dump files?

texts:
How to determine what is causing Microsoft SQL Server to create dump files over and over taking up 170 GB of disk space? How do I troubleshoot what is causing the dumps?
The path of the dump files is
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSQL\Log\Polybase\dump
Can I delete these?
I have looked at The SQL server Log folder is expanding because of the SQL Dump files (what to do with it ?) but it doesn't really answer my question - it mainly states to contact Microsoft.


texts: Proving the usefulness of a specific index

texts:
I'm looking for a way to prove that some recently added indexes are positively impacting a production system.
I've collected some dmv statistics to show that the indexes are indeed being used (frequently) but need some way to prove that they are having a positive enough impact on the select queries, that any negative impact from the added update / delete time is offput.

I'm looking for any insight / advice on how to go about proving that these indexes have a positive impact.


texts: Cannot remove Mirroring / nor restore or delete database:Error 3456 (SQL Server 2012)

texts:
I have a database that was a mirrored partner and went into suspended state. (Mirror Server was shut down).

I took the principal database out of mirroring using SSMS. But now I am unable to restore the mirror database so that I can set up the mirroring again.

When I run ALTER DATABASE X SET PARTNER OFF on the mirror, SQL Server logs

  Error 3456 (Could not redo log record)

When I run RESTORE on this database from a backup - SQL Server tells me I cannot because the database is involved in mirroring.

Is there any other way to drop this database safely? I cannot take it offline and cannot delete it for the same reasons. It seems I cannot remove mirroring on the Mirror, for some reason. Thank you

Edit:

I should probably give more background. The situation came about because I shut down the OS before stopping SQL Server & SQL Agent services. (Assuming that the OS would trigger a safe shut down of these services, like others, not wise).

This probably caused some sort of corruption which makes the DB un-editable, even to remove it from mirroring. Aside from shutting down SQL Server and moving the .mdf / .ldf files then restarting it - are there other options? Can I actually try editing the .ldf file to remove the offending record, or something like that?


texts: Unknown,AppDomain 66 (master.sys[runtime].65) is Marked for Unload Due to Memory Pressure

texts:
I have noticed this error occasionally in the SQL error log:

  spid20s,Unknown,AppDomain 79 (master.sys[runtime].78) is marked for unload due to memory pressure.

I am using SQL Server 2016, SP1 CU5 (I am pushing for patching but the company is resistant).

Everything I have read points to non-CLR-specific memory pressure. There are suggestions around changing the MemToLeave setting in the start up parameters. Is this still the case for newer versions of SQL Server, or are there other recommendations?


texts: Error installing Clustering with SQL Server 2012 on Windows Server 2012

texts:
We are installing SQL Server 2012 on Windows Server 2012 and trying to accomplish clustering.

We've setup all the core functionality on the servers: advanced cluster preparation on all nodes and then completion on the final node. However, when we get to completion phase it completes and at the end it throws an error.

Here are the error details:

  Feature: Data Quality Services
  Status: Failed: see logs for details
  Reason for failure: An error occurred for a dependency of the feature causing the setup process for the feature to fail.
  Next Step: Use the following information to resolve the error, run RemoveNode on all the nodes of this instance, and then run PrepareFailoverCluster and then run CompleteFailoverCluster process again.
  Component name: SQL Server Database Engine Services Instance Features
  Component error code: 0x80131577
  Error description: The given key was not present in the dictionary.

I've tried rebuilding the system a few times and tried running the commands it referrers to with no avail. I feel like it's something simple that I'm just missing, any ideas anyone?


texts: What is the industry term for superset of "metrics" and "dimensions"?

texts:
If I want to refer to measures/metrics and dimensions with a single term, what would be a good industry-accepted expression?

Metrics is used commonly in UIs as well such as Google Analytics or Tableau.

The context for my question is for a UI. I'd like to group both of them and refer to them with an industry-accepted name, if one exists.


texts:
I couldn't come up with better title.

I'm preparing data structure for GDPR and I ran into one problem.
I have two types of legal basis for storing the personal data:

Law
User specific (consent)

So some of the purposes for collecting data apply to all users (law) and some of them apply only to users which gave consent.

I have relation table Consent between User and DataCollectionPurpose.

Should I fill it only with data where user actually gave consent.
Should I fill it with "fake" data because law applies to user whether they gave consent or not.

Solution 1 will make me write two separate queries and union them, solution 2 will fill table with loads of redundant data.

EDIT: Added code for testing

/*
CREATE TABLE [Rule]
(
      ID int
      , RuleName varchar(30)
      , IsGlobal bit
);

CREATE TABLE Person
(
      ID int
      , [Name] varchar(30)
);

CREATE TABLE PersonRule
(
      PersonID int
      , RuleID int
);

INSERT INTO dbo.Person (ID, [Name])
    VALUES
      (1, 'Tom')
     , (2, 'Richard')
     , (3, 'Harry')

INSERT INTO dbo.[Rule] (ID, RuleName, IsGlobal)
    VALUES
      (1, 'LocalRule', 0)
    , (2, 'GlobalRule', 1);

INSERT INTO dbo.PersonRule (PersonID, RuleID)
    VALUES
     (1, 1)
    , (2, 1)

    /*
    drop table PersonRule;
    drop table Person;
    drop table [Rule];
    */
*/

SELECT P.*, r.* FROM dbo.Person p
JOIN dbo.PersonRule pr ON P.ID = pr.PersonID
JOIN dbo.[Rule] r ON pr.RuleID = r.ID
UNION ALL
SELECT P.*, r.* FROM dbo.[Rule] r
OUTER APPLY dbo.Person p
WHERE r.IsGlobal = 1


texts: How are database backups conducted using SQL Server VSS Writer Service

texts:
We have few servers where backups are running from an unknown source. The physical_device_name in the backupmediafamily table is something like {922DDBA9-0686-46B8-94BC-XXXXXXXXXX}2.

I was checking randomly and noticed this kind of backup was running on the database. So I stopped SQL Server VSS Writer service and the backup process stopped. It prompted me to think that backups are being also being conducted by this service. I am still unaware on how Windows/SQL decides to trigger these backup at particular times only since the SQL Server VSS Writer service is running 24*7.

I have also seen cases on few servers where SQL Server VSS Writer is running but those backups are not happening at database level.

Can someone please help me in understanding (identifying) what is causing SQL Server to trigger such backups as I want to stop them across entire organisation.


texts: Find reason long running stored proc SQL Server

texts:
I have a stored proc which is called every 2.5 seconds by a program, 24-7.
This proc normally runs under the 10 milliseconds. A few times a day (3 -5 times) it takes around 42953 milliseconds to run (always between 42950 and 42959 milliseconds).

How can I find or profile the reason why it takes so long?

Update:
Sql server version 2008 R2

Procedure:

select
    [view_EventsToRaise].EventId,
    [view_EventsToRaise].EventStatus,
    [view_EventsToRaise].Tag,
    [view_EventsToRaise].SessieGebruikerId,
    Postbus.Persoon_Id_afz      as PersoonIdAfz,
    Postbus.Persoon_Id_adr      as PersoonIdAdr,
    Postbus.Persoon_Id_derde    as PersoonIdDerde,
    Postbus.Locatie_Id          as LocatieId,
    Postbus.Artikel_Id          as ArtikelId,
    Postbus.Onderwerp_Id        as OnderwerpId,
    Postbus.Bedrijf_Id_afz      as BedrijfIdAfz,
    Postbus.Bedrijf_Id_adr      as BedrijfIdAdr,
    Postbus.Bedrijf_Id_derde    as BedrijfIdDerde,
    Postbus.Document_Id         as DocumentId,
    Postbus.Document            as Document,
    DataKenmerk.Code            as PostbusDataCode,
    PostbusData.Inhoud          as PostbusDataInhoud,
    SimObj.Id                   as SimObjId,
    Eventsoort.Code             as Eventsoort
from
    [view_EventsToRaise]
    left outer join Postbus
        left outer join PostbusData
            inner join DataKenmerk
            on PostbusData.DataKenmerk_id = DataKenmerk.id
        on PostbusData.Postbus_Id = Postbus.id
    on Postbus.Id = [view_EventsToRaise].PostbusId
    left outer join SimObjEvent
        inner join eventsoort
        on eventsoort.id = SimObjEvent.eventsoort_id
    on [view_EventsToRaise].SimObjEventId = SimObjEvent.Id
    left outer join SimObj
    on SimObj.Id = SimObjEvent.SimObj_Id
order by
    [view_EventsToRaise].Tijd,
    [view_EventsToRaise].EventId

Update 29-9-2014:
When the above described situation occurs, looking to the profiler, the following got mine attention:
When the RPC:Completed event is registered, the the starttime is the current system time at the log moment, but the endtime is a value in the future (current system time plus the duration, approx. 43 sec in the future).

How is the value of the endtime column determined?
Is this a prediction?


texts:
ALTER TABLE users ADD COLUMN full_text_vector TSVECTOR;

CREATE INDEX full_text_index ON users USING gin(full_text_vector gin_trgm_ops);

The last line is giving me the following error:

  SQLSTATE[42804]: Datatype mismatch: 7 ERROR: operator class
  "gin_trgm_ops" does not accept data type tsvector (SQL: CREATE INDEX
  full_text_index ON users USING gin(full_text_vector gin_trgm_ops))

Without adding gin_trgm_ops to the last line it works, but then I can't search on possible misspellings. Is it possible to both have rankings AND misspelled words?


texts: Is it a good idea to use COLUMN AS (left([Another_Column],(4)) instead of LEFT in the select?

texts:
I came across a table like this:

CREATE TABLE TABLE1
(
 CD1    int
,CD2    varchar(16)
,CD3    varchar(21)
,CD4    decimal(14,0)
,CD5    varchar(4)
,CD6    decimal(18,2)
,CD7    AS (left([CD3],(4)))
)

This table has more than 4 BILLION rows (totally unnecessary, but this is another topic).

As you can see in the last column, they use AS (left([CD3],(4))). I think this is pretty useless, since we almost NEVER have a SELECT on this table, and here is just using space.

Isn't it better to select that field during the select when needed?


texts: How to batch multiple insert statements for database dump with MySQL?

texts:
I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's MyISAM and apparently that number is unreliable (but it's a big table, anyway). The database is backed up daily using mysqldump.

Currently, restoring the database from this dump file takes ~10 hours, and is only going to get worse. I'm using the --skip-extended-insert option because restoration will fail with a single multi-valued insert statement for all rows in the table. My understanding is that max_allowed_packet can be extended up to 1GB, but that's still not enough.

I thought that this would be a common enough problem and might have a generally accepted solution but I'm having trouble finding anything. So, my questions are:

Does mysqldump have an option that allows multi-valued inserts to be batched for each table e.g. instead of creating a single insert, create multiple inserts for n rows
If not, is there some other utility/alternative to accomplish this, or do I have to roll my own backup script that queries for table rows in batches of n using the limit clause and manually create the insert statements myself?

MySQL v5.5.40
Tables are a mixture of MyISAM and InnoDB.
The backup takes place locally.


texts: Does SQL Server 2008 R2 10.50.6560.0 Support TLS 1.2?

texts:
We recently upgraded a non-production SQL Server 2008 R2 system to SP3, and then Windows Updates installed the latest Security Update (10.50.6560.0).  I see that TLS 1.2 support for 2008 R2 is provided in 10.50.6542.0, but that update will not install on top of 6560.  Login attempts are failing and I can see in Event Viewer that the client is requesting TLS 1.2, but the server doesn't support it.

SSMS returns:

  ...(provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
  (Microsoft SQL Server, Error: 10054)

System Event Log on server:

  Event ID 36874
  An TLS 1.2 connection request was received from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The SSL connection request has failed.

I have tried updating the various SQL clients on both client and server, enabling TLS 1.2 in the registry on the server (and rebooting), to no avail.  So now I'm beginning to think that build 6560 doesn't support it, and if that's the case, I have to uninstall it somehow so that I can install build 6542 first.

We have other servers running the same build that work fine, so I'm trying to find what's different about this server--no luck yet.


texts: "Document Failed Validation" what is wrong with my schema validation

texts:
I am trying to create a collection with JsonSchema (really its "BsonSchema") validation. This is a document that I want to insert (with bogus values).  Note that Id is coming from the mongo c# driver as the ObjectID via StringObjectIdGenerator.
{
   "_id": "60300d5d756a0b91278f2a3d",
   "UserId": 2,
   "UserName": "UserName_1",
   "Password": "Password_1",
  "PasswordSalt": "InstallSchema",
  "CreatedDateUtc": "2021-02-19T19:11:22.6438703Z"
}

This is the latest of the many schemas that I have tried to use.
  {
  "bsonType": "object",
  "properties": {
    "_id": {
      "bsonType": "string"
    },
    "UserId": {
      "bsonType": "int"
    },
    "UserName": {
      "bsonType": "string"
    },
    "Password": {
      "bsonType": "string"
    },
    "PasswordSalt": {
      "bsonType": "string"
    },
    "CreatedDateUtc": {
      "bsonType": "date"
    }
  },
  "required": ["_id", "UserId", "UserName", "Password", "PasswordSalt", "CreatedDateUtc" ]
}

When inserting the document into the collection it reports "Document failed validation" with no more specific detail.  I know the document is correct, its the schema that is not, but I cant figure out what is wrong with it. I've tried different variations of the Id property including _id, as an objectid type, and removing it completely, but I dont know if thats even the problem. Any ideas on how to figure this out?
Also I did try creating the collection without schema validation and the document inserts fine. Then in Compass, I ran analysis for the collection and exported what it calls a schema (a very different format from the validator schema) and the properties and types match the schema above.


texts: Get list of orders that have not been closed that are older than 60 days

texts:
I have a table te_dates that holds order status information for orders.

The table is a many-to-one relationship, so one order can have many row inserts into my order_status table.

(dsc_id is the order status code)

order_id | dsc_id |             te_date | te_date_id
----------------------------------------------------
    2880 |      1 | 2014-11-19 09:27:18 |      16486
    2880 |      2 | 2014-11-19 09:27:23 |      16487
    2880 |      3 | 2014-11-24 14:10:40 |      16710
    2880 |      4 | 2014-11-24 14:10:41 |      16711
    2880 |      0 | 2014-11-24 14:10:49 |      16712
    2880 |      8 | 2014-12-17 15:52:29 |      17782

My client is asking me to find all orders that are 60 days or older that have NOT been closed, and change their status.

My thought process is to select distinct order_id of files that have been opened (dsc_id = 1) that are greater than 60 days old, and are not currently closed (dsc_id = 8).

I just can't wrap my head around the query.

Any help would be appreciated.


texts:
I need a way to query a SQL Table and pull back data on a quarter by quarter basis.  I know that SQL Server has a DatePart() function which has a parameter of q = Quarter and I came up with this syntax, but for a table that holds roughly 50,000 rows this syntax is extremely slow in it.  Is this the best way to achieve this result or is there more optimization that can occur?

Declare @startdate date = '20170101', @enddate date = '20171231'
Select
Employeename
,[Total Amount Paid] = SUM(ISNULL(Totalcheckamt,0))
FROM dbo.PaymentHistory
WHERE DatePart(q,[DatePaid]) = 1
AND [DatePaid] BETWEEN CAST(DateAdd(yy, -1, @startdate) As Date)
                   AND CAST(DateAdd(yy, -1, @enddate) As Date)
GROUP BY Employeename
Order By Employeename ASC

EDIT
My desired returned results is like so

Employee Name -- Q1 ---   Q2 ---   Q3 ---    Q4
James           XXXXX.XX  XXXX.XX  XXXX.XX   XXXX.XX
Roger           XXXXX.XX  XXXX.XX  XXXX.XX   XXXX.XX

EDIT 2
I tried this syntax using a case statement but it gives me an error of the error below.  What should I alter in order to be able to succesfully execute this statement?

  Msg 130, Level 15, State 1, Line 18
  Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SELECT
Employeename
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 1
        Then SUM(ISNULL(TotalCheckamt,0))
    end
) As Q12016
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 1
        Then SUM(ISNULL(TotalCheckamt,0))
    end
) As Q12017
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 2
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q22016
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 2
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q22017
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 3
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q32016
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 3
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q32017
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2016' AND DATEPART(Quarter, [DatePaid]) = 4
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q42016
,SUM(
    case
        when Datepart(Year, [DatePaid]) = '2017' AND DATEPART(Quarter, [DatePaid]) = 4
        Then SUM(ISNULL(TotalCheckamt,0))
        end
) As Q42017
FROM dbo.PaymentHistory
    GROUP BY Employeename
    Order By Employeename ASC


texts: Postgresql how to compare the tables which are present in different database

texts:
How can I compare the data which is present in different database in postgresql.

For example, I have 2 databases db1  & db2

Both have the same tables like tb1 tb2 tb3

I want to compare which table has different record.


texts: ODBC Data Source Administrator: Driver name is an empty string

texts:
Below you will see a screen shot of a warning in Windows 7.  Why would I get this error when opening up "Data Sources (ODBC)"?  How can I resolve it?  If you can give me some tips on where to start looking, I can make edits to this question to help diagnose the issue.

It's not critical that I fix this since I'm still able to add new data sources, but it's just annoying to see it every time I go into this dialog.

Control Panel > Administrative Tools > Data Sources (ODBC) > click "System DSN" tab

One other thing I'm noticing is that when I go into the "System DSN" tab for the first time, no data sources show up.  If I click "Add" and then cancel, the list then populates with three data sources.


texts: Would Star Schema be useful for a Lakehouse table accessed from PowerBI?

texts:
In a Lakehouse, the data is stored in a datalake (deltalake in to be precise in my case) instead of a data warehouse. The data there is stored in parquet format, which is a columnar format. I am planning to query the data using SQL Analytics, which is built on top of Spark. The data would be accessed for reporting and dashboardring in PowerBI.
The way the data is arranged right now is in the form of big single tables. Would it benefit to divide those tables into Star Schemas or do you think that won't benefit for Spark SQL?


texts: How does innodb_stats_on_metadata relate to query execution plan stability?

texts: The MySQL documentation for innodb_stats_on_metadata says "It can also improve the stability of execution plans for queries that involve InnoDB tables."  That is a little vague.  What is an example scenario related to this statement?


texts: Import to clean postgres database incomplete

texts:
I have been trying to copy a postgresql database to my local machine to use for some testing. I first generated a dump using pg_dump -h myhost -U myuser -f mydump.sql mydatabasename and then tried to load it into my machine using psql -d mynewdb -f mydump.sql.

This ran fine however when I inspect the tables, I only have a very small portion of the data inserted. I checked the plain text dump and there is definitely more data in there. I also tried using --format=t and --format=c with pg_restore but had the same issue.

Does anyone have an idea as to how I can get all the data from the dump?


texts: Query keeps on giving me 'unknown column' issue

texts:
I've researched quite a lot for the past few days about this error that I'm getting but I cannot really fix it so I was hoping somebody could help me. The query keeps on telling me:

ERROR 1054 (42S22): Unknown column 'updated_at' in 'field list'

This is the query:

SELECT  TIME_TO_SEC(SUM(TIMEDIFF(updated_at, created_at))) AS total FROM
        ( SELECT  DISTINCT date(l.created_at),
                ( SELECT  MIN(created_at)
                    FROM  logs
                    WHERE  updated_at BETWEEN l.created_at AND l.updated_at
                ) AS TIME_ENTER,
                ( SELECT  MAX(updated_at)
                    FROM  logs
                    WHERE  created_at BETWEEN l.created_at AND l.updated_at
                ) AS TIME_EXIT
            FROM  logs l
        ) AS whatever
    WHERE  updated_at <> CONVERT(updated_at USING ASCII)
    GROUP BY  date(created_at);

and the description of my table would be:

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| job_id      | int(10) unsigned | NO   | MUL | NULL    |                |
| timecard_id | int(10) unsigned | NO   | MUL | NULL    |                |
| event_id    | int(10) unsigned | NO   | MUL | NULL    |                |
| duration    | int(11)          | YES  |     | NULL    |                |
| gap         | int(11)          | NO   |     | 0       |                |
| created_at  | timestamp        | YES  |     | NULL    |                |
| updated_at  | timestamp        | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

I tried looking for unprintable characters but I didn't succeed

Any help would be appreciated, thanks in advance!


texts: One way replication between 2 sql servers on different networks

texts:
I would like to replicate between 2 sql servers that exist in different networks. This link is not relevant to my case as the servers are not connected: https://docs.microsoft.com/en-us/sql/relational-databases/replication/tutorial-replicating-data-between-continuously-connected-servers?view=sql-server-2017

If we assume that I can get the first copy of the database and then move it to the new server, in order to set up transactional replication, is it necessary that I use a vpn?

I have checked these links but were of no help:

SQL Server Transactional Replication over Internet

SQL Server replication over internet error

Is there a tutorial somewhere on 1) How to do replication over the internet
2) How to do replication if a vpn is required?

More info: The source would be a sql server 2014 and the destination a sql server 2016.


texts: Identify when Table Schemas were last Modified

texts:
I'm trying to identify all tables that have had indices added/altered since January 15. Is this possible?

I've run:

select * from INFORMATION_SCHEMA.PARTITIONS where UPDATE_TIME >= '2019-01-15'

but this is returning tables that had their content updated, not structure.

Maybe there's a different INFORMATION_SCHEMA table I should use? I'm running mysql 5.6 if that makes a difference. I'm using myisam and innodb tables (majority are innodb).


texts: Does Oracle optimize database while you use?

texts:
I have a 10gb database, that the queries were running very fast on Oracle XE.

I exported this db with expdp and imported on a new server Oracle 12c (hardware better than original, much more RAM and CPU) with impdp and now the queries are being slow.

I think Oracle created some internal optimisation based on queries I frequently do, and now that the Database is imported, he lost all this optimisations. This is just my theory.

Anyone knows if Oracle optimize database while you use? If yes, how do I accelerate this process on a recently imported schema.

Edit: I monitored v$sql_monitor and there was just one problem I fixed with a new index. But queries still are slow.


texts: How get the total count and number of distinct rows in a single query?

texts:
I have the following relation:
contract => company => item

The contract (root) entity has an owner_id column which indicates which user created the contract. When I am doing some stuff with a list of items I would like to verify two things and differentiate between them:

if all processed item ID exists in the database
if all processed item ID belongs to the user

The naive solution would be to select all owner_id and iterate through them in the software, however, I would like to do this in the DB to prevent sending the result through the network.
The solution I came up with uses min-max to check if every returned value was the same ID:
SELECT
  COUNT("inner"."owner") AS "existingItemCount",
  MIN("inner"."owner") = MAX("inner"."owner") AS "hasAccessToAll"
FROM (
    SELECT contract.owner AS "owner"
    FROM item
    INNER JOIN company ON company.id = item.company__id
    INNER JOIN contract ON contract.id = company.contract__id
    WHERE item.id IN (2, 3, 11)
) "inner"
;

However, this seems a little hacky. Is there a "standard"/recommended way of getting both results via a single query in a performant way?


texts: How to search double quotes in fulltext search in sql

texts:
I have double quotes in my Keyword. How can I search this in my fulltext search query. I have this query

SELECT  top 10 K.[KEY], 10, K.[RANK]
FROM    CONTAINSTABLE(ProductKeywords, Keywords, '("19*") AND ( "<Cat>5" OR "<Cat>30" OR "<Cat>398" ) AND NOT "<Blocked>"' ) AS k

It works fine but when i have double quotes in my keyword like

SELECT  top 10 K.[KEY], 10, K.[RANK]
FROM    CONTAINSTABLE(ProductKeywords, Keywords, '("19"*") AND ( "<Cat>5" OR "<Cat>30" OR "<Cat>398" ) AND NOT "<Blocked>"' ) AS k

it gives this error

Msg 7630, Level 15, State 3, Line 1
Syntax error near '*' in the full-text search condition '("19"*") AND ( "<Cat>5" OR "<Cat>30" OR "<Cat>398" ) AND NOT "<Blocked>"'.


texts: Filter Condition Difference - Where Clause vs Join Condition

texts:
Quick easy filter question.

What would be the difference in output, or what impact would it have moving a filter condition out of a WHERE clause into the Join condition.

For example:

Select a1.Name, a2.State
from student a1
left join location a2 on a1.name_id = a2.name_id
where a1.name LIKE 'A%'
and a2.state = 'New York';

To This:

Select a1.Name, a2.State
from student a1
left join location a2 on (a1.name_id = a2.name_id) and a2.state = 'New York'
where a1.name LIKE 'A%';

Thanks all.


texts: If the login credentials are in the ODBC connection, which security connection setting to use?

texts:
We have a linked PostgreSQL server and the remote login and password are parsed using the ODBC settings on the Windows server.

Under the Security tab in Linked Server Properties within SQL, which connection setting should be used please?

Not be made
Be made without using a security context
Be made using the login's current security context
Be made using this security context

If the last one, why would you want to pass the login credentials twice?


texts:
I my current database schema, there are hundreds of tables that reference another single table (called transaction_entry), that stores generic information about each row (when it was created, last updated, who created, who updated, and so on). For instance consider this (simplified) schema:

CREATE TABLE transaction_entry (
   id integer,
   created timestamp,
   modified timestamp);
CREATE TABLE person (
   id integer,
   te_id integer REFERENCES transaction_entry(id),
   name text);

Currently, we update this information on the client side (python), but I am considering moving this to the database itself, either using triggers or rules.

This should behave so that every time one row is updated, the referenced transaction_entry should be updated as well. Most of the time a single row is updated in the queries, but there are rare cases that a lot of rows will be updated by a single query.

The postgres documentation does state that:

    If you actually want an operation that fires independently for each physical row, you probably want to use a trigger, not a rule

This statement alone should make me decide that a TRIGGER is the correct choice for my case, but I did test with a RULE as well, and that does work fine. This is the rule that I tested with (also simplified):

CREATE RULE foo AS ON UPDATE TO person DO ALSO
   UPDATE transaction_entry SET modified = statement_timestamp() WHERE id = OLD.te_id;

What is the best choice for this scenario? Is a TRIGGER really the only option here, since each row has its on transaction_entry or is a RULE a safe option as well? What about performance? Should there be a significant difference between those two for this case?

I am leaning towards RULES since the syntax is considerably shorter and simpler then a corresponding trigger.


texts: Trying to connect to DB2 through WinAutomation, which driver should I use?

texts:
I'm trying to connect to DB2 through winautomation, I haven't been able to make the connection by using the options below.
I'm using the following connection string format
Server=myAddress:myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword;

And these are the options available:


texts: Restore a database to different databases without losing any data

texts:
I work on a database structure that has a lot of data in it.  This database structure is used by approximately 40 different servers.  I want to change some of the table structures, by adding some columns to tables, etc, but I do not have time to change 40 database structures.

How can I change all databases structure without losing any data with a script?

Can I backup a database structure without data and restore it to the other servers without them losing their data?

The servers are Microsoft SQL Server 2005.


texts: Is it possible to encapsulate a table in MySQL?

texts:
I am new to Databases. I want the following:

UPDATEs in a database to happen only through a stored procedure
The user does not have GRANT UPDATE
The user has GRANT EXECUTE ON PROCEDURE

I tried it this way but figured out that the user needed to have a UPDATE permission.

Is there any other way in which this can be achieved?


texts: Assertion failure from InnoDB while restoring using Innobackupex

texts:
I thought I would give back to all those people who help me when I have a problem. This might be something old, but I did not find it anywhere so here goes:

We recently upgraded our production DBs from percona 5.1 to 5.5. Parallel to this we have been using innobackupex for our backups, which has never let us down... Until now.

We were adding a node from a backup, when during the recovery phase we ran into this error:

InnoDB: Failing assertion: len % DATA_NEW_ORDER_NULL_TYPE_BUF_SIZE <= 1

Our backups were made with:

nice -n 19 innobackupex-1.5.1 --stream=tar ./ --user XXXX --password 'YYYYY' \
  --ibbackup xtrabackup_55 | \
  nice -n 19 /usr/bin/pigz - | \
  ssh -p 32231 ***@backup.****.com \
    'cat - > /var/backup/db/#{Time.now.strftime("%Y-%m-%d")}.tar.gz'

and recovered with:

innobackupex-1.5.1 --apply-log . --ibbackup xtrabackup

The problem was that our backups had been made with the switch --ibbackup xtrabackup. This was fine for mysql/percona 5.1 but does not work on mysql/percona 5.5 due to the fact that current transactions are not commited/rollbacked at the time of the backup.


texts: ORA-27101 while trying to login after shutdown immediate

texts:
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 23 19:29:19 2018

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

Enter user-name: system
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0

Enter user-name: sysdba
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0


texts: Display the the result of a substraction in a MySQL query (1 table)

texts:
I've made a table that looks like this:

stop (table name)
-cityName {PK}
-schedule {PK}
-price
-routeNo

Basicly the customer wants to know what's the cost difference between 2 different stops by giving the city name of both A and B and the routeNo (routeNo is the same for both stops)

Now I have to make a MySQL query in order to display that difference:

Say City A cost 10$ and City B cost 15$ The returned value should be 5$ (price of B - price of A)

Sorry about the rookie question but I'm lost I've tried:

SELECT (b.price - a.price) AS Returned
FROM stop
WHERE a.cityName = 'A' AND b.cityName = 'B' AND routeNo = 'x';


texts: Can this query be improved? view for few count totals on a table

texts:
I am not a dba but I am trying to create an effecient query for a view that will return some totals on a table

select
 (select count(1) from alerts) as Total,
 (select count(1) from alerts where iscomplete is null or iscomplete = 0) as Active,
 (select count(1) from alerts where iscomplete = 1) as Complete


texts: Oracle writes whole file during database creation is it ok?

texts:
Consider empty (!) database creation:

CREATE TABLESPACE reload DATAFILE 'D:\myDb.dbf'  SIZE 3000M  ONLINE;

This is leads to writring 3000 megabytes to the disk for empty database. Is it normal for oracle? I am new to oracle database, did I miss something important in CREATE TABLESPACE which leads to such behavior?


texts: How to check existence in Oracle without scanning the entire table?

texts:
Normally, to check existence in Oracle I will do:
SELECT COUNT(1)
FROM foo
WHERE bar = 'baz'

However, if the foo table contains multiple rows where bar='baz', this query needlessly scans through the entire table in order to report the total count.
How do I quickly check if a column in a table contains at least one row with a specified value, and have the query short-circuit, such that as soon as it detects that the value exists, the query will stop and return, as opposed to scanning the entire table?
Ideally I would want this query to return a scalar value, regardless of how many rows in the table contain the value.

The following solution appears to short-circuit (compared to SELECT COUNT(1), this is much faster for a large table), however it will either return 1 if the value exists, or return no rows at all if the value does not exist. It would be better if a row was always returned.
SELECT 1
FROM DUAL
WHERE EXISTS (
    SELECT 1
    FROM foo
    WHERE bar = 'baz'
)


texts: SQL Server 2008 log directory has too much dump file

texts: My SQL Server log directory has too many dump files. I have 3 databases on the server and the number of the transactions on them is very small, why would there be so many large files??


texts: What versions of SSMS have an Express edition?

texts: I know all the SQL Server versions from 2005 to 2014 have an express edition. This question is specifically about SSMS. There was an SSMS Express 2005, 2008, and 2008 R2, but I haven't found any later version.


texts: Streaming replication in postgres question

texts:
We have recently setup streaming replication in our Postgres server (t01, t02). t01 is master and t02 is the slave. I want to understand the below two issues:

Recently our /var directory of t01 server got full and app team was not able to access the application. My understanding was if t01 /var was full, the connection should be made to t02 and application should start using that as t02 /var was not full.

If we shutdown t01 server, will my application automatically use the t02 databases, Streaming replication will provide HA in this case or not?


texts: What are the minimum matching criteria recommended for reliable demographic based patient matching?

texts:
When matching patients based on demographic data are there any recommendations on what fields should match for the patient to be the "Same Patient"?

I know the algorithms will be different for different implementations, I'm just curious if there are any best practices or recommendations around this process.

First Name
Last Name
Date of Birth
SSN
Address
City
State
Zip

etc?


texts: Best practice to store and execute a list of queries to build a Fact table

texts:
Good afternoon,

Junior DBA/Developer here:

I have a DataMart that I have to build with a bunch of Statistical information. Each entry will be a different statistic and I have 80 different queries that will be executed on top of the DW to build the Fact table.

My question is on the best way to store, maintain and execute these queries. I have thought about storing them in different .sql files, storing them all together in a table (each entry is a query), or even managing them all in the same file with a giant UNION ALL putting them all together. This seems like a nightmare if not done right, and I don't really know how to proceed in doing this cleanly.


texts: Financial Inst - Model Role+Relationships b/w Person and Account

texts:
A Person can be on one-or-more Accounts. For each Account a Person is on, Person has a certain role on that account i.e.

Person 1 is primary on Account 1

Person 1 is joint on Account 2

Person 1 is beneficiary on Account 3

Note:
An Account only has 1 primary Person, but can have any number (0+) of joint or beneficiary

How should this be modeled? I at least forsee these tables:

Person
- person_id
- name
- dob
- address

Account
- account_id
- creation_date

I'm unclear on representing a many-to-many relationship between Person and Account while simultaneously capturing the role (primary, joint, beneficiary).

My idea: Have multiple role Link tables like so:

Link_Primary_Person_Account
- person_id
- account_id

Link_Joint_Person_Account
- person_id
- account_id

Link_Beneficiary_Person_Account
- person_id
- account_id

What do you think? What are other ways of doing this? This is all I can come up with.


texts: Trying to import a large sql file using BigDump. Partial fail occuring

texts:
Problem:

I am using the php script: BigDump to import a sql file that phpMyAdmin says has 9344926 rows.

However when I run it, BigDump calculates and inserts 4050623 rows

It should have Total: 9344926 rows

My SQL file looks like this:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `ree`
--

-- --------------------------------------------------------

--
-- Table structure for table `media`
--

CREATE TABLE IF NOT EXISTS `media` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `URL` varchar(255) DEFAULT NULL COMMENT 'URL',
  `MLSNUMBER` bigint(20) NOT NULL COMMENT 'MLSNUMBER',
  `FILETYPE` varchar(32) DEFAULT NULL COMMENT 'FILETYPE',
  `MIMETYPE` varchar(128) DEFAULT NULL COMMENT 'MIMETYPE',
  `CREATEDATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'CREATEDATE',
  `MODIFYDATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'MODIFYDATE',
  `POSITION` int(10) NOT NULL COMMENT 'POSITION',
  PRIMARY KEY (`id`),
  KEY `MODIFYDATE` (`MODIFYDATE`),
  KEY `MLSNUMBER` (`MLSNUMBER`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9540549 ;

--
-- Dumping data for table `media`
--

INSERT INTO `media` (`id`, `URL`, `MLSNUMBER`, `FILETYPE`, `MIMETYPE`, `CREATEDATE`, `MODIFYDATE`, `POSITION`) VALUES
(1, 'http://image.realcomponline.com/photos.rps?PATH=PROPERTY/35A00/1C07B/35A0011C07B74E/8ECA936E29E4F7.jpg&g=100&sp=0&l=0&t=0&r=10000&b=10000&o=0&1cf=0&w=320&h=240', 212069136, 'PICTURE', 'image/jpeg', '2012-07-06 09:54:50', '2012-07-06 09:54:50', 7),
(2, 'http://image.realcomponline.com/photos.rps?PATH=PROPERTY/35A00/1C07B/35A0011C07B74E/13B4A1CB2C6483.jpg&g=100&sp=0&l=0&t=0&r=10000&b=10000&o=0&1cf=0&w=320&h=240', 212069136, 'PICTURE', 'image/jpeg', '2012-07-06 09:54:52', '2012-07-06 09:54:52', 8),
(3, 'http://image.realcomponline.com/photos.rps?PATH=PROPERTY/33019/DE74A/330196DE74A041/28DBA86D5DF4D9.jpg&g=100&sp=0&l=0&t=0&r=10000&b=10000&o=0&1cf=0&w=320&h=240', 212069138, 'PICTURE', 'image/jpeg', '2012-07-06 11:00:53', '2012-07-06 11:00:53', 6);
   ...
   ...
   ...

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Question:

Is the problem with the BigDump script or am I doing something wrong? Or is there a better way to go about my 9 million row import/export?


texts:
We switched a slave to master today and after restarting the new master it tries to connect to the old master.

Even with stop slave;, it tries to connect after restarting.