texts: How to create tablespace if it does not exist

texts:
When I create a new schema in Postgres through executing a script in psql I want to put it in a tablespace. If this tablespace does not exist then I want to create it first. Since the default SQL does not have this option I created a function:

CREATE OR REPLACE FUNCTION make_tablespace(tablespace CHARACTER,
                                           directory CHARACTER,
                                           owner CHARACTER)
  RETURNS void
AS
$$
BEGIN
  IF tablespace = '' THEN
    RAISE EXCEPTION 'No tablespace.';
  END IF;
  PERFORM SPCNAME FROM PG_TABLESPACE WHERE SPCNAME=tablespace;
  IF NOT FOUND THEN
    IF directory = '' THEN
      RAISE EXCEPTION 'No directory.';
    END IF;
    IF owner = '' THEN
      RAISE EXCEPTION 'No owner.';
    END IF;
    EXECUTE 'CREATE TABLESPACE '||tablespace||' OWNER '||owner||' LOCATION '''||directory||''';';
    RAISE NOTICE 'Tablespace % created.', tablespace;
  ELSE
    RAISE NOTICE 'Tablespace % already exists.', tablespace;
  END IF;
END $$ LANGUAGE plpgsql;

Unfortunately when I execute it (select make_tablespace('marco', '/opt/marco', 'marco');) this gives an error:

  ERROR:  CREATE TABLESPACE cannot be executed from a function or multi-command string

I searched the internet and there seems to be a workaround (some years ago) by using the dblink package. I do not want to install this. Is there today another way? I can return the SQL statement as a string but how to execute it then?


texts: Which is better: many join conditions or many where conditions?

texts:
I am trying to compare two queries:

Query 1:

SELECT a,b,c,d,e
FROM tableA
LEFT JOIN tableB
ON tableA.a=tableB.a
WHERE tableA.b=tableB.b AND tableA.c=tableB.c  AND tableA.d=tableB.d  AND tableA.e=tableB.e

Query 2:

SELECT a,b,c,d,e
FROM tableA
LEFT JOIN tableB
ON tableA.a=tableB.a AND tableA.b=tableB.b AND tableA.c=tableB.c  AND tableA.d=tableB.d
WHERE tableA.e=tableB.e

Am I right to say that the these two queries give the same results?

Further, is it correct to say that the first query builds a bigger table for which to do a bigger WHERE condition; whereas the second case we have a smaller constructed table to which the simple WHERE is then applied.

Assuming the results are the same, which query should be preferred? Is there an obvious performance issue?


texts: How would I implement multiple comboboxes in an MS access (2013) form that references a single column in a table or query?

texts:
So my goal is to have two fields, one with a dropdown for "inventory", and another that autopopulates with "quantity".
I was able to achieve that with a single pair by linking to a query. However, i would like to do that multiple times. As in, have multiple lines of an "inventory" and "quantity". When I tried that, each "quantity" box would update to the most recent "inventory" change, and I would like that to update only to the corresponding field.

Then i tried using dlookup, however I keep getting errors with my code.
I tried inputting an expression in the field directly, via control source.
=DLookUp("[test2]","[test]","[test1]= 3")
I just keep getting either #error, or #type.
Any ideas? Or even templates?


texts: Postgres - Partitioning old tables, partition query planning, optimisation

texts:
I've been trying to optimise our database, but not being a DBA and not having done a whole lot of tuning before I'm struggling a little with "too many options".

I've already run pg_tune and bumped a few of the memory options, and that gave a good performance increase. The problematic table is >250m rows at the moment, essentially consists of a timestamp, value, fk id row and has indexes on the (timestamp, fk id) and (fk id, timestamp) as well as the row id.

The "problematic" part comes when requesting say, a year's worth of data over more than a few of the fk ids (for example, a year's worth over a range of 150 fk ids)... queries run to 10 minutes.

I've come up with a pretty reasonable (it seems) partition function that splits data into tables based on the id of a table to which they are foreign keyed (so basically, these are many many thousands of rows per fk id per day, or maybe only up to a hundred per day, but they all end up in their own child table).

Then, the check constraints are just "is the id of this fk = to xxx".

This seemed like a reasonably good idea, because we often select data based on the foreign key id.

Now - I realised also that old data will still be in the master table, and couldn't see anything obvious in the docs about how to solve that (i.e. insert all pre-existing rows into child partitions), so I have also added a check constraint that says "and timestamp is > timestamp-at-which-child-table-was-made".

And then also noticed the caveat:

  Partitioning using these techniques will work well with up to perhaps a hundred partitions; don't try to use many thousands of partitions.

Besides this caveat being a bit vague "perhaps a hundred" and "not many thousands" - it seems like maybe my partitioning method is not ideal anyway since we may have thousands of rows in the foreign-keyed table, and hence many thousands of partitions (but then, how long does it really take to scan thousands of equality conditions?).

So it's starting to feel a little bit crazy, and I just wanted to sanity check.

The questions, then:

is it really ok to have thousands of partition tables, given very simple equality constraints (I intend to get rid of the timestamp check, so would only be based on fk id)?
how would I best go about moving data from the master table to the relevant child tables?
is this a sensible optimisation to make in the first place?

Hope someone can constrain my options somehow!


texts: Difference size DBCC Page and DBCC Fileheader

texts:
I'm trying to read the current database size using dbcc command.
In my test I execute following commands:

DBCC Fileheader('tempdb',1) => size = 988152
DBCC Page('tempdb',1,0,3) => size = 5112

When I check tempdb initial size with the user interface I get 7.720MB (= 988152 * 8 / 1024).
How come there is a difference between the size found with dbcc fileheader and dbcc page?

Update:

Output from DBCC FileHeader can be found here, the output from DBCC Page can be found here

Update:

Yesterday I restarted the server but the difference is still present.
The size with dbcc page is now 0.

  BindingID = 2c0a5351-ab60-11d2-896a-00c04fd9374a
  FileIdProp = 1 FileGroupId = 1                     Size = 0
  MaxSize = 65535 Growth = 32768                      Perf = 0
  BackupLsn = (0:0:0) FirstUpdateLsn = (0:0:0)
  OldestRestoredLsn = (0:0:0)         FirstNonloggedUpdateLsn = [NULL]
  MinSize = 0                         Status = 0
  UserShrinkSize = 65535 SectorSize = 512                    MaxLsn =
  (0:0:0)                    FirstLsn = (0:0:0) CreateLsn = (0:0:0)
  DifferentialBaseLsn = (0:0:0)        DifferentialBaseGuid =
  00000000-0000-0000-0000-000000000000              FileOfflineLsn =
  (0:0:0) FileIdGuid = b0c06a0f-4ee5-43aa-92be-8f03dee24690
  RestoreStatus = 0 RestoreRedoStartLsn = (0:0:0)
  RestoreSourceGuid = 00000000-0000-0000-0000-000000000000
  HardenedSkipLsn = [NULL]            ReplTxfTruncationLsn = [NULL]
  TxfBackupLsn = [NULL] FstrContainerSize = [NULL]
  MaxLsnBranchId = 00000000-0000-0000-0000-000000000000
  SecondaryRedoStartLsn = [NULL]      SecondaryDifferentialBaseLsn =
  [NULL] ReadOnlyLsn = (0:0:0)               ReadWriteLsn = (0:0:0)
  RestoreDifferentialBaseLsn = (0:0:0) RestoreDifferentialBaseGuid =
  00000000-0000-0000-0000-000000000000        RestorePathOrigin

DBCC Fileheader gives: minsize=131072, size=131072 and maxsize=-1


texts:
I was doing an audit of certificates in our environments to find those that have either expired or are expiring in 30 days. I used the following query:
SELECT  *
FROM    sys.certificates
WHERE   expiry_date < DATEADD(DAY,30,GETDATE());

I noticed in SSISDB that there are a large number of expired certificates:

What are these certificates used for, is it an issue that they have expired? I have not heard any complaints from our development team that SSIS isn't working as expected.


texts: periodic data move from local server to remote server

texts:
I have a local PostgreSQL server that is recording data on a solid state drive of less than 8 GB free space on a Windows 7 embedded 32 bit machine. In order to make a backup and store large data I need to setup a procedure to move my data at predefined intervals to a remote PostgreSQL server. I need to create a procedure to

connect
copy data to remote
confirm the copy
delete local
and then wait for next hour

Can someone guide me with the procedure? I am new to DB and server management. So would appreciate simple steps to achieve this.

Both servers run PostgreSQL, the DBs have the same table structures.


texts: SQL Server - How to track down error thrown in SSDT publish preview?

texts:
Without flailing through a file search, how does one track down the code that caused an error during SSDT publish preview?

Normally any errors come later such that the statement can be traced in the publish preview, but when the error comes in the preview I see no clear way to find what caused it.

I manually tracked it down this time (to a valid statement!):

(The error about I.RowNum is the same thing)

Update: The faulty code was in a stored procedure already existing on the server. Even though I removed it from my database project it still failed the publish preview, so I had to delete it from the server to make the publish work.  So a file search didn't even work to find the code it flagged.

Update 2: The error isn't actually failing the publish preview (at least by itself), it is just shown only when the step fails (I have since seen it appear again). The actual error is still unknown as the only other messages were data loss warnings that the advanced settings were set to allow. I am now confused more than ever.

Probably related: Update to SSDT started throwing ambiguous column name errors on valid SQL

Also related: Unaliased ORDER BY columns.

So I'm guessing this is actually a bug in SSDT at this point. Can anybody confirm? I'd still like to know how to track down the buggy code when it is on the server and fails the publish preview.

Thanks.


texts: Mysql performance for a nullable unique id column in a large table that references a record in another table

texts:
We have a large table of contacts (8 million records) that we promote to with mailings. When these people register we store them in a different members table. But we want to go back and update the contacts table each day with the members table ID for each conversion. I added a member_id column to the contacts table but I can't really use a member_id foreign key on that column as most will have a null value.

I'm trying to determine what the best type of index might be used on the member_id column to speed up queries on those people. Currently i have the column nullable with a unique index but queries on those people are taking several minutes which isn't very performant. Wondering if some more experienced people out there have dealt with this type of issue?


texts: Oracle 12cR2 - Image Copy Backup Scenario

texts:
Backup type: Image copy.
Scenario: I want to 1 full db + 6 incremental backup and keep last 7 days backup on storage.
Here is my script:
RUN {

allocate channel c1 device type disk format '/path/%U';

allocate channel c2 device type disk format '/path/%U';

allocate channel c3 device type disk format '/path/%U';

allocate channel c4 device type disk format '/path/%U';

allocate channel c5 device type disk format '/path/%U';

allocate channel c6 device type disk format '/path/%U';

allocate channel c7 device type disk format '/path/%U';

allocate channel c8 device type disk format '/path/%U';

CROSSCHECK ARCHIVELOG ALL;

CROSSCHECK BACKUP;

CROSSCHECK BACKUPSET;

CROSSCHECK COPY;

DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

DELETE NOPROMPT EXPIRED BACKUP;

DELETE NOPROMPT EXPIRED BACKUPSET;

DELETE NOPROMPT EXPIRED COPY;

BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'my_tag_01' DATABASE;

RECOVER COPY OF DATABASE WITH TAG 'my_tag_01';

DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-3';

BACKUP SPFILE FORMAT '/path/spfile_%T_%U';

BACKUP CURRENT CONTROLFILE FORMAT '/path/cfile_%T_%U';

}

exit

How can I design my script according my scenario?
Thank you so much.
Best regards,


texts: What does Error: 1204, Severity: 19, State: 4 mean?

texts:
I've found this error in our SQL log:

  "The instance of the SQL Server Database Engine cannot obtain a LOCK
  resource at this time. Rerun your statement when there are fewer
  active users. Ask the database administrator to check the lock and
  memory configuration for this instance, or to check for long-running
  transactions. Error: 1204, Severity: 19, State: 4."

From last 5 days this exception is coming in our staging SQL Server. There is no other process running still it is publishing error every night.

Select @@Version

Microsoft SQL Server 2014 - 12.0.4213.0 (X64) Jun 9 2015 12:06:16 Copyright
Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Does anyone know how to fix this? How to diagnose such error and find who is real culprit for it.


texts: MySQL Table Schema Design - Relationship with Junction or User Table

texts:
I am trying to determine the best approach to create a relationship between my notifications table and tables with user information within my current db architecture. The purpose of the notifications table is to allow users to send a notification (to request information) to other users within a specific organization and I'm not sure the ideal relationship and tables that should be involved. Right now my thought is to join on my member table, which is a junction between user and organization and have that member_id as the fk. However, I don't know if this will be beneficial to the information I am really trying to receive with the queries I plan to run. Here are the high level queries I plan on running:

query 1: query all users (with id, first_name, last_name, email) that are within the same organization as the logged in user

query 2: create a record for a notification and pass in the requester user_id (logged in user) and receiver user_id (user within organization being sent a request)

query 3: query current notifications (with first_name, last_name, email)

Here are the current tables in play:

User:

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `organization_id` int(11) DEFAULT NULL,
  `authentication_token` varchar(255) DEFAULT NULL,
  `reset_password_token` varchar(255) DEFAULT NULL,
  `reset_password_expires` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email` (`email`),
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Organization:

CREATE TABLE `organization` (
  `organization_id` int(11) NOT NULL AUTO_INCREMENT,
  `organization_name` varchar(255) DEFAULT NULL,
  `admin` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`organization_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Member (Junction Table between User and Organization):

CREATE TABLE `member` (
  `member_id` int(11) NOT NULL AUTO_INCREMENT,
  `member_email` varchar(255) DEFAULT NULL,
  `organization_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `team_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`member_id`),
  UNIQUE KEY `member_email` (`member_email`),
  UNIQUE KEY `member_user_id_organization_id_unique` (`organization_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `member_ibfk_1` FOREIGN KEY (`organization_id`) REFERENCES `organization` (`organization_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `member_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Notification Table (Currently a One-to-Many on the Member table):

CREATE TABLE `notification` (
  `notification_id` int(11) NOT NULL AUTO_INCREMENT,
  `requester` int(11) DEFAULT NULL,
  `receiver` int(11) DEFAULT NULL,
  `message` text,
  `status` varchar(255) DEFAULT NULL,
  `archived` tinyint(1) DEFAULT NULL,
  `member_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`notification_id`),
  KEY `member_id` (`member_id`),
  CONSTRAINT `notification_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `member` (`member_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


texts: Worked example of closure tables with from and thru dates

texts:
I work for a law enforcement agency, and have been asked to create a way of storing the chain of command in such a way that its structure can be viewed at any point in time, not only as it is now.

After trying several different approaches, such as adjacency list and nested sets, it seems that using closure tables would work best for this, since it allows other attributes of the relationship between two entities to be saved with the relationship. In my case, this includes the date the relationship started (FromDate) and the date it ended (ThruDate).

I have searched for a worked example of closure tables with additional attributes like dates with no success so far.

Does anyone know of a worked example of this design? I've used several things by Bill Karwin to help get started (primarily his book "SQL Antipatterns"), but they don't go far enough.

Any advice, links, or hints will be appreciated.


texts: enq: TX - row lock contention creating session waits

texts:
The database I'm working on has session waits due to DMLs on numerous tables in a procedure.

The DBA identifies this as enq: TX - row lock contention.

How can I resolve this issue?


texts:
i was trying to use TFS 2010 in SSMS 2012 and, even if i installed Team Foundation Server MSSCCI Provider 2012 64-bit and Visual Studio 2010 Team Explorer, there's no way  to see TFS under the Current source control plug-in of SSMS.

What can i do to solve this problem?

Thanks in advance

Simone


texts: Why is the page life expectancy 0 (zero) on SQL Server 2005

texts:
We have been wanting to know the page life expectancy for our SQL Server. So we look at the counter using Perform. The value is ZERO and never changes. I thought there must be some bug, so I checked the SQL Server DMV query

SELECT [object_name],[counter_name],[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

That also returns zero, all day long.

To make this more interesting, we checked the 'buffer cache hit ratio' counter, with it averaging around 99-100.

So how is the page life expectancy zero while the 'buffer cache hit ratio' is 100?

What are we missing? if it's always zero, it would mean to me that nothing stays in the buffer cache, which seems incorrect if the buffer cache hit ratio is 100?

Thanks in advance


texts:
I am trying to build a platform to share the empty desks in an office with someone outside the premises to utilize the space and generate some revenue. I am thinking of three major roles:

Member who will book a desk/seat(resources) for any period of time be it hours/days/weeks/months.
Member who will list empty spaces in his/her office.
Admin who will verify the listed spaces. Only verified spaces can be booked and other Admin related tasks such as managing users/resources etc.

I am trying to build this on MongoDB and want to focus on scalability.


texts: How to make distributor highly available in Always On Configuration

texts:
We have clients requested us to configure Always on with Replication. Microsoft does not support it. "The distributor should not be a host for any of the current (or intended) replicas of the availability group that the publishing database is (or will become) a member of. " We do not want distributor to be on the same server as Publisher.
Has anyone faced this change? Do you know the limitation for not having distributor as replica of the availability group. What other options do you recommend to have high availability for distributor in transactional replication in Always on Configuration.
Thanks


texts: Receiving historical SQL alerts when SQL Server agent is restarted

texts:
The issue is faced in SQL server 2014, whenever SQL Server Agent is restarted or if the server reboots, there are historical SQL alerts which are received and emails are being sent to the various DLs.

The SQL alerts are of the various errors received and alerts configured for the same. They are as old as from 2014.

I’ve troubleshoot the issue further and found that the most old historical alert we received is for 8/28/2014 9:16:36 AM. Checking the SQL error logs, the last date/time stamp of event is 11/30/2014 12:00:01 AM, and checking the Windows Application events logs we can see that the last event is of 8/27/2014 3:00:51 PM. Hence based on the information at the Microsoft site (SQL Server Agent reads the application log and compares events written there to alerts that you have defined. When SQL Server Agent finds a match, it fires an alert, which is an automated response to an event.) I’m assuming that in our case as well the historical alerts are being fired as there are these events in the Windows Events logs.

However, I still trying to figure why alerts of this particular day are being sent. As in, we are getting a historical alert for failed backup of ‘******’ on 9/14/2014 3:11:50 PM and the same entry is present in the Windows Application Event logs as well, the same event has occurred on 10/5/2014 3:14:34 PM and we are not receiving the historical alert for this event.

I’ve checked the table ‘sysmail_allitems’ and other related tables in msdb database and found that there are logs of the emails triggered in 2013 also but we are not receiving historical alerts of those events, hence there is a possibility that we are receiving historical alerts as those events present in the Windows Application Event logs.

I have also checked the mail queue and found them to be empty.

  EXEC msdb.dbo.sysmail_help_queue_sp

Please let me know if i'm troubleshooting in the wrong direction. Also let me know if you need any more information.


texts: update column based on the sort order of another query

texts: I'm trying to add arbitrarily ordered records to a database so that they can be sorted by the ui (or when I query the database ). My problem is I already have the list, and I need to add a default sorting based on alphabetical order. I'm thinking I should be able to do this with a subquery or coalesce, but I can't get it quire right. I'm doing this on MySQL so I'm hoping it's possible to do it at the database level.


texts: Restrict root user access to postgres user

texts:
I know this is a little weird but bear with me.

If a hacker gains access to a server's root user, he can type "su postgres" and then "psql" and boom - instant access to everything.

I was wondering if there was any possible way to block the root user from switching to the postgres user. In other words, I want to force a postgres-specific password to be used before postgres access is granted.

Is there any way to do this? Or is the root user always God?


texts:
I have a database with the following:

|Customer number| |Sales date| |Cancel date|

From this I want to count the number of active customers each year for lets say 10 years back.
If I want to do this for a single year I can do this (I think):

SELECT
    customernumber
FROM
    customers
WHERE
    Datepart(year, salesdate)<2015
    AND (Datepart(year, CancelDate)>2015 or Datepart(year, CancelDate) IS NULL)

But how do I do this for all years available?


texts:
Error:

2020-04-25 05:42:49.617 EDT [11233] ERROR:  could not map dynamic shared memory segment
2020-04-25 05:42:49.617 EDT [11234] ERROR:  could not map dynamic shared memory segment
2020-04-25 05:42:49.628 EDT [3938] someuser@somedb ERROR:  too many dynamic shared memory segments

This dynamic shared memory error has started to show in the log.

Should I need to reduce work_mem?

PostgreSQL version: postgres (PostgreSQL) 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)


texts: What's the approach to figure out reason for mysql crash?

texts:
The database I'm using crashes a lot recently. And I can't find a decent way to dig into the issue. I looked into mysql error log, but don't know how to start. I know there was a "load data infile" query running when it crashes. Sometimes the table is corrupted so I have to drop it and rebuild, sometimes the table is just fine (both cases are when the "load data infile" is running).

This happened many times and I've recovered the database several times. But it keeps happening and I want to find out the reason and resolve the cause. I've follow the recovery link, and I've tried to search around the failure message. But couldn't figure it out..

So what will you do to find out the crash cause? Is there a guideline? Really appreciate it.

  2014-12-08 11:01:52 30924 [Note] InnoDB: Resuming purge

  2014-12-08 11:59:10 7f945e0e3700  InnoDB: Assertion failure in thread 140275209877248 in file buf0buf.cc line 3687

  InnoDB: Failing assertion: buf_fix_count > 0

  InnoDB: We intentionally generate a memory trap.

  InnoDB: Submit a detailed bug report to (a link was here, I don't have enough reputation to post links yet).

  InnoDB: If you get repeated assertion failures or crashes, even

  InnoDB: immediately after the mysqld startup, there may be

  InnoDB: corruption in the InnoDB tablespace. Please refer to (a link was here, I don't have enough reputation to post links yet)

  InnoDB: about forcing recovery.

  19:59:10 UTC - mysqld got signal 6 ;

  This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Please help us make Percona Server better by reporting any bugs at percona bugs webiste (a link was here, I don't have enough reputation to post links yet)

  key_buffer_size=33554432

  read_buffer_size=131072

  max_used_connections=6

  max_threads=1002

  thread_count=3

  connection_count=3

  It is possible that mysqld could use up to

  key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 432019 K  bytes of memory

  Hope that's ok; if not, decrease some variables in the equation.

  Thread pointer: 0x0

  Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong...

  stack_bottom = 0 thread_stack 0x40000

  /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x8c821e]

  /usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x671f44]

  /lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7f98c3646cb0]

  /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x35)[0x7f98c2a9c425]

  /lib/x86_64-linux-gnu/libc.so.6(abort+0x17b)[0x7f98c2a9fb8b]

  /usr/sbin/mysqld[0xa5e1c8]

  /usr/sbin/mysqld[0xa754ce]

  /usr/sbin/mysqld[0xa766b3]

  /usr/sbin/mysqld[0xa5a727]

  /usr/sbin/mysqld[0xa3de9c]

  /usr/sbin/mysqld[0x9e384d]

  /usr/sbin/mysqld[0x9e0b7d]

  /usr/sbin/mysqld[0x9e1ecc]

  /usr/sbin/mysqld[0x9b299c]

  /usr/sbin/mysqld[0xa09f26]

  /usr/sbin/mysqld[0x9fc62c]

  /lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7f98c363ee9a]

  /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f98c2b59ccd]

  You may download the Percona Server operations manual by visiting (a link was here, I don't have enough reputation to post links yet). You may find information in the manual which will help you identify the cause of the crash.

  141208 11:59:14 mysqld_safe Number of processes running now: 0

  141208 11:59:14 mysqld_safe mysqld restarted


texts: How to detect if insert transactions in oracle db are slow?

texts:
At work, I have an Oracle DB (11g) in which I want to detect if there's slow performance while inserting data.
Here's the situation:

Some production devices send data results from tests to Server A, this server is a important server and it replicates the info to server B, which is the one that processes the data (files) to then insert it into the database with the problem, the database is in another server (Server C).

Now, the problem that I'm facing is that the data in server B is getting stuck there, a queue starts to form and it keeps growing and growing. Apparently the insertion is to slow, to give you an idea, in the Server A (which inserts data in a DB also in server A) the time to insert a single set of data goes from 133 ms to (very rarely) 2300 ms; while in server B it goes from 171 to 11900 (most likely to be above 2 seconds).

As you can see there're a lot of factors here, the capacity between Server A and Server B are also different, Server A has 96 GB of memory and server B has 32; Server A 24 CPU's, GenuineIntel, CPU MHz 1596; and Server B 16 CPU's, AutenticAMD, CPU MHz 3200.
I think it could be a problem of capacity in Server B; but I'd like to rule out a problem in the database; that's why I would like to find out if the insertion of data in the DB is really slow, because when my applications connect to it to read data, it does really okay, it's not slow.
Also, server B is the only one that inserts data into the DB in server C. I have no access to the insides of the applications that processes the data in Server B, and unfortunately there's almost not documentation to read.
Now, I'm not DBA, but I'd like to find a way to spy on the inserts and find out how long are they taking to finish.
Anybody know how I could achieve this?

Thank you!!


texts: Is it bad to have index space larger than data space?

texts:
Often I need to run queries against large tables that don't have the right index. So I ask the DBA to create such index. The first thing he does is look at the table statistics and see the index space size.

Often he would tell me to find an alternative solution because "the index is already larger than the table". He feels the index has to be smaller than the data, because, he told me "have you ever seen the index in a book? It's much smaller than the book itself, and that's how a table index should be".

I don't feel his philosophy is correct, but I can't challenge him because he's a lead DBA and I'm a developer. I feel if a query needs an index, the index should just be created, instead of finding "workarounds" that just make unreadable and unmaintainable SPs.

I'm selecting only the required columns. The problem is I'm filtering by date so the engine will necessarily do a table scan to match the columns. The query runs once a day, at night, to gather statistics, but it takes 15 minutes to run (we have another hard and fast rule: No procedure should take over 3 minutes).

The DBA showed me the index statistics. There were about 10 indexes on that table, of which only 6 were used (stats showed zero hits to 4 of them).  This is a large system with over 20 developers participating. The indexes were created for whatever reason, and probably no longer used.

We are required to support SQL Server 2008, since that's what the testing DBs run on. But the clients are all on 2014 and 2016.


texts: Postgresql query plan differs with limit value making the query slower for lower limits

texts:
I have this query:

SELECT  "table_1".* FROM "table_1"
WHERE "table_1"."deleted_at" IS NULL
  AND "table_1"."table_2_id" = 15797104
ORDER BY "table_1"."id" ASC LIMIT 1

The explain for the query looks like:

Limit  (cost=0.57..1208.61 rows=1 width=48)
    ->  Index Scan using table_1_pkey on table_1  (cost=0.57..46047923.71 rows=38118 width=48)
            Filter: ((deleted_at IS NULL) AND (table_2_id = 15797104))

This is incredibly slow as this table is absolutely huge.

Now, if I increase the LIMIT to 10:

SELECT  "table_1".* FROM "table_1"
WHERE "table_1"."deleted_at" IS NULL
  AND "table_1"."table_2_id" = 15797104
ORDER BY "table_1"."id" ASC LIMIT 10

This query runs a lot faster (from over 30minutes to less than a second) and the query plan looks like this:

Limit  (cost=11044.69..11044.71 rows=10 width=48)
    ->  Sort  (cost=11044.69..11139.98 rows=38118 width=48)"
        Sort Key: id
        ->  Index Scan using index_table_1_on_table_2_id on table_1  (cost=0.57..10220.97 rows=38118 width=48)
              Index Cond: (table_2_id = 15797104)
              Filter: (deleted_at IS NULL)

Note that instead of using the table_1_pkey index, it uses index_table_1_on_table_2_id. This makes much more sense. I imagine Postgresql is using the table_1_pkey to avoid doing a sort operation, but that actually ends up being a lot slower.

SELECT  count("table_1".*) FROM "table_1"
WHERE "table_1"."deleted_at" IS NULL
  AND "table_1"."table_2_id" = 15797104

returns 5000. And the whole table_1 table contains over 200M rows.

Is there a way to trick Posrgres into using the second query plan on the first query?


texts: Is there a way to limit the memory buffer pool for a database?

texts:
We have several databases on an instance of MS SQL Server 2012. Using Glenn Berry's DMV queries, I see the memory buffer pool used by each database.

Is there a way I can limit AltDB to only use a maximum of 10GB. My goal is that more memory is used by MainDB.

DB     | Memory Buffer Pool in GB
MainDB | 34.00
AltDB  | 24.00


texts: I want to design a forum script with MySql. Which database engine would have better performance? MyISAM or InnoDB ?


texts: Looping through string, adding all numbers e.g. '123' = 1+2+3, demo with working loop included

texts:
This works to output the string 123456 as:

1
2
3
4
5
6

Code:

declare @string varchar(20)
declare @index int
declare @len int
declare @char char(1)

set @string = '123456'
set @index = 1
set @len= LEN(@string)

WHILE @index<= @len
BEGIN
set @char = SUBSTRING(@string, @index, 1)
print @char

SET @index= @index+ 1
END

But when I try to use this code to add those values up when looping through them (1+2+3+4+5+6 = 21), then I do no get a result from SQL, please help

declare @string varchar(20)
declare @index int
declare @len int
declare @char char(1)

set @string = '123456'
set @index = 1
set @len= LEN(@string)

declare @Total int

WHILE @index<= @len
BEGIN
set @char = SUBSTRING(@string, @index, 1)
set @Total = @Total + cast(@char as int)

SET @index= @index+ 1
END

print @Total


texts:
It is my first question in this forum.
I wrote this code, but it is incorrect and I can't find a problem.
I have 2 tables: sciana (gid,id,geom) and sciana1(gid,id,geom).
If table sciana is empty I want to sciana.id = sciana.gid, but if sciana is not null I want to find max(sciana.id) + sciana1.gid

UPDATE sciana1
SET id =
CASE
WHEN sciana.gid IS NULL THEN
    id = sciana1.gid
WHEN sciana.gid IS NOT NULL THEN
    (SELECT max(sciana.id)+sciana1.gid
    FROM sciana )
END;

First example:

`sciana` IS EMPTY, so after the update `sciana1` looks like

         1,1,geom1
         2,2,geom2
         ...

Second example:

sciana IS NOT EMPTY

         1,1,geom1,
         ...
         9,9,geom9

so after the update, sciana1 contains

         1,10,geom10
         2,11,geom11
         ...


texts: Grafana - combine multiple SELECT from different measurments

texts:
I'm totally new here, I tried to find solution and I found this:
Influxdb and Grafana combine multiple SELECT

but this works fine if we have different fields from the same measurement, but this does not work (for me) when adding values from different measurements. What am I doing wrong here? Could you please take a look into this? Updated query - with proper interval - still does not work.

SELECT ( sum("valFromA") + sum("valFromB") )
AS "addedVal"
FROM (
    SELECT mean("valueA") AS "valFromA"
    FROM "DSA"
    WHERE "hostname" = 'host' AND $timeFilter GROUP BY time(15m) fill(null)
),
(
    SELECT mean("valueB") AS "valFromB"
    FROM "DSB"
    WHERE "hostname" = 'host' AND $timeFilter GROUP BY time(15m) fill(null)
)
GROUP BY time(15m) fill(null)

Nothing is drawn in this case.  This also show 2 counters under graph
DSA.addedval
DSB.addedval

BTW - when i split this queries to separate ones - it returns data. Combined as above shows empty graph. e.g.

Single select is ok:

SELECT ( sum("valFromA") )
AS "addedVal"
FROM (
     SELECT mean("valueA") AS "valFromA"
     FROM "DSA"
     WHERE "hostname" = 'host' AND $timeFilter GROUP BY time(15m) fill(null)
)
GROUP BY time(15m) fill(null)

Grafana 6.4.4 + Influx 5.1.0
Do I need to do something in Influx for this? I'm asking because I've access to grafana only, but I may ask our administrators.

UPDATE: I really tried to put this into proposed "select" by Akina, but I didn't succeed. I don't want to paste here my trials, because grafana does not understand clause UNION ALL. So maybe it is possible but in influx not directly from visualization (graph) query definition.

This is what I'm trying to do:

SELECT ( (sum("opt12") / sum("dst45")) * 100 )
AS "Ratio"
FROM (
    SELECT mean("ratioopt12") AS "opt12"
    FROM "ottb.gen.11"
    WHERE "hostname" = "serv1"  AND $timeFilter GROUP BY time(15m) fill(null)
),
(
    SELECT mean("ratiodst45") AS "dst45"
    FROM "ottb.gen.14"
    WHERE "hostname" = "serv1" AND $timeFilter GROUP BY time(15m) fill(null)
)
GROUP BY time(15m) fill(null)

Really have no idea what should I replace "field" with in this case, and what about group by time(15m) here?

SELECT sum(summ), field FROM (
   SELECT sum(val_1), field AS summ FROM tableA GROUP BY field
   UNION ALL
   SELECT sum(val_2), field FROM tableB GROUP BY field
) GROUP BY field

Any help would be appreciated.

Regards,
Mike


texts: Error During IMPDP in oracle database 12c

texts:
I create Dump file with EXPDP command in oracle database 11gR2 with operating system Oracle Linux 6.7

EXPDP Command:

expdp hmis/pass directory=MY_DATA_PUMP_DIR dumpfile=12c_db.dmp logfile=12c_db.log COMPRESSION=ALL FULL=Y

Now when I run IMPDP command in oracle database 12c with operating system Oracle Linux 7.7 then I am getting errors. I have CDB and PDB database in 12c

IMPDP Command:

impdp c##app/pass directory=MY_DATA_PUMP_DIR dumpfile=12c_db.dmp logfile=12c_db.log FULL=Y

Error:

ORA-39083: Object type TABLE:"HMIS"."TEMP_WE_TEST" failed to create with error:
ORA-01918: user 'HMIS' does not exist

ORA-39083: Object type TABLE:"APEX_030200"."WWV_FLOW_WORKSHEET_STICK" failed to create with error:
ORA-01918: user 'APEX_030200' does not exist

ORA-39083: Object type TABLE:"SYSMAN"."MGMT_GENSVC_AVAIL_BEACONS" failed to create with error:
ORA-01918: user 'SYSMAN' does not exist

How to solve this problem
Thanks


texts:
I am pretty new to MySql and Databases, and I was wondering in python sometimes I forgot to commit after an insert, and then when I do then next insert and commit that one the, auto-incrementing id column has skipped a number.

So I was wondering what happens when you insert and forget to commit is this bad for the Database? Or does is just skip because when you insert it assigned an id to it but didn't save it?

Thanks


texts:
Let say I have DB1, a database in PostgreSQL, and DB2, a database in Oracle.

How can I make a query joining these databases?

Is this even possible?


texts: Setting up Master-Master Replication in MySQL on a live system

texts:
I've just recently taken on the task of looking after our mySQL databases, and have been given the task of setting master-master replication. We have a live server which has a mySQL database which is constantly receiving data and a backup server which has been configured as a replication slave to the master. What I need to do now is set the live server up as a slave to the backup. I have an idea of what is necessary, and I guess I'm just looking for someone to confirm I've got it right before I go and do it (or tell me a better way of doing it).

Create slave user on backup-server
Grant replication privileges to the user
stop slave on backup-server
set database lock(read only) on live-server so no new data can be added (and hence replicated to backup)
Get master status from backup-server
-open new ssh session to live-server and issue change master command to point at backup-server
start slave on live
unlock database on live
start slave on backup


texts:
I'm getting the following error:

postgres@:~/>cat loadable_libraries.txt could not load library
"$libdir/plpython2": ERROR:  could not access file
"$libdir/plpython2": No such file or directory In database: db_name

When trying to run pg_upgrade from PostgreSQL-11 to PostgreSQL-13.
/usr/pgsql-13/bin/pg_upgrade -b /usr/pgsql-11/bin -B /usr/pgsql-13/bin -c -d /localpart0/db/postgres/11/db_name/data -D/localpart0/db/postgres/13/db_name/data -j 20 -r -v --clone

I don't have any plpython* extensions and already tried changing the pg_pltemplate entries for plpythonu and plpython2u to plpython3 and run the pg_upgrade but it fails either way.
Installed Packages:

postgresql11-plpython.x86_64    11.7-1PGDG.rhel7  @postgres11.prd
postgresql13-plpython3.x86_64   13.1-1PGDG.rhel7  @postgres13.prd

Any guidance would be greatly appreciated.


texts: How do you detect whether you have permission to query sys.dm_os_performance_counters?

texts:
According to the sys.dm_os_performance_counters documentation:

  On SQL Server, requires VIEW SERVER STATE permission.

  On SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

How can you check, from T-SQL, if you have these permissions?


texts:
Any idea why there is '1146 - table doesn't exists' on 'CREATE TABLE IF NOT EXISTS...' statement. It actually happened only on one of the slaves (there are two in total):

Last_SQL_Error: Error 'Table 'db_name.table_name' doesn't exist' on query. Default database: 'db_name'. Query: 'CREATE TABLE IF NOT EXISTS `table_name` (
          `step_id` int(11) NOT NULL,
          `item_id` int(11) NOT NULL,
          `item_type` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
          `date_attached` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
          PRIMARY KEY (`step_id`,`item_id`,`item_type`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'

EDIT:

I've re-synced this table but I'm not sure why this happened.

130802  9:32:03 [ERROR] Cannot find or open table db_name/table_name from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.


texts:
We are currently evaluating possibilities to store generic data structures. We found that at least from a functional point of view Oracle XMLType is a good alternative to the good old BLOB. Because you can query and update single fields from the xml and also create indexes on XPath expressions.

We are a bit worried about the performance of XMLType. Especially the select performance in interesting. We have queries that select multiple data structures at once. These need to be fast.

Such a query looks something like this

SELECT DOC_VALUE.getClobval() AS XML_VALUE FROM XML_TABLE WHERE d.ID = IN ('1','2',...);

Our XML documents are 7 to 8 KB in size. We are on Oracle 11g and create the XML column with type XMLTYPE.

Do you have experience about the performance of selects on xml type columns? What overall experiences do you have with XMLTYPE? Is this a robust and fast Oracle feature? Or is it rather something immature and experimental?

Regards, Mathias


texts: SQL database ODBC connection Multi-part identifier could not be bound

texts:
We're using PADS Professional xDX Designer (from MentorGraphics) with a SQL parts database connected through ODBC using views. All seems to be working just fine, except for our Inductor parts where somehow I get an error assigning the Sat. Current to a part.

I get the following error:

An ODBC error occurred:
The multi-part identifier "Sat. Current" could not be bound.
The multi-part identifier "Sat. Current" could not be bound.
Statement(s) could not be prepared.
State:37000 Native:4104,Origin:[Microsoft[ODBC SQL Server Driver][SQL Server]
State:37000 Native:4104,Origin:[Microsoft[ODBC SQL Server Driver][SQL Server]
State:37000 Native:8180,Origin:[Microsoft[ODBC SQL Server Driver][SQL Server]
(1400)

My SQL query used to create the view:

--DROP view _ARV_Inductor
--CREATE VIEW _ARV_Inductor AS
SELECT [Number] as [Part Number],
[Description],
CONVERT(float,[Value]) as [Value],
[Case],
[Variant_Member] as [Variant],
CONVERT(float,[Rdc]) as [Rdc],
CONVERT(float,[Tolerance]) as [Tolerance],
CONVERT(float,[Nom. Current]) as [Nom. Current],
CONVERT(float,[Sat. Current]) as [Sat. Current],
[Temp Min],
[Temp Max],
[Datasheet],
[CostPerUnit] as Price,
[Manufacturer_member] as [Manufacturer],
[Part name],
[RS],
[Farnell],
[Supplier_member] as [Supplier],
[Order code],
[Location]
FROM [PDXpertDB].[viewer].[SourceItemMasterView] where ([Type]='Inductor' AND [ShowInDX]='true')

All other properties assign correctly, when I use this view in SQL Server Management Studio I also get no errors and it shows the correct list.

Any suggestions what I might be doing wrong? Is it even a fault in my SQL part or might it have something to do with xDX designer?


texts:
I am trying to configure SymmetricDS to run a scheduled replication from one postgres database to another.

I've seen people suggesting doing that by setting these fields in node config:

job.routing.period.time.ms
job.push.period.time.ms
job.pull.period.time.ms

Or by creating time windows for replication in this table:

NODE_GROUP_CHANNEL_WINDOW

The first options seems to be better as I don't want to specify end time of replication. The issue is that I want to only replicate the database from one point in time, when the replication starts, so the data integrity is preserved (eg. if the replication takes 2h and there are changes made to the source DB after the replication had started I want them to be replicated in the next cycle)
Should I set all of these values to 24h, or does the routing job need to run more frequently?

While trying to test this I also ran into the following issue - not all the data has been replicated in single scheduled sync (the database is quite large):

  Reached the total byte threshold after 3 of 5 batches were extracted
  for node '001'.  The remaining batches will be extracted on a
  subsequent sync

How to disable the limitation? I know of 3 properties of each channel but I'm not sure how to configure them.

MAX_BATCH_SIZE
MAX_BATCH_TO_SEND
MAX_DATA_TO_ROUTE

The other thing I am not certain about is how to assign tables to routers and channels in order to achieve what I specified above.


texts: Handling data encoding issues while loading data to SQL from Script (Notepad++)

texts:
I'm pretty sure this is not a SQL Server problem.

I already asked a question HERE with an awesome explanation, BUT I still can't explain to the guys where I work that it has nothing to do with SQL Server collation or etc.

the situation:

We create scripts ( INSERTS/DELETS ) using ANSI.
We send these scripts to our clients.
Out clients run these scripts via SQLPLUS, SSMS, or any other method.

When they use something that is using ANSI, there's no problem.

BUT we would like to FORCE soomething, to tell them something like
"hey, you are running this script with a different encode, and some characters will be worngly formated, like an ã would be xE3.

is there a way to force a script to run on it's native encode ( the encode that I created )?

I really have no idea how to dead with this problem. We want to sent a script, and we want to be sure that this script will run with A being A, Ã being Ã.


texts: Pick last Vendor Number (From Master table ) who sold Item number(From detail)

texts:
We have a master table for Receipts which has

RCPSEQ, RCPNUMBER, PONUMBER, VENDORNUM, DATERCPT

AND Detail table which has:

RCPSEQ, PONUMBER, ITEM

I want to write a query to pick the Item number and the VendorNum who recently sold us that Item. Problem is grouping. with below query I am getting multiple PONUMBEr for same item and date.

SELECT
    p1.PONUMBER,
    P2.ITEMNO,
    (P2.MxDTAR) AS DATEM
FROM
    dbo.PORCPL P1
    join (select ITEMNO,   max(DTARRIVAL)as MxDTAR from PORCPL group by ITEMNO) as P2
        on P1.ITEMNO=p2.ITEMNO
order by
    ITEMNO


texts: How to influence the planner on Postgresql

texts:
I have the following:

a complex query, 'match', that returns about 200 rows (ids)
a huge table, 'usernames', indexed on id that maps them to usernames

When I try to left join the query (1) with the table (2) the planner decides to do a seq scan on the (2) table. This ends up taking about 90ms on my machine.

i.e:

   ->  CTE Scan on match l  (cost=0.00..3349.54 rows=167477 width=4) (actual time=3.458..5.188 rows=200 loops=1)
   ->  Hash  (cost=1638.00..1638.00 rows=100000 width=18) (actual time=66.537..66.537 rows=100000 loops=1)
         Buckets: 8192  Batches: 2  Memory Usage: 2542kB
         ->  Seq Scan on usernames r  (cost=0.00..1638.00 rows=100000 width=18) (actual time=0.019..27.595 rows=100000 loops=1)
 Planning time: 16.261 ms
 Execution time: 77.425 ms

If I add ('limit 200') in the query (1), the query time drops down to 15ms

   ->  CTE Scan on match l  (cost=0.00..4.00 rows=200 width=4) (actual time=3.623..6.163 rows=200 loops=1)
   ->  Index Scan using usernames_userid_idx on usernames r  (cost=0.29..6.71 rows=1 width=18) (actual time=0.017..0.017 rows=1 loops=200)
         Index Cond: (l.id = userid)
 Planning time: 4.682 ms
 Execution time: 10.093 ms

I understand that this is due to the planned not estimating correctly the number of returned rows of my 'match' query.

Is there anyway to hint the planner to do an Index Scan on the join, instead of the Seq Scan?

The complete query is:

EXPLAIN ANALYSE

WITH match AS

 (SELECT
   dist_match.match_userid AS id
     FROM

      (SELECT
        me.into_game AS into_game, r5.miles, r5.useridy AS match_userid
        FROM
          (SELECT l.id AS userid,
           category,
           r2.game,
           r3.game AS into_game,
           distance AS into_distance
           FROM userids l
           LEFT JOIN category r1 ON l.id = r1.userid
           LEFT JOIN games r2 ON l.id = r2.userid
           LEFT JOIN into_games r3 ON l.id = r3.userid
           LEFT JOIN into_distances r4 ON l.id = r4.userid
           WHERE l.id = 68931) AS me
         LEFT JOIN user_distances r5 ON me.userid = r5.useridx
         WHERE r5.miles < me.into_distance) AS dist_match
       LEFT JOIN games r6 ON (r6.userid = dist_match.match_userid
       AND r6.game = dist_match.into_game) ***LIMIT 200***)

SELECT l.id,name FROM match l LEFT JOIN usernames r ON l.id = r.userid;


texts:
I have been using SET keyword to assign the values to parameters declared in SQL Server something like,

Declare @StudentName Nvarchar(max)
SET @StudentName='ABC'

I'm not sure how to write this if they are more than 1 student name.

Say student names are being fetched from another Select Statement (Select student_name from student_tbl)

Will that be

SET @StudentName= 'Select student_name from student_tbl'

I'm not sure how SET works in case of multiple values.


texts: best way to deal with imperfect xls source file that needs to be imported into SQL via SSIS

texts:
The data is formatted like this:

Date       Type       Account
1/1/14     AAA        111
                      112
                      113

           BBB        114
                      115

1/4/14     AAA        116
                      117

           CCC        118

Each new line represents a new row in the Excel source file. So accts 112 and 113 are date 1/1/14 type AAA, and accts 114 and 115 are date 1/1/14 and type BBB.  Accts 116 and 117 are 1/14/14 and type AAA, etc. There are lots of blank rows and cells.

What I want, is to import this Excel file into SQL and have every account record include the date and type.

I have SQL server 2008 and Office 2010.  I can't edit the Excel source file, but I could save a local copy I suppose if necessary, but I'm guessing there's an easier way in the SSIS import process or in SQL.


texts: MySQL - Should I create a different database for users

texts: My question isn't really MySQL related, but that's the database I am using. My project was small so I put my user data and my business stuff on the same database. However, it's grown so much since and I've been thinking of separating the user part into an other database. It was nice to have them on the same database since I could do JOIN operations whenever I need to query things. I don't think I can do that on different databases. Are there any other downsides with 2 databases for the same web app? Would connection between them be an issue if they don't live in the same data center, for example.


texts: VACUUM FULL fails with 'ERROR: Check free disk space'

texts:
I am using Postgres 9.4. I have a database that \l+ tells me is 164 GB. When I try to run VACUUM FULL on the database, I get this error (after many hours):

ERROR:  could not extend file "base/18222/20547.2": wrote only 4096
        of 8192 bytes at block 279347
HINT:  Check free disk space.

I have 320GB of SSD on the server of which 89GB is available:

$ df -h
Filesystem                 Size  Used Avail Use% Mounted on
rootfs                     315G  211G   89G  71% /
udev                        10M     0   10M   0% /dev
tmpfs                      6.4G  192K  6.4G   1% /run
/dev/disk/by-label/DOROOT  315G  211G   89G  71% /
tmpfs                      5.0M     0  5.0M   0% /run/lock
tmpfs                       13G  4.0K   13G   1% /run/shm

I can't easily add more disk space to the server. Is there anything else I can do?

I found this question, so I can try that if needed. But I was wondering if I could tell VACUUM FULL to work with less memory.


texts: How do I override the dates with Temporal tables?

texts:
I'm trying to use temporal tables for our data lake environment to track changing data over time.  The first hurdle I hit is loading historical data to seed the tables.  Everything is loading as "now" instead of as of the point in the past.  Searching online the only solution I have found is to turn off system versioning, disable the period in the table, make changes to the table to fix the dates, then turn system versioning back on and hope that SQL Server will let me.  Is there a better way to force the datetime values to be what I need them to be?
Also, going forward, we are loading data that was updated yesterday and captured last night and being loaded into the lake early this morning.  I need to use yesterday's date as the effective date for the temporal table effective date, not the current timestamp.  Same issue, how can I force the datetime values?


texts:
Is it possible to have single row in a parent table be visible in more than one inherited (child) table?

Perhaps by inserting a row directly into the master table and then "making it visible" in one or more than one inherited table?

To clarify:

I want to insert into the parent table, then make that row visible in one or more child tables.  If the row is updated in one child table, the changes are immediately present in all tables where it is visible.

It does seem like this is not how Postgres inherited tables work, they appear to be more of a partitioning scheme.

I was thinking if I have a generic table with person information, then I could have a child table with that "person" and additional attributes, but if that person also belonged to a different class I could "make it visible" in another child table which would add different attributes.  If however I changed the person's contact number, which is generic and comes from the parent table, then that would be reflected in all the child tables where this row is seen.


texts: Moving MySQL databases/folder to another disk

texts:
I want to separate MySQL database from the OS (Ubuntu) disk by moving the entire database to an exclusive HDD (M.2), and update the database folder in my.cnf.

My question is: when the MySQL folder is on a separate disk, can I safely format the OS disk and install a fresh Ubuntu (and MySQL)?

Is it OK to install a fresh MySQL and simply point to the existing MySQL folder on a different disk in the my.cnf of freshly installed MySQL?

OR the best practice is to restore the databases from a dump after a fresh install, though the database is still available in the machine?


texts: Can Microsoft SQL Server be installed on Ubuntu 18.04 LTS?

texts:
Trying to install SQL Server on Ubuntu 18.04, I'm getting

# sudo apt-get install -y mssql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
 mssql-server : Depends: libcurl3 but it is not going to be installed
                Depends: openssl (<= 1.1.0)
E: Unable to correct problems, you have held broken packages.

Is it possible to install SQL Server on Ubuntu 18.04 and is it supported?


texts:
CREATE TABLE 'mydb.nation' (
'natcode' CHAR(3),
'natname' VARCHAR(45) NULL,
'exchange_rate' DECIMAL(9,5) NULL,
PRIMARY KEY ('natcode'));

CREATE TABLE 'mydb.stock' (
'stckcode' CHAR(3),
'stckname' VARCHAR(45) NULL,
'stckprice' DECIMAL(9,5) NULL,
'stckqty' DECIMAL(9,5) NULL,
'stckdiv' DECIMAL(9,5) NULL,
'stckPE' DECIMAL(9,5) NULL,
'natcode' CHAR(3) NOT NULL,
PRIMARY KEY('natcode'),
CONSTRAINT 'natcode'
    FOREIGN KEY('natcode')
    REFERENCES'mydb'.'nation'('natcode')
    ON DELETE RESTRICT
    ON UPDATE NO ACTION);

INSERT INTO 'mydb.nation' ('natcode','natname','exchange_rate') VALUES ('AUS','Australia','0.46'),
INSERT INTO 'mydb.nation' ('natcode','natname','exchange_rate') VALUES ('IND','India','0.0228'),
INSERT INTO 'mydb.nation' ('natcode','natname','exchange_rate') VALUES ('UK','United Kingdom','1'),
INSERT INTO 'mydb.nation' ('natcode','natname','exchange_rate') VALUES ('USA','United States','0.67'),


texts:
I have a database whose entire size is ~44GB, out of which ibdata1 is ~35GB. That doesnt make sense, since the size of the data shouldn't be more than 10GB.

I use the following query to get an esstimation of the data size:

    SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  30;

Any ideas how to clean ibdata1 and why has it grown so much?

BTW, I use innodb_file_per_table


texts: designing server side database to store user-specific data

texts:
I have a database with a number of quiz questions and answers. My basic design consists of these two tables

questions (question_id, question_string);

answers (answer_id, answer_string, correct_answer, question_id);

I would then like to keep track of user data, such as the number of times the user has seen a particular question. Assuming each user has their own unique ID, how could I go about designing a schema to store the data efficiently?

At the moment, I can only think of a table (user_id, question_id, number_of_times_seen). With thousands of users and thousands of questions, this table would grow rather quickly and might be a problem?


texts:
I am experiencing the following error, when I am trying to make an export to the database  using expdmp utility:

UDE-22303: operation generated ORACLE error 22303
OCI-22303: type "SYS"."KU$_STATUS1020" not found

Do you have any idea  what can cause this error?


texts:
I have one doubt in posgresql joins and filters. Please find below sql code snippets.

1)

select count(*)
FROM lfa1 a
LEFT JOIN lfb1 b
    ON a.lifnr = b.lifnr
    and a.hvr_is_deleted = 0
    and b.hvr_is_deleted = 0
    and a.mandt = '100'
    and b.mandt = '100'
LEFT JOIN LFM1 m1
    ON a.lifnr = m1.lifnr
    and m1.mandt = '100'
    and m1.hvr_is_deleted = 0
LEFT JOIN LFM2 m2
    ON a.lifnr = m2.lifnr
    and m1.ekorg= m2.ekorg
    and m2.mandt = '100'
    and m2.hvr_is_deleted = 0;

2)

select count(*)
FROM lfa1 a
LEFT JOIN lfb1 b
    ON a.lifnr = b.lifnr

LEFT JOIN LFM1 m1
    ON a.lifnr = m1.lifnr

LEFT JOIN LFM2 m2
    ON a.lifnr = m2.lifnr
    and m1.ekorg= m2.ekorg

WHERE
    a.hvr_is_deleted = 0
    and b.hvr_is_deleted = 0
    and a.mandt = '100'
    and b.mandt = '100'
    and m1.mandt = '100'
    and m1.hvr_is_deleted = 0
    and m2.mandt = '100'
    and m2.hvr_is_deleted = 0;

Both literally having same meaning but logically it's not working as both of the snippets are giving different counts. I want know how controls will be passed here to execute this code.
Can someone help me to understand would be appreciated.

Thanks

Ravi


texts: How does Couchdb calculates the document revision number?

texts:
Whats the exact algorithm to calculate the CouchDB revision number.
CouchDB uses a deterministic algorithm to calculate the document revision number.
Here is one relevant doc I found:
https://stackoverflow.com/questions/5954864/how-does-couchdb-calculate-the-revision-number

Buts its all in elrang, I am trying to implement it in dart.
I use sample document : {"_id":"123-this-is-an-id","hello":"world","testing":123} for testing.

When I do the MD5 hash of it , its gives me : f0853833850bce08dce0896e010a375e, where as the couchdb revision number is :

"_rev": "1-43dce8fe7e9a26b49390e3727a015cc0"

I google it everywhere, but if I can find the algorithm some where in text , UI can convert it into dart.


texts: How does Transparent Data Encryption actually work?

texts:
I've been trying to understand how TDE works with databases (particularly SQL Server). I've read numerous MSDN articles, blogs, info websites etc, but still cant get my head around all of it.

This is my understanding of TDE so far (with my assumptions & some questions):

A system key is created on installation of SQL Server -> but where is this stored??
The user creates a master key -> Now... Is this encrypted using symmetric key encryption and stored in the master db? If this is correct, then I assume that KEY = System key and TEXT = master key?
The master key is used to create a certificate -> this is then also stored in the master DB?
A per database encryption key is then generated (which is key for encrypting the DB data with symmetric encryption)
The certificate then encrypts the database encryption key, which is then stored in the database it was created for.

Now, for the last step then, would the process be this:

Encrypt(Certificate,Database Key) = Encrypted Database Key, then store EDK into database, then Encrypt(Database key, Database)?

Are my assumptions correct?
And then how does the data get decrypted? Because surly you cant retrieve the database key? Also, if someone gets hold of the certificate, doesn't that compromise the database?

I appreciate any assistance with this.


texts: MS Azure SQL: how do I import CSVs and set up push notifications?

texts:
Recently had a client who needed me to connect his online store databases to a POS system using MS Sql Azure 2014.

However, the normal methods using BULK insert nor SQl Service broker do not work. I usually receive errors like "Not available in this version of SQL server.

I tried the online azure website but I'm on windows 10 which does not seem to work well with silverlight....and using visual studio to access the database, does not accept bulk insert commands either....

Any guides on how to use to utilize these... wityh azure...normal SQL server 2014 ways do not work.


texts:
I'm going to implement a library system as a web application. And I decided to use this library schema. But there are some problems which I don't understand.

The Books entity, is it possible to use isbn as a primary key?
Is there a possible relationship between the Books entity and the Libraries entity? Is it a correct many-to-many relationship? I think that any book must be in only one library. So it is considered that one-to-many is more appropriate.


texts: Powershell to dump database schema to text file

texts:
I want to be able to dump a database schema to a sql text file using Powershell. I want this because our DBAs want to be able to do a diff on the file that is committed to SVN. They won't execute it if they can't see what it contains.

I've found many scripts that backup to a binary .bak file, but I can't find any that dump as a sql text file.

How do you do this in Powershell?


texts: Country specific fields in database schema

texts:
What would be the best practice to design a database schema that can have specific country fields.

e.g. a sample customer table

id
name
email
social_security_number (USA person ID)
curp (Mexico person ID)
cpf (Brazil person ID)
zip (USA postal code)
cep (Brazil postal code)
postcode (UK postal code)

But as we have more specific fields for each country the table will end up with a lot of fields.

The other approach would be to use a code instead

id
field001 > name
field002 > email
field003 > social security number for any country
field004 > postal code for any country

I understand that each approach has it's pros and cons.


texts:
I have a Microsoft SQL Server running on an IBM server with 10 cores and 64 GB RAM.

I have from 100 to 300 users working concurrently on the server.

Users complain about slowness of the application.  I've checked the server and found CPU usage is 6%, RAM usage is 4GB, and network usage is up to 1Mbps.  I have a 4Mbps dedicated line.

I've tried to collect other information on the server such as Blocking and Wait Statistics:

The Average resource wait time is 40 sec.

I am unable to interpret the cause. Can anyone suggest a way to resolve this?

If any further information is required, I can provide that.

EDIT:

As suggested, I've added the following items:

Page Life Expectancy 3

IO stats Snapshot

Wait Statistics

Server information

Memory status

Query Execution List


texts: Speeding up very slow UPDATE query (Postgresql 9.6)

texts:
In a app called Links, users post interesting content they've discovered recently and others can comment on these posts.

There's also an additional functionality, one that allows users to leave a comment on a comment.

All comments (and comments on comments) are saved in a table called links_publicreply in my postgresql 9.6.5 DB. This table contains a self-referential foreign key to support comments on comments. It's a big table, with nearly 200M rows.

One UPDATE query on the links_publicreply table is consistently showing up in slow_log. It's taking longer than 5000ms, and is ~100X slower than what I'm experiencing in most other postgresql operations.

Here's an example of the corresponding SQL from my slow log:

UPDATE "links_publicreply"
SET "direct_reply_tgt_text_prefix"='',
    "direct_reply_tgt_text_postfix"=''
WHERE "links_publicreply"."direct_reply_id"=175054159;

See the the explain analyze results: https://explain.depesz.com/s/G8wX

According to that, the Seq Scan ends up filtering 47,535,365 rows and is the source of slowness.

What do I do to reduce this exorbitant execution time?

Being an accidental DBA, I'm not an expert on the subject. My gut feel is that I'm filtering on the self-referential foreign key, so that should already be an index (thus optimized for lookups)? I'm a bit stumped here.

Addition:

Here's the entire output for \d links_publicreply:

            Column             |           Type           |                           Modifiers
-------------------------------+--------------------------+----------------------------------------------------------------
 id                            | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id               | integer                  | not null
 answer_to_id                  | integer                  | not null
 submitted_on                  | timestamp with time zone | not null
 description                   | text                     | not null
 category                      | character varying(20)    | not null
 seen                          | boolean                  | not null
 abuse                         | boolean                  | not null
 direct_reply_tgt_uname        | text                     |
 direct_reply_tgt_text_prefix  | text                     |
 direct_reply_tgt_text_postfix | text                     |
 direct_reply_id               | integer                  |
 level                         | integer                  |
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "id_answer_to_id" btree (answer_to_id, id DESC)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_direct_reply_id_fkey" FOREIGN KEY (direct_reply_id) REFERENCES links_publicreply(id)
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_direct_reply_id_fkey" FOREIGN KEY (direct_reply_id) REFERENCES links_publicreply(id)
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE


texts: Populate ID Field by Converting Group Name Values

texts:
This is a bit tricky to explain, but here goes.  We have a System Rights database that loads system users and system rights or access levels into two different tables.  The database is already built and live with some system data populating it daily.  I have been asked to build the SSIS package to populate a few new system users/rights into this database, but a couple of these systems do not have numeric fields identifying the position rights field as an id that I can populate the systemAccessLevelID field in the database with.  I will try to outline what I have and what I need to do.

SystemUsers Table:

SystemID - Int Defined in a system table
SystemAccessLevelID - Int populated from SSIS pull from source
Name - nvarchar(50) populated from SSIS pull from source

SystemRights Table:

SystemID - Int Defined in a system table
SystemAccessLevelID - Int populated from SSIS pull from source
SystemAccessLevelDesc - nvarchar populated from SSIS pull from
source

The idea here is one source database that stores all of the different system users with their rights in those respective systems to be able to run audit reports against the database.

The Problem
As an example in Source1 there is no numeric field that can be converted to Int to identify the Access levels for the different rights in the systems.

This has to get reloaded daily for audit purposes and the SystemAccessLevelID field needs to stay the same regardless if new system access levels are created in the source system.

I debated trying to create a function to loop through the SystemAccessLevelDesc field and converting each letter to an integer through hard coding a numeric value for every letter and special character then adding it all together, but I don't really know where to start with that.  I also feel that I must be missing an existing function or process somewhere that I could use and was hoping someone could point me in the right direction.

Example Data:

SystemID, SystemAccessLevelID, SystemAccessLevelDesc
1       , %DontHave%         , SYSADMIN
1       , %DontHave%         , SYSACCESS
1       , %DontHave%         , SYSREAD

How can I convert he different SystemAccessLevelDesc to get unique numeric values for SystemAccessLevelID?  I was hoping to be able to do this with a single Select statement still which is why I was thinking the user defined function was my best route.


texts:
I am creating a time series database that follows the table structure here: Storing intervals of time with no overlaps.  The difference with my design is that the FinishedAt column changes every time a user updates that particular record (you can actually see my question on his site at the bottom).

Basically, I have a FinishedAt column that has the current record with a date of 9999-12-31 until the next record with the same business key gets updated (in his example that key would be the SettingID). I run into the problem with his FK_IntegerSettings_SettingID_PreviousFinishedAt since I cannot make FinishedAt part of my primary key. I cannot make it part of my primary key because it is always changing....but is it okay to make FinishedAt part of my primary key, which would involve updating the primary key? Is this frowned upon even in a time series table?

I realize I can still use his other constraints, but I loose the very important foreign key constraint. This is my first encounter with this type of design so any direction will be appreciated. Thank you.


texts:
I am using mongodb with the web server.

Recently, I observed that mongodb server crashed after "no space left" for journalling.

So, after some online help I could repair and restart the DB.

But, for production, I am worried now.

Is there a way in mongodb - when bound to one machine - to truncate the journal files and prealloc files to get truncated without restart of mongodb or any other manual intervention?


texts: How do you partition an events table by a time based off the datediff(Lag,time)?

texts:
I am not sure how really to ask this question as i have never come across such grouping/ partitioning before.

Basically i have an events table with 50 million rows that give me a timestamp, user, video and a event. A super basic example of my current query is:

Activity as (
Select
Min(Timestamp) as start_Of_Activity,
Max(Timestamp) as End_Of_Activity
Session,
User,
Video,
Sum(Event_Count)
Group by User,Video,Session
)

I have just found out that the session ID is not accurate and wont be fixed so i have to change my business rules for defining an "Activity". Basically i have to run a Lag/Lead partition over each row to find out 'if The difference between a rows timestamp and the previous rows timestamp is over 2 hours for that user/video' then consider it another Row. Does anybody know how i should go about grouping this event data.

Example

Raw data example

Session ID:   User:  Video:   Activity_Start_Time   Event
10             1       2        12/2/19T12.05.05     play
10             1       2        12/2/19T01.05.05     stop
10             1       2        14/2/19T12.05.05     play
10             1       2        14/2/19T01.05.05     Watching
10             1       2        16/2/19T02.10.10     Stop

Current Grouping:

Session ID:   User:  Video:   Activity_Start_Time   Activity_End_Time
10             1       2        12/2/19T12.05.05     16/2/19T05.10.10

What the grouping needs to be

Session ID:   User:  Video:   Activity_Start_Time   Activity_End_Time
10             1       2        12/2/19T12.05.05     12/2/19T01.05.05
10             1       2        14/2/19T12.05.05     16/2/19T02.10.10


texts:
What will happen, if max_wal_size gets exceeded during backup?

Specifically, let's consider the following scenario on master:

Set max_wal_size to a very low number.
Run SELECT pg_start_backup('label', true); to create a checkpoint.
Perform lots of transactions, so that max_wal_size is exceeded.

If I am right, PostgreSQL cannot process WAL files, because doing so would pollute the checkpoint. On the other hand, no new WAL files can be created, because of the limit.


texts: I deleted 11 rows and I want the auto increment id values to reset to 6,7,8,9 … How can I achieve that without using the update statement each row one at a time. I want to find out if there is a way to avoid this sort of issue in the beginning when creating the database.


texts:
We have a very large staging table (> 80 GB). From our source system we load invoice data in the staging table. From the staging we transform the data and load into DWH/Fact. Every day we delete the current month, then reload from source into stage. The stage contains complete history over time.

In some DW loads we only need the current month. Sometimes year and previous year.

What is a better index strategy:

Clustered index on a date column (Fiscal Period)
Primary key with IDENTITY as surrogate key
Clustered index for natural key (some kind of line item e.g. invoice number)

All queries contain the date column (Fiscal Period) and sometimes additional columns like Invoice type as non-clustered index. In the ETL we can disable the non-clustered index but not the clustered index.

Which of the three types has the best performance for:

Insert into Stage table
Query the Stage table


texts: Sql Server Express edition - Could not allocate space for object [Table] in database [DB] because the 'PRIMARY' filegroup is full

texts:
I have reached the Sql Server Express edition limit of 10 Go of space per database and for every new INSERT I have this error :
2020-08-10 14:21:58.75 spid67 Could not allocate space for object [Table] in database [DB]
because the 'PRIMARY' filegroup is full.
Create disk space by deleting unneeded files, dropping objects in the filegroup,
adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

2020-08-10 14:21:58.75 spid67 CREATE DATABASE or ALTER DATABASE failed
because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.

To be sure I checked the remaining space with this query :
SELECT
  database_name = DB_NAME(database_id)
, log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID()
GROUP BY database_id

Which give me :
database_name|log_size_mb|row_size_mb|total_size_mb|
-------------|-----------|-----------|-------------|
DB           |    1224.00|   10184.00|     11408.00|

The row_size is my main issue, indeed I have accumulated a lot of data over time in a single table (few millions rows) but I only need to keep 1 year of these data, which is approximately 1/3 of the rows. So I deleted the old rows with a DELETE statement and I later read that it does not free the space instantly. And by running the query above the row_size effectively stays the same. I read a lot about database and file shrinking but it does not seems to be a good idea if the table number of rows will grow again fast which is my case.
My question is : is there a way to quickly fix my error by freeing space and allow my database to be able to insert new rows ? My need is to allow again new inserts in this database after deleting records of my huge table.
Note: I don't want to switch to another server with more space or to the standard edition. Because the express edition is sufficient for my use case, the table will grow fast again but I want to limit that situation happening again later.
EDIT: my table doesn't seems to be a heap because it contains in sys.partitions one index of type "Clustered Index/b-tree".


texts:
How can I capture queries that fail on the SQL Server due to connection failures?

Profiler doesnt seem to capture if there is a connectivity loss.

This is scenario:

DB server running in Domain A
SQL Client running on a server in Domain B - runs a transaction that would typically take 15 seconds to complete.
Network connectivity breaks at 10th second between Domain A and Domain B
SQL client displays error message - network failure
Transaction not completed on the DB server. No records updated.


texts: Restore to a point between two full SQL Server backups

texts:
I have two SQL Server database backups from Server1. Backup1 was made on, say, 2013-01-01 and Backup2 was made on 2013-01-03. What I want to do is restore Server2 to the intermediate state on 2013-01-02.

What I am trying to do is the following:

Restore Backup2 onto Server2 so it has the latest data.
Backup the transaction log on Server2.
Restore Backup1 on Server2.
Restore the transaction log using STOPAT = "2013-01-01 23:59:59"

I'm pretty sure I understand why this doesn't work. In step 2, I want the transaction log to go back to a certain backup, but there's no way to tell SQL Server which one I want. It just picks the latest one.

If that's correct, then I guess my question is, how can I trick SQL Server into giving me the transaction log I need. I've been looking at the backupset table and wondering if I can modify that to get what I need.

Server2 is a development server and not in production. I can modify it as necessary, even into an unusable state if it will help me get this data correct.


texts: Optimizing view to read rows as per usage statistics

texts:
There are two modules in the project:

Module 1: patient registration module

patient_admitted table holds information of patient admitted in hospital. patient_id is primary key.

patient_discharged table holds information of patient that got discharged from the hospital.

patient_personal_details view represents patient personal information from patient_admitted table such as name,age,gender etc.
It also reads from patient_discharged table in scenario as below:
A row in patient_admitted table is moved to patient_discharged table once the patient gets discharged, If medical_reports table consists such row  then the view retains that row information by reading it from patient_discharged table.

Module 2: Medical test module

medical_reports table holds medical report details of patient along with the personal information. If the patient is currently admitted then it reads the personal information from the patient_personal_details view.

Problem- Optimization needed:

Over the time the view would read bulk records from patient_discharged table.
Is it possible to discard records(row) in patient_personal_details view that haven't been accessed from past 120 days using some access statistics.

If there better way to design such scenario please suggest.


texts:
I was looking for quite some time on whats the best way of selecting or inserting some data.

I have two tables:

ReportData: report_data_id(pk), source_id(fk), value1;
Source: source_id(pk), source_name(varchar250);

I am doing a bulk insert into the ReportData around 10k of rows at once, but I don't know which is the best way of getting the source_id, because not all of the sources are defined.

I was thinking that a good way is writing a function to which I send a source (which is a string) and if the source exist return the id, if not insert the source and then return the id. And then before the bulk insert I will add this into the VALUES SELECT * FROM get_source('SourceString'). This then on insert is going to query that function 10k times.

In my ReportData I have like 10 columns which are FK and on each I need to do the same thing as described above.

Is this a good way of doing what I described above?


texts: Creating roles and granting rights to the role

texts:
I wanted to create a role called "user_updates" and this role should have the rights "table_note_updates".
The rights called "table_note_updates" is already available in my database. How to grant the rights to role that i create.


texts:
I am working with SQL server 2012. I want to copy a database along with all the data. I just want to create a dev DB.

I know SQL Server 2012 has a Copy Database... wizard. But when I run that wizard, I get an error on the last step:

On error, it tells me that error log in inside a text file on the shown path. When I go to that path, there is no such file there.

I tried backup and then restore with a different name as well. But it gave similar results. I got error while restoring. But I couldn't find the error log file.

Can anyone please help me with this?


texts:
I am working to run PostgreSQL is running locally but I cannot connect.
I installed postgress using brew, then I downloaded http://postgresapp.com

When I try to create a database

  createdb: could not connect to database postgres: could not connect to
  server: Permission denied     Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

I tried to troubleshot this by using :

sudo sysctl -w kern.sysv.shmall=65536
sudo sysctl -w kern.sysv.shmmax=16777216

But nothing work out.

Any ideas how to solve this


texts: Alternative (modern?) way of achieving effect of synchronous commits

texts: For a project we currently plan to establish a high-availability architecture based on two data centers (with short distance) and using synchronous commits at the DB level, using a commercial DB products. However, I'm wondering if other architectures are possible. Long ago, someone once mentioned to me (but I didn't get the details) that an alternative approach was to handle durability at the level of the database driver or possibly as an extension to the ORM (possibly Hibernate) based on some distributed in-memory storage scheme. As I understood it, the effect was that you could use asynchronous commits at the DB level (thus allowing use of cheaper DB products) and then still have the effects of synchronous commits due to this being handled in the ORM/database driver layer (but still transparent to the application code). Anyone heard of such a design or what it might refer to?


texts: MySql timeouts waiting for table lock, no apparent deadlocks

texts:
I started having the following error on two different applications that write to the same database and table:

  Lock wait timeout exceeded; try restarting transaction

At the beginning I thought it was caused by some slow queries creating deadlocks, but I haven't been able to find any evidence of that.

This is the output of the transactions section of show engine innodb status;

------------
TRANSACTIONS
------------
Trx id counter 1031368
Purge done for trx's n:o < 1031258 undo n:o < 0 state: running but idle
History list length 907
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1031348, not started
MySQL thread id 120, OS thread handle 0x7f5e4c148700, query id 1068 localhost db2_prod cleaning up
---TRANSACTION 0, not started
MySQL thread id 53, OS thread handle 0x7f5e3a209700, query id 1136 localhost root init
show engine innodb status
---TRANSACTION 1031286, not started
MySQL thread id 63, OS thread handle 0x7f5e4c0b5700, query id 509 localhost db1_prod cleaning up
---TRANSACTION 1031367, ACTIVE 16 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 24
MySQL thread id 121, OS thread handle 0x7f5e3a26b700, query id 1127 localhost db1_prod update
insert into `my_table` (`col_1`, `col_2`, `col_3`, `col_4`, `col_5`, `col_6`, `col_7`) values (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?,
------- TRX HAS BEEN WAITING 16 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 234 page no 1310 n bits 384 index `PRIMARY` of table `db1`.`my_table` trx id 1031367 lock mode S locks rec but not gap waiting
Record lock, heap no 230 PHYSICAL RECORD: n_fields 9; compact format; info bits 32
 0: len 4; hex 0000125b; asc    [;;
 1: len 4; hex 00002a4c; asc   *L;;
 2: len 4; hex 00000eeb; asc     ;;
 3: len 6; hex 0000000fbc8c; asc       ;;
 4: len 7; hex 390000014c1434; asc 9   L 4;;
 5: len 8; hex 0000000000001c40; asc        @;;
 6: len 5; hex 999968ce0c; asc   h  ;;
 7: len 4; hex 00000000; asc     ;;
 8: SQL NULL;

------------------
---TRANSACTION 1031308, ACTIVE 837 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 60, OS thread handle 0x7f5e3a23a700, query id 1059 localhost 127.0.0.1 db1_prod cleaning up
Trx read view will not see trx with id >= 1031309, sees < 1031309

Any idea what could be causing this? Let me know if you need more info. Thanks in advance.


texts: SQL Syntax Error - Unsure if it is empty value or backslash

texts:
I am getting a syntax error when the following query is being run:-

 INSERT INTO mw_reward_point_order VALUES(9477,0,0,144,,'100/1')

I originally thought it was because there was an empty value between the two , as I can manually populate the empty value like below and it works fine:-

 INSERT INTO mw_reward_point_order VALUES(9477,0,0,144,1,'100/1')

But I am not entirely sure, as the error reads SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use **near ''100/1')'** at line 1, query was: INSERT INTO mw_reward_point_order VALUES(9477,0,0,144,,'100/1')

Please could someone point out what exactly the syntax error is? Is it the lack of value between the two commas or in fact the backslash in the last value - possibly not being escaped?


texts: Efficient way to copy/sync a table - ideas on different approaches

texts:
We have an ISV database which we try to minimize contact with. It is an old 2008 R2 DB. We want to keep a copy of a number of tables in a different database. We are looking for a T-SQL solution to sync tables. This may need to take place when the system is being used.

I appreciate the answer will require further research and testing.

These tables are prone to locking from ISV app.
This is a low powered server and not really Enterprise.
Also we think the number of tables that we sync will increase
We want avoid anything like replication (the supplier of the ISV has habit of blaming any issues on us with no investigation whatsoever!)

These are my options (the ones I've thought of)

I've read bad things about MERGE in terms of performance, bearing in mind it's an older db, would this be fair?
tablediff.exe would be perfect but I'm concerned about locking the source table.
Is a straight truncate/insert with minimal locking/logging very inefficient - sounds stupid but I thought I'd include it.

Would just truncating and inserting a couple


texts:
I have a medium sized database (7 GB) with around 200 concurrent users. I am getting some database lag issues, suddenly my node-mysql client freezes during selects and inserts.

As a process of troubleshooting I checked SHOW STATUS on the DB. Everything seemed to be okay, but the connections attribute has a value of 262050.

I want to understand if this number is okay or if the figure is exorbitant?


texts: Multiple Update with Multiple Conditions

texts:
We are told to watch out for things being set to null (we dont want) by using the IN clause in the following:

UPDATE Tests SET
     TestScore =
              CASE
                  WHEN TestId = 10 THEN 1000
                  WHEN TestId = 11 THEN 1100
              END,
     TestScore2 =
              CASE
                  WHEN TestId = 10 THEN 2000
                  WHEN TestId = 11 THEN 2100
              END
     WHERE TestId IN (10, 11)

But what happens when there two conditions required, namely the joint combo of (TestId, TestSubId)? Ie, what do I do for the IN clause, the ???, in the following to show it has to be in the combos (10,25) and (11,22):

UPDATE Tests SET
    TestScore = CASE
        WHEN (TestId = 10 AND TestSubId = 25) THEN 1000
        WHEN (TestId = 11 AND TestSubId = 22) THEN 1100
    END,
    TestScore2 = CASE
        WHEN (TestId = 10 AND TestSubId = 25) THEN 2000
        WHEN (TestId = 11 AND TestSubId = 22) THEN 2100
    END
    WHERE TestId, TestSubId IN ?????


texts: Oracle Online Patching -- How To Exclude Tables in a Custom Schema

texts: Is it possible to exclude specific table objects from online patching? I believe the custom schema in which these tables exist is included in online patching. We are running 11g Enterprise Edition Release 11.2.0.4.0


texts:
I have to record a list of events made of action, reason (optional, may be multiple), user.
I have a master table with all the valid action, and another with all the valid action-reason combination.
All tables have a unique identity (not displayed in the example).

master_actions
action
A1
A2
A3

master_action_reasons
action  reason
A1      NULL
A2      R1
A2      R2
A3      R1
A3      R2
A3      R3

events
action  user   id
A1      John   E1
A3      Lisa   E2
A3      Sue    E3

reasons
event reason
E2    R1
E2    R3
E3    R2

Question: how do I make sure that the combination action-reason is valid, i.e. exists in master_action_reasons? Since the action is in one table, and the reason in another, I cannot use a foreign key.


texts: Database backup: NT AUTHORITY\\SYSTEM is not able to access the database

texts:
When trying to automatically backup a MsSQL database on a Windows 2012 Server, the following exception is thrown, and hence the backup is not taken:

  Description: Error encountered during backup. Error: [Microsoft.SqlServer.Management.Common.ExecutionFailureException:The server principal "NT AUTHORITY\SYSTEM" is not able to access the database "TestDB" under the current security context. BACKUP DATABASE is terminating abnormally. An exception occurred while executing a Transact-SQL statement or batch.]


texts: Using OLE DB Source SQL command vs desinging the query using the object tools

texts:
In SSIS, we can use the OLE DB Source SQL command to run a T-SQL script on the database and then use the output as the source. For example the a complex query can be wrapped in the OLD DB source SQL command and the output can be directly written to a file or target table. This way, we don't have to design the dataflow to do the Merge joins, lookups, etc etc.

I'm wondering if there is any disadvantage on this approach vs, defining the sql query in SSIS design tool (using the available objects in toolbox). It seems, it takes less time to get the job done and possibly faster to run.

Appreciate if you share your idea on pros and cons of this approach.

Thanks.


texts: Display Top X results for each unique value

texts:
I am trying to figure out how I can output any machine that has had the latest 3 backups be larger than 10GB. My SQL Table looks similar to the below table:

ComputerID | BackupImageName | WindowsVolumeGUID | FileSize | TimeCreated
1234       | PCBackup.spi    | kjsdfai-dadad-ddd | 10400    | 05-22-20
1234       | PCBackup1.spi   | kjsdfai-dadad-ddd | 10500    | 05-23-20
1234       | PCBackup2.spi   | kjsdfai-dadad-ddd | 10300    | 05-24-20
1234       | PCBackup3.spi   | kjsdfai-dadad-ddd | 10900    | 05-25-20
1225       | PCBackup4.spi   | kjsdfa5-dadad-ddd | 10600    | 05-22-20
1225       | PCBackup5.spi   | kjsdfa5-dadad-ddd | 5000     | 05-23-20
1225       | PCBackup6.spi   | kjsdfa5-dadad-ddd | 10000    | 05-24-20
1225       | PCBackup7.spi   | kjsdfa5-dadad-ddd | 4588     | 05-25-20

I need to output all machines (computerID's) or Volumes (WindowsVolumeGUID) that have their latest 3 files in the list greater than 10000 file size. So from the above table it would output the computerID of 1234 because its latest 3 are greater than 10000 file size and even though the computerID 1225 has 2 files greater than 10000 it would not output the result because there is a break in-between the two large files. The search will need to take into account the WindowsVolumeGUID since some machines have more than one volume and I need to take into account the individual volume backups and count the latest entries based on that.

Alternatively, I can also accomplish the same thing if there is a way to output the top 3 results for each unique WindowsVolumeGUID and not just across the board.

Any help would be greatly appreciated!


texts: why update a table take so long on oracle

texts:
I have a table A which has 3800+ rows, another table B with 300+ rows.

when I execute a update like this:

update A set some_colum = '2' where another_column not in (select id from B)

it takes 33 seconds to finish. Is that too bad performance?

how to improve?

I add a non-unique index on A.another_column, but it does't help. B.id is primary key.

here is the explain :