texts:
I'm not sure if I've completely fudged the design of my small hobby database (I'm not a DBA by any means), but I have a table like this (primary key is (staffid, effectivefrom)):

 staffid | target | effectivefrom
 --------|--------|---------------
  1      |  6.0   | 2012-01-01
  2      |  6.0   | 2012-01-01
  3      |  6.0   | 2012-01-01
  1      |  7.0   | 2012-03-01

So basically, three staff all start out with a target of 6.0, but on March, staff with ID 1 has a new target of 7.0. I want to maintain historical targets because it is relevant to other data in other tables.

I would like to have a user-defined function that takes a date as a parameter, and this function needs to join the above table with another table based on the date. Say the function is called with 1st of February as the date, I would like the result of the join to include the target column showing 6.0 for all staff.

Something like this (I think this won't work because there could be multiple rows before dateParameter):

 SELECT othertable.*, targets.target
     FROM othertable
     JOIN targets ON
         othertable.staffid = targets.staffid AND
         targets.effectivedate <= dateParameter;

Please let me know if I have done an absolute DBA 'no-no' or whether I just need some caffeine.


texts: Understanding Laravel relationships better

texts:
I want to make a simple project to understand better the Laravel database relationships. So I decided to make a movie storage project which is holding some movie names and some informations about that movie, it should be look like this:

So I wrote the relationships between these fields like this:
Movie AND Actor : Many To Many

Movie AND Director : Many To One

Movie AND IMDB : Many To One

Movie AND Genre : Many To Many

So now my question is, are these relationships correct or wrong ?


texts: Splitting Filegroups on a single SSD LUN

texts:
I've got an 8vcpu Hyperconverged 2008R2 Standard SQL Server with many TBs of data split across 4 Spindle LUNS.
Each database has its Filegroups split into 4 physical files.

I'm being given a 1TB SSD LUN (woo-hoo), my plan is to move all* non-clustered indexes to Index Filegroups on this LUN.

My question is: Should I split up the Filegroups into multiple files (even though it's the same LUN)?

I've read this article from 2011 by Paul Randal: https://www.sqlskills.com/blogs/paul/benchmarking-multiple-data-files-on-ssds-plus-the-latest-fusion-io-driver/
It suggests the Sweet Spot as being the number of Cores. But that was 6 years ago and things change.

I'm with an MSP who don't allow visibility of the Host, so I've got no idea about the true Physical NUMA I'm working with.

Can someone please point me in the direction of some good advice, or share their own sage advice?

*I won't blindly move all indexes. There will be some thought into that.


texts: Proper name for reference tables in OLTP

texts:
I know this might be a silly question. But I really appreciate accepting and answering this question.
When I have an OLTP database with some big tables like orders and items. I have other tables (similar to dimension tables in OLAP) where we store reference values, such as: currency, city, category.
What is the proper name for those reference tables? Can we still call them dimension tables in OLTP or do they have their own generic name?


texts: How to get non-deleted messages for one user_id if the same user_id appears in two tables at same time?

texts:
I'm trying to get non-deleted (deleted = 0) messages for one user_id if the same user_id appears in two tables at same time, but something is wrong because I am not getting the correct or expected data. This post is related to this other question (from me too).

So basically I have 3 tables:

default_recipient: stores user_id and message_id for not to repeat messages at default_messages table so when I reply to a message or send multiple users messages user_id and message_id references are stored at this table
default_messages: stores messages fields among sender user_id
default_profiles: stores fields related to users

Since the message is just one for sender and for receiver, then when one of the sides wants to delete and the other does not, then messages can't be physically deleted. So I mark the message with a flag deleted = 1.

If the same message_id has deleted = 1 in default_messages and also in default_recipient, then the message will be deleted physically because both parts sender and receiver deleted the message from their inbox.

Now I have these queries just for testing and see results:

1) select sender_user_id, deleted from default_messages;
2) select * from default_recipient;
3) SELECT DISTINCT p.*, rcp.*, msg.*, msg.id as message_id FROM default_messages msg
        LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id)
        LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id)
        WHERE msg.sender_user_id = 1 OR rcp.user_id = 1
        AND msg.deleted = 0
        GROUP BY (p.id)
        ORDER BY msg.date DESC;
4) SELECT rcp.user_id as rcpuid, rcp.message_id as rcpmid, msg.sender_user_id, msg.id as message_id FROM default_messages msg
        LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id)
        LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id)
        WHERE (msg.sender_user_id = 1 OR rcp.user_id = 1)
           AND (msg.deleted = 0 OR rcp.deleted = 0)
        ORDER BY msg.date DESC;

What I'm trying to do is get all messages from a specific user where the message aren't deleted. So taking as example user_id = 1, then I need to get those messages where default_messages.sender_user_id = 1 OR default_recipient.user_id = 1 AND default_messages = 0 AND default_recipient.deleted = 0 meaning: messages for user = 1 where deleted = 0.

Now with my data as you can see here and with queries 3 and 4 the results aren't correct and I'm looking to fix this query.

Any help is appreciated


texts: Setting up SQL server multiple Names but within the same Database

texts: Can we create different SQL Name (so will not change the previous connection strings) in a single SQL Server with the same content of the Database and log-in credentials?


texts:
I am creating a dynamic report on some data. This is using a reporting database, and I am only concerned about query speed, not insert speed, or anything else.

My first plan was to start extracting datacubes on dimensions to improve speed, however, looking at the table, I have the hunch that there are some other improvements I can make first.

The table in question is already "denormalized", but in a flat fashion, it looks something like this:

|ClientName                  |ClientId|InterestingMetric|
---------------------------------------------------------
|'Very big long client name' | 55     | 51650           |
|'Very big long client name' | 55     | 51655           |

I have the hunch that before I start "denormalizing", I might be able to get some some speed improvements by "normalizing" the data first. My reasoning is, there are only a few hundred clients, so I could get a speed up by reducing the table size and by reducing the comparison time by "normalizing" columns like client name; single simple inner joins on ints are very fast in relational dbs anyway.

Queries are doing comparisons on ClientName.

I know every case is different, and I have to run metrics, but am I on to something by thinking I may be able to get a  query performance boost by "normalizing" basic columns like client name?


texts: Which database system can break me free?

texts:
I am currently running a MySQL Database for logging and analyzing those logs.

My current table schema looks like this:

CREATE TABLE `mylogs` (
`transfer_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`client_id` INT UNSIGNED NOT NULL ,
`client_ip` INT UNSIGNED NOT NULL ,
`server_1_ip` INT UNSIGNED NOT NULL ,
`server_2_ip` INT UNSIGNED NOT NULL ,
`service` ENUM(  'service1',  'service2',  'service3',  '...',  'service500' ) NOT NULL ,
`mb_transferred` FLOAT UNSIGNED NOT NULL ,
`time` TIMESTAMP NOT NULL
) ENGINE = MYISAM ;

I run a service where I deliver a lot of downloads that go over 1 extra hosts between the destination and origin host.
They are represented by the integer interpretation of the 32 bit IP address.

My system currently handles about 500 inserts/second during peak hours.
I run a master-slave system. The master has an apache webserver with a PHP file that gets called from remote hosts and inserts a line into the log table.
Then the changes get replicated to the slaves where queries happen.

My queries are primarily aggregations over the mb_transferred field  over a range in the time field filtered by client_id.

SELECT SUM(mb_transferred) FROM mylogs WHERE client_id = 123 AND time > '2012-01-01 00:00:00'

The maser server runs an apache webserver with a simple php file that does the insert and is called by other servers.

My server is now almost at the limit. I already upgraded to big hardware.

I thought about using a GUID as primary key and using master master replication, that will for sure relieve something, but I think its short sighted, because it does not decrease the insert amount per server.

I am expecting higher trough-puts in the future and I am also worried about database size.

Also in future I plan to have a second table which defines "weights" for certain services.

Something like:

CREATE TABLE  `mylogs`.`service_weight` (
`plan` TINYINT NOT NULL ,
`service_name` ENUM(  'service_1',  'service_2' ) NOT NULL ,
`weight` FLOAT NOT NULL ,
PRIMARY KEY (  `plan` ,  `service_name` )
) ENGINE = MYISAM ;

Then I want to run join queries against this table and multiply the mb_transferred field with a weight factor.

I also want to add fields like "transfer_duration" to the logs table to calculate the speed of downloads and run queries to get statistical data how how well/bad the connection between certain networks, or certain servers for certain hosters is.

The point is. The data structure is simple, its just a huge amount of rows.

I have a lot of aggregation functions. This makes a light bulb in the "map reduce" section of my brain flashing.

I thougth about doing vertical shards and use client_id as a breaking point. For example if I have 10 server send every user to its userid mod 10 server.
This would be easy and relieve the load. But scaling will probably be awkward.

So i think with the size of the project that I am expecting to reach soon with the current growth I cannot do anything but turn towards a distributed database system.

I already tried to examine cassandra, project voldemort, amazon dynamodb and hbase but no matter how much I read I seem to run against walls.

I think the long years of relational thinking are somehow blockading my mind.

Can someone point me into the right direction on that?
What database system would be suited for my use case and why?

I dont need absolute up to the second data, a replication delay is acceptable.
I will not have updates, only inserts.
I would like to have a fault tolerant system
The data amount is getting huge so a sharded system would be nice
I have heavy I/O load


texts: What is the "Data partitioned, Shared-Nothing, Parallel Database" solution for PostgreSQL ?

texts: I was looking for a open source parallel database solution. It seems Postgres-XC is a viable solution. However, I don't need the replication. In this case, is there any better solution towards read optimized parallel architecture ?


texts:
Problem: I have an Application (in C#) that uses a regex from a SQL Table Column to check certain things. The details of that don't really matter for the question.

The thing is: "Key" users can make entries to that table (not through the Application but directly :( ). Now it happens from time to time, that they mess up regex syntax which leads to the Application to fail and I cannot change code of the Application.

What I thought of was: maybe there is a way to have MS SQL server check Regex syntax on insert and fail the insert if incorrect.

All search results I found deal with how to use regex in TSQL for SELECT etc. But that is not what I need. I just want the regex's syntax be checked and fail the insert if it is incorrect. Also the semantics of the regex is irrelevant. I just want the program to not fail with an exception. If the regex does not do what the user expects, then that's a different story.

Has anybody done this before or has an idea how this could be done? "That's impossible" would also be an appreciated answer, so I don't have to look any further and can turn to other approaches.


texts: Add column to database, used with logical replication generates large WAL files

texts:
I have a PostgreSQL 10 master database with a logical replication, but I faced with an issue.
I added one more column (int, without default value) to a big table in master (weight 39gb, over 100 million entries) within a transaction, and updated its value with a same value in another column.
begin;
alter table test add column "onecolumn" int;
update table test set "onecolumn"="secondcolumn"
commit;

Finally it generated large WAL files with weight 39gb, so Postgres replicated whole table instead of only a column.
Why Postgres generated so big WAL files? Because real weight of adding one column with int should be much less.
Replica identity of the table is set to default


texts: Oracle Context Index - finding unsynchronised records

texts:
Context indexes can be update with the procedure:

Ctx_Ddl.Sync_Index (index_name);

But what exactly is synchronised by this function?  How does Oracle know what is unsynchronised (i.e. how does it track the delta between what's in the index and what's in the underlying data)?  And can I as a DBA view this delta?

Moreover, am I able to exert some control over what data is synchronised?  Can I stop it from synchronising certain data within the index?


texts: Why MySQL ignores foreign keys when importing?

texts:
i don't have a big knowledge in database so excuse me if there is bad syntax or bad description but i did my best :)

When i importing to child table it ignores foreign key though the parent table is empty and hasn't any data yet, i used the following command to dump the data without this parent table data

mysqldump --ignore-table --no-create-info old_database > data.sql

then i importing this data to new database I've created

mysql new_database < data.sql

then i dumped the parent row data only using this command

mysqldump --tab="\\uploads"

importing parent_data as below

mysqlimport "\\uploads\parent_data.txt" new_database

why MySQL accepts this and ignore the existing of foreign key ? and is there a way to force MySQL to not ignore FKs when importing ?

child table show create info

| child |
CREATE TABLE `child`
(
    `id` bigint(19) NOT NULL,
    `flag` int(10) NOT NULL,
    PRIMARY KEY (`id`, `flag`),
    KEY `FK72610D66B7AE7814` (`flag`),
    CONSTRAINT `FK72610D66AFB765B8`
        FOREIGN KEY (`id`) REFERENCES `parent` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

parent create info

| parent |
CREATE TABLE `parent`
(
    `id` bigint(19) NOT NULL AUTO_INCREMENT,
    ...
) ;


texts: Is there a way to calculate yesterday / today of last month / year in SQL?

texts:
Is there a way to calculate today (and yesterday) of last month (and last year) in SQL?

Assuming today is 26-May-2015 then how to find?

Today of last year i.e. 26-May-2014?
Today of last month i.e. 26-April-2015?
Yesterday of last year i.e. 25-May-2014?
Yesterday of last month i.e. 25-April-2015?


texts: What is the best approach to index varchar and date range?

texts:
There is an order table which stores customer information and location. I need to filter the customer's last five locations within the last six months based on order status.

Query as follows,

SELECT *
FROM orders
WHERE (    status = 4
       AND receiver_phone = '91xxxxxxx'
       AND created_date BETWEEN '2017-08-06' AND '2017-03-01'
       AND NOT (    receiver_latitude = '10.9315615'
                AND receiver_latitude IS NOT NULL)
      )
ORDER BY created_date DESC
limit 5;

EXPLAIN ANALYZE:

    Limit  (cost=374121.96..374121.97 rows=1 width=449) (actual time=2151.298..2151.298 rows=0 loops=1)
   ->  Sort  (cost=374121.96..374121.97 rows=1 width=449) (actual time=2151.297..2151.297 rows=0 loops=1)
         Sort Key: created_date DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on orders  (cost=0.00..374121.95 rows=1 width=449) (actual time=2151.262..2151.262 rows=0 loops=1)
               Filter: ((created_date >= '2017-08-06 00:00:00+00'::timestamp with time zone) AND (created_date <= '2017-03-01 00:00:00+00'::timestamp with time zone) AND ((receiver_latitude <> 10.9315615) OR (receiver_latitude IS NULL)) AND (status = 4) AND ((receiver_phone)::text = '91xxxxxx'::text))
               Rows Removed by Filter: 4376895
 Planning time: 0.267 ms
 Execution time: 2151.917 ms

what are best approaches to optimize this query?


texts: Error migrating a big table from mySQL to SQL Server 2012

texts:
I'm trying to convert several mySQL tables to Microsoft SQL Server 2012 tables using the SQL Server Migration Assistant for MySQL.  The small tables get converted but when it comes to the big one (slightly less than 4 GB), after about 1:30 hour of processing, it gives the following error:

  (Elapsed Time = 00:01:24:32:197). Cannot truncate target table.
  Reason: ExecuteReader requires an open and available Connection. The
  connection's current state is closed.

Here is the output:

Migrating data...
Analyzing metadata...
Preparing table `big fmail user`.conversionlog...
Preparing data migration package...
Starting data migration Engine
Starting data migration...
The data migration engine is migrating table '`big fmail user`.conversionlog': > [big fmail user].dbo.conversionlog, 87489673 rows total
Table `big fmail user`.conversionlog data migration: 8760542 rows processed.
Table `big fmail user`.conversionlog data migration: 17510722 rows processed.
Table `big fmail user`.conversionlog data migration: 26249995 rows processed.
Table `big fmail user`.conversionlog data migration: 34998808 rows processed.
Table `big fmail user`.conversionlog data migration: 43757368 rows processed.
Table `big fmail user`.conversionlog data migration: 52494656 rows processed.
Table `big fmail user`.conversionlog data migration: 61254458 rows processed.
Cannot truncate target table. Reason: ExecuteReader requires an open and available Connection. The connection's current state is closed.
 Errors: Cannot truncate target table. Reason: ExecuteReader requires an open and available Connection. The connection's current state is closed.
Table `big fmail user`.conversionlog data migration: 0 rows processed.
Completing migration of table `big fmail user`.conversionlog...
Migration complete for table '`big fmail user`.conversionlog': > [big fmail user].dbo.conversionlog, 0 rows migrated (Elapsed Time = 00:01:24:32:197).
ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
Information: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
Data migration operation has finished.
    0 table(s) successfully migrated.
    0 table(s) partially migrated.
    1 table(s) failed to migrate.

Any suggestions as to what might cause this error, and how I should resolve it?


texts:
I'm trying to change the default port for mariadb to 52000. I've modifed the port in /etc/MySQL/mariadb.conf.d/50-server.cnf and restarted the service. However, each time it still starts up on the default port.

I'm running Debian. Is there another config somewhere that I need to modify?


texts: SQL Datetime / datetimeoffset(2) comparison & implicit cast wierdness

texts:
I discovered a problem when inserting data in our database. My insert statement was checking for the existence of data in the WHERE clause to prevent duplicate data being inserted. None was detected, and the INSERT happend. However, the unique constraint rejected the data as it already existed in the database.

The problem was the data to be inserted was DATETIMEOFFSET(2) and the database field being inserted into was DATETIME.

To show you want I'm talking about, run the following:

DECLARE @dt  DATETIME          = '2014-07-07 09:49:33.000';
DECLARE @dto DATETIMEOFFSET(2) = '2014-07-07 09:49:33.00 +07:00';

PRINT CASE WHEN @dt = @dto THEN 'Equals matches'
           ELSE 'Equals does not match'
      END

PRINT CASE WHEN @dt = CAST(@dto AS DATETIME) THEN 'Cast matches'
           ELSE 'Cast does not match'
      END

It prints:

Equals does not match
Cast matches

The comparison (=) operator does not perform the same way the implicit cast does if you insert the data. The cast/convert operator actually throws away the offset! Madness.

Why does the comparison operator work differently to the implicit cast that happends during an INSERT?


texts: Trying to restore users/passwords for a MySQL server

texts:
I had a system failure, but I managed to save the /var/lib/mysql library on my linux system.

I have since rebuilt a new Linux (Ubuntu) system (LAMP). I copied the old /var/lib/mysql/mysql folder to the new system as /var/lib/mysql/old_mysql, and I can see the contents using phpmyadmin.

I also copied all the folders from the old /var/lib/mysql to the new /var/lib/mysql.

If I do the following:

Export the contents of the old_mysql/user table, and import it into the new mysql/user table

Export the contents of the old_mysql/db table, and import it into the new mysql/db table

Will that restore the users and their passwords for all the databases?


texts: How to send an email with embedded values

texts:
I need to do a stored procedure that sends an email when a value reaches 100 with this wording in the body: "100 daily file downloaded from the user:USERID" where USERID is the user that reached 100.

I tried this:

    EXEC msdb.dbo.sp_send_dbmail
        @recipients='MYMAIL',
        @body='100 Daily documents downloaded',
        @subject ='100 Daily documents downloaded from user',
        @query = 'SELECT USERID FROM MYTABLE WHERE MYCOUNTER > ''100'''

I receive this error:

  Messaggio 22050, livello 16, stato 1, riga 0 Error formatting query, probably invalid parameters Messaggio 14661, livello 16, stato 1,
  procedura sp_send_dbmail, riga 504 Esecuzione della query non
  riuscita: Msg 208, Level 16, State 1, Server MYSERVER, Line 1 Invalid
  object name 'MYTABLE'.


texts:
I'm using MATCH AGAINST in boolean mode to add a simple search functionality to my site.

The column to be searched is varchar, with no full text index. The column value can be alphanumeric, all letters or all numbers, as well as certain combinations of both (eg. ABC-123').

There seems to be a few problems. First, the search doesn't work when there's a hyphen (like ABC-123). Second, it will not find partial matches. For a value of 856059, the searches look like this:

MATCH (field) AGAINST ('+856049' IN BOOLEAN MODE) // finds the record
MATCH (field) AGAINST ('+85604' IN BOOLEAN MODE) // does not find the record
MATCH (field) AGAINST ('+8560' IN BOOLEAN MODE) // does not find the record

I'm not sure why this is working this way. For various reasons I can't add a fulltext index to the column and use the "default" match against mode.


texts: Oracle 12 Startup upgrade throws error 443

texts:
Please follow the logic of my attempts at upgrade before suggesting a fix.  The error 443 is not the main problem here.

we are upgrading from oracle 11.2.0.4 to 12.2.0.1 on a Solaris 11.3 server 'test'.
all upgrades are successful except the one database called VIT which throws the error on startup upgrade (after logging in as oracle12): terminating the instance due to error 443
we cloned VIT and mounted it on another server.  It upgrades successfully.  We then mounted the same clone on 'test' and it does not startup, throwing the same error 443.
we then renamed VIT to VITNEW on 'test' and the upgrade works!  We then renamed it back to VIT and it throws the same error 443 on startup.

So after all these attempts it's obvious to me that there is either something with the name or the server, but bear in mind other databases work on that same server, and it's ONLY that name of VIT that does not work on 'test'.

Weird problem.  We have opened a SR for it, but Oracle have no answers yet.  Hoping someone might have an idea.


texts: SQL Server 2014 Reporting Services issues

texts:
I have set up SSRS on MS SQL 2014 Dev Edition using the ReportServer built in account as the Service Account.

My machine name is: POUYASAGER
My local login is pyous
I have given the ReportServer account full permissions to:

C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services

I have checked that SecureConnectionLevel is set to 0 in the rsreportserver.config file.

I set up ReportServer as the virtual directory so that the Report Server Web Service URLs would be: http://POUYASAGER:80/ReportServer

When I click on the link in the Reporting Services Configuration Manager which is the same as above, I get:

The permissions granted to user 'POUYASAGER\pyous' are insufficient for performing this operation. (rsAccessDenied)

When I navigate to: http://127.0.0.1:80/ReportServer, an authentication pop-up asks for a username and password.

When I navigate to the reports manager using: http://pouyasager:80/Reports, I get:
Error
Unable to connect to the remote server

How can I get SSRS working properly?

EDIT: I added "localhost" to the host headers and still got the same error.
EDIT: Using Chrome with Admin permissions, I navigate to http://localhost:80/ReportServer with the same results.


texts: Pagination with PostgreSQL 9.3: counting number of pages

texts:
I'm implementing pagination and sorting of rows in, let's say, products table, using multicolumn index on category, score and id.

-- index
create index category_score_id on products(category, score, id)
  where active = true;

-- selecting first page
select * from products where
  category = 1
  and active = true
order by score desc, id desc
limit 30;

-- selecting following pages
select * from products where
  category = 1
  and active = true
  and (score, id) < (893, 102458)  -- values from previous page
order by score desc, id desc
limit 30;

This works perfectly fine and as expected, using only index scan:

-- explain analyze of 2-nd query:
Limit  (cost=0.00..99.52 rows=30 width=2591) (actual time=0.090..0.179 rows=30 loops=1)
  ->  Index Scan Backward using category_score_id on products  (cost=0.00..76435.07 rows=23041 width=2591) (actual time=0.089..0.172 rows=30 loops=1)
        Index Cond: ((category = 1) AND (ROW(score, id) < ROW(893, 102458)))
Total runtime: 0.085 ms

What I am curious about is why the following query requires extra Bitmap Heap Scan:

select count(*) from products where
  category = 1
  and active = true;

-- explain analyze:
Aggregate  (cost=49987.80..49987.81 rows=1 width=0) (actual time=104.847..104.847 rows=1 loops=1)
  ->  Bitmap Heap Scan on products  (cost=538.85..49930.20 rows=23041 width=0) (actual time=8.919..98.952 rows=47937     loops=1)
        Recheck Cond: ((category = 1) AND active)
        Rows Removed by Index Recheck: 93006
        ->  Bitmap Index Scan on category_score_id  (cost=0.00..533.09 rows=23041 width=0) (actual time=7.181..7.181 rows=47937     loops=1)
              Index Cond: (category = 1)
Total runtime: 104.892 ms


texts: Oracle - Need help with RMAN Active Duplication on Windows 32 bit to a 64 bit

texts:
I need to have an Oracle 11g R1 on a Windows 32-bit duplicated to a Windows 64-bit using RMAN Active Duplication.

32-bit setup:

  Windows Server 2003 R2 Standard Ed. SP2
  Database - Oracle 11g  11.1.0.6.0

64-bit setup:

  Windows Server 2008 R2 Standard Ed.
  Startup Database - Oracle 11g 11.1.0.7.0

Although 11g R1 is not certified on Windows Server 2008 R2 it installed fine without any problems.

RMAN was invoked from 11g 11.1.0.6.0 as the TARGET to AUXILIARY 11g 11.1.0.7.0
(This was done for backward compatibility issues; to match the same or higher RMAN version).
Either database is not an RCVCAT, but connections are established as TARGET or AUXILIARY.

For privacy purposes, I replaced the names for the databases to match 32-bit and 64-bit accordingly.

Working from the 32-bit system, after executing RMAN I got the following error:

Starting Duplicate Db at 07-JAN-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=152 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   file  'D:\dbData\product\11.1.0\db\DATABASE\PWD32bit.ORA' auxiliary format
   'C:\app\oracle\product\11.1.0.7.0\db_1\DATABASE\PWD64bit.ORA'   ;
}
executing Memory Script

Starting backup at 07-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=118 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/07/2010 16:39:21
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/07/2010 16:39:21
ORA-17627: ORA-12577: Message 12577 not found;  product=RDBMS; facility=ORA

While invoking RMAN from the 64-bit I had the following:

PL/SQL package SYS.DBMS_BACKUP_RESTORE version 11.01.00.06 in TARGET database is notcurrent
PL/SQL package SYS.DBMS_RCVMAN version 11.01.00.06 in TARGET database is not current
connected to target database: 32-bit (DBID=2889597870)

If the problem is RMAN compatibility issues is it possible to upgrade to a higher RMAN version (in the 32-bit) or downgrade to a lower RMAN (if at all possible, in the 64-bit)
instead of upgrading the 32-bit database to patch 7 (11.1.0.7.0)?
The 32-bit database will be decommissioned after the duplication to the 64-bit.

If this is possible can anyone help in providing information about how to accomplish this?
Or if unrelated to this, then what is causing this problem and which way should I start troubleshooting?

Any help would be greatly appreciated.


texts: Could anyone please help me understand MSSQL Compatibility Level?

texts:
I have a task to upgrade a database from SQL Server 2005 to SQL Server 2012.
So I took backup of the old database and simply restored it onto SQL Server 2012 (SP3).

Executed query on New database:

SELECT compatibility_level
FROM sys.databases
WHERE name = 'MyNewDatabase';

Result:

compatibility_level
-------------------
90

But System db's are showing Compatibility_Level as '110' of MSSQL-Server-2012.

Let me conclude my problems with below questions:

If Compatibility_level = 90 is for SQL Server 2005, then has the database really been upgraded to SQL Server 2012 or not?
Why does the restored database show Compatibility_Level as '90' and not '110'?
Also, if it is only contains features of SQL Server 2005, then what is the better way to upgrade it to SQL Server 2012?

Is the upgrade as simple as "ALTER DB"? Like this:

ALTER DATABASE MyNewDatabase
SET COMPATIBILITY_LEVEL = '110';


texts: xp_delete_file - error message when no files for deleting

texts:
I have a job that deletes transaction log backups older than 3 days.

if for some reason there are no files to delete, it comes out with the following error message:

Msg 22049, Sev 16, State 1: xp_delete_file() returned error 2, 'The system cannot find the file specified.' [SQLSTATE 42000]
Job 'Log Shipping - Delete tran backups older than 3 days' : Step 1, 'delete the trn files older than 3 days' : Began Executing 2019-01-18 23:00:02

Msg 22049, Sev 16, State 1: xp_delete_file() returned error 2, 'The system cannot find the file specified.' [SQLSTATE 42000]

this is the code of the job:

-This will set the date time stamp to pass onto the Stored Proc for 72 hours from the current date when it runs
--The stored proc will recursively delete all TRN files from the parent level specified all the way down
declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(hh, -72, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

--5th argument below "0 - don't delete recursively (default)" and  "1 - delete files in sub directories"
EXECUTE master.dbo.xp_delete_file 0,N'\\homer\S_SYSTEMS\FullBackups\MY_SERVER\_log1\',N'trn', @DeleteDate,1

--5th argument below "0 - don't delete recursively (default)" and  "1 - delete files in sub directories"
EXECUTE master.dbo.xp_delete_file 0,N'\\homer\S_SYSTEMS\FullBackups\MY_SERVER\_log12',N'trn', @DeleteDate,1

Here is the job:

how can we change this?
if there are no files, that's fine, no need to fail the job.


texts: Server permissions of an activation stored procedure of a Server Broker queue

texts:
I have a stored procedure that queries the sys.dm_exec_requests view. In the stored procedure the view only returns one row, while the stored procedure needs to see all of them. The MSDN article on the view says that what is returned depends on the user permission:

  If the user has VIEW SERVER STATE permission on the server, the user
  will see all executing sessions on the instance of SQL Server;
  otherwise, the user will see only the current session.

The stored procedure is actually an activation stored procedure of a broker queue:

CREATE QUEUE test_queue
  WITH
  STATUS = ON,
  RETENTION = OFF ,
  ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = test_procedure,
    MAX_QUEUE_READERS = 1,
    EXECUTE AS SELF ),
POISON_MESSAGE_HANDLING (STATUS = ON)
ON [PRIMARY]

When I read the MSDN article, I changed

EXECUTE AS SELF

to

EXECUTE AS 'dbo'

which did not make any difference. sys.dm_exec_requests would still return a single row.

Also I tried to do

EXECUTE AS OWNER

Which made no difference either. As I understand the stored procedure owner is who created it, which was me. And as I'm in a sysadmin role that should be working, but it does not. I'll appreciate any troubleshooting tips.

In particular I would like to know how to list a server permissions for a given user so I could check if they include 'VIEW SERVER STATE' permission as per the article.

The confusing part is that user as I understand it is on database level, so it's unclear to me how it can have server permission. If you could clarify that, it would be great too.


texts: Restoring XtraBackup from MySQL 5.6 to MySQL 5.7

texts:
We are running MySQL 5.6 and taking backups using XtraBackup 2.4. We want to migrate to MySQL 5.7 using that same backup as we have datasets well over 600GB.

Is it possible to do so and what are prons and cons that we need to worry about while we use the XtraBackup restore?


texts:
I'm installing SQL Server 2008 R2 which would be helpful in

A. querying database.

B. Analysis Services

C. Reporting services and BIDS (SSRS)

D. Powerpivot in Sharepoint

During installation I get two options:

a. SQL Server feature installation: this would install database engine, analysis services, reporting services etc

b.  SQL Server for powerpivot for sharepoint: This would be helpful for powerpiviot in Sharepoint. Not sure this option would install all the options mentioned in first option - SQL Server feature installation.

My Question: Which option I should select? If i go for option-a at present, can I later change it to powerpivot option i.e option b.???


texts: How can I create a unique and random alphanumeric string using MySQL's Default Value? (v 5.6)

texts:
I am building a MySQL database and think using implicit DEFAULTS seems very promising.

I am successfully using DEFAULT CURRENT_TIMESTAMP and DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP for on insert timestamps and on update timestamps.

I would like to add a default for a public key. A 32 character unique and random alphanumeric string. Is this possible using the DEFAULT field specification? If so, how?

Or must I handle this by creating a random value and checking it's uniqueness, for the 1 in 1.5^50 chance it's a duplicate, on INSERT?

This article seemed promising, but didn't give me an definitive answers: https://www.percona.com/blog/2015/04/29/generated-virtual-columns-in-mysql-5-7-labs/

Thank you in advance


texts:
I've used dynamic SQL for many tasks and continuously run into the same problem: Printing values of variables used inside the Dynamic T-SQL statement.

EG:

Declare @SQL nvarchar(max), @Params nvarchar(max), @DebugMode bit, @Foobar int
select @DebugMode=1,@Foobar=364556423

set @SQL='Select @Foobar'
set @Params=N'@Foobar int'

if @DebugMode=1 print @SQL
exec sp_executeSQL @SQL,@Params
    ,@Foobar=@Foobar

The print results of the above code are simply "Select @Foobar". Is there any way to dynamically print the values & variable names of the sql being executed? Or when doing the print, replace parameters with their actual values so the SQL is re-runnable?

I have played with creating a function or two to accomplish something similar, but with data type conversions, pattern matching truncation issues, and non-dynamic solutions. I'm curious how other developers solve this issue without manually printing each and every variable manually.


texts: TSQL 2012 deleted table in INSTEAD OF and AFTER triggers?

texts:
My colleague ask me very simple, but difficult for me question:

If I have deleted table with 10 records in INSTEAD OF trigger and I delete 8 of them how many records will I have in deleted table in AFTER trigger?

I think it wuold be 10, but I am not sure.


texts: Oracle Stopping - Can't Login All Of A Sudden -

texts:
I'm having  a problem with Oracle, it happens randomly around once in three weeks or once a month. The instance that i'm using suddenly hangs, and no logins can be established to it.It won't accept connections and I can't login to it.
I would see no error in the alert logs. I would restart my application but the application still can't connect to it. There were no locks at that time. The thing is, there was no error in the alert logs as well, it would just hang all of a sudden. The only way to recover is to restart it. In my java thread dumps, I would see that the application is hanging at Socket.read part

What are Oracle's problems when I can't login to it at all? I can't initialize a connection that's why I need to resrtart the database. After restarting, I can connect again. There are no error on the alert logs.

What do you think I should look at?

Thanks


texts: Find the remote userid causing updates on my table

texts:
Small question here. I found some answer close to it but not exactly what I'm looking for.

Here is the situation: I have a SQL Server trigger on a table. Its task is to record the username (logon id) of the user who make some insert/update/delete.

So far, it's not so hard to find the information.

The complicated part is coming. The insert statements are made through an Access view with a generic SQL Server account.

What I want to do is find the (Windows) user that launch Access. I was able to find the host name (the name of the computer) but how can I found the user?

Thanks!


texts: ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xx.xx' (10060)

texts:
I'm trying to connect my local MySQL workbench to a 2nd generation MySQL instance on Google SQL platform using the following command on my windows laptop.

mysql --host=130.xxx.xx.xx --port=3306 --user=root --password

I have also created a user in the MYSQL from the Google Cloud shell and created a 'cwood' user and granted all permissions

 mysql --host=130.xxx.xx.xx --port=3306 --user=cwood --password

should the username be  cwood@something?

I double checked my IP address with whatismyIPaddress it is granted permission in the allowed network section.

I can connect via Google Cloud Shell on the Google Platform itself using root and also ping the IP address from the local cmd prompt and ping my machine from google shell.

As a troubleshooting step I have disabled the local firewalls on my laptop and granted full access to 0.0.0.0/0 to allow all connections on the google platform console so dont think this is the issue.

still no joy from either a cmd line connection on windows or using a putty client, can anyone give me any further pointers on the missing step? something like allow remote connections? being a managed service I dont have the local config files, ip tables as many google searches suggest

Cheers..


texts: How to find number of products having SALE price 50% of MRP using MYSQL?

texts:
Given below table(Product)

id|name|price_type|price|
--|----|----------|-----|
 1|book|MRP       |   10|
 2|book|SALE      |    5|
 3|pen |MRP       |   10|

In this table the answer is 1, since only one product "book" is having sale price 50% of mrp.

I tried doing the following query, but it is giving error

select name from product group by name HAVING  (count(product.name)>1 && product.price_type="MPR" && product.price_type="SALE");


texts: Why does this query not return records where both fields using CASE WHEN are zero?

texts:
I'm attempting challenge 13 on this SQLzoo page, but my query returns one fewer row than it's supposed to.

SELECT mdate,
  team1,
  SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) AS score1,
  team2,
  SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) AS score2
  FROM game JOIN goal ON matchid = id
GROUP BY mdate, matchid, team1, team2

When both SUM values would be zero, the query just doesn't return than record and I've no idea why.


texts:
I am trying to find answers for my below few queries which will help me in fine tuning my postgres DB. I did some googling but was not able to find answer.

1) Out of vacuum or Auto vacuum which is more resource intensive operation?
2) Is there a way to run Auto vacuum only when there is unusually heavy update activity?
3) How can I find out based on setting when my auto vacuum will start next time, based on threshold.


texts:
On SQL Server, I'm trying to prevent connection from staying open. From here I understand that Connection Lifetime allows a connection to stay alive for a certain period of time since it was opened.

Quote: When a connection is returned to the pool, its creation time is compared with the current time...

Is there a way to do something similar, but with connections who are idle? When they return to the pool they'd be allowed to live for X seconds until killing them, unless they're used again.


texts:
when I run my stored procedure it is a complete success, even if I run it through exec.

Running these:

    sp_GetServerLogins
    GO
    exec ( 'sp_GetServerLogins ')
    GO

I get this:

Needless to say this first:

    select @@servername

    EXEC sp_serveroption @server = @@servername
                        ,@optname =  'data access'
                        ,@optvalue =  'TRUE'

Now when I run the same stored procedure, on the same server, using OPENQUERY, this is what I get:

        declare @sql nvarchar(max)
        select @sql = 'SELECT * FROM OPENQUERY(' + quotename(@@servername) +
',' + '''' + 'SET FMTONLY OFF; exec sp_GetServerLogins ' + '''' + ')'

    exec ( @sql)

If you haven't found the difference is in the Login_From.

Basically what I am trying to achieve here is to find windows_logins that belong to a windows_group login that are also present in the same server.

Inside my procedure the critical line that get the windows group for an individual login is the following:

INSERT INTO @UserList EXEC xp_logininfo @LOGIN, 'members'

The funny thing is that I have servers where these two (running the procedure through exec AND running the procedure through OPENQUERY) return the exactly same thing. That is great.

Some other servers, however, give me the results above.

What is missing?
any ideas?

cheers
Marcello

P.S.

I need to use OPENQUERY because I want to achieve something like this, on the code below:

    drop table #radhe

    CREATE TABLE #Radhe(
        [n] [bigint] NULL,
        [Login_Name] [nvarchar](128) NULL,
        [Login_From] [nvarchar](128) NULL,
        [Account_Type] [nvarchar](128) NULL
    )

        declare @sql nvarchar(max)
        select @sql = 'SELECT * FROM OPENQUERY(' + quotename(@@servername) + ',' + '''' + 'SET FMTONLY OFF; exec sp_GetServerLogins ' + '''' + ')'

        INSERT INTO #Radhe
        exec ( @sql)

        SELECT * FROM #Radhe

Basically I have my data inside a temp table, it could be in fact a table variable.

If I do without the OPEQUERY:

    drop table #radhe

    CREATE TABLE #Radhe(
        [n] [bigint] NULL,
        [Login_Name] [nvarchar](128) NULL,
        [Login_From] [nvarchar](128) NULL,
        [Account_Type] [nvarchar](128) NULL
    )

        INSERT INTO #Radhe
        exec sp_GetServerLogins

I get the error below:

Msg 3930, Level 16, State 1, Line 11
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

when I use the function fn_my_permissions, I get the exact permissions inside and outside the OPENQUERY.

                declare @sql nvarchar(max)
                select @sql = 'SELECT * FROM OPENQUERY(' +

quotename(@@servername) + ',' + '''' + 'SET FMTONLY OFF; SELECT * FROM

fn_my_permissions(NULL, ''''SERVER''''); ' + '''' + ')'
                exec ( @sql)


texts:
So, I currently have a problem with my mySQL server opening far too many file handles.
This requires me to restart to server each day, as otherwise, I get the infamous "errno 24, too many open files" error.
I have already tried raising the system maximum count to 500k, but I still get that error.
At the time mySQL stops working, it uses about 384k file handles, far too many in my books.
System information:

VPS running on a Intel Xeon E5-2670 v3
12GB of DDR4 RAM
240GB SSD
Debian 8 64-bit, latest updates

More than enough ram.
my.cnf
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
explicit_defaults_for_timestamp

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address    = 127.0.0.1

# Fine tuning
max_allowed_packet = 16M
thread_stack       = 192K
thread_cache_size = 8
myisam_recover_options = BACKUP
max_connections = 384
key_buffer_size = 284M
tmp_table_size = 32M
max_heap_table_size = 32M

# Query cache
query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 16M

# Join buffer
join_buffer_size = 600K
table_open_cache = 440

# InnoDB
innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 32M

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 10
log-error   = /var/log/mysql/error.log

# Other stuff
[isamchk]
key_buffer = 16M

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

I am looking for a way to reduce the amount of file handles used, not raise it.
Thanks!


texts: Do I need full recovery mode when I have multiple daily backup?

texts:
I have created 5 maintenance plans on my SQL server (say 50 !) which generate databse backup 5 times daily. As I read in this Q/A the full recovery is good when I need Point-in-time recovery.

My exact question: Is generating multiple daily backup with simple recovery model, similar to full recovery backup with longer period?


texts: PostgreSQL: pg_upgrade 9.5 to 13 failing with "connection to database failed: FATAL: database "template1" does not exist"

texts:
I'm trying to upgrade a 9.5 server to 13 but the pg_upgrade process is failing after the main checks.  Server is running CentOS 8 on EC2 and is using the pgdg repo installs of each server.
bash-4.4$ /usr/pgsql-13/bin/pg_upgrade --old-bindir /usr/pgsql-9.5/bin --new-bindir /usr/pgsql-13/bin --old-datadir /var/lib/pgsql/9.5/data --new-datadir /var/lib/pgsql/13/data --old-port=54320 --new-port=5432 --socketdir=/var/run/postgresql/
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Checking for invalid "unknown" user columns                 ok
Checking for roles starting with "pg_"                      ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok

connection to database failed: FATAL:  database "template1" does not exist

could not connect to target postmaster started with the command:
"/usr/pgsql-13/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/13/data" -o "-p 5432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off -c vacuum_defer_cleanup_age=0  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/run/postgresql'" start
Failure, exiting

When I run that server start command manually, the server starts.  There's nothing in the log that indicates what the issue is for the pg_upgrade command.
Due to the size of the databases in question I can't do a dump/restore as in my testing it took over 48 hours to perform that process even when copying on the same machine.  So I'm hoping to get the upgrade process to work.


texts: importing myisam 5.0 database into a 5.5 innodb server

texts:
I have MySQL 5.0 and 5.5 servers (but not a 5.1 server).  The 5.0 server is myisam only, the 5.5 default is innodb.  I need to move a database from the 5.0 server to the 5.5 server.  The database is not big, the dump is about 32M.  Both servers are Linux.

I ran a test by importing the 5.0 database into the 5.5 (after editing dumpfile from MYISAM to INNODB) server and then diffing mysqldumps from both servers.  If I use
--order-by-primay for the dump, the diffs are only what I would expect (version, engine, dump time, etc).

Any gotchas that I am missing?  Can I import a myisam 5.0 database into a 5.5 innodb server without bad surprises?


texts:
I have a couple of questions regarding working of indexes in PostgreSQL.
I have a Friends table with the following index:

   Friends ( user_id1 ,user_id2)

user_id1 and user_id2 are foreign keys to user table

Are these equivalent? If not then why?

Index(user_id1,user_id2) and Index(user_id2,user_id1)

If I create Primary Key(user_id1,user_id2), does it automatically create indexes for it and

If the indexes in the first question are not equivalent, then which index is created on above primary key command?


texts: Set column to randomly chosen value from another table

texts:
Consider this:

UPDATE dest
SET dest_col = (SELECT s.src_col FROM source s
                WHERE s.name = 'abc'
                ORDER BY random() LIMIT 1 OFFSET 0);

My goal: Set dest.dest_col in every row to a randomly picked value out of all the values matched by the subquery.

To accomplish that, I added ORDER BY random() LIMIT 1. But this works by picking a random value once, and then setting it to every row in the dest.dest_col.

I tried adding OFFSET 0 (suggested in other answers as a way to prevent Postgres from optimizing the subquery), but this doesn't seem to have any effect.

Also note that I need this to work on PostgreSQL running on GCP.


texts:
I need to print an empty string using EXECUTE command in Postgres. So, how to circle with quotes the string str in order for Postgres to realize my intention?

CREATE OR REPLACE FUNCTION foo() RETURNS void AS
$$
DECLARE
empty_str text='';
BEGIN
EXECUTE 'SELECT '||str||'';
END;
$$
LANGUAGE plpgsql;

Table name is a variable in my query.

I want to print nothing at all (not even a carriage return and/or newline). It's a simplified example of my real code where I have to substitute NULL values with empty strings. I use empty string as an option value in NULL 'null_string' in COPY function.


texts: Can I use a mariadb/mysql dump from older version in newer version?

texts:
I'm a mariadb/mysql newbie. I want to take a complete backup.

Apparently this is the way (please correct me if I'm wrong):

mysqldump \
  -h... -u... -p... \
  --hex-blob --routines --triggers \
  --all-databases \
  --add-drop-database --add-drop-table \
  | gzip > backup.sql.gz

But according to this answer:

  The drawback is that mysqldumps created this way can only be reloaded into the same majot release version of mysql that the mysqldump was generated. In other words, a mysqldump from a MySQL 5.0 database cannot be loaded in 5.1 or 5.5. The reason ? The mysql schema is total different among major releases.

That was written in 2011 - is it still true?


texts: How to select the first unique data type in a table

texts:
Sorry for the messy layout question. This is my first post.

I have the following table structure under ORACLE database

List item

  Row     A      B        Date
  --------------------------------
  1       X      X        01-2012
  2       X      X        01-2013
  3       Null   Null     01-2013
  4       New    Apple    01-2016
  5       New    Orange   08-2015
  6       Null   Null     09-2015

I want to be able to query for the first time that "New" is in the database entry and get the value for both A, B and Date

Tried using syntax first_row but i get an invalid identifier result. Also attempted to create subqueries to limit and group by 'New' data but the issue I'm having is to only look at the "first" ever row per Date


texts:
I am going to comment on the scenario that I currently have when designing my database.

I have the following tables:

user (id, name, lastname)

role (id, name)

user_role (user_id, role_id, from_date, to_date)

period (id, name, from_date, to_date)

The period table serves as a data master to know the name of the periods that I define in my application.

In this case I mention only the user, role and user_role tables, but there are many more tables that have periods.

The intention of the period table is to act as a labeling system for the different periods that exist in the tables of my model. This table is not related in any way by any foreign key with other tables. To do the searches and relate other tables to this, a join is made using dates from and dates to.

For example, a tuple of the user_role table can have a date from and a date until that covers several periods defined in the period table, so it would be labeled with the names of the periods it comprises.

Is it okay for the period table to be an unrelated table? It only serves to give names to the periods, that's why I put it this way. Can you think of another better way to raise it? Should the period table be related to the other tables in some other way?

Thanks in advance.


texts:
I'm creating an index in a table with the following command:

CREATE INDEX IDX_CVE_OBS ON FACTF01 (CVE_OBS);

But it returns me the following error:

this operation is not defined for system tables. unsuccessful metadata update index IDX_CVE_OBS already exists

I have checked and the index name doesn't exists on that table. does the index name have to be unique across all the database? I'm not sure if the index name already exists on another table.


texts: What is the meaning of this operator: *=

texts:
I have a very old Access application that uses SQL Pass-Through Queries.  This application has been nursed along through multiple server upgrades, and we recently moved up to SQL 2012.

I now get a syntax error.  The last two lines of the code are where the problem is.  I believe it is some kind of JOIN, but I've never seen this syntax.  Removing the "*" allows the query to run, but leaves out data.  Is there a easy replacement of this syntax or do I need to redesign these queries using the JOIN phrasing?

SELECT survey.hole,survey_date,pit,cu,rec,zn, mn,north,east,elevation,
       rtrim(min_type) min_type,rtrim(rock_type) rock_type,
       rtrim(alt_type) alt_type, rtrim(min_style) min_style
FROM survey,assay,geology
WHERE  mined_out IS NULL
AND pit = (SELECT fmpit FROM tyrctl WHERE source = 'export by date')
AND  (survey_date >= (SELECT fmdate FROM tyrctl WHERE source = 'export by date')
AND survey_date <= (SELECT todate FROM tyrctl WHERE source = 'export by date'))
AND survey.hole *= assay.hole
AND survey.hole *= geology.hole


texts: MySQL 5.5 to 5.6 Upgrade. Will data remain the same?

texts:
We are considering to upgrade to MySQL 5.6 this summer. I would like to know if our data will be changed in any way once we perform the upgrade?

I've been reading and I found that mysql_upgrade upgrades the system tables only. I tested it and it seems to be true. But I just want to make sure that our data will remain the same after the upgrade. I don't want to have problems with our apps after we do the upgrade. Thanks!


texts: External Table: VARCHAR in Create but CHAR in Access Parameters

texts:
Consider this SO Question UTF-8 And External Tables.
A proposed solution contains VARCHAR2 columns in the CREATE section but those same columns get CHAR attributes in the ACCESS PARAMETERS section.

Why the difference?


texts: How to speed up MongoDB chunk moving between shards

texts:
On a MongoDB 3.6 in a replica set and WiredTiger engine we have a large collection with 7.7TB of data. We started to see severe performance issues (extreme CPU usage and slowdown of data replication) which we somehow solved by temporary using a new empty database. The data is basically usage logs and DB pattern is that mostly there are inserts (of new usage data) and occasionally data reads for certain users or reports.

To scale the system and solve performance issues in the long run, we now created two shards where the idea is to have a fast shard (with data of recent 2 months) and a slow shard (with all historical data). Fast shard run on n1-standard-8 (8 vCPUs, 30 GB memory) machines with SSD disks, while slow shard is on n1-highmem-2 (2 vCPUs, 13 GB memory) machines with rotational disks.

The database is not under any load (there are no new usage log inserts) and basically does nothing. The sharding process has started, but the speed of moving chunks is slow and will take around 40 days to move all the chunks. There are 200000 chunks left to be moved from fast to slow shard with average chunk size 32MB and it takes about 18 seconds to move one chunk.

What can be done to speed up this chunk migration process?

UPDATE

I have learned that if source shard is on SSD disks, then the chunk moving has almost double speed.


texts: Query "all of" across many-to-many relation

texts:
Imagine a setup of three tables, User, Group, and UserGroup, where UserGroup consists of simple a foreign key to each of User and Group tables.

User
----
id
name

Group
-----
id
name

UserGroup
---------
user_id
group_id

Now, I want to write a query selecting all users that are in all of some specified groups. e.g. Select * from users where the user is part of every one of "group1", "group2", and "group3".

With a Django ORM query, I'd do something like

users = (
    User.objects
    .filter(user_group__group_id=group1.id)
    .filter(user_group__group_id=group2.id)
    .filter(user_group__group_id=group2.id)
)

Which would produce a join for each call to .filter, e.g.

SELECT * FROM users
INNER JOIN user_group g1 ON g1.user_id = id
INNER JOIN user_group g2 ON g2.user_id = id
INNER JOIN user_group g3 ON g3.user_id = id
WHERE g1.group_id = %s
  AND g2.group_id = %s
  AND g3.group_id = %s

This becomes a bit hairy if I were to query a bigger set to match by.

So what is a better way to do this? If I were to ask for "any" rather that "all", if would be a simple matter of

SELECT * FROM users
INNER JOIN user_group g1 ON g1.user_id = id
WHERE g1.group_id in %s

But that is not what I need.

A small note: My specific environment is on Postgres, so no fancy MSSql thing will help me here. Preferably, the answer should be general enough to use in any SQL flavour.


texts: Create view with group by and extra fields

texts:
I had a hard time putting the question title together but essentially I want to create a view like this:

ALTER VIEW vPlaceStatistics
AS
SELECT COUNT (TypeID) as Count, TypeID
FROM PlaceStatistics
GROUP BY TypeID

...which works great, but there is a Date field (mydatefield) on the table that I want to be able to use as a filter in my view.

If include mydatefield in my view then it creates record for each date whereas I want it to create one for each TypeID.

Essentially, I want my view to have access to the date field without it being part of the group by, if that is the right way to do it.


texts: SQL Server Agent is not ending some jobs correctly

texts:
we observed a strange behaviour after migrating our datawarehouse from SQL Server 2012 to SQL Server 2019. Our Agent runs most of the jobs correctly after the migration. However there are a few were the single steps are getting marked as successful but the overall job is still marked as running. The job itself is also not getting restarted again based on our schedule. So the job is caught in that status. Of course we can try to build up a new agent job for the affected ones but would be interesting to know if someone had similar experience or knows were this behaviour is coming from.

We have currently SQL Server 2019 Enterprise Edition CU1 running. Agent XPs are set to 1.

Any clue what this is causing?


texts:
My problem is pivoting one single Date column into my row headings while all my current Column headings become rows.

My Column headings are [Date], which contains 50 years of days (from an awesome link provided by scsimon), and the other columns are vehicle license plates.

What I learned, from Mitch Wheat, is that using dates as individual columns, I am designing my table poorly. A google search result told me there is a hard limit on columns.

So I am back here now, after trial and error, left wanting.

My code...

SELECT [Date],[858-456],[128-5045],[111-7090],[111-7091],[111-7092],[111-7093],[133-1977],[133-1978],[133-1979],[133-9693],[113-9678],[133-1996],[115-2776],[115-2777],[128-5095]
FROM DateDimension
WHERE Date BETWEEN '1/19/2019' AND '1/25/2019'
pivot
(
FOR Date BETWEEN '1/19/2019' AND '1/25/2019';
)piv

My error message results in
"Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'pivot'."

I really hope my question is understandable. I found plenty of tutorials, and questions, on pivoting tables that contain only small amounts of data and targeting that. My table contains 50 years worth of dates.

Now for my question. What am I doing wrong when it comes to switching all my date column to the row headers and all my column headers to the first column?

Edit: All column titles: Date, 858-456, 128-5045 111-7090, 111-7091, 111-7092, 111-7093, 133-1977, 133-1978, 133-1979, 133-9693, 113-9678, 133-1996, 115-2776, 115-2777, 128-5095

The Between part is due to only wanting to view a specific range of dates for all of the vehicles (license plates).


texts:
SQL Server 2008 R2, an update corrupted my table somehow, likely to do with the storage subsystem. I am on a dev database, so aren't overly concerned about losing data, it isn't business critical. But I am writing a set of queries which will eventually be used on prod and the corruption happened in the middle. As I don't want to lose the previous changes (they took a while to complete), I decide I can run DBCC CHECKTABLE with the REPAIR_REBUILD option. Sure, restore from backup would normally happen on prod system, but I am on dev here and backups are virtually non existent. Anyway...

According to the spec here, this should be no problem, data will not be lost. It does state "Use as a last resort", but this appears to be mostly for constraint reasons. My table has zero constraints so it seems like this doens't apply here.  But just to be safe, I wrap the statement in a transaction, ready for rollback.

I set the database to single user mode, then run the DBCC statement. A few queries work, data looks about right, so I committed the transaction.

However, on further investigation, the statement has done as it states - there is no data loss. But it has ADDED rows to the table. The rows are identical to existing rows and will require a delete to remove them. As fas as I can see, this is not stated in the msdn article above.

For me, this counts as data loss - I haven't technically lost rows, but particular rows have been altered from their original values. Namely - rows that did not exist before do now. Again, this isn't technically loss but it does constitute a change to the data which will cause queries to return different values (imagine SUM or COUNT queries).

Does anyone have any ideas how or why SQL Server would decide adding additional rows are fine?


texts: Great slowdown of Django object list retrieval after moving from sqlite3 to mySQL on Amazon RDS

texts:
Be warned, there will be some Python programming stuff; my problem is tightly connected to database engine differences though - or at least I think so.

tl;dr: Querying for list of items and all photos connected to them takes a second or two on sqlite, a minute or two on mySQL on Amazon RDS. What's the typical overhead of using remote database solution?

My application stores multiple (and variable number of) images per product. Obvious solution is one-to-many relationship between product and its images. The Django models look like this:

class Product(models.Model):
    (other stuff here...)

class ProductImage(models.Model):
    product = models.ForeignKey(Product)
    url = models.URLField()

( index is generated for all key fields automatically )

In application, I need to send over a partial list of products as JSON. It looks like this:

data = Product.objects.filter(filtering criteria go there)
data_json = [ item.to_dict() for item in data ]

Which in turn calls to_dict function defined in my model:

def to_dict(self):
    images = self.productimage_set.all()
    imagelist = []
    for image in images:
        imagelist.append(image.url)

    return {
        (other stuff...)
        'images': imagelist
           }

So, for every item retrieved, I'm asking for images connected to it. While on sqlite3, this process was pretty much instantenous. After moving to Amazon's RDS, the filtering part takes 2-5s, the 'imagelist' generation up to two minutes. Any ideas what's happening?


texts:
I'm advising a small non-tech company that outsources their software, hardware, and support services to a local IT provider. This company is preparing to migrate several of their internal applications from an in-house managed server to a cloud-based provider.

One of these applications is a business-critical MS Access-based inventory management tool. This tool is unfortunately built and maintained by one person located overseas, so we expect very little support for any custom development and have to treat the application as a black box.

Sales employees are currently accessing the in-house Access application via VPN and virtual terminals, connections to which have been EXTREMELY unstable. Additionally, the application can hang for up to half an hour intermittently, which has gotten worse over the 2 years they have been using it. One of the primary goals of this cloud migration are to solve these performance/stability issues.

Final note: their IT provider is looking to use Anchor as a cloud vendor.

My question: How can we best resolve these performance issues by moving "to the cloud?"


texts: Is it possible to use OAUTH2 in MS SQL Server

texts:
I am using msdb.sp_senddbmail procedure to send emails from MS SQL Server but now I need to move to OAUTH2.
I tried to implement separate application and call it from procedures using xp_cmdshell but there are some limitations like argument character number limit and I can't pass HTML from sql to app easy so I am still trying to find best solution for this.
Is it possible to set somehow sp_senddbmail to use OAUTH2?


texts: Replicating multiple databases into one database

texts:
I would like to replicate multiple databases on different servers into one master database.

I have 5 different instances on separate servers all running SQL 2008 R2. The schemas and structure are all the same but the data is different. I need to replicate all the data into one database on a single instance.
What would the best way going forward?

There's about 132 tables which need to be replicated. I tried creating publishers on each server and subscribing them to a single database on one instance but with no success: data is being over written and some servers are not replicating.

I probably have the wrong approach or messed up something along the way. Your assistance will be highly appreciated.


texts: SQL database disaster recovery in a second datacenter using storage

texts:
I have the following:

Data Center (1) has a set of two Microsoft SQL Server databases. They are clustered using MSCS and shared NetApp storage FAS3250 system. This provides me with local fail-over / redundancy. I want now to extend this to a second data center, let’s call it Data Center (2). DC# 1 and DC# 2 and connected to a flat high speed network, so we can share the same IP subnet between the two data centers.  In DC# 2 there is another Microsoft SQL Server database connected to another NetApp storage FAS3250 system.  Here are my questions:

I wanted DC# 2 to be my disaster recovery for the SQL Server database.
I wanted the solution to be based on NetApp solution using SQL standard
Need to make sure any ODBC connections and client connections are now migrated to the SQL database server in DC#2

Any thoughts or ideas to make this happen?  Thanks,

Skaher


texts: Quickly updating table from large tsv, retaining a timestamp

texts:
Consider this tsv file with millions of rows, where uid is a unique identifier of any possible value.

  1  audi    a3      blue
 42  ferrari f60     red
 23  porsche 911     green

It should be used to initially create a MySQL table, and then update it periodically:

imported | uid | type   | model | color
---------+-----+--------+-------+------
monday       1   audi     a3      blue
monday      42   ferrari  f40     red
monday      23   porsche  911     green

Now, observe the following requirements:

If uid exists, the row should be updated, except imported, which should be retained.
If uid doesn't exist, new values should be inserted, and imported set to current timestamp.

Thus, the table might look like this later:

imported | uid | type   | model | color
---------+-----+--------+-------+-------
monday       1   audi     a3      blue
monday      42   ferrari  f60     white
monday      23   porsche  911     purple
thursday     6   bugatti  veyron  yellow

In other words, the procedure should update the table, but keep a distinction of when a row was imported for the first time. Doing this programatically is easy, but will be slow.

So how can I do this using only mysqlimport, load data infile or a similar very fast method? Using --delete and --ignore do not seem to offer a way to achieve the desired effect.


texts: best way to implement user with trial subscription

texts:
We have an old database implementation, in PostgreSQL, where users are dependent to an institution subscription, this is based on the renewal_date field of the institution table. Once this has expired users belong to an institution will no longer able to access our Saas webapp. See the image below:

Now, we wanted to implement a registration functionality where users are able to register whether their institution has a subscription to our tool. For this, they will be given a 30-day free trial to our tool. So now what I did is to separate the fields that I think can be placed to their own table. See the image below:

After doing some clean up, I realise it would be redundant to implement an expiry_date field in the Account table to store the dates of each user and use this to know when their subscription will end knowing there is an existing renewal_date that we are using to check that. So this is a challenge to me because not every user that will signup has their institution already subscribe to our Saas webapp. Any great suggestion over this problem will be appreciated.


texts: Get the 2nd or 3rd occurrence of a value in a delimited string


texts: Where Database Alerts & Operators Exist In SQL Server?

texts: My SQL Server database server has crashed. I have system databases backup. I will reinstall the sql server but i don't know how to find my operators, alerts and config settings. Is there a way to find it? I have master, model and msdb system backups.


texts:
I have following issue, I have hierarchy table where every record has level, code and description and I need to have description to look as it is was one column.

Example code

CREATE TABLE #tmp (Value NVARCHAR(255))
INSERT INTO #tmp (Value) VALUES ('L1 [ABC]'),('    L2 [DEFGH]'),('        L3 [E]')

SELECT
Value + SPACE(50 - DATALENGTH(Value)) + 'Description text'
FROM #tmp

Output

But i need to have description to start at red line for every hierarchy record.
Any idea ?


texts: Only first column in ORDER BY clause is sorted

texts:
It seems like I've misunderstood the concept of 'Order by'.

I have a table with this structure and data:

CREATE TABLE TestTest (Value1 Int, Value2 Int);

INSERT INTO TestTest VALUES
(1, 10),
(2, 9),
(3, 8),
(4, 7),
(5, 6),
(6, 5),
(7, 4),
(8, 3),
(9, 2),
(10, 1)
;

With the query below:

Select Value1 , Value2
from TestTest
order by Value1 desc,Value2 desc

I expect both columns Value1 and Value2 from 10 to 1 because I use DESC for both columns.

But I see this output:

Why isn't Value2 also in descending order?


texts: MySQL: unique combination of two columns

texts:
This is my friends table

userid(int) | friendid(int) | confirmed(tiny int)
--------------------
1 | 2 | 1
2 | 1 | 1
1 | 3 | 1
3 | 1 | 1
1 | 5 | 0
2 | 4 | 1
4 | 2 | 1
4 | 1 | 1
1 | 4 | 1

I have about 200k rows like this.

I want to delete duplicates. For example if userid=1 and friendid=2 then I don't want the row userid=2, friendid=1.

I want unique combinations across the two columns. This means if (1, 2) is there then (2, 1) should not be there and should be deleted.

Then I can make a friend list query like this

select * from friends where (userid=1 or friendid=1) and confirmed=1

My question is how to delete these duplicate rows?

Please also help me understand how to find the mutual friends of 2 users (userid of them may reside in either userid column or friendid in friends table).


texts: Options for keeping two SQL Server Compact databases in sync?

texts:
Developer here. I have a system where there is a front machine creating rows in a moderately complex database, and a back machine that must sync with the front machine to run reports.  Both run SQL Server Compact databases.

Currently, we pull (and back up) the database to the back. But now it takes too long due to network latency and database size, so we are evaluating our options. One of them is to add a timestamp/rowversion column to each table. We can then just pull updated/inserted records and apply them accordingly.

Is using the timestamp to grab all new/updated records from the front to update/add them to the back a viable solution? Will the timestamps stay in sync, or will the back get new timestamps when the records are applied?

Or is there another option I am not aware of?


texts: How to display all the dates between multiple two dates in a table?

texts:
I want to display all the dates between two dates for a particular record

And this is the table :

ID Start_Date  End_Date
-------------------------
1  2013-01-14  2013-01-18
2  2013-02-01  2013-02-04

and now i want to get all the dates between from date and to date.

Expected output

ID Date
-------------
1  2013-01-14
1  2013-01-15
1  2013-01-16
1  2013-01-17
1  2013-01-18
2  2013-02-01
2  2013-02-02
2  2013-02-03
2  2013-02-04

guide me to write query for that with out creating any extra tables.

I have already tried this following query

select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2012-02-10' and '2012-02-15'

its working fine for single record. but i want get all the date intervals from my table

Updated

i have 6 qty of chairs for all days. So one user book 3 chars on 2013-01-14 to 2013-01-18 another person book 2 chars on 2013-01-17 to 2013-01-20. So my expected output shown below.

ID Date        Available
------------------------
1  2013-01-14          3
1  2013-01-15          3
1  2013-01-16          3
1  2013-01-17          5
1  2013-01-18          5
1  2013-01-19          2
1  2013-01-20          2
1  2013-01-21          2


texts: MySQL cluster replication: Slave cluster should not store any data except data replicated from master cluster?

texts:
I am researching MySQL cluster replication. I read a section "We assume that the slave server or cluster is dedicated to replication of the master, and that no other data is being stored on it" in MySQL cluster manual. According to the section, slave cluster should not store any data except data replicated from master cluster.

I want to know for sure because I have an intention to deploy two cluster that a SQL node are both master and slave.
Example: I have two clusters with SQL node A belongs to cluster a and SQL node B belongs to cluster b

SQL node A --- master of node B and also slave of node B  ---> SQL node B

SQL node B <--- master of node A and also slave of node B ---> SQL node A

I think an bidirectional replication channel help me have two cluster for backup and scale out read/write (It allow web application to be read/write to two SQL nodes)

I am wondering the model is feasible ?


texts: Ensure integrity of other columns from foreign record

texts:
I have 3 tables:

Race
 - id PK

Racer
 - id PK
 - rank

Winner
 - id PK
 - race_id FK(Race.id)
 - racer_id FK(Racer.id)
 - rank
 - prize
 UNIQUE CONSTRAINT (race_id, rank)

Racer.rank is a non-unique values, as some players can tie to a rank. I try to have records in Winner to have its rank match the Racer.rank that it references to. The unique constraint is to ensure that each race has only one 1st place.

The integrity between Winner.rank and Racer.rank is to enforcing if we later update Racer.rank, we will have to update the value in Winner as well, or remove that Winner row. How can I enforce that matching?

Other separate question:
- Is there a keyword that describe what I try to accomplish? I try to search with "referential integrity", "foreign constraint non-unique"... but none gets me to satified result.


texts: Db2 REORGCHK returns the SQL2310N (Error "-668") message

texts:
I have a Db2 database on my Linux laptop. The database is not heavily loaded. I access it from time to time for the development purposes.

I removed a column:

db2 "alter table SCHEMA.TABLE drop column COLUMN"

Then, I run REORGCHK:

db2 "reorgchk update statistics on table all"

and got the following message:

Doing RUNSTATS ....

SQL2310N  The utility could not generate statistics.  Error "-668" was
returned.

However, if I do not ask REORGCHK to update statistics, the command succeeds:

db2 "reorgchk current statistics on table all"

Further investigation shows that RUNSTATS on the altered table:

db2 "runstats on table SCHEMA.TABLE for indexes all"

fails with the same error as above (i.e., SQL2310N, -668).

I tried to increse values of same parameters (LOGFILSIZ, LOGPRIMARY, LOGSECOND, and STAT_HEAP_SZ), but that did not help.

What could be the reason for the error? How to debug it? How could I fix it?


texts: Row lock contention issue with large transactions

texts:
I have a situation where we are acquiring lock on an object from database using SELECT FOR UPDATE. This is necessary for us to insert and delete records from multiple tables in an orderly fashion. The functionality works something like this.

Login -> Acquire lock on unique lock object and insert records to multiple tables and release lock -> Logout -> Acquire lock on same unique lock object and delete records from multiple tables and release lock.

We have synchronization enabled to track users have logged in before logging him out. It is taken care in Java code. However we obtain another lock at database level to make sure the database transactions are synchronized when large number of users are logging in.

Problem: The whole system works perfectly in multi-clustered servers and singleton servers. However, when the number of concurrent users reaches 4000+, we are facing row lock contention (Mode 6) in the database. And few users are not able to login.

Objective: To fix the locking mechanism to enable users to login and logout successfully.

Things tried so far: Added NOWAIT and SKIP LOCKED to SELECT FOR UPDATE query. This doesn't solve my problem because the first one simply throws an error and the second one basically skips the lock which would affect synchronization.

Need suggestions and opinions from Database experts to resolve this issue. TIA.


texts: Oracle unable to extend index but I have many space free

texts:
I have application using Oracle database, and when I'm trying to call my pl/sql function which inserts many rows in tables I get an error:

unable to extend index MY_INDEX by 128 in tablespace MY_TABLESPACE

I use this query to get free space in my tablespace:

select round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space
where tablespace_name ='MY_TABLESPACE';

And it returns me that I have about 40gb free.
What is the problem?

df -h

on my server says me that I have few Tb left, so it's not the problem either.

Result of this query is :

select * from dba_ts_quotas

TABLESPACE_NAME USERNAME            BYTES       MAX_BYTES BLOCKS MAX_BLOCKS DROPPED
TOOLS          PERFSTAT             3177054208  -1        387824 -1     NO
SYSAUX         APPQOSSYS            262144      -1        32     -1     NO
MY_TABLESPACE  MY_USER              7035027456  -1        858768 -1     NO
SYSAUX         AUDSYS               233963520   -1        28560  -1     NO
SYSAUX         GSMADMIN_INTERNAL    2293760     104857600 280    12800  NO
SYSAUX         FLOWS_FILES          524288      -1        64     -1     NO
MY_DATA        MY_DATA_USER         4343726080  -1        530240 -1     NO


texts: VS 2019 to SQL 2019 deployment issue - SELECT permission was denied on the object 'server_principals'

texts:
Good day,
We are currently facing an issue with SSIS deployment from Visual Studio 2019 to SQL Server 2019. Using the Visual Studio "Integration Services Deployment Wizard" to deploy even a blank project, results in the following error: The SELECT permission was denied on the object 'server_principals', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Screenshot:
I use my AD account (Windows Authentication) to deploy from VS 2019. My AD account has db_owner access across the server and databases. We have also attempted to deploy the dtsx file from SSMS 2019 itself which also results in the same error
I am struggling to find any online resources which specifically make reference to the SELECT issue on "Server_principals"
Has anyone perhaps experienced something like this or can anyone please advise where the issue could lie or where to troubleshoot next?
Thanks in advance


texts: It's possible optimize query with only index scan?

texts:
I have the following tables:

A: (id_a, field1, field2) with 32M rows
B: (id_b, id_a, field3, field4) with 3M rows

All columns are indexed, except b.field4. A.id_a and b.id_b are PK.

And the following query:

SELECT B.field4
FROM A
    INNER JOIN B using (id_a)
WHERE (A.field1 = 123 OR A.field2 = 123) AND B.field3 = 1234
LIMIT 1;

I have 48k rows on table A that match with the condition A.field1 = 123 OR A.field2 = 123) and 1.8k on table B that match with B.field3 = 1234P.

The number of rows returned by the query without the limit is 74.

Explain shows that 1.8k rows will be analyzed.

The question is: can this query be optimized? I was assigned to a task to improve it.
The only thing I think I can do is add field1 to table B and remove the OR field2 = 123 part.


texts: Materialized view bigger than table it is based on?

texts:
I have a database that's ~130 GB and one table makes up for most of this (85 GB). Because this table holds a lot of duplicates, we have a materialized view which basically uses a window function to only select the newest rows.

The original table holds 85 mio. records and the materialized view is expected to have approximately 15 mio. records. So I would expect about 15 GB of disk usage. There are no additional attributes in the view, that are not already in the table.

However, when I invoke REFRESH MATERIALIZED VIEW it fails with "not enough disk space", even though there are 160 GB free. I checked and it really writes that much (and then rolls back).

Why would I need that much space? 160 GB is almost twice of the original table, although it should contain only 1/5 of the data.

I am running Postgresql 10.6.


texts:
I have to debug a performance issue on a SQL Server 2016 database. The query was pretty simple - a few temporary tables joined with SQL tables and aggregation. The amount of rows of involved in temporary tables was really small (several tables, hundreds of rows).
I have found that one of the tables PK has 12% index fragmentation. If the table was removed from the query it runs faster. I rebuilt the index and everything was fine.
I believe there must be something wrong with the settings of the SQL Server 2016 instance, because the query is running perfectly fine on SQL Server 2014. The only difference I have found in the settings was in the maintenance plans - the pictures below shows the plans:
SQL Server 2016

SQL Server 2014

I guess there is something not set correctly in the Index Stats Options, which is causing statistics not to be recalculated. Can anyone tell me if am I am right (as I am not DBA) or say if the plan looks OK, so I can search for the issue in another place?


texts: Configuring SSIS,SSRS, and SQL Server - three server model

texts:
I have a requirement for a third party vendor application which requires SSIS and SSRS. The vendor had suggested this 3 server model, and my management is going ahead with the same. Three VMs are being provisioned, one each for SQL Server, SSIS, and SSRS.

I want to know in what ways I can configure SSIS and SSRS to work with the SQL Server. Also, what would be the best way to create SSIS packages? Because, if the packages have to be created on the dedicated instance, I still need to create a local instance of the database engine on the SSIS server.

I need some guidance as to how I can configure SSIS and SSRS in conjunction with the SQL Server instance in the third VM. I am fairly new to these two areas. I am going through these two links:

Configuring SSIS
Configuring SSRS

Please let me know if another URL would provide detailed step by step information.

Also, would the three servers have to be in the same cluster?

The system is spread across continents hence we expect a lot of users in the long term. The way it was planned was to have separate instances of SSIS and SSRS, and they point to a database in an Always On setup which stores the reporting information.


texts: Get The Latest values For Same ID/Duplicate ID

texts: ----------------I Want To Show Only Red marked product_id,on_hand,created_at, In this image Product_id have same Duplicate id but i want only last entry or latest product_id information and Show if last entry value is zero .


texts: How do you grant execute permission for a single stored procedure?

texts:
Normally when I create a stored procedure I use the following as a template of sort

Create procedure <procedurename>
<@param1 <type>, @param2 <type>, etc..>
as begin

 <procedure>

end

Is there a way to include granting execute permission on only that stored procedure while I'm at it?
For instance like ...

Grant execute [User_Execute]

... but only for this stored procedure?

I've seen some other similar questions but they seem to all refer to ALL of the stored procedures and not just one, nor have I seen one where you can specify permissions inside of the create procedure script.  Even answers about how I can set permissions without the GUI for specific stored procedures would be welcome.

Edit
The top answer certainly pointed me in the right direction, this is is essentially what i was looking for, I didn't think about batching the commands which is what I ended up doing, batching the command along with my stored procedure.  Anyway, I think it's pretty slick.

Create procedure <procedurename>
<@param1 <type>, @param2 <type>, etc..>
as begin

<procedure>

end
GO
GRANT EXECUTE ON <procedurename> to <username>
GO


texts: Server audit with more than 1.000 entries

texts:
On my Mssql 2008r2 I created a server audit for all login and logout:

USE MASTER
GO

CREATE SERVER AUDIT audit_test
TO FILE (FILEPATH = 'D:\Audit')
GO

CREATE SERVER AUDIT SPECIFICATION audit_test_spec
FOR SERVER AUDIT [audit_test]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP)
WITH (STATE=ON)
GO

For maximum file space I have set 1024 MB, more than enough.

However, when from the SQL Server Management Studio I go to security/audits/audit_test/view audit logs it only displays exactly the latest 1.000 rows. On the other hand, the file produced by the audit is 317 MB, so I guess it has more than 1.000 rows.

I even tried setting a filter, but it did not help: no line appear outside the latest 1.000.

How do I read the audit log file?


texts: Best method to give two Oracle databases on the same server access to common data

texts:
With a production and a test database on the same server, how should access be provided to some common quarterly update tables for both databases?

It is possible to put these tables into a third database, let us call it catalogs, but then  the tables within catalogs can't be accessed as efficiently as if the tables where directly in production or test. On the other hand, space is saved, as there is only one copy of the data.

I see the following two options

add a user and schema for catalog to
both database instances. That gives efficient access, but
duplicates space requirements and work to maintain the catalogs
add a third database instance for
catalogs and access this via database
links. This saves space, but what about performance.

Which of these solution is better?

Edit:

Some further information:
Catalog has about 60 tables using less than 1GB tablespace.
There are functions to be defined in the katalog schema and indexing is important.


texts: SQL Server Full txt Search - Thesaurus Entries

texts:
I'm thinking of adding a full text search to our product (currently using SQL 2005 Enterprise), so just doing some preliminary research.

I read that you can add thesaurus entries, so "Internet Explorer" would also have related entries such as "IE", "IE7" and so on.

Would it also work in reverse, so if I enter "IE7" would it find "Internet Explorer"?


texts: Grant postgres superuser to linux account

texts:
On my development (Ubuntu linux) laptop I have a postgres OS account, which owns the Postgres installation.

When I want to perform any postgres activities, create/drop databases etc, I must first su to the postgres account.

$ sudo su postgres

How can I alter my own OS account to have the OS-level privilages of postgres, so I will not need to su ?


texts: Data type for storing an array of flags (a bitmap/bit array)

texts:
I need to store a bit array for each record of a table, supporting the following operations:

Testing if a bit is set, and setting a bit (using SQL)
Querying and setting the value using ADO 2.8 (not ADO.NET)
Indexing (in order to benefit from the "covering index" feature)

The maximum number of bits to be stored in this array is fixed, but may exceed 32. That is, a simple int column doesn't always work.

From what I've seen so far, my options are:

Use several int columns
Use bigint (works as long as the number of bits is <= 64)
Use binary
?

The first option would work, but require quite a bit of refactoring in the code that accesses the data. The second option is a temporary relief only, and from my searches so far I'm not too sure if ADO works that well with bigint. I have no experience with binary, and I'm not aware of any other options.

Which data type would you choose, given the requirements?


texts:
At currently I am designing an inventory system for internal use and I have come across an interesting scenario when adding tables for creating the devices. Currently I have tables for Device, DeviceMake, DeviceModel and DeviceCategory. I am stuck now as to how I should define the relationship between these tables. I was  thinking of using them as orphan tables but I am not sure if in the long run that will prove problematic, versus having some sort of many to many relationships or otherwise. Each device should belong to a category, each device should have a Make and a Model should be found under a specific Make. For example,

Device Category [ Laptop, Mouse, Keyboard, etc...]
Make [ Samsung, HP, Dell, Logitech, etc...]
Model [ S9, Prodesk, XPS1200 ]

public class AssetCategory
{
    [Key]
    public int AssetCategoryId { get; set; }

    public string Name { get; set; }

    public ICollection<Asset> Assets { get; set; }
}

public class Asset
{
    [Key]
    public int AssetId { get; set; }

    [DataType(DataType.DateTime)]
    public DateTime DateAdded { get; set; }

    [Display(Name ="Assignment Status")]
    public AssignedStatus AssignmentStatus { get; set; }

    [Required]
    [Display(Name = "Serial Number")]
    public string SerialNumber { get; set; }

    [Required]
    public AssetCondition Condition { get; set; }

    public double Price { get; set; }

    [Display(Name = "Device Make")]
    public int? AssetMakeId { get; set; }
    public AssetMake AssetMake { get; set; }

    [Display(Name = "Device Model")]
    public int? AssetModelId { get; set; }
    public AssetModel AssetModel { get; set; }

    [Required]
    [Display(Name = "Device Category")]
    public int AssetCategoryId { get; set; }
    public AssetCategory AssetCategory { get; set; }

    public string Remarks { get; set; }

    //Mappings
    public virtual ICollection<AssetHistory> AssetHistory {get;set; }
    public virtual ICollection<AssetAssignments> AssetAssignments {get;set; }

    public enum AssetCondition
    {
        Good = 1,
        Dead = 2,
        Damaged = 3,
        Repairing = 4,
    }

    public enum AssignedStatus
    {
        Assigned = 2,
        Unassigned = 1
    }
}

public class AssetModel
{
    [Key]
    public int AssetModelId { get; set; }

    public string Name { get; set; }

    //public int AssetMakeId { get; set; }
    //public AssetMake AssetMake { get; set; }

    public ICollection<Asset> Assets { get; set; }
}

public class AssetMake
{
    [Key]
    public int AssetMakeId { get; set; }

    [Required]
    [MinLength(3,ErrorMessage = "Too short")]
    [MaxLength(300,ErrorMessage = "Too long")]
    public string Name { get; set; }

    //public int AssetModelId { get; set; }
    //public AssetModel AssetModel { get; set; }

    //public ICollection<AssetModel> Models { get; set; }
    public ICollection<Asset> Assets { get; set; }
}

What is the recommended way to represent this relationship in SQL?


texts:
I am interested in learning a DBMS. As a beginner which DBMS should i go for ?

I know few DBMS by name like Oracle,Sql Server,My Sql  .  How are these DBMS different from each other ?

As a beginner which DBMS should i go for ? I needed to learn DBMS as i am working with JavaEE and had to link the project with a database.