texts: Postgres Backup queries on AWS after reading Postgres docs

texts:
I am reading backup section of postgresql docs before heading over to create backup strategy on my EC2 instance.

So there are 2 ways to do it.

Create a dump using pg_dump.
File level backup with WAL archiving on. I will be using Snapshots so my use case falls in this category.

Now the question is do I need to freeze my filesystem before taking the backup? Postgresql docs suggest that WAL recovery does not get affected by an inconsistent snapshot. Does it make sense to do freeze just to be safe?

Should I take backup via both of above ways?

What sort of time period after which I should remove old WAL logs?

Docs also mention that WAL logs and data should be on the same filesystem to make backup consistent. In that case I am thinking to take backup of WAL logs separately from EBS snapshot and save it in S3 and some external place out of Amazon. Thoughts on this strategy?

Kindly mention if I am missing something.


texts: How to perform bulk delete and release free space?

texts:
Good Afternoon,
We have a database that wasn't been an archive anytime. The database has grown tremendously and we are running short of space. Hence there is a need to do a bulk delete and free space. Based on my research, in order to free up space, I have 2 options: shrink DB or shrink files. The shrink DB is a bad option because it ruins the fragmentation. Can anyone guide me on how to perform a bulk delete and free space? Also, we are planning to implement this process on a monthly schedule job.
Based on the discussion, I want to clarify 3 things:

Bulk Delete Without Locking The Table
Reclaim Free Space
Schedule Monthly Job

Looking forward to expert opinion.


texts: Mysql backup of remote server in local machine using percona xtrabackup

texts:
I had asked this question in serverfault.Later, i came to know about this site.

I have percona xtrabackup installed in my ubuntu client machine. My local machine IP address is 192.168.0.100. The database that i need to backup is in 192.168.0.200. Is it possible to do the backup of remote server in local server using percona xtrabackup? I tried doing the same using mysqldump in the past but i need to use percona xtrabackup in particular ? Thank you for the response.


texts: Dealing with out-of-range values on a PARTITION BY RANGE

texts:
I have a table for storing events which is partitioned on a timestamptz column by month using PARTITION BY RANGE.

There are 5 partitions at the moment, containing a span of a month each, starting from FOR VALUES FROM ('2018-01-01') TO ('2018-02-01') and ending with FOR VALUES FROM ('2018-05-01') TO ('2018-06-01').

Most data is entered in a linear and predictable fashion. However, events are consumed by apps who report them, and I do have to allow for a past event to be entered at any time - which could have a timestamp earlier than 2018-01-01, or even a future event (for example a charge that is expected to occur some time in the future).

I was planning on creating one partition for past events that would span much longer than a month since there is no expectation of having too many of those.

I'm not sure what would be the best approach for future events where no partition exists for yet.

Is there a way to get the min/max values I could store within the existing partitions? If not, I could create a reference table that would store these values but I prefer to not have to maintain that.

Should I create a trigger to check for each row being inserted (seems expensive)? Should I capture errors on inserts and deal with those one at a time?

Running on PostgreSQL 10.3.


texts: Teaching Slony replication to select slave nodes

texts:
There is a system with up to 256 nodes in db cluster. We need to replicate some data from one of them across to all others. But for every row to replicate there is a list of nodes where it must be replicated while on other nodes same row becomes orphan without one of parent table keys. To filter them out, or to prevent them from replicating needlessly, I'm trying to find a way to teach Slony to select nodes for replication however can't find such feature readily available. Has anyone tried it (customizing source code of its functions, etc.)?

_id,_text,_nodes
----------------
1   'one' {1,101}
2   'two' {2,102}
256 '...' {0,256}

Row id=1 is then replicated to node 1 and 101, etc.

Similar discussion Replicating only significant records to specific database however doesn't point in the right direction yet.


texts: Why am I able to query for more than 1024 columns but I can not make a view of the same query?

texts:
My boss is requesting a view with greater than 1024 columns where in he will only select a few columns he wants when he queries the view so that the actual returned columns are less than 1024. Why am I able to query for greater than 1024 columns but I can not save this very same query as a view?

I am using SQL Server 2012 Enterprise


texts: Extract data for all groups in a single SELECT statement

texts:
MySQL 5.

Table: schedule
Columns:
id  int(10) UN PK AI
startdate   date
groupid smallint(5) UN

The following statement extracts the Schedule ID for a given group (the schedule with the latest date not greater than the given date):

SELECT id FROM schedule
WHERE groupid=@group AND @date>=startdate
ORDER BY startdate DESC LIMIT 1

Question: Can I extract Schedule IDs for all groups, without enumerating (calling the above statement repeatedly) with all existing group IDs?


texts: Can we change database owner from [NT SERVICE\SQLSERVERAGENT] to [sa]

texts:
I have some list of databases with different owners.

Among them [NT SERVICE\SQLSERVERAGENT] is the owner of one database (let's say db_name ='Test_db_repli').

Can we change the owner of that particular database to [sa]?

If changed, will there be any problems in the future?


texts:
I need to create a new SQL server on my machine, currently I have;

  DESKTOP-RRCUM2L\SQLEXPRESS

Which works fine but was set up running version;

  14.0.1000.169

I need to create a server which runs version 13, in order to be able to deploy my database. I have downloaded SQL Server 2016 as I know this is required to use version 13.

Only I can find no way to do this, can anybody help me?

Thank you


texts: i want to ask if there is any name convention for sql server database roles, what is best practise or what i should follow. We have databases and a lot of people and i want to suggest some rules. thank you


texts: Enforcing Password Policy on an Instance

texts: Is there a way that I can enforce password policy on an entire instance so that I wouldn't have to do it on every login.


texts: Enabling IPAII protocol disable remote conections. Why?

texts:
I have some servers with IPAII protocol enabled with a certain port:

I'm using 1433 because a long story. but its working. We can use the default port 1433 AND IPAII ( for PHP developers ) with 1433 too.
But in production, when I changed, the server lost connection.
There's no way to explain this in a easy way.
Why I had no problems on one servers, but on another, it lost connection?
If the question is to abroad, I can get it better.


texts: DBCC CheckDB : Operating System Error 27(The drive cannot find the sector requested)

texts:
So my regular DBCC check had this lovely message waiting for me in the error logs this morning. I've been trying to find out more about this issue and what can be done about it. Manually running DBCC CHECKDB will result in DBCC aborting with Error 6 (connection closed by remote host) and get me this error in the SQL log file. Running DBCC CHECKDB(dbname) WITH PHYSICAL ONLY or DBCC CHECKDB(dbname, NOINDEX) will complete successfully and report no errors. Chkdsk reports no errors on the drive.
Both the drive and the database have ample free space. The disk is on a SAN (rather than local).
How can I determine if this a corruption error in the database file or an actual "on the disk" bad sector? Can this be fixed in place or do I need a new disk and to restore from backups? While I have good backups, if I restore a recent backup, will that also "restore" the missing data/sector?
FWIW the DB is 2.2 TB so I'm rather reluctant to jump into a fix until I really know what's going on.
EDIT: This morning, things went even further sideways. We had a log full error, but the log could not be extended or backed up due to the same disk error (sector cannot be found). This was preventing any actions in the database or it's related application. Had to back up the log to another drive to free up log space and allow for normal operations to continue.


texts:
I'm using Drupal 7 on Windows and its using SQL Server 2008 RS2 (SP2).
Now, I have to move my site and new server using SQL Server 2016.

How can I restore my database to new server? Is it possible?

Thanks.


texts: Can't read config files from pgAdminIII on local Kubuntu machine

texts:
My database starts fine, I can connect to it from pgAdmin and run queries.  However when I go to Tools->Server Configuration->postgresql.conf I get an error.  The error states

could not stat file "postgresql.conf": No such file or directory
CONTEXT: SQL function "pg_file_length" statement 1.
STATEMENT: SELECT pg_file_read('postgresql.conf', 0, pg_file_length('postgresql.conf'))

I tried to run that statement as its own query and came up with no such function.  I googled around and found that I need adminpack so I installed that.  Now when I run the same query I get `could not open file "postgresql.conf" for reading: No such file or directory.

The file does indeed exist at /etc/postgresql/9.4/main/ so I put the full path in the command and it tells me that absolute paths aren't allowed.  I made a symlink in my data directory /var/lib/postgresql/9.4/main of the postgresql.conf file.  Doing that allows me to open postgresql.conf from pgamin.  However, when I save the file I notice that I get a new postgresql.conf in my data directory and that it renames the symlink to postgresql.conf.bak which means when I have conflicting postgresql.conf files between the /etc/.../main and the /var/.../main directories.

One thought is to move all the .conf files from the /etc/ directory to the /var/ and then put symlinks in the /etc/ but I figure I should just ask rather than screw around.


texts:
I have been asked to create a read only user in a Oracle database 12c. The read only user must have read only grants only to the objects of select schemas, which I created and handed over.

But now the request from the team is that the newly created read only user must have read only grants to even to all those new objects created by those select schemas.

How is this possible? Inputs please!


texts:
In writing a random number function, I have learned that you can’t use NEWID() or RAND() inside a user function because they are non-deterministic.

I have also discovered that I can use PWDENCRYPT and get away with it. I would have thought that that was also non-deterministic. It certainly gives me a random-enough value for testing purposes (I’m not trying to simulate quantum physics here).

My questions:

is PWDENCRYPT really acceptable inside a user function (i.e., it works now, but can I expect it to keep working)?
is it random enough (for a reasonable definition of random)?


texts: Transactional Replication in SQL server 2014

texts:
We need to set up transactional replication in SQL server 2014, Enterprise edition so that:

Database to be replicated from Publisher which is approximately 2 TB in size. This would be used 24*6. Here we also need to use sql server encryption. Server has

  Approx 480 GB of RAM and 48 logical processors.

1) Subscriber to be used as Reporting server where users will only be doing their reporting tasks. This is to be implemented so as to avoid any additional overload on the primary server above. Data to be replicated should not be the encrypted as the SAN drive on this server is already encrypted. storage of around 3 TB.

2) Same database needs to be replicated on another server, which for almost 300 users would be available as VPN mode,connecting to it. This server would be required to have encryption data to be replicated.

I believe above 2 would be possible from a single publisher to 2 different subscriber via transactional replication. Please correct me if  am wrong as we would be setting this up for first time in SQL server 2014.

Also, would above be the best strategy for DR. The client does not agree with clustering. So we have suggested replication to be the best approach as per above requirements.

Please provide your expert recommendations that can be helpful for us to set up the above requirement.


texts: Are cut and paste of records from a table to another a correct pratice?

texts:
I am working on an app that does task dispatching.  A user inputs a task with a start date and end date.  The app gives the user a list of workers available and chooses to offer the task to some of them.  When a worker logs in the app and accepts the task offered, he is booked to do the task.

I plan on using the following strategy :
There would be a taskPossibilities table and a bookedTasks table
a SELECT in the bookedTasks would retain the workers who are not already booked for the chosen period.  All the workers chosen by the user would be put in the taskPossibilies table.  When a worker accepts a task, the record of this worker is copied to bookedTasks, and then all corresponding records in the taskPossibilities are deleted.

I am not asking how to do it : I have done it already, but tested on a local machine only.  I am wondering if this way of doing things represents a risk on data integrity, and wheter there is a better practice for such a situation.


texts: Find out beforehand how many records a query has

texts:
I develop reports in ExtJS resulting of queries in SQL Server 2012.

I use Ext.Grid with paging, therefore I can use offset to limit the amount of records a query will return. But for this ExtJS feature to work, I still must provide it the total amount of records the giving query has.

Some reports use filters and data changes during day, because of that I can only know the total count by running the query. This results in running it twice: once with count(*) to get total count and again to get proper data.

Is there a way to find the total count of a query, even when it has offset command, without having to run it twice as I'm doing now, and without looping through all records?


texts: Import Tables without Constraints from Schema Export

texts:
I am trying to do an import using data pump, but I am struggling to come up with the right options.  The export was taken as a schema export.  I am trying to import just the tables, with no other database objects.
I am currently using
INCLUDE=TABLE
TABLE_EXISTS_ACTION=REPLACE
REMAP_SCHEMA=
REMAP_TABLESPACE=

This is correctly importing the tables, but I am a little surprised to see that the import is also creating the constraints and triggers on the tables, or trying to at least.  The triggers are failing because they include a schema name that does not exist in the database.
I thought that by using INCLUDE=TABLE only the tables would be included.  Apparently that is not the case.  I am unable to use EXCLUDE=CONSTRAINT or EXCLUDE=TRIGGER as I am already using INCLUDE to limit the import to just tables.
Any ideas on how I could structure the import to only import tables?


texts: How can we demo Lost update, Uncommitted data, Unrepeatable data, Phantom on MySQL

texts:
I'm studying Database Management System and I have a project that I need to demo Lost update, Uncommitted data, Unrepeatable data, Phantom in a DMS I selected. I selected MySQL for my project. But after a couple of times trying to demonstrate I failed. This is a case I tried.

This is transaction T1

START TRANSACTION;
UPDATE roles SET roles.isAdmin = '0';

DO sleep(5);

ROLLBACK;

This is transaction T2

START TRANSACTION;

SELECT * FROM roles WHERE 1;

COMMIT;

 Can you help me to figure out how can I demonstrate Lost update, Uncommitted data, Unrepeatable data, Phantom situation?


texts: How to remove known elements from a JSON[] array in PostgreSQL?

texts:
I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to model those as JSON in native PostgreSQL arrays.

This is a stripped down snippet of my table creation statement:

CREATE TABLE test.persons
(
  id UUID,
  firstName TEXT,
  lastName TEXT,
  communicationData JSON[],
  CONSTRAINT pk_person PRIMARY KEY (id)
);

As you can see it is a person featuring a list of communication data objects in JSON. One of such objects might look like this:

{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf6"}

I can easily append such a JSON object to an array using PostgreSQL's array_append. However, I fail at removing a known value from the array. Consider f.e. this SQL statement:

UPDATE test.persons
SET communicationData = array_remove(
      communicationData,
      '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf6"}'::JSON
    )
WHERE id = 'f671eb6a-d603-11e3-bf6f-07ba007d953d';

This fails with ERROR:  could not identify an equality operator for type json. Do you have a hint how I could remove a known value from the JSON array? It would also be possible to remove by position in the array, as I know that one also...

PostgreSQL version is 9.3.4.


texts:
I have the following query:

CREATE PROCEDURE MYPROC(
IN @TABLES VARCHAR(500))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC MYPROC
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
BEGIN

 --declare variables

DECLARE @TABLE VARCHAR ( 100 ) ;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE C1 CURSOR FOR
SELECT * FROM TABLE(split(@TABLES, ';')) AS FUNC;

 -- create temp table
DECLARE GLOBAL TEMPORARY TABLE SESSION . KEYS
(
    INMATEID INTEGER NOT NULL ,
    ROWID INT GENERATED ALWAYS AS IDENTITY ( START WITH 1 , INCREMENT BY 1 )
)

WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED ;

IF ( @INMATEID IS NOT NULL ) THEN
    INSERT INTO SESSION . KEYS ( INMATEID ) VALUES ( @INMATEID ) ;
ELSE

    INSERT INTO SESSION . KEYS ( INMATEID )
    SELECT MyTable.ID
    FROM MYTABLE;
END IF ;

 -- select tables

OPEN C1;
FETCH C1 INTO @TABLE;

WHILE (SQLCODE = 0) DO
BEGIN

If (@TABLE = 'INMATES') THEN
   SELECT INMATES.* FROM ATABLE;  <-- THIS IS THE PROBLEM SPOT!
END IF;

FETCH C1 INTO @TABLE;
END;

END WHILE;
CLOSE C1;
END;

The problem is marked in the above procedure.  I want to loop through the passed in 'tables' and SELECT data from the tables that are passed in.  The problem is that I cannot do a SELECT statement within my while loop and if statement.  Are there any syntax problems that you see or is it something else entirely?

When I try to CREATE the procedure I get this error message:

SQL State: 42904
Vendor Code: -7032
Message: [SQL7032] SQL procedure, function, or trigger STSSP_JAILIF_GETDATA in PJGVDGD1 not created.

I've narrowed it down to that it's definitely related to my 'SELECT INMATES.*' line in the IF Statement.


texts: REPLMERG.exe cannot connect to distributor

texts:
We have a SQL Express 2016 instance that is doing merge replication to a SQL 2016 box. This is an upgraded instance on both sides. We upgraded from SQL Express 2012 to 2016 and we moved the database to a SQL 2016 instance.

Merge replication triggered by a batch file with the following parameters (server, distributor, and subscription info changed to numbers)

REM -- Declare the variables.
SET Publisher=1111111
SET Distributor=2222222
SET Subscriber=3333333\SQLEXPRESS
SET PublicationDB=44444444
SET SubscriptionDB=555555555
SET Publication=666666666

REM --Start the Merge Agent with concurrent upload and download processes.
REM -- The following command must be supplied without line breaks.
"C:\Program Files\Microsoft SQL Server\130\COM\REPLMERG.EXE" -Publication %Publication% -Publisher %Publisher%  -Subscriber  %Subscriber%  -Distributor %Distributor%  -PublisherDB %PublicationDB%  -SubscriberDB %SubscriptionDB% -PublisherSecurityMode 0  -OutputVerboseLevel 2  -SubscriberSecurityMode 1  -SubscriptionType 1 -DistributorSecurityMode 0  -Validate 3  -ParallelUploadDownload 1
pause;
exit

When the batch file is ran, it shows in the replication log file that it cannot connect to the distributor.

ERROR: ErrNo = 0x80045901, ErrSrc = <null>, ErrType = 9, ErrStr = The process could not connect to Distributor '2222222'.

I can manually trigger the replication from my account (I'm a DBA).

Am I just missing a username and password for this to automatically merge when the batch file is called or am I missing something blatantly obvious in my batch file?


texts: As a DBA, how would I go about transitioning from Oracle to SQL Server?

texts:
I'm an Oracle DBA that also has Sybase experience.

What are the major architectural and conceptual differences between the two RDBMS platforms?

An answer similar to the SQL Server->Oracle question here would be of most use.


texts: Return int value from boolean field in SQL select

texts: Is there a way to return an int value for a boolean field in a sql select statement?


texts: MySQL Install on Windows 8.1 hangs at server restart

texts:
I have a 64-bit Windows 8.1 machine and would like to install a local database to host some helper application databases. I have attempted this several times with this version of MySQL (5.6.25 GA) but it hangs at the server restart during the installation. My internet has been stable each time I have attempted this so from my assumptions, this is not caused with a network drop-out.

EDIT

After looking through the install logs and reading the MySQL Notifier message. According to those, the Windows Service cannot be found. During the installation, the service was called MySQL56 but MySQL Notifier says mysql56. Am I doing something wrong?


texts: SQL Cluster without a shared disk. Possible?

texts:
Is it possible to create a two node cluster using two basic network servers that only have local disks?

One will act as an idle standby server.  Without knowing too much about clustering I'm not sure if a separate shared disk array is required.

If so, is it possible to use a DAS setup, or does it have to be a SAN that supports clustering?


texts: Ok to drop indexes on FK's if they have no stats in the DMVS

texts:
I have used the well known query below from Kevin Kline to check for unused indexes. Several indexes created on Foreign keys returns no read stats, only writes.

Are you 100% safe to drop these indexes ? or could they be used by the optimizer for say inserts or Deletes and would not register any stats in the DMV ?? if so, how can one tell they are 100% safe to remove?

I have had the server running for 2 months so am certain I have covered our monthly workload cycle.

    SELECT o.name
    , indexname=i.name
    , i.index_id
    , reads=user_seeks + user_scans + user_lookups
    , writes =  user_updates
    , rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
    , CASE
        WHEN s.user_updates < 1 THEN 100
        ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
      END AS reads_per_write
    , 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
    FROM sys.dm_db_index_usage_stats s
    INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
    INNER JOIN sys.objects o on s.object_id = o.object_id
    INNER JOIN sys.schemas c on o.schema_id = c.schema_id
    WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND i.type_desc = 'nonclustered'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 2000
    ORDER BY  name, reads


texts: Oracle concatenation of queries and function index usage

texts:
I have a problem with something I can't understand about indexes in Oracle 11g.

We can create test data with:

create table test2(field1 varchar2(100),field2 varchar2(100),field3 number,field4 varchar2(100));

create index test2_idx1 on test2(upper(field1));

create index test2_idx1b on test2(field1);

create index test2_idx2 on test2(field3);

DECLARE
  j NUMBER :=1;
BEGIN
  FOR i IN 1..500000
  LOOP
    INSERT
    INTO test2
    (field1,field2, field3, field4)
    VALUES
    ('field1='||i,'a', j, 'i' );

    IF (i mod 1000)=0 THEN
       j := j+1;
    END IF;
  END LOOP;
  COMMIT;
END;

EXEC DBMS_STATS.GATHER_TABLE_STATS ('system', 'test2');

Then I make some explain plans which result I can't understand

Query 1:

SELECT * FROM test2 WHERE field3=1;

Explain plan:

Everything OK here. Index is used.

Query 2:

SELECT * FROM test2 WHERE upper(field1)='FIELD1=1';

Explain plan

Everything OK again. Index is used.

Query 3:

SELECT /*+ USE_CONCAT */ * FROM test2 WHERE field1='FIELD1=1' OR field3=1;

Explain plan

Indenxes are used in concatenation. No problem again.

Query 4:

SELECT /*+ USE_CONCAT */ * FROM test2 WHERE upper(field1)='FIELD1=1' OR field3=1;

Explain plan

Here my problem arises. Why is test2_idx1 not being used? Is it because it is a function index? Is there any workaround in this cases?

Thanks a lot in advance.


texts: Replace some rows in one table with rows of another table

texts:
I have two tables and a set of primary key values. I want to replace all rows of the first table whose primary keys are in this set with the corresponding (having the same primary key) rows of the second table.

(The two tables have the same structure.)

How to do this with MySQL and PHPMyAdmin or (worse) command line?

You can assume that both tables have a row for every value of the primary keys in the set.


texts: How to limit the number of sessions for a specific login

texts:
How can we let just one program to connect to the database, in case many programs have the connection string.
(limit the number of sessions for a specific login)


texts:
I have received a dump of a Oracle 11.2.0.2.0 db, which I'd like to export to some readable format like plain text, CSV, excel...

Is that possible in some sort of a simple way? I've found a few converters, but they're not free.

If nothing else, I did just install the latest Oracle Express Edition and Oracle SQL Developer, but I can't seem to find a way to import my .DMP file.
I've tried using the imp and impdp tools, but they both gave me an error:

imp 'sys AS SYSDBA' file=dump.dmp full=yes log=implog.txt

IMP-00038: Could not convert to environment character set's handle

impdp 'sys AS SYSDBA' file=dump.dmp full=yes log=implog.txt

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-39088: file name cannot contain a path specification

UPDATE:

It seems I needed to copy the dump to "C:\oraclexe\app\oracle\admin\XE\dpdump" for the IMPDP to work. But now I get the following error message (or maybe just a warning, because as far I see, the tables were created and the data was imported?):

ORA-39083: Object type REFRESH_GROUP failed to create with error:

ORA-23421: job number 1389 is not a job in the job queue

Failing sql is:

BEGIN
dbms_refresh.make('"DB"."TABLE"',list=>null,next_date=>null,interval=>null,implicit_destroy=>TRUE,lax=>FALSE,job=>1389,rollback_seg=>NULL,push_deferred_rpc=>TRUE,refresh_after_errors=>FALSE,purge_option => 1,parallelism => 0,heap_size => 0);
dbms_refresh.add(name=>'"DB"."TABLE"',list=>'"DB"."TABLE"',siteid=>0,export_db=>'XE');
 END;

Job "SYS"."SYS_IMPORT_FULL_01" completed with 32 error(s) at 06:59:30

UPDATE 2:

Yeah, I've just checked via Toad for Oracle, and all the data was successfully imported. I also managed to export to excel file using the same application.


texts: Efficient SQL database design for recording examples of a word

texts: I am designing a dictionary-like program which stores (and displays) a few example sentences of the given word. One way to design a SQL database for such problem is to create a table with two columns word and example and then for each example sentence add a new row with the repeated word in the column word. Is this approach common and efficient? I was also thinking of dynamically creation of a table of examples for each word and then relate the table to the word somehow. Is this approach better than previous one?


texts: How to solve this complex insertion problem

texts:
In our database we store policycase records. A policycase has a 1:1 relation to a (requested) policy record. The policy record has a 1:n relationship to policy_map. Where policy_map is a key, value table.

When a policycase is changed from our user application, the complete policycase record and all it's child records are copied and stored again. At least, that was the requirement. Due to a bug only a small number of the policy_map records are copied to the new policycase.

We want to execute an insert query that creates the missing policy_map records for the copied record, when they do not exist.

The old policycase can be recognized from a status HISTORICAL and the copied policycase from a status ARCHIVED. Both policycases have the same uniqueid field.

So the following select query gives me the policy_map records for the historical and the archived (copied) policycase records for a certain uniqueid:

SELECT
    policycase.status,
    policy.id,
    policy.uniqueid,
    policy_map.map_key,
    policy_map.map_value
FROM policycase
INNER JOIN policy ON policy.id = policycase.requested_id
INNER JOIN policy_map ON policy_map.policy_id = policy.id
WHERE policy.uniqueid = '91157235'

The result of this query is something like:

+------------+--------+----------+--------------------------------+-----------+
|   status   |   id   | uniqueid |            map_key             | map_value |
+------------+--------+----------+--------------------------------+-----------+
| HISTORICAL | 438957 | 91157235 | isPropertyOwner                | TRUE      |
| HISTORICAL | 438957 | 91157235 | livingAtProperty               | TRUE      |
| HISTORICAL | 438957 | 91157235 | stateOfMaintenance             | GOED      |
| HISTORICAL | 438957 | 91157235 | stateOfMaintenanceRoof         | 0         |
| HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseContent | J         |
| HISTORICAL | 438957 | 91157235 | unsecuredGuaranteeHouseFire    | J         |
| ARCHIVED   | 451423 | 91157235 | isPropertyOwner                | TRUE      |
| ARCHIVED   | 451423 | 91157235 | livingAtProperty               | TRUE      |
| ARCHIVED   | 451423 | 91157235 | stateOfMaintenance             | GOED      |
+------------+--------+----------+--------------------------------+-----------+

In the table above, the map records

stateOfMaintenanceRoof
unsecuredGuaranteeHouseContent
unsecuredGuaranteeHouseFire

need to be copied to the policy_map table, related to the ARCHIVED policycase. That needs to be done for every uniqueid in the database.

How would a query or function that accomplishes this look?

We are running Postgres 9.6 on Amazon RDS.


texts: ON creating BITMAP INDEX on a table in mysql throws error

texts:
I have database named "demo_dev" and added a table named "countries".

I wanted to create a bitmap index on a column, say, region as it has fewer unique values, using following query.

 CREATE BITMAP INDEX ON countries (region);

It threw an error as shown below:

  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 'BITMAP INDEX ON countries (region)'

What shoud be done for it to run successfully?

I just refered the following link : http://dev.mysql.com/worklog/task/?id=1524

mysql version : 5.5


texts: Inserting two tables together with no Id or UNIQUE anything

texts:
Let's assume we have two tables:

table1, retaining the following data:

Name Height Age
---- ------ ---
Mary   1.60  18
Mary   1.62  19
Mary   1.68  20
John   1.80  17
John   1.85  18
John   1.86  19

table2, with the rows shown below:

Name Height Age
---- ------ ---
Mary   1.60  18
Mary   1.62  19
Mary   1.68  20
John   1.80  17
John   1.85  18
John   1.86  19
Vince  1.78  22
Vince  1.80  23

How would I JOIN these two tables using Sybase so that afterward table1 has Vinces data in it?

Think of table2 as a temporary table that contains new information. I want to update table1 with the new information.


texts: Can we have two primaries for the same shard in mongodb?

texts:
Can we have a Mongodb deployment in two data centers in which the data set is divided into two shards A and B.

Data Center 1                     | Data Center 2

Shard A, Shard B                  | Shard A, Shard B

So some clients connect to Data Center 1 and write to the primary of Shard A and some other clients connect to Data Center 2 and write to the primary of Shard A there. Then some process does the reconciliation between Shards in two data centers.
The advantage of this setup is that clients can do a write to the data center that is closest to them and they can still read the latest data which is combined from Data Center 1 and 2.


texts:
I'm trying to write a plpgsql function that recursively returns a set of columns from records in a tree structure.

I have a data table and a table to link the data together:

DATATABLE
-----------
id integer
value text
info text

LINKTABLE
-----------
link integer
parent integer

My thought was to do like in the following function:

CREATE OR REPLACE FUNCTION my_function(itemID integer)
  RETURNS TABLE(id integer, value text) AS
$BODY$
BEGIN
    RETURN QUERY SELECT my_function(A.link) FROM linktable A, datatable B
        WHERE A.parent = B.id AND B.id = itemID) C;

    RETURN QUERY SELECT id, value FROM datatable WHERE id = itemID;
    RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

But that doesn't work, I get an error in my first query:

  ERROR:  structure of query does not match function result type

My Just-In-Brain compiler detects no problems, so what am I doing wrong here?


texts: Does the definition of functional dependency permit derivative values?

texts:
Sorry if the question in the title seemed vague. The question is rather broad and this is the best I could summarize.

I have read about functional dependency, 1nf, 2nf and 3nf. From what I understand, if A -> B then B is functionally dependent on A. In other words, if there is only a unique A for each B then B is functionally dependent on A. (Please correct me if I'm wrong)

Now consider the following table.

CREATE TABLE sales (
   product_id int,
   product_name varchar,
   year int,
   indirect_sales int,
   direct_sales int,
   total_sales int
);

The candidate key for each row is clearly (product_id, year). This table seems to be 2nf but I suspect this table is still not 3nf. The reason is that we can determine total_sales from direct_sales and indirect_sales because direct_sales + indirect_sales = total_sales. Could we call this a functional dependency then? Because for each direct and indirect sales there's a unique total sales. You can also see that this could potentially cause updating errors. If you update direct_sales you must also update the total_sales simultaneously.

So how would you deal with this in the context of normalization? Normally you would create a separate table with attributes but creating a table with direct, indirect and total sales seems artificial. Also how would you deal with this in a real situation in which 3nf was strictly required.

I have provided a simplified example so you can grasp the essence of the problem but the real problem that I'm battling is with these tables.

CREATE TABLE user (
   user_id int,
   user_name varchar,
   user_login varchar,
   user_password varchar,
   balance int
);

CREATE TABLE transaction (
   transaction_id int,
   user_id int REFERENCES user.user_id,
   amount int
);

This is a banking database that stores user information and transaction information for each user. I suspect this won't be considered 3nf because balance is always the sum of user transaction amounts. This will cause updation anomaly; if we update a transaction the balance must reflect it.

Can you help me make sense of this? Thanks.


texts: making a TSQL statement that has variables to be dynamic

texts:
I am having a real hard time trying to convert a TSQL statement (which be a store procedure) into a Dynamic SQL. Basically I must be able to pass the schema name as a parameter.

Here is my code:

DECLARE @query AS varchar(max)
DECLARE @schemaName AS varchar(5) = 'wl03' -- To be used later as a parameter in stored procedure
DECLARE @plus11Month AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+11, 0) AS DATE) --The 11th month from the current month
DECLARE @plus12Month AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+12, 0) AS DATE) --The 12th month from the current month

DROP TABLE IF EXISTS #12thMonthExpectedActivity

-- Add rows for existing month into a temporary table
-- The code below returns an error: The data types varchar and date are incompatible in the add operator.
SET @query = 'SELECT ' + @plus12Month + 'AS period, genusId, subjectId, waitingStageId, value, ' + GETDATE() + ' AS savedOn, ''<Automated>'' AS savedBy INTO #12thMonthExpectedActivity FROM [' + @schemaName + '].[ExpectedActivity]
WHERE period in(' + @plus11Month + ')'
-- EXEC(@query) will go here once the above is working

/* This original code works just fine!
SELECT @plus12Month AS period, genusId, subjectId, waitingStageId, value, GETDATE() AS savedOn, '<Automated>' AS savedBy INTO #12thMonthExpectedActivity FROM [wl03].[ExpectedActivity]
  WHERE period in(@plus11Month)
*/

The problem I have is defining the @query variable. I keep getting the same error:

  Msg 402, Level 16, State 1, Line 9 The data types varchar and date are
  incompatible in the add operator.

The code does work fine as long as I don't try to save it in a query. Any ideas? Thanks!


texts:
I have a table L(A, B, C). Functional dependencies are: A->B, A->C, B->A, B->C. Is this table in 3nf?
My thoughts: A and B are keys of this table. Table is in 3nf if there are no transitive dependencies between keys and non-prime attributes. There is a transitive dependence A->B->C, so it's not in 3nf? Or it does not work in this way, because B is a key? Hopefully I didn't confuse you too much. Thanks.


texts: Backup weekly current production db to Historical db server

texts:
I have a production sql db of 600gb and a historical server where i move the full db every month.
Now we want to have only the last 3 months information on the production server, and move every week data from the production DB to historical server, to then delete each register which have more than 90 days old.

I couldnt find the best way to do that.
Should i save the date of each register i put on the database to know when it was stored?
Does sql server have any function to know when a register was stored?

Both database are stored on AWS, so it´s easy to move data between them.
Thanks a lot!


texts: How to use bcp utility in a CLR procedure as a process

texts:
I made an CLR Stored Procedure and I want to call bcp utility as a process in it.
My project looks like :

[SqlProcedure]
public static int CmdShellBridgeBCP(string arguments)
{
       return processCMD("bcp.exe", arguments);
}

private static int processCMD(string fileName, string arguments)
{
     try
     {
           Process proc = new Process();
           proc.StartInfo.FileName = fileName;
           proc.StartInfo.Arguments = arguments;
           proc.Start();
           proc.WaitForExit();
     }
     catch (Exception ex)
     {
           return 1;
     }
     return 0;
}

And the stored procedure where I try to use this looks like :

set     @execcommand = @CSSdbname+'.BankCardtrxGPEtemp in '+@importdir + @Kfilename +' -c -S '+@CSSserver+' -r \n -T -C 1250 -e '+@importdir+'bcp.error -m 1000 >NUL'

exec    @result = dbo.CmdShellBridgeBCP @execcommand

Now the result is that nothing was imported in my BankCardtrxGPEtemp  table.
I have no errors, the return value is 0, not 1.

If I use

exec    @result = master..xp_cmdshell @execcommand, NO_OUTPUT

it works ok.
But I want not to use xp_cmdshell.

What is wrong with my code ?

Assembly has UNSAFE permission, because I have to use external shared folders to access files (txt, cvs, etc ...)


texts: SQL Server starts normally from Windows Services screen, but shows error through SQL Server Configuration Manager. Why?

texts:
I have a SQL Server 2005 instance that has worked fine for years. There have been no changes that I am aware of. Recently, I found that if I stop and then start the instance in SQL Server Configuration Manager, I get the error:

The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details

However, the service has indeed started, regardless of what the error says. If I dig through Event Viewer and the SQL Server ERRORLOG file, everything looks normal. There are no errors anywhere. In fact, if I start the service from the normal Windows Services screen (services.msc), it starts up immediately. Should I be concerned that it generates a false error through SQL Server Configuration Manager?

Also, is the Services section in SQL Server Configuration Manager just a "shell" of the full  Services screen, which is limited to SQL Server related services? Is there any reason not to stop and start SQL Server services from the full Windows Services screen?


texts:
The message is

  The log scan number (1166:312:1) passed to log scan in database
  'master' is not valid. This error may indicate data corruption or that
  the log file (.ldf) does not match the data file (.mdf). If this error
  occurred during replication, re-create the publication. Otherwise,
  restore from backup if the problem results in a failure during
  startup.

Can you help in resolving the issue.


texts: Traditional failover clustering vs. Availability Groups

texts:
I'm looking into Availability Groups to see if i can benefit from them, but I struggle to understand if I can or not.

Today, we are running a traditional failover cluster with around 150 databases. It is a collection of a lot of different stuff that is in a lot of cases hardly used, but we can't throw it away for a million different reasons. But prolonged downtime is unacceptable. The environment looks like this:

No single database is business critical (but the amount of DBs makes it critical)
Databases ranging from 10 MB to 10 GB
Avg. 30.000 Logical read/s, under 1 Physical read/s
Low CPU usage (below 20%)
Two physical servers in two data centers
Storage is on two VMAX 10k, one in each center, shared volumes distributed with VPLEX, so looing one center will not affect uptime on storage
No need for reporting or anything out of the ordinary
Most DBs are in simple mode, with a nightly full backup, business have no problem accepting the nightly backup as restore point
A rare few databases are running full mode and has 30 min transaction backups

The reason this solution was chosen at the time was licensing and administration reasons. We wanted to cut back on all the single SQL Servers doing nothing but cost us money. And we have removed about 60 SQL Server installations from the environment.

But now there is talk about how we should deal with this in the future, hardware replacement coming up in a year.

And the problem is, as I stated before, I can't quite get my head around if we can benefit from a change, maybe running Availability Groups on virtual machines instead?

So my question is:

What would you think is the best solution to look at given these factors?


texts: Why does AttachDBFile make everything slower?

texts:
In my ASP.NET app, I had tested a query against a 200.000 rows table with two connection strings, one connect to a remote SQL Server and once use AttachDBFile to attach to a database file in App_Data.

The query with the first connection is much faster than the later. Anybody could shed some light on this for me?


texts: Orphaned users with their respective database name

texts:
I need to get a list of the orphaned users in each DB, together with the database name in which they are found in. I did the below which gives the orphaned users in each DB, but I cannot figure out how to include another column next to each user found to show in which the DB. I tried using the DB_NAME() but to no avail.

use master;
GO

CREATE PROCEDURE orphaned_u
AS
BEGIN
DECLARE @command varchar(1000)
SELECT @command = 'use ?
if DB_ID("?")>4
EXEC sp_change_users_login ''Report'' '
EXEC sp_MSforeachdb @command
END

CREATE TABLE tempTable (
    user nvarchar(1000),
    User_sid sql_variant
)

INSERT INTO tempTable
EXEC master..orphaned_u

SELECT * FROM  dbo.tempTable

I have already looked at Find orphaned users but preferable I use the above code and find a way to display the database name in a column with the already built-in stored procedure sp_change_users_login ''Report''


texts:
A table with various prices per item have a trigger that will update price 3 when price 0 is changed.

The ERP that uses this database has a utility that when run updates the database with information coming from other locations.

It is possible, when this utility runs, inhibit execution of said trigger?

If it so, how can I identify when utility runs?


texts: How do I keep a SQL Server Profiler trace from jumping to the bottom entry?

texts:
When monitoring a trace in SQL Server Profiler, is there a way to prevent it from jumping to the most recent event without pausing the trace?

I'm often trying to scroll up and read descriptions of events, and as a new one comes in, it jumps to the bottom.


texts: Taking InnoDB MySQL/MariaDB cold file backup and restoring on new Omeka DB

texts:
I have an unresponsive Omeka MySQL/MariaDB database on a Windows server. It is unresponsive in the sense that it will not start from the XAMPP panel:

10:44:15 AM  [mysql]     Attempting to start MySQL app...  10:44:15 AM
[mysql]     Status change detected: running  10:44:19 AM  [mysql]
Status change detected: stopped  10:44:19 AM  [mysql]     Error: MySQL
shutdown unexpectedly.  10:44:19 AM  [mysql]     This may be due to a
blocked port, missing dependencies,  10:44:19 AM  [mysql]     improper
privileges, a crash, or a shutdown by another method.  10:44:19 AM
[mysql]     Press the Logs button to view error logs and check
10:44:19 AM  [mysql]     the Windows Event Viewer for more clues
10:44:19 AM  [mysql]     If you need more help, copy and post this
10:44:19 AM  [mysql]     entire log window on the forums

The MySQL error log doesn't seem to be very helpful:

InnoDB: using atomic writes. 2020-09-14 10:44:17 0 [Note] InnoDB:
Mutexes and rw_locks use Windows interlocked functions 2020-09-14
10:44:17 0 [Note] InnoDB: Uses event mutexes 2020-09-14 10:44:17 0
[Note] InnoDB: Compressed tables use zlib 1.2.11 2020-09-14 10:44:17 0
[Note] InnoDB: Number of pools: 1 2020-09-14 10:44:17 0 [Note] InnoDB:
Using SSE2 crc32 instructions 2020-09-14 10:44:17 0 [Note] InnoDB:
Initializing buffer pool, total size = 16M, instances = 1, chunk size
= 16M 2020-09-14 10:44:17 0 [Note] InnoDB: Completed initialization of buffer pool 2020-09-14 10:44:18 0 [Note] InnoDB: 128 out of 128
rollback segments are active. 2020-09-14 10:44:18 0 [Note] InnoDB:
Creating shared tablespace for temporary tables 2020-09-14 10:44:18 0
[Note] InnoDB: Setting file 'C:\xampp\mysql\data\ibtmp1' size to 12
MB. Physically writing the file full; Please wait ... 2020-09-14
10:44:18 0 [Note] InnoDB: File 'C:\xampp\mysql\data\ibtmp1' size is
now 12 MB. 2020-09-14 10:44:18 0 [Note] InnoDB: Waiting for purge to
start 2020-09-14 10:44:18 0 [Note] InnoDB: 10.4.11 started; log
sequence number 47055; transaction id 9 2020-09-14 10:44:18 0 [Note]
InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool
2020-09-14 10:44:18 0 [Note] Plugin 'FEEDBACK' is disabled. 2020-09-14
10:44:18 0 [Note] InnoDB: Buffer pool(s) load completed at 200914
10:44:18 2020-09-14 10:44:18 0 [Note] Server socket created on IP:
'::'.

The event logs indicates a corruption, however:

Error      9/14/2020 10:44:18 AM MariaDB               100
None     "Aborting" Error      9/14/2020 10:44:18 AM MariaDB
100         None     "Fatal error: Can't open and lock privilege
tables: Table '.\mysql\db' is marked as crashed and last (automatic?)
repair failed" Error      9/14/2020 10:44:18 AM MariaDB
100         None     "mysqld.exe: Table '.\mysql\db' is marked as
crashed and last (automatic?) repair failed"

I tried innodb_force_recovery set to 1, 2 and 3 but none worked. As I understand it, if I go any higher, it would result in data loss. So at this point, I think my options are pretty limited. I can't use any of the backup tools to take a data dump since they need the ability to log into the database. I can follow the steps here to physically make a cold backup of the database:
https://dev.mysql.com/doc/refman/5.6/en/innodb-backup.html
I can then try to recreate the blank database via Omeka/XAMPP and then restore the file. I was looking for feedback whether this seems like a reasonable plan or if there is an alternative in case others had faced a similar issue.


texts: SQL Server Linked Servers not connecting

texts:
I have configured linked servers with "connections will be made using the login's current security context", but can only make use of them when logged on to the server, not from another machine...

Works fine:

SSMS --> SQL_SERVER_A --linked server--> SQL_SERVER_B
The test connection to the linked server succeeded

Does not work:

SSMS --> ADMIN_PC --> SQL_SERVER_A ---linked server--> SQL_SERVER_B
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

I've ran the 'Kerberos Configuration Manager for SQL Server' and everything checks OK and user I'm using is the same.

Does someone have an idea what could be the problem?


texts: Transaction Log file size for Index reorg

texts:
I have a DB that is 100GB. It's fixed this way on purpose. It won't exceed this. I have two indices. One with a single value. One with three values.
I'm not a DBA. I'm a guy who's been tasked with working on SQL DB's part time on occasion. They were created by someone else and I'm on the steep part of the learning curve.
I have the transaction log set to 150GB. It works great until I automatically reorganize the indices. At this point I always get a transaction log full. Even if I set it to back up the log, then reorganize.
If I manually reorg it's fine, because I can do each index separately and I BU the trans log first.
So how big do I need the trans log to be, based on the above info, in order to be able to set up a maint task to reorg? Will it be any better if I do a rebuild instead?


texts: How do I rollback a transaction in v$transaction that's not in DBA_2PC_PENDING?

texts:
I have a hung open transaction (the client force-quit). I can see the transaction in v$transaction (it's currently open). I want to force-rollback that open transaction so I can continue to use the database.

There is the ROLLBACK FORCE command, however, all the docs online (and the oracle docs) mention taking the transaction id from DBA_2PC_PENDING. However, that table is empty!

How do I forcibly rollback a transaction that is visible in v$transaction but not in DBA_2PC_PENDING?


texts: PostgreSQL 9.5, getting "cached plan must not change result type" error

texts:
I had a really odd and unexpected behaviour in PostgreSQL. Debugging I found that the problem only occurred while including CREATE TEMP TABLE ... inside a function, and by using an alternative (in this case, I resorted to an array) the error would be gone. Perhaps any one experienced a similar error or knows what this error relates to? I might need to create temporary tables in functions sooner or later.

I'm porting an old Delphi XE6 program from a legacy proprietary database to PostgreSQL, changing the TxxTable components (proprietary) to TFDTable ones (FireDAC) pointing to a copy of the database in PostgreSQL 9.5.

Things were working peachy until I created this plpgsql function that inserts and updates some data here and there. I ran the function via PGConnection.ExecSQLScalar('SELECT do_stuff(:id)', FID);, where PGConnection is my TFDConnection to PostgreSQL (same where I have many other tables open and working well), do_stuff is the function in question, and FID is an ID I send as a parameter. The function runs well, as it does when running it from PgAdmin, but the connection becomes unstable as any already open table afterwards trying to do anything (insert/edit/move) will raise the following error:

  ERROR:  cached plan must not change result type
  STATEMENT:  FETCH FORWARD 50 FROM "357STM"`

FireDAC normally opens tables and queries using cursors:

  LOG:  execute 358STM: DECLARE "357STM" CURSOR WITH HOLD FOR SELECT *  FROM table

If I execute do_stuff(:id) via an TFDQuery component, the function itself fails (FireDAC executes it as a cursor), raising the same error stated above.

After tearing my hear out debugging, I realised that the only way to avoid the error was avoid creating any temporary tables inside the function. Whereas creating the function as CREATE TEMP TABLE tbl ON COMMIT DROP AS SELECT * FROM some_table WHERE .... or as CREATE TEMP TABLE tbl (id int, value text); the error was raised. Without CREATE TEMP TABLE, no errors raised.

Any clues will be highly appreciated!


texts: innodb_autoinc_lock_mode = 2, real danger if not using AUTO_INCREMENT columns

texts:
I've got a MySQL RDS database that uses pure generated GUIDs for key columns. The current value of the innodb_autoinc_lock_mode is 1, and due to some performance bottlenecks we want to change it 2. However, in MySQL documentation it says that:

If you are using statement-based replication, set innodb_autoinc_lock_mode to 0 or 1 and use the same value on the source and its replicas. Auto-increment values are not ensured to be the same on the replicas as on the source if you use innodb_autoinc_lock_mode = 2 (“interleaved”) or configurations where the source and replicas do not use the same lock mode.

Our replication mode is set to Mixed (default in RDS). So what I read here is telling me to keep innodb_autoinc_lock mode to 0 or 1. However the question is: Given that we are NOT using AUTO_INCREMENT columns, what would be the issue with setting innodb_autoinc_lock_mode to 2 with regards to statement-based replication and binlog replay (for recovery) ?


texts: I am trying to run this code and I get an error at line 11

texts:
CREATE TABLE IF NOT EXISTS `tblproduct` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(255) NOT NULL,
  `image` text NOT NULL,
  `price` double(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_code` (`code`)
)

INSERT INTO `tblproduct` (`id`, `name`, `code`, `image`, `price`)
VALUES
(1, '3D Camera', '3DcAM01', 'product-images/camera.jpg', '1500.00'),
(2, 'External Hard Drive', 'USB02', 'product-images/external-hard-drive.jpg', '800.00'),
(3, 'Wrist Watch', 'wristWear03', 'product-images/watch.jpg', '300.00');


texts:
In interviews for software developer roles SQL questions can sometimes come up. There is a classic question around switching values in a column, typically swapping the gender of people in a table for example:

id   name   sex   salary
------------------------
0    Tom    f     45000
1    Jane   m     22000

the typical question asks you to do this in one statement (and the answer is here: Switching values in a column with one update statement)

However my question is NOT that.

My question is what is the naive approach, that is, how I can temporarily store the values. In fact I'd like to do this two ways: 1. In a temporary column (that does not persist after the statement) and 2. In a temporary variable.


texts: Best solution for High Availability and SSRS on SQL Server 2008 R2?

texts:
I have 2 Physical Servers with SQL Server 2008 R2. – SQL Server 1(Active) & SQL Server 2 (Passive)
Web Application is developed using .Net 4.0 Framework.

I want to know the best solution to have high availability and also have SSRS for reporting.

Planned solution: Mirroring for Failover, and Transaction Replication for SSRS as the mirrored database can only be used for failover scenarios. SSRS will be on the Passive server, to reduce the load on the Active server.

Let me know if the solution is correct. Also suggest alternate approaches.


texts:
I recently added a global variable in mmy.cnf max_statement_time=120 ; to stop long running queries and to throw an exception. Its working fine. Then I am unable to run mysqldump for backing up all my databases , obviously its true that we have large databases so backing up using mysqldump will take hours since here I have added max_statement_time to 120 seconds only. So how I can pass this as a session variable thru mysqldump to set it to hours just for mysqldump and still global value remains 120 seconds itself. Apparently I am after something like below :

mysqldump --max_statement_time=1000000 -u root -pMYPASS --all-databases > file.sql

Do we have any option there ? I went thru the mysqldump and found that it has --variable-name=value , but its not accepting this. I am using Mariadb 10.2 version.


texts: Postgresql UPSERT contention on simple table

texts:
Postgresql 12.3: I am finding CPU spikes and wait times from UPSERT contention for what I would hope is a very simple table/use case.
The table definition is like so:
CREATE TABLE public.commit_check_queue
(
    id integer NOT NULL DEFAULT nextval('commit_check_queue_id_seq'::regclass),
    platform text COLLATE pg_catalog."default" NOT NULL,
    repository_id integer NOT NULL,
    sha text COLLATE pg_catalog."default" NOT NULL,
    next_check timestamp with time zone NOT NULL,
    CONSTRAINT commit_check_queue_pkey PRIMARY KEY (id),
    CONSTRAINT commit_check_queue_unique_platform_repo_sha UNIQUE (platform, repository_id, sha)
)

The UPSERT is defined in this function:
CREATE OR REPLACE FUNCTION public.add_commit_check(
    the_platform text,
    the_repository_id integer,
    the_sha text)
    RETURNS void
    LANGUAGE 'sql'

    COST 100
    VOLATILE

AS $BODY$
INSERT INTO commit_check_queue(platform, repository_id, sha, next_check)
VALUES(the_platform, the_repository_id, the_sha, NOW() + interval '5 minutes')
ON CONFLICT(platform, repository_id, sha)
DO UPDATE SET next_check = EXCLUDED.next_check;
$BODY$;

There are no other processes that read or write to this table, just dozens of concurrent AWS Lambda instances that call the function.
Yet if I leave this running a while it quickly builds up a lot of contention, e.g.
[5348]:LOG:  process 5348 acquired ShareLock on transaction 631218759 after 16076.829 ms
[5348]:CONTEXT:  while inserting index tuple (5,89) in relation "commit_check_queue"
[5348]:STATEMENT:  SELECT add_commit_check($1, $2, $3);

This "simple" function is also responsible for most of the CPU use even though there's a lot more complicate tasks being run.


texts:
The closest I can find to what I want to do is here, but my inexperience gets me lost. Basically, I want to copy a number of records, change one column and insert them back into the same table (so its almost a duplicate of the original data).

More info: Table menuship is a hash table for a food menu (thus each row in the table will identify a menu category (starter, maincourse or desert for example), and products (fish and chips).

Table structure:

menuship3, CREATE TABLE `menuship3` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `headhash` char(40) DEFAULT NULL,
  `menucardhash` char(40) DEFAULT NULL,
  `menucathash` char(40) DEFAULT NULL,
  `producthash` char(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `headhash` (`headhash`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8

In programming circles, I would say I want to "fork" my data...

If my table content was like so:

id, headhash, menucardhash, menucathash, producthash
-----------------------------------------------------
 1,  aaa    ,   aaa       ,   aaa      ,   aaa
 2,  aaa    ,   aaa       ,   aaa      ,   bbb
 3,  aaa    ,   aaa       ,   aaa      ,   ccc
 4,  aaa    ,   aaa       ,   bbb      ,   ddd
 5,  aaa    ,   aaa       ,   ccc      ,   eee
 6,  aaa    ,   other     ,   xyz      ,   fgi
 7,  aaa    ,   other     ,   xyz      ,   fgh

I want to duplicate all records with menucardhashaaa (rows 1-5), so I will end up with a table containing 12 records. The extra records will have new menucarhashqqq instead of menucardhashaaa.

id, headhash, menucardhash, menucathash, producthash
-----------------------------------------------------
 8,  aaa    ,   qqq       ,   aaa      ,   aaa
 9,  aaa    ,   qqq       ,   aaa      ,   bbb
10,  aaa    ,   qqq       ,   aaa      ,   ccc
11,  aaa    ,   qqq       ,   bbb      ,   ddd
12,  aaa    ,   qqq       ,   ccc      ,   eee

The result in effect means I have records that have similarity, records 1-5 are similar to records 8-12 - the differences being the id and menucardhash columns.

I was just going to select the records within PHP, change menucathash and send them back to the db, but I wondered if there was an SQL query that could do this for me and thus reduce cpu cycle overhead.

Is this possible via one or two queries, or am I better off having PHP carry the weight?

Thanks in advance...


texts: Is it possible to join derived tables to actual in Access 2007

texts:
Is it possible to join derived tables to actual tables in Access 2007 like in SQL Server 2008? I have an example below:

  SELECT *
    FROM
    --MyOtherTable is a "real" table
    MyOtherTable INNER JOIN
    --MyTable is a "real" table. MyDerivedTable is a "pseudo" table derived from MyTable
    (SELECT * FROM MyTable WHERE MyID >10) AS MyDerivedTable ON
    MyOtherTable.ID = MyDerivedTable.ID

is this possible in Access? The reason I am asking is because I would like to migrate some of my SQL Server 2008 queries into Access but most of my queries contain a lot of derived tables and I haven't been able to convert my queries if they require the use of a derived table.

I found this link which seems to suggest that it is not possible....

http://www.access-programmers.co.uk/forums/showthread.php?t=78833

Can someone please confirm? Thanks in advance.


texts: Need DDL for an Oracle Table that would be 1 row = 1 8K Block

texts:
For performance testing in our performance environment we want a means to turn over SGA the way production does. Our performance environment is a fraction of production and production's very nature makes it difficult to simulate on the performance environment.

When we test on the performance environment we tend to be very focussed on the type of workload we are testing and on the data that we have configured for the scenario. The result of all of this is that after a few runs we get performance improvements that we are not likely to see on production because, on the performance environment we are churning over the same data again and again in cache.

Clearing SGA (its an Oracle environment) also clears system level buffers for table definitions and schemas that negatively impact performance the first time we test. The second time we test the cache is too full of data and are not simulating the IO properly.

The solution we have imagined is to have an app read data from some purpose built table to 'turn over' SGA in the same way diverse data and workloads turn over SGA in production.

The imagined solution has this app connect to each database on the cluster in turn (bypassing the connection load balancer) and reading a row from the database from a purpose built table. The app round-robbin's the connections reading a row in turn and returning some small portion of the data but forcing Oracle to read the block. Given that the purpose built table contains more row data in KB's than the total SGA of the cluster, and given that we throttle the reads we can then say that we turn over all SGA in N minutes, which is what we want. With this we should be able to simulate SGA turn over at different rates and thereby simulate how our test load behaves under the varying load levels that production experiences.

The above spec imagines a row of fixed size that will be small enough to fit into a block but large enough that no more than one row will fit into a block. This will make the calculations easier when calculating for how many rows are required to turn over SGA or what rate to read the rows at to turn over SGA say in 5 or 10 minutes.

My question is this - what will the imagined row definition look like given that we use 8K block sizes. What other parameters will be advised and what gotchas do we have to consider in this imagined solution?

Any competing ideas are most welcome.


texts: Mysql 8, Query on user table garbles terminal. Is this an Installation issue?

texts:
I have Mytsql 8.0.1 Community Edition installed on Debian 10.1

Preparing to restore some dump files, I'm attempting to get a remote connection and cannot

ERROR 2003 (HY000): Can't connect to MySQL server on 'x.x.x.x' (111 "Connection refused")

I was attempting to look at users and see what was there and got this:

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| root             | tuivhk    |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.01 sec)

mysql> select user, host, authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| root             | %         | $A$005$sH
~m%:G'{a*wGdR.BP9eON1eqM95YlN.1CJB6YeiQbiQ7l33i.wZuD |
| root             | tuivhk    | $A$005$=BLI-+,?)~5,Zh0QZAK-aX/a+N++a+/8BS|+IX+++1+gO/0++Y60y_8ZA |
| +y_-+.i+f-_che+a | +-ca+h-_+ | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| +y_-+._e__i-+    | +-ca+h-_+ | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| +y_-+._y_        | +-ca+h-_+ | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| _--+             | +-ca+h-_+ | $A$005$d(+?aM^81^GZO+|Mg++Zdga4_Da-|UefKWXgyhBB++.DD+RYYDMDG__-+EA |
+------------------+-----------+------------------------------------------------------------------------+
6 _-+_ i+ _e+ (0.00 _ec)

+y_-+>

I could use some assistance figuring out what went wrong here.


texts: Is there a way to group the output of a query based on two rows

texts:
I'm not sure my question is asking the right question, so i will be as specific as possible in here.

I have the following data which i have changed the values of.

Below is the code I am using to pull this data through.

/*this is used for the open order book*/
SELECT co.[slsman]               AS Salesman,
       co.[cust_num]             AS "Customer Number",
       ca.NAME                   AS "Customer name",
       co.[co_num]               AS "Customer Order",
       ci.item,
       ca.curr_code,
       ci.price * ci.qty_ordered AS value
FROM   [co] co
       LEFT JOIN custaddr ca
              ON co.cust_num = ca.cust_num
                 AND co.cust_seq = ca.cust_seq
       LEFT JOIN coitem ci
              ON co.co_num = ci.co_num
WHERE  co.co_num LIKE 's%'
       AND co.stat = 'o'
       AND co.cust_seq = '1'
       AND item NOT LIKE '%carriage%'
       AND ci.stat NOT LIKE 'f'
       AND ci.stat NOT LIKE 'c'
ORDER  BY co.slsman,
          co.cust_num

What I am trying to do is merge all rows where the Customer order and the item part number are the same, and sum all the costs of these rows. I have tried adding a group by before the order by, but i understand after some reading that this does not work how i thought it did.

GROUP BY  co.co_num,
          ci.item

which gives the following error

  Msg 8120, Level 16, State 1, Line 3 Column 'co.slsman' is invalid in the select list because it is not contained in

either an aggregate function or the GROUP BY clause.

Below is what i want to be outputting. As you can see, i don't want to merge any cell text values, just the value one where customer order and item are the same.


texts: Streaming replication between postgresql 9.2 and 9.3

texts: Does anyone know if it's problematic to have a postgresql 9.3 slave setup to do streaming replication from a postgresql 9.2 master?


texts: mysql workbench connection type tcp/ip over ssh with libpam-google-authenticator enabled

texts: I'm attempting to connect to my MySQL Database using the Connection Method "Standard TCP/IP over SSH". I filled out all the SSH and MySQL connection details correctly, however I get the message: "Authentication error." because I'm protecting SSH with Two Form Factor Authenticator (libpam-google-authenticator). Is there another way to connect and resolve this issue?


texts:
I have come across a SQL 2005 server that uses SQL mail to send emails. This setup works fine as long as the MAPI profile points to an Exchange 2003 server, but breaks when the profile points to an Exchange 2010 server. The error: xp_sendmail: failed with mail error 0x80004005

Would updating the MAPI client help fix this, or is an exchange 2010 incompatibility?


texts:
I'm on SQL Server 2014 and I need to clean a table from duplicates. Problem is: if a duplicate has references in other tables, that references should be updated to reference the chosen non-duplicate entity.

E.g., say a have a Person table:

+------+-------+-------+------------------------+
|  id  | fname | lname | mail                   |
+------+-------+-------+------------------------+
| 1111 | John  | Smith | john.smith@example.com |
| 2222 | J.    | Smith | john.smith@example.com |
| 3333 | john  | smith | john.smith@example.com |
| 4444 | Smith | John  | john.smith@example.com |
+------+-------+-------+------------------------+

I need to delete 2222, 3333, 4444 and replace references around the database to these with a reference to 1111. After this operation, a unique index on mail will be created.

My approach would be gathering all the distinct mail and building a  id => [ ids ] map (e.g. 1111 => [ 2222, 3333, 4444]) and with a scripting language like Perl or PHP update all the tables which may have references to the duplicates and setting the correct id.

Since there are thousands of users and hundreds of tables with relations to them, I wonder if operations like this could be done directly in SQL Server function, with something like:

DELETE FROM [Person] WHERE [id] IN (2222, 3333, 4444) UPDATE REFERENCES WITH 1111


texts: Does PostgreSQL support multi-threaded replication?

texts:
Is PostgreSQL replication single-threaded ? Are there any tools to achieve multi-threaded replication ?

I'm asking this since Mariadb support this and currently I'm learning both these dbs.


texts: Foreign key subsumed by unique constraint in MariaDB/MySQL

texts:
I'm using a "migrations" approach to upgrading/downgrading table structure (Laravel). Essentially tables are added and altered as the application grows, but each "upgrade" script has an appropriate "downgrade" script to revert the changes and restore the structure to the previous version.

I want to implement the relationship "All actors are users, but not all users are actors" (typical one-to-zero-or-one relationship). I do this by creating a foreign key from actors to users, then adding a unique constraint to the FK column.

The problem: When dropping the unique constraint (to "downgrade" to the previous version), I get an error that the constraint is needed by the FK. This happens no matter whether there is data in any of the tables or not, and happens in both MySQL and in MariaDB. What am I missing here? As much as I know, this is the correct way to implement one-to-zero-or-one relationships...

Minimum working example:

App version 1 ("We only have users"):

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

App gets upgraded, v.1.5 ("We have users AND actors, but each actor must have a user account to login").

CREATE TABLE `actors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `subsumed_by_unique_later` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

App gets upgraded to version 2 ("Each actor may only have one user account"). A one-to-zero-or-one relationship needs to be enforced:

ALTER TABLE `actors` ADD CONSTRAINT this_subsumes_fk UNIQUE (`user_id`);

During testing, we need to revert to version 1.5, where the unique index was not there yet:

alter table `actors` drop index this_subsumes_fk;

And here's the error from dropping the index:

Cannot drop index 'this_subsumes_fk': needed in a foreign key constraint

The "Show create table.." output at this point is the following; both the unique constraint and the FK are there!!

CREATE TABLE `actors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `this_subsumes_fk` (`user_id`),
  CONSTRAINT `subsumed_by_unique_later` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


texts: How to represent it with UNIQUE indexes and foreign keys?

texts:
"Group" is a group of pupils. "Topic" is a topic of lessons.

To every group corresponds exactly one topic. (So there may be multiple groups per topic.)

To a pupil corresponds multiple groups, but maximum one group per topic.

How to implement this in terms of UNIQUE indexes and foreign keys in MySQL?


texts: With Postgres, can I make a rule that will not recurse?

texts:
The rule is already in place, and I'm hoping to fix the rule itself rather than change the whole process on how this works due to some bureaucracy I'd rather not deal with. Or in other words, I know there are other ways to achieve my goal, but I specifically want to know if it can be done somehow with a rule.
The current rule is that when an update occurs, another update fires to concatenate a few fields together. That update performed by the rule seems to call the rule again and generates an "infinite recursion" error.
Rule example:
ON UPDATE TO people DO
UPDATE people
SET "fullname" = concat(new."firstName", ' ', new."lastName")
WHERE people.id = new.id

Is there any way to tell the update that the rule does to not then call the rule again? Or to flag the rule with some sort of runOnce? It's my understanding rules alter the query, so I can't do something simple like AND people.fullname != new.fullname


texts: preserve generate_series rows through joins

texts:
I have a histogram query that filters the entities counted by conditions that can only be checked if joins are used.

The query looks something like this:

SELECT gs.t, COUNT(packets.id) FROM packets
RIGHT OUTER JOIN
  (SELECT generate_series(NOW() - '72 hours'::interval, NOW(), '1 hour') AS t) gs
ON packets.transceived_at > gs.t AND packets.transceived_at <= gs.t + '1 hour'
JOIN devices ON devices.id = packets.device_id
WHERE devices.mandate_id = 3
GROUP BY gs.t;

Sadly this will omit rows where there's no packet that was received between gs.t and gs.t + '1 hour'.

This does not happen when I don't join devices, but I need that table (and others in derivative queries) to filter some devices.

Is there a way to still get all rows of generate_series even when using joins?


texts: How to optimize SQL query which involves GROUP_CONCAT function with ORDER BY

texts:
I wrote the following SQL query to group results and then sort results in each group by date:

SELECT
origin,
provider,
SUBSTRING_INDEX(GROUP_CONCAT(price
            ORDER BY departure_date
            SEPARATOR ','),
        ',',
        10)
FROM
    `flights`
WHERE
    origin = 'LHR' AND destination IN ('FCO', 'CDG', 'AMS')
        AND cabin_type = 'ECONOMY'
GROUP BY origin , provider;

Second query:

SELECT
destination,
provider,
SUBSTRING_INDEX(GROUP_CONCAT(price
            ORDER BY departure_date
            SEPARATOR ','),
        ',',
        10)
FROM
    `flights`
WHERE
    origin = 'LHR' AND destination IN ('FCO', 'CDG', 'AMS')
        AND cabin_type = 'ECONOMY'
GROUP BY destination, provider;

InnoDB table flights is quite simple:

+----------------+---------------+------+-----+-----------+----------------+
| Field          | Type          | Null | Key | Default   | Extra          |
+----------------+---------------+------+-----+-----------+----------------+
| id             | int(11)       | NO   | PRI | NULL      | auto_increment |
| origin         | varchar(255)  | NO   | MUL | NULL      |                |
| destination    | varchar(255)  | NO   | MUL | NULL      |                |
| departure_date | date          | NO   | MUL | NULL      |                |
| price          | decimal(14,2) | NO   |     | NULL      |                |
| provider       | varchar(255)  | NO   |     | airnet    |                |
| cabin_type     | varchar(255)  | YES  |     | ECONOMY   |                |
| created_at     | datetime      | YES  |     | NULL      |                |
| updated_at     | datetime      | YES  |     | NULL      |                |
+----------------+---------------+------+-----+-----------+----------------+

I have indexes on columns: origin, destination, departure_date. And approx. 10 million records in the table.

Above-mentioned query takes about 200 to 600 milliseconds. I tried to use profiler and found out that most of the time is spent on Creating sort index (approx. 98% of whole query time).

Is there any way to optimize this query. I tried compund index on origin, provider, departure_date, but that did not seem to help. Maybe configuration should be tweaked more and if so which configuration variables must be considered?

Machine has 4 GB of RAM, 2 vCPUs and dedicated for DB only (10.3.13-Maria DB).


texts: How to move a SQL Agent Job to another Server?

texts:
I'm moving from SQL Server 2005 to SQL Server 2008R2, and I see there is one job in the SQL Server Agent Jobs that I need to move but I see it also in the SSIS---> Stored Packages\MSDB\File Manipulation.
Not sure if I right click on it and choose export, would that affect the production server if it wont be working in the new dev server? or how I can import just a copy of it to the other new development SQL server 2008?

Thanks for any advice or direction.


texts: Update fields that contains certain prefix by lookup table

texts:
I have a temporary table #NAMEOLD_NAMENEW which contains a column NAMEOLD and a column NAMENEW where every row contains the old and new value of a value to be updated. This table consists of roughly 2.000 rows. I created a clustered index on NAMEOLD.

The temporary table will be used to update certain fields in the database. I know which columns in which tables need to be updated (roughly 25 columns over 20 tables).

Now, the fields that need to be updated contain the string DOMAIN\ as a prefix and after that a NAMEOLD value of the temporary table.

What I want to do is: use the temporary table to replace the full value of the string DOMAIN\ plus the string after that with the appropriate value in the #NAMEOLD_NAMENEW table.

Example:

Example data in #NAMEOLD_NAMENEW:

  NAME_OLD NAMENEW
  user1 user5
  user2 user6

Example data in TABLE1:

  ColumnWithName1
  DOMAIN\user1
  DOMAIN\user2

Now running the query should update TABLE1 to:

  ColumnWithName1
  user5
  user6

I think that scanning the full table multiple times will not perform well enough (SELECT on certain tables already takes minutes).

What I thought of is typing out all the columns that need to be updated, and then updating them with a regex with a capture group. Something like this (non-working code):

UPDATE [dbo].[Table1]
SET [ColumnWithName1] = (SELECT [NAMENEW] from new_old WHERE [NAMEOLD] = [/1]) -- first CAPTUREGROUP??
FROM #NAMEOLD_NAMENEW new_old
WHERE [ColumnWithName1] like '%DOMAIN\(.*)'

Here the capture group does not work since it does not seem to exist in T-SQL. Furthermore, using the nested SELECT is I believe a bad practice for some reason.

Is it possible to do something like this in T-sql? Or is there perhaps even a better approach?


texts:
I am looking for information on recyclebin extents.

When dropping a table its segments are renamed to 'BIN$...' system generated names and the extents are hidden.

Will those extents still stop me from shrinking a data file (or tablespace)? If so I would like to account for them in the script.

Currently I am using a query inspired by AskTom's maxshrink.sql:

select tbs.TABLESPACE_NAME, df.FILE_NAME,
    round(MAX(e.BLOCKS+e.BLOCK_ID+1)*tbs.BLOCK_SIZE/1024/1024,2) "MinMB",
    round(MAX(df.BYTES)/1024/1024,2) "FileMB"
  from DBA_TABLESPACES tbs
  LEFT JOIN DBA_DATA_FILES df on tbs.TABLESPACE_NAME = df.TABLESPACE_NAME
  LEFT JOIN DBA_EXTENTS e on df.FILE_ID = e.FILE_ID AND df.RELATIVE_FNO = e.RELATIVE_FNO
  -- WHERE
  --   tbs.TABLESPACE_NAME like 'MY%'
  GROUP BY df.FILE_ID, df.RELATIVE_FNO, tbs.TABLESPACE_NAME, tbs.BLOCK_SIZE, df.FILE_NAME
  ORDER BY 1,2;

Update: Initially my question also asked why I no longer see the 'BIN$' segments created by Flashback Drop Table anymore in 12.2 anymore. However that was an error on my side, my test tables had simply no segments to begin with.


texts:
I have some problem with creating user in Enterprise Oracle Database System. A could'n create user using command like

create user name identified by password;

because of exception:

  ORA-65096: invalid common user or role name

So I found solution on Google:

alter session set container=PDBORCL;

and after that - is work. User was created.

But I can't log in to this account. What is wrong? I add all privileges to this account (CREATE SESSION of course too). I got error:

  ORA-01017: invalid username/password; login denied.

What the hell is going on?


texts: Granting access to all tables for a user

texts:
I'm new to Postgres and trying to migrate our MySQL databases over.  In MySQL I can grant SELECT, UPDATE, INSERT, and DELETE privileges on a low privileged user and enable those grants to apply to all tables in a specified database.  I must be missing something in Postgres because it looks like I have to grant those privileges for each table one at a time.  With many databases and hundreds of tables per database that seems like a daunting task just to get off the ground.  In addition, once a database is in operation, adding tables happens frequently enough that I wouldn't want to have to grant permissions each time unless absolutely necessary.

How is this best accomplished?


texts: Need a log based replication / CDC solution to publish postgres to oracle subscribers

texts:
We have a moderately large postgres 9.1 db that is currently 3TB and will likely grow much larger over the next couple of years. we need a reliable and fast solution for moving this data into either oracle 11G or sql server 2012 databases. we need a log based solution like replication or CDC with a minimal foot print of the postgres server and not looking for a  code based ETL solution like SSIS or SAP Data Services.

It should also be able to effectively handle blob / spatial data that is stored in postgres


texts: Does schema normalization enhances query efficiency

texts: I understand the importance of normalization in reducing data redundancy and various anomalies but I am wondering if it can also enhance the performance of your query?


texts: Grant privileges to tables in all (dynamic) schemas

texts:
We've setup an AWS Database Migration Service which migrates schema and tables from multiple sources.

The rule for migrating from source can be a wildcard on schema, meaning a schema could 'pop up' arbitrarily as it is created in the source.

From all the various granting of privileges I've seen in PostgreSQL, all seem to specify a specific schema, or loops through each existing schema.

Is there not a way to grant certain privileges to every current and future schema in a given database?

Ideally something like this would be great:

ALTER DEFAULT PRIVILEGES FOR ROLE my_role IN SCHEMA "*" GRANT SELECT ON TABLES TO my_role;

I'm aware the super_user role can be granted to a user to make this happen, but that seems awfully dirty for someone who should only have read permissions.

Appreciate the help.


texts:
I do not know English, sorry :(

example table:

CREATE TABLE IF NOT EXISTS `__example__` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`amount` decimal(10,2) DEFAULT NULL,
`fromDate` datetime DEFAULT NULL,
`toDate` datetime DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

and rows:

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (1, 1000.00, '2016-01-16 17:05:40', '2016-01-26 17:05:45');

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (2, 1000.00, '2016-01-26 17:05:40', '2016-02-05 17:05:45');

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (3, 1000.00, '2015-12-26 17:05:40', '2016-01-05 17:05:45');

INSERT INTO `__example__` (`ID`, `amount`, `fromDate`, `toDate`)
VALUES (4, 4000.00, '2015-12-26 17:05:40', '2016-02-04 17:05:45');

I want to see:

year | month | sum
2015 |    12 | 1400.00
2016 |    01 | 4700.00
2016 |    02 | 900.00

Example:

2 rows started at 12/2015 and the sum thereof is 5000.00(1000+4000).

But 12/2015 sum column should be 1400.00.

How can I do that with SQL query? Help me, please.


texts: Why is replication attempting to distribute NULL values into Identity columns

texts:
We are sporadically getting this error:

Explicit value must be specified for identity column

in our replication environment. I have checked the transaction command that is reporting the error and it contains three inserts for three different tables. All three inserts DO HAVE the identity value in them. If I remove the primary keys from the tables and allow nulls then three records with NULL in what was the primary key field are inserted AS WELL AS three records with the correct primary key values (as seen using sp_browsereplcmds).

I remove the null value records and re-enable the primary keys, identity and not for replication flag and replication continues as normal.

I'm really stumped and can't figure out what is going on, any suggestions appreciated.

Currently I'm considering modifying the automatically created MSSPs so that if it finds a null in what would be the primary key value it ignores it, but this shouldn't be necessary.

Cheers


texts: Is any tool for taking MySQL Dump Automatically with a particular time?

texts:
I am using VS2010 and MySQL for my project. My client wantw to take mysqldump every day like a daily backup. I want to give my client a tool for taking mysqldumps on a particular time schedule.

Do you know any tool that can fulfill my requirement?


texts: Trigger to compare inserted value with value from another table

texts:
I have two tables:

Stockitem (serialno, qty)    serialno is the primary key
and
Order(serialno,orderno,qty) orderno and serialno are the primary keys.

I want to create a trigger in order to ensure that when I input the quanitity in the Order table, the quantity should be less than that of the Stockitem. How can I accomplish this? I am using Oracle SQL.


texts: Replication: Procedure or function sp_MSupd_PROC has too many arguments specified

texts:
Source is one service pack behind destination (2012).  I know service packs can be issues here, but it looks like this is only for versions 2000 and 2005.
When using sp_helptext sp_MSupd_PROC the objects uses a CASE WHEN with the binary input (last parameter) and updates or adds columns with the other parameters where the primary key column is equal to the primary key passed in.  For non-SQL types reading this - is there an Entity Framework approach to this, as this entire replication process looks slow and completely outdated and I highly doubt is using efficient .NET code?
Using sp_help on the objects on both the source and destination show that they're both the same.  Also, I did look at this briefly, but the no specific transaction number comes with the error - so I can't do his second step.
I did a quick verification on the data in the table, confirming that it matches with the parameters shown from the sp_helptext.

I haven't found anything else I can do to troubleshoot here and updating service packs right now isn't an option.  Note that this replication was fine yesterday and the day before yesterday, so what would cause it to be fine for multiple days, but now not be fine.  I realize that if users change or update the source or destination object, that would cause problems, but I don't see that being the case.

Thanks.


texts: What does pg_stat_all_tables.idx_scan actually mean?

texts:
We have a busy PostgreSQL 9.5 server with OLTP-like traffic where pg_stat_all_tables.idx_scan values go up with considerate rate while we're having performance problems.

Does that (idx_scan increasing) actually mean that

the system is actually running lots of full scans through indexes (meaning, reading the whole index from the disk if not already in the cache), or
the system is actually getting some only some tuples ("rows") from those indexes (that is, using the index as intended)?

If the option 1 is true, how to figure out how to get system into state 2? Do I need some additional indexes or are some of my queries bad? All queries get nice performance while the system is under low load but during very high load pretty much any query can get unexpectedly poor execution time.

(The documentation at https://www.postgresql.org/docs/current/monitoring-stats.html only says "Number of index scans initiated on this table".)


texts: Which version of SQL Server 2016 supports R

texts:
Microsoft buying REvolution results (amongst other things) in the integration of R in SQL Server 2016.

Looking through the features supported by the SQL Server 2016 I cannot find R-Support. Does anyone know to what feature R-Integration belongs to or what version(s) integrate this feature?


texts: How to implement a query insert into BEFORE INSERT trigger syntax

texts:
I want to insert data using trigger into 2 tables. I want to implement my query insert to a before insert trigger. Since I was newbie for using MYSQL, I'm confused how to implement it.

insert into db1.table1 (id,name,date,address,nophone)
select a.id,a.name,a.date,a.address,a.nophone from db2.table2 a
where date(a.date) = '2015-08-19' and a.nophone not in (select nophone from db1.table3);

before this code is executed, I do select data like this :

SELECT a.ID, a.name, a.date, a.address, a.nophone FROM db2.table2 a
WHERE a.nophone in (SELECT nophone FROM db1.table3)
and DATE(a.date) = '2015-08-19';

if the result show that a.SenderNumber is Null, then do insert to table1 if else insert into table4

how to implement what I want into a before insert trigger


texts:
I have a column (DividendReinvested) that is initially NULL but gets calculated based off the previous DividendReinvested column.

The UniqueClassRN column is partitioned by ClassId. So how do I ensure the anchor in the below CTE is set for correct relevant ClassId?

The below code works but only if there is not different ClassID values.

Are there any easier solutions such as APPLY?

DECLARE @AnnualisedReturnTbl AS TABLE
(
    UniqueClassRN int,
    ClassID varchar(80),
    ValuationDate datetime,
    ValuationPercChange DECIMAL(32,12),
    LatestExDivDate datetime,
    DividendReinvested DECIMAL(32,12)
);

WITH CTE
AS
(
  SELECT
    UniqueClassRN = ROW_NUMBER() OVER (PARTITION BY ClassId ORDER BY ClassId, ValuationDate), -- partition by classid
    ClassId,
    ValuationDate AS ValuationDate,
    ValuationPercentageChange,
    LatestExDivDate,
    NULL AS DividendReinvested
  FROM ReportData
  WHERE Id = @Id
)
INSERT INTO @AnnualisedReturnTbl
SELECT *
FROM CTE;

WITH DividendReinvested_CTE
AS
(
    SELECT UniqueClassRN, ClassID, ValuationPercChange, ValuationDate,     LatestExDivDate, RunningDivReinvested = CAST(DividendReinvested AS     DECIMAL(32,12))
    FROM @AnnualisedReturnTbl
    WHERE UniqueClassRN = 1   -- HOW TO ENSURE IT USES CORRECT CLASS???

    UNION ALL

    SELECT a.UniqueClassRN, a.ClassID, a.ValuationPercChange,     a.ValuationDate, a.LatestExDivDate,
                CAST(ISNULL(d.RunningDivReinvested,0) * (1 +     (a.ValuationPercChange / 100)) AS DECIMAL(32,12)) as 'DividendReinvested'
    FROM DividendReinvested_CTE d
    INNER JOIN @AnnualisedReturnTbl a
        ON a.ClassID = d.ClassID
        AND a.UniqueClassRN = d.UniqueClassRN + 1
)
UPDATE a
    SET DividendReinvested = d.RunningDivReinvested
FROM DividendReinvested_CTE d
    INNER JOIN
        @AnnualisedReturnTbl a
    ON a.UniqueClassRN = d.UniqueClassRN;

Here is the output with a single ClassID (this data is correct)...

However, If there is a second different ClassID then the data results are skewed for ClassXY1234.


texts: Should i use subquery to limit table before a join?

texts:
In the case of a join followed by a where clause, would it be better to use a subquery to limit the results, and then do the join?  Example:

SELECT *
FROM Customers
    NATURAL JOIN Orders
WHERE shipped=1

In this case, it seams the DBMS will join the entire Customers table with the entire Orders table, and then filter the results based on the where clause. An equivalent query using subquery would be:

SELECT *
FROM Customers
    NATURAL JOIN (SELECT *
                     FROM Orders
                     WHERE shipped=1) AS O

Here, there is potentially a smaller Orders table to JOIN with.  Likewise, if there was a where clause that limited both Customers and Orders:

SELECT *
FROM Customers
    NATURAL JOIN Orders
WHERE country='US' AND shipped=1
(assuming country attribute belongs to Customers table)

An equivalent subquery query:

SELECT *
FROM (SELECT *
            FROM Customers
            WHERE country='US') AS C
    NATURAL JOIN (SELECT *
                                 FROM Orders
                                 WHERE shipped=1) AS O


texts: Why is an additional filtered statistic being ignored (EAV schema)?

texts:
I'm trying to improve a row estimate for this sub-query (of a larger query).  The estimate is showing 1266 rows.  The actual is 117k rows.  This particular property (EAV schema) only has two values defined for it (2 and 3):

declare @pPropVal smallint = 2;

select Value, ObjectId
  from Oav.ValueArray PropName
 where PropName.PropertyId = 897
   and PropName.Value  = @pPropVal
option (recompile)

The query plan shows the proper seek predicate on index IX_ValueArray_PropValObj on PropertyId and Value as expected.

(A) As an attempt to improve row estimates, an additional statistic was added which brought the row estimate up slightly to 3041:

create statistics [ST_SomePropertyName] ON [Oav].[ValueArray](PropertyId, Value, ObjectId)
 where
     (
             PropertyId = 897
         and [Value] is not null
     )
  with fullscan

The histogram shows a single row.  The HI key is just the PropertyId (the first column) which is not that useful so as I understand it, it is using the density information.

RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS  DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
897             0           196026   0                    1

All density Average Length  Columns
1           4               PropertyId
0.5         8               PropertyId, Value

Name    Updated Rows    Rows Sampled    Steps   Density Average key length  String Index    Filter Expression   Unfiltered Rows
ST_SomePropertyName May 20 2014  2:01PM 196026  196026  1   0   8   NO  ([PropertyId]=(897) AND [Value] IS NOT NULL)    9317055

(B) Since there is a filter on PropertyId = 897, I thought I could re-create the statistic like this:

create statistics [ST_SomePropertyName] ON [Oav].[ValueArray](Value, ObjectId)
where
    (
       PropertyId = 897
       and [Value] is not null
    )
 with fullscan

The histogram looks useful to my eyes but the estimator appears to be ignoring it because it reverts to the original estimate of 1266.

RANGE_HI_KEY  RANGE_ROWS  EQ_ROWS  DISTINCT_RANGE_ROWS   AVG_RANGE_ROWS
2             0           117760   0                     1
3             0           78266    0                     1

All density   Average Length  Columns
0.5           4               Value
5.101364E-06  12              Value, ObjectId

Name    Updated Rows    Rows Sampled    Steps   Density Average key length  String Index    Filter Expression   Unfiltered Rows
ST_SomePropertyName May 20 2014  2:04PM 196026  196026  2   0   12  NO  ([PropertyId]=(897) AND [Value] IS NOT NULL)    9317055

(C) It does work to filter to a fixed value (and not even need the 2nd two columns) but that is not a very practical solution.  This gave the exact estimate 117k.

create statistics [ST_SomePropertyName] ON [Oav].[ValueArray](PropertyId)
 where
     (
             PropertyId = 897
         and [Value] = 2
     )
  with fullscan

histogram:

RANGE_HI_KEY   RANGE_ROWS  EQ_ROWS  DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
897            0           117760   0                    1

(D) (Added to original question) An approach of limiting the value to a smaller range helps.  But if the range values are not uniform or the value was a string based field or not even known this may not be a good workaround in general:

CREATE STATISTICS [ST_ListUnderBrand_897] ON [Oav].[ValueArray](PropertyId, Value)
WHERE
  (
      PropertyId = 897
      and [Value] >= 1 and [Value] <= 20
  )
  with fullscan

This gives estimates of about 16k.  Changing the [1,20] to the exact [2,3] gives estimates of ~80k.  It seems obvious that the true range of Values from the table data are not really used (since it is 2nd column) and this is some estimate based mostly on the filter range.

Please note the Value field is a sql_variant but I don't think that is related as the query plan does not show any implicit conversions.

Why doesn't SQL Server use the statistics from B?   Should it?

Are there other options available to fix this?