texts: At which point in a log backup does SQL Server truncate the log file?

texts:
I am investigating an issue where on a somewhat predictable schedule where our third party backup software begins a full backup of some of our databases during the workday. It starts a log backup, detects a broken chain in the LSN's and then converts it to a full backup.

I checked and there are no other log backups being run against the SQL servers we are experiencing this with.

What happens is that a log backup begins on schedule, but on some days the connection with the backup server is lost during the backup. The job sits in limbo and when it restarts with the same job id there is a broken chain detected and it begins a full backup of affected databases.

Scouring the logs it I can see the backup failures and the backup restarting with the same LSN which is no longer there.

So my question is, when there is a log backup with the truncate option does SQL Server truncate the log as it is backed up, or at the successful completion of the job? In my case it seems like a range of LSN's is being backed up, marked as backed up and when the job fails it looks for those same LSN's again when the job restarts.


texts:
I added the ClearDB addon to Heroku, but upon using the created username to create a new database, the error appeared:

Executing:
CREATE SCHEMA `androidchatterdatabase` DEFAULT CHARACTER SET utf8 ;

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1044: Access denied for user 'bcb4810d4a34ac'@'%' to database 'androidchatterdatabase'
SQL Statement:
CREATE SCHEMA `androidchatterdatabase` DEFAULT CHARACTER SET utf8

Viewing the GRANTS for this user, it notes:

Given the readout notes on database heroku_09ace026abf6348, I have ALL PRIVILIEDGES

Attempting to access the Users and Privileges tab to alter the user, the below returns:

At present, I am stuck.

How can I alter the access so I can create a database?


texts: Auto logging / tracking records changes at MS SQL

texts:
how can I set automatic recording all changes that were done at tables records (Updates) and to get some info / logging summery?
thanks


texts:
Because we are having the same problems as in http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/e51b775a-8601-491b-8ad4-a71b0f0d59ce we have the following question:
That is when inserting from a flat file source we wmiis the last row from the file.

Can you show us where we can find the advanvced column view to set the text qualification to false? Thanks in advance.


texts:
I'm working on a project to add some permission-based roles to our database servers to restrict users from modifying data if they don't need to be doing so. I've got four groups for each client; Users, PreProd Users, Data Admins, and PreProd Data Admins. I've got things set up fine for the users, but I'm running into some trouble with the Admin side.

I'm very new to SQL, so a lot of my testing is trial and error. I think I may have botched something quite badly though. Two of my Logins, demo_Data_PP_Admin, and demo_Data_Admin, seem to be stuck. When I paste my script in to CREATE those logins, I am told that the logins already exist. Visually, I can confirm they do not exist. Attempts to DROP LOGIN [demo_Data_PP_Admin] or DROP LOGIN [demo_Data_Admin] are met with

Msg 15151, Level 16, State 1, Line 1
Cannot drop the login 'dh\demo_Data_Admin', because it does not exist or
you do not have permission.

I know this is a super confusing issue, and I'm trying to sort it all out myself with my relatively beginner knowledge here.

TL;DR, the command CREATE LOGIN [dh\demo_Data_Admin] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO has a red underline beneath [dh\demo_Data_Admin], claiming the login already exists, but I can visually confirm that it does not.

I can try my best to answer any questions that people may have. Again, I apologize for the confusing nature and potentially confusing explanation of events. An answer to this question will likely lead to another question as well.


texts: How to create Postgres DB with case insensitive collation

texts:
I am using the postgres version 9.4.1 64-bit on windows 7 64-bit.
I need to create DB with the setting "case sensitive = OFF" but couldn't make this work. I refereed already many question on SO and other forums as well but even after trying out all those options my DB is still case sensitive and my search queries returns only partial results.

Does any one have success getting this work on windows environment?

Note: I am aware of the ILIKE operator but that option is not going to be considered by Architect team as we have a clear requirements that Database storage should be CASE INSENSITIVE by default. We do ot have any scenarios where we will need case sensitive search.


texts: MongoDB: Grant roles to multiple databases based on a pattern

texts:
Is it possible to grant database access to a user based on a prefix?

For example in MYSQL you can do the following:

GRANT ALL PRIVILEGES ON  `username\_%` . * TO  'username'@'localhost';

In my use-case I would like to grant the readWrite role a specific user on a database of the same name -- but also grant the ability to create a database matching this pattern, and give read/write access to it.

So for example, myuser would have the readWrite role on myuser database, but also have the ability to create myuser-otherdatabase.

Is this possible without giving server-wide roles (readWriteAnyDatabase) to this user for this purpose?

The application using this user account needs to be able to create prefixed databases to segment out data, and it should be able to do this on the fly, rather than having to have an administrator grant roles to the additional databases.

Any thoughts?


texts: Running out of space, add new drive (add file to filegroup)

texts:
I've been looking into a bunch of options on how to tackle issue of running out of space

The MDF file is currently on D drive (log file on different drive), I have an new empty E drive that I would like to start using

Based on what i've been told/ read I need to add a file on the E drive, and then run an Alter database command to add this file to the existing file group?
Then sql server will automatically see there is a lot of free space on the E drive and start saving data there?

My questions are

1) is the above correct?

2) what are the scripts to run to add the new file to the filegroup

3) do i need to tell sql server to stop saving data to the old D drive (as its almost full)


texts: Poor UnionAggregate (CLR aggregate) performance - CPU cost hint?

texts:
I'm trying to use SQL Server Spatial to work with data related to geographic regions defined by a third party, and am having trouble getting acceptable performance.

The following query takes quite a long time to run, and doesn't use any parallel operators:

UPDATE
    tblRegions
SET
    RG_Geography = RegGeo
FROM
    tblRegions
    CROSS APPLY
    (
        SELECT
            geography::UnionAggregate(RGD_Geography) AS RegGeo
        FROM
            tblRegionDetail
        WHERE
            RGD_RG_ID = RG_ID
    ) agg

If I convert that update to a select, it takes around 25 seconds to get a single region, and pegs one CPU core while doing so.  If I take out the aggregate, the select finishes almost instantly.  That makes it pretty clear that the aggregate itself is the problem.

Is there anything I can do to hint to the optimizer that the aggregate will have a high CPU cost?  In addition to that, is there anything I can do to get this update to run faster?  Adding parallel query hints has helped some, but hasn't gotten performance to anywhere near acceptable.


texts: Copy package in Production DB to Test DB with dependencies?

texts:
This is my first question here, so please let me know if this isn't the right place to ask.

I'm a new programmer, fresh out of school. Primarily I develop in Python/C#, with a dash of SQL when needed.

At my job, I've been assigned a task in PL/SQL, which involves modifying some existing procedures. All well and good, some new syntax to learn, but no serious roadblocks.

My problem is, I have no idea how to migrate the existing package/views/tables to our test database, which is basically an un-maintained sandbox environment.

I only need to copy two stored procedures in a package, two views, and 3 tables, so that I can modify the procedures and verify that the output is as-expected. However, I feel like there must be a better way to move this data than "crtl+c, crtl+v" in the editor window, or downloading and uploading table export files via my local machine.

I'm working in Oracle SQL Developer, and really only need to move enough data to execute these procedures and test my work. My job is really the development here, and the more time I spend trying to set up this test environment, the less time I spend coding.

Is there some sort of way to easily copy a package and all its data dependencies from one database to another? Bonus points if it's in Oracle SQL Developer, and easy for a non-DB admin to do. Thanks!


texts: DB2 connection rejected with JDBC and ODBC

texts:
Good day to all,
Currently I have 4 as400 servers, with db2 installed, is the production server and development server with its two replicas.

The development java team uses IBM Toolbox for Java and JTOpen for conection to db2, in a Weblogic middleware, for direct connections to the database uses DbVisualizer with JTOpen (jt400.jar)

The team of RPG no has problem, at connect with IBM Access for Windows and work with DB2

Mysteriously on Sunday the development server connections began to be rejected for Java team

The error log is:

Long Message:
The application requester cannot establish the connection. (Not able to pass connection to server job. Server job program was not found.)

Details:
   Type: java.sql.SQLException
   Error Code: -99999
   SQL State: 08001

None of the other three servers has this problem. In this page of support recommend 3 possible solutions

The system name is incorrect. One way to verify this is to use ping
with the specified system name. If ping fails, then there is a
TCP/IP configuration problem between your client and the system.
The user ID or password is incorrect.
The IBM i database host server is not started. You can start this by running STRHOSTSVR *DATABASE on the IBM i command line. You
can verify if the IBM i database host server is running using the
utilities.JPing application.

In step one send a ping to the server and it responds correctly in all machines of java team
The step 2 was extensively tested, and 3 was able be verified, RPG dev team was not problem

A test was made more with an ODBC client and the connection was rejected also with this error log

Error in the connection test in initializing provider CWBSY1008

There was an error overall security rc=8007

I tried to find solution, but I have not found anything.

Other things I made

IPL Partial and Full - Result not work
Resetting router tables - Result not work
Power off firewall and antivirus - Result not work
Resetting password of QUSER - Result not work

Could you please help?


texts:
I've got legacy ecommerce database, which is awfully designed and I need to move all products from it to new solution. But I stuck with how product properties stored. So it basically has tens of tables named category_properties_<category_id> that have first column named product_id and then variable number of columns that contain properties_id.

I. e. there can be table category_properties_42 with records like

|<product1_id>|20|21|22|23|24|27|

and table category_properties_43 with records like

|<product2_id>|17|21|56|

Is it possible using MySQL to export this data in more reasonable format? Because variable number of columns and variable table names makes querying by product almost impossible. Thanks in advance.


texts: Differences between .dump and .sql in pg_dump PostgreSQL

texts:
I'm trying to take a backup of schema using pg_dump like,

$ pg_dump -U ganapathy -n hotel_management > ganapathy_schema.dump

It create the backup, But I can't restore it using pg_restore. It give error like this,

  pg_restore: [archiver] input file does not appear to be a valid
  archive

Then I try to take the backup with .sql extension like,

$ pg_dump -U ganapathy -n hotel_management > ganapathy_schema.sql

But It also gives the same error.

  pg_restore: [archiver] input file does not appear to be a valid
  archive

After that I try to take a backup by specifying the format like, -Fc in both .dump and .sql extensions.
Then I try to restore it. It restored successfully.

So now my doubt is,

  What is the differences between the extensions of .dump and .sql in
  pg_dump in PostgreSQL?

Because both needs to specify the format.

Thanks in advanced...


texts: Error ORA-12560 when trying to connect Windows SQL*Plus client to Oracle 11g Linux server

texts:
I installed 11g on Centos VM on my mac.I have everything up and working on server.I have included following command I ran on cenos.

$lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-JUN-2014 14:49:41

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hayer)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                23-JUN-2014 14:03:58
Uptime                    0 days 0 hr. 45 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/hayer/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hayer)(PORT=1522)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

My tnsnames.ora file is as follows:

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = hayer)(PORT = 1522))

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hayer)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
      (SID = orcl)
    )
  )

~
~
~
~

On Centos server I can log in using sqlplus too. I changed tnsnames.ora on windows to same as server.I have added oracle host information in windows host file and I can ping oracle host and telnet host on port 1522.

I have disables service iptables so no type of firewall exists on centos.

But I still get following error on Windows client:

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 23 14:51:31 2014

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

Enter user-name: system as sysdba
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name: system as sysdba
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error


texts: Is Tablespace something like Logical Volume?

texts: I'm confused about tablespaces in PostgreSQL. Is it something like LVM? I mean when disk is getting full, can we add another disk, format and then CREATE TABLESPACE tblspace LOCATION /media/disk2/data? Is it enough? Or we should manually ALTER databases, tables or indexes to take benefit of it?


texts: How to return most recent element with distinct subquery

texts:
I have such query but it returns duplicated rows for same speaker

SELECT
    "speakers"."id",
    "speakers"."name",
    "speakers"."surname",
    "speakers"."photo_url" as "photoURL",
    "talks"."title" as "lastTalkTitle",
    "talks"."publication_date"::date as "lastTalkDate",
    "talks"."language_id"::uuid as "lastTalkLanguage",
    "conference_groups"."name" as "lastTalkConferenceName",
    CASE WHEN "user_speakers_favourited"."user_id" = '3B3ED15E-788E-447E-B946-BA52E7E90E62' THEN TRUE ELSE FALSE END as "favourited",
    coalesce(count("talk_ratings"."id") FILTER (WHERE "talk_ratings"."rating_up"),0)::bigint as "ratingUp",
    coalesce(count("talk_ratings"."id") FILTER (WHERE "talk_ratings"."rating_down"),0)::bigint as "ratingDown",
    coalesce(count(DISTINCT "talks"."id"),0)::bigint as "talkCount"
FROM "speakers"
LEFT JOIN "talk_speaker" ON "talk_speaker"."speaker_id" = "speakers"."id"
LEFT JOIN (
    SELECT DISTINCT ON ("talk_speaker"."speaker_id") "talks".*
    FROM "talk_speaker"
    LEFT JOIN "talks" ON "talks"."id" = "talk_speaker"."talk_id"
    ORDER BY "talk_speaker"."speaker_id" DESC, "talks"."publication_date" DESC
    ) as "talks" ON "talks"."id" = "talk_speaker"."talk_id" AND "speakers"."id" = "talk_speaker"."speaker_id"
LEFT JOIN "conferences" ON "talks"."conference_id" = "conferences"."id"
LEFT JOIN "user_speakers_favourited" ON "user_speakers_favourited"."speaker_id" = "speakers"."id" AND "user_speakers_favourited"."user_id" = '3B3ED15E-788E-447E-B946-BA52E7E90E62'
LEFT JOIN "talk_ratings" ON "talks"."id" = "talk_ratings"."talk_id" AND "talk_speaker"."speaker_id" = "speakers"."id"
LEFT JOIN "conference_groups" ON "conference_groups"."id" = "conferences"."conference_group_id"
GROUP BY "speakers"."id", "talks"."title", "talks"."publication_date", "talks"."language_id", "conference_groups"."name", "conferences"."id", "user_speakers_favourited"."user_id"
ORDER BY lower("speakers"."surname") ASC, "talks"."publication_date" DESC

Query should return most recent talk for speaker

talk_speaker table is pivot table as talk can have multiple speakers and speaker can have multiple talks.

thanks in advance for help


texts: Is this an example of an Enum (Enumerated Type)

texts:
I'm looking at a code base that I have inherited. I'm new to Postgres (but have an Oracle background), and I think this in one of the queries is an example of an enum. Can someone confirm if I'm correct in this thinking, and if not then what this is, and how I find out more about it.

SELECT fp.id AS fund_part_id, fp.fund_id, fp.principle_amount, fp.detail
FROM finance.fund_part fp
WHERE fp.status = 'live'::finance.fund_part_status;

I can see if the pg_enum catalog (table) that there are a series of records that belong to one enumtypid oid. Some of the entries in the enumlabel name column are ['live', 'for sale', 'pre-agrement']


texts: How can I map a login to a database using T-SQL (not SSMS)

texts:
I'm writing a program that requires me to assign all permissions and everything in code. I have gotten stuck in this part:

I just want to do the equivalent of clicking the little box under "Map" for the msdb database and assigning that user to SqlAgentUser role. I need a user to be able to add/edit SQL Server Agent jobs. I can get the settings right using SSMS but I can't for the life of me figure out how to do it in raw SQL.

I've looked into ALTER LOGIN but I'm not seeing anything that does what I need. I suspect I just don't know the correct terms to Google. I don't normally do this sort of thing.

Any help is much appreciated!


texts: Parameterized Queries in PostgreSQL using PHP fail to handle null values

texts:
I'm using PHP and PostgreSQL on OpenBSD 6.1, with pg_prepare and pg_execute to effect parameterized queries.

$rs = pg_prepare($con, base64_encode($query), $query);
$rs = pg_execute($con, base64_encode($query), $query_parameters);

It's working for quite a lot of queries, but raises PHP errors in some cases where I'm passing, or attempting to pass, a null value to a UUID field. In attempt to have some data validation, I'm specifying for each value its datatype.

insert into dummy(text_1,ipv4_2,uuid_3,int_4) values($1::varchar(32),$2::inet,$3::uuid,$4::integer);

This works just fine for all cases, except null values. The schema does allow for nulls in all fields. Integers were causing me problems until I cast the input to an integer, and actually assigned a null when there was no value. PHP:

$p_int_4 = (int)$api_arguments['int_from_form'];
if ($p_int_4 == '' || is_null($p_int_4)) $p_int_4 = null;

The parameters are passed as an array with variables being used:

$query_parameters = array($p_text_1,$p_ipv4_2,$p_uuid_3,$p_int_4);

When I try to set the UUID variable to null, I still get a pg_execute error:

pg_execute(): Query failed: ERROR:  invalid input syntax for uuid: &quot;&quot;

How can I still have the benefits of data validation, while also sending a null to the database? Two ways way I can see to get it to pass is to take away the casting to UUID, which seems to be a pretty good layer of defense against SQL injection attacks, and casting to varchar instead and hoping PostgreSQL can "handle it" and get the casting correct on its side.

** UPDATE **

I've been "paraphrasing" production code, which I cannot paste here obviously, and it works for everything except UUIDs. This is code I'm using for the UUID, with just the variable names changed:

$p_uuid = (string)$this->api_arguments['ADD_UUID'];
if ($p_uuid == '' || is_null($p_uuid)) $p_uuid = null;

The SQL statement is this simple:

insert into dummy(myuuid) values($1::uuid);

* Final Update *

It's more accurate to say that PHP doesn't handle empty values well for integer and UUID datatypes when passing them as parameters to pg_execute.

The following code sets all parameters to NULL where they are empty.

for ($x = 0; $x < count($query_parameters); $x++) {
  if ($query_parameters[$x] == '' || is_null($query_parameters[$x]))
    query_parameters[$x] = null;
}

Thanks Jasen. Constructing a working example led me to the bug :)


texts: I would like to use Express instead of Standard. Does Express still have the annoying habit of going to sleep if left unused for a period of time?


texts: How to get an exclusive connection in AutoRestore job ( sp_RestoreGene)?

texts:
I am using Paul Brewer's script for AutoRestore sp_RestoreGene on SQL Server 2008R2 box.

I created the stored procedure dbo.sp_RestoreGene on the Server 1 where the backup file is located.

I called the store procedure:

USE [DBA_]
DECLARE @RC int
EXECUTE @RC = [dbo].[sp_RestoreGene]
@Database = 'Production'
,@TargetDatabase = 'Test'
,@WithRecovery = 1
,@WithCHECKDB = 1
,@WithReplace = 1
,@FromFileFullUNC = '\\Server1\Restore_backup\Backup\FullBackupForTest\'
,@Log_Reference = 'Incident xyz'

GO

It generated the following Restore script.  I created a job which runs the script below in scheduled time on Server 2

;DECLARE @msg_Production VARCHAR(1000)
;RESTORE DATABASE [Test] FROM DISK = N'\\Server1\Restore_backup\Backup\FullBackupForTest\Production.bak'  WITH REPLACE, FILE = 2, NORECOVERY, STATS=10, MOVE N'Production' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test.mdf', MOVE N'Production_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_log.ldf'
;SET @msg_Production = 'Incident xyz';  RAISERROR (@msg_Production,0,0) WITH LOG;RESTORE DATABASE [Test] WITH RECOVERY
;SET @msg_Production = 'Incident xyz';  RAISERROR (@msg_Production,0,0) WITH LOG;DBCC CHECKDB('Test') WITH NO_INFOMSGS, ALL_ERRORMSGS

I got an error:

  Msg 3101, Level 16, State 1, Line 2 Exclusive access could not be
  obtained because the database is in use. Msg 3013, Level 16, State 1,
  Line 2  RESTORE DATABASE is terminating abnormally.

To get the exclusive connection when I restore manually I use the script below . It works perfect.

use master
Go
ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
waitfor delay '00:00:05' -- 5 sec delay .. no other spid takes our space !
GO

Restore script

Go
ALTER DATABASE Test SET MULTI_USER
go

Can I include in the restore script the alter single user and alter set multi_user script? Or is there a better way to get exclusive connection for autorestore?

How to get exclusive connection for Auto restore ?


texts: Postgres - Querying Min, Max and getting associated columns data specific to those result of those rows

texts:
I have query in which returns averaged values by site and month.  What I want to know is the min and max of those monthly averages by site and (the difficult part) the month when each occurred.

Here is an example:

SQL Fiddle Stuff here

CREATE TABLE events (
    esite     integer NOT NULL,
    edate     timestamp with time zone NOT NULL,
    evalue    integer NOT NULL
);

INSERT INTO events Values
    (1, '2016-01-03', 11),
    (2, '2016-01-05', 90),
    (1, '2016-01-08', 7),
    (2, '2016-01-10', 40),
    (1, '2016-01-15', 12),
    (1, '2016-01-18', 66),
    (2, '2016-01-22', 54),
    (2, '2016-02-03', 70),
    (2, '2016-02-05', 56),
    (1, '2016-02-08', 61),
    (2, '2016-02-10', 23),
    (1, '2016-02-15', 30),
    (1, '2016-02-18', 15),
    (1, '2016-02-22', 41);

So I'm looking for a query that returns (by site) the min and max monthly average evalues and the months at which the min and max values occurred.  I can get this by using the query below:

SQL Fiddle this

select esite, date_trunc('month', edate) as emonth, round(avg(evalue),2) as evalue_avg from events
  Group by esite, emonth

-- produces the following output:
esite | emonth                      | evalue_avg
2     | January, 01 2016 00:00:00   | 61.33
1     | January, 01 2016 00:00:00   | 24
1     | February, 01 2016 00:00:00  | 36.75
2     | February, 01 2016 00:00:00  | 49.67

Now for the part I'm having difficultly with, I need produce the following result: -- basically the min, max values and the date(month) at which each occurred by site -- (Desired Output)

-- (one row per site)
esite | avg_min | eval_avg_min_date          | avg_max | eval_avg_max
1     | 24.00   | January, 01 2016 00:00:00  | 36.75   | February, 01 2016 00:00:00
2     | 49.67   |February, 01 2016 00:00:00  | 61.33   | January, 01 2016 00:00:00

I've searched around and have seen some examples using windowing and lateral joins, but I haven't been successful in getting any of them to work.  This might be a pivot but since I don't have a fixed number of sites, this tends to be difficult with PostgresSQL.

I'm guessing if anyone has an easy way to do this using postgres 9.4+.

Your help would be appreciated!


texts: How to migrate SQL Server Stored Procedures using temporary tables or table variables to Oracle?

texts:
C# developer encouraged by management to write SQL Server stored procedures often produce procedures like this

create table #t1 (...);
insert into #t1 Select ... from table_a where ...;
insert into #t1 Select ... from table_b where ...;
update #t1 Set ... = ... where ...
Select * from #t1;

The single statement are rather simple and this method makes them produce correct results.

Often my task is to migrate such procedures to Oracle.

Let's face the following facts.

To different temporary tables in SQL
Server are completely independent and
can have any ad hoc structure.
Oracle global common tables are
global objects and all uses much
share the same table structure.
Modifying this structure is
impossible, while it is used
anywhere.

One of the things I learned from an Oracle dba, was to avoid the use of temporary tables whenever it is possible. Even the performance on SQL server benefits from such modifications.

Replace the individual inserts by unions

In the most simple case, the above can be transformed into something like

select case when ... then ... end, ... from table_a where ...
union
select case when ... then ... end, ... from table_b where ...
Order by ...;

Use of functions

Both scalar functions and table valued functions can help to transform your procedure into a single query of the above form.

Common Table expressions aka Subquery Factoring

Subquery Factoring is nearly the best Oracle has to offer to avoid temporary tables. Using it the migration of SQL Server to Oracle is again rather easy. This requires SQL Server 2005 and above.

These modifications improve the SQL Server version and in many cases make the migration straight forward. In other cases resort to global temporary tables makes it possible to do the migration in a bounded time, but it is less satisfying.

Are there further ways to avoid the use of global temporary tables in Oracle?


texts: What happen when Postgresql tablespace is null?

texts:
I'm facing a DWH on postgresql with no DBA (I'm not one) so I turn to you with hope to figure this issue/s.
Long story short, when I check the disk usage per tablespace, I get different distribution than when I run du
 on the linux machine.
I have 3 tablespaces I use: dwh, ssd, and archive.
And 2 more pg_default and pg_global that as I understand come with the installation.
This is the output from pg_tablespace:

  spcname   | spcowner |     spclocation        | spcacl | spcoptions
------------+----------+------------------------+--------+------------
 pg_default |       10 |                        |        |
 pg_global  |       10 |                        |        |
 ssd        |    16385 | /ssd/rt/data           |        |
 dwh        |    16385 | /ssd/rt/dwh            |        |
 archive    |    16385 | /archive/postgres/data |        |

When I run this query:

SELECT  tablespace,
    sum(pg_total_relation_size((schemaname || '.' || tablename)::regclass))/1024/1024/1024 as total_usage_gb
FROM pg_tables
group by 1

which suppose to output the usage per tablespace I get the following:

 tablespace |     total_usage_gb
------------+------------------------
            |   252.6884689331054688
 archive    |  2268.1800308227539063
 dwh        |     9.9236679077148438
 pg_global  | 0.00161743164062500000
(4 rows)

When I run du -sh on the folders that the tablespaces use I get the following:

1.1T    /ssd/rt/data
7.8G    /ssd/rt/dwh
1.5T    /archive/postgres/data
697M    /var/lib/pgsql/9.1/data/base
1.7M    /var/lib/pgsql/9.1/data/global

A bit more info before I ask my questions:
When I run show default_tablespace I get the following:

 default_tablespace
--------------------

(1 row)

When I run show temp_tablespaces I get the following

  temp_tablespaces
 ------------------

 (1 row)

I'm using postgresql 9.1

My questions are:

When the tablespace is null, what happen? Because there are no row
from the DB query that lists the "ssd" tablespace, there is one row
with tablespace null that has 253GB.
I don't think it went to pg_global or pg_default because those folders has less than 1GB occupied.
Where is the 1.1TB that the du -sh shows on folder /ssd/rt/data which suppose to be tablespace "ssd"
The du -sh list 1.5TB in archive folder but my query returns 2.2TB in the archive tablespace.

I'm sorry in advance if this is a trivial issue, I tried looking at it in the documentation but couldn't find much.

-- UPDATE --
I found the pg_database system_catalog. In there, there is a column that holds the default tablespace which in my case is the one named, "ssd".
I still couldn't solve the discrepancy between pg_total_relation_size and du command.
-- END OF UPDATE --


texts:
Ihave a database with two tables in it. One table has approximately 20 million rows and another has approximately 70 million rows.

When I run sp_spaceused on these two tables I find that the total size of the data is ~6.7GB and the total size of the index is ~2.6GB. Combined this is a total of ~9.3GB.

This is the only data on this server.

I am a SQL novice so I would assume that the amount of RAM these two tables will eat up would be somewhere close to the 9.3GB after the entire data set and indexes have been loaded into memory. But I am apparently woefully wrong as after running queries for a few minutes the memory taken up by the SQL Server process will hit 30+GB and continue to climb. I've even tested running the same query over and over again and the amount of memory being taken continues to climb.

The reason for the excessive amount of RAM is that the eventual size of the data will be 5-6 times this size and this will be used for data analytics.

My question is why is SQL Server using so much memory? If the entire table and index are loaded into memory what is it continuing to load into memory? Is there a way I can control this? Is there a different type of index I can be using besides nonclustered indexes if my goal is in-memory analytics? I have been reading into columnstore indexes but after testing on a smaller dataset I was actually able to achieve better performance by removing indexes altogether than I was with a columnstore index.

Overall, I'm confused as to what is going on with this server. Can anyone chime in with some guidance? At this time I'm trying to test out SQL Server 2014 but it's still in CPT2 so it isn't a solution until release.

I've also looked into column based storage technologies such as Amazon Redshift but their service is not HIPAA compliant.

On this question I found a few queries to run.  The cached size of my database right now is 32230.390625.  This is greater than 3 times the total size of the database.

db_buffer_pages = 4125490

db_buffer_MB = 32230

I also wanted to do a bit of testing and decided to completely wipe the database, restart the SQL service, and reload the data.  When I did that it was using ~20 GB of RAM.  I can't understand why.  That is over two times the size of the raw data and index.


texts: simpler way to write if today or tomorrow is between ColA or ColB

texts:
SELECT * FROM my_table WHERE
getdate() between SPRFRDTE and SPRTODTE
or dateadd(DD, 1, getdate()) between SPRFRDTE and SPRTODTE

is there a way to write this as if TODAY or TOMORROW is between SPRFRDTE and SPRTODTE rather than if TODAY is between SPRFRDTE and SPRTODTE or if TOMORROW is between SPRFRDTE and SPRTODTE


texts: Data migration between databases in the same instance

texts:
I have two almost identical (structurally) databases:

Documents
DocumentsOld

The original database existed before table partitioning was available, so the solution they found was to create the second database and migrate all the old data to it after each year. Now they want to merge both databases again.

I've done some research and I found that both database have just minor structure differences between tables and no IDENTITY columns.

So, aside from the minor structural differences, I'd say it'd be mostly possible to just INSERT values from the old database back to the first one.

The database is actually somewhat big and has more than 100 tables.

What I want to know is, would it make sense to use SSIS to do this, or is SSIS suited to this kind of job? Or should I just write T-SQL code to do the job?

As an additional question, are there any tools more suited than the first two options above to this kind of job?


texts: MySQL - How can a deadlock b caused by the same transaction?

texts:
I am experiencing a deadlock scenario, and it's possible that I'm just not reading this correctly but here are the details, my interpretation is at the bottom.

The players:

Transaction 1: An ETL that is deleting records in batches.

Transaction 2: A store front.

The offending table is as such:

CREATE TABLE items
(
  id BIGINT PRIMARY KEY,
  secondary_key BIGINT UNIQUE
)

T1 is attempting to delete records by the secondary_key, meanwhile T2 is attempting to update the secondary_key on an existing record

So T1 is doing:

DELETE FROM items where secondary_key IN (1,2,3,4,5,6);

And T2 is doing:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- This is set at the beginning of a larger transaction

UPDATE items SET secondary_key = 7 where id = 4;

The deadlock log that I the get is as follows:

Transaction 1

*** (1) TRANSACTION:
TRANSACTION 42065889, ACTIVE 25 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 62416 lock struct(s), heap size 7577808, 9631608 row lock(s)
MySQL thread id 177980, OS thread handle 47144643110656, query id 45451329  preparing
DELETE FROM items
      WHERE secondary_id IN (
        SELECT
          li.id
        FROM staging.legacy_items li
        WHERE change_operation = 'D'
        AND li.id BETWEEN 9082453 AND 29059282
      )
      AND legacy_id IS NOT NULL

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 878323 page no 160719 n bits 96 index PRIMARY of table `items` trx id 42065889 lock_mode X waiting

Transaction 2

*** (2) TRANSACTION:
TRANSACTION 42065914, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
34 lock struct(s), heap size 3520, 19 row lock(s), undo log entries 10
MySQL thread id 177524, OS thread handle 47137066059520, query id 45451721 updating
UPDATE `items` SET `items`.`secondary_id` = 29059300 WHERE `items`.`id` = 15558171

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 878323 page no 160719 n bits 96 index PRIMARY of table `items` trx id 42065914 lock mode S locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 878323 page no 160719 n bits 96 index PRIMARY of table `items` trx id 42065914 lock_mode X locks rec but not gap waiting

Result

*** WE ROLL BACK TRANSACTION (2)

SO.. to me it looks like:

Transaction 2 gets a shared lock on single record
Transaction 1 takes an exclusive lock on range of records
Transaction 2 tries to take an exclusive lock on the single record that it already has a shared lock for.

I can't understand why Transaction 2 wouldn't be able to get an exclusive lock on the record that it already has a shared lock for.

Am I interpreting this completely wrong?

Thanks!


texts:
In our MySQL database we have set several collations.
e.g.

Connection collation
utf8_unicode_ci
Server configured default collation utf8_unicode_ci
Database default collation utf8_general_ci
Table default collation utf8_unicode_ci, utf8_general_ci
Column collation utf8_spanish_ci
Trigger collation utf8_general_ci

This leads to collation mismatch errors in some occasions.
Here my question:

Question 1

Is the assumption correct that the collation of a cell is inherited?

e.g. trigger / connection collation > column collation > table default collation > database default collation > server default collation

Question 2

How do I streamline the collation settings and set all collations to utf8_unicode_ci but exclude cells set to utf8_bin?

The best result would be a database default character set and collation, and no explicit settings in tables or columns

e.g.

Database

CREATE DATABASE `search` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Original Table

CREATE TABLE `searchIndex` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keyword` varchar(50) CHARACTER SET utf8 COLLATE utf8_spanish_ci NOT NULL DEFAULT ''
  `keywordCI` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ''
  PRIMARY KEY (`id`),
  KEY `eintragIndex_idx_keyword` (`keyword`),
) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Desired resulting table

CREATE TABLE `searchIndex` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keyword` varchar(50) NOT NULL DEFAULT ''
  `keywordCI` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ''
  PRIMARY KEY (`id`),
  KEY `eintragIndex_idx_keyword` (`keyword`),
)

Trying to achieve this with

-- set connection collation
SET NAMES utf8 COLLATE utf8_unicode_ci;
-- set default database collation
ALTER DATABASE search DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
-- convert table, explicit setting of the utf8_bin collation
ALTER TABLE `searchIndex` CONVERT TO CHARACTER SET DEFAULT, MODIFY keywordCI varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';

But this results in the following table definition (wrong collation for keywordCI, table charset and collation still set explicit)

CREATE TABLE `searchIndex` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keyword` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `keywordCI` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `eintragIndex_idx_keyword` (`keyword`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

With the following queries I get the desired result, but I don't understand why - I am expecting to see the collation setting since the connection and default collation of the database is different

-- Convert to utf8 with utf8_GENERAL_ci collation
ALTER TABLE eintragIndex CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
-- In a separate query, modify the keywordCI collation
ALTER TABLE eintragIndex MODIFY keywordCI varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';

I am currently working on MySQL 5.6.10

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.6.10-log |
+------------+

mysql> show variables like '%collation%';                                                                                +----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.04 sec)


texts: How to set CHECK_POLICY OFF in the sql script

texts:
While installing our software in a HP laptop, we got a SQL error stating "The password does not meet windows policy requirements because it is too short."

When I checked, the local security policy in my machine has minimum 8 characters and in that laptop it has 12 characters. Our SQL password has 11 characters. That is why it is not installed in that laptop alone.

We can increase the password more than 12. But maybe in future the minimum password requirement can even change to 20 characters. So we thought of disabling the password check. Since I am new to this SQL, I don't know where to add the condition check. I have two SQL scripts. The code snippet for the 2 scripts is shown below. Please let me know where to add it.

Script1:

ALTER LOGIN [sa] WITH PASSWORD=N'MSSql2008!' GO

IF EXISTS (SELECT * FROM syslogins WHERE name = 'teradyne')
    BEGIN ALTER LOGIN [teradyne] WITH PASSWORD=N'SQL_PWD' END

GO

Script2:

GO

EXEC ('IF NOT EXISTS (SELECT * FROM syslogins WHERE name = ''clientsoftware'')
           EXEC sp_addlogin @loginame=''clientsoftware'', @passwd=''TER_SQL_PWD''
     ') GO

CHECKPOINT

GO

Where SQL_PWD = software1! for both scripts.

Please let me know where to add that CHECK_POLICY and also whether I need to add that CHECK_EXPIRATION


texts:
We are deciding between bulk insert or SSIS. We have a simple spreadsheet with 5 columns, customerid, cost, date, product name, etc. The spreadsheet is generally only around 200 rows every week.

All we do is import the flat file CSV into a simple table, no transformations or anything. After , we conduct few calculations in T-SQL. We could care less about performance, considering only 200 rows. At the end, we want customer to run stored procedure, which imports csv, and conducts automatic T-sql calculations.

What is the best option for easy use and simplicity? Seems like more dependency with getting out of the T-SQL window, creating the SSIS package, and then calling the ssis package in t-sql. At the same token, I hear Bulk insert is old and not be used anymore.

I researched few articles SSIS vs bulk insert for performance, but have a question about simplicity.

https://www.mssqltips.com/sqlservertip/4241/data-import-performance-comparison-tsql-vs-ssis-for-large-import/


texts: MySQL: Why is inserting data with transactions faster then without them

texts:
I am using stored procedure to generate sequence consisting of prefix and number in given range (etc sequence "AB100", "AB101", ..., "AB199", "AB200")

The stored procedure includes REPEAT loop, in which character string is constructed and inserted into table.

When all the looping is inside transaction, the procedure runs very fast, but when I insert data without transaction, running the procedure lasts 400 times longer.

Can anyone explain, why is that so?

Alternatively, if anyone has better idea how to generate sequences like this, i will be happy to hear them.

Here is the code:

-- create table
CREATE TABLE seq
(
    Code CHAR(12)
) ENGINE = INNODB;

-- fast procedure with transactions
DELIMITER $$
CREATE PROCEDURE sp_sequence(IN val_prefix CHAR(2), IN val_from INT, IN val_to INT )
BEGIN
    START TRANSACTION;
    SET @val = val_from;
    REPEAT
        INSERT INTO seq (Code) VALUES (CONCAT(val_prefix, @val));
        SET @val = @val + 1;
    UNTIL @val > val_to END REPEAT;
    COMMIT;
END$$
DELIMITER ;

-- slow procedure without transactions
DROP PROCEDURE IF EXISTS sp_sequence_slow;
DELIMITER $$
CREATE PROCEDURE sp_sequence_slow(IN val_prefix CHAR(2), IN val_from INT, IN val_to INT )
BEGIN
    SET @val = val_from;
    REPEAT
        INSERT INTO seq (Code) VALUES (CONCAT(val_prefix, @val));
        SET @val = @val + 1;
    UNTIL @val > val_to END REPEAT;
END$$
DELIMITER ;

And here are results:

mysql> CALL sp_sequence('TX', 11000, 12000);
Query OK, 0 rows affected (0.09 sec)

mysql> CALL sp_sequence_slow('TX', 11000, 12000);
Query OK, 0 rows affected (40.07 sec)


texts: SQL Replication Automatically Add table to Publisher

texts:
I am using SQL 2012 transactional replication for an entire database. Everytime I add a new table, say Table A, I have to manually go to the SSMS GUI, and add the table to be publisher, then I resync to import data.

Is there a way to automatically add tables with data into replication, or do I have to write a dynamic/t-sql/powershell script, which checks if sys.tables is replicated and then automatically adds to replication sp_addarticle ?


texts:
I have a query that looks a bit like this:

SELECT * FROM Stuff WHERE name LIKE '[a\]]%'

My goal is to match all names starting with a or ] (closed bracket). However, this seems to match everything starting with a] or \\].

Is this possible without OR, a lot of auto-generated LIKEs?


texts:
Windows event log filling up with

ERROR 1: "The activated proc '[dbo].[SqlQueryNotificationStoredProcedure-]' running on queue 'test.dbo.SqlQueryNotificationService-' output the following: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'"

And

Error 2: An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I am using  SQL Server 2008 R2. I have tried changing owner of the database, but still receive error messages.The service broker is disabled in all database except tempdb and msdb.
How to solve this error?


texts: SQL aliasing to redirect traffic between databases on the same server

texts: In order to upgrade a database with no client disconnects, I clone an existing database and put it in a readonly mode. Now, I want all user traffic to be redirected from the original database to the cloned one (both are on the same server). I want to do it with SQL aliases. However, I see that aliasing is possible for server instances and protocols. Is there another way to go about database redirects on the same server?


texts: After Inserting , Update / Insert into another table in pl/sql trigger

texts:
I am working on PL/SQL trigger i have two table

 TAG Table

SR.NO    ID    TAG    BRAND_ID
------  ----  -----  ----------
  1      10    ABC       99

MY_TAGS Table

 SR_NO    TAG    COUNT   BRAND_ID
--------   ---   ------   -------
   1      ABC       1      99

I need a trigger in such a way that

If tag ABC is inserted  with BRAND_ID=99 one more times in TAG table then i need to increase the count of ABC to 2 in MY_TAGS table
If new values inserted other than ABC for 99 then i need to insert new row in MY_TAG table

How can I handle this situation using trigger. I am new to PL /SQL any help would be great.

I tried like this , but it will always execute Insert statement (i.e else part )

 CREATE or replace TRIGGER trig1
    AFTER INSERT ON TAG
    REFERENCING NEW AS newRow OLD AS oldRow
    FOR EACH ROW
    WHEN (newRow.ID >=0)
    BEGIN
    IF(:newRow.TAG=:oldRow.TAG AND :newRow.BRAND_ID=:oldRow.BRAND_ID) THEN
       UPDATE MY_TAGS SET COUNT=(select max(counter) from MY_TAG)+1) where brand_id=newRow.BRAND_ID and tag=:newRow.TAG;
    ELSE
       INSERT INTO MY_TAGS VALUES((select max(SR_NO) from MY_TAGS)+1,:newRow.TAG,1,:newRow.BRAND_ID);
    END IF;
    END trig1;


texts:
When I take a dump of a particular table which has CLOB data type, the resulting dump doesn't handle escaping of special characters.

I get ' un escaped, so insert statements won't work.

Any ways to fix it?

I'm right clicking on the query result and getting the dump.

I'm using SQL Developer and am new to Oracle.


texts:
I have a website running on Wordpress Multisite + Woocommerce with over 5700 users on a Nginx + Apache server. I recently experienced some down time but didn’t get any errors, just a blank page.

I contacted my hosting company (WP Engine) and they said the following:

  your site is suffering from slow queries being run on tables with too
  many rows.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Name                           Rows
wp_users                       6046
wp_groups_user_group           6867
wp_rg_form_view                7657
wp_posts                       12574
wp_rg_lead_detail              12925
wp_woocommerce_order_items     13510
wp_term_relationships          14581
wp_options                     17169
wp_comments                    57730
wp_commentmeta                 65568
wp_usermeta                    176243
wp_woocommerce_order_itemmeta  278636
wp_postmeta                    491916
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

  The reason the errors are happening is because the queries on the
  above tables are not completing fast enough. This is due to excessive
  table rows. Here is an example query that is having the issue:

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Time: 140219 13:00:28
# User@Host: Websitename @ localhost [127.0.0.1]
# Thread_id: 3948879  Schema: wp_dbname  Last_errno: 0  Killed: 0
# Query_time: 2.394413  Lock_time: 0.000171  Rows_sent: 1  Rows_examined: 510794  Rows_affected: 0  Rows_read: 510794
# Bytes_sent: 89
SET timestamp=1392814828;
SELECT COUNT( order_items.order_item_id )
        FROM wp_woocommerce_order_items as order_items
        LEFT JOIN wp_woocommerce_order_itemmeta AS itemmeta ON order_items.order_item_id = itemmeta.order_item_id
        LEFT JOIN wp_postmeta AS postmeta ON order_items.order_id = postmeta.post_id
        LEFT JOIN wp_term_relationships AS rel ON postmeta.post_id = rel.object_ID
        LEFT JOIN wp_term_taxonomy AS tax USING( term_taxonomy_id )
        LEFT JOIN wp_terms AS term USING( term_id )
        WHERE   term.slug IN ('completed','processing','on-hold')
        AND     tax.taxonomy        = 'shop_order_status'
        AND     (
                    (
                        itemmeta.meta_key = '_variation_id'
                        AND itemmeta.meta_value = '1261'
                    ) OR (
                        itemmeta.meta_key = '_product_id'
                        AND itemmeta.meta_value = '1261'
                    )
        )
        AND     (
                    (
                        postmeta.meta_key = '_billing_email'
                        AND postmeta.meta_value IN ( 'john@email.com' )
                    ) OR (
                        postmeta.meta_key = '_customer_user'
                        AND postmeta.meta_value = '2936' AND postmeta.meta_value > 0
                    )
                )
     /* From [example.com/product/example_product/] in [example.com/wp-content/plugins/woocommerce/woocommerce-core-functions.php:1862] */;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

And finally they suggested for me to work with my database/web developer to slim the database and this will speed the queries up and allow them to work properly.

As I am not database expert, I just wanted to ask here - is what WP Engine recommended to me reasonable? I ask because I know there are many websites running on much bigger databases that are larger than mine and they're working properly! That being said, Could it possibly be that WP Engine just has slow server processing or should I do what they recommended?

Thanks


texts: Watch "Alter Table" statement changes in Mysql database

texts:
I have tried to find any way to track changes in tables where the ALTER TABLE statement was used.

It is known that the trigger event can be INSERT, UPDATE or DELETE  according to ==> Mysql triggers

Anyway I tried to use this query to enable trigger for ALTER TABLE ==> CREATE TRIGGER my_trigger ALTER TABLE ON my table ... but it did not work.

Is there any way to watch changes in a mysql table if it was changed by using ALTER TABLE statement?


texts:
I have inherited a table that has NULL values for float fields, instead of a 0 value.  I am doing some basic testing for querying, and in my tests both ISNULL() and COALESCE() present the same accurate output.  In my testing (a table with roughly 5K records) time difference is very negligible as well.

Question being with the sample DDL below, is there a benefit to use ISNULL() over COALESCE() or vice-versa?  Are there circumstances that I have yet to see that could provide an inaccurate calculation?

Create Table #Test
(
  blue float
  ,red float
  ,green float
)

Insert Into #Test VALUES
('14', NULL, '12')
,(NULL, '12', '10')
,(NULL, NULL, '8')
,('10', '2', NULL)

Select
ISNULL(blue,0)+ISNULL(red,0)+ISNULL(green,0)
FROM #Test

SELECT
COALESCE(blue,0)+COALESCE(red,0)+COALESCE(green,0)
FROM #Test


texts: Create index for WHERE COALESCE() condition

texts:
I'm using PostgreSQL V9.6.11

Table DDL:

CREATE TABLE test_c (
          insrt_prcs_id bigint NOT NULL,
          updt_prcs_id bigint, src_sys_id integer NOT NULL,
          load_dttm timestamp(6) with time zone NOT NULL,
          updt_dttm timestamp(6) without time zone);

I was trying to create an index for the query below:

SELECT *
FROM test_c
WHERE COALESCE(u_dttm,l_dttm) > '2020-04-10 15:29:44.596311-07'
AND   COALESCE(u_dttm,l_dttm) <= '2020-04-11 15:29:44.596311-07'

Create index as:

create index idx_test_c  on test_c(COALESCE((updt_dttm, load_dttm)))

But the query planner is not scanning the index:

EXPLAIN ANALYZE
SELECT *
FROM test_c
WHERE COALESCE(u_dttm,l_dttm) > '2020-04-10 15:29:44.596311-07'
AND   COALESCE(u_dttm,l_dttm) <= '2020-04-11 15:29:44.596311-07'

Seq Scan on test_c as test_c (cost=0..1857.08 rows=207 width=496) (actual=5.203..5.203 rows=0 loops=1)
Filter: ((COALESCE((test_c.updt_dttm)::timestamp with time zone, test_c.load_dttm) > '2020-04-10 15:29:44.596311-07'::timestamp with time zone) AND (COALESCE((test_c.updt_dttm)::timestamp with time zone, test_c.load_dttm) <= '2020-04-11 15:29:44.596311-07'::timestamp with time zone))
Rows Removed by Filter: 41304

Why is the index scan not happening?


texts: Combining fields to make a unique record or keeping them separated

texts:
I've been asked to implement what has before, been a bunch of CSV files, into a MySQL database.  I'm at a point in the design where all the schemas, tables and fields have been mocked-up; however I'm hesitating about how to approach one particular part, especially with regards to read-ability (of eventual queries), flexibility and scalability.

A little background first:

We are collecting DNA samples from various types of animals (e.g. pigs/mice) [In the future, I can see us adding more animals.]; we need to keep track of metadata both for the actual sample that was collected as well as the animal.  Each animal has an ID (not defined by us), however it is not unique, and both a mouse and a pig could conceivably have the same ID.  Furthermore, each sample from a particular animal belongs to a sample set.  IDs for the sample are recycled (and thus non-unique), however the sample set ID is never recycled [thus the combination of sample ID & sample set ID is unique].

I will have several tables that will include fields with the AnimalID and others with the sampleID.

How I'm imagining settings things up:

for each of the tables that require animal information, I'll have (among others) the fields: AnimalID & AnimalType (e.g. mouse/pig)
for each of the tables that require sample information, I'll have (among others) the fields: SampleID & SampleSet

The problem with this, is that if I want to join across tables, I'll have to join across two fields.  However, I could use the SampleSet and AnimalType as foreign keys for lookup tables & it makes retrieving a particular set or animal type very straightforward.

An alternative approach:

for each of the tables that require animal information, I'll have a field (among others) that combines the AnimalID & Type together.  So I could have something like P### for a pig or M### for a mouse.
for each of the tables that require animal information, I'll have a field (among others) that combines the SampleID & Set together.  So I could have something like A### for sample set A.

The problem with this is that it's not as straightforward to select (for example) all the samples from a particular set (although of course you could query with a: where sampleID like "A%"; however it makes joining across tables straightforward.

So, which is the "better" more flexible approach?  Perhaps there is another approach that could leverage the advantages of both approaches?


texts: SSMS 2012 - Cannot execute script - Object reference not set to an instance of an object.(Microsoft.SqlServer.Smo)

texts:
I get this error while trying to debug a very simple script:

===================================

Cannot execute script.

===================================

Object reference not set to an instance of an object. (Microsoft.SqlServer.Smo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.Server.get_IsSingleUser()
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptAndResultsEditorControl.DebugWrapper.Start()
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptAndResultsEditorControl.OnDebugScript(Object sender, EventArgs a)

other than this, everything else works just fine.
Any idea how to solve this?


texts: How does the actual restoration works in SQL Server internally?

texts:
Suppose my Database DB1 lost some data, but I have a Full, Differential and then followed by Transaction log backups.

Till full\Differential backup I can understand it is like the snapshot of the database, but after this, how T-log Backup works, if we have point in time recovery.

Means like all the committed transaction from the T-log Backup are taken in a sequence and then after it is performed again on the Database (DB1).

Is this right?

If yes, Is this same is followed during log-shipping, i.e, when logs are shipped from one end to other.

Thanks in Advance.


texts: Estimating a Size of Joining a Relation with itself

texts:
I'm studying size estimation of logical query plans in order to select a physical query plan.

I was wondering what is the size of joining (natural join) a relation to itself?
e.g R(a,b) JOIN R(a,b)


texts: How to change SQL Server in a domain with a new server machine and latest SQL database?

texts:
We have an SQL Server in our domain.
The SQL Server version is MS SQL 2000 and the Operating System is Windows Server 2003.

Now I want to change the machine itself with new computer running windows server 2012 and SQL Server 2008.

How to do this ?


texts: SQL Server Availability Group LeaseTimeout and slow IO

texts:
Our 5 main databases runs on a physical (2 * 8 cores, 512GB, Hypertreading) SQL Server 2016 SP2 Enterprise in a single Availability Gruop and sometimes we get errors that the lease-timeout has expired. My understanding is that if the lease can't get updated there is a system-wide problem.
When I check the output of sp_server_diagnostics (*SQLDIAG*.xel files), in the log folder of the primary replica, around the time of the timeout I always find pending IO operations.

<ioSubsystem ioLatchTimeouts="0" intervalLongIos="0" totalLongIos="1">
<longestPendingRequests>
<pendingRequest duration="26566" filePath="\?\F:\SqlLogs\db1.ldf" offset="80824832" handle="0x8d10" />
<pendingRequest duration="1987" filePath="\?\O:\SqlLogs\db2.ldf" offset="3880740352" handle="0x1330" />
<pendingRequest duration="1093" filePath="\?\O:\SqlLogs\db3.ldf" offset="288143360" handle="0x132c" />
<pendingRequest duration="974" filePath="\?\O:\SqlLogs\db3.ldf" offset="288145408" handle="0x132c" />
<pendingRequest duration="937" filePath="\?\O:\SqlLogs\db3.ldf" offset="288146944" handle="0x132c" />
</longestPendingRequests>
</ioSubsystem>

This is what I find in the clusterlog of the primary replica:

WARN  [RES] SQL Server Availability Group: [hadrag] Failed to retrieve data column. Return code -1
ERR   [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost
ERR   [RES] SQL Server Availability Group <AG_Name>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel
ERR   [RES] SQL Server Availability Group <AG_Name>: [hadrag] Resource Alive result 0.
ERR   [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost
ERR   [RES] SQL Server Availability Group <AG_Name>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel
ERR   [RES] SQL Server Availability Group <AG_Name>: [hadrag] Resource Alive result 0.
WARN  [RHS] Resource AG_Name IsAlive has indicated failure.

This are the erros in the SQL Server errorlog:

Error: 19407, Severity: 16, State: 1
SQL Server hosting availability
group 'AG_Name' did not receive a process event signal from the
Windows Server Failover Cluster within the lease timeout period.
Error: 19407, Severity: 16, State: 1
The lease between availability
group 'AG_Name' and the Windows Server Failover Cluster has
expired. A connectivity issue occurred between the instance of SQL
Server and the Windows Server Failover Cluster. To determine whether
the availability group is failing over correctly, check the
corresponding availability group resource in the Windows Server
Failover Cluster.
Always On: The local replica of availability group 'AG_Name' is
going offline because either the lease expired or lease renewal
failed. This is an informational message only. No user action is
required.

This is the output from SELECT @@version:

Microsoft SQL Server 2016 (SP2-CU15) (KB4577775) - 13.0.5850.14 (X64)
Sep 17 2020 22:12:45   Copyright (c) Microsoft Corporation  Enterprise
Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2
Standard 6.3  (Build 9600: )

In our monitoring there are no signs of high cpu usage. Also no memory dumps are created at the time of the problem.
As a result of this timeout the WSFC-service restarts the cluster resource 'AG_Name'. After that this resource is restarted everything works perfect again.
What I don't understand is: how can slow IO-requests cause a lease-timeout? Can to many pending IO-requests cause a lease-timeout?


texts: Exporting from Oracle enterprise edition to standard edition reaches schemas limit

texts:
So, I'm trying to export an oracle database(10.2) from EE to SE over a networklink.
It all works fine except that I'm hitting a limit. The database has 1335 schemas and it seems that I can't transfers these in one go. Not with the schemas parameter at least.
Currently I'm doing a full=n with schemas=(the schemas to export) as I'm not really sure what a full=y exclude=schemas:() will transfer. Seeing that I want a clean database.

I tried using cmdline impdp and a par file. But got the same error in both:

When trying 1000 schemas:
ORA-39001: invalid argument value
ORA-39071: Value for SCHEMAS is badly formed.

When trying 250:
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-39097: Data Pump job encountered unexpected error -6502

And when using 25 schemas it works fine.

The syntax is exactly the same, being built dynamicly.
As we can see it appears that the SCHEMAS variable has a length wich oracle can't handle.

Has anyone of you tried the same thing? Would you recommend going the easy way with full=y? Are you confident it doesn't transfer EE history or data.

Update:
An example of EE data imported when using the full=y on SE

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
ORA-00001: unique constraint (SYS.WRI$_ALERT_THRESHOLD_PK) violated
Failing sql is:
BEGIN
dbms_server_alert.set_threshold(9000,dbms_server_alert.operator_GE,'85',dbms_server_alert.operator_GE,'97',1,1,NULL,dbms_server_alert.object_type_TABLESPACE,'');COMMIT; END;

Which is AWR which is an enterprise feature.

Another is

ORA-29357: object AUTO_TASK_CONSUMER_GROUP already exists
Failing sql is:
BEGIN
dbms_resource_manager.create_consumer_group('AUTO_TASK_CONSUMER_GROUP','System maintenance task consumer group','ROUND-ROBIN');COMMIT; END;

which I believe is the resource manager and in it's turn enterprise feature.


texts:
Is there an Open Source Metadata Management Solution? I'd like to create a Metadata Repository that will hold the details of the metadata of database schemas, tables and data items of hundreds of Enterprise databases.

I'm especially interested in something that can automatically query the schema data of the databases to be able to track changes in the metadata related to tables. i.e. changes to column data sizes, tables and columns added etc.


texts: Stored procedure deadlocking itself, how do I resolve it?

texts:
MSSQL 2012.  Stored procedure fires twice with the same parameters within 10ms of each other, and we get deadlocks.

Stored procedure basically looks like this (can't share actual code but here's the pertinent pseudocode):

BEGIN TRAN
UPDATE t
SET
    t.Column1 = NewValue1,
    t.Column2 = NewValue2,
    t.Column3 = NewValue3,
    etc for 20 columns...
FROM Table t
    INNER JOIN Results r
        ON r.ID = t.ResultID
WHERE
    UserID = @userid
    AND Deleted = 0

UPDATE t
    SET Deleted = 1
FROM Table t
    LEFT JOIN Results r
        ON r.ID = t.ResultID
WHERE
    t.UserID = @userid
    AND t.Deleted = 0
    AND r.ID IS NULL
COMMIT TRAN

Deadlocks are occurring on Table, indexes involved are UserID (non-clustered covering index) and the Primary Key.  When I run the procedure in my test environment, the execution plans show that both UPDATEs are using the covering index to get the rows (seek, no lookups), then having to update the PK and the covering index.

Possible solutions I've thought of are:

Removing the explicit transaction control.  Since the two updates don't really rely on each other, that seems viable.  Would that resolve the deadlock?
Removing the PK and making UserID the (non-unique) clustered index.  This, however, may impact other things that use the PK elsewhere.
Implementing some boolean logic in the second UPDATE to avoid running it unnecessarily.  Doesn't actually fix the problem though, just makes it happen less often.

I'm about 2 months into my career as a dba, and deadlocks have been difficult to get my head around.  Any advice would be greatly appreciated!


texts: How do you join three or more records from the same table?

texts:
I have table with a lot of data in it, and we would like a way to essentially flag certain documents to indicate that they are related to (x) other documents:

TABLE A
-----------
id
name
created_at
modified_at

Records:   id        name              created_at              modified_at
            1       arlo g    2020-04-29 12:30:00      2020-04-29 12:30:00
            2     kenny ws    2020-04-29 12:32:00      2020-04-29 12:32:00
            3      roger w    2020-04-29 12:36:00      2020-04-29 12:36:00
            4     frank lw    2020-04-29 12:41:00      2020-04-29 12:41:00
            5    depeche m    2020-04-29 12:43:00      2020-04-29 12:43:00
            6       manson    2020-04-29 12:46:00      2020-04-29 12:46:00

Now - assume a user is viewing document id 1. They want to tie it to documents 3 and 4.

If a user then opens document 1, the system can easily pull documents 3 and 4.

On the same note - if a user opens document 3, they can easily pull documents 1 and 4.

Is this possible? I am not sure how to structure a query clause to pull the document ids for a given document, such that the list is the same each time no matter which one of those documents is queried on.


texts:
I must be missing something pretty basic, but i have 2 tables.  Both are basic, just a primary key (as a uniqueidentifier) and a geometry column in each.  I'm trying to find if a point in one of the table exists in the polygon in the other table.

So this works without any problems (returns a row from PolygonTable):

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POINT (-117.6743333 33.8343333)', 0);
select * from PolygonTable where Polygon.STIntersects(@g)= 1

and this works as well

declare @h geometry
select @h =  Point from PointTable where PointGUID=N'3B77C26E-ACAC-4328-ED94-08D5EDE20723'
SELECT @h.ToString();  // outputs POINT (-117.6743333 33.8343333) above

but for some reason, the following does NOT work

declare @h geometry
select @h =  Point from PointTable where PointGUID=N'3B77C26E-ACAC-4328-ED94-08D5EDE20723'
select * from PolygonTable where Polygon.STIntersects(@h)= 1

That query returns no rows at all.  it's the same thing at the top query basically, what am i missing?

here is a gist of the basic table settings:
https://gist.github.com/vishnu4/9bd70658b49c2cfa00ca81ff1d25bc84
you can use the queries i have above to test my results.


texts: Why is my indexed DISTINCT ON so much slower than my INNER JOIN?

texts:
I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the latest_purchase_id column and update it with a trigger whenever I add a purchase (see https://dba.stackexchange.com/a/243988/186435).

I'd rather not use a trigger, so I tried using a DISTINCT ON query with an index, but it's much slower and I'm not sure why.

Table customers:

       Column        |  Type    |                       Modifiers                        | Storage  | Stats target | Description
---------------------+----------+--------------------------------------------------------+----------+--------------+-------------
 id                  | integer  | not null default nextval('customers_id_seq'::regclass) | plain    |              |
 latest_purchase_id  | integer  |                                                        | plain    |              |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
    "customers_latest_purchase_id" btree (latest_purchase_id)
Foreign-key constraints:
    "customers_latest_purchase_fk" FOREIGN KEY (latest_purchase_id) REFERENCES purchases(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "purchases" CONSTRAINT "purchases_customer_fk" FOREIGN KEY (customer_id) REFERENCES customers(id) DEFERRABLE INITIALLY DEFERRED
Has OIDs: no

Table purchases:

     Column   |  Type     |                        Modifiers                       | Storage  | Stats target | Description
--------------+-----------+--------------------------------------------------------+----------+--------------+-------------
 id           | integer   | not null default nextval('purchases_id_seq'::regclass) | plain    |              |
 customer_id  | integer   |                                                        | plain    |              |
Indexes:
    "purchases_pkey" PRIMARY KEY, btree (id)
    "purchases_customer_id_id" btree (customer_id, id)
    "purchases_customer_id" btree (customer_id)
Foreign-key constraints:
    "purchases_customer_fk" FOREIGN KEY (customer_id) REFERENCES customers(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "customers" CONSTRAINT "customers_latest_purchase_id" FOREIGN KEY (latest_purchase_id) REFERENCES purchases(id) DEFERRABLE INITIALLY DEFERRED
Has OIDs: no

DISTINCT ON query:

EXPLAIN ANALYZE SELECT DISTINCT ON (customer_id) id, customer_id FROM purchases ORDER BY customer_id DESC, id DESC;
 Result  (cost=0.43..162516.37 rows=381 width=8) (actual time=0.050..1478.196 rows=823 loops=1)
   ->  Unique  (cost=0.43..162516.37 rows=381 width=8) (actual time=0.047..1477.754 rows=823 loops=1)
         ->  Index Only Scan Backward using purchases_customer_id_id on purchases  (cost=0.43..157850.96 rows=1866163 width=8) (actual time=0.045..1066.759 rows=1866132 loops=1)
               Heap Fetches: 1363529
 Planning Time: 0.096 ms
 Execution Time: 1478.408 ms

INNER JOIN query based on latest_purchase:

EXPLAIN ANALYZE SELECT c.id, p.id FROM customers c JOIN purchases p ON c.latest_purchase = p.id;
 Nested Loop  (cost=0.43..43877.27 rows=7594 width=8) (actual time=0.508..112.665 rows=755 loops=1)
   ->  Seq Scan on customers d  (cost=0.00..213.94 rows=7594 width=8) (actual time=0.006..2.861 rows=7594 loops=1)
   ->  Index Only Scan using customers_purchase_pkey on purchases p  (cost=0.43..5.75 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=7594)
         Index Cond: (id = c.latest_purchase)
         Heap Fetches: 583
 Planning Time: 1.032 ms
 Execution Time: 112.861 ms


texts: SQL Server Multiple JOINS with Table Value Function - query never ends

texts:
I have a query with 4 joins using a table value function to get the data and when I execute it the query never ends.

Issue Details

Table valued function:

CREATE FUNCTION [dbo].[GetIndicator]
(
      @indicator varchar(50),
      @refDate datetime
)
RETURNS
TABLE
AS
RETURN
    SELECT
        T1.Id
        , T1.ColINT_1
        , T1.ColNVARCHAR_1 collate DATABASE_DEFAULT as ColNVARCHAR_1
        , T1.ColNVARCHAR_2
        , T1.ColSMALLDATETIME_1
        , T1.ColDECIMAL_1
        , T1.ColDECIMAL_1

    FROM TABLE2 T2
    JOIN TABLE3 T3
    ON T2.COLFKT3 = T3.Id
        AND T3.ReferenceDate = @RefDate
        AND T3.State != 'Deleted'
    JOIN TABLE4 T4
        ON T2.COLFKT4 = T4.Id AND T4.Name=@indicator
    JOIN TABLE1 T1
        ON T2.COLFKT1=T1.Id

Query:

DECLARE @RefDate datetime
SET @RefDate = '30 April 2014 23:59:59'

SELECT DISTINCT OTHERTABLE.Id As Id
    FROM
            GetIndicator('ID#1_0#INDICATOR_X',@RefDate) AS OTHERTABLE
        JOIN GetIndicator('ID#1_0#INDICATOR_Y',@RefDate) AS YTABLE
            ON OTHERTABLE.SomeId=YTABLE.SomeId
            AND OTHERTABLE.DateOfEntry=YTABLE.DateOfEntry
        JOIN GetIndicator('ID#1_0#INDICATOR_Z',@RefDate) AS ZTABLE
            ON OTHERTABLE.SomeId=ZTABLE.SomeId
            AND OTHERTABLE.DateOfEntry=ZTABLE.DateOfEntry
        JOIN GetIndicator('ID#1_0#INDICATOR_W',@RefDate) AS WTABLE
            ON OTHERTABLE.SomeId=WTABLE.SomeId
            AND OTHERTABLE.DateOfEntry=WTABLE.DateOfEntry
        JOIN GetIndicator('ID#1_0#INDICATOR_A',@RefDate) AS ATABLE
            ON OTHERTABLE.SomeId=ATABLE.SomeId
            AND OTHERTABLE.DateOfEntry=ATABLE.DateOfEntry

Other details:

SQL server version: 2008 R2
If I execute the table function code outside the query, with the same args, the execution time is less the 1s.
Each table function call return between 250 and 500 rows.


texts:
I have some query with CXCONSUMER wait time when i ran sp_whoisactive as shown in the screenshot below.

When i checked the status it is suspended.That means it is waiting for some process to release resource.As far as my understanding CXPACKET is the producer and CXCONSUMER is the consumer and CXPACKET is the main culprit and action should be taken on that.But i don't see any CXPACKET.
Since this is a parallel execution i am not sure where i should start fixing.

Can anyone suggest me how to find out the process on which my queries are waiting(because i see suspended as status)?

Additional Info

When i query sys.dm_os_wait_stats ,the top two entries are CONSUMER and CXPACKETS.

SQL Server 2016
128 GB RAM
MAX DOP : 8
Cost of threshold : 20


texts: MySQL: Select a subset/range of a result

texts:
Assuming there's an application for running events (marathons etc.). An event (i.e. a run) has an ID and serveral segments, divided by checkpoints. So here's what we might get:

SELECT * FROM segments WHERE event = 88025 ORDER BY pos ASC

+--------+-------+-----+---------+------+------+
| id     | event | pos | viewers | from |   to |
+--------+-------+-----+---------+------+------+
| 397967 | 88025 |   1 |    3348 |  478 |  477 |
| 397968 | 88025 |   2 |   21130 |  477 |  481 |
| 397969 | 88025 |   3 |    9252 |  481 |  471 |
| 397970 | 88025 |   4 |   16183 |  471 |  470 |
| 397971 | 88025 |   5 |   14955 |  470 |  468 |
| 397972 | 88025 |   6 |   12824 |  468 |  467 |
+--------+-------+-----+---------+------+------+

from and to are checkpoint IDs, pos is just for sorting, viewers are the people watching the show on each segment.

(Please don't discuss the schema; it's just a made up scenario, the actual table has more values, and it does make sense. ;))

I now want to answer questions of the following type: What was the maximum number of viewers been between checkpoint 481 and checkpoint 467?

The correct answer is obviously 16183, but which is the best query to get this value?

I already thought of the following solution with two subqueries, but this smells like bad performance:

SELECT MAX(viewers) FROM segments WHERE pos >= (SELECT pos FROM segments WHERE event = 88025 AND from = 478) AND pos <= (SELECT pos FROM segments WHERE event = 88025 AND to = 467);

As this query is to be executed quite frequently, I'm curious if there's a better way. Any ideas?


texts: Dropping a table/column automatically drops views using it - how to warn instead?

texts:
If I have a view that uses a column, and I remove that column/table, the view is dropped automatically.

Is there a setting I can change such that it instead prevents the removal of the column/table, warning that it has dependencies?

Operation System: I5 or IBM i

Operating System Version: V7R1M0

  [From comment] The view is not dropped; it may be marked as invalid.

SELECT * FROM QSYS2.SYSTABLES
WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' and table_name = 'MYVIEW';
--returns 1 row

ALTER TABLE MYLIB.MYTABLE DROP COLUMN TEST2;

SELECT * FROM QSYS2.SYSTABLES
WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB' and table_name = 'MYVIEW';
--returns 0 rows

...That looks to me like it was dropped?


texts:
Trying to get logging/tracing of Sybase 9.0.2.3396 (Adaptive Server Anywhere). I wish to log all the SQL executed against the database.

I tried RIBO and while I got it "working," I never saw anything captured. Port 5003 for listen, server: localhost, and Server Port 2638.

I also tried ODBC tracing on WinXP but with limited success. I got some "connection information" but never any actual transaction tracing.


texts: restoring a table from sql file in postgresql through Windows Command prompt

texts:
Im trying to restore an sql file which contains the dumpf a table (with COPY commands) into a table into the database, it would be great if someone could share an example, as the pg_restore doesn't help much in this regard. A link would do as well.

Further information: Through the psql command line client I tried to first access the database and then execute the method presented by @a_horse _with_no_name; but nothing seemed to happen, it just went on to show the next prompt; but there was no change in the backend(data was not inserted into the database). I may be doing something grossly wrong here, just got started with PostgreSQL. :)


texts: How to Move TempDB Files to a Different Drive or Folder?

texts: How do I move my TempDB Data or Log File(s) from wherever they are now to a different Drive or Folder?


texts: Oracle: Not able to create tablespace from stored procedure

texts:
I am trying to create tablespace from stored procedure.

create or replace procedure testprc
is
query_string VARCHAR2(4000);
tbs_name VARCHAR2(20):='test77';
begin
dbms_output.put_line('test..');

query_string := 'CREATE BIGFILE TABLESPACE '|| tbs_name || ' DATAFILE '''|| tbs_name || '.dat'' SIZE 1G REUSE AUTOEXTEND ON ONLINE';

EXECUTE IMMEDIATE query_string;

end;
/

But when I execute the stored procedure,

execute testprc

I am getting the following error:

test..

  BEGIN testprc; END;

  ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYSTEM.TESTPRC", line 10 ORA-06512: at line 1

When I create the tablespace from sqlplus, it is created successfully. I have connected to sqlplus as system user.

Please help me in fixing this and your help will be valuable.


texts: mysqldbcompare SELECT command denied to user

texts:
I am trying to run mysqldbcompare to see the difference between two tables:

mysqldbcompare --server1=server1:2016@localhost --server2=server2:2016@localhost user:user --difftype=sql --run-all-tests

I get this error. I have already granted all privileges to users.

  ERROR: Query failed. 1142 (42000): SELECT command denied to user 'server1'@'localhost' for table 'proc'


texts: Can I execute an external process via MySQL on table change?

texts:
When using triggers, if an update is done to a table then a trigger is executed. This is very convenient. But, what I would need is to execute an external script.
Is it possible to configure MySQL so that on a trigger/change of a value in a table an external process/script is executed?


texts:
I have a bigquery table with following columns:
user_id, unique_id, start_timestamp(UTC), end_timestamp

unique_id is always unique, no repeating values. currently data is grouped by user_id. and then sorted by start_timestamp.

Example Input:

Desired Output:


texts: `uuid_generate_v1mc` in Postgres “uuid-ossp” module: What is random multicast MAC address?

texts:
The uuid-ossp module extension (plugin) for Postgres offers this alternative method for generating a UUID value.

uuid_generate_v1mc()
This function generates a version 1 UUID but uses a random multicast MAC address instead of the real MAC address of the computer.

I assume the intent here is to address a security concern about recording the MAC address of the database server. So instead we want to use another alternate MAC address in its place as a part of generating a Version 1 UUID value.
My question is: What exactly is this “random multicast MAC address” to be used in place of the db server’s own MAC?
I have googled/binged but found no good explanation. Is this some other MAC currently in use being found on the db server’s local network?
What are the practical issues involved in using this command, in the context of a primary key in Postgres?
Example data from calling uuid_generate_v1mc repeatedly, with UUID values in first column:
e2a03f96-0e7f-11ea-9838-6bba9e946aa0    Bird    2019-11-24 06:01:41.394401
e30c64be-0e7f-11ea-8540-9b38c7ef1573    Bird    2019-11-24 06:01:42.108340
e33037ea-0e7f-11ea-ab4b-abc4bdb5ea40    Bird    2019-11-24 06:01:42.342973
e3495cca-0e7f-11ea-8070-03c037680e82    Bird    2019-11-24 06:01:42.507537
e3668dd6-0e7f-11ea-b4e7-87c2c65a3777    Bird    2019-11-24 06:01:42.699283


texts: How to query time specific performance in postgres?

texts: The data in the pg_stat_statements table is really useful for me. I need to know the queries and the time they take. How can I perform a query to pg_stat_statements to get the "total_time" over the last 2 hours? I see no column for this.


texts: Select consecutive rows that are in a date range of each other

texts:
Having a list of associated dates & names and I want to select: all rows with names having multiple dates when the difference between their dates is more than 1 month.
e.g.: only the entries indicated below marked with \*this*\
CREATE TABLE IF NOT EXISTS myTab (
    id          SERIAL PRIMARY KEY,
    dateID      DATETIME DEFAULT 0,
    name        VARCHAR(512)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO myTab
    (dateID, name)
VALUES
    ("20140811","Emmy"),    /*this*/
    ("20140922","Emmy"),    /*this*/
    ("20150920","Emmy"),    /*this*/
    ("20150922","Emmy"),
    ("20140722","Dave"),
    ("20140613","Stan"),
    ("20140622","Stan"),    /*this*/
    ("20151020","Stan"),    /*this*/
    ("20140305","Lora"),
    ("20140310","Lora");

In other words the criteria is:

Partition by name
Order by date
Compare 2 consecutive rows: IF diff > 1 MONTH THEN select both, ELSE skip

Here's a working example as well as my attempt based on another answer on SO:
Rextester working example and attempt
Additional conditions/hints/...

Rows having the same name are not necessarily inserted once after the other making them spaced with +1 id from eachothers. Nor they are inserted oredered by date. In the example above it's done so just for readability. In my real problem it's not the case!
After applying your suggestions on real data I noticed an extra condition to be added tagged /*this_EXRA*/ in the example above. The 3rd stan row is in less than 1 MONTH from the 2nd but validates it with the 4th. Thus, it should only be selected if it validates them both. So I guess this implicates looping row by row and compare with previous and next one each time.


texts: Amazon: How to change database name in Amazon RDS MySQL?

texts:
I want to change MYSQL database name of the DB instance of Amazon RDS.

How it can be possible?


texts: Is there a system wide way to change MariaDB to create tables using ROW_FORMAT = DYNAMIC

texts:
I've got a 700MB dump file from a mysql database that I was trying to undump into a new install of MariaDB and I got this error

  ERROR 1118 (42000) at line 7481: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

I got most of the tables to go through, but the table that doesn't want to pump through is kind of a strange one.  It's got about 10 blob fields in it (none of which should be over 1000 bytes each because of what they're supposed to be used for in my program).  The only other datatypes we've got are VARCHAR, CHAR, SMALLINT and INTEGER.

What I want to achieve is an automated way to convert MySQL to MariaDB so my customers can do the same thing.  So, even if I fix this problem, I'd like to know exactly what the problem is and if I can fix it on the MariaDB installation or the MySQL dumping.

Our customers use MyISAM tables because of a few outlying cases we can work around (in case that makes a difference). Seems like MariaDB created all these tables as InnoDB tables.

The syntax I used for creating the dump file was just mysqldump.exe DatabaseName --compatible=mssql > DatabaseName.sql.  I don't think the mssql compatibility causes issues, it was just a holdover I found in the batch file.


texts: How to find unused columns in MariaDB tables

texts:
I have a lot of tables in my DB that have a number of columns which might not be getting used as they slowly lost their relevance with time, I need to find such columns and delete those from the tables, Is there a definitive way to check if a column is getting used or not?
MariaDB 10.3.8


texts:
One thing that has bothered me for awhile is creating many many indexes for the one table where the query only differs slightly or contains many of the same columns.

An example case would be:

table1
--------------
id
state
first_name
last_name
city
phone
cat_id

Now, imagine the below queries:

SELECT * FROM table1 WHERE state = 'WA' AND cat_id = 124;
SELECT * FROM table1 WHERE state = 'WA' AND cat_id = 124 AND first_name = 'bob';
SELECT * FROM table1 WHERE state = 'WA' AND cat_id = 124 AND phone = '12345' AND first_name = 'bob';
SELECT * FROM table1 WHERE city = 'seattle' AND last_name = 'jones' AND phone = '12345';
SELECT * FROM table1 WHERE city = 'seattle' AND cat_id = 124 AND phone = '12345' AND last_name = 'jones';
SELECT * FROM table1 WHERE city = 'seattle' AND cat_id = 124 AND phone = '12345' AND first_name = 'bob' AND last_name = 'jones';

Normally I would just create an index for each type, unless there are far too many combinations. However, this generally feels like I am just creating one big mess, and on large tables, substantially increasing the INSERT/UPDATE/DELETE times.

Is there a better method to handle things like this?

Note: I don't have a table like this and I am also aware of leftmost indexing, this is purely an example case.


texts: Linked Server to AD reporting error even with max permissions

texts:
I've created a linked server pointing our AD to create some reports.
the query to retrive the info is this one:
SELECT * FROM OpenQuery (
ADSI,
'SELECT
     mail
    ,mobile
    ,telephoneNumber
    ,title
    ,sn
    ,givenName
    ,SAMAccountName
FROM ''LDAP://MyDomain.local/OU=MyOU,DC=MyDomain,DC=LOCAL''
WHERE objectClass = ''user''
AND NOT objectClass = ''computer''
'
) AS tblADS
ORDER BY SAMAccountName

and Im receiving the famous error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.

Security tab has the most powerfull user we have, with access to everything. just as a try we gave max permissions to AD to the login running sql server. nothing can solve this issue.
I'm looking for answers for 2 days over the internet but all answers are like typos, or users with 0 permissions.


texts: Efficient way to move 170 GB table from production to another server

texts:
I have a 170 GB table that is no longer referenced.

I want to move that table to another server. I tried these options in staging:

select into 8 GB table to another db - 1.47 min
SSIS package - 6 min

Can anyone suggest best way to do it? How can I break table into parts so I can make small packages and upload it?


texts: MySQL8 crash recovery from IBD files with import tablespace

texts:
Our database MySQL8 crashed due to no space left on the data directory. After we arranged more space the database kept crashing with the error

  [ERROR] [MY-013183] [InnoDB] Assertion failure: log0recv.cc:1948:!page
  || (ibool) !!page_is_comp(page) == dict_table_is_comp(index->table)
  thread 139625720006400

I know that there a lot of threads with this kind of issue. Believe me, I tried everything I could find, otherwise, I would not have opened this one.

I was only able to start it with recovery mode 6, but after that, it still does not start without recover mode. After an internet search, I tried many solutions but nothing worked.

At this point, I gave up trying to recover the DB and created a new DB with the same my.cnf hoping to import data from IBD files. I only changed the data directory from my.cnf file. Data directory from crashed DB was "/var/lib/mysql/sas/" the new DB has "/var/lib/mysql/sas/data/data/", with this I can move IBD files immediately. I cannot dump tables and import them to the new DB because the tables are too big and too many. It would take one month at least.

What I did was;

create table on the new DB
discard tablespace and move the IBD file to the new DB data directory
import tablespace
I am getting this error

  Page offset does not match file offset: page offset: 262144, file
  offset: 262144

I can select the same table in the crashed DB opened with recovery mode 6. So I don't think that the IBD file is corrupt. Here I went out of ideas.

I am quite desperate at this point and any help would be appreciated. Thank you


texts:
We have a legacy SQL Server 2005 DB that is 8 TB in size. We will be purging data older than 5 years from this DB. After the purge process is complete, I want to be able to shrink the DB or reduce the file size of the DB.

I know shrinking (DBCC shrink..) is not a good option. Hence, was thinking of exporting all tables/objects/data via a SSIS package to a new DB so I can reclaim all the space. Is there any other way of reducing the size of mdf and ndf files?

Alternatively, I could try exporting all data to a new filegroup and deleting the original filegroup. However, most of the tables are heaps and don't have a clustered index. Is it possible to move all data/tables to another filegroup without a clustered index? Also, can I delete the primary filegroup if I move all the data to a different filegroup?


texts:
I have these three tables on my database:

There is a one-to-one relationship between Code and Aggregation table (an AggregationId is always a CodeId. And of course, a CodeId is unique). Note: A Code.CodeId doesn't need to be in Aggregation table.

There is a one-to-many relationship between Aggregation and AggregationChildren table (an aggregation will have one or more children).

I think I'm wasting a lot of space repeating a NVARCHAR(20) data in three tables Code.CodeId, Aggregation.AggregationId and AggregationChildren.AggregationId.

By the way, `AggregationChildren.

What do you think?

I think I can delete Aggregation and AggregationChildren tables.Then, add two new columns to Code table:
One to indicate to replace Aggregation.AggregationId.
Another one to replace AggregationChildren.Position.


texts: Is there a way to use sysconv() built-in function?

texts:
If you get the definition of some views using sys.sp_helptext:

exec sys.sp_helptext 'sys.columns'

CREATE VIEW sys.columns
AS
  SELECT c.id                                                             AS object_id,
         c.NAME,
         c.colid                                                          AS column_id,
         c.xtype                                                          AS system_type_id,
         c.utype                                                          AS user_type_id,
         c.length                                                         AS max_length,
         c.prec                                                           AS PRECISION,
         c.scale,
         CONVERT(SYSNAME, CollationPropertyFromId(c.collationid, 'name')) AS collation_name,
         sysconv(bit, 1 - ( c.status & 1 ))                               AS is_nullable,-- CPM_NOTNULL
         sysconv(bit, c.status & 2)                                       AS is_ansi_padded,-- CPM_NOTRIM
         sysconv(bit, c.status & 8)                                       AS is_rowguidcol,-- CPM_ROWGUIDCOL
         sysconv(bit, c.status & 4)                                       AS is_identity,-- CPM_IDENTCOL
         sysconv(bit, c.status & 16)                                      AS is_computed,-- CPM_COMPUTED
         sysconv(bit, c.status & 32)                                      AS is_filestream,-- CPM_FILESTREAM
         sysconv(bit, c.status & 0x020000)                                AS is_replicated,-- CPM_REPLICAT
         sysconv(bit, c.status & 0x040000)                                AS is_non_sql_subscribed,-- CPM_NONSQSSUB
         sysconv(bit, c.status & 0x080000)                                AS is_merge_published,-- CPM_MERGEREPL
         sysconv(bit, c.status & 0x100000)                                AS is_dts_replicated,-- CPM_REPLDTS
         sysconv(bit, c.status & 2048)                                    AS is_xml_document,-- CPM_XML_DOC
         c.xmlns                                                          AS xml_collection_id,
         c.dflt                                                           AS default_object_id,
         c.chk                                                            AS rule_object_id,
         sysconv(bit, c.status & 0x1000000)                               AS is_sparse,-- CPM_SPARSE
         sysconv(bit, c.status & 0x2000000)                               AS is_column_set -- CPM_SPARSECOLUMNSET
  FROM   sys.syscolpars c
  WHERE  number = 0
         AND has_access('CO', c.id) = 1

There is a function called sysconv.

The function 'sysconv' is used by these views, and you can select values from this view without getting any error message.

select * from sys.columns;

But if I try something similar to:

select sysconv(bit, 375 & 8);

I receive the error:

  sysconv is not a recognized built-in function name

As Martin Smith pointed out, this function can be replaced by convert (bit, 375 & 8), but I wonder why I can select values from this view and I can't use it in my queries, even when SSMS recognizes it as as command, by changing the foreground color?


texts: GHOST_CLEANUP not freeing up space on SQL Server Availability Group

texts:
Had a problem in my SQL Server Cluster for 2 weeks, I had the GHOST_CLEANUP process running 24/7 never stopping even if the users were doing nothing.

I was checking the "Free Space" parameter in the Database and always was at the same value (Around 1GB).

I did a backup of the database and restored in the same server with the same name to see if running sp_clean_db_free_space would free up the space that GHOST_CLEANUP was not able to. After 2 hours...TADA!...the space was cleaned and I had 80GB free from 100GB Database.

So I decided to run sp_clean_db_free_space stored procedure on the original database to see what happens. After almost 30m it finished not freeing any space. The only different thing was that the original database was synched under an Availability Group. I disconnected the database from the Availability Group and now I can see that the GHOST_CLEANUP task is slowly freeing space.

I will assume that the problem was the Availability Group, but does anyone know WHY!?

I can confirm no blocking on primary by read operations on secondary because the secondary node is not even used for read only operations as the application doesn't allow to configure this. There are no users connected to the secondary node.

Today, after checking I found that each 5 seconds SQL Server jumped from 0-1% CPU to 10-15% CPU then dropped again to 0-1% CPU after 1 second. No I/0 usage, only CPU. Decided to disconnect all my Availability Replicas, after 1 minute, SQL Server dropped to a stable 0-1% CPU, connected the replicas again and remains at 0-1%. I have a problem with this GHOST_CLEANUP and I don't know how to solve it.

I am using Microsoft SQL Server 2012 (SP3-CU1) (KB3123299) - 11.0.6518.0 (X64).