texts: How to create an index to improve this query

texts:
I am trying to improve the speed of the following query:

SELECT COUNT(*) AS count,
       SUM(amount) AS total,
       AVG(case when service_years >= 30 then amount end) AS average,
       employer
FROM pensions_pension
WHERE agency_id = 4
GROUP BY employer
ORDER BY count DESC NULLS LAST;

Table structure:

id: integer (primary key)
name: text
slug: varchar(50)
title: text
employer: text
pension: numeric(10,2)
benefits: numeric(10,2)
disability: numeric(10,2)
amount: numeric(10,2)
service_years: numeric(10,2)
retirement_year: smallint
year: smallint
agency_id: integer (foreign key)
rank: smallint
note_text: text

Existing index definitions:

CREATE UNIQUE INDEX pensions_pension_pkey ON public.pensions_pension USING btree (id)
CREATE INDEX employer ON public.pensions_pension USING btree (employer)
CREATE INDEX pensions_pension_agency_id ON public.pensions_pension USING btree (agency_id)
CREATE INDEX pensions_pension_amount ON public.pensions_pension USING btree (amount DESC)
CREATE INDEX pensions_pension_employer ON public.pensions_pension USING btree (employer)
CREATE INDEX pensions_pension_rank_idx ON public.pensions_pension USING btree (rank, amount DESC)
CREATE INDEX pensions_pension_slug ON public.pensions_pension USING btree (slug)
CREATE INDEX pensions_pension_slug_like ON public.pensions_pension USING btree (slug varchar_pattern_ops)
CREATE INDEX pensions_pension_year ON public.pensions_pension USING btree (year)
CREATE INDEX year_amount ON public.pensions_pension USING btree (year DESC, amount DESC)
CREATE INDEX pensions_pension_year_29d1e58f8ad8ed60_uniq ON public.pensions_pension USING btree (year)
CREATE INDEX idx_pensions_pension_year_agency_id ON public.pensions_pension USING btree (year, agency_id) WHERE ((year <= 2016) AND (year IS NULL))
CREATE INDEX pensions_pension_idx_agency_id ON public.pensions_pension USING btree (agency_id)
CREATE INDEX pensions_pension_partial_agency_id__index ON public.pensions_pension USING btree (agency_id, employer) WHERE (agency_id = 4)

This is the query plan when I run ANALYZE EXPLAIN in postgresql:

Sort  (cost=215259.09..215266.48 rows=2956 width=96) (actual time=9976.097..9976.672 rows=3527 loops=1)
  Sort Key: (count(*)) DESC NULLS LAST
  Sort Method: quicksort  Memory: 551kB
  ->  HashAggregate  (cost=215044.35..215088.69 rows=2956 width=96) (actual time=9968.124..9973.413 rows=3527 loops=1)
        Group Key: employer
        ->  Seq Scan on pensions_pension  (cost=0.00..178968.04 rows=2886105 width=38) (actual time=0.054..5236.262 rows=2942951 loops=1)
              Filter: (agency_id = 4)
              Rows Removed by Filter: 2777772
Planning time: 0.443 ms
Execution time: 9983.167 ms

I tried to add an index on agency_id. I also tried creating a partial index on agency_id by setting it to agency_id=4. But the query planner keeps choosing a seq scan instead of an index scan. I'm fairly new to creating indexes and would like to learn about the process and how I can speed up something like this. I am running Postgresql 9.6.


texts: How can "HierarchyID" type work when "CLR" is disabled?

texts:
The following statement sp_configure 'clr enabled' returns:

name        minimum maximum config_value    run_value
clr enabled 0       1       0               0

I assume this means there are no CLR objects; however if I execute the following statements I get these:

SELECT * FROM sys.assembly_files

SELECT * FROM sys.assembly_types

I have read that The HierarchyID type is available to CLR clients as the SqlHierarchyId data type..

How can it be used without having CLR enabled and why do I need to enable it to create my own CLR objects?


texts: SSIS Catalogue Inaccessible After Server Collation Change

texts:
I changed the collation of a Microsoft SQL 2016 Standard server, by starting SQL server in single user admin mode and using the -q parameter to specify "Latin1_General_CI_AS" collation (previous setting was "SQL_Latin1_General_CP1_CI_AS"). Now when expanding the SSISDB Integration Services Catalogue, I get the below error.

Exception has been thrown by the target of an invocation. (mscorlib)

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot resolve collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in add operator occurring in SELECT statement column 1. (Microsoft SQL Server, Error: 451)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.4001&EvtSrc=MSSQLServer&EvtID=451&LinkId=20476

Everything else on the server seems to be OK, although the Agent XPs advanced setting got disabled, which I have corrected.

Is there a way to correct whatever is causing the collation conflict in the SSIS DB, or is the only solution to delete and recreate it?


texts: Quick nearest neighbor search in the 150-dimensional space

texts:
I want to create a database using any of the possible RDBMS. It will have a table with approximately 150 columns. The objective is to perform nearest neighbor search of some other objects. So it's a NNS in the 150-dimensional space.

I already tried to use some obvious methods like L1 or L2 distances but of course it takes a lot of time for tables with many rows. Also I tried to look at the KD-tree (note I did not test it) and PG-Strom but they are not a good solution for data with a many dimensions.

Can I somehow improve the speed of the described search using math methods (like KD-tree) or tech methods (like PG-Strom)?

I will try to use any RDBMS which allow to improve speed of the NNS. But MySQL and PostgreSQL are the most appropriate DBMS for me.


texts:
I'm trying to run a simple exercise with two docker containers with oracle database 12c (store/oracle/database-enterprise:12.2.0.1). It's pretty straigthforward to run both containers but I haven't been able to connect from one to the other, what am I missing? Both containers are running on the same machine (a MacBook) and both are using the same network, I can reach them with ping

Any help would be very useful

The commands I'm using:

docker container run -d -h localdomain --name odbc -p 1521:1521 -p 5500:5500 -e DB_SID=ORCLCDB -e DB_PDB=ORCLPDB1 -e DB_DOMAIN=localdomain store/oracle/database-enterprise:12.2.0.1-slim

docker container run -d -h localdomain --name odbc2 -p 1522:1521 -p 5501:5500 -e DB_SID=ORCLCDB -e DB_PDB=ORCLPDB1 -e DB_DOMAIN=localdomain store/oracle/database-enterprise:12.2.0.1-slim


texts: NULL queries getting stuck and using up connections

texts:
Running show processlist in phpmyadmin for me often shows queries with an Info of NULL and a Time of apparently a few seconds, but which have in fact been stuck there for a long time (each time I run show processlist the same Id may have a different Time). I have no idea what these queries are or why they get stuck, but they can often cause all my available connections to be used up and stuck on waiting in apache status, causing sites not to load. Is there a setting I can change to terminate queries that are stuck for a long time? Is the php.ini setting max_execution_time responsible for this (I had it set to 3000 from the default 90).

I have attached a screenshot of running show processlist.


texts: MySQL still holding memory after I shut it down?

texts:
I used this command to shutdown MySQL, however it seems the amount of free memory on my box has not changed?

That seems to odd.

/usr/local/webserver/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data0/mysql/mysql.sock shutdown

Does anyone know why the free memory might not change very much after unloading MySQL?


texts: Running a simple logging program, how to diagnose spikes mongod's CPU activity?

texts:
I'm running mongodb 1.4.7 - I've a node server set up which logs incoming requests in a logs collection:

{'u':'user123', 'l':'log data'}

In addition, once the request happens, the server will check if a users collection contains a user123 in this case via the .find({'u': 'user123'})

Running top I see this:

PID USER      PR  NI  VIRT  RES  SHR S  %CPU %MEM    TIME+  COMMAND
4709 mongodb   20   0 1568m 435m 303m R 176.3 11.0 370:22.58 mongod

This, however, only happens every so often, most of the time it sits at about 1-2% CPU and running mongotop shows that mongo isn't particularly stressed:

           mdb.users         1ms         1ms         0ms
           mdb.impressions   1ms         1ms         0ms

I'm getting a decent amount of traffic. What steps can I take to identify the source of resource usage spikes?


texts: Where does it make sense to store Indexes for partitioned tables?

texts: Prior to table partitioning, all the indexes were stored on a separate drive, per this question.  Now that I partitioned a couple of tables, SSMS is offering to store indexes in the partition scheme.  Is this a good idea?  Or should I be storing indexes on a separate drive, like I was before?  What are the pros or cons of either?


texts: MySQL 8.0 appending unknown character to column

texts:
I'm inserting a CSV files into MySQL 8.0 using Python 3.7:

bulk_insert_sql = "LOAD DATA LOCAL INFILE filename.csv INTO TABLE table_name FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' IGNORE 1 LINES"
# Execute the query built above
self._cursor.execute(bulk_insert_sql)

I could not then select items by the last column of the table directly in the MySQL cli or MySQL Workbench such as:

SELECT * FROM table_name WHERE column_name = "123";

I printed the inserted table from the MySQL cli and did not notice anything, but in MySQL WorkBench there is a square character representing an unprintable character after the column value.  I could select it using LIKE '%123%' though.

I used python cli to confirm the length of the value of the last column of a line in the csv file was 17 chars including the '\n' newline character and 16 after stripping the newline character.  The newline should be stripped during the query since the SQL command specifies it as the line delimiter.

I then looked closer at the column value in MySQL cli.  The length was specified as 17 characters (but as mentioned above, could only see the expected 16 characters when priting a row).  I then used the HEX() function in MySQL to print the hex value.  The hex value printed and I converted it using a website HEX to ASCII converter (https://www.rapidtables.com/convert/number/hex-to-ascii.html).

The converted ASCII printed as I expected the column would print if it was normal.

I have removed and re-installed MySQL 8.0 in Centos 7.6.

1) Any ideas about what is going on?
2) What can I do to further understand why that character is there and how to prevent it from being there.

This is a fresh install of Centos 7 and I was inserting bulk data about 15GB worth, and thought maybe I broke MySQL due to the amount of data.  However reinstall did not fix the issue.  Can anyone see anything I am doing that would cause this?

Here is an example table structure however it's happening in multiple tables:

CREATE TABLE IF NOT EXISTS `uspto`.`APPLICATION` (
  `ApplicationID` VARCHAR(20) NOT NULL,
  `PublicationID` VARCHAR(20) DEFAULT NULL,
  `FileDate` DATE DEFAULT NULL,
  `Kind` VARCHAR(2) DEFAULT NULL,
  `USSeriesCode` VARCHAR(2) DEFAULT NULL,
  `AppType` VARCHAR(45) DEFAULT NULL,
  `PublishDate` DATE DEFAULT NULL,
  `Title` VARCHAR(2000) DEFAULT NULL,
  `Abstract` TEXT DEFAULT NULL,
  `ClaimsNum` INT DEFAULT NULL,
  `DrawingsNum` INT DEFAULT NULL,
  `FiguresNum` INT DEFAULT NULL,
  `FileName` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ApplicationID`, `FileName`))
ENGINE = InnoDB;

Pastebin: https://pastebin.com/VwpzJnc0


texts: I am wanting to use UDTT in my database, but we have CLR turned off, and according to the docs for create type, all types require a CLR assembly

texts:
Is this true? Or can I use User defined Table Types with having CLR off? (I.e. they don't require the use of CLR)
If they do require CLR, is there an alternative I can use for my stored procedure parameter that serves the same functionality?


texts: Migrate large (200GB) mysql databases to new hosts with as little downtime as possible

texts:
My question is about migrating large amounts of MySQL data with as little downtime as possible.

I have 3 MySQL databases that need to be moved from one RDS instance inside a VPC into a brand new AWS account into a new RDS instance.

Each database is around 200GB.

I'd like to minimise downtime as much as possible on the 3 sites these databases support.

The sites are very read and write intensive, so unfortunately I can't go into a read-only mode for a few hours while I migrate.
I cannot SSH into and copy the data files for the mysql instances because this is RDS.
It takes around 4-5 hours to run mysqldump for the entire database to my local machine.

What options do I have to be able to do this and how much downtime would you estimate for any ideas?


texts: Reorder partitioned results by specific column

texts:
I have a query where I want to find the top 50 event_dates for each time zone. While I am getting that, it's not currently formatted in a usable manner. I want the results to look something like:

  ID  |  event_date  |  timezone

  1   | 2017-08-07  | Mountain

  2   | 2017-08-08 |    Mountain

  3   | 2017-08-09 |    Mountain

  4   | 2017-08-07 |    Central

  5   | 2017-08-08  | Central

  6   | 2017-08-09 |    Central

  7   | 2017-08-07 |    Eastern

  8   | 2017-08-08 |    Eastern

  9   | 2017-08-09 |       Eastern

  10  | 2017-08-07 |    Western

  11  | 2017-08-08 |    Western

  12  | 2017-08-09 |    Western

Currently, the results are being mixed between the timezones like this:

  ID  |  event_date  |  timezone

  1   | 2017-08-07 | Mountain

  2   | 2017-08-07 |    Central

  3   | 2017-08-07 |    Eastern

  4   | 2017-08-07 |    Western

  5   | 2017-08-08 | Mountain

  6   | 2017-08-08 |    Central

  7   | 2017-08-08 |    Eastern

  8   | 2017-08-08 |    Western

  9   | 2017-08-09 |       Mountain

  10  | 2017-08-09 |    Central

  11  | 2017-08-09 |    Eastern

  12  | 2017-08-09 |    Western

What would I need to do to update the query to output in that manner?

For reference, what I currently have is something like this:

SELECT TOP 200 ID, event_date, timezone FROM ...
ORDER BY ROW_NUMBER() OVER(PARTITION BY timezone ORDER BY timezone), timezone, event_date ASC


texts: Primary filegroup is full SQL Server 2008

texts:
I have a large table (~50 million rows) I'm trying to bulk insert into SQL Server and I get the error:

  Could not allocate space for object 'myDB' in database 'I 3 Stroke' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

There is another table in the database with around 25 million rows.  This database will only be used on a single machine, and it will be designed to mine data that already exists and it under no circumstances will ever grow beyond its current size.

For a situation such as this, what's the best way to tackle this so SQL Server doesn't complain?  Will the solution matter that this DB won't be exposed to multiple users?


texts: Stored procedure arguments get immediately truncated

texts:
I have a stored procedure with a single SELECT statement inside:

DROP PROCEDURE `pos_neg_test`//
CREATE PROCEDURE `pos_neg_test`(IN start_ts TIMESTAMP, IN end_ts TIMESTAMP)
    BEGIN
        SELECT `title_id`, `total`, `pos`, `neg` FROM
        (SELECT
            COUNT(message_id) AS total ,
            title_id
        FROM `messages_has_titles`
        INNER JOIN `messages` USING(message_id)
        WHERE msg_time < FROM_UNIXTIME(end_ts) AND msg_time >= FROM_UNIXTIME(start_ts)
        GROUP BY title_id
        ) total
    LEFT OUTER JOIN
        (SELECT
            COUNT(message_id) AS pos ,
            title_id FROM `messages_has_titles`
        INNER JOIN `messages` USING(message_id)
        WHERE msg_time < FROM_UNIXTIME(end_ts) AND msg_time >= FROM_UNIXTIME(start_ts) AND sentiment > 0
        GROUP BY title_id
        ) pos
    USING (title_id)
    LEFT OUTER JOIN
        (SELECT
            COUNT(message_id) AS neg ,
            title_id FROM `messages_has_titles`
        INNER JOIN `messages` USING(message_id)
        WHERE msg_time < FROM_UNIXTIME(end_ts) AND msg_time >= FROM_UNIXTIME(start_ts) AND sentiment < 0
        GROUP BY title_id
        ) neg
    USING (title_id);
END;

When I call this procedure from the mysql CLI, I got an empty result:

mysql> CALL pos_neg_test(1348142600, 1348143200);
Empty set (0.24 sec)

Query OK, 0 rows affected, 2 warnings (0.24 sec)

SHOW WARNINGS tells me, that my input argument got truncated:

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1265 | Data truncated for column 'start_ts' at row 1 |
| Warning | 1265 | Data truncated for column 'end_ts' at row 1   |
+---------+------+-----------------------------------------------+
0 rows in set (0.00 sec)

What did I do wrong?


texts: optimize mysqldump for approx. 20GB databases for 8GB RAM

texts:
MySQL server having following specifications:

8GB RAM
Data & Index of size 49.64GB
Dump file size is 17.24GB(18512936320 Bytes)
CPU:

Processor: Intel(R) Core(TM) i5-3330 CPU @ 3.00GHz
Architecture:   x86_64
vendor: Intel Corp.
size: 2400MHz
capacity: 2400MHz
width: 64 bits

OS:

Distributor ID:    Ubuntu
Description:   Ubuntu 14.04.5 LTS
Release:   14.04
Codename:  trusty

Free memory(RAM): this may be variable on specific time

         total       used       free     shared    buffers     cached

Mem:          7.7G       5.2G       2.5G        15M        44M       4.6G
-/+ buffers/cache:       597M       7.1G
Swap:         7.9G        61M       7.8G

Database 98% tables are InnoDB and rest of it are MyISAM.

I have used following query:

mysqldump -u root -p --single-transaction --routines --events --triggers --all-databases | gzip > all_databases_backup.sql.gz

I have tried following Global variables :

key_buffer_size = 512M
Innodb_buffer_pool_size = 1024M
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_thread_concurrency = 0

To optimize the backup total time but not only able to reduce the time even 1 min.

With default settings it is taking min : 27mins
With the setting shown above it takes :  26mins

Is there any thing else I have to configure to optimize mysqldump?


texts: How to view security permissions for encryption keys in SQL server

texts: Is there a way to view the security permissions assigned for encryption keys in SQL Server? Furthermore, where are all these keys stored?


texts:
I have a collection in MongoDB with 800M+ records. I wanted to do some cleanup, but the remove operation is really really (unusably) slow - even deleting 1000 records can take 10+ minutes.

The structure of one record in the collection is something like this:

{
     'stID' : '00000001',
     'recordID' : '28-4',
     'date' : ISODate('2017-06-06 20:00:00'),
     ......
}

There is a compound index on {'stID' : 1, 'recordID' : 1, 'date' : 1} and my delete query is like this: db.coll.remove({ 'stID' : '00000001', 'recordID' : '28-4' }, { multi: true });

The database is part of  a replicaSet, with one secondary and one arbiter, and I do understand that the delete operation has to fill the oplog with the _ids of each record to delete, but still I find it hard to understand why this could be so slow, especially since:

there is no considerable system or IO activity on the primary or the secondary
the secondary is not lagging behind the primary

We have never experienced problems with find or write/update operations. Can you suggest what other parameters/commands/tools should I use to see what is going on with the query and the servers.

The storage engine we use is WiredTiger. The server has 128GB of RAM and fast SSD drives in a RAID 1 configuration.


texts: Can't get this query to work (left outer join) Postgres

texts:
I've got this query I'd like to get working. I'm trying to count all the instances of a foreign key after a certain date that isn't null, and then left join that to the distinct of the original table, so I can see the variables with no entries. Here's my query:
select fulllist.fk_fc_id
from
(
    select distinct fk_fc_id
    from data_item
) as fulllist
left outer join
(
    select temp.fk_fc_id, count(*) as number
    from
    (
        select *
        from data_item
        where di_item_value not like 'Null'
            and di_timestamp > '2013-11-01 00:00:00'
    )   as temp
    group by fk_fc_id
)   as lj
    on lj.fc_fk_id=fulllist.fk_fc_id;

the error is

ERROR:  column lj.fc_fk_id does not exist
LINE 19:  on lj.fc_fk_id=fulllist.fk_fc_id;


texts: User with more than enough permission, cannot query collection

texts:
I'm in mongo shell. I'm authenticated as a user with way beyond enough permission to simply query a collection. Yet I cannot. Here is the user permissions...

What am I doing wrong and/or missing?

I'm completely stuck. Thanks for any help :-)


texts: Howto avoid textdata of trace gets truncated?

texts:
I have a trace file including the TEXTDATA column created by a server-side tracing.
Some of the traced queries are very long.

If I re-open the trace file on Profiler, the relevant long query is displayed completely - it has 340 rows of text and 10951 characters.

But after I have imported the trace file into a SQL-Server table, the relevant query seems to be truncated. The table has a ntext column that had been created by the fn_trace_gettable method.

I queried the table using different methods:
The text output in SSMS (with maximized number of characters configured) interrupts the output at line 52.
The file output of SSMS (*.rpt) also truncates, here the query is getting truncated at line 250.

So right now I wonder if it's possible to get the WHOLE query out of the trace file into a SQL-Server table and how?


texts: non-clustered unique indexes on sql server partitions

texts:
SQL Server 2008 requires unique non-clustered indexes created on a partition to include the partition field in the index definition.

I'm wondering why.


texts: Return inserted entity in mysql stored procedure

texts:
I want to have inserted data in stored procedure, because of of sql injection I use prepare and execute statement.
what should I've done to have the inserted data

following code is only a part of user table

DELIMITER ;;
CREATE PROCEDURE `UserCreate`
(
    IN `@Name`      VARCHAR(32)
)
BEGIN
    SET @Query = CONCAT
    (
        'INSERT INTO
            User
            (
                Name
            VALUES
            ('
                , '\'' , `@Name` , '\','
            ')'
    );

    PREPARE Statement FROM @Query;
    EXECUTE Statement;
    DEALLOCATE PREPARE Statement;

END ;;
DELIMITER ;

I use Binary uuid for having more security


texts: Importing data from file with ID dependencies

texts:
I have this little conundrum going on: I want to bulk import historical data from multiple CSV files like binance_btc_usd.csv with structure of time|value into table id|time|value where id refers to the identification of what exchange and what currency pair is going on, which is the file name. I dont want to bake-in an ID into the CSV files, as they may not be valid next time I re-run the script. COPY doesn't seem like the thing I am looking for.

CREATE TABLE history_data (
    id    SERIAL PRIMARY KEY,
    pair  INTEGER NOT NULL,
    time  TIMESTAMP,
    value DOUBLE PRECISION,
    CONSTRAINT pair_pair_id_fk FOREIGN KEY (pair) REFERENCES pair (id) ON DELETE CASCADE
);

CREATE TABLE pair (
    id    SERIAL PRIMARY KEY,
    exchange  INTEGER NOT NULL,
    base_coin INTEGER NOT NULL,
    quoted_coin INTEGER NOT NULL
    # and other ...

    CONSTRAINT exchange_currency_id_fk FOREIGN KEY (exchange) REFERENCES exchange (id) ON DELETE CASCADE
    CONSTRAINT base_coin_id_fk FOREIGN KEY (base_coin) REFERENCES currency (id) ON DELETE CASCADE
    CONSTRAINT quoted_coin_id_fk FOREIGN KEY (quoted_coin) REFERENCES (id) ON DELETE CASCADE
    CONSTRAINT pair_pair_id_fk FOREIGN KEY (pair) REFERENCES pair (id) ON DELETE CASCADE
);


texts:
I am using Sql server 2008 R2. To speed up the query execution in sql i have changed xquery part of it.I Have used cast method to query xml column and i got following results.
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT [col1] as [C Sl No.] ,[Table].[Customer_Name],[sl_no] as [T Sl No.] ,[Name2] as [Coll_Name] ,[Collection_Remarks] as [Coll_Remarks]  FROM [Db].dbo.[Table] WHERE ( [Db].dbo.[Table].[Main_Phone_no] LIKE '%94484%'  Escape '`' OR [xml_column_name].exist('for $x in /Root/Row where( ($x/xml_column_name[fn:contains(upper-case(.),fn:upper-case("94484"))]/text()) )  return $x')>0 OR [Db].dbo.[Table].[KeyPhNo] = '94484' )

Result:
                                Trail4   Trial 3  Trial2    Trial1        Average(ms)
  Client processing time            2       3        3          4           3.0000
  Total execution time              2059    1657    1745        1608        1767.2500
  Wait time on server replies       2057    1654    1742        1604        1764.2500

New Query:
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT [col1] as [C Sl No.] ,[Table].[Customer_Name],[sl_no] as [T Sl No.] ,[Name2] as [Coll_Name] ,[Collection_Remarks] as [Coll_Remarks]  FROM [Db].dbo.[Table] WHERE ( [Db].dbo.[Table].[Main_Phone_no] LIKE '%94484%'  Escape '`'
 OR CAST (xml_column_name as varchar(max)) like '%94484%'
 OR [Db].dbo.[Table].[KeyPhNo] = '94484' )

Result:
                               Trail4   Trial 3   Trial2    Trial1     Average(ms)
  Client processing time            4       3        2          3       3.0000
  Total execution time              314     371      230        479     348.5000
  Wait time on server replies       310     368      228        476     345.5000

Whether it is a proper way to speedup query? If not, is there any other method to improve the execution speed?


texts: AOAG windows 2 node cluster in confusion

texts:
We have a 2 node cluster alwayson availability group running with 4 resources/roles.
due to VM backup one of the node lost connectivity to node 2 and it marked node 2 as removed from cluster and tried to become primary while it was actually Node 2 that could not communicate with node1 but could with witness so it removed node1 from cluster became primary transferring roles to itself.
quorum is fileshare, for some reason the Node1 which was back online is not able to recognize node2 as the primary and attempts to bring its roles online leading to IP conflict errors and fails to come online, oddly shows node2 as offline but it isn't. I tried restarting cluster service and arp cache in node1 but nothing resolves it, hence I am here seeking help. any help appreciated.


texts: How to restore a database, ignoring the free space within each file?

texts:
Using the following script I get all the disk space used by each file in my database:

;with radhe as (
SELECT

DatabaseName = 'my Database', --DB_NAME(),

a.FILEID,

[FILE_SIZE_GB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000/1024.000, 2)),

[SPACE_USED_GB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, 'SpaceUsed') / 128.000/1024.000, 2)),

[FREE_SPACE_GB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128.000/1024.000, 2)),

a.NAME, a.FILENAME

FROM dbo.sysfiles a)

select * from radhe

this results:

As you can see on the above picture, the amount of free space inside MyDatabase is high.

I need a copy of this database so that I can test some partitions operations.

Is there a way I can restore this database ignoring the free space in each file?

Basically I will use my "new database" to develop and test something, I would prefer to keep it small.

How could I achieve that?


texts: How do I insert a record and put the ID into an existing record in another table?

texts:
I want to insert a record in one table and save the resuting ID in a record I already have in another table. Which sounds easy.

Simplistic example:

create table aaa (id serial primary key, foo int);
create table foos (id serial primary key, foo int, aaa_id int);

Imagine foos has rows which I want to insert in aaa.  But I want a record of aaa.id stored in foos.aaa_id.

Heres some code that definitely does not work:

with rows as (
    insert into aaa (foo)
        select foo from foos
        returning foos.id as foos_id, aaa.id as aaa_id
)
update foos
    set aaa_id = (select aaa_id from rows where foos_id = foos.id);

I can't find a way of getting the "returning" clause to return anything other than records from the table it inserted.

Surely there must be a simple way to do this?


texts: Sqlcmd: Invalid argument - when trying to pass a variable to sqlcmd

texts:
I'm trying to use a DOS command prompt on Server 2012R2 to run a batch with the following in it.  I want to run a SQL command (sqlcmd) and return the results to the command window.

This is what I'm currently trying but sqlcmd keeps throwing back Sqlcmd: 'test': Invalid argument. Enter '-?' for help.20:56:08.

FOR /F "tokens=* USEBACKQ" %%F IN (`sqlcmd -S localhost -E -i "backup.sql" -v dbname="test"`) DO (
    Echo %%F
)

However if I try it without params/variables it works perfectly!

FOR /F "tokens=* USEBACKQ" %%F IN (`sqlcmd -S localhost -E -i "backup.sql"`) DO (
    Echo %%F
)

Does anyone know a way around getting the variables passed to my sql query using sqlcmd, DOS CMD, as well as a FOR /F loop such as in my first example?


texts: How long can Deadlock info hold in innodb status page?

texts:
When I run SHOW ENGINE INNODB STATUS\G, I can see this timestamp

------------------------
LATEST DETECTED DEADLOCK
------------------------
121118 17:14:31

Questions

Any idea how long Deadlock details/info holds in innodb status page ?
After how long does it refreshes or clears the recent deadlock info?
Any way I can force to get the current status of Deadlocks?

Regards,
Mannoj


texts: Redshift using back slashes in a like statement causing it to fail

texts:
So i am attempting to filter a query on a file path field with a like statement using the below
'%text\Text\text%' this returns no results
'%text_Text_Text%' this returns the expected results
To troubleshoot i have attempted to escape the backslashes to no avail i didin't think they were special chars for the like function anyway.
i am querying Redshift with aginity workbench for redshift.
my question is why can;t i use backslashes or multiple backslashes in a like function if it's not a special character?


texts: Enrolling for Microsoft SQL Server related Newsletters from Microsoft

texts:
I would like to know the process about how to automatically receive information about updates related to SQL Server from Microsoft. I am referring to kind of newsletter kind of alerts..

Updates like :

Latest Service pack releases
SQL Server Version releases
SQL Server enhancements etc...

Can anyone let me know if you already having this ways in place. Thanks.


texts: Would a NoSQL solution be relevant for a real-time mobile game?

texts:
Developing an  iOS/Android/WP real time game.
Server side stuff will be done in Javascript using Node.js.
It will be a trivia with 8-players rooms + an admin site made in Python with Flask (for stats, account management, adding questions... It will also provide an API to get user profiles).

Basically I will have to fetch around ~10 questions/game (I will be checking if users already answered them or not so I can avoid redundancies too).
I was wondering if my project would really benefit from a nosql db.
I have never used one but wouldn't mind learning if it is relevant.

Player data : classic account infos, score, answered questions...
Questions data : questions + translation, answers, stats (times asked, correct/wrong answers count)...

RDMS choice would be psql.

Any thoughts?
Thank you


texts:
New to Azure SQL DB.  I was wondering what is the best practices for scheduling jobs like update statistics in Azure SQL DB.
Googling got a couple of blogs that uses Azure automation.

https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/01/15/automating-azure-sql-db-index-and-statistic-maintenance-using-azure-automation/

Is that the best way to do this?  Just looking for some suggestions on the pros and cons of this approach.  I will be testing this soon.


texts: How to recover MySQL table structure from FRM files

texts:
What I have learned from Google searching:

With MyISAM tables you can simply copy the FRM, MYD, and MYI files. In the folder for the DB I'm trying to recover, there are only FRM files and a "db.opt" file, so they must not be MyISAM tables.

Situation:

My computer crashed, but the data is still on the hard drive. The MySQL 5.5.8 server was installed with WAMP on the crashed computer.

I have tried copying the entire data folder over, but MySQL only creates a blank database - no tables.

There is an FRM file for each of the tables I wish to recover. However, they are not readable text files. Is there any way to recover the table structure from these files?

This post https://stackoverflow.com/a/7688688/1188138 indicates that the structure is in fact in those files, but does not provide a method of retrieval.

Thank you so much - I have tried everything I can think of.


texts: Database name not found but I can connect using IBM Data Studio. What happened?

texts:
In IBM Data Studio, I can create a connection to a remote IBM DB2 LUW database named DBEMP and browse the schema and data.

Here is the steps to create the database connection:

From IBM Data Studio, In Database Adminstration Explorer, select: New -> New Connection to Database.
Select DB2 for Linux, UNIX, and Windows in the dialog.
In General properties:

Database: DBEMP
Host: 10.221.5.111
Port Number: 50001
User Name: user1
Password: *
Click OK.

But when I tried to connect to the remote database server (AIX v7) via telnet command:

telnet 10.221.5.111

Note: I login using root user.

And then I run this command:

/db2/V10.1/bin/db2 connect to DBEMP

I got this error:

  SQL1013N  The database alias name or database name "DBEMP" could not
  be found. SQLSTATE=42705

Then I also run this command:

/db2/V10.1/bin/db2 list database directory

The result list doesn't have database named "DBEMP".

So what happened anyway?

If there is no database name exist, why IBM Data Studio can connect to it?


texts:
I've a table with more than 10 billion records. Many NEW records (in quantum of 40-50 million records)are added regularly in a file as input part of batch process at night. The active table is majorly used for read operations throughout the day. To facilitate fast insert we keep active and inactive table. Everyday we first wipe out the data from inactive table, copy the existing data from active table to inactive table and insert new records. Lastly switch the inactive table to make it active.

I need to know how can I avoid duplicates records existing records to be copied from active table and in NEW records while copying.
eg.

transaction_data_active
customer_num, help_number, issue_number issue_details, issue_closed_status

transaction_data_inactive
customer_num, help_number, issue_number, issue_details, issue_closed_status

The table doesn't have partitions and supports global index on customer_num, help_number, issue_number columns. We are using oracle 11g


texts: Update SQL Server Management Studio without restart

texts:
When I install or update SQL Server Management Studio it always asks to restart the server. Like now when I updated from 17.3 to 17.4, it asks to restart the server when the update is finished. Is it possible to avoid that somehow?

I now I should not have SSMS on the SQL Server itself, but we still do that, so it is annoying that you have to restart the db server just for updating the client.


texts: What is the best configuration for a MySQL instance with a lot of databases and lot of tables?

texts:
I have a MySQL database instance with more than 3000 databases in it. Each database contains more than 200 tables. Total data of all theses database comes around 100 GB. I am using Windows Server 2012R2 operating system with 4 GB of RAM. The RAM memory utilization of the server system was always showing very high. So I tried to restart the system and restart is not working. It is showing restarting for long time and not restarting. When I checked the logs I understood that there was a memory issue. What is the best configuration for the MySQL with above architecture? What do I need to do to make this work with out failure in the future?

[Warning] InnoDB: Difficult to find free blocks in the buffer pool (1486 search iterations)! 1486 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a newer version of your operating system may help. Look at the number of fsyncs in diagnostic info below. Pending flushes (fsync) log: 0; buffer pool: 0. 26099 OS file reads, 1 OS file writes, 1 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.


texts: How to find number of user connections to an Oracle database between a specific time period?

texts: I want to find how many users established connections to the oracle 11g database in a 10 minute time interval. Please help


texts:
For my system, students are required to create a user account by completing a registration form (personal, contact and course details; also username and password).

I need to collect registration data through a single form and insert the collected data into two related tables on my database (all student details in student table and login details in users table).

How best can I code this in php?


texts: What is the most efficient way to update a product table with 1 million rows in MySQL

texts:
I need to update a product table which currently has 1 million rows. For each row, I need to perform an expensive http operation and then update 1 field in the row based on a returned header.

I've read that it might be faster to insert into a new table than updating an existing one. Is that true and, if so, why?

What alterable properties of the product table e.g. Indexes might slow down updates? There are a number of indexes on the table (i did not create these).

MySQL version is 5.6.12 and the engine is MyISAM


texts:
I have a requirement to get rid of our old servers, however one of them includes a database server that is currently using SQL Server 2005.

We have a new server running with 2016 so I wanted to know the best way to upgrade. From what I have discovered is that ideally we would need 2008 as an interim step. However as we do not have 2008 and this cost more I have been looking at the possibility of doing a 2005 back up and then a 2016 restore.

I have tested this out with a very simple db and it seems to work. However I have read that I should change the compatibility level from 100 (use to be 90) to 130. My question is, is the necessary? After all some of these old db's are old legacy databases which require very little change. Is it really worth it?

So what I will not be able to use the fancy new ways of doing stuff, but as these are old does that matter? After all its not like it is needed otherwise we would not have created these db. I am yet to still test with a more complex db but wanted to make sure I am on the right path.


texts: Option to tell Toad to never split a file which contains multiple objects?

texts: Is there an option to tell it to stop prompting me to split a file which contains multiple objects when opening .sql files from an edit window?  I never want to split the file!


texts: rename a column without disrupting users

texts:
You can rename a column using the following statement:
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

However, this brings the old column out of commission immediately. Any use sites that used the old column name will start having problems.
Is there a way to put the table into a transitory state in which both the old and new name can be used to refer to the column, so that I can move over my clients and then afterwards decommission the old name?


texts:
I am running postgresql 9.2 on a 32-bit machine running windows xp with 4 gb of memory and I am getting out of memory errors.  I suspect it has something to do with these two parameters I have changed related to memory:

shared_buffers = 1024 MB
maintenance_work_mem = 1024 MB

Before changing these parameters, I was not getting out-of-memory errors.  Is this too much for a 32-bit machine?


texts: v$ or rc views to report on RMAN restore database preview

texts:
Setup:

We take an Incremental level 0 backup weekly using:

RMAN> backup as compressed backupset incremental level 0 database;
RMAN> backup check logical as compressed backupset (archivelog all delete all input);

We take an Incremental level 1 backup nightly (except on the night that we take the incremental level 0 backup) using:

RMAN> backup as compressed backupset incremental level 1 database;
RMAN> backup check logical as compressed backupset (archivelog all delete all input);

In RMAN, I know that I can report on when recovery will start and which SCN will need to be restored in order to clear the datafile fuzziness to bring the database to a consistent state using the following:

RMAN> restore database preview;

Media recovery start SCN is 49928711194
Recovery must be done beyond SCN 49928711196 to clear datafile fuzziness

This tells me that if I issue a restore database; i'll need to recover SCN's 49928711194 to 49928711196 before I can open the database.

From here, I know that I can query which archivelog backups are available and which SCNs these archivelog backups contain using:

SELECT sequence#, first_change#, next_change#, id2, handle from (
  SELECT sequence#, first_change#, next_change#, id2
    FROM v$backup_archivelog_details where 49928711194 between first_change# and next_change#
UNION
  SELECT sequence#, first_change#, next_change#, id2
    FROM v$backup_archivelog_details where 49928711196 between first_change# and next_change#) archivelog
  JOIN v$backup_piece p
    ON archivelog.id2=p.set_count;

SEQUENCE#   FIRST_CHANGE#   NEXT_CHANGE#    ID2     HANDLE
22030       49923438913     49928711521     7940    L:\ORACLE\BACKUPS\ARCH_2_7940_1

My question is: is there a way to query a v$ view or rc view to obtain the start SCN and beyond SCN that RMAN> restore database preview; provides in order to clear the datafile fuzziness for a given backupset?


texts: How to recover a crashed MyISAM table which myisamchk couldn't recover?

texts:
We take db backup by stopping the database and then issuing a tar -zcf on the mysql data directory. When we restore the data directory on another machine and try to start the slave, the mysql error log reports that a number of tables have crashed with messages similar to the following:

140221 19:09:49 [ERROR] /usr/sbin/mysqld: Table './adplatform/aggregate_app' is marked as crashed and should be repaired
140221 19:09:49 [Warning] Checking table:   './adplatform/aggregate_app'
140221 19:09:49 [ERROR] 1 client is using or hasn't closed the table properly

Trying to recover the table using myisamchk results in the following for a particular table:

$ myisamchk -o -f -r -v --key_buffer_size=4G --myisam_sort_buffer_size=4G --read_buffer_size=2G --write_buffer_size=2G aggregate_app
Warning: option 'read_buffer_size': unsigned value 2147483648 adjusted to 2147483647
Warning: option 'write_buffer_size': unsigned value 2147483648 adjusted to 2147483647
- recovering (with sort) MyISAM-table 'aggregate_app'
Data records: 337215562
- Fixing index 1
  - Searching for keys, allocating buffer for 49367435 keys
  - Last merge and dumping keys
- Fixing index 2
  - Searching for keys, allocating buffer for 252645129 keys
  - Last merge and dumping keys
myisamchk: Unexpected eof found when reading file '/tmp/ST5BRnZ9' (Errcode: 175)
myisamchk: error: 175 when fixing table
MyISAM-table 'aggregate_app' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

As you can see, I'm using the -o and the -f options and not using the -q option. Is there any way to repair this table?


texts:
When I attempt to search in the full-text catalog which is my created earlier, I haven't been finding any word within database.

For example:

My word: Where
CONTAINSTABLE(TABLO,*,' "*Where*" ')

Here is no problem.

But,

CONTAINSTABLE(TABLO,*,' "*here*" ')

if I wrote a thing like above the line,no rows returning.What should I do?


texts: PostgreSQL migrate data from one table to another (which inherits the first one)

texts:
I have a table "company" and another named "school" ("school" table inherits "company").
Most of the data is community generated and sometimes a cleaning is needed.
I would like to be able to "transfer" a row from the company table to the school table without modifying its ID (because there could be some foreign keys linked to it).

Is there a clean way to do this ?

Thanks for your help.


texts: How to determine if a mysql binary log file is empty?

texts:
In order to determine whether a database backup is needed or not, I'm trying to see whether a mysql binlog file has any data in it. (During a backup, we make a fresh binlog file and record the filename. Then later, we can check the contents of that binlog to see if anything has happened since the last backup.)

Is there any way to know when a binlog file is actually empty? Perhaps using the mysqlbinlog command?

I would check the position from SHOW MASTER STATUS, but the position seems to vary with the mysql version... is it constant within a version? My version of mysql (5.6.11) starts a new binary log at position 120.

An alternative approach would be to record the time that the binlog was created at and check to see if there were any changes made after that, but it makes me nervous relying on the speed of the backup process like that.


texts: How to replace empty, multi-column result with single 'none'

texts:
If it is possible to make query in Oracle which will return single column/row with value "none" instead of empty, multi-column result?
Otherwise return proper amount of columns with result.
This is example query in my Oracle DB which returns empty result.

SELECT *
  FROM (select '- Tablespace ->',
               t.tablespace_name ktablespace,
               '- Type->',
               substr(t.contents, 1, 1) tipo,
               '- Used(MB)->',
               trunc((d.tbs_size - nvl(s.free_space, 0)) / 1024 / 1024) ktbs_em_uso,
               '- ActualSize(MB)->',
               trunc(d.tbs_size / 1024 / 1024) ktbs_size,
               '- MaxSize(MB)->',
               trunc(d.tbs_maxsize / 1024 / 1024) ktbs_maxsize,
               '- FreeSpace(MB)->',
               trunc(nvl(s.free_space, 0) / 1024 / 1024) kfree_space,
               '- Space->',
               trunc((d.tbs_maxsize - d.tbs_size + nvl(s.free_space, 0)) / 1024 / 1024) kspace,
               '- Perc->',
               decode(d.tbs_maxsize,
                      0,
                      0,
                      trunc((d.tbs_size - nvl(s.free_space, 0)) * 100 /
                            d.tbs_maxsize)) kperc
          from (select SUM(bytes) tbs_size,
                       SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize,
                       tablespace_name tablespace
                  from (select nvl(bytes, 0) bytes,
                               nvl(maxbytes, 0) maxbytes,
                               tablespace_name
                          from dba_data_files
                        union all
                        select nvl(bytes, 0) bytes,
                               nvl(maxbytes, 0) maxbytes,
                               tablespace_name
                          from dba_temp_files)
                 group by tablespace_name) d,
               (select SUM(bytes) free_space, tablespace_name tablespace
                  from dba_free_space
                 group by tablespace_name) s,
               dba_tablespaces t
         where t.tablespace_name = d.tablespace(+)
           and t.tablespace_name = s.tablespace(+)
         order by 8)
 where kperc > 98
   and tipo <> 'T'
   and tipo <> 'U'

But application I'm using this select in waits result or "none", but not empty result.
I have tried to figure out this problem with CASE, COALESCE statement but nothing worked out.


texts:
I am currently designing (as an assignment for the course at the university) a database for online booking of hotels worldwide and I have stumbled into a problem.

Here's is the conceptual design:

And below are the tables of the relational layout so far :

GUEST
guest_ID varchar PK
email varchar
guest_password varchar
first_name varchar
last_name varchar
mobile_num varchar
member_status varchar
pref_language varchar
pref_currency_code varchar

HOTEL
hotel_ID varchar PK
hotel_name varchar
ratings_avg int
phone_num varchar
email varchar
currency_code varchar
street_name varchar
street_num varchar
zip_code varchar
city varchar
country varchar

ROOM
room_ID varchar PK
hotel_ID varchar FK to Hotel
room_name varchar
low_season_rate numeric
high_season_rate numeric
max_persons int

BOOKING
guest_ID varchar FK to Guest
room_ID varchar FK to Room
check_in date
check_out date
(a combination of the first 4 as PK)
persons_num int

PAYMENT
guest_ID varchar FK to Guest
room_ID varchar FK to Room
date_paid timestamp
amount numeric

EVALUATION
guest_ID varchar FK to Guest
hotel_ID varchar FK to Hotel
eval_date date
(a combination of the first 3 as PK)
rating int
guest_comment text

I came up with this design thinking as follows:
The guests will book rooms, which belong to hotels, pay for the rooms and then, if they want, after their stay, evaluate the hotel.

So I considered Booking and Payment as relationships between the Guest and the Room and the Evaluation as a relationship between the Guest and the Hotel.

The problem this design seems to have is that the Payment and Evaluation are completely cut off from the Booking, so their tables can fill even without a preexisting booking having taken place.

And the way I see it now, a Guest pays for a Stay(=Booking) in a Room and evaluates a Stay(=Booking) in a Hotel, so I am thinking that these tables should refer to the Booking table.

But Booking is a relationship, could I form a relationship between an entity and another relationship? Or could it be considered an entity?

I would welcome any thought about the subject.


texts: Changing the target schema on export or import?

texts:
I normally do the following for SQL dumps:
pg_dump -U username -cOx database > dump.sql

And then restore it into a new database with:
psql -U username new_database < dump.sql

Currently the database tables are not in the public schema, but a schema named for a user. No reason for it. I think it happened when there was a migration from MySQL to PostgreSQL a few years back using a utility whose name escapes me.
I'd like to move everything into public. I know I can do this by editing the dump.sql, which is a slow process given the size of the file (which is pretty small in database terms).
Is there a way to do this when exporting or importing a SQL dump?
This is PostgreSQL 13, btw.


texts:
I'm trying to understand more about the memory usage for SQL Server. I can't seem to figure out what's exactly stored in the memory buffer. My setup is a Windows Server 2008 x64, 8 GB RAM, SQL Server 2008 standard R2, maximum SQL Server memory 6000 MB.

After running SQL Server a couple of hours, the resource monitor shows sqlservr.exe has used about 6000 MB of memory, but when I check the usage for data and plan caches I get less than 6000 MB:

Cached plans          2541 MB
Database data cached  2706 MB
Total                 5247 MB

I used sys.dm_exec_cached_plan and sys.dm_os_buffer_descriptors to find these values, so from these values I don't know what the 750 MB are used for... I don't think SQL Server is reserving memory since this is a x64 machine... Am I missing something here?


texts: Postgresql query XMLTable with default namespace

texts:
I use PostgreSQL 12 to query XML data.

My data is as below

CREATE TABLE xmldata AS SELECT
xml $$
<ROWS xmlns="http://x.y.z">
  <ROW id="1">
    <COUNTRY_ID>AU</COUNTRY_ID>
    <COUNTRY_NAME>Australia</COUNTRY_NAME>
  </ROW>
  <ROW id="5">
    <COUNTRY_ID>JP</COUNTRY_ID>
    <COUNTRY_NAME>Japan</COUNTRY_NAME>
    <PREMIER_NAME>Shinzo Abe</PREMIER_NAME>
    <SIZE unit="sq_mi">145935</SIZE>
  </ROW>
  <ROW id="6">
    <COUNTRY_ID>SG</COUNTRY_ID>
    <COUNTRY_NAME>Singapore</COUNTRY_NAME>
    <SIZE unit="sq_km">697</SIZE>
  </ROW>
</ROWS>
$$ AS data;

When query, my SQL is as below:

SELECT xmltable.*
  FROM xmldata,
       XMLTABLE(namespaces(default 'http://x.y.z'), '//ROWS/ROW'
                PASSING data
                COLUMNS id int PATH '@id',
                        ordinality FOR ORDINALITY,
                        "COUNTRY_NAME" text,
                        country_id text PATH 'COUNTRY_ID',
                        size_sq_km float PATH 'SIZE[@unit = "sq_km"]',
                        size_other text PATH
                             'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',
                        premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified') ;

It happened this error:

DEFAULT namespace is not supported

Please instruct how to fix it ?
Many thanks.


texts:
I have a cube in SSAS 2012 with about 30 dimensions, one of which is Company.

The Company dimension contains a list of companies, of which there are several hundred in the cube.  Our security model requires that users have visibility only to those Company values to which they have been assigned.

In our SQL Server environment, we control this access through a series of functions that are JOINed in VIEWs.  The functions return a table listing all the Company values, which is used to filter results.

For instance:

CREATE VIEW dbo.FakeView
AS
SELECT d.*
FROM FunctionToGetAccess(SYSTEM_USER) f
INNER JOIN DataTable d
    ON d.CompanyID = f.CompanyID

Is there a way for me to port this logic, or a similar logic, to dimension-level security in SSAS?

A couple of notes:

The Cube will be rebuilt overnight
Security does not have to be current hourly, but should be reprocessed during the cube processing overnight
I would strongly prefer not to have to materialize the list of users and allowed clients into a table or dimension


texts: Restore of Replicated Database fails with “Cannot drop the table  because it is being used for replication.”

texts:
published database restoration failed once you setup transactional Replication using system Store procedures. Few work around to this problem is restore with KEEP_replication option and than drop the publication and subscription.

Is there anything we can do while setting up the Transactional replication using System SP that can avoid this issue ?


texts: Improve query performance of subquery with MAX()

texts:
I am looking to migrate our support database from MS SQL Server to Postgres and most of it has gone OK. However, some of the queries seem to be slower on PG than on the SQL Server.

We have a ticket table which has a related table assignments. In the assignment table (linked by ticket_id) we can make comments and assign the ticket to another group to work on the ticket. So to find out who currently has the ticket we need to get the last update for the ticket

Select tbltickets.ticketid, tbltickets.ticketsummary, ta2.*
from tblTickets
Join (
   SELECT tblassignment.ticketid,
          max(tblassignment.allocationid) AS maxallocationid
   FROM tblassignment
   join tbltickets on tblassignment.ticketid = tbltickets.ticketid
   GROUP BY tblassignment.ticketid) as ta on tbltickets.ticketid = ta.ticketid
join tblassignment ta2 on ta.maxallocationid = ta2.allocationid
where tbltickets.closed is NULL;

So it works but it slower than we would like circa 2seconds and will only get slower.

it is looking up the maxallocationid subquery that is taking the time at 1.5s as it is doing the calculation for all the assignments, not just the ones for the tickets we are interested in (e.g closed is NULL)

I can improve like this:

Select tbltickets.ticketid, tbltickets.ticketsummary, ta2.*
from tblTickets
Join (
   SELECT tblassignment.ticketid,
          max(tblassignment.allocationid) AS maxallocationid
   FROM tblassignment
   join tbltickets on tblassignment.ticketid = tbltickets.ticketid
   where tbltickets.closed is null
   GROUP BY tblassignment.ticketid) as ta on tbltickets.ticketid = ta.ticketid
join tblassignment ta2 on ta.maxallocationid = ta2.allocationid
where tbltickets.closed is NULL;

It now runs in 170 msec which is fine, but I cannot then use in a view to make it reusable with different criteria (or at least I don't know how).

Is there a way to allow for filtering the tickets, then look up maxassignment only on returned tickets, preferable without reorting to functions/stored procedures!

I have considered using a trigger to update the ticket table with info from the assignment, but do not want to go there if I can avoid it as I am not familiar with this!


texts: Case insensitive column names in a postgresql trigger function

texts:
I have a trigger function in PostgreSQL 12 which executes something like this:
CREATE OR REPLACE FUNCTION "my_latlon_function"()
RETURNS trigger AS
$$
BEGIN
    latcolumn:= (
        SELECT column_name
          FROM information_schema.columns
         WHERE table_schema = TG_TABLE_SCHEMA
           AND table_name = TG_TABLE_NAME
           AND column_name ~* '.*lat.*'
    );
    loncolumn := (
        SELECT column_name
          FROM information_schema.columns
         WHERE table_schema = TG_TABLE_SCHEMA
           AND table_name = TG_TABLE_NAME
           AND column_name ~* '.*lon.*'
    );
    EXECUTE 'select $1.' || loncolumn USING NEW INTO lon;
    EXECUTE 'select $1.' || latcolumn USING NEW INTO lat;

    -- do much stuff

    RETURN NEW;

END
$$
LANGUAGE 'plpgsql';

The problem is, the lat and lon columns have capital letters in their name, e.g. myLatitude and myLongitude. The trigger function is able to retrieve these names, that's no problem.
The problem lies in the two EXECUTE statements where it seems that the column names become lower-cased as stated by this error (in the underlying QUERY when the trigger is fired):
ERROR:  column "mylongitude" not found in data type gpspoints
LINE 1: select $1.myLongitude
               ^
QUERY:  select $1.myLongitude
CONTEXT:  PL/pgSQL function my_latlon_function() line 24 at EXECUTE
********** Error **********

ERROR: column "mylongitude" not found in data type gpspoints
SQL state: 42703
Context: PL/pgSQL function my_latlon_function() line 24 at EXECUTE

I know that PostgreSQL does need to enclose column names which have capital letters in their name into double quotes. Hence, I have tried to set double quotes in the two EXECUTE statements such as this:
    EXECUTE 'select $1.' || "loncolumn" USING NEW INTO lon;
    EXECUTE 'select $1.' || "latcolumn" USING NEW INTO lat;

But the error stays exactly the same.
If possible, how can I handle CamelCase column names in a PostgreSQL trigger function?
If not not, why?


texts: "ORA-01033: Oracle initialization or shutdown in progress" error after PC restart

texts:
Yesterday I installed Oracle 12c database on my PC and configured it like in this guide. Then I created two connections in SQL Developer: first one with a system user, then I created another user and made the second connection with that user. I managed to establish both connections. I even made some sample table using the second one. Well, everything worked great until I restarted my PC. After that I tried to establish a connection, but no luck with either of them. The following error message was displayed:

I simply don't get it. I restarted my PC normally, restarted Oracle services, also did a db shutdown and startup... nothing. Running select instance_name, status, database_status from v$instance; from SQL Plus says OPEN and ACTIVE.

Any suggestion? I'm simply running out of options...


texts:
My Users table crashed and I had to do a repair on it, now I'm getting the error:

ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MariaDB server

No matter how I try to connect

mysql -u root
mysql -u root --host=127.0.0.1
mysql --host=127.0.0.1 --user=root -p
mysql -h 127.0.0.1 -u root -p

Now the only way I can open the database is with

mysqld  --skip-grant-tables

However if I gain access by this method I can't grant the original permissions back
grant all on *.* to root@localhost with grant option; FLUSH PRIVILEGES;

ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

Is there a solution to escape this catch 22? Or am I destined to use --skip-grant-tables forever?
I really don't want to export and import 30GB worth of data into a new database.


texts: I want to take backup(pg dump) of busy posgres database in which read and write operation frequently happen. Also It has huge data. So can anyone suggest me, Can I take backup of my postgres database in operation time(Busy database) OR I need to stop operation and then only can take backup.


texts: alter public database link to private in oracle

texts:
I have a database link in my database which is created as public as below.

CREATE PUBLIC DATABASE LINK dblink_name CONNECT TO usernane USING 'tag';

I have a requirement to convert it to a PRIVATE database link.
Is there any way to ALTER it without recreating it?

Many thanks.


texts: Can this duplicate 'points' data be prevented?

texts:
I'm building a game in which a User does Activities, in each activity the player passes certain Areas, now after the game the player is awarded points for each area he passed through during an activity.
Therefor I assign a certain amount of points in the activity_area table.
What also happens is that the player can receive a points for a certain area of his at random moments, so points can be given for a certain area of that player without concerning an activity.
I created the user_area table to save the areas that a user owns (these are areas he has passed though during an activity). and the user_area_points table to save the random points received.
What I'm now worried about, is that 'points' are stored in two places, in the activity_area table and the user_area_points table.
Should this be further optimized?


texts: How do you change the storage parameters of a already made table (sql developer)?

texts:
Summarize the problem
I have been given 6 tables that have all been created already and I was told to change the storage parameters of those tables. the issue I run into is that I can only change pctfree, pctused, initrans and max trans and not the others (initial, next, pctincrease and maxextents)
Provide details and any research
I have done a lot of research, yet some of them do not work with sql developer at all. Whilst the others do work but as stated only for the 4 storage parameters.
When appropriate, describe what you’ve tried
alter table CUSTOMERS
pctused 20 pctfree 80;

This works perfectly for those two, but I am unable to add the others. From what I found online, this was in fact the only thing that worked for me.
I appreciate all input!


texts:
According to Performance Insights in Amazon RDS, the following query is causing some high wait times in my postgresql database:

SELECT (1) AS "a"
FROM "employee_employee"
WHERE ("employee_employee"."year" = 2018
  AND "employee_employee"."jurisdiction_id" = 537
  AND "employee_employee"."ual" IS NOT NULL)
LIMIT 1

This query comes from the python Django ORM and is written like this:

context["hide_ual"] = not records.filter(ual__isnull=False).exists()

I have indexes built for year and jurisdiction_id fields. The ual field is a numeric type and is very often NULL. I'm tempted to create a separate way to identify if a jurisdiction even has ual associated with it, so I can stop constantly checking to see if ual values exist. Before I do that, is there a better way to improve speed within the database?


texts: SQL Server Execution Plans and Index Drops

texts: When a SQL Server index is dropped (say it's a duplicate) what are the steps undertaken by the engine to clear and then rebuild the execution plans that reference it?  In particular I'm wondering whether it's an all-at-once process, an as-new-queries-occur process or something in between.  Thanks.


texts:
I have problem with following query Q1:

SELECT ADDRESS.STREET
     FROM  MYSALES.MYADDRESS ADDRESS
 INNER JOIN MYSALES.MYCOUNTRY MCOUNTRY ON MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID
 INNER JOIN MYGLOBAL.COUNTRY COUNTRY ON COUNTRY.COUNTRY_ID = MCOUNTRY.COUNTRY_ID

When we translate this to good old notation, Q2:

SELECT ADDRESS.STREET
FROM MYSALES.MYADDRESS ADDRESS
    ,MYSALES.MYCOUNTRY MCOUNTRY
    ,MYGLOBAL.COUNTRY COUNTRY
WHERE
    MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID
    AND COUNTRY.COUNTRY_ID = MCOUNTRY.COUNTRY_ID

The problems start when I wrap in view:

SELECT * FROM (Q1) or SELECT * FROM (Q2) results with error:

  ORA-01445: cannot select ROWID from, or sample, a join view without a
  key-preserved table
  01445. 00000 -  "cannot select ROWID from, or sample, a join view without a key-preserved table"

But for query Q3:

 SELECT * FROM  MYSALES.MYCOUNTRY MCOUNTRY
     INNER JOIN MYGLOBAL.COUNTRY COUNTRY ON COUNTRY.COUNTRY_ID = MCOUNTRY.COUNTRY_ID

Result of SELECT * FROM (Q3) is correct.

So the problem is in Q4:

  SELECT * FROM MYSALES.MYADDRESS ADDRESS
    INNER JOIN MYSALES.MYCOUNTRY MCOUNTRY ON MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID

When I wrap the last query in a view, it fails, with the above error.

The columns that end with _ID, are should be  primary or references key.

Could you please guide me a hint where should I look for solution ?


texts: Cannot connect to DB2 from command prompt on TLSv1.2 enabled port

texts:
I am trying to connect to DB2 version 11.x on linux from my local windows machine. Ran below command in by ibm's jre/bin, but still getting an error.

Command

C:\Program Files (x86)**>java com.ibm.db2.jcc.DB2Jcc -url "jdbc:db2://serverip:50001/dbname:sslConnection=true;sslCertLocation=D:\path_to_certificate;" -user dbusername -password dbpassword

Error

  [jcc][10512][13714]Failed to create connection. SQLCODE: -4499
  SQLSTATE: 08001 Message: [jcc][t4][2030][11211][4.25.13] A
  communication error occurred during operations on the connection's
  underlying socket, socket input stream, or socket output stream. Error
  location: Reply.fill() - socketInputStream.read (-1). Message:
  Received fatal alert: handshake_failure. ERRORCODE=-4499,
  SQLSTATE=08001

Note - I have given .cer certificate file in certificate path (As in this example, .arm is given), will it matter?

What am I doing wrong?

Analysis 1 Ran below command on path, output is :

Command

java com.ibm.db2.jcc.DB2Jcc -version

Output

IBM Data Server Driver for JDBC and SQLJ 4.25.13

That proves JCC version is correct (More than 4.1)

Analysis 2

Ran telnet from local windows machine towards DB2 linux machine on desired TLSv1.2 enabled port. It is success.


texts: Is there a way to make this select retrieve the same results with a single seek?

texts:
Is it possible to retrieve the same data as the following with a single seek or scan, either by modifying the query or influencing the optimizer's strategy?

Code and schema similar to this are currently on SQL Server 2014.

Repro script. Setup:

USE tempdb;
GO
IF OBJECT_ID('dbo.TestUpload', 'U') IS NOT NULL
  DROP TABLE dbo.TestUpload;

CREATE TABLE dbo.TestUpload(
    JobRunId bigint NOT NULL,
    ThingAName nvarchar(255) NOT NULL,
    ThingAType nvarchar(255) NOT NULL,
    ThingAGranularity nvarchar(255) NOT NULL,
    ThingBName nvarchar(255) NOT NULL,
    ThingBType nvarchar(255) NOT NULL,
    ThingBGranularity nvarchar(255) NOT NULL
);
CREATE CLUSTERED INDEX IX_JobRunId ON dbo.TestUpload (JobRunId);

GO

INSERT INTO dbo.TestUpload (JobRunId, ThingAName, ThingAType, ThingAGranularity, ThingBName, ThingBType, ThingBGranularity)
  VALUES (1, 'A', 'B', 'C', 'D', 'E', 'F');
GO 10

INSERT INTO dbo.TestUpload (JobRunId, ThingAName, ThingAType, ThingAGranularity, ThingBName, ThingBType, ThingBGranularity)
  VALUES (1, 'D', 'E', 'F', 'A', 'B', 'C');
GO 10

Query:

DECLARE @JobRunID bigint = 1;

SELECT JobRunId,
  ThingAName AS Name,
  ThingAType AS [Type],
  ThingAGranularity AS Granularity
FROM dbo.TestUpload
WHERE JobRunId = @JobRunID
UNION
SELECT JobRunId,
  ThingBName AS Name,
  ThingBType AS [Type],
  ThingBGranularity AS Granularity
FROM dbo.TestUpload
WHERE JobRunId = @JobRunID;

Tear down:

IF OBJECT_ID('dbo.TestUpload', 'U') IS NOT NULL
  DROP TABLE dbo.TestUpload;

I think this is probably not modeled ideally. I'm trying to get more information from the developer about how the schema was chosen but am curious if there's a TSQL trick I'm overlooking as it will be easier to change the query than the schema.


texts:
Is there any point in following up a AUTO INCREMENT with a NOT NULL?
Seeing as the db takes care of this, I assumed a AUTO INCREMENT will always have a value?


texts: Setting up SQL Server to handle queries from external domains

texts:
I am not a SQL Server admin. I have tried to google this but can't seem to use the right words to get the right info and they all talk about remote connections to a SQL Server in terms of admin and remote access.

I have a database on one domain, that needs to accept connections and queries etc... from web code (page) I am hosting on another domain. I am using Windows 2012 servers on AWS in the case of both domains, and I have port 1433 opened on the domain server with the database, but open only to the ip address of this other domain. The SQL Server is using the default windows based auth.

Can someone tell me where to find a guide to set up the SQL Server to accept these external requests with a proper user name and password ?


texts: Oracle APEX 5.1 connect to remote db using JDBC and query data

texts: I have only used db links to get access to remote db so far. Now I have to find a way to use JDBC to connect to remote db and get data. Can someone guide me what all things would I need to ask the remote db admin in order to setup a jdbc connection, and once I have the connection, how do I query the database using java stored procedure, just like I am querying it over remote db right now with db link? Is there a good quick start or a tutorial out there that I can use?


texts: MySQL router Access denied for user 'routeruser'@'%' to database 'mysql_innodb_cluster_metadata' (1044)

texts:
I am trying to deploy MySQL-router in kubernetes, I had some problems previously and they were fixed.
Now mysql-router pod is showing following error in kubernetes logs mysql-router-pod -n mysql-router

Succesfully contacted mysql server at 192.168.123.123. Checking for
cluster state. Succesfully contacted mysql server at 192.168.123.123.
Trying to bootstrap. Please enter MySQL password for routeruser:
FATAL ERROR ENCOUNTERED, attempting to undo new accounts that were
created

- New accounts cleaned up successfully
# Bootstrapping MySQL Router instance at '/tmp/mysqlrouter'...
\ Creating account(s) (only those that are needed, if any) Error: Error creating MySQL account for router (GRANTs stage): Error
executing MySQL query "GRANT SELECT, EXECUTE ON
mysql_innodb_cluster_metadata.* TO 'mysql_router21_tc47nqet6ty7'@'%'":
Access denied for user 'routeruser'@'%' to database
'mysql_innodb_cluster_metadata' (1044)

I had given privileges to routeruser as described here.


texts: After adding indexes to an AWS RDS MySQL DB getting huge/crippling spikes in IOPS

texts:
So after looking at the production database, I realised that several queries were running utilizing no indexes at all, and as such, were row-scanning close to 4 million rows. :/

I added the required indexes to both the production db instance and the staging read-replica instance.

The result of adding the indexes on the staging environment has been huge:

You can clearly see when the indexes were added, leading to a massive reduction in IOPS and as-such keeping the burst balance at it's maximum level.

However, on our production instance, all is not quite so perfect:

Again, in general the amount of IOPS has gone down dramatically. However, we are now seeing huge spikes in both read and write IOPS every day or so (no particular schedule) which then completely exhausts our burst balance and cripples the db instance until whatever it is doing is finished, at which point the burst balance goes back up again.

Does anyone have any idea's as to what might cause this sort of behaviour, especially in regards to adding indexes, as these spikes were not occuring before we added them.


texts: MYSql Full Text Boolean search and relevancy score improvements

texts:
I am using a mysql BOOLEAN full text search as shown in the query below and am getting what I think to be bad results that could be more relevant.

Query:

SELECT phppos_items.name,MATCH (phppos_items.name)
AGAINST ('Samsung S4 LCD*' IN BOOLEAN MODE) as rel
FROM `phppos_items`
WHERE MATCH (phppos_items.name) AGAINST ('Samsung S4 LCD*' IN BOOLEAN MODE)
ORDER BY `rel` DESC LIMIT 20;

The search is Samsung S4 LCD* IN BOOLEAN MODE and I would think "Samsung S4 LCD/Digi Black" and "Samsung S4 LCD/Digi White" would have a higher relevancy score; but it does NOT (Shown below).

Many of the items in the database contain 2 characters or less to search.

So I changed ft_min_word_len to 2 and restarted server and then dropped and created database again. I thought this would help; but it did not.

Do you have any idea on how to fix this? I need to use boolean mode as Natural Language search has the 50% rule and becoming a STOP word.

Example queries/results

mysql> show global variables WHERE Variable_name = 'ft_min_word_len';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 2     |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> use pos;
Database changed
mysql> SELECT phppos_items.name,MATCH (phppos_items.name) AGAINST ('Samsung S4 LCD*' IN BOOLEAN MODE) as rel FROM `phppos_items` WHERE MATCH (phppos_items.name) AGAINST ('Samsung S4 LCD*' IN BOOLEAN MODE) ORDER BY `rel` DESC LIMIT 20;
+----------------------------------+--------------------+
| name                             | rel                |
+----------------------------------+--------------------+
| Samsung Tab 3 8.0" T310 LCD/Digi | 1.9734089374542236 |
| Samsung Note 10.1" LCD/Digi      | 1.9734089374542236 |
| Samsung Tab 3 Lite 7" T110 LCD   | 1.9734089374542236 |
| Samsung Tab 4 7" T230 LCD        | 1.9734089374542236 |
| Samsung Tablet P600 LCD Black    | 1.9734089374542236 |
| Samsung T800/805 LCD/Digi        | 1.9734089374542236 |
| Samsung S3 LCD/Digi Blue         | 1.9734089374542236 |
| Samsung S3 LCD/Digi White        | 1.9734089374542236 |
| Samsung S4 LCD/Digi Black        | 1.9734089374542236 |
| Samsung S4 LCD/Digi White        | 1.9734089374542236 |
| Samsung S5 LCD/Digi Black        | 1.9734089374542236 |
| Samsung S5 LCD/Digi White        | 1.9734089374542236 |
| Samsung S4 Mini LCD/Digi Black   | 1.9734089374542236 |
| Samsung S4 Mini LCD/Digi White   | 1.9734089374542236 |
| Samsung i9000 LCD/Digi           | 1.9734089374542236 |
| Samsung Note 2 LCD/Digi Black    | 1.9734089374542236 |
| Samsung Note 2 LCD/Digi White    | 1.9734089374542236 |
| Samsung Note 3 LCD/Digi Black    | 1.9734089374542236 |
| Samsung Note 3 LCD/Digi White    | 1.9734089374542236 |
| Samsung Ace 2X LCD               | 1.9734089374542236 |
+----------------------------------+--------------------+
20 rows in set (0.00 sec)

mysql> SELECT @@VERSION;
+-----------+
| @@VERSION |
+-----------+
| 5.6.26    |
+-----------+
1 row in set (0.00 sec)


texts: Optimization: Moving variable declarations to the top of your procedure

texts:
While working on optimizing some stored procedures, I sat down with the DBA and went through some stored procedures with high blocking and/or high read/write activity.

One thing the DBA mentioned was I should declare all variables (especially TABLE ones) at the top of the stored procedure to avoid recompiles.

This is the first I have heard of this and was looking for some confirmation before revisiting all the different stored procedures we have. He was calling it "late viewing of the code", and the recompile was locking the schema which would account for the blocking.

Does moving all variable declarations to the top of your stored procedure reduce recompiles?


texts: DB2 Commands -- Different ways of stopping database

texts:
I'm hoping to get a more in-depth explanation of the following DB2 commands, which all relate to stopping the database in some way, and how they differ from each other.

How do DBAs think about these commands and decide which situations to apply them?

deactivate database
terminate
quiesce
db2stop

The official documentation doesn't provide more than a couple sentences and usually defines the commands in terms of themselves:

  DEACTIVATE DATABASE: This command deactivates the target database on all members in the
  instance or, a specified member.

Or,

  TERMINATE: Explicitly terminates the command line processor's back-end process.

After trying to research the difference between the commands, most online explanations consist of people simply parroting the documentation. But what is the real difference? Does anyone have an "in the trenches" view of which circumstances require which of these commands?


texts: A view to show worked time per task per user?

texts:
I have written this fiddle

CREATE TABLE task
    (`taskid` int, `description` varchar(255),
    PRIMARY KEY (`taskid`))
;

INSERT INTO task
    (`taskid`, `description`)
VALUES
    (1, 'Platform for building enterprise provisioning solutions'),
    (2,'Full-featured access management'),
    (3, 'Robust LDAP server for Java')
;

CREATE TABLE worktime
    (`taskid` int, `user` varchar(7), `startwork` datetime , `endwork` datetime)
;

INSERT INTO worktime
    (`taskid`, `user`, `startwork`, `endwork`)
VALUES
    (1, 'dirk', '2015-07-26 15:19:46', '2015-07-26 15:20:46'),
    (1, 'dirk', '2015-07-26 17:19:46', '2015-07-26 18:11:46'),
    (2, 'dirk', '2011-02-26 11:19:46', '2011-02-26 12:11:46'),
    (2, 'tim', '2011-02-26 11:19:46', '2011-02-26 12:11:46')
;

What I want is : Find the total time a user spent on a task.

E.g. say for task 1 and user dirk this would be 01:01 hours:minutes for task 1.

How can i create this view?


texts: Difference between Clustered Index and Unique Clustered Index?

texts:
I got difference between clustered index and unique index here. But what is the exact difference between clustered index and unique-clustered index?

AFAIK creating clustered index can be done for a column having unique values and if value is duplicate then clustered index cannot be set. Same is the case with unique-clustered index.

So i want to know the difference between them.


texts: What are the conditions for a functional dependency to exist?

texts:
Let be a relation R of scheme: R(A,B,C,D,E) and r an instance of the relation. What functional dependencies verify r?

Observing the functional dependencies that r verify are  doesn't show that they exists on R and are verified by all possible instances of R.

The definition of a functional dependency is:

I said that there is:

A→E, C→A, B→E, C→D, C→E, D→E, C → A, C→B

AB→D, AB→E, AC→B, AD→D, AD→E, BC→A, BC→D, BC→E, BD→ A, BD→C, BD→E, DE→A, DE→ C

ABC→D, ABC→ E, ABD→E, ABE→D

But I don't know if they do exist, I don't know what to do to verify if they actually exists


texts:
There is a piece of code, from a previous dev, that is doing a merge statement with variables, and processing one record at time.
I'm sure the merge can deal with multiple records at one.
create table dbo.Staging
(
    transactionID bigint not null primary key nonclustered,
    transactionDate date not null,
    productId int not null,
    orderId int not null,
    qty int not null,
    unitPrice decimal(8,2) not null
)

create table dbo.Transactions
(
    transactionID bigint not null primary key clustered,
    transactionDate date not null,
    productID int not null,
    ReferenceOrderID  int not null,
    Qty int not null,
    ActualCost decimal(8,2) not null
)
insert into dbo.Staging(transactionID,transactionDate,productId,orderId,qty,unitPrice)
values(1001,'20210101',45889,1501,5,105),
      (1002,'20210102',45892,1503,2,8),
      (1003,'20210103',45555,1555,5,5),
      (1004,'20210104',44444,1444,4,4)

insert into dbo.Transactions(transactionID,transactionDate,productId,ReferenceOrderID,qty,ActualCost)
values
      (1003,'20210101',41111,1111,1,1),
      (1004,'20210101',41111,1111,1,1)

The code is much longer, but the here is the code :
declare
    @bi_transactionID bigint ,
    @bi_transactionID_init bigint =0,
    @d_transactionDate date,
    @i_productId int,
    @i_orderId int,
    @i_qty int ,
    @dm_unitPrice decimal(8,2)

SELECT @bi_transactionID = MIN(s.transactionID)
FROM dbo.Staging as s
WHERE s.transactionID > @bi_transactionID_init

WHILE @bi_transactionID is not null
BEGIN
        SELECT
            @bi_transactionID= s.transactionID,
            @d_transactionDate=s.transactionDate ,
            @i_productId =s.productId,
            @i_orderId =s.orderId,
            @i_qty = s.qty,
            @dm_unitPrice = s.unitPrice
        FROM dbo.Staging as s
        WHERE
            s.transactionID = @bi_transactionID

        MERGE dbo.Transactions AS T
        USING (SELECT s.transactionID FROM dbo.Staging as s WHERE s.transactionID = @bi_transactionID) AS s
        ON (t.transactionID = s.transactionID)
        WHEN MATCHED THEN
        UPDATE  SET
                transactionDate = @d_transactionDate,
                productId = @i_productId,
                ReferenceOrderID=@i_orderId,
                qty = @i_qty,
                ActualCost = @dm_unitPrice

        WHEN NOT MATCHED THEN
        INSERT (transactionID,transactionDate,productId,ReferenceOrderID,qty,ActualCost)
        VALUES (@bi_transactionID, @d_transactionDate,@i_productId,@i_orderId,@i_qty,@dm_unitPrice);

        SET @bi_transactionID_init = @bi_transactionID

        SELECT @bi_transactionID = MIN(s.transactionID)
        FROM dbo.Staging as s
        WHERE s.transactionID > @bi_transactionID_init

END

I believe it can be done in one statement, with merge with out the while and varibles.
Can this be done ?


texts: sys.dm_exec_procedure_stats DMV contents contradict BOL

texts:
I am doing some analysis of long running stored procedures and have come across something of an anomaly, according to books online sys.dm_exec_procedure_stats should only display one row per cached stored procedure plan, however there is one User Stored Procedure that appears twice in the table.

Both 'versions' of this stored procedure have wildly different execution times / counts.

Is this just due to different plans being cached and used? If so I didn't realise that sql server cached multiple plans for the same object_id, I thought it was sort of a one-in one-out deal? Plus if this is the case, it seems to contradict BOLS definition of the DMV?

Further to this, if there are two versions of the plan in use, how can I tell what plan is being used in what situations?

Anyone else encountered this?


texts: Why does COUNT() aggregate return 0 for 'NULL'?

texts:
A SELECT statement returns several rows:

SELECT
  ColA
FROM
  TableA
WHERE
  ColA IS NULL

I get 47 rows that have 'NULL' for ColA within TableA.

ColA
NULL
NULL
NULL
etc...

If I add an aggregate to this query:

SELECT
  ColA,
  COUNT(ColA) AS theCount
FROM
  TableA
WHERE
  ColA IS NULL
GROUP BY ColA

I get

ColA | theCount
NULL | 0

Why is this happening, and what can I do to avoid this?


texts: How does Hash Join works with relational operators other than equality operator?

texts:
I have been reading about Hash Join and how does it work in physical level. However, there is some thing that I don't understand (because of my lack of knowledge).

This is the algorithm of Hash Join that I found;

for each row R1 in the build table
    begin
        calculate hash value on R1 join key(s)
        insert R1 into the appropriate hash bucket
    end
for each row R2 in the probe table
    begin
        calculate hash value on R2 join key(s)
        for each row R1 in the corresponding hash bucket
            if R1 joins with R2
                return (R1, R2)
    end

This is seems adequate for join conditions like "TABLE1.NAME == TABLE2.NAME" etc. However, how does it produce hashes or compares tham when join conditions are "TABLE1.NAME == TABLE2.NAME" && "TABLE1.AGE > TABLE2.AGE".

I just couldn't find and understand how they produce or compare the hashes that requires both equailty operator and some other relational operators like bigger than that etc.


texts: sqlplus connection to Oracle 11g takes much more time to establish than from another machine

texts:
I have an existing Ubuntu 16.04LTS 64bit system that connects to Oracle 11g (11.2.0.4.0 64bit/Windows).

From that Linux machine I can connect to Oracle via sqlplus instantly, using sqlplus uid/pwd@tnsname

I just installed a Linux 18.04LTS, on which I installed the same oracle client and RPM header packages, prepared for 18.04 via alien package converter:

oracle-instantclient12.2-basiclite 12.2.0.1.0-2 amd64 Oracle Instant Client Light package
oracle-instantclient12.2-devel 12.2.0.1.0-2 amd64 Development headers for Instant Client.
oracle-instantclient12.2-sqlplus 12.2.0.1.0-2 amd64 SQL*Plus for Instant Client.

Copied the same tnsnames, sqlnet and listener files, added same environment variables.

But. From the old machine the actual connection to the database in sqlplus takes about 0.5sec:

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 16 16:17:13 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.
[0,5sec]
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

And from the new one there is a pause of about 1 minute before displaying "Connected".

After connection is established, both sessions appear blazing fast when doing the queries.

I looked at the initial ideas regarding network and DNS resolution, but both machines are the same in this respect.

Is there anything Oracle serverside that could account for this?


texts: Can I use PHP function strtotime() in SQL queries?

texts:
I have this table, and I need to use a PHP function in the query.

id     time       lastTime
1      08:02
2      08:04
3      08:30

I am using this query with the strtotime(), and it's not working:

UPDATE table
SET lastTime = strtotime(time) - strtotime('08:00')";

How can it be done?


texts: I tried so many ways to restore a sample database (with the command on top of the picture) but i failed every single time. i successfully restored with this command in past. but now i am facing this problem. And when i am putting "\" to specify path, there is an error saying '\s' is not a valid command. You can see it's showing "D:ample.sql" :(


texts: Login lacks connect endpoint permission in SQL Server - Error 18456, State: 149

texts:
I have a fresh database I've setup, which I can connect to and perform operations like normal via the SQL Server Management Studio. I have two accounts I created for my application to use, and both can log in via SSMS. My program is running java and using the 'mssql-jdbc-6.4.0.jre8.jar' driver, and when I attempt to connect I can see the following error on the database log:

  Error: 18456, Severity: 14, Sate: 149
  Login failed for user 'application_read_only_user'. Reason:
  Login-based server access validation failed with an infrastructure
  error. Login lacks connect endpoint permission. [CLIENT: 127.0.0.1]

I have looked at several sources trying to solve this issue. The official Microsoft documentation does not seem to list state 149, nor do any results I've found online.

I have not modified my endpoints from the original settings, so I only have the following items:

Dedicated Admin Connection
TSQL Local Machine
TSQL Named Pipes
TSQL Default TCP
TSQL Default VIA

I have tried setting the SQL Server services to run as Local System.

There are a lot of similar questions on this site, and stack overflow, but none of them have been able to help. They all either have a different state, or do not list a state.


texts:
I have a union all that combines two statements for an SSRS report. I would like to create a third column that is based on a where clause from the table as the third field (col1, and col2 being the first two fields). Essentially I'm wanting this:

DECLARE @Parameter int = 0

SELECT col1,col2, 'here I would like to use the result of another select col3 WHERE some_condition FROM table 1'
FROM table 1
WHERE @Parameter int = 0

UNION ALL

SELECT col1,col2, 'same as above'
FROM table 1
WHERE @Parameter int = 1


texts:
I have installed sql server 64 bit.
Why does sql configuration manager show 32 and 64 bit options when only 64 bit version is installed?


texts: How to Monitor Transaction Log Usage In SQL Server?

texts:
I would like to monitor transaction log usage, regarding all the following aspects:

What Task\Job\Query is making it fill the drive\file.
Log file usage percentage.
What time the transaction happened.

Any relevant way of doing this which is tested would be helpful.


texts:
Does PostgreSQL support subtransactions?
From DOC

  "Note that a transaction can hold conflicting locks on the same row,
  even in different subtransactions;" (https://www.postgresql.org/docs/9.6/static/explicit-locking.html#LOCKING-ROWS)

On internet i didn't find such info, excepting PlPy or plv8 extensions or discussions on AST on PG WIKI.

If yes, how they are used - any doc?


texts:
I am using MySQL and PhpMyAdmin, I have a table named items - the structure is as follows;

+------+---------+------+---------+
| name | type    | Null | Default |
+------+---------+------+---------+
| id   | int(11) | No   | None    |
+------+---------+------+---------+
| year | int(4)  | Yes  | Null    |
+------+---------+------+---------+

Within PhpMyAdmin I run the following query;

UPDATE `item` SET `item_publication_year` = '' WHERE `item_id` = '5'

This executes successfully.

Then I run this query;

SELECT * FROM `item` WHERE `item_id` = '5'

This executes successfully and produces the following;

+-----+------+
| id  | year |
+-----+------+
| 5   | 0    |
+-----+------+

Why is the year field 0 - shouldn't it be NULL? Am I doing something wrong?


texts: Oracle linked server shows all tables from all schemas in sql server management studio

texts:
I have created a linked server to an oracle database ( 12c ) in sql server management studio. I am mapping the local user to a special remote user in the oracle db. Connection is working fine. The strange thing is that i am able to see all tables from all schemas.

I tried the following query

SELECT *
   FROM OPENQUERY(ORCL_LINK, 'SELECT  table_name, owner FROM  all_tables ORDER BY  owner, table_name')

As a result i see all schemas and all tables. When i run this query in my oracle database i onyl see tab

I thought that the linked server connection is running in the security context of the remote user but it seems it is not the case. Am i wrong?

Update 1

Oracle SQL Developer only shows me tables where i am the owner but when i do this query

SELECT * FROM USER_TAB_PRIVS WHERE privilege = 'SELECT';

i see many other tables. Those tables are visible in the linked server connection.


texts: List all connections, IP addresses and users in MongoDB 3.2

texts:
I'm attempting to create a list of all current connections to my MongoDB 3.2 database.  So far I've been able to list connections using:

db.currentOp(true).inprog.forEach(function(x) { print(x.client_s) })

hacked together from another answer for MongoDB 2.2 but it's a bit messy:

mongos> db.currentOp(true).inprog.forEach(function(x) { print(x.client_s) })
10.0.8.37:34572
10.0.8.42:40883
10.0.8.39:37430
10.0.8.39:43980
10.0.8.43:60428
10.0.8.38:37615
10.0.8.39:57476
[unknown type]
10.0.8.37:39592
[unknown type]
[unknown type]
10.0.8.38:44813
10.0.8.41:55001
[unknown type]
[unknown type]
[unknown type]
10.0.8.43:37350
[unknown type]
[unknown type]
[unknown type]
[unknown type]
[unknown type]
[unknown type]
[unknown type]
10.0.8.40:42973
[unknown type]
[unknown type]
[unknown type]
10.0.8.39:57488
[unknown type]
[unknown type]

I'd like to filter out the [unknown type] result and then tie each op to a user.  I've noticed username and connectionId pop up in the MongoDB logs, so I can see it's possible — just not sure how to proceed.


texts: Get relation data on comma separated values


texts:
my current database version is 11.2.0.3.0. I'm trying to purge my audit trail with a procedure. In my procedure I got this part of code:

SYS.dbms_audit_mgmt.clean_audit_trail(
audit_trail_type        =>  SYS.dbms_audit_mgmt.audit_trail_aud_std,
use_last_arch_timestamp => TRUE
);

I'm not running the procedure under SYS so there is SYS.dbms...in the code.
Everything is working without errors. But audit trail from sys.aud$ table is not purged!

Any ideas, why it's not working?

I read that: Bug 9164488 - DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL does not delete sys.aud$ and sys.fga_log$ tables ID 9164488.8+ was fixed in 11.2.0.2 version, so this should not be the case.

Also setting last archive timestamp is working fine!

SYS.dbms_audit_mgmt.set_last_archive_timestamp(
 audit_trail_type  => SYS.dbms_audit_mgmt.audit_trail_aud_std,
 last_archive_time => to_timestamp(SYSDATE - pocet_dnu),
 rac_instance_number  => null
);

Thanks

Honza


texts:
I have CentOS 64bit with CPanel installed and I use:

service mysql stop

It just keeps ticking periods and never seems like it stops. In the logs it just posts a lot of:

  130303 17:42:38 [Warning] /usr/sbin/mysqld: Forcing close of thread

In the err.log file, I see lots of these:

  [Warning] /usr/sbin/mysqld: Forcing close of thread

It used to be instant. Any idea why it does that and how to fix?

Right now I have to do killall -9 mysql but is there a better way?

The server is also very very active.

Is this a config issue? Do I have memroy settings too high?

[mysqld]
default-storage-engine=MyISAM
local-infile=0
symbolic-links=0
skip-networking
max_connections = 500
max_user_connections = 20
key_buffer = 512M
myisam_sort_buffer_size = 64M
join_buffer_size = 64M
read_buffer_size = 12M
sort_buffer_size = 12M
read_rnd_buffer_size = 12M
table_cache = 2048
thread_cache_size = 16K
wait_timeout = 30
connect_timeout = 15
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
low_priority_updates=1
concurrent_insert=ALWAYS
log-error=/var/log/mysql/error.log
tmpdir=/home/mysqltmp
myisam_repair_threads=4
[mysqld_safe]
open_files_limit = 8192
log-error=/var/log/mysql/error.log

[mysqldump]
quick
max_allowed_packet = 512M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M


texts:
I am running MySQL 5.5.52 I am trying to see how much of my innodb buffer pool is used. If I run 'show engine innodb status' I see this:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 10731520; in additional pool allocated 0
Dictionary memory allocated 1823022
Buffer pool size   639
Free buffers       0
Database pages     638
Old database pages 215
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 16622990, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 19765731, created 109264, written 174555
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 638, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Is there anything in there that tells me how much is used?

If I run MySQL workbench it shows InnoDB Buffer Usage: 79.2% but I
want a way to get this programmatically with SQL not just from a GUI.
How can I do that?


texts: MS Access migration from Oracle to SQL server?

texts:
Let me start off by saying I'm relatively new to databases etc. I have been asked to undergo a project is which I will attempt to migrate about 100 or so existing MS access 2003 databases from using linked Oracle 11g linked tables to using MS SQL server linked tables.

If you can imagine these 100 MS Access tables are vital and they currently do their job perfectly however the project is intended to just move away from Oracle in general. the SQL server database and tables all exist currently. The sql server and oracle versions both have the same content the only difference if the column naming convention i.e. ‘POLICY_NUMBER’ in Oracle and ‘PolicyNumber’ in sqlserver. What I want is to this is in the easiest way possible to make the Access queries/reports/forms work by simply changing the link from oracle to SQL server.

My points of investigation currently have been looking one Access database and trying to do a proof of concept on that single table. I have been looking at the MSysQueries table, in thinking I could alter the queries in here to point it to different databases and column names. This way I would not have to change the queries individually. I have not been too successful so far and feel way out of my depth.

The bottom line of this question and the reason for posting is simply asking has anyone ever done anything similar? What would you're approach be? Am I looking in the complete wrong direction?

EDIT:
The sqlserver and oracle both do the same task, they take a copy of production data and have it available to test on test databases. The reason they both do the same thing is the reason for this project, to move away from oracle in general and licensing fees etc. The access databases are used to view and execute certain queries. They work and people are happy with them but since a move from Oracle they need to be altered. It is kind of irrelevant what the function of the Access databases is because they cannot be broken or rewritten plain and simple.