texts:
I have setup a Credential/Procedure/Program/File Watcher/Job to monitor a folder for new files, but it is not working.  I am trying to figure out what I can check to troubleshoot why this isn't working.  After scaling back the code to a bare minimum, here are somethings I have already done.

Verify that files are being created in the folder monitored - They are.
Verify that the procedure can be run by itself - It can.
Verify that the File Watcher shows up in DBA_Scheduler_File_Watchers and is enabled. - It does and is.
Verify that DBA_Scheduler_Job_Run_Details shows a successful execution - It does NOT show any entries for this job.
Check the alert log - Nothing interesting.
Check for other trace files - I am getting trace files mattching *_j000_*.trc, but they just have this: FILE_TRANSFER error is: with nothing after it.

Could someone explain what I am doing wrong or give me a way to troubleshoot this further?  Here is my test code:

--Create Credential.
BEGIN
    sys.dbms_scheduler.create_credential(
        username => 'oracle',
        password => 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
        database_role => NULL,
        windows_domain => NULL,
        comments => NULL,
        credential_name => 'TestCredential'
    );
END;
/

CREATE TABLE FilesFound(FoundDate Date);

CREATE OR REPLACE PROCEDURE TestProcedure(iFileInfo In sys.scheduler_filewatcher_result) AS
BEGIN
   INSERT INTO FilesFound VALUES (sysdate);
   COMMIT;
END;
/

BEGIN
   DBMS_SCHEDULER.create_program(
      program_name => 'TestProgram',
      program_type => 'stored_procedure',
      program_action => 'TestProcedure',
      number_of_arguments => 1,
      enabled => False);
   DBMS_SCHEDULER.define_metadata_argument(
      program_name => 'TestProgram',
      metadata_attribute => 'event_message',
      argument_position => 1);
END;
/

BEGIN
dbms_scheduler.create_file_watcher(
   file_watcher_name => 'TestFileWatcher',
   directory_path => '/u01/test',
   file_name => '*.*',
   credential_name => 'TestCredential',
   destination => NULL,
   enabled => False);
END;
/

BEGIN
   dbms_scheduler.create_job(
   job_name => 'TestJob',
   program_name => 'TestProgram',
   event_condition => NULL,
   queue_spec => 'TestFileWatcher',
   auto_drop => False,
   enabled => False);

   --Enable Everything.
   dbms_scheduler.enable('TestProgram, TestFileWatcher, TestJob');
end;
/

--Set a one minute check interval.
BEGIN
  DBMS_SCHEDULER.set_attribute(
    'file_watcher_schedule',
    'repeat_interval',
    'freq=minutely; interval=1');
END;
/

--Create a file.
DECLARE
  vFile utl_file.file_type;
BEGIN
  EXECUTE IMMEDIATE 'create or replace directory TESTDIRECTORY as ''/u01/test''';
  vFile := utl_file.fopen('TESTDIRECTORY', 'TestFileWatcher.txt', 'w', NULL);
  utl_file.put_line(vFile, 'File has arrived '||SYSTIMESTAMP, TRUE);
  utl_file.fclose(vFile);
END;
/

--Delay to give time for file to appear.
BEGIN
   DBMS_LOCK.SLEEP(120);
END;
/

--Check the table.
BEGIN
   FOR vx IN (select count(*) ct from sys.FilesFound) LOOP
      If (vx.ct = 0) Then
         DBMS_Output.Put_Line('Failed - No File Watcher Activity.');
      Else
         DBMS_Output.Put_Line('Success - File Watcher Activity Found.');
      End If;
   END Loop;
END;
/

--Cleanup.
EXECUTE dbms_scheduler.drop_job(job_name => 'TestJob');
EXECUTE dbms_scheduler.drop_program(program_name => 'TestProgram');
EXECUTE dbms_scheduler.drop_file_watcher(file_watcher_name => 'TestFileWatcher');
EXECUTE DBMS_SCHEDULER.drop_credential(credential_name => 'TestCredential');
drop table FilesFound;
drop procedure TestProcedure;
drop directory TestDirectory;


texts:
I am using SQL Server version: Microsoft SQL Server 2012 - 11.0.2100.60 (X64).

I created a single table containing one and only one integer-valued column. I then added one and only one row to the table.

CREATE DATABASE SQLServerInternals;
GO

USE SQLServerInternals;
GO

CREATE TABLE page_header
(
    c1 INT IDENTITY NOT NULL
) ON [PRIMARY];
GO

INSERT INTO page_header DEFAULT VALUES;
GO

DBCC IND(SQLServerInternals,page_header,1);
GO
DBCC TRACEON(3604);
GO
DBCC PAGE (SQLServerInternals,1,231,2);
GO

Here is the output from DBCC PAGE.

Does anyone know what the DB Frag ID = 1 means and whether it is the value 0x0100 that I see in bytes 64-65? I assume that the value is at most a SMALLINT type. I assume that the remaining bytes are for "future use".


texts: How to switch from MariaDB to MySQL in WAMP server?

texts:
I installed wamp server on my PC and by default wamp server is connected to MariaDB as showed below

How can i switch to MySQL?
Thank you in advance for your help.


texts: Removing all Schemas and Objects from a database, excluding specific Schemas and Objects

texts:
I have a database with three main Schemas;

Global
Permissions
Unique Schema name based on the customers ID number (1, 2, 3, 4, ect...)

Each customer has objects in all three schemas, with the Global and Permissions schema object names, containing the customers ID number followed by an _

Example;

[1].[Test]
[Global].[1_Test]
[Permissions].[1_Test]

So far, I have written the bellow script that does this, but only keeps one customers data, not multiple;

    DECLARE @TenantId NVARCHAR(10)
DECLARE @Commands NVARCHAR(MAX)
DECLARE @Statements TABLE
    (
        Commands NVARCHAR(500)
    )

SET @TenantId = '686'

INSERT INTO @Statements
    EXECUTE  (

'SELECT ''DROP TABLE'' + '' '' + ''['' + s.[name] + '']'' + ''.'' + ''['' + t.[name] + '']'' AS ''Commands''

FROM sys.schemas s
    JOIN sys.tables t on s.schema_id = t.schema_id

WHERE   s.name <> '''+ @TenantId +'''
AND         s.name <> ''dbo''
AND         t.name NOT LIKE '''+ @TenantId +'_%''
AND         t.name NOT IN (''DatabaseVersion'', ''PermissionTypes'')

ORDER BY s.name, t.name'    )

SELECT * FROM @Statements

--SET @Commands =(
--SELECT STRING_AGG (CAST([Commands] AS NVARCHAR(MAX)), ' ') FROM @Statements )

--SELECT (@Commands)

How can I convert this so that it takes multiple IDs into account, except for just one? I feel that I need to use a Cursor here, but not sure how that would be structured.

Thanks in advance.


texts: Find user who kicked off a SQL Agent job at runtime

texts:
I have inherited a breakglass procedure that allows certain users to kick off a SQL Agent job that inflates their privileges on the instance.

It currently sends an email to say that it has been invoked, waits for 4 hours then revokes the privileges, sending an email out to say that it has been revoked.

But I just can't find a way to capture who kicked the Agent job off.

Is there a way that the user id details can be captured or is it always going to take the SQL Agent users details?


texts: how to convert query related to calculate work hours in MySQL to MS Access

texts:
I want to calculate work hours in each day using MS Access.I have query for same thing in MySQL but this thing is not working in MS Access using pdo php.

query in MySql

    SELECT MachineNo, Empcardno, Date, SEC_TO_TIME(SUM(tsecs)) AS total_time
FROM (
  SELECT CASE WHEN MachineNo = @mn AND Empcardno = @en AND `Date` = @dt
              THEN @rn := @rn + 1
              ELSE @rn := 1
         END AS rn,
         @mn := MachineNo AS MachineNo, @en := Empcardno AS Empcardno,
         @dt := `Date` AS `Date`,
         CASE WHEN @rn % 2 = 1 THEN -TIME_TO_SEC(`Time`)
              ELSE TIME_TO_SEC(`Time`)
         END AS tsecs
  FROM timesheet
  CROSS JOIN (SELECT @mn := 0, @en := 0, @dt := '', @rn := 0) init
  ORDER BY Date, Time
) t
GROUP BY MachineNo, Empcardno, Date

how to convert this query to make compatible with ms access pdo connection using php

table structure and data in ms-access

https://i.stack.imgur.com/YaCPp.png


texts: Is this a standard way to design a database?

texts:
I recently learned about how relationships are defined in the database at work, and was wondering if this is a standard practice.

Let's say we have two processes: Process A, and Process B.  Process B depends on the results from Process A, so there is a relationship that needs to be defined between a Process B run and a Process A run.  This is how the relationship is defined:

TableProcessA:
Id

and

TableProcessB:
Id
ProcessAId

Now, up to this point, things make sense to me, but then things get a little strange to me and my understanding of table design.  Whenever a row is created in TableProcessA or TableProcessB, a function is called that creates a globally unique id for each.  So basically, all of the Id fields in TableProcessA and TableProcessB will not contain any matches because the Id's are not just unique to its table, but to the entire database.

My question is, how standard is this?  I was brought up on the idea that each table should simply have an autoincrementing id that is unique just to the table and not the entire database.


texts: Using MySQL binary logs to keep backups between database dumps?

texts:
I currently take a full database backup using mysqldump once a day. I'd like to make this more granular (e.g. every 15 minutes), but can't just take more full dumps due to size constraints. Can I use mysqlbinlog to only get the changes since the last mysqldump?

How do I only get the binary logs since the exact time of the last mysqldump? If another transaction started before that time and finished after it, will it still be included properly in the log?


texts: Oracle password has expired, however I have no way of changing it

texts:
When I connect to my local development database, I get an error saying the password has expired.  I've been trying to change it with sqlplus to no avail:

C:\>sqlplus TPMDBO/password@localhost/global

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 6 15:41:02 2013

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

ERROR:
ORA-28001: the password has expired

Changing password for TPMDBO
New password:
Retype new password:
ERROR:
ORA-01017: invalid username/password; logon denied

Password unchanged

I get this error no matter what new password I type in.

My main question is how I can reset this password.  However, I'm also curious if there's a way to make the password never expire.  This is a local dev database, I really don't care about the security or anything and none of the data on it is important.


texts: How can I select the next available row id of varchar type in MySQL?

texts:
I have a table with primary key 'Id' of varchar type. I want to select the next row when user clicks next record, in php, for example
1st row id= 'a'
2nd row id= 'b'
3rd row id='h'
4th row id='z'

Im currently on page with get variable  id='a'
I want to get the next which is 'b', then 'h' then 'z'

The first record is easy as I send  page.php?id='a' to display
table records.

The next record,
Select id from table where id > 'a'
But I dont want 'h' or 'z' because 'b' is next.
I dont have a clue, any help will be appreciated


texts: Find full name from AD alias in SQL Server

texts: We have recently migrated from Sybase/SAP ASE to MSSQL Server. master..syslogins in  sybase has two columns name and fullname. Name would be AD alias name and fullname is Full Name of the user. Example...Dan Brown is the user and his AD alias would be BrownD. Now, in SQL Server i created logins using their AD alias. How would i get the full name from the alias name in SQL Server. Is there any function/sp available in SQL Server? Thanks for your help.


texts: Upgrading to SQL Server 2017 from SQL Server 2012

texts:
Upgrade or New Build? Any suggestions?
Does anyone have any gotchas or war stories to share about this process?

We have a 2012 instance in Prod that has both OLTP and OLAP databases. We are creating two instances, one to be dedicated to each of those tasks. The OLAP instance is a new build, however to minimize end-user down time we are considering an upgrade for the instance that will house the OLAP databases.

I'm looking for general recommendations that go beyond what I'm reading in the Microsoft docs.


texts: Where can I find the first standardization of SQL, SQL-86?

texts:
This question is different but similar to this request looking for SQL-89.

The first draft of SQL is labeled SQL-86. Numerous references are made to it. Is this available to download? Wikipedia doesn't even have a page for it. I'm interested in it for historical reasons.

It seems to also be called

ANSI X3.135-1986
CAN/CSA Z243.47-88
ISO 9075:1987
SQL/1
NBS FIPS 127

The spec is cited in documents from the period as coming from ANSI Technical Committee X3H2.

I know there is webstore.ansi.org, but I can not find the X3.135-1986 document. I can however find X3.168-1989 spec which is available under X3.135-1992 Searching for both X3.168, and X3.135 do not permit me to buy the 1986 spec.


texts: Very strange tables in mysql own database

texts:
I was checking some tables into MySQL database, and I can see a lot of tables (1000+) with random names, and I have no idea of how they created and why they exists.

I'm sure they don't belong to the original MySQL database, and the content of the tables is the more weird, just one column named data with datatype LONGBLOB. Some tables have rows in it , some don't.

Here is the query im using and some screnshot of the content of the tables.

Any idea of where this belong and if is secure to drop all of this tables?

SELECT table_name FROM information_schema.tables where table_schema="mysql" and table_name not in ("columns_priv","db","event","func","general_log","help_category","help_keyword,"help_relation","help_topic","host","innodb_index_stats","innodb_table_stats","ndb_binlog_index","plugin","proc","procs_priv","proxies_priv","servers","slave_master_info","slave_relay_log_info","slave_worker_info","slow_log","tables_priv","time_zone","time_zone_leap_second","time_zone_name","time_zone_transition","time_zone_transition_type","user"); /*exclude normal tables*/


texts: Does a read replica help if the problem are heavy queries?

texts:
The application that our team is responsible for is having ever more often DB performance problems. The application querying it doesn't throw lots of concurrent queries, the problem we have are rather some heavy queries joining a few tables. These queries are executed just every few minutes, and execution seldom overlap, but they sometimes make the DB choke and as the system has more data, it's a growing problem. The solution so far has been optimizing these queries, by trying e.g. to fetch less fields, and scaling up the DB.

I am relatively new to the team and today I asked if a read replica had been considered. I know little about DBs, so that felt like natural to me, but a senior engineer told me that it would barely help, because the read replica would have the same problems as the master: it would still need to do the same writes as the master, and the heavy queries would be equally heavy to it and the chances of it timing out, the same. The only gain would be the amount of them each of the instances has to serve.

Put like that, sounds very reasonable. My questions are:

Is a read replica really not of any help in this situation?
What are some alternatives?

PS: in case it matters, it's PostgreSQL.


texts: What is it called when you search the middle of a string instead of the beginning?

texts:
I am trying to polish up my vocabulary to better communicate with my fellow developers. We have several places in the site where we are debating if we should search for a string from the beginning 'running%' vs anywhere in the string '%running%.

I have been calling the middle search "fuzzy" which I realize is incorrect as fuzzy means changing the form of the word "run", "runing" [sic], "runed" [sic].

What is the correct terminology for searching the beginning of a string and searching the middle of a string?


texts: How to find all views with invalid column names

texts:
I am trying to refresh all my views using code I found on the web:

DECLARE @ViewName VARCHAR(256)
DECLARE cViews CURSOR READ_ONLY FOR SELECT name from sys.views
OPEN cViews
FETCH NEXT FROM cViews INTO @ViewName
WHILE @@FETCH_STATUS != -1
BEGIN
    EXEC SP_REFRESHVIEW @ViewName
    PRINT 'View ''' + @ViewName + ''' has been refreshed.'
    FETCH NEXT FROM cViews INTO @ViewName
END
CLOSE cViews
DEALLOCATE cViews

I am getting the error Error Message: Invalid column name 'CloseDate'., but it's not telling me which view has the error. How would I find the name of all views with an invalid column name with the name of the invalid column? Thank you.


texts: How to create database/tablespace/schema and users for quickstart developers on Oracle 12?

texts:
Background

I need to create a contained development space for a new application. This requires a user account to connect with all permissions, such as create/drop tables and stored procedures. Following the principle of least privilege, all of the application's database operations will be wrapped in stored procedures, so another application user account is required with only execute permissions to those stored procedures (itemized in production; all in development).

I've got a fresh Oracle 12c installation on Windows Server 2008 R2 Enterprise with available connections to what I think is called a database named 'orcl'. Now I can start sqlplus from cmd.exe

sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 8 09:49:03 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

How do I functionally accomplish the following?

I've provided the MySQL console (mysql -u root -p) equivalent instructions under each of my requirements.

new database/schema created

CREATE DATABASE appdb;

new dbo user created for DDL

CREATE USER 'appdb_owner'@'localhost' IDENTIFIED BY 'c0mpl3xpw0rd';
GRANT ALL PRIVILEGES ON appdb.* TO 'appdb_owner'@'localhost';

new test user for application execution

CREATE USER 'appdb_user'@'localhost' IDENTIFIED BY 'simplerpw0rd';
GRANT EXECUTE ON appdb.* TO 'appdb_user'@'localhost';

Failures

I'm very familiar with MySQL, but totally new to Oracle and sqlplus. I think some of my confusion stems from the distinction between a database, tablespace, and a schema from my lesser familiarity with DB2. From what I've seen on the 11.1 create database page, it looks unnecessarily complex (like a new installation) for what I'm trying to achieve, so I think what I am looking for instead is a new schema that will simply provide isolated access to the data. But when looking at the 10.2 documentation for schema creation, it says this:

  This statement does not actually create a schema. Oracle Database automatically creates a schema when you create a user (see CREATE USER). This statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions.

But when I try to create a user from the 10.2 documentation

sqlplus / as sysdba
SQL>CREATE USER appdb_owner IDENTIFIED BY c0mpl3xpw0rd;

I get the following error

  ORA-65096: invalid common user or role name

Which led me to an answer about distinguishing between common names, which I gather have access to all schemas, which is not what I want. So I'm not sure which comes first, the chicken or the egg?

I found another answer that said I needed to connect as SYSTEM, but sqlplus / as SYSTEM resulted in an error without even prompting for the password.


texts:
I am trying to insert user and multiple of his user role + return id of created user in single row. I used following query:

WITH inserted_user as (
 insert into "user" (username, password, nickname, account_non_expired,
account_non_locked, credentials_non_expired, enabled)
  values ('username', 'password-hash', 'nickanme', true, true, true, true)
  returning id
)
insert into user_role values
  ((select id
    from inserted_user), 2),
  ((select id
    from inserted_user), 3),
  ((select id
    from inserted_user), 4)
returning user_id;

My issue is, that in my result I see 3 rows with the same ID (this is bad for my java application which expects 1 number). I am not sure how to edit this to get my result. I tried to wrap this into another select + many other more or less dumb things, but none of it was working. Could you please help me?


texts: Error when using DBCA to create database

texts:
I am using Oracle Database XE (18c). The install completed without any error however, when I try to create a database using dbca it shows error: java.lang.NullPointerException.

The log is:
https://ghostbin.com/paste/G0SEb
I am still very new to Oracle. How can I solve this?


texts: Schema change not transmitted in a merge replication

texts:
I did an ALTER VIEW on SQL Server (which is the replication publisher), but just after that I found that the change is pending to be transmitted (executing sp_enumeratependingschemachanges):

I searched everywhere in the distribution database and I looked for answers at internet, but I did not find how to know the reason for this not to be transmitted (aka error message or similar).
Is there a way to check it?


texts: Recreate all foreign keys in all tables as deferrable (batch)

texts: I want to make all foreign keys in my database deferrable. But there is no possibility to alter an existing constraint. So I need to drop and add again each foreign key. How to do it automatically?


texts:
I have a database with almost 20 publications, and every once or twice a week a random number of publications needs to be reinitialized.  I have to go and do it manually then every thing works as expected.

I'm using the following command to initialize publication:

exec sp_reinitsubscription @publication = 'PubName', @subscriber = 'all'

The problem is that I have create it for each publication one by one,
so my question is, is there a way to execute it on all publications?


texts: Can I use SPARSE somehow on a non-nullable bit column with mostly false values?

texts:
I have a table that stores the results of queries that are run at least once a day. There's a bit column that represents whether the row is from the most recent run of a particular query with a particular set of arguments. (Yes, it's a functional dependency, but a necessary denormalization for performance, since most queries on this table are only interested in the most recent result.)

Since this bit column is almost always a false value, I'm looking for the best way to tune queries returning only the true values. Partitioning isn't an option (Standard Edition). It seems like making the column SPARSE would be an interesting solution, but I believe that would require me to change the column to nullable and use NULL rather than 0 for false values. Seems a little kludgy.

Is there an option similar to SPARSE that would optimize space/performance for a non-null bit column with mostly (well over 99%) false values?

Pinal Dave's article indicates that both zero and null values are optimized, but this doesn't seem right to me, since these are different values -- unless MSSQL is using the same mechanism for non-null columns to indicate the default value. This would be great if it were true, but the BOL doesn't mention this.


texts: Understanding of Oracle Statement-Level Read Consistency between Oracle 10g and 10g+

texts:
I'm new to Oracle and I just want to confirm my understanding of Statement-Level Read Consistency for Oracle 10g and 10g+.
The Oracle 10g document states:

Oracle always enforces statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in time—the time that the query began. Therefore, a query never sees dirty data or any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins.

Oracle 11g document states:

Oracle Database always enforces statement-level read consistency, which guarantees that data returned by a single query is committed and consistent with respect to a single point in time. The point in time to which a single SQL statement is consistent depends on the transaction isolation level and the nature of the query:

In the read committed isolation level, this point is the time at which the statement was opened. For example, if a SELECT statement opens at SCN 1000, then this statement is consistent to SCN 1000.

In a serializable or read-only transaction this point is the time the transaction began. For example, if a transaction begins at SCN 1000, and if multiple SELECT statements occur in this transaction, then each statement is consistent to SCN 1000.

In a Flashback Query operation (SELECT ... AS OF), the SELECT statement explicitly specifies the point in time. For example, you can query a table as it appeared last Thursday at 2 p.m.

See the bold text. If I understand correctly, Oracle changed its behavior between 10g and 10g+. In 10g, Statement-Level Read Consistency is always based on the view when the statement starts. In 10g+, Statement-Level Read Consistency is based on a view that depends on the isolation level and the query itself. Is my understanding correct? Thank you.


texts: MySQL : DROP TABLE Starting with a Prefix

texts:
EXAMPLE : I have more than 30 tables starting with a prefix "dp_" and about 12 starting with "ex_".

QUESTION : How can I drop all tables starting "dp_" in one query ?


texts: What is the equivalent of SQL Server's db_datareader/db_datawriter in PostgreSQL?

texts:
I'm porting an application from SQL Server to PostgreSQL. Most of this is simple as the application only really does simple selects, inserts and updates. However I need to create a user to access the database. The SQL Sever code creates its user like this:

USE [master] CREATE LOGIN [ExampleUserName] WITH PASSWORD = 'ExamplePassword', CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;
USE [ExampleDB];
CREATE USER [ExampleUserName] FOR LOGIN [ExampleUserName];
ALTER ROLE [db_datawriter] ADD MEMBER [ExampleUserName];
ALTER ROLE [db_datareader] ADD MEMBER [ExampleUserName];

What is the equivalent of that in PostgreSQL?


texts:
The following relation schema: R(A,C), S(A,D), T(X,Y).
Primary key attributes bold

Would like to know why the following two are not equivalent queries

Q1 = πA(σC<10(R)) and Q2 = σC<10(πA(R))

The answer: Q2 Invalid expression as the selection condition refers to a non-existent attribute. I'm not sure what the answer means. Q1 and Q2 looks similar but Q2 is invalid.

Could anyone provide some values and examples to explain? Thanks!


texts: How can I use Spatial Indexes for timestamps (non-spatial 1d data)?

texts:
I'm using MySQL 8.0. I have some table containing TIMESTAMP column and I need to select within TIMESTAMP range:

SELECT *
FROM t_tbl
WHERE ts BETWEEN @someDate1 AND @someDate2

I want to make this query as fast as possible so I decide to try GEOMETRY trick. First of all I add some magik geometry column:

ALTER TABLE t_tbl
  ADD COLUMN geom_ts GEOMETRY NOT NULL
  GENERATED ALWAYS AS (Point(TO_SECONDS(ts),0)) STORED;
ALTER TABLE t_tbl ADD SPATIAL INDEX IDX_g_i (geom_ts);

So ts momentum is really a point onto X (time) axis. Now to check ts is in some range I'm using such geometry expression:

SELECT *
FROM t_tbl
WHERE MBRCONTAINS(LineString(point(@someDate1,0),point(@someDate2,0)), geom_ts)

This works but EXPLAIN doesn't show me any spatial index usage - it performs tablescan...


texts: Howto remind and restore recovery mode during SSDT deployment?

texts:
Due to deployment of a SSDT database project I would like to be able to switch the target database's recovery mode from FULL to BULK LOGGED and back after the deployment finished in order to do any data manipulation changes with minor invasive logging.
I can't simply ignore the setting because deploying to development or staging environment should keep the SIMPLE setting but in production the setting should be switched back and forth.

Is it possible to "save" the original setting in Pre-Deployment script and restore this setting after the deployment? Do variables taken in pre-deployment keep alive between those scripts?


texts:
I have a MSSQL2008 and MSSQL2005 in my system.All SP's is up-to-date.

2008;

  Microsoft SQL Server 2008 (SP2) -
  10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft
  Corporation Enterprise Edition
  (64-bit) on Windows NT 6.1
  (Build 7600: )

2005;

  Microsoft SQL Server 2005 -
  9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005
  Microsoft Corporation Workgroup
  Edition on Windows NT 5.2 (Build 3790:
  Service Pack 2)

Now,My question is; I setup to linked to SQL Server 2005 from SQL Server 2008 under the Server Object.But,When I query , I am receiving the following error.

select * from [192.x.x.x].DBName.dbo.TableName

Result;

  Msg 7399, Level 16, State 1, Line 1

  The OLE DB provider "SQLNCLI10" for
  linked server "192.x.x.x" reported an
  error. The provider did not give any
  information about the error.

  Msg 7312, Level 16, State 1, Line 1

  Invalid use of schema or catalog for
  OLE DB provider "SQLNCLI10" for linked
  server "192.x.x.x". A four-part name
  was supplied, but the provider does
  not expose the necessary interfaces to
  use a catalog or schema.

Thanks in advance,

Cheers,


texts:
Started seeing some strange errors from dependent processes this morning. After some digging it appears that the file /mysql/proc.MYD is set to zero length. The file /mysql/db.MYD appears to be truncated as well.

What's even more interesting is that this isn't the case on the replicated slave.

What would cause this?
Why wouldn't it show up on the slave server?
If I copy the files /mysql/proc* from the slave back to the master - will this restore the procedures?

Nearly every file in the /mysql folder was touched at the same time. Some were changed while many were not. My first thought is that it's a failure in the file system but it appears to be localized to this one folder.

EDIT: Pertinent details:

RHEL 5.x
MySQL 5.5

I brought down both MySQL instances and am attempting to get the MySQL folder off of tape. I would run fsck on the volume but the man page has some pretty scary warnings.


texts:
Ok so, I'm still a beginner in databases. I have this code:

$sql="
  INSERT INTO complaints_members
    (password, complaint)
  VALUES
    ('$mypassword','$submit')
  ON DUPLICATE KEY UPDATE
    complaint='$submit' ;
";

This simply updates my complaint in the existing entry. How can I insert a new entry with the same key, instead of updating the old one?

I'm not very familiar with the terms of SQL, so I'm sorry in advance.


texts:
Let's say I have 3 main tables:
category, tag, url

category and tag have a MANY_MANY relationship (category_tag).

tag and url have a MANY_MANY relationship (url_tag).

I would like for a given category to retrieve all the linked urls.

What is the easiest, most efficient way to do this?

Could I maybe build a "VIEW"? What would be the query?

I am using MySQL.


texts: PostgreSQL Index Only Scan can NOT return ctid

texts:
I have this table in PostgreSQL 11:

CREATE TABLE A (id bigint PRIMARY KEY, text text)

Now I want to fetch the ctid of rows meeting some condition like id = 123.

However, even using pg_hint_plan to hint PostgreSQL to use Index Only Scan, it will always give me Index Scan:

/*+ IndexOnlyScan(a) */ explain analyze select ctid from a where id = 823977776533426178;
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Index Scan using a_pkey on a  (cost=0.14..8.16 rows=1 width=6) (actual time=0.038..0.039 rows=1 loops=1)
   Index Cond: (id = '823977776533426178'::bigint)
 Planning Time: 0.122 ms
 Execution Time: 0.095 ms
(4 rows)

My understanding is that ctid is the physical row id (block_no: record_offset) of each row and it must be included in any btree index, since it needs this information to fetch the row from heap file.

Then why can't an index-only scan return the ctid directly?
Is it just that PostgreSQL does not implement it that way?


texts: Group By With Rollup results table has totals at the top

texts:
I have the following query that returns a set of counts by weeks beginning with Monday, with a row for the aggregate totals.

select distinct
    DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) as B2B_Week,
    count (distinct bm.MISSION_ID) as B2B_Mission,
    count (distinct dl.DRIVER) as Distinct_B2B_Drivers
from
    DD.B2B_MISSIONS bm
    inner join dd.IN_GATE ig on bm.MISSION_ID = ig.IN_MISSION_ID
    inner join dd.XNS_DRIVER_LOGIN dl on ig.IN_DRIVER_ID = dl.driver
group by
    DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM)
  with rollup
order by
    DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM)
;

My Results Table looks like this:

B2B_Week    B2B_Mission Distinct_B2B_Drivers
            1647        357
44          717         264
45          930         301

I want it to look like this:

B2B_Week    B2B_Mission Distinct_B2B_Drivers
44          717         264
45          930         301
TOTAL       1647        357

ISSUES

The totals row appears at the top rather than the bottom. This is
probably related to the second problem.
I have been unable to use a CASE statement to return 'TOTAL' with
the date column is NULL. The primary problem is that the DATE is an
INT and 'TOTAL' is not. I have tried various methods of using CAST
on either the entire CASE statement or just pieces of it. I've tried putting the Case statement in the SELECT or in the GROUP BY statement.

Here is an example of what happens when I try to get TOTAL to appear.

case when DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) is null
then 'TOTAL' else DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) end

  "Conversion failed when converting the varchar value 'TOTAL' to data type int."

What am I doing wrong?


texts: Execution time high for Query in sp_executesql

texts:
I have a query generated from a .net application and parameters are linq parameters. The procedure I followed to tune the query is as below.

I run the application and capture the query using profiler.
The query is of the form

exec sp_executesql N'SELECT
[Extent1].[Id] AS [Id],
[Extent1].[GroupId] AS [GroupId],
[Extent1].[Agency] AS [Agency],
[Extent1].[Group] AS [Group],
[Extent1].[Claim] AS [Claim],
[Extent1].[StartDate] AS [StartDate],
[Extent1].[ExpireDate] AS [ExpireDate],
[Extent1].[IsActive] AS [IsActive]
FROM [dbo].[Permissions] AS [Extent1]
WHERE ([Extent1].[GroupId] = @p__linq__0) AND (([Extent1].[ExpireDate] IS NULL)
OR ([Extent1].[ExpireDate] > @p__linq__1)) AND ([Extent1].[IsActive] <> 1)',
N'@p__linq__0 int,@p__linq__1 datetime2(7)',
@p__linq__0=15,@p__linq__1='2017-05-10 00:00:00'

This is just a sample query. The actual query is much bigger and includes lots of joins involving multiple views and table etc.

My question here is:

When the run the query as it is, the execution times is around 15 sec
When I take out the query alone from sp_executesql and apply the filter values to the query, it runs in less than 1 sec.

I understand that since the values are hard-coded into the query it will be faster, but why is that much difference in execution time? Also the execution plans are different for both:

SQL Execution plan with sp_executesql

Execution Plan with hard-coded SQL

I am the only user working on this server and I execute same parameters in both scenarios. I have applied OPTION(RECOMPILE) to the end of the sp_executesql and it executes in 1 sec compared to 15 sec. But the execution plan is still not similar to the hard-coded plan.

Anyways OPTION(RECOMPILE) makes the query run fast in one second as it forces the query to compile and create new plan each time. But I am not supposed to hack the code and change the query. I will suggest OPTION(RECOMPILE) as a last resort to my application team.


texts: Does PostgreSQL have some sort of hidden feature to "clean up the internal identifiers"?

texts:
When I designed my database, I did it in the public schema. Later, I painstakingly moved it into a separate schema, as I began to realize that it was too messy to keep everything mixed in the public one.
I also stopped using "prefixes" for the names.
As a result, many of the "internal identifiers" (that is, the name of foreign keys, unique constraints and so on), now have extremely "ugly" names, reflecting their "past history" in my database.
I cannot express how much this annoys me, even though I rarely look at them and never refer to them in any of my application code.
Please tell me that there is some command or solid, battle-tested query I can run in order to "reset the internal identifiers". That is, to pick the standard format which PostgreSQL (or is it pgAdmin 4 that does this?) picks if you don't give it an explicit name? For example, I have a unique constraint with this identifier:
personal.XYZApp widgets_URL_key

However, the table name is now widgets and is not in the personal schema but instead the work schema. So the auto-picked no longer makes any sense.
I want it to be "reset" to a name that now makes sense, such as:
work.widgets_URL_key

Frankly, I don't care about the "format" it uses, as long as it's uniform.
Naturally, it's out of the question for me to manually sit and do this myself. I also don't fancy coding a script on my own which does this. I wish for this to be a "solved problem", such as running a simple special PostgreSQL query which performs this task.
I fear that this falls in the same category as the "ANSI-only, 64-char-max" application_name thing: it "would be good to have, but nobody has contributed it and we are not gonna do it ourselves because it doesn't directly affect us"...
In fact, why doesn't PostgreSQL auto-update these identifiers whenever anything changes in the structure? In fact, why have identifiers at all instead of referring to the "component" with its "live" name? I don't even understand why those "identifiers" for "internal" database objects (FKs, UNIQUEs, etc.) exist at all.


texts: I'm trying to better understand how SQL server does log buffering and flushing in order to better balance costs and performance.  I know that unless you use delayed durability, log entries for a transaction will be flushed all the way to disk at commit time.  However, what I'm really curious about is what else might get flushed along with that.  For example, if the log disk is already busy flushing the entries for a previous transaction (and thus can't flush for the current transaction yet), will entries for other transactions also get flushed to disk at the same time, or will the entries for each transaction get flushed serially.  Given that in most cases the actual flushing to disk is by far the most time consuming operation and the fact that for logging operations, the time to flush is affected only minimally by the size of the flush (at least for the frequent case of many small transactions), it would seem like a good optimization for the log processor to pipeline flushes such that while one flush is happening, entries from any committed transactions awaiting a flush are gathered together and flushed together rather than one at a time.  This would improve throughput with little impact on the latency of the first transaction (it might even improve average latency).  Does anyone know if such an optimization has been made in SQL Server?  If it has been made in a specific release, can you identify the release and where you found that information?


texts: What are changes made to a database schema that is already in 4NF that would break applications?

texts:
For this question I consider applications select and use columns by name (so I am excluding use of select * as a breaking change).

Asked in a different way, is it the case that once a Database schema is in 4NF then any change that needs to be applied to it should only be an "additive" change and therefore not break any existing applications that use it?

For me I am discounting some obvious changes:

Drop column (This change can be held back and only applied once all uses of the column are no longer active)
Drop table (Same as drop column)

For me I am considering "additive" changes as non-breaking:

Add column
Add table
Denormalisation (Add column + whatever triggers etc are required)

For me the "Refactoring" change that would break applications is to alter a columns datatype (e.g. Change from INTEGER to VARCHAR type)

What other changes can be made to a schema in 4NF that will break applications using that schema?

UPDATE: Currently we have:

Changing the data type in a breaking way (INTEGER -> VARCHAR)
Adding a constraint (or tightening an existing constraint)

Q: Are there any schema design changes that can take a design from 4NF into a different 4NF design?


texts: Split date range (timestamp) into equal parts by Month- SQL Server

texts:
Experts,
Have a question regarding splitting a date range into equal parts by months including the time part
example - fromdate - 06/29/2020 09:00:00 and todate - 06/29/2021 09:00:00
Want to split this date range into twelve equal parts like below
06/29/2020 09:00:00 - 06/30/2020 12:59:59
07/01/2020 00:00:00 - 07/31/2020 12:59:59
.........
.......
06/01/2021 00:00:00 - 06/29/2021 09:00:00

I cant write recursive CTE as this is a sql synapse module I am running against..
With below query I am able to split the date part, but time part is not coming properly as above.. Please help me as this is a blocking my development
declare @FromTs DATETIME
declare @ToTs DATETIME
SET @FromTs = GetDate()
SET @ToTs = DATEADD(month, 12, @FromTs)

;WITH n(n) AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.all_columns
),
d(qi,qrt,qtt,n,f,t,md,bp,ep,rn) AS
(
    SELECT
    ,n.n, @FromTs, @ToTs,
    DATEDIFF(MONTH, @FromTs, @ToTs),
    DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY( @FromTs),  @FromTs)),
    DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, n.n,
    DATEADD(DAY, 1-DAY( @FromTs),  @FromTs))))
    FROM n INNER JOIN <datetbl> AS d
    ON @ToTs >= DATEADD(MONTH, n.n-1, @FromTs)
)
SELECT qi,qrt,qtt,
new_from_date = CASE n WHEN 0  THEN f ELSE bp END,
new_to_date   = CASE n WHEN md THEN t ELSE ep END,rn
FROM d WHERE md >= n


texts:
I am new to pgAdmin and to SSH tunnels. I am trying to establish a connection to a postgres DB with SSH tunnel. I am on Windows 10. I am given the following instructions (I changed all the names and ports in the below)
Add the following to your SSH config (~/.ssh/config):
Host prod
  Hostname myorg.org.uk
  User sshusername
  IdentityFile idef.pem
  LocalForward 9999 localforward.amazonaws.com:8888

Now you can tunnel your way through to PostgreSQL:
ssh -N prod

And now psql et al can connect (You must open a new Terminal window while the SSH tunnel is running):
psql -h localhost -p 9999 -U connectionusername -d dproduction

I am also given the dproduction database password for the database I am trying to connect to: dproduction_pwd
I don't understand where everything goes in pgAdmin. I did the following:
Create-Server:
Name = test

Connection:
Host Name/Address: localhost
Prot: 9999
Maintenance database: postgres
username: connectionusername

SSH Tunnel:
Tunnel host: myorg.org.uk
Tunnel post: 9999
username: sshusername
Identity file: C:\idef.pem
Password: dproduction_pwd

I must be doing something wrong, as I don't use LocalForward from the ssh config above, where does this go? putting it in Tunnel host does not work.


texts: Rate of transactions reduced when mirror is synchronized

texts:
I'm currently managing a database of ~ 40 giga in a HA/mirrored set up with primary, mirror and witness nodes.

When the system is fully synchronized i noticed a decrease in rate of transactions which has a negative impact on my production environment (general slowness and UI latency).
When the mirror is temporarily suspended everything works as it should.
Principal and mirror are built on the same server model, same OS(SQL server 2012) and same hardware specs.

Is there anything i should check to match the performance when the mirror is suspended?


texts: Call python machine learning model from UDF or SP

texts:
I would like to run records from my database (Postgres), after an update, through a machine learning model. This model resides in a model.py file.

Next to simply porting the python model over to a SP or UDF (I do not know which would be the best to use, if at all).

How might I call this python model and pass the record parameters to the model and return a desired result?


texts: SQLite3 doesn't have concat_ws, looking for workaround

texts:
I'm trying to concatenate some columns and I would like to use space as my  output string delimiter.

Basically concat_ws would be a perfect fit, but it doesn't exist for SQLite3.

I'm inside a SELECT already and I'd like to end up with a string that looks like this:

col1_val col2_val col3_val

I have col1, col2, and col3 as my columns, and they may have empty strings as values which I'd not want to delimit.

col1, is safely expected to exist, to not be NULL, and not be empty.
However either col2 and col3 might be empty, and I'd thus want to skip.

I've tried a few different ideas, but all of them seem to end up with double delimiters when a string is empty.

So some expected results:

col1_val
col1_val col2_val
col1_val col3_val
col1_val col2_val col3_val


texts:
In MySQL, if I have schema myschema and a trigger named mytriggername I can drop a trigger like follows

DROP TRIGGER IF EXISTS myschema.mytriggername

Anyway, I can't find on DB2 docs for the DROP statement any counterpart for the DROP TRIGGER IF EXISTS statement?
Any suggestion?


texts:
I locked 3 innodb tables with write lock

lock tables hive.NEXT_COMPACTION_QUEUE_ID write;
lock tables hive.NEXT_LOCK_ID write;
lock tables hive.NEXT_TXN_ID write;

I find out which table is locked through SQL

SHOW OPEN TABLES WHERE `Database` LIKE 'hive' AND In_use > 0;

but only one table (last table I locked) ,why ？

+--------------------+----------------------------------------------+--------+-------------+
| Database           | Table                                        | In_use | Name_locked |
+--------------------+----------------------------------------------+--------+-------------+
| hive               | NEXT_TXN_ID                                  |      1 |           0 |

but when I want to show the first table which I thought isn't locked,it locks!why? is show open tables not correct?

 show create table hive.NEXT_COMPACTION_QUEUE_ID;
ERROR 1100 (HY000): Table 'NEXT_COMPACTION_QUEUE_ID' was not locked with LOCK TABLES


texts: Storing STRING and BOOL in a STRING Column?

texts:
I have a form_fields table that represent different HTML form fields (e.g. text, textarea, checkbox, etc.) that a user needs to populate. It also contains meta data about the fields, like whether or not it is required, placeholder text, label, etc.

I also have a table that maps to the columns of form_fields to capture the data that the users inputted, which looks like this:

===================================
form_field_values
===================================
id -> auto increment integer
-----------------------------------
form_fields.id -> FK to form_fields
-----------------------------------
value -> string, user supplied data
-----------------------------------

The problem is the value column. Most of the data I'm storing there is just a string (in the case of text fields, textareas -- which make up about 90% of what I'm trying to capture). In some cases, I need to store a bool value there, like whether or not a checkbox is checked.

Should I store the string "TRUE" or "FALSE" (or "1" or "0") in the value field? Then somehow cast it to a bool when retrieving the data?

Or did I design this form_field_values table wrong? Maybe I need a table that have columns matching form_fields columns, and store the data that way. Problem with this is that I would not be able to associate these columns with the columns in form_fields.


texts: Does a high CPU usage -say 98%- necessary mean the database will be slower?

texts:
I have 4 python scripts each running in their own virtual machine. The scripts are accessing a central postgresql database. All scripts are reading from the same table but posting to different tables in the same database. The database is located on its own machine. The DB machine has 8 CPUs.

Running the command top on the database server shows:

  PID USER      PR  NI  VIRT  RES  SHR S  %CPU %MEM    TIME+  COMMAND
12609 postgres  20   0 3700m 563m 559m R  98.4  3.5   1602:57 postgres
12603 postgres  20   0 3700m 927m 923m R  97.8  5.8   1603:17 postgres
20619 postgres  20   0 3700m 455m 451m R  96.4  2.8 237:36.05 postgres
12616 postgres  20   0 3700m 1.0g 1.0g R  94.8  6.4   1547:26 postgres

The CPU usage is extremely high. I have noted that my scripts have become slower all of a sudden hence my interrogation of resources using top. Question is, am i better off letting each script run alone i.e. with db exclusivity or the high CPU usage seen does not necessary slow down the database and I should be looking elsewhere to optimize my speeds?

Tuned Postgresq

I have tried tunning postgres as directed here but I don't see any increase in speeds or even higher memory utilization by postgres. I have set the shared buffers to 3.5GB as my machine has 16GB and edited the shared memory kannel to 4GB

$sysctl -p
kernel.shmmax = 4294967296
kernel.shmall = 1073741824
vm.overcommit_memory = 1


texts:
I have a weird issue in my Oracle database, after 10 - 15 minutes of not using it (Not performing any query, insert on SQL Developer tool) then it will say ORA-27101: shared memory realm does not exist so I have to login through sqlplus and do startup. After that I can now use my database again then after 10 - 15 minutes same thing happens.

Below is the alert log but I can't understand it

Sun Jan 24 19:03:29 2016
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_smon_1076.trc  (incident=43416):
ORA-00600: internal error code, arguments: [6856], [0], [102], [], [], [], [], [], [], [], [], []
Incident details in: c:\oracle\diag\rdbms\orcl\orcl\incident\incdir_43416\orcl_smon_1076_i43416.trc
Process debug not enabled via parameter _debug_enable
Sun Jan 24 19:03:34 2016
PMON (ospid: 6464): terminating the instance due to error 474
Sun Jan 24 19:03:54 2016
Instance terminated by PMON, pid = 6464

--
................................
--- End of Call Stack Trace -----
----- Process Map Dump -----
------------------- Memory Map of process ----------------
Start Addr-End Addr   Type     Size        ModuleName
Unab
0x00400000-0x06a4afff Image    0107261952  C:\ORACLE\PRODUCT\11.2.0\DBHOME_1\BIN\ORACLE.EXE
0x575c3a43-0xc6c0a8ab Image    1868852841  C:\WINDOWS\SYSTEM32\USER32.DLL
0x74bf0000-0x74c3afff Image    0000307200  C:\WINDOWS\SYSTEM32\MSWSOCK.DLL
------------------- End of memory map --------------------

----- Guard Frame Metadata Dump -----

internal error ORA-600 seen on the error stack
process appears to be having problems repeatedly, committing suicide
Background_Core_Dump = partial


texts: Querying MySQL and SQL Server from same IDE

texts:
I am currently working with MySQL and SQL Server.

For querying MySQL I use MySQL Workbench, for SQL Server I use SQL Server Management Studio.

I am mixing up shortcuts all the time. For instance, to execute a query in SSMS it's F5 and in MySQL WB it is CTRL-Enter.

Is it possible to query both databases from the same IDE? A plugin for WB or SMSS? A different IDE that works for both databases?


texts:
Consider the following query:

 mysql> SELECT GROUP_CONCAT(a.id SEPARATOR '\n')
    -> FROM assignment a
    -> INNER JOIN statements s ON a.id = s.assignment_id
    -> INNER JOIN assignment_status_log asl ON a.status_log_id = asl.id
    -> INNER JOIN assignment_status ags ON asl.status_id = ags.id
    -> WHERE ags.category <> 'PASSED'
    -> AND ags.category <> 'TEST'
    -> AND ags.category <> 'REJECTED'
    -> AND ags.category <> 'CANCELED'
    -> AND ags.category <> 'NA'
    -> AND s.id IS NOT NULL
    -> AND s.id IN (SELECT MAX(sts.id) AS dctrn__1 FROM assignment agr INNER JOIN statements sts ON agr.id = sts.assignment_id WHERE agr.id = a.id)
    -> AND s.grade > 0
    -> AND ags.types <> 'CB'
    -> AND ags.types <> 'CD'
    -> AND ags.types <> 'CE'
    -> AND ags.types <> 'CF'
    -> GROUP BY a.id;

I would like to have an idea as to how I can troubleshoot what is taking so long.  I have added index on s.grades and ags.types column, and am pondering if I would need to try to have the conditions on the JOIN instead of in WHERE clause.  It has currently been running for close to 2 hours, on 500 000 record. Awful.

Any insight or hints as to what is affecting the performance on this will be greatly appreciated.


texts: Why does DELETE leave a lingering effect on performance?

texts:
At the end is a test script for comparing the performance between a @table variable and a #temp table.  I think I've set it up correctly - the performance timings are taken outside of the DELETE/TRUNCATE commands.  The results that I am getting are as follows (times in milliseconds).

@Table Variable  #Temp (delete)  #Temp (truncate)
---------------  --------------  ----------------
5723             5180            5506
15636            14746           7800
14506            14300           5583
14030            15460           5386
16706            16186           5360

Just to make sure I'm sane, this shows that CURRENT_TIMESTAMP (aka GetDate()) is taken at the time of the statement, not of the batch, so there should be no interaction between TRUNCATE/DELETE with the SET @StartTime = CURRENT_TIMESTAMP statement.

select current_timestamp
waitfor delay '00:00:04'
select current_timestamp

-----------------------
2012-10-21 11:29:20.290

-----------------------
2012-10-21 11:29:24.290

It's quite consistent in the jump between the first run and the subsequent runs when DELETE is used to clear the table.  What am I missing in my understanding of DELETE?  I have repeated this many times, swapped the order, sized tempdb to not require growth etc.

CREATE TABLE #values (
  id int identity primary key, -- will be clustered
  name varchar(100) null,
  number int null,
  type char(3) not null,
  low int null,
  high int null,
  status smallint not null
);
GO
SET NOCOUNT ON;

DECLARE @values TABLE (
  id int identity primary key clustered,
  name varchar(100) null,
  number int null,
  type char(3) not null,
  low int null,
  high int null,
  status smallint not null
);
DECLARE  @ExecutionTime  TABLE(      Duration bigINT    )
DECLARE  @StartTime DATETIME,  @i INT = 1;
WHILE (@i <= 5)
  BEGIN
    DELETE @values;
    DBCC freeproccache With NO_InfoMSGS;
    DBCC DROPCLEANBUFFERS With NO_InfoMSGS;
    SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate()
    /****************** measured process ***********************/

    INSERT @values SELECT a.* FROM master..spt_values a join master..spt_values b on b.type='P' and b.number < 1000;

    /**************** end measured process *********************/
    INSERT @ExecutionTime
    SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP)
    SET @i +=  1
  END -- WHILE

SELECT DurationInMilliseconds = Duration FROM   @ExecutionTime
GO

-- Temporary table
DECLARE  @ExecutionTime  TABLE(      Duration bigINT    )
DECLARE  @StartTime DATETIME,  @i INT = 1;
WHILE (@i <= 5)
  BEGIN
    delete #values;
    -- TRUNCATE TABLE #values;
    DBCC freeproccache With NO_InfoMSGS;
    DBCC DROPCLEANBUFFERS With NO_InfoMSGS;
    SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate()
    /****************** measured process ***********************/

    INSERT #values SELECT a.* FROM master..spt_values a join master..spt_values b on b.type='P' and b.number < 1000;

    /**************** end measured process *********************/
    INSERT @ExecutionTime
    SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP)
    SET @i +=  1
  END -- WHILE

SELECT DurationInMilliseconds = Duration FROM   @ExecutionTime
GO

DROP TABLE  #values
SET NOCOUNT OFF;


texts: Postgresql small index for row contains query

texts:
I want to store several hundreds of gigabytes of geodata in a postgresql database.
I want to query the data on position, time and/or a unique identifier for each object.

My table layout is similar to this:

CREATE TABLE objects(
id int not null,
at timestamp not null,
pos geometry(Point) not null
/* Other columns irrelevant to the question */
) PARTITION BY RANGE (at)

BRIN indexes on at and pos perfectly serve the needs, since they speed up the queries with relatively small indexes.

A B-TREE index on id quickly becomes several gigabytes, so the server memory cannot hold many of them. BRIN is not suitable, as the id's are very spread within a page, so minimum and maximum is not a useful statistic.

Is there an alternate index type which can speed up queries like SELECT ... FROM objects WHERE id = x with smaller index sizes?

The table is append-only, so only for the newest timestamps new rows will be added.


texts:
What are the factors that would affect the RAM usage by a query?

How do I assess the rough RAM usage of a query given the complete SQL?

This is regarding the MySQL RDBMS.


texts: change encoding of database from latin1_swedish_ci to utf8

texts:
I have an huge database in latin1_swedish_ci. Now i need to convert all data to utf8 collation.

My question is about the consistency of the information. When I do this change it is possible corrupt the data that is in database?

something like this:

mysql> alter database user_db default character set utf8 collate utf8_unicode_ci;

there is any risk of changing the information?


texts: Import (imp) not showing final report stats/results

texts:
I'm trying to execute an import of a dmp file an not obtaining the final results of the import. It's really weird because i obtained really big files (nohup.out and imp_xxx.log) with the following content :

Column 98 1
Column 99
Column 100
Column 101 0
Column 102 0
Column 103
Column 104
Column 105
Column 106 0
Column 107 0

Expected:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing CPEDF33's objects into CPEDF33
. . importing table                 "x"            0 rows imported
. . importing table                 "x1"           0 rows imported
. . importing table                 "x2"           0 rows imported
. . importing table                 "x3"     2599617 rows imported
. . importing table                 "x4"     2244766 rows imported
. . importing table                 "x5"     1561223 rows imported
. . importing table                 "xx5"         20 rows imported
. . importing table                 "xx4"    1931263 rows imported
. . importing table                 "xx3"       2167 rows imported
. . importing table                 "xx2"    4044280 rows imported
. . importing table                 "xx1"    4126340 rows imported
. . importing table                 "x8"        2453 rows imported
. . importing table                 "x7"           0 rows imported

Imp command:

nohup imp MINE/Hi2013* file=H_FULL_20140212.dmp log=imp_H__FULL_20140212.log FROMUSER=MINE TOUSER=MINE STATISTICS=NONE ROWS=Y IGNORE=Y &


texts: How do I uninstall the old version of Postgres on CentOS 7?

texts:
I just installed Postgres 9.6 on CentOS 7.  However when I run

[root@server tmp]# /usr/bin/psql --version
psql (PostgreSQL) 9.2.23

It still seems to be pointing to my old version.  How do I uninstall the old version (or figure out what its called) without harming the new version?


texts: User defined function can only have select statements

texts:
One of the main differences between UDF and SP is that UDF can only have select statements inside it and not insert/update/delete statements. Can someone please explain the reason behind this? The below function:

create function test(..)
...
BEGIN
insert into EMPLOYEE('22',12000,'john');
return 0;
END

is not valid. But why is this so?


texts:
I am desperately seeking a solution for a problem, which i will try to fully describe hoping that a somebody can come up with a clever solution.

The metadata :

At the moment i have the following set of tables (minified to remove chatter) :

Table "Recipe" has a Foreign Key to table "Item" on Recipe.OutputITemId = Item.Id

Table "RecipeIngredient" has a Foreign Key to table "Item" on RecipeIngredient.ItemId = Item.Id

Table "RecipeIngredient" has a Foreign Key to table "Recipe" on RecipeIngredient.RecipeId = Recipe.Id

A recipe contains a number of ingredients that represent items. The items representing these ingredients could again be produced by a recipe. This creates a structure of recipe's and their ingredients.

At the moment the list of recipes is about 8000, the list of ingredients about 25000. I have written a recursive CTE that returns the tree structure resulting in 144000 rows. A fair amount of recipe's are reused.

The problem : Calculating the total cost.

The cost of a recipe has to be calculated based on the total cost (SUM SellValue) of all the descendant recipes or, when no recipe available for the current item, the SellValue of that particular item.
On stale data, traversing through a CTE with a cursor, these values could easily be updated, though this is not an option IMHO. The reason for this is that the SellValue of an item is updated VERY frequently, read constantly. An update of a certain item would cause numerous recipe's to have their pricing recalculated (up to 100 recipe's).

I have tried using recursive triggers, even though i hate em, on Item and Recipe updating the recipe based on its descendants, to no good effect.

Apparently my knowledge of SQL, plus searching Google for quite a while, didn't provide a feasible solution.

Please help, i'm pulling out the few hairs i have left... ;)

UPDATE 1:

The use of nested/recursive triggers on the recipe table led to a common problem, deadlocks. For now i'll investigate the possibility of using a recursive CTE with SUMS on the respective values.


texts: Complex constraint across all data in a table

texts:
We have a table to record processing that occurs on a system and we need to ensure that only a single row has an 'in process' status.

I want to ensure that the result of this is always either zero or one:

select count(id) from jobs where status in ('P', 'G');

We're using explicit transactions so ideally this check would happen on commit and abort the transaction if the invariant doesn't hold. Handling any racy processing that throws the occasional error is much easier for us to deal with than suddenly ending up with more than one job that is 'in process'.

The solution only needs to work with Postgres so we're quite happy to take a non-standard solution to this. We're currently on 8.4, but we'll be upgrading at some point to 9.x if that makes any difference.


texts:
My MariaDB mysql server doesn't start with error 1067
This is the error log:

140222  0:43:44 InnoDB: The InnoDB memory heap is disabled
140222  0:43:44 InnoDB: Mutexes and rw_locks use Windows interlocked functions
140222  0:43:44 InnoDB: Compressed tables use zlib 1.2.3
140222  0:43:44 InnoDB: CPU does not support crc32 instructions
140222  0:43:44 InnoDB: Initializing buffer pool, size = 3.9G
140222  0:43:44 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file .\ib_logfile0 is of different size 52428800 bytes
InnoDB: than specified in the .cnf file 83886080 bytes!
140222  0:43:44 [ERROR] Plugin 'InnoDB' init function returned error.
140222  0:43:44 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140222  0:43:44 [Note] Plugin 'FEEDBACK' is disabled.
140222  0:43:44 [ERROR] Unknown/unsupported storage engine: innodb
140222  0:43:44 [ERROR] Aborting

Anyone know why?


texts: Home rolled gridview search results shows rows that are supposed to be ignored

texts:
Note: I tried posting this on Stack Overflow but didn't get any good bites.  I'm still researching this but wanted to see if any db czars can assist here.

I'm in the process of trying to troubleshoot some SQL 2012 code in an ASP/C# gridview search that I rolled myself.  I have a search box that searches on specific fields but the twist here is that I only want results after a certain row.

In this database I have a sequence command that autogenerates a number in a column called 'record_number' for every entry in this table and only want results after record #500.  The entries are created manually via a different page.

Right now my search code is as follows:

"SelectCommand="SELECT [record_number], [column_a], [column_b], [column_c],
    [column_d], [column_e], [column_f], [column_g], [column_h],
    [column_i], [column_j]
  FROM [schema].[table_name] WHERE record_number >= 500
  AND ([column_a] LIKE '%' + @column_a + '%')
   OR ([column_c] LIKE '%' + @column_c + '%')
   OR ([column_e] LIKE '%' + @column_e + '%')
   OR ([column_g] LIKE '%' + @column_g + '%')
   OR ([column_h] LIKE '%' + @column_h + '%')
   OR ([column_j] LIKE '%' + @column_j + '%')"

I can post my SelectParameters if needed or anything else.  The challenge I'm facing is when I run a search for just the letter 'c' leveraging this code, the results that post have a record_number less AND greater than than 500. At this point I'm spinning my wheels as I'm not seeing where the problem lies.

If more information is needed by the masses, please let me know.

EDIT

Forgot this part - I've tried moving around the commands and consolidating the commands but no go.


texts:
Related to my MySQL issue

I have a user with USAGE grant in mysql.user, but in mysql.db this user has Select, Insert, Update, Delete. Thus the user can successfully query the database.

I can't find any information about how this mysql.db works, is it like some kind of cached permission in there ? Will a mysqld restart flush it ?

mysql> show grants for user@'xx.xx.xx.%';
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for user@xx.xx.xx.%                                      |
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'xx.xx.xx.%' IDENTIFIED BY PASSWORD 'xxx' REQUIRE SSL |

 mysql> select host,db,user, select_priv,update_priv,delete_priv  from
 mysql.db where  user='user';
 | host         | db   | user    | select_priv | update_priv |  delete_priv |
 | xx.xx.xx.1   | myDB | user    | Y           | Y           | Y


texts: Is there performance difference between "SET ROWCOUNT" and "TOP"?

texts:
I need to select batches of N rows from a large table.

I know of two ways of doing this:

SET ROWCOUNT

SET ROWCOUNT ####
SELECT something FROM table WHERE clauses
SET ROWCOUNT 0 -- rowcount is only needed for the one query above.

TOP

 SELECT TOP ### something FROM table WHERE clauses

Is there any performance difference (or any other reason to pick one over another) between the two solutions?

Notes:

If it matters, Sybase ASE is version 15.
The query is a single SELECT statement. The limit will not apply to any other statements (so usual reasons to pick rowcount over top don't apply).
The order of the rows does not matter

Research done:

This SO Q&A has accepted answer which claims no difference without any supporting references; and another answer that shows a possible un-measured difference for MS SQL server (which I'm not sure applies to Sybase as the reference is from Microsoft).


texts: How to ignore sql errors in stored procedure ? (Not handle)

texts:
I have a list of records I want to process and the cursor iterates through them from a table. The procedure just exits when it hits an insert statement and it fails. Is there a way I can ignore the errors and continue processing the following records ?

Cursor C1:
for each record:
    insert into table_t1(col1) values ('...');  <-- Insert statement fails for record 'n'
End cursor

I would like for it to not exit out but continue processing from n+1 to the end of cursor.

Any help appreciated.


texts: Can a connection's prepared statements be altered by Postgres's admin functionality?

texts:
I am trying to tamper proof as best as possible a peer to peer application.

I am concerned that the prepared statements will be altered.

Can the Postgres admin alter a connection's prepared statements with built-in functionality?

If so, how best can this be managed?  Randomly looping to unprepare and reprepare?


texts:
Any way to disable FT logs completely? I spent hours googling - no luck.

I get tons of "informational" messages literally every second.

2020-01-01 10:43:16.48 spid33s     Informational: Full-text Auto population initialized for table or indexed view xxx
2020-01-01 10:43:23.48 spid34s     Informational: Full-text Auto population completed for table or indexed view zzz
2020-01-01 10:43:23.48 spid36s     Informational: Full-text Auto population completed for table or indexed view xxx
2020-01-01 10:43:24.64 spid12s     Informational: Full-text Auto population initialized for table or indexed view xxx
2020-01-01 10:43:25.64 spid12s     Informational: Full-text Auto population completed for table or indexed view xxx
2020-01-01 10:43:26.58 spid36s     Informational: Full-text Auto population initialized for table or indexed view xxx
2020-01-01 10:43:26.98 spid17s     Informational: Full-text Auto population initialized for table or indexed view xxx

P.S. My cloud hosting provider charges me for "i/o ops per second" so this is something I want disabled. Also, these logs grow really fast, several gigabytes every week, so I had to write a log maintenance script (rollover + archive etc.)


texts: How to avoid duplicate in mysql INNODB transaction for non index key?

texts:
I have a INNODB table with txnno column without unique index but I have primary key on another column. Now I am inserting transaction from multiple places. And there are some concurrent insertion happening for same txnno.
So Is there it is required to use txnno as unique key or INNODB can have it implicitly.
And there is two type of entry. Form one place, I am using commit/rollback statment but there are some places Where I am not using that.
So how can I manage this without changing much code.
Also what is meant by 'no sessions' in the case of table locking.


texts: Pull entire row per user based on MAX(Column) MySQL

texts:
I am trying to pull whole record from database for the highest "Score" column per user.

TestTable structure

------------------
ID | Name | Score|
------------------
1  | john | 10
2  | mark | 20
3  | ted  | 15
4  | john | 5
5  | ted  | 50
6  | ted  | 70

So when I run

SELECT ID, Name, MAX(Score)
FROM TestTable
GROUP BY Name

I want the query to return entire row for highest score per user.

This is what I am expecting:

2  | mark | 20
1  | john | 10
6  | ted  | 70

But it does not necessarily return correct rows. It always return MAX(Score) but other values are not necessarily from the same row.

This is what I am getting:

1  | john | 20
2  | mark | 10
3  | ted  | 70

How can I pull this properly?
I am using MySQL.

I need it to perform good as this will be a big query pulling a lot of data from big tables.

Please help :)


texts:
Have issue with building a recursive query in Postgres.

Tried with this one but it doesn't seem to work. So table user have user_id and referred_by_user_id. referred_by_user_id can be empty, if it is empty we don't need to go up anymore.

So need to find all referrers above a user, going in dynamical depth. Current implementation with limit is naive, I would assume I can exit as soon as I reach X depth.

WITH RECURSIVE uplines(user_id, referred_by_user_id) AS (
     SELECT t.user_id,  t.referred_by_user_id
  FROM postgres.user AS t
  WHERE t.user_id = $userId
  UNION ALL
  SELECT t.user_id, t.referred_by_user_id
  FROM postgres.user AS t JOIN uplines AS a ON t.user_id = a.referred_by_user_id
)
SELECT uplines.user_id FROM uplines LIMIT $depth;

Have read bunch of SO posts about it but still cannot get it to work.


texts: SQL - Many-to-Many relationship with same table and with relationship constraint

texts:
I have a SellerProduct table. Each row within the table represents product information as offered by a seller. The SellerProduct table has the following columns:

id (serial, pk)
productName (nvarchar(50))
productDescription (ntext)
productPrice (decimal(10,2))
sellerId (int, fk to Seller table)

A product may be the same across sellers, but the productName, productDescription and productPrice can vary per seller.

For example, consider the product TI-89. Seller A may have the the following information for the product:

productName = TI-89 Graphing Calc
productDescription = A graphing calculator that...
productPrice 65.12

Seller B may have the the following information for the product:

productName = Texas Instrument's 89 Calculator
productDescription = Feature graphing capabilities...
productPrice 66.50

Admin users will be required to identify that products are the same across various sellers.

I need a way to capture this information (i.e. products are the same across sellers). I could create another table called SellerProductMapper as follows:

sellerProductId1 (int, pk, fk to SellerProdcut table)
sellerProductId2 (int, pk, fk to SellerProdcut table)

The problem with this approach is that it permits sellerProductId1 and sellerProductId2 to be from the same seller for a given row. That should not be allowed.

How can I capture this many-to-many relationship while enforcing this constraint?


texts: How to ensure backups are successfully persisted to S3 when using Ola Hallengren solution on AWS EC2 Instances

texts:
Improving Existing Backup Approach By Using Ola Hallengren Backup Solution
Deploying a new backup approach to eliminate dependency on a vendor tool (Cloudberry) that I've leveraged before. In my microblog post, I describe the process I'm taking which includes:

Creating and deploying all the maintenance solution via dbatools
Leveraging s5cmd as a sync tool to ensure backups get copied from EBS volume to S3. (performant alternative to installing aws-cli).
Using Red Gate SQL Monitor/PagerDuty integrations for monitoring and alerting on issues.

Painpoint - Replacing Existing Tool
The question is marked for AWS EC2 specifically as much of this is solved in Azure with the ability to backup to Azure Blob storage supported by SQL Server.
Cloudberry is a great budget oriented tool that allows you to use a GUI to schedule backups that automatically get pushed to S3. It's a pretty good budget solution, but I've seen serious scaling issues once you deal with thousands of databases. At this point, the local sqlite database and S3 metadata queries seem to cause slow-downs and impact the server RPO.
I've evaluated other tooling, but very few of them were flexible with S3 uploads as a native feature.
An eye towards reliability
As I've worked through the final steps I'm looking for points for failure and reliability concerns.
One area that becomes a bit less easy to validate is the successful sync of all contents to S3.
While the agent should fail with an exit code, knowing all files did get up to S3 is important.
How to Gain Assurance of All Files Persisted To S3
Here's a few questions specifically that I'm trying to work through and would appreciate any insight on.

Can EBS Snapshots replace the need to sync to s3 with a goal of 15 min RPO on a dedicated backup volume? I'm not including an data files/log files in this, so it would purely a backup drive.

Based on some prior tests I believe a shorter interval is difficult to get on EBS.

S5cmd performs very well, but isn't as controlled as doing this with a dedicated PowerShell script. For instance, simply iterating through the S3 files to perhaps generate a diff report at the end would take 8 seconds along on s5cmd and 43 seconds getting it via AWS PowerShell tools. With this running every 15 minutes I want as quick and minimal of a performance impact as possible to the server, not run a lot of custom scripts beyond this.
Is there any approach you'd take to audit the local directory of backups against S3 can validate nothing locally is missing or is there where relying on the sync tool just has to be done.
Any usage of AWS Backup, Data Sync, or other tooling natively integrated in AWS that could solve these issues?  FSx, DataSync, and others seem to add more risk and complexity to manage.

Other Solutions

I've considered dbatools based backups in the past, as I could gain full control of running the backup, pushing to s3, and logging all of this with more control. However, after deliberation and community discussion I decided against this as I felt the use-case for dbatools was more for adhoc backups and that leveraging Ola Hallengren Backup tooling was a more robust solution for production usage. The negative to using it is that my PowerShell error handling and logging isn't going to be implemented.

I look forward to any insight and appreciate the help


texts: When should I mark a column as the primary key?

texts:
I have a table like this:

// cookies
+----+---------+-------------------+------------+
| id | user_id |       token       |   expire   |
+----+---------+-------------------+------------+
| 1  | 32423   | dki3j4rf9u3e40... | 1467586386 |
| 2  | 65734   | erhj5473fv34gv... | 1467586521 |
| 3  | 21432   | 8u34ijf34t43gf... | 1467586640 |
+----+---------+-------------------+------------+

A few days that I think about it. I guess I don't need to id column. Currently id column is PK and also I have an unique-index on token column to make it fast for searching.

Now I want to know, can I remove id column and make token column as PK? Is that normal?

To be honest, I've never created a table without id column (It's always been the PK), So that's weird for me to choose token column as the PK.


texts: How do I convert between the transaction ID formats in fn_dblog() and sys.dm_tran_database_transactions?

texts:
fn_dblog() shows transaction IDs in this format:

0000:00049d43

And sys.dm_tran_database_transactions shows transaction IDs in this format:

9811233

I believe the above examples show the same transaction ID in different formats.

Is there a way to convert between the two formats? Or are these actually two different entities -- and in that case, is there a way of matching up fn_dblog() with the transaction-related DMVs?


texts:
I wish to use a mysql trigger that will update a column called peak to 0 or 1 based on if the CURTIME() is between 08:01 & 11:59.

Can anyone help with this trigger?


texts: After a point-in-time restore, is there any way I can get the transactions during restore time?

texts:
The hypotethical scenario:

Imagine that I have to do a point-in-time restore of a Azure SQL database, the original one is called DatabaseA and the restored is after restore called DatabaseA_restored.

During the time of restoring DatabaseA_restored there are transactions happening in DatabaseA (inserts, updates, deletes) because we cannot afford the downtime.

Is there anyway I can now cherry pick transactions/table changes and apply them to DatabaseA_restored so that I do not loose the relevant difference during restore?


texts:
I've solved, or I think I have, two book problems an 3NF, but I'm still a bit unsure about my understanding of it all and want to be sure I did it correctly.

For the first one I have

R(ABCD)

and FDs:

AB->C
BC->D
CD->A
AD->B

As far as I can tell these are all superkeys, and thus this is in 3NF as well as BCNF.

The second was:

R(ABCDE)

AB->C
C->D
D->B
D->E

The first being a superkey, and the rest not, so I have to find candidate keys and check if any RHS fields are in them making the FDs valid.

So I found AB AC and AD as candidates. That leaves only D->E in violation.

so..

R1 = D+ = DEB with all FDs that apply to it being keys so this is in 3NF and I think also BCNF.

R2 = R - (D+ - D) = ACD with the applicable FD being c->D which is not a key. and now for this D is not in a candidate key so this is not 3NF??

then I did:

R3 = C+ = CD   two elements so BCNF and 3NF
R4 = R2 - (C+ - C) = ACD - D = AC  same as above, so all good.

Final output being:

{DEB}, {CD}, {AC}.

Is this all correct?

Also, is it possible to have a something in 3NF that isn't in BCNF? From what I know it doesn't seem like it but I want to be sure.


texts: How do I get article properties from query in a merge replication

texts:
I would like to know if there are any queries to get article properties in a SQL Server merge replication. I'm able to get properties only by GUI. I select a publication properties then I get the page properties, then I select an article (a table) then if I want to check the Article Properties, I select Set Properties of Highlighted Table Article I get the four group of properties like as Copy Objects and Settings to Subscriber, Destination Object, Identification and Merging Changes.
Anybody knows if there are any queries to get that properties or to get a script that create the article with of all of these properties?


texts: SQL to Find Partial Duplicates of a Field

texts:
I have a table with a field for a member number. The table can contain records with duplicate values for this field and does.

I have a SQL query that will obtain any duplicate values for this field, however, I would now like to find records where there is another record with the same member number but only the first 8 digits out of the 9 that there are.

For example, I would like to return the below when the query is run;

MEMBNO
123456789
123456782

At the moment, my query only returns;

MEMBNO
123456789

where there are two or more records with this exact number.

Current query is;

SELECT
  basic.MEMBNO
FROM
  basic
GROUP BY
  basic.MEMBNO
HAVING
  COUNT(basic.MEMBNO) >1
ORDER BY
  basic.MEMBNO

A query I thought might work was;

SELECT
  basic.MEMBNO
FROM
  basic
GROUP BY
  basic.MEMBNO
HAVING
  COUNT(LEFT(basic.MEMBNO,8)) >1
ORDER BY
  basic.MEMBNO

I'm faily new to SQL and databases in general, so apologies if I haven't explained myself that well. Does it require the LIKE operator somewhere?

I'm using Microsoft SQL Server Report Builder 3.0.


texts: Is it possible to force a query to ignore the server option "remote proc transaction promotion" so it doesn't promote remote proc transaction?

texts:
I have a specific query that executes a stored procedure on a remote linked server and inserts the results into a local temp table.

I don't want it to promote a remote procedure transaction but I can't re-configure the server.

Is there a query hint or some way to force it to not promote a remote procedure transactions?

Edit: The reason I ask is because I'm getting the following error when trying to execute my remote procedure call:

  OLE DB provider "SQLNCLI11" for linked server "MyRemotelyLinkedServer" returned
  message "The transaction manager has disabled its support for
  remote/network transactions.". Msg 7391, Level 16, State 2, Procedure
  spMyRemoteProcedure, Line 64 [Batch
  Start Line 15] The operation could not be performed because OLE DB
  provider "SQLNCLI11" for linked server "MyRemotelyLinkedServer" was unable to
  begin a distributed transaction.

I know I can set "Enable Promotion of Distributed Transactions" to false on the Linked Server options to fix this, but I'm not allowed to make server option changes in this context, so I'm looking for a workaround.


texts: MASSIVE Identity column value jump in SQL Server 2014

texts:
So in the middle of basic coding and testing we saw a huge non-patterned jump in Identity values for multiple tables. We are unaware of any server blips or attempted bulk operations, but DBAs are looking into logs.
The gaps are not the typical 1,000 or 10,000 seen with server restarts and such.
The gap for Application_NO is 10,410,345 for a table with 2,320 rows
Transaction_Payment_NO jumped an astonishing 1,712,149,313 for a table with 685 records.
Any ideas on what could be causing such large and seemingly arbitrary jumps?


texts:
We have a dedicated MySQL server with 16GB of RAM that's serving 8 web workers with relatively high traffic (over 1000 requests per second at peak traffic)

At peak we're seeing the following errors in the web server's error logs:

SQLSTATE[HY000] [1135] Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

Here's a pastebin of my.cnf

I've also uploaded some graphs from collectd to an imgur album as well as a screenshot from mysqlcalculator.com with my settings (you might want to open them in a new window because the background is transparent and the text isn't very visible on imgur's dark background)

As you can see, very little memory is actually used (under 6GB if you exclude cached). mysqlmemorycalculator says 10GB.


texts:
Relating to mongodb's documentation, database commands such as count will be logged as component COMMAND. However neither db.count() nor db.runCommand({count:'mycollection'}) produces a COMMAND log entry, even if the default log level for COMMAND is raised from 0 to 1.
Dropping an index, a collection or a database produces a COMMAND log entry even with the default log level 0 though.

Moreover, it seems that almost all mongodb actions can be expressed as command (even a find), as shown in mongodb's documentation, so I wonder how one could properly differentiate between QUERY which logs messages related to queries and COMMANDs.

My goal is to filter out (administrative) commands from the logs for auditing (and I don't want to use mongodb's enterprise version with auditing functionalities for this single purpose).
I'm using mongodb version 4.0.5 Linux 64Bit.

Thanks for shedding light on this!


texts: Changing numeric type slow down query big time for postgres

texts:
I have 3 tables, leads, lead_addresses and addresses. Scheme looks like this:

recently we decided that foreign keys would be beneficial for us so we changed data types for lead_id and address_id from numeric to integer in lead_addresses table (in scheme named LeadAddresses). Reason for data type change is because foreign keys cannot be created between id which is integer and lead_id for numeric. Same for address attributes.

All tables mentioned have 10k+ records.

Problem:

We have query which works with numeric types well, but with integer types takes 20s+. What it does, its selects all leads and join addresses (only one address per lead, the last address created).

EXPLAIN ANALYZE
SELECT
  lead.id,
  lead.updated_at,
  lead.state,
  CONCAT(address.street, ', ', address.city, ', ', address.state) as address
FROM leads AS lead
LEFT JOIN lead_addresses lead_address ON (
  lead_address.lead_id = lead.id AND
  lead_address.id = (
     SELECT id FROM lead_addresses WHERE lead_id = lead.id ORDER BY id desc LIMIT 1
  )
)
LEFT JOIN addresses address ON (address.id = lead_address.address_id)
WHERE (lead.state = 1)
ORDER BY "lead"."updated_at"DESC
LIMIT 15000

We have original database which is in production and execution is fast. Here is result of explain: https://pastebin.com/raw/F6XaKNzp

We made exact clone of same database got rid of indexes on LeadAddresses
and changed types from numeric to integer, build indexes again.

Here is result of explain again: https://pastebin.com/raw/LhAyJ4ZQ
this time it takes too long.

I noticed the subplan being executed for each row, on the other hand in old production database its fast since subplan is executed once.

Can anybody give me advice on how to make planner execute subplan only once? how to change datatype of tables correctly?

Here are indexes:

Order of operations to create test db (the one where query is slow):

1) we made clone of database from old production

2) we executed query. Query was fast same as in production database

3) we dropped indexes on join table (LeadAddresses)

4) we changed numeric types to integer

5) we build same indexes again

6) query is still slow :'(

I'll be glad for any advice. Thanks.


texts: How can I speed up a query that orders by a calculated field?

texts:
I will try and give an example - this is not my table structure - I'm simply trying to outline the issue in order to find a solution...

Person
Id, Name

BrothersNames
Id, Name

SistersNames
Id, Name

PersonBrothers (join table)
PersonId, BrotherNameId

PersonSisters (join table)
PersonId, SisterNameId

OK - so imagine this database holds every person from a small country. The database holds a record of the names of everyone's brothers and sisters (it does not map a person to their brother or sister - just their names) so that we can find out statistics about names.

Obviously lots of names are shared so the join tables normalise this for us.

What I want to do is take one user and find out the number of matches of brother's names and number of matches of sister's names with every other user in the system, then add those two matches together and order by that descending.  So this would give us a list of users who have the most number of brothers and sister's names in common.

I'm really only interested in the top ten matches but I think I have to get the whole result set to work out the top ten matches.

Please note that in my actual data a person can have a million brothers or a milllion sisters.  This is where I'm getting performance issues.

This is how I'm calculating the matches for brothers and I do the same for sisters

select p.id, matches
FROM Person p
LEFT JOIN
        (
            SELECT
            COUNT(*) AS Matches,
            pbn.PersonId
            FROM PersonBrothersNames pbn
            INNER JOIN Brothersnames bn on pbn.BrothernameId =bn.Id
            inner join PersonBrothersName otherpbn on otherpbn.BrothernameId = bn.Id

            WHERE pbn.PersonId= @PersonId and pbn.PersonId <> otherpbn.personid
            GROUP BY  pbn.PersonId

        ) As BrothersNamesJoin ON BrothersNamesJoin.Person = p.Id

Please let me know if I should specify more info...
I am using SQL Server 2008 but is probably platform agnostic..


texts: Why does it take a long time to drop a function based index in Oracle?

texts:
I have a function based index that takes 25 minutes to create on a table of 94 Million rows.

When I drop the index, it takes 18 minutes.

Why does it take so long? I would have thought the drop would have been almost immediate? I have noticed that after ~10 minutes, the index is removed from user_indexes, but the script still executes for another ~8 minutes.

The table is partitioned, and this is an Oracle 10g RAC install. If any more information would help, I can add it as required.

Any light shed on this would be greatly appreciated.


texts: How to shutdown and startup a database to delete datafiles in between

texts:
OS: Windows 10 Professional / DBMS: Oracle 18c XE
How would one approach the issue of losing a connection while shutting down the database?
I would like to have one script which deletes datafilepaths inside the dbms, shuts down the database, deletes the datafiles on OS-level and then starts up the database again and "detects" when its running again to create new tablespaces.
Is there a way to do that?


texts: Machine account causing a Token-based server access validation failed with an infrastructure error

texts:
As the error suggests the SQL log is filling with the error:

Login failed for user 'xxx\xxx$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]

This is a SQL cluster and the account trying to login is the passive node account.  The account doesn't exist in SQL itself so I don't think this is a SID mismatch.

I have checked the ring buffer DMVs which I have placed below, however I don't think this is giving me much.

runtime Notification_Time   ErrorCode   CallingAPIName  APIName SPID    Record Id   Type    Record Time Current Time
2014-10-24 10:22:50.953 2014-10-23 15:38:24.547 0x139F  NLShimImpersonate   ImpersonateSecurityContext  62  18610   RING_BUFFER_SECURITY_ERROR  10966336138 11033802544

I have checked and IIS is not on the passive node so there is no double hop coming from there, and in the services I can see nothing obvious as to what could be causing it.  SQL Browser is set to local service, could that be trying to log in?


texts:
I am reading a tutorial about TOP keyword in T-SQL. But I don't really understand the examples given in the tutorial.

Example 1:

INSERT TOP n INTO table_name1 (Col1, Col2)
SELECT Col3, Col4 FROM table_name2;

The tutorial mentions that the TOP here doesn't really achieve anything since the database doesn't have a concept of order, which I kind of get.

And is it correct to assume that the n rows that are chosen from table_name2 are completely random records?

Example 2:

The tutorial gives another example that improves the first statement.

INSERT INTO table_name1 (Col1, Col2)
  SELECT TOP n  Col3, Col4
  FROM table_name2
Order By Col1;

Here is the confusing part. Putting the TOP keyword inside the SELECT statement is supposed to give us a better control over what rows get chosen and inserted through the ORDER BY clause. So I would assume the ORDER BY clause is part of the SELECT statement in the second example. But Col1 isn't part of table_name2.

This confused the hell out of me.
Thanks for any insight.


texts: overhead for INSERTS using change tracking and/or change data capture (CDC)

texts:
We are going to be implementing a mechanism to track changes in our SQL Server 2008 application database.  The programmers are concerned about INSERT speed being negatively impacted, so I have set out to test three cases: no mechanism to track changes, change tracking, and Change Data Capture (CDC).

In all of my tests, there appears to be no significant difference in the speed of INSERTs.  Does this make sense?  I want to make sure I am not overlooking anything before I remove this concern from our list of requirements.

(Note: my testing has shown differences with UPDATES, but we are only concerned about INSERTS)

EDIT: My tests were relatively simple INSERTS into a production-like table, but 250K of them.  The processes ran over 5 minutes, so I'm comfortable it was enough for our environment.


texts:
I have created procedure for orders table. The procedure checks if the order has shipped, then the customer can returns all/part of the shipped items. Once verified by the procedure and the order has shipped, order details will be replicated in deleted orders table. However, I get an error on the insert whenever I run the program. This is due to duplication - An UPDATE or INSERT statement attempted to insert a duplicate key.
Is there anyway I can insert records for the returned item without showing an error?
My procedure is:

CREATE PROCEDURE RET_ORD(PARAM_ONO ORDERS.ONO%TYPE)
  IS
    CURSOR CUR_RET
  IS
  SELECT * FROM ORDERS
  WHERE ONO = PARAM_ONO;
  V_CUR CUR_RET%ROWTYPE;
BEGIN
  OPEN CUR_RET;
 LOOP
  FETCH CUR_RET INTO V_CUR;
  EXIT WHEN CUR_RET%NOTFOUND;
 IF V_CUR.SHIPPED IS NOT NULL THEN
  INSERT INTO
  DELETED_ORDERS(ONO,CNO,ENO,ENAME,ORD_DE_D,STATUS)
  VALUES(V_CUR.ONO,V_CUR.CNO,V_CUR.ENO,USER,,SYSDATE,'RETURNED');
  DELETE FROM ODETAILS
  WHERE ONO = P_ONO;
 ELSIF V_CUR.SHIPPED IS NULL THEN
  DBMS_OUTPUT.PUT_LINE('CANNOT PROCESS ORDER '|| V_CUR.ONO);
  END IF;
 END LOOP;
 CLOSE CUR_RET;
END;


texts: Why does setting my column as Always Encrypted change its collation from SQL_Latin1_General_CP1_CI_AS to Latin1_General_BIN2?

texts:
I am encrypting a VARCHAR(50) NULL column as randomized. When doing this, the collation is changed from SQL_Latin1_General_CP1_CI_AS to Latin1_General_BIN2.

I cannot find any resources explaining why this happens, and I want to understand what the purpose of the collation change is.


texts: Error during SQL Server 2008 R2 SP3 upgrade: MODIFY FILE failed. File 'MSDBLog' does not exist

texts:
I have just installed SQL Server 2008 R2 SP3 and after installing it and trying to start the SQL Server instance again, it won't come online. In the SQL Server error log I can see that the following is logged:

2014-10-20 16:51:13.04 spid8s      MODIFY FILE failed. File 'MSDBLog' does not exist.
2014-10-20 16:51:13.04 spid8s      Error: 912, Severity: 21, State: 2.
2014-10-20 16:51:13.04 spid8s      Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2014-10-20 16:51:13.04 spid8s      Error: 3417, Severity: 21, State: 3.
2014-10-20 16:51:13.04 spid8s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2014-10-20 16:51:13.04 spid8s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

How can I fix the upgrade so it doesn't fail?