texts: Optimize query with multiple IN statements

texts:
So I have a large query which I have been trying to optimize as over time it has gotten fairly slow (~116k records taking a few seconds to get a result).  The context is a social network feed where the user is "authorized" to see the posts.  Each post has an associated tag (which can be a whole location, a classroom or a student.  The slow part in question looks like this:

WHERE (
  "post"."deletedAt" IS NULL AND (
    EXISTS (
      SELECT 1
        FROM "tags"
       WHERE "tags"."postId" = "post"."id" AND (
         "tags"."classroomId" IN (classroom ids) OR
         "tags"."locationId" IN (location ids) OR
         "tags"."studentId" IN (student ids)
       )
    )
  )
)

When I run EXPLAIN ANALYZE on the query I get this...

->  HashAggregate  (cost=4636.49..4637.35 rows=86 width=4) (actual time=23.245..23.269 rows=47 loops=1)"
    Group Key: tags_1."postId"
    ->  Seq Scan on tags tags_1  (cost=0.00..4636.22 rows=107 width=4) (actual time=1.147..23.214 rows=47 loops=1)
        Filter: (("classroomId" = 11) OR ("locationId" = 27) OR ("studentId" = 29))
        Rows Removed by Filter: 136955

It would be ideal to be able to somehow index these fields so that it would be faster to get all posts for specific users and places.  Does the community here have any recommendations for how I would be able to speed this section of query up?

I managed, so far, to make the query about 50% faster by indexing on the comments and likes but this is still a large bottleneck for me.  I have tried a few indices to help this but it hasn't really helped.  Maybe indices aren't the problem, is there a more efficient way to do multiple IN queries that I'm not aware of?

I appreciate any feedback anyone can give!  This is Postgres 9.5.2 by the way.


texts:
What is the most optimal way to do this?

In TV Show page I need to get all progress from all episodes in that TV Show
In Homepage I need to get the continue_watching
In Episode I need to get progress of the episode.
Some TV Shows have more than 100 episodes

Ex:

USER_ID | SHOW_ID | LAST_SEEN_ID | LAST_SEEN_TIME
10      | 5       | 12           | ---

USER_ID | EPISODE_ID | PROGRESS
10      | 10         | 15
10      | 11         | 20
10      | 12         | 95

OR

USER_ID | SHOW_ID | PROGRESS                     | LAST_SEEN_ID | LAST_SEEN_TIME
10      | 5       | {"10":15, "11":20, "12": 95} | 12           | ---

In PosgreSQL I can get the individual progress with:

SELECT progress->'10' as progress...

I think that the best method is the first but it will create a lot of rows and this could make DB slower?


texts: why archived log size is small but fast recovery area still has a lot free space

texts:
when recovery database I met a strange problem,my recovery script as follow:

RUN
{
  SET UNTIL TIME 'DEC 25 2013 09:00:00' ;
  RESTORE DATABASE;
  RECOVER DATABASE;
}

but rman report as follow:

...........
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/home/u01/app/oracle/oradata/ghsjdb/system01.dbf'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/27/2013 10:46:53
RMAN-06557: unable to restore archived log of thread 1 with sequence 742
RMAN-06558: archived log size of 37788 kb is bigger than available space of 6664 kb

then I check fast recovery area at once

SQL> select SPACE_USED/SPACE_LIMIT from v$recovery_file_dest;

SPACE_USED/SPACE_LIMIT
----------------------
            .200951743

SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /home/u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 20G

there all still 80% free space, is about 20g * 0.8 =16g ,why rman report no available space?


texts: MySQL and PostgreSQL has JSON data types, is it the same as NoSQL database?

texts:
MySQL and PostgreSQL introduced JSON data types, is it the same as NoSQL database?

Why?

NoSQL here is a class of data bases similar to MongoDb and DynamoDB.


texts:
I reviewed the question & answer here (mysqlimport locks all tables system wide) but I seem to still be stuck on this.
I am standing up a new master/slave pair of 5.7 servers, and have already imported the majority of data I need to perform a changeover from these older servers. After the apps are re-pointed to the new database, there will still be lingering day-of data that I will need to move over but I want to do this without locking any tables since this table is updated rather frequently. My primary goal is to make the re-point and the import of this remaining data as invisible as possible.
I have done several tests using mysqlimport to bring in the data from a .csv, but it doesn't seem that the --lock-tables=FALSE or --lock-tables=0 options are working for me. It's possible I am using the option wrong, too.
Here is what I've done thus far:

mysqlimport -u root -p dbname --fields-terminated-by=',' --fields-enclosed-by='\"' --lines-terminated-by='\n' /path/to/filename.csv This was to see what occurred without any sort of --lock-tables option as I did a test INSERT into the table, which was stopped by the mysqlimport.
Same command string as above, but with --lock-tables=0 option. Same results as #1.
Same command string as above, but with --lock-tables=FALSE option. Was able to perform a INSERT into the table, but running a SELECT to see data caused the mysqlimport to fail and report a Lock wait timeout exceeded error.

I feel like with #3 I am getting closer to what I am looking to accomplish, but it still seems like I'm either missing something or misunderstanding what can be done to bring this data in. If I have to incur some downtime to bring the data in, then that's what I have to do, but I'd much rather not.


texts: How to find the list of SQL Server jobs using PowerShell?

texts:
I need to create Windows PowerShell Scripts that will check and list down all SQL Jobs scheduled on a given database server.

Is it possible to find out the list using PowerShell Scripts? If yes, how to do it?


texts: SQL Server 2012 32 bit - will enabling locked pages allow it to use more memory?

texts:
I recently found out, that a server got a 32 bit installation of SQL Server 2012 rather than 64bit, and it is running out of memory now.

Can I have this 32 bit installation use more memory using locked pages?

I do not know much about this. I've read this http://support.microsoft.com/kb/2659143/en-us bit I am still not clear if this will help, what needs to be done etc


texts:
I am looking for some function that will return the type of given value/result like TYPE(value). Similar as for charset/collation for which there are functions CHARSET(value)//COLLATION(VALUE).

I know how to query a column type. But I am looking really for a function applicable to any value/result:

select CONCAT('hello', 5)
select IF(1, 'hello', 5) as v1, IF(0, 'hello', 5) as v2
(select 'hello' AS v) union all (select '5')
select 4.0 AS v

Does MySQL have any function to display the type?


texts: Keep default value as 0.0 for 0 or NULL for numeric column

texts:
I have a numeric column Number(28,3) in which I have to insert value as 0.0 ,
whenever I encounter 0 or NULL/blank at the time of data insertion.

I have tried below :

NVL(col_name,0.0)

but it's just replacing null values with 0


texts: Checking whether two tables have identical content in PostgreSQL

texts:
This has already been asked on Stack Overflow, but only for MySQL. I'm using PostgreSQL. Unfortunately (and surprisingly) PostgreSQL does not seem to have something like CHECKSUM table.

A PostgreSQL solution would be fine, but a generic one would be better. I found http://www.besttechtools.com/articles/article/sql-query-to-check-two-tables-have-identical-data, but I don't understand the logic used.

Background: I re-wrote some database generating code, so I need to check whether the old and new code produce identical results.


texts:
I've downloaded an unspecified SQL file with no file extension. When reading the first 10 lines of the file it shows among unreadable characters:

TAPE
Microsoft SQL ServerSPA
L
SFMB
8SSET
4Oct_2015_Latest backupDBA\TSSHTSPAD2
VOLBX
C:\DBASPAD
MSCI8
Oct_2015_LatestDBASFGI
PRIMARYSFINf
Oct_2009_DataC:\Program Files\Microsoft SQL Server 2000\MSSQL\data\Oct_2015_Latest.mdfSFINl
Oct_2009_LogC:\Program Files\Microsoft SQL Server 2000\MSSQL\data\Oct_2015_Latest_log.ldf
SPAD
MSDA
APAD
MQDA

I have done some reading on .mdf files, but cannot find out how to read this file. I am using Linux. Is there a way to read this in Linux or Windows?

The RESTORE HEADER ONLY command gave this:

Oct_2009_Data
C:\Program Files\Microsoft SQL Server 2000\MSSQL\data\Oct_2012_Latest.mdf
D
PRIMARY
1005584384
35184372080640
1
0
0
00000000-0000-0000-0000-000000000000
0
0
0
512
1
NULL
968000005802100003
4D42441E-4BCF-4351-9BAD-A03C53801BEF
0
1
NULL
NULL

Oct_2009_Log
C:\Program Files\Microsoft SQL Server 2000\MSSQL\data\Oct_2012_Latest_log.ldf
L
NULL
5477695488
35184372080640
2
0
0
00000000-0000-0000-0000-000000000000
0
0
0
512
0
NULL
0
00000000-0000-0000-0000-000000000000
0
0
NULL
NULL

And RESTORE FILEONLY gave INCOMPLETE in the first column and the rest were null.


texts:
So I have a table with tags and the videos they belong to. Both columns are primary.

..and so on

I need to make a list of the videos that have certain tags (multiple) associated with them.

So if I need all videos that have both tags 123 and 124, it would give me videos 14962 and 14963, etc.

I made the table structure assuming this would be easy, but have been staring at this for an hour and I'm still not seeing it.


texts: how columns ordering in sql server database table impact performance?

texts:
As I read about database design from logical model to physical databases, I noticed that the column ordering may need to be changed the sequence recorded in the logical data model. The DBMS is SQL Server.My question is how columns ordering in sql server database table  impact performance ?

Thank you so much for answering! I REALLY APPRECIATE IT.


texts:
We need to centralize our ability to manage the various schedules that our banking centers keep since it is a manual nightmare right now.    Do you see any obvious gaps in this?

A schedule is defined by a set of Days of the Week, open times and close times.
A logical record may look like this:

service_location    service_location_type  day_of_week  open_time  close_time
Main Street Branch  Lobby                  Monday       8am        5pm
Main Street Branch  Lobby                  Tuesday      8am        5pm
Main Street Branch  Lobby                  Wednesday    8am        12pm

I want to be able to reuse Schedules but am worried something here is a a bit off.


texts: Adding coordinates to table (forcing 1:1 association without unique identifiers on either side)

texts:
I have a table (around 60-80 million rows) in a postgresql database, that identifies census tracts that the observation belongs to (identifies an area of the US).  I want to place a random point within that polygon, and associate each observation (row) with a point.

I have written code that will create the random points, using arcGIS. (I wrote a script that will generate ~ 1kk points in <1 min)  What I cannot figure out is how to tie the coordinates back to the original data.

If I pipe the coordinates back into the sql database, then i can create a 3 column table of tract ID's, X and Y coordinates.  but if i join this table to the original data, then it'd essentially create a many to many join, which is not what i want.  I have 1:1 data, but I don't have unique identifiers on either side.

How would i force a 1:1 association?


texts: How can I manage to view if a table has been read recently in postgresql 12?

texts: How can I manage to view if a table has been read recently in postgresql 12 ?


texts: How to increase the size of query audit log?

texts:
I need to find the queries run against an Oracle instance.

On running the query:

SELECT
    first_load_time,
    sql_id,
    sql_fulltext
FROM
    v$sql
ORDER BY
    first_load_time ASC;

Only records from yesterday are displayed.

Running:

SELECT
    *
FROM
    dba_hist_sqltext;

Gives absolutely nothing.

What can I do to get sql history over a longer period of time, say 2 months?


texts: How to check if list of values in one column match list of values in another column (aggregations)

texts:
Given two results consisting of single key|value pair (you can imagine those are in CTEs), I want to join and group them by key, aggregate their values and return two different things:

a) those keys where aggregated list of values in first resultset exactly matches aggregated list of values in second resultset

b) those keys where aggregated list of values in first resultset matches second resultset independent of order

Actually I know something very close to this is string_agg, but It seems I can use this in select only and it's inefficient anyway. Is there something more efficient for this?

# resultset 1
|key  | value |
|-----|-------|
| 1   | 1     |
| 1   | 2     |
| 3   | 4     |
| 2   | 5     |
| 2   | 7     |
| 1   | 3     |

# resultset 2
|key  | value |
|-----|-------|
| 1   | 1     |
| 1   | 2     |
| 1   | 3     |
| 2   | 7     |
| 2   | 5     |
| 4   | 6     |

Desired result

a) key 1

(1,2,3 = 1,2,3)

b) key 1 and key 2

(5,7 = 7,5)


texts: CSSCAN for the character set change, database version 11.2.0.3.0

texts:
According to Document 745809.1, Installing Csscan.

While running the "Csminst.sql" we are getting below error

SQL> @$ORACLE_HOME/rdbms/admin/csminst.sql

create user csmig identified by csmig password expire account lock

*

ERROR at line 1:
ORA-28003: password verification for the specified password failed


texts: how to enable temporal database capability in mysql.?

texts:
How to enable the temporal database compatibility in mysql database?

Is there any library or a plug-in to enable the temporal upward compatibility? I want to keep the past records whenever a current record changes.


texts: Recommended procedure to remove old files after major version PostgreSQL database cluster upgrade

texts:
I (successfully) followed the upgrade procedure in  §18.6.1 of the PostgreSQL Manual to upgrade a PostgreSQL database cluster from 10.7 to 11.2 (running Fedora 29). I would like to remove the old tablepaces (some of which are user-defined tablespaces) and other legacy files to reclaim space not needed from the old cluster.

There are two approaches I’ve considered:

Connect to the old database cluster as postgres and run queries against the pg_catalog system tables to DROP tables, tablespaces, databases, etc.
Run find -L /path/to/tablespace/root -type d -user postgres -iname “PG_10_*” exec rm -rf {} \; to remove directories in the tablespace locations (note the new tablespaces share the same root directory as the old ones)

After either (1) or (2), I’d remove the old PGDATA directory.

Is there a preferred approach (including any methods not mentioned)?

Note, this is distinct from the following post: How do I uninstall the old version of Postgres on CentOS 7


texts: Table with both primary key and unique key

texts:
My system requirement defines a unique business/domain/natural key (i.e. <yyyymmdd><branch_id><running_number>, in bigint). Should I use this unique key as primary key or I should still create an extra sequential primary key? My table is operation table and not warehouse table.

Someone with SQL Server experience advice me to have both sequential primary key and a unique business key due to performance hit with clustered index at growth of data. However, I am using PostgreSQL 9.3.

Should I just make the unique business key as primary key, which simplify my backend application coding as I am using ORM (Hibernate)?
What benefit I can gain if I have both sequential primary key and a unique business key?


texts: SQL Server is caching query plans with the same query when using parameterized SQL

texts:
I'm using parametrized SQL queries in SQL Server 2012 SP1.

Refer to sys.dm_exec_cached_plans and sys.dm_exec_query_stats, it seems that the optimizer creates same plans multiple times instead reusing one already existed.

The server have lots of cached plans with the same query_hash and query_plan_hash, (exactly 4700 plans at the moment and increasing).

My questions are:

Is this situation normal?
Why optimizer create a new plan again and again which is already exists?
Is there a good way to force the optimizer reuse query plans in terms of parametrized SQL?


texts: Selecting multiple rows by given amount of volume


texts: How do I find the longest string in a row amongst various text fields?

texts: Does anyone have an easy query to find the longest string of characters in a data row from ALL alphanumeric (text, nchar, varchar, etc) type columns from ALL tables in a single SQL Server 2008 R2 database?


texts: MYSQL wont use index in "IN(SELECT ..)" sub clause

texts:
I have two tables in MYSQL with target fields set as index / primary index already. Then I ran the below sql query.

  EXPLAIN
  SELECT charge FROM ec_provider_account_option
  WHERE accountPermissionId='4'
  AND accountTypeId
      IN (select accountType from ec_provider where id = 3)

According to the result no index ref used obviously. The in clause "select accountType from ec_provider where id = 3" uses index by querying alone.

Plus, if I replace the subsquery in(select .. from ..) with in(1,2,3), it will return the same result, and using index ref.

 EXPLAIN
    SELECT charge FROM ec_provider_account_option
    WHERE accountPermissionId='4' and accountTypeId
          IN(1,2,3)

How comes that ?


texts: How to normalise a relation using functional dependencies

texts:
G = {{A,B}→{C},{B,D}→{E,F},{A,D}→{G,H},{A}→{I},{H}→{J} }.

Which is a key attribute: {A,B} or {A,B,D}?


texts: Concurrency / Transaction Concerns with PostgreSQL Copy (bulk insert)

texts:
We're building a CQRS system backed by Postgres. As part of this, we generate 'readmodels' which are precalculated projections of event sourced data. We regularly need to create new tables, query event sourced data, calculate a  projection and insert thousands of rows (mostly containing jsonb data) into new tables.

These inserts need to be done inside a transaction which also includes reads and writes to other tables.

Our initial code used standard INSERT ... ON CONFLICT UPDATE commands and achieved about 100 rows per second(). When however I changed it to use COPY ... FROM STDIN (FORMAT BINARY) the throughput improved by a factor of about 100. I'm aware that I can't do 'upserts' in the same way with the second method but was just planning to delete records before the COPY insertion (inside a transaction).

I'm thinking to use COPY flow of our application. I've received criticism however along the lines of "I doubt it will work. COPY is designed for bulk import. You'll have concurrency issues".

My gut feel is that with the MVCC and transactional DDL of Postgres, it's probably robust enough to handle something like this without breaking a sweat. I've had fear, uncertainty and doubt sown in me though!

My questions:

1/ Can I use COPY in normal transactions as I would other statements?

2/ Can mass COPY loads happen while the database is still live with normal production inserts and selectes still running against other tables?

3/ Can multiple connections concurrently perform COPY operations? (against different tables)

4/ Am I crazy for thinking to treat this like any other PG command? Is it designed to be used against live, production databases or is it more of a housekeeping tool.

We're using NPGSQL as the client library to Postgres.

Thanks!


texts: How to VACUUM FULL a single partition without stalling the parent?

texts:
I have a table called TABLE_A that's partitioned by month. Each partition is named according to it's month, TABLE_A_201810, TABLE_A_201811 etc

I've noticed that if I attempt to VACUUM an old partition

VACUUM FULL TABLE_A_201810;

I can no longer do inserts against TABLE_A (all the INSERTS timeout)

The VACUUM FULL takes a while, and I can't really afford to stop the other process that INSERTing to the newer partition.

My current solution is to detach TABLE_A_201810, do the VACUUM and then re-attach.

Is there a better solution?

UPDATE Using version 10.5, partitioning by range.
I'm doing a VACUUM FULL because thats the only way (that I know of) of returning free space to the OS. It also provides the best way of managing disk space (or in otherwords, it's the only way of checking how much free space is really available)


texts: Merge Replication from SQL 2008 R2 to SQL 2005 Express: SQL Server version (10.50) is not supported

texts:
I'm setting up a (one way) pull, RMO merge replication via web sync from my main database (SQL Server 2008 R2) and clients running SQL Server 2005 Express or 2008 Express (I must support both). I've set up the publication to support SQL 2005. When syncing to SQL 2008 Express, all is fine.

The problems arises when I use SQL 2005 Express. The error occurs on the first attempt at connecting. Sorry about the Norwegian text in the error message:

System.ApplicationException: [My custom text] --->  Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server test-db01. ---> Microsoft.SqlServer.Management.Common.ConnectionFailureException: This SQL Server version (10.50) is not supported.
   ved Microsoft.SqlServer.Management.Common.ConnectionManager.CheckServerVersion(ServerVersion version)
   ved Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
   ved Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
   --- Slutten på sporingen av intern unntaksstakk ---
   ved Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
   ved Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
   ved Microsoft.SqlServer.Management.Common.ConnectionManager.get_ServerVersion()
   ved Microsoft.SqlServer.Management.Common.ServerConnection.get_TrueName()
   ved Microsoft.SqlServer.Replication.ReplicationObject.CommonConstructor(ServerConnection connectionContext)
   ved Microsoft.SqlServer.Replication.ReplicationObject.set_ConnectionContext(ServerConnection value)
   ved Farry.FarryClients.FarryClientServices.FarryOfflineComponents.DbSync.MergeReplication.DropSubscription() i C:\Farry\Farry\FarryClients\FarryClientServices\FarryOfflineComponents\DbSync\MergeReplication.cs:linje 83
   --- Slutten på sporingen av intern unntaksstakk ---
   ved Farry.FarryClients.FarryClientServices.FarryOfflineComponents.DbSync.MergeReplication.DropSubscription() i C:\Farry\Farry\FarryClients\FarryClientServices\FarryOfflineComponents\DbSync\MergeReplication.cs:linje 129
   ved Farry.FarryClients.FarryClientServices.FarryNetClient.Offline.DbSyncManager.AddSubscription() i C:\Farry\Farry\FarryClients\FarryClientServices\FarryNetClient\Offline\DbSyncManager.cs:linje 142
   ved Farry.FarryClients.FarryClientServices.FarryNetClient.Offline.DbSyncManager.Work() i C:\Farry\Farry\FarryClients\FarryClientServices\FarryNetClient\Offline\DbSyncManager.cs:linje 181

As the publication is set up to support 2005, what must I remember (which I probably haven't) to make syncing to 2005 work?


texts: Is it safe to move GPT formatted mdf file to MBR formatted disk?

texts:
From our current SQL Server, I've copied a .mdf file to another disk (GPT to MBR).

It seems to create fault in whole disk, made MSSQLSERVICE offline.

Additionally, no error logs were unavailable while issue lasts.

However, I cannot specify if the incident was due to moving the file because incident occurred few days after moving the file.

Please help if anyone else had same symptoms or advice about what happens when moving GPT formatted .mdf file to MBR disk


texts: How do I replace strings of a table from another table column

texts:
How do I update/replace the value of the first table from the list of my second table in SQL. Sorry im not so good in using replace() of SQL especially replacing from values base from different table

First table.

ID | Value
======================
 1 | Fruits[Apple]
 2 | Fruits[Apple,Mango]
 3 | Apple[Red,Green]

Second table

Search | Replace
=========================
Apple  | Orange
Green  | Yellow


texts:
Looking for good, but quick references for underlying OS concepts related to SQLOS (Memory Access, Scheduling, etc...)?

I am researching SQLOS and how threads and scheduling are handled, but I would like to review some OS concepts and how SQLOS interacts with the environment. I am aware of the Operating Systems Concepts textbook, but I was wondering if there was a good resource available online that is a little more brief and to the point.


texts: Doing bulk load on database and monitoring the growth

texts:
I have a small doubt on how this works. We came to know last night one of our sql databases had bulk load operation done with vendor giving that data of approx 300 GB would be bulk loaded.

Before and after when i checked the space used within database using gui as well as sp spaceused i can hardly see any decrease in unused space as free space has merely went down below 10 Gb.

Also I thought may be ldf, if not mdf and ndf growths but did not find anything there. Please help me understand how this works

database is in full recovery model


texts: Mysql query with LIMIT much slower under RDS (5.6) than old 5.1 system

texts:
We inherited an old legacy system running on mysql 5.1, and moved it to RDS running 5.6.

One query is running much slower - it can take up to 20 minutes to run, where previously it took only a few seconds.

The table uses MyISAM, has about 150k records, and looks like:

Create Table: CREATE TABLE `mytable` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `showFlag` bigint(20) NOT NULL,
  `dateCreated` date NOT NULL,
  `f0` text NOT NULL,
  `first` text NOT NULL,
  `last` text NOT NULL,
  `f1` text NOT NULL,
  `f2` text NOT NULL,
  `d1` date NOT NULL,
  `d2` date NOT NULL,
  `city` text NOT NULL,
  `siteID` int(20) NOT NULL,
  `state` text NOT NULL,
  `f1` text NOT NULL,
  `f2` text NOT NULL,
  `f3` text NOT NULL,
  `f4` text NOT NULL,
  `f5` text NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `dateCreated` (`dateCreated`),
  KEY `siteID` (`siteID`),
  KEY `showFlag` (`showFlag`),
  FULLTEXT KEY `description` (`description`),
  FULLTEXT KEY `Name` (`first`,`last`)
) ENGINE=MyISAM AUTO_INCREMENT=212775 DEFAULT CHARSET=latin1

The problem queries look like:

SELECT
  showFlag,
  IF(showFlag < 0,description,SUBSTRING(description,1,700)) AS description,
  city,
  state,
  first,
  last,
  dateCreated
FROM mytable
WHERE  dateCreated >= "1969-12-31" AND dateCreated <="2014-08-06"
ORDER BY dateCreated DESC, siteID ASC, last LIMIT 100000,25

The application uses this query to enable paging through the records, and apparently bots are crawling the site pulling in all 150k records, 25 at a time.

show full processlist says that the query is spending most of its time in "Creating sort index".

Is there a simple fix for this what doesn't involve re-writing the legacy code?


texts:
I have SQL Server 2008 R2 Express edition, and I want to export the data in that database to another computer so I can import it again in that computer.

How please?

What I have tried

Right-click in the database -> Tasks -> Export -> Set server name + password + set database source.

My problem

I found that I have to set the destination, but I didn't find the destination as a file or something like that, I just found that the destination is the same database which I am extracting the data from.

Help please, thanks in advance


texts: Is a geographically distributed application with SQL Server replication a good idea?

texts:
We are currently thinking about a target architecture for a geographically distributed application.

This application tracks and schedules the movements of all material units across various production sites.

There will be two logical entities:

The first one is responsible of the operational/local management of a
specific store - there are as many instances of this entity as there
are stores
The second one is responsible of the global management (for example:
units transfers between stores) and therefore communicates with all
operational/local entities

Here is the logical diagram of the application:

The implementation of this application cannot be directly derived from the previous diagram because we have to take into account the following constraints:

Stores are located in different areas (typically a few stores per
area) and if the area is temporarily isolated from the global
network, the system should still be functional for a limited amount
of time.
The operational entity requires the global entity to work

Therefore, I thought about a new architecture where we would create identical instances of the global entity (same executables) with synchronized databases for each area:

I was wondering if anyone already used SQL Server replication to achieve the same kind of goal.

Is it the right way to proceed ? All recommendations/warnings are welcome !

Thanks a lot,
Sébastien.


texts:
We have almost 60 network guys working via a proxy server, developing on one Linux-based in-house server.

Suddenly, it is saying to everyone:

Warning: mysql_connect() [function.mysql-connect]:
Too many connections in XXXXXXXX FILE NAMES

We have cleared tmp folders as well, but after restarting we face the same issue.

What is the solution to this error?


texts: What is a good practice for storing unstructured tables as a field in mongodb

texts:
Context:

I am developing an application that requires handling tables that do not necessarily have an uniform structure (eg. different/extra columns). Those unstructured files also need to be grouped (say 5 table together makes a group, each table has a different content). And each file in the group needs to be versioned preferably in a simple and efficient way (eg. no need to copy the entire file if only a row is changed).

Current solution I thought of:

Define a schema with some standard fields that application needs, such as name of a file group, ..., timestamps, path_to_repository, hash_of_repository_content (to invalidate), status (eg. being_updated, for concurrency) }
At path_to_repository, I'll have a git repository and keep files in csv format (create a submodule for each file so that each file in a file group can be versioned independently).
In order to access those files, I'll get the repository and head will be the current version of the files. Then I can do the versioning easily, can keep tables of unlimited size without any structure and wouldnt be limited by mongodb's max file size. If there are any further information to be versioned I can simply add them to the repository.
To handle concurrency, I'll update a flag in the mongodb, so it would take care of the concurrency for me. If two applications simultaneously try to write into repository, they'd set a flag/lock first. if flag is already updated by someone else, they need to wait.

Question:

I am not sure is this is a good practice (whether I'm re-inventing the wheel or not, would it have a good performance, can this scale, if there is any problems I dont see). Any advice/experience/criticism would be great.

Probably irrelevant extra information:

this is going to be used by nodejs/mongoose and also by a python script.


texts: Why does this query take 32 sec to execute?

texts:
I keep my history records in one table and my pertinent data in another. I want to grab only the files from myfiles that currently have a given status recorded in statuses (ie, the latest status is the one I want to filter by). I'm trying a subquery which may be a mistake, but I can't really think of another way to do it because there are many statuses for a given file in myfiles.

MySQL 5.0.96, column schema on both tables involved is fewer than 15.

< 500 records in myfiles, 68k records in statuses:

SELECT myfiles.*,
(SELECT status FROM statuses WHERE statuses.itemtype=2 AND statuses.id=myfiles.id ORDER BY historyid DESC LIMIT 1) AS actualStatus
FROM myfiles
HAVING actualStatus=0

Here's a fiddle with the actual table schemas involved.
Thank you!


texts: Is it correct, order of where clause doesn't matter when it is used with join?

texts:
I have question on execution of queries in which join is used with where clauses.
I read this question on join with where conditions.

I tried options like disabling rules and checked actual query execution plan.

select table1.col1, table2.col2
from T1 table1,
T2 table2
where table1.col1 = table2.col1
and table1.col3 = 10

Actual query execution plan in above case was showing join first and then filter when rule is disabled.

But when I changed the order of where clause with where tabel1.col3 = 10 and table1.col1 = table2.col1, it was filtering out the rows before join.

Thus I think in case of rules disabled order of where clause matters but not in case of rules enabled which is default condition.

So is it correct that order doesn't matter with default behavior of SQL Server?


texts: Is it possible to support multiple snapshots / store a snapshot / restore from a snapshot to a state with another snapshot?

texts:
So, I would like to have a snapshot structure like the following:
A snapshot 1 is taken. Some code is run, then a snapshot 2 is taken.

Then some more code is run, and the database is reverted to snapshot 2. Then more code, then snapshot 2, N times.

Then, once all that is done, the database is restored to its original state, snapshot 1. Basically, I would like it to be the case that I do not need to delete existing snapshots when restoring a snapshot, but rather that snapshot 1 is "stored" in snapshot 2 somehow, and when snapshot 2 is loaded, snapshot 1 is available again for a subsequent restore.

It seems as though this should be possible, because if snapshot 1 is "frozen" immediately prior to snapshot 2 being created, then once snapshot 2 is reverted to, there should be nothing preventing snapshot 1 from working, since the database state is identical to when it was frozen and snapshot 2 was created.

Is this possible? Do I need to create a snapshot of a system table?


texts: Problem with restoring database which has a filestream

texts:
When I restored a database backup file, which uses FILESTREAM feature, everything was fine but after a while the database went to the (recovery pending) mode. I tried restarting SQL Server service and it still didn't work. I decided to delete the database and restore it again but I got this error while restoring:

  Restore of database 'Ka4' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)

  System.Data.SqlClient.SqlError: FILESTREAM feature is disabled.
  (Microsoft.SqlServer.SmoExtended)

I've reinstalled SQL Server instance but nothing has changed and it still keeps saying the same error.

According to this page I'm sure that FILESTREAM feature is enabled.

P.S. This is my SQL Server version:

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58


texts:
I have created a relation database structure below,

from this relational table i want to get the SUM of all data, below my query,

SELECT ((BASE_InvoiceLine.Price - IIf(SUM(BASE_Expense.Amount) IS NULL, BASE_PurchaseLine.Price, (BASE_PurchaseLine.Price + SUM(BASE_Expense.Amount)))) + BASE_PurchaseLine.Recycle) AS ProfitRecycle
FROM BASE_PurchaseLine INNER JOIN BASE_InvoiceLine ON BASE_PurchaseLine.PurchaseLineId = BASE_InvoiceLine.PurchaseLineId AND (BASE_PurchaseLine.IsDelete = 0) INNER JOIN BASE_Invoice ON BASE_InvoiceLine.InvoiceId = BASE_Invoice.InvoiceId AND (BASE_Invoice.IsDelete = 0) AND (BASE_InvoiceLine.IsDelete = 0) LEFT OUTER JOIN BASE_Expense ON BASE_PurchaseLine.PurchaseLineId = BASE_Expense.PurchaseLineId AND (BASE_Expense.IsDelete = 0)
GROUP BY BASE_PurchaseLine.Price, BASE_InvoiceLine.Price, BASE_PurchaseLine.Recycle

Above query display all the values only.. problem is how to get the Sum of this Query result,

Thanks


texts:
When I try to restore a database using
pg_restore -U username --no-owner --no-privileges -d mydb backupfile.dump
I get an error saying:
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;

How do I prevent this error? I looked at another similar question on the site, and tried what was suggested in the answer.
I used dropdb mydb and createdb mydb before running the pg_restore command.
In case it is relevant, I am trying to restore the database dumped from PostgreSQL 10.14 into a database in PostgreSQL 12.3.
Thank you.


texts:
I can't find any info regarding my question. This is more theoretical question.
For example, i have table keywords.

CREATE TABLE IF NOT EXISTS `keywords` (
  `kid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `language_id` int(11) NOT NULL,
  `keyword` varchar(120) NOT NULL,
  PRIMARY KEY (`kid`),
  UNIQUE KEY `custom_idx` (`language_id`,`keyword`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

My server has 1GB of free RAM memory. My table has millions of rows and size of my custom_idx is more than 1GB. Index can't fit into RAM memory. What would mysql do ? Is only part of this index will be stored in the memory ? Will this break BTREE/Clustered index performance dramatically ?

Also, if size of my index can't fit into innodb_buffer_pool_size limits, what would happen ?

Is replication is the answer to enormous InnoDB databases where indexes can't fit into RAM memory ?

I need to keep all my indexes in RAM ? What if my db size is more than 500gb?


texts: What are some best practices for using schemas in SQL Server?

texts: I understand the capabilities of SQL Server Schemas, but what are the best practices?  Sure they give another layer of security, and provide logical grouping of database objects within the database, but what is typical out there?  I often, in my experience, don't see many custom made schemas utilized.  Is this typical?  Is it the less-frequent scenario where custom schemas should be used?


texts: Was using `set datcollate='C'` a mistake?

texts:
In a pre-production environment running django 1.6 on PostgreSQL 9.1 we had a problem with database records arriving in a different order from what the application was expecting. So we ran this command as the postgres user:

update pg_database set datcollate='C' where datname='appname';

About a week later it was suddenly impossible to log into the application as a user which did exist in the database and had been unchanged for years. Attempts at looking up the user by username fails but looking up the same user by primary key worked, and the returned username was in fact correct.

That problem was fixed by reindexing the database like this:

REINDEX DATABASE appname;

Is it possible that the update command has broken the indexes? If that update command is indeed unsafe, then what is the safe way to change collation on a PostgreSQL database?

In case it is relevant, these are the queries that django generates for looking up the user by username or primary key:

SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."username" = username
SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login", "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name", "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff", "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user" WHERE "auth_user"."id" = 1


texts: MySQL GTID replication: is it possible to make DDL changes to a slave and then catch it up to a master?

texts:
Here's what I would like to do:
1. hang a slave off my production master.
2. stop replication
3. adjust some data types and create some indexes on the slave (no data mods)
4. restart replication
5. once the slave has caught up, lock the db & swap slave & master

-the goal, obv. is to have no significant downtime while making some changes that will take time (200G table getting modified)
-it should be possible, because the DDL changes I'm making have to do with shrinking data types & adding constraints - nothing that changes the actual values of data.
-since GTID replication is row-based, I don't know if replication will choke trying to insert, for example, 11 digit ids into a 5 digit id column, even if the values are in range.


texts: equivalent of oracle sql administration query in SQL Server 2008

texts:
I'm new to SQL Server administration, and I would like to get the equivalent of this Oracle 9i administration query :

select object_type,count(*) as number
from all_objects
where owner='ownerSID'
group by object_type;

Any help would be greatly appreciated :)


texts:
I needed to copy information from one row of a table to another.
I noticed I could do the following:

update address
set col1 = a2.col1,
col2 = a2.col2,
.....etc
from address a1, address a2
where a1.id = @to and a2.id = @from

However, the above SQL seems kind of ambiguous.
How do I know it's going to update row @to with data of @from and not the otherway around?

Edit: Note: while I have not done extensive testing, this query does seem to work as I try it against our development database. It simply updates the one @to row with results from the @from row.


texts: Should I separate table with a lot of columns and 3 index column?

texts:
I'm new to database stuff so I apologize if I say anything ridiculous.
I have a table with 300 columns, which stores the score of 300 questions. There is also an "id-user" columns which is the primary key, and three more columns named "weekScore", "monthScore" and "yearScore". Every time a user answer a particular question the id will found and score will added to the particular column, and the score added to weekScore, monthScore and yearScore too. As you see there is a lot of queries go to this table.
Question: I want to index three score columns (weekScore, monthScore and yearScore). is it better to separate these three columns and make another table because of the indexing?

Or I change the design to this, and index three columns (weekScore, monthScore and yearScore).


texts: MySQL Storage Engine Changed from InnoDB to MyISAM on restoring backup in lower version

texts:
I have two production instance, old production instance have MySQL version "5.1.73-community-log" and the new production instance have MySQL version "5.1.72-community"

When I take backup from the old production instance to new production instance some of the InnoDB tables changed to MyISAM tables.

I have almost same setting in both the instance the difference is I have changed "innodb_log_buffer_size" and "innodb_log_file_size" on new production instance to speedup the restore process.

Change effect:

Because of this all referential integrity were lost where the tables engine was previously InnoDB and now MyISAM.
"LOCKING" occurred frequently in "SELECT, INSERT, UPDATE" queries.

I am not able to find correct cause for this because when I check the SHOW ENGINES it shows below result which means InnoDB is enabled:

mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)


texts: Best practices for configuring the Drive/Filegroup setup for Microsoft SQL 2008

texts:
I am a software developer for a small company. I wish we had a dba but that is not on the cards at this point. I was working on migrating our 75GB SQL Server 2000 database to SQL Server 2008 R2.

The new (Windows 2008 R2) server has 64GB RAM and 8 x 72GB 10k SAS drives. I made the suggestion of configuring the 8 drives into 4 RAID 1 drives, each with a single partition and separating out the data as follows:

OS
SQL Transaction Log
Largest database tables (take up about 30GB) in separate filegroups
The remainder of the database in a separate filegroup.

Our network guy said this would not work and that using RAID 10 would work best.

I had set up a test computer to test my suggestion. I tested several scripts to test the performance of significant read and write operations. The test system would be slow the first few executions, but then scripts that normally run 30 seconds would run in less than a second.

The network guy had also had me talk with a dba friend of his who also suggested 1 large RAID 10 who said there would be I/O issues using RAID 0 and having separate filegroups on separate partition.

We have now been using the new server on RAID 10 for over a year now and run fine, but I always feel like we might be missing out on some performance using what seems like a very basic configuration.

Was my idea a bad one? Are there best practices for this sort of thing?


texts:
I have pers table and applied combine fulltext index on (prenom, nom) fields. Below are data in that table.

persID prenom      nom (pers table)
--------------------------------
 116    te          te
 117    te test     test te

Now, I tried to fetch above record using MATCH and AGAINST. Below is my query.

SELECT `Pers`.`persID`, Pers.prenom, Pers.nom
FROM `bdrplus`.`pers` AS `Pers`
LEFT JOIN `bdrplus`.`pers_detail` AS `PersDetail`
     ON ( `PersDetail`.`persID` = `Pers`.`persID` )
WHERE `Pers`.`etat` =1
AND `Pers`.`persID` !=55
AND MATCH(`Pers`.`prenom`, `Pers`.`nom`) AGAINST('te*' IN BOOLEAN MODE)

Result
------------------------
persID   prenom      nom
117     te test     test te

Now I tried using mysql LIKE(%..%) operator. Below is the query which I tried.

SELECT `Pers`.`persID`
FROM `bdrplus`.`pers` AS `Pers`
LEFT JOIN `bdrplus`.`pers_detail` AS `PersDetail`
     ON ( `PersDetail`.`persID` = `Pers`.`persID` )
WHERE `Pers`.`etat` =1
AND `Pers`.`persID` !=55
AND (Pers.prenom LIKE 'te%' OR Pers.nom LIKE 'te%')

Result
-----------------------
 persID prenom      nom (pers table)
 116    te          te
 117    te test     test te

I don't understand why I am not getting both records using MATCH AGAINST operator in fulltext index query?


texts: MySQL - Query for obtaining record when 2 columns match 2 or more values at the same time

texts:
I have this database that I got from this post that manages products and its variants:

+---------------+     +---------------+
| PRODUCTS      |-----< PRODUCT_SKUS  |
+---------------+     +---------------+
| #product_id   |     | #product_id   |
|  product_name |     | #sku_id       |
+---------------+     |  sku          |
        |             |  price        |
        |             +---------------+
        |                     |
+-------^-------+      +------^------+
| OPTIONS       |------< SKU_VALUES  |
+---------------+      +-------------+
| #product_id   |      | #product_id |
| #option_id    |      | #sku_id     |
|  option_name  |      | #option_id  |
+---------------+      |  value_id   |
        |              +------v------+
+-------^-------+             |
| OPTION_VALUES |-------------+
+---------------+
| #product_id   |
| #option_id    |
| #value_id     |
|  value_name   |
+---------------+

The problem is that I don't know how would I get the SKU at the moment that a user selects the options of the product he wants:

SKU_VALUES
==========
product_id sku_id option_id value_id
---------- ------ --------- --------
1          1      1         1        (W1SSCW; Size; Small)
1          1      2         1        (W1SSCW; Color; White)
1          2      1         1        (W1SSCB; Size; Small)
1          2      2         2        (W1SSCB; Color; Black)

Let's suppose that the user selects the product with ID 1 and the options size-small and color-black, how am I able to get the sku_id (in this case I would want value 2 from sku_id) in order to get the price that's inside the PRODUCT_SKUS table, I CANNOT write the following because it obviously doesn't make sense:

SELECT sku_id FROM SKU_VALUES
WHERE (SKU_VALUES.option_id = 1 AND SKU_VALUES.value_id = 1)
AND (SKU_VALUES.option_id = 2 AND SKU_VALUES.value_id = 2)

NOTE that it seems that I would need to append the same number of conditions (or whatever I need) as the number of options that are available from a product, in this case there are just 2 rows because the product has 2 options (size and color), but the product may have "n" options.


texts:
I want to select the max id_product and date that corresponds to this max id.
my problem is that it returns the max id_ product with a different date.

I can't use the max(date) because it will return always the max what i need is the date of this max id.

This my query :

 SELECT
   MAX(id_product),
   `date`
 FROM `products`
 WHERE `fk_stat` = '1' and `id_order` = '77185' ;

The result:

But the value that's must return:

09/07/2015 for id = 152752

Thanks for your help.


texts:
I have an Oracle db and the following tables:

raportor:
id      id_pct_lucru
58382   327342

ses_rap
id  an      status
1   2012    I
21  2013    I
41  2014    D

ch_trat
id      fk_id_raportor  FK_ID_SR
20128   58382           41

taed
cod_deseu   cant    fk_trat_id
12.01.06    137     20128
12.01.13    50      20128
12.01.06    22      20128

tid
id      den     cod_tip
3557    dep3    HS
3555    dep1    B
3556    dep2    HZC

ticd
fk_inst_trat_id     cod
3557                12.01.06
3556                12.01.13
3555                12.01.06

and the following sql:

SELECT RAP.ID_PCT_LUCRU as id_pl,
          ses_rap.an_raportare as an,
          taed.COD_DESEU as cod,
          sum(taed.cant) as cant,
          LISTAGG (TID.COD_TIP, ',') WITHIN GROUP (ORDER BY TID.COD_TIP) AS tip_op,
     FROM ch_trat cht
          INNER JOIN raportor rap
             ON cht.fk_id_raportor = rap.id
          INNER JOIN ses_rap
             ON ses_rap.id = CHT.FK_ID_SR
          INNER JOIN taed
             ON taed.FK_TRAT_ID = CHT.ID
          INNER JOIN tid
             ON tid.FK_TRAT_ID = CHT.ID
          INNER JOIN ticd
             ON TICD.FK_INST_TRAT_ID = TID.ID
             and TICD.COD=taed.COD_DESEU
    WHERE SES_RAP.STATUS = 'D'
        group by RAP.ID_PCT_LUCRU,
            SES_RAP.AN_RAPORTARE,
            CHT.ID,
            taed.COD_DESEU
     order by RAP.ID_PCT_LUCRU

and the result is:

ID_PL   AN      COD         cant    TIP_OP
327342  2014    12.01.06    318     B,B,HS,HS
327342  2014    12.01.13    50      HZC

but I want the result:

ID_PL   AN      COD         cant    TIP_OP
327342  2014    12.01.06    159     B,HS
327342  2014    12.01.13    50      HZC

and I don't know how to do it. Please HELP!


texts: How to use "BACKUP DATABASE", but the database is in a different server.?

texts:
We have 3 servers ( SQL SERVER 2008 ). Is there a way to backup a database, connected in a different server?

Example: i'm on SQL1. I want to do something like this:

BACKUP DATABASE [SQL2].DATABASENAME
     TO DISK ='BACKUP SERVER'

If there's a way to do this via T-SQL It would be great.


texts:
I was wondering whether databases such as SQL Server was writing changes to persistent medium (disk) whenever a operation is completed, but it seems not? Not even when committing a transaction it seem to do this but only commit to RAM. Correct me if I am wrong. The disk-write seem to happen on CHECKPOINT which is server-initiated and perhaps doesn't have to happen straight efter a COMMIT? In any case, my questions are:

If an update to the db is not persisted before returning OK to the client, what then will it do if the update fails in the end? How will the client know this? Can a client somehow instruct the db-server to really persist before saying "all is fine"?
Is there a good reason for not just persisting to disk directly instead of RAM in a modern server with really fast SSD-disk? Only if the performance is really critical? More so than persistence?


texts: The total number of locks exceeds the lock table size, even after increasing buffer pool size

texts:
I am trying to partition a table with 200 million records in it. This table already exists:

CREATE TABLE linkmap_ntoken (
  target_nToken varchar(200) NOT NULL,
  ntoken_hashcode int(15) NOT NULL,
  targeturl varchar(2000) NOT NULL,
  targetbreadcrumb varchar(400) NOT NULL,
  sourceurl varchar(2000) NOT NULL,
  sourcebreadcrumb varchar(400) NOT NULL
 ) ENGINE=InnoDB.

Now I am trying to create 500 partition with this statement:

ALTER TABLE linkmap_ntoken partition by HASH (ntoken_hashcode) partitions 500;

It throws exception: total number of locks exceeded the lock table size.

I read few suggestions online and changed my.cnf file. Here are the configurations:

# PER CLIENT SETTINGS #
# bit high but I got tons of ram here #
sort_buffer_size                = 2M
read_buffer_size                = 2M
binlog_cache_size               = 1M
wait_timeout                    = 31536000
interactive_timeout             = 300
max_allowed_packet              = 12M
thread_stack                    = 128K
table_cache                     = 1024
myisam_sort_buffer_size         = 1M
tmp_table_size                  = 12M
max_heap_table_size             = 12M

# LOGGING #
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/slowquery.log

# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000

# BINARY LOGGING #
log_bin                        = /var/lib/mysql/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 40G

I have restarted mysql after making these changes. Still it throws the exception. My server has 12 cores and 48GB Ram and I can dedicate this server for this partitioning process for time being. Are there any suggestions to bypass this exception (total number of locks exceeded)?

When I did show global variables, it is showing buffer pool size as 8388608. I changed innodb_buffer_pool_size under [mysqld] block and restarted. It doesn't seems to be affected. It is still 8388608.

Regarding "why partitioning", I do not have a unique column to have a primary key. Even If I add one I won't be able to query with it. I am using this table to query for an online application. I want my query to return results in less than a second.


texts: How can I add a rowversion column to a large table with minimal downtime

texts:
Using SQL Server 2008 and later, I want to add a rowversion column to a large table however when I simply

ALTER TABLE [Tablename]
ADD Rowversion [Rowversion] NOT NULL

Then the table is unavailable for updates for too long.

What strategies can I use to reduce this downtime? I'll consider anything. The simpler the better of course, but I'll consider any strategy.

My thinking is that as a last resort, I could maintain a copy staging table maintained by triggers and then sp_rename the staging table into the original table. But I'm hoping for something simpler/easier.


texts: I am moving from one server to another and I want to backup all databases + users/privileges/passwords from my MySQL Server. I found to backup a database using mysqldump, but I cannot figure out, how to backup all users and the given privileges. Is there a way to achieve this or do I have to set this newly up on the new server?


texts: SQL query with multiple many-to-one joins

texts:
I want to select a count of all surveys where the survey.property.address.city == "Garrison". I have the following tables (stripped for your convenience):

surveys                 properties             addresses
----------------        ---------------        ---------
id | property_id        id | address_id        id | city

And the failing query:

SELECT count(*)
FROM   surveys s
JOIN   properties p ON s.property_id = p.id
JOIN   addresses a ON p.address_id = a.id
WHERE  a.city = "Garrison"

The error message as follows:

ERROR:  column "Garrison" does not exist
LINE 5: WHERE addresses.city = "Garrison";


texts: Oracle 10 RAC VIP returns non-VIP address for client

texts:
I am not a DB or even Oracle expert, but I wanted to understand when and why Oracle returns a different non-VIP address to the client, assuming this is correct behaviour?

listener.ora

LISTENER_DB-A =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DB-A-VIP)(PORT = 1521)(IP = FIRST))
       # this is the VIP 10.0.0.15
    )
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.10)(PORT = 1521)(IP = FIRST))
    )
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
  )
)

Here, after connecting to the IP address 10.0.0.15, the Oracle server returns the 10.0.0.10 address to the client. This is my network sniff of the said behaviour:

Client to Oracle

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.15)(PORT=1521)))
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)
(SERVICE_NAME=my_db)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=5)(DELAY=5))))

Oracle to Client

(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=DB-A))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.10)(PORT=1521)))

I know that this behaviour is not mandatory, as I've seen Oracle server respond without said redirection. What is the reason the server is acting this way? Thanks in advance!


texts: Best way to update list of rows in oracle database

texts:
I need to update a variable list of database rows. The list of the affected ids will be generated by an application, the statement will be executed about 100 times a day (with an always different collection of ids)

The first approach to update the table was:

UPDATE mytable set mycolumn = 'X' where id in (1,2,3,4 .... 2345);

I know that this statement cannot be effectively cached in the Oracle SQL area since it does not use bind variables. And if the application needs to update very frequently, it could push other statements out of the SQL area, which would have an performance impact.

Another approach could be a series of update statements

UPDATE mytable set mycolumn = 'X' where id = 1;
UPDATE mytable set mycolumn = 'X' where id = 2;
UPDATE mytable set mycolumn = 'X' where id = 3;
UPDATE mytable set mycolumn = 'X' where id = 4;
....
UPDATE mytable set mycolumn = 'X' where id = 2345;

This approach can be cached in SQL Area as 'UPDATE mytable set mycolumn = :1 where id = :2', but it might become a problem concerning the network traffic.

So - which approach should I select?


texts: How to mix pairs of rows without JOIN (in a temporary table)?

texts:
I have a TEMPORARY table containing pairs of key/value as

id   col1      col2        type         pair
1    key1      key2        key          1
2    value1    value2      value        1
3    key3      key4        key          2
4    value3    value4      value        2
5    key5      key6        key          3
6    value5    value6      value        3

I want to mix the key/values as

pair    key_col1     key_col2      value_col1      value_col2
1       key1         key2          value1          value2
2       key3         key4          value3          value4
3       key5         key6          value5          value6

Since, it is a temporary table, I cannot use JOIN. How can I SELECT from this table to have pairs of key/value in each row?


texts: Is it possible to run Oracle 12c and AD-Server on Windows Server 2012 R2?

texts:
I have tried to install Oracle 12c Enterprise Edition on a computer also running as AD Server. Including the October 2015 patch. Operation system is Windows Server 2012 R2

A managed service account is used as user when installing the server. (and 32 bit client)

Everything looks perfect, until I reboot the computer. Then the computer starts to act as a zombie.
Usually, the oradim command does not respond at all, it just hangs.

This configuration, running both Oracle and AD server on the same computer is to save virtual machines in our vCloud test configuration.

When Oracle and AD is not installed on the same computer, it works like a charm.

Does anyone have a clue what's wrong, or if it is a known limitation?


texts: high concurency issue with read-write on mysql table

texts:
I have a mysql innoDB table (mysql 5.7, ubuntu 14.04) :

+-------------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+-------------------+-------------+------+-----+---------+----------------+
| id                | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| altitude          | smallint(6) | YES  |     | NULL    |                |
| counter           | double      | YES  |     | NULL    |                |
| datetime          | datetime    | NO   |     | NULL    |                |
| details           | longtext    | YES  |     | NULL    |                |
| deviation         | smallint(6) | YES  |     | NULL    |                |
| engine            | smallint(6) | YES  |     | NULL    |                |
| event             | smallint(6) | YES  |     | NULL    |                |
| fuel_level        | double      | YES  |     | NULL    |                |
| gsm_level         | int(11)     | YES  |     | NULL    |                |
| latitude          | double      | NO   |     | NULL    |                |
| longitude         | double      | NO   |     | NULL    |                |
| satellite         | smallint(6) | YES  |     | NULL    |                |
| speed             | smallint(6) | YES  |     | NULL    |                |
| time              | time        | NO   |     | NULL    |                |
| treated           | tinyint(1)  | NO   | MUL | 0       |                |
| id_driver         | bigint(20)  | YES  | MUL | NULL    |                |
| id_vehicle_device | bigint(20)  | NO   | MUL | NULL    |                |
+-------------------+-------------+------+-----+---------+----------------+

This table has 10 to 20 or more inserts per second, also there are some updates and reads with where condition from the same table. The table is about 60 GB and has more than 20 M records.

To avoid a very high increase of data on this table, which could pose problems on dealing with it, each day at midnight, I try to delete old records and keep the last 45 days. This delete has to be done relatively for each id_vehicle_device, so the delete can be general.

The issue I'm facing is that when the delete query is running, it locks the record table and prevents inserts. How could I delete old records without lock issue, and the best way to deal with read/write in such huge mysql table?


texts: SQL Server Non Clustered Index Compression

texts:
I'm struggling to find out the answer, we currently use compression on all our data warehouse tables, which has improved performance and also greatly reduced the storage space used.

I've only recently realised, that we aren't compressing any of our non-clustered indexes. Should we also be doing this? Are there any gotchas that we should be aware of?


texts: How to compare the database of a backup from another environment

texts:
Has anyone had any luck with taking a backup (approx 30 tables) from an Oracle database's live environment, then loading a test environment with this backup and doing a comparison between the test environment and the backup taken from the live environment? Preferably without using queries against the live environment.

Note that the live environment will have transactions changing its data by the time the test environment is loaded, so I can't do a comparison using the live environment's data after loading the test environment.

The idea is to pick up any missing records, columns or even tables that the backup has missed. It would also be great to know if the actual data values were the same.

I'm thinking a hash function might be the best way to go. Are there any tools available for this?


texts: Is it safe to rely on the order of an INSERT's OUTPUT clause?

texts:
Given this table:

CREATE TABLE dbo.Target (
   TargetId int identity(1, 1) NOT NULL,
   Color varchar(20) NOT NULL,
   Action varchar(10) NOT NULL, -- of course this should be normalized
   Code int NOT NULL,
   CONSTRAINT PK_Target PRIMARY KEY CLUSTERED (TargetId)
);

In two slightly different scenarios I want to insert rows and return the values from the identity column.

Scenario 1

INSERT dbo.Target (Color, Action, Code)
OUTPUT inserted.TargetId
SELECT t.Color, t.Action, t.Code
FROM
   (VALUES
      ('Blue', 'New', 1234),
      ('Blue', 'Cancel', 4567),
      ('Red', 'New', 5678)
   ) t (Color, Action, Code)
;

Scenario 2

CREATE TABLE #Target (
   Color varchar(20) NOT NULL,
   Action varchar(10) NOT NULL,
   Code int NOT NULL,
   PRIMARY KEY CLUSTERED (Color, Action)
);

-- Bulk insert to the table the same three rows as above by any means

INSERT dbo.Target (Color, Action, Code)
OUTPUT inserted.TargetId
SELECT t.Color, t.Action, t.Code
FROM #Target
;

Question

Can I rely on the returned identity values from the dbo.Target table insert to be returned in the order they existed in the 1) VALUES clause and 2) #Target table, so that I can correlate them by their position in the output rowset back to the original input?

For Reference

Here is some trimmed-down C# code that demonstrates what's happening in the application (scenario 1, soon to be converted to use SqlBulkCopy):

public IReadOnlyCollection<Target> InsertTargets(IEnumerable<Target> targets) {
   var targetList = targets.ToList();
   const string insertSql = @"
      INSERT dbo.Target (
         CoreItemId,
         TargetDateTimeUtc,
         TargetTypeId,
      )
      OUTPUT
         Inserted.TargetId
      SELECT
         input.CoreItemId,
         input.TargetDateTimeUtc,
         input.TargetTypeId,
      FROM
         (VALUES
            {0}
         ) input (
            CoreItemId,
            TargetDateTimeUtc,
            TargetTypeId
         );";
   var results = Connection.Query<DbTargetInsertResult>(
      string.Format(
         insertSql,
         string.Join(
            ", ",
            targetList
               .Select(target => $@"({target.CoreItemId
                  }, '{target.TargetDateTimeUtc:yyyy-MM-ddTHH:mm:ss.fff
                  }', {(byte) target.TargetType
                  })";
               )
         )
      )
      .ToList();
   return targetList
      .Zip( // The correlation that relies on the order of the two inputs being the same
         results,
         (inputTarget, insertResult) => new Target(
            insertResult.TargetId, // with the new TargetId to replace null.
            inputTarget.TargetDateTimeUtc,
            inputTarget.CoreItemId,
            inputTarget.TargetType
         )
      )
      .ToList()
      .AsReadOnly();
}


texts:
The oracle 10g DB that is being currently used has exceeded the limit of 4GB. For the application to continue the db needs to be upgraded to 11g. But the upgrade doc requires a data pump export to be done from 10g and then data pump import it to 11g.

But the concern is the 10g exceeding its limit of 4GB and hence will not allow a data pump export to be done. Is there any alternate option to move the data to 11g other than data pump export and import.

Have tried the export with expdp but i get an error saying the database has exceeded th 4gb limit.


texts: How to find which SQL server instance is consumimg more CPU on 4 node / 20 instance Windows cluster?

texts:
I want to know which sql server instance is consuming more CPU on a 4 node active -active - active - active windows cluster. ?

situation: Windows cluster - 4 Node
all active, each node has 5 sql server instances, i want to check the CPU utilization of all the active instances, any help ?


texts:
I have data in the form:

|Date      |Overdrawn|
|20/09/2018|1        |
|21/09/2018|1        |
|24/09/2018|0        |
|25/09/2018|1        |
|26/09/2018|1        |

I need to sum the number of overdrawn days with the following rule:

Missing dates (weekends) should be treated the same as the last date available.

i.e. If overdrawn on Friday, Saturday and Sunday should be counted as 2 days overdrawn. If Friday is not overdrawn, Saturday and Sunday should also be treated as not overdrawn.

In the example above date 20, 21, 22, 23, 25, 26 should be treated as overdrawn and the sum should return 6.

Can someone explain how to extrapolate the overdrawn value for the missing dates before summing?

Thanks


texts: Why does my PostgreSQL query cost so much when I tack on an additional group-by?

texts:
I need to extract two fields from a table. Here's the query with just one of the fields:

set schema 'data';
    explain (analyze, verbose)
     select
        count(example9),
        example9,
        example5
    from table
    group by  table.example9, table.example5
    order by count(example9) desc

Here's the query plan:

"Sort  (cost=151128.71..151149.41 rows=8280 width=79) (actual time=1602.128..1602.164 rows=1852 loops=1)"
"  Output: (count(example9)), example9, example5"
"  Sort Key: (count(table.example9)) DESC"
"  Sort Method: quicksort  Memory: 316kB"
"  ->  HashAggregate  (cost=150507.08..150589.88 rows=8280 width=79) (actual time=1601.299..1601.705 rows=1852 loops=1)"
"        Output: count(example9), example9, example5"
"        Group Key: table.example9, table.example5"
"        ->  Seq Scan on data.table  (cost=0.00..129087.90 rows=2855890 width=79) (actual time=0.013..633.542 rows=2807146 loops=1)"
"              Output: example1, example2, example3, example4, example5, example6, example7, example8, example9, example10, example11, example12, example13, example14"
"Planning time: 0.108 ms"
"Execution time: 1602.380 ms"

Here's the query with the second field I want:

set schema 'data';
    explain (analyze, verbose)
     select
        count(example9),
        example9,
        example6
    from table
    group by  table.example9, table.example6
    order by count(example9) desc

and the corresponding query plan:

"Sort  (cost=152197.99..152249.74 rows=20700 width=85) (actual time=1618.241..1618.265 rows=1794 loops=1)"
"  Output: (count(example9)), example9, example6"
"  Sort Key: (count(table.example9)) DESC"
"  Sort Method: quicksort  Memory: 313kB"
"  ->  HashAggregate  (cost=150507.08..150714.08 rows=20700 width=85) (actual time=1617.381..1617.799 rows=1794 loops=1)"
"        Output: count(example9), example9, example6"
"        Group Key: table.example9, table.example6"
"        ->  Seq Scan on data.table  (cost=0.00..129087.90 rows=2855890 width=85) (actual time=0.005..635.558 rows=2807146 loops=1)"
"              Output: example1, example2, example3, example4, example5, example6, example7, example8, example9, example10, example11, example12, example13, example14"
"Planning time: 0.067 ms"
"Execution time: 1618.559 ms"

Now (this is the problem), here are the two fields combined together in the same query:

set schema 'data';
    explain (analyze, verbose)
     select
        count(example9),
        example9,
        example6,
        example5
    from table
    group by  table.example9, table.example6, table.example5
    order by count(example9) desc

and here is the query plan for it:

"Sort  (cost=794824.44..795341.94 rows=207000 width=92) (actual time=23189.695..23189.725 rows=1872 loops=1)"
"  Output: (count(example9)), example9, example6, example5"
"  Sort Key: (count(table.example9)) DESC"
"  Sort Method: quicksort  Memory: 350kB"
"  ->  GroupAggregate  (cost=728162.97..765931.60 rows=207000 width=92) (actual time=19125.027..23189.203 rows=1872 loops=1)"
"        Output: count(example9), example9, example6, example5"
"        Group Key: table.example9, table.example6, table.example5"
"        ->  Sort  (cost=728162.97..735302.70 rows=2855890 width=92) (actual time=19110.027..22635.209 rows=2807146 loops=1)"
"              Output: example9, example6, example5"
"              Sort Key: table.example9, table.example6, table.example5"
"              Sort Method: external merge  Disk: 286688kB"
"              ->  Seq Scan on data.table  (cost=0.00..129087.90 rows=2855890 width=92) (actual time=0.009..962.086 rows=2807146 loops=1)"
"                    Output: example9, example6, example5"
"Planning time: 0.079 ms"
"Execution time: 23556.835 ms"

Why does the query suddenly become so much more loaded when I tack on that extra group-by?

EDIT: example9, example6, and example5 are all character varying


texts: How can I copy a user while preserving all privs?

texts:
We're changing domains.  I'd like to create a new account for myself, NewDomain\Bob which has the same su-level privileges as OldDomain\Bob, to minimize the chance of disruption when IT flips the switch.

I can easy assign the new login the same database roles.  However, I'd like to associate the new login with the same users.  Sadly, it seems that a user can have only one login.  So what are the alternatives?

I could create the new login with a temporary user, reassign ownership of schemas, drop the old dbo user, and finally rename the new user.  However, this would strip some privileges from the old login.  In practice this may not matter, but I don't like the idea of cutting off any of my own privs before the new account is available.

The sp_change_users_login stored procedure looked promising to repoint the dbo user to the new login, but it has some serious limits - like not being usable with Windows logins.

Is there a workaround, or am I perhaps trying to fix the wrong problem?


texts: local administrator does not have permissions to create database in db2

texts:
I have a fresh standard installation of DB2 11.5 on Windows server 2016. According to documentation, i need the to have SYSADM or SYSCTRL to create a new Database.
The documentation for "sysadm_group - System administration authority group name configuration parameter" states.

SYSADM authority is determined by the security facilities used in a specific operating environment.

For the Windows operating system, this parameter can be set to local or domain group. Group names must adhere to the length limits specified in SQL and XML limits. The following users have SYSADM authority if "NULL" is specified for sysadm_group database manager configuration parameter:

Members of the local Administrators group
Members of the Administrators group at the Domain Controller if DB2_GRP_LOOKUP is not set or set to DOMAIN
Members of DB2ADMNS group if Extended Security feature is enabled.
The location of the DB2ADMNS group was decided during installation
The LocalSystem account

The parameter is empty and my account is in the DB2ADMN group which was automatically created during the install and also in the local admin group. Extended Security is enabled on that instance. Since my account used to be in these group through another group I added my account explicitly to both groups. Either way, db2 still thinks I don't have enough permissions to create a database.
I checked db2diag but there are no entries logged.
Is there anything that I missed or that I can check to see what's going on?


texts: Is US/Pacific timezone in MySQL automatically adjusting to DST?

texts: I'd like to define a view in MySQL which will convert some system data always written in UTC to our headquarter time, which is San Francisco.  If I use CONVERT_TZ(..., 'UTC', 'US/Pacific'), will it automatically reflect daylight savings time changes, so I won't need to update the view when it happens?


texts: One table to rule them all, or thousand smaller?

texts:
I have 10k users in my MySQL database and for every user i need to store the last 500 events.

Please advise me, how I should design my database? One MegaEventsTable or dedicated tables for every user?
Pros of many small tables:

I can pick fast, for example, 50 events by type from 500 entries than from 500*10k entries.
It is faster get top 500 entries, and kill all other entries in table for this user
I can shard my entries database some time in future

Contras:

i feel that 10k tables is HORRIBLE idea (and what if there will be 100K)


texts:
I moved postgresql's data directory by following the following steps:

Stop postgres
cp -a source_data_directory destination_data_directory
export PGDATA=destination_data_directory
Changing data directory to destination_data_directory within postgresql.conf
pg_ctl start

But when I run: "pg_ctl start" I get the following error. Can someone please help me figure out the error.

server starting
postgres@Server:/usr/lib/postgresql/9.1/bin$ postgres cannot access the server configuration file "/home/destination_data_directory/postgresql.conf": No such file or directory


texts: Renaming the system generated partitions during interval partitioning

texts:
I am trying to rename the system-generated partition names (generated during interval partitioning). I try to do this while my table is live(data read and write is happening).
I need two scripts.

I want a script which will rename all the interval partitions, but not for today. As I get the following error when I try to rename the partition which is still active(The system does not allow me to rename the active partition). The following query renames all the partitions(including today's which is active)

declare
    hv varchar2(9);
begin
      for x in (select partition_name, high_value
      from user_tab_partitions
      where table_name = 'FOO' and partition_name not like 'PART_%')
loop
      execute immediate 'select to_char('||x.high_value||'-1,''YYYYMMDD'') from d   ual'     into       hv;
dbms_output.put_line('alter table foo rename partition '||x.partition_name
               ||' to PART_'||hv);
 end loop;
 end;

I get the following error when I try to execute this. All the partition gets renames except for today's.

      Error report: ORA-00054: resource busy and acquire with `NOWAIT` specified or timeout expired ORA-06512: at line 9 00054. 00000 - "resource busy and acquire with `NOWAIT` specified" *Cause: Resource interested is busy. *Action: Retry if necessary.

I want another script which will rename yesterday's partition(this will be running on daily basis) so that it keeps on renaming recently generated partition every day. I could run the previous script, but that will take more time to execute which I don't want.


texts:
We use JPA to create the objects that map to our database tables, however whenever it starts looking at our views the entire entity generation process grinds to a halt.

Is there a way in SQL Server to create a schema that only has access to the tables in our database?


texts: Sql Profiler: what does Duration measure for exec sp_execute

texts:
If I capture an "exec sp_execute" command in SQL Profiler (2012), what exactly does the duration measure?  I'm trying to understand whether or not factors outside of SQL Server (network or the client) can effect the duration.  So if the duration of one call is higher than that of a similar call, is it correct to say "SQL Server took more time to process that call".  Conversely if the duration includes the amount of time it takes the client to read the data, then I can not assume that the problem is on SQL Server (it could have been network or a problem with the client program).

I'm attempting to track down some performance problems, and the answer to this question will hopefully help me determine whether or not the problem is in SQL Server or some other component (network/client/etc.)

In case it makes a difference, I'm talking about executing a very simple select statement like:

SELECT * FROM Customer WHERE Id=? and Product='ABC'

Thanks in advance.


texts:
The SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is at different part of the "BEGIN".  Is the below two query behave the same?
Will the views, viewOne and viewTwo, inherit the dirty read?  Or the view needs to have an explicit "NOLOCK"

Query 1

set transaction isolation level read uncommitted
begin
    select col1 from viewOne
    select * from viewTwo
    select * from table1
end

Query 2

begin
    set transaction isolation level read uncommitted
    select col1 from viewOne
    select * from viewTwo
    select * from table1
end


texts: Violation of PRIMARY KEY constraint when there is no duplicate

texts:
I inherited an old sql server data base, and I need to create trigger that copy particular row with new dates to the same table. I have to manually set Id of objects and I am getting Violation of PRIMARY KEY although there is no record with that Id in the table( I even tried to set Id to ridiculously high value, but error is the same)

CREATE TRIGGER [dbo].[tr_HR_WORKER_UPDATED]
   ON [server].[dbo].[HR_WORKER]
   AFTER UPDATE
AS BEGIN
    SET NOCOUNT ON;
    IF UPDATE (ID_ORGANIZATION)
    BEGIN

        DECLARE @IdCounter NUMERIC(15, 4)
        DECLARE @NextId NUMERIC(15, 4) --Yes, Id is numeric
        SET @IdCounter = 148
        SET @NextId = (SELECT wartosc from AA_COUNTER where id_licznik = IdCounter) + 1.0000 --I have to get ID from other table, increment, and update that table
        select @NextId

        UPDATE AA_COUNTER--updating counter
        SET wartosc = @NextId
        WHERE id_licznik = @IdCounter

        INSERT INTO TR_ASSIGNMENT_ORG(
                                   [ID_ASSIGNMENT_ORG]
                                  ,[FIELD1]
                                  ,[FIELD2]
                                  ,[FIELD3]
                                  ,[DATE_FROM]
                                  ,[DATE_TO]
                                  ,[ID_RESPONSIBLE_WORKER]
                                  ,[ID_WORKER]
                                  ,[ID_STRUCT_ORG]
                                  ,[STACJA]
                                  ,[CHANGE_DATE]
                                  )
        SELECT
                                   @NextId
                                  ,P.[FIELD1]
                                  ,P.[FIELD2]
                                  ,P.[FIELD3]
                                  ,GETDATE()
                                  ,NULL
                                  ,P.[ID_RESPONSIBLE_WORKER]
                                  ,P.[ID_WORKER]
                                  ,I.[ID_STRUCT_ORG]
                                  ,P.[STACJA]
                                  ,GETDATE() -- I can execute code to this line
                                  FROM dbo.TR_ASSIGNMENT_ORG as P, inserted as I
                                  WHERE ID_RESPONSIBLE_WORKER = I.ID_WORKER

        UPDATE TR_ASSIGNMENT_ORG
        SET DATE_TO = GETDATE()
        FROM inserted I
        WHERE TR_ASSIGNMENT_ORG.ID_RESPONSIBLE_WORKER = I.ID_WORKER
        AND TR_ASSIGNMENT_ORG.DATA_DO IS NULL

    END
END

Can someone explain to me why sql server throws "Violation of PRIMARY KEY X" when there is no key with this value in table?

ID_ASSIGNMENT_ORG is primary key, and i want to assign @NextId to it


texts: Managing concurrency when using SELECT-UPDATE pattern

texts:
Let's say you have the following code (please ignore that it's awful):

BEGIN TRAN;
DECLARE @id int
SELECT @id = id + 1 FROM TableA;
UPDATE TableA SET id = @id; --TableA must have only one row, apparently!
COMMIT TRAN;
-- @id is returned to the client or used somewhere else

To my eye, this is NOT managing concurrency properly. Just because you have a transaction doesn't mean someone else won't read the same value that you did before you get to your update statement.

Now, leaving the code as is (I realize this is better handled as a single statement or even better using an autoincrement/identity column) what are sure ways to make it handle concurrency properly and prevent race conditions that allow two clients to get the same id value?

I'm pretty sure that adding a WITH (UPDLOCK, HOLDLOCK) to the SELECT will do the trick. The SERIALIZABLE transaction isolation level would seems to work as well since it denies anyone else to read what you did until the tran is over (UPDATE: this is false. See Martin's answer). Is that true? Will they both work equally well? Is one preferred over the other?

Imagine doing something more legitimate than an ID update--some calculation based on a read that you need to update. There could be many tables involved, some of which you'll write to and others you won't. What is the best practice here?

Having written this question, I think the lock hints are better because then you are only locking the tables you need, but I'd appreciate anyone's input.

P.S. And no, I don't know the best answer and really do want to get a better understanding! :)


texts: How to start Oracle database's listener service after a change in the IP address?

texts:
I'm using Windows XP as my Oracle database server. Today there was a change in the IP address if the database server.

I want start my database instance's listener again. Windows XP prompst that:

OracleOraDb11g_home1TNSListenerMZOA_LISTENER service on the local computer started and stopped. Some services stop automatically if they do not have anything to do.

(ps:MZOA is the name of my db instance)

I updated listener.ora to reflect the new IP.

And my listener.ora is:

# listener.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

ADR_BASE_MZOA_LISTENER = D:\app\Administrator

MZOA_LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =CHINA-F781B4F30)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1521))
 #caution:192.168.1.100 is my new ip
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.100)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_MZOA_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  (SID_DESC =
      (SID_NAME =mzoa)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
   )
  )

Unfortunately when I want start the listener, Windows still shows the above prompt. And I donn't know how to do.


texts:
I have two queries.

First query:

select files.originalfilename
from files
inner join digitalfiles on files.fileid = digitalfiles.fileid
where digitalfiles.path in (
        select *
        from Staple
        )

This will get the orginialfilename with extensions. Like below:

originalfilename

abc.doc

cool.bmp

1342.png

at.docx

Now the second query:

select ft.Extension
    ,COUNT(f.OriginalFileName) as [Count]
    ,sum(df.Length) / 1024 as [Size KB]
    ,ft.FileTypeId
    ,cast(count(f.OriginalFileName) * 100.0 / sum(count(*)) over () as decimal(18, 6)) as [Pct]
from Files f
inner join DigitalFiles df on f.FileId = df.FileId
left join FileTypes ft on df.FileTypeId = ft.FileTypeId
where f.Deleted = 1
group by ft.Extension
    ,ft.FileTypeId

This query will give me output as:
Extension, Count, Size KB, File ID, Percentage

I want to use the result of first query in second query and get the result.


texts:
There are two tables containing objects and their coordinates. One contains hotels, the other contains restaurants. How to build a table that for each hotel lists nearest restaurant name and the distance?

WITH restaurant AS (
           SELECT 'A' r_name, 0.0 x, 0.0 y FROM dual UNION ALL
           SELECT 'B' r_name, 0.0 x, 6.0 y FROM dual UNION ALL
           SELECT 'C' r_name, 9.5 x, 0.5 y FROM dual UNION ALL
           SELECT 'D' r_name, 8.2 x, 4.7 y FROM dual -- etc
           ),

     hotel AS (
           SELECT 'First_Hotel' h_name, 1.0 x, 6.0 y FROM dual UNION ALL
           SELECT 'Second_Hotel' h_name, -4.0 x, 3.0 y FROM dual -- etc
           )

     select h.h_name, r.r_name as nearest_restaurant, distance from restaurant r, hotel h  /* some query */

Should return

 hotel          nearest_restaurant    distance
 First_Hotel       B                    1     -- B is the nearest restaurant to First_Hotel. Distance is sqrt((1.0-0.0)^2+(6.0-6.0)^2)=1
 Second_Hotel      A                    5     --if both A and B on the same distance choose based on r_name

Edited: I also have geoloc for both tables with x and y coordinates. Can geoloc help to build the query?


texts:
I am looking at a legacy system that stores XML blobs

A view is created over this so the data is more accessible (for reporting)

CREATE view [dbo].[Data]
as
select top 100 percent
data.value('(/Input/@FirstName)[1]','varchar(100)') as FirstName,
(select data.query('for $i in /Input/Control[@Name="Postcode"]/* order by $i/@UpdateDateTime descending return $i')).value('(/Value/@ValueCode)[1]','varchar(50)') as 'Postcode'
...
...
from data

Will SQL server generate statistics for queries run against this view?


texts: How can you tell when a query plan reuse is causing performance problems

texts:
When investigating a performance issue, a workmate suggested running DBCC FREEPROCCACHE to clear the plan cache. He came to this conclusion after noticing performance improved after a reboot.

This feels like a rather quick and dirty approach - is there a more conclusive, definite way of ascertaining whether clearing the plan cache is the best approach? Making a mistake with this decision could result in a performance issue.

Apologizes if this question is too vague :)

Thanks!


texts: sp_send_dbmail issue when using variable for subject line

texts:
When using a variable for the subject line for a database trigger email, I get the variables back in the subject line but they look like this:

<i loan_id="6686"><cm cust_name="John Bonham  IIIII"><ld loan_number="5555"><e FAMILIAR_NAME="Test User"/></ld></cm></i>

Is there a way to clean it up so that it looks better? For instance remove the <>?

   SET @subject =
    CAST ( ( SELECT top 1 i.loan_id,cm.cust_name,  ld.loan_number,  e.FAMILIAR_NAME
              FROM inserted i inner join deleted d on i.LOAN_ID = d.Loan_ID and i.STATUS = 4
                inner join Status s  on i.STATUS = s.Status_ID
                inner join Loan_Details ld on i.LOAN_ID = ld.loan_id and ld.LOAN_ID = d.LOAN_ID
                and ld.STATUS = s.Status_ID
                inner join Employee e on ld.OFFICER = e.EMP_ID
                inner join REL_CUST rc on rc.LOAN_ID = ld.loan_id
                inner join Customer_Master cm on cm.CUST_ID = rc.CUST_ID and cm.CUST_ID = rc.REL_TRAN_ID
                and relationship_id = 1

          WHERE i.STATUS <> d.STATUS and i.STATUS = 4 and relationship_id = 1 and d.STATUS <>4
          ORDER BY i.loan_id ASC
          FOR XML PATH('')
) AS NVARCHAR(MAX) ) ;


texts: Referencing database programmatically via T-SQL

texts:
I am writing a stored procedure that takes a database name as an argument and returns a table of that database's indexes and their fragmentation level.  This stored procedure will live in our DBA database (the DB that contains tables the DBAs use for monitoring and optimizing things).  The systems in question are all SQL Server 2008 R2 if that makes a difference.

I have the basic query worked out, but am stuck on trying to provide the indexes' actual names.  To the best of my knowledge, that information is contained in each individual's sys.indexes view.  My specific problem is trying to reference that view programmatically from another database's stored procedure.

To illustrate, this is the portion of the query at issue:

FROM sys.dm_db_index_physical_stats(@db_id,NULL,NULL,NULL,NULL) p
INNER JOIN sys.indexes b ON p.[object_id] = b.[object_id]
    AND p.index_id = b.index_id
    AND b.index_id != 0

The query works fine when executed from the database identified by @db_id, because it is using the proper sys.indexes view.  If I try to call this from the DBA database, however, it all comes up null, as the sys.indexes view is for the wrong database.

In more general terms, I need to be able to do something like this:

DECLARE @db_name NVARCHAR(255) = 'my_database';
SELECT * FROM @db_name + '.sys.indexes';

or

USE @db_name;

I have tried switching databases or referencing other databases using combinations of string concatenation and OBJECT_NAME/OBJECT_ID/DB_ID functions and nothing seems to work.  I'd appreciate any ideas the community might have, but suspect I will have to retool this stored procedure to reside in each individual database.

Thanks in advance for any suggestions.


texts: Migrating Data from SQL Server 2005 to 2016

texts:
I am new to administering SQL Server however I am comfortable with the SQL language and making SSIS packages.

I want to migrate data from SQL Server 2005 to 2016.

My question is do I need to worry about the system databases and other objects such as indexes, stored procedures, view, security and permissions. logins or can I just migrate data.

Which would be the recommended procedure in this case.


texts:
I'm sure I'm missing an obvious solution on this, but I'm trying to summarize values for groups that are defined by an explicit group number and an implicit ordering.  I'm sure that doesn't make this much clearer, so let's say I have this example source heap table:

GroupID     Value
----------- -----------
1           5
1           5
1           3
2           4
2           1
1           4
2           3
2           5
2           2
1           1

I would like a query that provides me with the following results:

GroupID     Values
----------- -----------
1           13
2           5
1           4
2           10
1           1

The implied ordering is the challenge that I've just not found a way to get around... yet.  Any help would be appreciated.

I was hoping I could create a deterministic row ordering with a query similar to the following:

SELECT    *
        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
FROM Table WITH(TABLOCK)
OPTION (MAXDOP 1)

I am hoping this forces an allocation order scan which would then give me a deterministic row order.

Sadly, I'm stuck with the data as is. I've got no other indicators here such as date, etc. that would provide any set order.  My hope was the trick outlined above would suffice, but I'm not entirely certain it will.

EDIT: Just to close this up as I'm know there were questions around why I was asking this, I had a series of heap tables, named by month/year, that contained line-item amounts that the business wanted to be summed up by day (which they correlated to the implied groups in my question).  Because it doesn't look feasible to do this effectively, we've settled on aggregating at the month (e.g. table) level, so this post helped me justify the alteration to the business requirements.  Thanks for everyone's input!


texts: Unspecified error (MS Visual Database Tools) when creating a table using GUI

texts:
I added my user to db_ddladmin role, but initially i had my own custom role with rights to create a table for that user. Either way creating a table using TSQL works, being part of both roles. Problem occurs when i try to create a table using GUI, tables -> new table, i get the Unspecified Error(MS Visual Database Tools)

However if i add that user as a db_owner , it works. I would just like to know if there is any right in particular that i might have missed which specifies whether user can create a table through GUI or not?

Note:
I am using SSMS 17.4 and SQL Server 2016.User is part of a domain created login.


texts: Which databases use real multidimensional indices? Is oracle ever using several indices to get data from tables, or will it always take the one that seems to have the highest selectivity? How about other dbms?


texts:
I tried following the docs for the same.
It says postgres.conf will have service.crt for ssl_cert_file, but it has /etc/ssl/certs/ssl-cert-snakeoil.pem
and now I can't proceed with it.

Any insight or tutorial will be helpful.


texts:
I am somewhat new to SQL and I have seemingly simple query but I can't seem to figure out the correct combination of functions. We receive time data from an external source as a five-character string, for example "0500p". I want to convert this string into military time. The format required for output is a five character string, for example "17:00".
I have tried

SELECT CONVERT(CHAR(5),STD_TM,108)

but I think it's not recognizing my "time" as a time since it just outputs the same thing I put in.