texts: get table does not exist in mysql after adding new hard disk

texts:
I have added a new hard disk to my VM where my mysql server were located on. Suddenly, mysql returns table does not exist for all my databases; even though I can see all tables with show tables command.

mysql> use mydb;
mysql> show tables;
+-----------------------+
| Tables_in_mydb        |
+-----------------------+
| mytable               |
+-----------------------+
1 row in set (0.00 sec)

mysql> select * from mytable;
ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist

mysql> select * from mydb.mytable;
ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist

I have tried to restore tables with solutions provided here:

Using mysqlcheck --repair:

root@mylab:/var/lib/mysql# mysqlcheck -umyuser -pmypass --repair --
databases mydb
mydb.mytable
Error    : Table 'mydb.mytable' doesn't exist
status   : Operation failed

Using REPAIR TABLE:

mysql> use mydb;
mysql> REPAIR TABLE mytable;
+---------------------------+--------+----------+-------------------------------------------------+
| Table                     | Op     | Msg_type | Msg_text                                        |
+---------------------------+--------+----------+-------------------------------------------------+
| mydb.mytable              | repair | Error    | Table 'mydb.mytable' doesn't exist              |
| mydb.mytable              | repair | status   | Operation failed                                |
+---------------------------+--------+----------+-------------------------------------------------+
2 rows in set (0.00 sec)

Using ALTER TABLE t1 ENGINE = InnoDB;:

mysql> use mydb;
mysql> ALTER TABLE mytable ENGINE = InnoDB;
ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist

Using mysqldump:

root@mylab:/var/lib/mysql# mysqldump -umyuser -pmypass mydb > /root/mydb.sql
mysqldump: Got error: 1146: Table 'mydb.mytable' doesn't exist when using LOCK TABLES

I have tried repairing with these solutions. Since mysqlcheck had failed before, I tried myisamchk for all files starting with mytable in /var/lib/mysql/mydb/; the command output was is not a MyISAM-table for all of them.

EDIT 1:

I checked database management files in /var/lib/mysql/ which is set as datadir in /etc/mysql/my.cnf; all of them were listed there:

root@lab-188:/var/lib/mysql# ls -lat | grep ib
-rw-rw----  1 mysql mysql  5242880 May 24 12:22 ib_logfile0
-rw-rw----  1 mysql mysql 18874368 May 24 12:22 ibdata1
-rw-rw----  1 mysql mysql  5242880 May 24 11:08 ib_logfile1

EDIT 2:

I also tried following solutions:

Restart mysql service:

Still get the same error.

Fix mysql directory's permissions:

chmod -R 755 /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/

Nothing changed.

Try loading database files in another server:

I copied the whole directory of database, /var/lib/mysql/mydb/, to another server which has mysql too, restarted mysql there and logged in to it.

The database was shown there, but selecting content of its tables returned same error as previous server:

mysql> select * from mytable;
ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist

EDIT 3:

Another solution which I found here, was to disble/enable FOREIGN_KEY_CHECKS which didn't help in my case:

SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;


texts: MSDTC on SQL Server 2012 with mount point for data disk

texts:
I have installed a new SQL Server 2012 instance on a cluster. The installation went fine but when I've added the MSDTC as resource of the instance, it went wrong. When I just added it, the MSDTC was online but when I tried my first failover, it refused to go online. The error message was "MSDTC failed".

My configuration is:
a physical drive disk (let say J:) with two mount points "DATA" and "LOG".

I read some people who experienced this kind of errors with mount points and SQL Server 2008 but nothing with 2012.

I tried to create the "MSDTC" folder on the physical drive (the J: one) but nothing better.

Does anyone know something about it or know how to configure the MSDTC and force it to use the J: drive?


texts: Postgres hstore check if a key exists and does not match value

texts:
I have a hstore field in an audit table that stores all the fields that have changed as a result of an operation.

On inserts, the key, updated_by in changed_fields is NULL and on system updates it is set to system. I would like to return all rows where either the key is not defined or the key is not system but I am lost as to how to do this.

So far I have tried

select changed_fields -> 'updated_by'
from audit.logged_actions
where (changed_fields -> 'updated_by' != 'system'
       or defined(changed_fields, 'updated_by') = false)
order by event_id desc

But that doesn't work and I am not quite sure why. Any ideas on what I am doing wrong?


texts: How to call an Oracle Function with multi out Parameters though LINKSERVER

texts:
I need help as I am facing this problem. when I pass 2 output parameter to a function through a linked server I am getting this error. For 1 out parameter its working fine but when I use two output parameters it gives this error:

  “Msg 7215, Level 17, State 1, Procedure Function_Name, Line 28 Could not execute statement on remote server ‘linkserver_name’.”

Here is the query:

DECLARE @RET INT

EXECUTE (‘BEGIN ? := package_name.function_name(?,?,?,?,?,?,?,?,?,?,?,?,?); END;’, @RET OUTPUT
, @USER_NAME, @FIRST_NAME, @MIDDLE_NAME, @LAST_NAME, @DATE_OF_BIRTH
, @MOTHER_MAIDEN_NAME, @MOBILE_NO, @P_NATIONAL_ID, @NATIONAL_ID_EXP_DT, @PASSPORT_NO, @PPT_EXP_DATE
, @ERROR_CODE OUTPUT, @ERROR_DESCRIPTION OUTPUT )
AT linkserver_name;


texts: What's the fastest way to replicate data?

texts:
I have a mongodb database data on my computer. I want to upload that into amazon.com server.

The bottle neck is the internet connection.

One way is to zip the file and then upload it to the server. But what about if after that
I want to synchronize the server data with current data?

Another is to set the mongo in server as slave of the data in my computer. However, my computer is a home computer that connect to the internet via router. So how do the amazon server knows the IP of my computer? The IP is fe80::6df8:c5f9:bdb5:b9e2%15

It seems that it's the IPv6 IP. I do not know the IP4 IP.

So how do I turn a server in my computer to become master? How do I set the router to pass on connection?

Then there is mongodump. Is it fast?

Which one do you usually use?


texts: Getting collation confict in IF statment and need help resolving it please

texts:
A SQL script I am writing is generating the error:

  Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP850_CI_AS" in the equal to operation.

I am not familiar with this error and don't know how to deal with it. I have been able to determine it is caused by the last IF check in the script as once I remove it the script runs fine. Can anyone help me figure out how to fix this issue? Here is my current code:

DECLARE @Source varchar(25);

SET @Source = 'DOD';
/*
Source options are:
    DOD
    SPRAGUEENERGY
    SRAPROD
*/

IF (@Source = 'DOD')
BEGIN
    USE DOD;

END

IF(@Source = 'SPRAGUEENERGY')
BEGIN
    USE SPRAGUEENERGY;

END

IF (@Source = 'SRAPROD')
BEGIN
    USE SRAPROD;
END


texts: Amazon Postgres RDS: Update pg_attribute to update my custom attribute

texts:
I want to update my attribute to change its length. I am getting

Error: Permission denied for relation pg_attribute.

My SQL Query is like this:

DO $$
BEGIN
UPDATE
    pg_attribute
SET
    atttypmod = 5+4
WHERE
    attname LIKE '%my_attribute%'
    AND atttypmod = 1 + 4;
END $$;

I have set search_path to my user, my user has the usage on schema pg_catalog. Is this possible? Because if not, we would have to drop the idea of RDS DB and move to ec2-instance and creating our own postgres db

UPDATE:

Granting permission with this command:

GRANT UPDATE ON pg_attribute TO rds_superuser;

Result is:

WARNING:  no privileges were granted for "pg_attribute"
Query returned successfully with no result in 41 msec.


texts:
My syntax keeps giving me the below error, which is blowing my mind as I think (and please kindly correct me if I am incorrect), I have declared and set this variable above.

  Msg 137, Level 15, State 2, Line 1
  Must declare the scalar variable "@id".

Here is my syntax, and if I include a print @id statement the proper value will be output, however I still get the above error?!

Create Table #temphold
(
  dateadded datetime
  ,dateupdated datetime
  ,id varchar(100)
)

Declare @id varchar(100), @sql varchar(max)
Set @id = '12345'

set @sql = 'insert into   #temphold(dateadded,dateupdated,id) '
          +'select   getdate(),getdate(),COALESCE(@id,'''') '
PRINT @SQL
EXEC(@SQL)

Drop Table #temphold


texts: MySQL Index creation failing on table is full

texts:
UPDATE: tl;dr: The problem was MySQL uses the TMPDIR when creating indexes. And my TMPDIR was the one running out of disk space.

Original Q:

I'm trying to add an index to an InnoDB table, and getting a table is full error.
I have enough disk space and the MySQL configuration has a file-per-table=1. The table data is 85GB and I assume the index will be around 20GB - 30GB and I have much more disk space than that. I'm also using ext3 so I don't feel there's any problem with the file size limit from the OS point of view.

The logged error looks like this:

140616 13:04:33  InnoDB: Error: Write to file (merge) failed at offset 3 1940914176.
InnoDB: 1048576 bytes should have been written, only 970752 were written.
InnoDB: Operating system error number 0.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 0 means 'Success'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
140616 13:04:33 [ERROR] /usr/libexec/mysqld: The table 'my_table' is full

What is causing this and how can I resolve?

The create table:

`CREATE TABLE `my_table` (
 `uid_from` bigint(11) NOT NULL,
 `uid_to` bigint(11) NOT NULL,
 `counter` int(11) NOT NULL,
 `updated` date NOT NULL,
 PRIMARY KEY (`uid_to`,`uid_from`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8`

As is the Data is 87.4GB and I estimate there are around 1.5B rows.

SHOW GLOBAL VARIABLES LIKE 'tmpdir';
Variable_name   Value
tmpdir  /tmp

[root@web ~]# df -h /tmp
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       40G   24G   14G  64% /


texts:
I am looking to setup a replica for OLAP queries. Currently in my prod I have a master (OLTP node) replicating to a slave via streaming replica. I don't want to use streaming replica for OLAP queries because I would probably need different set of indexes and permissions on OLAP db. So I am planning to setup a pglogical replica.

Will there be any performance hit on master because of this?

Also I am planning to have master replicating to both a streaming replica (for HA) and a pglogical replica (for OLAP). Is it possible?

Any other implications/side-effects I should be worried about?


texts: Why do OLAP cubes perform better than Relational databases on certain tasks?

texts:
My team uses a Multidimensional OLAP database for a lot of it's analytics and predictive modeling. The standard line in all the documentation is "OLAP cubes are better suited for this type of application than relational databases".

On the other hand, you frequently hear that a relational database with a star or snowflake schema (sometimes called ROLAP) can do anything that a true OLAP database can do.

Why is it exactly that OLAP is better for analytical processing, aggregating, etc... than the relational model?
Is it true that ROLAP can do anything that a true OLAP can do, or are there still limitations or performance issues that can only be solved by using a true OLAP db?


texts: impdp - ORA-39000: bad dump file specification

texts:
While importing a dump file using the command:

impdp manipur/manipur file=D:\ManipurCBSdump\MICB_TABLES.dmp full=yes log = implog.txt

The following error arises:

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39088: file name cannot contain a path specification


texts: SSAS Tabular Model or SQL Relational Database for new Datawarehouse? Is SSAS still a good to learn technology?

texts:
I am in the process of creating a data warehouse using SQL Server for my company.
I have created a POC with a simple SQL Relational database with a few(about 10) stored Procs for the ETL process.
Now that I have business buy in to step it up to a production state, where I will be tripling the data, it's the right time to ensure the technology I use is best suited for our needs and is aligned with general best practices.

We will be processing about 500k records per day, with a maximum guess at about a million a day. The POC then aggregated all this data down into 5-minute chunks per client per day for the last 4 years. Total being a bit over 500k time slots, for each fact (POC had 4 fact tables) for each customer (Lets plan for 25). So for the POC, we are looking at approximately 50 million rows across all the facts by client and time. That leaves final views of the data at minor amounts, given that the last 6 months satisfies 85% of all questions, that means we should be sitting with result sets of approx 250k rows for 85% of the time if not less. Given that I expect to triple data, let's call it a maximum of 1 Million rows to satisfy 85% of queries, split across about 8 to 10 fact tables with about 4 or 5 dimension tables.

With that all said and done, as mentioned, I am in a very fortunate position where I can now choose the technology that is used for the backend of the warehouse. I have never really had the opportunity to really get entangled in SSAS and feel like its a technology I am lacking.
Is SSAS still a popular choice for Data Warehouses or has its popularity declined over the past few years? Is it still used as much as it was 5 years ago?

SHORT VERSION: Is SSAS still something worth learning/investing time and effort into?

We are currently on SQL 2016 and this will be built on SQL Server 2017

Thank you for all your input.


texts: List all parents/ascendants of all nodes

texts:
I have the following structure:
Example Tree:

Example Table:
    [table]

    id  parent
    ----------
    1   NULL
    2   1
    3   1
    4   2
    5   2
    6   2
    7   3
    8   4

For each id, I want to list all of its parents, including its ascendants in a separate row.
Desired Output:
    id  parent
    ----------
    1   NULL
    2   1
    3   1
    4   1
    4   2
    5   1
    5   2
    6   1
    6   2
    7   1
    7   3
    8   1
    8   2
    8   4

I tried to use a CTE, but I can't seem to wrap my head around it.
    with temp(id,parent) as (
    SELECT S.id, S.parent
    FROM [table] as S

    UNION ALL

    SELECT S2.id, S2.parent
    FROM [table] as S2
    inner join temp on S2.id=temp.parent and temp.id is not null
    )

    SELECT * FROM temp order by id

I'm trying to traverse up a hierarchy tree and list all the nodes it hits in a separate line for all starting points of the tree. This is how the sample data was given to me.


texts: Prevent SQL Server Management Studio changing destination DB on restore

texts:
Is there a way to prevent SQL Server Management Studio 2014 from automatically setting the destination database when you try to restore a backup to another database?

I just had a nasty surprise when I wanted to restore a "somewhat fresh" backup of the production DB on the acceptance, and noticed after the restore (+Overwrite database) started not on acceptance but production. I ruined a whole 6 hours of business work (thanks IDE to know better than me how to get me fired...)


texts: error while importing data in MySQL workbench

texts:
I tried to import data from a dump file on MySQL workbench 8, it is showing the (unknown database) error with code 1049 (42000)for each table in the database,
is it because I exported data from a different version of MySQL, is it an error with the folder (corrupted) or I have done something wrong in the process?

I choose Data import from server menu, then choose the dump folder and start importing then the error pop-up for every table, with 25 tables 25 errors.


texts: Delete rows in parent table based on criteria in child table - Oracle

texts:
I have a table B with a foreign key to table A. I want to DELETE some rows in table B, and I also want to DELETE their parent rows in table A. However, the delete criteria is based on table B. The rows in table A cannot be deleted first because the reference in table B restricts the deletion, but I also have to get the keys of A from the rows to delete in B.

Here is a SQLFiddle with a sample table structure:
http://sqlfiddle.com/#!4/f156c/4/0.

My first inclination was to attempt to save the keys by SELECTing them from B into a variable, and then use that to DELETE from A.

DECLARE
  A_ID_TO_DELETE DBMS_SQL.NUMBER_TABLE;
BEGIN
  SELECT A_ID BULK COLLECT INTO A_ID_TO_DELETE
  FROM (SELECT A_ID
        FROM B
        WHERE LENGTH(B_DATA) > 4
       );

  DELETE FROM B
  WHERE LENGTH(B_DATA) > 4;

  DELETE FROM A
  WHERE A_ID IN A_ID_TO_DELETE;
END;
/

But that just gives an PLS-00382: expression is of wrong type error. The error itself is coming from the DELETE on A; I know this because if I comment it out, the block runs.

How can I either get around the expression is of wrong type error, or what's another way to approach this?

Oracle version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

(Yes, I'm well aware of how old that is. Client's choice of DB, not ours.)


texts:
I'm using an sqlite database to store manually created labels for some data automatically queried from a live system. The data from the live system consists primarily of an address, comprised of 3 parts. Let's use URLs an example, the three parts being the protocol, the domain and the path. Initially, I would load a couple 100k worth of addresses into a table with each field of the address being a column and together building the primary key. The labels are then in additional columns.
CREATE TABLE OldWebsites (
    protocol VARCHAR (255) NOT NULL,
    domain   VARCHAR (255) NOT NULL,
    path     VARCHAR (255) NOT NULL,
    label1   INTEGER,
    label2   TEXT,
    CONSTRAINT address PRIMARY KEY (
        protocol,
        domain,
        path
    )
);

I found myself repeating labels over and over based on certain patterns that the address would match. Since I would always extend this table with new data and remove old data, this became too much of a hazzle, so I tried a different approach, namely just loading the existing addresses into one table and then have other tables for the data where I would write regex matchers for the address components
CREATE TABLE Websites (
    protocol VARCHAR (255) NOT NULL,
    domain   VARCHAR (255) NOT NULL,
    path     VARCHAR (255) NOT NULL,
    CONSTRAINT address PRIMARY KEY (
        protocol,
        domain,
        path
    )
);

CREATE TABLE Label1 (
    protocol_re VARCHAR (255) NOT NULL,
    domain_re   VARCHAR (255) NOT NULL,
    path_re     VARCHAR (255) NOT NULL,
    label1   INTEGER
    CONSTRAINT address_matcher PRIMARY KEY (
        protocol_re,
        domain_re,
        path_re
    )
);

CREATE TABLE Label2 (
    protocol_re VARCHAR (255) NOT NULL,
    domain_re   VARCHAR (255) NOT NULL,
    path_re     VARCHAR (255) NOT NULL,
    label2   TEXT,
    CONSTRAINT address_matcher PRIMARY KEY (
        protocol_re,
        domain_re,
        path_re
    )
);

Assume that I have already (using other queries) guaranteed, that there is exactly one match in each label table for each address in the Websites table. I would now like to write a query that reconstructs a table like the original OldWebsites one by matching labels and automatically queried data.
Something like this
SELECT Websites.*,
       Label1.label1,
       Label2.label2
  FROM Websites
       JOIN
       Label1 ON (Websites.protocol REGEXP '^' || Label1.protocol_re || '$' AND
                  Websites.domain REGEXP '^' || Label1.domain_re || '$' AND
                  Websites.path REGEXP '^' || Label1.path_re || '$')
       JOIN
       Label2 ON (Websites.protocol REGEXP '^' || Label2.protocol_re || '$' AND
                  Websites.domain REGEXP '^' || Label2.domain_re || '$' AND
                  Websites.path REGEXP '^' || Label2.path_re || '$');

Now.. this is really slow, especially for more label tables, using PCRE sqlite3 extension for the REGEXP function.
I would like to know if there's way to optimize this query using either parallelization (the query should run ideally from python) or using the knowledge that there is exactly 1 match in each Label table.
From my understanding, multiple inner joins should take at most the sum of the individual joins, correct?
Perhaps indexes are also helpful, but I have only a basic idea of what they are and no idea whether they would be of help here.


texts:
Is there a way to backup data using mysqldump --no-create-info ... where the inserts in the backup file will look like this:

insert into t1(col1, col2)values(val1,val2);

instead of

insert into t1 values(val1,val2);

The problem with the second way is that when the table structure changes, the insert will not work. With the first way, the table structure, e.g, one more column is added, will not affect the insert statement.

I have to use the mysqldump. Any suggestions please?


texts:
I've multiple linux servers which runs MySQL server and my applicaiton each.
I have a cron job which creates daily backups and uploads them to another server. Daily backups are OK but, i want to be able to recover the current state of the database in case of the server goes down or whatever happens.
Can I create a single "read only async replication server" and configure all other servers to create replications to that server?
OR, what is the right way to do that?


texts: Is it possible to list all user that accessed to specific table

texts:
Is there a way in Oracle (11) to make a list of users when they were last accessed (select / update / insert or delete) a table or view ?

Thanks


texts: How SQL Server agent calculates next run date for a job

texts:
I am developing a system for scheduling task & I am following Microsoft SQL agent approach to schedule task.
I have replicated sysschedules table for capturing users scheduling occurrences and timings.
Now I need to know how SQL Server get the Next_run_date and time from this table.

I have found solution on internet which are using sysjobhistory table to get the next run date, but in my case I have only sysschedules table. So, what is the formula to calculate the next_run_date and time for any job.

Note: I am not looking for entire code, concept/algorithm will work for me.


texts: Issue after moving the ib_logfile1 and ib_logfile0 files

texts:
I wanted to increase the innodb_log_file_size for better performance. And I increased it from the default value to 256 MB. As usual I got the error saying InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes. So I moved these files to different location and restarted the MySQL server. It worked. Before shutting down the MySQL, server I also made innodb_fast_shutdown  =  0.

But I have a doubt, after MySQL shutdown can we expect mysql is an consistent state and all the data that were in the ib_logfile0  and ib_logfile1 have been written to data files. So that I can delete these files or should I do something else also.

And if the answer is MySQL will be in consistent state than why these files contain some data even after graceful shutdown and start up with innodb_fast_shutdown  =  0.

[EDIT details]  giving the steps i have done

  Steps i followed:

  SET GLOBAL innodb_fast_shutdown = 0
  /etc/init.d/mysqld stop
  rm -f /var/lib/mysql/ib_logfile0
  rm -f /var/lib/mysql/ib_logfile1
  /etc/init.d/mysqld start

Is the above step is fine? if yes then how come after the clean shutdown and start up the log files contains some data. Ideally it should be empty.


texts: How do I pass or fail a job in SQL Server agent based on a return value from a stored procedure?

texts: What do I need to do in order to execute a step in a SQL Server 2012 agent job that returns a bit value back? Basically, I want to fail the job or pass to the next step based on the results from the execution of the stored proc.


texts: Getting Today's , MTD, YTD Sale and profit

texts:
I have a database which give me sale and profit for each date. Schema looks like following :

Date         Sales      Profit

2015-03-07   133333     12348
2015-03-08   214563     34545
2015-03-09   299593     59940

Now I want to get results like following:

      Today    MTD     YTD

Sales

Profit

The brute-force approach I am using have 6 sub-queries with union.

It looks like following:

  SELECT 'Sales', TotalSales, 0, 0
  FROM SalesTable
  where Date = GETDATE()
  UNION
  SELECT 'Sales',0, SUM(TotalSales), 0
  FROM SalesTable
  where MONTH(Date) = MONTH(GETDATE())
  UNION
  SELECT 'Sales',0,0, SUM(TotalSales)
  FROM SalesTable
  where YEAR(Date) = YEAR(GETDATE())

and the similar for profit.

How can this be done in a more efficient/structural way?


texts:
I'm having a hard time to understand how to filter out result of multidimensional expression.

This is my database schema.

And this is my data.

Manufacturer
╔════╦═══════════════════╗
║ Id ║       Name        ║
╠════╬═══════════════════╣
║  1 ║ Awesome Computers ║
║  2 ║ TailSpin Toys     ║
╚════╩═══════════════════╝

Item
╔════╦═════════╦════════════════╦═══════╗
║ Id ║  Name   ║ ManufacturerId ║ Stock ║
╠════╬═════════╬════════════════╬═══════╣
║  1 ║ PC      ║              1 ║    40 ║
║  2 ║ Server  ║              1 ║    10 ║
║  3 ║ STB     ║              2 ║    80 ║
║  4 ║ Console ║              2 ║    50 ║
╚════╩═════════╩════════════════╩═══════╝

Part
╔════╦══════════════════╦════════╦══════════╦═══════╗
║ Id ║       Name       ║ ItemId ║ StatusId ║ Stock ║
╠════╬══════════════════╬════════╬══════════╬═══════╣
║  1 ║ MBO              ║      1 ║        1 ║   100 ║
║  2 ║ Processor        ║      1 ║        1 ║   100 ║
║  3 ║ Server MBO       ║      2 ║        2 ║    20 ║
║  4 ║ Server processor ║      2 ║        2 ║    20 ║
║  5 ║ Main box         ║      3 ║        2 ║    40 ║
║  7 ║ Adapter          ║      3 ║        3 ║    30 ║
║  8 ║ Controller       ║      4 ║        2 ║    40 ║
║ 10 ║ Adapter          ║      4 ║        1 ║    60 ║
║ 11 ║ Memory card      ║      4 ║        2 ║    80 ║
╚════╩══════════════════╩════════╩══════════╩═══════╝

Status
╔════╦═════════════╗
║ Id ║    Name     ║
╠════╬═════════════╣
║  1 ║ No data     ║
║  2 ║ Available   ║
║  3 ║ Unavailable ║
╚════╩═════════════╝

I imported everything into the tabular model solution.
After this, I created two measures:

Table Item: ItemStock:=SUM([Stock])
Table Part: PartStock:=SUM([Stock])

Then I deployed the cube to the server.

By running the following MDX query...

SELECT
    NON EMPTY {
        [Part].[Name].CHILDREN
    } ON ROWS,
    {
        [Measures].[PartStock]
    } ON COLUMNS
FROM [Model]
WHERE (
    {
        [Status].[Id].&[1]
    }
)

...I get this resultset...

╔═══════════╦═══════════╗
║           ║ PartStock ║
╠═══════════╬═══════════╣
║ Adapter   ║        60 ║
║ MBO       ║       100 ║
║ Processor ║       100 ║
╚═══════════╩═══════════╝

...which is ok.

However, when running this MDX query...

SELECT
    NON EMPTY {
        [Item].[Name].CHILDREN
    } ON ROWS,
    {
        [Measures].[ItemStock]
    } ON COLUMNS
FROM [Model]
WHERE (
    {
        [Status].[Id].&[1]
    }
)

...I'm getting this resultset...

╔═════════╦═══════════╗
║         ║ ItemStock ║
╠═════════╬═══════════╣
║ Console ║        50 ║
║ PC      ║        40 ║
║ Server  ║        10 ║
║ STB     ║        80 ║
╚═════════╩═══════════╝

I was expecting that items in the ItemStock table would be filtered out by Part table as a many-to-many relationship. E.g. MBO, Processor and Adapter have references to items 1 and 4, so the result would be constrained to them, and the result should turn out like this:

╔═════════╦═══════════╗
║         ║ ItemStock ║
╠═════════╬═══════════╣
║ Console ║        50 ║
║ PC      ║        40 ║
╚═════════╩═══════════╝

What am I doing wrong?


texts:
I'm in oracle and I have a table 'parts' in which have a field 'Description', which contains description for a part.
my query is:

SELECT DESCRIPTION
FROM   PART
WHERE  PAR_ID=123
AND    DESCRIPTION LIKE 'CHARGE BATTERIES%';

it results:

CHARGE BATTERIES
CHARGE BATTERIES_3
CHARGE BATTERIES_4

I want it to only result '3' and remove all leading characters.
I'm familiar with sql trim, substr ans instr functions but don't know hot use these functions.
Any help will be appreciated.


texts: Preserve tab and line break while insert JSON string to Document in MongoDB

texts:
I'm inserting new document to MongoDB as following:
db.crawl_api_config.insertOne({
    apiHost: "logbasex.github.io",
    apiUrl: "20221",
    contentType: "Blog",
    headers: "",
    jsonPath: {
        "items": {
            "path": "arts",
            "name": "item",
            "type": "ArrayObject"
        }
    },
    parameters: ""})

And after insert this document, I double-check the database to make sure the JSON field is well-defined, but there is something wrong:
{"items": {"path": "arts", "name": "item", "type": "ArrayObject"}}

All tabs and line-breaks were disappeared. I don't know why. Is there any solution? Thanks.


texts:
I have multiple database in my MYSQL server and i want to take some particular tables backup from each database.
    can anyone help me in writting a shell script for this scenario.


texts: How do I make PostgreSQL include all the months where I did not buy anything?

texts:
I have this query:
SELECT avg(sum)
FROM
(
    SELECT date_trunc('month', "timestamp"), sum(amount) as sum
    FROM bookkeeping WHERE summary LIKE 'Bitcoin purchase:%'
    GROUP BY date_trunc('month', "timestamp")
) a

It gave me an impossible average cost of my Bitcoin purchases per month. I soon found out why: it ignores all the numerous months over the years where I did not buy Bitcoin at all, including only months when I did buy at least once. Since there aren't a ton of "empty" months included (with 0 for sum), it does the average of the sums for only the months where I bought at least once, so the average becomes completely wrong.
How do I make it "invent" rows for the months which aren't represented by at least one record, so that the avg(sum) will actually be meaningful?


texts:
I have a single column that contains information that I need to have split up into three separate columns. I currently have a SELECT statement that works partially, but my ChangeTo column is not returning any data and I cannot figure for the life of me why. Here is what the current column being returned looks like:

| bp_comment                                             |
----------------------------------------------------------
| changed status from "new" to "in progress"             |
| changed ORT status from "in progress" to "Code Review" |
| changed MileStone from "1.1" to "1.2"                  |

Here are how I need the results to return:

| ChangeType              |  ChangeFrom  |   ChangeTo  |
-----------------------------------------------------
| changed status from     | new          | in progress |
| changed ORT status from | in progress  | Code Review |
| changed MileStone from  | 1.1          | 1.2         |

Here are how the results are returning right now:

| ChangeType              |  ChangeFrom  |   ChangeTo  |
-----------------------------------------------------
| changed status from     | new          |             |
| changed ORT status from | in progress  |             |
| changed MileStone from  | 1.1          |             |

Here is the query I am using:

SELECT
    LEFT(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) + 5) AS 'ChangeType'
    ,REPLACE(SUBSTRING(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) + 5, CHARINDEX(' to ', CONVERT(VARCHAR(MAX), bp.bp_comment)) - CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) - 5), '"', '') AS 'ChangeFrom'
    ,REPLACE(RIGHT(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' to ', REVERSE(CONVERT(VARCHAR(MAX), bp.bp_comment)))), '"', '') AS 'ChangeTo'
FROM
    bug_posts bp

Thank you in advance!


texts: Windows Authentication and IS_MEMBER() function

texts:
We have an internal domain and an external one (for customers) that trusts the internal one (one way trust). I guess this is the case in many organizations.

We have a problem with the IS_MEMBER() function in SQL Server 2014, where the function doesn't seem to see the external domain.
It fails on various servers where SQL Server 2014 is installed, not just on some arbitrary one. Within the same domain controller,
the function is working fine in SQL Server 2008 R2.

Do you have any hints to help me resolve the problem ?


texts: Basic SQL question about setting constraints

texts:
In code such as the following, what is the need to write constraint pk_dept primary key (deptno) if just simply primary key (deptno) works?  Is pk_dept something the database uses internally, since it doesn't seem to be a visible part of the table?

create table dept
    (deptno decimal(2,0),
    dname  varchar(14),
    loc varchar(13),
    constraint pk_dept primary key (deptno));


texts: Help to understand 'log space used' metric

texts:
The chart below comes from a monitoring tool. The metric shown is "log space used" for SQL Server 2016 for the last 7 days.

I'm looking for help to understand what's going on here.

What I do know is that the IT department is using a 3rd party tool (Veeam) to "back up" regularly. I'm advised full backups are performed nightly and log backups are performed every 15 minutes (but I am not sure if these are disk image backups of the database files or commands issued to SQLServer by Veeam to perform a backup). One of the steps includes "Process logs" for SQL Server and I believe one of the steps there is to truncate the logs.

I understand truncating the log means to mark as inactive those portions of the log file that may be re-used. At the moment, I understand (rightly or wrongly) that a log file portion may be re-used if SQL Server knows it has been written to disk - ie via backup.

What I see in this chart is

An abnormally high usage of the log file for one DB at far left - ignore that.
Daily growth and then daily clearing of each log file (3 humps).
Growth over 3 days (Fri, Sat, Sun), then truncation.

My interpretation here is that log backups must be occurring, otherwise the usage would not drop back near to zero.

I suspect the log backups are occurring only once per day - although I don't think this chart proves it. For example, there is the possibility that there are more frequent log backups during business hours, but truncation does not occur at that time due to a long-running transaction. However at the nightly backup by Veeam, the truncation command is issued and succeeds. Despite this possibility, I still think it more likely that log backups are being carried out only once per day by Veeam.

Part of the issue here is that Veeam is a bit of a black box to me - I am not sure how IT has this configured. I am also not clear on whether it is taking snapshot images of the hard disks (ie database files), or instructing SQLServer to execute a backup.

My goal is to ensure we are taking frequent log file backups (and to work out a good frequency) in order to support a full recovery model across essentially all of these databases. If I determine that currently we are taking log backups no more frequently than nightly, then I also need to ensure that if I put measures in place for frequent log backups, that I'm not competing with Veeam such that Veeam and any scheduled task within SQLServer are not breaking log chains for each other - any suggestions on how to check and plan these things would be most appreciated.


texts: How can I import Create Table Statement from SQL Developer into Visio to create an Entity Relationship Diagram?

texts:
I have the following Create table statement from SQL Developer and I am trying to generate an ERD Diagram using 2003 Visio. I was wondering what I need to do to convert the Create Table from SQL Developer to an ERD Diagram Using Visio.

CREATE TABLE “TT_TEAM”.”HR_REVOKED_SECURITY_ORGS”
(
“ID” NUMBER NOT NULL ENABLE,
“ACAT_CODE” VARCHAR2(6 BYTE) NOT NULL ENABLE,
“APPR_SEQ_NO” NUMBER(3,0) NOT NULL ENABLE,
“USER_ID” VARCHAR2(30 BYTE) NOT NULL ENABLE,
“ACTION_IND” VARCHAR2(1 BYTE) NOT NULL ENABLE,
“ACTIVITY_DATE” DATE NOT NULL ENABLE,
“COAS_CODE” VARCHAR2(1 BYTE),
“ORGN_CODE” VARCHAR2(6 BYTE),
“POSN” VARCHAR2(6 BYTE) NOT NULL ENABLE,
“MANDATORY_APPR_IND” VARCHAR2(1 BYTE),
“APPR_POSN” VARCHAR2(6 BYTE),
“ORGN_MANAGER” VARCHAR2(6 BYTE) NOT NULL ENABLE;


texts:
I am using MS SQL, and I have to run several queries on the same table on different criteria. At first I ran each query on the original table although they all share some filtering (i.e Date, status). This took a lot of time (around 2 minutes).

There are duplicates in data rows, and all indexes are NON-CLUSTERED. I am only interested in 4 columns for my criteria and the result should output the count only, for all queries.

columns needed: TABLE, FIELD, AFTER, DATE, and there is an index on each of DATE and TABLE.

After creating a temp table with only the fields I need, it went down to a 1:40 minutes, which is still very bad.

CREATE TABLE #TEMP
(
    TABLE VARCHAR(30) NULL,
    FIELD VARCHAR(30) NULL,
    AFTER VARCHAR(1000) NULL,
    DATE DATETIME,
    SORT_ID INT IDENTITY(1,1)
)
CREATE CLUSTERED INDEX IX_ADT ON #TEMP(SORT_ID)

INSERT INTO #TEMP (TABLE, FIELD, AFTER, DATE)
    SELECT TABLE, FIELD, AFTER, DATE
    FROM mytbl WITH (NOLOCK)
    WHERE TABLE = 'OTB' AND
    FIELD = 'STATUS'

Runnig this -> (216598 row(s) affected)

Since not all queries rely on date range, I didn't include it in the query. The problem is that it's taking well above 1 minute to insert only. The above insert took 1:19 mins

I want to run something like this for several queries:

SELECT COUNT(*) AS COUNT
FROM #TEMP
WHERE AFTER = 'R' AND
DATE >= '2014-01-01' AND
DATE <= '2015-01-01'

It's a problem with the insert more than that of the selection, but the temp has way less rows than the original table which could be better than going through the table several times.

How can I optimize this?

EDIT

I have removed the sort ID, I thought the problem was with mainly the select and not insert. It was a guess.

I can't create a unique on any index since there is no unique field or rows.

I am using SQL Server 2012.

Table Info: It's a heap and has the following space usage:

name    rows        reserved    data        index_size  unused
mytbl   24869658    9204568 KB  3017952 KB  5816232 KB  370384 KB


texts: How to run SQL Job only on primary replica in AlwaysOn Availability Groups?

texts:
I have SQL Jobs that need to only on primary replica on AlwaysOn Availability Group. When jobs were originally created ServerA was primary, at later point ServerB became primary so jobs that were on ServerA failed and jobs had to be manually recreated on ServerB to run properly.

What is the way to have jobs run only on Primary replica of AlwaysOn Availability Group?


texts: How do I get DBeaver to do a DB2 UPDATE via Cursor

texts:
The following query works when run from the console via db2 command. But fails when running via DBeaver.
BEGIN NOT ATOMIC
  FOR R AS C CURSOR WITH HOLD FOR
    SELECT ROW_NUMBER() OVER() AS INDEX, ID
    FROM A_TABLE a
    WHERE a.ID BETWEEN 1 AND 1000
    ORDER BY a.ID
  DO
    UPDATE A_TABLE SET A_COL = 1 WHERE ID = R.ID;--
    IF MOD(R.INDEX, 100) = 0 THEN
      COMMIT;--
    END IF;--
  END FOR;--
  COMMIT;--
END;

DBeaver gives the following result

SQL Error [42601]: An unexpected token "END-OF-STATEMENT" was found following "WHERE ID = R.ID".  Expected tokens may include:  "<psm_semicolon>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14


texts:
we have a database server with Sql Server 2008R2 that runs some queries every day; now these queries are fast (I suppose Sql Server has in memory some good execution plans).

If we restart the database server these execution plans in memory will be lost: is it possible that the next time the queries run, Sql Server regenerate some execution plans worst than the previous ones?

If it is so, is it possible to prevent this problem?

Thank you.


texts: After RAM upgrade on 32-bit Oracle server, ORA-04031: unable to allocate x bytes of shared memory

texts:
We are in the process of upgrading our Oracle database server at work, from SUSE Linux Enterprise Server 10.4 / Oracle 10g / 4GB RAM (Intel 32-bit) to Scientific Linux 6.4 / Oracle 12c / 8GB RAM (Intel 64-bit).

As the first step, the additional RAM has been installed, but due to some application-related issue we had to pend the rest of the planned upgrade. To clarify, the server is still running Oracle 10g and SLES 10.4. No change has been made on the software side

Since the installation of the additional RAM, however, the only database instance on that machine has been running out of shared memory (with the error in the title) two afternoons in a row. The depletion was severe enough that the only way to recover was using SHUTDOWN ABORT.

No configuration change has been made, e.g. to the memory settings of the database:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1632M
sga_target                           big integer 1632M
pga_aggregate_target                 big integer 384M
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_advice                      string      ON
db_cache_size                        big integer 960M

It seems that the extra RAM actually cause the Oracle database software to have less RAM available to itself than before, but how exactly is this happening?


texts: MySQL slave_open_temp_tables always shows 1

texts:
I have a MySQL database set up for testing xtrabackup. The database is MySQL 5.5.52.

It is configured as a slave, however, there is very little in the way of replication (1 query every 10 seconds).

During the last test of xtrabackup (last night) it failed to complete due to:

180606 01:05:18 Slave open temp tables: 1
180606 01:05:18 Slave_open_temp_tables did not become zero after 300 seconds

Looking at the the database now, it is still showing Slave_open_temp_tables = 1.

I have tried restarting replication, and flushing the tables and logs. But it insists on staying at 1.

In the end I was forced to restart the database entirely, and that fixed the problem.

Is there a better way of 'flushing' this status without needing to resort to a full restart.


texts: Transactional Replication Error adding new subscription (null) article

texts:
When attempting to add a new subscription to an existing Transactional publication on 2008. It is all configured on 1 server as it is for Pre-Production testing.

I get the following error when attempting to create the new subscription.

Msg 20027, Level 11, State 1, Procedure sp_MSadd_subscription, Line 223
The article '(null)' does not exist.
Msg 14070, Level 16, State 1, Procedure sp_MSrepl_changesubstatus, Line 1208
Could not update the distribution database subscription table. The subscription status could not be changed.
Msg 14057, Level 16, State 1, Procedure sp_MSrepl_addsubscription_article, Line 383
The subscription could not be created.


texts: Speed up SELECT query with JOIN from table with 2.2M rows in PostgreSQL?

texts:
There are two tables in PostgreSQL:

//has 2.2 millions rows
create table account (
    account_id text primary key,
    email text not null,
    created_at timestamp not null
);

//has 1.8 millions rows
create table account_intercom (
    account_id text primary key references account(account_id),
    intercom_id text not null
);

It's easy to notice ~400k rows difference between these tables.
I want to select these 400k account_id and perform some operations with them in order to eliminate this "gap".

I tried this query:

SELECT account_id, email
  FROM
    account NATURAL LEFT JOIN
    account_intercom
  WHERE intercom_id IS NULL;

But it runs extremely slow.

What is the way to speed up this query?

QUERY PLAN
---------------------------------------------------
 Hash Left Join  (cost=75252.40..207091.91 rows=1 width=46) (actual time=30056.787..87284.881 rows=417195 loops=1)
   Hash Cond: (account.account_id = account_intercom.account_id)
   Filter: (account_intercom.intercom_id IS NULL)
   Rows Removed by Filter: 1799858
   ->  Seq Scan on account  (cost=0.00..47840.67 rows=2221867 width=46) (actual time=0.010..13484.228 rows=2217053 loops=1)
   ->  Hash  (cost=36919.51..36919.51 rows=1800551 width=48) (actual time=30018.683..30018.683 rows=1799858 loops=1)
         Buckets: 65536  Batches: 64  Memory Usage: 2722kB
         ->  Seq Scan on account_intercom  (cost=0.00..36919.51 rows=1800551 width=48) (actual time=0.008..9801.883 rows=1799858 loops=1)
 Planning time: 0.333 ms
 Execution time: 88794.797 ms


texts: PostgreSQL: Use column with hash based on other columns as unique index?

texts:
My previous problem is still not fixed: We cannot set a unique index because NULL values are allowed...

we use this table in our database:

CREATE TABLE offer (
   offer_id   serial PRIMARY KEY
 , product_id int NOT NULL REFERENCES product
 , price_old  numeric(10,2);
 , price      numeric(10,2);
 , price_alt  text          -- overrules price if present
 , valid_from timestamp NOT NULL
 , valid_to   timestamp     -- optional
 --   more attributes of the offer
 , CONSTRAINT some_kind_of_price_required
      CHECK (price IS NOT NULL OR price_alt IS NOT NULL)
);

And we use a unique index which doesn't work:
ALTER TABLE offers ADD CONSTRAINT offer_unique_index
UNIQUE(product_id, price_old, price, price_alt, valid_from, valid_to);

But the unique index doesn't work because price_old, price and price_alt can be NULL.... It's possible that only price is filled, only price and price_old or only price_alt...

Possible solution:

Now I might have a solution, but I don't know how to realize it exactly...

I thought if I could use NEWID() or MD5() to create an extra column named "uid" (which contains a hash based on the data of all other columns) and add a unique_index on that "uid" column, it could fix the problem. So when I try to insert the exact same values (prices, valid dates etc.), it will create a same hash (because the values are the same, obviously) and that triggers the unique index violation for the hash column.

Nice idea? But more important: possible?


texts: MDW Data Collector Server Activity collection step 3 suddenly stopped working

texts:
For many weeks my data collections from my database instace have been uploading to my MDW server. Suddenly step 3 of the Server Activity Collector has stopped working giving the following error:

Job Name          DataCollector Server Stats Coll n Upload
Step Name         collection_set_3_noncached_collect_and_upload_upload
Duration          00:00:05
Sql Severity      0
Sql Message ID    0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

  Message:
  Executed as user: domain_svcAccount. SSIS error. Component name: DFT - Find and save sql text, Code: -1071636471, Subcomponent: LKUP - Look up query text on target server [2], Description: SSIS Error Code DTS_E_OLEDBERROR.

  An OLE DB error has occurred. Error code: 0x80040E14.   An OLE DB record is available.

  Source: "Microsoft SQL Server Native Client 11.0"
  Hresult: 0x80040E14  Description: "The handle that was passed to dm_exec_sql_text was invalid.".

  SSIS error. Component name: DFT - Find and save sql text, Code: -1071611309, Subcomponent: LKUP - Look up query text on target server [2], Description: OLE DB error occurred while fetching parameterized rowset. Check SQLCommand and SqlCommandParam properties.  SSIS error. Component name: DFT - Find and save sql text, Code: -1073450974, Subcomponent: SSIS.Pipeline, Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "LKUP - Look up query text on target server" (2) failed with error code 0xC0208253 while processing input "Lookup Input" (16). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

  The master package exited with error, previous error messages should explain the cause.
  Process Exit Code 5.  The step failed.

Has anyone encountered this error before?


texts: Using same table in a UNION multiple times

texts:
I am given a query that is using the same table with same columns and filters multiple times and the only thing is changing is the Type which is given manually. I am thinking there must be a better way to do that.
Query statement is like below:
    Select Column 1, Column 2, Sum(Column 3) AS Test, "First Query" AS "Type"
           From Table X
           Where Column 1 = "Fly"
    Group by Column 1, Column 2

    UNION ALL

    Select Column 1, Column 2, Sum(Column 3) AS Test, "Second Query" AS "Type"
           From Table X
           Where Column 1 = "Fly"
    Group by Column 1, Column 2

    UNION ALL

    Select Column 1, Column 2, Sum(Column 3) AS Test, "Third Query" AS "Type"
           From Table X
           Where Column 1 = "Fly"
    Group by Column 1, Column 2

Thanks!


texts: how to set max_parallel_workers_per_gather permanently?

texts:
I am experiencing problems with parallelism due to max_parallel_workers_per_gather
I have set the following parameters in postgresql.conf:
max_worker_processes = 64
max_parallel_workers = 32
max_parallel_workers_per_gather =32

though, SHOW max_parallel_workers_per_gather; returns 0, unless I set the parameter within current session.
EXPLAIN returns Workers Planned only if I set SET max_parallel_workers_per_gather to 32; within the explained query.
Though, I can not set the parameter with every query, since I work through python sqlalchemy
postgres db works within docker container
How to set the max_parallel_workers_per_gather permanently?


texts: How do I link two Access Tables so if one Table gets updated both will?

texts:
I have an access database with a table that is linked to an excel sheet. I also have a duplicate table with the same data linked to a postgres database. My goal is to either link these two tables in such a way where when the excel linked table gets updated so does the postgres linked table or link a single table to both excel and postgre, is this even possible?

My ultimate goal is to use the excel sheet's data in a website. The excel sheet is the primary data source and all CRUD operations are done in excel, my website is simply a dashboard to display that data. I want my website to pull the data from the excel sheet when someone makes an edit and saves the file. As far as I know there's no way to do this without buying software. Since Access edits auto update a postgres database my thought process was to keep the existing links from excel to Access and then create another link from access to postgres and use postgres as the database my App would connect to.


texts:
After a dump import the disks got full and as to try to fix it I just removed the corresponding directory within /var/lib/mysql and now the database doesn't load anymore.

I have some other databases that were running on this server and now none of them works now. All what I want is to load the mysql again to recover the other databases.

I tried to start the database with innodb_force_recovery = 6 and I it didn't work.

May anyone give a clue to solve this problem?


texts: Is there a MySQL maximum database or table count?

texts:
I am in the process of creating a massive archive of bitboards stored in MySQL.  In my schema,  there are four linked sets of bitboards which--for query sake--are represented in four different tables.  Each of the four sets will have ~100k rows and I expect there to be about 35k of these sets.

My question is about the performance ramifications and limitations of the SQL server itself. I have considered the following models:

35k databases, each with four tables of 100k rows each
1 database, four named tables for each set, e.g., db948-a, db948-b, db948-c, db948-d

Additional information:

These tables are in no way linked to eachother; they do not need to be joined at any point.
They do not need to be UPDATED at any point (one-time INSERT for each of the four tables) and only SELECTs from then on.
This will be stored on ReiserFS (or comparable FS with no file-count concerns)
I expect to use MyISAM (no transactional support needed)

So my main concern is about MySQL limitations on table and database counts--given the above information, is there any limitation I'll exceed that would disqualify one of the schemas?

In addition, are there any glaring performance issues that would arise from either the 35k number of distinct databases, or 140k tables in one database?

Thanks!


texts: Admin on phpMyAdmin does not have Super privileges

texts:
I have Plesk via GoDaddy and I am the sole admin and user. When I go to phpMyAdmin and try to execute the following:

SET GLOBAL event_scheduler = 'ON'

I get the error "#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation"

Considering I don't have the tab for security how can I change the sql database privileges to allow this? It would be easy to do if it was local hosted but I can't find anything on how to do this with my specific setup.


texts: using count from subquery results to order parent query results

texts:
I have this query:
       select * from mbk_file
       where user_id = $1
       order by (
           select count(file_id) from mbk_file_label
           where file_id = mbk_file.id
       ) DESC

I noticed that changing DESC to ASC didnt change the order, even though I would expect it should. So that tells me that the subquery is not really doing anything useful.
I have these tables:
-----mbk_file------
id   bigint

-----mbk_file_label-----
id   bigint
file_id  (from above table)

where there is a 1 to many between the 1st and 2nd tables.
I don't think I need a group by clause, I just need a count of all the rows where file_id is from the 1st table?
I think I need to do something like this instead:
         select *, (
                 select count(file_id) from mbk_file_label
                 where file_id = mbk_file.id
           ) as xxx
           from mbk_file
           where user_id = $1
           order by xxx DESC

but I still don't understand how file_id will vary by row..
let me explain with data:
---mbk_file----   // has 3 records total
1
2
3

---mbk_file_label
1  1
1  2
1  3
1  4       // file 1 has 4 matching records

2  1
2  3       // file 2 has 2 matching records

3  2
3  3
3  4       // file 3 has 3 matching records

so what I want to do is rank the mbk-files, based off of how many matching records there are in mbk-file-label.
So the rank/order for select result for mbk-files would be:
1
3
2

since 1, has 4 matching records, file 2 has 2 matching records and file 3 has 3 matching records.  I hope this makes sense because it's pretty simple but i don't know how to create the query fml :(


texts: Value of prepared statement is listed as error in query string

texts:
I have made a Wordpress plugin that writes some data to a proprietary table.

The query as shown in the log is:

INSERT INTO berichten_devices (device_UUID, article_id) VALUES (%s, %s)

If I take this line and enter it manually via adminer in the database, replacing the first %s with 'test' and the second %s with 1:

INSERT INTO berichten_devices (device_UUID, article_id) VALUES ('test', 1)

The this is stored in the database without a problem. There is also another table that is written to from the plugin which works fine.

The php that is responsible for storing the data is as follows:

function insert_artice_read($uuid, $article_id) {
error_log('$uuid:'.$uuid, 0);
error_log('$article_id:'.$article_id, 0);

    $query = "INSERT INTO berichten_devices (device_UUID, article_id) VALUES (%s, %s)";
error_log('$query', 0);
error_log($query, 0);
    $query = $this->wpdb->prepare($uuid, $article_id);
    return $this->wpdb->query($query);
  }

The parameter $this->wpdb is defined as

$this->wpdb = $wpdb;

The logging that is shown is this:

dcr-wordpress | [Tue Jun 25 10:45:03.363771 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] $uuid:ABCDEF01-2345-6789-ABCD-9876543210AA
dcr-wordpress | [Tue Jun 25 10:45:03.363776 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] $article_id:1
dcr-wordpress | [Tue Jun 25 10:45:03.363781 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] $query
dcr-wordpress | [Tue Jun 25 10:45:03.363785 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] INSERT INTO berichten_devices (device_UUID, article_id) VALUES (%s, %s)
dcr-wordpress | [Tue Jun 25 10:45:03.364844 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] 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 'ABCDEF01-2345-6789-ABCD-9876543210AA' at line 1 bij query ABCDEF01-2345-6789-ABCD-9876543210AA gemaakt door require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, rest_api_loaded, WP_REST_Server->serve_request, WP_REST_Server->dispatch, Prop\\Inc\\Core\\RestController->article_retrieved, Prop\\Inc\\Common\\Repository->insert_artice_read
dcr-wordpress | 192.168.224.1 - - [25/Jun/2019:10:45:03 +0000] "POST /wp/wp-json/prop/v1/berichten-devices HTTP/1.1" 200 802 "-" "Mozilla/5.0 (iPhone; CPU iPhone OS 12_3_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148"

From this logging it is obvious that there is something wrong with the sql syntax, but this line seems weird to me:

for the right syntax to use near 'ABCDEF01-2345-6789-ABCD-9876543210AA'

This is data from the prepared statement parameters, I would expect to see  something like

for the right syntax to use near %s

I tried to use %i instead of %s for the second parameter, just be sure, but there was no difference.

This is the table definition:

CREATE TABLE IF NOT EXISTS berichten_devices (
  device_UUID     VARCHAR(255) NOT NULL,
  article_id      int(10)      NOT NULL,
  date            timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4),

  KEY `dev_uuid` (device_UUID)

My question is basically: "What am I doing wrong?"


texts: Can a trasaction be rolled back if a table is edited outside a transaction?

texts:
I am learning how transactions work using this short script

CREATE TABLE t_test (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;

START TRANSACTION;

INSERT
INTO    t_test
VALUES  (1);

SELECT  *
FROM    t_test;

 id
---
  1

SAVEPOINT tran2;

INSERT
INTO    t_test
VALUES  (2);

SELECT  *
FROM    t_test;

 id
---
  1
  2

ROLLBACK TO tran2;

SELECT  *
FROM    t_test;

 id
---
  1

ROLLBACK;

SELECT  *
FROM    t_test;

 id
---

Towards the end,ROLLBACK a savepoint is used.Does it mean when ROLLBACK is used it wipes out everything that was commited earlier on?.

Secondly,what if i edit the table outside of the transaction and the try ROLLBACK TO trans2 for instance,what happens?.


texts: Error from Program Compatibility Assistant when installing SQL Server 2008

texts:
I am new to this so please do not mind if this is dumb question.

I am receiving an error while installing SQL Server 2008 on Windows 8.1
I have .net 4. I have windows installer 4.5


texts: Why is there a difference in CSV Export Results of Oracle SQL Developer and SQL Plus Spool

texts:
When I export data from SQL Developer GUI, setting format: csv, left enclosure: ", right enclosure: ", and Encoding: UTF8, I get data exactly the way that I need it for processing in Python.

However, when I attempt to do the same thing from SQL Plus, I get very different results. Here are my settings (file.sql is simply a select statement):

export ORACLE_SID=MYSID
sqlplus / as sysdba <<EOF
conn myUser/myPass
set heading off
set termout off
set trimout off
set newpage none
set pages 0
set feedback off
set echo off
set verify off
set trimspool on
set sqlprompt ""
set colsep ","
set linesize 32767
@/home/username/file.sql
exit
EOF

How can I export the data from SQL Plus so that it is exactly like that from SQL Developer? As an FYI, when I run file -i myfile.csv I get "application/octet-stream; charset=binary", while from SQL Plus the file is some form of "ASCII" (and for some tables "ISO").


texts: Returning values for Stored Procedures in PostgreSQL

texts:
I was reading this on PostgreSQL Tutorials:

In case you want to return a value from a stored procedure, you can use output parameters. The final values of the output parameters will be returned to the caller.

And then I found a difference between function and stored procedure at DZone:

Stored procedures do not return a value, but stored functions return a single value

Can anyone please help me resolve this.
If we can return anything from stored procedures, please also let me know how to do that from a SELECT statement inside the body.
If I am wrong somewhere please inform.


texts: Cannot start SQL Server 2014 service. Memory Error (resource pool 'internal')

texts:
Under Windows 2012 R2 Standard (Fully Updated): When trying to start my SQL Server 2014 Service I get in Application event viewer:

"there is insufficient system memory in resource pool 'internal' to run this query"

Here is the complete ERRORLOG in pastebin.

Significant errors I can see:

2017-07-04 15:32:56.59 spid8s       Failed allocate pages: FAIL_PAGE_ALLOCATION 1
...
2017-07-04 15:32:56.62 spid8s      Error: 701, Severity: 17, State: 123.
2017-07-04 15:32:56.62 spid8s      There is insufficient system memory in resource pool 'internal' to run this query.
2017-07-04 15:32:56.65 spid8s      SQL Server shutdown has been initiated
2017-07-04 15:32:56.68 spid8s      Error: 701, Severity: 17, State: 123.
2017-07-04 15:32:56.68 spid8s      There is insufficient system memory in resource pool 'internal' to run this query.
2017-07-04 15:32:56.77 spid8s      SQL Server shutdown has been initiated

This is a RAM Map screnshot of my server.

This machine is hosted in a VPS, and technical support says the "Driver locked" RAM is assigned on demand, so I can't find why SQL Server throws this memory error. Any ideas??


texts: How to find Jobs that ran between two times

texts:
Is there a query to check what a Jobs that ran between to given times. I can check the Jobs that are scheduled between two times but I dont want that.

Example I want to know what the Jobs that ran between 16:00:00 and 17:00:00

There can be cases where job scheduled at 15:00:00 might running for more than 1 hour I want those as well. I did search in google all I got is jobs those are scheduled between two times


texts: "Your password has expired" after restart mysql when updated mysql 5.7.8-rc(debian)

texts:
echo time login in mysql shell it shows:

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

and error log shows:
Your password has expired. To log in you must change it using a client that supports expired passwords.


texts: Redshift DISTSTYLE ALL table takes less disk space?

texts:
(Self-migrating from StackOverflow.)

We have a fat table of exactly 591 columns in Redshift, distributed by a key.

We were unaware that fat tables chew up space - 2 blocks per column per slice, minimum, and we aren't close to filling up the first block on any slice.

I misunderstood a coworker to recommend using "DISTSTYLE ALL." That seemed maddeningly counter-intuitive, but I set about trying this, and got a surprise that the disk usage of the new table, with the same rows and columns, is actually 1/20th of the size of the original table.

I'm using this query to get the sizes, but Aginity agrees when reporting disk usage for these tables:

select trim(pgdb.datname) as Database,
       trim(a.name) as Table,
       b.mbytes
   from stv_tbl_perm a
   join pg_database as pgdb on pgdb.oid = a.db_id
   join (select tbl, sum(decode(unsorted, 1, 1, 0)) as unsorted_mbytes,
                count(*) as mbytes
from stv_blocklist group by tbl) b on a.id=b.tbl
join ( select sum(capacity) as  total
  from stv_partitions where part_begin=0 ) as part on 1=1
where a.slice=0 and pgdb.datname = 'my_schema'
AND a.name IN ('my_table',
               'my_table_distall')
order by Database, Name;

Yielding this output:

Schema       Table                mbytes
my_schema    my_table             222080
my_schema    my_table_distall      10740

The reason I mentioned the exact number of columns is that 222080=594*5*32*2, which means we use two blocks for every 5*32 slices for every column (including Redshift's three hidden columns.)

The new table, with DISTSTYLE ALL, is taking up an average of 3.6 block per column per node.

This is very confusing. Why is my new table smaller by a factor of 20?

Due to the size of our data, I actually didn't expect it to get bigger, because even with all the data, we would not take up more than one block per slice per column, but smaller seems insane.

Is it just as simple as "DISTSTYLE ALL duplicates the table to one slice on each node?" So I only have one or two blocks used per node and column, rather than one block or so of usage per slice and column?


texts:
We use SQL Server 2012 Enterprise Edition and Transparent Data Encryption (TDE). Microsoft have a command to let you change the encryption on the database "ALTER DATABASE ENCRYPTION KEY". The docs page helpfully gives you the command, but it doesn't detail the process. I posted this question a the bottom of the page, but I thought that I might get a quicker answer here.

When I run this command, is the data left unencrypted on the disk at any point?
i.e. Does this command first unencrypt all the data using the old key/certificate and then re-encrypt it using the new one, which would mean that the data is unencrypted at some point in the process.
OR, is the process a pipe that takes in the data encrypted in the old key and spits out the data encrypted with the new key and the data is never stored on the disk unencrypted at any point.

Bonus question, what happens to the log file during the key change?

MS docs
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-encryption-key-transact-sql


texts:
I have a 3rd party app that uses SQL Server as a back end. Specifically we have it on SQL Server 2000, SQL Server 2008 and SQL Server 2008R2. Any solution must be available to all, but solutions for only some would still be of interest.

The app is bespoke, out of support and the vendor doesn't exist any more. What I want to do is when it writes an insert is have a trigger to read that insert and start off a complex process. However, the app doesn't return focus until the SQL insert completes which means a lengthy trigger will cause the app to think SQL Server isn't responding and cancel/rollback the transaction.

Is there any kind of trigger that will 'release' immediately and then continue on with its processing? Or perhaps some other solution that I can implement internal to SQL Server.


texts: Automatic aging-out (deletion) of old records in Postgres

texts:
Does Postgres have any features to support aging-out old records?

I want to use Postgres for logging, as a sort of queue, where records (log events) older than two weeks are automatically deleted.


texts: How to Filter values in two tables using inner join?

texts:
How to filter values in two tables using inner join.

There are two tables Employee and SalaryIncrement:

Sample data:

CREATE TABLE Employee
(
EmployeeId INT IDENTITY(1,1) NOT NULL,
FullName VARCHAR(255) NOT NULL,
MonthlySalary MONEY NOT NULL
PRIMARY KEY(EmployeeId)
)

INSERT INTO Employee (FullName, MonthlySalary) VALUES ('John', 12000.00)
INSERT INTO Employee (FullName, MonthlySalary) VALUES ('Lee', 20000.00)

CREATE TABLE SalaryIncrement
(
SalaryIncrementId INT IDENTITY(1,1) NOT NULL,
SalaryIncreaseFrom DATE NOT NULL,
AmountOfSalaryIncrease MONEY NOT NULL,
EmployeeId INT NOT NULL
PRIMARY KEY(SalaryIncrementId)
)

INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2012-02-05', 5000.00, 2)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2012-02-05', 1000.00, 1)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2013-05-05', 2000.00, 1)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2013-04-05', 1000.00, 2)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2014-08-08', 1500.00, 1)
INSERT INTO SalaryIncrement (SalaryIncreaseFrom, AmountOfSalaryIncrease, EmployeeId) VALUES ('2016-03-01', 500.00, 1)

From the above two tables I need to retrieve data like in this image below.

I also Need to display additional two columns Old Monthly Salary and New Monthly Salary calculated by Amount of Salary increased.

Result:


texts:
I'm working in SQL Server 2008, I have a varchar(255) column in my table, I want to select out all of the strings which contain the ':' character, and select the first part of the string before (but not including) the ':' character into a table variable so I can then work with this new data set. I have a piece of code which selects the substring I want from a string variable but I don't know how to do this as part of selecting all of the required strings in one go from the original table.

IF PATINDEX('%:%',@Login) > 0
BEGIN
    set @Login = SUBSTRING(@Login,0,PATINDEX('%:%',@Login))
END

I would prefer to not to have to loop over the table but I am not experienced with sql and so am not sure what is possible and what is not

So if I have in my original table:

Login varchar(255)
-----
dn\jblogs
dn\frobins:21 jul 2013
dn\tbecker
dn\ghammer:17 jul 2013

then the script should pull out into a table variable:

StrippedLogin varchar(255)
-------------
dn\frobins
dn\ghammer


texts: mysql replication successful but slave not replicating

texts:
I have created a mysql master-slave configuration and things look fine .
the  " show master status;" on slave doesnt show any error . this is the output

 Slave_IO_State: Waiting for master to send event
                  Master_Host: 109.123.100.58
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:

However on replication on slave doesnt seem to be happening when master undergoes change

On master . SHOW MASTER STATUS

+------------------+----------+--------------+-------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB        |
+------------------+----------+--------------+-------------------------+
| mysql-bin.000001 |   639495 |              | mysql,informationschema |
+------------------+----------+--------------+-------------------------+

on slave SHOW PROCESSLIST yields

+-----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
| Id  | User        | Host      | db   | Command | Time  | State                                                                 | Info             |
+-----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
| 174 | system user |           | NULL | Connect | 25777 | Waiting for master to send event                                      | NULL             |
| 175 | system user |           | NULL | Connect | 25777 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |
| 199 | root        | localhost | NULL | Query   |     0 | NULL                                                                  | show processlist |
+-----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

Am i missing something


texts: Replace long GROUP BY list with a subquery

texts:
This is a repost of my question on Stack Overflow. They suggested to ask it here:

I found an online article from 2005, where the author claims, that many devs use GROUP BY wrong, and that you should better replace it with a subquery.

I've tested it on one of my queries, where I need to sort the result of a search by the number of joined entries from another table (more common ones should appear first). My original, classic approach was to join both tables on a common ID, group by each field in the select list and order the result by the count of the sub table.

Now, Jeff Smith from the linked blog claims, that you should better use a subselect, which does all the grouping, and than join to that subselect. Checking the execution plans of both approaches, SSMS states, that the large group by requires 52% of the time and the subselect one 48%, so from a technical standpoint, it seems, that the subselect approach is actually marginally faster. However, the "improved" SQL command seems to generate a more complicated execution plan (in terms of nodes)

What do you think? Can you give me some detail about how to interpret the execution plans in this specific case and which one is generally the preferable option?

SELECT
    a.ID,
    a.ID_AddressType,
    a.Name1,
    a.Name2,
    a.Street,
    a.Number,
    a.ZipCode,
    a.City,
    a.Country
FROM dbo.[Address] a
INNER JOIN CONTAINSTABLE(
    dbo.[Address],
    FullAddress,
    '"ZIE*"',
    5
) s ON a.ID = s.[KEY]
LEFT JOIN dbo.Haul h ON h.ID_DestinationAddress = a.ID
GROUP BY
    a.ID,
    a.ID_AddressType,
    a.Name1,
    a.Name2,
    a.Street,
    a.Number,
    a.ZipCode,
    a.City,
    a.Country,
    s.RANK
ORDER BY s.RANK DESC, COUNT(*) DESC;

SELECT
    a.ID,
    a.ID_AddressType,
    a.Name1,
    a.Name2,
    a.Street,
    a.Number,
    a.ZipCode,
    a.City,
    a.Country
FROM dbo.[Address] a
INNER JOIN CONTAINSTABLE(
    dbo.[Address],
    FullAddress,
    '"ZIE*"',
    5
) s ON a.ID = s.[KEY]
LEFT JOIN (
    SELECT ID_DestinationAddress, COUNT(*) Cnt
    FROM dbo.Haul
    GROUP BY ID_DestinationAddress
) h ON h.ID_DestinationAddress = a.ID
ORDER BY s.RANK DESC, h.Cnt DESC;


texts:
I am creating a trend report, in which the x-axis labels are first laywr as year (2014), second quarter (1,2), third period (month) (3,1), fourth week (3,4,5,1,2).

I need to change these labels as y2014 for year, Q1,Q2 for quarter, month3,month1 for month, and W3,W4,W5 etc. for week.

All I need is to change the labels as they appear.


texts:
SQL Server 2012

Notification Source can have multiple deliveries type and each delivery type can have one notification templates (For future, Each delivery type can multiple templates). Notification source Table have unique name field.

Many to Many Relation beetween Notification_source, notification_template is maintained by notification_deliverytype_relations table.

In Notification_triggers table we have foreign of notification_deliverytype_relations table (id column)

Need to write update query for updating notification source related deliverytypes and templates (Many to many Relation).
Need to write Delete Query for the same scenario.

Parameters is Notificationsourceid,deliverytypeid and Notifcationtemplateid


texts: How to fail over availability groups on multiple remote servers

texts:
We have many SQL Server installations with availability groups. During our maintenance window, we will need to fail over all the HA groups to secondaries, patch the primary servers, then fail back, patch secondary servers. It's a tedious process to log on to each server and do the failover/failback job.

I am looking for a way to automate this process. Is there any way we can run  failover statement from our central management server to multiple linked servers, with high availability group name and remote server name as variable. Tried this and it doesn't work:

declare
@remote_server [char](24),
@hag_name  nvarchar(24),

set @hag_name  = 'group1'
set @remote_server = 'Server1'

execute ('ALTER AVAILABILITY GROUP ? FAILOVER', @hag_name) at @remote_server


texts: Need demo scripts and sample corrupted databases for corruption databases troubleshoot

texts: I am looking for a demo for recovery of corruption databases in different ways.  Is there any link for those examples to practice in LAB machines.


texts: Is there anyother alternative for order by varchar clause? At worst case consider I want to order by varchar column that is not indexed. When I tested a query with loads of data it took many times to execute. It is obvious that it will take much time. But is there any other alternative approach for this without indexing that column?


texts: SQL server availability group cannot access database after disabling sql service

texts:
I've completed this setup:

OS: Windows server 2016

Database: SQL Server 2017

2 node availability group configured with automatic failover on both nodes. Fileshare witness configured, both nodes enabled readable secondary replicas.

Database showing synchronized on both nodes.  Availability group state: Healthy, configured normal quorum and Fileshare witness and node 1, node 2 all three has 1 vote and state is online.

The question is I cannot access a database when I stopped SQL service on one of the nodes. Failover occurs to DB2 and status changed to primary when I stopped SQL service on DB1. When I try to select from BikeStores in DB2 this error shows:

database lacks a quorum of nodes for high availability.

Is my configuration wrong? or am I trying to achieve an impossible scenario in availability group?

Is there anyone who experienced the same situation?

I think I've configured quorum correct. Fileshare witness has 1 vote and the other 2 nodes have 2 votes. If 1 node fails quorum should've 2 online votes ready. So, why is the database lacks a quorum of the node for high availability when stop service on one of the nodes. I've tried stopping SQL service on primary & secondary result is same, when I stop service on secondary I cannot access the database from the primary node either. When two nodes online it is working perfectly fine. Can access r/w from primary and ReadOnly from secondary. Manual failover working fine.

The purpose of deploying availability group is to make high availability but it is not working when one node down.


texts:
UPDATE backup.records FORCE INDEX(IDX_records_deviceId) SET readyToBeDeleted = 1 WHERE deviceId = 226219 AND timestamp <= 1445255546
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76903 page no 7 n bits 872 index `IDX_records_deviceId` of table `backup`.`records` trx id 992346439 lock_mode X waiting

DELETE r.* FROM backup.records r  WHERE readyToBeDeleted = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 76903 page no 7 n bits 872 index `IDX_records_deviceId` of table `backup`.`records` trx id 992346377 lock_mode X locks rec but not gap

I have a persistent deadlock between these two queries. The second query, the delete one, is using the wrong index. I have an index that has only the readyToBeDeleted column.

Is there any way to force an index into a DELETE statement?
If not, could you propose a way to avoid this deadlock?

Table definition
CREATE TABLE `records` (
  `recordId` int(11) NOT NULL AUTO_INCREMENT,
  `deviceId` int(11) DEFAULT NULL,
  `timestamp` bigint(20) DEFAULT NULL,
  `priority` set('SOS','HIGH','NORMAL','GTWFIX') DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `speed` int(11) DEFAULT NULL,
  `hdop` double DEFAULT NULL,
  `sat` int(11) DEFAULT NULL,
  `di1` tinyint(1) DEFAULT NULL,
  `di2` tinyint(1) DEFAULT NULL,
  `di3` tinyint(1) DEFAULT NULL,
  `do1` tinyint(1) DEFAULT NULL,
  `do2` tinyint(1) DEFAULT NULL,
  `movement` tinyint(1) DEFAULT NULL,
  `immobilizer` tinyint(1) DEFAULT NULL,
  `analogInput` int(11) DEFAULT NULL,
  `iButtonId` varchar(20) DEFAULT NULL,
  `usb` tinyint(1) DEFAULT NULL,
  `battery` int(11) DEFAULT NULL,
  `ReportCommand` varchar(10) DEFAULT NULL,
  `reportType` tinyint(1) DEFAULT NULL,
  `sendTime` bigint(20) DEFAULT NULL,
  `state` varchar(4) DEFAULT NULL,
  `engineRPM` int(11) DEFAULT NULL,
  `fuelConsumption` double DEFAULT NULL,
  `temporaryRecordId` int(11) DEFAULT NULL,
  `temporaryRecordAlertId` int(11) DEFAULT NULL,
  `imei` varchar(50) DEFAULT NULL,
  `type` varchar(25) DEFAULT NULL,
  `activeStatus` tinyint(1) DEFAULT NULL,
  `doDebug` tinyint(1) DEFAULT NULL,
  `sim` varchar(50) DEFAULT NULL,
  `processingPriorityRecord` tinyint(1) DEFAULT NULL,
  `receivingIncompleteCommunication` tinyint(1) DEFAULT NULL,
  `lastLogTime` int(11) DEFAULT NULL,
  `lastLatitude` double DEFAULT NULL,
  `lastLongitude` double DEFAULT NULL,
  `SystemId` int(11) DEFAULT NULL,
  `viaGtw` tinyint(4) DEFAULT NULL,
  `readyToBeDeleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`recordId`),
  UNIQUE KEY `IDX_records_deviceId` (`deviceId`,`timestamp`),
  KEY `UK_records_Send_time` (`deviceId`,`sendTime`),
  KEY `IDX_records_readyToBeDeleted` (`readyToBeDeleted`,`deviceId`)
) ENGINE=InnoDB AUTO_INCREMENT=118492776 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT


texts: MariaDB 10.2 crash on innodb table corruption - trying to restore the database

texts:
I'm running MariaDB 10.2 on Windows and had some hard drive corruption on my server that messed up some innodb tables. Unfortunately this also affected backups, so I'm trying to dump the tables and restore them.

Problem is, even with force recovery set to 6, the server will crash when it reaches a corrupt row. Dumping the table with asc and then desc worked for one table, but not for another and any attempt to jump the row with limit and offset still crashes.

I saw that there was a innodb_pass_corrupt_table_action=salvage option in 10.1, but it is deprecated and ignored with 10.2. Is there a similar setting for 10.2? Are there any other options to force MySQL to dump the tables? Thanks!


texts:
This is my first official design after having read a coursebook in preparation for the upcoming semester.  The goal is to have a user-based form creation/publication and submission web application.

A user should be able to create/publish a form on the site (my web server).  Then users (both the creator of the form and others) should be able to submit the form.  After that, a report could be generated and show which user submitted which answers.  I tend to over-complicate statements when simple is better.

Here is how I envision the relationships working:

A USER belongs to many GROUPS
A GROUP consists of many USERS
Due to the many-many association, a composite entity is created.
A USER can create many FORMS
Each FORM belongs to one USER
A FORM provides many QUESTIONS
Each QUESTION belongs to one FORM
A QUESTION has many ANSWERS
Each ANSWER goes with one QUESTION
A FORM has many SUBMISSIONS
Each SUBMISSION is tied to one FORM
A SUBMISSION has many SUBMISSION_ANSWERS
Each SUBMISSION_ANSWER is part of one SUBMISSION


texts:
I have several SQL Server 2008 databases with transaction logs which have gotten out of hand and filled the drive. One of these logs was 68gb yikes.

I know better than to kill off my backup chain by truncating the log but when I do a shrink alone, on several of the databases I get no reclamation of space whatsoever and on those that I do, the amount is negligible. I have verified that the client is running t-log backups hourly and that these have succeeded for the last several weeks.

Obviously I'm missing something. Can someone point me in the right direction or am I just stuck with having to manually truncate and pray nothing happens in the meantime?


texts: SQL Server 2012 SQL Log "the log file is not using Unicode format":

texts:
On only one server of many, the SQL log is blank and shows a message "the log file is not using Unicode Format" when viewed via SSMS. The log is in the right location as specified in the service properties and is readable with notepad. Log looks normal, just won't show up in Log File Viewer in SSMS. Any ideas why?

SQL Server 2012 in active passive Windows failover cluster on Windows 2012. SQL Server ver 11.0.3368

This instance is the SQL Server for MS SCCM. It has trace flags -T8295 and -T4199 as service startup parms. These were added by SCCM when its DB's were installed.

Thanks


texts: In Oracle, why would I not always delete all old archive log files with RMAN?

texts:
backup archivelog all delete all input;

I do not understand why you would not always do this.  Why would I only want to delete archive log files from one location?

When Oracle saves the archive log files to multiple destinations, am I correct they are all duplicate and exactly the same in each location (and thus why the all vs not all)?


texts: Cassandra change replication factor hive datastore

texts:
I am trying to change replication factor of HiveMetaStore but I receive this message:

cassandra@cqlsh:system> alter KEYSPACE HiveMetaStore with replication =
    {'class': 'NetworkTopologyStrategy', 'DCassandra1': 1};

  InvalidRequest: code=2200 [Invalid query] message="Unknown keyspace hivemetastore"

HiveMetaStore keyspace exists. What could be the problem?

I changed replication factor from OpsCenter and it worked.

Why was I not able to change id from cqlsh?


texts: How do I copy my SQL Azure database to a local SQL Server instance?

texts:
I have an OLTP database hosted on a SQL Azure instance. I want to pull a copy of the database down from the cloud so I can run some heavy extracts and OLAP-style queries against it without impacting the source database.

How do I pull a copy of the database down to a local SQL Server instance?


texts: A average Mark overall for all of one student subjects

texts:
Lets say  i have a student table , a enlistment table that shows SubjectId and Results for each student  how would i get the overall average mark for a student that has more than 3 subjects
Create table  a_Student(
StudentId int(5) Not null,
StudentForename varchar (30) Not null,
StudentSurname varchar (45) Not null,
PRIMARY KEY(StudentId)
);
Create table a_Enlistment(
StudentId int(5) Not null,
SubjectId char(6) Not null,
results char (2),
primary key (SubjectId,StudentId),
foreign key (SubjectId) references a_Subject(SubjectId),
foreign key (StudentId) references a_Student(StudentId)
);

insert into a_Student (StudentId,StudentForename,StudentSurname)
values ("33447","Alan","Burns"),
("33672","Mark","Harrison",);

 Insert into a_Enlistment (StudentId,SubjectId,Results)
values
        ("33447","TMP243","78"),
         ("33447","AWD169","45"),
         ("33447","IIA887","48"),
        ("33672","TMP243","57"),
       ("33672","AWD169","67"),
      ("33672","IIA887","48"),

So i want lets say Alans overall mark for his 3 subjects  how would i do that?
Any help is welcomed thanks a lot


texts: what happens when InnoDB logspace is too small

texts:
I've just seen several mysql errors in my log:

InnoDB: ERROR: the age of the last checkpoint is X
InnoDB: which exceeds the log group capacity Y

I understand, that I do have to increase the innodb_log_file/buffer_size setting in order to fix the issue.
What I don't understand is, what happens to my data when this error occurs?
Does MySql skip the log buffer file and write the corresponding data to the bin log directly?


texts: Extended Events replacement for Virtual file stats

texts: Is there a way to monitor virtual filestats data with Extended Events, instead of doing (select * from sys.dm_io_virtual_file_stats) in SSMS?


texts: Is a query with UNIX_TIMESTAMP using indexes in mysql?

texts:
If I use

.. WHERE UNIX_TIMESTAMP(`some_timestamp`)>NOW()

in a SELECT, does this query still make use on an index, set on some_timestamp ?


texts: MySQL binlog does not show SQL statements

texts:
While checking the binlogs, it struck me that I cannot see any SQL statement. Here's an example:

$> sudo mysqlbinlog
    --database=securities
    --start-datetime='2015-11-26 13:00:00'
    --stop-datetime='2015-11-26 14:00:00'
    --base64-output=decode-rows
    --verbose
    mysql-bin.000011

and the output

BEGIN
/*!*/;
# at 1014869455
# at 1014869609
#151126 13:00:16 server id 1  end_log_pos 1014869680    Query   thread_id=1100645   exec_time=0 error_code=0
SET TIMESTAMP=1448539216/*!*/;
COMMIT
/*!*/;
# at 1014869680
#151126 13:00:18 server id 1  end_log_pos 1014869750    Query   thread_id=1100211   exec_time=0 error_code=0
SET TIMESTAMP=1448539218/*!*/;
BEGIN
/*!*/;
# at 1014869750
# at 1014869904
#151126 13:00:18 server id 1  end_log_pos 1014869975    Query   thread_id=1100211   exec_time=0 error_code=0
SET TIMESTAMP=1448539218/*!*/;
COMMIT

However, database securities is being replicated to the slaves without any trouble. Also, what do the # at 1014869904 mean? I would have expected to see statements there.


texts: Why does this function return an error by itself, but when called by another function it doesn't return an error?

texts:
I have a function that calls a sub-function as part of the function. When running the sub-function with the same data it throws an error, when running the main function it doesn't. I would have expected it to throw an error in both cases, why is this?

Here are the functions (I removed some details that didn't seem relevant and changed some table names):

CREATE OR REPLACE FUNCTION delete_old_cases_people() RETURNS TEXT AS $$
DECLARE
    return_value TEXT;
    people_deleted INTEGER;

BEGIN

    --Some other stuff here that calls some other functions, inserts things, etc.. unlikely to be related to the issue

    SELECT delete_people(
        ARRAY(
            SELECT DISTINCT a.people_id FROM view_people_to_delete a
        )
    ) INTO people_deleted;

    RETURN 'People Deleted: ' || COALESCE(TRIM(people_deleted::TEXT),'Error') || ';';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION delete_people(peoplearray INTEGER[]) RETURNS INTEGER AS $$
DECLARE
    return_value INTEGER;

BEGIN

    --Some other stuff here that calls some other functions, inserts things, etc.. unlikely to be related to the issue

WITH deleted AS
    (DELETE FROM people WHERE people.kindid = ANY(peoplearray) RETURNING *)
SELECT COUNT(*)::INTEGER INTO return_value FROM deleted;

RETURN return_value;
END;
$$ LANGUAGE plpgsql;

Running SELECT delete_old_cases_people(); returns "People Deleted: 0", which is true I guess but running SELECT delete_people(ARRAY(SELECT DISTINCT a.people_id FROM view_people_to_delete a)); throws an error. I would have expected SELECT delete_old_cases_people(); to throw an error as well, why is this?

FYI: SELECT version(); returns: "PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit" I think this is not fully updated, but I don't have direct control over this version.


texts: Group by and average from related tables

texts:
Ok so this is kinda weird I'm not sure how to exactly tackle it without it being a massive query, that might be slow so this is the tables as it is

Concerts:
name | country_id | ticket_price
foo    2            50
bar    1            40

Country:
id | name
1    UK
2    US

Region:
id | name
1    EU
2    America

Country_Region:
id | country_id | region_id
1    1             1
2    2             2

of course this is fictitious data/ schema but it does exactly what the real one does, I need to keep the country/region as a many to many so now what I need to do is get an avarage of say the ticket_price per month per region.

I'm really not sure how to get that query.

Thanks

Edit: I should also point out that there aren't just 2 regions and more can be added the whole time and country within them changed.


texts:
I have a table which is like this.

CREATE TABLE `candidate` (
  `candidate_id` int(11) NOT NULL,
  `site_id` int(11) NOT NULL DEFAULT '0',
  `last_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `middle_name` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone_home` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone_cell` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone_work` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address` text COLLATE utf8_unicode_ci,
  `city` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `state` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `zip` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
  `source` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date_available` datetime DEFAULT NULL,
  `can_relocate` int(1) NOT NULL DEFAULT '0',
  `notes` text COLLATE utf8_unicode_ci,
  `key_skills` text COLLATE utf8_unicode_ci,
  `current_employer` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `entered_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Created-by user.',
  `owner` int(11) DEFAULT NULL,
  `date_created` varchar(250) COLLATE utf8_unicode_ci DEFAULT '',
  `date_modified` varchar(120) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email1` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email2` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `web_site` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  `import_id` int(11) NOT NULL DEFAULT '0',
  `is_hot` int(1) NOT NULL DEFAULT '0',
  `eeo_ethnic_type_id` int(11) DEFAULT '0',
  `eeo_veteran_type_id` int(11) DEFAULT '0',
  `eeo_disability_status` varchar(5) COLLATE utf8_unicode_ci DEFAULT '',
  `eeo_gender` varchar(5) COLLATE utf8_unicode_ci DEFAULT '',
  `desired_pay` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `current_pay` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `is_active` int(1) DEFAULT '1',
  `is_admin_hidden` int(1) DEFAULT '0',
  `best_time_to_call` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

And some of the data is like this.

INSERT INTO `candidate` (`candidate_id`, `site_id`, `last_name`, `first_name`, `middle_name`, `phone_home`, `phone_cell`, `phone_work`, `address`, `city`, `state`, `zip`, `source`, `date_available`, `can_relocate`, `notes`, `key_skills`, `current_employer`, `entered_by`, `owner`, `date_created`, `date_modified`, `email1`, `email2`, `web_site`, `import_id`, `is_hot`, `eeo_ethnic_type_id`, `eeo_veteran_type_id`, `eeo_disability_status`, `eeo_gender`, `desired_pay`, `current_pay`, `is_active`, `is_admin_hidden`, `best_time_to_call`) VALUES
(1, 1, 'Faiyaj', 'Mohammad', '', '0959927638,0888227677,0520110007', '', '', '', 'Bagh-110025,BR-0520110007368', '', 'undefined', '', NULL, 0, 'This resume was parsed automatically. You should review it for errors.', '', '', 1, 1, '2018-07-17T16:50:04+05:30', '2018-07-17T16:50:04+05:30', 'faiz_akimam@yahoo.com', '', '', 0, 0, 0, 0, '', '', '', '', 1, 0, ''),

The query which i am running is this

SELECT candidate_id,date_created, first_name,phone_cell, email1, notes, city, current_pay, desired_pay, key_skills, source, current_employer
FROM candidate
WHERE entered_by = '1' AND date_created = '2018-08-23'

The problem is this this query is not returning anything. the date_created is in date time format.


texts: whats the use of an autoincrement primary column?

texts:
I would like to know when is it really needed to use an auto increment primary column, what is the value it adds?

my colleague suggests to have a separate auto increment column for a table as a primary key. It should be named ‘_id’. Primary key should not store any business data.

Another colleague is of different opinion that we can have a non-auto increment column to be a primary key as we plan not to have duplicates.

One approach without auto-increment primary column:

1)  Create Tables in Database with the below fields

Table I
a.  SLOC_ID (Primary Key)
b.  Gerrit
c.  Parent Key
d.  Files Inserted
e.  Files Deleted
f.  Gerrit Last User (Non Service Accounts) update time
g.  Current_Patch_Number

Table II
a.  SLOC_ID (One to Many Relationship)
b.  File Path
c.  File Name

Another approach with auto-increment is below

PK,FK   Column Name
PK(autoincrement)   sloc_id
FK  change_list_id

PK,FK   Column Name
PK  sloc_details_id
FK  sloc_id
    linesmodified
    lines deleted
    filepath
    latestpatch
    modification_type

where can I find more details here?which of the best solution?