texts: Finding the different rows from two resultsets based on one of the resultsets (MINUS, NOT EXISTS)

texts:
I'm testing queries on two resultsets that have the same value in certain columns on Oracle 11g. I want to exclude the rows that share these columns from my final result based on the first table. Say I have the following resultsets:

Table1 (Where id is a number)

| id | col_a | col_b | col_c | col_d | col_e
|  1 |   a1  |   b1  |   c1  |   d1  |  100
|  2 |   a1  |   b2  |   c2  |   d1  |  200
|  3 |   a2  |   b3  |   r1  |   s1  |  300
|  4 |   a9  |   b9  |   r9  |   s9  |  400

Table2 (where id is a letter)

| id | col_a | col_b | col_c | col_d | col_e
|  A |   a1  |   x1  |   c1  |   d1  |  100
|  B |   a1  |   y2  |   c2  |   z1  |  200
|  C |   a1  |   b3  |   c3  |   s1  |  300
|  D |   a0  |   b0  |   r0  |   s0  |  400

My expected result would be the rows that are different from the columns I am trying to evaluate based on Table1. Evaluating that if col_a and col_c and col_e are the same, remove them from my resultset. Preferrably something like this:

TableR1

| id | col_a | col_b | col_c | col_d | col_e
|  3 |   a2  |   b3  |   r1  |   s1  |  300
|  4 |   a9  |   b9  |   r9  |   s9  |  400

Since I only really care about col_a, col_c and col_e, this is valid too:

TableR2

| col_a | col_c | col_e
|   a2  |   r1  |  300
|   a9  |   r9  |  400

I would like to know if I can use, in Oracle 11g:

MINUS
NOT EXISTS
Something else that I'm overlooking (Like JOINs)

I've tried with the first two by doing something like this which gives me something like TableR2:

SELECT  col_a,
        col_c,
        col_e
FROM    Table1

MINUS

SELECT  col_a,
        col_c,
        col_e
FROM    Table2

And something like this which gives me something like TableR1:

SELECT id, col_a, col_b, col_c, col_d, col_e FROM
Table1
WHERE NOT EXISTS
    (SELECT 1 FROM
    Table2
    WHERE Table1.col_a = Table2.col_a
    AND Table1.col_c = Table2.col_c
    AND Table1.col_e = Table2.col_e)

Which is the best approach, in accuracy and speed, for finding the rows that are different using those criteria using Table1 as a base?

I think using MINUS would give me the expected results (albeit with less data than what I'd optimally like to look at) but I've seen the NOT EXISTS approach being used and I'm not sure if I could use something similar so I can look at the remaining columns.


texts: How do you restore to a point in time between the last log backup before a full backup and the full backup?

texts:
Imagine the following timeline:

T0 = full backup
T1 = log backup
T2 = log backup
T3 = log backup
T4 = full backup
T5 = log backup

After all the above have transpired, if you still have all the backup files, how would you restore to a point in time between T3 and T4?

My understanding is that the full backup at T4 should lead the log file to mark transactions between T0 and T4 as inactive, so in theory, the transaction log may no longer contain transactions from the time before T4. In particular, the log backup at T5 will only contain transaction information from the time after T4. Is this correct?


texts: How to preserve ID generated from a PRIMARY KEY when moving data into a new table?

texts:
I have a large table (~4 million rows, ~100 fields) which I need to split into a few smaller tables by type so it doesn't become unmanageable as it grows.

Each entry has an ID which is an automatically generated primary key. These keys need to be preserved when the data is moved to new tables. Additionally, new unique keys will need to be generated; and these will need to be unique across all of the smaller tables, rather than simply within each table itself.

I was thinking that a way to do this would be to transfer the ID into a new field (or a non-automatically generated primary key field?) when transferring the data into the new tables. Once the data is transferred, I could strip down the original table so it is just a primary key so it can continue automatically generating keys for any additional data - i.e. before inserting data into one of the new tables, get a new primary key from the original table.

However, that seems rather clumsy!

Also it leaves the question, if a user is referring to an ID without knowing what type the data is (i.e. what table it will be found in), how could they be 'directed' to the correct table?

I'm sure there must be a better way to do this?


texts: Added R Services to SQL Server 2016 Enterprise - Cannot get past Msg 39021

texts:
SQL Server 2016 SP1 CU8

(Have already read the entirety of this answer Problem getting SQL Server 2016 R Services (In-Database) working, and all cited links)

We added R Services - In-database - from SP1 media, then re-ran the CU8 update, which detected and successfully patched the R Service.

The NT Service\MSSQLLaunchpad login has all 4 permissions stated for Local Policies => User Rights Assignment - viz:

Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
Bypass traverse checking (SeChangeNotifyPrivilege)
Log on as a service (SeServiceLogonRight)
Replace a process-level token (SeAssignPrimaryTokenPrivilege)

Server Windows Login MySQlServer\SQLRUserGroup is a server principal with public role membership.

All cited windows logins have express "Full" permissions to the ExtensibilityData folder, including all 20 of the MSSQLSERVERnn folders.

SQLRUserGroup has Full Control of the Working Directory.  (Services Restarted).

select CAST(SERVERPROPERTY('IsAdvancedAnalyticsInstalled') as int) as IsMLServicesInstalled
     , CAST(value_in_use as int) as ExternalScriptsEnabled
     , COALESCE(SIGN(SUSER_ID(CONCAT(CAST(SERVERPROPERTY('MachineName') as nvarchar(128))
                                   , '\SQLRUserGroup'
                                   , CAST(serverproperty('InstanceName') as nvarchar(128)
    )))), 0) as ImpliedAuthenticationEnabled
     , coalesce((select cast(r.value_data as int)
                   from sys.dm_server_registry as r
                  where r.registry_key like 'HKLM\Software\Microsoft\Microsoft SQL Server\%\SuperSocketNetLib\Tcp'
                    and r.value_name = 'Enabled'), -1) as IsTcpEnabled
  from sys.configurations
where name = 'external scripts enabled';

returns all 1's.

This has been run and the services restarted, including LaunchPad:

exec sp_configure 'external scripts enabled', 1;
reconfigure with override;

declare @login_name nvarchar(255) = CONCAT(cast(SERVERPROPERTY('MachineName') as nvarchar(128)), '\SQLRUserGroup', CAST(serverproperty('InstanceName') as nvarchar(128)));
if SUSER_ID(@login_name) is null
begin
    set @login_name = QUOTENAME(@login_name);
    exec('create login ' + @login_name + ' from windows;');
end;

select 'Machine Learning Services has been configured for use. SQL Server service needs to be restarted!' as Message

Everything has been installed and configured with either a Windows Local Admin or SQL sysadmin login.

This:

EXEC sp_execute_external_script  @language =N'R',
                                 @script=N'OutputDataSet<-InputDataSet',
                                 @input_data_1 =N'SELECT 42'
      WITH RESULT SETS (([TheAnswer] int not null));
GO

Always ends with:

  Msg 39021, Level 16, State 1, Line 1
  Unable to launch runtime for 'R' script. Please check the configuration of the 'R' runtime.

  Msg 39019, Level 16, State 1, Line 1
  An external script error occurred:
  Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).

  Msg 11536, Level 16, State 1, Line 1
  EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

The rlauncher.config doesn't have embedded spaces in the working directory:

RHOME=C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES
MPI_HOME=C:\Program Files\Microsoft MPI
INSTANCE_NAME=MSSQLSERVER
TRACE_LEVEL=1
JOB_CLEANUP_ON_EXIT=1
USER_POOL_SIZE=0
WORKING_DIRECTORY=M:\SQLData\MSSQL13.MSSQLSERVER\MSSQL\ExtensibilityData

I've read every 'R' Install page in BOL and elsewhere I can web-search-find.

Finally, after four hours of head-bashing, I give up!

Can ANYONE, please offer assistance?


texts: join performance, index vs. index+foreignkey

texts:
I'm using an orm framework that handles data (and its integrity) on top of mysql. So far I ignored foreign keys because the orm handles that just fine.
I recently read that foreign keys can enhance join-performance so I took the time two create a second schema where fks are added.

usually a join in the app/orm looks like this:

SELECT <fields...> FROM a
INNER JOIN b
ON a.property = b.pk
INNER JOIN c
ON a.property = c.pk
INNER JOIN d
ON c.property = d.pk
WHERE <something>

Each property that references a pk in another table always comes with an index but in the new additional schema an fk is also added (which, as i understand is formost a constraint).

Now I ran tests to measure the speed between schema a and b and could find no differences whatsoever. also whenever I use EXPLAIN on a statement it shows up exactly the same in both schemata.

am i missing something or is the conclusion valid that fks do in fact not enhance  select-performance if both property and pk in the join-condition are indexed anyway?

what could even be a possible advantage of the fk? the fact that the db knows that the value must exist?

is it true that mysql will attempt to put related data close together? and a foreign key will help mysql understand what is related?


texts:
We have got the same issue in two replica sets in AWS cloud.

According to rs.status() on primary secondary jumping between SECONDARY and unhealthy states.
With period of 10-20 seconds.

In logs I see socket errors. On secondary db.currentOp() shows only heartbeat operation that have seconds_run 1-30 seconds each.

But AWS security group assigned to all instances in replica set grant access to port 27017.

I have checked from each node - it can reach other two (mongo host:27017).

Also replica set used to work just before we tried to replace secondary with new node.

We detached EIP from one secondary to new (with properly confiured new node of course).

But new node got described above issue. We have tried three new nodes (from backup, clean, new EIP) with the same result.


texts:
I've been approached with a requirement. One of our devs wants to be able to store information in the database, but once there it needs to be encrypted, so that if anyone ever gets a copy of the database or access to the raw data within it they will not be able to read it. You should only be able to read the data once you have authenticated through the app via SSO.

He seems to think there is a simple function for this, that he can call from the application and decrypt the encryption...

I'm telling him that something like TDE is going to be required here which will take a little while to set up. I have no experience of this, so it will be a learning curve....

In short, the requirement is to protect sensitive information if the database is ever hacked, stolen or mislaid.

Any ideas? Or is TDE the way to go?


texts: SELECT to only see Offsetting Transactions in table?

texts:
I have a temp table like this

I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos.
So, I only want to see

CCC 333333333  Ambition Trust  Purchase,
CCC 333333333  Ambition Trust  Sell

This is the SQL giving me the same Portcode, Cusip combo from a table.
SELECT PortfolioName, CUSIP, COUNT(*) AS CNT
INTO #tmpCusip
FROM #tmpTrades
GROUP BY PortfolioName, MCUSIP
HAVING COUNT(*) > 1

Does anyone have any ideas?


texts: SQL SERVER: Header Blocker Across Multiple Databases with wait type CXCONSUMER

texts:
We have an instance of SQL Server which has multiple databases. A process in one database seems to be blocking a process in another database. When I look in activity monitor I can see a header blocker (A one in the header blocker column). This seems to be blocking other processes in different databases. I can see their ids in the blocked by column when I select one from the drop down. Am I correct that it is cross database blocking? I didn't think this was possible. They are all running exactly the same stored procedure, but they have their own instance in each database. They are doing updates and inserts, but only within their own databases.

eg

UPDATE  SCA
SET SCA.date_last_read_only = TDR.date_seen
FROM [dbo].[SINGLE_CERT_ACC] SCA
INNER JOIN #TMP_DELTA_READONLY TDR
ON SCA.id = TDR.id
SET @RecsUpdated = @RecsUpdated + @@ROWCOUNT


texts: Does cancelling (stopping early) a CLUSTER command cause a full rollback in Postgres 9.5?

texts: Let's say I want to CLUSTER a table.  There's no way to know how long it will take so I just run it.  After a couple hours, I need the data so I cancel the CLUSTER command.  Does CLUSTERing work with temp tables/files such that if I cancel before it's finished, all of that is erased and the hours it ran are wasted or does it make changes "live" such that canceling after a few hours isn't a full waste of time?


texts: Optimizer failing to load statistic when creating plan in SQL Server 2017 "Calculator failed. Replanning."

texts:
This morning, the statistics on one of our filtered indexes updated, and when a plan that uses that statistics recompiled it stopped using that index (and started scanning the entire clustered index).

As this filtered index has included columns to cover the query, I had to wonder why.

When I tried the statement with options (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 2363), I saw that the optimizer failed to load the statistic for the filtered, including column index:

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [VendorPost1].StatusCode

Loaded histogram for column QCOL: [VendorPost1].StatusCode from stats with id 14

Calculator failed. Replanning.

We recently changed the compatibility mode in our databases to 2017 (having migrated in early Aug), and this is second time we've had an issue with stats not loading.  The first time, a stat update with full scan fixed the issue, but this seems like it should be unnecessary.

Unfortunately, we can not find anything online that describe the issue, and I can't seem to recreate with test tables.

Does anyone know how to fix or even where additional information can be found?  I'm starting to worry this is a bug we'll need to involve Microsoft in.


texts: Is it enough to have only full backup and transaction log backup only for a good backup plan?

texts:
If Full backup is scheduled for every Sunday at 12 pm and differential back up is scheduled for for every 6 hours and transaction log backup is scheduled for every 15 minutes.

My problem is what is the use of differential backup every 6 hours if we are taking a transaction log backup every 15 minutes? Is it enough to have only full backup and transaction log backup only ?

Thank you


texts: how to use trigger to make SQL Job's step success or fail

texts:
Step 1. Call a script to trigger application process. When the application process start, it will insert a record to a specific table. And once the process ends, that record will be deleted from table. The insert and delete SQL is issued from application.

Step 2. Check the running status of step 1. Since there is record when the process is running. Currently, I use two SELECT statement to check. When first SELECT statement gets the record, then the process is started. And then delay about 15 minutes, run another SELECT statement, when the record is deleted. I know the process is finished. Once process is finished, I will trigger step 3 to run another process.

Step 2's method has drawback. Because step 1's process's time will vary from time to time. So I need to adjust the delay time. I think trigger is a better way in this case. But I do not know how to link the trigger with SQL Job Step's success and fail. Would you please provide an example? Thanks.


texts: How can I disable all unique index and constraints in Oracle?

texts:
I have been trying to do OracleBulkCopy on .NET, however, Oracle throws me errors. What specifically does OracleBulkCopy do, and how can I optimize its performance? (This was the post helped me alot) However, I do not know how to disable all unique index and constraints in the entire database.

Thanks in advance.


texts: Is there a problem with port parameter on MacOS MySQL CLI?

texts:
I'm Mac user and I've recently installed MySQL CLI with Homebrew :

brew install mysql-client

Most of time I use it for classic connection on default MySQL port :

mysql -h hostname -P 3306 -uuser -p

And it works great !

Now I need to use it to connect on specific port :

mysql -h hostname -P 44000 -uuser -p

And I get the following message :

ERROR 1045 (28000): Access denied for user 'root'@'ip' (using password: NO)

I've tried with passing password in clear or using a config file but I got the same message. It seems that password isn't send when a port other than default is used.

Anyone has already encountered this problem and know how to solve it please ?


texts: Exclude procedure call from transaction rollback

texts:
How can I exclude a statement in a transaction rollback?

Begin
Insert into table a
...
...
call proc_1()
...
...
Rollback

How can I rollback all the commands except/prevent proc_1() from rolling back?


texts: Query doesn't return data that's written recently

texts:
I have two micro services where one write to DB and another read from DB.
The first micro service is written in C++ while the other is written in NodeJS.
When querying for data say for a IP the MongoDB return older data or no data at all (In case if the client is new) but If I query like say after 5 min it returns the recent data.
I don't think this has to do with concurrency at all because C++ is performant enough to write to DB faster


texts: Parent/child relationship or not if the name of the field implies the relationship?

texts:
I am working with the harmonized system and try to catalog this in a MySQL database. This works for example like this:

01  LIVE ANIMALS
0101  horses, asses, mules and hinnies, live
0102  bovine animals, live
0103  swine, live
010310  Purebred breeding animals
010391  Weighing less than 50 (11023 lb) each
010392  Weighing 50 (11023 lb) or more each
0104  sheep and goats, live
0105  chickens, ducks, geese, turkeys, and guineas, live
0106  animals, live, nesoi Nesoi - not elsewhere specified of indicated.

As you can see, there is a clear parent-child relationship. This relationship can always be traced back by the numbering that is used. From this thread I learned that I best use a text data field for the codes (01, 0101, etc). My question is: should I somewhere log the parent-child relationship, or would this not be necessary? After all, I can do a:

SELECT * FROM accounts WHERE code LIKE '0103%'

And get all the subaccounts just fine. And I feel that a parent-child table could make things more complicated. But I'd like to hear what other people feel make sense.


texts: Can I safely delete .tmp files from a FoxPro 2.5?

texts:
Can I safely delete .tmp files from a FoxPro 2.5 application?

There are currently over 71k .tmp files. They are located in the root directory and the \TEMP directory.


texts: Transaction and data consistency during a failure

texts:
When a database begins a transaction, all statements executed in that transaction are isolated and atomic (and consistent and durable).  These are pretty much the definition of a transaction.

Wikipedia states that there are some databases that insure a transaction remains isolated by locking the rows and not unlocking them until the transaction has committed.

My question is:  How can a database that solely relies on locking guarantee consistency?  If a power outage occurs mid-transaction, there may be data partially written to the row.

Even for databases like SQL Server that use a Temporary DB to perform all the transactions, what happens if a power outage occurs as the database is committing the transactions to disk?

Does the data become corrupted or does the database handle that correctly?


texts: How can I minimize the risk of accidentally modifying the wrong database?

texts:
I just learned the hard way that disconnecting from a server in the Object Explorer doesn't stop you afterwards from executing query windows that were already open on that server.

My situation is like this: I have one instance of SSMS that I use to connect to our dev/staging server and to our production server. I had to delete a bunch of data on dev so I figured I should close my connection to production, but I didn't pay attention to the query window I was using. (Luckily we had a backup of only a few hours old.)

I'm not the first person to destroy production data and I won't be the last I'm sure. So
I'm looking for checklists, best practices, etc. that help you to minimise the risk of executing queries on the wrong database. Have you had this happen to you before, and how have you adapted your workflow to try to avoid this?


texts: How to add linked server SQL Server 2008 R2

texts:
I have two SQL Server 2008 located in different cities.

I have physical access to one of them and I can exec SQL Server Management Studio there.

For second server I have following parameters:

remote domain name
remote port
login
password

With these parameters I can connect to second server with SQL Server Management Studio.

But now, I have to create cross server queries. It is possible? How? :)

Do I have to add Linked Server? How?

I see only this window, but where do I writer domain/port/login/password info?


texts: Returning 1 Max Value when Some Counts Are Equal in Access

texts:
I'm building a database that needs to return peak traffic data for Month-over-Month and Year-over-Year Comparisons in Access. We are trying to determine what our busiest Monday is, Tuesday, Wednesday, and so on in each month. The date needs to be attached to the return value. It should look like this.

Sep
Date    DOW Data
9/12    Mon 995
9/13    Tue 982
9/14    Wed 986
9/1     Thu 1004
9/2     Fri 1008
9/17    Sat 750
9/18    Sun 954

I'm trying to run a SQL query that counts all of the individual records for a day, sorts by Day of Week, finds the max, and then returns that value. We are running into two major problems though. I can get it to look like the table above without the Date attached by running this query.

SELECT DOW, MAX(Counts) AS Max
FROM
(
SELECT DOW, Start, Count(*) AS Counts
FROM Outbound
GROUP BY DOW, START
)
GROUP BY DOW;

*"DOW" is a number value signifying the day (1=Monday, etc...) and "Start"is the actual date.

It returns the below table.

DOW Max
1   995
2   982
3   986
4   1004
5   1009
6   750
7   954

This has everything I need but that date. However, the 'Max' Value is not unique, so I can't relate it back to an earlier table to get a date associated with this count, because there may be several that have that same value. I also can't use DISTINCT because the 'Start' Value is different on every record. We only need to return one date if there are several with the max value, because this is used more for MOM and YOY comparison, so inter-month comparison isn't important.

To sum it up, we can either get the right data, but without the date, or get the date, but too much data.

Any ideas?


texts:
I have a query that use left outer join.

something like that :

select issues.id, journal.notes from issues left outer join on (issues.id = journal.issue_id and journal.notes != '')

So far it works.

But in fact in the database journal there can be multiple row that reference the issues one.

So for each row of journal, it duplicates the result of issues.

What I would like to do is merge notes row for a same issue.

Example :

issues :
  id
  1
  2

journal :
  issue_id, notes
  1, note 1
  2, note 1 for issue 2
  2, note 2 for issue 2

Actually it returns this result :

1, note 1
 2, note 1 for issue 2
 2, note 2 for issue 2

And I would like it to be :

1, note 1
 2, note 1 for issue 2\nnote 2 for issue 2

I am using postgres.

How can I do this kind of merge ?

Thanks.


texts: how to convert my PostgreSQL database table into CSV?

texts:
i need to convert my PostgreSQL table into csv, how to do this?
i have visited many sites they suggest the PostgreSQL command to convert the table into CSV but i always get the error message.

the query i tried was :

COPY persons TO 'C:\tmp\persons_db.csv' DELIMITER ',' CSV HEADER;

but PostgreSQL generates the error message as:

ERROR:  could not open file "/tmp/banke.csv" for writing: No such file or directory
********** Error **********

ERROR: could not open file "/tmp/banke.csv" for writing: No such file or directory
SQL state: 58P01

how to do this?
is there any other software or application that can convert the table into csv ?
if so then please suggest.
thank you all..


texts: Mariadb replication slave can only access information_schema db

texts:
I had a master-slave set up working fine until a power outage on the slave. Now I get

error connecting to master 'repl@10.0.0.1:50000' - retry-time: 60
maximum-retries: 86400
message: Access denied for user 'repl'@'localhost' (using password: YES)

The password has not changed and I can log in to the database using
slave-host$ mysql -h10.0.0.1 -P50000 --user=repl --password='secret'

However once logged in I cannot see any databases except information_schema. Trying use <my_db_name> gives an access denied error.
On the master
master-host: mysql> show grants for repl@localhost;
GRANT REPLICATION SLAVE ON *.* TO
  'repl'@'localhost' IDENTIFIED BY PASSWORD '*xxxxx'

I've tried re-granting access, FLUSH PRIVILEGES, START/STOP SLAVE; in various combinations but I'm not getting anywhere.
Nb. the host at 10.0.0.1 has an SSH tunnel setup to the datbase host, so grants for repl@localhost are appropriate because the master sees it as local traffic.
Master and slave both on:  10.1.26-MariaDB-0+deb9u1


texts: How to use SELECT statement to get data from SQlite table using with dictionary values?

texts:
I have below dictionary values.
Example:
dic = {'ID1': ['Name1', 'Name2'],
       'ID2': ['Name1', 'Name3'],
       'ID3': ['Name2']}

I want to print address of every matched names from above dict values into below format.
Example Output:
['ID1', 'adress1' ,'adress2'],
['ID2', 'adress1' ,'adress3'],
['ID3', 'adress2']

Below is my example code of database table that has "Emp_ID", "Emp_Name", "Emp_Address". The dict values are "Emp_Names", that should be match emp_Names and get the Adresses in to the list as Example out.
import sqlite3

conn=sqlite3.connect('emp.db')
conn.execute("""CREATE TABLE IF NOT EXISTS branch
        (Emp_ID TEXT UNIQUE,
    Emp_Name    TEXT UNIQUE,
    Emp_Address TEXT
    )""")

conn.execute("""INSERT OR IGNORE INTO branch(Emp_ID, Emp_Name, Emp_Address) \
    VALUES('Emp1', 'Name1', 'adress1')""")

conn.execute("""INSERT OR IGNORE INTO branch(Emp_ID, Emp_Name, Emp_Address) \
    VALUES('Emp2', 'Name2', 'adress2')""")

conn.execute("""INSERT OR IGNORE INTO branch(Emp_ID, Emp_Name, Emp_Address) \
    VALUES('Emp3', 'Name3', 'adress3')""")

conn.execute("""INSERT OR IGNORE INTO branch(Emp_ID, Emp_Name, Emp_Address) \
    VALUES('Emp4', 'Name4', 'adress4')""")

conn.execute("""INSERT OR IGNORE INTO branch(Emp_ID, Emp_Name, Emp_Address) \
    VALUES('Emp5', 'Name5', 'adress5')""")

conn.commit()
conn.close()

For example 'ID1' (column) should not to be changed.
And 'name1' should take its address and 'name2'should take its address and all matched names should take their addresses. Which statements are useful for my scenario. Is it possible? If possible how to to?


texts: creating a historical database, need help making a decision

texts:
I'm working with 5 processes that take about 8 hours to run, during each 8 hour process, it changes its location at random times but in a specific order. There could also be all 5 processes running at the same time.

I'm trying to track when the process starts, when it changes locations and when it stops, easy right.

I have gone back and forth trying to decide on a solid scalable design, i came up with the two listed below, I wanted to get everyones thoughts on why or why not either should be used. Any tips or suggestions are greatly welcomed

** The table consists of five columns: id, location, operation, started, finished.

when the process starts, id, location, operation and started are inserted into DB, finished is left NULL. When the process goes to the next location, the table is queried for the last operation at the location with a NULL finished time. Then the finished time is updated and then the next location is inserted into the database.

** I don't like this design but its quick and easy, the only problem i see is if somehow a row does not get closed or finished and there are two operations with the same location with Null finished times.

Same as number one except for when a row is inserted, i get the id of the last inserted row and save it in a variable, then once the process has moved locations i can simply update the row per its id.

** I like this design but since the process can run for 8+ hours, if the program crashes or if there is a pc issue i could lose the id and the script will fail.

i thought about breaking out the start and finished into their own separate tables but i am not in favor of this design unless someone can convince me otherwise.

Thanks for the help and suggestions!


texts: How to move the ownership/copy a table from one user to another?

texts:
I've been tasked with doing some tidying of our database. I'm not a DBA expert so here I am. (We don't have a DBA expert either).

We have a user Old_User and another user New_User.

Old_User owns a set of tables, Table_A and Table_B.
New_User has synonyms t_a and t_b respectively.

Can anyone give me some advice on how I should go about moving the ownership of Table_A and Table_B to New_User.

There are tables owned by New_User which have foreign keys to the tables owned by Old_User. I'm not sure if that complicates anything? Ideally, all referential integrity and constraints should be the same.

The goal will be to delete the Old_User eventually.


texts: Oracle 19c express manager not loading, listener will not register https port after exec dbms_xdb_config.gethttpsport()

texts:
We just set up a new oracle 19c install on a VM Windows Server 2020. I have been struggling trying to get the Express Manager working. At the end of the installation Oracle notified me that I can access the Express Manager at https://localhost:5500/em
But upon visiting the website in chrome the connection is refused. I disabled the firewall and receive the same message. I went through the oracle documentation and ensured that
dbms_xdb_config.gethttpsport() outputs 5500.
After running lsnrctl status I noticed that under listeners I am missing port 5500. I searched other users with the same issue they all have an entry for port 5500 like the following.
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=HOSTNAME.domain)(PORT=5500))(Security=(my_wallet_directory=C:\ORACLE\admin\ecoomdb\xdb_wallet))(Presentation=HTTP)(Session=RAW))
    Services Summary...

According to Oracle documentation when I run the command dbms_xdb_config.gethttpsport(5500) the listener should register the port. But after running the command muliple times this is not happening.


texts: Job management: How to get the next running date by querying MSDB

texts:
Table sysjobschedules contains 2 columns next_run_date and next_run_time. With the help of sysschedules and column enable it should be easy to determine the next running date/time of a specific job.
However, when you change a schedule, sysjobschedules is only refreshed 20 minutes after alteration by the Sql Server Agent.
Table sysjobactivity contains a datetime next_schedule_run_date which is updated in real time.
But I've failed to see how to link sysjobactivity to sysschedules since it doesn't contain a column to identify the id of the schedule.
Can someone help?


texts: Query missing one date; only happened today

texts:
This should be pretty reproducible I hope.

I'm on Oracle 11g. This query, which I use as part of another query, is acting strangely:

SELECT TRUNC(SYSDATE - ROWNUM) DATEITEM
                         FROM   DUAL
                         CONNECT BY ROWNUM <= 32
                         UNION
                         SELECT TRUNC(SYSDATE + ROWNUM) DATEITEM
                         FROM   DUAL
                         CONNECT BY ROWNUM <= 32
order by dateitem desc;

This should return 32 days before and after today's date, in descending order.

It has always worked, and always had, until today.

For some reason, this does not return September 26th. It skips right from 25 to 27th.

I cannot figure a rhyme or reason for this. This query did return the 25th on the 25th for example (and so I have no evidence that it leaves out only sysdate, as it worked yesterday).

What the heck have I done wrong here?


texts: Database index on FK with only NULL values

texts: We have in our database an index with table cardinality over 10.000 but full key cardinality only 1. The reason for this that all the values for the column are NULL, hence cardinality 1. Does this index still perform badly in practice? Or is it omitted because the values are all NULL? Our database is IBM DB2 9.7.


texts: Lock Escalation and Count Discrepancy in lock_acquired Extended Event

texts:
I'm trying to understand why there is a discrepancy in lock count in sys.dm_tran_locks and sqlserver.lock_acquired extended event in certain cases. Here is my repro script, I'm using the StackOverflow2013 database on SQL Server 2019 RTM, compat level 150.
/* Initial Setup */
IF OBJECT_ID('dbo.HighQuestionScores', 'U') IS NOT NULL
DROP TABLE dbo.HighQuestionScores;

CREATE TABLE dbo.HighQuestionScores
(
    Id INT PRIMARY KEY CLUSTERED,
    DisplayName NVARCHAR(40) NOT NULL,
    Reputation BIGINT NOT NULL,
    Score BIGINT
)

INSERT dbo.HighQuestionScores
        (Id, DisplayName, Reputation, Score)
SELECT u.Id,
       u.DisplayName,
       u.Reputation,
       NULL
FROM dbo.Users AS u;

CREATE INDEX ix_HighQuestionScores_Reputation ON dbo.HighQuestionScores  (Reputation);

Next I update the table statistics with a large fake row count
/* Chaotic Evil. */
UPDATE STATISTICS dbo.HighQuestionScores WITH ROWCOUNT = 99999999999999;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;

Then I open a transaction and update Score for Reputation, say 56
BEGIN TRAN;
UPDATE dbo.HighQuestionScores
SET Score = 1
WHERE Reputation = 56 /* 8066 records */
AND 1 = (SELECT 1);
/* Source: https://www.erikdarlingdata.com/sql-server/helpers-views-and-functions-i-use-in-presentations/ Thanks, Erik */
SELECT *
FROM dbo.WhatsUpLocks(@@SPID) AS wul
WHERE wul.locked_object = N'HighQuestionScores'
ROLLBACK;

I get a bunch of page locks (despite having an index on Reputation). I'm guessing the bad estimates really did a number on the optimizer there.

I also double checked using sp_whoisactive and it too returns the same information.
<Object name="HighQuestionScores" schema_name="dbo">
  <Locks>
    <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
    <Lock resource_type="PAGE" page_type="*" index_name="PK__HighQues__3214EC072EE1ADBA" request_mode="X" request_status="GRANT" request_count="6159" />
  </Locks>
</Object>

Meanwhile I also have an extended event running on sqlserver.lock_acquired separately. When I look at the grouped data I see 8066 page locks instead of initial 6159

I definitely do not a see a lock escalation (verified using sqlserver.lock_escalation event), so I guess my question is why is the extended event showing a discrepancy with a higher number of lock count?


texts: Does MariaDB require aliases for subquery table locks?

texts:
MySQL suffers from a bug (which they claim is not a bug) that prevents a table lock from working on a sub-query unless you create a table alias for each mention of the table name.

See: https://bugs.mysql.com/bug.php?id=31080

They say:

  "You cannot use a locked table multiple times in a single query. Use
  aliases instead, in which case you must obtain a lock for each alias
  separately"

I looked at the MariaDB lock tables manual page on their site, but it is unclear if this limitation has been fixed in Maria, or is still present.

Since my application builds queries dynamically, trying to work around this MySQL bug would create very messy code and a lot of unnecessary work to create pointless table aliases. If I can just swap the whole MySQL DB for Maria without compatibility issues, that would save a ton of time.


texts:
I backed up a database called app_data, on a 9.3 server, with this basic command:

pg_dump -n public -F custom app_data > app_data.pg.dump

Then I try to restore it on another server (running 9.4) like this:

pg_restore -C -d postgres app_data.pg.dump

But it puts all the tables in the postgres database. The man page says it will create and use a new database, app_data.

  -C --create

  Create the database before restoring into it. [...]

  When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is
             restored into the database name that appears in the archive.

That's not what it's doing. The name in the archive is app_data:

bash-4.2$ pg_restore -l app_data.pg.dump
;
; Archive created at Tue Dec 15 04:16:52 2015
;     dbname: app_data
...

Am I doing something wrong?


texts: In MySQL, how to execute two different SELECT statements depending on a condition?

texts:
SET @input1=123;
SET @input2='foobar';

IF @inout1>0 THEN

SELECT
    @result := col12
FROM
    table1
WHERE
   col11=@input1;

ELSE IF @input2 != ''

SELECT
    @result := col21
FROM
    table2
WHERE
    col22=@input2;

END

Basically depending on if @input1 or @input2 is set and if the set value satisfy certain condition, one of the two SELECT statements should be run. But I just couldn't get it to work with IF THEN ELSE statement in MySQL. What should be the right syntax?


texts:
Log shipping fails with the below error. Sql agent on secondary server has access to the folder and files in security. It is not a firewall issue. I created the jobs using the LS scripts as it fails through GUI.

I have done it before on a different server where there were several LS databases. This is a new primary and secondary server and not sure what I am missing. Thanks for the help

***** Error: Access to the path '\\sqlp\R$\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Database' is denied.(mscorlib) ***
----- END OF TRANSACTION LOG COPY     -----**


texts: Why would one need multiple instances of same Oracle database?

texts:
I came across following statement while reading this short article -

  An instance is the software (and memory) that Oracle uses to
  manipulate the data in the database. In order for the instance to be
  able to manipulate that data, the instance must open the database. A
  database can be opened (or mounted) by more than one instance,
  however, an instance can open at most one database.

I am wondering, why would one create multiple instances of same database?


texts: In Postgres 9.6, why is the GIN index not used for a JSONB column with text/int array?

texts:
In Postgres 9.6, why is the GIN index not used on my SELECT query below for a JSONB column with text/int array? How do I force Postgres to use the GIN index?

SET enable_seqscan to FALSE;
EXPLAIN ANALYZE SELECT * FROM items WHERE int_array_cast(metadata->>'types') @> '{52, 53}'

Output of EXPLAIN

Seq Scan on items  (cost=10000000000.00..10000000016.07 rows=1 width=2391) (actual time=0.073..0.117 rows=1 loops=1)
  Filter: (int_array_cast((metadata ->> 'types'::text)) @> '{10,14}'::integer[])
  Rows Removed by Filter: 37
Planning Time: 0.201 ms
Execution Time: 0.197 ms

Table Structure

CREATE TABLE "items" (
    "item_uuid" UUid NOT NULL,
    "metadata" JSONB,
    PRIMARY KEY ("item_uuid")
);

int_array_cast function defintion

CREATE OR REPLACE FUNCTION int_array_cast(TEXT) RETURNS INT[]
AS
$$
  SELECT CAST($1 AS INT[])
$$
IMMUTABLE
LANGUAGE SQL
RETURNS NULL ON NULL INPUT

Index created using GIN

CREATE INDEX IF NOT EXISTS items_metadata_dok_index ON items USING GIN(int_array_cast(metadata->>'types'))

Sample items in the table

item_uuid | metadata
--------------------------------------------------
        1 | {"types":"{1,2}", "name": "item_name1"}
        2 | {"types":"{10,11}", "name": "item_name2"}
        3 | {"types":"12", "name": "item_name3"}
        3 | {"name": "item_name4"}


texts: How to model very flexible referencing relationships between many tables?

texts:
I have various different entity types in my database. For purposes of example, consider an IMDB database:

Movie
Person
Actor
Director
Translation
and 20 more tables

My application enables free-form many-many relationships between any of these entities.

What is a sane way to model this in a maintainable way?

Currently, my app uses a table with "a_id", "a_type", "b_id", "b_type". With the type identifying the table (e.g. "movie"). This means there is no referential integrity checking, which makes me uneasy.

What are the alternatives? The only one that comes to mind is to expand the linking table with a column for every other table. So I have a_movie_id, a_actor_id, a_director_id and b_movie_id, b_actor_id, b_director_id. However, while this preserves referential integrity, it feels strange (I have not seen such tables before) and I suspect it would be difficult for application code to process it (it would have to have a big if-statement to check what is actually referenced).

Thus I ask for your advice - is there a convenient industry standard way to model such flexible relationships?

My database engine is is SQL Server.


texts: Queries and updates extremely slow after IndexOptimize

texts:
Database SQL Server 2017 Enterprise CU16 14.0.3076.1

We recently tried switching from the default Index Rebuild maintenance jobs to the Ola Hallengren IndexOptimize. The default Index Rebuild jobs had been running for a couple of months without any issues, and the queries and updates were working with acceptable execution times.
After running IndexOptimize on the database:

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

performance was extremely degraded. An update statement that took 100ms before IndexOptimize took 78.000ms afterwards (using an identical plan), and queries were also performing several orders of magnitude worse.

Since this still is a test database (we're migrating a production system from Oracle) we reverted to a backup and disabled IndexOptimize and everything returned to normal.

However, we would like to understand what IndexOptimize does differently from the "normal" Index Rebuild that could have caused this extreme performance degradation in order to make sure we avoid it once we go to production. Any suggestions on what to look for would be greatly appreciated.

Execution plan for the update statement when it is slow. i.e.
After IndexOptimize
Actual execution plan (coming asap)

I haven't been able to spot a difference.
Plan for the same query when it is fast
Actual execution plan


texts: Nested SQL Query (Query a subset of data)

texts:
Assume I have the following structure...

Structures
- ID
- Name

Fields
- ID
- StructureID
- Key

These table hold simplistic 'structures' (Think very simplistic tables).

There is then an additional table

DataValues
- ID
- FieldID
- Value
- RowID

Example data inside of the table...

ID | FieldID | Value             | RowID
1  | 5       | Blog Post Title   | 1
2  | 6       | Blog Post Body    | 1
3  | 5       | Another Post H1   | 2
4  | 6       | Another Body      | 2

Now I want the ability to do dynamic queries to each data set, so grab all the RowID's that follow a certain pattern.

SELECT DV.RowID FROM DataValues as DV
    INNER JOIN Fields as F ON DV.FieldID = F.ID
    INNER JOIN DataRows as DR ON DV.RowID = DR.ID
WHERE DR.StructureID = 2
AND (F.[Key] = 'Title' AND DV.[Value] = 'Blog Post Title')

The issue is then when we want to do multiple conditions

SELECT DV.RowID FROM DataValues as DV
    INNER JOIN Fields as F ON DV.FieldID = F.ID
    INNER JOIN DataRows as DR ON DV.RowID = DR.ID
WHERE DR.StructureID = 2
AND (F.[Key] = 'Title' AND DV.[Value] = 'Blog Post Title')
AND (F.[Key] = 'Body' AND DV.[Value] <> 'A Body We Dont Want')

This obviously wont work, is there a way to query a subset of the data?


texts: Its necessary to relate the ComplaintMessages entity with the User and Admin entities or is enough to relate with the Complaint entity? (ER Model)

texts:
I'm in doubt in the ER model of a scenario where a user can make complaints.

The user enters the title and the description of the complaint and submits the info. A site admin can see all user complaints and answer.

Also, the user then has access to the Admin answer and the user should be able to answer to that Admin answer similar to a chat scenario where then the admin will also have access to the new complaint message of the user, so there can be several messages.

I'm in doubt in which ER model is correct for this scenario if the 1 or 2, do you know if it is also necessary to relate the ComplaintMessages entity with the User and Admin entities?

1) It's not necessary to relate the ComplaintMessages entity with the User and Admin entities?

2) Or it is also necessary to relate the ComplaintMessages entity with the User and Admin entities?


texts: Delete SQL Server 2008 R2 server logins mapped to offline databases

texts:
I have circumstances that a server can host many databases and a lot of them get offline and transfered elsewhere from time to time.
This causes lots of server logins to be created and stay there after the transfer.
I need a script to find all the server logins that are mapped to offline or non existing databases so I can clean them.

Thank you!


texts: Remove both Special Characters and String Characters from output SQL Server

texts:
I am looking to remove some string characters in an output that I have so that it only shows numbers rather then letters and numbers. My Query is:

Select DISTINCT t.processed 'Title',
(Select max(reconst) from isbn where bib# = t.bib#) 'ISBN',
t.bib# 'Bib#',
(select max(collection) from item where bib#=t.bib#) 'Collection',
convert ( varchar(3), (Select count (*) from item i where i.bib# = t.bib#)) 'Total Items',
(Select count (*) from item i where i.bib# = t.bib# and item_status not in ('o','l','cr','m')) 'Eligible Items',
(Select count(*) from request r where r.bib# = t.bib#) 'Requests'
from title t
inner join isbn
on t.bib#=isbn.bib#
inner join item
on t.bib#=item.bib#
where t.bib# in (Select bib# from request)
and convert( int, (Select count(*) from item i where i.bib# = t.bib#)) <
convert( int, (Select count(*) from request r where r.bib# = t.bib#))
order by Requests desc

The output from the Query is:

The desired output is in the ISBN column which would would look like 9781501132957, which would be removing the letters C and also the words such as hardcover, HRD1, acid-free paper, softcover,PAP, DVD, CD, pbk and special characters such as (), :, ., -

Thank you in advance


texts:
Here is the query for the dataset:

SELECT DISTINCT CompanyId, Company
FROM            User_Hierarchy_Mapping
WHERE           UserId = @FieldManager

FieldManager is the parameter name. It's not taking the value of the parameter.

Please help me with the syntax to pass a parameter to a MySQL query.


texts: SSMS 2016 Job Schedule Wizard Starting At entry field

texts:
I am using SSMS 2016. In the wizard for job schedules the field for Starting/Ending At shows only one character, and is unusable. Here is the screen shot.

SSMS version should be the latest at 13.0.16000.28.

Anyone else see or experience this?


texts: In a SQL Server AlwaysOn AG synchronous mode scenario, if the secondary node goes down, is the primary down as well (since it cannot write to and commit to the secondary)?


texts: Three table query to find the most recent item based on a distinct value

texts:
I am attempting (without success thus far) to write a query that will join 3 tables and return the most recent record based on a distinct value.

Table A

itemnum  | itemname
-------- | --------
123      | document #1
456      | document #2

Table B

itemnum  | keyvaluedate
-------  | -------------
123      | 2015-09-05 00:00:00.000
456      | 2011-06-04 00:00:00.000
789      | 2011-06-04 00:00:00.000
546      | 2011-06-04 00:00:00.000
378      | 2011-06-04 00:00:00.000

Table C

itemnum  | keyvaluebig
-------- | -----------
123      | 123456
456      | 123456
545      | 987654
387      | 987654

Table A holds all of the records. What I need to find is the most recent record (keyvaluedate from table b) for each distinct project (keyvaluebig from table c).

The itemnum data is always unique. The keyvaluedate from Table B and the keyvaluebig from Table C both non-distinct values present.

What is the best way to go about this?


texts: Alternative to "FOR JSON" in SQL Server 2012?

texts:
In SQL Server 2016 I can output query results as JSON as described in the documentation Format Query Results as JSON with FOR JSON (SQL Server)

SELECT *
FROM x
FOR JSON PATH

But I'm finding that this is not supported in SQL Server 2012.

Is there a built-in way to convert tabular data to JSON data in SQL Server 2012?

To make this cross compatible I would ideally like to be able to use for json path in SQL Server 2012.


texts: Materialized View does not import properly when importing a db for a second time in another schema

texts:
When I import a database with materialized view mv_mt in just one schema I get no errors.

create materialized view mv_mt
refresh complete
next trunc( sysdate ) + 1
as
SELECT sysdate, media_type.*
from media_type;

But when I try to import the same database to a copy in another schema in the same tablespace I get the following errors:

IMP-00017: following statement failed with ORACLE error 1:
 "BEGIN   DBMS_JOB.ISUBMIT(JOB=>438,WHAT=>'dbms_refresh.refresh(''"ALEXANDRA""
 "."MV_MT"'');',NEXT_DATE=>TO_DATE('2012-07-02:14:22:36','YYYY-MM-DD:HH24:MI:"
 "SS'),INTERVAL=>'sysdate + 1 / 24 / 60 / 6  ',NO_PARSE=>TRUE); END;"
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
ORA-06512: at "SYS.DBMS_JOB", line 100
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 23421:
 "BEGIN   dbms_refresh.make('"ALEXANDRA"."MV_MT"',list=>null,next_date=>null,"
 "interval=>null,implicit_destroy=>TRUE,lax=>FALSE,job=>438,rollback_seg=>NUL"
 "L,push_deferred_rpc=>TRUE,refresh_after_errors=>FALSE,purge_option => 1,par"
 "allelism => 0,heap_size => 0); END;"
IMP-00003: ORACLE error 23421 encountered
ORA-23421: job number 438 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 793
ORA-06512: at "SYS.DBMS_REFRESH", line 86
ORA-06512: at "SYS.DBMS_REFRESH", line 62
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 23410:
 "BEGIN   dbms_refresh.add(name=>'"ALEXANDRA"."MV_MT"',list=>'"ALEXANDRA"."MV"
 "_MT"',siteid=>0,export_db=>'ORCL01'); END;"
IMP-00003: ORACLE error 23410 encountered
ORA-23410: materialized view "ALEXANDRA"."MV_MT" is already in a refresh group
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_IREFRESH", line 484
ORA-06512: at "SYS.DBMS_REFRESH", line 140
ORA-06512: at "SYS.DBMS_REFRESH", line 125
ORA-06512: at line 1

Anyone any ideas?


texts:
At the moment I have a view created, see it here in the answer. How can I create a function based on that crosstab() query , so I can pass a date, and get data for the specific date?

Also is it good practice to call that function multiple times and pass different dates to fill a chart (for example)?


texts: Database Schema for a User and his variable number of items

texts:
I planning to create tables which hold data related to my users. These tables contain the items that each user owns, and there is no exhaustive list of all the item. The number of items owned by a user might vary from 0 to 100. So the number of items owned by a user is not fixed. For example, A user "Sam" might have "iPod, MacBook, Car, House", while some other user "Dave" might have "Bike, Plane, iPhone, TV ...." etc. So what is the best schema for the tables? I came up with the following idea. Please suggest improvements if any:

I will have a table which holds data as follows:

User           Item
=========      =========
Sam            iPhone
Sam            MacBook
Sam            Car
Sam            House
Dave           Bike
Dave           Plane
Dave           iPhone
Dave           TV
.
.


texts: What's the (Big O) computational complexity of a PostgreSQL recursive common table expression?

texts:
For example, taking this StackOverflow #44620695 question, recursive path aggregation and CTE query for top-down tree postgres as an example, which uses a recursive CTE to traverse a tree structure to determine the paths from a starting node.

The screenshot above shows the the input data, the recursive CTE result, and a visualization of the source data.

Recursive CTE are iterative over the preceding result -- right? (as suggested in the accepted answer here) -- so would the time complexity be something like O(log n)?


texts: Is there a way to replicate certain part of collection in Mongodb

texts:
Is there away to stream certain part of collection in MongoDB or TokuMX from master server to another server? I need to duplicate only certain collection

Or if it isn't possible, are there any other alternative for database that doesn't enforce schema?


texts: how to upgrade and migrate postgres dbs to new server machine?

texts:
my production databases run on a centos 5.5 virtual machine. This server has 2 postgres 8.4 clusters installed on 2 different partitions; now my idea is to upgrade both db version and server os (centos 6) in order to set up a newer machine with also much more disk space. In this way i will be covered for many years.
I read postgres documentation and pg_upgrade seems to be a very useful tool but,if i understood well, it can be used only on same machine and on same mounting point. So how can i get my goal? pgdumpall command? note that clusters' sizes are (at the moment) 10gb and 1gb ,but cluster 1 grows up quite fast.

thanks


texts:
I followed the steps below.

I installed PostgreSQL.
I logged in as postgres via (sudo -i -u postgres)
I created new user new_user via shell script createuser --interactive
I granted that user permission to create databases.
I created database: createdb new_db.

Now I want to give new_user privileges be able to perform all default DML operations on tables created by himself (command bellow taken from this question), so I typed:

postgres=# ALTER DEFAULT PRIVILEGES FOR new_user IN SCHEMA
    public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO new_user;

But got an error:

ERROR:  syntax error at or near "new_user"
LINE 1: ALTER DEFAULT PRIVILEGES FOR new_user IN SCHEMA public GRANT...

I have no idea why this command is not working.


texts: Postgres: CREATE INDEX, but very slowly, to reduce performance hit on rest of database?

texts:
I have a large production PostgreSQL (9.1) database running on Ubuntu Linux. It is currently being queried and used. However there is another use I need later, and I will need to create a large (hundreds of gigabyte) index. I have run the CREATE INDEX ... in a screen session and that's running (for several days now), however this is affecting the performance of the rest of the database.

Is there anyway to create an index in postgres, but at a very low priority? So that it'll take longer to create the index, but the performance hit will be less.

Can I use renice the CREATE INDEX process? Will that work?
What about pausing the process for 1 out of every N (e.g. 2 or 3) minutes? Will that break things?
Can I ionice the process? Will that work?


texts: Order by 1 shortens the result set to one row

texts:
I'm running the following query:

DECLARE @sql NVARCHAR(max) = N''
SELECT @sql += N' EXEC dbo.DoSomething @Tablename = ''' + SCHEMA_NAME(T.schema_id) + N'.' + T.name + N''''
--SELECT *
FROM sys.tables T
INNER JOIN sys.indexes I ON I.object_id = T.object_id
ORDER by 1
PRINT  @sql

But I'm only getting one line printed.
If I take out the ORDER by 1 line, or replace it with
 ORDER BY t.name then I get the expected result.

Is this a bug in SQL Server?
I've tried it on SQL SErver 2016 SP1 and 2014 Sp2-Cu1.


texts:
I have captured an actual query plan for a specific query.

After this I've changed a few things around (including updating the statistics) and re-ran that specific query. Now the actual query plan is different (which makes sense).

The query now runs a lot faster. I'm curious if the new execution plan has anything to do with this, because other changes (changes to the IO setup, VM settings, sql instance restart, etc) may also be causing the improvement in performance. To test this I'd like to run the query once more, and try to force SQL Server to use the old execution plan.

Question: is there a way to re-run a query with a user-provided execution plan, or even run a query directly from such a plan?

Here's what I've tried to figure this one out:

I've searched in the books we have available at the office (Professional SQL Server 2012 Internals and Troubleshooting, Querying Microsoft SQL Server 2012);
Google searches, for example "run query based on specific query plan"
DBA.SE searches, for example "execute query plan" and "re run execution plan"
And finally, one that has answered my questions many time before: carefully check "Questions that may already have your answer" before hitting "Post your question" :-)

So bottom line: is this possible? If so: how?


texts:
I have a table that has columns like following. Batch number is a database design thing like row number

demand_id        batch_number    debit status     customer
     34               1             yes
     34               2                             jack
     35               1             no
     35               2                             kate

OUTPUT:

demand_id        batch_number    debit status     customer
     34               1             yes
                      2                             jack
     35               1             no
                      2                             kate


texts: Percona Server - binlog not automatically purge with mysqladmin flush-logs

texts:
I have a little problem and I need your help please. Here is the story : I have Percona Server in version 8.0.13 with one slave and the old binlogs on master are not deleted automatically when flush-logs appending.
Variables are set as :

# mysql -e"select @@expire_logs_days, @@binlog_expire_logs_seconds"
+--------------------+------------------------------+
| @@expire_logs_days | @@binlog_expire_logs_seconds |
+--------------------+------------------------------+
|                  0 |                        86400 |
+--------------------+------------------------------+

I have logrotate which execute mysqladmin flush-logs every night and binlogs are correctly rotated but old ones are not removed. When I execute this command manually the behaviour is the same.

In MySQL CLI the command PURGE BINARY LOGS BEFORE datetime works fine.

The slave replication is up to date or a little bit late but not more than a couple hour. So it can't be explain why old binlogs are still present on disks (some are 7 days old by example)

I also execute lsof to check if MySQL retain descriptor on old binlogs but no. I have no more idea to explain the problem.

Do you have any ideas to explain and fix this please ?

Thanks,


texts:
When I try to start

sudo service mysql start
start: Job failed to start

Then I look the /var/log/mysql/error.log

160122  8:43:42 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160122  8:43:42 [Note] Plugin 'FEDERATED' is disabled.
160122  8:43:42 InnoDB: The InnoDB memory heap is disabled
160122  8:43:42 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160122  8:43:42 InnoDB: Compressed tables use zlib 1.2.8
160122  8:43:42 InnoDB: Using Linux native AIO
^G/usr/sbin/mysqld: Can't create/write to file '/tmp/ibv0Dwa6' (Errcode: 13)
160122  8:43:42  InnoDB: Error: unable to create temporary file; errno: 13
160122  8:43:42 [ERROR] Plugin 'InnoDB' init function returned error.
160122  8:43:42 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160122  8:43:42 [ERROR] Unknown/unsupported storage engine: InnoDB
160122  8:43:42 [ERROR] Aborting

160122  8:43:42 [Note] /usr/sbin/mysqld: Shutdown complete

The permissions for /tmp

drwxr-xr-x   4 root root  4096 Jan 22 08:39 tmp

I also tried

sudo apt-get purge mysql-server-core-5.5 mysql-common

and to reinstall everything new.. But it didn't solve the problem.
The socket /etc/mysql/my.cnf exists:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address            = 127.0.0.1
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size         = 100M
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M
[isamchk]
key_buffer              = 16M
!includedir /etc/mysql/conf.d/

EDIT:

/var/run/mysqld I just saw this doesn't exist. How can I create this?

EDIT2:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

When I try to connect.


texts: Why additional plans are created under simple parameterization?

texts:
I have a table which has 2163 rows and clustered index on id column.

I run the following queries:

select id
from ticket_attach
where id = 1

select id
from ticket_attach
where id = 2

select id
from ticket_attach
where id = 3

Here is my plan cache:

Well, when I execute 1st query, I result in creating both adhoc plan and prepared plan.

I thought that under simple parameterization, the next two queries where id=2 and id=3 will use only prepared plan. Yes, those queries really used prepared plan, BUT in addition to that, they created adhoc plans. I wonder why SQL Server created those plans if it used already prepared plan. What it the purpose of creating them and taking place of plan cache?


texts: How can I call a remote User Defined Function with Parameters?

texts:
We have one SQL server which contains a link to another server that is on a different box. I would like to create a user-defined-function on ServerA which returns the value of the udf on ServerB, however SQL is telling me that Remote function calls are not allowed within a function.

Is there a way around this?

The udf does have a parameter, so I cannot use OPENQUERY. I thought I had it working by using sp_executesql, however although this works in a blank query window, it doesn't work from a user-defined function.

declare @someValue bit

exec ServerB.MyDatabase.dbo.sp_executesql
    N'SELECT @someValue = dbo.[SomeUDF](@id)',
    N'@id int, @someValue bit OUTPUT',
    @id=@id, @someValue=@someValue OUTPUT

select @someValue

The entire function gets called as part of a query, so I don't think I can use a stored procedure

SELECT *
FROM SomeTable
WHERE dbo.SomeUDF(Id) = 1


texts: How does a database conciliate data to files on distributed filesystems?

texts:
The question title might be a little misleading, but I was wondering what happens internally in a database implementation when the file used for storage are placed on a distributed filesystem (like Gluster or Ceph).

I would imagine that the database implementation keeps some data in memory and will conciliate to disk periodically. Does the database instance assume that it is the only one writing to the storage file and use this for optimizations somehow? Can someone elaborate and suggest some good materials on this topic?

My previous understanding was that this wouldn't work, but after a quick search on Google it seems that not only it works but it is fairly common (I'm not talking about explicit replication features).


texts: Missing DatabaseMail.exe.config after applying SQL Server 2016 SP2 CU1

texts:
After applying SQL Server 2016 SP2 CU1 (SP2 was already applied) to SQL Server 2016 on several different Windows Server 2016 installations, we noticed that Database Mail stopped working. Mails were being queued but not sent and we could find no error messages on the servers, neither in the SQL Server logs nor in the Windows Server event logs.

After verifying that none of the settings for Database Mail had changed and that everything was as it should be with regards to mail relaying server and other external factors, we could see that an old problem seemed to have resurfaced:

The DatabaseMail.exe.config configuration file for DatabaseMail.exe, allowing it to work without having .NET Framework 3.5 installed, had been removed by the SQL Server 2016 SP2 CU1 update.

On all instances where the patch had been applied, the config file above was missing from the binaries directory for the SQL Server installation. It was present before the patch was applied.

Example configuration file path:
C:\Program Files\Microsoft SQL Server\MSSQL13.instance_name\MSSQL\Binn\DatabaseMail.exe.config


texts: Add multiple rows in a single row with different columns

texts:
I want to join two tables (they are called Registers and Reads). As a result I would like to obtain 4 columns, corresponding to Registers ID.

According to the next example, by joining the tables, I obtain 2 rows. There could be some cases that I may find/obtain 4 rows because they exist in Reads table.

Table: Registers

    ID      Number    Prod_ID
    331       01       112233
    332       02       112233
    333       03       112233
    334       04       112233

Table: Reads

    Read_Id    Register_Id
      011          331
      012          332

I use this query to link join both tables:

    SELECT rg.ID
    FROM Reads rd LEFT JOIN Registers rg on rd.Register_ID = rg.ID
    WHERE rg.Prod_ID = 112233;

My result is next one:

    ID
    331
    332

What I really want is to obtain just one row, assigning the first result to the first column, second one to the second and so on. In addition, I would like to add an extra column that shows how many columns contain information.

Expected result:

    RegisterID1    RegisterID2    RegisterID3    RegisterID4    Count
        331            332                                        2

Is there any easy way to do this? Thank you very much!


texts: how to duplicate each row of a mysql table?

texts:
I've got a table of Product records from an old db that I must change to import on a new php system. They have the form:

name, property1_IT, property1_EN, property1_FR

I need to transform them by duplicating each row and change it like:

name, language, property1

where property1 will have the related translated value.

In other words, I'll need one row for each product translation, thus adding a language column and deleting the property translation fields.

Is it possible with sql or should I just use php to cicle through the records and doing the duplication tasks?


texts:
How can I change the storage engine of MongoDB permanently so that I don't have to run mongod without having to specify the storage engine every time? I am using shell version 3.2.7 on Windows 7 32-bit. I got an error that said:

  28663 Cannot start server. The default storage engine 'wiredTiger' is
  not available with this build of mongod. Please specify a different
  storage engine explicitly, e.g. --storageEngine=mmapv1., terminating.

Also does anyone know how to cleanly shutdown the server when I am done working? I use Ctrl+C but it causes an error, then I have to empty the data folder to get the process to start again.


texts: wal_level set to replica at database level but i dont see that in configuration file

texts:
wal_level is commented in postgresql.conf and i dont see any entry in auto.conf either. but at the databsae level i see its set to REPLICA. Is there any other place whee it could have been set?
postgres@postgresqlmaster:/etc/postgresql/12/data$ psql -p 5433
psql (12.3 (Ubuntu 12.3-1.pgdg18.04+1))
Type "help" for help.

postgres=# select name, setting, sourcefile, sourceline from pg_settings where name = 'wal_level';
   name    | setting | sourcefile | sourceline
-----------+---------+------------+------------
 wal_level | replica |            |
(1 row)

postgres@postgresqlmaster:/etc/postgresql/12/data$ cat /var/lib/postgresql/12/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'
shared_buffers = '200MB'
synchronous_standby_names = 'pgstandby_synch1'


texts: Why does SQL Server's RESTORE VERIFYONLY command fail intermittently?

texts:
Our maintenance scripts run RESTORE VERIFYONLY WITH CHECKSUM after each database is backed up to our backup server. A few of the databases often fail with the error shown below. But if we re-run the exact same verify command manually later, it runs without a problem and says the file is valid.

Msg 3203, Level 16, State 1, Server SQL11, Line 1
Read on "\\192.168.168.2\Backup\DBName\FULL\DBName_FULL_20160214.bak" failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Server SQL11, Line 1
VERIFY DATABASE is terminating abnormally.

Why does VERIFYONLY fail when the maintenance plan is running but not when we run it manually?


texts: Cannot access SQL Server - SQL Error 18456

texts:
SQL Server Login is failing for one user where as others can access.

Login is Windows Authenticated (Part of AD group user and admin access)

I can see login in list of xp_logininfo members for that particular group in SQL Server Security

Mixed Mode is enabled (Both SQL & Windows Login)

But still getting

  Login failed for user 'Domain\Username'. Reason: Could not find a
  login matching the name provided. [CLIENT: ]


texts: Ascending keys getting bad plans in SQL 2016 (new CE), replacement for TF 4139 et al.?

texts:
The new cardinality estimator (CE) in SQL Server 2016 has an unfortunate regression which hits our deployment hard. The query in question is a run-of-the-mill Hibernate generated update; however the updated table is also part of an indexed view. This is a one-row update that will typically also affect ~5 rows in the indexed view.

I've managed to narrow the problem down to cardinality estimation of the PK-column of a 2.5M table, if the query is first compiled for a PK-value inside the current statistics for this column all is well and an optimal  plan is generated.

If the PK was newer, id est outside the statistics range, CE 130 generates a very suboptimal plan, essentially estimating that the whole indexed view will be affected by the update. This was never an issue for us with CE 120 or CE 70, both estimators generating good plans. The leading column in question is "branded" as UNKNOWN.

I have however found that there are several trace-flags (2388-90, 4139) that could be used for the old query planner, but these have no effect on CE 120+.

Is there any newer settings or trace-flags I can invoke to ensure that the normal query plan will be found every time?

I have tried TF 9481 to force the old CE, then it works brilliantly, but this is hard to combine with Hibernate (it's hard to inject hints into the query, and I really don't want to muck about in Hibernate's internals). I've also tried TF 4199 and the newest CU, to no avail.


texts:
I have a really big problem with my database:

There was an electrical failure, a hard drive got damaged, whenever I tried to start the mysql server I got the error "I/O ERROR", I found that the ibdata1 file was corrupted.

I ran the mysqld_safe --innodb_force_recovery=6 command wich seemed to have done something, but now when I try to start the server, it gives me this error:

2018-01-26 13:59:08 0 [Note] Binlog end
180126 13:59:08 mysqld_safe mysqld from pid file /var/db/mysql/demo11.pid ended
180126 14:08:45 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
2018-01-26 14:09:01 0 [Warning] Failed to normalize the argument for --secure-file-priv.
2018-01-26 14:09:01 0 [ERROR] Aborting

2018-01-26 14:09:01 0 [Note] Binlog end
180126 14:09:02 mysqld_safe mysqld from pid file /var/db/mysql/demo11.pid ended

I tried commenting the line where the secure-file-priv option is in my.cnf file but the error just keeps apearing and I really want to know if my data is still in there.

I also tried to copy my entire mysql directory into another server but it also wouldn't load, I'm really freaking out right now.


texts:
How do I retrieve data in SQL from a previous date in the pass 12 months in a WHERE CLAUSE using the date and time functions?

--ServiceDates in past 12 months
WHERE YEAR(CONVERT(DATE, CLM_FROM_DT)) = 2016.


texts: Looking for industry standard and/or tool to represent and export RDBMS metadata

texts:
Do you know of an industry standard that is designed to describe the metadata contents of a relational database (or part of it, such as tables, columns, etc.)?

Do you know if a tool exists that does export the metadata of any of the top 5 RDBMS tools in any format such as XML (without having to type in DDL in the syntax of particular database)?

Thank you.


texts: Many DBMS implement views. However, do some of them implement change data capture on views (rather than actual tables), in a near real time fashion ?


texts: Separate table creation at runtime or add column

texts: I am planning to keep patient data in a patient table. I will have multiple Clients registered with me, and each client will have multiple doctors. Should I make patient table client wise or a single table with client and doctor ID field?


texts: Does InnoDB allow adding columns to a table with concurrent READ/WRITE of rows?

texts:
I have a large (3M rows) InnoDB table in MySQL 5.6.21 in production and I want to add some columns.

From the MySQL Documentation on Overview of Online DDL I understand that, even if this is an expensive operation that might degrade performance, concurrent DML is allowed as long as I don't add auto-increment columns, and concurrent queries (SELECTs) are always allowed.

Is this right? Do I have to explicitly state ALGORITHM=INPLACE and LOCK=NONE in the alter operation for this to be possible?

I'll be adding mainly unindexed, not null VARCHAR columns.


texts:
I have two tables, CLUSER and CLUSERRX. CLUSER contains all the employees at my company and CLUSERRX contains a list of all the people in CLUSER with a specific workgroup and all of the users from CLUSER with a similar, but not identical workgroup. The columns for these tables are as follows:

CREATE TABLE [dbo].[CLUSER]
  (
     [LOGONID]   [VARCHAR](15) NOT NULL,
     [WORKGROUP] [VARCHAR](30) NOT NULL,
     PRIMARY KEY CLUSTERED ( [LOGONID] ASC ) WITH (...other settings...,
       FILLFACTOR = 99) ON [PRIMARY]
  )
ON [PRIMARY]

CREATE TABLE [dbo].[CLUSERRX]
  (
     [LOGONID] [VARCHAR](15) NOT NULL,
     [RXUSER]  [VARCHAR](10) NOT NULL,
     CONSTRAINT [USERRX_LOGONID] PRIMARY KEY CLUSTERED
       ( [LOGONID] ASC, [RXUSER] ASC ) WITH (...other settings...,
     FILLFACTOR = 99) ON [PRIMARY]
  )
ON [PRIMARY]

I am able to generate a list of every CLUSERRX.LOGONID, CLUSER.WORKGROUP for that LOGONID, CLUSERRX.RXUSER, and CLUSER.WORKGROUP for the RXUSER using the following query:

SELECT u.LOGONID,
       u.WORKGROUP as 'USER WORKGROUP',
       r.RXUSER,
       p.WORKGROUP as 'PROVIDER WORKGROUP'
FROM   CLUSERRX r
       LEFT JOIN CLUSER u
         ON u.LOGONID = r.LOGONID
       LEFT JOIN cluser p
         ON p.LOGONID = r.RXUSER

What I need to do is take the data in CLUSER and find what relationships are missing in CLUSERRX.

Example data:

CLUSER
|-----------|------------|
|  LOGONID  |  WORKGROUP |
|-----------|------------|
| JCURCIO   | 001 USER   |
| TUSER     | 001 SUP    |
| ATEST     | 001 SUP    |
| MPAGE     | 001 User   |
|-----------|------------|

CLUSERRX
|-----------|------------|
|  LOGONID  |  RXUSER    |
|-----------|------------|
| JCURCIO   | TUSER      |
| JCURCIO   | ATEST      |
|-----------|------------|

Since there is no record for MPAGE in CLUSERRX for TUSER or ATEST that is incorrect and I need to display that.

I feel like I need to use something like:

SELECT u.LOGONID,
       u.WORKGROUP AS 'USER WORKGROUP',
       r.RXUSER,
       p.WORKGROUP AS 'PROVIDER WORKGROUP'
FROM   CLUSERRX r
       LEFT JOIN CLUSER u
              ON u.LOGONID = r.LOGONID
       LEFT JOIN cluser p
              ON p.LOGONID = r.RXUSER
WHERE  ( u.LOGONID NOT IN (SELECT u.LOGONID,
                                  u.WORKGROUP AS 'USER WORKGROUP',
                                  r.RXUSER,
                                  p.WORKGROUP AS 'PROVIDER WORKGROUP'
                           FROM   CLUSERRX r
                                  LEFT JOIN CLUSER u
                                         ON u.LOGONID = r.LOGONID
                                  LEFT JOIN cluser p
                                         ON p.LOGONID = r.RXUSER)
         AND p.WORKGROUP NOT IN (SELECT u.LOGONID,
                                        u.WORKGROUP AS 'USER WORKGROUP',
                                        r.RXUSER,
                                        p.WORKGROUP AS 'PROVIDER WORKGROUP'
                                 FROM   CLUSERRX r
                                        LEFT JOIN CLUSER u
                                               ON u.LOGONID = r.LOGONID
                                        LEFT JOIN cluser p
                                               ON p.LOGONID = r.RXUSER) )

But I have no idea how to format it properly to get the information I need. Can anyone provide any insight on what I may be missing, or if I am even in the right direction?


texts: SELECT using JOIN where result counts per join are less than n?

texts:
I am trying to query tableA, joining or sub-querying on tableB, where the count of matching rows from tableB are less than 4.
Example:
tableA
+-------------------+
|columnA|columnB    |
+-------------------+
|item1  |focaccia   |
|item2  |spinach    |
|item3  |goat cheese|
|item4  |beets      |
+-------------------+

tableB
+-------------------+
|columnA|columnB    |
+-------------------+
|blah1  |focaccia   |
|blah2  |focaccia   |
|blah3  |focaccia   |
|blah4  |spinach    |
|blah5  |goat cheese|
|blah6  |goat cheese|
|blah7  |goat cheese|
|blah8  |goat cheese|
|blah9  |goat cheese|
|blah10 |goat cheese|
|blah11 |beets      |
|blah12 |beets      |
|blah13 |beets      |
|blah14 |beets      |
|blah15 |beets      |
|blah16 |beets      |
|blah17 |beets      |
+-------------------+

The desired result from my SELECT from tableA here would be:
+-------------------+
|columnA|columnB    |
+-------------------+
|item1  |focaccia   |
|item2  |spinach    |
+-------------------+

... since the count of other matching items is greater than 4 each.
Is this possible?


texts: How could I use binary log as relay log?

texts: I has a full backup of MySQL, and binary logs from the time of starting full backup.Can I restore the data in another instance use replication like only start slave sql_thread; rather than mysql < mysql-binlog.xxxx?


texts: Select previous value in row without using a subquery

texts:
Say you have a table in PostgreSQL 11 with sets of values something like this below.

12 18
11 12
5  12
9  18
7  19
14 18
11 16
6  12
4  11
5  9

Is there a way to do a select without using sub-selects or select by using functions or trigger/functions to find where each value is lower than previous or could be higher than previous but this example is lower than previous (could be 2 row or 15+ rows) in both column and first column went higher than last row or both first and second column to terminate the select making the condition positive?

I do not know of a way to have present variables inside of a select.  If I could keep a look back variable then I should be able to do this.  I am wondering if anyone out there has ever come across this and made it work inside a select or they went outside of the database into GoLang / Python / C++ / Perl / PHP / etc.  Very simple to do once I leave the database but I would really like to keep this inside PostgreSQL 11.


texts: How can I calculate the cost of the query, if there is a `between` condition?

texts:
How can I calculate the cost of the query, if there is a between condition?

Select *
From A
Where A.id between 10 and 50

If Index not exists and there is only one condition:

Search in B+ tree to find the first block that meets the condition (Binary search).
Cost: log_2(b_A).
Search in the following blocks that meets the condition. (Suppose half of the blocks meet the condition). Cost: b_A/2.

If the condition was Where A.id > 10, then the cost would be: log_2(b_A) + b_A/2
If the condition was Where A.id < 50, then the cost would be: log_2(b_A) + b_A/2

What is the correct way to calculate cost of 2 ranges of the same field?


texts:
I have a query on Postgres, and I also have added proper index. Is there anything missing here?

SELECT orders.*, demo.name as d_name
FROM orders
LEFT JOIN users as demo ON demo.id = orders.dr_id
WHERE orders.customer_id = 526373
AND (orders.log_id = 300)
AND (orders.order_count_id IN (10, 1, 8, 2, 3))
LIMIT 5

Query plan from EXPLAIN ANALYZE:

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=2490.30..2867.15 rows=3 width=182) (actual time=33.292..34.115 rows=3 loops=1)
->  Nested Loop Left Join  (cost=2490.30..2867.15 rows=3 width=182) (actual time=33.291..34.113 rows=3 loops=1)
     ->  Bitmap Heap Scan on orders  (cost=2490.02..2842.22 rows=3 width=172) (actual time=33.279..34.097 rows=3 loops=1)
           Recheck Cond: ((customer_id = 526373) AND (order_count_id = ANY ('{10,1,8,2,3}'::integer[])))
           Rows Removed by Index Recheck: 1046
           Filter: (log_id = 300)
           ->  BitmapAnd  (cost=2490.02..2490.02 rows=89 width=0) (actual time=33.107..33.107 rows=0 loops=1)
                 ->  Bitmap Index Scan on idx_orders_customer_id (cost=0.00..146.54 rows=7747 width=0) (actual time=1.134..1.134 rows=6026 loops=1)
                       Index Cond: (customer_id = 526373)
                 ->  Bitmap Index Scan on idx_orders_order_count_id (cost=0.00..2343.23 rows=126540 width=0) (actual time=31.018..31.018 rows=70883 loops=1)
                       Index Cond: (order_count_id = ANY ('{10,1,8,2,3}'::integer[]))
     ->  Index Scan using users_pkey on users demo  (cost=0.28..8.30 rows=1 width=14) (actual time=0.003..0.003 rows=0 loops=3)
           Index Cond: (id = orders.dr_id)

**Total runtime: 34.233 ms**


texts:
I'm building a database for a divination system. Just to make it easy, I'll explain it in terms of the i ching.

Every element of a hexagram is a different table. There's one for yin and yang, one for the 3 positions in a trigram, one for the trigrams, one for the hexagrams. There's also a table for the correspondences, which is linked to each of the other tables - correspondences for yin or yang, for the 3 positions, the trigrams, and the hexagrams. There is also a table with commentary, which also links to each of the other tables (except for the correspondence table).

So, the table for the trigrams, for example, might be structured like this:

TriID
TriName
Line1                    -FK (YinYangID)
Line2                    -FK (YinYangID)
Line3                    -FK (YinYangID)
TrigramMeaning
Correspondence           -FK (CorrespondencesID)
Commentary               -FK (CommentariesID)

With data like:

 TriID  TriName Line1 Line2 Line3 TriMeaning Correspondence  Commentary
 1      Qian    Yin   Yin   Yin   Receptive  1               1
 2      Kun     Yang  Yang  Yang  Generation 2               2

The Correspondence table looks something like this

 CorrID    Person    Season     Color     Animal    Number     Element
 1         Father    Winter     Blue      Bear      3          Metal
 2         Mother    Summer     Red       Deer      6          Earth

(I know that all my I Ching data's wrong...)

The idea is that the same correspondences are required for every table, so the correspondence table would have an entry for each of the other tables. CorrespondencesIDs 1 through 4 might apply to the YinYang table, for example (Old and New Yin, old and new Yang), and then 5 through 13 to each of the trigrams, and so on.

It seems like this would work just right. On the other hand, it feels intuitively like I'm doing something wrong and breaking some rules. Since every entry on the correspondence table will be different, it seems like there should maybe just be similar entries on every table:

TrigramPerson
TrigramSeason
TrigramColor
TrigramAnimal

and then

HexagramPerson
HexagramSeason
HexagramColor

and so on.

What is the smart / best way to structure the database? Apologies if this is a dumb question - I'm new to database design, and some of these concepts still confuse the heck out of me.


texts: SQL Server to Oracle Export - Data conversion error

texts:
I have a rather weird problem with exporting a bunch of tables from SQL Server 2005 to Oracle database. I have SQL Agent job that deletes Oracle tables, recreates them and then exports the data using linked server and OPENQUERY. The entire script is here.

My problem is, the job fails while exporting data at the last table PSRPT_TEAM_ROLES_PSFT. The error message from the log files says the job failed due to a data conversion error: Msg 8114, Sev 16, State 5, Line 4 : Error converting data type varchar to numeric. [SQLSTATE 42000]. I have identified the column that is likely causing this error, BO_ID which is DECIMAL(31,0) at source and exported as NUMERIC(31,0) to destination. When I alter the destination column to NVARCHAR2 and export, the job doesn't fail but the Oracle guys told me that it significantly increased the size of the table. The weird part is, when I run the export query manually, even when the datatype NUMERIC(31,0) at destination I see no error. So I guess my question is

Am I missing something obvious?
Why does this table export fail when run as a SQL agent job and work perfectly when run manually?


texts: Unsure of Rank / Dense_Rank - Mulitple Columns - change rank when unique combination changes

texts:
I need to get the unique combinations of 2 fields, with a RankID for each unique combination

In this example I need to create new IDs for each Customer/Value combination.

Source table:

CustID     Value
1   20.00
1   50.00
1   50.00
2   30.00
2   50.00
2   50.00

Should return this please ( change when either of the columns change)

Nr  CustID     Value
1   1   20.00
2   1   50.00
2   1   50.00
3   2   30.00
4   2   50.00
4   2   50.00

Thus there are 4 unique combinations of Customer/Value
and thus 4 nrs.

SQL:

declare @table table(
CustomerID int,
Value       decimal(18,2)
)

insert Into @table(CustomerID,Value)values(2,50)
insert Into @table(CustomerID,Value)values(1,50)
insert Into @table(CustomerID,Value)values(1,50)
insert Into @table(CustomerID,Value)values(2,50)
insert Into @table(CustomerID,Value)values(1,20)
insert Into @table(CustomerID,Value)values(2,30)

select * from @table

select  RANK()   OVER(PARTITION BY CustomerID,Value ORDER BY  Value  DESC) as Rank ,
* from @table

http://www.sqlfiddle.com/#!3/7e60e/3


texts: Installing 10g client next to 11g database on same computer

texts:
I have Oracle 11.2.0.1.0 installed on Windows 7 64 bit

I want to import data from warehouse which has Oracle 10.2.0.4.0 installed.

When I try to import

impdp usr/pass@WAREHOUSE TABLES=TABLE DIRECTORY=DATA_PUMP_DIR NETWORK_LINK=D_W VERSION=10.2

It gives :

UDI-00018: Data Pump client is incompatible with database version 10.2.0.4.0

To overcome version incompatibility, I will install 10g client on my computer.

But I already have 11gr2 database installed, so I wanted to learn about potential problems.

Especially about having two Oracle Homes, tnsnames.ora files, etc..

Thanks.


texts: How to log the values that cause a unique constraint violation

texts:
I have a lengthy INSERT statement running on Oracle that fails with:

ORA-00001: unique constraint (MYPROJECT.MYTABLE_PK) violated

The constraint is:

CONSTRAINT "MYTABLE_PK" PRIMARY KEY ("ENCOUNTER_NUM", "CONCEPT_CD", "PROVIDER_ID", "START_DATE", "MODIFIER_CD")

The problematic INSERT statement is logged, but not the values causing the constraint violation.

Is there a way to log or otherwise save and view the values that violated the constraint?

This is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


texts:
Let's say we have two identical tables in two different databases.
Each have a primary key field that is auto incremented. For example:

TestDB: Table1
Key     Value
1       Mike
2       Jane
3       Stephanie

ProdDB: Table1
Key     Value
1       Mike
2       Jane
3       Stephanie

We add some records and delete some records from test, so they no longer match:

TestDB: Table1
Key     Value
1       Mike
4       Jacob
5       Lily
6       Laura

ProdDB: Table1
Key     Value
1       Mike
2       Jane
3       Stephanie

We need the data on production to match what is on test. Due to auto increment, we can't simply delete all the records and then push them in fresh, their keys would be 4,5,6,7 instead of 1,4,5,6. We can't reseed after, that would make them 1,2,3,4.

We could drop the table and just copy the whole thing over again, but from what I am told doing that too many times could corrupt the database.

I had thought that we could simply turn off auto increment, update the records, and then turn auto increment back on, but my DBA tells me that wouldn't work either. There must be a fairly standard process for moving data between test and production, so what are we missing?


texts:
Does MySQL use transactions for DROP DATABASE... statement (all tables use InnoDB engine)?

Basically I would like to know if any data will be lost if I kill DROP DATABASE... query while it's running?

I've tested this with tables and it works but I don't have big enough database to test it:

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (8.57 sec)

mysql> DELETE FROM t1;

Another MySQL session:

mysql> SHOW PROCESSLIST;
+-----+------+-----------+------+---------+------+----------+-------------------+-----------+---------------+
| Id  | User | Host      | db   | Command | Time | State    | Info              | Rows_sent | Rows_examined |
+-----+------+-----------+------+---------+------+----------+-------------------+-----------+---------------+
| 211 | root | localhost | test | Query   |    0 | init     | SHOW PROCESSLIST  |         0 |             0 |
| 242 | root | localhost | test | Query   |    4 | updating | DELETE FROM t1    |         0 |        879266 |
+-----+------+-----------+------+---------+------+----------+-------------------+-----------+---------------+
2 rows in set (0.00 sec)

mysql> KILL 242;
Query OK, 0 rows affected (0.00 sec)

back to first session

mysql> SELECT COUNT(*) FROM t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    283
Current database: test

+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (6.91 sec)


texts:
I´m using pgadmin and I´ve created a server, when trying to connect I get the following error:

Have you ever seen this problem?

Many thanks


texts: SQLAnywhere: Syntax error on call function; Call without IF construct is working

texts:
I've got this little code example where I call a procedure, which is taking 4 arguments:

DECLARE @tmp BIT
SET @tmp = 0
IF @tmp = 1
    call __insert_to_notifications_table('foo', '1234', '5678', 'Some comment')
ELSE
    print 'Path, which is choosen'

When running this, I'm getting

Syntaxerror at 'call' in line 4
SQLCODE=-131, ODBC 3-Status="42000"

If I run

call __insert_to_notifications_table('foo', '1234', '5678', 'Some comment')

directly, it is working fine. (the procedure is really called with double __ at the beginning; Also when renaming it's not working)

When running the code like this:

DECLARE @tmp BIT
SET @tmp = 0
IF @tmp = 1
    print 'other path'
ELSE
    print 'Path, which is choosen'

it seems to work. So I would hunt the issue down to the call of call.

What did I wrong here?


texts:
I'm using MySQL 5.6 with about 5 databases on linux. I have scheduled daily full backups using mysqldump. All databases use InnoDB engine.

What I observe is that the backup size seems to vary everyday. This would make sense if the backup size keeps increasing but what is happening is randomly the backup size goes down.

I have confirmed with application teams that do not purge or delete data and I understand mysqldump is a logical representation of the data but how can backup size decrease randomly when there is no delete/truncate of data?


texts: Use a SP to Archive/Revision related records in several related tables?

texts:
I have a database for an application that has a complicated relational table structure. Is there a good way to create revisions of the data, or archive the data before a user makes changes?

Ideally I would want a stored procedure that would save the state of all the related data when ran, insomuch that when a field is added to any of the related tables, the stored procedure would not miss it.


texts: Problem creating subscriptions on Replication Server version 15.7.1

texts:
if anyone knows what is the error? Can not use active or standby connection names for subscriptions
I got this error when I'm trying to create my subscriptions. I have already created a repdef WITH PRIMARY AT pb1.customer and when I tried to run my subscription, WITH REPLICATE AT pb2.customer. It is showing the error. I have also created a logical connection pb1.customer for dump/load db.

pb1 and pb2 are both ASE, ASE15.5 and ASE15.7 respectively.

Please advise.


texts: Using schema + Filegroups or entirely separate database(same instance) to partition some big tables?

texts:
I have the following challange:

I have a staging database which holds a few big tables (on SSD) but I start to run out of space on the SSDs. I use SQL server 2014 BI-Edition (= Standard Edition for the relational engine) so table partitioning out of the box is not available. My approach would be the splitting of the data into separate tables and joining them together in a view.

The data itself is (mostly) written once and then only read from.

What would be a better solution of the two suggested ones below? Why?

Solution A: Use Filegroups to move data to different disks:

create a two new filegroup on a separate disks (non-SSD, RAID5)
create several tables (e.g. one per Quarter Per Year) and move the old data to those tables on some rules (e.g. move data older thant 6 months)
one filegroup is readonly and the other one is read write. More current data resides in the read write filegroup and the older data is on the readonly filegroup
create the same index (same columns) for all tables
use a view to have transparent access to all data

Solution B:Use a database to hold the historic data

Implemented in more or less the same way but addng a database (in the same instance) as an additional layer

Thanks for sharing your ideas and advice.


texts: I only have backup of a TDE encrypted SQL Server database--can it be restored it to another server?

texts:
My situation is little weird--I have no access to the source database server, but I do have the database backups of a database that is encrypted with TDE.  When I restore it to another server, I am getting an error about the certificate.

I cannot access the source master database.  How can I restore the encrypted database on another server?