texts:
I've stumbled across a database design problem that's out of my league, and my go-to DBA guru is off on fire drills.

In essence, I have a table with the following primary key (PK for brevity):

child_id   integer
parent_id  integer
date       datetime

child_id and parent_id are foreign keys to entity tables. The "child" table itself also contains a foreign key to the "parent" table, and lo, each child_id always references the same parent_id as expected by the table above. In fact, turns out there's some extra code keeping the two in sync.

Which makes this overenthusiastic normalization novice say "I should remove the redundancy instead!"

I decompose to the following:

Table_1 PK:
child_id   integer
date       datetime

Table_2 PK:
parent_id  integer
date       datetime

Table_3: (already exists)
child_id   integer PRIMARY KEY
parent_id  integer FOREIGN KEY

And lo, when I join these guys together in the natural way, I recover the original table. It's my understanding that makes this 5NF.

However, now I realize there's a hidden business rule.

Normally, the dates associated with a given child_id must be a subset of the dates associated with the corresponding parent_id. You can see that the first table enforces this rule.

My decomposition does not enforce the rule, because you can freely add to Table 1 until the dates get too large.

Which leads me here, with the following questions:

Is this decomposition 5NF? While I'd say it permits insertion anomalies, it also seems to follow the Wiki example, which itself follows this guide. The phrase (emphasis mine) "we can reconstruct all the true facts from a normalized form consisting of three separate record types" gives me special pause, since no matter how much garbage I pump into Table_1, the natural join still ignores it.
Supposing I don't like this decomposition (I don't). I freely acknowledge that the practical solution is to leave the table and code as they are. But, in theory, is there a way to decompose and/or add constraints such that I get away from the first table and preserve my business rules?


texts: Why don't SQL Server queries use more than 7MB/sec of disk I/O

texts:
I have an SSD that, using IOmeter test, shows performance over 200MB/s.  However when I run any SQL query from local machine the windows resource monitor never shows disk IO above 7MB/sec.  This holds true even for queries that take over 2 minutes to run.  What could the bottleneck be that it is only using 7MB/sec from SSD?

I'm running:

Windows Server 2012 Standard
SQL server 2008 r2
Intel i7 3820
32GB of ram
sandisk SSD


texts: Why is PostgreSQL reporting a higher shared_buffers value than the parameter in the configuaration file when trying to enable huge_pages?

texts:
On a 7.5 GB host (Ubuntu 16.04.5 LTS), I have allocated 3 GB to shared_buffers:

postgres=# SHOW shared_buffers;
shared_buffers
----------------
 3GB
(1 row)

with:

postgres@my-host:~$ cat /etc/postgresql/10/main/postgresql.conf | grep shared_buffers
shared_buffers = 3072MB         # min 128kB, valid units are kB, MB, GB, TB

I have also configured my host with HP as:

postgres@my-host:~$ cat /proc/meminfo | grep ^Huge
HugePages_Total:    1600
HugePages_Free:     1600
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

With a Hugepagesize at 2048 kB, that works out as 1600*2048/1024 = 3200 MB of HP. When I then try to turn:

postgres@raeflightcachemaster0-nonprod:~$ cat /etc/postgresql/10/main/postgresql.conf | grep huge_pages
huge_pages = on

PostgreSQL (10.5) is reporting:

2018-09-27 09:31:54.397 BST [16739] FATAL:  could not map anonymous shared memory: Cannot allocate memory
2018-09-27 09:31:54.397 BST [16739] HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory, swap space, or huge pages. To reduce the request size (currently 3523592192 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
2018-09-27 09:31:54.397 BST [16739] LOG:  database system is shut down
pg_ctl: could not start server
Examine the log output.

and the DB server fails to start.

My question is, where is it getting the number 3523592192 bytes from? This value does not match the shared_buffers = 3072MB setting in postgresql.conf as it works out as 3523592192/1024/1024 = 3360.359375 MB. I get around the problem by increasing HugePages_Total slightly to 1700 so that we have 3400 MB of HP but I don't understand the discrepancy between the reported value of shared_bufferes and the configured value. Is this a units issue?


texts:
I have a linked MySQL -server to MSSQL-server and I am trying to INSERT data to the table admin_user on the MySQL -server, but end up getting the error:

  annot process the object "dbo.admin_user". The OLE DB provider
  "MSDASQL" for linked server "MYDB" indicates that either the
  object has no columns or the current user does not have permissions on
  that object.

This works fine:

SELECT * FROM openquery([MYDB], 'SELECT * FROM admin_user')

This gets the error:

INSERT into openquery([MYDB], 'dbo.admin_user') values ('Testi','Testaaja','me@google.com','koe','','','','','','1','N;','','')

Here are the rights of the user whom I used for creating the ODBC-connection

| xx.xxx.xxx.xx | me | *qweqweqwdq2edqdadasd| Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y
| Y | Y | Y | Y
| Y | Y | Y | Y
| Y | Y | Y | Y | Y | | | |
| 0 | 0 | 0 | 0 | | NULL |

>     | %             | me | *asdasadasdsadasdasdsad| Y           | Y           | Y           | Y           | Y           |
> Y         | Y           | Y             | Y            | Y         | Y
> | Y               | Y          | Y          | Y            | Y
> | Y                     | Y                | Y            | Y
> | Y                | Y                | Y              | Y
> | Y                  | Y                | Y          | Y            |
> Y                      |          |            |             |
> |             0 |           0 |               0 |                    0
> |        | NULL                  |

My catalog is bitnami_magento, I have the provider string configured with

DRIVER=(MySQL ODBC 5.3 ANSI Driver); SERVER= XX.XXX.XXX.XXX;PORT=3306;DATABASE=bitnami_magento; USER=me;PASSWORD=mypass;OPTION=3;

Also I have unchecked the "Level zero only" box from Provider Options (MSDASQL) and made sure that ad_hoc queries are allowed. Please tell me what I am doing wrong???

There are the instructions that I followed http://dbperf.wordpress.com/2010/07/22/link-mysql-to-ms-sql-server2008/


texts: Pinging SQL Server Availability Group Listener sometimes fails

texts:
I have a client with a Server Availability Group. Let's say the AG listener is named AG1 . The AG1 listener is set to static IP with two IP address. When I do a nslookup of AG1, the DNS entry has the same IP addresses. It's an active/passive AG with automatic failover and it hasn't failed over.

If I open a command prompt and ping AG1 , sometimes it succeeds (primary ip replies) and sometimes it fails (secondary ip address is unreachable). In other words, the "Ping AG1" command reply sometimes tries to ping the IP address of the Primary, and in that case it succeeds. Other times the "Ping AG1" command sometimes tries to ping the IP address of the secondary, and in that case it fails. It seems like it switches back and forth every 30 seconds or so.

Is this normal behavior? I would expect the primary to always respond , but perhaps ping isn't intended to work with an AG?

Update the AG group seems to be setup like the pics in this documentation https://docs.aws.amazon.com/quickstart/latest/sql/step3.html
where the AG listener's Network Mode is Static IP with two IP addresses


texts: Converting DATE and TIME integers to timestamp


texts:
how I would copy a table with all constraints?

I run select * into myNewTable
from baseTable
where 1=0

This copy a table but no with constraints


texts: Postgresql perl : "NOTICE: elseif should be elsif at line ..."

texts:
It's not the first time I get this strange NOTICE when adding perl functions in Postgresql :

NOTICE:  elseif should be elsif at line 11.

This happens at CREATE FUNCTION as well as when using the function.
Of course I tried to change with elsif but then it fails with syntax error.

Any advice would be appreciated (even if I can cope with that NOTICE)

This is my code:

CREATE FUNCTION hds_updatewebservers_products(character varying)
   RETURNS boolean
AS
$_$
   my $product_ref = @_[0];
   # Launch update
   $checkexitcode = system ("sudo -u dbliveud /home/dbliveud/scripts/maj/launch-all.sh " . $product_ref . " ");
   if ($checkexitcode > 0) {
     # Error while updating
     return false;
   } elseif {
     # Ok update done
     return true;
   }
$_$
LANGUAGE plperlu STRICT;


texts:
I'm quite the beginner when it comes to databases, so please bear that in mind.

I need to store user statistics in several categories for an application.

In the near future I will most likely need to add another application, with different categories, but for the same user.

Both applications will likely change which data is saved over time. perhaps via adding or removing individual fields or even entire categories.

Taking into account performance, maintenance and simplicity of future queries, what's the best approach to database design in this case?

Should I have one table for each application?

Or should I divide everything into smaller tables, maybe even stored in a different database?


texts: How connect to DB PosgreSQL in pgAdmin4 with port 84?

texts:
Install DB Server PosgreSQL
postgresql-9.6.1-1-windows-x64.

After Install I change number standard port to 84.
In postgresql.conf

port = 84               # 5433 (change requires restart)

I restarted service and new port working.

After I try to connected DB Server by pgAdmin4.

But when I change Connection Port pgAdmin4 wrote ERROR

Can I connected to DB with port less 1024?


texts: ORA-00959: Tablespace '$name' does not exist when using impdp

texts:
I'm new at Oracle databases. I'm executing an impdp command and encountered this error:

  ORA-00959: tablespace '$name' does not exist.

Is the only thing I can do is to create the $name tablespace?


texts: What is the best way to manage user access to MySQL via PHP?

texts:
I am working on a web application that requires auditing db queries in MySQL. In every application that I have developed or worked on it is standard practice to store the DB credentials in a protected configuration file and access the DB through that one user. Auditing, then, must be managed by the code (I'm using PHP).

My question: would it be better to create user accounts for each user in MySQL and use these credentials to create the DB connection? Doing so would allow MySQL to log the queries by each user either through the general query log or Mcafee's mysql-audit plugin.

I have read some older posts that claim allowing multiple user accounts creates security vulnerabilities, but I have not read a clear explanation of how/why.

It may be worth noting: admins create user accounts and set user permissions. Users cannot self-register.

Thanks for any help you can provide.


texts: Generate fake data for several tables with relationships

texts:
I have 3 tables where I want to generate fake data in UsersCategoryLink.

How can I insert in the table UserCategoryLink the column UserID with random users from the table User and a random id from the table Categories. In this SQL fiddle you can see the tables with some values.

UsersCategoryLink must be filled with random users and categories.
Every user must have two categories.
UserID and CategoryID are primary keys, so every value must be unique.
I am using SQL Server Express.


texts:
I have to store frequently (at least every min) the position (longitude, latitude) of animals in a DB, and perform for each update a search in order to determinate if ananother animal is near to it.

My question is about performance. I plan to use MySQL and FLOAT (10,6) for these variables.

Does a better SQL type exist for this kind of problem in MySQL?  It seems Microsoft released their GEOGRAPHY TYPE, maybe something exists in other DB manager?
Is it a good idea to create an index on these values (because I update them a lot but also search on them a lot)? If yes, what kind of index is the best (CLUSTERED)?
Do you have any advice for querying the DB?

Thanks.


texts: Maintenance Cleanup Job Task Fails but Backup Jop is successful

texts:
I get this error:

  Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'\\SQLSvr..." failed with the following error: "xp_delete_file() returned error 2, 'The system cannot find the file specified.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The server is running SQL Server 2008 R2 doing backups on one server to save to a remote server using UNC paths; file extension is ".bak".


texts: Performance issue on SQL Server 2008 R2 hosted on Windows Server 2008 R2

texts:
Our client is using SQL Server 2008 R2 SP3 on Windows Server 2008 R2 Enterprise SP1. I download their MDF.

Their server is:

Intel XEON E5-4620 2.20Ghz 128 GB RAM (SQL Server using 64GB, 20% free).
64 cores on 4 processors.
RAID SSD I/O system (but doesn't matter because physical reads is 0).

My computer is:

Intel i7 5700HQ 2.7GHz 16 GB RAM (SQL Server using 8GB).
4 cores on one processor.

I query simple Select * from StokKart. Table has 68k rows and 20 columns.

Client server results

(68648 row(s) affected)
Table 'StokKart'. Scan count 1, logical reads 3854, physical reads 0, read-ahead reads 0,
 lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:    CPU time = 640 ms,  elapsed time = 4013 ms.

My laptop results

(68600 row(s) affected)
Table 'StokKart'. Scan count 1, logical reads 3847, physical reads 0, read-ahead reads 0,
 lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:    CPU time = 187 ms,  elapsed time = 1742 ms.

I installed a fresh SQL Server on my machine using the client's backup and Windows 7. Client's Server always 4-4.5 seconds. Windows 7 machine under 2 seconds. Client always complains about a slow application. All indicators are normal. No I/O bottleneck, full RAM, or high CPU usage.

I tested the server at night when nobody was using the system. I use SQL Server Activity Monitor and Windows Resource Monitor for monitoring.

Query plans are exactly the same.

Server:

My Notebook:

I tried SQL Server 2014 on same server. Still 4000 ms and mine 1600-1700 ms.

Changing the query to Select Count(*) from Stokkart, the results are:

Server : CPU time = 16 ms,  elapsed time = 11 ms.
My Laptop : CPU time = 0 ms,  elapsed time = 3 ms.

I tested the server using its own local SSMS (local); my laptop, my SSMS, so not a network issue.


texts: Query returning data from two rows of a table in a single row

texts:
My table looks like this:

ID     APPLICATION    OTHERVALUE
0001   subapp         SubvalueA
0001   mainapp        MainValue1
0002   subapp         SubvalueB
0002   mainapp        MainValue1
0003   subapp         SubvalueA
0003   mainapp        MainValue2

I need all lines of subapp, but then I also need value of OtherValue when the application is mainapp from same table where ID is the same. I'm using Oracle.

How can I build a query for this?

Expected result:

ID     APPLICATION    OTHERVALUE    OTHERVALUE2
0001   subapp         SubvalueA     MainValue1
0002   subapp         SubvalueB     MainValue1
0003   subapp         SubvalueA     MainValue2


texts: How to extract the grants on default roles of PostgreSQL 10: pg_read_all_settings, pg_read_all_stats, pg_monitor and pg_signal_backend?

texts: I have a Postgres instance of version 10 running on AWS RDS. I have a user there with default roles granted to it. I want to create the same user on my sandbox where the instance is of version 9.4. Thus, I want to know the grants that are included in these default roles of version 10.


texts:
Would it be possible to give a table in MySQL query multiple aliases?

If so how would I do this?


texts: How to identify row change timestamp columns in Db2 Table

texts:
I need to copy some Db2 tables programmatically (java 11 using the standard Db2 jdbc driver). The tables in question lie on a Db2 for z/OS database (v12) and on a Db2 LUW database (v11.1). The target tables exist and have all the required columns.
Some tables contain rows defined like this:
<SOME_COL_NAME> TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

In order to do the copy, I wish to use the Db2 load utility and row change timestamps need to be preserved. The syntax for this is identical for both Db2 types. When the table contains a row change timestamp, then the LOAD statement requires a ROWCHANGETIMESTAMPOVERRIDE modifier. All this has been gleaned from the IBM documentation.
The difficulty is that I'm  unsure how to tell whether the table in question has a row change timestamp. For Db2 LUW it is possible to query SYSCAT.COLUMNS, since this has a a field called ROWCHANGETIMESTAMP which would be either 'Y' or 'N'. That would mean checking each column in each table, but it's doable. However, it would be nice if there was an easier way to determine whether the table has a row change timestamp.
For z/OS it isn't clear what should be checked. Is there a catalog query that would help?
I can provide more details, if necessary.


texts: SQL Server Log Shipping Randomly Stops Copying and Restoring

texts:
I'm fairly new to Log Shipping but have configured a few with no issues, however I have a server sitting on SQL 2008 R2 with 6 databases on there, all 6 of these are supposed to be log shipping. The issue I have is that for some reason one of the larger databases stopped shipping so I removed the Log Shipping and reinstated it from scratch for every 15 minutes. First of all my copy jobs are all working fine, it appears to be the restore jobs that are having a problem. Even though I have just taken a backup of one of the databases and restored it to the LS and then kicked of shipping again, the Restore job has a long list of:

  "Skipped log backup file. Secondary DB...."

messages and does this for every 15 minute file for the past 24 hours. One of the last messages states:

  "Could not find a log backup file that could be applied to secondary database"

What I don't understand is why is log shipping trying to restore all log files for the past 24 hours when I have just taken a full backup.

I get another message stating:

  "Could not find a log backup file that could be applied to secondary database"

which I don't understand as I can see files that are sitting there waiting to restore.


texts: To Change the Log file location of SQL Server 2014 in AlwaysOn

texts:
I did not find a way how to change Always On Log file location.

AlwaysOn Logs are saved in

  C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log

Even in configuration is set like this:

SQLBACKUPDIR="F:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup"

; Default directory for the Database Engine user databases.

SQLUSERDBDIR="D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data"

; Default directory for the Database Engine user database logs.

SQLUSERDBLOGDIR="E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data"

; Directory for Database Engine TempDB files.

SQLTEMPDBDIR="E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data"

We had a problem now that log file was 40 GB and filled drive C and server crashed.
This is only for Always on logs, all other logs are where they should be.
Databases and everything is ok. Just log files of AO.

Any ideas how to change AlwaysOn Log file location?

Thanks


texts:
I installed Sql Server 2019 in Windows PC.
In Sql Server Configuration Manager into SQL Server Service I stopped all the instances.
But still I can loggin to Sql Server via SSMS.why is that? Where is the Sql Server service?
In Windows Services I can see SQL Server (MSSQLSERVER01) that running and when I stopped it,I can't login to sqlserver. But why I can't see that service running in Sql Server Configuration Manager into SQL Server Service.
I login with server name:DESKTOP-IJ7544\MSSQLSERVER01


texts: SQL Server 2019 performance worse than 2012... am I missing something?

texts:
We have a SQL Server 2012 server which far outperforms a SQL Server 2019 database on (as far as I can see) the same infrastructure. We are hosting both databases on a cloud platform with the same SLAs. Both have 180GB RAM and 16 processors.

However there are a few key differences.

The 2012 database server is Enterprise, the 2019 is Standard. As far as I know, this shouldn't make a difference
The 2012 database was restored to the 2019 server and it's version changed to 150 (2019)
MAXDOP on the 2012 server was 0, 2019 server it is set to 8 as recommended by Microsoft and others
Cost threshold for parallelism = 5 on 2012 server, 20 on 2019 server

EDIT: Another major difference I just realised - one is Windows Server 2008,the other is Windows Server 2019. It could possibly be server side settings as well...

Allocation unit size is set to 64kb for all SQL disks, SQL has the right permissions to be able to control file sizes itself. Server is set to high performance mode. Anything else I should be changing server side?

Other database settings were not changed, so the following settings are default on 2019, I believe:

Legacy Cardinality Estimation = OFF
Parameter Sniffing = ON
Query Optimiser Fixes = OFF

Mainly the type of queries we do are large complex multi join queries performing updates and inserts, with the occasional small selects from users. We load large files to the database and then process the data in large queries, usually one at a time. In between these large "loads" we have users doing selects on other database tables not being loaded/processed in preparation for future load/process steps. Generally we are getting between 30%-50% performance reductions in processing.  I figured this was because of the MAXDOP setting, but altering it to 0 made no difference over a series of runs.

Our major symptom is we are getting lock timeouts when we try to connect to the 2019 server while it is busy processing, whereas the 2012 server still services connections, just very slowly. I was thinking of setting the connection timeout setting on the server to a high amount, however I suspect we still won't get responses from the server. It's like it's blocking all new connections if its even slightly busy.

Are there other things I should try? Are those database settings worth messing around with?

I could dive in further and start looking at DMVs, however this seems to be close to a "like for like" environment upgrade with considerable drops in performance. Just checking there isn't something else I should check before doing a bigger investigation.


texts: Why am I getting "Invalid object name 'sys.sysobjvalues'."

texts:
When I run

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.assembly_files'));

I can see that there is a self-join on sys.sysobjvalues, but when I try to select that table I get,

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.sysobjvalues'.

What's going on here? Why is that object name invalid?


texts: Impersonate user or login mapped to certificate

texts:
Assume that there is a certificate created in database

create certificate certName
    with subject = 'subj';
GO

And a user mapped to this certificate

create user userName
    from certificate certName;
GO

Trying to impersonate this user directly

execute as user = 'userName';
GO

or specifying user in the execute as clause of the module

create procedure procName
with execute as 'userName'
as
    set nocount on;
GO

returns error

  Msg 15517, Level 16, State 1 ...Cannot execute as the database
  principal because the principal "userName" does not exist, this type
  of principal cannot be impersonated, or you do not have permission.

However, I was not able to find this restriction mentioned in documentation (here and here), where the only relevant statement seems is

  user_name must exist in the current database and must be a singleton
  account. user_name cannot be a group, role, certificate, key, or
  built-in account, such as NT AUTHORITY\LocalService, NT
  AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

Is it possible to impersonate user (or login) mapped to certificate or not?


texts: Help restoring a mongodump 3.4 backup into a different instance

texts:
I am working on backup recovery for MongoDB 3.4. We have an instance that is running and being used. I set up a instance for doing testing of the backup and recovery process. In order to get data I did mongodump for each database and copied the files to my test server. Each backup write files to a directory that has the same name as the database. I used the gzip option to make it easier to move the files around. I am having issues trying to do the mongorestore. Can someone give me the syntax for mongorestore?

Here is the dump command

mongodump --port 27017 --authenticationDatabase admin -u my_admin_user
          -p password -o /backup_dir --gzip -d test_db

(split to multiple lines for readability)

These are the directories with the contents of my_test_db

root@my_vm:~/mongodb_backup_full_data_dump > ls -l
total 20
drwxr-xr-x. 2 root root 4096 Aug 24 13:30 admin
drwxr-xr-x. 2 root root 4096 Aug 24 13:51 data1
drwxr-xr-x. 2 root root 4096 Aug 24 14:20 data2
drwxr-xr-x. 2 root root 4096 Aug 24 14:20 data3
drwxr-xr-x. 2 root root 4096 Aug 24 14:20 my_test_db
root@my_vm:~/mongodb_backup_full_data_dump > ls -l ./my_test_db/*
-rw-r--r--. 1 root root    24263 Aug 24 14:20 ./my_test_db/journal_entries.bson.gz
-rw-r--r--. 1 root root      132 Aug 24 14:20 ./my_test_db/journal_entries.metadata.json.gz
-rw-r--r--. 1 root root      481 Aug 24 14:20 ./my_test_db/journal_users.bson.gz
-rw-r--r--. 1 root root      107 Aug 24 14:20 ./my_test_db/journal_users.metadata.json.gz
-rw-r--r--. 1 root root 14438174 Aug 24 14:20 ./my_test_db/test_collection.bson.gz
-rw-r--r--. 1 root root      104 Aug 24 14:20 ./my_test_db/test_collection.metadata.json.gz

and, this is what happens when I try to do the restore:

mongorestore --port 27017 --authenticationDatabase admin -u my_admin_user -p password -o /backup_dir \
--gzip -d test_db /root/mongodb_backup_full_data_dump/test_db/journal_entries.bson.gz

2017-08-25T15:24:05.487-0400    the --db and --collection args should only be used when restoring from a BSON file. Other uses are deprecated and will not exist in the future; use --nsInclude instead
2017-08-25T15:24:05.487-0400    checking for collection data in /root/mongodb_backup_full_data_dump/mediax
2017-08-25T15:24:05.487-0400    Failed: error scanning filesystem: file /root/mongodb_backup_full_data_dump/mediax is a directory, not a bson file


texts:
While installing the MS SQL Server 2012 Enterprise Edition, everything goes through fine but I'm not able to install Integration Services alone. I get the error as following:

  The following error has
  occurred:C:\SQLserver....\Binn\DTSSVCPERF.ini Click retry to retry
  the failed action.

I have tried re-installing many times, but still am facing the same issue.
The OS is Windows Server 2012.


texts:
Now I am using MySQL 5.7. this is my sql command I am write to update records:
set @update_id := 0;
UPDATE r_room_seat
SET status = 1,
room_play_id = (SELECT @update_id := max(room_play_id)),
user_id = <two different value>,
robot_flag = <two different value>
WHERE  status = 0
and online = 1
and room_play_id = (
    select room_play_id
    from r_room_seat
    where status = 0
    and app_id = 4
    group by room_play_id
    having count(*) = 2
    limit 1
)
LIMIT 2;
select *
from r_room_seat where room_play_id = @update_id;

now I want to update two lines in one sql. The first way I think is select two records and foreach to upate, but it is too low performance. And I have to lock the record before update. The best way is update multi records in one connection query. But the problem is I do not know how to update multi records in this query? Is it possible to update multi lines with different values in one query?


texts: MySQL: Do killed query results get cached?

texts:
If a query is executed from the MySQL command line, then KILLed it while it is running will the query and its partial results* be in the query cache and usable to subsequent runs of the identical query?

For simplicity's sake, let's say this is a plain old SELECT with no subqueries.

The list of exclusions on this page covers cases in which queries will not be cached such as the queried table changing between executions, and certain operations that are by nature not identical, but I do not see interrupted or killed queries listed anywhere. It must not be that a killed query is treated the same as one that ran to completion, but if it met all of the other criteria for caching it would already be cached* before it got killed, so I'm wondering what happens next.

MySQL 5.6

*I'm making several assumptions here.


texts: Oracle Illegal use of long datatype when moving tablespace to bigfile

texts:
I am currently migrating all the tables in my database to a bigfile tablespace. I have written a script to do this and all tables are migrated successfully except one. I am given the error:

Error report:
SQL Error: ORA-00997: illegal use of LONG datatype
00997. 00000 -  "illegal use of LONG datatype"

And I am converting the tablespaces by:

alter table mytable move tablespace bigfile_tablespace;

Changing the column type isn't an option.

I have a script that creates all the tablespace from scratch using bigfile and it goes through fine with this table so I am little stuck on this...


texts:
I'm in the progress of migrating my database from latin1 to utf8 character sets/collations.

I wrote a simple script that loops through all of my tables and executes this statement:

  ALTER TABLE {database}.{table} CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

This works great for almost all of the tables, but I noticed there are a few tables that fail to convert - the ALTER reports successful and there were no errors, but the collation remains as latin1.

Upon further inspection, I noticed that this particular table only has Integer columns, so perhaps this is the reason? But shouldn't that still at least change the table collation in the event I add VARCHARs later?

** edit **

I used phpMyAdmin's UI to convert the table and it gave me a slightly different command:

  ALTER TABLE {table} DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

What is the difference between these to statements; why would one work and not the other?


texts: What would be the best way to model my simple table?

texts:
What would be faster: searching for the right row through a table with 17 different columns, each with a tinyint value of 1-4?

Or, searching for the right row through a table with only 1 column with those 17 numbers concatenated together as a single bigint?

The numbers when combined are all unique from one another (no row has the same values/value as any other). I'm new to indexes and what not, so I have no idea how I should model my table/server logic.


texts: Identity Column Behaviour After Replication

texts:
Say we have two Servers: Server A and Server B. Data is being replicated (vanilla Transactional Replication) from A to B.

For one of the Tables, we have data as follows:

Server A   - Server B
=========    =========

ID|Content   ID|Content
----------   ----------
1 | ...      1 | ...
2 | ...      2 | ...
  ...         ...
98| ...      98| ...
99| ...      99| ...

After a while, we delete old data:

Server A   - Server B
=========    =========

ID|Content   ID|Content
----------   ----------
50| ...      50| ...
51| ...      51| ...
  ...         ...
98| ...      98| ...
99| ...      99| ...

Now, we changed the order of replication, so now Server B is Replicating to server A.

The issue is:

I Would have expected that new data that is added to Server B (and then replicated to Server A) would continue using the ID column (It would go to 100, 101, 102, ...). Instead, I noticed it starts back at 1.

The question is:

How will the DB handle it when the ID finally hits 50? Will the DB just start throwing Primary Key violations, or will it realize what is going on, and jump from ID 49 to ID 100?

If it would start throwing Primary Key violations, is there a command we can use to tell Server B that it should start at ID 100? And if so, can this command be done while it is replicating data?


texts:
I am trying PostgreSQL for the first time, but am finding the fulltext search very slow.

My database has a table with 5 columns and 5 million rows

CREATE TABLE table_test (
    id integer NOT NULL,
    titre character varying(210),
    annee integer,
    pays character varying(50),
    resume_article text
);
INSERT INTO table_test(titre, annee, pays, resume_article) VALUES
('Blanche-Neige et les Sept Nains', '1937', 'USA', 'L''action débute lorsqu''un jour, la Reine interrogeant son miroir, celui-ci lui répond que la plus belle du royaume est dorénavant la princesse Blanche-Neige'),
('Les Petits Mouchoirs', '2010', 'France', 'Max, riche propriétaire d''un hôtel-restaurant, et sa femme Véro invitent chaque année leurs amis dans leur maison au Cap Ferret pour célébrer l’anniversaire d''Antoine et le début des vacances.'),
('La Reine des neiges', '2013', 'USA', 'Le roi et la reine partent immédiatement voir les trolls dont le roi est capable de guérir Anna.'),
('Au service de Sa Majesté', 'UK', '1937', 'Un mauvais garçon new-yorkais, fuit au Royaume unis en empruntant l''identité de sa victime présumée, un Canadien.');

My tests

// 1st test
ALTER TABLE table_test ADD COLUMN fulltext tsvector NOT NULL DEFAULT '';
UPDATE table_test SET fulltext = (to_tsvector('french', COALESCE(titre, '')) || to_tsvector('french', COALESCE(resume_article, '')));
CREATE INDEX IF NOT EXISTS fulltext_all ON table_test USING GIN(fulltext);

SELECT * FROM table_test WHERE fulltext @@ to_tsquery('reine') AND pays='USA'
//Result in 12/15 seconds

// 2nd test
ALTER TABLE table_test ADD COLUMN fulltext tsvector NOT NULL DEFAULT '';
UPDATE table_test SET fulltext= to_tsvector('simple',"titre" || ' ' || "resume_article");
CREATE INDEX IF NOT EXISTS fulltext_all ON table_test USING GIN(fulltext);

SELECT * FROM table_test WHERE fulltext @@ to_tsquery('reine') AND pays='USA'
//Result in 15/20 seconds

SELECT * FROM table_test WHERE (titre ILIKE '%reine%' OR resume_article ILIKE '%reine%') AND pays='USA'
//Result in 5/6 seconds

And if the result is more than 1000 rows, I need to wait 2 or 3 minutes.

    CREATE TABLE table_test (
    id integer NOT NULL,
    titre character varying(210),
    annee integer,
    pays character varying(50),
    resume_article text
    tsv tsvector NOT NULL,
    CONSTRAINT table_test_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
CREATE INDEX idx_pays ON table_test USING btree(pays);
CREATE INDEX idx_tsv ON table_test USING gin(tsv);

EXPLAIN ANALYZE SELECT * FROM table_test WHERE tsv@@ to_tsquery('french', 'maison');

"Bitmap Heap Scan on table_test  (cost=3980.87..363013.72 rows=430047 width=212) (actual time=675.558..37781.252 rows=429066 loops=1)"
"  Recheck Cond: (tsv @@ '''maison'''::tsquery)"
"  Rows Removed by Index Recheck: 2172041"
"  Heap Blocks: exact=41111 lossy=79485"
"  ->  Bitmap Index Scan on idx_tsv  (cost=0.00..3873.36 rows=430047 width=0) (actual time=486.134..486.134 rows=429066 loops=1)"
"        Index Cond: (tsv @@ '''maison'''::tsquery)"
"Planning time: 0.506 ms"
"Execution time: 37848.042 ms"

When I try this query without EXPLAIN ANALYSE, it's 2 minutes to wait. Can anyone explain where my mistakes are?


texts: SQL Server : Transactional Replication Computed Column

texts:
I am a subscriber to a transactional replication subscription. I have no control over the publisher.

I have read the following article about schema changes to the publication database:

Make Schema Changes on Publication Databases

However, I am trying to change the schema on the subscription end (if possible). I am wondering if transactional replication supports the addition of persistent computed columns to the subscriber tables.

I would much prefer this solution versus adding a new view for every table that requires a computed value.


texts: How can I optimize a hierarchical query with multiple paths from child to root Oracle SQL?


texts: Efficiently DROP huge table with cascading replication in PostgreSQL

texts:
What I have:
Database: PostgreSQL 9.3
Table T,

Structure: 10 integers/bools and 1 text field
Size: Table 89 GB / Toast 1046 GB
Usage: about 10 inserts / minute
Other: reltuples 59913608 / relpages 11681783

Running cascading replication: Master -> Slave 1 -> Slave 2

Replication Master -> Slave 1 is quite fast, a good channel.
Replication Slave 1 -> Slave 2 is slow, cross-continent, about 10 Mbit/s.

This is a live, used database with about 1.5TB more data in it.

What's needed to be done:

Drop all data to start with a fresh setup (to do constant cleanups and not allow it to grow this big).

Question:
What would be the most efficient way to achieve this:

without causing huge lags between Master and Slave 1
without causing Slave 2 to get irreversibly lagged to a state where catching up is not possible

As I see it:

Safe way - do a copy, swap places, DELETE data constantly watching lag
Other way - do a copy, swap places, DROP table - but this would cause enormous amounts of data at once and Slave 2 would get lost?


texts: Why is this relation not in Boyce-Codd Normal Form (BCNF)?

texts:
I have the relation (A,B,C,D,E).

The relevant functional dependencies are:

{ E,B,C } → { D }
{ A,B } → { C }
{ E,B,D } → { C }
{ E,A,B } → { D }

The candidate key is (E,A,B).

I do not understand why this relation is not in BCNF, because the candidate key and its attributes are only elements on the left. Thanks a lot for any help!


texts:
I have a table with three columns: id, date, state.

For each unique value of id, I would like to select date and state, multiply the state array by a scalar variable for each id defined from else, and then sum up the state arrays where the date column is the same value.

How would this be achieved in a mysql statement?

Example:

SELECT scal_val FROM table1 WHERE id = 413 into @scal_val;
SELECT date, state * @scal_val as a FROM table2 WHERE id = 413
# now need to loop through ids in table2 for each id, adding state*@scal_val each time

table1:

╭───╥────────────┬─────────────╮
│   ║     id     │  scal_val   │
╞═══╬════════════╪═════════════╡
│ A ║ 413        │ 250         │
│ B ║ 414        │ 50          │
│ C ║ 415        │ 10          │
└───╨────────────┴─────────────┘

table2:

╭───╥────────────┬─────────────┬─────────────╮
│   ║     id     │    date     │    state    │
╞═══╬════════════╪═════════════╪═════════════╡
│ A ║ 413        │ 2016-01-01  │       1     │
│ B ║ 413        │ 2016-01-02  │       0     │
│ C ║ 413        │ 2016-01-03  │       1     │
│ D ║ 414        │ 2016-01-01  │       1     │
│ E ║ 414        │ 2016-01-02  │       1     │
│ F ║ 414        │ 2016-01-03  │       1     │
│ G ║ 415        │ 2016-01-01  │       1     │
│ H ║ 415        │ 2016-01-02  │       0     │
│ I ║ 415        │ 2016-01-03  │       0     │
└───╨────────────┴─────────────┴─────────────┘

result:

╭───╥────────────┬─────────────╮
│   ║   date     │    state    │
╞═══╬════════════╪═════════════╡
│ A ║ 2016-01-01 │ 310         │
│ B ║ 2016-01-01 │ 50          │
│ C ║ 2016-01-01 │ 300         │
└───╨────────────┴─────────────┘

MySQL version 5.6.10


texts:
I've got data that goes sort of like this:

Time    Name    country
12      steve   brazil
12      mary    brazil
12      kate    usa
12      john    usa
13      paul    canada
13      tim     canada

How would i go about writing a pivot query so that it looks like this?:

Time    Name1    Name2   Country
12      steve    mary    brazil
12      kate     john    usa
13      paul     tim     canada

Thanks!


texts: How to switch off statement logging from a trigger in PostgreSQL

texts:
I just spent ages working out how to record messages from triggers into a log file. And then nearly as long working out where the logs files were being created.

Anyway, I added this line to my trigger:

  RAISE LOG 'recentRecordId=<%>', recentRecordId;

And then found a logfile with the corresponding information:

2019-08-30 16:32:49.721 BST [14060] LOG:  recentRecordId=<1782862109>

Great - just what I wanted!

After that was an addition line (not required):

2019-08-30 16:32:49.721 BST [14060] CONTEXT:  PL/pgSQL function parsemess() line 597 at RAISE

Followed by another line (also not required) starting with:

2019-08-30 16:32:49.721 BST [14060] STATEMENT:  RELEASE _EXEC_SVP_000001DCAC8DC240;SAVEPOINT _EXEC_SVP_000001DCAC8DC240;

And continuing with the entire transaction (about 100 INSERTS).

The only data I want or need is on the LOG line, how do I stop logging CONTEXT and STATEMENT? It's generating vast amount of information and will soon clog my drive.


texts: Which option do I have to modify to see more records without press `q`

texts:
I'm connected remotly to my database server with cygwin in full screen.

When I query my database with

   select id, name from table limit 27;

I get 27 records and I can do a new query seeing the result of the previous query.

But, If I replace 27 by 28, I see 28 records in one screen with an (End) in reverse video.
If I want to do a new query, I have to press q.

I can't see the result of the previous query even if I use the scrollbar of my cygwin window.

Which option do I have to modify to increase this buffer ?

My psql client and database server version is 9.1.4 and they are installed on Ubuntu 12.04


texts: commit or rollback on a statement based replication, effect on auto increment id

texts:
How commit or rollback is safe on statement based replication in terms of auto_increment column?

If I execute below queries, shoudn't there be discrepancy between master and slave in auto_increment column in case of statement based replication.

CREATE TABLE t1 ( c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 CHAR(1)) ENGINE = INNODB;

Session 1
begin;
INSERT INTO t1 (c2) VALUES ('a'), ('b'), ('c'), ('d'); should increase c1 to 4 in master while no affect in slave as not yet commited, so not yet written to binlog

Session 2
begin;
INSERT INTO t1 (c2) VALUES ('a'), ('b'), ('c'), ('d');
commit; should have c1 start from 5 in master and from 1 in slave

Session 1
rollback or commit doesn't matter

https://dev.mysql.com/doc/refman/5.7/en/binary-log.html

  Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.
  Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed.

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

  In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost”. Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the “INSERT-like” statement is completed, and whether or not the containing transaction is rolled back.


texts: Postgres : Select items for an interval from the max date in store

texts:
I have a database with items looking like this :

 id , item_id , date_item , price
 0  , 3232    , 2019-04-22, 10
 0  , 3232    , 2019-04-23, 4
 0  , 3232    , 2019-04-24, 1
 0  , 3232    , 2019-04-25, 3
 0  , 3232    , 2019-04-26, 4
 0  , 3232    , 2019-04-29, 5
 0  , 3232    , 2019-04-30, 8
 0  , 3232    , 2019-05-01, 9
 0  , 3232    , 2019-05-02, 54
 0  , 3232    , 2019-05-03, 43
 0  , 3232    , 2019-05-06, 7
 0  , 3232    , 2019-05-07, 56

and more ( many item_id  and date_item )...

I'm trying to select the last 30 days of items in database but can't figure out the exact query.

currently i'm doing this :

select *
from items_prices
where date_item > current_timestamp - interval '30 day'
  and item_id = 3232;

Which output the above result because we are the 05-20 and the last item in store is the 05-07.

What im trying to do is to always select the lastest date item in DB and go 30 days from there.

I've been trying to do it with max() but i can't really figure it out and my query doesn't work:

select *
from items_prices
where date_item > max(date_item) - interval '30 day'
  and item_id = 3232;

or something like this :

select *
from items_prices
where my_max_date=(SELECT id, MAX(date_item) AS "Max Date"
                   FROM items_prices
                   where item_id = 3232) > my_max_date - interval '30 day'
  and item_id = 3232;

i can't figure it out.


texts: Startup procedure for availability replica becoming primary

texts:
Is there any way how to create a startup T-SQL script/procedure for starting availability replica (when state has changed from secondary to primary)?

I want to disable SSIS jobs specific for related availability groups.


texts:
I am trying to model a DW where I have many levels of geography (Neighborhood, District, City, State).

I have several demographic data that should be included in the model. These data include count of people living in that area, average monthly income, average age, and others. The lowest level I have data for is neighborhood, which means it can be grouped together in order to calculate the values for the upper levels.

The Geography is modeled in a single (denormalized) dimension, where each level gets its own column.

Now I have to fit the demographic data in the model. Should I put it in another, separate dimension, or should I put it in the Geography dimension? What about the aggregated levels?

Putting it all on the Geography dimension would leave me with a large number of columns:

Id
Neighborhood Name
District Name
City Name
State Name
Neighborhood Avg Monthly Income
Neighborhood Avg working population age
Neighborhood Number of people
...
District Avg Monthly Income
District Avg working population age
District Number of people
...
City Avg Monthly Income
City Avg working population age
City Number of people
...

Is this correct? This seems rather convoluted. I searched for alternative designs on several books (including Kimball's), but haven't been able to find anything satisfactory.

Are there better, proven, approved and reliable alternatives to this design?


texts: How can I identify all large tables that have no index?

texts: Is there a way I can identify all objects with over 10K rows and no index?


texts: How to select any couple of digits except '00' using mysql regex subset syntax?

texts:
I just found out that mysql doesn't support full regular expressions syntax. I have to select any couple of digits except of 00 (so, 11, 54, 02, are ok, but not 00).

Normally I would use a negative lookahead like this:

(?!00)[0-9]{2}

But in mysql it is not supported. So how could I do it?


texts: What is the difference between Row and Table Expressions in MySQL

texts: Can anybody explain with example what is a Row and Table expression and what is the difference between them. Any example for clarification will be appreciated.


texts:
I've got a script I'm using against a DB2 database, and I'd like it to be able to check that all required values are present prior to trying an INSERT-- so I'd like to select a list of the not null columns from the table I'm about to insert into. Since the table definition may change I'd like to do this rather than using a static list so the script won't break if there's a new not null column added.

Is there a way to get these values from a DB2 query, maybe against syscat?

VERSIONNUMBER VERSIONTIMESTAMP    AUTHID    VERSIONBUILDLEVEL
-------------------------------------------------------
10010400    2016-12-03 16:46:01.509317  ADMIN   s140509


texts: mysql finding field population of multiple columns the efficient way?

texts:
I have a large table in mysql with 150 columns (not my design)

and Im working on finding the population of each column (not empty string)

I have a table

email | fname | lname | ... | ... |
aaa   | bbb   | ccc   | rrr | qqq |
bbb   | eee   | ''    | ddd | bbb |
...   | ...   | ''    | ccc | eee |

desire result (format does not matter actually)

fields | # population     | % population
email  | 1501203          | 100
fname  | 1450000          | 95
lname  | ...              | ....
...

my code so far

SELECT 'email' as col1  ,COUNT(NULLIF( Email, '' )) as '#Populated'
                        , (COUNT(NULLIF( Email, '' ))  * 100/ (Select count(*)
from mytable)) as '% pupolation'
FROM mytable
union
SELECT 'fname'...
*repeat code above 150 times...*

is there a better way to do this?

i dont necessarily need to list the column names in 1 column, it just looks nicer


texts: Windows user automatically gets db_owner?

texts:
All databases from a server have just been migrated to a new server. It looks as if some users were carried across and some weren't, I'm not really sure as I did not know the server very well.

One user who was on the old server has requested a new windows login on the new one. When I go to create this login, and map it to a particular database, the 'db_owner' role is already checked, whereas for other databases it is not? The user has only requested read only.

Obviously I can just uncheck this but how is it that this is the case? Were they an owner of the previous database and someone has cleared out the users and created them anew and SQL remembers this person was a user? Or have all new users automatically been set to have db_owner granted to them? Is there anything I can query to see more information on this?

Thanks


texts: Why does MongoDB extend FileSize if it is already 5x larger than DataSize

texts:
I recently had to assign more diskspace to my MongoDB 2.4.8 instance.
This instance continually receives transactions, makes some updates and then deletes them after 3 months. I would therefore expect that the disk usage was relatively constant.
The documents have a relatively uniform size of 5KB.

db.stats()
{
"db" : "mydb",
"collections" : 16,
"objects" : 4.71578e+006,
"avgObjSize" : 5368.2594088278856000,
"dataSize" : 25315551828.0000000000000000,
"storageSize" : 111230508336.0000000000000000,
"numExtents" : 128,
"indexes" : 41,
"indexSize" : 1398799136.0000000000000000,
"fileSize" : 122280738816.0000000000000000,
"nsSizeMB" : 16,
"dataFileVersion" : {
    "major" : 4,
    "minor" : 5
},
"ok" : 1.0000000000000000
}

I understand that disk usage will be larger than data size due to preallocation and fragmentation, but I cannot see any reasonble explanation for a 5 to 1 ratio other than a large historical delete or a bug.

Is MongoDB unable to reuse space properly so that we must schedule manual repair-jobs on otherwise completely stable systems, or do I have another problem somewhere?


texts: When you use a replica to backup from so you don't have to turn off the master, is this considered High Availablity?

texts:
I read somewhere that MySQL can have a master / slave setup in which the master database can continue to run live and in production while the slave database can be turned off to snatch a backup.  And then when the backup is complete, the slave database can be turned back on, and all the changes made in the master will be replicated over.

Is this considered high availability?  Or does this also require that if the master fails, the slave take over? Or does that require something like vMotion from VMware?


texts: Is it possible to change the family_guid in MSSQL?

texts:
So I have a bit of a problem with some databases I'm trying to migrate to amazon RDS

We have about 20 Databases that are all derivatives of others that all share the same family_guid because they are based on backups of others. The problem is that amazon will not allow you to restore a DB that has the same family_guid of another even if they are two separate databases. This is where my question comes in, Is it possible to change the guid or am I out of luck and will need to do some exports/imports on these databases.


texts: Loop inside stored procedure doesn't stop after query cancellation

texts: I have a few nested stored procedures, which have a while loop at the end. When I stop the query, the loop keeps running, though. Every child sp is independant from another and has a duration of less than 1s. Any ideas why it keeps running or how to properly cancel it?


texts:
I'm trying to optimize a query like:

UPDATE master SET count =
   (SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id)

Where the master table is large, so running this results in a huge sequential scan. On the other hand, this count doesn't change so often, so my idea is to rewrite it like this:

UPDATE master SET count =
   (SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id)
   WHERE count !=
     (SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id)

EXPLAIN tells me this results in two subplans, which I'd expect. Is there a way to avoid the two executed subplans?

An attempt to rewrite the above as

UPDATE master SET count =
   (SELECT COUNT(*) FROM detail WHERE detail.master_id=master.id) AS cnt
   WHERE count != cnt

fails with syntax error at "AS".


texts: How to get row_count diagnostic after EXPLAIN ANALYZE in Postgres?

texts:
I normally retrieve the row_count of a query by doing GET DIAGNOSTICS row_count (normally within a function).

But if I want to run EXPLAIN ANALYZE on a query AND get row_count, I can't see any easy way to do that other than parsing the string output, because then GET DIAGNOSTICS row_count would return the number of rows in the EXPLAIN output.

Is there any built in way to do this other than parsing the string?


texts: Multidimensional databases on SQL Server 2008 R2?

texts:
I am developing a business intelligence-product using ASP and SQL Server 2008 R2.

Each time I get a new client I'm copying a current client's database without data. This is OK for now, but can be annoying on the long term.

My new problem is: When doing further development on the product with changes on the database setup (column names and such), then these changes needs to be done on every client-database.

Is it possible to do any kind of multidimensional data warehousing - Having separate data-entries on the "same" table?

I hope you know what I mean :)

FYI: Each client have its own user account mapped to their own database to increase the security


texts: How to keep MySQL general_log after I restart my computer?

texts:
I use a statement set global general_log = 'ON' to enable the general_log on my computer.

After I restart or shutdown it, I have to run the statement again.

How can I config MySQL write general log without to run the statement again and again?


texts:
I have the task to create an EAV table with some constraints.

Attributes are foreign keys and some values are also from other tables (not realized as foreign key, but as a CHECK constraint).

On this table are some constraints to be realized, one of which is that some object/attribute-combinations must be unique, while others don't.

Example: A photo can have multiple persons in it but only have one person who took it.

So I wrote a function to limit object/value-combination to max. 1, if a certain boolean is set for that attribute. (As I said, the attributes come as foreign keys from a different table.)

-- Create function (for use in CHECK constraint) that checks if
-- an objects get two value on the same attribute,
-- unless it's explicitly allowed in attributes table
CREATE OR REPLACE FUNCTION isNoDisallowedDoublette(_object text, _attribute text)
RETURNS boolean AS
$BODY$
  DECLARE
    singular    boolean;
    cnt         integer;
    stmt        text;
  BEGIN

    -- determine if attribute needs to be unique for object
    EXECUTE 'SELECT singular FROM attributes'
      ||  'WHERE attribute = '''
      ||  _attribute
      ||  ''';'
    INTO singular;

    -- if there are multiple values allowed, continue
    -- Note: if attributes.singular is not set,
    -- allow only one value to avoid conflicts
    IF singular IS FALSE THEN
      RETURN true;
    END IF;

    -- count entries identical to _row
    EXECUTE 'SELECT count(*) FROM objects '
        ||  'WHERE object = '''
        ||  _object
        ||  ''' AND attribute = '''
        ||  _attribute
        || ''';'
    INTO cnt;

    RETURN cnt = 0;
  END
$BODY$
LANGUAGE plpgsql;

This functions checks, if a particular object/attribute-combination is not yet present in the table (cnt = 0).

This works fine against inserting unwanted doublets.

The problem however is, that it doesn't allow updating such row.
The updated row seems to be considered a doublet.

Is there a way to fix this or is it a conceptual problem CHECK constraints just don't cover?

Robin


texts: How to connect via config file in mongodb

texts:
In config file I have:

   systemLog:
        destination: file
        logAppend: true
        path: c:\data\log\mongod.log
    storage:
        dbPath: c:\data\db
        journal:
            enabled: true
    replication:
       replSetName: "rs0"
    net:
       bindIp: 127.0.0.1
       port: 27017
    security:
         authorization: enabled

I'm trying to connect using mongod process like in documentation:

C:\Program Files\MongoDB\Server\3.6\bin> mongod --auth --dbpath /data/db --config C:\Program Files\MongoDB\Server\3.6\mongod.cfg

And getting:

  Error reading config file: No such file or directory
  try 'C:\Program Files\MongoDB\Server\3.6\bin\mongod.exe --help' for more information

Then trying with quotes

C:\Program Files\MongoDB\Server\3.6\bin> mongod --auth --dbpath /data/db --config "C:\Program Files\MongoDB\Server\3.6\mongod.cfg"

and getting:

  2018-07-02T02:49:21.272+0300 I CONTROL  [main] log file "c:\data\log\mongod.log" exists; moved to "c:\data\log\mongod.log.2018-07-01T23-49-21".

Starting mongo, then show dbs and see:

If I'm writting this snippet (without config), everything is fine:

mongod --auth --dbpath /data/db --bind_ip 127.0.0.1

With mongo:

What I'm doing wrong? I appreciate any help.


texts:
I just read the Brent Ozar Weekly Links email (July 28th Edition), and noticed the "Kendra's Link" talking about "Fix: Incorrect result when you execute a query that uses WITH RECOMPILE option in SQL Server 2012"

I replied to Brent Ozar's email, and Brent suggested that I post my question here instead of asking him to reply to me privately.

I had just finished patching all of our SQL Server 2012 servers in July to apply SQL Server 2012 SP2 plus the hotfix for the "data loss during online index rebuilds", so my servers are now at version 11.00.5522.0.

I did a little bit of research on this new hotfix.  I came to the conclusion that there is not a way to patch our systems with this new fix yet.

Here is the email I just sent to my manager. Do you guys agree with what I've pasted below:

Now that we’ve upgraded to SQL Server 2012 SP2 already, we don’t have a way to apply this new hotfix fix as of right now.

The fix was released in SQL Server 2012 SP1 CU11 (version 11.00.3449.0). As of this month, we are now at SQL Server 2012 SP2 plus the hotfix for the data loss during online index rebuild problem (version 11.00.5522.0).

You’d think that since we are at a version higher than the fix, we’d be covered. You know how they say that all the service packs include the previous service pack plus all the cumulative updates for the previous service packs, and all versions include everything from the versions below that version. Well, that’s not entirely true. Microsoft continues releasing CU’s for the previous service pack even after the new service pack is locked in QA. So they then have to play “catch-up” with the new service pack.

In reality, this is how it worked this time for the transition from SP1 to SP2:

• SQL Server 2012 SP2 (what I just patched all our SQL Server 2012 servers to) – includes SP1 plus CU1-CU9. It does not include SP1 CU10 or SP1 CU11.

• SQL Server 2012 SP2 CU1 was just released (last week I think) – that includes SP1 plus CU1-CU10. It does not include SP1 CU11 .

• That’s as far as MS has gotten with SP2. They haven’t released a CU for SP2 that includes the fixes from SP1 CU11 yet.

I assume there will be an SP2 CU2 coming out soon that will contain this fix, and we’ll probably want to talk about doing another round of SQL patching when that does get released.


texts:
I am trying to update columns based on the value of the inserted column of the table 'my_table'. I want to be able to record the timestamp of the values changed.
I am using the triggers but I can't find out how to choose the column dynamically

CREATE TRIGGER `record_time` AFTER UPDATE ON `my_table`
 FOR EACH ROW INSERT INTO `record_changes` (dynamic_column) values (CUR_DATE())

here you see that I want to choose the column dynamically(dynamic_column) based on the value of the column in the table 'my_table'.

table structure for 'record_changes' is something like this,
a,b,c,d.... upto 9 columns. The value being updated in 'my_table' is one of these 9 values.
So I want to select the column to update based on the value of the 'my_table'.
Can someone help?


texts:
I have received a database file and the instructions for loading it is to install SQL Server 2005 and then attach it using SQL Server Management Studio.

After installing everything, I tried to attach the MDF file but then it tells me:

  the directory lookup for the file "D:{folderName}{filename}.LDF" failed with the operating system error 21 (error not found)

An LDF file did not come with the database, so presumably it should be generated automatically.

Now, D drive is where my CD drive is, so it's not going to find anything there. Nor is it going to have any luck trying to create anything there.

Why is it trying to look for a log file at a specific path? Why not where the database file is?
How can I attach this database?

I realized that when I select a database to attach, three entries appear under "database details". an MDF, NDF, and LDF. The LDF's "current file path" points to the D drive path above, so I removed it.

This time, when I hit "OK", I get a different error message:

  Database cannot be upgraded because it is read-only or has read-only files.
  Make the database or files writeable, and rerun recovery.

  File activation Failure. The physical name D:{folder}{file}.LDF may be incorrect.
  New log file "..." was created. (Microsoft SQL Server, Error: 3415)

So now it creates a new log file in the same folder as the database file, which is great, but it seems like there are security issues.

Additional information:

The instructions require me to use the login name "sa", which appears to be the sysadmin account. I am connected to my SQL Server instance using that login.
I have checked the file properties that it is not read-only. The directory is not read-only either. All ACLs are allowed.
I am unable to attach the database. When I try to attach it, it throws an error message with "attach database failed".
Closing SSMS and reopening it as an Administrator made no difference.
select SERVERPROPERTY('ProductVersion') returns 9.00.4035.00. There's another file that comes with the database called "dbdata.ini" which says "IsSql2000=1" so presumably it is meant to be loaded in SQL Server 2000. I'll see if I can get it working on 2000.


texts:
I have 4 servers running MySQL 5.6. A,B,C and D. A and B are Master and slave whose default port is 3306. C and D are running in non-default ports 3360.

A and C are in Master-Master replication mode. C and D are in Master-Slave setup.

Now I need to change the ports on A and B from 3306 to 3360, what commands I need to run and in what order to have B,C and D back in replication?

Is the below steps right?

Stop the slaves in B, C and D.
Stop A and change the port in A to 3360 and start A.
Stop B and change the port in B to 3360. Ensure skip slave start is set to true.
Start B and run change master to master_port=3360 and start slave.
In C, run the same as step 4.
Start slave in D.

And also, while I type change master to master_port, should I write the full commands with master_log_pos, username, pass and then port? Or just the port is enough to restart the replciation?


texts: Will reindexing use the empty space or just grow table?

texts: I have a table which is 170 GB and it has 98 GB of free/empty space. The free space was created by a massive deletion. If I run DBCC REINDEX or ALTER INDEX REBUILD, will the index use that free space first or will the free space be ignored and table will just grow?


texts: Null column for username and password when using Login with *social network*

texts:
I want to know what are the conventions for columns like username and password when your application supports login with a third party, like login with facebook for instance.

Given the fact that when you Log with a 3rd party, you don't really need to specify a username, you'll just specify the 3rd party user id, in database design, you can expect either a blank field or a null value.

Since usernames are UNIQUE, I'm guessing going for nullable is the way to go, since it allows multiple null columns.

The real issue is that the user needs to specify either a username OR a 3rd party id. Which means both columns can be null, but not at the same time

So what's the convention for handling this design pattern?


texts: MySQL unknown column 'password_last_changed'

texts:
When creating a user using this command

create user 'foo'@localhost';

this error is shown

ERROR 1054 (42S22): Unknown column 'password_last_changed' in 'mysql.user'

Using MySQL server version: 5.7.6 on linux

I tried to add it but I dont know its data-type
How can I fix this


texts: Compare sysdate month with a column data

texts:
im application developer and we are using Oracle database for this project. I never used oracle before so i want some help to build a simple query. I have a CARD table with these columns EXPIREMONTH and EXPIREDAY. I would like to return the CARDS that will expire in the next month from the SYSDATE

  select * from CARDS where EXPIREMONTH == Sysdate('MM' + 1) // something like this

Thanks.


texts: Can I restore a .bmp file of full database backup to an Oracle 12c EE instance running on RDS?

texts:
I've got an Oracle 12c EE database set up on an RDS box with no SSH access. I do, however, have SQL Developer and SQL PLUS tools.

Is there any way I can restore a full backup into this database using these tools (or any other remote management utility)?


texts:
Looking for advice on how to match two differing column types:

I have two tables: RT-A and DA-B.

Under both tables there is a column named DeliveryDate, but the individual tables use differing column types; one time without timezone and the other numeric.

Example: in the DA-A table the DeliveryDate column reads: "02:00:00"
Example: in the RT-A table the DeliveryDate column reads, "2".

What is the correct expression in the WHERE clause of an SQL statement to join rows where they equal each other?
Plain equality obviously does not work With the current design:

"DA-A"."DeliveryHour" = "RT-A"."DeliveryHour"

This is how I have been writing the SQL, but it seems to be terrible in form:

WHERE
  "DA-A"."DeliveryHour" = '2:00:00' AND
  "RT-A"."DeliveryHour" = '2'


texts: Different ways to log the DML operations being performed, along with the hostname (or some type of unique ID) performing the said operations?

texts:
What are the different ways to log the different DML operations that are being performed, along with the host performing them.
I do not wish to use triggers as they are costly.
I considered SQL Audit for a while, but they only furnish you with the SPID, that may be useful, but for a limited window.
So SQL Audit feature won't bear any fruits in resolving this either.
What are the other options available at one's disposal?


texts:
I created a replication set.
I added localhost in the set in the beginning, but when I try to edit the member with the actual hostname. I get error "exception: hosts cannot switch between localhost and hostname"

I need to get rid of localhost:27017 because, otherwise, it doesn't let me enter any other member as hostname (i.e. non-localhost address)

my-rs0:PRIMARY> cfg=rs.conf();
{
    "_id" : "my-rs0",
    "version" : 1,
    "members" : [
        {
            "_id" : 0,
            "host" : "localhost:27017"
        }
    ]
}
my-rs0:PRIMARY> cfg.members[0].host="my-server04:27017"

my-rs0:PRIMARY> cfg
{
    "_id" : "my-rs0",
    "version" : 1,
    "members" : [
        {
            "_id" : 0,
            "host" : "my-server04:27017"
        }
    ]
}

using rs.reconfig(cfg);

my-rs0:PRIMARY> rs.reconfig(cfg);
        {
            "errmsg" : "exception: hosts cannot switch between localhost and hostname",
            "code" : 13645,
            "ok" : 0
        }

no luck with rs.add("my-server04:27017") or rs.remove("localhost:27017") as well.

 my-rs0:PRIMARY> rs.add("my-server04:27017");
    {
        "errmsg" : "exception: can't use localhost in repl set member names except when using it for all members",
        "code" : 13393,
        "ok" : 0
    }

I have tried all the reconfiguration methods mentioned here Replica Set Reconfig steps
But, none fixing above issue. Already spent hours, I am really frustrated.


texts: Grouping records with with consecutive start and end dates -Postgresql

texts:
We are migrating to a new record management system and I'm weeding through the issues with the data migration. Source data and destination data are all in Postgresql

In the source database we have multiple rows for the same vendor for each authorization code for that vendor and the start and end date of that authorization.

+----------+---------------+------------+-----------+
|  Vendor  | Authorization | Start Date | End Date  |
+----------+---------------+------------+-----------+
| Vendor 1 | #####         | 1/1/2017   | 2/28/2017 |
| Vendor 1 | #####         | 3/1/2017   | 4/15/2017 |
| Vendor 2 | #####         | 4/16/2017  | 5/31/2017 |
| Vendor 2 | #####         | 6/1/2017   | 7/12/2017 |
| Vendor 1 | #####         | 7/13/2017  | 9/30/2017 |
+----------+---------------+------------+-----------+

In the destination database we have records for the active vendor relationship at a given time with a start date and an end date. (Auth # start and end dates are tracked separately) After migration the example listed previously should look like this.

+----------+------------+-----------+
|  Vendor  | Start Date | End Date  |
+----------+------------+-----------+
| Vendor 1 | 1/1/2017   | 4/15/2017 |
| Vendor 2 | 4/16/2017  | 7/12/2017 |
| Vendor 1 | 7/13/2017  | 9/30/2017 |
+----------+------------+-----------+

I think I should be able to accomplish this joining the table back on it self n-1 number of times where n is the largest number of consecutive authorization numbers for the same vendor which is probably around 10, which is doable but definitely not preferable.

Is there a more common method to group data by consecutive start and end dates?


texts: mysql 8 information_schema.processlist -> correct approach to cache it?

texts:
I have a high concurrency mysql 8.0 server.
Thousands of tasks happen per second (up to a million rows per second read in average)

In some of the tasks that can be very performance intense I have an emergency switch to prevent the database queue from overflowing.

I read the number of processes (sometimes specific ones) from information_schema.processlist and if they exceed a certain value the task is skipped until the database is back in time.

So much so good, that worked perfectly fine in mysql 5.7 and before, but in mysql 8.0 they removed the query cache which might be the reason why it can not deliver such performance anymore.
In my case the server quickly is overflown with these SELECTS.

So I decided to run a background task that refreshes a table every second with a dump of the processlist.
I am using Innodb as MEMORY has more blocking issues.

This is the syntax I run every second:
processlist is "LIKE information_schema.processlist but I've changed the Info blob to a varchar"

BEGIN;
truncate table processlist;
replace into processlist SELECT ID,USER,HOST,DB,COMMAND,TIME,STATE,LEFT(INFO,1024) AS INFO FROM information_schema.processlist WHERE Command != 'Sleep';
COMMIT;

I am using a transaction to prevent race conditions and just truncate + replace into.

However I am not sure if this is the best solution to populate a "mirror" table.

So my question is: is this a good approach (especially not sure what truncate might  mean on performance) ? Any ideas how to do this better ?


texts: Oracle 12cR2 - Upgrade Catalog Command Taking Too Much Time

texts:
I patched my database (Windows database bundle patch 12.2.0.1.201020 (31654782)).
There are 6 instances on the server. After the patch, the UPGRADE CATALOG command I ran on RMAN for the first 5 instances was compiled in 2-3 minutes each. But it has been going on for 1 hour for the last intance. Is there a way to speed it up or is it a bug?
Best regards,


texts: Is there any way to replace sql server file_guid with new GUID?

texts:
I have two databases with same file_guid. Getting following error when trying to restore second database in Amazon RDS.

  Aborted the task because of a task failure or a concurrent RESTORE_DB
  request. Task ID 8 (RESTORE_DB) exception: Database DATABASENAME
  cannot be restored because there is already an existing database with
  the same file_guids on the instance.

This is because I already restored another database with same file_guid but different physical_name), which is an Amazon RDS limitation. If interested find details in here and here.

How can I update sys.database_files record?


texts: Oracle SQL Statement alter index and add partition

texts:
I have an index in which I have to remove one column and reindex back for rebuild:

ALTER INDEX <index_name> REBUILD;

Is it possible to add partition when I rebuild an index.  Partition will be based on one of the column index which is a datetime field. Something like:

ALTER INDEX <index_name> REBUILD, PARTITION BY RANGE(COLLECTIONTIME) INTERVAL     (INTERVAL '15' MINUTE)
(PARTITION INITIAL_PARTITION VALUES LESS THAN (DATE '2014-10-10') );

Not sure how to write the sql statement for it. Anyone can help?

Also, if it is possible will the existing records will also be partitioned?


texts: Identify related fields inside one column based on position pattern

texts:
I have a table (imported from excel) similar to this

In order to do subsequent manipulation of the data I would like to add a prefix like this

Can it be done via some SQL trick, or do I have to use a CURSOR?

Here is SQL for potential generation of a table http://pastebin.com/CHjhHfXL


texts: Accessing a SQL Server Denali CTP1 database with Management Studio 2012 SP1 Express

texts:
I had Management Studio 2011 Denali CTP1 installed before but I have uninstalled it.

Now I have installed the Management Studio 2012 SP1 Express without any database instances.

I have a webserver with a few SQL server instances, one of them was installed by the Denali CTP1 installer. I'm able to connect to it wothout problems with a Denali CTP 1 SSMS but not with the 2012SP1.

When I connect with SSMS2012SP1 I immediately get the error:

  Invalid object name 'sys.database_filestream_options'. (Microsoft SQL Server, Error: 208)

Although I'm connected to the database (and able to create or modify users for example), nearly every item throws some error when I expand it.

For example expanding "Databases" throws:

  Invalid column name 'synchronization_state'. (Microsoft SQL Server, Error: 207)

"Some authorization problem" - I thought. But after creating a new user and setting every possible right to it and trying to logging in with it, there is no difference.

I'm not able to figure out the problem.

I can access the database through code just fine, here is my connectionstring:

server\web11;Initial Catalog=a_database;Persist Security Info=True;Trusted_Connection=True;

So I figured that this is something Management Studio does.


texts: New boolean column in big table OR - bad idea ? - new related 0-1 table

texts:
I have a big table (200 billion rows) like this :

CREATE TABLE A (
A_Id INT NOT NULL PRIMARY KEY,
A_Attribute1 VARCHAR(100) NOT NULL,
... )

I must add new boolean column NOT NULL with false as a default value, like this :

ALTER TABLE A ADD A_BitAttribute BIT NOT NULL DEFAULT 0

Moreover, in the future, this column will be 0 most of time.

To prevent the cost of adding this column in the A table, I thinked about creating a new related table, like this :

CREATE TABLE ABitAttribute (
A_Id INT NOT NULL PRIMARY KEY,
, CONSTRAINT FK_ABitAttribute_A FOREIGN KEY (A_Id) REFERENCES A (A_Id) ON DELETE CASCADE
)

A row in this table indicates True for A_Id, no line indicates False. The outer join needed to retrieve the data doesn't bother me too much.

Are there any major drawback or theory violation in using this kind of technique ?

edit :

it has to be SQL Server 2005 compatible
the interruption of service has to be minimal (we usually garanty that database has no more than 1 hour)


texts: Two tables, same Attributes unable to count,

texts:
I am working on Two table together for count how many SKU.

As I'm able to sum them up in how much amount with no problem. but I need a count on how many QTY in SKU.

I am able to get a count in SKU in one table only but unable to do two table. I have tried UNION SELECT but nothing working

SELECT
     transaction_type, sku, count(quantity_purchased) as SKU_ORDER

FROM
    settlements_qty_test
WHERE
        transaction_type = 'order'
        AND amount_description = 'Principal'
        AND amount_type = 'ItemPrice'

UNION SELECT
     transaction_type, sku, count(quantity_purchased) as SKU_ORDER

FROM
    settlements_qty_test1
WHERE
        transaction_type = 'order'
        AND amount_description = 'Principal'
        AND amount_type = 'ItemPrice'

GROUP BY sku
ORDER BY sku

I have used this similar method for how much, and seems it working fine but not this one. Seems I am missing something?

I am still rookie MySQL.


texts: select rows where column contains same data in more than one record

texts:
I have a table that has a column called article_title. Let's say the table name is articles. I need to find out the records where the article_title data is the same on more than one record.

Here's what I've got:

select a.*
from articles a
where a.article_title = (select article_title
                         from articles
                         where article_title = a.article_title
                         AND a.id <> articles.id)


texts: SSRS minus first two dates as the header

texts:
I have a SSRS report that has one column called startime that is listed in mm:dd:yyyy hh:mm:ss format.  For the header of this column I want to minus the first date from the second date to give me an idea of how long is between the two dates, this will likely be in minutes.

I don't need a running total, I just need to be able to minus the first date from the second date in the column will be good enough.  I have bee playing around with the following but its throwing up some strange results.

=Fields!StartTime.Value - Previous(Fields!StartTime.Value)

Does anyone have any ideas?

Thanks


texts:
I want to migrate my mysql databse to oracle.
I have followed the migration demo from this url http://www.oracle.com/technetwork/database/migration/connect-step-mysql-1946352.html

but I got this message when try to associate migration repository

sql error on script execution. try deleting repository before creating repository

I am using
Oracle Sql Developer v 4.1.1.19Oracle express edition 11g releas 2

see the picture bellow what I have tried step by step

step1: Creating a user named migration_repo

step 2: Creating connection named migration_repo using migration_repo user

step3: try to associate migration repository

step4: executing the command

but after few seconds I got this error message.

Now. how can I solve this? what I have done wrong?


texts:
I need to use a third party DLL in a trigger c# code in SQL Server CLR

But when I try to add reference it just shows some DLLs from SQL Server.

How can I add my third party dll to SQL Server?


texts: Postgres Error: could not read block 167593 in file... SQL state: XX001

texts:
When creating a new column on a table, I'm getting the following error:

ERROR: could not read block 167593 in file "pg_tblspc/16384/PG_9.4_201409291/16386/157223695.1": read only 0 of 8192 bytes
SQL state: XX001

Obviously not good, as SQL state XX001 means corrupt data. I'm not too bothered as this is not critical data and can relatively easily be re-loaded from the source.

However, after searching for similar issues, all I can see from other people is this error for 'of relation base', not 'in file'. How is 'in file' different, and what does this mean? Is it more likely that I have a hardware issue?

Also, how can I check to see for certain that the data is corrupted?


texts: How can I disable utf8mb4 entirely on MySQL 5.5?

texts:
I know you're not supposed to do this, but I have a MySQL 5.5 master and a MySQL 5.1 replication slave. That's supposed to be okay, unless I do something stupid like use utf8mb4 that isn't supported by 5.1. Well, everything has been going well for a while and today I got a replication slave failure because character_set_client #45 isn't supported by MySQL 5.1.

I spent a lot of time trying to find out what #45 was... you see, it doesn't appear in any of MySQL's configuration files -- even on 5.5. I finally determined it was utf8mb4 (which I had suspected) but I'm not sure why it's being used. I performed a static query on the master ... something simple like UPDATE table SET field='value' and it replicated to the slave by setting the character set to utf8mb4. I executed hundreds of similar UPDATE statements at the same time, but some of them triggered the use of utf8mb4.

So... I'm going to be upgrading my slave to 5.5, but in the mean time is there a way to tell MySQL to never use utf8mb4?


texts:
Before I tried to avoid using cursor because of performance issues. But now I have to do some calculations to reach some goals. I have a stored procedure that has a code like this:

DECLARE  Outer_Cursor CURSOR FOR...

   OPEN Outer_Cursor
   FETCH NEXT FROM Outer_Cursor INTO ...

   WHILE @@FETCH_STATUS = 0
   BEGIN

      DECLARE  Inner_Cursor CURSOR  FOR...

      OPEN Outer_Cursor
      FETCH NEXT FROM Inner_Cursor INTO ...

      WHILE @@FETCH_STATUS = 0
      BEGIN

        ...

        FETCH NEXT FROM Inner_Cursor INTO ...
      END

      CLOSE Inner_Cursor
      DEALLOCATE Inner_Cursor

      FETCH NEXT FROM Outer_Cursor INTO ...

  END

  CLOSE Outer_Cursor
  DEALLOCATE Outer_Cursor

My question is: is it possible to avoid (minimize) so expensive cursor recreation for inner cursor. How can I reuse inner cursor declaration, is it possible?

Thank you.


texts:
I am moving my SQL server backups who run through the ola hallengren scripts to a new share.

On this share I granted full control to the AD-group containing the SQL Service accounts & the account which will execute the CMDEXEC through a proxy.

so far so good, this works for 4 out of 7 servers.
For 3 other servers however this is not the case and I get the error that the directory does not exist.

For the 4 servers where I do not have any issues, I did not issue a service restart or anything else. I only created the credential and proxy and changed the path for the backup share. (the same as on all servers).

The servers are SQL Server 2012,2014 & 2016 and they all have the problem (or don't have)

Does anybody have any idea what might be going on? Something that I am missing?


texts: AlwaysOn AG and Cross-Database/Server Transactions

texts:
We're about to move from SQL Server 2008 R2 Standard to SQL Server 2012 Enterprise and are reviewing or HA as part of process. Right now we use DPM and log shipping every 15 mins. Our MS partner is recommending AlwaysOn AG. I've heard of a lot of issues with AG, but the one that gives me the most concern is this one:

According to Microsoft, "Cross-database transactions and distributed transactions are not supported by AlwaysOn Availability Groups or by database mirroring". (http://msdn.microsoft.com/en-us/library/ms366279(v=sql.110).aspx)

We can't move to SQL Server 2014 (and it doesn't seem any better there anyway).

We have a 3rd party vendor app that uses 7 databases (bad design, but nothing we can do about it); many integrations that use multiple databases; and some that use multi-server transactions.

Is there a way to live with this limitation?

What do you guys do who use AG and have cross-database and cross-server transactions?

Regards

Mark


texts: Connect two remote oracle databases as a schemas on each other

texts:
This Question is about Oracle Database.
I have two remote databases DB_1 and DB_2, and I have two schemas on each database:
Schema DB_1 and Schema DB_2 on database DB_1, and Schema DB_1 and Schema DB_2 on database DB_2.
I want to reach database DB_2 Schema DB_2 from database DB_1 Schema DB_1.
For example: when I try this query  in DB_1 from  schema DB_1:
SELECT * FROM DB_2.example_table
I got an error because there is not table "example_table" in DB_2 Schema on the DB_1 database.
But, when I try the same query on DB_2 Schema DB_2, I got the correct result (some data).
So, I need to tune my databases in some way so I can SELECT * FROM DB_2.example_TABLE from DB_1 Database.
But, there is another problem, I don't have permission to CREATE LINK.
However, when I query SELECT * FROM ALL_LINKS; on my production database DB_1 schema DB_1 - there are no links at all. So, I need to figure out which way my production Databases was linked (unfortunately my colleagues don't know either). On my production database DB_1 schema DB_1 I can fetch data with the query:
SELECT * FROM DB_2.example_table
And the data I got is the same if I try to query the same on DB_2 production database DB_2 schema.
But there are no public\private Links on DB1/DB2 production databases at all. Is there another way to create a "connect" between my databases? (I queried SELECT * FROM ALL_LINKS from DB_1 schema DB_1, is this possible so I can fetch some other data if I try the query on DB_1 schema DB_2?)
Thanks in anticipation!


texts: MySQL connection process slow for non-root user

texts:
I'm using Percona server 5.6 on Linux.

When I connect to MySQL from the command line as the root user, it connects immediately.

I have also created a new user in the database, but when I try to connect using the newly created user, it pauses for 30 seconds and then it connects.

What could be the problem?

FYI: This question posted by me is also linked to this question if that could help is resolving this question

Another question which might help in solving this question


texts: Postgres upsert perform multiple actions _OR_ versioning of events in an "append only" store

texts:
Background

I am running on postgres 9.5

I have a table with events (in theory, it is, currently, an append only store).

Certain event types can appear only once on a given date. (So currently, we have a partial unique index and an upsert to update the "value" column when a conflict occurs <- so this is why it is not a fully append only store)

The goal: to be able to version the events mostly as an audit of past events - mostly in the sense that these events represent a misinterpretation of the facts and have thus been superseded by corrected events

I have, presently, come up with the notion by which I add a Nullable column with a timestamp to each event (I call it substituted_at). An event that has been superseded will have the timestamp recorded while the newest events will have that column value remain NULL. This should, hopefully, make retrieval of the events easy, for processing, I simply retrieve all events whose substituted_at value is NULL.

Another solution would have been to have moved the superseded events to an auxiliary table, but that didn't strike me as the way to go (comments?)

(Also, I am open to other propositions)

Sub goals:

version events whose type can only appear once per date
version events whose type can appear multiple times on a given date
it should be possible to version an event without supplying a replacement event (in effect removing it from the event store for processing)

Question

I would like to center on sub-goal #1.

It would seem that in case of conflict I need to update the substituted_at column with a timestamp and keep all the other values as they are. I would also need to insert a new row with the new data.

It does not seem possible with the ON CONFLICT Clause.

I would obviously like to do this transactionally.

Also the solution should keep in mind that sub-goal #2 cannot rely on an ON CONFLICT clause - since it is alright to have more than one event per type per date.

Also, sub-goal #3 means that there would be times where no replacement would come at all.

As I write these lines, it seems to me that centering on UPSERT to do the heavy lifting is not the robust way I should be heading... Also, in the face of versioning, does the partial unique index make sense anymore?


texts: SQL Server 2008 R2 - How to export/migrate only tables and their PKs

texts:
is there a way how I can only migrate/export table names with the PKs but no other relationships like FKs? So there is no dependency between the tables. And then import them to another database?

I would like to perform this between SQL Server 2008 R2 and Azure SQL.


texts: Distributed Availability Group Failover Issue

texts:
I have created Distributed Availability group set up as below:

In Data center A, created 2 node AG1 setup (Synchronous) in Datacenter B, created 2 node AG2 setup (asynchronous).

Created Distributed AG setup (synchronous) on top of these two AGs and it is working fine.

When i am executing Failover command (on Primary of DC A) to failover manually it is not failing over properly, it changes the role of AG1 as secondary but shows disconnected from AG2.

I executed
ALTER Availability group DistAG FAIlOVER_ALLOW_DATA_LOSS command on Primary of AG1.

please help to clarify if any config problem is there or i need to follow certain steps to fail-over.


texts: What is the difference between normal Select query and a Select query inside Common Table Expression(CTE)?

texts:
I'm having a table 'Products' and if Execute this query

SELECT TOP 1000 [ProductID]
      ,[ProductDesc]
      ,[ManufacturingDate]
      ,[ExpiryDate]
      ,[Price]
  FROM [TestSat].[dbo].[Products]

I got the output:

and If I use this select query:

SELECT ProductDesc,Price
  FROM [Products]
  WHERE Price>20.00

I got this output:

If I use CTE-Select like this:

 ;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
  FROM [Products]
  WHERE Price>20.00
)

I got this output:

The only difference I found between this simple Select Query as well as CTE Select query is the Column name (ProductDesc as ProdName).
Does it is the only difference between normal Select query as well as CTE Select query?


texts: How to get information about query plans that are out of cache?

texts: In SQL Server there are a lot of DMV/Fs and other options which allow me to get information about query plans that are in cache, but I can't find any method to get information about all query plans, not just ones saved in cache. Can you, please, show how can I explore all query plans such as a number of times it has been used, its size, etc.?


texts:
Ran into the following error...

K:\Microsoft SQL
Server\Data\MyAudit3_Dat.NDF_MSSQL_DBCC50: Operating system error
112(There is not enough space on the disk.) encountered.

In this case the disk space was very low which needed remediation... However, it lead me to ponder
what are the disk space requirements for dbcc checkdb?


texts: Can a SQL Server 2005 msdb database be restored as a User database?

texts:
Can a SQL Server 2005 msdb database be restored as a User database on another instance (same Build)?

"Why?" you ask: I need a list of the mail items that existed prior to installation of two service packs .. the list was deleted (msdb.dbo.sysmail_delete_mailitems_sp) in order to address a post-install storm of mail messages.


texts: SQL Licensing on Azure, Standard D4 Windows 2012 Server 8 core, is it 1 phsysical core?

texts:
I am trying to calculate my costs for Microsoft Azure. Imagine I take a Standart D4 Type machine running Windows 2012 and would like to install my own SQL Server 2012.
Are these 8 cores 1 quad core with hyperthreading enabled, or are these 2 Quad cores without hyperthreading ? I mean do I have to pay only 1 or 2 core license for SQL 2012 Standart  Edition?

I could't fint the answer online. Neither I have the chance to create the machine now and check the socket number.
Can anyone help me out?


texts: Reviewing the design of a database about animals of two distinct species

texts:
I am creating a small database using SQL Server Express 2017.

There are two animals (Foxes and Elephants).
The Foxes table will have the columns: Identifier,
Species, BodyLength, and FurColour.
The Elephant table will have: Identifier, Species,
ElephantWeightKG, and TuskLengthCM.
Each animal will have a list of "Items", I have an Items table from which Foxes and Elephants both share.
Currently, there is no overlap between the Fox and Elephant identifiers. But there is the
possibility in the future that the format of the Identifiers change
(worst case scenario there may be a bit of overlap). The database
should be designed to deal with this.

After a bit of trial and error, and research, this is what I have come up with:

create table Foxes
(
    Identifier varchar(50) not null constraint PK_FoxIdentifier primary key,
    Species varchar(50),
    BodyLength int,
    FurColour varchar(50),
);

create table Elephants
(
    Identifier varchar(50) not null constraint PK_ElephantsIdentifier primary key,
    Species varchar(50),
    ElephantWeightKG int,
    TuskLengthCM int,
);

create table Items
(
    ItemSeq int identity(1,1),
    ItemID as isnull(FoxID, ElephantID) + '-' + cast(ItemSeq as varchar) persisted primary key,
    FoxID varchar(50),
    ElephantID varchar(50),
    ItemDescription varchar(500),

    foreign key (FoxID) references Foxes(Identifier) on delete cascade on update cascade,
    foreign key (ElephantID) references Elephants(Identifier) on delete cascade on update cascade,

    constraint CK_FoxIDElephantsItemIdentifier check
    (
        case when FoxID is null then 0 else 1 end +
        case when ElephantID is null then 0 else 1 end = 1
    )
);

Is this an acceptable design? What would you do differently?

Thanks.


texts: Any way to force "garbage collection" in TokuDB?

texts:
Playing around with TokuDB I'm finding even after "optimize table" for things such as changing row compression or other DDLs it takes a non deterministic amount of time for freed space to be reflected in the file system.

Is there anyway to force this more immediately or otherwise view status of if the clean up is in progress or otherwise scheduled?


texts: Cannot restore SQL database backup to new database because new database is in use

texts:
I am attempting to create a new database from a backup file of an old database that is still in use. At one point I was able to successfully restore the database, but had to delete it later after attempting to overwrite it with fresh data. Now when I try to restore the database I get an error that the database is in use. I am certain to specify a different name from the old and have even attempted to give it a different name than the one I was giving it before. I have also made certain to rename the data and log files to something other than the old database.

This is the stack trace for the error:

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

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

   at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.PerformTask(ITaskExecutionContext context)
   at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.Perform(ITaskExecutionContext context)
   at Microsoft.SqlServer.Management.TaskForms.TaskExecutionManager.ExecuteTaskSequence(ISfcScriptCollector collector)

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

System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.SmoExtended)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&LinkId=20476

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

   at Microsoft.SqlServer.Management.Smo.RestorePlan.Execute()
   at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.PerformTask(ITaskExecutionContext context)

We have tried restarting the SQL server to close any possible calls to the database, but this did not correct the issue. I have checked the 'overwrite' and 'close existing connections' boxes in the restore wizard, but these also have not corrected the issue. Any other tips on how to correct this issue?