texts: Best Practice to Archive Old Data in MySQL

texts:
I'm using a MySQL database to store logs from multiple servers. The database's growth on average is 50 GB per day. Its total size is now 1.7 TB.

It has ID and datetime columns.

I want to move data older than one week to another database on the same server.

What are the steps needed for this?

As of now I do this:

Create the Archive Database and table.
Insert into ArchiveDB.table select * from old_tbl where datetime
<= Archivedate
Then DELETE FROM old_tbl WHERE datetime <= Archivedate

I want to implement this with the following conditions:

Insert and delete via batches.
Reclaim the space after deleting from the old database.
Update stats without downtime.

...Or if you know of any best practice then let me know.


texts: db schema for a object with multiple foreign key tables

texts:
I have a question table which contains question_id, question_text and choice_type_id ( say freetext, binary choice and multiple choices )

Now, the question table (which has question_id, question_text, choice_type_id) has a foreign key to the choice type table ( choice_type_id, choice_type_desc) . Now all the different choice types have a different schema.

For multiple choice,  I want to store multiple choice fields against a question. For binary choice , I want to store two choices against a question and for freetext, I have no choices.

How do I populate a question's choices based on its type and model this in a sql like db. I would further want to see user interactions with each of these questions and choices within that question ( i.e. the user chose which choices or entered what text for the given question)

I can model the question_choice table as a big table which can have all the three types and based on the type certain columns can have values and all other remain null.

This would be a very sparse representation and I'm not sure how this will scale.

What would be the best schema design considering I want fast reads and the question types will keep increasing further.


texts: How to design database tables using normalization in better manner in SQL?

texts:
I have designed my tables in below manner.

Countries Table:-

PkId --- + Country Name --- + StatusFlag
1        | India            | L
2        | China            | L
3        | Sri Lanka        | L

StateNames Table:-

PkId --- + CountryFkId --- + State Name --- + StatusFlag
1        | 1               | Maharastra     | L
2        | 1               | Madhya Pradesh | L
3        | 1               | Utter Pradesh  | L

Cities Table:-

PkId --- + StateFkId --- + City Name --- + StatusFlag
1        | 1             | Mumbai        | L
2        | 1             | Pune          | L
3        | 1             | Nagpur        | L

PinCodes Table:-

PkId --- + CityFkId --- + PinCode --- + Area ---       + StatusFlag
1        | 1            | 400037      | Antop Hill     | L
2        | 2            | 412206      | Ambade         | L
3        | 3            | 441108      | Ashta          | L

Customers Table:-

 PkId --- + CustomerId --- + CustomerName --- + PinCodeFkId--- + StatusFlag
 1        | C00001         | John             | 1              | L
 2        | C00002         | Ram              | 2              | L
 3        | C00003         | Anwar            | 3              | L

Query:-

Select C.CustomerId, C.CustomerName, P.Area, CT.CityName, S.StateName, CNT.CountyName
From dbo.Customers C
Inner Join dbo.PinCodes P On C.PinCodeFkId = P.PkId
Inner Join dbo.Cities CT On P.CityFkId = CT.PkId
Inner Join dbo,StateNames S On CT.StateFkId = S.PkId
Inner Join dbo.Countries CNT On S.CountryFkId = CNT.PkId

But my colleagues told that if I write like this, then huge logical reads will occur. Also they told that these table structures follow too much normalisation. Instead of designing Customers table like above, they have given other suggestion. i.e., Maintaining CountryName, StateName, CityName, PinCode and Area in Customers table itself.

Restructured Customers Table:-

PkId --- + CustomerId --- + CustomerName --- + PinCode ---   + Area ------- + CityName --- + StateName --- + CountryName--- + StatusFlag
1        | C00001         | John             | 400037        | Antop Hill   | Mumbai       | Maharastra    | India          | L
2        | C00002         | Ram              | 412206        | Ambade       | Pune         | Maharastra    | India          | L
3        | C00003         | Anwar            | 441108        | Ashta        | Nagpur       | Maharastra    | India          | L

Restructured Query:-

Select CustomerId, CustomerName, Area, CityName, StateName, CountyName
From dbo.Customers

In above query, I didn't put any join conditions with other relevant tables. Which kind of table design best suitable for normalisation and better performance when table has huge number of records?


texts:
I am very sorry if this question is structured in not a very helpful manner or the question itself is not a very good one!

I need to update a MSSQL table call component every 10 minutes based on information from another table call materials_progress

I have nearly 60000 records in component and more than 10000 records in materials_progress

I wrote an update query to do the job, but it takes longer than 4 minutes to complete execution!
Here is the query :

UPDATE component
SET stage_id =
CASE
    WHEN t.required_quantity <= t.total_received THEN 27
    WHEN t.total_ordered < t.total_received THEN 18
    ELSE 18
END
FROM
    (
    SELECT
        mp.job_id,
        mp.line_no,
        mp.component,
        l.quantity AS line_quantity,
        CASE WHEN mp.component_name_id = 2 THEN l.quantity*2
            ELSE l.quantity
        END AS required_quantity,
        SUM(ordered) AS total_ordered, SUM(received) AS total_received ,
        c.component_id
    FROM line l
    LEFT JOIN component c ON c.line_id = l.line_id
    LEFT JOIN materials_progress mp ON l.job_id = mp.job_id AND l.line_no = mp.line_no AND c.component_name_id = mp.component_name_id
    WHERE
        mp.job_id IS NOT NULL
        AND (mp.cancelled IS NULL OR mp.cancelled = 0)
        AND (mp.manual_override IS NULL OR mp.manual_override = 0)
        AND c.stage_id = 18
    GROUP BY  mp.job_id,  mp.line_no,  mp.component, l.quantity, mp.component_name_id, component_id
    ) AS t

WHERE
    component.component_id = t.component_id

I am not going to explain the scenario as it too complex.. could somebody please please tell me what makes this query this much expensive and a way to get around it?

Thank you very very much in advance!!!

Edit 1:
Here is the execution plan;


texts: Time taken to execute query seems longer than time measured in query...?

texts:
I'm trying to optimise a stored procedure.  I have inserted some manual profiling into my SP by just using a date variable and then displaying time elapsed by using datediff.

E.g.

DECLARE @MyDate datetime = GetDate()
PRINT '..profiling text...'
PRINT DateDiff(ms, @MyDate, GetDate())
SET @MyDate = GetDate()

<execute some actions here>

PRINT '..elapsed time...'
PRINT DateDiff(ms, @MyDate, GetDate())
SET @MyDate = GetDate()

When I run this in a query window of SQL Server Management Studio the total elapsed time of my profile equates to about 5 seconds.  However, the duration of the whole query is more like 14 seconds as indicated in the query properties in the bottom right corner (and when I time it manually with a watch).

Is there any reason why this would be happening?  There is nothing wrong with where I have placed or declared my date variables. There is definitly a mismatch in time output via datediff and time taken for the query.

It should be noted also that while optimising I am not returning any large tables of data.  I'm simply inserting them into temp tables to ensure that I don't have the overhead of returning results.


texts: Concatenate multiple columns into one column (,) separated

texts:
In SQL database that stores Images in a parcelImages table.Since each parcel can take part in multiple Images and each images can involve multiple parcels.

I want to get each parcel having Image value with comma separated values as shown above

I tried with this SQL Query

SELECT ParcelId,id,concat(ParcelId, ' ', id)
FROM ParcelImages
WHERE Id IN (43418,43464,43465,43466,43467,43470,43471,43482,43483)

but expected result not get. How can this be done?

I have also tried an INNER JOIN How can I achieve this, I'm kinda stuck at the early beginning.

If there is a table called STUDENTS

ID                     StudentName
----------             -------------
1                      Mary
1                      John
1                      Sam
2                      Alaina
2                      Edward

Result I expected was:

ID              StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward


texts: Query hangs forever at first but works after one day

texts:
I have two schemas namely A and B. I regularly copy contents of 10 tables from A to B. Here's is how I do it

First I rename the original tables in schema B
Then, using SQL Developer I copy the table to copy from schema A to B. (I right click the table to copy and select Copy and choose the destination schema)
Then I create all the indices that exist in the original tables.(Copying tables does not copy indexes, so you have to create them manually)

After the process is over, I run queries against all the tables. Everything goes fine with 9 of them, but with one of the tables the executed query hangs. The next day, when we run the same query against the same table, the execution takes only a moment. I have no idea what the problem is.  I tried not adding indexes but it didn't help. I know he way we copy table content might seem weird.


texts: Selecting "consecutive" rows with same values

texts:
I have a table with the following data:

userID  tStamp                status
------  -------------------   ------
Jason   2017-10-18 03:20:00   idle
Brown   2017-10-18 03:20:28   idle
Brown   2017-10-18 03:25:28   idle
Brown   2017-10-18 04:00:28   active
Brown   2017-10-18 04:10:28   active
Brown   2017-10-18 04:35:28   idle
Brown   2017-10-18 04:45:28   idle

I want to extract the consecutive rows with the same status. For example, I want to see the time spent by a user in the 'idle' status, followed by 'active' status, followed by 'idle' status, and so on.

How do I do this in a single SQL query?

My desired output is the following:

userID        staus          Duration_in_this_status (min)
------  -------------------   ------
Jason         idle             ---
Brown         idle              5
Brown         active           10
Brown         idle             10


texts: MariaDB Galera slave thinks it's replicating, but it isn't

texts:
I have a MariaDB 10.3 Galera slave node with general logging turned on.  It's consuming binlogs generated by a MySQL 5.5 NDB cluster.  The binlog format is row-based in both databases.

SHOW ALL SLAVES STATUS; indicates that Read_Master_Log_Pos and Exec_Master_Log_Pos are caught up to the position on the master DB, Seconds_Behind_Master is 0, and no errors are logged.

Making an INSERT/UPDATE/DELETE on the master database immediately causes the following entries in the slave's general log:

190412 21:02:04     74 Query    BEGIN
                    74 Query    COMMIT

Additionally, the Read and Exec positions will tick upwards, Executed_log_entries will increase, and the relay binlog will get a new entry.  Every metric and status indicator I know of suggests that the slave believes it's replicating, but the data doesn't replicate.  DDL statements do replicate, however.

There are no filtering rules of any kind on the master/slave nodes.  Replicate_Do_DB, Replicate_Ignore_DB, Replicate_Do_Table, Replicate_Ignore_Table, Replicate_Wild_Do_Table, and Replicate_Wild_Ignore_Table are all empty.

Where do I even start troubleshooting this?  What could cause the slave to silently fail to replicate?


texts: PostgreSQL Trigger to keep track of table changes

texts:
I am trying to create a trigger (Postgres 9.6) to track changes made to a table. This is my approach:

CREATE OR REPLACE FUNCTION taxon_history() RETURNS trigger AS
$BODY$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO history.taxon(operacao, "data", tecnico, original_oid, taxon)
        VALUES ('DELETE', current_timestamp, current_user, old.oid, old.taxon);
        RETURN old;

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO history.taxon(operacao, "data", tecnico, original_oid, taxon)
        VALUES ('DELETE', current_timestamp, current_user, old.oid, old.taxon);
        RETURN old;

    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO history.taxon(operacao, "data", tecnico, original_oid, taxon)
        VALUES ('INSERT', current_timestamp, current_user, new.oid, new.taxon);
        RETURN old;
    END IF;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER history_taxon
AFTER INSERT OR UPDATE OR DELETE ON taxon
FOR EACH ROW EXECUTE PROCEDURE taxon_history();

However when something changes in the (public).taxon table, no record is added to the history.taxon table. I also don´t get any error message so I am in the dark on why nothing is happening. What am I doing wrong?


texts: Mariadb configuration is not loaded from my.cnf

texts:
My mariadb configuration in my.cnf is not getting loaded for "log-error" and "pid-file". I have checked other configurations params are loaded.

[root@kvm10 ~]# cat /etc/my.cnf
[mysqld]
!includedir /etc/mysqld/conf.d
datadir=/mnt/mgmt/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/mnt/mgmt/var/lib/mysql_tmp
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
### TRT-3229 #####
sync_binlog=1
innodb_flush_method=O_DIRECT
innodb_support_xa = 1
myisam_repair_threads = 2
myisam_recover_options = FORCE
###################
innodb_file_per_table=1
innodb_log_buffer_size = 8M
table_open_cache=256
max_heap_table_size=256M
### TRT-4685 ###
max_connections=500
################
innodb_log_file_size = 512M

[mysqld_safe]
log-error=/var/log/mariadb/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@kvm10 ~]#

But the configuration value for log-error & pid-file is not picked by MariaDB.

[root@kvm10 ~]# mysql -e "show variables like 'pid_file'"
+---------------+-----------------------------------+
| Variable_name | Value                             |
+---------------+-----------------------------------+
| pid_file      | /mnt/mgmt/var/lib/mysql/kvm10.pid |
+---------------+-----------------------------------+
[root@kvm10 ~]# mysql -e "show variables like 'log_error'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_error     |       |
+---------------+-------+
[root@kvm10 ~]#

Am I missing somethig or making some mistake. I have checked the files permissions in /var/log/mariadb/mysqld.log & /var/run/mysqld/mysqld.pid.


texts:
My mysql data is like this

CREATE TABLE `extra_field` (
  `extra_field_id` int(11) NOT NULL,
  `data_item_id` int(11) DEFAULT '0',
  `field_name` varchar(255) DEFAULT NULL,
  `value` text,
  `import_id` int(11) DEFAULT NULL,
  `site_id` int(11) DEFAULT '0',
  `data_item_type` int(11) DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

And the data is like this.

INSERT INTO `extra_field` (`extra_field_id`, `data_item_id`, `field_name`, `value`, `import_id`, `site_id`, `data_item_type`) VALUES
(1275, 1219, 'Education', 'MBA', 0, 1, 100),
(1276, 1219, 'Contract or Permanent', 'Permanent', 0, 1, 100),
(1277, 1219, 'Notice period', '30 days', 0, 1, 100),
(1278, 1219, 'Total Experience', '12 yrs', 0, 1, 100),
(1279, 1219, 'Relevant Experience', '12 yrs', 0, 1, 100);

I want to write a custom query which returns the value in this way.

id      Education   Contract or permanent   Notice Period   Total Experience    relevant experience
1275    MBA         Permanent               30 days         12 yrs              12 yrs

How can i do this? The id will never repeat. and the values will be having only one entry.


texts:
In MySQL error log I am getting this warning message:

  [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly

Is it causing any problem in the server, and how I can avoid it?


texts: Extend mysql 5.5 tablespace to another drive on windows

texts:
I have greater than 300gb mysql database which is running out of disk space in D: drive on windows.( Presently the ibd data file is in D: drive which is running out of space). I have a F: drive which is having around 200 GB free space.

1)How can i extend the ibd data file to f: drive so that all new inserts go in f drive ?
Variable innodb_file_per_table is OFF currently.
Innodb_data_filepath : ibdtaxx:10M:autoextend

Please leave your suggestions on what will be the best course of action to follow in this situation.


texts: Is it possible to mysqldump a subset of a database required to reproduce a query?

texts:
Background

I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research).

Question

Is there a way that I can incorporate this select statement into a script that dumps the queried data into a new database, such that the database could be installed on a new mysql server, and the statement would work with the new database. The new database should not contain records in addition to those that have been used in the query.

Update:
For clarification, I am not interested in a csv dump of query results. What I need to be able to do is to dump the database subset so that it can be installed on another machine, and then the query itself can be reproducible (and modifiable with respect to the same dataset).

Example

For example, my analysis might query a subset of data that requires records from multiple (in this example 3) tables:

select table1.id, table1.level, table2.name, table2.level
       from table1 join table2 on table1.id = table2.table1_id
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum');


texts:
I administer a Microsoft SQL Server 2012 instance on VMWare, and I need to change the IP address.  Reading similar questions here on the site I am hopeful that the change will be a simple one, but my version in particular hasn't been discussed.

Running SQL Server 2012 on Windows Server 2012 R2, are there any dangers or relevant precautions I should take before changing the IP address?


texts: WHAT RDBMS ( SQL SERVER/ ORACLE ) close to manage MYSQL(DBA)

texts:
i have a requirement for learning MYSQL for DBA. here all training institutes teach on ORACLE AND SQL SERVER.
SQL is mandatory to work on RDBMS. what RDBMS SYNTAX CLOSE TO MYSQL for DBA(backups, restore, performance tuning, etc  )


texts: Can SQL Server database be changed from NoRecovery to Standby?

texts:
I'm receiving a log shipped database (and daily transaction logs) from an outside vendor -- to our FTP.  No internal access allowed.  No connection between the two databases.

I've restored the database (and transaction logs) "with NoRecovery" to allow further transaction logs to be applied, but it needs to be in Standby mode to allow for Users to read the data.

Is there a way to convert a database from NoRecovery to Standby?  [So that future transaction logs can be applied]


texts: Dropping a schedule whose name contain '.'

texts:
I was attempting to create a schedule called MYSCHEMA.MYSCHED in my Oracle database using SQL Developer 4.0 Early Access, and there appears to be a bug resulting in a schedule named MYSCHEMA. to be created.

How could I remove this schedule? This does not work:

EXECUTE DBMS_SCHEDULER.DROP_SCHEDULE('EDP_MANAGER.');

(results in 00931. 00000 -  "missing identifier")

While this:

EXECUTE DBMS_SCHEDULER.DROP_SCHEDULE('EDP_MANAGER\.');

results in "invalid character" instead.


texts: What query specific counters / metric if any are exposed for monitoring outside of SQL Server itself?

texts:
I am aware of generic Microsoft SQL Server Performance counters and other ways to monitor general SQL server performance through monitoring system, files, and executable performance, and its internal metrics etc. and I monitor that today through an enterprise wide monitoring solution.

I am wondering what, if any, query specific metrics (specific stored procedures) are exposed outside of Microsoft SQL server that I could query, collect, and monitor outside of SQL Server consoles, AND not requiring extensive permission. The monitoring account for example cannot run sp_who and sp_who2 and I am currently concerned with specific stored procedures.

So for example if I know an application that when it performs a particular task it kicks off sp_Task1, and then sp_task2.. etc.  I need to monitor, alert on long duration, and track long term run times of each of those tasks through an already adopted enterprise monitoring solution that does not currently have the ability to do it itself.

I need this as the application is 'sensitive' to long run times and it can set off a chain of other negative events. Using the image below, I am more concerned about width of the wave front trough-to-read trough than total height or peak-to-peak as if the waves touch things go down hill for a lot of applications really fast.

Though I am trying specifically know how to monitor the run time of a stored procedure from outside of Microsoft SQL server consoles...

My question is: What query specific counters / metric if any are exposed for monitoring outside of SQL Server itself in MS SQL Server 2008 or later?


texts: Specify Server for DBMS_Scheduler Job in Policy Managed RAC

texts:
A unit test requires a dbms_scheduler job to run on the same RAC node as the unit test is being run from.  I know that with an Admin managed database this could be done by creating a service that limited the available instances and then using that service in a job class the job uses. My question is, how can this be done in 11.2 with policy management?

Pools can be created that have only a single server in them and databases can be assigned to multiple pools, but as I understand it, a server can only be assigned to a single pool.  Therefore, a service can't be created that uses a single server and still have other services that use a pool defined with multiple servers including that one.

I also know that services can be created as either SINGLETON or UNIFORM, but since SIGNLETON doesn't provide for allowed servers or even preferred servers, I'm not sure how this would help.

Surely I am missing something that makes this all possible.


texts:
I am using the following code with Ola Hallalengren's script to run copy only backups on a 3 node AAG setup. I have configured to run backups using the "prefer secondary" option .   When I triggered the job from the primary replica, only db's outside the AAG are backing up and when I trigger it from one of the secondary only AAG db's are backing up.

The priority for backups has been given like so

Primary - 50%

Secondary - 50%

Secondary1 - 40%

What am I missing?Please suggest. Thanks!

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\\SQL02\Backups', @CopyOnly = 'Y', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = NULL, @CheckSum = 'Y', @LogToTable = 'Y'" -b


texts: Sql server - Rebuilding Indexes - Does it affect foreign keys?

texts: i am looking at speeding up the performance of my database, and thought that Rebuilding some indexes might help. My question is, will this invalidate Foreign Keys from other tables pointing to the table I am rebuilding, or will the links be preserved?


texts: Column must appear in the GROUP BY clause or be used in an aggregate function when using CASE statement in ORDER BY clause

texts:
I have a PostgreSQL database that contains events. Events can have multiple date ranges. Date ranges are stored in a separate table. I’d like to get the IDs of current and upcoming events. IDs of current events should be outputted first, followed by IDs of upcoming events.

This is my current SQL query:

SELECT date_ranges.event_id
FROM date_ranges
WHERE date_ranges.through_date >= current_date
GROUP BY date_ranges.event_id
ORDER BY CASE WHEN date_ranges.from_date <= current_date THEN 1 ELSE 2 END ASC;

However, it doesn’t work:

ERROR:  column "date_ranges.from_date" must appear in the GROUP BY clause or be used in an aggregate function
LINE 5: ORDER BY CASE WHEN date_ranges.from_date <= current_date THE...

How can I work around this problem in this case?


texts: Unable to connect to SQL server instance ubuntu 18.04 via SSMS which worked peviously

texts:
I am running SQL server on ubuntu 18.04 which I was able to connect to successfully via SSMS. But I am not able to connect anymore.
My first instinct was to check if the SQL server was running by executing

sudo systemctl status mssql-server

lo and behold the SQL server instance was active so I rebooted the entire server and ran the above command again and saw the following:

mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Wed 2019-10-16 16:49:17 UTC; 1min 13s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Process: 1025 ExecStart=/opt/mssql/bin/sqlservr (code=exited, status=1/FAILURE)
Main PID: 1025 (code=exited, status=1/FAILURE)
Oct 16 16:49:17 lamp-xxxxx systemd[1]: mssql-server.service: Main process exited, code=exited, status=1/FAILURE
Oct 16 16:49:17 lamp-xxxxx systemd[1]: mssql-server.service: Failed with result 'exit-code'.
Oct 16 16:49:17 lamp-xxxxx systemd[1]: mssql-server.service: Service hold-off time over, scheduling restart.
Oct 16 16:49:17 lamp-xxxxx systemd[1]: mssql-server.service: Scheduled restart job, restart counter is at 3.
Oct 16 16:49:17 lamp-xxxxx systemd[1]: Stopped Microsoft SQL Server Database Engine.
Oct 16 16:49:17 lamp-xxxxx systemd[1]: mssql-server.service: Start request repeated too quickly.
Oct 16 16:49:17 lamp-xxxxx systemd[1]: mssql-server.service: Failed with result 'exit-code'.
Oct 16 16:49:17 lamp-xxxxx systemd[1]: Failed to start Microsoft SQL Server Database Engine.

I checked the logs at /var/opt/mssql/log/errorlog and found this .

2019-10-14 13:52:56.16 spid51      CImageHelper::Init () Failed load of
symbol file with path = /binn/;/BINN - Not enough storage is available to
process this command.

2019-10-14 13:52:56.17 spid51      CImageHelper::Init () Failed load of
symbol file with path = /binn/;/BINN - Not enough storage is available to process this command.

2019-10-14 13:52:56.19 spid51      Error: 19099, Severity: 16, State: 1.
2019-10-14 13:52:56.19 spid51      Trace ID '1' was stopped because of an error. Cause: 0x80070005(Access is denied.). Restart the trace after correcting the problem.
2019-10-14 13:52:56.20 spid51      Error: 17053, Severity: 16, State: 1.
2019-10-14 13:52:56.20 spid51      FCB::MakePreviousWritesDurable: Operating system error (null) encountered.
2019-10-14 13:52:56.21 spid51      Error: 9001, Severity: 21, State: 1.
2019-10-14 13:52:56.21 spid51      The log for database 'master' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.
2019-10-14 13:52:56.24 spid51      Error: 9001, Severity: 21, State: 16.

I'm not an expert and don't know the next move to make.


texts: Add host to existing user @ Mysql with the same permission

texts: I would like to add another host for existing users at my DB. I want this new record to have a same privileges with the existing user as well. I saw this thread https://stackoverflow.com/questions/19281305/adding-new-host-entries-to-existing-mysql-users but it seems it does not copy the whole privileges that the existing users have. Any programmatic solution instead of manually adding the user one by one?


texts: How to check if current SQL session has uncommited changes?

texts:
Say you are in SQL Developer and want to check if the current user session (i.e. your connection) has uncommitted changes.

If you quit SQL Developer, it does such a check and displays a dialog box how to proceed. I want to check it without exiting - e.g. via executing a special SQL statement or a SQL Developer action.

Ideally, it would be great if one could configure SQL Developer such that it is visually indicated if the current session is 'dirty' (i.e. has uncommited changes) - e.g. via displaying a red margin around the worksheet.


texts: Is there a performance consideration if stored procedures are in a separate database than the source data?

texts:
This applies to a SQL Server 2012 instance.  We are considering structuring things to where we have a database that contains our source data, and then separate database (on the same instance) that would contain any stored procedures that query that data.  The stored procedures would then explicitly reference the "data" database when pulling any data.

Would there be any query performance problems in structuring things this way?

Edit: I'm coming at this from a developer's perspective; this is being proposed from the DBA level and I couldn't tell you what might be behind it. Best I can tell it's some sort of separation of interests - the data database is loading data from a 3rd party app so we can more easily report on it. I think they want to separate out stored procs we create to not clutter the "source data" db. This db is not a vendor database per se, but the application's data is unavailable in its native format. So there are daily processes that import the vendor data via ODBC into SQL Server so we can more easily get at it.


texts: Error 0x80131904 failed to load assembly id 65536 / HRESULT: 0x8013150A for microsoft.sqlserver.integrationservices.server

texts:
I am running SSIS Packages through an Agent job and getting error 0x80131904:

Message:

  Executed as user: NT Service\SQLSERVERAGENT.
  Microsoft (R) SQL Server Execute Package Utility
     Version 14.0.3023.8 for 64-bit
     Copyright (C) 2017 Microsoft. All rights reserved.

  Started:  1:00:00 PM
  Failed to execute IS server package because of error 0x80131904. Description:
  An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:

  System.IO.FileLoadException: Could not load file or assembly 'microsoft.sqlserver.integrationservices.server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

  System.IO.FileLoadException:
        at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
        at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
        at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)
        at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
        at System.Reflection.Assembly.Load(String assemblyString)

  Source: .Net SqlClient Data Provider
  Started:  1:00:00 PM
  Finished: 1:00:00 PM
  Elapsed:  0.328 seconds.

  The package execution failed.
  The step failed.

I researched and found that I should enable CLR and set TRUSTWORTHY to ON for the DB. I did that and in addition checked the database owner account; it's enabled and not orphaned. Still getting the error. I would highly appreciate any help on this.


texts:
DataGrip editor has command ⌘-/ to "Comment with line comment", in SQL that means adding -- to beginning of line.

On my MacBook Pro with Finnish keyboard, / is located in shift-7. When I press ⌘-shift-7, DataGrip opens search box in Help menu or flashes Code menu title, instead of commenting current line. Selecting "Comment with line comment" from Code menu works as expected.

Is there a keyboard shortcut that works on my keyboard?


texts: how to convert this varchar to datetime format?

texts:
I have a varchar column has data like (2015-12-02 20:40:37.8130000) which i have imported from csv file to SQL table. i need to convert this column to datatime as (2013-03-17 14:15:49.687).

 ALTER TABLE track_date alter column start_time datetime.

getting error as :

  Msg 241, Level 16, State 1, Line 1
  Conversion failed when converting date and/or time from character string.

while converting using convert function , getting same error:

SELECT convert(datetime,start_time,5) from track_date

  Msg 241, Level 16, State 1, Line 1
  Conversion failed when converting date and/or time from character string.

Can you please help, how to convert this varchar data to datetime format?


texts: Unmarshalling Hierarchical JSON in SQL Server 2016+

texts:
We are attempting to parse out data from a JSON file with hierarchical data. Right now, I am just trying to extract the inner array's data. The basic structure of the document is:

[
  {
    "url": "https://www.google.com",
    "image-url": "https://www.google.com/imghp",
    "labels": [
                {
                  "source": "Bob, Inc",
                  "name": "Whips",
                  "info": "Ouch"
                },
                {
                  "source": "Weezles of Oregon",
                  "name": "Chains",
                  "info": "Let me go"
                },
               ...
              ],
    "Fact": "Fictional"
  },
  ...
]

The final output required for this JSON object would be something like url, Fact, name, info (one row per label per object).

I can easily query the outer array of objects with OPENJSON() and JSON_VALUE(). However, I have not deduced how to extract the "labels" array so I can use OPENJSON on it to access the name and info fields. And that's where I need help.

If I can get the Labels array out, I can go from there. If I am missing something obvious, kick me an let me know what it is. But I've spent about five hours on this so far, and the solution isn't obvious (to me, at least)


texts: Editing file /etc/mysql/my.cnf for Master-slave repllication

texts:
here is my "my.cnf" file:
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise, they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

I followed this tutorial to configure Master-slave replication
[https://lucasvidelaine.wordpress.com/2017/12/14/replication-maitre-esclave-mysql/][1]
but after writing this on the file MySQL do not restart
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
#bind-address=127.0.0.1
server-id=1
log_bin=/var/log/mysql/mysql-bin.log
expire_logs_days=10
max_binlog_size=100M
binlog_do_db=maitredb

anybody have an idea, please?


texts:
I have a large PostgeSQL 9.1 database in production use. I want to free up some disk space. There is a large index, and I'm unsure if it's being used.

select * from pg_stat_all_indexes where indexrelname = 'myindexname';
 relid | indexrelid | schemaname |     relname     |     indexrelname      | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-----------------+-----------------------+----------+--------------+---------------
 20625 |   19563303 | public     | mytablename     | myindexname           |   575598 |       281635 |             0
(1 row)

Is this index being used? idx_tup_fetch is 0. Does that mean it's not being used? Can I drop the index without affecting any reads/queries?

This database is in production, real world use. This index was recently rebuilt, and there has been real world use since. If it hasn't been used since then, it will never be.

The index is about 130G and the table 50G.


texts: Form groups computing max - min depending on number of peers

texts:
This is table:

CREATE table point (
         datetime timestamp,
         value float)

I have data in some range of time

7:00 - 7:15:   0   5 : 5

7:15 - 7:30:   -   - : null

7:30 - 7:45:   7   - : 2

INSERT INTO point values ('2011-01-01 07:01:00', 0);
INSERT INTO point values ('2011-01-01 07:14:00', 5);
INSERT INTO point values ('2011-01-01 07:31:00', 7);

This is the the query:

SELECT date_trunc('minute', datetime) - ((EXTRACT(MINUTE FROM
      datetime)::int) % 15) * interval '1 minute',
    MAX(value) - MIN(value)
    from point
    where datetime BETWEEN '2011-01-01 07:00:00' and '2011-01-01 07:45:00'
    group by 1

This is the current result:

2011-01-01 07:00:00 |        5
2011-01-01 07:30:00 |        0

My expect result is:

2011-01-01 07:00:00 |        5
2011-01-01 07:30:00 |        2

The second result I want is 7 - (5 - 0) = 2. If previous time also has only one value, it also should be 7 - (5 - 0) = 2.


texts: Best Practices When Inserting Data (opinion question)

texts:
I'd just like to see what's the best practice when interfacing with a database.
When inserting/modifying data in a database, is it best practice to do logic checking prior to interfacing with the DB (for instance in php), or just let MySQL do the checking for instance using foreign key constraints, NOT NULL, and so on?

Example: I could check ahead of time that a primary key exists in one table before inserting in another table, or just attempt an insert and let the foreign key constraint error if it doesn't exist.

Best practice?


texts:
I need some help with my trigger statement, as I would like to only insert the new row if a column value does not already exist, or if it does exist update a corresponding column. Currently I have a trigger to insert the new row data then another trigger to update it.

TRIGGER `status_insert` BEFORE INSERT ON  `member_status_likes`
FOR EACH
ROW INSERT INTO member_status_like_count( status_id, status_likes )
VALUES (
NEW.status_id, 1
)

 TRIGGER `status_like_increment` AFTER INSERT ON  `member_status_likes`
FOR EACH
ROW UPDATE member_status_like_count SET status_likes = ( status_likes +1 ) WHERE status_id = NEW.status_id


texts: Does max server memory for SQL Server 2016 contain buffer pool plus in-memory columnstore plus in-memory OLTP, etc. cache?


texts: In PostgreSQL is there a way to rank/weigh columns so as to order the results?

texts:
Let's say I am searching through three text columns. I don't just want results that match a given word or phrase, but I also want to weigh them (column A > column B > column C) - thereby ordering the results such that those rows matching the word in column A will come up higher than those matching in column B, and those matching in column B will come up higher than those matching in column C.

Is this possible? Can you provide me with some terminology or suggestions for further reading?


texts: Create LOG files when creating multiple DATA files for tempdb?

texts:
I've a question that I can't seem to find an answer to (and I can't test it out for a few weeks so I'm doing research now during my downtime).

Should I be creating corresponding LOG files for every DATA file I create?

eg. if I create tempdev3.mdf, should I also create templog3.ldf?

Or do the LOG files get created automatically as and when needed by the SQL Server?

I'm using MS SQL 2008 R2 EE but I think the answer equally applies to 2005 and 2012.


texts:
I am trying to create a stored procedure on my Azure SQL Database, which has joins to other database table in Azure SQL Database.

I am getting the following error while creating the Stored Procedure:

  Msg 40515, Level 15, State 1, Procedure sp_select, Line 16 [Batch Start
  Line 0] Reference to database and/or server name in 'database.dbo.table'
  is not supported in this version of SQL Server.

I understand we can make cross database queries through Elastic Database Query, but how to create the stored procedure for it?


texts:
i´m trying to create trigger for update column, resulting of the min value of other columns. My sql code it´s the following:

DELIMITER //
CREATE TRIGGER t__price_final AFTER UPDATE ON market__combination_price
FOR EACH ROW
BEGIN
CASE
WHEN market__combination_price.price <= market__combination_price.offer
THEN UPDATE market__combination_price SET price_final =
market__combination_price.price;
WHEN market__combination_price.price > market__combination_price.offer
THEN UPDATE market__combination_price SET price_final =
market__combination_price.offer;
END CASE;
END;
//

But it doesn´t work. What could be the problem?


texts:
After spend almost decade in OLTP world I got the courage to try to build my first CUBE.
I installed VS2012 and created Business Intelligence -> Analysis Services.
I did build Data Source Views, Cube and few Dimensions.
Everything  was perfect in EXCEL until I get requests from users to do OUTER JOIN or EXISTS filtering in my cube.
I start googling and find that there is MDX Function EXISTS which cloud do the job for me.
Since this is MDX expression I have no clue where should I write that.
In cube Measures, In dimensions attributes ,Calculated Member or in Data Source View ?

Here is my business problem .
My DSV looks like

*Business case is to make EXCEL users be able to filter OSR_DUG_IMV Dimensions and Attributes, measuring table NABAVA , for members that do not EXISTS at table IZLAZI. *


texts: How to backup only specific days each month on SQL Server (I use both SQL Server 2005 and 2008) using a maintenance plan or Agent job. I mean by that to backup my databases from 28th of each month to 08th of the next month and so on?


texts:
While debugging some code, I stumbled into an error pretty much like the following:

SELECT *
FROM person
WHERE family_size > 3
ORDER BY birthday AND registered=0;

The error is that the AND clause was intended to be in the WHERE clause. Ie:

WHERE family_size > 3 AND registered=0

The "flawed" query throws no SQL syntax error or warning.
So I assume it's valid SQL. (right?)

But more interestingly, what is it supposed to do?
I tested this is a couple of RDBMS and my best bet is that it's doing something like:

ORDER BY (birthday AND registered=0);

Am I right?

Is this intended SQL behavior? (such as expected resolution of operator precedence)
Or is it some sort of fall-back?

Some details I should add after reading comments:

The original query was for use in MySQL, but I'm mostly interested in the intended use of the SQL language.
I did a quick test in MSAccess and behaved pretty much the same.
The columns were originally integers, but for the MSAccess test I did, I used Date/Time for 'birthday', just because.


texts: Creating Event that execute at end of each month

texts:
I need to create an event in mysql that runs at the end of every month 11 PM.I have tried creating an event that creates another events with code below:

DELIMITER |
CREATE EVENT `2014-10-31_CALL_ISRSP` ON SCHEDULE AT '2014-10-31 23:50:00'
DO
BEGIN
call myStoredProcedure();
set @creatNextEvnt := Concat("CREATE EVENT ",LAST_DAY( NOW() + INTERVAL 1 MONTH ),"_CALL_ISRSP ON SCHEDULE AT '",LAST_DAY( NOW() + INTERVAL 1 MONTH )," 23:50:00' DO BEGIN call IncidentsSummaryRecorder(); set @creatNextEvnt := Concat(\"CREATE EVENT \",LAST_DAY( NOW() + INTERVAL 1 MONTH ),\"_CALL_ISRSP ON SCHEDULE AT '\",LAST_DAY( NOW() + INTERVAL 1 MONTH ),\" 23:50:00'\"); PREPARE cer FROM @creatNextEvnt; EXECUTE cer; DEALLOCATE PREPARE cer; set @delLastEvent := Concat(\"DROP IF EXISTS EVENT \",LAST_DAY(NOW()),\"_CALL_ISRSP\");  PREPARE del FROM @delLastEvent; EXECUTE del; DEALLOCATE PREPARE del; END;");
select @creatNextEvnt;
PREPARE cer FROM @creatNextEvnt;
EXECUTE cer;
DEALLOCATE PREPARE cer;
set @delLastEvent := Concat("DROP IF EXISTS EVENT ",LAST_DAY(NOW()),"_CALL_ISRSP");
PREPARE delFROM @delLastEvent;
EXECUTE del;
DEALLOCATE PREPARE del;
END|

but this gives me an error that prepared statement does not support this type of command yet.

I tried storing my SQL strings to a file and executing them within the same event, but I do not know how to execute the file I created. Any help?


texts: Storing IP addresses - varchar(45) vs varbinary(16)

texts:
I am going to create a table with two fields - ID as BIGINT and IPAddress as either varchar(45) or varbinary(16). The idea is to store all unique IP addresses and use a reference ID instead the actual IP address in other tables.

Generally, I am going to create a stored procedure that is returning the ID for given IP address or (if the address was not found) insert the address and return the generated ID.

I am expecting to have many records (I cannot tell exactly how many), but I need the stored procedure above to be executed as fast as possible. So, I am wondering how to stored the actual IP address - in text or bytes format. Which is going to be better?

I have already written SQL CLR functions for transforming IP address bytes to string and the reverse, so transformation is not an issue (working with both IPv4 and IPv6).

I guess I need to create an index to optimize the search, but I am not sure should I include the IP address field to the clustered index, or to create a separate index and with which type the search will be faster?


texts: Error 909 restoring a database on Standard Edition

texts:
I ran into a problem with SQL Server 2012 Standard Edition (64-bit).
I have rebuilt a system with the exact same specifications for software as it had been built previously (Windows 2012, SQL Server 2012 Standard Edition).  Loading the majority of the databases seem to cause no issues, with the exception of just a few that cause the following error when attempting to restore:

Database 'xxx' cannot be started in this edition of SQL Server because part or all of object 'Country' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Database 'xxx' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)

The previous version of SQL Server was Standard as well. Short of uninstalling and re-installing the Enterprise version, is there a solution? I did some searching, and tried the following, but it doesn't really want to do anything to the database other than to set it to "emergency mode".
Recovering a SQL Server Database from Suspect Mode


texts: Where can I find old versions of SQL Developer (4.x)?

texts:
I need a version that works with 11.2.0.4 driver or lower.
In Oracle site there is the current version 20.4.1 and a link to the previous one. Each previous version you click has a link to the previous one, but this chain is broken.
20.4.1 points to 20.4 that points to 20.2 that points to 19.4. The last link is broken.
For some reason, there is a forgotten link to 3.2.2 version that google was able to find.
Do you guys know of any repository for archived versions?


texts:
Many times I face these three choices to work with IDs:

Using an auto-increment identity column?
Suggesting a new codification to use (eg, for students: first 4 position is the year when the student joins the university, 5th position is 0 for females 1 for males, the last 4 position is a sequential number)
Don't add any extra column and use only a unique information that already exists (eg: name (of course must be sure that's unique (there are no two possible entries with the same name)))

Question is: which choice do I make, and according to what criteria?


texts:
I have inherited a SQL DB that has 16 db files. All in the same filegroup of the 16 3 are set to grow the others are static consuming 1 GB each. Would there be a performance impact if I deleted the superfluous files. Should I do any re-indexing etc. afterwards?

Since this is on hold I'll try and be clearer though I believe the three gentlemen that responded have adequately answered my question. Thank you to them, all three responses have been helpful.

On the clarity issue the bottom line question is this. I have a DB that is made up of 16 files in the primary filegroup. They're on the same drive, in the same folder. 13 of the files make up less than 1% of the DB size. If I, following the proper procedure, delete them would there be likely be much of a performance impact (fragmentation etc.)

Thanks to all for the responses.


texts: Error while restoring database in SQL Server 2008 R2

texts:
This error occurs while I restore database from .bak file

  A system assertion check has failed. Check the SQL Server error log
  for details. Typically, an assertion failure is caused by a software
  bug or data corruption. To check for database corruption, consider
  running DBCC CHECKDB. (Microsoft SQL Server, Error: 3624)

After this the Database goes offline and it property sets to Single User.


texts: Database mail "Mailbox unavailable. The server response was: 5.7.1 Unable to relay for..."

texts:
I am getting this error on a named instance.  I set up the account and profile.  It is pointing to our internal anonymous smtp server.  The IP address has been allowed to relay on the SMTP server.  Firewall is open.  Windows firewall is open on port 25.

The database mail configuration hung when setting the service broker to enabled.  I put the msdb in single user mode and then enabled the broker service manually, then set it back to multi_user.

Still getting the error:  looks like an smtp error but the smtp settings are correct and the other instance on the same cluster is working fine.  Database mail xp's are enabled.  So the usual culprits are covered.

Anybody have any other ideas?


texts: Is best practice to Web Application users credentials using same database users credentials

texts:
I have multiple Web applications using same database (MariaDB), so i want to using same user credentials as centralize, so:

Is it best practice to using same database users credentials and
when add new user add the user also on the database with proper
privilege ??
OR
create separate table for the users and the any transaction using
only one database user to connect, insert, delete, update, ....
Thanks & Regards.


texts:
I am using redash for mySQL data source and trying to create a dashboard.
I have many table with the name as tablename_2021_01_01, tablename_2021_01_02, tablename_2021_01_03, tablename_2021_01_04 and so on. All these tables contain data in the same format. Now i want to pull data from 30 tables in one shot. How to do it?


texts: SQL Server : differential backup not updating

texts:
I'm using this script to create a differential backups for my database :

 BACKUP LOG myDatabase TO DISK = 'myDatabaseBackup.dif'

But what amazes me is that the generated file does not change in size, even if I insert more information or create new tables in my database. I expect the size to change because the full backup size changes if I add more data into my database.

Full backup script :

BACKUP LOG myDatabase TO DISK = 'myDatabaseBackup.bak'

I run the above scripts manually (every hour) because I'm using SQL Server Express, and apparently it does not have the Maintenance Plan feature.


texts:
We're a small shop, and I'm certainly not a DBA by any stretch of the imagination, but I've recently inherited a somewhat large older database (been running since at least 04), that in the past month has experienced a slow down. It started with our more complex queries, but has even started to affect the simpler ones.

Prior to my coworker leaving, he had upgraded the instance from 2008 (Standard) to 2014 (Enterprise). The files were also shrunk on the DB, (which I now know is frowned upon.) However, I believe the instance ran fine directly after the upgrade and the shrink.

We have two dedicated SQL servers, set up with AlwaysOn. The servers are about 5 years old, but they're still good servers (64GB memory, Intel Xeon). It doesn't matter which is primary, both experience the slow down. The servers have a few other instances, but the one I'm trying to troubleshoot is by far the biggest one. A few of these instances are Dev and Staging, both of which are about a month old. A query that I've been running will take two minutes on either of these, but 20-40 minutes on live. I've tried copying live to a new instance, but it too is slow.

I've tried restarting the instance, updating statistics, refreshing views (which we aren't really using), rebuilding indexes, but nothing seemed to help. Any ideas?


texts: Mariadb 10.2/MySQL 5.7 index condition pushdown on partitioned tables

texts:
In MySQL 5.7.3 index condition pushdown is also working on partitioned tables as stated here https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html.

Previously, index condition pushdown was not supported for partitioned tables. This limitation was removed in MySQL 5.7.3.

Can't find any information about MariaDB 10.2. I know it is in RC stage but maybe somebody knows about it.

In 10.1 without partitioning:
Extra: Using index condition; Using where; Using filesort

In 10.1 with partitioning on:
Extra: Using where; Using filesort

Thx


texts: mariadb (mysql) innodb not optimizing SELECT for partitions?

texts:
I have a table partitioned by on column "level" -- I have logs where level 7 is the verbose info, which is like 20x as many entries as all the other levels.

When purging old logs in level 7, it was taking quite a while. I had assumed it was just the amount of logs. (And possibly also the transaction time).

But when I compared the query-select with a clone in myisam, it seems it's ignoring the simple partition optimization it could make!

Even worse, this select optimization doesn't carry over to the DELETE query, as per EXPLAIN! It takes the partition, but won't use the date index!

Is this a bug, or just my lack of knowledge?

SHOW GLOBAL VARIABLES LIKE '%version%'
innodb_version                      5.6.26-74.0
protocol_version                    10
version                             10.1.9-MariaDB-1~jessie
version_comment                     mariadb.org binary distribution
version_compile_machine             x86_64
version_compile_os                  debian-linux-gnu
version_malloc_library              system jemalloc
version_ssl_library                 OpenSSL 1.0.1t 3 May 2016
wsrep_patch_version                 wsrep_25.11

https://gist.github.com/avimar/16824955e21781c7e2c6594ef743bc6f

show create table logs_fs
CREATE TABLE `logs_fs` (
  `uuid` char(36) DEFAULT NULL,
  `host` varchar(4) DEFAULT NULL,
  `date` datetime(6) NOT NULL,
  `hrtime` bigint(20) unsigned NOT NULL,
  `level` tinyint(4) DEFAULT NULL,
  `value` text,
  KEY `uuid` (`uuid`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (level)
(PARTITION p0 VALUES LESS THAN (7) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB) */

My naive query: sql should know which partition!

explain select SQL_NO_CACHE count(*) FROM `logs_fs` WHERE `date` < date_sub(now(), interval 1 week) and level=7
id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  logs_fs ALL date    NULL    NULL    NULL    1,777,576   Using where

I had to specify the partition AND leave out the level=7 to get down to a lower number!

explain select SQL_NO_CACHE count(*) FROM `logs_fs` PARTITION (p1) WHERE `date` < date_sub(now(), interval 1 week)
id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  logs_fs range   date    date    8   NULL    423,360 Using where; Using index

Indeed, it works in myisam! (Funny, it says it's querying 196k rows, but it returns the proper count of 210701 which matches innodb)

CREATE TABLE `logs_fs_myisam` (
  `uuid` char(36) DEFAULT NULL,
  `host` varchar(4) DEFAULT NULL,
  `date` datetime(6) NOT NULL,
  `hrtime` bigint(20) unsigned NOT NULL,
  `level` tinyint(4) DEFAULT NULL,
  `value` text,
  KEY `uuid` (`uuid`),
  KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (level)
(PARTITION p0 VALUES LESS THAN (7) ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (8) ENGINE = MyISAM) */

explain select SQL_NO_CACHE count(*) FROM `logs_fs_myisam` WHERE `date` < date_sub(now(), interval 1 week) and level=7`
id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  logs_fs_myisam  range   date    date    8   NULL    195,890 Using where


texts: Predicting the primary keys exhaustion in MySQL based on rate of insertion

texts:
We are running out of primary keys for our table in production. We do not want that to occur again. So we need a mechanism to alert us when we are running out of primary keys based on the rate of insertion so that we can mitigate that.

I have written a Jenkins job that executes a query on information_schema and extracts the index_length, table_rows and auto_increment from the MySQL database. But we need to keep track of the previous reading since we want to take diff of it with the current in order to predict when the keys are going to be exhausted.

SELECT
  INDEX_LENGTH, TABLE_NAME, AUTO_INCREMENT, TABLE_ROWS
FROM
  information_schema.TABLES
ORDER BY
  index_length DESC;

I cannot find anything related to the rate of change in MySQL tables that should predict the rate at which the primary keys are being used up. I am also open to use any good alternate approach if anyone thinks of but keep in mind I have to get a query run by a cron job or Jenkins job that should tell that which tables will be exhausted at the end of this year.


texts: What is a quick way of finding out what is hitting your database especially when the log files are going crazy. SQL profiler? If so, how?


texts: Huge 0.000001 and 0.000002 files and so on, in mysql data folder

texts:
In mysql data folder unknown files were created like this:

This is my.ini content:

[client]
host= .
port= 3306
socket= "MySQL"

[mysql]
no-auto-rehash

[mysqld]

max_allowed_packet = 800M
thread_concurrency = 8
skip-external-locking
port=3306
basedir="c:/zpanel/bin/mysql/"
datadir="e:/hafez/bin/mysql/mysql5.5.24/data/"
character-set-server=utf8
default-storage-engine=MYISAM
max_connections=200
query_cache_size = 128M
table_cache=256
tmp_table_size=256M
thread_cache_size=8
myisam_max_sort_file_size=32G
myisam_sort_buffer_size=205M
key_buffer_size = 384M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 2M

innodb_fast_shutdown=0
innodb_additional_mem_pool_size = 16M
innodb_log_buffer_size = 8M
innodb_buffer_pool_size=1800MB
innodb_log_file_size = 256M
innodb_thread_concurrency = 16
innodb_lock_wait_timeout = 120
innodb_file_per_table

innodb_doublewrite = 0
log-bin = 0
innodb_support_xa = 0
innodb_flush_log_at_trx_commit = 0

tmpdir= "e:/hafez/bin/mysql/mysql5.5.24/tmp/"

enable-named-pipe
skip-federated
server-id = 1

default-time-zone   = "SYSTEM"
log_error           = "c:/zpanel/logs/mysql/mysql.err"
pid_file            = "mysql.pid"
general_log         = 0
general_log_file    = "c:/zpanel/logs/mysql/mysql.log"
slow_query_log      = 0
slow_query_log_file = "c:/zpanel/logs/mysql/mysql-slow.log"

[myisamchk]
key_buffer_size = 1024M
sort_buffer_size = 1024M
read_buffer = 8M
write_buffer = 8M

[mysqldump]
quick
max_allowed_packet = 16M

[mysqlhotcopy]
interactive-timeout

I recently added some innodb tables and these files appeared!

These files are in root mysql data folder(e:/hafez/bin/mysql/mysql5.5.24/data) and aren't in any database folder.

What are these files?

Is it safe to delete this?


texts:
I am doing a design for a database using SQL Server 2008 R2, basically it is a simple database design for a forum (just like games fans forums). Fortunately I am struggling with it as I didn't find any free samples to compare my work to it.

As some people requested in the comments, this is what I did so far: For a Forum, I considered a User (Table), each user have a post (Table) and each post can start a topic (Table).

Table User : UserID (PK), Nickname, Email, Password, AvatarImage.

Table post : PostID (PK), UserID (FK), DateTime, Description.

The question that I don't have an answer for:-

How can I provide the possibility that a post can start a topic? Should I make a new table? what columns? or something can be done with the post table with the help maybe of stored procedures?
How can I make a topic after a topic (like answering a question)?


texts: SSIS Excel source not mapping all columns

texts:
I have a data that I need to import on a xlsx file.
The flow is simple, a person complete the information on this file, then put the file on a shared folder. Finally the ETL move the file to other location and load the data and put it on a SQL Table.

But for some strange reason now, the file load correct, but then fails. The error says

  SSIS.Pipeline] Error: Error in the validation of "component" Excel Origin "(150)". The validation status "VS_NEEDSNEWMETADATA" was returned.

I realized that the dataource is not loading some columns that exists in the file.

I've tried creating a new datasource and new connection, but the problem persits. I think the problem is on the file, but how can I change the metadata to map the columns tha are missing.


texts: Optimizing bulk update performance in PostgreSQL

texts:
Using PG 9.1 on Ubuntu 12.04.

It currently takes up to 24h for us to run a large set of UPDATE
statements on a database, which are of the form:

UPDATE table
SET field1 = constant1, field2 = constant2, ...
WHERE id = constid

(We're just overwriting fields of objects identified by ID.)  The values come from an external data source (not already in the DB in a table).

The tables have handfuls of indices each and no foreign key constraints.
No COMMIT is made till the end.

It takes 2h to import a pg_dump of the entire DB.  This seems like a
baseline we should reasonably target.

Short of producing a custom program that somehow reconstructs a data set
for PostgreSQL to re-import, is there anything we can do to bring the
bulk UPDATE performance closer to that of the import?  (This is an area
that we believe log-structured merge trees handle well, but we're
wondering if there's anything we can do within PostgreSQL.)

Some ideas:

dropping all non-ID indices and rebuilding afterward?
increasing checkpoint_segments, but does this actually help sustained
long-term throughput?
using the techniques mentioned here? (Load new data as table, then
"merge in" old data where ID is not found in new data)

Basically there's a bunch of things to try and we're not sure what the
most effective are or if we're overlooking other things. We'll be
spending the next few days experimenting, but we thought we'd ask here
as well.

I do have concurrent load on the table but it's read-only.


texts: Disabling Indexes and Auditing for large data import

texts:
Hopefully the title describes this sufficiently, but I have a process that runs and performs thousands of individual inserts and updates (nothing I can do to change that). My questions are:

1.
Is it worthwhile to disable all the indexes on the tables before the import, and rebuild the indexes afterwards

2.
Is it worthwhile (and is it even possible) to disable SQL auditing during the import and enable it when the import is complete.

The import is a binary operation, so either it works or it doesn't. It's also done on off-hours, so I'm not concerned with keeping either indexes up to date, or keeping auditing data.


texts: Joining 02 details tables, remove duplicated values from both sides in SQL Server 2008

texts:
In my case, both tables are details, sometimes it has 01 item in Tables A with mutiple items on Table B and vice versa.

SELECT DISTINCT
          x._PROCESS_INST_NO,
          x._GOODS_CD,
          x._QTY,
          y._GOODS_CD,
          Y._QTY
     FROM [TBL_PROC_PM] x
LEFT OUTER JOIN (SELECT  t._PROCESS_INST_NO,
                    t._GOODS_CD,
                  t._QTY
             FROM [TBL_PROC_PMS] t ) y ON y._PROCESS_INST_NO = x._PROCESS_INST_NO
             WHERE y._PROCESS_INST_NO IN('631886','635312')

I want to remove the duplicated values from both sides. Please help. Thanks a lot!


texts: SQL Server Command Line Install - How to change the edition?

texts:
I am creating a script to install SQL Server via the command line.
I cannot see a way to choose the edition I want to install. The GUI on my install media offers Evaluation, Developer or Express:
(When I choose Evaluation, I can enter the product code)

The ConfigurationFile.ini generated by the installer does not seem to have any sort of reference to the edition that is being installed. Once I run the installer, it installs standard edition (verified by PRINT @@version)
How can I specify the edition I wish to install?


texts: Database design - reusable elements (templates)

texts:
Suppose I have a table that stores items of a very specific type. Items of this type are stored in a bank for future use, and they can be changed specifically for a situation without being changed in the bank. They can also be used in bulk. When an instance is created, it does not need to "know" what template was used.

Examples:

A bank of warning messages to be sent or printed -- the users can optionally change the text when they use a message.
A bank of exam questions that teachers can use in exams or exercise       lists -- they can alter the contents when they use them.
A bank of predefined signup forms -- elements can be added or removed when a form is used.

In all cases, the element ("template") in the bank should not be changed.

How can I design the database model to handle this type of situation? I have searched on SE and Google, but I had no luck.

I've thought of some possible approaches, all of them assuming the existence of a separate table collection for bulk use (e.g. multi-part form, exam with many questions):

1. Storing everything in a single table foo.

A true/false column is_template tells if the entry is a template.
When an item is used, a copy of its record is added to the table (but with is_template set to false), possibly with changes made by users.
An additional FK column collection_id stores the id of the collection in which the item is used, or null if it is a template record.

I think this approach is not good because a single table would store data of different types (after all, a template and an instance are not the same thing) and simple queries would be unnecessarily complicated (such as SELECT ... FROM foo WHERE ... AND NOT is_template).

2. Using two tables: foo_template and foo_instance.

Both tables have exactly the same columns, except for:

a FK column collection_id, which is present only in the table foo_instance.
columns related to authoring and editing (author and time), which are present only in the table foo_template.

When an item is used, its record is copied from foo_template to foo_instance with the changes made by the user.

This approach seems better than the previous one, though having two tables whose column sets are almost equal does not look good for me.

3. Using three tables: foo, foo_template and foo_instance.

The table foo stores the data (the columns that are present in both a template and an instance).
The table foo_template stores only author/time data and a FK foo_id.
The table foo_instance stores only has a FK column collection_id and a FK foo_id.

Thus, we no longer have two tables with the same columns, since all the data is in the table foo. However, it also does not seem appropriate to me because queries would be overcomplicated (almost every query to foo_template or foo_instance would require using JOIN with foo.

So...

Is there any better approach for this type of situation or is some of the above approaches good enough?

[EDIT]

I have searched for "is_template" to check if my first approach is used, and here are a few things I have found (in other situations, but with the same idea of storing templates and instances of some type of data):

  is_template marks the task as a template. Physically, there is no difference between a task and a template; however, a template cannot be executed. Possible values are: true and false.
  Source

  is_template: Indicates whether the desktop is a template; the default is 0 (not a template).

Source

  To create a template, use the CREATE_TASK procedure but set is_template parameter to TRUE.

Source

  The only difference between a project that is a template and a regular project is simply a checkmark in a column named is_template in the projects table.

Source

  The field is_template is the only difference between a template and a mailing.

Source

So I think my first idea is not that bad, since it is used in many places, even by Oracle in a specific case. At the moment I am considering using it, but I am definitely open to new ideas.

Thank you


texts: Why does my statement result in a syntax error?

texts:
I tried to run this SQL statement in MySQL 5.5 and 5.7.1 but always got error.

DELIMITER //
CREATE PROCEDURE one_off_update_user_info_view_type()
  BEGIN
    DECLARE @batchsize BIGINT DEFAULT 1;
  END
//

You could check the script in local MySQL 5.5+ since it does not contain any business logic or inner-company databases. Could anyone tell me why the error arose and how to fix it?


texts: Database Schema -Images in separate tables?

texts:
I'm pretty new to web development and i'm creating a local guide app. Currently, I have the following table in the database:

Organization Table

Organization_Id(PK)
name
description
site
category_id

Description

organization: entity that describes a merchant
organization profile: page with organization's details (name, description, site, cover header_image, profile_image, detail_images)

header_image: organizations cover image
profile_image: organizations logo image
detail_images: images of the organization building

Relationships

An Organization has_one profile_image, has_one header_image, has_many details_images.

Question

What is the best aproach in terms of normalization to accomplish that?

Add one column for the profile_image and another to header_image in Organization table.
Create one table ProfileImage, other table HeaderImage with one_to_one relationship to Organization table and eventually another DetailImage table with one_to_many relationship.
Create an Image table to store all the images paths table with a column image_type (profile, header, details) using a one_to_many relationship.
Other option


texts: CREATE Windows User in database for not existing domain

texts:
Is it possible to create a Windows User in the database when the domain is not accessible?
I am preparing database at my dev environment outside of the targeted domain.
Created user is not expected to work at my dev of course.
I am trying:
CREATE USER [NOTEXISTINGDOMAIN\User1] WITHOUT LOGIN;

But getting an error:
Windows NT user or group 'NOTEXISTINGDOMAIN\User1' not found. Check the name again.

I have searched for help and it seems there is no way...which I don't believe much cause this seems to me like relevant scenario.
ADDITION:
Let me explain my scenario. My task is to downgrade database to older MS SQL Server (SQL 2017 to SQL 2016 to be precise). I cannot do this in customers environment (and domain, DC) cause obviosly there is no both MS SQL versions available. So I amd doing the downgrade in my dev. And I want to prepare the database for my customer with all objects in it, including users of all types, including Windows Users - so when my customer restores the database everything is ready and the same as in original database.
Obviosly I'll have to create users later after restoring the database in the customers environment.


texts: How do I load a data-only dump created with pg_dump to another database?

texts:
I have two PostgreSQL databases on my development server, one I use for development and another for production where I test my web applications. When I was using an earlier version of PostgreSQL I was able to easily create a TAR dump file of my prod database in pgAdmin and use that same TAR file to restore my development database after selection the option to clean the database first. A while back I upgraded pgAdmin to the version (1.20.0 I think) that is supposed to work with PostgreSQL 9.4. For whatever reason I cannot restore files in that version. I have tried TAR and SQL but the restore button is always protected.

I decided to use pg_dump and create a SQL file with only insert statements for my production database data. What I want to do is replicate what I was able to do in pgAdmin: Clean my development database and load my pg_dump SQL file into it.

I've search but so far I have not found any examples of how to do this in pgAdmin or in the terminal.


texts: Oracle 11gR2 - How To Audit SYS.USER$ Table

texts:
I have a Oracle 11gR2 database and I want to audit all select operations on SYS.USER$ table for which users select on this table. When I try this command (I'm trying to run this command with SYS user):
audit select on SYS.USER$;

It gives error below:

ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be
altered

How can I audit this table?
Best regards,


texts: Initial Full Backup required for Filegroup Backups?

texts:
I'm assuming that an initial FULL BACKUP is required if I am taking FILEGROUP backups and would like this confirmed.  Many examples show one at the start, but don't make note that it is explicitly required.

I have a database that is terabytes in size with 12 filegroups, 4 of which are active and contain 50% of the overall data.

If I could backup 8 out of 12 read only sets (plus the PRIMARY) in the initial backup, and then separately keep the other 4, it would be much faster to restore, than restoring all 12, and then needing to restore newer versions of the 4 active filegroups.  Is this possible, or am I looking at a FULL BACKUP of the whole database, then separate FILEGROUP backups for the active?


texts: Subquery in where clause forces seq scan instead of using trigram index

texts:
I have a query that is supposed to use a bunch of trigram indexes to speed up ILIKE clauses. This works as long as there isn't a subquery in one of the where terms.

I've simplified my query and can reproduce the issue with a trivial subquery and a simple ILIKE comparison. The actual query has several more ILIKE comparisons and some subqueries that actually do something useful. I'm using Postgres 9.6.

The index is defined like this:

CREATE INDEX datasets_notes_trgm_idx ON datasets USING GIN(notes gin_trgm_ops);

The following query is slow and doesn't use any of my indexes:

SELECT * FROM datasets
WHERE
    datasets.notes ILIKE '%test%' OR
    datasets_id IN (SELECT 1)

Explain output for this query:

"Seq Scan on datasets  (cost=0.01..21156.99 rows=53076 width=1428)
(actual time=0.042..155.697 rows=1098 loops=1)"
"  Filter: ((notes ~~* '%test%'::text) OR (hashed SubPlan 1))"
"  Rows Removed by Filter: 104967"
"  Buffers: shared hit=19634"
"  SubPlan 1"
"    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.003..0.006 rows=1 loops=1)"
"Planning time: 0.980 ms"
"Execution time: 158.579 ms"

The follow query is fast and uses the index:

SELECT * FROM datasets
WHERE
    datasets.notes ILIKE '%test%'

Explain output for this query:

"Bitmap Heap Scan on datasets  (cost=28.68..360.36 rows=87 width=1428)
(actual time=1.056..11.763 rows=1097 loops=1)"
"  Recheck Cond: (notes ~~* '%test%'::text)"
"  Rows Removed by Index Recheck: 16"
"  Heap Blocks: exact=1053"
"  Buffers: shared hit=1097"
"  ->  Bitmap Index Scan on datasets_notes_trgm_idx  (cost=0.00..28.66 rows=87 width=0) (actual time=0.732..0.732 rows=1113 loops=1)"
"        Index Cond: (notes ~~* '%test%'::text)"
"        Buffers: shared hit=12"
"Planning time: 0.869 ms"
"Execution time: 14.563 ms"

Replacing the subquery with a static list of ids also works and the trigram index is used.

SELECT * FROM datasets
WHERE
    datasets.notes ILIKE '%test%' OR
    datasets_id IN (1,2,3)

Explain output for this query:

"Bitmap Heap Scan on datasets  (cost=41.98..385.13 rows=90 width=1428) (actual time=0.593..7.194 rows=1100 loops=1)"
"  Recheck Cond: ((notes ~~* '%test%'::text) OR (datasets_id = ANY ('{1,2,3}'::integer[])))"
"  Rows Removed by Index Recheck: 16"
"  Heap Blocks: exact=1054"
"  Buffers: shared hit=1107"
"  ->  BitmapOr  (cost=41.98..41.98 rows=90 width=0) (actual time=0.428..0.428 rows=0 loops=1)"
"        Buffers: shared hit=21"
"        ->  Bitmap Index Scan on datasets_notes_trgm_idx  (cost=0.00..28.66 rows=87 width=0) (actual time=0.401..0.401 rows=1113 loops=1)"
"              Index Cond: (notes ~~* '%test%'::text)"
"              Buffers: shared hit=12"
"        ->  Bitmap Index Scan on datasets_pkey  (cost=0.00..13.28 rows=3 width=0) (actual time=0.019..0.019 rows=3 loops=1)"
"              Index Cond: (datasets_id = ANY ('{1,2,3}'::integer[]))"
"              Buffers: shared hit=9"
"Planning time: 0.543 ms"
"Execution time: 10.034 ms"

The actual performance difference is much larger in the full query that uses several more ILIKE comparisons.

Why does the subquery force a sequential scan on my query? And how can I avoid this and add subqueries while still using my trigram indexes?


texts: How to track how many times the data is read?

texts:
I want to track how many times records are read in a specific table.

Let's say that we have this table

ID      Name
1       Cat
2       Dog
3       Fish

I want to know how many times the record "Cat" has been read and how many times the record "Dog" has been read and so on.

What is the best way to achive this goal?


texts:
I created a datafile /tmp/encrypted_ts.dbf. When the machine started again, the datafile was gone. When I started it I got ORA-01157,  the details are:

ORA-01157: cannot identify/lock data file 54 - see DBWR trace file
ORA-01110: data file 54: '/tmp/encrypted_ts.dbf'

So I ran alter database datafile 54 offline drop;, and I got ORA-01516. How can I startup the database again?


texts: mysql select count on same table slow subquery

texts:
I've a small table which contains a call log history. Now I want to make a report from this, so I have to find the number of outgoing and incoming calls.

Company has a few extenstions ranging from 100 - 600. So In order to find out if it's an outgoing call, I check if the "dest" is not between 100 - 600 and I get the count using a sub query. (Meaning, two sub queries to get Outgoing and Incoming)

But the problem is, these two sub queries make the query a lot slow, It takes like 3 seconds. (without using these two sub queries it takes only 0.3s).

I read so many questions and answers and found out that the best approach would be to use a join instead of subqueries because of Mysql's low performance on subqueries.

But when I try to join the same table and try to get the same result, it always gives 0 rows. I can't understand how to get the same output using a join.

Here's my Query (with SQ)

SELECT SQL_CALC_FOUND_ROWS DATE(calldate) as clldate,
    clid as caller_id,
    src as source_ext,
    dst as dest_ext,
    (select count(clid) from cdr where src between 100 and 600 and clid = caller_id and DATE(calldate) = clldate) as outbound,
    (select count(clid) from cdr where dst not between 100 and 600 and clid = caller_id and DATE(calldate) = clldate) as inbound,
    sum(duration)/60 as call_duration
FROM cdr
group by DATE(calldate), clid
order by calldate LIMIT 0 , 10;

Here is the explain result:

id  select_typetable    type    possible_keys   key key_len ref rows    Extra
1   PRIMARY cdr ALL NULL    NULL    NULL    NULL    1765    "Using temporary; Using filesort"
3   "DEPENDENT SUBQUERY"    cdr ALL dst NULL    NULL    NULL    1765    "Using where"
2   "DEPENDENT SUBQUERY"    cdr ALL NULL    NULL    NULL    NULL    1765    "Using where"

Here is the show create table:

'CREATE TABLE `cdr` (
  `calldate` datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  `clid` varchar(80) NOT NULL DEFAULT '''',
  `src` varchar(80) NOT NULL DEFAULT '''',
  `dst` varchar(80) NOT NULL DEFAULT '''',
  `dcontext` varchar(80) NOT NULL DEFAULT '''',
  `channel` varchar(80) NOT NULL DEFAULT '''',
  `dstchannel` varchar(80) NOT NULL DEFAULT '''',
  `lastapp` varchar(80) NOT NULL DEFAULT '''',
  `lastdata` varchar(80) NOT NULL DEFAULT '''',
  `duration` int(11) NOT NULL DEFAULT ''0'',
  `billsec` int(11) NOT NULL DEFAULT ''0'',
  `disposition` varchar(45) NOT NULL DEFAULT '''',
  `amaflags` int(11) NOT NULL DEFAULT ''0'',
  `accountcode` varchar(20) NOT NULL DEFAULT '''',
  `uniqueid` varchar(32) NOT NULL DEFAULT '''',
  `userfield` varchar(255) NOT NULL DEFAULT '''',
  `did` varchar(50) NOT NULL DEFAULT '''',
  `recordingfile` varchar(255) NOT NULL DEFAULT '''',
  `cnum` varchar(40) NOT NULL DEFAULT '''',
  `cnam` varchar(40) NOT NULL DEFAULT '''',
  `outbound_cnum` varchar(40) NOT NULL DEFAULT '''',
  `outbound_cnam` varchar(40) NOT NULL DEFAULT '''',
  `dst_cnam` varchar(40) NOT NULL DEFAULT '''',
  KEY `calldate` (`calldate`),
  KEY `dst` (`dst`),
  KEY `accountcode` (`accountcode`),
  KEY `ClldateCid` (`calldate`,`clid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1'

Can anybody guide me on how to get this properly done without using sub queries.

Thank you all in advance :)


texts: How to accomplish such insert-or-update strategy?

texts:
Programming stored procedures isn't my strongest point when it comes to programming, but I'm trying to get better.
I've stumbled upon a business requirement that's kind of hard for me and I was wondering if anyone had already dealt with it in their career.
There's this data in a staging table that needs to be loaded in a production table with insert for the new data and insert-update(backup like) for the old data.
The sample of data in production table looks like this:

AssetID
BeginDate
EndDate
Field1
Field2
FieldN

1
(TS)
(TS)
0.2351
1.3510
4.1234

Meanwhile in staging:

AssetID
Field1
Field2
FieldN

1
0.2351
1.3510
4.1234

In prod the PK is AssetID and BeginDate meanwhile in staging is only AssetID (because the data is flushed every time).
The requirement is to write a stored procedure that when there are new assets in the staging area, they are inserted as new in prod(with BeginDate as CURRENT_TIMESTAMP and EndDate as NULL), meanwhile if there are data(in staging) that are different on the Field1,Field2 or FieldN fields from the production one, then a new record is written with the same AssetID but BeginDate as CURRENT_TIMESTAMP and EndDate as NULL, and the old record with the same combination (BeginDate memorized and EndDate is NULL) being updated with an EndDate as CURRENT_TIMESTAMP
Any of you have dealt with a similar task/requirement? If yes, could you share your solution please?


texts: MariaDB 5.5-56 not reading the my.cnf file and taking default values

texts: Permissions are correct. I actually went ahead and opened the file up to everyone (I know... big no-no but it's a closed off lab) any idea why MariaDB 5.5-56 wouldn't read the my.cnf file and just take default settings?


texts: How can I have multiple conditions on conflict with Postgres?

texts:
I have a table with different columns and I am doing an insert (as upsert) which should update values only in case of conflict:

INSERT INTO clients (name, address, postcode, email)
VALUES ( .... )
ON CONFLICT (name, address)
DO
    UPDATE
    SET email = EXCLUDED.email || ';' || customers.email;

but also I need an extra condition liek ON CONFLICT (address, postcode).

in a sort of:

INSERT INTO clients (name, address, postcode, email)
VALUES ( .... )
ON CONFLICT (name, address) OR CONFLICT (address, postcode) -- this is the change I am referring to
DO
    UPDATE
    SET email = EXCLUDED.email || ';' || customers.email;

How can I achieve that as only one conflict can be managed?


texts: Checking the data latency on an Always On Availability Group in ASYNC mode

texts:
We have a number of SQL Servers with Always on Availability Groups in asynchronous mode between a primary and secondary server with Manual fail over.

I created a latency report utilizing the below query that collects the data every min on each server.

On one of our servers the secondary routinely shows that it has an earlier last_commit_time then the primary.

I verified the server times are the same on both servers.

Why might this be?

;WITH
        AG_Stats AS
                (
                SELECT  AR.replica_server_name,
                        AG.name as AGName,
                        HARS.role_desc,
                        Db_name(DRS.database_id) [DBName],
                        DRS.last_commit_time
                FROM   sys.dm_hadr_database_replica_states DRS
                INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
                INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
                    AND AR.replica_id = HARS.replica_id
                INNER JOIN [sys].[availability_groups] AG on AG.group_id = AR.group_id
                ),
        Pri_CommitTime AS
                (
                SELECT  replica_server_name
                        , AGNAME
                        , DBName
                        , last_commit_time
                FROM    AG_Stats
                WHERE   role_desc = 'PRIMARY'
                ),
        Sec_CommitTime AS
                (
                SELECT  replica_server_name
                        , AGNAME
                        , DBName
                        , last_commit_time
                FROM    AG_Stats
                WHERE   role_desc = 'SECONDARY'
                )
    SELECT p.replica_server_name [primary_replica]
        , p.AGNAME
        , p.[DBName] AS [DatabaseName]
        , s.replica_server_name [secondary_replica]
        , DATEDIFF(ss,s.last_commit_time,p.last_commit_time) AS [Sync_Latency_Secs]
    FROM Pri_CommitTime p
    LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName] and  s.AGNAME = p.AGNAME


texts: Should I create a Factless Fact Table or Denormalise my table

texts:
I am creating a data warehouse from a store database and I have a question regarding the design of my dimensions and facts.

In the store database a table exists for Person, Person_Address and Person_Address_Type. These are linked by another tabled name Entity_Address_ID which links the three tables by their primary keys to give details on what a person's address is and what type of address it is.

My question is, should I create a dimension for all three tables, and a fact-less fact table to link them together or should I de-normalise my dimensions and add to each dimension a foreign key for the address  and address type they are linked too?

Here is a very quick UML of what the current database looks like to provide clarification


texts:
We have 12c database. we had create a pdb. Now i want to take a backup through
RMAN. Could you please share the steps to configure this backup.

I know to configure backup in 11g..For 12c Please share the steps.

thank you,


texts:
We have migrated SQL Server Standard Edition from 2008 R2 to 2014. After the migration, we are seeing a huge amount of blocking, and overall the instance is very slow.

The major wait we are seeing is Resource_Semaphore_Query_Compile. Some of the queries are requesting more memory and waiting on compile time.

This new server is a beast, Memory : 1TB and CPU : 2 physical cores with 30 logical processors each (60 total). Since this is Standard Edition, max memory is set to 128GB, and I believe there are some issues with maxdop setting and how SQL Server 2014 is utilizing the processors. Maxdop was set to 4 and we changed it to 2, still no luck.

Regarding the resource semaphore wait types: I updated stats on all databases with full scan. We cannot tune the queries as they are encrypted vendor code which used to perform perfectly well on SQL Server 2008 R2.

I cannot change the database compatibility level. I have enabled soft NUMA nodes on this instance by enabling startup trace flag 8079. It created 4 NUMA nodes (before the change, it was 2 NUMA nodes - I can see that in the SQL Server error log).

We are still seeing the Resource_Semaphore_Query_Compile waits. Some of the queries are requesting 24GB of memory.


texts: MYSQL: Display NULL value when foreing key is NULL

texts:
I have the following database structure in MYSQL:

CREATE TABLE xs_bugtracking (
                    `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    `product` VARCHAR(64) NOT NULL,
                    `title` VARCHAR(64) NOT NULL,
                    `status` VARCHAR(8) NOT NULL,
                    `descr` VARCHAR(1024) NOT NULL,
                    `assignee` INT(11),
                    `reported_by` INT(11) NOT NULL,
                    `importance` VARCHAR(8),
                    `create_date` INT(11) NOT NULL,
                    `modify_date` INT(11) NOT NULL
                    );

The fields assignee and reported_by are foreing key of the same table of wordpress db: wp_users

Now i want get all element with the following SELECT query:

SELECT xs_bugtracking.id, product, xs_bugtracking.title AS title, status, xs_bugtracking.descr AS descr,
            assignee_tbl.user_nicename AS assignee,reported_tbl.user_nicename AS reported_by,importance,
            FROM_UNIXTIME(create_date) AS 'create_date', FROM_UNIXTIME(create_date) AS 'modify_date'
            FROM xs_bugtracking, xs_products, wp_users AS assignee_tbl,
            wp_users AS reported_tbl WHERE
            xs_products.name=product AND xs_products.lang='en' AND
            (xs_bugtracking.assignee=assignee_tbl.ID OR ISNULL(xs_bugtracking.assignee)) AND
            xs_bugtracking.reported_by=reported_tbl.ID

Now, problems come when you have a row with assignee equals to NULL, infact the resulting table multiplies the rows when the value is NULL, because lost the JOIN from wp_users aka assignee_tbl.

There is a way to display assignee_tbl.user_nicename when assignee is not NULL and just NULL when assignee is NULL?

If you want check the full source code

Thanks in advance!


texts:
I have a SQL Server 2008 R2 instance that I have been trying to get Data Collection setup on. I get everything setup, but there is no data that gets uploaded. I've traced the root of the problem to be a failure in the SQL Agent jobs where it calls dcexec.

Isolating that out, I can reproduce the issue by running:

& "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\dcexec.exe" -c -s 1 -i "localhost\MSSQLSERVER" -m 1

I run the above command from a PowerShell window that I've opened with RunAs Administrator. The account I am running the above with is a domain account that is a local administrator on the server. In addition, the account also has the sysadmin role on the SQL instance.

The error that I get is:

SSIS error. Component name: GenerateTSQLPackageTask, Code: -1073548540,
Subcomponent: Generate T-SQL Package Task,
Description: An error occurred with the following error message:
"An error occurred while verifying the result set schema against the output table schema.
The data collector cannot connect to the management data warehouse. : Login failed.
The login is from an untrusted domain and cannot be used with Windows authentication."..

The master package exited with error, previous error messages should explain the cause.

That is the same error I see in the history on the SQL Server Agent job.

Note that if I execute dcexec with a bad instance name, such as:

& "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\dcexec.exe" -c -s 1 -i "localhost\MSSQLSERVER1" -m 1

Then, as expected, I get a different error:

Failed to get the SQL server instance id from the given instance name.

Any ideas on how to successfully login to the SQL instance with dcexec?


texts:
We are in process of development of project which will process around 51 000 000 records daily (~around 500-600 inserts per second). We choose Always On solution in SQL Server 2019 which will contain:

One Primary Server
Three replicas which has to work in
synchronously mode

According to my checks (at this same machine) with use of stored procedure (inserting data to main table) I got results:

Always On (1 PRIMARY, 3 REPLICAS):
~260 inserts per second (1 BATCH - 10 000 INSERTS)
~440 inserts per second (2 BATCHES - 10 000 INSERT RUN AT THE SAME TIME)
Without Always On (1 PRIMARY):
~620 insert per second (1 BATCH - 10 000 INSERTS)
~1200 insert per second (2 BATCHES - 10 000 INSERT RUN AT THE SAME TIME)

My questions are:

Do you have similiar experience with decrease of efficiency according to implementation Always On architecture ? (in my case it is around 2x times faster in solution without Always on)
Could you recomend similar solution in SQL Server ?


texts: Switching to Simple Recovery - shrinking transaction logs

texts:
Standard disclaimer: I am an "involuntary DBA" (a nice phrase I saw in this article) and have done lots and lots and lots and lots and lots of reading on this subject, but am still confused/concerned...

I need to change the recovery model of several SQL Server 2014 production databases from Full to Simple.

The databases are currently set to Full Recovery and were being backed-up using SqlBackupAndFTP, which would do a daily full backup, with 3 differential backups and 20 transaction backups each day.  This was working fine until we had huge problems with the company providing FTP-based storage.

About 3 months ago I switched to using Attix5 which is working well - however, I've now discovered that due to the way the software works the transaction logs are as big as the data files (in one case over 14Gb against a 13Gb data file).

I have been recommended to switch the recovery model to Simple, so that the transaction log doesn't grow.

I know that having Simple Recovery will not provide point-in-time restoration - that is absolutely fine, as full backups are taking place hourly and we are "happy to lose" anything that might have happened in the previous 1-59 minutes.

My understanding is that I should set the recovery model to Simple and let the backup take place, which will set a checkpoint in the transaction log allowing for the space to be re-used.

However, I really need to shrink the transaction log to a sensible size (so that it's not taking up so much of the backup space), but I keep reading that shrinking is a bad idea.  Or am I getting the wrong end of the stick?

If I shrink the transaction log down to around 25Mb (which should easily be big enough for a single hourly set of transaction) using the following command, is that enough?

DBCC SHRINKFILE('log file name',25)

(I'm really sorry to be asking something that appears to have been asked a zillion times before, but this is not something I can get wrong.)


texts: Changed root password from '' to ''. Now cannot connect to databases in Cloud SQL prompt

texts:
I changed the root password using Google APIs SQL Prompt from blank to  using:

UPDATE mysql.user SET password = PASSWORD('<password>') WHERE user = 'root';

It has been documented here that SQL prompt and some other APIs Services will lose functionality when a password is set on root. (Thanks for Hasturkun's comment below).

How then do we reset the password to <blank>?


texts: Are MySQL InnoDB transactions serializable?

texts:
I am confused whether or not MySQL transactions are serializable and whether or not read-anomalies are guarded against?

For example, what if I SELECT a value from a row, then UPDATE that row by incrementing the value. There is an opportunity for another query to UPDATE the row which is being read, leading to an improper update.

However, I was under the impression that even if I wrap these two statements in a transaction, that I am not guaranteed to be free of race conditions. See this StackOverflow thread. So to prevent this I used UPDATE with COALESCE which is a single query and thus guaranteed to be atomic. Was this assumption correct? Or would a transaction have worked here to guarantee no race conditions?

I see from the MySQL 5.7 documentation  on isolation levels that transactions are by default REPEATABLE_READ rather than SERIALIZABLE. What if I had set the transaction level to SERIALIZABLE? I attempted to read the documentation on REPEATABLE_READ but it did not increase my understanding with regards to this issue.


texts: Chaining remote SQL Server Agent Jobs with success/failure feedback

texts:
I have 2 Microsoft SQL Server 2014 (SP3-CU4) instances, residing on different servers.

ServerA has a linked server, pointing to ServerB\InstanceB.
Both instances have a job, and JobA triggers the remote execution of JobB.
So the execution of the jobs is chained.

Schematic of setup

ServerA\InstanceA

Server Objects

Linked Servers

LinkedServerToB (target: ServerB\InstanceB)

SQL Server Agent

Jobs

JobA

Step1 = Execute some local code
Step2 = EXEC [LinkedServerToB].[msdb].[dbo].sp_start_job @job_name="JobB"

ServerB\InstanceB

SQL Server Agent

Jobs

JobB

This all works fine.
Except for when JobB fails.
When that happens, JobA.Step2 doesn't know about that.
All JobA.Step2 did was trigger the remote execution of JobB, and then it's done.
So JobA in its entirety runs successfully, despite the failure of JobB.

Although I understand why it's behaving that way, its not the outcome I was looking for.
Instead, I'd like the parent job (JobA) to fail when a child job (JobB) fails.

How can I get JobA to fail when JobB fails?
Maybe some sort of success/failure feedback between them, but I don't know how to implement that.