texts: Oracle CPU vulnerabilities and Database Proactive Bundle Patch

texts:
I've applied the latest Oracle 12c Database Proactive Bundle Patch (DBBP).

My security team runs scans and April 2015 CPU (amongst other CPUs) continues to show up as vulnerabilities.

I need to prove that DBBP is a cumulative patch and all those old vulnerabilities are fixed.

sys.registry$history doesn't help

select ACTION,NAMESPACE,COMMENTS from registry$history;
ACTION                    NAMESPACE    VERSION     COMMENTS
------------------------- ------------ ------------------------
UPGRADE                   SERVER        12.2.0.1.0 Upgraded from 12.1.0.2.0

opatch lsinventory -bugs_fixed   or   opatch lsinventory -details
    isn't giving me what I want.

How can I find out what vulnerabilities are in April 2015 CPU and prove that DBBP has mitigated them???


texts: Where do I set the I/O size for DB2 9.7 LUW driver (AIX 6.1)?

texts:
I am looking to change the I/O size returned from the DB2 server for query results. (As per below instruction from SAP BODS 4.X performance optimization guide)

  Increase the size of each I/O from the database server to match the OS
  read-ahead I/O size.

Where do I actually change the parameters for the database ? Is it a driver configuration parameter or at server level? Also I have no clue where to check the OS read ahead block size(AIX 6.1, DB2 9.7 LUW)


texts:
I have:

DELETE FROM songs
WHERE songs.id_album = album.id
AND album.id_band =16`

  Error: #1054 - Unbekanntes Tabellenfeld 'album.id' in where clause.

Table album exists, field id too, 100%.

Where is error?

Thank you.


texts: Does PostgreSQL rewrite JOINs with OR's into UNIONs?

texts:
(Abbreviated) table summary:

-- Table cases:
     id                  SERIAL       PRIMARY KEY,
     application_number  VARCHAR(30)  NOT NULL,
     publication_number  VARCHAR(30)  NOT NULL,

-- Table patents:
     case_id                 INTEGER      PRIMARY KEY,  -- FK to cases(id)
     pct_application_number  VARCHAR(30)  NOT NULL,
     pct_publication_number  VARCHAR(30)  NOT NULL,

-- All character columns have working trigram indexes:
     CREATE INDEX cases_application_number_trgm_idx ON cases
         USING GIN (application_number gin_trgm_ops);
         -- (etc)

The following query is slow (~200ms) because it doesn't use the indexes:

SELECT  c.id
  FROM  cases c
        JOIN patents p ON p.case_id = c.id
 WHERE  c.application_number ILIKE '%1234%' OR p.pct_application_number ILIKE '%1234%'

The following queries are all fast (1-2ms):

-- AND instead of OR
WHERE  c.application_number ILIKE '%1234%' AND p.pct_application_number ILIKE '%1234%'

-- OR, but only table "cases"
WHERE  c.application_number ILIKE '%1234%' OR c.publication_number ILIKE '%1234%'

-- OR, but only table "patents"
WHERE  p.pct_application_number ILIKE '%1234%' OR p.pct_publication_number ILIKE '%1234%'

-- Simulating the OR with a UNION
SELECT  c.id
  FROM  cases c
        JOIN patents p ON p.case_id = c.id
 WHERE  c.application_number ILIKE '%1234%'
 UNION
SELECT  c.id
  FROM  cases c
        JOIN patents p ON p.case_id = c.id
 WHERE  p.pct_application_number ILIKE '%1234%'

Here is the EXPLAIN ANALYZE output for the slow query:

                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=2329.80..10590.54 rows=7 width=4) (actual time=54.951..186.713 rows=35 loops=1)
   Hash Cond: (c.id = p.case_id)
   Join Filter: (((c.application_number)::text ~~* '%1234%'::text) OR ((p.pct_application_number)::text ~~* '%1234%'::text))
   Rows Removed by Join Filter: 68223
   ->  Seq Scan on cases c  (cost=0.00..4981.99 rows=142099 width=12) (actual time=0.011..32.875 rows=142099 loops=1)
   ->  Hash  (cost=1142.58..1142.58 rows=68258 width=11) (actual time=31.105..31.105 rows=68258 loops=1)
         Buckets: 131072  Batches: 2  Memory Usage: 2473kB
         ->  Seq Scan on patents p  (cost=0.00..1142.58 rows=68258 width=11) (actual time=0.019..11.995 rows=68258 loops=1)
 Planning time: 1.875 ms
 Execution time: 186.780 ms
(10 rows)

The query as posted here is heavily reduced to illustrate the problem. The actual query is more complex and involves a text search in six (or more) columns in five (or more) tables, with about 10 output columns. I guess I could rewrite all that as series of queries and connect them into a huge UNION... is there a better way to deal with this problem?

Adding query plan with enable_seqscan disabled (as requested):

                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=0.71..18767.92 rows=7 width=4) (actual time=4.809..150.368 rows=35 loops=1)
   Merge Cond: (c.id = p.case_id)
   Join Filter: (((c.application_number)::text ~~* '%1234%'::text) OR ((p.pct_application_number)::text ~~* '%1234%'::text))
   Rows Removed by Join Filter: 68223
   ->  Index Scan using cases_pkey on cases c  (cost=0.42..14942.96 rows=142099 width=12) (actual time=0.004..32.695 rows=142097 loops=1)
   ->  Index Scan using patents_pkey on patents p  (cost=0.29..2275.63 rows=68258 width=11) (actual time=0.003..11.942 rows=68258 loops=1)
 Planning time: 1.007 ms
 Execution time: 150.399 ms
(8 rows)


texts: SQL Server 2014 Import Wizard missing data source option

texts:
I have already run the upsizing wizard from my production Access 2010 .accdb database to create all the table structures and move the data on to our new SQL Server 2014. This included adding some PKs on the server which didn't exist in Access and some other clean up.

I have duplicated all the table structures to create a development version of the tables in their own database on the same server and am now trying to pull the data from the development version of the .accdb into the development database. (i.e., I don't want to run the Upsizing Wizard again from the Dev Access DB because of all the structure clean up that I don't want to do again.)

Using Right-click | Tasks | Import Data to get the Import and Export Wizard, when I click the drop down on Data source, I have an option for Microsoft Access (Microsoft Jet Database Engine) which doesn't recognize the .accdb format.

I've seen screen shots from various places on the web of the Import Wizard with a Microsoft Access (Microsoft Access Database Engine) option which does recognize the .accdb format.

Why would my Import Wizard be missing this data source option, and what can I do to resolve that?

Based on Aaron's suggestions, I checked the ODBC driver, and it appears to be the 64-bit version:

If I explicitly run c:\windows\SysWOW64\odbcad32.exe, I get what must be the 32-bit version:

I'm running 64-bit Office on a 64-bit Win7 installation, and I'd assume that SQL Server 2014 is 64-bit by default (I didn't install the server nor the SSMS on my machine).

It seems that the initial link Aaron provided addresses the exact opposite of what I'm trying to accomplish. That issue seems to be trying to explicitly use the 32-bit driver on a 64-bit system, while I seem to be stumbling into the 32-bit driver when I want the 64-bit. It does seem that there may be something wrong in the registry, as that question mentions, but I'm not comfortable enough to root around in there on my own, though I can certainly follow some instructions.


texts: SQL - Passing a Stored Procedure Variable as a Parameter in a crystal report for end users to select

texts:
OK I know there are hundreds of posts out there that state how to pass a parameter from a stored procedure to a crystal report. But unfortunately with some research I haven't come across a specific answer to my question with regards to SQL Server variables and crystal report parameters.

Question: If I have a stored procedure and I am declaring a variable called "Location" and I want to reference that variable in my crystal report as a new parameter and end users to be presented a dialog box that allows them to select from various locations and that would in return results only for a specific location chosen from a drop down. How would that occur?

Methods tried so far:

Crystal Report Builder 11

In "parameter fields" under field explorer, select new and then choose "List of Values" to by Dynamic and choose the data source and column that's been used with my variable and it says "Parameters" is @location. But when I run the report I am presented with a dialog box for a location value but there are no values to select from. (To make a note the location field is being supplied by the users drop down selection via the report thus returning results from the stored procedure)
In "parameter fields" select new and then leave list of values as "static" and choose value field as column "location" and enter multiple values with regards to locations for the user to select from. (This does present the user with a dialog box suggesting drop down values for a location but no results returned so I was unsure if this was the proper way?)

Code:

ALTER PROCEDURE [dbo].[a_ras_TruckSummary_SP]

AS
BEGIN

IF (object_id('tempdb..#tempTruckSummary') IS NOT NULL)
BEGIN
    DROP TABLE #tempTruckSummary
END

DECLARE @location VARCHAR(10)

 SELECT O.order_no, L.shipped, L.price, L.[location] FROM orders O WITH
(NOLOCK)
INNER JOIN Ord_list L ON O.order_no = L.order_no
    WHERE O.routing NOT LIKE 'FEDX%'
    AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3')
    AND ISNULL(O.void,'') <> 'V'
    AND L.location = @location
    GROUP BY O.order_no, L.shipped, L.price, L.[location]


texts: how to save an image column in MSSQL to file system?

texts:
Imagine I have a table like this in MSSQL

-------------------------------------
file_name(varchar) | file_doc(image) |
--------------------------------------
A                  | image_binary1   |
--------------------------------------
B                  | image_binary2   |
--------------------------------------

I want to save contents of this table to file system something like the following

 - c:\somewhere\A.pdf
 - c:\somewhere\B.pdf

how to achive that in MSSQL?


texts: Usage of RESTRICT or No ACTION in FOREIGN KEY Constraints

texts:
Sorry if this question is naive, but I was unable to understand possible usage of RESTRICT or No ACTION. Consider a simple database of

CREATE TABLE column1
(
first_id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (first_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci";

CREATE TABLE column2
(
second_id int(11) NOT NULL AUTO_INCREMENT,
first_id int(11) REFERENCES column1(first_id) ON DELETE CASCADE,
PRIMARY KEY (second_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci";

If DELETE FROM column1 WHERE first_id='XX' the corresponding row in the column2 (child column) will be deleted
If DELETE FROM column2 WHERE first_id='XX' nothing will be deleted from column1 (parent column)

Right? then, what and how RESTRICT or NO ACTION can change this scenario ?


texts: How to copy MySQL Database from Linux to Windows?

texts:
I would like to copy a MySQL database form Linux to Windows, from a little research I found this url http://www.cyberciti.biz/tips/howto-copy-mysql-database-remote-server.html where the author says we could copy MySQL Database using the following command in Linux

Linux to Linux

mysqldump -u username -p 'password' db-name | ssh user@remote.box.com mysql -u username -p 'password' db-name

Is there a command for copying MySQL database from Linux to Windows?


texts: SQL server 2008 Express x64 in windows 8.1 fail

texts:
I've been download sql server express x64 version in this

I just install new windows 8.1 x64 vl version and install trial KIS for anti-virus. After i install sql server but it's not working

and

i uninstall KIS, SQL server and install avast and sql server again but the same :(.

How to fix that thanks.


texts: Extracting entity from attribute in entity-relationship diagram

texts:
I am developing a database model where I need to capture information about rent contracts for parking places within a parking. I have thus defined an entity called PARKING CONTRACT.

Requirements (r.) say, that each PARKING CONTRACT should be signed for exactly 1 car, which is, in turn, owned by exactly 1 car owner.

To fully define a PARKING CONTRACT, r. say, I need to record various information about car owner and his car. For each car owner: full name, address, passport number, etc. For each car: technical passport number, registration number, brand, etc.

According to the r. (they do not go into such details, here I am judging on the basis of attributes that need to be defined!), a car owner can be uniquely identified by their passport number; a car can be identified by registration number.

I first thought it would be good to model CAR OWNER and CAR as separate entities, and came to this:

.-----------------.                      .-------.                 .------------.
| PARKING CONTRACT|     Is signed for    |  CAR  |   Is owned by   |  CAR OWNER |
|                 |O_____________________|       |O________________|            |
'-----------------' 1                   1'-------' 1..*           1'------------'

But then I realized that with this design we may store as much CAR OWNERs as we like without any of them having any PARKING CONTRACTs for any of their CARs. I think it is not good - why a database should be able to store information about people which never had any PARKING CONTRACTs.

Another option I see here is to not extract CARs and CAR OWNERs into separate entities and to keep all information about them in the PARKING CONTRACT table, for each PARKING CONTRACT.

But this option doesn't seem to be too nice as well, as the table for PARKING CONTRACT, imho, becomes a chimera which tries to handle too much different information.

Also, I have read this brochure: https://support.ca.com/cadocs/0/CA%20ERwin%20Data%20Modeler%20r8-ENU/Bookshelf_Files/PDF/ERwin_Methods.pdf

and on the page 38, it tells that when you end up with attribute names like parking-contract-signer-passport-number, then that is a sign of a design problem.

The question is: What is the commonest way to handle situations like this one? Is any of these two approaches preferred? Or, maybe, there's a whole different third approach? And why is that approach preferred? Thanks!


texts: Unable to start oracle database after trying to enable archive log mode on

texts:
I am facing this unusual issue. I wanted to enable archive log mode from noarchivelog mode and used alter command for the same as  ALTER SYSTEM SET log_archive_dest='LOCATION=D:\Oracle\Redo Log Archive\'and then shutdown the database. When i tried to restart as SQL>STARTUP mount it is giving below error
ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed. After searching the web i tried
sql>ALTER SYSTEM SET log_archive_dest_1='LOCATION=D:\Oracle\Redo Log Archive\' scope=both; but getting

ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

and for the other commands like SQL>archive log list it showing above error i.e ORA-01034: ORACLE not available.

P.S: In init.ora in pfile folder there isn't any log_file_dest_1 parameter. Is there any way i could add it manually and start the database or any other way as alter command from sql plus is not working.


texts: Determine theoretical max connections for MariaDB

texts:
I want to upgrade the ram our MariaDB server (10.1.25-MariaDB-1~xenial) to handle more connections.
I have been determining the max connections mostly based on this article which suggests to use this formula
(Available_RAM - Global_buffers) / Thread_buffers
Global_buffers it the sum of these variables.
key_buffer_size
innodb_buffer_pool_size
innodb_log_buffer_size
innodb_additional_mem_pool
net_buffer_length

Thread_buffers is the sum of these variables.
sort_buffer_size
myisam_sort_buffer_size
read_buffer_size
join_buffer_size
read_rnd_buffer_size

This appears to work and for me it gives approx. 15 connections as my theoretical max.
When I query SHOW STATUS LIKE 'max_used_connections', which to my understanding gives me the historical maximum connections requested, I get 46 connections.
For background when I run SHOW VARIABLES LIKE 'max_connections' I get 151 which I believe is the default setting value.
Is 15 connections actually my theoretical max and did the additional 31 connections failed but MySQL registered them?
Is my math wrong or more likely am I missing some variables?


texts: map non unique attribute to a unique attribute

texts:
I have a mapping table that uses a non unique attribute.  I need a way to map that attribute to one of the attributes it is referring too, to get the unique attribute.  Here is an example of what i am trying to do:

DECLARE @TABLE1  TABLE (id int, table1_name varchar(max))
DECLARE @TABLE2  TABLE (id int, table2_name varchar(max))
DECLARE @TABLE1_TABLE2 TABLE (table1_name varchar(max), table2_name varchar(max))

insert into @TABLE1 (id, table1_name) values (1, 'table1_name1')
insert into @TABLE1 (id, table1_name) values (2, 'table1_name2')
insert into @TABLE1 (id, table1_name) values (3, 'table1_name3')

insert into @TABLE2 (id, table2_name) values (1, 'table2_name1')
insert into @TABLE2 (id, table2_name) values (2, 'table2_name2')
insert into @TABLE2 (id, table2_name) values (3, 'table2_name2')

insert into @TABLE1_TABLE2 (table1_name, table2_name) values ('table1_name1',
    'table2_name1')
insert into @TABLE1_TABLE2 (table1_name, table2_name) values ('table1_name2',
    'table2_name2')
insert into @TABLE1_TABLE2 (table1_name, table2_name) values ('table1_name3',
    'table2_name2')

select t1.id as t1id,min(t2.id) as t2id
    from @TABLE1_TABLE2 tt
    inner join @TABLE1 t1 on tt.table1_name = t1.table1_name
    inner join @TABLE2 t2 on tt.table2_name = t2.table2_name
    GROUP BY t1.id

This returns:

1  1
2  2
3  2

I need it to be either

1  1
2  2
3  3

or

1  1
2  3
3  2

I was thinking my best bet would be to write an aggregate function, but CLR is not an option right now.  Rebuilding the relationship is not an option either, as we are stealing it from another database so the pk doesn't map, only the name.
The only thing that is working is doing it in a cursor, which unsurprisingly is slow enough that I have to get it set-wise.

EDIT:

If there are multiple entries of TABLE1, then this will breakdown.  For instance if we use this for TABLE1_TABLE2 instead (notice that we had to keep the old, no longer valid pk):

insert into @TABLE1_TABLE2 (oldid, table1_name, table2_name) values (123451234,
    'table1_name1','table2_name1')
insert into @TABLE1_TABLE2 (oldid, table1_name, table2_name) values (4563456,
    'table1_name1','table2_name2')

insert into @TABLE1_TABLE2 (oldid, table1_name, table2_name) values
    (3563456,'table1_name2', 'table2_name2')

insert into @TABLE1_TABLE2 (oldid, table1_name, table2_name) values
    (4563456,'table1_name3', 'table2_name2')
insert into @TABLE1_TABLE2 (oldid, table1_name, table2_name) values
    (123451234,'table1_name3', 'table2_name1')

Then to get the query to work, you have to map the old pk to a new one, like so:

 select * from @TABLE1_TABLE2 ga
    inner join ( select oldid, id as newid from (
    select oldid,max(table2_name) as table2_name, row_number() over(partition by
        max(table2_name) order by oldid) as rn from @TABLE1_TABLE2
    GROUP BY oldid ) tmp
inner join ( select table2_name,id, row_number() over(partition by table2_name order by
    id) as rn from @TABLE2) g on tmp.table2_name = g.table2_name and g.rn = tmp.rn )
    ids on ga.oldid = ids.oldid
inner join @TABLE2 g on ids.newid = g.id
inner join @TABLE1 a on ga.table1_name = a.table1_name

Thanks for the help!
Let me know if the edit is off topic now, wanted to share my discovery in case it saves someone some frustration.


texts: Does amazon rds PostgreSQL require vacuum?

texts: It isn't clear if auto vacuum is running in rds. Thanks!


texts: Cassandra backup from all nodes or just one?

texts:
Cassandra is a distributed database, where each node is in sync with the other nodes from the same ring/cluster.

When taking backups based on a snapshot, do I need to back up each node individually or is one enough?

The docs say:

  To take a global snapshot, run the nodetool snapshot command using a
  parallel ssh utility, such as pssh.

Am I missing a point here?


texts: How to get the settings associated with a stored procedure in relation to filtered indexes?

texts:
I quite like using filtered indexes in the proper context.

I have added the following filtered index:

USE [COLA];
CREATE UNIQUE NONCLUSTERED INDEX F_InterviewReport
ON [dbo].[tbl_application_documents] (  [DocumentID] ASC )
include( State )
WHERE (DocumentClass = 'cadirect.onlineforms.InterviewReport')
WITH (  PAD_INDEX = OFF, FILLFACTOR = 90   , SORT_IN_TEMPDB = OFF , ONLINE = OFF,
DROP_EXISTING = ON,
IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [NONCLUSTERED_INDEXES]

everything worked fine on server_staging.

but on server_production I got the following error message:

  Msg 1934, Level 16, State 1,  Procedure usp_ins_document_details, Line
  32 [Batch Start Line 2] INSERT failed because the following SET
  options have incorrect settings:  'ANSI_NULLS, QUOTED_IDENTIFIER'.

  Verify that SET options are correct for use with indexed views and/or
  indexes on computed columns and/or filtered indexes and/or query
  notifications and/or XML data type methods and/or spatial index
  operations.

Both servers are at the moment at this Cumulative update:

  Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) - 13.0.5337.0 (X64)
  May 16 2019 02:24:21   Copyright (c) Microsoft Corporation  Enterprise
  Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2
  Datacenter 6.3  (Build 9600: ) (Hypervisor)

the procedure in question is very trivial, it is posted here:

CREATE PROCEDURE [dbo].[usp_ins_document_details]
/*
 * author's name
 * 27/07/2004
 *
 * Amended
 * 28/10/2005
 *
 * Prevent the SP trying to insert the same record twice -- seems to
 * be happening with regards to the InterviewReport. Quicker to sort
 * this out that debug millions of lines of code.
 *
 * _sigh_ I am *such* a hack.
 *
 */
(
 @appid   numeric,
 @doc   varchar(50),
 @path   varchar(255)
)
AS
 DECLARE @already int

 SELECT  @already = COUNT(*)
 FROM  tbl_application_documents
 WHERE applicationid = @appid
 AND documentclass = @doc

 IF( @already = 0 )
  BEGIN

  INSERT INTO  tbl_application_documents
    (ApplicationID,DocumentClass,UserFilePath,DateCreated)
  VALUES  (@appid,  @doc, @path, getdate() )
  SELECT @@IDENTITY AS DocumentID

  END
 ELSE
  BEGIN

  SELECT  DocumentID
  FROM  tbl_application_documents
  WHERE applicationid = @appid
  AND documentclass = @doc

  END

questions:

How can I find out the current settings of the procedure in each of those servers?
SET options have incorrect settings:  ANSI_NULLS, QUOTED_IDENTIFIER - what are the correct settings?


texts: How to design database for polymorphic relationships

texts:
I have the following scenerio:

There are some categories
Each category has attributes
An attribute could be of free text or collection of options
There is a product which is assigned to category which needs to store values/options of attributes

How can a model be made without any repetition, and which correctly addresses the polymorphic nature of attributes?


texts: Can MongoDB support TTL per document, not per index?

texts:
I am trying to do a Redis-like collection in MongoDB. I need a collection of documents that expire and are automatically deleted after a certain time.
I can use TTL Indexes but with this, every document on my collection is deleted after a fixed amount of time (The time set on the index). I need that every document expires after a parameterized TTL, for example, in the same collection, I would like to insert a document that expires after 30 seconds and right after that, another that expires in like, 120 seconds.
Does MongoDB support this use case? or for this I should use Redis and store a TTL Key? (exactly what I need, but I would have to add Redis to the program while I already have Mongo)


texts: Can PAGELATCH Waits cause increase in SOS_SCHEDULER_YIELD wait in SQL Server?

texts:
We have a high concurrency web server application on which we have some amount of PAGELATCH_UP & PAGELATCH_SH Waits due to tempdb contention. But recently we have noticed the SOS_SCHEDULER_YIELD Waits also going up.

Could the PAGELATCH Waits cause the SOS_SCHEDULER_YIELD Wait to go up?

Also, the web server application and sql server are hosted on the same AWS cloud server.


texts: Does a covering nonclustered index help to answer Select * queries?

texts:
Our application developers run a lot of SELECT * queries against our database system, unfortunately.

The missing index suggestions of sys.dm_db_missing_index contain a lot of cases that indicate to create a 100% covering non-clustered index for those cases. For example actually there is an index with 3 index columns and additional 25 included columns suggested. Other indexes with the key columns suggested are already in place but then require lookups.

I am a little surprised about this. However since the underlying queries are running very often with high costs and the suggested index comes with an impact of about 50% this is maybe an interesting option.

I know it is not best practice and bad design to support those queries, but I doubt this could be changed within adequate time, so I wonder if those indexes will help at all or produce more costs than benefit?

Is there a rule or do I just have to try / error the consequences?

Row counts are between 60,000 and 2.1 million.
The missing indexes DMVs tell me on average (over multiple servers) 2424 seek operations could be performed for the queries (this is a number counting up continuously for about 3 months and the query cost of 19 in average could be impacted positively of about 50%.
Removing SELECT * would be best, but I am asked to optimize it as is, and since SQL Server itself suggests this, I think it is worth thinking about it anyway.
The existing queries are not really slow, < 1 sec, but this is quite subjective as well I think. Execution plan tells the key lookups cost 80% of the whole, so I guess saving this might be desirable anyways.
I did some tests without / with the 100% covering index. Results:

SELECT * :  Costs before: 0,026 after: 0,00329 (new index used) = -88% improvement
UPDATE: before: 0,043333 after: 0,0533 (ix used) = +20%
INSERT: before: 0,1133 after: 0,1233 (ix not used)=equal
DELETE: before: 0,645 after: 0,655 (ix used) =equal.

Conclusion: It tunes the selects much more than it slows down the changes. Why not apply?


texts: postgres sequence problems, manually created pids, and proper sequence resetting

texts:
Developers encountered issues with sequences in our postgres 9.6 database over the past year, where they stop working, so they have resorted to things like inserting rows with a pid manually created based on the last inserted record's pid, and resetting the sequence using this code:

SELECT pg_catalog.setval(pg_get_serial_sequence('mytable', 'pid'),
       (SELECT MAX(pid)+1 FROM mytable) );

I am experienced enough to know that we should be able to rely on the database to create our ids, and that creating one's own PIDs based on last max is not a 'best practice' nor safe for all scenarios, though it generally has worked for our daily use.

The reason they stopped working the first time is the well-known cause that a table was restored for example. However, at this point I think the manual code and sequences are stepping on each other, and the sequence resetting code is not solid. It seems obvious that inserting the last max will undermine the sequence, which simply has its own number it increments.

While I am reading up, I would like to know if someone point me in the right direction today to get the sequence back up, and working flawlessly, on an existing table - and have it work even though code is in place that will sometimes insert a pid from code, based on last max.  (Longer term, of course, I am fully aware it is best all such code will is dropped - but is there a way to work around that code for now?)

Included in a solution would be a way in postgreSQL 9.6 to have it reset the sequence itself if there is a conflict - not sure if possible, and am bracing for lectures from the more experienced out there - but that's why I am here!

Finally - and this is one disturbing fact that got me here - after resetting the sequence in pg admin I see two pids in the table in pg admin 3 and 4, which also shows in the create script.  The 'second' PID column does not show when doing \d in psql, which is good - but I thought it might be relevant.

UPDATE - on that last point, the ghost duplicate PIDs for a table in pg admin were caused because there were two sequences for that same column/table combo, the second created at some point to try and fix broken sequences over time (eg mytable_pid_seq and mytable_pid_seq1).  Not sure why/how this was allowed to happen in the database.


texts:
I am using GetAncestry function found at this post, The problem is when I try to use it with select query Mysql hang, I am not sure why it is happens my Mysql version is "5.5.16" any help appriciable

DELIMITER $$
DROP FUNCTION IF EXISTS `junk`.`GetAncestry` $$
CREATE FUNCTION `junk`.`GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(parent_id,-1) INTO ch FROM
        (SELECT parent_id FROM pctable WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END $$
DELIMITER ;


texts:
Can someone tell me what the icon of a server and a blue database symbol means. It is attached to database engine instance in my object explore view.

(I apologize, I am unable to add a image of the symbol at this time as I am new to stack overflow and am short two rep points needed to post images. If necessary when I cross the rep points threshold, I will attach an image.)

Here is a picture


texts: pg_restore fails for a pg_dump from a db that worked just fine

texts:
Any idea how this can happen? I have a table which I created by loading data into it via the COPY command. A column in it was declared as VARCHAR(200). I dumped the table, moved it to another computer, and tried to restore it but got an error

pg_restore: [archiver (db)] Error from TOC entry 4154; 0 259261 TABLE DATA foo postgres
pg_restore: [archiver (db)] COPY failed for table "foo": ERROR:  value too long for type character varying(200)
CONTEXT:  COPY foo, line 22556, column a: "สถานีอนามัยเฉลิมพระเกียรติพระบาทส?..."
pg_restore: *** aborted because of error

Update: Interestingly, when I check the length of this record, I get 82, safely less than 200.

SELECT Length(a) FROM foo WHERE a LIKE 'สถานีอนามัยเฉลิมพระเกียรติพระบาทส%'
82

And, yes, the new database is also set with UTF8 encoding.


texts:
I am trying to update ^G (Ctrl G) in a table, below is scenario-

CREATE TABLET TEST(COLA INT,DESCRIPTION VARCHAR(20));

I have crtl G in my data for description field, so to replace it with a \,I am running following command-

UPDATE TEST
SET DESCRIPTION=REPLACE(DESCRIPTION,CHAR(92),CHAR(7))
WHERE DESCRIPTION like '%' CHAR(07) '%';

It runs fine,converts the ^G into \ but it gives a warning as well

TRUNCATED INCORRECT INTEGER VALUE '%'

Can somebody please help me understand this warning.


texts: What exactly are secondary members acknowledging in replicasets with MongoDB?

texts:
I try to understand the mechanics and behavior of MongoDB's replica-features better. I hope to get some insightful information from someone who is familiar with mongo's internals and behaviour. My aim is to understand Mongo's durability guarantees in a high-availability setup. The data I want to maintain would have unique constraints (by using two unique indexes), to maintain a 1:1 relationship by having two unique values in one doc. Inconsistencies or only partial durability would be fatal for my 1:1 relation and hardly resolvable once happened, especially undetected.

My question:

What exactly are secondaries acknowledging in a replica-set setup and using the writeconcern:"majority"?

That only the statement is acknowledged and applied in the secondary or that the whole oplog up until the new statement is applied?

Explanation of what exactly I am looking for:

According to their own documentation and some other sources (e.g. Aphyr's try on simulating network-partitions and his follow-up) using write-concern "majority" will make sure, that no write will be lost even in partitioning-scenarios and the like. (since I cannot post more than two links, I will not be able to refence mongodb's documenation). Stale reads are acceptable in my use-case.

But one thing that I just cannot figure out from their documentation is, what exactly is acknowledged on the replicas.

Replica-Sets are replicated using the oplog (collection of statements that modified data) by distributing them asynchronously. For avoiding a rollback once a failed primary tries to join the cluster with writes, that were not distributed (because of the asynchronous nature), they recommend using the majority-writeconcern (Doc->Replica->Replica Concepts->High Availbilty->Rollback). To my understanding, there is a case, where this cannot be achieved, depending on what exactly is acknowledged.

Let's say we have three instances, (1), (2), (3). One is primary (1,P) and the other two are secondaries (2,S), (3,S).

We are now trying to insert two new rows/docs {1} and {2}. And due to network-instability, instances will be unavailable to each other at some times.

Let's say the replicasets acknowledge single statements from the primary, then the following can happen:

instance (3,S) is unavailable, but inserting record {1} will succeed since there are 2 out of 3 available (majority achieved).

Current state:

(1,P):{{1}}

(2,S):{{1}}

(3,S):{{}} unreachable

instance (2,S) becomes unavailable and instance (3,S) happens to be online (not restarted, just online to the others). Meanwhile no asynchronous replication was initiated by the primary. Record {2} is inserted. Majority achieved again (2 out of 3)

Current state:

(1,P):{{1},{2}}

(2,S):{{1}} unreachable

(3,S):{{2}}

If (1,P) goes down, an election will happen. (2) or (3) will become primary. But that will make the database inconsistent, since the new primary has no knowledge of the other lost doc/row. Now some sort of rollback is bound to happen when the original primary comes online (and thus losing data).

If acknowledges are made by applying all latest oplog-entries, then this should not happen. (2,S) would have both entries when acknowledging {2}. And then will also win the election. Data is not lost.

I am well aware that this is unlikely to happen. But if it happens, I don't see how it would even be detected or avoidable unless automatic failover is just disabled.


texts:
Hi I am attempting to backup the transaction log file to setup mirroring in SQL Server

I execute

BACKUP LOG CUSTOMER TO DISK ='K:\JonDB\CUSTOMER.trn' WITH INIT
GO

And I receive

Processed 6587361 pages for database 'CUSTOMER', file 'CUSTOMER' on file 1.
Processed 0 pages for database 'CUSTOMER', file 'CUSTOMER_log' on file 1.
Processed 6 pages for database 'CUSTOMER', file 'CUSTOMER_log2' on file 1.
BACKUP DATABASE successfully processed 6587368 pages in 969.013 seconds (46.948 MB/sec).
Msg 3049, Level 16, State 1, Line 5
BACKUP detected corruption in the database log. Check the errorlog for more information.
Msg 3013, Level 16, State 1, Line 5
BACKUP LOG is terminating abnormally.

Where is the error log? I have a lot of disk space available so it cannot be this. I need to create the backup of the database and transaction log file to setup mirroring I believe. If I can get around this step, it would be ideal.


texts: Optimization paramters read-only mysql 5.7 database?

texts:
What's the best way to MySQL database that is only read-only, and will never have any modifications made to it.

1) I have increased my innodb_buffer_pool_size to 70% of available RAM

2) Increase query cache size to 40mb

Running against a load generator, after some time the database starts bottlenecking to only 1000 statements per second.

The configuration is 2-cores, 8GB ram 50GB SSD. The database contains a single InnoDB 13 GB table with the same exact query running on two indexed columns.

We are using a vertx server to hit the database. The symptom we see is that the web service performance degrades after 15-20 minutes of heavy load.

Where can the bottle neck be? What parameters should we optimize?


texts: Delete records from single Azure DB table that has no linking - reference to database and/or server name .. not supported

texts:
Environment

Azure SQL database (migrated from SQL Server)
Old database originally (15 years+)
Dealing with a single table (trying to delete records)
Using an admin user
Using SSMS 2014
There are some triggers on the table (disabling them yields no change in error)
Name of database is databasename in our example here
There is an azure blob storage where files are stored and are "referenced" by an app via the records in said tblImages table -- but I cannot see any hard link between the two (is that even possible?)

I can select, insert and update records in this table, but cannot delete.

Failing Script

delete from
[databasename].[dbo].[tblImages]
where companyid = 123

Error:

  Msg 40515, Level 15, State 1, Line 91 Reference to database and/or
  server name in 'Azure.dbo.tblImages' is not supported in this version
  of SQL Server.

But who said anything about "Azure.dbo.tblImages' ?!?

Things I've tried

Have tried fully qualifying the table name, and not - no change.
Have tried deleting records using the "edit top 200" feature, same error
tried disabling all triggers on the table, no change
tried script "create to new window" but nothing about linked databases/servers or other table constraints

Here's a simple test that succeeds with entirely same environment:

CREATE TABLE _tmpPersons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

INSERT INTO _tmpPersons ( PersonID , LastName , FirstName , Address , City )
VALUES (123, 'Tore', 'Tess', '123 Main St.', 'Nowheresville');

select * from _tmpPersons;

delete from _tmpPersons where PersonId = 123;

select * from _tmpPersons;

I have already tried fully qualified [database].[schema].[table] and different combinations thereof.

It is definitely a table, and there ARE triggers, but disabling them changes nothing.

Zero results found from:

SELECT OBJECT_NAME(referencing_id), referenced_database_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL;


texts: Check for spaces or zeros in all columns in all tables

texts:
I have multiple tables in an Oracle schema that have columns with incorrect values:

Number columns where the value is zero, instead of null.
Text columns where the value is a space, instead of null.

I’m in the process of cleaning up the data. I will eventually create constraints on the columns to prevent bad data being entered in the first place. But before I do that, I need to find the columns that have existing problems and investigate them.
To start, I would like to produce a list of columns that have incorrect values.

For each table in a schema, provide a list of columns that have zeros or spaces as values.

table_name    column_name    datatype    value    count
—————————————————————————————————-————————————-———————-
roads         width          number      0        500
sidewalks     description    varchar2    [space]  10000
sidewalks     const_year     number      0        2000

Question:
What would it take to produce a list like this in Oracle?
(Note: I’m a public works technician by day, not a database administrator. Layman’s terms would be appreciated.)


texts: Increasing timeout period for schema compare

texts:
I need to increase the timeout period for schema comparison.

I'm using Visual Studio 2012 to compare two SQL Server 2008 R2 databases on Windows Server 2008.

I believe this is something I need to set in the Windows registry but I can't find where.


texts: Increase Max Memory setting on a SQL Server Production

texts:
I have a production box with 5 instances on.

Version: SQL Server 2014 SP3 Enterprise.

I discovered that even though the machine has ~400GB memory assigned, the combined MAX MEMORY settings on all 5 instances is less than 200GB.

The instances don't suffer from memory pressure, but as we already have that memory assigned, it's a waste not to use it.

I would like to increase it to higher value, leaving 10% for the OS (some instances will be assigned higher than others).

However, I've never done such a large increase of this setting before.

I know it’s a dynamic settings which doesn’t require a restart, however, I'm wondering on the following:

Should I make the increase in 2 phases (i.e increase half the amount, wait a week and then increase it again )?
Can something suddenly break (or make a performance degradation) if SQL server suddenly has so much more memory to play with ?

We don't have lock pages in memory setting enabled, and we don't have traceflag 834 on.


texts: Is there an easy way in PostgreSQL to escape newlines, carriage returns, and tabs?

texts:
Let's say I want to escape these to see a similar string

SELECT *
FROM ( VALUES
  (E'\r\n\t FOO BAR \t\n\r')
) AS t(str);

How do I get the c-style escapes, \r\n\t FOO BAR \t\n\r back out?

SELECT *
FROM ( VALUES
  (E'foo\nfoo',E'\n\tBAR\t','baz'),
  (E'quz','qaz','quz')
) AS t(x,y,z);
  x  |        y         |  z
-----+------------------+-----
 foo+|                 +| baz
 foo |         BAR      |
 quz | qaz              | quz
(2 rows)

Want instead..

  x       |        y         |  z
----------+------------------+-----
 foo\nfoo | \n\tBAR          | baz
 quz      | qaz              | quz
(2 rows)


texts: Replication compatibility between minor MySQL versions

texts:
I have a dedicated server running MySQL 5.7, installed using MySQL's Yum repository.

Yum always installs the latest version, which is MySQL 5.7.11 at the time of this writing.

I want to replicate this server to Amazon RDS, to have an offsite, real-time backup.

The problem is, RDS is always at least one minor version behind. To illustrate this, they just released MySQL 5.7 on RDS yesterday, yet they did not pick the latest version, and only offer MySQL 5.7.10.

So I would be replicating from a newer master to an older slave.

Is this acceptable, as long as we're talking minor versions?

The only page I could find on the MySQL website is this one:
https://dev.mysql.com/doc/refman/5.0/en/replication-compatibility.html

It basically says that you can only replicate from an older master to a newer slave, but they are talking major releases, i.e. 5.6 to 5.7.

This does not give me a clue to whether it is OK to replicate to a slave running an older minor version, e.g. from a 5.6.11 master to a 5.6.10 slave.


texts: MySQL 8: Truncate table stuck in `checking permissions` state for minutes

texts:
Sometimes but not always truncate table statements get stuck in checking permissions state for several minutes (up to 7-8 minutes).

I'm running MySQL 8.0.12 (for Linux on x86_64 (MySQL Community Server - GPL))
My tables are partitioned with around 365 partitions in each.

There are only 5 users (excluding root and mysql.). The 5 users are not tied to a specific host. Sometimes the same users is used from multiple hosts at same time.

Is this a known problem? What are the possible causes or solutions?

Any help is much appreciated!


texts: How do I configure my SQL Server instance to use a different Edition with minimal side effects?

texts:
I'm in the process of migrating an old server running Windows 2008 R2 with SQL 2012 Express to a new server running SQL Server 2017 Standard and Windows 2016. I would like to mirror databases between them, but the Express Edition doesn't support it. I tried to upgrade it to SQL 2012 Standard Evaluation, but I received the following error:

  Failed The selected SQL Server instance does not
  meet upgrade matrix requirements.

I discovered this isn't possible:
Upgrade SQL Server 2012 Express to Evaluation Edition

I was forced to install SQL Server 2012 Evaluation as a new side-by-side instance and named it ./SQLSERVER2. In order to use this instance, however, it would require moving a ton of data files and changing the instance names in all my configuration files. This will also require server downtime as well. This will be a major PITA without the humous and I want to avoid it if at all possible.

Is there a way of reconfiguring ./SQLSERVER to use the Standard Evaluation Edition with minimal downtime and side-effects?


texts: Restoring database to UNC path on local drive

texts:
When I try to restore a database using a restore command with a local UNC path:

RESTORE DATABASE [dbname]
FROM DISK = N'\\PC91\D\backup.BAK' WITH  FILE = 1,
MOVE N'test' TO N'\\PC91\D\dbname.MDF',
MOVE N'test_log' TO N'\\PC91\D\dbname_log.LDF',
NOUNLOAD, STATS = 10

I get an error:

  Msg 3634, Level 16, State 1, Line 1
      The operating system returned the error '5(Access is denied.)' while attempting 'CreateFileW' on '\PC91\D\dbname.MDF'.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

If I use a local drive letter instead, then it works:

RESTORE DATABASE [dbname]
FROM DISK = N'D:\backup.BAK' WITH FILE = 1,
MOVE N'test' TO N'D:\dbname.MDF',
MOVE N'test_log' TO N'D:\dbname_log.LDF',
NOUNLOAD, STATS = 10

This command also restores the database to same folder. So why is there an error when I specify the network path?


texts:
Is there a way to hide the most bottom status bar? It doesn't have that much useful information (line number, column number, etc) and takes a decent amount of screen real-estate. I don't think it's hideable but I figured I'd ask.

See image below

SSMS 17.6


texts: Changing field length when foreign keys reference primary key field in table

texts: I have a situation where a number of foreign keys are referencing a column in Table "A". I would like to change the length of the column in table "A". What is the best way to accomplish this in SQL Server 2008 R2? I am really hoping I don't have to drop all 30 foreign key constraints and re-create them again...


texts: Find IDs from a list that don't exist in a table

texts:
Say I have the following schema and data:

create table images(
  id int not null
);

insert into images values(1), (2), (3), (4), (6), (8);

I want to perform a query like:

select id from images where id not exists in(4, 5, 6);

But this doesn't work. The case above should return 5, since it doesn't exist in the table records.


texts: How to bring all replicas on a SQL availability group to an earlier state / point in time?

texts:
I have a SQL Always on availability group, with 4 replicas. One of them is the primary. Lets say someone accidentally drops a table on the primary. This transaction would go to all of the secondary replicas.

Now if I want to restore the state of the entire group to before the table was dropped, what would be the correct (time efficient) sequence of steps to take? Assuming I have all the full and transaction backup files needed.

From my current understanding, restoring a back up on the primary alone would cause a data conflict (or corruption issues) on the secondary replicas - so I have to suspend data movement and drop the availability databases on the secondary before I restore a back up, then re-seed the databases from scratch on the secondary replicas. Is this is the best approach? Thanks.


texts: SQL Server cluster not available after failure of the "main" server

texts:
I aksed this question on stackoverflow, but was asked to repost here so here it is:

I have a database that I'm setting up in a clustered environment. It'll be two servers with a shared storage. ServerA is the server where I setup the database, and ServerB is a node in the SQLServer cluster.

If I forcibly turn off ServerB everything keeps on working as expected, but if I turn off ServerA the instance is not available anymore.

Windows cluster is still alive (I can remote desktop onto the cluster via shared name), but the SQL is not visible, and cluster manager reports the resource as offline.

This kinda defeats the purpose of the cluster and I need to solve it, but it's not really in my area of expertise, and I have no idea where to start.

Thanks in advance.

EDIT (Additional info asked in comments): Windows was installed I added clustering feature for DTC and SQL Server. As for access, IP addresses of the machines are *.101 for main; *.102 for second server; *.99 for access point SQL server instances and *.111 for cluster manager. I can access 99 from both machines but can't access database at 101 or 102 from either.

EDIT #2 -  I tried increasing the failover threshold (to 50 in 24 hours, for out testing purposes), but the server still doesn't wake up after cutting off the network for server1. The cluster comes back in about 20 sec or so, but SQL Server stays down. In the logs I have serveral errors and one Critical level message that is probably causing the fail. Message is:

  The Cluster service is shutting down because quorum was lost. This
  could be due to the loss of network connectivity between some or all
  nodes in the cluster, or a failover of the witness disk.  Run the
  Validate a Configuration wizard to check your network configuration.
  If the condition persists, check for hardware or software errors
  related to the network adapter. Also check for failures in any other
  network components to which the node is connected such as hubs,
  switches, or bridges.


texts:
So I'm fairly new to AWS and I'm hoping you guys can reassure me as I couldn't find anything in the AWS documentation either way. I actually have two questions, the first:

I have an RDS MySql instance in AWS that wasn't set up by myself (a previous employee did this). This instance is more powerful than we need so I want to reduce it's instance class in order to reduce costs.

Can I simply change it and apply the changes at the next maintenance schedule without loosing data?

The current instance is a multi-AZ configuration.

The second:

How can I identify how much of the RDS storage is currently in use? As I'd like to decrease this if possible as we aren't even using 50% of it.

Thanks,


texts: How do I add a where statement at the end of query for a column that does not have a table to reference from?

texts:
select
CASE WHEN (to_char(((case when v.trip_order_start_date is null then case when v.manual_start_date is null then t.required_pickup_date else v.trip_order_start_date end else v.trip_order_start_date end  + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago' + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') < to_char((now() at time zone 'utc' at time zone 'America/Chicago'), 'YYYYMM')
        AND(coalesce(posted_date, paid_date) IS NULL
            OR to_char((coalesce(v.posted_date, v.paid_date) + (interval  '-1 hours' * ofc.offset)) at time zone 'utc' at time zone 'America/Chicago', 'YYYYMM') > to_char((now() at time zone 'utc' at time zone 'America/Chicago' - interval '1' month), 'YYYYMM'))) THEN 'YES' ELSE 'NO' END AS accrual

WHERE
accrual = 'YES'

The problem is there is no table for me to reference the "accrual" column so I get the below error
[Code: 0, SQL State: 42703]  ERROR: column "accrual" does not exist
Position: 10708  [Script position: 10708 - 10715]


texts: How to index for multiple inequality WHERE search conditions?

texts:
I am thinking on the problem, if there is a multiple columns on which search must work as fast as possible.

For example, see the table TEST(COL_A, COL_B) with a (tree) index TEST_IDX(COL_A, COL_B). This index can be used for the following WHERE conditions:

WHERE COL_A='x' AND COL_B='y'
WHERE COL_A='x' AND COL_B<'y'

But what to do, if both of the condition terms are using inequality? So I am thinking on a WHERE COL_A<'x' AND COL_B<'y'? AFAIK, normal record-tree indices in such cases can't work. I can imagine some data structure which could make such queries also fast, but I think it should use a much sophisticated data structure as a simple tree.

Do this in MySQL exist? Or in another SQL servers?


texts: Can I mysqldump an entire database while using where='condition' on individual tables?

texts:
I have used mysqldump to dump an entire database,

However, when I want to use --where=condition", on individual tables, I have dumped individual tables

mysqldump  --where='condition' mydb table > table.sql

and then reconstructed the database table by table

 for i in *sql; do mysql newdb < i; done

Is it possible to use the where clause with mysqldump on specific tables when dumping an entire database?


texts: Select from arrays in a way similar to IN

texts:
I have this table and data:

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[]
);

INSERT INTO sal_emp
VALUES
   ('Bil'  , '{1, 2, 3, 4}'),
   ('Bill2', '{5, 6, 7, 8}');

I can run query:

select name from sal_emp where pay_by_quarter @> ARRAY[1,4];

And got "Bil".

Is there a way to run a query like:

select name from sal_emp where pay_by_quarter @> ARRAY[1,4,5]

and get "Bil", "Bill2"?


texts:
I'm working on a design for a database for school timetable and I need some help. So, basically, the database is made up of tables like teacher, student, subject, TimePeriod, and Classroom.

I would just like some advice to see if what I'm doing is allowed. It might not necessarily be good, but I'm beyond that.

I’ve created a many-to-many relationship between subject and timePeriod so essentially I'm saying that many Subjects are allocated to many timePeriods, and I've created an "association" table called SubjectPeriod between these. I've then linked two other tables to the SubjectPeriod table like below:

SubjectPeriod (TimePeriodID, SubjectD, ClassroomID, TeacherID, Details)

Classroom and Teacher are connected to SubjectPeriod as a one-to-many as in:

One teacher teaches many SubjectPeriods
And One Classroom hosts Many SubjectPeriods

Does this make sense or is it even allowed?

I'm not too confident with database design so I'm struggling a little with this.


texts: Not all columns show up on select * on DB2 for IBM i

texts:
My client has a table on their IBMi with a foreign key to another table. It's not showing up with a SELECT * FROM lib.table. However, if I specifically mention it in the column list. It does show up.

SELECT
        col1, col2.col3, fxcolid
    FROM  lib.table

I'm using the jt400 driver to query from SQLWorkbench/J.


texts: Do varchar(max), nvarchar(max) and varbinary(max) columns affect select queries?

texts:
Consider this table:

create table Books
(
    Id bigint not null primary key identity(1, 1),
    UniqueToken varchar(100) not null,
    [Text] nvarchar(max) not null
)

Let's imagine that we have over 100,000 books in this table.

Now we're given a 10,000 books data to insert into this table, some of which are duplicate. So we need to filter duplicates first, and then insert new books.

One way to check for the duplicates is this way:

select UniqueToken
from Books
where UniqueToken in
(
    'first unique token',
    'second unique token'
    -- 10,000 items here
)

Does the existence of Text column affect this query's performance? If so, how can we optimized it?

P.S.
I have the same structure, for some other data. And it's not performing well. A friend told me that I should break my table into two tables as follow:

create table BookUniqueTokens
(
    Id bigint not null primary key identity(1, 1),
    UniqueToken varchar(100)
)

create table Books
(
    Id bigint not null primary key,
    [Text] nvarchar(max)
)

And I have to do my duplicate finding algorithm on the first table only, and then insert data into both of them. This way he claimed performance gets way better, because tables are physically separate. He claimed that [Text] column affects any select query on the UniqueToken column.


texts:
How can I check if a table contains duplicate rows, with the same value of the couple  (date, time) ?
How can I remove all but one row ?
Id is a sequential number that I increment after every post, how can I restore it after removing a row ?

the table tableTen is:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | YES  |     | NULL    |       |
| symbol    | varchar(25) | YES  |     | NULL    |       |
| date      | date        | YES  | MUL | NULL    |       |
| time      | time        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+


texts: Unable to conditionally commit/rollback transaction

texts:
I am trying to create a reuable SQL script that I can run that will guard against unintentional db changes based on the expected number of affected rows (not looking to create a sproc/func at this time.)

Using the answer to this SO question, I tried implementing the same but am not having much luck.

I have...

/*create table test (
    val int
);
insert into test (val) values (1);
insert into test (val) values (2);
insert into test (val) values (3);
insert into test (val) values (4); */

SET SQL_SAFE_UPDATES = 0;
set @@autocommit = 0; /* troubleshooting, doesnt seem to affect outcome */
start transaction;

set @expected = 2;  /* should cause rollback */

delete from test where val > 3;

set @rc = Found_rows();
select @rc as row_count;

set @tran = (select if(@rc = @expected,'ROLLBACK','COMMIT'));
prepare stmt from @tran;
execute stmt;

select * from test;

SET SQL_SAFE_UPDATES = 1;

But it appears to be committing the transaction no matter what.

I don't imagine it matters, but this is on an AWS RDS (10.0.24-MariaDB) via MySQL Workbench 6.3

I'm more familiar with MSSQL (if that helps any.)

PS: Also found this suggestion (which I am less certain about.)


texts: MySQL 5.5 Replication to Galera/Percona XtraDB Cluster

texts:
We are currently investigating moving our current MySQL 5.5 Master > 5 x Slaves (with Statement Based Replication, apx 250GB data and growing) to a HA clustering option. Mainly to provide greater resilience, write scaling, and easier to carry out admin stuff like Optimising tables without taking the master offline.

We have ruled out (for the moment) MySQL ndb Cluster as it would require too many changes to our current database structure (large number of BLOB fields amongst other problems).

So we are looking at either Galera Cluster or Percona Cluster as the alternatives, as the they seem to do what we want, without 'too' steep a learning curve.

We would want to start off by setting the Cluster up as a Slave to the main Master (for testing etc), before moving over to the Cluster as our Primary system in the future. Is this possible? Reading through the documentation doesn't seem to really talk about this side of things (unless I've missed it).


texts: What is the difference between a "record" and a "row" in SQL Server?

texts:
There was a rather innocuous question about adding dates and times in SQL Server that set off a rather fascinating taxonomic debate.

So how do we differentiate between these related terms and how we use them properly?

Row

Record


texts: I recently set up a ramdisk tmpfs on our mysql server and the performance improvement has been quite noticeable.  I've allocated 24 out of 32 GB's of ram for the ramdisk.  My only concern is what happens when there's a terrible query that ends up requiring more space than the allocated tmpfs?  I've read in a few places that it depends on the type of query but wanted to confirm that in some instances such a query can effectively shut down mysql.


texts: Query to return all Oracle Deadlocks in 12c

texts:
Is there an object I can query in 12c to return all ORA-60 Deadlock alerts?

I just want to do a weekly report that outputs the number of deadlocks that have occurred and what schema they occurred on.

My Oracle knowledge is not great, and I have drawn a blank when searching for resources on this online.

Thanks


texts: SQL Server 2014 vulnerabilities reported by Nessus

texts:
I have some vulnerabilities reported for my SQL Server 2014 instance:

  List of RC4 cipher suites supported by the remote server :

 High Strength Ciphers (>= 112-bit key)

   TLSv1
      RC4-MD5                      Kx=RSA         Au=RSA      Enc=RC4(128)             Mac=MD5
      RC4-SHA                      Kx=RSA         Au=RSA      Enc=RC4(128)             Mac=SHA1

  The fields above are :

 {OpenSSL ciphername}
  Kx={key exchange}
  Au={authentication}
  Enc={symmetric encryption method}
  Mac={message authentication code}
  {export flag}

  SSLv3 is enabled and the server supports at least one cipher.

  Nessus determined that the remote server supports SSLv3 with at least one CBC
      cipher suite, indicating that this server is vulnerable.

  It appears that TLSv1 or newer is supported on the server. However, the
  Fallback SCSV mechanism is not supported, allowing connections to be "rolled
  back" to SSLv3.

My OS is Windows Server 2012, and use Nessus to scan my Server. How can I fix the above vulnerabilities?


texts: Why is this simple query taking more than 1 second?

texts:
The table has around 17M rows and is MyISAM.

select DateModified,PELTM,PSLTM
from financial_key_ratios
where SecurityId = 4525
  and DateModified >= '2015-01-07';

This simple query seems to take more than 1 second a lot of times.

The indexes seem to be fine. Here is the output from explain:

 explain
 select DateModified,PELTM,PSLTM
 from financial_key_ratios
 where SecurityId = 4525
   and DateModified >= '2015-01-07';
+----+-------------+----------------------+-------+------------------------------------------------+------------+---------+------+------+-------------+
| id | select_type | table                | type  | possible_keys                                  | key        | key_len | ref  | rows | Extra       |
+----+-------------+----------------------+-------+------------------------------------------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | financial_key_ratios | range | SecurityId,secid,DateModified_index,secid_date | secid_date | 7       | NULL |  156 | Using where |
+----+-------------+----------------------+-------+------------------------------------------------+------------+---------+------+------+-------------+

What could we be doing wrong?

CREATE TABLE `financial_key_ratios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `SecurityId` int(11) NOT NULL,
  `PELTM` decimal(12,4) NOT NULL,
  `PSLTM` decimal(12,4) NOT NULL,
  `POCFLTM` decimal(12,4) NOT NULL,
  `PEG` decimal(12,4) NOT NULL,
  `PBV` decimal(12,4) NOT NULL,
  `EVEBTIDA` decimal(12,4) NOT NULL,
  `Evbyrevenue` decimal(12,4) NOT NULL,
  `Fcf_yeild` decimal(12,4) NOT NULL,
  `Earnings_yeild` decimal(12,4) NOT NULL,
  `Pe_annualized` decimal(12,4) NOT NULL,
  `Pbys_annualized` decimal(12,4) NOT NULL,
  `Pbybv_annualized` decimal(12,4) NOT NULL,
  `Evbyebitda_annualized` decimal(12,4) NOT NULL,
  `Evbyrevenue_annualized` decimal(12,4) NOT NULL,
  `Pe_ltm_adj` decimal(12,4) NOT NULL,
  `Enterprise_value` decimal(12,4) NOT NULL,
  `Marketcap` decimal(12,4) NOT NULL,
  `DividendYield` decimal(12,4) NOT NULL,
  `DateModified` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `SecurityId` (`SecurityId`,`DateModified`),
  KEY `secid` (`SecurityId`),
  KEY `DateModified_index` (`DateModified`),
  KEY `secid_date` (`SecurityId`,`DateModified`)
) ENGINE=MyISAM AUTO_INCREMENT=65138826 DEFAULT CHARSET=latin1 |

Update:

I already have all the indexes in place. It is NOT always slow. Out of 14K requests, it is reported in slow query log around 100 times.

Here is the update from slow query log

Time=1.22s (68s)  Lock=0.00s (0s)  Rows=249.2 (13955),
select DateModified,PELTM,PSLTM from financial_key_ratios
where SecurityId = N and DateModified >= 'S'


texts: How to display one name in three tables many-to-many relationship?

texts:
I have three tables (student's_info, subjects and marks):

This is student's_info:

----------------
|s_id | s_name |
----------------
| 1   | Liza   |
| 2   | Jake   |
----------------

This is subjects:

------------------
|sub_id | subject|
------------------
| 1   | Word     |
| 2   | Windows  |
| 3   | Excel    |
------------------

and this is marks:

-----------------------------------
|id | s_id |sub_id| n1  | n2 | n3 |
-----------------------------------
| 1 |  1   |  1   |  50 | 20 | 10 |
| 2 |  1   |  2   |  44 | 33 | 23 |
| 3 |  1   |  3   |  90 | 44 | 31 |
| 4 |  2   |  1   |  32 | 12 | 11 |
| 5 |  2   |  2   |  56 | 28 | 32 |
| 6 |  2   |  3   |  66 | 33 | 44 |
-----------------------------------

I have this query to select data:

SELECT students_info.*, marks.*, subjects.subject
  FROM subjects
         RIGHT JOIN (students_info
                       RIGHT JOIN marks ON students_info.s_id = marks.s_id
                    ) ON subjects.sub_id = marks.sub_id";

The query result is:

Liza  Word     50 20 10
Liza  Windows  44 33 23
Liza  Excel    90 44 31
Jake  Word     32 12 11
Jake  Windows  56 28 21
Jake  Excel    66 33 44

My question is: Why is the sutdent's name repeated? How can I get blank out the student's name if it's the same as in the previous row?


texts: ola.hallengren - Alternative MySQL scripts!

texts:
Having worked with MS SQL for many years and found ola.hallengren scripts incredibly useful - I find my self in a MySQL environment.
Is there a MySQL alternative to ola.hallengren? - Perhaps someone has created a batch of scripts to show worst performing queries, re-indexing jobs etc that is popular in the area of Database optimisation, maintenance etc.
I've reviewed Optimize a MySQL Database using MySQL Workbench - Although useful, doesn't quite give me the flexibility of what I need - that Ola answers with there scripts.


texts: Dynamic database reference in T-SQL scripts

texts:
We have a set of databases (development, QA and production, dev-foo & dev-bar, qa-foo & qa-bar and just foo & bar respectively).

Inside foo family of databases there's a view that should use respective bar database to do cross-database joins. In other words,

select f.*, b.* from foo f inner join <bar-db>.dbo.bar b on f.barid = b.id

The question is: is there any way I could write this script once and then have a database-level variable <bar-db> that would point to an appropriate "instance" of a bar database?


texts:
I hope that someone can help me.

I need to search to filter out values in a database. The database has about 14 000 records of Koalas that have come into care.

Each Koala has their own name with a fate that has happened with it.

I need to extract the koalaName for the Koala that has been Relocated, Rescued, or In Care but eliminate the koalaName if it has been eventually Found Dead.

In the case of the output above Alec is eliminated in the results as he has eventually Died, Euthanased or Found Dead. In the case of Aleta and Alex I just need to get their name once.

This is the query I've been trying to work with.

SELECT `id`, `koalaName`, `fate`
FROM main  A
    WHERE EXISTS
(
    SELECT `id`, `koalaName`, `fate`
    FROM    main  B
    WHERE   A.koalaName = B.koalaName
    AND     B.`fate` REGEXP 'Released|Relocated|Care'
)
AND NOT EXISTS
(
    SELECT `id`, `koalaName`, `fate`
    FROM    main  B
    WHERE   A.koalaName = B.koalaName
    AND     B.`fate` REGEXP 'Died|Euth|Dead'
)

I just don't seem to be able to get the result to output the koalaName as a single value instead of returning multiple values

e.g. Alex should only appear once.

Many thanks in advance, I think its simple but I just don't seem to be able to do it.


texts: How does MySQL (InnoDB engine) read data from disk? What scanning algorithm does it use? Since the rows are stored in data pages will it read the entire data page to get the resulting rows?


texts: SQL Server TDE with PKI - converting P12/PKX to PVK/DER format

texts:
To use TDE for SQL Server we are obliged to use a PKI certificate. The certificate has the format .p12(.pkx). Microsoft offeres a certficate converter for SQL Server. Using it - it creates three certificates and one pvk file.

C:\Program Files\Microsoft\PVKConverter>PVKConverter.exe -i D:\path\nameofpkicert.pkx -o mycert -d Password -e Password2
Processed PFX certificate D:\path to generat
e 3 PVK/DER certificate pairs.
Generated PVK/DER certificate pairs:
(mycert_1.cer)
(mycert_2.cer)
(mycert_3.cer, mycert_3.pvk)

How do I import this files to a SQL Server?


texts: Unique Constraint with multiple null columns

texts:
I have two tables

PC(Id,EmpName Not NULL, PCName NULL, HostName NULL,.... PhysicalLocation NULL, PCType Not NULL)
PCNetwork(Id, EmpName Not NULL, PCName NULL, HostName NULL,IPAddr,....,PhysicalLocation NULL, PCType Not NULL)

Id is a surrogate key, EmpName and PCType are not null fields and the rest can be null.
I created Unique Constraint on PC with these columns and FK on PCNetwork. If there is a null in one of the column of a record then the constraint works perfect but if more than one column has null the constraint is not working. This is a follow up question of this post

Edit:- My unique constraint and foreign key constriant is not working

alter table PC add constraint UK_PC_EMPName_PCName_PCType
unique (EMPName, PCName, HostName, PhysicalLocation, PCType);

alter table PCNetwork add constraint FK_PC_PCNetwork
foreign key (EMPName, PCName, HostName, PhysicalLocation, PCType)
references PC (EMPName, PCName, HostName, PhysicalLocation, PCType);


texts: Best way to populate a new column in a large table?

texts:
We have a 2.2 GB table in Postgres with 7,801,611 rows in it. We are adding a uuid/guid column to it and I am wondering what the best way to populate that column is (as we want to add a NOT NULL constraint to it).

If I understand Postgres correctly an update is technically a delete and insert so this is basically rebuilding the entire 2.2 gb table. Also we have a slave running so we don't want that to lag behind.

Is there any way better than writing a script that slowly populates it over time?


texts: Reference a non primary key in another table in postgresql

texts: I have a two columns should references two columns in another table. And those two columns are not primary key. I think I can't use foreign key, because it can only references primary keys.


texts: Strange Issue with SQL Server to Oracle Linked Server Select Query

texts:
I have a SQL Server (2014 SP2) with a linked server to an Oracle 11 database.

I have a very simple select statement to an Oracle view which I know should return around 140k rows. But here's the thing, when I run it in SQL Server I immediately see records in the results window, but all of a sudden, the query hangs when it has so far returned only around 2000 rows and it just sits there forever doing nothing else. Sometimes, another few thousands rows appear before it again hangs.

The wait state on the query is on OLEDB which is to be expected. I'm thinking some sort of Network bottleneck...?

Sometimes I do get an error after a while: Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle"

Before anyone suggests it, 'allow in process' is ticked on the provider settings.

I'm looking for ideas to troubleshoot this at either the SQL Server side or the Oracle side to check that it's not a database config issue.


texts: Select children of product, with no other active parents

texts:
Given these two tables in Postgres 9.6:

CREATE TABLE product (
  id bigint PRIMARY KEY,
  status text
);

CREATE TABLE products_m_products (
  parent_product_id bigint REFERENCES product,
  child_product_id  bigint REFERENCES product
);

How to select all active children of a given product that have no other active parent?

As I understand it needs to be something like this:

Get all children of product A.
Get all the parents of the children.
Filter out all non active (status = 'DELETED') parents.
Count the remaining parents for each children.
Keep children whose parent count is 1 or less.

But how do I put this into a query?

I tried this:

select distinct child, parent
from product as child,
     product as parent,
     (select child_product_id
          from products_m_products
          where parent_product_id = 1337) as children,
     products_m_products as parents
where child.id = children.child_product_id
    AND parents.child_product_id = children.child_product_id
    AND child.product_status = 'ACTIVE'
    AND parents.parent_product_id = parent.id
    AND parent.product_status = 'ACTIVE';

But I'm not sure if this is correct.

We can assume that A is active, but it should be excluded from the final result since A will be deactivated.


texts: Unexpected query execution plan on MySQL 8 when using Datetime range

texts:
I have a MySQL 8 database with about 900 millions of rows. When I run the following query the response is very slow because table B doesn't seem to be using the right index:

SELECT  B.name

FROM A JOIN B ON A.key1 = B.key1 AND A.key2 = B.key2
WHERE
    A.active = True
    AND B.datetime  > '2020-05-28 00:00:00' AND B.datetime  < '2020-05-29 00:00:00'
    AND A.type = 1

The query has the following execution plan (EXPLAIN FORMAT=JSON):

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "10202799.44"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "A",
          "access_type": "ref",
          "possible_keys": [
            "ix_A_type_key1",
          ],
          "key": "ix_A_type_active",
          "used_key_parts": [
            "type",
            "active"
          ],
          "key_length": "5",
          "ref": [
            "const",
            "const"
          ],
          "rows_examined_per_scan": 62738,
          "rows_produced_per_join": 62738,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "62738.00",
            "eval_cost": "6273.80",
            "prefix_cost": "69011.80",
            "data_read_per_join": "1G"
          },
          "used_columns": [
            "active",
            "type",
            "key1",
            "key2"
          ]
        }
      },
      {
        "table": {
          "table_name": "B",
          "access_type": "ref",
          "possible_keys": [
            "ix_B_key1_datetime",
            "ix_B_key2_datetime"
          ],
          "key": "ix_B_key2_datetime",
          "used_key_parts": [
            "key2"
          ],
          "key_length": "258",
          "ref": [
            "A.key2"
          ],
          "rows_examined_per_scan": 147,
          "rows_produced_per_join": 3136,
          "filtered": "0.03",
          "cost_info": {
            "read_cost": "9211170.19",
            "eval_cost": "313.69",
            "prefix_cost": "10202799.44",
            "data_read_per_join": "96M"
          },
          "used_columns": [
            "key1",
            "key2",
            "datetime",
            "name",
          ],
          "attached_condition": "((`B`.`key1` = `A`.`key1`) and (`B`.`datetime` > TIMESTAMP'2020-05-28 00:00:00') and (`B`.`datetime` < TIMESTAMP'2020-05-29 00:00:00'))"
        }
      }
    ]
  }
}

Table B In the used_key_parts it's only using the column key2 instead of the full composite index key2 + datetime (ix_B_key2_datetime). Why is not using the datetime part of the composite index? Is there a way to speed up this query?


texts: Throw and RaiseError of level 16 do not cause alert to occur

texts:
The next alert has been created:

USE [msdb]
GO

/****** Object:  Alert [Notify]    Script Date: 09/09/2014 11:38:31 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Notify',
        @message_id=0,
        @severity=16,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=0,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

I expect that the next code will cause it to occur:

THROW 51000, 'The record does not exist.', 1;

Or the next code:

 RAISERROR ('Error raised.', 16,  1);

System Messages does not contain the error messages:

SELECT *
FROM sys.messages
WHERE language_id = 1033
and severity>=16
ORDER BY severity asc, message_id asc

I also have no events in History tab:

What am I missing?


texts: How can extract the table schema from just the .frm file?

texts:
I have extracted the data directory of mysql from a backup and need to get the schema from an old table, however it's a backup from a different machine.

I have read a fair number of tutorials today on how to do this, but each time it seems to fail or I end up having to reinstall mysql because mysql hangs or crashes. I have tried the following:

Create a different database
Create table with same name in that database
Replace file
Stop/Start engine
Recover from the .frm file

I have tried in various orders and combinations.

Is there any external tool that can extract the schema from the .frm file? I can see the column names if I open up the file. I had a look but I can't seem to find anything that will enable me to do this.

Thanks in advance.


texts: COUNTING results per row in self referenced table

texts:
I have a table called Clients. And at this table, I have an idFather to know who added this row to the table.

Sample:

id | user    | idFather | createdAt
---+---------+----------+-----------
01 | raphael | null     | 01-06-2016
02 | jose    | 01       | 03-06-2016
03 | ryan    | 01       | 02-06-2016
04 | alyssa  | 03       | 04-06-2016

I'm trying to count how many clients each client invited to the system this month.

I'm using this query to count, but I'm doing the counting using PHP. I want to do it in MySQL.

SELECT COUNT(*) FROM client
    WHERE idFather = 1
    AND MONTH(createdAt) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

For now, I'm doing it like this:

Get all results from database, and for each client, executing the query above. I'm looking to execute a query and get it counted from the database without the need look at each one with PHP. Today I have just 60 clients in the table, but next month, it can be 1 thousand, or more. It will not work properly with php.

Thanks for the help!

EDIT:
I'm looking to get a query result like this:

id | user    | idFather | createdAt  | totalInvited
---+---------+----------+------------+-------------
01 | raphael | null     | 01-06-2016 | 2
02 | jose    | 01       | 03-06-2016 | 0
03 | ryan    | 01       | 02-06-2016 | 1
04 | alyssa  | 03       | 04-06-2016 | 0


texts:
I have a stored procedure that is supposed to delete several rows from a table. It does not work and gives me the error:

The transaction ended in the trigger. The batch has been aborted

I tried running the individual queries to see if they would work separately. I found that the error is that it doesn't let me delete more than 1 row at a time. Here is an example:

DELETE FROM DetalleBusqueda
WHERE IdResultado IN (106577, 106578, 106579)

That query does not work, but the following one does:

DELETE FROM DetalleBusqueda
WHERE IdResultado IN (106577)

The first query is supposed to delete 3 rows, and the second one only 1 row. The second one does what it's supposed but the first one gives me the aforementioned error.

Does anyone have any idea what is it that I have to do so the procedure can delete more than a row at a time?


texts: Can't create triggers in MySQL, no errors but no triggers either

texts:
I'm trying to create triggers without much success I'm not getting any error but the triggers don't show up either... So what's wrong with this?

USE `wa3`;

CREATE TABLE `transactions` (
    `id` CHAR(36) NOT NULL,
    `account_number` INT(11) NOT NULL,
    `transactions` INT(11) NOT NULL,
    `debit` DECIMAL(16,2) NULL DEFAULT NULL,
    `credit` DECIMAL(16,2) NULL DEFAULT NULL,
    `balance` DECIMAL(16,2) NOT NULL,
    `created` DATETIME NULL DEFAULT NULL,
    `modified` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

DELIMITER //
CREATE TRIGGER `wa3`.`InsertCreditOrDebitNotNull` BEFORE INSERT ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NULL AND NEW.debit IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both be null';
  END IF;
END;
CREATE TRIGGER `wa3`.`UpdateCreditOrDebitNotNull` BEFORE UPDATE ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NULL AND NEW.debit IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both be null';
  END IF;
END;
DELIMITER ;

DELIMITER //
CREATE TRIGGER `wa3`.`InsertBothCreditAndDebit` BEFORE INSERT ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NOT NULL AND NEW.debit IS NOT NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both have a value';
  END IF;
END;
CREATE TRIGGER `wa3`.`UpdateBothCreditAndDebit` BEFORE UPDATE ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NOT NULL AND NEW.debit IS NOT NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both have a value';
  END IF;
END;
DELIMITER ;

SHOW TRIGGERS;

  /* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 2 queries: 0.015 sec. */


texts: Multiple inserts of batch insert over the wire?

texts:
I am generating about 500 records.

What I want to do is INSERT them all at the same time because:

The server is located elsewhere and I would rather send one big payload over.
This seems like less overhead than sending 500 separate insert queries over the wire.

But because they're batch inserting, I don't want to rollback everything if one insert fails - I want to try to insert the rest in there and record the failure(s).

What's the best way to do this in SQL Server? Is BULK INSERT what I want?


texts:
I have a doubt about the use of oracle tablespaces. I've decided to define for each schema of the datababase two different tablespaces (one for data, and another one for indexes).

The problem is that some time has passed, and my developers had mixed the tablespaces creating indexes in the data tablespace and creating tables and partitions in the index tablespace.

I would like to know how this could impact on performance.

Thanks in advance


texts: What benefit is there from enabling Query Store on msdb?

texts:
Of the SQL system database (master, model, msdb, tempdb) query store can only be used on msdb. I looked and don't find any documentation about query store on msdb.

While you can't see it in the GUI, it can be validated on your SQL 2016 instance

Validate Query Store is off

USE msdb
SELECT * FROM sys.database_query_store_options;

Turn Query Store on

USE [master]
GO
ALTER DATABASE msdb SET QUERY_STORE = ON
GO
ALTER DATABASE msdb SET QUERY_STORE (OPERATION_MODE = READ_WRITE
, INTERVAL_LENGTH_MINUTES = 30
, MAX_STORAGE_SIZE_MB = 1000
, QUERY_CAPTURE_MODE = AUTO)
GO

Validate Query Store is on

USE msdb
SELECT * FROM sys.database_query_store_options;

Of all the system database why is msdb the only one with the option to use Query Store, and what value does it add?

-- Stop Query Store
USE [master]
GO
ALTER DATABASE msdb SET QUERY_STORE = OFF
GO


texts: row_number over partition at Version < Firebird 3


texts: Why is pg_stat_activity.query_start null for almost all queries?  What is the meaning of a NULL query_start field?


texts: How to automatically execute a stored procedure every day at a particular time?

texts: Can you please help me how to execute a stored procedure automatically in SQL database without any transaction in SQL? I want to execute on particular fix time.


texts:
I'm planning to import and update a huge table (3 Bil. rows) in the following step:

create table with fill factor x
import the data from csv files (6 files, ~21GB each)
add a BIGSERIAL column
create index for the BIGSERIAL column
cluster the table by the BIGSERIAL column?
update one column in the table in batches specified by a range of the BIGSERIAL column

It's a huge operation for me, so I really need your advice on the fill factor x I need to set in step 1, so that it'll have the best impact on the subsequent steps. All columns in the table definition are fixed size. Step 6 is supposed to be most CPU-intensive, since it involves spatial queries (postGIS).


texts: MongoDB disk operations with journaling disabled

texts:
Assuming MongoDB 3.6, usage of WiredTiger, with journaling disabled:

How does mongod write data to hard drive? It performs write operation per every document? Per every chunk? And similarly: how about reads? Also: are those reads and writes random or sequential?

Going further: are writes made during writing journal file similar to those when creating snapshot?

Further explanation of question: I'm trying to limit usage of hard drive  by Docker containers running MongoDB, so I started by benchmarking my disk following this guide: binarylane.com - How to benchmark disk I/O
. It states that

  Again, databases and many other programs will read very small chunks
  of data - 4 kilobytes is a good working estimate.

I'm wondering if this assumption is true for MongoDB.


texts:
We are a company doing surveys with different kind of questionnaires. There is a company in Mumbai (MySQL) specialized in Type 1 questionnaire and one in Peru (SQL-Server) specialized in Type 2. I also work with a CRM system as Salesforce and want to connect these data as well.

I thought about getting all these data into one in-house database using ETL. I was curious if it is possible to virtually connect these data warehouses so I do not need a DW in-house and can query the data where they are stored but can connect to them. Is this possible? I could not find anything but maybe I am looking with the wrong vocabulary.


texts:
Just got the below error in one of the slave. I just Googled it and changed the variable myisam_data_pointer_size from 4 to 6.

Somehow, my ALTER TABLE was successful.

Can you please let me know whether my approach was right. I am not sure on this variable?
Will this cause any performance issue. should I roll back it ?

130208  9:40:19 [ERROR] Slave: Error 'The table '#sql-19fc_3f8acc6' is full' on query. Default database: 'registry'. Query: 'alter table enom_log_2012_12_03 add responsedatatype char(1) not null after requestdata', Error_code: 1114
130208  9:40:19 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'pumpkin-bin.008662' position 411952695

Problem Description

Check for the Alerts in Errorlog for any errors and failures. Check if MySQL is up? if not verify if the process was Normal shutdown.

Advice

mysql> SHOW TABLE STATUS FROM registry LIKE 'enom_log_2012_12_03'\G
*************************** 1. row ***************************
           Name: enom_log_2012_12_03
         Engine: MyISAM
        Version: 9
     Row_format: Dynamic
           Rows: 1786679
 Avg_row_length: 2403
    Data_length: 4293600396
Max_data_length: 4294967295
   Index_length: 60999680
      Data_free: 0
 Auto_increment: 1786680
    Create_time: 2013-02-06 15:19:07
    Update_time: 2013-02-06 15:22:16
     Check_time: 2013-02-06 15:26:35
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> show global variables like '%tmp%';
+----------------+-----------------------+
| Variable_name  | Value                 |
+----------------+-----------------------+
| bdb_tmpdir     | /usr/local/mysql/tmp/ |
| max_tmp_tables | 32                    |
| tmp_table_size | 8388608               |
| tmpdir         | /usr/local/mysql/tmp  |
+----------------+-----------------------+

mysql> show global variables like '%myisam%';
+---------------------------------+---------------+
| Variable_name                   | Value         |
+---------------------------------+---------------+
| myisam_data_pointer_size        | 4             |--before changing it was 4 .. i changed it to 6
| myisam_max_extra_sort_file_size | 2147483648    |
| myisam_max_sort_file_size       | 2147483647    |
| myisam_recover_options          | OFF           |
| myisam_repair_threads           | 1             |
| myisam_sort_buffer_size         | 4194304       |
| myisam_stats_method             | nulls_unequal |
+---------------------------------+---------------


texts:
I want to assess how much data "churn" (insert, update, delete) a DB is undergoing, preferably per unit time.  For example, I'd like to know how many rows were inserted in the last hour.  Even better, I'd like to know the size of data those rows represented.  I'd like the same information for updates and deletes.

I assume an easy way to check this is by inspecting the transaction log periodically.  What is a simple way to do this?  Is there a DMV that is well suited for this?  Is there a tool I should use?


texts: Elements inside a container: How to perform atomic operations on them?

texts:
In my DB (Postgres 9.4) I have the following items: Tshirts, Jeans, Shoes and Boxes.

Tshirts, Jeans, Shoes can be placed inside a box and they can have a color (the color may be null).
Each element of the box MUST have the same color of the other.

To check (and also to ensure) the last condition I use a trigger.

/*
If I try to INSERT (OR UPDATE) an item in a box
containing elements of different color, it raises an exception.
*/
BEGIN
   IF  (
         SELECT color FROM tshirt WHERE tshirt.box_id = NEW.box_id
         UNION SELECT color FROM jeans WHERE jeans.box_id = NEW.box_id
         UNION SELECT color FROM shoes WHERE shoes.box_id = NEW.box_id
       ) <> NEW.color THEN

          RAISE EXCEPTION 'Error..';
          RETURN NULL;

    END IF;
END;

Now, let's suppose I want to change the color from 'Blue' to 'Red' for any item inside a Box (let's say box_id = 1):

UPDATE Tshirts SET color = 'Red' WHERE box_id = 1;
UPDATE Jeans SET color = 'Red' WHERE box_id = 1;
UPDATE Shoes SET color = 'Red' WHERE box_id = 1;

This code will fail because of the trigger.

The only way I can change the color of each item in the box is:

Remove all items from the box (setting their box_id to null).
Change the color of all the elements.
Put all the elements inside the box (setting back their box_id).

Is there any way (through modeling or triggers) to avoid this tricky situation? It would be great if I could change the elements' color atomically.

Thank in advance and sorry for my bad english.


texts: Postgres: my "data/backup" directory is way too large

texts:
In my postgres 9.3 data directory, there exists a subdir called "backup" that is 6.1G compared to my pg_xlog which is only 130M. I understand that pg_xlog is the WAL logs, so what is in the backup?

There are files there from a month ago so not sure why they're there, if I need them, and how I can prevent them from being created if not needed.

Any explanations?


texts: Column To generate Flag Values for use in bitwise comparisons

texts:
We are in the process of creating a new application where various tables are used to hold enumerations.  Since the IDs in these tables will be used in bitwise comparisons, we'd like to ensure new rows inserted will automatically use the decimal number that corresponds to the next bit being "set".

For instance, if we had an alarm clock application, we might include a "Weekdays" table which could be used in the following way:

CREATE TABLE dbo.Weekdays
(
    WeekdayID INT NOT NULL
        CONSTRAINT PK_Weekdays PRIMARY KEY CLUSTERED
    , WeekdayName VARCHAR(20)
);

INSERT INTO dbo.Weekdays
VALUES (1, 'Sunday')
    , (2, 'Monday')
    , (4, 'Tuesday')
    , (8, 'Wednesday')
    , (16, 'Thursday')
    , (32, 'Friday')
    , (64, 'Saturday');

CREATE TABLE dbo.AlarmsDefined
(
    AlarmID INT NOT NULL
        CONSTRAINT PK_AlarmsDefined PRIMARY KEY CLUSTERED
    , Weekdays INT NOT NULL
);

INSERT INTO dbo.AlarmsDefined (AlarmID, Weekdays)
VALUES (1, 50);

SELECT W.WeekdayName
FROM dbo.AlarmsDefined AD
    INNER JOIN dbo.Weekdays W ON (AD.Weekdays & W.WeekdayID) = W.WeekdayID

The results of the above SELECT query:

I'd like to be able to define the Weekdays table in the above example such that if we added another row to the table, the next WeekdayID would be automatically set as 128, which is the next decimal number that can be used in bitwise comparisons.

This of course can be accomplished by putting in the values by hand into the primary key column.

But is there a way to specify an automatically incrementing value which would adhere to the flag values? For example these would be the unique PK values generated:

ID
--
1
2
4
8
16
32
...

and the trigger would automatically look at the last value and generate the next in the sequence needed?


texts:
2015-04-07 13:49:53 23758 [ERROR] Slave I/O: error connecting to master 'replication'@master-host:3306' - retry-time: 60  retries: 1, Error_code: 2005

This was after attempting to do a change master to from previously running replication.  The only change was the master host.   The new guy I wanted to change master to is running from the same actual master.

This is using GTID so i was doing auto position=1 and not mucking with master_log_file master_log_position values.

I am able to connect to the new master using the replication credentials that show in mysql.slave_master_info via mysql client. Show grants confirms it has replication slave on .

I'm not seeing anything about denied connection attempts in the new master's error file, but I do see retry error attempts on the slave.

There's nothing more useful in show slave status\G

After getting frustrated I did a change master to back to the old master and it started running just fine.

I'm at a loss for how to continue trouble shooting this.


texts: Return list of columns in user-provided subquery

texts:
I am trying to fill a ComboBox of all columns from results of a query. That query is submitted by the user on the form, so the program needs to run the query then have a procedure run that extracts just the column names. I know the following works for a typical table:

SELECT
    c.name 'Column Name'
FROM
    sys.columns c
WHERE
    c.object_id = OBJECT_ID('Vendor')

What would be the best way to insert the user query (select * from vendor) as a subselect and get the same result?

Edit:
I could do(as Thomas Stringer noted):

for (int i=0; i < dataSet.Tables["Vendor"].Columns.Count; i++ )
{
    ColumnChoices.Items.Add(dataSet.Tables["Vendor"].Columns[i].ColumnName.ToString());
}

...though that isn't very efficient because it requires loading all of the data into the dataset when I really only want the columns.


texts: Get results from "combined, either inner join"

texts:
I have a feeling this must have been asked multiple times, but I just cannot find a solution, or even a questions probably because of difficulty in explaining.
We all know that inner join basically gives an "intersection" of two tables. And hence multiple inner joins give the intersection of all tables. What I -however- wish is to get an inner join that gets an intersection of table B with A or table C with A.
Like for a request that gets all organizations I'm either moderator or admininistrator.
SELECT *
FROM organization
INNER JOIN moderator_organization
  ON organization.id = moderator_organization.organization AND moderator_organization.user = 10
INNER JOIN admin_organization
  ON organization.id = admin_organization.organization AND admin_organization.user = 10

Above however would only select the organizations from which the user (with id 10) is both admin and organizer, not one where either is true. To visualize in a diagram I'd like:

Where the red region is what I'd like to request.


texts: How to get all the months from the last paid date in MySql?

texts:
I need to get the all the months from the last_payment_Date for the user from that table for processing purposing.

As of now i am using this query..

SELECT `Name`, `Acc_No` FROM `user_table_details` where
month(`last_payment_Date`)!=month(CURDATE())

Example : if a user Sam is paid on 12-05-2014, then i need all the months from, JUN, JULY, AUG, SEP, OCT to till month  - to display it for that user as "payment pending for the months". Is it possible through MySql Query?

By ACUBE.


texts:
I have two SQL Server databases. One is client (Windows application) and the second is on the server. I want to sync these two databases every so often (e.g. every 2 minutes!).

I have read about different ways of syncing like replication, time stamp, log tables using triggers, Microsoft Sync Framework and so on.

Actually I don't like to use a syncing method which might be a black box (like replication) because I don't want the SQL Server specific tables to be blocked while I'm updating them and syncing them with the server.

which method do you think that I should use in such circumstance? Remember that every several minutes I must send several table changes from client to the server and fetch also two table changes from server.
I have found a method which is strange but new. Is that possible that I log all executed (for specific preferred) stored procedures in client and send them with their parameters in a .sql file to the server and execute them there? The same will happen on the server and sent to the client. Do you think that this is a simple yet useful method or not?
please suggest me any useful approach if you can. Thank you so much.

EDIT: Remember that this is a real-time synchronization and this makes it special. It means when the client user is using the table, the synchronization process with server must happen every several minutes so none of the tables must be locked.


texts: Connecting to Oracle DB with LDAP-Credentials

texts:
So I would like to connect to a remote Oracle database from my Linux system using the oraclient 19c on the command line, but whenever I try to connect it sees the realmpart of my username as the server.

For example I want to connect with my username "USER@REALM.DOMAIN" to the database identified by "DB" in my tnsnames.ora, i do sqlplus USER@REALM.DOMAIN@DB and of course it complains with ORA-12154: TNS:could not resolve the connect identifier specified, because it thinks "REALM.DOMAIN" is the database.

When I try it without the realmpart like sqlplus USER@DB it returns ORA-01017: invalid username/password; logon denied.

How can I pass my username to the client?