texts:
I have a script that runs weekly. It updates multiple tables in my database. It turns out one statement attempts to update 150,000 rows on a table that contains 250,000 rows. That failed this week after I'd introduced a trigger on that table.
The trigger is auditing one column on that table - if the value in that column changes, a record is inserted into a table with identical name but in the 'audit' schema.
ALTER trigger [dbo].[tau_myTableName] on [dbo].[myTableName]
    after update
    as

    if exists (
        select 1 from inserted i join deleted d on i.primaryKeyId = d.primaryKeyId
        where   isNull(i.someColumn,-1) <> isNull(d.someColumn,-1)
        )

        insert into audit.myTableName (transaction_type, transaction_user, primaryKeyId, someColumn)
        select
        'U', SYSTEM_USER, primaryKeyId, someColumn
        from inserted;

The update statement that was hanging, is:
UPDATE  myTableName
    SET     unrelatedEmailAddressColumn= 'someone@someemail.com'
    WHERE   unrelatedEmailAddressColumn IS NOT NULL AND unrelatedEmailAddressColumn <> ''

Why should that update hang?
When the trigger is enabled, the script doesn't complete in 20 minutes. The statement before this one has persisted. When I disable the trigger the script completes in under a minute. The script runs on two databases - if I leave the trigger disabled on the second database then the first completes and the second hangs. It's a test environment with minimal users, the script is run immediately after a restore and sp_who2 doesn't show any unexpected connections.
The trigger should insert zero rows as it is auditing a column that is not updated by the statement. That said, I've discovered a second trigger exists after update on that table. The second trigger updates a column in any row being updated. This shouldn't conflict but I haven't explicitly set a trigger order, so I will either set an order or merge the triggers then test again. As an aside, I pulled the update statement out of the script file and run just that single update in SSMS with the same hanging effect.


texts: How to get all distinct words in a column

texts:
I have a table with a text column that can contain a single word, more words separated by a space or a NULL value. I need to get all the distinct words in that column (words non column entries).

For getting all the distinct column entries I use

SELECT DISTINCT(col_name) AS col_name FROM table_name ORDER BY col_name ASC

but I don't know how to get all the distinct words.


texts: How to architect an analytical BI platform?

texts:
I am working on a new BI project for a medium sized corporation. Right now there is no Analytical infrastructure in place and the reporting is done manually in Excel. There are a few different data sources (from different systems like Billing) that need to be integrated to perform the reporting and analytics. Some of them are data dumps that need some custom conversions to get into a database ready form. These have a large number of columns. These need to be processed, required columns filtered and aggregations done etc. Typically there's about 50 GB of data produced daily and will be inserted to the existing tables each day.

We've identified that an analytical database like Vertica can be worth looking into. We don't have any prior experience with non-OLTP databases. My understanding is that Vertica (and other like it) are read-optimised and good for analytical tasks. My question is how does it fair at the initial stage where the raw data are loaded and processed? Should we use a traditional OLTP database like Oracle for that and then use the Vertica for Star Schema, Dimensional modelling type datastore? Is Vertica suited for ETL scenarios?

How is a typical architecture of this kind of scenario?


texts: How to pass multi-valued characters in SSRS Report?

texts:
I have a SSRS report that has 3 data sets feeding off one data source. The main dataset is a stored procedure that is aggregating some data based on a set of parameters powered by the other two datasets.

The main stored procedure powering this report has 4 parameters. One is an ID for the type of data, two are for start and end dates and the third is just a flag parameter. The flag parameter is the multi-valued parameter where I want to pass multiple flag values that are also VARCHAR values.

In my stored procedure for my @Flag parameter, I have the common:

WHERE [Flag] IN (@Flag)

Then of course the @Flag parameter on the SSRS report set to allow 'Multiple Values' that are also populated from a query that is pulling those @Flag values from a dimension table.

My Problem

In most cases when dealing with INT values, using the same technique works. However, when I'm dealing with character values, it fails. If I choose one flag, the report works magically. If I select more than one flag, it doesn't seem to pass the flags correctly to the stored procedure and no results return.

When testing the multi-value flag directly in the stored procedure:

WHERE [Flag] IN ('A', 'B', 'C')

The stored procedure works correctly. So the problem is not the stored procedure, but how SSRS is passing the multi-valued values to the @Flag parameter.

Solutions Tried

I tried doing the following adjustments to the SSRS dataset for this @Flag parameters:

=join(Parameters!<your param name>.Value,",")

AND this one too:

=SPLIT(JOIN(Parameters!<your param name>.Value,","),",")

These all work on single values, but never multi-values.

What am I missing here?


texts: "Filter:" instead of "Index cond:" in index production usage

texts:
An index to optimize fetching multiple external identifiers and update underlying data was created. The index is a simple btree of the fields. The external identifier needs to be a string as it depends on the source the data.
create index reports_collector_existing_index
on reports(provider_id, connection_id, id_on_site);

The query is running very slow in production (PostgreSQL 13.2 in Amazon RDS), and the explain indicates that the index isn't fully being used with the identifiers field in it being ignored and instead it is going to the table via Filter:, as described below:
production=# explain analyse SELECT * FROM reports WHERE (("provider_id" = 38) AND ("connection_id" = 1175966)
AND ("id_on_site" IN ('104992038676612727711', '109990966674014073837', '105453161196787392789', '102255741242869923419', '104173931474885108430', '106078528214550610325', '115918069366580690259', '101145134508987068276', '115359007991887431873', '115909079192172351351', '109019484929659145996', '114223295457618192193', '100701806344027988654', '116172834178410664645', '109271042536972421195', '112763372050251071013', '114228853909813896127', '100378649534685813241', '109853822973690938225'));

 Index Scan using reports_collector_existing_index on reports  (cost=0.70..2.78 rows=1 width=695) (actual time=1588.672..78750.175 rows=50 loops=1)
   Index Cond: ((provider_id = 38) AND (connection_id = 1175966))
   Filter: (id_on_site = ANY ('{104992038676612727711,109990966674014073837,105453161196787392789,102255741242869923419,104173931474885108430,106078528214550610325,115918069366580690259,101145134508987068276,11535900799188743187>
   Rows Removed by Filter: 3675
 Planning Time: 2052.299 ms
 Execution Time: 78750.268 ms
(6 rows)

The same database dumped into a local environment (although excluding more than 1tb of tables' data for the sake of development) is used to run the same query. The explain shows then proper index usage:
development=# <same query from above>
 Sort  (cost=210.14..210.15 rows=6 width=615) (actual time=0.207..0.209 rows=50 loops=1)
   Sort Key: id_on_site
   Sort Method: quicksort  Memory: 43kB
   ->  Index Scan using reports_collector_existing_index on reports  (cost=0.41..210.06 rows=6 width=615) (actual time=0.038..0.177 rows=50 loops=1)
         Index Cond: ((provider_id = 38) AND (connection_id = 1175966) AND (id_on_site = ANY ('{104992038676612727711,109990966674014073837,105453161196787392789,102255741242869923419,104173931474885108430,106078528214550610325,115>
 Planning Time: 0.637 ms
 Execution Time: 0.243 ms

What could be causing the production environment to not fully use the index?
I have run ANALYZE on the table and also created statistics on the columns involved. I have tried recreating the index, using ASC, nulls last, and partial indexes. All result in the same slow Filter:. I have no idea why the sort node is only present on dev, though I would love the production had that sort too. Filtering on the first 2 integers is very fast.
On production it is going through all records in the provider_id/connection_id scope, that is why 3675 iterations.


texts: SQL Server - Adding non-nullable column to existing table - SSDT Publishing

texts:
Due to business logic, we need a new column in a table that is critical to ensure is always populated. Therefore it should be added to the table as NOT NULL. Unlike previous questions that explain how to do this manually, this needs to be managed by the SSDT publish.

I have been banging my head against the wall for a while over this simple-sounding task due to some realizations:

A default value is not appropriate, and it cannot be a computed column. Perhaps it is a foreign key column, but for others we cannot use a fake value like 0 or -1 because those values might have significance (e.g. numeric data).
Adding the column in a pre-deployment script will fail the publish when it automatically tries to create the same column, a second time (even if the pre-deployment script is written to be idempotent) (this one is really aggravating as I can otherwise think of an easy solution)
Altering the column to NOT NULL in a post-deployment script will be reverted each time the SSDT schema refresh occurs (so at the very least our codebase will mismatch between source control and what is actually on the server)
Adding the column as nullable now with the intention of changing to NOT NULL in the future does not work across multiple branches/forks in source control, as the target systems will not necessarily all have the table in the same state next time they are upgraded (not that this is a good approach anyway IMO)

The approach I have heard from others is to directly update the table definition (so the schema refresh is consistent), write a predeployment script that moves the entire contents of the table to a temporary table with the new column population logic included, then to move the rows back in a postdeployment script. This seems risky as all hell though, and still pisses off the Publish Preview when it detects a NOT NULL column is being added to a table with existing data (since that validation runs before the predeployment scripting).

How should I go about adding a new, non-nullable column without risking orphaned data, or moving data back and forth on every publish with lengthy migration scripts that are inherently risky?

Thanks.


texts: Looking for performance improvements for this query

texts:
This query is taking a long time to execute. We are expecting results in 0 sec, if possible.

Please help me to do any other following:

Rewrite the query
Suggest any indexes
Any other optimization that may be needed.

Also, please help isolate root cause.

Here is the Query And Explain Plan:

mysql> explain SELECT * FROM (`xgen_studentMLs`)
    LEFT JOIN `xgen_quizzes`
         ON xgen_studentMLs.quizid = xgen_quizzes.idnum
    WHERE
          `student` = 27126
      AND xgen_studentMLs.topic = 1829
      AND xgen_studentMLs.metatype = 'topic'
      AND (xgen_studentMLs.department='Nursing' OR xgen_studentMLs.department='Biology')
      AND (xgen_quizzes.book IS NULL OR xgen_quizzes.book=0)
    ORDER BY xgen_studentMLs.timestamp DESC LIMIT 100;

+----+-------------+-----------------+--------+---------------+---------+---------+------------------------------+------+-----------------------------+
| id | select_type | table           | type   | possible_keys | key     | key_len | ref                          | rows | Extra                       |
+----+-------------+-----------------+--------+---------------+---------+---------+------------------------------+------+-----------------------------+
|  1 | SIMPLE      | xgen_studentMLs | range  | student,mult  | mult    | 265     | NULL                         |   18 | Using where; Using filesort |
|  1 | SIMPLE      | xgen_quizzes    | eq_ref | PRIMARY       | PRIMARY | 4       | prepu.xgen_studentMLs.quizid |    1 | Using where                 |
+----+-------------+-----------------+--------+---------------+---------+---------+------------------------------+------+-----------------------------+

Here are table structures:

mysql> show create table xgen_quizzes\G
*************************** 1. row ***************************
       Table: xgen_quizzes
Create Table: CREATE TABLE `xgen_quizzes` (
  `idnum` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL DEFAULT '0',
  `timestarted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `timefinished` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `questionlist` mediumtext NOT NULL,
  `topics` mediumtext NOT NULL,
  `totalnum` int(11) NOT NULL DEFAULT '0',
  `completednum` int(11) NOT NULL DEFAULT '0',
  `assignment` int(11) NOT NULL DEFAULT '0',
  `department` varchar(255) NOT NULL DEFAULT '',
  `book` int(11) NOT NULL DEFAULT '0',
  `cqs` mediumtext NOT NULL,
  `metatype` varchar(25) DEFAULT 'topic',
  PRIMARY KEY (`idnum`),
  KEY `userid` (`userid`),
  KEY `assignment` (`assignment`)
) ENGINE=InnoDB AUTO_INCREMENT=13547573 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table xgen_studentMLs\G
*************************** 1. row ***************************
       Table: xgen_studentMLs
Create Table: CREATE TABLE `xgen_studentMLs` (
  `student` int(11) NOT NULL,
  `topic` int(11) NOT NULL,
  `ML` float NOT NULL,
  `MI` int(3) NOT NULL DEFAULT '0',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `department` varchar(255) NOT NULL,
  `metatype` varchar(25) DEFAULT 'topic',
  `quizid` int(11) NOT NULL DEFAULT '0',
  KEY `student` (`student`),
  KEY `mult` (`topic`,`department`,`student`,`timestamp`,`ML`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


texts: Best practices for allowing rollback of individual customers on a shared database

texts:
Imagine having multiple customers (as an example) on a shared database, and needing a backup system so that a single customer can be rolled back without affecting the other customers. - It's not important whether the customers themselves can order the rollback or only an administrator of sorts, but for a host of reasons is having a database for each customer not an option.
Edit:
There would be a customer table, but the customer's data will be spread across many tables. Maybe because a customer can add stuff only they can access.
\Edit
How can this best be achieved?
I have considered the following options (don't mind the order):
#1
Making a regular database backup and building a mechanism that clears all relevant rows in all relevant tables and then reads the customer's data from the backup-file and insert it into the database.
This mechanism will have to be maintained as the database changes.
#2
Making a shadow-table for each relevant table.
The needed mechanism will essentially be the same as for #1, but it won't be necessary to read from an external backup-file. The mechanism and shadow-tables will have to be maintained as the database changes.
This will work better than #1 if customers need be able to order rollback autonomously.
Any option I'm missing?
Anything I should be mindful about?
Most importantly: Any best practice for doing this sort of thing?
I'm using PostgreSQL in case that includes anything that can help.


texts: How to set Proxy Settings when installing Oracle 11g on Windows 7 Ultimate Edition?

texts:
I have downloaded the file - "win32_11gR2_database_1of2.zip" from oracle site.

While installing Oracle 11g, I find this window in the first place:

What to do?

What values should I put on the dialog box?

Where can I get the related info very concisely?


texts: I used the CDC to create a table for logging from the database. For each table, the INSERT operation that is performed by the user stores, with the SYSTEM_USER command in the triggers defined on the tables, but unfortunately the amount Return is a value defined by the SP_CHANGEOWNER command.


texts: Is the transfer time from PostgreSQL to the application optimisable?

texts:
I have a very simple database running on PostgreSQL 9.3. In this database I have one table called "vehicle_states" created as is:

create table MOBILE_STATE_MESSAGE
(
    id_mobile_state_message SERIAL PRIMARY KEY,
    topic_name VARCHAR(100) NOT NULL,
    date_time TIMESTAMP NOT NULL,

    latitude DOUBLE PRECISION NULL,
    longitude DOUBLE PRECISION NULL,
    altitude REAL NULL,
    depth REAL NULL,
    heading REAL NULL,
    roll REAL NULL,
    pitch REAL NULL,
    speed_along_speed REAL NULL,
    speed_along_direction REAL NULL,
    speed_vector_vx REAL NULL,
    speed_vector_vy REAL NULL
);
CREATE INDEX topic_name_idx on mobile_state_message (topic_name)

So this table is the history of some vehicles. Each vehicles has a different name (the topic_name column). Then, I have an index on the topic names.

This database is populated with data. I have 250000 positions for each vehicles, and 5 vehicles. So I have 1000000 rows in the table.

In pgadmin I run the following query :

SELECT
  mobile_state_message.latitude,
  mobile_state_message.longitude
FROM
  public.mobile_state_message
WHERE
  topic_name = 'MyCar';

So, I ask for all the positions of "MyCar" vehicle.
This request took 14 seconds to execute in pgAdmin.

When I start this request in "analyze" mode, with EXPLAIN ANALYZE, I have the following result :

Bitmap Heap Scan on mobile_state_message  (cost=6448.78..26264.75 rows=251917 width=16) (actual time=28.570..79.810 rows=250000 loops=1)
  Recheck Cond: ((topic_name)::text = 'MyCar'::text)
  ->  Bitmap Index Scan on topic_name_idx  (cost=0.00..6385.80 rows=251917 width=0) (actual time=25.752..25.752 rows=250000 loops=1)
        Index Cond: ((topic_name)::text = 'MyCar'::text)
Total runtime: 87.527 ms

Here I see that the request is processed in 87 ms.

I read some posts on the difference between EXPLAIN ANALYZE and Normal request. They said that the main difference is that when we perform an ANALYZE, there is no result data serialization.

My database server and pgAdmin are running on the same computer, so this can't be a network bandwidth problem.

So can I speed up this serialization time ?

Do you think that 14 seconds to fetch 250000 rows containing 2 double values is a normal timing?

Before posting this message I read the Slow Query Question wiki page. I played with some server parameters like shared_buffers, effective_cache_size, and work_mem, but these parameters didn't speed up anything for my particular problem.


texts: Difference between idx_tup_read and idx_tup_fetch on Postgres

texts:
On Postgres 8.4 when you do:

select * from pg_stat_all_indexes where relname = 'table_name';

It returns the fields idx_tup_read and idx_tup_fetch, what is the difference?


texts: Which is the most performant way to copy a number of SQL Server tables to a readonly SQLite database?

texts:
I have a database with about 50 tables with use together about 1 GB space.
The SQLite database will be readonly.
This copying will be done once each month.

EDIT:

I see one possible solution using linked server via SQLite 3 ODBC Driver which results in rather simple t-sql scripts. I don't know about its performance.

I guess that other solution require more work, but obtain better performance.


texts:
Situation: There is one SQL Server (2014) database that has multiple partitioned tables, that all share the same partitioning strategy. Tables have a partition per unique [PartitionKey]. Every [Id] is unique and the column [PartitionKey] is reused about 20 million times per table, but each unqique value isn't spread over different partitions. [PartitionKey] is a formatted date(yyyyMM) stored as an int.

Table Definition

CREATE TABLE [dbo].[Table1](
    [Id] [uniqueidentifier] NOT NULL,
    [PartitionKey] [int] NOT NULL,
    [CreationDate] [datetime] null,
CONSTRAINT [PK_NC_Table1] PRIMARY KEY NONCLUSTERED
(
    [Id] ASC
))

Considering the following queries:

Query 1: Search for unique result (most important)

SELECT t1.id, t2.PartitionKey
FROM Table1 t1
WHERE t1.id = @id and t1.PartitionKey = @partitionKey

Query 2: Search for a range of data within a partition

SELECT t1.id, t2.PartitionKey
FROM Table1 t1
WHERE t1.PartitionKey = @partitionKey
AND t1.CreationDate > @creationDate

Would the order of a partitioned Clustered index matter? If so, how does it affect performance and what order would be best to suit my case?

Aligned Index: [Id] first

CREATE CLUSTERED INDEX [CI_Id_PartitionKey] ON [dbo].[Table1]
(
    [Id],
    [PartitionKey]
) ON [PartitionKeyScheme]([PartitionKey])

Aligned Index: [PartitionKey] first

CREATE CLUSTERED INDEX [CI_Id_PartitionKey] ON [dbo].[Table1]
(
    [PartitionKey],
    [Id]
) ON [PartitionKeyScheme]([PartitionKey])


texts: Why are queries parsed in such a way that disallows the use of column aliases in most clauses?

texts:
While trying to write a query, I found out (the hard way) that SQL Server parses WHEREs in a query long before parsing the SELECTs when executing a query.

The MSDN docs say that the general logical parsing order is such that SELECT is parsed nearly last (thus resulting in "no such object [alias]" errors when trying to use a column alias in other clauses). There was even a suggestion to allow for aliases to be used anywhere, which was shot down by the Microsoft team, citing ANSI standards compliance issues (which suggests that this behavior is part of the ANSI standard).

As a programmer (not a DBA), I found this behavior somewhat confusing, since it seems to me that it largely defeats the purpose of having column aliases (or, at the very least, column aliases could be made significantly more powerful if they were parsed earlier in the query execution), since the only place you can actually use the aliases is in ORDER BY. As a programmer, it seems like it's missing a huge opportunity for making queries more powerful, convenient, and DRY.

It looks like it's such a glaring issue that it stands to reason, then, that there are other reasons for deciding that column aliases shouldn't be allowed in anything other than SELECT and ORDER BY, but what are those reasons?


texts: Postgres - autovacuum on several big tables

texts:
I have an issue with autovacuum - I have several big tables (over 10 million records) which are updated very frequently (500 updates per second). Everything works OK until bloat happen :)

As I can see, some of the tables are not autovacuumed for more than 10 minutes, and in that period, a lot of dead tuples are generated which then kill performance.

So, my question is - what is the flow of autovacuum process?
E.g. If I have 10 tables that should be vacuumed (autovacuum threshold is reached on those tables), will Postgres vacuum tables one-by-one, or will it vacuum all tables in parallel? I couldn't find that info in official documentation. Another question - Postgres will spawn single autovacuum worker per database? If I have only one database, then I do not have to change "autovacuum_max_workers" parameter?

I have changed the following autovacuum config parameters:

autovacuum_vacuum_cost_limit = 1000
autovacuum_max_workers = 6
autovacuum_vacuum_scale_factor = 0.01
maintenance_work_mem = 512MB

Beside that, I have configured autovacuum threshold on big tables (because 1% od 10 million is 100k):

ALTER TABLE mytable SET (autovacuum_vacuum_threshold = 15000);
ALTER TABLE mytable SET (autovacuum_vacuum_scale_factor = 0);


texts: Full-text search migration 2005 -> 2012 (.ndf file?)

texts:
So I'm trying to figure out the details for migrating from SQL Server 2005 to SQL Server 2012.

I tried to do restore and backup of a DB from 2005 to 2012. This worked well and for the text search catalog created a .ndf file.
I tried to detach and attach a DB from 2005 to 2012, this also seemed to work fine including the text search but I'm wondering where's the catalog files on the file system because I don't see a .ndf file or a dedicated folder for text search catalog (the way we used to have in versions prior to 2008)?

(PS. I atttached only .mdf and .ldf files, not sure how to attach a specific text search catalog file because it's multiple files)

I might have missed something but any suggestions will be helpful, thanks!


texts: Append values to MySQL column and delete old values

texts:
Engine MySQL 5.6.37

I have a table with the following structure:

CREATE TABLE `multi` (
  `unitid` varchar(16) NOT NULL,
  `value` varchar(21) DEFAULT NULL,
  `date_time` timestamp NULL DEFAULT NULL,
   PRIMARY KEY (`unitid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

When I perform inserts on this table, I would like to append the 'value' column with new values seperated by  a delimiter, and then only keep the latest 3 values at any point.

I am doing it this way using PHP PDO  -

 $sql = "INSERT INTO multi (unitid, value, date_time)
VALUES (?,?,?) ON DUPLICATE KEY UPDATE presence_value= CONCAT(value, ?),
date_time = ?;";
$stmt = $db->prepare($sql);
$stmt->execute([$unit, $value, $time, ','.$value, $time]);

Whenever this php is called, it will keep appending the value column with new values separated by comma.

I then perform a CRON job every few minutes to alter the value column and keep the latest 3 values.

Is there a better way to do this? I would like to eliminate the CRON job altogether. Can I do this using triggers?


texts:
I need to change returned value, from select statement, based on several conditions. I tried something like that:

,CASE i.DocValue
  WHEN 'F2' AND c.CondCode IN ('ZPR0','ZT10','Z305') THEN c.CondVal
  ELSE 0
END as Value

why it is not working and what is recommended way to do this ?
There will be next several WHEN conditions.

Thank you


texts: Collations and codesets in PostgreSQL 9.x

texts:
In Windows 7, the collation is set to "English_United States.1252" when I select the "English, United States" locale in the installer. In Linux, the collation is set to "en_US.UTF-8" by default.

I haven't found any way to set the collation's codeset to UTF-8 in Windows so I'm just wondering if the databases will behave differently in these example cases? Or in general, what's the impact of the codeset part of a collation to begin with?

I have the encoding set to UTF-8 in both databases and the question is whether the difference in codesets of collations will cause difference in behavior.


texts: How to make MySQL 8 alert when integer overflow happens during INSERT ... ON DUPLICATE KEY?

texts:
Let's consider we have a simple table with auto-incrementing integer ID and some unique column:

CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `value` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `value` (`value`)
);

Imagine we inserted into it so many times that ID reached the maximum:

INSERT INTO test VALUES
(4294967294, 1),
(4294967295, 2);

Now we are trying to insert one more row and if it's duplicate then change the value to 3:

INSERT INTO test (`value`) VALUES (1) ON DUPLICATE KEY UPDATE `value` = 3;

What is the expected behaviour?

        id     value
----------  --------
4294967294         3
4294967295         2

What we get in fact:

        id     value
----------  --------
4294967294         1
4294967295         3

How? Because of the integer overflow the auto-generated ID of the new row was again 4294967295.

So now the question. Is it possible to make MySQL 8 alert if the overflow happens during query? In my case such silent overflow caused huge data loss. Because all newly added rows just overwrote the last row.

PS I'm not asking how to fix or prevent such overflow. That's obvious. I'm asking exactly how to catch such errors.


texts: Recover data in a SQL Server table without backups

texts: We are having a database without backup to restore. (we didn’t keep backups as client not having enough space in his SQL Server 2012). Unfortunately some data has been lost and we need to recover those. Please provide a way of doing this.


texts:
I got hit by error "Tale is full" on my in memory database.

My database is 4.5GB in size and has 300 partitions.

I run MySql 5.6.17 version_compile_machine=x86_64 on 64 bit Windows 10,  16GB ram. Disk are NTFS and have 100GB spare place.

I've setup tmp_table_size and max_heap_table_size to 12GB.
Those values ware 30 MiB untill "table is full" error, so it seems that they ware and are ignored (changing to 12GB did not help.)

If it has to do something with this setting innodb buffer pool size is 12GiB also.

System runs on 50% of RAM memory.

What else can do to increase allowed size of in memory DB?


texts:
We have a database Ms
DBCC CHECKDB (MS)

Or
ALTER DATABASE MS SET SINGLE_USER
DBCC CHECKDB(MS,REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE MS SET MULTI_USER

Error message

Msg 0, Level 11, State 0, Line 0:
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0:
A severe error occurred on the current command.  The results, if any, should be discarded.

After spending hours and testing each table, the problematic table was found "LC"
DBCC CHECKTABLE(LC)

Error Message

Msg 211, Level 23, State 51, Line 1:
Possible schema corruption. Run DBCC CHECKCATALOG.

Now,
DBCC CHECKCATALOG

Message

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I also tried to have a look on indexes
SELECT * FROM sysindexes

Error Message

Msg 211, Level 23, State 51, Line 1:
Possible schema corruption. Run DBCC CHECKCATALOG.
Msg 0, Level 20, State 0, Line 0:
A severe error occurred on the current command.  The results, if any, should be discarded.

Again,
SELECT * FROM LC

Or
DROP TABLE LC

Error Message

Msg 211, Level 23, State 51, Line 1:
Possible schema corruption. Run DBCC CHECKCATALOG.
Msg 0, Level 20, State 0, Line 0:
A severe error occurred on the current command.  The results, if any, should be discarded.

I would not mind data loss for that particular table, I want to have the table dropped. Any ideas, please help!


texts: How use raise notice in nested and sub-function?

texts:
I'm using PgAdmin to create multiple pgsql functions.
I'm beginner on this environment.
I'm using 'raise notice' to help me for debugging phase.

I have two functions with 'raise notice' statement.
The first function calls the second function.

I can show the 'raise notice' statement for the first function.
I can't show the 'raise notice' statement for the second function. In place, the console show blank lines for each 'raise notice' that should be shown.

Function 1 - Example :

raise notice 'Function 1 executing';    -- This raise notice works
PERFORM public.function_2();

Function 2 - Example :

raise notice 'Function 2 is now running';   -- This raise notice doesn't work

Is it possible to do what I want?


texts: Fast local backup - Very slow backup via Network

texts:
Since last week, i'm having this problem.

Backups are taking forever to be done if I make them via Network.

If I backup the database to a local disk ( or N'nul' ) it's very fast.

It's strange because even with my personal PC I can't make network backups ( there's nothing with the cluster, or configuration that we have in the production instances ).

In This connect item  they say something about a SQL 2005 issue.

What can we track to find the issue? What perfmon counter can we use?

We have already restarded the servers. Restarted instances. restarted clusters. Disabled Anti-virus. Nothing helped us.

This is some logs:

Via NUL:

  backup database My_DB to
  disk =N'nul'
  --Processed 542896 pages for database 'My_DB ', file
  'My_DB ' on file 1.

  --Processed 382456 pages for database 'My_DB ', file
  'My_DB ' on file 1.

  --Processed 1 pages for database 'My_DB ', file
  'My_DB ' on file 1.

  --BACKUP DATABASE successfully processed 925353 pages in 19.911 seconds (
  363.081 MB/sec).

Via Network:

  backup database My_DB to
  disk ='\Server\D\BACKUPS_TEST\teste.My_DB .bkp'

  --Processed 542896 pages for database 'My_DB ', file
  'My_DB ' on file 1.

  --Processed 382456 pages for database 'My_DB ', file
  'My_DB ' on file 1.

  --Processed 1 pages for database 'My_DB ', file
  'My_DB ' on file 1.

  --BACKUP DATABASE successfully processed 925353 pages in 129.223 seconds
  (15.944 MB/sec).

I can copy files through servers with no problems. I really have no Idea what to do. I would be grateful to share more information.

EDIT1:

Something I notice is this query below ( in the destination server ):

WAITFOR (RECEIVE message_body FROM WMIEventProviderNotificationQueue),    TIMEOUT 5000

There are a lot of websites saying different things about it, so, I don't know for sure what it is.

I can see this query running via trace or with sp_whoisactive. It runs, then it restarts itself. Could this be the problem ??


texts: Create index on very large table with many shared values

texts:
I'm looking to create an index on a large table (~50 million rows) on a field with lots of non-unique values.

Table schema looks like:

 Column |         Type          | Modifiers | Storage  | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
 gid    | character varying(20) |           | extended |              |
 word   | character varying(30) |           | extended |              |
 stat   | double precision      |           | plain    |              |
Has OIDs: no

I want to create an index on the 'word' column. There is a fairly regular pattern where each word appears about 1000 times. I need to do fast SELECT * FROM mytable WHERE word='something';queries. Creating a regular B-Tree index on these tables takes a ton of time but does substantially improve performance.

I'm uncomfortable with my solution right now for several reasons

(1) The selection of a B-Tree index isn't particularly motivated. Are there alternative indexing schemes that perform better on fields with highly duplicated values?

(2) I'm in a production environment where these tables pop into and out of existence fairly regularly. Because not all tables will always be heavily queried I've opted to only build indexes on a table when certain (outside the DB) applications are triggered, such that I know 10k+ queries will be performed on the table+field. Waiting 20 minutes while an index is being created isn't ideal however. The situation is delicate; optimization gained by creating the index competes with the initial time-sink required to create the index. Are there 'cheaper' indexes to create? perhaps ones that will perform overall slightly worse than B-Tree but have less initial creation cost?


texts: Upgrading PostgreSQL 9.4.9 to 9.6.x on Debian

texts:
Current version:

PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

OS:

Debian GNU/Linux 8.6 (jessie)

Any considerations and/or recommendations on upgrading to 9.6.x? I've read a few questions suggesting replication to the higher version while, others suggest using pg_upgrade.


texts: Error when installing SQL Management Studio 2014 from product installer

texts:
My Inno Setup 5 installer internally calls SQLManagementStudio_x86_ENU.exe (2014 SP1), and this consistently fails.

However, when I open Command Prompt in administrator mode and execute SQLManagementStudio_x86_ENU.exe directly, passing in the exact same arguments that are used by the Inno Setup 5 installer, SSMS installation is consistently successful.

I am updating the installer for an older product. One of the components deployed by the installer used to be SQL Server Management Studio 2005. I am updating that installer to instead install SQL Server Management Studio 2014. The installer is produced in Inno Setup 5.

I am executing on the 2014 SP1 version of SQLManagementStudio_x86_ENU.exe.

I am using the following arguments: /ACTION=install /QS /IACCEPTSQLSERVERLICENSETERMS /FEATURES=ADV_SSMS,BC,Conn,SDK,SNAC_SDK,Tools /UpdateEnabled=False

I am installing to a virtual machine running Windows 7 SP1 32-bit. I am on a domain account, with local administrator rights on the current machine. The following versions of the .NET Framework are installed:

v4.5.51209 (Full)
v4.5.52109 (Client)
v3.5.30729.5420
v3.0.30729.5420
v2.0.50727.5420

The relevant sample from the end of my SqlSetup.log file is:

02/22/2016 17:43:24.217 Saved .Net security policy file
02/22/2016 17:43:24.218 Attempting to release setup mutex
02/22/2016 17:43:24.219 Setup mutex has been released
02/22/2016 17:43:24.220 SQM key not found
02/22/2016 17:43:24.220 Setup closed with exit code: 0x84C40013
02/22/2016 17:43:24.221 ======================================================================

I tried pasting the Full Output from my SqlSetup.log here. However, I couldn't include it all as this took me over the content limit for how much text can be in a single post.

Relevant section from my Inno Setup 5 log below.

Note that this section includes custom logging information I have added myself so as to trace when registry keys are being analyzed, and what the results are. At present, the CheckSSMS2014SP1x86Installed function is behaving correctly as it is identifying that SSMS 2014 has not been installed.

2016-02-22 17:42:25.820   InstallSSMS2014SP1x86
2016-02-22 17:42:25.820   SSMS2014SP1RegistryCheck
2016-02-22 17:42:25.820   SSMS2014SP1RegistryCheck: Checking if registry key exists: HKLM32\SOFTWARE\Microsoft\Microsoft SQL Server\120\Tools\ClientSetup
2016-02-22 17:42:25.820   SSMS2014SP1RegistryCheck: Key does not exist!
2016-02-22 17:42:25.820   SSMS2014SP1RegistryCheck: Returning false
2016-02-22 17:42:25.820   InstallSSMS2014SP1x86: Returning true
2016-02-22 17:42:25.820   -- Run entry --
2016-02-22 17:42:25.820   Run as: Current user
2016-02-22 17:42:25.820   Type: Exec
2016-02-22 17:42:25.820   Filename: C:\Program Files\Application Directory\Components\MS SQL Server\SQL Server 2014\SP1\Express\x86\SQLManagementStudio_x86_ENU.exe
2016-02-22 17:42:25.820   Parameters: /ACTION=install /QS /IACCEPTSQLSERVERLICENSETERMS /FEATURES=ADV_SSMS,BC,Conn,SDK,SNAC_SDK,Tools /UpdateEnabled=False
2016-02-22 17:43:24.280   Process exit code: 2227044361
2016-02-22 17:43:24.280   CheckSSMS2014SP1x86Installed
2016-02-22 17:43:24.280   SSMS2014SP1RegistryCheck
2016-02-22 17:43:24.280   SSMS2014SP1RegistryCheck: Checking if registry key exists: HKLM32\SOFTWARE\Microsoft\Microsoft SQL Server\120\Tools\ClientSetup
2016-02-22 17:43:24.280   SSMS2014SP1RegistryCheck: Key does not exist!
2016-02-22 17:43:24.280   SSMS2014SP1RegistryCheck: Returning false
2016-02-22 17:43:24.280   Exception message:
2016-02-22 17:43:24.280   Message box (OK):
                          Internal error: Expression error 'Runtime Error (at 57:279):

                          Problem installing SQL Server Management Studio 2014 SP1. Unable to continue with installation. Please check your computer specifications meet minimum requirements.'


texts: How to insert into temp table on Server A results from dynamic SQL open query stored proc call to remote server B via Remote Query showplan operator?

texts:
I have to call a stored procedure on server B from server A using open query via dynamic SQL, and insert the results into a temp table created on server A. I noticed in the execution plan, it's using a Remote Scan showplan operator. I know when possible the Remote Query showplan operator is more efficient, but I'm not sure if it's possible in this case? (The remote stored procedure returns about 1.5 million rows.)

This is my query on local server A:

CREATE TABLE #FinalResults (Field1 INT, Field2 VARCHAR(100))
DECLARE @DynamicSQL NVARCHAR(MAX) = ''

SELECT @DynamicSQL = @DynamicSQL +
'
SELECT Field1, Field2
FROM OPENQUERY(MyRemoteServerB, ''EXEC sp_MyRemoteStoredProcedure'')
'

INSERT INTO #FinalResults
EXEC sp_ExecuteSQL @DynamicSQL;

This is my query in my remote procedure sp_MyRemoteStoredProcedure on my remote server B:

CREATE TABLE #Results (Field1 INT, Field2 VARCHAR(100))

INSERT INTO #Results
SELECT Field1, Field2
FROM Database1.dbo.Table1
WHERE DateField >= 'Date1'
    AND DateField < 'Date2'

UNION

SELECT Field1, Field2
FROM Database2.dbo.Table1
WHERE DateField >= 'Date1'
    AND DateField < 'Date2'

SELECT Field1, Field2
FROM #Results

Relevant execution plan operators:


texts: I am not able to create this DB2 SP. What is the issue with it? Not getting it

texts:
I am not able to create this DB2 SP. What is the issue with it?

I get the following error:

  Category   Timestamp   Duration    Message     Line  Position
  Error 18/12/2013 09:43:46     0:00:00.031  - DB2 Database Error: ERROR [42601] [IBM][DB2/AIX64] SQL0104N  An unexpected token "PROTECTION" was found following "M, CUST_PREMIUM FROM".  Expected tokens may include:  "INTO".  11    271

I need to create sp accepting 2 input parameters and using same in select to return data and if no data found the raise exception saying no data found.

CREATE PROCEDURE "PROTECTION"."GET_PROSPER_PRE_APP_CUSTOMERS"
(IN "I_FSA_REF"  VARCHAR(6), IN "I_CUST_ID" VARCHAR(20))
  LANGUAGE SQL
  NOT DETERMINISTIC
  EXTERNAL ACTION
  MODIFIES SQL DATA
  CALLED ON NULL INPUT
  INHERIT SPECIAL REGISTERS
  OLD SAVEPOINT LEVEL
BEGIN
    SELECT FSA_REF, CUST_ID, CUST_DATE_INSERTED, CUST_EXPIRY, CUST_TITLE, CUST_FORENAMES, CUST_SURNAME, CUST_ADDRESS_1, CUST_ADDRESS_2, CUST_ADDRESS_3, CUST_ADDRESS_4, CUST_COUNTRY, CUST_POSTCODE, CUST_EMAIL, CUST_SEX, CUST_DOB, CUST_SUM_INSURED, CUST_TERM, CUST_PREMIUM FROM "PROTECTION"."PROSPER_PRE_APP_CUSTOMERS"
    WHERE FSA_REF = I_FSA_REF AND CUST_ID = I_CUST_ID;

    DECLARE handler_type HANDLER FOR NOT FOUND
    BEGIN
       SET MESSAGE_TEXT = 'NO DATA FOUND WITH GIVEN CONDITIONS';
       EXIT;
    END IF;

END;


texts: I had to run dbcc checkdb repair_allow_data_loss with all_errormsgs and it's been runing over 27 hours. DB size is over 15gb and the server (windows 2008) has 32gb memory. I did run this on SQL 2005 and as I said it's been going over 27 hours. Is this normal? If not, can I terminate it without causing any problem on the DB? I did take backup before I run it.


texts: How can I filter an extended event session by is_cache attribute?

texts:
I created an extended event to log all successful logins into my prod server. As you can guess there are hundreds of them coming in one second so I just need to filter some of those according to whether it is a cached connection or not. I couldn't create a filter on is_cached attribute of login event. I already filtered by [sqlserver].[client_app_name] attribute. When I watch Live Data, I can filter on is_cached attribute btw.

Here is the script of event:

CREATE EVENT SESSION deneme ON SERVER
ADD EVENT sqlserver.login(    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.username)
    WHERE ([sqlserver].[client_app_name]<>N'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')
ADD TARGET package0.event_file(SET filename=N'D:\_VTY\LastLoginAudits\audit_logon.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

I tried adding a predicate in where clause like this but it didn't work:

WHERE ([sqlserver].[client_app_name]<>N'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')
    AND [sqlserver].[is_dac]<>N'True')

Thanks in advance.


texts: Small startup: Cassandra + Hadoop or ElasticSearch

texts:
To give some context, I am from a small start up (4 team members) and we work in the education space.

We've developed an e-learning platform that several schools are already using. We currently have around 20k page requests to the platform coming in a day.

We want to start gathering every bit of data we can about our users so that we can start doing some data analysis and learning from our data.

I've spent the last couple of weeks learning up about Cassandra as I think it will be a great solution for gathering all this user data and being able to read it quickly. I've also learned that to do things like SUM and AVG queries, I'll have to use something like Hadoop to be able to run mapreduce operations on the data as Cassandra doesn't allow for this sort of querying.

Our current MySQL data base is VERY small (150mb or so). With that in mind, is it worth going down the Cassandra road yet?

I'm beginning to wonder if I'm looking too far ahead, so far as scalability is concerned.
An option I have been considering is to simplify things and begin with ElasticSearch, storing all our user event data and allowing us to easily search it without having to use something like Hadoop.

The main question: Is Cassandra going to be suitable for us yet at such a small scale? Or should we consider a simpler noSQL store like ElasticSearch?


texts: What will be the initial size of my tempdb data file(s) after restart?

texts: sys.database_files and sys.sysfiles report on size, but that's just the current size of the file. Is there a way to find out what the initial size of the tempdb datafiles will be after it's recreated during a system restart?


texts: Adding a foreign key constraint without losing the current data

texts:
I have one table called users and another table called teachers (the users table has data in it).

I want to add a column to teachers with a foreign key constraint making a reference to a column named id in the users table.

How can I do that without losing the rows contained in the users table?


texts: move last three characters of column to a new column

texts:
I have a column ( cases ) that contains 14 characters xxxxxxxxxx-xxx
I need to remove the "-" and move the last three characters to column ( subcase )

is there a easy way to do this as there are 1000's of entries


texts: Field = Parameter OR Parameter IS NULL Pattern

texts:
I am aware of the parameter sniffing issues associated with stored procedures written with a predicate like the following:

CREATE PROCEDURE [dbo].[Get] @Parameter INT = NULL AS BEGIN;
    SELECT [Field] FROM [dbo].[Table]
    WHERE [Field] = @Parameter
    OR @Parameter IS NULL;
END;

Depending on the value of the parameter, Scalar or NULL on first execution, a plan is cached that will likely be sub optimal for the opposite value.

Assuming [Field] is scalar, and the clustering index on a table. What are the pros and cons to the following approaches to writing a stored procedure(s) to support the query:

Conditioned selects in same stored procedure

CREATE PROCEDURE [dbo].[Get] @Parameter INT = NULL AS BEGIN;
    IF(@Parameter IS NOT NULL) BEGIN;
        SELECT [Field]
        FROM [dbo].[Table]
        WHERE [Field] = @Parameter;
    END;
    ELSE BEGIN;
        SELECT [Field]
        FROM [dbo].[Table];
    END;
END;

Dynamic SQL within stored procedure

CREATE PROCEDURE [dbo].[Get] @Parameter INT = NULL AS BEGIN;
    DECLARE @sql NVARCHAR(MAX) = N'';
    SET @sql += N'SELECT [Field]'
    SET @sql += N'FROM [dbo].[Table]';

    IF(@Parameter IS NOT NULL) BEGIN;
        @sql += N'WHERE [Field] = @Parameter';
    END;

    SET @sql += N';';

    EXEC sp_executesql @sql N'@Parameter INT', @Parameter;
END;

Separate stored procedures

CREATE PROCEDURE [dbo].[Get] @Parameter INT = NULL AS BEGIN;
    SELECT [Field]
    FROM [dbo].[Table]
    WHERE [Field] = @Parameter;
END;

CREATE PROCEDURE [dbo].[GetAll] AS BEGIN;
    SELECT [Field]
    FROM [dbo].[Table];
END;


texts:
I have following MERGE statement. Table table_A contains list of ID (cli_id) and column fb_flag. The query inside update the column fb_flag in table_A when cli_id is matching. Which works.
However when I want insert something else when the IDs are not matching I have difficulties.
See my two attempts in comments.

MERGE INTO tab_A chi
USING (SELECT    cli_id,
                 'TRUE' AS free_book_b
      FROM       tab_freebook d) fb
ON (chi.cli_id = fb.cli_id)
WHEN MATCHED THEN
  UPDATE SET chi.fb_flag = fb.free_book_b;
--first attempt
/* WHEN NOT MATCHED THEN
     UPDATE SET chi.fb_flag = 'FALSE'; */
--second attempt
/* WHEN NOT MATCHED THEN
     INSERT (chi.cli_id)
     VALUES ('FALSE')
     WHERE chi.cli_id IS NULL; */

Thank you for any advice.

EDIT:
Expected result:

tabA

| cli_id |   fb_flag   |
------------------------
|   1    |     NULL    |
|   2    |     NULL    |
|   3    |     NULL    |
|   4    |     NULL    |

tab_freebook

| cli_id | free_book_b |
------------------------
|   23   |     TRUE    |
|   2    |     TRUE    |
|   3    |     TRUE    |
|   6    |     TRUE    |

What I need have in tabA

| cli_id |   fb_flag   |
------------------------
|   1    |     FALSE   |
|   2    |     TRUE    |
|   3    |     TRUE    |
|   4    |     FALSE   |

I have found temporary solution - when I am creating tabA, I fill it with 'FALSE' and then the merge update only the columns where the condition is met.
Which gives me expected result, but it's not really nice and clean as with just merge.


texts: Unattended SQL install on a remote machine

texts:
My product installation does an unattended install of SQL Server (SQL Server Express by default). I use command line arguments rather than a configuration file, so I'm quite familiar with this process.

This unattended install occurs on the same machine that my install media is running on. However I need to look at installing the SQL Server instance on a separate machine to what the install media is running on. The target machine will be on the same network.

Is it possible to do this unattended? If so, is it simply extra command line arguments or is it a different process (i.e. a PowerShell script)?


texts: When should I use a unique constraint instead of a unique index?

texts:
When I want a column to have distinct values, I can either use a constraint

create table t1(
id int primary key,
code varchar(10) unique NULL
);
go

or I can use a unique index

create table t2(
id int primary key,
code varchar(10) NULL
);
go

create unique index I_t2 on t2(code);

Columns with unique constraints seem to be good candidates for unique indexes.

Are there any known reasons to use unique constraints and not to use unique indexes instead?


texts: In Oracle 12c trying to create table with columns greater than 4000

texts:
New to Oracle and SQL.
I have already followed normal procedure to allow for this size:

startup upgrade
alter system set MAX_STRING_SIZE ='EXTENDED' scope=spfile;
@%ORACLE_HOME%\RDBMS\ADMIN\utl32k.sql
@%ORACLE_HOME%\RDBMS\ADMIN\utlrp.sql
shutdown immediate
startup;

No error messages during sql scripts or startup.
Tried to create table column in user but still getting error below:

SQL> CONN oracle
<password>
SQL> CREATE TABLE UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000));
CREATE TABLE UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000))
                                                *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> SHOW PARAMETER MAX_STRING_SIZE

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
max_string_size              string     EXTENDED

Please assist me in finding my error.

@Balazs:  I have attempted your commands but receive errors:

$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 18 08:21:34 2019

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

SQL> show parameter max_string_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED
SQL> create table UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000));
create table UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000))
                                                *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> CONN sys / as sysdba
Enter password:
Connected.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP UPGRADE;
ORACLE instance started.

Total System Global Area 5033164800 bytes
Fixed Size                  8803120 bytes
Variable Size            1040190672 bytes
Database Buffers         3976200192 bytes
Redo Buffers                7970816 bytes
Database mounted.
Database opened.
SQL> ALTER SYSTEM SET max_string_size=extended;
ALTER SYSTEM SET max_string_size=extended
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified

SQL>

Not sure where I have gone wrong.


texts: How to list all grants per user/role on PostgreSQL

texts:
I've run these statements on Postgres CLI (I'm using PostgreSQL v13.1):
CREATE ROLE blog_user;
GRANT blog_user TO current_user;

And I created a function
CREATE FUNCTION SIGNUP(username TEXT, email TEXT, password TEXT)
RETURNS jwt_token AS
$$
DECLARE
  token_information jwt_token;
BEGIN
....
END;
$$ LANGUAGE PLPGSQL VOLATILE SECURITY DEFINER;

Finally I granted a permission:
GRANT EXECUTE ON FUNCTION SIGNUP(username TEXT, email TEXT, password TEXT) TO anonymous;

I wish to list all grants per user/role in my schema/database. \du and \du+ show basic information, which does not contain info about the grant (execute on function) made recently.


texts:
I want to know how to verify disk hits by a select query in PostgreSQL 9.4 on CentOS 6.5. Also please let me know how far enabling the log_statement_stats in the postgresql.conf will help me.

I need to understand the 'filesystem blocks in/out' value in the logs when I switch the above parameter to on.

For example, please check the log snippet:

2016-02-03 14:12:45 PST LOG:  statement: select * from test_stats_collect ;
2016-02-03 14:12:45 PST LOG:  QUERY STATISTICS
2016-02-03 14:12:45 PST DETAIL:  ! system usage stats:
        !       0.069489 elapsed 0.027995 user 0.002999 system sec
        !       [0.131979 user 0.146977 sys total]
        !       0/0 [3280/26440] filesystem blocks in/out
        !       0/5 [19/4866] page faults/reclaims, 0 [0] swaps
        !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
        !       20/116 [125/174] voluntary/involuntary context switches


texts: Why is sql_text "select @@trancount" in open but sleeping transaction?

texts:
Using SQL Server Management Studio, open a query window and execute

BEGIN TRAN

In another window execute

select [text],
from sys.sysprocesses
cross apply sys.dm_exec_sql_text(sql_handle)
where status = 'sleeping'
and open_tran = 1

I see select @@trancount

What's up with this? Who's counting transactions? Is this a Management Studio thing?


texts: App Engine Standard connection to Cloud SQL Latency Randomly

texts:
I have a pretty "basic" app that we designed that was originally on a local plesk server and we migrated to GAE/GSQL/GCS. app engine, mysql, cloud storage.

Here's some background info:

App is PHP based, and runs great on the local server. When we migrate to the cloud we notice this random yet extremely latency that happens. It's so bad that the app times out and gives a SPDY timeout error. We utilize cloudflare for SPDY assistance so we started there and they said it's the the server. Then we went to google. We've been going back and forth back and forth and I am looking for other avenues of help.

I am running an app on a F2 standard GAE instance and a G1-small CloudSQL instance (gen 2). All same region/zone. There is also a failover sql instance.

There is really no pattern to it but users on the app notice a bad timeout very frequently and it dies after 60 seconds. (which points to a PHP timeout right? We checked the code and it runs fine on the local server)

I dont have a whole lot of traffic on this app yet (maybe a few users a day) so i dont know if it's traffic load. Here's some basic stats for you:

https://imgur.com/a/U1tk5ak

Some Google Engineers said our app has trouble scaling (QPS never will get about 1)

https://imgur.com/a/XWh44bm

And asked if we are threading. We are not. We do not use memcache yet either.

I also see a ton of these:

https://imgur.com/a/eVSNqc3

Which looks like this bug: https://github.com/GoogleCloudPlatform/cloudsql-proxy/issues/126

But I am unsure if this is all related.

We've tried going through Google's tech support, they said we have "manual locks" but our dev team doesn't agree nor know what this really means. Again, the same framework of the app (session handling etc) code is used in many apps with a ton of users on it (non GAE, they're on compute on AWS) so this is our first venture to GAE.

We connect using standard MySQL connection parameters and use the same framework in a lot of applications and it runs fine. We use the required proxy to connect to CloudSQL.

The speed and constant lag shouldn't be there. We don't know what this issue could be. My questions are:

1) Do you see any issues here? All database logs are above and summaries

2) Can you help me understand what may be wrong here?

Thank you!


texts:
I am trying to pull all data where a sale date is >= to a certain datetime.  When I execute my query I get an error of:

  Msg 241, Level 16, State 1, Line 15
  Conversion failed when converting date and/or time from character string.

What should I alter to get his query operational?
Below is sample DDL

Declare @datetime datetime = GetDate(), @sql varchar(max)

Create Table #dinonuggets
(
  saledate datetime
  ,item varchar(500)
)

Insert Into #dinonuggets Values
('2016-03-30 12:02:00.000','chair')
,('2016-03-04 08:12:48.000','stove')
,('2016-03-07 09:37:39.000','washer')

Set @sql = 'Select * FROM #dinonuggets WHERE saledate >= '+@datetime+' '
Print @sql
Exec (@sql)

My expected output would be
03/30/2016 12:02:00.000 chair
 as that is the only data that meets the criteria


texts: How can I see who has accessed the database, viewed the tables or executed SELECT, for SQL Server 2008 R2?


texts:
I have a 19GB archive table that is working as expected on one server. I copied the following 2 files to another server. The table is not working on the new server.

r_mi_raw_20120712.ARZ
r_mi_raw_20120712.frm

The first server version is 5.5.22-log MySQL Community Server (GPL) by Remi
The second server version is: 5.5.20 MySQL Community Server (GPL)

What can be the reason?
When I used mysqlshow db_name it would list the table, but when I use --count I do not get count. Instead I get "Opening tables" state for command Field list.

update:

mysql seems to be writing to the ARN file...

r_mi_raw_20120712.ARN

Is this one time optimization? How do I improve the speed of ARN file creation?


texts: Is it a good idea to have a read connection point to both write database and read replica?

texts:
In my laravel app I have a write connection to a write database, and a read connection to a dns server that is connected to a single read replica (as instructed here) In the past the dns server was connected to two read replicas but I removed one to cut costs.

It happened that the only read replica I had had a physical failure and had to restart, in that time my whole system went down. I was wondering if it's a good idea to make read connection point to the dns server, which in turn points to both the read replica that I have and the write database as depicted here:


texts: SQL Server 2017 - In Memory and Native Compilation

texts:
I've been using these features experimentally, but have, to date, encountered 3 big issues.

Runtime errors have put the database into a suspect state.  This has happened twice.
Complicated arithmetic expressions occasionally give different results to normal T-SQL when compiled natively.
SSDT fails to deploy changes to databases which in-memory and natively compiled  procedures are affected by the changes.

Although I've achieved some impressive runtime savings by moving parts of the workload to in-memory, there's no way I can consider it ready for production use, while point 1 remains an issue. 2 and 3 are slightly less important, but are in themselves sufficient to block in-memory in my organisation at preent.


texts: When I convert the linked tables to local tables I got this error?

texts:
Referential integrity cannot be enforced between linked tables and local tables.

Enforcement of referential integrity will be dropped on all relationships between tables converted to local and tables that remain linked, then I get the options ok cancel and help

I clicked ok and it does nothing.

I am trying to make them all Local so I can edit them without the main database that everyone else is using to be changed in any way.


texts: For a boolean settings table, should I toggle a flag, or insert/delete?

texts:
I have a settings table.

SETTINGS
--------
settingId int
settingName varchar(20)

Each user can choose to apply the settings, or not.  The interface allows them to quickly toggle the settings on or off.  The setting is by default off.

I'm looking for best practice on the USER_SETTINGS table.

Should it be designed such that only active settings are included in the USER_SETTINGS table?  This would involve a lot of inserts and deletes on the table.

USER_SETTINGS (only active settings)
------------------------------------
settingId int
userId int

Or, should it be designed with an active flag?  This would involve a single insert for each setting, followed by several updates.

USER_SETTINGS (settings enforced by a flag)
-------------------------------------------
settingId int
userId int
active bit

Setting history is not really important to me, so I wouldn't need to see if the active flag was ever used.

Is it best to use a smaller table and insert, delete, insert, delete, etc., or is it best to add that extra bit, and insert, update, update, update?

I'm running SQL Server 2005.


texts: Efficient way to compute unique values using a PostgreSQL GIN index

texts:
Say I have a JSONB column, with a GIN index on it.

I can use jsonb_object_keys() to fetch the keys for each row. But is there an efficient way of getting the unique set of keys, across all rows?

Surely the GIN index must have this information already, right?

NB: I know the "classic" solution: split the single column into a many-to-many relationship.


texts: I'm new to DBM and I'm trying to learn it online. Honestly, I need some help with this. I don't know if I'm doing this right, and any help will be appreciated. This is my answer for i.


texts:
I'm trying to open and replace a string in a text file with the following procedure:

BEGIN
v_file_name := 'text_file.txt';
v_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'W');
v_id_nextval := v_id_nextval + 1;
BEGIN
LOOP
    utl_file.get_line(v_filehandle, v_new_line);
    v_new_line := replace(v_new_line, 30690, v_id_nextval);
    EXIT WHEN v_new_line IS NULL;
  END LOOP;
END;
utl_file.fclose(v_filehandle);

The problem is that after this procedure runs, the text file becomes empty.
If I replace the 'W' by 'R' in the

v_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'W');

The value of the variable is replaced correctly, but there are no changes made in the text file, because it has only read access.

Am I doing something wrong or is it not possible at all?

PS: I'm using Oracle 9i.

Regards.


texts: MySQL: Is it possible to order a query by a specific letter using ORDER BY?

texts:
What I'd like to do is order my query so that the results show every row found, but are ordered by showing results that begin with the letter 'D' first. Is this something that is possible to do with ORDER BY? I know I can use WHERE field LIKE 'D%', but I'd like all the results not just those with D.

My query currently looks as follows:

SELECT pname, pdescription, price FROM products WHERE manufacturer = 3 ORDER BY pname ASC


texts: Find tables with modified data after a specified datetime

texts:
I am performing few inserts and updates on my database using tool provided by app supplier. I want to compare the updated database and backup after running the tool.

But my database size is around 250GB, that takes long time to compare. Rather than full database compare I want to compare the tables which are modified after running the tool.

How can I get all the table names those are modified after specified date like 2015-04-14?

Can someone help me with this please?


texts: Mongodb performance: compound query vs moving to other collection

texts:
My application uses mongodb to store order information. At some point orders are sent after which I need to generate some reporting of all orders in a timerange.

To give an idea of my order document:

{
    status: [type: String, range: 6 different statuses],
    timestamp: [date],
    customer:
        {
            id: [int, range: about 50]
            other non searchable indexed data
        }
    other non searchable indexed data
}

With a single collection I would query:

{ "status": "SENT", "customer.id" : ..., "timestamp" : {$gte: ..., $lt: ...}}

With an indexes like:

{ "status" : 1, "timestamp" : -1, "customer.id" : 1}
{ "status" : 1, "timestamp" : 1, "customer.id" : 1}

I would be able to query these rows. However I found that multiple criteria in a query would slow it down.

So my question is:

Is it better (performance wise, but also best practices) to use compound indexes with compound queries or to have multiple collections for different status (like order_sent, order_new, order_finished) when the database has millions of orders.

Or in other words, how much of a performance impact does extra criteria have on a correctly index query (and are the proposed indexes viable for this situation).


texts:
I have a clustered SQL 2016 standard database. It is set up in a virtual environment with a total of 250 GB of storage. However, my developer set the initial size of the databases to be 100 GB. There are two databases (200 GB total), using 40 GB and 20 GB, respectively.

I am unable to run a backup due to the large initial database size. I have read multiple articles about how shrinking the database is against best practices including 'Stop Shrinking Your Database Files. Seriously. Now.' but I don't see any other options.

Can someone assist? My plan is to run the command DBCC SHRINKDATABASE down to a size just larger than the databases are now and then rebuild the index. I appreciate any suggestions.


texts:
i'm running a wordpress which needs some mysql optimization, i have a slow query and i would like to get rid of "Using temporary; Using filesort"

query:

EXPLAIN SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (1,3,4,5) )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date
DESC LIMIT 0, 10;

+----+-------------+-----------------------+------+--------------------------+------------------+---------+----------------+------+----------------------------------------------+
| id | select_type | table                 | type | possible_keys            | key              | key_len | ref            | rows | Extra                                        |
+----+-------------+-----------------------+------+--------------------------+------------------+---------+----------------+------+----------------------------------------------+
| 1  | SIMPLE      | wp_posts              | ref  | PRIMARY,type_status_date | type_status_date | 62      | const          | 4    | Using where; Using temporary; Using filesort |
| 1  | SIMPLE      | wp_term_relationships | ref  | PRIMARY,term_taxonomy_id | PRIMARY          | 8       | wp.wp_posts.ID | 1    | Using where; Using index                     |
+----+-------------+-----------------------+------+--------------------------+------------------+---------+----------------+------+----------------------------------------------+

i have uploaded the dump files for the 2 test tables (4 categories and 10 posts)

http://pastebin.com/6zhVGQH7

http://pastebin.com/vUnkKqtP

i have tried a lot of things but nothing worked, tried to add indexes and force them, tried with a subquery and a lot more , from what i saw the major problems are on join line and on the order by post_date, mysql can't use type_status_date index for order by because the date column is not "the leftmost column", there is already a post_date index which is not used, even if i reorder the columns in the type_status_date this in not helping

Thanks


texts: Does Query Plan cache gets cleared by itself?

texts:
I have been working on query plan stored in plan cache and every time i search for a particular query with the below sql query,

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,cp.plan_handle,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
where
st.text like '%SearchModel%'
order by ExecutionCount desc

I see different counts for the result.I am not clearing the plan cache and no other developers.
I am working on SQL Server 2016 and i have turned on Optimize for Adhoc option.The queries are generated from application using LINQ-SQL.So i hope the queries generated are adhoc.Please correct me if i am wrong.

Why is that the cached plan counts varies each time.?

Does anyone has any insight from the below screenshot regarding plan cache usage.It has more memory allocated for adhoch queries as queries in my application comes from LINQ-SQL.
I guess prepared queries are compiled queries?


texts: Drop user in redshift which has privilege on some object

texts:
I have a pretty standard problem that I can't resolve. I want to remove a user in redshift DROP USER u_A; which returns me: user "u_A" cannot be dropped because the user has a privilege on some object.

The problem is that I have no idea what kind of privilege is this and on what object.

In PostgreSQL I would just REASSIGN OWNED BY u_A TO u_B where a u_B is some other user. The problem is that redshift does not support REASSIGN. The amount of tables and schemas I have is too big (so I can't randomly try everything hoping that at some point I will remove the needed privilege.

So how can I remove that user?


texts: Create a file with variable name in power shell Power shell

texts:
I am trying to execute the below power shell script to script out the db in sql server.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "SRVBLRDBATST98\MSSQLSERVER1"

$abc=$s.databases

foreach ($tables in $abc)
{
$tables.Script() | out-File e:\pruthvi\db.txt -Append
}

The above power shell will script the out put to db.txt, but i need the output like dbname.txt for each database which will be created everytime for each databases.
any help?


texts: The CPU utilization spikes to 80+ even with just one user on Amazon RDS (MYSQL INNODB)

texts:
These are my RDS instance configs

Instance Class: db.m3.xlarge
Storage 40 GB
Engine MySQL 5.6.34
Multi AZ : No

I have a the node.js based backend hosted in EC2 instance and also have a cloud front with 10 minutes cache window which hosts one of our intense query with takes around 20-30 seconds (i know this is intense and i am optimizing this) but on the mean time with say minimal number of user say 2-3 the CPU spikes to around 85-100% and i am not able to figure out why. any suggestion and ideas are much appreciated.


texts:
I work for a school district and one of the many things the admin wants to know is how many tardies a student has for a given time frame.  I could certainly go retrieve the count from the attendance table, but wondered about summarizing the data and if that might be faster/better.  So, I've conisdered a table that looks something like:

create table stu_summary (
id int identity primary key nonclustered not null,
stu_id char(10) not null primary key references student (stu_id),
group_name varchar(50) not null,
item_name varchar(50),
value varchar(100))

which would then allow me to have data that looked like:

1, 'abc112233', 'tardy-count', '2011-F-21-99383', 2

which would signify that this record shows that student abc112233 has 2 tardies for the academic year 2011 in fall for progress period 21 and class id 99383.

Is there a better way to store this kind of info?  I'm hoping to keep the table flexible and not tie it down to the specific data columns.  Am I headed in a bad direction?  What have you done to store this kind of date span specific summaries?

Edit
There has been speculation as to the amount of data, number of tables in question here, so let me illuminate:

There are 70k rows of attendance recorded each day, 1.2m per month
Each student's tardy count will be displayed in an attendance window with a grid.  This is important because a student normally has 6 classes and so their attendance will be loaded frequently throughout the day
The attendance table has a code column in it that determines what kind of attendance it is.  The tardy code is one of those values.


texts: Multiple queries to linked server (Access db) block Sql server

texts:
On a SQL server 2012 I've created a linked server pointing to Access database:

It all works fine, unless we have more than one query to this server - queries that are not even related to the same table within Access. When such thing happens, SQL server simply waits with "SUSPENDED" status, while trying to kill the queries ends with "KILLED/ROLLBACK" status with 0% progress and either "0s" or regularly increasing "Estimated time remaining". The only way back out of that situation is the restart of the SQL server.

What can be the cause of this locking? How to resolve it?


texts: why doesn't this parameterized sql return anything while the non parameterized one does?

texts:
I have tried to use sql parameter on one of my queries and noticed when ever i do so, it just doesn't return anything! while the none parametrized one works just fine.
Why is it ?
None parametrized one :

string columnCommentsQuery = string.Format("SELECT   * FROM   ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', '{0}', 'column', default)", table[2].ToString());

    DataTable comments = new DataTable();
    SqlCommand sql = new SqlCommand(columnCommentsQuery, conn);
    conn.Open();
    SqlDataAdapter da = new SqlDataAdapter(sql);
    da.Fill(comments);
    conn.Close();

Parametrized one:

string columnCommentsQuery = "SELECT   * FROM   ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', @tb, 'column', default)";
     DataTable comments = new DataTable();
     SqlCommand sql = new SqlCommand(columnCommentsQuery, conn);
     sql.Parameters.AddWithValue("tb", "'" + table[2].ToString() + "'");
     conn.Open();
     SqlDataAdapter da = new SqlDataAdapter(sql);
     da.Fill(comments);
     conn.Close();


texts: SQL Server Configuration on Multiple Instance Server

texts:
I have a 2016 Standard edition server with two instances. The server has 8 logical processors and 40GB memory (it's a testing server on hyper-v so the spec isn't high!) and it doesn't have any existing workload to profile.

Does the advice around configuration of MAXDOP and TempDb files still stand true on a multiple instance server?

My thoughts were to split the server resources and apply the configuration based on those.

e.g. each instance would have

16GB MAX memory
MAXDOP 4
4 TempDB files


texts:
I have a requirement similar to previous discussions at:

Writing a simple bank schema: How should I keep my balances in sync with their transaction history?
Trigger in combination with transaction

I have two tables, [Account].[Balance] and [Transaction].[Amount]:

CREATE TABLE Account (
      AccountID    INT
    , Balance      MONEY
);

CREATE TABLE Transaction (
      TransactionID INT
     , AccountID    INT
    , Amount      MONEY
);

When there is an insert, update or delete against the [Transaction] table, the [Account].[Balance] should be updated based on the [Amount].

Currently I have a trigger to do this job:

ALTER TRIGGER [dbo].[TransactionChanged]
ON  [dbo].[Transaction]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
IF  EXISTS (select 1 from [Deleted]) OR EXISTS (select 1 from [Inserted])
    UPDATE [dbo].[Account]
    SET
    [Account].[Balance] = [Account].[Balance] +
        (
            Select ISNULL(Sum([Inserted].[Amount]),0)
            From [Inserted]
            Where [Account].[AccountID] = [Inserted].[AccountID]
        )
        -
        (
            Select ISNULL(Sum([Deleted].[Amount]),0)
            From [Deleted]
            Where [Account].[AccountID] = [Deleted].[AccountID]
        )
END

Although this seems to be working, I have questions:

Does the trigger follow the relational database's ACID principle? Is there any chance an insert might be committed but the trigger fail?
My IF and UPDATE statements look strange. Is there any better way to update the correct [Account] row?


texts: Insert Array of JSON-s into Postgres table

texts:
I have an array containing JSON of the following form:

[
  {
    "country": "ANY",
    "category": "poetry"
  },
  {
    "country": "ES",
    "category": "science"
  }
]

What I currently do is - iterate over the JSON-s in the Array in Python and insert them into the DB table one by one like so:

sql = 'insert into table_name (country, category) values (%s, %s)'
vars = (country, category)
app_utils.run_query_pool_commit(con_pool, sql, vars)

I'm curious if there's a more elegant way of doing such kind of bulk inserts without having to make a loop and inserting the objects one by one.

Thanks.


texts: Can indexing views in SQL Server 2005+ speed up SELECT count(*) from a View?

texts:
My initial question was How does someone determine if it is a good idea to index a view?.

Update: My underlying assumption was that indexing views speeds them up. This is apparently not sensible, since views just execute queries, as the comments below point out.

What I'm doing right now is very similar to what this guy here is doing: I'm finding views that are selecting about 2K rows out of 2 million rows, take far too long to fetch rows, and I thought that a schema bound indexed view might help.

The operation that I was trying to optimize is:

select * from vwSample where TYPE_CODE = 'X';

TYPE_CODE is a 10-character string, there are about 2K unique TYPE_CODE values in vwSample, and there are about 2-5 million rows in vwSample, and there are between 2K and 10K rows for each unique value of TYPE_CODE. I didn't post my execution plan because I was trying to ask a "general practice" question.

Update: it looks like my question is wrong-headed, I should just write and optimize queries, and ignore the fact that there's a view, maybe even just write it as a regular query and ignore this view. And there are no "turbo buttons" available in views, that can make my view faster, by magic as the accepted answer suggests. My question appears to boil down to "I'm a SQL Server n00b and I know nothing about optimizing views, how do I speed up select * from vwSomething where KEY=X where vwSomething is a view?".


texts:
I have very simple function:

CREATE OR REPLACE FUNCTION myscheme.get_last_timecode(ptable_ids integer[], ptimecode bigint DEFAULT NULL::bigint)
  RETURNS bigint AS
$BODY$
  SELECT MAX(timecode) AS timecode
  FROM myscheme.event
  WHERE table_id = ANY($1) AND ($2 IS NULL OR timecode <= $2);
$BODY$
  LANGUAGE sql STABLE
  COST 100;

Count of rows:

SELECT COUNT(*) FROM myscheme.event WHERE table_id = 1; -- 120
SELECT COUNT(*) FROM myscheme.event WHERE table_id = 2; -- 18
SELECT COUNT(*) FROM myscheme.event WHERE table_id = 3; -- 839795

The results are quite unexpected:

EXPLAIN ANALYZE SELECT myscheme.get_last_timecode(ARRAY[1], NULL) -- Total runtime: 212.552 ms
EXPLAIN ANALYZE SELECT myscheme.get_last_timecode(ARRAY[2], NULL) -- Total runtime: 213.713 ms
EXPLAIN ANALYZE SELECT myscheme.get_last_timecode(ARRAY[3], NULL) -- Total runtime: 0.186 ms (the fastest!)

When I use plain query, execution time is normal:

EXPLAIN ANALYZE SELECT MAX(timecode) AS timecode FROM myscheme.event
  WHERE table_id = ANY(ARRAY[1]) AND (NULL IS NULL OR timecode <= NULL);

Aggregate  (cost=10.18..10.19 rows=1 width=8) (actual time=0.079..0.079 rows=1 loops=1)
  ->  Index Scan using event_table_id_index on event  (cost=0.42..10.05 rows=51 width=8) (actual time=0.013..0.067 rows=120 loops=1)
        Index Cond: (table_id = ANY ('{1}'::integer[]))
Total runtime: 0.101 ms

but it uses another execution plan for table_id = 3:

EXPLAIN ANALYZE SELECT MAX(timecode) AS timecode FROM poker.event
  WHERE table_id = ANY(ARRAY[3]) AND (NULL IS NULL OR timecode <= NULL);

Result  (cost=0.47..0.48 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.42..0.47 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1)
          ->  Index Scan Backward using event_timecode_key on event  (cost=0.42..35873.11 rows=845301 width=8) (actual time=0.015..0.015 rows=1 loops=1)
                Index Cond: (timecode IS NOT NULL)
                Filter: (table_id = ANY ('{3}'::integer[]))
Total runtime: 0.038 ms

Can anybody explain me how to create a function (or index) whose execution time will not depend on the amount of data?

SELECT version();
PostgreSQL 9.3.14 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

And the table definition:

CREATE TABLE myscheme.event
(
  id bigserial NOT NULL,
  table_id integer,
  deal_id bigint,
  type_id integer NOT NULL,
  timecode bigserial NOT NULL,
  created timestamp with time zone NOT NULL DEFAULT now(),
  parent_id bigint,
  prev_id bigint,
  CONSTRAINT event_pkey PRIMARY KEY (id),
  CONSTRAINT event_deal_id_fkey FOREIGN KEY (deal_id)
      REFERENCES myscheme.deal (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT event_parent_id_fkey FOREIGN KEY (parent_id)
      REFERENCES myscheme.event (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT event_prev_id_fkey FOREIGN KEY (prev_id)
      REFERENCES myscheme.event (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT event_table_id_fkey FOREIGN KEY (table_id)
      REFERENCES myscheme.tables (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT event_type_id_fkey FOREIGN KEY (type_id)
      REFERENCES myscheme.event_type (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT event_timecode_key UNIQUE (timecode)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX event_deal_id_index ON myscheme.event USING btree (deal_id);
CREATE INDEX event_table_id_index ON myscheme.event  USING btree (table_id);
CREATE INDEX event_type_id_index ON myscheme.event USING btree (type_id);


texts: SQL Server: How to export database with linked servers

texts:
I'm a web developer and I want to export a database to  work on it in my local machine.

I'm using generate script with data. the script is generated but without the linked servers used by some Views.

Is their a way to generate a full script with linked servers or do I have to create it manually with sp_addlinkedserver?


texts: Azure SQL Database "Login failed for user" contained users.

texts:
I'm having a similar issue to this question (https://dba.stackexchange.com/posts/123094/edit)! I have followed the following article: https://azure.microsoft.com/en-gb/documentation/articles/sql-database-manage-logins/ and just cannot get it to fully work.

-- first, connect to the master database

CREATE LOGIN login1 WITH password='password';

-- Establish a new connection to the database

CREATE USER login1user FROM LOGIN login1;

GRANT CONNECT TO login1user

EXEC sp_addrolemember 'db_datareader', 'login1user ';

EXEC sp_addrolemember 'db_datawriter', 'login1user ';

EXEC sp_addrolemember 'db_ddladmin', 'login1user ';

The issue is, I cannot connect to my database with login1user. I have to use the login1 details, which isn't really what I was expecting to happen. I would be happy having the names the same, except for bullet point three in the remarks section of this article (https://msdn.microsoft.com/library/ff929188.aspx), suggests that this isn't best practise.

Any help appreciated!


texts: Aggregate data from two tables with different field names

texts:
I have two tables that each have date and amount fields.  This is for a donation system so one is pledges and the other is donations.  Here are the structure of relevant tables and fields:

pledges

pledge_date
amount_pledged

donations

processed_date
amount_donated

I would like to end up with results that sum pledges and donations by month.  Resulting in:

Year    Month    Pledged    Donated
2015    01       120        80
2015    02       90         100
2015    05       0          10
2015    06       120        0

Obviously it's easy enough to get the data summed and grouped from the tables individually, but is there a way to get it all in one result set?  Here is the sample SQL to get the data just from the pledges table for example:

SELECT year(pledge_date) as year, month(pledge_date) as month, sum(amount_pledged) as pledged
FROM pledges
GROUP by year(pledge_date), month(pledge_date)
ORDER by pledge_date ASC

I am trying to accomplish this in MySQL.

Thanks to anyone for your help or clues on how to structure this!


texts: How can I get the whole execution log of stored procedure?

texts: I know that how many times it was executed from execution_count in sys.dm_exec_procedure_stats.But how these values are Calculated? Is there any way to know how these values get calculated? I want whole log when the stored procedure was first time executed to till last_execution_time logs.


texts: Fixing MyISAM database without losing data

texts:
My old server was crashed but I was able to recover the databases from MySQL through testdrive.

I'm trying to dump a database using mysqldump but it gives error for certain tables are crashed and should repaired.

I tried myisamchk --safe-recover /var/lib/mysql/database/table.MYI
And also myisamchk -r -q /var/lib/mysql/database/table.MYI

The tables fix themselves but unfortunately they delete that data from MYD files.

Is there a way to fix the tables without losing data from MYD or is it possible to convert the MYD into human readable format like a txt file?


texts:
I am trying to find the first occurrence of "12345" in the decimal expansion of $\pi$ by using MySQL. Possibly by using the Instr function?

But I am stuck because it only displays the first 7 fractional digits.


texts: SQL server permissions during installation

texts:
I'm trying to find out when a SQL server installation grants permissions on the OS.

I know that the following permissions are giving during setup msdn article about permissions.

However what I don't know is how SQL server will react if the permissions are there, but granted through a group. My problem is that the OS group policies do not allow for several of the required permissions to be granted. And we are going to add the users to a group, that is then included in the group policy.

Will SQL server correctly detect that the required permissions are present, and skip adding the users individually? Or will the installation end on error because it can not grant the required privileges?

We're planning to test this soon, however due to time constraints I was hoping some-one here has experience with this issue, and can give me an answer.

Update: I'm quickly adding some information in the hopes that someone can explain the observed behavior to me...

We are able to install SQL server, despite group policies preventing nearly all the permissions mentioned in the before linked MSDN article. However if we then try and run an SSIS package, or certain server wide jobs, we receive the error that SQL Server lacks the required privileges.

Why was SQL Server able to install, without error even, despite not being able to add the sql server agent and service users to the User Right policies it needs according to the MSDN documentation?

Currently SQL Server is running, despite the user not having local permissions, and not having the Log on as a service permissions... Am I wrong in my understanding of why these permissions are required?


texts:
I have new server and i installed oracle 10g same like production. and I have databump full backup using sys user. I need to restore on the new server.
I used the following command while database is not mounted but asks for directory parameter.

Code:

impdp full=y direcotry=/home/oracle/rmanscripts/dumbbackup dumpfile=sysfull.dmp logfile=full_db.log.

please share me the steps to restore the database to a new server!


texts: How to login with Windows Authentication mode after domain name changes in SQL Server?

texts:
I am using SQL Servre 2008 R2. I recently changed my domain name and after that when I try to login to SSMS using Windows authentication, I get an error

  Login Failed For User UserName
  Microsoft Sql Server Error : 18456.

How to resolve it? Can somebody please help to get login in SSMS using Windows authentication mode again?

Thanks in advance


texts: How to use Italian locale for Postgres messages

texts:
On Oracle I have set:

export NLS_LANG=Italian_ITALY.UTF8

.. on Unix shell and now I get status and error message in Italian.

How to set in Postgres shell (psql)?
I have set:

SET lc_messages TO 'it_IT.UTF-8';

But it is all in English:

prova
video-# ;
ERROR:  syntax error at or near "prova"
LINE 1: prova
        ^
video=# select count(*) from generale ;
 count
-------
  2170
(1 row)


texts:
There is a DB {MySql} with 5 tables A, BIG, C, D, E. Their grow factor is about 1/100/1/1/1.
The table BIG has Insert/Read/Update request ratio about 1/10/2. The Inserts and Updates 'cannot fail'.

The table 'BIG' has data that:

they are critical in the same day in which they are created (in the ACID point of view, the AC is very important), after 2 day its criticality gets smaller and smaller.
they provide the basis for statistical information located in some othe tables (F, G,...). There are some "data-pumps" that reads the data from BIG and write it on F, G. The data pumps reads about 100 rows from BIG and write about 1 row on F, 1 on G etc. After that operation the rows on BIG can be removed.

More Numbers: For the table BIG i expect that: About +2k rows per day in the peakday, about +0.5k rows per day as average. The growing is cyclical: (i.e. mon=+0.5,...,wed=+0.5,sat=+1k,sun=+2k, mon=+0.5,...) and for this reason i would activate the clean of the data once per week (i.e. on mondays)

Description of Data: they are basically user requests that needs to be served 'live' (max 1 hour). Basically there is no need to store those served requests after they are marked as consumed; I just need to do some stats on that (maybe after some day, but no hurry on that).

Deploy information: The deploy is on Heroku and i would use MySql (said to be good at reads) or Postgres (said to be good at updates?) any advice on that too?

What would be a good option in order to effectively manage the scalability of the DB? Is the data pump a good solution?

I was thinking an in memory table BIG but it is said to provide a good Read ratio (like it would be a cache), what about the Inserts and Updates?
Are there any other options ?


texts: Oracle sql plan for a select with procedure in terms section

texts:
Is there any difference in optimizing a plan to execute any sql request in case of using a procedure from a package which checks to display row or not and in case of constructing a plain sql query without procedures ?

I mean, should be any difference in sql plans ?
And how it might affect the performance of execution.

for instance

select * from atbl t where checkme(t.fld) = 1;

or

select * from atbl t where t.fld = 'OK'

Thank you in advance.


texts:
I must be setting up the And/OR portion of my query incorrectly.  In the sample dataset below, after filtering out the criteria in my Where clause only FR ni23 should be returned, however my query returns

resonance   userid

Del         er12

NULL        em23

Samit       ra23

            em23

FR          ni23

This is the syntax I attempted, but is not accurate, where does the problem lie, and what should I change to fix?

Declare @T1 Table (resonance varchar(100), userid varchar(10))

Insert Into @T1 (resonance, userid) Values
('Del', 'er12'), (NULL, 'em23'), ('Samit', 'ra23'),
(NULL, 'axel'), ('', 'em23'), ('', 'red'), ('FR', 'ni23')

Select *
FROM @T1
WHERE userid LIKE '%[0-9]%'
AND (resonance <> 'DEL' OR resonance IS NULL OR resonance <> 'Samit')


texts: Find DB principals accessing a table in Azure SQL

texts:
My use case is as follows:

A table in my database is accessible by application A, B, C, etc each of whom has their own database principal. We want to start limiting the principals that can access certain table in my database, i.e. we want it such that only application A has access to said table. The idea is to relegate access to the table such that B and C calls application A instead of going to the table directly.

One naive way is to remove the principal B and C's access to the table straight away, but this will cause an application outage on B and C. A better approach is to reimplement B and C to call A first before taking away their access.

The challenge is that B and C might be accessing the table via an opaque library. We have been slowly phasing out the opaque library in favor of calling application A, but we are not 100% sure that B and C no longer accesses the table.

I have been thinking that I could check the database audit log to check which principals have been accessing that table. I have been using Azure SQL Auditing feature in my project which gives me the stats on individual statement, including its query string.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-audit-log-format

However, the query string itself might be composed of JOINs of several tables, or the string is an EXEC of a stored procedure, i.e. the granularity of the audit is on the statement level. It doesn't tell me explicitly which tables are involved in a given statement.

Is there a way to effectively capture this information that table T has been used by principals A, B, C in the past X hours?


texts: Data are not saved to database - Auto creating new datafile in oracle

texts:
I have some problem with Oracle database. Before it works like that, if INDX[Counter].dbf file reach its maximum size (8GB) Oracle automatically created new datafile INDX[Counter+1].dbf. But on server wasn't enough size to create new datafile. I increased space on server, I created new datafile manually:

ALTER TABLESPACE INDX2
ADD DATAFILE 'D:\oracle\product\10.2.0\oradata\profi\INDX18.DBF'
SIZE 4M
AUTOEXTEND ON NEXT 4M
MAXSIZE 8G;

Right now after INDX18.dbf reach 8GB Oracle wasn't create new datafile automatically and data are no longer stored. I had to add new datafile manually. How to set Oracle database to auto create new data file after reach maximum size of single datafile?

Best regards


texts: Does TRANSACTION/COMMIT/ROLLBACK only work on certain MySQL engines such as InnoDB?

texts:
Are transaction functions only relevant for particular database engines on MySQL, or are transactions supported under all MySQL engines, including MyISAM and InnoDB?

someone says here that transactions only work in some tables types


texts: ReportServerTempDB in AlwaysOn Availability group

texts:
ReportserverTempdb logfile is 6TB in size and growing and I need help. I am supporting a huge reporting server farm and the client wants reporting services to be in AlawysON Availability group(AAG) and we configured reportserver and reportserverTempDB on in three node Primary replica, synchronize Secondary replica and DR in a different location with manual synchronization.
Microsoft recommends reportservertempdb to be in simple recovery mode but if you add it to AAG that is not an option. Does anyone has reportservertempdb in AAG?
I would like to advise customer to take reporservertempDB out of AAG and we can just keep the copy of the reportservertempdb on each node and that should be fine.
Please advise.


texts: Is there a logical size limit to enum tables

texts:
I'm setting up a database to handle a FAQ page for different musical venues. The basic structure is:
id | venue_id | category | question | answer

category links to an enum table so our app can allow users to filter questions.  I am trying to decide if it makes sense to use an enum table for questions since all venues will be able to answer the same (optional) questions.
Our current design shows there could be around 200 total questions.  Obviously this isn't enough to cause any type of functional issue, but I am wondering if it is logical to have an enum table this large.  What are the things that I should consider when making this decision?
Additionally, if questions are tied to a category would it make sense to eliminate the category all together in lieu of the question enum or is better not to bog a enum table down with more columns than neccessary?


texts: Remote access to SQL Server 2008 R2 on Windows Server 2012 R2?

texts:
I have SQL Server 2008 R2 (named instance) on a Windows Server 2012 R2:

Enabled remote access to SQL Server
Enabled shared memory & TCP/IP protocols & named pipes for the SQL Server instance.
Created a firewall rule for TCP Port 1433
Created a firewall rule for UDP Port 1434
Created a firewall rule for SQLBrowser.exe
Created a firewall rule for SQLservr.exe
Ensured SQL Browser & SQL Server are running

I'm able to remotely access SQL Server (ip\instance name,port number) but can't access with (ip\instance name).

I tried with turning off firewall, but the problem was not resolved.

How can I fix it?


texts: Retrieve Data faster from a huge Oracle Table

texts:
I have a huge Oracle table ( ttdinv700 ) which contains almost 20 million rows.

I am trying to execute the below query, on the table:

   select max(ttdinv700.t$trdt)
   from ttdinv700, ttiitm001
   where ttdinv700.t$item = rpad(ttiitm001.t$item,16,' ')
   and ttdinv700.t$koor = 1
   and ttdinv700.t$kost in (2,7);

The execution is taking a lot of time. Could you please suggest a way to retrieve the data faster?

I cannot partition the table as this is a production database.