Execute PL/SQL script block in C# I am trying to execute a PL/Sql block by using the OracleClientProvider in .Net . The language  I am using is c#, the DB is oracle10g

What I am actually doing is the following :

  //ConnectionSting is the connection String
  OracleConnection connection = new OracleConnection(connectionString);
  OracleCommand cmd = new OracleCommand();
  cmd.Connection = connection;

  // Open the connection
  connection.Open();

  //queryFile contains the PL/SQL Script I am trying to execute;
  String queryFile = ConfigurationManager.AppSettings["MasterDbScript"];
  String dataInFile = new StreamReader(queryFile).ReadToEnd();
  cmd.CommandText = dataInFile;

  connection.Open();
  cmd.ExecuteNonQuery();
  //close the connection
  connection.close();

The PL/SQL block executes properly when I run it through ORACLE client but over here it throws an error ORA-00922: missing or invalid option

I want to ask :
1. Are the scripts executed in a different way from the normal query?
2. What am I doing wrong ?

Suggestions/Answers

Thanks.

From my limited understanding, blocks should be embedded in the C# or put in a package and called.  I've seen it like this:

@"
BEGIN
   SchemaName.PackageName.ProcedureName(p_LoginID => :loginID,
      p_Password => :password, p_IPAddr => :ipAddr)";
END;”.Replace(Environment.NewLine, “\n”);


INSERT BULK in SQL Profiler without values I started SQL Profiler to catch actions that are doing on server, and among them I found one row where is stated:

SQL BatchStarting: insert bulk table_name <column_list>

but there is no values?

How to see what database accept from application?

I don't believe there's a Profiler trace event that shows this information.

Insert Bulk Background

Behind the scenes, the client performs a bulk load by sending a TDS SQL Batch message containing an INSERT BULK statement which specifies the destination table and columns as well as any bulk load options requested by the client (e.g. "keep NULLs," "check constraints" or "fire triggers").

However, as you've observed, this INSERT BULK statement doesn't contain the values to insert. Instead, these are transmitted by one or more TDS Bulk Load BCP messages that follow the SQL Batch message. The first Bulk Load BCP message starts with metadata describing the shape of the data set to come. Following this metadata comes the data to insert, encoded in an efficient binary format. (With minor exception, the format used is identical to the binary format the server uses to send results to the client.)

Unfortunately, Profiler doesn't provide an event that displays Bulk Load BCP messages. :-(

Reference

Possibly Helpful: A SO answer which provides clues on using Profiler to get a sense for what a bulk insert is touching. (Thanks to @SqlWorldWide for mentioning this answer in a comment.)


MySQL - How to make a DATEDIFF in years? This is my first post (and English is not my mother tongue) so I hope I won't screw this post up.

I am new to MySQL but I learned a lot with 100+ youtube tutorials and your numerous posts. I designed my database using PHPmyadmin and MySQL Workbench.

In my table subventions (grants), I created a trigger to be able to calculate the annual amount of money of a grant, using my columns subventions.début (beginning), subventions.fin (end) and subventions.montant (amount).

As DATEDIFF only gives me an answer in days, I naively thought that I only had to divide it by 365 days to obtain my answer. It works if my beginning date is the first of january and the end date the 31st of December, but does not if it stars somewhere else during the year. Here is my trigger:

SET NEW.montant_annuel = NEW.montant / (datediff(NEW.fin,NEW.début)/365)

Here is my table and the result in the 'montant_annuel' column:

If I use 365.25 (which is the "real" number of days in a year), it does not work either.

Thanks! :)

Yes, TIMESTAMPDIFF is the best approach:

select TIMESTAMPDIFF(year,'2011-01-01', now() ) ;  --> 4
select TIMESTAMPDIFF(year,'2011-08-28', now() ) ;  --> 4
select TIMESTAMPDIFF(year,'2011-09-01', now() ) ;  --> 3


SQL Server storage - evaluate the number and types of read write operations for the existing deployment Training for SQL Server, one of the questions in Learning material says:
Before migrating on-premise SQL Server deployment (single server) to Azure VM, you need to evaluate "number and types of read/write operations" of the existing deployment
Which tool should you use ?
Options (pick correct one)

SQL Server Profiler trace using TSQL template
SQLIOSim
Database Engine Tuning Advisor
SQL Disk Usage Standard Report

Best probably would have been "Performance Monitor", but its not in the list
I think correct one is 4) "SQL Disk Usage Standard Report", but Learning Material says that correct  answer is 2) "SQLIOSim"
But, In my opinion, SQLIOSim is for testing the disk subsystem, it is not for measuring existing SQL Server instance metrics
Who is right - me, Learning material, or you (your option) ?
And what they mean by "types" of read/write operations ?

Who is right - me, Learning material, or you (your option ) ?

None of the options is correct.  The "Disk Usage Standard Report" is a database-level report that displays disk space utilization, not IO.

And what they mean by "types" of read/write operations ?

Judging that the "correct" answer was SQLIOsim, it probably meant sequential/random IO size that you can measure with SQLIO or Diskspd.
Here's what they're getting at:
When preparing for a SQL Server migration, a simple and effective migration plan is to provide equivalent-or-better resources in the new environment.  Alternatively you can attempt to measure the SQL Server resource utilization and base size the new environment off of that.
Of the two approaches the first is simpler and safer, and for a migration to an Azure VM, where you get to choose the VM family, size, and storage configuration, and where subsequent right-sizing is simple, that's the approach I would take.
Use Diskspd or SQLIO to measure the IO system, and use the system specs or standard benchmarks to measure the CPU and Memory capabilities, and spec the new enviornment from that.  Very simple, and can be done by a sysadmin, developer, or DBA.
The problem with this approach is that sometimes the existing deployment is unnecessarily fast, and results in an unnecessarily expensive new environment.  Then I would dig in with DBA and admin tools and adjust the estimate based on the actual utilization.
This happens frequently for storage where either:

There's a high-end shared SAN device providing storage for multiple servers.  You might measure a very high peak throughput on such a device, and would be unlikely to want to provide an Azure Disk configuration that matches it for each server.

SQL Server databases are stored on local NVMe flash storage and replicated with AlwaysON Availability Groups.  Storing your databases on the local flash drive is not supported for SQL Server on Azure VMs (although that's how Azure SQL Database Premium/Business Critical runs).

For that you would use something like the Azure SQL Database DTU Calculator, which will collect performance counters and create an estimate of the cpu and IO throughput required.
For IO I would start with:
Logical Disk - Avg. Disk Bytes/Read
Logical Disk - Avg. Disk Bytes/Write
Logical Disk - Avg. Disk sec/Read
Logical Disk - Avg. Disk sec/Write
Logical Disk - Disk Reads/sec
Logical Disk - Disk Writes/sec
Logical Disk - Disk Read Bytes/sec
Logical Disk - Disk Write Bytes/sec

Focusing mainly on the log and tempdb volumes, unless you see consistently significant data file read IO.  But you still have to imagine how the workload would behave if you reduced peak capacity in the new environment.  Do you really care if backup takes a little longer?  Sometimes yes, sometimes no.  Is the CPU maxed only during an overnight batch operation, and do you really care if that takes a bit longer?
The additional data collection and the need to interpret it with the understanding of how SQL Server works and in the context of the business needs of the application create significant additional complexity.


'skip rows' in a flat file source does not work as advertised In SSIS(2012 specifically) there is an option in the Flat File Datasource to skip a number of lines at the beginning of a file. The preview appears correctly. However, when trying to execute the data flow, it appears that those rows are still being validated; so that if they contain invalid data(blank rows), the datasource fails. This prevents using a conditional switch to remove rows, since the data flow never starts.

Is this accurate description of the normal functioning and is there any way to work around it?

Sample data looks like -

COL1|COL2|COL3

abc|123|10
def|456|20
ghi|789|30

You're reading it backwards. You have instructed it to skip the first row of data and it does that. It then looks in the next row for column headers, which it won't find because it's blank. The actual data should be found on the third row. It tries to clue you in when you look at your preview. I create two files, one with your data and the other with the first line blank and the headers in the second row.

Notice we are previewing rows 2-4

Contrast that with your blank in between header and data

Still previewing rows 2-4 but the default header names are the first rows of data. Not quite what we're expecting.

When I created a simple Data flow using the two connection managers, it fails (as expected) on the BlankSecondRow as the above definition performs some validation that the header names are actually there. If I uncheck "Column names in the first data row" and up the "Header Rows to Skip to" 2, then it will work.


Updating multiple mysql rows where column has specific value I have 1 CSV file with 50,000 rows of data and 2 columns.

The columns are user ID's and twitter profile photos.

24525,https://pbs.twimg.com/profile_images/666407537084796928/YBGgi9BO.png
27753,https://pbs.twimg.com/profile_images/2284174752/64pe9ctjko2omrtcij7a.png
24434,https://pbs.twimg.com/profile_images/638751551457103872/KN-NzuRl.png
19911,https://pbs.twimg.com/profile_images/1240079072/logo-mysql-170x170.png

I have a MYSQL database and am interested in updating 1 table that looks like this.

umeta_id  - primary key ( we do not have this in our csv )
user_id -  This is the first column in our csv
meta_key -  We want to update when meta_key is 'twitter_photo'
meta_value - twitter profile photos go here

I can update 1 row with query like this

UPDATE `wp_usermeta`
SET meta_value='https://pbs.twimg.com/profile_images/666407537084796928/YBGgi9BO.png'
WHERE meta_key LIKE 'twitter_photo'
AND user_id='24525';

I have been struggling to figure out how to do multiple rows.  I tried this but it does not work.

 UPDATE `wp_usermeta` SET `meta_value` = CASE `user_id`
                      WHEN 24525 THEN 'https://pbs.twimg.com/profile_images/666407537084796928/YBGgi9BO.png'
                      WHEN 27753 THEN 'https://pbs.twimg.com/profile_images/2284174752/64pe9ctjko2omrtcij7a.png'
                      WHEN 24434 THEN 'https://pbs.twimg.com/profile_images/638751551457103872/KN-NzuRl.png'
                      ELSE `meta_value`
                    END,
         WHERE `user_id` IN (24525, 27753, 24434) AND `meta_key` LIKE 'twitter_photo';

Any help in the right direction would be much appreciated.

create table temp with two fields your have in your csv and

load data infile '/path/your.csv' into table temp FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;

then perform join

update wp_usermeta, temp set wp_usermeta.meta_value=temp.meta_value where wp_usermeta.user_id=temp.user_id;


CTE part returns stores and dates that have not sold this products '001,002,003', the second part returns only those that have not sold any of them.

NOTE: COUNT(*) = 3 because you're looking for 3 products.

WITH ct AS
(
  SELECT
    store_id,
    dt,
    product_code
  FROM
    DAILY_SALES_RECORDS
  WHERE
    product_code IN ('001', '002', '003')
  GROUP BY
    store_id,
    dt,
    product_code
  HAVING
    SUM(sales) = 0
)
SELECT
  store_id,
  dt
FROM
  ct
GROUP BY
  store_id,
  dt
HAVING
  COUNT(*) = 3;

store_id | dt
-------: | :------------------
       1 | 01/01/2019 00:00:00
       3 | 01/01/2019 00:00:00
       1 | 03/01/2019 00:00:00

db<>fiddle here

As ypercube has pointed out in comment the easiest, grouping by store_id and date returning those that has sold nothing:

SELECT
    store_id,
    dt
  FROM
    DAILY_SALES_RECORDS
  WHERE
    product_code IN ('001', '002', '003')
  GROUP BY
    store_id,
    dt
  HAVING
    SUM(sales) = 0 ;


does INSERT INTO ... ON DUPLICATE KEY UPDATE executes even if the row has not changed? Let me explain :

Let us assume we have a database "user" :

ID   Name   Surname
===================
1    John   Doe
2    Foo    Bar
3    Foo    Baz

Now I wanna change all these three rows. I will throw the following insert :

INSERT INTO user (ID, Name, Surname) VALUES (1, 'John', 'Conor') ON DUPLICATE KEY UPDATE ID = 1, Name = 'John', Surname = 'Conor';
INSERT INTO user (ID, Name, Surname) VALUES (2, 'Foo', 'Bar') ON DUPLICATE KEY UPDATE ID = 2, Name = 'Foo', Surname = 'Bar';
INSERT INTO user (ID, Name, Surname) VALUES (3, 'Foo', 'Baz') ON DUPLICATE KEY UPDATE ID = 3, Name = 'Foo', Surname = 'Baz';

Question

Does the command ON DUPLICATE KEY execute itself if the row before the update was the same ? Meaning, does it will execute for the rows with ID equal to 2 and 3 even if it should not executes (because it is useless) ? I ask this question for performance issues.

MySQL executes ON DUPLICATE KEY UPDATE the same way it executes UPDATE statements:

It checks the contents of each row (and columns) to be updated and if they are identical to the supplied, it does not do any update. It still has to check them though.

So the result in your case (where you send 3 rows to be inserted) would be:

3 rows to be inserted
3 key collisions

0 rows inserted
3 to be updated

1 row actually updated
2 identical (no update)

A few things about your syntax:

you don't need to update the UNIQUE key.
you can use VALUES(column) in the UPDATE part.
you can combine the multiple inserts into one:

INSERT INTO user
  (ID, Name, Surname)
VALUES
  (1, 'John', 'Conor'),
  (2, 'Foo',  'Bar'),
  (3, 'Foo',  'Baz')
ON DUPLICATE KEY UPDATE
  Name = VALUES(Name),
  Surname = VALUES(Surname) ;


Optimizing SSIS Packages I have a SSIS package that loads data in an Access DB to tables on a SQL Server DB.

Currently I double click the package and execute it. There are times when there is a lot of data to load.

My question is there a way I can optimize the SSIS package so that it loads faster?

You'd have to optimize the source and I'm not sure what optimizations are available for Access - a quick Google search reveals this and something about indexes here but I'm honestly clueless about how Access indexes work.

It occurs to me that you could create a linked server to the Access database and then just schedule a job to run a query against the link.

EXEC master.dbo.sp_addlinkedserver
    @server = N'MyAccessSource',
    @srvproduct=N'',
    @provider=N'Microsoft.Jet.OLEDB.4.0',
    @datasrc=N'C:\my\accessdb.mdb';

or even query the source using OPENROWSET

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\my\accessdb.mdb';Table);
GO

or

select * from
  openrowset('Microsoft.ACE.OLEDB.12.0', 'C:\my\accessdb.mdb',
  'select * from Table');
GO

I guess this would save you the overhead of firing up SSIS packages and worrying about data flow tasks.


mysqldump vs LOAD DATA INFILE Why would you ever use mysqldump to repopulate a table over load data infile?

Here's the back story to my question.  We had a table that was incorrectly partitioned.  It was supposed to be partitioned in 10M row chunks by PK.  After 90M => 100M it started getting partitioned in 100M row chunks (100M=>200M etc).

The most recent partitions had 135M rows between them and we decided it was time to bite some downtime and fix this mess.

The game plan was basically:

1) mysqldump >= 100M

2) drop partitions >= 100M

3) Create desired partioned ranges

4) readd via the mysqldump files

These dumps were done in 10M chunks so we could reload into the downed master and slaves simultaneously and do some sanity checking in between so we didn't get too far in before realizing we were screwed.  This was an append only table that didn't mutate so we were able to have the historical dumps done before hand and scp'd to local copies before the real downtime had to start.  Because of this we added --skip-disable-keys since our version of mysql doesn't allow you to do this per partition and didn't want to continuous rebuild after each chunk so that could have something to do with the performance diff I'm about to lay out.

Some benchmark tests before hand left us with an estimated 90 minutes of downtime.  We were wrong; the mysqldump reloads were taking about 3-4 times longer than expected.

We had some time to just twiddle our thumbs.  Part of the contingency plan was to not drop one of the slaves partitions until the master and everyone else was rebuilt "just in case".  While the rebuild was going we decided to do a test, select * into outfile from the untouched slave for some segments we'd yet to get to, then reload those w/ load data infile.

We made our dump, gzip'd it and copied it over to the machines in process of rebuilding.  To save some overhead of having to uncompress it then read it again we gzip -dc'd it into a named pipe and then loaded from them.

The load data approach was finishing in about 4 minutes/chunk rather than the 12-15 minutes the mysqldump reload was taking.

I know the manual says this can be faster for larger loads but this left me struggling for a reason why we should use mysqldump if our schema is already in place?

P.S. I'm aware of reorganize partition but have found this dump/reload into alteeed schema to be more performant in the past.

If you have a schema already in place, you should use mysqldump options to create the dump of the data to perform in the inserts only

mysqldump -h... -u... -p... --no-create-info --databases ... > MySQLData.sql

You should also make sure to raise the bulk_insert_buffer_size (default is 8M) on all DB Servers to accommodate large extended inserts. This will also help LOAD DATA INFILE if loading non-empty tables.

You are going to have to also adjust your max_allowed_packet (default is 1M).

Try these settings for starters:

[mysqld]
max_allowed_packet=256M
bulk_insert_buffer_size=256M

UPDATE 2011-10-11 06:53 EDT

Regardless of how beefy the hardware is, how much RAM you have allocated, how well tuned the OS is, and how current the version of MySQL is, MySQL will only perform as well as it is configured.

Example: If you have a swimming pool that can hold 10,000 gallons of water, you have a truck holding 10,000 gallons of water, and the hose on the truck is only as big as a straw, you can only push but so much water through the straw to fill the pool. Getting a bigger truck or getting a faster water pump simply will not improve things. You must exchange the hose for a much bigger hose to accommodate more water, thus, more throughput.

In like fashion, MySQL 5.5 out-of-the-box does not come fully tuned.

Example #1: MySQL 5.5 comes with semisynchronous replication. By default, it is disabled. You must do the two step process of starting mysql, runnning INSTALL PLUGIN on master and slave modules, shutdown mysql, add timing and activation options to my.cnf for the semisych features, then starting up mysql for the second time. Only then will semisync replication work.

Example #2: The bulk insert buffer by default is 8M. The bulk insert buffer will not grow because of the presence of any specific hardware or software. It stays 8M until you increase it. It is possible to increase it by either adding it to my.cnf and restarting mysql or running SET bulk_insert_buffer_size = 268435456; to set it to 256M within a session and then load the mysqldump within that same session.

Example #3: MySQL 5.5 is fully capable of engaging multiple CPUs. By default, the features for engaging multiple CPUs is disabled. They require tuning because although MySQL 5.5 is multicore ready, MySQL 5.5 is only as multithreaded as you configure it.

Conclusion: You must configure MySQL to recognize that it has beefy hardware, more RAM, and a cooperative OS at its disposal.


MYSQL REPLACE string content with field value How can I REPLACE a string value with a other field content?

SELECT REPLACE(html_input,'!value',field_value) FROM table

Value of html_input is <input type='text' value='!value'>

Value of field_value is test

Expected result:

<input type='text' value='test'>

String literals must be enclosed by quotes '.

Quote symbols within a literal must be quoted by slash as \'.

SELECT REPLACE(html_input,'!value',field_value)
FROM table
/* WHERE html_input = '<input type=\'text\' value=\'!value\'>'
     AND field_value = 'test';
*/


How do you extend the tablespace when using innodb_file_per_table? With innodb_file_per_table off, you can create multiple tablespaces on multiple devices if necessary to manage growth, balance I/O, etc.

With the option on, how do you control growth of the files? Do they autoextend? And can you set a maximum and then extend the tablespace for a given table onto another filesystem if necessary?

This is interesting because last month (July 25, 2012 15 days ago) some asked a similar question about extending the system tablespace file ibdata1 : Database Design - Creating Multiple databases to avoid the headache of limit on table size

Please read that link and see if you would like to extend ibdata1 the way I detailed

To be totally honest with you, storing ibdata across separate volumes from the datadir is a bad idea , and storing .ibd files is even worse, because of

MetaData Issues : I wrote about this as well

Mar 25, 2012 : Why does InnoDB store all databases in one file? (See WARNING)
Oct 16, 2011 : Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql
Sep 28, 2011 : How to Recover an InnoDB table whose files were moved around

Performance Issues : I wrote about that back in August 2011

MySQL InnoDB data file directly on a disk slice (fixed-size)?

You will improve things if you go with innodb_file_per_table. Why?

You only need one ibdata1 file
No data would reside in ibdata1
All data and index pages would not spread across multiple tablespaces

Controlling individual tablespace growth is rather simple. You must schedule proper maintenance windows for this:

For example, to shrink an InnoDB table named mydb.mytable simply run

ALTER TABLE mydb.mytable ENGINE=InnoDB;

With innodb_file_per_table on, the table shrinks.

With innodb_file_per_table off, ibdata1 just grows rapidly.

There is no autoextend feature for .ibd files. That only applies to ibdata1. In light of this, the maximum size of an InnoDB table stored in a .ibd file would be OS dependent:

In ext3, an InnoDB table can go to 2TB
In ext4, an InnoDB table can go to 16TB

To be more blunt, you should never attempt to spread .ibd to other volumes. Percona has strongly denounced this : http://www.mysqlperformanceblog.com/2010/12/25/spreading-ibd-files-across-multiple-disks-the-optimization-that-isnt/


Fuzzystrmatch Postgresql similarity func with array How do I pass an array to the SIMILARITY ()function

I am looking for something similar to the following:

SELECT denomination, is_active
   FROM MyTable WHERE
SIMILARITY(denomination, ['Coke', 'Coque']) > 0.4;

The similarity function does not take in array as argument so it will not do what you want out of the box

You have a couple of options create a wrapper function that takes in array that the calls Similarity and returns a value to compare against.

2nd option is do a cross join to the array like so

SELECT denomination, is_active , unnest
    FROM mytable  cross join (
        select * from
        unnest(array['coke', 'coqe'])
    ) dd
    where SIMILARITY(denomination, unnest) > 0.4;


If function not exist then create function in SQL server If not  Exists (Select * from sys.objects where name ='FNLX_getDate'  and type =N'FN')

    Create function [dbo].[FNLX_getDate]() returns Datetime
    as
    Begin
    .................
    End

When I execute the above script I get the error

  Incorrect syntax near the keyword 'function'.

What can be wrong?

Using Microsoft SQL Server to execute above script.

Unfortunately you can't use create function like that. It must be the first statement in a batch.

You can try dynamic SQL instead like:

If not Exists (Select * from sys.objects where name ='FNLX_getDate'  and type =N'FN')
    BEGIN
        DECLARE @sql NVARCHAR(MAX);
        SET @sql = N'CREATE FUNCTION ...';
        EXEC sp_executesql @sql;
    END

Or the classic way (like you can see if you generate scripts with SSMS) : drop and create:

If Exists (Select * from sys.objects where name ='FNLX_getDate'  and type =N'FN')
    drop function [dbo].[FNLX_getDate]
GO
Create function [dbo].[FNLX_getDate]() returns Datetime
as
Begin
.................
End

Notice the GO SSMS batch separator.


SARGable WHERE clause for two date columns I have what is, to me, an interesting question on SARGability. In this case, it's about using a predicate on the difference between two date columns. Here's the setup:

USE [tempdb]
SET NOCOUNT ON

IF OBJECT_ID('tempdb..#sargme') IS NOT NULL
BEGIN
DROP TABLE #sargme
END

SELECT TOP 1000
IDENTITY (BIGINT, 1,1) AS ID,
CAST(DATEADD(DAY, [m].[severity] * -1, GETDATE()) AS DATE) AS [DateCol1],
CAST(DATEADD(DAY, [m].[severity], GETDATE()) AS DATE) AS [DateCol2]
INTO #sargme
FROM sys.[messages] AS [m]

ALTER TABLE [#sargme] ADD CONSTRAINT [pk_whatever] PRIMARY KEY CLUSTERED ([ID])
CREATE NONCLUSTERED INDEX [ix_dates] ON [#sargme] ([DateCol1], [DateCol2])

What I'll see pretty frequently, is something like this:

/*definitely not sargable*/
SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [#sargme] AS [s]
WHERE
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) >= 48;

...which definitely isn't SARGable. It results in an index scan, reads all 1000 rows, no good. Estimated rows stink. You'd never put this in production.

It would be nice if we could materialize CTEs, because that would help us make this, well, more SARGable-er, technically speaking. But no, we get the same execution plan as up top.

/*would be nice if it were sargable*/
WITH    [x] AS ( SELECT
                * ,
                DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2]) AS [ddif]
               FROM
                [#sargme] AS [s])
     SELECT
        *
     FROM
        [x]
     WHERE
        [x].[ddif] >= 48;

And of course, since we are not using constants, this code changes nothing, and is not even half SARGable. No fun. Same execution plan.

/*not even half sargable*/
SELECT
    * ,
    DATEDIFF(DAY, [s].[DateCol1], [s].[DateCol2])
FROM
    [#sargme] AS [s]
WHERE
    [s].[DateCol2] >= DATEADD(DAY, 48, [s].[DateCol1])

If you're feeling lucky, and you're obeying all the ANSI SET options in your connection strings, you could add a computed column, and search on it...

ALTER TABLE [#sargme] ADD [ddiff] AS
DATEDIFF(DAY, DateCol1, DateCol2) PERSISTED

CREATE NONCLUSTERED INDEX [ix_dates2] ON [#sargme] ([ddiff], [DateCol1], [DateCol2])

SELECT [s].[ID] ,
       [s].[DateCol1] ,
       [s].[DateCol2]
FROM [#sargme] AS [s]
WHERE [ddiff] >= 48

This will get you an index seek with three queries. The odd man out is where we add 48 days to DateCol1. The query with DATEDIFF in the WHERE clause, the CTE, and the final query with a predicate on the computed column all give you a much nicer plan with much nicer estimates, and all that.

Which brings me to the question: in a single query, is there a SARGable way to perform this search?

No temp tables, no table variables, no altering the table structure, and no views.

I'm fine with self-joins, CTEs, subqueries, or multiple passes over the data. Can work with any version of SQL Server.

Avoiding the computed column is an artificial limitation because I'm more interested in a query solution than anything else.

Just adding this quickly so it exists as an answer (though I know it's not the answer you want).

An indexed computed column is usually the right solution for this type of problem.

It:

makes the predicate an indexable expression
allows automatic statistics to be created for better cardinality estimation
does not need to take any space in the base table

To be clear on that last point, the computed column is not required to be persisted in this case:

-- Note: not PERSISTED, metadata change only
ALTER TABLE #sargme
ADD DayDiff AS DATEDIFF(DAY, DateCol1, DateCol2);

-- Index the expression
CREATE NONCLUSTERED INDEX index_name
ON #sargme (DayDiff)
INCLUDE (DateCol1, DateCol2);

Now the query:

SELECT
    S.ID,
    S.DateCol1,
    S.DateCol2,
    DATEDIFF(DAY, S.DateCol1, S.DateCol2)
FROM
    #sargme AS S
WHERE
    DATEDIFF(DAY, S.DateCol1, S.DateCol2) >= 48;

...gives the following trivial plan:

As Martin Smith said, if you have connections using the wrong set options, you could create a regular column and maintain the computed value using triggers.

All this only really matters (code challenge aside) if there's a real problem to solve, of course, as Aaron says in his answer.

This is fun to think about, but I don't know any way to achieve what you want reasonably given the constraints in the question. It seems like any optimal solution would require a new data structure of some type; the closest we have being the 'function index' approximation provided by an index on a non-persisted computed column as above.


How can I use an environment variable in a Postgres function? Let's say I have a windows user environment variable SOME_STRING.

Is it possible to use its value in a PL/pgSQL function?

If not, do I need to use an untrusted language (like PL/pythonu) or can I use a trusted one?

I am on PG 9.2

You can not access a environment variable directly; but you can access a custom configuration setting and set that to a value from the environment at startup.  For example, you can pass the follow startup option using PGOPTIONS:

PGOPTIONS="-c 'custom.some_string=${SOME_STRING}'"

Note: the dot (.) in the name is important.

Then retrieve the value using current_setting:

SELECT current_setting('custom.some_string');

(I have tried this on Linux and found it to be working, it should be the same for Windows, but may need some slight modification to cater for that particular platform.)


Adding Users after Database Restore I have to restore our production database to a test instance on a daily bases.  This test instance gives people access to the data that we do not want changing production data.  When I restore the database there are logins in the test instance with access to the database that do not have a login or user account in production instance.  So I have a script that goes and adds each user to the users list in the restored database on the test instance.  This list of users changes more than I would like. So I am always making changes to the script.

Does any one know a way to pull a list of instance logins with access to the restored database and add them to the user list of the restored database.  I have to add them with read, write and execute permissions

Thanks

Create a table in your test database with the users that should have access, create a restore script that copies that table into a temp table, do your restore, recreate the table in your test database, use that table to create the appropriate access permissions.


How does SSMS get a list of files in a backup? When restoring a database that has multiple files, SSMS gives you the option to restore as other paths and file names. How does it retrieve the list of files from the backup? Can I do that in a SQL script? C#? PS?

Here is a simple script that might help you.

Works for me on SQL Server 2008 sp2

select @@version
--Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
--Sep 16 2010 19:43:16
--Copyright (c) 1988-2008 Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

Part 1:

--------------------------------------------------------------
-- first get the list of all backups in your server
--------------------------------------------------------------

declare @backup_type CHAR(1) = 'D' ;    --'D' full, 'L' log

with Radhe as (
    SELECT  @@Servername as [Server_Name],
        B.name as Database_Name,
        ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))),
               'NEVER')
            as DaysSinceLastBackup,
        ISNULL(Convert(char(11), MAX(backup_finish_date), 113)
                   + ' '
                   + CONVERT(VARCHAR(8), MAX(backup_finish_date), 108),
               'NEVER')
            as LastBackupDate
       ,BackupSize_GB = CAST(COALESCE(MAX(A.BACKUP_SIZE), 0)
                                 /1024.00/1024.00/1024.00
                             AS NUMERIC(18,2))
       ,BackupSize_MB = CAST(COALESCE(MAX(A.BACKUP_SIZE), 0)
                                 /1024.00/1024.00
                             AS NUMERIC(18,2))
       ,media_set_id = MAX(A.media_set_id)
       ,[AVG Backup Duration] = AVG(CAST(DATEDIFF(s, A.backup_start_date,
                                                    A.backup_finish_date)
                                         AS int))
       ,[Longest Backup Duration] = MAX(CAST(DATEDIFF(s, A.backup_start_date,
                                                         A.backup_finish_date)
                                             AS int))
       ,A.type
   FROM sys.databases B
       LEFT OUTER JOIN msdb.dbo.backupset A
           ON A.database_name = B.name
           AND ( @backup_type IS NULL
                 OR A.type = @backup_type )
   GROUP BY B.Name, A.type
)
SELECT r.[Server_Name]
    ,r.Database_Name
    ,[Backup Type] = r.type
    ,r.DaysSinceLastBackup
    ,r.LastBackupDate
    ,r.BackupSize_GB
    ,r.BackupSize_MB
    ,F.physical_device_name
    ,r.[AVG Backup Duration]
    ,r.[Longest Backup Duration]
FROM Radhe r
    LEFT OUTER JOIN msdb.dbo.backupmediafamily F
        ON R.media_set_id = F.media_set_id
ORDER BY r.Server_Name, r.Database_Name ;

Part 2:

--------------------------------------------------------------
-- now with any of the backup files listed above
-- you can add them in the place below and select from @fileListTable
--------------------------------------------------------------

declare @fileListTable table
(
    LogicalName          nvarchar(128),
    PhysicalName         nvarchar(260),
    [Type]               char(1),
    FileGroupName        nvarchar(128),
    Size                 numeric(20,0),
    MaxSize              numeric(20,0),
    FileID               bigint,
    CreateLSN            numeric(25,0),
    DropLSN              numeric(25,0),
    UniqueID             uniqueidentifier,
    ReadOnlyLSN          numeric(25,0),
    ReadWriteLSN         numeric(25,0),
    BackupSizeInBytes    bigint,
    SourceBlockSize      int,
    FileGroupID          int,
    LogGroupGUID         uniqueidentifier,
    DifferentialBaseLSN  numeric(25,0),
    DifferentialBaseGUID uniqueidentifier,
    IsReadOnl            bit,
    IsPresent            bit,
    TDEThumbprint        varbinary(32) -- remove this column if using SQL 2005
) ;

insert into @fileListTable
    exec('restore filelistonly from disk = ''YourBackupFile.bak''') ;

select * from @fileListTable ;

Have a look at these other answers too:

How to use variables with RESTORE FILELISTONLY

SQL Server “RESTORE FILELISTONLY” Resultset


SQL server 2012 inner join estimated number of rows issue I have a simple join like the one below:

select *
from fact_sales f
join dim_company d on f.company_SK = d.company_SK

The fact table contains a little over 500 million records (with NC columnstore), the query will return all of them, however, the estimated number of rows after the join is only 300 million. Up till the hash join, the estimated number of rows are correct, only after the join it drops to 300 million. Here's the estimated plan for the query:

I've updated the stats on the SK column used in the join in both the fact table (with fullscan) and the dimension table, here's the histogram for each:

This issue only seems to occur for a couple dimension tables in the database, joining the other dimension tables doesn't produce the same type of cardinality estimate issue - any suggestions on how to possibly fix this or further investigate?

If I add a where clause to the query, it correctly estimates the number of rows before/after the join, e.g.

select *
from fact_sales f
join dim_company d on f.company_SK = d.company_SK where company_SK = 1

will estimate 467,583,000 rows coming out of the join, which matches what's in the histogram.

The issue seems to only occur when I don't have any filter in the query. It's causing a problem in a bigger query (sort spill). I've narrowed it down to this particular join.

I do have an FK constraint, but they have been turned off (WITH NOCHECK) on the fact table (we were told to turn them off so ETL can go faster). Unfortunately, turning the FK back on is not an option :(

Update: enabling trace flag 2301 solved the issue :p

I was able to reproduce your issue in SQL Server 2014 with the legacy CE and TF 4199 enabled. I used a rowstore fact table because I have little experience with columnstore.

For your query with the filter, the query optimizer rewrites the query to something like this:

select *
from fact_sales f
join dim_company d on f.company_SK = d.company_SK
where f.company_SK = 1 and d.company_SK = 1

For that query the optimizer can directly uses the histogram step containing 1 for both tables and you get 467,583,000 X 1 as the estimated number of rows.

The query without a filter will use linear interpolation:

  For joins with a single equality or inequality predicate, the legacy
  CE joins the histograms on the join columns by aligning the two
  histograms step-by-step using linear interpolation.

Note that the statistics for your dimension table do not have a histogram step with a RANGE_HI_KEY value of 1, which is where almost all of the data is in your fact table. Something is going wrong with the linear interpolation step for that value. If I create your dimension table without a 0 value then the histogram gets a step with a RANGE_HI_KEY value of 1. That fixes the estimate. In short, you got unlucky with your histogram. I don't think there is a supported way to force the histogram on the dimension table to contain a step for 1, unless perhaps you are willing to add additional filters to your query.

I don't know the exact rules of your data, but I can give you two workarounds. The first workaround is to enable trace flag 2301 for the query. That fixes the estimate for this part of the query but may have other negative effects for the rest of your query. This trace flag is documented by MS, but talk to your DBA before using it.

The second workaround is to encourage the query optimizer to use a different method of cardinality estimation for the join. The following query gave a good estimate for me and only does a single scan on your fact table:

select *
from FACT_SALES f
join (SELECT COMPANY_SK FROM DIM_COMPANY UNION SELECT 1) d
on f.company_SK = d.company_SK;


MySQL Update Multiple Rows in Large Table I have a large InnoDB MySQL database table. I would like to run this query against the database table:

Update myBigTable Set someColumn=x WHERE userId = 7 OR userId = 88 OR userId = 22 OR userId = 99;

The exact contents of the where clause is dynamic. Through a web front end, an administrator checks one or more users who they want to update via this query.

However, when the above query is run, mysql usually returns:

#1205 - Lock wait timeout exceeded; try restarting transaction

So I'm trying to find some way to refactor my sql query so that it doesn't need to do a table lock. The 'someColumn' that is being updated and the userId both have indices defined.

I first tried:

Update myBigTable Set someColumn=x WHERE userId IN (7, 88, 22, 99);

However, the same problem persisted. I know that I can do four separate queries like:

Update myBigTable Set someColumn=x WHERE userId = 7;
Update myBigTable Set someColumn=x WHERE userId = 88;
Update myBigTable Set someColumn=x WHERE userId = 22;
Update myBigTable Set someColumn=x WHERE userId = 99;

The above approach does work, but it requires four separate queries (and in the worst case, a user could spawn 150 queries based on our current UI design - this number could grow over time as well).

What is the best practice for doing a sql update statement in MySQL Innodb to update multiple rows in a large table to the same value?

SHOW ENGINE INNODB STATUS\G
and see
https://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded
increasing the lock timeout period could solve the issue.
Apparently default is 50seconds. Which is maybe enough for all of the rows with userid=99, but not enough for rows with 99,22,88 or 7 userid.
Just speculation.


Using host names in `pg_hba.conf`? According to the PostgreSQL documentation:

  A host name specification that starts with a dot (.) matches a suffix
  of the actual host name. So .example.com would match foo.example.com
  (but not just example.com).

So I put this line in pg_hba.conf and restarted PostgreSQL:

hostssl market_data market_data_access .horsholm.dk md5

Then I attempt to connect to the database:

psql "postgresql://market_data_access@<server-IP>:5433/market_data?sslmode=require"

Instead of a password prompt, I get this:

psql: FATAL:  no pg_hba.conf entry for host "195.249.206.131", user "market_data_access", database "market_data", SSL on

Just to check, I make reverse DNS look-up of the IP address listed in the error message:

$ host 195.249.206.131
131.206.249.195.in-addr.arpa is an alias for 131.129-190.206.249.195.in-addr.arpa.
131.129-190.206.249.195.in-addr.arpa domain name pointer kommune.horsholm.dk.
$

What am I doing wrong?

It's not sufficient that 195.249.206.131 resolves to kommune.horsholm.dk that matches the wildcard.

After checking that, Postgres will try to resolve kommune.horsholm.dk and find that it doesn't resolve to 195.249.206.131 (as it doesn't resolve
at all in this particular case)

$ host kommune.horsholm.dk
Host kommune.horsholm.dk not found: 3(NXDOMAIN)

Doc quote:

  If a host name is specified (anything that is not an IP address range
  or a special key word is treated as a host name), that name is
  compared with the result of a reverse name resolution of the client's
  IP address (e.g., reverse DNS lookup, if DNS is used). Host name
  comparisons are case insensitive. If there is a match, then a forward
  name resolution (e.g., forward DNS lookup) is performed on the host
  name to check whether any of the addresses it resolves to are equal to
  the client's IP address. If both directions match, then the entry is
  considered to match


Incorrect EST Timezone instead of EDT in Oracle I am not getting correct timezone in Oracle Database. The issue is happening only starting 2021. Please see two same queries - first one for year 2020 and second for year 2021. The first one is correctly giving the timezone as EDT; the second one is incorrectly giving EST. How to fix this?

SELECT
   TO_CHAR(FROM_TZ(TIMESTAMP '2020-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE
FROM DUAL ;

---
EDT

SELECT
   TO_CHAR(FROM_TZ(TIMESTAMP '2021-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE
FROM DUAL ;

---
EST

I did not investigate any further for specific database and timezone file versions, but I guess you use and old database version with an outdated timezone file.

10.2.0.5.0:

SQL> select VERSION from v$timezone_file;

   VERSION
----------
         4

SQL> SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2020-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE FROM DUAL ;

TIM
---
EDT

SQL> SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2021-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE FROM DUAL ;

TIM
---
EST

11.2.0.4.180417:

SQL> select VERSION from v$timezone_file;

   VERSION
----------
        14

SQL> SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2020-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE FROM DUAL ;

TIM
---
EDT

SQL> SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2021-08-01 00:00:00', 'America/New_York'),'TZD') AS TIMEZONE FROM DUAL ;

TIM
---
EDT

It's time to upgrade.

This is a good starting point (requires a valid Oracle Support contract which you would need anyway for downloading the patch as well): Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)


Logging user's id with the query he executed So, I am trying to log not just the postgres user but his ID from my  table (for example) Person (person_id) when he calls functions.

So far, from what I can tell I can get almost every information I need from these system tables:

pg_stat_activity
pg_stat_statements if I activate it (but probably won't be able to due to some restrictions)

But the part that I can't seem to figure out is if I want to log the user's ID from (for example) the User table (user_id for example).

Basically I'd like to create a small table that will only house the last month's log, and I will insert into it.

user_id (not system id, but from my own table)
IP address
timestamptz of when he called the function/query
query he executed

My main issue is the user_id part, since the other 3 values already exist in the pg_stat_activity (client_addr, query_start, query)

If there's no easy way to make this happen, lets say I want to create a function that will somehow select the user_id based on parameters returned by pg_stat_activity, but I can't seem to figure out how I would do that. Hidden table parameters like tableoid, cmin, cmax, xmin, xmax, ctid don't help me here, at least I don't see how, maybe I missed something?

Based on your comments, the INSERT could look something like this:

INSERT INTO log (userid, clientaddr, calltime, query)
SELECT user_id,  -- function argument
       a.client_addr,
       a.query_start,
       a.query
FROM pg_stat_activity AS a
WHERE a.pid = pg_backend_pid();

The key to identifying the current session is the process ID of the backend process.


Tuning dedicated Percona Server with XtraDB for OLTP Hardware specs for an OLTP DB

dual quad core xeon with hyperthreading
24GB ram
Hardware RAID10 BBU

Software specs

Standard install of Ubuntu 10.04LTS with default LVM partitioning
Installed Percona Server with XtraDB

My priority is data integrity/protection followed closely by performance.
QUESTIONS

Are there settings in the RAID card that should be or should not be set? Since I have BBU, write-back should be set right?
Is ext4 ok or should I create a separate xfs volume for the data?
What should the kernel scheduler be set to? noop? deadline?
Are there other settings that I should tweak at the Hardware/OS/Filesystem level?

When I head the word 'Percona Server', the first thing that jumps at me is : "Does the DB Server have multiple CPUs?" These days, that is taken for granted. What is unfortunate is that most people forget to configure MySQL or Percona Server to summon those multiple CPUs.

MySQL 5.1 with InnoDB 5.1 Plugin, MySQL 5.5 and Percona Server have options to increase threading. Here are those options along with other InnoDB features you may wish to tune:

innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. Best round number to set for this is (2 X Number of CPUs) + Number of Disks. As I learned firsthand from the Percona NYC Conference back in May 2011, you should set this to 0 in order to alert InnoDB Storage Engine to find the best number of threads for the environment it is running in.

innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.

innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.

innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).

innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64.

innodb_replication_delay imposes thread delay on a slave is innodb_thread_concurrency is reached.

innodb_read_ahead_threshold allows linear readings of the set number of extents (64 pages [page = 16K]) before switching to asynchronous reading.

Time would escape me if I named more options. You can read about them in MySQL's Documentation.

Switching gears to RAID: Please stick with your RAID10. It is easier to maintain other redundant setups, especially RAID5.. Also, make sure you have the latest firmware for your RAID card. Otherwise, RAID firmware may conflict with the OS kernel resulting either in disk crashes or lower overall throughput.

As for Is ext4 ok or should I create a separate xfs volume for the data?, keep in mind that ext3 places a max size limit of 2TB per file. If you are not dealing with TBs of data, don't sweat using ext3 or ext4.

A separate data volume would increase disk performance since mysqld will not compete with the OS.


Is it possible to enforce unique values in a column, excluding nulls? I have a business requirement to have a FK field on TableA referencing TableB.  If there is a value in TableBID, it should be unique, but it's not mandatory, so there could be multiple records with null values.

Is it possible somehow to enforce this type of uniqueness on the DB level, using either an index or a constraint?

You can create a unique filtered index as below.

CREATE UNIQUE NONCLUSTERED INDEX IX ON TableA(TableBID) WHERE TableBID IS NOT NULL

For versions of SQL Server prior to filtered index support (pre 2008) see this related question.


What is the purpose of the USING parameter in SQL? Working with some code on a standard MySQL database, and I came across this statement:

SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1

What's throwing me is: what is USING for, what does it do?

My first assumption was that it involved working with foreign keys or possibly indexes, but the DB schema has no indications of any foreign keys or indexes, period.
My googlefu has failed me, probably because USING is such a common word in most text on sites out there. So I wonder if the DB gurus hear could shed some light on what this does before I look to modify this and multiple other queries in this code.

In the context of your query, USING helps satisfy a JOIN so long as the two tables involved in the JOIN have the same column names to join with. It is like doing a NATURAL JOIN.

Your query

SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1

works the same as

SELECT * FROM foo LEFT JOIN bar ON foo.bar_id = bar.bar_id WHERE foo_id = 1

According to the MySQL Documentation on JOIN syntax

  The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables:

a LEFT JOIN b USING (c1,c2,c3)

  The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.


What version of PostgreSQL first implemented autovacuum? I'm just wondering what version of PostgreSQL first implemented autovacuum.

Prior to PostgreSQL 8.1 it was an extension; 8.1 moved autovacuum into the distribution and away from pg_autovacuum, from the release notes

Move /contrib/pg_autovacuum into the main server (Alvaro)
Integrating autovacuum into the server allows it to be automatically started and stopped in sync with the database server, and allows autovacuum to be configured from postgresql.conf.

But concurrent vacuuming and enabled-by-default autovacuum was introduced in 8.3,

Autovacuum is now enabled by default (Alvaro)
Several changes were made to eliminate disadvantages of having autovacuum enabled, thereby justifying the change in default. Several other autovacuum parameter defaults were also modified.


Failed to populate indices of table table_name I need to design my PostgreSQL database and I use SQL Power Architect to do it.

But after I import it to SQL Power Architect and drag the table to workspace, I got error  message : Failed to populate indices of table <table_name>, I explore the error, and got Caused by : org.postgresql.util.PSQLException: ERROR: column i.indproc does not exist.

Actually I never use column i.indproc in my database, and this error appear when I drag each tables to workspace (for the first time, after I delete the table from workspace and drag it again, the error not appear)

Here the message

  java.lang.RuntimeException: ca.sqlpower.sqlobject.SQLObjectException:
  Failed to populate indices of table login     at
  ca.sqlpower.sqlobject.SQLObject$1.run(SQLObject.java:193)     at
  java.awt.event.InvocationEvent.dispatch(Unknown Source)   at
  java.awt.EventQueue.dispatchEventImpl(Unknown Source)     at
  java.awt.EventQueue.access$500(Unknown Source)    at
  java.awt.EventQueue$3.run(Unknown Source)     at
  java.awt.EventQueue$3.run(Unknown Source)     at
  java.security.AccessController.doPrivileged(Native Method)    at
  java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown
  Source)   at java.awt.EventQueue.dispatchEvent(Unknown Source)    at
  java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown
  Source)   at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)  at
  java.awt.EventDispatchThread.run(Unknown Source) Caused by:
  ca.sqlpower.sqlobject.SQLObjectException: Failed to populate indices
  of table login    at
  ca.sqlpower.sqlobject.SQLTable.populateIndices(SQLTable.java:550)     at
  ca.sqlpower.sqlobject.SQLTable.populateColumns(SQLTable.java:369)     at
  ca.sqlpower.sqlobject.SQLTable.populateImpl(SQLTable.java:1335)   at
  ca.sqlpower.sqlobject.SQLObject.populate(SQLObject.java:186)  at
  ca.sqlpower.sqlobject.SQLTable.getChildren(SQLTable.java:1963)    at
  ca.sqlpower.sqlobject.SQLObject.getChildren(SQLObject.java:285)   at
  ca.sqlpower.architect.swingui.PlayPen$AddObjectsTask.ensurePopulated(PlayPen.java:1599)
    at
  ca.sqlpower.architect.swingui.PlayPen$AddObjectsTask.doStuff(PlayPen.java:1574)
    at ca.sqlpower.swingui.SPSwingWorker.run(SPSwingWorker.java:104)    at
  java.lang.Thread.run(Unknown Source) Caused by:
  org.postgresql.util.PSQLException: ERROR: column i.indproc does not
  exist     at
  org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
    at
  org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
    at
  org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
    at
  org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    at
  org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
    at
  org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:236)     at
  org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getIndexInfo(AbstractJdbc2DatabaseMetaData.java:3735)
    at
  ca.sqlpower.sql.jdbcwrapper.DatabaseMetaDataDecorator.getIndexInfo(DatabaseMetaDataDecorator.java:426)
    at
  ca.sqlpower.sql.jdbcwrapper.PostgresDatabaseMetaDataDecorator.getIndexInfo(PostgresDatabaseMetaDataDecorator.java:173)
    at
  ca.sqlpower.sqlobject.SQLIndex.fetchIndicesForTableAndUpdatePK(SQLIndex.java:827)
    at ca.sqlpower.sqlobject.SQLTable.populateIndices(SQLTable.java:538)
    ... 9 more

Please help me to solve this problem

This very likely is caused by Postgres version support. If you are trying to connect to a Postgres 10 server SQL Power Architect does not support this version. My solution, which is far from perfect, is to run a small older Postgres in my development desktop for ERD purposes. From what I can gather, version 10 support is either unlikely and/or problematic at best.


Read on MDF file failed with reason 15105 The log shipping backup job for one of our databases is failing with the error message:

  Read on "D:\Data\Filename.mdf" failed: 1(failed to retrieve text for this error. Reason: 15105)

Attempting to run the job out of schedule (to see if this gleans any additional data) results in a failure with the same message.

What is the cause of this message and how would I resolve it?

Select @@VERSION:

  Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) Mar 19 2015
  12:32:14 Copyright (c) Microsoft Corporation Standard Edition (64-bit)
  on Windows NT 6.1  (Build 7601: Service Pack 1)

DB Compatibility Level: SQL Server 2008 (100)

Recovery model: Bulk-logged

Results of DBCC CHECKDB:

DBCC CHECKDB ('DatabaseName') WITH PHYSICAL_ONLY reports:

DBCC results for 'DatabaseName'.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:2112497) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:2112498) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:2112499) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:2112500) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
CHECKDB found 0 allocation errors and 4 consistency errors not associated with any single object.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:2112497) allocated to object ID 557349150, index ID 1, partition ID 72057601138884608, alloc unit ID 72057601177681920 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:2112498) allocated to object ID 557349150, index ID 1, partition ID 72057601138884608, alloc unit ID 72057601177681920 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:2112499) allocated to object ID 557349150, index ID 1, partition ID 72057601138884608, alloc unit ID 72057601177681920 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:2112500) allocated to object ID 557349150, index ID 1, partition ID 72057601138884608, alloc unit ID 72057601177681920 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'schemaname.tablename' (object ID 557349150).
CHECKDB found 0 allocation errors and 8 consistency errors in database 'DatabaseName'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DatabaseName).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

As your mention error

  Read on "D:\Data\Filename.mdf" failed: 1(failed to retrieve text for
  this error. Reason: 15105)

MS SQL error 15105 is an error message which occur when a user try to create database file backup to a network shared disk or when hosting database file on a network share is not supported, and as a result such error message event take place.

How is MS SQL error 15105 caused ?

MS SQL error 15105 is caused due to following stuffs.

1)Trying to attach an .mdf file, that has not been detached properly.
2)There might not be enough free space on the destination where the user wants to keep his database backup file.
3)There might not be the permission to access the location of the backup file that is assigned in in the SQL Server service account.
4)It may also be possibility that the location of the folder in the error message is not the same instance where the user want to restore the backup. In this case it’s unlikely that the service has access to the folder.
5)If the user has copied the backup file from another computer, then the destination computer may block the file due to security reason.
6)Corruption of Windows Operating System
7)Attack of Viruses
8)Incomplete software installation
9)Excessive startup entries and registry errors.

For ref Here & Here


How to use insert delay with the InnoDB engine and use less connection for insert statements? I'm working on an application which involves a lot of database writes, approximately ~70% inserts and 30% reads. This ratio would also include updates which I consider to be one read and one write. Through insert statements multiple clients insert data in database via insert statement below:

$mysqli->prepare("INSERT INTO `track` (user, uniq_name, ad_name, ad_delay_time ) values (?, ?, ?, ?)");

The question is should I use either insert_delay or use mysqli_multi_query mechanism because insert statement utilizes ~100% cpu on server. I am using the InnoDB engine on my database so insert delayed is not possible. Insertion on server is ~36k/hr and 99.89% read, also i am using select statement there retrieve data seven times in single query, this query takes 150 seconds on server to execute. What kind of technique or mechanism can I use for this task? My server memory is 2 gb, should i expand memory?.  Have a look on this problem, any suggestion will be grateful to me.

Table's structure:

+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL              | auto_increment |
| user            | varchar(100) | NO   |     | NULL              |                |
| uniq_name       | varchar(200) | NO   |     | NULL              |                |
| ad_name         | varchar(200) | NO   |     | NULL              |                |
| ad_delay_time   | int(11)      | NO   |     | NULL              |                |
| track_time      | timestamp    | NO   | MUL | CURRENT_TIMESTAMP |                |
+-----------------+--------------+------+-----+-------------------+----------------+

My database present status, it shows 41k insertions (writes), which is very slow for my database.

Since you have more writes then reads, I would like to recommend the following
Decent Tuning of InnoDB would be the key
Buffer Pool (Sized by innodb_buffer_pool_size)
Since InnoDB does not support INSERT DELAYED, using a large InnoDB Buffer Pool is closest thing you can get to INSERT DELAYED. All DML (INSERTs, UPDATEs, and DELETEs) would be cached in the InnoDB Buffer Pool. Transactional information for the Writes are written immediately to the Redo Logs (ib_logfile0, ib_logfile1). The writes that are posted in the Buffer Pool are periodically flushed from memory to disk via ibdata1 (InsertBuffer for Secondary Indexes, Double Write Buffer). The larger the Buffer Pool, the large the amount of INSERTs can be cached. In a system with 8GB or more of RAM, use 75-80% of the RAM as the innodb_buffer_pool_size. In a system with very little RAM, 25% (to accommodate the OS).
CAVEAT : You can set innodb_doublewrite to 0 to speed up writes even more, but at the risk of data integrity. You can also speed things up with setting innodb_flush_method to O_DIRECT to prevent caching InnoDB to the OS.
Redo Logs (Sized by innodb_log_file_size)
By default, the redo logs are named ib_logfile0 and ib_logfile1 and would be 5MB each. The size should be 25% of the innodb_buffer_pool_size. If the redo logs already exist, add the new setting in my.cnf, shutdown mysql, delete them, and restart mysql.
Log Buffer (Sized by innodb_log_buffer_size)
The log buffer holds changes in RAM before flushing them into the redo logs. The default is 8M. The larger the log buffer, the less the Disk I/O. Be careful with very large transactions, as this may slow down COMMITs by milliseconds.
Accessing Multiple CPUs
MySQL 5.5 and the MySQL 5.1 InnoDB Plugin have settings to have InnoDB Storage Engine access multiple CPUs. Here are the options you need to set:

innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. It is usually recommended to set for this is (2 X Number of CPUs) + Number of Disks. Last year, I learned firsthand from the Percona NYC Conference that you should set this to 0 in order to alert the InnoDB Storage Engine to find the best number of threads for the environment it is running in.
innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.
innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.
innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).
innodb_read_io_threads (set this to 3000) and innodb_write_io_threads (set this to 7000) (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64. Set these to 64. Also, set the innodb_io_capacity to 10000.

Upgrade to MySQL 5.5
If you have MySQL 5.0, upgrade to MySQL 5.5. If you have MySQL 5.1.37 or prior, upgrade to MySQL 5.5. If you have MySQL 5.1.38 or above and want to remain in MySQL 5.1, install the InnoDB Plugin. That way, you can take advantage of all the CPUs for InnoDB.


Microsoft SQL Server partitioning with on premise server I'm planning for a small hosted ASP.NET application which uses a Microsoft SQL Server 2008 database. One of the users which uses the application doesn't want his data to be stored in a remote database and has requested to store his rows of the database on his on premise database server.

First, is this possible with SQL server? And if it is, is this even safer if the application is still running on a remote server and the data is used there (application has to load and transfer data from on premise server to remote server, so the data could potentially be loaded by anyone)?

Yes this is possible, but is not a good idea. You can theoretically run a web application anywhere and punch firewall holes so that the web application can access a database on prem. This opens up a whole lot of issues as the database will have to be on a secure network segment and can wreck havoc on security policies.

You can make this more secure by using SSL for the database connection and use strict access lists on the firewalls, use Site2Site VPN and so forth but you are accessing the database over wide area network which can affect other users.

But in any case you will have to re-route all traffic from this customer to a different server, which will make the application more complex so it's probably easier to install the application on-prem for this customer.


Sql Server 2008 x64 ODBC Linked Server to Oracle Not Working I have an install of Sql Server 2008 x64, installed on two boxes. One is a Win 7 x64 workstation (Sql Server 2008 x64 SP2 developer edition), the other is a Windows Server 2003 x64 (Sql Server 2008 x64 SP2 Enterprise edition).  I am trying to create a linked server to an external vendor's Oracle instance.  I have installed the Oracle ODAC 11g 64 bit drivers using the full install (not the XCopy version).

The drivers appear to have all been installed correctly.  I have created and updated my tnsnames.ora file using the correct IP, port, etc to the remote server and rebooted.  Using the 64 ODBC admin tool, I am able to create the ODBC connection to the Oracle server and the "Test" button returns as successful using the alias in the tnsnames.ora file and the correct user ID and password.

I then go in to Sql Server 2008 and try to create my linked servers.  I can create the OLE DB linked server and it connects successfully, I can list the tables/views in the catalog and do queries against them with only one significant problem.  For tables with TIMESTAMP fields, a normal 4-part query throws fits.  Looking around this appears to be a common problem with OLEDB linked servers from Sql Server to Oracle and using OPENQUERY is the most common workaround, which I do have working.

The ODBC connection, which is what our vendor recommends using to connect to them, is where I have big problems. I can create the linked server, which appears to be successfully created using the System DSN ODBC connection I created earlier.  I can view the lists of tables/views in the catalog and they all appear to show up correctly.  However, when I try to get data, it fails completely.

If I try to right click on a view name and select Script -> SELECT To.... I get a message that says that:

  [tablename] contains no columns that can be selected or the current
  user does not have permissions on that object.

If I try to script out a SELECT manually and run it, as I know most of the column names, I get an error message:

  The OLE DB provider "MSDASQL" for linked server [linked server name]
  returned an invalid column definition for table [table name].

The vendor states that the user ID (the same one in both cases) has the proper rights to the tables/views, which it appears to as the OLE DB connection mostly works.  The Oracle server is 10g, but I don't know if it's 32 or 64 bit.  Would that make a difference?

Right now I'm working on getting this working from the Win7 x64 workstation, but a short test on the 2003 server yielded the same results.  If I have to, I guess I can make the OLEDB/OPENQUERY solution work.  However it's not ideal or recommended by our vendor.  Any ideas what I might be missing on getting the ODBC connection working?

If I try to right click on a view name and select Script -> SELECT To.... I get a message

The GUI is probably trying to access an Oracle Data Dictionary View (in order to read the column names & data types) that it does not have permission to select from, hence the error message.

Ask the Vendors Oracle DBA to trace the ODBC session so that you can track down the exact cause.


Adding value to int array I looked at the postgres website and to add a integer to a intarray it says int[] + int.  I have a int array in a table and I eventually want to insert the array with another value added to it into another table.  So testing with a select statement I am not sure where I am messing up.

So a am doing:

SELECT myarray + 9::integer from foo
ERROR: operator does not exist: smallint[] + integer

What am I missing.  It looks just like the postgres website on intarrays from 9.1 postgres.

Use the concatenation operator

myarray || 9

Details in the manual: https://www.postgresql.org/docs/current/9.1/functions-array.html


When is the time that Couchbase write documents to disk? As the title said, when is the time that Couchbase write documents to disk? every 60 seconds? every 1 seconds? every 100 ms? or everytime there are read command to that document?

I will answer your question as I lay out the higher level flow objects take through Couchbase on their way to disk.

Object is written to the managed cache by the application
Simultaneously a pointer to that object is placed into a queue (Disk Write Queue) for the object to be persisted to the DB storage engine, currently called Couchstore. A pointer is also put into a replication queue to replicate that object to a replica, but that is another discussion.
Depending on server resources, namely disk write speed, the objects will then be moved to Couchstore. The disk write queue can grow quite large if the amount of incoming data is large and the disk are slow. This is an area that needs to be looked at as part of sizing a cluster. It is not just all about raw read/write speeds, but also how the queue is filling and draining. You want the disk fill and drain rates to be as close as possible and the disk write queue to be as close to 0 as you can get it. This is especially critical if you are going to be using PERSIST_TO style writes.
Once it gets to Couchstore, it is persisted to disk. There is some batching in there, 1000 per vBucket if I recall correctly. This is to improve performance. Also, while there is an OS buffer (I was incorrect in using that term before), Couchbase does not depend on it, but there is an fsync after each batch.

One thing to remember, pretty much all of this can be monitored using the Admin Rest API or looked at in the WebUI. These are all great candidates for being monitored on an ongoing basis if you care about performance.


Using Oracle SCN to Calculate RPO Is the difference between the SCN of primary and secondary a good indicator of RPO?

We are planning for the below logic to get the RPO.

Get the current SCN from secondary.
Get the time stamp of the above SCN(as A).
Get the current SCN from primary.
Get the time stamp of the above SCN(as B).
Calculate the RPO as the difference of B-A.

Is this logic valid?

If you see the standby summary in DGMGRL then it is quite straightforward to know whether your configuration is satisfying your RPO and RTO(derived from business needs) or not.

DGMGRL> show database orcl_stby;

Database - orcl_stby

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      916.00 KByte/s
  Real Time Query: ON

The transport lag is measured as the delta from the primary database last update and the standby last received redo. This can effect your RPO. If you have to fail over your primary database at this time, redo data that did not arrive at the standby database server will be lost.

Similarly Apply Lag represent your RTO which indicates how far behind your standby database server is compared to the primary when it comes to applying redo data.


10195 plans for the same query! So, I've ran the BrentOzar script which has identified 10195 plans for the same query!!!  The query is below:

SELECT *
FROM [table1]
INNER JOIN [table2]
  ON [table1].[versionId] = [table2].[VersionId]
INNER JOIN [table3]
  ON [table2].[ContentId] = [table3].[nodeId]
INNER JOIN [table4]
  ON [table3].[nodeId] = [table4].[id]
WHERE ([table4].[nodeObjectType] = 'abcde123-fgh3-4ijk-8lmn-424f222332ff')
  AND ([table1].[published] = 0
  AND [table1].[releaseDate] <= '2017-07-22 17:43:47')
  AND ([table1].[newest]=1)
ORDER BY [table2].[VersionDate] DESC, [table4].[sortOrder]

The only difference between all 10195 of them is the date field (releasedate). value which has a different date for each plan.

Regarding indexes, the following applies:

Table1: NC on nodeid and versionid, C on versionId
Table2: NC on versionID, C on ID
Table3: NC on nodeid, C on pk
Table4: NC on nodeObjectType, NC on parentID, C on ID

Anyone any ideas what's the best way to resolve this as it's getting a big ridiculous!

Thanks in advance!

You can set the Forced Parameterization option at the database level to avoid this issue, but it does have a drawback in that queries could now experience parameter sniffing issues. CPU utilization should decrease as a result of the change. I consulted on a system that had very high CPU utilization and very high compilations per second. Enabling Forced Parameterization dropped the CPU utilization by about 50%. Another system had minimal change in CPU utilization since their compilations per second wasn't that high. In both cases, the plan cache size decreased as queries were now reusing plans in cache rather than compiling a new query plan each time.

Alternatively, you could modify the application so that it is sending over parameterized queries/prepared statements instead of adhoc SQL. Or use stored procedures.


Lob Logical Read and lob read-ahead reads in NCCI I have a test table with following structure.

CREATE TABLE [dbo].[DW_test](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[CourtCaseID] [int] NOT NULL,
[ActionID] [int] NOT NULL,
PRIMARY KEY CLUSTERED([ID] ASC)

Next I populated my table with about 470 million records with the following script.

insert into DW_test
--select count(*)
--from (
select top 1000000 abs(checksum(newid())) % 100000 + 1 a, abs(checksum(newid())) % 10 + 1 b
from sys.all_objects
cross join sys.all_objects a
cross join sys.all_objects b
cross join sys.all_objects c
cross join sys.all_objects d
cross join sys.all_objects e
cross join sys.all_objects f
cross join sys.all_objects g
--) t
GO

The script was executed about 470 times to generate 470 million records in the table
and created NCCI on that table.

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_test1] ON [dbo].[DW_test]
(
    [CourtCaseID],
    [ActionID]
)

Next I test simple query to count the records in the table.

DBCC DROPCLEANBUFFERS
GO
select COUNT_BIG(*)
from DW_test

If I turn SET STATISTICS IO ON I get the following results

With cold cache

  Table 'DW_test'. Scan count 25, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 469697, lob physical reads 1, lob read-ahead reads 1877324.

  Table 'DW_test'. Segment reads 453, segment skipped 0.

  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

With warm cache

  Table 'DW_test'. Scan count 25, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 229248, lob physical reads 0, lob read-ahead reads 0.

  Table 'DW_test'. Segment reads 453, segment skipped 0.

  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.`

I know that 1 logical read is a single data page read from buffer pool during query execution and 1 physical read is a single data page read from disk. RedGate tells us that read-ahead read is:

  This number tells us how many of the physical reads were satisfied by SQL Servers ‘Read-ahead’ mechanism.  This is directly tied to physical reads, so if there are no physical reads, you will have 0 for Read-Ahead reads.

In my case I am dealing with lob logical, physical and read ahead reads. I want to understand what this number means in my particular case.

How it is possible to have only 1 lob physical read with cold cache if the table has about 470 million records?

How it is possible the total number of lob pages decreased from about 2.3 million in cold cache to about 220k in warm cache?

How it is possible to have only 1 lob physical read with cold cache if the table has about 470 million records?

There is one LOB physical read and 1,877,324 read-ahead reads. Read-ahead is still a physical read, just performed in advance (prefetching). The quote from Redgate is incorrect.

  How it is possible the total number of lob pages decreased from about 2.3 million in cold cache to about 220k in warm cache?

Logical reads count the number of times a page in memory was touched. The reduction is from 469,697 (cold cache) to 229,248 (warm). I don't have a complete explanation for this, but it may be in part because columnstore data is cached contiguously in a separate columnstore object pool, rather than the general page-sized buffer pool.

Read-ahead reads pages from the upper levels of a b-tree to identify pages to read in advance of the scan, which may also result in a varying number of 'extra' logical reads, depending on timing and the characteristics of the storage system. Columnstore indexes have a b-tree structure on persistent storage.

Another factor is parallelism (it seems your test was run at DOP 24) since overlapping requests from different threads can result in additional logical reads. You may find the excess logical reads reduce somewhat if you run with OPTION (MAXDOP 1).

Internal storage details for columnstore are not well-documented, nor fully supported by the usual DMVs. At this stage, I would say the best explanation for the reduction in reads between cold and warm cache cases is the difference between on-disk and in-memory (cached) storage.

Columnstore data (dictionaries and segments) are stored at LOBs on disk, but are not held in memory in the same format, for performance reasons. Because they are different things, you can't just add logical reads to physical reads to get a meaningful result.


How to retrieve the query I used to create a view? for my company I created a view which is used for exporting.
Maybe there is a problem with the query that is used to create this table.

So here is my question:

Is it possible to get the query I used to create said view table ?

View definitions are stored in the information schema:

SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
   WHERE TABLE_NAME = 'yourviewname'


How to cast (a) a date value recorded as a VARCHAR to (b) a specific DATE format for (c) use in an ORDER BY clause How would I cast a column value (a "non-MySQL" DATE string retained as a VARCHAR) to a "MySQL date" so it can be used for sorting?

I have dates in the format of 06-02-2019 (6 feb) but MySQL itself doesn't seem to recognize this by just using the default DATE() function.

What I currently have is: SELECT * FROM Services ORDER BY Date(nextdate) DESC;

You are looking for the STR_TO_DATE() function. MySQL expects by default dates in ISO format.

In your particular case it would be something like:

SELECT * FROM Services ORDER BY STR_TO_DATE(nextdate, %d-%m-%Y) DESC;

%d-%m-%Y or %m-%d-%Y, whatever is the "wrong" format you are using.

Using ISO dates (either strings or, preferably, timestamps) plus an index will have the efect that it will speed up your queries.


some InnoDB settings in My.cnf are messing up everything, why? When I edit my.cnf in MySQL 5.0 in filemanager and add the following settings, I get logged out automatically from Kloxo and when I remove these line and restart server everything is ok then:

innodb_buffer_pool_size = 50M
innodb_log_file_size = 15M

but when I add the following line and restart I have no problem and this setting has been set:

bulk_insert_buffer_size=25M

Why is this happening?

P.S.:
I should note that Horde_groupware database have innoDB tables, when everything is messed up and I SELECT * FROM Horde_groupware.tblname I get the error says bad information in .frm file.

The next time you start mysql successfully, please run these

SHOW VARIABLES LIKE 'innodb_log_file_size';

This will show you the size of the InnoDB Log Files. The names of those files are (by default)

/var/lib/mysql/ib_logfile0
/var/lib/mysql/ib_logfile1

In order to change the size of the InnoDB Log Files, you must

Add innodb_log_file_size = 15M to my.cnf
service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start (InnoDB Log Files Get Recreated)

I wrote about this before : How to safely change MySQL innodb variable 'innodb_log_file_size'?

This will recreate the InnoDB Log Files.

With regard to

  I should note that Horde_groupware database have innoDB tables, when everything is messed up and I SELECT * FROM Horde_groupware.tblname I get the error says bad information in .frm file.

If Horde_groupware.tblname is an InnoDB table and you get this error, run this:

SHOW ENGINES;

You should see all the supported storage engines for the mysql instance. With the erro you are see on that table, there is one of three possiblilities:

InnoDB is not listed as one of the storage engines
InnoDB is marked as DISABLED
The table itself is corrupt


SQL*Loader and DC firewall timeout We are loading a huge dataset using SQLLDR and there are index creation that lasts for a couple hours or even more. Firewall drops this connection as there is no data transfer during index creation for an hour.
Oracle doesn't see this session as idle and doesn't drop connection, but firewall kills.

Do we have any workaround of this issue except changing firewall settings?

SKIP_INDEX_MAINTENANCE

  The SKIP_INDEX_MAINTENANCE SQL*Loader command-line parameter specifies
  whether to stop index maintenance for direct path loads.

Then you can build the indexes after.


Simple recursive CTE bombs out when changing operator in the WHERE clause As the title states, I have a recursive CTE that bombs out when I change the operators in the WHERE clause, even if there are only two rows of data.

CREATE TABLE #Recursion
(Parent varchar(10), Child varchar(10), TopDate datetime)

INSERT INTO #Recursion (Parent, Child, TopDate)
VALUES
('00003137', '00003137', '2018-08-31'),
('04536347', '00003137', '2017-02-28'),
('05458040', '05458040', '9999-12-31'),
('00269705',' 05458040',' 9999-12-31')

;WITH
Parent AS
  ( SELECT parent
     , child
     , 1 as sort
     , TopDate
 FROM #Recursion
WHERE parent = child
 UNION ALL
   SELECT s.parent
        , d.child
        , d.sort + 1
        , s.TopDate
 FROM Parent as d
 JOIN #Recursion s
   ON s.child= d.parent
WHERE s.TopDate < d.TopDate
)

  SELECT parent
       , child
       , sort
    FROM Parent
ORDER BY parent, sort desc
OPTION (maxrecursion 0);

DROP TABLE #Recursion

If I leave the where clause as such, it works

WHERE s.TopDate < d.TopDate

As soon as I change it to this, it bombs and will not complete

WHERE s.TopDate <= d.TopDate

I do need to run some testing if the dates are the less than or equal. How come this wont work, even with only two rows of data?

Here is some example data. Note how the date field can be identical

parent      child       sort    TopDate
00003137    04536347    2       2017-02-28
00003137    00003137    1       2018-08-31
00269705    00269705    2       9999-12-31
00269705    05458040    1       9999-12-31

I can get this to work on all data except data that has identical TopDates

If you model root nodes as having parent=child, you must exclude them from the recursive clause or you'll get an infinite loop.  Your s.TopDate < d.TopDate is currently the only thing in your query preventing an infinite loop.

EG, if you change < to <= the query will enter an infinite loop.  Note this is why (maxrecursion 0) is perhaps not a good idea.

drop table if exists #Recursion
go
create table #Recursion(parent int, child int, topdate datetime)
go
insert into #Recursion(parent,child,topdate) values (1,1,getdate());

;WITH
Parent AS
  ( SELECT parent
         , child
         , 1 as [order]
         , TopDate
     FROM #Recursion
    WHERE parent = child
 UNION ALL
   SELECT s.parent
        , d.child
        , d.[order] + 1
        , s.TopDate
     FROM Parent as d
     JOIN #Recursion s
       ON s.child = d.parent
    WHERE s.TopDate < d.TopDate
)

  SELECT parent
       , child
       , [order]
    FROM Parent
ORDER BY parent, [order] desc
OPTION (maxrecursion 0);

Also

ON s.child = d.parent

should probably be

ON d.child = s.parent

As you appear to be starting at the root nodes, so you need to recurse down by UNION ALL-ing rows whose parent is the previous iteration's child.


Strategies for reducing data to summaries within a table over time? I have a large data set where large numbers of entries differ only by timestamp.  In the short term it's beneficial to maintain individual entries so that we can correlate by time for individual data points.  But after enough time passes, that level of granularity is unnecessary and I'm comfortable replacing those individual entries with a summarized entry by means of a count column.

So if the table has entries this when it's fresh:

+---------------------+------+-------+
| stamp               | data | count |
+---------------------+------+-------+
| 2016/01/01:12:54:00 | abcd | 1     |
| 2016/01/01:12:56:58 | abcd | 1     |
| 2016/01/01:13:02:36 | abcd | 1     |
+---------------------+------+-------+

I'm happy to combine all the entries of a given hour after, say, a week:

+---------------------+------+-------+
| stamp               | data | count |
+---------------------+------+-------+
| 2016/01/01:12:00:00 | abcd | 2     |
| 2016/01/01:13:00:00 | abcd | 1     |
+---------------------+------+-------+

And maybe after a month I only care about the granularity of a day:

+---------------------+------+-------+
| stamp               | data | count |
+---------------------+------+-------+
| 2016/01/01:00:00:00 | abcd | 3     |
+---------------------+------+-------+

This isn't hard to do in SQL (PostgreSQL shown here):

select distinct(date_trunc('hour', stamp),data,sum(count)
into table tmpdata
from data
where stamp < now() - interval '7 days'
group by date_trunc('hour', stamp),data;

...and then clear the rows I just summarized out of data and replace them with the summaries I created in tmpdata...  It seems preferable to keep the data within a single table rather than have separate tables for different granularity of data.

This all works, but it's awkward, and I'd like to learn a better way if there is one.  So I have two questions:

What is the name for this sort of summary-replacement-of-data-over-time?  I would say the data is being coalesced, but COALESCE has a distinct other meaning in SQL.  It would help to know the terminology used to describe this sort of thing so I can tag it and talk about it properly.
What strategies / algorithms / methods of achieving this goal would you recommend, with an eye towards elegance?

I would simply call it "aggregation".

As for elegance, you can do this with a single SQL command without another table using a data-modifying CTE:

For (only) hourly aggregation of rows that are older than a week:

WITH del AS (
   DELETE FROM data d
   WHERE  stamp < now() - interval '7 days'   -- ">" would be wrong
   AND   (date_trunc('hour', stamp) <> stamp  -- not truncated yet.
          OR EXISTS (  -- more than one in same hour
             SELECT 1
             FROM   data
             WHERE  date_trunc('hour', stamp) = date_trunc('hour', d.stamp)
             AND    ctid <> d.ctid   -- except self
             )
         )
   RETURNING *
   )
INSERT INTO data(stamp, data, count)
SELECT date_trunc('hour', stamp), data, sum(count)
FROM   del
GROUP  BY 1,2;

Only replaces rows that need replacing. Repeated calls would not repeat writes.

If you know a lower bound where all rows have been aggregated already, add another WHERE condition to make it faster:

AND stamp > $lower_bound

Obviously, you need an index on stamp or, ideally, a partial index only including rows younger than $lower_bound that would need to be recreated from time to time. Related:

PostgreSQL partial index unused when created on a table with existing data
Add datetime constraint to a PostgreSQL multi-column partial index

Using ctid since no PK is defined.

In-order sequence generation
How do I decompose ctid into page and row numbers?

You may want to VACUUM FULL data; to reclaim table bloat after that. But be aware of implications. Maybe pg_repack is for you:

VACUUM returning disk space to operating system

Aside: DISTINCT and GROUP BY was redundant in your original query. And you were aggregating rows younger than a week instead of older.


What is transaction-managed data blocks? While reading the Database Concepts (Oracle 12c) I found the following:

  Block header: This part contains general information about the block,
  including disk address and segment type. For blocks that are
  transaction-managed, the block header contains active and historical
  transaction information.

I want to know what's is a transaction-managed block?

It's a block that can be modified transactionally.

The best explanation is probably from page 106 in Tom Kyte's Expert Oracle Database Architecture

  ... transaction information when relevant (only blocks that are
  transaction-managed have this information-- a temporary sort block
  would not, for example) regarding active and past transactions on
  the block ...

Most blocks, of course, are subject to transactional modification so they would have transaction information.


SQL Server 2016 Maximum Bytes/Row We have a third party database that has a table with over 300 columns, and in SQL Server 2012 we are getting an error that we are exceeding allowed maximum 8060 bytes. I was wondering if upgrading to SQL Server 2016 will help.

Does SQL Server 2016 also have a limitation of 8060 bytes? Is it possible to have more columns in SQL Server 2016 without encountering the error below?

  Error: The table "X" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

This limitation relies on the core physical page structure in SQL Server and, no, this hasn't changed in SQL Server 2016 (and won't in SQL Server 2017, either).

There are ways to get around this, though. Top of mind is splitting some larger data out into a separate table. Or just living with the warning message if you know your data won't ever fill all columns on the same row (or you can prevent it).

Or going back to the 3rd party and seeing why they have this and how they prevent actual row problems.


How to add an extended property to a table type? According to the MSDN documentation, 'TABLE_TYPE' is a valid value for the @level1type parameter as shown below.

EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'An INT value.',
@level0type = N'SCHEMA', @level0name = dbo,
@level1type = N'TABLE_TYPE',  @level1name = AllTypesRecord,
@level2type = N'COLUMN', @level2name = 'Id'
GO

In Visual Studio 2013 in an SSDT project, the @level1type parameter name is underscored and has the error

  SQL70539: Invalid argument (@level1type)

Trying to execute the statement in a query window results in the error

  Msg 15600, Level 16, State 5, Procedure sp_addextendedproperty, Line 37
  An invalid parameter or option was specified for procedure 'sp_addextendedproperty'.

How can I add an extended property to a table type?

The MSDN documentation:

http://msdn.microsoft.com/en-us/library/ms180047.aspx

appears to be incorrect.  TABLE_TYPE is not a valid level1type value.

Documentation is also missing here:

http://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx

but a comment at the bottom provided the answer.

Extended properties may be specified for a table type by using a leve1type of 'TYPE'

EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'An INT value.',
@level0type = N'SCHEMA', @level0name = dbo,
@level1type = N'TYPE',  @level1name = AllTypesRecord,
@level2type = N'COLUMN', @level2name = 'Id'
Go


MySql event ENABLED on both primary and replicate of RDS multi-AZ deployment I have a mysql database instance deployed in a multi-az configuration on Amazon RDS. We've seen replication fail a couple of times in the last two months, with an error message like,

Apply Error 1590: The incident LOST_EVENTS occured on the master. Message: error writing to the binary log

Looking closer into the logs on the replica, I noticed that it was trying to execute an event (we have a heartbeat event that is ENABLED on the primary, and should be in a SLAVESIDE_DISABLED state on the replica(s). Looking closer, I realized the failing replica has this event in ENABLED state, and since the server is running as ``--read-only` it fails while trying to run the event. There are errors like below in the replica logs.

2016-02-28 20:55:00 16040 [ERROR] Event Scheduler: [database_name@%][database_name.update_heartbeat_event] The MySQL server is running with the --read-only option so it cannot execute this statement

I tried dropping the event on the primary, but it had no effect on the replica, presumably because replication is in failing at this point.

Any pointers to help fix this (besides creating a new readonly replica from the primary?)

Thanks

Looks like AWS RDS has a bug where when creating a read-only replica of a mysql database with events, it creates the events on the replica with ENABLED status instead of SLAVESIDE_DISABLED.

I verified this by recreating the failing replica. When it created the replica from the snapshot, it put in the events with the wrong state.

Dropping and recreating the event(s) on the primary resolved the issue by creating the same events with the correct status.


MySQL Grouping Order Is it possible to force the order in which a result set is grouped? I have a table with multiple languages in it, and I'm doing a GROUP_CONCAT to get a comma separated list of the product name in each language, for each product_id. However it doesn't seem possible to get MySQL to return that concatenated string in any particular order of language_id's. What I'd like is to be able to order the grouping by language id, so that they'll always come out in a pre-determined order.

Is this possible? If so, how? If not possible within the SELECT statement, is there a modification I can make to the table to adjust how the GROUP BY would order the result?

You can use ORDER BY inside GROUP_CONCAT() function. You can also change the separator, if you don't want to use comma. Further details in MySQL documentation: GROUP_CONCAT()

Example:

SELECT
    product_id,
    GROUP_CONCAT(name
                 ORDER BY language_id
                 SEPARATOR ','
                ) AS product_names
FROM
    products_languages
GROUP BY
    product_id ;