MySQL: Block ANALYZE TABLE Queries Is there a way to revoke access for ANALYZE TABLE which is granted by needed Insert privilege?
I have an application that throw multiple ANALYZE TABLE queries per second. And am looking for a way to decline these queries from the DB side.
Update
It can't be blocked from the app side, since tracing which part of the code is doing so will cost days searching between millions line of code.

The answer is no, ANALYZE TABLE privilege is implicit if the user has SELECT and INSERT privilege on the table. There's no way to use access privileges alone to revoke privilege to run ANALYZE TABLE.
Are you sure the client is explicitly running ANALYZE TABLE statements? It's possible they are simply running SHOW TABLE STATUS or quering INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS. If so, then this might help:
SET GLOBAL innodb_stats_on_metadata=OFF;

For InnoDB tables, the work done by ANALYZE TABLE is identical to those metadata queries. Querying metadata triggers the resampling of table statistics, which can be a burden if it's done too frequently. Turning off the automatic update of statistics on any metadata query is useful. We disable that on all our MySQL instances at my company.
I recall years ago when phpMyAdmin added queries to the INFORMATION_SCHEMA to discover metadata, it caused some large hosting providers to crash, because hundreds of users at a time were viewing their databases in phpMyAdmin.
I also ran into this when I developed the database component for the Zend Framework 1.0. I found a workaround: use DESCRIBE instead of INFORMATION_SCHEMA queries. DESCRIBE doesn't trigger the update of table statistics.
If the client is really running the literal statement ANALYZE TABLE, and you can't change the client code (which would be the obvious solution), my only other suggestion is to run an SQL proxy in between the client and the database, and filter out ANALYZE TABLE statements.
For example, here's a blog describing use of ProxySQL to filter out offending queries:
https://www.percona.com/blog/2016/08/23/how-to-stop-an-offending-query-with-proxysql/


Is there any way to clear all tables using Liquibase? I'm currently using Liquibase for creating test data in my database before each test run.

I came across a problem where some of the test cases need special data, while others need the tables to be empty.

So I decided to give Liquibase contexts a try and only insert the data I really need for the current testcase plus - in an abstract superclass - all the data every test case needs (user accounts and stuff)...

I would like to clear the tables before each run, however I can't seem to find anything for this case except dropAll(), which seems to be overkill...

I'm not quite sure if it is even possible - because of foreign keys and other constraints - but I wonder if there is a way to delete the content of ALL the tables without dropping them completely?

I guess the fastest way would be to use a custom sql tag that uses  truncate ... cascade on all "top-level" tables.

Using the cascade keyword for truncate will also (recursively) delete all rows from all dependent tables (all tables referencing the one that is being truncated).

Something like:

<sql>
   truncate table parent_1, parent_2, foobar cascade;
</sql>

Don't know if that is feasible with the number of tables you have or not.


Replicating PostgreSQL data into Citus/Greenplum? I need to integrate data from 3 different PostgreSQL databases (OLTP application backends) in a data warehouse. For the data warehouse itself I consider using Citus or Greenplum. There is a requirement that the data from applications has to be synced with the data warehouse as close to real time as possible (everything above 3-5 minutes delay is unacceptable, real time replication would be the best). In this regard I have the following questions:

Will Postgres logical replication work with Citus? Citus is a Postgres extension, can you treat a Citus cluster as an ordinary Postgres database? If yes, then logical replication should theoretically work, but how does it deal with distributed tables?
Greenplum is a Postgres fork, so will Postgres logical replication work with it at all? I have also read that Greenplum is not optimized for OLTP workloads, does that mean it will break when I try to ingest OLTP data into it?
If logical replication does not work with Citus/Greenplum, then how to stream data from Postgres? Do I need to stream logical-level WAL into Kafka and then write custom logic for translating it into SQL statements on the target database? Are there any tools for that?

Bonus question: does anyone have experience with both Citus and Greenplum, especially with their SQL limitations? I know that Citus does not fully support correlated subqueries and recursive CTEs, does Greenplum have any similar limitations?
I would appreciate any help with these questions, I tried googling but there is little or no info on the subject, could you please give at least some direction?

After some research I've managed to come up with an answer. Kudos to @a_horse_with_no_name for pointing me in the right direction.
Real-time sync between different data sources can be done using Kafka Connect.
Kafka Connect allows to send data from various systems to a Kafka cluster via Source Connectors and read data from Kafka using Sink Connectors. All the platform-specific code for getting data from a system to Kafka or transferring data from Kafka to a system is abstracted away by the maintainers of the specific connector.
Connector maintainers usually will respect some kind of a "contract" between Sinks and Sources, i.e. they will agree in advance on a normalized message format, which allows to use disparate Sources and Sinks (e.g. you can have a PostgreSQL Source and Elasticsearch & Neo4J Sinks in parallel, despite the fact they have completely different paradigms).
For the case of syncing Postgres with Citus/Greenplum, it should be possible to do via Debezium PostgreSQL Source Connector and JDBC Sink connector (both Citus and Greenplum have JDBC support).


jsonb_each_text(jsonb) returns jsonb, not text According to docs , jsonb_each_text(jsonb) returns

  setof key text, value text

but if you pg_typeof(value) on result it shows jsonb, Eg:

SELECT *, pg_typeof(key) AS type_key, pg_typeof(value) AS type_value
FROM jsonb_each('{"2": "-200.08"}');

 key |   value   | type_key | type_value
-----+-----------+----------+------------
 2   | "-200.08" | text     | jsonb

UPDATE:
I have mistaken docs for jsonb_each_text instead of jsonb_each

jsonb_each is not jsonb_each_text

No, you're calling jsonb_each and you're referencing the docs for jsonb_each_text It shows

On \dfS json_each_text,

      Name       | Result data type |              Argument data types              |
-----------------+------------------+-----------------------------------------------+
 jsonb_each_text | SETOF record     | from_json jsonb, OUT key text, OUT value text
 jsonb_each      | SETOF record     | from_json jsonb, OUT key text, OUT value jsonb

Making that adjustment to call jsonb_each_text

SELECT *, pg_typeof(key) AS type_key, pg_typeof(value) AS type_value
FROM jsonb_each_text('{"2": "-200.08"}');

 key |  value  | type_key | type_value
-----+---------+----------+------------
 2   | -200.08 | text     | text


From a user table check if one of them exists on a table I'm developing a database with SQLServer 2012 SP2.

I have these tables:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [...],
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC)
)

CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    [Created]       NVARCHAR (50) NOT NULL,
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)

CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId]         INT NOT NULL,
    [Position]              INT NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    CONSTRAINT [FK_AggregationChildren_Aggregation]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE
)

I also have this user type:

CREATE TYPE [dbo].[SerialList]
AS TABLE
(
    Serial nvarchar(20)
);

I use this type as a parameter for a Stored Procedure. I use it to pass a list of Serials.

The stored procedure prototype is:

CREATE PROCEDURE [dbo].[AddSerials]
      @serials as dbo.SerialList READONLY
    , @parentCode nvarchar(20)
    , @isAuto bit

I need to loop @serials to check if all of them are in AggregationChildren table. I know that I can do it using a CURSOR but I wondering if I can do it using something faster than a CURSOR that need to loop over all of the @serials rows.

If need something than return 0 if one (or more) of the Serial in @serials don't exist on AggregationChildren table.

Any idea?

Assuming my reading of the schema is correct, this should do what you want:

create procedure dbo.AddSerials (
      @serials as dbo.SerialList readonly
    , @parentCode nvarchar(20)
    , @isAuto bit
    ) as
begin;
  set nocount on;
  declare @AggregationId int;
  select @AggregationId = CodeId
    from dbo.[Code] c
    where c.Serial = @parentCode;
  if @AggregationId is null
    begin;
      -- if the parent doesn't exist, children don't exist
      return 0;
    end;
  -- parent exists, check children
  if exists (
    select 1
      from @serials s
      where not exists (
        select 1
          from dbo.AggregationChildren as ac
            inner join dbo.[Code] as acc
              on ac.AggregationChildrenId = acc.CodeId
                and ac.AggregationId = @AggregationId
          where acc.Serial = s.Serial
        )
    )
    begin;
      -- at least one @serials does not exist under parent
      return 0;
    end;
    else
    begin;
      -- all of the @serials exist under parent
      return 1;
    end;
end;

If you want to skip checking the parent code, this would also work:

create procedure dbo.AddSerials (
      @serials as dbo.SerialList readonly
    , @parentCode nvarchar(20)
    , @isAuto bit
    ) as
begin;
  set nocount on;
  if exists (
    select 1
      from @serials s
      where not exists (
        select 1
          from dbo.[Code] as ac
            inner join dbo.Aggregation as a
              on ac.CodeId = a.AggregationId
                and ac.Serial = @parentCode
            inner join dbo.AggregationChildren as ac
              on a.AggregationId = ac.AggregationId
            inner join dbo.[Code] as acc
              on ac.AggregationChildrenId = acc.CodeId
          where acc.Serial = s.Serial
        )
    )
    begin;
      -- at least one @serials does not exist under parent
      return 0;
    end;
    else
    begin;
      -- all of the @serials exist under parent
      return 1;
    end;
end;


Does page verification mode affect DBCC CHECKDB behavior? Does DB page verification mode (torn page vs checksum) affect DBCC CHECKDB behavior?  Have some large (TBs) older db's still with TORN page detection mode and curious if check db works any better / worse / slower / faster based upon page verification mode.

It is highly recommended to set a database to use Page Verify CHECKSUM.The Checksum is safer and its performance overhead is still small enough.
As discussed in this article.

When changing a database to use CHECKSUM it is important to know that
the option isn’t used for pages that have already been written to disk
and as such it will only be applied to new pages are written or when
existing pages are modified and re-written to the IO subsystem.
A common misconception regarding the CHECKSUM option is that it
replaces the need for consistency checking databases. This is a very
incorrect assumption; as we’ve seen the CHECKSUM error is only
reported on the read operation of an inconsistent page, if the page
isn’t read the error isn’t reported. However the biggest reason is
that the DBCC CHECKDB routine performs a lot more thorough checking at
the database level and includes checks that a CHECKSUM simply doesn’t
cover, therefore the best practice is to combine CHECKSUM page
verification and regular consistency checks.


SQL Server using index scan instead of seek when updating a view that has an INSTEAD OF UPDATE trigger via a cursor I have a 3rd party application which uses a cursor that updates rows in my SQL Server 2014 table. I am unable to modify this code and have therefore devised a scheme to point the cursor to a view which has an INSTEAD OF UPDATE trigger where I can intercept and fully control the update. I am very aware that cursors are evil, but I am forced to work with them because I cannot modify the source programs.

When the UPDATE...WHERE CURRENT OF statement executes, it's performing a clustered index scan rather than a seek to locate the record that the cursor is currently pointing at. I cannot determine why the optimizer is doing a scan when it could be doing a seek. I believe that it's a combination of cursor + view + instead of update trigger that is causing the problem, because if I remove any one of these 3 variables from my tests, the index seek is used properly.

Note that it doesn't matter how many rows are in the table; the optimizer always uses a scan, according to the execution plan. I've even removed all of the logic within the trigger to further simplify the test.

Here's some simple code to recreate the issue:

CREATE TABLE [dbo].[Person](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Name] [varchar](40) NULL,
  CONSTRAINT [PK_Person_Id] PRIMARY KEY CLUSTERED ( [Id] ASC )
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
  ON [PRIMARY]
GO

CREATE VIEW [dbo].[vPerson]
AS SELECT Id, Name FROM dbo.Person
GO

CREATE TRIGGER [dbo].[InsteadOfUpdate_vPerson] ON [dbo].[vPerson]
INSTEAD OF UPDATE
AS
BEGIN
  SET NOCOUNT ON
END
GO

INSERT INTO Person VALUES('John Doe');
GO

--This will use a seek
UPDATE dbo.vPerson
SET Name = 'Jane Doe'
WHERE Id = 1
GO

--This will not use a seek
DECLARE c CURSOR FOR
SELECT * FROM dbo.vPerson
WHERE Id = 1
FOR UPDATE

OPEN c
FETCH NEXT FROM c

UPDATE dbo.vPerson
SET Name = 'Jane Doe'
WHERE CURRENT OF c
CLOSE c
DEALLOCATE c
GO

Any help or suggestions on things to try would be greatly appreciated.

This looks like an oversight.

The optimizer cannot generate an apply-style indexed loops join when the update is performed using WHERE CURRENT OF and the target view (schema-bound or not) has a T-SQL instead-of update trigger, regardless of the number of rows, or any other considerations:

That shows an example with almost 20,000 rows in the table (copied from the AdventureWorks' Person table, as it happens).

The join predicate gets 'stuck' on the nested loops join operator itself, rather than being pushed into the inner side to produce a seek:

Since you cannot change the code, you should report this as a bug through your normal Microsoft Support channel. You may also report the bug on Connect, but the chances are much lower of getting a quick response or fix via that route.

Just for interest's sake, the plan you are after is possible using an API cursor positioned update (the most analogous operation internally):

DECLARE
    @cur integer,
    @scrollopt integer = 2 | 8192 | 32768 | 131072, -- DYNAMIC | AUTO_FETCH | CHECK_ACCEPTED_TYPES | DYNAMIC_ACCEPTABLE
    @ccopt integer = 2 | 32768 | 131072, -- SCROLL_LOCKS | CHECK_ACCEPTED_OPTS | SCROLL_LOCKS_ACCEPTABLE
    @rowcount integer = 1;

-- Open the cursor
EXECUTE sys.sp_cursoropen
    @cur OUTPUT,
    N'
    SELECT * FROM dbo.vPerson WHERE Id = 1;
    ',
    @scrollopt OUTPUT,
    @ccopt OUTPUT,
    @rowcount OUTPUT;

-- Request a positioned update
EXECUTE sys.sp_cursor
    @cur,
    1, -- UPDATE
    1, -- row number in buffer
    'dbo.vPerson', -- table (unambiguous in this case)
    'Name=''Banana'''; -- new value

-- Close
EXECUTE sys.sp_cursorclose -1;

The execution plan is:

Notice the index seek on Person (the predicate is not 'stuck'):

This is not a workaround for you since you cannot change the source query. There's no way to hint or plan guide your way around your issue; the optimizer simply cannot generate the seek plan you expect in your specific case. Attempting e.g. a FORCESEEK hint simply results in an error message saying the optimizer could not produce an execution plan.


Deleting old files in /var/lib/postgresql/9.x/main after changing postgresql data_directory Very recently, I changed the data_directory of posgresql (9.3.10) to point to a new data disk I recently added to my VM (Ubuntu 14.04 OS). All the old data has been transferred to this new disk as well.

Prior to this shift, the data_directory was residing in the disk that had the OS installed too. This was a digestible arrangement initially, but ultimately the data_directory almost came to fill the entire 30GB space available. Thus I made the shift.

My question is: can I delete the contents of the old var/lib/postgresql/9.x/main/ folder now that data_directory is entirely pointing to a new location?

The old files are still intact, taking a ton of space, and the only reason I haven't deleted them is because other elements in the postgresql config (or some daemon scripts) might be referring to this folder structure somehow. I'm not sure. Someone with experience can perhaps help out here.

There shouldn't be any references as all the scripts/utils/etc use the data directory environment variable.

the only place I would look is in the new configuration file - take a look you don't have any includes that point to the old folder (not sure at what version they added this option.)

BTW, it's always a good practice to rename (mv dir dir_bck) the folder for a week to see you really don't have anything trying to access there.
(you shouldn't anyway as it's stale data...)

Regards,
 - Jony


How can I change default location for tablespaces In Oracle, how can I find out where datafiles for a tablespace are created by default if I do not provide a full path for the datafile but just the name?
And how can I change this default location?

show parameter db_create_file_dest will point to default datafile location.

To change default location execute this command

alter system set db_create_file_dest='target_dir' scope=both;

(change is immediate no need to bounce the database)


UNION ALL date on the same row I am unioning multiple tables together in Postgres, where the timestamp is slightly different, but the same if I round to the hour. Is it possible to make each union show on the same row, if the date is the same? Currently I'm getting duplicate rows of each date.

select date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp),
COALESCE(count(*), 0) as "1489",
null as "1616"
from actions
where group_id = 1489
and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
group by 1
union all
select date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp),
null as "1489",
COALESCE(count(*), 0) as "1616"
from actions
where group_id = 1616
and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
group by 1
order by 1

It returns:

You can use FULL JOIN like this:

SELECT
    COALESCE(a1.t, a2.t) as t,
    a1."1489",
    a2."1616"
FROM
(
    select
        date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp) as t,
        COALESCE(count(*), 0) as "1489"
    from actions
    where group_id = 1489
        and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
    group by 1
) as a1
FULL JOIN
(
    select
        date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp) as t,
        COALESCE(count(*), 0) as "1616"
    from actions
    where group_id = 1616
        and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
    group by 1
) as a2 ON (a1.t=a2.t)
ORDER BY 1;

Or just use CASE:

SELECT
    date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp),
    SUM(CASE WHEN group_id=1489 THEN 1 ELSE 0 END) as "1489",
    SUM(CASE WHEN group_id=1616 THEN 1 ELSE 0 END) as "1616"
FROM actions
WHERE
    group_id in (1489, 1616)
    AND TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
GROUP BY 1
ORDER BY 1;


How to handle a results in a field where they could be NVARCHAR, FLOAT, or INT I have a table that holds results. Based on what the ResultTypeId is these could be either NVARCHAR, FLOAT, or INT. In order to handle all these types the Value field is NVARCHAR

I've found that when handling the results I get problems. For example when trying to sum multiple FLOAT results I need to cast them to FLOAT first.

I think this is the wrong approach to storing the data. The only alternative I can think of is to create a value field for each data type, e.g.:
ValueNumeric ValueText.

What is the recommended approach for this problem?

Assuming your question is asking how you might define a table similar to your 'alternative', here is 'an' answer.

While I cannot tell you 'the recommended approach', I can say we have a table that is very similar to your situation.  We can store different kinds of data depending on other criteria.  Our table definition is similar to what your 'alternative' describes.

In our table, we have a column called DATA_TYPE CHAR(1) which identifies the type of data being stored.  I didn't design the table (as I would have opted for more than 1 character to identify the data type), but valid values are

C (Character)
D (Date)
I (Integer)
P (Decimal) - (it comes from a mainframe data type called packed-decimal)

(In your situation, you might also define F-Float, B-Bit, etc.)

We then have separate columns to hold the different data types

CREATE TABLE [SCFBDM].[CDB_AUDIT_LOG](
    [ENTITY_TYPE] [char](6) NOT NULL,
    [ENTITY_ID] [char](2) NOT NULL,
    [ENTITY_KEY] [datetime2](6) NOT NULL,
    [POLICY_NUMBER] [int] NOT NULL,
    [DEC_AMEND] [decimal](7, 3) NOT NULL,
    [ITEM] [datetime2](6) NOT NULL,
    [ENDORSEMENT] [char](6) NOT NULL,
    [ENDORSEMENT_ITEM] [datetime2](6) NOT NULL,
    [TABLE_OWNER] [char](8) NOT NULL,
    [TABLE_NAME] [char](18) NOT NULL,
    [ENTITY_OR_COLUMN] [char](1) NOT NULL,
    [ENTITY_COLUMN_NAME] [char](18) NOT NULL,
    [ROW_TYPE] [char](1) NOT NULL,
    [ACTION] [char](1) NOT NULL,
    [OVERALL_EFFECT] [char](1) NOT NULL,
    [ACCOUNT_NUMBER] [datetime2](6) NOT NULL,
    [DATA_TYPE] [char](1) NOT NULL,
    [DATA_CHARACTER] [char](35) NULL,
    [DATA_DATE] [date] NULL,
    [DATA_DECIMAL] [decimal](11, 2) NULL,
    [DATA_INTEGER] [int] NULL,
    [USER_ID] [char](8) NULL
)


SQL Server 2012 not loading its self-generated SSL certificate I have a requirement to remove the SSL certificate that is being used by a SQL Server 2012 SP4 database instance for data in transit encryption.
The client wants the SQL Server to load its self-generated certificate instead of the SSL certificate that is currently loaded.

After following the correct steps for removing the certificate by
opening SQL Server configuration manager, selecting SQL Server network Configuration, select Protocols for the database instance, right click, properties, select certificate tab, clicking clear, and then restarting the database instance I get the following error along with a failed start of the database instance which is,

  "Unable to load user-specified certificate [Cert Hash(sha1)
  "***removed****"]. The server will not accept a connection. You should
  verify that the certificate is correctly installed. See "Configuring
  Certificate for Use by SSL" in Books Online."

The thumb print id matches the certificate that we cleared.

It appears that the database instance is failing to load its self-generated SSL certificate or the procedure for removing the currently loaded SSL certificate is not fully removing it.

I've never encountered this problem before. I have been unable to find
any blogs or BOL that address this issue and how to resolve it. Additionally, the certificate value in the registy is blank. Which makes me question how SQL Server even knows to look for the SSL certificate after clearing it.

How do you get SQL Server 2012 SP4 to go from using a loaded SSL certificate to using its self-generated SSL certificate?

Discovered that a service broker endpoint was created using the same cert that we were trying to remove. After querying DMVs for sessions and connections we discovered that no connections were using the service broker endpoint so we deleted the server broker, dropped the certificate, then followed all of the steps for removing the cert in SQL Server Manager, removed the certificate from the Windows certificate store, then restarted the database instance. Confirmed that it loaded by reading in the SQL Server error log... "A self-generated certificate was successfully loaded for encryption."


Create a foreign key to a table that doesn't exist in postgres I'm trying to put together a solution where I have a bunch of script files that contain my schema creation. Something similar to Visual Studio's SQL Project, but for Postgres.

My problem is that the files won't necessarily be read in the correct order, for instance the first file/script/table read has a dependency on the 2nd file/script/table.

I was hoping there was some way I could disable the check to see if the referred table/column does exist, creating all the tables, and then re-enabling the check.

I'm running 9.2.24

My 2nd solution would be to order the files in such a way that it does only create a table when the dependencies already exist, but my above mention method would be preferred.

Not possible. The source and target of a foreign key constraint must exist when you create the foreign key.

The usual solution to that problem is, to have all foreign keys in a separate script that is run after all tables have been created. Not together with each table.


Oracle PDB broken after upgrade from 12.1 to 12.2 We upgraded our development environment Oracle 12.1 RDBMS to 12.2 version. The CDB contained 3 PDBs of which 2 upgraded nicely but 1 did not. The resulting situation is that during the upgrade SYSTEM and SYSAUX tablespaces ran out of space which caused all the packages etc also to fail. Now we cannot rebuild the internals because of the lack of space (ORA-65114: space usage in container is too high) and we cannot expand/add or frankly do any operations due to broken system data e.g:

SQL> alter pluggable database storage unlimited;
alter pluggable database storage unlimited
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'LBACSYS.LBAC$BEFORE_ALTER'
ORA-00604: error occurred at recursive SQL level 2
ORA-65114: space usage in container is too high
ORA-06508: PL/SQL: could not find program unit being called:
"LBACSYS.LBAC_EVENTS"
ORA-06512: at line 2

We have tried to create a new PDB from the failed PDB (did not work) and create a new PDB and move the old PDB's USERS tablespace files under the new PDB (using the same names, but also did not work due to mismatching checksums on PDB start).

The PDB seems to be usable by the end users but no management activities can be performed (expdp or even just looking at a table metadata in Sqldeveloper).

Is there any chance for recovery at all or should we start looking up backups?

As suggested by Balazs Papp, opening the PDB in UPGRADE mode did the trick. After that I was able to set the STORAGE limit, resize the tablespaces and rebuild broken packages.


Schedule SQL Jobs from Central Management Server I've set up a Central Management Server on a SQL Server 2016 instance.  I'm able to manually run queries against multiple servers, but I want to be able to run automated maintenance scripts.
Is there a way to set up a single SQL Job that will run a script against ALL registered servers?

There is a separate way to control multi server jobs as described in the following link.
It is a shame that these were never integrated together by MS
https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-master-job?view=sql-server-ver15


Integer comparison in MySQL. does it cause lots of overhead? I have an 8 digit number in my db. I want to do a select on the table in MySql (Innodb) and get the list of all numbers that are equal or smaller than my number. Then I sort them from larger to smaller.

I store the number as Integer.
Let's say my number is : 12040670

and after the select I get this list :

10340070
12005608
.
.
.
etc

Then I sort them from large to small.

If my table contains 1,000,000 records, and I run such comparison, do you think
it causes lots of load on the db server ? How about indexing the column ? Does
indexing help in lowering overhead when it comes to integer comparison ?

My answer assumes that you are describing a simple query like this:

SELECT `Number` FROM `Table` WHERE `Number` <= 12040670 ORDER BY `Number` DESC

From your question, it's not clear how you're storing the numbers.  If the column isn't an integer data type, you should make one (probably INT UNSIGNED).  That will help the performance whether the column is indexed or not.

There probably should be an index on the numbers column; it will significantly increase the speed of the query.  If the numbers column is the PRIMARY key, then it's already indexed.

The overhead of integer comparison isn't really relevant here; if the numbers are INTs, the comparison is very simple and efficient and if they aren't, the data types of the data and test value would be cast to the same type, which doesn't cost much at all.  The real concern would be having to read the entire table if there isn't an index available.

1,000,000 rows is nothing to MySQL.  If this query uses an index, there will be practically no load from it.  If there is no index and it has to read the entire table, there may be some load and the server may need to hit the disk if the table isn't all in memory, but this query is simple and it probably wouldn't cause significant load.  It may take up to a second to complete, but unless you would be running the query with a different reference number frequently, you probably wouldn't notice the query.


Update Mysql records using Same table but Based on same table record I have table in which i want to update records based on it's 'wishlist_id' value.
Sample format of table records.

Expected table record after update.

So can you help to update this record using mysql query?

Assuming that all records with wishlist IS NULL must be updated with the value taken from the closest previous (while sorting by id ASC) record with non-NULL wishlist value:

UPDATE test
SET wishlist = (@tmp := COALESCE(wishlist, @tmp))
WHERE (@tmp := -1)
ORDER BY id ASC

fiddle


Is there a way to Oracle expdp not inherit char type nls semantics on DDL? expdp, by default, generated internal DDL to create table observing current NLS_LENGTH_SEMANTICS of each column.

If a column as BYTE (the default and most used) NLS_LENGTH_SEMANTICS, the internal DDL would be like:

CREATE TABLE BDATA.Artikel(
    Key                   VARCHAR2(3 BYTE)  NOT NULL,
    Name                  VARCHAR2(60 BYTE) NOT NULL,
    Abkuerzung            VARCHAR2(5 BYTE)  NOT NULL
);

But I want an option to force expdp to not get/inherit the CHAR_USED value from DBA_TAB_COLUMNS, resulting into the following DDL:

CREATE TABLE BDATA.Artikel(
    Key                   VARCHAR2(3)  NOT NULL,
    Name                  VARCHAR2(60) NOT NULL,
    Abkuerzung            VARCHAR2(5)  NOT NULL
);

The reason is because the target database has a AL32UTF8 charset and NLS_LENGTH_SEMANTICS set as CHAR. So I want the tables be converted from BYTE to CHAR (accordingly to NLS_LENGTH_SEMANTICS parameter value).

I made a quick and dirty solution which worked for me today (Oracle 12.1), no reason to wait for the next 11 years.

create or replace trigger T_mutate_len_semantics after create on database
when (SYS.DICTIONARY_OBJ_TYPE = 'TABLE' and SYS.DICTIONARY_OBJ_OWNER not in ('SYS','SYSTEM'))
 declare
    stmt varchar2(2000);
begin
  --debug
  dbms_system.ksdwrt(2,'T_mutate_len_semantics: After creating table '||SYS.DICTIONARY_OBJ_NAME);
  for r in (select column_name, data_length, data_type
            from dba_tab_columns
            where table_name = SYS.DICTIONARY_OBJ_NAME
              and owner = SYS.DICTIONARY_OBJ_OWNER
              and data_type in ('VARCHAR2','CHAR')
              and DATA_LENGTH = CHAR_LENGTH
            )
    loop
        stmt := 'alter table '||SYS.DICTIONARY_OBJ_OWNER||'.'||SYS.DICTIONARY_OBJ_NAME||' modify ('||r.column_name||' '||r.data_type||'('||r.data_length||' CHAR))';
        dbms_system.ksdwrt(2,'T_mutate_len_semantics: '||stmt);
        execute immediate stmt;
    end loop;
end;
/


Error Code: 1239. Incorrect foreign key definition for 'foreign key without name': reference and table don't match I'm making a new database in MySQL in which after making the Department table I receive an error when adding other tables.

CREATE TABLE Department (dept_name varchar(20), building varchar(15), budget numeric(12,2), primary key(dept_name));

CREATE TABLE Course (course_id varchar(7), title varchar(50), dept_name varchar(20),credits numeric(2,0), primary key(course_id),
 foreign key(dept_name) references Department);

CREATE TABLE Instructor (ID varchar (5),name varchar (20) not null,dept_name varchar (20),salary numeric (8,2),primary key (ID),
foreign key (dept_name) references Department);

I had a look at Error 1239: Incorrect foreign key definition   but it didn't seem to help.

You need to add column name along with table name.

  CREATE TABLE Department (dept_name varchar(20), building varchar(15), budget numeric(12,2), primary key(dept_name));

        CREATE TABLE Course (course_id varchar(7), title varchar(50), dept_name varchar(20),credits numeric(2,0), primary key(course_id),
         foreign key(dept_name) references Department(dept_name));

        CREATE TABLE Instructor (ID varchar (5),name varchar (20) not null,dept_name varchar (20),salary numeric (8,2),primary key (ID),
        foreign key (dept_name) references Department(dept_name));


Slow UPDATE FROM Query in Large Table I'm currently trying to merge two very large (11 million rows) tables, and my query has been running for over two days with no end in sight.

My basic query is:

UPDATE us_demand
SET ...
FROM us_demand_addtl AS sub
WHERE us_demand.geoid10=sub.geoid10;

The tables each have a spatial column that is indexed (but not one of the updated columns), and geoid10 is NOT indexed in either table.

Machine Specs:

2x 7200RPM 1tb Drives in Raid

Intel i74790k - 4.00ghz quad core

32gb of RAM

Analyze Result:

I have two questions.  Why is this operation so slow, and what can I do to speed it up?

I would index geoid10 on both tables.  If you can commit partial results, commit every 1000 to 10,000 updates.  You should be able to do the updates in a stored procedure.  This may reduce the overhead for lock contention.


Could not find stored procedure I created a procedure to rebuild indexes with a cursor. Instead of executing properly, I get an error message:

  Msg 2812, Level 16, State 62
  Could not find stored procedure

What to do to get rid of this situation?

USE [ACT]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
ALTER PROCEDURE [dbo].[myProcedure]
AS
BEGIN

DECLARE @idx_name VARCHAR(200) -- Index name
DECLARE @tableName VARCHAR(256) -- Table Name
DECLARE @sql varchar(500)

DECLARE Index_Rebuild_Cursor CURSOR FOR
SELECT OBJECT_NAME(ps.OBJECT_ID)tbl_name,b.name idx_name
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.avg_fragmentation_in_percent>80

OPEN Index_Rebuild_Cursor
FETCH NEXT FROM Index_Rebuild_Cursor INTO @tableName,@idx_name

WHILE @@FETCH_STATUS = 0
BEGIN

 SET @sql='ALTER INDEX '+@idx_name+' ON '+@tableName+' REBUILD'
 EXEC @sql

 FETCH NEXT FROM Index_Rebuild_Cursor INTO @tableName,@idx_name
END

CLOSE Index_Rebuild_Cursor
DEALLOCATE Index_Rebuild_Cursor

END

EXEC @sql is invalid syntax (you need EXEC(@sql); - those parentheses are important). Nonetheless, I highly recommend:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'ALTER ...';
-----------^ this N is important too
...

EXEC sp_executesql @sql;

Doing this as a habit makes sure that you will be able to use strongly typed parameters when you can.


Property class Length Annotation PersistedResolvableAnnotation I am trying to deploy an ssdt project using sqlpackage.exe utility.
I get the following error (in German):

  Fehler bei der Erstellung des Bereitstellungsplans. Die Bereitstellung kann nicht fortgesetzt werden. Die Property-Klasse
  Length ist nicht in der Element- oder Annotation-Klasse
  PersistedResolvableAnnotation enthalten.

It means sth. like:

  Error while creating deployment plan. Deployment cannot proceed. The
  property-class Length is not included in the element or annotation
  class PersistedResolvableAnnotation.

I could not find much information about "PersistedResolvableAnnotation". But I realized that it is included in the model.xml in the dacpac.

It contains handful of SqlLogins defined similar to this one:

<Element Type="SqlUser" Name="[Angela]">
            <Property Name="IsWithoutLogin" Value="True" />
            <Relationship Name="DefaultSchema">
                <Entry>
                    <References Name="[Angela]" Disambiguator="8" />
                    <Annotation Type="**PersistedResolvableAnnotation**" Name="[Angela]">
                        <Property Name="TargetTypeStorage" Value="SqlSchema" />
                        <Property Name="Length" Value="8" />
                        <Property Name="Offset" Value="62" />
                    </Annotation>
                </Entry>
            </Relationship>
        </Element>

Angela (and the other candidates) do not exist on destination server as Login, although they are registered database users in the target db. I would expect another error - if any - than this useless one.

The database the project is based on a pretty old SQL Server verison. Is it possible that this is some deprecated feature / syntax or property coming from Sql Server version and is simply not supported ?
Does anyone know more details?

I found the issue myself. I will document it in case anyone else is facing the issue in future.

The following user definition:

CREATE USER [Angela] WITHOUT LOGIN
    WITH DEFAULT_SCHEMA = [Angela];

created the following code in the model.xml that avoids publishing, maybe because the schema (Angela) used there is invalid because it simply does not exist:

<Element Type="SqlUser" Name="[Angela]">
            <Property Name="IsWithoutLogin" Value="True" />
            <Relationship Name="DefaultSchema">
                <Entry>
                    <Annotation Type="PersistedResolvableAnnotation" Name="[Angela]">
                        <Property Name="TargetTypeStorage" Value="SqlSchema" />
                        <Property Name="Length" Value="9" />
                        <Property Name="Offset" Value="63" />
                    </Annotation>
                </Entry>
            </Relationship>
        </Element>

After changing the user definition to:

CREATE USER [Angela] WITHOUT LOGIN
    WITH DEFAULT_SCHEMA = [dbo];

the model lookes as followes and could be published:

<Element Type="SqlUser" Name="[Angela]">
        <Property Name="IsWithoutLogin" Value="True" />
    </Element>

I have a feeling that this strange behavior is kind of a follow-up of another misbehavior because I would expect the not existing schema to raise a compiler error.


Attached database to SQL Server 2016 and now won't work in SQL Server 2014 I have a SQL Server 2014 database (on a portable hard drive) which I had to attach to a SQL Server 2016 machine using the normal attach method.

However, since attaching it to that computer, it no longer works on the SQL Server 2014 computer even though I didn't change the compatibility level or anything else for that matter.

I'm now stuck because SQL Server 2016 will not work on Windows 7 machines which is what all the other computers run on. I just happened to attach it on a laptop running Windows 10 and SQL 2016 thinking it wouldn't be a problem.

Is there any way to reverse this situation? I'm not able to get any good information from the error screen in management studio.

When you attached the mdf file(s) to the SQL Server 2016 instance the database was upgraded. The files were marked as being of the new version. There is no downgrade path in SQL Server.

The best option is restore from backup. Else, you many be able to script the schema and data and build a new database on the 2014 instance which is identical to the previous one. Here's one way of achieving this. Be aware of the things the tool may not script, like users, permissions, synonyms etc.

See also: How to move a database from SQL Server 2012 to SQL Server 2005


Proper way of restoring base backups We have daily backups of a postgres db and it is in the form of a tarred (gz) base backup.
I need to restore this into another db of the same version. However, I am confused with the article that I am reading about doing this.
The version of the postgres DB is 8.2.4.
These are the main steps given :
rm -rf /var/lib/postgresql/10/main/*
cp -r /basebackup/* /var/lib/postgresql/10/main/
rm -rf /var/lib/postgresql/10/main/pg_wal/*

So in my case, the postgresql data directory is /usr/local/pgsql824/data. I believe this must be /var/lib/postgresql/main in the command above. I have untarred the tar.gz base backup into /usr/local/temp. So I suppose that this path is /basebackup in the command above. Please correct me if I am wrong.
Now, there comes the part of the "recovery.conf" file. These are the contents of the file as given by the article :
vim /var/lib/postgresql/10/main/recovery.conf
restore_command = 'cp /wal_archive/%f "%p"'
recovery_target_lsn = '3/72658818'

What is the purpose of the "/wal_archive" if I have already untarred contents of the base backup into /usr/local/temp ? This is what I am not sure about.
This is the link to the full article :
https://severalnines.com/database-blog/point-time-postgresql-database-restoration
I just need a clear explanation on how to restore the base backup to the new database data directory.

Usually a basebackup with no further description would mean the thing taken by pg_basebackup.  But that didn't exist back in 8.2., so it isn't clear what "a basebackup" means here.  Not that it is very clear anyway, as pg_basebackup has many options.
The article you linked to is for point-in-time recovery.  Obviously a base backup cannot include changes that had not yet happened at the time the backup was taken.  The WAL archive is needed to roll the database forward from the ending time of the backup, to the desired point in time.  If you don't actually want to do a PITR but just restore to the ending time of the basebackup, then you won't need the recovery.conf, and you also need to not do the rm -rf /var/lib/postgresql/10/main/pg_wal/*.  (Although if you did run that command, it wouldn't do any harm anyway as the directory in question was not called 'pg_wal' back then.)  You just start up the database.


Postgresql query history hacking I believe that PostgreSQL can log slow or unsuccessful queries. Probably I can set also PostgreSQL to log all queries executed. Contrarily, I am interested to know if there is a way that a malicious attacker can get access to all the queries successfully executed on the PostgreSQL server, if I have disabled logging as much as possible. Is this possible? Or once a query has been successfully executed (might be a SELECT or UPDATE query) I can be 100% sure that the DB server has no memory of successfully executed queries and therefore no one else can get access to this information. I am using PostgreSQL 9.3.

[can I] be 100% sure that the DB server has no memory of successfully executed queries and therefore no one else can get access to this information?

No, you can never be 100% sure of that.

PostgreSQL maintains an internal plan cache. Currently this cache is only used for prepared statements; one-shot statements do not go into the cache. If you use a prepared statement though, the plan will be cached, and when no longer required the cached plan is invalidated but not necessarily deleted until the PostgreSQL backend that cached it exits.

PostgreSQL stores the query string in an internal debug variable. When a new query replaces the old one the debug variable is overwritten. If the new query is shorter than the old one, some of the old query won't be overwritten.

A query could be running a backend that has pages of memory written to disk in swap space because of memory pressure. That memory might contain the query string and/or parameters. This swap space isn't necessary overwritten even after the query has finished and the backend has exited. If the server is powered off abruptly the hard drive could be forensically analyzed and might discover query text.

When PostgreSQL frees internal query planner/executor objects, it does not zero them. It just marks the memory as free for re-use. This memory remains mapped until the PostgreSQL backend exits, and may contain query text, parameter values, etc.

When a PostgreSQL backend exits, the kernel doesn't generally zero its memory, just marks it as freed. An attacker who can exploit the kernel to gain raw memory read access could then see some past (and, of course, all current) queries.

So if:

You have encrypted swap space with a password supplied interactively on boot;
You accept that anyone with physical access to the machine will probably be able to exploit it to retrieve recent query history;
You understand that anyone with root access, or access as the postgres system user, can trace PostgreSQL's execution and capture query data (just not retroactively); and
You only care about query text recoverability after the individual PostgreSQL backend that ran that query has exited

then turning off logging is probably good enough. You also need to avoid using extensions like pg_stat_plans and pg_stat_statements that might capture query text and record it in the database.

Note that if PL/Python, PL/Perl, etc are installed, or some contrib modules are present, then it might be fairly easy to escalate access from the postgres database user (or other superuser) to the postgres shell user. From there you can reconfigure the database to enable logging, install extensions, inject C code, etc. There is no guarantee you would detect such an exploit, so they might be logging activity for some time.

An attacker who gets access even as the table owner might install a FOR EACH STATEMENT trigger on tables of interest that logs queries affecting those tables. That won't work for SELECT, but will for everything else. They don't even need to be database superuser for that.

Generally your queries shouldn't be security sensitive so much as your query parameter data. You can greatly reduce the chances of it being captured or logged by using proper bind parameters, e.g. libpq's PQexecParams, PgJDBC's PreparedStatement class, etc.

Most importantly, though ... if you want to maintain database security, don't lose privileged access credentials. Don't use a superuser account except from the DB server and set pg_hba.conf up so the superuser can't log in remotely. Don't have applications connect to the database as the same account that owns the tables they use, use a different account and GRANT only the rights they require. etc.

IMO PostgreSQL needs an extension in the protocol that lets you mark query parameters as "sensitive". Such query parameters should never be emitted in log files, shown in pg_stat_activity or otherwise recorded. This feature does not currently exist, though, and I'm not aware of anybody working on it


What are the privileges required to execute a trigger function in PostgreSQL 8.4? What are the privileges required to execute a trigger function in PostgreSQL 8.4?

It seems that the privileges set to a role does not matter to execute a trigger function. I think I have seen some day that the privileges required to execute a trigger function is the EXECUTE privilege but for the owner of the table, not the actual role that performs the action which fires the trigger that calls the trigger function.

I cannot find the documentation part that explains that point, any help?

Trigger functions behave just like other functions as far as privileges are concerned. With a minor exception:

  To create a trigger on a table, the user must have the TRIGGER
  privilege on the table. The user must also have EXECUTE privilege on the trigger function.

UPDATE
After feedback in the comments I did some research. There is an open TODO item in the Postgres Wiki:

  Tighten trigger permission checks

Linked to this thread on Postgres hackers. Currently, EXECUTE privileges on a trigger function are only checked at trigger create time, but not at runtime. So revoking EXECUTE on the trigger function has no effect on a trigger once created. Your observation seems to be correct.

This does not grant any additional privileges to manipulate objects. If the calling role lacks privileges needed to execute (parts of) the function body, the usual exception is raised. To pave the way, you could make a privileged user OWNER of the function and use the

SECURITY DEFINER

clause, as documented in the manual here. It causes the function to be run with the permissions of the owner instead of the invoker (default).

If the owner is a superuser, you need to be extra careful who you grant the EXECUTE privilege and what the function can do to avoid abuse. You may want to

REVOKE ALL ON FUNCTION foo() FROM public;

to begin with and use SET search_path for the function.
Be sure to read the chapter on Writing SECURITY DEFINER Functions Safely.

Find a code example in this related answer on SO.


SSRS connection string Encryption i am working on porject to encrypt the connection between ssrs reports and sql server. we have already installed ssl certificate on sql server. can i know how to encrypt the connection string in SSRS datasource

When you setup a data source, you can specify components of the connection string directly.

Simply add the following to the connection string:

Encrypt=True;TrustServerCertificate=True

That will force encryption between the SQL Server and the SSRS reporting server for the given data source.  Any report that uses that data source will obtain its data via an encrypted channel.  The TrustServerCertificate=True tells the client (SSRS) to trust the server certificate, even if it's a self-generated, or untrusted, certificate.


Azure: Replication Beginner help We have SQL Azure database running with our website. However today's outage of SQL Azure give us a setback. And now we are looking a way to replicate [as I read on internet] is best to do have 2 database running in some sort of fault tolerance mode. Well, Frankly I didn't know much of terminology or don't have much knowledge. I am software developer and can just manage 100-200MB Database.

Now, my question/request is what we can do to ensure that if one SQL Azure DB is down then we can back our site on other database, which is same, with almost latest data from main database.

I try to search on internet, but most of article use terminology that I hardly understand them. Maybe some wise word will help me find direction. Thanks.

Traditionally SQL Server relied on technologies like SQL Server Replication, Log Shipping or Change Data Capture/Change Tracking to replicate data across multiple locations. Unfortunately none are available in SQL Azure. Instead SQL Azure has the SQL Data Sync feature:

  SQL Data Sync (Preview) is a service of SQL Database that enables you to synchronize the data you select across multiple SQL Server and SQL Database instances. To synchronize your data you create sync groups which define the databases, tables and columns to synchronize as well as the synchronization schedule. Each sync group must have at least one SQL Database instance which serves as the sync group hub in a hub-and-spoke topology.

At this time SQL Data Sync is in preview and has some known limitations.

Also SQL Azure does offer backup/restore and export/import features, see Windows Azure SQL Database Backup and Restore, but these are all 'copy everything' solutions, not 'copy only what is changed'.

A possible solution is to switch to SQL Server on Windows Azure, which gives you a traditional SQL Server instance capable of doing all the technologies above, and you can have it replicate data to a SQL Server hosted on Amazon EC2. See Provisioning a SQL Server Virtual Machine on Windows Azure.


Tables not displaying after creating database by script. Do I need to insert? I successfully created (scripted) a database in SQL Server 2012.

My problem is that while the database itself is listed in the object explorer, the tables from the database are not. The database I scripted did not contain actual data, i.e., there were no 'Insert' statements. Is this last the reason why the tables are not listed?

I tried to see if the tables were stored somehow without appearing by running an insert into statement for  one of the tables in the script statement , but I get an error:

  " Msg 208, Level 16, State 1, Line 1 Invalid object name
  'ContactInfo'. "

What am I doing wrong? Why aren't the database tables listed?

Here is the actual script I used:

CREATE DATABASE [Name];
GO

CREATE TABLE Company  (CompName Varchar(10) PRIMARY KEY , CompCity VarChar(20), CompState CHAR(2), CompAddress Varchar(50), CompZip SMALLINT  );

CREATE TABLE NamePhone (CompName VarChar(10) NOT NULL PRIMARY KEY REFERENCES Company(CompName) , CompPhone   Varchar (12) NOT NULL );

CREATE TABLE ContactInfo (ContactFName NVarChar(20) PRIMARY KEY , ContactLName NVarchar(20), ContTitle Varchar(10), ContactDirPhone Char(10), ContEmail Varchar(320)
, ContCompany Varchar(10) REFERENCES Company(CompName) );

CREATE TABLE ContactReportsTo (ReportstoFName NVarChar(20) , ReportstoLName NVarchar(30), ReportstoPhone Varchar(50), ReportstoCompany Varchar(10) REFERENCES
Company(CompName),  ContactFName NVarchar(20)  REFERENCES ContactInfo(ContactFName));

In Object Explorer, drill down to your database, and then to Tables.

Right-click Tables and click Refresh.

If your newly created tables do not show up, perhaps you created the tables under a different database by mistake. This might happen if you were missing a USE YourDatabase in which case they would likely end up in the default database for your login (often master)

Also, the following will show all of the tables in a database. Run it for your database. If they do not show up, SQL Server did not create the tables (at all/in the desired location).

use [YOUR_DATABASE]
GO
select * from sys.tables
GO


Efficient way to get last 10 rows of each group in SQLite, can CTE help? Consider there is a table of job runs history with the following schema:

job_runs
(
    run_id integer not null, -- identifier of the run
    job_id integer not null, -- identifier of the job
    run_number integer not null, -- job run number, run numbers increment for each job
    status text not null, -- status of the run (running, completed, killed, ...)
    primary key (run_id)
    -- ...
)

and it is required to get the last 10 runs with status != 'running' for each job (jobs differ by job_id). To do that I wrote the following query:

SELECT
    *
FROM
    job_runs AS JR1
WHERE
    JR1.run_number IN
    (
        SELECT
            JR2.run_number
        FROM
            job_runs AS JR2
        WHERE
            JR2.job_id = JR1.job_id
            AND
            JR2.status != 'running'
        ORDER BY
            JR2.run_number
        DESC
        LIMIT
            10
    )

It do what I need, but even though there is a multifield index on the job_id and run_num fields of the job_runs table the query is slow, because it scans job_runs table and for each its row runs subquery. The index helps subqueries to run fast each time, but the fact that the nester query scans entire table kills performance. So how can I tune performance of the query?

some thoughts:

Number of jobs (different job_ids) is small and if there were a FOR loop in SQLite it would be easy to loop over all distinct job_ids and run the subquery
passing the job id instead of JR1.job_id then UNION all results.
Also I've tried to construct the query using recursive CTE, but unsuccessfully.

important:

Please don't suggest to run the loop inside the source code of my application. I need pure SQL solution.

Since the number of distinct job_id is small, you can try this way of writing this type of queries. I've often used in MySQL that doesn't have window functions - and you can't even have a subquery with a IN (SELECT ... LIMIT x). Try this with an index on (job_id, run_number, status):

SELECT
    jr.*
FROM
    ( SELECT DISTINCT job_id
      FROM job_runs
    ) AS jrd
  JOIN
    job_runs AS jr
    ON  jr.job_id = jrd.job_id
    AND jr.status <> 'running'
    AND jr.run_number >= COALESCE(
        (
            SELECT jri.run_number
            FROM job_runs AS jri
            WHERE jri.job_id = jrd.job_id
              AND jri.status <> 'running'
            ORDER BY jri.run_number DESC
            LIMIT 1 OFFSET 9
        ), -2147483647)
    ;

and this (with same index):

SELECT
    jr.*
FROM
    ( SELECT DISTINCT job_id
      FROM job_runs
    ) AS jrd
  JOIN
    job_runs AS jr
    ON  jr.run_id IN
        (
            SELECT jri.run_id
            FROM job_runs AS jri
            WHERE jri.job_id = jrd.job_id
              AND jri.status <> 'running'
            ORDER BY jri.run_number DESC
            LIMIT 10
        )
    ;


Disk performance causing SQL Server Command Timeout? I setup a perfmon collector to capture data around some recent SQL Server command timeout errors and most indicators point to disk issues. CPU and memory numbers look normal. This is a SQL Server 2008 physical server with 2 disks. SQL data files reside on a one disk (RAID 5) and SQL log files on another.

At the time of the errors these particular counters are way beyond normal (about 10x worse than normal):

% Disk Time
Disk Reads/Sec (on the data disk)
Disk sec/Read (on the data disk)
SQLServer:Buffer Manager\Page life expectancy

With these perfmon findings, what would the next step be to isolate the problem?  Running SQL Profiler?

Command timeout simply means that your queries don't return in the 30 seconds allocated by default. What is the size of your database and how much RAM you have?

The high disk usage is most likely a symptom, not a cause, and is caused by table scans, ie. missing indexes. A quick look at the execution plan of your timing out queries will reveal if this guess is correct or not (See Displaying Execution Plans by Using SQL Server Profiler Event Classes.

As a general advice I suggest you approach this as a performance investigation and deploy the well tested Waits and Queues methodology.


MySQL always writes simple temporary/memory tables to disk (both tmp_table_size and max_heap_table_size are set to 8GB) I'm struggling with this issue and just can't figure out why this is happening.

CREATE TABLE sitecache._test ENGINE=MEMORY SELECT * FROM website.gallery_likes WHERE 1=0;

Every time I execute this query, the Created tmp disk tables server variable increases immediately by 5. The same thing happens for CREATE TEMPORARY TABLE ... and also for creating a table with a structure definition instead of copying the structure from another table.

Structure of website.gallery_likes:

CREATE TABLE gallery_likes (
  photoid mediumint(8) unsigned NOT NULL,
  userid int(10) unsigned NOT NULL,
  timestamp int(10) unsigned NOT NULL,
  KEY photoid (photoid,userid)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

As you can see there's no TEXT and no BLOB fields, it's a fairly simple table. If I then dump a large set of data into that temporary/memory table, it takes several minutes to complete and I can see how mysqld is writing a lot of data to a temporary file in the C:\Windows\Temp directory.

tmp_table_size and max_heap_table_size are both set to 8GB (and confirmed via SHOW VARIABLES).

MySQL 5.6.17 x64 on Windows Server 2012 R2 x64 with 64GB RAM.

Config:

[client]
no-beep
port=3306

[mysql]
default-character-set=latin1

[mysqld_safe]
open-files-limit=16384

[mysqld]
log-bin="D:/MySQL Blackhole/master_replication.log"
sync_binlog=0
binlog-do-db=...
binlog-ignore-db=...
...edited...
binlog-format=STATEMENT
expire_logs_days=1
server-id=1
ft_min_word_len=3
innodb_ft_min_token_size=3
port=3306
datadir="D:/MySQL/data/"
character-set-server=latin1
default-storage-engine=MYISAM
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=NONE
general-log=0
general_log_file="MYSQL_SERVER.log"
slow-query-log=0
slow_query_log_file="MYSQL_SERVER-slow.log"
long_query_time=10
log-error="MYSQL_SERVER.err"
max_connections=1024
query_cache_size=0
table_open_cache=4096
tmp_table_size=8G
max_heap_table_size=8G
thread_cache_size=64
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=256M
key_buffer_size=8192M
read_buffer_size=2M
read_rnd_buffer_size=128M
sort_buffer_size=8M
innodb_flush_log_at_trx_commit=0
innodb_flush_log_at_timeout=1
innodb_log_buffer_size=32M
innodb_buffer_pool_size=32G
innodb_log_file_size=8G
innodb_thread_concurrency=0
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=0
innodb_open_files=4096
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=strict_crc32
innodb_adaptive_hash_index=ON
back_log=80
flush_time=0
join_buffer_size=8M
max_allowed_packet=16M
max_connect_errors=10
open_files_limit=16384
query_cache_type=0
table_definition_cache=2500
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

(query_cache_size=0 and default-storage-engine=MYISAM on purpose)

This happens on the production system. I have MySQL 5.6.16 x64 on my development machine (Windows 8.1 x64) running with the exact same databases and almost the same config (smaller innodb buffer pool, tmp_table_size at 512M, no replication) and this doesn't happen here. Something environmental must be different.

I've now read the documentation multiple times but I just don't see any connection as to why this happens. The table is fairly simple with no columns that are more than 512 bytes long.
Full disclosure: I already posted it on StackOverflow but nobody responded.

Okay, in case anyone stumbles upon a similar problem and Google directs them here:
I don't exactly figured out why, but sort of figured out what causes it, phpMyAdmin was the culprit.

Issuing the same commands directly in the mysql command line did not cause the temporary tables to be written to disk.
Now, I also have phpMyAdmin on my development machine and it's not an issue here. But I'm running PHP as a module in Apache, whereas on the production server it's IIS with PHP running as FastCGI. So some configuration or limitation must've caused this, not exactly sure.


MySQL - Unix timestamp from columns with hour, day, month I have MySQL table with columns (day, month, hour). Is it possible to build unix timestamp from this within the query itself?

I am looking for something like this:

SELECT unix(2016, a.month, a.day, a.hour, 00, 00) as d
FROM a
WHERE d > 456456 AND d < 789789

Values 456456 and 789789 is provided from an application. I know, I can create unix timestamp from STR_TO_DATE, but then I have to create it for every comparison in WHERE clause as well, which I am afraid will be slow.

Create table:

CREATE TABLE `a` (
  `data_a` smallint(6) NOT NULL DEFAULT '0',
  `hour` tinyint(4) NOT NULL,
  `day` tinyint(4) NOT NULL,
  `month` tinyint(4) NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

MySQL version 5.0

Instead of transforming the columns in the WHERE clause, do so with the constants given by the application. Those calculations only have to be done once.

We can write the condition in a short, compact form:

SELECT STR_TO_DATE(...)
FROM a
WHERE  (2016, a.month, a.day, a.hour, 0, 0)
      >
      (  YEAR(FROM_UNIXTIME(456456)),
        MONTH(FROM_UNIXTIME(456456)),
          DAY(FROM_UNIXTIME(456456)),
         HOUR(FROM_UNIXTIME(456456)),
       MINUTE(FROM_UNIXTIME(456456)),
       SECOND(FROM_UNIXTIME(456456))
      )
  AND  (2016, a.month, a.day, a.hour, 0, 0)
      <
      (  YEAR(FROM_UNIXTIME(789789)),
        MONTH(FROM_UNIXTIME(789789)),
          DAY(FROM_UNIXTIME(789789)),
         HOUR(FROM_UNIXTIME(789789)),
       MINUTE(FROM_UNIXTIME(789789)),
       SECOND(FROM_UNIXTIME(789789))
      )

or in a long form, which has much more chances of using indexes effectively, in versions before 5.7:

SELECT STR_TO_DATE(...)
FROM a
WHERE ((2016 > YEAR(FROM_UNIXTIME(456456)))
    OR (2016 = YEAR(FROM_UNIXTIME(456456))
        AND a.month > MONTH(FROM_UNIXTIME(456456)))
    OR (2016 = YEAR(FROM_UNIXTIME(456456))
        AND a.month = MONTH(FROM_UNIXTIME(456456))
        AND a.day > DAY(FROM_UNIXTIME(456456)))
    OR (2016 = YEAR(FROM_UNIXTIME(456456))
        AND a.month = MONTH(FROM_UNIXTIME(456456))
        AND a.day = DAY(FROM_UNIXTIME(456456))
        AND a.hour > HOUR(FROM_UNIXTIME(456456))))
AND   ((2016 < YEAR(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month < MONTH(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month = MONTH(FROM_UNIXTIME(789789))
        AND a.day < DAY(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month = MONTH(FROM_UNIXTIME(789789))
        AND a.day = DAY(FROM_UNIXTIME(789789))
        AND a.hour < HOUR(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month = MONTH(FROM_UNIXTIME(789789))
        AND a.day = DAY(FROM_UNIXTIME(789789))
        AND a.hour = HOUR(FROM_UNIXTIME(789789))
        AND 0 < MINUTE(FROM_UNIXTIME(789789))+SECOND(FROM_UNIXTIME(789789))))

This will also allow MySQL to use indexes on this query, if available. If not already done, you should create an index as (month, day, hour) (in this order!).


How do I grant the privilege of connecting to SSIS? Attempting to run SSIS packages using dtexec.exe, I got this error message:

Could not load package "D:\Filename.dtsx" because of error 0xC00160AE.
Description: Connecting to the Integration Services service on the computer "" failed
with the following error: "Access is denied."

By default, only administrators have access to the Integration Services service.  On
Windows Vista and later, the process must be running with administrative privileges in
order to connect to the Integration Services service. See the help topic for information
on how to configure access to the service.

Typically for Microsoft, there's no clarification about which help topic has this information.  Certainly the dtexec /? help does not.

Running dtexec from a command prompt launched with admin privileges, or from an application with admin privileges, did not resolve the problem.

I eventually found the solution on MSDN.  To grant users rights to the Integration Services service:

Open Component Services; from a Run dialog, you can enter "dcomcnfg".
On the left-hand tree, navigate to Component Services | Computers | My Computer | DCOM Config.
Find "Microsoft SQL Server Integration Services 12.0".  This is for 2014; I believe earlier versions leave out "Microsoft," so check under "S" if you don't see it under "M".
Open Properties | Security and for each type of permission hit Edit and add an appropriate AD group or user.  Be sure to check the specific permissions required, such as Remote Launch when editing Launch and Activation Permission.  If anyone knows exactly which permissions are necessary for running SSIS, please share.
Launch Services, and restart the SQL Server Integration Services service.

You should now be able to connect to Integration Services from within SQL Studio or dtexec.exe.


why does my clustered index scan have ordered = false When I look to at my query plan, I see plenty of clustered index seeks (yay!) and scans (not quite so yay!) but in some of them ordered = False.  I will mention that we have parallelism involved, though it does not appear to have any relation to these.  The indexes were set up with ordering.

What does this mean?  That the query plan is ignoring this order?  Or that the query is choosing not to order?  Or... heck, I have no idea what I'm looking at...!

It just means that the query optimiser doesn't require an explicit order guarantee either for some later operator in the plan (e.g. merge join or stream aggregate) or to avoid a sort because you have explicitly requested an order by.

When ordered = false you might in some circumstances get an allocation ordered scan rather than a scan that follows the linked list of the leaf pages in index key order for example.


MySQL crashes daily on VPS with Ubuntu Server 12.04 - How can I find the error or conflict? Every day for the past four days, my website server crashes at least once. (Error establishing database connection.

As long as I catch it, I simply have to do the following command:

sudo /etc/init.d/mysql start

and its up and running in about 30 seconds.

I imagine this is not enough info to diagnose, but I am sure there is an error log or something I can pull which would be more useful.

It is running on a single core w/ 512 mb memory. The whole site is funneled through CloudFlare, so even at peak traffic it never really sees an average load above 0.25 when checking htop.

One random thought - I have an embedded twitter feed that shows latest tweets. It happened today immediately after the tweet was posted. I have no idea if there is a connection and didn't pay attention the last few days when tweeting. Just thought I would throw it out there in case someone knows of a conflict.

Any ideas?

Thank you.

Check the value of the variable "log_error":

select @@log_error

or

show variables like 'log_error'

It defaults to /var/log/mysqld.log on a RHEL6 server though can be different.

Then check this log file - it will generally give you a reason why it's failing.

Alternatively, if the service is being killed because the server is running out of memory, check the system log:

/var/log/messages

For the "Out Of Memory killer" doing it's thing or any other messages that might be relevant around the time that the server died.


Blocking continues after enabling snapshot isolation On database hosted on a SQL Server 2012 instance, I have enabled ALLOW_SNAPSHOT_ISOLATION and verified the state as ON using

SELECT snapshot_isolation_state_desc,name from sys.databases

However, in 2 separate sessions, if I run a long running select with TABLOCK in the 1st and an UPDATE in the 2nd (or vice versa), whichever query starts first blocks the second query (as per sp_who2)

Looking at

select * from sys.dm_exec_requests , both queries have a transaction isolation level of read committed (2)

As per my understanding, with snapshot isolation on, tempdb usage should increase however blocking should not occur in this situation. Am I missing some configuration steps to achieve this behaviour?

If your intention is to avoid readers from blocking writers and visa-versa in the default READ_COMMITTED isolation level, turn on the READ_COMMITTED_SNAPSHOT database option.  This will cause row versioning instead of locking to be used to implement statement-level read consistency.

Although often confused, the ALLOW_SNAPSHOT_ISOLATION option is not related to READ_COMMITTED_SNAPSHOT.  ALLOW_SNAPSHOT_ISOLATION allows the separate SNAPSHOT isolation level to be used to provide multi-statement read consistency but code changes are required to use it.  The READ_COMMITTED_SNAPSHOT option provides statement-level read-consistency without code changes, although one should be aware of the implications the locking behavior have on apps that rely upon locking.


Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days? Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns.

The database development started in the days of SQL Server 6.0.

One of the rules followed from the beginning was, Avoid creating a clustered index based on an incrementing key, as you find in these Index Optimization Tips.

Now using SQL Server 2005 and SQL Server 2008, I have the strong impression that the circumstances changed. Meanwhile, these primary key columns are perfect first candidates for the clustered index of the table.

The myth goes back to before SQL Server 6.5, which added row level locking. And hinted at here by Kalen Delaney.

It was to do with "hot spots" of data page usage and the fact that a whole 2k page (SQL Server 7 and higher use 8k pages) was locked, rather then an inserted row
Edit, Feb 2012

Found authoritative article by Kimberly L. Tripp

"The Clustered Index Debate Continues..."

  Hotspots were something that we greatly tried to avoid PRIOR to SQL Server 7.0 because of page level locking (and this is where the term hot spot became a negative term). In fact, it doesn't have to be a negative term. However, since the storage engine was rearchitected/redesigned (in SQL Server 7.0) and now includes true row level locking, this motivation (to avoid hotspots) is no longer there.

Edit, May 2013

The link in lucky7_2000's answer seems to say that hotspots can exist and they cause issues.
However, the article uses a non-unique clustered index on TranTime. This requires a uniquifier to be added. Which means the index in not strictly monotonically increasing (and too wide). The link in that answer does not contradict this answer or my links

On a personal level, I have woked on databases where I inserted tens of thousands of rows per second into a table that has a bigint IDENTITY column as the clustered PK.


How to query Wikipedia's database to retrieve the main topic and the surface forms of a given title? First, Wikipedia's database scheme diagram is available here. I have used one of their dumps locally to work with it.

Now, I need a single MySQL query to check both page and redirect tables with a given input 'Dog_breeder' or 'Dog_breeding' for example. I want to get the list of both types of pages linked to this input.

For example, Dog_breeding is a main topic and the below pages will redirect to it:

Dog_Breeder
Dog_breeder
Dog_breeders
Stud_dog

In the database, this is stored in this fashion:

|---------------------------|       |---------------------------|
| table: page               |       | table: redirect           |
|---------------------------|       |---------------------------|
| page_id   | page_title    |       | rd_from   | rd_title      |
|---------------------------|       |---------------------------|
| 476072    | Dog_breeding  |       | 796236    | Dog_breeding  |
| 796236    | Dog_breeder   |       | 16274701  | Dog_breeding  |
| 16274701  | Dog_breeders  |       | 37353772  | Dog_breeding  |
| 37353772  | Stud_dog      |       | 40738352  | Dog_breeding  |
| 40738352  | Dog_Breeder   |       |---------------------------|
| 11606599  | Dog_breeders  |
| 22534134  | Dog_breeders  |
|---------------------------|

So all pages are in the page table and then redirections are stored in the redirect table (rd_from is the page that will be redirected to the main rd_title page)

I have the query to retrieve all redirect entries of a main topic:

SELECT
    page_id,
    page_title,
    rd_from,
    rd_title
FROM
    redirect
    JOIN page ON page_id = rd_from
WHERE rd_title = 'Dog_breeding'

But two problems:

it is not giving me the main page itself ('Dog_breeding')
if I put WHERE rd_title = 'Dog_breeder' it finds nothing...

I would like to get something like the below regardless of the input being Dog_breeding or Dog_breeder:

page_id     | page_title    | rd_from   | rd_title
-------------------------------------------------------
476072      | Dog_breeding  |           |
796236      | Dog_breeder   | 796236    | Dog_breeding
16274701    | Dog_breeders  | 16274701  | Dog_breeding
37353772    | Stud_dog      | 37353772  | Dog_breeding
40738352    | Dog_Breeder   | 40738352  | Dog_breeding

I guess I could do it with PHP and several back and forth with the Database (check if it's a main topic, if not grab the main topic, then query like I did above) but I feel like there is a one-query solution?

Initially it seems pretty easy to solve the question, by adding a new criteria to the WHERE clause.

SELECT
    page.page_id,
    page.page_title,
    redirect.rd_from,
    redirect.rd_title
FROM
    redirect
    JOIN page ON page.page_id = redirect.rd_from
WHERE
    redirect.rd_title = 'Dog_breeding' or page.page_title = 'Dog_breeding'

But as Lazhar has pointed out, this only works if the requested text belongs to the main wiki page, not for the redirected pages.

After taking a look at table definitions:

MediaWiki - Manual:page table

  page_is_redirect
  A value of 1 here indicates the article is a redirect; it is 0 in all other cases

MediaWiki - Manual:redirect table

  Contains for each page that is currently a redirect (i.e., not for old revisions which are redirects) the id of the source page and the information about target. The target page may or may not exist.

I've decided to use this field to solve the problem.

First we need to know the title of the main page, the page that is not a redirection. In this question: Dog_breeding

select page.page_title as title from page where page.page_title = 'Dog_breeders' and page_is_redirect = 0
union
select redirect.rd_title as title
from redirect
where redirect.rd_from in (select page.page_id from page where page.page_title = 'Dog_breeders' and page_is_redirect = 1)

Then using the first approach:

SELECT
    page.page_id,
    page.page_title,
    redirect.rd_from,
    redirect.rd_title
FROM
    page
    left JOIN redirect ON page.page_id = redirect.rd_from
    join (select page.page_title as title from page where page.page_title = 'Dog_breeders' and page_is_redirect = 0
          union
          select redirect.rd_title as title
          from redirect
          where redirect.rd_from in (select page.page_id from page where page.page_title = 'Dog_breeders' and page_is_redirect = 1)
          ) cteTitle
WHERE
    redirect.rd_title = cteTitle.title or page.page_title = cteTitle.title;

This is the result:

+----------+--------------+----------+--------------+
|  page_id |  page_title  |  rd_from |   rd_title   |
+----------+--------------+----------+--------------+
|  796236  |  Dog_breeder |  796236  | Dog_breeding |
+----------+--------------+----------+--------------+
| 16274701 | Dog_breeders | 16274701 | Dog_breeding |
+----------+--------------+----------+--------------+
| 37353772 |   Stud_dog   | 37353772 | Dog_breeding |
+----------+--------------+----------+--------------+
| 40738352 |  Dog_Breeder | 40738352 | Dog_breeding |
+----------+--------------+----------+--------------+
|  476072  | Dog_breeding |   NULL   |     NULL     |
+----------+--------------+----------+--------------+

Can check it here: http://rextester.com/OQMV97513

NOTE: There is another solution using pagelinks table in SO


What is an appropriate datatype for an RFID, and is there a standard definition for the format? I have a database that tracks people based on RFID tags embedded in their shoes.  This data arrives in a VARCHAR(MAX) column with other data mixed in, in no particular order.  So far, all the RFID values are formatted like the following:

AnnnnnnnnAnnAAAA  (where A = 0-9  and n = A-Z)

I am writing a report to extract RFIDs from these messages and store them in their own table.  As long as the RFIDs follow this same pattern, I can find them. If the RFID format changes, my extraction process will fail.

Is there a standard format for RFID tags?  What datatype would be best suited to store RFID tags?

I am going with VARCHAR(20) for now.

I don't like answering my own questions (feels like cheating) but here goes:

According to a post on StackOverflow there is no fixed datasize.  RFIDS can vary in both length and format, depending on the industry.

I will stick with varchar(20) for my own purposes, because the batch of RFIDS that we purchased all fall within that size limit.


Why is pg_dumpall throwing an "OID does not exist'' error? When I run pg_dumpall I'm now getting this:

$ pg_dumpall -f may2012
pg_dump: schema with OID 7549789 does not exist
pg_dumpall: pg_dump failed on database "dealermade", exiting

How do I resolve it?

UPDATE

Here is some of the data requested by @depesz:

select relname from pg_class where relnamespace = 7549789;
   relname
--------------
 lotdata
 lotdata_pkey
(2 rows)

do:

select * from pg_class where relnamespace = 7549789;

and:

select * from pg_proc where pronamespace = 7549789;

These are the most likely suspects (there are other objects that can be related to schema).

Make sure you run the queries in dealermade database.

After you will see the results - answer can be obvious (for example: delete from ... where ..), or more tricky - depending on what is the bad object. But at least you will have some data.

Also - try enabling logging of all queries, and rerun pg_dump to see what was the exact query that failed.


ORA-01017 Invalid Username/Password in SQL Developer I installed Oracle 18c XE and SQL Developer 18.4, both are the latest.

Logged in with SYSTEM and created a user vbk in SQL Developer.

I see the user running select * from dba_users, but when I try to connect in SQL Developer, it says

ORA-01017 Invalid Username/Password

I tried to reset the password with SYSTEM user:

ALTER USER vbk IDENTIFIED BY xxx;

and I get

ORA-01918: user 'VBK' does not exist

I see that in the message the user name is upper case, even though the user was created with lower case.

How to connect with the user?

As explained in this answer on SO, if you create a user (or any other identifier) in lowercase, you will have to always quote it when referencing, because by default Oracle (and other SQL databases, except may be Postgres) will convert unquoted identifiers to uppercase.

connect "vbk"/password

or

ALTER USER "vbk" IDENTIFIED BY xxx;


What's the limit of TDE certificates allowed in master database [SQL Server] Let's say I have a SQL instance with 10k databases that I want to enable TDE using different certificates for each database.
Is there any kind of limit to creating certificates on master database?
Will I be allowed to create 10k certificates or more?

Any help is appreciated. Thanks

Microsoft seems not to mention a specific limit for keys or certs, but for databases it is 32767 user databases per instance.

If you consider the TDE key hierarchy all databases have their own DEK, so it's naturally to assume that at least one (and with rollover) at least two keys are possible. (It would not make sense to specify a maximum for databases if they can't use all features like a DEK).

The DEKs might all be encrypted with a single database master certificate (or multiple, but you would not have one for each DB. But even if you would, since those are just stored in master database tables, I would not see a problem (besides heavy CPU usage on startup).


Table from one of the SELECTS cannot be used in field list I'm trying the following query in a MySQL based DB:

SELECT
    alert.name, alert.new_state_date, alert.prev_state_date
FROM
    grafana.alert
WHERE
    alert.id = 1
UNION SELECT
    annotation.epoch
FROM
    grafana.annotation
ORDER BY annotation.id DESC
LIMIT 1;

My intent is to just get the data showing in the same panel so I can stop switching back and forth. However, I get the following error:

  Error Code: 1250. Table 'annotation' from one of the SELECTs cannot be used in field list

To make the two queries compatible for a UNION fill the column list of the second one with NULLs to match the length of that of the first one. To be sure the types of the first column match, you could explicitly cast() them to char. (Optional if they are already of compatible types.)

Also use UNION ALL because UNION (without ALL) tries to eliminate duplicates.

And I guess the ORDER BY was meant for the result FROM grafana.annotatio not for the whole result set. To make MySQL know that, put the second query in parenthesis.

SELECT cast(alert.name AS char),
       alert.new_state_date,
       alert.prev_state_date
       FROM grafana.alert
       WHERE alert.id = 1
UNION ALL
(SELECT cast(annotation.epoch AS char),
        NULL,
        NULL
        FROM grafana.annotation
        ORDER BY annotation.id DESC
        LIMIT 1);

If both queries return one row, you could also cross join them, to have the results side by side.

SELECT *
       FROM (SELECT alert.name,
                    alert.new_state_date,
                    alert.prev_state_date
                    FROM grafana.alert
                    WHERE alert.id = 1) x
            CROSS JOIN (SELECT annotation.epoch
                               FROM grafana.annotation
                               ORDER BY annotation.id DESC
                               LIMIT 1) y;


output parameter in SQL I'm trying to pass a new ID onto another procedure but keep getting a "too many parameters" error.

CREATE OR ALTER PROCEDURE dbo.Process_Insert
  @Name        VARCHAR(50),
  @IntOnly     INT,
  @enabled     BIT,
  @CustomerID  INT,
  @AllowAttr   BIT,
  @IsKit       BIT = 0,
  @Body        NVARCHAR(MAX)=NULL,
  @new_ident   INT NULL OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Process
(Name,
 IntOnly,
 enabled,
 CustomerID,
 AllowAttr,
 IsKit,
 Body)
VALUES
(
  @Name, @IntOnly, @enabled, @CustomerID, @AllowAttr, @IsKit, @Body);
SET @new_ident=SCOPE_IDENTITY();
END;

Then in the below procedure I get "Procedure or function has too many arguments specified". I'm guessing it has something to do with the output parameter, but can't work it out.

CREATE OR ALTER PROCEDURE dbo.Customer_Init
  @CustID INT
AS
DECLARE
  @vendID INT;
EXEC dbo.Process_Insert
     @Name="NameOfEntry",
     @IntOnly=0,
     @enabled=False,
     @CustomerID=@CustID,
     @AllowAttr=False,
     @Body="test",
     @new_ident=@vendID OUTPUT;
SELECT @vendID;

You need to pass @IsKit as well, between AllowAttr and Body.


migration of oracle xe 10 to xe 11 fails with wrong paths used in the import I'm following the documentation to upgrade an OracleXE 10.2 database to OracleXE 11.2. The export succeeded without any problem, the import (on another machine with XE 11) fails though. The log file shows Oracle trying to put files in non-existant paths, and some paths still referring to XE 10:

;;;
Import: Release 11.2.0.2.0 - Production on Vr Mrt 20 13:26:58 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
ORA-39060: table(s) dropped because of conflict with master table
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/<replaced asterisks for stackexchange formatting> full=Y directory=DUMP_DIR dumpfile=DB10G.dmp logfile=expdpDB10G1.log
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file 'C:\OPT\ORACLEXE\ORADATA\XE\UNDO.DBF'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
Failing sql is:
CREATE UNDO TABLESPACE "UNDO" DATAFILE 'C:\OPT\ORACLEXE\ORADATA\XE\UNDO.DBF' SIZE 52428800 AUTOEXTEND ON NEXT 5242880 MAXSIZE 524288000 BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file 'C:\OPT\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\CRPTBL01.DBF'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

My OracleXE 11 is installed like this (by specifying c:\opt in the install wizard):

c:\opt\oraclexe\app\oracle
c:\opt\oraclexe\app\oracle\admin
c:\opt\oraclexe\app\oracle\diag
c:\opt\oraclexe\app\oracle\fast_recovery_area
c:\opt\oraclexe\app\oracle\oradata
c:\opt\oraclexe\app\oracle\product

So not only is the import tool trying to create files in a wrong dir:

ORA-01119: error in creating database file C:\OPT\ORACLEXE\ORADATA\XE\UNDO.DBF'

it is also refering to paths containing 10 versioning:

ORA-01119: error in creating database file 'C:\OPT\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\CRPTBL01.DBF'

I have no idea where Oracle is getting these paths from?

I found out what the problem is, in my XE 10 additional tablespaces were created. When the import into 11 discovers those tablespaces do not exist yet, it tries to recreate them with the statement with which they were created in the first place, in XE 10. That explains where the wrong paths are coming from.
I just copy & pasted the create statements from the log, changed the paths, executed them, repeated the import and now it works.
A bit strange that the documentation doesn't mention this possible hiccup.


SQL2008 - Alter Table Alter Column to increase length results in data loss My understanding was that you could alter the length of a column without losing any data as long as you modified the table via TSQL.

I extended the length of a column to 510 characters from 255, data type and everything else was left alone.

Since pushing this change to prod some users have said their data has gone missing. I'm yet to confirm whether the users are accurate in their assessment but it's a little concerning that I modified the column and they're reporting data loss.

I thought I'd gone about it in a safe and recommended way? Could I have lost data by increasing the column length via TSQL?

Typically you would get an error message and the operation would be prevented, e.g.

create table #name (name nvarchar(20))
insert #name (name) values ('Robert Carnegie')
select * from #name
ALTER TABLE #name ALTER COLUMN name nvarchar(10)  -- error and halts here
SELECT * FROM #name

(1 row(s) affected)
name
--------------------
Robert Carnegie

(1 row(s) affected)

Msg 8152, Level 16, State 13, Line 10
String or binary data would be truncated.
The statement has been terminated.

However, as explained in https://blog.sqlauthority.com/2015/02/14/sql-server-msg-8152-level-16-state-14-string-or-binary-data-would-be-truncated/
that error message isn't produced after the command SET ANSI_WARNINGS OFF.

You could have applied the command to the wrong table or column.

You could have mistaken the column sizes e.g. originally nvarchar(2555) or now nvarchar(51).  Or maybe there had already been an undocumented change in the production database to make the column width 600 characters - or maybe "max" - so, again, you'd be reducing it.  But you should get the error message and the change wouldn't happen.

You could have blocked someone else's transaction storing new data to the table - although I think making a varchar column longer may not even require altering the data pages.  The actual storage wouldn't change.  But - I don't know - SQL Server may check all of the data anyway for any ALTER TABLE ALTER COLUMN command.


Get a record's current partition In SQL Server 2008 R2, how would I determine what partition a record currently lives in?

When you can identify a row or set of rows already in the table, you can do it like this:

SELECT $PARTITION.[PartitionFunctionName](PartitioningColumn)
  FROM dbo.table
  WHERE <clause to identify rows>;

If you have just a value, and it's not necessarily in the table yet, you can tell what partition it would be in given the current state using $PARTITION independently:

SELECT $PARTITION.[PartitionFunctionName](SomeConstant);

(This is also useful shorthand if you are identifying the row(s) in the table using the same column.)


Repair with 1 thread Why would MySQL be using Repair with 1 thread to rebuild the indexes on a table (1 primary key, 2 billion rows) when I have myisam_repair_threads set to 4?

According to the MySQL Documentation on myisam_repair_threads

If this value is greater than 1, MyISAM table indexes are created in
parallel (each index in its own thread) during the Repair by sorting
process. The default value is 1.
Note Multi-threaded repair is still beta-quality code.

As it  says, it is in beta. Allowing for setting the value is evidently not implemented.
UPDATE 2013-05-13 22:24  EDT
Given the table you mentioned in the comment, here is what you could do to see what just what MySQL does to handles the situation. Let's say the table is mydb.mytable and uses the MyISAM storage engine. This is what mechanically has to happen:
use mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new DISABLE KEYS;
INSERT INTO mytable_new SELECT * FROM mytable;
ALTER TABLE mytable_new ENABLE KEYS;
ALTER TABLE mytable_new RENAME mytable_old, mytable_new RENAME mytable;
DROP TABLE mytable_old;

This is as granular as you can get as far as the repair threads go. Each unique index and the PRIMARY KEY is loaded during the INSERT INTO mytable_new SELECT * FROM mytable phase. All the remaining nonunique indexes are built during the ALTER TABLE mytable_new ENABLE KEYS; phase.
Since Oracle is concentrating on the development of InnoDB, I double if they will get around to implementing myisam_repair_threads as a number beyond 1. Why? Think about. Index pages are written sequentially with myisam_repair_threads set to 1. If myisam_repair_threads was greater 1, each set of index pages per index would be written in an interleaved fashion.
This would make the index fragmented. Of course, the solution would be to write each index's set of sequential pages in eight(8) different places. They would eventually have to be written contiguously into one common index space (in the case of the MyISAM table, that would be the .MYI file). Until Oracle comes up with a way to create nonunique indexes in parallel, you will have to settle for one thread.


Check if column has same value in any row I have the following case.
+-----------+--------+-------------+
| ArticleID | UserID | IsConnected |
+-----------+--------+-------------+
|         1 |     25 | True        |
|         1 |     45 | True        |
|         2 |     55 | False       |
+-----------+--------+-------------+

So I need to create a View who will return True if there is more than 1 record in the table with same ArticleID.
To make this easier the table looks the same, but without IsConnected column.

Something like this, may be
select
 article_id, user_id,
 case
   when count(1) over (partition by article_id) > 1 then 'true'
   else 'false'
 end is_connected
from something


How do I turn off MariaDB GTID to revert back to binlog replication? We have found concerns with GTID on MariaDB related to our multi-tenant MyISAM databases. It sounds like there are problems with temporary table replication so we'd like to revert away from GTID. How do I do this?

On each slave, run this. It'll do it without downtime and very little replication delay.

stop slave; change master to master_use_gtid=no; start slave;

You can watch show slave status \G, specifically "Using_Gtid, "Master_Log_File" and "Read_Master_Log_Pos". Assure the first is "No" and the other two are climbing after this command.


Is it possible to schema-qualify an operator? Let's say I create an extension, like PostGIS, in the schema postgis,

CREATE EXTENSION postgis WITH SCHEMA postgis;

Is it possible to fully qualify an operator like && in the below example,

SELECT box2d && ST_MakePoint(5,5)
FROM postgis.box2d(postgis.ST_MakeEnvelope(0,0,10,10));

But "postgis.&&" and even "postgis".&& and "postgis"."&&" is not working for me. The docs on "Operator Type Resolution" seem to hint at being possible,

  If a qualified operator name was given, only operators in the specified schema are considered.

Put another way, can I use an operator that is "not visible".

SELECT pg_type_is_visible(
  'postgis.&&(postgis.geometry,postgis.geometry)'::regoperator
);

Clearly, I can add postgis to my search_path but I'm looking to leave that unmodified and in ever other context we can in fact qualify everything.

Be aware that the OPERATOR construct (like OPERATOR(postgis.&&)) is not 100 % identical to the operator itself. Operators lose their implicit precedence and fall back to default operator precedence, which can have sneaky side effects. Consider:

SELECT 3 + 3 * 2                        -- 9
     , 3 + 3 OPERATOR(pg_catalog.*) 2;  -- 12

There is no effect for OPERATOR(postgis.&&) as && has default operator precedence to begin with.

Related:

Use PostgreSQL builtin operator <@ after including extension intarray
GIN index on smallint[] column not used or error “operator is not unique”

Another limitation: the OPERATOR construct does not work for SQL syntax elements like AND, OR, BETWEEN etc., which are also sometimes called "operators" (and have their own operator precedence). Only for operators listed in the system catalog pg_operator.