texts: how to write conditional where clause query

texts:
Hi I want write the mysql query conditionally. I am basically using the Wordpress, please have a look at my same tables.

tbl1

id      name

1   A
2   B
3   C
4   D
5   E

tbl2

id    post_id   key             value

1   1   m_type          1
2   1   m_date          2013-11-22
3   2   m_type          2
4   2   m_date          2013-11-20
5   2   m_routine       2
6   3   m_type          2
7   3   m_date          2013-11-10
8   3   m_routine       null
9   3   m_custom_rountine   5

I have the maintenance date in tbl2. So I want to get the all the post id filtered by following types.

Maintenance

Due today
Week due.
Year due.
over due.

tbl2 has the due date m_date. But My problem when m_type = 1. I need to add the where clause like WHERE CURDATE() = m_date else m_type =2 means m_date will be the starting date of maintenance and I need to increment the date using m_routine or m_custom_routine.

For example,
m_type = 2
m_date = 2013-11-22
m_routine = 2

Then I want check

CURDATE() = 2013-11-22 OR 2013-11-24 OR 2013-11-26 etc.

I was thinking to use CASE .. WHEN... THEN in WHERE clause. But I am not getting it properly.

Desired output

CURDATE() = 2013-11-22

Output post_id = 1,2 (2-routine incremented date from 2013-11-20)

CURDATE() = 2013-11-15

Output post_id = 3  (custom_routine incremented date from 2013-11-10)
Hope I explained well enough. Please someone help me.

SUDO code

if(m_type == 1){
    m_date = CURDATE();
}
else if(m_type == 2){
    increment_date = m_date increment m_rountine (OR) m_date increment m_custom_rountine
    CURDATE() = increment_date
}

Edit:-

I created the sql fiddle for your ref


texts: Upgrade Oracle APEX 4.0.2 to 5.1.1 error

texts:
I installed Oracle 11g XE on CentOS 7 and everything went fine.

I wanted to upgrade APEX to the latest version and followed instructions in Upgrading Oracle Application Express within Oracle Database 11g Express Edition (XE). Did everything that said and get following error:

ORA-24344: success with compilation error ...
in create_upgrade_triggers for ...
create or replace trigger wwv_flow_upgrade_trg133
before insert or update or delete on WWV_PURGE_WORKSPACE_RESPONSES begin


texts: GUID as reason for MySQL import slowness

texts:
We faced MySQL restores DB VERY slow: ~3 min for 15 MB dump. This is a new dedicated server with a lot of RAM.

We were told the most possible reason is GUID is uded as primary key in many tables:

CREATE TABLE `acl_actions` (
  `id` char(36) NOT NULL,
  `date_entered` datetime NOT NULL,
  `name` varchar(150) default NULL,
  ........
  `category` varchar(100) default NULL,
  `deleted` tinyint(1) default '0',
  PRIMARY KEY  (`id`),
  KEY `idx_aclaction_id_del` (`id`,`deleted`),
  KEY `idx_category_name` (`category`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The explanation:

  This will certainly affect bulk insert performance as in the case of
  a dump import as the MYI file which holds indexes will be doing random
  I/O rather that the boost it would receive for sequential I/O in the
  case of a monotonically incrementing integer. Your key_buffer_size
  also comes in to play but does not seem to be undersized in this case
  based on key blocks free and the watermark key blocks used.

I'm not sure with that person and moreover, I have not enough experience myself, so I have to ask here:
Why on Earth there will be random IO? Because id is almost random so index's content will be not-so-linear?

Is it possible to decrease performance so drastically because of GUIDs?
Is there any workaround except replace UIDs to autoincremental ID globally?

UPD: after all we found the main reason for slowness: binlog. We have disabled binlog and this reduces import time x12!


texts: DELETE statement execution cancellation in SSMS

texts:
I ran a delete statement with wrong WHERE condition (which deletes all records from table instead of just 10 records) but during execution I found it and CANCELLED the execution in Management Studio,  which is still in query execution cancellation process (its a big table).

As far as I know if my cancellation completes successfully I don't lose any data, is my understanding correct? if not can anyone please tell me is there a way to recover deleted data from transaction log. The database is in simple recovery model.


texts: Differential backup same size as of Full Backup

texts: I have a SQL Server database of almost 2 TB size, it has tables with image datatypes. Now the size of differential backups are same as that of full backup. Does image datatype makes any difference to this? Or am i missing something.


texts: Are many NULL columns harmful in mysql InnoDB?

texts:
As I explored the clustered B-tree index system of InnoDB, I think that the presence of many NULL (or small) columns has no significant effect on InnoDB performance.

Does the presence of excess columns slow down mysql performance?

P.S. I tried to practically test, but no significant effect. However, I think it should be compared under heavy load. This is the reason that I am curious to learn about technical reasoning on this matter.


texts: How to connect using PDB userid on Oracle 18c XE?

texts:
I have created a PDB on Oracle 18c XE using following SQL command

create pluggable database virlux
admin user test1admin identified by test1pwd
file_name_convert = ('/pdbseed/', '/test1/')
;

Now, I try to connect directly to this PDB using new Userid using following command

sqlplus test1admin/test1pwd@virlux

and Oracle answer by error ORA-12541: no listener

I have then tried with SYSTEM userid with following command

sqlplus SYSTEM/syspwd@virlux

and I have obtained same error number !

What happens ?

I have set ORACLE_HOME and SET_ADMIN variables correctly.

My tnsnames.ora file found in %TNS_ADMIN% folder contains following lines

# tnsnames.ora Network Configuration File: D:\Oracle\dbhomeXE\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

VIRLUX =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = VIRLUX)
    )
  )

XEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XEPDB1)
    )
  )

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

I have just added VIRLUX in this file.

I have also stopped and started all Oracle services using net use commands.

The listener.ora file contains following lines

# listener.ora Network Configuration File: D:\Oracle\dbhomeXE\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

DEFAULT_SERVICE_LISTENER = XE

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\Oracle\dbhomeXE)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\Oracle\dbhomeXE\bin\oraclr18.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

When I start LSNRCTL STATUS command, I obtain


texts:
We have a table structure returned by this function:

RETURNS TABLE (ticker VARCHAR,
           current_recommendation INTEGER,
           previous_approved_recommendation INTEGER,
           date_appended TEXT,
           suggested_recommendation INTEGER,
           date_suggested TEXT,
           suggested_by VARCHAR,
           approved_recommendation INTEGER,
           date_accepted TEXT,
           approved_by VARCHAR,
           recommendation_status INTEGER
           )

On certain conditions, rather than returning values, we want to return just the first & last values, and everything else is NULL.

v_select := 'SELECT ''' || p_ticker || ''',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,' || v_status;

But it is not working:

ERROR:  structure of query does not match function result type
DETAIL:  Returned type text does not match expected type character varying in column 1.

Any help would be great.  We are all kind of new at Postgres, after migrating some thing from ORacle.


texts: Iterating over the output of a SQL query with PowerShell

texts:
I have about 300 databases, (most) with a table, [File], and a column [Location] that holds a UNC path to a file. While migrating SANs, some needed files may not have been copied.

This issue materializes when a web GUI attempts to open a file from the table that doesn't exist. I've been trying to put together some PowerShell to iterate over the locations and run Test-Path, since that seems like the most direct way to perform a check.

I'm running into problems in two places:

I also need to check if the file exists in the old location, so I perform the REPLACE on [Location] to get the old SAN name in my query, but can't seem to parse the output correctly.

I would like to Copy-Item directly from what passes the test, but again, can't seem to parse the output correctly.

What am I missing? It's really the last two lines I can't figure out.

Code is below, and hacked together from an example HERE:

$MS='Microsoft.SQLServer'
@('.SMO') |
     foreach-object {
    if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null)
           {"missing SMO component $MS$_"}
     }
set-psdebug -strict
$ErrorActionPreference = "stop" #

$My="$MS.Management.Smo" #
@("Instance1","Instance2", "Instance3") |
   foreach-object {new-object ("$My.Server") $_ } |
    Where-Object {$_.ServerType -ne $null} |
      Foreach-object {$_.Databases -match "DataPattern[0-9]"} |
         Foreach-object {
         $Db=$_
         $_.ExecuteWithResults(‘IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE TABLE_SCHEMA = ''schema'' AND TABLE_NAME = ''File'' AND COLUMN_NAME = ''Location'') SELECT [Location], REPLACE(Location, ''Computer'', ''Old-Computer'') [OldLocation] FROM [schema].[File]’).Tables[0]}|
                ForEach-Object {if(!(Test-Path -Path $_ ) -And (Test-Path -Path $_ -eq     True)) {
                    Copy-Item $_ $_ -ErrorAction Continue } }

Thanks

EDIT:

Ended up getting it to work with the below two lines of code, but it's incredibly slow if I select anything more than the top 50 lines from each table. Any tips on speeding this up?

ForEach-Object {if(!(Test-Path -Path $_.Location ) -And (Test-Path -Path $_.OldLocation -eq True)) {
Copy-Item $_.OldLocation $_.Location -ErrorAction Continue } }


texts:
I'm new in handling MySQL database, I made a tracking system I using the MySQL database incorporated on the xampp package as a database. Everything is running smoothly, however upon reaching 90,112 kb on the idb file of one of the tables from the database, it crashed and it cannot be used anymore. I cannot start the server due to the table.
It shows the following error.

  [Apache] Error: Apache shutdown unexpectedly.
   [Apache] This may be due to a blocked port, missing dependencies,
   [Apache] improper privileges, a crash, or a shutdown by another method.

As a temporary action I deleted the table a made a new one with the same structure and imported all the data that I have extracted before. Now it seems that it will happen again as the idb file of the table is reaching 90,112 kb and the system is slowing down again. Is there a limit on the table memory? Is there a way I can prevent this from happening?  Please help me out.

Thanks in advance


texts: SPI postgresql - insert data into table from postgres.c

texts:
I'm working inside postgres.c (inside exexc_simple_query) of postgresql 9.1 (line 1094 of http://doxygen.postgresql.org/postgres_8c_source.html#l00829 ). I need to insert some query information  in a table that I will check in a second moment.
I'm trying to do it with SPI using these three lines:

SPI_connect();
SPI_exec("INSERT INTO testvalues (11,6)", 5);
SPI_finish();

but when I start the server and I send a query I get segmentation fault:

LOG:  server process (PID 13856) was terminated by signal 11: Segmentation fault
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
FATAL:  the database system is in recovery mode
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted; last known up at 2013-07-14 00:40:22 CEST
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 0/17D7368
LOG:  redo is not required
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections

Do you have any idea how I can fix it? Is there a better way to insert data from the postgres.c?


texts: MySQL - Search Alphanumeric string using wildcards - underscore (_) and asterisk (*)

texts:
I have a table, products, with a product_id column as follows:
product_id
110177
110177T
177

I am trying to filter down the product results that should fetch result as below but it is also fetching the id 177
110177
110177T

Query - select * from products where produuct_id like %'177'%
What updates in query to discard the string 177 in search result and only fetch rest of two?


texts: OPENQUERY giving wrong result on linked server

texts:
I have setup linked servers on my SQL Server and choose Oracle as a linked server.
SOMETIMES, When I run the query below under the SA username, I get different result. However when I run under my own username I get correct result. I Can't understand this anomaly. The below query returns only one row. The difference occurs in ORGANIZATION_FULL_NAME column only.
SELECT * FROM OPENQUERY(HR_ORACLE,'
            SELECT e.CHILD_ORG_IND_MGR_POS_PER_ID,e.CURRENT_EMPLOYEE_FLAG,e.EMAIL_ADDRESS,
            e.ENTERPRISE_MANAGER_PERSON_ID,e.ENTERPRISE_MANAGER_POSITION_ID,e.FIRST_NAME,e.LAST_NAME,e.FULL_NAME,e.GENDER,e.JOB_CATEGORY_CODE,e.JOB_CATEGORY_NAME,
            e.JOB_ID,e.JOB_NAME,e.JOB_SUBCATEGORY_CODE,
            e.JOB_SUBCATEGORY_NAME,e.MOBILE_PHONE_NUMBER,ltrim(rtrim(e.NATIONAL_IDENTIFIER)) NATIONAL_IDENTIFIER,e.ORG_CURATOR_PERSON_ID,e.ORG_STR_DEDUCTED_MGR_PERSON_ID,e.ORGANIZATION_FULL_NAME,e.ORGANIZATION_ID,
            e.ORGANIZATION_SHORT_NAME,e.PARENT_ORG_IND_MGR_POS_PER_ID,e.PARENT_ORG_IND_MGR_POSITION_ID,e.PATRONYMIC,e.PERSON_ID,e.PERSON_IS_CURATOR_FLAG,e.PERSON_IS_ENTERPRISE_MGR_FLAG,
            e.POS_ORG_MGR_POSITION_FLAG,e.POSITION_ID,e.L3_PARENT_ORGANIZATION_ID
            FROM XXRCR.XXRCR_CUR_EMPS_EVER_V e
            WHERE e.EMAIL_ADDRESS like (''%EMAIL@EMAIL.COM'')  ')

These are my linked server settings:

and view definition:


texts:
I am trying to set up a Transaction Replication. I encountered error when i created a Publisher.While creating publisher i gave SQL Server agent accounts for Snapshot Agent and Log Reader Agent. When I checked the Log reader agent it is running, but Snapshot agent is giving error message as below:

  Access to the path 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\ReplData\unc\EAICS-SQL-01_TEST_REPL_1_TEST_PUB2\20180502143109\' is denied.

I went to that path and there is no \unc\ folder created.
Do you have any suggestions?

Additional details

I gave service account for SQL Server Agent and DB engine as NT AUTHORITY\NETWORK SERVICES. So I believe it is not a domain service account. SQL-1 and SQL-2 are my server names. I created Publisher at SQL-1, so SQL-1 server account will already have access to the folder right?


texts: Queries are performing bad after SQL Server 2008 SP4 upgrade

texts: We had SQL Server 2008 SP2 and Microsoft told us to upgrade it to SP4 if we wanted any support from them. Now that Microsoft has warned us, my path to go to SQL Server 2016 SP1 is clear. Going back to my question, I upgraded our production servers on Saturday to SQL Server 2008 SP4. But stored procedures which were performing fast (this is a relative term) are running way too slow on Monday. I don't see any blocking. Users are not Memory pressure seems normal. I see Available as well as Free Memory when I open task manager. CPU is a bit high. But not pegged at 100%. My guess is, plan cache was flushed after rebooting servers post upgrade and queries are taking hit today. All index maintenance jobs were successful prior to upgrade. Should I have run full index maintenance after the upgrade? I ran DBCC CHECKDB after upgrade and it was clear. Replications are in sync. Modified Stats are updated daily. Let me know if you need any more information. Any insight will be a great help. Thank you.


texts: Why do I need DAC if I am able to connect to the server via sqlcmd?

texts:
DAC attracted my attention at the moment when I was unable to connect to the SQL Server using a traditional way (the reason was deleting logon trigger). I made some searches on the web and it turned out that the only way to access the Server was to use DAC connection which unfortunately was turned off by default. However, I was able to overcome the problem (I removed trigger) using just sqlcmd.

My question is if I can connect to the SQL Server just using sqlcmd, what is the purpose of DAC then?


texts: creating a table from a selection and then using group by - oracle sql

texts:
I'm working in oracle and I can't figure out what's wrong with this statement.  The error it gives me is must name this expression with a column alias

CREATE TABLE shipping_costs_grouped AS
    SELECT SUM(invoice_amt), SUM(entered_weight), SUM(billed_weight), SUM(net_amt), MAX(ZONE), pps_num
    FROM shipping_costs GROUP BY pps_num;

I know it has something to do with the group by statement but I don't know how to resolve it


texts:
Is there a way of run an alter table command with cast on MySQL databases?

I know that is possible to do it with Postgresql and Sql servers, but I didn't find anything about it when searching the documentation..

My database is very large and I want to convert Decimal to BigInt, so I'm trying to do it with a single command and avoid workarounds..


texts: Do data types go into a Relational Data Model (RDM)?

texts:
I have recently been asked to draw a Relational Data Model for a flight information database. I came up with the design in the picture below, but I was told that the drawing is not correct because that is not quite what is meant by RDM and also because the data types are not included. Is this true? I have looked into many articles and books and I have not seen yet any RDM with data types in it.

Thanks!


texts: Can I alter a view being referenced with schemabinding if the change fulfills the binding requirement?

texts:
In SQL Server 2008, I have views WITH SCHEMABINDING and I need to alter one.

I am changing a column while not changing its alias so consumers will be unaffected.

If I were to drop all my other views that depend on this I would be able to recreate them again, so SCHEMABINDING is still valid.

Can I temporarily disable the schema lock on this view in order to ALTER it?

Or must I drop the dependent views and recreate them once it's changed?


texts: I was wondering what defense MySQL server uses to protect against brute force attacks on the user credentials? I hope this isn't too simple of a question! I have tried to Google it, but can't seem to find really any clear cut answer.


texts: MySQL Select all clients that searched for two different items or more

texts:
I have a table like this :

serial,ip,term
1,1.2.3.4,bag
2,2.3.4.5,shampoo
3,1.2.3.4,handbag
4,3.4.5.6,clothes
5,2.3.4.5,battery
6,4.5.6.7,lamp

I want to select all IPs that did search for multiple different items while excluding the term 'bag' from the selection (in this case I am looking for 2.3.4.5)

Thank you very much


texts: First run of planner very slow on massively partitioned PostgreSQL 9.6 table

texts:
I have a rather massive table of financial data in Postgres 9.6. It is partitioned on two levels, first by trading day (DATE type) and subsequently by security ID (BIGINT). Overall the table comprises 20 per-day paritions which are each further subdivided into 15 per-instrument partitions. I use the conventional method of partitioning using CHECK constraints. The partitions are unlogged tables which I load in-bulk via COPY.

When prforming EXPLAIN ANALYZE on trivial queries which can be satisfied from a single partition I see that the first run of the planner can take up to 30 seconds in some cases. I suppose this has to do with some attributes of the tables which the planner uses taking time to load into memory. What's causing this behavior? How can I "warm up" the database to avoid this lengthy planning which surprises my users?

Example EXPLAIN ANALYZE output:

trading_offline=# EXPLAIN ANALYZE--
select * from public."ticks"
where trading_day ='2016-06-20'::date and inst_id= 1535681 and is_trade = true
and qty<-200;

First time - planning took 2.4 seconds:

                                        QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Append  (cost=0.00..0.00 rows=1 width=161) (actual time=0.016..0.016 rows=0 loops=1)
->  Seq Scan on ticks  (cost=0.00..0.00 rows=1 width=161) (actual time=0.014..0.014 rows=0 loops=1)
        Filter: (is_trade AND (qty < '-200'::integer) AND (trading_day = '2016-06-20'::date) AND (inst_id = 1535681))
Planning time: 2415.293 ms
Execution time: 0.051 ms
(5 rows)

xeur_trading_offline=# explain analyze select * from xeur."ticks"
where trading_day ='2016-06-20'::date and inst_id= 1535681 and is_trade = true
and qty<-200;

Subsequent times - planning is down to ~ 40 ms:

                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Append  (cost=0.00..0.00 rows=1 width=161) (actual time=0.017..0.017 rows=0 loops=1)
->  Seq Scan on ticks  (cost=0.00..0.00 rows=1 width=161) (actual time=0.017..0.017 rows=0 loops=1)
        Filter: (is_trade AND (qty < '-200'::integer) AND (trading_day = '2016-06-20'::date) AND (inst_id = 1535681))
Planning time: 42.067 ms
Execution time: 0.062 ms
(5 rows)

                       QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Append  (cost=0.00..0.00 rows=1 width=161) (actual time=0.024..0.024 rows=0 loops=1)
->  Seq Scan on ticks  (cost=0.00..0.00 rows=1 width=161) (actual time=0.024..0.024 rows=0 loops=1)
        Filter: (is_trade AND (qty < '-200'::integer) AND (trading_day = '2016-06-20'::date) AND (inst_id = 1535681))
Planning time: 42.082 ms
Execution time: 0.070 ms
(5 rows)


texts: Is there any point in specifying the precision and scale of a number for a foreign key?

texts:
I am running Oracle 11gR2 11.2.0.3. I have two tables:

table1 contains a column named SUBJECT_ID, whose datatype is NUMBER(7,0) (numbers range from 13 to 48480)
table2 contains a a column named SUBJECT_ID, whose datatype is NUMBER, and is a foreign key to table1.SUBJECT_ID.

Is there any point in specifying the precision and scale of table1.SUBJECT_ID (i.e. declaring it NUMBER(7,0) instead of simply NUMBER)?

The Oracle documentation advises to specify the precision and scale, as "it provides extra integrity checking on input". But in my case wouldn't the foreign key constraint take care of this integrity checking?


texts: Where do this Constant Scan and Left Outer Join come from in a trivial SELECT query plan?

texts:
I have this table:

CREATE TABLE [dbo].[Accounts] (
    [AccountId] UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID(),
    -- WHATEVER other columns
);
GO
CREATE UNIQUE CLUSTERED INDEX [AccountsIndex]
    ON [dbo].[Accounts]([AccountId] ASC);
GO

This query:

DECLARE @result UNIQUEIDENTIFIER
SELECT @result = AccountId FROM Accounts WHERE AccountId='guid-here'

executes with a query plan consisting of a single Index Seek - as expected:

SELECT <---- Clustered Index Seek

This query does the same:

DECLARE @result UNIQUEIDENTIFIER
SET @result = (SELECT AccountId FROM Accounts WHERE AccountId='guid-here')

but it's executed with a plan where result of Index Seek is Left Outer Joined with result of some Constant Scan and then fed into Compute Scalar:

SELECT <--- Compute Scalar <--- Left Outer Join <--- Constant Scan
                                      ^
                                      |------Clustered Index Seek

What's that extra magic? What does that Constant Scan followed by Left Outer Join do?


texts: EventID 1196 | SQL Cluster & FailoverClustering

texts:
I have Pre-Staged the SQLCluster objects, since login had no Domain admin rights.
Later SQL Cluster installation completed successfully.(Also tested with failover and failback).It is working fine till date.

But I am receiving error in event log of failover cluster, that Cluster resources failed registration of one or more associated DNS name(s).

Can anybody explain its impact?

Require help to understand below error.

**
Cluster network name resource 'SQL Network Name (servername)' failed
registration of one or more associated DNS name(s) for the following reason:
DNS operation refused.
**


texts: Why should the most restrictive condition be listed last in the where clause?

texts:
The book I'm going through has given a rather poor explanation of why exactlt to place the most restrictive condition last in the WHERE clause.

Say that we have Query 1:

SELECT COUNT(*)
FROM TEST
WHERE LAST_NAME = 'SMITH'
AND CITY = 'INDIANAPOLIS';

And Query 2:

SELECT COUNT(*)
FROM TEST
WHERE CITY = 'INDIANAPOLIS'
AND LAST_NAME = 'SMITH';

and the first query takes 20 seconds, while the 2nd query only takes 10. The book goes on to say that "Because the 2nd query returned faster results and the most restrictive condition was listed last in the WHERE clause, it is safe to assume that the optimizer reads the WHERE clause from the bottom up.

I'm confused as to how I'm supposed to make that inference. Is it that filtering after 1 condition is much slower than the first filter?


texts:
We are using Maria DB (v5.3 I believe) in our production environment.

One issue that we have, is that historically, the dev user (which has full rights and privileges) is used in all applications that connect to the db. We've started making application specific users and replacing those with the dev user.

Once we replace the dev user, the plan is to limit the rights/privileges on the (dev) user to only read only.

I believe we've now replace the majority of the application users from dev to their app specific user. However, I'm not 100% certain that no app is still using the dev user.

My question is, is there sql, or logs, that tell me the users who connected to to the db's w/in the past day/week/month, etc? How can i tell who connected to the db from a historical point of view?


texts: Why do case/iif return nullable datatypes?

texts:
Consider the following sproc:

create or alter procedure TestSproc
as
begin

declare @ZeroBit bit = 0, @OneBit bit = 1

-- Table.Id is a nullable integer column
select iif(Table.Id is null, @ZeroBit, @OneBit) as ColumnNotNull

end

Once it's been compiled, running the following:

select name, system_type_name, is_nullable
  from sys.dm_exec_describe_first_result_set_for_object(object_id('TestSproc'), 0)

shows the ColumnNotNull column correctly typed as a bit, but that it is nullable.

The nullability does not make sense to me, since the arguments I passed to iif are non-null constants, so why is the resultant column nullable? And is it possible to get it to be non-nullable without wrapping the expression in isnull to get around this, which to my mind is - or rather, should be - unnecessary?

The documentation for iif makes no mention of nullability, merely stating that this function "Returns the data type with the highest precedence from the types in true_value and false_value".


texts: Why can an Oracle SET UNUSED COLUMN cause internal UPDATE SET X=X and huge log generation?

texts:
On Oracle 11.2.0.4.1, we tried to run a simple ALTER TABLE PARTITIONED_LARGE SET UNUSED COLUMN XXXXX; on a large ref-partitioned child table to decommission a column.

The table has hundreds of millions of rows across thousands of partitions. Most partitions have been truncated and no segments left; probably about 220 of them have data. As per standard practice the point of the SET UNUSED COLUMN was to avoid redo generation and long downtime, while allowing us to regain space moving forward.

Something seems to have gone horribly awry; it started generating dozens of GB of redo logs; eventually filling up UNDOTBS; then failed with the below and started rolling back.

Fri Apr 24 23:48:13 2015
ORA-01555 caused by SQL statement below (SQL ID: 1ksq8yfdxv3ys, Query Duration=13500 sec, SCN: 0x0b5b.6392295a):
update "SCHEMA"."PARTITIONED_LARGE" set "XXXXX" = "XXXXX"

Seeing the statement above it's pretty obvious why it's generating undo/redo, but why did Oracle decide internally to do such an update during a SET UNUSED COLUMN?!

Other points that may or may not be relevant

at the time the command was started, a restore point had been created for flashback purposes. This restore point was dropped part way through when flashback log size became a problem
the column is defined as BINARY_DOUBLE DEFAULT 0 CONSTRAINT XXXXX_NN NOT NULL
the SET UNUSED had been earlier tested on a test DB with about 75% of the data of the DB in question (but not exactly the same data) and had no issues; ran within seconds.

Can anyone explain this behaviour, either in terms of expected behaviour or known issues?


texts: Why can't I change the isolation levels of my connection in MySQL?

texts:
I am trying to experiment a bit with the isolation level of MySQL in a test environment.
I do the following:

mysql> set @@session.tx_isolation='READ-UNCOMMITED';
ERROR 1231 (42000): Variable 'tx_isolation' can't be set to the value of 'READ-UNCOMMITED'

This also fails:

mysql> update information_schema.session_variables set variable_value='READ-UNCOMMITED' where variable_name='TX_ISOLATION';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

What am I doing wrong here? I am root. So it should not be a rights issue.


texts:
Oracle 12c database comes with default Unified auditing enabled even if we set it to FALSE which has ORA_SECURECONFIG policy enabled by default.

Apparently, the audit options for that particular policy captures many default options such as Alter and create statements.

My question is, by default for any new installation, mixed mode is enabled which means that the traditional auditing is enabled and also Unified auditing is enabled as well both capturing the same audit_options.

Why is it that way? What is the point of Mixed mode? When by default tradionally Oracle audits certain user actions, why should ORA_SECURECONFIG should also audit the same default user actions and write to AUDSYS schema?

And if I need to keep only Unified auditing, then how to disable traditional auditing? Should I change audit_trail from DB to NONE?


texts: Can mysql restore a single table from a large mysqldump?

texts:
What is the best way to restore a single table from a large (~5GB) database mysqldump file?

update:
I have found solutions (posted below) using command line tools to parse the table, but is there a way to do this with mysqlimport?


texts: How to perform almost the same query N times without rewriting it N times?

texts:
Basically I have a list of numbers like:

1,2,3,4,5

And I want to perform a query like this for each of the previous numbers:

SELECT COUNT(*), field_name
FROM my_table
WHERE field_name = NUMBER

Where "NUMBER" will be 1, 2 ... 5 in this example. So I expect a result containing 5 rows and 2 columns.

Right now I'm doing the task this way:

(SELECT ... field_name = 1) UNION (SELECT ... field_name = 2 ) ... (SELECT ... field_name = 5)

Is there any alternative way to do this while still using a single query? In particularly I don't like how the last query may become very long as my list of numbers grows.

-----EDIT-----

What if the query to execute would be something like:

SELECT *
FROM my_table
WHERE field_name > NUMBER

Using the same mechanism as above but this time let's image that this query will return more than 1 row at every execution.

Is there any way to create a single, elegant, query to do this job instead of using "UNION" multiple times?


texts:
I've never come up against a similar situation and so I'm looking to gather some tips on how to accomplish this. Here's the situation:

I'm creating an application to track and manage sheet music and their accompanying information. Books can have many songs (Many-to-many). Where I get tripped up is when we start tracking the artists. Books can have multiple artists, but songs can also have different artists.

For example, a Garth Brooks compilation book (Garth is the artist for the book) has a song that was a duet with Trisha Yearwood. Trisha Yearwood isn't a co-artist for the entire book, but can be for multiple songs IN that book. I would like to relate Books to Artists as well as Songs to Artists so I can search and find all Books and Songs that Garth Brooks has been an artist in.

-------
|Books|
-------
|id   |
|name |
-------

-------
|Songs|
-------
|id   |
|name |
-------

//Join Table for Books and Songs
--------------
|Book_songs  |
--------------
|id          |
|books_id FK |
|songs_id FK |
--------------

---------
|Artists|
---------
|id     |
|name   |
---------

Would this be as simple as a separate join table for both Books and Songs?:

----------------
|Book_artists  |
----------------
|id            |
|books_id   FK |
|artists_id FK |
----------------

----------------
|Song_artists  |
----------------
|id            |
|songs_id   FK |
|artists_id FK |
----------------

Or is there a better way to design these relations?

Thanks for your help in advance!


texts: PL-SQL query to find the most recent record the "Operation_date" of which is lower than a specified date

texts:
I have a table with below structure:
create table DOC_AMOUNT_DETAIL
(
  col_id         NUMBER,
  amount         NUMBER,
  operation_date DATE
)

some example data for this table is :
col_id    |  amount   |  operation_date
---------------------------------------
  1       |  5000     |  11/1/2020
  2       |  1000     |  11/1/2020
  3       |  3000     |  11/1/2020
  1       |  1000     |  11/14/2020
  2       |   500     |  11/14/2020

If the amount column of each col_id changes , a new record will be inserted in the table with a new operation_date which is exactly the date of the change. What I need from the table is the amount for each col_id which has the max(operation_date)<= (a specific date for example 11/15/2020) by this I mean I need the last update of the record the operation_date of which is lower than the date I'm querying the table. So my desired result is this :
    col_id    |  amount
    --------------------
      1       |  1000
      2       |  500
      3       |  3000

What I've written so far is this and it gives me the correct output:
select d.col_id,
       d.amount
  from doc_amount_detail d
 inner join (select t.col_id,
             max(t.operation_date) operation_date
               from doc_amount_detail t
              where t.operation_date <= to_date('11/15/2020', 'mm/dd/yyyy')
              group by t.col_id
             ) d2
    on d.col_id = d2.col_id
   and d.operation_date = d2.operation_date

and I want to know whether there are better ways of writing this query.
Thanks in advance.


texts: Any pointers on reducing my privileges in production but not making my job excessively difficult

texts:
Running SQL Server 2005 and 2008 on Windows 2008 R2.

We're going to be reducing privileges in production for developers - and I'd like to do the same for myself as a DBA, limiting rights to production and elevating when required.

My primary goal would be to eliminate stupid mistakes - made by DBAs, devopers will have read access in production at the most.  We like to act like we are superheroes that cannot make a mistake, but not having production rights all the time makes sense and it a best practice, recommended by some.

What's the best approach?  What will be least painful to use day to day and during installations?

We currently have a windows group for DBAs that has rights to all of our servers and databases.

I'd also be interested in lowering OS / remote login permissions - but I'm most concerned with DB rights.

I'm guessing we'd need elevated privs to run traces as sa, and possibly to some ownership cleanup before we take away our old login's SA rights.  What other problems might we expect?

Thanks for your advice and sharing your experiences!


texts: Performing data update operations while backing up a large SQL Server database

texts:
I have a large (in the tens of millions of records) database that I am going to perform a full database backup on.

However, the database is large enough that transactions can start before and during, as well as commit during and after the backup takes place.

For example:

T0 = Transaction A start
T1 = Full database backup start
T2 = Transaction B start (will not deadlock with A)
T3 = Transaction A commit/rollback (does not matter, does it?)
T4 = Full database backup end
T5 = Transaction B commit/rollback (again, does not matter, does it?)

T0          T1          T2          T3          T4          T6
||----------||----------||----------||----------||----------||---------->

My understanding is that no locks are used during a backup (although other performance problems may arise due to say, high I/O), but I'm not sure what I can guarantee what will be committed or not.

Also, my concern isn't that the database will be in an inconsistent state, but rather, what that state will be (even if it's not deterministic, if there's a set of rules that can be consistently applied) and how it got there (for example, how much of the data file is used along with the transaction log to create a backup file)?


texts: Let the data have been backed up at time interval of 2 hr and taking backup at time "A" and after 1 hr Database crashes so how can I recover in MySql from Logs.


texts: SQL Queries Slow in the Upgraded environment

texts:
I setup and configured an new SQL database server with SQL Server 2016 Standard Edition.
This is going to replace our existing SQL database server which is 2008 R2 database server.
I restored recent backup form the current database server(SQL Server 2008 R2) and pointed the application to the new database server.
But when i run queries from the application,the queries are very slow.Even those queries which used to run fast current database server is very slow in the new server.
New database server hardware specs are much better then the current database server.

This is the plan for one of the query which is taking almost 3 minutes to execute.

This plan is from current server(SQL Server 2008 R2) and it takes 10 seconds only for the same query.

I have been trying out what is causing this slow performance.
Steps i tried.

I changed the compatibility of the database from 2008(100) to 2016(130) after restore.
Tried to find changes in the execution plan.
Saw Implicit conversion warning in the execution plan,but the column mentioned in that message is not found in the query.

FYI,the sql queries are generating from the application using linq and not stored as a stored procedure.

See the attached screenshot when i run EXEC sp_BlitzFirst @SinceStartup = 1;

Rebuilt Indexes and Updated statistics,but not much help as of now.
I have noticed a SORT operation with cost 95% ,but i already have an index for that SORT filed,so i don't think that SORT is an issue.Correct me if i am wrong.

I have noticed that in the actual execution plan,for most of my operation Estimated Number of execution and Actual number of execution is having big difference.What i should make of that?


texts: Where is the information about what files each database stored for SQL Server 2008 R2

texts:
I need to find where SQL Server 2008 R2 stores what files back each database. The kicker is that the server has completely crashed (hardware failure). I've managed to recover all the .mdf/.ldf files.

I need to get a single database up and running on another server asap, but whoever set the server up originally didn't match the database names to the file names very well, so I need to somehow extract the mapping between the two without going through a full restore of SQL Server, i.e. I need to know the names of the files for database XXXXX as it was attached to SQL Server.

Is this possible? Was thinking about attached the recovered master database onto another SQL Server instance, but I cannot seem to find any information about data files in another master database, so it might be a waste of time.

Thanks


texts: What does 'nls' in nls_date_format stand for?

texts:
For example:

alter session set nls_date_format = 'DD-MON-YYYY hh24:mi:ss';

changes the date format for the session.

What is nls here?


texts: T-SQL script to perform a compressed backup with maximum compression level

texts:
I have a database with full backup of almost 2GB.
When I perform a full backup (without compression) and then compress it with winrar it become 140MB, but when I backup WITH COMPRESSION syntax the result is 315MB, and unfortunately after compression with winrar the size not changes.
How can I use T-SQL for full compression at maximum level?


texts: Calculate all latitude and longitude distance in result using MySQL

texts:
this is my current query to calculate between two latitude and longitude, but how do i using this or similar formula to calculate all the latitude and longitude in result?

SELECT ROUND(6353 * 2 * ASIN(SQRT( POWER(SIN((a.GPSLat -
      abs(b.GPSLat)) * pi()/180 / 2),2) + COS(a.GPSLat * pi()/180 ) * COS(
      abs(b.GPSLat) *  pi()/180) * POWER(SIN((a.GPSLon - b.GPSLon) *
      pi()/180 / 2), 2) )), 2) as TotalKM
from table1 a
      inner join table1 b on a.ID = 70 and b.ID = 71;

This example of Lat and Lon in my database

The result count by id70 and id71

Edited : The result that i need is

(id69 + id70 = 0.4km),

(0.4km + id71 = 0.7km),

(0.7km + id72 = 1.0km),

totalkm is 2.1km

ID | GPSLAT   | GPSLon     | DistanceInKM
69 | 3.214502 | 101.638419 | 0.0
70 | 3.214478 | 101.638101 | 0.4
71 | 3.214668 | 101.638049 | 0.7
72 | 3.215089 | 101.638022 | 1.0
                           |TotalKM
                             2.1


texts:
I have mysql server running successfully.
The error log location is as..

mysql> show variables like '%log_erro%';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| log_error     | /var/log/mysql/err.log |
+---------------+------------------------+
1 row in set (0.00 sec)

The issue am facing is that when i am changing the error log location from above to some other location by changing from my.cnf  and then restarting the server but the error log is not getting generated and servers starts successfully.

EDIT

After restarting the server the value of the above variable get changed.
but i didn't found the error log in that location.


texts:
I'm trying to setup an SQL agent job to use my SSIS package. The package contains an FTP task that is failing. I'm getting the following error in both the job history and logging that I've setup (Log gives no other detail...):

  "Unable to connect to FTP server using "FTP Connection Manager"

I've tried setting up the SSIS package with the ProtectionLevel of EncryptSensitiveWithPassword. Going into the SQL job and entering the password. All the tasks run fine except for the FTP task.

I then tried setting up the SSIS package with the ProtectionLevel of DontSaveSensitive. I then setup a configuration file. Manually entered the password into the DTSConfig file. Chose the configuration file in the configurations tab of the step setup. But I'm still getting the same error when running the job.

The package runs fine when ran in visual studio.

Any ideas?


texts: sp_BlitzFirst - Wait Stats Interpretation

texts:
I have some BlitzFirst Wait Stats that look as follows:

Is this the proper way to interpret this? During the 30 second sample, there were a total of 627.1 seconds of IO_Completion waiting, an average of .5 waits per core per second and a total of 20,723 of these waits, each averaging 30.3 MS?

Also, I am not using the Since Startup option.


texts: Returning auto-generated primary key after SQL insertion

texts:
I am trying to submit data to a database and then have it return the value of the user_id (primary key) created by the insertion, but I can't get it to return the value. I use the same query in phpAdmin and it works just fine. I know this should be simple, but I just can't get it. Here is my code:

(I do have values to connect to the database, just didn't include them for privacy.)

$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_SITE)
OR die ('Could not connect to MYSQL: ' . mysqli_connect_error() );
$nameQuery="INSERT into nameReunion
    (username, password,email,firstName,middleName,lastName, maidenName,     lastUpdate)
VALUES
('$username', '$password', '$email', '$fname', '$miname', '$lname', '$maname', CURDATE());";

$r = @mysqli_query ($dbc, $nameQuery);

if(!$r) {
echo "The database was not updated.";
} else {
echo 'The connection was successful<br/>';
};

$getUserID="SELECT user_id FROM nameReunion WHERE username='$username';";
$r2=@mysqli_query ($dbc, $getUserID);
echo $r2;
echo $getUserID;

Please help. I have spent wayyy too much time on something I fear should be very simple.


texts: MYSQL TABLESPACE = innodb_file_per_table GIVING ERROR 1030 (HY000): Got error 168 from storage engine

texts:
I have tried this statement to place a tables in different disks. Operating system is Linux Oracle 7, I've changed permissions in the directory with chmod -vR 777, to have total access to the disk directory. I keep getting this error after running the following statement taken from mysql page directly

CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE = innodb_file_per_table
     DATA DIRECTORY = '/home/egonzalez/Documentos';

  ERROR 1030 (HY000): Got error 168 from storage engine

As far as I know, is a permission problem that mysql doesn't have access to the directory, but after changing permission in the directory I don't know how to proceed.


texts:
I have been experiencing some very slow queries for a specific table. This table grows daily. I ran the pg_relation_size function, which gives the size of a table in the database:

mydb=# select pg_relation_size('reports');
 pg_relation_size
------------------
      10318856192
(1 row)

Now I assume the return value is in bytes. So if I wanted gigabyte size, then this is basically saying 10.3 gigabytes. Could a table that is in 10 gigabytes in size really reduce the performance of queries against that table?


texts: Mysql slow copying to tmp table to indexing

texts:
I try to indexing table.
The table size is about 60GB.
But indexing query is hang on 'copying to tmp table' 18 hours.
I found advice setting 'myisam_sort_buffer_size'

myisam_sort_buffer_size : 128M
RAM : 32GB
Window Server, MariaDB

Is it Okay increase to myisam_sort_buffer_size setting value?
Could you recommened myisam_sort_buffer_size value?

What can I do to speed up add index?


texts: Whether or not to create separate tables

texts:
I am working on a project, where I need to store the information related to A and B, the A and B both have identical columns.There might be thousands of As and Bs. Should I use "type" column to differentiate between these two ? Is there any performance issue because each time it will require a full table search ?
I have 3 tables that are having similar structure for A and B.


texts: Making sure balance field is always consistently updated with triggers

texts:
This is my database schema, related to the problem.

This is one of the triggers:

CREATE OR REPLACE FUNCTION "public"."update_balance_bet"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
    DECLARE
    currentBalance INTEGER;
    BEGIN

    currentBalance = (SELECT balance FROM "public"."Users" WHERE id=NEW."UserId");
    UPDATE "public"."Users" SET balance=(NEW.amount+currentBalance);
    RETURN NEW;
END
$BODY$

CREATE TRIGGER bet_trigger
    AFTER INSERT ON "public"."Bets"
    FOR EACH ROW
    EXECUTE FUNCTION update_balance_bet();

Basically from my app I insert records into table Bets, Bonuses, Withdrawals and Deposits, then the rest of the work is done by triggers. My questions is whether the mechanism I used will guarantee consistency in all cases, or should I use raw locking? Am I right in assumption that inserting into Bets table is one transaction along with trigger function updating balance, so in case something goes wrong with insertion into Bets, balance wont be updated OR if something wrong goes with balance updating, new record wont be inserted into Bets table?


texts: PostgreSQL: websearch_to_tsquery precedence / predicate ordering

texts:
How does precedence work in websearch_to_tsquery? I have below example query:
websearch_to_tsquery('exp1 exp2 OR "multi word expression" -weak')

The issue I have is that the results include text containing 'weak' because they match 'exp1 exp2'.
However when I remove OR "multi word expression eg query being
websearch_to_tsquery('exp1 exp2 -weak')

then it works as expected and weak is not present in results.
I suspect the initial query is interpreted as
(exp1 and exp2) OR ("multi word expression" and not weak)

instead of my intention of
(exp1 and exp2) OR ("multi word expression") and not weak
So how would I write the later query using `websearch_to_tsquery?


texts: Sql Server Agent notification email  with custom message

texts:
I have set email notification on the completition of a scheduled job.

Is it possible to customize the email content?


texts: Best Practice for Handling Table with Large Amount of Transaction Data

texts:
I have a transaction table storing large amount of transaction data. Only the most recent data of one month are required to be available for transactional use in real-time. Less recent data may only be used for report generation or offline analysis.
What is the best practice for handling such transaction data?
Should I create a history table with same columns and move data with age over one month from the transaction table to the history table with a batch job?
Should I create a history table per year? Or just use a single history table to host all past data?
Or Should I use database partitioning instead of history tables?
Or should I create a data warehouse and move data into it?


texts: Match Against search not doing exact search?

texts:
I'm using MariaDB version 10.2.22 with a InnoDB engine, utf8 charset, it has one column that is indexed FULLTEXT. The table itself is only 4.4 GB with avg row length at 255.  When I do a query such as:
    SELECT m.id as id
    FROM
    table1 m,
    table2 d
    where
    m.id= d.id AND
    MATCH ([column]) AGAINST ('"CE20-2019"' in boolean mode)

I get a "SQL Error [128] [HY000]:Table handler out of memory". I suspect that the "-2019" is being searched or not included, I'm not sure, and that the found matches exceeds the memory allowed. The [column] is a "LongText" datatype.
I've tried changing the innodb_buffer_pool_size to 4 GB and then 8 GB, but still get this error.
(System has 24 GB of RAM, not sure if my comfortable changing it to 70% of RAM listed on their documentation found here: https://mariadb.com/kb/en/mariadb-memory-allocation/), since I'm on version 10.2.22, this innodb_buffer_pool_size variable is suppose to be dynamic so I don't have to restart the MariaDB service to restart Mysqld.
Any ideas what's causing the Table handler out of memory?


texts: Database stuck in restoring mode during log shipping

texts:
The error log say -
The database is marked RESTORING and is in a state that does not allow recovery to be run.


texts: Threads_cached 0 while thread_cache_size > 0

texts:
I'm trying to little bit understand with some MySQL (in my case - MariaDB) performance.

One point I found - "threads caching".

So - I set:

thread_cache_size = 4

in my.cnf and restart server.

In status I see:

MariaDB [mysql]> show status where variable_name like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 2     |
| Threads_created   | 23    |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

Next - I run mysqlslap:

$ mysqlslap -v -u user -p --auto-generate-sql --concurrency=10 --number-of-queries=100
...
Number of clients running queries: 10
Average number of queries per client: 10

Thus, if I correct uderstood, after clients disconnected - server must save 4 thread in cache to use it later, but:

MariaDB [mysql]> show status where variable_name='Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 0     |
+----------------+-------+
1 row in set (0.00 sec)

Threads_cached still have 0.


texts: Is the entire row logged when an UPDATE statement is executed?

texts:
I would like to estimate the logging impact of an UPDATE statement that changes one column on a few hundred thousand rows.  Does the entire row get logged, or just the one column?

I know that this can be done in batches with log backups running to keep the log size under control, but the crux of question is whether that will need to be done or not.


texts: Use a value for a Postgresql setting in a trigger

texts:
We're using a trigger to track changes in various database tables. This works fine. Now we want to be able to track the changes back to the web request that caused them.

I'd like to be able to use SET LOCAL for this, because it will also serve as a useful check that a transaction has been properly created. So the idea is to insert a row in a requests table and then use SET LOCAL pubsubpull.request_id to set the ID for the request which can then be used in the INSERT statement in the update log table generated in the trigger (if available).

I can see what the value is using SHOW pubsubpull.request_id, but how do I capture that value so it can be used in the INSERT table? SHOW doesn't seem to be legal in SELECT in any way. These user defined options don't seem to show up in the pg_settings view so I can't select it from there.

I have looked at using txid_current() but would prefer not to need to.


texts: SSRS appears to be ignoring Permissions set using Report Manager

texts:
I have setup SSRS on SQL Server 2008 in native mode.

As an administrator I can login to report manager, upload reports and run them, and also use the Web Service URL to generate reports.

I have also created a local user on the machine, I went into Report Manager as Admin, and at the top level set permissions that should assign the local user to all roles.

When I login to the machine as that user, and then navigate to Report Manager I just get the heading for the page, but do not see any of the folders that are configured.

I've checked and the folders are set to inherit parent permissions and they are showing the newly created local user in there too.

It seems odd that I have set the permissions, yet SSRS is still not showing what I should be able to see. Is there another step I need to take other than configuring the permissions in Report Manager?

When logged in as the newly created local user:

Report Manager - Shows the heading for the page, but no folders/items

Web Service URL (http://machine/ReportServer) - rsAccessDeniedError


texts: Does PostgreSQL btree_gin extension use btree or gin data structure?

texts:
In order to define a GIN index on an array and scalar field (for example) you need to enable the btree_gin extension in PostgreSQL. Then you can define your indexes as a GIN index.

However I don't understand if, under the hood, PostgreSQL is using a btree structure or a GIN structure.


texts:
I have a postgresql db (v9.4) and its default year is 2026.
Is there any way I can modify it?
I have read about modifying the postgresql.conf file, but I have just found the timezone property.


texts:
I need to migrate an existing Oracle Database into a Derby one. I want to know if there's a tool, a script or another way to do that work.

It is using any of the interesting features of Oracle, as I can see from the database information from SQL Developer, except sequences and indexes.

Thanks!


texts: Slow window function query with big table

texts:
I'm doing some performance testing on a new DB design on PostgreSQL 9.4rc1 and I'm seeing some pretty slow queries using window functions.  Here is my table setup:

CREATE TABLE player_stat (
  player_id    VARCHAR(200) NOT NULL,
  stat_id      BIGINT NOT NULL,
  value        BIGINT NOT NULL DEFAULT 0,
  last_updated TIMESTAMP WITH TIME ZONE NOT NULL,
  last_active  TIMESTAMP WITH TIME ZONE DEFAULT NULL,

  CONSTRAINT player_stat_pk PRIMARY KEY (player_id, stat_id),
  CONSTRAINT player_stat_fk1 FOREIGN KEY(stat_id) REFERENCES stat (id)
);
CREATE INDEX player_stat_stat_value_player_desc
  ON player_stat (stat_id, value DESC, player_id ASC);

I've inserted 30 million rows into this table split among 3 stats:

INSERT INTO player_stat (player_id, stat_id, value, last_updated) SELECT x.id, 1, x.v, now() FROM (SELECT generate_series(1,10000000) as id, trunc(random() * (1900-1200) + 1200) as v) AS x;
INSERT INTO player_stat (player_id, stat_id, value, last_updated) SELECT x.id, 2, x.v, now() FROM (SELECT generate_series(1,10000000) as id, trunc(random() * (1900-1200) + 1200) as v) AS x;
INSERT INTO player_stat (player_id, stat_id, value, last_updated) SELECT x.id, 3, x.v, now() FROM (SELECT generate_series(1,10000000) as id, trunc(random() * (1900-1200) + 1200) as v) AS x;

Then I try to rank the players for a given stat (EDIT):

SELECT * FROM
( SELECT player_id
       , rank() OVER (ORDER BY value DESC, player_id ASC)  as rank
  FROM player_stat
  WHERE stat_id = 1
) as t
WHERE rank <= 20
ORDER BY rank ASC;

This query takes about 5.5 seconds to return.  Running Explain on it returns the following:

"Sort  (cost=1167612.28..1176082.26 rows=3387993 width=15) (actual time=9726.132..9726.135 rows=20 loops=1)"
"  Sort Key: t.rank"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Subquery Scan on t  (cost=0.56..684349.57 rows=3387993 width=15) (actual time=0.080..9726.116 rows=20 loops=1)"
"        Filter: (t.rank <= 20)"
"        Rows Removed by Filter: 9999980"
"        ->  WindowAgg  (cost=0.56..557299.83 rows=10163979 width=15) (actual time=0.077..8351.124 rows=10000000 loops=1)"
"              ->  Index Only Scan using player_stat_stat_value_player_desc on player_stat  (cost=0.56..379430.20 rows=10163979 width=15) (actual time=0.054..2319.007 rows=10000000 loops=1)"
"                    Index Cond: (stat_id = 1)"
"                    Heap Fetches: 0"
"Planning time: 0.187 ms"
"Execution time: 9726.172 ms"

Is there any way can I speed this up?  The time it takes seems to be growing linearly with the number of players in the table.


texts: SQL Server 2017 and SSRS on Same Server?

texts: I heard that you cannot have SSRS and SQL Server on the same server, I have not been able to find anything that says this or otherwise, is this a true fact?


texts: Add column on huge InnoDB table in MySQL 4.1

texts:
I need to add a column on a huge InnoDB table (200GB) which resides on MySQL 4.1.21 (SunOS).

CPU has 16GB RAM and 200GB of more disk space.

Is there any quick way of adding this rather than ALTER TABLE? Because, ALTER TABLE as far as I know would take some months to complete.

Any thoughts experts?


texts: MongoDB on Raspberry Pi --dbpath not setting

texts:
I installed MongoDB using this tutorial. I have tried to set --dbpath in /etc/mongodb.conf like this:

dbpath=/path/that/iwant

#where to log
logpath=/var/log/mongodb/mongodb.log

logappend=true

When I mongod I get:

ERROR: dbpath (/data/db/) does not exist.

Further more, if I declare mongodb --dbpath /path/that/iwant i get:

exception in initAndListen: 10309 Unable to create/open lock file
errno:13 Permission denied Is a mongod instance already running?

Q: How do I get mongod to reference the proper path in the config file without specifying.

Bonus: Why is permission being denied from mongo. (I have tried sudo chmod -R 775 /path/that/iwant and sudo chown -R mongodb /path/that/iwant)

I am new to mongo and database admin exchange. As a brother from Stack Overflow I am humbled.

Edit: I added the path /data/db to the pie and chown mongodb on it. If I sudo mongod the server starts. If I try to start it with my user it will not start getting an error Unable to create/open lock file which exists.

Edit: Here is what my /etc/init.d/mongd looks like:

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
DAEMON=/usr/bin/mongod
DESC=database

# Default defaults.  Can be overridden by the /etc/default/$NAME
NAME=mongodb
CONF=/etc/mongodb.conf
DATA=/path/that/iwant
LOGDIR=/var/log/mongodb
PIDFILE=/var/run/$NAME.pid
LOGFILE=$LOGDIR/$NAME.log  # Server logfile
ENABLE_MONGODB=yes

...further down

DAEMONUSER=${DAEMONUSER:-mongodb}
DAEMON_OPTS=${DAEMON_OPTS:-"--dbpath $DATA --logpath $LOGFILE run"}
DAEMON_OPTS="$DAEMON_OPTS --config $CONF --rest"

This is what ps aux | grep mongo looks like after i start the server with sudo mongod:

root     10747  0.1  0.3   5124  1612 pts/0    S+   10:41   0:00 sudo mongod
root     10748  1.8  3.8  84896 17416 pts/0    Sl+  10:41   0:00 mongod
1001     10823  0.0  0.1   3544   808 pts/1    S+   10:42   0:00 grep --color=auto mongo


texts: How to restrict users to select only query through application textbox field?

texts:
Say that I have a web application that lets users to type in a SQL query (SQL Server 2008+). I want users to be able to run any SELECT query and read whatever is in the database, but I do not want them to be able to insert/update/drop/truncate/exec etc. (in short: only select). Please note that there are no data or even database metadata that are considered confidential in the entire database.

The rules currently in place:

No ";" character. If it is there, it must be exactly one and at the end of the trimmed query string.
Query must begin with select (case ignored, white-spaces are part of regex).
All newlines are removed from the query.

Is this enough? Is there any way to do anything else than just select?

EDIT: Thank you Thomas Stringer for the note about SELECT ...  INSERT. This is exactly the information I was after. I will remove all the newlines from the query string, which should help solve this problem. Is there any other way to get around this and be able to modify the data?

NOTE: CTE is not relevant for this case. Lets assume that query has to begin with SELECT.


texts:
I have a table of about 106k rows where about 11k rows have the values in 2 columns swapped. I want to run a query to fix it, but I don't think I can do this:

UPDATE `game`
SET `homescore`=`awayscore`, `awayscore`=`homescore`
WHERE (`awayscore`>`homescore` AND `winner`=`hometeam`) OR (`awayscore`<`homescore` AND `winner`=`awayteam`);

Or can I? I'm worried the 2 scores will end up the same value.

Also, please validate that my query will do what I intend to do:

I need to swap the home and away scores on the rows where the recorded winner (which is correct) doesn't match the scores recorded (they were accidentally swapped by a coding mistake now fixed) saying who the winner is (team with more points).


texts: update statistics in ola.hallengren maintenance solution

texts:
Is there a reason the default IndexOptimize job not include:

@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

We will have to remember to change t incude above two lines to the job.
Is there a reason it is not included in default job?

Thanks


texts: Difference in converting varchar2 column to number in Oracle 11 and 12

texts:
I have a table tblA ( ID varchar2(5), DESC varchar2(200) ) which includes data:

 '1', 'One'
 '2', 'Two'

My query is something like:

select anotherTbl.ID, T.txt
from anotherTbl,
     (select 0 as ID, 'Undefined' as txt from dual
      union
      select to_number(tblA.ID) as ID, tblA.DESC as txt from tblA
     ) T
where anotherTbl.extID = T.ID
and T.ID = 1 -- Actually 1 is a parameter

This query works in Oracle 12. However, in Oracle, an error ORA-01722: invalid number was raised.

More than weird, if I change to_number(tblA.ID) to to_number(trim(tblA.ID)), it would run sound in both Oracle 11 and Oracle 12!

I checked each IDs in tblA, just '1', '2', '3', ..., and '20', no spaces, no offence characters.

My guess is that Oracle 11 does not support this comparison, but could anyone please explain in a little more detail?


texts: Are physical backups sufficient in PostgreSQL?

texts:
Should I make logical backupds too from time to time or are physical backups sufficient for recovery in case of emergency in PostgreSQL? What are the advantages of logical backups?

I have heard somewhere that physical backups are not reliable enough without logical ones. But do not know why.


texts:
Last week one of my sites server crashed.  I was not able to access the ssh server and could only use the basic linux console on digitalocean.com.  Because I could not get into my ssh server I was not able to perform a pg_dump to backup my database.  I was however able to use rsync to pull those files out onto my hard drive.  The problem is that I have no idea how to put those files back into my server.  I don't believe I can use:

>psql -U username -f backupfile.sql

to restore those files because the rsync command only got me a main dir with tons of db files in it.  I so not see any .sql files in there so I have no idea what to do to get that database back into my site.  Does anyone have any advice or know if this is even possible? It might be better to launch my site clean and manually rebuild the database.  Thank you for checking this out.


texts: Full scans/sec metric seems very high, dropped after killing 2 queries

texts:
Our app today (java spring web application) suffered a major slump and Investigating the SQL Server metrics I found what seems to me a very high full scans/sec value.

I killed the 2 SPIDs that where responsible for the query (a big report that after the execution is written in Jasper Report) and the value dropped instantly.

Those are two screenshots before and after the kill:

Before:

After:

I'm not very expert in SQL Server since I'm not the DBA, but that values looks very worrying to me, can you confirm it?

Instance is SQL Server 2014.

Thanks


texts: Will a MERGE between two tables ever be faster than a DROP and SELECT INTO on the second table?

texts: Is there a scenario where it's faster for synchronizing the data from TableA to TableB by using a MERGE query as opposed to dropping TableB and doing a SELECT * INTO TableB FROM TableA?


texts: dbcc checkdb gives error "Incorrect PFS free space information for page"

texts:
dbcc checkdb

query for database gives errors. What is it, is it dangerous and how to resolve?

Full log file: download file or view on github (errors begin from string 364)

Possible
There was disk d:\ crash before (there are .mdf and .ldf files on d:\Data). Disk was restored from backup. But not sure if these errors are related with this hardware crash.

  DBCC results for 'DocStreams'. Msg 8914, Level 16, State 1, Line 1
  Incorrect PFS free space information for page (1:103151) in object ID
  1993058136, index ID 1, partition ID 72057594434682880, alloc unit ID
  71906736119218176 (type LOB data). Expected value   0_PCT_FULL, actual
  value 100_PCT_FULL. Msg 8914, Level 16, State 1, Line 1 Incorrect PFS
  free space information for page (1:103887) in object ID 1993058136,
  index ID 1, partition ID 72057594434682880, alloc unit ID
  71906736119218176 (type LOB data). Expected value   0_PCT_FULL, actual
  value 100_PCT_FULL. Msg 8914, Level 16, State 1, Line 1 Incorrect PFS
  free space information for page (1:104839) in object ID 1993058136,
  index ID 1, partition ID 72057594434682880, alloc unit ID
  71906736119218176 (type LOB data). Expected value   0_PCT_FULL, actual
  value 100_PCT_FULL.


texts: How to control psql displaying rows with (END)?

texts:
After my computer upgraded from Mac Sierra to High Sierra, my psql started exhibiting an annoying behaviour. Sometimes when it lists rows, it prints (END) instead of the prompt, and I have to press any button to stop go out of the display and get the prompt. It doesn't happen for every query, but it's not easy to predict when it happens. Sometimes it puts an (END) after 2 rows, but not after 15 rows.

What controls these aspects of psql, and how can I control it and turn it off? It's annoying that I have press a key for every query I run, or it swallows the first character and make me write "elect * from".

Additional input

It seems to trigger when the display of the rows is wider than the terminal window - i.e. it flows over multiple lines. I use the normal, built-in Terminal app for Mac.


texts: Innodb: Operating system error number 1117 in a file operation

texts:
i installed apache and mysql 5.5 on windows 7 64 bit. it worked but today mysql can not start. when i see at error log i find this

120616 16:35:47 [Note] Plugin 'FEDERATED' is disabled.
120616 16:35:48 InnoDB: The InnoDB memory heap is disabled
120616 16:35:48 InnoDB: Mutexes and rw_locks use Windows interlocked functions
120616 16:35:48 InnoDB: Compressed tables use zlib 1.2.3
120616 16:35:48 InnoDB: Initializing buffer pool, size = 103.0M
120616 16:35:48 InnoDB: Completed initialization of buffer pool
120616 16:35:48 InnoDB: highest supported file format is Barracuda.
120616 16:35:50  InnoDB: Operating system error number 1117 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: File name .\ib_logfile0
InnoDB: File operation call: 'Windows aio'.
InnoDB: Cannot continue operation.

i search "InnoDB: Operating system error number 1117 in a file operation." and find this link but i cant open mysql site.
what is problem?
what can i do?


texts: Class-table inheritance without flag column

texts:
I am trying to implement proper Class Table Inheritance (aka Table Per Type Inheritance) in mySQL.

AFAIK it's not offered out of the box so some hacking around is needed to make it tick.

Current situation:

Here's my example schema:

To break this down:

Customer is the parent table which contains common fields for all Customers.
CustomerPerson/CustomerOrganization are child tables and contain all fields of Customer + the specific ones depending whether Customer is Organization or Person.
I'm using a flag (column type on Customer) to differentiate between Person or Organization.

If I'm not mistaken, that "flag" would be called a discriminator in this context.

The problem.

  I think having a flag/discriminator column on Customer is not the ideal and another table needs to be added, e.g CustomerType which connects the child tables with the parent tables.

  Do I need another table or am I overthinking this? If yes how should I connect them?

Notes:

The above example works just fine but I'd like to hear ideas on how to do this in the best way possible.
Best-way possible is meant in terms of scalability and DRY-ness. Having a type column on every table that deals with this differentiation will just make things more messy as time passes and the schema gets more complex.


texts: Ola Hallengren DataBaseBackup to URL results in un-equal file sizes causing IO Device Error

texts:
we have been using Ola's Maintenance Solution and Database backup direct to URL since we migrated our system to Azure IAAS.  We have multi terabyte DB's (approx 40% Filestream data) and therefore split the backups over multiple files to keep them below 195GB each.
We had no issues until the last couple of weeks where the Full backup started to fail with an IO Device Error, telling me that the file size was greater than the 195GB limit so I increased the @NumberOfFiles parameter.
The first week this worked, the next week it failed again even though the DB had only grown a few GB in size.
I ended up having to add 10 to the number of files parameter and the backup completed successfully.
The issue is, most files written to are approx 90GB and 4 are approx 180GB.
Does anyone know the reason for the un-equal file sizes and a way to prevent this?
EXECUTE [dbo].[DatabaseBackup]
@Databases = '<dbname>',
@URL = '<storage account>',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@CheckSum = 'Y',
@LogToTable = 'Y',
@MaxTransferSize = 4194304,
@Blocksize = 65536,
@NumberOfFiles = 36

Thanks in advance


texts: Creating an auditing strategy when a user has been defined in a table instead of in SQL

texts:
To preface this post, I'm pretty new to database design, so please excuse me if there's already a simple or immediate answer to this post.

In my DB I have a table called t_employees which acts as a "user" table.  In other words, tables that keep track of things like "modifiedBy", "createdBy", etc. use this table.

The table looks like this:

CREATE TABLE t_employees (
    employeeId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    employeeExternalId VARCHAR(50) NOT NULL,
    email NVARCHAR(128) NOT NULL,
    passwordHash NVARCHAR(128),
    firstName NVARCHAR(60),
    lastName NVARCHAR(60),
    dateHired DATE,
    gender INT,
    DOB DATE,
    ...
    --And a bunch of other random fields that an employee can use
);

And this is where my question resides.

I'm trying to come up with an auditing solution that will capture "who modified this data" when an update, insert, or delete statement occurs in my application.  I've looked into threads like this where people can audit a table via triggers that keep track of the system user who's making the changes.  However, this approach doesn't work for me as I'm trying to keep track of the employeeId from the t_employees table who made the changes and I only have 1 system user in my database.

I've thought of creating an SP that I call before every update, insert, or delete statement that logs what data is being inserted, updated, or deleted along with the employeeId of the user who made the request into an audit table.  However, I feel like there must be a better solution to this as this seems kind of redundant.

Any help or advice would be very much appreciated.  Thank you.


texts: Overlapped I/O error installing SQL Server 2008 R2 SP2 Express

texts:
A user of our software is attempting a normal installation on Windows V8.1 x64 (theoretically completely updated). As part of the install, SQL Server 2008 R2 SP2 is normally installed as the back-end database.

This one user is currently getting an error 997 - Overlapped I/O operations is in progress. The relevant portion of the log file:

MSI (s) (40:90) [09:36:49:634]: SECREPAIR: New Hash Database creation complete.
MSI (s) (40:90) [09:36:49:634]: SECREPAIR: Crypt Provider not initialized. Error:0
MSI (s) (40:90) [09:36:49:642]: SECREPAIR: Crypt Provider not initialized. Error:0
MSI (s) (40:90) [09:36:49:643]: SECREPAIR: Crypt Provider not initialized. Error:0
MSI (s) (40:90) [09:36:49:643]: SECREPAIR: Crypt Provider not initialized. Error:0
MSI (s) (40:90) [09:36:49:643]: SECREPAIR: Crypt Provider not initialized. Error:0
MSI (s) (40:90) [09:36:49:643]: SECREPAIR: Crypt Provider not initialized. Error:997
MSI (s) (40:90) [09:36:49:643]: SECUREREPAIR: Failed to CreateContentHash of the file: Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x86\Microsoft.SqlServer.Configuration.SqlBrowserExtension.dll: for computing its hash. Error: 997
MSI (s) (40:90) [09:36:49:645]: SECREPAIR: Failed to create hash for the install source files
MSI (s) (40:90) [09:36:49:645]: SECUREREPAIR: SecureRepair Failed. Error code: 3e554415638
Action start 9:36:49: ProcessComponents.
Error 997. Overlapped I/O operation is in progress.

I have looked into the problems with KB2918614, and this patch is apparently not even installed - at least it doesn't show up in the list of installed fixes.

I disabled the running antivirus, and the error still occurs.

I did a manual install after downloading a fresh file from Microsoft - error still occurs.

I am now at a bit of a loss. I will continue searching for an answer online, but with all of the experience here - does anyone have any specific suggestions: things to check, things to install, things to disable, random voodoo idols to pray to, whatever.


texts: Database replication issues - Master is attempting to 'replicate' updates to the mysql users (password updates)

texts:
I have mysql database replication setup with the slave on a cloud provider and the master on a dedicated server with cPanel / WHM installed.

My replication stalled with a series of error messages. I managed to clear each individually, however, I'm concerned that these errors even appeared to begin with.

I have about 40 individual databases being replicated, all listed in the my.cnf file as binlog_do_db = dbname and all working so far as expected. I have several other databases that are not being replicated from that server as well for reference.

Here's a list of the errors I've received. All of these have appeared after I updated the mysql root password on the Master server.

Error 'Can't find any matching row in the user table' on query. Default database: 'dbname'. Query: 'SET PASSWORD FOR 'root'@'mywebsite.com'='xxx''

Error 'Can't find any matching row in the user table' on query. Default database: 'dbname'. Query: 'SET PASSWORD FOR 'root'@'127.0.0.1'='xxx''

Error 'Unknown column 'Password' in 'field list'' on query. Default database: ''. Query: 'UPDATE mysql.user SET Password=PASSWORD('xxx') WHERE User='root''

Prior to these errors, I had another error:

Error 'Can't find any matching row in the user table' on query. Default database: ''. Query: 'SET PASSWORD FOR 'username'@'localhost'='xxx''

This first error prompted me to reset passwords as an extra precaution as the mysql user username hadn't been updated by me on the master server. By updating the other passwords (including root), the remaining errors were triggered.

To clear the first error, I had to create a mysql user for username on my slave mysql server before it would clear. This means that effectively, anytime I update a user in cPanel on the master server, it's trying to update that user with the same credentials on the slave server.

Why is this happening and how do I disable it?

For reference, I used this tutorial to setup the master - slave relationship.


texts: How to select 3 records before x ID and 5 records after x ID with Laravel Eloquent?

texts: How can I select 3 records before x ID and 5 records after x ID with Laravel Eloquent or mysql?


texts:
Our company has SQL Server 2014 and 2016 and will acquire licenses for SQL Server 2019.
Our environment is in VMWARE, and as I understand it, one license is required for each V-Core that the server is using.

So if my server has 12 V-Core will I need 12 licenses?

I read this document from Microsoft and understood it this way, is that correct?
SQL Server 2019 Licensing Guide
Thanks.


texts: Can you prevent more than X number of rows being updated in SQL Server?

texts: Is there a way to configure SQL Server to limit the number of rows than an update statement can modify?  Say I wanted the limit to be 30,000 rows and someone fired an update that would modify 45,000 rows, they would receive an error or some other preventive message.


texts: Request suggestion for a database between MySQL and Postgresql

texts:
We maintain a list of all the SAN Switch Ports that are either free or allocated to hosts. Currently, we enter the information on the company portal. But we need to move if off to a database because as of now we have about 2200 rows of data in it and each row has 10 columns. I got the number after extracting the data into an excel sheet. I am expecting that atleast 1000 more rows will be added in a few months.

All of our SAN Mgmt servers are windows 2003, but none of them have MS Office installed, so cannot use MS Excel. Hence the need for an open source database.

I have tried MySQL and PostgreSQL. I find MySQL easier to deal with, but I have read that data reliablity wise PostgreSQL is better. I tried both databases for a few days, imported CSV files to both as that was easier compared to manually entering 2200 entries in it. Both MySQL and PostgreSQL seem to be doing good. Though, I had to edit the CSV file because the field seperator "," was used in some places in of the cells and that made it break and skew the entries in MySQL. PostgreSQL didnt have any trouble with that.

A request to experienced Database Admins - kindly let me know that for my need will MySQL suffice? Is there a chance of Data Corruption with MySQL? Or would it be better to move to PostgreSQL right now itself?

My apologies for not using database specific terminologies here because I do not hail from a DB Background.

Please let me know.


texts: Why is my UTF-8 document raising UTF-8 encoding errors in Azure Data Lake Analytics?

texts:
I have a document that was compressed in gunzip from a unknown source system. It was downloaded and decompressed using a 7zip console application. The document is a CSV file that appears to be encoded in UTF-8.

It's then uploaded to Azure Data Lake Store right after compression. Then there is a U-SQL job setup to simply copy it from one folder to another folder. This process fails and raises a UTF-8 encoding error for a value: Ã©e

Testing

I downloaded the document from the store and removed all records but that one with the value flagged by Azure. In Notepad++, it shows the document as UTF-8. I save the document as UTF-8 again and upload it back to the store. I run the process again and the process succeeds with that value as UTF-8

What am I missing here? Is it possible the original document is not truly UTF-8? Is there something else causing a false positive? I'm a bit baffled.

Possibilities

The document is not truly UTF-8 and needs to be recoded
Maybe the method that's uploading the file is recoding it
Maybe 7zip is recoding it incorrectly

Environment/Tools

Windows Server
Python 2.7
Azure Data Lake Store
Azure Data Lake Analytics
7Zip.exe
gz
Azure API

USQL

Just the base USQL job that defines the schema then selects all fields to a new directory. No transformation happening outside of leaving out the headers. The file is CSV, comma delimited with double quotes on strings. Schema is all strings regardless of data type. Extractors tried is TEXT and CSV with both set to be encoded:UTF8 even though both are default to UTF8 according to Azure documentation on the system.

Other Notes

This same document was uploaded in the past to BLOB storage and imported in the same fashion into Azure Data Warehouse without errors via Polybase.
The value that causes the UTF-8 encoding error is a URL mangled among 1 million other records.
It looks like there are ASCII characters coming in even though it's a UTF-8 document.
When I convert it to ANSI and use the ASCII extractor the file succeeds.
Azure Data Lake Analytics does not allow you to ignore the error as it's an encoding issue. I'd be happy invalidating the record all together like you can in Azure Data Warehouse.


texts: How to populate central management server registration from a list

texts:
I need to delete users from all the instances in my domain.

In order to do that, I want to create a CMS group and execute queries on it.
Problem is I have more than 150 servers to add. How can I bulk add servers to the group and not do it manually?


texts: Fetch wikipedia articles query optimization in mysql wikipedia Database

texts:
I have imported the Wikipedia database to a local MySQL database and I wrote a query to fetch wiki pages.

My query is as follows :

SELECT
  p.page_title,
  p.page_id,
  t.id,
  text.old_text
FROM wikipedia_en.page p
  INNER JOIN wikipedia_en.categorylinks cl
         ON  cl.cl_from = p.page_id AND cl.cl_type = 'page'
         AND p.page_namespace = 0
         AND p.page_is_redirect = 0
  INNER JOIN wikipedia_en.revision ON revision.rev_id = p.page_latest
  INNER JOIN wikipedia_en.text ON text.old_id = revision.rev_text_id

   -- filter out pages with no topic
  INNER JOIN topics.topics t  ON cl.cl_to = t.topic
LIMIT 520000, 10000;

The query takes about 2s if the LIMIT offset starts at 0 but as the offset grows up (e.g 520000) the query execution takes a remarkable time ( e.g for and offset of 520000 it takes about 4min )

Here is the query Explain Plan :

+----+-------------+----------+--------+------------------------------------------------+------------+---------+-----------------------------------+--------+--------------------------+
| id | select_type | table    | type   | possible_keys                                  | key        | key_len | ref                               | rows   | Extra                    |
+----+-------------+----------+--------+------------------------------------------------+------------+---------+-----------------------------------+--------+--------------------------+
|  1 | SIMPLE      | t        | ALL    | topics_topic_index                             | NULL       | NULL    | NULL                              | 141413 |                          |
|  1 | SIMPLE      | cl       | ref    | PRIMARY,cl_timestamp,cl_sortkey                | cl_sortkey | 258     | topics.t.topic,const              |     14 | Using where; Using index |
|  1 | SIMPLE      | p        | eq_ref | PRIMARY,name_title,page_redirect_namespace_len | PRIMARY    | 4       | wikipedia_en.cl.cl_from           |      1 | Using where              |
|  1 | SIMPLE      | revision | eq_ref | PRIMARY                                        | PRIMARY    | 4       | wikipedia_en.p.page_latest        |      1 |                          |
|  1 | SIMPLE      | text     | eq_ref | PRIMARY                                        | PRIMARY    | 4       | wikipedia_en.revision.rev_text_id |      1 |                          |
+----+-------------+----------+--------+------------------------------------------------+------------+---------+-----------------------------------+--------+--------------------------+

I'm asking if there is a way to optimize this query using subqueries or temporary tables.


texts:
I was thinking that I have a very good understanding about my sql and joins but i miserably failed to make this query. I have to make employee leave report, therefore I want all employee list no matter he make leave or not, if there is no leave then zero will be displayed. I have this query currently:

select
leave_detail_id, emp_id, Employee_Name, leave_type, leave_name, sum(leave_total_days) total_leave, leave_current_status_id
from employee emp
left join  tbl_emp_leave_details eld
ON emp.PK = eld.emp_id
inner join tbl_leave_types lt
ON lt.leave_id = eld.leave_type
where leave_current_status_id = 1
and leave_start_date >= '01-07-2019'
and leave_end_date <= '31-07-2019'

and emp.Active=1
    group by leave_type

You can see that even after applying left join i am unable to bring all the employees. Please help me to understand the problem. I know that where filter is also apply to join. but how can i correct this with (an explanation)?


texts: How long does it take for powershell to restore a SQL Backup?

texts:
I executed this Powershell script to backup a Database, Drop that database and then restore it with a different name:

    $srv = new-object Microsoft.SqlServer.Management.Smo.Server("(local)")
    $res = new-object Microsoft.SqlServer.Management.Smo.Restore
    $backup = new-object Microsoft.SqlServer.Management.Smo.Backup

    $backup.Devices.AddDevice("D:\AWS_CM.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
    $backup.Database = "Arizona_CM"
    $backup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
    $backup.Initialize = $TRUE
    $backup.SqlBackup($srv)
    $srv.Databases["Arizona_CM"].Drop()

    $res.Devices.AddDevice("D:\AWS_CM.bak", [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
    $res.Database = "AWS_CM"
    $res.NoRecovery = $TRUE
    $res.SqlRestore($srv)

The script worked. However 10 minutes have passed and when I see the Database in SQL Server it is still in "Restoring" mode even though the size is only 80 mb.

Does anyone know how long restoring Database in SQL Server through powershell usually takes?


texts: Aggregate a JSON doing an average on values without defining keys

texts:
I would like to know if there is a way or an existing function to perform an aggregation on a JSON field by doing the average of values if the key appears multiple times with PostgreSQL. The json's result keys are unknow and have to be add to it if these keys are meet during the aggregate function. They can't be define on the query.

datetime             data
2018-05-06 18:17:00  {"speed":23.3,"orientation":1.3,"o2":75.2,"pm25":12.1}
2018-05-06 19:17:00  {"speed":20.3,"pm25":13.1}
2018-05-07 15:02:00  {"speed":21.3,"orientation":1.3,"pm10":72.2}

Imagine here we want the result aggregate by day

The result wanted :

day         data
2018-05-06  {"speed":21.8,"orientation":1.3,"o2":75.2,"pm25":12.6}
2018-05-07  {"speed":21.3,"orientation":1.3,"pm10":72.2}

The first two rows has been aggregate doing an average on recurrent keys and kept all of the existing keys.


texts: Parallelism with temp table but not table variable?

texts:
The first query (inserts into table variable) takes twice as long as the second one. It does not use parallelism in the execution plan.

The second query (inserts into temp table) uses parallelism in its execution plan and is able to achieve the results in almost half the time.

I am trying to run this from a table function hence the need for the table variable as opposed to the temp table.

The execution plan is quite complex and I would prefer not to dive in that direction (yet). I am wondering if someone has an explanation or hypothesis of why the first SQL is not using parallelism, while the second is.

First:

DECLARE @TableVar as TABLE (
    [Date] [date] NULL,
    [B] [int] NULL,
    [C] [decimal](5, 3) NULL)

INSERT INTO
    @TableVar
SELECT
    [Date]        = CAST(LO.Dt as Date)
  , [B]           = DMC.[B]
  , [C]           = DMC.[C]
FROM
                 dbo.fnTblFunc1(@DateStart, @DateEnd) AS DMC
    INNER JOIN   dbo.fnTblFunc2(@DateStart, @DateEnd) AS LO ON DMC.Date = LO.Dt
OPTION (FORCE ORDER  )

Second:

CREATE TABLE #TempTbl(
    [Date] [date] NULL,
    [B] [int] NULL,
    [C] [decimal](5, 3) NULL)

INSERT INTO
    #TempTbl
SELECT
    [Date]        = CAST(LO.Dt as Date)
  , [B]           = DMC.[B]
  , [C]           = DMC.[C]
FROM
                 dbo.fnTblFunc1(@DateStart, @DateEnd) AS DMC
    INNER JOIN   dbo.fnTblFunc2(@DateStart, @DateEnd) AS LO ON DMC.Date = LO.Dt
OPTION (FORCE ORDER  )

DROP TABLE #TempTbl


texts: Why have SQL databases like PostgresSQL and MySQL added JSON data types?

texts: I'm looking for scenarios in which storing a JSON object in a relational database would be useful.


texts:
I am a newbie and would appreciate suggestions/material to research
I have 3 types on information in a single table

problems

reasons

fixes

problem data is related to reasons by 1 to many
reasons data is related to fixes by 1 to many
I am thinking of creating a dimension table for dates as mentioned here
How could I design a star schema out of it? As of now the entire data is in a single table (all joined together)
#####################
update 1 - example data and my 1st solution below. Please guide
my columns are
problem id
problem description
problem associate department
problem due date
reason id
reason description
reason associated person
reason due date
fix id
fix description
fix owning employee
fix due date

example data

+------------+---------------------+------------------------------+------------------+-----------+--------------------------+--------------------------+-----------------+--------+----------------------+---------------------+--------------+
| problem id | problem description | problem associate department | problem due date | reason id |    reason description    | reason associated person | reason due date | fix id |   fix description    | fix owning employee | fix due date |
+------------+---------------------+------------------------------+------------------+-----------+--------------------------+--------------------------+-----------------+--------+----------------------+---------------------+--------------+
|          1 | wrong customer info | sales                        | 1-Jan-21         |         1 | customer gave wrong info | a                        | 15-Dec-20       |      1 | contact customer     | bb                  | 10-Dec-20    |
|          1 | wrong customer info | sales                        | 1-Jan-21         |         2 | customer gave wrong info | a                        | 15-Dec-20       |      2 | verify customer info | cc                  | 15-Dec-20    |
|          1 | wrong customer info | sales                        | 1-Jan-21         |         1 | data entry error         | b                        | 16-Dec-20       |      1 | correct data entry   | bb                  | 16-Dec-20    |
|          1 | wrong customer info | sales                        | 1-Jan-21         |         1 | database wrong join      | c                        | 20-Dec-20       |      1 | fix join             | aa                  | 19-Dec-20    |
|          1 | wrong customer info | sales                        | 1-Jan-21         |         2 | database wrong join      | c                        | 20-Dec-20       |      2 | something else       | dd                  | 19-Dec-20    |
|          1 | wrong customer info | sales                        | 1-Jan-21         |         3 | database wrong join      | c                        | 20-Dec-20       |      3 | modify tables        | bb                  | 19-Dec-20    |
+------------+---------------------+------------------------------+------------------+-----------+--------------------------+--------------------------+-----------------+--------+----------------------+---------------------+--------------+

I am thinking of a design like below. all joins would be 1:1 in case of fact table. In case of fact table2 calendar there would be 1:many join as that fact table would have one row per date while the dim tables could have same date on multiple rows


texts:
I query a table

SELECT COUNT(*) FROM sf_guard_user_group

return count 1 but table has total 28 records. But if I test it via putty from another machine, it shows count 28. Help to sort out.


texts: How to use insert and select simultaneously?

texts:
I have table test and it has columns id,name and age.I want the id so I am querying like
it

select id from test where age=12

After I get the id I am updating the name using update query

Now lets say if age=13 not present then I will insert a row(insert into test(age) values(13)) and after it is inserted I will get the id(as id is autoincremented).So is it possible to do insert and select simultaneously?


texts: Restrict Full access to certain User of Database

texts:
The scenario is the following:

Given SQL Server Instance created and handled not by me
Creation of Database and User on given Instance by me

Question:

To my limited understanding, the sa login will have full access to my Database. Is there a way I can create a user that will be the only user/login that will have full access to my Database, despite the fact I don't own the instance?


texts:
In the manual for creating UDTs in postgresql I found the following statement:

  PostgreSQL allows user-defined types to take one or more simple constants or identifiers as modifiers. However, this information must be capable of being packed into a single non-negative integer value for storage in the system catalogs.

So is there really no way, to store more information that would fit in a non-negative integer? I would like e.g. to be able to store the name of a function that should be called as a default upon insertion on the data (since there might be multiple functions that could work on that data). That is not possible?


texts: I have a problem: cannot backup DB2 8.1 with backup command line in Windows 2003. I think my HDD was failure (after read error showed by backup command). So, I want to copy the folder/ directory which contains all of my data to send it to an IBM expert. But I don't know where is data saved. I install DB2 8.1 and everything was default when I create my database.


texts: How to make inet_server_addr() return localhost in spite of ::1/128

texts: How to make inet_server_addr() return an name as IPv4?