texts: mysql binary keyword issue with special characters

texts:
Password in database:- !|%$&./()=?ì^
If I use below query:-
SELECT * FROM `user` WHERE Password='!|%$&./()=?ì^' AND Status='1'

This works fine.
But I am checking for case sensitive passwords with below query:-
SELECT * FROM `user` WHERE BINARY Password='!|%$&./()=?ì^' AND Status='1'

This does not provide any data. On removing ì, this works.
Password field collation: latin1_swedish_ci
Note:- I can not change collation because there are too much data in database.
Any suggestion will be appreciable.


texts:
I can query XML, which has defined xmlns tag with common table expression

DECLARE @XML XML

SET @XML =
'<NodeA xmlns="https://XYZ.xsd">
<NodeB verzePis="">
    <NodeC1 attrA="Hello" />
    <NodeC2 attrA="World" />
  </NodeB>
</NodeA>
'

;WITH XMLNAMESPACES(DEFAULT 'https://XYZ.xsd')

    SELECT
        r.value('fn:local-name(.)', 'nvarchar(50)') as SectionName,
        r.value('@attrA','NVARCHAR(250)') attrA
    FROM    @XML.nodes('/NodeA/NodeB/*') AS t(r);

But what if source XML does not contain any xmlns tag ?
How can i workaround missing namespace ?


texts:
Regarding the this question I want to ask

Comparing two queries:

(1)
DELETE dbA.dbo.tableA
FROM dbA.dbo.tableA a WITH(NOLOCK)
JOIN dbB.dbo.tableB b WITH(NOLOCK)
  ON
      b.colA = a.colA
  AND b.colB = a.colB

and

(2)
DELETE FROM dbA.dbo.tableA
WHERE EXISTS
(
  SELECT *
  FROM dbB.dbo.tableB b WITH(NOLOCK)
  where
      b.colA = dbA.dbo.tableA .colA
  AND b.colB = dbA.dbo.tableA .colB
)

it is clear, that the queries do the similar work, if we not consider concurrent writes which can lead to dirty reads.

BUT i have a doubt

was I right with the following comment?

  note, that there will be placed a shared (upgradeable?) lock @ tableA from the very
  beginning of the statement (2), instead of two NOLOCK tables in join (1),
  which will place the first lock - UX lock only after there will be
  found the first row to delete


texts: How to index/optimize sum of two columns in PostgreSQL?

texts:
We have a database where one table contains serialized temporary data that needs to be kept for various times (usually between tens of minutes and two weeks). We also have a low priority background process that removes old rows from the table. The background process removes up to 1000 lines during one transaction:

delete from temporarydata
    where id in (
        select id from temporarydata
            where (created + ttl) <= 1553755330 limit 1000
    )

The 1553755330 in the example is current number of seconds since UNIX epoch and created contains seconds since UNIX epoch the data was added and the ttl contains the number seconds the data should be kept alive.

Technically this does work but there are around 2M lines in the temporary data and the subselect gets pretty slow because the sum requires doing sequential scan over the table to find all matching rows. This causes extra background load on the database.

> explain (analyze,verbose,timing,buffers) select id from temporarydata
        where (created + ttl) <= 1553755330 limit 1000

Limit  (cost=0.00..402.34 rows=1000 width=16) (actual time=6735.811..6735.811 rows=0 loops=1)
  Output: id
  Buffers: shared hit=3068 read=230500
  ->  Seq Scan on public.temporarydata  (cost=0.00..262980.99 rows=653622 width=16) (actual time=6735.809..6735.809 rows=0 loops=1)
        Output: id
        Filter: ((temporarydata.created + temporarydata.ttl) <= 1553755330)
        Rows Removed by Filter: 1916405
        Buffers: shared hit=3068 read=230500
Planning time: 0.402 ms
Execution time: 6735.849 ms

I'd prefer just adding a new index that can always contain the sum of created + ttl that PostgreSQL were able to use for this query automatically. Is this possible with high performance?

(I'm considering rewriting the application code to save created and expires instead of ttl where expires is created+ttl. Then I compute logical ttl as difference of those values. I think the application does not emit heavy queries on ttl alone.)


texts:
I am using oracle 11g express edition and I am getting the below error when trying to create a change set.
Error report:
ORA-06550: line 2, column 3:
PLS-00201: identifier 'DBMS_CDC_PUBLISH.CREATE_CHANGE_SET' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I know CDC is desupported in later versions of oracle but I think it is supported here.
Can anyone help please?
I've tried to explicitly grant permission to the SYSTEM user with
grant execute on DBMS_CDC_PUBLISH to SYSTEM;

But I get
ORA-04042: procedure, function, package, or package body does not exist


texts:
I am looking to restrict SELECT/INSERT for the 60-80 tables in the database.
The database has very complex security structure, and want to restrict INSERT/SELECT for all users (and except SysAdmin, no other login should have access to those tables).
is that possible? thank you in advance.


texts: How to detect which indexes have checksums and which ones don't?

texts: Is there a query that will show which tables / indexes need to be rebuilt so that they have checksums available for data verification?   The DB was switched recently from torn page to checksum verification so only some newer indexes / tables have the required checksums and those which index maint has rebuilt.  Need to see what the remaining tables / indexes are that need rebuilding to have checksum enabled.


texts:
I have a query as below:

SELECT o.order_id,
       c.customer_name,
       Group_concat(p.product_name, ' x', p.product_quantity ORDER BY
       receipt_order ASC
       SEPARATOR
       '
') AS items,
       order_status,
       o.delivery_collection,
       o.order_total,
       o.payment_method,
       o.discount_value,
       o.order_time,
       o.order_note,
       IF(o.delivery_collection = 'delivery', (SELECT
       Group_concat(address_1, address_2, postcode SEPARATOR '
') AS delAdd
                                               FROM   address a
                                               WHERE
       c.customer_id = a.customer_id
       AND c.customer_type =
           a.customer_type), NULL)
FROM   orders o,
       order_products p,
       customer c
WHERE  c.customer_id = o.customer_id
       AND c.customer_type = o.customer_type
       AND o.order_id = p.order_id
       AND order_time > Now() - INTERVAL 72 hour
GROUP  BY p.order_id
ORDER  BY order_status DESC,
          o.order_id DESC

The problem I have is that the Group_Concat() inside the IF concatenates but it doesn't include the separator and is not called delAdd.

I have also tried CONCAT_WS and CONCAT but all are giving the same results.

I am using MySQL 5.6.43 on 10.3.18-MariaDB server.


texts: Computer restarted and Postgres 9.2 service cannot be started?

texts:
My firewall is down and when I tried to start the Postgres service it gave me an error that said: the service started and then stopped. Some services stop automatically if they are not in use by other services or programs.

I need to start postgres for pgadmin and when I tried to connect to pgadmin it gave me an error that I've seen before but none of those conditions of the error is true because it worked right before my computer was automatically restarted.

I honestly didn't want to change my conf file and simply do it from the pgadmin postgres.conf gui.

The console also gave me an error about a line that had debug assertions =, which I put a hash mark to the left of which got rid of the error.

When I try to run postgres from the command prompt it gives me that postgresql.conf contains errors? But what errors would they be?

I also tried running from the console a couple of different ways and one of the errors I got was pg_ctl: could not start service "postgresql-9.2": error code 1063


texts: i always get o for my result.why is it so?

texts:
CREATE DEFINER=`root`@`localhost` PROCEDURE `roomAvailbility`(
  IN checkindate date,
  IN checkoutdate date,
  out roomtype  char(20))
BEGIN
  select count(*)
    INTO roomtype
  from room_1401557b where not exists
    (select room_1401557b.room_id
     from reserved_room_1401557b,room_1401557b,reservation_1401557b
     where room_1401557b.room_id=reserved_room_1401557b.room_id
     and reservation_1401557b.reservation_id=reserved_room_1401557b.reservation_id
     and current_date()>=check_in_date and current_date()<check_out_date)
  and room_1401557b.room_type=roomtype
  ;
END


texts:
CREATE TABLE TEST
(
  ID    NUMBER,
  COL1  NCLOB                                   DEFAULT 'EMPTY_CLOB()',
  COL2  NCLOB                                   DEFAULT EMPTY_CLOB(),
  COL3  NCLOB                                   DEFAULT (EMPTY_CLOB())
);

What is the diferrence between DEFAULT 'EMPTY_CLOB()' and DEFAULT EMPTY_CLOB() and DEFAULT (EMPTY_CLOB()). All empty_clob() output same or are they different?

INSERT INTO TEST(ID) VALUES(1);

SELECT ID, LENGTH(COL1), LENGTH(COL2), LENGTH(COL3) FROM TEST;

output:

1   12  0 0


texts:
We have a database hosted on azure. It is part of an azure elastic pool.
From yesterday all our database operation are consistently failing with following error.

The transaction log for database is full due to 'OLDEST_PAGE'

We checked for all active transactions on database but there are no active transactions currently but still log file is taking 100% of space.

I have gone through following documentation pages, but I was not able to understand the issue completely:

The transacion log
Database checkpoints

Can anyone please help me understand this issue?

Update

When we moved this DB to new pool then log size reduced automatically.
We are not experiencing any issue after move.


texts: Select parent with a specific child with ordering by a column with no grouping by this column

texts:
I have got a parent table, let's call it Post, with two columns - PostId, Title. I have got another one table, let's call it Comment - CommentId, PostId (FK), CreatedDate, Type, Message

I need to get a flat view of parent and one of it's childs which will look like below:

PostId, Title, CommentId, CreatedDate, Type, Message

I need the oldest one comment in this view for each post, but I also need Type and message though I don't want to group by CreatedDate, Type and Message.

Without grouping by them I can't use ORDER BY

I have got two options here - using PARTITION OVER and CROSS/OUTER APPLY. First is not an option cause of performance gain, second one is ok but I can't for example filter by Type (WHERE Type = 3) since it's a view.

What I've tried:

Option one:

SELECT DISTINCT
        P.PostId,
        P.Title
FROM dbo.Post AS P
OUTER APPLY
(
    SELECT TOP 1
        C.CommentId,
        C.CreatedDate,
        C.Type,
        C.Message
    FROM dbo.Comment C
    WHERE
        C.PostId = P.PostId
    ORDER BY C.CreatedDate ASC
)

Option two:

SELECT
        P.PostId,
        P.Title,
        C.CommentId,
        C.CreatedDate,
        C.Type,
        C.Message
        ROW_NUMBER() OVER (PARTITION BY p.PostId
                           ORDER BY C.CreatedDate ASC) AS Generation
FROM dbo.Post P
LEFT JOIN dbo.Comment C
ON C.PostId = P.PostId

and then use WHERE Generation = 1 from application server

To be clear, the issue is that OUTER APPLY performs great with SQL side paging (skip 100 take 100 for example), but when I need to take a count - it's a bottleneck since of each row processing

Not sure if it helps, but I need something like this:


texts: Is it possible to restore a MySql database only using files from Data directory?

texts:
I have a mysql installed inside a windows virtual machine. The hard drive was crashed and the system is not able to restore all again. But I can have access to entire c: ROOT. So I went to

c:/ProgramData/Mysql/Mysql Server 5.5/data/myDatabaseName

There I can see different files like: .frm, .opt, .trg and .trn.
In a regular way, I could run a mysqldump to backup and restore all database from command line, but the system is not able to be restored. So I had to copy all these files and past inside inside the same  directory inside same database in another physical instance.

The issue is some tables are not been recognized by this new instance. So I think copy and past is not enough to restore the database.

What am I missing?

Note: Database has innoDb and Myisam tables.


texts: Track daily view count of youtube videos

texts:
I am making program that searches youtube for given keyword and need to get links of active videos. Term "active" means that video must have 1000 views per day. Youtube API doesn't give such statistics, only total view count. Now I want to run Java program every day to search for given term and to save data into database. I made database in SQLite and want to ask you if my database model is ok.
So, I have just one table VIDEO. Primary key is "saying key",  based on date. For example: 201108040001, 201108040002, 201108040003, 201108050001, 201108050002, 201108050003.
Second attribute is Address, for example: http://www.youtube.com/watch?v=pqha-F3KYHk&feature=youtube_gdata_player, http://www.youtube.com/watch?v=FAIgWGCYGJU&feature=youtube_gdata_player, etc.
Third attribute is Date.
Forth attrubute is TotalViewCount.
Fifth attribute is SearchTerm.

Now I wonder if this database model is good for getting statistical data. For example, I want to get videos that have more than 1000 view per day. To do this I have to subtract TotalViewCounts from 2 dates, and so for every row in a table.

Here is table DDL:

CREATE TABLE VIDEO (
 VideoID        INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 Address        VARCHAR( 100 )  NOT NULL,
 Date           DATE            NOT NULL,
 TotalViewCount INT             NOT NULL,
 SearchTerm     VARCHAR( 100 )  NOT NULL
);


texts: SSAS queries time out when Windows File System Cache drops

texts:
We have a SQL 2008 Analysis Services instance on Windows 2003 that is experiencing near-daily performance issues (query timeouts) during peak activity. A second identical instance queried from the same application (using round-robin IP to load balance) doesn't have the same issue. We've even switched the two instances, and the problem seems to "stay" with the physical server. We know it's not an SSAS configuration issue. The cube is rebuilt once per day.

Here's what we've been able to determine: the timeouts occur when the Memory\System Cache Resident Bytes perfmon counter drops unexpectedly (we've been calling this a "cache flush"). For example, this morning it dropped from about 14GB to 8.4GB within about 2 minutes. When this happens, the Memory:Page Reads/sec jumps from 0 up to 800-1200, and things start going sideways.

You can see the immediate impact on the MSOLAP counters: Storage Engine Query\Data reads/sec drops dangerously from 50,000 to 5,000 (these obviously weren't physical reads, they were reads from the system cache; this is confirmed by a similar drop to Process(msmdsrv)\IO Read Operations/sec). Storage Engine Query\Avg time/query climbs from 500-2000 up to 20,000-50,000 and queries start timing out. Threads\Processing pool busy threads jump from ~50 up to the max configured value of 240, and the Threads\Processing pool job queue length starts climbing.

Queries continue timing out over the next 30 minutes or so, until (I presume) the file cache is re-filled with the missing data. Memory\Page Reads/sec goes back down to near zero, and things appear to return to "normal".

During this entire process, there is no significant change to Process(msmdsrv):Working Set (~10.5GB) , Process(Total):Working Set (~13.9GB) or MSOLAP:Memory\Memory Usage KB (~50GB). This is a 48-core server with 128GB RAM, running only SSAS (the other server that isn't having trouble is a 24-core box, we haven't found any other significant differences between them).

Instance memory settings are as follows:

<TotalMemoryLimit>80000000000</TotalMemoryLimit>
<LowMemoryLimit>70000000000</LowMemoryLimit>
<PreAllocate>40000000000</PreAllocate>

Based especially on the Memory Usage KB, I don't believe that memory is an issue, other than figuring out why SSAS isn't using its internal memory to cache the entire cube (the cube is about 32GB).

We are currently working to optimize the cube and query design (which I highly suspect will make the problem disappear entirely).

In the meantime, we need to

understand this windows file cache behavior
figure out why its consistently a problem on one physical server and not the other

Here are my questions:

I assume this is "normal" behavior by SSAS? Is there any way to force the entire 32GB cube into SSAS memory, to eliminate the need to read from the windows file cache entirely?
Is there any way to determine what exactly is causing this cache flush, and why it doesn't occur on the (very similar) other host?
Any other ways to mitigate this problem??

I found some other references to SSAS and the windows file cache (the links found here for example), but these seem to imply that the file caching is a bad thing, and talk about a utility to deliberately flush the cache, which seems like the opposite of what I need.

Edit: Added a 100pt bounty


texts:
I have two exact tables with different data. I need to be able to generate a Mysql View with the following:

All new rows that don't appear in previous_table but appears in current_table.
All rows in previous_table that has updated column values in current_table.

The tables don't have any primary keys. I cannot change the schema because it is out of my control.

Any help would be deeply appreciated.


texts: Same query hash for totally different queries

texts:
Query_hash definition from  sys.dm_exec_query_stats is:

  query_hash: Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.

But when I search for one particular query_hash (as shown below), I am getting multiple query texts that differ heavily in logic and size_in_bytes (for plan).

Note: I am using SQL Server 2016

DECLARE @QueryHashTest BINARY(8)
SET @QueryHashTest = CONVERT(BINARY(8), 'Ð…U¹üŒv¿')

SELECT
        QCP.objtype
        ,qStat.query_hash,
        CONVERT(VARCHAR(100),  qStat.query_Hash) AS VARCHAR_query_hash
        ,sText.text AS QueryText
        ,QCP.size_in_bytes
        ,qStat.creation_time
        ,qp.query_plan
FROM (
        SELECT query_hash,
               COUNT(query_hash) AS PlanCount
        FROM sys.dm_exec_query_stats
        GROUP BY query_hash
    ) AS MultipleQ
INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash
INNER JOIN sys.dm_exec_cached_plans  QCP
    ON QCP.plan_handle = qStat.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText
CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp
WHERE PlanCount > 1
AND QCP.objtype = 'Proc'
AND qStat.query_hash= @QueryHashTest
AND (size_in_bytes >= 2179072 OR size_in_bytes <= 262144)
ORDER BY size_in_bytes DESC

Result

Why are the unrelated queries showing same query_hash? Is it a bug or is there something unexpected going on in the database?


texts: Procedures using TVPs are slower when the TVP numeric value gets larger?

texts:
A legacy application has a nightly job that repeatedly calls some store procedure using a TVP and passes in batches of 10,000 ids that are in sequential order that it needs to process.  Now that the ids are in the millions, it seems that this process is taking noticeably longer. The roughly the same number of batch calls are being run each night, but from profiling it seemed that the procedure was getting slower.
We checked the usual culprits, rebuilt the indices and updated stats on the tables in use and tried sticking a recompile on the procedure. But nothing fixed the regression.
The procedure does some processing and returns a few result each with a cardinality of maybe 10000 rows. One of my colleagues looked at it and fixed the performance regression by updating the store procedure by simply adding the following to the top of the query:
select id into #t from @ids

and replacing all usages of @ids with #t.
I was amazed at this simple fix, and was trying to understand it more. I tried to create a very simple reproduction.
create table dbo.ids
(
   id int primary key clustered,
   timestamp
);

create type dbo.tvp as table(id int primary key clustered)

insert into dbo.ids(id)
select row_number() over (order by 1/0)
from string_split(space(1414),' ') a,string_split(space(1414),' ') b
go
create or alter procedure dbo.tvp_proc
(
    @ids dbo.tvp readonly
)
as
begin
    declare @_ int = 0, @r int = 5;
    while(@r > 0)
        select @_ = count(*), @r -= 1
        from dbo.ids i
        where exists (
            select 1
            from @ids t
            where t.id = i.id
        );
end
go
create or alter procedure dbo.temp_proc
(
    @ids dbo.tvp readonly
)
as
begin
    select * into #t from @ids
    declare @_ int = 0, @r int = 5;
    while(@r > 0)
        select @_ = count(*), @r -= 1
        from dbo.ids i
        where exists (
            select 1
            from #t t
            where t.id = i.id
        );
end

And here is my simple benchmark.
set nocount on;
declare @s nvarchar(4000)=
'declare @ids tvp;
insert into @ids(id)
select @init + row_number() over (order by 1/0)
from string_split(space(99),char(32)) a,string_split(space(99),char(32)) b
declare @s datetime2 = sysutcdatetime()
create table #d(_ int)
insert into #d
exec dbo.tvp_proc @ids
print concat(right(concat(space(10),format(@init,''N0'')),10),char(9),datediff(ms, @s, sysutcdatetime()))',
@params nvarchar(20)=N'@init int'
print 'tvp result'
exec sp_executesql @s,@params,10000000
exec sp_executesql @s,@params,1000000
exec sp_executesql @s,@params,100000
exec sp_executesql @s,@params,10000
select @s=replace(@s,'tvp_proc','temp_proc')
print 'temp table result'
exec sp_executesql @s,@params,10000000
exec sp_executesql @s,@params,1000000
exec sp_executesql @s,@params,100000
exec sp_executesql @s,@params,10000

Running this benchmark on my machine yields the following results:
tvp result
10,000,000  653
 1,000,000  341
   100,000  42
    10,000  12
temp table result
10,000,000  52
 1,000,000  60
   100,000  57
    10,000  59

The results show that the tvp approach seems to slow down as the ids inside get bigger, where as the temp table stays pretty consistent. Anyone have an idea as to why referencing a tvp with larger values is slower than a temp table?


texts:
Gone through the below link also but i am unable to import CSV file in mysql. Even i had tried by using > LOAD DATA LOCAL INFILE but same error persisting.
Please help me in this. queries i tried had appended below

MySQL import csv only gets half the lines

mysql> LOAD DATA INFILE 'home/karthick.g.s/Desktop/karthick.csv'
    -> INTO TABLE test_1
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '^'
    -> LINES TERMINATED BY '\r\n';
ERROR 13 (HY000): Can't get stat of '/var/lib/mysql/home/karthick.g.s/Desktop/karthick.csv' (Errcode: 2)
mysql> LOAD DATA LOCAL INFILE 'home/karthick.g.s/Desktop/karthick.csv'
    -> INTO TABLE test_1
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '^'
    -> LINES TERMINATED BY '\r\n';
ERROR 1148 (42000): The used command is not allowed with this MySQL version

I am using the version 5.5.37-0ubuntu0.12.04.1


texts: How to install columnstore in MariaDB on Mac OS X with Homebrew

texts:
I would analyse time-series data using MariaDB ColumnStore but error encountered

MariaDB [(none)]> CREATE DATABASE forex;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE forex;
Database changed
MariaDB [forex]> CREATE TABLE gbpusd (
    ->     id INT,
    ->     time DATETIME,
    ->     open DOUBLE,
    ->     high DOUBLE,
    ->     low DOUBLE,
    ->     close DOUBLE
    ->     ) engine=ColumnStore default character set=utf8;
ERROR 1286 (42000): Unknown storage engine 'ColumnStore'

brew install mariadb

After checking, I found the columnstore engine didn't exist.

MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                                               | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
| MyISAM             | YES     | MyISAM storage engine                                                            | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

Please advise how to enable columnstore in MariaDB


texts: Disable and Rename sa, years after build

texts:
Answers and comments at a recent popular question Does changing "sa" password require a SQL restart (in mixed mode)? indicate that renaming and disabling sa at build time is a best practice.

Any time you have a well-known account, like administrator on a Windows system or sa for SQL Server, you should take certain steps to secure it. Let's look at specifically what you should do with sa:
Set a hard to guess password.
Rename sa.
Disable sa.
Ensure that no other accounts exist named sa.

Source
But what if it has been years since the instance was built? the original DBA is long gone, I don't know what strange things they might have done. It's a production server, I can't just change make changes to the sa login and fix things as they come to light.
If I rename and disable the sa account years later, what issues might I have?
Can all the possible issues be identified and addressed, before making changes to the sa account?


texts: Error: 9602, Severity: 16, State: 1.The error is printed in terse mode because there was error during formatting

texts:
From today onward i am getting the below  error in
SQL Server 2012

  Error: 9602, Severity: 16, State: 1. (Params:). The error is printed
  in terse mode because there was error during formatting. Tracing, ETW,
  notifications etc are skipped.

I am also attaching the screen shot.

Any suggestion will be appreciated.


texts: Creator of a MySQL table automatically granted all table permissions?

texts:
I recently read it in SilberSchatz book that in MySQL the creator of a relation/view is the owner and granted all permissions on the relation/element including the power to transfer these grants to other users.

I tested this on MySQL myself:

Logged in root account

mysql -u root -p

I created a new user test_user.

create user test_user identified by ''

Then I gave grant to test_user for creating new tables on whole database "testing" which already exists

grant CREATE on testing.* to test_user

Then I logged into test_user account

mysql -u test_user -p

I created a new table TABLE1

create table table1(id int auto_increment,name varchar(20), primary key (id));

But when I insert data into this table it is giving error.

insert into table1 values(1,'alankrit');

  ERROR : INSERT command denied to user 'test_user' for table 'table1'

When I do give test_user INSERT grant from root then I can insert data into table1.

The question is that test_user is creating table TABLE1 so why is he not getting INSERT grant on this table on its own as emphasised in this book.

Secondly, do we need to ask root to grant us permissions on everything we create on our own? Wouldn't this be problematic? How will this work?


texts: percona mysql crashing at innodb initialization

texts:
i have a slave percona db setup that also maintains few of it's own tables

it started crashing with this in error.log:

InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 4047010, file name bin-log.000529
InnoDB: and relay log file
InnoDB: position 0 4047154, file name /var/mysql/relay/relay-log.001563
InnoDB: Last MySQL binlog file position 0 71871595, file name /var/mysql/bin/bin-log.001518
120709  9:00:27  InnoDB: Waiting for the background threads to start
120709  9:00:27 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=2147483648
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2427524 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = (nil) thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x39)[0x8024a9]
/usr/sbin/mysqld(handle_segfault+0x3c2)[0x519df2]
/lib/libpthread.so.0(+0xef60)[0x7f3edfb98f60]
/usr/sbin/mysqld[0x838d8f]
/usr/sbin/mysqld[0x935e67]
/usr/sbin/mysqld[0x936795]
/usr/sbin/mysqld[0x92c2bd]
/usr/sbin/mysqld[0x8579f5]
/usr/sbin/mysqld[0x84ce2c]
/lib/libpthread.so.0(+0x68ba)[0x7f3edfb908ba]
/lib/libc.so.6(clone+0x6d)[0x7f3edee3202d]

after that mysql process dies

there is enough memory on server - that is not the cause

any ideas?


texts: Postgresql AUTOVACUUM ANALYSE parameters for a specified table

texts:
I would like to set autovacuum parameters for a specified table.

My problem is that AUTOVACUUM ANALYSE on a small table takes way to long to complete.
This table has only 2 columns (including the primary key) and 150 rows but it is frequently updated. Each row gets updated every minute after the client process has started.

I've found out that the autovacuum process on this table is taking up to 20 seconds.
From what I've understood so far, this is because the autovacuum process is not executed as frequently as it should.

Following are the parameters set on the server configuration :

autovacuum_analyse_threshold => 50
autovacuum_analyse_scale_factor => 0.1

What parameters do you suggest I set specifically on this table ?

Thanks in advance.

UPDATE 1 : In production, the autovacuum analyse process runs on this table every 2 minutes and it take more than 10s.

UPDATE 2 : Table size is 15998 pages.

UDPATE 3 : I have 99 users (client connections) updating the table every 15 seconds.


texts: CMS (Central Manamenent Server) help/guidance?

texts:
We have just recently come across the Central Management Server feature in SQL2008.
We currently have a number of environments and think that this feature could really help us in running multiple instances queries.
But before making the move to implement this to our production environment, would anyone with experience please help us with the following points?

Are there any best practices you recommend?
Are there any drawbacks/cons to consider if we decide to use the feature?
Any negative in creating a group consisting of both 2005 and 2008 servers?
And in regards to transactions, is this per instance or a whole group?

any reply would be very much appreciated.


texts: Missing FROM-clause entry with constraint on value from type in PostgreSQL

texts:
I have an issue with the following script:

CREATE DOMAIN shop.amount AS numeric(8,2) DEFAULT 0 NOT NULL CHECK (value > 0::numeric);

CREATE TYPE shop.money AS (
   m_amount shop.amount,
   m_currency shop.currency
 );

CREATE TABLE shop.order_item
 (
   oi_id bigserial NOT NULL,
   oi_order_id bigint NOT NULL,
   oi_article_id bigint NOT NULL,
   oi_article_price shop.money NOT NULL,
   CONSTRAINT oi_pk_id PRIMARY KEY (oi_id),
   CONSTRAINT oi_fk_article_id FOREIGN KEY (oi_article_id)
       REFERENCES shop.article (a_id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION,
   CONSTRAINT oi_pk_order_id FOREIGN KEY (oi_order_id)
       REFERENCES shop."order" (o_id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION,
   CONSTRAINT price_cannot_be_less_than_ten CHECK (oi_article_price.m_amount >= 10::numeric)
 );

When I execute the script using PGAdmin I get

ERROR:  missing FROM-clause entry for table "oi_article_price"

I guess that the error is related to the constraint, somehow.
What's wrong?


texts: Postgres full text search with multiple columns, why concat in index and not at runtime?

texts:
I've come across full text search in postgres in the last few days, and I am a little confused about indexing when searching across multiple columns.

The postgres docs talk about creating a ts_vector index on concatenated columns, like so:

CREATE INDEX pgweb_idx ON pgweb
    USING gin(to_tsvector('english', title || ' ' || body));

which I can search like so:

... WHERE
      (to_tsvector('english', title||' '||body) @@ to_tsquery('english', 'foo'))

However, if I wanted to sometimes search just the title, sometimes just the body, and sometimes both, I would need 3 separate indexes. And if I added in a third column, that could potentially be 6 indexes, and so on.

An alternative which I haven't seen in the docs is just to index the two columns seperately, and then just use a normal WHERE...OR query:

... WHERE
      (to_tsvector('english', title) @@ to_tsquery('english','foo'))
    OR
      (to_tsvector('english', body) @@ to_tsquery('english','foo'))

Benchmarking the two on ~1million rows seems to have basically no difference in performance.

So my question is:

Why would I want to concatenate indexes like this, rather than just indexing columns individually? What are the advantages/disadvantages of both?

My best guess is that if I knew in advance I would only want to ever search both columns (never one at a time) I would only ever need one index by concatenating which use less memory.


texts: What is the optimal set of database maintenance tasks for high volume of transactions

texts:
I have a database that has a high volume of data being inserted and deleted each week.

Every week there are approximately one million rows of data being deleted and inserted. Each month the base table on which the data gets inserted grows by around a million rows.

I think this large number of transactions are causing the log file to grow larger and get fragmented.

I am afraid the performance may get worse each month.

What is the best way to maintain and manage such a database?

If anyone can explain or provide a link that deals with such a scenario that would be great.


texts: How to correct SQL Server Import and Export Wizard error that says my data conversion length must be between 0 and 4000?

texts:
The message I get from the 2017 SQL Server Import and Export Wizard which I need help with is,

  "Error 0xc0204016: SSIS.Pipeline: The 'Data Conversion 0 - 0.Outputs[Data Conversion Output].Columns[Description]' has a
  length that is not valid. The length must be between 0 and 4000"

The data source column in question is titled "Description" and is of Excel type "General". This spreadsheet was given a long filename extension of ".iqy.xslx", and it was produced by a SharePoint Online's "Export to Excel" command which I performed on a SharePoint list. I'm using Excel installed locally via the Office365 website. Back in the SharePoint list, the original column "Description" is of type "Multiple lines of text". The destination I am trying to populate using the SQL Server Import and Export Wizard is a VARCHAR(MAX) column within a table inside an Azure SQL Database.

You can clearly see in the following screen how the SQL Server Import and Export Wizard perceives the Excel data column in question. Here it calls it a "LongText", and I'm unable to change the data types of neither the source nor the destination:

The error message pops up once I begin the conversion in a subsequent screen, and you can see it if you click on the Messages link as seen below.

How can I properly input this simple Excel spreadsheet into my table on Azure?


texts: SQL Server Web Service in a web environment architecture

texts:
I am new at SQL Server web Service and not too knowledgeable in setting up a secure web environment...
From what I've read until now regarding SQL Web Service, I would need to:
1. create a stored procedure,
2. create an endpoint
3. create a web service
4. call the stored procedure from the web...

Sounds easy enough although for Steps 3 and 4, I don't have a clue how to do for now... but my question is in the architecture before I start testing.   From my understanding, the SQL web service resides on the SQL Server?
If this is the case, it means that the DMZ would have direct access to our internal network? that's a nono as far as I know...

In an architecture where I would want an SQL Server within a closed off network, and an application/business tier within a DMZ and web servers in another DMZ... How would I be able to get some information that is needed from the SQL Server in the closed off network while keeping it secure?  hence the reason why I started investigating the possibility of using the SQL Server Web Service...

In my mind, each web server in DMZ1 will have their DB in the DMZ which will contain minimal non sensitive info to run the website, but retrieving general accumulated information (such as points, sales or historic info, etc.) that can be generated from different web sites and different sources cannot be transferred to the Website DBs due to their size and need the data to be in real-time.
I was thinking of having a DMZ2  which contains the SQL Server Web Service (as an SQL Web Service Layer) which would retrieve data from the SQL server within our network.
Is it possible?
Is it a good idea?
If not, How would you go about it?

Thank you for your help.


texts: LEFT JOIN subquery fails in the presence of an EXISTS clause in the main query

texts:
I have this query:
SELECT c.id contact_id, engagement.sent
FROM contact c
LEFT JOIN (
    /* ➊ Inner query */
    SELECT eq.contact_id contact_id, COUNT(*) sent
    FROM mailing_job j
    INNER JOIN mailing_event_queue eq ON eq.job_id = j.id
    WHERE j.end_date > NOW() - INTERVAL 3 MONTH
    GROUP BY eq.contact_id
  ) engagement ON engagement.contact_id = c.id

 WHERE
  /* ➋ various conditions on table 'c' */
 do_not_email = 0 AND is_opt_out = 0 AND is_deceased = 0 AND is_deleted = 0
   AND contact_type = 'Individual'

   /* ➌ */
   AND EXISTS (
      SELECT gc.contact_id
      FROM group_contact gc
      WHERE gc.contact_id = c.id AND gc.group_id IN (30,386,14,6,214,5,88,361,334,18,9,17,240,7,13,10,292,291,290,12)
    )

   /* ➍ AND c.id=1 */
ORDER BY c.id

The inner query ➊ when run on its own yields results, e.g. sent: 15  for contact 1. This is the correct, expected data.
However, running the whole query gives NULL for the whole sent column! Which is vexing me.
Eliminating the WHERE clauses at ➋, or eliminating the EXISTS at ➌, or including the commented out ➍ clause causes it to behave and return sent: 15 for the test contact.

I'm on MariaDB 10.3.
EDIT
Hoping to add clarity. Fundamentally this is weird -I think- and possibly a bug in MariaDB, though I hope not!
The Inner query ➊ returns rows, e.g.
contact_id  sent
1           15
...

The main query also picks up contact with ID 1.
Now in all my years, when I've left joined a table on a primary key (i.e. c.id) where a record in the subquery matches, it's never changed its values.
The fact that those values come and go with changing other WHERE clauses, but in ways that still mean the outer query generates the matching contact ID, I think, is weird.
I'm really hoping I've done something stupid, but I can't see it.


texts:
I have a table in MySQL which has approximately 10 million records, and now a query which used to take micro seconds now takes more that 8 seconds to execute!
Here is my table structure:

CREATE TABLE `runs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `runstub` VARCHAR(20) DEFAULT NULL,
  `userid` INT(11) DEFAULT NULL,
  `processid` INT(11) DEFAULT NULL,
  `rundata` VARCHAR(255) DEFAULT NULL,
  `token` VARCHAR(60) DEFAULT NULL,
  `created_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`)
) ENGINE=INNODB AUTO_INCREMENT=10039971 DEFAULT CHARSET=latin1;

and here is the query that takes more than 8 secs...

SELECT MIN(created_at), MAX(created_at) FROM runs ORDER BY id DESC LIMIT 10000

Is there a way I can optimise this query to get it run in less than a second?

This is the result I want to achieve: From the last 10k records (not all), the time it took. I want the time it started (MIN(created_at)) and the time it ended (MAX(created_at)). How can I achieve that without the limit?


texts: Trigger failing when called using sp_executesql

texts:
We have a recursive trigger on a table that executes AFTER INSERT. There is a check to ensure the trigger does not recurse more than 32 levels deep:

IF(SELECT TRIGGER_NESTLEVEL()) >= 31
        RETURN

When I insert a row into this table using SSMS, the statement appears to execute as a batch and the trigger is executed successfully. All of the changes the trigger performed were correct.

However, when I insert a row in the table using a web app (the ORM we are using is called Dapper) the statement gets parameterized and executed via sp_executesql and the following exception is thrown:

  Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Both statements were executed using the same data and on the same machine (local instance of SQL Server 2012 Express). Recursive triggers have been enabled on the target database. I can also verify that my test data is nowhere near 32 levels deep.

From what I have read here https://stackoverflow.com/questions/21265979/dapper-calls-sp-executesql-when-i-have-parameters-is-there-a-way-around-that it appears that the call to sp_executesql is an ADO.NET behaviour and not directly controlled by Dapper. I can verify that I get the same error when using LINQ 2 SQL and EF to insert a row in my table.

I have performed the same tests and received the same results on an instance of SQL Azure.

Would anybody have an idea why the trigger would throw an error when called using sp_executesql?


texts: How can I determine the date of a snapshot in SQL Server 2008r2?

texts:
I am working on implementing an ETL process to load data into a warehouse.

Part of this process is pulling data from our production server (Server P) to a staging server where the actual load into the warehouse (and the warehouse itself) is located (Server S).  We are using snapshot replication for this.

For QC and some other purposes, I need a way to determine the date that the snapshot was taken on Server P, from Server S.  Linking servers for this query I don't think is an option.

I'm hoping that there is a record in a metadata table somewhere to reflect this, or some other command I can run to query the snapshot history.


texts: Inconsistent query execution times using trigram indexes?

texts:
I have a table residences...

I installed trgm: create extension pg_trgm;

After adding trigram indexes to both grp and name columns using:

CREATE INDEX residences_name_trgm ON residences USING GIN (name gin_trgm_ops);
CREATE INDEX residences_grp_trgm ON residences USING GIN (grp gin_trgm_ops);

And checking performance of a simple query... it appears that the total runtime for a query on the name column runs ~100x faster. Why is this?

EXPLAIN ANALYZE SELECT * FROM residences WHERE name ILIKE '%Sutton%';
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on residences  (cost=36.02..47.90 rows=3 width=1872) (actual time=0.390..0.720 rows=21 loops=1)
   Recheck Cond: ((name)::text ~~ '%Sutton%'::text)
   ->  Bitmap Index Scan on residences_name_trgm_gin  (cost=0.00..36.02 rows=3 width=0) (actual time=0.354..0.354 rows=21 loops=1)
         Index Cond: ((name)::text ~~ '%Sutton%'::text)
 Total runtime: 0.814 ms
(5 rows)

and

EXPLAIN ANALYZE SELECT * FROM residences WHERE grp ILIKE '%Sutton%';
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on residences  (cost=97.93..8879.41 rows=5927 width=1872) (actual time=5.516..115.634 rows=5968 loops=1)
   Recheck Cond: ((grp)::text ~~ '%Sutton%'::text)
   ->  Bitmap Index Scan on residences_grp_trgm_gin  (cost=0.00..96.45 rows=5927 width=0) (actual time=4.366..4.366 rows=5968 loops=1)
         Index Cond: ((grp)::text ~~ '%Sutton%'::text)
 Total runtime: 119.779 ms
(5 rows)

For reference, both name and grp columns are CHARACTER VARYING(50).

Futhermore... counting distinct values in each column yields:

SELECT COUNT(DISTINCT grp) FROM residences;
421

SELECT COUNT(DISTINCT name) FROM residences
7750

That is, the name column actually has ~20x more distinct values, but returns values much faster.

More information on this technique can be found on depesz's article WAITING FOR 9.1 – FASTER LIKE/ILIKE and another similar tutorial on the PalominoDB blog.


texts:
I have a table with three columns (ID, ReviewToId, ReviewFromId)

I would like to get record with the ReviewToId and ReviewFromId is not Consistent

For example, with pic attached:

I would like to return result with ID 4,6 and 8 Cos

4 say it review from 2, but 2 review to 3
5 review to 6, but 6 say it review from nothing
7 review is 8, but 8 say it review from nothing

How to write the SQL query for this data result?

Using left outer join?


texts: How to add Reporting services sharePoint features in SQL Server 2008R2

texts:
I am facing some issues related to SSRS on SQL Server 2008R2,
how can we have install below features:

Reporting Services- SharePoint

Reporting Services Add-in for SharePoint Products

Any one could you please help me.

Thanks


texts: SQLite, update one row with another row data

texts:
I have a following table
CREATE TABLE LoadRecords(RoomNum INTEGER DEFAULT 0, UserNum INTEGER DEFAULT 0, PresetNum INTEGER DEFAULT 0, PresetName TEXT DEFAUTL 'Preset', Load1 INTEGER DEFAULT 0, Load2 INTEGER DEFAULT 0, Load3 INTEGER DEFAULT 0, Load4 INTEGER DEFAULT 0, Load5 INTEGER DEFAULT 0, Load6 INTEGER DEFAULT 0, Load7 INTEGER DEFAULT 0, Load8 INTEGER DEFAULT 0, Load9 INTEGER DEFAULT 0, Load10 INTEGER DEFAULT 0, Load11 INTEGER DEFAULT 0, Load12 INTEGER DEFAULT 0, Load13 INTEGER DEFAULT 0, Load14 INTEGER DEFAULT 0, Load15 INTEGER DEFAULT 0, Load16 INTEGER DEFAULT 0, Load17 INTEGER DEFAULT 0, Load18 INTEGER DEFAULT 0, Load19 INTEGER DEFAULT 0, Load20 INTEGER DEFAULT 0, Circuit1 TEXT DEFAUTL '-', Circuit2 TEXT DEFAUTL '-', Circuit3 TEXT DEFAUTL '-', Circuit4 TEXT DEFAUTL '-', Circuit5 TEXT DEFAUTL '-', Circuit6 TEXT DEFAUTL '-', Circuit7 TEXT DEFAUTL '-', Circuit8 TEXT DEFAUTL '-', Circuit9 TEXT DEFAUTL '-', Circuit10 TEXT DEFAUTL '-', Circuit11 TEXT DEFAUTL '-', Circuit12 TEXT DEFAUTL '-', Circuit13 TEXT DEFAUTL '-', Circuit14 TEXT DEFAUTL '-', Circuit15 TEXT DEFAUTL '-', Circuit16 TEXT DEFAUTL '-', Circuit17 TEXT DEFAUTL '-', Circuit18 TEXT DEFAUTL '-', Circuit19 TEXT DEFAUTL '-', Circuit20 TEXT DEFAUTL '-')

I would like to update all Load1, Load2, Load3... Load20 from one row to few other rows within the same RoomNum but different UserNum inside of the same table.

is there a query that can do that for specific PresetNum ?
Edit.
This is what I have used. I wish SQLite supported loops as I really need to be able to change RoomNum plenty of times...


texts: mysqlsh rebootClusterFromCompleteOutage force prompt

texts:
From mysqlsh I can run this instruction:

...
shell.connect(host, pass);
dba.rebootClusterFromCompleteOutage(clusterName);

But then it's asking me (y|N answer) for each instance it finds offline whether or not I want to turn it on again.

In the context of scripting this instruction (automation), how can I ignore these prompts and force y(es) for all of them?


texts:
there is a table ticket_messages.
CREATE TABLE `ticket_messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ticket_id` bigint(20) NOT NULL,
  `attachments` json DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I need to get a list of unique ticket_ids, and is there any attachments for this ticket.
I try this:
select ticket_id, any(attachments is not null)
from ticket_messages
group by ticket_id

and get error
[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(attachments is not null) from ticket_messages group by ti' at line 1

And try this:
select ticket_id
from ticket_messages
group by ticket_id
having any(attachments is not null)

and get error:
[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(attachments is not null)' at line 4

is it possible to compute has_attachments here?


texts: Using "Where clause" In OPENROWSET as variable

texts:
I need to have a where clause in OPENROWSET but it is not always fix.
It might be null sometimes.
I have a procedure as you can see here :

ALTER PROCEDURE [dbo].[OpenExcel] @TableName VARCHAR(2000)
    ,@FileName VARCHAR(2000)
    ,@ExcelName VARCHAR(2000)
    ,@SheetName VARCHAR(100)
    ,@Header VARCHAR(50)
    ,@WhereClause VARCHAR(1000)
    ,@ResultOpenExcel INT OUTPUT
AS
BEGIN
    DECLARE @Detail NVARCHAR(max)
        ,@RowFlag BIT = 0

    SET @ResultOpenExcel = 0

    BEGIN TRY
        BEGIN TRANSACTION OpenExcel

        DECLARE @Provider NVARCHAR(2000);

        SET @Provider = 'Microsoft.ACE.OLEDB.12.0';

        DECLARE @date DATETIME

        SET @date = getdate()

        DECLARE @SQL NVARCHAR(MAX) = ' SELECT * ,' + QUOTENAME(@ExcelName, '''') + ' AS ExcelName ,' + QUOTENAME(@date, '''') + ' AS TimeStamp
   INTO #TTT
     from openrowset(''' + @Provider + ''',''Excel 8.0;Database=' + @FileName + ';hdr=' + @Header + '' + ''',
                     ''select * from [' + @SheetName + '$]' + @WhereClause + ' );

     insert into ' + @TableName + '
     select *
     from #TTT
     drop Table #TTT'

        PRINT @SQL

        EXEC (@SQL)

        --------------------------------
        DECLARE @RowCount INT = @@ROWCOUNT;

        IF (@RowCount > 1)
            SET @RowFlag = 1
        SET @ResultOpenExcel = 1

        COMMIT TRANSACTION OpenExcel
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION OpenExcel

        SET @ResultOpenExcel = 0

        SELECT @Detail = ErrorMessage
        FROM dbo.GetErrorInfo()

        EXECUTE [TestTest].[dbo].[Sp_Log] @TableName
            ,'[dbo].[OpenExcel]'
            ,@Detail
    END CATCH

    RETURN @ResultOpenExcel
END

I need to pass the "Where Clause" it might sometimes have a content and sometimes not. For instance it might be  like this : Where age >'23'.
I'm testing my procedure but it gives me this error :

Incorrect syntax near 23

What is wrong with the openrowset part of my query?

the @Print part show me this in my example:

"SELECT * ,'SMR1' AS ExcelName ,'Oct  6 2018  4:07PM' AS TimeStamp
   INTO #TTT
     from openrowset('Microsoft.ACE.OLEDB.12.0','Excel 8.0;Database=C:\NewExcelFile\SMR1.xlsx;hdr=yes',
                     'select * from [Sheet1$]where Age >'23' );"

It seems that my query is correct so what is wrong here?


texts: How to make MYSQL smart enough to understand when it should take into account stop word and when it shouldn't?

texts:
Ok, here is the problem. I have a table

Text
text nature ....
text2 nature ....
a lot of nature ... rows here..
....
go with nature
with nature her go it

Now, i select:

select * From MyFullText where MATCH(text) AGAINST ('+go +with +nature' IN BOOLEAN MODE)

As a human being, we will bring out only 2 rows:

Text
go with nature
with nature her go it

However, MySql is not human & it shows all rows that have the word "nature" since "go" & "with" are stop words so it doesn't take into account these words.

What if we have 1000 rows containing the word "nature"? if that is the case then user will waste a lot of time for searching.

In this case like '%go with nature%' does a much better job than fulltext.

So, can you find out an elegant solution that meets these requirements:

-Use "Fulltext ability for all non-stop words"

-If there are "stop words" in the search term then it should go back to use LIKE as in like '%go with nature%'

-Generally, it will be very smart to bring the best result depending on the search term.


texts: Constrain unique value between multiple tables

texts:
I have a database where there's one base table that is extended (by foreign keying) by other tables. Something like this:

I've been assigned a task where I have to do the following check before inserting an "Animal":

Animals Id must be UNIQUE
(Thus) Fishes Id must be UNIQUE
There may be ZERO OR MORE "dead" (Alive = False) Fishes for a specific FishEspecieId
There may be ZERO OR ONE "alive" (Alive = True) Fish for a specific FishEspecieId

The task suggested that I do it on API insert method, something like this (pseudo-code):

Method Insert(Fish fish)
    Fish[] fishes = DB.GetAll<Fish>()

    if (fishes.Any(f => f.FishEspecieId == fish.FishEspecieId f.Animal.Alive))
        throw "Only one alive fish allowed"

    Animal animal = DB.Insert<Animal>({alive: true})
    DB.Insert({Id: animal.Id, ...})

I'm actually going to, indeed, do a check in the API. However, I think it's a good idea to also ensure the rules on the DB side.

How may I do this in SQL Server? And is this indeed a good idea or should I keep only in the backend side?

Ps. This is only a MRE. I know that if this was my database I should make it a single table. If that was the case, I would simply use UNIQUE(FishEspecieId) WHERE (Alive = 1). In my case, however, I have many tables that extends "Animal", or something like this.


texts: Update stats w/full scan on SQL Server 2014 uses 100% cpu, on 2008 R2, 15%

texts:
Why does full scan update statistics use 100% of CPU on SQL Server 2014 when it uses maybe 20% of CPU on SQL Server 2008 R2, for the same tables, with similar hardware capability?

I have been looking at MAXDOP, other options, and really see nothing that stands out. I realize that there could be settings that could cause this, but the settings are very similar for both databases (for example, MAXDOP is 4 for both, with both having multiple cores). Both are Enterprise Edition.

Is there something "different" in SQL Server 2014 versus SQL Server 2008 R2 that could explain this?  I have the memory option at 90% for both servers.  Any thoughts on what to look for?

I run update statistics with full (100%) scan once a week on two servers using SQL Server 2008 R2/SP3 and SQL Server 2014/SP2, and the databases have the same structure.  On the 2008 R2 server the update stats of two very large tables takes several hours, which is what I expect, but the CPU stays under 20% or so utilization the entire time. On the 2014 server, though, the CPU goes to 100% for about 40 minutes. The tables are a bit smaller on the 2014 server. I see this by using SQL Monitor analysis menus.

Here is the output of the Ola log file on the 2014 SQL Server, CPU goes to 100% from about 2:10 to 2:45:

Date and time: 2017-06-24 02:10:20
Command: UPDATE STATISTICS [InVA].[dbo].[AuditField] [_WA_Sys_00000005_15502E78] WITH FULLSCAN
Outcome: Succeeded
Duration: 00:07:48
Date and time: 2017-06-24 02:18:08

Date and time: 2017-06-24 02:18:08
Command: UPDATE STATISTICS [InVA].[dbo].[AuditField] [_WA_Sys_00000006_15502E78] WITH FULLSCAN
Outcome: Succeeded
Duration: 00:32:22
Date and time: 2017-06-24 02:50:30

Here is the output of the Ola log file on the 2008 R2 SQL Server for the two stats above, but CPU goes to maybe 15%:

Date and time: 2017-06-24 03:30:32
Command: UPDATE STATISTICS [InGA].[dbo].[AuditField] [_WA_Sys_00000003_0425A276] WITH FULLSCAN
Outcome: Succeeded
Duration: 00:05:00
Date and time: 2017-06-24 03:35:32

Date and time: 2017-06-24 03:35:32
Command: UPDATE STATISTICS [InGA].[dbo].[AuditField] [_WA_Sys_00000004_0425A276] WITH FULLSCAN
Outcome: Succeeded
Duration: 00:52:31
Date and time: 2017-06-24 04:28:03

I cannot run them with server maxdop=1 since that eliminates all parallel plan generation, and that could hurt the application.  I plan to go the opposite direction and increase it to 8 (there are 16 cores on the box) and see what happens.  May go faster to reduce the length of time the CPU is pegged.  This job runs while users are mostly gone.


texts: Backing up the database without compromising performance

texts:
I have a data base with a size up to 120 Gb and I need do a backup every night.

The problem is that the system needs stand operative all the 24 hours.

In this moments the backup takes up to 2 hours to complete.

How I can do the backup without compromising server performance?

Thanks,
Andrés


texts:
I am very new with Oracle, I have installed 11g 64 bit on my Windows 10. And only started the oracle database using emctl start dbconsole. I have the following code to be connected to my database, which results in the shown exception. I have investigated a lot about this issue on web/stackoverflow/youtube etc, and could not find a straightforward working procedure, would you What is the problem? Please note that the exception happens at the following line:

con = DriverManager.getConnection(url, props);

The code looks like:

    String url = "jdbc:oracle:thin:@localhost:1158:searchengine";

    //properties for creating connection to Oracle database
    Properties props = new Properties();
    props.setProperty("user", "sys");
    props.setProperty("password", "1234");

    //creating connection to Oracle database using JDBC
    try
        {
            //Class.forName("oracle.jdbc.OracleDriver");
            con = DriverManager.getConnection(url, props);
            //con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1158:searchengine", "system", "1234");
            String query = "alter session set nls_calendar='Persian'";
            PreparedStatement pr = con.prepareStatement(query);
            pr.execute();
            query = "select sysdate from dual";
            pr = con.prepareStatement(query);
            ResultSet res = pr.executeQuery();
            while (res.next())
                {
                    System.out.println(res.toString());
                }
        }
    catch (Exception e)
        {
            e.printStackTrace();
            return false;
        }

    java.sql.SQLRecoverableException: IO Error: Got minus one from a read call
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:489)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:208)
    at Controllers.DBController.connect(DBController.java:24)
    at Wrapper.TestMain.start(TestMain.java:21)
    at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$162(LauncherImpl.java:863)
    at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$175(PlatformImpl.java:326)
    at com.sun.javafx.application.PlatformImpl.lambda$null$173(PlatformImpl.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(PlatformImpl.java:294)
    at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
    at java.lang.Thread.run(Thread.java:745)
Caused by: oracle.net.ns.NetException: Got minus one from a read call
    at oracle.net.ns.Packet.receive(Packet.java:311)
    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:300)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340)


texts: Is there a way to truncate table that has foreign a key?

texts: Is there a way to truncate table that has foreign keys ? Delete and reseed can take too long. Is deleting and recreating keys only way ? If so is there a tool that does this ?


texts: Master data services(MDS) upgrade from SQL2016 to SQL2019

texts:
Team,
I have not worked on MDS before and need some advice/inputs.
We are doing an doing an in-place upgrade of MDS (master data services) from SQL2016 enterprise to SQL2019 enterprise. i would like to know the following

Will the existing Model packages created in SQL2016 continue to work on SQL2019 or do we need to redeploy once again on SQL2019?
Any issues or changes needed after doing an in-place upgrade of MDS from SQL2016 to SQL2019.


texts: How could I connect to MySQL database running on WAMP server from other computers in same LAN?

texts:
I'm new in web development. I'm requesting help from all of you. My problem is:

I installed WAMP server in my desktop computer. My desktop computer has a static LAN IP (192.168.0.101).

I'm able to access WAMP server homepage from my other computers connected to the same network by using the LAN IP (192.168.0.101).
But while I was trying to access phpMyAdmin from other computer it says:

  Forbidden. You don't have permission to access/phpmyadmin/ on this server. Apache/2.4.9(Win64) PHP/5.5.12 Server at 192.168.0.101 Port 80.

I also tried to connect to the MySQL database running on my desktop computer using cmd in other computer by the following code, mysql -h 192.168.0.101 -u root -p (there is no password for root user).

Please help me how could I be able to access phpMyAdmin and MySQL database from other computer connected to the same network.


texts:
I have in my MySQL DB a few tables
Most of the time, I'm only doing INSERT in my db. I have up to 4 connections to the DB, each one inserting into a different table. There cannot be two connections inserting data in the same table at the same time.

In one of my table, I'm doing requests like : INSERT INTO ... VALUES <1000 tuples> ON DUPLICATE KEY UPDATE < do some basic operation on columns which are not in the PK> .
This table does not have any index, it only has on PK on 2 columns. The row format is fixed (only fixed size column). The table is using MyISAM, and has about 3 million rows (The total size of the table is about 400Mb, and the PK takes about 40Mb.)

Most of the time, the UPDATE part of the query is applied, since we may have a lot of lines already existing in the DB in our queries.

My problem is that doing an insert for 10000 rows take about 10s, which is a real problem for my application.

I tried to OPTIMIZE the table but it didn't change anything.

Here are the config parameters I changed:

key_buffer_size = 2G
max_allowed_packet = 64M
table_open_cache = 256
Binary logs are disabled (it's not

I put this big key_buffer_size since other tables might be using it too.

So, do you guys have any suggestion about how to fix that problem ?

If you need more infos, plz tell me :)

Thanks !

Edit: I'm using JDBC to run my queries, is there something specific to my case to enable in jdbc parameters ?


texts: Composite primary key plus a separate (surrogate) id column for foreign key references

texts:
I am trying to build a database about locations like so:

Country → Region → Town

Countries will be populated with a list of pre-defined worldwide countries, regions & towns are input by users.
Region may not apply to every location and so a blank string will be allowed (hence the many to many link table).
This is the best design I have come up with:

The town_id column is set to AUTO_INCREMENT & UNIQUE. It is then used as a foreign key reference in other tables.
The town column on its own is not UNIQUE as you may have the same town name in multiple places.
Is the towns table correct?
The reason I ask is because it just seems odd to me to have the town_id as the reference, however this cannot be included or used as the primary key (because if it did, it would cause duplicates)?
I would welcome feedback if this approach is correct.


texts: Task generated T-SQL shows unknown property IsMemoryOptimized

texts:
I have modified a Maintenance plan on SQL Server 2008 using SSMS 2014, particularly Reorganize index task - I have added another two databases for this task.

When I wanted to check if the T-SQL is OK, the View T-SQL button shows the error:

  unknown property IsMemoryOptimized

Could (should?) that be fixed somehow?


texts:
I am trying to setup an alert where the application team can get the notification on their event viewer for any queries which are running longer than 2 minutes. This is what I have so far. For some reason, the application team is not receiving the alert format which they are expecting(spid number,and the exact message text which is in the store proc) Can someone suggest what I am missing here? The time threshold is being passed as 2 minutes from the SQL Agent

create PROCEDURE [dbo].[usp_long_running_Query]
@timeThreshold time --  '00:02:00.0000000'
AS
    BEGIN
        DECLARE @tmpTime time
        DECLARE @dbname varchar(20)
        DECLARE @vmsg varchar(2000)
        DECLARE @vstatus numeric
        DECLARE @vstrstatus varchar(100)
        DECLARE @spid varchar(10)
        declare @hostName varchar(30) = CONVERT(nvarchar(30),SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
        declare @Date nvarchar(25) = CONVERT(nvarchar(25),GETDATE())
        DECLARE @svr varchar(30) = @@SERVERNAME
    SELECT TOP 1
        right(convert(varchar,dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 121), 12) as 'batch_duration', DB_NAME(qt.dbid) as 'db_name',P.spid
    INTO #queryTime
    FROM master.dbo.sysprocesses P
        INNER JOIN sys.dm_exec_query_stats b
            ON p.sql_handle = b.sql_handle
        CROSS APPLY sys.dm_exec_sql_text(b.sql_handle) as qt
        LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
    WHERE P.spid > 50
        AND P.status not in ('background', 'sleeping')
        AND P.cmd not in ('AWAITING COMMAND'
                        ,'MIRROR HANDLER'
                        ,'LAZY WRITER'
                        ,'CHECKPOINT SLEEP'
                        ,'RA MANAGER')
        AND DB_NAME(qt.dbid) = 'xxx'
        AND (P.loginame like 'xyz%'
                OR P.loginame like'xyz%'
                OR P.loginame like'xyz%'
                OR P.loginame like'xyz%'
                OR P.loginame like'xyz%'
                OR P.loginame like 'Ipa%')
        --AND o.name = 'usp_OpSec_getScreeningComplianceSummaryMvmDetail'
    ORDER BY batch_duration DESC

    SET @dbname = (SELECT db_name FROM #queryTime)
    SET @tmpTime = (SELECT batch_duration FROM #queryTime)
    SET @spid = (SELECT spid FROM #queryTime)
    SET @vstatus = (SELECT db_status FROM Dxxx.dbo.TDBSTATUS WHERE db_na = @dbname)
    SET @vstrstatus=CAST(@vstatus as varchar(100))

    IF @tmpTime > @timeThreshold
        BEGIN
            SELECT @vmsg='AlertType=DBMaint Host='+@hostName+' Instance='+@svr+' Date='+@Date+' MSG= Error: 82192 The database ' + @dbname + ' has long running queries > 2 min. Please monitor this database.  The status is ' + @vstrstatus  + '.' + ' The query has been running for ' + CAST(@tmpTime as varchar(50)) + '. The spid of the longest running query is ' + @spid
            INSERT INTO Dxxx.dbo.TDBMSG(DB_NA,MSG_TXT,SEVERITY) VALUES (@dbname,@vmsg,'FATAL')

            SET @vmsg = 'AlertType=DBMaint Host='+@hostName+' Instance='+@svr+' Date='+@Date+' MSG= Error: 82192 The database ' + @dbname + ' has long running queries > 2 min. Please monitor this database.  The status is ' + @vstrstatus  + '.' + ' The query has been running for ' + CAST(@tmpTime as varchar(50)) + '. The spid of the longest running query is ' + @spid
            RAISERROR (82192,17,1,@dbname,@vstrstatus,@vmsg)with nowait
            DROP TABLE #queryTime
        END
    ELSE
        BEGIN
            DROP TABLE #queryTime
        END
END
GO

The expected alert should be something as mentioned below.  Please note DB mail is not being used, when raiseerror happens, the alert gets pushed into centralized server and from there the app team receives it in their event viewer

Error: 82192 Severity: 17 State: 1 AlertType=DBMaint Host=xxxx Instance=Cxxxxx Date=May 20 2019  9:45PM MSG= The database has long running queries > 2 min. Please monitor the database.


texts:
I saw a Tuple Relational Calculus formula, and it contained the symbol <>

I couldn't find any reference to it anywhere - can anyone tell me what this means?
(perhaps this question will be a future first Google result)


texts:
My company is starting a new initiative aimed at building a financial database from scratch.

We would be using it in these ways:

Time series analysis of: a company's financial data (ex: IBM's total fixed assets over time), aggregations (ex: total fixed assets for the materials sector over time), etc.
Single company snapshot: various data points of a single company
Analysis of multiple companies across multiple data fields for a single time frame, usually the current day.
Back-testing, rank analysis, data analysis, etc. of ideas and custom factors.

Approximate breadth of data:

3000 companies
3500 data fields (ex: total fixed assets, earnings, etc.)
500 aggregation levels

Periodicity: daily, monthly, quarterly, annual

20 year look-back that would grow over time

Question: In our PostgreSQL database, what schema should we use? Right now I am thinking one time series table per company, per category of data field for the fully normalized DB. For example, one table for, say, all the balance sheet fields for IBM, another table for IBM's cash flow items, etc for all categories of data and for each company. Timestamps as records and data fields as columns/fields. Then for fast queries, create a warehouse and views, etc. that are not fully normalized but optimized for queries for my use-cases listed above. However, if you look at my number of companies and fields above, I will probably end up with more than 200,000 tables for just my base financial data if my tables are pretty wide, which isn't great either. That's a lot of tables, but I don't see another good way to do it.

If there is a better place to ask this question, please let me know.

If you need more information, I am happy to edit my question and add it.

PS - I asked a similar question on the SO Quant site, but didn't get much schema help. Also, non-schema focused answers are okay, but note that I am looking for help with schema design here.


texts: Which one is created first? Database or Instance?

texts:
Forgive me I just learned Oracle. There exist so many terminologies in the Oracle world.

Regarding the concept of database and instance. I already know some differences between them.

The Oracle database includes all the physical files belonging to the server while the Oracle instance will provide the interface between the user and the data he wants to manipulate.

But I want to know which one is created first?

I had thought the database should be created first, before the instance. But after I read this post Creating a Database with the CREATE DATABASE Statement I think I am.

Because the manual shows theses instructions:

Step 5: (Windows Only) Create an Instance
Step 9: Issue the CREATE DATABASE Statement

Please help me to clarify it.


texts:
I have turned on notifications via email on my SQL Server Agent if any of my scheduled jobs failed or is successful.

Can anyone help me if I want to set up Alerts, if in case one of the scheduled job is running longer than expected.  Best is probably thru SSMS.


texts: Improve MySQL query efficiency for first row in a group

texts:
I've written the query below in MySQL to get the top 10 top landing pages across all browser sessions.

Reading other similar posts about how to access the first row in a group, it seemed like the solution was the following:

SELECT MIN(`created_at`) AS `created_at`, `session_token`, `url`
FROM `session`
GROUP BY `session_token`;

This produced incorrect results and I found that while using MIN() to get the first record in a group, it only applied to the specified column and that other columns could be picked from other rows within the group.

I amended the query to the one below which produces the correct result:

SELECT `b`.`created_at`, `b`.`session_token`, `b`.`url`
FROM (
    SELECT MIN(`created_at`) AS `created_at`, `session_token`, `url`
    FROM `session`
    GROUP BY `session_token`
) a
INNER JOIN `session` b USING (`session_token`, `created_at`);

I've created the solution below that produces the correct results, however it is now using two subqueries.

SELECT `c`.`url`, COUNT(*) AS `hits`
FROM (
    SELECT `b`.`created_at`, `b`.`session_token`, `b`.`url`
    FROM (
        SELECT MIN(`created_at`) AS `created_at`, `session_token`, `url`
        FROM `session`
        GROUP BY `session_token`
    ) `a`
    INNER JOIN `session` `b` USING (`session_token`, `created_at`)
) AS `c`
GROUP BY `c`.`url`
ORDER BY `hits` DESC
LIMIT 10;

I've only tested it on a small dataset and it doesn't seem particularly fast. Could it be improved to increase efficiency?


texts: Can we export Selected Row with Subquery using data pump

texts:
I am trying to export selected rowS from my database using SUBQUERY but I got error.

expdp directory=exp_data dumpfile=selected_row.dmp logfile=rows.log tables=sample,super query="where name IN (select name from azeem.sample where name like 'Ten');"

Export: Release 11.2.0.4.0 - Production on Tue Jan 12 21:58:37 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39035: Data filter SUBQUERY has already been specified.

Can we use subquery in datapump?


texts: How to retrieve missing values of an id from a list?

texts:
have a data structure that is value/tag inside a table on Oracle sql
Lets say I have this structore
exTable

ID
TAG
VALUE

1
1
x

1
4
y

2
2
x

2
3
y

2
4
z

I want to retrieve the missing tags for a list of values I have, for example [1,2,3,4,5]
In this case Im searching for this as an output if I look for (1,2)

ID
MISSINGTAG

1
2

1
3

1
5

2
1

2
5

Been trying with temp tables and joins but im still missing something because when i try to join I either dont get anything or just get the ones that do exists, I achieved want I want for one id using a minus
select column_value as COL_NAME
from table(sys.dbms_debug_vc2coll('1',
'2',
'3',
'4',
'5'))
MINUS
SELECT TAG FROM exTable WHERE ID='1';
But for doing it for a list of IDS im have not had luck
If im duplicating the question let me know


texts: mysql innodb space x did not exist in memory

texts:
Into my innodb log I got the errors below. How to fix?
What did it mean? Some tables are corrupted but not all.

InnoDB: space id 1753 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/eav_attribute,
InnoDB: space id 1777 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/catalog_eav_attribute,
InnoDB: space id 1626 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/catalog_category_entity_int,
InnoDB: space id 1609 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/catalog_category_entity_text,
InnoDB: space id 1610 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/catalog_category_entity_varchar,
InnoDB: space id 1611 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/customer_eav_attribute,
InnoDB: space id 1746 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/customer_eav_attribute_website,
InnoDB: space id 1747 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/customer_form_attribute,
InnoDB: space id 1754 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/eav_attribute_label,
InnoDB: space id 1779 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/eav_attribute_option,
InnoDB: space id 1780 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/eav_attribute_option_value,
InnoDB: space id 1781 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/eav_entity_attribute,
InnoDB: space id 1784 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/eav_form_element,
InnoDB: space id 1792 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/googleshopping_attributes,
InnoDB: space id 1804 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/salesrule_product_attribute,
InnoDB: space id 1516 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/googlebase_attributes,
InnoDB: space id 1798 did not exist in memory. Retrying an open.
130906 16:31:27  InnoDB: error: space object of table test_magento/eav_attribute_set,


texts: Can there exist Tables with no Non-Prime Attributes? If so, are they always normalised to 3NF?

texts:
Can there exist a table with no non-prime attributes?
Here is an example of a table which I believe has no non-prime attributes:

I came to this conclusion because the list of attributes in the relation is [id, tag_desc_id, name] and the list of candidate keys is [{id}, {tag_desc_id, name}]. There are no attributes in the list of attributes that are in none of the candidate keys. Thus, there are no non-prime attributes.
Is my reasoning correct?
If so, is it normalised to 3NF?
The criteria for 3NF is:

2NF

Every non-prime attribute of R is non-transitively dependent on every key of R

There are no non-prime attributes, thus 3NF is satisfied
The criteria for 2NF is:

1NF

Every non-prime attribute is (...)

Again, there are no non-prime attributes.
Is this true generally?
For any table, assuming the table has a primary key id as above (and so 1NF is satisfied), and the table has no non-prime attributes, is it always the case that the table is normalised to the third normal form (3NF)?


texts: How can I lock rows FOR UPDATE without returning data over the connection?

texts:
I want to do something like this:

begin;
select * from foos where owner_id=123 and unread=true order by id for update;
update foos set unread=false where owner_id=123 and unread=true;
commit;

The goal is to avoid deadlock when two processes execute the UPDATE simultaneously. Problem described more here: Why am I getting a deadlock for a single UPDATE query?

In the statement where I acquire the lock, I don't need any info about the rows. I just want to lock those particular rows. Is there a way to do this (elegant or hacky) which tells postgres to not do any of the work of actually giving me the data?


texts:
I have a common product table in db1 and db2. I want to make it one table because it has one inventory (stock of products). What is the best solution to share product table among two different database? I have few thoughts:

Writing trigger on update/delete/insert one table and update another same time.
Creating view on product table and used in second db (I don't know how)
replication (I don't know how)

Can you guide me on the best way?


texts:
I would like to know if there is a big dig difference between Intel and AMD chips in terms of how sql behaves?  I've been told that their is a big difference between the chips depending on application.  Is there any way I can test or someone can advise me on how I can go about troubleshooting this?

Thanks


texts: List all articles with their amounts at a certain date+time


texts:
I have been asked a question related to database constraints. I am familiar with Oracle DDLs and DMLs and know how to define FK, PK and standard constraints related to relations / tables using DDL.

What I am not so sure of was when I was asked to present constraints formally using Relational Calculus.

So my question is:

Can someone provide an example of a simple constraint expressed formally using Relational Calculus?


texts: Secondary mysql instance runs but mysqld_multi reports it is "not running"

texts:
On my Ubuntu server, there are two mysql instances running at the same time.

A default, normal mysql instance
A secondary mysql instance running with mysqld_multi.

The command sudo service mysql status correctly states the mysql service is active.

I want to check the secondary instance's status so I use this command :

sudo mysqld_multi report

...and I get :

MySQL server from group: mysqld1 is not running

which seems pretty inconsistent since I can connect to this mysql instance from my DB client.

Why does mysqld_multi tell me the instance is not running ?


texts: How to debug deadlocks in  SQL Server 2008 R2?

texts:
I'm using SQL Server 2008 R2 that automatically lock database items based on my queries.

Sometimes this deadlocks - two threads with different queries never ends.

Is there any way to debug how SQL Server locks and unlocks resources? I have tried SQL Trace but it has no information about resources locks.


texts:
I have created 3 jobs in my MS SQL Server

The job returns an error on 2. Remove top 4 lines

  Message
  Executed as user: myDomain\administrator. The process could not be created for step 2 of job 0xA849BBB21D41B64387336C2CAA7FB632 (reason: The system cannot find the file specified).  The step failed.

The job is a CMD script that works fine in CMD window

more +4 "D:\export\BRANCHES.XML" >"D:\export\BRANCHES2.XML"

Also, my third job is not working either. It wont change replace the text.

Here is the code

(Get-Content D:\export\BRANCHES2.XML) | Foreach-Object {$_ -replace '(1 rows(s) affected)','MyValue'}  | Out-File  D:\export\BRANCHES3.XML

Both the directory and the file exists on D:\export


texts: Transaction log changes not synchronizing with AlwaysOn

texts:
SQL Server 2012 Enterprise in an AlwaysOn configuration.  The Availability Group for this specific DB is synchronized and healthy.

I had to add a second transaction log file (logfile2) to the primary.  The file did not replicate until I restarted the instance (was doing other maintenance).
After the instance was restarted the transaction log appeared at the primary and secondary.  12 hours have passed since the instance was restarted and the log files show the same time/datestamp AND file size.  Good so far ...

It appears however that transactions hitting logfile2 on the primary are not being synchronized to the secondary for over 20 minutes.  In addition to the normal transaction log backup that runs every 2 hours, which includes a CHECKPOINT command, I had to install a SQL agent job that performs a
CHECKPOINT on the DB every 15 minutes.  The reasoning is that transactions going to logfile2 are languishing there and not getting applied to the database; so, I force the flush of the log files to the database, which then synchronizes to the secondary.

Can anyone shed some light on this behavior?
(Please hold your comments on having multiple log files.  Unless you've walked a mile in my shoes ...)


texts: Part of statement missing from sys.dm_exec_query_stats

texts:
I'm trying to debug the individual parts of a large query; I'm using the statement offsets in sys.dm_exec_query_stats to separate them. There is one particularly troublesome statement that for some reason is not present in sys.dm_exec_query_stats; it is, however, present in sys.dm_exec_requests. Is this expected behavior? Can I not get the query stats for that substring because it's currently executing? To be specific, this returns the executing part of the query:
select * from sys.dm_exec_requests where statement_start_offset = 29466

But this does not:
select * from sys.dm_exec_query_stats where statement_start_offset = 29466

This is a production system and installing third-party tools or scripts is something I want to avoid unless absolutely necessary. Just looking to see if someone has thoughts about what could be going on with the DMVs here.


texts: Upgrading Mongodb from 3.2.6 to 4.2. Which is the better way to do it?

texts:
What is the best way to upgrade MongoDB?

Say I have 3.2.6 and I want to upgrade to 4.2

Can I just use mongodump and mongorestore? Or are there any intricacies to it?


texts: Using of OPTIMIZE FOR hints in a sql query

texts:
as I know, OPTIMIZE FOR hint helps to avoid parameter sniffing.

For such a stored procedure:

SELECT * FROM Tabele WHERE param=@param1

We have two parameter S1 with high selectivity and s2 with low selectivity.

If we are using such a hint:

OPTION(OPTIMIZE FOR @param1=s1)

and then send s2 to stored procedure, we have still parameter sniffing.

Now I have a conceptual question!:

How doe's OPTIMZE FOR hint help us actually to avoid parameter sniffing?


texts: Fetching user info from many databases at one go

texts:
I have a requirement to fetch all the schema available in 100+ Oracle instances running in RHEL environment.

All the schema information such as username, roles assigned and is to be dumped in one place from where it will be used for other purposes.

Is it feasible? I can provide more info if this question is sounding vague.

Are there any utilities to perform this?


texts: Efficiently get first 10 results and total number of results

texts:
For a search page, I need the first 10 results and the total number of results of a query.

That part is easy:

SELECT TOP(10) ... FROM ... WHERE ... ORDER BY ...
SELECT COUNT(*) FROM ... WHERE ...

But it's also amazingly slow. Both statements take about the same time.

So I found this one:

SELECT TOP(10) ... FROM ... WHERE ... ORDER BY ...
SELECT @@ROWCOUNT

That cuts down the time by ~50%. Great. But unfortunately the rowcount returned is 10. If I remove the "TOP(10)" the rowcount is correct, but the timespan is more or less the same as in the original version. (Even though I only access the first 10 rows.)

That's frustrating, because the total-number-of-lines info must exist, even in the TOP(10) version. (At least I think so, because we use a very complex ORDER BY clause, with 3 "CASE" conditions to sort empty data to the bottom)

Is there an efficient way to get both? TOP(10) data and total number?


texts: Multiple Availability groups under same IP

texts:
Is it possible to have 4 Availability Groups distributed on 3 servers all of them under the same IP?

For example I would like the users to connect to DATABASE01 on SQLSTACK01 no matter if the Database is in the server 1 or server 3.

Thanks!


texts: My Galera Cluster does not connect across AWS regions

texts:
I am building a MariaDB Galera Cluster, on AWS, EC2. Using MariaDB 10.5 and Galera 4.
I have two nodes in the California region, named galera_ca and galera_ca2, and a third node in Oregon name galera_or
The security groups between the servers are wide open, and the ping time between OR and CA is 12ms
I started galera_ca using galera_new_cluster
I started galera_ca2 using systemctl start mariadb

The two California nodes connect and share data just fine.
wsrep_cluster_size indicates that I have two nodes in the cluster.

I started galera_or using systemctl start mariadb

Oregon then hangs on starting for a very long time, and wsrep_cluster_size shows three nodes, but the server cannot be connected to.
Eventually systemctl start mariadb returns this message:
Job for mariadb.service failed because a fatal signal was delivered to the control process. See "systemctl status mariadb.service" and "journalctl -xe" for details.
systemctl status mariadb.service doesn't show anything exceptional,
journalctl -xe shows:
requested state transfer from 'any', but it is impossible to select State Transfer donor: Resource temporarily unavailable
wsrep_cluster_size still shows three nodes, but mariadb is not running in OR.
If I try to shut down either of the California servers systemctl stop mariadb it never returns.
All three servers have basically the same configuration:
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so

wsrep_node_name='galera_ca'
wsrep_node_address="INTERNAL ADDRESS OF THIS NODE"

wsrep_cluster_name='galera-experiment'
wsrep_cluster_address="gcomm://EXTERNAL ADDRESS OF NODE CA, EXTERNAL ADDRESS OF NODE CA2, EXTERNAL ADDRESS OF NODE OR"

wsrep_provider_options="gcache.size=300M; gcache.page_size=300M"
wsrep_slave_threads=4
wsrep_sst_method=rsync

binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

If I try just one CA node and the OR node, the result is the same.


texts: Oracle shrink space release space to operational system in one step?

texts: Does oracle release space to operational system when is issued alter table ... shrink space (one step) or just after move HWM (two steps) alter table ... shrink space compact?


texts: Linked Server - Double Hop - Kerberos - TCP

texts:
I am having trouble with a Linked Server.

I would typically not use a linked server, but legacy issues are forcing my hand.

I am attempting to set up a linked server between two new production SQL servers. I have configured SQL on both machines with domain service accounts running MSSQLSvc on each respective SQL instance.

The domain service accounts have SPNs registered;
I have verified this through AD attribute editor(sp?). I can perform single hop queries on the actual servers, but no luck with the double hop from my laptop (domain admin).

I have used the Kerberos Config tool on each server and configured what I can.

This is where google falls short on this issue. When I use the Kerberos Config Tool on one of the servers I get this for my service account running MSSQLsvc:

TCP must be enabled to use Kerberos authentication.

In the SQL server config manager, I have TCP/IP enabled.

I have restarted services and the server on several occasions.

Any thoughts? I can provide more information.


texts: ERROR 3680 (HY000): Failed to create schema directory 'wwww' (errno: 2 - No such file or directory)

texts:
I'm trying to create a database using MySQL but getting an error message as follows:

mysql> create database XXX
    -> ;
ERROR 3680 (HY000): failed to create schema directory 'XXX' (errno: 2 - No such
file or directory)


texts: Compare table content from different PostgreSQL 9.3 DBs

texts:
The problem

How do I efficiently and idiomatically compare the content of two tables, each in a different DB?

I have 3 DBs (the classic dev, stg and prod). Each DB has a few tables which should be exactly the same regardless of environment, like countries.

I would like to write a script that would compare these tables and alert me when there's a mismatch.

Characteristics

There are less than 10 tables to compare
The tables are not very large - at most ~100K of data
The script is executed from a Jenkins CI machine

Current solution

Currently, I am using pg_dump and comparing the files. I wonder if there is a more idiomatic approach.


texts:
We are running the backup from the replication server and it is working good, since we are not facing any other issues, some information for better understanding our strategy:

Backup Type: Full Backup
Server : MySQL 5.5
Backup Tool: mysqldump
Interval: Every 24 hours.
Data size at present : 2 TB.

Actually we are in need to store the back up of 13 months but we have only 2 months. The data is growing around 6% in every month. We are running out of Storage.

Recently, we thought of moving into mysql incremental backup. The decided plan is we need to run full backup on every week interval and incremental backup at every 24 hours.

Our main concern is storage, since our data is growing every month, I need to know the answer for following questions.

What is the best possible solution for backup and storing the data considering our situation?
Whether the incremental backup strategy will solve our storage issue?
Is there any other better way to handle this situation?

Any suggestions welcome, thank you in advance.


texts: SQL Server - backup restore - lose indexes?

texts:
It looks like at a client site, after a database backup and restore (from SQL Server 2008 to SQL Server 2012), it looks like they have lost indexes from the tables and we had to manually create them. We have seen two instances of this.

I thought backup and restore keeps all the objects intact. Is this possible? Is there a setting that actually lets users omit objects during a restore?


texts: DAC Connection using the SSMS for executing the code

texts:
Can anyone help me on below . I am trying to execute the SQLCMD with DAC (-A switch) but unable to do so .

How can I do it both ways below :

Execute in SSMS Query Pane
Execute using PowerShell

Below is what I have tried with no luck .

EXEC master.dbo.xp_cmdshell "SQLCMD -S SQLServer -d Db1 -A
go
select count(*) AS FileCount from TableName
go"

output

  'select' is not recognized as an internal or external command,
  operable program or batch file. NULL

Message

  Msg 103, Level 15, State 4, Line 1 The identifier that starts with '

SQLCMD -S SQLServer -d Db1 -A
go
select count(*) AS FileCount' is too long. Maximum length is 128.


texts: SQL Server's Equivalent in MySQL's binary log

texts: Is there any MySQL's binary log equivalent in SQL Server.If yes, how can we retrieve data changes(with data) from that log and store it in a table or in a file? I cant enable CDC since I am using SQL Server 2012 Standard Edition. Kindly suggest.


texts:
I recently updated a SQL server to the current version. I have major performance issues with some queries for example this query takes 8 seconds with legacy CE:
But 5 minutes with CE 150:
https://www.brentozar.com/pastetheplan/?id=Bkj1RbGrP
I updated statistics for the whole DB and ran a full scan update to the tables in this query.
Any idea how can I fix this performance issue?


texts: SQL Anywhere 12 - exporting data to Excel

texts:
Do I need to install MS Office Excel on machine where is SQL Anywhere 12 server for below code to work?

Or SA 12 or Windows have MS Excel Driver builtin?

SELECT * FROM SalesOrders;
OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls);
DBQ=c:\\test\\sales.xls;
READONLY=0' INTO "newSalesData";