texts: How to optimize this crazy SQL Query (Specific)

texts:
In short, I have this SQLite3 query that uses up 30% of CPU when being run.
SELECT s.*
    ,r.RaagEnglish
    ,(
        SELECT w.WriterEnglish
        FROM Writer w
        WHERE w.WriterID = (
                SELECT MAX(s1.WriterID)
                FROM Shabad s1
                WHERE s1.ShabadID = s.ShabadID
                )
        ) AS WriterEnglish
FROM Shabad s
    ,raag r
WHERE r.RaagID = s.RaagID
    AND s.ShabadID = $SHABADNO
ORDER BY ShabadID ASC

I have 3 tables in my Database: Writer, Raag, and Shabad.
A row in Shabad has a WriterID and a RaagID column.
This SQL Query has to get all the Rows in Shabad that match with the Column ShabadID, plus they have to match the WriterID column with WriterEnglish column from the Writer Table. And match RaagID column to RaagEnglish column from the Raag table.
This is a big query, and I am a SQL n00b, so I would appreciate if someone could help me optimize this query.
In the query above, Shabad is the Article, Raag is the Article Format. Max chooses the maximum value from all of the rows of a specific column.
Here is my DB (no private info, don't worry):
Raag

Writer

Shabad:


texts: How to sync two databases (alternative to mysql replication)?

texts:
Hello i have 2 mysql databases where most of the structure is constant except few tables (additions) in 2 dbs. I need to synchronize these 2 databases mainly common tables from one db to another. This is only 1 way.

I cannot use replication here because my databases are not same. I need to take the changes from db1 and then sync into db2 and this needs to happen in real time. Are there any tools or scripts available on linux which does this or do we need to write custom scripts? If custom scripts, any suggestions would be helpful.


texts: Conditional loop date lookup SQL Server 2008

texts:
If I have two tables, first one is WorkTime:

ID  EmpNAME     Hours/Day   Date0
1   Mark           7    2015-12-01
2   George         8    2015-12-01
3   Jennifer       6    2015-12-03
4   Mark           5    2015-12-08
5   Jennifer       6    2015-12-08

and the second one contains unique names is EmpName:

ID  EmpNAME
1   Mark
2   George
3   Jennifer

Excepted output will repeat all unique names table for each particular existing date with null values if the name did not exist in this date like below table

 EmpNAME    Hours/Day     Date0
    Mark         7        2015-12-01
    George       8        2015-12-01
    Jennifer    null    **2015-12-01**
    Mark        null    **2015-12-03**
    George      null    **2015-12-03**
    Jennifer     6        2015-12-03
    Mark         5        2015-12-08
    George      null    **2015-12-08**
    Jennifer     6        2015-12-08

How can I get that?


texts: Does sp_spaceused data contain more than the sum of each table data?

texts:
I wanna monitor the size of each table in my database. After doing so, I was wondering why the sum of the data of each table does not correnspond to the data size of the whole database I get after calling sp_spaceused.

Here are my results:
ExampleDatabase: 46 589 544 KB (data)
Sum(data) of each table: 18 564 680 KB (data)

The only objects I was ignoring while saving each tables size (data) were the system-tables and some tables, whose size is not worth talking about.

Because I can't see the error in my "catch table size procedure", I'm wondering if it's the sp_spaceused.data column, which maybe contains more than the sum of each table data?


texts:
I need to install SQL Server 2005 Express on Windows 8 Home Edition x64.

But I get a compatibility error!

I have tried SP1, SP2, SP3 of SQL Server 2005.

Visual Studio 2008 is working fine.

Any suggestions?


texts: extract values from different stored procedures and create one report

texts:
At the company I work, the third party taking care of the database. So there is  giant complex stored procedure that produces the result for one parameter only. When management needs a desirable report - they run one parameter, grabbing the sum of money for that parameter. Then running next parameter,  grabbing another sum for parameter and so on. After that they inserting all that sums into excel spreadsheet into a table.

So my job is to automate all this process.
Is any way the same stored procedure can run particular parameters - insert result in tempTable, then run next parameters - insert result in same tempTable and so on.
Do you guys know what I mean? The result of different sets of parameters should be like on a picture below

@GLCompanyGuid  varchar(8000)       =   'CpmpanyName_1',
@PostDateFrom datetime              =   '01-01-2016',
@PostDateTo   datetime              =   '01-31-2016',
@CompanyLocationGuids varchar(8000) =   'CompanyLocation_1'
@CarrierLocationGuids varchar(8000) =    Carrier_1,
@LineGuid     varchar(8000)         =   'Line_1'

--Now I need to run same store procedure but with different parameters value and insert the result at the same TempTable

@GLCompanyGuid  varchar(8000)       =   'CpmpanyName_2',
@PostDateFrom datetime              =   '01-01-2016',
@PostDateTo   datetime              =   '01-31-2016',
@CompanyLocationGuids varchar(8000) =   'CompanyLocation_2'
@CarrierLocationGuids varchar(8000) =    Carrier_2,
@LineGuid     varchar(8000)         =   'Line_2'

--So how can I make one stored procedure run through one set of parameters, insert values in temp table, then run through another set of parameters - insert values in temp table and so on?[![enter image description here][1]][1]


texts: Is there a way to determine if a postgres function depends on a user-defined type?

texts:
I am writing a Ruby script to handle functions and user-defined types in Postgres. I want to make sure, that before a function is created in the database, if it uses a user-defined type, than this type exists in the db (so that if it doesn't, it gets created). Is there a way to check this?

Clarification:

I have my function definitions in separate files in the form:

CREATE OR REPLACE FUNCTION foo(....)
...
END; $$;

and my type definitions in the form:

CREATE TYPE bar AS (
    ...
);

So currently, the only thing I came up with is this: given the knowledge of the name of a type (bar), scan the body of the function foo with a regex. If bar appears somewhere in the function, then check that bar exists and create it if it doesn't. However, I have around 50 functions and around 10 types, so if I go and create all the functions, it will take quite some time in scanning.


texts: How to restore and overwrite an existing database on Azure SQL MI

texts:
We are trying to restore a backup file in Azure SQL MI using SSMS. the issue is we are trying to overwrite an existing database and we notice this feature is not present in SSMS for Azure SQL MI but available for SQL Server.
Is it possible to overwrite the database using SQL scripts.


texts: Set field values to newly imported rows in PostgreSQL table with existing data

texts:
I have a PostgreSQL table with existing data, and needs to import all the rows in a CSV file into that table. I am using pgadmin3's Import tool to do the import.

Question: For the newly imported rows, how do I set the values for a specific column (not found in the CSV)? I cant do UPDATE staging.tablename SET colname = 'somename' the way I could when importing into an empty table. Or is this approach wrong?


texts: Displaying the order of the columns of a table

texts:
I created a table, and want to find the display the order of its columns.
Should I use the following query to display the info ordered by column_id?

select * from sys.columns c
where c.object_id = object_id('Customer')
order by column_id

create table dbo.Customer
(
    CustomerId int primary key,
    CustomerName varchar(255),
    CustomerAddress varchar(255),
    EnrollmentDate date
)

Reading Microsoft SQL Server documentation, I am seeing the information below, so want to be sure:

Column name  Data type  Description
-----------  ---------  ----------------------------------------------
column_id:   int        ID of the column. Is unique within the object.
                        Column IDs might not be sequential.


texts: Is it possible to get a list of procedures created with WITH EXECUTE?

texts:
Is it possible to get a list of stored procedures create with WITH EXECUTE?

I tried looking at sys.procedures, but they all have the principal set to null, and I know there are some procedures created this way.


texts: Moving data between Oracle and PostgreSQL

texts:
For a future project I need to use the following setup:

Some small (offline) data boxes (collecting data over the time) fill PostgreSQL databases (about 1 TB a year in total).

From time to time I would have the chance to move data out of those boxes (onto a stick or mobile drive) and import that into a huge corporate Oracle database.

So far, this is hopefully not a really bad solution. At the moment I'm looking for things to be prepared for easy use later.

Has anyone ever done things like that?

Experiences, what to do and what to prevent?
Any other compatibility?
Things to do in advance?
How to move the data? Special dump-settings?


texts: What could cause the wrong ID to be inserted?

texts:
I have a SQL Server 2008 server (build 10.0.5500).  Earlier this week I ran this on a table that already had data in it:

delete from dbo.table
go
dbcc checkident('dbo.table',reseed,0)

When the user went to create a new record later on, somehow an ID of 0 was inserted into the ID column, instead of the 1 SQL Server usually puts in if identity(1,1) is configured for the ID.

This caused some weird issues, but clearing the data and running the reseed resulted in a 1 being inserted, as expected.  I can't duplicate the issue.

For reference, here's the general format for our save sp's:

alter procedure dbo._TableSave
    @pk_id int,
    @field varchar(50)
as
    if (@pk_id is null)
    begin
        set nocount on;

        insert into dbo.Table
        (
            Field
        )
        values
        (
            @field
        );
        select scope_identity();
    end
    else
    begin
        update dbo.Table
        set Field=@field
        where PK_ID=@pk_id

        select @pk_id
    end

Does anyone know what could cause SQL Server to insert a 0 in the ID when it should have been a 1?

We are not inserting data into identity columns (with identity_insert on) anywhere in the application.


texts: Parsing the value and copying it to another column in postgresql

texts:
column_1 (varchar) has values with the format like 1024 MB, 2048 MB etc. There also exits column_2 (int). Now I would like to parse the value in column_1 and update the column_2 with values like 1024, 2048. I am using PostgreSQL.
How do I do this ? I am fairly new to databases. Any help/suggestion is appreciated.


texts: Partition Function Question - DatetimeOffset


texts: MySQL: Performance issues in heavy multi-threaded environment

texts:
I have 23 of these tables in one database:

CREATE TABLE foo1 (
  DateTime datetime NOT NULL,
  BidOpen decimal(11, 5) NOT NULL,
  BidHigh decimal(11, 5) NOT NULL,
  BidLow decimal(11, 5) NOT NULL,
  BidClose decimal(11, 5) NOT NULL,
  AskOpen decimal(11, 5) NOT NULL,
  AskHigh decimal(11, 5) NOT NULL,
  AskLow decimal(11, 5) NOT NULL,
  AskClose decimal(11, 5) NOT NULL,
  Volume decimal(8, 2) NOT NULL
) ENGINE = InnoDB;

ALTER TABLE foo1 ADD UNIQUE INDEX AskHigh (DateTime, AskHigh);
ALTER TABLE foo1 ADD UNIQUE INDEX AskLow (DateTime, AskLow);
ALTER TABLE foo1 ADD UNIQUE INDEX BidHigh (DateTime, BidHigh);
ALTER TABLE foo1 ADD UNIQUE INDEX BidLow (DateTime, BidLow);
ALTER TABLE foo1 ADD UNIQUE INDEX DateTime (DateTime);

When running this query in ~70 threads
SELECT MIN(DateTime) FROM fooXY WHERE DateTime>'2018-08-20 09:53:00' AND AskHigh>1.7444;
almost each needs ~2 seconds for ~225k rows.

The issues is only when these queries are ran multi-threaded.
Single execution is very fast and as expected.

Environment:

Server has 4GB RAM
MySQL 8.0.15
8 cores
key_buffer_size == 268435456
innodb_thread_concurrency = 64
innodb_buffer_pool_size = 3133495296 [e.q. 75% of available RAM]
MySQL is using all of the available cores.

My question:

How can be this query optimized/rewritten so there is no performance issue?

Update:
As requested, the result of EXPLAIN:

id              1
select_type     SIMPLE
table           foo1
partitions      (null)
type            range
possible_keys   PRIMARY
key             PRIMARY
key_len         5
ref             (null)
rows            113400
filtered        33.33
Extra           Using where

The result of the suggestions in the chat or the answer:

Suggestions by user ypercube:

I changed the DateTime index to PRIMARY KEY.
Changing MIN() to ORDER BY DateTime LIMIT 1 fixed the performance issues.. The job needs now 50% of the time.

Suggestions by user Rick James:

The other indexes - apart from PRIMARY KEY are indeed not needed. The deletion of them also shrunk the database by almost 50%. This alone didn't fix the issue.
Changing the pool size to 50% of the available RAM. I don't see any change in comparison to 75% so I leave the 50% in order to avoid/minimize any possible swapping.


texts: How can I pass object to procedure from trigger?

texts:
I am using mysql 5.6.
After insert or update, inside the trigger I am calling procedure, So how can i  pass NEW and OLD object from trigger to procedure ?


texts: Handling concurrent updates, inserting if and only if no record already exists

texts:
I'm writing a Java application to update a relational database (currently H2, but we'll likely switch to PostgreSQL at some point). I have a process which does the following:

Poll an external system for a list of data items.
For each item, check whether or not it has already been imported into our system by querying an IMPORT_LOG table. If not:
Import the new data item, inserting data into various tables.
Insert a new record into the IMPORT_LOG table.

This process might be run concurrently, either in different threads or scaled out over multiple machines.

I'd like to avoid the concurrency hazard where two threads might both start to import the same data item, check IMPORT_LOG, find nothing there, and then both insert the data items.

What might be a good approach? I've been considering:

Using SERIALIZABLE transaction isolation.
Relying on a UNIQUE constraint in IMPORT_LOG to error and rollback one of the transactions.

Are either of those workable? Is there something else that might work better?


texts:
I have a file (mysql error file) full of this message

  [ERROR] Got error 134 when reading table './xxxx/wp_options'

I don't really know how to fix this problem


texts: Why are there any Innodb_buffer_pool_reads for a small db?

texts:
I have a small DB, < 400MB, using MariaDB 10.4 on a machine with 16GB ram. I've set the innodb_buffer_pool_size to 2G while running some query performance tests.

With the size set to 2G, more than 4x the total size of the DB, why would I get any Innodb_buffer_pool_reads? Why wouldn't all reads be found in the buffer pool?

After a test run I get
  Innodb_buffer_pool_read_requests = 280,980,648
  Innodb_buffer_pool_reads = 27,089

I don't mind the hit rate; I'm just wondering if I misunderstand something about the buffer pool.


texts: Does MySQL use its index metadata to optimize COUNT(*) queries?

texts:
Let's consider a user_actions table with a standard indexed primary key user_action_id.
I want to know the number of user actions up to a certain key (say 100 millions):
SELECT COUNT(*) AS `user_actions_count`
FROM `user_actions`
WHERE `user_action_id` < 100000000

In my understanding, the index b-tree has to keep the sub-node counts at each tree node for balancing purpose. Would that information be used to perform such an obvious query or would the engine have to scan the whole index to actually count rows?
As a matter of fact, I encounter unexpected performance issues on that query.
Thanks for your help.
EDIT:
Here is the EXPLAINation of the query:
id  select_type table        type  possible_keys key     key_len ref  rows      Extra
1   SIMPLE      user_actions range PRIMARY       PRIMARY 4       NULL 268537554 Using where; Using index


texts: Is it possible to query uncommitted data of a different session?

texts:
For interactive debugging purposes I'm wondering whether it's possible to query the data another session has currently put into the database but not yet committed.

Say I have an my application open in a debugger and when I'm stopped in a certain breakpoint I would like to check interactively what the session of the app "is seeing" via simple queries from SQLDeveloper (or sqlplus).

Is this possible with Oracle XE? (As it is for debugging, there's no DBA or security constraints.)


texts: Availability Group Listener and old client software

texts:
We've got a multisegment AG Listener named SQLAGL01 which points to these two addresses:
10.143.162.32 (the primary server)
10.140.165.32 (DR server)
Because the 3rd party (which means we can't fix it, and no we can't migrate to something else) application software uses old drivers which don't support MultiSubnetFailover=True, we're seeing issues where reports fail half the time because SQLAGL01 resolves to the DR address 10.140.165.32.
Following the Microsoft docs, we set RegisterAllProvidersIP=0 and restarted the cluster.  However, SQLAGL01 still resolves to the DR address half the time.
In Failover Cluster Manager, the AG Listener's owner node is the primary server, and the DR address is offline.  (Is that normal?)
Here's a screenshot of the cluster role:

EDIT: Added RegisterAllProvidersIP value.
PS C:\windows\system32> get-clusterresource "Cluster Name" | Get-ClusterParameter RegisterAllProvidersIP

Object       Name                   Value Type
------       ----                   ----- ----
Cluster Name RegisterAllProvidersIP 0     UInt32

EDIT 2: Added AG cluster resource values.  There doesn't seem to be a RegisterAllProvidersIP resource for the AG Listener.

EDIT 3: Added the actual Listener resource value.
PS C:\windows\system32> get-clusterresource KYHIXSQLAGL01_KYHIXSQLAGL01 | Get-ClusterParameter RegisterAllProvidersIP

Object                      Name                   Value Type
------                      ----                   ----- ----
KYHIXSQLAGL01_KYHIXSQLAGL01 RegisterAllProvidersIP 1     UInt32


texts: Determine total no of logical/physical reads

texts:
I am working on the optimization of a SP which contains some business logic using looping. I have removed the looping and converted those piece of code into some simple insert/update statements.

Now I've to do benchmarking and compare old and new code in terms of execution time and logical/physical reads.

My problem is because of the loop in my old code, how can I determine what is the total no of logical/physical reads. Because in SSMS, I can see thousands of IO stats statements like:

"Table 'Employee'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 43, lob physical reads 0, lob read-ahead reads 0."


texts: Will Rebuild and Reorganize improve Database Migration Script performance execution?

texts:
I would like to know if a Database Schema Migration Script (generated by Visual Studio 2008 or greater) will run faster if my target database got indexes rebuilt and/or reorganized.

Note: For all tables, when index fragmentation level is between 5% and 30% I reorganize the index. When index index fragmentation level is above 30% I rebuild the index.

Note 2: Database schema migration script generated by Visual Studio has a lot of commands to alter table structure, add constraints, foreing keys and indexes in order to keep target database structure updated/syncronized with files contained in our Database project (.dbproj file).


texts: How to allow an SQL-ID to access one database through a view in another?

texts:
I have 2 databases in MS-SQL Server 2012. The first database, ‘DatabaseA’, is accessed by multiple databases including my second database ‘DatabaseB’. I have a view called ‘vwDB_B’ in ‘DatabaseB’ that joins/filters some information from both ‘DatabaseA’ and ‘DatabaseB’ (The view is set to run under an SQL-id that has access to both ‘DatabaseA’ and ‘DatabaseB’. Both DBs are on the same server. DatabaseB accesses objects in DatabaseA through their fully qualified names.

Now, we have an SQL-id that will be used by external users called ‘ExternalID’. This SQL-id should be able to retrieve data from ‘vwDB_B’. How can I achieve it?

If I give SELECT access to the ‘ExternalID’ on the ‘vwDB_B’ view, I get an error message that the ID doesn’t have the access to ‘DatabaseA’. I don’t want to grant read-only access to ‘ExternalID’ (or add it to a read-only role) on ‘DatabaseA’ because this DB has some sensitive information and I only want this id to access the information provided by my ‘vwDB_B’ view.


texts: Same SSRS report, two different subscription names

texts: I would like to know how to change same SSRS reports from one subscription name to two different “from” names?


texts: In READ COMMITTED isolation, is it possible to read the old value after the update?

texts:
I'm trying to solve an issue with SQL Server 2008 R2 and am grasping at straws.

Given that data is housed in leaf-nodes of the B-tree, and is also exists in indexes, that someone could read multiple values during a single UPDATE statement?

Imagine one process updates a row:

--Change status from Pending -> Waiting
BEGIN TRANSACTION
   UPDATE Transactions SET Status = 'Waiting'
   WHERE TransactionID = 12345
COMMIT TRANSACTION

Is it possible that during this BEGIN TRANS; UPDATE; COMMIT another process could read both new and old values?

I wonder this because the update does not alter a value in only one place. That value will exist in multiple places:

leaf nodes of clustered index
IX_Tranasctions_1
IX_Tranasctions_2
IX_Tranasctions_3
IX_Tranasctions_4
IX_Tranasctions_5
...
IX_Tranasctions_n

If the update begins, it has to update values in all these locations. What happens if another process uses an index execution plan to find the value:

IX_Clustered: Pending Waiting
IX_Transactions_1: Pending Waiting
IX_Transactions_2: Pending Waiting
IX_Transactions_3: Pending
IX_Transactions_4: Pending
IX_Transactions_5: Pending
...
IX_Transactions_n: Pending

At that instant, if a query is issued that looks at the value in the clustered index it will find Waiting.

But if a query uses IX_Transactions_4 it will find a value of Pending.

Undocumenting the locking mechanism

The lock order internally is undocumented, but i assume SQL Server takes a shared lock on the clustered and non-clustered indexes:

IX_Clustered: Shared
IX_Transactions_1: Shared
IX_Transactions_2: Shared
IX_Transactions_3: Shared
IX_Transactions_4: Shared
IX_Transactions_5: Shared

Then upgrades those locks to Update locks:

IX_Clustered: Shared Update
IX_Transactions_1: Shared Update
IX_Transactions_2: Shared Update
IX_Transactions_3: Shared Update
IX_Transactions_4: Shared Update
IX_Transactions_5: Shared Update

at this point, another process can still read the values in those 5 indexes; because selects are compatible with Update locks.

Then the update proceeds. If we take a picture at an instant in time:

IX_Clustered: Shared Update Exclusive -> value changed
IX_Transactions_1: Shared Update Exclusive -> value changed
IX_Transactions_2: Shared Update Exclusive
IX_Transactions_3: Shared Update
IX_Transactions_4: Shared Update
IX_Transactions_5: Shared Update

Another process can no longer read values from items with an Exclusive lock, but it can still read values from items that still have the Update lock (Shared locks are compatible with Update locks)

Unless of course that's not what happens

That only works if SQL only promotes to exclusive as needed. If, instead it goes:

IX_Clustered: S U IX X -> value changed
IX_Transactions_1: S U IX X -> value changed
IX_Transactions_2: S U IX X
IX_Transactions_3: S U IX
IX_Transactions_4: S U IX
IX_Transactions_5: S U IX

And i can't type anymore; my will to continue is crushed. Like i said, i'm grasping at straws.


texts:
I create a database named Anandschool and tables inside named teacher inside database.
when i see in right hand side in database then it shows only system table but when i apply command ""select * from teacher"" then it showing.
How can i see tables name in right side of database.


texts: Need only single table from ibdata1 file

texts:
I currently have a corrupt database on my live site, because a single Table that has gone missing.

Table 'test1.catalog_product_index_price' doesn't exist

I logged onto my server and downloaded a compressed backup file "/backup/2018-02-03/system/dirs/_var_lib_mysql_.tar.gz"

Once i extracted it locally I noticed ibdata1 file, and this would be my first time trying to work with this type of file.

I only need to recover a single table from the "test1" database. How can I go about exporting this table using the ibdata1 file?

Thanks


texts:
After canceling an Ubuntu upgrade, Mysql refuses to start.

this is what I get when I try to run it :

$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

When I try starting it using service command I get this :

$ service mysql start
start: Rejected send message, 1 matched rules; type="method_call", sender=":1.74" (uid=1000 pid=16096 comm="start mysql ") interface="com.ubuntu.Upstart0_6.Job" member="Start" error name="(unset)" requested_reply="0" destination="com.ubuntu.Upstart" (uid=0 pid=1 comm="/sbin/init")

I tried also to uninstall it but I get this error :

$ sudo apt-get remove mysql
E: dpkg a été interrompu. Il est nécessaire d'utiliser « sudo dpkg --configure -a » pour corriger le problème.

The error is in french, but those familiar with Ubuntu will get it I guess.

After that I do what they suggested :

$ sudo dpkg --configure -a
Paramétrage de mysql-server-5.5 (5.5.31-0ubuntu0.12.04.2) ...

And then it endlessly configures.. until I get this error :

start: Job failed to start
invoke-rc.d: initscript mysql, action "start" failed.
dpkg : erreur de traitement de mysql-server-5.5 (--configure) :
le sous-processus script post-installation installé a retourné une erreur de sortie d'état 1
Des erreurs ont été rencontrées pendant l'exécution :
mysql-server-5.5

almost the same thing when I try an autoremove :

sudo apt-get autoremove
Lecture des listes de paquets... Fait
Construction de l'arbre des dépendances
Lecture des informations d'état... Fait
Les paquets suivants seront ENLEVÉS :
  libdevil1c2 libgearman6 libgts-0.7-5 libgts-bin liblasi0 libtokyocabinet8
0 mis à jour, 0 nouvellement installés, 6 à enlever et 6 non mis à jour.
1 partiellement installés ou enlevés.
Après cette opération, 3 232 ko d'espace disque seront libérés.
Souhaitez-vous continuer [O/n] ? O
(Lecture de la base de données... 536300 fichiers et répertoires déjà     installés.)
Suppression de libdevil1c2 ...
Suppression de libgearman6 ...
Suppression de libgts-bin ...
Suppression de libgts-0.7-5 ...
Suppression de liblasi0 ...
Suppression de libtokyocabinet8 ...
Traitement des actions différées (« triggers ») pour « libc-bin »...
ldconfig deferred processing now taking place
Traitement des actions différées (« triggers ») pour « man-db »...
Paramétrage de mysql-server-5.5 (5.5.31-0ubuntu0.12.04.2) ...
start: Job failed to start
invoke-rc.d: initscript mysql, action "start" failed.
dpkg : erreur de traitement de mysql-server-5.5 (--configure) :
 le sous-processus script post-installation installé a retourné une erreur de sortie d'état 1
Des erreurs ont été rencontrées pendant l'exécution :
 mysql-server-5.5
E: Sub-process /usr/bin/dpkg returned an error code (1)

My Mysql version is :

$ mysql -V
mysql  Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.2

Any hints about this ?


texts:
Update: I updated the question to be more specific and also updated the diagram.

I am currently in my last semester, I did use SQL before then I left for MongoDB but the practice project requires to use some design patterns and only SQL. After understanding the concepts for normalization and relationships again, I designed the database and its tables before I start the code for the application.

Primary Goal:
Develop a Web Application for online restaurant table booking and food delivery.

DB Requirements:

DB must apply RDMS concepts
DB must have appropriate normalization or denormalization
ERD must have propper annotations

What I built:

What I tried to use during design:

1NF
2NF
3NF
Relationships

Conclusion:
So far I am not sure if I have made any mistake, I wanted to ask for a review from experts if these tables are indeed in 3NF. I am also thinking to understand more about BCNF, 4NF, 5NF and 6NF and use them if possible.

Any help is appreciated.

Thanks

EDIT 1:

Business Requirements: "Removed because it was unnecessary"

P.s. PartyID = CustomerID or RestaurantID


texts: How can I make a host show up by hostname in 'show processlist' rather than by IP address?

texts: I thought this might be something controlled via RDNS, but I updated that and am still getting connections for a couple machines showing in 'show processlist' as the IP address (and port) rather than hostname and port. I'm wondering how/where this is controlled.  2 out of about 20 machines are showing by their IP address.


texts: Can we directly upgrade Oracle 12.1 to 19 c or do we need to upgrade 12.1 to 12.2 first and then 19c, and when 19c will be available for download


texts: Setting up a Grand Jury relational database structure

texts:
I'm designing a database to store information about a grand jury. I'm wondering if my tables are normalized, and if they structure makes sense.

A report is sent in for review by grand jury.
Then a pdf file of their investigation is uploaded once they finish.
The database must keep track of: Who is the roster for the year of
the investigation, all cases for each year, and all files connected
to each case.
Files can be the reports themselves, attachments, final reports, and
responses.

The Grand Jury serves as a group. There are 19 individuals per year that serve on the grand jury each year, and can not serve again for another 1 or 2 years after serving. Reports are handled as a whole by all of the grand jurors. A grand juror can be "Excused" and replaced if necessary.


texts: Organize repeating timestamped values in schema

texts:
I am creating models to hold some financial data. The information frequently is split into timestamped values, e.g:

Some value, now  |  as of Jan 1st  |  as of Jun 1st  | as of Sep 1st

There are four timeframes per value; for each of them values will differ, but conceptually mean the same thing. I have around 30 of different 'timeframed' values to store. Timeframes in each case are usually the same, but technically can change. There is also some not timestamped stuff to store in addition to the above.

How to model this into db schema in order to keep it clean, elegant and efficient? Right now it's hard to anticipate the way this data will be used and what type of queries will be most frequent.

I'm considering flat schema to make querying as easy as possible (no joins), so the columns would be:

id  |  value_now  | value_now_date  | value_previous  |  ...

... but the model gets huge and I'm tempted to add separate table instead, having columns:

id  |  FK to main model  |  value_name  | date_frame

Are there significant benefits of such approach? Or the previous one? Are there other options I should consider?


texts: Change existing column in PG to auto-incremental primary key

texts:
I have a database in Postgresql, which was migrated from SQL Server (only data).
On SQL Server, a table from this database has these columns:

measure_id
datum
measure

where measure_id is auto-incremental primary key, datum is datetime and measure is float.
After migration in Postrgresql, measure_id is column of type bigint.

How can I change this column (measure_id) to bigserial and assign it as primary key, now that my table is full of data?


texts: Postgres: using existing index to support GROUP BY query on custom function

texts:
I'm trying to optimize a query that looks like this:

SELECT f(a), sum(b) FROM table GROUP BY f(a) ORDER BY f(a) LIMIT 10

I have a b-tree index on column a, and f is a custom function where I know that if x > y, then f(x) >= f(y).

It seems like in a perfect world one ought to be able to execute this query using the index on column a to avoid reading more rows than necessary to calculate the first 10 groups, since an index scan on a will return all the rows in the first 10 groups before it starts returning rows not in the first 10 groups.

I know I could optimize this by creating an index on f(a), but I have multiple function fs so don't want to pay the overhead of doing that for each one.

Is there a way to re-write this query (or my function f, for that matter) so that it could use my existing index on column a to avoid reading the whole table?


texts:
I have a table with a lot of records (more than 15M) and i am executing an alter query to add 2 columns to the table.
the query is taking around 1hr to finish executing.
So my question is: How does an alter work? and why is it affected by the number of records?
is it possible to speed up an alter query using column indexes?


texts: Performance benefits of multiple .ndf files

texts: Is there a benefit to having multiple mdfs with SSDs? I assume that with multiple cores there is, since there is less contention?


texts: AWS Postgres RDS "storage-full" and not responding. How to bring back online?

texts:
I cannot reboot nor stop the DB in AWS console. I cannot connect it with PG Admin. My Java app also print error like this.
org.postgresql.util.PSQLException:
Connection to xxxx.us-east-1.rds.amazonaws.com: 5432 refused.
Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

And And I just realize the storage has been decreasing all the way to zero after the DB is down. I find it really astonishing since the amount of QPS is sparse and low. How can the storage decrease so steadily?


texts: What is the fastest way to reset SQL Server database if you can not use RESTORE?

texts:
I have integration tests that require test database to function.
Since tests should generally be independent, I reset the database in the beginning of each
test.

I can not use RESTORE, since some parts of architecture (which I do not control) are caching the connections, and will fail with connection lost on next call.

Right now I am creating a snapshot, then calling DELETE + INSERT on each table to synchronize data with snapshot. However, it takes 1 second per reset which is way too much (150 tests = 150 seconds). I have a lot of tables, but they are almost empty so there is no reason for it to be so slow.

So how can I replace database with its previous version in less than 1 second without losing connections?

My next idea would be to add some kind of change tracking since each test only affects some tables, but it would make reset code even more complicated.

UPDATE: I added SET STATISTICS TIME ON and I am getting

SQL Server parse and compile time:
   CPU time = 327 ms, elapsed time = 343 ms.

for my reset SP. I assume it is due to ALTER TABLE ... NOCHECK CONSTRAINT ALL calls in the beginning of the SP. I wonder if it is possible to suppress recompilation in this case.


texts: Huge disk space usage increase when altering a column

texts:
I hope this question is easy for someone to answer :)

I have a table that looks like this (unimportant columns hidden)

CREATE TABLE [dbo].[Log](
    [LogID] [int] IDENTITY(1,1) NOT NULL,
    --several other columns
    [Name] [nvarchar](512) NOT NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
    [LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Now I did some indexing on that table and that also involved shortening the Name-column to 256 width.
But when I change this column

ALTER TABLE Log ALTER COLUMN Name NVARCHAR(256) NOT NULL
GO

The database grows by a non trivial amount. (And yes I double checked - this is the only change I make here)
In total that table has 90746 entries and before altering the table SSMS said the size was 247.56MB.

But after this update the database grew to 336.13MB.

Don't know if that matters here but SELECT @@VERSION gets Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
    Mar 29 2009 10:11:52
    Copyright (c) 1988-2008 Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

How can this be? Can someone explain?

UPDATE: I tried DBCC SHRINKDATABASE (myyDB, 0); that led to a smaller database of 268.81MB, but that is still bigger than the original database size with a wider column, I still do not understand :)


texts: Tracking (un)successful autovacuums in postgres

texts:
I am trying to help a team of junior, senior, principal and chief (mostly JEE) developers to be more data-centric and data-aware. In some cases we look into the data-processing costs, complexity of the algorithms, predictability of the results and statistical robustness of the estimates for query plans. In other cases we blindly believe that use of indexes is always great and scan of the tables is always bad. Sometimes we just opportunistically throw gazillions of insert, update, delete queries into the DB and hope for the best. We run load tests afterwards and we notice that our tables and indexes are bloated beyond imagination, the tables became pretty much unmanageable in size and chaos rules the area.
A good way to proceed is to train and learn complexity classes, understand the costs, have the right attitude. This change is very fruitful but hard and slow. As long as I am breathing, I'll continue this journey.
For now we are trying to understand why autovacuum for some tables kicks in so seldomly. We've got a postgres server (v9.5 I believe) running in azure cloud (test environment). We pay for 10K IOPS, and we use them fully (we write to the DB like hell). In the last 24 hours I see that autovacuum was run only 2 times for two large tables through
select * from pg_stat_all_tables order by last_autovacuum desc

In order to trigger an autovacuum, I created:
create table a(a int)

ALTER TABLE a SET (autovacuum_vacuum_scale_factor  = 0.0 );
ALTER TABLE a SET (autovacuum_vacuum_threshold     = 10  );
ALTER TABLE a SET (autovacuum_analyze_scale_factor = 0.0 );
ALTER TABLE a SET (autovacuum_analyz_threshold     = 10  );

and ran the following two statements multiple times:
delete from a;
insert into a (a) select generate_series(1,10);

This should have triggered an autovacuum on the table, but pg_stat_all_tables has NULL for last_autovacuum column for table a.
We also set log_autovacuum_min_duration to a very low value (like 250ms or even 0), but the only two entries in the logs are:
postgresql-2021-02-18_010000.log:2021-02-18 01:56:29 UTC-602a2e9c.284-LOG:  automatic vacuum of table "asc_rs.pg_toast.pg_toast_3760410": index scans: 1
postgresql-2021-02-18_060000.log:2021-02-18 06:35:47 UTC-602a2e9c.284-LOG:  automatic vacuum of table "asc_rs.pg_toast.pg_toast_3112937": index scans: 1

Our settings are:

We have a feeling that autovacuum is killed on large tables because of row-locks. Can we log this information in any way? Can we also log (failing) autovacuum attempts? How does postgres decide to start  an autovacuum job  (or more generally speaking tradeoffs between regular changes in the DB vs. maintenance jobs) on a very high load system? If parameters for kicking off of autovacuum are meat, would it definitely be kicked off or wait until the load of IOs decrease?
EDIT:
We do not see any errors/breaks w.r.t. the autovacuums in the logs (thanks Laurenz Albe)
DETAIL:  while scanning block 5756 of relation "laurenz.vacme"


texts:
I have to following problem: since my database runs out of memory in a few days, I am looking for a way how to make the database use more than one file. I would like to store upcoming data in another file, which is located on a different hard drive.

I am using SQL Server 2008. Under "database properties" I already found out I can create more logical files. I created one in the filegroup PRIMARY. Anyway, SQL Server doesn't use the new file. What can be the reason for this?

Do I have to limit the file size of my "primary" file to make it use the new file? I don't want to wait until my disc runs out of memory, hopefully somebody has some way to solve this!

Thank you very much in advance!

tommy


texts: MySQL Replication ERROR 1130: error conecting to master

texts:
I'm having difficulty connecting MySQL slave database to master using MyWorkBench.

I tried:

executing

CREATE USER 'replication'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON . TO 'replication'@localhost' WITH GRANT OPTION;

using IP address instead of the server name

I confirmed that:

I followed all the steps for setting server_id for slave and master
I could ping both master and slave servers

Can anyone help me with this, please?


texts: Is good idea to implement ProxySQL in production with Docker?

texts: The idea is to have solution with ProxySQL to give high availability solution with docker implementation? But this question maybe not have sense, I am to investigate alternative solution without VIP implementation.


texts: mysql showing two select statements as one output

texts:
Hobby databasist here.

In my database, the statement below will output the number of posts per hour which discussed subject foo.

select
    count(postid)
from
    myTable
where
    (sub like ("%foo%") or comment like ("%foo%"))
    and replies != 'NULL'
group by
    time div 3600
order by time div 3600 asc;

Output: [time div 3600, foo count]

this statement below would output the number of posts per hour which discussed subject bar.

select
    count(postid)
from
    myTable
where
    (sub like ("%bar%") or comment like ("%bar%"))
    and replies != 'NULL'
group
    by time div 3600
order by
    time div 3600 asc;

Output: [time div 3600, bar count]

How to I combine the output such that:

Output: [Time div 3600, foo count, bar count]


texts: Mysql encrypted database contents by proxy?

texts:
Forive the amateur nature of this question. I work with Wordpress, often transforming the vanilla flavour into custom made versions for clients.

Increasingly clients require more and more marketing information, which often includes entering traffic source conversions, contact data, measuring performance by users etc. Also the amount of lead generation that is happening on these sites increases too.

I looked for some solutions, including encrypting informations but this would always mean i have to hack each and every plugin, and of course the wordpress core which is something i want to avoid and there seems to be the same problem, that decryption files would still be on the server giving a hacker the means to decrypt. (for easy solutions)

So my question:

Is there some better server based utility where i can simply specify the database path, username and password to some utility that encrypts and decrypts information from another database as data is passed to it?

This would be a tidy way of encryping this information. I could specify a database elsewhere with much harder security and specify some sort of proxy database setting in the wp file.

Any ideas? What i am looking for is a super clean and easy way for very very basic server admins to increase the encyption of data inside wordpress in a universal method, ie works for all datas, no need to modify any wordpress plugins or core etc.


texts: Login failed for User 18456 State 5. User id works on other machines

texts:
I have a really weird login issue I've been pursuing. I experience this only on my Macbook Pro (2018) and only when logging into this one specific SQL Server 2016 database directly through a database client.

Login attempts through any database client to this specific database are met with:

  Login failed for user ''. (Msg 18456, Level 14).

Failure state is 5, "invalid user id".

These credentials work fine when used as credentials for a local copy of the java application that this DB backs.

These credentials work fine when used to login from another machine.

User ID/password does not work only when used to log in through a database client, and only on my machine. Tested with DataGrip and SQLPro for SQL Server. Both applications can log into other SQL Server databases without issues.

I generated a new user account to no avail. The issue persists. I can connect to other database servers through 1433, and my application can connect to this database when running locally with the same credentials

I am connected through a VPN, but I don't think that has anything to do with it.

I would suspect that it's some kind of encoding issue with the request, but the problem persists across multiple applications.

Anybody have any ideas?


texts: SQL Server - error running 'sp_addsubscription' when not sysadmin

texts:
Windows 2008 R2
MSSQLSERVER 2012 (SP1) Standard

When running sp_addsubscription under account within db_owner role of both user and distribution databases, I get the following error:

Msg 14260, Level 16, State 1, Procedure sp_MSadd_subscriber_info, Line 44
You do not have sufficient permission to run this command. Contact your system administrator.
Msg 14042, Level 16, State 1, Procedure sp_MSrepl_addsubscriber, Line 342
Could not create Subscriber.

...despite books online (https://msdn.microsoft.com/en-us/library/ms181702.aspx) stating that “…db_owner fixed database role can execute sp_addsubscription”.

sp_addsubscription appears to kick off another sys procedure called sp_MSadd_subscriber_info which requires sysadmin:

-- Security Check
    IF IS_SRVROLEMEMBER ('sysadmin') != 1
    BEGIN
        -- "You do not have sufficient permission to run this command."
        RAISERROR(14260, 16, -1)
        RETURN 1
    END

Therefore, sp_addsubscription cannot be run without sysadmin seemingly.

I've also recreated this on a Windows 2012 R2 machine running MSSQLSERVER 2012 (standard).

However, on a Windows 2012 R2 machine running MSSQLSERVER 2014, the issue does  NOT present itself.

Cannot find any information online on this. Is this a bug with earlier versions of windows / SQL does anyone know? Alternatively, am I missing something?


texts: how to GROUP BY on specific column and COUNT() the other columns?

texts:
I've queried a few tables and result is as below:

I want to count correct col upon ct_id(course) col. I queried a lot but gained no success. How to query such a thing?

for example in SELECT I should have two columns: -correctNo -incorrectNo for each course(ct_id).
Note: The last column(correct) is a derived column: IF(selected_answer=answer,1,0) as correct


texts: Can I loop through a pivoted row in Sql Server?


texts: Best approach to apply a long list of LIKE patterns?

texts:
As a follow-up to this question, I have one of my own.
The original question concerns the use of a CASE statement with > than 100 options and the statement has to be used in 4 places - so obviously the SQL is pretty hairy. The OP's question concerned SQL Server 2012, my question, however, is about PostgreSQL.
In my answer, I proposed the use of a VIEW as a "one-stop-shop" solution - i.e. declare the VIEW once, use it anywhere - and this applies for any query in the future also and any variant thereof.
Another poster (@AndriyM) proposed the use of a CROSS APPLY to resolve the issue which is another solution. The PostgreSQL syntax is JOIN LATERAL
I then added a CTE (Common Table Expression) to my original answer as yet another possible solution.
So, the OP now has 5 options:

CASE
VIEW
JOIN LATERAL (CROSS APPLY for SQL Server)
CTE
Separate table

I excluded the option of changing the underlying data since, frequently in this forum, consultants/DBA's/programmers are not allowed change underlying data - makes the answers more interesting also!
Obviously, a CASE expression with > 100 options (x4) is horribly cumbersome and complex - but when is it a good idea to use CASE and at what point does it become a minus rather than a plus?
In my opinion (and not just because it's my answer!), a VIEW is the optimal solution - it's simple, will work for all RDBMS's and is permanent and will work for all queries now and into the future should the OP wish to modify the query.
The JOIN LATERAL construct will work also as a kind of derived table, which is pretty much what a CTE is also. They can both be used down the line in the same query.
Which of the 5 approaches is better/best and at what point does the technical (ease of use, speed, query plan optimisation) tilt in the particular solution's favour?


texts: Why does the scipt executes only once the sqlscript

texts:
the following script is pretty simple, but it works not as expected. I searched for an answer but I am stuck.

#!/bin/bash

db_con=user/pw@db
bname='filename'

for f in $(seq 1 10);
do
    sqlplus -S $db_con @$bname$f
    sleep 2
done

In the *.sql file are statements like:

update db.table_name
set column = bla
where column=foo;
commit;

I expect, that it will run filename1.sql to filename10.sql but sqlplus output shows me only the result of filename1.sql.


texts: how to create backup for update tables structures in existing DB?

texts: I have Sql Server DB, I know I can Create thru "Generate Scripts" backup for SP and Functions then I just replace inside the script the "CREATE" with "ALTER", that will update the target DB with the latest Functions And SP but how to do that for Tables structures  and "User Defined Tables" ?


texts: Problem with manual Change Tracking Cleanup

texts:
I read this blog post regarding the manual Change Tracking Cleanup stored procedure; however the manual clean up doesn’t look like working for me.

Here is my test set up:

Created a test database and enabled change tracking with retention set to 5 min
Created a test table and enabled change tracking on it and entered some test data, like 15000 rows
Performed some random updates
Checked the change tracking internal table and the change tracking meta data made it to the internal table as expected
Waited for > 5 min and then executed exec sp_flush_CT_internal_table_on_demand  @TableToClean = 'test_CTcleanup' under DAC and with out DAC, but the older change tracking meta data doesn’t look like being cleaned up
I have kept the change tracking auto clean disabled on the database, as I want to perform a manual clean up

Can someone please let me know where I am doing something  wrong?


texts: Use another disk for "optimize table" temporary file

texts:
I have a large table (70 Go) on a disk with only 9 Go free space, which I need to optimize ("optimize table ..").

I have changed the "tmpdir" parameter so that the temporary file is created on another disk with enough space.

However the temporary file "#sql..." is still created in the mysql data folder, where tables are located.

Is there a way to tell mysql to create the temporary file on another disk for "optimize table"?

If not, is the only solution to transfer the table onto another disk, run "optimize table" there, then replace the old table file?

Here is the row returned by "SHOW TABLE STATUS" for the table:

Engine  Version Row_format  Rows    Avg_row_length  Data_length Max_data_length Index_length    Data_free   Auto_increment  Create_time Update_time Check_time  Collation   Checksum    Create_options  Comment
InnoDB  10  Compact 218780456   176 38628507648 0   18179555328 17278435328 290029517   2016-02-25 16:33:50 NULL    NULL    utf8_general_ci NULL


texts: SQL Trigger to Insert a row in Table B is the record is inserted in Table A

texts:
I have a requirement where I have to Insert a Record into Table "Site" after the record is inserted into InventOperationalSiteStaging Table. I created a SQL trigger which is working fine for single record scenario but failing when I insert multiple records at a time. Here is the trigger code. Please gjuide me to fix this issue.
CREATE trigger [dbo].[InventSiteAfterInsert] on [dbo].[InventOperationalSiteStaging]
After Insert,Update
AS

DECLARE @SiteId varchar (10),
       @SiteName varchar(60),
       @Description VARCHAR(200),
       @DeleteIndicator bit,
       @ExternalKey VARCHAR(400);

SELECT @SiteId = ins.SITEID FROM INSERTED ins;
SELECT @SiteName = ins.SITENAME FROM INSERTED ins;
SELECT @ExternalKey = ins.MV2ExternalKey FROM INSERTED ins;
;
set @DeleteIndicator = 0;

MERGE Site AS [target]
USING (SELECT [i].MV2ExternalKey FROM INSERTED AS [i]) AS source
ON ([target].Name = [source].MV2ExternalKey)
WHEN MATCHED THEN
UPDATE  SET
         Name = @SiteID,
         Description = @SiteName,
         DeleteIndicator = @DeleteIndicator

WHEN NOT MATCHED THEN
INSERT (
       [Name]
      ,[Description]
      ,[ExternalKey]
      ,[DeleteIndicator])
VALUES (@SiteId,
    @SiteName,
    @ExternalKey,
    @DeleteIndicator );


texts: I have a monitoring system that is telling me that the Full Scans /sec on one of my servers is rather high.  I am trying to determine which queries may be causing the issue.  Is there a simple/easy way to determine which queries are performing full scans on the server?


texts: TokuDB + MariaDB 10: should I use a clustered index

texts:
I have a large table (200 Columns.. I know, but it wasn't my design) that will grow fast and I will have a lot of useless rows in it, identified by an IP (a varchar 32 column).

The table will get hundreds of thousands of rows per day and I will need to delete few thousand rows with a specific IP in that column. During the day at regular intervals (5 min perhaps) I will need to select rows and avoid rows with certain IP. The deletions I'll probably do in the evening not to put too much load on the DB.

Should I use a clustered index or a regular index on this column? TokuDB claim there's no performance loss unlike InnoDB but still we're talking 200 columns (a lot of them empty to be fair (or null or 0s)).

I will need to add more indexes as well on a few other varchar columns as well over which I'll perform selects. Some will have huge cardinality as they're "Timestamps with milliseconds".

Will I benefit or suffer from clustered indexes?


texts: How to insert a large amount of records to the database in a short time?

texts:
I have to insert a bunch of records(500,000) to the database at once, using the fastest way. I've tried inserting 1,700 records at once but it took twenty minutes!! I am using SQL-SERVER, and C#.

Although the following table won't contribute to the technique for fast inserting, I decide to link it anyway so you get a better understanding about the number of fields I need to insert(duplicate it by around 500K).


texts: Can a user with readonly access change SQL server settings

texts:
Database administration is not a strong point of mine, and I was wondering if a user with read only access can change the server settings.

In their query I can see this at the beginning:

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

I'm worried because we have been having SQL database deadlock issues and performance issue, and Microsoft say SET ARITHABORT OFF should be SET ARITHABORT ON

Thanks.


texts: What does a DBA have to know about SSAS?

texts:
I've seen a lot of material covering the business aspect of SSAS, but not really much about the important aspects of administration and management.

From the point of view of administering an instance of SQL Server Analysis Services, what does a working DBA have to know about SSAS to manage it correctly and efficiently?


texts: Remove invisible null characters a string's ending

texts:
For an unknown reason, many strings in one of my VARCHAR(1000) columns have been terminated with invisible characters.

declare @BrokenString varbinary(max)=0x6D0079002000620075006700670065006400200073007400720069006E00670000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003F003F00;
select cast(@BrokenString as nvarchar(max)) -- returns 'my bugged string'
select cast(@BrokenString as nvarchar(max)) + ' is bugged' -- still returns 'my bugged string' !

declare @BrokenStringTable table (Brokey nvarchar(max));
insert into @BrokenStringTable
select cast(@BrokenString as nvarchar(max));
select * from @BrokenStringTable for json auto;

The output from the select * from @BrokenStringTable for json auto; statement looks like this :

[{"Brokey":"my bugged string\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000??"}]

How can I detect which records in my table contains these characters ? It seems using charindex, where+like and any other normal solutions just don't work with these.


texts:
I'm using PostgreSQL 9.6 and have the following table:

CREATE TABLE eco.tracks (
    id                   bigserial  NOT NULL,
    time_track           timestamp  NOT NULL,
    track                bigint  ,
    distance_1             int  ,

    CONSTRAINT pk_tracks PRIMARY KEY ( id )
 );

And I manipulate data it contains with a very simple query:

  SELECT (track, distance_1, time_track)
    FROM eco.tracks
   WHERE time_track - (%s) <=  INTERVAL '10 seconds'
     AND (%s)-time_track <=  INTERVAL '10 seconds'
     AND distance_1 IS NOT NULL
ORDER BY distance_1 asc LIMIT 1;

Some inputs:

INSERT INTO tracks (id, time_track, track, distance_1) VALUES (23467, '2018-05-18 20:43:06', 386, 17965);
INSERT INTO tracks (id, time_track, track, distance_1) VALUES (23468, '2018-05-18 20:43:07', 386, 18168);
INSERT INTO tracks (id, time_track, track, distance_1) VALUES (23469, '2018-05-18 20:43:08', 386, 18168);
INSERT INTO tracks (id, time_track, track, distance_1) VALUES (22974, '2018-05-18 20:40:07', 376, 8394);
INSERT INTO tracks (id, time_track, track, distance_1) VALUES (22968, '2018-05-18 20:40:00', 376, 9051);
INSERT INTO tracks (id, time_track, track, distance_1) VALUES (22837, '2018-05-18 20:39:17', 366, 13133);

This query is used to take the closest track number & other two params  in range of setted time +- 10sec. But in my system, this one is a bottleneck, is it possible to improve it?

I've tried to play around with value work_mem, no success.

Actually, I use it for checking like if-else. And then make another query.
Can I merge it?

    SELECT (track, distance_1, aircraft_time)
      FROM eco.tracks WHERE time_track - (%s) <=  INTERVAL '10 seconds' and (%s)-time_track <=  INTERVAL '10 seconds'  ORDER BY distance_1 asc LIMIT 1,
    CASE track WHEN NOT NULL THEN
    (
    INSERT INTO eco.noise (
    time_noise, track, distance, aircraft_time)
      VALUES (%s, track, distance_1, aircraft_time) ON CONFLICT DO NOTHING;
    )

    OR

 WITH check_1 AS
  (SELECT track, distance_1, aircraft_time FROM eco.tracks
 WHERE time_track - (%s) <=  INTERVAL '10 seconds' and (%s)-time_track <=  INTERVAL '10 seconds'  ORDER BY distance_1 asc LIMIT 1)
 INSERT INTO eco.noise (time_noise, track, distance, aircraft_time)
  VALUES (%s, check_1.track, check_1.distance_1, check_1.aircraft_time)
 WHERE
 check_1.track IS NOT NULL ON CONFLICT DO NOTHING;
                )

Note: I use it in Python 3 by using psycopg2.


texts: Failure during 2 backup jobs for one database

texts:
I have 2 jobs for one database.  One does a full backup every hour.  The other one is a transaction log backup every 15 minutes.

I've checked the job history.   It shows some failures at 16:00.  The detail is as follows:

Does that mean that 2 jobs can't run at the same time?

The error detail is:

  Message Executed as user: NT SERVICE\SQLSERVERAGENT.
  Cannot open backup device 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorksLT_from_Azure.bak'.
  Operating system error 32(The process cannot access the file because it is being used by another process.).
  [SQLSTATE 42000] (Error 3201) BACKUP LOG is terminating abnormally.
  [SQLSTATE 42000] (Error 3013). The step failed.'

Could you please help me for that?


texts:
I try to run SQL Server Upgrade Advisor 2014 against a SQL Server 2008 server.

Everything looks fine when connecting to the 2008 server and I can select which database I would like to analyze.

But once launched, it runs forever, without doing anything (staying at step Analyzing Rules: 0/112).

What's happening and how can I fix that ?

Both SQL Server 2008 and 2014 are Express versions and are installed on my laptop. Unfortunately, this is the only way I found to run the Upgrade Advisor (there are too many restrictions to run it on real prod / dev databases).

The SQL 2008 instance is with SP3 as needed.

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)   Sep 21 2011 22:45:45   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

When the Advisor is frozen, we can see 4 sleeping queries (no active ones):

SELECT name,
       cmptlevel
FROM
  (SELECT name,
          dbid,
          cmptlevel,
          DATABASEPROPERTYEX(name, N'UserAccess') AS 'UserAccess',
          DATABASEPROPERTYEX(name, N'Status') AS 'Status',
          DATABASEPROPERTYEX(name, N'IsInStandBy') AS 'IsInStandBy'
   FROM master.dbo.sysdatabases) t
WHERE LOWER(name) NOT IN ('tempdb',
                          'master',
                          'model',
                          'msdb')
  AND HAS_DBACCESS(name) = 1
  AND dbid NOT IN (32767)
  AND UserAccess != 'SINGLE_USER'
  AND Status = 'ONLINE'
  AND IsInStandBy = 0
ORDER BY name;

and

(@dbname nvarchar(256))
SELECT COUNT(*)
FROM master.dbo.sysdatabases
WHERE name=@dbname

and

SELECT 'Edition'=SUBSTRING(@@VERSION,PATINDEX(N'%Corporation%',@@VERSION)+DATALENGTH('Corporation')+2,PATINDEX(N'% on %',@@VERSION)-(PATINDEX(N'%Corporation%',@@VERSION)+DATALENGTH('Corporation')+2))

and

SELECT N'Job.Step',
        j.name + N'.' + js.step_name
FROM msdb.dbo.syssubsystems ss
JOIN msdb.dbo.sysjobsteps js ON ss.subsystem = js.subsystem
JOIN msdb.dbo.sysjobs j ON js.job_id = j.job_id
WHERE ss.description_id=14555
UNION
SELECT N'Proxy',
        p.name
FROM msdb.dbo.sysproxysubsystem ps
JOIN msdb.dbo.sysproxies p ON ps.proxy_id = p.proxy_id
JOIN msdb.dbo.syssubsystems ss ON ss.subsystem_id = ps.subsystem_id
WHERE ss.description_id=14555

Of course, when we launch them manually, they work.

And there's nothing suspicious when I launch a SQL trace on the server...


texts: SSIS packages run locally but not on Azure (possible host name issue?)

texts:
I am trying to make SSIS packages using Visual Studio 19 that import data from tables in a remote Azure Managed Instance SQL database to a Microsoft Azure SQL DB database, aiming to schedule these to run weekly on Azure Data Factory. I am fairly new to both SSIS and to Microsoft Azure, but there is no-one else in the company with more expertise with either that I could consult for advice.

I have made a package consisting of an SQL script to truncate some tables, ADO NET sources for the source database (since it uses Active Directory authentication), lookups to prevent duplication of destination data (where I'm not truncating first) and OLE DB destinations for the destination tables.

The package runs fine (if somewhat slowly) in Visual Studio on my PC. However when I deploy it via SSMS I get the following validation warning messages:

  Package: "Data_Import": Connection "ConnectionManager1" contains host
  name.

  Package: "Data_Import": Connection "ConnectionManager2" contains host
  name.

  Package: "Data_Import": Connection "ConnectionManager3" contains host
  name.

And when I attempt to execute the package it fails with the messages:

  Import New Data:Error: Campaign Source failed validation and returned
  error code 0xC0208449.

and:

  Import New Data:Error: ADO NET Source has failed to acquire the
  connection {2FA367CE-3495-40B5-926D-F8A08AC5A3DA} with the following
  error message: "A network-related or instance-specific error occurred
  while establishing a connection to SQL Server. The server was not
  found or was not accessible. Verify that the instance name is correct
  and that SQL Server is configured to allow remote connections.
  (provider: TCP Provider, error: 0 - A connection attempt failed
  because the connected party did not properly respond after a period of
  time, or established connection failed because connected host has
  failed to respond.)".

("Import New Data" is one of the data flow tasks and "Campaign Source" is an ADO NET source for the "Campaign" table).

I suspect this is because the connection managers contain the host name as advised by the warning before deploying, as this page suggests that packages featuring the host name instead of the IP address may fail in Azure (but it does not explain how to change the package so it uses IP addresses instead of names): https://docs.microsoft.com/en-us/sql/integration-services/lift-shift/ssis-azure-validate-packages?view=sql-server-2017

What does this mean and how can I change the connection manager properties so they don't contain the host name, so that the packages will run in Azure?

Alternatively, if this is not likely to be the source of the failure, what should I do instead?

Update: We were informed by Microsoft that we needed to peer our VNET with the source database VNET and then link this to the SSIS IR. We have done this, but now we are getting a different error:

  { "SsisDBOperationGuid": "b40b6e4d-f557-4397-9db0-947d641edbe4",
  "SsisDBOperationId": 19, "OperationErrorMessages": "5/29/2019 8:56:44
  AM +00:00 : Import New Data:Error: ADO NET Source has failed to
  acquire the connection {2FA367CE-3495-40B5-926D-F8A08AC5A3DA} with the
  following error message: \"One or more errors
  occurred.\".\r\n\n5/29/2019 8:56:44 AM +00:00 : Import New Data:Error:
  Campaign Source failed validation and returned error code
  0xC0208449.\r\n\n5/29/2019 8:56:44 AM +00:00 : Import New Data:Error:
  One or more component failed validation.\r\n\n",
  "effectiveIntegrationRuntime": "SSIS-IR-PROD" (North
  Europe)", "executionDuration": 14452 }

Because a small test package to import only one table also failed, I assume there is still a connectivity issue between our database and the source database. I have tried running the package in 32-bit runtime but it still gives the same errors.

Any ideas? I am feeling quite at a loss regarding what to try next!


texts: Warning before issuing delete/update command in mysql/oracle , then executes

texts:
Is there any utility / function either in mysql / oracle that prevent I issue some delete/update command wrongly, a message must prompt at command_line* (if possible in ubuntu) asking whether "you sure to run this command" , if yes then delete/update command will execute?

I have googled this, but not a specific solution is provided for this.
I tried SOURCE also, but it is not exactly what I want.

Do any one have any idea over this?

If I'm about to fire update command on > 100 000 records, a prompt should come asking "do you really want to run?", like in Unix if we use rm filename, it asks if you are you sure you want to delete?


texts: Can COALESCE be used with PERFORM/EXECUTE in PostgreSQL?

texts:
I have the following usage of COALESCE, which works as expected:
[...]coalesce
(
    status_avail_direct.status,
    (
        SELECT  status
        FROM    status_avail_direct

        WHERE   (status_avail_direct.meter_id    = oms_rec.meter) AND
                (status_avail_direct.clt_rec_at <= clt_rec.captured_at)

        ORDER BY    status_avail_direct.clt_rec_at DESC
        LIMIT       1
    ),
    (SELECT -1)
)

To make it better readable, I would like to extract and document the embedded SELECT into some outer statement/query or alike. The important thing is that I need values of the currently processed rows for that SELECT to work, e.g. oms_rec.meter and clt_rec.captured_at. From my understanding, because of that a CTE doesn't work, but a prepared statement should exactly fit my needs. So I changed the SQL to the following:
PREPARE status_calc_by_time(timestamp with time zone, int) AS
    SELECT  status
    FROM    status_avail_direct

    WHERE   (status_avail_direct.meter_id    = $2) AND
            (status_avail_direct.clt_rec_at <= $1)

    ORDER BY    status_avail_direct.clt_rec_at DESC
    LIMIT       1
;

[...]coalesce
(
    status_avail_direct.status,
    (EXECUTE status_calc_by_time(clt_rec.captured_at, oms_rec.meter)),
    (SELECT -1)
)

But that doesn't work, a syntax error is recognized instead:

[Code: 0, SQL State: 42601]  FEHLER: Syntaxfehler bei »status_calc_by_time« Position: 263  [Script position: 1510 - 1550]

From the docs I had the feeling that wherever I can use a query directly, I might be able to use a prepared statement as well:

statement
Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement.

https://www.postgresql.org/docs/9.2/sql-prepare.html

Outputs
The command tag returned by EXECUTE is that of the prepared statement, and not EXECUTE.

https://www.postgresql.org/docs/9.2/sql-execute.html
Is there something wrong with my syntax I don't see or does executing a prepared statement like that simply doesn't work at all in this context? In the latter case, where can I use EXECUTE most likely/only?


texts: Why is this blob saving when used as a concatenated string but not as a prepared statement

texts:
I have a PHP mysqli prepared statement that looks like this:

$editSQL = "UPDATE tblClientReminders
              SET Header = ?, Notes = ?, DepartmentID = ?,
                  TierID = ?, RemindType = ?, RemindDate = ?, DateAdded = ?
              WHERE ReminderID = ?";
     $stmt = $conn->prepare($editSQL);
     $stmt->bind_param('sbiiissi', $input[1], $input[2], $input[3],
             $input[4], $input[5], $input[6],
             $DateAdded, $_SESSION[$thisTracker]);
     $DateAdded = date("Y-m-d h:i:s");
     $stmt->execute();
     $stmt->close();

When I run this, it executes without throwing any errors and runs everything else as expected; however, the "Notes" field is cleared even though $input[2] has content.  But when I do this using concatenation:

$editSQL = "UPDATE tblClientReminders
         SET Header = '".$input[1]."', Notes = '".$input[2]."',
             DepartmentID = '".$input[3]."', TierID = '".$input[4]."',
             RemindType = '".$input[5]."', RemindDate = '".$input[6]."',
             DateAdded = '".date("Y-m-d h:i:s")."'
         WHERE ReminderID = '".$_SESSION[$thisTracker]."'";
     $stmt = $conn->prepare($editSQL);
     $stmt->execute();
     $stmt->close();

$input[2] is properly saved along with everything else.  I have verified that the column "Notes" is a blob in the mySQL database, and that $input[2] actually contains the intended data when I try the prepared statement.  So, why is $input[2] not saving in the first example, and how would I fix that.

The only other factor here I can think of is that $input[2] is passing an AES string, but I can't think of why that would be a problem since other queries elsewhere in my code are saving AES strings just fine, but I think this is the only one that does it to a blob.

An example of inputs:

$input[1] = 'test';
$input[2] = '8L2G1urlEQEjZdyMrSQeow==::88f32b3584167b1d7286a57f861d4fd6';
$input[3] = 1;
$input[4] = 3;
$input[5] = 0;
$input[6] = '2018-11-20';
$_SESSION[$thisTracker] = 15;


texts: Are the MariaDB and MySql drivers interchangeable when connecting to a MySQL backend Aurora cluster?

texts:
I saw a similar question asked before but I didn't get enough supporting information

Does anyone know any new information on this?

Can MariaDB connector (driver) be used instead of the MySQL connector (driver) when connecting to AWS Aurora cluster MySQL instance?


texts: SET CONNECTION in PostgreSQL gives "unrecognized configuration parameter"

texts:
According to the PostgreSQL documentation, there appears to be support for SET CONNECTION.

However, when I try to execute it from the psql prompt, I get

  ERROR:  unrecognized configuration parameter "connection"

I'm well aware of using \connect but I want to be able to switch databases within the execution of a .sql file.

Is this a bug in PostgreSQL?  Or does some flag need to be set to create the parameter?


texts: Detect circular relations in a distribution table

texts:
I have the following two tables, that will define distribution rates from items on table A to other items on table A (I think I can say B defines a many-to-many relationship for A with itself).

create table A (
    code char(5) not null primary key,
    desc varchar(50) not null
)

create table B (
    code1 char(5) not null,
    code2 char(5) not null,
    perc numeric(6,2) not null,

    constraint pk_B primary key (code1, code2),
    constraint fk_B_1 foreign key (code1) references A (code) on update cascade on delete cascade,
    constraint fk_B_2 foreign key (code2) references A (code) on update cascade on delete cascade
)

I'd like to have a simple way to check if a new record being inserted on B will cause a circular reference to be created.

This example illustrates the problem:

insert into B (code1, code2, perc) values ('01.10', '02.10', 100);
insert into B (code1, code2, perc) values ('02.10', '02.11', 50);
insert into B (code1, code2, perc) values ('02.10', '02.12', 50);
insert into B (code1, code2, perc) values ('02.11', '04.10', 50);
insert into B (code1, code2, perc) values ('02.11', '02.12', 50);
insert into B (code1, code2, perc) values ('04.10', '01.10', 50);
insert into B (code1, code2, perc) values ('04.10', '04.11', 50);

This set of data would generate the following circular reference:

I'm using Firebird 2.5, but I'd prefer to use only standard sql, because portability is something I'm concerned. We might be changing DBMS soon.


texts: Is it possible to expose the max depth of the B-Tree behind a rowstore index or a way to see how many nodes were traversed for a particular query?

texts:
I'm personally interested in data structures and with knowing that rowstore indexes are stored with a B-Tree behind them, I'm always curious in trying to visualize what those B-Trees look like for a given table/index.
Is there a way to correlate or expose the number of nodes in a B-Tree for a given rowstore index via the execution plan, statistics, operator properties, index / table properties etc?
Furthermore can I do the same to determine the number of nodes traversed for a particular query?
Bonus points if there's a way to determine the number of leaf nodes in the B-Tree too.


texts: Database design for holding read/unread content state

texts:
In forum application I have member table and post table. What I need is to keep track of what posts have been read by which members. What I thought about is to have a junction table with two columns: member_id and post_id. Each time a post is seen (read) by a member a record is inserted.

I am going to need to fetch/calculate/perform:

Number of unread posts for each member
Get all unread posts for each member
Given list of posts, indicate which ones are unread by particular
member
Mark all posts as read for each member separately

And I am going to need to do this on every request, since 1) and 2) is displayed in sidebar widget present on all pages and 3) is going to be needed when browsing through posts (also very common).

The model works in theory (and in practice under not much load), but sure it does not seem like a scalable solution. I am going to get ~1,000 posts per day viewed by ~500 members which gives 500,000 records a day in junction table...

I am a programmer not DB expert, thus I am asking for some ideas on how to optimize this situation.


texts: Temporary Table error when executing entirety of script

texts:
I'm having a problem with running the following script in it's entirety - That is, if I run it line-by-line separately it appears to work, but if I run everything at once, I get the error Message "There is already an object named '#customers' in the database." Any ideas as to why this happens?

if object_id('tempdb..#customers') is not null DROP TABLE #customers
if object_id('tempdb..#temporary_storage') is not null DROP TABLE #temporary_storage

SELECT [customer_number]
  INTO #customers
  FROM [source_placeholder]

SELECT DISTINCT([customer_number]) AS 'customer_number'
  INTO #temporary_storage
  FROM #customers

    IF object_id('tempdb..#customers') is not null DROP TABLE #customers

SELECT *
 INTO #customers
 FROM #temporary_storage


texts: Visual studio 2010 missing BI 2010 (SSDT 2010)

texts:
Where can I find a link to install SSIS 2010 ( SSDT 2010 or Business Intelligence 2010)

I have VS 2010 on my machine.


texts: MySQL - How to check whether all values of a given SELECTed row are equals?

texts:
Using MySQL, how can i SELECT all rows whose values are equals ? For instance, suppose a SELECT of a given table as follows

COLUMN_A   COLUMN_B   COLUMN_C ... COLUMN_N
       1          2          4            7
       6          3          5            6
       5          5          5            5
       7          1          3            4
       3          3          3            3

I need a SELECT which should return

COLUMN_A   COLUMN_B   COLUMN_C ... COLUMN_N
       5          5          5            5
       3          3          3            3


texts:
I cannot get SQL Server to parse my query as I tried to alter a table and add a constraint.
like this:

ALTER TABLE TaxVal
ADD CONSTRAINT ch_taxVal CHECK (CASE WHEN Col1 IS NOT NULL THEN Col2 = 1 ELSE 0 END)

I want to update or set the value of col2 base on the value or condition of col1. Thank you


texts:
I've newly taken over administration of a system with about 25 databases. Using Redgate Monitor to chart some metrics, I see that each day, one database shows values for log flushes / second that look like a "normal" chart - a zig-zag line throughout the day.

Redgate explains the metric: "A log flush occurs when a transaction is committed and data is written from the log cache to the physical transaction log file."

All well and good - work is happening and data is getting flushed to disk as it commits.

My question is - why do all the other databases fail to register anything much above the x-axis at all?

Redgate also explains: "Log flushes per second should generally correlate with the number of transactions per second."

When I view transactions per second, plenty of databases are showing zig-zag chart lines - so what's going on that only one of them is showing activity for log flushes? Does this mean all the other databases are failing to commit?

All user databases are using full recovery model.

(I also note between 2am and 5am there are huge spikes in log flushes for almost all databases - still to investigate the cause of these).


texts:
I have a really big table (approx. > 100.000.000 rows and a size of > 50GB) and it's getting a big performance kill right now. Beneath the primary key (id) it uses a fulltext key on a varchar(500) field to use the mysql fulltext search option.

However, saving and getting rows to this table is getting really slow right now.. How can I handle this? This is my first problem. My second problem is, that getting a backup with mysqldump of this table is no use, because it would take months to import it back. The third problem is, that this table uses the engine MYISAM and a conversion to INNODB is also not possible (I've tested it and aborted the proccess after 72 hours).

So, what would be a good future-proof approach to speed this table up, back it up correctly and maybe convert it to INNODB? (INNODB should accept FULLTEXT with my mysql version)


texts:
When we update a MySQL record with php, we can check if it has effect using:

$mysqli->affected_rows;

But how do I check which column has been modified?

Example, in my table have the columns: id / name / age

In a record we have the data: 1 / Woton / 18

If I send an: UPDATE mytable SET id = '1', name = 'Woton', age = '20' WHERE id = '1'

Only the age field has changed, but SET was for everyone, how do I get the columns back that the data actually changed?


texts:
In Microsoft SQL Server you can see Execution Plans for queries to see how a query is executed, what joins take the most time and see any missing indexs

now i want to do the same for MySQL scripts that run on a MySQL Server, i am wondering what programs or tools i can uses for this

i am aware of EXPLAIN however from my reading it only handles SELECT which is ok for SELECT and INSERT INTO ... SELECT queries but i also want to check out UPDATE and DELETE queries

EDIT: we are running MySQL 5.5


texts: How do you set the MySQL password on Mac OS X?

texts:
I've downloaded mysql 5.25 on mac os x Lion (10.7.4). I just tried setting my password in the usual way, however I'm getting an access denied error which is odd:

[user@Macintosh:~] #mysqladmin -uroot password 'secret'

mysqladmin: connect to server at 'localhost' failed

Is there something I'm missing about setting the root password for this version of mysql on this version of the mac? The database is definitely running according to what I see in the control panel / ps -ef.


texts: Updating a table which contains an Image datatype column

texts:
I have a table which contains a few columns, one of which is an image datatype, this is used to store BLOB data, which could be anything from a jpg to a large PDF.

I have a requirement to update one of the other columns in the table, however the query is taking a ludicrously long time to complete - that's if it does complete at all.

The table is the following format:

ID (int), HexData (image), FileName(nvar), Filetype(nvar), folder(nvar), user(int)

The query used is simply -

update [database].[dbo].[myTable]
set user = 5
where id = 2

This is a test table on a test database, not the live system. There are only 5 rows, no indexes (including clustered), and no other tables in the database.


texts: Can a MySQL trigger include a where clause

texts:
I need to create a trigger that, any time a goat's sireRegNo column is updated, its sireName column is automatically updated to match.

I have a database that stores animal genealogy. Each row stores both the names and the registration #s of both its parents. The sireName field is readonly, but registrars may change sireRegno.

Create statement:

CREATE TABLE `table_name` (
    `id_goat` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(80) NOT NULL,
    `regNo` varchar(50) DEFAULT '',
    `sireName` varchar(80) DEFAULT NULL,
    `sireRegNo` varchar(50) DEFAULT '',
    PRIMARY KEY (`id_goat`,`name`),
    UNIQUE KEY `id_goat_UNIQUE` (`id_goat`),
    UNIQUE KEY `name_UNIQUE` (`name`),
    UNIQUE KEY `regNo_UNIQUE` (`regNo`)
) ENGINE=InnoDB AUTO_INCREMENT=188784 DEFAULT CHARSET=utf8;

example data:

table_name

name    |   regNo   |   sireName    | sireRegNo
----------------------------------------------
Old Man |   2048    |   Methusela   |   9999
Daddy   |   1234    |   Grandpa     |   4321
Kid     |   9191    |   Daddy       |   1234

DISCLAIMER: Yes, I know this is not normalized. I've tried JOINing on the regno, and found it took forever. Reason being, I think, we have a search page that, using DataTables, loads every row in the DB (only the public data from each, though). It doesn't display it all at once, but it loads it, so it can use search input to filter through them client side. So we were basically trying to JOIN the entire table to itself, and it took ~30 seconds per page load.

I know there's probably something I did wrong somehow, and there's a way to do this "properly" but that's not what I'm asking here. Even so, answers to that end are welcome.

Kid is now a teenager, and insists that someone put him in the database wrong, and that his real dad is Old Man, regno 2048.

My question then - can I make a trigger so that, when the registrar changes Kid's sireRegNo to 2048, Kid's sireName column gets changed to "Old Man"?

TRIGGER VERSION (least comfortable with this, least certain of what this looks like to anybody else)

Here's my attempt at an "actual" trigger, just in case there's some significant difference between triggers and stored procedures. Modified from mid way down this page

mysql> delimiter //
mysql> CREATE TRIGGER update_sire_name BEFORE UPDATE ON table_name
    -> FOR EACH ROW #I trust this means "each row that is modified"...
    -> BEGIN
    ->     IF NEW.sireRegNo = table_name.regNo THEN #searches regNo field of every row in DB for the new value stored in sireRegNo
    ->         SET NEW.sireName = table_name.name
    ->     END IF;
    -> END;//
mysql> delimiter ;

STORED PROCEDURE VERSION (more comfortable, possibly farther from what I need?)

modified from this page

DELIMITER //
CREATE PROCEDURE sp3
(IN reg CHAR(50))
BEGIN
    SELECT name FROM goatdata
    WHERE regno = NEW.reg;
    SET NEW.sireName = 'result of above SELECT statement'
END //
DELIMITER ;

I've not dealt with triggers or stored procedures before, so that looks weird to me and is probably flat wrong in multiple ways. Also, I realize this is for a stored procedure, but as I've been searching, I saw somewhere say triggers ARE stored procedures, so I'm hoping this is clear, and maybe even that I could just replace "procedure" with "trigger"? Maybe? It certainly feels a lot closer to what I'm trying to do, in any case.

REGULAR MYSQL (clearest to me, but obviously not remotely what I need)

SELECT name, regno FROM table_name WHERE regno = 'correct sire regno';
UPDATE table_name SET sireName = 'correct sire name' WHERE regno = 'kid regno'


texts: SQL Server Replication Alternative Software

texts:
We have used SQL Server Replication for a long time and had some issues with it that sometimes we needed to reinitialize subscriptions to fix some issues & other times we needed to destroy the whole replication structure & rebuild it again!

Our main concern is that once we have a replication issue, almost all the time the easy solution will be to reinitialize the replication which is not accepted for our business requirements.

Now we are preparing to release a new big project & we are trying to look for 3rd party software for doing SQL Server replication.

Our setup includes Servers distributed in branches ( Different countries ) + mobile clients ( laptops with local SQL Server databases ) and we need to replicate data between all of these with the ability to offer article filtering.

Would somebody please suggest some alternate solutions for us?


texts:
I have this query:

    SELECT id,type,source_id,link,short_link,cat_id,title,description,images,views,comments,published,publishedon FROM listings WHERE
(YEARWEEK( FROM_UNIXTIME( publishedon ) , 1 ) = YEARWEEK( CURDATE( ) , 1 )) AND
(id != 88587) AND
(cat_id = '64')
AND (deleted = 0)
AND (published = 1)
ORDER BY views DESC LIMIT 1 OFFSET 0

table explain

CREATE TABLE IF NOT EXISTS `listings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` tinyint(1) NOT NULL DEFAULT '1',
  `hash` char(32) NOT NULL,
  `source_id` int(10) unsigned NOT NULL,
  `link` varchar(255) NOT NULL,
  `short_link` varchar(255) NOT NULL,
  `cat_id` mediumint(5) NOT NULL,
  `title` mediumtext NOT NULL,
  `description` mediumtext,
  `content` mediumtext,
  `images` mediumtext,
  `videos` mediumtext,
  `views` int(10) unsigned NOT NULL,
  `comments` int(11) DEFAULT '0',
  `comments_update` int(11) NOT NULL DEFAULT '0',
  `editor_id` int(11) NOT NULL DEFAULT '0',
  `auther_name` varchar(255) DEFAULT NULL,
  `createdby_id` int(10) NOT NULL,
  `createdon` int(20) NOT NULL,
  `editedby_id` int(10) NOT NULL,
  `editedon` int(20) NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `deletedon` int(20) NOT NULL,
  `deletedby_id` int(10) NOT NULL,
  `deletedfor` varchar(255) NOT NULL,
  `published` tinyint(1) NOT NULL DEFAULT '1',
  `publishedon` int(20) NOT NULL,
  `publishedby_id` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8956 ;

I cannot make any changes in fields types.

Is there any way to make it fast?
It takes to execute 0.7518 sec,0.8776 sec.


texts: Can we export data from PostgreSQL to xlsx file?

texts:
I want to export the result of a query to xlsx file format.
I know we can export it to csv file, but I want to export it to xlsx file.

Like the following:

copy(select name_related from hr_employee limit 3) to '/tmp/ABC.xlsx'
delimiter ',' xlsx header;


texts: SQL Server : execute stored procedure from another stored procedure

texts:
I have two stored procedures.

Proc1 executes a SQL statement and results a data table as a result set.
I need to use the datatable in a select SQL that resides in proc2 as a datasource. like,

SELECT Col1
FROM (Exec Proc1)

How can I do it? This syntax here shows error in SQL Server.


texts: Use name of table as input in procedure and store the output of SELECT statement in output variable SQL

texts:
I am trying to create a procedure, that will accept as input a variable called @tablename and will return as output the value of a variable called @myoutput. As the names of the variables signify, @tablename should be the name of a table and @myoutput should be just be the sum of the the first column of the table specified  by @tablename, divided by the number 3.3. The procedure I have written is the following:

CREATE PROCEDURE sample_proc
@tablename VARCHAR(MAX),
@myoutput DECIMAL(20,5) OUTPUT

AS
SET NOCOUNT ON;

    @myoutput=EXEC('SELECT SUM('+@tablename+'.column1)/3.3)'

When I just use EXEC(), the select statement is executed as it should, but I cannot get the value I want from the table. What I want, is to store the value I calculate in the variable @myoutput, and export that to my main script. The above code produces an error. Does anyone know how I can implement this?


texts: 32-bit or 64-bit for development and deployment?

texts:
I'm creating a Windows desktop application and want to include a database. I've chosen SQL Server Compact 4 but when I download it there are two versions (32-bit and 64-bit). My development machine is 64-bit, so do I download this version? Or, do I download the version that I expect users to run (which, I guess, is most likely to be 32-bit). If I download and work with the 64-bit, when I release the database, will it cause errors on 32-bit machines?
Thank you.


texts: Effect of where statement in subquery (to get rid of unnecessary join)

texts:
Please consider the following query:

select *
from parent p
where p.id in (
  select p.id
  from parent p
  inner join child c on c.parentId = p.id
  where p.age > 50
)

Is it safe (in general!) to rewrite the query to this, in order to save a join? The subquery contains the same alias as the outer query (not a typo).

select *
from parent p
where p.id in (
  select p.id
  from child c
  where c.parentId = p.id
  and p.age > 50
)

My concerns are that the subquery's where-statement is going to be applied to the outer query's result. Is that reasoning correct?

Please note that the query above is just a simple example and the real query is big, where the sub-select is somewhere in some or-condition and contains several joins.


texts: Inserting with implicit type conversion causes warning for cardinality estimates

texts:
I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning:

  Type conversion in expression (CONVERT_IMPLICIT(nvarchar(50),[tempdb].[dbo].[#MyFunIntTable].[EvenCoolerColumn],0)) may affect "Cardinality Estimate" in query plan choice.

Being a concerned citizen, I checked all of the obvious suspects and eventually dug into the XML to confirm that it was actually warning about the insert into the table. The problem is, I can't figure out why this would ever affect cardinality estimates. If I were doing this in a join or somewhere with a little more logic it would make sense, but there shouldn't be a cardinality estimate mismatch for the actual insert operation, right?

I noticed that this happened when it was more than just a trivial query - as soon as more than one value is inserted, or we're pulling a value from a table, we hit this.

This question has attracted some potential duplicates, including:

Why am I getting an implicit conversion of Int / Smallint to Varchar, and is it really impacting Cardinality Estimates?
Type conversion in expression may affect "CardinalityEstimate" in query plan choice?

I think it is different from these questions because I'm literally not doing anything with this column. I'm not using it in a filter, or a sort, or a grouping, or a join, or in a function - any of these things makes the scenario more complicated. All I'm doing is inserting a bigint into a nvarchar, which should never impact a meaningful cardinality estimate that I can think of.

What I'm specifically looking for out of an answer is:

An explanation of why I get this warning despite nothing of interest going on - is it just that SQL Server will be conservative and report even when it won't affect plan choice?
What cardinality estimate is actually at risk here, and what operation would change based off of inaccuracies in that cardinality estimate?
Is there a scenario where this could affect plan choice? Obviously if I start joining or filtering on the converted column it could, but as-is?
Is there anything that can be done to prevent it from warning, besides changing data types (assume this is a requirement of how the data models interact)

I recreated it with the below simple example (paste the plan)

DROP TABLE IF EXISTS #MyFunStringTable;
DROP TABLE IF EXISTS #MyFunIntTable;

CREATE TABLE #MyFunStringTable
(
  SuperCoolColumn nvarchar(50) COLLATE DATABASE_DEFAULT NULL
);

CREATE TABLE #MyFunIntTable
(
  EvenCoolerColumn bigint NULL
);

INSERT INTO #MyFunIntTable
( EvenCoolerColumn )
VALUES
( 1 ),
( 2 ),
( 3 ),
( 4 ),
( 5 );

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
  SELECT EvenCoolerColumn
    FROM #MyFunIntTable;

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
VALUES
( 1 );

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
VALUES
( 1 ),
( 2 );

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
  SELECT 1;

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
SELECT 1
UNION ALL
SELECT 2;

INSERT INTO #MyFunStringTable
( SuperCoolColumn )
  SELECT 1
    FROM #MyFunIntTable;


texts: Enumerate drives available to SQL Server

texts:
I'm trying to work out a way of listing the drives that a particular instance of SQL Server is "aware" of, and understand the process.

I can run EXEC master..xp_fixeddrives which gives me a list of drives, but when I look in the GUI (Eg. Databases | Restore Database | From device | Add ) the list of available drives is far fewer.

By what process does SQL server become "aware" of a drive and is there any way to problematically determine the drives it is aware of?

Thanks in advance!


texts: Entity-Relationship Diagrams: Identifying which way a relationship flows

texts:
I am having trouble grasping the "relationship" concept of an entity-relationship diagram. For example, you have Classroom and Furniture as your entities. If you were to put a relationship diamond with the word Contains and a line between the two, how would one be able to tell which contains which?

Obviously we know that Furniture cannot contain a Classroom, however, when it comes to applications and data models there are often foreign concepts and terms meaning we have no prior knowledge of the relationship or possibilities.

Without actually having words to describe the diagram, which would defeat the purpose of the ERD (as it should speak for itself), how would one determine visually which way the relationship was flowing?

Thanks in advance.

FYI; in case it is relevant - the line touching Classroom has a "one and only one" symbol, the line touching Furniture has a "zero through many" symbol.


texts:
I have two tables that I want to combine.

I need to be able to show all DelDates that had changes from the original (record 1). Record 1 as the first Record and Record 0 (table 2) always being the last record.

Result should be:

I am new to SQL and with my research, it seems I need to use UNION to join both tables and to join the new table (from Union) by itself.  When I tried doing that, it created duplicate rows and columns.


texts: Oracle Query to Compare dates from Two Different tables


texts:
A user entered a batch of records with the wrong suffix in the record name - FY15-Q2 instead of FY16-Q1. So, the records are something like:

-LAG-FY15-Q2
-AN-FY15-Q2
-OY-FY15-Q2
-RV-FY15-Q2
etc

when they should be

-LAG-FY16-Q1
-AN-FY16-Q1
etc

There are a fairly limited number of prefixes, so I could always do multiple update statements like SET record name = LAG-FY16-Q1 WHERE record name = LAG-FY15-Q2.

It strikes me there might be a more elegant way to do this with a single update statement.  Something with trim, perhaps?  Complicating this is the fact that some of the prefixes are three letters and others are two.


texts: We have a table that is clustered on identity/datetime2. It is partitioned on the same datetime2. Are there any reasons to cluster on datetime2/identity instead? I understand the reasoning behind clustering generally, but with partitioning included, do things change?