texts:
[Copied from ServerFault to what seems like a better-suited forum]

Our Oracle server (version 12c) is running on a RedHat-6.7 server with 128GB of RAM.

The current size of the only database on it is only about 60GB.

Yet, we see plenty of disk reads -- on the device where the database is stored -- in the iostat-output (shown in blue). There are some writes (shown in yellow) too, of course:

While the writes to permanent storage make sense because we do make modifications to the data some times, the reads do not -- the entire DB can fit in memory twice over... Indeed, we should be able to store it on a USB-stick and still have good read-times -- comparable to those of Redis, for example.

What parts of the Oracle-configuration should we examine and tune to make the server use all available RAM?

We will look at the INMEMORY settings. But it would seem, the inmemory_* parameters come into play only, when the server has to (because of RAM shortage) decide, what to drop from memory to be re-read back later. In our case nothing should ever be dropped from memory, because there is room for everything. And yet some things are, evidently, being (re)read again and again...

Update: the output of free on the server currently reads:

             total       used       free     shared    buffers     cached
Mem:     132044132   99974992   32069140   89131408     292508   93060984
-/+ buffers/cache:    6621500  125422632
Swap:     33554428          0   33554428

The processes owned by the oracle-user all seem to share the above-mentioned 85GB memory piece. Maybe, we really are just constraining the server ourselves -- the 30GB unused memory seems to suggest that. How do we tell the server to use more?


texts: Zero-downtime repartitioning on postgres 12

texts:
I have a ~1 billion row table called things. The things transition through several states (tracked in the state column), eventually ending up in either a broken or finished state.

broken or finished rows almost never change, but we need to read them from time to time. The data is very cold. Rows in other states change a lot, so they lead to a lot of bloat and autovacuums.

We're using postgres 12, so this seems like a good case for declarative partitioning. It feels like a small-hot table for active things makes sense, then move the rows  to larger cold partitions once they stop changing.

Here's a simplified structure we've considered:

CREATE SEQUENCE things_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE things (
    id integer NOT NULL DEFAULT nextval('things_id_seq'),
    account_id integer NOT NULL,
    state text NOT NULL,
    value integer NOT NULL
)
PARTITION BY LIST (state);

CREATE TABLE things_default PARTITION OF things DEFAULT;

CREATE TABLE things_finished PARTITION OF things FOR VALUES IN ('finished') PARTITION BY LIST (account_id);
CREATE TABLE things_finished_default PARTITION OF things_finished DEFAULT;
CREATE TABLE things_finished_account_id_1 PARTITION OF things_finished FOR VALUES IN (1);
CREATE TABLE things_finished_account_id_2 PARTITION OF things_finished FOR VALUES IN (2);

CREATE TABLE things_broken PARTITION OF things FOR VALUES IN ('broken');

CREATE INDEX things_id ON things USING btree (id);
CREATE INDEX things_state ON things USING btree (state);
CREATE INDEX things_account ON things USING btree (account_id);
CREATE INDEX things_value ON things USING btree (value);

Over half of the things are in a finished state, so that cold partition is still quite large. To keep it from growing too large, we've included sub-partitions for some large accounts.

Is feasible it is to re-partition if we need to adjust the structure down the track, or do we need to get the partitioning scheme right at the start? Currently we manage to apply nearly all schema changes with no user-visible downtime - is it possible to re-partition without long-held locks that impact user traffic?

For example, what if I want to add a new sub-partition to the things_finished table because an account has grown quite large? This seems to work:

BEGIN;

CREATE TABLE things_finished_account_id_3 ( like things including all );

INSERT INTO things_finished_account_id_3 SELECT * FROM things WHERE state = 'finished' and account_id = 3;

DELETE FROM things WHERE state = 'finished' AND account_id = 3;

ALTER TABLE things_finished ATTACH PARTITION things_finished_account_id_3 FOR VALUES IN (3);

COMMIT;

However the ALTER TABLE takes out a AccessExclusiveLock on the things_finished_default table, blocking all reads and writes to that partition for the last part of the transaction.

Maybe an alternative partitioning scheme will be easier to re-partition on a live database?


texts: How to protect SQL Server 2012 Backups on Standard Editions

texts:
I understand that the password option to protect the SQL Server backups has been discontinued beginning with SQL Server 2012 and the feature to encrypt backups using certificates is available only from SQL Server 2014. So in such cases, are there any other In-Built options available to protect SQL Server backups that I might have missed?

TDE is only available on Enterprise editions. But am looking something for Standard Editions.


texts:
I observed two things that I cannot really explain.

the number of queries in pg_stat_statements are sometimes less, sometimes more than it was last time(930, 925 and then 931 within 10 seconds). Should not the number of rows increase monotonically untill it reaches 1000 (or whatever limit I set)?
the numbers for calls, blks_written, etc. also change nonmonotonically.

Are stats reset each day or what?


texts:
I have non-numeric string IDs in a table, and would like to cast them to unique integers. I need the mapping to be invertible / one-to-one. The string IDs are of uniform length.

I want to do this by literally aggregating the ASCII codes of each character in the ID. The ASCII function only returns the leftmost character. Is there an efficient way to cast the entire string?


texts:
I have two rather simple queries. The first query

 UPDATE mp_physical SET periodic_number = '' WHERE periodic_number is NULL;

and it's plan

 duration: 0.125 ms  plan:
    Query Text: UPDATE mp_physical  SET periodic_number = '' WHERE periodic_number is NULL;
    Update on mp_physical  (cost=0.42..7.34 rows=1 width=801)
      ->  Index Scan using "_I_periodic_number" on mp_physical  (cost=0.42..7.34 rows=1 width=801)
            Index Cond: (periodic_number IS NULL)

And the second one:

 UPDATE observations_optical_temp SET designation = '' WHERE periodic_number is NULL;

and it's plan:

duration: 2817.375 ms  plan:
    Query Text: UPDATE observations_optical_temp SET periodic_number = '' WHERE periodic_number is NULL;
    Update on observations_optical_temp  (cost=103.55..9223.01 rows=5049 width=212)
      ->  Bitmap Heap Scan on observations_optical_temp  (cost=103.55..9223.01 rows=5049 width=212)
            Recheck Cond: (periodic_number IS NULL)
            ->  Bitmap Index Scan on "_I_per_num_temp"  (cost=0.00..102.29 rows=5049 width=0)
                  Index Cond: (periodic_number IS NULL)

I expect the second plan shold be the same the first one. But it's not. Why?
Here are dumps of the tables.

CREATE TABLE public.mp_physical(
    id_mpp serial NOT NULL,
    id_comet_parts integer,
    "SPK_id" public.nonnegative_int,
    designation varchar(30),
    name varchar(100),
    prefix varchar,
    "is_NEO" bool,
    "H" double precision,
    "G" double precision,
    diameter public.nonnegative_double,
    extent varchar(30),
    extent_error public.nonnegative_double,
    geometric_albedo public.nonnegative_double,
    rot_per public.nonnegative_double,
    "GM" public.nonnegative_double,
    "BV" public.nonnegative_double,
    "UB" public.nonnegative_double,
    "spec_B" varchar(30),
    "spec_T" varchar(30),
    lca double precision,
    multiplicity public.nonnegative_int,
    polar_ang double precision,
    polar_slope_ang double precision,
    a double precision,
    b double precision,
    mass public.nonnegative_double,
    mp_type public.mp_type NOT NULL,
    periodic_number varchar(5),
    diameter_method_def varchar(200),
    discovery_info text,
    "H_sigma" public.nonnegative_double,
    "G_sigma" public.nonnegative_double,
    diameter_sigma public.nonnegative_double,
    geometric_albedo_sigma public.nonnegative_double,
    rot_per_sigma public.nonnegative_double,
    "GM_sigma" public.nonnegative_double,
    "BV_sigma" public.nonnegative_double,
    "UB_sigma" public.nonnegative_double,
    lca_sigma public.nonnegative_double,
    a_sigma public.nonnegative_double,
    b_sigma public.nonnegative_double,
    polar_ang_sigma public.nonnegative_double,
    mass_sigma public.nonnegative_double,
    CONSTRAINT "_C_id_ap" PRIMARY KEY (id_mpp)
);

CREATE INDEX "_I_name" ON  mp_physical  USING btree (name);
CREATE INDEX "_I_designation" ON mp_physical USING btree(mpp_designation);
CREATE INDEX "_I_periodic_number" ON mp_physical USING btree(periodic_number);
CREATE INDEX "_I_mp_type" ON mp_physical USING btree(mp_type);

And

  CREATE TEMPORARY TABLE "observations_optical_temp"(note_1,date,"RA","Dec",magnitude,band,id_observatory,id_mpp,"Dec_degree",observatory_code,periodic_number,mpp_designation,mp_type)
AS SELECT note_1,date,"RA","Dec",magnitude,band,id_observatory,id_mpp,"Dec_degree",'1'::varchar(3),'1'::varchar(8),'1'::varchar(30),'A'::public.mp_type FROM observations_optical;

CREATE TABLE observations_optical(
    id_obs_o bigint
    note_1 varchar,
    date timestamp NOT NULL,
    "RA" time NOT NULL,
    "Dec_degree" integer NOT NULL,
    "Dec" time NOT NULL,
    magnitude double precision,
    band varchar,
    id_observatory integer,
    id_mpp integer,
    CONSTRAINT "_PK_id_obs_o" PRIMARY KEY (id_obs_o)
);
CREATE INDEX "_I_temp_1" ON observations_optical_temp USING btree(mpp_designation);
CREATE INDEX "_I_temp_2" ON observations_optical_temp USING btree(periodic_number);
CREATE INDEX "_I_temp_3" ON observations_optical_temp USING btree(mp_type);


texts: How can I make sure the SQL Server query optimizer uses the exact tables in the query

texts:
I have a query running in SQL Server 2008:

select count(*)
from table1 join
table2 on table1.col1 = table2.col2

In the data warehouse where this script runs, a table called thin_table1 is exclusively populated by table1 (we use this thin table for indexed view creation. Read this answer for a little more detail).

The problem is that the optimizer is choosing to use thin_table1 rather than table1 during execution. This does not happen in SQL Server 2005. This new execution plan won't work for our current operation.

How can I turn off this "pass-through" feature, either at the database or session-level (or in SSIS)? I have many SSIS packages and stored procedures running during the data load, so I don't want to touch all the objects individually.

At this point, even knowing what that feature is called would be helpful in searching for the answer.

EDIT:
Went back to look at the same plan in 2005. Looks like it did happen there, but it to much less dramatic effect. I thought it was a problem just in 2008 but the same functionality appeared in 2005.

EDIT2:
A DBA here noticed that the plan is referencing an indexed view. We normally drop our indexed views at runtime, but in this test scenario, they were still built. Looks like when the indexed view is active, it will use that and any tables associated with it when the query executes.

Is there a way to bypass this automatic reference to indexed views?


texts: Should we enable innodb_file_per_table for single tenant SaaS app (thousands of databases)

texts:
We have a mySQL backed SaaS application where each customer has their own mySQL database. So think of it like a wordpress/magento etc. database for each client.

Each database has ~200 table, and we expect to have about 1,000 databases per server, so 200,000+ tables. We currently have innodb_file_per_table enabled.

Our question is regarding whether we should use innodb_file_per_table or not. We've seen some conflicting information on this:

Performance: With the large number of tables, is it better or worse to use innodb_file_per_table. We understand there can be issues with the number of file pointers required to have hundreds of thousands of tables open.
Stability: There is some discussion that using innodb_file_per_table is safer or improves crash recorvery
Concurrency: Some reports say that using innodb_file_per_table improves concurrency because there are individual tablespaces.
I presume nothing has come from innodb_file_per_database. Facebook talked about this back in 2009 (!) but I've not seen anything since. https://www.facebook.com/notes/mysql...ent_id=6285127

Welcome any thoughts on this.


texts: MongoDB Benchmark with write-intensive on geo-indexed DB

texts:
I am using PostgreSQL 9.3 on AWS RDS to store geo information into an indexed table of points.

My data is write-oriented, meaning that I write nearly 1,000 points per each geo search query.

I am considering some scaling solutions, and MongoDB seems like a natural choice because it's mature, scales up well, and supports geo indexes out of the box.

Is there any reliable and recent benchmark that compares MongoDB performance to other SQL and NoSQL solutions, preferably under write-intensive scenarios?


texts: Is there a way to execute statements on server startup?

texts: Is there something similar to mysql init-file, where one can put prewarm statements?


texts: Inserting to a table when everything is parameterized

texts:
I need to grab data stored on a remote AS/400 file system to which I connect using a linked server connection, so I can send OPENQUERY select statements and receive the data on SQL Server.

I don't know how many files/tables I'm going to end up with yet, but at this point I know enough to see that I'm going to need a flexible and scalable solution.... that ideally performs well enough.

So I have created a table in my Metadata database that looks something like this:

Id    Filename      FilterColumn    FilterValue    LastTransferred
 1    STDLIB.FOOBAR      TRXDATE           NULL               NULL
 2    STDLIB.SOMETH         NULL           NULL               NULL
...

My control flow looks like this:

   Collect Metadata
         ↓
[ Foreach Loop Start ]

   Execute DeleteStmt
         ↓
   Execute OPENQUERY
         ↓
 Write to destination

[ Foreach Loop End   ]

In the Collect Metadata step I have a data flow task that reads from the above metadata table and a script component that uses it to generate T-SQL statements - one to truncate/delete the existing local tables, and another to select the remote data; then I send everything into a Recordset Destination.

So far, so good.

The Foreach Loop Container iterates the recordset and can execute the generated truncate/delete commands, then executes the OPENQUERY select command, and dumps the result set into a dedicated User::RemoteData object variable.

And this is where I'm stumped: in the "Write to destination" step, I have another script component (configured as a "source") that takes this variable and uses an OleDbDataAdapter to fill up a DataTable, from which I'm able to pick up the column names and actual row data (every iteration of the Foreach loop container has a different table in User::RemoteData).

So I started implementing the CreateNewOutputRows override like this:

public override void CreateNewOutputRows()
{
    using (var records = new DataTable())
    using (var adapter = new OleDbDataAdapter())
    {
        adapter.Fill(records, Variables.RemoteData);
        var builder = new StringBuilder();
        builder.Append("INSERT INTO dbo.");
        builder.Append(Variables.StagingTableName);

        var columnNames = new string[records.Columns.Count];
        for (var index = 0; index < records.Columns.Count; index++)
        {
            columnNames[index] = records.Columns[index].ColumnName;
        }

        builder.Append("(");
        builder.Append(string.Join(",", columnNames);
        builder.Append("););

        builder.Append(" VALUES (");
        foreach (DataColumn column in records.Columns)
        {
            // ok, stop, think. this can't be right.
        }
    }
}

So I stopped and started thinking... and I can't think of a nice, neat way to do this.

Am I going to have to script these INSERT commands and check column types to properly single-quote-delimit string values and whatnot?

Isn't this going to perform like a snail? I'm not liking that the Foreach container is going to process each table sequentially, and I can already see the above solution completing in hours... when it's only the "transfer data over from remote system" part (I have many more ETL steps coming up, that will need to consume this data).

What would be a better way? Do I have to drop the idea of sourcing my data flow from my Metadata database, and have n "statically-defined" workflows, one for each table?


texts: Performance: Left joining same table multiple times

texts:
Let's say I've the following table:

id | name  | created_by  | updated_by | deleted_by
--------------------------------------------------
1  | alice | NULL        | NULL       | NULL
2  | ben   | NULL        | 3          | 1
3  | kane  | 2           | NULL       | 3
4  | ali   | 1           | 1          | NULL
5  | peter | 1           | 1          | 3
6  | rose  | 1           | 1          | 5
.
.

Approach 1: I'd run a query like this:

SELECT
u.*, a.name as 'creator', b.name as 'updator', c.name as 'deletor'
FROM users u
LEFT JOIN users a on u.created_by = a.id
LEFT JOIN users b on u.updated_by = b.id
LEFT JOIN users c on u.deleted_by = c.id
ORDER BY u.id
LIMIT 10

If the table has 1000-10000 records, primary id, is the query bad or good?

Approach 2: Or is it better to fetch without left joining, then on application level, I do another query to fetch the creator/updator/deletor, like so:

Query 1:

 SELECT
u.*
FROM users u
ORDER BY u.id
LIMIT 10

Query 2:

SELECT
u.name
FROM users u
WHERE `id` IN ($ids) # $ids is an array of all created_by+updated_by+deleted_by from query 1
ORDER BY u.id
LIMIT 10


texts: Breaking changes between 2008R2 RTM going to 2008R2 SP3?

texts:
I have a few A/P FCIs running 2008R2 RTM, and I'd like to upgrade the passive node to SP3, fail over, and then upgrade the formerly passive node.

Has anyone else attempted this? Are there breaking changes in the Service Pack?

I do not have a suitable QA environment to test, and I can only find breaking changes between release versions.

Thanks


texts: Can NOLOCK in a stored procedure be overridden when executing it?

texts:
Our application interfaces with a partner's application by calling one of their stored procedures.  They insist on sprinkling NOLOCK hints throughout their code and this is a long stored procedure.  We are intermittently getting bad data and suspect a race condition.

Is there any way, short of manually changing their stored procedure, that we can override their NOLOCK hints when executing it?

Note -- our code making the stored procedure call is a .NET application, in case that gives us any other options.


texts: Select most recent record per day based on 3 columns

texts:
I have a table that looks like this:
CREATE TABLE tbl_AppUsage(
ID int IDENTITY(1,1) NOT NULL,
UserName nchar(250) NULL,
UPN nchar(250) NULL,
AppDisplayName nchar(250) NULL,
AppId nchar(250) NULL,
CreatedDateTime nchar(250) NULL);

INSERT INTO tbl_AppUsage (UserName, UPN, AppDisplayName, AppId, CreatedDateTime)
VALUES
    ('aaa', 'aaa@mail.com', 'Adobe DC', '1234fdr234', '07/10/2020 08:39:14'),
    ('aaa', 'aaa@mail.com', 'Adobe DC', '1234fdr234', '07/10/2020 08:51:14'),
    ('aaa', 'aaa@mail.com', 'Adobe DC', '1234fdr234', '07/10/2020 09:39:14'),
    ('aaa', 'aaa@mail.com', 'Adobe DC', '1234fdr234', '07/11/2020 09:39:14'),
    ('bbb', 'bbb@mail.com', 'Adobe DC', '1234fdr234', '07/10/2020 08:39:14'),
    ('bbb', 'bbb@mail.com', 'Adobe DC', '1234fdr234', '07/10/2020 09:39:14'),
    ('bbb', 'bbb@mail.com', 'Adobe DC', '1234fdr234', '07/11/2020 10:39:14'),
    ('bbb', 'bbb@mail.com', 'Adobe DC', '1234fdr234', '07/11/2020 10:51:14')
;

I want to create a query that returns the newest record per day per user

I tried with the following:
SELECT
       ID
      ,UserName
      ,UPN
      ,AppDisplayName
      ,AppId
      ,CreatedDateTime
  FROM tbl_AppUsage T1
  WHERE CreatedDateTime=(
  SELECT MAX(CreatedDateTime)
  FROM tbl_AppUsage
  WHERE UserName = T1.UserName AND AppId = T1.AppId)
  GROUP BY UserName

No success and I am a bit stuck, to be honest.
The table that I will query has 200 000 plus records and I am using MS SQL 2017 as DBMS.
I am unsure whats the best method to get the data with performance in mind.
Note: I know that the columns (like CreatedDateTme) can be other types but in this case, it has to be a nchar.


texts: Import MySQL table from a textfile to a remote host with compression

texts:
I can import data.sql onto my foreign host with this command:

mysql -u username -p -h foreign-host.example.org -A DATA-BASE-NAME < data.sql

Which works fine for small export files, but I cannot find an explanation how to import a large file, so the transfer will be compressed.


texts: Data Compression Through Base36 Encoding

texts:
I'm trying to store a lot of large numbers that truncate when stored as INT values. Unfortunately they are too large for this type.

I'm thinking about using Base36 encoding to accomplish this. Are there any other ways to go about fixing this problem?


texts: Full text search over multiple related tables: indices and performance

texts:
We have the following database structure

CREATE TABLE objects (
    id       int   PRIMARY KEY,
    name     text,
    address  text
);

CREATE TABLE tasks (
    id int           PRIMARY KEY,
    object_id int    NOT NULL,
    actor_id int     NOT NULL,
    description text
);

CREATE TABLE actors (
    id   int  PRIMARY KEY,
    name text
);

The user enters a whitespace-separated list of words (search terms, basically) and we have to search for tasks, that satisfy the following: the task is a "match" if each search term occurs at least once in concatenation of task's description, the name and address of its associated object and the name of its associated actor.

Now, if we are not concerned about performance, we can just do something like this (given query "foo bar"):

SELECT t.id, t.description
FROM tasks AS t
INNER JOIN actors AS a ON t.actor_id = a.id
INNER JOIN objects AS o ON t.object_id = o.id
WHERE to_tsvector(concat_ws(' ', t.description, o.name, o.address, a.name)) @@
    plainto_tsquery('foo bar');

Unfortunately, we are concerned about performance. The dataset will, probably, be as follows (and it is expected to grow):

about 10000 objects
about 1000 actors
about 100000 tasks evenly distributed between objects

What I've considered:

Make a denormalized table like this:

CREATE TABLE task_documents (
    id int PRIMARY KEY,
    doc tsvector
)

The field "doc" will contain the concatenation of task's description, associated object's name and address and actor's name. We will have to create an index over this field and it will be used in full text search queries. This table will be updated in update / insert triggers on tasks, actors, objects.

Drawbacks: tons of duplicated data (this one I am not quite concerned about), and updates to the master tables will become unpredictable in terms of number of rows updated (say, you update a name of some object and now suddenly you must update thousands of rows in task_documents).

Honestly, I don't have any more (good) ideas. It is obviously impossible to create an index, spanning 3 tables, so that it will be used for the WHERE clause in the original query.

UPD

Here's a sqlfiddle with DB schema and some data. I had to make it up, because we have no real data at the moment.


texts: The index columns in sys.index_columns different from what are in the index

texts:
When I run the following I get 3 indexed columns:

SELECT b.name,
       c.name
FROM sys.index_columns a
INNER JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id
INNER JOIN sys.columns c ON b.object_id = c.object_id
AND a.column_id = c.column_id
WHERE b.object_id = OBJECT_ID('dbo.MyTableIssue')
  AND b.name = 'IX_MyTableIssue_ColId_Col2Id'

Returns:

IX_MyTableIssue_ColId_Col2Id    ColId
IX_MyTableIssue_ColId_Col2Id    Col2Id
IX_MyTableIssue_ColId_Col2Id    ColDate

But when I do a right click, script index as then create to new query window I get the following:

CREATE NONCLUSTERED INDEX [IX_MyTableIssue_ColId_Col2Id] ON [dbo].[MyTableIssue]
(
    [ColId] ASC,
    [Col2Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

I'm running SQL Sever 2008.  Any advice or direction is greatly appreciated.


texts:
We have master server on one Datacenter, and the Replicate is in another Datacenter

Intermittently, on the replicate server, we see this as below.

  “2018-12-19 07:24:25 UTC: : @: [25775]: [2-1] FATAL:  could not receive data from WAL stream: server closed the connection unexpectedly
  This probably means the server terminated abnormally
  before or while processing the request.”

We have all DB (master and replicate settings) in place for streaming replication, and we are using the same settings on 100+ replicates and there is no issue except this.

I would appreciate if someone would help here.

we have wal_files on the master
hot_standy is on

When we initially start the replicate server, it streams, and if in case streaming is lagging, we have restore_command to restore from master pg_xlog_archive.

So, most of the time, we don't have issues, but sometimes we have this error.


texts: SQL server Virtual account and folder permissions

texts:
How does SQL server 2012 still work after changing the Service account to a domain account or even a local account. For example: as mentioned in MSDN

https://msdn.microsoft.com/en-us/library/ms143504.aspx#VA_Desc

This folder Instid\MSSQL\data has full control privileges to the virtual account NT service\MSSQLSERVER. But I am confused how SQL server still starts after changing the service account to a domain or local account as this new service account will not be having privileges to this particular folder unless we explicitly provide it. And from what I tested, SQL Server fails to start if you remove NT service\MSSQLSERVER from Instid\MSSQL\data folder's permissions.

So does this mean any account which is a SQL server service account is automatically added to NT service\MSSQLSERVER group?, How does this work?


texts: Will CDC work with SQL Server Always on Availability groups? Do you need to set up CDC on both/multiples nodes if using Availability Clusters?


texts: MySQL Trigger: Insert depending on NEW.column of type enum

texts:
I am trying to add a trigger to one of my tables. Depending on a enum-value of the newly inserted row it should execute a different insert-query. I am using phpMyAdmin and am trying to execute my script in the SQL-Tab but it always get an error which tells me absolutely nothing...

Here's my SQL Script that i try to execute in the SQL Tab of phpMyAdmin:

CREATE TRIGGER insert_after_new_kpi_trig
    AFTER INSERT ON KPI
    FOR EACH ROW

    IF NEW.kpi_type = 3 THEN
        INSERT INTO ProjectKPIData (project_id, kpi_id, value) SELECT p.id, NEW.id, 0 FROM Projects;
        INSERT INTO TeamKPIData (team_id, kpi_id, value) SELECT t.id, NEW.id, 0 FROM Teams;
    ELSE IF NEW.kpi_type = 1 THEN
        INSERT INTO TeamKPIData (team_id, kpi_id, value) SELECT t.id, NEW.id, 0 FROM Teams;
    ELSE IF NEW.kpi_type = 2 THEN
        INSERT INTO ProjectKPIData (project_id, kpi_id, value) SELECT p.id, NEW.id, 0 FROM Projects;
    END IF;

And this is the error I'm getting:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6

So can someone tell me if something is wrong with my script or do I have to use the string-value of the enum for the comparison instead of the index that I'm currently using? Thank you for your help.

Edit:
Maybe I should also mention what the enum is its the following

enum('TEAM', 'PROJECT', 'BOTH')

So if I use 3 in the if condition it should be equal to BOTH


texts: the result of REPLACE and CONVERT is a VARCHAR(8000) - how can I have a VARCHAR(50) as a result instead?

texts:
I have many situation where I need to convert big float numbers with many decimal places to a rounded number with 2, or 1 or no decimal places at all.

the way I have been doing this is demonstrated below:

declare @t table ( [rows] float )

insert into @t
select 1.344
union all select 32.3577
union all select 65.345
union all select 12.568
union all select 12.4333

select * from @t

Now I will do my convertion and save them all into a temp table and then we are going to look at the structure of the temp table.

begin try
   drop table #radhe
end try
begin catch
end catch

select
[Rows Formated] = REPLACE(CONVERT(VARCHAR(50),CAST(sp.[Rows] AS MONEY),1), '.00','')
into #radhe
from @t sp

select *
from #radhe

that is all fine, it worked great, it did what I wanted, however, when I look at the structure of the table I get the following:

use tempdb
go

sp_help '#radhe'

you can see on the picture, the varchar(8000).
can we avoid that?

I would be happy with a varchar(50)


texts: How to avoid Functions from a Performance Stand-point in Azure SQL Database?

texts:
I read this article quite recently on the performance issues to do with  Functions.

I'm currently in the development phase of a new database on the Azure SQL Database platform. It won't go live for a new months yet. I'm using a variety of scalar-valued functions with one being to convert UTC to Local Date where it is specified in the Config table, e.g. AUS Eastern Standard Time

The function is called as the following:

SELECT
    dbo.fnGetLocalDate(DateColumn) as DateColumn,
FROM
    table

Does anyone have any suggestions on how to avoid functions in cases such as this? I find them useful for code reuse and I'm not sure how to avoid it here.

I'm also wondering whether with vNext fixing the performance issues with Functions in the Azure platform that I'm better off continuing to work with functions.

CREATE FUNCTION [dbo].[fnGetLocalDate]
(
    @DateToConvert datetimeoffset = NULL
)
RETURNS datetimeoffset
AS
BEGIN

    DECLARE @TimeZone varchar(50)

    RETURN
    CASE
        WHEN @DateToConvert is NULL then NULL
    ELSE
        CONVERT(datetimeoffset, @DateToConvert AT TIME ZONE (SELECT Value FROM LookUp.Config WHERE Property = 'TimeZone'))
    END

END


texts: How would I move SQL MSSQL.1 and MSSQL.2 location?

texts:
We have a SQL instance where the MSSQL.1 and MSSQL.2 were installed on a SAN drive. We are moving away from this SAN do to faults and we wish to move the files and folders to a new SAN. How would we do this?

All DB datafiles have been moved and are up and running again, I just want to know if it is necessary to move these over:


texts:
I'm running SQL Server 2016. I have a database that has run, apparently, out of space on the D: drive. The daily index rebuild started producing the error shown below.

  Msg 1101, Level 17, State 12, Line 3
  Could not allocate a new page for database 'MarketDataAnalysisDB' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

First thing I did was remove the logs from the D: drive onto the C: drive so that I could (hopefully) delete some older historic data and free up space.  I was able to move the log files but the indexes still did not run (same error as above).

So I set out to delete rows of data with the intent of freeing up space so that future rows could grow back into the database. Following the delete statement, and 4 1/2 hours later, I got a similar error

  Msg 1105, Level 17, State 2, Line 2
  Could not allocate space for object '' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

While the error information is very informative I can do none of the suggested items. There is nothing on the drive but the database file. When I started reading about shrinking the database I realized I don't really understand why I'm getting this error. I've attached an image of the used space information in hopes that someone who understands this better than I, can provide a safe way to reduce the database size. The goal is to allow new records to come in while I figure out a rolling partition implementation that is currently beyond my SQL skills to implement on my own.

The other option I might consider is purchasing a larger hard drive for the data. But, from what I see, there appears to be space while at the same time SQL Server thinks I'm out of space.

What is the best way to reduce the size of my database?

DB Usage/space details

[EDIT]: I didn't realize it right away, but, my attempt to delete rows that took 4 hours and the log file located on the c: drive which had nearly 200GB free filled the log file so now the c: drive is out of disk space! What to do?


texts: ALTER TABLE ADD CONSTRAINT failing when executed from function

texts:
So I have 2 functions that I am writing, one to drop all foreign constraints for a schema, and the other will recreate these foreign constraints as they once were.

The drop constraints function works by selecting various "deferrable constraint levels" into respective temp tables (e.g. temp tables for: fully_deferrable, not_deferrable, semi_deferrable) to make sure that information exists for the subsequent recreate constraints function.  I am running into an error though that will not allow me to create the constraint because this constraint already exists.  So the recreate constraints function will error out with something like: " ERROR: constraint "avg_test_k_school_fkey" for relation "avg_test" already exists", even though I have already dropped this foreign constraint via the previous function.

I have checked the tables to make sure all the foreign constraints are dropped, and when I run the ALTER TABLE statement outside of the function, the foreign constraint is added with no problem.  So I am wondering what in the world is going on with executing this from the function.  I am thinking that perhaps I am doing something wrong control-flow wise, or that these operations are not possible to call from functions.  Please let me know if you have any questions!

This function is for the recreation of foreign keys. not_deferrable_constraints is a temp table that has the child table, parent table, whether or not a constraint is deferred, the fields of the primary/foreign keys, and the name of the constraint.

Here is the SQL:

 CREATE OR REPLACE FUNCTION test.recreate_all_foreign_constraints(schema_name name)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare
_tbl record;
add_constraint record;
begin

--Recreate not_deferrable_constraints
for _tbl in(select child_table from not_deferrable_constraints) loop
    for add_constraint in(select constraint_name, fk_column, pk_column, parent_table from not_deferrable_constraints where child_table = _tbl.child_table) loop
        execute 'ALTER TABLE ' ||schema_name||'.'|| _tbl.child_table || ' ADD CONSTRAINT ' || add_constraint.constraint_name || ' FOREIGN KEY ('|| add_constraint.fk_column ||') REFERENCES '|| add_constraint.parent_table ||' ('|| add_constraint.pk_column ||')';
    end loop;
end loop;
end;
$function$
;

It would be super cool if this worked! I'm so close I can feel it!! Thanks for your help!


texts:
I am trying to import a .CSV file into a mssql table using bulk insert (Although I am open to other methods). I have a bulk insert sql statement but it says (0 row(s) affected) when I execute the the file. However, I have data in the file itself.

Sample CSV File (Header + One Line below header)

.CSV Header

  changedate,deactivate,lockout,noemail,empno,orighire,salary,salut,fname,mi,lname,username,hstreet1,hstreet2,hcity,hstate,hcountry,hzip,busphone,busext,cellular,empemail,jobtitle,jobcode,jobgroup,orglevel1,orglevel2,orglevel3,orglevel4,orglevel5,company,perfdate,supervisor,misc1,misc2,misc3,misc4,active

.CSV Content

  2014-04-03 00:00:00,NO ,NO ,NO ,1133,2014-04-03 00:00:00,1111.1111,,test,test,test,user.test,111 test way,,Sacramento,CA,USA,11111,,,,test@test.org,test test,057,02,sss,261,SAC,,test,ttt,1911-11-11 00:00:00,1111,N,,test,0,NULL

.SQL

BULK
INSERT dbo.Archive
FROM 'C:\scripts\User.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\r\n',
FIRSTROW = 2
)
GO

I tried to achieve this through ps and this was the outcome and code:

# Database variables
$sqlserver = "dbserver"
$database = "db"
$table = "tb"

# CSV variables
$csvfile = "C:\scripts\User.csv"
$csvdelimiter = ","
$firstRowColumnNames = $true

################### No need to modify anything below ###################
Write-Host "Script started..."
$elapsed = [System.Diagnostics.Stopwatch]::StartNew()
[void][Reflection.Assembly]::LoadWithPartialName("System.Data")
[void][Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient")

# 50k worked fastest and kept memory usage to a minimum
$batchsize = 50000

# Build the sqlbulkcopy connection, and set the timeout to infinite
$connectionstring = "Data Source=$sqlserver;Integrated         Security=true;Initial Catalog=$database;"
$bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock)
$bulkcopy.DestinationTableName = $table
$bulkcopy.bulkcopyTimeout = 0
$bulkcopy.batchsize = $batchsize

# Create the datatable, and autogenerate the columns.
$datatable = New-Object System.Data.DataTable

# Open the text file from disk
$reader = New-Object System.IO.StreamReader($csvfile)
$columns = (Get-Content $csvfile -First 1).Split($csvdelimiter)
if ($firstRowColumnNames -eq $true) { $null = $reader.readLine() }

foreach ($column in $columns) {
$null = $datatable.Columns.Add()
}

# Read in the data, line by line
while (($line = $reader.ReadLine()) -ne $null)  {
$null = $datatable.Rows.Add($line.Split($csvdelimiter))
$i++; if (($i % $batchsize) -eq 0) {
    $bulkcopy.WriteToServer($datatable)
    Write-Host "$i rows have been inserted in $($elapsed.Elapsed.ToString())."
    $datatable.Clear()
}
}

# Add in all the remaining rows since the last clear
if($datatable.Rows.Count -gt 0) {
$bulkcopy.WriteToServer($datatable)
$datatable.Clear()
}

# Clean Up
$reader.Close(); $reader.Dispose()
$bulkcopy.Close(); $bulkcopy.Dispose()
$datatable.Dispose()

Write-Host "Script complete. $i rows have been inserted into the database."
Write-Host "Total Elapsed Time: $($elapsed.Elapsed.ToString())"
# Sometimes the Garbage Collector takes too long to clear the huge datatable.
[System.GC]::Collect()

ERROR

  PS C:\scripts> C:\scripts\Import-CSVtoSQL.ps1
  Script started...
  Exception calling "WriteToServer" with "1" argument(s): "Received an invalid column length from the bcp
  client for colid 38."
  At C:\scripts\Import-CSVtoSQL.ps1:51 char:2
  +     $bulkcopy.WriteToServer($datatable)
  +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : SqlException


texts: How can I check available space of an index's pages in PostgreSQL?


texts: Is there any harm in having PRINT statements in (production) SP's?

texts:
Is there any harm at all in having PRINT statements in stored procedures, even in production? I have some primitive PRINT debugging statements in a huge SP enclosed by a IF @debug = 1 parameter such as this:

IF(@debug = 1) PRINT 'Some debug info'

When I exec the SP with @debug = 1 through SSMS, the debug statment(s) will print.

Can anyone think of anything harmful from having a reasonable amount of debugging PRINT statements in production code, assuming the SP is shielded behind numerous layers of firewall, service authentication, SQL server authentication, and so on?


texts: Where is the list of attached databases stored?

texts:
I know database instance names are stored in the registry in subkeys of HKLM:SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL and the path to master.mdf is stored in HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\__InstanceId__\\MSSQLSERVER\Parameters@SqlArg0. However, I don't see a table in master.mdf or a registry key where the names of the other attached databases are stored.

I know I can look in sys.databases for the databases. However, what I want to know is how to find this information out without sql server running. Is it stored in the master.mdf? In the registry? Can I get that information with a hex editor, OrcaMDF or something else? How does Sql server know what databases to attach at startup?


texts: Unable To Log In Through Windows Authentication

texts:
See this question for details of troubleshooting I've been stepping through (I did answer 2, which didn't work - it was a corrupt master file).

In configuration manager, I changed to a Built-in account, to solve the problem in the above linked question.  That didn't work.  When I realized it was a corrupt master file and replaced everything, the issue of starting SQL Server worked.  Now I can't log into it.  Originally, SQL Server Express has a default account that I've never touched in the radio option This account: and underneath that Account Name: (it appears to be NT SERVICE\MSSQL$SQLEXPRESS).  Ignorantly, I've never set a password because I use Windows Authentication when logging in and from what I knew, the password was my normal Windows account.  In addition, I've tried setting up a backup account as a sysadmin SQL authentication login, but it never works on logging in and after hours of research a while ago, I gave up on this (different issue, but never identified why setting up another use for sysadmin didn't work).

I've tried using the below:

NT SERVICE\MSSQLSERVER

I've also tried the options in the Built-in account Network Service, Local System, and Local Service and none of these recognize my built in Windows credentials, which is very scary (my other SQLExpress does).
This doesn't work with my normal Windows password and I know the default account had $ characters in it, so I don't think that was the default.  When setting up SQL Server, what default account does it use?

Update

It appears to be this account => NT SERVICE\MSSQL$SQLEXPRESS.  However, my windows authentication still fails.  This would imply that SQL Server set a default password for the above account when I installed, yet somehow my normal Windows Authentication username and password was able to login.  I have no idea how that happened, but this appears to be the case as the Windows Auth password for the above account won't allow me to login when I log in with Windows Auth.


texts: Can adding both Asc and Desc indexes improve performance?

texts: Was wondering if adding an ascending and descending index (on the same column) could allow SQL to search any faster by utilizing both indexes at the same time?


texts: How to get the sum of filtered fields in SQL

texts:
How can I filtered values in SQL?

When I execute this query, I'm getting the result below.

SELECT Stock_TBL.StockID, Stock_TBL.ItemCode, Stock_TBL.ItemDescription,
       InvoiceItems_TBL.StoreName, InvoiceItems_TBL.Quantity
FROM Stock_TBL
INNER JOIN InvoiceItems_TBL ON Stock_TBL.StockID = InvoiceItems_TBL.StockID
INNER JOIN Invoice_TBL ON InvoiceItems_TBL.InvoiceNo = Invoice_TBL.InvoiceNo
GROUP BY Stock_TBL.StockID, Stock_TBL.ItemCode, Stock_TBL.ItemDescription,
         InvoiceItems_TBL.StoreName, InvoiceItems_TBL.Quantity

I want that filtered like the below screen shot. How can i do it (for this three tables are involving and only 5 stores).


texts: mysqld is generating a lot of disk IO writes. How can I figure out why?

texts:
I have a Ubuntu 14.04 server running half a dozen wordpress sites. Total traffic to the server is on the order of 30k page views a day.

Over the past couple months, with page views and network traffic roughly flat, my disk I/O has been trending up significantly. It has doubled over the past month. When I run iotop, I can see that mysqld (v. 5.5) is constantly writing to disk at a rate of around 5MB per minute. I can't think of anything happening on any of the sites that would generate so much writing.

I have spent a lot of time reading mysql tuning tutorials and adjusting variables (mostly to do with tmp table size, and query cache parameters) and nothing I have tried has made any difference. My current guess is that there is probably a bad query in one of the wordpress plugins in use. But there are a lot of plugins spread over half a dozen wordpress installs and I don't know where to look.

Is there some way that I can pinpoint what exactly is causing mysqld to be writing to the disk so much?

UPDATE : This question was explored in better detail and answered thoroughly here.


texts:
I use sql server 2012, and have multi cube.

I can connect to my created cube from Excel (Data Tab\Connections) with windows authentication. How can set permission to SQL Server user on my cube. and how can connect to cube with SQL Server user.

thanks in advance.


texts: Script not emailing from Maintenance Plan/SSIS

texts:
I have created the following script to send emails from my plan but it isn't sending out. It works correctly in a query window and under an agent job. This is added to the plan as a 'Execute T-SQL Statement Task'

MSSQL 2008 R2

USE msdb

DECLARE @EmailBody VARCHAR(MAX)
DECLARE @EmailProfile VARCHAR(100) = 'SQL_Alert'
DECLARE @Email VARCHAR(100) = 'my email is here'
DECLARE @JobName VARCHAR(200) = 'job name is here'
DECLARE @Subject VARCHAR(200) = @JobName + ' Status: '

;WITH CTE
AS
(
SELECT distinct top 1 sj.name AS [Agent Job Name],
CASE WHEN sjh.run_status=0 THEN 'Failed'
                 WHEN sjh.run_status=1 THEN 'Succeeded'
                 WHEN sjh.run_status=2 THEN 'Retry'
                 WHEN sjh.run_status=3 THEN 'Cancelled'
           ELSE 'Unknown'
      END [Job Outcome],
sja.run_requested_date,
CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration
FROM    msdb.dbo.sysjobactivity sja
INNER   JOIN msdb.dbo.sysjobs sj
ON  sja.job_id = sj.job_id
INNER   JOIN msdb.dbo.sysjobhistory sjh
ON  sjh.job_id = sj.job_id
WHERE   sja.run_requested_date IS NOT NULL
AND sj.name = @JobName
AND sjh.step_id = 0
)

SELECT @EmailBody =
'Agent Name: '
 + CHAR(9)
 + (SELECT [Agent Job Name] FROM CTE)
 + CHAR(13)
 + 'Job Outcome: '
 + CHAR(9)
 + (SELECT [Job Outcome] FROM CTE)
 + CHAR (13)
 + 'Ran At: '
 + CHAR(9)
 + CONVERT(VARCHAR(100), (SELECT run_requested_date FROM CTE), 25)
 + CHAR (13)
 + 'Duration: '
 + CHAR(9)
 + (SELECT [Duration] FROM CTE)

IF (@EmailBody IS NOT NULL)
BEGIN
SET @Subject = @Subject +
CASE WHEN @EmailBody LIKE '%Failed%' THEN 'Failed'
     WHEN @EmailBody LIKE '%Succeeded%'  THEN 'Succeeded'
     WHEN @EmailBody LIKE '%Retry%' THEN 'Retry'
     WHEN @EmailBody LIKE '%Cancelled%' THEN 'Cancelled'
     ELSE 'Unknown'
     END;

EXEC sp_send_dbmail
@profile_name = @EmailProfile,
@recipients = @Email,
@subject = @Subject,
@Body = @EmailBody
END

Results from history

  Executing query "USE msdb    DECLARE @EmailBody VARCHAR(MAX)  DECLA...".: 100% complete  End Progress  DTExec: The package execution returned DTSER_SUCCESS (0).  Started:  11:38:55 AM  Finished: 11:38:58 AM  Elapsed:  2.995 seconds.  The package executed successfully.  The step succeeded.


texts: PostgreSQL/Psycopg2 upsert syntax to update columns

texts:
I want to have Psycopg2 update col1, col2 and col3 when there is a conflict of id.

In my Python code I have the insert SQL currently as:

insert_sql = '''INSERT INTO {t} (id,col1,col2,col3)
        VALUES (%s,%s,NULLIF(%s, 'nan'), NULLIF(%s, 'nan'))
        ON CONFLICT (id)
        DO NOTHING;'''

Basically instead of DO NOTHING I want to set:

(col1,col2,col3) = (%s,NULLIF(%s, 'nan'), NULLIF(%s, 'nan'))

Which ignores inserting ID and updates col1, col2, and col3.
The problem is using %s to pass in tuple variables in Python using Psycopg2:

cur.execute(insert_sql.format(t='my_table'),(int(id),new_col1,new_col2,new_col3))

What is the syntax used to refer to the %s corresponding to col1, col2, and col3 to update ON CONFLICT?


texts: Steal ownership on database from user that no longer exists

texts:
We have a few SQL Server 2008 R2 databases where its member domain was changed (i.e. it was connected to DOMAINA and is now on DOMAINB). We have three databases where the owner of the database is DOMAINA\User, which no longer exists.

I have tried logging on to SQL Server as DOMAINB\Administrator and sa and run the following command:

ALTER AUTHORIZATION ON DestDB TO sa

Error:

  Msg 15151, Level 16, State 1, Line 1
  Cannot find the object 'DestDB', because it does not exist or you do not have permission.

I was under the (obviously incorrect) assumption that sa could do virtually anything on the server.

How can I get ownership of these databases back?


texts: Fault tolerance for Database sharding and Database partitioning

texts:
I'm aware that database sharding is splitting up of datasets horizontally into various database instances, whereas database partitioning uses one single instance.
In Database Sharding, what if one of the database crashes? we would lose that part of the data completely. We won't be able to read or write on it. I'm assuming we are keeping a replica of all the databases we have shared? Is there any better approach? That would be too expensive, I believe if we have many database instances.
In Database partition, we could create a replica of the main database (that would be just one replica) since data partition splits dataset in the same database.
One last question would be, why would we go for a master-slave approach? Do the slaves have complete data or are the data partitioned among the slaves? I believe that the Master database has complete data, but I'm not sure about the slaves? If the slaves have different data partitioned, let's say, how would the fault tolerance. Would it just read from the Master database then?
I know these are a lot of questions. Could you please help me? I am interested in this and that's why I have so many questions, which I am not able to grasp.


texts:
I have two questions about how to partition data across servers.

Can I create and connect two or more databases, with the records correctly associated, using only a mapping table stored in a third (or fourth), separate database to link them? For example, suppose I wanted to store a family tree with info such as date of birth, mother's maiden name, citizenship/immigration status, ethnic/religious affiliation, and so on for each member, but I wanted to partition the info across databases (and machines) to reduce the amount of personally identifiable information (PII) exposure that might occur through a single breach. I want to link a mapping table (stored in its own separate database) with another small database that includes tables of DOB and ethnic/religious affiliation and a few other details, and with a third database that contains the rest of the sensitive information (e.g., mother's maiden name, citizenship/immigration status, and place of birth). Bottom line: if I insert or pull a record, I want to insert/acquire ALL information about a single entity, but as noted above, I don't want to store all of the info in one database. Is it possible to store each database independently, with nothing in common, using only a mapping table from a separate database to link them? If not, what is the minimum that I must include in each database to achieve what I describe?
How would I determine the increase in time/space complexity due to the use of remote databases/multiple machines? The database system I'm contemplating is MySQL, but I'm assuming the answer would tend to be the same across the board.

EDIT: Because the point is to avoid storing all data in any single database/machine, database replication is not what I'm looking for. I want to actually partition the data across servers. Thanks so much for your help!

P.S. Please also note that this measure is not replacing encryption or any combination of security measures -- all of which are also being applied. This is in addition to those protective measures. We have multiple recent examples of millions of people losing entire sets of PII through the breach of a single database.


texts: SSIS flat file import - Issue with datetime format

texts:
I have a flat file with datetime format "5/19/2017 05:22:23.777 PM", and when I try to insert this data into a table using SSIS transformation I am getting these errors:

[Flat File Source [2]] Error: Data conversion failed. The data conversion for column ""DT_Date"" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[Flat File Source [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Flat File Source.Outputs[Flat File Source Output].Columns["DT_Date"]" failed because error code 0xC0209084 occurred, and the error row disposition on "Flat File Source.Outputs[Flat File Source Output].Columns["DT_Date"]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

The target column is defined as datetime.
Please help me on this, any help is really appreciated.
How can I solve this issue?


texts: mysql Lock wait timeout exceeded; try restarting transaction

texts:
we are running java application, running for ages, back end is db is MySQL, recently updated to mysql 5.6. Everything was running fine, past week started getting this error
 Lock wait timeout exceeded; try restarting transaction which looks like never stop ending
don't know what to do with this error to stop. why it is occurring all of sudden


texts: How do I use subquery on the same table in MySQL?

texts:
I have a query like this which takes a really long time to run. The table is around 4 million rows.

DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now;

I'm hoping I could create an index for (value_was, value_now) so I could do something like

DELETE FROM TABLE WHERE
ID1 IN (SELECT ID1 from TABLE where value_was IS NULL)
OR ID2 IN (SELECT ID2 FROM TABLE WHERE value_was <= value_now);

This table doesn't have primary key. It has two composite keys. And I guess I cannot use the same table in subquery, but how do I improve the performance of the first query?

Thanks very much any suggestion would be much appreciated.


texts: SQL aggregate function performance in a table with many records that is well-indexed

texts:
My question is about 3 tables that stores data of a single entity, and there is a report generation feature that refers these tables and calculate COUNT and AVG information.

There are three tables: review, rating, and rating_values, all three using MySQL InnoDB.

Review table

CREATE TABLE IF NOT EXISTS `review` (
  `review_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique review ID of this review.',
  `nid` int(10) unsigned NOT NULL,
  `sub_target` int(10) unsigned NOT NULL,
  `timestamp` int(10) unsigned NOT NULL DEFAULT '0',
  `score` float DEFAULT NULL,
  PRIMARY KEY (`review_id`),
  KEY `nid` (`nid`),
  KEY `uid` (`uid`),
  KEY `nid_uid` (`nid`,`uid`),
  KEY `score` (`score`),
  KEY `timestamp` (`timestamp`),
);

Review table acts as the the primary table. Each review can have 0 or more (we have a limit of 18 at the moment in application level) rating values. A rating value is something like "Food", "Location", etc for a restaurant review.

Rating table

'rating' (
  `review_id` int(10) unsigned NOT NULL, //foreign key to {review}.review_id
  `type` varchar(255) NOT NULL,
  `score` float DEFAULT NULL,
  KEY `review_id` (`review_id`),
  KEY `score` (`score`)
);

In this table, type is a machine name of the "food", "location", etc I mentioned above. There is a foreign key to the base table that stores these rating type information.
In our application, each rating can have multiple rating values that count towards the main rating. Say, there is "food", and there can multiple sub questions such as "food - appearance", "food - taste". These values can be 0 or more. If there are more than 1 sub question, this score field contains the average of the sub questions (please see the table below).

Rating value table

'rating_value' (
  `review_id` int(10) unsigned NOT NULL,// foreign key to the {review}.review_id
  `type` varchar(255) NOT NULL,
  `sid` int(10) unsigned DEFAULT NULL,
  `score` int(10) unsigned DEFAULT NULL,
  KEY `review_id` (`review_id`),
  KEY `type` (`type`)
)

This is where I expect most of the data would go into. Each sub question has an sid that is referred here.

A typical review would add rows like this:

Review:

review_id | nid | sub_target | timestamp | score
1         | 583 |         786| 1388167200| 4.32

Rating (please note how the two floats add up to 4.32 above):

review_id|type     |score
1        |food     |2.10
1        |location |2.22

Rating value:

review_id|type     |sid|score
1        |food     |1  |2.00
1        |food     |1  |2.30
1        |location |2  |2.12
1        |location |3  |2.22
1        |location |3  |2.22

We will be generating reports with COUNT and AVG on rating_value tables. However, there will be always a JOIN with review table, and the filter would be review.nid, which is indexed.
When accessing reviews, application will almost every time use the review_id, which are either primary or foreign keys in all tables.
Sometimes we need to CAST the score field to nearest integer.

Admittedly I'm not an expert in databases, but I spent a lot of time normalizing the above, and I ended up with the above database after all three steps. I'm eager to learn, so if you have suggestions to above table structure, please don't hesitate to mention them. Note that all review and rating tables will contain some extra data that I didn't mention here. the rating table has a text field to allow users to write a text review. That's why rating values and rating are separated.

The reports we generate are never going to run on all rows. Just a small set of rows filtered by the nid (plus additional filters).

My questions are:

Is it common to spread data of a single user operation across multiple rows? I expect the rating_value table to grow almost 10 times (in record count) than the review table.
While (inner) JOINing tables, and calculating averages, can database engines filter down the eligible rows using the indexes first?
If you have any thoughts about the above schema, please don't hesitate to comment on.


texts: Does MySQL support defining a table inside a session without appending it to the database?

texts:
As per stated in the title. Does MySQL support defining locally a table? (as a 'local variable' perhaps?)
I understand (according to 'Source') that MySQL has 'two types of variables': (1) local and (2) user variables, nevertheless neither can be a whole table.

Source:

https://stackoverflow.com/questions/13670659/mysql-local-variables

EDIT: I think that using the 'temporary' flag in 'create' is the answer
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

from the docs: https://dev.mysql.com/doc/refman/8.0/en/create-table.html


texts: How to check if a value is in an array in a table

texts:
I have a table if key - array pairs and need to check if a string is an element in a given array in this table. I have managed to write an if statement to check if the element is in an array but if I try to get the array from the table it does not work.

In the example below,
SELECT list FROM public."HelloWorldTable" WHERE name = 'HelloWorldList'
selects the list element that contains an array that is identical to the one in the other if statement but I can not seem to access it the same.

MWE

CREATE TABLE IF NOT EXISTS "HelloWorldTable"(
    Name text PRIMARY KEY,
    List text[]
);

INSERT INTO public."HelloWorldTable" (name, list) VALUES ('HelloWorldList', ARRAY['Hello', 'World']);

DO $$
BEGIN
  IF 'Hello' = ANY (ARRAY['Hello', 'World']::text[]) THEN
      RAISE NOTICE 'Found';
  ELSE
      RAISE NOTICE 'Not found';
  END IF;

-- failing attempt:

--  IF 'Hello' = ANY (SELECT list FROM public."HelloWorldTable"
--    WHERE name = 'HelloWorldList') THEN
--      RAISE NOTICE 'Found';
--  ELSE
--      RAISE NOTICE 'Not found';
--  END IF;

END $$;

I found this other question but I can not get it working the way I need it to. I have tried adding ::text and ::text[] to the second if statement but I only get different errors.
How can I solve this?


texts: MS Access write conflict XMIN at (2^32)/2 -1

texts: I have several clients running access 32 bit with a postgres database backends. A while ago, almost all the access apps started to return "write conflicts" when the end users would make changes to the records. Something intresting we also noticed was the XMIN value for all the records that could not be changed was at (2^32)/2-1 or 2,147,483,647. However, when selected in psql, the values were different. (higher than (2^32)/2-1). I am wondering what recourse I have to fix this issue so the applications will work again.


texts: Advisability of using STATISTICS_NORECOMPUTE

texts:
I've recently become involved in maintaining a set of databases with some interesting index problems.  One of the ones that aggravates me the most is the differences in the indexes between development, test, model and production machines.  Since the differences make tuning queries rather difficult syncing them up is one of my first projects.

As I've compared the test and model environments I've noticed that most of the indexes in the model environment have STATISTICS_NORECOMPUTE set to ON while those in test do not.  In all of the environments there is a nightly job that updates the statistics of all of the databases.

I've never dealt with STATISTICS_NORECOMPUTE before so here are my questions.  Are there any best practices when dealing with this setting?  If I'm doing statistics updates at the end of the day is it best to turn STATISTICS_NORECOMPUTE in all of the environments on all of the indexes?  Or is there a good reason not to?

EDIT:
I've found one of Kimberly Tripp's blogs on the subject here that seems to suggest that STATISTICS_NORECOMPUTE should be used sparingly at best.  But I'm still concerned about turning it off globally.  Has anyone tried this and what did they experience?


texts: Locks during the physical backup process by Mariabackup

texts:
I read the manual, but I do not fathom what you can do and what you should not do during the backup process by Mariabackup. Particularly, as MariaDB Enterprise Backup has additional features including minimised locks.
I have huge databases and want to know if it is safe to use them during the backup process.


texts: Tracking down unused Views and Stored Procedures

texts:
I am working on a SQL Server 2005 database that has two Microsoft Access ADP frontends.

This database needs a lot of love and attention which I am attempting to give it.

There are lots of stored procedures and views in this database, some are used, some are not, some are linked to Forms or Reports in Access, some are run directly in Access.

To make this project more manageable I would like to drop anything that is historical and not used any more. All the code is in source control so I can restore anything if needed.

What ways are there to log which stored procedures and views are used?

For the stored procedures I can add an INSERT INTO dbo.log with the name of the stored procedure to every stored procedure, but I can't think of a way to do this with the views.


texts: How to select date of only today from datetime column in mysql

texts:
I have table that have column of datetime that have value like 2017-01-13 17:01:56. Such values will have more than hundreds in a day like ordering in a restaurant. Now I want to select the orders that applied only today or date from datepicker. I don't know how to write the WHERE condition. My database is MySql and I do on c# with WPF.

Thanks.


texts: How do I generate the ENCRYPTED_VALUE for CREATE COLUMN ENCRYPTION KEY?

texts:
I can make a certificate in the windows certificate store for the master column key easily enough with:

$cert = New-SelfSignedCertificate `
    -Subject "Always Encrypted Sample Cert" `
    -CertStoreLocation Cert:\CurrentUser\My `
    -Type Custom
$thumbprint = $cert.ThumbPrint
Write-Verbose "Cert thumbprint $thumbprint"

$smoDatabase.ExecuteNonQuery(@"
CREATE COLUMN MASTER KEY [$($MasterKeyName)]
WITH (
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/My/$($thumbprint)'
);
"@);

The problem come with the CREATE COLUMN ENCRYPTION KEY

CREATE COLUMN ENCRYPTION KEY key_name
WITH VALUES (
    COLUMN_MASTER_KEY = [$($MasterKeyName)],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = $EncryptedValue
);

I don't know how to get the calue for ENCRYPTED_VALUE. I try the following:

C:\Users\zippy> $certProvider = new-object 'System.Data.SqlCLient.SqlColumnEncryptionCertificateStoreProvider'
C:\Users\zippy> $byte = [byte[]]@() # I don't know what to put here
C:\Users\zippy> $certProvider.EncryptColumnEncryptionKey('Current User/My/0879e6a82f7fe3f88e91e37e69f08c8fa1f4cc48', 'RSA_OAEP', $byte)

And then I get

  Exception calling "EncryptColumnEncryptionKey" with "3" argument(s): "Empty column encryption key specified.
  Parameter name: columnEncryptionKey"
  At line:1 char:1
  + $certProvider.EncryptColumnEncryptionKey('Current User/My/0879e6a82f7 ...
  + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : ArgumentException

So how do I get the value to put in EncryptColumnEncryptionKey()?


texts:
I install Oracle 11GR2 on Cenots 5.9 . i edited bash_profile to correct values for ORACLE_HOME and ORACLE_BASE and etc .
But when i want to run SqlPlus , " command not found " is shown . but when i set thwse values with export , i can run it .
can any one help me ?


texts:
I've just installed a Percona instance of Mysql on an Amazon server and can not find the my.cnf file anywhere. I found auto.cnf in /var/lib/mysql but there is nothing in that file like what I have seen for my standard mysql installs.

I'm using:

mysql  Ver 14.14 Distrib 5.6.41-84.1, for Linux (x86_64) using  6.2

Previously I've used 5.6.14 and 5.6.35 I guess this version changed a bit?

I tried mysqld --help --verbose | grep my.cnf | tr " " "\n", find . -iname "my.cnf", find / -iname "my.cnf", locate my.cnf, and whereis my.cnf. The whereis gave back a changed the shell, but had the same end result as all other commands which was no my.cnf found.

The whereis appended my: before my shell prompt e.g. [root@ip-ip_address /]#.

Mysql is running but from all SO threads I read there is no global variable I can select to find the conf file. Does anyone know how I can find where my configuration file lives?


texts: Filter and then sort products by availability

texts:
I have table with products. Each product can be avaible in 1+ suppliers, so product can be present multiple times.

products
- id_productSupplier (primary key)
- id_product (foreign key)
- availability (int - how many days takes to deliver product)

I need:

list of 10 product_id
1 record per id_product with lowest availability
sort it by availability of lowest available availability per product_id

For example, when id_productSupplier has availability=0, and other id_productSupplier with same product_id has availability=5, I want to order this product_id with availability=0.

Data:

id_productSupplier | id_product | availability
1 | 1 | 2
2 | 1 | 5
3 | 2 | 7
4 | 2 | 4
5 | 3 | 1

Expected result:

id_productSupplier | id_product | availability
5 | 3 | 1
1 | 1 | 2
4 | 2 | 4

Here what I have so far:

SELECT id_product FROM products GROUP BY id_product ORDER BY availability LIMIT 10

This does not really work, as it first randomly groups id_product and then orders by availability...

So, is there way to first get rid of records with greater availability for particular product_id and then sort it by availability?

I use MariaDB 5.5 (almost same as MySQL 5.5).


texts: Cons of using a star schema in a columnar database

texts:
My organisation is implementing a new data warehouse using a columnar data store (Redshift) and the performance is currently abysmal.  There are many causes for this but I think the key reason is because of the way we have our data structured, we're implementing a classic star schema design, which surely is optimal for a traditional RDBMS (OLTP) and not necessarily for OLAP?

I'm looking for someone with experience on this to briefly explain the pros and cons of structuring large quantities of data in a star schema on amazon redshift.

I'm obviously no expert, but I'm looking for some advice and guidance on this.

thanks,
Matt


texts: Do agile software development approaches apply to SQL?

texts: I would like to learn if agile software development methods/principles/patterns are valid to SQL programming as well. If yes, where would be a good place to start learning about that? Are there any articles or books targeting agile development in SQL context?


texts: Can I create a new user in MS SQL from windows and provide the SID

texts:
I am trying migrate databases and along with the database I am trying to also migrate the users without having to re-map the users. I was trying to recreate the windows user with the old server SID. Is this possible? I have an example of the code I have been trying to use but SQL gives me an incorrect syntax error.

Query:

USE [master]
GO
CREATE LOGIN [domain\ntuser] FROM WINDOWS
WITH
DEFAULT_DATABASE=[master],
SID=0x01000000000000000000000000000000000000000000000000000000;
GO

Error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '0x01000000000000000000000000000000000000000000000000000000'.


texts: How to fix the sys.host_summary table in MariaDB 10.3.13

texts:
using mysqldump while doing a backup gives the following error:

mysqldump: Got error: 1356: "View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them" when using LOCK TABLES

What can I do to fix that?


texts: GMT timezone in Mysql timestamp literal value

texts:
In brief

We want to input value for a timestamp column WITH timezone value in the literal string e.g. '201708-09 10:11 GMT +07:00'

In full

We have a sample table

create table some_table (
  start_time timestamp default current_timestamp
);

We can insert this value

into some_table values('2017-08-09 10:11');

but this will fail

into some_table values('2017-08-09 10:11 GMT +07:00');

How can we set timezone value for that column when insert?


texts: Slow While Loop, Query Improvment Assistance

texts:
I am working on creating a Datawarehouse.
I have created a Time Dimension (Dim_Time), at 5-minute intervals. Hour Aggregations will have [Minutes] = NULL.
For the purpose of this example:

CREATE TABLE [dbo].[Dim_Time](
    [TimeID] [int] IDENTITY(1,1) NOT NULL,
    [StartDateTime] [datetime] NULL,
    [Hour] [int] NULL,
    [Minute] [int] NULL,
  CONSTRAINT [PK_Dim_Time] PRIMARY KEY CLUSTERED
  ([TimeID] ASC)
  ) ON [PRIMARY]
GO

Then I have an Incoming Table, which is updated every 5 minutes from the OLTP Database.

CREATE TABLE [dbo].[Stg_IncomingQueue](
    [IncomingID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [int] NOT NULL,
    [TimeID] [int] NULL,
    [InsertTime] [datetime] NULL,
 CONSTRAINT [PK_IncomingQueueMonitor] PRIMARY KEY CLUSTERED
([IncomingID] ASC)
) ON [PRIMARY]
GO

I then have the following While loop. It's purpose is to get the correct 5-Minute time slot (TimeID) that relates to a particular incoming row:

WHILE 0 < (SELECT COUNT(*) FROM [dba_local].[dbo].[Stg_IncomingQueue] WHERE TimeID IS NULL)
BEGIN

    SELECT TOP 1 @IncomingID = IncomingID, @RowInserTime = InsertTime
    FROM [dba_local].[dbo].[Stg_IncomingQueue] WHERE TimeID IS NULL

    ;WITH DimTime
    AS (
        SELECT MAX(TimeID) AS MaxTimeID FROM [dba_local].[dbo].[Dim_Time]
        WHERE StartDateTime < @RowInserTime AND [Minute] IS NOT NULL
    )
    UPDATE [dba_local].[dbo].[Stg_IncomingQueue]
    SET TimeID = (SELECT MaxTimeID FROM DimTime)
    WHERE IncomingID = @IncomingID

END

It's such a simple process, and yet I cannot figure out a simpler way to update the TimeID. As per the CTE SELECT in the loop, I need to get the MAX(TimeID) where the StartDateTime is less then the rows InsertTime.
Because time is the only relationship, I am struggling with all options to do this in 1 query without the loop, but I feel it is possible

Please can someone help me out here either with a better option or confirming that this is the simplest way.

Thank you very much for your time and assistance.
Wade


texts: quickly jump to an object in Object Explorer in SSMS

texts: When expanding a tree with a large number of objects such as the Tables tree in SSMS Object Explorer, is there a method of quickly navigating to the table I am looking for, if I know the exact name or the first few letters of the name? I know that by typing the table name, the explorer will navigate to the respective table, but with table names like dbo.Table1, I want to avoid typing the 'dbo' portion of the name and just start typing Table1. I am not looking to use Filtering as I find this is cumbersome when navigating dozens of times throughout the day. I am using SMSS V11.0.3128.0.


texts: Mirroring after restoring the database on a new machine

texts:
We've had alot of problems with one of our database servers and two days ago the call was made to change a disk and reinstall the OS. Now this server functioned as part of a mirroring setup. Basically after the OS and SQL we're reinstalled i restored the Master database hoping mirroring would continue without causing my headpains.

I should state i'm not a DBA but i try to make due with the knowledge i have.

At the moment the server is giving the following errors;

Please create a master key in the database or open the master key in the session before performing this operation.

Followed by the following Information message;

Database Mirroring login attempt failed with error: 'Connection handshake failed.
Error 15581 occurred while initializing the private key corresponding to the certificate.
The SQL Server errorlog and the Windows event log may contain entries related to this error. State 88.'.  [CLIENT: XXX.XXX.XXX.92]

We've setup mirroring using Certifcates, this is because the servers do not run in the same domain. Now from googling i should have apperently made a backup of the master key (sadly i didn't even know SQL had a master key so i didn't).

What suprises me is that i can actually backup the certificate, i can login to the SQL Server using different credentials so it does seem to be able to encrypt/decrypt somethings (unless these things are not Master Key related).

Basically i'm somewhat at a deadend, how do i resolve these problems? Should i completely reinstall the mirroring? Is this at all recoverable? Any adivce and pointers in the right direction would be very much appreciated.

Answer (ill post as the answer later, need to wait 8 hours :( )

Bit fast for me answering my own question, but basically what i did was the following:

On the "restored" server

Remove the Endpoint
Remove the All Mirror related users, logins & certifactes
Create a new certificate and setup an endpoint
Create inbound access for the Witness and Principal

On the Witness and Principal;

Remove the user, login & certificate of "restored" server
Create a new user, login with the newly generated certificate from the restored server.

After this my mirroring started to Synchronize.


texts: Shrinking a physical transaction log file on a mirrored database

texts:
I have 2 SQL Server 2008 Standard Edition instances that are mirrored.  Both instances are running Service Pack 1.

I run Full backups daily and transaction log backups every half hour (through maintenance plans).

My physical DB file is ~22GB. My physical log file has grown to ~41GB.

The log seems to grow the most when running a weekly INDEX_REORGANIZE. Last week it grew by 15GB. The previous week by 10GB.

My full backups are now ~50GB in size. Shouldn't that be around ~22GB? I feel that the transaction log backups are not properly clearing out space from the log file.

What is the safest way to shrink the transaction log file? I was looking at running dbcc shrinkfile after a transaction log backup.


texts: When is data moved during an Oracle partition split?

texts:
I need to split an Oracle partition, and I'm confused about whether or not Oracle is going to physically relocate the data when I execute the split. Here's what I intend to do:

alter table SCHEMA.DATATABLE
   split partition MAXVAL_PART
   at (TO_DATE(' 2013-04-01 00:00:00',
               'SYYYY-MM-DD HH24:MI:SS',
               'NLS_CALENDAR=GREGORIAN'))
   into (partition PARTFOREXISTINGROWS
        tablespace EXISTINGMAXVALTABLESPACE,
         partition MAXVAL_PART
        tablespace NEWMAXVALTABLESPACE);

The problem is that my current MAXVAL_PART has 320 GB of data in it, and I don't want to physically move this data on the disk. If I cut off the partition at 2013-04-01, then there will be no data in the new partition, but I'm getting conflicting information about whether this will still necessitate a move of all the data. Ideally, Oracle sees that my new MAXVAL partition will be empty, defines it in the new table space, and I'm all done.

Will this command move the data that's already on the disk, or will it leave it in place and just create a new partition?

This article says Oracle will detect the empty partition and do a fast split
http://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm

But I see in a number of places that a split means all data gets moved to new storage. Which is it?


texts: How to speed up query which is using  operator?

texts:
I am using earthdistance extension (along with cube extension).

I have following query:

SELECT "User"."id"
    ,"User"."displayName"
    ,"User"."firstName"
    ,"User"."lastName"
    ,"User"."email"
    ,("User".position <@> Point(45, 45)) AS distance
FROM "Users" AS "User"
WHERE (("User".position <@> Point(45, 45)) <= 621.371192)
ORDER BY distance ASC
    ,"User"."id" ASC LIMIT 25;

Here is explain/analyze http://explain.depesz.com/s/61pp
I am using Postgres 9.3.

I have created GIST index on position column (which is of type point but it is not used by query (I am querying across 50k rows):

CREATE INDEX users_position_index
ON "Users"
USING gist
("position");


texts: Web application using SQL Server 2008 starts hanging

texts:
We have a custom web application that uses SQL Server 2008 (10.50.1600). It's worked fine for years, but lately web pages that return data "instantaneously" have begun showing the "endless swirly blue thing" in IE. Something is causing queries to run slower, lock, contend or something else. Doesn't seem to be deadlocking, as I am not seeing deadlock errors at the user level, which I have (very rarely) seen (and know they will trigger a specific error).

If users close out of IE completely and go back in, they usually are able to do the previous work without this hanging behavior (well, until such time as the hang happens again).

How would you begin to diagnose the source of this issue?


texts: Sybase query: How can I sort the extraction (by well formed columns)

texts:
community
I built a Sybase query statement in a script (using the isql command/line ) below is the code:

My script contains:

 isql -U databasename_dba -P password  <<EOF!
    select  customerid, notional, counterparty2 from table_1  inner join table_2
    on id = id2
    go
    quit
    EOF!

I'm getting this:

My desired output would be:

Thinking that extracting just the data (excluding the headers) might fix the output. I added in my code -b

isql -U databasename_dba -P password  -b <<EOF!

But the output doesn't change that much:

Thanks in advance for your ideas


texts: How to SELECT with two CSV files/... on PostgreSQL?

texts:
I have two CSV files which you can insert into the following PostgreSQL schema.
However, I cannot \COPY the data into the tables.
Pseudocode

Let data_id=1
Show name and event_value from data.csv and log.csv, respectively
Do this request from R with connection to PostgreSQL

Pseudocode in other words but not correct syntax

SELECT name, event_value
    FROM data, log
    WHERE data_id=1;

CSV files

I enclose the header in double quotes in CSV files (thread PG COPY error: invalid input syntax for integer).
data.csv where two integer/SERIAL + integer

"data_id", "event_value"
1, 777
1, 666
2, 111
4, 123
3, 324
1, 245

log.csv where integer/SERIAL + string so I put enclosed the latter one in double quotes

"data_id", "name"
1, "leo"
2, "leopold"
3, "lorem"

PostgreSQL

Schema and workflow

DROP TABLE IF EXISTS data, log;
CREATE TABLE data (
        data_id SERIAL NOT NULL, -- no primary key possible here, because possible have many events per data id
        event_value INTEGER NOT NULL
);
CREATE TABLE log (
        data_id SERIAL PRIMARY KEY NOT NULL,
        name INTEGER NOT NULL
);

-- https://stackoverflow.com/a/2987451/54964
-- https://soleil4716.wordpress.com/2010/08/19/using-copy-command-in-postgresql/
-- joanolo, HEADER
\COPY data FROM '/home/masi/Data/data.csv' DELIMITER ',' CSV HEADER;
\COPY log FROM '/home/masi/Data/log.csv' DELIMITER ',' CSV HEADER;

Work flow pseudocode

Start from clean table (drop old tables) and Create new tables
Insert two CSV files into the tables
Do SELECT now with connection to PostgreSQL from R

Output

masi@masi:~$ psql -f schema.sql
psql:schema.sql:1: NOTICE:  table "data" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE TABLE
COPY 6
psql:schema.sql:14: ERROR:  invalid input syntax for integer: " leo"
CONTEXT:  COPY log, line 2, column name: " leo"

I should not have mistakes in CSV files, based on the thread PG COPY error: invalid input syntax for integer.

OS: Linux Debian 8.7
PostgreSQL: 9.4
Related: R sqldf/... approach in the thread How to select on CSV files by R sqldf/data.table/dplyr?


texts: PowerShell command 'New-Item' fails when invoked through SQL Server Agent job

texts:
I have the following PowerShell script to create a folder at a share location. The share has full control permissions for everyone. I have granted all permissions for testing.

The problem is the script works fine when I manually execute. It also executes fine when I execute it as SQL Server Agent service account. But the same script fails when I try to execute as part of SQL Server Agent job. Am I missing something here?

$path = "\\server\shared\path\01222020"
New-Item -path $path -ItemType Directory -Force

Exception:
Date        1/22/2020 5:25:10 PM
Log     Job History (test)

Step ID     1
Server      serverA
Job Name        Test
Step Name       Test
Duration        00:00:01
Sql Severity    0
Sql Message ID  0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted   0

Message
Executed as user: domain\agent_service_account. A job step received an error at
line 9 in a PowerShell script. The corresponding line is 'New-Item -path $path
-ItemType Directory '. Correct the script and reschedule the job. The error
information returned by PowerShell is: 'Invalid Path:
'\\server\shared\path\01222020'.  '.  Process Exit Code -1.  The step failed.


texts: Using an RDBMS for querying tens of Terabytes of time-series data?

texts:
I stumbled over an algorithmic / data-structures question over on SO, which I'll short quote:

  (...) opinions regarding best of
  breed data structures to be used for indexing time-series (aka
  column-wise data, aka flat linear).

  Queries that will be required:

All values in the time range [t0,t1]

All values in the time range [t0,t1] that are greater/less than v0

All values in the time range [t0,t1] that are in the value range[v0,v1]

  The data sets consist of summarized time-series (...)
  The
  data set(s) in question are about 15-20TB in size, hence processing is
  performed in a distributed manner - because some of the queries
  described above will result in datasets larger than the physical
  amount of memory available on any one system.

  Distributed processing in this context also means dispatching the
  required data specific computation along with the time-series query,
  so that the computation can occur as close to the data as is possible
  - so as to reduce node to node communications (somewhat similar to map/reduce paradigm) - in short proximity of computation and data is
  very critical.

I'll readily admit that problems of this scale are way over my head, but my first hunch (even with the data sizes mentioned) given this problem would have been to ask whether they checked if large-scale RDBMS (well, I guess Oracle, or Oracle, right?) could handle this in a sane way.

So here's the question: Can a (enterprise?) RDBMS handle this kind of problem today with acceptable performance vs. a "hand-coded" solution.

Note: Hope this isn't too vague, and feel free to re-tag as appropriate :-)


texts: SQL Difference Optimistic Reading and Optimistic Writing

texts:
What is the difference between optimistic read and optimistic write?

Or probably more so, can someone define these 4?

(a) Optimistic Reading

(b) Pessimistic Reading

(c) Optimistic Writing

(d) Pessimistic Writing

Referring to question and answer here:
https://stackoverflow.com/questions/2741016/read-committed-snapshot-vs-snapshot-isolation-level

'READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. In contrast, SNAPSHOT does optimistic reads and optimistic writes.


texts:
We are planning to configure AlwaysOn on SQL Server 2016 (Windows 2016).This is Azure Machine

The DB size is around 15TB. Which is the best method to take backup from Primary and restore in Secondary Replica?

If i follow Backup and restore method definately it will take minimum 40+ hrs for backup and another 40+ hrs for restore, in our environment.

Please suggest. We are testing this in QA, based on this we will plan for PROD.


texts: SET ARITHABORT, .NET, and explanations inconsistency?

texts:
A few days ago I happen to have stumbled on a lot of cases in a new DB I've been given where ARITHABORT set to OFF literally devastate performances. Being my first time facing this specific problem I tried to read as much as possible about it, but I still have some hard time building a definitive picture.

The evidence:

ARITHABORT is one of the parameter that is officially documented by MS to be used in the choice of the plan to use
Plans created with ARITHABORT set to ON and set to OFF are clearly different
All versions of SQL Server ships with ARITHABORT set to ON
Microsoft recommends to keep it always set to ON
.NET always set it to OFF, which can, and do, leads to some horrible performances

Aside from wondering why .NET always set it to OFF, the matter would look quite settled, and thus I'd proceed to explain it to my tech leader, and that we need to set it back to ON every time we open a connection to the server.

But then, it seems there are quite a few voices advocating the contrary, one especially is linked in the second answer to this question:

Why would SET ARITHABORT ON dramatically speed up a query?

I've tried to go through the entire document, but he lost me quite quickly, especially in the part where he says that ARITHABORT is un-influent when compatibility lever is set at 80 or more, which is not clearly the case. And he lost me even more when, like many others, seems quite firm on stating that Microsoft documentation is wrong about the importance of the ARITHABORT parameter. On top of that, he suggests that if the SP is a lot slower when run from code and it's extremely faster when run from SSMS, the fault is in the SSMS and we should instead correct this one so that it set ARITHABORT to off.

Quite honestly...I'm a little lost. Is there some definitive evidence about the entire stuff?

Should we always force .Net to use ARITHABORT set to on, accordingly to with what the company who produce SQL Server says, or what?


texts: Get all the users that are managed by a targeted manager

texts:
I have the following model:

Table user:  (id, name, direct_manager_id)

Table customer: (id, name, managed_by_id)

i.e. users can have another user as their direct manager, Customers can be managed by a user.

My requirement is to create a simple select query that will retrieve all the customers that are managed by a specific user. moreover I would like that query to retrieve all the customers that are managed by users that their direct manager is that same user (1 level deep of direct management). and to retrieve all the customers that their direct manager is manged by a user that the direct manager of him is the targeted user (2nd level deep of direct management).... (to the nth level deep)

Here is a picture of the hierarchy:

           targeted User
            /          \
           user1         user2
           /  \              / \
          c1   c2           c3  user3
                                    /
                                   c4

So the select query with the targeted user id will select the customers c1-c4


texts: How do I change the instance name in MS SQL 2005 without installing a new instance?

texts:
I have an instance that really has nothing on it, it's merely been installed (it was intended for one project, but that never actually got done on this server, the project was done in duplicate on another server) so since I have the instance, I want to rename it.

Can this be done? How would I do so?

Further investigation (googling) says it's not really possible. Additional consultation with my coworkers says I might as well install a 2k8 instance and move on.


texts: How does DYNAMIC Row format handle NULL storage MYSQL 5.7?

texts:
As a newbie to Mysql I just can't understand how NULL values are stored in each row formats.

As per docs(last bullet-in), NULLs for fixed length columns (BIGINTs) also take their data space reserved. Meanwhile, NULLs for variable length columns are handled differently. The behaviour is defined under REDUNDANT row format description. I doubt what will be the behaviour with DYNAMIC / COMPRESSED row formats?

Note: I have tried to create 997 bigints in a table and have inserted value for one column. I had around 1957 rows but only had 26 pages, which means NULLs doesn't occupy space for fixed length. Am I understanding it in correct way? Please anyone help!
Specifications: 5.7.23 | Innodb | Dynamic Row | 16kb page size


texts:
I'm exporting database from oracle version 18c and importing it on oracle version is 12.1. I encounter below error while importing.
Connected to: Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file "/backup/dumps/RB-umps/CALYPSO_DEV2_2021.dmp"
I've used parameter version=12.1 while exporting the database.
Is there any compatibility issue between oracle 18c (18.12) & 12.1 ? Anyone face this error ?


texts:
We are considering RabbitMQ for queuing several thousand tasks per minute and then processing them from 300+ virtual machines. We have a smart scheduling web API that will intelligently (fairly) distribute tasks from 100+ queues to the VMs based on business needs. We know that RabbitMQ is definitely capable of handling this kind of load. Currently, we are querying the database each time across multiple tables which is contributing to 30-40% of the SQL Server load. Instead, we were thinking about setting up a trigger and then pushing the data to RabbitMQ (via an intermediate exchange API) as the data comes in. I realize that SQL Service Broker is also a potential option vs. RabbitMQ for queues, so I have a few questions about SQL Service Broker (SQL Server 2016 and up) that I hope you can answer:

Can we store the queue data in a durable manner and then fetch tasks from specific queues based on business needs? (I assume yes, but just confirming.)
How reliable is it for 100+ queues? I recall that the older versions of the Service Broker were quite unstable.
Can we create new queues dynamically / programmatically?
Can we create min-priority queues (i.e. as tasks are pushed onto the queue, it is sorted based on priority)?
What is the impact in terms of performance and load? At least with RabbitMQ, we can offload the queue management responsibility to a RabbitMQ cluster, leaving our SQL Server free to process other queries.
Is it true that we need to setup some kind of "conversation" between queues in order for it to work?
Are there any other differences / pros/cons that we need to be aware of considering one versus the other?


texts: SQL Server dynamic inserts based on another table's rows

texts:
I've derived the following pseudo-coded script as a generic insert with variables that I want to replace for each row in another table (I can assume that the columns are always the same for the insert and select statements):

INSERT INTO [dbo].[TNAME]
            --Replace with list of columns for [TNAME]
           ([craft]
           ,[description]
           ,[craftid])
    SELECT
        --Replace with list of columns for [TNAME]
       [craft]
      ,[description]
      ,coalesce(const.maxs, 0) + row_number() over (order by (select NULL))
  FROM [altdb].[dbo].[TNAME] cross join
  (select max([IDCOL]) as maxs from [TNAME]) const
  where [altdb].[dbo].[TNAME].[PRIMARYCOL] not in (select [PRIMARYCOL] from [TNAME])

The variables that I want to replace are: [TNAME], [IDCOL], [PRIMARYCOL] from the [altdb].[dbo].[LISTOFTABLES] table that looks like this:

  TNAME  |  IDCOL   |  PRIMARYCOL
  ------------------------------
  TABLE1 | TABLE1ID |    T1
  TABLE2 | TABLE2ID |    T2
  TABLE3 | TABLE3ID |    T3

So with the example list of tables above I want to be able to output 3 copies of the first script but with values for that row substituted into the variables and dynamically listing all the columns for that table in the INSERT and SELECT statements. How can I go about doing that?


texts: Is there any way to retrieve "Messages" in SQL Server through JDBC?

texts: By "Messages" I mean the Messages tab in the following figure, which includes "Warning", "n rows affected" and in the figure below, the execution time. In JDBC, ResultSet class is used to retrieve the "Results". Is there any way to get all the information in the "Message" tab through JDBC?


texts: Query to get data in one status not in another status


texts: Inserting 30k prices each minute, how can I skip an entry if the price did not change?

texts:
I receive around 30k price updates for various articles every minute and I need to write the new prices into our database (Which is a MySQL Database running InnoDB)

The table containing the prices right now has only three columns: Article_id, InsertTime, and ArticlePrice

My Problem is: I do not want to insert duplicate prices, but I want to store historical data. So when the price goes up or down I insert it. When it stays the same I skip it. Since I receive a lot of data I can not query first and then decide if I can insert or not, as this approach will be too time consuming.

Can this be solved on a database level? For example by adding a time column and using it as a primary key?

I also thought of solving it in the application itself, for example by keeping a List of the previous prices and compare them there before inserting into the database, but I want to guarantee a consistent database and this seems complicated.

Any help is appreciated.


texts: Efficient way to DELETE whole partition's data in table partitioned by HASH(DAY(datetime))?

texts:
I have a MySQL InnoDB table PARTITIONed by HASH(DAY(dateTime)).
I'd like to remove partitions holding data older than 10 days.

CREATE TABLE `partitioned` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dateTime` datetime NOT NULL,
  `sourceId` int(10) unsigned NOT NULL,
  `destinationId` int(10) unsigned NOT NULL,
  `times` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`,`dateTime`),
  UNIQUE KEY `key` (`dateTime`,`sourceId`,`destinationId`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY HASH(DAY(`dateTime`)) PARTITIONS 31

I've tried some queries:

EXPLAIN PARTITIONS SELECT * FROM `partitioned` WHERE DAY(`dateTime`) IN (1,2,3,4);
EXPLAIN PARTITIONS SELECT * FROM `partitioned` WHERE `dateTime` < '2015-01-01';

EXPLAIN for those two is showing that all partitions will be inspected. Not good!

EXPLAIN PARTITIONS SELECT * FROM `partitioned` WHERE `dateTime` IN ('2015-01-01','2015-01-02','2015-01-03','2015-01-04');

EXPLAIN for this one is showing that only required partitions will be inspected. I suppose that DELETE will also touch only required partitions, right?

Questions are:

So, what is the fastest available way to DELETE from table
partitioned by HASH?
Is it possible to get name of partition holding
DAY? Which function is MYSQL using for partitioning using
HASH(DAY())?

Or it is a bad idea and I should move to RANGE partitioning with DROPing and CREATing partitions on daily basis?

UPD: I see http://dev.mysql.com/doc/refman/5.5/en/alter-table-partition-operations.html that for MySQL >= 5.5.0 it is possible to TRUNCATE PARTITION, but what if I have AUTO_INCREMENT field in partitioned table? Is TRUNCATE PARTITION reseting auto_increment field or what?

UPD2: I'll try to answer my question.
Ok, we can get name of partition by EXPLAIN PARTITIONS SELECT * FROM partitioned WHERE dateTime IN ("2011-01-01"). As soon as we know name of partition, we can do ALTER TABLE partitioned TRUNCATE PARTITION {partitionName}, right? Is it a good way, what do you think?


texts: Why is PostgreSQL using 1-based arrays (first element being at index 1) and not 0-based arrays (first element being at index 0)?