texts: Read after transaction from secondary session not visible

texts:
We've got a database (MySQL ) that has a client 'per channel'.
When a player changes channel from, say, 1 to 2, we have to save the state of the player to the database.

Saving is done in multiple transactions (per table), which include in most cases a DELETE FROM table WHERE player = X; INSERT INTO table VALUES (...);. Some other tables require only an UPDATE statement.

The problem is as follows:

Player starts moving from channel 1 to channel 2
Channel 1 registers transition in Redis, making sure that player is bounced on Channel 2 until player is saved.
Player disconnects from channel 1
Player connects to channel 2, gets asked to reconnect (bounce)
Channel 1 received disconnection, saves character, disables bouncing
Player connects to channel 2, gets accepted
Channel 2 loads info from database --THIS IS OUTDATED INFO--

I'm about to, instead of deleting the Redis key, to change the TTL of the key to a couple seconds to prevent it from loading old info. But it doesn't guarantee anything, as the database is a system on its own...

For the record, there's a MariaDB instance that acts as a slave for backup purposes.


texts: SQL Server Stored Procedure - Does Raiserror affect performance

texts:
Does RAISERROR in a SQL Server stored procedure affect performance?

Would I be better off just having a try-catch wrapper around the whole procedure and just catch the errors in the database access layer?

Right now for logging in a user, I check if the user exists and RAISERROR if the user does not exist. Would I better just let the procedure return null and interpret that in the database access layer?


texts:
I have problems adding a databases to Always ON.
When I try to connect to named instance I get an error:

Names are correct. Also when I try to connect from server a to server b with server\instance is giving same error, but just server is working? But here server name is greyed out so I cannot change it?
On server itself when I use server\instance is working.

I did not set DNS Aliases, maybe I need to do that?

Any ideas?

Thanks


texts: I want to know the usage of Synonyms in oracle.I have searched in internet there i have learned like synonyms will be created in one schema and we can use that in another schema. But still am not clear with synonyms.Can anyone clear my doubt about Synonyms


texts: Could not connect to postgres after installing 9.4 and uninstalling 9.3

texts:
I have installed postgresql 9.4 and I removed postgresql 9.3 from my computer. Now when I try to run:

foo@bar:~$ psql

I get the error

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Note:

foo@bar:~$ psql --version
psql (PostgreSQL) 9.4.2


texts: How can I get distinct counts with different where clauses on the same field?

texts:
How can I get DISTINCT row COUNTs with different conditions in a single SELECT statement, and if count is zero get zero as a result?

I tried the following query:

SELECT DISTINCT
    A.DeviceFilter,
    (SELECT count(*) FROM DeviceLicense AS B WHERE A.DeviceFilter = B.DeviceFilter AND b.OrganizationId = 1001)
FROM
    DeviceLicense AS A
GROUP BY
    A.DeviceFilter


texts: How can I save a severely fragmented database/table?

texts:
I've recently made a discovery that might explain my slow SQL run times.

I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 GB this is a phenomenally HUGE issue. The result is that I have a severely fragmented table (and likely the entire db is severely fragmented). It makes updates incredibly slow as SQL must first create the space for the update, before writing it. And to run updates, it has to piece together thousands of 1 MB pieces of information each time before performing calculations.

So my question: is there a way to resolve this and defragment? I've already gone into Properties - Files and reset the mdf growth level to 100MB, and db recovery is on simple. Can I defragment, or do I need to just go in and recreate the table, or re-create the entire db? I know it will be a lot of time and work, but my main goal for this db is time efficiency. It needs to be as fast as possible and if that means starting from the beginning again, so be it.

In addition, it was brought to my attention that rather than having one very large mdf I can create 'filegroups'. I understand this is something like saving multiple mdfs; one for each table, or even index. How can I safely create these filegroups and will they also help optimize query time and efficiency?


texts: Can this array group count query be improved?

texts:
So I have the following query

explain analyze
with tags as (
    select unnest(tags) as tag_name from tasks where user_id = 1
) select
        count(9),
        tag_name
    from
        tags
    group by
        tag_name
    order by
        count(9) desc
    limit 50

Gives me the following result:

Limit  (cost=3243.86..3243.99 rows=50 width=32) (actual time=2.278..2.278 rows=1 loops=1)
  CTE tags
    ->  Bitmap Heap Scan on tasks  (cost=12.35..1917.72 rows=52700 width=13) (actual time=0.098..2.074 rows=261 loops=1)
          Recheck Cond: (user_id = 1)
          ->  Bitmap Index Scan on index_tasks_user_id  (cost=0.00..12.22 rows=527 width=0) (actual time=0.065..0.065 rows=261 loops=1)
                Index Cond: (user_id = 1)
  ->  Sort  (cost=1326.14..1326.64 rows=200 width=32) (actual time=2.278..2.278 rows=1 loops=1)
        Sort Key: (count(9))
        Sort Method: quicksort  Memory: 25kB
        ->  HashAggregate  (cost=1317.50..1319.50 rows=200 width=32) (actual time=2.273..2.274 rows=1 loops=1)
              ->  CTE Scan on tags  (cost=0.00..1054.00 rows=52700 width=32) (actual time=0.099..2.177 rows=261 loops=1)
Total runtime: 2.314 ms

Which is pretty decent I suppose. The previous way of doing things where to have a bunch of join tables and that gave me something like below:

Limit  (cost=919.38..919.40 rows=50 width=12) (actual time=163.164..163.257 rows=50 loops=1)
  ->  Sort  (cost=919.38..919.48 rows=206 width=12) (actual time=163.162..163.194 rows=50 loops=1)
        Sort Key: (count(*))
        Sort Method: top-N heapsort  Memory: 28kB
        ->  HashAggregate  (cost=917.39..918.01 rows=206 width=12) (actual time=162.899..163.008 rows=132 loops=1)
              ->  Nested Loop  (cost=456.90..917.19 rows=206 width=12) (actual time=1.040..162.361 rows=416 loops=1)
                    ->  Hash Join  (cost=456.90..904.32 rows=206 width=4) (actual time=1.029..159.429 rows=416 loops=1)
                          Hash Cond: (taggings.workout_id = workouts.id)
                          ->  Seq Scan on taggings  (cost=0.00..416.64 rows=40214 width=8) (actual time=0.010..45.753 rows=37029 loops=1)
                          ->  Hash  (cost=455.91..455.91 rows=282 width=4) (actual time=1.004..1.004 rows=293 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 11kB
                                ->  Bitmap Heap Scan on workouts  (cost=4.49..455.91 rows=282 width=4) (actual time=0.101..0.744 rows=293 loops=1)
                                      Recheck Cond: (user_id = 1)
                                      ->  Bitmap Index Scan on index_workouts_on_user_id  (cost=0.00..4.48 rows=282 width=0) (actual time=0.058..0.058 rows=293 loops=1)
                                            Index Cond: (user_id = 1)
                    ->  Index Scan using tags_pkey on tags  (cost=0.00..0.06 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=416)
                          Index Cond: (id = taggings.tag_id)
Total runtime: 163.393 ms

Now forget about the last explain and lets focus on the first one. Can it be optimized further? Any tricks or such that I might be missing out on? I guess an index on the user_id column should be plenty for this query?


texts: Is querying by a PRIMARY KEY in MySQL faster than other queries?

texts:
I have two queries that return on a dataset of 200,000 records with apparently the same performance. The first looks up a primary key, and the second a varchar(32) with no special constraints.

Am I using MySQL BENCHMARK correctly here?

SELECT BENCHMARK(2000000000, (SELECT `col` FROM `table` WHERE `id` = somekey LIMIT 1))

SELECT BENCHMARK(2000000000, (SELECT `col` FROM `table` WHERE `string` = somestring LIMIT 1))

They both have a similar run time. I understand the fastest possible query should be the case where it's the primary key without any other predicates, but I thought the second form (a random 32 digit hex number with no constraints) would run significantly slower. Am I missing something? Maybe my dataset is too small to see the difference?


texts:
I have a field called PropNum which is the proposal number for a marketing pursuit.  I want this field to be automatically generated by gathering data from other fields, automatically generating the last three numbers and automatically restarting back to 001 at the beginning of the next year.  For example, I have the following line in my data:

        Year,        Office,     PropNum,
Record 1 2016       STL         16STL001
Record 2 2016       STL         16STL002
Record 3 2016       CHI         16CHI001
Record 4 2016       CHI         16CHI002

In addition to starting over at the beginning of the year, I would like the number to be sequential based the office.  As you can see in my example the STL office has a list of Proposal numbers and the CHI office has another.  Can anyone help me write the script to do this?


texts: Slow mysql query full text search tag database

texts:
In the database are ~700k images. Each image has its own tag(s). I want to be able to search images by the tags with a full text search query. The query does exactly what I want but it's very slow. Can someone please help me speed it up, or create another one.

There are indexes on all the id-fields and the name field in the image_tag table.

SELECT
    image.*
FROM image
INNER JOIN (SELECT image_to_tag.image_id,
             GROUP_CONCAT(image_tag.`name`) AS tags
        FROM image_to_tag
        INNER JOIN image_tag
        ON (image_tag.id = image_to_tag.image_tag_id)
    GROUP BY image_to_tag.image_id) t ON t.image_id = image.id
WHERE MATCH (t.tags) AGAINST ('+justin* +backgrounds*' IN BOOLEAN MODE)

Table: image

id | filename
1  | image1.jpg
2  | image2.jpg
3  | image3.jpg

Table: image_to_tag

image_id | image_tag_id
1        | 1
1        | 2
2        | 3
2        | 4

Table: image_tag

id | name
1  | justin bieber
2  | backgrounds
3  | justin timberlake
4  | other backgrounds

If I search for "justin background" I want to find images 1 and 2. If I search for "justin bieber background" I want to find image 1.

Results explain query:


texts: What are the benefits of using a different tablespace for a new user schema?

texts:
The tables in the schema  are intended to be used read only and is only updated once each three month.

My question refers to
performance
maintenance (backup/restore, export/import)

What about the temporary tablespace, is it better to use a different one in this case?


texts: 2013. Lost connection to MySQL server during query error

texts:
I have a table with close to 20 million rows.

In phpMyAdmin, I attempt to add a new column:

ALTER TABLE `myTable` ADD `newColumn` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0';

After about 60 secs (every time), I get the error.

I've had my server guy increase both net_read_timeout and net_write_timeout from 60-600 seconds, but the error still occurs after 60 secs.

Just wanted to see if I'm going in the right direction here, or should be chasing something else?

The error log from just prior to the crash says (nothing before this for a long time):

150209 05:03:39 mysqld_safe Number of processes running now: 0
150209 05:03:39 mysqld_safe mysqld restarted
2015-02-09 05:03:40 0 [Warning] option 'interactive_timeout': unsigned value 9999999999 adjusted to 31536000
2015-02-09 05:03:40 0 [Warning] option 'wait_timeout': unsigned value 9999999999 adjusted to 31536000
2015-02-09 05:03:40 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-02-09 05:03:40 6744 [Note] Plugin 'FEDERATED' is disabled.
2015-02-09 05:03:40 6744 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2015-02-09 05:03:40 6744 [Note] InnoDB: The InnoDB memory heap is disabled
2015-02-09 05:03:40 6744 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2015-02-09 05:03:40 6744 [Note] InnoDB: Memory barrier is not used
2015-02-09 05:03:40 6744 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-02-09 05:03:40 6744 [Note] InnoDB: Using Linux native AIO
2015-02-09 05:03:40 6744 [Note] InnoDB: Not using CPU crc32 instructions
2015-02-09 05:03:40 6744 [Note] InnoDB: Initializing buffer pool, size = 54.0M
2015-02-09 05:03:40 6744 [Note] InnoDB: Completed initialization of buffer pool
2015-02-09 05:03:40 6744 [Note] InnoDB: Highest supported file format is Barracuda.
2015-02-09 05:03:40 6744 [Note] InnoDB: The log sequence numbers 108217366409 and 108217366409 in ibdata files do not match the log sequence number 108487328890 in the ib_logfiles!
2015-02-09 05:03:40 6744 [Note] InnoDB: Database was not shutdown normally!
2015-02-09 05:03:40 6744 [Note] InnoDB: Starting crash recovery.
2015-02-09 05:03:40 6744 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-02-09 05:03:41 6744 [Note] InnoDB: Restoring possible half-written data pages
2015-02-09 05:03:41 6744 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 394549, file name sbdb-bin.000108
2015-02-09 05:03:42 6744 [Note] InnoDB: 128 rollback segment(s) are active.
2015-02-09 05:03:42 6744 [Note] InnoDB: Waiting for purge to start
2015-02-09 05:03:42 6744 [Note] InnoDB: 5.6.22 started; log sequence number 108487328890
2015-02-09 05:03:42 6744 [Note] Recovering after a crash using sbdb-bin
2015-02-09 05:03:42 6744 [Note] Starting crash recovery...
2015-02-09 05:03:42 6744 [Note] Crash recovery finished.
2015-02-09 05:03:43 6744 [Note] Server hostname (bind-address): '*'; port: 3306
2015-02-09 05:03:43 6744 [Note] IPv6 is available.
2015-02-09 05:03:43 6744 [Note]   - '::' resolves to '::';
2015-02-09 05:03:43 6744 [Note] Server socket created on IP: '::'.
2015-02-09 05:03:43 6744 [Note] Event Scheduler: Loaded 0 events
2015-02-09 05:03:43 6744 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.22-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

Thanks for your time and help.


texts: In what order does sqllogship.exe process groups of log files?

texts:
In what order does sqllogship.exe processes groups of log files based solely on filename order or does it does it do it based on file timestamp primary sort then filename secondary sort?

It appears to handles things in filename order when all log files have the same timestamp.


texts: Move DB/LOG files - Detach/Attach - Problem

texts:
I wanted to move the MDF/LDF file locations for a DB. From what I read I found I should use Detach/Attach and not just take the DB off line and relocate the files. I did just that and used the below to Re-Attach the DB.

CREATE DATABASE MyAdventureWorks
    ON (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Data.mdf'),
    (FILENAME = 'C:\MySQLServer\AdventureWorks2012_Log.ldf')
    FOR ATTACH;
GO

Of course using my new file locations which used two separate volumes and not the location moving from.

BUUUT, all this did was put the files back on the original volume that I did not specify. It also put the DB in a READ ONLY state.

Any help would be appreciated.

Thanks

SQL Server 2016


texts: log_reuse_Wait_desc always return LOG_BACKUP even right after log backup

texts:
We are using SQL Server 2012 standard Edition (SP4)- 11.0.700.0(x64) (Build 14393)(HYpervisor).

Data File Size = 171 GB
Log File Size = 106 GB

When I queried sys.databases to see the log_reuse_Wait_desc it is always LOG_BACKUP- even right after log backup!
When I run DBCC SQLPERF(logspace) I see "Log space Used %" is less than 1%.
DBCC loginfo returns 773 which is high.

FYI- This database is not involved in Log Shipping/replication/mirroring.  The recovery model for this DB is Full and log backups are taken every 30 minutes. I know log backups are happening as I checked from msdb.dbo.backupset.

This database had % autogrowth setting on log file until last week. I changed it as part of config change.

How do I find why the log_reuse_desc is returning LOG_BACKUP always?

I would like to explore some options to reduce the log file size on the disk. (I know it is a bad bad thing to trucate and shrink the log file, but at this point I would like to know my options available). Can someone please suggest a graceful way to do this.


texts:
I have a query which takes about 6 secs to execute.

SELECT * FROM
(SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, user.firstname, user.lastname, op.ID AS prouctOrderID, sp.ID AS productID, user.userID
   FROM table_orders_1_products AS op
   LEFT JOIN table_orders_1 AS ord ON ord.ID=op.orderID
   LEFT JOIN cms_user_aditional AS USER ON ord.userAdditional=USER.ID
   LEFT JOIN table_status AS stat ON stat.ID=op.statusID
   LEFT JOIN table_product_1 AS sp ON op.productID=sp.ID
   LEFT JOIN table_partners AS spp ON sp.supplier=spp.ID
   WHERE op.statusID!='0'
   ORDER BY ord.ID DESC LIMIT 1000) AS allO
ORDER BY allO.ID DESC

If I remove the "bolded" order by statement it takes only 40ms.

Is there any work around to fix this issue?

Create table for test_orders_1

CREATE TABLE `com_shop_orders_1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `date` int(11) NOT NULL,
  `email` text NOT NULL,
  `name` text,
  `userID` int(11) DEFAULT NULL,
  `status` int(3) NOT NULL DEFAULT '1',
  `productsTotal` double NOT NULL,
  `total` double NOT NULL,
  `tax` double NOT NULL,
  `discount` double NOT NULL,
  `shippingID` int(3) NOT NULL,
  `shippingPrice` double NOT NULL,
  `paymentID` int(3) NOT NULL,
  `userAdditional` int(11) NOT NULL,
  `transacitonID` text NOT NULL,
  `paymentPrice` double NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `userID` (`userID`),
  KEY `shippingID` (`shippingID`),
  KEY `paymentID` (`paymentID`),
  KEY `ID_index` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=208180 DEFAULT CHARSET=latin1;


texts: Fetch first 9 records and make one more record which will be as "Others" in MySQL

texts:
I am fetching records from one table with count of one field with other field as name.
I want only 10 records. out of which 9 records give me field and its count. but i want to show 10 record as "Others" with all remaining fields with count. This is something like wrapping records.
Something like below is table contents.

emp_id | designation

 1   |   software Engg.

 2   |   software Engg.

 3   |   Project Manager

not less than 10 designation.

And I want to show first 10 records as
Software Engineers  20
Project Manager     5
....
....
....
Others    50

Is there any way to make SQL Query for mysql db so that it will be fast and save time in application level where I am adding up record counts for "Others" ?

Please suggest how I can make it possible in effective way.


texts: Wrong Last Execution Time sys.dm_exec_procedure_stats

texts:
I am running the following query it shows a Stored Proc that I know ran on Mon 6/6/2016 8:45 AM but the below query shows that the last execution time was 2016-05-19 12:09:23.197. Is there something I am missing here or is this an issue on the server? Everything I have found online refers to the opposite issue where people are getting reports that the proc is running when it isn't.

SELECT * FROM sys.dm_exec_procedure_stats AS s
INNER JOIN sys.procedures AS p
ON s.[object_id] = p.[object_id]


texts: Maintenance plan fails on non-existent database

texts:
We have a maintenance plan on our production server which does all the usual stuff... checking integrity, rebuilding indexes, backups, etc.

Lately, it has been failing every night, complaining about a database which does not exist. The maintenance plan is set to work only on All user databases.

However, the plan craps out every night, and gives the following message in the job history:

  Executing the query "USE [RollbackSnapshotTempDB{73B1E0C2-00FC-476A-A67..." failed with the following error:
  "Database 'RollbackSnapshotTempDB{12345678-1234-1234-5678-012345678912}' does not exist. Make sure that the name is entered correctly.".
  Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

(I changed the GUID, I wasn't sure if that is any sensitive info.)

We definitely do not have a user database (or system database either, for that matter) by that name. And there is nothing listed under the Database Snapshots folder either.

As per Aaron's comment, I queried sys.databases to see if that weird name is listed, but it is not listed in there either. Nor is it listed in SELECT DISTINCT database_name FROM msdb.dbo.backupset.

Running on SQL Server 2012, build 11.0.5343.


texts:
After the head node running Rocks Cluster on CentOS 6.8 restarted MySQL dies with the below errors. Could it really be I "hit a bug"?

161109 13:58:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
18:58:40 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

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

Thread pointer: 0x1b5bc40
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7ffdb526bd50 thread_stack 0x40000
/usr/libexec/mysqld(my_print_stacktrace+0x29) [0x8509f9]
/usr/libexec/mysqld(handle_fatal_signal+0x483) [0x6a3f13]
/lib64/libpthread.so.0() [0x33c3e0f7e0]
/usr/libexec/mysqld(ha_resolve_by_name(THD*, st_mysql_lex_string const*)+0x81) [0x698831]
/usr/libexec/mysqld(open_table_def(THD*, st_table_share*, unsigned int)+0xe0a) [0x610ada]
/usr/libexec/mysqld(get_table_share(THD*, TABLE_LIST*, char*, unsigned int, unsigned int, int*)+0x20b) [0x6042eb]
/usr/libexec/mysqld() [0x608eaa]
/usr/libexec/mysqld(open_table(THD*, TABLE_LIST*, st_mem_root*, bool*, unsigned int)+0x7a1) [0x60ac01]
/usr/libexec/mysqld(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int)+0x5d0) [0x60b7d0]
/usr/libexec/mysqld(open_and_lock_tables_derived(THD*, TABLE_LIST*, bool)+0x6a) [0x60b9da]
/usr/libexec/mysqld(plugin_init(int*, char**, int)+0x622) [0x7197e2]
/usr/libexec/mysqld() [0x5bf202]
/usr/libexec/mysqld(main+0x1b3) [0x5c1af3]
/lib64/libc.so.6(__libc_start_main+0xfd) [0x33c361ed1d]
/usr/libexec/mysqld() [0x5098f9]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 0
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
161109 13:58:40 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Here is the config file:

cat /etc/my.cnf
[mysqld]
transaction-isolation=READ-COMMITTED
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

key_buffer              = 16M
key_buffer_size         = 32M
max_allowed_packet      = 16M
thread_stack            = 256K
thread_cache_size       = 64
query_cache_limit       = 8M
query_cache_size        = 64M
query_cache_type        = 1
# Important: see Configuring the Databases and Setting max_connections
max_connections         = 25

# log-bin should be on a disk with enough free space
log-bin=/export/home/cloudera-scm/logs/mysql_binary_log
#log-bin-index=/export/home/cloudera-scm/logs/mysql_binary_log.index

# For MySQL version 5.1.8 or later. Comment out binlog_format for older versions.
binlog_format           = mixed

read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M

# InnoDB settings
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit  = 2
innodb_log_buffer_size          = 64M
innodb_buffer_pool_size         = 4G
innodb_thread_concurrency       = 8
innodb_flush_method             = O_DIRECT
innodb_log_file_size = 512M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Version:

mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1


texts: Migrating from Oracle to MySQL using WorkBench Migration Tool

texts:
I am currently migrating from Oracle to MySQL and I am going to use the  migration tool in workbench to help me with the procedure. I have entered my source connection details as follows:

Database System: Generic RDBMS
Connection Method: ODBC(Manually Entered parameters)
Driver:Oracle RDB driver
Hostname: 127.0.0.1  Port:1521
Username: user
Database:db

But I seem to be receiving the following error when testing the connection:

ERROR: Error during Connect to source DBMS: Error("('HY093', '[HY093] [Oracle][ODBC]
[Rdb]Invalid parameter number. (-2000) (SQLDriverConnect);

[HY093] [Oracle][ODBC][Rdb]Invalid parameter number. (-2000)')"):
error calling Python module function DbGenericRE.connect

I am struggling to figure out what parameter is wrong, any one have an idea what could be causing this?


texts: I've got 16GB of ram, how should I configure MySQL Server?

texts:
I've got a dedicated production server with multiple CPUs and 16GB of RAM.

How can I configure it to take advantage of server resources for better performance? Website traffic is increasing and MySQL starts using a lot of CPU.

This is my current MySQL server configuration.

I am also planning to optimize the database, and check the queries to improve the slow ones.


texts: Unique constraint to enforce max one null per item

texts:
I am using PostgreSQL 9.3. Suppose we are manufacturing some items of certain types, at any moment we are producing at most one item of a certain type. The following table captures our history of manufactured items, there may be rows where manufactured_until is null - those are the items produced currently.

create table item
(
  id int,
  type_id int,
  manufactured_from timestamp,
  manufactured_until timestamp
)

Sample data:

1  | 101  | 1.1.2000    | 31.12.2012
2  | 102  | 1.4.2003    | 1.1.2010
3  | 101  | 1.1.2013    |
4  | 102  | 2.1.2010    | 4.5.2014
5  | 102  | 5.5.2014    |

The following logic should hold: for each item type, there should be at most one item being produced at the moment (manufactured IS NULL). In the example, I should not be able to add the record (6, 101, 27.8.2014, NULL).

I'd like to write a UNIQUE constraint that will guard this. Is it possible? For bonus points, is there a reasonably complex way to guard that the intervals do not overlap for one item type?


texts: Insert results from a stored procedure into a table variable

texts:
I have a stored procedure that stores values in a table variable. I select these values and return them when the procedure is called.

I am trying to set these return values in another table variable but I can't figure it out.

Stored procedure

ALTER PROCEDURE [dbo].[GetOrSetDomainId]
@DomainName varchar(50),
@DomainUrl varchar(50)
AS
BEGIN
    DECLARE @DomainId bigint;
    DECLARE @NumberOfRwos bigint;

    DECLARE @DomainHistory TABLE
    (
        DomainId bigint,
        HasHistory bit,
        ServerOnline bit,
        DatabaseOnline bit,
        ServerPerformance bigint,
        DatabasePerformance bigint,
        SoldTickets bigint
    )

    SELECT  @NumberOfRwos =  COUNT(Id)
    FROM DomainData
    WHERE DomainName = @DomainName OR DomainUrl = @DomainUrl

    IF(@NumberOfRwos = 0)
    BEGIN
        INSERT INTO DomainData (DomainName, DomainUrl) VALUES (@DomainName, @DomainUrl)

         SELECT @DomainId =  @@IDENTITY

         INSERT INTO @DomainHistory(DomainId,HasHistory)VALUES(@DomainId, 0)

         SELECT * FROM @DomainHistory
    END

    ELSE
    BEGIN
    ---not important here----
    END

END

Calling code

I call the procedure using:

DECLARE @DomainHistory TABLE
(
    DomainId bigint,
    HasHistory bit,
    ServerOnline bit,
    DatabaseOnline bit,
    ServerPerformance bigint,
    DatabasePerformance bigint,
    SoldTickets bigint
)

SET @DomainHistory = EXEC GetOrSetDomainId 'test', 'test2'
---Other alternatives:---
INSERT INTO @DomainHistory(DomainId,HasHistory)
VALUES(EXEC GetOrSetDomainId 'test', 'test2')

How can I do this?


texts:
I have following query:

strfinal = "
    TRANSFORM Format(Sum([Mandays].[Hours]),""#0.0"") AS [The Value]
    SELECT
        Mandays.WorkTypeCode AS WONo,
        Format(Sum([Mandays].[Hours]),""0000.0"") AS Total
    FROM Mandays
    GROUP BY Mandays.WorkTypeCode
    PIVOT UCase([Ent By]); "

The query returns more than 2048 records so it is not possible to show in VB6 MSHFLexGrid (limit is 2048). How do I change the query so that top 2000 records are fetched?


texts: I need help with a SQL Query. SQL beginner here!

texts:
I'm totally new to SQL and I've inherited a customer's server (the company that managed it no longer exists). They need me to run a SQL Query that shows their inventory sold for pots.

Specifically, how many of each item sold by each price code during a specific period of time. The guy who pulled this info used a program called Crystal Reports, but he said these reports could be run via SQL Query. This is what they were able to get from the guy (he created the database) before he retired:

Items sold contained in:
ARInvoiceDetails & ARInvoiceHdr

ARInvoiceDetails is the main table.

ARInvoiceHdr contains invoice date.

ARInvoiceDetails contains item sold + price code (price code contained in ARPriceLevels).

Item description is obtained from ICItems.

Size is obtained from ICItemSizes (ICValidSizes).

Note: categories and subcategories are also contained in ICItems using ICCategories & ICSubCategories

ARInvoiceDetails contains:

Selling Price (ARDetSellingPrice) - my note: I don't see this one, but I do see a ARDetUnitPrice.

Price Code (ARDetPriceLevelID).

Quantity Sold (ARDetQty).

My issue(s) is that I can't figure out how to pull the data from both tables. I've done a little research into join clauses, but don't there need to be at least one column in common between the tables? I've been analyzing the output from Queries that show the column names from both tables. There are NO common column names. There is, however, a column in each table that has the same data but different column names.

ARInvoiceDetails - column name: ArDetPriceLevelID (price code)

ARInvoiceHdr - column name:  ArInvPriceLevelID (price code)

According to the person that requested the report, price code will tell them the size and color of pots. Any help would be appreciated! I have access to the server and can answer any questions if this isn't enough information. I can't stress this enough - I'm totally new to SQL and my level of SQL knowledge is poor at best. I'm using Microsoft SQL Server Management Studio version 10.50.4042.0

Thanks!


texts: Joining on a M-N lookup table vs keyword searching a single column

texts:
I am in the process of writing an image gallery webapp similar to that of Danbooru, and in the process, decided to take a look at danbooru's database schema for an idea about how things are structured. In doing so, I've noticed that the 'uploads' table contains a column named tag_string, which I'm guessing contains comma-separated tags. There's also a tags table which (again, by the looks of it) contains metadata about individual tags.

This got me thinking about the practicality of my initial design whereby I have a M-N lookup table specifically for mapping N individual tags to M different files. My design would use SQL Join to get a result set containing all of the images I'm looking for, while I can forsee danbooru's solution using a search on the text column containing the list of tags associated with that image.

My lookup table could concievably get very large (in terms of # of rows) if every file contains upwards of 20 or more tags, whereas the other solution only extends the size of the files table by one column.

With these differences in mind, what are the pros and cons of the different implementations?

For reference, here is an incomplete (but contains enough information regarding this question) design of my schema.


texts:
my table looks like

CREATE TABLE `test_comments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned DEFAULT NULL,
  `test_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `comment` mediumtext COLLATE utf8_unicode_ci NOT NULL,
  `rate` mediumint(9) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `test_comments_user_id_foreign` (`user_id`),
  KEY `test_comments_test_id_foreign` (`test_id`),
  KEY `test_comments_parent_id_foreign` (`parent_id`),
  CONSTRAINT `test_comments_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `test_comments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `test_comments_test_id_foreign` FOREIGN KEY (`test_id`) REFERENCES `tests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `test_comments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

how can i retrieve comments like this structure:

[
    {
        "id": 3,
        "comment": "...",
        "comments": [
            {
                "id": 12,
                "comment": "answer for #3",
                "comments": []
            },
            {
                "id": 14,
                "comment": "answer for #3",
                "comments": [
                    {
                        "id": 22,
                        "comment": "sub comment for #14",
                        "comments": [
                            {
                                "id": 23,
                                "comment": "discussion continues..."
                            }
                        ]
                    }
                ]
            },
        ]
    },
    {
        "id": 44,
        "comment": "wow so many comments!",
        "comments": []
    }
]

I had an idea to form this structure in the application layer, but I think that this will negatively affect the memory consumption and the load as a whole.

I do not know how others solve such problems and i have not yet found a solution. I will be grateful if the community will tell me the right way :)


texts:
I'm completely stuck on a connection problem that we're experiencing for users connecting to our SQL Server instance from a particular domain using a particular application. The error that we're seeing is (from Windows Event log):
2020-11-30 00:11:22.33 Logon       Error: 17806, Severity: 20, State: 14.
2020-11-30 00:11:22.33 Logon       SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed   [CLIENT: xxx.xxx.xxx.xxx]
2020-11-30 00:11:22.33 Logon       Error: 18452, Severity: 14, State: 1.
2020-11-30 00:11:22.33 Logon       Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: xxx.xxx.xxx.xxx]

Users are able to connect to the database over the same session from an old legacy app that uses the old ADO database driver. But our newer .NET Framework app is unable to connect due to this error. Also, it works when I run the app from my sysadmin account. As a hapless developer with a limited understanding of SQL Server security I'm a bit out on thin ice when it comes to further diagnosing the problem here. Does anyone have any ideas what could be causing this problem?


texts:
What I want to do is to check if value of column equals some string, but case insensitive. Is there a performance difference when using ilike without wildcards (percents) and lower() ?

I was planning to create index on lower(column_name) and use lower(column_name) = value (value is lower case already) but it turns out that ORM I'm using doesn't allow me to use lower() in a pretty way (but has convenient method for ilike). Will column_name ilike 'value' use lower(column_name) index?


texts: Finding data recently created/updated in a whole PostgreSQL database

texts:
I have inherited a web application running on a PostgreSQL database, which I don't know well, and there is little documentation for the code and the database...

So I try to understand things like "which data is created/updated in the database when I perform this action in the web interface ?".

I know I could just look at the code of the application, but it's quite confuse and opaque.

I also know I can use track_commit_timestamp=on to find the last commit on a given table (see https://dba.stackexchange.com/a/168752/144567).

But what I would like is find the last changed data in the whole database (there is more than 900 tables), is it possible ? Thanks.

Postgres version is 9.6.


texts: is vertical scaling good idea for a mysql database server?

texts:
I operate a mariadb/mysql database server and i do not want to get into the problems with managing a multi-cluster database server
i have access to huge CPU and RAM and STORAGE when needed

so my question to the experienced and expert db admins is that is vertical scaling good enough? yes downtime is ok as long as data is protected
I rather deal with downtime of some system failure or issue that will only happen very rarely...it is better than the headache with managing a multi-cluster database to me

so what are the bottlenecks i will run into running a database up to 512GB memory and like 128vCPU? and like 2TB of SSD storage?

how is this worse than lets say running like a 8 cluster of mariadb/mysql database with each 64GB memory, 16vCPU and 256GB SSD storage?

yes i know about iops but i mean with lots of redis caching i think one can reduce stress on that part as well

pretty much there is more and more powerful CPU and servers that one can pack lots of stuffs in a single machine..not like years ago where that was not possible or at least easy..now one can pack several Terabytes, TBs(not a typo) of memory in a single machine

please send your advice and tips
thanks!!!


texts: Retrieving auto-generated keys for an INSERT statement

texts:
What is the best way to retrieve an  auto-generated keys after an INSERT statement ?

After searching online for DB2 I found this https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.java.doc/src/tpc/imjcc_t0057053.html

Is there a way to do it without using java ?

Preferably I would work using python so ibm_db.

Can such a thing be done on a postgres database ?


texts: Oracle - Is a separate database more secure than using the same database and a separate schema

texts: We have very few developers and the ask from management is insane (computer vision, AI, IoT, etc.). Management wants to split the production database which uses Oracle Apex because the Apex application will be on the public internet and therefore security is an issue. Is a separate database more secure than using the same database and a separate schema when using the Oracle database? I hate REST and all the complications like authentication that comes with it, so I would much rather be on the same database and just do a straight SQL select. Can I just grant select on specific tables between two schemas and thereby keep sensitive financial data equally secure as when having it on a separate database and sharing data using REST or a slow database link?


texts: space usage on sys.allocation_units and sp_spaceused

texts:
It is a known fact that the DMVs dont hold accurate information regarding number of pages and count of rows.
However, when you have the stats updated, I can't see why they wouldn't.

I am working on a monitoring tool, want to know disk size of each index and data, etc.
Eventually I would like to find the right fill factor, and other things etc.

The space used by my function and the old sp_spaceused differs a little bit on the space usage, but not on record count.

Can you see if there is anything missing in my select?

this is the sp_spaceused (then I convert the numbers in MB):

sp_spaceused 'tblBOrderRelationship'
go

select 318008/1024.00 AS reserved,
140208/1024.00  AS data,
177048/1024.00 AS index_size,
752/1024.00    AS unused

But when I run my select, code below\picture below, I get slightly different figures.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    schema_name(t.schema_id) as SchemaName,
    t.NAME AS TableName,
    t.type_desc,
    t.is_ms_shipped,
    t.is_published,
    t.lob_data_space_id,
    t.filestream_data_space_id,
    t.is_replicated,
    t.has_replication_filter,
    t.is_merge_published,
    t.is_sync_tran_subscribed,
    --t.is_filetable,
    i.name as indexName,
    i.type_desc,
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint,
    i.fill_factor,
    i.is_padded,

    sum(p.rows)               OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as RowCounts,
    sum(a.total_pages)        OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as TotalPages,
    sum(a.used_pages)         OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as UsedPages,
    sum(a.data_pages)         OVER (PARTITION BY t.OBJECT_ID,i.index_id)  as DataPages,

    (sum(a.total_pages)       OVER (PARTITION BY t.OBJECT_ID,i.index_id)  * 8) / 1024 as TotalSpaceMB,
    (sum(a.used_pages)        OVER (PARTITION BY t.OBJECT_ID,i.index_id)  * 8) / 1024 as UsedSpaceMB,
    (sum(a.data_pages)        OVER (PARTITION BY t.OBJECT_ID,i.index_id)  * 8) / 1024 as DataSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255
AND T.NAME = 'tblBOrderRelationship'

the figures

the bigger picture, including the index names

Now doing some calculations to check the results:

--==================================
-- the figures from sp_spaceused
--==================================
select 318008/1024.00 AS reserved,
140208/1024.00  AS data,
177048/1024.00 AS index_size,
752/1024.00    AS unused

--==================================
-- the figures from my select
--==================================
select 137+61+56+54 AS reserved,
       137 AS data,
       61+56+54 AS index_size

It is not so far off, really, apart the fact I did not calculate the unused space!

What can I do to make this accurate?

AFTER CHANGES:

After I replaced 1024 by 1024.00 the results are much more accurate.
I noticed records have been inserted into the table in question, and obviously the stats are not so up to date, but still the results match (under 1 MB difference - which is all right for me)

The new result sets are:

--==================================
-- the figures from sp_spaceused
--==================================
select
318072 /1024.00 AS reserved,
140208 /1024.00 AS data,
177096 /1024.00 AS index_size,
768 /1024.00 AS unused
go

--==================================
-- the figures from my select
--==================================
select 137.7578125+61.7968750+56.4218750+54.6406250 as reserved,
       137.7578125 as data,
       61.7968750+56.4218750+54.6406250 as index_size


texts:
I want to create an histogram based on price column values from equipment table.

I know I can count the frequency of prices in an interval I can set, but the problem is that I won't get the ranges with 0 frequency.

so if I want an interval of 200 and the values are:

0,55,150,420,490,550

I will only get the ranges:

0-200, count =   3

400-600, count =   3

as a solution I can write explicitly the ranges, like:

SELECT
count(CASE WHEN price>= 0 AND price < 200 THEN 1 END) AS '[0 - 200]',
count(CASE WHEN price>= 200 AND price < 400 THEN 1 END) AS '[200 - 400]'
...
...
from equipment
group by eq_type;

if I want to set multiple ranges, let's say from 0 to 10000 in steps of 200,
is there a way I can create an array/series and use it in the query instead of typing 50 rows of the above code?


texts:
We are trying to stop having a certain warning message emailed to us. The specific error is a ORA 600 - 17183 and 17302. We have been told by Oracle that we can ignore these warnings as they are a bug in Oracle 12.0.1.2 and are patched in the next version.

Currently we are running OEM 12.1.0.3.0 and are upgrading soon to 12.1.0.5.0. We have done research online to try and figure out the correct regex expression to ignore the above mentioned codes in the Alert log but to no avail. This is what we currently have to try and ignore the 17302 message in the Metric Collection Settings > Generic Alert Log Error:

.*ORA-0*(54|1142|1146)\D.*|.*ORA-00600:.*\[17302[^\]]*\].*

Any ideas/thoughts why this wouldn't work to suppress the emails?


texts: Need to Check if a Table Exists Before Deleting a Record

texts:
I have the following SQL query in my Ruby app:

sql = "DELETE FROM `#{database}`.`table1` WHERE `same_id` = #{some_id};"

The problem is that in rare circumstances table1 might not exist. I need to construct this query so even if the table does not exist SQL does not throw an error.

How can I implement this?


texts:
Simple structure:

have a table PAGES - which stores information about specific landing pages
working on developing a GROUPing concept for the pages, where we are saying that a page may or may not belong to a group, and it will only belong to a single group.

Would a linking table group_page.group_id,group_page.page_id be the proper way to go?  Or
pages.group_id ?


texts: The disappearing act of the "Invalid length parameter passed to the LEFT or SUBSTRING function" error

texts:
I'm running into the "Invalid length parameter passed to the LEFT or SUBSTRING function" error, but it goes away and the query works when I include the column I'm passing into those functions, any clue?

Doesn't work:

SELECT
  SQT.QUOTATIONID,
  UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager
  --, Email.LOCATOR
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
    ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
    ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
    ON D.PRIMARYCONTACTEMAIL = Email.RECID

  Invalid length parameter passed to the LEFT or SUBSTRING function

Works:

SELECT
  SQT.QUOTATIONID,
  UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager
  , Email.LOCATOR
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
    ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
    ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
    ON D.PRIMARYCONTACTEMAIL = Email.RECID

This is continuously repeatable, and the only change I made to the query was included the "Email.LOCATOR" column.
This query was working for years and just randomly stopped working today. I'm pretty certain it's a data issue, but am still perplexed why selecting the Email.LOCATOR column fixes the issue.


texts:
I want to know the risks of the pg_start_backup() control function.

For example, what will happen if someone resets the server, or if there is a power failure during pg_start_backup's operation?

What will happen if something goes wrong with the postgres process and it just gets out?

What will happen if the hard drive goes full with this option enabled?


texts: Queries which return more than 100k rows

texts:
I have a following query SELECT * FROM table_name WHERE #3_column BETWEEN start_date AND end_date which is taking about 10 seconds on my computer to execute and returns about 180k rows. Table schema is shown below:

Column      |  Type
#1  column  |  boolean
#2  column  |  double precision
#3  column  |  date
#4  column  |  date
#5  column  |  double precision
#6  column  |  double precision
#7  column  |  double precision
#8  column  |  integer
#9  column  |  double precision
#10 column  |  integer
#11 column  |  character varying(6)

Additional info: indexes are set on #3_column but query is doing sequential scan because of this fact.

My question is how to optimize this query? (I presume that reading from disk takes too long and that this cannot be optimized on query level). Thank you!

EDIT: this is result of explain (buffers, analyze) select * ... (in this scenario there is more than 150K rows returned)

Seq Scan on table_name  (cost=0.00..62504.01 rows=1298033 width=61) (actual time=0.015..1689.019 rows=1310109 loops=1)
  Filter: ((#3_column >= start_date::date) AND (#3_column <= end_date::date))
  Rows Removed by Filter: 926092
  Buffers: shared hit=20845 read=8116
Planning time: 0.124 ms
Execution time: 1899.839 ms

More info: table will have (when data is fully imported) around 20 milion rows, I am using postgres version 9.4.4


texts: Postgresql regex match all digits in column

texts:
How do I extract all digits from a string with regex in pgsql?

select regexp_matches('dsa8a552a5a2a5?', '\d+');

Will output only 8.

Need to get 8552525


texts: Table size reduce with help changing collation charset [MySQL]

texts:
I have table which is big enough, phpmyadmin shows that it takes more than 40Gb space. I can't remove data from it, but I've figure out one thing: there is one field which is described as

  varchar(3)    utf8_general_ci

There are always latin symbols.

I thought that changing collation to latin1_general_ci would reduce the Table size.

I've tried this out on small version of it with 200 Mb but I did not get expected result :(

Why so ?


texts:
Availability group has concept of listener that directs the requests to appropriate replica.
So is listener a single point of failure? How to handle this?


texts: SQL Server not starting at Windows startup but starts manually

texts:
My SQL Server instance's (SQL Server 2008 R2 Express) service has Startup Type = Automatic. The past couple of times I've restarted my computer the service has failed to start on its own, but it starts just fine when I manually start the service.

Event Viewer shows that the service fails to start because of a connection timeout. There are 2 entries regarding this timeout:

  Entry 1

  A timeout was reached (30000 milliseconds) while waiting for the SQL
  Server (SQLEXPRESS) service to connect.

  Entry 2

  The SQL Server (SQLEXPRESS) service failed to start due to the
  following error:  The service did not respond to the start or control
  request in a timely fashion.

I checked the ERRORLOG files and there are no errors logged around the time of those Event Viewer entries.

Any ideas on the cause of this problem or how I can investigate further?


texts:
This is very basic question, but I couldn't find an answer here...

So I have this very useful procedure without any parameters:

CREATE OR REPLACE PROCEDURE DISABLE_TRIGGERS
IS
  v_statement VARCHAR2(500);
  CURSOR trigger_cur
  IS
    SELECT trigger_name
     FROM   user_triggers;
BEGIN

  FOR i in trigger_cur
  LOOP
    v_statement := 'ALTER TRIGGER '||i.trigger_name||' DISABLE';
    EXECUTE IMMEDIATE v_statement;
  END LOOP;
END;

And I don't know how to call it...

This way:

begin
    DISABLE_TRIGGERS;
end;

I get a message that the procedure was compiled.

So maybe this way:

begin
    DISABLE_TRIGGERS()
end;

?

Which way is correct?


texts: Named a table “order” in Microsoft Office Access database and the webapp now can't see the table (even after renaming it)

texts:
I am creating a web application in Dreamweaver using ASP VBScript. The web application is a store and I have to implement a database into it (which I did). I created a table in my database called order and for some reason I couldn't dynamically enter the data from a record set that I wanted to.

I searched on Google and came across someone saying that order is a reserved word and that I should name my table something else. I did so (several times, several names, and even new tables with the same fields, thinking I could use this table, no joy.) But ever since, whenever I try to create a new recordset I am only picking up the old table (the one named order, the other two table are working fine).

I couldn't see the new renamed table fields when creating a recordset no matter how much I refreshed the database window.

I am able to access the database that I want to use when I double click the database through Local view or Remote View (I can't find the old DB anywhere). The problems arise when I want to use the data from the order recordset (since this is the recordset it is forcing me to use even though I can't). I am getting this error when I press on the + button of the recordset that is related to the order table

  [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

So when I go and change this code to the name of an existing table in my DB (which is now orders)  I then get this error

  [Microsoft][ODBC Microsoft Access Driver] The Microsoft Office Access database engine cannot find the input table or query 'Orders'.  Make sure it exists and that its name is spelled correctly' .

Here's the DB:

Here's the code where the problem is:

<!--#include file="Connections/cnFreshNew.asp" -->
<%
Dim rsProducts
Dim rsProducts_cmd
Dim rsProducts_numRows

Set rsProducts_cmd = Server.CreateObject ("ADODB.Command")
rsProducts_cmd.ActiveConnection = MM_cnFreshNew_STRING
rsProducts_cmd.CommandText = "SELECT * FROM Products"
rsProducts_cmd.Prepared = true

Set rsProducts = rsProducts_cmd.Execute
rsProducts_numRows = 0
%>
<%
Dim rsUsers
Dim rsUsers_cmd
Dim rsUsers_numRows

Set rsUsers_cmd = Server.CreateObject ("ADODB.Command")
rsUsers_cmd.ActiveConnection = MM_cnFreshNew_STRING
rsUsers_cmd.CommandText = "SELECT * FROM Users"
rsUsers_cmd.Prepared = true

Set rsUsers = rsUsers_cmd.Execute
rsUsers_numRows = 0
%>
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_cnFreshNew_STRING
Recordset1_cmd.CommandText = "SELECT * FROM Orders"
Recordset1_cmd.Prepared = true

Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>

I can't create a brand new DB and start over because I need my lectures login credentials as it is on the college's server and it's a bit too late to do that now, sadly.


texts: Comparing count of 2 tables, is this the correct way?

texts:
I'm building a survey system where

A client that has many users
A user can have multiple surveys.

I have a table users and a table survey_completions, I'm wondering if this is the correct (or best) way to see how many of the users for client 10, have a completion in the survey_completion table

SELECT
survey_id,
(SELECT COUNT(*) FROM users u WHERE u.client_id = 10) as users,
(SELECT COUNT(*) FROM survey_completions sc INNER JOIN users u ON u.id = sc.user_id WHERE u.client_id = 10) as num_users_completed
FROM survey_completions sc
INNER JOIN users u ON u.id = sc.user_id
WHERE u.client_id = 10
GROUP BY sc.survey_id


texts: Should I worry for this number of Aborted clients & connects?

texts:
I am getting below number of Aborted clients & connects

mysqladmin ext | grep Abort

Which results as:

| Aborted_clients                               | 719          |
| Aborted_connects                              | 4458         |

One thing to note is, I am getting frequent "MySQL Server has gone away" error.
Not sure if above number of Aborted-** can cause the

  MySQL Server has gone away


texts: mysql “Lock wait timeout exceeded” two transaction with different keys indexed

texts:
I have set of tables that I need to delete their records with a specific key and insert new records. Different transactions are used one for each key:

Transaction 1:

DELETE FROM table1 WHERE id=1; -- many records
DELETE FROM table2 WHERE id=1; -- many records
INSERT INTO table1 VALUES (1,...),...; -- many records
INSERT INTO table2 VALUES (1,...),...; -- many records

Transaction 2:

DELETE FROM table1 WHERE id=2; -- many records
DELETE FROM table2 WHERE id=2; -- many records
INSERT INTO table1 VALUES (2,...),...; -- many records
INSERT INTO table2 VALUES (2,...),...; -- many records

When the two transactions are running at the same time, I get the lock wait timeout exceeded error for one of them. The id for both table is indexed, so it should be row based locking, why do I still get this error?

Using InnoDB tables.


texts: How to display '0' where no data found in PL/SQL Oracle Reports

texts:
I have an Oracle Report (11g) in which I have selected a number field containing amount and have mentioned the format mask as 999,999,999,999. The data is displayed as follows.

Data1            25,000
Data2
Data3            23,000

Now I want to show a simple '0'in front of an empty record (i.e. Data2). I've tried it in adding a Formula Column (PL/SQL), or by initializing a '0' in the property palate of field by defining '0' against "Value if null". But, it doesn't work.

However, when I remove the format mask, value '0' is displayed by both above mentioned ways (by adding a formula column or by defining the value against 'value if null'). But, by doing this, the amount is displayed without format mask (i.e. 25000 and 23000).

How can I achieve the above discussed scenario. I want that the format mask should be there, and there should b '0' in front of empty records as well. In short, I need the following output in Oracle Reports 11g - 64 bit.

Data1            25,000
Data2                 0
Data3            23,000


texts: Problem with compare value with its aggregate function within the same table

texts:
I need to find the list of customer_id for customer who have spent more than the average customer.

This is the the query I use to generate table above:

SELECT customer_id, sum(Total) AS customer_total
FROM (SELECT T.order_id, customer_id, Total
              FROM (SELECT order_id, SUM(quantity * B.price) AS Total
                    FROM ORDERITEM AS O, BOOK AS B
                    WHERE B.b_id = O.bid
                    GROUP BY O.order_id) AS A JOIN "TRANSACTION" AS T
                          ON A.order_id = T.order_id)
GROUP BY customer_id
ORDER BY customer_total DESC

How can I start from here to find the list of customers who have spent more than the average customer? And also how to give the limit of digits for double? Thanks in advance!

Below is the query I tried, but it returns empty. And I am sure the correct result is not empty.


texts: MySQL - two tables with relation one to many - retrieve "n" fields from table1 WHERE SUM of a field on table2 is less/equal/greater than "value"

texts:
Tables structure:

CREATE TABLE IF NOT EXISTS `A` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`num` int(6) NOT NULL,
`docnr` int(6) NOT NULL,
`docy` varchar(3) NOT NULL,
`docd` date NOT NULL,
`ent` int(3) NOT NULL,
`paym` tinyint(1) NOT NULL,
`cli` int(6) NOT NULL,
`rags` varchar(40) NOT NULL,
`addr` varchar(30) NOT NULL,
`cap` varchar(5) NOT NULL,
`city` varchar(30) NOT NULL,
`pro` varchar(2) NOT NULL,
`naz` varchar(3) NOT NULL,
`telp` varchar(6) NOT NULL,
`teln` int(13) NOT NULL,
`rif` text NOT NULL,
`totd` decimal(12,2) NOT NULL DEFAULT '0.00',
`toti` decimal(12,2) NOT NULL DEFAULT '0.00',
`deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `B` (
`id` int(11) NOT NULL,
`num` int(6) NOT NULL,
`seq` int(4) NOT NULL,
`typ` varchar(10) NOT NULL,
`art` int(6) NOT NULL,
`art_x` varchar(17) NOT NULL,
`cod` int(3) NOT NULL,
`descr` text NOT NULL,
`qty` decimal(11,4) NOT NULL,
`um` varchar(2) NOT NULL,
`val` decimal(12,2) NOT NULL,
`sc1` decimal(4,1) NOT NULL,
`sc2` decimal(4,1) NOT NULL,
`sc3` decimal(4,1) NOT NULL,
`iv` int(3) NOT NULL,
`tot` decimal(12,2) NOT NULL DEFAULT '0.00',
`toti` decimal(12,2) NOT NULL DEFAULT '0.00',
`deleted` tinyint(1) NOT NULL DEFAULT '0',
KEY `idseq` (`id`,`seq`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I have two tables, A and B;
the relation between them is one to many (Aone->Bmany) given by field "id";
I need to retrieve "n" fields from table A WHERE the SUM of the field B.toti on table B is less/equal/greater than an "x" given value;

Example:

table A
id | num | docnr | docy |
1  | 100 | 563   | abc  |
2  | 50  | 9978  | abc  |
3  | 150 | 56    | abc  |
4  | 150 | 679   | abc  |
5  | 150 | 67    | def  |
6  | 150 | 12    | def  |

table B
id | seq | toti
1  | 1   | 10
2  | 2   | 15
2  | 3   | 5
4  | 4   | 10
3  | 5   | 25
2  | 6   | 567
3  | 7   | 2354
4  | 8   | 768
1  | 88  | 456
1  | 33  | 30
1  | 35  | 89
4  | 31  | 4543
5  | 77  | 223
5  | 9   | 34
6  | 13  | 156
6  | 63  | 6758
5  | 93  | 6
6  | 53  | 22

So:

SUM of all B.toti with id=1 -> 585
SUM of all B.toti with id=2 -> 587
SUM of all B.toti with id=3 -> 2379
SUM of all B.toti with id=4 -> 5321
SUM of all B.toti with id=5 -> 263
etc..

I want to SELECT A.docnr, A.docy, A.paym, A.num WHERE the result of the sum above is less/equal/greater than "value" (passed to the query as STRING);

Using the sums above:
if the sum of all B.toti fields for that 'id' is greater than, let's say, '585' then retrieve 'n' fields on table A for the matching 'id', in this case retrieve the row on A for the 'id' 2,3,4;

How can I achieve this?
I already tried my own solution but it was a complete mess and soo slow!!
Consider that later I may need to retrieve some data from a couple of other tables (with a join), I prefer to specify this in the case it could be relevant for the solution;

Can't figure out a good or acceptable solution, please help me.
Cris


texts: when do i usually optimize an innodb table?

texts:
I have a table that's always used 24/7. There is always a stream of update and/or insert  of 10-20 rows per 30 to 60 seconds.

As stated on the MYSQL documentation:
"After doing substantial insert, update, or delete operations on an InnoDB table that has its own .ibd file because it was created with the innodb_file_per_table option enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system."

I am not sure if what im doing with the table is "substantial" enough for me to optimize it regulary.

Also, what is the impact on performance with doing optimize table regulary?


texts: How to design database with january-december for every entry

texts:
Need help for designing my database, I have a table named transaction with columns:  tr_id, item, item_cost, item_quantity, total and my problem is:

I need to divide the item_quantity from january to december:

for example:

tr_id: 1
item: sock
item_cost(each): 20
item_quantity: 12
total: 240
jan:
feb:
mar: to dec:

I need a suggestion on how to design a database with this kind of entries.


texts: Statistics needs to be captured for query analysis

texts:
What statistics are required to theoretically prove that X query work better than Y query. As per my knowledge I need to look for:

Query elapsed time
Logical read
Logical write
Physical read
Physical write

Do I need to have other statistics also to measure? Do I also need to measure

Memory pressure

TempDb space usage

Buffer usage

Query plan (Scan, Seek)
to get perfect analysis.

Ex scenario:
I have two queries one is using index and one without index. I need to show which query is performing well.


texts: Find player's latest match whether player was the winner or loser

texts:
I have a table of match results with the following fields (and many more):

id
date_played
begin_time
end_time
winner_id   <-----foreign key into user.id
win_start_hcp
win_end_hcp
loser_id    <-----foreign key into user.id
loser_start_hcp
loser_end_hcp

I want to find the win_end_hcp for the last match played by user.id=X, whether he was a winner or a loser of that match.


texts:
Can someone help me with these?

BULK INSERT DATABESE01.dbo.TABLE01
FROM '\\COMPUTER01\FOLDER01\TextFile.txt'
WITH
(
    FIELDTERMINATOR = ' ',
    rowterminator = '\n',
    tablock
)

The error shows, couldn't open:

  Could not bulk insert because file '\SERVERNAME\FOLDERNAME\textFile.txt' could not be opened. Operating system error code 5(Access is denied.)

The path is on another computer on the network.


texts: How to restore database with ib_log0, ib_log1 and ibdata1 files in MySQL

texts:
We have a Mysql5.5 running on Windows server 2008 from past 4 years.

Recently we have moved ib* files to new locations because of space constraint in c drive.

When we started the Mysql it created all 3 ib* files in the default location. We could able to see the databases and tables through sql workbench.  But when selecting data it says <schema>.<table> doesn't exists.  So we copied back all 3 ib* files back to default location.  But the service is not getting started. Getting error code 1067.

And My Error Log is as below.

160603 12:23:33 [Note] Plugin 'FEDERATED' is disabled.
160603 12:23:34 InnoDB: The InnoDB memory heap is disabled
160603 12:23:34 InnoDB: Mutexes and rw_locks use Windows interlocked functions
160603 12:23:34 InnoDB: Compressed tables use zlib 1.2.3
160603 12:23:34 InnoDB: Initializing buffer pool, size = 753.0M
160603 12:23:34 InnoDB: Completed initialization of buffer pool
160603 12:23:34  InnoDB: Operating system error number 5 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory. It may also be you have created a subdirectory
InnoDB: of the same name as a data file.
InnoDB: File name .\ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

Running service as default LocalSystem.

And checked administrator login has full rights on data folder.

Can anyone suggest how to get back my tables with data using the old ib* files which I moved to new location.

Thanks in advance.


texts: Understanding Sort Estimates with TOP(variable)

texts:
I have the below query and I am trying to understand the sort estimate when I use TOP with a variable.

PLAN

create table justint ( c1 int )

insert into justint
select top 1000000 row_number() over(order by t1.number) as N
from   master..spt_values t1
cross join master..spt_values t2

declare  @MaxRowsToReturn    INT     = 500
SELECT  top (@MaxRowsToReturn) c1
FROM justint WITH (NOLOCK)
where  c1 >= 600000
ORDER BY c1 ASC

So how does an estimate of 404986 going into the sort operator comes out as an estimate of 100?  Is it just an arbitrary number since it cant sniff the variable in the TOP operator?


texts:
mongo 2.4.6 x64
RAM 24 GB

With normal work memory consume around 10GB.
After that we started read data from db (create archive raw data) memory consuming up to 90% of all memory trough 5day. And go forfard by 0.1% by day. Is it normal situation and what happening when oom-killer come ?

mongotop
insert  query update delete getmore command flushes mapped  vsize res faults  locked db idx miss %     qr|qw   ar|aw  netIn netOut conn     set repl       time
    *0     *0     *0     *0       0     2|0       0 21513g 43028g 1.49g      0     .:0.1%          0       0|0     0|0   190b 3k    31 replica  PRI   02:25:08
    *0     *0     *0     *0       0     1|0       0 21513g 43028g 1.49g      0     .:0.1%          0       0|0     0|0    62b 3k    31 replica  PRI   02:25:09
    *0     *0     *0     *0       0     2|0       0 21513g 43028g 1.49g      0     .:0.1%          0       0|0     0|0   190b 3k    31 replica  PRI   02:25:10
    *0     *0     *0     *0       0     1|0       0 21513g 43028g 1.49g      0     .:0.1%          0       0|0     0|0    62b 3k    31 replica  PRI   02:25:11

free -m
             total       used       free     shared    buffers cached
Mem:         24031      23835        195          0         33 1542
-/+ buffers/cache:      22259       1771
Swap:          511          4        507

Collection = 20TB


texts: SQL Server Populate replication server from DPM

texts:
I've inherited an old SQL Server database with 4 machines: production_server(2008.r2), DPM_server(DPM 2007), and two replication_server1(2008.r2) replication_server2(2012). SQL Server had to be reinstalled on replication_server2, and so all data on that machine was lost.

I need to get transaction replication from production_server to this server back online without hammering the production server to create the initial snapshot.

Is it possible to use my DPM server to provide a recent copy of the data, Either via a Backup or snapshot?

Or, Is it possible to have replication_server1 provide the initial snapshot, and then point it to the production_server publication once everything has caught up?

I haven't been able to find clear answers in the documentation, thanks in advance for your time.


texts: Error : failed to Retrieve data for this request when expanding subscriptions in SQL Server 2008

texts:
We are getting error when expanding subscriptions to delete them, we have already dropped distribution database. The error text is

  Failed to Retrieve data for this request. Microsoft.SqlServer.Management.Sdk.Sfc)


texts:
I have user who is trying to access some tables from our Microsoft SQL Server 2008 R2 system (which very recently got moved to a brand new hardware/software stack) using Access 2007 via an ODBC connection, a method they've been using for years. Now they're getting 'Reserved Error (-7711); there is no message for this error.'

In my googling process I haven't been able to identify any relevant information that isn't related to 2003 versions of Sybase. I'm guessing there's some security of configuration setting I've forgotten to set on the new box but to me the error seems pretty obscure and I'm not sure where to go from here.

Any help you can provide would be greatly appreciated.


texts: How to get accurate row counts in partitions in a table

texts:
I am trying to fetch row counts in each partition for a table. But I got confused as in sys.partitions row counts is on basis of indexes.

So how can I get exact row count for partitions in a table.


texts:
Please see the following link from Super User. I cannot get anything working:

  https://superuser.com/q/342609/93569

Update: This link has been dramatically changed! Please see the link.


texts:
This is my Oracle DB :

Server :      hp805
Listener :    7633
SID :         idopd
ServiceName : serv01
Login :       ikr_adm
Passwd :      ikr_adm_01

How do you connect to this DB ?


texts: Inconsistent equality checking of Unicode text in Postgres 9.5?

texts:
Using PostgreSQL 9.5. It seems like Postgres is being inconsistent with how it compares strings similar to لى. A unique constraint is considering some strings equal that GROUP BY is considering different.

I have a select query that is using GROUP BY on a TEXT column and an aggregate function on some other column to ensure uniqueness of the TEXT column in the output, and I'm inserting the results into a table with a primary key (and thus unique) constraint on the TEXT column.

Looks similar to this; I've just changed the table names for simplicity:

 INSERT INTO mytable ( % mytable has string TEXT PRIMARY KEY, score INT
            SELECT
            sq1.string string, sq2.score / sq1.score
            FROM
            (
                SELECT n.string string, SUM(n.score) score
                FROM
                othertable1 n % has string TEXT (non-unique) and score INT
                GROUP BY string
            ) sq1,
            (
                SELECT n.string string, SUM(n.score) score
                FROM
                othertable2 n % has string TEXT (non-unique) and score INT
                GROUP BY string
            ) sq2
            WHERE sq1.string = sq2.string
            ORDER BY score DESC LIMIT 100000
        );

This should never fail, right? It fails: violation of unique constraint due on key لى.

And it has worked many times before with other data sets containing millions of rows in PG 9.3; I don't know whether لى was in the data back then. I know Arabic has decorations you can put on the letters, so I wonder if that's tripping it up.

Does anyone have an alternative explanation, or should I report this as a bug once I can reproduce it more easily?

UPDATE: Confirmed that the query runs successfully on a PostgreSQL 9.3 server with the same data. There are some moving parts here, so I'm trying to find exactly what the problematic strings are so I can make a simple list of queries anyone can run to expose a bug.

UPDATE 2: Argh, I can't get my database to give me a set of strings I can copy into a table and expose a bug. I've been trying to do it with COPY TO. Something along the way keeps stripping the Arabic text of the differences that are making it fail, I think. But I tried a simpler query, and it's also failing. It's more obvious that this should work:

INSERT INTO mytable ( % mytable has string TEXT PRIMARY KEY, score INT
    SELECT n.string string, SUM(n.score) score
    FROM
    othertable2 n % has string TEXT (non-unique) and score INT
    GROUP BY string
);

I'm still working towards getting something others can try because, of course, a bug report is useless if I say that it only works on my data.

UPDATE 3: I ran it again with different data and ran into the same problem with Cyrillic characters У and В. Making a table containing them didn't reveal anything. Same problem as with the Arabic text, I think. Something is getting stripped along the way.

UPDATE 4: This is definitely a bug. I'm still trying to figure out how to report this. I found a workaround:

WITH glitch(string, score) AS (
    SELECT n.string string, SUM(n.score) score
    FROM
    othertable2 n % has string TEXT (non-unique) and score INT
    GROUP BY string
)
INSERT INTO mytable ( % mytable has string TEXT PRIMARY KEY, score INT
    SELECT DISTINCT ON(string) * FROM glitch
);


texts: What is the best way to remove this table partition?

texts:
I have to change a partitioned table in Sql Server 2008 R2 to a normal table to make my database compatible with Sql Server 2016 Standard Edition.

Actually the table has 5 partitions with the following number of rows:

> boundary, rows
2009-01-01 00:00:00.000 419
2010-01-01 00:00:00.000 386031
2011-01-01 00:00:00.000 1307990
2012-01-01 00:00:00.000 673183
NULL                    9743057

The table contains a BLOB (image) column. The total size of the table is around 25 GB.

I have read through the question How to remove a table partition but although it has answered there is no accepted answer and the answers do not address my question entirely.

I realized the ALTER PARTITION FUNCTION MERGE RANGE command, but I do not really understand what will happen. Will the data be merget into one of the existing filegroups and afterwards I will still have a partitioned table?

Do I instead have to copy all the data into a new table with the same structure (may take quite a while...)?

I will have to perform this action during a downtime, so I need a procedure that is as efficient as possible.


texts: Insert into a table selecting data from a sub query

texts:
I would like to insert a few data into a table selecting data from a subquery having multiple joins.

I tried the following way:

insert into mytable (columns)
select * from
 (select columns
  from tableA
  UNION ALL
  select columns from tableB
  UNION ALL
  select columns from tableC)

Can you please suggest me a way out, since I am getting the following error while executing the above query.

  Incorrect syntax near ')'.


texts: Fastest way to do a mysqldump restore on Amazon RDS?

texts:
We need to restore a database from a 7-Gb mysqldump file to a RDS Amazon instance.
The full restore takes between 3 and 4 hours (probably because of network latency since the dump has to reside on a EC2), and it's too long for us. For reference, a restore to a local mysqld would take less than 30 minutes.

Is there a Amazon-related solution that would allow us to reduce the import time?

Questions about optimizing a mysqldump restore time have been asked before, such as:

Speeding up mysqldump / reload
MySQL any way to import a huge (32 GB) sql dump faster?
Reasonable times for a mysqldump restore?
MySQL settings useful to speed up a mysqldump import

However, this question is different because relates to a RDS instance, so:

Additional constraints are applied, for instance some MySQL variables (e.g. innodb_doublewrite) cannot be changed to improve performances
There may be some Amazon-native solution or product that would solve this issue easily and nicely


texts: For a standby workload of long running analytical queries, is it better to turn on hot_standby_feedback or set max_standby_*_delay settings to -1?

texts:
When spinning up a hot standby server specifically for BI/Analytics purposes where long running queries may be common, is it better to turn on hot_standby_feedback or set the max_standby_*_delay settings to -1?

My understanding is that the hot_standby_feedback prevents the master from doing things like VACUUM until it is safe to do likewise on the standby where the max_standby_*_delay settings allow VACUUM to commence on the master but the standby, if necessary, waits to apply any vacuum cleanup that could conflict with a long running query.

Furthermore, the docs state for hot_standby_feedback:

  Remedial possibilities exist if the number of standby-query cancellations is found to be unacceptable. The first option is to set the parameter hot_standby_feedback, which prevents VACUUM from removing recently-dead rows and so cleanup conflicts do not occur. If you do this, you should note that this will delay cleanup of dead rows on the primary, which may result in undesirable table bloat. However, the cleanup situation will be no worse than if the standby queries were running directly on the primary server, and you are still getting the benefit of off-loading execution onto the standby.

And for max_standby_*_delay the docs state:

  If the standby server is tasked as an additional server for decision support queries then it might be acceptable to set the maximum delay values to many hours, or even -1 which means wait forever for queries to complete.

It's still unclear to me which is better and what the exact pros and cons of each are?


texts: Ok, I will start this off by: Yes, I know I should have paid more attention.  That being said, I was trying to create a full backup of my DB prior to doing truncation on a number of the tables.  When I did this, I failed to carefully inspect the destination section of the wizard.  I ended up placing my full backup inside of a *.trn Transaction Log file.  I can see it if I inspect the contents, but is there any way to remove just the Full Backup component, leaving the Transaction Log component intact?  I only ask this because the full backup is 63 GB.  I don't want to delete the existing Transaction Log in that file if I can avoid it.


texts: MySQL begin end from documentation doesn't work

texts:
I am using XAMPP. I simply tired this begin..end -

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;
  WHILE v1 > 0 DO
    SET v1 = v1 - 1;
  END WHILE;
END;

statement and got error. This happens with every begin..end or while that I try. Is it because of my mysql version ? XAMPP version is v3.2.2 and mysql is Version information: 4.7.9. I am really confused. The error is :

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3

Thank you in advance!


texts: connect to server with IP address running sql-express instance through sql-management studio

texts:
I have my VPS server in a remote location with ip address 172.16.11.2 (for example) having two instances of sql-server express running.

one instance is Microsoft SQL Server 2008 R2 (SP2)  where I connect from my local PC using remote desktop with the following credentials:

 servername : 172.16.11.2
 authentication : sql - server authentication
 userid: xx
 password:xx

the other instance is:

Microsoft SQL Server 2008 R2 (SP2) with advanced services. To connect to this machine I remote desktop to the server and type in

 servername : machinename\sql-express
 authentication : sql - server authentication
 userid: xx
 password:xx

now using remote desktop from my local machine how will I connect to this instance of sql-server.

I tried

 172.16.11.2\sqlexpress

but this failed. I also enabled sharedmemory/namedpipes/tcp-ip but still I am not able to connect. When I go to the machine where sql-express is installed and then type machinename\sqlexpress I am able to connect the sql-express instance, but from my local machine using sql-management studio if i type in ipaddress\sqlexpress I am not able to connect. It gives me network related issue error.


texts: Replication Server version 15.7.1 - Sybase ASE 15.5, how to change primary data server in RSSD?

texts:
Recently, I am requested to migrate the current Sybase data server to a new Sybase data server (let's say, from pub1 to pub2). To do this, I need to rebuild all the RepAgents which have been set up previously in pub1. Is there any primary data server I would need to set in the RSSD? Because I could see when I run the command 'connect' without any option, it will connect to (pub1.repserver) instead of (pub2.repserver). Is there any configuration required on the RSSD to make it (pub2.repserver)? Also, I actually have setup one RepAgent (pub2.db_lotte), the setup process using rs_init was successful, but seems the RepAgent connection is down after the setup process (admin who_is_down), the connection couldn't bring up and no error is captured in the replication log.

Appreciate if anyone could help.

Thanks.


texts:
I have a mediawiki running on my local machine and just started running mysqldump last night:

$ mysqldump -h localhost -u  wikiuser --default-character-set=UTF8 wikidb --add-locks --skip-lock-table> ~/Dropbox/admin/wikimedia_backup/backup.sql

But now mediawiki protests

  Warning: The database has been locked for maintenance, so you will not
  be able to save your edits right now. You may wish to cut-n-paste the
  text into a text file and save it for later. The administrator who
  locked it offered this explanation: Dumping Database, Access will be
  restored shortly

This sent me looking for locks in the wikidb database. I ran

UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

, but problem persists.

My sqldump statement runs with the  --skip-lock-table options (I think I should add or replace with  --add-locks?)

I reran the command with both options.

I also tried to kill the process outright:

mysql> SHOW FULL PROCESSLIST \G;
*************************** 1. row ***************************
     Id: 77
   User: wikiuser
   Host: localhost
     db: wikidb
Command: Query
   Time: 0
  State: NULL
   Info: SHOW FULL PROCESSLIST
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> kill query 77;
ERROR 1317 (70100): Query execution was interrupted

So, two questions:

how do I lift the existing lock;
how should my options be in the mysqldump call to avoid the problem, prospectively?


texts: MySQL v.5.7.x: What will happen if I remove NO_ZERO_IN_DATE & NO_ZERO_DATE

texts:
I am trying to copy a database to another via phpMyAdmin and the process is not completed.

Error: # 1067 - Invalid default value for 'date_start'

Have a look in the following structure:

CREATE TABLE `prefix_coupon` (
`coupon_id` int (11) NOT NULL AUTO_INCREMENT,
`name` varchar (128) NOT NULL,
`code` varchar (10) NOT NULL,
`type` char (1) NOT NULL,
`discount 'decimal (15,4) NOT NULL,
`logged` tinyint (1) NOT NULL,
`shipping` tinyint (1) NOT NULL,
`total` decimal (15,4) NOT NULL,
`date_start` date NOT NULL DEFAULT '0000-00-00',
`date_end` date NOT NULL DEFAULT '0000-00-00',
`uses_total` int (11) NOT NULL,
`uses_customer` varchar (11) NOT NULL,
`status` tinyint (1) NOT NULL,
`date_added` datetime NOT NULL,
PRIMARY KEY (`coupon_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8

See the columns date_start &date_end that have a default value of '0000-00-00'.

If I run the command:

show variables like 'sql_mode';

Result: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

NO_ZERO_IN_DATE, NO_ZERO_DATE are the ones that "create" the problem.

The question is what will happen if I remove these two?
Should I do it?


texts: How to commit transaction on an after update event trigger?

texts:
I'm new here in dba.stackexchange.com, so if I were to make any mistake, my apologies in advance.

I kinda confused on how trigger work. So I have a trigger called updcredaccess on table A that updates table B on after update event. Then I need the currently updated value on table A for other table to inspect, so I used autonomous transaction trigger. But then when I update, the value on table A doesn't seems to be updated with the current value.

Here is my table

Table A
id varchar(10)
val varchar(10)

Table B
id varchar(10)
val varchar(10)

and here is my trigger
Table A trigger

create or replace trigger updA
after update on A
for each row
declare
pragma autonomous_transaction;
cek varchar2(5);
begin
commit;
select val into cek from A where id = :new.id;
dbms_output.put_line('Cek cek: ' || cek);
update B set val = :new.val where id = :new.id;
commit;
end;
/

And for table B
 Table B trigger

create or replace trigger updB
after update on B
for each row
declare
v varchar2(5);
begin
select val into v from B where id = :new.id;
-- Some other pl/sql code --
end;
/

I then input 2 records like

 insert all into A values ('a', 'false') into A values ('b', 'false') select * from dual;

Then i tried this code

 update A set val = 'true'

The query works fine. But I was so confused because at the log console it says

  Cek cek: false (The output log at trigger A)

The value still hasn't changed.

So, what I need is the current new val value at the 2nd trigger, so It can be processed. Can anyone help?


texts: SQL Server 2012 Unable to Utilize Defined Amount of Locks

texts:
So I've been beating my head against the wall for two days about this and I can't find a solution. I'm running a script to stage a large amount of data. This script requires roughly 35 million locks in order to complete; this translates into needing 3.36GB of memory available for locks. I set the number of locks to 37 million in the configured values, however after the reboot the running values are only allowing for 16,695,296 locks.

This corresponds to roughly 14% of available memory. As SQL Server 2012 can use 40% of available memory for locks, I should have plenty a room, but I don't. My question is: why can't SQL Server utilize the extra 26% of available lock memory? Everything I've read says that it should be able to.

Also, I did try running the locks on dynamic, but that also failed for the same reasons and with the same error codes: error 1204, instance can not obtain a lock resource. I know it's the server configuration and not the script, because I ran the exact same script, with the exact same data set on an older server (SQL Server 2008) without issue.

I appeal to the database admin gods for help; help me oh great ones!

Edit: This is a VM on a fail-over cluster, the VM has 16 GB of memory allotted to it. I've given SQL Server 12GB and left 4GB available for the host operating system.


texts: What are 'Range' and 'Radius' queries and what is the difference between them?

texts:
While searching for easy and effective tools to improve performance of Data fetching for our application, we bumped into "Redis".

In documentation of redis, there is following specific mention of these two types or categories of queries:

  It supports data structures such as strings, hashes, lists, sets, sorted sets with range queries, bitmaps, hyperloglogs and geospatial indexes with radius queries...

This was first time in area of database and data structures, I have read such thing. I dug out internet to know more, but found nothing useful.

What exactly are Range and Radius queries? What is the difference between the two? Any explanation with example will be useful.


texts: Error: "mod_fcgid: can't apply process slot for" makes my server unresponsive for a period of time

texts:
Following error keeps appearing in my PHP error_log

mod_fcgid: can't apply process slot for

So far I pinpointed this issue to following settings in httpd.conf

FcgidMaxProcessesPerClass 8
FcgidMaxProcesses 20

By default these settings were set to 8 and 20, this would result in periodical freezes of my server (2 times per hour for 1 - 2 minutes).

I modified these settings to the following

FcgidMaxProcessesPerClass 1000
FcgidMaxProcesses 1000

With these new settings of 1000 each, server freezes occur only once per day but for as long as 10 - 30 minutes.

--What I mean by server freezes--

Both PHP and MySQL server is unresponsive. 500 error is returned to the client after a minute or longer.

This freezing behavior stop completely on its own, everything goes back to normal after stated time above.

High server load is not likely to be the issue, since these freezes occur randomly each day. Both CPU and RAM is at 3% - 6% when freezes occur.


texts: Are TEXT fields stored separately from the row in MySQL-ISAM?

texts:
We have a "notes" table and some of the queries are getting very slow even though we're not searching on the note contents. We're wondering if moving the note content into a separate table would speed things up?

I wouldn't think separating the content would matter -- aren't TEXT fields essentially just pointers to somewhere else on disk? Or does table size matter?

Does InnoDB handle this better?


texts: Single table with more columns vs multiple tables with fewer columns

texts:
What would be a better database design for a social network website? A single table with more columns and fewer rows, or multiple tables with fewer columns but more rows?
For example: A user can post an update on their wall or in a group.
Two database designs I can think of are:
Design 1
UserPosts

id
userId
post
datetime

UserGroupPost:

id
groupId
userId
post
datetime

Potential problem : Might require joins, which can (in future) be a slow query.
Design 2
Posts:

id
userId
groupId
post
datetime (where groupid would be null if user posts on their wall)

Potential Problem : Looping over large dataset could take a (long)time.

How can I get better performance when data increases? Is there any other (better) way?


texts: Can not run pgAgent as daemon in CentOS 6.6

texts:
I have a server with CentOS 6.6 & postgresql 9.5.

I have successfully setup the pgAgent from source code in postgresql website mentioned, but when I start it, I got error: Error: Cannot get the official hostname (error 11: Resource temporarily unavailable)

I start pgagent by this command: pgagent host=127.0.0.1 dbname=postgres user=postgres
I setup my password in .pgpass file, and I can connect to server by using psql.


texts: How to remove a listener IP from a SQL Availability Group Listener by T-SQL

texts:
The title says it all.
I have an availability group that was a multiple subnet, but now the environment have changed ,all replicas exist within a single subnet.
How to remove the superfluous second IP.
Is this only way remove the ip from Failover Cluster Manager?
I have submited a feedback to micosoft,welcome to vote.

https://connect.microsoft.com/SQLServer/feedback/details/2907012/how-do-you-remove-a-listener-ip-from-a-sql-availability-group-listener


texts: Increase sga_max_size on Oracle 10g Windows

texts:
i would like to increase the max memory size of one Oracle 10g database installed on Windows.

The v$parameter shows

  sga_max_size 452984832

and

  sga_target 452984832

Note that the ISDEFAULT column for sga_max_size is TRUE and every other IS settings are FLASE

I have found a init.ora file in the admin\MYINSTANCE\scripts and i have changed both parameters and restarted the service with no success.

There is another init.ora file in srvm\admin folder but i don't know if i can modify this one.

Where should i modify this parameter to have the memory increased ?

Thanks


texts:
when we use SUM on a data table gives a 1 or 2 cents of difference due to rounding in Postgres.

We are using Postgres 9.4, the field data types is numeric with 19,3
and the result have to be with two decimals.

for example:

 15.309
 10.111
  5.005

and the result must be: 30.43

the query is:

SELECT SUM(fcv_total_comprobante)::numeric(19,2)
FROM factura_venta
WHERE DATE(fcv_fecha_comprobante) BETWEEN '2015-09-01' AND '2015-09-30'

the result given is 30.42 (because is 30.425) and must by 30.43

which would be the best way to round a number to 2 decimal places?


texts: What is the replacement for EXEC sp_dboption  in SQL Server 2012?

texts:
From microsoft connect you learn, that it is closed by design. But they do not tell how to replace

EXEC sp_dboption master

to query the current db options.


texts: How to backup databases to ensure data recovery

texts: I have 15 customer databases that have minimum replication at different sites.  I currently do nightly back-ups and transfer the database back-up nightly to our main server via 7zip.  We recently had a hard drive crash at 6PM which means we lost all data in the database from 2AM - 6PM that day.  How can I back-up our databases throughout the day without compromising the functionality of the database during the day? We do over 1,000 transactions daily so we are constantly writing to the database.  My current method is not realistic for hourly back-ups as performance would suffer on the machine.


texts: Derive Date Spans from Start and End Dates in SQL Server table


texts: Multiple foreign keys referencing a single table in one statement

texts:
I have written the following statement:

CREATE TABLE ORDEN (
    `numero` INT UNSIGNED ZEROFILL NOT NULL,
    `fecha` DATE NOT NULL,
    `cliente` INT(6) UNSIGNED NOT NULL,
    `nombre_cliente` VARCHAR(64) NOT NULL,
    `direccion_cliente` VARCHAR(64) NOT NULL,
    `email_cliente` VARCHAR(64),

    /* Se utiliza ON DELETE NO ACTION porque probablemente (al igual que cualquier otro documento del mismo tipo) las órdenes se quieran conservar */
    FOREIGN KEY `fk_ORDEN_CLIENTE_ID`(`cliente`) REFERENCES CLIENTE(`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
    FOREIGN KEY `fk_ORDEN_CLIENTE_NOMBRE`(`nombre_cliente`) REFERENCES CLIENTE(`nombre`) ON DELETE NO ACTION ON UPDATE CASCADE,
    FOREIGN KEY `fk_ORDEN_CLIENTE_DIRECCION`(`direccion_cliente`) REFERENCES CLIENTE(`direccion`) ON DELETE NO ACTION ON UPDATE CASCADE,
    FOREIGN KEY `fk_ORDEN_CLIENTE_EMAIL`(`email_cliente`) REFERENCES CLIENTE(`email`) ON DELETE NO ACTION ON UPDATE CASCADE,
    PRIMARY KEY(`numero`)
);

The problem is that I'm getting this error when trying to execute the statement:

  Error Code: 1215. Cannot add foreign key constraint

I've been thinking that maybe referencing many columns from a single table with foreign keys in one statement is not allowed.

Here is the definition of the CLIENTE table as well:

CREATE TABLE CLIENTE (
    `id` INT(6) UNSIGNED ZEROFILL NOT NULL,
    `email` VARCHAR(64), /* Dirección electrónica */
    `nombre` VARCHAR(64) NOT NULL,
    `direccion` VARCHAR(64) NOT NULL,

    PRIMARY KEY(`id`),
    UNIQUE(`direccion`)
);

How can I resolve my issue? I've been searching for a solution but had no success.


texts: "Incorrect string value" while migrating from MSSQL to MySQL with Workbench

texts:
I got the task to migrate our MSSQL database into a MySQL database.
Therefore I am using the recent version of MySQL Workbench and FreeTDS Driver.

During the migration I always get errors like this:

ERROR: `dbo`.`emails_text`:Inserting Data: Incorrect string value: '\xA0</td>...' for column 'description' at row 1
ERROR: `dbo`.`prospect_lists`:Inserting Data: Incorrect string value: '\xFCrs MB...' for column 'description' at row 4
ERROR: `dbo`.`campaigns`:Inserting Data: Incorrect string value: '\xFChrung...' for column 'name' at row 24

I assume that utf8 is the troublemaker but I couldn't find any solution to my problem till now.


texts:
I have the following db schemas:

Comments table

and Posts table ( has FK to Comments table)  :

As you can see, there is a set of the same columns for both tables.
Is it bad design? If yes, how can I avoid it? Also I want to use Entity Framework to map this entities.
I tried to use inheritance and table-per-hierarchy approach , but unsucessfully (difficult to understand how to map self-referencing table)

Regards